On-premesis SQL Server setup for MFA against Azure AD I am trying to set up multi-factor authentication in SSMS using an on-premesis SQL Server (Standard Edition).  I believe the SSMS is simple enough.  Under the "Login" tab, I have chosen "Active Directory - Universal with MFA support" and have my user name (like "john.doe@example.com").

Under "Connection Properties," I put in a specific database and my Azure AD tenant ID (guid):

When I try to log in, I get a popup from Microsoft, which I assume is the SSO login:

However, when I put in my password, I get error 18456, State 1:

I assume this is because I need to add the user on the server side.  However, I have not been able to find how to do that for an on-premesis SQL Server (there are plenty of guides for Azure SQL).  I am using Standard Edition 2017.  What do I need to do to complete the setup?

I am trying to set up multi-factor authentication in SSMS using an on-premesis SQL Server (Standard Edition).

"Active Directory - Universal with MFA Support" is an Azure Active Directory authentication method.  SQL Server (on premises) doesn't support Azure Active Directory Authentication; it only supports SQL Auth and Windows Auth (NTLM and Kerberos).


Large scale, hard to model design advice needed I would love to hear some opinions or thoughts on a MySQL database design.

Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of these systems is unique, and will be reporting unique data.

The data sent can be categorized as frequent, and infrequent data. The infrequent data is only sent about once a day and doesn't change much; it is basically just configuration based data.

Frequent data is sent every 2-3 minutes while the system is turned on, and represents the current state of the system.

This data needs to be stored in a database for each system, and be accessible at any given time from a PHP page. Essentially for any system in the field, a PHP page needs to be able to access all the data on that client system and display it. In other words, the database needs to show the state of the system.

