texts: select top 5 accounts in each region in an month per sales

texts:
My data is in table Sales

ACCT_ID |REGION | DATE_FILTER | SALES
1001    | TEXAS |  2018-01    | 10
1002    | DENV  |  2018-01    | 30
1004    | WDC   |  2018-01    | 50
1001    | TEXAS |  2018-02    | 500
1002    | DENV  |  2018-02    | 300
1004    | WDC   |  2018-02    | 120

I want to find out rank of each account in a district per month by sales my output will look like

ACCT_ID |REGION | DATE_FILTER | SALES   | Rank
1001    | TEXAS |  2018-01    | 10      |3
1002    | DENV  |  2018-01    | 30      |2
1004    | WDC   |  2018-01    | 50      |1
1001    | TEXAS |  2018-02    | 500     |1
1002    | DENV  |  2018-02    | 300     |2
1004    | WDC   |  2018-02    | 120     |3

I am using Mysql 5.7 version, I have tried below

SELECT
        ACCT_ID ,REGION,SALES,DATE_FILTER,n RANK
    FROM
      ( SELECT  @prev := '', @n := 0 ) init
    JOIN
      ( SELECT  @n := if(DATE_FILTER != @prev_rpt_mnth and REGION != @prev_reg, 1, @n + 1) AS n, /* HERE I AM USING TWO COLUMNS TO GROUP BY */
                @prev_rpt_mnth := DATE_FILTER,
                @prev_reg := REGION
                ACCT_ID ,REGION,SALES,DATE_FILTER,UNITS
            FROM  SALES
            ORDER BY
                DATE_FILTER,REGION ASC,
                UNITS DESC
            LIMIT 999999
      ) x
    WHERE  n <= 5
    ORDER BY  DATE_FILTER,REGION, n;

I am getting duplicates as well it not properly ranking based on sales. Where I am going wrong and is there any other efficient way.


texts: Does the command SET STATISTICS...ON turn statistics information on for all queries running throughout the server or just the current connection?

texts:
In regards to SET STATISTICS TIME ON and SET STATISTICS IO ON I noticed in the remarks of the Microsoft doc, it says the following:

  When STATISTICS IO is ON, statistical information is displayed, and
  when OFF, the information isn't displayed.

  After this option is set ON, all Transact-SQL statements return the
  statistical information until the option is set to OFF.

Does that mean all Transact-SQL statements that execute in the current connection, or across the whole server?
When I tested it myself, it seemed to be only within the scope of my connection.


texts:
I have following table which records employee's attendance in a company.

    CREATE TABLE EMP_ATNDNC
    (
    WORK_DAY    DATE                         NOT NULL,
    EMP_ID      VARCHAR2(20)                 NOT NULL,
    STATUS      VARCHAR2(1)   DEFAULT '0'    NOT NULL,
    TMSTMP      TIMESTAMP,
    RCRD_VER    TIMESTAMP                    NOT NULL,

   CONSTRAINT  EMP_ATNDNC_PK PRIMARY KEY(WORK_DAY,EMP_ID),
   CONSTRAINT  EMP_ATNDNC_FK_DAY FOREIGN KEY(WORK_DAY) REFERENCES SCHOOL_OPEN_RCRD(WORK_DAY),
   CONSTRAINT  EMP_ATNDNC_FK_ID FOREIGN KEY(EMP_ID) REFERENCES EMP_RCRD(EMP_ID)
   );

As of now, it has two records:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM

If I run this query:

    SELECT * FROM EMP_ATNDNC WHERE WORK_DAY BETWEEN TO_DATE('24-AUG-16') AND TO_DATE('30-SEP-16');

I get only this record:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                          RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM     27-AUG-16 05.38.46.022000000 PM

Also If I run this query:

    SELECT * FROM EMP_ATNDNC ORDER BY WORK_DAY ASC;

I get this result:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM

Instead of:

    WORK_DAY    EMP_ID     STATUS           TMSTMP                             RCRD_VER
    26-AUG-16     9999       0      26-AUG-16 04.50.04.628000000 PM       26-AUG-16 04.50.04.628000000 PM
    29-SEP-16     1234       0      27-AUG-16 05.38.46.022000000 PM       27-AUG-16 05.38.46.022000000 PM

Why is this happening?


texts: How to get user name to appear in Alerts

texts:
Setting up some alerting in SQL server 2017 which get dispatched by email to certain DBAs.

Some examples of alerting I am trying to configure would be for Alert number 229 -  The EXECUTE permission was denied on the object 'X'

At the moment there is no details included in the email with regards to who had their permission denied - I am wondering, is it possible to include this?

Current notifications look like:

  DATE/TIME:    7/2/2018 1:29:59 PM

  DESCRIPTION:  The EXECUTE permission was denied on the object
  'sp_Price', database 'Customisations', schema 'dbo'.


texts:
I have a table LoanDeadline that has a column called deadline, it's simple text and not Date and it's in the format of

July 25, 2014

How do I compare this with sysdate ?
So far I have

SELECT 'YOUR LOAN IS APPROVED'
FROM LOANDEADLINE LD
WHERE SYSDATE < LD.DEADLINE;

But this doesnt work because my SYSDATE and LD.DEADLINE are different  data types.


texts: Is it possible to output multidimensional data from linear table without iteration?

texts:
I have the following table on a cycling club's website:

CREATE TABLE `mileage` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `rideID` mediumint(9) NOT NULL,
 `rideDate` date NOT NULL,
 `isWeekly` tinyint(1) NOT NULL,
 `riderUid` smallint(6) NOT NULL,
 `leaderUid` smallint(6) NOT NULL,
 `mileage` smallint(6) NOT NULL,
 `days` smallint(6) NOT NULL DEFAULT '1',
 `eBike` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8081 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Each entry represents one rider/trip, where the rider is the person who rode and the trip is the rider's part of a club ride.

When the site was built, the club wanted the ability to get mileage between dates (defaulting to current month or YTD).

The following query works well for that application (variables are from PHP):

SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
SUM(mileage) as miles,
COUNT(*) as cnt
FROM `mileage` m
    LEFT JOIN `members` w ON (w.uid = m.riderUid)
WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate')
GROUP BY m.riderUid
ORDER BY miles DESC

Now they've requested to get the data month-by-month for any given pair of dates. Meaning they want to see columns output for, say, January, February, March ... etc.

I suppose I can do this iteratively, using PHP to feed successive monthly start and end dates and then glomming them all together on the page, but that feels kind of clunky. I wonder if there's an easier way using a MySQL query.

So, is this kind of thing possible using a query or do I have to brute-force it using PHP or JavaScript? I should add that I'm not a database guru by any stretch, just a journeyman coder.


texts:
In SQL Server 2014 (currently 12.0.6205) when I disable backup jobs either manually through SSMS or through T-SQL, I am seeing that the job still runs regardless of the job's enable status. Do I need to also always disable a job's schedule as well? That seems wrong.

These jobs are backup jobs using maintenance plans
They weren't running when they were disabled


texts: SQL Server Login attempts fail when using SSMS but succeed when using SQLCMD

texts:
I have my SQLServer 2005 instance configured for mixed mode fine. When using SSMS, none of the sql server logins seem to work. Just "Login failed for user '..'" message. But when I use SQLCMD, it works fine. The behavior is not just for one specific login account. All sql server logins including 'sa' fail when tried via SSMS. Windows logins work fine.

Any ideas?


texts:
I am experiencing some messages in our Error logs on a Development database relating to TDE.  This is running on SQL Server 2017 CU21.
[TDE] SetDbeState on DEK 0x0000000000000000: Old State: None (0), New State: Unencrypted (1).
I've altered the DEK hash before posting.
This error message is logged twice every 15 minutes when I perform a log backup.  Additionally, we sporadically see these logged when there are no backups, but this differs slightly in that I have 3 messages - 2 with a New State: Unencrypted and 1 with a New State: Encrypted which is between the two unencrypted messages.
Querying the sys.databases field returns a 1 for the is_encrypted column for the user database and tempdb. Further, if I query sys.dm_database_encryption_keys, the user database and tempdb both return 3 (encrypted).
I have tested encryption state by taking a backup and attempting to restore locally which failed due to a missing certificate.  I cannot attach this database to another server without first adding it's certificate.
I'm not seeing an performance issues, but this is flooding the logs.


texts: Stored procedure in master slave replication

texts:
I have a master-slave replication for a schema products which is being replicated with binlog_do_db = products.

one of the the tables in the master schema is being archived by calling a stored procedure which is in schema products_archive.The data is moved to a different schema on the same database from products to products_archive.

START TRANSACTION;
        INSERT INTO products_archive.dial
        SELECT * FROM products.dial
        WHERE products.dial.dial_datestamp <= DATE(NOW() - INTERVAL 30 DAY);
        DELETE FROM products.dial
        WHERE producst.dial.dial_datestamp <= DATE(NOw() - INTERVAL 30 DAY);
COMMIT;

But the slave has only one schema products which is not being archived.
Since stored procedure is on the schema which is not being replicated the master should be archived and the slave shouldn't.

But what instead happened is the master archive was successful and the same data got deleted on the slave.

How did the data get purged on the slave as the schema products_archive is not added to list of replicated schemas.


texts:
I apologize if this question is too specific, if it is, please let me know!

I have searched around both on here and the web on selecting the best project-specific engine to fit your needs, and have come up with nothing. So, generally, what is the best way to select a host/DB Management System?

In my specific case, I will have a DB storing data from now on (forever?) from up to 150 sources, with data coming every 30s 24/7/365 (up to 300 rows added/min). Each source will be inserting into it's own table. The data will be read in bulk from each table - e.g. dumping 1k+ rows at a time.

A general explanation on how to highlight the positives and negatives of each option would be immensely helpful.

Once I've selected a platform to start on, what can I do to minimize the space I use? I plan to match data types with the data coming in, but here is an example:

{ (up to)12-digit unsigned int, date/time(epoch), unsigned long, signed int (<10k), unsigned int (<1k), unsigned int (<250), unsigned double (<500) }
{ 120308, 1428844432, 8222786, -5, 0, 0, 24.7 }

