Maintenance plan delete backup I succeeded in making a backup through maintenance plan.

All I need now is to find how to delete a folder (containing old backup) through the same maintenance plan.

Bob,

I am NOT a big fan of maintenance plans and I try to avoid using them. While they work ok for small needs it becomes hard to customize them but some ppl disagree with that.

You need to look at the Maintenance Plan cleanup task. Make sure you enter the extension as 'bak' without quotes instead of '.bak' without quotes. The following two articles will help for you. Let us know if you need further help.

http://msdn.microsoft.com/en-us/library/ms177182.aspx

http://www.bidn.com/blogs/BradSchacht/ssis/249/sql-maintenance-plan-doesn-t-delete-backups


Alter table - existing column as primary key I am attempting to set one of the columns in the table as primary key.

Here are the columns from the table:

Now, when I run the sql:

ALTER TABLE cb_company
ADD PRIMARY KEY(uuid);

I get the following error -

Error Code: 1072. Key column 'uuid' doesn't exist in table

Here's the output of SHOW CREATE TABLE cb_company

CREATE TABLE `cb_company` ( `uuid` text,
                            `num_employees` text,
                            `name` text,
                            `aliases` text,
                            `short_description` text,
                            `primary_role` text,
                            `funding_total_usd` int(11) DEFAULT NULL,
                            `num_investments` text,
                            `homepage` text,
                            `num_founders` int(11) DEFAULT NULL,
                            `founders` text,
                            `num_investors` int(11) DEFAULT NULL,
                            `investors` text,
                            `location` text,
                            `category_list` text,
                            `num_funding_rounds` int(11) DEFAULT NULL
                          ) ENGINE=InnoDB
                            DEFAULT CHARSET=utf8mb4
                            COLLATE=utf8mb4_0900_ai_ci

A TEXT column cannot be in an INDEX (a PRIMARY KEY is an index).  As Akina points out, uuids may as well be

uuid CHAR(36) CHARACTER SET ascii

Such is indexable.  Before changing away from TEXT, running your ALTER will give you

ERROR 1170 (42000): BLOB/TEXT column 'uuid' used in key specification without a key length

You could change the datatype, then do the ALTER.  Or if you are starting from scratch,

CREATE TABLE `cb_company` ( `uuid` CHARACTER SET ascii,
                        `num_employees` text,
                        ...
                        PRIMARY KEY(uuid)
                      ) ENGINE=InnoDB
                        DEFAULT CHARSET=utf8mb4
                        COLLATE=utf8mb4_0900_ai_ci;

It is generally better to use VARCHAR with realistic limits.

If num_employees is a number, use INT.

If announced_on is a date, consider using DATE if you ever plan to compare or manipulate as a date.

Be aware that you will have to provide the uuid value.  One way is

INSERT INTO cb_company (uuid, num_employees, ... )
     VALUES
     (UUID(), ...)

Then a problem is -- how to retrieve the value given a company name?  Perhaps you also want some other index.

Etc.


Safe:

SELECT *
FROM table
WHERE urls RLIKE '[^ -~]'

Strong:

SELECT *
FROM table
WHERE urls RLIKE '[ऀ-ॿ]'


How do you cope with Database Server Inventory Management Is it common for SQL Server DBAs to have to struggle with Server Inventory Management due to multiple parties / departments bringing online their own instances of SQL Server?

If so have you been able to successfully add controls to prevent this problem?
Also how do you monitor your environment on a regular basis to troll for unmanaged instances so that you can proactively prevent embarrassing situations like databases without backups?

I don't have direct experience with this task but know how folks get around this problem. :-)

You setup some monitoring program to look out for all SQL Server instances on the network and anytime something comes up that is NOT in your radar, you can catch them right away. There are lot of options and here are couple of them. I am pretty there might be others or many variants of these two.

http://www.straightpathsql.com/archives/2009/02/what-sql-instances-are-installed-on-my-network/

http://andrekamman.com/find-sql-servers-on-your-network-with-powershell

That said, I have to tell you that if someone brings an instance without notifying the DBA or production support team then this should be handled politically. Get your boss (in confidence) to talk to their boss and avoid future recurrence of the problem. GL.


Remove a shard in MongoDB I have a MongoDB cluster, I accidentally hosed a shard, I don't have a backup. It's pretty easy to recover the data, but after issuing the removeShard command, it says it's draining. However, the shard is unreachable, and will forever be unreachable (I no longer have access to the cloud IP address, and the data is gone). I just want to tell mongo to forget about the shard forever. Is this possible?  i.e. Can you remove a shard from mongo without the config controller having access to it?

Wiki answer generated from question comments by SDillon

No; at least in versions prior to 2.6. After 2.6 I am not 100% sure.

I've had this happen in development, thankfully a while ago. I dropped the collection and recovered the data to it. Here is a MongoDB email group post regarding this topic and your exact challenge.

Another mistake I made, once, was to monkey around in the config database and attempt to remove the shard info that way. I ruined that dev cluster.


Trying to get multiple mysql instances running on Ubuntu dev box I'm trying to get multiple instances of mysql running on a dev box for some testing.  This guy is running ubuntu so the setup's a little different than our production setups.

I'd like to find the easiest way to basically sudo service start   and have it use a different .cnf init file (other than the distro default location of /etc/mysql/my.cnf

Any ideas?

Have you looked into the mysqld_multi tool ?

This will allow you to run different configurations and different distros using the same configuration file (/etc/mysql/my.cnf) and you could adjust the init.d script to use the mysqld_multi commands instead of mysqld.

Another tool might be mysql sandbox, but I've never used that personally on any distro.


MySQL: Why is inserting data with transactions faster then without them I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200")

The stored procedure includes REPEAT loop, in which character string is constructed and inserted into table.

When all the looping is inside transaction, the procedure runs very fast, but when I insert data without transaction, running the procedure lasts 400 times longer.

Can anyone explain, why is that so?

Alternatively, if anyone has better idea how to generate sequences like this, i will be happy to hear them.

Here is the code:

-- create table
CREATE TABLE seq
(
    Code CHAR(12)
) ENGINE = INNODB;

-- fast procedure with transactions
DELIMITER $$
CREATE PROCEDURE sp_sequence(IN val_prefix CHAR(2), IN val_from INT, IN val_to INT )
BEGIN
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        INSERT INTO seq (Code) VALUES (CONCAT(val_prefix, @val));
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    COMMIT;
END$$
DELIMITER ;

-- slow procedure without transactions
DROP PROCEDURE IF EXISTS sp_sequence_slow;
DELIMITER $$
CREATE PROCEDURE sp_sequence_slow(IN val_prefix CHAR(2), IN val_from INT, IN val_to INT )
BEGIN
    SET @val = val_from;
    REPEAT
        INSERT INTO seq (Code) VALUES (CONCAT(val_prefix, @val));
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
END$$
DELIMITER ;

And here are results:

mysql> CALL sp_sequence('TX', 11000, 12000);
Query OK, 0 rows affected (0.09 sec)

mysql> CALL sp_sequence_slow('TX', 11000, 12000);
Query OK, 0 rows affected (40.07 sec)

Without explicit transaction, all statements are in auto-commit mode - so each insert is separate transaction. And that comes with some overhead for syncing etc.

If you insert all rows without commiting in between, mysql can do lot of it in memory/cache and sync it to disk only once (simplified, caches will probably be flushed multiple times by filesystem and OS).

In case of no explicit transaction each insert has to wait for disk to confirm writing all changes before returning and letting the loop continue. This can be sometimes relaxed a bit by flushing periodically instead after each transaction, but you might lose some data on failure IIRC.

On the other side - if you insert/update too many rows in one transaction, log with information for possible rollback can grow a lot and slow everything down. So transaction size should be reasonable (not millions of rows).

Some tips: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html


Mariadb started giving erroneous query results for 1 view 10.1.44-MariaDB-0ubuntu0.18.04.1

Have a view volv_action which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its datetime column is included in the where clause. This started happening in more than one database with this particular view on the same instance each with their own data in the tables (details on that part at bottom).

Data that's there:

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020;

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22

With action_date_created doing a comparison in where clause like this gets no rows returned:

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020 and action_date_created > '2020-01-01'

Change ">" to "<" and ask for records before 2020 which clearly there are none, and it will return exactly 1 record which is not before 2020.

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020 and action_date_created < '2020-01-01'

scene_id    action_date_created
11020     2020-02-08 07:14:28

Deleting that particular record will cause the next record to be retrieved instead.

Adding "order by 1" on the end causes a query to give correct results

SELECT scene_id, action_date_created FROM volv_action
WHERE scene_id=11020 and action_date_created > '2020-01-01'
order by 1

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22

A query where "action_date_created is not null" without other clauses on that column gives correct results, but not if there is one of the ">" clauses that causes failure.

Querying the underlying table with action_date_created works fine. Spot-checking other views that have similar datetime columns does not reproduce the problem. Analyzed all tables in the database.  Have recreated the view, problem persists.

Later found it is not just this database on the instance with the problem for this specific view.

There is a separate dev database on the same Mariadb instance with an identical data model but different data in it. Checked and it has started showing the same exact problem with its data as well.

The queries had worked for years. I know it worked successfully at 11pm last night, but was broken when someone ran one around 2am.

We've done a service mysql restart, but same problem persists. I'm perplexed. Any help would be appreciated.

Edit to give link to schema code:
https://raw.githubusercontent.com/volundmush/mushcode/master/Schema.sql

You get one wrong row and when that row is removed, you get the next wrong row in your result - means there are mismatches between table data and index data. You had checked through two clients and getting the same wrong result which means...

Option 1

If at all something happened to your databases around your mentioned 2 a.m. try running (for all databases)

mysqlcheck -o --all-databases

in commandline and

analyze table tablename;

Finally

optimize [no_write_to_binlog | local] table tablename;

The analyze / optimize are used again to ensure if everything is fine for your specific tables.

Option 2

Check your log files past previous night 11 p.m. If you can not find anything odd, post it here, we shall help.


