Does mysqld wait for init-script code to complete before opening the instance? I'm trying to automate the execution of an application code at MySQL startup.
The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete before making the instance and the databases available.

I tried to set up a quick test case that doesn't seem to work as I expect:

create a simple test table and a procedure that just inserts a record

create table init_test_tab (x char);
delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  do sleep(300);
  insert into init_test_tab VALUES(p);
END
$$

call the procedure from an init-file:

$ cat /var/lib/mysql/mysql-init.sql
call xxxx.init_test('y');
$ grep init /etc/mysql/my.cnf
init-file = /var/lib/mysql/mysql-init.sql

Restart the server to verify the timings and check the content of the test table:

session 1 - the table is empty:

[Wed Nov 28 15:54:26 2012]> select * from init_test_tab;
Empty set (0.00 sec)

session 2 - restart mysqld:

# time service mysql restart
mysql stop/waiting
mysql start/running, process 3031

real    0m10.116s
user    0m0.024s
sys     0m0.068s

Note that it takes just 10s to restart.

session 1 - connection lost (as expected):

[Wed Nov 28 15:54:33 2012]> select * from init_test_tab;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR:
Can't connect to the server

[Wed Nov 28 15:54:45 2012]> select * from init_test_tab;
No connection. Trying to reconnect...
Connection id:    2
Current database: xxxxx

+------+
| x    |
+------+
| y    |
+------+
1 row in set (0.01 sec)

[Wed Nov 28 15:54:50 2012]>

Note that the procedure is invoked automatically (as expected) as the table is not empty anymore. But the insert should have been executed only after the sleep(300) call, not immediately (or, to be accurate, just in a few seconds).

So now I'm trying to understand:

If I put a procedure call (or whatever application code) in the init-file, will it be executed as expected (in my test case the sleep call seems to be skipped)?
Could I have some kind of logging of what gets executed (apart from modifying the code itself)?
If the code in the init-file takes time, could that affect the availability of the MySQL instance right after the restart?

Thanks
Dimitre

Trying to Understand #1

If I put a procedure call (or whatever application code) in the init-file, will it be executed as expected (in my test case the sleep call seems to be skipped)?

Yes, it will. In the past, I have recommended using init-file to create dedicated keycaches for MyISAM tables:

Aug 28, 2012 : MySQL InnoDB Index in swap (See Suggestion #3)
Mar 20, 2012 : optimal table design mysql with primay key and varchar value

I am currently do this for clients at my employer's company.
Trying to Understand #2

Could I have some kind of logging of what gets executed (apart from modifying the code itself)?

Unfortunately, no. You would have one of the following:

Write changes to some MyISAM table to mark stages
Run something like SELECT 'DEBUG#1;' ... SELECT 'DEBUG#2;' to see if it echoes to the error file. Place these SELECT queries in different places within your code.

Trying to Understand #3

If the code in the init-file takes time, could that ? affect the availability of the MySQL instance right after the restart

Just from the example you posted in the question, they answer would still be no. However, in order for to be sure, you will have to let mysqld tell you this. Assuming /var/log/mysqld.log is your error log, here is how to check:

Open an SSH seesion (Session1) to DB Server
Open an SSH seesion (Session2) to DB Server
In Session1, service mysql stop
In Session1, tail -f /var/log/mysqld.log
In Session2, service mysql start

Watch the output of Session1. If you see something like
121018 17:59:09 [Note] /usr/sbin/mysqld: ready for connections.

in under 5 minutes (300 seconds), this then confirms that the answer is no, the availability of the MySQL instance right after the restart is not affected.
Why does SLEEP() not work ???
As to why the SLEEP function seems to be skipped, here is my best guess:
Way back in MySQL 5.0.12, SLEEP was getting inserted into the query cache via some optimization. It could be possible that the Stored Procedure's internal code is being optimized and mysqld decided not to let SLEEP interfere with the formation of the EXPLAIN plan and its execution.
The patch for it was applied when back in MySQL 5.0. Could that patch have been missed when going from MySQL 5.0 to MySQL 5.1 and beyond? That's one possibility. Another possibility is that SLEEP is optimized away in spite of the patch.
Some use SLEEP to imbed mutexing, perform stress testing, or fancy job scheduling

https://stackoverflow.com/q/4284524/491757
https://stackoverflow.com/q/949950/491757
http://forums.mysql.com/read.php?98,242758,242792#msg-242792
http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html

Using SLEEP does not always perform as expected.
UPDATE 2012-11-27 15:50 EDT
If you really want to control the SLEEP function, DO NOT USE THE FUNCTION !!!
Create a loop that checks for the passing of time
Instead of your code
delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  do sleep(300);
  insert into init_test_tab VALUES(p);
END
$$

imbed the loop like this:
delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
  declare dt_now,dt_then datetime;
  declare x int;

  set dt_now = now();
  set dt_then = dt_now + interval 300 second;
  while dt_now < dt_then do
    set dt_now = now();
  end while;

  insert into init_test_tab VALUES(p);
END
$$

Please see if that construct work for you.


Using sp_BlitzIndex - Diagnosed as "Index Hoarder: Addicted to nulls" Context: MS SQL Server 2012

In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls".

I'm not looking for a debate over NULLs and their place. I know that there are cases where NULLs are perfectly appropriate, and even necessary. The issue here is the feedback provided by sp_BlitzIndex, and the value of enforcing the not NULL constraint.

In most of the tables sp_BlitzIndex "diagnosed" as having numerous columns that allow NULLs, there are not any actual NULL values. We programmatically don't allow them. I just never unchecked the box that says "Allow nulls" to enforce that constraint on the database level.

What benefits does enforcing this constraint provide, and why it is one of the problems that her script identifies?

Thanks!

I think the script in question is sp_BlitzIndex.

The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question:

Is it valid for those columns to all really contain nulls?
When tables are created, are people being careful to set the right
properties?

If the answer to both of those questions is yes, then it's totally fine. The check is just there because it's worth thinking about. It's in there mostly just for starting conversations about data integrity and validity, that's all.

Thanks for using the script!


Best way to implement concurrent table based queue I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have multiple instances of the processing app, spread across several servers.

How can I ensure that each record is picked by one app only? Is there a way to flag/lock the record?

Right now, to avoid two or more picking up the same link, I'm allowing each instance only to pick up a certain set of records (based in the MOD of their ID), but this is not a transparent way to increase queue processing speed just by adding new instances.

First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more suitable engines like InnoDB have a higher write penalty (to provide ACID properties) and are optimized for accessing records that are spatially and temporally close (those are set on memory). There is also not a good change notification system for MySQL- it has to be implemented as a polling. There are dozens of pieces of software more optimized for that task.

Having said that, I have seen successfully implement this kind of access if the performance/efficiency requirements are not very high. Many people cannot afford to introduce and maintain a complete separate piece of technology just for a small part of the business logic.

SELECT FOR UPDATE is what you are looking for- read serialization. While an UPDATE/DELETE will always lock the row during a running MYSQL transaction, you may want to avoid a large transaction while the process is going on, so:

START TRANSACTION;
SELECT * FROM your_table WHERE state != 'PROCESSING'
  ORDER BY date_added ASC LIMIT 1 FOR UPDATE;
if (rows_selected = 0) { //finished processing the queue, abort}
else {
UPDATE your_table WHERE id = $row.id SET state = 'PROCESSING'
COMMIT;

// row is processed here, outside of the transaction, and it can take as much time as we want

// once we finish:
DELETE FROM your_table WHERE id = $row.id and state = 'PROCESSING' LIMIT 1;
}

MySQL will take care of locking all the concurrent selects except one when selecting rows. As this can lead to a lot of locked connections at the same time, keep the initial transaction as small as possible and try to process more that 1 row at a time.


Calculating Price Based Off Price Break Table I have an orders table and a price break table.  If an itemcode exists in the price break table then the price is reduced based on the quantity.  I am not sure of what the syntax would be for a conditional like this, so I am going to seek assistance.  This is sample DDL and a query that I started on with a case statement but not sure how to finish it off

 DECLARE @Parent TABLE
(
  itemname VARCHAR(100),
  itemprice FLOAT,
  itemqty INT
)

 DECLARE @Exceptions TABLE
(
  itemname VARCHAR(100),
  qtyordered VARCHAR(10),
  itemrpice FLOAT
)

 INSERT INTO @Parent ( itemname, itemprice, itemqty )
            VALUES  (  'rr11',   0.00 ,     10 ),
                    (  'rr11',   0.00 ,     5 ),
                    (  'rr11',   0.00 ,     22 ),
                    (  'mc22',   15.00,     5 )

 INSERT INTO @Exceptions ( itemname, qtyordered, itemrpice )
            VALUES      (  'rr11',  '1-5' ,     15.50 ),
                        (  'rr11',  '6-10',     11.00 ),
                        (  'rr11',  '>11' ,     17.00 )
Select
itemname
,case when itemprice IN (Select itemrpice from @Exceptions) Then
--compare the quantities to gather the pricing info
itemqty
FROM @Parent

I would approach this using a SQL JOIN that joins both tables on the item, and the appropriate discount.

DECLARE @Parent TABLE
(
    ItemName varchar(100) NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
    , ItemQuantity int NOT NULL
);

DECLARE @Exceptions TABLE
(
    ItemName varchar(100) NOT NULL
    , MinQuantityOrdered int NOT NULL
    , MaxQuantityOrdered int NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
);

INSERT INTO @Parent (ItemName, ItemPrice, ItemQuantity)
VALUES ('rr11', 999999.99, 10)
    , ('rr11', 999999.99, 5)
    , ('rr11', 999999.99, 22)
    , ('mc22', 15.00, 5)

INSERT INTO @Exceptions (ItemName, MinQuantityOrdered, MaxQuantityOrdered, ItemPrice)
VALUES ('rr11', 1, 5, 15.50)
    , ('rr11', 6, 10, 11.00)
    , ('rr11', 11, 999999, 17.00);

SELECT p.ItemName
    , p.ItemQuantity
    , AdjustedPrice = ISNULL(e.ItemPrice, p.ItemPrice)
    , AdjustedTotal = p.ItemQuantity * ISNULL(e.ItemPrice, p.ItemPrice)
FROM @Parent p
    LEFT JOIN @Exceptions e ON p.itemname = e.itemname
        AND p.ItemQuantity >= e.MinQuantityOrdered
        AND p.ItemQuantity <= e.MaxQuantityOrdered;

The results:

+----------+--------------+---------------+---------------+
| ItemName | ItemQuantity | AdjustedPrice | AdjustedTotal |
+----------+--------------+---------------+---------------+
| rr11     |           10 |       11.0000 |      110.0000 |
| rr11     |            5 |       15.5000 |       77.5000 |
| rr11     |           22 |       17.0000 |      374.0000 |
| mc22     |            5 |       15.0000 |       75.0000 |
+----------+--------------+---------------+---------------+

You'll notice in the example above, I have arbitrarily chosen the value 999999.00 as the maximum price for item rr11 in the @Parent table.  That was only because you chose to use 0.00 for the default price of the item.  If you use 0.00 for a default price, and then forget to create an entry in the @Exceptions table for that item, people will buy as many of those items as they can order since they will be essentially free.  I used 999999 as the maximum quantity for a discount in the @Exceptions table since you need to choose some maximum number for the MaxQuantityOrdered column, and 999999 seems like a fairly high number of items to order.  If you believe you'll sell a million or more items in one order, by all means increase that.  Since the quantity columns are int data types, the maximum you could specify would be 2147483647, which is a lot.

A couple of nit-picky things I noticed:

Use the correct case, for both keywords, data types, and column names.

keywords should be in ALL CAPS, that is how they are shown in the documentation.
data types should be lower-case since that is how they are listed in sys.types
column names should be camel case, or in the very least not alllowercapscrammedtogether.  Just for sanity of the reader.

You are putting quotes around numeric values.  Don't.
You should use a numeric range for values that are numeric.  i.e. Don't use a character column like 0 - 15 for the range of quantities, use two columns, MinValue and MaxValue with 0 and 15.
This one is a bit subjective, and many people won't agree fully with me.  Don't use a char value such as ItemName as the key to join rows between tables unless you are certain those values will not change.  Use a surrogate integer key to uniquely identify each item.  Name it ItemID.  Then if you ever need to correct the spelling of an item, you won't need to update every reference to that item in the database.  Yes, you can use relational integrity to cascade those changes throughout all referenced tables, but if you just use a surrogate key from the start, you'll never have to worry about that.


How to create implicit select statements I need to create an implicit select statement for each table with all of its coluns - i.e. SELECT COL1, COL2, COL3 FROM TABLE1 instead of SELECT * FROM TABLE1.

There are too many tables to use the scripting feature in SMSS so I was hoping to use one of the system tables. I can query the table and column names from INFORMATION_SCHEMA.COLUMNS, but the I'd have to use a cursor to build the string as required.

Just hoping there's an easier way of doing it.

Thanks

This will work on 2005+ provided you don't have any columns or objects with apostrophes in them - if so you'll need to do some extra massaging to the output. Also, please don't use INFORMATION_SCHEMA unless you absolutely have to...

DECLARE @sql nvarchar(max); SET @sql = N'';

SELECT @sql = @sql + N'PRINT ''SELECT ' + STUFF((SELECT ',' + QUOTENAME(name)
    FROM sys.columns AS c WHERE [object_id] = t.[object_id]
    ORDER BY column_id FOR XML PATH,
    TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
  + ' FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

(Also, your list of columns needs to be reasonable, < 8K, for the PRINT portion of this to work.)


TokuDB + MariaDB 10: should I use a clustered index I have a large table (200 Columns.. I know, but it wasn't my design) that will grow fast and I will have a lot of useless rows in it, identified by an IP (a varchar 32 column).

The table will get hundreds of thousands of rows per day and I will need to delete few thousand rows with a specific IP in that column. During the day at regular intervals (5 min perhaps) I will need to select rows and avoid rows with certain IP. The deletions I'll probably do in the evening not to put too much load on the DB.

Should I use a clustered index or a regular index on this column? TokuDB claim there's no performance loss unlike InnoDB but still we're talking 200 columns (a lot of them empty to be fair (or null or 0s)).

I will need to add more indexes as well on a few other varchar columns as well over which I'll perform selects. Some will have huge cardinality as they're "Timestamps with milliseconds".

Will I benefit or suffer from clustered indexes?

That's a tough question to answer without more schema and workload information, but the key benefit to a clustered secondary index is that it contains not just the indexed columns and all other columns in the table. "Normal" secondary indexes contain the indexed columns and the primary key (which is used to look-up the other columns for your SELECT STATEMENTS via the PK index).

The downside to a secondary clustered index is that all of the data is stored twice. Once for your PK and once for the clustered secondary index. If your data is compressible this may be less of an issue, but a clustered index certainly takes up more space on disk than an unclustered index.

Now for the benefit. If you are doing range scans, or your query needs multiple rows for the same IP address, a clustered secondary index will perform significantly better than an unclustered one. If your database is much larger than your cache then you'll save 1 IO per row returned, which can make a significant difference for your queries.

The insert performance on the table is affected by this choice, but you didn't mention if you have an insert performance issue.


Consequences of changing NLS_TIMESTAMP_FORMAT I administer a 9.2.0.8 database where the NLS_TIMESTAMP_FORMAT is set to 'DD-MON-RR HH.MI.SSXFF AM';.

This leads to odd results when using the CAST TO TIMESTAMP function:

select CAST('14-SEP-2011' AS TIMESTAMP) "DATE" from dual;
----------
14-SEP-2020 11:00:00.000000 AM

If I alter the NLS_TIMESTAMP_FORMAT to 'DD-MON-YYYY HH.MI.SSXFF AM' then I am good to go.

Would changing the NLS_TIMESTAMP_FORMAT parameter on the database to YYYY have any consequences?

The database-level NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT parameters are not particularly useful because they are always overridden by the client settings.  So even if you change the database setting, 99+% of the time when a user comes along and connects to the database, their session is going to set a NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT based on the client's NLS_LANG or other national language settings (i.e. applications using the thin JDBC driver use the JVM's internationalization settings rather than relying on the client NLS_LANG) and overrule the setting you made in the database.  There are a few corner cases involving database jobs using DBMS_JOBS or DBMS_SCHEDULER where there is no client where the database NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT are used, I believe, but those are rather rare.

You could create a login trigger that did an ALTER SESSION to set the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT for your sessions.  That would overrule the settings the client requested when it created the session.  As Nick points out, you'd have to worry that other queries are relying on the current setting for implicit conversions and your change might break that code.

In general, you're much better off avoiding implicit conversions wherever possible but particularly when it comes to converting strings to or from dates and timestamps simply because there are so many different formats that you're setting yourself up for problems.  If you want to specify a date literal in your code, you're much better using the ANSI date and timestamp syntax

SQL> ed
Wrote file afiedt.buf

  1  select cast( date '2011-09-14' as timestamp ) dt,
  2         timestamp '2011-09-14 13:15:30' ts
  3*   from dual
SQL> /

DT                             TS
------------------------------ -----------------------------------
14-SEP-11 12.00.00.000000 AM   14-SEP-11 01.15.30.000000000 PM

If you want to convert a string to a date or a timestamp, you're better off using a TO_DATE or a TO_TIMESTAMP with an explicit format mask.


Is SQL Server Backup single or multi-threaded? If you perform a...

BACKUP DATABASE [DB_Name] TO DISK=N'c:\backups\db_name.bak' WITH STATS=10

Will that perform a single or multi-threaded write?

Our Server support company advised us that if we were to multi-thread our backups we would get better disk write performance. They advised us that SQL Backup is multi-threaded, but i think the above statement would be single-threaded. If we specify multiple DISKS to write to, then it will stripe the backup across multiple devices and therefore be multi-threaded by my understanding.

If I perform the following T-SQL while the backup is processing, i can see that the SPID performing the backup has multiple processes, but only one seems to write to disk, the others i presume are reading and doing other things. It's just the write threads i'm referring the question to.

USE MASTER
GO

SELECT *
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_requests r ON p.spid = r.session_id
WHERE spid=(spid of backup T-SQL)

Thanks in advance.

Backup to multiple files is perhaps what your adviser is referring too. You'll often see improvements (although usually modest) if you're backing up to multiple files on a single disk/array, up to the point that you're saturating source and destination IO paths obviously.

BACKUP DATABASE [MyDatabase]
TO
  DISK = N'Z:\backup\MyDatabase_File1.bak'
  , DISK = N'Z:\backup\MyDatabase_File2.bak'
  , DISK = N'Z:\backup\MyDatabase_File3.bak'
  , DISK = N'Z:\backup\MyDatabase_File4.bak'
WITH
    NAME = N'MyDatabase - Full Backup'
    , INIT
    , STATS = 10

Compression is the obvious addition if you're using 2008+. If not, there are gains to be had from experimenting with BUFFERCOUNT, BLOCKSIZE and MAXTRANSFERSIZE option. Paul Randall's article on Advanced Backup & Restore Options is a good place to start.


Migrate/Export DB from Oracle 11g Enterprise to Oracle 11g Express So I'm a DBA newbie and I've got an Oracle Enterprise server running in production with the following information under All Reports -> Data Dictionary Reports -> Version Banner, under "Your_Database_Settings".

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I would like to export/clone the database I have running on the server and import it into a new Oracle 11g Express installation to run some sandboxed tests.

Can someone help me with detailed instructions on how to make that happen?

This is example with HR user(example user in Oracle database) and Countries table. We'll extract a couple of rows with query - where region_id=2(table has 25 rows).
I created another user, HR2 and table Countries with same structure(but no FK constraints to ease import).
From HR we will export subset of Countries table and in HR2 we'll import the data.

> [oracle@orcla ~]$ expdp system/password tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:\"where region_id=2\"

Export: Release 11.2.0.1.0 - Production on Wed Dec 9 23:51:33 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:"where region_id=2"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "HR"."COUNTRIES"                            5.921 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl1/dpdump/testdump.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:51:35

[oracle@orcla ~]$ impdp system/password directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2
Import: Release 11.2.0.1.0 - Production on Wed Dec 9 23:52:04 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR2"."COUNTRIES"                           5.921 KB       5 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 23:52:06

Ok, now to check the result.

sqlplus hr2/hr2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 00:02:39 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from countries;

  COUNT(*)
----------
         5

This is a general idea so adjust it to your environment and needs.


How to aggregate transactional records by month year but based on subscription period not calendar months? I have a table containing transactional events for customers:

table: events
| id   | cust_id  | date                       | amount |
|------|----------|----------------------------|--------|
| 78   | 211      | 2010-06-18 20:10:00.000000 | 20     |
| 84   | 211      | 2010-07-18 05:53:00.000000 | 35     |
| 91   | 211      | 2010-07-27 05:45:00.000000 | 25     |
| 2136 | 211      | 2011-02-14 20:07:00.000000 | 5000   |
| 2947 | 211      | 2011-02-21 20:29:25.000000 | 22     |
| 2945 | 211      | 2011-03-14 20:48:26.000000 | 22     |

I want to produce a year and month aggregation of the sum of the amounts, but where the month periods correspond to the customer's subscription period. This period has a specific start date, for example the 20th of the month.

The query can take the inputs:

customer id - the customer to generate the report for
start day of period - the day of the month on which the "month" should start

Here is a query that naively groups by month without the custom period:

SELECT
  extract(YEAR FROM date)  AS year,
  extract(MONTH FROM date) AS month,
  sum(amount)              AS amount
FROM events
WHERE cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

Example output of this naive query:

| year | month | amount |
|------|-------|--------|
| 2010 | 6     | 20     |
| 2010 | 7     | 60     |
| 2011 | 2     | 5022   |
| 2011 | 3     | 22     |

But, given that start day for customer 211 is the 20th of the month, I want the actual output to be:

| year | month | amount |
|------|-------|--------|
| 2010 | 5     | 20     |  from May 20 - June 19
| 2010 | 6     | 35     |  from June 20 - July 19
| 2010 | 7     | 25     |  from July 20 - Aug 19
| 2011 | 1     | 5000   |  from Jan 20 - Feb 19
| 2011 | 2     | 44     |  from Feb 20 - March 19

I'm at a loss for how to go from my naive query to the query I need to generate this output.

First it calculates month according to next formula:

Month = If day < 20 then, if month = 1 then month = 12 else previous month

Obviously you can change day 20 by no matter which other day.

  NOTE: As far as I understand, this date 2010-06-18 20:10:00.000000 should
  returns Month=5 due Day=18

with calcMonth as
(
    SELECT extract(YEAR FROM date)  AS year,
           case when extract(day from date) < 20
                then (case when extract(MONTH FROM date) = 1
                           then 12
                           else extract(MONTH FROM date) - 1
                      end)
                else extract(MONTH FROM date)
                end AS month,
            amount,
            cust_id
    FROM  events
)
SELECT year,
       month,
       sum(amount) AS amount
FROM   calcMonth
WHERE  cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

year | month | amount
:--- | :---- | -----:
2010 | 5     |     20
2010 | 6     |     35
2010 | 7     |     25
2011 | 1     |   5000
2011 | 2     |     44

dbfiddle here

These are values returned by CTE:

    SELECT date as Date,
           extract(YEAR FROM date)  AS Year,
           extract(day from date) AS Day,
           extract(MONTH FROM date) AS Month,
           case when extract(day from date) < 20
                then (case when extract(MONTH FROM date) = 1
                           then 12
                           else extract(MONTH FROM date) - 1
                      end)
                else extract(MONTH FROM date)
                end AS CalMonth,
            Amount
    FROM  events

date                | year | day | month | calmonth | amount
:------------------ | :--- | :-- | :---- | :------- | -----:
2010-06-18 20:10:00 | 2010 | 18  | 6     | 5        |     20
2010-07-18 05:53:00 | 2010 | 18  | 7     | 6        |     35
2010-07-27 05:45:00 | 2010 | 27  | 7     | 7        |     25
2011-02-14 20:07:00 | 2011 | 14  | 2     | 1        |   5000
2011-02-21 20:29:25 | 2011 | 21  | 2     | 2        |     22
2011-03-14 20:48:26 | 2011 | 14  | 3     | 2        |     22

dbfiddle here


rownum won't return records above 100 I have this query:

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
    SELECT hostaddress,
           modulename,
           severity,
           escalation,
           classification,
           description,
           remarks,
           count(remarks) AS tally,
           max(entrydate) AS lastupdate,
           min(entrydate) AS firstupdate
    FROM vw_ericsson_summary
    GROUP BY hostaddress,
             modulename,
             severity,
             classification,
             escalation,
             description,
             remarks
    ORDER BY lastupdate ASC
  ) v
)
WHERE ROWNUM BETWEEN 1 AND 100

This works okay. but when rownum is greater than 100 such as 101, it won't return records anymore.

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
    SELECT hostaddress,
           modulename,
           severity,
           escalation,
           classification,
           description,
           remarks,
           count(remarks) AS tally,
           max(entrydate) AS lastupdate,
           min(entrydate) AS firstupdate
    FROM vw_ericsson_summary
    GROUP BY hostaddress,
             modulename,
             severity,
             classification,
             escalation,
             description,
             remarks
    ORDER BY lastupdate ASC
  ) v
)
WHERE ROWNUM BETWEEN 101 AND 200;

