How does MySQL still function even though I move .ibd file to arbitrary places? I have a table Person. AFAIU, the data for this table is stored in /usr/local/mysql/data/my_database/person.ibd.

Now, I make an insert. Then I check out the contents of person.ibd with vim. The data I have just entered is indeed there.

Then, I move the person.ibd to my home directory. I again make an insert and check out the contents of person.ibd with vim. The new data is inserted into the file even though the file is now in my home directory, instead of being at /usr/local/mysql/data/my_database/person.ibd.

I repeat this action by moving the person.ibd to various other places, such as to /etc/. The new data is always inserted.

So, what's going on? How does this happen? Does MySQL search the whole file system before writing the insert to a file in the file system?

Furthermore:

I open the person.ibd with vim and edit a varchar type field of a record and save the file. Then I run a select but MySQL sees the old data. I exit from MySQL and enter again, but MySQL still sees the old data even though that data does not exist anymore in person.ibd and it is replaced by the new content which I have edited into the person.ibd file using vim.

So again, what's going on?

Do NOT edit a .ibd file!  Things are likely to go wrong:

The layout of the file is complex, and you are likely to make it unreadable by MySQL.
The data is probably cached in RAM, so your changes are not immediately recognized and/or they will be overwritten when MySQL decides to rewrite the 16KB block you modified.
There could be a block split, further rendering your edits a mess.

As for "Moving" a file, let me give you a basic lesson in the *nix filesystem.  A file is (in simplistic terms) two things:  A chunk of data sitting somewhere on disk, and a directory entry pointing to that chunk of data.  When a "file" is "opened" (which MySQL did long ago), it grabs the pointer to the chunk of data, but does not look at the directory entry again.  You did mv, which only removes one directory entry and creates a new entry.  This new entry points to the same chunk of data on disk.

So, until, MySQL closes the file, it still has the file, regardless of what path(s) it is known by.

Note that that explains how "hard" links can work trivially.  A second reference (hard link) to the same file is simply a different directory entry pointing to the same chunk of data.

Please study the structure of *nix filesystem.  Your incomplete knowledge is rapidly propelling you into disaster!

By the way, if you leave the .ibd in its new location. MySQL won't find it after it restarts.  You will have "lost" that table, and have a repair situation that could be baffling.


Is there a way to get index fragmentation without using dm_db_index_physical_stats? I am using SQL Server 2016 SP1 standard edition.

The view dm_db_index_physical_stats is very slow on big databases. Even if I specify the 'LIMITED' option.

I use the view to get index fragmentation, to determine if I should do a REBUILD or a REORG in my maintenance. But as the databases grows, the call to the DMV dm_db_index_physical_stats takes more and more time. On some database, which can be as big as 1TB, it can take an hour to query the DMV. (I have no down time, there is always clients connected to the database doing work.)

So I was wondering if there's a way to know index fragmentation without querying the DMV.

I'll just resume the great suggestions that were made in the comments section :
The short answer is No, you have to use the DMV.

One possible alternative to using the DMV on the whole database would be to do it by table, but I need to do it for the whole database, and I already split index maintenance on multiple days so that it fits as much as possible in the maintenance window.

Another alternative would be to restore the database on another server and gather the index fragmentation over there.

I can also try to see if there's some unused indexes and see if I can remove some. I've found this query very use full, if it can serve someone else.
If you see indexes with no reads and only writes, it means that the index is only used when you insert rows in the table, but never in the SELECT queries. You can then analyse and decide if it's usefull and should be kept.

SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
            AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count]
FROM    sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) AS ddios
  ON ddius.[index_id] = ddios.[index_id]
  AND ddius.[object_id] = ddios.[object_id]
  AND SP.[partition_number] = ddios.[partition_number]
  AND ddius.[database_id] = ddios.[database_id]
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
      AND ddius.[index_id] > 0
      AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name ]


Debit Credit Balance calculating from previous records with Pagination MYSQL I have table with the following schema and demo data-
id  date        particulars          debit  credit
104 29-12-2020  Crediting            NULL   1000
105 29-12-2020  Purchased Item:A     2000   NULL
106 29-12-2020  Purchased Item:B     200    NULL
107 29-12-2020  Purchased Item:C     1000   NULL
108 29-12-2020  Credited             NULL   1200
109 29-12-2020  Credited             NULL   500
110 29-12-2020  Credited             NULL   2100
111 29-12-2020  Credited             NULL   1300
112 01-01-2021  Purchased Item:Q     200    NULL
113 02-01-2021  Purchased Item:A     2300   NULL

I want to calcualte running balance along with filters like from this date to this and pagination included.
So far by referring this Answer
I found the satisfied results, only the problem lies with WHERE clause.
Query i have used =
SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance`
FROM billing_ledger  ORDER BY id asc

when i use this query im not getting expected balance as it should be
SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance`
FROM billing_ledger where date >= '2021-01-01' ORDER BY id asc

i want this second query to also consider the above rows but to give me results for the following dates with where clause and also with LIMIT
Here is the fiddle: FiddleLink
In fiddle, we can see that the second query is not considering the rows above, and thus the balance is not correct as it is in the 1st query.

As you are using the user-defined variables to iterate and so calculating the balnace, you have to set the initial variable ti the right amount

