From Azure-DTU to regular machine There's a way to calculate how many DTU's a given regular server outputs, but i need the other way around!

I'm planning on leaving the azure environment, and I need a way to measure how much CPU/Memory I should use on my RDS.
Right now I know I consume around 100 DTUs...

so, roughly speaking, which machine is capable of such output?

This answer from SO seems to indicate it may intentionally be clouded in mystery... which makes perfect sense if you're Microsoft and very much want people to move into the cloud instead of hosting their own architecture. The cloud is where they get a much bigger slice of the $ pie, after all!

You could always default back to classic recommendations on specs based on nature of workload, size, etc. People were spec'ing VMs long before DTUs :)

Here are a few resources in this vein, though there are many more and none are all-inclusive:

SimpleTalk's What Spec Server Should I Buy?
A DBA.SE post on DW server CPU/RAM calculations
Brent Ozar's What's the Smallest SQL Server You Should Build

Brent Ozar also offers training in this area if you really need to hit the nail on the head with this server transition (I am not affiliated with Brent Ozar Unlimited in any way).

At the end of the day, you'll probably have to do some tuning either way, so I'd start small and work my way up in a test environment. You know your application(s) and workload(s) best so you need to figure out what features and specs are going to get you to your desired state.


ORACLE restore - how to restore specific .bkp file? I just started working with oracle and I have some issue.
My database is NONARCHIVELOG mode.
I made two backups:
they are at locations:

/opt/oracle/flash_recovery_area/CTGINST1/backupset/ol_mf_____20130408.bkp

/opt/oracle/flash_recovery_area/CTGINST1/backupset/ol_mf_____20130407.bkp

I want to restore second file from 7th April.
I read some guide on:
http://orafusion.com/art_rman3.htm

but I can not find command how to specify restore command with specific backup files from these two (or more). It does not say on this link and on other links I read.
I want to use it with RMAN  commands.
what will be the commands to restore my older backup?

I found

RMAN> restore database;

but how it will know what file to restore?
Also does it always need to restore  spfile and control file because I did not create some additional backups (just ran command backup database)

Thank you!!

TL;DR: Just supply the tag of the backup you want to restore the database from, for example restore database from tag 'INTERESTING_TAG';

DISCLAIMER

The solution provided here is based solely on my own experience, you use it on your own risk. I'm not liable for any damages (including data loss) caused by using this solution.

  Also, do I always need to restore spfile and control file because I
  did not create some additional backups (just ran command backup
  database)?

By default RMAN is configured to automatically back up control file and spfile after every successful backup and on every database structural change (for example, adding datafiles) which causes these changes to be reflected in control files. Thus after every successful database backup with backup database, the spfile and control file will be automatically backed up.

You can determine if autobackup is enabled by issuing show controlfile autobackup; in RMAN, and enable it saying configure controlfile autobackup on; in RMAN.

Since you supplied the paths to the backup sets you want to restore your database from, I assume that you obtained them from your current control file using RMAN. It won't hurt to save this information and other information about your existing backups to a plain text file because you will restore one of the previous control files and chances are this information will be lost:

[oracle@oca ~]$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
[oracle@oca ~]$ rman target=/ log 'list_backup.txt'

RMAN> list backup;
RMAN> exit;

You'll need to restore your database AND control file(s), because the database datafile headers of every datafile should be in sync with control file(s), i. e. they should have the same System Change Number (SCN).

As I already said, you just need to supply the tag name to restore the database from a specific backup. You can determine which tags were assigned to the backups by you or by the system, the completion date and time of the backups, and other information about backups saying list backup in RMAN (we already saved this info to the text file). Here's the output of list backup in my sample installation:

RMAN> list backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
78      Full    300.26M    DISK        00:01:40     10-APR-13 05:10:32
        BP Key: 78   Status: AVAILABLE  Compressed: YES  Tag: TAG20130410T050852
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/backupset/2013_04_10/o1_mf_nnndf_TAG20130410T050852_8pbc14yv_.bkp
  List of Datafiles in backup set 78
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/system01.dbf
  2       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/sysaux01.dbf
  3       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/undotbs01.dbf
  4       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/users01.dbf
  5       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
79      Full    9.39M      DISK        00:00:02     10-APR-13 05:10:39
        BP Key: 79   Status: AVAILABLE  Compressed: NO  Tag: TAG20130410T051037
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812350360_8pbk.bkp
  SPFILE Included: Modification time: 10-APR-13 04:55:39
  SPFILE db_unique_name: OCAEXAM
  Control File Included: Ckp SCN: 3985848      Ckp time: 10-APR-13 04:52:40

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
80      Full    300.30M    DISK        00:01:39     10-APR-13 05:18:03
        BP Key: 80   Status: AVAILABLE  Compressed: YES  Tag: DELETE_ME
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/backupset/2013_04_10/o1_mf_nnndf_DELETE_ME_8pbch94j_.bkp
  List of Datafiles in backup set 80
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/system01.dbf
  2       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/sysaux01.dbf
  3       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/undotbs01.dbf
  4       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/users01.dbf
  5       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
81      Full    9.39M      DISK        00:00:01     10-APR-13 05:18:11
        BP Key: 81   Status: AVAILABLE  Compressed: NO  Tag: TAG20130410T051810
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812351553_8pbm.bkp
  SPFILE Included: Modification time: 10-APR-13 05:13:53
  SPFILE db_unique_name: OCAEXAM
  Control File Included: Ckp SCN: 3986589      Ckp time: 10-APR-13 05:12:33

From this output, you can see that the first backup was (automatically) assigned the tag TAG20130410T050852, and that the control file and spfile autobackup follows immediately (check the Completion Time field). You can also see that I performed another database backup, and I manually assigned it the tag DELETE_ME, and, of course, it's immediately followed by autobackup too. Notice also that the files in every backup have the same SCN, and that SCNs match the SCNs of the control files in the adjacent autobackups.

We will restore the database from the backup tagged TAG20130410T050852 which is older than the other backup tagged DELETE_ME, and we will restore the control file from the autobackup first.

In order to restore the control file from backup with RMAN, your instance should be in NOMOUNT state (only spfile is accessed in this state by the instance–control file(s) and datafiles are not accessed):

RMAN> shutdown immediate;

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started
database mounted

(Though, you may use abort clause instead of immediate, and Oracle won't bother shutting down the database orderly–you'll restore it from the previous backup anyway.)

Restore the controlfile from autobackup:

RMAN> restore controlfile from
2> '/u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812350360_8pbk.bkp';

Starting restore at 10-APR-13 06:01:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/ocaexam/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ocaexam/control02.ctl
Finished restore at 10-APR-13 06:01:27

In order for RMAN to be able to read backup records in control file (just like it did when we were issuing list backup), we need to put the database in MOUNT state:

RMAN> sql 'alter database mount';

We're now ready to restore the database:

RMAN> restore database from tag 'TAG20130410T050852';

The final step is to open the database:

RMAN> sql 'alter database open resetlogs';

We specified resetlogs clause here because the existing redo log files are no longer usable since they were in use by the previous database, and thus should be reset so that they can be used by the restored database.

Now query for great good!


pt-table-checksum from remote server I am using pt-table-checksum for the first time and have some questions that I can't seem to resolve by reading the documentation on Percona's website.

Per the documentation, pt-table-checksum performs an online replication consistency check by executing checksum queries on the master. Does this script have to be run on the master? Can I run from another server that has no MySQL installed? I tried this:

pt-table-checksum --host=mysqlistance --user=user --password=password --replicate-check-only

But I get some errors indicated that it is unable to connect to the localhost. However, it specifies the IP instead of localhost.

You need to use a mysql user that has permissions from the host your executing the script from.

Run select user, host from mysql.user on the database to see if your user has an entry for the IP you're connecting from. If not, you will have to grant permissions to the user for that host.


Cap on number of rows matching a condition in Postgres Say I have a db schema like this

user_id text
photo_id text
date_created timestamp

and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would be to do

SELECT COUNT(*) FROM user_photos WHERE user_id='usr_123'

compare the result to 30, if it's lower, then do the insert. Naive because if you can trigger multiple requests in a short amount of time, you can win a race and end up writing more than 30 records.

Is there a way to enforce this constraint in the database? eg create some kind of index that says "only 30 rows can be inserted", or something? Is there a way to have a count column that increments separately for each userId, and you can limit its range to 0-29?

If you used a transaction, would that lock the rows you read?

Your program could manually number the records for each user then you could put a unique constraint on the combination of the user_id and the recordnumber (or make it the primary key) and put a check constraint on recordnumber cheking it is between 1 and 30.


Azure - How can I check if my first full backup is available As per Microsoft documentation Learn about automatic SQL Database backups

How often do backups happen?

  Full database backups happen weekly, differential database backups
  generally happen every few hours, and transaction log backups
  generally happen every 5 - 10 minutes. The first full backup is
  scheduled immediately after a database is created. It usually
  completes within 30 minutes, but it can take longer when the database
  is of a significant size. For example, the initial backup can take
  longer on a restored database or a database copy. After the first full
  backup, all further backups are scheduled automatically and managed
  silently in the background. The exact timing of all database backups
  is determined by the SQL Database service as it balances the overall
  system workload.

Is there a way for me to check if my first full backup is completed and ready for restore?

I understand it will be first available in the same location and later replicated to other locations.  I also know that I can check by trying to add a new database from backup and see if my database backup shows up in the list.  I am more interested if there are system tables, management views that I can query to check?

You can use Get-AzureRmSqlDatabaseGeoBackup Powershell cmdlet to get all available backups on a specified server.

Get-AzureRMSqlDatabaseGeoBackup -ResourceGroupName "ContosoResourceGroup" -ServerName "ContosoServer"

Hope this helps.


List top N rentals for each month I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server.  The database is the Sakila Sample database and the table is rental. Here are the first several rows in my database client (Navicat Premium):

I'd like the take the month from the rental_date field.
Each film has a unique inventory_id.

So far I've got this:

SELECT
  MONTH( rental_date ) AS month,
    inventory_id,
    COUNT ( inventory_id ) AS no_of_rentals
FROM
    rental
GROUP BY
    MONTH ( rental_date ),
    inventory_id
