Why does restarting MongoDB 3.0.x boost performance? I have a MongoDB in my Development environment running on Windows, the server has 64GB of RAM, but MongoDB has only consumed 26.9GB (here we are building the database), so it should be able to hold all the indexes and data in ram:

DB Stats:
Data Size: 27.8071 GB
Index Size: 3.96 GB

After 3 or 4 hours running, insert performance drops to only 2-3k inserts per second, despite all indexes being in RAM (or they should be).

However, I've noticed if I restart the server, insert speeds rates increase to x3 or x4.

I'm wondering why this is, and could it be caused by running on Windows?

The collections being inserted (with any frequency) have (at this time) 20 mil, 44 mil, and 51 mil.

The first collection of 20mil has a very random index based on a 256 bit hash, the other two collections are indexed on ObjectId. This is the bottle neck I cannot shift without sharding, but it is concerning that the insert rate changes so much after a restart.

I do not want to periodically restart my Primary node, causing clients to have to fail over to a secondary.

Edit: I should also say, I am running with write concern unacknowledged.

Please find example image of logs, apologies I did not capture a text output of this log.

Some typical slow queries when they are popped up by the profiler, set at 100ms:

2015-08-18T11:56:34.768+0100 I COMMAND  [conn7] command Slice_BTC.$cmd command: insert { insert: "Transactions", writeConcern: { w: 0 }, ordered: false, documents: 765 } keyUpdates:0 writeConflicts:0 numYields:
 acquireCount: { w: 7 } }, Database: { acquireCount: { w: 7 } }, Collection: { acquireCount: { w: 7 } } } 790ms
2015-08-18T11:56:35.184+0100 I QUERY    [conn10] query Slice_BTC.Outputs query: { $query: { t: ObjectId('55d2cf76137e6e233c231ecf'), i: 96 } } planSummary: IXSCAN { t: 1, i: 1 } ntoreturn:1 ntoskip:0 nscanned:1
:0 writeConflicts:0 numYields:0 nreturned:1 reslen:224 locks:{ Global: { acquireCount: { r: 1 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } 378ms

Typical size of Transactions object: 178 bytes.
Typical size of Outputs object: 204 bytes.

I believe the slowdown is caused by running in Windows. I am going to attempt this on a Linux box once our new server arrives.

However, the 26GB limit is because of the WiredTiger default configuration which only caches 50% up to max ram.

You can change this using the http://docs.mongodb.org/v3.0/reference/configuration-options/#storage.wiredTiger.engineConfig.cacheSizeGB option.


Restoring Oracle Database using Rman I have a problem with restoring the database using RMAN. I have a script that does a full backup of the database every one week. Logs show that the backup was created correctly.

set ORACLE_SID=MY_DB
set LOGFILE=path/full_%DATE%.log
set CMDFILE=path/restore.config

rman target / nocatalog cmdfile=%CMDFILE% msglog %LOGFILE%;

restore.config
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as compressed backupset incremental level 0 database format '\\path\data_%Y_%M_%D.%U';
backup as compressed backupset archivelog all delete all input format '\\path\archive_logs_%Y_%M_%D.%U';
backup as compressed backupset current controlfile format '\\path\control_files_%Y_%M_%D.%U';
backup as compressed backupset spfile format '\\path\spfile_%Y_%M_%D.%U';
CROSSCHECK BACKUP;
delete noprompt obsolete recovery window of 0 days;
}

Backup files are located on another server.
I prepared the file, which was to restore the database from the backup.

run {
shutdown immediate;
startup mount;
catalog start with 'path to dump files';
restore database;
recover database;
alter database open;
}

After this command I have this error:

unable to find archived log
archived log thread=1 sequence=13407
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/26/2016 12:16:26
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13407 and starting SCN of 111916667960

Recovery Manager complete.

Then I reworked the script but it did not help:

run {
shutdown immediate;
startup nomount;
restore controlfile from 'path to control file';
shutdown immediate;
startup mount;
catalog start with 'path to dumps\';
restore database;
recover database;
alter database open resetlogs;
}

and now i have this error:
Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'Path to db\SYSTEM01.DBF'

I would add that if I create a base and tries to create a backup and then restore it then these commands works.

Could someone explain to me why it does not work and how can I restore the database from this backup?

Both errors indicate that you need more archive log to complete media recovery and bring your data files to a consistent state.

If the datafiles are newer than the redo data you have available, the the alternative is to grab an older backup of the datafiles and work from there with the redo data you do have from the latest backup run. A common strategy is to continuously send archive logs to a backup destination between backups, and in that way minimizing the loss of data leading up to the next scheduled backup.

If you would rather aim to get an independently consistent backupset, you need to capture all the datafiles as well as all of the redo generated while backup was running. I would suggest something like this sequence of backup operations:

run{
backup database ;
sql 'alter system switch logfile';
sql 'alter system archive log current';
backup archivelog all ;
}

Note how you would first complete the database backup, then force a switch to the next log group before triggering an archive process and finally start backing up the archived logs. Those logs now have a much better chance of containing the redo data you need to run media recovery on files restored from this backup set.

UPDATE:

In your current situation, if this really is all the data you have to work with, I think you should still be able to bypass the ORA-1547 by pretending to do a manual incomplete media recovery and then open with resetlogs.

STARTUP FORCE MOUNT
RECOVER DATABASE UNTIL CANCEL;
CANCEL;
ALTER DATABASE OPEN RESETLOGS;


Using mysql's load infile to only populate certain columns I have a csv file with a lot of data in it and I want to upload it into a mysql table. However, I would like to keep one column (primary id) empty so I can edit it myself.
In mysql's documentation  it says that you can specify columns, but from my research it seems that you can only specify the columns of the csv file.

So is it possible to specify the columns of the table

Check this example:

# cat /tmp/db.txt
id,col1,col2
1,23,"dsafsdf"
2,-1,"ghfdhg"
7,9,"strsdrt"

# mysql test
mysql> CREATE TABLE `test` (
       `id` int(11) NOT NULL,
       `mycol1` int(11) DEFAULT NULL,
       `mycol2` varchar(20) DEFAULT NULL,
       PRIMARY KEY (`id`);

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, mycol1, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     23 | dsafsdf |
|  2 |     -1 | ghfdhg  |
|  7 |      9 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

If you want to specify certain columns:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, @ignore, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |   NULL | dsafsdf |
|  2 |   NULL | ghfdhg  |
|  7 |   NULL | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

And you can even set them with your own expressions:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY00 ','
       OPTIONALLY ENCLOSED by '"' IGNORE 1 LINES (id, @var1, mycol2)
       SET mycol1 = @var1 + 1;

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     24 | dsafsdf |
|  2 |      0 | ghfdhg  |
|  7 |     10 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

However, please understand that a primary key cannot be null nor duplicated, so you cannot set it to NULL unless it gets a default value, for example, because it is an auto_increment column.


Better database design I'm using a MySQL database to store data from my java application.
In java I have two classes:
The VocabularyList class has a name and holds a list of vocabularies.

A Vocabulary has a vocabulary field and a translation field. (E.g. vocabulary=bridge;translation=Brücke)
Currently my database looks like this:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`name`),
  UNIQUE KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `vocabularies` (
  `vocabulary` varchar(20) NOT NULL,
  `translation` varchar(20) NOT NULL,
  `id` int(11) NOT NULL
);

Question:

How can I ensure that if I delete a vocabulary list that all vocabularies with the id of the vocabulary list are also deleted from the vocabularies table?

Is my design bad and if yes, how can I improve it?

I would begin with a little tweak:

In your current design, there is no need to have "id" column on vocabularylists table. I will write it from top of my head, so apologies in advance for any syntactic mistakes. :)

First change:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

This way, you will have numeric ID and easily modifiable name of the vocabulary. This will make occasional renaming of vocabulary list a bit easier. But, of course, you can use only name columnt and totally strip out the id.

Second change:
You need to connect the vocabularies to vocabularylists with a foreign key constraint. The vocabularies table will look like this:

CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (list_id) REFERENCES vocabularylists(id) ON DELETE CASCADE
);

By creating foreign key, you ensure so-called referential integrity: from now on, your database cannot store vocabulaies that do not belong to a vocabulary list. Also (as ensured by ON DELETE CASCADE) any deletes on vocabularylists automaticcaly result to deletion of corresponding entries on vocabularies.

Third change:

Querying the vocabularies table will be slow. Put indexes on your tables:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  INDEX `name_idx` (`name`)
);
CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (`list_id`) REFERENCES vocabularylists(`id`) ON DELETE CASCADE,
    INDEX `vocabulary_idx` (`vocabulary`),
    INDEX `translation_idx` (`translation`)
);

This will give you much better performance.

A hint:

You have a very simple structure defined here. With little data, there will be no significant problems with performance. But the only thing you really need - looking at those two db tables - is a key-value store. So instead of a database, you could easily use a plain/CSV/... file which will be loaded on application startup into some in-memory store (at the beginning a hashmap, then something like Redis maybe).

Also a word in one language can have multiple meanings in another. Your database - as it is - cannot store those things. I don't know if it is desired behavior but it will be limiting you in case you are extending your application.


Oracle 12cR1 - Duplicate Database With No Resetlogs Database version: Oracle 12cR1
Operating system: Oracle Linux 7.x
ASM: yes

Duplicate script:

cat duplicatetodisaster.sh

ORACLE_UNQNAME=xxx
ORACLE_SID=xxx
ORACLE_HOME=xxx