Thank you for any help you may have.


texts:
It is well-known that the SQL function SLEEP() can be used to attack web applications, e.g. as described here. The sleep function cannot be disabled since it is compiled into the MySQL code and does not require any special privilege.

My question is: does MySQL include any functions except SLEEP() which have all of the following properties?

The function is not commonly required by web applications. Say, 95% of CRUD apps don't need to use the function.
The function is not provided by an external plugin, but is included in the core MySQL codebase.
The function does not require any special privileges to be executed.
The function provides some non-zero benefit to attackers in a SQL injection.

My motivation for the question: I am interested in techniques to prevent SQL injections by limiting MySQL functionality. I am curious if SLEEP()-injections are the only candidate for this technique or if there are other attacks that could be prevented.


texts: Copy SQL Logins and Permissions From One Server to Another

texts:
I need to duplicate all Logins and their associated database-level permissions from one server to another. I am using SQL Server 2008 R2 for Server A and SQL Server 2012 for Server B I am able to find a few different scripts to transfer Logins from server A to server B. However, I nee to also transfer database-level permissions for each Login.

I've reviewed the following for transferring Logins:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
http://support.microsoft.com/kb/918992

The SQL Soldier solution seems to work fine.

The same databases that reside on server A also reside on server B. how can I automate/script the Login and permissions transfer from one server to another?


texts: TiDB vs Vitess - How should I base my decision?

texts:
I need a scalable MySQL-compatable solution that can scale to multiple installs across a network, handling millions of records on several tables with tens of thousands of concurrent requests per-second. I don't want to rely on pre-rolled cloud solutions.

I have come across TiDB and Vitess. These two are especially important for me because I want to use Acra for security. I found this blog post which puts Vitess under Distributed SQL 1.0 – NewSQL stating "Poor Fault Tolerance" and "No Multi-Shard ACID Transactions." Then, tidb under "Distributed SQL 2.0 – Globally Distributed SQL."

Under tidb I see "This choice essentially makes TiDB unfit for multi-region deployments since majority of transactions in a random-access OLTP workload will now experience high WAN latency for getting a timestamp from the global timestamp oracle running in a different region." This is concerning though idk if it's still true.

I have not been able to find any benchmarks comparing the two. I'm not sure how to evaluate which to use. I have a lot of records, about 20 tables and lots of joins. Pretty standard really except that I'm to a point where I know I need to expand beyond one dedicated server, both for more hardware and presumably fault tolerance.

How do Vitess and tidb compare/contrast?


texts:
I am doing this tutorial of Microsoft: Basic Data Mining Tutorial

http://technet.microsoft.com/en-us/library/ms167167.aspx

I am using SQL Server 2012 and SSIS - SQL Server Integration Services
but I got stuck on one problem,... it says: "Login time expired" ... it cannot connect to SQL Server...

I turned off the Windows Firewall but it didn't help.
I also made sure that TCP/IP and Named Pipes are ENABLED within SQL Server...

Any ideas?

Here is a screenshot:


texts: Multiple Selects With One Column Encrypted

texts:
I am trying to do two select statements on the same table. However I get errors. The reason for this query is one of the columns is 256 bit CRC encrypted. Here is my query:

SELECT (select COLUMNA from TABLE) AS 'NumberA',
(select CAST(aes_decrypt(COLUMNB,'key','IV') AS CHAR) from TABLE) AS 'Number2';

This query throws this error:

Error Code: 1242. Subquery returns more than 1 row

Both of these selects by themselves work fine. However I need to figure out how to select all of the columns and use the encrypted column in the query as well.

Also, if you can't answer the question, don't put it on hold. Just state you don't know the answer


texts: Exclude a Variable from getting logged in a CheckPoint file

texts:
I have an SSIS package in SQL Server 2014 which uses checkpoint to handle the cases where it needs to be run after a failure. There is a variable in the package that I want to be set to 0 whenever the package is run (regardless if it is an initial run, or after a failure which is based on a CheckPoint file).

Currently, when the package fails, the current value for this variable is logged in the checkpoint file and next time that the package will be run, it uses the value recorded in the checkpoint file which is not necessarily zero (the value that I want it to be initialised to EVERY time the package is run).

Is there a way to exclude a certain variable from getting logged in a CheckPoint file?

Thanks for your help in advance.


texts: Master port is disabled from Public IP, MySQL log is not getting refreshed

texts:
My MySQL Replication setup is over internet.
Master is on Public IP.

If I stop mysql service in master, immediately an error message is thrown in slave. And when I start the service, slave is immediately connecting to master and replication is happening well.

But if the port is disabled from the Public IP, no error is getting thrown in slave log and showing as connection success, but data is not getting replicating from master to slave. And if the port is enabled for Public IP, it is showing as connection success and data is not getting replicating from master to slave.

Until I restart the slave, data is not getting replicating from Master to Slave.
Once I restart the slave, every thing goes fine.

Can I know, what is the reason for not getting replicated.

And Is there any way to come to know the master status from the slave, when this type of behavior occurs.

Also is there any option to stop slave when master port is disabled.

Please help me on this.

I am using MySQL version 5.0.24.


texts: T-SQL Optimizing a Join on TOP Value from another table

texts:
I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going the incremental approach.

The basic format of our queries is as below, only I've stripped out about 20 more joins and 30+ more columns that would also be included.  The stripped out columns and joins are very straightforward with no aggregation, subqueries, or other types of calculation involved.  What's left is the main fact table (First_Source_Table) and the most problematic datapoint to collect.  Second_Source_Table consists of many records for each Account_ID, but we only want to include the first record for each Account_ID.

Now my constraints.  This in a replicated environment on SQL Server 2008.  Unfortunately I have no control over the source tables, and while I can add new indexes on them, they will be lost the next day.  I've tried calculating an in-between table off of Second_Source_Table before I do the full-table, but as that would need to be re-calculated each night, it didn't have a material impact on the overall calculation time.

The code below works, but if you look at the execution plan and IO Stats, the logic associated with Second_Source_Table constitutes about 80% of all resources used, but changing this field to NULL only cuts execution time in half.  I'll also point out again that being a replicated environment, there are no issues to worry about with locking or other users writing to the tables we're in.

INSERT INTO
    New_Table
SELECT
    First_Source_Table.Account_ID,
    (
        select
            top 1
            Second_Source_Table.Code
        FROM
            Second_Source_Table
        WHERE
            Second_Source_Table.Account_ID = First_Source_Table.Account_ID
        ORDER BY
            Second_Source_Table.ID
    ) as Code
FROM
    First_Source_Table


texts: UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)

texts:
I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID into another column.)

What I'm trying to achieve is to normalize the dataset. The not yet normalized data has no indexing. My thought was that I would not build indexes on the raw values, waiting, instead to index the foreign keys that will be replacing the varchar values with tinyint values after the update completes.

UPDATE A
SET A.AutoClassID = B.AutoClassID
FROM AutoDataImportStaging.dbo.Automobile as A
JOIN AutoData.dbo.AutoClass as B on (A.AutoClassName = B.AutoClassName)

Background

using MSSQL 2008 R2 on Server 2008 R2
server has 8 GB RAM
server has one RAID10, 7200 RPM SATA (not great, I know, in production this will only read data and not write data; plus recent HD shortage made this necessary for cost)
server has dual quad-core Xeon CPU
the machine is not doing anything else (currently dedicated to dev, only this process)
simple logging turned on (? - but does it still log so that it can rollback?)
note that the query references two different DBs, for what that's worth
"width" of a record in table getting updated is 455 bytes

Resources During Execution

physical RAM is maxed out
disk I/O is maxed out
CPU is hardly doing anything (choke point is I/O)
run time has been 14 hours and counting!

I suspect a few things like I need an index on the raw data, even though I will be dropping the column (AutoClassName) after the normalization updates. I also wonder if I should just loop down the table one record at a time instead of the JOIN, which seemed ridiculous at the time I started this, but now it seems that would have been faster.

How should I change my methodology for my remaining normalization updates (similar to this one) more quickly?