SET @variable = 0;
SELECT
-SUM(debit)  + SUM(credit) INTO @variable
FROM billing_ledger where date < '2021-01-01';
SELECT id, date, particulars, debit, credit
, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance`
FROM billing_ledger where date >= '2021-01-01' ORDER BY id asc ;

✓

✓

 id | date       | particulars           | debit | credit | balance
--: | :--------- | :-------------------- | ----: | -----: | ------:
112 | 2021-01-01 | Purchased Item: Eggs  |   200 |   null |    2700
113 | 2021-01-02 | Purchased Item: Onion |  2300 |   null |     400


How to quantify rollback events in SQL Server between 2 dates I need to quantify rollback events and find theirs reasons, without change the application code. There is any way to collect this data?

The reason to check the rollback occurrences have need to identify whether the change of configuration in my jboss datasource from local-tx transaction system to no-tx will impact the batch executions

As originally supplied by Mark Sinkinson in a question comment, the answer was found in:

Identifying rolled back transactions by SQLscope

From that link:

  In SQL 2008 R2 and before there are very few ways to identify transactions that have been rolled back. A server side trace capturing the SQLTransaction events is one way. You’d filter on EventSubClass = 2 to capture just the rollbacks. Or you could try reading the transaction log using fn_dblog() filtering on Operation = LOP_ABORT_XACT. Yet another way, which provides very limited data, is to periodically query the syscacheobjects sql column for any text which is like ‘%rollback%’. Each of these methods has their own issues and overheads. Ideally we’d like a light-weight solution.

  Fortunately, in SQL Server 2012 extended events comes to the rescue. You can either use extended events to map to a trace definition for SQLTransaction events or you could roll your own event session. Here’s one possible roll your own solution:

-- Create the event session to catch rollbacks

CREATE EVENT SESSION evtsRollback
ON SERVER
ADD EVENT sqlserver.sql_transaction (
  ACTION (sqlserver.session_id,sqlserver.database_id,sqlserver.sql_text)    -- Could add more actions
  WHERE transaction_state = 2                        -- Rollbacks only
  AND sqlserver.database_id = 8                        -- Just this one database
)
ADD TARGET package0.ring_buffer;
GO

-- Start the sesssion
ALTER EVENT SESSION evtsRollback ON SERVER STATE=START;

-- Begin a transaction, make a change, then rollback the transaction

-- Drop the event
ALTER EVENT SESSION evtsRollback ON SERVER DROP EVENT sqlserver.sql_transaction;

-- Query the results
SELECT
  event.value('(event/@name)[1]','varchar(50)') AS event,
  DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event.value('(event/@timestamp)[1]','datetime2')) AS [timestamp],
  event.value('(event/action[@name="session_id"])[1]','int') AS session_id,
  event.value('(event/action[@name="database_id"])[1]','int') AS database_id,
  event.value('(event/data[@name="duration"])[1]','bigint') AS duration_microseconds,
  event.value('(event/data[@name="transaction_id"])[1]','bigint') AS transaction_id,
  event.value('(event/data[@name="transaction_state"]/text)[1]','nvarchar(max)') AS transaction_state,
  event.value('(event/data[@name="transaction_type"]/text)[1]','nvarchar(max)') AS transaction_type,
  event.value('(event/action[@name="sql_text"])[1]','nvarchar(max)') AS sql_text
FROM
(
  SELECT n.query('.') AS event
  FROM
  (
    SELECT CAST(target_data as XML) AS target_data
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
    ON s.address = t.event_session_address
    WHERE s.name = 'evtsRollback'
    AND t.target_name = 'ring_buffer'
  ) AS s
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(n)
) AS t;

-- Tidy up
DROP EVENT SESSION evtsRollback ON SERVER;


How to clear pg_xlog directory? I have found myself in a situation where the pg_xlog contains 60GB of data due to a wrong configuration option set.

I had setup the wal_level, archive_mode=on and archive_command for barman which then got uninstalled. Can I simply comment these options as per default postgresql.conf config file and delete the files in the pg_xlog directory?

What is the correct procedure to remove these files in order to avoid database corruption?

Managed to solve it quite simply. Stopped the db, commented the options in postgresql.conf and restarted. PostgreSQL took care of deleting everything automatically.


MySQL 5.7 performance issue with 300 GB Innodb We have a database running on MysSQL 5.7. The database size is around 300 GB, running on a dedicated Linux server (RHEL 6) with 144 GB RAM, 16 CPUs and 15 GB swap size.

The server is busy throughout the day with a minimum of 50 connections. Most of the queries have indexes and are optimized once weekly. But we are still facing a performance issue.

So requesting a review of the my.cnf configuration and please suggest any changes.

my.cnf

[mysqld]
basedir=/usr
datadir=/sql/mysql/data57
socket=/sql/mysql/data/mysql.sock
skip-external-locking
key_buffer_size = 4000M
max_allowed_packet = 5120M
table_open_cache = 4000
sort_buffer_size = 128M
read_buffer_size =8M
join_buffer_size=128M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 100
query_cache_size=0
query_cache_limit=4M
query_cache_type="ON"
query_cache_min_res_unit=20K
query_prealloc_size=40K
query_alloc_block_size=40K
max_connections=300
sql_mode = ""
interactive_timeout = 28800
wait_timeout = 7200
connect_timeout = 60
default_password_lifetime=0
old_passwords=2
lower_case_table_names=1
tmpdir=/tmpfs
tmp_table_size=20G
max_heap_table_size=170M
innodb_buffer_pool_size=100G
innodb_buffer_pool_instances=16
innodb_buffer_pool_chunk_size=6562M
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_log_file_size=2G
innodb_lock_wait_timeout=180
net_buffer_length=8K
transaction-isolation=READ-COMMITTED
innodb_lock_wait_timeout=180
federated

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/sql/mysql/data/mysql.sock

[mysql.server]
user=mysql
basedir=/usr
log-bin=mysql-bin
binlog_format=mixed
server-id=1

[mysqldump]
quick
max_allowed_packet=1G

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size=1024M
sort_buffer_size=256K
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout

Query

 SELECT year_no              AS "Year",
       month_no             AS "Month",
       area                 AS "Area",
       hq                   AS "Headquarters",
       region               AS "Region",
       depot                AS "Depot Name",
       aw                   AS "Dist Name",
       ss_channel           AS "Channel",
       sales.classification AS "Classification",
       sales.beat           AS "Beat",
       sales.salesman       AS "Salesman",
       sales.cust           AS "Retailer UID",
       Sum(qty)             AS "Qty",
       Sum(amt)             AS "Amount"
FROM   sales
       JOIN date_tbl
         ON dt = sales.dt
       JOIN or_areas
         ON area = sales.area
       JOIN hq
         ON hq = sales.hq
       JOIN or_regions
         ON region = sales.region
       JOIN depot
         ON depot = sales.depot
       JOIN or_aws
         ON aw = sales.aw
       JOIN prods
         ON sku = sales.sku
       JOIN prod_grp
         ON product_group = sales.product_group
       JOIN brand
         ON brand = sales.brand
       JOIN dsm
         ON ( salesman = sales.salesman )
       JOIN cust
         ON retailer = sales.retailer
WHERE  ss_inv_status IN ( 1, 3, -3 )
       AND ss_cust_name IS NOT NULL
       AND cal_date BETWEEN ? AND ?
       AND sales.zone IN ( ? )
       AND sales.region IN ( ? )
       AND sales.depot IN ( ? )
       AND sales.zone IN ( ? )
       AND sales.distributor_division = ?
       AND sales.brand IN ( ? )
GROUP  BY year_no,
          month_no,
          aw,
          sales.cust,
          prod,
          cal_date;

The ? are place holders and the values are entered as inputs.

Explain

More information

The performance problem is very big queries, which take 30 mins to execute.
These are report queries which are generated on daily basis.
It is not a DW system.

I think you have found an example of "over-normalization" and why it is bad to "normalize continuous values" such as dates.

I recommend you abandon date_tbl and pull a date into sales.  Also get rid of separate columns for year_no and month_no; instead compute them as needed.

Once you have done that, some of these may be useful:

INDEX(cal_date)
INDEX(brand, cal_date)
INDEX(zone, cal_date)

It depends on what ? are likely to be.  If, say, brand is frequently a single item, then (brand, cal_date) is likely to be the most useful.

Have you measured any improvement after OPTIMIZE TABLE?  I doubt it.


Unknown _TransactionIndex table in Azure SQL Database Just noticed today that there is a random table in my Azure SQL Database that is named "_TransactionIndex_{guid}"

It has 1 column, Id, that contains 8 GUIDs.

Couldn't find any information about this table - what is it?  Does it mean that I have an issue with transactions within my application?

It's an internal table that is used during the import of a bacpac. If all is well it should be gone when the import is done. If it remains in the database the import wasn't completed successfully.


Mssql database old bakup restored on production server I am using mssql server 2016, I have accidentally restored the 24 hours old database backup file on production server, so I have lost the data for today, means I have lost the data of 24 hours, is there any chance I can get back my original database so that I can bring back the production database to original state, means before restoring the old database on production server.

is there any chance I can get back my original database so that I can bring back the production database to original state

There is NO possibility to do the UNDO/ROLLBACK action from the restore, you got only way to find recent backups that performed before restore operation.

Seems it's been more than 10 hours, if the database not in Full recovery mode and No Log backups, the translations whatever been posted during this 10 hours and happening now would be lost, i think you need quick action plan on this, without wasting time to find UNDO/ROLLBACK methods try to recover from recent backups by taking current state offline to avoid further lose.


how to get a better execution plan for empty sets? I have a query I'm trying to optimize, something like this:

SELECT 1 FROM HugeView
WHERE (Col1 = 'a' AND @val = 1) OR (Col2 = 'a' AND @Val = 0)

(where Col1 and Col2 are on different tables)

If I hardcode the @Val value (1/0) - SQL Server knows to build an execution plan in which it only accesses the relevant table for either Col1 or Col2.

But when using a variable all the tables in HugeView are accessed.

Can you advise on a way of doing it in a way that would help SQL Server not access the unecessary table?

Limitations:

Can't use option (recompile)
Can't encapsulate this code in an SP
Can't change HugeView

Unfortunately all aforementioned limitations are part of the product's design and/or dev scope - nothing I can do about that - just work within what I have.

I do however know that @var is either 1 or 0, and can create and query (and join on) temp or "real" tables as I like.

Also notice I don't select from the tables of Col1 or Col2 - I only use them for filtering.

I tried creating and joining on empty tables, or top(@somevar) depending on @var's value - but didn't help.

UNION ALL won't help as in any case the optimizer doesn't know the value of @val. Also can't change the query - if I could then of course all would've been solved.

To reproduce your issue, I created three tables with 1000 rows. The view definition does left outer joins on the primary keys of the table which should allow for join elimination. Here's the code:

CREATE TABLE dbo.BASE_TABLE (ID BIGINT NOT NULL, PRIMARY KEY (ID));
INSERT INTO dbo.BASE_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.COL1_TABLE (ID BIGINT NOT NULL, COL1 VARCHAR(1), PRIMARY KEY (ID));
INSERT INTO dbo.COL1_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'A'
FROM master..spt_values;

CREATE TABLE dbo.COL2_TABLE (ID BIGINT NOT NULL, COL2 VARCHAR(1), PRIMARY KEY (ID));
INSERT INTO dbo.COL2_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'A'
FROM master..spt_values;

GO

CREATE VIEW HugeView AS
SELECT b.ID AS B_ID, c1.COL1, c2.COL2
FROM dbo.BASE_TABLE b
LEFT OUTER JOIN dbo.COL1_TABLE c1 ON b.ID = c1.ID
LEFT OUTER JOIN dbo.COL2_TABLE c2 ON b.ID = c2.ID;

GO

The following query access all three tables when it shouldn't have to:

DECLARE @Val BIGINT = 1;
SELECT 1 FROM dbo.HugeView
WHERE (Col1 = 'A' AND @val = 1) OR (Col2 = 'A' AND @Val = 0);

Note the thickness of the arrows in the actual plan:

If I add a RECOMPILE hint then I only get two tables in the actual plan as desired:

Probably the most straightforward fix is to use UNION ALL. Here's the estimated plan:

At first the plan might look bad because it references all three tables. However, the highlighted startup predicate filter is important:

What that means is that branch of the query plan may not be executed depending on the value of the parameter. If I get the actual plan you can see that only one half of the plan was executed:

dbfiddle link for everything in this post.

Note that depending on the complexity of the view the startup filter may not appear in the optimal place. However it should save you some amount of unnecessary work.


How to achieve Data synchronization in Active/Active clustering using SQL Server 2012 Standard Edition I have a requirement of Active/Active clustering to be implemented using SQL Server 2012 Standard Edition in shared storage SAN. How the data share or synchronization between the two instances on the the disks of nodes in shared storage is going to achieve on the same.

My concern here is how the data synchronization happens between the two nodes in SQL Server 2012 Standard Edition and how to configure the same?

There is no such thing in SQL 2012.

There are Availability Groups which don't use shared storage. It it's Enterprise edition only (or SQL 2016).


Understanding Set Returning Function (SRF) in the SELECT List Why is there  a difference in behavior between using a Set Returning Function (SRF) in the SELECT list vs using SRF in the FROM clause?

For example, for a simple SRF returning 2 rows:

CREATE OR REPLACE FUNCTION gen_series(out integer, out int)
  RETURNS SETOF record AS $$
  SELECT 1,1
  UNION
  SELECT 2,2;
$$ LANGUAGE SQL;

SELECT gen_series(); returns two single column rows each containing a record:

=>  gen_series
------------
 (1,1)
 (2,2)
(2 rows)

Whereas SELECT * FROM gen_series(); returns two rows with the record expanded:

=>  column1 | column2
---------+---------
       1 |       1
       2 |       2
(2 rows)

By comparison, if the SRF is returning a single column, then calling the SRF in the SELECT or FROM clause makes no difference. e.g.:

=> SELECT generate_series(1,2);
 generate_series
-----------------
               1
               2
(2 rows)

=> SELECT * FROM generate_series(1,2);
 generate_series
-----------------
               1
               2
(2 rows)

My questions are:

I don't quite see why in the second case, the SRF behavior is different from the first case just because the returned table has a single column. Is this really consistent behavior in terms of types, tuples and sets?
What's the difference between the two cases that leads to the different behavior?
SRF can be used as tables as shown above, but can tables be used to replace SRFs as well? e.g.

SELECT my_table;

Apparently, this can't be done, but why is SELECT my_SRF(); possible, whereas
SELECT my_table; is not allowed (in terms of relations and mathematics)?

Postgres treats the simple case differently. Multiple columns are treated as composite type (table row), which is only decomposed with SELECT * FROM ..., while a single column of scalar type is treated as just that, no added composite type wrapper. So SELECT my_SRF() produces the same as SELECT * FROM my_SRF() for that simple case. The manual about Table Functions:

Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows).

I agree this is confusing, and you are not the first to be confused. (Consider the alternative, though: adding a composite type wrapper around a single column might be even more confusing.)
But not as confusing as what happens used to happen with multiple SRF functions in the SELECT list before Postgres 10. This was fixed for good:

What is the expected behaviour for multiple set-returning functions in select clause?

The standard SQL way is to move SRF functions to the FROM clause. Use a LATERAL join to operate on table columns. The manual suggests:

The LATERAL syntax produces less surprising results when calling
multiple set-returning functions, and should usually be used instead.

What is the difference between LATERAL and a subquery in PostgreSQL?


How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()? I have a stored procedure which creates a complementary trigger and audit table when a table name is passed into it.  The SP filters out all the calculated columns and other stuff that stops you doing an 'SELECT *' for auditing and then writes a trigger that inserts those specific columns from table into table_audit upon DELETE/INSERT/UPDATE along with some audit data such as HOST_NAME() and COLUMNS_UPDATED().  This has been little used but has generally worked for the clients who've asked for it.

In a recent round of testing I was asked to set up the auditing on a test database.  This caused inserts in our main table to fail because "string or binary data would be truncated", after investigation I found the column where the results of COLUMNS_UPDATED() was being stored was the issue.

The definition of the column was:

[UpdateColumns] [varbinary](16) NULL

Changing the definition to this has made everything work again:

[UpdateColumns] [varbinary](24) NULL

However what this highlights is that I don't understand the relationship between the number of columns in the table (95 in this case, 7 of which are calculated) and the size of the output from COLUMNS_UPDATED().  I thought [varbinary](16) = 128bits, which should be more than enough flags for 95 columns.

So my question is: what is the relationship?

Secondary question: Can I easily derive from the number of columns that will be audited a value for x in [UpdateColumns] [varbinary](x) when building the trigger or would I be better off just setting x to some larger number?

Let me start out with a question: Why do you store the value of COLUMNS_UPDATED() if you don't understand how to read it? What possible value can you get out of storing it?

But to answer your question, COLUMNS_UPDATED() returns one bit per column that is currently in the table or was previously in the table. COLUMNS_UPDATED() is based on the column_id that you can retrieve from sys.columns. Every new column get an increasing value assigned in there, kind of like an IDENTITY value, however restarted for each table. So if you remove and re-add columns old values will not get reused; instead you will have gaps. The following code snippet demonstrates that:

CREATE TABLE dbo.tx(c1 INT, c2 INT, c3 INT);
ALTER TABLE dbo.tx DROP COLUMN c2;
ALTER TABLE dbo.tx ADD c4 INT;

SELECT name, column_id FROM sys.columns WHERE object_id = OBJECT_ID('dbo.tx');

This is the output of this query:

|name | column_id|
+-----+----------+
|c1   | 1        |
|c3   | 3        |
|c4   | 4        |

That has the big advantage that you do not have to change trigger code that was looking for a specific column. However, it has the huge disadvantage that that everything gets obscured. Also you might not be able to script out your tables and triggers and recreate them in a testing database because there the column_id values might be different.

So to figure out how many bits you need, just look at the MAX(column_id) value for your table.

An alternative is to use the UPDATE() function that takes a single column name and returns a value indicating if that column was changed or not. Both functions have the additional disadvantage that they do not actually compare the values; they just report that the column was referenced, even if the value in the column stayed unchanged.

In your case it looks like you are trying to capture the actual data changes. Have a look at Change Data Capture - no need to reinvent the wheel.


Is it safe to delete the default "postgres" role? I'm trying to understand if it's okay for me drop the "postgres" role, or at the least, remove it as the superuser.
Thanks!

I'm trying to understand if it's okay for me drop the "postgres" role,
  or at the least, remove it as the superuser.

Dropping the role owning the template databases and all the system tables of all databases is not possible. There is even a specific error for that, should you try to DROP ROLE postgres:

ERROR:  cannot drop role postgres because it is required by the database system

It's technically possible to remove the superuser capability to postgres, but  if there is no superuser left in the system, any administrative task that only a superuser can do will be out of reach, and that would be really inconvenient.

On the other hand, it's entirely reasonable to deny through pg_hba.conf the ability for the postgres role to connect remotely, and also to set and enforce a password for the postgres account even for local-only connections.


Session_id: negative number Today, while monitoring my data base I see an special session_id with negative number. (-4)

Anyone would help me to understand what do it please?

Per BOL:

blocking_session_id
ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

I did a write up on it here.  However the simple way to get rid of it

I wasn't able to find a whole lot of information on -4 spids.  Everything I was able to find sounded like a tempdb issue.  This is the solution I use to get rid of -2s but it should work for you:

 SELECT request_owner_guid
 FROM sys.dm_tran_locks
 WHERE request_session_id = -4

Take each row from the result and use it to create a KILL statement.

KILL 'D21D8DAB-C75C-411A-879B-FFDE10E3F397'

Be warned however we do have a system that gets -2s (I know that's different but even so) on a regular basis and if you kill them it can cause problems with the system.  If this is the only time you've seen it I think you'll be fine though.


Relational Databases many records stored in one I've built a few relational databases in Access, and will now start moving to SQL Server. I want to build a database for me to practice and learn, and thought about building a fake restaurant's database. I've been thinking about the design, and came across an interesting problem.

Each menu item would have a record in one table. Receipts would go on another table, customers too. But how would I join many menu items to a single receipt record?

I would prefer an answer about the design itself rather than the solution in SQL Server.

I know this question might sound simple to answer but I'm new to database design.

Thank you.

What you're describing is called a "many-to-many" relationship. Each receipt can have many menu items; each menu item can be on many receipts.

This is normally handled by creating a "bridge" table.

Let's assume you already have the following tables: menu_item, customer, and receipt. Further, let's assume each one has a primary key column (uniquely identifies a given row), <tablename>_id.

So, each row in receipt would be connected to a customer, and would need to be able to have several menu_items associated with it.

First, you'd add customer_id as a column in the receipt table, to tie the receipt to a customer.

However, you have an indeterminate number of menu items to attach as well. One option would be to have 5, 10, maybe 15 columns, each of which could be tied to one menu item. However, this is messy - you leave a lot of columns empty (even NULL columns consume some space), and you still run the risk of eventually having a customer who orders more than that number of items.

It's much cleaner to add a new table, receipt_item. This table would have its own primary key (receipt_itme_id), and would tie to one receipt (so it has a receipt_id column), and one menu_item (so, add the menu_item_id column). In the case of a restaurant, you might have other columns as well: instructions, to note that the steak should be medium rare, for example. That's just a thought from your example; all a bridge table must have is the ID values from the two tables it connects.

So, each receipt could have anywhere from one to dozens of receipt_item rows connected to it, and each of those ties to a menu_item.


Can I use attribute name to identify XML element? Rather than using the element index COLUMN[1]/@value below, is it possible to retrieve the column value by specifying an attribute name such as: COLUMN[@name=”Prod_ID”]/@value?

When I tried, I got this error:

  Msg 2389, Level 16, State 1, Line 41
  XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

My code:

DECLARE @xml AS XML = '<DATA>
  <ROW RowNumber="1">
    <COLUMN Name="ProdID" Value="1234" />
    <COLUMN Name="AppID" Value="20144" />
    <COLUMN Name="ConsentDesc" Value="My Description" />
    <COLUMN Name="ConsentVer" Value="" />
    <COLUMN Name="ConsentAcceptance" Value="6789" />
    <COLUMN Name="ConsentType" Value="TermsAndConditions" />
    <COLUMN Name="CreatedDate" Value="8/22/2014 3:04:29 PM" />
  </ROW>
</DATA>'

SELECT
   m.c.value('@RowNumber', 'varchar(50)') AS RowNumber ,
   m.c.value('COLUMN[1]/@Value', 'varchar(50)') AS [ProdID] ,
   m.c.value('COLUMN[2]/@Value', 'varchar(50)') AS [AppID] ,
   m.c.value('COLUMN[3]/@Value', 'varchar(50)') AS [ConsentDesc.] ,
   m.c.value('COLUMN[4]/@Value', 'varchar(50)') AS [ConsentVer.] ,
   m.c.value('COLUMN[5]/@Value', 'varchar(50)') AS [ConsentAcceptance] ,
   m.c.value('COLUMN[6]/@Value', 'varchar(50)') AS [ConsentType] ,
   m.c.value('COLUMN[7]/@Value', 'varchar(50)') AS [CreatedDate]
FROM
   @xml.nodes('DATA/ROW') AS m ( c )

The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end.  Here's an example:

DECLARE @xml AS XML = '<DATA>
  <ROW RowNumber="1">
    <COLUMN Name="ProdID" Value="1234" />
    <COLUMN Name="AppID" Value="20144" />
    <COLUMN Name="ConsentDesc" Value="My Description" />
    <COLUMN Name="ConsentVer" Value="" />
    <COLUMN Name="ConsentAcceptance" Value="6789" />
    <COLUMN Name="ConsentType" Value="TermsAndConditions" />
    <COLUMN Name="CreatedDate" Value="8/22/2014 3:04:29 PM" />
  </ROW>
</DATA>'

SELECT  m.c.value('@RowNumber', 'varchar(50)') AS RowNumber ,
        m.c.value('(COLUMN[@Name="ProdID"]/@Value)[1]', 'varchar(50)') AS [ProdID] ,
        m.c.value('(COLUMN[@Name="AppID"]/@Value)[1]', 'varchar(50)') AS [AppID] ,
        m.c.value('(COLUMN[@Name="ConsentDesc"]/@Value)[1]', 'varchar(50)') AS [ConsentDesc.] ,
        m.c.value('(COLUMN[@Name="ConsentVer"]/@Value)[1]', 'varchar(50)') AS [ConsentVer.] ,
        m.c.value('(COLUMN[@Name="ConsentAcceptance"]/@Value)[1]', 'varchar(50)') AS [ConsentAcceptance] ,
        m.c.value('(COLUMN[@Name="ConsentType"]/@Value)[1]', 'varchar(50)') AS [ConsentType] ,
        m.c.value('(COLUMN[@Name="CreatedDate"]/@Value)[1]', 'varchar(50)') AS [CreatedDate]
FROM    @xml.nodes('DATA/ROW') AS m ( c )


Getting a list of active objects that use cursors but don't explicitly close/deallocate them Sometimes our developers will write a query that uses cursors, but doesn't close them out explicitly. I'm trying to generate a list of active objects in my production database that use cursors but don't explicitly close/deallocate them. To do this I've written a simple statement that does the job, but is extremely slow:

select distinct name,
            definition
from   SYS.SQL_MODULES
   inner join SYS.OBJECTS O
           on SQL_MODULES.OBJECT_ID = O.OBJECT_ID
where  SQL_MODULES.DEFINITION like '%open%'
   and SQL_MODULES.DEFINITION like '%declare % cursor%'
   and ( SQL_MODULES.DEFINITION not like '%close%'
          or SQL_MODULES.DEFINITION not like '%deallocate%' )

Currently this takes something like 3 minutes to run. Is there a better way to get the information I'm looking for?

Rather than search all of your stored procedure text for those wildcards, you'd probably be better off looking for cursors that are open, and their associated text. That would likely make it easier to locate cursor names, stored procs, etc. written by forgetful developers.

To test, run this in one window:

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#commands') IS NOT NULL
   BEGIN
         DROP TABLE #commands;
   END;

CREATE TABLE #commands
(
  ID INT IDENTITY(1, 1)
         PRIMARY KEY CLUSTERED,
  Command NVARCHAR(2000)
);
DECLARE @CurrentCommand NVARCHAR(2000);

INSERT INTO #commands ( Command )
    SELECT 'SELECT 1';

DECLARE result_cursor CURSOR
FOR
        SELECT Command
            FROM #commands;

OPEN result_cursor;
FETCH NEXT FROM result_cursor INTO @CurrentCommand;
WHILE @@FETCH_STATUS = 0
      BEGIN

            EXEC (@CurrentCommand);

            FETCH NEXT FROM result_cursor INTO @CurrentCommand;
      END;

--CLOSE result_cursor;
--DEALLOCATE result_cursor;

Then in another window, run this:

SELECT dec.session_id, dec.cursor_id, dec.name, dec.properties, dec.creation_time, dec.is_open, dec.is_close_on_commit,
        dec.fetch_status, dec.worker_time, dec.reads, dec.writes, dec.dormant_duration, dest.text
    FROM sys.dm_exec_cursors (0) AS dec
    CROSS APPLY sys.dm_exec_sql_text(dec.sql_handle) AS dest
    WHERE dec.is_open = 1;

You should see the cursor opened and left hanging by the other session here, along with any other open cursors.

If you need to monitor for these (and you don't have a monitoring tool), you can use the dormant_duration column and poll that DMV with an agent job that will fire off an alert or email when an open cursor is dormant for a certain amount of time. That's in milliseconds.

Hope this helps!


What happens to replication if rows have been deleted on a target already? In a normal replicated world, the following sequence happens:

MAIN ASE: delete N rows issued
RS:       delete N rows sent to replication target
TARGET:   delete same N rows

However, what happens if the following sequence of events occurs?

TARGET:   delete N rows (before main server has them deleted)
MAIN ASE: delete N rows issued
RS:       delete N rows sent to replication target
TARGET:   No such rows to delete?!?!?!

What happens then?

Does the rep server fail? Does the main ASE transaction abort? Does everyone act like this never happened and replication worked (since end result is as if it did)?

P.S. Yes, I know, typically you shouldn't ever pull such a stunt and data should never be changed on replication target. And if not you should use "set replication off" on main server session. The question is "what happens if this does occur, in violation of 'should'".

What happens from a SAP/Sybase Replication Server (SRS) perspective?

Depends on SRS version and how the DBA's configured the SRS instance.

Prior to SRS 15.2 the 'empty' DELETE would cause no problems, ie, the DELETE would be issued against the target, no rows found/affected, and SRS would continue to process the next transaction.

The key here is that a DELETE statement that does not find any rows to delete merely completes, successfully (ie, without error), with @@rowcount=0.

NOTE: The same 'logic' applies to UPDATEs, INSERT/SELECTs and SELECT/INTOs that complete successfully but affect no rows.

NOTE: The same 'logic' applies to DML statements that complete successfully but affect a different number of rows.

With SRS 15.2+ a replication server error class was introduced with the primary emphasis being to notice when transactions affect a different number of rows in the target ... and when said issue occurs to raise an error (eg, 5185) and suspend the DSI connection.

Each transaction coming over from the source is tagged with the number of affected rows.  If the transaction, when applied to the target, affects a different number of rows then SRS will (by default) suspend the DSI and dump an error message to the errorlog.

The DBA can modify the default rowcount mismatch behavior by assigning a different action to the corresponding error number in the replication server error class (see the assign action command for more details).

Obviously (?) the DBA should research any rowcount mismatch errors to determine if they are ok/valid, or if they indicate an issue with the the target being out of sync with the source.

Other scenarios the DBA would want to investigate could include:

replicated stored procs that don't affect the same set of rows on the target
custom function strings that modify a transaction in such a was as to (explicitly) modify the number of affected rows in the target
lack of a primary key for a table

Keep in mind that while SRS is routinely used to keep a DR/backup database in sync with the primary database, SRS can also be used to:

distribute subsets of data (distribution model)
consolidate data (roll-up model)
maintain historical/audit data (eg, convert DELETEs/UPDATEs to save 'old' image of data in a history table)
propagate sub-/super-sets of data per application requirements

Net result is that the occurrence of rowcount mismatches may or may not be ok ... it all depends on the individual SRS environments.


Database design for mobile app At first I would like to say hello to all of you, since it is my first post here on DB Administrators.

I am writing an app which store drinks and their data. At the beginning it was storing all in the local SQLite database and it was all updated by downloading newer version of app by the user. Since DB is still growing, I figured out that it would be nice if user may add a entry himself. So I decided to create MySQL db and sync it with local SQLite. And here are my issues:

How to download only the newer and updated entries, and avoid downloading same entry once again?
How to provide edit entry possibility to db? Is it better to create new table with proposal entries and after verification, move them to main table? Or maybe should I create additional columns, which would be proposals for new values?
What if I would get lots of changes proposals? Is it better to store only the last one? Or maybe block edit possibility if a proposal is already set?
How to avoid cheaters and scammers? Some bored people may want to try make a mess, editing multiple entries with fake data. How to avoid such kind of events? Grant edit access only to verified people?
If I would like to store images of items, is it better to store it in db or use some kind of flicker or instagram instead?
Is it better to save new entry in SQLite instant after edit? Or just post it to MySQL and add it to SQLite only after verification and only via SQLite and MySQL sync?
Is it good idea to set key value to EAN code? At least it is constant and connected to only one product. There will be no items containing no EAN.

Thanks for you replies. And also I would like to apologise if some of my questions doesn't fit in DBA rules. I am not db administrator.... yet ;)

How to download only the newer and updated entries, and avoid
  downloading same entry once again?

Use a database synchronization tool like SymmetricDS or Daffodil Replicator.

You'd have a central database server, like PostgreSQL, with SymmetricDS running on it.
Each client, such as an Android smartphone, runs SQLite and SymmetricDS.
The SymmetricDS clients and server communicate with each other over HTTP(S), sending changesets back and forth.

  How to provide edit entry possibility to db? Is it better to create
  new table with proposal entries and after verification, move them to
  main table? Or maybe should I create additional columns, which would
  be proposals for new values?

I would add an is_approved or similar column that needs to be true for items to show up to regular users. Editor and admins can see unapproved items.

  What if I would get lots of changes proposals? Is it better to store
  only the last one? Or maybe block edit possibility if a proposal is
  already set?

Use History Tables (copy-on-write to a table similar to your main tables) to capture each change. That way you can rollback to earlier or better versions if you need to. Use Optimistic Concurrency Control to prevent updates if data changed.

  How to avoid cheaters and scammers? Some bored people may want to try
  make a mess, editing multiple entries with fake data. How to avoid
  such kind of events? Grant edit access only to verified people?

Like this site, add "user abilities" after users have proven themselves. Block bad users. Use machine learning to filter spam.

  If I would like to store images of items, is it better to store it in
  db or use some kind of flicker or instagram instead?

Usually database is better, if you can cache heavily on web server, and can do incremental backup.

  Is it better to save new entry in SQLite instant after edit? Or just
  post it to MySQL and add it to SQLite only after verification and only
  via SQLite and MySQL sync?

Saving to SQLite locally first allows for offline usage, and is probably more responsive. http://offlinefirst.org/

  Is it good idea to set key value to EAN code? At least it is constant
  and connected to only one product. There will be no items containing
  no EAN.

No, I would use a meaningless key for product_id and a unique nullable column for EAN.


Include single quote string in redshift dynamic SQL I have function named
Schema.func_date(date,varchar)

I wanna do like this(I am using below statement inside Store proc)
Execute 'Select *, ID as Main_Id, ' ||
         'schema.func_date(quote_literal('2020-02-20'),quote_literal('ST')), '||
         'from main_table'

I am getting Invalid operation error while passing single quote string. How to pass the single quote string perfectly in execute statement?

While the QUOTE_LITERAL() function is helpful in specific contexts, I think you still need to manually escape the single quotes when you use Dynamic SQL.
So your query should follow this:
Execute 'Select *, ID as Main_Id, ' ||
         'schema.func_date(quote_literal(''2020-02-20''),quote_literal(''ST'')), '||
         'from main_table'


Why ALL PRIVILEGES ON `db`.* gives access to all databases? I've installed Ubuntu 12.10(quantal) and mysql-server-5.5.

When I execute:

drop user ''@'localhost';
create database `redmine`;
grant all on `redmine`.* to 'redmine'@'localhost';

User redmine@localhost can access not only redmine database, but also other databases.

How do I prevent him from accessing any database except redmine?

P.S.
I can't reproduce this on other distributions. What is a proper behaviour? Is this a bug?

UPDATE1:

mysql> show grants;
+--------------------------------------------------------------+
| Grants for redmine@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'redmine'@'localhost' |
| GRANT ALL PRIVILEGES ON `redmine`.* TO 'redmine'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| x |
+----------------+
1 row in set (0.00 sec)

The grants you gave on the redmine database have nothing to do with access to the test database.

The problem is the way mysql was installed. It allows full access to the test database. It is not a bug. I wrote a past post (Question and Answer) on why anybody can access a test database and how to fix it:

MySQL : Why are there "test" entries in mysql.db? (Question)
MySQL : Why are there "test" entries in mysql.db? (Answer)

In essence, the problem is the table mysql.db. It contains entries that allow everyone and his grandmother to do anything in a test database or any database whose first five characters are test_. Removing those entries with DELETE FROM mysql.db WHERE LEFT(db,4)='test'; will immediately solve your particular problem.

I have other posts on how to thoroughly strip anonymous users from mysql.user and why

Cannot drop anonymous user from mysql.user
mysqladmin user account not secure?
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Grant table privileges to a user connecting from any host

Please read these carefully because my past answers will show you things like

SELECT USER(),CURRENT_USER();

This will break down the difference between how you logged in and how mysqld allowed you to login.


Subtract values in same column from 2 different dates in same Mysql table I have a MySql table like:

Item        Date          Likes

Book        2012-05-20    402
Book        2012-05-21    432
Book        2012-05-22    450
Pen         2012-05-20    20
Pen         2012-05-21    20
Pen         2012-05-22    25

1) What is the simplest query for calculating and returning the difference in Likes column between 2 set dates for all of the Item values, ordered by the Difference?

For example:

Likes difference between 2012-05-20 and 2012-05-21 returns next results:

Item        Difference
Book        30
Pen         0

Likes difference between 2012-05-20 and 2012-05-22 returns next results:

Item        Difference
Book        48
Pen         5

2) Also there will be some cases when there will be no records for some of the dates for an Item in the table, in those cases no results for that Item should be returned.

3) What should be the correct indices to optimize the speed of the query?

I did come across this query that might work but it is a huge query and I am sure that there can be a more compact and elegant solution :)

SELECT t1.item, t2.likes - t1.likes difference
FROM table t1, table t2
WHERE t1.item = t2.item
  AND t1.date = @first_date
  AND t2.date = @second_date

If you need all items (ever the data on @first_date or @second_date is not present), rewrite from cartesian to FULL JOIN and use COALESCE to replace NULLs with zeros.


Enabling SSL in MariaDB running on Synology NAS I am having problems with enabling SSL in MariaDB running on Synology NAS (DSM  version 6.0) using self signed certificates. I have modified the my.cnf file to point to certificate files and the DB seems to be loading the .cnf file ok, the ssl variables are listing the corect location of the cert files (verified over phpmyadmin) however it still says that ssl is diabled (variable have_ssl=disabled) and the DB is refusing incoming ssl connections. This is my first time working with Synology NAS so I have no idea what to try, so any help or suggestion is welcome.
Thank you in advance.

Edit: I tried contacting synology tech support but got answer that they do not provide any support for mariadb beyond just installing it.

Please, check the following configuration files and variables:

File: /etc/mysql/my.cnf

[client]
...
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem
ssl-verify-server-cert
...

[mysqld]
...
ssl
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
...

File: /etc/hosts

127.0.0.1 localhost CNSERVER

MariaDB Command-Line:

$ mysql -u root -p -h CNSERVER
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 10.1.14-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| have_openssl        | YES                             |
| have_ssl            | YES                             |
| ssl_ca              | /var/lib/mysql/ca.pem           |
| ssl_capath          |                                 |
| ssl_cert            | /var/lib/mysql/server-cert.pem  |
| ssl_cipher          |                                 |
| ssl_crl             |                                 |
| ssl_crlpath         |                                 |
| ssl_key             | /var/lib/mysql/server-key.pem   |
| version_ssl_library | OpenSSL 1.0.2g-fips  1 Mar 2016 |
+---------------------+---------------------------------+
10 rows in set (0.00 sec)

MariaDB [(none)]> SHOW SESSION STATUS LIKE '%Ssl_version%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)


Can connect using SQL Server Management Studio but cannot ping or via program I have a remote SQL Server: myremotedb.myremotedomain.gbl.

I could connect to it from another server myserver.myareadomain (not in the same domain) using SQL Server Management Studio without problem.

But I couldn't ping it from my server (myserver.myareadomain), nor connect to it via the program that we developed.

PS: I don't have admin right on the remote server.

Do you have any ideas?

If you can situationally access SQL Server over the network, this sounds like a firewall issue. To quickly test, simply turn the firewall off on myremotedb.myremotedomain.gbl first. Then, retest the application connectivity. If that doesn't work, turn the firewall of on your app server and retry connecting.

Baring that, I'd start testing ports using nmap to see which ports are able to get through to the target server. nmap is like ping but allows you to test port numbers as well. Test to see whether you can nmap port 1433 (the default sql port) from the app server to myremotedb.myremotedomain.gbl.

https://nmap.org/book/inst-windows.html


Generic log-table for CRUD operations that support both single and composite Primary Keys I'm trying to come up with a decent table design for a table that will log CRUD operations for a small application. There won't be a lot of traffic, which could justify a single table for all events. So far, my idea looks like this

ID int identity primary key,
[Schema] nvarchar(128),
[Table] nvarchar(128),
ObjectID int, -- primary key of the object we're logging
Action nchar(1) , -- the type of event (I/U/D, short for INSERT, UPDATE etc
Field nvarchar(128), -- the changed column, in the event of UPDATE
OldValue nvarchar(1000),
NewValue nvarchar(1000),
ChangedAt datetime2,
ChangedBy nvarchar(128)

This would cover most of our use cases. There is, however, one itch with this design, and that is when I want to log changes to records where the Primary Key spans multiple columns, aka a Composite Primary Key.

In order to satisfy this need, I would have to extend the proposed table-design with yet another ID-column, allowing us to store both sides of the composite primary key.

You probably see that this is heading down the wrong path. What if I later need to log events for a table containing a Primary Key spanning 3 columns or more? Simply extending the log-table with "ObjectID_2" and "ObjectID_3" columns sounds asinine. Not to mention that the majority events will only have NULL-markers in those columns, as most our tables use single-column primary keys.

I am no fan of generic tables and column in general, and I'm starting to think a better solution would be to keep a separate log-table for each table that needs auditing. Am I going about this the wrong way?

Change Tracking does not solve this problem. The log-table needs to be exposed to the web and in a human-readable format. I have considered both CDC and Temporal Tables, but then we'd already be in the realm of "log everything for a single table", which is not my use-case. The log-table will be used by the application to log whatever events they care about, not every DML operation. That's is why I opted for a single-table solution.

Your issue sounds like it colud make use of a dimensional model, with the objects to be tracked as Dimension(s) and the events to be measured as Facts, especially if this table needs to be human readable and exposed to end users. Check out this resource for further reading BACK TO DIMENSIONAL MODELING BASICS and The 10 Essential Rules of Dimensional Modeling


Update multiple rows with different columns I am trying to perform a bulk MySQL update where I know only certain columns need to be updated for each row, e.g. row A only "name" changed to "Sue", row B "name" and "address" changed to "Joe" and "Evergreen Terrace", etc. All the columns and data may be different.

Most multi-row examples typically update the same columns for each row, but is there a way for the SQL command to specify only what's changed per row?

Something like:

INSERT INTO user (id, name, address, telephone)
VALUES
    (1, 'Tim', NULL, NULL),
    (2, NULL, 'America', '000')
ON DUPLICATE KEY UPDATE
    name = IF(ISNULL(VALUES(name)), name, VALUES(name)),
    address = IF(ISNULL(VALUES(address)), address, VALUES(address)),
    telephone = IF(ISNULL(VALUES(telephone)), telephone, VALUES(telephone))

will work (leaving the NULL fields untouched), presuming the id field is the primary key and will trigger the DUPLICATE KEY UPDATE, although I can't comment on how efficient it would be.


How often to backup database and what are the favoroble practices to do so Curious to know on how often you guys back up SaaS?

I have determine that i need backups to be done every hour for an application I have, based on how often data is updated an amount of users. I have estimated that in an hour i can have about 10 new entries and about 100-200 updated entries, something on those lines, some times, at peak, it could be 2-3 times more than that.

How do you determine this number and what do you guys do?

I don't understand why you're asking this question. If you're buying in a service, then backups should be part of the SLA and not your responsibility. If it's not part of the service, then why are you using SaaS?

On the other hand, if you need to be backing up something, why are you backing up every hour?

Technically, if your application is running on a DBMS with a transaction log, it's possible to be backing up continuously, even replicating logs to (an)other machine(s). Then you have a backup process that runs regularly simply to make recovery faster (a newer backup + a small amount of logs is faster to restore than an older backup + a larger amount of logs). A retention policy on these backups and log files determines how far back in time you can go back to in case of user error (backups aren't just for when your have a "crash", they'll also be needed if someone deletes or modifies data they shouldn't have). The retention policy will often be a compromise between how far back in time you can "go" and how much space you need to store the backups + logs.


Postgres - detect change in JSONB column value from 'null' to some value across rows I have to work with a table that looks like this:

The actual table is obviously much larger, but for sake of discussion, is there any way to elegantly query the difference between when { a: null } turned into { a: "something"? }

My instinct is to try something (probably pseudocode) like this:

select (select update_time as finish_time from fun_table where json_data -> 'a' != 'null' as tableA) - (select update_time as start_time from fun_table where json_data -> 'a' = 'null as tableB) from tableA, tableB

But there are a lot of different JSON objects in the actual json_data..."b", "c", etc. So again, is there a better / cleaner way to find the difference between the timestamps when "a" turned from "null" to "something"?

JSONB supports equality checks. So, if you're using it, you need only

Create an AFTER trigger on

CREATE TRIGGER foo
  AFTER UPDATE OF json_data
  ON myTable
  FOR EACH ROW
  WHEN (new.json_data IS DISTINCT FROM old.json_data)
  EXECUTE PROCEDURE myFunction;

Either,

Insert the id, and new.update_time into a new table, or
Update the update_time setting it the current time, and query later for update_time > last_time_ran.


How to stop MySQL from replicating CREATE TABLE... on Slave for ignored databases Mine is a simple MySQL Master-Slave setup, with --binlog-do-db option as discussed in this article.

As per the documentation, logs are written for DDL statements for all databases irrespective of '--binlog-do-db'.

Is there any way to stop this??

binlog_do_db = dbname says to replicate any statement when the default database is dbname.  So be careful what USE statement precedes the statement:

USE non_repl_db;
CREATE TABLE ...;


Conditional loop date lookup SQL Server 2008 If I have two tables, first one is WorkTime:

ID  EmpNAME     Hours/Day   Date0
1   Mark           7    2015-12-01
2   George         8    2015-12-01
3   Jennifer       6    2015-12-03
4   Mark           5    2015-12-08
5   Jennifer       6    2015-12-08

and the second one contains unique names is EmpName:

ID  EmpNAME
1   Mark
2   George
3   Jennifer

Excepted output will repeat all unique names table for each particular existing date with null values if the name did not exist in this date like below table

 EmpNAME    Hours/Day     Date0
    Mark         7        2015-12-01
    George       8        2015-12-01
    Jennifer    null    **2015-12-01**
    Mark        null    **2015-12-03**
    George      null    **2015-12-03**
    Jennifer     6        2015-12-03
    Mark         5        2015-12-08
    George      null    **2015-12-08**
    Jennifer     6        2015-12-08

How can I get that?

Here is example of solution, which give you expected result:

SELECT EmpNAME,[Hours/Day],Date0
FROM (SELECT DISTINCT Date0 FROM #WorkTime) D
CROSS JOIN (SELECT EmpNAME FROM #EmpName) EN
OUTER APPLY (SELECT [Hours/Day] FROM #WorkTime wt
             WHERE EN.EmpNAME=wt.EmpNAME AND D.Date0=wt.Date0) HD
ORDER BY Date0

At this moment, I don't see other way.


What network access do I need when I restoring files using RMAN? I am restoring files from one server to another server using Oracle RMAN, what network accesses do I need?(between these servers)

ftp? port 1521 should be open? or what?

I appreciate your answer thanks...

If you are using the 11g duplicate from active database option, the only port that needs to be open is the listener port (usually 1521). If you are using any other options to copy your files to the standby host, the required ports for the protocol that you use (scp/ftp/nfs ...)

Apart from the 11g 'from active database' option, RMAN does only read backupfiles that are present on the node on which the restore/clone is to be taking place. Present in this setting means that they thould be readable. The can be on [n]fs or tape, as long as your host can read them it is ok.

I hope this helps


Stuck task in management studio, how do you handle them? After clicking on "Take database offline" in management studio this message stays hang and won't close if you click on close.

What's a good way to deal with stuck jobs like these in management studio ? Can you kill them via the activity monitor ? Should I seek what process is stopping this job from going through and terminate it ?

I would say don't ever use the "Take Offline" thing in the GUI unless you know for a fact that the database is not in use. By anything. That's hard to know without doing some legwork, so why not save this script somewhere and always use this?
USE [master];
GO
ALTER DATABASE $dbname$ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE $dbname$ SET OFFLINE;

And then the reciprocal of course:
ALTER DATABASE $dbname$ SET ONLINE;
GO
ALTER DATABASE $dbname$ SET MULTI_USER;

The reason you need to set it to SINGLE_USER first is to kick out any existing users (there is an option to do so on the detach dialog, but not the take offline dialog), since SQL Server needs exclusive access to the database in order to take it offline. Now, you may want to do some extra legwork anyway to see who is currently using the database, as if you do this in the middle of a large backup operation or ETL job or what have you, that might be problematic.
EDIT : I have filed a suggestion on Connect about this (see Connect #2687832) and have also posted it to Trello (filed under "Object Explorer").
This was fixed in SSMS 16.3; there is now a checkbox that asks if you want to forcefully terminate any active connections.


Dynamic SQL UNPIVOT and putting results into existing table I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting.

So, I have the data as follows that contains the weekly sales.

The numbers you see as columns refer the weeks of a year, and the columns are populated once the data is updated, and the week numbers don't go beyond 53, which means we know how many columns are going to be returned.

I can use static unpivot to handle this one, but the reason why I'm using Dynamic SQL to pull this off is to work with something new that I'm not familiar with, and trying to learn more about it, and the other reason is to get some knowledge that I can use dynamic sql on our monthly sales data, since I don't know how many columns are going to be returned and how can I put the unknown number of columns returned of the dynamic sql into a local table.

I've made some research and tried to unpivot the data to normalize with the following query, and it seems to be working.

-- create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
                      FROM (
                            -- get columns as a list
                            select  TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
                            from    INFORMATION_SCHEMA.COLUMNS
                            where   TABLE_NAME = 'WEEKLY_SALES'
                            and     ORDINAL_POSITION not in (1,2,3,4,5)
                            ) cols
                      ORDER BY ORDINAL_POSITION
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
                      )

-- Unpivot using dynamic sql
declare @sqlStr nvarchar(max)
set @sqlStr = N'
            select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, Col as WEEK, UNITS
            from(
                select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, ' + @cols + '
                from WEEKLY_SALES
            ) as cp
            unpivot
            (
                UNITS for Col in (' + @cols + ')
            ) as up'

exec sp_executesql @sqlStr

Now, I'm looking for a way to put the result of the above query into a local table. Could you please help me in doing this? The thing I'm going to do is, to truncate the table every week and insert the data with the updated weekly sales.

I've created a table where I will put the results into, and performed the following query, but it returns the error messages mentioned below.

   DECLARE
   @queryUpdate AS NVARCHAR(MAX),
   @queryInsert AS NVARCHAR(MAX);

SET @queryInsert = 'INSERT INTO dbo.WEEKLY_SALES_ (AREAS, COMPANY, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ')
                SELECT src.AREAS, src.COMPANY, src.PRODUCT, src.MARKET, src.SUBTERRITORIES ' + REPLACE(@cols, '[', 'src.[') + '
                FROM ('
             + @sqlStr
             + ') as src
                LEFT JOIN dbo.WEEKLY_SALES_ as dest
                   ON src.COMPANY = dest.COMPANY
                  AND src.AREAS = dest.AREAS
                  AND src.PRODUCT = dest.PRODUCT
                  AND src.MARKET = dest.MARKET
                  AND src.SUBTERRITORIES = dest.SUBTERRITORIES
                WHERE dest.COMPANY IS NULL;';

PRINT (@queryInsert);
EXECUTE (@queryInsert);

  Msg 102, Level 15, State 1, Line 2
  Incorrect syntax near '.'.
  Msg 156, Level 15, State 1, Line 12
  Incorrect syntax near the keyword 'as'.

Is there anything else am I missing?

Take away the text INSERT INTO dbo.WEEKLY_SALES... and everything after it on that line. Then change SELECT src.AREAS to:

SELECT INTO dbo.WEEKLY_SALES
src.AREAS, src.COMPANY ...etc

Also, before your SET @query statement, add:

DROP TABLE dbo.WEEKLY_SALES

That way, when it runs, it will drop yesterday's values (the entire table definition as well).  Then the rest of the statement will create and load the table on the fly.

Honestly, if you're going to truncate each time anyways, you might want to consider creating the table dynamically using Select into. Basically just drop the table first, then add the into [table name] after the select keyword.  In terms of straight easy coding, that's as simple as it gets. It won't give you the best performance on reading the table though because there won't be any indexes. That's really the only downside.


SQL Server Page Caching If I am not mistaken, SQL Server caches pages in order not to hit the disk each and every time. How does this caching work in the case of shared storage and multiple active SQL servers in a cluster? Since there is more than one active SQL server, how does the server know if the page is still valid? Do we need to disable this caching behavior for shared storage case? If so, how?

Normally, any single database can only ever1 be opened by a single SQL Server instance.  There is no way for multiple instances, even on shared storage, to simultaneously provide write access to the same pages of a database.

SQL Server does indeed cache pages, however each SQL Server instance allocates its own private memory, and no other process, including other SQL Server instances, can access that memory.  This is by-design, and works that way as a principle tenet enabling consistency.

In the case of shared storage, typically used for SQL Server Clustering, you have two (or more) computers connected to the shared storage, however only one of those servers can actually open the databases stored on that shared storage at any given time.  If one of the those servers stops, the clustering service will provide access to the other server, allowing the instance on that machine to serve the database.

A single Windows Failover Cluster Server can have multiple SQL Server Clusters installed, however each has it's own shared storage, and are essentially for all intents-and-purposes independent of each other.  They may occupy the same server computer, and they may not.  Still, they'll never be able to read from the same database file.

1 - read-only reporting workloads can be supported across multiple instances of SQL Server via Scalable Shared Databases.  The cost-benefit-ratio of such a setup is typically far below that required to make it feasible.


Check if bit is set in mysql I have a field which stores the days of the week in binary representation. For example, Mon-Wed-Fri would be represented as b'0101010' with the 1st digit as Sunday and the last as Saturday.

What I want to achieve: If today is Monday, I want to check if the Monday bit is set to 1. I do that by doing (b'0101010' & b'0100000' > 0). But I am struggling in converting the current date into the corresponding weekday binary representation. How can I achieve so?

The following expression will give the bit representation of today's day of week:

POW(2, 7 - DAYOFWEEK(NOW())

POW(2, n) raises 2 to the nth power, which is the same as shifting 1 n bits from the right.
DAYOFWEEK(date) returns the day of the week as a number, with Sunday = 1.
Since you have the low-numbered dates in the high-order bits, 7 - DOW converts that to Sunday = 6, Saturday = 0.

Wouldn't it be easier to declare the column as a SET, though? Then you could just use FIND_IN_SET().


postgres user cannot drop database Im trying to drop some databases on a postgres 9.6 server, but when I try Im told I am unable to.

I was under the impression that the postgres user was like a superuser.

su postgres
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (9.6.8)
Type "help" for help.

postgres=> DROP DATABASE d;
ERROR:  must be owner of database d
postgres=>

The problem with su postgres command, you changed the user to posgress but bash (to be more specific environment) owner still root or the original user, in-order to fix this issue use su - postgres instead of su postgres


Using View filters for tenants in SQL-Server 2008 I'm currently creating a Multi-tenant data architecture in a SaaS BI-product.

The product are using shared database and shared schema, but with tenant view filters and tenant data encryption
(Inspiration found here: http://msdn.microsoft.com/en-us/library/aa479086.aspx)

When creating the view filter, I can do both these:

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_SID()

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_ID()

Using SID() I am using the Security Identifier that looks something like this:

  0x3425F1595A67774ABF485CDC30285443

Using ID() I am using the normal Identifier that looks something like this:

  265

Which one is the absolute best to use and why? :)
I expect that both are absolutely unique and will never be reused in newer user-logins

I'd use SUSER_SID for this reason:

SUSER_ID is the principal_id column in sys.server_principals
SUSER_SID is the SID column in sys.server_principals and be generated by Active Directory (Windows login) or SQL Server

If you move the database to another server

then SUSER_ID will almost certainly be different.
SUSER_SID will be the same for Windows logins, but can be made the same for SQL logins by specifiying SID for CREATE LOGIN.


ALTER USER postgres PASSWORD null; now has locked me out of psql I have installed postgresql version 9.3.14 on High Sierra via HomeBrew. Upon trying to access the server via psql, I was being prompted for a password for the user postgres. Using postgres as the password got me into the database, but I was confused, as I have never been prompted for a password for the default user before. I looked around online on how to circumvent this, and I tried the solution suggested by lalligood on remove password requirement for user postgres which was to run alter user postgres password null;
and now I cannot access psql. Trying postgres, null or omitting a password does not get me in, and I do not know what to do.

Check your PostgreSQL pg_hba.conf. It should have lines like this copied directly from the docs.

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

You don't have to keep these. But it's normal to have these so you can connect locally without having to worry about passwords using your local system authentication methods. You also don't need both of these. If you only ever connect over sockets, feel free to nuke the one with tcp. This will allow you to log in with any locally authenticated user.

So you can log in as PostgreSQL with,

sudo -u postgresql psql -d myDatabase

or using tcp

sudo -u postgresql psql -d myDatabase -h 127.0.0.1


What AWS instance to choose for write-heavy PostgreSQL database server We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At
the moment we are using a smaller cloud service for the server hosting. Currently we are looking
at the possibility to move our server to AWS. The problems we are having are mainly related
to our database server and therefore we would like to have AWS-instance which is significantly better
than the one we are using now.

The stats for the cloud server where the database server is located are as follows:

8 CORES
16GB RAM
100 GB, RAID-10 * SAS 15.000 RPM

Is there anything in particular you should think about when choosing what amazon instance to use
for a PostgreSQL database server?

Asking "What AWS instance to choose for write-heavy PostgreSQL database server" is somewhat like asking "which spoon should I use to cut up this loaf of bread".

The answer is: don't use a spoon, use a knife. Or in this case, real hardware like a dedicated server with a batch of good quality SSDs and a decent raid controller that has a battery-backed write-back cache. Or at least a VPS with very strong I/O performance guarantees.

If in doubt, benchmark.

That said, you can make AWS perform significantly better than the defaults by using provisioned I/O EBS volumes on EBS-optimized instances, improving performance while retaining data durability. Alternately, you can sacrifice durability in exchange for more performance by using a high I/O instance with the database on the instance store; in this case you will need a careful backup and replication scheme because all your data will be lost if your instance stops or crashes.

If you need more assistance, contact me directly.


IN and NOT IN for XML column I have a table with a xml column.
Xml is similar to

<Root>
  <Row>
    <user>abc</user>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <user>vf</user>
    <Rowid>2</Rowid>
  </Row>
  <Row>
    <user>ert</user>
    <Rowid>3</Rowid>
  </Row>
  <Maxrowid>3</Maxrowid>
</Root>

Now below query return sl_no column and myxmlcolumn of rows containing xml column having values 'abc' or 'xyz' in node 'user'().Below query i am using similar to IN option of sql.

SELECT
    [mytable].[Sl_no],
    [mytable].[myxmlcolumn]
    FROM [mydb].dbo.[mytable]
    WHERE
        [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) return $x') > 0

I want similar kind of query which does same work as sql 'NOT IN' does. That is in my case i want rows not having values 'abc' or 'xyz' in node 'user'() in xml column.
So please help me on this.

The exist() Method (xml Data Type) returns a bit.
1 if at least one node is found and 0 if no nodes are found (empty result set).

To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0.

[myxmlcolumn].exist('for $x in /Root/Row
                     where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')]))
                     return $x') = 0

Your FLWOR query can be rewritten using a predicate on the user node instead,

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 0

And for the IN version of the query you check if exist returns 1 instead.

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 1


What is the meaning of "Monotonically Changing Shard Keys"? I am writing my first pubblication concerning MongoDB and more specific MongoDB Sharded clusters.
On the documentantion I found that the shard key in case of ranged sharding should be "Non-Monotonically Changing Shard Keys".

Can someone explain it to me? Example would be appreciate.

Thanks.

Monotonic here is used in the strict mathematical sense. A monotonically changing key is such that every key value is not less than (monotonically increasing) or not greater than (monotonically decreasing) all previously generated keys. A sequence, in the SQL sense of this word, would be an example of a monotonic key.

A non-monotonically changing key, conversely, is such that its value could be less than, greater than, or equal to any of previously generated values. A random value would be an example of that.

A monotonically changing key used for sharding causes newly inserted documents to be routed to a single shard, creating a performance hotspot. With a non-monotonic key writes will be distributed across all shards (hopefully), thus improving performance.


SQL Server 2016 Audit Actions, Can DatabaseAuditSpecification capture param values used in statement? I am using Schema_Object_access_Group audit action type. When I run a select, insert, update SQL statement, it records all statements nicely.

For example, captured Sql statement looks like :

UPDATE TOP (200) SomeTable
SET ColName1 = @ColName1
WHERE (ColName2 = @ColName2) AND ...

It is not explaining what was updated value and which row was updated.

I want to capture updated actual value used in SET ColName1 = @ColName1, is there an audit action type to accomplish the job. I want a way to capture parameters used in any select, update, insert statements.

Willing to use audit events as change log, to track who changed what without using the following:

Trigger approach to log all changed values
Change Data Capture "CDC"

There is no audit action to capture this information, it is a limitation of SQL Server Auditing. Basically it captures the statements after the query optimiser has already parameterised the query.

There are two open requests that I know of to alter this behaviour (here and here) that you could vote for, however, this limitation has been around for some time and doesn't seem likely to change. Note that this is apparently different in Azure SQL Database, however, I haven't tested this to confirm.

Your options for this are triggers, CDC, SQL Trace or third-party auditing tools.


Update with joining another table CREATE TABLE `cities` (
  `id` int(11) NOT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables

-- --------------------------------------------------------

--
-- Table structure for table `cities_extended`
--

CREATE TABLE `cities_extended` (
  `id` int(11) NOT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` int(5) UNSIGNED ZEROFILL NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL,
  `state_id` int(11) NOT NULL DEFAULT '0',
  `city_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities_extended`
