How to migrate data of to another sql server Good day,

I have a database running on a virtual machine hosted at our ISP. It is a fairly large database which has about 5GB of data inserted into it every day (network logs).

It is partitioned daily at midnight and has a file group and a file for every day.

We currently only have enough space to keep a few days data on the virtual machine. I have setup a dedicated machine at our offices which is running sql server with plenty of hdd space  and will be much better suited for running queries on the large volumes of data.

Now what I want to find out is:
how can I efficiently move off one (partition / filegroup) from sql1 machine to sql2 machine?

We don't have great internet here in South Africa, so any sort of compression / safety checks will be needed.

I was thinking I could backup the file to a .bak and zip it, then download that file to the sql2 server and restore it. However I am looking for a more integrated approach possibly using sql server and  want not not code my whole own program to do such a thing.

I know that sql server has a lot of mechanisms built in for data warehousing. I am sure that there is some component in sql which does something like this. After all I am trying to achieve a basic version of data warehousing.

What options do I have / does sql server provide me and where can I go look to how to implement them properly?

I am running Sql Server 2012 Enterprise on both machines.

For very large Databases you can always use the compression method in SQL server 2012. That will help a lot in reducing down the backup size of the database:

For how to use and more on this refer to below link:

http://www.sqlservergeeks.com/sql-server-database-backup-compression-faster-disaster-recovery/

To avail the space, make sure you're backup retention period is appropriate so as to make proper amount of disk space available.
If possible do some TPM like cleaning activity on the disk to get some space.
Also, for data level compression you can just script out the schema and then move data using BCP OUT and BCP IN. You can even zip those files and move them across the destination server.
Last but not least: One more thing to add :

Make sure that Instant File Initialization is enabled on the server. That will dramatically reduce the restore time. Refer to below link for more info on this..

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx


How can I verify that a full database restore reflects the exact source database in SQL Server? We are decommissioning our old SQL Server 2000 Ent. instance in favor of SQL Server 2008 R2 Ent. My planned migration path is:

Terminate client connections (2000)
Full Backup (2000)
Restore (2008 R2)

I am being asked to provide conclusive proof that every single transaction "made it" and that the data is an exact replication of what existed on the 2000 instance.

I hope that I can use the following documentation as evidence:

Full Database Backups
Performing a Complete Database Restore (Full Recovery Model)

However, if this is not sufficient, the only thing I can think of is to rip through ever row of every table of every database and calculate a checksum (on both instances) as well as get row counts for every table in every database.

Is there any better way to satisfy the "exact replica" verification criteria? I'm also open to better documentation.

When you take the backup of database, the last LSN on the source will be X. If any activity will occur (including, say, an automated checkpoint), the source LSN will progress forward to X+n. If there is any activity that occurred on the source and was not captured on in the backup it would leave an imprint in the source log, somewhere between LSN X and X+n. Using fn_dblog one can look at the log and see if any such activity occurred.

An easy way to ensure no such activity occurs after backup is to set the database to read_only immediately after taking the backup. To prevent any activity sneaking in between the backup and the change to read_only you can disable connections or set the database in single_user mode, even start the server in single user mode.

So a procedure like following should be pretty much bulletproof:

restart source server in single user mode (-m)
connect and take the backup
change database to read_only
restore the database on the new server
run any tests you have on the new server
connect applications to new server
decommission or restart old server


One to Many Relationship: Describing the Tables If we have two tables in a One-to-Many relation ship, is there a standard name to properly refer to the tables?

For example if I have a table of books, and a table of authors (ignoring for now the possibility of joint authorship), how can we generically refer to the two tables in the relationship.

I have come to refer to the authors as the Parent table, and the books as the Child table, arguing that the books may be regarded as the brain-children of the authors. This description doesn’t work as well for, say, a table of sales and saleitems.

I have sometimes seen them referred to as the one and many tables, but I wonder whether there is a more useful way of referring to them.

Is there a standard way of referring to the tables?

The typical nomenclature for this type of table design would be "class-subclass".  In your example of Books and Authors, Books is a subclass of the Author class.  Any book automatically has an author, since no book can ever be written without having an author write it.  Same goes for Sales and SalesItems; each SalesItem must have an associated Sales object.

However, "parent-child" may be employed where it makes sense.  As is so often typical, the answer is It Depends™.

Using nomenclature that makes sense for the specific domain in question, and is easily and readily understood by the stakeholders, ensures future comprehension.

One-to-many is a typical term used to describe the relationship between rows in each table.  The relationship might also be one-to-one or many-to-one or even many-to-many, depending on domain requirements.


Switch to InnoDB, and

Set innodb_buffer_pool_size to at least 250M
Show us the tentative SELECTs
SHOW CREATE TABLE (DESC is inadequate)


Creating MySQL NDB cluster on Hyper-V I want to create a MySQL NDB cluster on Hyper-V for testing so I have some questions in this regard.

Can I use Windows XP 32 bit? (because I already have a VM) If not then can I use Windows 7 or 10?
Do I only need one VM where both nodes will be installed (I don't know if MySQL supports multiple instances or something like that) or do I need at least two VMs?
I read that NDB cluster require load balancer. Can I do it without load balancer? I mean I will manually run statements (Insert/Update/Delete) on server1 to check if it goes to server2 or not instead of using some PHP application.
Do I need free version of MySQL or some enterprise version?
Do I only need MySQL or some other software also needed to setup cluster?

Can I use Windows XP 32 bit? (because I already have a VM) If not then
  can I use Windows 7 or 10?

You have the list of supported platforms here:
http://www.mysql.com/support/supportedplatforms/cluster.html

From there I can see there are 32-bit binaries, and several windows versions, but check your specific combination. I would suggest not using 32-bit binaries/OS when possible for MySQL due to memory limitations, but you say it is

  for testing

So I assume that for a small (< 2GB data) test that is ok?

  Do I only need one VM where both nodes will be installed (I don't know if MySQL supports multiple instances or something like that) or do I need at least two VMs?

For production, 4 nodes are recommended. You can technically run it on 2 nodes -yes, you can run multiple instances of MySQL (and ndbd) on the same physical server, but again, you are doing a test, so depending which part you care to test more or less realistically it will work for you or not-. Network partitions and latency issues, and memory pressure will not show on a single (or just a few), small, machine test. Even for different VMs on the same physical server, you will not get a realistic scenario. But maybe you are not interested on that, and only on learning how to set it up and/or pure controlled switchover tasks.

  I read that NDB cluster require load balancer. Can I do it without load balancer? I mean I will manually run statements (Insert/Update/Delete) on server1 to check if it goes to server2 or not instead of using some PHP application.

Yes, you can do that, but what is the point then, on having a cluster? You want 4 nodes at least (2 SQL and 2 data nodes you can crash at any time and have transparent failover). Why not set up a single Master-slave replication topology (Or master-master, with read only secondary master) in your case?

  Do I need free version of MySQL or some enterprise version?

MySQL has a dual-license model. There is a community version of the NDB Cluster. It is free (no cost) but licensed under GPL, which means it has restrictions on how to use it (linked application must also be GPL). If you are an ISV or a developer shop that has some deliverables to clients, you may need to purchase an Enterprise licence. For testing, where you are not going to distribute the server or the application to anyone, you can use the Community edition. I am not a lawyer, read the license or hire one to read it for you.

  Do I only need MySQL or some other software also needed to setup cluster?

Aside from really considering a load balancing solution (or integrate it on your application), for a production deployment I would say it is a must to have: a monitoring solution (providing trends and alerts), administration tools that make your life easier. Probably the most important part is someone to adapt your application- MySQL cluster is very different from a typical MySQL/InnoDB setup, and in most cases migrating to that is not transparent for how the application access it (it is more memory and key-value focused).

Here you have summary of differences between MySQL NDB Cluster and other similar concepts:

What's the difference between MySQL Fabric and MySQL Cluster


SQL Server's BACKUP DATABASE - how to access the original exception? DECLARE @DbName SYSNAME;
SET @DbName = 'MyDatabase';

BACKUP DATABASE @DbName
    TO DISK = 'C:\MyDatabase.bak'
    WITH INIT,
    NAME = 'MyDatabase-FullBackup',
    STATS = 5;

This, sure enough, is a problematic code, given you're running under NETWORK SERVICE privileges. The output I am getting from it (because I am trying to write to c:\ to which NETWORK SERVICE doesn't have write access) is following when running in SSMS:

  Msg 3201, Level 16, State 1, Line 4
  Cannot open backup device 'C:\MyDatabase.bak'. Operating system error 5(Access is denied.).
  Msg 3013, Level 16, State 1, Line 4
  BACKUP DATABASE is terminating abnormally.

Both surface in SSMS's Query Analyzer output as exceptions, but it looks like (maybe I'm talking gibberish here) that the first filesystem-related one is handled internally by the BACKUP statement which then replaces the message with a more generic "backup is terminating abnormally".

If I then try to handle the exceptions in my script like so

BEGIN TRY

  DECLARE @DbName SYSNAME;
  SET @DbName = 'MyDatabase';

  BACKUP DATABASE @DbName
    TO DISK = 'C:\MyDatabase.bak'
    WITH INIT,
    NAME = 'MyDatabase-FullBackup',
    STATS = 5;

END TRY
BEGIN CATCH
  SELECT  ERROR_NUMBER()
        , ERROR_MESSAGE()
END CATCH

then I am only intercepting (well, in this case only showing with SELECT) the 3013 exception, obviously.

Question: is it at all possible to programmatically access the first exception (as it makes far better sense for logging problems during the script execution)?

Thank you!

You can use extended events to capture all errors in your session (this will include some internal errors in your case that the user doesn't get to see normally).

Use the following code to do this (SQL Server 2008 minimum)

CREATE EVENT SESSION ErrorCatcher ON SERVER
ADD EVENT sqlserver.error_reported (WHERE severity >= 16)
ADD TARGET package0.ring_buffer
WITH (
     EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
     )

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = START

BEGIN TRY
    BACKUP DATABASE MyDatabase
        TO DISK = 'C:\MyDatabase.bak'
        WITH INIT,
        NAME = 'MyDatabase-FullBackup',
        STATS = 5;
END TRY
BEGIN CATCH
    DECLARE @target_data XML

    WAITFOR DELAY '00:00:01' -- Wait for the MAX_DISPATCH_LATENCY time

    SELECT @target_data = CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
       ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'ErrorCatcher'

    SELECT
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh,
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="error"]/value)[1]', 'int') as error,
        n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
        n.value('(data[@name="duration"]/value)[1]', 'int') as state,
        n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
        n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
    FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