texts:
We are currently in the process of moving our development, test and live databases on to separate servers and instances and I am looking at how best to manage access for DBA tasks and also IT Support tasks (permission to read/write data but not execute SP's, create tables, etc), mainly in the live environment.

What is the recommended way to provide access for a DBA?  Is it good practice to create a windows AD account specifically for the DBA and then add it as a login to SQL Server with sysadmin server role?

What about managing access for IT Support team?  We really want to log/audit individual team members so we know who did what, etc.  Would this be best done using individual SQL Server Logins with each login mapped to a corresponding user in each database as needed (seems like a lot of maintenance would be required adding/removing users)?  Or is there a way to manage access using say a windows AD group with each team member using their own AD account but use the AD group in SQL Server to manage permissions (but still being able to log an individuals actions)?

Also, should IT Support team members login to SQL Server Management Studio (SSMS) from a server in the same domain as the SQL instance is running or can they install SSMS on their laptops and login remotely?  Issue here being their laptops are on different domain so windows auth may not work (although I did find a way to launch SSMS from command line and have it login using specified domain credentials), just not sure what best practice is here.


texts: How to restore Postgres cluster from pg_probackup archive

texts:
I'm trying to configure continuous backup solution using pg_probackup tool. So far I did the following:

Configured archive_command according to the docs
Created backup using pg_probackup backup command with a stream mode
Restored using pg_probackup restore on another computer

However, I failed to restore database backup by manually copying files and setting restore_command in recovery.conf. Here's what I've done:

Created new data dir and copied files from pg_probackup to that dir
Created recovery.conf with restore_command set to (I'm using Postgres on Windows so paths are Windows-style) '"<path to pg_probackup.exe>" archive-get -B <backup dir> --instance <instancename> --wal-file-path "%p" --wal-file-name %f'
Started Postgres server

As far as I can see recovery went fine - server is running. However, there're errors in the log file:

ERROR:  invalid page in block 0 of relation global/1262

and I assume something went wrong with recovery after all.

Can anyone please tell me what I'm doing wrong here?


texts:
Now I came across an issue, I have table as below:

Task Id       StudentId    Marks
  1                1         12
  1                2         15
  1                3         17
  2                1         15
  2                3         20
  3                1         8
  3                3         5
  3                5         17

I was required to get a lists of studentid who has max and marks in each task. So the required output is:

TaskId               StudentId      Marks

 1                    3            17
 2                    3            20
 3                    5            17

Now I created a temporary table to store max.marks of each task.

TaskId  Max.Marks

 1     17
 2     20
 3     17

Then I joined the temporary table to the main table and fetched the row with marks equal to max.marks of that task.

Can anyone suggest a better query without any sub-queries and temporary table?

Thanks


texts: How to select the first 10 records for each table in the same database using sql

texts:
I have 50+ tables in a database.

I don't want to use the following SQL on each table in a database.

select * from {table_name}

So, is it possible to display the first 10 records for each table inside the same database using sql?


texts:
I have an application which has logged an error like

Object <obj id> generated an exception.

The application store all its information in a SQL Server database which contains 100+ tables.

For sure somewhere there must be a table which contains all the objects of this type and I'm sure obj id is the key column there.

How can I find this object? Do I have to create 100+ SELECT queries to query each table or maybe there is a more convenient solution?


texts: Unable to Upgrade SQL Server from 2012 to 2016 SP2 in Cluster environment

texts:
I have a Windows 2012 R2 Datacenter cluster environment.
With active Passive nodes of SQL Server 2012 SP4.

I am trying to upgrade the SQl Server from 2012 SP4 to 2016 SP2.
I am performing in-place upgradation first on Passive node and then on active node.

My installation failed with below error.
Error: the cluster resource is not online. Bring analysis service server online before starting upgrading process

I have analysis services install on both the instance node.
First the Analysis service was stopped when I got the error, So I started the analysis services and re run the installation but it failed with same error.

Since analysis services are not required I tried to remove the feature but guess we cant add or remove features from 2012 cluster environment.

So, Basically I am stuck here and dont know how to proceed with the installation.

Can anyone please help me out!!

Thanks in advance.

@Learning_DBAdmin Following you lead, i checked for dependencies and found there are no dependencies on analysis services.

Also i dont see analysis service resource in roles tab in failover cluster manager.

Is it posssible that i havent properly configured the analysis services on the server?

Following your previous link i came to know that i need to create SSAS generic resource manually in the failover cluster manager. Can you help me to create SSAS generic resource in failover cluster manager.

Thanks in advance.


texts: MySQL - ERROR 1045 (28000): Access denied for user : permission issue

texts:
ex :on first server :

    mysql> show grants for 'test'@'localhost';
    +---------------------------------------------------+
    | Grants for test@localhost                         |
    +---------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' |
    +---------------------------------------------------+

on second server its showing like this :

    mysql> show grants for 'root'@'localhost';

    | Grants for root@localhost                                                                                |

    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****************' WITH GRANT OPTION |

1 row in set (0.36 sec)

So problem is when i try to grant all for any user or database then getting "subject line"


texts: Local CouchDB on Mac Opens New Browser Tab Every 5 Seconds

texts:
I am doing some testing with CouchDB. I downloaded the CouchDB application on my Mac and ran it - it opened in my browser, I verified, created a database, added docs ... then I closed the application.

Next time I opened the CouchDB application, it opened a screen with the side menu but no other content (see attached image) and proceeds to open a new and identical tab every 5-10 seconds. The only way I can stop it is by closing the CouchDB application.

Any ideas about what is going on here?


texts:
I created a sample table as below

CREATE TABLE [dbo].[StatisticsDemo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
   ) ON [PRIMARY]

Then I inserted below data as below:

SELECT NAME,COUNT(*) AS COUNT
FROM StatisticsDemo
GROUP BY NAME

NAME    COUNT
-------------
AABBCC  59999
XXYYZZ  1

Then I created below Non Clustered Index :

CREATE NONCLUSTERED INDEX [NCI_STATISTICSDEMO_NAME] ON [dbo].[StatisticsDemo]
(
    [Name] ASC
)

Now I ran the below query:

SELECT NAME FROM [dbo].[StatisticsDemo]
WHERE NAME = 'AABBCC'

As expected it is returning 59999 rows but it is doing Index Seek on Non Clustered Index. But as per my knowledge, it should do Index Scan as 99.99% of data satisfied the filter criteria mentioned in the select query.

Can some please tell me why it is doing Index Seek instead of Index Scan?

The purpose of the entire activity was to prove(as I am about to give presentation on statistics) that SQL Server looks into statistics to identify the number of records which matches the filter criteria of query before preparing the execution plan and based on the % of records matches out of total records in table, it will either decide to do a SCAN or SEEK. If % of records matches is approximately equal to total number of records in table, it should do SCAN. But that is not happening. Same is the case when I am using AdventureWorks2016 Database and running below query:

select * from [Sales].[SalesOrderHeader] WHERE SalesOrderID >= 43659 AND
SalesOrderID <= 73659

The above query returning 30001 records out of 31465. But it is still doing Clustered Index Seek.

I am getting terribly confused and it is shaking my concepts. :( Can some please help.

PS: I cleaned Plan cache as well but no luck. SQL Server version is 2016.


texts: PostgreSQL partial index unused when created on a table with existing data

texts:
In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/12026593/808921

I am attempting to make use of the "partial index" feature of PostgreSQL that Erwin Brandstetter recommends. I have a table with a couple million records already, and I would like to add the index to that table, like so:

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision)
WHERE deprovision = 0;

(the vast majority of the records will have deprovision = 1)

The problem is that when I try to use this index with the simplest of queries, PostgreSQL acts as if it isn't there:

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

The really strange thing is that I have found that if this index is created before there is data in the table, then it does actually work fine. Don't believe me? Here are some SQL Fiddle entries that prove it:

Partial Index Created After Inserts (Index not working)

Partial Index Created Before Inserts (Index working properly)

In both of those, just expand the "View Execution Plan" link to see what I'm talking about.

So, my question is - what do I have to do in order to get PostgreSQL to start using a partial index on a table that had data in it before the index was created?

BTW I'm also the developer of SQL Fiddle and this question is related to a new development effort I'm making for it.


texts: Can I use the same keywords/symbols as DATEADD and DATEPART in user-defined functions

texts:
The SQL functions DATEADD, DATEDIFF and DATEPART take a keyword parameter specifying the date/time period you're dealing with - it's not a varchar/string, it's a bare keyword. (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql)

I'm creating some user-defined functions that do date/time manipulation, and need to include an argument which does the same thing as these functions - i.e. I want to create a user defined function that looks like this:

DATESEQUENCE(DAY, '2017-01-01', '2017-06-01')

or

DATESEQUENCE(MINUTE, '2017-07-31 11:00', '2017-07-31 15:00')

Is there any way to define an argument to a user-defined function using the built-in set of keywords/symbols for date/time periods?


texts: add column without metadata lock in mysql 5.6

texts:
I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the reference articles http://www.mysqlperformanceblog.com/2013/07/05/schema-changes-whats-new-in-mysql-5-6/.

Executing the following sql throws

mysql> alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation.
Try ALGORITHM=COPY.

Is there a way to add column without affecting app read/write ?


texts:
Using T-SQL or Powershell, how can I create a stored procedure that performs the following:

Copy a SQL Server Agent Job to another backup Job on the same server (with a new name)
Disable the backup job so that it is not scheduled.

USE msdb;

IF object_id('[backupSQLServerJob]') IS NULL EXEC ('create procedure [backupSQLServerJob] as select 1')
GO

ALTER PROCEDURE backupSQLServerJob
    @JobName varchar(50)
AS
BEGIN

    DECLARE @BackupName VARCHAR(100) = CONCAT(@JobName, '_bak_', CONVERT(VARCHAR(10), GETDATE(), 112));

    IF EXISTS (SELECT * FROM dbo.sysjobs WHERE name = @JobName)
    BEGIN
        -- What goes here?
    END
END

The solution needs to work on SQL Server 2008 - 2014


texts:
I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have  multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server version. I'm trying to add target server using the script below but the the EXEC statement does not recognized the @TargetServer variable.

 Declare @SQLVersion varchar(10)
set @SQLVersion = (SELECT
   CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2)
          WHEN '8.' THEN '2000'
          WHEN '9.' THEN '2005'
          WHEN '10' THEN '2008'
          WHEN '11' THEN '2012'
          WHEN '12' THEN '2014'
          WHEN '13' THEN '2016'
          WHEN '14' THEN '2017'
          WHEN '15' THEN '2019'
    END)
Declare @TargetServer varchar(20)
SET @TargetServer = 'ProdEMM' --<--Insert target server name here.
IF @SQLVersion > '2014'
EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = N'@TargetServer'
IF @SQLVersion = '2012'
EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer'
IF @SQLVersion < '2012'
    EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer'

ERROR:

Msg 14262, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 88 [Batch Start Line 0]
The specified @server_name ('@TARGETSERVER') does not exist.

What would be the best way to handle this situation?


texts: Select from a list of values from JSON encoded data

texts:
I have saved the details of the product in JSON format.
I am wondering how to select from a list of values from a "JSON_ENCODED DATA"

For Example
I want to select TV with screen_type as LED or LCD.

And the value is saved as

{"screen_size":"22 - 24", "screen_type":"LED", "smart_tv":"no", "3d":"no", "resolution":"HD Ready", "hdmi":"1", "usb":"1"}

It is possible to do with UNION query like:

SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LED"%'
UNION
SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LCD"%'

But the query will be too lengthy if there are lot of choices.
Is there are any solution in a simpler way.


texts: Secure connection strings to a Azure MSSQL server

texts:
Forgive me, because I'm playing monkey-in-the-middle here.

I write data migration systems. A customer has their data in a private MSSQL DB that is managed by a 3rd party. The 3rd party agreed to send backups (using litespeed) on a schedule so we can get a local copy of the data.

The customer's IT guy has been tasked with providing a DB and access via a connection string for our project. Originally, he spun up a Azure VM running SQL. Normally we would suggest SSPI for security, allowing the AD to handle access and avoiding user/pass in the connection string.

The problem is that you cannot connect an Azure VM to your Active Directory. When he asked how to do so, MS's Azure team suggested another solution using an SaaS SQL server and uploading the DB as a Data Tier app. That does allow it to use their AD, but requires a hopelessly complex 7-step restore process.

I suspect there is a better solution, but I am not familiar enough with MSSQL security models, or Azure, to know the proper lingo. Suggestions? It doesn't have to be AD based.


texts: how to enable CDC in google cloud sql server for AWS DMS replication?

texts: I am trying to migrate data from GCP cloud sql server to AWS aurora MySQL using DMS CDC, for this I need to enable CDC on DMS source database which cloud SQL. As per the AWS documentation I need to enable CDC by executing "sp_cdc_enable_db" SP and for this I need sysadmin access but google cloud don't support sysadmin access. So, in this scenario how to enable CDC?


texts: SSRS report viewer - log on failed (rsLogonFailed)

texts:
I was redirected here from StackOverflow...

I have set up a web based report viewer using the SSRS reportviewer control (sql server 2008 r2) including the ability to specify credentials.

When I comment out the bit that sets the credentials and run the web page the report is displayed.

When I set the credentials to something invalid I get "The request failed with HTTP status 401: Unauthorized."

When I set the credentials to something valid I get "Log on failed. Ensure the user name and password are correct. (rsLogonFailed)"

For the valid credentials I have gone into the SQL Server Reporting Services web page and set up the user security in Site Settings and also set up the secrity in the Folder Settings as discussed in this link HowTo Install SQL Server 2008 R2.  I have verified that the users have db_owner and RSExecRole in SQL server and that goes for both the ReportServer database and the source data database.  Also I have added the users to the SQLServerReportServerUser... security group.

The code that calls the report server control is as follows, the error occurs on the last line

ReportViewer1.ProcessingMode = ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportServerUrl = new System.Uri(@"http://MyComputer/reportserver/");
ReportViewer1.ServerReport.ReportPath = @"/MyReport";
CustomReportCredentials creds = new CustomReportCredentials("ReportViewer", "thepassword", "Mycomputer");
ReportViewer1.ServerReport.ReportServerCredentials = creds;
ReportViewer1.AsyncRendering = false;
ReportParameterInfoCollection parameters = ReportViewer1.ServerReport.GetParameters();

I implemented CustomReportCredentials as

public class CustomReportCredentials : IReportServerCredentials {
    // local variable for network credential.
    private string _UserName;
    private string _PassWord;
    private string _DomainName;
    private Cookie _authCookie;

    public CustomReportCredentials(string UserName, string PassWord, string DomainName) {
        _UserName = UserName;
        _PassWord = PassWord;
        _DomainName = DomainName;
    }

    public CustomReportCredentials(Cookie authCookie) {
        _authCookie = authCookie;
    }

    public WindowsIdentity ImpersonationUser {
        get {
            return null;  // not using ImpersonationUser
        }
    }

    public ICredentials NetworkCredentials {
        get {
            // use NetworkCredentials
            return new NetworkCredential(_UserName, _PassWord, _DomainName);
        }
    }

    public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority) {
        authCookie = null;
        user = password = authority = null;
        return false;
    }

}