Although records are actually greater than 100, (it's 128). I'm using Oracle for Toad.

Is there any other way of returning s specified row number from a selection such as above?

Using rownum without an order by will give you random results each time you run your query. To do a proper paging, you need to put this into a derived table together with an order by

select *
from (
  select foo.*
  from foo
  order by some_column -- this is important!!
) t
where rownum between 101 and 200;

For more details please read the explanation on rownum works in the manual http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns009.htm#SQLRF00255

For a very good discussion on how to do paging efficiently in Oracle (and other DBMS) I can highly recommend this site: http://use-the-index-luke.com/sql/partial-results/fetch-next-page

Edit after revealing the real statement:

My answer doesn't really change: you are not applying an order by in the inner query.

Your current query looks like this:

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
     ....
  ) v
)
WHERE ROWNUM BETWEEN 101 AND 200;

So there is no order by applied to the statement where the rownum is generated.

You need to apply the order by when selecting from derived table named v not inside it  (and you don't really need the rownum as recnum in the inner query either)

SELECT *
FROM (
  SELECT *
  FROM (
     ....
  ) v
  ORDER BY lastupdate ASC
)
WHERE ROWNUM BETWEEN 101 AND 200;


Best configuration for MySQL? InnoDB and MyISAM I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)... but results haven't turned out how I'd hoped.
Using InnoDB the execution time is around 5.3 Seconds while for MyISAM it takes around 2.2 Seconds.

My current mysql configuration is:

innodb_io_capacity = 8000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_log_buffer_size = 32M
innodb_log_file_size = 564M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_thread_concurrency = 0

Everything else is at its default value.
My server is currently running: Ubuntu 14.04 x64
Has 12GB RAM
6 Cores
And using an SSD (service provider says it provides 10,000 IOPS)

What would be the best configuration for InnoDB if I were to only use InnoDB, and what would be the best configuration for just MyISAM (like would it for some reason be faster if I were to disable InnoDB while not using it.)?

Right now just thinking of using my server for storing stuff, and will need to write more data, than reading it... while later on, thinking of reading more than writing.
Not sure if it matters, but right now running nginx (do not have apache on the server), php 5.6 and MySQL 5.6.19.

MyISAM is faster than innoDB in insertion and that is because it relies on OS to write data to disk while innoDB insures final disk write (fsync()). moreover, innodb MVCC feature reduces the write speed. If the little delay in write is not a problem, stick to innoDB. you don't want to face lots of table crash or long table locks while your table face read and write simultaneously. this page has a lot on optimizing disk I/O of innoDB


Why does mongodb virtual memory get so large I have just compiled MongoDB in my linuxOS and not insert any data into DB.

The virtual memory is getting very large, about 40G.

  ps aux|grep mongod

mongodb  176574  0.0  0.0 423784 39008 pts/308  Sl   09:00   0:00  mongod

Why does the virtual memory so huge, I haven't insert any data in it?

MongoDB uses memory mapped files for performance I'll point you to a longer answer from me on this here but in essence large amounts of virtual memory usage are not something to worry about. In the case of virtual memory you'll see double usage with journaling, however this is less drastic then it sounds as the space is not actually used until there is an update. It is marked to the OS as being used despite being potentially empty when you start.

These aspects are all elements of how MongoDB uses the memory subsystem to provide performance, similarly to how it preallocates disk space to ensure these resources are marked and ready to serve your connections when they occur.

There's an interest presentation on MongoDB's storage layer that explains how the storage is managed and allocated, aggressively in most cases which tends to bump up these numbers.


Drop tables where the last update is over 1 week ago I can see in PMA that it stores information for every table.

I was wondering if there was an SQL query to drop any table in the database that have a last update time of more than 1 week ago?

All the metadata about tables are stored in the db named information_schema.

Try this:

SELECT
    TABLE_NAME
FROM
    information_schema.TABLES