rman target sys/***@PROD auxiliary sys/***@DRC <<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;
duplicate target database to 'db_name' noopen from active database;
}
exit;
EOF

We are trying to create a disaster database with the log shipping method. Basic logic; production database will be duplicated and a disaster database will be created that continues in mount mode. Then, archivelogs transferred from production database will be recovered by disaster database and their continuity will be ensured.
As you can see in the rman script I shared; I tried to prevent the database from doing resetlogs by adding the noopen parameter, but still, after the restore and recover successfully finished, the controlfile created and resetlogs the database. Because of this, all incarnations in the database, DBID parameter and everything to keep it in sync with the production database are broken. Based on this, the transferred archivelogs cannot be recovered and continuity cannot be maintained. How can I prevent this situation?
Below, I share where resetlogs does exactly from the alert log:
Completed: CREATE CONTROLFILE REUSE SET DATABASE "DB_NAME" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY     4382
 LOGFILE
  GROUP  11  SIZE 1536 M ,
  GROUP  12  SIZE 1536 M ,
  GROUP  13  SIZE 1536 M
 DATAFILE
  '+DATA/.../.../system...'
 CHARACTER SET WE8ISO8859P9

Another issue; there are about 250 datafiles, why did this happen for a single datafile...
Best regards,

So you described Data Guard with a standby database. If you want a standby database, tell RMAN to create a standby database:
duplicate target database for standby from active database;


Comparing two different ODBC system-DNS entries. Which is faster? PS script i want to find out which ODBC Connection is faster on my VM with Windows Server 2008 R2. I have two different ODBC providers. The first is "Oracle Instant Client ODBC" in v11. The other "Microsoft ODBC for Oracle". I thought, that i can use Powershell to test how long two simmilar querys run with each provider. (Later i also want to test if the MS Provider for Oracle is faster on Win08R2 or on Win12R2)

I found a nice script here: LINK

i modified it only a bit. So now i have tihs litte script:

 #SQL Authentication
 $t_start = Get-Date

 $connectstring = "DSN=instant;Uid=USER;Pwd=PASSWORD;"
 #Windows Authentication
 #$connectstring = "DSN=myDsn;"
 $sql = "SELECT * FROM TABLE1 WHERE ROWNUM <= 900;"

 $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
 $conn.open()
 $cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
 $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
 $dt = New-Object system.Data.datatable
 $null = $da.fill($dt)
 $conn.close()
 $dt | measure

 $t_end = Get-Date
 Write-Host ($t_end - $t_start)

It works fine for litte querys but the problem is that if i want to select more than 840 rows i get an error:

Exception calling "Fill" with 1 argument (s): "Input string was not in a correct format."
Line: 14 Sign: 2
    In Zeile:14 Zeichen:2
    +  $null = $da.fill($dt)
    +  ~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : FormatException

The result of the skript is:

Count    : 840
Average  :
Sum      :
Maximum  :
Minimum  :
Property :

00:00:01.4530713

If i change the SQL-Query to "ROWNUM <= 839" it works. If i change to "ROWNUM <= 841" i get this error.

My aim was to really make a big query like "ROWNUM <= 1.000.000"

The table i query has about 1.200.000 rows...

Is there a posibility to change something, so that i am able to query large tables? Or has anyone a nice freeware tool i can use to query ODBC-SYSTEM-DSN entries and get a usefull querytime so that i can compare my different ODBC's ?

I haven't done a lot of PS so not sure what the error is really saying but... it looks like you are trying to fill a local table with the output from the select *? If that is true, I am wondering if PS created a temp table with a number/int column type for rownum but the rownum value in row 840 or 841 is something other than a number/int. Or something similar for another column.

It is expecting one data type when setting up the the temp table but is finding another.

I have seen this happen more than a few times over the years of doing data etl.

Is rownum ALWAYS in number?

What does all the data row 840 and 841 look like?

What happens if instead of select * you itemized one field? i.e. SELECT [first_ name] FROM TABLE1 WHERE ROWNUM <= 900;

What if you changed the rownum? between 900 and 1800?

BTW: cool script!


Covering index used despite missing column I have the following query, using MariaDB 10 / InnoDB:

SELECT id, sender_id, receiver_id, thread_id, date_created, content
FROM user_message
WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY date_created DESC
LIMIT 20

This query fetches messages according to the given conditions and sorts by date created.

I have a covering index over (thread_id, date_created).

When running EXPLAIN, the correct index is used and I get the output "Using where", although the query is using a column in the middle of the statement that is not in the index. I can use any value for "placeholder = x" and the result is the same.

If I change the sorting to use another column, the EXPLAIN correctly indicates "Using where. Using filesort."

I'm having a head-scratching moment. Could anyone please shed light on this? What I would expect to see is that an additional filesort would be needed as the covering index could not be completely used due to the additional column.

Case A
Query:

WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY some_column DESC
LIMIT 20

Index:

(thread_id, date_created)

Plan:

Index is used
Using Where
Using filesort

No problem there, right? If the index is used (to partially match the WHERE condition), we still need a sort operation to order the results by some_column (which is not in the index). We also need an extra check (Using Where) to keep only the rows that match the 2nd condition, too. OK.

Case B (the question)
Query:

WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY date_created DESC
LIMIT 20

Index:

(thread_id, date_created)

Plan:

Index is used
Using Where
-- no "Using filesort"

So, why does it not need a sort here? Because the index is enough to sort as the query wants. There is of course the additional problem of the extra condition (AND placeholder = FALSE) which is not covered by the index.

OK but we don't really need a sort here. The index can provide us with results that match the first condition (WHERE thread_id = 12345) and are in the wanted order for output. The only additional check we need - and what the plan does - is to get the rows from the table, in the order provided by the index, and check this 2nd condition until we get 20 matches. That's what the **Using Where"" means.

We may get the 20 matches in the first 20 rows (so really good and fast) or in the first 100 (still likely fast enough) or in the first 1000000 (probably very, very slow) or we may get just 19 matches from the table even after reading all the matching rows from the index (really very slow on a big table). It all depends on the distribution of data.

Case C (even better plan)
Query:

WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY date_created DESC
LIMIT 20

Index:

(placeholder, thread_id, date_created)

Plan:

Index is used
-- no "Using Where"
-- no "Using filesort"

Now  our index matches both conditions and the order by. The plan is pretty simple: get the first* 20 matches from the index and read the corresponding rows from the table. No extra check (No "Using Where") and no sort (no "Using filesort") needed.

first*: the first 20 when reading the index backwards from the end (as we have ORDER BY .. DESC) but that's not a problem. B-tree indexes can be read forwards and backwards with almost equal performance.


I want to Create a brand new slave for my MySql Percona server 5.7 I have a Master Master Replication setup using mysql Percona Server 5.7, but for some unknown reasons, seems like my passive server is broken "thats not the issue", the replication is bad and the data been bad in the passive for a while.

I have decided to create a new Passive server from the current active.
What is the best way to create a new passive server "from the active one" without or with minimum downtime? given that:

bin logs set on Active and current passive
Relay log is set
log expire days = 3
sync-binlog = 1
binlog-format = ROW
log-slave-updates = ON

please help

Based on your comments- using Linux with a non-trivially small sized database, I will recommend you to use Percona XtraBackup ( https://www.percona.com/software/mysql-database/percona-xtrabackup ) to generate a copy of your current active database in a fast way and in an almost 100% hot way (no read or write blocking except to gather the binlog position, which normally should only take less than 2 seconds).

You can follow the steps at: https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/setting_up_replication.html (How to setup a slave for replication in 6 simple steps with Percona XtraBackup).

I asked those questions because the alternative is mysqldump/mydumper, but that will take more to recover for your data size (also XtraBackup didn't use to support non-linux). Here is how to do it ina hot way just in case: https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

A common mistake in both cases is thinking you need to stop writes or shutdown your master- you don't, just make sure you start replicating from the coordinates corresponding to the moment you took your backup (the end of XtraBackup run or the start of mysqldump/mydumper).

To prevent data drifts in the future consider performing regular consistency checks with tools such as pt-table-checksum or migrating to row based replication.


Create a read-write user and a read-only user I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have:

Either on public schema, or on a custom schema I don't especially care
A read-write user
A read-only user
The read-write user should be able to create a table in the schema
The read-only user should be able to read from this new table automatically, i.e. without an additional GRANT

I tried to follow this AWS blog post and this answer but it seems I'm hitting a wall.

I've tried the following to no avail, postgres user is the user created by RDS that has the role rds_superuser:

-- Making sure roles can't do anything on the database without explicit grant
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE my_database FROM PUBLIC;
CREATE SCHEMA my_schema;

-- Read-write
CREATE ROLE read_write;

GRANT CONNECT ON DATABASE my_database TO read_write;
GRANT USAGE, CREATE ON SCHEMA my_schema TO read_write;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT USAGE ON SEQUENCES TO read_write;

-- Read-only
CREATE ROLE read_only;

GRANT CONNECT ON DATABASE my_database TO read_only;
GRANT USAGE ON SCHEMA my_schema TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO read_only;

-- Users
CREATE USER read_write_user WITH PASSWORD 'password_1';
GRANT read_write TO my_database;

CREATE USER read_only_user WITH PASSWORD 'password_2';
GRANT read_only TO my_database_read_only;

When I connect to the database with user read_write_user, I can create a new table my_table, but if I connect with user read_only_user, I can't access it, I get the following error: permission denied for table my_table.

I connected back with the user postgres and saw that I didn't have rights to grant access to this table to read_only role:

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO read_only;

leads to this error: no privileges were granted for "my_table".

So if I understand correctly postgres user doesn't have enough rights to grant access to objects created by read_write_user and the ALTER DEFAULT PRIVILEGES didn't do what I thought it would do.

I tried to add the following queries on a new database before revoking/granting anything:

GRANT ALL ON DATABASE my_database TO postgres;
GRANT ALL ON SCHEMA my_schema TO postgres;

and it didn't help.

I have the feeling that this is linked to the fact the rds_superuser is not a real superuser but I don't know how to go forward, is there a way to do what I'm trying to do?

ALTER DEFAULT PRIVILEGES only affects tables created by the user who ran the ALTER DEFAULT PRIVILEGES statement.

You'd have to do this:

ALTER DEFAULT PRIVILEGES FOR ROLE read_write IN SCHEMA my_schema
   GRANT SELECT ON TABLES TO read_only;


Defining SQL Server domain/AD service accounts during installation vs. after the fact. Anything outside of "personal preference/time saving"? Kind of a silly question, but, I'm installing multiple SQL Servers for a client, but they do not have a domain service account for SQL Server created for me yet. They will have one ready for me soon... Does it really make any difference defining the Service Accounts during installation vs. adding them after the fact via configuration manager?

Probably more of a personal preference/time saving question, but I was wondering if there was anything outside of that. I just prefer to have as much done during the installation process rather than going back and adding the domain account after the fact.

Although it is going to be a bit of a pain to go back and change all of them, even if it can be done with scripts, there is one factor that I would consider beyond all of the technical stuff that can obviously be addressed: Anxiety.

Given the two scenarios:

Installed with domain service accounts
Installed without domain service accounts and changed afterwards

Assume that something goes wrong a few days after all of these servers get put into production.  Let's say a Windows security update gets installed on domain controllers, or the organizations routers get patched, or some new security software gets pushed out to every server.  Suddenly, your SQL Servers stop working because they get login failures.  You may not know about the change in the environment.  If you went with scenario #1, you're going to have zero, or much less, anxiety that the installation/setup process was the cause of the issue.

If you used scenario #2, you are going to have more anxiety that you missed something, screwed something up, etc.  I would, anyway.  Some people wouldn't, but I think I lot of us would have a bit of panic.  Additionally, would other people in the project possibly start pointing out the post-install service account change if there was an outage of this nature?  Regardless of whether it is a reasonable concern, someone may start questioning anything that is out-of-the-ordinary.

So if it's a server or two and an outage isn't going to cause you any anxiety--no big deal.  But if an outage is going to cause anxiety, I would stick with scenario #1 if possible.


Does index occupy disk memory for rows for which index expression is NULL? Let's assume that I have following pg_trgm index on mydata JSONB column:

CREATE TABLE mytable (mydata JSONB);
CREATE INDEX trgm_index_1 ON mytable
USING gin ((mydata#>>'{myfield}') gin_trgm_ops);

And let's assume that I will insert following data into mytable:

500k rows with mydata = { "myfield": "some_text" }
500k rows with mydata = { "another_field": "some_text" }

So 1M rows in total, but half of them contains value that is used in index expression (mydata#>>'{myfield}'). Does it mean that index will use ~50% less memory on disk than it would use if all of rows contained myfield json field?

Let's simply try it!

I've created the table from your question, then did the following:

INSERT INTO mytable
SELECT json_object(ARRAY['myfield', i::text || 'bla'])::jsonb
FROM generate_series (1, 100000) t(i);

\di+ trgm_index_1

 Schema │     Name     │ Type  │ Owner  │  Table  │ Size  │ Description
────────┼──────────────┼───────┼────────┼─────────┼───────┼─────────────
 test   │ trgm_index_1 │ index │ avaczi │ mytable │ 19 MB │

So far, we have only rows where myfield is present.  Let's now add some other rows, too:

INSERT INTO mytable
SELECT json_object(ARRAY['other_field', i::text || 'bla'])::jsonb
FROM generate_series (100001, 200000) t(i);

After this, the reported index size has changed to 22 MB, meaning that the new rows are also accounted for.  If you want to exclude them, you have to create a partial index:

CREATE INDEX trgm_index_partial ON mytable
USING gin ((mydata#>>'{myfield}') gin_trgm_ops)
WHERE mydata ? 'myfield';

The size of this one is initially the same (19 MB) as the non-partial index, but after adding the second group of rows, it does not change:

\di+ trgm_index_*

 Schema │        Name        │ Type  │ Owner  │  Table  │ Size  │ Description
────────┼────────────────────┼───────┼────────┼─────────┼───────┼─────────────
 test   │ trgm_index_1       │ index │ avaczi │ mytable │ 22 MB │
 test   │ trgm_index_partial │ index │ avaczi │ mytable │ 19 MB │


Create Text File From Stored Procedure I have an ETL process (in SSIS) that extracts data from the database and writes the data to a predefined text file. Currently, I create the directories and files manually to satisfy this process.

To add a new variable to this process, I run a usp that adds a new row to a table, which is read in to the ETL process and determines which packages to run.

To remove the manual creation of the directories and files mentioned above, and to remove the possibility of human error, I want to create the new directory and file when a new row is added via the usp.

In short; I check to see if the subdirectory exists using xp_dirtree, and then use xp_create_subdir to create the new subdirectory. What I want to do now is create an empty text file, in the new subdirectory, that will have data written to it during the ETL process.

The options I think I have are the following:

Use xp_cmdshell; I recognise that there is a stigma attached to this, but I am not adverse to using it if it is the most optimal way. This is disabled by default, but I can reconfigure on and off within the usp.
Use fso; I'ved used fso within .NET scripts, but have no experience of using it within SQL Server. I can't seem to find any resources that dictate how to create a text file, only read and write.
Use SSIS; within the ETL project I have a flat file connection manager, where the connection string is dynamically set, based on the name of the variable. I'm unsure if the text file will automatically be created if it does not exists? I have manually created them to avoid any chance of failure. If the text file is automatically created, this could be an option.

I've purposely left BCP out of the options above, as I don't have a result set to copy, and therefore presume this is not a viable option? Unless I can "copy" an empty dataset to create the text file?

Can anybody provide any useful resource for how to accomplish this? Or can anybody provide any pointers on how best to achieve this? Is xp_cmdshell the best way? I want to create an empty text file when I create the new subdirectory.

Thanks in advance for any help offered.

You could use SQL CLR. This allows (more-or-less) any C# code to be executed from within SQL Server as if it were a stored procedure. This includes OS and file system manipulation. The code can accept values from and return values to T-SQL. Permissions can be controlled just like any other stored procedure, sidestepping many of the concerns with xp_cmdshell.


Understanding SQL Server Cache I have a tricky question and I do not exactly know where to find relevant information to this.

I have SQL Server 2014 Enterprise installed on a database server. On this server there exist multiple databases, some belong to us and some to other companies.

My concern is with cache memory. I have a basic understanding of how SQL Server uses cache memory to retrieve data pages fast. You run a query, and usually, data pages get loaded to the cache and with repeatable calls, it gets results from this.

But... our application is highly used in usual working hours by our customers but at night there are always maintenance tasks going on. I am assuming (please tell me if I am wrong) that during the day, the cache gets loaded with useful data pages, but at night it is deleted because of the maintenance tasks, and so the next day, when customers start doing transactions, data pages have to be again loaded into the cache first, which takes time and slows a lot of things down.

So, can I somehow prove if that happens?

If yes, can I manage it? Such as somehow telling SQL Server to not load into cache?

I would be grateful for some specific articles discussing this topic. I could not find any, or better say, I do not know how to name this problem.

but at night it is deleted because of the maintenance task

I would not say deleted as more "overwritten" or "evicted". Maintenance may simply use the memory for other things. Not a big issue normally.

  and so a next day, when customers do transactions, data pages have to be again loaded
  to  Cache, which takes time and slows a lot of things down.

If that is really that brutal the case, you have either an edge case or a hardware problem.

Generally you can preseed the cache by running a number of "maintenance queries" in the early morning (to reload the data into the cache at 0600 in the morning for example) as part of maintenance. I.e. you end it by querying for the data you want loaded into the cache. Problem solved.

But generally this should not result in an EXTREME slowdown - yes, initial requests will be slower, but that should fix itself quite fast as the cache is going to fill up with every query. Is it possible that your experienced slowdown is not "cache" but "morning spike"? There are a lot of systems where at specific times (one of them the morning when people log in and check their work) the load is a lot higher than later during the day. In this case, you need to scale the hardware up to handle this. WOuld be among the first times I would have seen maintenance as a very significant issue.


SSIS package deployment. Changing protection level takes a very long time We are in the process of migrating our servers to a new data center. Currently we are on SQL Server 2014 and SSIS packages deploy in seconds.

The new data center has several SQL server 2016 installs and I noticed that on some of them the "Changing protection level" takes a very long time (around 7 minutes!) but on some it is fast. When I change the SSIS project's TargetServerVersion to 2016 the changing protection level is slow on all new servers.

The ProtectionLevel of the project and packages is set to (and has been) to DontSaveSensitive.

What is causing the slowness and how can I troubleshoot this? It eventually does deploy without any errors.

It seems that I solved my own problem. I do not know exactly what cause it, but here is what i did.

It turns out that only one of the projects that we have had the problem I described. That project happened to have multiple packages included. For testing I removed all packages from the project and added one at the time, built the project after each step and tested the deployment. They all worked fine individually and ultimately I ended up with a project with all packages that deploys fast.

This got me thinking that maybe the project file/build files got hosed somehow and they all needed a full refresh/rebuild.


Putting SQL Server databases into individual folders We have a Dev server where we are trying out some automated partitioning scripts.

The folder is getting quite large in terms of number of files.

Is it possible to automatically have SQL Server use a sub folder for all files pertaining to each one of the databases using the database name as a sub-folder name?

Now this should really be a feature, I tried to find it on Microsoft connect but did not if anyone has a way to do this via database properties I for one would be very happy.

But as it stands this is not possible except by changing the default database location registry setting either in the server properties or like this.

USE [master]
GO
— Change default location for data files
EXEC   xp_instance_regwrite
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultData',
   REG_SZ,
   N'C:\MSSQL\Data'
GO
— Change default location for log files
EXEC   xp_instance_regwrite
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultLog',
   REG_SZ,
   N'C:\MSSQL\Logs'
GO

But if you have multiple databases on the server you would have to remember to change this before adding a file to each of them.

There is also the possibility to to this dynamically, you could create a stored procedure that you call with a database name that will read the current path for the mdf file and add a new file to the same path so to borrow a bit from stackexchange

create procedure USP_ADDFILE

@database sysname,
@logicalname sysname,
@filename sysname,
@filegroup sysname,
@size int
AS
if @database is NULL return
if @filename is null return
if @filegroup is null set @filegroup = 'Default'
if @size is null return
Declare @Path nvarchar(max)
Declare @Sql nvarchar(max)

select @Path = (select LEFT(physical_name
                   ,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
    FROM sys.master_files mf
    INNER JOIN sys.[databases] d
    ON mf.[database_id] = d.[database_id]
    WHERE d.[name] = @database AND type = 0)

Set @Sql = 'Alter Database '+ QuoteName( @database )+'
    Add File( Name = '''+ @filename +'''
            , SIZE = '+ CAST (@size as nvarchar(10)) +'GB
            , FileName = ''' +  @Path+@filename+'.NDF'''
            + ') To FileGroup '+QuoteName(@filegroup)  +''
exec ( @Sql )


Columns are added in wrong order I have a encountered strange behaviour adding columns to an existing table. I run those scripts using osql several instances.

Let Table a_table have initially three columns A_col, B_col and C_col.

To extend this table I use the following statements:

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
            WHERE COLUMN_NAME = N'D_col' AND TABLE_NAME = N'a_table')
BEGIN
ALTER TABLE [dbo].[a_table] ADD [D_col] int DEFAULT -1 NOT NULL
END

Up to now this script worked fine. I have a script adding two batches of columns (15 each) separated with a GO statement.

IF NOT ... -- col 1
...
END
:
:
IF NOT ... -- col 15
...
END
GO

IF NOT ... -- col 16
...
END
:
:
IF NOT ... -- col 30
...
END
GO

I expect the columns 1-15 to be added first and 16-30 second. But it happens from time to time that the second batch gets executed first so the columns appear in the wrong order (16-30, 1-15).

Any explanation for this behaviour is appreciated!

Finally we were able to sort it out: It was a GO in a block comment /* ... GO ... */.