What am I missing?
Thanks


texts:
Our nightly maintenance plan includes a database integrity check which normally takes 45 minutes to complete. Now it appears to hang. When looking at the Activity Monitor, a series of process items are listed with the same session id. They are largely all the same, here is a summary

Task State: SUSPENDED
Wait Time: All the same and now more than one day
Wait Type: CXPACKET
Wait Resource: exchangeEvent id=Port3fcfae200 WaitType=e_waitPortOpen nodeId=0

This is the second day, we rebooted the server but it happened again as soon as we ran the check.

For context, we are running SQL Server 2012 BI Edition, 8 cores, 14 GB of memory. Overall CPU is around 18%. This is being run as the SQLServerAgent. The only recent change in the server was the configuration of some push publishing (transactional replication)


texts:
I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to handle time (which is, naturally, a fairly crucial aspect of the database).

Nobody can teach more than one thing at a given time, so having time as a primary key makes sense. Classes work on an hourly basis so integers are acceptable to this end. Times don't change from week to week.

While this seems fine on paper (and there is a quick mock-up of the database in Access below), this nonetheless has some additional baggage.

Monday 9am == 9. Monday 2pm == 14, etc. Cool. But Tuesday 9am == 33 and Tuesday 2pm == 38. A little bit awkward. Also, if one is using the integers for simple calculation (worked between 10 and 14?... 4 hours worked... correct), this can be easily messed up (worked all of monday and tuesday?... 48 hours worked... incorrect). But is integer still the best option for this type of data (given that date is unimportant)?

Data relating to class times will likely have to be manually input into the database. The database may have to be able to provide some sort of automation (automatically assign teachers to modules) and/or allow users to select modules to teach. RDMS likely to be MySQL.


texts: UPDATE STATISTICS options fail in Amazon RDS SQL Server

texts:
In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t;
GO
CREATE TABLE #t (id INT NOT NULL);
GO
UPDATE STATISTICS #t
GO

But try to specify the rowcount on the statistics:

UPDATE STATISTICS #t
  WITH ROWCOUNT = 100000000;
GO

And you get an error:

  Msg 1088, Level 16, State 12, Line 1 Cannot find the object "#t"
  because it does not exist or you do not have permissions.

The STATS_STREAM, ROWCOUNT, and PAGECOUNT options are documented, but not officially supported. Is there a workaround for AWS RDS SQL Server so that they work?


texts:
I'm trying to think about design for some hybrid E-commerce/car Dealer Database.

All is okay, but one problem that I can't manage to solve, or better say - solved, but can be better than this?

I have categories that don't have same properties. Cars have their Car Make and Car Model respective to that Make. But parts and some other categories for selling have  Make but User when Adding post will type Model.
Some categories have Year, Drive... But other have their totally different properties.

All I want to say - Categories have different properties. Post will be searchable by Category.

When user choose Cars - Search form is opened with Make, Model, Year, Drive... But other Categories will give other properties to choose for search.

Post Table - is Single Post(Car, Part, Product...) It will keep common properties for every category.

Post_Value Table - is Property Value

Possible_Values Table - Is all possible Values for that property if it has one. Like "Make" Property that will keep possible values of Audi, BMW, Renault...

Input_type Table - Will keep what is input type used for that Property (Text, Dropdown, Checkbox, Radio...) for Front End, used for dynamic add of properties in Form when user adding Post.

I thought of something like this. But "problem" is that every property will be stored in varchar type. And for 100 Posts with 40 Properties Table will be filled with 4000 rows.

That is bad for searching - I will get list of properties and I need to compare that list with list of properties for every post. And every item from query list must be matched with items from property list from Post_Value table.

I will use Entity framework with predicates for this to match all items with all other list items. I will Index Value and Name of property in Property and Post_Value table.

But speed of this design is really worry me. Any other suggestion or tip to improve this if this is a good way to think about?


texts: Why does FREEPROCCACHE let you shrink tempdb?

texts:
Somebody did something stupid and tempdb exploded in size.  Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked).

There is lots of free space in tempdb (SQL2014 SP1); tempdb > Tasks > Shrink > Files > (shows how much available space I can release)

I shrink (SSMS 2016) to leave more space then the 'minimum shrink to size'.  But the files do not actually shrink.

I am working with the business owner, I am watching the server (sp_whoisactive, etc) the server is at rest.

Searching for a solution leads me to When Shrinking Tempdb Just Won’t Shrink(brentozar.com)

I run the following, and it works tempdb, is back to original size.

 DBCC FREEPROCCACHE

 USE [tempdb]
 GO
 DBCC SHRINKFILE (N'tempdev' , 2048)
 GO

I understand that DBCC FREEPROCCACHE releases all the cache data, and impacts plans.  But if it was taking space in tempdb, it should have showed as used space, and not been available for normal shrinking.

Why did this work?

Based on the comments pointing to What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files. Let me phrase this question from the other way.

Tempdb thinks it has space to shed as evidenced by 'minimum shrink to size', the space that won't go away got created in a temp processes that are no longer running. There is a big chunk of allocated and unused space, why can't I release it without destroying the query plan(s)?

Are the query plans or cache not living on allocated disc space, but still being protected from shrinking?

Analogy: 'minimum shrink to size' shows me the glass is half full/empty but it is not possible to transfer to a smaller glass without breaking the glass and loosing almost all of the contents.

A couple years later, same issue on a SQL 2014 instance, less emergent allows time to try experiment.

If you could somehow identify the query plan, you might be able to delete just that plan releasing the hold on the pages, and then be able to shrink tempdb.  I am looking for ways to accomplish this but have not yet been successful.

Maybe you can use DBCC PAGE to see what is on that page so you can address it?

DBCC TRACEON (3604)  -- By default, the output is sent to the errorlog. If you want the output to come back to your current connection, turn on trace flag 3604.
GO
DBCC PAGE ([tempdb], 1, 1, 3); -- detailed dump of the PFS page
GO

I see the last page is 937, so I go look at it.

DBCC PAGE ([tempdb], 1, 937, 3);
GO

Nothing clearly an issue there, The last few pages look empty.

I have tried the suggestion by Mike Fields (February 4, 2016 12:01 pm) Multiple attempts did not even free 1MB.

  I ran into this problem yesterday, using the SSMS task to shrink files never accomplished anything, then I re-read the documentation on Shrink File and realized I needed to try this:

DBCC SHRINKFILE (N'tempdev' , NOTRUNCATE) -- Move allocated pages from end of file to top of file
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY) -- Drop unallocated pages from end of file

  I did this for all 8 of the tempdev files and slowly, over time, it finally recovered about 80% of the space. I was truly surprised by this as nothing else had worked. I had to run a job every 15 minutes through the day but it eventually recovered the space

On a SQL 2014 instance I have 'naturally' cleared the cache when a large database was rebuilt with a clustered column store indexes (nightly process). The same SHRINKFILE that would not do anything the previous day works as expected in the morning.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 2000)
GO

Maybe if I could recreate the problem on demand on a 2016+ instance with Query Store, it would be easier to track down exactly what is happening?


