Using an index for both asc and desc on a string column I know a trick to make order by queries faster when we want to order by asc and desc.
For integers store a negated value.
So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, -2, -1 and so the actual row will be sorted desc by that column (if e.g I show the non-negated version).
Question: How do I use this trick with strings?
How is a string "negated" or reversed so that the trick works for string columns as well?

Update
MySQL can not use an index for a query of the type:
ORDER BY col1 ASC col2 DESC
If the column I want to descent is an integer I can store the negated value and do the query as:
ORDER BY col1 ASC col2 ASC
In this case the index can be used and I will in fact get the value in descending order due to negation -11, -8, -7, 5, -2, -1 is ascending but if you remove the negation is it descending.
My problem is that I am not clear how I could apply this approach to a string column. Can I somehow get the arithmetical value of the string?

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction.

But in the case you describe, where you have a multi-column index and you want to order by one column ascending and the other column descending, that's a different story. The sort order must go in the same direction for both columns if it uses an index.

(Not that every sort has to use an index. You can certainly run the query and let it sort matching rows with a filesort.)

To reverse the sort order of strings, you could add your own custom collation. https://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

But you'd have to store your strings using that new collation, and rebuild your index. The index would only be useful for the reverse-sorting.

Re comment from @Cratylus:

A collation is what defines "A" as being before "B" and all the other ordering of letters. So if you define a collation that says that "B" is before "A", and use that collation in your column definition, then sorting on that column, or creating an index on the column, would be in the reverse order.

Note: the following doesn't create a proper collation, it just demonstrates the steps to create one.

Edit /usr/share/mysql/charsets/Index.xml and make this edit:

<charset name="latin1">

  . . . leave other collations alone . . .

  <collation name="latin1_reverse_ci" id="251">
    <order>Dutch</order>
    <order>English</order>
    <order>French</order>
    <order>German Duden</order>
    <order>Italian</order>
    <order>Latin</order>
    <order>Portuguese</order>
    <order>Spanish</order>
  </collation>

</charset>

Edit /usr/share/mysql/charsets/latin1.xml and make this edit:

<charset name="latin1">

. . . leave other collations alone . . .

<collation name="latin1_reverse_ci">
<map>
 AE B1 AC A2 A0 9E 9C 8D BE 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 97 B1 AC A2 A0 9E 9C 8D BD 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 FF FE FD FC FB FA F9 F8 F7 F6 F5 F4 F3 F2 F1 F0
 EF EE ED EC EB EA E9 E8 E7 E6 E5 E4 E3 E2 E1 E0
 DF DE DD DC DB DA D9 D8 D7 D6 D5 D4 D3 D2 D1 D0
 CF CE CD CC CB CA C9 C8 C7 C6 C5 C4 C3 C2 C1 C0
 BF BC BB BA B9 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 B8
 B7 B6 B5 B4 B3 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 40
 3F 3E 3D 3C 3B 3A 39 38 37 36 35 34 33 32 31 30
 2F 2E 2D 2C 2B 2A 29 28 27 26 25 24 23 22 21 20
 1F 1E 1D 1C 1B 1A 19 18 17 16 15 14 13 12 11 10
 0F 0E 0D 0C 0B 0A 09 08 07 06 05 04 03 02 01 00
</map>
</collation>

</charset>
. . .

Restart mysqld.
Confirm you have the new collation:

mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
. . .
| latin1_general_ci   | latin1  |  48 |         | Yes      |       1 |
| latin1_general_cs   | latin1  |  49 |         | Yes      |       1 |
| latin1_spanish_ci   | latin1  |  94 |         | Yes      |       1 |
| latin1_reverse_ci   | latin1  | 251 |         |          |       0 |
. . .

Create a table and fill it with some text:

mysql> CREATE TABLE foo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    t VARCHAR(60) COLLATE latin1_reverse_ci,
    x DATE,
    KEY(t)
);

mysql> INSERT INTO foo (t) VALUES ('Harry'), ('Ron'), ('Hermione');

Demonstrate that the sort order is the reverse of what you'd think it should be:

mysql> SELECT * FROM foo ORDER BY t ASC;
+----+----------+------+
| id | t        | x    |
+----+----------+------+
|  2 | Ron      | NULL |
|  3 | Hermione | NULL |
|  1 | Harry    | NULL |
+----+----------+------+

Confirm that it doesn't use a filesort:

mysql> EXPLAIN SELECT * FROM foo WHERE t LIKE 'H%' ORDER BY t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
   partitions: NULL
         type: range
possible_keys: t
          key: t
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

I had to put a condition on the rows, because otherwise it thought it was going to read all the rows in the table, and it did a table-scan and filesort.

Re comment from @ypercube:

It doesn't reverse the order of characters in a given string like REVERSE(), it reverses which characters are considered greater than or less than each other when comparing two strings. So no, the CHAR padding spaces don't matter, they're still at the end of both strings.

Example (no index this time, just using order by):

mysql> create table t (c char(20) collate latin1_reverse_ci);
mysql> insert into t values ('A'), ('B'), ('C');
mysql> select * from t order by c;
+------+
| c    |
+------+
| C    |
| B    |
| A    |
+------+

Edit: no, @ypercube is correct, this doesn't work with CHAR because the space character is in the wrong place when I naively reorder the collation simply by putting all the bytes in the reverse order. That's not the proper way to design a collation.

So this answer should serve as a demo of the mechanics of creating a collation, not as a proper collation for reversing alphabetical order.


Should I use mysql_upgrade after mysqld_safe when upgrading MariaDB? I'm wanting to know the best practice to do a MariaDB upgrade on opensuse.

I am getting this error:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db =
'a_database'': Cannot load from mysql.proc. The table is probably
corrupted (1548)

I'm assuming that an upgrade will fix the error.

So what is the best practice to do that?

When you upgrade versions, run the upgrade script.  Often there are new columns in various system tables.


Linked server from SQL server on AWS RDS to On premises MS SQL server database We are planing to migrate an on-premises SQL Server db to AWS RDS SQL Server. My only concern is how can I create a linked server from AWS RDS SQL Server to on-premises SQL Server db? Is it possible? I am checking it because there are many views using linked server to other on-premises dbs.

The Implement Linked Servers with Amazon RDS for Microsoft SQL Server doc says RDS supports below options:

SQL Server (EC2 or on-premises) to RDS SQL Server
RDS SQL Server to SQL Server (RDS, EC2, or on-premises)