We tested the code in SSMS were the GO is ignored (as it is in a block comment). osql does not ignore it! We had the two batches in this block comment in a previous update. The first was ignored. The second was executed because the GO in the comment broke the comment.

Thanks to @gbn, @JNK and @Brandon for their help!


Which databases are recommended to be used as embedded databases? Which databases are recommended to be used as embedded databases to store data within an application?  The embedded database may or may not synchronize back to a larger system database.

A list can be found on Wikipedia. I think the overwhelming leader in embedded sql databases is sqlite. Found everywhere!


How to select sets of data from a table I have a table containing data that represents a tree structure of data, using the nested set model.  I'd like to store data for more than one tree in the table and have only one of the sets of data be available.  The idea is that there may be more than one version of the tree and even trees in development before going "live" with them.

My question is about where to safely store the "This is the live set" flag.

This is what I have (minimalized):

TreeDataSets
id
DataText
...
VersionId

Versions
id
VerName
InUse

Version
id
InUse

TreeDataSets
ID  DataText    VersionId
1   ABCD           100
2   EFGH           100
3   IJKL           100
4   1234           101
5   5678           101
6   3456           101
7   AB12           102
8   CD34           102

Versions
ID  Vername     InUse
100 Characters  Y
101 Numbers     N
102 Mixed       N

