Configure MariaDB to log row deletion ONLY I'm investigating some rows/tables that intermittently go missing on a Magento site, and of course there's no entries in the error log. (I suspect Magento queries/rules or human error might be the culprit) I know I can enable the General log, but I don't want to log EVERYTHING as this site processes a huge amount of sales every day and the log would be massive.

I'm hoping someone might know of a way to configure either the general log or log table to record only DELETE commands. Open to any suggestions you might have of a better way to go about this.

This is mostly copied from the docs:

Trigger Example - Logging deletions

  Scenario: We want to keep a log file containing data from rows that have been deleted from the BOOKS Table. Here's a Trigger definition that accomplishes this:

CREATE TRIGGER Books_Delete
AFTER DELETE ON Books                 /* See note 1 */
  REFERENCING OLD ROW AS Old          /* See note 2 */
FOR EACH ROW                          /* See note 3 */
  INSERT INTO Books_Deleted_Log       /* See note 4 */
      VALUES (Old.title);             /* See note 5 */

The Trigger action has to be AFTER, since the Trigger action includes a SQL-data change statement. You should also replace Books with your table name
It is conventional to use the alias "Old" or "Old_Row" for the old row.
No log will occur for a DELETE statement that affects zero rows.
Replace Books_Deleted_Log with the name of the table you're logging the deleted rows into. Make sure this table has all the columns you need to log the information you need.
OLD is an alias for a single old row, so OLD.TITLE is the scalar value derived from the TITLE Column of that old row. You will have to change Title to a column you want to track. You can also add more columns by following the Old.<column name> pattern using commas as separators.


MS SQL Server 2016 - Database Security - Login Permissions I have an SQL Server 2016 database with Always On structure. I have a login that I called x. My x login's properties are:

Default database: abc
Server roles: public (ticked)
User mapping: Map (ticked), abc database (ticked), default schema (dbo), permissions on abc database: db_owner and public
Guest account enabled for: qaz
Database role membership for qaz: public
Securables: Permissions: CONNECT SQL, VIEW ANY DATABASE
Status: Grant and enabled are ticked.

So my problem is:

When I connect to this user and trying expand databases. I have no problem with my own database. But when I try to expand other databases, sometimes it gives "The database <db_name> is not accesible (true one) and sometimes I can expand a few databases but I can't see any table or objects and I can't operation on this databases (false one).

