How long will a temporary MEMORY table persist if I don't drop it (MySQL) I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary table within the procedure...

BEGIN;

/* generates the temporary table of ID's */
CALL fetch_inheritance_groups('abc123',0);

/* uses the results of the stored procedure in the WHERE */
SELECT a.User_ID
FROM usr_relationships r
INNER JOIN usr_accts a ON a.User_ID = r.User_ID
WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)
GROUP BY r.User_ID;

COMMIT;

When calling the procedure, the first value is the top ID of the branch I want, and the second is the tier which the procedure uses during recursions. Prior to the recursive loop it checks if tier = 0 and if it is it runs:

DROP TEMPORARY TABLE IF EXISTS id_list;
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;

So my question is: If I don't DROP the temporary MEMORY table at the end of the procedure, or within my transaction, how long will that table persist in memory?  Is it automatically dropped once the session ends, or will it remain in memory as long as the connection is open?

**N.B. The obvious answer might be to drop the temp table prior to the commit statement, but lets assume for a moment that I can't do that.*

EDIT: To be a little more precise, what if persistent connections are employed, will the table persist through multiple requests?  So far it seems that it will and that we would need to explicitly remove the temp table to free up that resource.

UPDATE: Based on the advice from the commenters, I've found a way of adjusting my stored procedure so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the stored procedure and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

CALL fetch_inheritance_groups('abc123','0',@IDS);

... then within the stored procedure, I added a second IF tier = 0 at the very end with the following:

IF tier = 0
    THEN
    SELECT GROUP_CONCAT(DISTINCT iid SEPARATOR ',') FROM id_list INTO inherited_set;
    DROP TEMPORARY TABLE IF EXISTS id_list;
END IF;

So the result of the stored procedure is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)

... is now ...

WHERE r.Group_ID = 'abc123' OR FIND_IN_SET(r.Group_ID,@IDS)

Voila!  Thanks to the commentors for your input, and for giving me the reason I needed to try a little harder :)

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.
According to the Book

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.
It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session --  it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.
I hope this answers your question.
UPDATE 2014-01-31 11:26 EST
In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.


SAN for SQL Server: One large spindle set for all loads, or discrete by load? SQL Server 2014, Standard Edition (running under vmware, fwiw).
Adding a Dell MD3420 DAS box.

In the old school, you would set up discrete spindle sets for MDF, LDF, TEMPDB, BACKUP.

E.g.

MDF:  Qty 6: 300GB-15K, RAID 10  // 900GB storage
LDF:  Qty 4: 150GB-15K, RAID 10  // smaller data footprint, spindles to increase IOPS
TEMPDB: Qty 4: 150GB-15K, RAID 10  // smaller data footprint, spindles to increase IOPS
BACKUP: Qty 2: 2TB, RAID 1

Use Case:
This system has 5 databases, and is OLTP.

Nowadays, I am being told: Just set up Qty 10: 600GB-15K drives, and put MDF, LDF, TEMPDB and BACKUP all on there. The overall higher performance of the larger spindle set more than makes up for the conention writing to LDF and BACKUP concurrently during backups.

a) Is this new way the right way?
b) LDF is supposed to be discrete relative to MDF... I feel funny!
c) I really feel backups should be their own spindle set (for a bunch of reasons)

The winner is ?  SQL Tradition? Or the Dell Storage Architect?

This is a database recovery question first and foremost, then performance.

DB Recovery:

By keeping the LDF and the MDF file in the same physical array, then you lose any chance of performing a 'tail of log' backup.  This means your data is gone from either:

The last transaction log taken and copied off the failed array.
A mirrored pair/availability group/replicated database elsewhere

So first question is 'how much data can you lose'.  If you cannot lose more than the frequency in which you back your logs then putting it on that array isn't going to meet your data loss objective.

Performance:

The rest really depends on your usage patterns.  It seems like you do not have a active machine we can get a baseline from, if I'm wrong let me know.  Assuming we don't though, we should understand that DiskIO is critical, especially when there might not be enough RAM or in a high transaction environment. Do you think it will be very highly transactional?  If so, give lots of disks to the transaction log drive.  That version of the Dell SAN let's you short stroke your disks which should give you a good performance boost as the transaction log drives typically go multi disk not for space, but for write performance.

Putting TempDB on it's own disks was useful but if you don't have any real clear idea as to why you would do it, then really you should test this or just put it in the same array and test that.  TempDB can have odd patterns so you might get various performance results and growth.  Many people though do just put it on the same disks as the data.  It's up to you in this case.

It sometimes might be worth segregating disks by the app patterns and the DBs that support them.  I'm not sure if you have that option.  If you don't, your layout looks fine but without testing it's impossible to tell.  Personally I would test as much as I could first, look at how much growth we are expecting, and then see if it makes more sense to add more IOPS to the main data array, or to keep them separate.

Are you able to do any kind of benchmarking at all?


Why is mysqlhotcopy slower compared to rsync? My understanding is mysqlhotcopy works very similar to rsync.

In one of our servers backing the database file using rsync takes about 15 minutes for 50GB data.

Backing up the same data using mysqlhotcopy takes about 30 minutes.

How come mysqlhotcopy is significantly slower?

rsync does not have any MySQL-specific gotchas to watch out for. rsync can copy literally anything with impunity.

mysqlhotcopy has to watch for opportunities to lock files which could delay actual start time for copying.

I wrote an earlier post contrasting rsync and XtraBackup along this line of reasoning

I also wrote a post on how to copy using rsync on a running mysql server with relative brief downtime.

Here is post I made in ServerFault

Here is still another post in ServerFault, with sample code !!!


Oracle Enterprise manager express is another language I have recently installed oracle 12c for my lab, everything went fine, but when i log on the OEM express everything is in spanish, how can i change the language back to english?

I have the OS  (windows 2008 R2) in English and everything is installed in English.

Thanks in advance for the help.

OEM is a web-based application so the language is determined by the default on web browser like Internet Explorer, make sure to change it to English (note you may see Internet Explorer menu in English but the default language (localization is not)) it can be change from internet option. give it a try


Stored Procedure is taking very long to complete We have a csv file with 2000 rows and a database table1 about 2 million rows and another table2 with 60000 rows.

We need to query from table1 based on parameters from the csv file, so each row in csv should execute one select query. Initially we tried to execute the following query within a for loop in the application:

SELECT table1.c1,
       table1.c2,
       ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
       point(table1.c3, table1.c4)) * 0.2 AS length,
       table1.c5
FROM   table1
WHERE  table1.c1 IN (SELECT DISTINCT table2.c1
                     FROM      table2
                     LEFT JOIN table2.c1=table3.c1
                     WHERE table2.c2=1
                     AND   table2.c3 BETWEEN 1000 AND 2000)
HAVING length < csv.c4[i]
AND    table1.c5 BETWEEN date("start_date") AND date("end_date")
ORDER BY table.c1

the csv.c1[i] i is actually the loop index. Because of the 2000 round trips to MySQL server, it takes a long time to complete this. It takes about 16 hours just to get through the queries.

So, I wrote the SP below to avoid looping and simply call this SP so can do the looping in MySQL server:

CREATE PROCEDURE sp()
    BEGIN
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT * FROM csv_based_table;
      OPEN cur1;
      read_loop: LOOP
         FETCH cur1 INTO arg0, arg1, arg2, arg3;
         SELECT col0,
                col1,
                ST_Distance_Sphere(point(arg1, arg2),point(col3,col4))*2 AS length,
                arg0
         FROM   important_table1
         WHERE  col0 IN (SELECT DISTINCT c0
                         FROM   important_table2
                         LEFT JOIN table3
                         ON col0 = c0
                         WHERE c1=1
                         and c2 BETWEEN 1000 AND 2000)
         HAVING distance < arg3
         AND col5 BETWEEN date("start_date") AND date("end_date")
         ORDER BY arg0, col0;
      END LOOP;
      CLOSE cur1;
    END;

So, instead of using the csv file, we create a database table for that csv file and run the above stored procedure. Problem is this turned out to be slower than the original 2000 iterations loop. In a test over 100 csv rows, for loop-based original solution finished in 317.6 seconds while the stored procedure took 321.5 seconds just for the SP itself. Why is this happening and how can I optimize this?

Here's one suggestion:

CREATE TEMPORARY TABLE csv
( i int not null
, c1 ... not null
, c2 ... not null
, c3 ... not null
, c4 ... not null )