Pausing the import of an SQL file in Postgresql? I am currently importing a large database (6 - 7 GB's) into my postgresql on Ubuntu. It's taking sometime to do. A problem has come up where I need to physically relocate the laptop the postgresql is installed on and will more than likely need to be done before the import operation finishes.

Is it possible to pause the import of the database?

If not, will putting the laptop into hibernate while I relocate it cause the import to freeze or fail?

I'm assuming based on the post that the PostgreSQL database server and the process restoring the dump are on the same machine. If so:

  Is it possible to pause the import of the database?

Not really. You could SIGSTOP the pg_restore or psql process and/or the corresponding postgres backend, but I wouldn't consider that my first-choice option.

  If not, will putting the laptop into hibernate while I relocate it cause the import to freeze or fail?

Since it's a loopback TCP connection or a unix socket connection when you're restoring to a local PostgreSQL instance, it doesn't care if the network changes or goes away. So suspend and resume is just fine.

BTW, 9GB is not large. 1TB is large.


Connecting Oracle 11g to Postgres via dblink I  have administrator access to our Oracle database , but was wondering if its possible to connect an Oracle 11g database to Postgres database and if so, what steps would I need to follow.

I've seen a few tutorials mention an ODBC driver that needs to be installed, but looks like this driver needs to be installed on the Oracle server itself?

Community Wiki answer collecting answers left in question comments

a_horse_with_no_name:

The ODBC driver needs to be on the computer that connects to the Oracle server not on the Oracle server itself. Plus: you can't use dblink for this, Postgres' DBLink can only connect to other Postgres servers. But you can use a foreign data wrapper. You only need the Oracle client libraries (not the ODBC driver) on the Postgres server, nothing needs to be installed on the Oracle server.

If you don't have administrator access to the Oracle database nor can you change anything in Postgres, then there is nothing you can do. Oracle's DBLink can only connect to other Oracle servers. You could install Oracle's "Heterogeneous Services" to be able to connect through ODBC (the Postgres ODBC driver, not the Oracle one) but that again would require that you have admin access to the Oracle database.

You need install Oracle's "Heterogeneous Services" then install the ODBC driver for Postgres, not the one for Oracle.

EdStevens:

It's more than just the ODBC driver.  You'll need to install the Oracle Transparent Gateway for ODBC.  The Oracle database sends a client request to the Oracle listener, that uses a gateway config file to further resolve the request to an ODBC DSN. The full instructions are pretty complex but I have an article outlining the pieces and their connections. That should give you a good overview and framework with which to understand the more detailed instructions.

You'll need both the Oracle gateway and the Postgres ODBC driver. The best link I could find was this:

Where to download DG4ODBC from?

Of course, when you get there, you'll want to adjust for your version.  For the ODBC driver, that would come from Postgres.


Redesign basic Mysql storage architecture We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this:

capture_table
----------
client_id
date
time
data1
...
datan
---------

The data is always queried by date.

After the tables were getting too big, we decided to use several tables to divide the data.
We created three PHP scripts that ran every few minutes dividing the original data into 3 tables (MyISAM) per month and creating an index table.

index_table
data_table_2014-01-1
data_table_2014-01-2
data_table_2014-01-3
...
...

Currently we have this structure and about 2.5 million of rows per table for our current number of clients, and growing (DB weights about 15GB).
The queries are done by joining the data from all the tables involved between two dates using UNION ALL, always searching for date and client. Example, search between 2014-01-01 and 2014-01-15:

(SELECT * FROM data_table_2014-01_1 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_2 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_3 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')

Is this the better approach? Could this be achieved in a better way? I was thinking of table partitioning for example...

Thanks a lot.

In my opinion, you should use an SSD if you are facing performance problems. Here is a 120 GB drive for approx. 64 Euro (< 100$ US). This will keep your application ticking over for a couple of years without any need for a redesign and performance should substantially improve for minimum expense.

Down the road, if you are ultimately deciding to move to a DW scenario, may I recommend PostgreSQL as your DW server over MySQL? MySQL is good for read-heavy web-facing apps, but not for DW type work. PostgreSQL has CHECK CONSTRAINTs, SET operators (INTERSECT, EXCEPT), CTEs (Common Table Expressions) and Windowing functions (all of which MySQL (incredibly) lacks).

Traditionally PostgreSQL was not strong on the replication, clustering and connection pooling front, but that has changed.


SQL Server Partitioning - what to use for partition key? I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance - eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.

Over a six month period we'll need to store up 360 million rows in the Coupon Instance table and up to 72 million (assuming max 20% redemption rate) in the Redemption table. I get the feeling that these numbers are too big for a single partition?

My question is - what to use as the partition key? One obvious candidate would be by issuance event, giving approximately 6 partitions. But then I think that maybe even that would give a partition size that is too large to allow for optimal performance? Would it be possible to partition by two keys eg by issuance event + last digit of the customer id? So the logic would be:

If issuance event = 1 and last digit of customer id < 5 then
    Store in partition 1
Else if issuance event = 1 and last digit of customer id >4 then
    Store in partition 2
Else if issuance event =2 and last digit of customer id <5 then
    Store in partition 3
Else if issuance event =2 and last digit of customer id >4 then
    Store in partition 4
Etc...

Also, I'm not sure of the spec of the database server that we'll need. Will 16gb and 8CPUs be enough? The db needs to be able to return a result from the coupon instance table, keyed on a numeric barcode value in less than half a second. The expected transaction request for validate (select) and redeem (insert) is expected to peak at approximately 3,500 per minute.

The SQL Server 2008r2 64bit db server will be provisioned as VM from a very powerful host with access to a high performance and large capacity SAN.

I'd be very grateful for any advice from those that have deployed a SQL Server solution to manage similar volumes.

Regards

Rob.

The server spec questions should be directed to either Serverfault or DBA.SE.

For the partitioning question, I don't think you necessarily need to partition for this.

360m rows is a lot but it's not too unwieldy.

Do NOT under any circumstances try to partition based on the last digit of a field.  I'm not sure this would even work, but it's not SARGable which wouldn't be tenable.

If you only need to do a single row seek based on a numeric key, partitioning probably will not help.

If you do decide to pursue the partition route, bear in mind to be effective all your queries need to include your partition key(s) so the engine knows which partition to check.  Otherwise it will check them all and you actually hurt performance.


Unable to remove a user in Oracle 11g I have a streams replication  related user in Oracle 11g. I see the user in dba_users table and was anle to login using the user but when I try to drop the user i get the error.

SQL> drop user <username> cascade;
Error at line 1:
ORA-1403: no data found.

Loos like data got corrupted in internal tables.

Any idea how to fix this mess and remove the user?

The ORA-01403 seems to indicate that you have a System Event Trigger for DROP USER.

You'll need to hunt down the appropriate trigger.

SELECT *
from dba_triggers
where base_object_type like 'DATABASE%'
and triggering_event like 'DROP%'
and status='ENABLED'
;

If that doesn't work, then I recommend that you open a ticket with Oracle Support.


SQL redundant databases both sets being modified independently yet staying concurrent I have a server in location 1. This location has slow external internet but all the employees. Location 2 has fast external internet but no people are there. I have a mobile app that I would like to connect to location 2 and have an instantaneous sync. between the two databases. What is the proper way to do this? If a field is updated at site 1 the server sends it to 2. If 2 is modified it sends it to site 1. The last change is always used.

SQL Server has various flavours of replication with different capabilities.

It sounds like peer-to-peer transactional replication best fits your needs.


How to Solve ERROR 1064 (42000)? DROP PROCEDURE IF EXISTS proc_x;
DELIMITER #
CREATE DEFINER=`root`@`localhost`  PROCEDURE proc_x(sequel CHAR)
proc_main:BEGIN
SET @SQL = CONCAT(@sequel);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END proc_main #

Above is a syntax for creating stored procedure in my mysql database.
When I execute following statement I get error.

CALL proc_x("INSERT  INTO tbl_rec(id,name,brnachID) values(10,'Krunal',07)");

  Error : ERROR 1064 (42000) at line 93: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Can anyone help me to solve it?

Currently I'm using Mysql on UNIX.

(sequel CHAR)
CONCAT(@sequel)

@variables are totally independent of non-@ variables.

Two fixes needed:

SET @SQL = CONCAT(@sequel); --> SET @SQL = sequel;
(sequel CHAR) --> (sequel VARCHAR(999))


How to speed up a query with a lot of subqueries I have a query which uses a series of functions to return the status of each document for  a  loan.

SELECT loan_number,
  borrower_name,
  get_application_status(loan_number, date_application_ordered, date_application_verified),
  get_appraisal_status(loan_number, date_appraisal_ordered, date_appraisal_verified),
  get_endorsement_status(loan_number, date_endorsement_ordered, date_endorsement_verified),
  get_waiver_status(loan_number, date_waiver_ordered, date_waiver_verified),
  get_insurance_status(loan_number, date_insurance_ordered, date_insurance_verified)
FROM loans_table
WHERE loan_type IN ('A', 'B', 'C')
AND filing_date > TO_DATE('30-OCT-12')
AND filing_date <= TO_DATE('13-NOV-12');

It returns several hundred rows and takes around 30 seconds. I suspect that it is taking so long because each function uses a subquery with a MAX function, like this:

FUNCTION get_waiver_status (loan_number IN NUMBER, date_ordered IN DATE,
                            date_verified IN DATE) RETURN varchar2
AS
last_upload_date DATE := NULL;
BEGIN
  IF date_ordered IS NULL THEN
    RETURN 'Not Ordered';
  END IF;
  SELECT MAX(upload_date) INTO last_upload_date
  FROM documents_table
  WHERE documents_table.loan_number = loan_number
  AND document_type = 'waiver';
  IF last_upload_date >= date_verified THEN
    RETURN 'Not Verified Since Last Upload';
  ELSE IF last_upload_date < date_verified THEN
    RETURN 'Verified';
  END IF;
END get_waiver_status;

How do I get the query to return faster? I have made indexes on loan_number and filing_date. I tried creating a trigger on documents_table to store the last upload_date of a document into a new table for the function to retrieve, but if a document is deleted, I need to be able to select the next latest upload_date from documents_table, which I can't do from within the trigger because I get a mutating table error. I am using PL/SQL on Oracle 10g R2.

I guess that using subqueries inside functions is the main performance problem.

It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only:

SELECT
  loan.loan_number,
  loan.borrower_name,
  ...
  CASE WHEN loan.date_waiver_ordered IS NULL
         THEN 'Not Ordered'
       WHEN  MAX(doc.upload_date) >= loan.date_waiver_verified
         THEN 'Not Verified Since Last Upload'
       WHEN  MAX(doc.upload_date) < loan.date_waiver_verified
         THEN 'Verified'
       ELSE NULL
  END  AS get_waiver_status,
  ...

FROM loans_table AS loan

  LEFT JOIN documents_table AS doc             --- joining
    ON  doc.loan_number = loan.loan_number
    AND doc.document_type = 'waiver'

WHERE loan.loan_type IN ('A', 'B', 'C')
  AND loan.filing_date >= TO_DATE('30-OCT-12')
  AND loan.filing_date <  TO_DATE('13-NOV-12')

GROUP BY
    loan.PK,                                   --- the Primary Key and
    loan.loan_type,                            --- all the other needed columns
    loan.filing_date,
    loan.loan_number,
    loan.borrower_name,
    loan.date_waiver_verified ;


How do you design schema for tracking change history in NoSQL? I'm thinking of trying a document store db for a project that needs to keep track of changes. I'd like to prototype with firebase, but I think the schema/implementation would be the same in MongoDb

The (simplified) setup is that I have three tables/collections Project, Asset and User. A (logged-in) User can change (Create, Update or Delete) a Project or an Asset. The Asset can be assigned to (maximum one) Project.

Any time the Asset or Project has any changes made, I'd like to capture the User that made the change, when they made it, and in most cases, the previous state.

Add three attributes to the project and asset documents - WrittenWhen, WrittenBy and IsActive. Populate these in the "real" data.

When a record changes copy it but with IsActive flag flipped. Retain the original values for WrittenWhen and WrittenBy. The original record is updated with the amending user's id and the time stamp. I do it this way around so any other record holding this record's magic id retains that pointer.

When reading only look for records with the right value of IsActive.

IsActive can be omitted if the archive records are written to a separate collection / namespace / database / whatever your technology supports.

  Thank you Michael, Couple concerns I have with this implementation; Size - this may have a lot of redundant data in it, possibly 100 or more times data than needed would be stored, with many deployments using mongodb this may not be an issue, but with the BaaS the like of firebase, this can make a big difference. My other concern is that it doesn't know which data point was edited unless you diff with previous, and with possibility of multiple users editing multiple data points, to show a "last updated by * on *" it may take a lookup of many older records – Daniel Mar 10 at 16:16

The points you raise are valid. An alternative design would be to store only the changed values in the history store. A record there would then be (primary key field(s), name of changed field, old value, new value, written by, written when). Storing only the parts that changed leads directly to two new problems.

One, you have a mandatory overhead of separating the changed parts for every single write, as opposed to deferring the diff costs until needed. Typically we want OLTP writes to be fast and audit reporting is not performance critical. So it would seem reasonable to me to defer that work to when the report is required. Of course the cost of writing the whole record will be more than of writing the shorter diff-only record. You can benchmark your infrastructure to see if that is significant.

Two, by storing only deltas you loose the data context in which a change occured. By this I mean by only storing deltas we can read a single audit record to see that, say, field 53 changed from "A" to "B". What values did all the other fields have when that change occurred? To find out you have to read backwards through history until you find the most recent value for the other fields in which you're interested, potentially all the way to the "live" record. Again, I don't know if this is a valid use case for your scenario, or if change frequency and audit read frequency would make it a problem, but it is something worth considering.

To show "last updated by .. on .. " with either scheme is a single record lookup of the last history record written because both ways store this information on every history record. To find what changed with the full record stored will require a second read and a diff, which is not needed with a delta store. To reconstruct a historical record will be light work with full records and processing-heavy with deltas.

I don't know your requirements or constraints well enough to fully evaluate one design over the other. My preference would be for the one I documented due to ease of implementation and (likely) performance. I will note, however, that there are no free lunches. The work has to be done somewhere, sometime.


How to reproduce "Could not continue scan with NOLOCK due to data movement" I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries.

I understand this has something to do with attempting to select data when there has been a page split which caused the data to no longer be where it was supposed to be - I assume that's what's happening in my environment.

How would I reproduce this?

I'm trying to do a short-term workaround to catch the error and retry when this happens, but I can't test it if I can't reproduce it. Is there a reasonably reliable way to cause this?

When it does happen, executing the query again results in success - so I don't really have any concerns about the actual data or database being permanently corrupt. Some of the tables in the query (along with their indexes) get dropped, recreated, and repopulated often, so I'm assuming it's something related to that.

Removing NOLOCK is my long-term issue to deal with. The reason NOLOCK was put there in the first place was that the queries are so bad that they were deadlocking with day-to-day transactions, so NOLOCK was a band-aid to stop the deadlocks (which worked). So I need a band-aid on a band-aid until we can do a permanent solution.

If I could reproduce it with a Hello World, I would plan on probably slapping the band-aid into the job in less than an hour. Can't do a search-and-replace removing NOLOCK, because I'd start getting the app deadlocks again, which are worse for me than an occasional failing job.

Using read committed snapshot isolation is a good possibility - I'll have to work with our database team to get more details about that. Part of our problem is that we don't have a SQL Server expert to deal with that kind of thing, and I don't understand the isolation levels well enough to make that change right now.

Since one potential 'band aid' to the NOLOCK issues is to stop using NOLOCK and begin using READ_COMMITTED_SNAPSHOT isolation, I want to point you to the blog post at http://www.brentozar.com by Kendra Little: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide.

Kendra provides a fair amount of detail on benefits and risks with using the READ_COMMITTED_SNAPSHOT isolation level.

This isolation level becomes the default isolation level for the database code.
You have to have only one user in the database in order to make the change to the READ_COMMITTED_SNAPSHOT isolation level.
Even if you use READ_COMMITTED_SNAPSHOT isolation you will still need to remove the NOLOCK hints since they override the default.
Some of your code may well have problems that need curing.

Some years ago we implemented READ_COMMITTED_SNAPSHOT isolation on a database that was severely suffering from blocking.  But once we changed the isolation level we began getting deadlocks in a couple of critical areas.

Why did this happen?  Because the previous isolation level caused heavy blocking, the code could "never" reach the point of deadlocking.  However, with
READ_COMMITTED_SNAPSHOT isolation, the queries could keep moving forward. However, some percentage of the no-longer waiting transactions began deadlocking.

Fortunately our case was resolved quickly by determining the deadlock points and adjusting the indexes on a couple of tables to have a more rational column order.  This greatly reduced our locking problems.


Methods for finding new Trace Flags in SQL Server There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, Microsoft employees, etc., what are ways to find new trace flags?

I've read through a couple recent posts by Aaron Bertrand here and here, but didn't spot anything about new Trace Flags.

I copied the data and log file of mssqlsystemresource to a new location, and attached it like a regular database to poke through system tables and views, but didn't spot anything immediately. I considered taking a list of known Trace Flags, and looping through numbers not on that list, to see which ones DBCC TRACEON would allow, but wanted to ask the question here first.

Assuming that the DBCC command to enable them has to check in with some resource to make sure the Trace Flag is valid, where does it reach out to? Is there a .dll or some other system file that holds a list?

I know the question casts a wide net, but what spurred this was reading about a Trace Flag with specific intended behavior alongside a new feature in 2016 that was not having the described effect. My initial thought was that perhaps the numbers were transposed somehow, like 7129 becoming 7219. I was hoping to get a list of valid trace flags within a range, say 7000-7999, to look for permutations. Testing them all, both as DBCC TRACEON flags and startup parameters would be quite a nuisance, combined with testing the results against the feature behavior.

There's nothing you can do to find the list except ask or pick them up from posts/slide decks/etc. The list only exists in the code, in a header file where the valid trace flag numbers are mapped to names in a big enum in the C++ code, and then the names are used in the rest of the code.

As Aaron said, you can enable any trace flag number, and if it doesn't do anything, or you don't exercise the functionality that the trace flag is relevant to, you won't notice any difference in behavior.

DBCC TRACEON doesn't check anything - as there's no run-time list of which numbers are valid or not - it just enables that trace flag number in a bitmap of what flags are set for that connection/globally.

The problem with having a validity check is that would expose which trace flags are valid, allowing them to be discovered. This way the 'valid list' is effectively obfuscated, which is what the SQL team wants.

Regarding Kin's suggestion in a comment that SQL Server should have select * from sys.available_trace_flags - Yes and no. There are many trace flags that are highly detrimental to performance and are only necessary for debugging problems under guidance from Product Support, but SQL Server could list the 'safe' flags.


Is it wise to drop fixed-length columns to free up space? I have a huge table in SQL Server 2005. (The table is about 40gb)  I am looking for the safest and most pain-free way to either free up space, or delay its growth.

The table has three fixed length (char(10)) columns that are a) completely unneeded and b) almost totally filled with NULL.