--
ALTER TABLE `cities_extended`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cities_extended`
--
ALTER TABLE `cities_extended`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

UPDATE
    cities_extended
SET
    cities_extended.`city_id` = cities.`id`
FROM
   cities_extended
INNER JOIN
    cities
ON
    cities_extended.`state_id` = cities.`state_id`
AND
    cities_extended.`city`= cities.`city`

Results in

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
   cities_extended
JOIN
    cities
ON
    cities_extended.`state_id' at line 5

giving this error can you help me

MySQL's syntax for UPDATE does not have FROM (you probably got the syntax from a SQL Server example).

The syntax in MySQL is:

UPDATE
     <table_references>   -- this can be multiple tables joined
                         -- with the JOIN .. ON syntax
SET
     column_name_1 = value1,
     column_name_2 = value2
WHERE
     <conditions>
... ;

Try this:

UPDATE
        cities_extended
    INNER JOIN
        cities
    ON
        cities_extended.state_id = cities.state_id
    AND
        cities_extended.city = cities.city
SET
    cities_extended.city_id = cities.id ;

or with table aliases:

UPDATE
        cities_extended AS ce
    INNER JOIN
        cities AS c
    ON
        ce.state_id = c.state_id
    AND
        ce.city = c.city
SET
    ce.city_id = c.id ;


