Side By Side Upgrade and Migration from 2008 to 2016 Side By Side Upgrade and Migration from 2008 to 2016

Old Server Name Test1

New Server Name Test1

Default Instance Test 1

My question is when the new server be named as the old one 'Test1" and it will try to register the dns while the old server is online wont be there a problem as the name is already being registered. In that case what will be the workaround since it is a side by side upgrade and migration I need to use the same server name and need to use  both the servers in order to upgrade and mirgate and then turn off the old server after the switch over

I've had to do this and just had a record available for the old server.  Renamed the DNS entries and did a ipconfig /flushdns on the servers themselves since they were replicating back in case of a failback.  We set the DNS to a very low TTL so clients also got updated.  Powershell made all this very easy and fast.

If your clients are easily identified and you have access, you can do a flushdns yourself on them as well.  I recorded and documented all connections going to the database prior to the upgrade so I knew all the clients to look out for.  Then finally I set the old database to block all users except the sole one I was using for replication on the old machine and ran traces to see if anyone was attempting to connect to the old instance.  None did but if they were, I would inform them to update DNS or check their connection string.


Is it unwise to run replication on same physical server? I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into is that we're already maxed out on power at our datacenter. So adding another physical server is not an option.

Our existing database server is fairly under-utilized as far as cpu (load averages never really get above 1 on a quad-core). So the leading idea is to toss in some new drives and double the memory (from 8GB to 16) and run a second mysql instance on the same physical machine. Each instance would have separate disks for the database.

Is there anything wrong with this idea?

Edit (more info):
I've (luckily) never had anything bad enough happen to take down the server, but am trying to plan ahead. We of course have nightly backups that we could recover from. But I figured having the redundant data on separate disks would provide a quicker solution if the master server's drives failed (obviously not if the entire machine goes out).

As for the reporting aspect, any tables we would report off of are MyIsam. So doing expensive reads on the same tables that are being written to can bog down the server. My assumption was having a slave server to report off of wouldn't affect the main server as long as we threw enough RAM at it (since cpu load hasn't been an issue yet).

For redundancy in terms of system reliability and data safety running a slave on the same machine as the master offers you nothing (or close to). If something bad enough to bring down the master happens it will probably bring down the slave too.

For purely segregating users for access rights reasons, a good RDBMS will offer more effective ways of doing that.

Running the two databases on the same machine will require more RAM to run at the same efficiency as the two databases will compete for space to keep they various buffers and caches. There might be a performance benefit though via IO-load segregation, if the datafiles for the slave are on different physical drives than the master. In this case you can run complex reports that required many disk reads against the slave without competing for the master for drive IO bandwidth.

Edit: as DTest mentions in his comment below, one other possible benefit of a slave DB (even if on the same drives as the master) is that complex long-running queries in the slave that might otherwise cause locking issues for the day-to-day-running queries on the master are safer. Though you are still better off having the slave on different drives as such significant queries are likely to cause IO contention issues.


How to select data in the same table on different rows that are empty? I use this select on my SQLite database:

SELECT attributeX
 FROM table
 GROUP BY attributeX
 HAVING COUNT(attributeY) = 0;

My table:

  attributeX (type integer) |  attributeY (type text)
----------------+-------
  1452 | aaa
  1452 | bbb
  1452 |
  1452 | bbb
  1453 |
  1453 |
  1453 |
  1454 | eee
  1454 | fff
  1455 | iii
      ....

I get result 1453 only when all items in column attributeY are empty for 1453. So in SQLite I get result 1453.

I have the same database only with myschema in PostgreSQL. I use this SELECT:

SELECT attributeX
 FROM myschema.table
 GROUP BY attributeX
 HAVING COUNT(attributeY) = 0;

and I get:

 attributeX
------------
(0 rows)

How to select in PostgreSQL?

SQLite schema of this table:

CREATE TABLE table (
 attribute1 INTEGER PRIMARY KEY,
 attributeX INTEGER NOT NULL,
 attribute2 TEXT NOT NULL,
 attributeY TEXT
);

CREATE INDEX table_attributeY_idx ON table (attributeY);
CREATE INDEX table_attribute2_idx ON table (attribute2);
CREATE INDEX table_attributeX_idx ON table (attributeX);

PostgreSQL schema of this table:

Table "myschema.table"
        Column  |          Type         |                                    Modifier                             | Storage  | Desc
----------------+-----------------------+-------------------------------------------------------------------------+----------+-------
 attribute1     | integer               | not null implicitly nextval('myschema.table_attribute1_seq'::regclass)  | plain    |
 attributeX     | integer               | not null                                                                | plain    |
 attribute2     | character varying(32) | not null                                                                | extended |
 attributeY     | text                  |                                                                         | extended |
Indexy:
    "table_attribute1_pkey" PRIMARY KEY, btree (attribute1)
    "fki_table_attributeX_fkey" btree (attributeX)
Conditions for foreign key:
    "table_attributeX_fkey" FOREIGN KEY (attributeX) REFERENCES myschema.table2(attributeX2) ON UPDATE RESTRICT ON DELETE CASCADE
Has OID: no

Insert:

INSERT INTO myschema.table (attributeX, attribute2, attributeY)
VALUES (%s, %s,%s)... (python script)

You explain:

  I get result 1453 only when all items in column attributeY are empty for 1453.

But that's incorrect. Bold emphasis mine. The aggregate function count returns (per documentation):

  number of input rows for which the value of expression is not null

The same is true for SQLite (per documentation):

  The count(X) function returns a count of the number of times that X is
  not NULL in a group.

You obviously have one or more rows with a non-null value in attributeY in your Postgres table - probably an empty string ''.

Test with:

SELECT *
FROM   myschema.table
WHERE  attributeX = 1453
AND    attributeY IS NOT NULL;

Be sure to understand the difference between "empty" ('') and NULL:

Empty strings are character types (typically text, varchar or char) with 0 characters ('') - so basically "nothing", the equivalent of 0 for a numeric data type. NULL is possible for any data type and means "unknown". Some clients have a hard time making the difference clear in their display.

Alternative query

To find attributeX where all attributeY are either empty or NULL use this alternative query:

SELECT attributeX
FROM   myschema.table
GROUP  BY attributeX
HAVING NOT bool_or(attributeY <> '');

The expression attributeY <> '' is only true for non-null, non-empty attributeY. The aggregate function bool_or returns (per documentation):

  true if at least one input value is true, otherwise false

Those are the results we exclude (NOT ...) and return the rest. Voilá.

There are many other (less elegant) ways to achieve the same. Like:

... HAVING count(CASE WHEN attributeY <> '' THEN 1 END) = 0;
... HAVING count(attributeY <> '' OR NULL) = 0;
... HAVING count(NULLIF(attributeY, '') = 0;

More:

For absolute performance, is SUM faster or COUNT?
Replace empty strings with null values


Alternative to Temporary Table with Create View - MySQL I have a view which uses many tables, and statements and functions. It is very slow, the main reason being that I only have read-only access to the main tables which do not have the appropriate indexes, so joins evaluate very slowly.

Following some advice I have broken the joins down using temporary tables, to ensure that every join has indexed fields on both sides. This has worked very well. If I run a select to pull the final output it runs 60x quicker than previously. However I cannot use this to drive the necessary view, as views cannot refer to temporary tables.

Are there any alternatives to temporary tables, or different approaches I could take?

Thanks

View is just an alias to the query that allow you to use its result in other queries in terms of tables not subqueries. But each time you refer to the view, the query from its definition is invoked. No temporary tables created when view is defined.

Therefore views do not improve the overall performance at all and are intended to make DB structure more clear and logical.

Prior to the MySQL 5.7 query results can be cached that really speed up some queries. But as far as caching conflicts with InnoDB internals, it is disabled now and can cause significant slowdown and overall performance losses when enabled. So views should be considered as threat to the performance.

On the other hand, temporary tables, especially those with ENGINE=MEMORY,  can significantly improve the performance. But you have to ensure that temptable is updated each time the referred tables are updated. Triggers are the good tool to keep reference consistency of the derived tables. Sure even memory-based temptables should be properly indexed for maximum performance.


Secure Symmetric key from sa Can I secure SQL Server symmetric key from [sa] account? Can I create symmetric key using local user certificate via MMC?

Thanks

Can I secure SQL Server symmetric key from [sa] account?

Nope. The best you can do (and should be doing) is to:

Rename SA
Use Windows authentication only
Disable SA (also see #2)

However, SA is just a login. The server roles of sysadmins gives full privileges over the system.

The only way to secure a symmetric key using SQL Server against a sysadmins is to use multiple encryption techniques involving the users and/or the application.

Note that always encrypted and HSMs use Asymmetric keys.

  Can I create symmetric key using local user certificate via MMC?

That's not going to help you with this issue.


Large archive table with "unimportant" data, how to improve/redesign? We have a SQL DB that collects analog and digital sensor data from several hundred devices. The archive table is quite simple, just a (datetime) timestamp, (int) stationId, (int) datatapointId and (double) value (plus 3 bool flags).

The DB collects about 12 million entries per month. This table is supposed to be always growing, no rollbacks, updates or deletions are neccessary.

The main purpose of this table is to be used to show archive values in our web visualization ordered by timestamp and filtered by this or that id, therefore I created the relevant indexes for the fields I need. It works, but the performance is quite poor (about 5 seconds when filtering by a start/end time, stationId and datapointId).

What would be a better way to store and query this kind of data? Is SQL Server Standard 2016 perhaps not the right DBMS for such an archive? The original decision to use SQL Server dates back several years when the amount of data was way smaller than today. Is there some magic in SQL Server that I can apply to trade reliability for performance?

It would be good to know where that 5 seconds goes. Is it disk, network, memory or CPU?

Guessing it's disk there are ways to speed this up. One is to get faster disk: flash or NVMe would be nice! Another, more plausible solution, is to compress the data so less IO is required for the same information content. SQL Server supports several options. Also look into clustered columnstore indexes. These are specifically designed to efficiently store and process large amounts of data.

Looking outside SQL Server it sounds like you have time-series data. There are many DBMS specifically tailored to this use-case.

Before you go that far you may look into refactoring your application architecture. Since the data is write-once you can pre-process, or at least cache, any query against it. What visualisations do you use? Often data is aggregated so, say, min, max and average per hour/ day/ minute are shown. If this is your case you can pre-calculate these aggregates, store them and remove the detailed data. If you use n-tiles, candlesticks or line charts, calculate the corresponding SVG, PNG or JPEG once for each interval, and stitch them together to make larger charts. A lot of this store-and-stitch can be done in the file system or CDN avoiding load on the DB server at all.


Query is slow for certain users I have a couple queries called from a C# .NET web application which are always fast for me (I am an local admin on the SQL Server) but for a group of users (domain group with required permissions), the query is incredibly slow to the point it times out in the application.

What would cause the exact same query to run differently for different users?

More info:

The query is inline SQL in the C# code, not a stored procedure
The app uses domain authentication and both the user and myself run the query through the app
Seems like the issue is different plans and one was cached so that is why it was different for different users. Something is affecting the cache because now the query is slow for me via the app and fast in SQL Server Management Studio.

If the parameters are the same (I'm assuming that's what's meant by exact same query), it shouldn't be parameter sniffing (users get a bad plan for the wrong parameter(s)), but rather users are getting different plans for the same parameter(s). It could be because of settings like quoted_identifier and arithabort, which you can compare in sys.dm_exec_sessions for the fast user and the slow user, or it could be because they have different default schemas and objects are referenced without the schema prefix. Parameter sniffing may still be involved (hence why one of them has a bad plan).


Does the hash join operator always pull from the build side? Watching queries execute with Live Query Statistics I noticed that it seems SQL Server is lazily constructing a hash table from the build input of a hash join.

This is a meaningful difference in the case of 0 probe rows. It potentially saves the entire build side tree.

I always thought a hash ran like this:

Create build-side hash table.
Match all probe rows against it.

But is it the following instead?

Pull first probe row.
Complete the operation if no row available (short-circuit).
Match all probe rows against it.

I'm not sure how to conclusively test which way it is. I'm not sure the Live Query Statistics output can be trusted in this way. Does anyone know how this works?

If you post the actual query plan for your specific query, we might be able to comment on that more directly.

But in general, I believe (and have always observed) that the hash join operator does always runs as you expected:

Create build-side hash table.
Match all probe rows against it.

This generally makes sense given that SQL Server will place the smaller (estimated) rowset on the build side of the join. The probe side is expected to be larger than the build side (and therefore to contain at least some rows).

In addition, SQL Server has a potential bitmap optimization that it can apply to the probe side of a hash join in some cases, but this optimization requires that the build side be processed first.

An example

For a simple query where we force a large build side of the hash join and an empty probe side, the actual execution plan shows that all rows are processed on the build side of the join.

-- Fully processes the build side of the hash join
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM master..spt_values v3
    WHERE number < -100000
) p
    ON p.number = b.number

Can the build side ever be skipped?

There is at least one example where the build side of the hash join can be skipped: In some cases, the entire join can be eliminated. For example, here SQL Server is able to prove prior to execution that the probe side will have 0 rows. The final query plan is therefore a constant scan and there is no hash join.

-- No hash join at all; the query optimizer realizes no rows are guaranteed
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM master..spt_values v3
    WHERE 0=1
) p
    ON p.number = b.number

What about batch mode?

Based on a quick test, it seems that the batch mode hash join operator will also fully process the build side of the hash join.

-- The batch mode hash join also fully processes the build side
-- even if there are 0 rows on the probe side
SELECT number
INTO #spt_values
FROM master..spt_values
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_#spt_values ON #spt_values
GO
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM #spt_values v1
    CROSS JOIN #spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM #spt_values v3
    WHERE number < -100000
) p
    ON p.number = b.number
GO


mongosniff is missing in mongo package I installed mongodb-10gen version 2.4.6 in Debian. This package does not include mongosniff.

How can I install a pre-built version of mongosniff in Debian ?

This is to do with a libpcap dependency that mongosniff has, to explain why this is the case, take a look here and here for the history.

At the moment, that means that you need to go to the MongoDB downloads page and get the mongosniff binary from one of the tarballs provided there.  For 64-bit Linux, version 2.4.6 specifically, you would want this version, though there is no specific reason not to use a later version of the mongosniff tool with 2.4.6

I would also note that Wireshark has MongoDB dissectors built in, so you can also use that to read/view a standard tcpdump of MongoDB traffic as an alternative.  For an example, take a look at my Q&A over on Serverfault related to filtering:

https://serverfault.com/a/521966/108132


Null Values and Performance I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.)
But still there are times when saving user's data, there may be null values or empty strings.

Can you please guide me to get the effect of having NULL values or empty strings in a database, where SELECT is most used?

NOTE: Specifically for MySQL 5.5 or later.

Performance-wise I think there are a few issues you will run into on MySQL.  The first one is that MySQL doesn't handle very large numbers of joins as well as one might like.  This can cause performance issues.   Also you do have extra table scans and that has a performance cost too.

Finally, and this is InnoDB specific, but every table is clustered on a primary key and the engine does not support scanning a table in physical order, so in this regard joins of large record sets is going to become a performance issue.  At some point you end up with a sequential scan but this is a sequential index scan, traversing every node in a tree, and that leads to a lot of extra disk I/O.


good practices of creating tables in mysql Hi I was thinking of developing an web apllication using php and mysql.
Application will give a professor the required data about the student.

i want to design application considering Engineering college.

So professor can view topper in perticular branch,overall topper in college,search student by roll no,display the list of students i roll no order or on the basis of percentage

Overalll data will consist of

Student name,roll no,year,percentage,department name,marks in each subject,subject name,branch

So please guide me to create table for the same in mysql..

thanks in anticipation

I once asked for answers to a problem very similar to yours, and even though I think back on how frustrated I was after a Software Architect did it to me, now, as a much wiser developer, I give the same answer to you.

  Database Design for Mere Mortals: 2nd Edition
  (2003) by  Michael J. Hernandez. There is also a  Database Design for Mere
  Mortals: 3rd Edition
  (2013) by the same author.

 -- Come back in a couple years as a database engineer, then give someone else the same answer.


Counting working days in a month I wrote this query that suppose to count the working days in a month but I get the following error:

[42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 305

The date is a timestamp and this is the function below serves as the denominator to normalize sales:
(count(((date_part('month',date))+ Interval '1 month'- Interval '1 day')-(date_part('month',date)))
           filter (where date_part('dow', date) not in (6,0)))

In the end I will have:
select date_part('month', date) as month,
sum(sales)/denominator as Monthly_noralized_sales
from sales;

I'm not sure if that is what you're trying to achieve, but to count working days you need a range of dates.
IMHO the best way to deal with dates is by using a calendar table. Keep in mine that you need to add holidays and days off to your calendar. You can find a lot of samples googling a bit.
If you don't have/want to use a calendar table, you can take advantage of generate_series function.
create table t (dt timestamp);
insert into t values ('20200110'),('20200203'),('20200715');

select
    date_part('month', dts) mt,
    count(*) as wd
from
    (select
        generate_series(date_trunc('month', dt)::date,
                        (date_trunc('month', dt)::date
                                    + interval '1 month - 1 day')::date,
                        interval '1 day') dts
     from
        t) t2
where
    date_part('dow', dts) <> 6
group by
    date_part('month', dts);

mt | wd
:- | -:
7  | 27
1  | 27
2  | 24

db<>fiddle here


Large Logfile full but very small tlog backup We have a SQL Server 2014 SP2 instance, where I discovered a strange behavior. One of the databases (Full recovery model) has a log file of approx 10GB and I see regular file growths at random times.

I haven't yet found out what causes the file growths, but what intrigues me is that the log file is empty in the morning and the log backup (taken every 15min) is most of the time 700KB and one time 12MB.

Can somebody explain how a full log file of 10GB can generate such a small log backups? Can backup compression do that?

Because the log growths happen at night, I wasn't able to execute DBCC SQLPERF(LOGSPACE), but I expect that the file was full, otherwise it wouldn't have grown. Likewise, I cannot say what the log_reuse_wait_desc was.

I have now setup monitoring so I can obtain extra data.

You should check log_reuse_wait_desc of sys.databases after taking log backup to find out the reason.

Log backups just try to clear the log, but it can happen that it's impossible.

In this article sys.databases (Transact-SQL) you can find possible reasons WHY the log cannot be cleared, it can be open transaction, replication, mirroring, ecc.

If the log cannot clear it will grow. Once the cause of log retention is gone, log is cleared and can be reused.

One natural cause of this is full backup.
If your db is large enough and full backup takes some hours to complete, even if log backups can run concurrently and their size is small as usual, for the whole duration of full backup the log cannot be cleared.

This means that you log file should be large enough to accomodate all the data modifications for at least these some hours.

  Can somebody explain how a full log file of 10GB can generate such a
  small log backups?

It's not a log size to generate log records :)

It's your database writing activity.
If you run log backups every 15 minutes and they are about 700Kb it means that your activity generates 700Kb of log records every 15 minutes.

What you are asking about instead is what can it be to prevent my log to be cleared for such a long time that it accumulates 10Gb of data and still cannot be truncated ?

If the log was not truncated at the last log backup, it does not imply that the next log backup will grab more log records. The next log backup will start with the next record to the record where the previous backup was stopped. Log backups are not cumulative.

I will give you an example:

I opened transaction and went home without doing commit or rollback. Log backups run regularly every 15 minutes and every log backup grabs 1Mb of log records. I return to work after 1 week of illness. My transaction is still open! For one week the log could not be truncated. My log backups were still 1Mb, but my log is almost full: it accumulated 1Mb*4 *24*7 Mb=672Mb.

Now that I'm back, I finally issue a commit. The next log backup is still 1Mb but it could finally truncate the log.

  So in my case, it could be that something is blocking the log truncation => log file grows but for several weeks a log backup of 700KB can be taken?

Yes, exactly. You should find this "something" that blocks log truncation.


best possible index on this situation create table #testing(id int, name varchar(50))
go

Insert Into #testing
Values(231, 'fasd')

Insert Into #testing
Values(232, 'dsffd')

Insert Into #testing
Values(233, 'xas')

Insert Into #testing
Values(234, 'asdasd')

create table #testing2(id int, name varchar(50))
go
Insert Into #testing(id)
Values(231)

Insert Into #testing(id)
Values(232)

Insert Into #testing(id)
Values(233)

Insert Into #testing(id)
Values(234)
go

update m
set name = x.name
from #testing2 m
join #testing x
on m.id = x.id
Where m.name is null

Without an index on #testing and #testing2

index on id column on both #testing and #testing2

index is

create nonclustered index ix_test1 on #testing1(id) include (name)
create nonclustered index ix_test1 on #testing2(id)

What would be the best possible index on this situation? Is it right to not have an index on #testing(name) because the write/update would be slower?

Do you want to perform the update once or on a regular basis? If the update on million rows is done once, then the best solution is to create a temp column "processed" on #testing2 table of bit (int,tinyint) which will serve as your null filter. The index on bit or int columns works a lot more optimal than on varchar. Also, having 2 type of values on index definition (0 for null 1 for not null) will be very fast.

Keep your indexes and add the second index and you will have the following Plan if you change the filtering options in the query on "processed" column as it is marked in the picture below with the plan:

create nonclustered index ix_test1 on testing2(processed, id)


How does Oracle handle composite index lookups? MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix:

  MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index.

A quote from this answer on PostgreSQL caught my attention:

  This is somewhat different in Oracle 11 which can sometimes also use columns that are not at the beginning of the index definition.

Under what circumstances can Oracle (at least in 11g) do a lookup without the left-most prefix columns existing in the query?

In Oracle, we have a feature called Index skip-scan that makes it possible to benefit from composite indexes even if the leading (left most) part of the index is not specified in the WHERE condition. This is a 9i New Feature, documented here:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm


How to grant a user UNSAFE ASSEMBLY permission How can we assign a user , e.g.: the database owner (DBO) the UNSAFE ASSEMBLY permission?

I'm asking it to enable a user to create an assembly and don't know the SA password, so EXEC sp_changedbowner ‘sa’ can't help.

Any idea?

Thank you.

Only users mapped to a login that's a member of the sysadmin fixed server role can create and alter an assembly with an UNSAFE permission set defined. Therefore, to achieve your desired result you would have to add that respective login to the sysadmin fixed server role.

BOL reference on CREATE ASSEMBLY:

  Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies. Only members of the sysadmin fixed server role can create and alter UNSAFE assemblies.


How to group time intervals by order of magnitude in Postgres? I have a bunch of time intervals that I would like to analyze.  (Thousands of them.)  They range from 0 seconds up to several thousand days, but most of them are less than ten minutes.

I would like to group them by roughly how long they are.

I found that I can use the following to group them by the number of minutes (ignoring the seconds):

select count(*), date_trunc('minute', gap) as rounded from mytable
group by rounded order by rounded
;

This groups the majority of the results into the 0-9 minutes groups.

However, there are still hundreds of rows, because all of the odd amounts (e.g. 21 days 13 hours 58 minutes) are still unique when only truncated to the nearest minute.

What I would like would be to group the values more broadly—for instance, 0-10 minutes, then 10-59 minutes, 1-12 hours, 13-47 hours, 2-10 days, etc.  The exact groupings aren't particularly important, but the point is I want to round the times variably, something conceptually similar to rounding them all to two "significant digits."

How can I do this in Postgres?

(Note: The query doesn't have to be efficient; nor does it have to be simple, though simplicity is nice.  I'm using Postgres 10.)

A CASE expression can help:

SELECT CASE WHEN gap <= INTERVAL '10 minutes'
            THEN '0-10 minutes'
            WHEN gap <= INTERVAL '59 minutes'
            THEN '10-59 minutes'
            ...
            WHEN gap <= INTERVAL '10 days'
            THEN '2-10 days'
            ELSE 'more than 10 days'
       END AS gap_range
...
GROUP BY gap_range;


How to restore SQL Server 2008 R2 backup to SQL Server 2008 I am unable to restore a backup from a 64-bit instance onto a 32-bit instance.

Source Server:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

Destination server:

Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (Intel X86)

Error while restoring:

  Restore failed for Server 'servername'
  The database was backed up on a server running version 10.50.2500. That version is incompatible with this server, which is running version 10.00.4064. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Your issue isn't with the 32-bit vs. 64-bit, but it's that you are trying to restore a database on an older version.

  The database was backed up on a server running version 10.50.2600.
  That version is incompatible with this server, which is running
  version 10.00.4064.

This means that the source DB is 2008 R2, and destination DB version is 2008 (not R2).

The Best way to do this is to Generate the script of Database and execute it on other machine.

More Reading

Raj


Create WAL at interval, but only if data changed I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure.

However, this creates a LOT of big files, even if nothing has changed!  Is there a way to tell PostgreSQL to only create these WAL files at the timeout interval IF something has changed?

The archive_timeout and the checkpoint_timeout feed off each other in such a way that you will get a WAL switch once per whichever of those two settings is greater, even on a system that is otherwise idle.  So you can increase checkpoint_timeout to the maximum value (1 hour) to cut down on the idle log generation by a factor of 12.  The problem is that a checkpoint creates a record that needs to be archived, and the archival creates traffic that needs to be checkpointed (or at least, that PostgreSQL can't prove to itself doesn't need to be checkpointed)

There are a variety of ways around this, but most of them only work if you are running something less ancient than version 8.4.


Different kinds of pricing PostgreSQL I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this:

+----+------+-----------+-----------+
| ID | Name | price_old | price_new |
+----+------+-----------+-----------+
| 1  | ball |   12.00   |   8.00    |
+----+------+-----------+-----------+

EDIT:

Now we would like to handle different kinds of pricing. Not only decimals (like in price_old and price_new), but also like "against any reasonable offer". There is only one option, static price OR some kind of text. What do you think what's best? Separate table for pricing, or extra columns. I think it's not so smart to change price_old and price_new to varchar instead of decimal, so more kinds of pricing are available in one column... Cause it can cause trouble I guess.

Further we have different scenarios of availability of products on our site. A product can be available for 24 hours, or while stocks last. Guess it would be a good idea to have a separate table for this also? I always think it's not a good idea to have different columns to choose from in one table, like if one column is empty use the other one. Not that flexible... But what do you guys think?

According to the true nature of things (as I understand it), I suggest two tables:
CREATE TABLE product (
   product_id serial PRIMARY KEY
 , product    text NOT NULL
 --   more attributes of the product
);

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price      int           -- prices in cent
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

We are dealing with two different entities:

The product per se, with all its (hardly changing) attributes. Goes into table product.

The deal you are offering, with a price, a "price alternative" (price_alt) a start time (valid_from), an optional end time (valid_to), ... Table offer.

This adequate model has technical advantages:

You automatically have a history of prices, which you can keep or delete or backup (and restore without interfering with the working system).

Most of the data goes into product. Most of the updates go into offer, in the form of new entries, so INSERTs really. INSERTing small rows is much cheaper than UPDATEs on big rows.

You can always add a VIEW (or MATERIALIZED VIEW if you need the read performance) to provide a complete table of current offers including product details.
Price
Assuming freely formulated price alternatives (price_alt), hence data type text. If it's just the same recurring phrases, use an enum or create a lookup-table and only store a price_alt_id in offer.
I would define that price_alt overrules price. There can still be a price (might serve as guideline for haggling or as minimum), but that's optional. The constraint some_kind_of_price_required enforces that some kind of price is always present.
Either way, I'd expect an actual price in the majority of offers. Use an integer column and store the amount in cent. That's typically simpler and faster. An int only occupies 4 bytes, where a numeric for your case typically occupies 8-12 bytes.
Additional columns with mostly NULL values are cheap:

Calculating and saving space in PostgreSQL
Adding nullable column to table costs more than 10 minutes

The design with an additional column for price_alt is much more effective and also much cleaner than switching to text representation of numeric prices (that would be a very bad decision). A view could show a single price column as formatted text (overruled by price_alt).
I would define valid offers as:

the latest offer per product with valid_from pre-dating "now"
valid_to IS NULL or greater than "now"
product in store (outside the scpoe of this answer).

So there is at most one offer per product at any given time.


Are expdp/impdp commands appropriate for daily schema sync? I've done following comands:

expdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=expdpSCH_%date%.log

It runs great, without errors. But when I use command:

impdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=impdpSCH_%date%.log

It gave me bunch of errors that tables, sequences and other stuff already exist. So I have a question, could I use such an approach or it's wrong and I should use something else?

No, that is not a possible way:

Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it.
You can configure how export should work if a table already exists but if there is a table that s referenced by another table then TRUNCATE and REPLACE will not  work.
Even if there is no foreign key reference to a table indexes will slow down the import dramatically
A lot of other problems may occur (concerning triggers, partitions, references to other tables and maybe more)

So the only reasonable way to synchronize the schema using expdp/impdp is to drop the schema before you import.

If you don't want to drop the schema for some reason but replace the data of the tables  a lot of additional operations are necessary.


I have Fragmented Index on a empty table! I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be possible? I assume if there is no data in a table, the index should be blank and can not be fragmented.

    SELECT
    a.index_id,
    name,
    avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N'Limited') AS a
    JOIN sys.indexes AS b
        ON a.object_id = b.object_id
        AND a.index_id = b.index_id