If I drop these columns will SQL Server start using the freed space within the data file when new records are imported?  Or does their fragmented nature (I assume) because of being spread out over many records mean that SQL Server will ignore the space for performance reasons?

If I do drop them, can I run shrink database to free up the space?

I'm the only developer who utilizes this database, and I know these columns are surplus to requirements.  They were added along with every other column in a flat file 15 years ago when different lazy developers created this database.

Dropping them will reclaim space if you rebuild indexes or run DBCC CLEANTABLE
New rows will not have these columns
Fixed length columns always have space reserved whether NULL or not (note: variable length columns with NULL don't consume space usually)
Don't shrink the database, it will only grow again and has other issues (out of scope here)
40GB is not big

Note: an index rebuild to reclaim space assumes you have a clustered index


Mysql - Examine last query run by thread in sleep state I am seeing some threads in the "sleep" state for longer than normal. Normal maybe being 1-3 seconds with the abnormal being up to 60 seconds or longer. It makes sense that this is an application issue so my question is if it's possible to see the last query run by a thread that is in a sleep state. From there I can hopefully track down the code that is leaving these connections open.

First you have to set general logs of mysql by add lines in /etc/mysql/my.cnf 's mysqld section.

general_log     = 1
general_log_file        = /var/log/mysql/mysql.log

Restart mysql -

sudo service mysql restart

If you don't want to restart mysql server-

SET global log_output = 'FILE';
SET global general_log_file='/var/log/mysql/mysql.log';
SET global general_log = 1;

If you wants to read logs from table, mysql database have a general_log table which insert all logs. you can get by-

   SET global log_output = 'table';
   SET global general_log = 1;
   SELECT * FROM mysql.general_log;

and you can disable by -

SET global general_log = 0;

Now you can see those process which are in sleep state & get its Id-

show full processlist;

open new tab and see the logs of ID & its running command-

cat /var/log/mysql/mysql.log | grep ID_value

or if you read logs from table then-

 SELECT * FROM mysql.general_log where thread_id=1;

Be careful to use it on Production server, it may effect your server performance (due to additional disk write) if transactions committing on huge no. per second.


Can I speed up a large MySQL/MariaDB join through partitioning or merging? I'm trying to run the following join query as part of a more complex one on MariaDB 10.1.26.

select distinct
    project_commits.project_id,
    date_format(created_at, '%x%v1') as week_commit
    from project_commits
    left join commits
    on project_commits.commit_id = commits.id;

Both join fields are indexed. However, the join involves a full scan of project_commits and an index lookup on commits.  This is corroborated by the output of EXPLAIN.

+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+
| id   | select_type | table           | type   | possible_keys | key     | key_len | ref                                 | rows       | Extra           |
+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+
|    1 | SIMPLE      | project_commits | ALL    | NULL          | NULL    | NULL    | NULL                                | 5417294109 | Using temporary |
|    1 | SIMPLE      | commits         | eq_ref | PRIMARY       | PRIMARY | 4       | ghtorrent.project_commits.commit_id |          1 |                 |
+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+

The sizes of the two tables are relatively large: project_commits contains 5 billion rows and commits 847 million rows. Also the server's memory size is relatively small (16GB).  This probably means that index lookups hit the (unfortunately magnetic) disk, and therefore performance takes a heavy hit.  According to the output of pmonitor run on the generated temporary table, the query, which has already run for more than half a day, will take another 373 hours to complete.

/home/mysql/ghtorrent/project_commits#P#p0.MYD 6.68% ETA 373:38:11

Could I somehow increase the query's performance either by partitioning the tables, so that the join can be performed in memory, or by forcing MySQL to perform a sort-merge join? As the time involved for running alternative strategies could be many hours, I'd rather have a suggestion, instead of trying things out.

From the looks of the EXPLAIN plan, you are doing a full table scan on project_commits.

From the looks of the query, I surmise there is a one-to-many relationship from commits to project_commits. The problem I see is that your query is gathering data as many-on-one.

You are also using LEFT JOIN.

Your query is saying:

  Show me all project_commits that are connected to or orphaned from commits

Instead, you might want the query to say:

  Show me all project_commits that are connected to commits

SUGGESTION #1 : Flip Table Order

EXPLAIN select distinct
    project_commits.project_id,
    date_format(created_at, '%x%v1') as week_commit
    from commits
    left join project_commits
    on commits.id = project_commits.commit_id;

SUGGESTION #2 : Use INNER JOIN

EXPLAIN select distinct
    project_commits.project_id,
    date_format(created_at, '%x%v1') as week_commit
    from project_commits
    inner join commits
    on project_commits.commit_id = commits.id;

SUGGESTION #3 : Add created_at index

If you are already have an index on created_at or if you already have a compound index whose first column is created_at, skip this suggestion.

ALTER TABLE `project_commits` ADD INDEX created_at_ndx (`created_at`);

Adding an index will change EXPLAIN plan to do an index scan instead of a table scan

SUGGESTION #4 : Alter the JOIN behavior

You could manipulate the style of the join operation by tweeking the optimizer

According to MySQL Documentation on Block Nested-Loop and Batched Key Access Joins

  When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

  For BKA to be used, the batched_key_access flag of the optimizer_switch system variable must be set to on. BKA uses MRR, so the mrr flag must also be on. Currently, the cost estimation for MRR is too pessimistic. Hence, it is also necessary for mrr_cost_based to be off for BKA to be used.

This same page recommends doing this:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

GIVE IT A TRY !!!

Note : I have no idea what to expect from my suggestions. After all, your LEFT JOIN is like an iterative Cartesian Join with the potential of make a temp table that is the following

  4.573 Quintillion rows (5.4 Billion X 0.847 Billion) being looked up

Have fun and let us know what you find


Extract new lines from log file to MYSQL everyone.
I am new here, I was recommended by Onare, welp, this will be my first question.
I have a file for ex. "file.log" that contains printing logs.

LINE1: PRINTERNAME | userwhichprinted | ID | DATE | 1 | 1 | - | FROMIP | FILENAME | - | -
LINE2: PRINTERNAME | userwhichprinted | ID | DATE | 1 | 1 | - | FROMIP | FILENAME | - | -
LINE3: PRINTERNAME | userwhichprinted | ID | DATE | 1 | 1 | - | FROMIP | FILENAME | - | -

And so on, actually it is a CUPS Server, someone may be familiarized with it.
I want to make a trigger that put every new line to the database. If there are any more questions needed, let me know, thank you in advance.

Perhaps something like the following is what you are looking for?

LOAD DATA LOCAL INFILE '/path/to/raw/data/file.log'
INTO TABLE TABLE_NAME
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;


Print table documentation with comments? I have a MySQL database, and I have used MySQL Workbench to insert text into the "comment" fields of many of the columns and tables; the plan was to have the documentation stored in the database.

However, I do not know how to either a) add a comment to a column or, more importantly b) retrieve this information.