Slow update commands only on newly added data? Data gets read in every quarter into this table. Then after it is read in, certain fields get updated through an update command. The updates run really fast on the previous quarter's data, but when it gets to the new data it takes a long time.

We solved the problem by rebuilding the primary key after data is read in, and then running the update commands. This makes it just as fast as previous quarters but the rebuild can take a long time.

When I rebuild the indexes,  we started using a fill factor but that did not help the cause.

Is there anything else I should consider?

It is taking a long time because of out-of-date statistics. You've added a lot of rows to the table, so the statistics are not good now. Rebuilding the primary key seems to resolve the performance issue because you get updated statistics when you rebuild an index. But rebuilding an index is an expensive way to just get updated statistics.

Run UPDATE STATISTICS WITH FULLSCAN on the primary key, or consider a sampling. If the table has millions of rows in it, perhaps a 30 percent sampling would do the trick.


Database table design question Firstly, database novice here.  I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able to split a Sales Order Line Item into 2 or more Purchase Orders and a Purchase Order Line Item into 2 or more Shipping Containers.

There will be some(a lot of) adjustments back and forth where once a Sales Order Line Item gets split and put into multiple Purchase Orders it might get split differently later on.  And same when a Purchase Order Line Item is processed into Shipping Containers.  I can't for the life of me how to best handle this operation.