Could someone give me an explanation?

Thank you in advance.

Nazila

Fragmentation values are meaningless for small tables.
An empty (or emptied) table is small.

Small is determined by how many 8k data pages are allocated to it.

For example, see this question: Why is my database still fragmented after I rebuilt and reindexed everything?


How to determine high availability configuration is working correctly I am in the process of setting up always on high availability clusters for the first time. Can anyone explain to me from the networking perspective after configuring the group and testing to make sure it is functioning properly, how does SQL know when to fail over, and how would my application connecting to the SQL server know where it is pointing?

So for example I have an application foo connecting to a named instance DB01.FOO. When foo fails and is rolled over to DB02.FOO, how would my application know it should be talking to DB02.FOO?

DB02.FOO is in the same domain but a different server. I am just trying to wrap my head around all aspects of this before attempting it. If there are any links someone would recommend that would be awesome as well.

how does SQL know when to fail over

There is a Flexible Failover Policy which you can use to adjust the sensitivity to the criteria that will trigger a failover. The default is FAILURE_CONDITION_LEVEL = 3 which means "critical error, server unresponsive, or server down." You can see more details here and here.

  how would my application ... know where it is pointing

If your availability group listener is properly configured, the connection string should not need any further knowledge to always connect (even if after a retry) to the primary replica on a specific server - it does not need to know those implementation details at all. All it needs to do is point at the listener, and the listener determines if the primary replica is currently on DB01 or DB02. This is pretty powerful since it lets you adjust your AG configuration and move replicas all over the place without ever having to touch the applications (except in cases where you (a) add a replica with read-only intent or (b) have to move the listener). You can see more details here.


MySQL - Why specific query doesn't use cache? My customer is running MySQL 5.7.16 on Ubuntu16.04 as backend for Wordpress site.
A lot of heavy, bad written SQLs.
I was able to improve situation significant by enabling MySQL cache, slow query log is almost empty now.
But - certain query doesn't use cache and I cannot understand  why.
SQL uses two tables, which are pretty static, so it's not cache invalidation.
Any idea how I can debug that?

TIA, Vitaly

1) SHOW STATUS LIKE "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1700     |
| Qcache_free_memory      | 49555584 |
| Qcache_hits             | 17960465 |
| Qcache_inserts          | 6928571  |
| Qcache_lowmem_prunes    | 1850967  |
| Qcache_not_cached       | 924283   |
| Qcache_queries_in_cache | 13303    |
| Qcache_total_blocks     | 28441    |
+-------------------------+----------+

2)Example of SQL:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts
  LEFT JOIN wp_postmeta
    ON ( wp_posts.ID = wp_postmeta.post_id )
  LEFT JOIN wp_postmeta AS mt1
    ON (wp_posts.ID = mt1.post_id
    AND mt1.meta_key = '_pagefrog_fbia_status' )
WHERE 1=1
  AND (     ( wp_postmeta.meta_key = '_pagefrog_fbia_status'
          AND wp_postmeta.meta_value = '1' )
       OR     mt1.post_id IS NULL
       OR   ( wp_postmeta.meta_key = '_pagefrog_fbia_status'
          AND wp_postmeta.meta_value = '' ) )
  AND wp_posts.post_type = 'post'
  AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

Addenda

SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+-----------------------------+----------+

When either table is modified, all entries in the QC are removed (pruned) from the QC for that table.
If the query has too big a resultset, it won't go into the QC.  (Please provide SHOW VARIABLES LIKE 'query_cache*';

Do you have this on wp_postmeta: PRIMARY KEY(post_ID, meta_key) ?  If you can switch to that, performance should improve when the QC is not used.  If you have trouble, let's see SHOW CREATE TABLE wp_postmeta; some WP versions are really lame when it comes to optimizing that schema.

Do you need the GROUP BY?  JOIN...GROUP BY often suffers from the explosion-implosion syndrome that makes such queries slow.  There may be a workaround.

Will you be "paginating"?  If so, use of OFFSET is inefficient.

Getting rid of SQL_CALC_FOUND_ROWS will speed up the query.

That is a confusing query, what is it trying to do?

Index

PRIMARY KEY (meta_id),         -- useless and slows things down
KEY post_id (post_id),         -- inadequate
KEY meta_key (meta_key(191))   -- prefixing is next to useless

Change to this

Change `meta_key` from VARCHAR(255) to VARCHAR(191)
PRIMARY KEY (post_id, meta_key)
no other indexes unless you ever search for meta_key without post_id

Notes:

You probably don't have very long keys, so changing to 191 may be safe.  You could run SELECT MAX(CHAR_LENGTH(meta_key)) FROM .. to see what the current max is.
If you can't shrink to 191, then try to upgrade to 5.6.3 / 5.5.14, which allows you to increase the limit (with some effort), or 5.7.7, which allows it by default.
'Composite' keys are often better than single-column keys, especially in this case.
Having the primary access pattern be the PK makes it faster.
These improvements may not help that particular contorted query, but it will help the table in other ways.

(I have been ranting about WP for years.  I would be happy to advise them.)


Importing Data with Single .ibd file I have one ".ibd" file with me. I have configured my local Database server with FILE_PER_TABLE option. i have two question here.

When i was trying to restore/import the IBD file, the engine expect me to have the structure information. Is it not possible to load the data and structure using Single IBD file?
Also can we do hot copy and restore the innodb files from one server to other as MyISAM engine?

Please need your suggestion on the same.

1) To get a single table restored and/or imported, use mysqldump. See the "how" links in my post here.

2) For a hot copy of InnoDB tables, use Perconas XtraBackup tool (BTW, the Percona website generally and their mysqlperformance blog are excellent MySQL resources).

You can also replicate from InnoDB masters to MyISAM slaves and the other way round (although why one would want to do this is a mystery to me).


SELECT Without wildcard returns 0 rows For some reason my SELECT query with an AND clause is exhibiting some odd behaviour. It returns zero rows, when I know for a fact that there are matches. If I remove either of the clauses, it works. Or, the strangest part, if I add a wildcard to the end of the string to match, it returns the expected results. Even though there doesn't appear to be any extra bytes at the end of the column data.

A demonstration:

SELECT HEX(type),
       type,
       category
  FROM cats
 WHERE `category` = 10 AND
       `type` LIKE "Furry";

Empty set (0.07 sec)

but

SELECT HEX(type),type,category FROM cats WHERE `type` LIKE "Furry";
+----------------------------------+------------------------+----------+
| HEX(type)                        | type                   | category |
+----------------------------------+------------------------+----------+
| 4675727279                       | Furry                  |       10 |
...

And

SELECT HEX(type),type,category FROM cats WHERE `category`=10 AND `type` LIKE "Furry%";
+----------------------------------+------------------------+----------+
| HEX(type)                        | type                   | category |
+----------------------------------+------------------------+----------+
| 4675727279                       | Furry                  |       10 |
...

What am I missing here?

Based on the execution plan, and the data distribution, MySQL would decide to scan the table or one of the indexes to get the required data.

If the data is to be read from an index, and the index is corrupted, it is possible that no data or wrong data is returned.

Optimizing the table will rebuild the table and its indexes. So, if an index was corrupted, it will be fixed after the optimization.

HTH


Problem with my gs startnode, Griddb I’m trying to start a GridDB node with gs_startnode command. But, there is an error: AD0103: Unable to start node

First make sure you have created a Cluster and a Node definition file, then re-run the command .This should fix this error.


How to test user permissions I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database only as admin. My admin is not sysadmin, because of problem with default schema (sysadmins have always default schema dbo, but i need something else), but I can set almost any permissions I need.
I proceed from this
For testing permissions there is HAS_PERMS_BY_NAME and it works well. So I tried to impersonate notadmin. I set IMPERSONATE ANY LOGIN permission to admin, and it works well. I test it this way (on master db):
SELECT HAS_PERMS_BY_NAME('notadmin', 'LOGIN', 'IMPERSONATE')  -- return 1
EXECUTE AS login = 'notadmin'
    SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE')  -- return 1
REVERT

Because I don't like ANY (this way I can impersonate someone with sysadmin role), I tried set this
GRANT IMPERSONATE ON LOGIN::admin to notadmin

and then a run same query. But it failed - returned 0 and error:
Cannot execute as the server principal because the principal "notadmin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Principal have to exist and can by impersonated, with IMPERSONATE ANY LOGIN it works. So which permission I need to impersonate notadmin?
Or is there some other way how can I test some other db user permission and do not allow impersonate any other users?

Edit (16.04.2021 12:13):
I find that HAS_PERMS_BY_NAME returns same values as system views in master db. So SELECT * FROM sys.server_permissions is useless