I have looked through the limited MySQL documentation on Describe, Show Columns, and Show Extensions.

For example, I would like to return this:

mysql> describe dogs;

+----------------+-------------------------------+
| Field          | Comment                       |
+----------------+-------------------------------+
| id             | primary key                   |
| Name           | Dog's name                    |
| species_id     | Foreign key to look up species|

Instead of

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| name           | varchar (255)| YES  | MUL | NULL    |                |
| species_id     | int(11)      | YES  | MUL | NULL    |                |

Although this would do:

mysql> describe dogs;

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| Field          | Type         | Null | Key | Default | Extra          | Comment                       |
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |                               |
| name           | varchar (255)| YES  | MUL | NULL    |                | Dog's name                    |
| species_id     | int(11)      | YES  | MUL | NULL    |                | Foreign key to look up species|

What are my options? Does MySQL workbench export such documentation?

You can do this with normal DML/DDL.

Create a table with comments:

mysql> create table testcomments (
    -> col1 integer comment 'comment on integer column',
    -> col2 varchar(20) comment 'comment on varchat col');
Query OK, 0 rows affected (0.01 sec)

Select the information from the data dictionary (documentation link):

mysql> select column_name, column_comment
    -> from INFORMATION_SCHEMA.COLUMNS
    -> where TABLE_NAME = 'testcomments';
+-------------+---------------------------+
| column_name | column_comment            |
+-------------+---------------------------+
| col1        | comment on integer column |
| col2        | comment on varchat col    |
+-------------+---------------------------+
2 rows in set (0.00 sec)

mysql>

Add/modify a comment:

mysql> alter table testcomments
    -> change column col1
    -> col1 integer comment 'modified comment on integer column';

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select column_name, column_comment  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'testcomments';
+-------------+------------------------------------+
| column_name | column_comment                     |
+-------------+------------------------------------+
| col1        | modified comment on integer column |
| col2        | comment on varchat col             |
+-------------+------------------------------------+
2 rows in set (0.00 sec)

mysql>

Modify a table comment:

mysql> alter table testcomments comment 'foo';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

View a table comment:

mysql> select TABLE_COMMENT
    -> from INFORMATION_SCHEMA.TABLES
    -> where TABLE_NAME = 'testcomments';
+---------------+
| TABLE_COMMENT |
+---------------+
| foo           |
+---------------+
1 row in set (0.00 sec)

mysql>


Under what circumstances can a division expression reduce the number of rows in a query's result? I'm experiencing a problem with a query that I've written.

Given a table with the schema:

create table somevals(division varchar(100),
                      subdivision varchar(100),
                      good float,
                      bad float);

I would like to find, for each unique (division, subdivision), the sum of good values, the sum of bad values, and the ratio of good to bad values. So, I wrote the query:

select division, subdivision, g, b, g / b as ratio
from (select division, subdivision, sum(good) as g, sum(bad) as b
      from somevals
      group by division, subdivision
     ) as temp

The problem is that this returns far fewer rows than I would expect. However, if I remove the division (g / b as ratio), this query returns the correct number of rows in the result (about three times as many). None of the columns contain Null values. None of the bad values are less than or equal to zero, so sum(bad) will always be non-zero.

I am at a loss for how this could be happening. Under what circumstances can adding a division expression in the select statement reduce the number of rows in the result?

You simply cannot change the number of rows returned by changing the SELECT clause.  The problem must lie elsewhere.

If you're absolutely sure that's what's happening, I would run DBCC on the table to make sure it's not corrupted.


optimize my query mysql I have the following query:

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId
WHERE d.castName LIKE '%castname%'

for some names it works well, but for some other it takes a long time to retrieve results..

film_info is a view containing 100,000 rows.
The structure is: movieName, ImdbId, year, rate, posterLink(link to image of movie)

director_movie: is a table containing 2,259,630 rows.
The structure is: Id(unique), castName, castImdbId, movieName, movieImdbId

The goal of this query join is to retrieve list of movies by each director.
I defined castName and movieImdbId as indexes (in order to speed up joins and read from table), but actually I am a bit confused about how to chose an appropriate index. (If I have to use it in my case..). I read one of the factor to see if index is useful is cardinality which I am also confused with this term.. because it change when I add another index to my table..
I tried different combinations of columns for indexes but none of them speed up my query results..

Could someone kindly explain me what is the best way to improve the performance of my query (in terms of time)?

Thanks in advance

This is your original query

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId
WHERE d.castName LIKE '%castname%'

There are three things you can do

SUGGESTION #1

You should reorganize the query so that the castName is searched first

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId;

SUGGESTION #2

Run these please

SELECT COUNT(1) WithDistinctCount FROM
(SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId) A;
SELECT COUNT(1) WithoutDistinctCount FROM
(SELECT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId) A;

