Rows with a variable number of columns? I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter.

I'm using code to fragment a tweet into words, usernames, and hashtags, so

I'm meeting @President over coffee to talk about my new job #cabinet #woot

would break down into {meeting, over, coffee, talk, about, new, job} (getting rid of words under 2 letters and after stripping punctuation), {president}, and {cabinet,woot}.

With my lack of experience in this area, I'd like to just be able to grow the number of rows in a column as necessary, but my intuition tells me that this would be a nightmare.  Another possibility I've considered is just picking an arbitrary number like 10 columns to store words, 5 for usernames, etc., but this would be roughing it a bit, as I don't know for sure how many there will be, and I'd have to throw out what may be valuable information.

As it stands now, I have 3 varchars of sufficient length which hold "meeting over coffee talk about new job", "president", and "cabinet woot".  So, whenever I need to grab the information for a particular tweet, I retrieve the row and break up the strings.

Is this the "correct" route?  Am I missing an obvious way that will make my life easier? (I had found something online about using array, but I'm using MySQL and it doesn't seem to support that datatype.

Edited after a couple of comments by the question asker!:
Better idea for your design:
(If you're not familar with these diagrams, that's seven tables, with foreign key links as pictured [the three _Link tables all have two foreign keys referencing the data tables]. It probably looks like I'm overcomplicating things, but trust me, "tokenise once and store in the database" is so much more efficient than "tokenise data each time you retrieve it".)
Sample data, using a simple tweet (ignoring Users for now, it's the same concept though): "test tweet! #howdoesthiswork #newbie"
      Tweet
TweetID   WordCount
   1         2

Tweet_Word_Link
TweetID   WordID
   1         1
   1         2

      Word
WordID    Word
   1      test
   2      tweet

Tweet_Hashtag_Link
TweetID  HashtagID
   1         1
   1         2

      Hashtag
HashtagID  Hashtag
    1      #howdoesthiswork
    2      #newbie

This allows you to easily get all the users linked in a tweet:
SELECT U.UserName
FROM  User U
        INNER JOIN
      Tweet_User_Link TUL ON U.UserID = TUL.UserID
        INNER JOIN
      Tweet T ON TUL.TweetID = T.TweetID
WHERE T.TweetID = <your ID here>

(change table names as appropriate for Hashtags and Words)
Or, slightly more complicated, get the top 10 words for all tweets mentioning a specific hashtag:
SELECT TOP 10 W.Word, COUNT(*) AS WordCount
FROM  Word W
        INNER JOIN
      Tweet_Word_Link TWL ON TWL.WordID = W.WordID
        INNER JOIN
      Tweet T ON TWL.TweetID = T.TweetID
        INNER JOIN
      Tweet_Hashtag_Link THL ON THL.TweetID = T.TweetID
        INNER JOIN
      Hashtag H ON H.HashtagID = THL.HashtagID
WHERE H.Hashtag = <your hashtag here>
GROUP By W.Word

(I've used ANSI SQL syntax, I'm not 100% sure if MySQL supports all of ANSI SQL - but it should be close enough.)


Use group_concat to merge multiple field rows into one field

create table A(a_id,a_name);
create table B(a_id,b_name varchar(5));
insert into A values(1,'john'),(2,'paul'),(3,'joy');
insert into B values(1,'x'),(1,'y'),(1,'z');

set group_concat_max_len = 4096; -- default: 1024

-- the query
select A.a_name, group_concat(B.b_name separator ',')
from A, B
where A.a_id = B.a_id
group by A.a_name;

-- result
+--------+--------------------------------------+
| a_name | group_concat(B.b_name separator ',') |
+--------+--------------------------------------+
| john   | x,y,z                                |
+--------+--------------------------------------+
1 row in set (0.00 sec)


DBA to work from home I was wondering is it feasible or doable to work from home as a DBA or Database designer/developer or Business intelligence developer.
I have seen developers doing great working from home, but can DBAs work from home and be productive or do they have to physically be at work.

Do you know any DBA working from home.
If yes how is he managing it and what challenges is he facing
Is it more productive to work from home as a DBA

It depends...

Anyone can work from home if they have the proper work ethic.  If your very self motivated and task oriented then it will work fine.  It is important that you are able to show the people that you report to that you are producing top quality work.

As a manager it is harder for me to manage remote employees because I have to work harder to make a personal connection with them and ensure that we are working well together.  I have some great people that work for me, database professionals, that work from home hundreds of miles away.


Triggers After Insert and After Update - Good Practice It is a good practice to have a separate trigger for After Insert and another one for After Update or there is no problem in having both in the same logic?

Also from a perspective of performance it will be better to have them isolated?

From a performance point of view if the are doing the same thing then there will be no difference. If your trigger performs tests to see what needs to be done (i.e. needs to treat updated rows differently to newly inserted ones) then there could be an impact if query plans that are sub-optimal for common cases get cached and reused for those cases.

For a code maintenance PoV the decision is also split by what differences there are in the behaviours. If the action taken in each case is identical (or near identical) then it makes sense to have one piece of code than two (so if you fix a bug in one you don;t have to remember to replicate the fix in the other location) - in this case using one trigger for both actions is preferred unless there is a significant performance difference. If the actions of each trigger for the trigger are noticeably different then keep them separate, again for code clarity reasons.

Of course it also depends what database system you are using: different ones will have different performance caveats that you may need to be concerned about.


How to optimize multiple self-JOINs? I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design.

One of the tables looks as follows (relevant cols only):

CREATE TABLE IF NOT EXISTS CountryData (
  countryDataID INT PRIMARY KEY AUTO_INCREMENT,
  dataID INT NOT NULL REFERENCES DataSources (dataID),
  dataCode VARCHAR(30) NULL,
  countryID INT NOT NULL REFERENCES Countries (countryID),
  year INT NOT NULL ,
  data DEC(20,4) NULL,
  INDEX countryDataYear (dataID, countryID, year));

The data column has values for a few hundred indicators, 90 countries, and 30 years for ~1mn rows total. A standard query requires selecting N indicators for a particular year and C countries, yielding a CxN table for 90 rows max.

With all values in a single column, self-joins seemed like the way to go. So I have experimented with various suggestions to speed those up, including indexing and creating new (temp) tables. At 9 self-joins, the query takes a little under 1 min. Beyond that, it spins forever.

The new table from where the self-joins take place has only about 1,000 rows, indexed on what seem to be the relevant variables - creation takes about 0.5 sec:

CREATE TABLE Growth
    SELECT dataID, countryID, year, data
    FROM CountryData
    WHERE dataID > 522 AND year = 2017;

CREATE INDEX growth_ix
    ON Growth (dataID, countryID);

The SELECT query then arranges up to XX indicators in the results table, with XX unfortunately <10:

SELECT
    Countries.countryName AS Country,
    em01.em,
    em02.em,
    em03.em
    ...
    emX.em
FROM
    (SELECT
        em1.data AS em,
        em1.countryID
    FROM Growth AS em1
    WHERE
    em1.dataID = 523) as em01
    JOIN
    (SELECT
        em2.data AS em,
        em2.countryID
    FROM Growth AS em2
    WHERE
    em2.dataID = 524) as em02
    USING (countryID)
    JOIN
    (SELECT
        em3.data AS em,
        em3.countryID
    FROM Growth AS em3
    WHERE
    em3.dataID = 525) as em03
    USING (countryID)
    ...
    JOIN
    (SELECT
        emX.data AS em,
        emX.countryID
    FROM Growth AS em5
    WHERE
    emX.dataID = 527) as emXX
    USING (countryID)
    JOIN Countries
    USING (countryID)

I'd actually like to retrieve a few more variables, plus potentially join other tables. Now I'm wondering whether there's a way to run this more efficiently, or whether I should take an altogether different approach, such as using wide tables with indicators in different columns to avoid self-joins.

I don't think there is a need for denormalizing the table. Self-joining will work fine if you have indexes that will be used effectively. For the specific query, I'd add an index on (year, dataID, countryID, data) and not use any derived tables:

SELECT
    c.countryName AS Country,
    em01.data AS data01,
    em02.data AS data02,
    ...
    emXX.data AS dataXX
FROM
    Countries AS c
      JOIN CountryData AS em01
        ON  em01.year = 2017
        AND em01.dataID = 523
        AND em01.countryID = c.countryID
      JOIN CountryData AS em02
        ON  em02.year = 2017
        AND em02.dataID = 524
        AND em02.countryID = c.countryID
      ...
      JOIN CountryData AS emXX
        ON  emXX.year = 2017
        AND emXX.dataID = YYY
        AND emXX.countryID = c.countryID
 ;

The only issue you'll have is that MySQL has a hard limit of maximum 61 joins in a query. So, you won't be able to have 90 columns with the above query.

Another observation is that you don't seem to need any aggregated data but just a tiny subset of the (small or big table, doesn't matter). With the suggested index, you could just write a query like this:

SELECT
    cd.countryID,
    c.countryName AS Country,
    cd.dataID,
    cd.data
FROM
    Countries AS c
      JOIN CountryData AS cd
        ON  cd.countryID = c.countryID
WHERE
      cd.year = 2017
  AND cd.dataID IN (522, 523, ..., YYY)
ORDER BY
    cd.countryID,
    cd.dataID ;

and have the pivot transformation done in your application.


Issue Installing SSMS V17.0 When I try to install SQL Server Management Studio, Version 17.0, I receive the following error message:

  "The Generally Available (GA) version of SQL Server Management Studio
  (SSMS) v17.0 cannot be installed because a pre-release version of SSMS
  is installed on your computer. Uninstall the pre-release version of
  SSMS from 'Add/Remove Programs' in the Control Panel and run SSMS
  setup again."

I uninstalled the pre-release version, but still have the issue. When I look at the log file, the condition that is causing the problem is

  [1118:18D4][2017-05-09T16:05:55]i052: Condition 'NOT RCBitsInstalled
  OR (RCBitsInstalled > "2016.140.17030.0" OR RCBitsInstalled <
  "2016.140.0.0")' evaluates to false.

But looking into the log file further, that value seems fine.

  [1118:18D4][2017-05-09T16:05:55]i000: Setting version variable
  'RCBitsInstalled' to value '2016.140.16000.64'

Anyone know a way around this? Thanks!

Delete the folder C:\Program Files (x86)\Microsoft SQL Server\140\Tools\ and then try the installer again. You may need to reboot to release all file handles.


How many row level locks can I have to same row? I need some insights on row-level locking in Postgres.

How many shared locks (FOR SHARE) can be concurrently on a particular tuple in a relation .
Will have many shared locks on same tuple cause any performance/or any issue to database.
If such limitations exist, how to configure them and advisable limits.

Quote from the manual

  PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes

(Emphasis mine)

So the only performance issue that you might get, is the number of disk writes heavy locking might cause.

While there is an "unlimited" number of row locks in the system, there is a limit on the number of objects (e.g. tables) that can participate in locking for a single transaction, which is controlled through max_locks_per_transaction

That property controls the number of objects one transaction can hold locks for, not the number of locks. A transaction can lock a million rows in a single table. However the default value of 64 for max_locks_per_transaction means that a transaction can hold locks for at most 64 tables (but e.g. a million rows in each table). So if your transactions never touch more than 64 tables, there is nothing to worry about.

A common reason to increase this value is if you are dropping a lot of tables in a single transaction e.g. through a drop owned by current_user statement to clean up a database.


Why is add timestamp column with default not null so slow in Oracle 11g? In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows).
I cannot have more than 30 - 60 seconds of downtime when I run it in production.

This statement completes instantly:

alter table
my_table
add my_timestamp_col timestamp(6) with time zone;

However I would like to default the column to current_timestamp at time zone 'UTC' and force it be non-null.

With other data types, Oracle 11g will do this quickly if you use "add column  default  not null"
For example both of these complete instantly:

alter table
my_table
add my_varchar_col varchar2(10) default 'hello' not null;

alter table
my_table
add my_date_col date default sysdate not null;

But with a timestamp it is slow (at least tens of minutes on my test DB). I assume this means it is updating every row.

So my questions are:

Why is it different with a timestamp than other data types?
Is there a fast way to accomplish the outcome where the column is
forced to be non-null and new rows default to the current UTC
timestamp?

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

This is actually improved in Oracle 12c.

From Oracle Magazine's Ask Tom column:
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

  In Oracle Database 11g you were able to perform a fast add of a column
  to a table if it had a default value and was defined as NOT NULL.
  (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you
  attempted to add a column with a default value and that column
  permitted null values, the ADD COLUMN operation could take a
  significant amount of time, generate a large amount of undo and redo,
  and lock the entire table for the duration of the operation. In Oracle
  Database 12c, that time, volume, and locking are no longer part of the
  process.

I know this doesn't help if you're stuck on 11gR2, since you probably want to get that column added before taking an outage to upgrade the DB.

As to why it takes so long only when the data type is TIMESTAMP, I'm at a loss.  Maybe since the TIMESTAMP data type is a few more bytes to store than DATE, or than VARCHAR2 for empty strings, and those extra bytes are causing a cascade of row migrations.  To test that theory, try adding a fixed length CHAR column (in a non-Prod environment): alter table my_table add my_char_col CHAR(200) default 'hello' not null; I expect this would not be instantaneous due to row migrations.

Your best bet on 11gR2 might be either to

Take an outage at an appropriate time that is ok with your stakeholders to add this column

or

Use DBMS_REDEFINITION to add the column as part of an online redefinition.  This should be non-blocking, and even though it may take hours, the users will not even notice it happening (no locking).  If you have a lot of indexes, this becomes a little more difficult.  If you have foreign keys, this becomes even more difficult.  If you have stored procs or views that reference this table, be sure to recompile them after you FINISH the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).

or

Maybe add the column without the NOT NULL constraint.  When that finishes, then alter the column to add the NOT NULL constraint. My intuition is that you'll still get locking somewhere in the process, so no promises. Maybe just a crazy idea.

If you want to try the Online Redefinition, then you might find these links helpful:

http://www.oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1.php
http://docs.oracle.com/cd/E18283_01/server.112/e17120/tables007.htm


can I replace nulls in a PIVOT with zeroes? I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes?  I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am curious if there is a shortcut.

If you have the following table of data that you want to pivot:

CREATE TABLE yourtable (id int, name varchar2(1), value varchar(10));

INSERT ALL
    INTO yourtable (id, name, value )
         VALUES (1, 'A', '1500')
    INTO yourtable (id, name, value )
         VALUES (1, 'B', '4500')
    INTO yourtable (id, name, value )
         VALUES (2, 'C', '3.5')
    INTO yourtable (id, name, value )
         VALUES (3, 'B', 'test')
    INTO yourtable (id, name, value )
         VALUES (4, 'A', 'blah')
    INTO yourtable (id, name, value )
         VALUES (4, 'C', 'hello')
SELECT * FROM dual;

And your current code is similar to:

select id, OptionA, OptionB, OptionC
from
(
  select id, name, value
  from yourtable
) src
pivot
(
  max(value)
  for name in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv

See SQL Fiddle with Demo.  The sample data will create null values.

When you want to replace the null values you have to do it in the final SELECT list. So your code will need to be:

select id,
  coalesce(OptionA, '0') OptionA,
  coalesce(OptionB, '0') OptionB,
  coalesce(OptionC, '0') OptionC
from
(
  select id, name, value
  from yourtable
)
pivot
(
  max(value)
  for name in ('A' as OptionA, 'B' as OptionB, 'C' as OptionC)
);

See SQL Fiddle with Demo


How do we identify service shutdown time in MySQL How can we identify service shutdown time in MySQL community version 5.7.20. I presume that "statement/sql/shutdown" and "statement/com/Shutdown" are the events that occurs while shutdown a service, using this events I tried to set triggers to load the time. But I am unable to set the triggers too.

The way I identified is through error log. Do we have any other variable or a way to identify the time of last service down.

There are three(3) ways using file timestamps:

InnoDB Buffer Pool

If you have innodb_buffer_pool_dump_at_shutdown configured, look for the timestamp of the file that was written. The default filename is ib_buffer_pool. Mentioned this back on Dec 06, 2017 (Control InnoDB buffer pool allocation in MySQL (5.7+) ib_buffer_pool is usually written in folder set by datadir, you can run the following after shutdown:

cd /var/lib/mysql
ls -l --time-style=+"%Y-%m-%d %H:%M:%S" ib_buffer_pool | awk '{print $6,$7}'

Binary Logging

If mysql has binary logging enabled, get the timestamp of the last binary log after shutdown.

Error Log

You are probably saying, "I DON'T WANT TO LOOK INSIDE THE ERROR LOG !!!"

You don't have to. Just get the timestamp of the error log after the shutdown is complete.


Secure method beside phpMyAdmin for remote mysql Recently, one of my centos linux servers was hacked and the cause of due to phpMyAdmin. It was said due to this problem PhpMyAdmin vulnerability CVE-2011-2505 (http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2011-2505). What is the best way to login securely into my mysql db? Any idea please.

Use a VPN connection to connect to the network which is hosting the database server.

A database server should never be directly accessible from the public Internet.  If it is directly accessible it will be broken in to. It's just a mater of time.


Sum between multiple joined tables I have these tables:

DROP TABLE IF EXISTS books;
CREATE TABLE `books` (
  `bookId` mediumint(8) UNSIGNED NOT NULL,
  `title` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `books`
  ADD PRIMARY KEY (`bookId`),

DROP TABLE IF EXISTS movements;
CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementTypeId` tinyint(3) UNSIGNED NOT NULL,
  `deletedFlag` tinyint(3) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementId` (`movementTypeId`,`deletedFlag`) USING BTREE;

DROP TABLE IF EXISTS movements_types;
CREATE TABLE `movements_types` (
  `movementTypeId` mediumint(8) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements_types`
  ADD PRIMARY KEY (`movementTypeId`);

DROP TABLE IF EXISTS movements_books;
CREATE TABLE `movements_books` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `bookId` mediumint(8) UNSIGNED NOT NULL,
  `bookSize` tinyint(3) UNSIGNED NOT NULL,
  `quantity` smallint(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `books` (`bookId`, `title`) VALUES
(1, 'Harry Potter'),
(2, 'Mysql Join'),
(3, 'Comedy');

INSERT INTO `movements` (`movementId`, `movementTypeId`, `deletedFlag`) VALUES
(7, 1, 0),
(8, 2, 0),
(9, 2, 0);

INSERT INTO `movements_types` (`movementTypeId`, `title`) VALUES
(1, "Bought"),
(2, "Sold");

INSERT INTO `movements_books` (`movementId`, `bookId`, `bookSize`, `quantity`) VALUES
(7, 1, 1, 3),
(7, 1, 2, 3),
(7, 2, 1, 3),
(7, 2, 2, 3),
(8, 1, 1, 2),
(8, 1, 1, 2),
(9, 2, 1, 3);

bookSize is just an integer indicating the size of the book.

movements_books actually has 111824 records, movements has 4534.
deletedFlag is 1 for a deleted movement (I prefer to keep them flagged) and 0 otherwise.

I need help creating indexes and I need these results:

1) sizes sold/bought/available for each book (bookId, bookSize, booksSold, booksBought, booksBought minus booksSold)

2) books sold/bought/available for each book of all size (bookId, booksSold, booksBought, booksBought minus booksSold)

To get the one of the statistics I tried:

SELECT
   books.title
   movements_books_grouped.bookId,
   SUM(IF(movements.movementTypeId=1, movements_books_grouped.quantity, NULL)) AS booksBought,
   SUM(IF(movements.movementTypeId=2, movements_books_grouped.quantity, NULL)) AS booksSold,
   (
       SUM(IF(movements.movementTypeId=1, movements_books_grouped.quantity, 0))-
       SUM(IF(movements.movementTypeId=2, movements_books_grouped.quantity, 0))
   ) AS booksAvailability
   FROM
   (
   SELECT bookId,quantity,movementId FROM movements_books GROUP BY bookId
   ) AS movements_books_grouped
   JOIN movements ON movements.movementId=movements_books_grouped.movementId
   JOIN books ON books.bookId=movements_books_grouped.bookId
   GROUP BY movements_books_grouped.bookId
   ORDER BY books.title

but it's very slow.
Edit: I needed to add another table to the example because that's the one that makes really slow the last query. I need to join to this table because I need to order the result by title.

I suggest the schema is "over-normalized".  I would suggest any table that lists a 'movement' should include the direction (movementTypeId).  Perhaps it could be done with a sign instead of an extra column?  (Positive for Bought, negative for Sold.)

There are no indexes on movements_books; maybe other tables?

The GROUP BY bookId in the subquery is probably wrong.

(cleaned up a little, and fixed missing comma:)

SELECT  b.title, mbg.bookId,
        SUM(IF(m.movementTypeId=1, mbg.quantity, NULL)) AS booksBought,
        SUM(IF(m.movementTypeId=2, mbg.quantity, NULL)) AS booksSold,
        ( SUM(IF(m.movementTypeId=1, mbg.quantity, 0))-
          SUM(IF(m.movementTypeId=2, mbg.quantity, 0))
        ) AS booksAvailability,
    FROM (
        SELECT  bookId, quantity, movementId
            FROM  movements_books AS mb AS b
            GROUP BY  bookId
         ) AS  AS mbg
    JOIN  movements AS m  ON m.movementId = mbg.movementId
    JOIN  books AS b2  ON b.bookId = mbg.bookId
    GROUP BY  mbg.bookId
    ORDER BY  b2.title

movements_books needs

PRIMARY KEY(bookId, movementId),
INDEX(movementId, bookId)


Modifying initial values for an AUTO_INCREMENT column I am using MYSQL Workbench, and I have a table called Customer and have a primary key customer_id which has the INT data type and is auto incremented ("AI" in Workbench column specifications).

This is the DDL code that workbench generates:

ALTER TABLE infrastructure.mfg
CHANGE COLUMN
 mfg_id mfg_id INT(10) UNSIGNED
NOT NULL AUTO_INCREMENT

My intentions:

I want to start the numbering at "1000" and auto increment by 100.
I want the start number to be for only this table

For example:

1st = 1000
2nd = 1100
3rd = 1200
4th = 1300

How can I do this? Do I use these functions and if so how?

LAST_INSERT_ID()
mysql_insert_id()

If this is not an option can I check the "G" rather than the "AI"?

If I check "G" the MySQL script that is generated is:

ALTER TABLE infrastructure.mfg

CHANGE COLUMN mfg_id
 mfg_id INT(10) GENERATED ALWAYS AS () VIRTUAL

Where is there information on how to use this?

MySQL docs for CREATE TABLE explain almost everything about AUTO_INCREMENT columns:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

-- irrelevant stuff omitted

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

-- irrelevant stuff omitted

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value

-- many stuff omitted

There are two AUTO_INCREMENT settings, the column attribute and a table option that can change the initial value of the auto incremented column from the default (1):

  Column Data Types and Attributes

  AUTO_INCREMENT

  An integer or floating-point column can have the additional
  attribute AUTO_INCREMENT. When you insert a value of NULL
  (recommended) or 0 into an indexed AUTO_INCREMENT column, the column
  is set to the next sequence value. Typically this is value+1,
  where value is the largest value for the column currently in the
  table. AUTO_INCREMENT sequences begin with 1.      ...

  Table Options

  AUTO_INCREMENT

  The initial AUTO_INCREMENT value for the table. ...

So, to add a modify a column with the AUTO_INCREMENT attribute that starts from a 100, you do:

ALTER TABLE infrastructure.mfg
    CHANGE COLUMN mfg_id mfg_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    AUTO_INCREMENT = 100 ;

Test in dbfiddle.uk

Further information can be found in the related pages of the documentation:

Using AUTO_INCREMENT
AUTO_INCREMENT Handling in InnoDB

Particularly in the last one above, it explains what options you have regarding changing the increment from 1 to 100. This isn't trivial:

  InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

  If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

Test 2 in dbfiddle.uk

Note that these two variables affect all the tables in the MySQL Server.


Optimising MySQL query with lots of self-joins I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved.

In some cases it can be said that one entity is a constituent of a constituency. This is stored in the entity_relationship table.

I then intend to add entities to the relationships based on this: If entity Joe Bloggs is a constituent of constituency University of Life, and the University of Life is itself a constituent of constituency Made Up Universities then I will add Joe Bloggs into the 2nd relationship as an implied-constituent.

When all the implied-consituent relationships are populated (by running the query until no records are added) I'll have a quick way to find out who's linked to what without needing to do recursion at that stage.

The entity_relationship table looks like:

+-----------------+---------------+
| Field           | Type          |
+-----------------+---------------+
| entity_id       | int(10)       |
| relationship_id | int(10)       |
| type            | enum(...)     |
+-----------------+---------------+

and has keys (currently LOADS of 'em, trying to optimise!)

+------------+----------+--------------+-----------------+-------------+------+
| Non_unique | Key_name | Seq_in_index | Column_name     | Cardinality | Null |
+------------+----------+--------------+-----------------+-------------+------+
|          0 | PRIMARY  |            1 | entity_id       |      179429 |      |
|          0 | PRIMARY  |            2 | relationship_id |      179429 |      |
|          1 | r_t_e    |            1 | relationship_id |      179429 |      |
|          1 | r_t_e    |            2 | type            |      179429 | YES  |
|          1 | r_t_e    |            3 | entity_id       |      179429 |      |
|          1 | t_r_e    |            1 | type            |           8 | YES  |
|          1 | t_r_e    |            2 | relationship_id |      179429 |      |
|          1 | t_r_e    |            3 | entity_id       |      179429 |      |
|          1 | t_e_r    |            1 | type            |           6 | YES  |
|          1 | t_e_r    |            2 | entity_id       |      179429 |      |
|          1 | t_e_r    |            3 | relationship_id |      179429 |      |
+------------+----------+--------------+-----------------+-------------+------+

And then the query I'm trying is:

INSERT INTO entity_relationship
SELECT lt.entity_id entity_id,
       py.relationship_id relationship_id,
       'implied-constituent' `type`
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt,
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id
  AND pt.type='constituent'
  AND pt.relationship_id = py.relationship_id
  AND py.type='constituency';

The problem is this is taking 42s to run (even when the query results in zero rows to insert). The output of EXPLAIN (on the SELECT) shows:

+--------+-----+----+-------+---+------------------------+-----+------------------------+
| s._type|table|type|key    |len|ref                     |rows |Extra                   |
+--------+-----+----+-------+---+------------------------+-----+------------------------+
| SIMPLE |lt   |ref |t_r_e  |2  |const                   |89714|Using where; Using index|
| SIMPLE |ly   |ref |r_t_e  |6  |lt.relationship_id,const|    1|Using where; Using index|
| SIMPLE |pt   |ref |PRIMARY|4  |ly.entity_id            |    1|Using where             |
| SIMPLE |py   |ref |r_t_e  |6  |pt.relationship_id,const|    1|Using where; Using index|
+--------+-----+----+-------+---+------------------------+-----+------------------------+

Which looks OK - except perhaps the 3rd line where it does not say Using Index.

Can anyone see a way to optimise this?

How to fix it in MySQL

As can be seen from line 3 of the EXPLAIN statement, a [edit:]covering index is not being used here. It needs to join on the entity_id and the type and no key is available for that.

ALTER TABLE entity_relationship ADD KEY e_t_r
   (entity_id, type, relationship_id);

This makes the key available, but MySQL chooses not to use it. It can be forced with: USE INDEX (e_t_r) :

SELECT lt.entity_id entity_id,
       py.relationship_id relationship_id,
       'implied-constituent' `type`
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt USE INDEX (e_t_r),
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id
  AND pt.type='constituent'
  AND pt.relationship_id = py.relationship_id
  AND py.type='constituency';

This now runs in 0.8s. (compare to 17.7s without forcing it to use that index.) This is with MySQL 5.1.63.

How to fix it in MariaDb

Well, you don't have to!

MariaDb executes the query very fast with or without the USE INDEX intervention (0.25s, but it's on a different host so I would expect this to be nearer the 0.8s in the optimised version above).

Also, MariaDb does not use the new index and is quite happy with the t_e_r index, including using it as a covering index (i.e. uses the index for the entire data fetch).

I'm getting more and more impressed with MariaDb and now considering switching.


BULK INSERT continue on PRIMARY KEY error I get a CSV file with a several million records every few days. I need to insert them into our database.  What I do is quite straightforward:
BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

This works well.  However, it may be the case that I get some duplicated records. When I run the import I, as expected, get the error:
Violation of PRIMARY KEY constraint 'PK__id__790FF7A****'. Cannot insert duplicate key in object 'dbo.activity'. The duplicate key value is (1234)

This gets quite cumbersome when instead of one duplicated record I have hundreds.  Is there any way to tell BULK INSERT to ignore (or log) those errors and carry on with the good records? I am thinking about something like:
BEGIN TRY:
  BULK INSERT dbo.activity
  FROM 'C:\tmp\activity.csv'
  WITH (options....)
END TRY
BEGIN CATCH:
  SELECT
        ERROR_NUMBER() AS ErrorNumber
       ,ERROR_MESSAGE() AS ErrorMessage;
   -- I know this line wrong.  It is just what I would like to do
  CONTINUE;
END CATCH

Thanks!

I think a good approch would be to have a staging table (without PK constraint).
You will load your data from your bulk command into this table and then, you should be able to script down the logic you want to implement into a store procedure that you can run after
Ex: Check for duplicate, log them to another table or send a mail, delete the duplicate base on some logic and then insert the remaining rows into the target table.


How to store token in postgresql? I have a column in table with type character varying  here is the
query:

update logins
set access_token='l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77' and uuid='123456789'
where username = 'PWD_2006'

It give me the error:

  ERROR:  invalid input syntax for type boolean:  "l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77"
  LINE 1: update logins set access_token='l38r8cfjm31b38ye91yjn1956vr3...
                                             ^
  SQL state: 22P02
  Character: 32

Your query is interpreted as

... SET access_token =
        ('l38r8cfjm31b38ye91yjn1956vr3n74tc5sc0btholj0iain77'
         and uuid='123456789')

That is, AND is treated as a logical operator, and the string constant is interpreted as a boolean literal, which causes the error.

Replace AND with , like the comment suggests.


How to find out why this query is in waiting state? I have a query that has been several minutes in waiting and is not running at all.

In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds?

How can I find out why this query is in waiting state?

What makes you think your session is waiting?

When a statement uses the CPU, it is not waiting. There is no wait event that accounts for using the CPU for processing. People often forget this. Your session is most likely using the CPU. There is no indication in your output that your session is waiting.

V$SESSION

  SECONDS_IN_WAIT

  If the session is currently waiting, then the value is the amount of
  time waited for the current wait. If the session is not in a wait,
  then the value is the amount of time since the start of the last wait.

You should query WAIT_TIME as well:

  WAIT_TIME

  If the session is currently waiting, then the value is 0. ...

Another method to confirm that your session is ON CPU would be querying ASH.

V$ACTIVE_SESSION_HISTORY

In ASH, the SESSION_STATE column has 2 different possible values:

  SESSION_STATE

  Session state:

  WAITING

  ON CPU

Another method would be to query V$SESSTAT (joined with V$STATNAME) an check whether the statistic CPU time increases.

You can run any CPU-bound query to test this, for example the below query uses nothing else but CPU. Start it, and you will we see SECONDS_IN_WAIT increasing, while your session is not waiting, but using CPU.

with g as (select * from dual connect by level <= 1000)
select count(*) from g,g,g,g;


Daily Value of Stock Portfolio Postgres I am working on making a database that queries the daily balance of a stock portfolio. The balance would be calculated as (sharesA x priceA)+(sharesB x priceB), etc. for each day.
I have a pricesclose table like below:
date       | ticker | close
2020-01-02 | IVV    | 326.32
2020-01-03 | IVV    | 323.81
2020-01-06 | IVV    | 325.09
2020-01-07 | IVV    | 324.20
2020-01-02 | IEF    | 110.73
2020-01-03 | IEF    | 111.47
2020-01-06 | IEF    | 111.35
2020-01-07 | IEF    | 111.19

And a transactions table:
id | ticker | transaction_type | shares | price  | transaction_date
1  | IVV    | buy              | 18     | 324.98 | 2020-01-02
2  | IEF    | buy              | 36     | 110.69 | 2020-01-02
3  | IEF    | sell             | -6     | 111.35 | 2020-01-06

What I want is:
date       | ticker | shares | close  | totalshares | marketvalue
2020-01-02 | IEF    | 36     | 110.73 |          36 | 3986.28
2020-01-02 | IVV    | 18     | 326.32 |          18 | 5873.76
2020-01-03 | IEF    |        | 111.47 |          36 | 4012.92
2020-01-03 | IVV    |        | 323.81 |          18 | 5828.58
2020-01-06 | IEF    | -6     | 111.35 |          30 | 3340.5
2020-01-06 | IVV    |        | 325.09 |          18 | 5851.62
2020-01-07 | IEF    |        | 111.19 |          30 | 3335.7
2020-01-07 | IVV    |        | 324.2  |          18 | 5835.6
2020-01-08 | IEF    |        | 110.93 |          30 | 3327.9
2020-01-08 | IVV    |        | 325.85 |          18 | 5865.3

But this is what I'm getting:

Here is my query.
SELECT
  pricesclose.Date
 ,pricesclose.ticker
 ,transactions.shares
 ,pricesclose.close
 ,(pricesclose.close * transactions.shares) AS mktvalue,
 ,SUM(shares) OVER(partition by pricesclose.ticker order by pricesclose.date)
FROM
  pricesclose
LEFT JOIN
  transactions
    ON pricesclose.ticker = transactions.ticker
        AND pricesclose.Date = transactions.transDate
ORDER BY
  pricesclose.Date

For some reason, the SUM OVER isn't totaling each ticker properly. My other issues is getting the mktvalue column to reference the sum column. I am also unsure of the best way to have the total balance in one line instead of six like it's currently doing.
Please let me know what is wrong with my SUM OVER. Or if there is a better way to execute this, I am open to that as well.


Is it a good idea to use Synonyms to avoid creating a duplicate table? We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we have to update 3 databases.

Would it be a better idea to delete the Users table from databases 2 and 3 and replace it with a Synonym that points to database 1?

Here's the Pros/Cons I can think of:

Pros

Easier Maintenance. Can update Users in one location instead of 3
User Ids would match between databases (Important since a lot of add-on apps are based on UserId)

Cons

Don't think this is standard procedure, so might be confusing
Users would have to have identical settings between databases
(From gbn's answer below) If Database 1 ever goes down, Database 2 and 3 will also be unavailable. Also there is the potential problem of the data being inconsistent in the event of a restore

This is an option I'm considering for a few different tables containing Settings that are identical between the databases, not just the Users table. I am using Users in the example since it is easy to understand.

Why do you have 3 databases with the same users?

What happens if one database goes down now?

I'm asking these questions because the Con of the user-source database going down preventing use of the other two databases may not be as big a problem as it sounds.

Additionally, if a database goes down now you will already have consistency problems if users are modified in the other two DBs during that period. I don't think we can give the best advice without more context.

Right now I would create a fourth database for users, make changes only there, and sync across the other databases. You're already distributed-denormalized by changing essentially the same data in three places, and probably already have consistency problems. If fault tolerance is important then this scheme still gives that while solving the multiple entry problem.

I think that having this fourth user/settings-only database rather than using one of your existing ones is a good strategy as it becomes less likely to go down since it isn't tied to other resources or heavily used. I see now that you said you can't do this, but I'm not clear on why. Do the applications on the main database support user editing directly, or is user editing a completely separate function that can be pointed elsewhere?

True, with this "canonical user table" idea--whether you use synonyms or synchronize the data--you can't modify users during a down user DB, but that seems ok to me. Fix the problem and bring it up! One source, one place to edit, one thing to fix if broken. With synchronization, all other databases have temporary copies they can work from, but no editing. Minimizing data duplication across systems is a great and useful goal. It is a serious problem to enter the same data in three places, so do whatever you can to eliminate that.

To address more of your comments, if your user IDs are different across all your applications, then you should seriously consider a planned down time for your two new databases to bring them in sync with the main one (ask a separate question if you need ideas on how to accomplish this, which is tricky but not really THAT hard).

If you analyze your business needs and find that the main database must have the user data that's okay, you still use it as canonical, then choose between synonyms or synchronizing to address how unplanned down times affect all the databases.

One additional Con to using synonyms is that you would no longer be able to have proper FKs to Users in each database.


Query executes fast, but keeps the table locked for some time Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table.

It creates a table with a foreign key constraint and inserts 8000 random values, with the foreign key always NULL.

The 8000 rows are inserted very quickly, and it is possible to query them very quickly.

But it's impossible to drop the table for a few seconds.

PRAGMA foreign_keys = 0;

DROP TABLE IF EXISTS Drawings;

CREATE TABLE Drawings (
    PartNumber       TEXT,
    Description      TEXT NOT NULL,
    ParentPartNumber TEXT,
    FOREIGN KEY ( ParentPartNumber ) REFERENCES Drawings ( PartNumber )
);

CREATE UNIQUE INDEX PartNumber_Idx ON Drawings (
    PartNumber
);

INSERT INTO Drawings (
    PartNumber,
    Description
)
  WITH RECURSIVE
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 8000)
  SELECT x, random() FROM cte;

PRAGMA foreign_keys = 1;

SELECT *
  FROM Drawings;

--DROP TABLE IF EXISTS Drawings;

EDIT

I just found out that disabling the foreign key check while dropping the table speeds up the operation:

PRAGMA foreign_keys = 0;
DROP TABLE IF EXISTS Drawings;
PRAGMA foreign_keys = 1;

The question is still open: why dropping a table (or performing other operations that I haven't identified yet) cannot be done immediately after populating the table above, but it can be done after leaving the database alone for a few seconds or after disabling the foreign key check?

The documentation says:

  Indices are not required for child key columns but they are almost always beneficial.
  Each time an application deletes a row from the parent table, it performs [some query].
  If this SELECT returns any rows at all, then SQLite concludes that deleting the row from the parent table would violate the foreign key constraint and returns an error.
  If these queries cannot use an index, they are forced to do a linear scan of the entire child table. In a non-trivial database, this may be prohibitively expensive.

  So, in most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index.

You should create an index on Drawings(ParentPartNumber).


Why does PostgreSQL accept junk when parsing an INTERVAL psql (11.8)
Type "help" for help.

public=# select '!@#$%^&*()00:00:00.01<>?<>:'::interval;
  interval
-------------
 00:00:00.01
(1 row)

Seems to take just about anything... I don't see anything in the docs about this...
It's kind of cool but also worrisome... can I rely on say '__00:00:00.01__' ALWAYS working?
And what are the REAL rules - the ones in the docs are not complete apparently...

The documentation is the (open source) implementation of ParseDateTime in src/backend/utils/adt/datetime.c, which is used to parse all datetime data types. Essentially, this is not documented, but "working as implemented".
As to "why", you'd have to trawl the commit log and the related discussions on -hackers, but since some of that code is old enough to vote, this might prove difficult.
Perhaps the best answer is that the datetime parsing function is deliberately lax to allow various input formats.


Oracle sql query to xml I have the below query.

SELECT DISTINCT P.PLAYERID, P.FIRSTNAME, P.LASTNAME, P.POSITION,PT.POINTS, PT.MINUTES, CALC_TENDEX(P.PLAYERID, PT.TEAMID, 2009) TENDEX, T.DIVISION, PT.TEAMID, T.TEAMNAME
FROM PLAYERS P
JOIN PLAYERS_TEAMS PT ON P.PLAYERID = PT.PLAYERID
JOIN TEAMS T ON PT.TEAMID=T.TEAMID
WHERE PT.YEAR=2009 AND T.DIVISION='East'
ORDER BY TENDEX DESC
FETCH FIRST 12 ROWS ONLY;

I want to output this query as xml, with custom format like this:

<nba dataset="topplayers">
    <division ID="East">
        <player></player>
                .
                .
                .
        <player></player>
</nba>

I have tried using SQL/XML but whatever I try does not return answers I want. Is there some easy way to directly convert it to xml?

EDIT: SQL/XML I have tried (along with various alterations of this)

SELECT DISTINCT XMLELEMENT("nba",
         XMLAGG (
           XMLELEMENT("division",
             XMLATTRIBUTES(T.DIVISION AS "ID"),
               (SELECT DISTINCT XMLAGG(
               XMLELEMENT("player",
                 XMLFOREST(
                   P.PLAYERID AS "playerid",
                   P.FIRSTNAME AS "firstname",
                   P.LASTNAME AS "lastname",
                   P.POSITION AS "position",
                   PT.POINTS AS "points",
                   PT.MINUTES AS "minutes",
                   CALC_TENDEX(PT.PLAYERID, PT.TEAMID, 2009) AS "tindex",
                   T.DIVISION AS "division",
                   PT.TEAMID AS "teamid",
                   T.TEAMNAME AS "teamname"
                  )
                )
              )
              FROM PLAYERS P
              JOIN PLAYERS_TEAMS PT ON P.PLAYERID = PT.PLAYERID
              JOIN TEAMS T ON PT.TEAMID=T.TEAMID
              WHERE PT.YEAR=2009 AND T.DIVISION='East' AND ROWNUM < 12
            )
          )
        )
       ) AS "nba"
FROM TEAMS T
WHERE T.DIVISION = 'East';

An easy way to convert SQL directly to XML, for example:

SQL> select dbms_xmlgen.getxml('select * from hr.employees where employee_id = 100')
       as xml from dual;

XML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-03</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
</ROWSET>

If you have a fixed format of your XML, you can describe it in XSLT and use XMLTRANSFORM to take care of the rest.


Why does changing the sort order return a different number of results? Is it a bug? I have a query that contains COALESCE in the select list.  If I change the order by clause of the query I get a different number of results.

-- 225 rows - with order by  desc
select coalesce(tdo.orderId, -1) , o.*
FROM dbo.[Order] o
       JOIN dbo.TradeDeskOrder tdo  ON o.orderId = tdo.orderId
       join dbo.CodeValues cv1  ON o.[type] = cv1.iCodeValue
       AND cv1.vchCodeType = 'Order.Type' AND cv1.vchSource = 'Trading.UI'
       JOIN dbo.TradeCentralAccounts am  ON o.iCfnAccountId = am.id
       JOIN dbo.TradeCentralProducts pm  ON o.iProductID = pm.iProductID
order by o.orderid desc

-- 233 rows - order by
select coalesce(tdo.orderId, -1) , o.*
FROM dbo.[Order] o
       JOIN dbo.TradeDeskOrder tdo  ON o.orderId = tdo.orderId
       join dbo.CodeValues cv1  ON o.[type] = cv1.iCodeValue
       AND cv1.vchCodeType = 'Order.Type' AND cv1.vchSource = 'Trading.UI'
       JOIN dbo.TradeCentralAccounts am  ON o.iCfnAccountId = am.id
       JOIN dbo.TradeCentralProducts pm  ON o.iProductID = pm.iProductID
order by o.orderid

If I remove the coalesce from the select list I get 233 rows regardless of sort order.  Is this a bug?  I'm using SQL 2014 SP2-CU13.

The two should be equivalent since they only differ in the ORDER BY, and this is last in the logical processing order of the clauses you use.

The physical processing order, however, can be anything the optimizer chooses. In this case I would guess (and it can only be a guess without execution plans for both queries) the DESC causes the optimizer to evaluate the COALESCE later in the plan than in the other query. This causes physical join operators to compare on NULL instead of -1. That comparison fails and so fewer rows are returned.

Anyhoo, you shouldn't get different row counts either way.


Upgrading SQL Server 2012 from SP2 to SP3 We need to upgrade our SQL Server 2012 from Service Pack 2 to Service Pack 3. We are no database experts and would like to have some info regarding this.
We are going to be doing this upgrade on the lower environments first.
Is there anything we need to specially take care of while or before doing this upgrade. We have checked for the system requirements and we are fine there.
We plan to take a database backup and just run the installation for upgrade and for the latest patches for SP3. Is that all?
Please let me know if we are missing something in here.

Thanks

1/ Microsoft recommends to test it in your UAT environment before proceeding with deployment in production environment.

2/ Backup system & user databases

3/ If you use analysis services backup Analysis Service configuration files, databases and repositories. Backup files present at location
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Data\
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Config\ directory.

4/ Make sure enough space is available on drive where resource/system databases are present. Resource database is utilized during service pack upgrade.

5/ Service pack upgrade would require a downtime of application. Make sure application does not access database during service pack upgrade. Make sure you stop all jobs and activities on database before proceeding. you don't need to shutdown database services to apply service pack.

6/ Make sure you are administrator on the system and always run the service pack executable with administrator privileges.

Although instructions for SP2 still relvant for SP3 also, please see the below source. I presume this a standalone instance?

Source: http://social.technet.microsoft.com/wiki/contents/articles/25632.how-to-install-sql-server-2012-sp2-on-standalone-sql-server-instance.aspx


B+ Tree structure with buckets (Begginer question) I am enrolled in a course and I am confused about few concepts in the structure of the B+Tree. I know what it does and what's the difference between it and BTree, but I got a bit conflicted when I saw 2 different examples:

1-B+Tree with buckets at the end:

2-B+Tree without buckets at the end:

Example 2 is the most popular example I noticed on the web.
My instructor told me that in example 2 the pointers of the leaves can point to buckets too, but wait a second it would be the same as the first example but with the last pointer to the left going to the next leaf.

Question: Is it mandatory for a b+tree leaf to have a pointer towards the next leaf?

My instructor told me to make it easier on myself to assume the bucket as a leaf that has a pointer towards the next bucket. It's better not to understand something on understanding something using a wrong way.

There's this clash inside me about the B+tree structure. Is there more than one structure for a b+tree?

It is not mandatory for a b+tree leaf to have a pointer towards the next leaf, but it is a widely used optimization to allow efficient scanning of value ranges, such as, in the context of relational databases, supporting WHERE field BETWEEN x AND y or WHERE field > x predicates. Frequently you see a leaf pointing to both next and previous leaves, supporting bidirectional scanning as well (for WHERE field < x). Apart from the search predicate support, it may also be useful for record ordering and aggregation.


slow UPDATE of rows by COUNTing from another table I try to update a table by counting rows from another table as

UPDATE t1 SET c1 = (
    SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
    )

strangely, this query stuck at Sending data state for hours.

SELECT COUNT(*) FROM t2

returns ~16M in 2 seconds.

SELECT id FROM t2 GROUP BY id

returns 50K rows in 5 seconds.

The table structures are very simple

CREATE TABLE t2
(
item int(11) unsigned NOT NULL,
id mediumint(7) unsigned NOT NULL,
PRIMARY KEY (item,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

Remove correlation:

UPDATE t1
JOIN ( SELECT t2.id, COUNT(*) c1
       FROM t2
       GROUP BY t2.id ) t3 ON t3.id=t1.id
SET t1.c1 = t3.c1;


Offline to Online database synchronization programmatically using PHP and MySQL I am working on a project in which the client requires an online as well as an offline application. Offline application data will be manually synced to online database linked to the web application.

Database is MySQL, some of the functions need to be available online as well as offline. Suppose any user who does not have internet access will use the offline application. Once he is done with his work, and has internet access, he will sync all his work/data to the online database with single click.

I know how to push the data to the online database, but the complexity here is how to maintain the indexes/primary keys of the records in sequence, without losing any data or mismatching any data.

How do I maintain the primary keys in sequence if a user syncs his offline data to online?

Plan A:  You charge your client an arm an leg to develop code to keep track of diffs, avoid dup keys, upload data, etc.

Plan B:  Multi-source replication (available at least in MariaDB).

With Multi-source, each offline laptop can be a "Master".  The "online server" is a Slave.  All writes must be done on the Master(s).  Not being able to write to the online Slave should not be an issue, just add a extra, centralized, offline Master in HQ if needed.

As for dup keys -- You must design the schema to avoid any issues like that.  This probably means no AUTO_INCREMENT or FOREIGN KEYS.  It may mean each offline Master has separate tables (or separate a database) for anything that can't live without things that will cause trouble.  But then, that means having some sort of resolution code after each connect.  (The upload is merely Replication doing its thing.)

Plan C:  NDB Cluster.  This is a significantly different Engine available under MySQL.  The set up is complex.  However, it provides a different model for replication -- "eventual consistency".  Rather than hanging on a dup key (etc), you provide rules for what to do in such situations.  These rules are automatically applied when replication leads to a conflict.

Plan D:  Galera Cluster (built into MariaDB 10.x; manually installable on MySQL) -- With this clustering technology, "nodes" can come and go; when they reconnect they automatically get back in sync.  But...  There needs to always be a quorum, that is more than 50% online.  Although you can write to any node, you cannot write to nodes that are not part of the quorum.

Plan E:  Convince the client to stay connected.  Or not do any writes when disconnected.  In this case, the offline machine can be a readonly Slave.  Writes must wait for connection to the remote Master.

Note:  In any case, you should write the application with an explicit eye to what app actions need writes versus what can be readonly.  Suggest you go as far as to have two separate connections -- one for writes and associated reads, one for plain reads.  This will facilitate whatever Plan you pick.

Which do I recommend?  I have not heard enough about your situation to say which.


Impact of changing instances' passwords As part of a security policy, the company I work for has given me the task of changing the sa's and SQL Server Agent service accounts' passwords for several instances.

What's the possible impact of doing this? Is there anything in particular I should watch out for? Is there any whitepaper, guide or even blog post about this kind of task? What kind of 'backup' plan should I have in place in case anything goes wrong?

Any guidance is appreciated.

Well for security reason you should not be letting anyone use the "sa" account. If you do then obviously changing that password will break whatever is using it. I try to set this password to a random password, rename it (only SQL 2005 and above allow this), and then disable it. I have not found anything so far that explicitly requires the "sa" account to function, my own admin account suffices.

With regards to the service account this should not cause any problems. Again as long as no other resource or service is using the same account.

The only backup plan I can suggest is having the old password handy to reset the accounts back to in the event something breaks.


Does the SQL Server backup process use a buffer cache? When a full database backup command is executed:

Does the backup process use only the memory that has been assigned
to the SQL Server (instance)?
Does the backup process "flush out" the page from the memory (buffer pool), the dirty buffer?

I want to know if it will impact the performance (regarding all the resources that it takes and its bad impact) by flushing out data.

Does the backup process use only the memory that has been assigned to the SQL Server (instance)?

The memory that you assign with MIN and MAX memory is just for Buffer Pool and for SQL Server 2012 and up, the memory manager has changed.
When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The target is commonly 4 MB for each buffer resulting in approximately four to eight buffers.
Don't do it on PROD, just for your own learning benefit:
You can find out how much memory your backup is taking:
TRACE FLAG 3605: This trace flag will send the trace to SQL Server error logs.
TRACE FLAG 3213: This trace flag gives information about backup and restore throughput and other configurations.
Below is from my test server:

Does the backup process "flush out" the page from the memory (buffer pool), the dirty buffer?

Yes, as it forces a checkpoint to occur. A full backup will force a database checkpoint which flushes all updated-in-memory pages to disk before anything is read by the backup.
If you want to estimate the amount of total buffer memory that a particular full database backup to a physical disk would use:
-- Estimate the amount of total buffer memory that a particular full database backup to a physical disk would use
-- Reference: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspx
declare @MaxTransferSize float,
@BufferCount bigint,
@DBName varchar(255),
@BackupDevices bigint
-- Default value is zero. Value to be provided in MB.
set @MaxTransferSize = 0
-- Default value is zero
set @BufferCount = 0
-- Provide the name of the database to be backed up
set @DBName = 'entities' --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<----- CHANGE HERE YOUR DB NAME !!!
-- Number of disk devices that you are writing the backup to
set @BackupDevices = 1
declare @DatabaseDeviceCount int
select @DatabaseDeviceCount=count(distinct(substring(physical_name,1,charindex(physical_name,':')+1)))
from sys.master_files
where database_id = db_id(@DBName)
and type_desc <> 'LOG'

if @BufferCount = 0
                set @BufferCount = (@BackupDevices*3) + @BackupDevices + (2 * @DatabaseDeviceCount)

if @MaxTransferSize = 0
                set @MaxTransferSize = 1

select 'Total buffer space (MB): ' + cast ((@Buffercount * @MaxTransferSize) as varchar(10))

Refer to:

How It Works: What is Restore/Backup Doing?
How It Works: SQL Server Backup Buffer Exchange


Several options such as:

SELECT (SELECT SUM(follow) FROM fb WHERE name = 20)
    + (SELECT SUM(follow) FROM tw WHERE name = 20);

Or:

SELECT SUM(follow) FROM (
    SELECT follow FROM fb WHERE name = 20
    UNION ALL
    SELECT follow FROM tw WHERE name = 20
) as u;

The query in the question has one problem:

SELECT tw.name,fb.name,SUM(fb.follow+tw.follow)
FROM fb, tw
WHERE fb.name = 20 AND tw.name=20;

values are counted twice because fb CROSS JOIN tw (i.e. fb, tw) duplicates each follow value

This is the result of the CROSS JOIN query without SUMafter duplication:

id  name    follow  id  name    follow
1   20      1       1   20      3
2   20      2       1   20      3
1   20      1       2   20      4
2   20      2       2   20      4

Note that values (all 1) have been replaced by 1, 2, 3, 4 because it is easier to see the problem.


Small MySQL table swap/replace taking incredibly long time We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL:

RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging TO user_accounts, user_accounts_temp TO user_accounts_staging;

Aside from writing to the staging table during the import, the tables are only read by our application (no other writes). The staging/production tables are always around same size (~40k rows). All reads to the staging/production table queries are by ID. There are no FKs in the tables.

The mystery is that the time to do the RENAME takes between 0sec-8min (usually it's 0sec). But in the latter case, it blocks reads to the table causing our production site to hang.

How can I fix/understand what the problem is?  My understanding of what I've read about the rename operation is that this should always be nearly instant, and this is the best practice for doing periodic imports.

Rename table requires a LOCK on the table to complete.

In your 0sec case there were no connections using the table and it works immediately.

In you 8 min case, The RENAME table waits in a stage something like "waiting for table metadata lock" until the existing SELECTS are done. Also the SELECTs that are issued after the RENAME waits on the lock. Hence the hang you notice.

You could run show full processlist when this occurs to confirm.

To avoid this, you will have to time the rename in the same app that is reading or kill the existing connections that are holding the locks.

Perhaps the SELECT queries need to be tuned as well. 40K row table shouldn't take that long to return. Since you mentioned all the queries are by ID, is there an INDEX on ID?


Single versus Multiple databases I am working on a hospital management system using DB2 with pureXML.

Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use multiple databases in order to be scalable?

Designing a platform: one database or multiple databases? is relevant background reading for your question.

You're possibly approaching this from the wrong angle. Is there really going to be a single, all mighty, all encompassing, one true system called "THE Hospital Management System"? Or will there be:

Appointments Management System
Patient Records Systems
Medicines/pharmacology database (highly likely to be something bought in, rather than re-invented)
... ?

I'm sure there are dozens of other applications to add the list. Each of these is a separate application and probably a separate database, all of which need to talk to each other via a defined interface. One big application with one big database is a fast road to chaos.


MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST I'm trying to figure out why the reporting of slave lag is different in SHOW SLAVE STATUS and SHOW PROCESSLIST, in MySQL 5.5.13.
The only difference to other slaves of this master is that it is replicating over a relatively slow connection, about 10Mb/sec (cross site).

SHOW SLAVE STATUS reports a slave lag of 0, or occasionally, the real slave lag, once every 5-10 requests. (I'm looking at Seconds_Behind_Master)

SHOW PROCESSLIST, shows the slave lag under the system_user's time column (the one that belongs to the replication sql thread), like so:

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 63953
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 61077
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

Why would these differ? Or rather, why would SHOW SLAVE STATUS lie? The monitoring system is looking at this command, and goes crazy due to "spikes" once in a while.
I know for a fact the right number is what SHOW PROCESSLIST reports, since the slave took a good few days to be initialized, and is catching up slowly.

SHOW PROCESSLIST, shows the slave lag under the system_user's time
  column (the one that belongs to the replication sql thread)

No. It's the time that the Slave sql thread has been in its current state.


PostgreSQL ORDER BY clause effect different number of rows to return in pivot table I've worked on "Seven Databases in Seven Weeks" recently, and when implementing the the task with building a pivot table using crosstab() - the task was already described here: https://stackoverflow.com/questions/34665186/creating-a-calendar-like-table-using-crosstab-in-postgres?rq=1

I faced kind of PostgreSQL "magic" for ORDER BY clause.

The right query is:

SELECT * FROM crosstab (
  'SELECT extract(week from starts) AS week,
     extract(dow from starts) AS day, count(*)
   FROM events
   GROUP BY week, day
   ORDER BY week',
  'SELECT generate_series(0,6)'
) AS (
week int,
Sunday int, Monday int, Tuesday int,
Wednesday int, Thursday int, Friday int, Saturday int
) ORDER BY week;

The data in the table the following:

It will return this data:

If I were to omit the ORDER BY week in first query in crosstab() function, it's will return 12 rows instead of 10.

SELECT * FROM crosstab (
  'SELECT extract(week from starts) AS week,
     extract(dow from starts) AS day, count(*)
   FROM events
   GROUP BY week, day',
  'SELECT generate_series(0,6)'
) AS (
week int,
Sunday int, Monday int, Tuesday int,
Wednesday int, Thursday int, Friday int, Saturday int
) ORDER BY week;

Output:

There are three strange things here:

first is that when to run EXPLAIN VERBOSE on both of the queries, it show the completely identical QUERY PLAN except ORDER BY clause;
when to run the sub-queries separately it will return 12 rows for both;
ORDER BY clause should impact on the order only.

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Sort  (cost=59.83..62.33 rows=1000 width=32)
   Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
   Sort Key: crosstab.week
   ->  Function Scan on public.crosstab  (cost=0.00..10.00 rows=1000 width=32)
         Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
         Function Call: crosstab('SELECT extract(week from starts) AS week,
      extract(dow from starts) AS day, count(*)
    FROM events
    GROUP BY week, day
    ORDER BY week'::text, 'SELECT generate_series(0,6)'::text)
(10 rows)

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Sort  (cost=59.83..62.33 rows=1000 width=32)
   Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
   Sort Key: crosstab.week
   ->  Function Scan on public.crosstab  (cost=0.00..10.00 rows=1000 width=32)
         Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
         Function Call: crosstab('SELECT extract(week from starts) AS week,
      extract(dow from starts) AS day, count(*)
    FROM events
    GROUP BY week, day'::text, 'SELECT generate_series(0,6)'::text)
(9 rows)

How to explain this behvaior? The closest question was this, but it's not the same I think: Why does changing the sort order return a different number of results? Is it a bug?

Thanks in advance.

Omitting the ORDER BY goes against what the documentation recommends:

  In practice the source_sql query should always specify ORDER BY 1 to
  ensure that values with the same row_name are brought together

This ORDER BY is a requirement of the algorithm inside crosstab() because it's counting on identical values being contiguous. What happens inside crosstab() is a black blox as far as the executor of the main query is concerned. It doesn't know and doesn't care that this algorithm is sensitive to the order of the rows inside crosstab. To start with, it doesn't know that the arguments passed to crosstab() are queries. These queries are executed by a different instance of the executor, as dynamic SQL. It seems that your reasoning in the question is ignoring that, which is why you see properties in this query that are unusual in normal SQL queries, which don't involve embedding dynamic SQL.


Illegal XML character out of the extended event I'm capturing extended events in SQL server. Trying to analyse the trace - converting the event data to xml an error comes up:

  Msg 9420, Level 16, State 1, Line 28
  XML parsing: line 1, character 731, illegal xml character

Investigating the issue, I find that there is a character of ascii number 3 in the sql_text action value. This value is passed by a client in form of dynamic SQL. This is of little concern here. The problem is that XE cannot handle weird characters in sql_text.

This is how I get to the data causing the trouble:

declare @data nvarchar(max)
declare @id int
SET NOCOUNT ON

if object_id('tempdb.dbo.#xerrors') is null --drop table #xerrors
begin
    select CAST(null as XML) as xml_event_data, *
    into #xerrors
    from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet',
                '...\Log\error_*.xem',
                null,
                null
            )

    alter table #xerrors
    add id int identity(1,1) primary key not null

end

while exists(select 1 from #xerrors where (@id is null or ID > @id) and xml_event_data is null)
begin
    select top 1 @id = id, @data = event_data from #xerrors where (@id is null or ID > @id) and xml_event_data is null order by id

    print @id

    update #xerrors set xml_event_data = CONVERT(xml, event_data) where id = @id

end

select ascii(SUBSTRING(event_data, 731, 1)), event_data from #xerrors where id = 11253

And my session setup is:

-- TRIES TO ELIMINATE PREVIOUS SESSIONS
if exists(select 1 from sys.dm_xe_sessions xes where xes.name = 'error_capture')
      DROP EVENT SESSION error_capture ON SERVER
GO

-- CREATES THE SESSION
CREATE EVENT SESSION error_capture ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION (
        sqlserver.tsql_stack,
        sqlserver.sql_text,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.client_app_name,
        sqlserver.is_system,
        sqlserver.nt_username,
        sqlserver.username,
        sqlserver.transaction_id,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.database_context
    )
    --actions
    WHERE Severity > 10
)
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = '...\Log\error.xet' , metadatafile = '...\Log\error.xem')
GO

-- STARTS THE SESSION
ALTER EVENT SESSION error_capture ON SERVER STATE = START

Do you consider this a bug in the XE framework or am I doing something wrong? I believe all events should be valid XML...

The file paths have been intentionaly shortened

Since there are no takers yet, I offer a guesswork answer: Yes, I think it's a bug.

Workaround: replace the illegal character once you know which one it is before converting the data to XML like this:

select xml_event_data = CONVERT(xml, replace(event_data,CHAR(3),'&#3;')), *
from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet',
                '...\Log\error_*.xem',
                null,
                null
            )


Are there any potential problems with running distinct Oracle rman archivelog backups of the same database at the same time? The scenario is that RMAN archivelog backups are scheduled to tape (TSM) every 2 hours. The scheduling ensures that such backups are run in a mutually exclusive fashion.
A separate crontab based mechanism has been put in place (by an unrelated team) with the result that "identical" RMAN archivelog backups are sometimes run at the same time.

Are there any potential problems with this, as regards to subsequent restore operations?

Normally RMAN takes care of this. It will only make a backup of the archivelog files of which it does not have a backup.

The restore just reads the archivelogs that it needs from tape.


return percentage of records based on value in column I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column.  The percentage is given by the user.  A stripped down version of the query is below

SELECT OrganizationID,
       CustomerID,
       OrderID
  FROM x

The output would look something like

 OrganizationID, CustomerID, OrderID
  123             abc         p456
  123             cba         p457
  123             bac         p458
  456             def         p987
  456             efd         p988

I need to be able to pull say 50 percent of the output for each organizationid. So in this example I'd come back with 2-3 records.  Which records doesnt matter as long as the correct percentage of the records is pulled.

Any ideas on how to go about this?

;WITH CTE
     AS (SELECT OrganizationID,
                CustomerID,
                OrderID,
                COUNT(*) OVER (PARTITION BY OrganizationID) AS C,
                ROW_NUMBER() OVER (PARTITION BY OrganizationID
                                       ORDER BY (SELECT 0))     AS RN
         FROM   YourTable)
SELECT OrganizationID,
       CustomerID,
       OrderID
FROM   CTE
WHERE  RN <= CEILING(0.5 * C)

SQL Fiddle


db table column design question I have a database that will track "location" in a building.

Location1 contains country data.
Location2 contains building data.
Location3 contains room data.

Each location table has a FK to the parent table.  So for example, I have data that looks like this:

 Location1
 id   | name          | description
 ----------------------------------------------
 1    | United States |

 Location2
 id   |  loc1_id   | name          | description
 ------------------------------------------------------------
 1    |   1        |  buildingx    | building x in new york

 Location3
 id   |  loc2_id   | name          | description
 ------------------------------------------------------------
 1    |   1        |  5-125       | 5th Floor Room 125
 2    |   1        |  1-12b       | 1st Floor Room 12b

In my widgets table, i now want to specify where each widget is located.
I don't really want to have 3 columns, one for each location table.
I was thinking of saving values like this:

 widgets
 id   |  loc_id   | name
 -----------------------------------
 1    |   1.1.1    | test widget
 2    |   1.1.2    |  my favorite widget

Does anyone have any comments on the advantages / disadvantages of this approach?

1.1.2 as a location seems very arbitrary.  Your structure will not allow you to do much dynamic reporting and scaling up would cause issues. Every time you need to query up the chain, you will need to break the item up, which will break your index.

1.1.1 may refer to NY Fl 5, but 1.1.2 could refer to LA (los angeles, Louisiana,???) floor 4.

TBL1 - 1, USA
TBL2 - 100, STATE, TBL1.FK
TBL3 - 1000, CITY, TBL2.FK
TBL4 - 10000, BUILDING, TBL3.FK, May want to also include ZIPCODE here.
TBL5 - 100000, FLOOR, TBL4.FK

In you Data warehouse, you could flatten it, but you might want to keep separate Dimension tables for cube reporting.


When setting up an AlwaysOn Availability Group, should the master database be one of the "Availability Databases"? I just got a Sql Server 2016 Availability Group setup (A Primary, a Syncronous Secondary and a Async Secondary).  I have five databases on the servers.

I setup an application to run queries against the server (using Entity Framework with a "failover partner" connection string.)

When I tried to test the failover by rebooting the primary server, I got the following error:

  Server MySecondaryServer, database master is not configured for database mirroring.

It seems odd to mirror a system database like this, so we did not have it setup as one of the "Availability Databases".

So, here is my question: Should I add the master database to the list of "Availability Databases" and will it cause problems if I do it?

It is not possible to include master as an Availability Database within an Availability Group (AG). System databases (master, model, msdb, tempdb) cannot be part of the Availability Group.

System databases are instance-level objects. They are needed in order for the instance to be online & available. Availability Groups are a level below that, and provide database-level replication from one instance to another (or several others).

SQL Server will prevent you from adding system databases to an AG because those databases need to be read-write on every instance in order for the instance to be online.

Additionally, as stated in the answer on your other question, you should not be using the failover  partner connection string attribute. That attribute is designed for use with database mirroring--a different, deprecated availability feature. Instead, you should use an AG Listener, which will also provide proper direction to the primary node, and also allow you to use other AG-specific features, like read-only routing.

Getting back to your error....

Your code probably shouldn't rely on the master database, particularly in an AG, where your database could move to a different instance with a different master database. In the interest of the simplest & most bullet-proof AG architecture, I generally recommend that you go out of your way to make your database self-contained, or make the intentional, educated decision to put something outside of the database.

My first guess (and not knowing more about your code or your connection) is that your query shouldn't be referencing master at all. It could be just that your connection string is landing you in the master database instead of the database you want--so fix your code or connection string, not your AG.


MySQL - Manipulating MyISAM files directly brings down entire database Lately, we have noticed (seemingly) random slowdowns on our system, where an innocuous query like

SHOW CREATE TABLE tableName; or SHOW COLUMNS FROM tableName will take upwards of 20 minutes, being stuck in a state like waiting for table meta data lock.

Another common one is a simple select statement like SELECT * FROM someTable WHERE id=123 will take a similarly long time, being stuck in a state like sending data.

Our server is pretty busy, so something like this can easily cause us to exceed our 500 connections limit, which then makes the server crash.

After some headscratching, we have figured out that these issues seem to be coming up when the MyISAM files (*.MYI, *.MYD, *.frm) are being manipulated directly. This is a common occurence as we have several crons that move data back and forth between servers by copying the MyISAM files from one server directly to the /var/lib/mysql/dbname folder on another server as a temp table, then rename the temp table to the name of the main table, and then running a FLUSH TABLE tableName on the destination server. When the files are being manipulated directly on the command line or via a cron (ie, while a file is being copied or unzipped into the destination directory), this is when problems occur.

We have been able to test and recreate this repeatedly, where something like rming a file or even running ls can bring the system to a halt.

So, my main question is: Is this standard documented behavior? Is it always a no-no to manipulate the MyISAM files directly? (Not counting command line tools like myisamchk, but specifically standard file operations on the command line such as cp and rm). I don't remember seeing this in the documentation, and I have even seen this as a recommended way of transfering data (https://stackoverflow.com/questions/879176/how-to-recover-mysql-database-from-myd-myi-frm-files).

The secondary question is: What is the recommended way of sharing data between servers? One way would be to use mysqldump to export and mysql to import, but the reason we are moving the MyISAM directly is because it is (seemingly) a lot faster and doesn't require that extra space for dump files.

Another idea is too keep the process as is, but copy the MyISAM files to a seperate database, and then integrate them into the main database by using RENAME TABLE tempDB.tableName TO mainDB.tableName. Would that make a difference since the files being manipulated belong to another database?

Also noting that we have been doing this for over a year, but problems have been more noticeable in the last month.

EDIT: Note that the files copied from the origin server are guaranteed not to be written to while the copy takes place. That is not the case for the destination server, where the issue is happening. This question (Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)) may be related to mine except it seems to be concerned with the data on the origin server, not the destination server.

Using MySQL 5.5.25 Planning to upgrade to 5.7 soon but can't yet

You had setup MySQL Replication, you could achieve the following:
SLAVE
You could do the following on the Slave in Session #1
mysql> STOP SLAVE SQL_THREAD;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> DO SLEEP(86400);

NOTE: Doing STOP SLAVE SQL_THREAD stop SQL processing but continues collecting binary log events from the Master.
Open another session (Session #2) and copy the .frm, .MYD, and .MYI files to your heart's content as you stated in the question.
When you are done copying the files from the Slave to the target server, simply kill the sleep session in Session #1 and mysql session. Log back into mysql and run
mysql> START SLAVE;

Doing it this way assures that all the MyISAM tables are from the same point-in-time.
MASTER
The Master can keep on going with all the reads and writes.


Reuse subquery as in another result In a typical social-network-like application I have the following query that gathers news from a set of followed channels.
The main query gets the news, while the subquery gets the followed channels:

QUERY 1: Get latest news:

select n.id, n.title, n.description
from community.news n
where n.id_channel in (
       select id_channel from community.user_follows uf
       where uf.id_user ='3d4c788e0e34657457febba2040297c7'
)

The app needs this table of recent news, but also needs the followed channels, so I execute the subquery again:

QUERY 2, same as the subquery:

select id_channel from community.user_follows uf
where uf.id_user ='3d4c788e0e34657457febba2040297c7'

Can I optimize this? Both queries are always executed in sequence. Is it worth to use temporary tables or something like that? Or is there a way to access a subquery result?

Or is there a way to access a subquery result?

Sure there are ways, but only in the same query at the next higher level.
You can extend the scope with a CTE, but still limited to the same query (anywhere).
To preserve a derived table across queries in the same session, materialize the result in a temporary table.
Or a regular table (maybe UNLOGGED?) to extend the scope to all sessions.

What's reasonable depends on the details of your installation.

Ideally, you do it all in a single query, in a single query level, even. The difficulty is the return type. You have to combine columns, possibly creating some redundancy in the result. You can either reduce query time or reduce redundant data in the result, but not both. Combining everything into a single JSON column might be an alternative.

To get everything in a single query:

SELECT n.id, n.title, n.description, id_channel
FROM   community.user_follows uf
JOIN   community.news         n USING (id_channel)
WHERE  uf.id_user ='3d4c788e0e34657457febba2040297c7';


What's the procedure to set up username/password on mongodb? I have tried setting up username / password .But none of the methods seems to work.

1. In mongod.conf ----> remove **#auth=true** to **auth =true**

2.Started the mongod server --> sudo service mongodb restart

3. Created the user in --> use admin -->db.createUSer()
Followed these procedure still not able to figure out how it works its not working.

mongod --auth

But then I try to connect its opening with out any username password error.

I have checked many documentations and answers but was still not able to figure out.

Create an admin to create users:

> use admin
> ( rs.initiate() )
> db.createUser({user:"siteUserAdmin",pwd:"password",roles:[{role:"userAdminAnyDatabase",db:"admin"}]})

Login as userAdmin:

$ mongo -u siteUserAdmin -p password --authenticationDatabase admin

Create an admin for the cluster:

> use admin
> db.createUser({user:"replSetManager",pwd:"password",roles:[{role:"clusterManager",db:"admin"}]})
> db.auth(replSetManager,password)
> rs.status()  -> ok

Create a user that can access all databases but not admin:

> use admin
> var w = { user:"will", roles:["readWriteAnyDatabase"], pwd:"abc"}
> db.createUser(w)

Create a user only for the test database:

> use test
> var a = { user:"joe", pwd:"asdf", roles:["readWrite"]}
> db.createUser(a)

Many roles can be combined and you can create super admins. Information on all the built-in-roles: http://docs.mongodb.org/manual/reference/built-in-roles/

(You can build your own roles also if needed: http://docs.mongodb.org/manual/tutorial/manage-users-and-roles/#create-user-defined-role)


Set PK as a combined string of table 1 and 2 records Say, for example, I have 2 tables - Table 1 is Drink Type and Table 2 is Flavor. What is the query to create the unique identifier column which would result to drink_type_drink_flavor?

+-------------+-------------------+
| drink_type  | drink_flavor      |
+-------------+-------------------+
| Fruity      | Apple             |
| Coffee      | Caramel           |

Expected PK for above would be Fruity_Apple and Coffee_Caramel

I think you just need a proper UNIQUE or PRIMARY KEY constraint:

ALTER TABLE drink
    ADD CONSTRAINT drink_pk
        PRIMARY KEY
        (drink_type, drink_flavor);`

If you often want the concatenation of the 2 columns in query results, you can use a view or a generated column (depending on MySQL version).


Why is Oracle using the index here? Name     Null     Type
-------- -------- ---------
ID       NOT NULL NUMBER(4)
GROUP_ID          NUMBER(4)
TEXT              CLOB

There is a btree index on group_id. Here's how many rows each group_id has and the corresponding percentage:

GROUP_ID               COUNT                  PCT
---------------------- ---------------------- ----------------------
1                      1                      1
2                      2                      1
3                      4                      3
4                      8                      6
5                      16                     12
6                      32                     24
7                      64                     47
8                      9                      7

I ran this

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST', cascade=>true);

Which, if I understand correctly, will gather stats for the optimizer.

Now, I remember seeing that Oracle will not use the index and perform a full table scan instead if it's retrieving more than 5% or so of all rows. However, when I ran this query, it only started performing a FTS when group_id was 7, which has 47% of all rows.

Is this the way it's supposed to be?

Oracle also tracks the number of blocks that will be retrieved using the index values.  It may be your data is clustered by group_id.  In that case, it may make sense to use the index when retrieving more than 5% of the rows.  If the data is sufficiently clustered for values 5 and 6, then using the index may be roughly equivalent to doing full table scan of a few blocks.

The relevant calculation will involve the number of index blocks needing to be retrieved, the number of data blocks needing to be retrieved, and the CPU required to access the data.  There are tuning parameters which adjust the relative costs of retrieving index versus data blocks.  This can change the plan that is being used.


Scalable table structure for periodically updated stats that get aggregated over time I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later.  For example, every 30 seconds I could receive the number of oranges sold at a store in the last 30 seconds.  Later, I want to retrieve this data from the database and use it to generate charts showing information like the number of oranges sold for a store over the last 24 hours, last x weeks, last x months, and last x years.

If I just dump everything into one table, it seems like it would grow very quickly, especially if you have lots of data sources (stores).  My thought was that the data could be averaged so that it was less granular over time.  That is, keep detailed records over the last couple of hours (entries in the DB for every 30 seconds), then perhaps averages of 15 minute time spans for the last few weeks, then keep averages of each day for the last few months, etc.

This way you have a large number of recent records, a good number of relatively older records, and a few old records.  However all the data is still there, it's just summed up and averaged into one entry over days or months instead of 30 seconds.

Does this approach make sense?  Is there a better approach?  How would I organize this into a table?  Would it be multiple tables?  Is SQL (probably MySQL) a good fit or would something work better?  Any thoughts on this would be greatly appreciated!

I'm frankly always nervous about throwing away details.  For that reason I'd try to find a way to keep handy (or at least archive) the most granular data.  That way, if your summarization requirements change, you can always go back to the source data and recalculate appropriate summary statistics.

As to your approach of calculating averages over varying times at various ages, this is a fair practice that would meet the needs of many organizations, to a point.

While it may certainly be true that you don't care about per 30 second periods after a few weeks, let alone one year from now, at what point do you stop increasing the reporting period?  Do you stop at days or do you go to weeks?  (or months? or quarters?)

Once you get past days, you run into issues with doing year over year comparisons, which many organizations rely upon.  I have seen some datawarehouses where there are multiple periods precalculated.  Whether you do this or just pick one "longest period" e.g. days, is a matter of how you trade off quick access and redundant data.

Regarding "How would I organize this into a table?" the best way would be to have starting and ending date/times (down to the second) for the period being represented, along with the average count over that period.  For convenience, you might also include a partitioning attribute that describes the length of the period, e.g. 'm', 'h', 'd', 'w', 'M', 'q', 'y' or whatever makes sense for your summaries.


SQL Server 2019 performance worse than 2012... am I missing something? We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs. Both have 180GB RAM and 16 processors.

However there are a few key differences.

The 2012 database server is Enterprise, the 2019 is Standard. As far as I know, this shouldn't make a difference
The 2012 database was restored to the 2019 server and it's version changed to 150 (2019)
MAXDOP on the 2012 server was 0, 2019 server it is set to 8 as recommended by Microsoft and others
Cost threshold for parallelism = 5 on 2012 server, 20 on 2019 server

EDIT: Another major difference I just realised - one is Windows Server 2008,the other is Windows Server 2019. It could possibly be server side settings as well...

Allocation unit size is set to 64kb for all SQL disks, SQL has the right permissions to be able to control file sizes itself. Server is set to high performance mode. Anything else I should be changing server side?

Other database settings were not changed, so the following settings are default on 2019, I believe:

Legacy Cardinality Estimation = OFF
Parameter Sniffing = ON
Query Optimiser Fixes = OFF

Mainly the type of queries we do are large complex multi join queries performing updates and inserts, with the occasional small selects from users. We load large files to the database and then process the data in large queries, usually one at a time. In between these large "loads" we have users doing selects on other database tables not being loaded/processed in preparation for future load/process steps. Generally we are getting between 30%-50% performance reductions in processing.  I figured this was because of the MAXDOP setting, but altering it to 0 made no difference over a series of runs.

Our major symptom is we are getting lock timeouts when we try to connect to the 2019 server while it is busy processing, whereas the 2012 server still services connections, just very slowly. I was thinking of setting the connection timeout setting on the server to a high amount, however I suspect we still won't get responses from the server. It's like it's blocking all new connections if its even slightly busy.

Are there other things I should try? Are those database settings worth messing around with?

I could dive in further and start looking at DMVs, however this seems to be close to a "like for like" environment upgrade with considerable drops in performance. Just checking there isn't something else I should check before doing a bigger investigation.

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level.

Change the Database Compatibility Level and use the Query Store
If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher database compatibility level with:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;


How to automatically rename columns from same table join? When I join two tables to avoid conflicts I use column aliases. But that is prone to errors if there a lot of columns. Also, some ORMS require to use hardcoded or dynamic prefixes to column names. Is there automatic way to rename columns in such a way that all columns from T22 start with "c_t22_" and all columns from T23 start with "c_t23_"?

select T1.id,
       T1.p1, T21.name as p1_name,
       T1.p2, T22.name as p2_name,
       T1.p3, T23.name as p3_name
from T1
join T2 as T21 on T1.p1 = T21.id,
join T2 as T22 on T1.p2 = T22.id,
join T2 as T23 on T1.p3 = T23.id

SQLite is designed as a small, embedded database to be accessed from a 'real' programming language, so it has no built-in mechanism for dynamic SQL.

You have to construct the SQL query from inside that other programming language; something like this:

cols = []
for tab in tables:
    for col in columns:
       cols.append("c_{}_{}".format(tab, col))
sql = "SELECT " + ", ".join(cols) + " ..."
execute(sql)


How can I mysqldump all databases except the mysql schema? I want to backup all 200+ databases on a MySQL server to all-dbs.sql.

I want to exclude the mysql schema.

How can I do that?

You need to collect all the database names into a space delimited list. Use that for mysqldump

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql

Give it a Try !!!