GRANT IMPERSONATE ON LOGIN::admin to notadmin is telling SQL Server that you want to give the IMPERSONATE permission to the notadmin account so that it can IMPERSONATE other users. It sounds like your goal is the opposite, that you want to impersonate the notadmin account from the admin account. You already are accomplishing this when you run the SQL code EXECUTE AS LOGIN = 'notadmin' (when you're logged in as your admin account).


How do I identify all super users in SQL Server? For a security audit,I need to identify all super users and log every activity performed. Best approach for this?

By "super user" I mean sysadmin, serveradmin, securityadmin, processadmin, and dbcreator.

You can use this code:

SELECT   name,type_desc,is_disabled,
         (IS_SRVROLEMEMBER ('sysadmin',name)) AS ISSRVROLE
FROM     master.sys.server_principals
-- WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name ;

sys.server_principals - Contains a row for every server-level principal

IS_SRVROLEMEMBER - Indicates whether a SQL Server login is a member of the specified server role.

You would also need to check for the sysadmin-equivalent CONTROL SERVER permission for the server principal in sys.server_permissions.

IS_SRVROLEMEMBER can return:

0 -  login is not a member of role.
1 - login is a member of role.
NULL - role or login is not valid, or you do not have permission to view the role membership.


Question about NULL values in SQL Consider the following relation instance myTable

num1    num2
100     100
100     NULL
NULL    100
NULL    NULL

Query 1:

SELECT *
FROM myTable
WHERE num1 != NULL;

Query 2:

SELECT *
FROM myTable
WHERE num1 = NULL;

I know that NULL values are looked up using IS NULL and IS NOT NULL.
But I want to know how are the above two queries evaluated. What is the number of tuples outputted by Q1 and Q2 by the ANSI standards?

NULL means that there is no value for that column of the record and it does not equal anything. So in both of your queries nothing can equal NULL.

Wiki Entry on Null

  The Greek lowercase omega (ω) character is used to represent Null in database theory.
  Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker.


How can I optimize a mysqldump of a large database? I have a symfony application with an InnoDB database that is ~2GB with 57 tables.  The majority of the size of the database resides in a single table (~1.2GB).  I am currently using mysqldump to backup the database nightly.

Due to my comcast connection, oftentimes if I am running a dump manually my connection to the server will timeout before the dump is complete causing me to have to rerun the dump.  [I currently run a cron that does the dump nightly, this is just for dumps that I run manually.]

Is there a way to speed up the dumps for the connection timeout issue, but also to limit the time the server is occupied with this process?

BTW, I am currently working on reducing the size of the overall database to resolve this issue.

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways:

Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly flicking between the location being read from and the location being written to.
The output of mysqldump will be very compressible, so if you can not separate the output from the input as mentioned above pipe the output through gzip or similar. This will reduce the amount of writing being done (so reduce the overall IO load, and the amount of head movement) at the expense of some CPU time (which you may have a lot of spare at these times anyway).
Also, (as well or instead of compression) pass the output through a pipe utility (like pv) that supports large write buffers to group blocks written to the drives together more, again to reduce the effect of head-movement latency - this will make quite a difference if using the --quick option to reduce the RAM impact of backing up large tables).
Only run your backup process when IO load is otherwise low.

You may be fixing the wrong issue though: it might be easier to address the connection drops instead (though reducing the I/O load imposed by your backups will help reduce the effect you have on other users so is worth trying anyway). Could you run your manual backups through screen (or similar tools like tmux)? That way if your connection to the server drops you can just reconnect and reattach to the screen session without any processes getting interrupted.

If you are sending the data directly over the connection (i.e. you are running mysqldump on your local machine against a remote database, so the dump appears locally) you might be better off running the dump on the server first, compressing as needed, then transferring the data over the network using a tool (such as rsync) which supports partial transfers so you can resume the transfer (instead of restarting) if a connection drop interrupts it.

As part of your "reducing the size of the overall database to resolve this issue" I would guess that a large chunk of your data does not change. You might be able to move a large chunk of the 1.2Gb from that main table off into another and remove that from those that are copied by the mysqldump call. You don't need to backup this data every time if it never changes. Splitting data between tables and databases this way is usually referred to as data partitioning and can also allow you to spread the data and I/O load over multiple drives. High-end database have built in support for automatic partitioning, though in mysql you will probably have to do it manually and alter your data access layer to account for it.

Straying off-topic for this site (so you should probably nip over to ServerFault or SuperUser to ask if you need more detail): If you seem to be losing connections due to inactivity, check the options in your SSH server and SSH client to make sure keep-alive packets are enabled and being sent often enough. If seeing drops even if the connection is active you could also try using OpenVPN or similar to wrap the connection - it should handle a short drop, even a complete drop if your entire connection is down for a few seconds, such that the SSH client and server don't notice.


Pivoting with NULL Dates I'm attempting the pivot below:

SELECT
   EmpID as ID,
   [1] as January,
   [2] as February,
   [3] as March,
   [4] as April,
   [5] as May,
   [6] as June,
   [7] as July,
   [8] as August,
   [9] as September,
   [10] as October,
   [11] as November,
   [12] as December,
   isnull((processeddate),' ') as 'Null'
FROM
(
  SELECT EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth
  FROM [dbo].[Invoices] where Inv_Number not in ('1035','1039')
)
AS source
PIVOT
(
  sum(EmpDeductAmt) for TMonth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
)
AS pvtmonth
ORDER BY EmpID;

However, I receive an error message of "Invalid column name 'processeddate' upon hitting F5.

I'm fairly green, but the code worked beautifully when the isnull((processed date),' ') as 'Null' code was left off.

The desired output would consist of columns pertaining to each month in addition to a column for NULL months, as some of the records in my dataset have NULL as their date. Failure to account for the NULL dates shortchanges the dollar amount arrived out. The rows  are arranged by employee identification number.

Is there a suggested remedy for this issue?

Many, many thanks!

So at this point in the code:

isnull((processeddate),' ') as 'Null'

The column processeddate is not visible to SQL Server, because you are selecting available fields from the result of the following query:

SELECT EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth
  FROM [dbo].[Invoices] where Inv_Number not in ('1035','1039')

...along with the pivoted columns [1] through [12].

You either need to include ProcessedDate in the above query, or exclude it from the outer SELECT.


Restoring a database that belonged to an AG I'm trying to perfect our disaster recovery steps...

We take backups of a database that, in production, is in an AG.  It is the only database in the AG.

If we lose the building, I need to be able to restore it to a new instance.  Initially, for time's sake, this instance would not be running in an AG.

Of course, if you try a normal restore, you get error Msg 3104: "RESTORE cannot operate on database 'SI_EDI_PRD' because it is configured for database mirroring or has joined an availability group."

And, since the instance is not configured for AlwaysOn, you can't try to run the ALTER AVAILABILITY GROUP command.

How can one restore this database??

Looks like Brent answered it in a thread I hadn't seen:
SQL Availability Group restore database to standalone server

Short answer: Don't restore the master database of an AG.

I'm not happy with this....But it'll do.

Thanks, everyone!


MySQL is using an Index not listed in possible_keys In a very basic setup, I have a table with some fields:

firstname (VARCHAR)
lastname (VARCHAR)
country (INT)
email (VARCHAR)

I created in Index over 2 fields:

CREATE INDEX countr_email_idx ON person (country, email)

This is a simple example from school, so no assumptions on whether the index is of any use should be made.

Now when I query a simple Select over the email field, the MySQL documentation states that because it is not the left-most attribute of my index, it can't be used. This is also reflected in the empty possible_keys value when i run the query with EXPLAIN:

EXPLAIN SELECT passenger_id, email
FROM passengerdetails
WHERE email LIKE 'bob%'

which yields

id | select_type | table            | type  | possible_keys | key              | key_len | ref  | rows  | Extra
1  | SIMPLE      | passengerdetails | index | NULL          | countr_email_idx | 225     | NULL | 36370 | Using where; Using index

However, countr_email_idx is listed as the index that is used after all. Why does this happen?

The documentation for MySQL has the following bit of information regarding the EXPLAIN Output Format:

  It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

So while an index seek is not expected, MySQL will still retrieve the data via the index rather than via a normal data row scan.

Addendum regarding primary key indexes

On the assumption that you are using the InnoDB engine and that the PK on the passengerdetails tables is based on the passenger_id, then all other secondary indexes will be based on this primary key as stated in the article 14.11.9 Clustered and Secondary Indexes in the official documentation.

Even if you didn't create a primary clustered index yourself, MySQL will do it for you to ensure speedy queries. This is documented as:

  If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

and:

  If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.


Storing Different Products and Joining on table Names I am setting up a database using mysql to store different products with different product types.

For example I could have Books, Software and clothes etc.