If WithDistinctCount and WithoutDistinctCount are the same, then remove DISTINCT. It may actually return the results faster.

SUGGESTION #3 (Optional)

If you implement my FULLTEXT suggestion (Joining two Mysql VIEWs takes a very long time), you could redo subquery d

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE MATCH(castName) AGAINST ('+castname' IN BOOLEAN MODE)) d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId;

This requires you create a FULLTEXT index on director_movie

ALTER TABLE director_movie ADD FULLTEXT castname_ft_ndx (castname);

GIVE IT A TRY !!!


Best practices for Feed restructuring in a social app database I have a social app, where users can post to the school they chose on registration. Each post can have a hashtag and a school (all pointers, (hashtag is optional, school is not)).

Users see posts based on school or hashtag. Now we want to introduce a new feature, Polls. Polls are another type of post... Users will be seeing them in their feed among other posts! Besides other properties, they too have a hashtag and a school.

Right now when a user asks for posts, I'm just making a query to the Post collection, comparing only the school or hashtag field (depending on the screen they're at). So if they're on the Home screen, I'm going to have something like:

  find all documents in Post, where school equals <some_value>

And if they are on the Hashtag screen, we have something like:

  find all documents in Post, where hashtag equals <some_value>

Since we are going to have the new Poll collection, I'm wondering what implementation would be best... Here's what I have so far:

1st implementation

We keep the posts query as is and we add another one, pretty much the same, for the polls and we combine the results. Have in mind here, that our app fetches data with pagination, so we bring the posts 20-by-20. I'm saying this because in case we have a page and the responses of the 2 queries bring 20 documents each, we'll have to compare dates to know what the response should be... eg 16 posts and 4 polls.

2nd (and last) implementation

We create a new collection, Feed, which would combine both collections and it would look like this:

Feed

post: <Pointer to Post>
poll: <Pointer to Poll>
creator: <Pointer to User> // we need this for block checking or if you open your profile
school: <Pointer to School>
hashtag: <Pointer to Hashtag>

Every time a post or poll will be created, their triggers will create a Feed document, with all the necessary values.

I believe the second solution is the best, as it is much cleaner than the first. I also think they're pretty much the same complexity-wise.

Any ideas on which one is better? If you have any other implementation to suggest, please do so!

NOTE: All collections are indexed.

Real mongo-design centers around the queries and only the queries. From the database perspective, there is no necessity to separate the document types. From the query perspective, I get the impression that you basically need a single collection, which contains all documents. If you need to distinguish between posts and polls, do this via a field in the document.

Also get rid of this "pointer to user" stuff. Put a copy of the necessary user fields into the respective document, and use this to display whatever you need.

The ultimate goal is, to display your complete page with a single query, without using the aggregation framework (which is slow and only a mediocre workaround for bad database design.)

And get these relational concepts out of your head. Use redundancy, optimize for the reading query. If a user changes, well yes, you'll have to update a thousand documents - but this is not the normal case, so you optimize the read and do more work on the write.

In a comment, you asked:

  What if I still created a new collection Feed, but instead of pointers, I'll use copies of documents? (without every field, only selected ones) Could that somehow slow things down or not be as fast/good as just integrating the polls inside the Post collection?

There's nothing wrong with duplicating the necessary data for a given query in a separate collection like your "Feed" collection. But "having too many fields" is once more a relational thought.

You don't have any fields in a collection. You have documents, and these documents have a number of fields individually.

There is no negative effect when you have 100 document types which sum up to 10000 different fields. There may be a technical drawback if you start to create many indexes, but apart from that you may well dump everything in a single collection.


Can't create indexes on really large table! I have a table with over 300 Million rows in it.  When I try to create an index on the table I get the error message below.

  Msg 1105, Level 17, State 2, Line 3 Could not allocate space for
  object 'dbo.SORT temporary run storage:  422212472471552' in database
  'MSD' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup.

When I look at the files, the one using the PRIMARY file group is set to grow by 10% and is unrestricted for max size.  My hard drive still has 500 gigs of space left on it.  Does anyone know why I am hitting this problem and how I can get around it?

One thing you can try is to temporarily set the recovery model to Simple.

Alternatively, you can tell the create index command to use tempdb.  Since tempdb is typically in the Simple recovery mode.

CREATE INDEX ...
WITH (SORT_IN_TEMPDB = ON);


Setting up a root user password I'm newbie in MySQL. I installed MySQL5.5 Server and Client in my Debian Wheezy. All I did was enter aptitude install myqsl-server. Now, I have no idea what to do!

I found a webpage that says I have to set up a root user password first, and, to do that, I have to enter with the following command:

mysqladmin -u root password NEWPASSWORD

Where "NEWPASSWORD" is, well, the new password. The rest are literally commands. So, I opened my terminal and as normal user, tried to enter with this command and I got this output:

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

So, considering all I did, what are the next steps? I have to create a root user password, but for what? And after that what do I have to do to create a simple table representing data?

All tutorials I read on the internet are very difficult to understand!

It seems that your server are down, you should start your mysql server first.


Why SQL Server Migration Assistant keeps crashing on the 'Migrate Data' step? I'm trying to migrate a MySQL database to SQL Server using the SQL Server Migration Assistant for MySQL. Converting the schema and synchronizing with SQL Server works fine.

However, after clicking the Migrate Data button, SSMA crashes after a few seconds (SSMA has stopped working). What might be the cause of this problem?

One more thing that helped me in addition to setting affinity to Core 0 only:

SSMA stopped crashing after I set Parallel Data Migration Thread Count to 1.
You can find it in Tools > Project settings > Genaral > Migration (in the left pane) > Parallel Data Migration. Set Parallel data migration mode to Custom and then Thread count to 1.

My current version is 7.0.0, MySQL ODBC Driver version is 5.3.6.


MS SQL Back up not restore the MS SQL 2014? I am new comer to MS SQL 2014. I will try restore db back up. but given following error. expect some expert help.

  TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'MSSQL_MED' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)

------------------------------

Database 'MSSQL_MED' cannot be started in this edition of SQL Server because part or all of object 'Answer_statusComponent_signings_History' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'MSSQL_MED' cannot be started in this edition of SQL Server because it contains a partition function 'archive_applog_pf'. Only Enterprise edition of SQL Server supports partitioning.
Database 'MSSQL_MED' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

-------version info-------------------

Microsoft SQL Server Management Studio                      12.0.2000.8
Microsoft Analysis Services Client Tools                        12.0.2000.8
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                     3.0 6.0
Microsoft Internet Explorer                     9.11.14393.0
Microsoft .NET Framework                        4.0.30319.42000
Operating System                        6.3.14393

You are trying to restore a database that was backed up from a version of sql server that's Enterprise and is using Enterprise features like data compression - my guess is you are trying to restore this backup to either Standard or Express which don't support those Enterprise features until Sql 2016 SP1 - if possible, upgrade your 2014 edition to 2016 SP1.

The steps involved in upgrading are too numerous for this Q/A environment.

If you decide to upgrade, check the information in this link Upgrade SQL Server.  Additional information can be found by searching for

  upgrade sql 2014 to 2016

Sql 2016 SP1 has a number of features available in ALL Sql Server editions (Standard, Express, etc.) that used to be only available in Enterprise edition.  For more information on those new features, please check

SQL Server 2016 Service Pack 1 (SP1) released

Upgrading from one release of Sql Server to another needs planning and testing to prevent unexpected surprises.

Instead of upgrading the current Sql Server 2014 environment that you are trying to restore to, you might be better off installing a brand new Sql Server 2016 Express instance and applying SP1 to that new install.  Then restore your database to that new instance.  Be aware that Sql Server 2016 Express edition can only accommodate database sizes of 10GB, so depending on the size of the database you are trying to restore - that may limit your ability to use Sql Server Express


Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server I am working on a report which will give information for different segments like

blocking process
slow running queries
disk space etc.

I also want to add one more segment where I can get information related to SQL Server index fragmentation.

Is there anyway to find information for Index Fragmentation and statistics in SQL Server with the help of system tables?

Is there anyway to find information for Index Fragmentation and statistics in SQL Server with the help of system tables?

The more appropriate way of finding fragmentation details for SQL Server is by using DMV sys.dm_db_index_physical_stats. Please note since DMV's came from SQL Server 2005 onward below script will work for 2005 onward only

select
                    o.name AS objectName,
                    i.name AS indexName,
     s.name as Schemaname
                ,p.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL , NULL, null) AS p
                    INNER JOIN sys.objects as o
                        ON p.object_id = o.object_id
                    INNER JOIN sys.schemas as s
                        ON s.schema_id = o.schema_id
                    INNER JOIN sys.indexes i
                        ON p.object_id = i.object_id
                        AND i.index_id = p.index_id
                WHERE p.page_count > 2000 and p.avg_fragmentation_in_percent >5
                AND p.index_id > 0 and s.name <> 'sys'

For statistics you also have sys.dm_db_stats_properties IF you are using SQL Server 2008 R2 Sp2 and above you can use below script. Source

-- Script - Find Details for Statistics of Whole Database
-- (c) Pinal Dave
-- Download Script from - https://blog.sqlauthority.com/contact-me/sign-up/
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
dsp.modification_counter,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
ORDER BY DaysOld;