Normally, it should be the same error ("The database <db_name> is not accesible) all of the other databases because I have no permission except my own database.

How can I check the this case and how can I fix it?

Best regards,

There is a user "Guest" in each database. This user gives some permission for everyone.
My guess is that for the database where you can "Expend" the DB, your "guest" user  in those database is probably enable and probably have the "Connect" to the database securable (and I hope not more then connect)
To disable that user, run this TSQL:
Use [yourDB]
go
REVOKE CONNECT FROM guest;
GO

Once this is done, your login x should receive the error you are expecting.


What prerequisites package is needed for Oracle 11g + 12c on the same OL7 server? I need to install 3 Oracle homes on the same Oracle Linux 7 server, but it's not clear to me what prerequisites I need to install before proceeding.

What I'm planning to set up, is a two-standalone-instance (11.2 + 12.2) server and an Oracle Grid infrastructure for taking advantage of the benefits of the Oracle Restart.

As a prerequisite, on OL7 we need to run:

yum install oracle-rdbms-server-11gR2-preinstall -y   (in case on Oracle 11.2)
yum install oracle-database-server-12cR2-preinstall -y   (in case on Oracle 12.2)

Considering I'm planning to install the following Oracle homes

12.2 Grid infrastructure
12.2 Database
11.2 Database

should I run both the aforementioned yum commands, or only 12cR2 prerequisite is actually required?

The problem with oracle-database-server-12cR2-preinstall is that it does not install gcc. For a base release, it is fine, but upon installing a PSU/RU, opatch fails because of that.

[root@o61 ~]# yum deplist oracle-rdbms-server-11gR2-preinstall-1.0-14.el6.x86_64 | grep dependency | grep gcc
  dependency: gcc-c++
  dependency: gcc
  dependency: libgcc
[root@o61 ~]#

[root@o71 ~]# yum deplist oracle-database-server-12cR2-preinstall.x86_64 | grep dependency | grep gcc
  dependency: libgcc
[root@o71 ~]#

It is not needed to install both packages. I would just do this (as I usually do):

yum install oracle-database-server-12cR2-preinstall gcc gcc-c++ -y


Restoring a database using backups that cover the same time periods multiple times everyone

I have some questions about a database restore sequence. Suppose I have the following backup events:

t00 Database creation

t01 FULL database backup 1

t02 Database DIFFERENTIAL backup 1

t03 Log backup 1
t04 log backup 2
t05 Log backup 3
t06 log backup 4
t07 log backup 5

t12 Full Database Backup 2

t13 Log backup 9
t14 Log backup 10

t15 Disaster Hits!

Now, suppose I want to restore the database back to the end of log backup 4, what I would normally do is to restore

Full database backup 1,
Log backup 1
Log backup 2
Log backup 3
Log backup 4

I skip Database DIFFERENTIAL backup 1 because I believe it is redundant. But I have seen many articles that would include Database DIFFERENTIAL backup 1 in the sequence.

The question is: Aren't we applying the changes contained in the backups twice if we were to include the differential backup in the sequence?

Is there any difference between these two approaches, either in terms of possible data loss or performance?

The overall question is then: Is it CONCEPTUALLY okay to restore a database using all kinds of backups (Full, Differential, Log, Files/Filegroups) as long as they are restored in order from the earliest to the latest, and the backups span the entire time period required, even though they might containing overlapping information about changes to the database?

Thanks

DIFFERENTIAL backups are there for one and only one purpose: to speed up restore by allowing you to skip one or more LOG backups. Namely, all LOG backups taken between the last FULL backup and the DIFFERENTIAL in question.

Since, in your example, you have no LOG backups between the FULL and DIFFERENTIAL, then there is no LOG backup to skip so there is no restore speed to gain. Attempting to restore the DIFFERENTIAL is a waste of time.

If, say, you'd modify the sequence like this:

t01 FULL database backup 1
t02 Log backup 1
t03 log backup 2
t04 Log backup 3
t05 log backup 4
t06 Database DIFFERENTIAL backup 1
t07 log backup 5

then it would be a benefit by allowing you to skip the restore of LOG backups 1-4.


what is mongodb telling me when it logs an update? After several minutes of a large program that pushes a lot of data into mongo, my log starts to show a message for (I guess) each update. This seems awfully noisy, is there some special reason it feels the need to do this?

Tue Jun  5 14:32:37 [conn3] update benchmark.entity
 query: { corefEntityId: "45-LOCATION" }
 update: { $set: { corefEntityId: "45-LOCATION", type: "Location" },
 $push: { indocs: { docid: "cfcc403b-714f-4c5d-8507-ccb5b6354654",
   ordinal: 26, label: "United States", mentions: [ "United States" ] } },
   $addToSet: { allMentions: { $each: [ "United States" ] } },
   $inc: { documentCount: 1 } } 116ms

As a perhaps more answerable footnote, I wonder: is an 'upsert' that adds to some internal document faster or slower than a plain insert into some other collection of the internal document as a top-level document?

I think you meant upsert rather than upstart (upstart being a type of job on an Ubuntu system).  Upsert means "update the document if present; insert (a single document) if missing". MongoDB determines that the document is missing solely via examining the criteria document you pass to it.

That query to determine whether the document exists is likely the slow part here.  You should run an explain on just the find to determine if it is using indexes correctly and is as efficient as it should be:

http://www.mongodb.org/display/DOCS/Explain

If it is not using an index then you should add one to speed things up.  Depending on whether this example is typical of your query plan I would expect that an index on "corefEntityId" would be a good idea at a minimum.  If the find is more complex in other cases you may want to look into a compound index and use hint to see which is optimal:

http://www.mongodb.org/display/DOCS/Optimization#Optimization-Hint

In terms of the logging - when it logs a query like you have seen in your example, the reason is because the default "slowms" value is 100.  That is, MongoDB will log every query that takes longer than 100ms to complete.  This can be adjusted:

http://www.mongodb.org/display/DOCS/Database+Profiler#DatabaseProfiler-EnablingProfiling

If you wanted to set it to 200ms for example:

db.setProfilingLevel(1,200)


Update just one unused row I have a table as follows:

token  | status   | userid | serviceid
-------+----------+--------+----------
token1 |   ACTIVE |      1 |         2
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL
token2 | INACTIVE |   NULL |      NULL

Token values (token1, token2, ..., tokenN) are only for illustrative purposes. They are completely random hash values.

I want to update a specific row, where token is not used, e.g.

WHERE STATUS="Inactive" AND USERID=NULL AND SERVICEID=NULL

...but I can't really match TOKEN value.

TOKEN column consists of random and unique values, and it also serves as INDEX in my table.

Updating single row is impossible, because all rows can really match this WHERE clause.

UPDATE tbltokens
SET status="Used", userid=1, serviceid=2
WHERE status="Unused"
AND userid=NULL
AND serviceid=NULL;

How can I update a specific row, with unique (and unknown) value and thus not specifying it when executing query?

Would be best to fetch first row where the above mentioned conditions are met and use its token value so I can update first row where conditions are met?

I want to update any single record that fits to desired criteria, and this is WHERE status="Unused" AND serviceid=NULL AND userid=NULL

Based on the idea that you want to update a single record that fits your criteria, but do not care which record that is, this will work for you:

UPDATE tbltokens
SET status='Used', userid=1, serviceid=2
WHERE status = 'Unused' AND userid IS NULL AND serviceid IS NULL
LIMIT 1;

Using LIMIT on an UPDATE query works the same as on a SELECT. It's detailed in the MySQL doc for UPDATE.

You should also use IS NULL instead of = NULL, as this is the correct syntax for checking NULL values.

Also string literals should be quoted with single quotes (like this: 'text') and not with double quotes so the code wil continue to work in different SQL modes (eg ANSI) where double quoted strings are identifiers and not constants.


Self Join? Or am I wrong? We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item:

customer     item
Sally        1
Sally        2
Sally        3
John         1
John         2
Paul         2
Paul         3

We want to find users who bought "1" and not "3". In this case we only want "John".

No, a self join would not really help, but if your database system has an EXCEPT operator (or something similar, in Oracle it is called MINUS) it would.

Here's how you get your result:

SELECT customer FROM Sales
WHERE item = 1
AND customer NOT IN (SELECT customer FROM Sales WHERE item = 3)

If you have an EXCEPT operator:

SELECT customer FROM Sales WHERE item = 1
EXCEPT
SELECT customer FROM Sales WHERE item = 3


UPDATE STATEMENT GETTING STUCK I am facing a performance issue with an update statement. It takes too long to update, but when I perform a select on that updating table it completes very fast.

UPDATE mo
   SET mo.RNCNEID    = ts.RNCID,
       mo.SiteNEID   = ts.SiteID,
       mo.SectorNEID = ts.SectorID
FROM COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR AS mo
 INNER JOIN COPS_UMTS_HUAWEI.Aggregation.tsector   AS ts
     ON mo.rncname      = ts.rncname
    AND mo.cellid       = ts.cid
    AND ts.VendorID     = 6
    AND ts.TechnologyID = 2
WHERE mo.RNCNEID IS NULL;

After running the below query:

SELECT COUNT(*)
FROM   COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR WITH (NOLOCK)
WHERE  RNCNEID IS NULL;

...it completes very fast.

It takes too long to update but when I do select on that updating table its completes very fast .

If the UPDATE query is taking a long time, but the SELECT part is fast when run on its own, then one possible reason is that the UPDATE is getting blocked due to incompatible locks being held by another session.

You can check for this while UPDATE query is running by, for example, running sp_WhoIsActive:

EXEC sp_WhoIsActive;

In this screenshot, I'm trying to run an UPDATE in session_id 57, but it's blocked by another session that's holding locks that my query needs.  You can see this in the wait_info and blocking_session_id columns, which show LCK_M_X waits (which is an exclusive lock, usually required by another UPDATE query running) and 53 (the other session that is holding the lock).

  After running the below query...Query completes very fast .

Running that count(*) query will likely make sure all of the pages needed for the query are in memory (meaning it will be faster, because they won't have to be read from disk first).  This probably just masking the blocking issue, as the blocking query finishes faster when the pages are in memory.


Could not resize shared memory segment error I have a PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) running in a docker container.
I get the following error with some queries:

could not resize shared memory segment "/PostgreSQL.860708388" to 536870912 bytes: No space left on device`

system:

Ubuntu 18.04.5
memory: 126 GB
cores: 64

disk usage is fine, as well as quotas.
Postgres settings:

work_mem: 30GB,
dynamic_shared_memory_type: posix
max_parallel_workers_per_gather: 32

Lowering number on max_parallel_workers_per_gather to 8 seems to have fixed the problem in my case.


Why is creating and dropping a random index fixing this performance problem? I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/reorganize fragmented indexes on this table regularly.

Now we are facing this issue very often: Lots of queries accessing this table will suddenly start taking longer than usual. When I check, I observe that the execution plans for the queries have changed.

I create and drop a random non-clustered index and this fixes the issue.

What I don't get is, what is causing these sudden slowness randomly anytime and what does creating and dropping the index do in the background to the table to fix it which the index rebuild job doesn't do?

I need to find what exactly is triggering these slowdowns so that a permanent solution can be found as I can't just just keeping creating and dropping the index to fix this issue every time.

Any help here would be greatly appreciated.

Dropping an index on your table will flush the execution plan that refer to this table from the cache. (I think the index need to contain a column that is referenced in the query but not 100% sure about that).

SQL then build a fresh execution plan which "fix" your issue.

You could try to rebuild the stats (instead of dropping an index), or drop the execution plan manually (see Sp_blitz store procedures to get the command easilly). You will probably have the same behavior (Query fix). If so, then you may want to read on parameter sniffing issue.

P.s. This is rarelly a good practive to have a table without cluster index. Usually, the only good case I've seen is for log table where you want the insert to be done real quick... but in your case, if you have nonclustered index, you will have overhead for the inserts anyway.


My Database Tables Revert to MyISAM Instead of InnoDB (MySQL) I am trying to change my tables engine to InnoDB because I am experiencing problems with the current engine which is inconsistent and crashes often. Unfortunately, it reverts back to MyISAM. This is the query that I am running:

SET @DATABASE_NAME = 'my_database'; SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;

What could I be missing or doing wrong?

You need USE my_database; before, so that you will be in that database.

The SELECT generates a bunch of ALTERs, but does not perform them.  To perform them:

Plan A:  (Straightforward)  Copy & Paste them into mysql commandline tool or suitable window in some UI.

Plan B:  Build a Stored Procedure that loops ('cursor') through the output to prepare/execute/deallocate-prepare each one.


Recommendation about mysqldump? I have approx 90 GB sized database, and I need to schedule a mysqldump backup. How long will it take to perform a full hot backup approximately? I'm asking because I've never had to backup a database as big as this one yet.

LE: giving the tips below I may estimate that your dump could take 20 minutes (and longer). But with these tips and high performance CPUs and lot of memory the time may be smaller.

You didn't provide enough informations regarding the specific of your data. Are there a single database or multiple? Is all data InnoDB or there are MyISAM tables around?
If you have only InnoDB tables then you may use the "--single-transaction" option of mysqldump utility.
According to manual:

  --single-transaction    This option sets the transaction isolation
  mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to
  the server before dumping data. It is useful only with transactional
  tables such as InnoDB, because then it dumps the consistent state of
  the database at the time when START TRANSACTION was issued without
  blocking any applications.

  When using this option, you should keep in mind that only InnoDB
  tables are dumped in a consistent state.    For example, any MyISAM or
  MEMORY tables dumped while using this option may still change state.

  While a --single-transaction dump is in process, to ensure a valid
  dump file (correct table contents and binary log coordinates), no
  other connection should use the following statements: ALTER TABLE,
  CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent
  read is not isolated from those statements, so use of them on a table
  to be dumped can cause the SELECT that is performed by mysqldump to
  retrieve the table contents to obtain incorrect contents or fail.

  The --single-transaction option and the --lock-tables option are
  mutually exclusive because LOCK TABLES causes any pending transactions
  to be committed implicitly.

  This option is not supported for MySQL Cluster tables; the results
  cannot be guaranteed to be consistent due to the fact that the
  NDBCLUSTER storage engine supports only the READ_COMMITTED transaction
  isolation level. You should always use NDB backup and restore instead.

  To dump large tables, combine the --single-transaction option with
  the --quick option.

Therefore, for a consistent snapshot, if there are any chance to get "ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE" commands on the database, then you should use the "--lock-tables" option. Also, to get a fast restore you could use the "--disable-keys".

However, there are also other techniques to speed up the dumping, such as:
- increase the "key_buffer_size" at the maximum 4GB. The variable is dynamic so you can issue "*SET GLOBAL key_buffer_size=4*1024*1024*1024;*" (or whatever you can) just before the dump and revert the value after the dump.
- increase the "max_heap_table_size". If you have "--order-by-primary" option issued there may be a lot of temporary tables produced. I've found that setting max_heap_table_size larger than the most tables increases the speed.
- redirect the dump directly to an archive utility which support pipeline, such as: gzip, pigz, bzip2, pbzip2, 7zip, rar, etc. This way, you can avoid using the HDD during the dump.

Example of use (ordered by the speed!):

PIGZ: Parallel GZip, grade 6 from 9
PIGZ: 30 sec : 319976345 GZIP - multi-threading: 8 cpu by default
Arch: mysqldump -u root -p --opt DATABASE | pigz -6 -c > dumpname.sql.gz
Unarch: pigz -cdk dumpname.sql.gz | mysql -u root -p DATABASE

PBZIP2: Parallel BZip2. grade 6 from 9(my personal favourite)
PBZIP2: 45-60 sec : 231360931 BZ2 - multi-threading: maxim sau cu -p8
Arh: mysqldump -u root -p --opt DATABASE | pbzip2 -c >dumpname.sql.bz2
Unarch: pbzip2 -cdk dumpname.sql.bz2 | mysql -u root -p DATABASE

7Zip, container ZIP, metoda BZip2, grade 3 from 9
7ZA: 75 sec : 233503528 ZIP - multi-threading: maxim
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=3 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.gz | mysql -u root -p DATABASE

7Zip, container ZIP, metoda BZip2, grade 6 from 9
7ZA: 95 sec : 230986947 ZIP - multi-threading: maxim
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=6 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.zip | mysql -u root -p DATABASE

GZIP: container GZ, grade 6 from 9
GZIP: 95 sec : 318920792 - multi-threading: none
Arch: mysqldump -u root -p --opt DATABASE | gzip -6 --rsyncable > dumpname.sql.gz
Unarch: gzip -cdk dumpname.sql.gz | mysql -u root -p DATABASE

RAR: container RAR, grade 3 from 5, test -mt16
RAR: 95 sec : 248149972 RAR - multi-threading: auto - maxim 5 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m3 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE
"-si" switch undocumented in manual; may be found in rar.txt (/usr/share/doc/rar/)

RAR: container RAR, grade 4 from 5
RAR: 125 sec : 246759443 RAR - multi-threading: auto - maxim 6 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m4 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE

**** RAR: container RAR, grade 5 from 5 ****
RAR: 140 sec : 246242715 RAR, grade 5 from 5 - multi-threading: auto - maxim 6 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m5 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE

**** 7Zip, container 7Z, method LZMA default, grade 3 from 9 ****
7ZA: 145 sec : 266638306 7ZA - multi-threading: none (b'cose method)
Arch: mysqldump -u root -p --opt DATABASE | 7za a -mmt=on -mx3 -sidumpname.sql dumpname.sql.7z
Unarch: 7za e -so dumpname.sql.7z | mysql -u root -p DATABASE

**** 7Zip, container ZIP, metoda BZip2, grade 9 from 9 ****
7ZA: 330 sec : 228609475 ZIP(bzip2) - multi-threading: maxim - 16 cpu
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=9 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.7z | mysql -u root -p DATABASE

The tests are made on a 4+2GB (of data+indexes) database, with a total of 8 CPU cores and 32 GB RAM.
There are other solutions too: nanozip, zpaq, lbzip2, pXZ, pIXZ, plzip, lrzip, which some of them I've tried out (like nanozip and zpaq), but even if the dump is smaller, the time increased significantly.

You have some good clues now, but, as a conclusion, you need to check out some points before establish the right measures you need to take, like if there are moments when nobody is querying the data, the storage type (MyISAM is not transactional, but InnoDB is), amount of RAM (for key_buffer_size and max_heap_table_size), number of CPU's (to take advantage of multi-threaded archiving).

  LE2: Consider taking the dump over a replication slave, if available.


Best way to get time zone key name from SQL Server Below is what I pieced together but I wanted to see what other ways are available.

SET NOCOUNT ON;
GO
DECLARE @tz VARCHAR(50)
EXEC [master].[dbo].[xp_regread]
    'HKEY_LOCAL_MACHINE'
    ,'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
    ,'TimeZoneKeyName'
    ,@tz OUT;

SELECT
    GETDATE()
    ,'(' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),3),1)
    + '' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),2),1)
    + '' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),1),1) +')'

Output: 2014-10-14 16:22:21.037 (CST)

Reading the reg key with this or SQLCLR is the only correct way I know (I personally would create a job which updates a table instead of enabling xp_regread though.).

This powershell script is an example of how to update a config table with this information.

$timeZone = (get-itemproperty 'HKLM:\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\').TimeZoneKeyName

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=.;database=myDatabase;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "
        MERGE ConfigTable AS target
        USING (SELECT 'TimeZone', @timeZone) AS source (ConfigKey, ConfigValue)
        ON (target.ConfigKey = source.ConfigKey)
        WHEN MATCHED THEN UPDATE SET ConfigValue = source.ConfigValue
        WHEN NOT MATCHED THEN INSERT (ConfigKey, ConfigValue) VALUES (source.ConfigKey, source.ConfigValue)"
$command.Parameters.AddWithValue("@timeZone", $timeZone)
$Command.ExecuteNonQuery()
$Connection.Close()

All other systems with offset etc are giving incorrect results.


Percona restore databases error I am trying to restore Percona backup but getting the below error.

[/]# xtrabackup --copy-back --target-dir=/backup-billingsystem
xtrabackup: recognized server arguments: --innodb_file_per_table=1 --open_files_limit=16384 --innodb_buffer_pool_size=128M --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_io_capacity=100 --server-id=1 --log_bin=mysql-bin
xtrabackup: recognized client arguments: --password=* --user=root --copy-back=1 --target-dir=/backup-billingsystem
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
Error: datadir must be specified.

The error message is quite explicit:

Error: datadir must be specified.

The documentation is clear on this too:

  --datadir=DIRECTORY The source directory for the backup. This should be the same as the datadir for your MySQL server, so it should be read
  from my.cnf if that exists; otherwise you must specify it on the
  command line.

  When combined with the --copy-back or --move-back option, --datadir
  refers to the destination directory.

Note the last sentence, and make sure you're providing the correct path.

The documentation is here.


MySQL update based on main table data with cross table subquery I have the following SELECT statement which I have simplified here. It actually takes 0.6s to run as it does some calculations based on two tables (table1 & table2).

SELECT
    table1.id,table1.lat,table1.lon,
    (SELECT id
       FROM table2
      WHERE latitude=table1.lat AND longitude=table1.lon
      LIMIT 0,1) AS subqueryresult
FROM
    table1

Maybe this thread will even help me to improve my select. The two tables itself have no common ids.

What I intend to do is update every row of table1 with the value received in subqueryresult. subqueryresult itself results out of a SELECT based on the main table data (table1.lat & table1.lon) and just uses table2 as a reference.

Any idea how to achieve that?

First of all, the fact that two tables don't have ids in common does not mean you cannot join them. You can JOIN two tables using any boolean expression. This can be table1.id = table2.id, but in can also be table2.latitude = table1.lat AND table2.longitude = table1.latitude.

Let's assume these are your tables:

SELECT * FROM table1;

id |       lat |       lon
-: | --------: | --------:
 1 | 41.390205 |  2.154007
 2 | 40.416775 | -3.703790
 3 | 41.979401 |  2.821426
 4 | 40.304665 | -3.723679

SELECT * FROM table2;

 id |  latitude | longitude
--: | --------: | --------:
101 | 41.390205 |  2.154007
102 | 40.416775 | -3.703790
103 | 41.979401 |  2.821426
124 | 43.262985 | -2.935013

We could perform your original query with a subquery, and get:

SELECT
    table1.id,
    table1.lat,
    table1.lon,
    (SELECT id
       FROM table2
      WHERE latitude=table1.lat AND longitude=table1.lon
      LIMIT 0,1
    ) AS subqueryresult
FROM
    table1

id |       lat |       lon | subqueryresult
-: | --------: | --------: | -------------:
 1 | 41.390205 |  2.154007 |            101
 2 | 40.416775 | -3.703790 |            102
 3 | 41.979401 |  2.821426 |            103
 4 | 40.304665 | -3.723679 |           null

(Nearly) the same can be done with a JOIN through the following query:

SELECT
    table1.id, table1.lat, table1.lon, table2.id AS table2_id
FROM
    table1
    JOIN table2 ON table2.latitude = table1.lat AND table2.longitude = table1.lon

id |       lat |       lon | table2_id
-: | --------: | --------: | --------:
 1 | 41.390205 |  2.154007 |       101
 2 | 40.416775 | -3.703790 |       102
 3 | 41.979401 |  2.821426 |       103

(If you want to get the row with no match on table2, you can use LEFT JOIN instead of JOIN).

If you want to UPDATE table1.id to be the same as table2.id, you can do so on MySQL by using:

UPDATE
    table1
    JOIN table2 ON table2.latitude = table1.lat AND table2.longitude = table1.lon
SET
    table1.id = table2.id

SELECT * FROM table1

 id |       lat |       lon
--: | --------: | --------:
  4 | 40.304665 | -3.723679
101 | 41.390205 |  2.154007
102 | 40.416775 | -3.703790
103 | 41.979401 |  2.821426

Note that this is not standard SQL, which does not allow to have a JOIN on this part of an UPDATE. If more than one matching row exists on table2, the value used for the update could be any of the matching ones.

The standard SQL way to do the same thing is:

UPDATE
    table1
SET
    id =         /* Your subquery! */
    (SELECT
        table2.id
    FROM
        table2
    WHERE
            table2.latitude = table1.lat
        AND table2.longitude = table1.lon
    ORDER BY id     /* good to have a criterion to select id */
    LIMIT 1
    )
WHERE
    EXISTS     /* The subquery actually returns something */
    (SELECT
         table2.id
    FROM
        table2
    WHERE
            table2.latitude = table1.lat
        AND table2.longitude = table1.lon
    ) ;

Or:

-- Standard SQL
UPDATE
    table1
SET
    id = coalesce(
        (SELECT
            table2.id
        FROM
            table2
        WHERE
                table2.latitude = table1.lat
            AND table2.longitude = table1.lon
        ORDER BY id
        LIMIT 1
        ), table1.id
    ) ;

You can check everything at dbfiddle here


Found important issue with SQL Server 2008 R2. Need Advice Recently while installing my copy of SQL Server 2008 R2; the power went OFF. So now it does say the some features are installed and some are not but when I run SQL server detection manager it says that there is nothing. I inquired further and there is probably some problem in the registry counter. I also tried uninstalling SQL server's installed components from Control Panel and I was successful in uninstalling SOME components but some others are now saying that some totally other components are now missing and HENCE it cannot be uninstalled

The log error is below:

  Warning 26003. Microsoft SQL Server 2008 R2 Setup Support Files cannot
  be uninstalled because the following products are installed:
  * SQL Server 2008 R2 Common Files
  * SQL Server 2008 R2 Database Engine Shared.

Please can I have some expert or novice (practically anyone) advice and solution for this issue.

In most cases where you hit this issue you will just need to follow the manual uninstall steps Microsoft provides here. That will, in cases I have had to, get the system to a point where you can successful reinstall SQL Server to a working state.


Restore Log and Differential Backups Simultaneously in MS SQL Server I have the full backup of a database, also one differential backup and one log backup. Is it possible to restore this 3 backups together? SSMS lets only full backup and only one of them. Is there a way to restore both? It's okay to show the way in t-sql too.

Thanks for your time!

Use tsql. Also, highly recommend to use sp_restoregene. This will generate restore commands for you which you can just execute .


Add an index on a new column I'm adding a new column (bigints, initial values all null), to a large (~5 billion rows) Postgres (9.6) table.

I want to add an index on the row (algorithm: concurrently), and I'm wondering if there's a way I can save it the time of scanning through all five billion null fields to add them to the index, since I'm adding the index when the column is brand new and has no data. Is there any kind of optimization for this, or should I just let it run? (Is it even theoretically possible for there to be an optimization, or does it need to add something to the index even for null fields?)

No, sorry.

PostgreSQL generally indexes NULL fields, so would it would have to add an entry for each row.  You can make an index not index NULLs (using a partial index, or using a non-default type of index which doesn't index NULLs), but it is not implemented for such an index to observe an all-NULL constraint on the column and so take a shortcut of not scanning the table.  It would uselessly scan the table looking for not-NULLs anyway.  Interesting idea though.


mariadb galera bootstrapping fails Starting point at the sandbox sb1, sb2, sb3:
All boxes identical.
A Fresh debian 9 install
mariadb 10.2
Galera3
build a test galera cluster with empty databases works fine.
All three nodes come up and work as expected.
then after stopping the test cluster
copy a LVM snapshot from a production mariadb server over to sb1
start mariadb server with standalone config to see if all databases and tables are correct.
(config I took from the server the data come from)
shutdown and restart the standalone to see if there is a problem.
All is fine, server starts and stops as expected.
Then change configs to fit galera
try to bootstrap with
> galera_new_cluster
and that
https://pastebin.com/e5jm4Tec
is what happens.
my.cnf
[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

max_connections=700
log-error=/var/log/mysql/error.log

#------------------------------------------------------------
#galera settings
#------------------------------------------------------------

wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=512M"

datadir = /var/lib/mysql
wsrep_sst_method=rsync
#wsrep_sst_method = xtrabackup-v2

wsrep_cluster_name="tam_cluster"
 wsrep_cluster_address="gcomm://162.255.86.161,74.208.250.175,162.255.86.145,93.115.26.72"

#for each machine different
wsrep_node_address="162.255.86.145"
wsrep_node_name="node_5"

!includedir /etc/mysql/conf.d/

someone any idea what's going wrong?

I made it work.

The problem is how the bootstrap script tries to start the server.

I tried on different machines and all had the same symptoms when trying to bootstrap the cluster using the script galera_new_cluster.

It has something to do with systemd, I guess.

bootstrap the new cluster "by hand" using

mysqld -umysql --wsrep-new-cluster

did the trick on all machines I tried.

And while starting the other nodes, it is also a good idea not use the "systemd way"

systemd mechanism has a timeout, and if your sync with the other nodes takes a bit longer, systemd spits out an error, but mysql keeps running and does what it is supposed to do.

It's not a bad thing, but it's kind of confusing.

So for now I recommend, starting the other nodes also "by hand"

mysqld -umysql

if all your nodes are synced, open another terminal and

mysqladmin -u<YourPrivilegedUser> -p shutdown

after that you can start and stop your nodes normally, using the "systemd way"

hope it helps.

Ju


Recurring Memory Issues We have a Windows Server 2008R2 server with 32GB or RAM running SQL Server 2008R2, along with an IIS server. The machine is also used as a backup server.

The problem is, periodically (every week or two) the SQL takes up all the memory, literally all of it: resmon shows 0MB available. Now, I did look the problem up, and I know SQL is supposed to take up the entire memory, but it really hurts the web performance, and in the end the SQL itself starts stuttering; scheduled jobs don't execute (the logs say "insufficient resources") etc.

I tried limiting the max memory size to 4096MB. The memory footprint dropped - and rose again to full in a week.
I tried scheduling a job that would empty the caches every night. Is that likely to help?
If not, what will?

edit Last time it crashed, already after I limited it to 4GB, I wasn't on-site, unfortunately. But the SQL Server logs show the available page file space dropping below 4MB, and the Windows logs report low virtual memory conditions for hours, with sqlservr.exe consuming the most memory (though only about 1,640,919,040 bytes, for some reason).

This sounds a lot like a problem I have experienced before at a client who was also running IIS and SQL Server 2008 R2 on the same server. The problem would also start with reports of very bad web performance and then slowly grind to a complete halt.

After many hours of trouble shooting we managed to attribute it to a single SQL Server setting found under the "Processors" heading in the Server Properties dialog:

When this setting was enabled we constantly experienced the degradation in performance. Although this setting is related to CPU resources we also observed increased memory usage on the server as you explained. It looked as if when this setting was enabled SQL became so hungry for resources that IIS was unable to allocate the resources needed to serve the incoming requests and this in turn caused a backlog of SQL operations which in turn made SQL want to consume more resources and so on and so forth. I have no empirical evidence to back up these claims but this was the observed behavior at the time.

I realize correlation does not equal causation, but we were able to confidently replicate and subsequently mitigate the issue on multiple servers by switching the setting on and off.

After seeing this repeated at a few clients we adopted the policy to have this setting switched of at all clients and we went so far as to report on any changes made to this setting. Suffice to say we have since then not yet experienced issues of this nature again.

I am not saying this is definitely the solution to your problem but given the similarities between the issues you experienced and the ours I would recommend you try it as a first resort since it is very easy to attempt.


Prepared statements with many parameters is slow I have an application written in PHP with Laravel that regularly prepares and executes statements like this:

   -- All parameters are varchar(10)
   SELECT c1, c2, c3, c4
      from MyBigTable
     where is_active = 1
       and c1 in (@P1, @P2, @P3, @P4 ... @P250)
       AND c2 is not NULL

Users have a big data grid, and they can select many rows (there is even a button to select 'ALL'). If they select 250 rows, this statement is what happens. But it takes more than one minute to run, which is unacceptable.

Table MyBigTable has about 10 millions rows. The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes, and that the only issue is in the use of prepared statements. (If you think I am wrong, just let me know). Moreover, I understand that these prepared statements are prepared, used once and discarded, so I don't think they are really beneficial.

What recommendation should I give to the developers?

Should I just tell them to stop using prepared statements, and hardcode the 250 values in the query?

Or should I give them some workaround, like the use of temporary tables (make a temporary table, insert 250 values, then make a query on MyBigTable JOINed with temp)?

Or any other idea?

EDIT: execution plan https://www.brentozar.com/pastetheplan/?id=rJ-b2XalH

1) IN clauses are during the optimization phase expanded to multiple OR. Example: C1 = @P1 Or C1 = @P2.

2) If the parameter count is greater than 63 the optimization process will build an internal table for it. Having said that, I’m not so sure about the stats being generated on the dynamically generated temp table.

3) Putting your parameters in a #temp table may result in a slightly different execution plan, however, this may also introduce concurrency issues depending on the design of your application.

4) I would, before introducing a temp table, try to rewrite the query into smaller logical chunks. For example, isolate the filter process against an optimized smaller outer table and cross apply that with the inner table.

5) Ensure that your stats are updated and that you are getting a parallel execution plan. Would be nice if you could share your plan so we can have a peek at what’s going on.

  Blockquote
  The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes,

6) "index seek" can be "bad" if they represent a lot of logical reads. You should check the statistics in order to be sure that index tuning won't yield any better results.


Redshift: Table columns can be found in information_schema.columns but not in pg_catalog.pg_table_def I create a table in Redshift. When I tried to search for the table definition, I get back results from information_schema.columns by running the following query:

select * from information_schema.columns
where table_name = 'table' and table_schema='schema'

However, when I run a query against pg_catalog.pg_table_def, I don't get back any result.

select * from pg_catalog.pg_table_def
where tablename = 'table' and  schemaname = 'schema'

Could anyone help me understand why this is happening? The table is created and owned by the account I'm using.

Check show search_path; to make sure you are on the current path where the table was created.
As stated on the original AWS redshift documentation.

  PG_TABLE_DEF only returns information about tables that are visible to the user.

to get what you want you should run

set search_path to '$user', '<#your_schema#>';
select * from pg_catalog.pg_table_def where tablename = '<#your_table#>';

Hope that helps.


MySQL - query to get length of service in Years, months, days I have the following query that I'm trying to get the length of service of a person at their company. The idea is to find how many years, months, and days they have served there.

For example:

Todays date: Jun 13, 2017.

Exhibit A: John Doe has served since Feb 10, 2016. So length of service should be 1 year, 4 months, 3 days excluding the end date.

Exhibit B: Jane Doe has served since 13th Jun, 2007. So length of service should be Or 10 years excluding the end date.

My Query spoiler: it's not right

    SELECT  `firstname` ,
            DATE_FORMAT( CURDATE( ) ,  '%Y' ) - DATE_FORMAT( startdate,  '%Y' ) - ( DATE_FORMAT( CURDATE( ) ,  '00-%m-%d' ) < DATE_FORMAT( startdate,  '00-%m-%d' ) ) AS years,
            PERIOD_DIFF( DATE_FORMAT( CURDATE( ) ,  '%Y%m' ) , DATE_FORMAT( startdate, '%Y%m' ) ) AS months,
            DATEDIFF( CURDATE( ) , startdate ) AS days
    FROM users

It gives me the results in years, as well as months, as well as days. So for someone who has served a year it would output: 1 year, 12 months, 365 days etc.

A possible (not the best) solution to this:

SELECT firstname
     , startdate
     , CASE
           WHEN YEAR(CURDATE()) < YEAR(startdate) THEN 0 #or some such validation
           ELSE YEAR(CURDATE()) - YEAR(startdate)
       END years
     , CASE
           WHEN MONTH(CURDATE()) < MONTH(startdate) THEN MONTH(startdate) - MONTH(CURDATE())
           ELSE MONTH(CURDATE()) - MONTH(startdate)
       END months
     , CASE
           WHEN DAY(CURDATE()) < DAY(startdate) THEN DAY(startdate) - DAY(CURDATE())
           ELSE DAY(CURDATE()) - DAY(startdate)
       END days
FROM users;


Scaling PostgreSQL TRIGGER(s) How Postgres triggers mechanism scales ?

We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s).

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service.

Before going all in with Postgres TRIGGER(s) we would like to know how they scale: how many triggers can we create on a single Postgres installation? Does they affect query performance? Did anyone before tried this ?

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service.

That's a pretty standard use for a trigger.

  Before going all in with Postgres TRIGGER(s) we would like to know how they scale: how many triggers can we create on a single Postgres installation?

If you keep creating them, eventually you'll run out of disk space.

There's no specific limit for triggers.

PostgreSQL limits are documented on the about page.

  Does they affect query performance?

It depends on the trigger type, trigger language, and what the trigger does.

A simple PL/PgSQL BEFORE ... FOR EACH STATEMENT trigger that doesn't do anything has near-zero overhead.

FOR EACH ROW triggers have higher overhead than FOR EACH STATEMENT triggers. Scaling, obviously, with the affected row counts.

AFTER triggers are more expensive than BEFORE triggers because they must be queued up until the statement finishes doing its work, then executed. They aren't spilled to disk if the queue gets big (at least in 9.4 and below, may change in future) so huge AFTER trigger queues can cause available memory to overrun, resulting in the statement aborting.

A trigger that modifies the NEW row before insert/update is cheaper than a trigger that does DML.

The specific use case you want would perform better with an in-progress enhancement that might make it into PostgreSQL 9.5 (if we're lucky), where FOR EACH STATEMENT triggers can see virtual OLD and NEW tables. This isn't possible in current PostgreSQL versions, so you must use FOR EACH ROW triggers instead.

  Did anyone before tried this ?

Of course. It's a pretty standard use for triggers, along with auditing, sanity checking, etc.

You'll want to look into LISTEN and NOTIFY for a good way to wake up your worker when changes to the task table happen.

You're already doing the most important thing by avoiding talking to external systems directly from triggers. That tends to be problematic for performance and reliability. People often try to do things like send mail directly from a trigger, and that's bad news.


How to get the schema name of a table of type regclass in PostgreSQL? In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??).

CREATE OR REPLACE FUNCTION column_exists(_tbl regclass, col_name text)
  RETURNS bool AS
$func$
  SELECT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema=get_schema($1)
      AND table_name=get_table($1)
      AND column_name=$2
    );
$func$  LANGUAGE sql;

So if the table name is 'staging.my_table'::regclass, I'd like to get staging from an imaginary function get_schema.

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?

In addition, is it guaranteed that the table name _tbl, when converted to text, will always have a schema name? (i.e. not omitting things such as public.)

I'm not very familiar with the regclass type. I searched but couldn't find how to extract the schema name, and just wanted to ask first before re-inventing wheels.

Can I just implement this function with e.g. split_part(_tbl::text, '.', 1)?

I'm not sure that would be safe. Instead, I would use,

SELECT nspname
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS ns
  ON c.relnamespace = ns.oid
WHERE c.oid = _tbl;

That is guaranteed to work.

  In writing a function to test if a column col_name exists in a table _tbl, I'd like to extract the table's schema name, which is passed into the function as a regclass parameter (for security??).

So you could do something like,

SELECT nspname
FROM pg_catalog.pg_attribute AS a
JOIN pg_catalog.pg_class AS c
  ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace AS ns
  ON c.relnamespace = ns.oid
WHERE c.oid = _tbl
  AND a.attname = col_name;

Update

If you have a fully-qualified identifier, one with the table name, and schema name inside, You can use parse_ident to parse it safely.


How to check if there is partitions in use in fact tables? I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?

The below script will give you a list of tables, and count of partitions, where there are tables with multiple partitions:

select schema_name(schema_id), name, COUNT(name) from sys.tables t
inner join sys.partitions p on t.object_id = p.object_id
group by schema_name(schema_id), name
having COUNT(name) > 1


Postgres sql dump import error I'm facing a issue while importing the dump in my system, i'm getting the error as

  " Error: syntax error at or near ""   LOCK TABLESDB_NAME` WRITE;"

and exiting with "out of memory"

We had taken back-up from Suse linux and trying to import in  Cent OS 6.3.

Any suggestion to fix this issue.

Thanks in Advance,

LOCK TABLES dname WRITE is not a valid PostgreSQL statement. It's a MySQL statement. You can't just take a MySQL dump and restore it to Postgres. You'll need to convert it either manually or find some kind of converter script.


Migrate database from SQL Server 2005 to SQL Server 2008 R2 DBAs... please be gentle with me, I'm a 'greenhorn' SQL Server DBA, pressed into service because my company's DBA had resigned and we haven't found his replacement, yet.

First, my situation:

I'm supposed to 'transfer' a web-app from a server in Singapore to a (pair of) newly-built server(s) in Indonesia. Unfortunately, the Singapore DB server is running SQL Server 2005 Express Edition, while the new server is running SQL Server 2008 R2. To make things worse, the developers of the web-app is no longer supporting us (we're practically coasting with the last version of the web-app)*.