What I am think of is creating a parent table (product_table with the structure:

ID |TYPE|

Then Having a TYPE table which would be:

ID |TYPE |Product_ identifier_Table

So then the Product_ identifier_Table would contain the name of the table the which contains the product details.

Would this be the correct way of setting up the database?

I'm not sure why you need different tables for different product details. I'm going to guess though, that it's because your product types are so different that they have very different attributes (clothing will have sizes: S, M, L whereas books will have have an indicator of hardcover vs. paperback, etc...). You might want to try a more generic solution, where you have a table of product attribute types and another table of attributes. Something like this:

product
-------
  product_id
  name

product_attribute_type
----------------------
  attribute_type_id
  attribute_type_name

product_attribute
-----------------
  product_attribute_id
  attribute_type_id (FK to product_attribute_type)
  product_id (FK to product)
  value

Then you can have data that looks like this

product
-------
ID | name
-----------
1  | Awesome pants
2  | Book of cool stuff
3  | 1337 software

product_attribute_type
----------------------
ID | attribute_type_name
------------------------
1  | waist size
2  | number of pages
3  | system requirements
4  | inseam

product_attribute
-----------------
attribute_type_id | product_id | value
--------------------------------------
1                 | 1          | 33 inches
4                 | 1          | 34 inches
3                 | 3          | Win7, 4 GB RAM
2                 | 2          | 703 pages

Getting info on a product is easy:

select product.name, product_attribute.value, product_attribute_type.name
from product
inner join product_attribute on product.product_id = product_attribute.product_id
inner join product_attribute_type on product_attribute_type.attribute_type_id = product_attribute.attribute_type_id
where product.product_id = $PRODUCT_ID;


AlwaysOn Availability Group Forced Failover May I request your input.

I setup SQL 2012 with always on availability group for my databases in sync and secondary readable mode. I completed failover tests with sync mode and it is functioning as expected.

After a forced failover testing with async mode, do I need to rebuild my old primary database? Because changes in the new primary are not reflected back. The database status is not synchronized...instead of synchronizing...

Appreciate your input as I get my head around this concept.

Thank you

After a forced failover testing with async mode, do I need to rebuild my old primary database?

I'm not sure exactly what you mean by "rebuild" the database, but provided the databases are still in a working condition then you shouldn't need to take any actions like that.
What you're seeing, by performing a forced failover, is by design.  If you do a forced failover, you could potentially have failed over to a replica that isn't completely caught up or at the same point-in-time as the primary replica.  Because of that, data movement is suspended to the secondary replica(s) from the now primary replica so there is a way to have manual intervention if you are now on a database that is "behind".  That behavior that you are seeing is a good thing.
This BOL reference explains it all:

After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.
When a secondary database is resumed, it initiates data synchronization with the corresponding primary database. The secondary database rolls back any log records that were never committed on the new primary database. Therefore, if you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one of the synchronous-commit secondary databases.

Please see this BOL reference on how to resume an AG database.
The T-SQL for this would be:
alter database YourDatabaseName
set hadr resume;

NOTE/WARNING/DISCLAIMER: You really need to do some leg work to ensure that you are not causing data loss by resuming data movement.  See above, it could be a huge problem.  The point of suspending data movement is for this very reason:  To manually make sure you can recover as much data as possible.  When you resume data movement, that could be irreversible.  When you resume data movement after a forced failover, you have to have the words "potential data loss" in the forefront of your mind always.


selecting from sys.dm_db_missing_index_details will start to return rows, and then sits and spins I am trying to run some analysis on missing indexes on a server (I haven't ever done it on this server before).  I was running sp_blitzindex and it would hang up on "Inserting data into #MissingIndexes."  I dug into the query and found where it hits sys.dm_db_missing_index_details.

Trying to select from sys.dm_db_missing_index_details will return about 300 records, and then it'll sit and spin.  I didn't want to let it keep going to see how long it would take to finish, but I let it run for over two minutes and it didn't complete.

Why would this take a long time to return?  What would cause this?

This sounds like other sessions are blocking the dynamic management view.

CREATE TABLE and DROP TABLE force an update to the internal tables maintained for the sys.dm_db_missing_index_details.

I was able to recreate a blocking scenario where an open transaction prevents reads against sys.dm_db_missing_index_details.  Run this in an SSMS window on your development machine:

BEGIN TRANSACTION
IF OBJECT_ID(N'dbo.mid_test_2', N'U') IS NOT NULL
DROP TABLE dbo.mid_test_2;
IF OBJECT_ID(N'dbo.mid_test', N'U') IS NOT NULL
DROP TABLE dbo.mid_test;
CREATE TABLE dbo.mid_test
(
    ID int NOT NULL PRIMARY KEY CLUSTERED
    , SomeVal varbinary(2000) NOT NULL DEFAULT (CRYPT_GEN_RANDOM(2000))
);

CREATE TABLE dbo.mid_test_2
(
    ID int NOT NULL FOREIGN KEY REFERENCES dbo.mid_test(ID)
    , SomeVal varbinary(2000) NOT NULL DEFAULT (CRYPT_GEN_RANDOM(2000))
);
GO

;WITH src AS
(
    SELECT v.num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.mid_test (ID)
SELECT s1.num
    + (s2.num * 10)
    + (s3.num * 100)
    + (s4.num * 1000)
    + (s5.num * 10000)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
ORDER BY 1;

;WITH src AS
(
    SELECT v.num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.mid_test_2 (ID)
SELECT s1.num
    + (s2.num * 10)
    + (s3.num * 100)
    + (s4.num * 1000)
    + (s5.num * 10000)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
ORDER BY 1;

SELECT *
FROM dbo.mid_test m1
    INNER JOIN dbo.mid_test_2 m2 ON m1.ID = m2.ID
WHERE m2.ID = 66666

Now, in another SSMS window, run the following (you may need to run it several times depending on how quick you are!)

SELECT *
FROM sys.dm_db_missing_index_details;

For me, the above query blocks until I either commit or rollback the transaction started in the first query window, with:

COMMIT TRANSACTION

or

ROLLBACK TRANSACTION

Unfortunately, running SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; prior to querying sys.dm_db_missing_index_details does not help; it still blocks until the open transaction commits or rolls back.


decode(null,null,'true','false') = 'true'? Today my database world was shattered.

For my whole lifetime I thought, comparing with a null value in Oracle results in the "false-case".

But not so for the decode function.

Anybody know, what's the idea behind this?

DECODE

  In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.


Row-row based logging in a blackhole replication filter setup? I am using two MySQL server instances on the same server to filter replication to a third external server. My filter slave is using the blackhole engine as described here: https://dev.mysql.com/doc/refman/5.7/en/blackhole-storage-engine.html

Both master and slave use statement based replication. The documentation says:

  Inserts into a BLACKHOLE table do not store any data, but if statement
  based binary logging is enabled, the SQL statements are logged and
  replicated to slave servers. This can be useful as a repeater or
  filter mechanism.

The above statement makes me assume that if I had both of my MySQL instances set to row based replication, nothing would make it to the third, external database. Which kind of makes sense since there are no actual rows in the filtering blackhole database.

However, I have been thinking... Would it not be possible for the filtering middle instance to simply pass on any row based instructions it receives to its own binlog, i.e. would a row-row filtering blackhole setup not work?

https://dev.mysql.com/doc/refman/8.0/en/replication-features-blackhole.html -->

The BLACKHOLE storage engine accepts data but discards it and does not store it. When performing binary logging, all inserts to such tables are always logged, regardless of the logging format in use. Updates and deletes are handled differently depending on whether statement based or row based logging is in use. With the statement based logging format, all statements affecting BLACKHOLE tables are logged, but their effects ignored. When using row-based logging, updates and deletes to such tables are simply skipped—they are not written to the binary log. A warning is logged whenever this occurs.
For this reason we recommend when you replicate to tables using the BLACKHOLE storage engine that you have the binlog_format server variable set to STATEMENT, and not to either ROW or MIXED.


Inconsistent statistics on jsonb column with btree index I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly after analyzing the table.

I'm using Postgres 9.6 here. The setup for my tests is the following, I'm inserting a single key "x" into the jsonb column "params" with values between 1 and 6, with 1 being the rarest value and 6 the most common. I also have a regular int column "single_param" that contains the same distribution of values for comparison.:

CREATE TABLE test_data (
    id      serial,
    single_param    int,
    params      jsonb
);

INSERT INTO test_data
SELECT
    generate_series(1, 1000000) AS id,
    floor(log(random() * 9999999 + 1)) AS single_param,
    json_build_object(
        'x', floor(log(random() * 9999999 + 1))
    ) AS params;

CREATE INDEX idx_test_btree ON test_data (cast(test_data.params->>'x' AS int));
CREATE INDEX idx_test_gin ON test_data USING GIN (params);
CREATE INDEX ON test_data(id)
CREATE INDEX ON test_data(single_param)

The query I'm testing is a typical query for paginating results, I'm sorting by id and limiting the output to the first 50 rows.

SELECT * FROM test_data where (params->>'x')::int = 1 ORDER BY id DESC LIMIT 50;

I get one of the two explain analyze outputs randomnly after running VACUUM ANALYZE:

Limit  (cost=0.42..836.59 rows=50 width=33) (actual time=39.679..410.292 rows=10 loops=1)
  ->  Index Scan Backward using test_data_id_idx on test_data  (cost=0.42..44317.43 rows=2650 width=33) (actual time=39.678..410.283 rows=10 loops=1)
        Filter: (((params ->> 'x'::text))::integer = 1)
        Rows Removed by Filter: 999990"
Planning time: 0.106 ms
Execution time: 410.314 ms

or

Limit  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.034 rows=10 loops=1)
  ->  Sort  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.032 rows=10 loops=1)
        Sort Key: id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using idx_test_btree on test_data  (cost=0.42..8.44 rows=1 width=33) (actual time=0.007..0.016 rows=10 loops=1)
              Index Cond: (((params ->> 'x'::text))::integer = 1)
Planning time: 0.320 ms
Execution time: 0.052 ms

The difference is that the estimate for the number of columns matching the where clause is different between the two plans. In the first one the estimate is 2650 rows, in the second one 1 row while the real number is 10 rows.

The following version of the query that can potentially use the GIN index does seem to use a default estimate for the json column of 1%, which also results in the bad query plan like above:

SELECT * FROM test_data where params @> '{"x": 1}' ORDER BY id DESC LIMIT 50;

My original assumption was that Postgres would not have any statistics on the jsonb column and always use an estimate like it does for the query using the @> operator. But for the query that is written to be able to use the btree index I created, it uses different estimates. Sometimes those are good enough, and sometimes they're bad.

Where do those estimates come from? I guess they're some kind of statistic Postgres creates with the index. For column statistics there is the option to collect more accurate statistics, is there anything like that for these statistics here? Or any other way to get Postgres to pick the better plan in my case?

Currently (version 9.6), Postgres does not have any statistics about the internals of document types like json, jsonb, xml or hstore. (There has been discussion whether and how to change that.) Instead, the Postgres query planner uses constant default frequency estimates (like you observed).

However, there are separate statistics for functional indexes like your idx_test_btree. The manual has this tip for you:

  Tip: Although per-column tweaking of ANALYZE frequency might not be
  very productive, you might find it worthwhile to do per-column
  adjustment of the level of detail of the statistics collected by
  ANALYZE. Columns that are heavily used in WHERE clauses and have
  highly irregular data distributions might require a finer-grain data
  histogram than other columns. See ALTER TABLE SET STATISTICS, or
  change the database-wide default using the default_statistics_target
  configuration parameter.

  Also, by default there is limited information available about the
  selectivity of functions. However, if you create an expression index
  that uses a function call, useful statistics will be gathered about
  the function, which can greatly improve query plans that use the
  expression index.

The volume of statistics gathered depends on general setting of default_statistics_target, which can be overruled with a per-column setting. The setting for the column automatically covers depending indexes.

The default setting of 100 is conservative. For your test with 1M rows, if data distribution is uneven, it may help to increase it substantially. Checking on this once more I found you can actually tweak the statistics target per index column with ALTER INDEX, which is currently not documented. See related discussion on pgsql-docs.

ALTER TABLE idx_test_btree ALTER int4 SET STATISTICS 2000;  -- max 10000, default 100

Default names for index columns are not exactly intuitive, but you can look it up with:

SELECT attname FROM pg_attribute WHERE attrelid = 'idx_test_btree'::regclass

Should result in the type name int4 as index column name for your case.

The best setting for STATISTICS depends on several factors: data distribution, data type, update frequency, characteristics of typical queries, ...

Internally, this sets the value of pg_attribute.attstattarget, and the exact meaning of this is (per documentation):

  For scalar data types, attstattarget is both the target number of
  "most common values" to collect, and the target number of histogram
  bins to create.

Then run ANALYZE if you don't want to wait for autovacuum to kick in:

ANALYZE test_data;

You must ANALYZE the table, since you cannot ANALYZE indexes directly. Check with (before and after if you want to verify the effect):

SELECT * FROM pg_statistic WHERE starelid = 'idx_test_btree'::regclass;

Try your query again ...

Related:

Check statistics targets in PostgreSQL
Index that is not used, yet influences query
PostgreSQL partial index unused when created on a table with existing data


Multiple left joins with common fields are overwritten Tables
transactions t

transaction_type
transaction_name

sale
one

sale
two

sales s

name
amount

one
10.0

two
5.0

purchases p

name
amount

Wanted

transaction_type
transaction_name
amount

sale
one
10.0

sale
two
5.0

Tried
select t.transaction_type, t.transaction_name, s.amount, p.amount
from transactions t
left join sales s on t.transaction_type='sale' and t.transaction_name=s.name
left join purchases p on t.transaction_type='purchase' and t.transaction_name=p.name

Got

transaction_type
transaction_name
amount

sale
one
null

sale
two
null

I understand that the outer join of the purchases is overwriting the amounts from the sales because of the common field, but what is the best approach in this case?
Note that there will be more tables to join like this. This is mysql in case it makes a difference, the query has been paraphrased and there is an orm wrapper.
I know I can do it with separate queries, inner joins and unions, is that best? I was hoping to do it in one query.
TIA!

You can use Coalesce to show s.amount or p.amount which one is not null:
Query:
select t.transaction_type, t.transaction_name, coalesce( s.amount, p.amount) Amount
from transactions t
left join sales s on t.transaction_type='sale' and t.transaction_name=s.name
left join purchases p on t.transaction_type='purchase' and t.transaction_name=p.name

create schema:
create table transactions(transaction_type  varchar(30),transaction_name varchar(30));
insert into transactions values('sale','one');
insert into transactions values('sale','two');
insert into transactions values('purchase','two');

create table sales (name    varchar(30), amount float);
insert into sales values('one', 10.0);
insert into sales values('two', 5.0);

create table purchases (name    varchar(30),amount float);
insert into purchases values('two', 7.0);

Check the db-fiddle link:
https://www.db-fiddle.com/#&togetherjs=E7luJAKsF5


Date based partitioning I have a table with a last_update column, I want to add a partition based on the last_update column to remove old values.

My problem is that I have a unique key in the table which does not include the last_update column.

What are my options?

Should I change the way I work with this table and remove the unique index?

EDIT

CREATE TABLE `fileso` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hostId` int(11) unsigned NOT NULL COMMENT 'code',
  `sha256` binary(32) NOT NULL COMMENT 'meir',
  `fileName` varchar(150) DEFAULT NULL COMMENT 'meir',
  `fullPath` varchar(350) DEFAULT NULL COMMENT 'meir',
  `datein` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueness` (`uniqueness`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The unique key is an Hash representation of a couple of columns in the table(sha256, fullPath,hostId).

If you are going to remove 'old' records, then you are defeating the "uniqueness" of uniqueness -- since you cannot check against already-deleted rows.

So, make that just a INDEX, not UNIQUE.

Meanwhile, of what use is id?  You may as well promote uniqueness to be the PRIMARY KEY.  (Yeah, this contradicts my previous recommendation.)

But, back to the question of PARTITIONing forcing the inclusion of last_update on all unique keys...

Plan A:

no `id`
PRIMARY KEY (uniqueness, last_update)

Uniqueness would need to be checked with a SELECT, not INSERT IGNORE.

Plan B:

no `id`
INDEX (uniqueness)

This has problems in Replication and other situations when you need to specifically identify a row.

Plan C:

keep `id`
PRIMARY KEY(id, last_update)
INDEX(uniqueness)

Uniqueness would need to be checked with a SELECT, not INSERT IGNORE.

I prefer Plan A, unless there is somthing to indicate that id is necessary.

Meanwhile, see my advice on using PARTITIONing for a sliding dataset -- here

Addenda

With Plan A, do something like this to prevent a race condition:

BEGIN;
$result = SELECT uniqueness FROM tbl WHERE uniqueness = $x FOR UPDATE;
if $result, you have a dup; ROLLBACK and do whatever is appropriate.
INSERT INTO tbl (...) VALUES (...);
COMMIT;

Be sure to check for errors at each step -- deadlocks and timeouts can happen (rarely).  If so, start over at the BEGIN.

If practical, consider batching the SELECT and INSERT so you can check and insert multiple rows at the same time.  (I assume dups are rare?)


How to get the SQL-server login name of an integrated security account? Let's say I'm a user in windows domain DC (DataCenter).
My user-name is DC\Somebody
I'm in group DC\Developers
Now SQL-Server has the login group called DC\Developers, and with that, I can login on the server with integrated security.

I'm now trying to get the SQL-Server login (by which I mean DC\Developers), but I can't figure out how. All I can get is the username (DC\Somebody).
But I want to get DC\Developers.
How can I do that ?

This are the functions I tried so far, without success:

SELECT -- http://blog.sqlconcepts.co.nz/2011/07/who-am-i.html
        ORIGINAL_LOGIN() AS OriginalLogin
       ,SUSER_SNAME() AS CurrentContext
       ,SESSION_USER AS SessionUser
       ,SYSTEM_USER AS SystemUser
       ,CURRENT_USER AS CurrentUser
       ,user AS "WhichUserIsThat?"
       ,ORIGINAL_LOGIN() AS OriginalLogin
       ,USER_NAME() AS UserName

Also, what happens if multiple groups are added as sql-server logins, and I'm a member of them all, and then I login with integrated security. Which login will it take ? It appears to be the alphabetically first ...

To get the AD groups with exsting logins on the instance that your login DC\Somebody belongs to

xp_logininfo

EXEC xp_logininfo 'DC\Somebody','all';

sys.login_token

SELECT DISTINCT lt.name
FROM sys.login_token lt
INNER JOIN  sys.server_principals  sp
ON lt.principal_id = sp.principal_id AND  sp.type != 'R'
WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL)
AND sp.type != 'R';

An example to validate this for other logins with impersonation

EXECUTE AS LOGIN ='DOMAIN\a-rvertongen';

SELECT DISTINCT lt.name
FROM sys.login_token lt
INNER JOIN  sys.server_principals  sp
ON lt.principal_id = sp.principal_id
WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL)
AND sp.type != 'R';

REVERT;

Result

name
DOMAIN\a-rvertongen
DOMAIN\SERVER_RO
DOMAIN\SERVER_sysadmin
BUILTIN\Administrators

  Also, what happens if multiple groups are added as sql-server logins,
  and I'm a member of them all, and then I login with integrated
  security.Which login will it take ?

Permissions for multiple AD groups

You will be granted the GRANT / DENY rights of each  AD Group.
DENY take precedence over GRANT permissions.

A more thorough write up in a previous answer by  @Max Vernon can be found here.


What is the best way to reload data in MongoDB I am benchmarking MongoDB using YCSB framework using the latest drivers.

During the benchmark, I change the cluster setup (adding/removing shards) extensively and delete/insert workloads in the order of 5GB regularly. To try and make this process quicker I have tried two approaches,

In the first approach I use db.collection.remove() function to remove documents from the data which takes around 20 mins to remove 5GB of documents from one shard. This method preserves the indexes, which results in an increased insertion throughput.

In the second approach I use db.collection.drop() function to remove documents from the data which almost instantly removes the data. However, insertion takes much longer due to balancing the data across shards.

Is there a better approach to do these tasks? If not, which one of the two
is recommended?

I'd recommend looking into pre-splitting and/or using a hashed shard key to do the insertion and stick with dropping the collection (with remove you are basically doing a delete for every write, so it will always be slow). The hashed shard key is usually the easiest one to get started with.

If you are looking to measure write throughput then each of those approaches (when done properly) will allow you to utilize all available shards immediately rather than hitting one shard first, waiting for the balancer to move data etc. (also, if you use a monotonically increasing shard key then you will only ever write to a single shard - see the three pitfalls posts for good descriptions of all that).


Can't add Foreign Key Constraint As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error.

CREATE TABLE Job
(
    JobNumber INT NOT NULL,
    JobName CHAR(30) NOT NULL,
    JobDescription CHAR(60) NOT NULL,
    Title Char(30) NOT NULL,
    PersonID INT NOT NULL,
   CONSTRAINT PK_JobNum PRIMARY KEY (JobNumber)
);

CREATE TABLE Employee
(
    PersonID INT NOT NULL,
    FirstName CHAR(30) NOT NULL,
    LastName CHAR(30) NOT NULL,
    CONSTRAINT Pk_PersonID PRIMARY KEY(PersonID),
    CONSTRAINT FK_Job_2_Employee FOREIGN KEY(PersonID) REFERENCES Job(PersonID)
);

CREATE TABLE Game
(
    GameNumber INT NOT NULL,
    Title CHAR(30) NOT NULL,
    System CHAR(30) NOT NULL,
    CONSTRAINT PK_GameNumber PRIMARY KEY(GameNumber),
    CONSTRAINT FK_Job_2_Game FOREIGN KEY(Title) REFERENCES Job(Title)
);

Error comes up when I try to initialize the second table. (And it'll probably come up on the third.)

Multiple people can have the same job, and a person can have multiple jobs.
The JobNumber and the JobName aren't the same thing. Each JobNumber only has one employee, And A single JobNumber has only one JobName.
The JobNumber and what job a person has is not the same thing.
The job number and the kind of job isn't the same thing. For example Both Job Number 1235 and 1435 could both have the job "level designer."

It appears you are putting the foreign key on the wrong table.  The column PersonID on the Jobs table should reference the PersonID on the Employee table.

If you are assigning persons to jobs, then your original foreign key should be on JobId.  This would require adding the JobId to the Employees table and removing the PersonId from the Jobs table.

If Employees can have many Jobs and Jobs can have more than one Employee, then you will want a join table.  It will have foreign keys to both the Jobs and Employees table.  The primary key of the join table will include both the PersonId and JobId.


How to get max sequence of rows? I have a table with statistic of games which has next rows (I only write about rows that has interest in this context):

id        BIGINT PRIMARY KEY
date      TIMESTAMP WITH TIME ZONE
status    VARCHAR  -- 'WON' or 'LOSE'
player_id BIGINT   -- FOREIGN KEY

I need to write a query that select number of consecutive win games (status = 'WON') by some user (user_id = %some id%).

A case for window functions:

SELECT count(*) As longest_streak
FROM  (
   SELECT row_number() OVER w
          - count(status = 'WON' OR NULL) OVER w AS grp
   FROM   game
   WHERE  player_id = 1          -- %some id%
   WINDOW w AS (ORDER BY date)
   ) sub
GROUP  BY grp
ORDER  BY count(*) DESC
LIMIT  1;

Or use the aggregate FILTER clause in Postgres 9.4 or later:

...
   SELECT row_number() OVER w
          - count(*) FILTER (WHERE status = 'WON') OVER w AS grp
...

Return counts for multiple ranges in a single SELECT statement

We count rows ordered by date and subtract the running number of wins. If the next row is a win, it still falls in the same group grp. Then get the maximum count per group.

Detailed explanation:

Select longest continuous sequence


SQL Server mirroring / failover alternative I am looking at building at reliable database with Microsoft SQL Server, with mirroring and automatic failover. The SQL Server here is standard version, not enterprise one so High Availability Solutions is not supported.

Just wonder if there is any third-party tool we can use as an alternative?

It's SQL Server 2014 I'm using.

I'm not sure I understand, you state you are going to use mirroring, in standard it is still available so why can't you use that?  Or is the question that is there anything else available?

You can also use Failover clustering in standard edition, the limit is you can only use 2 nodes but I don't see that as an issue for your request.

Also, if you are using any virtualization technology (VMware, Hyper-V etc) there may be options that you can speak to your virtualization about which are edition (not to mention software) agnostic.

Mat


Same product name with different price in different states Consider the price of products in the table differs in each state. If the price varies in district I also have to mention it. How can I do this for more than 5 states?

product Name   Price  Wholeseller Price Retailer price   state     district
-------------|------|------------------|---------------|-----------|------
fan          |   6  |   12         |     18            | delhi      name1
wheels       |   10 |   17         |     27            | delhi      name1
biscuits     |   5  |   10         |     23            | delhi      name1
biscuits     |   6  |   11         |     24            | delhi      name2
fan          |   7  |   13         |     20            | karnataka  ...
wheels       |   9  |   16         |     26            | karnataka  ...
biscuits     |   5  |   12         |     25            | karnataka  ...

Image of table here.

Here I'm repeating the product for each state. Is it a correct method? Should I make a separate table for states & districts or is there a need to normalize?

You should normalize your tables.  In third normal form (3NF) you tables would look like this:

Notice that this is different from your normalized table picture in two important respects:  First, states and districts should be separated into two tables so you don't repeat the state names.  Second, and more importantly, prices depend on district and product.  Therefore the price should be in the intersection table between district and product, not in the same table as states and districts.


Is it possible to disable all mysql_connect(): Too many connections emails, but not other errors? Right now I get about 4000 emails daily from this error as I run a DB backup that clogs up the DB for a few mins ant these emails are getting enormous.

Wondering if its possible to stop mysql from sending me emails about this

The answer is that, disable your mailer on the script that checking for this error. Look for the script and comment out the mailer.


How to select maximum row value using optimal way I have a large table:
+----+-----------+------------+--------+--------+--------+
| Id | lClientId | datAccess  | lProp1 | lProp2 | lProp3 |
+----+-----------+------------+--------+--------+--------+
|  0 |         5 | 10/10/2020 |        |        |      9 |
|  1 |         5 | 10/11/2020 |      2 |        |        |
|  2 |         5 | 10/12/2020 |      2 |        |        |
|  3 |         5 | 10/12/2020 |      3 |     10 |        |
|  4 |         6 | 10/13/2020 |      3 |     10 |      8 |
|  5 |         6 | 10/29/2020 |        |     11 |        |
|  6 |         6 | 10/15/2020 |      9 |     13 |        |
|  7 |         6 | 10/16/2020 |        |        |     16 |
+----+-----------+------------+--------+--------+--------+

I need to extract each prop for each clients (lProp1 - lProp3 fields) accociated with the last date with the last date of access (datAccess can be repeated, in this case i need the last one (by Id)). I.e. the desired result is:
+-----------+------------+--------+--------+--------+
| lClientId | datAccess  | lProp1 | lProp2 | lProp3 |
+-----------+------------+--------+--------+--------+
|         5 | 10/12/2020 |     3  |   10   |      9 |
|         6 | 10/29/2020 |     9  |   11   |     16 |
+-----------+------------+--------+--------+--------+

I tried to create several subqueries with SELECT which selects the maximum of each column with corresponding maximum date but the performance is falling down. Is there a way to select desiried values by single query? Because this is very expensive to go thru all the rows in table each time it search maximum value of date. The single pass is enough to get desiried values.
I use ADODB and VBA so maybe it's better to go thru all the rows and extract desiried fields manually? Using Recordset.MoveNext i can check all the fields by using single pass. Just i thought the engine is faster than if i access each field by Recordeset/Variant/etc.
The database is MS ACCESS.
Thanks in advance.

This is the SQL view of a msaccess query for your table
SELECT
    t1.[lClientId]
    ,MAX(t1.datAccess) AS datAccess
    , MAX((SELECT TOP 1  t2.[lProp1] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp1] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp1] DESC))  AS lProp1
    , MAX((SELECT TOP 1  t2.[lProp2] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp2] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp2] DESC))  AS lProp2
    , MAX((SELECT TOP 1  t2.[lProp3] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp3] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp3] DESC))  AS lProp3