HAVING
    COUNT ( inventory_id ) > 1
ORDER BY
    MONTH ( rental_date ),
    inventory_id;

Unfortunately, that returns all films with more than one rental per month, which is not what I want.

I'd like to see something more like:

month   inventory_id  no_of_rentals
_________________________________
April   367           5
April   98            4
(3 more rows)
May     144           5
May     32            5
(3 more rows)
etc...

Bonus points if you can get the film title!

Thanks!

Rob

UPDATE:

Thanks to Dominique Boucher for the answer.

Here's my finished query:

WITH cte AS (
    SELECT
      MONTH(rental_date) rental_month_no,
      DATENAME(month, rental_date) rental_month,
      inventory_id,
      COUNT( inventory_id ) no_of_rental,
      ROW_NUMBER( ) OVER ( PARTITION BY MONTH(rental_date) ORDER BY COUNT( inventory_id ) DESC) Rank
    FROM
      rental
    GROUP BY
      MONTH(rental_date),
      DATENAME(month, rental_date),
      inventory_id
    HAVING COUNT( inventory_id ) > 1
)
SELECT
    rental_month,
    cte.inventory_id,
    title,
    no_of_rental,
    rank
FROM
  cte
  JOIN inventory ON cte.inventory_id = inventory.inventory_id
  JOIN film ON inventory.film_id = film.film_id
WHERE
  Rank < 6
ORDER BY rental_month_no, no_of_rental DESC;

I added code to fetch the titles and limited results to films that were rented more than once in a month.

Here are the results in Navicat:

Use pretty much the same script in a CTE but add the row number (partition by month).
This will return all rows with a row number in front, reseting at 1 for every month. Then you can just issue your query with a "where rn <=5" and that will make it.

Here's an example just to give you a starting point:

create table #Demo (Rental_id int identity, rental_month int, inventoryId int, customer_id int)

insert into #Demo values (2,556,495),(2,236,495),(2,116,495),(2,556,495),(2,556,495),(2,116,495),(2,323,495),(2,56,495),(2,49,495),(2,49,495),
(2,116,495),(2,556,495),(2,453,495),(2,236,495),(2,34,495),(2,116,495),(2,556,495),(2,556,495),(2,24,495),(2,24,495)

insert into #Demo values (3,117,495),(3,236,495),(3,117,495),(3,236,495),(3,117,495),(3,116,495),(3,117,495),(3,236,495),(3,117,495),(3,236,495),
(3,116,495),(3,556,495),(3,453,495),(3,236,495),(3,34,495),(3,117,495),(3,556,495),(3,556,495),(3,117,495),(3,24,495),(3,34,495)

go

with cte as (
select rental_month, inventoryId, COUNT(inventoryId) "no_of_rental", ROW_NUMBER() over (PARTITION by rental_month order by count(inventoryId)) "RN"
from #Demo
group by rental_month, inventoryId
)
select * from cte where RN <6;

drop table #Demo


Securing Always Encrypted SQL Server from "sa" I'm currently studying SQL Server Always Encrypted feature. How can I disable column decryption to "sa" if all they have to do is add Column Encryption Setting=enabled in the login's additional login parameters in SQL Server Management Studio?

I read articles in Google. They only said we can secure data from admins but no example how.

The column encryption key (CEK) is used to encrypt the data and is stored in the database. The CEK is secured using  a column master key (CMK).

The CMK is stored outside of SQL Server. Metadata about the key is stored within SQL Server.

If you restrict access to the CMK your database administrators / system administrators (sa) will not have the ability to decrypt.


Getting the percentage of a result set for same ID rows I have the below table obtained as an output using the query provided below that.

;WITH cte AS (
       SELECT c.CaseID AS 'Case #',
       m.ManufacturerName,
       ou.OutcomeName
FROM Consumes con
INNER JOIN [Case] c
ON con.FKCaseID = c.CaseID
INNER JOIN Manufacturer m
ON m.ManufacturerID = con.FKManufacturerID
INNER JOIN Case_Outcome oc
ON oc.FKCaseID = c.CaseID
INNER JOIN OutCome ou
ON oc.FKOutcomeID = ou.OutcomeID
)

