Missing mysql, sys, performance_schema I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database up and working and forgot it.
Fast forward to now, I just did a backup up my whole data-dir using percona's xtrabackup and transferred it to a new server. Copy the backup into my data-dir and attempt to start mysql.  Fails with
Tablespace 1, name 'sys/sys_config', file './sys/sys_config.ibd' is missing!

mysqld: Can't create/write to file './performance_schema/cond_instances_2201.sdi' (OS errno 2 - No such file or directory)
mysqld: Can't create file './performance_schema/cond_instances_2201.sdi' (errno: 2 - No such file or directory)

I look at the the data-dir (/var/lib/mysql) and while I notice my main database folder, I don't see a sys or performance_schema folder. Curiously I do see a mysql folder but its full of files called slow_log and general_log.
My first thought is the backup failed so I checked the old server and it has the exact same thing! However, I do have a mysql.ibd on the old server. So I run show databases and it shows all 3 databases (sys, performance_schema, mysql). I try to go to sys/sysconfig and it complains about the table space but I am able to go into mysql database tables and perofrmance_schema tables.
Something is definitely up and I read people suggest doing an upgrade and mysql can self correct missing main tables (mysql_upgrade is deprecated so I must use mysqld and force it). I am scared to run update on my current server so I go back to the new server (the one I attempted to restore from backup)
I rename /var/lib/mysql/mysql to /var/lib/mysql/backup and execute sudo -u mysql mysqld --upgrade=FORCE --skip-grant-tables --skip-networking
and get in the error log
/usr/sbin/mysqld (mysqld 8.0.21-12) starting as process 4000
2020-12-13T23:41:31.040574Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-13T23:41:31.279802Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-13T23:41:31.281181Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci' failed with error code = 1049, error message = 'System schema directory does not exist.'.
2020-12-13T23:41:31.281565Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-12-13T23:41:31.281986Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-12-13T23:41:32.579767Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-12)  Percona Server (GPL), Release '12', Revision '7ddfdfe'.

So I am not sure what to do, I would prefer to get my new server up and running instead of messing with my old server because it's currently live and being used.
I have innodb table per file
Should I expect to see a mysql folder inside my data-dir that looks to be slow_log and general_log backups? Will the sys,performance_schma,mysql databases have their own folder in the data-dir?
I wish there was a way to tell mysql to disregard the sys,mysql,performance_schema tables and re-create them, I thought that's what the upgrade option would do but it fails
Edit 12/23/2020
I think its problem that only shows up with the newest mysql. The backup was done on Percona Mysql 8.0.19-10  and the failed restore was attempted on Percona Mysql 8.0.21-12.  Normally I wouldn't think it was the difference in versions...
However, I just happen to upgrade the percona mysql to 8.0.22-13 on the the one that was previously 8.0.19-10 and the exact same issue occured. I believe the newer mysql version is more picky and fails to load if sys_config.ibd is missing where as 8.0.19 had no issues
Edit 12/24/2020
I uninstalled percona 8.0.21-12 on the target server and installed 8.0.19-10. Restored the backup with no issues (despite missing sys folder, etc). While the problem of missing a sys folder is still an issue it seems to only prevent startup with 8.0.2x

If you see the slow log and general log in /var/lib/mysql/mysql, it means that in the config files for the old server, these logs were specified without directories, and that in the old server, the datadir is actually configured to be /var/lib/mysql/mysql, and not the default /var/lib/mysql. That would explain the appearance of the logs there. Check the old servers .cnf file and make sure the datadir matches before attempting the restore again.
After you figure out where the correct datadir was on the old server, match that in the new server, and do the restore. Be sure to set ownership after restoring: "chown -R mysql:mysql /var/lib/mysql" (or wherever the datadir ended up)
Also, keep in mind that mysql_upgrade or update do not recreate much from scratch. They mostly modify system objects to match the new version, and leave others intact, so if something is outright missing (like an entire system schema) it probably won't work.


How can I check the path of my.ini, through the command line? I would like to know, if possible, how to display (using the command line) the path to my.ini used by the server.

I am using MySQL 5.6 inside a windows IIS.

I have the following:

Program Files\MySQL\MySQL Server 5.6\my-default.ini
C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

For sure, the server is using the second one. Is the path stored in any variable inside mysql server variables?
I was checking some system variables but I couldn't find it.

Any ideas?

UPDATE
After Ronaldo's post I ran a --help and this is what I found:

...
Default options are read from the following files in the given order:
C:\Windows\my.ini
C:\Windows\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.6\my.ini
C:\Program Files\MySQL\MySQL Server 5.6\my.cnf

The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
...

The strange thing is that I don't have none of these files. I sit to display hided files on each folder.

So, I took a look to this post and I followed the steps.
Finally I arrived at the window MySQL56 Properties(Local Computer).
I could read:

Path to executable:
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file

BUT,
if we click over it with the mouse's right button and choose Select All, this is what we have:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe"
--defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

So that replies my question.

mysqld.exe does not report the location of the my.ini it is using. I learned over the years that the my.ini file is supposed to be in the parent folder of the data and bin directories:

Feb 06, 2012 : MySQL Database and Table Loss
Jan 14, 2014 : MySql InnoDB tables are missing after restarting (See INVESTIGATION #2)

What can you do form the command line ?

From the command line, run this

C:\> mysqld --help --verbose > C:\mysqld_options.txt
notepad C:\mysqld_options.txt

Go look for the line that says

Default options are read from the following files in the given order:

The line underneath will tell you the places where mysqld looks for my.ini

If you don't see one, then use the parent folder of the data and bin directory to make one.

Give it a Try !!!


How to use the dbms_sql to get the analyze for insert into statement declare
 v_cursor number;
 stmt varchar2(2000);
 s varchar2(40);
 cursor cc1 is select voc.SQL_ID from v$open_cursor voc left join v$sql vs on voc.SQL_ID=vs.SQL_ID  where voc.SID in(select sid from v$mystat) and to_char(substr(vs.SQL_FULLTEXT,1,2000)=stmt;
begin
delete from table1;
v_cursor:=dbms_sql.open_cursor;
stmt:='inset into table2 (column1,column2) values(''a'',''b'')';
dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
insert into table1 select * from tables(dbms_xplan.display
open cc1;
loop
fetch cc1 into s;
exit;
end loop;
close cc1;
insert into table1 select * from(dbms_xplan.display_cursor(s,0,'ALL'));

I execute this and select  from the table1 but the record in table1 is tells me:

  it could also be that the plan is no longer in cursor cache(check v$sql_plan).

But if I change the stmt to update statement and execute, the record in table1 is correct.
So i want to know where can I get the analyze about the insert statement.

The child_number argument to dbms_xplan.display_cursor needs to be taken from v$sql as well, it's not necessarily zero. (I'm not exactly sure what influences that number, but dropping and recreating table2 in your example gives me a new child_number each time).

Here's a modified version of your procedure (fixed a few typoes in what you have above):

DECLARE
  v_cursor NUMBER;
  stmt     VARCHAR2(2000);
  s        VARCHAR2(40);
  cn       NUMBER;
  CURSOR cc1
  IS
    SELECT voc.SQL_ID, vs.child_number
    FROM v$open_cursor voc
    LEFT JOIN v$sql vs
    ON voc.SQL_ID  =vs.SQL_ID
    WHERE voc.SID IN (SELECT sid FROM v$mystat)
      AND TO_CHAR(SUBSTR(vs.SQL_FULLTEXT,1,2000))=stmt;
BEGIN
  DELETE FROM table1;
  v_cursor:=dbms_sql.open_cursor;
  stmt    :='insert into table2 values (''a'',''b'')';
  dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
  OPEN cc1;
  LOOP
    FETCH cc1 INTO s, cn;
    EXIT;
  END LOOP;
  CLOSE cc1;
  -- To inspect the child number:
  -- dbms_output.put_line(s || ' '|| cn);
  INSERT INTO table1 SELECT * FROM table(dbms_xplan.display_cursor(s,cn,'ALL'));
END;
/


What happens to mysql information_schema after switching to innodb_file_per_table? We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box.

We've backed all our dbs up and about to set the per_table_file flag drop all the dbs and re-import, but what will happen to the information_schema database in the original mysql data file, since this db wont be dropped and recreated, it wont be in the per table/file format, will we still free up space from our original huge datafile, or will this remain the same size?

The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing.

I wrote about this 5 years ago : How is INFORMATION_SCHEMA implemented in MySQL?

If you performed a mysqldump of the infomration_schema, reimporting it will do nothing.

mysqld will correct and protect any outside manual attempts to change it.

For example, suppose you wanted to create a database called mynewdb and your datadir is /var/lib/mysql. if you ran the following commands

cd /var/lib/mysql
mkdir mynewdb
chown mysql:mysql mynewdb

This will create a database in the eyes of mysqld.

mynewdb shows up in INFORMATION_SCHEMA.SCHEMATA

You can verify this by running the following:

mysql> SHOW DATABASES;
mysql> SELECT * FROM information_schema.schemata;

The database mynewdb will be present

As for your actual question, don't worry. You cannot break the INFORMATION_SCHEMA. mysqld won't let you.


Decimal or Point Data Type for storing Geo location data in MySQL I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options:

1) 2 columns for Each Address:
Latitude DECIMAL(10, 8),
Longitude DECIMAL(11, 8)

2) One Column for Each Address:
GeoLocation POINT

My program need to find all Address that fall under 1 Kilometer Radius from current Location (Mobile Client).

Also below command does not work for me, not sure what I need to have for this.

CREATE SPATIAL INDEX GeoLocation ON tblAddress (GeoLocation);

So what is the suggested approach to store data for faster calculation and retrieval here. Please advise.