Where can I download oracle 10g software for solaris? I need 10g RAC software (Solaris 11 SPARC) for an audit.

It's no longer on OTN.
I've opened an SR, but it is taking forever then they gave me the wrong O/S.

Do you know of any other place to get it?


Scripting all of available databases to .sql files containing schema+data+users with single command I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance.

As i'm told it is better to script the databases and run the script on the new instance.

I can do this in 2 ways:
1. Running a script on the 2008 server to get all of users and passwords and user mapping to databases and then Script all of the databases (without users information) and then restore databases on new instance and then run the users script for logings and users mapping to databases. i have been told it is a better way

-2: Script all of the databases using Script to WIZARD and check-mark DATA+SCHEMA+USERS and then script the database to .SQL file and run this SQL script on the new instance.

What is your suggestions? Which is the best way to this without facing any issue.
Kind regards

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use

EXEC sp_change_users_login 'Auto_Fix', 'username'

for fixing user accounts.

The Wizard approach sounds doable.

If you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier versions of SQL Server still works with 2008. The following will connect to a local SQL Server instance and create a DDL script for my_database:

scptxfr /s .\SQLExpress /d my_database /I /f database_script.sql /O /H

Without programming, it is going to be difficult to get all the data out in scripts.


Can scheduled and continous replication configurations exist side-by-side on the same primary/secondary servers? Environment
We have a core sql server cluster. This cluster contains some databases that get replicated to a load-balanced sql cluster of currently 3 servers. These databases are replicated each 12 hours but will eventually be replicated every 4 hours.
Requirement
On this cluster a new database is created and we need this database to be replicated asap to the load-balanced sql cluster. A delay of seconds or minutes is allowed and writes to this database are currently and in the future low (a few per hour).
Questions
Can two different replication plans coexist side-by-side on the same environment?
Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?
Does this create a high risk for a large existing scheduled replication job?
Our DBA says that this replication scenario creates a high risk for the existing replication configuration (2x a day).
My brainwaves
I can't imagine that this minor write activity with continuous transaction replication can create issues for the large existing replication job. I can imagine the other way around that our continuous replication will suffer twice a day due to the large replication job. We are perfectly fine with that as replication is required ASAP during regular operation.

Can two different replication plans coexist side-by-side on the same environment?

Yes, you can create many publishers for the same articles that replicates to different subscribers.

  Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?

Yes, if you are replicating a new database or subset of data to other server, it should be fine.

You need to think about if the distributor server is same on the publisher server or you are having a different distribution server.

When distributor and publishers share the same server instance, then it is highly likely that you will hit a performance bottleneck, depending on how much data you are replicating (all data vs subset of data) and your network latency between your publisher databases and subscriber databases.

  Does this create a high risk for a large existing scheduled replication job? Our DBA says that this replication scenario creates a high risk for the existing replication configuration (2x a day).

As I mentioned in above answer, it depends on how much data you are replicating and the amount of transactions happening on publisher database. Better to follow your DBA recommendations if he/she has proper stats or baseline that will show the current behavior.


Update values in one table based on contents of another Software Name: Oracle SQL Developer Version 17.2.0.188

I have three tables: orders, order_details, and products.

I wanted to run a query to update units_in_stock in the products table based on quantity in order_details, where the associated orders row has been marked as shipped, and has a shipping date.

Here's what I tried:

UPDATE Products SET (Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity)
INNER JOIN (
    Orders INNER JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID
    ) ON Products.Product_ID = Order_Details.Product_ID
WHERE (
        ((Orders.Shipped_Date) IS NOT NULL)
        AND ((Orders.This_Order_Was_Shipped) = "YES")
        );

When I run this, I get:

Error at Command Line : 1 Column : 45

Line 1, Column 45 is the equal sign in the very first statement that said

Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity

Can you help me get this working?

Looks like this should do it for Oracle:

UPDATE Products
SET Products.Unit_In_Stock = Products.Unit_In_Stock -
(
    SELECT Order_Details.Quantity
    FROM Orders
        INNER JOIN Order_Details
        ON Orders.Order_ID = Order_Details.Order_ID
    WHERE Products.Product_ID = Order_Details.Product_ID
    AND (Orders.Shipped_Date IS NOT NULL)
    AND (Orders.This_Order_Was_Shipped = "YES")
)
WHERE EXISTS
(
    SELECT Order_Details.Quantity
    FROM Orders
        INNER JOIN Order_Details
        ON Orders.Order_ID = Order_Details.Order_ID
    WHERE Products.Product_ID = Order_Details.Product_ID
    AND (Orders.Shipped_Date IS NOT NULL)
    AND (Orders.This_Order_Was_Shipped = "YES")
);


Help Improving My Database Design I'm new to databases and trying to design a model in Django in which I store Contest info and the Players who play the contest.  My first approach was below in which I created a Contest table that had a 1-to-many relationship with Entries.

class Contest(models.Model):
    start_date = models.DateTimeField()
    sport = models.CharField(max_length=200)
    contest_name = models.CharField(max_length=200)
    buy_in = models.IntegerField()

class Entries(models.Model):
    name = models.CharField(max_length=200)
    profit = models.FloatField()
    contest = models.ForeignKey('Contest',on_delete=models.CASCADE)

However when I think about my future usage I'm going to want to, for example, query all players of a specific sport and get all of their profits.  Or I may want to get the names of the top performers from the past month.  I feel the way the current DB is set up won't be really efficient and as my DB grows those queries will get more intensive if I have to filter all the contests by a certain date, and then check if that player was even in the contest...etc

Essentially adding the sport and date field in Entries will make things much easier for me but from what I understand you want to avoid duplicate fields so I'm trying to approach this in the "right" way.  Thanks.

UPDATED MODEL APPROACH

class Contest(models.Model):
    start_date = models.DateTimeField()
    sport = models.CharField(max_length=200)
    contest_name = models.CharField(max_length=200)
    buy_in = models.IntegerField()

class Player(models.Model):
    name = models.CharField(max_length=200)
    contest = models.ManyToManyField(Contest)

class Entries(models.Model):
    num_entries = models.IntegerField()
    profit = models.FloatField()
    player = models.ForeignKey('Player',on_delete=models.CASCADE)

So what I decided to was create a many-to-many relationship between the Contest and Player tables.  And a 1-to-many from the Player to the Entries.  I'm wondering is this better?

I'd definitely create at least 3 maybe 4 tables/models here.

One table contains all contents with the their details.
Second table with all the users/players.
Third table where you store the n:m relation between user and contest.
(Optional) Fourth table for the different types of sport.

But to be honest I'm not sure how to do this in django. Especially when it comes to the third table.


PostgreSQL: Query for location of global tablespace? I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides.

As all our clients are on the same host as the database manager, it should be easy enough for user-created tablespaces; the client can look up the filesystem path for the tablespace (in spclocation), and use OS calls to check how much available space is available:

adb=> select * from pg_tablespace;

  spcname   | spcowner |    spclocation    |       spcacl
------------+----------+-------------------+---------------------
 pg_default |       10 |                   |
 pg_global  |       10 |                   |
 adb        |  2033793 | /database/adb     | {adb=C/adb}

I can't see how, from the client, to get the path to where the global tablespace is stored; an empty string is returned for it in the above query.

Unfortunately, we have many legacy systems in the field using a particular database created in the global tablespace, and it would be a monumental effort to get it moved into a user-created tablespace.

Hopefully I'm just missing something really simple.

pg_default and pg_global locations are "hardcoded".

pg_default lives in:

select setting||'/base' from pg_settings where name='data_directory';

and pg_global lives in:

select setting||'/global' from pg_settings where name='data_directory';

src/backend/commands/tablespace.c says so:

 * There are two tablespaces created at initdb time: pg_global (for shared
 * tables) and pg_default (for everything else).  For backwards compatibility
 * and to remain functional on platforms without symlinks, these tablespaces
 * are accessed specially: they are respectively
 *          $PGDATA/global/relfilenode
 *          $PGDATA/base/dboid/relfilenode

Please also note, that exposing location of data directory is a - not so terrible but still - security hole.

app01@postgres=> show data_directory;
ERROR:  must be superuser to examine "data_directory"


How does DYNAMIC Row format handle NULL storage MYSQL 5.7? As a newbie to Mysql I just can't understand how NULL values are stored in each row formats.

As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. Meanwhile, NULLs for variable length columns are handled differently. The behaviour is defined under REDUNDANT row format description. I doubt what will be the behaviour with DYNAMIC / COMPRESSED row formats?

Note: I have tried to create 997 bigints in a table and have inserted value for one column. I had around 1957 rows but only had 26 pages, which means NULLs doesn't occupy space for fixed length. Am I understanding it in correct way? Please anyone help!
Specifications: 5.7.23 | Innodb | Dynamic Row | 16kb page size

The second bullet point 'The variable-length part of the record header contains a bit vector for indicating NULL columns'.

So when a row has a field that is NULL, the bit in the bit vector of the header is set.

AFAIK this is the same for DYNAMIC/COMPRESSED that really only changed the handling of variable length fields.


Should I drop a btree index on a column after adding a unique constraint? On one of my existing tables I have a btree index. I am now adding a unique constraint to this column. I know behind the scenes it will add a UNIQUE btree index to this column as well.
In this scenario, should I drop the original btree index added to the column to prevent redundant indexes?