Load the csv file into the temporary table, and use a join to get the result.

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM   table1
CROSS JOIN csv
WHERE  table1.c1 IN (
                 SELECT DISTINCT table2.c1
                 FROM      table2
                 LEFT JOIN table2.c1=table3.c1
                 WHERE table2.c2=1
                 AND   table2.c3 BETWEEN 1000 AND 2000
HAVING length < csv.c4
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1

I would then look at the IN predicate (I'll assume this is what it is supposed to be):

SELECT DISTINCT table2.c1
FROM      table2
LEFT JOIN table3
    ON table2.c1=table3.c1
WHERE table2.c2=1
  AND table2.c3 BETWEEN 1000 AND 2000

The outer join against table3 can be eliminated (a smart optimizer should realize this, but I don't know if this is implemented in MySQL):

SELECT DISTINCT table2.c1
FROM      table2
WHERE table2.c2=1
  AND table2.c3 BETWEEN 1000 AND 2000

This leaves us with:

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM   table1
CROSS JOIN csv
WHERE  table1.c1 IN (
                 SELECT DISTINCT table2.c1
                 FROM      table2
                 WHERE table2.c2=1
                 AND   table2.c3 BETWEEN 1000 AND 2000
       )
HAVING length < csv.c4
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1

Next step, is trying to replace the IN PREDICATE with a JOIN:

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM   table1
CROSS JOIN csv
JOIN ( SELECT DISTINCT table2.c1
       FROM      table2
       WHERE table2.c2=1
       AND   table2.c3 BETWEEN 1000 AND 2000
     ) as X
   ON X.c1 = table1.c1
HAVING length < csv.c4
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1

It may be advantageous to push the range predicate into table1 before doing the cross join:

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM (
    select ... from table1
    where table1.c5 BETWEEN date(start_date) AND date(end_date)
) as table1
CROSS JOIN csv
[....]


Design star schema for relatonal database I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.
in this example i assume that the fact table is (order-orderitem-book)
And the measures is (category-customer-time)
My question is about book author how can we put is as measure? Is it allowed to put many to many relationship in star schema ??
And if i am wrong how to draw star schema to this relatonal db?

You could put a many-to-many relationship within a data warehouse, but many people consider it bad practice to do so - even so far as some data warehousing tools do not permit it to be created at all. Here is how I would create a star-schema from your design:

As your Author table and Category table only have one valuable attribute (the name) I would roll them into the Book table which would then become your first dimension. The Customer table can stay as-is and become a dimension as well. You would then roll the two Order tables into one and create a Order fact table which consists of OrderID, Date, BookID, CustomerID, Price - like so:

CREATE TABLE DimBook
(
    BookID      INT          NOT NULL PRIMARY KEY,
    Author      VARCHAR(50)  NOT NULL,
    Category    VARCHAR(50)  NOT NULL,
    Title       VARCHAR(50)  NOT NULL,
    ISBN        VARCHAR(50)  NOT NULL,
    Year        SMALLINT     NOT NULL,
    Price       DECIMAL(9,2) NOT NULL,
    NoPages     SMALLINT     NOT NULL,
    Description VARCHAR(100) NOT NULL
);

CREATE TABLE DimCustomer
(
    CustomerID INT         NOT NULL PRIMARY KEY,
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    ZipCode    VARCHAR(20) NOT NULL,
    City       VARCHAR(50) NOT NULL,
    State      VARCHAR(50) NOT NULL
);

CREATE TABLE FactOrders
(
    OrderID    INT          NOT NULL,
    "Date"     DATETIME     NOT NULL,
    BookID     INT          NOT NULL REFERENCES DimBook(BookID),
    CustomerID INT          NOT NULL REFERENCES DimCustomer(CustomerID),
    Price      DECIMAL(9,2) NOT NULL
);

You may also want to consider a Date dimension which is also commonly found in star-schemas and data warehouses to make searching by dates easier. A very basic implementation is below:

CREATE TABLE DimDate
(
    "Date"  DATETIME NOT NULL PRIMARY KEY,
    "Year"  SMALLINT NOT NULL,
    "Month" TINYINT  NOT NULL,
    "Day"   TINYINT  NOT NULL
);

Then, just add a foreign key from your Date attribute in the fact table to the Date key in the DimDate table. This would produce something like:

If you need to handle scenarios where a book can have many authors (which frequently happens), there are a couple of ways to do so.

The first, and my recommendation, is to have all of the authors within the Author attribute. This would allow you to easily search for all books written by the same combination of authors.

The second approach denormalises the Author attribute into its own dimension which is then referenced by the book dimension. This would create a snowflake schema (your question stated you wanted a star schema so I avoided this approach) and would also be slower when trying to search by multiple authors.

Ultimately, it depends on your exact needs and the requirements you are trying to meet. I would personally stick with having all authors in the same attribute as this is the easiest design and meets your star schema requirement.


Performance Counters for SQL Server Related to iSCSI Disk Access I am planning to move my SQL Server databases (plus TLogs and TempDB) to a new LUN on our iSCSI SAN.  The current LUNs used by these files are on their own two disk RAID 1 disk group and I'm going to a larger but shared 14 disk RAID 10 disk group.  I want to measure the performance of the current configuration and the new configuration as I move each database over and ensure that I am not starting to hit any disk performance issues (or see if I am actually increasing the performance).

There are a bunch of posts on the internet on SQL performance counters such as this one, but I am really interested in just the few that are related to network/disk usage and any latency or limits that are associated with disk reads/writes.  What are some of the important SQL Server or Windows performance counters that I should look at to create a current baseline/comparison for iSCSI disk access for SQL Server?

I agree with Mr. Denny.

When analyzing disk performance issues I would look into this counters.

Avg. Disk sec/Read - is the average time, in seconds, of a read of data from the disk.

Avg. Disk sec/Write - is the average time, in seconds, of a write of data to the disk.

Ideally you would be looking less than 15ms latency but in reality I have seen it as high as 30ms in Tier II storage.

Another counter is Avg. Disk Queue Length and you want see that number lower if possible.


Performance impact of having gaps in Identity Column in SQL Server 2005 We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there are now gaps in the primary key column.

Is there any impact to the performance of the clustered index on these tables as a result of these "missing" identities?

From my understanding as long as the index b-tree is refreshed then there shouldn't be any problems, but I can't find anything specific to confirm this.

Thanks

I don't see how gaps in your identity values could have A N Y negative impact on your performance.

After all - the values are still all 4-byte integer, they're still as optimal for JOINs as anything can be ..... and even if you have gaps, the IDENTITY values are ever-increasing....

Of course, if you physically deleted rows from your clustered index, then there will be "holes" in your pages (the "Swiss Cheese" problem :-) ) - but those would be fixed by a nightly database maintenance that you certainly do have in place, don't you??

And even if you do occasionally delete a row from your table, the impact on your index fragmentation will be marginal.


Why is it truncate table, and not simply truncate? Upon reviewing some of our code a colleague posited that he was annoyed at always having to write TRUNCATE TABLE when there is no real other usage for TRUNCATE.

Googling this, I indeed can not find a specific reason why the TABLE part is needed.
What am I missing? Is there some historical reason for the TABLE in TRUNCATE TABLE?

The syntax choice is likely for consistency, albeit redundant.  TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword.

I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


Slow MongoDB start time when data is replicated to a new machine We have a 3.2.13 Mongodb replica set with WiredTiger on AWS.

We are observing a strange behaviour: when stopping and starting the mongod service, the start time is about 10 minutes, but when stopping the mongod service, then replicating the data disk to a new AWS instance, and restarting on that machine, the start time is about 2 hours.

What may be the cause for this?

It looks like the initial slowness is because the EBS volume (which is restored from snapshot) is being initialized during the service startup.

https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-initialize.html


Having regex_matches exclude cases where both capturing groups match a 0-length string I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so:
I'm looking to split sequences that begin with a positive or negative integer followed by a sequence of letters and periods that begins with a letter.  Neither the integer nor sequence of letters/periods is required, but at least one should be for it to match.
I don't know that that's a very clear description, so here's an example.  The string '9a.-16.y -y. -7hello world' should return {{9, a.},{-16,},{,y},{,y.},{-7,hello},{,world}}.
I've gotten pretty close with the following code
select array(
  select regexp_matches(
    '9a.-16.y -y. -7hello world',
    '(-\d+|\d*)([a-zA-Z][a-zA-Z.]*|)',
    'g'
));

But it's including cases where neither capturing group matches.
{{9,a.},{-16,},{,},{,y},{,},{,},{,y.},{,},{-7,hello},{,},{,world},{,}}

I can get this by making the whole preface digit optional and ignoring the trailing pipe you put in the last group, I think you can remove the NULLs after by another process:
select array(
  select regexp_matches(
    '9a.-16.y -y. -7hello world',
    '(-\d+|\d+)?([a-zA-Z][a-zA-Z.]*)',
    'g'
));
                        array
-----------------------------------------------------
 {{9,a.},{NULL,y},{NULL,y.},{-7,hello},{NULL,world}}


When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE? The manual for Postgres 12 says:

  The following operations are always parallel restricted.

  Scans of common table expressions (CTEs).
  Scans of temporary tables.
  ...

Further down on the same manual page:

  [...] Similarly, functions must be marked PARALLEL RESTRICTED if they access
  temporary tables, client connection state, cursors, prepared
  statements, or miscellaneous backend-local state which the system
  cannot synchronize across workers. For example, setseed and random are
  parallel restricted for this last reason.

No mention of CTEs. Now I am unsure whether I can use PARALLEL SAFE for functions containing a CTE. It would make sense to me for those to be only PARALLEL RESTRICTED.

Context: I have to determine the best label for existing user-defined functions. The setting is new since Postgres 9.6, and can have a massive impact on performance as operations involving functions that are not PARALLEL SAFE will not be performed by parallel workers, PARALLEL RESTRICTED only by the leader. (And PARALLEL USAFE disables parallelism altogether.)

I posted a related question on pgsql-general.

If a function contains an query that uses a CTE, and that function is used by a parallel worker process, the query and the CTE will only be executed in the private process context of the parallel worker. There is no shared state involved in creating or scanning that CTE.

So it is safe to mark the function PARALLEL SAFE.

The quotation from the documentation says that a CTE defined in a query that should be parallelized can only be scanned by the leader process, not by the parallel workers, precisely for the reason that the CTE is not shared between processes. This has no impact on CTEs defined in queries running in functions called by parallel workers, because such nested queries won't be parallelized anyway:

  Even when it is in general possible for parallel query plans to be generated, the planner will not generate them for a given query if any of the following are true:

  [...]

  The query is running inside of another query that is already parallel. For example, if a function called by a parallel query issues an SQL query itself, that query will never use a parallel plan.


Should I use SQL Server DMVs with NOLOCK I am trying to monitor live performance data and usage, which cannot be tracked with PerformanceMonitor. What are the consequences of reading DMVs in a production live OLTP database? : Eg: sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.dm_os_performance_counters, sys.dm_exec_connections, sys.dm_io_virtual_file_stats,  sys.dm_exec_sql_text,etc

Should I just utilize WITH (NOLOCK) when querying Dmvs? Would this resolve many of the resource issues? Is there anything such as dirty read rollbacks in DMVs. I know it can exist in applications tables eg where someone submits an order then cancels, etc. Additionally, with NOLOCK, is there higher chance query will continue forever in high volume environment since I am not locking rows, pages, and things will continue be added into DMV TableView?

Thank you,

What are the consequences of reading DMVs in a production live OLTP database?

Negligible as far as my experience goes. DMV's as the name suggests are basically views or functions ( like sys.dm_db_index_physical_stats ) which get information from various caches and system tables. DMV's are very light and read data from caches. They do not cause blocking and depending on amount of data you need to fetch they very very quick. The point here is always only query data from caches which you actually need.

You may use NOLOCK hint in your DMV query you would see some people using it but frankly I do not see much of need to use it with DMV's. Its function though still remains same. So when you use NOLOCK hint you say to SQL Server that give me the data I am asking no matter what is state of the data is. It may have been changed and yet not committed so you get "dirty" data. NOLOCK hint avoids select queries taking shared lock while reading data. The drawbacks are many

  Additionally, with NOLOCK, is there higher chance query will continue forever in high volume environment since I am not locking rows, pages, and things will continue be added into DMV TableView?

Query will only continue for duration until it finishes its task, unless you run a never ending loop. The NOLOCK only ensures that query is minimally blocked and also note that when using NOLOCK if required other locks like schema stability  are taken


Scenario to automate the promotion of a read replica when the master db is not responding I would like to set a scenario like the following:

Set an RDS event subscription [that the MySQL is unresponsive]
that will publish to an SNS topic
that will trigger a Lambda function
which will run a script that will promote a read replica [in another region] to master

Is such a case feasible?

Which DB metric should I monitor to confirm that the DB is not responding and that there is no false alarm?

Would it be dangerous to automate the promotion of the read replica? Should such actions remain manual and just add alerts?

Beware of Split Brain.

The tough problem in promoting a Slave to be Master happens when the problem is in the network.  With a dead network, most types of monitoring will mistakenly think that the Master is dead and promote the Slave.  Now you would have both servers alive and thinking they are the 'Master'.  This condition is called the dreaded "split brain".  Pretty quickly, clients insert duplicate keys and/or generate duplicate auto_increment ids.  The data corruption can be terrible to unravel.

MHA half solves it by promoting a slave when it is sure that it is safe.  If the problem might be a network glitch, it punts.

Galera fully solves it by requiring a quorum of nodes to vote that they have the majority.  This, however, requires 3 nodes, preferably in 3 geographic locations.  (Think flood, hurricane, earthquake, etc.)  All nodes can be writable (that's another feature of Galera).  Galera is built into MariaDB and PXC.


PostgreSQL batch inserts fail silent randomly I have been using Kettle to import data from a SQL Server to PostgreSQL on AWS RDS and when I was enabling all of my foreign key constraints I realized that data was missing, often in the even hundreds.

But checking the log of the Kettle job, it seems to be doing all read and writes correctly from a table of 1923 entries

2017/02/26 21:36:42 - Table output.5 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.3 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.7 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.6 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.1 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)
2017/02/26 21:36:42 - Table output.2 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)
2017/02/26 21:36:42 - Table output.4 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.0 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)