You could use MySQL's spatial extensions with GIS.

In a code example from Google displaying points on a map, they state:

  When you create the MySQL table, you want to pay particular attention
  to the lat and lng attributes. With the current zoom capabilities of
  Google Maps, you should only need 6 digits of precision after the
  decimal.

  To keep the storage space required for our table at a minimum, you can
  specify that the lat and lng attributes are floats of size (10,6).
  That will let the fields store 6 digits after the decimal, plus up to
  4 digits before the decimal, e.g. -123.456789 degrees I wouldn't worry
  about performance differences between numeric types. Decent indices
  will have a far greater effect.

You could also try DECIMAL as @gandaliter suggest.

  DECIMAL is the MySQL data-type for
  exact arithmetic. Unlike FLOAT its precision is fixed for any size of
  number, so by using it instead of FLOAT you might avoid precision
  errors when doing some calculations. If you were just storing and
  retrieving the numbers without calculation then in practice FLOAT
  would be safe, although there's no harm in using DECIMAL. With
  calculations FLOAT is still mostly ok, but to be absolutely sure of
  8d.p. precision you should use DECIMAL.

  Latitudes range from -90 to +90 (degrees), so DECIMAL(10,8) is ok for
  that, but longitudes range from -180 to +180 (degrees) so you need
  DECIMAL(11,8). The first number is the total number of digits stored,
  and the second is the number after the decimal point.
  In short: lat DECIMAL(10,8) NOT NULL, lng DECIMAL(11,8) NOT NULL
  This explains how MySQL works with floating-point data-types.

Related information:

MySQL and GIS, GeoIP: FAQs, How-To, Articles, Blogs
Creating a Store Locator with PHP, MySQL & Google Maps
Using the new spatial functions in MySQL 5.6 for geo-enabled applications


SQL Server 2012 enlarge database space I tried to enlarge a database size of database instance on my SQL Server 2012.
I manually added a new .MDF file under database instance

After restart on shrink database dialog I can see available space

on shrink files dialog the available free space is added

but when I executed following SQL query the available space still small

select
    name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
   , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from
    dbo.sysfiles a

The database still see only two files ....

What I did wrong ?

[Edit] If just statistics shown wrong in query, it's OK for me. My question is the whole process to add space to the database made in the right way or I need to add something to finish the process? Sorry for editing later

Thanks for any help

Referring to DBCC UPDATEUSAGE (Transact-SQL):

Update the stats - sysviews are out of sync.

It seems the system views are not always accurate, and sp_spaceused is notorious for this. A form of caching is applied to non-critical, but heavy process data collection functions - like measuring partitions (like what you just created another of) and allocations.

Examining sp_spaceused (Transact-SQL), we see this:

  When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Try running:

EXEC sp_spaceused @updateusage = N'TRUE';


postgreSQL update set only where cast is possible and ignore error? I have 2 columns in a PostgreSQL table.  The mac_address_temp column is for migration from character type to MAC-address type:

mac_address      | macaddr               |
mac_address_temp | character varying(17) |

I want to migrate data from mac_address_temp to mac_address, but there is some data that can't be cast as macaddr type.

mac_address | mac_address_temp
-------------+------------------
            | AAB5:4f27:e299
            | AAB54f27e299

UPDATE mactable SET mac_address = CAST(mac_address_temp as macaddr);
ERROR:  invalid input syntax for type macaddr: "AAB5:4f27:e299"

Is there a way to ignore where CAST doesn't work and still update rest of the table?

Just remove the ':' characters and the update will work:

UPDATE mactable SET mac_address = CAST(replace(mac_address_temp,':','') as macaddr);

To get rid of everything that's not a hex digit:

postgres=# insert into mactable (mac_address_temp) values ('AAB5:-4f-27:e2-99');
INSERT 0 1
postgres=# select regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g')
postgres-# from mactable;
 regexp_replace
----------------
 AAB54f27e299
 AAB54f27e299
(2 rows)

postgres=#

So the update would be:

UPDATE mactest SET mac_address = CAST(regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g') as macaddr);


SQL Server 2014 In Memory OLTP - Garbage Collection on Transaction Rollback In SQL Server 2014, In Memory OLTP, What happens, if a transaction is rolled back and newly created version of row is no more required. Is it duty of garbage collector to remove even such type of rows or such garbage will be collected run time on transaction rollback ?

My understanding from the SIGMOD paper Hekaton: SQL Server’s Memory-Optimized OLTP Engine is that it gets handled the same as other types of garbage.
A couple of relevant sections are

6.2.3 Transaction Rollback
Transactions can be rolled back at user request or due to failures in commit processing. Rollback is achieved
by invalidating all versions created by the transaction and clearing
the end-timestamp field of all versions deleted by the transaction. If
there are any other transactions dependent on the outcome of the
rolled-back transaction, they are so notified. Again the write-set of
the transaction is used to perform this operation very efficiently.

Note that it says invalidated not garbage collected.
Also see the Garbage Collection section (emphasis mine)

8.1.1 GC Correctness
First, care must be taken to identify which versions might be garbage. Potential garbage versions may be created
by one of two processes. First, a version becomes garbage if a) it
was deleted (via explicit DELETE or through an UPDATE operation) by a
committed transaction and b) the version cannot be read or otherwise
acted upon by any transaction in the system. A second, and less common
way for versions to become garbage is if they were created by a
transaction that subsequently rolls back. The first and most important
property of the GC is that it correctly determines which versions are
actually garbage. The visibility of a version is determined by its
begin and end timestamps. Any version whose end timestamp is less
than the current oldest active transaction in the system is not
visible to any transaction and can be safely discarded. A GC thread
periodically scans the global transaction map to determine the begin
timestamp of the oldest active transaction in the system. When the GC
process is notified that it should begin collection, transactions
committed or aborted since the last GC cycle are ordered by their end
timestamps.

But in practice it does seem the garbage from rollbacks is dispensed with quicker than for commits so perhaps the aborting thread does clean up its own garbage at least some of the time.
To see this I created the following proc (in a memory optimised database)
CREATE PROC GetStats
AS
SELECT *
FROM   sys.dm_db_xtp_table_memory_stats
WHERE  object_id = object_id('dbo.Test')

SELECT name AS 'index_name',
       s.*
FROM   sys.dm_db_xtp_hash_index_stats s
       JOIN sys.indexes i
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
WHERE  i.object_id = object_id('dbo.Test')

SELECT name AS 'index_name',
       s.index_id,
       scans_started,
       rows_returned,
       rows_expired,
       rows_expired_removed
FROM   sys.dm_db_xtp_index_stats s
       JOIN sys.indexes i
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
WHERE  object_id('dbo.Test') = s.object_id;

And then ran the following, experimenting with toggling the ROLLBACK and COMMIT
CREATE TABLE [dbo].[Test]
  (
     [Id]     [INT] NOT NULL CONSTRAINT PK PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 8),
     [String] CHAR(900) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX IX NONCLUSTERED HASH WITH (BUCKET_COUNT = 8)
  ) WITH ( MEMORY_OPTIMIZED = ON )

INSERT INTO [dbo].[Test]
VALUES
    (1,REPLICATE('A',900)),
    (2,REPLICATE('A',900)),
    (3,REPLICATE('A',900)),
    (4,REPLICATE('A',900)),
    (5,REPLICATE('A',900)),
    (6,REPLICATE('A',900)),
    (7,REPLICATE('A',900)),
    (8,REPLICATE('A',900))

/*Get initial position*/
EXEC GetStats

BEGIN TRAN
    UPDATE [dbo].[Test]
    SET    [String] = REPLICATE('B', 900)

/*Mid Transaction*/
EXEC GetStats

COMMIT

/*Force a scan of both indexes*/
SELECT COUNT(*)
FROM   dbo.Test WITH (INDEX=IX)
EXCEPT
SELECT COUNT(*)
FROM   dbo.Test WITH (INDEX = PK)

EXEC GetStats

DROP TABLE [dbo].[Test]

I consistently got results summarized as follows.
The effect of the UPDATE was the same for both committed and rolled back transactions on memory_allocated_for_table_kb (Grew from 7KB to 15KB)
Bucket stats
The initial bucket stats for both tests was the same with
+------------+--------------------+--------------------+------------------+------------------+
| index_name | total_bucket_count | empty_bucket_count | avg_chain_length | max_chain_length |
+------------+--------------------+--------------------+------------------+------------------+
| IX         |                  8 |                  7 |                8 |                8 |
| PK         |                  8 |                  7 |                8 |                8 |
+------------+--------------------+--------------------+------------------+------------------+

Mid transaction (before rollback or commit) the bucket counts are
+------------+--------------------+--------------------+------------------+------------------+
| index_name | total_bucket_count | empty_bucket_count | avg_chain_length | max_chain_length |
+------------+--------------------+--------------------+------------------+------------------+
| IX         |                  8 |                  6 |                8 |                8 |
| PK         |                  8 |                  7 |               16 |               16 |
+------------+--------------------+--------------------+------------------+------------------+

Following the rollback they instantly revert to the initial count but after the commit they remain as in the second table showing that garbage is now present.
The single bucket that apparently all the PK values hash to now has 16 linked rows rather than the previous 8.
And now the hash index on the other column has two buckets in use with 8 linked rows in each (for both the "before" and "after" string values of AAA... and BBB...)
Rows Expired
After Commit
+------------+----------+---------------+---------------+--------------+----------------------+
| index_name | index_id | scans_started | rows_returned | rows_expired | rows_expired_removed |
+------------+----------+---------------+---------------+--------------+----------------------+
| IX         |        2 |             1 |             8 |            8 |                    0 |
| PK         |        3 |            10 |            16 |            8 |                    0 |
+------------+----------+---------------+---------------+--------------+----------------------+