If the unique constraint is defined on the same columns as the index, and the columns are in the same order, the index is redundant with the index created by the constraint and can be dropped without any problems.


Selecting entries with the same count as in another table I'm using a SELECT statement to select multiple IDs and than use

GROUP BY ID
HAVING COUNT(*) = someNumber

to only have the IDs with a certain count.

What I however actually want to do is to select all those IDs that have the same count that they have in another table.
I tried some variations of the following but couldn't get my desired result.

GROUP BY ID
HAVING COUNT(*) = (SELECT COUNT(entryName_ID) FROM entryNames)

Is it even possible to do what I want? And if so how?

If I understand your requirement correctly, You can try something like this.

This will get count per ID from table1 and table2 and then return only those records where T1.ID count matches the second table

SELECT T1.ID FROM
(
    SELECT ID,Count(*) c
    From Table1 GROUP BY ID
) T1
INNER JOIN
(
     SELECT ID,Count(*) c
     From Table2 GROUP BY ID
) T2
ON T1.ID = T2.ID AND T1.c = T2.c


PL/SQL Procedure: limit number of INSERTS I have a procedure that is supposed to take an employee from a certain table and insert it into another table that records who is in a leadership role for a certain period.

The thing is, there can only be a maximum of two employees in leadership roles for a given period.

The primary key in the table is the pair (employee,month) and there is no manual insert, so it's all done trough the procedure.

How do I structure it so that concurrent accesses can't register more than 2 employees in a leadership role for the same month?

I thought about getting a COUNT(*) on the number of rows for that month, but if an insert occurs after the count is registered, it would no longer be valid. Is there any way to control this right on the procedure or do I need to resort a trigger to check after each insert and rollback if the number exceeds 2?

I cannot change the structure of the table, just the procedure.

Ok, the trigger is trivial I think, for DBMS_LOCK, just to give you the idea:

create or replace procedure proc_insert(...) as
  l_count number;
  l_handle varchar2(128);
  l_return number;
begin
  dbms_lock.allocate_unique('leadership_role_insert_lock', l_handle);
  l_return := dbms_lock.request(l_handle);

  if l_return = 0 then
    -- lock successfully acquired
    select count(*) into l_count from leadership_role_table where ...;

    if l_count < 2 then
    -- inserting
      commit;
    else
      -- not inserting
      l_return := dbms_lock.release(l_handle);
      raise_application_error(-20001, 'your error goes here');
    end if;
  else
    -- could not acquire the lock
    raise_application_error(-20001, 'your error goes here');
  end if;

  -- this kind of lock needs an explicit release
  l_return := dbms_lock.release(l_handle);
  exception when others then
    rollback;
    l_return := dbms_lock.release(l_handle);
end;
/

With the above, concurrent calls of dbms_lock.request will be blocked until the previous session releases the lock.


SQL Delete duplicate rows based on column I need to remove the duplication of rows based on column to have unique data in this column, additionally I need another condition when the value equal specific information.

In my example I want to filter by TagName to avoid duplication plus I need the value of Alarmstate = 'UNACK_ALM'.

Please refer to the pictures for more clarification, I did it using Excel but I need to write it in SQL view.

Thanks for coopearation.

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

    --REMOVE DUPLICATES
    WITH CTE AS
    (SELECT ROW_NUMBER() OVER
(PARTITION BY column_tocheck_duplication_against ORDER BY ( SELECT 0)) RN
    FROM TableName)
    DELETE FROM CTE WHERE RN > 1


Nested push in Mongoose I have a user schema like that:
name: String,
nick: String,
email: String,
password: String,

bio: String,
color: String,
followers: [String],
followings: [String],

posts: [{
    author: {
        name: String,
        nick: String
    },
    content: String,
    date: Date,
    simpleDate: String,
    color: String,
    likes: [String]
}]

I want to when user push the like button my backend take the id of post and add user's nick to array that named "likes". So I want to find the object that has post that has this id and add a new string to likes array of it.
User.findOneAndUpdate({'posts._id': req.params.postId},{$push:{posts:{likes:{likes:req.header.authenticatedUser.nick}}}},
    (err,result) => {...});

I tried something like that but it's not working and looks like very wrong.
How can I find and push to nested object like that?
-edit-
When I use the code below
User.findOneAndUpdate({'posts._id': req.params.postId},{"$push":{"posts":{"likes":req.header.authenticatedUser.nick}}},(err,results)=>{
    if(err){
        console.log(err);
    }
});

it adds a likes array to posts. But I want add my usurname to likes array of post that has index 2

Before the change your application code, you should try to mongo shell this command on your test environment.
db.test.findOneAndUpdate({},
    {"$push":
        {"post.2.likes":"Yunus"}
    })


Generating Pivot In SQL Server 2008 I am attempting to create a Pivot Table in SQL Server 2008, but I am getting an error of

  Incorrect syntax near the keyword 'FOR'

Below is sample DDL and the query I tried to execute - how should this query be changed so that it will execute succesfully?

      Declare @House Table
  (
    studID int
    ,sID int
    ,course varchar(500)
    ,cls int
    ,lv decimal(10,2)
  )

  Insert Into @House (studID, sID, course, cls, lv) VALUES
  (52, 52, 'Remaining Metrics (14th Century)', 31, '12.5')
  ,(52, 52, 'History Of The World (Part I)', 33, '200.0')
  ,(52, 52, 'Singing Socks In The Rain (Part III)', 12, '3.5')
  ,(11, 11, 'Remaining Metrics (14th Century)', 31, '2.5')
  ,(11, 11, 'History Of The World (Part I)', 33, '1.5')
  ,(11, 11, 'Singing Socks In The Rain (Part III)', 12, '2.4')

  Select studID from @House
  PIVOT (lv FOR course IN ([Remaining Metrics (14th Century)],[History Of The World (Part I)], [Singing Socks In The Rain (Part III)])) As Drafts

It would be helpful to see your expected output, but at a guess, you need this:

SELECT
    studID,
    [Remaining Metrics (14th Century)],
    [History Of The World (Part I)],
    [Singing Socks In The Rain (Part III)]
FROM
    (SELECT StudID, Course, SUM(lv) AS Lv FROM @House GROUP BY StudID, Course) AS X
PIVOT
    (
    SUM(lv) FOR course IN ([Remaining Metrics (14th Century)], [History Of The World (Part I)], [Singing Socks In The Rain (Part III)])
    ) As Drafts

...if you want to see the total of each students' "Lv" values, in a column for each course.


How verify if replicaset is working? I am newbie with MongoDB and even more in administration of databases. But my boss passed me an issue to solve.

SCENARIO

I have two servers using MongoDB and ReplicaSet, when I run the command

db.adminCommand( { replSetGetStatus : 1 } )

on the primary server I get the result below:

{
    ...
    "date" : ISODate("2018-12-17T10:39:00.247Z"),
    "myState" : 1,
    ...
}

and on the secondary server I get the result below:

{
    ...
    "date" : ISODate("2018-12-17T10:39:00.247Z"),
    "myState" : 2,
    ...
}

as expected.

QUESTIONS

If I stop the network interface (eth0) of the primary server to activate ReplicaSet, should the myStatus attribute of the secondary server change to 1?
If the status is to change, I have already tested it and it is not changing. How do I know that the secondary has become primary?

If I stop the network interface (eth0) of the primary server to activate ReplicaSet, should the myState attribute of the secondary server change to 1?

Since you only have two voting members in your replica set, the strict majority of voting members required to elect or maintain a primary (n/2 + 1)  is 2.

You should expect that if either member is unavailable, there will be no primary: a two member replica set does not provide automatic fault tolerance.

  If the status is to change, I have already tested it and it is not changing. How do I know that the secondary has become primary?

The replica set member state changes to reflect the current role of the replica set member. Your surviving secondary cannot become a primary without manual intervention to forcibly reconfigure the replica set. The recommended solution for production deployments is to add a third member to your replica set (ideally another data-bearing member hosted on a third machine) so your deployment can automatically recover from any single member being unavailable.

There are several ways to confirm member state via the mongo shell:

The default mongo shell prompt is prefixed with the replica set name and member state of the MongoDB process you are connected to, for example: replset:PRIMARY>. If the member state has changed, hitting <enter> should re-establish a connection and refresh the shell prompt.
Check via db.isMaster().primary. This status value should be set to the current host (equivalent to db.isMaster().me) if it is primary.
Check the member state in rs.status().myState. A value of 1 indicates the current member has the state PRIMARY.

You can find out more information about the election process by searching for MongoDB log lines with the  REPL log component. When you shutdown the primary on your two member replica set, you should see log messages similar to the following on your remaining secondary:

# Checking from a bash shell (note the intentional space after "REPL ")
grep "REPL " mongod.log | tail -1

2018-12-24T12:40:01.894+1100 I REPL     [replexec-2] Not starting an election, since we are not electable due to: Not standing for election because I cannot see a majority


PostgreSQL pg_dump custom file input not dumping data When I'm using custom format (-Fc) in pg_dump, the file is smaller (42kb), and when I restore it, it only restores the tables and columns, not the data.

But when I try to use plain text (-Fp), the file is larger (73kb), and when I try to restore it, all data are included (table, column, rows, etc).

Also, when I try to use a Database Manager (dBeaver to be exact), and I try to backup, I can see the command, and it's the same command that I use in pg_dump. But the dump file is larger (73kb) and when I tried to restore using that dump, all data are included.