Version
ID  InUse
1   100

So, I can either store the "InUse" flag against every row of the versions table, or have it in a separate table.  (Both option are shown - I hope you get the idea).

Disadvantages with storing the flag in every row is that if more than one row is set to InUse, the data will be messed up.  The disadvantages with storing the flag in an extra table is err, that there is an extra table and sql is a little more complex.

Is this a common data storage pattern?  What is the best practice?

Is there a way of enforcing only one row is set to true within the database, if the 2 table option is preferred?

With one table you would need to limit it to a single row.

I think a trigger on InUse would be a better solution.


Mongo large chunks will not split I had a 3 shard set up, and ran out of capacity, so I added 3 more shards. (Each shard is a replica set).  But the data isn't spread across the cluster evenly.  I have my chunkSize set to the standard 64mb:

mongos> db.settings.find( { _id:"chunksize" } )
{ "_id" : "chunksize", "value" : 64 }

I thought this meant that when a chunk hits 64mb, it splits into two equal chunks both of size 32mb.  That's what is demonstrated here.  Is that not correct?

Here is my sharding distribution:

mongos> db.accounts.getShardDistribution()
Shard rs_0 at rs_0/mongo_rs_0_member_1:27018,mongo_rs_0_member_2:27019,mongo_rs_0_member_3:27020
 data : 137.62GiB docs : 41991598 chunks : 1882
 estimated data per chunk : 74.88MiB
 estimated docs per chunk : 22312

Shard rs_1 at rs_1/mongo_rs_1_member_1:27018,mongo_rs_1_member_2:27019,mongo_rs_1_member_3:27020
 data : 135.2GiB docs : 41159069 chunks : 1882
 estimated data per chunk : 73.56MiB
 estimated docs per chunk : 21869

Shard rs_2 at rs_2/mongo_rs_2_member_1:27018,mongo_rs_2_member_2:27019,mongo_rs_2_member_3:27020
 data : 219.92GiB docs : 69739096 chunks : 1882
 estimated data per chunk : 119.66MiB
 estimated docs per chunk : 37055

Shard rs_3 at rs_3/mongo_rs_3_member_1:27018,mongo_rs_3_member_2:27019,mongo_rs_3_member_3:27020
 data : 101.52GiB docs : 30650628 chunks : 1882
 estimated data per chunk : 55.23MiB
 estimated docs per chunk : 16286

Shard rs_4 at rs_4/mongo_rs_4_member_1:27018,mongo_rs_4_member_2:27019,mongo_rs_4_member_3:27020
 data : 103.38GiB docs : 31071379 chunks : 1883
 estimated data per chunk : 56.22MiB
 estimated docs per chunk : 16500

Shard rs_5 at rs_5/mongo_rs_5_member_1:27018,mongo_rs_5_member_2:27019,mongo_rs_5_member_3:27020
 data : 101.1GiB docs : 30516395 chunks : 1881
 estimated data per chunk : 55.04MiB
 estimated docs per chunk : 16223

Totals
 data : 798.77GiB docs : 245128165 chunks : 11292
 Shard rs_0 contains 17.23% data, 17.13% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_1 contains 16.92% data, 16.79% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_2 contains 27.53% data, 28.45% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_3 contains 12.7% data, 12.5% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_4 contains 12.94% data, 12.67% docs in cluster, avg obj size on shard : 3KiB
 Shard rs_5 contains 12.65% data, 12.44% docs in cluster, avg obj size on shard : 3KiB

What's up with this? How can the first 3 shards/replica sets have an average size greater than 64mb when that is set to be chunkSize? Rs_2 is 119mb!  Rs_2 has 27.53% of the data when it should have 16.6%.

I have very high cardinality in my shardkey, and it is not in a monotonically increasing one.

What should I do here? I can manually find chunks that are large and split them, but that is a pain.  Do I lower my chunkSize? Is there some service/call I need to run to do this automatically?

Lot to go through here, so I'll take it piece by piece, first off splitting:

  I thought this meant that when a chunk hits 64mb, it splits into two
  equal chunks both of size 32mb. That's what is demonstrated here. Is
  that not correct?

That's not quite how it works.  If you have a 64MB chunk and you manually run a splitFind command, you will get (by default) 2 chunks split at the mid-point.  Auto splitting is done differently though - the details are actually quite involved, but use what I explain as a rule of thumb and you will be close enough.

Each mongos tracks how much data it has seen inserted/updated for each chunk (approximately).  When it sees that ~20% of the maximum chunk size (so 12-13MiB by default) has been written to a particular chunk it will attempt an automatic split of that chunk.  It sends a splitVector command to the primary that owns the chunk asking for it to evaluate the chunk range and return any potential split points.  If the primary replies with valid points, then the mongos will attempt to split on those points.  If there are no valid split points, then the mongos will retry this process when it the updates/writes get to 40%, 60% of the max chunk size.

As you can see, this does not wait for a chunk to reach the max size before splitting, in fact it should happen long before that and with a normally operating cluster you should not see such large chunks in general.

  What's up with this? How can the first 3 shards/replica sets have an
  average size greater than 64mb when that is set to be chunkSize? Rs_2
  is 119mb!

The only thing preventing large chunks from occurring is the auto-split functionality described above.  Your average chunk sizes suggest that something is preventing the chunks from being split.  There are a couple of possible reasons for this, but the most common is that the shard key is not granular enough.

If your chunk ranges get down to a single key value then no further splits are possible and you get "jumbo" chunks.  I would need to see the ranges to be sure, but you can probably manually inspect them easily enough from sh.status(true) but for a more easily digestible version take a look at this Q&A I posted about determining the chunk distribution.

If that is the issue you only really have 2 choices - either live with the jumbo chunks (and possibly increase the max chunk size to allow them to move around - anything over the max will be aborted and tagged as "jumbo" by the mongos), or re-shard the data with a more granular shard key that prevents the creation of single key chunks.

  Rs_2 has 27.53% of the data when it should have 16.6%.

This is a fairly common misconception about the balancer - it does not balance based on the data size, it just balances the number of chunks (which you can see are nicely distributed) - from that perspective a chunk with 0 documents in it counts just the same as one with 250k documents.  Hence, the reason for the imbalance in terms of the data is because of the imbalance in the chunks themselves (some contain a lot more data than others).

  What should I do here? I can manually find chunks that are large and
  split them, but that is a pain. Do I lower my chunkSize?

Lowering the chunk size would cause the mongos to check for split points more frequently, but it won't help if the splits are failing (which your chunk size averages suggest is the case), it will just fail more often.  As a first step I would find the largest chunks (see the Q&A link above) and split those as a priority first.

If you are going to do any manual splitting or moving, I recommend turning off the balancer so that it is not holding the meta data lock and does not kick in as soon as you start splitting.  It's also generally a good idea to do it at a low traffic time because otherwise the auto-splitting I described above could interfere also.