How to read configuration option into variable? I want to read configuration option as TEXT but SHOW statement is strange because it does not return any rows.

create or replace function get_param()
returns text as $$
begin
  return query show custom.param; -- does not work, because show does not return setof
end;
$$ language plpgsql;

set custom.param = 'custom value';
select get_param() as param;

Is there any other way of doing this?

Actually I found answer in a question asked by me :) set session - custom variable to store user id :

SELECT current_setting('custom.param');

Besides current_setting(), SHOW is workable in this context, it's just a matter of returning a scalar rather than a set. Your function slightly fixed to work would be:

create or replace function get_param()
returns text as $$
declare v text;
begin
  SHOW custom.param INTO v;
  return v;
end;
$$ language plpgsql;

However this form does not seem to offer any advantage over calling directly current_setting(), so it's probably not worth creating such functions.


Oracle client throwing ORA-12541: TNS:no listener when i was trying to connect my remote server as-

sqlplus SYSTEM/oracle@192.168.0.171:1521/kannel

it shows me this error-

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 15 15:36:52 2013
copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

I have checked my listener, it is running

[root@roracle bin]# ./lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-NOV-2013 15:42:24

 Copyright (c) 1991, 2011, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused

tnsnames.ora file contain -

KANNEL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kannel)
     )
 )

listener.ora file contain

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
   (GLOBAL_DBNAME = kannel)
   (ORACLE_HOME = /opt/u01/app/product/11.2.0/dbhome_1)
   (SID_NAME = kannel)
 )
)

LISTENER=
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
  )
 )

sqlnet.ora file contain-

SQLNET.AUTHENTICATION_SERVICES= (ALL)

NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)

Oracle home path -

$ORACLE_HOME=/opt/u01/app/product/11.2.0/dbname_1

Oracle base path -

$ORACLE_BASE=/opt/u01/app

i have set environment variable