If it helps, here are the tables I have so far with some details excluded for brevity:

CREATE TABLE PurchaseOrder (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    PurchaseOrderNumber VARCHAR(15) UNIQUE,

    PRIMARY KEY(Id)
);

CREATE TABLE Container (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    ContainerName VARCHAR(20) UNIQUE,
    PurchaseOrderId INTEGER UNSIGNED,

    PRIMARY KEY(Id),
    FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
);

CREATE TABLE SalesOrder (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    ClientId INTEGER UNSIGNED,
    SalesOrderNumber VARCHAR(10),

    PRIMARY KEY(Id),
    FOREIGN KEY(ClientId) REFERENCES Client(Id)
);

CREATE TABLE SalesOrderLineItem (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    SalesOrderId INTEGER UNSIGNED,
    ProductId INTEGER UNSIGNED,
    Qty INTEGER,
    Price DECIMAL(5,2),
    Cost DECIMAL(5,2),

    PRIMARY KEY(Id),
    FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),
    FOREIGN KEY(ProductId) REFERENCES Product(Id)
);

CREATE TABLE PurchaseOrderLineItem (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    PurchaseOrderId INTEGER UNSIGNED,
    SalesOrderId INTEGER UNSIGNED,
    ProductId INTEGER UNSIGNED,
    ClientId INTEGER UNSIGNED,
    MfgId INTEGER UNSIGNED,

    PRIMARY KEY(Id),
    FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
    FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),
    FOREIGN KEY(ProductId) REFERENCES SalesOrder(Id),
    FOREIGN KEY(ClientId) REFERENCES Client(Id),
    FOREIGN KEY(MfgId) REFERENCES Mfg(Id)
);