WHERE
    UPDATE_TIME < (NOW()-INTERVAL 1 WEEK)

UPDATE: i forgot to mention this will get you all the tables that you need to drop, after that you can build the DROP statements.


How can I improve performance of the stored procedure? Stored Procedure

CREATE     procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime)
as

Declare @intdate datetime
select @intdate = max(rate_date) from Interestrate where rate_type = 'Zero'
and rate_date <= @portdate

Update transactiontable set NonDiscount = null, Discount = null, NonDiscountTcurr = null, DiscountTcurr = null,
NonDiscountNew = null, DiscountNew = null
where
port = @port and portdate = @portdate

Update tr set NonDiscount = (case sss_ind when 'P' then -exposure else exposure end) *
      dbo.Foo(ccd, 'USD', @portdate,
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),
NonDiscountTcurr = (case sss_ind when 'P' then -exposure else exposure end)
from
Phy p1
where
port = @port and portdate = @portdate
and
tr.trans = p1.trans
and
p1.Sub <> 'Option'

Update tr set NonDiscount = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end) *
      dbo.Foo(ccd, 'USD', @portdate,
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),
NonDiscountTcurr = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end)
from
Phy p1
where
port = @port and portfolio = @portfolio
and
tr.trans = p1.trans
and
p1.Sub = 'Option'

CREATE   function [dbo].[Foo]
(@currency1 varchar(10),
@currency2 varchar(10),
@portdate datetime, @month int) returns float

As
BEGIN
Declare
@CurrentRate float,
@Ratedate datetime

select @Ratedate = max(rate_date) from fx where
(
currency1 = @currency1 and currency2 = @currency2 and forward_month = @month
or
currency2 = @currency1 and currency1 = @currency2 and forward_month = @month
)
and
@portdate >= Rate_date

IF exists ( select * from fx where currency1 = @currency1
and currency2 = @currency2  and rate_date = @Ratedate and forward_month = @month)

  SELECT @CurrentRate =  Rate from fx where currency1 = @currency1 and
  currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month

ELSE

  IF exists ( select * from fx where currency1 =
  @currency2 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month)

  select @CurrentRate = 1/Rate  from fx  where currency1 = @currency2
  and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month

  ELSE
   select @CurrentRate = 1

return (@CurrRate )
END

I understand that scalar function calls can limit the performance of a query. Can someone see a way to remove the function calls, replacing them with a JOIN or CROSS APPLY or something instead, to see if this helps performance? I've tried, but haven't been able to find a way.

More info about current performance:  A single run of the procedure, updating ~25,000 rows, takes 20-25 minutes to run. Looking at the execution plan, I see a lot of reads tied up in the function, and I'm thinking that with a JOIN solution (or some such), that number of reads would drop dramatically, and the performance may improve. Plus, as noted above, I've heard scalar function calls are (in general) "bad" in queries.

Here's an attempt to rewrite the function into the procedure:

CREATE PROCEDURE [dbo].[ImproveProcedure]
(
  @port varchar(50)
 ,@portdate datetime
)
AS
BEGIN
    DECLARE @intdate datetime;
    SELECT @intdate = MAX(rate_date)
      FROM Interestrate
     WHERE rate_type = 'Zero'
       AND rate_date <= @portdate
    ;

    IF (OBJECT_ID('tempdb..#tmp_fx') IS NOT NULL) DROP TABLE #tmp_fx;
    CREATE TABLE #tmp_fx
         ( currency1 varchar(10)
          ,currency2 varchar(10)
          ,month int
          ,rate_date datetime
          ,rate float
          ,CONSTRAINT PK_tmp_fx PRIMARY KEY (currency2, month)
         );

    -- NOTE: currency1 not needed in PK, as it's always 'USD' - may want to change if add'l values are added.

    -- Get base currency pairs, months, and max dates
    INSERT INTO #tmp_fx
    SELECT currency1, currency2, forward_month, MAX(rate_date)
      FROM (SELECT currency1, currency2, forward_month, rate_date
              FROM fx
             WHERE currency1 = 'USD'
               AND rate_date <= @portdate
            UNION ALL
            SELECT currency2, currency1, forward_month, rate_date
              FROM fx
             WHERE currency2 = 'USD'
               AND rate_date <= @portdate
           ) sq
     GROUP BY currency1, currency2, forward_month
    ;

    -- Set rates based on previous query:
    --   Step 1: rates where currency1 = currency1 -- multiply
    UPDATE t
       SET rate = fx.Rate
      FROM #tmp_fx t
             INNER JOIN fx ON (    t.currency1 = fx.currency1
                               AND t.currency2 = fx.currency2
                               AND t.month = fx.forward_month
                               AND t.rate_date = fx.rate_date
                              )
    ;

    --   Step 2: rates where currency2 = currency1 -- divide
    UPDATE t
       SET rate = 1 / fx.Rate
      FROM #tmp_fx t
             INNER JOIN fx ON (    t.currency2 = fx.currency1
                               AND t.currency1 = fx.currency2
                               AND t.month = fx.forward_month
                               AND t.rate_date = fx.rate_date
                              )
     WHERE fx.rate IS NULL
    ;

    -- NOTE - unset rates left NULL - will force to 1 in final query

    UPDATE tr
       SET NonDiscount = null
          ,Discount = null
          ,NonDiscountTcurr = null
          ,DiscountTcurr = null
          ,NonDiscountNew = null
          ,DiscountNew = null
     WHERE port = @port
       AND portdate = @portdate
    ;

    UPDATE tr
       SET NonDiscount =  CASE WHEN p1.Sub <> 'Option'
                            THEN CASE sss_ind
                                   WHEN 'P' then -exposure
                                   ELSE          exposure
                                 END
                            ELSE CASE WHEN buysell IN ('A', 'S')
                                   THEN -vol * markprice
                                   ELSE vol * markprice
                                 END
                          END
                        * ISNULL(fx.Rate, CAST(1 as float))
          ,NonDiscountTcurr = CASE WHEN p1.Sub <> 'Option'
                                THEN CASE sss_ind
                                       WHEN 'P' then -exposure
                                       ELSE          exposure
                                     END
                                ELSE CASE WHEN buysell IN ('A', 'S')
                                       THEN -vol * markprice
                                       ELSE vol * markprice
                                     END
                              END
      FROM tr
             INNER JOIN Phy p1 ON (tr.trans = p1.trans)
             LEFT  JOIN #tmp_fx fx ON (    'USD' = fx.currency1
                                       AND ccd = fx.currency2
                                       AND portdate = fx.rate_date
                                       AND  CASE
                                              WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) < 1
                                                THEN 1
                                              WHEN DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature)) > 48
                                                THEN 48
                                              ELSE   DATEDIFF(month, portdate, COALESCE(priceend1, priceend2, mature))
                                            END
                                          = fx.month
                                      )
     WHERE port = @port
       AND portdate = @portdate
       AND p1.Sub IS NOT NULL
    ;
END;

Your function uses ccd, portdate, and the number of months between portdate and three possible dates (using the first that's not NULL) to calculate the rate that the exposure should be multiplied by.

If you can find a match where currency1 is 'USD', then you multiple by rate;
If the only match is where currency2 is 'USD', then you divide by rate;
if no match is found, the rate defaults to 1

So, let's try to build a table with your available rates in it.

We have basically have two factors that identify the rate_date values we'll use:

the month, an integer between 1 and 48;
the two currencies, one of which will be 'USD', the other of which will vary.

The maximum date for each of these will determine what rate is needed.

That gives us at most 48 * (number of currencies) rows to worry about, in practical terms. I'll guess that's 10-15 thousand rows, not an unreasonably large table.

For joining purposes, it will help to standardize the currency, so the first one is always USD - that way we don't have to worry about whether we're multiplying or dividing by the rate.

So, we build a temporary table with the unique values of currency1, currency2, and forward_month (always putting 'USD' in the temp table's currency1 column), and the maximum rate_date for each unique set of those three values.

Then, we add in the rates. In case it's relevant, we'll do it just like you did; If we have a case where 'USD' is in fx as currency1, we use that in preference over any rows where 'USD' is in currency2. Where 'USD' is in currrency1, we store rate directly; where it's in currency2, we need to divide by the rate, so we store 1 / rate.

Then, instead of using the function to find the rate, we join to the temporary table, based on:

the temp table's currency1 being 'USD'
the temp table's currency2 being the ccd from the other tables in the query
the rate_date matching the portdate column (which matches the passed-in @portdate)
the month matches the number of months between portdate, and the first of priceend1, priceend2, and mature that's not NULL. (Note: COALESCE takes a list of two or more values, and returns the first non-NULL value from the list; it's a little easier to read than ISNULL(x,ISNULL(y,z)), but works the same)

We make this a LEFT JOIN, so we include rows where we can't find a matching row in the temp table.

Then, we simply multiply the appropriate value (depending on the value of Sub) by the rate returned (or by 1, if we get back a NULL, either because the temp table had no match, or because the temp table had a matching row with a NULL rate).

NOTE: I combined your original two UPDATE statements into one, adding another CASE on the value of p1.Sub. To guarantee that this is working like your original two statement version, I also put in a check to see if p1.Sub is NULL; if that cannot be NULL, then you can remove that check.

Try this out and see if it works for you. It's not fully optimized (I think we could reduce the rows in the temp table by checking for currencies that are actually in our target transaction rows, for example) but it does remove the row-by-row nature of the function you were using.

NOTE: code is untested.


MySQL 5.7 Upgradation - Binary log basename Recently upgraded to MySQL 5.7 and I am in the process of setting up binary log. I have enabled the binary log but I am not able to set the log_bin_basename and binary log path. It showing it is a global read only variable. I need to separate binary logs from data directory. How to edit the log_bin_basename?. After editing in configuration file, the server not starting. Thanks in advance.

After, discussion with my colleagues, I found that, the log_bin_basename is read only variable and it took the value from the log-bin definition. So I added another variable log-bin, without modifying the default log_bin variable. Now the binary log enabled and basename pointed to the separate directory.

# BINARY LOGGING
#log_bin # This is default
log-bin                       =       /logs/binlog/mysql-bin
log_bin_basename              =       /logs/binlog/mysql-bin
expire_logs_days              =       5
sync-binlog                   =       1


SQL Server Temporal Tables History Table size I have a question regarding the under the hood parts of the History Table in the Temporal Tables feature of Microsoft's SQL Server.

To ask it I am going to create a hypothetical situation.  Say I have a table with 300 columns in it.  Each column has a char(10) in it.  (3,000 bytes).

I insert a row into the main table.

I then go an modify one of the columns with an update statement.  That causes the modified row to be inserted into the history table.

I then change that same column 4 more times (5 modifications total.)

What I am wondering is if that means that the size of my history table is now 3,000 x 5 = 15,000 bytes?

Or does it do some kind of compression under the hood to realize that most of the data did not change and does not need to be stored each time?

Or does it do some kind of compression under the hood to realize that
  most of the data did not change and does not need to be stored each
  time?

By default, the  temporal table's history table is PAGE compressed.

Source

  To ask it I am going to create a hypothetical situation. Say I have a
  table with 300 columns in it. Each column has a char(10) in it. (3,000
  bytes).

  I insert a row into the main table.

  I then go an modify one of the columns with an update statement. That
  causes the modified row to be inserted into the history table.

  I then change that same column 4 more times (5 modifications total.)

  What I am wondering is if that means that the size of my history table
  is now 3,000 x 5 = 15,000 bytes?

Testing

Here is some pseudo code to create the table & add  300 char(10) columns

CREATE TABLE dbo.BigTable (id int identity(1,1)  primary key not null   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON)
;

  To ask it I am going to create a hypothetical situation. Say I have a
  table with 300 columns in it. Each column has a char(10) in it. (3,000
  bytes).

SELECT
TOP(300)
 'ALTER TABLE dbo.BigTable ADD COLUMN'+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(50))+ ' char(10)'