[root@roracle bin]# env | sort

}
_=/bin/env
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTCONTROL=ignoredups
HISTSIZE=1000
HOME=/root
HOSTNAME=roracle
LANG=en_IN
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOADEDMODULES=
LOGNAME=root
LS_COLORS=rs=<......something........>:
MAIL=/var/spool/mail/root
module=() {  eval `/usr/bin/modulecmd bash $*`
MODULEPATH=/usr/share/Modules/modulefiles:/etc/modulefiles
MODULESHOME=/usr/share/Modules
OLDPWD=/root
ORACLE_BASE=/opt/u01/app/
ORACLE_HOME=/opt/u01/app/product/11.2.0/dbhome_1
ORACLE_SID=kannel
PATH=/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/opt/u01/app/product/11.2.0/dbhome_1/bin
QTDIR=/usr/lib64/qt-3.3
QTINC=/usr/lib64/qt-3.3/include
QTLIB=/usr/lib64/qt-3.3/lib
SHELL=/bin/bash
SHLVL=1
SSH_CLIENT=192.168.0.83 35604 22
SSH_CONNECTION=192.168.0.83 35604 192.168.0.171 22
SSH_TTY=/dev/pts/0
TERM=xterm
USER=root

When i access database as a localhost -

./sqlplus / as sysdba[oracle@roracle bin]$ ./sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 16:04:02 2013

 Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> select v$database;
 select v$database
 *
 ERROR at line 1:
 ORA-01034: ORACLE not available
 Process ID: 0
 Session ID: 0 Serial number: 0

I Don't understand where i am mistaking.....Can somebody help?

Could it be that you have started the listener process as root? I can see you are trying lsnrctl status as user root, so please make sure that you start the listener process as the oracle user, and then start the database (also as the Oracle user)

Also if you get "Connected to an idle instance", this means that you are connecting to an instance that is not running, or you have set the environment wrong for connecting to the instance you want to connect to.

Make sure you have the Oracle software directory in your path, such as /u01/app/oracle/product/11.0/dbhome_1/bin

and ORACLE_SID set to the database instance name.


MySQL Cursor Batching Process I have a cursor in MySQL and what I want to do is rather than having an enormous cursor loop that can be up to 4000+ users in size, do it smaller batches so that the MySQL engine doesn't suffer.  I have been told to add a SLEEP into it, but have read it is also possible to batch it but not quite sure how to do that most efficiently.  In the middle of the loop, it goes off and does a couple of other Stored Procedures for each user, but they are quite small and quick - its the main loop below I am stuck on - any help greatly appreciated:-

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through
  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
    CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
    SELECT SLEEP(1);
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN
    LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;

Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second.

I will add that code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`(
)
BEGIN
  DECLARE v_BatchCount,v_BatchLimit INTEGER;
  DECLARE v_user_id INTEGER DEFAULT 0 ;
  DECLARE v_officialjobtitle VARCHAR(100) DEFAULT '';
  DECLARE v_location VARCHAR(100) DEFAULT '';
  DECLARE v_corp_id BIGINT DEFAULT 0;
  DECLARE v_name VARCHAR(100) DEFAULT '';
  DECLARE maxcount INTEGER DEFAULT 5; -- 1000; -- 10 ;  -- max numbers of users (testing) - change to select count(*) from users where role='Level 6';
  DECLARE loopcounter INTEGER DEFAULT 0 ;
  DECLARE user_cursor CURSOR FOR
  SELECT u.`user_id`,TRIM(u.`officialjobtitle`),TRIM(u.`location`),u.`corp_id`,CONCAT(TRIM(u.`fname`),' ',TRIM(u.`lname`)) `name` FROM users u WHERE u.`role`='Level 6' ORDER BY u.`officialjobtitle` ;
  SELECT COUNT(*) INTO maxcount FROM users WHERE role='Level 6' and corp_id=1; -- number of users to loop through

  SET v_BatchCount = 0;
  SET v_BatchLimit = 100;

  OPEN user_cursor ;
  get_users :
  LOOP
    FETCH user_cursor INTO v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name ;
    IF v_officialjobtitle<>'' THEN
        CALL sp_Step2 (v_user_id,v_officialjobtitle,v_location,v_corp_id,v_name) ;  -- Do all process for the current user
        SET v_BatchCount = v_BatchCount + 1;
        IF v_BatchCount = v_BatchLimit THEN
            SELECT SLEEP(1) INTO @dummy;
            SET v_BatchCount = 0;
        END IF ;
    END IF ;
    SET loopcounter = loopcounter + 1 ;
    IF loopcounter > maxcount THEN
        LEAVE get_users ;
    END IF ;
  END LOOP get_users ;
  CLOSE user_cursor ;

  SELECT loopcounter AS `Rows_Processed`;

END$$

DELIMITER ;

Please note the code

SET v_BatchCount = 0;
SET v_BatchLimit = 100;

and

SET v_BatchCount = v_BatchCount + 1;
IF v_BatchCount = v_BatchLimit THEN
    SELECT SLEEP(1) INTO @dummy;
    SET v_BatchCount = 0;
END IF ;

These encapsulate the SLEEP for every 100 rows.

Feel free to increase the v_BatchLimit to 1000 or more if you are dealing with large result sets.

Please note that I changed

SELECT SLEEP(1);

to

SELECT SLEEP(1) INTO @dummy;

That way, each SLEEP command does not echo anything.


Unable to add records to query in Access 2007 I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it.

There are three tables. Let's call them A, B, and C. Each has a unique primary key, but B and C also contain a foreign key that indexes into A. If building a query that does a inner join between A and B, the query is both editable and I can add new records to it. Similarly, a query that inner joins A and C is editable and supports adding new records. However, when doing inner join on A and B, then inner joining result with C, I can edit records, but not add new records. There is presumably something amiss with the "one-to-manyness" of this query, but I'm having a hard time seeing it, and also thinking through possible remedies.

With typical usage, tables A, B, and C should all have 1:1 relationships (though this is not enforced). Any ideas?

Here's the SQL syntax for the join

SELECT [Contact Information].[Record #],
        [Contact Information].[Last Name],
        [Contact Information].[First Name],
        [Study Information].[Study Group],
        [Study Information].[Study Status],
        [Clinical Information].[Contact Name],
        [Study Information].[Contact Name2]
FROM ([Contact Information]
        INNER JOIN [Study Information]
            ON [Contact Information].[Record #] = [Study Information].[Contact Name2])
        INNER JOIN [Clinical Information]
            ON [Contact Information].[Record #] = [Clinical Information].[Contact Name];

and here's a link to image that shows how Access is visually interpreting the query

Any ideas? Thanks!

Microsoft Access has a setting in the Query Properties window called Recordset Type.  You need to set this to Dynaset (Inconsistent Updates) to allow your query to be updatable.


How to pass schema prefix to queries that don't contain it I have a bunch of SQL queries that don't have a schema prefix. Without the prefix, the queries won't run.
I wonder if there is a way to apply a schema to a query, without manually adding -- e.g., copying and pasting -- the schema prefix to each query. The prefix is the same for all the queries.
Also just to add, I use MS SQL Server. Thanks!
EDIT
I am adding a sample query as requested.
The queries are basically like
   USE   DBID1000

SELECT   T1.USERID
  FROM   TABLE1 AS T1 WITH (NOLOCK)
  JOIN   TABLE2 AS T2 ON T1.NAME = T2.NAME

However, this won't run unless I add a schema prefix as shown below (assume the prefix is dbdbo
   USE   DBID1000

SELECT   T1.USERID
  FROM   dbdbo.TABLE1 AS T1 WITH (NOLOCK)
  JOIN   dbdbo.TABLE2 AS T2 ON T1.NAME = T2.NAME

Set the default schema for the user to the schema that your queries need to use.

ALTER USER [UserName] WITH DEFAULT_SCHEMA=[SchemaName]


PostgreSQL poor performing order_by ASC I have a situation where I prepare a list of buffers to process by a task queue. Currently my buffer table contains over 350.000 rows.

Every estimated 30 seconds the following query is ran to check what buffers to run:

SELECT "buffer_incomingbuffer"."id"
FROM "buffer_incomingbuffer"

WHERE (
  (
    "buffer_incomingbuffer"."run_date" <= '2019-02-28 08:20:19.243339'
    OR
    "buffer_incomingbuffer"."run_date" IS NULL
  )
  AND "buffer_incomingbuffer"."attempts" < 10
  AND "buffer_incomingbuffer"."completed_date" IS NULL
  AND "buffer_incomingbuffer"."locked_at" IS NULL
  AND "shop_id" = 'any-uuid-here'
)

ORDER BY "buffer_incomingbuffer"."created_date" ASC
LIMIT 10

With the following index:

buffer_incomingbuffer (run_date, attempts, completed_date, locked_at, shop_id, created_date)

This query takes over 500ms, whereas changing the ORDER BY into a DESC will take around 0.5ms.

Following the EXPLAIN ANALYZE execution of both queryies:

ORDER BY created_date ASC

Limit  (cost=0.42..794.50 rows=10 width=24) (actual time=541.306..541.553 rows=10 loops=1)
  ->  Index Scan using buffer_incomingbuffer_created_date_index on buffer_incomingbuffer  (cost=0.42..146110.50 rows=1840 width=24) (actual time=541.289..541.400 rows=10 loops=1)
        Filter: ((completed_date IS NULL) AND (locked_at IS NULL) AND ((run_date <= '2019-02-28 08:20:19.243339+00'::timestamp with time zone) OR (run_date IS NULL)) AND (attempts < 10) AND (shop_id = '43ca2e21-e1f5-4151-8fa3-c429c638d03d'::uuid))
        Rows Removed by Filter: 244674
Planning time: 0.168 ms
Execution time: 541.688 ms

ORDER BY created_date DESC

Limit  (cost=0.42..794.50 rows=10 width=24) (actual time=0.085..0.391 rows=10 loops=1)
  ->  Index Scan Backward using buffer_incomingbuffer_created_date_index on buffer_incomingbuffer  (cost=0.42..146110.50 rows=1840 width=24) (actual time=0.058..0.192 rows=10 loops=1)
        Filter: ((completed_date IS NULL) AND (locked_at IS NULL) AND ((run_date <= '2019-02-28 08:20:19.243339+00'::timestamp with time zone) OR (run_date IS NULL)) AND (attempts < 10) AND (shop_id = '43ca2e21-e1f5-4151-8fa3-c429c638d03d'::uuid))
Planning time: 0.206 ms
Execution time: 0.545 ms

To me it seems the biggest (for me unexplainable) difference between these two query plans is the "filter" part.

Reason of the order by is because we'd like to get the things that "came in" first, also "go out" first. The current execution time is a hiccup for the application and I am not sure how to tackle this issue. Like to hear if I am missing something!

The problem is that your buffers which have low created_date are selectively depleted in things which match your WHERE clause.  So it needs to walk more and more of the index before it accumulates 10 rows which match the WHERE.  Presumably the reason it is selectively depleted is that once you get the results of your query, you do something to the rows which cause them to no longer match the WHERE clause next time the query gets run.  I'm guessing based on the column names that that thing is setting "completed_date" to something which no longer NULL?  If so, you might want to create in index on (completed_date, created_date).  Or maybe (shop_id,completed_date, created_date).

The reason that changing the sort order improves the query is that it starts using part of the index not selectively depleted.  If you were to adopt that sort order in production, you would find that its performance quickly degrades as that part of the index would become depleted.

You showed us the definition of one index, but clearly that is not your only index, and it not clear what you believe the significance of that index is.


Is my ER Diagram correct? Why use relationships? This is the first time I've made an Entity Relationship Diagram (ERD) for database that will be built on MySQL. The business domain of relevance is about equipment issues management.

Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and do the linking in my PHP/MySQL code.

Is there a reason why I should create these relationships?

I have read a lot of info on database design and relationships.

I want to be sure the ERD I've created is correct; did I make mistakes? Or are there better ways to do this?

The ERD under consideration

What my database should do:

I have a list of users (users) that can create tickets (issue_tickets).
They will give a description of the problem, maybe add some files (issue_files).
Then other users (issue_watchers) will be attached to solve the problem.
All the users (users) can comment on a ticket (issue_tickets).
A ticket (issue_tickets) only has one status (issue_status) at a time.
A ticket (issue_tickets) only has one category (issue_category).
A ticket (issue_tickets) can have many comments (issue_comments) and many watchers (issue_watchers)
A ticket (issue_tickets) only has one user (users) who created the ticket.
A ticket (issue_tickets) only applies to one equipment (equipment).
A user can create many tickets
An equipment can have many tickets

It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources including ad hoc queries, data imports, etc. It is irresponsible to think the data in the database is protected because the application has protections. Sure you think that you will force all changes through a webservice or some such, but the reality is that no one is going to add a million new customer records, from that company you just bought, one record at a time through a service. Your database has to be designed to account for the fact that people will change it directly at the source or through other applications (some of which may not be able to use your web service). Further, the application interface is usually more likely to be thrown away or redesigned than the data and when that happens, you may lose all or some of the data integrity rules. In designing databases, you have to think about how the data needs to be protected over time not about what is easiest and most convenient for the programmer or the intial application.  A data professional sets up PK/FK relationships in the database because that is where the data lives and it is the best place to set it up to protect the data quality.

I see data from a lot of different companies in my current position and I see all too many  that didn't set up their PK/FK relationships in the database because they have data integrity problems that a correctly designed database would not have.


COPY FROM STDIN from client app I have a client appconsole. I want to execute a SQL command "copy from stdin" and send data from client STDIN to Postgres server to fill a table. Is it possible without psqlclient and just via sqlcommand copy?

Every time, in other posts when someone asks about it, folks always tell us to use psqlclient, but I don't want to use it.

I've searched already in psqlclient source code at git. There, they are using methods like "PQputCopyData(conn, buf, linelen)" from libpq.dll to process stream data.

So that's why I'm asking if it is possible to pass data using just SQL commands and client data pipe. If it's not possible, what is the reason for this command "FROM STDIN"?

Sure it's possible just TIAS.
CREATE TABLE f ( id int );

Then
$ echo 1 | psql -d test -c 'COPY f FROM STDIN'
COPY 1

In fact all of \COPY is just a wrapper around that functionality. From the docs, on COPY

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.
So that's why i'm asking, if it is possible to pass data using just sql commands and client data pipe, if it's not possible, what is the reason for this command "FROM STDIN"?

Because it's also possible for the server to open a file handle to a local file and read from that file directly. So there are two modes, the server reads from a file, or the server read reads from STDIN. \copy itself just pipes the data over the socket to the server and tells it to read from STDIN.


Is it OK to duplicate parent field in child table for performance? I have a very complicated query which involve more than 10 tables. Some parent tables are loaded only to search on one field. I wonder if I can copy this field in the child table (this data is never updated), so that I don't need to load the parent table in my query.

I've always learned that I should not store the same data in two different place, but maybe it's "allowed" for performance ?

For example, imagine a country table, a city table, and a street table. Having the country code in the street table would speed up some complicated queries.

Thanks

What you are doing is called denormalization.

The rule of thumb is "normalize till it hurts, denormalize till it works."  Denormalization should follow the access pattern of your queries.  If you have a common query that goes from joining on 10 tables to just a few with just a small bit of duplication, go for it!


Repair Mysql table structure from existing frm file i have moved my mysql database from another computer, but when i moved to the other some table seems doesn't exist, when i check to mysql data folder, the  MYI and MYD from  table is gone, just FRM left, how do i create the same table structure from existing FRM ? never mind if the data is gone. i just need the structure, so how i could do that ? by the way, i am using windows, i have try with this command but it seem doesn't work

mysql> repair TABLE m_tarif2012 USE_FRM;
+-----------------------+--------+----------+------------------+
| Table                 | Op     | Msg_type | Msg_text         |
+-----------------------+--------+----------+------------------+
| rsud_2012.m_tarif2012 | repair | Warning  | Can't open table |
| rsud_2012.m_tarif2012 | repair | status   | Operation failed |
+-----------------------+--------+----------+------------------+
2 rows in set (0.01 sec)

I have never done it, but this blog post might be useful. You should be able to just create the .MYI and .MYD files (with the correct mysql file permissions) to recover the table structure. But as you said, you won't have the data recovered.


Index Tuning on Joined Query I have 2 tables (tblA and tblB) joined by ReportID key. This key is a Primary Clustered key on tblA, and is a FK on tblB.
I have created a Non-clustered index on UserID and UserDate in tblA.

I have a query where I want to return a number of rows from tblB based off a ReportID selection from tblA.

My issue is the Execution Plan runs a Clustered Index SCAN on tblB. How do I get it to perform a seek? Or how can I improve the query execution?

Thanks!

SELECT
     b.[Asset]
    ,b.[Investment]
    ,b.[Currency]
    ,b.[Address]
    ,b.[Email]
    ,b.[Region]
    ,b.[Country]
    ,b.[Sector]
FROM
    tblB b
INNER JOIN
    tblA a
    ON a.ReportID = b.ReportID
WHERE a.UserID = @UserID
    AND a.UserDate = @Period;

Put a non-clustered index on b.ReportID, and 'include' all the other columns from b that your query uses.


SQL - sum columns with same name from diferent tables I have 3 Tables:

_tbl1_____
ID  C1  C2
--  --  --
AA   2   3
BB  10  12
CC  20  22

_tbl2_____
ID  C1  C2
--  --  --
BB  10  12
AA   2   3
CC  20  22

_tbl3_____
ID  C1  C2
--  --  --
AA   0   0
BB  10  12
HH  55  66

and I want sum the columns with same name and rows with same id
to get a result like this:

Query result tbl
ID  C1  C2
--  --  --
AA   4   6
BB  30  36
CC  40  44
HH  55  66

Thank you guys for your help.. I really need it.

If they're all in the same order, you can do it easily like this,

SELECT t.id, sum(c1), sum(c2)
FROM (
  SELECT *
  FROM tbl1
  UNION ALL ( TABLE tbl2 )
  UNION ALL ( TABLE tbl3 )
) AS t
GROUP BY t.id
ORDER BY 1;

If they're in different orders just replace TABLE tbl2 with SELECT id, c1, c2 FROM tbl2 (they're the same thing).


How to create and fill multiple session temp tables with dynamic SQL? I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON keys and their types such that I can construct a strings that contain the text to create a temp table and pivot the results of OPENJSON to INSERT into that temp table.

However, I am having issues dynamically creating the temp tables with EXECUTE( @stmt ), EXEC sp_executesql @stmt and the OPENROWSET adhoc distributed query trick.

DECLARE @dynamicCreateStmt NVARCHAR(MAX),
        @dynamicPivotInsertStmt NVARCHAR(MAX);

--CREATE statement for #temp table
SET @dynamicCreateStmt = '< some combination of string constants and results of STRING_AGG>'

--PIVOT - INSERT statement for #temp table
SET @dynamicPivotInsertStmt = '< some combination of string constants and results of STRING_AGG>'

--First try
EXEC (@dynamicCreateStmt);
EXEC (@dynamicPivotInsertStmt);
SELECT * FROM #temp into #Working1

<repeat for various "working" tables>

--Second try
EXEC sp_executesql @dynamicCreateStmt;
EXEC sp_executesql @dynamicPivotInsertStmt;
SELECT * FROM #temp into #Working1

<repeat for various "working" tables>

--Third try; tries to skip intermediate table.
DECLARE @dynamicPivotStmt NVARCHAR(MAX);

--PIVOT statement for #temp table
SET @dynamicPivotStmt = '< some combination of string constants and results of STRING_AGG>'

SELECT * INTO #Working1 FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;Database=MyDB;',
     @dynamicPivotStmt)

<repeat for various "working" tables>

The first 2 will execute but the temp table created is not in the current scope. The last wont run because a variable cannot be supplied as the query parameter.

EXECUTE ... INTO is not an option as that is limited to one occurrence per  run and I need to dynamically create additional tables as the run progresses.

Any ideas of how to dynamically create and fill multiple session temp tables
 at different locations in the same run?

Creating temp tables dynamically means you won't be able to access them in your session as you've discovered because they're scoped to a session and the dynamic execution is not actually in your session scope.

You can get around this by creating global temporary tables (##tablename instead of #tablename) instead which are accessible across open sessions.

If you have multiple sessions running the same query, make sure you dynamically assign a name that will be unique to your session, for example:

DECLARE @TableName NVARCHAR(255)

SELECT @TableName = '##MyTempTable_' + CAST(@@SPID AS NVARCHAR(5))

--CREATE statement for #temp table
SET @dynamicCreateStmt = '< some combination of string constants and results of
STRING_AGG __ Use @TableName here to define the table name for your session only>'

This should allow you to dynamically create and use your temp table but interact with it in isolation to your scope, even if running multiple instances of this query concurrently.


Safest way to uninstall the previous SQL Server versions I'm trying to free up some space in my Testing SQL Server. It has SQL Server 2005, 2008, 2008R2 and the compact or express edition. Since I only use lately the 2008R2 version, its not clear for me from the Program Files which of the older folders I should delete (if I'm doing this right?). I see folders named as (80, 90, 100, MSSQL.1,MSSQL.2, MSSQL.3, MSSQL.4, MSSQL10_50.SQL2008R2...)

I will keep the ones that has the 2008R2 obviously, but is there a better way to not accidently delete something that I'm not supposed to?

I'm using MS.Windows Server 2003 R2 Enterprise Edition. Thanks

I strongly suggest to read Working with Multiple instance of SQL Server. If it is not interrupting at all STOP the SQL Server services which is not required its more safer approach. Microsoft has history of behaving incorrectly when instance is removed from server having multiple instances present. I have seen it multiple times

IMHO If you have multiple instances its more safer to use command prompt to remove SQL Server instance as compared to GUI. Below is the dummy script. You have to open CMD as administrator and move to folder where Setp.exe file is present and then run below script.

Setup.exe /Action=Uninstall /FEATURES=SQL,AS,RS,IS,Tools /INSTANCENAME=MSSQLSERVER

You can read about uninstall parameters from Books Online. You can also have a look at how to uninstall existing instance of SQL Server

If you want to remove SQL Server 2005 This support article is very well written


Importing files with a mix of decimal separators Using SQL Server Management Studio 18.3 I want to import data (semicolon separated text file) that has some columns with dot as decimal separator, and some with comma as decimal separator. I can't find an option to import both types on the fly, forcing me to choose nvarchar for either of them, and then transform it. Changing locale first would not help either, obviously. (I find it odd that the import wizard is not locale-agnostic.) How do you best resolve this problem?

Maybe you can try to edit (with notepad++) the file before import and clean (with a carefull find and replace) the separator you don't like.


What would cause an orphaned ##MS_PolicyEventProcessingLogin##? This morning I noticed my SQL Log was filling with the following message:

  The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following:
  'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

Running the following EXEC sp_change_users_login 'report' revealed that the login had in fact been orphaned.

I was able to fix it by running the following as recommended in this MSDN post.

EXEC sp_change_users_login
    'Auto_Fix', '##MS_PolicyEventProcessingLogin##',
    NULL, 'fakepassword'

But the question remains: What in the world would have caused this principal to have been orphaned in the first place?  Googling and researching reveal that others have had this problem but I have yet to find a description of cause.  Nothing of note that I am aware of occurred the moment the error began appearing.

We moved the whole server to a SAN storage model last summer, we restored everything (including msdb) during that move but that was months ago. It was only something recent that made the symptom manifest as it doesn't appear in the log earlier than a couple weeks ago.

We upgraded two servers (from SQL 2000) to SQL 2008R2 using in-place upgrade. We started getting these messages in SQL logs after upgrade. We did not change this or any other logins or users during the upgrade process.

My guess is upgrade process left two accounts (##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##) orphaned.

EXEC sp_change_users_login 'Auto_Fix', '<User Name>' fixed this issue.


What are the major differences between mysql5.6 and mysql 5.7 Can anyone tell me the difference between mysql5.6 and mysql5.7

The release notes for MySQL 5.7 are here.

"What's new in MySQL 5.7" can be found here.

Changes of note:

ONLY_FULL_GROUP_BY is now turned on by default, meaning MySQL
will no longer return random data when a user has incorrectly
specified columns in the where clause, but missed them out of the
group by clause.
Improvements to online DDL - more ALTER operations can be done
online without the storage engine having to make a copy of the object
in question.
InnoDB now supports MySQL-supported spatial data types
InnoDB supports tablespaces
Native JSON support
A new sys schema, with summarized performance data
Multi-source replication


How to correctly use group by for getting the record with the closest time for each rec_id Background

I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of rec_ids at a given time.

For a single rec_id this query works perfectly fine to query its value at rec_time 590:

SELECT rec_time,rec_value
FROM records
WHERE rec_id = 4 AND rec_time <= 590
ORDER BY time DESC
LIMIT 1;

I use rec_time <= 590 as the latest value can be set at any instance at or before 590.

PROBLEM

I would like to extend this to work for multiple rec_ids in a single query.

SELECT rec_id,rec_time,rec_value
FROM records
WHERE rec_id IN (4,9,21,565,951,93,6,15,64)
AND rec_time <= 590
GROUP BY rec_id
ORDER BY time DESC, rec_id ASC;

The query above seems to work but I have noticed that I get inconsistent results and I figured that I'm not using GROUP BY correctly.

I have tried a few solutions found in slightly different questions here, but I haven't found any solution which gives consistent results yet.

Can this be solved with GROUP BY or should I use nested queries. If so, how? I gave a shot at using JOIN but I couldn't get it to work.

WITH cte AS ( SELECT rec_id,
                     rec_time,
                     FIRST_VALUE(rec_value) OVER (PARTITION BY rec_id ORDER BY rec_time DESC) rec_value
              FROM records
              WHERE rec_id IN (4,9,21,565,951,93,6,15,64)
              AND rec_time <= 590 )
SELECT rec_id, MAX(rec_time) rec_time, rec_value
FROM cte
GROUP BY rec_id /* , rec_value */ ;

or ever

SELECT DISTINCT rec_id,
                MAX(rec_time) OVER (PARTITION BY rec_id) rec_time,
                FIRST_VALUE(rec_value) OVER (PARTITION BY rec_id ORDER BY rec_time DESC) rec_value
FROM records
WHERE rec_id IN (4,9,21,565,951,93,6,15,64)
AND rec_time <= 590;


increasing disk space for mysql data volume I have a master mysql server which has approached 80% of the available disk space. Total disk space size is 200GB. Though, it's not an immediate concern but I have to deal with at some point soon. the data directory is mounted on to logical volume.

--- Logical volume ---
LV Path                /dev/mysql/data
LV Name                data
VG Name                mysql
LV UUID                UNse6Y-QgCX-Ap6D-UXvr-VXs5-r8dc-g91Vhn
LV Write Access        read/write
LV Creation host, time localhost, 2012-09-19 15:22:49 +0100
LV Status              available
# open                 2
LV Size                200.00 GiB
Current LE             51200
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:3

And this is mounted under /data directory.

/dev/mapper/mysql-data  /data                   reiserfs        defaults,noatime,nodiratime,notail,data=writeback 0 1

Unfortunately, the binary logs are located inside the the /data directory.

# grep -i log-bin /etc/my.cnf
log-bin                 = /data/binlog/mysql-bin.log

I am looking for the best option to increase the disk space and or moving the binary logs to
a separate disk location without having to restart mysql or with minimal downtime?

The database version is:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.61-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

Is there any good solution? Please help.

You say you have bin logging enabled.  This leads me to believe you have replication running.

To minimize down time you could promote your slave to master.  The finer details on how to do this really depends on your setup.  Maybe it will involve updating application configurations, maybe it will involve changing the DNS to your "master server".

Prior to the promotion ensure you slave is configured they way you would like, e.g. not writing binlogs to the data directory.  Make sure all the grants present on the master are on the slave.  Make sure the slave is not running in read only mode when you are ready to bounce.

Prior to promotion you'll want to ensure both the master and slave are in read only mode.   Run show master status; on the slave to see what log file and position you'll use for change master to after you reconfigure and restart the old master to run as a slave.

Once the old slave is promoted to the new master you can take your time to move the old binlogs to a new file system and reconfigure to keep new logs out of the data dir.


Can RMAN duplicate be used to "replace" an existing database? I have an unused database B, not valuable.  I'd like to make a copy of database A, but do not want to go through all the manual steps RMAN requires to set up the auxiliary database.  Can I use RMAN duplicate to copy database B over database A, replacing everything in it but reusing the instance?  I know you can synchronize database copies this way, I'm hoping this is just a more drastic version of that procedure.

Thanks!

The changes required to setup a duplicate from A (target) to B (auxiliary) in RMAN are pretty trivial.

1) db_file_name_convert AND log_file_name_convert parameters in the auxiliary database. If the file paths are exactly the same, simple name replacement should suffice:

db_file_name_convert='PROD','TEST'
log_file_name_convert='PROD','TEST'

IF your path changes are more complicated, you can specify full path replacement:

db_file_name_convert='/u01/oradata/PROD/system_PROD_01.dbf','/export/home/oracle/oradata/TEST/system_01.dbf','/u01/oradata/PROD/sysaux_PROD_01.dbf','/export/home/oracle/oradata/TEST/sysaux_01.dbf'
log_file_name_convert='/u02/oradata/PROD/redo_PROD_01.log','/export/home/oracle/oradata/TEST/redo_01.log','/u02/oradata/PROD/redo_PROD_02.log','/export/home/oracle/oradata/TEST/redo_02.log'

2) Make sure to setup an oracle password file for the instance and configured for remote login.

3) Make sure the target and auxiliary instances are configured in TNSNAMES.ORA on both instance servers (if they are different).

4) Shutdown B (auxiliary), remove all control files, redo logs and data files.

5) Startup B in NOMOUNT mode.

IF YOU ARE RUNNING 11G

6) Connect to rman like so:

rman target sys/change_on_install@A auxiliary sys/change_on_install@B

duplicate target database to "B" from active database;

Oracle will handle backup mode, compression, transport and unpack of the A database to replace the B database. When this completes, your database will be done.

IF YOU ARE RUNNING 10G

6) Configure flash recovery area in the target instance:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/oraflash' SCOPE=BOTH;

7) Backup database using rman. If your A (target) is in archive log mode, this can be done hot. If not, cold. Compressed below is optional. I always use it. The backupset ends up around 10-20% of the original instance size.

rman target sys/change_on_install@A

backup database as compressed backupset;

8) Using the location you chose above for the recovery area, copy the database to the server auxiliary is on (this is better if you have clustered/shared storage between the two servers). The date of course is the date you run the rman backup. Make sure the files end up in the same place. If there isn't room, you have options, but the easiest way on Unix/Linux is a symbolic link, or on Windows (NTFS only) a junction.

/u01/oraflash/PROD/backupset/2012_06_29

9) Using rman, duplicate the target database:

rman target sys/change_on_instance@A auxiliary sys/change_on_install@B

duplicate target database to "B";

AND, you are up.

IF you really want to do a new clone with "less" effort, then you can setup a very shell database (an init.ora file with only 1 parameter), and use the SPFILE parameter in the rman duplicate to copy the parameter file over to the new instance, converting every name based parameter on the way. The can be good, except when the test machine may have more than one instance, and memory tuning may need tweaking.


How to create a pivot table I need to create a pivot table using only two columns, home team number and away team number.

This is the full table:

But i need to combine these two into a single query:

The resulting table should be 32 records. Any ideas?

Use a union. If you want to keep track of the origin for each, you can add a constant column

Select c1 as x, c2 as y, 1 as origin from...
UNION all
Select c3 as x, c4 as y, 2 as origin from...


Postgres Point-In-Time Recovery with a limited point in time I'm configuring PostgreSQL WAL-based backup for point-in-time recovery. Actually, I don't really need any point in time beyond 1 month ago and don't want to waste time for the logs older than that.

Is that possible to combine the old logs with the current baseline snapshot, to get a new snapshot as if it was made 1 month ago while keeping all the newer logs for the PITR recovery?

To explain that schematically:

current_database == base1.tgz + older_logs + newer_logs
                 == base2.tgz + newer_logs

How to get base2.tgz?

There isn't a magic way of doing that, the options I see is:

At a defined interval, do another basebackup and remove old backups. You can use pg_archivecleanup contrib to remove the old archives (read the contents of backup_label file contained in your basebackup to check which files each basebackup needs). This is a broadly used approach, and recommended for its simplicity.
Another approach is to extract the basebackup into a directory, add a recovery.conf file and start PostgreSQL to consume the archives.

Following an example of recovery.conf to achieve that:

# restore the archives
restore_command = 'cp /path/to/archives/%f %p'
# remove archives already restored (you must install pg_archivecleanup contrib)
archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
# select a target time of last month (you must create this by hand)
recovery_target_time = '2014-01-10 00:00'
# make PostgreSQL pause when it reaches the target time
pause_at_recovery_target = true
# make the PostgreSQL stop itself
recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'

The problem is that you'll need to do it all by hand. I had no time to test it myself, but you can try and see if the following shell script works for such thing:

#!/bin/sh
# create data dir
mkdir -p /path/to/data
cd /path/to/data
# extract base backup
tar xvf /path/to/base1.tar.gz
# create recovery.conf (as the above)
cat > recovery.conf <<EOL
    restore_command = 'cp /path/to/archives/%f %p'
    archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
    recovery_target_time = '`date -d '1 month ago'`'
    pause_at_recovery_target = true
    recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'
EOL
# start PostgreSQL
pg_ctl -D . start

You can also use the recovery_end_command to recreate the .tar.gz file or just keep the backup as a directory.

Sorry for the not tested solution, but you can try it and I can edit if any mistake is found.


bulk_insert_buffer_size and InnoDB I read the following on this page

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

  bulk_insert_buffer_size MyISAM uses a special tree-like cache to make
  bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...),
  (...), ..., and LOAD DATA INFILE. This variable limits the size of the
  cache tree in bytes per thread. Setting it to 0 disables this
  optimization. Note: This cache is used only when adding data to a
  non-empty table. The default value is 8MB. This variable was added in
  MySQL 4.0.3. This variable previously was named
  myisam_bulk_insert_tree_size.

I take this to mean that bulk_insert_buffer_size has no bearing on
InnoDB tables, only on MyISAM tables. Is this correct?

Yes, you are correct. I wrote about this before: How can I optimize this mySQL table that will need to hold 5+ million rows?

WHen it comes to bulk loading InnoDB tables, one of the most frequently ignored settings to disable is innodb_doublewrite.

InnoDB Architecture

Please note the double write buffer inside the system tablespace file ibdata1. Every time a 16K page is written to an InnoDB table or transaction log, the page is written to the double wrote buffer first. All you need to do is disable it, load your data, and reenable it.

STEP 01 : Disable Fast Shutdown

mysql -uroot -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"

STEP 02 : Restart mysql without the double write buffer

service mysql restart --innodb-doublewrite=0

STEP 03 : Load the InnoDB data

STEP 04 : Restart mysql

service mysql restart

CAVEAT

You can disable the foreign key checks and the unique checks as well. Any standard mysqldump will do that for you already.


How to make MySQL log to 2 separate remote servers? Documentation on this topic is scarce at best. I have two syslog servers and I need to send MySQL logs to them. Currently, I believe the logs are stored at either .mysql_history and/or mysqld.log Does mysql have the capability within itself to log remotely, or do I need to write a script to do so?

If mysql has this capability, it seems that you could only assign it to one location. I need two, so I would need to know if this is able to be done or just go with the script again.

I tried testing one server and tried setting environment variable MYSQL_HISTFILE location to one remote server; however, this is not working.

Any help or resources?

The .mysql_history file is only for commands in the mysql client, and only for the current operating system user. I don't know of any way to log this anywhere but in .mysql_history.

The mysqld.log can be written to the local syslog daemon, so it ends up in /var/log/messages (or wherever you send syslog). You enable this with the following entries in /etc/my.cnf:

[mysqld_safe]
syslog
syslog-tag = mysql

The syslog-tag is optional, but helpful so your mysql log entries are easily separated from other syslog entries. I like to name the tag something specific to the server, if I'm going to merge mysql logs from multiple instances into one remote syslog.

Then you can use rsyslog to send the syslog content (or only the subset of entries with the given tag) to another server. See http://www.rsyslog.com/storing-and-forwarding-remote-messages/

This question might also be helpful: send logs to multiple rsyslog servers


Creating a New Database Across a Network with SQL Server 2008 R2 I appreciate that the above should not be done ('Creating a New Database Across a Network with SQL Server 2008 R2') for all of the reasons outlined here but a client wants to know how to do this and cannot be told otherwise.

To setup and test this I have two laptops connected across a local area network (machine one is 'VAIOE', machine two is 'VAION'). I am attempting to write a new database using VIAOE running SQL Server 2008 R2 Developer Edition across the network to a shared drive on VIAON called 'CShare' (VAION's C:Drive). I have enabled all of the appropriate permissions such that I am able to navigate (using VAIOE) on to VAION '\VAION\CShare\Databases\' and create a new Access database (for example).

Now to attempt to create a database (from VAIOE onto the C:Drive of VAION) using SQL Server 2008 R2 in management studio, I open a new scipt and write (USING Master):

CREATE DATABASE [networkedR2] ON  PRIMARY
( NAME = N'networkedR2', FILENAME = N'\\VAION\CShare\aaData\aaSSDBs\networkedR2.mdf' ,
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networkedR2_log', FILENAME = N'\\VAION\CShare\aaData\aaSSDBs\networkedR2_log.ldf' ,
  SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

and I get the following error

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\VAION\CShare\aaData\aaSSDBs\networkedR2.mdf" failed with the operating system error 5(failed to retrieve text for this error. Reason: 15105).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

indicating I do not have the correct permissions. Can someone tell me ewhat I have done wrong here as I have hit a wall?

Edit:
I am aware that for earlier versions of SQL Server the DBCC TRACEON(1807, -1) flag was required. This should not be the case here.

What account is SQL Server running as on VAIOE? That will be the one that needs permission to access the share on VAION. And if SQL Server isn't running as a domain account, that might be tricky to get it to authenticate correctly.


Proposal: MySQL blob handling revision Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side?  Essentially it would create its own file that was protected from everything except itself in a file-folder type of architecture so that it would be easier/quicker to write out a blob link.

Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small.

I have discussed TOAST before in the DBA StackExchange

May 01, 2012 : what is bigger than a longblob?
Mar 21, 2012 : Are many NULL columns harmful in mysql InnoDB?
Jul 19, 2011 : Different MySQL Datafile Sizes After Restoration

YOUR QUESTION

To answer your question, neither InnoDB nor MyISAM feature an external mechanism like TOAST. The closest thing for InnoDB is overflow pages (See MySQL Documentation).

SUGGESTION #1

You will have to tune for BLOBs in one or more ways:

Create a table with a BLOB field and a foreign key to the parent table for the remainder of the data.
In the event of multiple BLOBs in a single row, find a way to combine all the BLOB data into a single BLOB field and compress it and/or change the ROW_FORMAT (See MySQLPerformanceBlog in this one).
Look for the biggest BLOB in your dataset and scale up the innodb_log_buffer_size and innodb_log_file_size to accommodate 10 such BLOBs (InnoDB only)

Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

SUGGESTION #2

Switch to PostgreSQL :-(

GIVE IT A TRY !!!


PostgreSQL database size doesn't match used space on disk I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which results in using more disk space that PostgreSQL reports the DB is using.

I use following query to report size of each database:

SELECT schema_name,
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;

    schema_name     | pg_size_pretty |                pct
--------------------+----------------+------------------------------------
 production         | 605 GB         |            62.70818987165323895600
 dev                | 116 GB         |            12.05199834243206743500
 pg_toast           | 12 GB          |             1.26824870382580753200
 staging            | 12 GB          |             1.26031018275065892500
 test               | 1497 MB        |             0.15143744784303601600
 pg_catalog         | 26 MB          |         0.002621403693008641646300
 public             | 624 kB         |         0.000061661486144352849300
 information_schema | 96 kB          |         0.000009486382483746592200
 repack             | 0 bytes        | 0.00000000000000000000000000000000

this gives an idea that the occupied space should be around 750GB. However in reality PostgreSQL is using almost twice as much:

$ du -hs /var/lib/postgresql/9.6/main/base/
1.3T    /var/lib/postgresql/9.6/main/base/

Part of the problem is pgsql_tmp, that is occupying 349GB. Is there a safe way how to remove unused files from pgsql_tmp?

349G    /var/lib/postgresql/9.6/main/base/pgsql_tmp/

I've already tried VACUUM FULL and pg_repack on largest tables without any success. The only way how to get rid of wasted disk space seems to be dumping tables to SQL and reimporting into a clean server.

I've updated query for computing database sizes (indexes included):

SELECT schema_name,
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;

As for the tmp files, I've deleted all files older than 1 day:

find /var/lib/postgresql/9.6/main/base/pgsql_tmp/ -type f -mtime +1 -delete

at least in our setup queries typically takes minutes, at most hours. So, temporary files older than one day were probably left by some postgresql process that had crashed. Temp files are suffixed with process's PID, like pgsql_tmp13774.1 where 13774 should be PID of postgresql process.


How to speed up select distinct? I have a simple select distinct on some time series data:

SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';

And it takes 112 seconds. Here's the query plan:

http://explain.depesz.com/s/NTyA

My application has to preform a lot of distinct operations and counts like this. Is there a faster way to get this kind of data?

You probably don't want to hear this, but the best option to speed up SELECT DISTINCT is to avoid DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries.
Sometimes, GROUP BY is faster, because it takes a different code path.
In your particular case, it doesn't seem like you can get rid of DISTINCT (well, see blow). But you can support the query with a special index if you have many queries of that kind:
CREATE INDEX foo ON events (project_id, "time", user_id);

Adding user_id is only useful if you get index-only scans out of this. Follow the link for details. Would remove the expensive Bitmap Heap Scan from your query plan, which consumes 90% of the query time.
Your EXPLAIN shows 2,491 distinct users out of half a million qualifying rows. This won't become super-fast, no matter what you do, but it can be substantially faster. With around 200 rows per user, emulating an index skip scan on above index might pay. The range condition on time complicates matters, and 200 rows per user is still a moderate number. So not sure. See:

SELECT DISTINCT is slower than expected on my table in PostgreSQL
Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest row per user

Either way, if time intervals in your queries are always the same, a MATERIALIZED VIEW folding user_id per (project_id, <fixed time interval>) would go a long way. No chance there with varying time intervals, though. Maybe you could at least fold users per hour or some other minimum time unit, and that would buy enough performance to warrant the considerable overhead. Can be combined with either query style.
Nitpick:
Most probably, the predicates on "time" should really be:
AND "time" >= '2015-01-11 8:00:00'
AND "time" <  '2015-02-10 8:00:00';
Aside:
Don't use time as identifier. It's a reserved word in standard SQL and a basic type in Postgres.


Sql Substring and Search String I have a table in my database for storing supplier contacts and it is laid out like so:

Table: supplier_contacts
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NULL,
    supplier_id INT

I have an external VB.Net project that will be using the database, and will be passing in contact names as string with the first and last names put together.  For example:

Database:
    first_name = John
    last_name = DOe

VB.Net:
    name = "John Doe"

My thought in order to properly get the contact_id value is to just put together a stored procedure that can be called that does a search through the table using substrings and such.  My issue is I can't get it to work.

This is basically what I have some up with for a sort of "proof of concept" query to see if I can get this to work.

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact))

My problem comes when the last_name column is blank, since it isn't a mandatory field.  So I am not sure how to set up the last_name condition to essentially be an optional check.

How about something like this..

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE (first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact)))
or
first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1)


Problem migrating PostGIS I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10.
I'm using CentOS 7.X

Using pg_upgrade it should be quite straightforward, but the problem is PostGIS.
Postgres 9.3 has PostGIS 2.1 available, whereas Postgres 10 has PostGis 2.4.4.
So I compiled PostGIS 2.4 for Postgres 9.3 and installed it.
Then for each database, I've made:

ALTER EXTENSION postgis UPDATE;

In this way, I've migrated to PostGIS 2.4.

But when I run pg_upgrade, I receive this error:

  Cannot access the file «$libdir/postgis-2.1»: It doesn't exist the file or the directory.

If I do this:

cd /usr/pgsql-10/lib/
ln -s postgis-2.4.so postgis-2.1.so

It doesn't work either, because if I run pg_ugrade another time:

pg_restore: creating FUNCTION "public.geomfromewkb("bytea")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 547; 1255 90291 FUNCTION geomfromewkb("bytea") ema
pg_restore: [archiver (db)] could not execute query: ERROR:  could not find function "LWGEOMFromWKB" in file "/usr/pgsql-10/lib/postgis-2.1.so"<br>
    Command was: CREATE FUNCTION "public"."geomfromewkb"("bytea") RETURNS "public"."geometry"
     LANGUAGE "c" IMMUTABLE STRICT
     AS '$libdir/postgis-2.1', 'LWGEOMFromWKB';

Why does Postgres 9.3 reference PostGIS 2.1 after migrating to 2.4?

The other option I see is to compile PosGIS 2.1 for Postgres 10.

UPDATE 1

After reading this tip, I've done for each database I've done:

psql mydb -c 'ALTER EXTENSION postgis UPDATE;'
psql mydb -c 'ALTER EXTENSION postgis_topology UPDATE;'
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.1/uninstall_legacy.sql
psql mydb -f /usr/pgsql-9.3/share/contrib/postgis-2.4/legacy.sql

with identical results.

SELECT probin, COUNT(*)
FROM pg_catalog.pg_proc
WHERE probin LIKE '%/postgis%'
GROUP BY probin
ORDER BY probin;

returns:

       probin        | count
---------------------+-------
 $libdir/postgis-2.1 |   373

UPDATE 2

This guide explains the internals of PostGIS migration.

Clearly, PostGIS tells it migrates the extension, but doesn't apply the migration script to change the procedures.

The problem is that I forgot to migrate a template database.
Thereafter pg_upgrade upgrades successfully.


MongoDB document found on replica set but not in mongos I have a sharded cluster: two shards - each a replica set with 3 nodes, and a config replica set (with 3 nodes).

I sharded my cluster by two collections in the same database:
  - players.user_id - it's a hashed shard key
  - users._id - an unhashed shard key

I wanted to shard users._id with a hashed key. So I followed these steps https://docs.mongodb.org/manual/faq/sharding/#can-i-change-the-shard-key-after-sharding-a-collection.

Notes:
1. Since the users collection was already sharded, each backup and restore was done on the same replica set; the backup & restore was done with data dumps.
2. After I dropped my users collection I created the collection and added the _id: hashed index on it and then configured the sharding with _id: hashed.
3. After the sharding I restored the data and ran my application, which created other indexes, a _id: 1 index and other indexes - some of them being sparse.

After this I have a lot of users (like half of them) which I can find querying the mongod on any of my replica set nodes (from where I backed them up and restored), but not querying the mongos.

Also, my counts are wrong:

mongos> db.users.find({}).length()
355
mongos> db.users.find({}).count()
664

repl-set-1:SECONDARY> db.users.find({}).length()
36
repl-set-2:SECONDARY> db.users.find({}).length()
628

I have balacing enabled, all of my replica sets nodes are in sync within their replica sets, each server can reach the others, I don't have timeouts or other visible configuration problems.

mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("...")
}
  shards:
    {  "_id" : "repl-set-1",  "host" : "repl-set-1/repl-set-1-1:27017,repl-set-1-2:27017,repl-set-1-3:27017" }
    {  "_id" : "repl-set-2",  "host" : "repl-set-2/repl-set-2-1:27017,repl-set-2-2:27017,repl-set-2-3:27017" }
  active mongoses:
    "3.2.1" : 3
  balancer:
    Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
        No recent migrations
  databases:
    {  "_id" : "mydb",  "primary" : "repl-set-2",  "partitioned" : true }
        mydb.players
            shard key: { "user" : "hashed" }
            unique: false
            balancing: true
            chunks:
                repl-set-1  2
                repl-set-2  2
            { "user" : { "$minKey" : 1 } } -->> { "user" : NumberLong("-4611686018427387902") } on : repl-set-1 Timestamp(2, 2)
            { "user" : NumberLong("-4611686018427387902") } -->> { "user" : NumberLong(0) } on : repl-set-1 Timestamp(2, 3)
            { "user" : NumberLong(0) } -->> { "user" : NumberLong("4611686018427387902") } on : repl-set-2 Timestamp(2, 4)
            { "user" : NumberLong("4611686018427387902") } -->> { "user" : { "$maxKey" : 1 } } on : repl-set-2 Timestamp(2, 5)
        mydb.users
            shard key: { "_id" : "hashed" }
            unique: false
            balancing: true
            chunks:
      repl-set-1    2
      repl-set-2    2
            { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong("-4611686018427387902") } on : repl-set-1 Timestamp(2, 2)
            { "_id" : NumberLong("-4611686018427387902") } -->> { "_id" : NumberLong(0) } on : repl-set-1 Timestamp(2, 3)
            { "_id" : NumberLong(0) } -->> { "_id" : NumberLong("4611686018427387902") } on : repl-set-2 Timestamp(2, 4)
            { "_id" : NumberLong("4611686018427387902") } -->> { "_id" : { "$maxKey" : 1 } } on : repl-set-2 Timestamp(2, 5)

My questions are:
  1. How can I debug this? I tried a lot of things...
  2. What may cause this? The way that I backed up and restored data, the presence of sparse indexes?
  3. How can I fix this, if I can?

Have you followed this guide from MongoDB to restore a sharded cluster ? Apparently it is different from restoring a non-sharded cluster.

The key here is the backup and restore done on Shards for a sharded collection. The reason for the current state of your database is because, config server or cached information on Mongos are not aware of the restoring happened on Shards. So the meta information stored on config servers doesn't have latest information about the distribution of the data in shards.

You can try getting into proper state,

By restoring the cluster by following the guide.
by tying to remove shard and re-adding the shard again. So that MongoDB would drain all the the data from one replica-set to the primary replica set and then re-balance the data, after re-adding the shard. You may use, removeShard and addShard operations.


Is it allowed to for two tables to have two types of relationships between them? I have been going back and forth working with relational and no-sql databases for some time now and my questions may be the result of mixing the way of thinking about how to approach a problem.
So consider a simple multiple-choice quiz scenario where a question has an undefined number of answers (meaning it is not a fixed number of answers that each question has. One question could have two alternatives and another could have five). This question will have one correct answer. As such, if we want to have an initial translation between what I just wrote to SQL terms, it will be something like this:
Question                Answer
===============|       |==========
Id             |       |Id
---------------|------<|----------
Title          |       |Body
---------------|-------|----------
CorrectAnswerId|       |QuestionId

So in my mind, a question can have many answers, and an answer can only be linked to one question -> One-to-Many relationship between Question and Asnwer.
Also each question has one and only one correct answer -> One-to-One relationship between Question and Asnwer.
What I am trying to ask is, Is this design acceptable? If not what approach can I take?
One thing that I could do is to add a IsCorrectAnswer flag to the Answer entity. However it makes updating the correct answer to a question more difficult and (in my opinion) error-prone.
Clarification of what I mean by "allowed"
I would like the data to be normalized as much as possible (which I think won't be with my first solution) and I would like not to have any update, insert and delete anomalies either (which I think the second solution "might" have)

This might sound a bit crazy, but as noted by ypercube in the answer linked in the comments, it is best to consider this a separate relation: for which we need a new table Question_CorrectAnswer, and it's PK is QuestionID.
This means that we only need to update a single row to change the correct answer.
Our only issue is that to prevent update anomalies, whereby someone could enter an Answer as being the correct one for a different Question, we must redefine Answer's primary key as being AnswerID, QuestionID, and FK on those two columns together.
So we define our tables like this:
(please don't take this as the best way to write CREATE TABLE, I've left out constraint names for example)
CREATE TABLE Question (
    QuestionId int IDENTITY PRIMARY KEY,
    Title varchar(100) NOT NULL
)

CREATE TABLE Answer (
    AnswerId int IDENTITY,
    QuestionId int NOT NULL REFERENCES Question (QuestionID),
    Body varchar(1000) NOT NULL,
    PRIMARY KEY (AnswerID, QuestionID)
)

CREATE TABLE Question_CorrectAnswer (
    QuestionId int NOT NULL PRIMARY KEY,
    AnswerId int NOT NULL,
    FOREIGN KEY (AnswerId, QuestionID) REFERENCES Answer (AnswerID, QuestionID)
)

It does have the downside of requiring an extra join, though.


Should I join datetime to a date using cast or range? This question is a take-off from the excellent one posed here:

Cast to date is sargable but is it a good idea?

In my case, I am not concerned with the WHERE clause but in joining to an events table which has a column of type DATE

One table has DATETIME2 and the other has DATE... so I can effectively JOIN using a CAST( AS DATE) or I can use a "traditional" range query (>= date AND < date+1).

My question is which is preferable? The DATETIME values will almost never match the predicate DATE value.

I expect to stay on the order of 2M rows having the DATETIME and under 5k having the DATE (if this consideration makes a difference)

Should I expect the same behavior on the JOIN as I might using the WHERE clause? Which should I prefer to retain performance with scaling? Does the answer change with MSSQL 2012?

My generalized use-case is to treat my events table like a calendar table

SELECT
    events.columns
    ,SOME_AGGREGATIONS(tasks.column)
FROM
    events
LEFT OUTER JOIN
    tasks
        --This appropriately states my intent clearer
        ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn
        --But is this more effective/scalable?
        --ON tasks.datetimecolumn >= events.datecolumn
        --AND tasks.datetimecolumn < DATEADD(day,1,events.datecolumn)
GROUP BY
    events.columns

"It depends".

One advantage of the = predicate and cast to date is that the join can be hash or merge. The range version will force a nested loops plan.

If there are no useful indexes to seek into the datetimecolumn on tasks this would make a substantial difference.

Setting up the 5K/ 2 million rows of test data mentioned in the question

CREATE TABLE events
  (
     eventId    INT IDENTITY PRIMARY KEY,
     datecolumn DATE NOT NULL,
     details    CHAR(1000) DEFAULT 'D'
  )

INSERT INTO events
            (datecolumn)
SELECT TOP 5000 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY @@SPID), GETDATE())
FROM   spt_values v1,
       spt_values v2

CREATE TABLE tasks
  (
     taskId         INT IDENTITY PRIMARY KEY,
     datetimecolumn DATETIME2 NOT NULL,
     details        CHAR(1000) DEFAULT 'D'
  );

WITH N
     AS (SELECT number
         FROM   spt_values
         WHERE  number BETWEEN 1 AND 40
                AND type = 'P')
INSERT INTO tasks
            (datetimecolumn)
SELECT DATEADD(MINUTE, number, CAST(datecolumn AS DATETIME2))
FROM   events,
       N

Then turning on

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

And trying the CAST version

SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn
GROUP  BY events.eventId

Completed in 7.4 seconds

Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 1, logical reads 28679
Table 'events'. Scan count 1, logical reads 719

   CPU time = 3042 ms,  elapsed time = 7434 ms.

The estimated number of rows coming out of the join and into the GROUP BY was far too small (5006.27 vs actual 2,000,000) and the hash aggregate spilled to tempdb

Trying the range predicate

SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datetimecolumn >= events.datecolumn
            AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)
GROUP  BY events.eventId

The lack of an equality predicate forces a nested loops plan. As there are no useful indexes to support this query it has no option but to scan the 2 million row table 5,000 times.

On my machine that gave a parallel plan that eventually completed after 1 minute 40 seconds.

Table 'tasks'. Scan count 4, logical reads 143390000
Table 'events'. Scan count 5, logical reads 788
Table 'Worktable'. Scan count 0, logical reads 0
  CPU time = 368193 ms,  elapsed time = 100528 ms.

This time the number of rows coming out of the join and into the aggregate was grossly over estimated (at estimated 124,939,000 vs actual 2,000,000)

Repeating the experiment after altering the tables to make the respective date/time columns the clustered primary key altered the results.

Both queries ended up choosing a nested loops plan. The CAST as DATE version gave a serial version that completed in 4.5 seconds and the range version a parallel plan that completed in elapsed time 1.1 seconds with CPU time of 3.2 seconds.

Applying MAXDOP 1 to the second query to make the figures more easily comparable returns the following.

Query 1

Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 5000, logical reads 78137
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3167 ms,  elapsed time = 4497 ms.

Query 2

Table 'tasks'. Scan count 5000, logical reads 49440
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3042 ms,  elapsed time = 3147 ms.

Query 1 had an estimated 5006.73 rows coming out of the join and the hash aggregate spilled to tempdb again.

Query 2 again has a large overestimate (at 120,927,000 this time).

The other obvious difference between the two results is that the range query looks like it manages to seek into tasks more efficiently in some way. Only reading 49,440 pages vs 78,137.

The range that the cast as date version seeks into is derived from an internal function GetRangeThroughConvert. The plan shows a residual predicate on CONVERT(date,[dbo].[tasks].[datetimecolumn],0)= [dbo].[events].[datecolumn].

If Query 2 is altered to

   LEFT OUTER JOIN tasks
     ON tasks.datetimecolumn > DATEADD(day, -1, events.datecolumn)
        AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)

Then the number of reads then becomes the same. The dynamic seek used by the CAST AS DATE version reads unnecessary rows (two days worth rather than one) and then discards them with the residual predicate.

One other possibility would be to restructure the table to store the date and time components in different columns.

CREATE TABLE [dbo].[tasks](
    [taskId] [int] IDENTITY(1,1) NOT NULL,
    [datecolumn] date NOT NULL,
    [timecolumn] time NOT NULL,
    [datetimecolumn]  AS DATEADD(day, DATEDIFF(DAY,0,[datecolumn]), CAST([timecolumn] AS DATETIME2(7))),
    [details] [char](1000) NULL,
PRIMARY KEY CLUSTERED
(
    [datecolumn] ASC,
    [timecolumn] ASC
))

The datetimecolumn can be derived from the component parts and this has no effect on row size (as width of date + time(n) is the same as the width of datetime2(n)). (With an exception being if the additional column increases the size of the NULL_BITMAP)

The query is then a straight forward = predicate

SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datecolumn = events.datecolumn
GROUP  BY events.eventId

This would allow a merge join between the tables without any need to sort. Though for these table sizes a nested loops join was chosen anyway with stats as below.

Table 'tasks'. Scan count 5000, logical reads 44285
Table 'events'. Scan count 1, logical reads 717
   CPU time = 2980 ms,  elapsed time = 3012 ms.

As well as potentially allowing different logical join types storing the date separately as the leading index column would also potentially benefit other queries on tasks such as grouping by date.

As for why the = predicate shows fewer logical reads on tasks than the > <= version with the same nested loops plan (44,285 vs 49,440) this appears to be related to the read ahead mechanism.

Turning on trace flag 652 reduces the logical reads of the range version to the same as that of the equals version.


How to add index to increase speed of mysql statement How do I increase the speed of this mysql statement? taking 1.10s but was wondering if it could be made faster.

SELECT
        res_rooms.room_id AS room,
        res_rooms.type AS room_type,
        res_rooms.status AS room_status,
        IFNULL(utilizations.user_id, - 1) AS user_id,
        CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
        CONCAT_WS(0, system_users.name, camps.s_first) AS name,
        IFNULL(utilizations.start_date, NULL) AS start_date,
        IFNULL(utilizations.end_date, NULL) AS end_date,
        utilizations.keys_issued AS keys_issued,
        utilizations.resource_type AS resource_type,
        utilizations.staff_id AS staff_id,
        CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
        CONCAT_WS(0,
                students.date_of_birth,
                camps.date_of_birth) AS dob,
        CONCAT_WS(0, students.gender, camps.sex) AS gender,
        CONCAT_WS(0, students.status, camps.status) AS status,
        countries.common_name AS citizenship,
        CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
        IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
        IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
    FROM
        res_rooms
            LEFT JOIN
        utilizations ON utilizations.resource_id = res_rooms.room_id
            LEFT JOIN
        system_users ON system_users.user_id = utilizations.user_id
            LEFT JOIN
        camps ON camps.camp_id = utilizations.user_id
            LEFT JOIN
        students ON students.user_id = utilizations.user_id
            LEFT JOIN
        countries ON (students.citizenship = countries.code
            || camps.citizenship = countries.code)
            LEFT JOIN
        fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
    WHERE
        (students.status = 'B'
            OR students.status = 'VC')
            AND utilizations.start_date <= CURDATE()
    ORDER BY room_id , start_date DESC

When using Explain:

Stats of the query:

You should add an indexes for each table having NULL in the  possible_keys of EXPLAIN. Columns used for index should be the same used for JOINs. In case of countries table you have to add an index for code field while corresponding tables students and camps should be indexed on the citizenship fields each.

The common rule is:

When you join two tables with ON condition, each table should have indexed columns used for ON.

SELECT *
  FROM a
  JOIN b ON a.foo = b.bar;

Here table a should have index (foo) while table b should have index (bar)

When you filter out rows by WHERE clause, columns mentioned in the WHERE conditions should be indexed:

SELECT *
  FROM a
 WHERE a.foo = 1234;

Here table a should have index (foo).

Complex conditions that refers to the multiple columns in the same table require multicolumn indexes:

SELECT *
  FROM a
 WHERE a.foo = 1234
   AND a.bar BETWEEN '2017-07-20' AND '2017-07-21';

Here table a should have an multicolumn index (foo, bar) or (bar,foo), or even  both. It's depends on certain data stored in your table and some performance measurements should be performed. Even though for different queries different indexes can become optimal and day-wide range could be faster with (foo,bar) while month-wide range could be faster with (bar,foo).


SQL Server Permission I installed SQL Server, I deleted my windows user account, now I'm trying to access SQL Server using administrator account but it could not connect. It throws error like this

  The database TEST is not accessible. (ObjectExplorer)

Only sysadmins can manage the instance and by default Administrators are not members of the sysadmin role.

You need to restart your instance in single user mode. Here is an article on Technet that describes how to do it: Connect to SQL Server When System Administrators Are Locked Out

Basically it boils down to "restart the instance with the -m switch"

Another way to achieve the same is described in this scary post by Argenis Fernandez. In this case, he impersonates the service SID of the SQL Server instance in order to gain access as a sysadmin and add his user name back to the syadmin role.


Microsoft SQL Server Error 1475 when Mirroring Could anybody help me please.

I am trying to mirror our SQL Server 2005 to our DR site. I have successfully done half of the databases without any issues.

However, the remaining ones all have the below Microsoft SQL Server Error 1475:

Database mirroring cannot be enabled because the "DBNAME" database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror. (.Net SqlClient Data Provider)
I am not sure why this is happening as I am doing nothing different to the ones that work and that is as below. The only difference is these databases are slightly larger in size than the ones now mirroring.

Change recovery Mode to Full,
Take a full backup on Principal,
Take a log backup on Principal,
Copy to DR and restore both with Norecovery,
Start Mirroring on the Principal.
I have researched the error but most of the comments state to do the above but this is not working for me.

Any help much appreciated.

On your primary do you have a transaction log backup job running ?

what would be happening is this db is larger by the time restores are completed the primary db would've had another transaction log backup taken, you just need to copy this transaction log backup to the DR and restore and mirroring should initialise.

other option is if you have enough disk space on your log drive you can disable log backups on the primary until you complete mirroring setup and re-enable it.

hope this help.


Identify columns that allow NULL but don't contain NULL I'm working with a legacy database that never had any FOREIGN KEYs defined and not very many NOT NULL constraints, even where they are obviously applicable. I've been manually adding these in where the migrations are cheap and the applicability is obvious.

However, I'd really like a systematic way to identify some appropriate targets instead of stumbling around.

I'm wondering if it's possible to write a query (as slow as it might be) to run on a snapshot of the database that would identify every table.column which is not NOT NULL but which doesn't have any (or perhaps has very few NULLs.

I saw https://stackoverflow.com/a/17688833/56690 which is kind of the inverse problem but I am not really familiar enough with this kind of meta-SQL to know how reasonable it is to accomplish what I'm hoping.

I adapted the query from the linked answer in my question and have something I think works, but I'd prefer if it returned a table instead of just raising notices. I may improve it later but if I don't and anybody else does later, I'll switch the accepted answer. Ditto for any obvious performance improvements (though I understand this is likely to just be slow).

DO $$
DECLARE
   rec      record;
  _notnull  boolean;
BEGIN
   FOR rec IN
      SELECT format('SELECT COUNT(*) = 0 FROM %s WHERE %I IS NULL;'
                   , c.oid::regclass, a.attname) AS qry_to_run
            ,c.oid::regclass AS tbl
            ,a.attname       AS col
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname <> 'information_schema'
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system, temp, toast tbls
      AND    c.relkind = 'r'
      AND    a.attnum > 0
      AND    a.attnotnull = FALSE
      AND    a.attisdropped = FALSE
   LOOP
      EXECUTE rec.qry_to_run INTO _notnull;

      IF _notnull THEN
        RAISE NOTICE 'Table % has no NULLs in the NULLable field %'
                    , rec.tbl,rec.col;
      END IF;
   END LOOP;
END
$$;


using multiple selects on a single table mysql I have a table like this in my database

now what I want is to extract the sum of credit where credit_type equals V and sum of credit where credit_type equals R, then find the difference between the two(R and V). My result should contain identity_number, sum of credit where credit_type equals V, sum of credit where credit_type equals R, balance between the two sums. As an example looking at the above table identity_number 2885 should have something like this in my returned result 2885, 3000, 1560+370, 3000-(1560+370). How do I write such a query? hope you understood the problem.


read before write transaction Talking about transactions (db transactions), does a read operation have always precede a write operation?

That's obvious, isn't it?

Thanks in advance

*Of course my question doesn't take into account any specific locking protocol or whatever it's actually related to the following question:

A transaction T is defined by a partial order, termed <, such that:

if r[x] and w[x] belong to T, then r[x] < w[x].

Thanks again.

does a read operation have always precede a write operation

Definitely not.

Consider the following transaction:

START TRANSACTION;
UPDATE employee
   SET salary = null;
COMMIT;

No read done by the transaction controlling code:


can't connect to pgbouncer administration database I installed pgbouncer and I setup the pgbouncer.ini and my application can connect to pgbouncer and make queries.

But when I try to connect to the virtual administration database of pgbouncer by using the following command

psql -p 6543 -U postgres pgbouncer

I get the following message

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.6543"?

Try with host in your connection string. For example, if you used listen_addr = 127.0.0.1 in your config file, then use:

psql -h 127.0.0.1 -p 6543 -U postgres pgbouncer