After Rollback
+------------+----------+---------------+---------------+--------------+----------------------+
| index_name | index_id | scans_started | rows_returned | rows_expired | rows_expired_removed |
+------------+----------+---------------+---------------+--------------+----------------------+
| IX         |        2 |             1 |             8 |            8 |                    8 |
| PK         |        3 |            10 |            16 |            8 |                    8 |
+------------+----------+---------------+---------------+--------------+----------------------+

The rows_expired is 8 in both cases but rows_expired_removed is 0 following the transaction commit whereas all of them were removed after the rollback.


SQL Server 2012 database 3 mirroring questions I've been looking for the answer to these three questions but just cant find a straight answer..

When mirroring with a witness, does all three SQL Server editions have to have the same name? I.E at the moment I have 3 servers labeled ABC-SERVER1, ABC-SERVER2, ABC-SERVER3. My main server is called SQL-SERVER3 on server3, would I then have to have the named instance SQL-SERVER3 running on ABC-SERVER2 and 1?
If the SQL Server instances don't have to be labeled all the same i.e they can be (SQL ABC-SERVER1), (ABC SQL-SERVER2) etc... If you are using a program for example MS Access and you connect to a server for example server3, how would access know where to go if server3 was offline?
As I understand it you can have 2 SQL Server versions running at any one time from the 1 licence, as long as only 1 instance of SQL Server is in use at any one time. To get a witness running I would have to use a SQL Server Express edition or buy another licence, is that correct?

Thanks in advance for taking your time out to read this....

You can use SQL Server Express for the witness.

You only need a single license so long as you never use the mirror server for anything other than a mirror, and only failover at most once in 90 days.

The names of the servers, and the names of the SQL instances are irrelevant.

Access can somewhat automatically use the failover server as long as the connection string for the linked tables uses the SQL Native Client and the correct syntax:

Failover Partner=myMirrorServerAddress;

See http://www.connectionstrings.com for more info.


Evaluate different EXPLAINs on Redshift I'm trying to understand EXPLAIN on Redshift. I'm in a case were I have data like this

id | user_id | created_at
---|---------------------------------
1  | 1       | 2017-02-08 14:32:10.96
2  | 1       | 2017-02-07 14:32:10.96
3  | 2       | 2017-02-06 14:32:10.96
4  | 2       | 2017-02-05 14:32:10.96

I want :

id | user_id | created_at
---|---------------------------------
1  | 1       | 2017-02-08 14:32:10.96
3  | 2       | 2017-02-06 14:32:10.96

I have this two queries :

SELECT id,
       user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM my_table) x
WHERE rownum = 1;

With the EXPLAIN  have :

