I can't select directly from a table I created My client created a user in his Oracle instance for me to install the objects I have created in my local instance. However, this strange behavior prevents me to deliver my project. I guess it's a security/role related issue, but can't figure out what's missing:

CREATE TABLE "test"(A NUMBER);  -- WORKS FINE

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'test'; -- WORKS FINE, object exists

SELECT * FROM test; -- ORA-00942: table or view does not exist

Any help will be greatly appreciated. I need to point out the exact missing configuration to my client.

Found the answer! Took me less than I expected. The script that created the table quoted the table name, so the only way to find the table in a select statement was quoting the name.

Same problem posted here:

What exactly do quotation marks around the table name do? (Stack Overflow)


Adding array element to JSON column in PostgreSQL We have a table (avl_vehiculo_data_estatica), which has a JSON column (data_vehdatesta) and, we need to update the contents of it by adding an element to actual JSON array that's stored in it:

Actual contents:

[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}]

Desired contents:

[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}, {"iddata":"6","valor":"ACTIVO"}]

So, what has to be done is to add a new JSON array element with the following values:

{"iddata":"6","valor":"ACTIVO"}

We've tried several PostgreSQL JSON related operators and functions, but haven't been able to find the correct mix to achieve such update, so any suggestions might be helpful.

DB Version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

PS: There's a similar question/answer but no exactly the same as our case.

Just use || to concatenate jsonb, if you're using 9.5+.

SELECT lhs::jsonb || rhs::jsonb
FROM ( VALUES
  (
    '[{"iddata":"1","valor":"18"},{"iddata":"2","valor":"10"},{"iddata":"3","valor":"N/A"},{"iddata":"4","valor":"53"},{"iddata":"5","valor":"Agencia Vespucio Oeste"}]',
    '{"iddata":"6","valor":"ACTIVO"}'
  )
) AS t(lhs,rhs);

Outputs:

[{"valor": "18", "iddata": "1"}, {"valor": "10", "iddata": "2"}, {"valor": "N/A", "iddata": "3"}, {"valor": "53", "iddata": "4"}, {"valor": "Agencia Vespucio Oeste", "iddata": "5"}, {"valor": "ACTIVO", "iddata": "6"}]

Note the explicit cast should not be required. I suggest storing your types as JSONB to start out with, unless you have good reason not to.


Multiple instances of mysqld in iotop I see one mysqld process in the output of ps (or top),
mysql     77858  9.9 30.3 13750312 10089460 ?   Sl   Jul09 443:27 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

but at the same time, I see multiple instances in the output of iotop command.
 77883 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  5.27 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
  8537 be/4 mysql       0.00 B/s    3.44 K/s  0.00 %  4.50 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77887 be/4 mysql       0.00 B/s  433.41 K/s  0.00 %  1.79 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77884 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  1.59 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77889 be/4 mysql       0.00 B/s  433.41 K/s  0.00 %  0.93 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77886 be/4 mysql       0.00 B/s  901.22 K/s  0.00 %  0.29 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 77888 be/4 mysql       0.00 B/s  220.15 K/s  0.00 %  0.13 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
126013 be/4 mysql       0.00 B/s    6.88 K/s  0.00 %  0.07 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 48458 be/4 mysql       0.00 B/s   10.32 K/s  0.00 %  0.05 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 78017 be/4 mysql       0.00 B/s    3.44 K/s  0.00 %  0.04 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
 31528 be/4 mysql       0.00 B/s    3.44 K/s  0.00 %  0.04 % mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

I want to know how much that is normal? Which mysql parameter controls that? Should I increase more or decrease? I guess that is related to the connections made to the db.
The system is ubuntu 18.04 and has 32 GB of memory and the pool size is 8 GB.

Normal.
On some operating systems, MySQL shows up as a single process.  (There are probably "threads" inside it.)
On other operating systems, there is one main process, but each connection has its own process.  This is what you are seeing.
A combination of max_connections, "connection pooling", thread_cache_size, user activity, and maybe some other things, lead to how many processes you will see.
Is it an issue?  Generally, no.  If you have configured things poorly, other clues will be more important than the number of processes in ps or top or iotop.  You are very likely to see that most of them are in "Sleep" mode when you do SHOW PROCESSLIST.  If you see lots of non-Sleep connections, the you may be missing some indexes (especially "composite" indexes) or have poorly formulated queries.  The slowlog is a better way to look for those.


MariaDB is AutoIncrementing on update I have a weird issue and i can't find the fault. I have a MariaDB table with 4 rows

[id] [clientid] [lastcheck] [Version]

The id field is set to AutoIncrement. Everything is working fine with my script for adding and updating values

INSERT INTO $dbtable_update
    (clientid, lastcheck, currentversion)
VALUES
    ('$clientid', '$lastcheck', '$currentversion')
ON DUPLICATE KEY UPDATE
    lastcheck = '$lastcheck',
    currentversion = '$currentversion'

But as soon as I update values on an existing entry, the AutoIncement is counting the id row. Can someone help me out, what I am doing wrong?

Note (from Comments):  There is UNIQUE(clientid).

The following assumes that you feel the gaps in the id sequence is a problem. This usually isn't a problem, unless these gaps become enormous and there is a risk that at some point you will exceed the maximum id value.

I'm further going to assume the table is defined similar to this:

CREATE TABLE clients(
  id int unsigned AUTO_INCREMENT,
  clientid varchar(30) UNIQUE NOT NULL,
  lastcheck datetime,
  version varchar(10),
  PRIMARY KEY (id)
);

If you execute an INSERT ... ON DUPLICATE KEY UPDATE statement against this table, then the id column will increase every time since it attempts to INSERT first, then if that fails it does the UPDATE.

To avoid this you can do a SELECT against the table first to see if the clientid exists, then do an INSERT or an UPDATE depending on the result.

However, if you instead had a table like this:

CREATE TABLE clients (
  clientid varchar(30) NOT NULL,
  lastcheck datetime,
  version varchar(10),
  createtime datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (clientid)
);