FROM Table1 AS t1
GROUP BY t1.[lClientId];

Result

lClientId
datAccess
lProp1
lProp2
lProp3

5
10.12.2020
3
10
9

6
29.10.2020
9
11
16


How Does SSIS Manage Connection Traffic? I recently started using SSIS in my internship and I am still learning. We have a dedicated SSIS server that executes all of the ETL jobs and does not hold any other databases. We have many SQL servers as source and destination.

How does SSIS manage data transfer and transformations from source to destination when it is just executing the job and package? Does it establish a direct connection between the two or is the data channeled through the SSIS server?

Do you recommend adding SSIS to sources and/or destination servers and running jobs locally?

The machine, or in this case SSIS server, that calls the DTSX package will be where it executes, using its local resources (i.e. CPU and RAM) to channel data through the SSIS server.  The ETL transformations will be done on the SSIS server, again using its resources. The remote machines with data sources and destinations will still see disk IO or ram increase, but not much else.

Executing ETL jobs on database servers might not be the best idea depending on what you're doing, as this could cause the database server to be slow. Just look at what you're trying to accomplish and go from there.

I've read and used as a reference WROX's Professional Microsoft SQL Server 2012 Integration Services.  Great resource if you're going to be using SSIS a lot.

Understanding the Differences between Local and Remote Execution


Select any variant of

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c, m
    WHERE c.type = 1
      AND m.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON m.type = 1
    WHERE c.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON c.type = 1
    WHERE m.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON m.type = 1
          AND c.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m /* ON 1 = 1 */
    WHERE c.type = 1
      AND m.type = 1