SELECT c.[Case #],
c.ManufacturerName,
       STUFF((SELECT ','+OutcomeName
       FROM cte
       WHERE c.[Case #] = [Case #]
       FOR XML PATH('')),1,1,'') as OutcomeName
FROM cte c
GROUP BY c.[Case #],c.ManufacturerNAme

For all the manufacturers, I need to list down the # events occurred due to their drug and the percentage of each outcome occurred.

How can I get this? I used the below query but it doesn't give the proper output percentage.

SELECT
  m.ManufacturerName,
  COUNT(c.CaseID) AS '# Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID)*100.0 AS 'Death Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Hospitalization' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Hospitalization Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Life Threatening' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Life Threatening Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Disability' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Disability Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Congenital Anomaly' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Congenital Anomaly Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Required Intervention' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Required Intervention Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Other Serious' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Other Serious Events'
FROM Consumes con
   INNER JOIN [Case] c ON con.FKCaseID = c.CaseID
   INNER JOIN Manufacturer m ON m.ManufacturerID = con.FKManufacturerID
   INNER JOIN Case_Outcome oc ON oc.FKCaseID = c.CaseID
   INNER JOIN OutCome ou ON oc.FKOutcomeID = ou.OutcomeID
GROUP BY m.ManufacturerName

The above query gives either 0.0 or 100.0 when all the outcomes are of the same type. When there are different outcomes for the same case, this query doesn't work as expected. What am I doing wrong here? Any help would be much appreciated.

The datatype of a COUNT is a [BIG]INT and integer divison results in truncated values, either zero or one. Multiplying this times 100.0 returns  either 0.0 or 100.0, so you just need to change the order of calculation to multiply first:

100.0 * COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID) AS 'Death Events',


How can I implement data encryption in SQL Express for a VB6 application? I am a developer tasked with maintaining a legacy application that is written in VB6. With the upcoming introduction of GDPR we are working on an encryption solution for our users. Our users use a variety of different versions of SQL Server and a variety of OS's. They host their own databases on their own servers.

I have come up with an idea of using column level encryption on the tables using certificates installed on the database server, then using a view to retrieve unencrypted data. Then using an INSTEAD OF trigger to update the encrypted data.

This means no changes to the application for data access. Unfortunately, because we connect via an ADODB.Recordset in many places the system will not allow the data to be updated (it throws an OLE DB generated error).

My next idea was to implement object model classes within the VB6 code and save via a stored procedure. This will involve allot of laborious  code changes so we would ideally like to avoid this.

Does anybody have any experience solving this issue or any advice regarding implementing the correct form of encryption to enable our clients to be compliant?

Thanks in advance

I hope it is permitted to mention our third party product, NetLib Encryptionizer. It provides Transparent Data Encryption for all versions and editions of SQL Server including Express.


MongoDB Collection Level Permission Issues I am trying to set up a MongoDB user that only has access to certain collections across several databases.  The issue is that these databases are created dynamically at runtime.
I decided to employ collection level access in order to do this with the following permission on the user role:
"db" : "admin",
"privileges" : [
    {
        "resource " : {
            "cluster" : true
        },
        "actions" : [
            "listDatabases"
        ]
    },
    {
        "resource" : {
            "db" : "",
            "collection" : "packages"
        },
        "actions" : [
            "find",
            "insert",
            "remove",
            "update",
            "listCollections"
        ]
    }]

What I want is for any user with this role to be able to read/write/remove in any database but only on the packages collection inside of that database, and be unable to read or write to any other collection.  When I launch mongod in --auth I do not see any databases besides admin.
I am certain this is just me being unable to figure out exactly what actions / resources / roles I am supposed to grant.

Not clear what you mean but this could be a solution:
var role = "<role name>";
admin = db.getSiblingDB("admin");
if (admin.getRoles({ rolesInfo: role }).length == 0)
   admin.createRole({ role: role, privileges: [], roles: [] });
admin.grantRolesToUser(<the user>, [role]);

Then you would have to run this code every time when a new database is created.
db.getSiblingDB(<your dynamic database name>).getCollectionNames().forEach(function (item) {
   db.getSiblingDB("admin").grantPrivilegesToRole(role, [{
      resource: {
         db: <your dynamic database name>, collection: item },
         actions: ["find", "insert", "remove", "update"] }])
   })


Hourly Set Report in MySQL I have a daily reporting system in place which shows the number of calls made by a particular telecaller in a day. And it works well.

I'd like to bifurcate it further into hours for e.g 9-10, 10-11 and so on.

The current mysql code is this. Please excuse imperfect code, I'm a self learner and have learnt what I have from Stackexchange.

    SELECT
comments.hrname,
IF((HOUR(comments.date))= '9', COUNT(comments.Outcome),'') AS `9-10`,
IF((HOUR(comments.date)) = '10', COUNT(comments.Outcome),'') AS `10-11`,
IF((HOUR(comments.date)) = '11', COUNT(comments.Outcome),'') AS `11-12`,
IF((HOUR(comments.date)) = '12', COUNT(comments.Outcome),'') AS `12-13`,
IF((HOUR(comments.date)) = '13', COUNT(comments.Outcome),'') AS `13-14`,
IF((HOUR(comments.date)) = '14', COUNT(comments.Outcome),'') AS `14-15`,
IF((HOUR(comments.date)) = '15', COUNT(comments.Outcome),'') AS `15-16`,
IF((HOUR(comments.date)) = '16', COUNT(comments.Outcome),'') AS `16-17`,
IF((HOUR(comments.date)) = '17', COUNT(comments.Outcome),'') AS `17-18`,
IF((HOUR(comments.date)) = '18', COUNT(comments.Outcome),'') AS `18-19`,
IF((HOUR(comments.date)) = '19', COUNT(comments.Outcome),'') AS `19-20`,
IF((HOUR(comments.date)) = '20', COUNT(comments.Outcome),'') AS `20-21`,
IF((HOUR(comments.date)) = '21', COUNT(comments.Outcome),'') AS `21-22`
FROM
comments
WHERE
DATE(comments.date) = DATE(NOW()) AND
MONTH(comments.date) = MONTH(NOW()) AND
YEAR(comments.date) = YEAR(NOW())
GROUP BY
comments.hrname

And the outcome I get is this:

Where am I going wrong?

P.S This is an internal software for approx 10 users, primarily designed to eradicate shared excel use, so SQL Injection etc is not really a concern AFAIK.

Any help would be much much appreciated.

What your query does, is putting the COUNT() in the column you want, but it actually counts every row. There's only one condition, where COUNT() distinguishes between "to count" or "not to count". And that is, that when you COUNT(*) every row is counted, no matter what. When you COUNT(column_name), it counts only the rows that are not NULL. But that just aside.

What you actually want to do is this:

SUM(IF((HOUR(comments.date)) = '9', 1, 0)) AS `9-10`,

Another note:
When you do

WHERE
DATE(comments.date) = DATE(NOW()) AND
MONTH(comments.date) = MONTH(NOW()) AND
YEAR(comments.date) = YEAR(NOW())

the comparison for month and year is unnecessary, since this is already covered in DATE().

An even better way to do this is actually doing this:

WHERE comments.date BETWEEN CURDATE() AND CONCAT(CURDATE(), ' 23:59:59')

or

WHERE comments.date >= CURDATE()
AND comments.date < CURDATE() + INTERVAL 1 DAY

This is because MySQL can't use an index on a column (if there is any), when you use a function on this column.


Does 3NF require 2NF? I am a bit confused with the definition of 3NF.

According to Wikipedia:

  The third normal form (3NF) is a normal form used in database
  normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's
  definition states that a table is in 3NF if and only if both of the
  following conditions hold:

  The relation R (table) is in second normal form (2NF)
  Every non-prime attribute of R is non-transitively dependent on every superkey of R.

But, according to book  the Database System Concepts 6th Edition by Abraham Silberschatz, Henry F.Korth, S.Sudarshan:

  A relation schema R is in third normal form (3NF) with respect to a
  set F of functional dependencies if, for all functional dependencies
  in F+ of the form α → β, where α ⊆ R and β ⊆ R, at least one of the
  following holds:

  α → β is a trivial functional dependency.
  α is a superkey for R.
  Each attribute A in β − α is contained in a candidate key for R.

Also, according to Database Systems 6th Edition by Ramez Elmasri  Shamkant B. Navathe:

  A relation schema R is in third normal form (3NF) if, whenever a
  nontrivial functional dependency X→A holds in R, either (a) X is a
  superkey of R, or (b) A is a prime attribute of R.

After reading the above definitions, I am not sure if a relation schema has to be in 2NF in order to be in 3NF. Also, does a relation schema have to be in 3NF in order to be in BCNF?

The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms.  So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form.

When normalizing a model, you normally work at resolving one normal form before the next.  This makes normalization simpler and less error prone as you are only applying a few rules at at time.  It is possible that an entity at a lower normal form is also at a higher normal form.  Most 3rd normal form entities are also in 4th and 5th normal form.

4th, 5th, and 6th normal forms go beyond the functional dependence.  Where applicable, they further simplify the data, at the expense of more tables.  They reduce redundancy, and increase data integrity.


SQL Server: Creating a user database with permissions At work, I am tasked with creating a user database.  It needs to account for (end)user permissions for 8 different installed programs.  The database will be hosted by each client containing their specific information behind a firewall.  I have the majority of it built out (tables below), but I don't know how to incorporate permissions.

I could put all available permissions in a table, and then link them to users/groups and applications in another table.  Or I could put all available permissions in a table, then link them to users/groups in another table, and then applications in yet another table.  Problem is that both of these options don't play well with the applications because I cannot think of a way to actually search for a specific permission (ex: call to database to find if user can do 'x') without the programmer hard-coding the permission Ids into the program.

I could have a table per application with each of that application's permissions as a column, but there are 70+ permissions for each application and the tables would get huge (also not very expandable if we need to add more permissions later).

So the end questions is; How do I set up permissions for end-user accounts in a way that the programmer does not have to know and hard-code the Ids into the applications?

Table - Description

Applications - Basically a list of our applications so we have an Id to link the permissions to
GlobalSettings - Settings used for all users, mostly password requirments
Groups - Lists all end-user administrator defined groups of users (this way a permission can be tied to a group of users at one time)
PreviousPasswords - Lists the last x hashed passwords for each user (the ones they can't use again)
UnusablePasswords - Lists passwords we won't let our end-users use (such as password1)
User_Group_Link - Links a User to a Group
Users - Lists all end-users and their needed data (birthday, name, etc)

Some Ideas (Anyone: please feel free to Edit/Add)
ApplicationPermissions (Application Level Permissions)

ApplicationID
PermissionID
Allow (Y/N/1/0)
Value (As in a Max Number of "X" allowed)
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

User_Application_Permissions (User Level Permissions)

UserID
ApplicationID
Allow (Y/N/1/0)
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

UserPermissions (Descriptive/Attribute Table)

PermissionID
PermissionDescription
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID

Users (Descriptive/Attribute Table)

UserID
UserName
CreatedDate
CreatedByUserID
LastUpdatedDate
LastUpdatedByUserID


Delete duplicates and reroute referencing rows to new master Given three tables:

A users table with id's:

╔════╗
║ id ║
╠════╣
║ 1  ║
╟────╢
║ 2  ║
╟────╢
║ 3  ║
╚════╝

A purposes table with user_id's and name's:

╔════╤═════════╤══════════╗
║ id │ user_id │ name     ║
╠════╪═════════╪══════════╣
║ 1  │ 3       │ Business ║
╟────┼─────────┼──────────╢
║ 2  │ 2       │ Personal ║
╟────┼─────────┼──────────╢
║ 3  │ 2       │ Personal ║
╚════╧═════════╧══════════╝

And a journeys table with user_id's and purpose_id's.

╔════╤═════════╤════════════╗
║ id │ user_id │ purpose_id ║
╠════╪═════════╪════════════╣
║ 1  │ 2       │ 3          ║
╟────┼─────────┼────────────╢
║ 2  │ 1       │ 2          ║
╟────┼─────────┼────────────╢
║ 3  │ 1       │ 1          ║
╚════╧═════════╧════════════╝

We want to construct a query, in PostgreSQL, where we find all of the records in the purposes table that have the same name and user_id, deleting those with the least number of associated journeys while preserving the one record with the maxmium number of journeys while also updating the purpose_id of those journeys whose associated purpose has been deleted.

Right now I have a query to find those duplicate purposes using a window function, however the result only returns one of the duplicates and it's not certain they it has the maximum number of journeys (as I haven't specified that in any of the clauses).

select id from (
  select
    id,
    row_number()
  over(partition by user_id, name) as dupe_count
  from
    purposes
) purposes
where purposes.dupe_count > 1;

Can be a single statement with data-modifying CTEs:

WITH blacklist AS (
   SELECT *
   FROM  (
      SELECT id
           , first_value(id) OVER (PARTITION BY user_id, name
                                   ORDER BY j_ct DESC NULLS LAST, id) AS master_id
                                                               -- id as tiebreaker
      FROM   purposes
      LEFT   JOIN (  -- left join to preserve rows without any journeys
         SELECT purpose_id AS id, count(*) AS j_ct
         FROM   journeys
         GROUP  BY 1
         ) j USING (id)
      ) sub
   WHERE  master_id <> id  -- is not master_id
   )
 , del AS (
   DELETE FROM purposes p
   USING  blacklist b
   WHERE  b.id = p.id
   )
UPDATE journeys j
SET    purpose_id = b.master_id
FROM   blacklist b
WHERE  b.id = j.purpose_id;

This should do exactly what you need - even in cases of ties or where no journeys exist at all.

Step by step

The 1st CTE blacklist identifies all IDs in table purposes where another row exists with the same (user_id, name) but more associated journeys. Remember that other ID as master_id to reassign journeys further down the road. id serves as tiebreaker: the smallest id prevails.

The 2nd CTE del deletes the identified dupes.
The outer UPDATE reroutes journeys to their new master.

Note DESC NULLS LAST in the ORDER BY clause. Important for cases with no journeys at all, since that result in a NULL value for the journey count j_ct. See:

PostgreSQL sort by datetime asc, null first?

Use the window function first_value() to get the master_id for each group of peers - after forming groups with PARTITION BY and sorting properly with ORDER BY.


MariaDB defragmenting with new option innodb-defragment=1 I try to defragment table using innodb-defragment=1
https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/

But it was a bad experience :
- locks on table
- no a good defragmentation result (always data free on table)

So I use percona-tools
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

And get a good result no lock et no more data free on table. (But need space on disk to duplicate table)

But what's wrong with the first method ?

UPDATE :

Myth-busters.

Defragmentation is a concept that mostly does not apply to InnoDB.  It is rarely worth the effort to use OPTIMIZE TABLE or other techniques to defrag.

There are several ways in which InnoDB fragments and/or has "free" space:

The rows in a block not filling up the block.
BTree blocks gravitate to 69% full.  If you will insert new rows at random spots, this is sort of the limit of the intra-block waste.
The blocks in an 'extent' not consuming the entire extent.
Undo/redo records temporarily consuming space.
Data and Indexes are each BTrees, with various 'free' space.

SHOW TABLE STATUS shows only part of the "free" space.  Furthermore, it will usually indicate 4/5/6/7MB as free for non-small tables because of reserving space in an 'extent' for future action.

Why defrag?

Defragmentation provides only a tiny benefit to performance; so this is rarely a valid reason.
If you are almost out of disk space, defragging may keep you out of trouble briefly.  But if the defrag action requires copying the table/tablespace, then you may not have room to do it.  Catch-22.  Well, OK, MariaDB's feature avoids the copy.  But will it shrink the size of the tablespace file, thereby giving space back to the OS?
InnoDB already does some defragmentation -- by combining adjacent somewhat-empty blocks automatically (under certain circumstances).

(Looking at TABLE STATUS provided)

It shows 0.9GB "data" but a huge 6.9GB "free".  (I assume that STATUS was taken after an OPTIMIZE?)  This can exist in multiple situations:

innodb_file_per_table = OFF, in which case, the "free" is how much free space is in ibdata1, and has nothing to do with the table in question.  (You say this was not your case.)
The table has a thousand PARTITIONs.  Each partition always shows some "free" space; it could add up to 6.9GB.  (One reason why PARTITIONing may not be wise to pursue.)
You recently DELETEd most of the table's rows.  This was probably a very slow process.  It would have been faster to create a new table, copy over the rows to keep, then rename to put the table in place.  And, guess what, this approach would have given you 'optimize' for free.

The defrag feature in MariaDB, according to the manual:

  Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.

So...

It does not take extra disk space while defragmenting.  This can be very important if you are too low on disk space.
It fails to give the space back to the OS, even for file_per_table.
It apparently does not prevent accesses to the table during the defragmenting.  (Or at worst does it in quick bursts.)


Affected rows after update with OpenQuery Is there a way to get the affected row count, when updating through an connection server?

I'm using MSSQL connected to MySQL with ODBC.

UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM table WHERE status=0') SET status=1

Use a modified Linked Server Naming Convention Instead with @@rowcount

UPDATE  [linkedServerName]...[databaseName.TableName]
set status=1 WHERE status=0
select @@rowcount

Note:

Follow these steps to get Microsoft SQL Server linked server naming convention to work with MySQL:

https://stackoverflow.com/questions/31968343/select-from-mysql-linked-server-using-sql-server-without-openquery

  you can use the statement below

  select * from [linkedServerName]...[databaseName.TableName]

  but before executing the code above ,, you have to do some changes ..

  in the SQL Server Management Studio , go to "linked servers" Folder , open Providers Folder , find MSDASQL and gets it's propertythen check "Level Zero Only" press Ok ... then execute above query and Enjoy it !!!


How can I fix these broken synonyms? When I run the following query:

SELECT S.OWNER   AS SYN_OWNER,
  S.SYNONYM_NAME AS SYN_NAME,
  S.TABLE_OWNER  AS OBJ_OWNER,
  S.TABLE_NAME   AS OBJ_NAME,
  CASE
    WHEN O.OWNER IS NULL
    THEN 'MISSING'
    ELSE O.STATUS
  END AS OBJ_STATUS
FROM DBA_SYNONYMS S
LEFT JOIN DBA_OBJECTS O
ON S.TABLE_OWNER = O.OWNER
AND S.TABLE_NAME = O.OBJECT_NAME
WHERE O.OWNER   IS NULL
OR O.STATUS     != 'VALID';

It gives me the following output:

PUBLIC  GV$LOADPSTAT    SYSTEM  GV_$LOADPSTAT   INVALID
PUBLIC  GV$LOADISTAT    SYSTEM  GV_$LOADISTAT   INVALID
PUBLIC  V$LOADPSTAT SYSTEM  V_$LOADPSTAT    INVALID
PUBLIC  V$LOADISTAT SYSTEM  V_$LOADISTAT    INVALID
PUBLIC  LOADER_CONSTRAINT_INFO  SYSTEM  LOADER_CONSTRAINT_INFO  MISSING
PUBLIC  LOADER_COL_INFO SYSTEM  LOADER_COL_INFO MISSING
PUBLIC  GV$DB_TRANSPORTABLE_PLATFORM    SYS GV$_DB_TRANSPORTABLE_PLATFORM   MISSING
PUBLIC  LOADER_COL_TYPE SYSTEM  LOADER_COL_TYPE MISSING
PUBLIC  LOADER_OID_INFO SYSTEM  LOADER_OID_INFO MISSING
PUBLIC  LOADER_PARAM_INFO   SYSTEM  LOADER_PARAM_INFO   MISSING
PUBLIC  LOADER_FULL_ATTR_NAME   SYSTEM  LOADER_FULL_ATTR_NAME   MISSING
PUBLIC  LOADER_INTCOL_INFO  SYSTEM  LOADER_INTCOL_INFO  MISSING
PUBLIC  LOADER_NESTED_VARRAYS   SYSTEM  LOADER_NESTED_VARRAYS   MISSING
PUBLIC  LOADER_LOB_FLAGS    SYSTEM  LOADER_LOB_FLAGS    MISSING
PUBLIC  LOADER_DIR_OBJS SYSTEM  LOADER_DIR_OBJS MISSING
PUBLIC  GV$SYSTEM_FIX_CONTROL   SYS GV$SYSTEM_FIX_CONTROL   MISSING
PUBLIC  LOADER_PART_INFO    SYSTEM  LOADER_PART_INFO    MISSING
PUBLIC  LOADER_COL_FLAGS    SYSTEM  LOADER_COL_FLAGS    MISSING
SYS DEF$_SCHEDULE   SYSTEM  DEF$_SCHEDULE   MISSING
PUBLIC  GV$TRANSPORTABLE_PLATFORM   SYS GV$_TRANSPORTABLE_PLATFORM  MISSING
PUBLIC  LOADER_TRIGGER_INFO SYSTEM  LOADER_TRIGGER_INFO MISSING
PUBLIC  LOADER_TAB_INFO SYSTEM  LOADER_TAB_INFO MISSING

If I try to recompile or drop these:

ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

It says:

SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

How could this be so? I'm logged in as SYSTEM.

If I try to drop it:

DROP PUBLIC SYNONYM GV_$LOADPSTAT;

It says:

SQL Error: ORA-01432: public synonym to be dropped does not exist
01432. 00000 -  "public synonym to be dropped does not exist"

How can it not exist when the query result shows it?

First of all, DBA_SYNONYMS.SYNONYM_NAME is the name of the synonym.
DBA_SYNONYMS.TABLE_NAME is the name of the object the synonym points to.
In your query, it is not the synonym that is INVALID, but the object that it points to.
It is not the synonym that needs to be fixed, but the object.

  If I try to recompile or drop these:

  ALTER PUBLIC SYNONYM GV_$LOADPSTAT COMPILE;

  It says:

  SQL Error: ORA-01031: insufficient privileges

  01031. 00000 -  "insufficient privileges"

  How could this be so? I'm logged in as SYSTEM.

It is a bug on your version.

  If I try to drop it:

  DROP PUBLIC SYNONYM GV_$LOADPSTAT; It says:

  SQL Error: ORA-01432: public synonym to be dropped does not exist

  01432. 00000 -  "public synonym to be dropped does not exist"

  How can it not exist when the query result shows it?

Then name of the synonym is GV$LOADPSTAT. You are trying to drop the object (view) here, called: GV_$LOADPSTAT. And that view is owned by SYS, not SYSTEM.

Where you see MISSING, the synonyms points to a non-existent object. Replace the object or drop the synonym.


sp_Blitz says "Page Verification Not Optimal" on MDS database I've run sp_blitz (version 45) on our test database server.
It complains about

  Database [MDS] has TORN_PAGE_DETECTION for page verification.  SQL Server may have a harder time recognizing and recovering from storage corruption.  Consider using CHECKSUM instead.

But this is a database from Microsoft.
Is it wise to change the Page Verification to CheckSum?

Or should we wait for Microsoft to release a new version of MDS?

I would suggest you follow what the sp_blitz recommended. You can change the option to CHEKSUM. Checksum might be able to detect more problem that torn_page.

Torn page allows you to detect whether page was successfully written to disk or not. It would not check what is inconsistency inside the page. While checksum performs more thorough checks. Having said all this you must know there is no replacement of good backup and you must (if possible) run dbcc checkdb every day.

What Paul said, quoting from This SQLServercentral link

  The difference is Unmeasurably small - all it's doing is grabbing the
  first two bits from each of the 16 sectors, storing them in the page
  header, and then writing an alternating bit pattern into the two bits
  in each sector. And the reverse when the page is read again. Almost
  nothing compared to what a page checksum is doing by reading the whole
  page and adding the contents to a 'checksum'.

  However, torn-page detection does not detect corruption within sectors
  - only when a write fails some way through. On 2005 onwards you should always have page checksums enabled for this extra benefit - 1-2% CPU
  shouldn't be an issue (otherwise you've got other problems).

  Some blog posts around this: How to tell if the IO subsystem is
  causing corruptions? and Inside The Storage Engine: Does turning on
  page checksums discard any torn-page protection?.

Following is what BOL document has to say

  Consider the following important points when you use the PAGE_VERIFY
  option: • The default is CHECKSUM.

  • When a user or system database is upgraded to SQL Server 2005 or a
  later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is
  retained. We recommend that you use CHECKSUM

  In earlier versions of SQL Server, the PAGE_VERIFY database option is
  set to NONE for the tempdb database and cannot be modified. In SQL
  Server 2008 and later versions, the default value for the tempdb
  database is CHECKSUM for new installations of SQL Server. When
  upgrading an installation SQL Server, the default value remains NONE.
  The option can be modified. We recommend that you use CHECKSUM for
  the tempdb database.

NOTE: If you enable page checksum it would not be enabled for entire pages of the databases. There is no process as such which puts this option for all pages. When a certain page would be read in memory, changed, and written back to disk checksum would then be enabled for that page. So you would have to perform operation like index rebuild to bring as much page as possible in memory. Paul Randal has Myth section related to Checksum please read it.
Databases that are created on SQL Server 2005 and 2008 will have page checksums enabled automatically unless user changes the setting in the model database.


Fast way to split primary key into ranges I want to split values of an auto-incrementing primary key into ranges of approximately equal size as fast as possible. The number of ranges is low, less than 20. So far I've came up with this:

Determine approximate row count by querying sys.partitions and calculate bucket size.

Run a query select min(id), max(id) from (select top 1000000 id from my_table order by id desc) t

Run a series of queries

select min(id), max(id)
from (
    select top 1000000 id
    from my_table where id > $prev_max
    order by id desc) t;

until all the ranges are found.

Query times are acceptable (several seconds for the first one and less than second for subsequent queries) but is there an easier (preferably a single query) way? Generated ranges don't need to be exact, it's ok if they intersect or have gaps, I just need a good enough estimate.


Create Random Number up to 50 Digits and Store in Varchar Does anyone have code; looking for a random number function generator.
I would just supply with a variable length @NumberLength = 50, etc.
It can create numbers up to 50 digits, and store in varchar. (Bigint does not store this high)
I am using Floor Rand, Floor, NewId(), still not receiving answer, that exceeds 12 digits.

If anyone has a solution, that would be great. I need to randomize a column in table.

Thank you,

Note: Needs to be in a function, functions cannot accept NewID or rand() for some reason, so I sometimes utilize views.
If random number is used again, no issue, we can have little bit repetition.

Here's one approach (demo).

Create a view as CRYPT_GEN_RANDOM won't be allowable in a function

CREATE VIEW dbo.LongRandom
AS
  SELECT CONVERT(VARCHAR(500), CRYPT_GEN_RANDOM(500), 2) AS random_hex_string

And then call it in a function and Replace all A-F with empty strings. The below uses TRANSLATE to map all B-F to A and a final REPLACE to remove all the As.

You could also simply use 6 nested REPLACE (if you are on a version < 2017 you will have to anyway)

CREATE FUNCTION dbo.reallyBigRandInt(@Length TINYINT)
RETURNS VARCHAR(50)
AS
  BEGIN
      RETURN
        (SELECT LEFT(REPLACE(TRANSLATE(random_hex_string,'BCDEF','AAAAA'),'A',''), @Length)
         FROM   dbo.LongRandom)
  END

The string returned from the view is 10 times longer than the maximum final string required to make it extremely unlikely that removing these characters will leave insufficient characters.

Then select from that

SELECT dbo.reallyBigRandInt(50);

NB: You could also forgo the string parsing and just use CRYPT_GEN_RANDOM to generate three bigint that you glue together (without minus signs) but this is less random unless you are careful.

The maximum bigint is 9223372036854775807 so clearly there is a lower probability that the leftmost digit will be 9. Similarly there is a greater probability that the second character will be 1 or 0 than any other digit. As it is possible for the second character to be 3 only if the leading character is 0-8. Similar issues exist for the other positions but decline in importance as you move rightwards.

If this isn't a problem for you you can concatenate the right most 17 digits of three bigints together to get a 51 character numeric string and then cut that down to the desired length.

CREATE VIEW dbo.RandomBigInt
AS
  SELECT CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)) AS number