The information itself is all text-based, and there is a lot of it. The config data (that doesn't change much) is key-value pairs and there are currently about 100 of them.

My idea for the design was to have 100+ columns, and 1 row for each system to hold the config data. But I am worried about having that many columns, mainly because it isn't too future proof if I need to add columns in the future. I am also worried about insert speed if I do it that way. This might blow out to a 2000 row x 200 column table that gets accessed about 100 times a second so I need to cater for this in my initial design.

I am also wondering, if there are any design philosophies out there that cater for frequently- and seldom-changing data based on the engine. This would make sense as I want to keep INSERT/UPDATE time low, and I don't care too much about the SELECT time from PHP.

I would also love to know how to split up data. If frequently changing data can be categorised in a few different ways should I have a bunch of tables, representing the data and join them on selects? I am worried about this because I will probably have to make a report to show common properties between all systems (i.e. show all systems with a certain condition).

I hope I have provided enough information here for someone to point me in the right direction, any help on the matter would be great. Or if someone has done something similar and can offer advice I would be very appreciative.

Store the data in flat text files which are uniquely named based on each system, not in a database.

Databases (particularly relational ones) excel at storing data in tables with a consistant schema, relating those tables to one another and querying them in arbitrary ways. What you are describing is (based on my reading) basically a web page to display text files. There is only one way to access the data (via what is effectively a unique key, the system name), there don't appear to be any relationships, and the data will vary considerably from system to system (hence your issue with lots of columns).

So I don't see much benefit in using a relational database (a document database such as RavenDB, CouchDB or MongoDB may be a better fit, but even they are likely to be overkill for what you describe).

If you're really keen on using MySQL, just store the contends of the file in a table you can lookup via a name:

system_name varchar(255) NOT NULL Primary Key
file_type char(2) NOT NULL = 'F' or 'IF' (for frequent or infrequent)
data Longtext NULL

And parse your textual data in PHP (or simply display it).

You could store your data exactly as you receive it, or process it into XML, JSON or YAML to standardise it.

I just noticed the show common properties requirement.

Perhaps you could have columns in your table for those common properties, but not all the properties. You can quickly query the common properties while still being able to view the full data. (I don't have direct experience with any of the above document databases, but I believe all of them will let you index arbitrary fields in your documents, which will be much more flexible than a fixed number of columns).

If you really want to stay with MySQL, you may want to look into the Entity-Attribute-Value pattern. (IMO, EAV is an anti-pattern, but it should be OK just for the common properties).

Of course, if display time isn't really a big worry, just opening and parsing 1000 text files isn't going to take too long (I'd expect ~10 seconds).


Procedures In Oracle --Procedures Exercise:
create or replace procedure Display
is
cursor ABC is
select empno, ename, sal
from emp
where deptno=10;
emp_rec ABC%rowtype;
Begin
Open ABC;
Loop
fetch ABC into emp_rec;
exit when ABC%notfound;
end Loop;
dbms_output.put_line(emp_rec.empno||'   '
||emp_rec.ename||'  '||emp_rec.sal);
end;
/

When I execute the following code there is only one output shown for deptno=10, while deptno=10 contains 3 records.

You are only printing the last row, not every row:

You need to move the dbms_output inside the loop:

create or replace procedure Display
is
  cursor ABC is
    select empno, ename, sal
    from emp
    where deptno=10;

  emp_rec ABC%rowtype;

Begin
  Open ABC;
  Loop
    fetch ABC into emp_rec;
    exit when ABC%notfound;

    --- the output needs to be here
    dbms_output.put_line(emp_rec.empno||'   '||emp_rec.ename||'  '||emp_rec.sal);
  end Loop;
end;
/


Postgres: "show max_connections;" output a different value from postgresql.conf file I'm trying to increase the max_connections value from the config file, but the show max_connections; command always shows 100.
I restarted postgres after changing the conf file,
I have modified the correct config file which is displayed by the command: show config_file;

As the current value is stored in postgresql.auto.conf, it was changed using ALTER SYSTEM and thus has priority over any value in postgresql.conf.
You can either use ALTER SYSTEM again to increase the value:
alter system set max_connections = 250;

which will adjust the value stored in postgresql.auto.conf
Or you can remove the entry from postgresql.auto.conf by using:
alter system reset max_connections;

Then the value from postgresql.conf will be used.
In either case, you have to restart Postgres to apply the new value.


Find the last SQL statement executed for a given connection MySQL running on Windows 7 64 bit.

I'm trying to track down a leak of connections, I'm pretty sure the only ASP.NET app using the server is at fault here.

We received the "Too many connections" error and checking the server we saw many connections with status Sleep.

I know that, fiddling with my.cf, MySQL can be forced to close sleeping connection after a shorter time, but I would rather fix the actual problem instead of the symptoms.

So I was wondering if there's a way to retrieve the SQL statement that was last executed on a given connection.
I think if I could do that on older sleeping connections, I might have an easier time tracking down this leak.

Of course, if anyone can think of an alternative solution for this scenario, any suggestion is very welcome.

You can enable "general query log", which will log each executed query, along with its session/connection ID, so if you grep the connection ID, you will have all the queries on one session.

HOWEVER, 'sleep'ing connections are not the cause of max connection error, but a result of it. They are not harmful to stay there. In fact, they are established connections that can be re-used by future sessions.

What would be more useful in this case is to search the slow query log, and check the innodb status to get information about locks and dead locks that have happened:

SHOW ENGINE INNODB STATUS;


Same record shows up multiple times in ordered paginated query Some records show up on multiple result "pages" with the following query:

SELECT
    `description`,
    `wallpaper`
FROM
    `bbr_bar`
WHERE
    `country_id` = '2510769'
ORDER BY
    `online` DESC

For instance, record with id 99 shows up on the first "page" (LIMIT 10 OFFSET 0) but also on the last one LIMIT 10 OFFSET 50. Obviously this should not happen.

The problem disappears under the the following circumstances:

if I don't filter the query, or don't order it (both country_id and online are int(11), not foreign keys)
if I remove one of the two fields from the SELECT clause (both varchar(255), not foreign keys)
if I delete some tables or fields in the schema. However, afters hours of effort, I am still unable to say for sure which tables/fields have an impact and which ones don't. Looks like random (even if I know it is not). Hence I don't paste here any CREATE TABLE or INSERT statement as I don't think they are relevant.

Only thing I can say for sure is that if I mysqldump all tables and restore them in a new schema, the problem is still there.

(There are other fields in the table but adding/removing them has no effect on the result).

Questions:

Am I right if I conclude that something is corrupted in my schema?
How could I fix this?

(MySQL version: 5.6.24 Win32 x86)

I have some bad news for you: According to MySQL, IT'S NOT A BUG !!!

There are two bug reports on this behavior and is considered non-critical

MySQL 5.6 (Bug #69732 : LIMIT clause results in duplicate data across pages)
MySQL 5.5 (Bug #65307 : Problem with order by and limit offset)

Here is the rationale expressed in Bug #69732

  Without a distinct ORDER BY the result order is undefined. Period.

  Even if the same query, executed twice, results results in different order there is nothing wrong with that. You may be used to what looks like deterministic results but that is actually an illusion. It may be what you see in the usual case but there is nothing that guarantees this. Result order can change as statistics get updated, as index trees are reshuffled, as data is partitioned across different physical machines so that result order depends on network latencies ...

  So if you want pagination then make sure you ORDER BY on something guaranteed to be UNIQUE, or live with the fact that the sort order of identical values in a non-unique sequence is not deterministic and can change at any time without prior notice.

In your particular case, the online field is probably not distinct enough. Even if you index bbr_bar by (country_id,online), this may still be not distinct enough. You may have to involve another column to drive the sort order (perhaps an auto increment field or a timestamp field).

Give it a try!!!!


Are PostgreSQL 10.x identity columns inherently indexed? unique? Are PostgreSQL 10.x identity columns inherently indexed? unique? Or do I need to add a (unique) index to the column as desired?

Are PostgreSQL 10.x identity columns inherently indexed?

No

  unique?

No

  Or do I need to add a (unique) index to the column as desired?

You don't have to, if you desire it you can. Or just declare the IDENTITY COLUMN to be a PRIMARY KEY (as they're often used). All PRIMARY KEYs are UNIQUE and indexed.

See also

What are Identity Columns?


How often does the slow_log table get updated in MySQL? I've changed the long_query_time from 10 secs to 5 secs. I haven't seen any queries logged for a few hours now. Am I supposed to tweak my database for the slow_log table to gets updated more often?

There were slow queries that took longer than 10 seconds and I've already dealt with them. However, the first byte time for my WordPress website is still > 3 seconds according to tools.pingdom.com.

Updates:

I've just changed the long_query_time to 1 sec and still, I haven't seen any slow queries logged yet. Could it be possible that I now have no queries that takes longer than 1 sec to execute (that seems too good to be true, doesn't it?)?

I've seen queries that took longer than 1 sec now so everything is working fine for me - I see the slow queries that I need to see.

*How I got the slow queries to be logged in the slow_log table in mysql database?*
I'm using Amazon RDS MySQL, so as soon as I changed slow_query_log to 1, the slow queries appear in the slow_log table.

By default, slow queries are not recorded anywhere.
There are three(3) ways to activate the Slow Query Log
ACTIVATION #1 : Text File
To log slow queries to a text file, please add the following to the /etc/my.cnf
[mysqld]
long-query-time=5
log-output=FILE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log

ACTIVATION #2 : CSV Table (mysql.slow_log)
To log slow queries to mysql.slow_log, please add the following to the /etc/my.cnf
[mysqld]
long-query-time=5
log-output=TABLE
slow-query-log

ACTIVATION #3 : Both Methods (Text File and Table)
[mysqld]
long-query-time=5
log-output=FILE,TABLE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log

SUMMARY
Once you edit /etc/my.cnf (or my.ini for Windows), you must restart mysql

For Linux, service mysql restart
For Windows
net stop mysql
net start mysql

If you would like to test the slow log after the mysql restart, simply run this:
SELECT SLEEP(12);

If long-query-time >= 12, this statement should land the the slow log.
Here are the variables used in the above methods

long-query-time (Set this to 0 so everything and its grandmother lands in the Slow Query Log)
log-output
slow-query-log
slow-query-log-file

For more information on Slow Query Logging, please read the MySQL Documentation

The Slow Query Log
Selecting General Query and Slow Query Log Output Destinations

CAVEAT
If you decide to use mysql.slow_log, keep in mind that the default mysql.slow_log is a CSV table. You can query against mysql.slow_log, but the table has no index.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)

mysql>

If you would like the slow log to be a MyISAM so you can query it like any other table, please run the following:
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Here is what you should see afterwards:
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.slow_log ADD INDEX (start_time);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

You can read my past answers on this:

Nov 18, 2011 : Create a custom log table in MySQL
Mar 30, 2012 : MySQL slow log configuration


MySQL database backup plan I have a MySQL database that is about 17GB in size. Right now we don't have a backup for this and I have been tasked to look into backing it up automatically.

I am a software developer myself, not an expert database engineer.

After some research I found out that MySQL has something called MySQL Replication, which will automatically create a backup database on different server.

My concern about this is that if someone accidentally drops a table, or deletes a record, the same will happen on the slave database as well - so that would not serve as a backup.

I need suggestions on what can be done with our MySQL database so that it can be backed up automatically.

Note that mysqlbackup is not a hot backup solution. For databases the size of your one, there really is no debate; see the related Q & A Mysql backup strategies?

As Shlomi Noach (GitHub's MySQL guy) says in his answer, Percona's XtraBackup is your go-to choice. It is also worth reading MySQL Backup and Restore Best Practices by Krzysztof Ksiazek.

By the way, no backup strategy on earth will protect you from devs/DBAs deleting records/dropping tables, that's why you should be using incremental backups; maybe LVM snapshots or flush logs. However, incremental backups are also performed by XtraBackup. See also Example Backup and Recovery Strategy in the MySQL reference manual.

On a more general note, broad approaches to this issue are discussed in:

Database Backup and Recovery Best Practices by Ali Navid Akhtar, Jeff Buchholtz, Michael Ryan, and Kumar Setty
Backup and Recovery Best Practices by Omelchenko Alexander

In any case, on a production system, devs (or DBAs for that matter) should not be wandering through the filesystem and/or database deleting stuff ad libitum. Any and all changes on production should have been performed at least twice on test/UAT or similar systems before being implemented in prod! This is best practice.


Must an index cover all selected columns for it to be used for ORDER BY? Over at SO, someone recently asked Why isn't ORDER BY using the index?

The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows.  One of the columns, an integer, was indexed—and the OP sought to retrieve his entire table sorted on that column:

SELECT * FROM person ORDER BY age

He attached EXPLAIN output showing that this query was resolved with a filesort (rather than the index) and asked why that would be.

Despite the hint FORCE INDEX FOR ORDER BY (age) causing the index to be used, someone answered (with supporting comments/upvotes from others) that an index is only used for sorting when the selected columns are all read from the index (i.e. as would normally be indicated by Using index in the Extra column of EXPLAIN output).  An explanation was later given that traversing the index and then fetching columns from the table results in random I/O, which MySQL views as more expensive than a filesort.

This appears to fly in the face of the manual chapter on ORDER BY Optimization, which not only conveys the strong impression that satisfying ORDER BY from an index is preferable to performing additional sorting (indeed, filesort is a combination of quicksort and mergesort and therefore must have a lower bound of Ω(nlog n); whilst walking through the index in order and seeking into the table ought to be O(n)—so this makes perfect sense), but it also neglects to mention this alleged "optimisation" whilst also stating:

  The following queries use the index to resolve the ORDER BY part:

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

To my reading, that is precisely the case in this situation (yet the index was not being used without an explicit hint).

My questions are:

Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?

If so, where is this documented (if at all)?
If not, what was going on here?

Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?

This is a loaded question because there are factors that determine whether an index is worth using.

FACTOR #1

For any given index, what is the key population? In other words, what is the cardinality (distinct count) of all tuples recorded in the index?

FACTOR #2

What storage engine are you using? Are all needed columns accessible from an index?

WHAT'S NEXT ???

Let's take a simple example: a table that holds two values (Male and Female)

Let create such a table with a test for index usage

USE test
DROP TABLE IF EXISTS mf;
CREATE TABLE mf
(
    id int not null auto_increment,
    gender char(1),
    primary key (id),
    key (gender)
) ENGINE=InnODB;
INSERT INTO mf (gender) VALUES
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
ANALYZE TABLE mf;
EXPLAIN SELECT gender FROM mf WHERE gender='F';
EXPLAIN SELECT gender FROM mf WHERE gender='M';
EXPLAIN SELECT id FROM mf WHERE gender='F';
EXPLAIN SELECT id FROM mf WHERE gender='M';

TEST InnoDB

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mf
    -> (
    ->     id int not null auto_increment,
    ->     gender char(1),
    ->     primary key (id),
    ->     key (gender)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO mf (gender) VALUES
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.06 sec)
Records: 40  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |   37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |   37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql>

TEST MyISAM

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mf
    -> (
    ->     id int not null auto_increment,
    ->     gender char(1),
    ->     primary key (id),
    ->     key (gender)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO mf (gender) VALUES
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
    -> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.00 sec)
Records: 40  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |   36 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | mf    | ref  | gender        | gender | 2       | const |    3 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | mf    | ALL  | gender        | NULL | NULL    | NULL |   40 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Analysis for InnoDB

When the data was loaded as InnoDB, please note that all four EXPLAIN plans used the gender index. The third and fourth EXPLAIN plans used the gender index even though the requested data was id. Why? Because id is in the PRIMARY KEY and all secondary indexes have reference pointers back to the PRIMARY KEY (via the gen_clust_index).

Analysis for MyISAM

When the data was loaded as MyISAM, please note that the first three EXPLAIN plans used the gender index. In the fourth EXPLAIN plan, the Query Optimizer decided not to use an index at all. It opted for a full table scan instead. Why?

Regardless of DBMS, Query Optimizers operate on a very simple rule-of-thumb: If an index is being screened as a candidate to be used for performing the lookup and Query Optimizer computes that it must lookup more than 5% of the total number of rows in the table:

a full index scan is done if all needed columns for retrieval are in the selected index
a full table scan otherwise

CONCLUSION

If you do not have proper covering indexes, or if the key population for any given tuple is more than 5% of the table, six things must happen:

Come to the realization that you must profile the queries
Find all WHERE, GROUP BY, and ORDER BY` clauses from those Queries
Formulate indexes in this order

WHERE clause columns with static values
GROUP BY columns
ORDER BY columns

Avoid Full Table Scans (Queries lacking a sensible WHERE clause)
Avoid Bad Key Populations (or at least cache those Bad Key Populations)
Decide on the best MySQL Storage Engine (InnoDB or MyISAM) for the Tables

I have written about this 5% rule of thumb in the past:

May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Mar 22, 2012 : Why does MySQL choose this execution plan?
Mar 09, 2012 : index not being used
Jan 18, 2012 : MySQL status variable Handler_read_rnd_next is growing a lot
Dec 27, 2011 : MySQL - fastest way to ALTER TABLE for InnoDB
Jul 29, 2011 : MySQL Query Optimization : Indexing and Pagination
Jul 12, 2011 : MySQL very slow query when changing one WHERE field despite no index/key

UPDATE 2012-11-14 13:05 EDT

I took a look back at your question and at the original SO post. Then, I thought about my Analysis for InnoDB I mentioned before. It coincides with the person table. Why?

For both tables mf and person

Storage Engine is InnoDB
Primary Key is id
Table access is by secondary index
If table was MyISAM, we would see a completely different EXPLAIN plan

Now, look at the query from the SO question : select * from person order by age\G. Since there is no WHERE clause, you explicitly demanded a full table scan. The default sort order of the table would be by id (PRIMARY KEY) because of its auto_increment and the gen_clust_index (aka Clustered Index) is ordered by internal rowid. When you ordered by the index, keep in mind that InnoDB secondary indexes have the rowid attached to each index entry. This produces the internal need for full row access each time.

Setting up ORDER BY on an InnoDB table can be a rather daunting task if you ignore these facts about how InnoDB indexes are organized.

Going back to that SO query, since you explicitly demanded a full table scan, IMHO the MySQL Query Optimizer did the correct thing (or at least, chose the path of least resistance). When it comes to InnoDB and the SO query, it is far easier to perform a full table scan and then some filesort rather than doing a full index scan and a row lookup via the gen_clust_index for each secondary index entry.

I am not an advocate of using Index Hints because it ignores the EXPLAIN plan. Notwithstanding, if you really know your data better than InnoDB, you will have to resort to Index Hints, especially with queries that have no WHERE clause.

UPDATE 2012-11-14 14:21 EDT

According to the book Understanding MySQL Internals

Page 202 Paragraph 7 says the following:

  The data is stored in a special structure called a clustered index, which is a B-tree with the primary key acting as the key value, and the actual record (rather than a pointer) in the data part. Thus, each InnoDB table must have a primary key. If one is not supplied, a special row ID column not normally visible to the user is added to act as a primary key. A secondary key will store the value of the primary key that identifies the record. The B-tree code can be found in innobase/btr/btr0btr.c.

This is why I stated earlier : it is far easier to perform a full table scan and then some filesort rather than doing a full index scan and a row lookup via the gen_clust_index for each secondary index entry. InnoDB is going to do a double index lookup every time. That sounds kind of brutal, but that's just the facts. Again, take into consideration the lack of WHERE clause. This, in itself, is the hint to the MySQL Query Optimizer to do a full table scan.


How to join unknown table from Xref table? I have a database that was provided by one of our vendors that I am trying to tie into another system, but I have never come across something designed this way.

I want to search for "sensors" named x, and get their readings as the result. There is one main "Xref" table, and then about 500 other tables that contain the sensor readings.

I can search for my desired sensors with this query, which returns the table name and columns containing the readings:

select * from [Xref] where name like '%Sensor_a%'
----------------------------------------------
Name        TableColumn TableName
----------------------------------------------
Sensor_a1   2429        SensorData12
Sensor_a2   2430        SensorData12

With the results from above, I am able to find the sensor readings from the appropriate table:

select top 1 [Time],[2429],[2430] FROM [SensorData12]
----------------------------------------------
Time                2429        2430
----------------------------------------------
2015-10-01 00:00:48 88.7        37.1

Is there a way to get the readings in one request to the sql-server? I can't figure out how to do it with the dynamic table names that may change depending on the name of the sensor I search for.

One way would be to use a cursor and dynamic sql. Granted the solution below would need to be modified to accommodate your actual data, especially if the number of columns is variable, but for the example you gave it does work.

CREATE TABLE Xref(name varchar(20),TableColumn varchar(10),TableName varchar(20))

CREATE TABLE SensorData12([time] datetime2(0), [2429] decimal(3,1),[2430] decimal(3,1))

INSERT INTO [Xref]
VALUES('Sensor_a1', '2429', 'SensorData12'),
     ('Sensor_a2', '2430', 'SensorData12');

INSERT INTO [SensorData12]
VALUES ('2015-10-01 00:00:48', 88.7 ,37.1)

DECLARE @tablename sysname,
       @col1      sysname,
       @col2      sysname,
       @sql       nvarchar(max);

DECLARE c_tables CURSOR LOCAL
FOR SELECT [Tablename],
         MAX(CASE WHEN [name] = 'Sensor_a1' THEN [TableColumn]
            END) AS [col1],
         MAX(CASE WHEN [name] = 'Sensor_a2' THEN [TableColumn]
            END) AS [col2]
    FROM [Xref]
    GROUP BY [TableName];
OPEN c_tables;

WHILE 1 = 1
    BEGIN
       FETCH NEXT FROM c_tables INTO @tablename,
                               @col1,
                               @col2;

       SET @sql = 'select top 1 [time],[' + @col1 + '],[' + @col2 + '] from [' + @tablename + '];';

       EXEC [sp_executesql]
           @sql;

       IF @@FETCH_STATUS < 0
          BEGIN BREAK
          END;

    END;
CLOSE c_tables;
DEALLOCATE c_tables;

DROP TABLE [Xref];
DROP TABLE [SensorData12];


"A severe error occurred" - Login Failed (but only occasionally) I'm using a C# application to execute a query against SQL Server. Here's the relavent portion of an exception I'm encountering once every twenty-four hours or so:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'S-1-9-3-3788657366-1166700905-846186909-3339902698'.
A severe error occurred on the current command.  The results, if any, should be discarded.

Here are the salient points:

I've been running this exact query for years, usually several
thousand times per day, with no problem.
The problem started immediately after updating to SQL Server 2014
from SQL Server 2008.
Unlike in SQL Server 2008, where I used a server-level user for
login, I'm now using a database-level login. That database user is
mapped to DBO. I'm not sure if this is even relevant but since the
error message says "login failed" I figured I'd mention it.

Again, the problem occurs only once in a thousand times -- maybe once every 24 hrs.

Is there a way I can reverse lookup the user in question given the user account ID? Recall that the error message provided me with this user ID: S-1-9-3-3788657366-1166700905-846186909-3339902698. Not that it should matter since my C# app only uses this single user account.

For reference, here is my connection string:

Server=inst2;Database=ema;User ID=emauser;Password=myPasswordGoesHere

********* UPDATE ********

I invested a few hours on this and received some great help from people on this site. There doesn't appear to be any workable solution right now.

This error message, specifically when the username is omitted and replaced with a bogus SID that doesn't correspond to any particular user on the database server or calling client machines, is just a bug in SQL Server that occurs when using contained users. The following links touch on the subject:

https://stackoverflow.com/questions/18629840/sql-login-failed-for-user-s-1-5-21-1482476501-1214440339-839522115-500-long-id (see SiHoran's answer)

and

https://connect.microsoft.com/SQLServer/feedback/details/772069/login-failure-with-connection-pooling-in-contained-db-after-connection-has-added-contained-user

It seems to rear its head when making a call from C# / .NET program.

As far as I can tell, the "contained user" functionality is just a bit flaky at the moment. Presumably switching back to an instance-level user (e.g. "sa") will fix the problem. Will try that next.

Just in case this helps, I'm posting some code that might help you work out what account that sid represents:

USE tempdb;
GO
-- to translate SID in binary format to AD format
CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY,
            REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END
GO

SELECT dbo.fn_SIDToString(sp.sid), sp.*
FROM sys.server_principals sp
WHERE dbo.fn_SIDToString(sp.sid) = 'S-1-9-3-3788657366-1166700905-846186909-3339902698';

Thanks goes to @James for pointing out that the sid might be in master.sys.server_principals and to @AaronBertrand for pointing me to a great piece of code that will translate SQL Server binary sids to the Windows format.

N.B.: I put this function in tempdb to avoid creating it in a permanent place.  You may want to put it somewhere else for future use, since tempdb gets recreated automatically upon SQL Server startup, and thus the function here will not survive service restarts or machine reboots, etc.


Why isn't the following query using the `always` index? This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query:

SELECT * FROM movies, showtimes
WHERE site='5' AND
    (showtimes.movie = movies.id OR always='true') AND
    ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')\
GROUP BY movies.id ORDER BY listorder

I'm not sure who wrote this. Looks like it's on a database I designed 5+ years ago, which unknown people have been tinkering with quite a bit since I last touched it. The tables in question are:

CREATE TABLE `movies` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `rating` varchar(5) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `special` varchar(255) NOT NULL default '',
  `url` varchar(64) NOT NULL default '',
  `poster` varchar(255) NOT NULL default '',
  `runtime` varchar(20) NOT NULL default '',
  `listorder` tinyint(4) NOT NULL default '0',
  `comsoon` enum('false','true') NOT NULL default 'false',
  `always` enum('false','true') NOT NULL default 'false',
  `site` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `rating` (`rating`),
  KEY `site` (`site`),
  KEY `comsoon` (`comsoon`),
  KEY `always` (`always`),
  KEY `listorder` (`listorder`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `showtimes` (
  `movie` int(11) NOT NULL default '0',
  `showdate` date NOT NULL default '0000-00-00',
  `showtime` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`movie`,`showdate`,`showtime`),
  KEY `showdate` (`showdate`),
  KEY `showtime` (`showtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When I first was alerted to this situation, movies.always did not even have an index, nor did movies.listorder. I was sure that was the problem, however, adding an index on those two columns did not change the EXPLAIN:

mysql> EXPLAIN SELECT * FROM movies, showtimes WHERE site='5' AND (showtimes.movie = movies.id OR always='true') AND ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')  GROUP BY movies.id ORDER BY listorder;
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys       | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | showtimes | index | PRIMARY,showdate    | PRIMARY | 10      | NULL | 93411 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | movies    | ALL   | PRIMARY,site,always | NULL    | NULL    | NULL |    25 | Using where                                  |
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

I tried rewriting the query as follows:

SELECT * FROM movies, showtimes
WHERE (site='5' AND always='true') OR
  (site='5' AND showtimes.movie = movies.id AND showdate>='2012-4-11' AND showdate<'2012-04-18')
GROUP BY movies.id ORDER BY listorder

However the EXPLAIN was no different. WHy is this query not using an index on the movies table? How can I explain to the developers that they should rewrite this query to make it not bring the server to it's knees?

It might be possible to rewrite it into a UNION

SELECT * FROM (
 (
  SELECT movies.* FROM movies
   WHERE site='5' AND always='true'
 ) UNION
 (
  SELECT movies.* FROM movies
  INNER JOIN showtimes ON showtimes.movie=movies.id
  WHERE site='5' AND showdate BETWEEN '2012-4-11' AND '2012-04-18'
 )
) AS movie_shows
GROUP BY id
ORDER BY listorder

I haven't tested the above, but am curious the results of the EXPLAIN

Your always attribute is failing in two ways:

It is very low selectivity as @ypercube points out, since there are only two possible
values. MySQL will discard using these type of indexes
The query has OR. Meaning it would have to do a table scan anyway to get match both conditions.


Mysql trigger to update if date match and insert if no match all BEFORE INSERT please pardon my English i am using language translator. I have a table summary ,i want a TRIGGER to fire before insert on this table i.e summary and what i want is that, the TRIGGER should check if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert

`summary`
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 200   |   50        |  30  |
|22/17 | 100   |   10        |  80  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

`summary` // this is how i want it to be
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 300   |   150       |  30  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

my table code

CREATE TABLE `summary` (
  `id_` int(11) NOT NULL,
  `date_` date NOT NULL,
  `income_` text NOT NULL,
  `expenditure_` text NOT NULL,
  `other_` text NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `summary`
(`id_`, `date_`, `income_`, `expenditure_`, `other_`)
VALUES
(1, '2017-12-22', 200, 50, 30),
(2, '2017-12-22', 100, 10, 80),
(3, '2017-12-23', 50, 100, 0);

This is what i have been trying so far, though am a Novist
trigger code

DELIMITER
$$
CREATE TRIGGER inc_trig
BEFORE INSERT
ON income_
FOR EaCH ROW
BEGIN
DECLARE income,expenditure,other INT;

SET income=1; SET expenditure=2; SET other=3;

IF( NEW.date_ != date_ )THEN

IF NEW.name_ = income THEN
INSERT INTO summary (date_,income)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = expenditure THEN
INSERT INTO summary (date_,expenditure_)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = other THEN
INSERT INTO summary (date_,other_)
VALUES (new.date_,new.amount);

ELSE(new.date_ = date_)THEN

IF NEW.name_ = income_ THEN
UPDATE summary SET income_ = income_ + new.amount);

ELSEIF NEW.name_ = expenditure THEN
UPDATE summary SET expenditure_ = expenditure_ + new.amount);

ELSEIF NEW.name_ = other THEN
UPDATE summary SET other_ = other_ + new.amount);

END IF;

END
$$

"if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert" -- You have just specified what INSERT ... ON DUPLICATE KEY UPDATE ... does.  Use that.

this trigger works but how to include the ON DUPLICATE KEY UPDATE
giving me tough time and dosent work out

DELIMITER $$
CREATE TRIGGER inc_trig
    BEFORE INSERT ON income_
FOR EACH ROW
BEGIN
    DECLARE fees,bus,uniform,c_uniform,sp_wear,pullover,car INT;

    SET fees=1; SET bus=2; SET uniform=3; SET c_uniform=4;
    SET sp_wear=5; SET pullover=6; SET car=7;

    IF NEW.income_name_ = fees THEN
    INSERT INTO income_summary_monthly
            (date_, fees_, month_, term_, schid_)
        VALUES
            (new.date_, new.amount_, new.month_, new.term_, new.schid_)
        ON DUPLICATE KEY UPDATE
            amount_ = VALUES(amount_);

// Or do you want
//          amount_ = amount_ + VALUES(amount_) ??

    ELSEIF NEW.income_name_ = bus THEN
    ...
    END IF;

END  $$
DELIMITER ;

I am a bit lost since the original code does not look much like this version.


Cannot execute inserts querys after upgrade to mysql 5.7 I have Debian 8 (Jessie) server and i followed this procedure to upgrade from mysql 5.5 to mysql 5.7 https://dev.mysql.com/doc/refman/5.7/en/linux-installation-debian.html

Now, any script in the server that have an INSERT query is not working... The script is being executed but the INSERT query is returning always false with no error description...

Any ideas how to solve?

This is what i have installed

Also, when i click on Database button in phpmyadmin

i'm getting this error

If your question is about #1055...

1055: sql_mode includes ONLY_FULL_GROUP_BY.  When doing a GROUP BY, you should include all the columns in the SELECT other than aggregates.

If your question is about INSERT, I don't see any supporting info.


Substring join or additional table, which is faster? I have a case that come up regularly at work. I have many tables that use a 4 character string as a foreign key : G191

The G is a sort of category, the 19 is the year, and the 1 is an instance.
Getting all rows of category G is something we do very often. Usually like:

SELECT * FROM [Table]
WHERE Left([ID], 1) = 'G'

There is a way to get this effect without manipulating a string, by joining to a table where this category is defined:

SELECT * FROM [Table]
JOIN [Categories] ON [Table].CategoryID = [Categories].CategoryID
WHERE [Categories].Letter = 'G'

My co worker insists that the first way is more performant, and rolls his eyes at me for doing it the second way.

Which one is better? Does joining by another table really add more work then checking the first character of a string?

Why Yours Is Better

In general, your pattern is a better idea.

Performance will depend on indexes, predicate selectivity, and table size though.

The reason your pattern is a better idea comes down to the concept of SARGability, which is a fancy word for if your search arguments can be used as seek predicates, or even fully pushed predicates, (i.e. not being processed in Filter operators after accessing an index).

Some examples of where this can hurt in joins and where clauses are:

function(column) = something
column + column = something
column + value = something
column = @something or @something IS NULL
column like ‘%something%’
column = case when …

When you do stuff like this, your queries can end up with all sorts of bad side effects:

Increased CPU (burn baby burn)
Index Scans (when you could have Seeks)
Implicit Conversion (if your predicates produce a different data type)
Poor Cardinality Estimates (poking the optimizer in the eye)
Inappropriate Plan Choices (because the optimizer is blind now, you jerk)
Long Running Queries (yay job security!)

Better Options

SARGable options for what you're looking for would include:

WHERE [ID] LIKE 'G%'

or

WHERE [ID] >= 'G' AND [ID] < 'H'

An alternative solution would be to add a computed column in just the table you're searching:

ALTER TABLE [Table]
    ADD Lefty AS Left([ID], 1);

CREATE INDEX ix_whatever
    ON [Table] (CategoryID , Lefty);

Though like I said before, the performance difference may not be dramatic with smaller tables.

It's also possible that this index won't be used since your example query is selecting all of the table columns, and this index doesn't cover them. But that's a story for a different day.


SQL Server not starting at Windows startup but starts manually My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it starts just fine when I manually start the service.

Event Viewer shows that the service fails to start because of a connection timeout. There are 2 entries regarding this timeout:

  Entry 1

  A timeout was reached (30000 milliseconds) while waiting for the SQL
  Server (SQLEXPRESS) service to connect.

  Entry 2

  The SQL Server (SQLEXPRESS) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

I checked the ERRORLOG files and there are no errors logged around the time of those Event Viewer entries.

Any ideas on the cause of this problem or how I can investigate further?

This issue happens because the SQL Server lost the logon id credentials or used an account without rights to start services.

For starting your services automatically, SQL Server needs an account with rights to start services during operating system start up.

To solve this issue you need to go
Administrative Tools > Services > SQL Server(MSSQLSERVER or the name of your instances) > Right Click >  Properties > Move to Log On > Set the Log On option using a Local System Account

If you have chosen this option, hit on Browse Button and find the Local Service Account or Network service Account there > Click in Apply.

Restart your computer and you will able to start your SQL Server.


Best way to copy MS SQL (2000) database files to an external drive We have a system running on Windows 2003, with a MS SQL server 2000 (Standard) instance as the data source. As we have sold the product, we are in the process of moving everything to the purchasing party.

We have cloned the virtual machines, and when they set them up, they are stating that the databases are corrupt and would like copies of the MDF/LDF files directly.

What is the cleanest way to do this? There are approximately 90 separate databases configured, and I don't really want to go in, stop/detach/copy/attach/restart, especially as I've heard that reattaching doesn't always go well. I also don't know if every database is stored in the same place.

I am looking for the least involved method of confirming the storage location and copy process for 90+ database files to a connected external hard drive.

Per the specific requirements of getting copies of the .mdf and .ldf files, you can simply stop the SQL Server service, copy the files, then start the SQL Server service.

The way to do it without downtime would be to take full backups, but then the other party would be required to restore the backups instead of just copying files.  This would be the preferred method in almost all cases, but if the other party doesn't want to manually restore 90 databases or take the time to automate the restoration, the file copy method is understandable.


Best Possible Solution for Reporting Mirroring or Replication? Hi I have SQL SERVER 2008R2 Data Centre Edition and I use SSRS and a Vendor application for reporting.  I have a Reporting Server where we backup and restore everyday from Live Server to have Data available for our reports.

I face many problems because of this any code developed has to wait a day until the restore is done to test. Any code developed on Reporting Server is Over written. Surprise surprise reports show a day old data and I could go on and on.

I know I have two options available Mirroring and Replication:

Mirroring will allow read-only mode and will reports run fine? Does this Read-only mode any other implications that I am missing ?

Replication I do not have Primary Keys on all the tables so I am left only with the option of Merge replication which uses a globally unique identifier (GUID), I cannot let sql server to add this column to all the tables in my databases.

Can anyone please advise me a solution for having a Live Copy of Sql Server Database on another Server for Reporting Purposes?

MY Requirments

I can live with a few minutes old data.
Any changes made on
Production Server needs to be copied over to Reporting Server i.e
Data, Sql Server Objects (Tables, Views, Store Procedures,
Everything)
Any Objects created on Reporting Server does not need
to copied over to production server/databases.

Please advise any solution as it will be a great help Thank you in Advance.

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying.  You have to create and maintain snapshots, which can be annoying.

Your options are, in no particular order:

Mirroring with snapshots : effective but has management overhead.
Backup and restore : Are you restoring from full backups rather than differential or logs?  If so, you may be able to reduce the time spent on restores.
Log shipping : management overhead, database is unavailable when log backups are being applied
Replication : challenging from a management perspective, not really intended for syncing full databases
Report from transactional database - This option is all too often discarded over unsubstantiated performance concerns.  Those concerns can also be mitigated somewhat through the use of Resource Governor, snapshot isolation, etc. This is an underutilized option, IMO.
ETL with SSIS or a similar tool. You would have to do your own schema changes, however.
Availability Groups, if you can upgrade to 2012 (I assume you're on 2008 R2).  This is really the best option available these days.
Idera makes a tool called Virtual Database that allows you to mount and query a backup file.  It's an interesting option in some cases.

It's difficult to make a clear recommendation without knowing a lot more information about your environment.  I've used most of these methods to varying degrees of success.  Note that most places end up building out more robust data marts / data warehouses for reporting and analytics, so you'll probably end up with the ETL route one day.

Oh, and make sure you have licensed the secondary server ;)


How to convert Postgres from 32 bit to 64 bit I would like to convert from PG 32 bit to 64 bit. I am testing with pg_upgrade (in several scenarios) but I think it cannot do that.

With pg_upgrade, I can upgrade PG version 8.5 to v.9 (both of them run in 32 bit or  64 bit).

Until now, I haven't found how to change from a 32 bit install to 64 bit. How can this be done?

You can't use pg_upgrade for this, as it can only upgrade clusters from versions that are built with the same options on the same architecture. You must either:

dump and reload, with the associated down-time; or
Run both the 32-bit and 64-bit versions concurrently and set up a 3rd party replication tool like Bucardo, Londiste or Slony-I to stream rows across. Once they're in sync, stop the 32-bit DB and remove the replication.

Also, there's no PostgreSQL 8.5. Did you mean 8.4? "v9" isn't very specific either - PostgreSQL 9.0, 9.1, and 9.2 are all quite distinct releases. See the version policy, which outlines PostgreSQL's quirky versioning.


Declare variable of table type in PL/pgSQL I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:

q1 = select * from foo;
q2 = select * from bar;
for t1 in q1:
  for t2 in q2:
    -- do something with t1 and t2

I looked into the return next construct but that seems to only able to handle return values.

In PostgreSQL, every table name serves as type name for the row type (a.k.a. composite type) automatically - not a table type, there are no "table types" or "table variables" in Postgres (but there are typed tables).
So you can just declare a variable of that type in PL/pgSQL.

CREATE FUNCTION foo()
 RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
  q1 foo;  -- "foo" ...
  q2 bar;  -- ... and "bar" are existing (visible) table names
BEGIN

FOR q1 IN
   SELECT * from foo
LOOP
   FOR q2 IN
      SELECT * from bar
   LOOP
       -- do something with q1 and q2
       -- since q1 and q2 are well known types, you can access columns
       -- with attribute notation. Like: q1.col1
   END LOOP;
END LOOP;

END
$func$

A FOR loop works with a built-in cursor. There are also explicit cursors in plpgsql.

You could also just declare variables of the generic type record. It can take any row type at assignment automatically. But special rules apply. Be sure to follow the link and read the chapter of the manual!

While it's often convenient to have the function return SETOF <table name>, returning SETOF record is not as convenient. The system does not know what the function returns this way and you have to add a column definition list with every call. Which is a pain. Details about table functions in the manual.

Often there are more efficient solutions with plain SQL, though. Looping is a measure of last resort, when you can do things in one scan where you would need multiple scans in pure SQL.


How to perform the dreaded double hop with linked servers? The situation:
I have SERVER1, SERVER2, ACCESSDB on SERVER3.

I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB.

I want to be able to access the linked server on SERVER1 from SERVER2 (aka the double hop).

When I log into SERVER2 I get the good ol - "dont have permissions" issue when I test the connection. I checked SERVER3 logs and it says the incoming connection is ANONYMOUS. I need to be able to pass some (any) credentials to SERVER3 from SERVER2.

NO, I cannot use a SQL account. I have to use Windows Auth.

Yes, I need to use KERBEROS and set up SPNs. That's where my question comes in. How do I properly set up KERBEROS and delegation?

I want to be able to run

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

from SERVER2 and have it return 'KERBEROS' and not 'NTLM'.

What specific commands do I need to run to properly set up KERBEROS/SPNs? How do I create this "chain"?

FYI - Both instances are NAMED instances on SQL2008 R2 Standard (x64).

Security Account Delegation

And for SQL Server 2000 but still valid too

Basically

Configure the server in AD
Set up the SPN
Use sp_addlinkedsrvlogin so useself = true


PL/pgSQL regclass quoting of table named like keyword I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new name as a string. I am using format() to construct the new name, and would typically use the %I placeholder. This works as long as the passed in table name doesn't match any PL/pgSQL keyword. In this case, no matter what type component I choose (%I or %s), the quoting is wrong.

Consider the following function:

CREATE OR REPLACE FUNCTION make_name(old_name regclass)
RETURNS text
LANGUAGE plpgsql AS
$$
BEGIN
    RETURN format('%I_new', old_name);
END;
$$;

Further assume that there are two tables: treenode and overlay. Calling this function for both results in the following new names:

SELECT make_name('overlay'::regclass);
     make_name
-------------------
 """overlay"""_new
(1 row)

SELECT make_name('treenode'::regclass);
  make_name
--------------
 treenode_new
(1 row)

As it turns out overlay is also a PL/pgSQL function (just like format, but treenode is not), which seems to change quoting behavior. If %s is used with format(), the result would be "overlay"_new. The same happens when I use the || operator. If I use text as input parameter type, everything works as expected, but I would prefer using regclass.

Is there a way to format a string with a keyword-matching regclass table name (e.g. overlay) without quotes, just like it is the case for a non-keyword matching regclass table name (e.g. treenode)?

Explanation

I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution.

0.

The reason why overlay is quoted is not because it's a function name, but because it's a reserved word.
Also, overlay() is not a "PL/pgSQL function" (nor PL/pgSQL keyword), it's a standard SQL function built into the Postgres core (LANGUAGE internal, so C behind the curtains). In fact, PL/pgSQL has nothing to do with any of this.

1.

A simple logic error.

format('%I_new', old_name)

This would escape any non-standard identifier with surrounding double quotes before '_new' is appended and would fail even with text as input type. You have to append '_new' before quoting the whole of it:

format('%I', old_name || '_new')

But that still won't work for regclass. Keep reading.

2.

It's nonsense to add quotes to a regclass variable with %I, since its text representation is already quoted automatically where needed. (Similar to quote_ident(), but cannot be NULL; since the input is regclass it cannot be NULL to begin with.) You would apply it twice. Always use %s for regclass input (the string as is).

3.

format() is overkill for this. Just use quote_ident():

quote_ident(old_name || '_new')

4.

Most importantly, as mentioned above, the text representation of a regclass variable is automatically escaped. That's built into the cast. To get to the raw text, retrieve it from system catalog pg_class directly. A regclass value is just the oid of this table internally.

SELECT relname FROM pg_class WHERE oid = $1;

Solution

This does what you are looking for:

CREATE OR REPLACE FUNCTION make_name(old_name regclass)
  RETURNS text AS
$func$
SELECT quote_ident(relname || '_new') FROM pg_class WHERE oid = $1;
$func$  LANGUAGE sql STABLE;

Test

CREATE TEMP TABLE "sUICiDAL' namE"();
CREATE TEMP TABLE overlay();

SELECT make_name('overlay') AS n1
     , make_name('"sUICiDAL'' namE"')  AS n2;

     n1      |          n2
-------------+----------------------
 overlay_new | "sUICiDAL' namE_new"

Note that overlay_new is a perfectly legal identifier so it's not quoted.

If you want the name unescaped (no double quotes) just use:

SELECT relname || '_new' FROM pg_class WHERE oid = $1;


How to determine if postgres database contains GiST indexes and what type? Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?

Whenever you need to examine the structure of your database via code, always think "I should look at information_schema or pg_catalog". information_schema contains a standardized schema (66 views), whereas pg_catalog is PostgreSQL-specific, but contains more info (97 tables or views).

select
  *
from
  pg_catalog.pg_indexes
where
  indexdef ~* '\ygist\y'

Will show you all the gist indexes and their tables, names, and definitions.

~* means match a regular expression, case-insensitive.

\y means word boundary, so it would find ' gist ' but not 'logistics' in that column


Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views? I'm getting the following error when I try to use the FORCESEEK hint with the query below:

  Msg 8622, Level 16, State 1, Line 96 Query processor could not produce
  a query plan because of the hints defined in this query. Resubmit the
  query without specifying any hints and without using SET FORCEPLAN.

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (FORCESEEK)
INNER JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

IndexedView1 and IndexedView2 are both schemabound indexed views, and there's a unique clustered index on both views on the fields IndexedField1, IndexedField2.
Not sure if relevant, but the clustered index is 40 bytes big (per row).

Here's an example of exactly what both views look like under the hood:

SELECT
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' OtherField2)), 32) AS IndexedField2
FROM dbo.Table1

Nothing too fancy, and no hints inside the view's query.

This is the estimated execution plan (without trying to use a query hint):

Here's a repro.  It's documented that you can't use FORCESEEK on an indexed view without NOEXPAND.

EG

drop table if exists table1
drop table if exists table2
go
create table Table1(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200))
create table Table2(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200))

go

create view IndexedView1
with schemabinding
as
SELECT
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' + OtherField2)), 32) AS IndexedField2
FROM dbo.Table1

go

create view IndexedView2
with schemabinding
as
SELECT
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' + OtherField2)), 32) AS IndexedField2
FROM dbo.Table2

go

create unique clustered index pk_IndexedView1 on IndexedView1(IndexedField1,IndexedField2)
create unique clustered index pk_IndexedView2 on IndexedView2(IndexedField1,IndexedField2)

go

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (FORCESEEK)
INNER JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

    /*
Msg 8622, Level 16, State 1, Line 34
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
*/
go
drop table if exists #TEMP

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1  AS A with (noexpand)
INNER JOIN dbo.IndexedView2 AS B with (noexpand)
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

executes with plan:

And after applying NOEXPAND, and specifying that that the plan should only involve the view indexes, you can then use the FORCESEEK hint, or specify the join style (MERGE, LOOP, or HASH) if you want.

EG

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (NOEXPAND)
INNER MERGE JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2


How to update one database from another? I'm using SQL Server 2008. I have two databases called Adb and Bdb. There is a common table called commonTable. How can I update Adb database every 15 minutes using the data from the same table in Bdb?

Without more specifics:

CREATE PROCEDURE dbo.UpdateA
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE A
    SET col1 = B.col1 /* , ... */
    FROM Adb.dbo.commonTable AS A
    INNER JOIN Bdb.dbo.commonTable AS B
    ON A.key_col = B.key_col;

  INSERT Adb.dbo.commonTable(key_col, col1 /* , ... */)
    SELECT key_col, col1 /* , ... */
    FROM Bdb.dbo.commonTable AS B
    WHERE NOT EXISTS (SELECT 1 FROM Adb.dbo.commonTable
      WHERE key_col = B.key_col);

  DELETE A
    FROM Adb.dbo.commonTable AS A
    LEFT OUTER JOIN Bdb.dbo.commonTable AS B
    ON A.key_col = B.key_col
    WHERE B.key_col IS NULL;
END
GO

Now schedule that stored procedure every 15 minutes using a SQL Server Agent job.

Isolation, concurrency, error handling etc. are all additional considerations, but most concerns should be minor if this is the only process that writes to Adb. You might also consider a more concise MERGE command, but the syntax is daunting and it buys you less than you might expect in terms of improving concurrency and eliminating race conditions.

You might also want to consider transactional replication - get rid of this concept of having to do anything manually, and also the idea that data could be up to 15 minutes out of date.


How to drop and recreate catalog view in postgres Can any one please help me to drop and recreation of sys views in EDB(Postgres).

Manipulating the catalogs (the contents) is already something that is absolutely discouraged:

  Normally, one should not change the system catalogs by hand [...]

The reason for this is that you have to know the structure very well in order to know exactly what to change so that you don't corrupt your catalog.  For example, this is how an ALTER TYPE ... ADD VALUE ... used to work pre-9.1 (one of the simpler things - try to write a query for changing a column type):

INSERT INTO pg_enum(enumtypid, enumlabel)
    SELECT t_oid, 'new_value'
      FROM pg_type as t
     WHERE t.typtype = 'e'
       AND t.oid = 'your_schema.your_type'::regtype::oid;

Take a factor of 1000, multiply 'absolutely' with it, and then you get how much it is discouraged to change the structure.  Fortunately, it does not even seem possible (as user postgres, and you cannot have more privileges than that):

CREATE OR REPLACE VIEW pg_catalog.pg_stat_activity AS
SELECT [...], 'bla' AS bla
  FROM [...]
 WHERE [...];

ERROR:  permission denied: "pg_stat_activity" is a system catalog


Unable to create a datasource. Datasource fails to 'test successfully' for SSRS I imported an SSRS 2008 Datasource to SSRS 2012 using RS Scripter tool.

The DB connection is the same for the SSRS 2008 and SSRS 2012 data source created.

I can 'test successfully' the login/pw for SSRS 2008 datasource, but  'fail' with SSRS 2012.

The login has permissions and exists (They are the same for both SSRS datasources)

Any suggestions on how to trouble shoot this?
I asked Server Admins if there are firewall issues, they stated there shouldn't be any.
I was able to ping both hosts (though maybe that doesn't mean much sometimes)

The issue had to due with Windows GPO it was unknown to me at the time
http://technet.microsoft.com/en-us/library/bb326317%28v=sql.90%29.aspx


How to get rid of ORA-28002 message the password will expire within 6 days? I have a user getting an ORA-28002 indicating that the password will expire within six days.  I ran the following:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

But when I try to log in as the user, the message is still there. Executing this:

select * from dba_profiles where RESOURCE_NAME LIKE 'PASSWORD_LIFE_TIME';

shows that the values was really changed to UNLIMITED.


Access a DB through Another DB ONLY I have a DB [MainData] with the following data in [dbo].[Table1]

Customer    Value1   Value2
--------    ------   ------
CustA       Foo           1
CustA       Bar          25
CustB       Foo          22
CUSTA       Baz         259
CUSTC       Zoo          31

So - then, I have a DB called
CustA with a view defined as follows:

CREATE DATABASE CustAData;
GO

USE [CustAData]
GO

CREATE VIEW Table1 AS
(
    SELECT * FROM [MainData].[dbo].[Table1] WHERE Customer = 'CustA';
)

In this way, when customer A connects to the [CustAData].dbo.Table1 view - he only sees his 3 records - and CustB and CustC's data is automatically hidden.

Then - i create a user for CustomerA which ONLY has access to database [CustAData].  The problem is that when this user executes a SELECT * FROM dbo.Table1 I get an error that the user does not have access to the [MainData] database.

Is there any way to setup the security so that this user can ONLY access their database, which shares data (selectively) from another database without giving them direct access to the source database?

Thanks - maybe this doesn't make any sense, but it would be a very convenient way to configure this setup.  Thanks in advance.

EJ

Turns out Cross Database Chaining was the answer (for us at least).  Turning this on allowed me to give the user limited (dataread/datawriter) access to the database that only shows their data, and NOT the underlying database that actually stores everyone's data.

I appreciate the suggestions - thanks for weighing in.

As an aside - the reason I don't like using schema's is due to generally wanting to have each be assigned a default schema, and not have to qualify every table name with the schema name.

I find that for testing purposes, constantly having to either update my default schema (to simulate working as different users), or actually connecting as lots of different users who already have preset default schemas ends up being more of a headache than just logging in with Windows Auth as me (always) and just connecting to different databases to get different user's data.

This also allows me to (conceptually) have an entire database for each user - which really cleanly and nicely isolates their data.

Data liberation can then be achieved by literally just SELECT * FROM each table in the user's database.

Also - from my testing, of separating by schema or database (in SQL 2012) made no difference that I was able to detect across many hundreds of thousands of a variety of different types of operation.

If someone knows of some performance issue I'm not aware of - I'd love to know about it, but... at this point, keeping each customer's data in their own database is the right answer for us.

Thanks again.  Good night.


what are the SplitPage as Transaction Name? when I run the function fn_dblog to get some info from the SQL Server transaction log - I am after what is deleting something sometimes only
- I have set up a server side trace for this now.

I thought I could see something related to my mysterious deleting from the transaction log, but they are backed up every 15 min...

anyway, while looking at the transaction log using the query below, I see this:

select * from fn_dblog (null, null)
where spid is not null
order by [begin time] desc

is it because the inserted rows did not fit in a page of the clustered index, and therefore had to be created somewhere else allocating a new page for them?

could this be an indication that the FILLFACTOR is too high?

I'd say that just reading the transaction log won't give you a good idea if the fillfactor is too high. Keep in mind that Page Splits are not always a bad thing, and they are necessary.

Your fill factor is set when the index is created and rebuilt, so I'd look at how often your indexes need to be rebuilt if you use Ola's maintenance solution it will track that for you. Use tools like Ola's Command Log table and judgement based on the size of the index, size of the table, type of key values to decide on your fill factor. You can usually set your fill factor to 100 and then re-evaluate if there are a lot of index rebuild commands in Command Log.

New pages created during Page Splits like in your example will not allocate empty space based on your fill factor, they will simply fill the pages as needed for the new data.

In general, a good clustered index key will always be incrementing, so every page split will be simply to allocate a new page at the end of the index. This changes a bit with varchar columns, since they can cause page splits during an update.


RMAN or Fresh install I'm trying to reduce the time to install Oracle on a pipeline we have. At the moment, it's installed using dbca in silent mode with a database template file. The majority of the time seems to be creating the data dictionary etc. I have a theory that it would be quicker to clone a totally vanilla install and add the application specific data using data pump after. I just wanted to see if it's worth the effort trying this - has anyone tried it?

FYI - it's Oracle 12c EE using ASM for storage.

You can try and compare this yourself with DBCA.

When you create a new database with DBCA and choose the Custom Database option, that does not include datafiles, and creates a database from scratch by running scripts to build the dictionary and other objects. This may take 15-60 minutes depending on the hardware and options selected.

When you choose the General Purpose option, that in fact performs an RMAN restore of a database that was already prepared and included in the database software. This should not take more than 5 minutes. It is indeed faster. (I do not like it though, because it contains extra cost options such as Spatial or OLAP, and I prefer to keep the database small and simple, so by default I skip all options, even Java and Text).

With DBCA, you can use an existing database as source for creating a template that includes datafiles as well, so yes, you can build your initial database, create a template from it, and reuse it.


Change code editor variable highlighting colour Within the SSMS Code Editor (for SQL Server 2012) I have this code written within the editor, that has a Dark theme applied.  When you click on a variable, SSMS highlights all the other occurrences of that variable in scope.  Notice that the variable highlighting colour is quite dark against the background.

Within the Tools > Options > Fonts and Colors menu, can anyone tell me which is the correct Display items choice to edit this colour?  I've been through all of the ones that I can see that roughly match the same colour, but none actually effect the code.

Variable colors share settings with table, object and other names. So you can change the text color using Identifier:

Just note that it will also affect table names, column names etc.

For the highlighted color of identifiers and variables, use Selected Text. The highlights will use a slightly muted version of that color.

And no, these are not my current settings, and I only kept them that way long enough to grab a screen shot...


Binary storage in Cassandra, HBase I am looking at some implementations of Cassandra and HBase for medium-sized data sets (~1M resources) to be exposed to clients as graphs (via e.g. Tinkerpop). I would also like to store binaries in the same data stores. While it seems like both systems support storing large binaries one way o another (HBase via HDFS) I wonder what the performance implications would be for using these versus flat file storage. Are these systems designed to store binaries at scale, or are they more targeted at metadata storage? I am talking about 100s of Tb of binary data.

Thanks

.s

Cassandra definitely is more suitable for storage of metadata only, when you have big payloads, it's performance isn't very good. Similar stuff was for HBase when I did use it several years ago.

For storage of binary data itself, I maybe would go with something S3-compatible.


Best way to design a database for a bakery store? I am building an application that will be used as a Sales Management system for a bakery store. I have built the following tables:

Products has a single row per product. This is a Master Data definition only. It has somethings like 'Pizza`.

Ingredients has rows like Flour and Tomatoes . These are raw input materials required to prepare different products. The bakery store needs to keep track of how much the raw input materials cost.

Product_Ingredients describes what raw materials go in a final product. It could have something like Pizza | Tomatoes and Pizza | Green Peppers, Cup Cake|Strawberry Mix etc.

My question is this:
As a final product, the bakery offers Strawberry Mix for sale.
But in the database it is considered as an ingredient.
I can also add it as a separate Product but here is what makes this case complicated:

In case the bakery runs out of Strawberry mix, they want to grab one from the shelf and convert it from a final product into an ingredient that will be used to make other final products.

How can I go about turning this logic into database objects that make sense?

Here is what I tried:
I created an Inventory table that has a row for each raw material.

When the bakery buys raw material, these are stored in the inventory table normally.
The strawbery mix is inserted into the Products table to get the same treatment as the other final products (sale / discount / tax, etc).

When the bakery staff want to use the mix from the shelf, they will have to "convert" it from final to raw material. Meaning, the application will mark the product unfit for sale, and will also insert a row in the inventory table.

My solution has problems that can have ways around, but I wanted to know if there are better ways (as I am sure) of solving this problem.

You have an ingredient; let's call this "strawberry mix, packet, 100g". You also have a product; let's call this "strawberry mix". There is a Product_Ingredients row which maps the product "strawberry mix" to the ingredient "strawberry mix, packet, 100g" with a quantity of 1. No need to convert anything to anything.


What does a RED X on a database user mean? I created two new AD groups and added them as Users of a database, but their icons show with a red X.
What does this mean?

It does not mean the user is disabled (you can only disable logins), it means the user does not have connect privileges to the database. I'm not sure exactly how your users were created, but the easiest way to demonstrate this is:

CREATE LOGIN u1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE tempdb;
GO
CREATE USER u1 FROM LOGIN u1;
GO
ALTER LOGIN u1 DISABLE;
GO
-- u1 has no red x even though the login has been disabled

CREATE USER u2 WITHOUT LOGIN;
GO
-- check Object Explorer, u2 has no red x

DENY CONNECT TO u2;
GO
-- check Object Explorer, u2 now has a red x!

CREATE USER u3 WITHOUT LOGIN;
GO
-- check Object Explorer, u3 has no red x

REVOKE CONNECT FROM u3;
GO
-- check Object Explorer, u3 now has a red x!

(You may need to refresh Object Explorer between GO commands because, well, caching.)

To fix it (assuming you actually want them to be able to connect to the database):

GRANT CONNECT TO [DomainName\BI360Consultants];
GRANT CONNECT TO [DomainName\BI360Users];

Surely you'll need to apply more permissions depending on what you need them to be able to do in the database.

There may be other, more obscure ways to get into this state (e.g. adding a domain group to a role in a database without actually adding a user, as described in MichaelK's answer). Though I'll be honest, when I tried to do what the OP did, the old way or the right way, I was not able to add the domain group to a role without a user present:

-- the old way
EXEC sys.sp_addrolemember N'db_datareader', N'[CAKE\MyGroup]';

  Msg 15410, Level 11, State 1, Procedure sp_addrolememberUser or role '[CAKE\MyGroup]' does not exist in this database.

-- the right way
ALTER ROLE db_datareader ADD MEMBER [CAKE\MyGroup];

  Msg 15151, Level 16, State 1Cannot add the principal 'CAKE\MyGroup', because it does not exist or you do not have permission.

Of course with this outcome I did not see any such user in sysusers (deprecated; stop using it) or sys.database_principals. However, if I did this (thanks to sepupic's answer):

GRANT SELECT ON dbo.SomeTable TO [CAKE\MyGroup];

Then the user did show up in those views and did show up as a user in Object Explorer with the red x due to HAS_DBACCESS() = 0. Which still amounts to roughly the same thing: "can't access the database." So if the above GRANT CONNECT does not work (in my case, that did get rid of the red x, but I did not try to actually query the database as that account), also try the following, knowing that it might fail:

CREATE USER [DOMAIN\Group] FROM LOGIN [DOMAIN\Group];

In my case, when I granted connect to this user, it prevented me from running the CREATE USER command:

  Msg 15023, Level 16, State 1, Line 16User, group, or role 'CAKE\MyGroup' already exists in the current database.

This state will always be true for guest/INFORMATION_SCHEMA/sys - with the exception of the guest account on certain system databases. Ignore that and leave them alone.

From the sp_addrolemember topic:

From the sys.sysusers topic:


Skipping chunk X of Y.Z because MySQL chose no index instead of the word_sid_typeindex My environment:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.8 (Santiago)
# uname -a
Linux A.B.C 2.6.32-642.4.2.el6.x86_64 #1 SMP Mon Aug 15 02:06:41 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux
# rpm -q mysql-server percona-toolkit
mysql-server-5.1.73-7.el6.x86_64
percona-toolkit-2.2.19-1.noarch
#

I'm running pt-table-checksum and I'm getting following error:

  Skipping chunk X of Y.Z because MySQL chose no index instead of the
  word_sid_typeindex.

mysql> DESCRIBE search_index;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| word  | varchar(50)      | NO   | MUL |         |       |
| sid   | int(10) unsigned | NO   | MUL | 0       |       |
| type  | varchar(16)      | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS WHERE Name='search_index'\G;
*************************** 1. row ***************************
           Name: search_index
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3155432
 Avg_row_length: 51
    Data_length: 163250176
Max_data_length: 0
   Index_length: 415514624
      Data_free: 839909376
 Auto_increment: NULL
    Create_time: 2016-09-01 21:14:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.13 sec)

ERROR:
No query specified

mysql>

Please advice.

The message is both expected and disturbing.
WHY EXPECTED ?
I don't find this message disturbing at all. I would expect pt-table-checksum to bypass chunks since there is no defined uniqueness. Granted, InnoDB will create a row-based index called gen_clust_index when there is no PRIMARY KEY or UNIQUE INDEX. So, chunks read from the table have no rhyme or reason to its order other than order of insertion.
WHY DISTURBING ?
Since you have a replication tag on the question, now this becomes disturbing.
When you have many writes on a master, the slave may write rows in a different order than the master. If the search_index table had a PRIMARY KEY or a UNIQUE INDEX, that would stabilize checksum evaluation. Unfortunately, that cannot be said in the case of a table that has an internally generated index (gen_clust_index) as the only form of uniqueness with an InnoDB table ON BOTH MASTER AND SLAVE.
SOLUTION

Define a PRIMARY KEY for search indexon the Master.
mysqldump search_index from the Master and load it into the Slave


Can't overwrite an existing Azure SQL Managed Instance database during restore We're working on migrating some SQL Server VMs to Azure SQL Managed Instances.  We did an initial backup/restore without a problem:

RESTORE DATABASE [xxx]
    FROM URL = 'https://<account-name>.blob.core.windows.net/<container-name>/<filename>.bak'

After doing testing, we now need to do a fresh restore from our SQL VMs.  We started doing the same process we initially did but when restoring, we got an error:

Msg 1801, Level 16, State 1, Line 1
Database 'xxx' already exists. Choose a different database name.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So I tried the WITH REPLACE option.  Unfortunately:

Msg 41901, Level 16, State 2, Line 1
One or more of the options (replace) are not supported for this statement in SQL Database Managed Instance. Review the documentation for supported options.

Reviewing the documentation just tells me that WITH REPLACE isn't available on a Managed Instance.  I can't find any documentation on doing an in-place restore/overwrite of an existing managed instance database.

Am I forced to drop and re-create the database?

Yes, you will have to DROP the prior database before RESTORE as the REPLACE syntax isn't supported on Managed Instance.

Doing a RESTORE on Managed Instance (MI) will do some other work behind the scenes, too. In the General Purpose tier, you get one Azure Premium disk per data file, and that is handled completely by MI. This manifests in theRESTORE syntax by not allowing you to use MOVE (because the service essentially does that for you).

REPLACE is similarly disallowed right now, and I suspect it's also related to the magic of allocating storage and moving files on restore.


SQL native client installation App team had suggested to reinstall SQL native client for SQL server 2012.
My question here is can I download the SQL server native client installation file and just install over the existing native client?
Or do I need to remove the existing native client and install again?

do I need to remove the existing native client and install again?

No you do not need to remove any old native client. Quoting from official document Installing SQL Server Native Client

If a previous version of SQL Server Native Client is also installed on the computer, SQL Server Native Client 11.0 will be installed side-by-side with the earlier version.

Please also note that as per Microsoft after SQL Server 2012 native client download no native clients of future SQL Server versions would be available for download separately and SQL Server 2012 native client can be used for SQL Server 2012 and later versions.

Note that Microsoft will not be releasing a SQL Server 2014 or later version of the SQL Server Native Client. The SQL Server 2012 Native Client (v11, e.g. sqlncli11.dll), can continue to be utilized by SQL Server 2014 and later versions.


Fastest way to move MongoDB to a new server I have to move a 200GB MongoDB from one server to another (both Windows 2016 OS).  During this operation, it is assumed that the accompanying program will be offline, so no need to worry about inconsistent data, but I need to minimize the downtime.  Diskspace is not an issue, but downtime is.
  I've tried testing it with mongodump and mongorestore, but that took almost 20 hours to complete, which seems pretty ridiculous.  I'm looking at a couple other options:

Use copydb
Take a snapshot of the disk and/or copy the db folder directly to the new server and point the installation to that by changing the path in the .conf file.

I'm guessing #2 would be fastest if it will work, but I haven't found any real confirmation that it works on a Windows platform.  Has anyone successfully done this?  If so, is it as simple as I described?  As mentioned, MongoDB and its accompanying program can be turned off completely for this operation.

Alternatively, can anyone give me a ballpark figure on how long copydb takes versus a mongodump/mongorestore operation?

Final solution:
We went with option #2 - turning off MongoDB and manually copying the database files over to the new server.  It worked like a charm.  For future database migrations and changes, I will probably be making a replica set as suggested by @SqlWorldWide and @Markus.  That looks like it would definitely the best solution for someone who has time to properly plan a migration.


Is there a way to create a MySQL/Percona Slave without using a dump? I haven't been able to find anything so I'm sure the answer is no, what are you, a noob? but I feel the need to ask anyway :)

Is there a simple utility that would allow a new slave server to be brought up on an existing master without the need to perform a dump on the master?

I've set up slaves in the traditional manner several times so I'm not without knowledge of the process, but I'm curious if anyone has felt the same as I do, that there must be an easier way.

I would expect such a utility would use something similar to Percona's Xtrabackup, but instead of outputting to a dump file, it would stream the output directly to the slave, and then automatically enable the slave relationship after the feed has ended.

Is this realistic?

I noticed the streaming option in Xtrabackup, but couldn't find examples outside of its ability to just save a tar on another server, which isn't what I was looking for.  I want a solution that could stream directly into the destination DB without the requirement of working with a dump file at all.  This is particularly handy when working with large datasets.

Something close to what you're asking can be done with mysqldump. You can stream its output directly to the slave.

# mysqldump -h master -A --master-data --single-transaction --quick | mysql -h slave

mysqldump will add CHANGE MASTER TO to the output. However, it doesn't include MASTER_HOST, MASTER_USER and MASTER_PASSWORD. It has to be configured separately.

# mysql -h slave -e "CHANGE MASTER TO MASTER_HOST='master',
  MASTER_USER='repl', MASTER_PASSWORD='replpass'; START SLAVE;"

Additional notes:

You can run the abovementioned command on the master, or slave or any other host. Note the -h option in mysqldump and mysql. See mysql Options.
The master is not down when you're taking a dump.
There will not be any binlog ID issues because no matter where you run mysqldump -h master, the dump will come from the master. Respectively, the binlog coordinates will point to the binlog on the master.
The defaults will cause InnoDB tables to be table locked, that is unless --single-transaction is specified which negates the requirement of a table lock for a consistent dump.
Using --quick will ensure large tables are read one row at a time rather than buffering the entire row set in memory, which is more conducive to this type of streaming dump.


Getting MySQL User Variable in Sequence, While Using ORDER BY RAND() I am running a SELECT statement, which ultimately will be in an INSERT..SELECT statement.

I want a result like this

ID        COUNTER
3           1
7           2
1           3
20          4
12          5

When I run this query, I get everything in sequence:

SELECT 16132,@counter:=@counter+1 as counter
  FROM table t
 INNER JOIN (SELECT @counter:=-1) b
 LIMIT 5;

But I want it to be randomized, so I add ORDER BY RAND() at the end of the query, but then I end up with "random" values in the counter column. I think the randomized counter values match to the larger, entire result set, but is there a way I can get a counter column that will go in sequence when I am using ORDER BY RAND() ?

You need to get your random row-set first and then number the rows.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE tbl
    (`ID` int)
;

INSERT INTO tbl
    (`ID`)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
   (10),
   (11),
   (12),
   (13),
   (14),
   (15),
   (16),
   (17),
   (18),
   (19),
   (20)
;

Use LIMIT and ORDER BY RAND() together to get your random row-set:

Query 1:

SELECT *
  FROM tbl
 ORDER BY RAND()
 LIMIT 5;

Results:

| ID |
|----|
| 18 |
| 10 |
| 15 |
| 16 |
|  2 |

Wrap that into an outer query to apply the row numbering:

Query 2:

SELECT ID,@counter:=@counter+1 as counter
FROM (
  SELECT *
    FROM tbl
   ORDER BY RAND()
   LIMIT 5
  )t
INNER JOIN (SELECT @counter:=-1) b;

Results:

| ID | COUNTER |
|----|---------|
| 19 |       0 |
| 10 |       1 |
|  1 |       2 |
|  7 |       3 |
| 17 |       4 |

Running the same query again to show its really random:

Query 3:

SELECT ID,@counter:=@counter+1 as counter
FROM (
  SELECT *
    FROM tbl
   ORDER BY RAND()
   LIMIT 5
  )t
INNER JOIN (SELECT @counter:=-1) b;

Results:

| ID | COUNTER |
|----|---------|
|  4 |       0 |
|  8 |       1 |
| 18 |       2 |
| 14 |       3 |
| 16 |       4 |