Any ideas on what seems to be the problem? As of now, I'm using plain-text as the format, but I want to use the custom one.

I'm using PostgreSQL 11.2.

This is full command that I was using in pg_dump. And this is the exact same command that dBeaver is using to dump the database.

pg_dump.exe --format=c -n public --verbose --host=localhost --port=5432 --username=postgres -f db.backup postgres_db

I usually do this and I do not have that problem.

Backup

pg_dump -p 5432 -U postgres -Fc -d postgres -f /tmp/postgres.custom

Restoration

pg_restore -d postgres -p 5432 -U postgres -C /tmp/postgres.custom


How can I optimize this query? I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query:

(
SELECT  `time`
FROM  `eventlog`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `screenshots`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `sitelog`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `keylog`
WHERE userid =  '9819'
)
UNION ALL (

SELECT  `time`
FROM  `webcamcaps`
WHERE userid =  '9819'
)
ORDER BY  `time` ASC
LIMIT 1;

Here's the EXPLAIN:

+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
| id | select_type  | table            | type | possible_keys | key    | key_len | ref   | rows | Extra          |
+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
|  1 | PRIMARY      | eventlog         | ref  | userid        | userid | 4       | const |  308 |                |
|  2 | UNION        | screenshots      | ref  | userid        | userid | 4       | const | 7850 |                |
|  3 | UNION        | sitelog          | ref  | userid        | userid | 4       | const | 1949 |                |
|  4 | UNION        | keylog           | ref  | userid        | userid | 4       | const | 3589 |                |
|  5 | UNION        | webcamcaps       | ref  | userid        | userid | 4       | const | 8035 |                |
| NULL | UNION RESULT | <union1,2,3,4,5> | ALL  | NULL          | NULL   | NULL    | NULL  | NULL | Using filesort |
+----+--------------+------------------+------+---------------+--------+---------+-------+------+----------------+
6 rows in set (0.00 sec)

How can I optimize this so I'm not looking through so many rows?

Take advantage of the index for each table doing something like this:
(SELECT  MIN(`time`) `time` FROM  `eventlog` WHERE userid =  '9819') UNION ALL
(SELECT  MIN(`time`) FROM  `screenshots` WHERE userid =  '9819')     UNION ALL
(SELECT  MIN(`time`) FROM  `sitelog` WHERE userid =  '9819')         UNION ALL
(SELECT  MIN(`time`) FROM  `keylog` WHERE userid =  '9819')          UNION ALL
(SELECT  MIN(`time`) FROM  `webcamcaps` WHERE userid =  '9819')
ORDER BY  `time` ASC
LIMIT 1;

or
(SELECT  `time` FROM  `eventlog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `screenshots` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `sitelog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `keylog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `webcamcaps` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
ORDER BY  `time  ASC
LIMIT 1;

The main idea is to get each subquery to return 1 row.
Give it a Try !!!


Optimal way(performance wise) to store array like data in MySQL I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:

order(
item1(item id, quantity)
item2(item id, quantity))

And i want to know what is the optimal way to store the order, i came up with two ways to do that,
The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,

The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if  the user submits an order with * number of items i will have * number of rows for the order in the order items table.

When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.

If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.

If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.

Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.


My connection to a Mysql DB has been blocked after spamming queries, for how long? I am creating a web application that depends on querying a legacy DB without an API.

It's MySql.

Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app fired a bunch of (Probably a few hundred) get requests towards the database, and now i can't connect to it anymore, it simply times out.

I horrified tried to log into to it via the browser and PHPmyAdmin, and to my relief nothing was wrong, but in Status->Processes i can see this process:

Kill    25289014    myusername  localhost   None    Query   0   ---

I reckon the DB killed all incoming connections from my user when i began to spam it from my application.

Do any of you guys know if the "block" will be lifted after a certain amount of hours. It's about 12 hours ago now.

Or if i have to contact the administrator to lift the block, or kill the kill process? My user doesn't seem to have the privilege to kill the process as there is no buttons to do so.

Thanks in Advance.

SUGGESTION
If you have the RELOAD privilege, you can run this
mysql> FLUSH HOSTS;

According to MySQL Documentation on running FLUSH HOSTS:

Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

You should run this command
SELECT host,Reload_priv FROM mysql.user
WHERE user = 'mysqluser';

If nothing appears, then you do not have the RELOAD privilege. Consequently, you have no choice but to contact the system administrators to run FLUSH HOSTS; for you.
ALTERNATIVE SUGGESTION
Ask the system administrators to configure max_connect_errors in my.cnf
[mysqld]
max_connect_errors = 18446744073709551615;

To avoid restarting mysql, ask the system administrators to run this
mysql> set global max_connect_errors = 18446744073709551615;

Give it a Try !!!


Query slower after upgrade from sql server 2008R2 to 2014sp2 I recently upgraded from SQL Server 2008R2 to 2014SP2 and upgraded the databases to the compatiblity level 120.
This query generates a better execution plan with the old cardinality estimator (traceflag 9481 enabled): https://www.brentozar.com/pastetheplan/?id=Hy9skV1Ox
It only uses 269 logical reads on object2 and 3 on object3.
With the new cardinality estimator i get this execution plan: https://www.brentozar.com/pastetheplan/?id=BJAnpXk_e
Unfortunately this results in 269 logical reads on object2 and 68688 on object3.

The server is configured for adhoc workload.
Even enabling the traceflag 4199 doesn't improve the estimates/execution plan
Why does the new cardinality estimator generate such a worse plan?
Why are the estimates so far off?
What could i do to improve it besides disabling the new estimator or using query hints?

I understand your disappointment with the query plan regressions that you experienced. However, Microsoft changed some core assumptions about the cardinality estimator. They could not avoid some query plan regression. To quote  Juergen Thomas:

  However to state it pretty clearly as well, it was NOT a goal to avoid any regressions compared to the existing CE. The new SQL Server 2014 CE is NOT integrated following the principals of QFEs. This means our expectation is that the new SQL Server 2014 CE will create better plans for many queries, especially complex queries, but will also result in worse plans for some queries than the old CE resulted in.

To answer your first question, the optimizer appears to pick a worse plan with the new CE because of a 1 row cardinality estimate from Object2. This makes a nested loop join very attractive to the optimizer. However, the actual number of rows returned from Object2 was 34182. This means that the estimated cost for the nested loop plan was an underestimate by about 30000X.

The legacy CE gives a 208.733 cardinality estimate from Object2. This is still very far off, but it's enough to give a plan that uses a merge join a lower estimated cost than a nested loop join plan. SQL Server gave the nonclustered index seek on Object3 a cost of 0.0032831. With a nested loop plan under the legacy CE, we could expect a total cost for 208 index seeks to be about 0.0032831 * 208.733 = 0.68529 which is much higher than the final estimated subtree cost for the merge join plan, 0.0171922.

To answer your second question, the cardinality estimate formulas for a query as simple as yours are actually published by Microsoft. I recommend referencing the excellent white paper on differences between the legacy and new CE found here. Focus on why the cardinality estimates are 1 for the new CE and 208.733 for the legacy CE. That's unexpected because the legacy CE assumes independence of filters but the new CE uses exponential backoff. In general for such a query I would expect the new CE to give a larger cardinality estimate for Object2. You should be able to figure what's going on by looking at the statistics on Object2.

To answer your third question, we can get general strategies from the white paper. The following is an abbreviated quote:

  Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods.
  Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE.
  Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE.
  Use fundamental cardinality estimate skew troubleshooting methods.
  Revert to the legacy CE entirely.

For your problem in particular, first I would focus on the statistics. It's not clear to me why the cardinality estimate for an index scan on Object3 would be so far off. I recommend updating statistics with FULLSCAN on all of the involved objects and indexes before doing more tests. Updating the statistics again after changing the CE is also a good step. You should be able to use the white paper to figure out exactly why you're seeing the cardinality estimates that you're seeing.

I can give you more detailed help if you provide more information. I understand wanting to protect your IP but what you have there is a pretty simple query. Can you change the table and column names and provide the exact query text, relevant table DDL, index DDL, and information about the statistics?

If all else fails and you need to fix it without hints or trace flags, you could try updating to SQL Server 2016 or changing the indexes on your tables. You're unlikely to get the bad nested loop plan if you remove the index, but of course removing an index could affect other queries in a negative way.


Mirroring not working after a server crash I'm using SQL Server 2008R2. Last night, the host machine on which my mirror database was running basically went belly up. Thankfully the principle database was on a different host server and so was fine. However...

The principle is working but says suspended. When i try to resume the mirroring, I get an error in the SQL error log giving an error number of 9004.

A quick Google of this error number come back with this article. (tl;dr: transaction log is damaged)

So, does this mean that the transaction log shipping between the principle and the mirror has somehow got screwed up? How do I fix this?

Is it as simple as doing a full backup on the principle and a full transaction log, then restoring them both on the mirror database with norecovery switch on and then set up the mirroring again?

Or will I need to do something more drastic?

The likely issue is that the principal has had log backups performed since the mirroring session was paused and it cannot catch up, so re-initializing the mirror with a full and log backup should fix the problem.  First you will need to remove the mirroring from both servers with:

alter database [mydb] set partner off

Then restore the full and log backup with NORECOVERY and re-run through the mirroring wizard.