FROM master..spt_values;

  I insert a row into the main table.

INSERT INTO dbo.BigTable
(
Columns 1 Until 300
)
VALUES
('bla', * 300);

  I then go an modify one of the columns with an update statement. That
  causes the modified row to be inserted into the history table. I then
  change that same column 4 more times (5 modifications total.)

UPDATE dbo.BigTable
SET [COLUMN7] = 'blabla';
GO 5

  What I am wondering is if that means that the size of my history table
  is now 3,000 x 5 = 15,000 bytes?

Well, when using the query from this source

This is the result:

SchemaName  TableName   RowCounts   Used_MB Unused_MB   Total_MB
dbo MSSQL_TemporalHistoryFor_2002106173 5   0.02    0.05    0.07

We get about 0.02 MB used for this history table, which is more than the 0.015mb specified.

However when we check SSMS, the details are lower.

Only 0.008 MB?

A simplified version of the query being run is this one:

DECLARE
@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)
SELECT @_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'MSSQL_TemporalHistoryFor_2002106173',@_msparam_3=N'dbo'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

SELECT         ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)

Even more simplified, we see that the history table's clustered index data is only one data page;

SELECT  i.name, i.index_id,a.data_pages,i.type_desc
  FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.name = 'ix_MSSQL_TemporalHistoryFor_2002106173'

Result

name    index_id    data_pages  type_desc
ix_MSSQL_TemporalHistoryFor_2002106173  1   1   CLUSTERED

Why the difference?

This is due to the ssms query only considering  data_pages. This differs from  used_pages

Consider this query:

SELECT  i.name, i.index_id,a.data_pages,a.used_pages,a.total_pages, i.type_desc
  FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.name = 'ix_MSSQL_TemporalHistoryFor_2002106173'

name    index_id    data_pages  used_pages  total_pages type_desc
ix_MSSQL_TemporalHistoryFor_2002106173  1   1   2   9   CLUSTERED

Not all used_pages will be data_pages, while all data_pages will be used_pages.

There are overhead pages like PFS, GAM, SGAM, HEADER PAGE etc.

Summary

In our example, due to  the PAGE compression, the data stored in the history table is in the 8KB range.
This results in one data page used.

The SSMS Query only considers data_pages, while the other query takes into account all used_pages.

For each update, an additional row is stored in the history table, without (page) compression, the data pages would amount to 2 (and be above or equal to 15k bytes).


postgres: do I really need to use Collate and Ctype when restoring database? Need to import in Linux a Windows postgres exported database.
In windows it's listed as follows:
    Name     |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-------------+----------+----------+--------------------+--------------------+-----------------------
   the_db    | postgres | UTF8     | Hebrew_Israel.1255 | Hebrew_Israel.1255 |

Do I really need to create the database using the same collate and ctype before restoring?
I already tested, and I can import just by creating a new database which will make it using its defaults:
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 the_db    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Restoring was successful. Hoever, I am afraid because of the application, not to cause any errors when interacting with the db later.
What are your consideration gentleman?

The main consideration is that if you use ORDER BY with a column of a string data type, the result will be sorted according to the database collation unless you specify an explicit COLLATE clause for the column definition or with the ORDER BY. If your users expect the results sorted according to Hebrew sorting rules, they might be unhappy.
To get a Hebrew collation on Unix, use the locale he_IL.utf8.


Updating a big replicated Dimension (SQL Server PDW) We use a SQL Server PDW appliance for our data warehouse. One of the tables in our warehouse is a replicated table with about 20 million rows. As part of our ETL process we need to expire old records from this dimension; however, we are seeing that updating a handful of records (<100) takes over 1 hour to complete. This is what I would like to improve if I can.

Naturally, one option that I thought about was changing this Dimension from Replicated to Distributed. My testing shows that it would fix the issue with the ETL process taking long (from 1.5 hours came down to 30 secs) but all the joins against the Distributed version of this dimension would be affected since the joins are almost never based on the same distribution column. When I look at the execution plan of some of these queries I usually see either a ShuffleMove or a BroadcastMove operation.

So my question to the PDW guru's here is:

Is there anything else that can be done in order to improve the performance of updating records in the replicated version of this Dimension?

Again, moving to a Distributed table doesn't seem to be the best solution since it will affect hundreds of already written SQL queries and reports developed by other people.

A few questions. 20 million rows isn't necessarily that large.

What process are you using to perform your updates and deletes right now?

Is the dimension a CLUSTERED COLUMNSTORE INDEX, CLUSTERED INDEX or HEAP?

Are you saying that there is movement whilst you update and delete this table or did you just see movement when you changed the table from replicated to distributed?

If it is the latter that is not surprising. You are unlikely to be join and aggregation compatible. If you are doing something to trigger the movement through your update / delete then we could look at that - although a concrete example would be helpful.

In general terms I would start by trying to keep the ETL simple.

Use CTAS against the dimension selecting only the rows you want to keep, union in any new rows and use CASE to pick up any changes (converting the UPDATE into a transform within the CTAS). Once complete you can then use a pair of RENAME OBJECT commands to switch from the current table to the new table. This gives you the added benefit of having a historic view of your table - which you can drop at your leisure.


Show a Warning on logon in MariaDB I pretend to show a personalized message at login in MariaDB ver. 10.1.37 , in accordance with the data in one table.

My idea was to use a stored procedure as shown in fromdual.com but i can't find the my.cnf in my disk (I'm using Xampp), so i created the file and added SET GLOBAL init_connect="CALL audit.login_trigger()"; but now I have this error:

  error: Found option without preceding group in config file:
  C:\xampp\mysql\my.cnf at line: 1 Fatal error in defaults handling.
  Program aborted

Q1)What preceding group should I type?
In this pdf from fromdual there is no details on this topic.

Q2) is there any other method? I have look in to this question but I need to check the tables to create the message

This same question has been made in server fault, but I think that this site is more adequate.

Your my.cnf file should have:

[mysqld]
init_connect="CALL audit.login_trigger()"

Or any other group name from running mysqld --help --verbose, there's a line like The following groups are read: mysqld server mysqld-10.1 mariadb mariadb-10.1 client-server galera, so any of those groups be can used instead of [mysqld].

SET GLOBAL ... is for setting mysqld system variables at runtime. The config file takes the shorter form.


What's the maximum number of listeners an Oracle database can have? Can an Oracle database have more than one listener? If so, what's the maximum number of listeners a database can have?

To the best of my knowledge, there is no limit.  Of course, it is exceptionally rare that you would have more than one listener for a database.  I've seem people run two listeners just in case one listener fails but I've never heard of anyone wanting to run more than two.  If you really wanted to, though, nothing stops you from running a dozen listeners on a dozen different ports or on a dozen different machines.  But since the listener is virtually never the bottleneck and the listener virtually never fails, that sort of redundancy is rarely helpful.


User created automatically because of AD Group? I have a Development SQL Server that I'm managing with Active Directory Groups, sometimes when I do my checks on the server I find that some users were "automatically" added to SQL Server Database with their schema as default...

I noticed this because one user created a view and it was created with on his own schema...

Is there any configuration parameter that is allowing this? How can this be avoided?

Thanks,
Jimm

This is expected / documented behavior as noted in the documentation for CREATE SCHEMA (in the Remarks section):

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

  A login has CONTROL SERVER privileges.
  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

  When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.

I emphasized part of that final statement: you / they need to specify a schema name when creating objects (i.e. CREATE TABLE Sales.TableName instead of CREATE TABLE TableName). The schema name that you specify does not need to be dbo, it just needs to already exist.


Reading data from oracle 8 in 2019 I'm an intern stuck dealing with a database system that is as old as I am.

The Issue:

I have a perl script pulling data from a server running Oracle 8 (8.1.7) and placing the data into a .csv.  The server that runs the script is being decommissioned and I am now need to port that script into a new server running a client for oracle 11 (11.2.0.4.0).  The problem is that when I run that script in the new server I get an error:

  failed: ORA-03134: Connections to this server version are no longer
  supported. (DBD ERROR: OCISessionBegin)

All servers are linux servers

The questions:

Documentation reagarding Oracle 8 is scarce but I'm assuming the error is happening because Oracle client 11 dosen't support oracle 8.  Assuming I can find a copy of client 8 is it possible to load 2 clients on one machine?
I could easily re-write the script in Java. The issue is figuring out how to find a driver that is compatible with Oracle 8, what is the latest version of OJDBC that supports oracle 8?

I'm open to other suggestions as to how to read data from Oracle 8. Using a server running Oracle 10 as an in-between is not an option

List of method (I know) to transfer data from one Oracle DB to another

database link
Data Pump (expdp/impdp)
exp/imp
SQL Developer -> Migrate Database
custom script ( eg PERL )
CSV/XML Dump and Import
ETL software (eg Informatica

The largest problem of any of those is "what is supported?".

*Database Link is only "supported" between a "2 major versions".  eg: 10g can talk to 8i but 11g can't.

Data Pump should support a lot larger range.  I don't think expdp was made available until 10g.

Classic Export/Import is limited on what is supported.  Luckily, Oracle Support has a document for "what works with what".  Ask your supervisors to get a copy of Doc ID 132904.1 for you.  YOU MUST HAVE A VALID SUPPORT CONTRACT TO ACCESS THIS DOCUMENT.

SQL Developer might help you out.  You'll need to import a JDBC driver that can talk to 8i.  You may have to contact Oracle Support to get that driver.  (latest is a 10g driver)

custom script can have problem if you cannot load two different drivers simultanously.

CSV/XML concept needs to be carefully thought out.  beware of \n.

ETL Software still needs the appropriate driver.

REMEMBER

10g is the latest that can talk to 8i.
11g is the latest that can talk to 9i.

Oracle Support has all the knowledge of "what works with what".

Update (2019-09-23)

Someone figured out how to import the Oracle 8i JDBC thin driver into an 18c database in order to run queries against Oracle 7.

https://community.oracle.com/thread/4268386


MySQL cluster error: "Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock" I am trying to configure MySQL cluster under three Windows 7 machines with disabled firewall, cluster information is like the following:

2 hosts with each running 1 data node

3rd host running management node and 2 MySQL Servers (MySQLD)

MySQL cluster application has been downloaded from http://dev.mysql.com/downloads/cluster/

On each machine I have created two folder inside C:\MySQL_Cluster\My_Cluster\  with the following names: conf and data

Data folder from the previous step has been copied from MySQL cluster application data folder.

the MySQL server application has been also configured in the environment variable

MySQL config file has contain the following:

    [ndbd default]
    noofreplicas=2
    datadir=C:\MySQL_Cluster\My_Cluster\data

    [ndbd]
    hostname=host_nod_1
    Nodeid=2

    [ndbd]
    hostname=host_nod_2
    Nodeid=3

    [ndb_mgmd]
    Nodeid=1
    hostname=host_management

    [mysqld]
    Nodeid=101
    hostname=host_management

    [mysqld]
    Nodeid=102
    hostname=host_management

My.cnf contain the following:

My1.cnf:
[mysqld]
ndb-nodeid=101
ndbcluster
datadir=C:\MySQL_Cluster\My_Cluster\data\mysqld_1
Port=1111
ndb-connectstring=host_management:1186

My2.cnf:

    [mysqld]
        ndb-nodeid=101
        ndbcluster
        datadir=C:\MySQL_Cluster\My_Cluster\data\mysqld_1
        Port=2222
        ndb-connectstring=host_management:1186

to start the management node I used the following command:

    C:\mysql\cluster\bin>ndb_mgmd --initial -f C:\MySQL_Cluster\My_Cluster\conf\config.ini --configdir=C:\MySQL_Cluster\My_Cluster\conf

to start data nodes I used the following command on each node:
   C:\mysql\cluster\bin>ndbd -c host_management:1186 --initial

to start API node (MySQL node) I used the following commands:

C:\>MySQL_Cluster\My_Cluster\data\mysqld_1> mysqld --install My1111 --defaults-file="C:\MySQL_Cluster\My_Cluster\conf\my1111.cnf"
***************************************************************************************************************
C:\>MySQL_Cluster\My_Cluster\data\mysqld_2> mysqld --install My2222 --defaults-file="C:\MySQL_Cluster\My_Cluster\conf\my2222.cnf"

when I checked management using the following command it retains the result:

C:\mysql\cluster\bin>ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @host_nod_1  (mysql-5.6.15 ndb-7.3.4, Nodegroup: 0, *)
id=3    @host_nod_2  (mysql-5.6.15 ndb-7.3.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @host_management  (mysql-5.6.15 ndb-7.3.4)

[mysqld(API)]   2 node(s)
id=4 (not connected, accepting connect from host_management)
id=5 (not connected, accepting connect from host_management)

if I connect for example to MySQL1 and create a table:

create table test.t1(id int) ENGINE=NDBCLUSTER;
I've got the following error and warnings:

ERROR 157 (HY000): Could not connect to storage engine
Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire
global schema lock
Warning | 1296 | Got error 4009 'Cluster Failure' from NDB
Warning | 1296 | Got error 4009 'Cluster Failure' from NDB

what did I miss in my configuration?
Edit adding hostname.err
2014-12-24 14:28:25 808 [Warning] NDB : Tables not available after 30 seconds.  Consider increasing --ndb-wait-setup value
2014-12-24 14:52:52 808 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure
2014-12-24 14:55:22 808 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure
2014-12-24 14:55:22 a34 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2014-12-24 14:55:49 808 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure

If you have had a previous version of MySQL server installed, can you run where mysqld in a command prompt to make sure that you're running the MySQL Cluster version of the mysqld binary and not the 'normal' mysqld binary?

Since your management node and SQL nodes both seem to reside on the same machine, you could try changing the config.ini to:

[mysqld]
HostName = 127.0.0.1

instead of

[mysqld]
hostname = host_management

And the SQL Node my.cnf files to:

[mysqld]
ndb-connectstring=127.0.0.1:1186

instead of

[mysqld]
ndb-connectstring=host_management:1186

This might rule out some networking issues.

EDIT: this only works if all hostnames in the config resolve to localhost, meaning you can only use localhost addresses/hostnames in a single server setup.

Also, your management node is showing slots for NodeId's 4 and 5 for the SQL nodes, which doesn't match the definitions in your current config. You could try removing the NodeId configuration from either the SQL Node my.cnf files or the config.ini

You need to perform a rolling restart for the config changes to take effect. As noted by RolandoMySQLDBA, be careful about the --initial

Note: Placing all SQL nodes on the management node machine is not a recommended setup because it creates a single point of failure in your cluster. You could consider placing the SQL nodes on the data node machines.


Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup With MySQL 8.0.20 currently installed on Ubuntu, I am unable to use Percona XtraBackup 2.3.7 due to breaking changes introduced in MySQL 8.0.20.
How can we downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu, which I believe is the latest MySQL version currently supported by XtraBackup? dpkg-reconfigure mysql-apt-config does not allow us to select a minor version.
Must the current data be backup using mysqldump before the downgrade, and restored using the dump after the downgrade?

Xtrabackup 8.0.12 should work with MySQL 8.0.20
Percona xtrabackup 8.0.12 release

Percona XtraBackup 8.0.12 now supports backup and restore processing for all versions of MySQL up to and including 8.0.20; previous versions of Percona XtraBackup will not work with MySQL 8.0.20 and higher.


How to check history of primary node in an Availability Group? How do I see history of failovers of the primary node of a cluster? I'm trying to figure out at what point secondary node started to act as primary.

We are running Windows Server 2012 with SQL Server 2012 and Availability Groups.

Here's my go-to query to pull the events that Aaron mentioned (availability_replica_state_change).  This script also takes the liberty of converting the UTC time to your local time:

declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());

-------------------------------------------------------------------------------
------------------- target event_file path retrieval --------------------------
-------------------------------------------------------------------------------
;with target_data_cte as
(
    select
        target_data =
            convert(xml, target_data)
    from sys.dm_xe_sessions s
    inner join sys.dm_xe_session_targets st
    on s.address = st.event_session_address
    where s.name = 'alwayson_health'
    and st.target_name = 'event_file'
),
full_path_cte as
(
    select
        full_path =
            target_data.value('(EventFileTarget/File/@name)[1]', 'varchar(1024)')
    from target_data_cte
)
select
    @xel_path =
        left(full_path, len(full_path) - charindex('\', reverse(full_path))) +
        '\AlwaysOn_health*.xel'
from full_path_cte;

-------------------------------------------------------------------------------
------------------- replica state change events -------------------------------
-------------------------------------------------------------------------------
;with state_change_data as
(
    select
        object_name,
        event_data =
            convert(xml, event_data)
    from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
)
select
    object_name,
    event_timestamp =
        dateadd(hour, @utc_adjustment, event_data.value('(event/@timestamp)[1]', 'datetime')),
    ag_name =
        event_data.value('(event/data[@name = "availability_group_name"]/value)[1]', 'varchar(64)'),
    previous_state =
        event_data.value('(event/data[@name = "previous_state"]/text)[1]', 'varchar(64)'),
    current_state =
        event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)')
from state_change_data
where object_name = 'availability_replica_state_change'
order by event_timestamp desc;


Replication monitoring refresher for distribution job affecting performance I have a performance problem due to a sql agent job executing dbo.sp_replmonitorrefreshjob.

  The environment is a 2 node Cluster with SQL Server 2005 SP2 and with
  transaction replication. The OS is  Windows Server 2003 R2.

This job runs a stored procedure on distribution database and Application Team experienced total Integration Inbound & Outbound transaction failure due to multiple queries being blocked on the Database. Upon looking deeper into the queries running on the database the following query was identified to have been running for over 9 Hours and when this job(exec dbo.sp_replmonitorrefreshjob) is killed, the issue goes away.
This job was setup to set with 2147 retry attempts and   retry interval 1 so it is pretty much running 24/7.

Some of the code for this Stored Procedure is

ALTER procedure [sys].[sp_replmonitorrefreshjob]
    (
    @iterations tinyint = 0 -- 0 - run continuously, non 0 - run for specified iterations
    ,@profile bit = 0 -- for internal use - DO NOT DOCUMENT (remove this before release)
     )

Is there a way to improve this job and how often does it need to run. If we can get away with this job and if there is another option let me know.
Upgrade is not an option to different version or even service pack is not a question.

Generally, replication monitor doesn't do a very good job.
Have a look at this script by Robert Davis that measures latency:

http://www.sqlsoldier.com/wp/sqlserver/measuringtransactionalreplicationlatencywithouttracertokens


What are the performance implications of polymodels versus normal Bigtable models in Google App Engine? What produces the best performance in regular Google App Engine use, a polymodel or a normal "Bigtable" model?

The polymodel, effectively, creates a column in the parent table called "class" which provides the inheritance tracking. Whereas a normal Bigtable, inherited from a parent class, creates a new and separate data structure, without the ability to query the parent and find all children of all subtyped classes.

There probably is a performance difference, but it's probably pretty small.

Here's what I found (all based on the the Google Docs for Python).

BigTable does not support PolyModel information natively.  Instead, it's implemented using a 'class' property. So, when you try to perform a search, for example, to find a base class, you will be searching on this 'class' property.
All queries that use the PolyModel class have an extra filter applied that filters by the given class (using the 'class' property).
Any indexes created for a PolyModel class, must take into account the extra 'class' column.

Essentially, the PolyModel class takes care of the 'class' property on its own, inserting it into queries and utilizing for other queries.  Other than that, it works identical to standard BigTable usage.

So, the difference is really just adding and maintaining one extra column.

Is there a performance difference?  Yes, probably so.  Every column you add to any database system is going to have a performance impact.  But, is it significant?  Probably not.


Identify Non matching records in Join- Oracle I have a Lookup table like below

Table MASTER

CD SEQ DEF
1 1 A
1 1 B
1 1 C
2 1 A
2 1 B
2 1 C
2 1 D

Table INFO

SID CD SEQ DEF
1000 2 1 A
1000 2 1 B
1000 2 1 C

2000 2 1 B
2000 2 1 C
2000 2 1 D
2000 2 1 E

How should i write a query to say for the SID 1000 using the same CD, and SEQ
D is missing compared to Master table.
and for the SID 2000 using the same CD,SEQ All master DEF records exists and also extra DEF record E exists which is not there in Master table.

Your help is highly appreciated. Thanks

Assuming that the combinations of values in CD, SEQ, DEF in the MASTER table are unique, you could write a cross join which gives you all "valid" combinations for the INFO table - see Query1.  Then you could this query, along with a query that returns all rows from INFO, and the set operators MINUS and UNION ALL to find the rows you are looking for (see Query2).

Query1

select sid, cd, seq, def
from
  ( select unique sid from info )
, ( select cd, seq, def from master )
;

SID CD  SEQ DEF
1000    1   1   A
1000    1   1   B
1000    1   1   C
1000    2   1   A
1000    2   1   B
1000    2   1   C
1000    2   1   D
2000    1   1   A
2000    1   1   B
2000    1   1   C
2000    2   1   A
2000    2   1   B
2000    2   1   C
2000    2   1   D

Query2

select *
from (
  (
    select sid, cd, seq, def, 'missing from info' as comments     -- Query1
    from
      ( select unique sid from info )
    , ( select cd, seq, def from master )
    minus
    select sid, cd, seq, def, 'missing from info' from info       -- INFO + comment
  ) union all (
    select sid, cd, seq, def, 'in info (not in master)' from info -- INFO + comment
    minus
    select sid, cd, seq, def, 'in info (not in master)'           -- Query1
    from
      ( select unique sid from info )
    , ( select cd, seq, def from master )
  )
) where cd = 2 and seq = 1
;

Result

SID   CD  SEQ DEF  COMMENTS
1000  2   1   D    missing from info
2000  2   1   A    missing from info
2000  2   1   E    in info (not in master)

Caveat: In your question, you are saying (among other things) that
"SID 2000 using the same CD,SEQ All master DEF records exist"
although the combination (cd, seq, def) 2-1-A is missing from info.  Query2
will report this combination to be missing.

Dbfiddle here.  Table comparison technique described here.


Index for VARCHAR column has low efficiency I have two tables with relationship one to many.

create table locations
(
  id bigint
     auto_increment
     primary key,
  location_id varchar(128)
     null,
  constraint locations_location_id_uindex
     unique (location_id)
);

create table links
(
  id bigint
     auto_increment
     primary key,
  location_id varchar(128)
     null,
  loc_id bigint
     null,
  constraint links_location_id_fk
     foreign key (loc_id)
     references locations (id)
);

create index links_location_id_index
   on links (location_id);

The locations table has 596_752 records and links table has 1_221_450 ones.  Both tables are very often updated with a new data, there is no a deletion process. The location_id in both tables has size 30 characters.
When a new data appear in links table I have to find all links.location_id that do not exist in locations.location_id. In order to do so I execute the following query:

SELECT links.location_id
FROM links
LEFT JOIN locations l
   ON links.location_id = l.location_id
WHERE l.location_id IS NULL

The log output says:

34 rows retrieved starting from 1 in 1 m 11 s 678 ms (execution: 1 m 11 s 668 ms, fetching: 10 ms)

As you can see, it works too long and I am looking for a way how to improve it, can You help me?

EDIT
The info about MySQL server, it's db.t2.micro AWS instance:

innodb_version  8.0.13
protocol_version    10
slave_type_conversions  ""
tls_version TLSv1,TLSv1.1,TLSv1.2
version 8.0.13
version_comment Source distribution
version_compile_machine x86_64
version_compile_os  Linux
version_compile_zlib    1.2.11

Would you try this and let me know if this improves your performance:

SELECT links.location_id FROM links
WHERE NOT Exists(
select location_id from locations);


Changing metadata changes query output issue Recently stumbled across the following problem: changing metadata changes query output.

Here is how to get it.

CREATE TABLE [dbo].[prod]
(
[ID] [tinyint] NOT NULL,
[values] [tinyint] NOT NULL
)
GO

The following query always returns ‘This is the end’ (error always raised). Also the filter should be some sort of a random generator.

BEGIN TRY
SELECT [id] FROM
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
-- Random generator 1 always returns false
(FLOOR(RAND()*(1))=1)
-- Random generator 2 does not always return false
/*
SUBSTRING( cast(NEWID() as varchar(max)), 1, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F')
*/
-- Random generator 3 does not always return false
/*
SUBSTRING(cast(cast( DATEPART(MILLISECOND,GETDATE()) as char(3)) as varchar(3)), 2, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
*/
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

Now add primary key constraint. Note: no data changes.

ALTER TABLE dbo.prod
ADD CONSTRAINT PK_1 PRIMARY KEY NONCLUSTERED(id);

And now the same query

BEGIN TRY
SELECT [id] FROM
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
(FLOOR(RAND()*(1))=1)
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

never returns an error.

It is understandable that changing metadata may change execution plan but it was unexpected that it changes query’s output. Is this behaviour correct?

Is this behaviour correct?

It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion.

The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans.

As a consequence, query optimization may change the query's output or runtime behaviour, if there is a dependency on the timing or number of evaluations of an expression (or scalar function).

In the question's examples, the runtime behaviour depends on whether the query optimizer identifies each expression as a runtime constant or not. The execution engine may decide to evaluate (and cache) runtime constant expressions before query execution begins.

If the optimizer identifies the expression 1/0 as a runtime constant, the XML form of show plan output will show it labelled as ConstExprxxxx:

Runtime constants like this can be evaluated once before query execution begins, and the result cached, as mentioned previously. Where this occurs, a divide by zero error is returned during this pre-query constant expression evaluation.

Adding the index to the table happens to take a route through query optimization that does not result in 1/0 being labelled a runtime constant (no ConstExprxxxx below):

In this particular case, the presence of a uniqueness constraint means the optimizer decides there is no benefit in caching the result of an expression that will only be evaluated once. The start-up filter means the faulty expression is never evaluated.

These expression evaluation behaviours are not documented or completely determined by the presence of ConstExpr labels in all cases. The answer is not to write code that depends on the timing or number of evaluations of expressions by the execution engine.


Is it possible to sync a Pervasive SQL database to a PostgreSQL database, and vice-versa? I was wondering if it is possible to sync a Pervasive SQL database to a PostgreSQL database.

If I set this up I would need the ability to sync both ways either on command or via a schedule.

Does anyone know if this is even remotely possible?

Possible, yes.

We can't really give you guidance without more info, but you can generally build in logic regarding synchronization and use external apps to synchronize as you need to as a last resort.  PostreSQL also has,a bunch of other tools you might be able to use on that side including untrusted stored procedure languages.

It might not be easy, and it may take a lot of work, but it is possible.


Alternative to cross server query in SSIS I need to do a cross-server query in SSIS and have been having a hard time figuring out how to do it. Here is what I am trying to achieve:
SELECT Name, Email
FROM Server1.dbo.table1
WHERE UniqueID IN (SELECT UniqueID FROM Server2.dbo.table2)

The inner query (which is on a separate database on a separate server) will return a small list of identifiers - no more than a few dozen. Instead of explicitly doing a cross-server query, I would like to do the following:

Fetch the inner query and save it in a variable (var1).
Execute a second query that like this:

SELECT Name, Email
FROM Server1.dbo.table1
WHERE UniqueID IN var1

I think this should be pretty simple to do, but I don't know how to implement it. I am comfortable setting up database connections and doing some configurations in the db source object. The parts I am not sure about is how to save the result set from the first query to a variable, and then how to use that variable in the second query. For example I think I need to pass it as a parameter, but I don't know if I have to format the result set into a list of comma separated values, or if I can just pass the object directly to the second query.
If someone could give me some pointers, or maybe even a couple screenshots to demonstrate how to set this up I would be very appreciative.

you need two datasources and you have to use the lookup transformation to filter the Server1.


Backup on both secondaries or a dedicated backup secondary In my current replica set setup, I have a a primary and 2 secondaries (both a normal secondaries - priority 1 on each, no delay, no hidden). Note that reads and writes are only against the primary.

Backups are occurring at regular intervals using LVM on both secondaries. This seems like overkill to me.

Is it more common practice to have one dedicated backup secondary rather than backup on both secondaries? And set this dedicated backup secondary to have priority 0?

Unless you are actively archiving those LVM snapshots and copying them off the servers, having two live nodes doing snapshotting is not overkill. The alternative would be having a single node that would hold all of your snapshots, meaning you would not be able to restore to a previous state if anything happened to that machine.

One important thing to consider with this setup is that there is always the possibility of the replica set electing one of the secondaries to become a new primary, which means there is a risk of all writes/reads hitting the LVM volume that is being regularly snapshotted. That performance hit might not be severe, but it could impact the clients using the database.

The most common practise I have come across is to dedicate a hidden secondary with priority 0 to do the backups. If your replica set spans multiple geographical locations, it might make sense to have one of those dedicated backup nodes in each location.

By setting priority to 0, you know that the dedicated backup node will never step up and become a primary and by keeping it hidden it will not be advertised to any database clients, so no reads would reach it even if reads from secondaries were allowed.

If you choose to go down that route, make sure to keep the number of votes uneven, for example by making the dedicated backup node have zero votes if you already have three members in the replica set.


SQL Server job failure email without Internet connection We are using a local area network and we send emails through Outlook via Exchange Server.

My question is can I get SQL Server job failure notification emails in Outlook via LAN (without an Internet connection)? And if yes, how?

You just need to enable mail profile and get the email exchange server IP and listening port as well as a valid active directory mail account for setting up email alert notification for jobs as well as alert on multiple things(dis.

Please check below link for setting up mail profile and operator:

https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

https://sqlbackupandftp.com/blog/how-to-set-up-sql-server-email-job-notifications

For setting up notification on alerts:

https://www.sqlshack.com/create-configure-sql-server-agent-alerts/

Hope above helps.


Fulltext search limiting users possibilities We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure.
CREATE PROCEDURE [dbo].[SearchPages]
    @Term varchar(200)
AS
BEGIN
    SELECT pc.SearchData, from PageContent pc
    where contains(pc.SearchData, @Term)
END

Searches with space in them fails, also there is a pletora of sql functions we do not want to expose to the users like
NEAR((bike,control), 10, TRUE)

and the like or binary operators like AND or OR.
So we need to escape the term in some way.
One way that immediately comes in mind is to put an AND between every word.
However, it doesnt feel acceptable.
So is there any better suggestions?

Here are three suggestions.

Suggestion 1. Use plainto_tsquery when you convert the user provided search string to PostgreSQL's tsquery, see the PostgreSQL docs. — I think that plainto_tsquery discards any special magical characters + AND, OR etcetera. (But I'm not completely sure. You'd better verify this so you don't do anything dangerous)  Oops I didn't notice that this was about SQL Server not PostgreSQL.

Suggestion 2. Build a HTML form for advanced search queries, with fields like:

Include all these words: ...
Include any of these words: ...
Exclude all these ...

And so on. Next, construct the otherwise dangerous full text search expression yourself in SQL Server syntax.

Suggestion 3. You could provide a single search text field, and let e.g. "-someword" mean that "someword" should be excluded, and "+anotherword" mean that "anotherword" must be included. Then, you build your own parser for this search field, and convert the resulting "abstract search tree" to something SQL Server understands, and give it to SQL Server.


Selecting large data set on windows and linux I need to parse a large data set, something like:

mysql_query("SELECT * FROM largeTable")
while($r = fetch()) {
 //> Do something
}

I just need 1 row to be in memory at time, because I need to operate on single row each time.

The fact is that on windows at the end I see memory_get_peak_usage() = 500M while on debian it's 4M

Is something wrong with my windows?

If this questions doesn't belong here please move to stackoverflow thanks!

Depends on your client libraries, the way your app handles memory, and what you're doing inside the fetch.

Pulling back one row at a time is a very dangerous design pattern for database servers to handle.  They have to materialize all your results first, then spoon feed them to you one row at a time while you slice & dice results.  You might be holding locks or slowing down the database server.  Instead, pull all of the data into memory on your app and do the slicing and dicing after you've let go of your locks, OR do your slicing and dicing in batches on the database server.  Batch processing is way more efficient than row-by-row processing.  In the DBA community, this is known as ReBAR - Row By Agonizing Row.


Read system_health event_file instead of ring_buffer for deadlocks extended events I'm currently using this disaster to locate recent deadlocks by reading the system_health Extended Events ring buffer.

select top 2000000000
      XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
      --
      -- Extract the <deadlock>...</deadlock> tag from within the event
      -- Todo: Surely there is a better (xml) way to do this.
      --
      substring(convert(varchar(max), XEvent.query('.')),
          -- start
          patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),
          -- end
          patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
          ) AS XdlFile
 from
    (
      select cast (target_data as xml) as TargetData
        from sys.dm_xe_session_targets st with (nolock)
        join sys.dm_xe_sessions s with (nolock)
          on s.address = st.event_session_address
       where [name] = 'system_health'
         and st.target_name = N'ring_buffer'
    ) as Data
 cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
 order by CreationDateUtc desc

It works fine however the events don't seem to hang around very long (like 24 hours?)  I guess this is the "ring buffer" part of it.  Now I stumbled onto a link that was reading the system_health "file" which has similar information:

select event_data = CONVERT(XML, event_data)
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where event_data like '%xml_deadlock%'

Is this file the same as the ring buffer but just stays around longer?  Any disadvantages to using a file?  Does anyone with some XML skills want to convert the top script?

The goal is to copy/paste the XdlFile field into a new file and read it directly into SSMS or Sql Sentry Plan Explorer using "File Open".

Ring Buffer Links:

https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks#tabs

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/

https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/

http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/

http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx

Deadlock error isn't returning the deadlock SQL

File:

https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/

@@version = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)