texts:
I'm struggling with a problem of folk memory re Postgres.

The folklore

I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered from an issue where if the machine holding the server failed (e.g. kernel crash), when the machine was restarted, Postgres would start but the indexes on the database might be corrupted. For instance

SELECT foo FROM bar WHERE baz = 123

would fail because the index on bar.baz would be corrupted. Reindexing the table fixed the issue but as there was no way to detect this issue, and this was in an appliance environment (no DBA on hand), in essence to guard against this it would be necessary to reindex all tables on a reboot, which would be prohibitively expensive. Thus (the reasoning goes) without a DBA on hand it would not be sensible to use Postgres particularly in an environment where tables were large and computing indices expensive.

Because of this folklore, MySQL is preferred in such circumstances.

My theory

I dimly recall seeing this issue happening under 8.4, so I think there's some truth in it.
I am assured this also has happened on Postgres 9.3 under Ubuntu 14.0.4. I don't know whether it happens 'frequently'. The system it happened on is running with (admittedly old) enterprise grade SAS drives which claim write caching is turned off, so I don't think this is a write caching problem.
Were this still the case, I think I'd be able to find more evidence of it on the web than I can find.
Thus I suspect it may be fixed, or may be a result of something bad in postgresql.conf (though inspection suggests only the autovacuum settings are changed from the defaults).

Further information

In the system I know did this recently (using 9.3) no LVM is in use. The storage system is ext4 on a physical drive mounted with default attributes. This drive is an (admittedly old) enterprise SAS drive plus SAS1068 PCI-X Fusion-MPT SAS controller which sdparm claims it doesn't have a write cache switched on, but this has (allegedly) been seen on multiple drives with different vendors etc., though always on ext4 on Ubuntu.
fsync is in the default state (on I believe).
No master/slave configuration  - just a single master - (so it's not the multixact bug).
Tables are created with default indexing (no fancy hash indexes).
Postgres regularly updated to whatever the relevant Ubuntu LTS release has. The latest problem was on 14.04.

Questions

Is manual rebuilding of indices an expected task after a system crash?
If not, has it ever been, and when was that changed?
Is it possible to avoid the need for manual reindexing?


texts: Mssql database old bakup restored on production server

texts: I am using mssql server 2016, I have accidentally restored the 24 hours old database backup file on production server, so I have lost the data for today, means I have lost the data of 24 hours, is there any chance I can get back my original database so that I can bring back the production database to original state, means before restoring the old database on production server.


texts:
I have a query I am trying to return all users from with their join_date based on a LEFT JOIN. The query is like so:

SELECT users.name,
MIN(history.time_out) as join_date
FROM flexlm_user users
LEFT JOIN flexlm_history history ON users.id=history.user_id
GROUP BY users.name
ORDER BY 2

However, the results are returning Null for join_date where history.time_in is Null. The history table has no Nulls for the time_out field. Any help is much appreciated.

current results are like this

name    join_date
user1   NULL
user2   NULL
user3   NULL
user4   48:00.0
user5   31:00.0
user6   59:00.0
user7   15:00.0

I want results like this:

name    join_date
user1   13:00.0
user2   5:00.0
user3   7:00.0
user4   4:00.0
user5   31:00.0
user6   59:00.0
user7   15:00.0

History Table:

CREATE TABLE [dbo].[flexlm_history](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [server_id] [int] NOT NULL,
    [user_id] [int] NOT NULL,
    [product_id] [int] NOT NULL,
    [update_id] [int] NOT NULL,
    [time_out] [datetime] NOT NULL,
    [time_in] [datetime] NULL
)

Sample Data:

+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+
| id  | server_id | user_id | product_id | update_id |        time_out         |         time_in         |
+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+
| 615 |         2 |       9 |          8 |      4670 | 2017-07-28 14:10:00.000 | 2017-07-28 14:43:00.000 |
| 616 |         2 |       9 |         10 |      4670 | 2017-07-28 14:10:00.000 | 2017-07-28 14:43:00.000 |
| 617 |         2 |      24 |          9 |      4672 | 2017-07-28 14:13:00.000 | NULL                    |
| 618 |         2 |       2 |          9 |      4672 | 2017-07-28 14:14:00.000 | NULL                    |
| 619 |         2 |      76 |          4 |      4676 | 2017-07-28 14:42:00.000 | 2017-07-28 16:53:00.000 |
| 620 |         3 |      18 |         20 |      4685 | 2017-07-28 15:22:00.000 | 2017-07-28 15:53:00.000 |
| 621 |         2 |      21 |          4 |      4686 | 2017-07-28 15:29:00.000 | 2017-07-28 16:33:00.000 |
| 622 |         2 |      24 |          4 |      4690 | 2017-07-28 15:48:00.000 | 2017-07-28 16:33:00.000 |
| 623 |         2 |       9 |          2 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 624 |         2 |       9 |          8 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 625 |         2 |       9 |         10 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 626 |         2 |      43 |          2 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 627 |         2 |      43 |          5 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 628 |         2 |      43 |          6 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 629 |         2 |      43 |          8 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 630 |         2 |      43 |          9 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 631 |         2 |      43 |         10 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 632 |         2 |      43 |         11 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+

User Table:

CREATE TABLE [dbo].[flexlm_user](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](25) NOT NULL,
)

Sample Data:

+----+-------+
| id | name  |
+----+-------+
|  9 | user1 |
| 24 | user2 |
| 76 | user3 |
|  2 | user4 |
| 18 | user5 |
| 21 | user6 |
| 24 | user7 |
|  9 | user8 |
| 43 | user9 |
+----+-------+


texts: Why is Postgres on RDS maxing out on CPU every few hours?

texts:
Using Amazon RDS, we are running ETL scripts to migrate our data. However, every few hours there is a huge CPU spike.

Here are the ETL specs (per ETL):

50 records inserted / second
pool of 1000 connections

Here are the server's specs:

Amazon R3.8XL
244 GB RAM
32 vCPU
3TB SSD
10 GiB Network Performance
No Multi-AZ (yet)

Here are the main & modified PG parameter group settings:

checkpoint_completion_target = 0.9
checkpoint_segments          = 16
effective_cache_size         = {DBInstanceClassMemory/10923} (8kb)
maintenance_work_mem         = {DBInstanceClassMemory/16384} (kb)
max_connections              = {DBInstanceClassMemory/12582880}
max_locks_per_transaction    = 64
shared_buffers               = {DBInstanceClassMemory/32768} (8kb)
work_mem                     = {DBInstanceClassMemory/20480000} (kb)

In this case, DBInstanceClassMemory is approximately 244,000,000,000 bytes. The (8kb) means the value is taken as blocks of 8kb, so shared_buffers = 244000000000/32768*8000 = 60 gb. All of the changes made were based on this article, and I set the effective_cache_size to 75% because (as you will see below) the memory doesn't seem to be getting fully utilized.

Here is a screenshot of our database server's stats over a 6 hour time span:

The graph on the top left shows the CPU spikes, and you can see the correlated drop in Write IOPS (the graph below it).

What can be the reason for these CPU spikes? They almost completely freeze queries by the ETL (taking upwards of 3 minutes for queries that would usually take less than a second).


texts:
SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it.

Why is AT TIME ZONE nondeterministic?

Example Showing Non-Determinism

Executing:

CREATE TABLE Test (
    LegacyTimestamp DATETIME,
    Timestamp AS LegacyTimestamp AT TIME ZONE 'Eastern Standard Time' PERSISTED
);

Returns the following error:

Msg 4936, Level 16, State 1, Line 1
Computed column 'Timestamp' in table 'Test' cannot be persisted because the column is non-deterministic.


texts: Restore a 140GB - 292 Million entrie MySQL-Database faster?

texts:
i need help. I have an old innodb Server and my storage limit reached.
I dont use files per table entries because in old times there were no ability at this time. My ibdata grown up fast and there is no ability to optimize them expect files per table. It is a productive database consists about 292 Million mails.

I dump my sql on a separate disk on production server

mysqldump --databases --single-transaction --quick -uroot -p databasename | gzip > backup.gz

and i restore with that

gunzip < backup.gz | mysql -u root -p databasename

I need more than 7h to restore the gzip dump on a 8(16th) Core i9 9900, 64GB DDR4 and NVME drive...it takes to long

Is it better to use an other backup method oder better dump directives or dont use gzip, dont know.

I made some optimizations on the restore test server:

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 8000
innodb_io_capacity = 80000
innodb_thread_concurrency= 0
innodb_read_io_threads = 64
innodb_read_io_threads = 64
innodb_buffer_pool_size=45G
innodb_file_per_table=1
innodb_log_buffer_size=128M
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M


texts: An example of a relation without any functional dependency?

texts:
I have to find a really bad example where you cannot apply Heaths Theorem because of a missing functional dependency.

But am I right, that every relation has at least one functional dependency?

Or can anyone of you come up with an example table with at least 3 Sets of Attributes to project it and then join it together. The result should of course be different from the original table to proof Heath's Theorem right (no FD -> no lossless decompositions)

It does not matter, wheter it is an sql example or an abstract one.


texts: Understanding DB Throughput Calculations

texts:
I'm trying to understand more about databases and am taking a course that calculates throughput throughout. An example for a DynamoDb table looks like...

  our application requires us to read 10 items of 6KB / second, what should we set the throughput for?

Coming at this from a web dev's perspective I want to understand what is happening here.

Is this saying that if someone requests 10 items from our DB we want them to be able to get 6KB worth of data per second for each item?

So if those 10 items total size is 100 KB and we have our DB configured to read 6 KB of data per second, it will take about 17 seconds to perform that read operation (100/6)?

Am I understanding the requirements correctly or do I have something fundamentally wrong? I want to make sure I have this concept down before moving on.


texts: How to modify multiple databases structure simultaneously?

texts:
I have multiple databases on the same server which have the same structure as each other. whenever I want to modify a table or a procedure or add, edit or delete something on one of them I should run the modifications manually on all the others too!

Is there a way to modify the structure of all of the databases simultaneously and preferably automatic?

I use MySQL workbench tool to connect to my databases.


texts:
I can match one set of conditions like so:

select ID from FOO where col1=1 and col2=2 and col3=3

But, what if I have an array, [(1,2,3), (4,5,6), (7,8,9)], how do I get the ID's in this case?

To clarify, I would like a way to match each of the tuples to the three columns.  If it was broken out it would be:

select ID from FOO where col1=1 and col2=2 and col3=3

select ID from FOO where col1=4 and col2=5 and col3=6

select ID from FOO where col1=7 and col2=8 and col3=9

If I have to loop, so be it, I thought there might be a more elegant way to do this.

My version of sqlite is 3.8.11, which ships with Python 3.5.1.


texts: SQL Deadlocks - Same Stored Procedure Called Concurrently

texts:
I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to display results to the user quickly.

The process:

User or application opens a specific order
A stored procedure is called to retreive the data (lets call it dbo.Get_Data)
dbo.Get_Data checks event logs to see if there have been any changes since the last time it looked for the data
If it finds a new event, it runs the expensive query to update the data
Data is returned

IF (    SELECT [LastStaticUpdateEvent] < [LastSaveEvent]
        FROM [dbo].[Events]
        WHERE [OrderNumber] = @OrderNumber
    )
    BEGIN
       -- Update Static table
       UPDATE [Static]
       SET [Static].[A] = [App].[A]
          ,[Static].[B] = [App].[B]
       FROM [dbo].[AppData]            AS [App]   -- View with many joins (4-5 secs)
       INNER JOIN [dbo].[StaticResuts] AS [Static]
           ON [Static].[OrderNumber] = [App].[OrderNumber]
       WHERE [App].[OrderNumber] = @OrderNumber

       -- Update Event Log
       UPDATE [dbo].[Events]
       SET [LastStaticUpdateVent] = SYSDATETIME()
       WHERE [OrderNumber] = @OrderNumber

    END

SELECT * FROM [dbo].[StaticResults]

The problem is that this data can be requested concurrently.  If User A and User B both call dbo.Get_Data before the first UPDATE is complete, a deadlock occurs.

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?


texts: SQL Server 2012 cannot use file server for log shipping

texts:
I have a problem with log shipping in SQL Server 2012.
I have two servers for log shipping, let's say server A and server B.

For the first experiment, when I use a shared folder from my computer based on Windows 10, it works great.

But when I try to use a shared folder from my file server such as synologi or infortrend, it failed, even after I added permissions for SQL Server Agent, my administrator on each server, my user on each server, even if I added full control permission for everyone.

Do you have any advice for my problem? Or must SQL Server 2012 log shipping use a file server which is based on a Windows OS?

When I look in the job history, I see:

  Error, Can't open backup device

I assume that this backup job from log shipping can't be done because the device (file server) cannot be accessed by SQL Server.

I am very sure that my servers, and my file server, use the same domain and the same segment, that's why I doubt that log shipping from SQL Server 2012 really works with a file server.

If I manual backup using the GUI, it won't show us the directory mapped in My Computer. But if I use a backup script, it would fail because there is no file backup in my file server.

Trying a BACKUP DATABASE command to the share, I get the following error message:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\[fileserver IP]\coba\TESTLAGICOBA.bak'.
Operating system error 86(The specified network password is not correct.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


texts:
I am try to make a cursor with loop condition.
purpose of cursor:- I want to execute a procedure 17times .

Procedure definition:

dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF

DECLARE @IN_DIFF int
DECLARE vendor_cursor CURSOR FOR
SELECT date_time FROM dumy_mis
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @IN_DIFF

WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor


texts: Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?

texts:
We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it.
When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 secs.
On observing execution plan for both the queries, it is totally different than one another. Without OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY it returns 154 rows.
I am puzzled on how using OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY can degrade performance and cause different execution plan? Since it limits the records that should be returned, I thought it will improve the performance.
Does anyone has any inputs on how to proceed further?


texts: Update table from a join which returns multiple rows for each result

texts:
I am trying to merge information contained in 2 different archives but I will try to simplify my problem to its root cause.
I have 2 tables A and B and I want to update a column in A with values from a column of B if the value in B is not null, otherwise I have to preserve the value in A.
A and B are not direclty related but I have the information I need to identify which elements of A are also present in B.
To avoid perform all operations in a single and really complex query I've added a Foreign Key in table B where I store, if present, the key value of the corresponding element in A.
At this point my idea was to perform an update joining the tables:
UPDATE A
JOIN B ON B.A_FK = A.PK
SET A.INFO = IFNULL(B.INFO, A.INFO);

Unfortunately for me this doesn't work in all cases because in table B there can be more rows that refers to a single row of table A. For this scope lets assume that only one of them has a non null value.
Table A
--------------
| PK | value |
--------------
| 1  |   X   |
--------------

Table B
---------------------
| PK | value | A_FK |
---------------------
|  n |   Y   |  1   |
|  m |  null |  1   |
---------------------

In this case the value in table A is not modified and is still X.
I hoped that performing the update process it would have run the update 2 times for my row in A and that my result would have been the following but that's not the case.
Table A
--------------
| PK | value |
--------------
| 1  |   Y   |
--------------

I really would like to avoid resorting to a cursor to iterate over all rows, so can I modify my query to obtain this result?


texts: How recover Tablespace from table in MySQL

texts:
my database fail and I dont have a backup so I have the .ibd file each table in database but when I try recover in other database coping the files there the database show me the structure and all tables but trying access to the data (Select * from users) say ERROR 1812 (HY000): Tablespace is missing for table arcaschool.users. and I cant recover the info.
Im using MySQL 8 in Ubuntu 20.04.
Thanks for your support.
Error Screen Shot


texts: MySQL - alter huge table without slowing down server

texts:
I'm trying to alter a pretty large table (25GB including index) by adding a column & modifying an index, but in addition to taking a very long time (> 1 hour), which is ok, it affects the whole system, making queries on other tables more than 100 times slower, which is a huge issue and forced me to cancel the operation.

The server is pretty solid (196GB RAM with only 70GB used, SSD RAID disks with 300GB free space). Storage engine is MyISAM.

Is there any way to give the ALTER TABLE very low priority so that it doesn't dramatically affect the rest of the operations (I don't care if it takes twice the time)? Is it even normal that it does?


texts: How to find where an unauthenticated user connects from

texts:
We are seeing some "unauthenticated users" when I run the command show full processlist in mysql (v5.6).

| Id       | User                 | Host                  | db           | Command     | Time     | State                | Info                                                                                | Rows_sent| Rows_examined |
| 29285830 | unauthenticated user | connecting host       | NULL         | Connect     |    NULL  | login                | NULL                                                                                |        0 |             0 |

--skip-name-resolve is ON.

We have a monitoring agent, and a bunch of webservers and backend machines querying the database.

I am trying to write a python script which does ssh to the remote database, runs netstat and then runs show full processlist in MySQL and compares the connected IP addresses. However, the unauthenticated user's connection is very shortlived (less than a couple of seconds) and I don't get the IP address correctly. Is this the right approach or is there a better way to find the IP address of the unauthenticated user? There are about 1000+ connections.


texts: compare time difference between current time with last data timestamp


texts: How is the number of records on a select determined as high or low by optimizer?

texts:
What is the threshold between using the index and doing an direct access to the database?
I assume this decision is related to the number of index access (and subsequent access to the tables). If this number is really low the index should be used. But if the number is high I guess a direct access would be better in terms of performance. But how is high defined? Is there e.g. some variable that specifies this? E.g. in terms of percentage of the table size?


texts: How widespread is the adoption and usage of database snapshots?

texts:
After reading up on database snapshots, it seems that they are useful as a backup and quick restore method for small  DDL and DML changes. Additionally they are useful for large scale dev/test environments.

My intended use will be in our test environment where we need to test DDL/DML scripts and want to revert to the previous state without requiring lengthy restore times. Are there other methods besides snapshots here (excluding VM solutions)? Has this been widely adopted and are there any issues to be aware of? I'm trying to get a sense of the community outside of MS documentation and the few blogs I have read. If your impression that the documentation available online is representative, that's a fine answer for me.

Thanks again, gurus!


texts: Recreate a table that was deleted during an ALTER statement (errno: -1)?

texts:
For the third time in the past 2-3 weeks I've gotten the following message during an ALTER statement:

  Error 2013: Lost connection to MySQL server during query SQL Statement:
  ALTER TABLE `my_database`.`my_table` ...

In this case I was changing AUTO_INCREMENT to 55, that's it. This was necessary because we store JSON strings in LONGTEXT fields with references to database IDs, but during the migration script it did not insert all of the rows correctly so the IDs were mismatched from what already exists out there. AUTO_INCREMENT had to be fixed for solely a functional purpose.

The MySQL Workbench version I'm using is 6.3.3.0 build 592.

When trying to refresh my schemata, I get:

  Error Code: 2013 Lost connection to MySQL server during query

When I try to recreate the table, I get this message:

  Error Code: 1005. Can't create table 'my_database.my_table' (errno: -1)

It seems that the ALTER deleted the old table definition before creating the new table definition.

How can I fix this?

The database is running on AWS RDS, so I can't change anything except what Amazon allows me to. I cannot recreate any of the previous three tables by name in my schema anymore, but only for InnoDB. If I were to try creating this table in MyISAM, it would work.

I'm using Propel for the migration script, so I did explicitly specify the id column by suppressing the autoIncrement field of the XML table tags. I didn't want to dump my tables and rerun the migration script, so I fixed the IDs manually and then attempted to change the AUTO_INCREMENT to what it should be, when this all happened.


texts:
Is there a way to list all my databases with tables in a single command?

Expectation:

mysql> SHOW DATABASES, TABLES;

    DB1 table1
        table2
    DB2 table1
        table2
        table3
        table4
        table5
    DB3 table1
        table2


texts: Migrate foreign key type from char to binary - ways to deal with the fallout?

texts:
I am in the process of slimming down a database that has been using char/varchar fields where binary/varbinary would be a better choice (given the data represented is byte arrays).

One of the fields I am changing is used in foreign key contraints on a number of other tables.

I cannot drop the current FK constraints, migrate the column to its new type and then simply re-create the FK constraints as the data types would not match.