I'm thinking to create additional association tables between Sales Order Line Item and Purchase Orders and for Purchase Order Line Item and Containers to keep track of this kind of back and froth splitting?

Help appreciated!

In general, be parsimonious with auto-generated keys.  If you need one for the user's convenience, OK, but you should still enforce the natural key.

For example, you have PurchaseOrder.  It has a unique key, externally provided.  use it.  Adding your own auto-generated value adds complexity and nothing else.

Similarly, SalesOrderLineItem.Id is unique throughout the table.  Wouldn't you rather see line 1, 2, 3 than 62783444, 62783445, 62783446?  That makes the primary key SaleOrderID, LineItem (instead of Id).  Same for PurchaseOrderLineItem, which then gains a key of PurchaseOrder, LineItem.

You mention "splitting" a few times; I take it you mean a given Sales quantity may be apportioned to more than one Purchase order and Shipping order.  Resist the temptation (if it comes) to use a trigger to enforce the relationship that, say, the sum total shipped is not more than was ordered.  That kind of question can be implemented in SQL, and should be, but not as a referential-integrity constraint.  Write a stored procedure to verify the order is "in bounds", and call it from your application at the appropriate time.  That will permit real-world anomalous situations such as lost shipments and botched orders.

Other than that, you seem to be shooting for 3NF, which is a good place to be.  If you keep following the 1:n relationships to define tables and enforce constraints rigorously, you'll likely end up with a sound design.