This seems to work:

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
   from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where object_name = 'xml_deadlock_report'
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc


Concatenate date and varchar I am need to select the date stored in a column transactionDate datatype date and time stored in column transactionTime datatype varchar from a table transactionTable in mysql. My query is like

select concat( CAST( transactionDate as char), transactionTime) as transaction
from transactionTable
where id = 123

There are values in both the columns but the above returns me a null. How can I concat date and varchar columns?

The CONCAT() function returns NULL when one of the arguments is null. You could use COALESCE() to bypass the issue, converting the nulls to empty strings - or to any other constant value, for example '00:00:00' for the time part:

select concat( coalesce(cast(transactionDate as char), ''),
               ' ',
               coalesce(transactionTime, '00:00:00')
             ) as transaction
from transactionTable
where id = 123 ;

In fact, you don't really need an explicit cast(), the cast to char is implicit:

select concat( coalesce(transactionDate, ''),
               ' ',
               coalesce(transactionTime, '00:00:00')
             ) as transaction
from transactionTable
where id = 123 ;


SQL Server Files in Secondary file group updates much slower from linked server My SQL server fact tables are incrementally being updated with reference to a linked server. The queries run routinely on a regular basis and have been executing successfully for a long time.  The execution time for all queries combined is below 5 minutes.  Recently the updates failed because the primary file group reached its limit, even though I set file growth to 'unlimited'.