END CATCH

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = STOP

DROP EVENT SESSION ErrorCatcher ON SERVER

Of course you can create the event session only once and just start/stop it on demand. The session is persistent and remains on the server until you drop it.

The ring buffer target is resident in memory and with default settings uses up to 4 MB to capture events before it starts deleting old events (on a FIFO basis). The buffer is also released when you stop the session.


Delete word, its meanings, its meaning's example sentences from DB I have three tables as below (simplified for demonstration):

words
=====
integer id
text    word

meanings
========
integer id
integer word_id
text    meaning

examples
========
integer id
integer meaning_id
text    sentence

where, word_id stores id of the word in words table and meaning_id stores id of the meaning in meanings table. I am trying to figure out a sql query, given a word's id, to delete the word with all its meanings and example sentences all at one time. Is such sql query possible to compose? If so, how?

Edit1: I am using SQLite3 as the database.

Edit2: I figured the following solution which requires 3 sql queries in order:

DELETE FROM examples WHERE meaning_id IN (SELECT id FROM meanings WHERE word_id=the_given_id);
DELETE FROM meanings WHERE word_id=the_given_id;
DELETE FROM words WHERE id=the_given_id;

I'm still looking for the answer to my question: is the whole process possible to be done in one query?

Use the cascade deletion.
Does SQLite support ON DELETE CASCADE declaration for FOREIGN KEY?


Can I use dbms_redefinition with bulk data transformations for better results? Here is a situation for bulk loading in Oracle:

Schema A  { T1, T2, T3, T4}
Schema B  { TR1, TR2}

Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of data that need to be loaded into empty schema B. There is both data transformation and data mapping involved between schemas.

Would dbms_redefinition package help here? With my reading, it seems to work to redefine table within the same schema. Not sure if it works in the above situation. Also, how does this approach compare to others like CTAS.

John,
normally it does not make sense to use dbms_redefinition in your case since you are not redefining a table. You are creating an extra schema. dbms_redefinition is meant for situations where for example your table T1 gets an other layout and this has to be done online. Application can stil use the old form of the table while you are completing the new table. This is done by creating materialized view logs.

I hope this helps.


Grouping values into buckets I'm working a query and I wasn't sure if my current approach is the "best" approach.
I got a table filled with values and a table with buckets that I use to group those values.

There are 2 types of buckets:

A priority bucket, if items apply to the conditions they should fall in this bucket and be excluded for any following bucket.
A simple range bucket, the values should fall within a range.

I set up a simplified version that can be used to test.
the [Filter] column in #tempBUcket here is used for the priority buckets

The table definitions

create table #TempData([Id] int, [value] int);
create table #TempBucket([Id] int, [FromVal] int,[ToVal] int,[Filter] int);

Some test data

INSERT INTO #TempData (Id,value)
    VALUES  (1,1),(2,2),(3,3),(4,4),(5,5),
            (6,6),(7,7),(8,8),(9,9),(10,1),
            (11,2),(12,3),(13,4),(14,5),(15,6),
            (16,7),(17,8),(18,9),(19,10),(20,11);
INSERT INTO #TempBucket(Id,FromVal,ToVal,Filter)
    VALUES  (1,0,3,0),(2,3,6,0),
            (3,6,9,0),(4,9,12,0),
            (5,NULL,NULL,1),(6,NULL,NULL,2);

Right now I have this query which works and gives the results I want but I feel this could be done better and more efficiently.

WITH filteredData as
    (SELECT tv.*,COALESCE(tb1.Id,tb2.Id) as BucketId FROM #TempData tv
    LEFT JOIN #TempBucket tb1 --prio bucket join
        ON tb1.Filter IS NOT NULL AND tb1.Filter = tv.Value
    LEFT JOIN #TempBucket tb2 --range bucket join
        ON tb2.Fromval < tv.value AND tv.value <= tb2.ToVal
    )
SELECT Count(fd.value) as count ,BucketId FROM filteredData fd GROUP BY BucketId;

dbfiddle.uk

My solution does only 1 scan of #TempBucket but it adds an additional sort, I'm not sure it's more efficient, you should test it on volumes of data:

;with cte as
(
select t.*, tb.id as BucketId,
       row_number() over(partition by t.id order by tb.FromVal) as rn
from #TempData t
     join #TempBucket tb
         on (t.value = tb.filter)
         or (tb.Fromval < t.value AND t.value <= tb.ToVal)
)
select count(id) as cnt, BucketId
from cte
where rn = 1
group by BucketId;

To give a priority to the row in priority bucket it sorts by tb.FromVal which is null in case of priority bucket, alternatively you can sort on tb.Filter desc, I just don't know the exact criteria of priority bucket whether the FromVal,ToVal are null or Filter = 0


How do I shorten validation time? I have a query I'm using as the source for an OLE DB source component. When I load the package or try to execute the package, the validation on that component takes a very long time. In fact, I left the package overnight and it hadn't finished validating the next morning.

I've found so many (too many) examples of how to delay validation, work offline, etc., but I don't want to workaround the validation. I want to figure out how to make the validation process faster. Currently, when I run the query in SSMS it returns one row (total) in 20 seconds, so why does validation currently run for many hours without completion?

When I run sp_who2 I see that package is executing a select statement against the db with very high CPUTime and low DiskIO (e.g. 390000/300). Is this the issue? If so, how do I correct this or where do need to go to investigate how to fix this? My package is sitting on the same server that the SQL Serve instance is running on, so I can't imagine connection issues are the cause of the validation slowness.

UPDATE

I'm on a tight deadline, so I will have to stop investigating the root cause of this for now. To workaround this, I replaced the Data Flow task where the OLE DB Source component was using this query with an Execute SQL task. I then created a stored procedure that simply does INSERT INTO TableName SELECT Col1,Col2,ect. FROM ViewName. When I run the package now it all runs in a reasonable amount of time like I would expect it to. I'm not going to make this the answer to the question because this still really bothers me, and I'm losing some built in logging functionality in SSIS to do it this way. If anyone knows the asnwer, please let me know!

I think that your OLE DB source is causing the issue

Create a new table with the same schema as your current source and point the component at it.
Within the package, update to your actual source, as this will force it to handle it at run-time.  The easiest way I have found to do this is to create a variable and assign it to the source expression.  See an example of this at https://stackoverflow.com/questions/18288183/parameterized-oledb-source-query

Another possible solution is to set the DelayValidation property of the Data Flow task to True - See https://technet.microsoft.com/en-us/library/aa337088(v=sql.105).aspx#Anchor_4


Does row-based replication with "binlog_row_image=minimal" cause issues when ALTER TABLE reorders columns? Just trying to get my head around the caveats of using binlow_row_image=minimal with row-based replication in MySQL 8.0.23... To avoid silent data drift, the requirements seem to be that:

All tables need a primary key in both databases, which is identical for each pair of tables.
All tables need to have the same number and type of columns, in the correct order, in both databases.

Given the above, if an ALTER TABLE for example deleted a column, could you ever have a race condition where replication of pending binlog events which insert rows into that table would break? Would this only happen if you used something like slave_parallel_workers, or would even that be safe?
I'm trying to figure out if this is worth using or if it's more performance tweak when you don't require hard data consistency.

Parallel replication allows concurrent transactions out of order, but does not allow concurrent non-transactional updates. Certain SQL statements — including DDL statements — cannot be rolled back, so they cause a "barrier" such that they wait until all parallel workers catch up, and workers are blocked from executing changes after the non-transactional change.
This means there's no race condition. DDL effectively forces a kind of "synchronization".


restoring Postgres dump with tablespaces I'm trying to restore a dump done with pg_dumpall > mybak.sql from a server that has 1 table in a different tablespace that the default one.

On the target machine I have recreated the directory structure of the tablespace and it is owned by the postgres user with read/write permissions but still the restore is failing. Any suggestions?

command used to restore is:

psql mydb -f mybak.sql

Thanks

Got it,

had to recreate the tablespace manually on target before restoring the dump.

Thanks anyway


Transaction Log Truncation with Copy Only I have a database backup job set to use full backup with the Copy only option on once daily. The copy only is on because from what I have read that is the only way to backup a database that is connected to an availability group. I have log backups happening every 20 minutes using the same options on the same AG. What is the best practice for truncating the transaction logs after these backups run. The full backup is not truncating since its using copy only the same for the log backups. They are growing out of control. I know i can use DBCC SHRINKFILE but the more i read about it the more dangerous it seems. Is there some other method or best practice?

Any advice is appreciated.

I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, I think they really mean, specifically, log backups (and copy_only backups, if you have some need for those).

You should occasionally run full backups on the primary IMHO. The copy_only restriction is about full backups, not log backups, and only applies to the secondary AFAIK.

You will not be able to use shrinkfile against your current transaction logs because they are full of activity that has technically not been backed up. Once you take a full (non-copy_only) backup on the primary, then let one log backup run, you should be able to shrink the log file manually. Currently your log backups are working because the database is set to full, but they are continuing to grow because (presumably) you've never run a proper full backup on the primary.

This should be a one-time operation, and you shouldn't shrink them too small; you need to set your primary to be backing up regularly, and you need to accommodate for the largest set of activity that will occur between full or log backups. I won't go on and on about how shrinking files only so they can grow again is a futile exercise and guarantees poor performance, but I could. :-)


Selecting default value query returns blank I am trying to select the default value if a row do not have a value with this snippet
1
SELECT IFNULL((SELECT as_title FROM all_scheduled_messages WHERE id = 1 LIMIT 1) ,'lorem ipsum');

When i run the query,it returns a blank and so is when i
2
SELECT as_title FROM all_scheduled_messages WHERE id = 1 LIMIT 1

Why is my first query returning the text i want as default?.

I think you're going about this all wrong.

You want to wrap IFNULL around the actual field.

SELECT IFNULL(as_title, 'lorem ipsum') FROM all_scheduled_messages WHERE id = 1 LIMIT 1;

However, if you're returning a blank, rather than a NULL from your query, IFNULL will not work. You'll need to NULL the value first:

SELECT IFNULL(NULLIF(as_title, ''), 'lorem ipsum') FROM all_scheduled_messages WHERE id = 1 LIMIT 1;