What approach should I take in migrating this? How would you go about it?


texts:
I attached 4 10GB disks to MySQL Server and I used RAID 0:

but still MySQL performance is the same. Do I have to change configuration in my.cnf to let MySQL know about RAID 0?

This are my mount results:

This is not development box, it is my production server. I used to have MySQL server on AWS but now I am using Azure, and I/O really hurts MySQL.

I am not a server admin expert, but I am trying to solve here my problem, a consultant recommended to me to do RAID 0.

I am using newrelic to test my performance. It shows how bad is MySQL, my server is 8 cores and 14 GB Azure server.


texts: Is the [NT Service\SQLSERVERAGENT] a supported type for SQL Server Agent service?

texts:
Reading and re-reading  MSDN "Select an Account for the SQL Server Agent Service".

NT Service\SQLSERVERAGENT is not listed in the "Supported Service Account Types" section.
And, if not, what is it installed with SQL Server for?
What could this possibly mean, I wonder?
How should I better undesrstand it?


texts: Missing archivelogs when restoring duplicate

texts:
We restore backups to our test systems from production via a point in time RMAN Duplicate with an online full backup and hourly archivelog.  Every blue moon something goes wrong (working to resolve that separately) and I end up not having an archive log.  It spends hours restoring the datafiles, gets to the archivelogs and then bombs.

How can I verify that RMAN has all the correct archivelogs and datafiles to restore before it starts pushing the data around significantly?


texts: Query External LDAP Server from SQL Server

texts:
I have done extensive research on querying LDAP Server but so far has not been able to work around. Therefore, asking experts help

Background: I have a client whose LDAP Server is OnPremise (though facing publicly) and I have a SQL Server(2012 SE) inside the firewall. The communication between the External LDAP and Internal SQL Server are fine as I can ping each other. I also created a remote domain account and used this account when created Linked Server on SQL Server. I also have our internal Domain controller that has Domain Trust Relationship. Now, I have created 2 Linked Servers using Domain account but I have not been able to query LDAP Server on both internally and externally. I hope this is making sense. Anyways, Here are the definitions of my Linked Server and errors I am receiving

[Solved] Linked Server to an Internal LDAP Server: This was resolved by using ADuser@domain.com instead of domain/ADuser

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active
Directory Service Interfaces', @provider=N'ADSDSOObject',
@datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain/ADUser',@rmtpassword='########'

Error:

  Msg 7321, Level 16, State 2, Line 1
  An error occurred while preparing the query "SELECT *
  FROM  'LDAP://Domain.com/OU=Accounts,DC=Domain,DC=com'
  --WHERE objectClass =  'User'
  " for execution against OLE DB provider "ADSDSOObject" for linked server
  "ADSI".

Linked Server to an External LDAP Server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSIE', @srvproduct=N'Active
Directory Service Interfaces', @provider=N'ADSDSOObject',
@datasrc=N'CustomerIPAdress'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain/ADUser',@rmtpassword='########'

Error:

  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "ADsDSOObject" for linked server "ADSIE" reported an >error. The provider indicates that the user did not have the permission to >perform the operation.
  Msg 7321, Level 16, State 2, Line 1
  An error occurred while preparing the query "SELECT *
    FROM 'LDAP://Customer IP Adress'
    WHERE objectClass = 'User'
    " for execution against OLE DB provider "ADsDSOObject" for linked server >"ADSIE".

Any help would be appreciated.Thanks in advance.


texts: Do id columns help (in speed) select statements?

texts:
I have a table which contains data from aggregation software.
The columns mostly are int columns, but three of them are string columns.
It looks sort of like:

userId, someData1, someData2,     someData3, otherIdColumn, childId, websiteId
------------------------------------------------------------------------------

1, somestring,  someotherstring  justastring    3,             2,       1
1, somestring1, someotherstring1 justastring    3,             3,       3
1, somestring2, someotherstring2 justastring    2,             2,       1
1, somestring3, someotherstring3 justastring    1,             9,       5
2, somestring4, someotherstring4 justastring    4,             2,      10

and so on.

The table contains something like 80m rows for now.
When I'm trying to make a query using aggregation functions (SUM) and grouping by one of the string columns (someData1, someData2, someData3) it takes very VERY long time (more than 10 minutes for a query).

I'm trying to optimize the table right now, the first thing that I've did I've added indexes to the string columns, but I want to make it even faster.
I thought of adding an ID column (pk, ai, nn) as of it will be make the select queries faster.

What do you think about it? do you have any advice how more I can optimize this table? Note: I have only like 5 columns that I'm using group by. 3 of them is string columns, and they are making the problem.


texts: how to set default value of column 0 when NULL is inseerted

texts:
I have a table with a column which has default value to 0. However when null is inserted to this table, I want it to set it to default.

As the insert query is generic and used by other databases too, I cannot make any changes to the insert statement.

Can I have constraints or case statement on create table, so that default 0 value is inserted whenever null is passed.


texts: Unexpected scans during delete operation using WHERE IN

texts:
I've got a query like the following:

DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
    SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)

tblFEStatsBrowsers has got 553 rows.
tblFEStatsPaperHits has got 47.974.301 rows.

tblFEStatsBrowsers:

CREATE TABLE [dbo].[tblFEStatsBrowsers](
    [BrowserID] [smallint] IDENTITY(1,1) NOT NULL,
    [Browser] [varchar](50) NOT NULL,
    [Name] [varchar](40) NOT NULL,
    [Version] [varchar](10) NOT NULL,
    CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)

tblFEStatsPaperHits:

CREATE TABLE [dbo].[tblFEStatsPaperHits](
    [PaperID] [int] NOT NULL,
    [Created] [smalldatetime] NOT NULL,
    [IP] [binary](4) NULL,
    [PlatformID] [tinyint] NULL,
    [BrowserID] [smallint] NULL,
    [ReferrerID] [int] NULL,
    [UserLanguage] [char](2) NULL
)

There's a clustered index on tblFEStatsPaperHits that does not include BrowserID. Performing the inner query will thus require a full table scan of tblFEStatsPaperHits - which is totally OK.

Currently, a full scan is executed for each row in tblFEStatsBrowsers, meaning I've got 553 full table scans of tblFEStatsPaperHits.

Rewriting to just a WHERE EXISTS doesn't change the plan:

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)

However, as suggested by Adam Machanic, adding a HASH JOIN option does result in the optimal execution plan (just a single scan of tblFEStatsPaperHits):

DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
    SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)

Now this isn't as much a question of how to fix this - I can either use the OPTION (HASH JOIN) or create a temp table manually. I'm more wondering why the query optimizer would ever use the plan it currently does.

Since the QO doesn't have any stats on the BrowserID column, I'm guessing it's assuming the worst - 50 million distinct values, thus requiring quite a large in-memory/tempdb worktable. As such, the safest way is to perform scans for each row in tblFEStatsBrowsers. There is no foreign key relationship between the BrowserID columns in the two tables, so the QO can't deduct any info from tblFEStatsBrowsers.

Is this, as simple as it sounds, the reason?

Update 1
To give a couple of stats:
OPTION (HASH JOIN):
208.711 logical reads (12 scans)

OPTION (LOOP JOIN, HASH GROUP):
11.008.698 logical reads (~scan per BrowserID (339))

No options:
11.008.775 logical reads (~scan per BrowserID (339))

Update 2
Excellent answers, all of you - thanks! Tough to pick just one. Though Martin was first and Remus provides an excellent solution, I have to give it to the Kiwi for going mental on the details :)


texts: Recover a dropped MySQL database with its procedures

texts:
I had some databases on a server and MySQL server on this server has been uninstalled accidentally by someone.

I had some backups on this server and could recover all tables and their data successfully, but the problem is that the events and procedures are lost.

How can I recover the lost events and procedures? I searched a lot to know if it is possible or not.


texts:
Got the following nagios alert this morning for our MySQL replica:

  14:49 PROBLEM: mysql2/MySQL replication check is CRITICAL, 10.1.0.133 is 72870 seconds behind, Aug 09, 21:49 UTC

When I look at SHOW SLAVE STATUS on the replica, the places I normally look at are fine:

Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 73698
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_SQL_Errno: 0

Where else should I be looking to diagnose and fix replication?


texts: use SQLcl with EZconnect and multiple hostnames (ADDRESS_LIST=)

texts:
this is our classic tnsnames.ora

test1=
(DESCRIPTION=
    (CONNECT_TIMEOUT=4)
    (TRANSPORT_CONNECT_TIMEOUT=3)
    (ENABLE=BROKEN)
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521))
    )
    (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS)
    )
)

I use this SQLcl command:

sql -nohistory -noupdates -S $username/$password@$hostname:$port/$servicename @$filename

How to specifiy multiple hostnames? Not only one? It's some kind of active passive cluster (Exadata).

Edit after first answer:

I added in shell script (in this dir tnsnames.ora):

TNS_ADMIN=/example/example

I call $sqlcl -nohistory -noupdates -S $username/$password@"MY-DB" @$filename and get back the error:

./script.sh
  USER          = MY_USER
  URL           = jdbc:oracle:thin:@MY-DB
  Error Message = IO Error: Unknown host specified
  USER          = MY_USER
  URL           = jdbc:oracle:thin:@MY-DB:1521/MY-DB
  Error Message = IO Error: Invalid connection string format, a valid format is: "host:port:sid"


texts: Read replica setting giving error "No candidate servers found"

texts:
I am running my website with Laravel 5 + MongoDB.

I am using mongo cluster to speed up data read and write, and to lower CPU usage of the mongo server.

I have set up the replica like this: 10.0.1.111 is primary replica set, and 10.0.1.112 & 10.0.1.113 is the secondary replica set.

Config::set('database.connections.'.$schemaName, array(
    'host' => array('10.0.1.111','10.0.1.112','10.0.1.113'),
    'driver'    => 'mongodb',
    'port'     => 27017,
    'database'  => $schemaName,
    'prefix'    => '',
    'options'  => array(
        'replicaSet' => 'replset-name',
        'readPreference' => \MongoClient::RP_SECONDARY_PREFERRED
    )
));