How do I track logins, connections, user accounts, queries, ...? I am a BI person who has been asked to step in and pretend I am a DBA.  I am not a novice with databases but AM a novice at the admin type stuff.

We have a SQL Server database that is going to be decommissioned.  I have been asked to determine who is using the server, the databases that are being used and the queries being run.

So far I've created a table to track server logins as well as check number of connections as referred to @ http://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/

However, once I had that info I was asked to track:

The user account or credentials being used to connect to the server
the database(s) being queried
The IP address or DNS name from which the connection was made
The SQL being used

They want to know specifically who is using any databases on the server so they can determine which need to be migrated and who they need to contact as the database is migrated.

Any help is GREATLY appreciated!

I would suggest you to run a server side trace - for your entire business life cycle. There might be cases where some reports are ran end of month or end of quarter.

So you will need below event in a server side trace 11,13,16,19,33,37,61,162 with these columns 1,6,8,10,11,12,14,27,35,40.

You can filter out stuff that you dont need using sp_trace_setevent (note that it is announced deprecated).

Alternatively you can use Extended events - Tracking SQL Server Database Usage.

Note: Currently in my environment, I am using Server side trace and its a very low impact. Also, I have a job that dumps the profiler info into a table for analysis.

You can have below table structure for loading trace data :

CREATE TABLE [dbo].[login_trace](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](300) NULL,
    [HostName] [varchar](300) NULL,
    [ApplicationName] [varchar](300) NULL,
    [DatabaseName] [varchar](200) NULL,
    [LoginName] [varchar](100) NULL,
    [StartTime] [datetime] NULL
) ON [PRIMARY]