MySQL: Is it possible to run a search on a specific database for a specific string? I need to find a specific email address in a database.

I don't know in which table the email is set so I'd like to run a search on the whole database.
Is that possible?

Thanks

You might be able to query your database with the help of the Needle in a Haystack code.

From the author

  Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Jon's Date of birth is saved. This code is search all the tables and all the rows and columns in a MYSQL Database. The code is written in PHP. For faster result, we are only searching in the varchar field. (if anyone needs to search in other field, he can just comment the varchar selecting part.)

Description

The above mentioned solution is a PHP page which allows you to enter your connection details and then just queries your database for the string.

The PHP program iterates through the tables (show tables;) stores this information in an array and then iterates through the description of each table to retrieve the columns to search.

It then performs a simple SELECT * from $table where $column = $value.

The standard version searches only in varchar fields, but this can be altered in the source code.

  Disclaimer: This is not my work and I am in no way affiliated with the author.


Changing own user permissions with Oracle SQL not being the DBA I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes I'll want, for instance, to copy information from production to a development environment. When doing this kind of stuff, it always scares me to think of what would happen were I to copy or merge or whatever in the wrong direction, then destroying the production database.

I'm currently connecting to the schemas as a regular user with read/write permissions (I'm not the DBA here).

The ideal would be to have some way of being able to decrease in a fine grained way my own permissions (or having some system akin to having to typesudo in Linux. It'd be cool that any kind of write action to DEV would make me have to write my password).

I imagine this is a recurrent situation. How do people generally solve this kind of issues? I could of course ask the DBA to create different users with different kinds of permissions, but that gets tiresome, as their permissions end-up not being fine-grained and there are like 5 or 6 different environments where I'd have to create new users to. Maybe he could grant me rights to create new users (being those users always limited to have in the best case having exactly the same rights as my original user?)

Thanks

You mentioned as one of your examples as "from production to development" .. so I'll start there, and with that, assume that your different "environments" are, in fact, different instances/databases as well.

Always "PULL" data ... rather than "PUSH".
So in other words, login to where you the data will be INSERTED.  That id should have INSERT access to the target tables. That same id should ONLY HAVE READ access to the source (ie production).
You can set that up via a db link or such.

You then

INSERT into <local table>
  select * from <remote table>@dblink;

and you can't go wrong, since you don't have access.

This works for any two environments ... if you setup those db links using READ ONLY ids, and you do the INSERT locally with a user with local INSERT privs .. you're safe.


SELECT LIMIT 1 per column value? Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select one row per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 3         | i hate this   |
| 6         | this is weird |
| 9         | how are you   |
| 16        | too slow      |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary?  Is it possible to somehow use DISTINCT on a single column?

That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id values and for the rest of the columns, you can (have to) use aggregate functions like MIN(), MAX(), AVG(), SUM() as you will have more than one values per group and only one can be shown.

SELECT
    user_id
  , MIN(comment) AS comment  -- it will show the first in alphabetical order
                             -- you could also use MAX()
FROM
    tableX
GROUP BY
    user_id ;

MySQL allows also the following unorthodox solution, that will return one (more or less random) comment per user:

SELECT
    user_id
  , comment
FROM
    tableX
GROUP BY
    user_id ;

This last query will not work but raise an error if the (stricter) ONLY_FULL_GROUP_BY mode is enabled. In the recently released 5.7 version, this mode is the default and a new function, ANY_VALUE(), is provided. For more details, see the MySQL Handling of GROUP BY page. The query can be written now:

SELECT
    user_id
  , ANY_VALUE(comment) AS comment
FROM
    tableX
GROUP BY
    user_id ;

Note that with either the "unorthodox" version or using the recent ANY_VALUE() function, if we add more columns in the SELECT list, their values is not guaranteed to be from the same row, just from a row in the same group. The way they are selected is not exactly random, depends on the execution plan and the indexes used.


In postgresql binary replication, indexes are replicated or not? And if i dont like it? I want to know if indexes are replicated in a binary replication in postgresql.

And if it is, how can i disable this behavior?

So if slave becomes master i can start to index it using reindex?

The postgres replication works at the WAL (Write-Ahead-Log) level, which basically contains a copy of each disk block that is modified. The slave just streams the WAL records and play them back. So, everything that modifies the disk, which includes indexes, are replicated.

This also means that the slave will use exactly same amount of disk space (for the database) as that of the master, and will use exactly the same query plan as that of the master etc.

It is not possible to change that behavior as long as you are using postgres in-built replication.

When the slave is promoted as master, there is no need to run reindex as index is already available.


PostgreSQL: Save roundtrips / Network performance For my work I often have to to large selects loading a lot of data. The connection happens with JDBC driver and high fetch size. This is not a problem when working directly on the server of the database. However when connecting from a different machine, the load times get terrible and it takes >10x times longer to load the data.

Note that the amount of data is actually small (100s of MB, 200k rows) and the network bandwidth can't be the limiting factor. Therefore it's almost certainly latency and hence round trips that cause the bad performance.

Latency issue seem confirmed by checking with wireshark and I see a huge back and forth between the client and server. (note both windows). Similar what was observed in this old topic here.

Are there any server or client side (JDBC) settings that would reduce roundtrips and lower the latency costs?

EDIT:

On same machine:
Remote: 20 min to load data (ping ~120ms)
local: 50 seconds (ping < 1ms)

where local means intranet and db server is in a building about 300 feet away. And on the actual server it's also 50 seconds. Explain analyze says 105ms.

I do understand the issue I simply wasn't aware the impact is that big. I also wonder if it's a windows issue as it seems there are far too many ack after very few data when connecting from remote. I should probably ask about that in a network forum.

there is nothing on PostgreSQL side to deal with this.

JDBC side i know nothing about but quick google search points to no

A solution i use when working with large data sets is to fetch only a few records at a time  1000 to 10000.

Select * from tablename where tablename.id > 1 ordered by id limit 1000

next set of queries

Select * from tablename where tablename.id > 1000 ordered by id limit 1000

Some people call this  asynchronous  fetching/paging.

Some background
Most db connections run in synchronize mode by default, "command sent"  client waits until all data is delivered.  This makes the app appear to hang for long periods of time, this makes the users think the app has crashed or something else has gone wrong.

Using asynchronous technique the data is continuously fetch and  delivered using a background process/thread while the rest of the code is free to continue working giving the user the appearance the application is very snappy.

Its allot harder to write code around fetching only a few records at a time but can be  worth it on big data sets, as the app does not have to wait on all the data

There are several tools to make this easier,  such as creating cursor on the server and fetch only X records from the server. But there are draw backs to cursors,  eating server resources,  required to run clean up code.

Some client libraries makes this easy by directly supporting cursor other do not support this feature  JDBC does not appear to support this so it will have to be written.


Checking if an Username is already in use I am writing some scripts for my website, and I am currently focussing on checking if a username is already in use. I have tried writing a SELECT script where the datafield is selected and checks if the POSTed data matches.

Here is my code:

$username = $_POST["username"];
$password = $_POST["password"];

{ // Check if account already exists

        $Select_Account = "SELECT ";
        $Select_Account .= "Username ";
        $Select_Account .= "FROM UserData ";
        $Select_Account .= "WHERE UserData.Username ";
        $Select_Account .= "= '$username'";

        $Select_Account_Query = mysql_query($Select_Account);

        if ($Select_Account_Query) {
            echo "Account Exists";
        } else {
            echo "Account does not exist";
        }

    }

I cannot figure out the issue that is preventing the code from ECHOing "Account does not exist" when a value that it inputted is not inside of the table.

Please be aware that I am quite new to writing PHP and MySQL, so I am not fully up to scratch with all the queries and the syntax yet. All help will be greatly appreciated.

mysql_query() returns false only when there is something wrong with your query and it fails to execute. Otherwise, the return value is a resource which evaluates to success. That is your code always echoes "Account Exists". Check for the number of rows the SELECT statement fetched to determine whether the user exists or not.

Your code is also not secure. It is vulnerable to SQL injection. I would highly recommend NOT using mysql_* family of PHP functions. They are deprecated as of PHP 5.5.0 and will be removed in future releases. Consider switching over to mysqli_* family of functions or better use parameterized queries with PDO.

PDO:

$conn = new PDO('mysql:host='.$dbhost.';dbname='.$dbname,
                 $dbusername,
                 $dbpassword);

$username = $_POST["username"];
$password = $_POST["password"];

//Query statement with placeholder
$query = "SELECT Username
          FROM Userdata
          WHERE Userdata.Username = ?";

//Put the parameters in an array
$params = array($username);

//Execute it
try {
    $stmt = $conn->prepare($query);
    $result = $stmt->execute($params);
} catch(PDOException $ex) {
    echo $ex->getMessage());
}

//Now Check for the row count
if($stmt->rowCount === 1) {
    echo "Account Exists";
} else {
    echo "Account does not exist";
}


Increasing tempdb data files on 2 node Availability Group I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files will be subject to PREEMPTIVE_OS_WRITEFILEGATHER waits for up to 30 minutes (based on testing).

I am concerned that these waits will affect the application/user experience so here is my proposal.

Make the change on Node B
Disable synchronisation
Failover to Node B on successful completion
Make the change on Node A
Fail back to Node A on successful completion
Enable synchronisation.

Does anyone see any issues with this ?

Thanks

The steps you provide are correct except for the "disable synchronization" and "enable synchronization," which are not needed.  If you pause synchronization prior to failing over, you may have some data loss.

The stopping/starting of synchronization occurs automatically when failover is performed, regardless of whether it is automatic or manual.  So the process would be:

Assuming node B is the secondary, make the tempdb modifications on node B
Fail over to node B
Make the tempdb modifications on node A
Fail back to node A

If you are concerned that the tempdb changes might cause issues with replication to the secondary, and thereby affect commit times on the primary, you could change the sync mode to asynchronous to accommodate:

Assuming node B is the secondary, configure node B as an asynchronous replica
Make the tempdb modifications on node B
Configure node B as a synchronous replica and ensure that replication is caught up
Fail over to node B
Configure node A as an asynchronous replica
Make the tempdb modifications on node A
Configure node A as an synchronous replica and ensure that replication is caught up
Fail back to node A

I think this is overkill as I don't believe tempdb is used in the replication process, but I can't find an authoritative answer that states that explicitly.


Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits I'm running Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) on a 4 vCPU VM with max degree of parallelism set to 2 and cost threshold for parallelism set to 50.