And function

CREATE FUNCTION dbo.reallyBigRandInt2(@Length TINYINT)
RETURNS VARCHAR(50)
AS
  BEGIN
      RETURN
        (SELECT RIGHT(RIGHT(FORMAT(number, 'D19'),17) +
                      RIGHT(FORMAT(number, 'D19'),17) +
                      RIGHT(FORMAT(number, 'D19'),17), @Length)
FROM  dbo.RandomBigInt)
  END


Query Performance Tuning When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters?  Do you run the query and view the actual execution plan?  If so, what are the things you look for?  Obviously table/index scans are the bit hits, but what else?

For a quick assessment, get the execution plan out of SSMS and in to Plan Explorer.

Review the most expensive operations for anything unexpected. Sorts, worktables, inappropriate join operators (e.g. nested loop where you expect a merge or hash).
Look at the rowcounts at each stage of the plan, are they broadly within the range you expected to see?
Look at the estimated vs actual rows. If you're actuals are close to the estimates, it's more likely you have a good plan. If there are big variations, find out why (missing and/or out of date statistics for instance).
Evaluate the potential for parameter sniffing issues. Look for areas where cardinality may vary and test against a range of input parameters.

Lots of freely available reference material out there, Grant Fitchley's SQL Server Execution Plans is a good start. I also found Joe Chang's blog posts and ebook on execution plan costs very useful.