When I use this, it shows a "No candidate servers found" error. When I use a single host 10.0.1.111 it works well. It will be highly appreciated if you can give a solution to this problem.


texts:
We have a single master setup with many slaves in germany and some slaves in australia.

To have faster writes in australia we want a master-master setup between germany and australia. Read slaves will remain under the local master in both regions.

We tested Percona XtraDB Cluster, but the commits are synchronous, and writes are way too slow.

Is there an option in XtraDB to speed things up? Maybe tranfer multiple transactions in one block between the two masters? Or allow commits to be accepted before the second nodes applied it?

Is there another master-master solution, that works better (maybe asynchronously) under the given high latency of ~300ms?


texts: Does innodb_online_alter_log_max_size took memory from ram or Hard Disk

texts:
I got an Mysql (5.7) error while altering huge table (400 G) for adding an index:

ERROR 1799 (HY000): Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

I guess that I had to increase innodb_online_alter_log_max_size but I'm afraid that it while took it from RAM cause it is already full enough.

N.B: I had enough space on tmpdir path.


texts: Using Partial String match from one query results in where clause for another SQL Server 2012

texts:
I need results field of query1 to drive

I have one query that produces a table of several columns, namely "Time_stamp", "Value", and "Name" from two existing tables.

Select TOP 20
    MshiftV1.Field_ID
    ,MshiftV1.Timestamp
    ,LEFT(MshiftF1.Name,12) as Name
    ,MshiftV1.Value
    ,MShiftF1.ID
From
    [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Values_5Min_RealTime_V6] as MshiftV1
Inner Join
    [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Fields_5Min_RealTime_V6] as MshiftF1 on MshiftV1.Field_ID = MshiftF1.ID
Where
    MshiftV1.Quality = 1
    and MshiftV1.Value not like '%INF%'
    and MshiftV1.Value not like '%IND%'
    and MshiftF1.Name not like '%_Splice_%'
order by
    MshiftV1.Timestamp, ABS(convert (float, MshiftV1.Value)) desc

I have another, separate query for two different tables from the first, and it produces Fields like "Time_Stamp", "Correlation_Pair", and "Percent_Change".

select
    BSCv1.Timestamp "Field Timestamp"
    ,BSCV1.Value as Correlation_Pair
    ,BSCV2.Value "Percent Change"
    ,BSCF1.Name "Field Name"
    ,BSCF1.ID "DEV Name ID"
    ,BSCV1.Field_ID "Field ID"
    ,BSCF2.Name "Value Name"
    ,BSCV2.Field_ID "Value ID"
    ,BSCF2.ID "V ID"
    ,BSCv2.Timestamp "Value Timestamp"
From
    [BabyCare].[dbo].[BSC_v3_values] as BSCV1
inner join
    [BabyCare].[dbo].[BSC_v3_fields] as BSCF1 on BSCV1.Field_ID = BSCF1.ID
inner join
    [BabyCare].[dbo].[BSC_v3_values] as BSCV2 on BSCv1.Timestamp = BSCv2.timestamp
inner join
    [BabyCare].[dbo].[BSC_v3_fields] as BSCF2 on BSCV2.Field_ID = BSCF2.ID
                                              and BSCV1.Quality = 1
                                              and BSCF1.Name like '%Dev_N%'
                                              and BSCF2.Name like  '%Dev_V%'
                                              AND RIGHT (SUBSTRING(BSCF1.Name,5,5),3) = RIGHT (SUBSTRING(BSCF2.Name,5,5),3)
                                              and BSCv1.Timestamp = BSCv2.Timestamp
where
    BSCV1.Quality = 1
    and BSCF1.Name like '%Dev_N%' and BSCF2.Name like  '%Dev_V%'
    AND RIGHT (SUBSTRING(BSCF1.Name,5,5),3) = RIGHT (SUBSTRING(BSCF2.Name,5,5),3)
    and BSCv1.Timestamp = BSCv2.Timestamp
order by
    ABS(convert(float,BSCV2.Value)) desc, BSCv1.Timestamp Desc

Both queries work great, but I have a recent need to combine results so that output from first query drives the second query. Specifically, I need the second query to only return records where the first 12 characters in the "BSCV1.Value" field are equal to the first 12 characters of the output of the first queries field "MshiftF1.Name", AND where "Timestamps" are equal.

Any thoughts?


texts: SQL Server 2016 capped at specific CPU usage

texts:
I have a situation where my SQL server is capped at a constant CPU usage rate (13%)
and only four cores are being utilized.

There is no resource governors or anything like that in place.

This runs on VMWare environment.
SQL Server 2016
Windows 2016


texts: "Role does not exist" error when granting usage on public schema, but role does exit

texts:
In Postgres, I'm trying to grant USAGE on the public schema to a specific role -

GRANT ALL ON SCHEMA public TO MyRole;

The problem is that this command generates an error -

  ERROR:  role "myrole" does not exist

However, the role does exist.  Listing roles outputs the following table -

   Role name   |                         Attributes                         |    Member of
---------------+------------------------------------------------------------+-----------------
 rds_user      | Create role, Create DB                                    +| {rds_superuser}
               | Password valid until infinity                              |
 MyRole        |                                                            | {}
 rds_superuser | Cannot login                                               | {}
 rdsadmin      | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
               | Password valid until infinity                              |
 rdsrepladmin  | No inheritance, Cannot login, Replication                  | {}

I'm new to Postgres, so I may be missing some subtle command, but as far as I can tell I've done everything I need to.  Can anyone suggest what the problem may be and how I should go about resolving it?


texts: At What Point Should I Split or Partition a Very Large but Simple Table

texts:
Our site has some large but simple (INT, INT, DATE) tables for stats. Each table has up to 300,000,000 rows, and gets bigger every day.

The hosting provider has suggested that we split or partition the tables, and I have seen this recommendation elsewhere on numerous occasions.

However...

I am struggling to reconcile this advice with the stated max capacity for SQL Server - a database size of 524,272 terabytes, with table rows limited only by "available storage".

Based upon those figures, the table described above could easily have centillions of rows (10 to the power of 303).

Ah ha you might say, there is a difference between CAPABILITY and PERFORMANCE.

But in virtually every question about SQL Server performance the answer is "It depends.... on table design and query design".

That is why I am asking this question. The table design couldn't be much simpler. Nor could the queries which are simple count(*) operations based on an indexed ID field.


texts: NOLOCK hint changes order of records returned

texts:
There is a clustered index on a table Client field LastName.

When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in question. That hint changes the order of records. Should it?.  I am positive that no other session has an open transaction with the changes to that table (at least sp_who2 is not showing me any).

How can the difference in the order be explained?

Additional information pulled from comments:

There is no order by. Should non clustered index enforce the order?
The queries still return different order even when using an index hint specifying a clustered index. Should they? I am wondering why nolock changes the order of returned records without a visible change of the plans.
I did a WinDiff on them - same except for [the] (nolock) [query hint].


texts: Transaction Log Shipping Primary Server Status not Shown on Secondary Monitoring Server

texts:
I setup Log Shipping. I have Secondary Destination Server as Monitoring server also. For some reason when running this system stored procedure or looking at Server-->Reports-->Transaction Log Shipping Status:

EXEC sp_help_log_shipping_monitor

Secondary Destination (also Monitoring): I only see rows for Secondary Destination, do not see any rows for Primary

Primary Server: I see rows for Primary, even though this is not the monitoring server,

My Monitor logins are temporarily SA on both servers, to make sure its not security issue, so not sure why is Primary is not showing on Secondary Monitoring server. Is this the way Log Shipping is supposed to work by functionally ?


texts: Auto vacuuming high-write, high-update, and mostly read table types

texts:
What are good auto vacuum settings (recommendations) for tables like:

High Write Table Insert load
Range between 30-10,000 inserts in a day. The table can idle for weeks without load, but can get bursts of inserts at least three times a week.

High Update Table
It uses partition table data, is 3-8 times the size of my table from a single insert.

High Write Table
A single row gets updated only once, but bursts of unique key updates in a day
and needs to be updated it could be 30-10,000 key update.

High Read Table
Most tables are high read tables set to fill factor 80 for my data warehouse, housing table that came from the computation of High Update Table

My deletes happen monthly and in batches. Everything relating to the key gets deleted or moved as backup.
Currently my fill factor is set to be 10-20 for high update table.
Using TDS db.t3.large but I switch to db.t3.micro during low traffic.
Also, does setting fill factor really low slow down selects?


texts:
Which is the redundant relationship in this ER Diagram and why? A few of my friends and I have been trying to figure it out but keep going in circles.

where the attributes of each entity are as follows:

Employee: Employee_No, Name, Address and Telephone_No.
Department: Department_No, Name and Location.
Training Course: Course Code, Title, Start_Date and Description.


texts: Why is SQL Server's online indexing causing blocking?

texts:
My understanding is that online indexing acquires locks at the beginning (Preparation) and end (Final) phases. However, we see a lot of DML queries blocked during the Build phase.

A sampling of the queries being blocked show they have a few things in common - the Wait Type is PAGELATCH_UP, the Wait Resource references a PFS page, and the query uses locking hints (NOLOCK, ROWLOCK, etc). Could locking hints be interfering?

Can anyone shed some light here or point me in the right direction?

Let me add, the actual command was:

ALTER TABLE dbo.Whatever DROP CONSTRAINT [PK_Whatever] WITH (ONLINE=ON);

This was the clustered index and there were no nonclustered indexes.


texts:
Say I have a table like this in SQL Server 2008:

id  |  name  |  qty
-------------------
1   |  john  |    1
2   |  bill  |    3
3   |  mary  |    2
4   |  jill  |    5

I would like to query this table and return 1 row for each batch of, at most, a quantity of 2. So, the result of the query would look like:

id  |  name  |  qty
-------------------
1   |  john  |    1
2   |  bill  |    2
2   |  bill  |    1
3   |  mary  |    2
4   |  jill  |    2
4   |  jill  |    2
4   |  jill  |    1

Can this be done neatly without using a cursor? Is this possible using unpivot?

By the way, the qty column can have a maximum value of 10.