But when running the query count on Postgres I get 1423 entries, and similar behavior on maybe 10% of all tables I have transferred.

As I don't have any prior experience in neither Kettle nor Postgres, what is it that can cause this issue?

With the help of the author of the script i used (sqlserver2pgsql) we realized it was a (new) issue with how Kettle handled parallel output table truncates.

https://github.com/dalibo/sqlserver2pgsql/issues/57#issuecomment-282675275

What happened was, and the reason i only lacked even hundreds of data, that the output job in some cases did run truncate after one or more workers already had done inserts on the table


Reporting Services buttons show up one under the other instead of side-by-side This seems a browser problem to me.  When I render a reporting services report in IE11, the "Export", "Refresh" and "Export to Data Feed" buttons show up under each other taking up a lot of extra screen space.  Does anybody know what could be causing this?

I am running Reporting Services 2012:

My IE version:

and here is how the buttons appear:

I found this self-answered question on Stack Overflow - maybe it will help you.

SQL Server Report Manager report showing export, refresh, data feed buttons on separate lines


How to select the sum from three tables and group by date I have 3 tables.  works,software,agent

it shows like that:

agent:

id  name
1   agent1
2   agent2
3   agent3
4   agent4

works

id  name    date(TIMESTAMP type)    agent_id
1   w1  2014/1/1    1
2   w2  2014/2/2    1
3   w3  2014/1/3    2
4   w4  2014/1/4    2
5   w5  2014/1/5    3
6   w6  2014/1/6    4
7   w7  2014/1/7    4
8   w8  2014/1/8    4
9   w9  2014/3/9    4

software

id  name    date(TIMESTAMP type)  agent_id
1   s1  2014/1/1    1
2   s2  2014/2/2    1
3   s3  2014/1/3    2
4   s4  2014/1/4    2
5   s5  2014/1/5    3
6   s6  2014/1/6    4
7   s7  2014/1/7    4
8   s8  2014/1/8    4
9   s9  2014/3/9    4

and I want to get the result like that: get the every month of current year sum of works + software connect by agent_id

Jan-14  Feb-14  Mar-14  Apr-14  May-14  Jun-14  Jul-14  Aug-14  Sep-14  Oct-14  Nov-14  Dec-14
agent1  2   2   0   0   0   0   0   0   0   0   0   0
agent2  4   0   0   0   0   0   0   0   0   0   0   0
agent3  2   0   0   0   0   0   0   0   0   0   0   0
agent4  6   0   2   0   0   0   0   0   0   0   0   0

How to write the sql in MySql?

With other servers you  can use common table expressions.  With MySQL you can use derived tables as in:

select name, year, month, sum(cnt) from
(select agent.name, year(date) as year, monthname(date) as month, count(*) as cnt
from
agent inner join software on (agent.id = software.agent_id) group by agent.name, year(date), monthname(date)
union (select agent.name, year(date) as year, monthname(date) as month, count(*) as cnt
from
agent inner join works on (agent.id = works.agent_id)
group by agent.name, year(date), monthname(date))) T1
group by name, year, month


Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error? I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.

As part of the check I compute the size of the data type's range for every IDENTITY column; I use that to calculate what percentage of that range has been exhausted.  For DECIMAL and NUMERIC the size of that range is 2 * 10^p - 2 where p is the precision.

I created a bunch of test tables with DECIMAL and NUMERIC IDENTITY columns and attempted to calculate their ranges as follows:

SELECT POWER(10.0, precision)
FROM sys.columns
WHERE
       is_identity = 1
   AND type_is_decimal_or_numeric
;

This threw the following error:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.

I narrowed it down to the IDENTITY columns of type DECIMAL(38, 0) (i.e. with the maximum precision), so I then tried the POWER() calculation directly on that value.

All of the following queries

SELECT POWER(10.0, 38.0);
SELECT CONVERT(FLOAT, (POWER(10.0, 38.0)));
SELECT CAST(POWER(10.0, 38.0) AS FLOAT);

also resulted in the same error.

Why does SQL Server try to convert the output of POWER(), which is of type FLOAT, to NUMERIC (especially when FLOAT has a higher precedence)?
How can I dynamically calculate the range of a DECIMAL or NUMERIC column for all possible precisions (including p = 38, of course)?

Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding POWER() here.

Hold on to your knickers.

Preamble

First, I present to you exhibit A, the MSDN documentation for POWER():

  Syntax

  POWER ( float_expression , y )

  Arguments

  float_expression
  Is an expression of type float or of a type that can be implicitly converted to float.

  Return Types

  Same as float_expression.

You may conclude from reading that last line that POWER()'s return type is FLOAT, but read again. float_expression is "of type float or of a type that can be implicitly converted to float". So, despite its name, float_expression may actually be a FLOAT, a DECIMAL, or an INT. Since the output of POWER() is the same as that of float_expression, it too may also be one of those types.

So we have a scalar function with return types that depend on the input. Could it be?

Observations

I present to you exhibit B, a test demonstrating that POWER() casts its output to different data types depending on its input.

SELECT
    POWER(10, 3)             AS int
  , POWER(1000000000000, 3)  AS numeric0     -- one trillion
  , POWER(10.0, 3)           AS numeric1
  , POWER(10.12305, 3)       AS numeric5
  , POWER(1e1, 3)            AS float
INTO power_test;

EXECUTE sp_help power_test;

DROP TABLE power_test;

The relevant results are:

Column_name    Type      Length    Prec     Scale
-------------------------------------------------
int            int       4         10       0
numeric0       numeric   17        38       0
numeric1       numeric   17        38       1
numeric5       numeric   17        38       5
float          float     8         53       NULL

What appears to be happening is that POWER() casts float_expression into the smallest type that fits it, not including BIGINT.

Therefore, SELECT POWER(10.0, 38); fails with an overflow error because 10.0 gets cast to NUMERIC(38, 1) which isn't big enough to hold the result of 1038. That's because 1038 expands to take 39 digits before the decimal, whereas NUMERIC(38, 1) can store 37 digits before the decimal plus one after it. Therefore, the maximum value NUMERIC(38, 1) can hold is 1037 - 0.1.

Armed with this understanding I can concoct another overflow failure as follows.

SELECT POWER(1000000000, 3);    -- one billion

One billion (as opposed to the one trillion from the first example, which is cast to NUMERIC(38, 0)) is just small enough to fit in an INT. One billion raised to the third power, however, is too big for INT, hence the overflow error.

Several other functions exhibit similar behavior, where their output type is dependent on their input:

Mathematical functions: POWER(), CEILING(), FLOOR(), RADIANS(), DEGREES(), and ABS()
System functions and expressions: NULLIF(), ISNULL(), COALESCE(), IIF(), CHOOSE(), and CASE expressions
Arithmetic operators: Both SELECT 2 * @MAX_INT; and SELECT @MAX_SMALLINT + @MAX_SMALLINT;, for example, result in arithmetic overflows when the variables are of the named data type.

Conclusion

In this particular case, the solution is to use SELECT POWER(1e1, precision).... This will work for all possible precisions since 1e1 gets cast to FLOAT, which can hold ridiculously large numbers.

Since these functions are so commonplace, it's important to understand that your results may be rounded or may cause overflow errors due to their behavior. If you expect or rely on a specific data type for your output, explicitly cast the relevant input as necessary.

So kids, now that you know this, you may go forth and prosper.


In MySQL, get number of pregnant women who received 3 ANC checkups I am working on a project where it is required to find out the total number of pregnant women who have received 3 ANC(Ante-Natal Care) checkups done.

A pregnant women is said to receive 3 ANC checkups if any 3 of the 4 columns(visit1_date,visit2_date,visit3_date and visit4_date) have some value.

For this, I need to calculate the count of number of rows where any 3 of 4 columns given in the table are not null.

So, basically, what is required is to find a count of all rows where any three of

visit1_date, visit2_date,visit3_date,visit4_date are NOT NULL

My table has columns individually for each of the visit dates

I have tried using IFNULL, but I have no idea if this would help.

Something like (untested):

select *
from <table>
where if(visit1_date,1,0)+if(visit2_date,1,0)+if(visit3_date,1,0)+if(visit4_date,1,0) >= 3;

should do the trick.


I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation from Czech: "chyba" = "error", "Stav SQL" = "SQL state"):

ERROR:  cannot insert into view "ukaz_lok"
DETAIL:  Views that return columns that are not columns of their base relation are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
********** Chyba **********

ERROR: cannot insert into view "ukaz_lok"
Stav SQL: 55000