Sync two Oracle production server databases I have an Oracle database that runs a 6 hours batch job every day. This process slows down performance during the 6 hours timeframe.

Is there any method that I could use to build another server that runs the batch job, and once is done, sync the data to the production server? (The time taken must be shorter than 6 hours.)

You could use database link to transfer data between servers.

Assuming you already have the link set up, replicating data can be as simple as:

insert into target_table
select * from source_table@link_name;
commit;

Of course this will be more involved if the data volume is large or you have complex schema.


Merge Failed when update same rows The MERGE statement attempted to UPDATE or DELETE the same row more
  than once. This happens when a target row matches more than one source
  row. A MERGE statement cannot UPDATE/DELETE the same row of the target
  table multiple times. Refine the ON clause to ensure a target row
  matches at most one source row, or use the GROUP BY clause to group
  the source rows.

I know this error can be fixed with update and not exists statements.

But i need this to be done with merge since merge will be faster than two statements.

Source:

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

Target:

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       0 |        0 |        0 |
|   3144000 | medium    | 3144000_medium.jpg| Google  |       1 |        0 |        0 |
|   3144000 | large     | 3144000_large.jpg | Google  |       1 |        0 |        0 |
|   3144005 | small     | 3144005_small.jpg | Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        0 |        0 |
|   3144005 | large     | 3144005_large.jpg | Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

Merge:

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       1 |        0 |        0 |
|   3144000 | medium    | 3144000_medium.jpg| Google  |       1 |        0 |        0 |
|   3144000 | large     | 3144000_large.jpg | Google  |       1 |        0 |        0 |
|   3144005 | small     | 3144005_small.jpg | Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       1 |        1 |        1 |
|   3144005 | large     | 3144005_large.jpg | Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

Query:

MERGE INTO [luannw2016].[ImageThumbnailTrack] AS TARGET
USING #ImageThumbnailTrack AS SOURCE ON TARGET.ProductId = SOURCE.ProductId
AND TARGET.ImageType = SOURCE.ImageType
WHEN MATCHED
THEN
    UPDATE
    SET ImageName  = SOURCE.ImageName,
        Website    = SOURCE.Website,
        Thumb90    = SOURCE.Thumb90,
        Thumb200   = SOURCE.Thumb200,
        Thumb500   = SOURCE.Thumb500
WHEN NOT MATCHED
THEN
    INSERT ([ProductId],[ImageType],ImageName,Website,Thumb90,Thumb200,Thumb500)
    VALUES ([ProductId],[ImageType],ImageName,Website,Thumb90,Thumb200,Thumb500);

It is no secret that I am not a fan of MERGE, for good reasons, and typically when I see customer code with MERGE, I re-write it using the "old" way of separate statements (and recommend others do the same, especially when they are not MERGE experts).

  since merge will be faster than two statements.

NO IT WON'T. MERGE still runs the exact same semantics as separate statements would. There is no performance advantage at all. Please read up on it and be sure you understand how it works - if you are so confident MERGE is faster, then when you get your statement working, run some tests and try to prove it. I'm afraid you'll be disappointed with the results, but at least now your conclusion will be based on facts.

I think the error message is pretty clear; since ProductId,ImageType is not unique, you might need to add the ThumbXX columns to the comparison.

AND TARGET.Thumb90    = SOURCE.Thumb90
AND TARGET.Thumb200   = SOURCE.Thumb200
AND TARGET.Thumb500   = SOURCE.Thumb500

Also you should properly alias the VALUES() list:

WHEN NOT MATCHED
THEN
    INSERT ([ProductId],[ImageType],...)
    VALUES (SOURCE.[ProductId],SOURCE.[ImageType],...);

I would still write this as UPDATE ... WHERE EXISTS and then INSERT ... WHERE NOT EXISTS (and wrapped in an appropriate transaction of course, which your current MERGE statement needs but lacks).


Problem installing SQL Failover cluster Let me first tell you my current environment in which there is one windows cluster on which there is sql cluster with two nodes, i.e, A and B in it. Of which, one A is active and B is passive.
So, what I want is to use my passive node, i.e, B for some other application's database and make it work like active node for this application and A which will act as passive in this case.
Using this I can utilize my one server which is free at a time.

The point is that can we do that?

Yes you can do that with Failover Clustering. But you will need to license this second instance.

If one node fails and can't be brought up quickly, you need to consider whether or not the hardware can support both instances for a period of time. In a 2-instance scenario, it is generally advised to have 3 nodes: active/active/passive.


SQL Server 2008 Database growth issue. I am a .net developer and not an expert database administrator.

I have an application running on SQL server 2008.  Yesterday there was an issue with a certain query getting executed in an infinite loop which caused the database to grow from 500 MB to 380 GB.  The log file itself was 300 GB and the mdf file 80 GB.

Today I stopped the query and truncated the log file to 12 MB.  Then I figured out which tables had grown and truncated them, but still my database size is 67 GB!

I ran a query to determine the size of all tables https://stackoverflow.com/a/7892349/287100

However, the total size of all tables was less than 100 MB.  I want to know why the database size is so big and how I can shrink it back to around 500MB.

Thanks.

You can shrink an MDF file using:

USE [Databasename]
GO
DBCC SHRINKDATABASE(N'Databasename' )
GO

and you can shrink an LDF file using:

USE  Data base
GO
DBCC SHRINKFILE(databsename_Log, 1)
GO


Combine tsvector and timestamp in one index? I have a table that has a text field, and a timestamp. I have various indexes, such as a btree on the timestamp (this works great for "get the most recent N") and a GIN on the text (for full text search, looks like CREATE INDEX foo ON bar USING GIN (to_tsvector('english', the_text)).
I need to support a query that looks like SELECT * FROM foo WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?) ORDER BY timestamp DESC LIMIT 1000. This works fine for when the text query has very few matches, but when the query has a lot of matches in the GIN, it takes an unbelievably long amount of time, since it seems to be grabbing the timestamp from the heap for each one, then sorting.
Is there any way to create a single index on both columns that supports this?
For example, if I had two normal columns a and b, I know that creating an index on (a, b) would speed up SELECT * FROM table WHERE a = ? ORDER BY b DESC LIMIT 1000. Is there an equivalent for when a is a full text search and b is a timestamp?
I've tried CREATE EXTENSION btree_gin then creating an index on (to_tsvector('english', the_text), timestamp) or (timestamp, to_tsvector('english', the_text)) using GIN or GiST. But none of those four indexes seem to change the query plan on a test table with dummy data. I could try them in production, but they would take a very long time to create (days).

You could create a RUM index like this:
CREATE INDEX ON bar USING rum (
   to_tsvector('english', the_text) rum_tsvector_hash_ops,
   timestamp
);

And query like
SELECT * FROM foo
WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?)
  AND timestamp < '2021-01-01 00:00:00'
ORDER BY timestamp DESC
LIMIT 1000;

You need a WHERE condition with timestamp for the RUM index.


how to solve Invalid usage of the option NEXT in the FETCH statement I am using jtds 1.2.2 driver with SQL Server 2008. Below is the query I tried to execute

