NULL value causes blank row in SELECT results for text concatenation I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results:

postgres=# SELECT '  (' || obj_id || ', ''' || obj_type || '''),' FROM il2.objects WHERE obj_id < 11 ORDER BY obj_id;
        ?column?
-------------------------
   (1, 'ScienceDomain'),

   (3, 'PIs'),
   (10, 'Instrument'),
(4 rows)

Doing a select *, it's pretty clear it's being caused by the obj_type being NULL for obj_id 2:

postgres=# SELECT * FROM il2.objects WHERE obj_id < 11;
 obj_id |   obj_type
--------+---------------
     10 | Instrument
      1 | ScienceDomain
      2 |
      3 | PIs
(4 rows)

(confirming it's NULL):

postgres=# SELECT * FROM il2.objects WHERE obj_type IS NULL;
 obj_id | obj_type
--------+----------
      2 |

Why is the result of the first SELECT giving me a blank row?
Even casting obj_type::text still gave me a blank row.

Additional Info:
The schema, for what it's worth:

postgres=# \d il2.objects
                                  Table "il2.objects"
  Column  |       Type        | Collation | Nullable |             Default
----------+-------------------+-----------+----------+----------------------------------
 obj_id   | integer           |           | not null | generated by default as identity
 obj_type | character varying |           |          |
Indexes:
    "objects_pkey" PRIMARY KEY, btree (obj_id)

Use COALESCE function to return the current value or an empty string.

SELECT '  (' || obj_id || ', ''' || coalesce(obj_type, '') || '''),'
FROM   objects;

| ?column?        |
| :-------------- |
|   (1, 'val 1'), |
|   (2, ''),      |
|   (3, 'val 3'), |
|   (4, 'val 4'), |

db<>fiddle here


how to get rank number that group by a column I spend hours trying to solve this, but I can't.

Well, I simply have a table call invoices

I have data like this

I want to know each customer rank by their total sum

How can I archive this?

You can use a variable for this purpose.

select customer_id, total, rank
from (
      select customer_id, total, @rank := @rank + 1 as rank
      from (
            select customer_id, sum(total) total
            from   invoices
            group by customer_id
            order by sum(total) desc
           ) t1, (select @rank := 0) t2
     ) t3
where customer_id = 1;

customer_id | total | rank
----------: | ----: | ---:
          1 | 14080 |    2

dbfiddle here


Merge 2 pairs of tables from 2 databases. Beginner I have two MySql databases DB1 and DB2. Each of them has tables tbl1 and tbl2. These two tables have the same primary key (Column1). Tables in DB1 have N rows. I want to create new database and table, which will look like:

DB1.tbl1.Col1   | DB1.tbl1.Col2 | ... | DB1.tbl1.ColK | DB1.tbl2.Col2 | DB1.tbl2.ColZ
    ...         |    ...        | ... |     ...       |    ...        |      ...
DB2.tbl1.Col1+N | DB2.tbl1.Col2 | ... | DB2.tbl1.ColK | DB2.tbl2.Col2 | DB2.tbl2.ColZ
    ...         |    ...        | ... |     ...       |    ...        |      ...

But, I could not use MERGE as I need indexes in DB2.tbl1.Col1 in new table to be DB3.tbl.Col1=DB1.tbl1.Col1, DB2.tbl1.Col1+N.

I now how to do it on the side of client (on Python), but is it possible to do it via some MySql script on server side?

Thank you.

how about?

create table DB3.tbl1 as
select * from (
  select
    DB1.tbl1.Col1,
    DB1.tbl1.Col2, ...
    DB1.tbl1.ColZ
  from DB1.tbl1
  union all
  select
    DB2.tbl1.Col1 + n
    DB2.tbl1.Col2, ...
    DB2.tbl1.ColZ
  from DB2.tbl1
)

could also replace the + n by + ( select count(*) from DB1.tbl1 )


What is a SQL Server Distributor? This question (SQL Server transactional replication distributor), mentions a blog post HERE, which talks about how the distribution database works in relation to publishers and subscribers. Seems pretty clear.

Is the Distributor a fully fledged instance of SQL Server? I understand that it could run on a separate server, so it would seem like it. If not, what is the difference between a Distribution server and a SQL Server instance?

I guess from THIS article that the transaction logs of the publisher are stored in the distribution database, which are then read by the subscribers. And that this is called 'transactional replication'? Are there any other types of replication?

Yes the Distributor is a "fully fledged instance of SQL Server". A Distributor is a SQL Server instance that contains a distribution database. The Distribution server could have other databases on it.

The transaction logs of the publisher are NOT stored in the distribution database. What gets stored in there are the DML commands (INSERT/UPDATE/DELETE), though represented a bit different.

The other types of replication are Merge, Snapshot and Peer to Peer.


Optimizing a slow query to better use indexing I have a query that looks like the following:

SELECT time_stop, some_count
        FROM foo
        WHERE user_id = 1
        AND time_start >= '2016-07-27 00:00:00'
        AND time_stop <= '2016-07-27 23:59:59'
        AND some_count = ( SELECT MAX(some_count)
          FROM foo
          WHERE user_id = 1
          AND tm_start >= '2016-07-27 00:00:00'
          AND tm_stop <= '2016-07-27 23:59:59'
       );

The table schema looks something like this:

Create Table: CREATE TABLE `foo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `time_start` datetime(6) DEFAULT NULL,
  `time_stop` datetime(6) DEFAULT NULL,
  `some_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),,
  KEY `user_id` (`user_id`),
  KEY `time_start` (`time_start`)
) ENGINE=InnoDB AUTO_INCREMENT=418005923 DEFAULT CHARSET=latin1

EXPLAIN output looks like the following:

+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys      | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | foo            | NULL       | ref  | user_id,time_start | user_id | 4       | const | 931364 |     1.67 | Using where |
|  2 | SUBQUERY    | foo            | NULL       | ref  | user_id,time_start | user_id | 4       | const | 931364 |    16.66 | Using where |
+----+-------------+----------------+------------+------+------------------+---------+---------+-------+--------+----------+-------------+

I'm using MySQL 5.7.11.

I think the major problem I'm having is there's very little cardinality in the indexed keys here. The query is attempting to get the largest value of some_count for a given date range. It also wants the exact datetime that the max value was met. This could return multiple rows, which returns to the little cardinality problem.

I'm not sure if I could re-write the query to better work with the existing indexes, I'm guessing I could though.

Your query seems to be simply this:

SELECT time_stop, some_count
    FROM foo
    WHERE user_id = 1
    AND time_start >= '2016-07-27 00:00:00'
    AND time_stop <= '2016-07-27 23:59:59'
 ORDER BY some_count DESC LIMIT 1;

You need an index on (user_id,time_start) -- in that order -- and, assuming start is always earlier than stop, adding the seemingly-unnecessary AND time_start <= '2016-07-27 23:59:59' will allow the database to avoid even considering rows with time_start values later.

Also adding time_stop to the same index will not help you, for the same reason that the telephone directory won't help you find all people with first names beginning with the letter L.  Adding some_count to the index also won't help, for the same reason.  In a multi-column index, the values in column B are sorted within identical groups of column A (hence the telephone directory reference). The values in column C are sorted within identical groups of B, etc... so if A is an equality comparison, and B is a range, as in your query, those are easy to pinpoint... but the values of C appear random.  For C to be usable in an index, B needs also to be an equality comparison, which it is not, in your case.

However, if these are the only columns you're reading, then adding those columns to the index (to the right of user_id and time_start) could be useful for a different reason -- when a query uses an index that contains all the columns selected, it's a "covering index," and the entire result can be read directly from the index, instead of the main table, potentially giving a benefit that you have to weigh against the cost of maintaining larger indexes.


SELECT records based on return of COUNT query I would like to create a query that returns records if values in a column occur more than once.  I came across other Stack Exchange questions but these only dealt with returning the number of occurrences and not the actual records.
For example, if I have a table of "Names":

Id   Name        City
1    Mike        NYC
2    Bob         DC
3    Rachel      DC
4    Mike        NYC
5    Bob         DC
6    Melissa     LA
7    Melissa     LA

...and I want to get the count of each name, I understand that I use the SQL:

SELECT Name, COUNT(Name) AS "Count"
FROM Names
GROUP BY Name;

The following would be returned:

Name       Count
Bob        2
Mike       2
Rachel     1
Melissa    2

Now, If I want to get the count of each name where the count is more than 1, I do this:

SELECT Name, COUNT(Name) AS "Count"
FROM Names
GROUP BY Name
HAVING COUNT(Name) > 1;

The following would be returned:

Name       Count
Bob        2
Mike       2
Melissa    2

Now, what I want to do is return all the columns for each value if they occur more than once.  I thought about doing a join on the table after doing the counts, but that seems like an expensive operation when I tried it a few times.  The more columns I added, I noticed that it was comparing those too, which is not what I want.  What I'd like is the result below.

Name       City    Count
Bob        DC        2
Mike       NYC       2
Melissa    LA        2

I know there should be an easy way to do this, but I'm just not finding it.  Thanks!

This can be done using a window function:

select name, city, cnt
from (
  select name, city, count(*) over (partition by name) as cnt
  from names
) t
where cnt > 1;


What's the disadvantage of encrypt mariadb? I know if I encrypt mariadb,  no one can steal my data.
What's the disadvantage of  encrypt  mariadb?

Your question is a bit open-ended, but here are few things that might be considered disadvantages:

Encryption and decryption are CPU-intensive tasks, so you will see increased CPU utilization. If your server is CPU-bound, this may be a problem.
As a consequence of the above, encryption/decryption add certain latency to your I/O operations, so query performance might be negatively affected.
There is certain effort involved in managing encryption keys. Also, if you ever lose them, you lose all your data as well.


mssql suddenly cannot access physical file SQL server 2019 on Centos 7 x86_64. It was running fine for quite long time, then got broken.
I was not doing anything special, neither update or restart or whatever. At some point the database went offline, and in errorlog I see these lines every time when I try to bring db online:

  FCB::Open failed: Could not open file /var/opt/mssql/data/base.mdf for file number 1.  OS error: 5(Access is denied.).
  Unable to open the physical file "/var/opt/mssql/data/base.mdf". Operating system error 5: "5(Access is denied.)".

I tried to su into mssql user. All files and paths were accessible. selinux is disabled on that machine.

I then tried to run sql service as root. Still same problem (root cannot get access to files?)

Tried to make all files 777. Tried to reboot in case someone is holding some lock or whatever. No luck. At this point I think that it is some weird bug in mssql and it just blames OS.

Dropped the base and restored from backup with same name and same file locations. Worked without any warning, and works ever since.

I would appreciate if anyone could share some wisdom as of what could be happening there, because even though the problem was easily solved, I really don't like to deal with haunted servers.

This is definitely interesting. Normally, Access is denied error is due to changed/missing permission on the file. Did you see any related errors in journalctl?
If it happens again, I'd raise a ticket with Microsoft.


Job not running on Schedule So I have a basic SQL agent job that runs a Robocopy script to move all files from one folder to another.

Job is a pretty basic set up.

With a pretty basic schedule.

And yet it has yet to run. I don't mean run successfully either I mean run at all. Is there any reason this might be the case?

For additional information I'll script out the job as well.

USE [msdb]
GO

/****** Object:  Job [MoveMantisFilesToArchive]    Script Date: 12/23/2015 10:21:52 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/23/2015 10:21:52 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MoveMantisFilesToArchive',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Moves Mantis files to archive. It''s a very descriptive title.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'MyEmailGroup', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Move the files in the afformentioned title.]    Script Date: 12/23/2015 10:21:53 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Move the files in the afformentioned title.',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'robocopy MySoruce MyDestination /mov',
        @flags=0,
        @proxy_name=N'RunsAs'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'M-F',
        @enabled=1,
        @freq_type=8,
        @freq_interval=62,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20151218,
        @active_end_date=99991231,
        @active_start_time=170000,
        @active_end_time=235959,
        @schedule_uid=N'bcb83273-19e8-49fb-a456-8517642370e3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Commentary on this question:  Looking over this post I observe that your job was originally running as 'sa'.  It appears that the service account for your SQL Server was not given rights to the necessary file shares.

This is apparently what led to the job looking as if it were "Running" forever. Of course, nothing was actually happening.

It is a best practice to withhold giving the SQL Server service account rights to any non-essential folders. This helps keep the SQL Server environment from being exploited for unsafe activities.  (Much the same reason that the xp_cmdshell stored procedure is disabled by default.)

When you switched from sa to an account that had the needed rights to the file system everything worked.  Which was, of course, the right thing to do.

Scheduled SQL Agent jobs do sometimes hang (but look like they are still 'running') for a long time. Likely this is usually due to external issues, such as not getting access to file system.

As long as the SQL Agent believes the job is "running", it will not try to start the job again.

Simple lessons:

Think of 'sa' as ruling the SQL Server, but must beg for rights elsewhere.
When reviewing SQL Agent job history, be alert to jobs that have been running much too long. That usually means that the SQL Agent does not realize that the process has died.
Always plan to use a proxy account for SQL Agent jobs that need to access data or objects outside of the SQL Server. And make sure that the rights are granted to the Credential that the Proxy is using.

And, of course, every rule has exceptions.


ACE.OLEDB.12 installed but everything has a 0 on sp_msset_oledb_prop So I installed ACE.OLEDB.12 to import excel files into sql server. After this, I can acces this procedure to see if everything is ok, but it's not. I have Zeroes on everything. This is a simple question. Should I restart sql server to validade it? I'm asking this because it's a production server and it's really hard to find a free time to restart it. If I'm sure this is going to fix the issue, I can restart it anytime. Thanks.

You may need to run commands to enable them.

   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
GO
   EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'SqlServerLIKE', 1
GO


Oracle: Casting timestamp as date and comparing result I don‘t really understand what cast(timestamp as date) is doing. I am on Oracle Database 11g Express Edition Release 11.2.0.2.0.

select cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) from dual

Displays the date 20190516 (without time) in the date format I have in my session.

select to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') from dual;

Shows the date with time in my session format. So far everything as I expected.

So casting seems to remove the time part. Now I want to compare the result of the cast with a date which works in SQL Server but not Oracle.

select case when cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) = to_date('20190516', 'yyyymmdd') then 1 else 0 end as match from dual; -- 0 = not matched
select case when trunc(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS')) = to_date('20190516', 'yyyymmdd') then 1 else 0 end as match from dual; -- 1 = matched

What exacty is returned by cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) when it does not seem to be just the date as indicated by the first select? Why can‘t I compare the result?

As shown I luckily have another solution with trunc(). I would like to know why comparing does not work  and if there is a possiblity to get the compare to work with cast. I am porting a view from SQL Server to Oracle and would like to keep as much as possible the same and it is done with cast in SQL Server.

Casting to a DATE datatype from a TIMESTAMP only removes the fractional seconds, therefore your direct to_date('20190516', 'yyyymmdd') comparison is rightly failing. It's just a display issue that has confused you.

Definition of the Oracle DATE datatype (from here):

  Valid date range from January 1, 4712 BC to December 31, 9999 AD. The
  default format is determined explicitly by the NLS_DATE_FORMAT
  parameter or implicitly by the NLS_TERRITORY parameter. The size is
  fixed at 7 bytes. This datatype contains the datetime fields YEAR,
  MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional
  seconds or a time zone.

This DB Fiddle demonstrates.

These queries probably demonstrate best:

-- cast to date, and format with TO_CHAR to show that the time element still exists
select  TO_CHAR(
               cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date)
                ,'yyyymmdd HH24:MI:SS') from dual;

-- demonstrate that the fractional element gets removed
select cast(
         cast(to_timestamp('20190516 08:00:00.123', 'yyyymmdd HH24:MI:SS.FF')
              as date)
       as timestamp) from dual;


PHPMyAdmin for multiple servers w/ single pma database We have PHPMyAdmin setup for our various MySQL instances.  The annoying part is the current setup is requiring an individual pma database on each instance.  Because of this the severs that are slaves cannot run in readonly mode.  (PHPMyAdmin needs to write to the pma database).

Can someone offer advice or point me to an appropriate walk through on how to set this up?

I have a little surprise you : Did you know that if you have the SUPER privilege, you can write to a read_only database ?
According to the MySQL Documentation on read_only:

This variable is off by default. When it is enabled, the server
permits no updates except from users that have the SUPER privilege or
(on a slave server) from updates performed by slave threads. In
replication setups, it can be useful to enable read_only on slave
servers to ensure that slaves accept updates only from the master
server and not from clients

All you need to do is connect to the slave as a DB user with the SUPER privilege and write to the pma database to your heart's content.
UPDATE 2011-10-24 23:33 EDT
On the slave servers, you must add this /etc/my.cnf and restart mysql
[mysqld]
replicate-ignore-db=pma

That way, each pma database can be specific to the local DB instance. As for writing, you must use SUPER privilege to bypass the read_only restriction. Only other alternative is to drop the read_only option from /etc/my.cnf and restat mysql.


How do I open an ERwin Naming Standards file (.nsm) in Excel? I have a Naming Standards file in ERwin (.nsm format) that I want to be able to open up in Excel (for example, in order to compare 2 .nsm files). How would I be able to do that?

First, you need to load the .nsm file into the model. This is done through Tools > Names > Model Naming Options... > Use File: (select the appropriate file).

Well, if you've got version R9 or higher, you're in luck. As per this post, the naming standards are an object in the model explorer just like every other entity, so you can simply find it in the model explorer, right click on it, select properties, and on the glossary tab click the save button, and save as .csv.

For pre-R9 versions, you need to go to Tools > Names > Edit Naming Standards... The Naming Standards Editor window should pop up. There's a few tabs near the bottom, click the one that says 'glossary'. You'll now have an option to export the file as .csv.

Took me way too long to figure this out myself, so hopefully this could help the next guy who has the same issue. ;)


MySQL: Unique constraint on large column I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.

MySQL appears to enforce constraints using an index. In InnoDB, index sizes appear to be limited to 767 bytes - not nearly enough for the VARCHAR(3071) column that is holding the data.

Any thoughts on how to have the database enforce uniqueness of the data, without compromising on maximum data length or the usage of InnoDB?

You do not want a gigantic gen_clust_index (Internal Clustered Index). That size is ungodly huge even for a secondary index.

You may have to resort to triggers or stored procedures to check for the key well in advance.

You could also think about performing an SHA1 function call using the VARCHAR(3071) field. SHA1 will return a 40-character field. This hash may be just what you need to index.

Suppose you have this

CREATE TABLE mytable
(
    id int not null auto_increment,
    txt VARCHAR(3071),
    primary key (id)
) ENGINE=InnODB;

and you want to make a UNIQUE index on txt. Try the SHA1 approach

CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable ADD txtsha1 CHAR(40);
ALTER TABLE mytable ADD UNIQUE KEY (txtsha1);
INSERT INTO mytablenew (id,txt,txtsha1)
SELECT id,txt,SHA1(txt) FROM mytable;

Then, count them

SELECT COUNT(1) FROM mytable;
SELECT COUNT(1) FROM mytablenew;

If the Counts are the Same, CONGRATULATIONS !!! Now you have a unique index of length 40. You can finish up with:

ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

This could be more atomically as pointed out in the comments below:

RENAME TABLE mytable TO mytableold, mytablenew TO mytable;
DROP TABLE mytableold;

Perform this on whatever table you intend to have this big column. You have to remember to add the SHA1 of the data along with the data upon INSERT.

The odds of duplicate keys is 1 in 2 to the 160th power (that 1.4615016373309029182036848327163e+48. If I get the exact figure, I'll post it someday).

Give it a Try !!!


Index for numeric field is not used I have PG server (9.0.4) installed on Debian x86.
In one of my tables I use numeric(6,1):

CREATE TABLE mytable(name text, numfield numeric(6,1))

Also index created for this field:

CREATE INDEX mytable_numfield_idx ON mytable USING btree (numfield);

Everything is going fine if I use this field with real numeric value. As I see the query uses the index:

EXPLAIN ANALYZE SELECT * FROM kodiall WHERE kodgo=123.0

  Index Scan using mytable_numfield_idx on mytable  (cost=0.00..8.27 rows=1
  width=193) (actual time=0.085..0.087 rows=1 loops=1)

  Index Cond: (numfield = 123.0)

  Total runtime: 0.131 ms

But the index is ignored for some reason if I use 0.0 or NULL as condition value:

EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0
--EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=NULL

  Seq Scan on mytable  (cost=0.00..57.80 rows=1080 width=193) (actual
  time=0.033..1.853 rows=1088 loops=1)

  Filter: (numfield = 0.0)

What wrong here? Is 0.0 not a numeric value?

In real query I use LEFT JOIN with numfield and values in joined table can be NULL.

While you EXPLAIN your query, in the first instance, you can see that the planner estimates the total row count to 1

Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 **rows=1** width=193)

Using an index in this case is beneficial as there are hardly any rows matching your WHERE condition.

In the second case, there are around 1080 rows that meet your condition. Going through the index for each of these would be expensive when compared to a sequential scan. (More disk movements for reading index entries and then touching the actual table rows.)

Seq Scan on mytable (cost=0.00..57.80 **rows=1080** width=193)

You can still try forcing the planner to use the index for the second case by temporarily disabling a sequential scan. You can achieve that with:

BEGIN;
set enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0
COMMIT;

The actual time taken by the above statement is likely to be more as compared to a sequential scan selected by the planner.

Note: Sequential scan is not always bad. If you are selecting a major chunk of your table it is faster to read the entire table and filter the rows rather than jumping between the pages of the index and the table itself.


Usage of non standardized queries So this is more of a theory question than practical but I'd appreciate any help. My friend and I are doing a project to learn new stuff and have fun. He's a coder and I like to think that I'm good with databases. Today an issue arose because he said that he thinks we shouldn't have repetitive queries just for different tables and had an idea of using ? or some other symbol in my queries so he can replace it with appropriate word in code.

However, I don't agree, partly because I like having all of my queries in pure SQL and partly because I don't think that it's a good permanent solution. For an example what if a table gets new fields? The query is automatically not valid anymore.

Even if there are tables that are relatively similar and could use the same query is his idea a good practice or am I right about separating the queries?

Sorry if this is not the right place to post the question since it's not really a specific problem.

When you add a column to a table, most, if not all, queries referencing the table will need changing.  That is the cost of adding a column.

Using ? is good.  It (or the equivalent) is available in most client languages.  But... It only lets you change numbers and strings, not table names.  Numbers and strings are the flexibility you need.  A parameterized table name implies that the schema is poorly designed -- It is usually bad to have multiple tables with the same sets of columns.


How do I determine why my stored procedure is getting deadlocked? We have a stored procedure that our application uses to query data (no write operations are performed). I have the complete staging database copied to my local version of Sql Server developer edition.  The stored procedure locally runs perfectly fine and gives me results in less than a second.  When I run it on our staging system the stored procedure gets suspended and sits there.

I cannot figure out why this is getting deadlocked and I feel stuck.

When I run exec sp_lock I see a LOT of entries for a specific spid with all different object ids, and all the locks are of type TAB with different object ids (probably makes sense as the query returns about 30 fields, at least half of which are subqueries).

When I run exec sp_who2 I see that SPID keeps going between SUSPENDED and RUNNABLE states, with ever increasing CPUTime and DiskIO values.

I'm not sure where to go from here.  I can almost guarantee that the tables the stored procedure is using is not being used by anything else, as this system isn't live.  Also, other stored procedures querying data over the same tables run flawlessly.  How can I identify the source of the deadlock?

I think "deadlocked" is the wrong term in this case.  Your query was getting blocked.  Deadlocked means that it is blocked by another process that is in turn blocked, waiting for your original process to complete.  Since these two processes will never complete, one of them is chosen to the victim of a deadlock, and it is terminated, allowing the other one to continue.


AlwaysOn - Running SQL Agent Jobs on Secondary read-only node writing audit information to database I am setting up an AlwaysOn Availability Group with 3 nodes. 1 Primary, 1 Secondary Read-only node and 1 secondary non-readable node.
I want to offload my maintenance jobs to my secondary read-only node which is fine. I can do this by adding a step to all my maintenance jobs to check the node. If it's the Primary node, the job will stop before moving on to the next step.

I also have a maintenance database where I record all my maintenance job information such as failures, start times, end time, durations, index/statistic names etc etc. I want to keep this database within my AG group rather than have a different maintenance DB on each node.
So my question is..

IF my maintenance jobs are running on my secondary read-only node and I need this to record audit information in a maintenance database within the AG Group, will this automatically write the data to the Primary Node DB or will it try and write this to the Secondary Node database. If it tries to write to the secondary node will it fail? If it does, is there a good workaround that doesn't involve having a separate maintenanceDB on each node or keeping my maintenanceDB within it's own separate Availability Group?

No, it won't automatically write to the primary database instance. You need to connect to the primary instance and perform your data inserts on that instance (linked servers is one option to do this).

You also need to make sure you're running your jobs in the right location. For example, Transaction Log Backups can be offloaded to a secondary, but even a synchronous secondary can run behind the primary (sometimes by a lot of time). If that happens, are you going to be able to meet your RPO goals?

Index maintenance is going to have to run on the primary as it needs a writable database - you can't offload this task.

CHECKDB can be run on the secondary, but I think it should be run on all instances if at all possible. When replication happens it only transfers the log data, not data pages. If you have a disk subsystem corruption on one of your replicas that you don't run CHECKDB on then you may not find corruption until you failover.


Complex MySQL query through relational tables I am generating a query that returns a set of profiles that meet all the conditions posted below

I am receiving the data like so in Ruby, then making a dynamic MySQL query based on the contents -

[{ attribute_id: 58, parent_profile_name: 'Douglas-Connelly' },
{ attribute_id: 26, parent_profile_name: 'Brekke LLC' },
{ attribute_id: 35, val: 'Asa' },
{ attribute_id: 38, val: 'Stanton' }]

These are the current contents of the database

profile_attribute_values
profile_id  attribute_id    parent_profile_id   val
6           58              2
6           26              5
6           35              nil                 'Asa'
6           38              nil                 'Stanton'

profile
id     name
2      Douglas-Connelly
5      Brekke LLC
6      nil

I need to return all profiles that meet all the conditions - profiles that have a relation to profile_attribute_values, where the attribute_id is x, and the val is y, AND where the attribute_id is x, and the parent_profile name = y

WHAT I CURRENTLY HAVE

SELECT * FROM

(SELECT P2. *
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
) A,

(SELECT P1. *
FROM profiles P1
INNER JOIN profile_attribute_values PAV ON P1.id = PAV.profile_id
INNER JOIN profile_attribute_values PAV2 ON P1.id = PAV2.profile_id
WHERE (PAV.ne_attribute_id = '35' AND PAV.val = 'ASA')
AND (PAV2.ne_attribute_id = '38' AND PAV2.val = 'Stanton')
) B

WHERE A.id = B.id

This will return

profile
id    name
6     nil

which is exactly what I want, though the tricky part is the second parent_profile condition where I need attribute_id 26, and parent_profile_name: 'Brekke LLC'

I know this wont work, but I need this to do something like this

SELECT * FROM

(SELECT P2. *
FROM profile_attribute_values PAV
INNER JOIN profiles P1 ON P1.id = PAV.parent_profile_id
INNER JOIN profiles P2 ON P2.id = PAV.profile_id
WHERE (PAV.ne_attribute_id = '58' AND P1.`name` = 'Douglas-Connelly')
AND (PAV.ne_attribute_id = '26' AND P1.`name` = 'Brekke LLC')
) A,
.....

I am generating the SQL statement dynamically, so I really need it to be as clean as possible. I generally use ruby active record for everything, so I am a little babied when it comes to SQL statements. Thanks!

Looks like straightforward addition of INNER JOIN for every condition will do

SELECT id
FROM profile p

INNER JOIN profile_attribute_values pav1 ON p.id = pav1.profile_id
  AND pav1.attribute_id = '58'
INNER JOIN profiles p1 ON p1.id = pav1.parent_profile_id
  AND p1.`name` = 'Douglas-Connelly'

INNER JOIN profile_attribute_values pav2 ON p.id = pav2.profile_id
  AND pav2.attribute_id = '26'
INNER JOIN profiles p2 ON p2.id = pav2.parent_profile_id
  AND p2.`name` = 'Brekke LLC'

INNER JOIN profile_attribute_values pav3 ON p.id = pav3.profile_id
  AND pav3.ne_attribute_id = '35' AND pav3.val = 'ASA'

INNER JOIN profile_attribute_values pav4 ON p.id = pav4.profile_id
  AND pav4.ne_attribute_id = '38' AND pav4.val = 'Stanton'


Foreign keys to prevent "orphan" file rows in a database for a scenario involving projects, files and downloads In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables:

┌──────────┐ ┌──────────────────┐ ┌────────┐
│projects  │ │downloads         │ │files   │
├──────────┤ ├──────────────────┤ ├────────┤
│project_id│ │download_id       │ │file_id │
│name      │ │project_id        │ │contents│
│(FK?)     │ │download_timestamp│ │(FK?)   │
└──────────┘ │file_id           │ └────────┘
             │(FK?)             │
             └──────────────────┘

All downloads and files belong to a project.
Everytime a file is downloaded, a new download is inserted.
If the same contents already exists in the files table for the same project, the new download will point to the existing file, otherwise a new file is also inserted.

I have two foreign keys in downloads, project_id and file_id. Is there a better structure, especially one that prevents orphaned files (files without an associated download)?

Schematically (out of syntax).

Structures:

CREATE TABLE project ( project_id identity,
                       primary key (project_id),
                       name varchar,
                       unique (name) );

CREATE TABLE file ( file_id identity,
                    primary key (file_id),
                    filelink varchar,
                    content varchar,
                    unique (bf(file_link || content)) );

CREATE TABLE download ( download_id identity,
                        primary key (download_id),
                        project_id,
                        foreign key (project_id) references project (project_id),
                        file_id,
                        foreign key (file_id) references file (file_id),
                        download_timestamp default current_timestamp );

Insertion:

INSERT INTO project (name)
VALUES (@name)
ON CONFLICT (name) DO NOTHING;

INSERT INTO file (filelink, content)
VALUES (@filelink, @content)
ON CONFLICT (filelink, content) DO NOTHING;

INSERT INTO downloads (project_id, file_id)
SELECT project.project_id, file.file_id
FROM project, file
WHERE project.name = @name
  AND file.filelink = @filelink
  AND file.content = @content;


Some book suggestions for performance tuning I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in different ways affect performance and also   understanding things like table scans, indexes  an statistics.

I don't just want to blindly add everything the tuning advisor says so I am looking for the knowledge required to assess these recommendations in the context our environment and how to fully utilise the tools available.  I am working with MSSQL2008+ so a book the utilises this environment specific would be good.

Thanks in advance

I like Kalen Delaney's internals book:

Microsoft SQL Server 2008 Internals

I also like the Brent Ozar / Christian Bolton book:

Professional SQL Server 2008 Internals and Troubleshooting

For T-SQL tuning, you can't get much a better T-SQL wizard than Itzik Ben-Gan:

Inside Microsoft SQL Server 2008: T-SQL Programming
Inside Microsoft SQL Server 2008: T-SQL Querying

And query tuning overall, Grant Fritchey and Sajal Dam:

SQL Server 2008 Query Performance Tuning Distilled

There are also some interesting solutions and opinions presented in various chapters in the two MVP Deep Dives titles (from which all proceeds go to two charities, War Child and Operation Smile):

SQL Server MVP Deep Dives Volume 1 (War Child)
SQL Server MVP Deep Dives Volume 2 (Operation Smile)

And a couple of the Red Gate titles @Kev left out, but which are both excellent:

Inside the SQL Server Query Optimizer (Ben Nevarez)
Troubleshooting SQL Server: A Guide for the Accidental DBA (Jonathan Kehayias / Ted Kreuger)


Revoke database-level privileges without the need for "use db_name;" statement I have a MySQL user with database-level privileges.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mydb.* TO 'theuser';

The user can create tables and insert rows into them.

When the user exceeds the limits, for example, the number of allowed rows in his plan, I want to revoke the INSERT privilege. So the user cannot insert new rows into the tables anymore. It's a plan with soft limits.

I know I can execute the following query:

REVOKE INSERT ON mydb.* FROM 'theuser';

But it doesn't affect until the next use mydb.

According to MySQL docs:

  A grant table reload affects privileges for each existing client connection as follows:

  Table and column privilege changes take effect with the client's next request.
  Database privilege changes take effect the next time the client executes a USE db_name statement.
  Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

So my problem is how can I force MySQL to reload database-level privileges without the need for "use db_name" statement, just like table-level privileges.

There is really nothing you can do to circumvent this. Why ???

Look at your third bulletpoint:

  Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

That bulletpoint is true for a user with SUPER privileges or root@localhost. Such a user cannot change a live connection's grants in full in the middle of an external or separate session. That has to be true even more so for a non-superuser.

By definition, you should be able to revoke a privilege you could grant.

But look at the grants again

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mydb.* TO 'theuser';

Notice that this user cannot give away its grants. Why not ??? The WITH GRANT OPTION is missing. So, theuser cannot give away its grants to anyone else.

What you are seeking is the opposite: to revoke the grants. In this case, you are revoking from yourself. Unfortunately, there is no such thing as WITH REVOKE OPTION.

Switching to a database with USE mydb signals mysqld to trigger the reload of theuser's grants from all the grant tables (mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv). Doing this is far easier to implement that creating an option to revoke grants from a live connection (kinda like unsubscribing from a service that sends you email). Otherwise, grants would more frequently reload grants into RAM for mysqld or into a session's internal buffers. This would introduce some significant read traffic from these tables just for establishing grants.

In light of all this, there are only fours things you can do:

Do the USE mydb (Best Option)
Code Changes (Godspeed, Spiderman !!!)

Disconnect and open a new session
Change application to no longer do SELECT if the user is theuser
Use Views, then you can just drops views and trap read errors based on missing views


Size of Column store index on Table I copied a row-based table with about 90 million rows into a column store index Table.

The decrease rate of the table was acceptable. But I want the size of column store index.

The sp_spaceused procedure didn't give any result for the column-based table. How can we get the size of the index?

With a clustered columnstore index, the data and index are one and the same so the reported data size includes both data and index.

A traditional b-tree clustered index also has non-leaf index pages which is reported separately by the index space.


New SQL Server with slow Index Spool (Eager Spool) and old server used index So yesterday I posted this question regarding that we have upgraded from SQL Server 2008 to SQL Server 2017 and that we excperience some performance problems.

I got the answer to try updating statistics which worked for one database, but not the others. Should I set compability level from 2008 to 2017?  At the current time, compatibility level has been left at 100 (SQL Server 2008).

Old server was a physical server, the new server is a VM (Hyper-V).

After the upgrade the execution plan looked different from the old one
and it seems to use Index Spool (Eager Spool) instead of using index. Which means that execution time went from 3 seconds on the old server to 1 minute 50 seconds on the new server.

The only thing I notice that is different from the database that does not have Eager Spool with same query is that the table rows are less, it's 13.000.000 rows on the non-working vs 70.000 rows on the working with correct execution plan.

The plans below are anonymized but I hope there's enough information.

Here is the plan with Eager Spool vs The other databas with Index

I have tried

Updating statistics
Changing cost threshold for parallelism and max degree of parallelism accordingly.
Double check indexes if they're as before.
I've looked at READ_COMMITTED_SNAPSHOT.
I've checked the NUMA configuration.

Another problem is that wait types are high

LCK_M_IS,
CXPACKET,
LCK_M_X,
ASYNC_IO_COMPLETION,
LCK_M_IX,
PAGELATCH_EX,
BACKBUFFER,
TRACEWRITE.

I am starting to run out of ideas.

Max memory is set to leave 4GB for OS, I noticed yesterday that power plan on the host machine was set to balanced but changed it to high perf, but still same problem.

On received advice to update statistics:
Seems to have worked on one of the databases, however I still have a problem on all the other databases.

The old server was a physical server with 8 cores and 32GB RAM.  The new server is a VM running on Hyper-V with 4 cores and 64GB Ram.

It's possible having double the amount of RAM in the new server is causing the query optimizer to choose a plan that performs worse than expected.

You could try using DBCC OPTIMIZER_WHATIF1 to see what happens if you have 20 cores and only 32 GB of RAM, like the old system.  I wrote a blog post showing how that works on sqlserverscience.com.

To see the effect on plan choice with 32GB of RAM, you could use this command:

dbcc optimizer_whatif ('MemoryMBs', 32768);

You'll need to evict the plan for the query from the plan cache, or force a new plan to be compiled using WITH RECOMPILE or OPTION (RECOMPILE) to see if that causes SQL Server to choose the "better" plan.

If you see a better plan after using dbcc optimizer_whatif, you might consider either re-factoring the query to allow the optimizer to make a better choice, or you could create a plan guide.

You may be interested in this question, and the answers about the differences in execution plans on two servers with different resources.

FYI for future visitors: the question states that most of the databases have been kept at compatibility level 100; this rules out an array of potential issues related to cardinality estimator changes in SQL Server 2014+.

1 - be aware, this is an unsupported DBCC command, and should only be used for testing purposes.


What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes? When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000).

Why does Oracle call this datatype VARCHAR2 and not just VARCHAR like other DBMSs?

It would seem that Oracle at one time had plans to give a different definition to VARCHAR than to VARCHAR2.  It has told customers this and recommends against using VARCHAR.  Whatever their plans were, as of 11.2.0.2 VARCHAR is identical to VARCHAR2.  Here is what the SQL Language Reference 11g Release 2 says:

  Do not use the VARCHAR data type. Use
  the VARCHAR2 data type instead.
  Although the VARCHAR data type is
  currently synonymous with VARCHAR2,
  the VARCHAR data type is scheduled to
  be redefined as a separate data type
  used for variable-length character
  strings compared with different
  comparison semantics.

The PL/SQL User's Guide and Reference 10g Release 2 puts it this way:

  Currently, VARCHAR is synonymous with
  VARCHAR2. However, in future releases
  of PL/SQL, to accommodate emerging SQL
  standards, VARCHAR might become a
  separate datatype with different
  comparison semantics. It is a good
  idea to use VARCHAR2 rather than
  VARCHAR.

The Database Concepts 10g Release 2 document says the same thing in stronger terms:

  The VARCHAR datatype is synonymous
  with the VARCHAR2 datatype. To avoid
  possible changes in behavior, always
  use the VARCHAR2 datatype to store
  variable-length character strings.

The Oracle 9.2 and 8.1.7 documentation say essentially the same thing, so even though Oracle continually discourages the use of VARCHAR, so far they haven't done anything to change it's parity with VARCHAR2.


Fill factor based on index ranges I'm designing a Postgres database for an events app.  The app lists events sorted by when they start.  Initially the app displays only 30 events.  As users scroll through the list of events, more events are fetched from the database.  In reduced form, the queries (depending on the direction in which the user is scrolling) are:

SELECT ?
FROM events
WHERE starts_at >= ?
ORDER BY starts_at
OFFSET ? LIMIT ?

SELECT ?
FROM events
WHERE starts_at < ?
ORDER BY starts_at DESC
OFFSET ? LIMIT ?

I plan on clustering the table on starts_at with a fill factor of about 70%, and expect I'll want to run the cluster command periodically to maintain performance.

Almost all of the events that users add will have a starts_at value in the future.  Therefore, although specifying a 70% fill factor for events which start in the future makes sense, using a 70% fill factor for events that started in the past seems like a waste of disk space.

Is there a way to have Postgres cluster the events table such that the fill factor for events where starts_at < CURRENT_TIMESTAMP is 99% while the fill factor for events where starts_at >= CURRENT_TIMESTAMP is 70%?

Unless you plan on regularly updating portions of your table, I'm curious why you would set your FILLFACTOR on the table to anything other than the default of 100%?

Regarding your question about different fillfactor settings for the same table, I don't think that is possible. If you were planning to set the fillfactor per index, then that might have some benefit -- but if you are only inserting then set the fillfactor to 100% (or close to it).


Data Warehouse: Remove legacy data after conversion into new system I have a Data Warehouse that pulls data from two locations, the first being our modern system, the second being our legacy system. The legacy system consists of 200 databases with identical schemas that are periodically and individually being converted into our modern system. The process of converting the legacy system will take several months, and our customer needs reporting on all the data from both modern and legacy systems during this time.

My question is this: Once we remove one of the 200 legacy databases and convert into our modern system, how can its data be easily removed from the data warehouse? I know that removing large amounts of data from a data warehouse can be cumbersome and require rebuilding of indexes, etc.

I would suggest dropping the database once it is no longer needed.

DROP DATABASE LegacyDatabase_0001;
DROP DATABASE LegacyDatabase_0123;

The database space is freed for the use of other expanding databases and/or for creating further new databases.

If you are concerned about data loss you could archive the LegacyDatabases to some backup location, a file share, tape backups, whatever your choice may be.


how to query multiple counts on related tables For each Forum, there are many Posts, and for each Post, there are many Comments

I'm trying to query the total number of Posts and Comments for each Forum

So I'm looking to have an output as such:

Forum name | post_count | comment_count
honda cars     256          489
toyota cars    53           230

Here are the tables

Forum
::Posts
  ::Comments

If your schema has a useful naming convention it could work like this:

SELECT f.forum, p.post_count, p.comment_count
FROM   forum f
LEFT   JOIN (
   SELECT forum_id, count(*) AS post_count, sum(c_ct) AS comment_count
   FROM   posts p
   LEFT   JOIN (
      SELECT post_id, count(*) AS c_ct
      FROM   comments
      GROUP  BY 1
      ) c USING (post_id)
   GROUP  BY 1
   ) p USING (forum_id);

LEFT JOIN is to include posts without comments and forums without posts.
If you had provided actual table definitions, I wouldn't need to speculate on column names.


Trigger function to update data from one table to another table with different servers in PostgreSQL There are two databases with different servers in PostgreSQL in PGAdmin.
One database db1 of server s1 and another database db2 of server s2.
The condition is: if the field id.table1 of db1 is same as id.table2 of db2 (of the other server) then one of the field 'status' of table2 of db2 which has its value as submit should changes to open in PostgreSQL in PGAdmin.
I need to write trigger function for this.

There are couple of ways to deal with this using triggers

FDW wrapper as stated by a_horse_with_no_name and use PLpgSQL
use an un-trusted PL language such as PLpython.

FDW: depending on the version of PG can be fast or extremely slow.

PLpython requires python language with psycopg library to be installed.

I like to use plpython as its easier to add logic if the connection between the two servers becomes broken,  When the connection is broken use another table to hold onto the pending changes,  then use PgAgent task to retry updating the data.


Spanish characters saved incorrect in Mysql I have googled this already, but I couldn't find what's wrong in my situation.

I have a wordpress site that has a lot of non-english users which type in words such as "español" it is printed correctly with php or sent via email, but when it's saved to the database, it appears as "espau00f1ol".

It's a MyISAM, mysql database with utf8mb4 unicode encoding. What do I need to change in order to be able to save non-English characters?

Check and log/dump the insert/update queries - this looks to me as if the php code somehow recodes your data. The 00f1 is the hexadecimal representation of the character ñ and in the form \u00f1 it might be used as an special/escape sequence (for example in JSON).


Good articles for SQL Server Health Check specifically for Web Applications I am searching for Database Health check articles specifically for a web application back-end server and SQL Server 2005/2008.

My current checklist includes:

- Index Fragmentation
- SQL Server logs
- Mirroring Monitor
- Backup completion
- File growth

What else should I look for? Are there good articles specifically for a webapp DBA scenario?

(Note: I don't need to worry about many database users - there is only one user the application itself)

The two I've found most useful:

Brent Ozar's Blitz! Script
Glenn Berry's DMV Diagnostic queries

Posting these as an answer since you haven't received any other responses. Also, not sure how long this question will live; it may be viewed by some as a shopping list request that will eventually be obsolete.


pg_dump: Show GRANT Statements only I executed this manually:

GRANT SELECT ON TABLE my_table TO some_user;

How can I dump this statement again?

I looked at pg_dumpd but only found a way to do a data-only dump or a schema-only dump.

If I do the following, then I get both (CREATE TABLE and GRANT statements)

pg_dump --schema-only -t my_table my_db

Is there a way to exclude the CREATE statements, and to show the GRANT statements only?

This should work,

pg_dump --section=pre-data -d myDb | grep -e '^\(GRANT\|REVOKE\)'


Calculating disk usage with INFORMATION_SCHEMA.TABLE versus file system? MySQL's database size can be calculated with

SELECT table_schema AS db_name, SUM(data_length + index_length) AS size
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema != 'INFORMATION_SCHEMA'
GROUP BY db_name

But you can also calculate the database size by looking at the disk-usage of the data-directory. Should these two numbers ever deviate? Is there ever a reason to use one of these methods of the other? By extension, how is INFORMATION_SCHEMA.TABLES's data_length and index_length calculated?

Well, it seems like there are two ways the deviate in performance,

Statistics when you query with innodb_stats_on_metadata (the default) you force the statistics to be updated (if it's not cached). You can turn this off, but doing so requires you make a GLOBAL change. From the docs:

  When innodb_stats_on_metadata is enabled, InnoDB updates non-persistent statistics when metadata statements such as SHOW TABLE STATUS or when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Leaving the setting disabled can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

  To change the setting, issue the statement SET GLOBAL innodb_stats_on_metadata=mode, where mode is either ON or OFF (or 1 or 0). Changing the setting requires privileges sufficient to set global system variables (see Section 5.1.9.1, “System Variable Privileges”) and immediately affects the operation of all connections.

Cache else it seems that the INFORMATION_SCHEMA is reading from a cache in Table_statistics::get_stat. That seems to be documented under information_schema_stats_expiry. But it also seems you can refresh the cache with ANALYZE table (which the docs mention), and the testing suite. So the cache can drift by that many seconds.

I don't see anything else documented or in the code that would leave me to believe there was a difference.


How does mysqldump take place when there are more than one database? I'm writing a shell script wherein I'm doing a mysqldump of all databases.

My questions are:

Are the tables of all databases in the server locked during the mysqldump?
If I have to do an incremental backup using mysqlbinlog from the last full backup, should I take the time when mysqldump started or when it ended?
What happens when one is using the database when mysqldump is running?
How can I take the start time for mysqlbinlog? What happens when mysqldump is executed?

Let me address each of your three Questions
QUESTION #1

Are the tables of all databases in the server locked during the mysqldump?

ANSWER TO QUESTION #1
Depends on the default settings you allow and what setting you use to override. The --opt parameter is enabled by default. That enables --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys.
If you have a database with all InnoDB tables or a mix of InnoDB tables and read-only MyISAM tables, you can just use --single-transaction. This will create a clean point-in-time snapshot of all tables involved in the mysqldump. Any INSERT/UPDATE/DELETE queries against an InnoDB table will not interfere with the point-in-time consistency of the mysqldump.
By the way, --single-transaction is mutually exclusive to --lock-all-tables. Thus, using --single-transaction will disable --lock-all-tables.
QUESTION #2

If I have to do an incremental backup using mysqlbinlog from the last full backup, should I take the time when mysqldump started or when it ended?

ANSWER TO QUESTION #2
You could take the time if you do mysqlbinlog at different times of the day. You could save yourself some off that housekeeping information by simply running FLUSH LOGS; or FLUSH BINARY LOGS; the same time every night. For example, you could script a cronjob to run FLUSH LOGS;at 11:59 PM like this
#!/bin/sh
sleep 55
mysql -uroot -p... -e"FLUSH LOGS;"

That way you can logically group together all binary logs that have the same date when formulating any needed incremental backup.
You do not have record times or positions in this respect: If you include the option --master-data=2 in the mysqldump, the log file and position at recorded as a comment on line 22 of the mysqldump output.
QUESTION #3

What happens when one is using the database when mysqldump is running? How can I take the start time for mysqlbinlog? What happens when mysqldump is executed?

ANSWER TO QUESTION #3
Here is where you have to be very careful. As I mentioned earlier, if you have a database with all InnoDB tables or a mix of InnoDB tables and read-only MyISAM tables, you can use --single-transaction and every table involved in the mysqldump will exist in the same point-in-time.
What Can Go Wrong #1
If you are performing INSERT/UPDATE/DELETE queries against a MyISAM table during the mysqldump, then the MyISAM tables will not be point-in-time consistent.
What Can Go Wrong #2
If you are performing ALTER TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE against any table (MyISAM or InnoDB), consistent mysqldumps are no longer isolated. The transaction you issued with --single-transaction is released (made null and void) and all tables afterward can no longer the point-in-time consistent.
If one of these occur, recording start times or position is rendered useless.


Initial Discovery process I am looking to better my knowledge without actually being in the field, I was wondering what initial steps you take when you are assuming the responsibility of a server with SQL-Server installed on it. There are a few basics like hardware inventory, and permissions, backups. But what else do you do?
I did find some decent infromation on a few forums such as...

1. Backups

Ensure all db's are being backed up(system plus user dbs)
Ensure transaction logs backups are implemented where needed (check DB Recovery Models)
Check whether backups are native backups or 3rd party(ex. Red-Gate SQLBackups or Quest LiteSpeed)
Ensure to test the db backups on a test server using restores

2. SQL Jobs / Replication / Reporting Services / Analysis Services / Integration Services

Check on all and ensure functional

3. Logs

Check SQL Server Error Logs, Windows Logs for issues

4. Security

Server-Level Security, Services Security, Logins/Users Security, Audit Security, File System Security

5. Baseline the System

Use tools like Performance Monitor, SQL Profiler, Active SPID Performance

6. Implement a Monitoring, Alerting & Performance Tool

www.sqlserverops.com, Microsoft Systems Center, www.idera.com, Quest, Red-Gate

7. Implement a Change Control System for all Production Changes

all changes go through a process(request ticket, change ticket, approvals, deployments)

That is a great list. I would also use Brent Ozar Unlimited sp_Blitz, http://www.brentozar.com/first-aid/sql-server-downloads/. It will give you great overview of what might not be setup quite right.


How to deploy case-insensitive embedded firebird database? I'm trying to find out how to create CI firebird embedded database. I've found (here) that there are some character sets each of them has one or many collations. So, UTF8 has UCS_BASIC, UNICODE and UNICODE_CI collations. From documentation I can set any collation for any column during table creation. My question is: Is it possible to set collation on whole database? Or I have to impose collation on all columns?

The syntax you're looking for is here

  CREATE {DATABASE | SCHEMA}    ...    [PAGE_SIZE [=] size]    ...
  [DEFAULT CHARACTER SET charset [COLLATION collation]]    ...
  [DIFFERENCE FILE 'filepath']

  size  ::=  4096 | 8192 | 16384

Also check out this page and this link from it specifically
about case insensitive searches and collations on columns.

The good news is that you can set it both by database and column.

Also, check this out - you can also use "shadow" columns if you
can't get exactly what you want "out of the box" - i.e. UPPER()
function.


How not to distribute reads in pgpool I want to use pgpool to replicate one database to another.  I want to do my processing on my master database (reads/writes/inserts/deletes), replicate that database to another database as it is modified and have other reads be directed to the replicated database.

As I read the pgpool documentation it appears that reads will be distributed across all databases in the cluster. Is there a way to not do that and direct one class of reads to one database and another class of read/writes to the master?

No, pgpool is an all or nothing solution.  All you can control is the precentage of read queries going to each host, not what kinds of (read) queries.


Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table I was trying to separate DB indexes from ibdata1 I took the steps in what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql

I dumped data, dropped databases, and started mysql service.

I got the following error and now I'm stuck:

mysql -u root -p < alldb.sql
mysql: unknown option '--innodb_file_per_table'

What is the solution now?

When I remove the below lines from my.cnf everything works fine:

[mysql]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Why innodb_file_per_table is an unknown option?

The weired thing is that I can set innodb_file_per_table dynamically and it separates .ibd file when I alter them to InnoDB engine.

EDIT:
It should be an unknown option as I've put it below [mysql] not [mysqld]. I went through the process again and now it works just fine. ibd files are in their respective database folder.

Phew...

Problem solved:

It should be an unknown option as I've put it below [mysql] not [mysqld]. I put all the parameters below [mysqld] and I went through the process again and now it works just fine. ibd files are in their respective database folder.


Posible to obtain history ledger(balance) with sql query? Not sure if this is the place to ask but here it goes. I am using a Postgres server and to make things simpler, I'll try to show a brief scenario of what I need.

CREATE TABLE operations(id,type,item,price,amount)
 AS VALUES
  ( 1::int, 'buy' , 'item1',  2, 10::int ),
  ( 2::int, 'buy' , 'item1',  3,  4::int ),
  ( 3::int, 'sell', 'item1',  4, 12::int )
;

If I go through a FIFO cost basis the ledger or balances after each order should look like:

id  |  price   | amount |

1   |      2   |     10 |

2   |      2   |     10 |
    |      3   |      4 |

3   |      3   |      2 |

One thing that I've tried is to have a table like operation_balances in which for each processed operation I insert the corresponding balance. This works however there's a problem with the huge amount of balances that get inserted for large number of operations so it's not a valid solution. I was wondering if it's possible to obtain the balance part for each order as part of a sql query. It may take a little while to run but it would save a lot of database space.

To start, and this is just to get you somewhere,

SELECT *,
  jsonb_agg(jsonb_build_object('p',price,'a',amount))
    FILTER (WHERE type='buy')
    OVER (PARTITION BY item ORDER BY id)
    AS cumpurchased
FROM operations;
 id | type | item  | price | amount |             cumpurchased
----+------+-------+-------+--------+---------------------------------------
  1 | buy  | item1 |     2 |     10 | [{"a": 10, "p": 2}]
  2 | buy  | item1 |     3 |      4 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]
  3 | sell | item1 |     4 |     12 | [{"a": 10, "p": 2}, {"a": 4, "p": 3}]

Here, we serialize the purchases into cumpurchased. This seems simple, now we can blow this up by expanding cumpurchased to the individual transactions.

SELECT id, type, item, price, amount, cumsale, e, eid
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
ORDER BY id, eid;
 id | type | item  | price | amount | cumsale |         e         | eid
----+------+-------+-------+--------+---------+-------------------+-----
  1 | buy  | item1 |     2 |     10 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 10, "p": 2} |   1
  2 | buy  | item1 |     3 |      4 |         | {"a": 4, "p": 3}  |   2
  3 | sell | item1 |     4 |     12 |      12 | {"a": 10, "p": 2} |   1
  3 | sell | item1 |     4 |     12 |      12 | {"a": 4, "p": 3}  |   2
(5 rows)

You should be ale to see where I'm going with this...

SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
  AS qty_remaining
FROM (
        SELECT *,
                sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
        jsonb_agg(jsonb_build_object('p',price,'a',amount))
                        FILTER (WHERE type='buy')
        OVER (PARTITION BY item ORDER BY id)
        AS cumpurchased
        FROM operations
) AS with_agg
CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid);
 id | type | item  |         e         | eid | qty_remaining
----+------+-------+-------------------+-----+---------------
  1 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 10, "p": 2} |   1 |            10
  2 | buy  | item1 | {"a": 4, "p": 3}  |   2 |            14
  3 | sell | item1 | {"a": 10, "p": 2} |   1 |            -2
  3 | sell | item1 | {"a": 4, "p": 3}  |   2 |             2
(5 rows)

From this point, you need to filter out the -2, we wrap the query again and put that conditional in there. Then we just select from that.

SELECT id, e->>'p' AS price, least(qty_remaining,(e->>'a')::int) AS amount
FROM (
      SELECT id,type,item,e,eid,sum(
        (e->>'a')::int
      ) OVER (PARTITION BY id ORDER BY eid)
        - coalesce(cumsale,0)
        AS qty_remaining
      FROM (
        SELECT *,
          sum(amount) FILTER (WHERE type='sell') OVER (PARTITION BY id) AS cumsale,
          jsonb_agg(jsonb_build_object('p',price,'a',amount))
            FILTER (WHERE type='buy')
            OVER (PARTITION BY item ORDER BY id)
            AS cumpurchased
        FROM operations
      ) AS with_agg
      CROSS JOIN LATERAL jsonb_array_elements(cumpurchased)
        WITH ORDINALITY
        AS jsonb(e,eid)
      ) AS t
WHERE qty_remaining > 0;
 id | price | amount
----+-------+--------
  1 | 2     |     10
  2 | 2     |     10
  2 | 3     |      4
  3 | 3     |      2
(4 rows)


How to use reference to another table in aggregate function I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write:

SELECT  [GroupId] = cr.[GroupId],
        [OnCreateCount] =  SUM(CASE WHEN cr.StatusOfRequest = 1 THEN 1 ELSE 0 END),
        [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END),
        [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND THEN 1 ELSE 0 END),
        [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END),
        [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END),
        [TotalCount] = COUNT(*)
FROM [FK].[CertificateRequest] cr
GROUP BY cr.[GroupId]

Here OnCreateCount and CreatedCount are the same, but it's wrong. CreatedCount is number of rows with status 1 which are referenced from table [Certificate]. OnCreate is number of rows with status 1 which are not referenced from table [Certificate]

Here is how I solved it:

SELECT  [GroupId] = cr.[GroupId],
        [OnCreateCount] =  SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NULL THEN 1 ELSE 0 END),
        [NewCount] = SUM(CASE WHEN cr.StatusOfRequest = 2 THEN 1 ELSE 0 END),
        [CreatedCount] = SUM(CASE WHEN cr.StatusOfRequest = 1 AND c.ID IS NOT NULL THEN 1 ELSE 0 END),
        [ReviewCount] = SUM(CASE WHEN cr.StatusOfRequest = 3 THEN 1 ELSE 0 END),
        [RejectedCount] = SUM(CASE WHEN cr.StatusOfRequest = 4 THEN 1 ELSE 0 END),
        [TotalCount] = COUNT(*)

FROM [FK].[CertificateRequest] cr
OUTER APPLY
(
  SELECT TOP 1 *
  FROM [FK].[Certificate] c
  WHERE c.CertificateRequestID = cr.ID
) c
GROUP BY cr.[GroupId]

But I really don't like OUTER APPLY here which is used just to determine whenever there is any row in referenced table.

What is the easiest way to get required result?

The OUTER APPLY is probably not too bad, but if you did want to replace it with something else here is another option:

With cte As (
    Select GroupId,
        StatusOfRequest,
        HasCertificate = iif(Exists (Select 1
                                       From FK.Certificate As c
                                       Where c.CertificateRequestID = cr.ID), 1, 0)
      From FK.CertificateRequest As cr)
Select GroupID,
    OnCreateCount = SUM(CASE WHEN StatusOfRequest = 1 AND HasCertificate = 0 THEN 1 END),
    ...
    NewCount = SUM(CASE WHEN StatusOfRequest = 1 AND HasCertificate = 1 THEN 1 END),
    ...
  From cte
  Group By GroupID;

This assumes you have a version of SQL Server which supports iif and common table expressions. If not, easy to translate to CASE and subquery respectively.


How do I determine which IP I should use for my MariaDB Galera Cluster? I have set up MariaDB Galera Cluster (let's say 3-4 nodes).

Which IP should I specify in the configuration of my application?

Should I specify all of them or I should have some proxy in front of the Cluster so the app can see the cluster as one?

Can I have the cluster without a proxy and still see it in my app as one node not as a cluster?

First, you should have 3 nodes in your cluster (or an odd number), not "3-4", so to always know which is the majority group and avoid the split brain problem.

You can specify any of the three node IPs as the database is replicated in all nodes.  However, to make the system resilient you should have a Virtual IP address that allows you to reach any of the nodes.

In my system, I have a S9S ClusterControl with two HAProxy load balancers associated to the same Virtual IP address.  This VIP is the address that clients use to contact the cluster.


Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version? I am currently working on a project that needs to operate in different SQL environments. Most of my developers have SQL Server 2017. The application runs in SQL Azure, and the client has recently started running some environments using SQL Server 2016. The project is using SSDT to generate Dacpac files which works fine for development purposes, however we recently ran into a case where a developer used a system function that was introduced in 2017. The stored procedure scripts that referenced this function worked fine in our dev and Azure environments and failed in the 2016 environment.

Is there an easy way to validate a script against some arbitrary SQL Server version, without requiring us to create multiple test environments for each SQL Server version we wish to support? The method does not need to be 100% foolproof, but should catch the vast majority of potential compatibility issues.

There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for anything, and you'll need to do further research to cover changes to undocumented functions.

  without requiring us to create multiple test environments for each SQL Server version we wish to support?

Would it not be acceptable to test against the minimum version you need to support, rather than testing against every version from that onwards? That would only be one extra instance you need to add to your CI/Test/other processes. If 2016sp1 is the earliest version you need to support then almost everything is available in all editions so you can use Express and not even have any licensing costs for that one instance. You might get away with that with prior versions too, if you don't use any features that were standard-/enterprise- only prior to 2016sp1.


How to get a list of global temporary tables in the current database? I am trying to get a list of the global temporary tables that are currently active. There seems to be an issue with them bumping in to each other (same name). As the 'acting' dba, trying to sort out a mess here.

For SQL Server 2005+, all global temp tables (tables that are created with a ## prefix) will be found in tempdb.  This should work for you:

SELECT * FROM tempdb.sys.tables
WHERE name LIKE '##%'


Speeding up very slow UPDATE query (Postgresql 9.6) In a app called Links, users post interesting content they've discovered recently and others can comment on these posts.

There's also an additional functionality, one that allows users to leave a comment on a comment.

All comments (and comments on comments) are saved in a table called links_publicreply in my postgresql 9.6.5 DB. This table contains a self-referential foreign key to support comments on comments. It's a big table, with nearly 200M rows.

One UPDATE query on the links_publicreply table is consistently showing up in slow_log. It's taking longer than 5000ms, and is ~100X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

See the the explain analyze results: https://explain.depesz.com/s/G8wX

According to that, the Seq Scan ends up filtering 47,535,365 rows and is the source of slowness.

What do I do to reduce this exorbitant execution time?

Being an accidental DBA, I'm not an expert on the subject. My gut feel is that I'm filtering on the self-referential foreign key, so that should already be an index (thus optimized for lookups)? I'm a bit stumped here.

Addition:

Here's the entire output for \d links_publicreply:

            Column             |           Type           |                           Modifiers
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     |
 direct_reply_tgt_text_prefix  | text                     |
 direct_reply_tgt_text_postfix | text                     |
 direct_reply_id               | integer                  |
 level                         | integer                  |
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

You should create a B-tree index:

CREATE INDEX ON links_publicreply (direct_reply_id);

Note: your index links_publicreply_answer_to_id is useless and should be dropped.


Database running out of space My database has 16MB of space left.

I used to just truncate as I was taught but I found these links that advise against truncating:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/

Is there anything else I can do on my database to reduce the size other than deleting table records? I am new to the DBA forum and I probably should have looked around for other questions before posting but I am desperate as I am worried about my database going down.

If your database has 16 MB of free space, and the drive it's on has plenty of free space, then don't worry about it. As long as there is sufficient space and you haven't disabled the database's ability to autogrow, then the database will grow the data file when it needs to. Of course you should set your data file's autogrow to some realistic increment, balancing duration it takes to grow with the frequency it will have to grow. The defaults of 1MB or 10% are not realistic at all, and should have been changed ages ago. If you have instant file initialization enabled you can lean towards larger, but I have no idea what a good balance for you might be.

You can check autogrow settings here:

SELECT name, type_desc, size, growth, is_percent_growth
  FROM [database_name].sys.database_files;

Ideally you will create the database with sufficient size to avoid autogrowth at all, since this operation can be expensive (especially if you don't have instant file initialization) and can block all other activity for the duration of the growth event. You may want to manually expand the file now or during a maintenance window to avoid this happening during peak activity. So proactively you can do this (e.g. to change the auto growth setting to 100 MB):

ALTER DATABASE [database_name]
  MODIFY FILE (NAME = N'database_name_data', FILEGROWTH = 102400KB);

If your drive has 16 MB of free space, then you need to free up some space somehow, or move the database to a drive that has more space. It's possible that the drive has been taken up by an overgrown log file, probably because you're in full recovery model and have never taken a log backup. In this case you can either:

Switch to simple recovery mode, run CHECKPOINT, and then DBCC SHRINKFILE to make the log a reasonable size. Resist the temptation to shrink the file to 1 MB; you don't want to get into a tug-of-war with your disk space.
Stay in full, then BACKUP LOG, and then DBCC SHRINKFILE with the same caveats as (1).

Now, it could also be that you're in simple mode and the log has ballooned to support some atypically large transaction. So you may still be able to do (1) without any worry, but if you're going to be running these large transactions again, or not doing (1) or (2) regularly, then you're only "solving" the problem temporarily. Get a bigger disk or use full recovery model and manage your log much more effectively.

That all said, this is just a guess; maybe your drive is actually being taken up by a large (and useless) page file, or your MP3 collection, or videos from your last vacation.


Select Into removes IDENTITY property from target table I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement.

  source table name: Sales.Orders
  column name: orderid
  column property: int type, not null, defined as a primary key, identity property is defined on the column)
  Column name: custid
  column property: int type, foreign key, null
  target table name: Sales.MyOrders

In the 70-461 exam book - Querying Microsoft SQL Server 2012, chapter 10 p336:

it gives code

ISNULL (orderid + 0, -1) as orderid,
--this will remove IDENTITY property and make column not null

Make custid column not null in target able

ISNULL (custid, -1) as custid, --make column not null

I don't understand these two code items, though it works.

Could someone tell me the concepts behind the code?

The code provided in the book:

select isnull (orderid + 0, -1) as orderid,
isnull (custid, -1) as custid,
empid,
isnull (cast(orderdate as date), '19000101') as orderdate,
shipcountry,
freight
into Sales.MyOrders
from Sales.Orders
where shipcountry = N'Norway';

The behaviors you're curious about are explained in the documentation:

  When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
  ...- The identity column is part of an expression.
  ...If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.

And in this thread from UC:

  Metadata is determined based on the source column and expressions used
  in the SELECT list. Below are the rules:

  Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the
  engine. So if you use CAST(somecol as char(8)) then the expression is NULLable.
  ...
  So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.


How to update JSON using logic equivalent to WHERE clause My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a specific key-value pair, and then update the value.

Here is a mock-up of my JSON structure using dummy values:

declare @data nvarchar(max);
set @data = N'{"add":[{"id":-1,"valA":7,"valB":8,"valC":"text","valD":{"3":"text"}},'
+ '{"id":-2,"valA":5,"valB":8,"valC":"Moretext","valD":{"2":"textB","3":"Moretextagain"}}'
+ '{"id":-1,"valA":9,"valB":8,"valC":"SecondCase","valD":{"2":"textX","3":"MoretextagainSigh"}}'
+ ']}';
select * from openjson(@data);

Basically the problem is: if you can find "id":-1 then update it to "id":999.

(The use case is that the data here are linked to other data and none of it has been persisted. The other data is identified by the negative ID values. After persisting the other data I get a "real" ID back from the database. I now need to update this linked data to remove the negative ID and replace it with the one that the database provided.)

From the other question I can see one option is to extract all of this into a table, perform the update and rebuild the JSON. I feel the structure is non-trivial (for example, the object for valC may have different numbers of elements). It's simple, yes, but outputting all this to a table then rebuilding it - I think is not simple.

I really want this pseudocode to work:

update @data
set 'add.id' = 999
where 'add.id' = -1;

Following is a worked example.

Caveat: the final result is a JSON fragment which is the content of the '$.add' array, rather than the original @data variable containing that array - but the logic is good for solving the problem

declare @data nvarchar(max);
set @data = N'{"add":[{"id":-1,"valA":7,"valB":8,"valC":"text","valD":{"3":"text"}},'
+ '{"id":-2,"valA":5,"valB":8,"valC":"Moretext","valD":{"2":"textB","3":"Moretextagain"}},'
+ '{"id":-1,"valA":9,"valB":8,"valC":"SecondCase","valD":{"2":"textX","3":"MoretextagainSigh"}}'
+ ']}';

select * from openjson(@data);

declare @idToFind int = -1;
declare @newId int = 999;
declare @arrayElement nvarchar(max);
declare @tempSQL nvarchar(max);
declare @add nvarchar(max);
declare @nDoublequote nvarchar(1) = cast(char(39) as nvarchar(1)); -- double quote character as an nvarchar

select @add = json_query(@data, '$.add');

-- Loop through the elements in the '$.add' array that have an id value we are interested in
declare cur_json cursor local fast_forward for select cast([key] as nvarchar(16)) from openjson(@add) where (select json_value([value],'$.id')) = @idToFind;
open cur_json;
fetch next from cur_json into @arrayElement;

    while @@fetch_status = 0
        begin

            -- an element in the '$.add' array has an id that needs updating

            set @tempSQL = concat(N'select @add = json_modify(@add, ' , @nDoublequote , N'$[' , @arrayElement , N'].id' , @nDoublequote , N', ' , @nDoublequote , cast(@newId as nvarchar(16)) , @nDoublequote, N')');
            select ' tempSQL: '+isNull(@tempSQL,'<null>')+'. '; -- just for debugging purposes
            exec sp_executeSQL @tempSQL, N'@add NVARCHAR(MAX) OUTPUT', @add = @add OUTPUT

            fetch next from cur_json INTO @arrayElement; -- check for other array elements having the same id value
        end

close cur_json;
deallocate cur_json;

select * from openjson(@add);

The key to this solution is the cursor which iterates through the array but selects only elements that match on our required id value (ie. the WHERE part of my question). This cursor actually only returns the array's [key] value - which is, in other words, the matching element's array index.

The second part of this solution is to build a string containing the SQL that uses json_modify to modify the value, referencing that value using the array index we just obtained from the cursor, and then using exec sp_executeSQL to effect that.

I'll be honest, I'm not 100% sure why the parameters are required with that procedure - I've taken this part of the solution from yet another question.

This approach -

having the sought and replacement values in parameters
using a cursor to obtain the array indices for matching elements
then building dynamic SQL to effect the changes

is reproducible and actually fairly simple once you strip away the comments and excess select statements that illustrate the solution.


Is it possible to find the values of MySQL variables if I do not have SELECT privileges on mysql.user? I am getting a 'msyql server has gone away' error sometimes when doing a large binary insert, so I wanted to check the settings on mysql, things like wait_timeout and max_allowed_packet. However

show create table mysql.user

returns

[Err] 1142 - SELECT command denied to user '557574_prod'@'96.25.95.162' for table 'user'

I'd rather not hit up tech support whenever I want to know some of the relevant settings, so I was wondering if there might be a workaround?

You could do things like these:

SHOW VARIABLES;

SHOW GRANTS;

Show status;


SQL Server 2017 Linux CU1 - MODIFY FILE encountered operating system error 31 I have a "VMWare RHEL 7.4" machine that I have installed "SQL Server 2017 Linux CU1" and I created a "Linux Linear Volume", see steps at the bottom, when I try to restore a database backup in the "Linear Volume" I got the error below.

/*
Msg 5149, Level 16, State 3, Line 6
MODIFY FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to expand the physical file '/sqldata/mssql_data/defense/defense_Data_01.MDF'.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
*/

I was able to restore the database backup on "/var/opt/mssql/data" without any problem, then I detached and moved the database to the "Linear Volume" and I was able to attach the database just fine, however any operation that needs to extend a datafile fails with the same error message.

I have setup "Linux Linear Volumes" the same way for Oracle and PostgreSQL databases and it has always worked fine with those.

Have you seen this problem? Is this a bug with "SQL Server 2017 Linux CU1" ?

Create Linux Linear Volume

vmware add /dev/sdc disk 100gb
Partition /dev/sdc and create /dev/sdc1 partition of 100gb
fdisk /dev/sdc
Create Volume Group
vgcreate vgsqldata /dev/sdc1
vgscan
vgdisplay vgsqldata
Logical Volume
lvcreate -l 25599 vgsqldata -n lvsqldata
lvdisplay -v /dev/vgsqldata/lvsqldata
Format the volume
mkfs.ext3 /dev/vgsqldata/lvsqldata
Mount
mkdir /sqldata
mount -t ext3 /dev/vgsqldata/lvsqldata /sqldata
df -kh
touch /sqldata/test.txt
ls -la /sqldata
rm -rf /sqldata/test.txt
Persist the mount
vi /etc/fstab
--mount logical volume
/dev/vgsqldata/lvsqldata  /sqldata             ext3    defaults        1 1
Change Owner
chown -R mssql:mssql /sqldata
ls -la /
"drwxr-xr-x.   5 mssql mssql  4096 Nov  7 10:43 sqldata"

The reason we use "Linux Linear Volumes" is because it is easier to add more space later, just need to add another vmware disk, partition the disk and add the new disk to the Linear Volume.

Increase Linux Linear Volume

vmware add /dev/sdd disk 100gb
Partition /dev/sdd and create /dev/sdd1 partition of 100gb
fdisk /dev/sdd
Extend Volume Group
vgextend vgsqldata /dev/sdd1
Extend Logical Volume
lvextend -l +100%FREE /vgsqldata/lvsqldata

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html-single/logical_volume_manager_administration/

I appreciate any feedback on this problem.

Thanks,

Marcelo Marques
Senior Technical Manager, OCP
www.esri.com

I figure out the issue.

Problem:  EXT3 files system is not supported.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup
File System         XFS or EXT4 (other file systems, such as BTRFS, are unsupported

Solution: Create an EXT4 file system.

Comment the line in /etc/fstab that mounts the ext3 filesystem

vi /etc/fstab

-mount logical volume

"#/dev/vgsqldata/lvsqldata  /sqldata             ext3    defaults        1 1"

:wq!

Reboot the server

reboot

Check the file system is not mounted

df -kh

Check the linear volume

vgscan

vgdisplay vgsqldata

lvdisplay -v /dev/vgsqldata/lvsqldata

Format the volume as ext4

mkfs.ext4 /dev/vgsqldata/lvsqldata

Mount

mkdir /sqldata

mount -t ext4 /dev/vgsqldata/lvsqldata /sqldata

df -kh

touch /sqldata/test.txt

ls -la /sqldata

rm -rf /sqldata/test.txt

Persist the mount

vi /etc/fstab

-mount logical volume

/dev/vgsqldata/lvsqldata  /sqldata             ext4    defaults        1 1

Change Owner

chown -R mssql:mssql /sqldata

ls -la /

"drwxr-xr-x.   5 mssql mssql  4096 Nov  7 10:43 sqldata"


PAD_INDEX and FILLFACTOR on clustered Identity index I am trying to work out what setting for PAD_INDEX I should use for a clustered index on a IDENTITY column, the table is rarely going to be updated after first insert so I am looking for the setting that will result in the most of the space in the pages being used.

However I have found some documentation on the MSDN that seems to conflict with each other (or at least confuse me enough to ask for clarification).

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

  PAD_INDEX = { ON | OFF } When ON, the percentage of free space
  specified by FILLFACTOR is applied to the intermediate level pages of
  the index. When OFF or a FILLFACTOR value is not specified, the
  intermediate level pages are filled to near capacity leaving enough
  space for at least one row of the maximum size the index can have,
  considering the set of keys on the intermediate pages. The default is
  OFF.

And from: http://msdn.microsoft.com/en-us/library/ms177459.aspx

  The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Given the clause When a FILLFACTOR value is not specified, does that mean that the default FILLFACTOR of 0 is ignored regardless of the setting of PAD_INDEX?

Following on from that does that mean that I need to specify both PAD_INDEX = ON and FILLFACTOR = 100 on my identity clustered index to get maximum page usage?

PAD_INDEX only applies to non leave level pages. By default those are filled full.

So specifying PAD_INDEX=ON together with FILLFACTOR is only useful when you specify a fillfactor smaller then 100%.

In your case, specifying PAD_INDEX is not necessary, since by default your leave pages are how you want them. Full.


Write commit in replica set MongoDB Suppose We have a replica set with 1 primary and 2 secondaries.

During write operation on Primary, will write commit happen at once upon finishing write operation on primary and before spreading changes to secondaries? Or it will happen once all changes will be apllied on secondaries?

This can be specified for every write operation:

From the docs about Verify Write Operations to Replica Sets:

  For a replica set, the default write concern requests acknowledgement only from the primary. You can, however, override this default write concern, such as to confirm write operations on a specified number of the replica set members.

  [...]

  To override the default write concern, specify a write concern with each write operation. For example, the following method includes a write concern that specifies that the method return only after the write propagates to the primary and at least one secondary or the method times out after 5 seconds.

db.products.insert(
   { item: "envelopes", qty : 100, type: "Clasp" },
   { writeConcern: { w: 2, wtimeout: 5000 } }
)


Changing Domain on SQL Server I currently have some SQL Servers that sit on a sub-domain. We are eliminating that sub-domain and moving everything to the regular domain.

As far as SQL Server configuration goes, is there anything I need to change aside from potentially using different service accounts?

Note this is simply a domain change - the server name should be the same.

We did the same thing a while ago. The change went smoothly, we just had to change the various service accounts. We stopped all services before the move, then changed the accounts, then started everything without a hitch.