You can adjust it as per your requirement.


Dynamically changeable database We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear for me how can I use that.

You can use symbolic links with MySQL. All you would need to do is make a symbolic link for the .frm, .MYI and .MYD files of your MyISAM tables into the MySQL database schema directory that you want. After that, run flush tables; and they should show up.

It is also important that the MySQL user is able to access those files, so you might have a bit of an issue with user rights.

Also, depending on your MySQL version, there maybe a setting in the my.cnf which enables/disables symbolic links, so you would need to keep an eye on that too.


To take a full backup and differential backup of all databases Can anyone help me with a script to take a full or complete backup of all databases once in a week and to take differential backups daily. ?

You should look at Ola Hallengren's scripts

https://ola.hallengren.com/

The FAQ has examples on using these scripts with SQL Express.

https://ola.hallengren.com/frequently-asked-questions.html


Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK I wrote this script without COMMIT in a new Query window (Query1):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3

and in another query window I write this script without COMMIT (Query2):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 1

Everything is OK, when I run Query1 and then Query2 I can select row with RegioID=1. But if I write the first query this way (Query3):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3 OR RegionID = 4

...and add RegionID = 4 in result, I can't run Query2. Why wheras Query2 result does not has intersect with Query3 I can't run it?

The 3rd query has an OR which most likely means that a scan is happening. The scan will be blocked by RegionID = 1

If you run this, it should run OK.

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 4

SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3

The alternative is that you still have query 1 transaction open...

Edit:

I can't reproduce it (SQL Server 2008 R2 x64 developer). I get an index seek on query 3.

However, in my first iteration I forgot a PRIMARY KEY on my table and query1 blocked query2 because it was a table scan. Which backs up my answer above mostly

Edit 2:

I can't get either Query 2 or Query 3 to block each other (no matter which runs first)

After comments:

A scan places an XLOCK per scanned row
OR is often non-SARGable: AND is OK. (OR has some optimisations) though)


Why does Postgresql Data directory revert to default after updates/reboots? I have something that is pretty simple to explain. We have certain server builds as it relates to directories in our CentOS boxes at my company and so to accommodate those, I have a script that changes the PostgreSQL data directory. However, there seem to be some occasions where it reverts back to the default value. I know one such instance was after our IT department did some upgrades and then rebooted. I can't say for sure what exactly does this, but let me show you the code that I use to change the data directory and maybe someone can figure out what I am doing wrong.
sed -i "s/Environment=PGDATA=\/var\/lib\/pgsql\/11\/data/Environment=PGDATA=\/usr\/local\/pgsql\/data/" /usr/lib/systemd/system/postgresql-11.service

systemctl daemon-reload

systemctl restart postgresql-11

There is more, but that is what changes the data directory. Any ideas why this randomly will switch back to /var/lib/pgsql/11/data and how I can prevent this from changing back? My guess is that I need to change it somewhere other than just the service file, but I have yet to find out where else I need to change it.
I guess it could help if I provided some information. This is on a CentOS 7 box, with Postgresql 11.

After looking up some more details. It seems that the better options is create an override file in /etc/systemd .
Though, I cannot vouch for if this is best practice, I believe that it is a better option since I do not have to edit the default file.
dir=/etc/systemd/system/postgresql-11.service.d
if [ ! -d $dir ]
then
    mkdir $dir
    chmod 755 $dir
fi
cd $dir
touch override.conf
echo '[Service]' >> override.conf
echo 'Environment=PGDATA=/usr/local/pgsql/data' >> override.conf
systemctl daemon-reload
systemctl restart postgresql-11

I know normally this is done via systemctl edit, but from what i can tell, this does the same thing, just without using systemctl. This DID work and it allowed postgres to work even though the data directory in /usr/lib/systemd/system/postgresql-11.service does not have a working data directory in it. If someone has a better answer, I can gladly accept that though.
I arrived at this solution based off
https://pgstef.github.io/2018/02/28/custom_pgdata_with_systemd.html


Combining multiple-row data into a single row I have a table that has columns like following

ID  Number  Value1  User1  Value2  User2  Value3  User3
--  ------  ------  -----  ------  -----  ------  ------
1   123     0       Jack
1   123                    1       TOM
1   123                                   2       Tim
1   456     0       Jones
1   456                    1       Jim
1   456                                   2       Carter

I need result like the following,

ID  Number  Value1  User1  Value2  User2  Value3  User3
--  ------  ------  -----  ------  -----  ------  ------
1   123     0       Jack   1       TOM    2       Tim
1   456     0       Jones  1       Jim    2       Carter

Can anyone please help me out on this?

The column ID remains same. The columns Value and User will go till Value6 and User6 and each row has values like one below the other. I need to combine all of them and show it as a single row.

Simple Group by will work for your expected output

select
columnid,
Number,
max(Value1) as value1, max(User1)as user1 , max(Value2) as value2,  max(User2) as user2,max(Value3) as value3,max(User3) as user3
from
table
group by
columnid,
number