I plan on creating a new Instance on the SQL2008R2.

I have several questions that have been bugging my mind:

How do I find out what 'features' (e.g., Reporting Service, Replication, etc.) has been installed on the Singapore server?
How to ensure a complete backup (Databases and Stored Procedures and other relevant tidbits)?
I noted that there are several Stored Procedures (not many, just 4 that I can see under the "Databases\AppDB\Programmability\Stored Procedures" tree), how to ensure successful migration of these Stored Procedures? Any gotchas I need to be aware of?

Thank you for any inputs.

* The plan actually is to just migrate the web-app as a "read-only" app for archival/audit
purposes; management has plans to deploy a better web-app in its place.

In order.

Some easy options: You could look at the start->run services.msc and see which services are there; you could run powershell and look at run gsv *SQL*; or you could look at the Start menu->Microsoft SQL Server 2008 R2 and see what's there.
If you're a database newbie, I would take a look at the excellent database maintenance scripts from Ola Hallengren.  These cover backups, index maintenance and database integrity checks.
I would migrate the database by taking a backup of the old database and restoring that to the new server - this will bring all objects, tables, data, stored procedures etc.  However, you'll then have to migrate any appropriate scheduled tasks and user IDs.  There are some SQL Server Integration Services tasks that'll help with jobs and logins (indeed, they'll also help with the data migration...).

Lastly, welcome to the job.  I would recommend that you have a trawl through Red Gate's bookstore - there are a lot of free PDF versions of their books.  Have a skim of SQL Server Backup and Restore,  Troubleshooting SQL Server for the Accidental DBA.

Good luck!

edit.  A pair of thoughts, relating to your migration from 2005 to 2008 R2 specifically.

You should be OK doing this by using database backup and restore - good news.
However, be aware that it is entirely possible that some features your app relies on may be deprecated / discontinued. Once you get up & running on SQL Server 2008 R2, you can keep an eye out for these features using the sys.dm_os_performance_counters DMV.  There are various blog posts out there, but the gist is:

SELECT * FROM sys.dm_os_performance_counters
WHERE Object_name like '%:Deprecated Features'
AND cntr_value > 0

(unfortunately, tracking Deprecated Features wasn't part of the SQL 2005 release...)


column precision exceeded. ORA-01438 is so unmeaningful I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not.

Let's suppose you have a Java batched prepared statement writing data to database. If one of the columns has a value larger than specified precision it throws ORA-01438 exception. Is there any way to find out which column is that?

It would indeed be nice if Oracle provided more information for this error.  Until then here is an idea.  You could call a package to do the insert and the package could verify each of the values separately as follows:

drop table t1;
create table t1 (c1 number(3), c2 number(2), c3 number(1));

CREATE OR REPLACE PACKAGE p1 AS

   Procedure InsertData(iValue1 In Number, iValue2 In Number, iValue3 In Number);

END;
/

CREATE OR REPLACE PACKAGE BODY p1 AS

Procedure InsertData(iValue1 In Number, iValue2 In Number, iValue3 In Number) Is
   vValue1 T1.C1%Type;
   vValue2 T1.C2%Type;
   vValue3 T1.C3%Type;
   ePrecisionExceeded Exception;
   PRAGMA EXCEPTION_INIT(ePrecisionExceeded, -1438);
Begin
   INSERT INTO T1 VALUES (iValue1, iValue2, iValue3);
Exception
   When ePrecisionExceeded Then
      vValue1 := iValue1;
      vValue2 := iValue2;
      vValue3 := iValue3;
End;

END;
/

execute P1.InsertData(100,102,103);

The check is only done when the exception is raised, so the only overhead is the variable declaration.  You will know which value caused the exception based on the line number.  If this were the only reason to move logic into a package it might not be worth it, but since there are many other benefits, it is worth considering.


Schedule Maintenance Plan - Request For a Good Sequence I have installed Ola Hallengren's MaintenanceSolution.sql script.

So what I'll get out-of-the-box are a lot of jobs:

My questions is:

What schedules should I implement based on the jobs that are provided with the script?

The SQL-Full Backup starts on Saturday at 11:00 pm. So I thought I would use the following schedules:

Job                           Schedule
----------------------------- ------------------------
IndexOptimize                 Saturday at 10:00 pm
Database IntegrityCheck       Saturday at 09:00 pm
CommandLog Cleanup            Sunday at 03:00 am
Output File Cleanup           Sunday at 03:15 am
SP_delte_backuphistory        Sunday at 03:20 am
SP_purge_Jobhistory           Sunday at 03:25 am

What's your advice?

Every comment is welcome.

It looks like your goal is to "chain" certain jobs: to run an integrity check, then an index optimization, then a backup.

First - it may make sense to take a backup before the check and index optimization. If something were to go horribly wrong during those steps, you could get back to the start to try to resolve things. If you take differential backups of this DB, you might want to take a final diff backup before, and a full backup after.

Second, the chaining seems reasonable in general. However, until you've run the jobs, you have no idea how long they'll take. If there's a chance that the DB check and the index optimization could take longer than an hour, I'd space things out more. Overlapping these things isn't necessarily a good idea.

If the tightest possible chaining is desirable, then move the job steps into a single job, and order them as desired. Or, as suggested by @AaronBertrand, only set and actual schedule for the first job; have the first job kick off the second as its final step, the second job kick off the third, and so forth.


Search every column in every table in Sybase Database I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is very convoluted. I've been able to figure out most of the schema to gain access to the data I need, however, there are a few things still left to find. I know what they are called in the actual POS, but I can't seem to find them anywhere in the database (although I haven't actually looked through all 200+ tables).

I'm wondering if there's any way to run a query to search for a specific string in all the columns in all the tables. BTW, I'm using the "Interactive SQL" application that comes with the Sybase Database Client software to connect to the database.

I was being tasked to do similar task recently. IT is not easy in especially SybaseASE.
Option 1 :
You can write custom tool to BCP out data in files and then grep those files to search for particular text that you want to look for. You can use PERL regex to do as well or use native Unix/Linux commands to parse those files and find the required text that you want to look for.
Option 2 :
Another option (which is not that great - quick and dirty way using TSQL ) that worked for me .
Note: This will take long time (slow and unoptimized) depending on your database size, hardware, workload running on the server as well as database layout.
TEST, TEST and TEST it before hand !!
--- run below script and copy the output and run it in another query window.
set nocount on
declare @searchvalue varchar(255)
select @searchvalue = 'test string to search'---- replace this .. !!
print 'set nocount on'
print 'go'
print 'create table #results (table_name sysname, column_name sysname)'
print 'go"

select
   'insert #results select distinct '''
   + object_name(c.id)
   + ''' as table_name, '''
   + c.name
   + ''' as column_name from '
   + object_name(c.id)
   + ' where '
   + c.name
   + ' LIKE ''%'
   + @searchvalue
   + '%'''
   + char(10)
from
   syscolumns c, sysobjects o
where
   c.usertype in (
-- only look for char, varchar, text etc datatypes as we are not interested in int, datetime etc....
    1
    ,2
    ,18
    ,19
    ,24
    ,25
    ,42
    )
and o.type ='U' -- only user tables we are interested in
and c.id = o.id
and c.length>=datalength(@searchvalue )
--and object_name(c.id) = 'some table if you want to filter'

print 'select * from #results'
print 'go"
go


How to check if a subquery has exactly one distinct result and a specified value concisely? I found myself writing the following:

select 'yes'
where exists(select * from foo where val=1)
and not exists(select * from foo where val<>1);

and wondering if there is a more concise way without sacrificing too much readability.

I found one way which I am posting as an answer but I'm not entirely happy with it and would be very interested in alternatives

In this case val is unique within foo - there are no duplicates

Concise, fast (especially with many rows), my favorite concerning readability and would work with dupes, too:

SELECT count(*) = 1 AND min(val) = 1 FROM foo;

Returns TRUE / FALSE .. or NULL - only in the case of exactly one row with val IS NULL, because count() never returns NULL or no row.

The second 1 in the example just happens to be the same as the first, because of your example.

The query in the question fails with NULL values. Consider the simple demo:

CREATE TABLE foo (id int, val int);
INSERT INTO foo VALUES (1, 1),(2, NULL);

SELECT 'yes'
WHERE      EXISTS(SELECT * FROM foo WHERE val =  1)
AND    NOT EXISTS(SELECT * FROM foo WHERE val <> 1);

IS DISTINCT FROM would fix this, but it could still fail with duplicates in val - which you have ruled out for this case.

Your answer works fine.
Returns 'yes' / no row.

I would prefer this shorter form, though. Don't forget that PostgreSQL (unlike Oracle) has a proper boolean type.

SELECT array_agg(val) = array[1] FROM foo;

Returns TRUE / FALSE / NULL.


Mutually exclusive many-to-many relationships I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, animals or bacteria, and each plant, animal or bacterium can be in an arbitrary number of containers.

So far this is very straightforward, but the part I'm having a problem with is that each container should only contain elements of the same type. Mixed containers that e.g. contain both plants and animals should be a constraint violation in the database.

My original schema for this was the following:

containers
----------
id
...
...

containers_plants
-----------------
container_id
plant_id

containers_animals
------------------
container_id
animal_id

containers_bacteria
-------------------
container_id
bacterium_id

But with this schema, I can't come up with how to implement the constraint that containers should be homogeneous.

Is there a way to implement this with referential integrity and ensuring on the database level that the containers are homogeneous?

I'm using Postgres 9.6 for this.

There is a way to implement this declaratively only without changing your current setup much, if you agree to introduce some redundancy to it. What follows can be considered a development on RDFozz's suggestion, although the idea fully formed in my mind before I read his answer (and it is different enough to warrant its own answer post anyway).

Implementation

Here is what you do, step by step:

Create a containerTypes table along the lines of the one suggested in RDFozz's answer:

CREATE TABLE containerTypes
(
  id int PRIMARY KEY,
  description varchar(30)
);

Populate it with pre-defined IDs for each type. For the purpose of this answer, let them match RDFozz's example: 1 for plants, 2 for animals, 3 for bacteria.
Add a containerType_id column to containers and make it non-nullable and a foreign key.

ALTER TABLE containers
ADD containerType_id int NOT NULL
  REFERENCES containerTypes (id);

Assuming the id column is already the primary key of containers, create a unique constraint on (id, containerType_id).

ALTER TABLE containers
ADD CONSTRAINT UQ_containers_id_containerTypeId
  UNIQUE (id, containerType_id);

This is where the redundancies begin. If id is declared to be the primary key, we can rest assured it is unique. If it is unique, any combination of id and another column is bound to be unique as well without additional declaration of uniqueness – so, what is the point? The point is that by formally declaring the column pair unique we let them be referable, i.e. to be the target of a foreign key constraint, which is what this part is about.
Add a containerType_id column to each of the junction tables (containers_animals, containers_plants, containers_bacteria). Making it a foreign key is completely optional. What is crucial is to make sure the column has the same value for all rows, different for each table: 1 for containers_plants, 2 for containers_animals, 3 for containers_bacteria, according to the descriptions in containerTypes. In each case you can also make that value the default to simplify your insert statements:

ALTER TABLE containers_plants
ADD containerType_id NOT NULL
  DEFAULT (1)
  CHECK (containerType_id = 1);

ALTER TABLE containers_animals
ADD containerType_id NOT NULL
  DEFAULT (2)
  CHECK (containerType_id = 2);

ALTER TABLE containers_bacteria
ADD containerType_id NOT NULL
  DEFAULT (3)
  CHECK (containerType_id = 3);

In each of the junction tables, make the pair of columns (container_id, containerType_id) a foreign key constraint referencing containers.

ALTER TABLE containers_plants
ADD CONSTRAINT FK_containersPlants_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

ALTER TABLE containers_animals
ADD CONSTRAINT FK_containersAnimals_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

ALTER TABLE containers_bacteria
ADD CONSTRAINT FK_containersBacteria_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

If container_id is already defined to be a reference to containers, feel free to remove that constraint from each table as no longer necessary.

How it works

By adding the container type column and making it participate in the foreign key constraints, you prepare a mechanism preventing the container type from changing. Changing the type in the containers type would be possible only if the foreign keys were defined with the DEFERRABLE clause, which they are not supposed to be in this implementation.

Even if they were deferrable, changing the type would still be impossible because of the check constraint on the other side of the containers—junction table relationship. Each junction table allows only one specific container type. That not only prevents existing references from changing the type but also prevents addition of wrong type references. That is, if you have a container of type 2 (animals), you can only add items to it using the table where type 2 is allowed, which is containers_animals, and would be unable to add rows referencing it to, say, containers_bacteria, which accepts only type 3 containers.

Finally, your own decision to have different tables for plants, animals, and bacteria, and different junction tables for each entity type, already makes it impossible for a container to have items of more than one type.

So, all these factors combined ensure, in a purely declarative way, that all your containers will be homogeneous.


SSIS: Scripts in package part not being executed when target server is 2016 I am having an issue with SSIS packages where scripts included in package parts ar not executed when the target server version is SQL Server 2016.

Create an SSIS project, set target server version to SQL Server 2016 (very important!)
Go ahead and add a simple script in the default Package. This should work fine.

In my case I have a script that sends an email using SendGrid service via SMTP. You can see the code here: https://pastebin.com/DW3hfBP3
However I tested this issue with other scripts Something like: MessageBox.Show("Great. Another bug. Thanks a lot guys..."); would do.

Create a package part.
Now just try re-creating the simple script from step 2 into the package part. Then add the package part onto the default Package or another package and execute the task. Even though the execution is successful, the script is not executed.

NOTE: If you try to copy the script from the default package into the package part, you should also notice a moronic error saying that the version is wrong. This is why I say to re-create the script in the package part.

I wonder if anyone could reproduce this issue and if there is a way around it? Worth noticing that this issue doesn't seem to happen when the target server is SQL 2017.

Microsoft confirmed that this is indeed a bug that will be fixed in the next version of SQL Server Data Tools (SSDT) for Visual Studio 2017.

The confirmation was in the Integration Services (SSIS) Advisors Group on Yammer. The group is closed so I cannot post a direct link to it. However here is what I have been told:

  There is no easy workaround. A package containing package part has issues when persisting in previous target server version. You can try to either:

  Install and use SSDT for VS2015 release 16.5 (https://go.microsoft.com/fwlink/?LinkID=832313)
  Use your existing SSDT for VS2017, but always design your package targeting to latest SQL Server. When you want to deploy to SQL Server 2016, switch target server version to 2016, but DO NOT open the package again or launch deployment wizard. Instead, you should copy the dtsx into another empty project which does not use package part, and deploy that project.

  Sorry for the inconvenience. We will fix it ASAP.

NOTE: I have since then stopped using package parts and instead, I use parametrised child packages as an alternative.


How to remove Report Server only? I've gone ahead and configured the Report Server after SQL Server installation, and as part of that I've ended up with:
1) a report server database and
2) a link (I think in 2016 they call it Web Portal), where reports are to be deployed I guess.

I realized this was incorrect, I should not have configured/installed Report Server to begin with. I need to remove REPORT SERVER without removing Reporting Services. To be specific, I need the link (the Web Portal, or the link Report Server puts up) REMOVED and the Report Server database removed also.

The ONLY instructions I get are for removing Reporting Services, here and here , all that's talked about is removing Reporting Services. I just need the Report SERVER (DBs) and Report Server LINK to be removed. How do I accomplish that?

I'm using SQL Server 2016. Thank you very much in advance.

To take the Reporting Services databases out of commission on this server without disrupting the service itself, you'll need to point the service to databases elsewhere. To do this, open Reporting Services Configuration Manager on the server running the RS service, and change the "Database Name" entry in the Database menu page:

To shut down the Report Manager web portal, open up the RSReportServer.config file. This is located in the install directory (usually C:\Program Files\Microsoft SQL Server\[Instance Name]\Reporting Services\ReportServer).

Under the <Service> element, there will be several elements that begin with "Is". These control which services/functions are enabled. The last entry will be <IsReportManagerEnabled>.

Change this value from True to False. Save the file and close.

Some changes to RSReportServer.config require a service restart, so you may have to restart the RS service to get this change to stick. Once it does, the RS service will no longer respond to requests to the Report Manager portal but the separate web service that processes report requests will continue running.

You can read more about RSReportServer.config and switching individual services on/off here: https://msdn.microsoft.com/en-us/library/ms157273.aspx#bkmk_service


Importing CSV file into a PostgreSQL table I am using Python with pandas to import a CSV file into a table in Postgres

import pandas as pd
import psycopg2
from sqlalchemy import create_engine

df = pd.read_csv('products.csv', sep=';', low_memory=False)
engine = create_engine('postgresql://myuser:mypass@server/postgres')
df.to_sql('new_table', con=engine, if_exists='append', index=False, chunksize=20000)

The .csv file size is ~10GB. I left the script running for 15 hours but it's nowhere near finishing. What better way can I use to push the db to the server?

I can't import the db from the server directly because the compressed file size is larger than the size allowed.

I used psql to push the CSV file to the table, as suggested by @a_horse_with_no_name.

psql -h port -d db -U user -c "\copy products from 'products.csv' with delimiter as ',' csv header;"

It only took a couple of minutes to copy the table, compared to 10+ hours with the python script.


Index fragmentation increased significantly after rebuild I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter.  Mine might be such a scenario.

I have an index on a huge table (86 million rows) that was about 33% fragmented.

A few days ago I started noticing certain queries were taking too long.  It seemed like they weren't using one of the indexes even though the queries are written in such a way that they should use the index (I could be wrong about the index not being used)

So one of the things I did was look at the index fragmentation. It was 33%  I did a rebuild at a time when nothing would be using the table (except that as a test I did run a query on the table at the same time as rebuilding the index - Foolish perhaps).

The rebuild took approximately 10 to 20 minutes. For some reason I didn't check the fragmentation immediately after.  I checked it the day after (queries still slow) and saw that it had increased to 56.06%!

If I try another rebuild will I make it even worse? Should I try a reorganize instead? (I read that Microsoft recommends a reorganize if fragmentation is less than 30% and a rebuild if more than 30%)

Disclaimer: I am aware that there may be some bad practices going on here (size of table, design, or anything)  I didn't create this, I merely inherited responsibility for it.

Edit: I risked another rebuild.  This time checked immediately.  It was 0.01 fragmented.  I checked again ten or so minutes later and it was about 1% fragmented.  I checked again approximately one minute intervals and it's becoming gradually more fragmented.   Some hours later it's now 53.55% fragmented.

Output of @@version:

  Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
  Feb 10 2007 00:59:02
  Copyright (c) 1988-2005 Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

Autoshrink is on (shows 'true' in the database properties dialog box)

Edit : Some further information to give extra detail to the question....

At a fixed time each day the table gets a bulk update of approximately 50,000 to 100,000 rows of new data.  For the rest of the day there are no updates or new records,   it's purely queried for data.

The primary key is on 'recordid' and it is an auto increment field.  It's value is higher for new inserts than anything else in the table so there's no having to sandwich the data between existing data in the index.

Here is the create index code for the index in question...

USE [EWS]
GO

/****** Object:  Index [IX_AmtoteAccountActivity]    Script Date: 03/03/2016 21:07:14 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity] ON [dbo].[AmtoteAccountActivity]
(
    [AccountNumber] ASC,
    [_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

In case it matters/is relevant (I'm not sure if it is) here is the create index code for the primary key...

USE [EWS]
GO

/****** Object:  Index [PK_AmtoteAccountActivity]  Script Date: 03/03/2016 21:10:11 ******/
ALTER TABLE [dbo].[AmtoteAccountActivity] ADD  CONSTRAINT [PK_AmtoteAccountActivity]
PRIMARY KEY NONCLUSTERED
(
    [RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]
GO

Finally, as suggested by @Shanky and @KookieMonster I believe the fragmentation (which steadily grows after rebuild) is caused by auto_shrink being on for this database.

Just like @KookieMonster noticed, You have Auto Shrink turned on. And one of disadvantages of shrink commands is fragmenting Your indexes once again:

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


Calculation With VARCHAR I am attempting to do a calculation in SQL Server 2008 R2 but I keep getting an error of

  Msg 245, Level 16, State 1, Line 10
  Conversion failed when converting the varchar value '.05' to data type int.

Altering table structure is not an option, is there a way I can alter the query so that this will succesfully execute?

DECLARE @Test1 TABLE
(
  field1 VARCHAR(10),
  field2 INT
);

INSERT INTO @Test1 ( field1, field2 )
    VALUES ( '.05', 12 );

SELECT field1 / field2
FROM @Test1;

Is this what you want to happen?

DECLARE @Test1 TABLE
(
  field1 VARCHAR(10),
  field2 INT
);

INSERT INTO @Test1 ( field1, field2 )
    VALUES ( '.05', 12 );

SELECT field1 / CONVERT(DECIMAL(18, 2), field2)
    FROM @Test1;

Since this was marked as the answer: I converted the integer value to a decimal because of data type precedence. The string '.05' can't be converted to an integer, but it makes a perfectly fine decimal.


Really strange behavior using variables in MySql query I was answering a Question on Stack Overflow forum and the OP notice a really strange behavior that I could reproduce using the SQLFiddle site

So lets go to it.

We have this setup:

CREATE TABLE inventory
    (`animal` varchar(6), `date` date, `quantity` int);
INSERT INTO inventory
    (`animal`, `date`, `quantity`)
VALUES
    ('dog', '2015-01-01', 400),
    ('cat', '2015-01-01', 300),
    ('dog', '2015-01-02', 402),
    ('rabbit', '2015-01-01', 500),
    ('cat', '2015-01-02', 304),
    ('rabbit', '2015-01-02', 508),
       ('rabbit', '2015-01-03', 524),
       ('rabbit', '2015-01-04', 556),
       ('rabbit', '2015-01-05', 620),
    ('rabbit', '2015-01-06', 748);

The requirement of the OP is to have this result:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      500 |             8 |
| rabbit | 2015-01-02 |      508 |            16 |
| rabbit | 2015-01-03 |      524 |            32 |
| rabbit | 2015-01-04 |      656 |            64 |
| rabbit | 2015-01-05 |      620 |           128 |
| rabbit | 2015-01-06 |      748 |          null |
+--------+------------+----------+---------------+

So I did it as you can see it here: http://sqlfiddle.com/#!9/c77d8/182

It is working fine, but the OP said that on his side he was experiencing a strange behavior, the very first time that he runs the SQL it returns all null for the quantity_diff column and on the second time it returns -248 for the last quantity_diff.

So I asked what was that he did different and the only thing that I noticed is the MySql version. In my answer I've create the setup on the 5.6 version (5.6.21 on the above fiddle) and on his version he created the setup on the 5.5 (5.5.44-0ubuntu0.14.04.1) his fiddle

The more strange thing is that I've tested the same setup here on my machine with mysql version 5.6.21 (windows 7) and it behaves exactly as the user mentioned with the MySql Worckbench as client, but I could solve the problem adding the variable definition prior to the query execution as this:

set @qt:=null;
select animal,
       `date`,
       quantity,
       lead-quantity quantity_diff
from ( select i.animal,
              i.`date`,
              @qt as lead,
              @qt := i.quantity as quantity
        from inventory i
       where i.animal = 'rabbit'
       order by `date` desc
     ) tab
order by `date`;

So Is this some weird behavior? Or some known bug? Or (most probably) a server configuration regarding the use of variables @something in a query?

http://sqlfiddle.com/#!9/c77d8/189 - so after the query runs once in the current session, the last value is reused. That's expected behavior. MySQL user variables are session-specific.

Now why the 5.5 fiddle does not start with NULL - I suppose the fiddle runs EXPLAIN on the query before actual execution, and because of https://bugs.mysql.com/bug.php?id=44802 the derived table is materialized during that EXPLAIN (the subquery is executed) and so the variable already has a value in the actual run.

I tried to verify it in http://sqlfiddle.com/#!2/c77d8/16 - you can see that I am not running the query at all, only the EXPLAIN and it is enough for @qt to get the value of 500. That does not happen in the 5.6 version http://sqlfiddle.com/#!9/c77d8/190

So it is not exactly a bug, more a design/implementation limitation of optimizer before 5.6 which is documented (even when the case with user variables is not made clear, it is just a side effect of the documented behavior).


Use different Windows account in pl/sql Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean:

Other code...
LOG_IN_OTHER_USER('SPECIALUSER', 'SECRETPASSWORD');
UTL_FILE.PUT_LINE(OutputFile,the_outfile);
UTL_FILE.FFLUSH(OutputFile);
LOG_OFF_OTHER_USER('SPECIALUSER');
Other code...

Assuming 11g.
There are a few ways to do this. Easiest is to use the Oracle Job Scheduler with the Remote Job Agent. That agent can run using credentials for a qualified OS account. You could run a job that selects data from the database and spools it in the required output directory.

You could also copy a file to the Remote Job agent. This is closest to the code flow you described. For docu and examples see the book I wrote about the Oracle Scheduler.

There are no ways to switch OS accounts directly in pl/sql because whatever you do in the database, you are using the credentials of the Oracle Database Instance. The Oracle Remote Job Agent can use own credentials and is accessible from within pl/sql by using the dbms_scheduler package.


How to trigger a function with DDL query? I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored function like this:

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION checksql (
       stmt TEXT
   )
   returns int
BEGIN

   if locate("create", stmt) = 1 then
       if locate("primary key", stmt) = 0 then
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Primary key defined';
       end if;
   else
       SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Unknown SQL statement';
   end if;
   return 1;
END$$
DELIMITER ;

but I could not find a way to trigger a function whenever a  DDL get executed.

One way is:

START TRANSACTION;
SET autocommit=0;
SELECT checksql("CREATE TABLE ...");
CREATE TABLE ...;
COMMIT;

But this forces me to make the changes in my code. Is there any methods that i can use to trigger the function without making any code changes?

DDL statements terminate transactions.  So your attempt with START TRANSACTION; ... COMMIT; does not work.  (MySQL 8.0 may change that.)

If you are just checking for the existence of a PRIMARY KEY, suggest this (in a Stored Procedure) (and with a pre-created DATABASE test_area):

USE test_area;
CREATE TABLE ...  -- probably need prepare+execute+deallocate_prepare
abort if errors
IF ( ( SELECT ... FROM information_schema ...
    WHERE table_schema = 'test_area' ) )
THEN
    issue error message
END IF;
DROP TABLE ...;  -- probably need prepare+execute+deallocate_prepare


I'm trying to enable SQL Filestream on Windows Server 2012 and SQL Server 2016 64 bit, Management Studio 2017 The machine config is Windows Server 2012, 64 Bit SQL Server 2016, and SQL Server Management Studio 2017. When I use SQL Server Configuration Manager to enable filestream, it gives me an error that I do not know how to get around.

  There was an unknown error applying the FILESTREAM settings. check the
  parameters are valid. (0x80041008)

Root Cause?

During October we installed SQL Server 2016 on the server – but the desktop icons were still set to SQL Server 2017. When I used the 2016 Configuration Manager, it seemed to work.

Multiple versions installed on TEST Server:

Configuration manager on TEST Server with Configuration Manager 2017:

SQL Server Version on TEST Server:

Using SQL Server 2016 Configuration Manager:

So basically we were running the wrong version of configuration manager. Also we did a server restart. When I had previously tried to configure it on both 2016, 2017 configuration manager, it didn't work.


Comparing the values of two columns with different datatypes I have two columns in a table which need to be compared against the data.The problem that I am facing is that each of the columns have different datatypes: float and datetime2.

EventID EventStartDate
------- ---------------------------
201605  2017-09-21 12:40:29.9800000
201504  2017-09-21 12:40:46.8430000
201606  2017-09-21 12:41:39.3370000
201609  2017-09-21 12:41:57.3100000

Now, I have to compare the values in the column named EventID with the values in the column named EventStartDate; e.g., I need to compare the year and month in '201605' with the year and month parts of '2017-09-21 12:40:29.9800000'.

Here's an example of how you could use a combination of CONVERT, REPLACE and SUBSTRING to 'convert' your DateTime2 column to a float.

You can 'convert' the DateTime2 column to a varchar data type, then use SUBSTRING to retrieve the CCYY-MM part and then use REPLACE to eliminate the '-' in the date.

I added an extra row to the sample data so you could see that the compare works.

SET NOCOUNT ON
Declare @T TABLE (EventID float, EventStartDate Datetime2)
insert into @T VALUES
(201605, '2017-09-21 12:40:29.9800000'),
(201504, '2017-09-21 12:40:46.8430000'),
(201606, '2017-09-21 12:41:39.3370000'),
(201609, '2017-09-21 12:41:57.3100000'),
(201709, '2017-09-21 12:41:57.3100000')

SELECT *
    ,CONVERT(FLOAT, REPLACE(SUBSTRING(CONVERT(VARCHAR(7), EventStartDate), 1, 7), '-', ''))
FROM @T
WHERE EventID = CONVERT(FLOAT, REPLACE(SUBSTRING(CONVERT(VARCHAR(7), EventStartDate), 1, 7), '-', ''))

Results:

| EventID | EventStartDate              | (No column name) |
|---------|-----------------------------|------------------|
| 201709  | 2017-09-21 12:41:57.3100000 | 201709           |


MongoDB service not working after upgrade to Ubuntu 15.04 I have recently upgraded my Ubuntu from 14.10 to 15.04. All features seems to be working fine except MongoDB. It is not controllable from service as we do normally in Ubuntu.

sudo service mongodb start

or

sudo service mongod start

I get this error message

  Failed to restart mongod.service: Unit mongod.service failed to load:
  No such file or directory.

But I have to start it manually each time I have to connect my application to MongoDB server using

sudo mongod -f /etc/mongod.conf

So MongoDB is connected as long as that terminal is opened. I am using MongoDB in Ruby on Rails application using Mongoid adapater/ORM. Any help in this regard will be appreciated.

Since your error is:

Failed to restart mongod.service: Unit mongod.service failed to load: No such file or directory.

This is pretty much saying that your startup path is not in the expected location or else the Ubuntu install has led to problems. See: http://docs.mongodb.org/manual/tutorial/manage-mongodb-processes/

EDIT: Updated with link to what appears to be the answer, namely something is wrong with Ubuntu and mongo.

https://stackoverflow.com/questions/29879231/upgrade-to-ubuntu-15-04-from-14-10-breaks-mongo-how-to-fix

Joel Parke has defined the problem and pointed to a ticket on the issue. https://jira.mongodb.org/browse/SERVER-17742

That ticket reportedly explains how to "cleanly install current versions of mongodb on Ubuntu 15.04 by replacing the apt source."


What db permissions are needed for pgAgent user? I have successfully set up pgAgent daemon (running on the same server as Postgres 9.3). I would like to restrict permissions on pgAgent. Created 'pgagent' login role, and granted it (via group role) all permissions on postgres.pgagent schema:

CREATE ROLE pgagent LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE scheduler
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT scheduler TO pgagent;
grant all on schema pgagent to scheduler;
grant all on all tables in schema pgagent to scheduler;
grant all on all functions in schema pgagent to scheduler;
grant connect on database postgres to scheduler;

However pgAgent refuses to execute any jobs, and just sits there idly. No error messages in logs. If I start pgagent as 'postgres' user it runs fine. Or, if I 'grant postgres to scheduler' it also runs fine.

What permissions am I missing here?

Had this same issue!

Looking in the postgres log it appeared pgagent was failing to invoke one of the sequences in order to add entries into the job log.  Once I did the below (in postgres 9.3) and restarted my pgagents, all worked well:

GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_user;


SQLITE: A problem of tags and products I'm searching for a way to create a query to do the following:

Let's consider 3 tables:

products: List of products
tags: list of tags
tag_ties: table used to associate a tag to a product

Let's consider this structure for each table:

Products:

id (int, autoincrement)
name (varchar, name of the product)

Tags:

id (int autoincrement)
label (varchar, label of the tag)

Tag_ties:

id (int, autoincrement)
tag_id (int, reference to a tag id)
ref_id (int, reference to a product id)

What I want:

Obtain all the products who are tagged with tags id 10, 11 and 12 for example.

This query does not work, as it returns the products having at least one of the tags:

select
    p.name as name,
    p.id as id
from
    products p inner join tag_ties ties
on
    p.id=ties.ref_id
where
    ties.ref_id=p.id and
    ties.tag_id in (10,11,12)
group by
    p.id
order by
    p.name asc

You can solve this problem using intersect statements. Do a separate select for each tag_id and join them with intersects and you'll only get the records that match all three tag_ids.

select products.id, products.name from
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 10
intersect
select products.id, products.name from
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 11
intersect
select products.id, products.name from
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 12

Here's a reference article on using intersect

You can also use a temporary view to make this look a little nicer.

create temporary view temp_view as
select name, products.id as id, tag_ties.tag_id as tag_id
from products join tag_ties
on tag_ties.ref_id = products.id

select name, id from temp_view where tag_id = 10
intersect ...


Combine column from multiple rows into single row I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this delimited list from SELECT clause and COALESCE trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.

The data looks like this:

id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy

My results need to look like this:

id  customer_code comments
------------------------------
1   Dilbert        Hard Worker
2   Wally          Lazy

So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.

My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE
(
  id INT,
  row_num INT,
  customer_code VARCHAR(32),
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)


Create shared user mapping in PostgreSQL foreign data wrapper server We are using a foreign data wrapper to query across databases on a single PostgreSQL RDS. The foreign data wrapper server needs a user mapping for each user who will query against the remote server. However, adding the user mapping for each user may prove to be error prone.
All of our users who need to query against the foreign data server have a shared role, e.g. role_name, on our PostgreSQL server.
How can we share the foreign data wrapper server user mapping between users?

The only way to share a user mapping is if you define it for PUBLIC, but then it applies to all users. Groups don't work in that context, probably because it would cause ambiguity if several user mappings apply to a single user through inheritance.
If you don't want to go with a user mapping per user, you could opt to create one for PUBLIC but limit access to the foreign tables to role_name through permissions.
I know that this is not a great solution either, because it will allow all users to see the user mapping and the password in there (if there is one). But at least it is easy to GRANT SELECT ON ALL TABLES IN SCHEMA ... TO role_name.


FileStream Db restore Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)

The database's total size is 450 GB, from which 350GB is inital filestream size
The average picture size is around 200Kb, the total number is around 118,000, and same number of filestream files.
The database configuration has 1 filestream file
I have two virtual machines (with IFI enabled on both):

The first has read/write of 30MB/s, the second has 300MB/s
The backup on the first VM takes around 4 hours, restore takes around 40 hours
Restoring on the second VM takes around 4 hours

The question is, why is SQL server on both VMs not using max IO speed of disks when it is clearly available?
The only wait types during both restores are BACKUPIO and BACKUPITHREAD.

The question is, why is SQL server on both VMs not using max IO speed of disks when it is clearly available?

Your testing methodology:

  2.) Backup is a single file 3.) Slower one is on SAN with RAID 1, Faster one RAID 1 attached disks 4.) When doing the restore on both instances, servers are idle, and nothing except SQL server is active. Throughput is calculated by comparing fileshare copy/paste through resource monitor, by crystal disk mark, by disk speed during primary filegroup backup, by this restore with filestream.

Unfortunately the testing methodology was flawed, severely, when it comes to how SQL Server works.

  2.) Backup is a single file

This will cause SQL Server to use very little resources to read and write so as not to create too much contention on the files and volumes. To get the best backup/restore performance you'll need to backup and restore to/from multiple files and will probably have to change the buffercount and maxtransfersize options also.

  3.) Slower one is on SAN with RAID 1, Faster one RAID 1 attached disks

I would totally expect that, there would be more latency to get to the SAN than DAS. This probably (other than latency) won't effect the outcome too awful much, assuming the SAN has the same or more cache than the local disk controllers and the latency isn't too terrible.

  Throughput is calculated by comparing fileshare copy/paste through resource monitor, by crystal disk mark [...]

File copies and crystal disk mark are using multiple threads to read and write data combined with buffered I/O. They use completely different flags when opening/writing to files on disk, among even more differences. What's applicable to these tests isn't applicable to other applications or services. It's not that one is good or bad, just they behave completely different.

How can you speed this up?

Backup/Restore to multiple files
Change BUFFERCOUNT and MAXTRANSFERSIZE
Use separate disks on separate controllers for reading vs writing