XN Subquery Scan x  (cost=1000001263779.68..1000001513986.60 rows=50042 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=1000001263779.68..1000001388883.14 rows=10008277 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Sort  (cost=1000001263779.68..1000001288800.37 rows=10008277 width=16)
              Sort Key: user_id, created_at
              ->  XN Seq Scan on my_table  (cost=0.00..100082.77 rows=10008277 width=16)

Then the other query :

SELECT ac1.user_id, ac1.created_at FROM my_table ac1
JOIN
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM my_table
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE;

And the EXPLAIN :

XN Hash Join DS_DIST_NONE  (cost=150798.74..771939079.62 rows=7257 width=16)
  Hash Cond: (("outer".created_at = "inner".maxdate) AND ("outer".user_id = "inner".user_id))
  ->  XN Seq Scan on my_table ac1  (cost=0.00..100082.77 rows=10008277 width=16)
  ->  XN Hash  (cost=150606.01..150606.01 rows=38548 width=16)
        ->  XN Subquery Scan ac2  (cost=150124.15..150606.01 rows=38548 width=16)
              ->  XN HashAggregate  (cost=150124.15..150220.52 rows=38548 width=16)
                    ->  XN Seq Scan on my_table  (cost=0.00..100082.77 rows=10008277 width=16)

Result are a little bit slower with the first query but I'm lost when I try to understand EXPLAINs.
It seems that the cost is higher on the query that use ROW_NUMBER() but that's it same with rows.

But what can I extract from those EXPLAINs (I sadly cannot use ANALYZE on Redshift)?

Fabio has given a great answer.

However for Redshift, it is worth adding that how your data is physically laid out has a dramatic impact on EXPLAIN plan cost.

Create some dummy data:
(inspired by https://stackoverflow.com/questions/38667215/redshift-how-can-i-generate-a-series-of-numbers-without-creating-a-table-called)

drop table if exists #my_table;
create table #my_table as
select
    (row_number() over (order by 1)) - 1 as user_id
    ,(current_date - user_id::int)::timestamp created_at
from
    stl_load_errors
limit 24

Reproduce similar EXPLAIN plans to the question:

explain
SELECT user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM #my_table) x
WHERE rownum = 1

EXPLAIN:
XN Subquery Scan x  (cost=1000000000000.79..1000000000001.39 rows=1 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=1000000000000.79..1000000000001.09 rows=24 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Sort  (cost=1000000000000.79..1000000000000.85 rows=24 width=16)
              Sort Key: user_id, created_at
              ->  XN Network  (cost=0.00..0.24 rows=24 width=16)
                    Distribute
                    ->  XN Seq Scan on "#my_table"  (cost=0.00..0.24 rows=24 width=16)

Next:

explain
SELECT ac1.user_id, ac1.created_at FROM #my_table ac1
JOIN
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM #my_table
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE

EXPLAIN:
XN Hash Join DS_DIST_INNER  (cost=0.72..822858.77 rows=13 width=16)
  Inner Dist Key: ac1.user_id
  Hash Cond: (("outer".maxdate = "inner".created_at) AND ("outer".user_id = "inner".user_id))
  ->  XN Subquery Scan ac2  (cost=0.36..0.66 rows=24 width=16)
        ->  XN HashAggregate  (cost=0.36..0.42 rows=24 width=16)
              ->  XN Seq Scan on "#my_table"  (cost=0.00..0.24 rows=24 width=16)
  ->  XN Hash  (cost=0.24..0.24 rows=24 width=16)
        ->  XN Seq Scan on "#my_table" ac1  (cost=0.00..0.24 rows=24 width=16)

This is indeed much faster but data is distributed between nodes (DB_DIST_INNER).

Now try:

drop table #my_table_dist;
create table #my_table_dist
distkey(user_id) sortkey(user_id,created_at) as
select
    (row_number() over (order by 1)) - 1 as user_id
    ,(current_date - user_id::int)::timestamp created_at
from
    stl_load_errors
limit 24

Now run EXPLAIN:

explain
SELECT user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM #my_table_dist) x
WHERE rownum = 1

EXPLAIN:
XN Subquery Scan x  (cost=0.00..0.78 rows=1 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=0.00..0.48 rows=24 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Seq Scan on "#my_table_dist"  (cost=0.00..0.24 rows=24 width=16)
 The data is already sorted and distributed so Redshift simply reads off the answer.

Whilst:

explain
SELECT ac1.user_id, ac1.created_at FROM #my_table ac1
JOIN
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM #my_table_dist
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE

EXPLAIN:
XN Hash Join DS_DIST_INNER  (cost=0.36..822858.77 rows=13 width=16)
  Inner Dist Key: ac1.user_id
  Hash Cond: (("outer".maxdate = "inner".created_at) AND ("outer".user_id = "inner".user_id))
  ->  XN Subquery Scan ac2  (cost=0.00..0.66 rows=24 width=16)
        ->  XN GroupAggregate  (cost=0.00..0.42 rows=24 width=16)
              ->  XN Seq Scan on "#my_table_dist"  (cost=0.00..0.24 rows=24 width=16)
  ->  XN Hash  (cost=0.24..0.24 rows=24 width=16)
        ->  XN Seq Scan on "#my_table" ac1  (cost=0.00..0.24 rows=24 width=16)
Note no difference in cost due to distribution of data between nodes (DB_DIST_INNER).


Duplicating database on the same server without data loss I want to duplicate an innodb database on my mysql server for testing another version of the software.
Using tools for example phpmyadmin reduce the database around 20 MB and a "lot" of rows are missing.

Just copying the folder under /var/lib/mysql won't work, because I didn't enabled something special for the innodb engine.

How can I create a duplicate of an innodb database?

a "lot" of rows are missing

Your interests would be best served if you'd investigate and come up with an explanation of why this is happening.  It's not acceptable for a tool or utility to behave mysteriously, so either the tool has a defect, or isn't intended to be used as you are using it, or there is a quirk in your database structure that is interacting with something in the utility that you're using to cause these problems.

As a DBA, I like to be as "operationally close" to the server as possible, without tools making decisions and being "helpful."  I use a graphical query browser for only three things:  Running queries where I want to scroll through the results, editing stored programs, and adding comments to large numbers of undocumented table columns.  Everything else, I do by hand because I like to understand exactly what the server is being asked to do, and I like the server to understand me, as well.  So, here's how I do what I think you are describing.

I assume you're talking about cloning a schema on a single server, to a different schema on the same server.

To state it procedurally, "for each table (or other object) in `prod_db`, create an identically named object in `dev_db` including all of the indexes, foreign key constraints referencing the appropriate table in the new schema, and, optionally, copy all the data."

Here's how I do that.

$ mysqldump [options*] --databases prod_db | perl -pe 's/`prod_db`/`dev_db`/g' > dump.sql

This generates a human-readable dumpfile, with every instance of `prod_db` (including the backticks) replaced with `dev_db`.  Perl handles that filtering as the dumpfile passes through the | pipeline.  Then I review the file's contents for sanity and pipe it right back in the mysql:

$ mysql < dump.sql

Done.

Now for the details.

If I take the first few lines of the file generated by this command:

$ mysqldump [options] --databases sakila

I see this near the beginning of the file:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `sakila`;

Now this:

$ mysqldump --databases sakila | perl -pe 's/`sakila`/`dolphin`/g'

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dolphin` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `dolphin`;

Magic.  This process carries one major caveat:  you can't have a table with the same name as the original database, because the table name `sakila` will be changed to `dolphin` just like the database name was changed.

*options for mysqldump are very important here, because if you make a mistake, you run the risk of trashing your live database.  This option is particularly useful.

--skip-add-drop-table

If you do a normal mysqldump, before each CREATE TABLE in the resulting file, there's a DROP TABLE IF EXISTS statement.

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actor` ( ...

When you add the --skip-add-drop-table to mysqldump it does write the DROP TABLE IF EXISTS statement into the dumpfile, so there is no danger of dropping the wrong table.  The worst case is that it will try to create a table that already exists, and if that happens, the script will halt.  Then you can figure out what the problem was.

You should also add the --verbose option to mysqldump because it shows you progress as the dump is proceeding.

--events --triggers --routines

If you have MySQL Event Scheduler events, or triggers, or stored functions/procedures ("routines") these flags will ensure that mysqldump copies those, as well.  You need to review them, however, to be sure that any references to the schema are correct, because the schema name may not be properly backticked in these.  That shouldn't be a major problem, because you don't need to reference the schema when defining these -- they run in the context of the schema in which they are defined.

--no-data

Use this option if you only want to clone the structures, not the rows in the tables.  You can also use this option for a dry run to examine the resulting "dump.sql" file for sanity.  Remember, these files contain nothing more or less than the SQL statements needed to recreate a database, and are therefore, human-readable.

--single-transaction

You want to use this when the tables you're dumping are InnoDB because it won't lock the tables or block other operations, but it will ensure that the data you get is consistent from table to table, because it issues START TRANSACTION WITH CONSISTENT SNAPSHOT against the original server before extracting the dump file.

What's Perl doing here?

perl -pe 's/`prod_db`/`dev_db`/g'

What we're essentially saying is perl, -e execute the following mini-script (in quotes) and -p pretend that I've wrapped this instruction in a loop that says "read from standard in", "apply changes to data", "print resulting data to standard out".  s/ substitute, when you see `prod_db` replace / it with `dev_db` and / do this g globally (instead of only replacing the first occurrence on any given line, replace every occurrence on every given line).  Perl does this replacement very efficiently and is on virtually every system, and freely available for systems where it isn't installed, including Windows.  You could do the same thing with sed, and the command would be similar, though not identical.

The steps above are essentially how I perform this operation when my developers need an entire schema cloned to the same server but with a different schema (database) name.


Is dmp file suitable despite ORA-01555 'snapshop too old' warnings? Well, the title of the question is self-explanatory.

Export finished successfully with warnings.
Log shows several ORA-01555 snapshot too old errors.

- Is it OK to use that dmp file to perform an import full in another instance?

- Would running the export in restricted mode (no users connected) garantee no 'ORA-01555 snapshot too old' would occur?

Technically, you could, but it is incomplete, so better repeat the export after performing the necessary actions to avoid ORA-01555 errors (have an appropriately sized undo tablespace, set undo_retention and retention guarantee).


how to obtain the functionality of LISTAGG function in Oracle 10g? I need to put output of a query in single line so that I can create a single tar file of all the files. But my database is running on 10g version. LISTAGG would have been very useful in this case. How to acheive same output as LISTAGG without using it

example:

SQL> select col1 from table1;

col1

--------
file1

file2

file3

OUTPUT is required in following format,

col

--------
file1 file2 file3

I have prepared the following query to achieve this. Though there are number of ways.

SELCT col1 FROM table1;

COL1
-----
file1
file2
file3

SELECT
   RTRIM (XMLAGG (XMLELEMENT (c, col1 || ',')).EXTRACT ('//TEXT()'), ',') col1
FROM table1;

COL1
-----------------
file1,file2,file3

OR

SELECT SUBSTR (SYS_CONNECT_BY_PATH (col1, ','), 2) col1
      FROM (SELECT col1 , ROW_NUMBER () OVER (ORDER BY col1 ) rn,
                   COUNT (*) OVER () cnt
              FROM table1)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

Here is the dbfiddle with one more way to get the result.

Read more on String Aggregation Techniques


Database Security - Prevent column writing? I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products.

Am I able to prevent writing / editing of that column except by a specific user?

I'm in a rails app, if it matters.

I am concerned if someone gets in my DB

Well, don't let them.

  Am I able to prevent writing / editing of that column except by a specific user?

If your tables aren't owned by the user the application runs as, then yes, you can use column-level GRANTs to give partial access to tables.

So you'd GRANT everyone the right to use the other columns, and only grant one particular database user the right to edit that particular column.

However:

This only works with database users created with CREATE USER or CREATE ROLE. If your application does its own user management with ordinary database tables, PostgreSQL's permissions cannot be used.
Rails likes to have the application user own the tables so it can do migrations etc. You cannot restrict the rights of the owner.

... so in practice, I suspect that the answer will be "no, not with Rails, do it at the application level".

There's not really enough detail here to say more.


Remove Column From Replicated Article without re-snapshoting in 2008r2 I've inherited an undocumented 2008r2 Transactional Replication set-up.

There are close to 500GB of replicated articles with lots of hidden gotchas such as Indexed views being replicated to Tables.

The Development team need to Drop 1 column from a small lookup table, and Create a new column on another Table. In addition the Triggers on the 2 tables will be modified.

How can I accomplish this without re-taking the snapshot and copying all the data over again? (Am I on a fools errand? should I just start planning for a long weekend? the sooner I have a plan the better.)

The entire Publisher database isn't replicated - only selected articles. (So my snapshot options are limited, can't restore from backup)

I've done some research but can't find anything specific to 2008R2.

The article below talks about adding, dropping, and modifying columns in detail for 2008 R2 for various types of replication. I would start there, let me know if this is not what you were looking for.

  Replication supports a wide range of schema changes to published objects. When you make any of the following schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers:

  ALTER TABLE
  ALTER TABLE SET LOCK ESCALATION should not be used if schema change replication is enabled and a topology includes SQL Server 2005 or SQL Server Compact 3.5 Subscribers.ALTER VIEW
  ALTER VIEW
  ALTER PROCEDURE
  ALTER FUNCTION
  ALTER TRIGGER

  ALTER TRIGGER can be used only for data manipulation language [DML] triggers because data definition language [DDL] triggers cannot be replicated.

  You can use replication stored procedures to specify whether these schema changes are replicated. The stored procedure that you use depends on the type of publication.

Source


SQL trigger to get User ID column I would like to achieve the following:

Users log into a web app, using their credentials from the users table.

After inserting a row into the inserttest table, a trigger activates in which it creates a new row in the log_audit table, with one of the values as the userid of the user who is currently logged in.

Is it possible to script this entirely in T-SQL? I can't seem to get my trigger to work properly.

Webapp connects via service account. It only uses one login in SQL Server (the one that is used in the connection string in the webconfig).

This is what I have:

CREATE TABLE inserttest (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  name VARCHAR(20) NULL,
)

CREATE TABLE users (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  name VARCHAR(20) NULL
)

CREATE TABLE log_audit (
  logid INT PRIMARY KEY IDENTITY,
  userid INT NOT NULL,
  logdate DATE NULL
)

CREATE TRIGGER tgr_insert ON inserttest
AFTER UPDATE, INSERT
AS BEGIN
  INSERT INTO log_audit (userid, logdate)
  VALUES (@USERIDofCURRENTAPPUSERinUSERSTABLE, GETDATE)
END -- trigger script will not work of course

If the application connects to SQL Server with service account credentials instead of current user credentials, the code will need to set CONTEXT_INFO, SESSION_CONTEXT, or save the user name to a table keyed by session_id as described in this answer. The value can then be used in the trigger code.  SESSION_CONTEXT is the most elegant but is only available in SQL Server 2016 and SQL Azure Database.


Do primary keys change when indexes are rebuilt or reorganized? I have a database containing tables that each have auto-increment int columns serving as the primary key column.

When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?

Does it matter if the indexes are 'clustered'?

I'm using various versions of MS T-SQL from 2008 onward.

No, they will not.

Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.

However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.


How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job? I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this stored procedure, there is a query that selects data from ServerB.DatabaseB.SchemaB.TableB. There is a linked server set up from ServerA to ServerB with the security context set to: "Be made using the login's current security context". When the job executes the stored procedure and the stored procedure tries to select from the remote table the following error is thrown:

Executed as user: domain\user.
Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)
OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)
OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does
not allow remote connections.". [SQLSTATE 01000] (Error 7412).  The step failed.

I have made sure that the remote server allows remote connections and that the [domain\user] login has appropriate access to the remote server and database.

Does anybody have a resolution to this? It started out as a problem with trying to perform SQL authentication across the wire so we created the domain user and now I'm having this timeout issue.

edit

We now have named pipes enabled on the production servers and I am still getting an error.

Executed as user: &lt;domain&gt;\&lt;user&gt;. Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)  OLE DB provider
"SQLNCLI" for linked server "&lt;remote server&gt;" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI" for linked server "&lt;remote server&gt;" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". [SQLSTATE 01000] (Error 7412).  The step failed.

any ideas?

SQL Agent will use Shared Memory to connect to the local server.  The Instances will attempt to use named pipes to talk to each other.  You can adjust this by creating an alias in the SQL Server Client Network Utility (cliconfg.exe) and setting the alias to use TCP.


DB2 will not INSERT into Created Temp Table that I created I normally use SQL Server but for this project I'm having to learn DB2.  If I can get the below code to work I'm set for the whole project.  Below is a elementary example of what I cannot get to work.  All I want to do is make a Created Global Temp Table (CGTT) and append data to it.  The same code below will work if I use a Declared Global Temp Table, but for my purposes I must have a CGTT.

CREATE GLOBAL TEMPORARY TABLE TEST_TBL(
    KEY_ID BIGINT,
    SOMETEXT VARCHAR(10)
);

INSERT INTO USERID.TEST_TBL
VALUES(123456,'TEST TEST');

SELECT * FROM USERID.TEST_TBL;
SELECT COUNT(*) FROM USERID.TEST_TBL;

The above executes fine with no errors.  When the insert is executed it even tells me "1 row updated".  However, the select * and count(*) both give me 0 results;  I'm wondering if this is a permissions issue, but can I have permissions to create a CGTT but not insert to it??

Any help provided would be most appreciated.
Thanks in advance.

By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table.

You should either create the table using the ON COMMIT PRESERVE ROWS option, or disable autocommit while running your commands and issue an explicit COMMIT when you are done. Which option you choose depends on your business logic.


Scheduled stop of maintenance task I need something to automatically stop my index maintenance job on Saturday night. How do I do that? How can I check automatically if my index maintenance job is still running and if it's running, stop it every Saturday night before 12 am?

Right now I do it manually using:

USE msdb
GO
EXEC dbo.sp_stop_job
N'The name of the job'

How can I automate stopping the job at a certain time ?

Programmatically, you can use DATEName(DW, GETDATE()) to see if it is Saturday along with time and then have your sp_stop_job run to stop the job.

You can put that code in IF .. check saturday and time then run stop job code else exit gracefully.

It would be much better if you use Ola's Index maintenance solution (or Minnionware - I have not used it though, since I am using the former).

EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES',
        @FragmentationLow = NULL,
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh =  'INDEX_REORGANIZE',
        @FragmentationLevel1 = 5,
        @FragmentationLevel2 = 30,
        @UpdateStatistics = 'ALL',
        @OnlyModifiedStatistics = 'Y',
        @TimeLimit = 5400, -- 90 minutes, specified in seconds
        @LogToTable = 'Y',
        @Indexes = 'ALL_INDEXES',
        @SortInTempdb = 'Y'


Search results mismatch after postgresql update Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the absolutely identical configs, the search results changed drastically. The number of results decreased many times for some search queries. The rows added today (after update) were found each time, while some older ones cannot be found by a given phrase.

We use tsvector to perform full text search.

Any ideas what might be causing this?

Example query:

SELECT * FROM content, to_tsquery('bulgarian_utf8', '{keyword}') q
WHERE (tsv_title @@ q OR tsv @@ q) ) AS c