I thought it would be helpful to move my fact tables to the secondary file in the same file group of my database to allow for growth. I also shrank the database which reduced file sizes (especially the log file) substantially.

I found that my queries were now running extremely slow, some taking up to 2 hours where they had before run in 5 minutes.

I have now moved them back to the database's primary file and completion time is now back to normal.  I do not however understand what went wrong with the distribution of the tables among the database files.

My queries are running because the log file is smaller, but I need to allow for future growth and would like to place some tables in the secondary file if I run out of space again.

The problem was not on my database as I thought, but on the system's tempdb. I added some additional files that point to a different location to the tempdb.  This resolved the space problem.  Thanks to Denis for resolving the performance issue for me.


Should I use UPDATE STATISTICS option? I am not a guru of SQL Server, I'm just studying it, and I have the following question:

Should I use UPDATE STATISTICS option?

I want to know something from your experience is it good practice and it is better to do it on a regular basis or it is better don't even touch it?

on my personal experience the answer is it depends

yes -
When you're not getting acceptable query response times from a properly indexed table, or tables that get a huge number of inserts/updates/deletes

if you have been monitoring query performance, or stored procedures performance, and the estimates do not match the real values
but you need to be careful with a few things when using fullscan

if you keep the AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS both set to ON
and use update stats with percentage
and take good care of your indexes (when you rebuild an index it automatically update the stats for that index)

  The following example updates the statistics for all indexes on the
  SalesOrderDetail table.

USE AdventureWorks2012;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

there is another good article about UPDATE STATISTICS: the Secret IO Explosion

and this one: SQL SERVER STATISTICS: MAINTENANCE AND BEST PRACTICES
where the author mentions examples and situations.