This happened before with some other INSTEAD OF triggers - this bug lasted just for some time (few hours? I don't remember exactly) and then PostgreSQL found the trigger. I tried various things (change the trigger, delete all triggers and views and creating them anew etc.), but I'm not sure what worked, just that something worked. So how exactly to make the PostgreSQL recognize the trigger?

I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Just in case it was caused by something in the code, here it is:

CREATE TABLE lokalita
(
  kod_lok text NOT NULL,
  nazev text NOT NULL,
  katastr text NOT NULL,
  presnost integer NOT NULL,
  stred geometry(Point,4326) NOT NULL,
  rozsah geometry(Polygon,4326),
  CONSTRAINT pk_lok PRIMARY KEY (kod_lok),
  CONSTRAINT fk_pro_pr FOREIGN KEY (presnost)
      REFERENCES pro_presnost (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE OR REPLACE VIEW ukaz_lok AS
  SELECT
    l.kod_lok AS _kod,
    l.nazev AS _nazev,
    l.katastr AS _katastr,
    l.presnost AS _presnost,
    50.0 AS _sirka,
    14.0 AS _delka
  FROM lokalita l;

CREATE OR REPLACE FUNCTION osetri_lok() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO lokalita (kod_lok, nazev, katastr, presnost, stred)
    VALUES(
      NEW._kod,
      NEW._nazev,
      NEW._katastr,
      NEW._presnost,
      ST_GeomFromText('POINT('|| NEW.delka ||' '|| NEW.sirka ||')', 4326)
    );
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE lokalita
    SET
      nazev = NEW._nazev,
      katastr = NEW._katastr,
      presnost = NEW._presnost,
      stred = ST_GeomFromText('POINT('|| NEW.delka ||' '|| NEW.sirka ||')', 4326)
    WHERE lod_lok = OLD._kod;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM lokalita WHERE kod_lok = OLD._kod;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER osetri_lok
INSTEAD OF DELETE ON ukaz_lok
FOR EACH ROW
  EXECUTE PROCEDURE osetri_lok();

INSERT INTO ukaz_lok (_kod, _nazev, _katastr, _presnost, _delka, _sirka)
  VALUES(
  'Kote2',
  'Kotěhůlky 2',
  'Kotěhůlky',
  2,
  14.060316,
  50.432044
);

You have a trigger INSTEAD OF DELETE in your code, but none INSTEAD OF INSERT. Create that, too.
You can make that a trigger INSTEAD OF INSERT OR DELETE, but I'd suggest separate trigger functions for INSERT and DELETE and separate triggers. Simplifies the function code (no more need for IF TG_OP =  etc.).
Also consider this quote from the manual:

A row-level INSTEAD OF trigger should either return NULL to
indicate that it did not modify any data from the view's underlying
base tables, or it should return the view row that was passed in (the
NEW row for INSERT and UPDATE operations, or the OLD row for
DELETE operations). A nonnull return value is used to signal that
the trigger performed the necessary data modifications in the view.
This will cause the count of the number of rows affected by the
command to be incremented. For INSERT and UPDATE operations only,
the trigger may modify the NEW row before returning it. This will
change the data returned by INSERT RETURNING or UPDATE RETURNING,
and is useful when the view will not show exactly the same data that
was provided.


WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant? I'm in the process of rewriting queries that no longer pull in all the required data. My question is in regard to a practice I've never seen and haven't found any question on StackExchange that specifically addresses the issue.

I know the point of the HAVING statement is to introduce conditions on aggregations, just like WHERE introduces conditions on individual rows. However, what I'm seeing in this code is HAVING being used in lieu of WHERE on queries with aggregations. The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.

For example:

SELECT id, filedate, SUM(amount)
FROM Sales
GROUP BY id, filedate
HAVING id = 123 AND filedate = '1/1/2018'

As opposed to:

SELECT id, filedate, SUM(amount)
FROM Sales
WHERE id = 123 AND filedate = '1/1/2018'
GROUP BY id, filedate

Are there performance implications or other advantages/disadvantages to this strategy?

I haven't tried running diagnostics myself, not a priority and I'd have to do it on my own time. However, I think I may if there isn't a clear answer on this.

My concern is how the optimizer views this query. Does it aggregate all data and then restrict the result set based on the HAVING clause, or does it realize it can apply the having conditions on the individual rows since they are specifically referencing non-aggregated columns?

EDIT: For my example queries and the actual SQL I'm rewriting, the plans are identical, but the queries are of similar complexity and I'm not yet knowledgeable enough to draw conclusions from the identical plans.

The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.

The problem here is in how you are describing what the HAVING clause applies to. The HAVING clause always applies to aggregated fields, which is all remaining columns post-aggregation. You are trying to show / say that the HAVING clause is not being applied to any aggregate functions, which is what they usually apply to. But in reality, the HAVING clause governs the result of that aggregate function, or, in your first example, the result of the grouping column. But in both cases, the aggregation has already been performed.

So in terms of performance (not to mention readability for others trying to update this code later), you use the WHERE clause to filter down to what will be aggregated, and then the HAVING clause to filter out what has been aggregated. And, while the result of a simple test as shown in the question masks the difference between the timing of the two (or logical placement in the sequence that the query is processed in) such that they "appear" to be doing the same thing, I would be quite surprised if it was not less efficient to aggregate a bunch of rows only to throw them out later when logically they could have been eliminated prior to storing / computing the aggregations. HOWEVER, if you do see that they execution plans are similar for this simple example, I am willing to bet that it is merely due to the optimizer seeing that it would be more efficient to make those HAVING conditions actual WHERE conditions as it rewrites the query before it executes it. But in that case, I still would advise against writing queries this way because you are making the optimizer take extra time to rewrite bad code when it should be spending that time / CPU cycles finding a more efficient plan. @DavidSpillett added (in a comment on this answer): "Furthermore, you are relying on the query planner seeing the optimisation potential, which it may not in more complex queries or if your code ends up ported to another database (or even just an older version of SQL Server)".

For what it's worth, even the Microsoft documentation for the HAVING clause stated that it acted as a WHERE clause when no GROUP BY was present. Now that the documentation is on GitHub, I was able to correct it recently via Pull Request #235: Correct and improve HAVING clause.


How can I set two values at a single command? Here I want to set Country name for Los Vegas and Alaska from a single sql command select *from drcat;

+----+-------+-----+----------+------------+---------+
| id | name  | age | salary   | address    | Country |
+----+-------+-----+----------+------------+---------+
|  1 | ALICE |  27 | 84000.00 | Los Vegas  | 0       |
|  2 | BOB   |  30 | 90000.50 | California | 0       |
|  3 | SMITH |  28 | 88000.89 | New York   | USA     |
|  4 | DAVID |  30 | 78000.00 | Virginia   | 0       |
|  5 | PETER |  26 | 89321.00 | Singapore  | 0       |
|  6 | NOVAK |  28 | 96015.00 | Alaska     | 0       |
|  7 | HAL   |  32 | 74552.00 | New York   | USA     |
+----+-------+-----+----------+------------+---------+
7 rows in set (0.00 sec)

UPDATE drcat SET Country='USA' WHERE address='California' 'Alaska';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

You can use an OR condition:

UPDATE drcat SET Country='USA' WHERE address='California' OR address='Alaska';

or you can use IN ([List of values]):

UPDATE drcat SET Country='USA' WHERE address IN ('California', 'Alaska');


Multi values for SSRS parameter with Like statement IN SSRS, I have a parameter named Comment which I set to Allow multiple values

and specified available values for it.

My problem is that if I select one value, I will get results but if I select more than one, I will get an error.

The problem is probably because I wrote my SQL statement like this:

dbo.tbl_Status.Comments LIKE ('%' + @Comment + '%')

NB:  I need to use LIKE and not IN

You would need to change your query in an expression and concatenate the condition using the JOIN expression on your parameter values.

For instance, if you create a report based on the Adventureworks 2014 database you could add a parameter like this:

With some values that appear in the table:

If you then enter an expression for your dataset instead of a query like this:

="SELECT "
&     "[NationalIDNumber],"
&      "[JobTitle]"
&"FROM [AdventureWorks2014].[HumanResources].[Employee]"
&"WHERE [JobTitle] LIKE '%" & Join(Parameters!jobtitle.Label, "%' OR [JobTitle] LIKE '%") & "%'"

It would result in what you want when selecting a single value:

Because the concatenated expression is this:

SELECT [NationalIDNumber]
    ,[JobTitle]
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [JobTitle] LIKE '%Engineer%'

And it would also work when you select multiple values:

Because the expression would result in this:

SELECT [NationalIDNumber]
    ,[JobTitle]
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [JobTitle] LIKE '%Engineer%'
    OR [JobTitle] LIKE '%Marketing%'

Full .RDL file can be found on Github Gist and requires a local Adventureworks 2014 database.


What is the significance of root and page level fragmentation in a clustered b-tree index? In investigating fragmentation on some of our large tables in a SQL Server 2005 database, I was using a query based on sys.dm_db_index_physical_stats. The query returns a row for each level in the b-tree for an index. In evaluating the clustered indexes, I noticed that fragmentation increased the further up the tree I went. At the leaf level, fragmentation was only 1%, but at the root level, it was 50% fragmented or greater. Here's an example:

TableName         IndexName             avg_fragmentation_in_percent   record_count
MyTableName       MyClustered_Index     91.66666667                    1,901
MyTableName       MyClustered_Index     35.77064703                    528,681
MyTableName       MyClustered_Index     1.948812233                    3,616,888

So looking at this, I was curious: is the fragmentation I am seeing at the upper levels because the pages that the index values are occupying are acting the same way that really small tables do, occupying mixed extents, or something similar to that?

If that is not the case, do I need to be concerned with those fragmentation numbers in the upper levels, or is the leaf level fragmentation the only thing that is significant here?

Thanks for your time.

EDIT 1:

Adding the query results with page counts:

TableName           IndexName           avg_fragmentation_in_percent    page_count
MyTable             MyClusteredIndex    0.2687447                       823086
MyTable             MyClusteredIndex    6.389452333                     1972
MyTable             MyClusteredIndex    90.90909091                     11

Fragmentation in non-leaf levels is typically not an issue due to the difference in the number of pages between the leaf and non-leaf levels.  For example, here are the number of pages per level in a fairly ordinary 8 million row table with an int primary key:

index_level page_count
0           156623
1           653
2           5
3           1

The 659 non-leaf pages have a very good chance to remain in buffer pool because any seek into the table starts at the root and walks down the tree.  Even if they did need to be pulled from disk and it was completely random 8k I/O, they still make up less than one half of one percent of the table.


Installing SQL Server, I get unmet dependencies libc++1 and libjemalloc1 but it is not installable Here is the output,

$ sudo apt install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mssql-server : Depends: libjemalloc1 but it is not installable
                Depends: libc++1 but it is not installable

This is because both of those packages are in the Ubuntu Universe, to enable that repository you can use,

sudo add-apt-repository "deb http://archive.ubuntu.com/ubuntu $(lsb_release -sc) universe"

Note that the Universe Packages are not Officially Supported by Canonical and the Ubuntu Team, but instead by the community. For more information see the Security FAQ


What does COUNT(1) actually count? Possible Duplicate:
  What is the difference between select count(*) and select count(any_non_null_column)?

I have heard it commonly stated that when counting the number of rows in a query, you should not do a COUNT(*) but you should do a count on an indexed column.

I've seen DBAs, when counting the number of rows, run SELECT COUNT(1) FROM table;.

What is the 1 in the query?

I've tried putting in other numbers (2, 0, -1) and result is always the same as using 1.

Is this just a shortcut rather than having list a specific column to count?

Are there any performance differences to using a constant versus listing a column name?

I'm currently using MySQl 5.1.60.

if you put count(*), count(1) or count("test") it will give you the same result because mysql will count the number of rows, for example:

select count(fieldname) from table;

will display the same result that

select count(*) from table;

or

select count(1) from table

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

mysql> select 1 from language;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
6 rows in set (0.00 sec)

mysql> select 'anything'from language;
+----------+
| anything |
+----------+
| anything |
| anything |
| anything |
| anything |
| anything |
| anything |
+----------+
6 rows in set (0.00 sec)

mysql> select count(1), count(*), count('anything') from language;
+----------+----------+-------------------+
| count(1) | count(*) | count('anything') |
+----------+----------+-------------------+
|        6 |        6 |                 6 |
+----------+----------+-------------------+
1 row in set (0.00 sec)

the result will be a number equal to the number of rows of that table.

PLEASE BE AWARE THAT NULL FIELDS WILL NOT BE COUNTED....

mysql> select original_language_id from film where original_language_id is null;
+----------------------+
| original_language_id |
+----------------------+
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
......
|                 NULL |
+----------------------+
1000 rows in set (0.00 sec)

mysql> select count(original_language_id) from film where original_language_id is null;
+-----------------------------+
| count(original_language_id) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

FOR THAT REASON IF YOU WANT TO COUNT ROWS I THINK count(something) is a good option

mysql> SELECT COUNT(1) FROM film;
+----------+
| COUNT(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)


How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint' CREATE TABLE EMPLOYEE (
 employeeNumber VARCHAR (15) NOT NULL,
employeeName VARCHAR (20) NOT NULL,
department VARCHAR (10) NOT NULL,
 phone INT NOT NULL,
PRIMARY KEY (employeeName, department)

CREATE TABLE DEPARTMENTS(
department VARCHAR (10) NOT NULL,
 department Code VARCHAR(10) NOT NULL,
department Name VARCHAR(30) NOT NULL,
FOREIGN KEY (department)
REFERENCES EMPLOYEE (department)

CREATE TABLE EMAIL_INFO(
 employee Name VARCHAR (20) NOT NULL,
 email VARCHAR (30) NOT NULL,
FOREIGN KEY (employeeName)
 REFERENCES EMPLOYEE (employeeName)
);

From MySQL documentation:

  MySQL requires indexes on foreign keys and referenced keys so that
  foreign key checks can be fast and not require a table scan. In the
  referencing table, there must be an index where the foreign key
  columns are listed as the first columns in the same order. Such an
  index is created on the referencing table automatically if it does not
  exist. This index might be silently dropped later if you create
  another index that can be used to enforce the foreign key constraint.
  index_name, if given, is used as described previously.

You need to create corresponding indexes on EMPLOYEE table.


maxdate not bringing back single line I am using SQL Server Management Studio.I have tried several max date options to return the latest records and notes but keep getting multiple entries instead of just the latest one.  can you advise where I have gone wrong and suggest better alternative

SELECT     [Pupil ID], [Event Date],[Header Note], [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],[Header Note],[Module ID]
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]

  This gives the error... Msg 306, Level 16, State 2, Line 4

The text, ntext, and image data types can not be compared or sorted, except when using 'IS NULL' or 'LIKE' operator.

So proceeded onto this 2nd attempt and used CAST, this got rid of the 306 error but is still not returning the maximum entry, ie for Pupil id 59775, only 1 line should be returned showing the entry for 2008-01-08

SELECT     [Pupil ID], [Event Date], CAST([Header Note]AS VARCHAR(100))NOTE, [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],CAST([Header Note]AS VARCHAR(100)),[Module ID]
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]

also tried

SELECT     [Pupil ID], MAX(CAST([Event Date] AS DATETIME)), CAST([Header Note]AS VARCHAR(100))NOTE, [Module ID], [Event ID] AS ID
FROM         dbo.vwPupil_EventType EV
WHERE     ([Module ID] = 22)
GROUP BY [Event ID],[Pupil ID], [Event Date],CAST([Header Note]AS VARCHAR(100)),[Module ID]
HAVING [Event Date]IN (SELECT MAX([Event Date])
FROM dbo.vwPupil_EventType
WHERE EV.[Event ID] = [Event ID])
ORDER BY [Pupil ID]

You didn't supply any test data to validate my results against and perhaps I'm misunderstanding exactly what you are trying to achieve, but it seems this would give you the correct answer.

Declare @T table
(
[pupil id] int,
[event date] date,
[header note] varchar(50),
[module id] int,
[event id] int
)

insert into @T values(1,'2010-01-01','note1',22,1)
insert into @T values(1,'2016-01-01','latest note',22,1)

insert into @T values(2,'2013-01-01','note1',22,1)
insert into @T values(2,'2015-01-01','latest note',22,1)

SELECT [Pupil ID]
    ,[Event Date]
    ,[Header Note]
    ,[Module ID]
    ,[Event ID] AS ID
FROM @T EV
WHERE [Module ID] = 22
    AND [Event Date] = (
        SELECT MAX([Event Date])
        FROM @t
        WHERE [Pupil ID] = EV.[Pupil ID]
            AND [Module ID] = EV.[Module ID]
        )


What happens when we take a timediff between timestamp and datetime Say I have following table in mysql (5.5+):

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `y_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My x_time is a timestamp, and y_time is a datetime column

I want to understand that what happens when I do TIMESTAMPDIFF(SECOND, x_time, y_time)

Does x_time gets converted to datetime and then the difference is calculated? or it happens vice-versa? Mysql manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff) says: One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary.

My assumption is that both the columns x_time and y_time has to been of one datatype to calculate TIMESTAMPDIFF, and if it is not already then mysql has to do it internally. Am I wrong here?

Also, if it has to convert one of the column to match the another column's datatype, then how the performance will matter for the following query? Will it do the datatype conversion for 5000 rows? And how time consuming process is this?

SELECT * from test WHERE `TIMESTAMPDIFF(SECOND, x_time, y_time)` LIMIT 100000, 5000

In addition to Rolando's answer, please note that a TIMESTAMP column is timezone-aware, while DATETIME is not.

If you don't have multiple time zones, then you're OK; but still, if ever you wanted to convert to other timezones, the TIMESTAMPDIFF calculation will provide with corrupted results.


Counting failed attempt Here is my code:
create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(1),
isLocked number(1)
);

I want to count attempt column and if 5 then isLocked become 1 from 0;
How can I make query, view or procedure?

A couple of notes,
Rather than track only failures, it's generally a good idea to know when people sign in, too. There is probably already a table that handles authentication tokens, but there's no reason why failed_login_attempts couldn't contain both successful and unsuccessful authentication attempts.
By having isLocked on a secondary table you leave yourself open to a bunch of crazy hack "solutions" to fix data inconsistencies when an account should be marked as locked or not ... particularly for users who regularly lose their passwords. Logically, if an account is "locked", then that should go on the Account (or User) table so that you're not always hitting a table that will quickly have a lot of data to determine whether a login can take place.
For example:
Account
  id
  name
  email
  ...
  is_locked
  updated_at

Then ...
LoginTXN
  id
  account_id
  success
  source_ip
  created_at

From here you can record all login attempts into LoginTXN (or whatever you decide to call it), mark the success field as 0 on failure, 1 on success (or N and Y), and go from there.
When checking to see how many login attempts have been made, you'll need to do something like:
SELECT COUNT(txn.id)
  FROM LoginTXN txn INNER JOIN (SELECT z.account_id, MAX(z.id) as recent_success
                                  FROM LoginTXN z
                                 WHERE z.account_id = 999
                                 GROUP BY z.account_id) sok ON txn.account_id = sok.account_id
                                                           AND txn.id > sok.recent_success

Note: This is pseudo-code. Don't simply copy/paste it.

Which will then give you a count. If you want to have conditions like "5 failed attempts in the last hour", thereby locking the account for — at most — 60 minutes, you could do that with a WHERE statement that examines the created_at value in LoginTXN.
Of course, if this is being put into a view or a stored procedure, then an is_locked field on Account may be wholly unnecessary as the authentication rule could be encapsulated completely within the view or stored procedure.
Hope this gives you a couple of pointers that will lead you to a more complete solution.

Friendly Aside: Try to stick with a consistent naming scheme when making tables. If you prefer to use words separated by underscores, like is_locked, then stick with that. If you prefer camel-case, like isLocked, then stick with that. You — and every other developer who works on this code in the future — will appreciate a predictable consistency. As you can probably see from my examples above, I tend to go with camel-cased tables, and underscored columns.


How to upgrade SQL Server 2008 to 2012 with table partition? My project is upgrading from SQL Server 2008 to 2012, but old version consisting table partition. is this feature will work same as in 2012 also ?

If you're talking about partitioning that utilizes partition schemes and partition functions in Sql Server 2008, you're obviously using Enterprise Edition.  That functionality has been supported in all Enterprise releases since its inception in 2005.

As of Sql server 2016 SP1, that functionality is supported in ALL editions.

Generally speaking, anytime you are preparing to upgrade from one version of Sql Server to another (2008 -> 2012, etc.), it's prudent to run the Upgrade Advisor tool for the release you are targeting.

  Upgrade Advisor analyzes installed components from earlier versions of
  SQL Server, and then generates a report that identifies issues to fix
  either before or after you upgrade.


Create stored procedure inside try-catch block I'm using sqlserver 2014 and trying to create a StoredProcedure inside try-catch block like this:

BEGIN TRY
  CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT ''
END TRY
BEGIN CATCH
  ...
END CATCH

It fails to run with the error of: Incorrect syntax near 'SELECT' Expecting EXTERNAL.
Then i changed sp like: CREATE PROCEDURE [ammeghezi1] (@w INT) AS BEGIN SELECT '' END (cover sp with BEGIN-END block), But the error did not change. I also add GO after BEGIN TRY statement and it just got worst.
I'm getting to conclude that creating sp inside TRY_CATCH block is not practical.

Is this even possible to create sp inside TRY_CATCH block? And how?

From the CREATE PROCEDURE documentation:

  The CREATE PROCEDURE statement cannot be combined with other
  Transact-SQL statements in a single batch.

If you need a try/catch, you'll need to execute the DDL using dynamic SQL so that it is in a separate batch:

BEGIN TRY
    DECLARE @CreateProcedureStatement nvarchar(MAX) =
N'CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT ''''';
    EXEC sp_executesql  @CreateProcedureStatement;
END TRY
BEGIN CATCH
    THROW;
END CATCH
GO


SQL Server Compact - Will it perform well with heavier traffic sites? I'm using Orchard CMS, and on initial setup, the default setting is to use SQL Server Compact. I'm not too knowledgeable about different database types and performance.

So, will SQL Server Compact be able to handle a website getting 1,000+ hits? Assuming all else with the hosting provider is exceptional (bandwidth, up-time, etc).

I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues.

See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx  for warnings on:

Lost updates.
Inconsistent analysis (nonrepeatable reads).
Phantom reads.

If you need a free and much more functional version of SQL Server, consider using SQL Server Express.  Its limits are considerable above the Compact edition.  You can lookup its limitations.


How to user the Tablediff Utility with Replication Filters I am using merge replication in SQL 2012. I am trying out the TableDiff utility to show non convergence.

I see a problem with this approach though. This is because I am using parameterised filters to filter the subscriptions.

In this instance the TableDiff utility tells me that there are missing rows in the subscription and generates the SQL to insert them. If I swap the source and destination around it generates a whole lot of delete statements to remove the records at the publication.

This is not a correct result. It should just check the rows that exist at the subscriber, or understand the filters and know which rows should be at the subscriber.

Can I use this utility in this instance? If not is there an alternative? I find it surprising that none of the documentation about this utility mentions this problem.

Run TableDiff.exe against a view configured to limit the source table in the same way your replication filter does.  I've created an example here:

USE Test;

    /* Create two test tables, TableDiff1 is the source and TableDiff2 is the destination */
CREATE TABLE TableDiff1
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff1_TableData DEFAULT (NEWID())
);

CREATE TABLE TableDiff2
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff2_TableData DEFAULT (NEWID())
);

    /* Create some dummy data in TableDiff1 */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (2, DEFAULT);

    /* Copy the dummy data into TableDiff2, with a FILTER */
INSERT INTO TableDiff2 (FilterColumn, TableData)
SELECT FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

    /* Add some more data to TableDiff1 that does not exist in TableDiff2
       so TableDiff.exe shows differences between the two tables */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);

SELECT * FROM TableDiff1;
SELECT * FROM TableDiff2;

Next, we run TableDiff.exe against both tables, clearly there will be differences since we are not excluding the filtered rows (FilterColumn = 1)

  C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost
  -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1 -destinationserve
  r localhost -destinationdatabase Test -destinationschema dbo -destinationtable T
  ableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1] on localhost and Table [Test].[dbo].[TableDiff2]
 on localhost have 2 differences.
Err     TableID
Src. Only       3
Src. Only       4
The requested operation took 0.1573824 seconds.

Next, we create a View that has a WHERE filter to select only the FilterColumn = 1 rows:

GO
CREATE VIEW TableDiff1_Filtered
AS
SELECT TableID, FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

Now, when we run TableDiff.exe against the view, we get the desired results.  Only a single row is identified as missing from the destination table (the row we added after "replicating" the rows from the source table).

  C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost
  -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1_Filtered -destina
  tionserver localhost -destinationdatabase Test -destinationschema dbo -destinati
  ontable TableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1_Filtered
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1_Filtered] on localhost and Table [Test].[dbo].[Ta
bleDiff2] on localhost have 1 differences.
Err     TableID
Src. Only       4
The requested operation took 0.1220787 seconds.


Lost data, how to recover (sql server) We currently have a db that the backup has been set to Simple that is being backed up every night (we have backups for last 7 days), is it possible to find records that were deleted in say June?

The db is fully functional so no corrupting or anything, we just want to be able to see if we can some how find deleted records in the current log file.
I have used apexsql log but that shows there is nothing, assuming because when a backup is done in simple mode, it pretty much discards the log.

Another question is, say i want to be able to have the ability to get deleted records in the future, should i be setting my backup to "full" and is that good enough or do i need also do bulk log backups?

I guess is there a simple way that its explained on what mode means what and what type of recover I get.  The ms article https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017 talks about this but It was not clear to me what needs to happen in diff scenarios when one needs to get specific deleted/changed records.

As long as your recovery model is Simple and full backups for only last 7 days you do not have any chance to see what was deleted in June.

Entries in the log file for Simple recovery are discarded when SQL Server does not need them for crash recovery.

The answer to your second question - yes, you have to switch to Full recovery. Then with the help of Apex software (or some other) you probably will be able to restore deleted in the past records if you have log backups for that period of time.
Be careful when switching to Full recovery - it has gotchas. Make sure to learn this Q/A.


Programmatically determine whether a database was shut down cleanly or whether it required crash recovery There are various ways to shut down the SQL Server service (I can think of at least: SHUTDOWN, SHUTDOWN WITH NOWAIT, stopping the service (including bugs), killing the process, power loss).

An unclean database shutdown causes crash recovery to run when the server starts up again. I would like to track events when crash recovery was needed. I'd like to look into those cases.

How can I query a running SQL Server instance to see what databases underwent crash recovery on the last server startup (or the last time they were brought online)? In order to automate this the query must not be a manual procedure.

I did not find anything in sys.databases. In particular, is_cleanly_shutdown apparently does not represent what I'd like to find out.

Crash recovery will be used every time SQL Server is restarted. So I am assuming that when you take it offline cleanly means no transactions are rolled back. That information can be found in the error log. We can write code to read the error log based on timestamp and read the logs to see which database transactions were rolled back

EX:

Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2015-01-05 01:15:48.39 Server      Software Usage Metrics is disabled.
2015-01-05 01:15:48.39 spid4s      Starting up database 'master'.
2015-01-05 01:15:48.50 spid4s      140 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2015-01-05 01:15:48.50 Server      CLR version v4.0.30319 loaded.
2015-01-05 01:15:48.60 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2015-01-05 01:15:48.60 spid4s      0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.


SQL Server restore database files Say I'm using SQL Server and have set sa password in security. Can the database files be copied to another computer (running same SQL Server setup but with different sa password security) and restored as database of same name and get access to all the data?

would like to secure or prevent it from being accessed or copied (tables, views, data etc)

No, this is impossible and anybody telling you otherwise is selling you snake oil. What you want is called DRM and SQL Server cannot do DRM. At best you can prevent accidental media loss (ie. your IP ends up on a stolen laptop or on disk in a flea market and somebody simply reads the precious table and views definition) and that scenario is prevented with on-disk encryption (either TDE or BitLocker will do). But you cannot prevent an administrator from inspecting your database.


Postgresql 9 table lock resolution I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute?

From my limited understanding I think it's a table level lock that isn't releasing. The table pg_locks shows a single AccessExclusiveLock that has its granted column set to f. Running ps aux | grep postgres confirms that there is a postgres ALTER TABLE waiting.

If it helps the statement is one that's been created by the Django ORM (South in particular).

Thanks.

There is most probably another transaction open that did a select on that table.

Look for sessions that are " in transaction".

Make sure they end their transaction (so that the sessions shows "" or if you cannot do that, kill them with pg_terminate_backend()


A table with many records get slow to write and read I have a payment service which has the transactions table on MySQL along with other tables:

transactions
-------------
- user_id
- time
- amount
- card_id

Every minute hundreds of transactions occur and many users check their app for transaction history. Sometimes, it gets slow to read and write because of high high load.

What would be best approach to deal with this situation?
I am thinking to move transactions table from MySQL to NoSQL technology (MongoDB or Cassandra). Or can I solve this problem on MySql itself?

UPDATED

Transactions schema:

CREATE TABLE `transactions` (
  `id` int(10) NOT NULL AUTO_INCREMENT
  `userid` int(10) DEFAULT NULL
  `store_id` int(10) DEFAULT NULL
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `amount` decimal(20,2) DEFAULT NULL
  `note` varchar(255) DEFAULT NULL
  `status` int(3) DEFAULT NULL
  `error_mes` varchar(250) DEFAULT NULL
  `card_mask` varchar(100) DEFAULT NULL,
  `card_id` int(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tr_store_id_id_fk` (`store_id`)
) ENGINE=MyISAM AUTO_INCREMENT DEFAULT CHARSET=utf8;

Queries:

- select * from transactions where userid='xxxx'
- select * from transactions where userid='xxxx' and created_at between 'xxx' and 'xxx'
- select * from transactions where userid='xxxx' and card_id='xxx'
- select * from transactions where userid='xxxx' and store_id='xxx'

How can you have PRIMARY KEY(tran_id) without a column named tran_id???

Your queries need at least one index starting with user_id and 3 composite indexes, say:

INDEX(userid, created_at)
INDEX(card_id, userid)
INDEX(store_id, userid)

Surely most of the fields should be NOT NULL??

Bug:  INT(15) will not hold 15 digits; it will hold only 9 (signed 32-bit numbers).  Suggest DECIMAL(16,0) if you are storing credit card numbers.  But then, I would seriously advise hiring a security consultant -- you do not want to lose a laptop full of CC numbers; you will be in deep do-do.


Combining count queries into one table row I'm banging my head against the wall because I'm sure this is super easy.

I have a few queries set up that get counts for a few related tables.  This is for an arcade game tracking system.  The queries look at two tables and returns counts for how many of each game is in inventory

(SELECT Machine.MName, Count(Machine.Mid) AS CountOfMID
FROM Machine
GROUP BY Machine.MName
ORDER BY Machine.MName;)

and another for how many games are out for rent, again it counts the number of each game (multiple copies) that are out for rent:

(SELECT Machine.MNAME, Count(Rental.Mid) AS CountOfMID1
FROM Machine LEFT JOIN Rental ON Machine.[MID] = Rental.[MID]
GROUP BY Machine.MNAME;)

My question is how do I make a report that has the each game listed and then out to the side have the appropriate counts pulled in?  Something similar to this is what I'm after:

CATEGORY --- NAME ------ INVENTORY ------ AVAILABLE ------ RENTED
Video........Frogger.........2...............1..............1.....
.............Metroid.........5...............1..............4....
Pinball......Simpsons........10..............8..............2....

I'm currently putting all of my bounded controls in the MName HEADER of my report for grouping and I just need this last piece to put these counts in the same column.  I have tried using the control source for a bounded control to be a sql query just like I had in the actually saved query and it just pops up with a "enter parameter" box meaning I worded it wrong or something.

I'm wondering if I need to put the data in the "details" section of the report to tie each row in to the tables in my queries.  Not sure how to do any of this and I've tried everything and googled all night.

Any help would be greatly appreciated.  Pictures below:

Define Query objects (call them QRented and QInventory) with SQL definitions as you have provided:

(SELECT Machine.MNAME, Count(Rental.Mid) AS CountOfMID1
FROM Machine JOIN Rental ON Machine.[MID] = Rental.[MID]
GROUP BY Machine.MNAME;)

and this:

(SELECT Machine.MName, Count(Machine.Mid) AS CountOfMID
FROM Machine
GROUP BY Machine.MName
ORDER BY Machine.MName;)

Now create a combined query from your Query objects like this:

SELECT QInventory.MName, QInventory.CountOfMID as Inventory,
    QInventory.CountOfMID  - QRented.MIDI as Available,
    QRented.MIDI  as Rented
FROM QQInventory
LEFT JOIN QRented on QQInventory.MName = QRented.MName
ORDER BY Machine.MName;


Is there an option to prioritize a table while replication conflict occurs? I have a master slave configuration for my production stage environment. Sometimes I need to write to some tables of my stage server i.e slave server. In that case conflicts occurs as content from both master and slave differs. So is there any option that for some particular tables if conflict occurs I want to give priority to the content of the tables in slave server. So that it will not be overridden by the data from master server.

Note: I am very new to database and devops. So please bear me and let me know if it is a noob question.

No.

The Master and the Slave should always (aside from a minor delay) have identical data.  If they don't, you need to investigate and fix it.  Else, things could get worse and worse.

Percona Tools has a tool to discover whether there are differences between Master and Slave, plus a tool to fix them for you.  However, it assumes that the Master is the Master, not the Slave.

Is the Slave universally the "better"?  Then consider "failing over" -- That is, make the Slave the Master and the Master the Slave.  Now, your problem (as stated) goes away.

(Sorry, there are too many details to spell out details of all the steps you need in this Q&A.)


AWS RDS Showing CPU spikes on a MySQL system every 5 minutes -- how can I track the cause? I'm seeing a CPU spike every 5 minutes (though it's not FIXED on the 5 minutes, every so often it's 6 minutes between a spike, then 5, 5, 5 again).  None of my code runs on a 5 minute interval (it either runs statements EVERY minute, or every few HOURS).

This is what the graph looks like:

Is there any way I can analyze what is causing the CPU to spike like this?  Seeing a list of queries is good, but seeing their CPU impact is more what I'm after; a direct "A is causing B" way to dig down.

Does it exist?

See the slow query list in mysql. Using that you could track which one is the culprit and taking more time to respond.


Getting only 4 rows per idea from ideas table i have a table of ideas that stores responses from evaluators and stores as one row per evalautor
For ex : Evalautor 1 evaluated idea A then in table record gets added as
IDEA A, COMMENTS , EVALUATOR ID
as single idea have 4 evaluator response and i need to show data in this format
IDEA ID ||. EVALUATOR 1 || EVALUATOR 2|| EVALUATOR 3 || EVALUATOR 4
1 --           Comments --      Comments  --    Comments   --      Comments
Need assistance in creating logic to get information in format given above.
Query i am running :
SELECT * from Toy_ideas GROUP BY evaluator id
but getting results as
Ideaid -- comments -- evaluator id

As the comments mentioned, your question is lacking a lot of details. Specifically what is the criteria / logic you want to use to select 4 rows when idea with id = DC889 has 10 rows, for example?
In any case, this will get you a random set of 4 rows for idea DC889:
SELECT * -- Replace * with only the columns you need, as * is bad practice
FROM IdeaTable
WHERE id = 'DC889'
LIMIT 4;


How to use prepared statements within MySQL stored procedures? I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable to the LIMIT clause. I have this stored procedure here:

DROP PROCEDURE IF EXISTS fixbalance;
CREATE PROCEDURE fixbalance (userid INT)
  BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE balance INT DEFAULT 0;
  DECLARE idcnt INT;

  SET idcnt = (SELECT COALESCE(COUNT(id), 0)
               FROM coupon_operations
               WHERE user_id = userid);
  IF idcnt <> 0 THEN
    WHILE i <= idcnt DO
      BEGIN
        SET @iter = i;
        SET @user_id = userid;
        SET @sql = CONCAT('SELECT id AS curid
                           FROM coupon_operations
                           WHERE user_id = ?
                           ORDER BY id ASC
                           LIMIT ?, 1');
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @user_id, @iter;
        DEALLOCATE PREPARE stmt;
        SET balance = balance + (SELECT points
                                 FROM coupon_operations
                                 WHERE user_id = @user_id
                                 AND id = @curid);
        UPDATE coupon_operations SET balance = balance;
        SET i = i + 1;
      END;
    END WHILE;
  END IF;
END;
|

This does not work - I'm not sure how to pass the curid.

The solution was to SET the variable in the prepared statement itself as in:

SET @sql = CONCAT('SET @curid = SELECT id
                                FROM coupon_operations
                                WHERE user_id = ?
                                ORDER BY id ASC
                                LIMIT ?, 1');


Creating Excel files in SSIS based with a predefined format I need to create a statistical report in Excel and wondering if I can use SSIS to read data from the database (calculate the aggregated numbers) and load them into an Excel file with a specific layout. From what I can see, in SSIS 2008 R2, we don't have control on the target excel file, cell by cell, or perhaps I missed something. Is there any way to achieve it through SSIS?

Thanks for your help in advance.

If your SSIS package runs with the appropriate file system permissions, you can create a template Excel file with the desired formatting and copy that file to a new one (using a file system or script task) where the data will be exported. If the sheet will contain a variable number of cells that must be formatted, you can use conditional formatting in the template and have Excel apply the format based on actual column values or a formatting flag, say in a hidden column, which could be populated from the data.

See this article for an example of using a template. This article may also be helpful; it mentions an ingenious way to update specific cells.


System account locked, insufficient privileges to log in as sysdba I've researched the web but I could not found exactly the same situation than mine so I'm posting this message.

I'm connecting via Remote Desktop Connection to a remote server (Win2k3 Server). This server has installed (select * from v$version):

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 32-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Running both of these sentences returns error:

c:\> sqlplus
SQL> conn / as sysdba
Insufficient privileges
SQL> connect system/****
Account locked

The remote computer is where the db is installed, so I'm not connecting to a remote db. The windows user account belongs to Administrator and Ora_DBA Windows groups.

Db has only one user available (the one used by the application we develope), but it hasn't enough privileges to alter SYSTEM account and unlock it.

I have no idea what else can I do, because I have no way to connect to de db as an administrator. I hope I have provided enough data for my question, if I missed anything please ask.

EDIT: I've checked again Windows user membership: Administrators, ora_dba, Users and "Remote Workplace Users" (hope I've translated last one right).
PWD%ORACLE_SID%.ora file does not exist (suppose this is right).

EDIT2: I'm connecting to local db. I've found 3 installed SQLPLUS: one from Oracle Forms 6i (yeah, I know it's really old version), another one from Oracle 10 client, and the last one from Oracle 10 DB. I did not know about looking for ORACLE_HOME in HKLM/SOFTWARE/ORACLE, as I've read in a comment. In registry, all entries I've found point to Oracle Forms folder. I tried running sqlplus with full path, the sqlplus belonging to DB installation, and finally I've connected conn / as sysdba and I have run unlock account sentence, so problem is solved as my goal was to unlock system account.

The windows user account belongs to Administrator and Ora_DBA Windows
  groups.

Double check this, because you get the above error, when you are not a member of the ora_dba group.

Check %ORACLE_HOME%/network/admin/sqlnet.ora for the following line:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

If it is set to NONE, that disables logging in with / as sysdba, but would result in a ORA-01017: invalid username/password; logon denied error.

Finally, SYS password is stored outside the database, in a file, on Windows it is %ORACLE_HOME%/database/PWD%ORACLE_SID%.ora. You can recreate it (give new SYS password) as:

orapwd file=%ORACLE_HOME%/database/PWD%ORACLE_SID%.ora force=y

After that, you can should be able to log in with the given password as:

connect sys/password as sysdba


oracle rman connecting to a database in not mount state When connected to oracle database through rman directly from the terminal, everything works fine, but when the same connection string is executed through a shell script rman connects to the database in a no mount state, not sure why, using oralce 11g, oracle Linux, and the connection  string is :

rman target / catalog (userName)/(password)@//(IPADDRESS):(PORT)/SYNDEV

To connect to RMAN from the operating system command line and hide authentication information, you must first start RMAN and then perform either of the following actions:

Run the CONNECT commands at the RMAN prompt. ...
Run a command file at the RMAN prompt that contains the connection
information.


Relational vs Non-Relational Database for Events Database I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either database-type storage is the simple part. The deciding factor is the performance of the following queries:

Select events by location within a specific date range.
Select all events in a given location (city, town, etc.), sort by date.
Search for events by keyword, within a specific date range, within a specific location.

Events basically have ID, NAME, LOCATION, VENUE, START DATE, END DATE

In a relational schema I would have an EVENTS table, a DATES table for storing dates separately because events can occur on more than one date and they are repeatable, and a VENUES table from which the event location (country, city, etc) can be cross-referenced.

I have no experience with no-SQL databases, so if you vote for no-SQL please suggest how you see the "schema" being organized and which particular DB.

I hope this question is specific enough. Query performance is the deciding factor.

After further consideration, I realize the question more properly distills to the availability of date/time functions that can facilitate fast date range queries.  I know MySQL and PostgreSQL have such functions.  PostgreSQL is even looking a little better at this point in terms of syntax.  I don't know what NoSQL solutions have to offer regarding this.

I know the system can certainly be modelled comfortably in a relational database. I also am aware that each no-sql solution is different. I was wondering if anyone with any specific knowledge of a particular no-sql DB could cite why that particular DB is good for the solution.

This seems a little outside the scope of a StackExchange question.  However.....

NoSQL databases are, typically, build to resolve specific issues with the relational model.  The most common issue addressed is scalability.  However, because they're all designed to address different aspects of certain problems that some applications have with the relational model, there really isn't something you can say about all of them as a whole.  Maybe you need to handle terabytes of data?  Or maybe you need to handle a data schema that is very dynamic across the board and an EAV type schema would just kill performance?  Maybe you want a data store that cares about availability more than consistency (see CAP theorem)?  Each one is good at something that could be wildly different from what another is good at, while an RDBMS is a much more generalized database.

The answer I hear from people I trust on this topic is that if you don't already know why you need to abandon a traditional RDBMS for NoSQL, then you almost certainly should stick to an RDBMS.  There's a reason it took 30 years before people began to seriously consider data store models beyond the RDBMS.  If your queries are requiring features not available in MySQL/MariaDB, you may wish to consider another RDBMS that has a more robust feature set.  That could be PostgreSQL, or Oracle, or MS SQL Server.

Your proposed schema here already suits the relational model fairly well.  You may need a few junction tables for many-to-many relationships, but I don't think you necessarily will see many of those.  If you properly size the DB, properly index your tables, and analyze your common queries -- things you have to do with NoSQL, too -- I don't see that you'd have a huge problem.  You may even be able to get away with views for the most common queries.