In the mornings, when trying to display an Estimated Execution Plan for a SELECT TOP 100 query, I run into massive waits and the operation to render the estimated plan takes minutes, often times in the 5 - 7 minute range.  Again, this is not the actual execution of the query, this is just the process to display an Estimated Execution Plan.

sp_WhoIsActive will show either PAGEIOLATCH_SH waits or LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits and when I run Paul Randal's WaitingTasks.sql script during the operation it shows CXPACKET waits with the worker threads showing PAGEIOLATCH_SH waits:

*resource description field = exchangeEvent id=Port5f6069e600 WaitType=e_waitPortOpen waiterType=Coordinator nodeId=1 tid=0 ownerActivity=notYetOpened waiterActivity=waitForAllOwnersToOpen

The worker threads look to be bringing the entire stats table into memory (as those page numbers as well as subsequent page numbers shown from Paul Randal's query point back to clustered key for the stats table).  Once the plan does come back, it's basically instantaneous for the remainder of the day, even after I see most of the stats table attrition from cache with only various records remaining (that I assume were pulled due to seek operations from similar queries).

I would expect this initial behavior if the query was actually executing with a plan that used SCAN operators, but why is it doing this when evaluating execution plans only to arrive at a SEEK operator as shown in the plan linked above?  What can I do (aside from running this statement before office hours so my data is appropriately cached) to help improve performance here?  I'm assuming a pair of covering indexes would be beneficial, but would they really guarantee any changes in behavior?  I have to work within some storage and maintenance window limitations here, and the query itself is generated from a vendor solution, so any other suggestions (besides better indexing) would be welcome at this point.

It appears your request for an actual execution plan triggered stats updates.  Since you mention this happens in the mornings, I imagine there's an overnight process that does a lot of modifications to the tables involved?

Thus SQL Server uses the stats to create the plan, has hit the modification threshold, and executes automatic stats updates as part of the operation.

In the XML for the estimated plan you shared, I see these close-together update dates for stats from this morning:

LastUpdate="2019-05-06T09:12:49.92"
LastUpdate="2019-05-06T09:12:58.3"
LastUpdate="2019-05-06T09:13:20.33"
LastUpdate="2019-05-06T09:13:09.67"
LastUpdate="2019-05-06T09:12:59.05"
LastUpdate="2019-05-06T09:12:39.56"

If these are very large, busy tables (seems likely based on the sampling percentages), then it's not too surprising that the stats updates are taking a lot of horsepower.


how determine the best path that satisfies a condition declare @flat table
(
    id int not null,
    percentage int not null,
    value float not null
)

insert into @flat (id, percentage, value)
select 1,10,0.333
union all
select 1,20,0.4
union all
select 2, 50, 0.5
union all
select 2, 70, 0.67
union all
select 3, 10, 0.125
union all
select 3, 40, 0.325

having this sample data, how could i determine the rows that maximize the sum of the values ?
Having the following conditions:

one percentage per id only
the sum of the percentages must be 100

there is two possible solutions:

-- id percentage  value     sum
--  1     10      0.333    0.333
--  2     50      0.5      0.833
--  3     40      0.325    1.158

-- id percentage  value     sum
--  1     20      0.4       0.4
--  2     70      0.67      1.070
--  3     10      0.125     1.195

but it matters only the last one.

Congratulations, you have a subset sum problem! It's an NP-complete problem. Arriving at the solution is going to call for brute force. i.e. Generate and test all possible subsets. WARNING: This may involve the sketchy Cartesian products your mother warned you about.

Depending on the size of your actual data domain, in SQL that is going to range from "computationally expensive but it works" at best to "who jammed the kernel to 100% and crashed the database server?" at worst.

As a computationally-intensive problem, it is more advisable to solve it in a dynamic programming language than in a SQL DBMS.


Moving mysql union query to postgresql I am trying to move a mysql query to postgresql and I have a problem with the union operation, this is the query:

SELECT
          "user_firstname"
        , "user_lastname"
        , "course_fullname"
        , "course_completion_completeddate"
        , "course_completion_status"
        , "ESR Employee Number"
        , "DH Job Role"
        , "Department"
        from mdl_course_completions
        union
        SELECT
          auser.firstname AS user_firstname,
          auser.lastname AS user_lastname,
          course.fullname AS course_fullname,
          from_unixtime(base.timecompleted) AS course_completion_completeddate,
          CASE WHEN base.status = 50 OR base.status = 75 THEN 'Complete' END AS course_completion_status,
          COALESCE(user_9.data,'') AS user_custom_field_9,
          COALESCE(user_5.data,'') AS user_custom_field_5,
          COALESCE(user_49.data,'') AS user_department
        FROM mdl_course_completions base
         LEFT JOIN mdl_user auser
             ON auser.id = base.userid
         LEFT JOIN mdl_course course
             ON course.id = base.course
         LEFT JOIN mdl_user_info_data user_9
            ON user_9.userid = auser.id AND user_9.fieldid = 9
         LEFT JOIN mdl_user_info_data user_5
            ON user_5.userid = auser.id AND user_5.fieldid = 5
          LEFT JOIN mdl_user_info_data user_49
            ON user_49.userid = auser.id AND user_49.fieldid = 49
        WHERE
            (base.timecompleted > 1485953790
              and
            base.timecompleted < 1486040190
              and
            base.timecompleted IS NOT NULL)
              and
            course.id = 1730
              and
            base.status  IN (50,75)
        ;

The only function doesn't work is from_unixtime, but I changed for to_timestamp and it's ok, the problem is when I execute the query I get this error:

ERROR:  column "user_firstname" does not exist
LINE 2:           "user_firstname"

And I really don't understand why this runs with MySQL but does not with Postgres. Maybe I need to change it to join, so, any ideas?

The issue is relevant to this whole part and not just  "user_firstname".

SELECT
          "user_firstname"
        , "user_lastname"
        , "course_fullname"
        , "course_completion_completeddate"
        , "course_completion_status"
        , "ESR Employee Number"
        , "DH Job Role"
        , "Department"
        from mdl_course_completions

In MySQL "user_firstname" is interpreted as a string (which probably wasn't you intention)
In PostgreSQL "user_firstname" is interpreted as a column name.

Demo

create table t (x int);
insert into t (x) values (1),(2),(3);

select "x" as col from t;

PostgreSQL

+---+
|col|
+---+
| 1 |
+---+
| 2 |
+---+
| 3 |
+---+

MySQL

+---+
|col|
+---+
| x |
+---+
| x |
+---+
| x |
+---+


After upgrade from 9.1 to 9.3, commands still point to 9.1 Upgraded from 9.1 to 9.3 on my Ubuntu 12.04 server. Everything worked great but running commands like 'createdb' still runs them from the 9.1 path.

/usr/lib/postgresql/9.1/bin/createdb: unrecognized option....

Edit, additional info:
I used this gist https://gist.github.com/ibussieres/11262268
I already uninstalled 9.1 with the regular 'sudo apt-get remove postgresql-9.1'

This is the output of requested commands

which createdb
  /usr/bin/createdb

createdb --version
  createdb (PostgreSQL) 9.1.14

which psql
  /usr/bin/psql

psql --version
  psql (PostgreSQL) 9.3.5

Option 1: remove createdb for 9.1

When in doubt, use dpkg -S to learn which packages provide a certain command.

Example:

$ dpkg -S createdb
postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/tutorial-createdb.html
postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/app-createdb.html
postgresql-client-9.1: /usr/lib/postgresql/9.1/bin/createdb
postgresql-client-common: /usr/bin/createdb
postgresql-doc-9.1: /usr/share/doc/postgresql-doc-9.1/html/manage-ag-createdb.html
postgresql-client-9.1: /usr/share/postgresql/9.1/man/man1/createdb.1.gz
wwwconfig-common: /usr/share/wwwconfig-common/pgsql-createdb.sh

From this it appears that /usr/lib/postgresql/9.1/bin/createdb is provided by postgresql-client-9.1, and that it's still installed. You may uninstall it since presumably you have now postgresql-client-9.3

Option 2: address the right instance without removing anything

Debian allows multiple simultaneous PostgreSQL instances, which is why it allows to have different createdb at the same time. Assuming you'd need to keep postgresql-client-9.1, that is possible too.

The /usr/bin/createdb command, in the standard $PATH (as opposed to deep under /usr/lib), is actually a wrapper that will call the real binary for the correct version depending on the context.

That context may be given through the additional option, specific to Debian/Ubuntu, called --cluster to specify which version and server it should target. It's also available as an environment variable PGCLUSTER, as documented in pg_wrapper, and defaults can also be specified in /etc/postgresql-common/user_clusters.

Weird: createdb pointing to 9.1.14 and psql to 9.3.5

The one thing that doesn't look right in your question is this issue.
Normally pgsql and createdb should reach the same version/cluster, since in fact they should point to the same script to start with:

$ ls -l /usr/bin/createdb /usr/bin/psql
lrwxrwxrwx 1 root root 37 sept.  8  2012 /usr/bin/createdb -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root 37 sept.  8  2012 /usr/bin/psql -> ../share/postgresql-common/pg_wrapper

This would require further digging into the environment to find out how your situation differs from the default.


Import data into MySQL using Talend Open Studio I have been trying to import CSV data about 60 MB; it contains about 400,000 rows, which should not be too much.  I tried these ways to import the data:

MySQL Workbench - it took about two hours!!!
Using MySQL LOAD INFILE command - it took about 15 seconds!
Using Talend Open Studio - almost as long as Workbench.

Why are the GUI tools working so badly?  I want to use GUI-based tools, instead of writing scripts.

With Talend Open Studio, is there a way to speed things up?  I heard that there is possibly another Talend product that deals with Big Data, will this do it faster than Talend Open Studio?

Hope someone out there knows!

I know that in Hadoop using Pig, I can get the the CSV from HDFS into Pig memory very quickly.

What parameters are You use for Talend tMySQLOutput? What MySQL settings?
It could not be faster then INFILE, but still really depend from settings.

You can post Your question on TelndForge forum with screen short of Job components, will try to help

For example one of my regular Talend-MySQL Jobs (billing prepare) - transfer from->to mysql 2M of records, 400Mb, it take 6-15 minutes depending from servers (we have 2 configuration)

edit, because always better 1 time to show, than 100 times explain

2.3M rows, 300Mb file, speed of tMySQL with 100 rows per insert:

same with 10 000 rows per insert

tBulkOutputExec:

but then total time:
original 10 000 rows:

bulk:

just because after execution it wait commit transaction, single transaction


Variable problems in PostgreSQL with language plpython3u I am setting up a function where recursive action is relevant. The function basically maps two floats  according to a rule and then returns a string. The code below is stripped for the actual function, but it should serve as a relevant code example (full code available). The original code (python 2.7) works fine, so I am thinking that there is something about the language, plpython3u, used in PostgreSQL which messes it up.
I imagine this is related to local variables and global variables. I would have thought the variables remained local, but it does not look that way. Is there a way to work around this?
Running the function I get an error message indicating that a variable has already been
ERROR:  UnboundLocalError: local variable 'square' referenced before assignment
CONTEXT:  Traceback (most recent call last):
  PL/Python function "qdgc_get_recursivestring", line 3, in <module>
    square ='ABC'+square
PL/Python function "qdgc_get_recursivestring"
SQL state: 38000

Function:
CREATE OR REPLACE FUNCTION public.qdgc_get_recursivestring(
    lon_value double precision,
    lat_value double precision,
    depthlevel integer,
    square text)
    RETURNS text
    LANGUAGE 'plpython3u'
    COST 100
    VOLATILE PARALLEL UNSAFE

AS $BODY$

square ='ABC'+square

depthlevel = depthlevel -1

if depthlevel<1:
    return square
else:
    return qdgc_get_recursivestring(lon_value,lat_value,depthlevel,square)

$BODY$;

It is necessary to declare a variable within the function. Add "global square" here:
[..]
AS $BODY$

global square

square ='ABC'+square
[..]

You will have to do the same for depthlevel.


MySQL: Procedure for mysql certification I have keen interest in database and since start of my career I worked only in MySQL. But, I have not any professional certification related with MySQL. I have below concerns for certification:

Any minimum eligibility like education or experience
What are the similarities and differences between MySQL certification and MySQL DBA certification?
What is the procedure for registration and appearing in the exam?
What will be the total cost in India?
How much beneficial it would be in my career?
Recommend few good tutorials or documentations or Books for this certification?

Your suggestion would be highly appreciated...

MySQL certification, exam, course & cost details can all be found on the Oracle Education site here (Indian site linked to):

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=458&get_params=p_track_id:MYSQLDBA56OCP

The certification process is simple enough to become a MySQL Certified Associate or Professional.  You just need to pass the relevant exams (e.g. for Oracle Certified Professional, MySQL 5.6 Database Administrator, you have to pass 1Z0-883 / MySQL 5.6 Database Administrator).

That's it.

For the Expert certification status things are a bit more complex, but check out the Oracle Education site linked above.

The cost is high if you do the courses and the exams.  If you're confident in your abilities and have lots of experience, you can do the exams without having done the courses, but in my experience, doing the course is a good grounding and they will cover everything that the exam requires.

How beneficial?  Well that depends on the job market, employer's requirements and your other skills.  Obviously a certification separates you from people who don't have one, but that's all you can realistically say.  There will be non-certified people with more experience than you.


How to solve repetitive ORA-2391 error in DBeaver 4.3.3.1 I can't seem to find the way to avoid ORA-2391 "exceeded simultaneous SESSIONS_PER_USER limit" when working with DBeaver 4.3.3.1. Everytime my session goes idle and I need to recconect I'm getting this error. I'm not sure if this is a DBeaver issue or I am missing something in my Oracle connection configuration.

I already talked with DBA and he showed me that whenever I connect to my database DBeaver creates two sessions (two rows in table v$session) and when he tries to kill them, they hang out for a while until they finally go away and I can connect again.

I am sure I don't have any other session opened with any other tool.

My goal is to keep working with DBeaver since this tool has some amazing features, but without having to bother my DBA everytime I can't connect due to simultaneous sessions.

If there could be any way to stop DBeaver from creating more than one session for just one connection, I will appreciate any help.

PS:(I hope I'm clear with my question, English is not my native language)

I found the solution thanks to Balazs. There are a couple of options in the Preferences window that could generate multiple sessions.

The first one is at Preferences > General > Editors > SQL Editor, Connections group and "Open separate connection for each editor" option.
The other one is at Preferences > Database > Metadata, Metadata group and "Open separate connection for metadata read" option.

Both options must be unchecked in order to avoid creation of multiple sessions for same connection, therefore to avoid ORA-02391 error.


mongorestore Too many open files When I run mongorestore I get error running create command: 24: Too many open files.

I've updated my launchctl limit and ulimit.

When I run lanchctl limit I get:

cpu         unlimited      unlimited
filesize    unlimited      unlimited
data        unlimited      unlimited
stack       8388608        67104768
core        0              unlimited
rss         unlimited      unlimited
memlock     unlimited      unlimited
maxproc     709            1064
maxfiles    256000         256000

When I run ulimit -a I get:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 256000
pipe size            (512 bytes, -p) 1
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 709
virtual memory          (kbytes, -v) unlimited

My mongo version is 3.4.7

My macOS is 10.13.2

How can I complete mongorestore?

Also worth mentioning: if I try running mongorestore more than once without logging out of the computer or restarting, I get cannot make pipe for command substitution: Too many open files and my computer crashes (This was a brand new iMac as of August 2017).

ulimit can be set in the same terminal by
ulimit -n 10000;

You can change the value in /etc/security/limit.conf file by
nofiles to 10000 #line

the maximum no of files can be opened in any linux/unix system is 64000. be sure of this no. If your restoring of database size is huge then try to make them smaller pieces like create the database and then try to restore one collection or few collections at a time. If you have memory limit like 4/8 GB of RAM then it is better to restore collection by collection instead of whole database.

As you are trying mongorestore from local system I believe this is not a production system and not a serious business, if my assumption is correct please try to restore few collections at a time and see if you still face the same issue.

Thanks @wylliam-judd for sharing the solution; I didn't face this issue as I used linux as a remote host and accessed from putty and in the same window; anyway I'm adding your solution to help others

The solution was to run ulimit -n 64000 in the console running mongod rather than in the console running mongorestore. Though I did run it in both consoles to be safe, and I can't be sure whether the ulimit in the mongorestore console was necessary or not. I also attempted many other solutions in these months, and I can't be entirely sure that none of them had an impact, but the difference that finally mattered was running ulimit in the console running mongod.


SQL Server Express vs. Oracle Express Edition vs DB2 Express-C I have been trying to compare the features of the SQL Server Express, Oracle XE and DB2 Express-C, but find it difficult to compile all the differences into one place.  Especially the current differences as this is a constantly shifting battle field.  Anyone have this information on hand or know of someone who tracks this?  To the best of my knowledge:

DB2 Express-C (10.5)

cores: 2
mem: 16G (4G before 10.5)
size: 15 terabytes of user data per database

Oracle Express Edition (11g)

cores: 1
mem: 1G
size: 11G

SQL Server Express (2012)

cores: lesser of 1 socket or 4 cores
mem: 1G
size: 10G

Other than these obvious measures, what am I missing?  I know I could go with open source as well, and MySQL and PostgreSQL are both certainly viable solutions, but for the sake of argument, lets limit it to just these 3 options.  In my view, DB2 is the clear cut winner.

Another article on this subject can be found here.

I think you are missing couple important parameters. If you believe the project will advance , you have to keep in mind that one day you will need to switch to non-express version. And at that point license cost becomes very important; switching to another RDMS may require serious refactoring (and usually it does). For startup project I'd not pay too much attention to hardware configuration supported in express version, I'm pretty sure even the lowest will work fine. More important thing in my opinion is DBA's skills; in practice, not so many database professionals are equally good in all RDMS. So ask your DBA which server she/he would prefer before making a decision.


Why isn't the ASYMKEY_ID function returning the Asymmetric Key ID? I have created database master key. Then I have created asymmetric key:

CREATE ASYMMETRIC KEY smGK_АSymmetricKey_01
WITH ALGORITHM = RSA_512

In the documentation is said, that:

  PASSWORD = 'password'

  Specifies the password with which to encrypt the
  private key. If this clause is not present, the private key will be
  encrypted with the database master key.

So, the key should be encrypted with the database master key. Then, why the following is not working:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass'

SELECT [Email], ASYMKEY_ID('smGK_АSymmetricKey_01'), EncryptByAsymKey(AsymKey_ID('smGK_АSymmetricKey_01'), [Email])
FROM  myTable

CLOSE MASTER KEY

and returns NULL for the second and the third column.

Also, why the EncryptByAsymKey and DeCryptByAsymKey functions works with the same key ID? I think that when asymmetric key is used, I will grant to specific users to control the public key and to be able to decrypt data and other user to control the private key and to be the only one who can read it?

If this is not the case and only one group of users will be able to encrypt - decrypt the data, then as the example here, there is no logical difference between the two type of keys (only in terms of performance).

Note:

The key is created for sure:

SELECT *
FROM sys.asymmetric_keys;

I have try to create the key using PASSWORD but my code is still not working.

Why is this not working?

The AsymKey_ID function is returning NULL because it cannot find that particular Asymmetric Key. I also got a NULL until I prefixed the literal string with an N. So the following should work:

SELECT [Email],
       ASYMKEY_ID(N'smGK_АSymmetricKey_01'),
       EncryptByAsymKey(AsymKey_ID(N'smGK_АSymmetricKey_01'), [Email])
FROM   myTable;

The root cause of this issue is that you have a character in the key name that does not cleanly translate into ASCII (for the Code Page used by the default Collation of the Database that this is running in). In order to see the issue more clearly, run the following:

SELECT 'smGK_АSymmetricKey_01';
-- smGK_?SymmetricKey_01

Now that we know which character is causing the issue, we can copy/paste it by itself into the UNICODE() built-in function to see its value, and converting that decimal value into binary/hex will give us the Code Point (remember to include the N prefix on the literal so that it doesn't get translated into "?"):

SELECT UNICODE(N'А') AS [Decimal], CONVERT(BINARY(2), UNICODE(N'А')) AS [CodePoint];
-- 1040 0x0410

We can then look up that Code Point at http://unicode-table.com/en/0410/ to see that it is "Cyrillic Capital Letter A". This means that a second solution to this issue is to drop and recreate the Asymmetric Key with a Latin Capital "A" (i.e. CHAR(65) ) as that would not require the N prefix on the string literal.

ALSO, you do not need the OPEN MASTER KEY... / CLOSE MASTER KEY statements, at least not to encrypt with an Asymmetric Key; the single statement shown above will work without the Open and Close. Not sure about when using DECRYPTBYASYMKEY. The Open / Close is needed, however, with a Symmetric Key.

  why the EncryptByAsymKey and DeCryptByAsymKey functions works with the same key ID? I think that when asymmetric key is used, I will grant to specific users to control the public key and ...

These two functions use the same AsymKeyID because that is how they are supposed to work. "Controlling" a public key does not make sense because it is "public". The whole idea is that knowledge of the "public" key does not hurt you because it cannot be used to decrypt the data. This is why people sometimes put their PGP public key in their email signature (or at least used to). This allows for anyone to encrypt data so that only those who have the "private" key will be able to read it. This is why you don't need an open Key or even specify a password when encrypting via ENCRYPTBYASYMKEY. Also, the public_key column in sys.asymmetric_keys is available to anyone who can SELECT from that system catalog view.

  If this is not the case and only one group of users will be able to encrypt - decrypt the data, then there is no logical difference between the two type of keys (only in terms of performance).

Again, anyone (who has rights to see the key **) can encrypt, but only those with access to the password of either the Database Master Key OR the Asymmetric Key itself (depending on which one the Asymmetric Key was protected with) will be able to decrypt. So you do have separation.

There is also a rather large difference between Symmetric and Asymmetric encyption, besides Symmetric being much faster than Asymmetric:

When using a Symmetric Key, it needs to be "open" in order to encrypt or decrypt, not just decrypt. Hence, no separation.
Symmetric encryption is vulnerable to "Whole-value substitution" attacks. The following example is taken from the MSDN page for ENCRYPTBYKEY:

  Employee_ID        Standard_Title             Base_Pay
  345                Copy Room Assistant        Fskj%7^edhn00
  697                Chief Financial Officer    M0x8900f56543
  694                Data Entry Supervisor      Cvc97824%^34f

  Without breaking the encryption, a malicious user can infer significant information from the context in which the ciphertext is stored. Because a Chief Financial Officer is paid more than a Copy Room Assistant, it follows that the value encrypted as M0x8900f56543 must be greater than the value encrypted as Fskj%7^edhn00. If so, any user with ALTER permission on the table can give the Copy Room Assistant a raise by replacing the data in his Base_Pay field with a copy of the data stored in the Base_Pay field of the Chief Financial Officer. This whole-value substitution attack bypasses encryption altogether.

  Whole-value substitution attacks can be thwarted by adding contextual information to the plaintext before encrypting it. This contextual information is used to verify that the plaintext data has not been moved.

  If an authenticator parameter is specified when data is encrypted, the same authenticator is required to decrypt the data by using DecryptByKey. ...

** The O.P. pointed out in a comment on this answer that Users need meta-data visibility rights to an Asymmetric Key in order to see it and use it. This can be done by:

being in a role that implicitly has such rights
being granted a Database-level permission that implicitly has such rights
being granted any of the Asymmetric Key permissions on a particular Asymmetric Key:

GRANT VIEW DEFINITION ON ASYMMETRIC KEY::{asym_key_name} TO [{user_name}];

Please see the MSDN page for GRANT Asymmetric Key Permissions for full details.

Not having such rights would be another possible reason for the ASYMKEY_ID built-in function returning NULL. And because Asymmetric Keys can be "hidden" from Users, the ability to encrypt using one or more keys can be prohibited.


Database design for products with bundles of products I am building a database system for my retail business. I have set some tables which are:

Product
Purchase
Sales
Balance

All are connected one another and are able to show my inventory level.

The problem I am having is I also sell bundles of products - that have different prices than their individual prices.
Example: I sell an orange for $1, an apple for $1.2; I sell fruit package 1 (2 oranges and 2 apples) for $3.8, package 2 (4 oranges and 4 apples) for $7.

Is there a right way how to create relationship for these product bundles?

PS: I am using FileMaker Pro creating this.

The pattern you are describing is often called a "parts explosion" or "bill of materials."  It is part of the graphs and trees portion in the study of data structures.  The essence of the solution is to realize that any given "product" can be made up of other "products."  The design is then a network structure where there is a Product table that has a row for each product - whether it is made up of other products or not, and then a Product Component table that has a row for each product that is made up of other products and each corresponding product that is a component of that product.  In your case, each product has a price.  So you would have something like this

Product
-----------------------------------
|Name             |Price          |
-----------------------------------
|Orange           |1             |
|Apple            |1.20          |
|Fruit Package    |3.80          |
-----------------------------------

Product Component
----------------------------------------------------------
|Product               |Contains                |Quantity|
----------------------------------------------------------
|Fruit Package         |Orange                  |2       |
|Fruit Package         |Apple                   |2       |
----------------------------------------------------------

This design is preferable to a single table with a recursive association as it cleanly separates what are really two entity types - nodes and links.  In our case, the products are the nodes, and the product components are the links.

While the network design is a common structure, querying it is problematic as when completely filled it is a recursive structure of varying depth.  Industrial strength DBMS' such as Oracle and SQL Server have special language elements (Oracle's CONNECT BY and SQL Server's recursive CTE) to aid in making the query declarative.  Given you are using File Maker Pro, which I know little about, you may not have such language constructs to help and may have to write procedure code to traverse the network.  This issue can be eased however if the network turns out to be of fixed depth - say every product has either no components, or one level of components.  Here are some references with regard to network structures in database design:

Practical Issues in Database Management - Fabian Pascal.  Chapter 7 provides the best and most understandable explanation I have found.
Joe Celko's Trees and Hierarchies in SQL for Smarties, Second Edition.  This is an entire book on the topic specific to the SQL standard.
Enterprise Model Patterns - David Hay.  A book about patterns common to all organizations (unfortunately the ER Diagrams are presented in UML but that can be overcome) there are several examples of network structures.


import csv file into mysql with custom data change I have a .csv file contains data like this:

Username   Password   Email
test123    123        test@foobar.com

I'm currently using MySQL, and I have a table named user with some columns like this:

UserID    Username    Password    Email

What i'm looking for is how can I import my csv data into user table and convert password column with MD5 method so what I have at the last is

UserID    Username    Password                          Email
1          test123     202cb962ac59075b964b07152d234b70  test@foobar.com

NB: I have a hundred record in my csv file.
Any help please. Thanks for advance.

What's below works for your data - be careful of any variation in the field width - that's why delimited files are better - csv, or better still, if you can get it, pipe-delimited (|). It also successfully deals with the header line.

I did the following:

My code will do for both MD5 and SHA2 passwords - uncomment the relevant bits.

CREATE TABLE my_user
(
  user_id int not null auto_increment primary key,
  username varchar(11),
  p_word varchar(64), -- VARCHAR(32) for MD5
  e_mail varchar(75)
);

using a sample based on your data (users.csv)

$> more users.csv;
Username   Password   Email
test123    123        test@foobar.com
test456    567        sdfsd@blah.com
$>

And then after some Googling, I came up with this

LOAD DATA
INFILE '/mysql/mdb5.5/inst/users.csv'
INTO TABLE my_user
IGNORE 1 LINES
(@row)
SET username = TRIM(SUBSTR(@row,  1, 11)),
    -- p_word   = MD5(TRIM(SUBSTR(@row, 12, 22))),
    p_word = SHA2(TRIM(SUBSTR(@row, 12, 11)), 256),
    e_mail  = TRIM(SUBSTR(@row, 23, 80))
;

And voilà!

MariaDB [test]> select * from my_user;
+---------+----------+------------------------------------------------------------------+-----------------+
| user_id | username | p_word                                                           | e_mail          |
+---------+----------+------------------------------------------------------------------+-----------------+
|       1 | test123  | a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3 | test@foobar.com |
|       2 | test456  | 97a6d21df7c51e8289ac1a8c026aaac143e15aa1957f54f42e30d8f8a85c3a55 | sdfsd@blah.com  |
+---------+----------+------------------------------------------------------------------+-----------------+
2 rows in set (0.01 sec)


MongoDB MMS replica set fails to restart I have a MMS replica set deployed with 3 instances. It was working fine until I shut it down this morning in order to do some changes and restarted it. From logs, it feels that there was an unclean shutdown of replica set. The mongod server on primary is failing to start now.

Here are the full logs

2015-08-20T07:08:41.389+0000 W -        [initandlisten] Detected unclean shutdown - /data/XXXXXXXXXXXX/mongod.lock is not empty.
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] journal dir=/data/XXXXXXXXXXXXX/journal
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] recover begin
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] info no lsn file in journal/ directory
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] recover lsn: 0
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] recover /data/XXXXXXXXXXXX/journal/j._0
2015-08-20T07:08:41.407+0000 I JOURNAL  [initandlisten] recover cleaning up
2015-08-20T07:08:41.407+0000 I JOURNAL  [initandlisten] removeJournalFiles
2015-08-20T07:08:41.641+0000 I JOURNAL  [initandlisten] recover done
2015-08-20T07:08:41.641+0000 I JOURNAL  [initandlisten] preallocating a journal file /data/XXXXXXXXXXX/journal/prealloc.0
2015-08-20T07:08:44.074+0000 I -        [initandlisten]   File Preallocator Progress: 744488960/1073741824 69%
2015-08-20T07:08:47.176+0000 I -        [initandlisten]   File Preallocator Progress: 901775360/1073741824 83%
2015-08-20T07:08:50.274+0000 I -        [initandlisten]   File Preallocator Progress: 1027604480/1073741824 95%
2015-08-20T07:09:09.057+0000 I JOURNAL  [durability] Durability thread started
2015-08-20T07:09:09.057+0000 I JOURNAL  [journal writer] Journal writer thread started
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] MongoDB starting : pid=25507 port=27000 dbpath=/data/XXXXXXXXXXXXX 64-bit host=CH$
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] db version v3.0.2
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] git version: 6201872043ecbbc0a4cc169b5482dcf385fc464f
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1f 6 Jan 2014
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] build info: Linux ip-10-229-1-2 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 U$
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] allocator: tcmalloc
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] options: { config: "/data/XXXXXXXXXXX/automation-mongod.conf", net: { port: 270$
2015-08-20T07:09:09.074+0000 I -        [initandlisten] Invariant failure _name == nsToDatabaseSubstring( ns ) src/mongo/db/catalog/database.c$
2015-08-20T07:09:09.091+0000 I CONTROL  [initandlisten]
 0xf4f859 0xef0031 0xed4b52 0x91e106 0x91e18f 0x920033 0x922cb0 0x808701 0x7d4ba4 0x7f0503489ec5 0x805d17
----- BEGIN BACKTRACE -----
{"backtrace":[{"b":"400000","o":"B4F859"},{"b":"400000","o":"AF0031"},{"b":"400000","o":"AD4B52"},{"b":"400000","o":"51E106"},{"b":"400000","o$
 mongod(_ZN5mongo15printStackTraceERSo+0x29) [0xf4f859]
 mongod(_ZN5mongo10logContextEPKc+0xE1) [0xef0031]
 mongod(_ZN5mongo15invariantFailedEPKcS1_j+0xB2) [0xed4b52]
 mongod(_ZNK5mongo8Database13getCollectionERKNS_10StringDataE+0x36) [0x91e106]
 mongod(_ZN5mongo8Database30_getOrCreateCollectionInstanceEPNS_16OperationContextERKNS_10StringDataE+0x1F) [0x91e18f]
 mongod(_ZN5mongo8DatabaseC1EPNS_16OperationContextERKNS_10StringDataEPNS_20DatabaseCatalogEntryE+0x1E3) [0x920033]
 mongod(_ZN5mongo14DatabaseHolder6openDbEPNS_16OperationContextERKNS_10StringDataEPb+0x150) [0x922cb0]
mongod(_ZN5mongo14DatabaseHolder6openDbEPNS_16OperationContextERKNS_10StringDataEPb+0x150) [0x922cb0]
 mongod(_ZN5mongo13initAndListenEi+0xC01) [0x808701]
 mongod(main+0x134) [0x7d4ba4]
 libc.so.6(__libc_start_main+0xF5) [0x7f0503489ec5]
 mongod(+0x405D17) [0x805d17]
-----  END BACKTRACE  -----
2015-08-20T07:09:09.091+0000 I -        [initandlisten]

***aborting after invariant() failure

Any idea how can I fix it? I have been trying to fix it since past 4 hours but nothing seems to be working.

So, it turned out that issue was in .ns file of one of the database. I had to delete that file and restart the server. Server started successfully however, the database whose .ns I deleted was lost.

For newbies in mongoDB .ns file is the namespace file that mongo creates for each database in its data (/data) directory.


Can I deactivate log shipping jobs without raising errors? I've set up log shipping from Server A to Server B.  I then set it up from B to A when B was the primary.

I've reverted back to A being primary and disabled the backup, copy and restore jobs associated with B to A shipping.  However, I have a failure of the LSAlert job on both A and B.

Is there a way to suppress these error or must I remove Log Shipping from B to A while A is primary?

My goal is to leave the log shipping configuration in place for DR, but have it not raise errors stating that databases have not been sync'd.

Thanks

This normally occurs when the log-shipping is not removed cleanly. To remove the orphan entries you have to delete them from msdb logshipping tables.

On Primary :
delete from msdb..log_shipping_monitor_secondary
where secondary_database = '' -- secondary db name goes here
delete from dbo.log_shipping_secondary
where primary_database='' -- primary db name goes here

On Secondary:

delete from msdb..log_shipping_monitor_primary
where primary_database='' -- primary db name goes here

delete from dbo.log_shipping_primary_secondaries
where secondary_database='' -- secondary db name goes here


MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default What do you guys think about:
After each query, I put "Order BY PrimaryKey DESC"
 VS
I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there.

Which one will be faster and skip the sort?
OR both of them do sort? nothing optimize ?

Before MySQL 8.0, and MariaDB:
You may use DESC in an index declaration, but it is ignored.  (The PRIMARY KEY is an index.)
You must say DESC in the ORDER BY clause if you want the resultset sorted that way.
For multi-column indexes, all columns in ORDER BY should go the same way (all ASC or all DESC) in order to be able to use an INDEX.  (There are many cases where an index won't be useful for ORDER BY anyway.)
MySQL 8.0:  DESC is honored in INDEXes.


Is there a way to find out how many times a stored procedure was invoked since last DB start I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably in Sybase) how many times a give stored proc was invoked so that we can gain some confidence in removing procs .that were never called recently.

If you can take the hit on performance, enable tracing for a "typical" session (set tracefile "/some/path/" for some_spid), and using set show_sqltext on you can see all the SQL executed. Then you will need to parse the output looking for stored procedures. Of course this approach cannot give you 100% confidence (e.g. maybe you have a batch job that runs once per year) but it's a start.


Rebuild Windows cluster for Alwayson. I am setting up Alwayson,somehow my cluster is gone ( get-clusternode shows down,down,down and Joining). SQL Server is working fine. What Should I do? Create new cluster or build new server? Any suggestions, best practice or Whitepaper will help.

Thank you,
Coorsktm

Questions to ask yourself start with:
How fast do you need it up?  Is it costing you money per hour being down?  Are you out of SLA?  If you don't have SLA's yet for this service can you get them real quick or have at least a good idea if it's OK to be down?

Typically you would want to learn why it is down then decide but sometimes time doesn't allow that.  Other's it could be within maintenance hours so if you know how long it takes to rebuild the cluster you know how much you have to troubleshoot.

Thus my answer would be: figure it out as long as possible while giving yourself enough time to rebuild and test it while keeping the business need it serves in mind as priority.

Personally I try to manage a process and not servers.  I wrote a lot of Powershell automation to build up a brand new FCI from scratch, build out a read only replica AG node, test, open network, test network, etc. etc.  Something like this is great if you're rebuilding and you can start your automation that way if you haven't already.  I can give you some reference on how to begin if you'd like.

Let us know if you need more info by editing your question to give us more background and clarity. Thanks!


Realtime SQL Server Monitoring Tools? We been having a lot of sql server performance problems lately. I've recently been digging into performance analysis using the variety of DMVs. I've definitely found some interesting things. Unfortunately, i'm a developer and can't spend a whole lot of time on this issue and we don't have any in house knowledge in this area. SQL performance monitoring almost seems like an art in itself. So i've been researching some tools that will lead us down the right path and take a lot of guess work out the equation. The competing tools i've found so far are:

Ignite - Confio

SQL Monitor - Redgate

Foglight - Quest

Diagnostic Manager - Idera

Performance Advisor - SQL Sentry
I'm leaning towards SQL Monitor because we have a variety of red gate tools and have been extremely happy with them and it comes at a very attractive price compared to the competition. I also really like the web interface compared to a stand alone app. I'm sure by the price there are features in the other tools that red gate doesn't have. There is only so much you can get from marketing info & videos on their website. Can anyone give some advice to which of the products (if not all) give alerts that are more geared towards the type of shop that doesn't have anyone who is SQL Server performance anaylsis pro?

Edit: Which of the tools require a separate machine? It appears from confio web site that ignite does.

I think the comments on the question answer it. I'm creating this so i can mark the question as answered.


MySQL - How to group data by Day and Hour I have some problem in getting the query for this sample format. My idea is select hours on that day and group it by day or select all the 7 days and group it by hours?

here is the format:

I assume you have one column of interest (dt, a DATETIME) and you COUNTing the number of rows to get the values for that table?

First, do this to see if you get the right values (but not the desired layout):

 SELECT HOUR(dt), WEEKDAY(dt), COUNT(*)
      FROM tbl
      GROUP BY HOUR(dt), WEEKDAY(dt)
      ORDER BY HOUR(dt), WEEKDAY(dt);

Second, you need to "pivot" the table.  However, you have particular columns.

See http://mysql.rjweb.org/doc.php/pivot for how to generate the code for pivoting.

There are still a few formatting issues...

For the headings, you need to turn "0" into "Mon", etc.  Suggest you hard-code the titles.

"23" can be turned into "23:00 - 23:59" with a CONCAT() and some more code.

NULL can be turned into 0 via IFNULL().

See how far you can get.  (Since this question is 3 weeks old, I don't want to put more effort into it if you have already gone away.)

Personally, I would do (and in fact have done) similar stuff using PHP instead of SQL.  PHP code is longer, but less contorted.


How to count decimals without zeros How can I count all decimal place up to the first zero

For example I have a column named x with an entry of numeric datatype value 1.955000000

I want to count the decimal places without the zeros. So here I want the query to make an output of 3 not 9 (with zeros)

The only thing I can think of, is to convert the value to a string, remove all trailing zeros, convert it back to a numeric and then use the scale() function:

scale(trim(trailing '0' from the_column::text)::numeric)

The following example:

create table t1 (val numeric);

insert into t1
values
  (1.955000000),
  (1.10),
  (1.1010),
  (1.102030);

select val, scale(trim(trailing '0' from val::text)::numeric)
from t1;

returns:

val         | scale
------------+------
1.955000000 |     3
       1.10 |     1
     1.1010 |     3
   1.102030 |     5


SQLITE - Update multiple columns, in multiple rows on a table with a composite PK First of all, a question:
UPDATE table_a
    SET column_d = 100, column_e = 200
    WHERE column_a = 1 and column_b = 0 and column_c = 1;

UPDATE table_a
    SET column_d = 123, column_e = 150
    WHERE column_a = 2 and column_b = 1 and column_c = 3

...

Imagine this goes on.
Would any performance improvement come from merging these in a single query?
If so, how can it be done?
Remember I am asking specifically for SQLlite3

Finding each row to update requires an index lookup. Writing a combined query would not change this.
Minimize the transaction overhead by running all UPDATEs inside a single transaction. Further performance improvements are not possible.


Oracle db impdp command syntax error I'm trying to run
impdp system/password dumpfile=FILE.dmp fromuser=USER1 touser=USER1 directory=$DIRECTORY on oracle server but keep getting following error:

LRM-00118: syntax error at '=' at the end of input

I've come to this after running the following commands on oracle server sqlplus:

alter session set container=sgeplus;
CREATE USER USER1 IDENTIFIED BY USER1;
GRANT ALL PRIVILEGES TO USER1 WITH ADMIN OPTION;
GRANT CONNECT TO USER1;
CREATE USER GEOC_WEB IDENTIFIED BY GEOC_WEB;
GRANT CONNECT TO GEOC_WEB;

And running this on oracle server:

expdp sgeplus/sgeplus@localhost/orcl full=y dumpfile=sge210b.dmp

I haven't found any similar error, so any help will be welcomed!

Thanks in advance.

Fromuser and touser are not valid using datapump. If you are trying to change the schema, you can use remap_schema:

REMAP_SCHEMA=source_schema:target_schema

https://docs.oracle.com/database/121/SUTIL/GUID-619809A6-1966-42D6-9ACC-A3E0ADC36523.htm#SUTIL927

Or you can list which schemas to import:

SCHEMAS=schema_name [,...]

https://docs.oracle.com/database/121/SUTIL/GUID-6FEDE3AF-0ED5-46D6-BE91-C3F840291735.htm#SUTIL931


Conditional Foreign Key Relationship I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables.  Here's the hierachy of tables, this is done via FK refrences from child to parent

                  Store
            /                \
  Employees                    \
                             TransactionalStores
                            /       |         \
                     Kiosks         |          BrickMortars
                                 Onlines

I currently have a FK relation from Employee to store

ALTER TABLE Employees ADD CONSTRAINT Employee_Store
            FOREIGN KEY (TransStoreId)
            REFERENCES TransactionalStores(StoreId)

I would like to add the conditional:

WHERE TransactionalStores.storeType != 'ONLINE_TYPE'

Is this possible or must I subclass TransactionalStores into two new subTypes (e.g. PhysicalStores and VirtualStores)

Foreign keys can be made conditional...sort of. You don't show the layout of each table, so here is a typical design showing your relationships:

create table TransactionalStores(
    ID        int   not null auto_increment,
    StoreType char  not null,
    ..., -- other data
    constraint CK_TransStoreType check( StoreType in( 'B', 'K', 'O' )),
    constraint PK_TransactionalStores primary key( ID ),
    constraint UQ_TransStoreTypes unique( ID, StoreType ) -- for FK references
);
create table Kiosks(
    ID         int   not null,
    StoreType  char  not null,
    ..., -- other Kiosk data
    constraint CK_KioskStoreType check( StoreType = 'K' ), -- kiosks only
    constraint PK_Kiosks primary key( ID, StoreType ),
    constraint FK_Kiosks_TransStores foreign key( ID, StoreType )
        references TransactionalStores( ID, StoreType )
);

The Onlines and BrickMorters would have the same basic structure but with StoreType constrained to only 'O' or 'B' as appropriate.

Now you want a reference from another table to TransactionalStores (and through it to the various store tables) but limited to Kiosks and BrickMorter. The only difference would be in the constraint:

create table Employees(
    ID         int       not null,
    StoreID    int,
    StoreType  char,
    ..., -- other Employee data
    constraint PK_Employees primary key( ID ),
    constraint CK_Employees_StoreType check( coalesce( StoreType, 'X' ) <> 'O' )), -- Online not allowed
    constraint FK_Employees_TransStores foreign key( StoreID, StoreType )
        references TransactionalStores( ID, StoreType )
);

In this table, the FK reference forces StoreType to be either 'K', 'O' or 'B' but the field constraint further limits it to only 'K' or 'B'.

For illustration, I've used a check constraint to limit the store types in the TransactionStores table. In real life, a StoreTypes lookup table with StoreType being a FK to that table would probably be a better design choice.


MySql query slows down I have a table (cumulative_energy_consumptions) defined as:

id int(11) pk
sensor_id int(11) fk
value decimal
recorded_at datetime

with two indexes, one on sensor_id, the other on (sensor_id, recorded_at) and it uses InnoDB engine.

Given a sensor and a datetime, I have to find the row with the maximum 'recorded_at' before that datetime.
It can be solved with the following query:

    SELECT MAX(recorded_at)
    FROM cumulative_energy_consumptions
    WHERE sensor_id = 88 AND recorded_at <= '2016-06-29 00:00:00'

and it is incredibly fast (0.00030 s on my machine), even with a very chatty sensor (id 88) that has more than 300k records.

Instead of a single sensor, I wrote the following query to get the result for many sensors at once:

    SELECT sensor_id, MAX(recorded_at) AS first_before
            FROM cumulative_energy_consumptions
            WHERE
                sensor_id IN (80, 85, 88, 89)
                AND
                recorded_at <= '2016-07-10 00:00:00'
            GROUP BY sensor_id

and it is also very very fast (0.00055 s)

Now I was trying to write the query for one sensor and many datetimes, and I tried that:

SELECT input_times.*, (
        SELECT MAX(recorded_at)
        FROM cumulative_energy_consumptions
        WHERE sensor_id = 88 AND recorded_at <= input_times.instant
    ) as res
FROM
    (
        SELECT '2016-06-29 00:00:00' as instant
--      UNION SELECT '2016-06-30 00:00:00' as instant
--    UNION SELECT '2016-07-01 00:00:00' as instant
--    UNION SELECT '2016-07-02 00:00:00' as instant
   ) as input_times

when the table created using unions has only one row, it takes 0.0004s and I was expecting that with two rows it would have taken roughly the double, but it completes in 0.2 s; with 4 rows it takes 0.4s and so on.

Why does MySql uses a different strategy with multiple rows instead of applying the strategy for one row many times? Is there a way to rewrite this query to make it as fast as expected?

Thank you

Try this rewriting. It avoids the GROUP BY and should be using the index on (sensor_id, recorded_at) for both the subquery and the join:

SELECT
    input_times.instant,
    c.sensor_id,
    c.recorded_at AS first_before,
    c.value
FROM
    ( SELECT 80 AS sensor_id UNION ALL
      SELECT 85  UNION ALL
      SELECT 88  UNION ALL
      SELECT 89
    ) AS s
  CROSS JOIN
    ( SELECT '2016-06-29 00:00:00' AS instant
      UNION ALL
      ...
    ) AS input_times
  JOIN
    cumulative_energy_consumptions AS c
  ON  c.sensor_id = s.sensor_id
  AND c.recorded_at = ( SELECT ci.recorded_at
                        FROM cumulative_energy_consumptions AS ci
                        WHERE ci.sensor_id = s.sensor_id
                          AND ci.recorded_at <= input_times.instant
                        ORDER BY ci.recorded_at DESC
                        LIMIT 1
                      ) ;


MariaDB: calculate the reads/writes ratio in percent I found a blog post howto calculate the reads/writes ratio in percent.

I receive NULL values in our env.

> SELECT @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) AS `Total`,
    ->  @total_reads := SUM(IF(variable_name = 'Com_select', variable_value, 0)) AS `Total reads`,
    ->  @total_writes := SUM(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes`,
    ->  ROUND((@total_reads / @total_com * 100),2) as `Reads %`,
    ->  ROUND((@total_writes / @total_com * 100),2) as `Writes %`
    -> FROM information_schema.GLOBAL_STATUS;
+-----------+-------------+--------------+---------+----------+
| Total     | Total reads | Total writes | Reads % | Writes % |
+-----------+-------------+--------------+---------+----------+
| 109286055 |    85029655 |     24256400 |    NULL |     NULL |
+-----------+-------------+--------------+---------+----------+
1 row in set (0.03 sec)

MariaDB [(none)]> select VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.01 sec)

any idea?

The Reads% and Writes% does not have the @totals until too late.  This should work better:

SELECT  Total,
       `Total reads`,
       `Total writes`,
       ROUND((@total_reads / @total_com * 100), 2) as `Reads %`,
       ROUND((@total_writes / @total_com * 100), 2) as `Writes %`
    FROM
      ( SELECT  @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete',
                             'Com_insert', 'Com_update', 'Com_replace'),
                             variable_value, 0)) AS `Total`,
                @total_reads := SUM(IF(variable_name = 'Com_select',
                                variable_value, 0)) AS `Total reads`,
                @total_writes := SUM(IF(variable_name IN ('Com_delete',
                                        'Com_insert', 'Com_update', 'Com_replace'
                          ), variable_value, 0)) as `Total writes`
            FROM  information_schema.GLOBAL_STATUS
      ) x;


Why does an index scan read more pages than exist in an index? I've been trying out running some queries for a testing scenario and I noticed that when doing a full-scan of a nonclustered index, SQL Server reads more pages than actually are in the index.

As you can see from the image below, my query is (a simple one):

select
    sometext
    , somemoretext
from tbl

and I have an exact covering nonclustered index for those columns.

In Profiler the number of pages read for this is 22,356 as you can see in the image.

But when looking at the index details, under Fragmentation, the number of pages for this index is 22,278.

What is the cause for this difference in pages read (78 to be precise)? Is the number in the Fragmentation section not reliable or is there some other explanation?

The index is not fragmented (or appears not to be), so there are no more pages than those 22,278 .. or are there? I wasn't able to find an explanation for this online, on Microsoft sites or otherwise.

(This is a controlled environment, no inserts and no deletes occurred on the table while taking screenshots for this question)

I have also pasted the plan here.

You should check what code is submitted to the server when you open "Fragmentation" (using SQL Server Profiler)

And you'll see the following code:

select partition_number as PartitionNumber,
       index_type_desc  as IndexType,
       index_depth as Depth,
       avg_fragmentation_in_percent as AverageFragmentation,
       page_count   as Pages,
       avg_page_space_used_in_percent   as AveragePageDensity,
       record_count as Rows,
       ghost_record_count   as GhostRows,
       version_ghost_record_count   as VersionGhostRows,
       min_record_size_in_bytes as MinimumRecordSize,
       max_record_size_in_bytes as MaximumRecordSize,
       avg_record_size_in_bytes as AverageRecordSize,
       forwarded_record_count as ForwardedRecords
from sys.dm_db_index_physical_stats(7, 773577794, 2, NULL, 'SAMPLED')

Note the mode: it's 'SAMPLED'.

The code returns only 1 row that reflects page_count of the leaf level of the index.

Now change the code to use 'DETAILED' and you'll find missing pages from non-leaf levels.

Here is my repro.

I first look at Fragmentation tab where I find that my ix_include_filler index has 20836 pages.

Then I executed the code that I saw in Profiler, and the code as it is uses "SAMPLED", returns 1 row and shows 20836 pages that we see in "Fragmentation" tab.

Then I changed "SAMPLED" to "DETAILED" and clearly see that these 20836 are only from a leaf level. Level N1 has other 36 pages + a root.

If your SSMS launches different code when you open Fragmentation tab, maybe you should update your question with SSMS version and spy the code it sends by yourself.

My SSMS's version is 10.50.6000.34 and my explanation is valid for this version.