Your problem is probably due to the difference from 9.3.4 to 9.3.5, see the manual for that, where it states:

  E.2.1. Migration to Version 9.3.5

  A dump/restore is not required for those running 9.3.X.

  However, this release corrects a logic error in pg_upgrade, as well as an index corruption problem in some GiST indexes. See the first two changelog entries below to find out whether your installation has been affected and what steps you should take if so.

and below, the first 2 changelog entries:

  In pg_upgrade, remove pg_multixact files left behind by initdb (Bruce Momjian)

  If you used a pre-9.3.5 version of pg_upgrade to upgrade a database cluster to 9.3, it might have left behind a file $PGDATA/pg_multixact/offsets/0000 that should not be there and will eventually cause problems in VACUUM. However, in common cases this file is actually valid and must not be removed. To determine whether your installation has this problem, run this query as superuser, in any database of the cluster:

WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
  SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
         NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
         NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
         EXISTS (SELECT * FROM list WHERE file != '0000')
         AS file_0000_removal_required;

  If this query returns t, manually remove the file $PGDATA/pg_multixact/offsets/0000. Do nothing if the query returns f
  Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)

  This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.

  ...


Exclusion constraint on recursive foreign key (on the example of a Tree) I have the table with recursive foreign key. It's just hierarchical tree structure:

CREATE TABLE tree (
    id              INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    value           INTEGER NOT NULL,
    tree_id         SMALLINT NOT NULL CHECK (tree_id > CAST(0 AS SMALLINT)),
    parent_id       INTEGER REFERENCES tree ON DELETE RESTRICT ON UPDATE RESTRICT,

    EXCLUDE (tree_id WITH =) WHERE (parent_id IS NULL) -- allow only one root within tree
);

How to reject inserting records with non-null parent_id and different tree_id? I.e. foreign key parent_id can have reference only to primary key id within the same tree_id.

Use a composite foreign key:

(tree_id, parent_id) REFERENCES (tree_id, id)

You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)


Issue with MongoDB Replication Set I have 3 "CentOs 7" VM's in AWS environment in which I have setup MongoDB Repication Set using below command.

mongod --fork --logpath "/var/rs1/1.log" --replSet yogi --dbpath "/var/rs1/" --port 27017

Replication works fine, but when I restart any one of VM. Restarted VM comes out of replication group(I observed that mongo started without --replSet)

I placed above command in /etc/rc.local after giving +x permission and restarted the VM, I got the same issue.

If I run the same command after killing mongodb process it works fine.

What can be the issue here, how can I get VM to get added in Replica Set automatically after restart?

When I add the command in rs.local file I get below messages in logs

 Nov 17 10:20:09 13 rc.local: forked process: 889
 Nov 17 10:20:09 13 rc.local: ERROR: child process failed, exited with error number 1

I fixed the issue by adding below lines in /etc/mongod.conf

replication:
   replSetName: yogi


"undefined function pg_pconnect()" when connecting to Postgres from CodeIgniter I just installed Postgresql and pgadmin in my Ubuntu system. I can create a database from the pgadmin interface and everything seems ok, but when I try to connect to the database from my CodeIgniter application it cannot not connect. It shows an error like:

An uncaught Exception was encountered

  Type: Error

  Message: Call to undefined function pg_pconnect()

  Filename: /my codeigniter application
  path/system/database/drivers/postgre/postgre_driver.php

My database configuration is:

$db['postgres_db'] = array(
    'dsn'      => '',

    'hostname' => 'localhost',
    'username' => 'postgres',
    'password' => '1234',
    'database' => 'finance',
    'dbdriver' => 'postgre',
    'dbprefix' => '',
    'pconnect' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE,
    'port'     => 5432
);

How can I resolve this problem?

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'pgsql:host=localhost;dbname=Absences_DB',
    'username' => 'postgres',
    'password' => 'XXX',
    'database' => 'Absences_DB',
    'dbdriver' => 'pdo',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

Make sure you have the PDO driver enabled in PHP.


Clarification on Database Replication SQL Server 2008

I have to do database replication which is a new for me.

Goal is have mirror database on servers 2 and 3.

When we set up database replication in the Server 2, 3 from Server # 1, do I need to take DB backup for Server # 2 and Server # 3 as well?

How do I test whether my databases are running fine even on the failure of Server # 1 ?

When we set up Database Replication in the Server 2, 3 from Server # 1, Do i need to take DB backup for Server # 2 and Server # 3 as well.

It depends -

If you are replicating whole database and the size of database is huge

then its best to initialize replication from database backup.

IF you are replicating subset of data (as opposed to the entire database)

then you just run the snapshot (behind the scene it does a bcp out and bcp in of the data).

  How do i test whether my databases are running fine even on the failure of Server # 1 ?

This will eventually depend on the frequency of log reader agent. If you set it to run continuously then you will see your changes immediately on the subscribers. The latency depends on the amount of activity at publisher (server 1), network latency, distribution database - if it is on the same server as publisher or is on its own dedicated server.

There are many factors to consider when you choose replication as Disaster recovery (a stand by server when your primary is down).

As a side note:

What you are looking is a good HA - case when server 1 is down .. do we have a standby that can serve the application ? .. something along that line.

You should consider moving to a more recent version - sql server 2014 and look into using AlwaysON Availability groups.


postgres: list role grants for all users I have an audit requirement to query all roles granted to users; listed by user.
For example:

Username   Roles
user1      role1_rw, role2_ro, rol3_rw
user2      role2_rw

I cannnot use psql meta-commands as this result set will be merged with some other queries to do analysis of the grants.
The roles have been created to manage ro/rw access to various application schemas.
In oracle, this is quite straight forward; I dont see a single view to return this in Postgres. The querying user is a superuser.

Does anyone have a query to return this?
Postgres 9.4 on ubuntu

You can see the users using the psql client with the option \du+(like @dezso already explained).

But, if you want it, you can make a query on pg_roles, eg:

  SELECT
      r.rolname,
      r.rolsuper,
      r.rolinherit,
      r.rolcreaterole,
      r.rolcreatedb,
      r.rolcanlogin,
      r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;

Please, take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/user-manag.html


mysqldump 5.5.25 use swap instead of memory mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory.

# free -m
             total       used       free     shared    buffers     cached
Mem:         32177      31427        749          0        208      10747
-/+ buffers/cache:      20471      11705
Swap:        16002      12952       3049

# cat /proc/sys/vm/swappiness
5

#[mysqldump]
quick
max_allowed_packet = 16M

Your problem is, that mysqldump normally caches the results it produces.
You have to use the --quick option, to prevent this.

 --quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.


How to detect performance issues in function working on temporary tables? I have a function which creates many temporary tables and performs inserts, updates, deletes and selects on them.
Some columns are textual and I have a lot of SIMILAR TO operations going on
The function is taking too long to execute and I don't know how to detect the problem, because I can't do an explain analyze on temporary tables to detect expensive operations.
What could possibly be done? Any suggestion would be appreciated.