... then you could use the same INSERT ... ON DUPLICATE KEY statement as before. There would be no id column to worry about, the clientid would be unique (since it's the primary key) and the sequence could be seen from the new createtime column (which might be useful on its own).

You can alter your current table to get the new one with a statement similar to this:

ALTER TABLE clients
  DROP PRIMARY KEY,
  DROP id,
  ADD PRIMARY KEY (clientid),
  ADD COLUMN createtime datetime NOT NULL DEFAULT current_timestamp();

(Note however that all the createtimes for existing records will then be set to the current timestamp when you run the ALTER.)


Sum up counts in subquery I am writing a query to check if I have got duplicates in a certain column.

SELECT COUNT(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol
  HAVING COUNT(*) > 1

That will give me an output like

3
5
6

Which of course is the number of records for each value of thecol where thecol is not unique.

Now how do I only get the sum 14? Should I have the existing query as a subquery?

Does this do what you want?

SELECT SUM(x.records)
FROM
(
    SELECT COUNT(thecol) AS records
    FROM thetable
    WHERE thecol IS NOT NULL
    GROUP BY thecol
    HAVING COUNT(*) > 1
) AS x


Why is LIKE producing different results than my regex? Using Postgres 9.5.  Having some trouble using regular expressions to find matching rows.  I tried

select * FROM my_object_times t where t.name ~ '/\(.*\)\s*$/'
...
(0 rows)

which returned no rows, but this query

select * FROM my_object_times t where t.name like '%(%)';

returns tons of rows.  How am I messing up the regular expression above?

The forward slash is just a regular character in your regular expression, it is not a delimiter like it would be in Perl.  So it will only match strings which have literal forward slashes in them.


Show created date of all binary logs I'm looking for a way to extend the SHOW BINARY LOGS command to show the created date, or to at least show the time range each log is applicable for.

Is there a way to do this within MySQL or am I going to have to read each log individually to check what time period it covers?

MySQL doesn't store metadata about binary logs (well, it does, but only to control current and/or executed commands, not about the binlog themselves).

You can use SHOW BINLOG EVENTS to read the actual binlogs, but that most likely will not be helpful, and will be very slow (it just read the logs). Yes, the only way I can think of is to read the actual logs with mysqlbinlog and check the second line after the log position of the first and last events.

# at 256
#181130  9:28:36 server id 123  end_log_pos 545

If you don't need exact dates, there may be a shortcut for avoid reading the whole log- you can use the mtime (last modified time) of the filesystem metadata to get an aproximation of when it was last written (but not that will not be 100% acurate- flushing may be delayed by the filesystem, and the time the log was written may be different than that of when one the transaction was executed. But it may be a shortcut if you just want to find a particular event before reading just the one you are intereted in.


Is there any way to recover lost table data on sql server without backups? Unfortunately some tables are completely deleted with important data. The database is hosted on azure, but there is no back up for past 1 month as well.

Is there any way I can get the lost data?
The database is azure SQL v 11.

You may actually be able to do a restore.  Grant Fritchey talked about it here.

Basically if you go to the portal and look at the screen for the database there is a restore option.  Once you select it it will give you the oldest restore point available.  You can't overwrite your database but you can restore it to an alternate location.  It looks like you can only do this threw the GUI.  Once you have your alternate location restored you can get your data.

Since you can't do cross database queries you may have to find a third party software or download the data then upload it into the correct DB.

(There was something about being able to do cross database queries if you are in an elastic pool but I've never done that myself.)


MySQL derived query with random order Here I have two tables in MySQL 5.6...

CREATE TABLE Zones (
  ZoneID int,
  ZoneName varchar(10),
  AccountID int,
  ProxyType int
);

CREATE TABLE ProxyServers (
  ProxyID int,
  ProxyType int,
  ProxyIP varchar(10),
  ProxyPort int,
  Enabled tinyint
);

My structure is a lot more complex than the one outlined about but I am trying to keep this as simple as possible to explain.

Basically I want to return 1 row of data consisting of
Zones.ID, Zones.ZoneName, and a ProxyID selected at random joined by ProxyType

SELECT * FROM Zones z
LEFT JOIN ProxyServers ps ON ps.ProxyID = (
      SELECT ProxyID FROM ProxyServers WHERE ProxyType = z.ProxyType ORDER BY RAND() LIMIT 1
    )
ORDER BY ZoneID DESC
LIMIT 1

This is simple enough to do in T-SQL, but can't figure it out in MySQL. Plus what is rather strange, even thought the data set does not change, the results are different ever time the query is run. Sometimes no rows, sometimes 4, sometimes even duplicate rows. I don't get it :/ I think it's the ORDER BY RAND() causing this because when I remove that it's fine.

If you want the query to return just 1 row, a zone with a random proxy (chosen from the associated ones), then it's easy to fix the query:

SELECT z.*, ps.*
FROM Zones AS z
  LEFT JOIN ProxyServers AS ps
  ON ps.ProxyType = z.ProxyType
-- WHERE <some condition to choose from some zones or just one>
ORDER BY RAND()
LIMIT 1 ;

or even better:

SELECT z.*, ps.*
FROM
    ( SELECT *
      FROM Zones
      ORDER BY ZoneID DESC
      LIMIT 1
    ) AS z
  LEFT JOIN ProxyServers AS ps
  ON ps.ProxyType = z.ProxyType
ORDER BY RAND()
LIMIT 1 ;

If you want multiple or all the zones, each one with a randomly chosen proxy server, it's really not trivial to solve, due to the way that MySQL executes subqueries (in ON and WHERE clauses).

In short, the subqueries (the ON condition in your case) is executed for each and every combination of z and ps. Sometimes the randomly chosen ProxyID matches the ps.ProxyID, sometimes it doesn't. Therefore, for each zone, you may get 0 matches, 1, 2 or more, which is exactly what you see in the results.
See previous answers in similar issues:

How can this return more than 1 result?
Problem with MySQL subquery

Writing a query that behaves as expected - and return 1 row for each zone - it really hard because the values of RAND() are not found previously stored in a table but provided during run time.

A work around would be to abuse two other non-standard MySQL "features". First, that subqueries allow ORDER BY without LIMIT, which is normally redundant. Second, that in GROUP BY queries, it is allowed to have expressions in the SELECT list which are not aggregates and are not in the GROUP BY list. This is also against the standard and often leads to unexpected results for poorly written queries.

Combining these two ill-behaving features, we can have a monster:

SELECT g.*
FROM
  ( SELECT z.*,
           ps.ProxyID, ps.ProxyIP, zs.ProxyPort, ps.Enabled
    FROM Zones AS z
      LEFT JOIN ProxyServers AS ps
      ON ps.ProxyType = z.ProxyType
    ORDER BY z.ZoneID, RAND()
  ) AS g
GROUP BY ZoneID ;

It's certainly not guaranteed to work, as recent or future versions of MySQL may or do detect the ill behaviour and may either remove the redundant ORDER BY killing the wanted functionality or may throw an error due to the non-standard GROUP BY clause.


Cannot execute statement: impossible to write to binary log? When I try to create a user story/issue in my my percona xtradb cluster I am getting below error. How to fix it?
Error creating issue:

Could not create workflow instance: root cause: while inserting:
[GenericEntity:OSWorkflowEntry][id,10009][name,Agile Simplified
Workflow for Project FF][state,0]
(SQL Exception while executing the following:INSERT INTO OS_WFENTRY (ID,
NAME,  INITIALIZED, STATE) VALUES (?, ?, ?, ?)
(Cannot execute statement: impossible to write to binary
log since BINLOG_FORMAT = STATEMENT and at least one table uses a
storage engine limited to row-based logging. InnoDB is limited to
row-logging when transaction isolation level is READ COMMITTED or READ
UNCOMMITTED.))

It looks as if your server is misconfigured, or (though somewhat less likely) your code is improperly setting the binlog_format system variable for the current session to STATEMENT (apparently these incorrect modes of operation are, appropriately, more aggressively prevented in 5.6, so I assume you are using something earlier).

# In order for Galera to work correctly binlog format should be ROW
— http://www.percona.com/doc/percona-xtradb-cluster/5.5/manual/bootstrap.html
any binlog_format other than ROW was never supported (also well documented)
— https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1263991

This should be the configuration in all of your nodes in my.cnf.  If your cluster has been running without this, the nodes may not be consistent, though it's hard to imagine it running like this at all, though there were cirumstances in older releases that allowed the server to start with invalid configuration when it should not have done so.


How to add multiple query results into one csv file? By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by Aaron Bertrand.

Here is my actual command which gets recent LOG backups for each database in the server:

EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command =
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg''
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = ''L''
WHERE sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

Now, the problem is I want to send all these query results into email in CSV file but I am not able to paste all these results. I tried creating a new table and storing all query results there, unfortunately I got an error. I need an alternative solution.

I would be glad to hear your ideas.

I started by extracting the SELECT query into a separate SSMS window.  I replaced double-ticks (quotes) with single-ticks and added into dbo.TableForEmailingLogBackups that would allow me to create a real table to hold all of the results.

Note:  I had to change a part of your main query from WHERE sysdb.name = DB_NAME() to AND sysdb.name = DB_NAME() to get the query to work.  This is what that looked like:

SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as 'Size Meg'
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig'
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + ' minutes, '
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 'Yes' ELSE 'No' END AS isOlderThan24Hours
into dbo.TableForEmailingLogBackups
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = 'L'
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;

Then, I used the following code to truncate dbo.TableForEmailingLogBackups, insert the results of the stored procedure call EXEC master.dbo.sp_ineachdb and send the email. Note that you'll need to change the email part to send it to the appropriate email address

truncate table dbo.TableForEmailingLogBackups

insert into dbo.TableForEmailingLogBackups
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command =
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg''
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = ''L''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SqlServerEmailProfile',
    @recipients = 'recipients@.test.com;',
    @subject = 'Transaction log backup report',
    @query = N'set nocount on;select * from dbo.TableForEmailingLogBackups',
    @attach_query_result_as_file = 1,
    @query_result_header= 1,
    @query_attachment_filename = 'test.csv',
    @importance = 'High',
    @query_result_no_padding = 1,
    @query_result_separator = ','

A minute later, I received the email with the attachment.


Need advise on Indexaphobia: High value missing index with High Impact. When index is already there I am trying to understand the true meaning of Missing index for this particular table when I already have index on it and how to address it.
I have a heavily used table and is of approximately 2.5GB. Since its heavily used, a bit hesitant to create index which are not very much required(debatable). This table was heap earlier, recently it is changed to table after changing primary key to clustered from non-clustered.
When I run sp_blitzindex with database name or with this table, it gives result as below:

Mostly it suggests to create index on APT_ID column and include suggests LOGID, RECEIVE_TIME and few other columns. If we look at table definition primary key is defined at LOGID and RECEIVE_TIME. And also we have a NC index on APT_ID column.
Table DDL is as below:
CREATE TABLE [dbo].[TXN_LOG](
    [LOGID] [int] IDENTITY(1,1) NOT NULL,
    [RECEIVE_TIME] [varchar](15) NOT NULL,
    [APT_ID] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    .
    .
    .
    [ERROR_CODE] [varchar](20) NULL,
    [MESSAGE_ID] [varchar](40) NULL,
    [END_POINT_ID] [varchar](50) NULL,
    [NODE_ID] [varchar](40) NULL,
    [TIMEOUT_NETWORK_ID] [int] NULL,
    [TXN_SUMMARY] [numeric](1, 0) NULL,
    [D_FLAG] [numeric](1, 0) NULL,
     CONSTRAINT [PKTXN_LOG] PRIMARY KEY CLUSTERED
    (
        [LOGID] ASC,
        [RECEIVE_TIME] ASC
    ))
GO

NC Index on this table is :
CREATE NONCLUSTERED INDEX [IDX_TXN_LOG_1] ON [dbo].[TXN_LOG]
(
    [APT_ID] ASC
)
INCLUDE([RECEIVE_TIME])
GO

Current usage of index doesn't seem to support NC index as write is higher than read count.

Sincere apologies for attaching images in the question. Appreciate if I can get some expert advice on this. Thanks in advance.

Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) - 12.0.6118.4
(X64)   Dec 12 2019 21:46:15   Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
 (Build 9600: ) (Hypervisor)

To clean up and simplify your question a little:

The index you already have is on [LOGID], [RECEIVE_TIME]
SQL Server is asking for an index on APT_ID, and it wants you to include LOGID, RECEIVE_TIME

That is indeed a different index than you already have.
Let's take the old example of the phone book. The white pages of the phone book were on LAST_NAME, FIRST_NAME, MIDDLE_NAME. That's great if you run a query like this:
SELECT *
FROM PHONE_BOOK
WHERE LAST_NAME = 'OZAR'

But if you don't know someone's last name, and you ask for this:
SELECT *
FROM PHONE_BOOK
WHERE FIRST_NAME = 'BRENT'

Then you're going to be scanning the entire phone book looking for me. The first column in the index is incredibly important. That's why SQL Server is asking for an index on APT_ID, plus other columns included.
So you might ask, "Why isn't an existing index on APT_ID alone enough?" The problem is that in your case, APT_ID isn't selective enough, or people are asking for ranges of APT_IDs. SQL Server's having to do tons of key lookups, and the missing index recommendations are trying to remove those.


Migrating database from oracle 11.2.0.03 to 11.2.0.04. I have been assigned task of migrating our oracle database from 11.2.0.03 to 11.2.0.04 version. Being new to oracle, I have list down all activities I need to carry out to make it successful. I will be creating new database in newer version and creating dump of my present database and export that to newer versions.
I have listed down below activity for same :

Need to take dump data from Existing  databases to new DEV, UAT and PROD databases.(Sequences also needs to be refreshed)

My confusion is, do I need to take care of more scenarios. Since I am not DBA, just a programmer, maybe I am missing some critical points.

Your best bet is to look at the Oracle patching advisor (in Oracle parlance this is a patch, not an upgrade, as you're updating to a new patch set):

Doc ID: 331.1 (My Oracle Support login required)


Looking to find a way to determine replicated objects from subscriber I am trying to create a process to script out items that have been added to subscriber databases in transactional or snapshot replication - sometimes we find users have added a view or proc to a database for example.

I know that from the publisher end you can check to see if an object "is Published" but is there something comparable from the Subscriber end - something like an "is subscribed"?

If we have to script out every single object we can, but it would be much less time consuming if I could filter out all of the objects from the publication.

Poking around I don't see such a thing

On the subscriber database you can run this.

SELECT *
  FROM [SubscribeDBName].[dbo].[MSreplication_objects]


Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken? I am using Postgres 9.3.

I have 2 tables : authn_session and customer. Every authn_session belongs to a customer (and hence has a customer_id as a column which is a FK to customer's id).

Note : These tables contain additional FK references to other table(s) and indexes.

Now, I start 2 different transactions which do the following in the below mentioned order :

Tx :1

BEGIN;
UPDATE customer
    SET customer__created_by =
            (case when customer__created_by = 1 then 5
             else customer__created_by end),
        customer__modified_by =
            (case when customer__modified_by = 1 then 5
             else customer__modified_by end);
UPDATE authn_session
    SET authn_session__created_by =
            (case when authn_session__created_by = 1 then 5
             else authn_session__created_by end),
        authn_session__modified_by =
            (case when authn_session__modified_by = 1 then 5
             else authn_session__modified_by end);

Tx : 2

BEGIN;
DELETE FROM authn_session
    WHERE authn_session__guid IN ('abc3344-ab12-4444-9fdd-f4c5a6f7f210');
DELETE FROM customer
    WHERE customer__id != 0
      AND customer__id = 3
      AND customer__name = 'C2'
      AND customer__domain_name = 'a2.com';

Now, When I look at the locks, using the query mentioned below, one particular row interests me :

locktype | relation     |mode                |tid| vtid| pid |  granted
tuple    | authn_session|AccessExclusiveLock |   | 11/5| 47894| TRUE

Query :

SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'mY-db' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();

Now, according to the postgres documentation AccessExclusiveLocks are granted only in case of :

  Acquired by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

I am not doing any of those things explicitly. Then why is my second process (running the second transaction - I checked it's value in DB) getting an AccessExclusiveLock? And why does the pg_activities_blocked view say that the second process (Delete) is blocked by the first (UPDATE)?

BTW, the result of running these two queries simultaneously (one statement from each query at a time) leads to a deadlock . Am I missing anything that could lead to the second process getting an AccessExclusiveLock on the tuple?

You are quoting from the wrong section of the manual. That passage is from the Table-level Locks section. The lock type you are interested in is specified to be tuple. That means you need to refer to the manual's Row-level Locks section in order to find out when that kind of lock is acquired.

And in the beginning of Row-level Locks it says (original emphasis preserved):

  In addition to table-level locks, there are row-level locks, which can be exclusive or shared locks. An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.

Therefore, the lock could be acquired either by Tx 1 or by Tx 2, because one is updating rows and the other is deleting rows.

Moreover, as pointed out by ypercubeᵀᴹ in a comment, your update statements are not restricted by a filter, thus each running on the entire table. When run in parallel with a delete, even a filtered one, such an update can naturally be expected to cause collisions acquiring exclusive locks.


How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server? Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast.

The problem is, from research, MyISAM is okay with being moved entirely, but InnoDB is not.

I read about this: http://www.mysqlperformanceblog.com/2009/07/31/copying-innodb-tables-between-servers/

The final question is, is there a way to move the entire database at once and make sure that both the InnoDB and MyISAM tables are good.

Any ideas? Thank a lot!

Before you give up on mysqldump, have you ever considered performing parallel mysqldumps?

I wrote a script on how to do this: How can I optimize a mysqldump of a large database?

Going back to your question, you could resort to doing rsync of a live server as long as the target DB server will have the exact same version of MySQL that the source DB server has.

Just run rsync against /var/lib/mysql on a master and copy it to /var/lib/mysql on the target server. Of course, I would run rsync several times. Until the final rsync you should run the FLUSH TABLES WITH READ LOCK. Before copying make sure you hose all binary logs and start from scratch.

If you want the target server to have binary logging, please make you have something like the following in /etc/my.cnf:

[mysqld]
log-bin=mysql-bin

Please try running this script in the event you do not want to shutdown MySQL on the master:

RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);"
sleep 60
SLEEPID=`mysql -u... -p... -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
rsync -r * slaveserver:/var/lib/mysql/.
mysql -u... -p... -e"KILL ${SLEEPID};"

I am a little more conservative in terms of data and index pages being cached while doing this. Personally, I prefer to shutdown mysql after several rsyncs instead of the FLUSH TABLES WITH READ LOCK. Another alternative to this script would be the following script which shuts down mysql for the final rsync:

mysql -u... -p... -e"RESET MASTER;"
RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
service mysql stop
rsync -r * slaveserver:/var/lib/mysql/.
service mysql start

Give it a try !!!

CAVEAT

If you have any InnoDB data, you should set this about 1 hour before attempt to rsync:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will cause InnoDB to page out uncommitted data from the InnoDB Buffer Pool faster. In MySQL 5.5, this is no longer necessary.


Is there a graceful or safe restart for mysql like for apache httpd? I would like to restart mysql gracefully just like httpd where threads are served before restart. I would dislike queries breaking.

Any "requested" shutdown sequence in MySQL (short of kill -9) is somewhat graceful, since transactions in progress (on transactional tables) are rolled back, but here are a couple of ways to make a restart as clean as possible.

Note: if you are shutting down the server for an upgrade, then don't use this process; instead, follow the process detailed in this answer.

Otherwise, if you're just restarting an otherwise-healthy server so that you can change a read-only global variable or something similar, here is a graceful path:

First, enable innodb_fast_shutdown if it isn't already.  This isn't directly related to the gracefulness of the shutdown, but it should bring your server back faster.

mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_fast_shutdown = 1;
Query OK, 0 rows affected (0.01 sec)

Next, instruct the server to close all open tables as soon as no currently-running queries are referencing them.  This step also has nothing to do with the graceful shutdown, but it will make the subsequent step go faster:

mysql> FLUSH LOCAL TABLES;
Query OK, 0 rows affected (41.12 sec)

The FLUSH TABLES statement (with the optional LOCAL keyword, which avoids an unnecessary but otherwise harmless flush of any slaves) will block and your prompt won't return until all of the tables can be closed.  Once each table has been "flushed" (closed), if a query subsequently references the table, it will be automatically reopened, but that's okay.  What we're accomplishing with this step is making less work for the final step:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (13.74 sec)

mysql>

This statement flushes all tables (hence the advantage of getting some of that out of the way less disruptively with the prior step) and acquires a global (server-wide) read-only lock on them.

You can't have a global read lock until every currently running "write" query (i.e., pretty much everything but SELECT) is done.  Issuing the lock request will allow existing queries to finish but won't allow new ones to start.

Your prompt doesn't return until you hold this global lock, so every query that is in progress when you request the lock is able to finish, and you know they're finished, because you get the prompt back.   Any subsequent queries that try to write anything to any table will just stall, changing no data, waiting indefinitely for the lock, until...

you change your mind about the restart and release the lock manually (UNLOCK TABLES;)
you restart the server, or
you accidentally or intentionally disconnect the command line client from this thread (so don't do that).  Keep this window connected and sitting at the mysql prompt:

Resist the temptation to close this.

mysql>

This idle console prompt is what's holding the global lock for you.  Lose this, lose the lock.

From another console window, restart MySQL the way you normally would, either with initscripts (e.g., your local variant of service mysql.server restart) or with mysqladmin shutdown followed by a manual restart.


How join two databases data, thats contains auto increment ids, into a new database My company is evolving a database schema from several databases, one for each customer, to an unique one. We was facing a lot of problems using separated databases. them we decided to start using a centralized data base.

My concern is about the auto incremented ids from the databases.

for example, we have products: 1, 2 and 3 in one database and other products: 1,2 and 3 in other database (with same ids). Those ids are also used as references for another tables.

How can i join those databases in just one and does not mess with the already inserted Ids?

Non trivial.  And, if you have FOREIGN KEYS, it will be worse than that.

Plan A:

Do this to every table in one of the databases that has a potentially conflicting id:

UPDATE tbl SET foo_id = foo_id + 1000

(Pick the number based on avoiding the MAX(foo_id) in the other database.)

Then proceed to merge the matching tables.

Plan B:  This has the advantage of not destroying any tables until you have checked the result.

Create a third database and tables.  Write a bunch of SQL statements to carefully copy all the rows from one db, then the other db, to the new db.  Be sure to copy tables in the correct order.  Hopefully, the lets you create new ids for everything.  (I have not thought through the details.)


Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu) Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ??

Ideally, I'm looking for a simple ERD or UML class diagram in a format that is easy to work with (SVG, PDF or any common UML file format).

So far, I founded only software for MySQL and it's for Windows,
but that's not solution.

Have a look at at (the order of the items implies my personal choices)

DbSchema (not free, but not expensive: http://dbschema.com/)
SchemaSpy (http://schemaspy.sourceforge.net/)
Power*Architect (http://code.google.com/p/power-architect/) although development seems to have stopped)

All of them work with PostgreSQL (and other DBMS)

(Note: I have nothing to do with DbSchema. I just think it's a pretty good tool)


What are the security implications of sp_executesql? By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql.

However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql.

As a temporary workaround, I've been granting EXECUTE rights to sp_executesql on an as-needed basis (through a role in the master database). But this is beginning to be a maintenance pain.

If I grant EXECUTE back to public, are there any repercussions I need to be aware of?

None. sp_executesql executes SQL, under exactly the same context and privileges as the original caller would execute the same SQL. There are many, many, cases when dynamic SQL is unavoidable.


sp_BlitzFirst @AsOf Functionality Not Working? I'm trying to run the sp_BlitzFirst stored procedure (using the 7/15/2016 version) with the @AsOf parameter, and I'm getting this error:

EXEC sp_BlitzFirst @AsOf = '2015-02-23 18:45', @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults'

Setting up configuration variables
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

The SQL server version is 11.0.6020.0 (X64).

I used the example query from the documentation page and got this. It's choking on the triple ''' when trying to cast @AsOf to a varchar.

I've worked around this for now, but the documentation said to post here about errors thrown!

Unfortunately, compatibility level 90 (SQL Server 2005) is not supported.

From the known issues list at: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

"Only Microsoft-supported versions of SQL Server are supported here - sorry, 2005 and 2000. Some of these may work some of the time on 2005, but no promises, and don't file a support issue when they fail. (For example, we know the output tables won't work on SQL 2005 because one of the output fields is a DATETIMEOFFSET datatype, which isn't available in 2005.)"


Which timeout(s) are causing my linked server query to time out? We have two databases on linked SQL Servers that are partially synchronized. Occasionally, one side goes down for an upgrade, then the other goes down for an upgrade, and then we re-synchronize.

Upgrades can take upwards of two days. Re-synchronizing two days worth of data is a large operation, so we broke it into batches. During testing, we completed twenty batches successfully before this message appeared in the query window in SSMS:

  OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" returned message "Query timeout expired".

We restored, tested it again, and the operation was successful. We tested on a slower pair of machines, and it timed out on the first batch. We concluded that environmental noise (other processes on the server) and random variance is causing our query to occasionally take too long.

Here is the query that is timing out:

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    ) AS remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source
ON source.ID = target.ID
-- We need to pull this record
WHEN NOT MATCHED BY target THEN
    INSERT (
        Name, Value, Synced
    ) VALUES (
        source.Name, source.Value, 1
    )
-- We already have this record
WHEN MATCHED THEN
    UPDATE SET target.Synced = 1
-- There is an OUTPUT clause here that collects the remote IDs of all of
-- the rows that were synced and stores them in a table. We update the
-- remote table before we do the next batch, so that we don't sync the same
-- 500000 rows over and over again.
;

The query above represents one batch. It replaces batchablequery below. We separated it out like this because we sync more than one table.

DECLARE @count INT = 500000
WHILE @count > 0
BEGIN
    batchablequery
    SET @count = @@ROWCOUNT

    INSERT INTO MyLogTable
    VALUES(GETDATE(), 'We finished a batch of size ' + CONVERT(varchar(20), @count))

    CHECKPOINT
END

We could fix the query timeout in one of three ways:

Improve query performance

It spends most of its time waiting with a PAGEIOLATCH_SH

Decrease or throttle batch size
Increase timeout

Before we choose a solution, we want to fully understand which timeouts are affecting us. We are confused by which ones are applicable to our query. Here are our current settings, and some notes we took from MSDN:

Server Remote Query Timeout

Ours is set to 600

Linked Server Connection Timeout

Ours is set 0, so sp_configure Remote Query Timeout Option is used instead.

Linked Server Query Timeout

Ours is set to 0, so sp_configure Query Wait option is used instead.

Query Wait

Ours is set to -1, so 25 times the estimated query cost is used.
The estimated query cost doesn't have a unit of measure on it...

Which setting or settings are the cause of our timeout?

You could try putting the remoteData into a temp table before doing the merge. a lot of your time will be taken up with comparisons of data not the actual retrieval so if you run the

SELECT remoteData.Name AS Name, remoteData.Value AS Value
into #remoteData
    FROM OPENQUERY (
        MyLinkedServer,'
        SELECT TOP 500000 Name, Value
        FROM MyDatabase.dbo.MyTable
        WHERE Synced = 0'
    )

then in the merge statement

MERGE INTO dbo.MyTable AS target
USING (
    SELECT localData.ID, remoteData.Name AS Name, remoteData.Value AS Value
    FROM #remoteData as remoteData
    JOIN dbo.MyTable AS localData ON localData.Name = remoteData.Name
    WHERE localData.Synced = 0
) AS source

It may help (can't promise)


100k bit column in PostgreSQL What is the best way to be store, query & index a column containing around 100k bits?
I am attempting to do it creating a table like so:
CREATE TABLE public.search_bf
(
    id serial,
    bf integer ARRAY[3125]
)
WITH (
    OIDS = FALSE
);

So column bf would have 3125 32bit integers which would give me the neccessary bits. I'm now trying to find a way of indexing and querying this.
I would like to use intarray but this doesn't work for bits.
I could use intarray if I just created a column integer ARRAY[100000] but this would be really inefficent in terms of storage.

The solution I'm moving ahead with is to have an smallint[] which esentially gives me a total of 65k unique values / bits. I will then store the index of the bits that are 'on'. This won't be very efficent in terms of storage if densely populated. I could use int4 for higher indexes but this would consume even more space.
I can query it for off bits using @> operator.
I should be able to index this with a GIN index.


Backed up MySql folder… upgraded to newer version and cant seem to recover data I copied the entire MySql folder mysql-8.0.13-macos10.14-x86_64 and installed a newer version mysql-8.0.17-macos10.14-x86_64.

I should have researched but thought the installer would handle moving the data over (I only backed up as a precaution).

After installing my older MySQL folder only has 'data' and 'keyring' and the new the obvious. My backed up folder seems to have everything the old MySQL did before the new install.

Is there a way to get the older data?

I've tried copying the data folder to the new install folder, data & keyring.

I'm on Mac. upgraded from MySQL 8.0.13 to 8.0.17

Old MySQL Directory:
/usr/local/mysql-8.0.13-macos10.14-x86_64/

Now after installing the update:
/usr/local/mysql-8.0.17-macos10.14-x86_64/

At this point, it is probably simplest to use mysqldump to dump the old data and load it into the new path.


Should Fileshare be part of Cluster resource in multisubnet AAG? We are planning to expand an existing AAG setup which is a 2 node setup in DC and will be adding a 3rd node in San Jose(different subnet). The current setup is configured with Node and File Share majority. I was going through a Microsoft Whitepaper for AAG setups and want to have a clear idea about this comment which was mentioned

"Because the third node is in a separate data center and no longer has a vote, you should change this quorum model to Node and File Share Majority. Create a file share in the primary data center on a server node that will not be participating in the WSFC"

The current file share witness can actually be seen in the cluster core resources. Does the above sentence now mean that the file share shouldn't be  a part of it? Please clarify

I'd need to see the article where that quote is from to understand the context, but the file share will still be part of the cluster and will still be configured as the witness.

Votes: Node1=Yes, Node2=Yes, Node3=No, File share=Yes

If you ever failover to the other data center, you'll need to create a quorum there. Maybe you'd spin up another node and file share when you failover. Those would be created in the San Jose data center. Once those are in place, add the VM to the cluster and then reconfigure the cluster to use the new file share in the San Jose DC for the witness. After failover, disable the votes for the DC data center. You don't want those resources deciding if you have quorum when there's a network glitch between the two data centers.


ANSI NULL OFF By Default in database option I have a weird behaviour here when creating database in sql server. The default ANSI values is set to false when creating database.

ALTER DATABASE [NewDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NewDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NewDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NewDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NewDB] SET ARITHABORT OFF
GO

However, when i execute the query after database is created in query editor

SELECT * FROM [dbo].[TableName] WHERE [ColumnName] = NULL

It is obvious that the actual setting is ANSI_NULLS ON since no row returns.

==========================================================================

My question is:

1) Why is this so? The database option is ANSI_NULLS OFF but query result actually show it is ANSI_NULLS ON. This is also applicable to all other ANSI settings, where it actually resembles ANSI_NULLS ON while the database option is set to OFF.

2) Why is the default database option is ANSI_NULLS OFF instead of ANSI_NULLS ON. (Similar applicable to other ANSI options as well, why is it default to OFF since ON is the one being recommended?)

I have tested this from SQL Server 2012 up to 2017 and all version results in same behaviour. I am quite confused with this.

Some of the database settings are useless. As you have noticed.

The client API (ODBC etc) will override them. And, the client app (SSMS for instance) can further override whatever was overridden by the API.

So why are those database settings still there? Because stone-age API didn't do such override. I'm thinking DbLibrary here, but this might apply to other very old APIs as well.

Why do they have such weird database option values as default? Because of backwards compatibility. I.e., so you can create a database "as default" and use a stone-age API "as default" and get the same behaviour as you did back in 1989.


How do I merge similar records with different validity dates? The table I am working on has three components:

An ID column (primary key in another table)
Some data columns
Date valid from/to columns.

Values:

ID   Data From        To
1    a    2015-01-01  2015-01-05
1    a    2015-01-06  2015-01-10
1    b    2015-01-11  2015-01-15
1    a    2015-01-16  2015-01-20
2    c    2015-01-01  2015-01-05
2    c    2015-01-06  2015-01-10

The table is updated by taking "snapshots" of another data source at some intervals and assigning validity dates to records. The problem is that these snapshots create duplicate entries for records (with different validity dates) that were not changed at all during that interval.

I want to reduce the size of the table by looking for rows with consecutive dates and by merging them and assigning them a single validity period. For example:

ID   Data From        To
1    a    2015-01-01  2015-01-10
1    b    2015-01-11  2015-01-15
1    a    2015-01-16  2015-01-20
2    c    2015-01-01  2015-01-10

The logic I currently have is:

Select and sort all rows by ID, data fields, and 'valid from' fields (so they are in groups of consecutive rows).
Use a cursor to compare adjacent rows for similarity.
If they are same, then merge rows and change validity period to include both rows.

I understand that cursors are very inefficient (I have a large dataset), so I am looking for other approaches.

If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from] and the maximum [to] per island.

There is an established method of solving this using two ROW_NUMBER calls:

WITH islands AS
(
  SELECT
    id,
    data,
    [from],
    [to],
    island = ROW_NUMBER() OVER (PARTITION BY id       ORDER BY [from])
           - ROW_NUMBER() OVER (PARTITION BY id, data ORDER BY [from])
  FROM
    #mergeTest
)
SELECT
  id,
  data,
  [from] = MIN([from]),
  [to]   = MAX([to])
FROM
  islands
GROUP BY
  id,
  data,
  island
;

This query will work in as low version as SQL Server 2005.


Error 71501, 71561 while encrypting columns using always encrypted feature of SQL Server 2016 1) I am trying to encrypt some varchar columns which are not identity / primary using the always encrypted feature of SQL Server 2016,
but getting an error code 71501,71561 for unresolved and / or ambiguous reference.

How do I resolve this ?

2) Is there a way to nominate a column as primary key for all tables at once ,which do not have a Primary Key .

Thanks

Regarding your first question, which version of SSMS, are you using? If it's older than 17.0, I recommend trying the newest version (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms).

Based on your description, I suspect you are using SSMS 16.x. The likely root cause of this problem is that the objects in your database have external dependencies (on objects in other databases) or have broken dependencies. In SSMS 16.x, the Always Encrypted wizard does a comprehensive validation of all dependencies in the entire database, including objects not dependent on that tables that you are trying to encrypt. We have fixed this problem in SSMS 17, by limiting the validation only to the tables containing the columns you are encrypting and the wizard does not error out anymore when encountering a validation error. Instead, it completes encryption and produces a warning, allowing you to investigate/address the issue later.

Regarding your second question - you need to designate a primary key column for each table.


How to move a SQL Agent Job to another Server? I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File Manipulation.
Not sure if I right click on it and choose export, would that affect the production server if it wont be working in the new dev server? or how I can import just a copy of it to the other new development SQL server 2008?

Thanks for any advice or direction.

If you're talking about moving SQL Server Agent Job, I suggest you use the option "Script Job as -> Create" and than copy and paste the code on the new server. Once you have the code on the new server you can edit it (or execute it and than edit the job through Job Properties window) so it could work in a new enviroment.


How can I dump the schema of a SQL server database on Linux? UPDATE:
My goal is to get a set of SQL commands that can b executed and would recreate the database that I made including all the resources such as tables, indexes, trigger functions, privilege grants etc - same as what the MySQL and POstgres commands below do.  I'm working on an open source project and I want to be able to give a sql file to the end user so all they ned to do is run it to get their database correctly set up.
On MySQL:
mysqldump --no-data somedatabasename dump_schema_mysql.out

On Postgres:
pg_dump -s somedatabasename > dump_schema_postgres.out

I can't seem to find an equivalent for SQL server on Linux, though surely such a common task must be easily done?
I have the sqlcmd utility running ok:
ubuntu@ubuntu:~$ sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 17.6.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided]
  [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-D Dsn flag, indicate -S is Dsn]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-g enable column encryption]
  [-G use Azure Active Directory for authentication]
  [-? show syntax summary]
ubuntu@ubuntu:~$ sqlcmd -?

Check out mssql-scripter. It's cross-platform and created by Microsoft. It has a bunch of options to control how the scripting happens.


InnoDB tables in an 8GB RAM server: Are these settings optimized? I've been reading about how to optimize my database (MySQL 5.6.51) settings, and I've found an article suggesting to increase the key_buffer as much as possible.
Here's my my.cnf file:
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
performance-schema=0
max_allowed_packet=32M
open_files_limit=10000
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_file_per_table=1
key_buffer_size = 32M
default-storage-engine=MyISAM

query_cache_type=2
query_cache_size=128M
query_cache_limit=2M

Should I keep these settings as they are? I'm administrating my own website database, but I'm not a proper Database Admin.
Thanks for your help.
EDIT: Sorry, by mistake I indicated my MySQL version as being 7.0.33, but that is my PHP version! (very silly mistake, I know). I corrected that in the question.

Take a look at the date on the article you linked to. It's from 2006, which is fifteen years ago as we write this. The latest version of MySQL at that time was 5.0.30.
The key_buffer_size configuration option only applies to MyISAM tables. It's the size of RAM allocated for caching MyISAM indexes. InnoDB tables do not use the key buffer.
I notice one of your settings is default-storage-engine=MyISAM which suggests you have at least some MyISAM tables, unless you have specified each of your tables is InnoDB. You can check what engine is used for each of your tables:
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

The default storage engine in MySQL was changed to InnoDB in MySQL 5.5, circa 2010. There are many reasons to prefer to use InnoDB over MyISAM in modern versions of MySQL. You should use InnoDB unless you have a specific reason not to, and have proven with measurements that you need to use MyISAM.
You asked if your settings are optimal for a server with 8GB RAM. We don't have enough information about your usage of the database to know that.
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

Why do you set this? Do you ever use the slow query log for diagnosing performance problems? Do you rotate the slow.log periodically so it doesn't continue to fill up your storage (IMO, no log should be allowed to exist on a production server unless you have automated log rotation).
performance-schema=0

Why do you disable the performance schema? In some old versions of MySQL, it was slow, but that has been fixed and there's no reason to disable it. I do not disable it in databases I operate, even those that are very sensitive to performance.
max_allowed_packet=32M

Is this large enough? It means you cannot have any SQL query longer than 32MB, or a result set of a query that returns a row larger than 32MB. Do you use BLOB/TEXT columns?
open_files_limit=10000

Why do you set this? Did you measure anything that suggested the default open_files_limit is not enough? How many tables do you have? What is your typical number of Threads_connected? Have you read https://dev.mysql.com/doc/refman/8.0/en/table-cache.html
innodb_buffer_pool_size = 512M

This is half of one GB, but you said you have a server with 8GB RAM. Why not use more? On the other hand, 512MB might be more than enough, if your data is small. You haven't said anything about how much data you store in InnoDB tables.
Note that the innodb config options have no effect on MyISAM tables, so if you do still use MyISAM tables, then there's no benefit to tuning innodb options.
innodb_log_file_size = 128M

Is this enough? The appropriate log file size is determined mostly by your rate of write traffic. Also it must be at least 10x the size of your largest BLOB/TEXT data. Do you have any large BLOB/TEXT columns?
innodb_file_per_table=1

This is the default in current versions of MySQL. So why specify it in the options file if it's the default anyway?
key_buffer_size = 32M

There's no point tuning key_buffer_size if you use InnoDB tables. There are still a few MyISAM tables in the mysql system schema up to version 8.0, but these tables are small and cached in other ways. The key buffer is probably not important to tune.
default-storage-engine=MyISAM

I recommend against setting the default storage engine to MyISAM. It's slower than InnoDB in most benchmarks, and doesn't support ACID properties of an RDBMS.
query_cache_type=2
query_cache_size=128M
query_cache_limit=2M

The query cache is deprecated, as it has been discovered it causes a bottleneck of performance. I recommend setting query_cache_type=0 and query_cache_size=0. This is not necessary in MySQL 8.0, because the query cache has been removed altogether.


Executing query against Azure SQL db using Linked server takes hours I have a linked server to Azure SQL database and I am trying to run the below SQL query which takes hours to run and at one point, I then have to cancel the execution. I have researched everywhere and have tried everything but no result. This query takes about 3-4 minutes when I run it on local, the reason being the DealerMediaRates table has about 7.8 million records. I could probably optimize this query, maybe that probably solves the Azure issue but I find it hard to believe that this query would run for hours when I use Azure linked server that I eventually have to stop. Our SQL server tier in Azure is S2, we even tried switching it to S3 but no luck.

UPDATE  dmr
SET     Quantity = t.[One50K],
        LastModifierRoutineName = @LastUpdatedRoutineName,
        LastModifiedUtc = GETDATE()
FROM    AZUREDB.dbname.dbo.DealerMediaRates dmr
INNER JOIN AZUREDB.dbname.dbo.DealerMedias dm ON dm.DealerMediaId = dmr.DealerMediaId
INNER JOIN AZUREDB.dbname.dbo.Dealers d ON d.DealerId = dm.DealerId
INNER JOIN AZUREDB.dbname.dbo.DealerGroups dg ON dg.DealerGroupId = d.DealerGroupId
INNER JOIN AZUREDB.dbname.dbo.DealerMetadataValues dmdv ON dmdv.DealerId = d.DealerId
INNER JOIN AZUREDB.dbname.dbo.MetadataFields mf ON mf.MetadataFieldId = dmdv.MetadataFieldId AND mf.ClientId = @MetisClientId AND mf.MetadataCode = 'Real Green #'
INNER JOIN SAExMark.dbo.RealGreenZipCodeData t ON t.ParentCompanyID = dmdv.[Value] AND t.DealerID = dm.SubDescription AND t.Zip = dm.[Description] AND dm.MediaCode = 'DM' AND t.[SegmentCode] = 'RA'
WHERE   dmr.MediaRateClassificationId = 13
AND     dmr.MediaSegmentId = 5

THIS TAKES ABOUT 7 MINS to RUN

UPDATE  dm
SET     IsDeleted = 1,
        LastModifierRoutineName = @LastUpdatedRoutineName,
        LastModifiedUtc = GETDATE()
FROM    AZUREDB.dbname.dbo.DealerMedias dm
INNER JOIN AZUREDB.dbname.dbo.Dealers d ON d.DealerId = dm.DealerId
INNER JOIN AZUREDB.dbname.dbo.DealerGroups dg ON dg.DealerGroupId = d.DealerGroupId AND dg.ClientId = @MetisClientId
INNER JOIN AZUREDB.dbname.dbo.DealerMetadataValues dmdv ON dmdv.DealerId = d.DealerId
INNER JOIN AZUREDB.dbname.dbo.MetadataFields mf ON mf.MetadataFieldId = dmdv.MetadataFieldId AND mf.ClientId = @MetisClientId AND mf.MetadataCode = 'Real Green #'
LEFT OUTER JOIN SAExMark.dbo.RealGreenZipCodeData t ON t.ParentCompanyID = dmdv.[Value] AND t.DealerID = dm.SubDescription AND t.Zip = dm.[Description] AND dm.MediaCode = 'DM'
WHERE   t.DealerID IS NULL
AND     dm.MediaCode = 'DM'
AND     d.Name NOT LIKE '%Test%'

]3

I think you're running into a key limitation of distributed queries (e.g. four part notation).  As per this MS blog post:

  data for remote queries are collected from the remote servers,
  scrubbed locally, combined together and presented to end user as single record set

Basically the source tables in their entirety are trying to get pulled over the linked server and all the joins and filtering are happening locally.  When you're not working with millions of records this is often transparent, but when you include large tables you can immediately feel the pain.

In your situation, I suggest you push a copy of the SAExMark.dbo.RealGreenZipCodeData table up to your Azure DB and then run this UPDATE statement from a connection made directly to the AzureDB.  If you need to initiate the process from a different server, stuff the update into a Stored Procedure in the Azure DB and then execute the SP remotely.

Alternatively, if you don't want/can't push that table to Azure, you could convert the query to utilize OPENQUERY for all the remote join operations.  This will quickly get ugly, but it's possible.  OPENQUERY will force execution of whatever query you pass to it remotely, therefore allowing the join logic against that large table to be done remotely so only whatever records are returned from that query travel back over the linked server.  These would then be joined against the local copy of the SAExMark.dbo.RealGreenZipCodeData to identify what records in AZUREDB.dbname.dbo.DealerMedias would need to be updated.  I really don't like this approach just to update a table on a remote database, but it's an option.


How to find unused logins in SQL Server? I have a couple of databases on my server, each with its own users. Now I need to find and delete all those logins, which are not mapped to any user in any database. But I've also seen that it is possible that logins are not mapped to any user but they are able to do some operations because they have roles like sysadmin. So in this case those logins are in use. I want to know how can I find really not useful logins which are just left orphaned without any use?

Thanks

I have something really ugly I made years ago. I just need to implement a dynamic SQL to automatically execute this "select". well here it goes:

First create this report table:

CREATE TABLE #REPORT ( [DATABASE] VARCHAR (MAX),[USER] VARCHAR (MAX))

Then execute this query and run it:

select 'use ['+name+'] '+char(13)+
'insert into #report
            select db_name(db_id()) collate sql_latin1_general_cp1_ci_as as [database] ,
                   name collate sql_latin1_general_cp1_ci_as as [user]
    from sys.database_principals
        where type in (''s'',''u'')
            and name not in
                (''dbo'',''guest'',''information_schema'',''sys'')'+char(13)+char(13)
from sys.databases
    where name not in ('master','model','msdb','tempdb')

Then you can have:

--login without user
select name from sys.server_principals
    where name not in
        (select [user] from #report)
        order by 1

AND

--user without login
select [database], [user] collate latin1_general_ci_as
    from #report
        where [user] not in
            ( select name collate latin1_general_ci_as
                from sys.server_principals)
order by 1


Postgres: Difference between Tuples inserted and Live tuples Many apologies in advance, but I was not able to find this out myself:

What is the difference between live tuples and tuples inserted in postgres statistics?

There's this post, but it doesn't answer the question: pg_stat_get_live_tuples are Representing the number of live [...] rows (tuples) in the table. But what means "live"?

The Postgres documentation doesn't provide answers either:

pg_stat_get_live_tuples(oid) Number of live rows in table
pg_stat_get_tuples_inserted(oid) Number of rows inserted into table

Why do my stats show 213,324,422 inserted tuples, but only 124,510,280 live tuples? (3,087,919 dead tuples)

The PG Admin documentation is also unclear about this. It further says that Tuples Inserted   displays the number of tuples inserted into the database within the last week.

I would like to know the total number of existing entries in my database. Is this live rows or inserted rows?

This nomenclature all comes down to knowing about MVCC, or Multi-Version Concurrency Control, which is how PostgreSQL handles transactions and concurrency.

When you update or delete a row, doesn't actually remove the row. When you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, the database simply marks the row as unavailable for future transactions, and for UPDATE, under the hood it's a combined INSERT then DELETE, where the previous version of the row is marked unavailable. These new versions of rows are generally referred to as the "live" rows, and the older versions are referred to as "dead" rows.

The statistics that you are looking at, where it shows 213,324,422 inserted tuples are the number of new data inserts that has been done on that table. The 124,510,280 live tuples are the number of tuples that are up to date in the table and available to be read or modified in transactions. The 3,087,919 dead tuples are the number of tuples that have been changed and are unavailable to be used in future transactions. The autovacuum daemon, or a manual vacuum will eventually come along and mark the space of those "dead" tuples available for future use, which means that new INSERTS can overwrite the data in them.

Side note, since these are just statistics that are gathered by the statistics collector, they aren't going to be exact, but they're good enough to give you an idea of how active the table is, and how much bloat there is (live vs dead tuples), and how well the autovacuum daemon is keeping up with your workload.

Hopefully this makes it clearer. You can read more about this in detail in the PostgreSQL Manual in the chapter on Concurrency Control


Postgresql installation failed if we provide non-english password(Chinese or French) I am using Postgresql postgresql-9.6.2-2-windows-x64.exe installation on a French Windows OS.

I specified Télécharger as the password during postgres installation on the French OS.

(Note: I got the same error when I used Chinese characters.)

Locale used: C

I have tried even french locale but the result was same.

Please help.

Installation Logs:

[12:09:33] Write the base directory to the ini file...
[12:09:33] Write the version number to the ini file...
Initialisation du cluster de bases de données (cela peut prendre un certain temps)...
Executing cscript //NoLogo "C:\Program Files\PostgreSQL\9.6/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Program Files\PostgreSQL\9.6" "C:\Program Files\PostgreSQL\9.6\data" 5432 "C" 0
Script exit code: 1

Script output:
 WScript.Shell Initialized...
Scripting.FileSystemObject initialized...

Called CreateDirectory(C:\Program Files\PostgreSQL\9.6\data)...
Called CreateDirectory(C:\Program Files\PostgreSQL\9.6)...
WScript.Network initialized...
strParentOfDataDirC:\Program Files\PostgreSQL\9.6
logged in userFrench-win7\Admin
Called AclCheck(C:\Program Files\PostgreSQL\9.6\data)
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Executing icacls to ensure the French-win7\Admin account can read the path C:\Program Files\PostgreSQL\9.6\data
    Executing batch file 'radABEF2.bat'...
    fichier trait'˜: C:\Program Files\PostgreSQL\9.6\data
1 fichiers correctement trait's˜; 'chec du traitement de 0 fichiers

Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can write to the data directory (using icacls) to  French-win7\Admin:
    Executing batch file 'radABEF2.bat'...
    fichier trait'˜: C:\Program Files\PostgreSQL\9.6\data
1 fichiers correctement trait's˜; 'chec du traitement de 0 fichiers

    Executing batch file 'radABEF2.bat'...
    Les fichiers de ce cluster appartiendront … l'utilisateur ® Admin ¯.
Le processus serveur doit ‚galement lui appartenir.

L'instance sera initialis‚e avec la locale ® C ¯.
La configuration de la recherche plein texte a ‚t‚ initialis‚e … ® english ¯.

Les sommes de contr“les des pages de donn‚es sont d‚sactiv‚es.

correction des droits sur le r‚pertoire existant C:/Program Files/PostgreSQL/9.6/data... ok
cr‚ation des sous-r‚pertoires... ok
s‚lection de la valeur par d‚faut de max_connections... 100
s‚lection de la valeur par d‚faut pour shared_buffers... 128MB
s‚lection de l'impl‚mentation de la m‚moire partag‚e dynamique...windows
cr‚ation des fichiers de configuration... ok
lancement du script bootstrap...ok
ex‚cution de l'initialisation aprŠs bootstrap...FATAL:  invalid byte sequence for encoding "UTF8": 0xe9 0x6c 0xe9
le processus fils a quitt‚ avec le code de sortie 1
initdb : suppression du contenu du r‚pertoire des donn‚es ® C:/Program Files/PostgreSQL/9.6/data ¯

Called Die(Failed to initialise the database cluster with initdb)...
Failed to initialise the database cluster with initdb

Script stderr:
 Program ended with an error exit code

Erreur dans l'exécution de cscript //NoLogo "C:\Program Files\PostgreSQL\9.6/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Program Files\PostgreSQL\9.6" "C:\Program Files\PostgreSQL\9.6\data" 5432 "C" 0 : Program ended with an error exit code
Un problème est survenu lors de l'exécution de l'étape de post-installation. L'installation peut avoir échoué
 L'initialisation du cluster de bases de données a échoué.
Création du Désinstalleur
Création du désinstalleur 25%
Création du désinstalleur 50%
Création du désinstalleur 75%
Création du désinstalleur 100%
Installation terminée
Log finished 09/12/2017 at 12:14:05

I'm not a Postgresql guy, but from this blog by Jasper Peilee:

  If you need Asian-language support, don’t leave(sp) the locale as “default”. Simply choose any other locales, e.g. “C” in the combo list.

  After the installation, supposing you need simplified Chinese support, change the configuration using command:

  Windows: initdb -D data –locale=chinese-simplified_china.936 -E UTF-8
  Unix/Linux: –locale=zh_CN.UTF-8

Hopefully that points you in the proper direction.  If not, please update your question with some more information such as the error you receive, the installer you're using, the installer options, etc.


How to make a remote mysqldump? I know that's it's possible to do a remote mysqldump if I specify --host but is it possible to create the file in the remote server as well?

What about piping a gzipped mysql dump over ssh.


Should future time be stored in UTC or local time? Clearly in most cases time should be store in UTC. And in all cases a datetime that records a past event should be stored in UTC. We do this because civil time keeps changing and an event happened at a moment that's detached from whatever the local government thinks the time is.

But when times are detached from dates, when they refer to wall-clock times, I contend that they should be stored in wall-clock time.

Let's say I have a system that has to perform an event at 3pm in a particular time zone, I contend that I'm best to store the time as 3pm. This is even more true when the event is recurring and should always occur at 3pm -- whether daylight savings is in effect or not. And if it should occur for multiple users in various time zones at 3pm-local, storing anything in UTC just seems silly.

To me, this feels obvious. But I'm looking for a standard, a specification, or at least a blog post from a trusted source that can back me up.

In general I agree with your appraisal. When a time is going to be used under any of the following circumstances using local time only is appropriate:

There is some other bit of context that makes the time zone obvious or irrelevant.

The time is going to only be used in reference to a local event like a concert. Since I know the concert is taking place in venue X it would be weird if the time wasn't in the time zone for venue X. Similarly it would be weird if my doctor's appointment was made based on a different time zone than the location of the appointment.
I shouldn't have to set a time zone to get the alarm on my phone to go off at 6 o'clock in the morning. I should be able to say 6 am, and the phone should wake me up everyday at 6 am local time. Note I'm just talking about a basic alarm like you would have with a clock radio. I'm not talking about scheduling a meeting/appointment with people in different time zones.

For some reason the fact that X occurs on a regular interval is more important that X occurs at a specific time.

Something needs to happen once a day and it doesn't really matter when it happens. Usually you would like to do some scheduled task during non-peak hours, but it is conceivable that this isn't always important.

Assuming the above is true then I'd fully support using local time. You didn't mention the database platform, but if your database platform supports it I think an event that isn't tied to a date should be stored in a pure time data type, assuming your database platform supports/offers this data type. For instance in SQL Server, I'd use the Time data type to store the hours of operation for a store. Using SQL Server once again, I'd use the DateTime2 data type to store the date and start time for a concert.

Since we're on the subject I personally would use UTC time on the server for everything that doesn't fit in the "local time only" parameters I outlined above, like you stated in your question. Many database platforms offer an analog to SQL Server's DateTimeOffset but this isn't really a safe solution because you end up losing the actual time zone information in lieu of the offset, and offsets change. For a more detailed overview on this and other time related issues, from a .NET point of view, check out this article written by Jon Skeet.


Arithmetic overflow error varchar to numeric I'm getting an arithmetic overflow error and I can't seem to find out the case.
I've recently had to adjust some stored procedures, to allow for dates in the future to be put in.

The following part of the ensuing data extraction now fails with arithmetic overflow errors:

SELECT
  CONVERT(varchar(8),DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()))/[Column2]
FROM Table1;

I'm not entirely sure why this error is occurring.
There are no more than 8 characters in any given entry, and changing it to varchar(max) doesn't do anything (as you'd expect).

Removing the /[Column2] does remove the error, but I can't for the life of me figure out why.

EDIT:
CAST([Column2] as numeric(8)) does the trick.
However, select max(len([Column2])) says the longest field is 2 chars long.

[Column1] is a decimal (32,0)
[Column2] is a decimal (3,0)

So what's going on? Why is this causing a problem?

You get the error because essentially at the end you're dividing an 8 character long string by a numeric value which is has precision as 3 like this example below

DECLARE @num VARCHAR(8) = '12356456'
DECLARE @den DECIMAL(3,0) = 121

SELECT @num / @den

SQL Server internally tries to convert VARCHAR(8) to DECIMAL(3,0) and fails with your error.

CAST([Column2] as numeric(8)) works because now VARCHAR(8) can be converted to numeric(8).

You don't really need to convert DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()) to VARCHAR(8)


Massive deviation of tablespace max_size compared to bytes used by segments on that tablespace I just checked the size of our tablespaces, because the admins said that they've made them bigger after we've had a full tablespace yesterday and I've discovered something really strange.

I hope someone can shed some light on this situation.
Here's what I did:

1) I queried the size of a certain tablespace, let's call it 'XYZ' for this purpose. XYZ is the tablespace that was full yesterday and got an upgrade in size.

select (max_size / 1024 / 1024) as size_megabyte from user_tablespaces where tablespace_name = 'XYZ'

The result is 2048 MB.

2) I queried the segments using the tablespace 'XYZ' and summed up their used bytes

select sum(bytes)/1024/1024 as size_megabyte from user_segments where tablespace_name = 'XYZ'

The result is 36000 MB.

3) I queried the free space left on the tablespace 'XYZ'

select sum(bytes)/1024/1024 as size_megabyte from user_free_space where tablespace_name = 'XYZ'

The result is 4000 MB

At this point my jaw dropped, and I can't fathom what's going on here.
Does anyone have a clue? Are my queries faulty?

Judging by the results of 2) and 3), I'd expect query 1) to return 40000 MB and not 2048 MB.

The way you have acquired the tablespace size is incorrect.

  MAX_SIZE(NUMBER) returns Default maximum size of segments (in Oracle blocks).

You can query dba_data_files and dba_free_space to get the result

Following is the exceprt of monitoring script that we have been using.

SELECT * FROM (SELECT a.tablespace_name, SUM(a.tots)/1024/1024-SUM(a.sumb)/1024/1024 Tot_Used_MB, SUM(a.tots)/1024/1024 Tot_Size_MB,
SUM(a.sumb)/1024/1024 Tot_Free_MB
FROM (SELECT tablespace_name,0 tots,SUM(bytes) sumb
FROM dba_free_space a  GROUP BY tablespace_name
UNION  SELECT tablespace_name,SUM(bytes) tots,0 FROM
dba_data_files GROUP BY tablespace_name) a GROUP BY a.tablespace_name);


Game database- many to many relationship I am designing a simple relational database about a game that can be played by 2 players each

time. I have these tables: Player, Game, PlayerPlaysGame, Payment. I recognize there is a

many-to-many relationship between Player and Game and one-to-many between Player and Payment

since one player can make many payments whereby one payment can't be made by more than one

player. In PlayerPlaysGame table I have only playerID and gameID attributes. Can I also add

gameDate and gameScore to that table or is it better to have them in Game table?

I'd like to ask if I'm on the right track.

Thank you.

Yes, I believe you are on the right track.  The gameDate and gameScore fields are more a part of the game entity and not the player, so they should be on the Gametable.  Besides, bridge tables should really only contain the related ID fields.

If gameDate and gameScore were on the PlayerPlaysGame table, they would only be dependent on the gameID and not the playerID.  By definition, this would constitute a partial dependency, and would violate second normal form.


How to store very small values (E-314) in SQL Server database? I had created table which hold small floating values using below query

create table temp_data (val float)

But when it gives warning message for very small values (e-314)

insert into temp_pce_rank_data values(2.466987305926227e-314)

Data gets inserted with value '0' warning message is: The floating point value '2.466987305926227e-314' is out of the range of computer representation (8 bytes).

How I can store & retirve very small values (E-314) in SQL Server database?
Thanks

None of the provided numeric types (float, decimal) are able to store so small numbers. The example you have, 2.466987305926227e-314, is smaller than the smallest normal double-precision float number.
I see two options, if you want to store such numbers:

Use a custom solution with two columns, one for the mantissa and another for the exponent. The disadvantage will be that you'll have to do all the math operators and functions with complex expressions (even for addition).

Use a custom CLR User-Defined Type. From that page:

Beginning with SQL Server 2005, you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.
Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to the following:
Date, time, currency, and extended numeric types
Geospatial applications
Encoded or encrypted data


SQL Error (126): Incorrect key file for table '.\mysql\user.MYI'; try to repair it To give you a bit more info I had moved my webserver from my C: drive to my D: drive and had upgrade my OS from Server 2008 to 2012.

All my website are fine and load without an issue and I havent run into any weird issues with them.

I will get this error in the title when I try to add a database to specific sql user or even add a new sql user all together.

Also tried a copy of my webserver on a VM with Windows 7 as the OS and did not get this issue so I think its something with Server 2012 but not sure what.

Any help would be greatly appreciated!

When using MyISAM, messages like that usually occur after a power failure (or other crash of mysqld).

USE mysql;
CHECK TABLE user;
REPAIR TABLE user;


SQL Server Memory allocation and max server memory setting For one of our SQL Server, we recently add more memory. The initial value was 12GB. The server experienced a lack of physical memory so we added another 12GB, for a total of 24GB. Then I have set the followings values : min. server memory 1024MB - max server memory : 19456. Well everything is running fine, but I have few questions about SQL Server memory usage. Indeed, SQL server suddenly used all the assigned memory :

I have a good book which say :"Once memory usage has increased beyond the min server memory setting, SQL Server won't release any memory below that amount" - Is the same thing happening here with max server memory ?

I'm wondering (again) how to measure how much memory SQL is really using. I'm confused with the parameter "Total server memory (KB)" ("this counter measures SQL Server's total buffer pool usage"). According perfmon it is only 2MB :

Maybe we could spare the ESX some RAM and SQL Server memory (i.e PLE) will still show good values - How to be sure ? A good explanation on how SQL memory works in that case will be really appreciate!

Your configuration is fine. 24GB total RAM, 19GB for SQL Server leaving 5GB for the OS.

SQL Server is memory hungry and will consume all the 19GB available, that is perfectly normal.

You could lower the max server memory from 19GB. I wouldn't do that however, if you were experiencing memory pressure with 12GB of memory.

The question would be what other roles is that server performing that would use the memory you would potentially deallocate from SQL Server? Can those roles (maybe SSIS, SSRS, SSAS etc) be moved to an alternative server?


extra steps after changing storage engine and adding index Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases.

I have a php/MySQL site uses the MyISAM storage engine.  It doesn't have any indexes on the columns.

I want to change the engine to innoDB and add indexes to the columns.

Some tables have a couple hundred thousand rows so it's not a very small database.

My question is mostly about the data that already is in the database.  Do I need to do anything after these changes to make the already stored data aware of that ? or make them compatible with these changes ?

You came to right place. Let's look at an example.

Suppose you have the following MyISAM table:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    firstname varchar(20),
    lastname varchar(20),
    straddr varchar(40),
    city varchar(40),
    state char(2),
    primary key (id)
) ENGINE=MyISAM;

You said you want to do two things:

Change the table to InnoDB
Add an index

For the above table, here is how to convert the table to InnoDB and add an index on names

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ALTER TABLE mydb.mytable ADD INDEX name (lastname,firstname);

Here is a way to make the table InnoDB, index it, and keep the original as MyISAM

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
ALTER TABLE mydb.mytablenew ENGINE=InnoDB;
ALTER TABLE mydb.mytablenew ADD INDEX name (lastname,firstname);
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytableold;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;

CAVEAT

This answer is really an oversimplification of the conversion. There are still other aspects of setting up InnoDB (Here is a Pictorial Representation of InnoDB's Infrastructure).  Here are a few of my past posts on such configuration due diligence:

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
Mar 04, 2011 : Clarification on MySQL innodb_flush_method variable
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Sep 12, 2011 : Possible to make MySQL use more than one core?
Feb 22, 2012 : Ibdata usage and Recommendations?
Apr 01, 2012 : Is innodb_file_per_table advisable?


Best situation to use READ UNCOMMITTED isolation level As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons might it be used?

Actually I read the answers before, but I could not understand it completely because there were not enough examples.

I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries for data analysis and troubleshooting don't impact the production workload, with the understanding the results might not be correct.

Sadly, I see READ_UNCOMMITTED/NOLOCK used widely in production code to avoid blocking at the expense of data integrity. The proper solution is a row-versioning isolation level (SNAPSHOT or READ_COMMITTED with the READ_COMMITTED_SNAPSHOT database option ON) and/or attention to query and index tuning.

I recently code reviewed a proc where the only change was to remove NOLOCK because it sometimes returned wrong results. Removing NOLOCK was a good thing but, knowing that missed or duplicated rows typically happens during allocation order scans of large tables, I suggested also refactoring to use a UNION ALL technique to promote efficient index use. The query now runs in a few milliseconds with correct results, the best of all worlds.


Definition of "table variable" and "base table" When reading SQL standard text (SQL:2003), I can not get the meaning of following sentence (in "Cluase 4.3 Tables" in SQL:2003-1):

  SQL-data consists entirely of table variables, called base tables.

I can not find the definition of "table variable" in the standard text, am I miss something obvious?

Community wiki answer:

A table variable is an instance of a table. Tables are called relations in relational algebra - hence relational variable (or relvar for short).

Base table means that they are not views which are derived from base tables.


Can I create a MySQL CSV table directly from an existing CSV file? MySQL has a CSV storage engine.

I'm wondering if I could create such a table directly from an existing CSV file.

Made up command as example:

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV FROM FILE /file;

What I'm trying to achieve is an efficient way to load data that doesn't depend on LOAD DATA as that command opens up the possibility of arbitrary command execution.

Here is something a MySQL Daredevil is more than willing to try.
I just did the following in MySQL 5.6.14 right on my Laptop
C:\Windows\system32>mysql -Dtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.14 MySQL Community Server (GPL)

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

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

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

MySQL://localhost/redwards/3306 test :: create table dt (a INT NOT NULL,b CHAR(10) NOT NULL) ENGINE=CSV;
Query OK, 0 rows affected (0.08 sec)

MySQL://localhost/redwards/3306 test :: insert into dt values (1,'rolando'),(2,'pamela'),(3,'dominique'),(4,'diamond');
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL://localhost/redwards/3306 information_schema :: flush tables;
Query OK, 0 rows affected (0.31 sec)

MySQL://localhost/redwards/3306 test :: exit
Bye

Now, going into the OS:
C:\Windows\system32>cd \MySQL_5.6.14\data\test

C:\MySQL_5.6.14\data\test>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14\data\test

12/18/2013  04:18 PM    <DIR>          .
12/18/2013  04:18 PM    <DIR>          ..
10/24/2013  10:20 AM                65 db.opt
12/18/2013  04:20 PM                35 dt.CSM
12/18/2013  04:19 PM                49 dt.CSV
12/18/2013  04:18 PM             8,578 dt.frm
10/29/2013  06:06 PM                 0 exp1#P#part00.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part00.MYI
10/29/2013  06:06 PM                 0 exp1#P#part01.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part01.MYI
10/29/2013  06:06 PM                 0 exp1#P#part02.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part02.MYI
10/29/2013  06:06 PM               336 exp1#P#part03.MYD
11/15/2013  05:44 PM             2,048 exp1#P#part03.MYI
10/29/2013  06:06 PM                 0 exp1#P#part99.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part99.MYI
10/29/2013  06:06 PM             8,642 exp1.frm
10/29/2013  06:06 PM                60 exp1.par
10/24/2013  10:30 AM             8,682 menus.frm
10/24/2013  10:30 AM                68 menus.MYD
10/28/2013  03:00 PM             1,024 menus.MYI
10/24/2013  10:30 AM             8,584 pages.frm
10/24/2013  10:30 AM                80 pages.MYD
10/28/2013  03:00 PM             2,048 pages.MYI
              22 File(s)         44,395 bytes
               2 Dir(s)  1,869,894,590,464 bytes free

C:\MySQL_5.6.14\data\test>type dt.CSV
1,"rolando"
2,"pamela"
3,"dominique"
4,"diamond"

C:\MySQL_5.6.14\data\test>

As show in the above commands in mysql and in the Windows command line, the CSV file that mysql can handle has the following two characteristics:

fields delimited by commas
character fields enclosed in double quotes

Since you created the test table as CSV, here is what you should do:

Make sure the existing CSV file has the two characteristics for mysql to work with it
Go to the folder test in your datadir.
Rename test.CSV to test1.CSV
Copy your existing CSV file to test.CSV
Login to mysql and run FLUSH TABLES;

Afterwards, the table shold be fully accessiable.
CAVEAT (Windows)
If Windows has the CSV table locked, run these commands

Make sure the existing  CSV file has the two characteristics for mysql to work with it
Go to the folder test in your datadir.
Run net stop mysql
Rename test.CSV to test1.CSV
Copy your existing CSV file to test.CSV
Run net start mysql

Give it a Try !!!