After a quick search I don't have anything generic immediately to hand, but I have in the past seen scripts used to automate this process.  It tends to need to be customized to fit the particular issue (imagine an imbalance due to a monotonic shard key versus an issue with chunk data density for example).


Oracle how do I find out the max number of concurrent sessions? I am creating an application with ASP.Net and I wanted to figure out how many users concurrently connect to our database. (the existing app is a Windows app and there is no way to get this information).

I am not looking for live data but what the max # of users had been at any point. If there is a way to specify a date range than that would be preferred.

Thank you

Right now we are using 11.x but will migrate to 12 (or higher)

The below returns the maximum number of concurrent sessions in the instance since it started, example:

SQL> select max_utilization from v$resource_limit where resource_name = 'sessions';

MAX_UTILIZATION
---------------
             94


Minimum SQL Server rights that allow viewing column default values I have an SQL Server 2008 database, some of the fields (columns) in some of the tables are configured with default values.

I have a requirement to limit user security as much as possible, ideally only allow read/write permissions. However, the users need to be able to see what the default values for the columns (well it's actually an application that needs to read the defaults).

Normally I can give the user access to the following roles if I want read/write only:

db_datareader
db_datawriter

The problem is that this prevents access to the default values. This can be confirmed by opening a table is design view (they get a message saying they have limited access and can only view the table design), and the default values are just shown blank.

If I add the db_owner role then they can view the default values fine, but I don't want to give the owner powers.

Is there something else I can do in order to restrict to only read/write but with the extra ability to to see what the default values are?

This will allow the defaults to show up in the lower pane of the table designer:

GRANT VIEW DEFINITION ON dbo.tablename TO [username];

Of course this doesn't give them the ability to make any changes to the table, and in general I discourage the use of the designers anyway. They're littered with bugs and inconsistencies. With VIEW DEFINITION rights, your user can also pull this information from the catalog views:

SELECT
  [constraint] = d.name,
  [column] = c.name,
  [default] = d.definition
FROM sys.default_constraints AS d
  INNER JOIN sys.tables AS t
  ON d.parent_object_id = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE s.name = N'dbo'
    AND t.name = N'tablename';

  Is there a way to do something similar for whole tables of database? I mean, about the GRANT VIEW DEFINITION ON dbo.tablename TO [username];.

Sure, dynamic SQL is very handy here.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N''GRANT VIEW DEFINITION ON '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
  + N' TO [username];' + CHAR(13) + CHAR(10)
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 “No such file or directory”) when configuring cluster I have 3 MariaDB nodes.
Want to configure cluster.
Specified in gcomm:// in my.cnf ips of all members in cluster according to this.

And added ?pc.wait_prim=no at then of gcomm://ip1,ip2,ip3

Then mysqld process starts on each VM, but when i try to it connect with mysql -uroot -ppass in order to run

SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

error pops:

  Can't connect to local MySQL server through socket
  '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

error.log of each process shows:

2018-02-01 15:13:23 140529975699392 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer '10.0.0.42:,10.0.0.44:,10.0.0.43:'
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 71d7b5ed tcp://10.0.0.44:45
2018-02-01 15:13:23 140529975699392 [Warning] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') address 'tcp://10.0.0.44:4567' points to own listeddress, blacklisting
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 6ac0d002 tcp://10.0.0.42:45
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2018-02-01 15:13:23 140529975699392 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection established to 6bd1b1f9 tcp://10.0.0.43:45
2018-02-01 15:13:23 140529975699392 [Note] WSREP: gcomm: connected
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Opened channel 'my_wsrep_cluster'
2018-02-01 15:13:23 140529975699392 [Note] WSREP: Waiting for SST to complete.
2018-02-01 15:13:23 140529635424000 [Note] WSREP: declaring 6ac0d002 at tcp://10.0.0.42:4567 stable
2018-02-01 15:13:23 140529635424000 [Note] WSREP: declaring 6bd1b1f9 at tcp://10.0.0.43:4567 stable
2018-02-01 15:13:23 140529635424000 [Warning] WSREP: no nodes coming from prim view, prim not possible
2018-02-01 15:13:23 140529635424000 [Note] WSREP: view(view_id(NON_PRIM,6ac0d002,2) memb {
        6ac0d002,0
        6bd1b1f9,0
        71d7b5ed,0
} joined {
} left {
} partitioned {
})
2018-02-01 15:13:23 140529627031296 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 2, memb_num = 3
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Flow-control interval: [28, 28]
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Trying to continue unpaused monitor
2018-02-01 15:13:23 140529627031296 [Note] WSREP: Received NON-PRIMARY.
2018-02-01 15:13:23 140529975253760 [Note] WSREP: New cluster view: global state: :-1, view# -1: non-Primary, number of nodes: 3, my ind protocol version -1
2018-02-01 15:13:23 140529975253760 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2018-02-01 15:13:26 140529635424000 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') connection to peer 71d7b5ed with addr tcp://10.0.0.44timed out, no messages seen in PT3S
2018-02-01 15:13:26 140529635424000 [Note] WSREP: (71d7b5ed, 'tcp://0.0.0.0:4567') turning message relay requesting off

Why does it happen and what's wrong?

Check your config files /etc/my.cnf and /etc/my.cnf.d/server.cnf and look for the socket parameter. Make sure it's the same for client and server. A good place for the socket parameter is in the [client-server] section in /etc/my.cnf. The socket file is created by the MariaDB server when it starts up.

Note that there are other places where MariaDB config files could reside, and these could potentially override your settings in the two files mentioned above. For more details, see this page in the MariaDB KB.

Also note that you may be able to connect to MariaDB through TCP rather than through the socket. You can specify that you want to use TCP rather than the socket with mysql ... --protocol=TCP. If this is successful, you can then do SHOW VARIABLES LIKE 'socket'; to find out where the socket file really is (or should be). You can then connect with mysql ... --socket=/path/to/file.sock or edit the config files so that the client reads it correctly from there.

In some cases you may also be able to figure out what socket file location is used by listing the processes: ps -ef | grep mysql which could output something like

root     23900     1  0 13:27 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/vserv-03.pid
mysql    24402 23900  0 13:27 ?        00:00:50 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep_on=ON --wsrep_provider=/usr/lib64/galera/libgalera_smm.so --log-error=/var/lib/mysql/vserv-03.err --open-files-limit=65535 --pid-file=/var/lib/mysql/vserv-03.pid --socket=/var/lib/mysql/mysql.sock --wsrep_start_position=98b22222-aa16-1114-ba1a-886633c6b8eb:55133333

As you can see here (scroll right), it's using a socket at /var/lib/mysql/mysql.sock.

EDIT:
A really good way of finding the location of the socket file is:

netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     22418    /var/lib/mysql/mysql.sock


Deadlock: How to prevent Primary Key Lock in SELECT In MySQL 5.7 we are running tracking queries every day at 0:00.

Lately, these are causing deadlocks with live calls that happen simultaneously which causes issues in our live service. (Or maybe I just started to notice this now..)

Tracking Call:

INSERT INTO track_daily (date, dau, mau, chips, subscriptions, trueskill,
                         results_canceled, origin)
SELECT DATE_SUB('17-05-31', INTERVAL 1 DAY), count(*), 14, 315, 1,
       if(count(*) = 0, 0, avg(mean - 3 * stddev)), 0.0299, 'facebook'
FROM   user
WHERE  lastLogin >= DATE_SUB('17-05-31', INTERVAL 1 DAY)
AND    user.origin = 'facebook'

Lock:

  RECORD LOCKS space id 1084 page no 6776 n bits 176 index PRIMARY of table data.user trx id 6727675275 lock mode S

Service Call:

UPDATE user
SET    chips = chips + -1, chipsWon = chipsWon + 0
WHERE  uid = '1448963' AND chips >= --1

Lock:

  RECORD LOCKS space id 1084 page no 6776 n bits 176 index PRIMARY of table data.user trx id 6727675426 lock_mode X locks rec but not gap waiting

I understand this is happening because both queries are locking the PRIMARY key of the user table, but I do not understand why the tracking call requires this lock. Can someone give me a tip how to optimize the tracking call so it does not lock the PRIMARY anymore?

Edit: The result from SHOW CREATE TABLE:

CREATE TABLE `user` (
    `uid` int(10) unsigned NOT NULL,
    `mean` double NOT NULL DEFAULT ''25'',
    `stddev` double NOT NULL DEFAULT ''8.33333333333333'',
    `trust` double NOT NULL DEFAULT ''1'',
    `roundsPlayed` int(10) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedDaily` smallint(5) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedLeague` int(10) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedChips` int(10) unsigned NOT NULL DEFAULT ''0'',
    `dailyScore` int(11) NOT NULL DEFAULT ''0'',
    `leagueScore` int(11) NOT NULL DEFAULT ''0'',
    `alltimeScore` int(11) NOT NULL DEFAULT ''0'',
    `displayName` varchar(255) NOT NULL DEFAULT ''Neuling'',
    `imageUrl` varchar(255) NOT NULL,
    `chips` mediumint(8) unsigned NOT NULL DEFAULT ''17'',
    `chipsWon` mediumint(8) NOT NULL DEFAULT ''0'',
    `lastLogin` timestamp NULL DEFAULT NULL,
    `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `origin` varchar(20) NOT NULL,
    `banned` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
    PRIMARY KEY (`uid`),
    KEY `leagueScore` (`leagueScore`),
    KEY `dailyScore` (`dailyScore`),
    KEY `chipsWon` (`chipsWon`),
    KEY `user_roundsPlayedLeague_idx` (`roundsPlayedLeague`),
    KEY `user_roundsPlayedChips_idx` (`roundsPlayedChips`),
    KEY `user_roundsPlayed_idx` (`roundsPlayed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Making queries faster is a way to minimize deadlocks, if not avoid them.

INDEX(origin, lastLogin)

should help the first query.

The UPDATE should be helped by

INDEX(uid, chips)

Please provide SHOW CREATE TABLE if you wish to discuss thing further.

How many cron jobs run at midnight?  If more than one, stagger them.  Otherwise the buffalo will stumble over each other.

If you are collecting "yesterday's" data, you should include a stopper -- else it could include a few records from "today":

WHERE lastLogin >= '2017-05-31' - INTERVAL 1 DAY
  AND lastLogin  < '2017-05-31'

With that, it won't matter if you schedule it a few minutes after midnight.

I cringed at --1 and + -1, but I guess they work OK.


Why are my domain and username being prepended to stored procedures I create? In Microsoft SQL Server Management Studio, every time I write a stored procedure such as ThisIsAStoredProcedure, it ends up showing in the list of stored procedures as COMPANYDOMAIN\MYUSERNAME.ThisIsAStoredProcedure.

I'd like it to be dbo.ThisIsAStoredProcedure instead.

I've tried right clicking and renaming, but this doesn't work.

I have three concerns:

Why is this happening?
How can I change the existing stored procedures with my domain\username prefix to dbo.procedurename?
How can I change the configuration to name procedures dbo.procedurename by default?

If you didn't explicitly set it, then objects created by you will be created under the default schema for your user. In SSMS, look in:
Database -> Security -> Users -> <you> -> Properties -> General -> Default schema
ALTER SCHEMA dbo TRANSFER [COMPANYDOMAIN\MYUSERNAME].ThisIsAStoredProcedure;
Change the setting you looked at in 1.

Of course, you may not have permission to do 2 or 3, in which case it's a question for the person that does have permission.


Msg 142, Level 15, State 2 -- Can't figure out what I have wrong I've looked at other threads for the error I'm receiving, but I don't see where I'm making the mistake and don't see any extra commas, which seems to be the main cause of issues. I'd be very appreciative if someone could point out where I'm making my error.

CREATE TABLE Accounts.AdminUsers ( admID             INT              NOT NULL    IDENTITY(1,1)
                          , admFN            VARCHAR(35)      NULL
                          , admLN            VARCHAR(35)      NULL
                          , admUserName  VARCHAR(64)      NOT NULL
                          , admPass      VARCHAR(128)     NOT NULL
                          , admPassIV        VARCHAR(32)      NOT NULL
                          , admPassResetDate DATETIME2    NULL
                          , admEEID      VARCHAR(30)      NULL
                          , admEmail         VARCHAR(255)     NULL
                          , admAcctEnabled   BIT              NOT NULL
                          , admHier      HIERARCHYID      NOT NULL
                          , admNumLogins     SMALLINT         NULL
                          , admLastLogin     DATETIME2    NULL
                          , admCreateTS  DATETIME2    NOT NULL
                          , admTID       TINYINT          NOT NULL
                          , CONSTRAINT PK_Accounts_AdminUsers
                            PRIMARY KEY CLUSTERED (admID)
                          , CONSTRAINT DF_Accounts_AdminUsers_admAcctEnabled
                            DEFAULT (1)
                          , CONSTRAINT DF_Accounts_AdminUsers_admCreateTS
                            DEFAULT (CURRENT_TIMESTAMP)
                          , CONSTRAINT FK_Accounts_AdminUsers_AdminUserTypes
                            FOREIGN KEY (admTID) REFERENCES Accounts.AdminUserTypes(admTID)
                          );

Move the CONSTRAINT DEFAULT next to the field definition:

CREATE TABLE Accounts.AdminUsers ( admID             INT              NOT NULL    IDENTITY(1,1)
                          , admFN            VARCHAR(35)      NULL
                          , admLN            VARCHAR(35)      NULL
                          , admUserName  VARCHAR(64)      NOT NULL
                          , admPass      VARCHAR(128)     NOT NULL
                          , admPassIV        VARCHAR(32)      NOT NULL
                          , admPassResetDate DATETIME2    NULL
                          , admEEID      VARCHAR(30)      NULL
                          , admEmail         VARCHAR(255)     NULL
                          , admAcctEnabled   BIT              NOT NULL CONSTRAINT DF_Accounts_AdminUsers_admAcctEnabled
                            DEFAULT (1)
                          , admHier      HIERARCHYID      NOT NULL
                          , admNumLogins     SMALLINT         NULL
                          , admLastLogin     DATETIME2    NULL
                          , admCreateTS  DATETIME2    NOT NULL CONSTRAINT DF_Accounts_AdminUsers_admCreateTS
                            DEFAULT (CURRENT_TIMESTAMP)
                          , admTID       TINYINT          NOT NULL
                          , CONSTRAINT PK_Accounts_AdminUsers
                            PRIMARY KEY CLUSTERED (admID)
                          );


Backup Specific days monthly SQL Server How to backup only specific days each month on SQL Server (I use both SQL Server 2005 and 2008) using a maintenance plan or Agent job. I mean by that to backup my databases from 28th of each month to 08th of the next month and so on?

This will be tough to do with the built-in schedule UI, unless you want to create 12 different schedules, but you can easily build this into your job step. Assuming it runs every night:

IF DAY(GETDATE()) <= 8 OR DAY(GETDATE()) >= 28
BEGIN
  -- do backups
END

On other days (9th -> 27th), the job will still run, but everything inside the IF conditional will not. So other than having an entry in sysjobhistory, it will be as if it didn't run at all.

Another idea would be to have a different job that runs every morning, checks the date in a similar way, then enables / disables the backup job accordingly. Or similarly with two schedules (one on the 9th to disable, and one on the 28th to enable).


How to aggregate timeseries several months long - when I only want a portion of each month I have a need to see a trend of data for "month to date"  So I want to pull the 1st x number of days of each month for the last 14 months, and the n aggregate this.

My data has approximately 10k data points each day

So far I have only been able to figure out how to do this by writing a double while loop - the outer loop counting down the months and the inner loop selecting each day and aggregating the data for the day - then storing it in a temp table.

Once I have stepped through each month and each day I then selet the data from the temp table and aggregate this to give me monthly data summed for each contract type.

For various reasons I need to run my select 4 times for each day.

This all ends up meaning my SQL takes around 120 secs to run.  This is sub-optimal - as I am hoping to have this used by SSRS to pull a report on demand when a user wants to see it. Making them wait 2 whole minutes?  Not Desirable - especially considering primary target audience is the exec team

Here is my SQL (NB I've changed a couple of table/ column names )

Declare
    @ReportDate as DATE
    ,@month as DATE
    ,@dayCounter as INT
    ,@monthCounter as INT
    ,@reportDateCurrentMonth as DATE

SET @ReportDate = sysdatetime()
SET @month = @ReportDate
SET @dayCounter = DATEPART(DAY,@ReportDate)
SET @monthCounter = '0'
SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)

CREATE TABLE #DailyVolumes
    (
       contract_name varchar(50)
       ,Volume INT
       ,date_registered DATETIME
    )

WHILE @month > DATEADD(month,-15,@ReportDate)
    BEGIN

          WHILE @dayCounter > '0'

          BEGIN

                INSERT INTO #DailyVolumes
                SELECT
                CONCAT('PREFIX-',DWRccn.contract_name) AS contract_name
                    ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
                    ,a.date_registered
                FROM accession a
                    Left  join value1 P on a.value1_id = p.value1_id
                    left  join [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn on a.contract_code = DWRccn.contract_code
                where
                    [date_registered] = @reportDateCurrentMonth
                    AND a.lis_code = 'S'
                    AND visit_type IN ('I', 'E')
                GROUP BY
                    CONCAT('PREFIX-',DWRccn.contract_name)
                    ,a.date_registered

                -- Set new values on Daily counter & Date to grab
                SET @dayCounter = @dayCounter - '1'
                SET @reportDateCurrentMonth = DATEADD(day,-1,@reportDateCurrentMonth)
          END;

       SET @monthCounter = @monthCounter - '1'
       SET @month = DATEADD(month,-1,@month)
       SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)
       SET @dayCounter = DATEPART(DAY,@ReportDate)

    END;

-- SUM Daily Data into Monthly Slices
Select
    sum(Volume) AS Volume
    ,contract_name
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) AS MonthRegistered
FROM #DailyVolumes
group by
    contract_name
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0)
ORDER BY
    MonthRegistered DESC
    ,contract_name
-- Clear Temp Table
DROP TABLE #DailyVolumes

I am hoping someone can tell me how to accomlish what I am after...  Which is to do away with the loops and have the data aggregate in a single operation

NB I have done my aggregation for full month periods - that was a piece of cake compared to this

NOTE: I'm assuming you're running SQL Server 2012 (or higher), otherwise the datefromparts() code will need to be modified.

I'd opt for getting rid of the loops in favor of a single query that includes a date generator, which is relatively easy to do with a common table expression (CTE), eg:

declare @days   tinyint,         -- first X days of a month to query
        @months smallint,        -- go back Y months for our query
        @today  date

select  @days   = 5              -- only interested in days 1-5 of each month
        @months = 4              -- only interested in this month plus the previous 4 months
        @today  = sysdatetime();

with
dategen as
(select -- piece our years/months/days together into dates
        datefromparts ( year(dateadd(m,-m.number,@today)),  -- get year  for @today minus m.number of months
                       month(dateadd(m,-m.number,@today)),  -- get month for @today minus m.number of months
                       d.number) as search_date             -- day of month

 from   master..spt_values m,
        master..spt_values d

 -- no join clause => cartesian product (or you could add a cross join, your call)
 where  m.type = 'P'
 and    m.number between 0 and @months
 and    d.type = 'P'
 and    d.number between 1 and @days)

select  search_date
from    dategen
where   search_date <= @today
order by 1

 | search_date         |  @today = 18 November 2017
 | ------------------- |
 | 01/07/2017 00:00:00 |  first 5 days of July 2017
 | 02/07/2017 00:00:00 |
 | 03/07/2017 00:00:00 |
 | 04/07/2017 00:00:00 |
 | 05/07/2017 00:00:00 |

 | 01/08/2017 00:00:00 |  first 5 days of August 2017
 | 02/08/2017 00:00:00 |
 | 03/08/2017 00:00:00 |
 | 04/08/2017 00:00:00 |
 | 05/08/2017 00:00:00 |

 | 01/09/2017 00:00:00 |  first 5 days of September 2017
 | 02/09/2017 00:00:00 |
 | 03/09/2017 00:00:00 |
 | 04/09/2017 00:00:00 |
 | 05/09/2017 00:00:00 |

 | 01/10/2017 00:00:00 |  first 5 days of October 2017
 | 02/10/2017 00:00:00 |
 | 03/10/2017 00:00:00 |
 | 04/10/2017 00:00:00 |
 | 05/10/2017 00:00:00 |

 | 01/11/2017 00:00:00 |  first 5 days of November 2017
 | 02/11/2017 00:00:00 |
 | 03/11/2017 00:00:00 |
 | 04/11/2017 00:00:00 |
 | 05/11/2017 00:00:00 |

Here's a dbfiddle for the (above) CTE example.

Plugging the CTE into your insert/select would generate something like:

-- declare/select @variables;

with
dategen as
(select datefromparts( year(dateadd(m,-m.number,@today)),
                      month(dateadd(m,-m.number,@today)),
                      d.number) as search_date

 from   master..spt_values m,
        master..spt_values d

 where  m.type = 'P'
 and    m.number between 0 and @months
 and    d.type = 'P'
 and    d.number between 1 and @days)

INSERT INTO #DailyVolumes
SELECT CONCAT('PREFIX-',DWRccn.contract_name)                              AS contract_name
      ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
      ,a.date_registered

FROM   accession a

join   dategen dg
on     a.date_registered = dg.search_date

Left
join   value1 P
on     a.value1_id = p.value1_id

left
join   [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn
on     a.contract_code = DWRccn.contract_code

where
AND    a.lis_code = 'S'
AND    visit_type IN ('I', 'E')
AND    dg.search_date <= @today

GROUP BY CONCAT('PREFIX-',DWRccn.contract_name)
        ,a.date_registered

NOTES:

rename local variables as you see fit
set the @months/@days values to your desired limits
I haven't tested the insert/select (don't have the DDL for your system) so there may be some minor syntax issues ... ??
if performance is still not quite up to what you're expecting then it'll be necessary to review the query plan (and potentially the available indexes)

Assuming the (above) CTE/insert/select query generates the desired results, the next step would be to eliminate #DailyVolumes and add the final select/sum(Volume) code to the above query ... easiest to understand would probably be something like:

with

dategen as
(select ...),

dailyvolumes as
(select ... from accession/dategen/value1/DWRcnn ...)

select sum(Volume) ...
from   dailyvolumes ...


Difficult categorising of rows First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. The table represents completed tasks, the columns are:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook
Requester
Approver
Cost
...

Reference is used when the customer provides their own ID for the task, typically this is for example "THR#000123123" or "IX#01212". It is always in the format "ABC#123".

Now, I need to categorise these into multiple arbitrary categories and subcategories based on only the type and reference fields:

Chargeable (type = 3)

By company IX (reference like "IX#%")
All other Chargeable

Non-chargeable (type <> 3)

By company THR (reference like "THR#%")

Return-to-base (type = 2)
On-site (type = 1)

Return to base (type = 2)
All other non-Chargeable

A task can only belong to one category, so if a task for company THR was "return-to-base", it must only be under category 2.1.1, not 2.2. I realise these categories seem very arbitrary but this structure represents the most useful break-down of almost any selection of tasks from the table.

My question is, is it possible to create a view that contains the TaskID and its category? Or should I just implement this logic in the front-end after selecting every TaskID, Type and Reference?

Ok, so my answer here is conditioned on the following assumptions:

This categorization needs to be relatively stable and
It may be re-used in various ways, and
It needs to perform well.

The obvious, simple answer is to use table methods in PostgreSQL.  What you do is create a SQL language function which does not hit the table and returns the value you want.  Make it immutable so you can index the output if you want to query against it, etc.  Note the specific rules aren't really clear from your post but this should get you started.

CREATE OR REPLACE FUNCTION subcategory(task) -- task is table name
RETURNS text LANGUAGE sql IMMUTABLE AS
$$
    SELECT CASE WHEN $1.type = 3 THEN
                CASE WHEN $I.reference LIKE 'IX#%'
                     THEN 'Company IX Chargeable'
                     ELSE 'Other Chargeable'
                END
                WHEN $1.reference LIKE 'IX#%' THEN
                CASE WHEN $1.type = 5 THEN 'Company IX Rebool'
                     ELSE 'Company IX Other'
                END
                WHEN $1.reference LIKE 'THR#%' AND $1.type IN (1, 2) THEN
                CASE WHEN $1.type = 2 THEN 'THR Return to Base'
                     WHEN $1.type = 1 THEN 'THR On Site'
                END
                WHEN $1.type = 2 THEN 'Return to Base'
                ELSE 'Other Nonchargeable'
           END;
$$;

You can then query this using:

SELECT t.subcategory FROM task t;

Note that the table name is non-optional here.  The parser converts this to:

SELECT subcategory(t) FROM task t;

You can use this however in any part of the select statement including the where clause.  If it proves slow, you can add cost estimates (but I think this should be fast), and you can even index the output using PostgreSQL's functional indexes.

On the performance side, LIKE and substring() don't seem to be significantly different:

postgres=# select count(*) from generate_series(1, 10000000);
  count
----------
 10000000
(1 row)

Time: 9007.618 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count
---------
 1111112
(1 row)

Time: 13653.000 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count
---------
 1111112
(1 row)

Time: 16681.860 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count
---------
 1111112
(1 row)

Time: 17163.470 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count
---------
 1111112
(1 row)

Time: 17052.004 ms

So sometimes one is faster than the other, but they don't seem out of line with eachother.


Advice on deleting all rows and reclaiming space of very large table I'm not a dba...let's start with that :) I'm an app developer. But our database has an archive table which is 252 million rows and 170GB, and I've been tasked to fix it (actually, there are multiple tables in this category that I need to clean up). But we've decided we do not need to keep any of this data. As this table is an archive table, by which I mean there is a primary transaction table for this data, but the application has a way to keep the transaction table smaller by archiving, or moving, older records to this archive table.

Being an archive table, it has no dependencies per SSMS (no indexes or foreign keys). This db is set to full recovery mode.

We have short maintenance windows, so need a process that is fast, and we have limited disk space, so we need to reclaim the disk space from this table.

We can truncate the table, but then how to reclaim the space? We don't want to do a db shrink, as it shrinks all tables and hence then we'd need to run index rebuilds...at least that is my understanding, which could be incorrect.

I could save a script from SSMS (Script Table as Create To...). Then drop the table, then recreate it... Is this risky? Will it reclaim the space?

Other ideas?

Because you have limited experience with database administration, be extra careful. You have a test copy of your database server set up, right?

You mentioned the database uses full recovery model. This is VERY important, because if the transaction logs have not been getting backed up often enough, there is no way to shrink them. You'd have to put the database in simple recovery mode (which for all intents and purposes gets rid of the transaction log) and wait while that processes, then you could move forward. Given the amount of data, I'd imagine it would take a while. For information on the recovery models, go here:  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server This article gives a basic overview.

To answer your question, truncating or dropping the archive table will not free up space - that's what shrinking the database does. All the truncate/drop does is mark the appropriate pages in the database as unused. There's little sense in dropping the table and re-creating it. A truncate basically gets the same end result and is less prone to error.

Keep in mind, if this application sends more data to this archive table, the database will expand after you've run the shrink procedure! (Are you really using SQL Server 2005? If so, seriously consider upgrading to a supported version, and getting newer hardware if it's a physical server.) So, just like the application moves data to the archive table, you should consider implementing a way to remove unneeded data from the archive table to avoid this situation in the future.  But first things first.

If you haven't already, read this: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql It's a Microsoft doc that describes shrinking a database in some good detail. This is a decent overview, and contains some best practices as well as caveats.

If you don't want to shrink the database, your other option is to create a new database, transfer to it what you want to keep from the old database, then drop the old database. Of course, this will take up quite a bit of disk space and time. It will also put the server under considerable load.

You are correct in that a database shrink can increase index fragmentation. Having said that, such fragmentation may not have a noticeable effect on performance. That's why you have a test server. :-)

You may have to adjust your maintenance window for this operation. It will take as long as it takes. While the truncate is in progress, the database will be online. You can keep the database online during a shrink as well. Performance may suffer noticeably depending on usage, how queries are tuned, hardware, and operating system configuration. Again, test before doing anything in production! A shrink can be stopped and started at a later time, however, if space is at a premium starting and stopping defeats the purpose.

One more thing: depending on how fast data is added to this database, truncating and shrinking may only delay the inevitable. Consider adding storage capacity.

If you have further details or clarification, I'll update my answer accordingly. In the meantime, best of luck, and let us know how things go!


Programatically scan a stored procedures parameters I have been trying to figure out how to scan a stored procs parameters from the execution plan. I often see most people suggest using the ParameterList within the execution plan xml. This works fine when there is just one statement that uses all of the parameters. However this is often not the case. The stored procedure parameters could be distributed over several queries or not used in a query at all.

In my examples below, I would like to know how to get the distinct parameters and their values from the example using sql xml functions. Also, you will notice that not all of the parameters are output into the ParameterLists.

/***********************************************************************/
/*EXAMPLE: you have to scan all of the selects parameter lists to match up the parameter names to the proc parameters */
/***********************************************************************/
IF OBJECT_ID (N'dbo.TestFooProc') IS NULL
   EXEC('CREATE PROCEDURE dbo.TestFooProc AS SET NOCOUNT ON;');
GO

ALTER PROCEDURE dbo.TestFooProc (
    @p1 int,
    @p2 int,
    @p3 int = 8675309
) AS
BEGIN
    --distribute the parameters over multiple selects
    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p2;

    -- other simulated queries ...

    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p1;

END
GO

EXEC dbo.TestFooProc @p1 = 5, @p2 = 22

SELECT [db_name] = DB_NAME([eps].[database_id]), qt.[text], qp.[query_plan]
FROM  sys.[dm_exec_procedure_stats] eps
CROSS APPLY sys.[dm_exec_sql_text](eps.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) AS qp
WHERE [eps].[database_id] = DB_ID()
    AND qt.[text] LIKE '%dbo.TestFooProc%'

RETURN  -- CLEANUP

IF OBJECT_ID (N'dbo.TestFooProc') IS NOT NULL
   DROP PROCEDURE dbo.TestFooProc;
GO

Ok, after much digging into this I have determined this is not often possible. I will detail the following scenarios that break down the ability to get the original parameters from the execution plan.

If the parameter is not used in a where clause. Scenarios:

The parameter is placed into a local variable as a way to work around parameter sniffing
The parameter is used in a calculation in a local variable

If the parameter is used in a where clause but a function is used upon it
If the parameter is used in multiple sql statements in the stored proc it will appear in multiple parameter lists.

I just wanted a list of all of the original parameters passed into the stored proc and their values from the original plan creation so that I could recreate the call if need be. I know that I could obtain this information using extended events or profiler but I was trying to extract it from the query plans.


How to analyze impact of a query on another queries? Suppose I have two tables let's call them values and candlesticks. I can compute candlesticks over values using three different methods:

In trigger before inserting new value with insert on duplicate key update
Periodically issue insert into candlesticks ... select from values.
Using some service written in some language that would periodically poll values, computing updating or adding new candlesticks.

I'd like to analyze how this methods would impact queries to both values and candlesticks. The best thing I came up so far is to write a script that would put new values and another one which would issue two typical queries (one for candlesticks and one for values) and use performance_schema as it described in manual to gather data about queries and plot it.

Which seems like a problem that should be solved already by some tool. Is there a such tool (free) that can monitor some query performance over time? Maybe there are some other approaches to the problem?

Is there a such tool that can monitor some query performance over
  time?

There are several solutions such as Spotlight, SolorWinds, SQL Monitor (Red Gate).


SQL Max() revision number This SQL returns a table with everything unique, which is what I want.  However, if there are two (or more) event numbers, I need it to return the line with the highest rev_num for that eventnum.

I have read about the max() function, but I'm not having any success with it.

select distinct a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, rev_num
from mv_outage_duration a
    inner join mv_outhist_transformers b
        on a.restore_operation_id = b.phase_restored_operation_id
    inner join mv_aeven d
        on d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    inner join mv_we_outage_premise e
        on e.xfmr = b.xfmr_name
where e.we_premise = '995184'

Here's what the output looks like:

FINAL ANSWER:

First off, both answers helped immensely. The final query looks like this:

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, rev_num
from mv_outage_duration a
    inner join mv_outhist_transformers b
        on a.restore_operation_id = b.phase_restored_operation_id
    inner join mv_aeven d
        on d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    inner join mv_we_outage_premise e
        on e.xfmr = b.xfmr_name
where e.we_premise = '995184'
  and rev_num = (select max(rev_num)
                   from mv_aeven d
                  where d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID)

Max did a great job of explaining why I should restructure my SQL in a way that uses the inner joins. Marty was correct in that I needed a subquery on the where clause, just needed to point to the right columns for the data I needed.  This could have been my fault as I'm not sure I explained the data structure well enough.

Thank you both for your time and efforts!

Have you tried something like this?  Since I don't have your actual tables (nor do I have time to fake them up), I cannot test this.  However, this might help point you in the correct direction.  Failing that, you may want to look at common-table-expressions, or CTE's.

SELECT a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, MAX(rev_num) AS MaxRevNum
FROM mv_outage_duration a
    INNER JOIN mv_outhist_transformers b
        ON a.restore_operation_id = b.phase_restored_operation_id
    INNER JOIN mv_aeven d
        ON d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    INNER JOIN mv_we_outage_premise e
        ON e.xfmr = b.xfmr_name
WHERE e.we_premise = '995184'
GROUP BY a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code

EDIT;  perhaps this will help

SELECT a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, MAX(rev_num) AS MaxRevNum
FROM mv_outage_duration a
    INNER JOIN mv_outhist_transformers b
        ON a.restore_operation_id = b.phase_restored_operation_id
    INNER JOIN mv_aeven d
        ON d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    INNER JOIN mv_we_outage_premise e
        ON e.xfmr = b.xfmr_name
WHERE e.we_premise = '995184'
    AND d.weather_code IN (SELECT d.weather_code FROM mv_aeven WHERE mv_aeven.rev_num = MaxRevNum)
GROUP BY a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code;


AlwaysON - Force users connection to use Readable Secondary Nodes I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Processes like Reporting, ETL (extraction of data), and read workload are being redirected to the secondary replica using the Listener and the "initial catalog" and "applicationintent=readonly" parameters.

The problem is: My client wants to force all non-admin users to connect always to the secondary-readable replica. BUT! make this automatically. The client does not want that the users have to add parameters to the connection in SQL Server Management Studio.

I'm testing logon trigger with no results. I can't find a way out to this.

Is this possible?

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5.

There is nothing in 2016 to change this behavior AFAIK.


Check if database backup using pg_dump is in progress I am using PostgreSQL 9.2 and using pg_dump to create DB backup. How could I determine is db backup and restore is in progress. So I can prevent another user in initiating another.

The only thing I can think of is to check pg_stat_activity.application_name and see if any session with pg_dump (or pg_restore) is currently active.

application_name is a value supplied by the client connecting to the database. So in theory this is not something that can be trusted completely - but I am not aware that pg_dump allows you to overwrite this. And if you don't use that information for security purposes this should be OK.


Simple SSIS Package Failed User does not exist or has not permission I created and build SSIS Package with "Transfer SQL Server Objects
Task".
I Copied the package.dtsx to network folder with permissions    set
to everyone.
I created the SSIS Job where i set path to that   package.
I am owner of source and destination database too.
Package run as 32b.

But Job is failing. What can be wrong ? Why it is searching my credentials COMPANY\MYUSERNAME when the job is executed as COMPANY\SERVICEJOBACCOUNT ? (which also has access to the databases). Thank you

Error is following

Execution failed with the following error:
"ERROR : errorCode=0 description=Cannot find the user 'COMPANY\MYUSERNAME',
because it does not exist or you do not have permission.

The solution was that job needed to has an access to the default folder (on the server)

C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo

My colleage solve it and according to him it is some kind of bug which is patched in newer version of SQL Server.


Cannot rename the table because it is published for replication I need to rename some tables in my DB - SQL Server 2005, with this:

EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT'

but I can't do this - I get this exception -

  Cannot rename the table because it is published for replication

This DB doesn't have replication - it was removed 5 years ago - we've just tried it and removed it after a week. So I suppose that it should be a flag somewhere.

Please help me remove this flag somehow.

sp_removedbreplication 'DB_NAME' that will remove replication from the database.
From How to cleanup Replication Bits by Chris Skorlinski of Microsoft SQL Server Escalation Services:

sp_removedbreplication – Removes all replication objects from a database without updating data at the Distributor. This stored procedure is executed at the Publisher on the publication database or at the Subscriber, on the subscription database.
sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.


Materialized view with FAST refresh on remote table: How to include a GEOMETRY column? I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column.
Options for the GEOMETRY column datatype include:

ESRI's proprietary implementation of ST_GEOMETRY (user-defined datatype; is an 'object' datatype)
Oracle's SDO_GEOMETRY datatype

To start, I can successfully create a fast refresh MV without a GEOMETRY column:
create materialized view log on maximo.workorder with primary key;  --remote table
grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema

create materialized view my_gis_schema.wo_mv
build immediate
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    maximo.workorder@my_dblink

The MV above works, but I want to store the XY coordinates from the remote table in a GEOMETRY column in the MV (right now, the coordinates are stored in number columns, not a geometry column).
Unfortunately,  my options for the GEOMETRY column in an MV seem pretty limited:

Oracle doesn’t seem to support ESRI's ST_GEOMETRY datatype in MVs (more info here and here).

The SQL would be: sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape

Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs with the fast refresh option on a remote table: ORA-12015: cannot create a fast refresh materialized view from a complex query

The SQL would be: sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape

Question:
Is there a way to include a GEOMETRY column in a materialized view on a remote table, using the fast refresh option?

Regarding the SDO_GEOMETRY/fast refresh/remote table scenario:
The issue doesn't seem to happen when the MV selects from a local table. The issue only seems to happen when we select from a remote table/using a dblink. It seems to be normal Oracle behavior.

Using remote table/dblink (fails):
The steps were adapted from the answer in this post: Create fast-refresh MV over dblink on table without PK?
SQL> create table t5 (c1 number not null unique, c2 number);

Table created.
SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
  2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
  3  from t5@self;
select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape

             *
ERROR at line 2:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Using a local table (succeeds):
SQL> create table t5 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
  2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
  3  from t5;

Materialized view created.

SQL> insert into t5 values (1, 1);

1 row created.

SQL> insert into t5 values (2, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV5', 'f');

PL/SQL procedure successfully completed.

SQL> select refresh_method from user_mviews where mview_name = 'MV5';

REFRESH_METHOD
--------
FAST

Of course, that doesn't answer your question about how to get a GEOMETRY column working.
But I think that test will at least be help demonstrate that the combination of 1) SDO_GEOMETRY, 2) fast refresh, and 3) using a remote table is what's causing the issue. If we remove any of those three things, then the MV seems to work fine.