You can use the auto_explain extension to explain slow queries automatically from their native environment.  It will have no trouble seeing the temp tables.
You could manually load the extension if you are a superuser.  But this module is useful enough I just always put it (as well as pg_stat_statements) into shared_preload_libraries in postgresql.conf, that way it is always available.  The syntax for activating it in a specific client connection would be then be set auto_explain.log_min_duration = '150ms'; and set auto_explain.log_analyze=on;.  The output goes to the database server's log file.  You can also have it sent to the client, by doing set client_min_messages TO log;, but I don't know what C# will do with such messages once it receives them through that channel.


MySQLfabric -- Manage setup problem MySQL fabric does not get configured , getting these errors :

[mysql@dev-dm-gitest101z /root]# mysqlfabric manage setup

Error :
[INFO] 1413953890.788408 - MainThread - Initializing persister: user (fabric),
server (dev.local:3301), database (fabric).

Error: Command (CREATE TABLE providers (provider_id VARCHAR(256) NOT NULL, type INT
NOT NULL, username VARCHAR(100) NOT NULL, password VARCHAR(128) NOT NULL, url
VARCHAR(256) NOT NULL, tenant VARCHAR(100) NOT NULL, default_image VARCHAR(256),
default_flavor VARCHAR(256), CONSTRAINT pk_provider_id PRIMARY KEY (provider_id)), ())
failed accessing (dev.local:3301). 1071 (42000): Specified key was too long;
max key length is 767 bytes.

$ mysqlfabric manage setup

[INFO] 1413955889.898494 - MainThread - Initializing persister: user (fabric),
server (localhost:3301), database (fabric).

Error: Command (CREATE TABLE log (subject VARCHAR(40) NOT NULL, reported TIMESTAMP
/*!50604 (6) */ NOT NULL, reporter VARCHAR(64) NOT NULL, message TEXT, category int NOT NULL,
type int NOT NULL, INDEX key_subject_reported (subject, reported), INDEX key_reporter (reporter),
INDEX key_reported (reported), INDEX key_category (category), INDEX key_type (type)),
()) failed accessing (localhost:3301). 2055: Lost connection to MySQL server at 'localhost:3301',
system error: timed out.

You have probably encountered a bug caused by your default server charset

BUG #74192


Decryption in SQL Server 2012 I've encrypted texts stored in sql server 2012 which I want to decrypt using sql. AES 256 bit algorithm was used to encrypt the data and I've the key used to encrypt. This encryption was done in a third party application and not in SQL Server.
Is there as way I can decrypt it?

You can use AES 256 in SQL Server, but that does not guarantee that you will be able to decrypt your data. If the "key" you have is a string used to create the key, then you might be able to use the built-in DECRYPTBYKEY function. I emphasized "might" since there is an optional second value that can be used to encrypt data (in SQL Server it is called the "authenticator"), and if it was used when encrypting the data, then it must also be used when decrypting.

The following example uses a plain text phrase as KEY_SOURCE and does not use an authenticator:

USE [tempdb];

-- DROP SYMMETRIC KEY [test_key];
CREATE SYMMETRIC KEY [test_key]
WITH ALGORITHM = AES_256,
     KEY_SOURCE = '{your "key"}'
ENCRYPTION BY PASSWORD = 'weak';

OPEN SYMMETRIC KEY [test_key]
   DECRYPTION BY PASSWORD = 'weak';

DECLARE @Encrypted VARBINARY(8000);
SET @Encrypted = ENCRYPTBYKEY(KEY_GUID(N'test_key'), 'test me!');

SELECT @Encrypted AS [EncryptedValue],
       DECRYPTBYKEY(@Encrypted) [DecryptedBytes],
       CONVERT(VARCHAR(500), DECRYPTBYKEY(@Encrypted)) AS [DecryptedText];

CLOSE SYMMETRIC KEY [test_key];

Returns:

EncryptedValue                   DecryptedBytes        DecryptedText
0x00A6E1C5C3035A45BC33EEFA...    0x74657374206D6521    test me!

IF, on the other hand, you have the actual binary key itself, then that will not work with the built-in T-SQL functions. In this case you might be able to use SQLCLR in order to use the AesManaged Class in .NET which accepts a 128 bit (16 byte), 192 bit (24 byte), or 256 bit (32 byte) key. Of course, this requires a second value, the "initialization vector (IV)", and I don't know if you have that, and I haven't been able to get it to work when not supplying a value for it. And while it might not matter, there is a "Padding" property that has a few different possible values and it is always possible that Guidewire used a different setting than the default .NET setting, so it could require a bit of trial and error.


What do these pstart and pstop values mean? I'm attempting to tune a query and I want to be able to tell if partition pruning is happening. On one part of the query, the I'm getting a Pstart and Pstop value = "KEY":

| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|*  5 |      INDEX RANGE SCAN                     | IX_SOMEINDEX                   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |

Another line of the execution plan shows a Pstart and Pstop value = "ROWID":

| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|  10 |        TABLE ACCESS BY GLOBAL INDEX ROWID | LOOP                           |     1 |    18 |     2   (0)| 00:00:01 | ROWID | ROWID |

Basically I'm looking for definitions of what ROWID and KEY mean within the context of Pstart and Pstop.

Broadly, they mean that the partition(s) that need to be accessed will be determined at runtime.  In the first plan, where both are KEY, some earlier step in the plan is producing one or more values for the partition key.  Those partition keys are then used to determine which partitions Oracle needs to scan the index for.  In the second plan, the ROWID from the global index tells Oracle with partition to probe for each row that is being retrieved.


Could a null column be part of a primary key? I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship.

I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is the sql script to create these two tables and their relationships:

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [SentToRanger] BIT NOT NULL DEFAULT 0,
    [LastChange] NVARCHAR(50) NOT NULL,
    [UserName] NVARCHAR(50) NOT NULL,
    [Source] NVARCHAR(50) NOT NULL,
    [Reason] NVARCHAR(200) NULL,
    [HelperCodeId] NVARCHAR(20) NULL,
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
)

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [HelperCodeId] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
)

Is that correct?

A Code and a HelperCode are both different entities. A HelperCode can be an used (none Code references it), or used (only one Code references it).

Maybe Code.HelperCodeId must be part of Code table primary key. But I'm not sure if a null column could be part of a primary. Doing this, I want to prevent that two or more Codes reference the same HelperCode.

To answer the question in the title, no, all the primary columns have to be NOT NULL.

But without altering the design of the tables, you could add a filtered index on the Code (HelperCodeId) column:

CREATE UNIQUE INDEX
    FUX_Code_HelperCodeId
ON dbo.Code
    (HelperCodeId)
WHERE
    HelperCodeId IS NOT NULL ;

The filter (WHERE HelperCodeId IS NOT NULL) is needed because of the way SQL-Server treats nulls in unique constraints and unique indexes. Without the filter, SQL-Server would not allow more than one row with NULL in HelperCodeId.

An alternative design would be remove the HelperCodeId from Code and add a third table that will store the Code - HelperCode relationships. The relationship between the two entities seem to be Zero-or-One--to--Zero-or-One (both a Code can have no HelperCode and a HelperCode might be used by no Code):

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [SentToRanger] BIT NOT NULL DEFAULT 0,
    [LastChange] NVARCHAR(50) NOT NULL,
    [UserName] NVARCHAR(50) NOT NULL,
    [Source] NVARCHAR(50) NOT NULL,
    [Reason] NVARCHAR(200) NULL,
    --
    -- removed:   [HelperCodeId] NVARCHAR(20) NULL,
    --
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
) ;

HelperCode remains unchanged:

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [HelperCodeId] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
) ;

The additional table will have two UNIQUE contraints (or one primary and one unique) to ensure that every Code is related to (maximum) one HelperCode and each HelperCode is related to (maximum) one Code. Both columns would be NOT NULL:

CREATE TABLE [dbo].[Code_HelperCode]
(
    [CodeId] NVARCHAR(20) NOT NULL,
    [HelperCodeId] NVARCHAR(20) NOT NULL,
    CONSTRAINT [UQ_Code_HelperCode_CodeId]
       UNIQUE (CodeId),
    CONSTRAINT [UQ_Code_HelperCode_HelperCodeId]
       UNIQUE (HelperCodeId),
    CONSTRAINT [FK_HelperCode_Code]
       FOREIGN KEY ([CodeId])
        REFERENCES [dbo].[Code] ([Id]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
) ;


Is it a good idea to use null values to distinguish between records? I have a table with two columns. State and Name. State can have two values, 0 and 1.
When the state is 1, there needs to be a value in the column Name. If the state is 0 the Name must be null.

My question is what is considered as a good database design. I've come up with two options.

Remove the column state and just distinguish the state whatever the Name value is null or not.
Have the column State distinguish the state. But then I need to somehow enforce not null values in the case state is 1.

Based on your comment, I would suggest that the addition of the 'State' column adds additional application complexity (constraints necessary to keep the 'State' column in sync with the value in the 'Name' column) for, what appears to be, no added benefit.


Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows? Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that is supposed to tell you which tables are corrupted:

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

In fact I have two questions:
1) Where do you execute such a script? I thought the scripting shell in MySQL Workbench would to the job by saving this snippet as a Python file and then executing it - however it reports invalid syntax already in the "for ..." line.
2) According to http://dev.mysql.com/doc/refman/5.5/en/innochecksum.html innochecksum is a utility provided by MySQL/Oracle. However, I do not seem to find it in the bin or other folders of my MySQL installation. How do I obtain it?