SELECT * from AM_API  FETCH NEXT 10 ROWS ONLY

Anyway after executing above query I got below error

Caused by: java.sql.SQLException: Invalid usage of the option NEXT in the FETCH statement.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:777)
    at org.jaggeryjs.hostobjects.db.DatabaseHostObject.executeQuery(DatabaseHostObject.java:510)

Any idea how to solve this issue?

UPDATE

I have read this that saying FETCH is working on SQL Server 2012 onwards only. Then how to limit result in SQL server 2008. The query going to execute is not under my control. I just postfixed FETCH NEXT 10 ROWS ONLY to the query to limit the result. So is there a way to limit the result by adding a limit query as a postfix to the query.

Upgrading to SQL Server 2012 will help but you'll still get the same error. From the documentation:

Limitations in Using OFFSET-FETCH:

ORDER BY is mandatory to use OFFSET and FETCH clause.
OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

To return a non-deterministic 10 rows from your table you could do this in SQL Server 2012:
SELECT *
FROM AM_API
ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

If you cannot upgrade you have a few additional options. SET ROWCOUNT is not deprecated for SELECT statements so the following should work on SQL Server 2008:
SET ROWCOUNT 10;

SELECT *
FROM AM_API;

SET ROWCOUNT 0;

You could also use a TOP expression:
SELECT TOP 10 *
FROM AM_API;


I have a query on two big tables that is too slow! I have a simple query:

SELECT  t1."TimeS" as TimeS, t1."JobID" as JobID, t1."TaskIndex" as TaskIndex,  t1."MachineID" as MachineID ,t2."EventType" as EventType,  t1."UserName" as UserName, t1."CpuReq" as CpuReq, t1."MemReq" as MemReq,
  t2."TimeS" as FinTime  , (t2."TimeS"-t1."TimeS") as duration
FROM  scheduled_tasks_table  as t1 ,  public.dead_tasks_table as t2
     WHERE t1. "TaskIndex"= t2. "TaskIndex"
     AND t1."JobID"= t2."JobID" AND (t2."TimeS"-t1."TimeS")>='0'

I have 15GB memory (RAM) and my Config File is:

max_connections = 200
shared_buffers = 3840MB
effective_cache_size = 11520MB
work_mem = 19660kB
maintenance_work_mem = 960MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Both my tables both have a single primary key and a single indexe as:( TaskIndex, JobId, Timet)

But this query is really slow:

EXPLAIN:

" "Merge Join  (cost=19982628.08..1436656715.61 rows=20251904912 width=105)"
"  Merge Cond: ((t1."TaskIndex" = t2."TaskIndex") AND (t1."JobID" = t2."JobID"))"
"  Join Filter: ((t2."TimeS" - t1."TimeS") >= '0'::bigint)"
"  ->  Sort  (cost=12319742.87..12438008.64 rows=47306308 width=93)"
" Sort Key: t1."TaskIndex", t1."JobID""
"  ->  Seq Scan on scheduled_tasks_table t1  (cost=0.00..1438498.08 rows=47306308 width=93)"
"  ->  Materialize  (cost=7662858.23..7874671.39 rows=42362632 width=28)"

Probably problem is here:

(t2."TimeS"-t1."TimeS")>='0'

Postgresql does not use index for this. Can you try just:

t2."TimeS" >= t1."TimeS"


Why does DELETE not work after an UPDATE in a CTE? CREATE table test (
    id integer,
    x integer
);

INSERT INTO test VALUES (1, 1);

SELECT works:

WITH res AS (
    UPDATE test SET x = 10 WHERE id = 1
    RETURNING id
)
SELECT id FROM test WHERE id IN (SELECT id FROM res);

id
---
 1
(1 row)

But DELETE does not work:

WITH res AS (
    UPDATE test SET x = 10 WHERE id = 1
    RETURNING id
)
DELETE FROM test WHERE id IN (SELECT id FROM res);

DELETE 0

Common table expressions (CTE) of the same statement are all based on the same snapshot of the database. I.e. all sub-statements see the same state of underlying tables.

Your DELETE tries to modify the same row(s) that your UPDATE already modifies.
The manual addresses your case exactly:

  Trying to update the same row twice in a single statement is not
  supported. Only one of the modifications takes place, but it is not
  easy (and sometimes not possible) to reliably predict which one. This
  also applies to deleting a row that was already updated in the same
  statement: only the update is performed. Therefore you should
  generally avoid trying to modify a single row twice in a single
  statement. In particular avoid writing WITH sub-statements that could
  affect the same rows changed by the main statement or a sibling
  sub-statement. The effects of such a statement will not be
  predictable.

Bold emphasis mine.


Handling lossy data before character- set migration I am trying to migrate a database from US7ASCII to AL32UTF8 using CSSCAN. During the initial scanning using CSSCAN we have found the presence of lossy data both in data dictionary() and application data.Can any one please help as to how can we handle the lossy data before converting database from US7ASCII to AL32UTF8 .

**USER.TABLE|COLUMN      LOSSY**

SYS.SOURCE$|SOURCE         143

Straight from the Manual:

  The easiest approach to migrating convertible data dictionary data is to create a new database in the target character set and then re-create all of the data dictionary and schema definitions by exporting and importing. However, this method means creating a new database.

Based on what I'm reading in this manual, that's hands-down the safest and easiest way to do this migration.


Is there a way to improve performance of this simple SELECT query? I'm still relatively new to MySQL so please forgive any ignorance on my part. I've tried looking through other posts on here and StackExchange, but haven't had much luck in improving the performance of this simple SELECT query. Let me start with the table schema, the SELECT statement, and the EXPLAIN for it:

Table:

CREATE TABLE `Observations` (
  `InstrumentID` bigint(20) NOT NULL,
  `Epoch` bigint(20) NOT NULL,
  `EndingEpoch` bigint(20) DEFAULT NULL,
  `PhenomenonName` varchar(16) NOT NULL,
  `Value` decimal(18,5) DEFAULT NULL,
  `IsEstimated` bit(1) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`InstrumentID`,`PhenomenonName`,`Epoch`,`Value`),
  KEY `FK_31` (`PhenomenonName`),
  KEY `idx_Epoch` (`Epoch`),
  CONSTRAINT `FK_27` FOREIGN KEY (`InstrumentID`) REFERENCES `Instruments` (`InstrumentID`),
  CONSTRAINT `FK_31` FOREIGN KEY (`PhenomenonName`) REFERENCES `Phenomena` (`PhenomenonName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SELECT:

SELECT PhenomenonName, Value, Epoch
FROM Observations
WHERE InstrumentID=2
AND Epoch BETWEEN 1514782800 AND 1546318740
AND PhenomenonName IN ('demand')
ORDER BY Epoch

EXPLAIN:

{
    "id": 1,
    "select_type": "SIMPLE",
    "table": "Observations",
    "partitions": null,
    "type": "range",
    "possible_keys": "PRIMARY,FK_31,idx_Epoch",
    "key": "PRIMARY",
    "key_len": "34",
    "ref": null,
    "rows": 1042464,
    "filtered": 100,
    "Extra": "Using where"
}

Sorry the explain is in JSON, I know that's not typical but the network configuration needs to be updated for my company's VPN so I can't connect via Workbench right now.

So, this query is for 1 year of 1-minute data, which is ~525K rows out of a table of ~18M rows and it takes ~22 seconds. Originally the PRIMARY key had Epoch before PhenomenonName, which was forcing it to do an index_merge_intersect. But, fixing the order of the key (which as you can see leads to it just using the PRIMARY key now) only shaved off 1 second. I've also tried running ANALYZE on the table, and changing Epoch BETWEEN 1514782800 AND 1546318740 to Epoch >= 1514782800 AND Epoch <= 1546318740, both of which seemed to make no difference.

Granted the hardware right now is very small since we're still developing the server--only a db.t2.small, which is 1 vCPU and 2GB RAM. However, increasing it to a db.t2.large, 2 vCPU and 8GB RAM, brings it down to 13 seconds. And then increasing it again to a db.t2.2xlarge, 8 vCPU and 32GB RAM, doesn't seem to improve performance--it's still ~13 seconds. Also, the storage is 100 GiB SSD and the buffer pool size is 75% of total RAM as per AWS's default setting.

Is this just a hardware limitation? If so, that's fine, but I want to make sure I'm not missing any opportunities to optimize the table schema or select statement, especially since this is just a query for one instrument, and queries will regularly have to be made aggregating several of them together.

Thanks in advance, and please let me know if I can provide anymore information.

Edit: By the way, this is unrelated, but as you can see I have two foreign key restraints, but only one of them has created an actual key. Is there any reason for this? My understanding was that foreign key restraints automatically create a key.

Edit2: The query took ~23 seconds on the small instance, and since adding Value to the PRIMARY KEY as per @SQLRaptor's suggestion, it's down to ~21 seconds. Still about ~13 seconds on the db.t2.2xlarge.

525K rows takes time to shovel.  It takes time to read from disk (unless it happens to be cached in RAM.  It takes time to send that many rows across the wire to the client.  And the client has to receive them.

The index is optimal; it has the desired columns in the desired order.  But it is suspicious.  Keep in mind that a PRIMARY KEY is, by definition, unique.  It is usually not reasonable to have a "value" in the PK.  And there is no difference if you leave it out.

One thing that will make a small improvement is to shrink the datatypes where practical.  Almost no application need BIGINT, but you have 3.  BIGINT is 8 bytes.  INT is only 4 bytes and can hold a timestamp (a la epoch).  Surely you don't have billions of 'Instruments'.  PhenomenonName could be normalized or perhaps turned into a 1-byte ENUM.


Is it worth adding or rebuilding an index on a table already involved in an active query I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due to a database shrink.
So the position I'm in is basically waiting it out and stewing on what I forgot to do. I'm not going to kill the query as then I will enter the even longer and more frustrating roll-back hell.
But this has got me thinking...is there a way to add an index on a table already actively running a query and would the late arrival of healthy indexes speed up the remainder of that query?
After thinking about it my feeling is that just rebuilding an index on a table already involved in a running query would probably be very slow and slow down the query itself resulting in an even longer wait, but, is there a way to pause a query to carry out the addition of an index?
I suspect not as suspending one query mid-way to allow other things to happen feels like it could lead to mayhem, but I'm just interested to know if anyone has had any experience of this?
------------------- EDIT --------------------------------
Thanks for your responses.
This is the offending query complete with its plan:
https://www.brentozar.com/PasteThePlan/?id=SyP_Cu3Hu
Here's the query that ran much quicker without the clustered index:
quicker query

It is not possible as the rebuild operation would be blocked due to the actively running query holding a lock on the table, and therefore it wouldn't complete until after the running query completed. Note this is to ensure data consistency, as some of the data pages of the index could contain a mix of data that was processed and data that was not yet processed by your query, and moving that data around could result in repeated reads of data for your query, resulting in invalid results, etc.
A couple other things I'd like to note, I'm not sure why a SHRINK was recently run on your database, but just a heads up that rebuilding your indexes will cause the database to regrow again. (I'm not saying you shouldn't rebuild your indexes, as 95% fragmentation is high, but just a heads up on how SHRINK is usually a wasted operation.)
On that note, 95% fragmentation is not usual unless your tables don't have clustered indexes on them. Just wanted to make sure you do have clustered indexes on your tables (as it is only in rare edge cases one would not use them)?
Also, there's unfortunately no way to determine exactly how long a rollback will take. It's possible the rollback could be anywhere between instantaneous to longer than the query has been running for. So if you want to try killing it, it is possible you get lucky and it rolls back quicker than the remainder it would've taken to complete. But it's hard to say. (I've rolled back hours long running queries in seconds before, but it's no guarantee.)
Finally, even with 95% index fragmentation, 12+ hours for a query to run is also an unusual amount of time. I'd be curious how big your tables are, and what kind of query it is that's running so slowly, if you'd be willing to share. I'd bet it can be sped up a lot more, even after rebuilding your indexes (as that only helps so much).


Fastest way to determine size of groups of FILESTREAM objects We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects.

To date, we have been using queries like select ClientID, sum(datalength(FilestreamColumn)) from dbo.tblFilestream group by ClientID; however, this takes a very long time to complete on a large dataset, and I imagine it is fairly hostile to the buffer cache.  I can perform a directory listing, parse the entries in Excel, and get the results back faster than SQL does.  However, that's because the dir command isn't reading the contents into memory.

We could add a new column to store this info and maintain it each time we perform an operation, but we'd prefer not to.  That being said, we may have no other choice.

Regarding the 'all objects' part of the question - this question indicates that sys.database_files can be used, but it is always 0 for us, even in SQL 2008 R2 SP2.

Don't know if its the fastest but I grouped the Filestream column into batches of 5000 rows then calculated each batch size before creating a running total:

CREATE PROCEDURE CalculateFSSize @RowCnt
AS

BEGIN

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'FSSizeBatch' AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.FSSizeBatch

CREATE TABLE FSSizeBatch(BatchID INT
                                ,StartID INT
                                ,EndID INT
                                ,BatchCnt INT
                                ,BatchSize DECIMAL(18,4)
                                ,RunningTotal DECIMAL(18,4))

DECLARE @BatchID INT
    ,@StartID INT
    ,@EndID INT
    ,@BatchCnt INT

DECLARE cur_1 CURSOR FOR

    SELECT ((Rank - 1) / @RowCnt) + 1 AS BatchID
            ,MIN(ID) AS StartID
            ,MAX(ID) AS EndID
            ,COUNT(*) AS BatchCnt
    FROM (
        SELECT RANK() OVER (ORDER BY ID) Rank
            ,ID
        FROM [dbo].[FS]
        ) T
    GROUP BY ((Rank - 1) / @RowCnt) + 1
    ORDER BY BatchID

OPEN cur_1
FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO FSSizeBatch(BatchID, StartID, EndID, BatchCnt, BatchSize)

SELECT @BatchID
        ,@StartID
        ,@EndID
        ,@BatchCnt
        ,SUM(CAST(DATALENGTH([JSONDATA]) + DATALENGTH([ZIPDATA]) AS DECIMAL)/1024/1014 )
FROM [dbo].[FS]
WHERE ID BETWEEN @StartID AND @EndID

  FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt
END

CLOSE cur_1
DEALLOCATE cur_1

;WITH a AS (
        SELECT BatchID, RunningTotal, SUM(BatchSize) OVER(ORDER BY BatchID ROWS UNBOUNDED PRECEDING) AS RowTotal
        FROM FSSizeBatch
        )
UPDATE a
SET RunningTotal = RowTotal

SELECT *
FROM FSSizeBatch

END


Using only vanilla PostgreSQL, how do I predict a future number based on existing data? I have a table called "events", with a timestamp column.

Based on that timestamp column's current records, I want to determine when the next record in the future is likely to occur.

I don't want to install any software, extensions, or pay for any APIs or register any accounts. I'm talking about the most simple and easy-to-express method to get a fair estimate of which future timestamp will correspond to the next record.

Is there some kind of basic mathematical formula for this? The expression should not know anything about what kind of data is in the table, nor care about it, and just use some kind of math such as taking the sum() of all the existing timestamps and dividing them by the position of the moon or something.

Anything which will produce a seemingly sensible estimate. "For entertainment purposes only", rather than life-critical medical decisions.

Besides the point that there is literally a whole branch of mathematics which is only focusing on answering your question, which should give you a hint that there is no "silver bullte" easy formula to use in any case.

WARNING I only have a basic understanding of statistics but I know that you cannot use the mean as a reliable value to predict future values. In almost all, if not all, cases a prediction depends very much on the distribution type of your events. This mean value will only get you a rough estimate which might be better than selecting a random number.

Anyway here is my implementation of a mean timestamp between two events:

Create table eventsTable(
  id int,
  TS timestamp);

Insert into eventsTable VALUES (1, '2020-05-07 08:00');
Insert into eventsTable VALUES (1, '2020-05-07 08:05');
Insert into eventsTable VALUES (1, '2020-05-07 08:10');
Insert into eventsTable VALUES (1, '2020-05-07 08:15');
Insert into eventsTable VALUES (1, '2020-05-07 08:20');
Insert into eventsTable VALUES (1, '2020-05-07 08:25');

SELECT
  EXTRACT(EPOCH FROM (Max(TS) - Min(TS))) / ( COUNT(*)  -1) as Mean_Number_Of_Seconds_Untill_Next_Event
FROM eventsTable
WHERE TS > '2020-05-07 08:10'

Fiddle link

It just takes the maximum and minimum value and divides it by the number of new events in that time interval. Use the where clause to find a good time interval for performance and prediction correctness.


Identify whether the given servers time is in daylight saving or not I have to get local date data across many servers scattered around the globe and identify whether it is in daylight saving mode or not ?

Need help.

This is solved in SQL Server 2016 with AT TIME ZONE. And I wrote an article about it here: http://sqlperformance.com/2016/07/sql-plan/at-time-zone

In earlier versions you'll probably need some CLR to read that information from the registry.


Maintaining mongodb for a huge list of message "documents"? Intro:

I am working on an app right now for live messaging and it's almost complete. The database strategy is in order. So when a new message is created it is sent as an object fitting my mongoDB model, nice.

Theory:

I am no expert (obviously), but I have a few things in mind. One, somehow limit the list of documents to maybe 1,000 and export the rest somewhere (not a million dollar idea, but just brainstorming). That's really it lol!

Question:

So I am asking what is the best way to manage a huge list of documents, most at the tail end never being needed (just saved to go back on some conversations). Thinking about it most old messages when you scroll far up (skype, text, etc) disappear after awhile. So I am thinking my theory isn't so bad?

Is there a standard for handling a huge list? Should I just leave it alone, I feel like it would become hard to manage?

A capped collection would allow you to maintain a relatively small list, though you would want to have a decent buffer, or be very confident in terms of the size of your documents.  The other option would be a TTL collection, but that would be more prone to fragmentation since it will be doing a lot of deletes (this will mean having to deal with said fragmentation regularly with repair, compact or a resync).
Based on what you outline, this sounds very like what people have done when looking to implement a message queue with MongoDB - you use the capped or TTL collection for your immediate needs, then you decide what level of durability you want for the documents long term. That is, store them in another collection, database, or even another instance of MongoDB entirely before they are removed.  At a larger scale I have even seen people have long term and short term shards, but that should be a long way down the line from your initial attempts.
If you do go down the path of a capped collection, with "long term" storage elsewhere, it is often beneficial to have the capped collection on one disk and the other data elsewhere (capped collections tend to have sequential access patterns, whereas regular collections tend towards random access).  That means using different databases, different disk mount points, and the directoryperdb option.
There are a lot of potential options here but thankfully several of the approaches have been publicised and documented.  To start I would take a look at these:
http://www.mongodb.com/presentations/mongodb-message-queue
http://captaincodeman.com/2011/05/28/simple-service-bus-message-queue-mongodb/
http://blog.mongodb.org/post/29495793738/pub-sub-with-mongodb
Hopefully that's enough to get you started.


Fan-In replication 4 Master -> 1 slave I am trying to merge 4 source db's (one for each region) into a global db. We use ID ranges as primary key on the tables in each region and so the merge should happen nicely. I have a fairly high volume of updates/inserts occurring in these regional db's, about 1MB a second.

However -

The blackhole solution does not quite work for more than two master DB's. I have 4 source DB's and chaining them serially like that would slow down the updates/inserts too much and overall make the system too fragile.
I don't think the stop slave/switch master/start slave loop would work either because that would slow things down too much. All source db's update at the same time.
I can't use MariaDB since it's still in Alpha and this is a production environment.

The source DB's have all the same name and exact same table structure. Although when there are upgrades, we will have situations where the table structures are out of sync for a few days during gradual rollout.

Here is what I tried -

One Slave for each region source master
Each Slave has federated tables that point to tables in the Global DB
Triggers on the replicated table in the slave pass on the change to the Global DB via updates/inserts/deletes on the federated table

Problem here - the update/deletes on the Federated tables in the slave, seem to trigger "select *" within the global db for some reason and are s-l-o-w. Yes, I have the proper indexes all around.

Help!! Any other ideas?

There is no solution for fan-in with traditional MySQL replication.

Another option is Tungsten Replicator. Here are a couple of posts on how it supports fan-in replication:

http://narmitag.wordpress.com/2012/09/27/setting-up-fan-in-using-tungsten-replicator/
https://code.google.com/p/tungsten-replicator/wiki/TRCMultiMasterInstallation

Another option is Galera replication, which is integrated with Percona XtraDB Cluster and MariaDB Galera Cluster. In Galera, all cluster nodes take replicated changes from all other cluster nodes. Which satisfies your goal of fan-in -- sort of -- but it means that all nodes have all the data.

For what it's worth, MariaDB 10 made their General Availability announcement on March 31, 2014.

I don't recommend the Federated storage engine. It should really be deprecated, because it's slow, buggy, and fragile. My feel is that MySQL is considering scrapping it. See http://www.tocker.ca/2013/10/17/how-do-you-use-the-federated-storage-engine.html


with (tablock) does not seem to make code faster for insert select I read about minimal logging and that one should use with (tablock) for insert selects. I had high expectations when I added this but it does not seem to make a difference. I use it in ETL and tables are empty (truncated) and the recovery model is "simple".
Am I missing something? I also see tablockx sometimes but when I read about minimal logging it mentioned specifically tablock.
E.g. here it does not mention tablockx https://docs.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver15

How many rows are you inserting at one time? Despite what the comments say, minimal logging does affect performance, but it's very minute and maybe a moot thing to worry about.
Using the TABLOCK query hint moreso helps with large data insert performance because it tells the SQL Optimizer to take a Table Lock upfront, as opposed to going through Lock Escalation which can be costly at runtime for large batches of data.
Generally without that hint, an insert might cause a bunch of Row Level Locks to occur first, and then when the SQL Engine realizes it crossed a certain threshold of Row Level Locks, it escalates to a Table Lock anyway, which means it wasted it's time spinning it's wheels acquiring Row Level Locks to start with. This is why for a large insert of rows, using that hint can be helpful.
Typically you need to be inserting at least a certain number of rows (I believe at least 102,400 rows per parallel thread executing the insert) for the TABLOCK query hint to be helpful with performance, otherwise it doesn't matter or in some cases can even hinder performance as Erik Darling wrote up in this interesting article.


ORDER BY with IF-ELSE statement (DESC doesn't work) Descending sort type does not work with this syntax, please help.

<?php

require_once("../connection/conn.php");

$compare = $_GET["sortfield"];

if(isset($_GET["sortfield"])){
    if(isset($_GET["sorttype"]) == "asc"){
        if($compare == "gender"){

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY gender ASC, first_name ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "last_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "first_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "timestamp") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);
        }else{

        }

    }elseif(isset($_GET["sorttype"]) == "desc"){

        if($compare == "gender"){

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY gender DESC, first_name DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "last_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "first_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "timestamp") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);
        }else{

        }

    }else{

    }
}else{
    echo json_encode(["Message"=>"No Sortfield and Sort type found!"]);
    echo "<br>";
    echo json_encode(["Message"=>"Please select the sortfield and sort type!"]);
}

Welcome.

Please notice that you are in a wrong place.

dba - here you can give your mysql code alone and ask for help.

You might have asked this question in stackoverflow.

Anyway here is what you miss.

if(isset($_GET["sortfield"])){

is right. But

if(isset($_GET["sorttype"]) == "asc"){

is not right as the expression to the left of equal condition operator would evaluate to true or false and if you check the logical value == "asc" it will fail. It should be

if(isset($_GET["sorttype"]) and $_GET["sorttype"]== "asc"){

or simply

if($_GET["sorttype"]== "asc"){

if you are sure that it is already set (as of the first if).

Also, please go through this before asking. https://stackoverflow.com/help/minimal-reproducible-example

If you had gone through the mentioned page, formatted your question clearly and asked at the right place, you would have got your answer on day one itself for sure.


Group-by in an Oracle database on an Oracle database. If I make an:

select id,id_operator,denumire,cod_fiscal,loc_den,str
 from rap where is_punct_lucru=1
 and id_operator in (
    select id_operator from rap
        where is_punct_lucru=1
        group by id_operator,denumire,loc_den,str having count(*) > 1
    )
order by id_operator

I get:

ID      ID_OPERATOR     DENUMIRE            COD_FISCAL      LOC_DEN                 STR
70446   762200          SC AF SRL           xxx-453502      Alba Iulia              SOSEAUA DE CENTURA
70430   762200          SC AF SRL           xxx-453449      Alba Iulia              SOSEAUA DE CENTURA
70445   762200          SC AF SRL - OCNA    xxx-379128      Ocna                    MIHAIL EMINESCU

but if I "shoot" just the

select id_operator,count(*) from rap
        where is_punct_lucru=1
        group by id_operator,denumire,loc_den,str having count(*) > 1

it gives me just 2 rows on 762200 because of the LOC_DEN and STR.
How can I include the ID in the group by without loosing the rows...???

ok, I solved this by:

select * from
(
    select id,id_operator,denumire,loc_den,str,
           row_number() over (partition by
                     id_operator,loc_den,str order by id_operator) as no
    from rap
    where is_punct_lucru=1
    order by id_operator
)
where no>1


Should I add transitive foreign keys? Simple example: there is a table of customers.

create table Customers (
  id integer,
  constraint CustomersPK primary key (id)
)

All the other data in the database should link to a Customer, so e.g. Orders looks like this:

create table Orders (
  id integer,
  customer integer,
  constraint OrdersPK primary key (customer, id),
  constraint OrdersFKCustomers foreign key (customer) references Customers (id)
)

Suppose now there is a table linking to Orders:

create table Items (
  id integer,
  customer integer,
  order integer,
  constraint ItemsPK primary key (customer, id),
  constraint ItemsFKOrders foreign key (customer, order) references Orders (customer, id)
)

Should I add a separate foreign key from Items to Customers?

...
constraint ItemsFKCustomers foreign key (customer) references Customers (id)

A picture instead: should I add the dashed line/FK?

Edit: I have added primary key definitions to the tables. I'd like to re-iterate on the point I made above: the database is basically siloed by customers, as a correctness / security measure. Therefore, all primary keys contain the customer ID.

I think this is the  original idea.

First thing to notice is that the PK on the LineItem table has three attributes {CustomerID, CustomerOrderNo, OdrerItemNo}, as opposed to just two in your example.

Second thing to note is the confusion resulting from the use of the generic id name for an attribute.

The CustomerOrderNo should ideally be (1,2,3..) for each customer and OrderItemNo (1,2,3 ...) for each order.

Well, this is nice if possible, but requires a query looking for the previous max value, like

select max(CustomerOrderNo)
from Order
where CustomerID = specific_customer ;

which is often not preferred in high-transaction-volume environments, so it is common to see these replaced by an auto-increment, essentially serving the same purpose. It is true that this auto-incremet is now unique, hence it can be used as a KEY -- but you may choose to look at it as a necessary compromise for the OrderItemNo.

So, with some renaming CustomerOrderNo -> OrderNo and OrderItemNo -> ItemNo you may arrive to this model

So now if you look at the Order the following are unique

{OrderNo}             -- PK
{CustomerID, OrderNo} -- superkey,  AK on the diagram.

Note that {CustomerID, OrderNo} is propagated to the LineItem to serve as a FK.

If you squint a bit, this is close to your example, but with PKs {ItemNo} and {OrderNo} only -- as opposed to two column PKs from your example.

Now the question is, why not simplify to something like this?

Which is fine, but introduces PATH DEPENDENCE -- you can not join LineItem with Customer directly, must use Order in the join.

I prefer the first case when possible -- you choose your favourite. And obviously, there is no need for direct FK from LineItem to Customer in these three cases.


operator does not exist: interval < date I want to do the following comparison :

IF ( V_C_CRITERE2.DATE_FIN_SOUHAITEE - TO_DATE(V_SEUIL_ALERT) < CURRENT_DATE) THEN

DATE_FIN_SOUHAITEE is a DATE.

But a get this error :

operator does not exist: interval < date

How can i do the comparison?

Although the question states that DATE_FIN_SOUHAITEE is a DATE, the error message "operator does not exist: timestamp without time zone - numeric" makes it clear that it really is a timestamp. In Postgres - unlike Oracle - a timestamp is something substantially different than a date (in Oracle they are more or less the same thing - they just differ in the precision).

In Postgres you can only subtract an interval from a timestamp, and you can subtract integer values from a date

If V_SEUIL_ALERT is indeed a varchar that stores a number that represents years, you need to convert that string value to a proper interval in order to be able to subtract it from a timestamp:

V_C_CRITERE2.DATE_FIN_SOUHAITEE - make_interval(years => V_SEUIL_ALERT::integer)

the above can then be compared using < with current_date.

I don't know what the original code in Oracle did, because DATE_FIN_SOUHAITEE - V_SEUIL_ALERT would implicitly convert V_SEUIL_ALERT to a number which is then taken as days to be subtracted from DATE_FIN_SOUHAITEE, not years.

If that column indeed stores days rather than years (as stated in the comments), you need to use:

make_interval(days => V_SEUIL_ALERT::integer)

You should really take the opportunity of that migration and fix the wrong data type for the column V_SEUIL_ALERT to be an integer, numeric or even interval if that is what you use it for. Never store numbers in VARCHAR columns. That was already a really, really bad idea in Oracle.


Processes dissappear from processlist (MariaDB) In my application I need to monitor list of process ids for some database user, so I execute SHOW PROCESSLIST command to get information.
The problem is that sometimes some Id dissapears from command output but it comes back on next execution. For me it seems like a bug, what cause this strange behaviour?

Example:

1934,1935,1936,1937,1938
1934,1935,1936,1937,1938
1934,1935,1936,1937,1938
1934,1935,1936,1938
1934,1935,1936,1937,1938

Version:

MariaDB [db_w1]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.1.24-MariaDB |
+-----------------+
1 row in set (0.00 sec)

Processes, in the MySQL PROCESS LIST, are representations of connections from outside the server. A mysql -h {host} will create a client connection. They are transient in that, if a client disconnects, the server process no longer exists.

As such a process showing in a process list can no longer be there on subsequent access if the connection was disconnected from the client.