UPDATE: As I did not trust my own MySQL installation, I downloaded the zip files for both 32 and 64 bit versions of 5.5.31 but can confirm that a innochecksum file is not included.

Thanks.

ANSWER #1
This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
echo %SQLSTMT%
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" > C:\windows\InnoDBCheck.sql

After you run this script, add this to my.ini
[mysqld]
init-file=C:\windows\InnoDBCheck.sql

Then, restart mysql
net stop mysql
met start mysql

during the startup, just after crash recovery, the
Forgive me for such a crude solution since I have very little dealing with MySQL for Windows (that's not sarcasm, I am serious).
ANSWER #2
Here is something more serious
I just checked my MySQL 5.6.10 no-install ZIP file. It has innochecksum.
C:\>dir \MySQL_5.6.10\bin\i*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.6.10\bin

01/22/2013  07:05 PM         4,065,792 innochecksum.exe
01/22/2013  07:05 PM         3,706,880 innochecksum.pdb
               2 File(s)      7,772,672 bytes
               0 Dir(s)  160,596,770,816 bytes free

C:\>

Download that ZIP and try running that .
You could then collect all the .ibd files with something like this:
cd "C:\Program Files\MySQL\MySQL 5.5\data"
dir *.ibd /s/b > C:\ibdfiles.bat

You can then edit C:\ibdfiles.bat and prepend innochecksum against every filename.
Sorry this is not a full answer, but at least you can a hold of innochecksum.exe


Database table data with different set of values I am trying to create a database table for my project but I stuck in this type of data.

The purpose is the admin can add, modify or insert types of questions and choices

example:

Do you like pokemon?

Yes
No

Pick the pokemons you like

[] charmander

[] pikachu

[] squirtle

[] bulbasaur

Pick your starting pokemon:

charmander
pikachu
squirtle
bulbasaur

Why pikachu is so cute?

Explanation here

Here is my table

Questions table

  id  |  question   |  type

Choices table

  id  |    choice   |  question_id

UserAnswers  table

  id  |    choice_id  | question_id  |  answer  |   userid

my problem is where to put the explanation of the user since it would be a large set of text? I am still very new in database designing and don't know what to do with this type of scenario. Please any recommendation for a good way to design my table.

I think you're on the right track.

I presume questions.type (NB type might be a reserved word and cause problems) will have the check constraint check type in ('multiple_choice','free_text') or something similar.

Then all you would need to do is rename your proposed UserAnswers to UserMultipleChoiceAnswers and drop the column answer,
and create another table, UserFreeTextAnswers with columns id, question_id, answer, user_id.


very poor cardinality estimate on a geometry intersection I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results in a bad query plan.

In particular the right most nodes of the query plan seem to think that the STPointFromText function will return a cardinality of 1000, and that the intersection of this point set with the geometry index returns 30% of the 54k rows.
(ran 1 million points through the table without finding a counter-example that actually returned more than 1 row)

The result isn't horrible in this abbreviated query, but when i join the output of this onto anything else, the high cardinality estimate forces the upstream table to be a tablescan+hashmap, even though the overall query returns 1 row. This extended query is running a few times per second, so i'm wondering how i can optimise this.

The spatial index is HHHH, for a highest resolution (over the approx 4000km longest side of the domain) of approx 80x50m, there are 56k polygons in the index, with expected minimum size of ~100m.

Note the difference between the est rows and the actual.

Estimated query plan.

Sounds like you want to get rowgoals to play their part on the query - so try using TOP(1), maybe with testing to avoid NULLs (in case of non-matching SRIDs). That way you can get the "nearest neighbour" functionality to kick in. I know you're using Contains, but you want to use a method that tells the QO that you're only going to get a single row back.

http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/ might have a few tips...


Capturing the result(print statements) of SP_UPDATESTATS I am creating a SQL Server Job to update the statistics once in a week. To create a Job I came up with the below code.

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Weekly Statistics Update',
    @category_name=N'Database Maintenance' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Weekly Statistics Update',
    @step_name = N'Updating statistics',
    @subsystem = N'TSQL',
    @command = N'DECLARE @SQL VARCHAR(8000)
DECLARE @DB_NAME SYSNAME
DECLARE STATSUPDATE_CURSOR CURSOR FORWARD_ONLY STATIC FOR
  SELECT Quotename([name])
  FROM   master.sys.databases
  WHERE  [name] NOT IN ( ''master'', ''tempdb'', ''model'', ''msdb'',
                         ''ReportServer'', ''ReportServerTempDB'' )

OPEN STATSUPDATE_CURSOR

FETCH NEXT FROM STATSUPDATE_CURSOR INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
      SELECT @SQL = Concat(''EXEC '', @DB_NAME, ''.sys.sp_updatestats;'')
                    + Char(13)

      --PRINT @SQL

      EXEC @SQL
      FETCH NEXT FROM STATSUPDATE_CURSOR INTO @DB_NAME
  END

CLOSE STATSUPDATE_CURSOR

DEALLOCATE STATSUPDATE_CURSOR ',
    @retry_attempts = 3,
    @retry_interval = 5 ; --5 minutes
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'Weekly Once statistics update',
    @freq_type = 8,  -- Weekly
    @freq_interval = 1, --Sunday
    @active_start_time = '1:00:00' ;  -- Sunday Morning 1 AM

USE msdb ;

GO
EXEC sp_attach_schedule
   @job_name = N'Weekly Statistics Update',
   @schedule_name = N'Weekly Once statistics update';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Weekly Statistics Update';
GO

Is there a way to capture the result(Print Statements) of sp_executesql inside the Job. I searched for the answer but no luck is there a way to do this?

I would use the free Ola Hallengren maintenance solution to update statistics.

See Example C from the above link, where you can update the statistics in all user databases via: -

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'

You can also edit this for your needs to only do an individual database.

The logging you require can be achieved by specifiying the LogToTable parameter.

As a final point. Remember you don't have to update statistics if you are rebuilding your indexes. Rebuiliding your indexes during your weekly maintenance window might be your best option. Again you can use the Ola Hallengren solution for this.


Heap-Organized Tables and 'Heap' the Data Structure AFAIK, heap-organized tables, which are default in Oracle and PostgreSQL, are just tables without any clustered index.

I'm wondering where the term 'heap' got its name. It's just a pile of data, not related to 'Heap' the data structure, right? Just another confusing name that made me search everywhere why databases use 'Heap'.

Is it from 'Heap' the pool of memory above stack? Or is there any historical / academical backgrounds?

In computing generally a "heap" is just a general dumping ground, available for allocation then a block of memory/disk is needed. See https://en.wikipedia.org/wiki/Memory_management#HEAP for a lot more detail.

The data structure called a heap can be used to manage the allocation of space, or to operate as a priority queue, amongst other more minor uses. See https://en.wikipedia.org/wiki/Heap_(data_structure)

In a database, and more generally, a storage heap may be managed by a head-based data structure, though that does not have to be the case: the names being the same does not imply that they are in any way mutually inclusive.


get only rows with max group value for example, we have:

element | group_value | value
a       | 1           | 2000
a       | 2           | 1500
a       | 2           | 2500
b       | 1           | 1000

I'd like to return only last 3 records as those are the records with max group value for each element.

I know for a solution with subquery but is there an efficient one?

To clarify: for element 'a':

2 is highest group_value so it returns rows 2 and 3 > (and not the first row as it's group value is not highest),

for element 'b':

1 is > highest group_value so it returns row(s) 4

My (performance-vice not good) solution to the problem is:

select *
from   x x1
where (element, group_value) in (select   element, max(group_value)
                                 from     x x2
                                 where    x1.element = x2.element
                                 group by x2.element)

First answer uses a CTE to select max(group_value) and then join with the table.

with maxgv as
(
    select element, max(group_value) maxg
    from x
    group by element
)
select x.element, x.group_value, x.value
from   maxgv
inner join x
on         x.element = maxgv.element
and        x.group_value = maxgv.maxg
;

This one uses RANK() function:

with grp as
 (
     select element, group_value, value,
            rank() over (partition by element order by element, group_value desc) rn
     from x
)
select element, group_value, value
from   grp
where  rn = 1;

Both returns the same result:

| element | group_value | value |
|---------|-------------|-------|
| a       | 2           | 1500  |
| a       | 2           | 2500  |
| b       | 1           | 1000  |

Rextester here

But, just to check and compare performance, we need the table schema. I don't know which index is using your current query.


Reset every Oracle sequence to the MAX value of its target table PK? I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did.  It even took my AUTOINCREMENT fields and created sequences for my tables in their place.  It also did a good job of migrating the data as well.

The only real loose end I see is that when it created the sequences, it did not take into account the existing data in the table and instead all of the sequences are starting NEXTVAL of 1.

I can imagine a simple PL/SQL script that will set the next value of the sequence based on the MAX value in the primary key column of a specific table to a specific sequence but then I have to do this over a hundred times and well I just don't have the patience for that.

I wonder if there is some way I can write a PL/SQL script that uses meta data in the SYSTEM schema to dynamically do this for every table/sequence pair in an entire user space?  Does anybody have any other better ideas how to handle this?  I ran out of interns BTW.

I made this script as I did not find a script online that dynamically sets all my sequences to the current highest ID. Tested on Oracle 11.2.0.4.

DECLARE
  difference         INTEGER;
  sqlstmt            VARCHAR2(255) ;
  sqlstmt2           VARCHAR2(255) ;
  sqlstmt3           VARCHAR2(255) ;
  sequenceValue      NUMBER;
  sequencename       VARCHAR2(30) ;
  sequencelastnumber INTEGER;
  CURSOR allseq
  IS
     SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
  DBMS_OUTPUT.enable(32000) ;
  OPEN allseq;
  LOOP
    FETCH allseq INTO sequencename, sequencelastnumber;
    EXIT
  WHEN allseq%NOTFOUND;
    sqlstmt  := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
    --Assuming: <tablename>_id is <sequencename>
    sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
    --DBMS_OUTPUT.PUT_LINE(sqlstmt2);
    --Attention: makes use of user_sequences.last_number --> possible cache problems!
    EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
    IF difference > 0 THEN
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
      EXECUTE IMMEDIATE sqlstmt || difference;
      sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
      EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
      DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
      EXECUTE IMMEDIATE sqlstmt || 1;
      DBMS_OUTPUT.PUT_LINE('') ;
    END IF;
  END LOOP;
  CLOSE allseq;
END;


Specifying per-table recovery mode I have a table that is used for log data; the data is only INSERTed, never UPDATEd. The table has no business data whatsoever, and thus it is not a problem if I cannot recover it in case of a disaster.

In addition to inserting, I periodically want to empty this table.

The table has no foreign keys, and only one clustered index on the field which contains the log date/time.

The database recovery mode is currently set to FULL. Is it possible to prevent logging transactions when writing/deleting from this specific table, while still logging changes to other tables?

No, logging and recovery don't work at that level. What I recommend is, instead of trying to make one database serve two different recovery objectives, store this logging information in its own database (or a different, existing database) with alternate recovery objectives.

If that is not an option for some reason, the only thing you can really do to minimize the impact on the log is to:

Issue truncate instead of delete - which is still logged, but less so.
Instead of allowing for lots of small inserts, queue those up into batches and do what you can to make the eventual insert eligible for minimally logged transactions.


Column does not exists when accessing inner type field in ORDER BY In PostgreSQL 9.4 I have two types:

CREATE TYPE a_schema.type_child {
  an_order smallint
}

CREATE TYPE a_schema.type_parent {
  pluto uuid,
  child type_child
}

Then in a view I have this script:

SELECT
    something_a,
    something_b,

    ARRAY(SELECT
            ROW (a_uuid,
              a_schema.get_a_type_child(a_type_child_id)
            )::a_schema.type_parent AS tp
          FROM a_schema.a_table
          ORDER BY ((tp).type_child).an_order ASC)
FROM ....

where the stored procedure just does a select as:

CREATE FUNCTION a_schema.get_a_type_child(IN type_id smallint, OUT a_type a_schema.type_child)
  RETURNS a_schema.type_child AS
$BODY$
DECLARE
BEGIN

  SELECT
      ROW (a_order)::a_schema.type_child
  FROM a_schema_another_table
  WHERE at_id = type_id
  LIMIT 1
  INTO a_type;

END;

$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 1;

The error I get is:

  ERROR: column "tp" does not exist

related to the ORDER BY clause.

I know that I could just do

ORDER BY tp

But then I have to define an ordering for the type, which introduces a lot of code.

Is there any way to access the inner type field an_order for ordering purpose?

Explanation for the error

You get the error message:

  ERROR: column "tp" does not exist

because you cannot use output columns in another expression in the ORDER BY clause. You can only reference output column names as a whole. Expressions must be built on input columns. The manual:

  Each expression can be the name or ordinal number of an output column
  (SELECT list item), or it can be an arbitrary expression formed from
  input-column values.

Bold emphasis mine.

Solution

You would need the row type as input to your query to involve it in expressions in ORDER BY. You can do that with a LATERAL join:

SELECT
    something_a,
    something_b,
    ARRAY (
      SELECT (a_uuid, my_child)::a_schema.type_parent AS tp
      FROM   a_schema.a_table
      LEFT   JOIN LATERAL (SELECT get_a_type_child(a_type_child_id) AS my_child) t ON true
      ORDER  BY (t.my_child).an_order
      )
FROM ....

Why LEFT JOIN LATERAL () ON true?

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

Asides

The solution is merely a proof of concept. You need intimate knowledge of row and composite type handling to cope with this setup, and it will still be awkward and slow. Your setup is more complicated than necessary to begin with. I would have to type so much to explain everything, it's just not worth it.

I agree with @a_horse: a properly normalized schema will save you a lot of headache.

The function can be simpler, but there is nothing wrong with PL/pgSQL per se. SECURITY DEFINER functions are exempt from function inlining anyway. There are other pros and cons for SQL vs. PL/pgSQL functions:

Difference between language sql and language plpgsql in PostgreSQL functions

Note: functions. Your term stored procedure is a bit off, since Postgres does not have true stored procedures. Just functions - doing almost but not quite the same.


Reorganize and Rebuild Indexes, Pros and Cons? I'm studying SQL Server and now am learning how to Reorganize and Rebuild Indexes. I've read already at msdn.microsoft, but It's not clear for me yet.
Please advise what is the best way when I have to use it. And what are the pros and cons of using it?

From Rebuild or Reorganize: SQL Server Index Maintenance by Kendra Little:
Short version:

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).
Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Should I use REBUILD or REORGANIZE?

If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.
If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.
If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.

Read Kendra's article for other important information you should take into account.


Postgresql how to compare the tables which are present in different database How can I compare the data which is present in different database in postgresql.

For example, I have 2 databases db1  & db2

Both have the same tables like tb1 tb2 tb3

I want to compare which table has different record.

you could use the postgres_fdw to link the 2 dbs together and execute queries to compare the different tables as if they we're in the same db.

see the documentation on postgres_fdw for examples.
Note, this requires that you will be able to connect from one db to the other remotely. and queries might be resource intensive (as you are basically pulling the data of the remote table over the wire)


What is .mdf_MSSQL_DBCC15 file We use MSSQL 2016 database, I recently see a file call DatabaseName.mdf_MSSQL_DBCC15 in the same location where my database .mdf file located.  It has identical file size as my .mdf database file.

Do you know what is this file and what caused it to be created?
I googled around and the term snapshot comes up here and there but I can't confirm 100%.

This would be a file created by using a DBCC.

Summarizing the pertinent information in the link that Shekar Kola provided:

  The behavior of DBCC CHECKDB commands beginning with SQL Server 2014

  DBCC CHECKDB creates an internal snapshot database.
  The internal snapshot database is created by using physical database    files.

  Consider a database that has the three files E:\Data\my_DB.mdf, E:\Data\my_DB.ndf, and E:\Data\my_DB.ldf.
  Consider that the internal snapshot database is created with database ID 10.
  The internal snapshot database will be created by using the files E:\Data\my_DB.mdf_MSSQL_DBCC10 and E:\Data\my_DB.ndf_MSSQL_DBCC10.
  Notice that new files are created in the same folder with the naming convention
  _MSSQL_DBCC.

  The new files are marked as sparse at the file system level.

  The "Size on Disk" used by the new files will increase based on how much data is updated in the source database during the DBCC
  CHECKDB command.
  The "Size" of the new files will be the same file as the .mdf or .ndf file.

  The new files are deleted at the end of DBCC CHECKDB processing.

  These additional files that are created by DBCC CHECKDB have the "Delete on Close" attributes set.
  If the operating system encounters an unexpected shutdown while the DBCC CHECKDB command is in progress, then these files will not be
  cleaned up. They will accumulate space, and potentially will prevent
  future DBCC CHECKDB executions from completing correctly. In that
  case, you can delete these new files after you confirm that there is
  no DBCC CHECKDB command currently being executed.

  The new files are visible by using ordinary file utilities such as Windows Explorer.


Does mysql make some internal maintenance after some uptime? I've got a dedicated mysql server (5.5.44) which runs now about 202 days. Between Friday at about 11:00 a.m. and Saturday at about 3:30 a.m. I've got a very high load on that server (my monitoring shows a system load from up to 25). I've checked serveral log files but can't find anything what goes wrong. I've also checked the mysql-slow.log and there I discovered, that there are queries logged, which are normally never logged. The first query from that file shows me

Query_time: 11.387071  Lock_time: 0.000045 Rows_sent: 8  Rows_examined: 9975109

When I take the exact same query now and run it directly, I got in the explanation that only 19023 rows have to be examined. The slow query log is full of such examples.

So it looks to me, that some internal indexes from mysql have failed. Could that be? Or is there anything internal maintenance running in mysql?

By now, the mysql server is fast as before. But what happened in the time between friday and saturday?

If you are using a "hosted service", they could be doing something, such as a backup.
EVENTs can do things at specific times.
Otherwise, nothing in mysql should cause what you are seeing.
Could the entire server be taking a dump?  Do ps when the load average goes high.
Let's see the query -- "Rows examined" will stay the same unless the table or the query is being modified.  It's an exact number, unlike "Rows" in EXPLAIN.  If you got 19023 from EXPLAIN, that is mighty far off, but it is not impossible.  Let's also see SHOW CREATE TABLE, maybe you have a lot of TEXTs or BLOBs; these sometimes confuse EXPLAIN.
Could there be a long-running transaction (or something) that happens every weekend.  "Interference" from other tasks can cause a burst of "slow queries".
11 seconds to touch 10M rows -- not bad.
Lower long_query_time down to at least 2.
Use `pt-query-digest to summarize the naughty queries.  This catches both (1) slow, but rare, queries, and (2) frequent, but fast, queries.


Pass a parameter from SSRS to MySQL Here is the query for the dataset:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = @FieldManager

FieldManager is the parameter name. It's not taking the value of the parameter.

Please help me with the syntax to pass a parameter to a MySQL query.

If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead.

So for your query it should look like:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = ?

That way you should get a parameter named Parameter1. If you have more than one question mark your parameters will be named Parameter1, Parameter2 etc in the same order as they appear in the query.

This isn't specific to MySQL but to any ODBC connection. See Binding Parameters ODBC for reference.