How to best store the Google Web Ngram data? This is a continuation of How to best store Google ngrams in a database?, which covers how to store the Google Ngram Book data.

I'm looking to store the Google NGram Web data, which is slightly different in format (no page/year info; just counts):

...
ceramics collectables collectibles 55
ceramics collectables fine 130
...
serve as the incoming 92
serve as the incubator 99

Since this is a very simple data structure, what is a good method for storing this data that is reasonably quick to import, and quick to retrieve the count by a particular ngram?

I like the idea of a relational db, simply because of the common methods for accessing it, but I'm guessing most of these other non-relation db's (e.g. tokyo hashtable) have pretty common methods as well.

Update

Examples of a queries:

# primary query
> SELECT ngram_count FROM ngram_table WHERE ngram = 'ceramics collectables fine';

ceramics collectables collectibles 55
ceramics collectables fine 130

# secondary query (not needed, but nice if have option)
SELECT ngram_count FROM ngram_table WHERE ngram LIKE '%collectables%';

ceramics collectables collectibles 55

I have the script you need right here

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID BIGINT UNSIGNED NOT NULL,
    NGRAM_COUNT SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    NGRAM_COUNT SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;
    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.NGRAM_COUNT);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',85),
('pamela',86),
('dominique',87),
('diamond',88),
('rolando edwards',185),
('pamela edwards',186),
('dominique edwards',187),
('diamond edwards',188),
('rolando angel edwards',285),
('pamela claricia edwards',286),
('dominique sharlisee edwards',287),
('diamond ashley edwards',288);
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.* FROM
ngram_key A,ngram_rec B
WHERE A.ngram IN ('rolando angel edwards','rolando edwards','rolando')
AND A.ngram_id=B.ngram_id;

here is what the sample data generates:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS ngram_key;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS ngram_rec;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS ngram_blk;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE ngram_key
    -> (
    ->     NGRAM_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     NGRAM VARCHAR(64) NOT NULL,
    ->     PRIMARY KEY (NGRAM),
    ->     KEY (NGRAM_ID)
    -> ) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
Query OK, 0 rows affected (0.53 sec)

mysql> CREATE TABLE ngram_rec
    -> (
    ->     NGRAM_ID BIGINT UNSIGNED NOT NULL,
    ->     NGRAM_COUNT SMALLINT NOT NULL,
    ->     PRIMARY KEY (NGRAM_ID)
    -> ) ENGINE=MyISAM ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE ngram_blk
    -> (
    ->     NGRAM VARCHAR(64) NOT NULL,
    ->     NGRAM_COUNT SMALLINT NOT NULL
    -> ) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
    -> BEGIN
    ->     DECLARE NEW_ID BIGINT;
    ->     INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    ->     SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    ->     INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.NGRAM_COUNT);
    -> END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> INSERT INTO ngram_blk VALUES
    -> ('rolando',85),
    -> ('pamela',86),
    -> ('dominique',87),
    -> ('diamond',88),
    -> ('rolando edwards',185),
    -> ('pamela edwards',186),
    -> ('dominique edwards',187),
    -> ('diamond edwards',188),
    -> ('rolando angel edwards',285),
    -> ('pamela claricia edwards',286),
    -> ('dominique sharlisee edwards',287),
    -> ('diamond ashley edwards',288);
Query OK, 12 rows affected (0.10 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ngram_key;
+----------+-----------------------------+
| NGRAM_ID | NGRAM                       |
+----------+-----------------------------+
|       11 | dominique sharlisee edwards |
|        1 | rolando                     |
|        9 | rolando angel edwards       |
|        4 | diamond                     |
|        8 | diamond edwards             |
|        2 | pamela                      |
|        3 | dominique                   |
|        6 | pamela edwards              |
|        5 | rolando edwards             |
|       12 | diamond ashley edwards      |
|        7 | dominique edwards           |
|       10 | pamela claricia edwards     |
+----------+-----------------------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM ngram_rec;
+----------+-------------+
| NGRAM_ID | NGRAM_COUNT |
+----------+-------------+
|        1 |          85 |
|        2 |          86 |
|        3 |          87 |
|        4 |          88 |
|        5 |         185 |
|        6 |         186 |
|        7 |         187 |
|        8 |         188 |
|        9 |         285 |
|       10 |         286 |
|       11 |         287 |
|       12 |         288 |
+----------+-------------+
12 rows in set (0.00 sec)

mysql> SELECT A.ngram NGram,B.* FROM
    -> ngram_key A,ngram_rec B
    -> WHERE A.ngram IN ('rolando angel edwards','rolando edwards','rolando')
    -> AND A.ngram_id=B.ngram_id;
+-----------------------+----------+-------------+
| NGram                 | NGRAM_ID | NGRAM_COUNT |
+-----------------------+----------+-------------+
| rolando               |        1 |          85 |
| rolando angel edwards |        9 |         285 |
| rolando edwards       |        5 |         185 |
+-----------------------+----------+-------------+
3 rows in set (0.00 sec)

Give it a Try !!!


Guidance for using composite keys to identify rows Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting to build a table that (with 4 keys linked) would describe a unique entry in the table. I'm curious if this is a good plan of attack or if there is a better way.

For visual purposes, picture the following table. We have inventory items that are organized like the following table: ([K] is symbolic of the primary key, the lines are relationships)

    Sheet_Class        Sheet_Type         Sheet_Size
    ===========        ==========         ==========
[K] Sheet_Class-.  [K] Sheet_Type--.  [K] Sheet_Size
                 '---- Sheet_Class  '---- Sheet_Type
                                          Length
                                          Width
                                          Thickness

The data may present itself in the following way, but for brevity I've excluded bringing over the linked columns:

 Sheet_Class    Sheet_Type    Sheet_Size                        (Tables)
[Sheet_Class]  [Sheet_Type]  [Length], [Width], [Thickness]     (Column Values)
=============  ============  ==============================

Aluminum
               5052-H32
                             48, 96, 0.032
                             48, 96, 0.040
                             48, 96, 0.063

               6061-T6
                             60, 120,0.032
                             60, 120,0.040
                             60, 120,0.063

Steel
               1018-CRS
                             48, 96, 0.018
                             48, 96, 0.023
                             48, 96, 0.031

As it stands (and I've shown in my "schema" above), I use a simple (auto-increment) integer primary key for entries in the Sheet_Size table. However, I'd like to know if it's better to use a combination of the Sheet_Type, Length, Width, & Thickness columns instead? Given each entry in Sheet_Size should share all these unique qualities, and that an auto-incrementing field wouldn't demonstrate this well enough, is this the best route to take?

If I'm not explaining the situation well enough, please let me know. I'm finding myself needing to break out these portions (Class vs. Type vs. Actual stock sizes) of an inventoried material for other logic purposes, but I'm up for any other kind of feedback.

Any guidance would be appreciated.

Update (08-12-2011)

After the answers posted, I've decided to do a combination of Mark's answer & X-Zero's answer. I decided it's a good idea to place a unique constraint on the length, width and thickness columns, but I also like the idea of breaking out material sizes in to unique rows and linking them with a relationship.

Unfortunately I can not accept both answers, so I am going to accept X-Zeros for taking (what I feel) to be a more critical look at the problem and offering a schema adjustment.

Thank you everyone for your answers.

After thinking about this, I would revise your table structure just slightly.
First, revise your sheet-size table:

Sheet_size
===========
Id
Length
Width
Thickness

Second, create a sheet-size/type relationship table:

Sheet_size_type
================
Sheet_Type_Id
Sheet_Size_Id

Then, create the following constraints:

The primary key (and index) of Sheet_size should be the id column
There should be some sort of unique key (and index) enforced on the dimensions in Sheet_size.  Consider, are two sheets of dimensions (48, 96, .5) and (96, 48, .5) equal (that is, does the direction of the dimensions matter)?  This sort of problem may be difficult to enforce if through the use of the columns as part of the primary key, but becomes more manageable when using constraints and stored procedures.
The primary key (and index) of Sheet_size_type should use both of the foreign keys, starting with the one with the lower cardinality (probably sheet_type, given your example).  You may want an additional index in the other direction, but it might not be necessary.

This revision will save you database space (as a ratio of the number of sheet types using the same size), and shouldn't impact overhead too much.

There are other potential concerns about equality/uniqueness if you are using a float datatype, as the imprecision may trip you up unexpectedly.  You should consider whether or not a fixed-point type, to some given precision, would be more appropriate.


Excessive RAM consumption in MySQL Cluster at this moment I have configured a cluster that has 5 nodes:

2 data nodes
2 SQL nodes
1 node manager

My file.config.ini is the following:

[ndbd default]

NoOfReplicas = 2 # Number of replicas
DataMemory = 256M IndexMemory = 100M
ServerPort = 2202.
MaxNoOfConcurrentTransactions = 429496
MaxNoOfConcurrentOperations = 472496
MaxNoOfLocalOperations = 519745

[ndb_mgmd]

NodeId = 1
HostName = 192.168.10.145 # Hostname or IP address of MGM node
DataDir = / var / lib / mysql-cluster # Directory for MGM node log files

[ndbd]

HostName = 192.168.10.181 # Hostname or IP address
NodeId = 2 # Node ID for this data node
DataDir = / mnt / dataPartition / mysql / data # Directory for this data node's data files

[ndbd]

HostName = 192.168.10.183 # Hostname or IP address
NodeId = 3 # Node ID for this data node
DataDir = / mnt / dataPartition / mysql / data # Directory for this data node's data files

[mysqld]

HostName = 192.168.10.140 # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

[mysqld]

HostName = 192.168.10.184 # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

[mysqld]

IN THE NDB_MGM REPORT, IT SAYS THE FOLLOWING:

ndb_mgm> 2 REPORT MEMORYUSAGE
Node 2: Data usage is 18% (1476 32K pages of total 8192)
Node 2: Index usage is 11% (1461 8K pages of total 12832)
ndb_mgm> 3 REPORT MEMORYUSAGE
Node 3: Data usage is 18% (1480 32K pages of total 8192)
Node 3: Index usage is 11% (1462 8K pages of total 12832)

Each data node has 2gb of ram, it is assumed that with this configuration should not consume more than 500 mb of ram the ndbd process, but I am consuming 1.89 gb of ram per node, I am using disk storage for the fields that they are not indexed

What is wrong or what is wrong?

The 429496 transaction records consume around 400 MBytes
(around 900 bytes per record).

The operation records consume also around 400 MBytes,
around 300 bytes in transaction coordinator and
500 bytes in ldm thread.

In addition it uses memory for a lot of other buffers such
as REDO buffers, SEND buffers, job buffers. These normally
consume a few hundred MBytes.

In addition you can decrease memory usage by setting
BatchSizePerLocalScan to a smaller value (e.g. 16).
This should save a few hundred MBytes of memory space.


SQL Server Database backup regressed to historical date I have the following code snippet that takes backup of my production database every night -

use My_database
go
checkpoint
go
use [My_database]
go
DBCC SHRINKFILE (N'my_db_log', 500)
go
backup database My_database
to disk = 'W:\...\My_database._dmp1.bak'
, disk = 'W:\...\My_database._dmp2.bak'
... ... ...
, disk = 'W:\...\My_database._dmp30.bak'
with stats = 10
go

When I'd tried restoring the database to a test server I identified the backup was regressed to a historical date, as identified from different application related tables. This was not in sync with the date backup had been actually generated.

I am actually a Java / Java EE developer and not very versatile with SQL Server, any help in this area will be much appreciated. This database runs SQL Server 2008R2.

There was no latency, manually the files were copied 5 files at a time, was complete in 30-45 minutes. The command used for restoration was:

RESTORE DATABASE [MY_TESTDB]
FROM DISK = N'W:\...\My_database._dmp1.bak',
DISK = N'W:\...\My_database._dmp2.bak', ... ... ...,
DISK = N'W:\...\My_database._dmp30.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

Also this backup had run last time on 23 Oct 2015 but the restored test DB got updated only till 31 Jul 2015.

There was only one backup in each backup device. I would hardly believe there had been a problem with restoration, as it was refreshed to multiple databases by others with DBA knowledge, but result was always 31 Jul 2015.

Short answer: add ", INIT" to the end of your WITH statement to re-initialize the backup file on every backup. Per Microsoft:

INIT Specifies that all backup sets should be overwritten...NOINIT is the default.

https://msdn.microsoft.com/en-us/library/ms186865.aspx

I modified your code below to include the new change:

use My_database
go
checkpoint
go
use [My_database]
go
DBCC SHRINKFILE (N'my_db_log', 500)
go
backup database My_database
to disk = 'W:\...\My_database._dmp1.bak'
, disk = 'W:\...\My_database._dmp2.bak'
... ... ...
, disk = 'W:\...\My_database._dmp30.bak'
with stats = 10, INIT
go

p.s. I'd get rid of your SHRINKFILE in production because it fragments the database and it can cause interruptions in service because SHRINKFILE is usually slow. Any index rebuild/reorganize will probably bloat your database back to a similar size anyway.

And you could get rid of checkpoint as well as SQL Server issues an automatic checkpoint when a backup is taken to insure that the current state matches the log.


XMLDB - Nodes to Rows I have an application that stores XML as XMLType in a table. This is the first time I'm using XMLDB and XMLType features, and was wondering if I could use the results from an XQuery FLWOR expression in a "relational" way.

Considering the following example XML:

<PerfInfo TimeStamp="2014-11-23 17:13:04" GUID="5d4a9a3f-5a1c-478c-b12f-39e9f33172f4">
    <EventLog Count="408">
        <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-17 00:18:31" Provider="Application Error" ID="1000">Faulting application name: wmiprvse.exe</EventRecord>
        <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-17 00:18:43" Provider="Application Error" ID="1000">Faulting application name: wmiprvse.exe</EventRecord>
        <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-18 16:59:28" Provider="McLogEvent" ID="5051">A thread in process C:\Program Files\Common Files\McAfee\SystemCore\mcshield.exe took longer than 90000 ms to complete </EventRecord>
    <EventLog/>
<PerfInfo/>

You'll probably notice that these are extracts from the Windows Event Log.

These results (the actual XML files are, of course, way bigger and contain a lot of other metrics, but the principle remains the same) are stored in following table:

CREATE TABLE SPERF_XML_RAW
(
    "ID"                NUMBER              NOT NULL,
    "GUID"              CHAR(36 CHAR)       NOT NULL,
    "HOSTNAME"          VARCHAR2(16 CHAR)   NOT NULL,
    "USERNAME"          VARCHAR2(16 CHAR)   NOT NULL,
    "DATA_XML"          PUBLIC.XMLTYPE      NOT NULL,
    "DATE_CREATED"      TIMESTAMP(6)        NOT NULL,
    "DATE_INSERTED"     TIMESTAMP(6)        DEFAULT SYSDATE NOT NULL,
    CONSTRAINT SPERF_XML_RAW_PK PRIMARY KEY(ID)
)

I can use XQuery to, for example, query all the Provider names of an Event Record in the different XML documents, like this:

SELECT ID, GUID,
   XMLQuery(
       'for $i in /PerfInfo/EventLog/EventRecord
       return $i/@Provider'
       PASSING DATA_XML RETURNING CONTENT
   ) "DATA_THINGS"
FROM SPERF_XML_RAW;

And the results would be along these lines:

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application ErrorApplication ErrorMcLogEvent

However, the results I would like to have is this:

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    McLogEvent

Or, when querying multiple documents:

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    McLogEvent
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    ESENT
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    Microsoft-Windows-Search
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    McLogEvent
3   cd1054b1-1fe0-4842-a9ed-3f1b3c09ea0f    Application Hang
4   6c95827b-8fba-42d7-b34e-fec0b9e9606a    Group Policy
4   6c95827b-8fba-42d7-b34e-fec0b9e9606a    Microsoft-Restart-Manager

In other words, I want to be able to query my XML document in such a way that each selected Node returns as a Row. Is there a way to achieve this using XMLDB and Oracle 11.2g?

P.S.: In an actual query I would of course retrieve more than just the provider, this is just an example query. I'm looking to build different views into the document to explore the different metrics contained in the documents

I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts.

First, my original question can be solved by using XMLTable:

SELECT
  x.ID            AS "MEASUREMENT_ID",
  x.GUID          AS "MEASUREMENT_GUID",
  a.COLUMN_VALUE  AS "MESSAGE"
FROM SPERF_XML_RAW x,
  XMLTable('/PerfInfo/EventLog/EventRecord/@Provider'
            PASSING x.DATA_XML) a;

Note that COLUMN_VALUE is a pseudocolumn that can be used in conjunction with XMLTable

As additional information I would like to add the following. You can create multiple columns with the following syntax:

SELECT
    x.ID,
    x.GUID,
    res."Provider",
    res."TimeCreated"
FROM SPERF_XML_RAW x,
    XMLTABLE('/PerfInfo'
      PASSING x.DATA_XML
      COLUMNS
         "Provider"     VARCHAR2(32 CHAR)   PATH '/PerfInfo/EventLog/EventRecord',
         "TimeCreated"  TIMESTAMP           PATH '/PerfInfo/EventLog/EventRecord') res;

But Oracle will throw ORA-19279: XPTY0004 when one or more columns return multiple nodes.

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

The solution to THAT problem can be found below:

SELECT
  x.ID              AS "MEASUREMENT_ID",
  x.GUID            AS "MEASUREMENT_GUID",
  res."TIMECREATED",
  res."LOGNAME",
  res."LEVEL",
  res."ID",
  res."PROVIDER",
  res."MESSAGE"
FROM SPERF_XML_RAW x,
   XMLTable('/PerfInfo'
      PASSING x.DATA_XML
      COLUMNS
        "EventRecords" XMLTYPE PATH 'EventLog') rec,
   XMLTABLE ('EventLog/EventRecord'
       PASSING rec."EventRecords"
       COLUMNS
        "LOGNAME"         VARCHAR(32 CHAR)      PATH '@LogName',
        "ID"              NUMBER                PATH '@ID',
        "LEVEL"           CHAR(1 CHAR)          PATH '@Level',
        "PROVIDER"        VARCHAR2(128 CHAR)    PATH '@Provider',
        "TIMECREATED"     VARCHAR2(32 CHAR)     PATH '@TimeCreated',
        "MESSAGE"         CLOB                  PATH 'text()') res;

This will probably fail once more as soon as your document has too many levels, and I have not included a document with namespaces, which will most likely cause this to explode as well.

All information was found in the XML DB Developers guide: https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm#g1055369


Script for migrating server role permissions - SQL Server I need to obtain a script for all the user-defined server roles in my SQL Server instance with their assigned permissions, such as the below:

GRANT ALTER ANY AVAILABILITY GROUP TO [ServerRole1]

What I have done so far is:

SELECT ' GRANT ' + SPerm.permission_name + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
    where sp.type='R' and sp.name!='public'

but I am getting the error Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in add operator occurring in SELECT statement column 1.

I have then tried to solved it as below I am getting the error 'Expression type int is invalid for COLLATE clause':

SELECT ' GRANT ' + SPerm.permission_name + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id collate SQL_Latin1_General_CP1_CI_AS  = SPerm.grantee_principal_id collate SQL_Latin1_General_CP1_CI_AS
    where sp.type='R' and sp.name!='public'

In this case the issue was not with the join but with the text concatenation.

The following should work

SELECT ' GRANT ' + SPerm.permission_name COLLATE Latin1_General_CI_AS + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
    where sp.type='R' and sp.name!='public'

As an aside, I would strongly recommend looking at the Copy-DBAServerRole Powershell cmdlet in DBATools which is a Powershell module - it does the heavy lifting for you in jobs like these.


SQL 2016 Always on Listener I have followed many articles on how to get SQL 2016 always on setup and I am hopelessly stuck on the listener.

One of such articles is http://blog.fedenko.info/2016/06/sql-server-2016-always-on-availability.html

Not exactly what I am looking for but very close, I found it very hard to find a clear step by step and many different articles have different ways of doing it.

My listen problem:

On my DC open AD user and computers added SQL-HA-LISTENER as a computer

In DNS added SQL-HA-LISTENER 192.168.10.82

When i ping 192.168.10.82 i get Destination host unreachable

(192.168.10.82 and SQL-HA-LISTENER are not actual computers on the network)

Telnet 192.168.10.82 1433 closed, as to be expected.

I am confused as to what a listener "Is"

Is it a VM with SQL expressed installed on it?

That would be the only thing that makes sense.

If so how do i need to configure it to be a listener?

The Listener is a virtual object that "sits" on top of your AG, and directs your traffic.

You can create the Listener object in AD - SQL-HA-LISTENER as a computer, and grant access on it, to the actual computer nodes (replica hosts) that will be in your AG.


MySQL: Adding innodb_log_file_size in my.cnf causes not starting MySQL service I checked and found that InnoDB pluginis active.
By default, Innodb_log_file_size=5MB. As per the MySQL Documentation,
its ideal values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.

InnoDB_buffer_pool_size=1024MB and innodb_log_files_in_group=2

So, Value of Innodb_log_file_size should be 512MB.
To achieve this, I have written below line in /etc/my.cnf and tried to restart it.

innodb_log_file_size=512M

I am getting below error:
Starting MySQL.Manager of pid-file quit without updating fi[FAILED]

Please note that I have only two log files -one is for slow query log file and another is error log.

Can anyone help me to find why there is a strange behavior and I am
not able to edit Innodb_log_file_size?

To change innodb_log_file_size do following

Stop MySQL gracefully. Check the error log and make sure it was "Normal shutdown", no crashes, no errors etc.
Move existing ib_logfile* to safe place. You will need them if you want to roll back
Change innodb_log_file_size in my.cnf to the new value
Start MySQL. InnoDB will notice there are no ib_logfile* and will create new ones


How to generate this MongoDB diagnostics HTML report? I'm seeing this kind of report in many JIRA bugs and I want to generate it for my mongo instance.

What software was used for generating it?

These charts are produced by some internal tools being developed by the MongoDB engineering team for working with time series data as sampled from metrics like MongoDB's serverStatus command and Linux tools such as  iostat. There have been a few iterations of tools using languages like Python and Go, but usage and interpretation of data is currently more of an art for engineers familiar with the MongoDB internals. A general challenge is filtering the available metrics into those that are relevant (or possibly relevant) for a performance problem you are trying to understand. The chart images you see in Jira are the result of engineers manually analyzing and filtering metrics of interest.

I'm not aware of a public version of these tools yet, however you should be able to find a library for generating similar compact charts for metrics of interest: the chart format is called sparkline.  All of the metrics in your screenshot are from the wiredTiger section of the serverStatus command.

For operational metrics and monitoring, a production environment would normally feed into a metrics aggregation server or service which could provide more interactive monitoring and alerting across your entire deployment(s). For example, MongoDB Cloud Manager (SaaS) and MongoDB Ops Manager (on-premise) both include charts for common metrics. If you want to implement more custom monitoring, a good reference is the list of commands used by the MongoDB Cloud Manager monitoring agent.

MongoDB 3.2+ also includes an FTDC (Full-Time Data Capture) feature which periodically logs metrics (serverStatus, replication status, ...) to compressed files in the  diagnostic.data directory in the MongoDB dbPath. The diagnostic history is limited to 200MB of metrics as at MongoDB 3.4. You can see what commands are used in the FTDC source code, eg: src/mongo/db/ftdc/ftdc_mongod.cpp. For versions of MongoDB prior to 3.2 the engineers used to request the same information be collected manually for relevant problematic periods. FTDC information can be useful for MongoDB engineers to be able to request recent metrics history to help diagnose issues for postmortem analysis without having access to your full monitoring solution.


Retrieve latest 3 values from a large table Engine MySQL 5.6.37
I have a continuously growing table (around 10M rows) which keeps the values from devices along with a timestamp. Below is the table structure:

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`,`date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I would like to know if one of the past 3 meter values has crossed zero (for all meter IDs). I tried to use the below query and iterate through all the device IDs using PHP, but this approach is rather slow when I have around 500 meterids to scan:

SELECT COUNT(CASE WHEN metervalue>0 THEN 1 END) AS present
FROM (SELECT *
      FROM meterreadings
      where meterid= ?
      order by Id desc
      LIMIT 3) AS a

Is there a better way I can get this done, preferably with one single query and no iteration?

Check:

SELECT meterid /* , IF(SUM(metervalue>0),1,0) flag */
FROM ( SELECT t.*,
              IF(@meterid=t.meterid,
                 @rownum:=@rownum+1,
                 @rownum:=1+least(0,@meterid:=t.meterid)) rown
       FROM meterreadings t,
            (SELECT @rownum:=1, @meterid:=0) vars
       /* WHERE t.date_time > @some_date_time */
       ORDER BY meterid, date_time DESC
     ) nums
WHERE rown < 4
GROUP BY meterid
HAVING SUM(metervalue>0) > 0

The resultset is a list of meterid's which have at least 1 positive value within last 3 values.

Commented WHERE allows to decrease the amount of records processed by setting some "start point".

If you need full list of meterid's with a 'flag' of positive value existence - remove HAVING condition and uncomment flag field in the output list.


Declaring variable for a whole script I have a PostgreSQL 10 script for which I would like to declare variables once at the beginning and then use them throughout the script (with possible transactions).

I know I can do:

DO $$
DECLARE test float := 0.1 ;
BEGIN
    < do whatever here>
END $$

but that makes me declare the variables each time I have a BEGIN / END block.

There are no global variables per se in Postgres. But we have "customized options" that can be streched for the puropose.

SET myvars.test TO '0.1';

Persists for the duration of the session (not just transaction). Use with:

SELECT current_setting('myvars.test')::float;

Be aware that values are stored as text. So you may need to cast as demonstrated.

To avoid exceptions when referencing an unset variable and get NULL instead:

SELECT current_setting('myvars.test', true)::float;

See:

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

There are a number of other workarounds, too:

Is there a way to define a named constant in a PostgreSQL query?


Should ERD derived attributes be included in logical tables? I have created my entity-relationship diagram (ERD for brevity) and it has derived attributes.

When it comes to database design using a relational schema, I've designed my tables and linked everything properly.

I don't know if I should add a column to my table that has a derived attribute? For example, the derived attribute is calculated as follows:

Total = Price * Quantity

And it is related to the following illustrative table schema:

Customer (
    ID,
    CustomerName,
    ItemsID,
    Total
);

Should Total be included in the Customer table?

What you can do is not always appropriate for the various usages of your relational model. If you were creating a data warehouse in order to analyze customer sales then derived attributes would be appropriate.  I have done this for a summary table for a reporting tool.  The query would have joined upwards of thirty tables with many aggregations such as sums and show all values an entity has had.  A summary table, refreshed daily, listed derived attributes was a great solution for reporting.

For an online transaction processing database using derived attributes is not always the best solution.

For example: now your total is price * quantity

Next month management decides to implement a discount of 10% for customers who order more than $1000 in a calendar year.   Your total column now looks inflexible.

Unfortunately the things you can say today that "will never change" such as Total = price * quantity are really an example of business logic.  Business logic can change anytime in unexpected ways.

To continue with your example....if management institutes a discount and you have a customers table, orders table then all you have to do is add a discount table.  Then you can create a view which encapsulates the business logic of the day to derive total sales.  When the logic changes you can change the view much easier than recalculating the derived attributes that are fixed in a table.

And if you really want to be prepared you could store the changes in the business logic in a table in the database and cover off "Who, What, why".  So if the Bob the Manager offers a discount and five years later Sue, the new manager, says "When did we start offering discounts and who authorized it?" you are a database star.


Storing terabytes of data in SQL Server I need to develop a production system capable of storing a 100 kb textfile and a serial number for every piece produced. I know the approximate output over 2 years, the time the data has to be stored, and have calculated a need of around 25-30 TB of storage.

I have some experience working with databases, but handling that amount of data is new to me.

My first guess would be to simply store the files as a blob inside a table in the database. What are the problems of storing that amount of data in a single table? What are possible backup scenarios?

Another approach would be to store the data in several tables (e.g. a table per month).

A third approach would be to store references to the data in SQL Server, zip the files, and store them somewhere else.

The purpose of this storage system is to query the text file by using the serial number.

What would you do?

Historically, I'd shy away from storing files in the database and instead include pointers like in your alternative 3.  With SQL Server 2012, however, you might look into the FileTable feature.  The files are still stored within SQL Server (meaning that you'd have to be aware of the implications on your backup strategy), but also are easily accessible through the standard Windows API so you can manipulate them in Windows Explorer or PowerShell.  If you actually need to search within the text files as well, FileTable can do that.

On the other hand, storing 25-30 "extra" TB of data within a SQL Server database will be a challenge to maintain.  If this is just for compliance/auditing purposes (i.e., the probability of somebody actually looking at a particular file is low) and files aren't inserted very often, I'd probably stick with the pointers-to-files approach.  But if you need access frequently or there are a lot of changes, the advantage goes to FileTable because it takes care of the pointer-handling part of the process.


Default collation not working? I don't understand why the default collation on my psqlserver 11 instance returns a different result than using with explicit collation.

Details:

show LC_CTYPE;        -- de-AT
show LC_COLLATE;      -- de-AT

When I use the default collation I get an unexpected output:

demo=# SELECT unnest(array['a','B', 'A']) order by 1;
 unnest
--------
 A
 B
 a
(3 rows)

When I add the default collation explicitly, I get the expected output:

demo=# SELECT unnest(array['a','B', 'A']) collate "de-AT-x-icu" ORDER BY 1;
 unnest
--------
 a
 A
 B
(3 rows)

The collation de-AT-x-icu uses exactly the default values for LC_COLLATE and LC_COLTYPE shown above:

SELECT collname, collcollate, collctype
FROM pg_collation
where collname like '%de-AT%';

  collname   | collcollate | collctype
-------------+-------------+-----------
 de-AT-x-icu | de-AT       | de-AT

So I think both queries should return the same result: What am I missing?

More info:

postgresql version 11.5
OS inside the Docker container:
Linux 8660fb4cef84 4.9.184-linuxkit #1 SMP Tue Jul 2 22:58:16 UTC 2019 x86_64 Linux

Let me guess: you are using Alpine Linux, which uses musl as its C library, which uses ICU collations by default.

It is known that the collations in Alpine Linux don't work quite as they should, which is the cause of the problem. I am not sure if that is PostgreSQL's fault or not.

At any rate, you should use a different Linux distribution that uses glibc.


How to run mysql_upgrade on mysql workbench Is there anyway to run mysql_upgrade on mysql workbench?
Or it can be run only on command line?

mysql_upgrade is a shell script and does not have a GUI interface from MySQL Workbench. Run it from the command line (depending on your operating system).


db2: configurations covered by "create db autoconfigure" I am new to configuring memory in db2. I want to create a database using this command:
db2 CREATE DB TEST USING CODESET UTF-8 TERRITORY US AUTOCONFIGURE USING mem_percent 80 APPLY DB ONLY
According to IBM's Knowledge Center:

...the AUTOCONFIGURE command which will generate values for parameters based on your responses to questions about workload characteristics.
Some configuration parameters can be set to AUTOMATIC, allowing the database manager to automatically manage these parameters to reflect the current resource requirements. To turn off the AUTOMATIC setting of a configuration parameter while maintaining the current internal setting, use the MANUAL keyword with the UPDATE DATABASE CONFIGURATION command.

Is there a resource online that will tell me the values that AUTOCONFIGURE based on my current database manager configurations (assuming that I have not modified anything related to memory)? I want to know if not setting certain memory-related configurations will affect the performance of my database. For example, if I don't set configurations like pckcachesz to automatic, what is the default value of it?

You can start by creating the database without using AUTOCONFIGURE. Once it is created use AUTOCONFIGURE with the option APPLY NONE. It will show you what DB2 thinks are better settings.

Many DBAs then use the output of APPLY NONE as input to manually setting the values using update db cfg, it is a common approach.


Mysql backup of remote server in local machine using percona xtrabackup I had asked this question in serverfault.Later, i came to know about this site.

I have percona xtrabackup installed in my ubuntu client machine. My local machine IP address is 192.168.0.100. The database that i need to backup is in 192.168.0.200. Is it possible to do the backup of remote server in local server using percona xtrabackup? I tried doing the same using mysqldump in the past but i need to use percona xtrabackup in particular ? Thank you for the response.

You need to install Percona Xtrabackup on the remote server and then SSH from your local server to the remote server. From this position, you can initiate the backup and stream to your local server.

Xtrabackup requires access to MySQL's data directory (and MySQL instance as well), so it can't be run on another server. However, it can be triggered by another server with SSH (or alternative solution).

For example:

ssh serverA "innobackupex --stream=xbstream /var/lib/mysql" > backup.xbstream


Is the mapping between PostgreSQL system tables and information_schema views documented? If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog).

I'm looking for documentation that describes the mapping between

database objects and the system tables
the system tables and the information_schema views

For example, I'd like to be able to take a sequence name from a database, and be able to tell quickly where the sequence itself, the sequence's name, and the sequence's ACL (from GRANT and REVOKE statements) would be found in both the system tables and in the information_schema views.

Is that documented anywhere? I've been through PostgreSQL's online documentation, and I've found details of both the ANSI and PostgreSQL catalogs, but I haven't found documentation of the mappings.

Update

It looks like the information is probably in the source file information_schema.sql, but that file doesn't seem to be in the online source code. (See, for example, initdb.c at http://doxygen.postgresql.org/.)

The system tables are the implementation of database objects.  So if you, say, call a function foo(), the DBMS looks in pg_proc to see if there is a function foo and what the arguments and the source code and so on are.  The layout and arrangement of the system catalogs are merely the way the implementors of various features over time made them. You already pointed out the documentation of the catalogs.  In many cases, there is a simple mapping, say, between a function and pg_proc.  But in other cases, such as for an index, it's a bit more complicated.  You will have to dig that information out of the documentation or perhaps the many examples of system catalog queries flying around.

The information schema is specified by the SQL standard.  The principle there is, if you enter these DDL commands, then a query of the information schema should give these results.  In many cases, there is again a simple mapping between objects and information schema views, but it's not straightforward in all cases.  So maintaining a separate documentation of the mapping of this information would be cumbersome and probably useless.  The principle is DDL in, information schema out, not what happens in the PostgreSQL system catalogs.

Ultimately, if you want to know whether a sequence would be found in a catalog table or an information schema view, you need to query these tables.  The reality is too complicated for this to be simpler.  (I think.  Send a patch if you have a better idea.)


Reclaiming unallocated space from tempdb database in SQL Server Express 2012 I need to reclaim space from tempdb database which has more than 50 GB unallocated space. I have tried DBCC SHRINKDATABASE but it didn't do anything. Please note, I am aware that it has performance implications but I have no choice. I run SQL Server Express on a laptop and really need this 50 GB space. Also I have tried restarting SQL Server/laptop and have ensured that no process is running right now.

I have done it using

  DBCC SHRINKFILE ('tempdev',10)

Somehow DBCC SHRINKDATABASE was not working. Even DBCC SHRINKFILE ('tempdev',0) or DBCC SHRINKFILE ('tempdev') doesn't work. We have to assign some figure in MB to which it will shrink the database to.


sql server in docker using 15gb of disk space I'm following the instructions here to create my own docker container containing a sql server instance with databases and data configured.

I've added two databases, a few tables containing at most hundreds of items each, and a few stored procedures.

The base sql server image is 1.57 gb . (I'm using 2019-latest).

I would expect the total size to be only slightly higher. However in actual fact the container disk space usage is 15.6GB (virtual 17.1GB).

Does anyone have any idea why this might be, and how it can be reduced?

EDIT 1:
I've just checked and one of the databases I added is 14gb according to SSMS (tasks > shrink > database > currently allocated space). However it contains very little data (It could be scripted in a 1mb file) so I don't know why this is.

EDIT 2:
Mystery Solved: For some reason the scrript I was using to create the database had allocated 11gb for the log file.

The database files could be using those 15gb even in an newly created and empty database.

You can check the size of the databases using SSMS:

Or check the size of all databases at once using Alex Aza's code or some other code you like from SQL Server 2008: How to query all databases sizes? which is the source of this sample:

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

If you see a database that's bigger than expected, you can use DBCC SHRINKFILE (Transact-SQL) to reduce it. Using this command is ok in your specific situation though and not a recommendation to any situation of a big database file.


innodb , mysql Indexes difference when adding index When I am altering one of my MySQL table to add an index I am getting below error in error log.

  Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in the MySQL

What would be the exact root cause of this issue. Is the table not closed properly? I have read altering the table may remove the extra index which is causing the issue and we may create it later. But I want to know the exact reason why this is happening.
I am assuming inside innodb 4 indexes means those indexes definition updated in table .frm file and .ibd file along with ibdata1. But what does it mean by "inside mysql"?

Edit (add CREATE)

CREATE TABLE b (
    p datetime DEFAULT NULL,
    q datetime DEFAULT NULL,
    r bigint(20) NOT NULL,
    s varchar(25) DEFAULT NULL,
    t varchar(50) DEFAULT NULL,
    u datetime DEFAULT NULL,
    v varchar(50) DEFAULT NULL,
    w varchar(20) DEFAULT NULL,
    x longtext,
    y longtext,
    z bigint(20) DEFAULT NULL,
    k bigint(20) DEFAULT NULL,
    PRIMARY KEY (r),
    KEY e (z),
    KEY f (u),
    KEY g (s)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Try a no-op ALTER:

ALTER TABLE tablename ENGINE=InnoDB;

(Suggested in this forum.)

(Edits)

I do not know why the problem happened.

Here is another 'fix' that might work:

CREATE TABLE b_new LIKE b;
INSERT INTO b_new
    SELECT * FROM b;
RENAME TABLE b TO b_old, b_new TO b;
DROP TABLE b_old;


MySQL: OPTIMIZE TABLE vs. myisamchk I have a very large table (~50GB) and periodically rows are purged
from it and I want to run OPTIMIZE TABLE to recover the space. But I
do not have enough space to run it. If I do run it the server hangs
and must be killed and restarted and the table is damaged and must be
repaired. I do this with myisamchk.

At the MySQL documentation I read this:

  To coalesce fragmented rows and eliminate wasted space that results
  from deleting or updating rows, run myisamchk in recovery mode:

shell> myisamchk -r tbl_name

  You can optimize a table in the same way by using the OPTIMIZE TABLE
  SQL statement. OPTIMIZE TABLE does a table repair and a key
  analysis, and also sorts the index tree so that key lookups are
  faster. There is also no possibility of unwanted interaction between a
  utility and the server, because the server does all the work when you
  use OPTIMIZE TABLE.

Does this mean that myisamchk -r -a -S does the same thing as OPTIMIZE TABLE?

If they do do the same thing, why does myisamchk work but OPTIMIZE
TABLE run out of space?

They do the same thing, but there is a difference and a grave danger.

Here is the difference:

OPTIMIZE TABLE is done in the mysql client or in a mysql session.
myisamchk is a utility, not a client. Thus, there are no table locking safeguards. Therefore, you should never run myisamchk -r -a -S in a live system. Otherwise, you can quickly (in fact, instantaneously) corrupt a MyISAM table.

You could move the MyISAM table to another folder and run myisamchk against the table files without interference from mysqld or any client connections thereof.

Other difference

mysqld will create temp tables
myisamchk can figure out if it needs to make a temp table or do things in place.

More info

Doing OPTIMIZE TABLE will does a complete copy of the table. It does this under the hood

ALTER TABLE mytable ENGINE=MyISAM;
ANALYZE TABLE mytable;

Doing OPTIMIZE TABLE could make the temp table in /tmp and not in place like myisamchk

Please remember that tmpdir is mapped somewhere. That's where space may give out.


Help With Merge Statement I am wanting to compare the Distinct sn from HMI_Temp to sn in my table HMI -> if the sn exists, I want to update the values - got this syntax CHECK! - if the sn does NOT exist, I want to insert the data from HMI_Temp into HMI I have tried this syntax, but I get errors of:

  Invalid column name 'cb'
  Invalid column name 'lp'
  Invalid column name cn
  Invalid column name stn

And this is my syntax - how should I re-write this to accomplish my desired result?

MERGE INTO HMI AS Target
USING (SELECT DISTINCT sn FROM HMI_Temp) AS Source ON Target.sn = Source.sn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

DDL:

CREATE TABLE [dbo].[HMI_Temp]
(
    [sID] [int] IDENTITY(1,1) NOT NULL,
    stn [float] NULL,
    [sn] [nvarchar](255) NULL,
    [cn] [nvarchar](max) NULL,
    [lp] [nvarchar](max) NULL,
    [cb] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED
    (
        [sID] ASC
    ) WITH
      (
           PAD_INDEX = OFF
         , STATISTICS_NORECOMPUTE = OFF
         , IGNORE_DUP_KEY = OFF
         , ALLOW_ROW_LOCKS = ON
         , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[HMI_Temp] ON
GO

INSERT [dbo].[HMI_Temp] ([sID], stn, [sn], [cn], [lp], [cb])
VALUES (1, 8888, N'Test', N'Test', N'Test123', N'Test456')
VALUES (1, 8888, N'SecondTest', N'SecondTest', N'SecondTest123', N'SecondTest456')
GO

This is the update statement I was using that worked, but now throwing a wrench in it for the insert if not exists :(

UPDATE y
SET y.lp = x.lp
,y.cb = x.cb
,y.cn = x.cn
FROM HMI y
INNER JOIN HMI_Temp x
ON y.stn = x.stn
AND y.sn = x.sn
AND y.cn = x.cn

Here is at least a part of the problem: If you have two rows in HMI_Temp with the same value for sn but different values for cb (or any other column), which one should SQL Server pick?

Here is a better pattern than MERGE, IMHO, which has all kinds of issues:

INSERT dbo.HMI(stn,sn,cn,lp,cb)
SELECT stn,sn,cn,lp,cb
FROM
(
  SELECT stn,sn,cn,lp,cb,rn = ROW_NUMBER() OVER
    (
      PARTITION BY sn,cn -- makes sn + cn distinct
      ORDER BY sID       -- picks which row to keep
    )
  FROM dbo.HMI_Temp
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.HMI WHERE sn = hmi.sn
  )
) AS x
WHERE rn = 1;

This is taking the row with the lowest sID but you may wish to rank the rows a different way or use MIN/MAX on each of the other four columns to determine which row you want to keep. Hard to tell from your question.


How to insert data in table with constraint on column? - in parallel queries I have an Oracle database with a table with a unique field in it:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

Then from a session-1 someone is trying to insert some data:

insert into t(n) values(1);
-- no commit yet

and in session-2 someone is trying to do the same:

insert into t(n) values(1);
-- hangs here, wating for commit/rollback in session-1

What is the correct way to overcome such pity situations?
Session-2 seemes trapped, it may hangs for a vary long time 'being caught' by session-1.

I know the way with sequences, but what if the task is more complicated, say: to write a function which checks if a value exists and return it, and if not exists then INSERT (and here is the trap just inroduced, when running the same code in parallel) and then return it.

-- Ex:
create or replace function f(pn number) return number
is
  pret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  insert into t(n) values (pn);
  -- hangs here in parallel calls f(<new_value>)
  return pn;
end;

Commit as soon as possible. Otherwise, just to give you an idea:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

create or replace function f(pn number) return number
is
  pret number;
  l_ret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  l_ret := dbms_lock.request(id => pn, timeout => 0, release_on_commit => true);
  dbms_output.put_line('lock return code: ' || l_ret);
  if l_ret = 0 then
    insert into t(n) values (pn);
    return pn;
  else
    return -1; -- can not insert
  end if;
end;
/

Session 1:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 0
f return code: 1

select * from t;

         N
----------
         1

Session 2:

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

lock return code: 1
f return code: -1

select * from t;

no rows selected

Session 1:

commit;

Session 2:

select * from t;

         N
----------
         1

set serveroutput on
declare
  l_ret number;
begin
  l_ret := f(1);
  dbms_output.put_line('f return code: ' || l_ret);
end;
/

f return code: 1

I am not sure this will really help you, the ideal solution would be to prevent multiple sessions trying to insert the same value in long transactions.


need help building a query to detect duplicates on multiple columns I have the following table
create table catalog_product_entity_varchar
(
    value_id     int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id     smallint unsigned default 0 not null comment 'Store ID',
    entity_id    int unsigned      default 0 not null comment 'Entity ID',
    value        varchar(255)                null comment 'Value',
)

I want to find all entity_ids where attribute_id = 86 for the same store_id where the value is the same.
For example for the columns attribute_id, store_id, entity_id, value
86, 1, 1, mypath
is duplicate with
86, 1, 2, mypath
but is not duplicate with
86, 2, 1, mypath
The result would be entity_id, store_id, value
Any help is appreciated.

You can accomplish this with grouping on your store_id and value in a CTE or subquery to get the dupes and then re-joining the dupes to your  catalog_product_entity_varchar table like so:
WITH CTE_Dupes AS
(
     SELECT store_id, value
     FROM catalog_product_entity_varchar
     WHERE attribute_id = 86
     GROUP BY store_id, value
     HAVING COUNT(1) > 1
)

SELECT C.entity_id, C.store_id, C.value
FROM catalog_product_entity_varchar C
INNER JOIN CTE_Dupes D
    ON C.store_id = D.store_id
    AND C.value = D.value
WHERE C.attribute_id = 86


I would like to emulate the Microsoft SQL Server function GetSqlBinary() in MySQL version 5.6 I would like to emulate the Microsoft SQL Server function GetSqlBinary() in MySQL version 5.6.
  The C# namespace System.Data.SqlClient implements GetSqlBinary(), which is like fetching a blob.But the C# namespace MySql.Data.MySqlClient does not  implement GetSqlBinary().
  So is seems that the difference is that GetSqlBinary gives you a lump of data as a byte array, while GetSqlBytes is similar but stores the data in a buffer which allows you to interact with the underlying data as a stream.
  Here is the definition of GetSqlBinary():

GetSqlBinary returns an SqlBinary structure:
Represents a variable-length stream of binary data to be stored in or retrieved from a database.

GetSqlBytes returns an SqlBytes class:
Represents a mutable reference type that wraps either a Buffer or a Stream.

Please suggest a way to do the function GetSqlBinary() in MySQL. Thank you.
Frank

I don't quite understand the problem you are having - let me explain why. The SqlBinary class is just a wrapper around a byte[] which adds some nice programmer-friendly methods and properties to it. But underneath the hood it is just a byte array. You can find this out by viewing the source code here:

http://referencesource.microsoft.com/#System.Data/System/Data/SQLTypes/SQLBinary.cs,2cc17fbd58a1a375

Also, you can view the source code to the SqlDataReader class here:

http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlDataReader.cs,e46454446a9e2777

In short, it just takes the value of the field, casts it to a byte[] and constructs a new SqlBinary value from it. There is nothing stopping you from doing the same or implementing it yourself. Heck, if you implemented a MySqlBinary class you could even submit it as an enhancement to the library if you wanted to, I'm sure you are not the only person who would like it.

In short, doing this:

var binary = (byte[])reader.GetValue(i);

Is pretty much all you really need here.


SQL hourly data aggregation in postgresql I am a newbie with database so I'm seeking your help with this one.

I have a table containing time series data.

2012/01/01 00:10, 10
2012/01/01 00:30, 5
2012/01/01 01:00, 10
2012/01/01 01:40, 10
2012/01/01 02:00, 20

The table is storing interval based data by keeping only the upper limit of the interval. For example the first row represents an interval from [00:00 - 00:10] with a value of 10, second row represents an interval from (00:10 - 00:30] with a value of 5 and the third one represents an interval from (00:30 - 01:00) with a value of 10.

I need an efficient query in Postgres to aggregate hourly data for a structure like the one described above. So the result would be something like this:

2012/01/01 00:00, 2012/01/01 01:00, 25
2012/01/01 01:00, 2012/01/01 02:00, 30

Note that the time-series data is big so any help with indexing this would be much appreciated.

Thanks,
dan

select
  date_trunc('hour', t - interval '1 minute') as interv_start,
  date_trunc('hour', t - interval '1 minute')  + interval '1 hours' as interv_end,
 sum(v)
  from myt
    group by date_trunc('hour', t - interval '1 minute')
order by interv_start

see sqlfiddle

As for the index: you could try a function index on date_trunc('hour', t - interval '1 minute') but I'm not sure postgresql can use it.


MariaDB Parallel Replication Drifting at random times I have recently been thrown in the deep-end to manage our Database servers since our DBA left.

The current set-up is Mariadb (mariadb Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu), InnoDB (I turned on innodb_file_per_table as we had some 200+GB ibdata1 files.). We use parallel (conservative) replication.

a little bit of the config (just without ssl certs)

innodb_file_per_table = On
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 20
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
table_open_cache = 8192
thread_cache_size = 256
table_cache = 70000
open_files_limit = 100000
log_slave_updates
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
## Logging
log-output = FILE
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/slow-log
log-slow-slave-statements
long-query-time = 30
log_warnings = 2

I have noticed that sometimes our servers drift but this usually corrects itself as I am assuming a large statement(s), one of the reasons I turned on Logging. But some servers will just not correct themselves and one of these only has a 2.6G ibdata1 file, I have restarted slave a few times but its just very slow. I am good on the Linux and Hardware side and ensured there is no other problems with disks. The network link between the servers via web, each server has 1Gbps Connection though and we see no latency ~6ms ping and stable. Some servers drift when there is no load on the servers, like the slave is not requesting from the master and it certainly doesn't seem to use full bandwidth. Overnight one of our servers has gone from being synced to 3 hours behind and seems to be increasing. I cannot see any issue on other database or even any users logged in to our application. could something be locking it from replicating?

Any help or suggestions would be really appreciated as I am kind of thrown in the deep-end here, I am not a DBA but whilst we do not currently have one I am the "best fit" and we need to get these servers replicated correctly for production.

If anyone also has any suggestions on learning resources, I think maybe this setup needs to be started from scratch, we do not use GTID for example, I wonder if we even use the correct replication method.

Many Thanks.

innodb_file_per_table = On  -- only applies during CREATE TABLE or ALTER TABLE
table_open_cache = 8192
table_cache = 70000  -- old name for table_open_cache; 70K is too high
init-connect='SET NAMES utf8'  -- keep in mind that user=root skips init_connect
long-query-time = 30  -- so high as to be virtually useless

"one of these only has a 2.6G ibdata1 file" -- What do you mean?

"But some servers will just not correct" -- Do you mean 'some Slaves'?  Or 'some Clients'?

"no latency ~6ms ping and stable" -- that implies several hundred miles or km.

"Some servers drift" -- clocks drift; servers don't.  What do you mean?

"3 hours behind" -- Do SHOW SLAVE STATUS;

Replication existed for more than a decade before GTID was added.  That won't be to blame for what you are seeing.


Security considerations of allowing logical replication subscribers on PostgreSQL I run a non-profit dedicated to sharing data kind of like Wikipedia.

We recently had a client that wanted a replica of our database, and we realized that by using PostgreSQL's new logical replication we can replicate tables of our DB to a server they control.

This would be great for fulfilling our mission of sharing the data. It's 100× better than providing slow APIs.

We created a role for them like this:

CREATE ROLE some_client WITH REPLICATION LOGIN PASSWORD 'long-password';
GRANT SELECT ON TABLE some_table TO some_client;

And we created a PUBLICATION for them like this:

CREATE PUBLICATION testpublication FOR TABLE ONLY some_table;

Is there any risk of doing this? My analysis is that this gives them SELECT access to a table that they're replicating to their own server, but that's it. Any other concerns? If there are concerns, are there ways to make this work? We have tables we don't want to share, but most of our tables only have public data.

Note that the GRANT SELECT is only needed for the subscriber in order to do a bulk copy of the initial data in the table.  If they do not have that GRANT, they can still start a subscription, and they would be able to see any newly inserted rows or the new version of any updated rows.  The fact that the GRANT SELECT is needed for full functionality seems like wart in the design, as declining to GRANT the SELECT access does not deny them access to all the data, merely the subset that already existed and was never updated.  This is confusing in a way that could lead to security mistakes.

Also, there is no fine-grain control to which users can subscribe to which publications in a database (at least, not that I can find).  Any user WITH REPLICATION who can connect to a given database can initiate subscriptions to all publications in that database.  Combined with the previously discussed limitation in which denying SELECT on a table doesn't deny all visibility to the contents of the table, this is really limiting. Although maybe not in your use case, if you only have one outside party, or if all outside parties need access to the same thing.

Cascading logical replication is possible. So you can gain some fine-grained control by creating one "sterile" database for each publication you want separate control over.  In that sterile database, you create a subscription to the publication in the main database (connecting to it with some internal role if course, not the external user's role), and create a publication of those same tables.  Then you grant the external user's role CONNECT (or using pg_hba.conf settings) to only the sterile database and not the main database.  You could do this either with a separate database server, or a separate database within the same database server.  It does annoyingly increase the storage needs, and the CPU overhead of republishing the data internally.

In order to let the outside party connect in order to do the replication, you will have to poke a hole in your firewall to let them in.  Doing that could open you up to various miscreants trying to hack into your database. So make sure you have strong passwords (or some better form of authentication, like client certs) on all roles, not just "some_client".  Or poke the firewall hole only for the client's domain or IP address range.  Or make sure all your roles in pg_hba other than "some_client" are allowed only from internal IP addresses.  Or all of the above.

Finally, you open yourself up to accidental (or even intentional) denial of service attacks from this client.  If they fail to keep their database up and running and following along with your publication, it would cause your database to start retain pg_wal files, perhaps to the point where it fills up your storage system and causes downtime. So you should have some way of monitoring pg_wal size and be prepared to forcibly drop the replication slot if it is not keeping up.


Cannot connect to Aurora RDS using workbench over ssh I set up an environment on AWS that consists of a VPC with a private and public subnet following the instructions for Magento Quick Start: http://docs.aws.amazon.com/quickstart/latest/magento/architecture.html
The private subnet includes:

an EC2 instance (web server)
an Aurora RDS that connects to the web server

The public subnet includes:

a bastion instance for ssh connections to the EC2 instance in the private subnet

Generally, I can connect to Aurora RDS via ssh by connecting to the bastion host, then connecting to the private EC2 instance, and then to the RDS, however this makes it hard to perform simple tasks on the RDS.
Is it possible to connect to the RDS using mySQL workbench over ssh through the bastion?
I've tried everything but nothing seems to work.
Thanks.

This took me forever....

Other than the permissions in the security groups that i set initially, i had to allow port forward on the bastion instance.

connect to the bastion instance via SSH
go to /etc/ssh
sudo nano sshd_config
Set: AllowPortForwarding yes
restart the instance
Connect with workbench


Oracle SQL Developer 3.2.09 is consuming lot of memory on windows 7 Oracle SQL Developer  Version 3.2.09 is consuming lot of memory on windows 7.

I can see, after 1/2 days of usage and without closing the this application, the memory consumption is going upto 700 MB.

So, in this case, I need to close the application and again restart.

Any remedies for this can be helpful.

Details of SQL Developer are:

Oracle SQL Developer  Version 3.2.09

Build MAIN-09.30

JDK we are using is JDK 1.6

Add below lines to C:\Users\<<user>>\AppData\Roaming\sqldeveloper\4.2.0\product.conf

For 64 bit SQL Developer add:

Add64VMOption -XX:+UseG1GC
Add64VMOption -XX:MinHeapFreeRatio=15
Add64VMOption -XX:MaxHeapFreeRatio=30

For 32 bit SQL Developer add:

Add32VMOption -XX:+UseG1GC
Add32VMOption -XX:MinHeapFreeRatio=15
Add32VMOption -XX:MaxHeapFreeRatio=30

Restart SQL Developer.

To reduce the memory close only the results pane that has huge result set.

TIP: I typically increase JVM memory to 2 GB so SQL Developer does not crash for queries with large result sets. This can be done by adding below line to product.conf:

Add64VMOption -Xmx2048m


Fast hamming distance queries in postgres I have a large database (16M rows) containing perceptual hashes of images.

I'd like to be able to search for rows by hamming distance in a reasonable timeframe.

Currently, as far as I properly understand the issue, I think the best option here would be a custom SP-GiST implementation that implements a BK-Tree, but that seems like a lot of work, and I'm still fuzzy on the practical details of properly implementing a custom index. Calculating the hamming distance is tractable enough, and I do know C, though.

Basically, what is the appropriate approach here? I  need to be able to query for matches within a certain edit-distance of a hash. As I understand it, Levenshtein distance with strings of equal length is functionally hamming distance, so there is at least some existing support for what I want, though no clear way to create an index from it (remember, the value I'm querying for changes. I cannot pre-compute the distance from a fixed value, since that would only be useful for that one value).

The hashes are currently stored as a 64-char string containing the binary ASCII encoding of the hash (e.g. "10010101..."), but I can convert them to int64 easily enough. The real issue is I need to be able to query relatively fast.

It seems like it could be possible to achieve something along the lines of what I want with the pg_trgm, but I'm a bit unclear on how the trigram matching mechamism works (in particular, what does the similarity metric it returns actually represent? It looks kind of like edit-distance).

Insert performance is not critical (it's very computationally expensive to calculate the hashes for each row), so I primarily care about searching.

Well, I spent a while looking at writing a custom postgres C extension, and wound up just writting a Cython database wrapper that maintains a BK-tree structure in memory.

Basically, it maintains a in-memory copy of the phash values from the database, and all updates to the database are replayed into the BK-tree.

It's all up on github here. It also has a LOT of unit-tests.

Querying across a dataset of 10 million hash values for items with a distance of 4 results in touching ~0.25%-0.5% of the values in the tree, and takes ~100 ms.


ORA02063 error while connecting to remote databse I have connected to remote database using DB link. From prod databases it works fine but from test env reporting error invalid username/password. Please help.

My Oracle Support Doc ID [456320.1] indicates that the IDENTIFIED BY VALUES clause is only intended to be used by internal utilities (export/import) when used in a database link definition, which may be why the link is not working.  Database link passwords are stored in a separate encrypted format starting in Oracle 10.2.

  The only valid value to be passed in the "IDENTIFIED BY VALUES" clause is an encrypted dblink password identifier (note - this is different from the user password identifier stored in the PASSWORD column of USER$).

The document indicates because this is the expected and intended behavior of the "IDENTIFIED BY VALUES" clause, using it in a database link definition and then trying to use that link to execute remote SQL statements isn't classified as a bug if you receive an ORA-600 error.

Once created, the database link DDL in the database already stores the remote schema password using the IDENTIFIED BY VALUES encrypted format (for example, when retrieved with DBMS_METADATA.GET_DDL()).  Once created using the actual remote schema password, it will be encrypted by Oracle, so unless there is a very good security reason not to use the actual password when creating the link, it seems like this is the best strategy if you are using the database link for normal remote DML operations.


Does calling a stored procedure implicitly start a new transaction? If I'm in a transaction, will calling a stored procedure implicitly commit the current transaction and start a new one?

Let's find out:

In session 1:

DELIMITER ;;
CREATE PROCEDURE p1() BEGIN SELECT 1; END;;
DELIMITER ;

CREATE TABLE t1 (id int unsigned auto_increment PRIMARY KEY);
START TRANSACTION;
INSERT INTO t1() VALUES ();
CALL p1();

In session 2:

SELECT * FROM t1;

which gives:

  Empty set (0.000 sec)

If the procedure call had caused the current transaction to be committed in order to start a new one, then we would have expected to see a row from the above SELECT.

So, back in session 1:

COMMIT;

And then session 2, run the SELECT again:

SELECT * FROM t1;

which now gives the expected result:

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.001 sec)

Conclusion: No, calling a stored procedure does not commit the current transaction and does not start a new transaction.


What is a good size to accommodate a single-use database for a small application? I'm looking to provision out arbitrarily many sandboxed MySQL databases for anyone to use, similar to how SQLFiddle does, but permanent and with API keys (one database per key), which would allow small developers to set up single-use databases for small applications. What is a sensible upper limit for the drive space I should allow these to take up?

It is generally accepted, by those who study such things in the Big Data realm, that the majority of databases fit within 10 GB. There are many examples of some truly very large databases in the Terabyte to PB range but these are not the norm and I gather not applicable to your use case. Per your description, your use case would appear to easily fall within 10GB or less category. I would further guess you could refine that number for your use case to between 2 to 5GB for "single-use databases for small applications."

If it was my use case, I'd go to the developers and ask to see their local instances of the DB and refine my estimations.


sql server , set N prefix as unicode for all queries by default Is there any option in ms sql server which use N prefix for my database.
There are many query in my application which non of them use N prefix for nvarchar fields , and now I have to set N for all of them and it takes long time to do.
My collatation is Persian_100 and it need unicode (N prefix) so any advise is appreciated

select * from table1 where field1=N'حمید' ;

I want skip N prefix in my query , but it acts as unicode for all type of queries:

No, there's no way to do this.

Any option that did what you want would be extremely confusing because there would need to be a way to turn it off within the string literal syntax. There are other considerations (like storage size) to be aware of when choosing data types, so a setting like this could be very dangerous to put in play.

With regards to .NET applications that send parameterized T-SQL statements to SQL Server, the framework takes care of this for you because a .NET String inherently supports Unicode and things were built using that assumption. This applies to ORM-type libraries as well if they're programmed well.

Consider the following code snippet:

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = @"SELECT * FROM sys.server_principals WHERE name = @name";

IDbDataParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@name";
param.Value = "dbo";
cmd.Parameters.Add(param);

IDataReader dr = cmd.ExecuteReader();

What actually gets sent to SQL Server is this (line break added for clarity):

exec sp_executesql N'SELECT * FROM sys.server_principals WHERE name = @name',
    N'@name nvarchar(3)',@name=N'dbo'

You can see that the framework automatically used a Unicode type for the parameter (the length of 3 is a separate discussion entirely...).

The places where you need to modify code are in situations where SQL queries are not parameterized (or are unparameterizable, i.e., DDL) -- in other words, queries that have parameters concatenated directly into the string, or contain string constants. See my answer here for an example of how to avoid SQL injection issues when using dynamic SQL.


Procedures using TVPs are slower when the TVP numeric value gets larger? A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process.  Now that the ids are in the millions, it seems that this process is taking noticeably longer. The roughly the same number of batch calls are being run each night, but from profiling it seemed that the procedure was getting slower.
We checked the usual culprits, rebuilt the indices and updated stats on the tables in use and tried sticking a recompile on the procedure. But nothing fixed the regression.
The procedure does some processing and returns a few result each with a cardinality of maybe 10000 rows. One of my colleagues looked at it and fixed the performance regression by updating the store procedure by simply adding the following to the top of the query:
select id into #t from @ids

and replacing all usages of @ids with #t.
I was amazed at this simple fix, and was trying to understand it more. I tried to create a very simple reproduction.
create table dbo.ids
(
   id int primary key clustered,
   timestamp
);

create type dbo.tvp as table(id int primary key clustered)

insert into dbo.ids(id)
select row_number() over (order by 1/0)
from string_split(space(1414),' ') a,string_split(space(1414),' ') b
go
create or alter procedure dbo.tvp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from @ids t
            where t.id = i.id
        );
end
go
create or alter procedure dbo.temp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    select * into #t from @ids
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from #t t
            where t.id = i.id
        );
end

And here is my simple benchmark.
set nocount on;
declare @s nvarchar(4000)=
'declare @ids tvp;
insert into @ids(id)
select @init + row_number() over (order by 1/0)
from string_split(space(99),char(32)) a,string_split(space(99),char(32)) b
declare @s datetime2 = sysutcdatetime()
create table #d(_ int)
insert into #d
exec dbo.tvp_proc @ids
print concat(right(concat(space(10),format(@init,''N0'')),10),char(9),datediff(ms, @s, sysutcdatetime()))',
@params nvarchar(20)=N'@init int'
print 'tvp result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000
select @s=replace(@s,'tvp_proc','temp_proc')
print 'temp table result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000

Running this benchmark on my machine yields the following results:
tvp result
10,000,000  653
 1,000,000  341
   100,000  42
    10,000  12
temp table result
10,000,000  52
 1,000,000  60
   100,000  57
    10,000  59

The results show that the tvp approach seems to slow down as the ids inside get bigger, where as the temp table stays pretty consistent. Anyone have an idea as to why referencing a tvp with larger values is slower than a temp table?

Table Variables, even when used as a parameter (TVP), are given very poor cardinality estimates as opposed to Temp Tables which are much more accurately estimated. This difference is especially noticeable as the amount of data grows that's being utilized in a TVP vs Temp Table. If you looked closely at the Estimated Number of Rows vs the Actual Number of Rows in the execution plans of each implementation, you should see Temp Tables are a lot more accurately estimated.
You can read more on TVPs and their downsides in this Jeremiah Peschka post. Specifically the Gotchas section:

First: the table variable that comes in as a table valued parameter
cannot be changed. You’re stuck with whatever values show up. No
inserts, updates, or deletes can be applied.
Second: table valued parameters are still table variables – they get
terrible cardinality estimates.
We can get around both of these problems with the same technique –
copy the contents of the TVP into a temp table.

Additionally, TVPs used in procedures can result in parameter sniffing issues, as this other post details. This quote adds some specifics to the cardinality estimates you'll encounter with TVPs regardless of how big the actual Table Variable is:

Table Variables (unless you Recompile, or use a Trace Flag), will
sport either a 1 or 100 row estimate, depending on which version of
the Cardinality Estimator you use. The old version guesses 1 row, the
new guesses 100 rows.

This is an additional good article on cardinality estimate issues that result from Table Variables, by Pinal Dave.

One key reason why having a bad cardinality estimate such as an under-estimate in this case, is because it'll result in the SQL Engine under-provisioning the necessary server resources to process the query and serve the data. For example, your query is likely requesting much less memory than it needs for it to be processed because of the low cardinality estimate making the SQL Engine think there's a lot less rows going to be returned than there actually will be. The larger the table variable gets, the larger the discrepancy between the estimate vs actual.

You should pretty much always opt for Temp Tables when possible for the very reason they have many more performance benefits than a Table Variable, and can do almost everything a Table Variable can do and more. In the cases where you need to use a Table Variable, selecting it into a Temp Table first and using that Temp Table in your subsequent querying is the way to go.


Federated tables and triggers This is the scenario.

Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2).

I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1.

I have created a temporary table (base on DB1 and federated on DB2), so when I insert a row on T1 which complies with some requirements I copy to my DB1 temporary table.

That's OK.

With the federated table on DB2 I get the row I need for T2.

I presumed that with a trigger on the federated temporary table of DB2 it's possible to insert that row to T2, but the trigger never fires.

Any suggestions?

You should not put any faith in triggers when it comes to federated tables. You are better off creating a temporary trigger on the real MyISAM table on the source side.

Picture this:

On Server S1
- T1 is MyISAM table
- T2 is FEDERATED table to T1 over on S2

Do the following:

Create the Trigger on T1 to insert a row into T2
Insert Data into T1
Drop the Trigger

ALTERNATIVE

You would be way better off using MySQL Replication as follows:

S1 is the Master
S2 is the Slave
Set replicate-do-table=db1.T1 in my.cnf on S2

This would automatically do all DML against that table provided you replicate to the same named table.


What does: ERROR: tuple offset out of range: 0 mean? It seems (but I can't tell for sure) that after I added a postgis indexed column (USING gist) to my database last week, I have been seeing a lot of those errors:

ERROR: tuple offset out of range: 0

The errors occur on all kinds of queries, also some that does not use those columns, and hence shouldn't use that index right?

Anyways my question is: What does that error message mean, and could it have something to do with postgis, or am I off track?

I'm on postgresql 9.3 and postgis 2.1

It was all caused by the bug described and fixed in this release: http://www.postgresql.org/about/news/1506/

It caused us a lot of trouble!


why is blevel null for some indexes in Oracle DB? I was trying to see the b-tree levels for indexes and to my surprise I see some of them are set to null.

SELECT index_name, blevel FROM user_indexes ORDER BY 2 desc;

Their name starts with "SYS_"(picture attached below). SO I am assuming they are system generated indexes? Maybe of primary keys? Either ways I am curious as to why they are null. I can understand if the value is 0 which mean everything is in leaf nodes and it will just take 1 hop for unique scan to get the data.

Indexes whose the name start with SYS_IL.. are LOB indexes.

BLEVEL is a property of B-tree indexes, and does not make sense for LOB indexes.

Even if you collect statistics, BLEVEL for such an index will remain null.


SQL DB2 query with where clause Can you please help me out with the below query?

I need to extract from column VAL_DT only items with date older than today's date but this rule should not apply to items that contain the following wordings in column MVT_DS: "NOSTRO", "SCREV", "SCRIN", "MAN".

here is my attempt

SELECT * FROM table_name
WHERE VAL_DT < CURRENT DATE  IF MVT_DS <> 'NOSTRO' AND MVT_DS <> 'SCREV' AND MVT_DS <> 'SCRIN' AND MVT_DS <> 'MAN'

I would amend your logic to:

WHERE
    VAL_DT < CURRENT DATE
    OR
    (
        MVT_DS = 'NOSTRO'
        OR MVT_DS = 'SCREV'
        OR MVT_DS = 'SCRIN'
        OR MVT_DS = 'MAN'
    )

or even

WHERE
    VAL_DT < CURRENT DATE
    OR MVT_DS IN (
      'NOSTRO', 'SCREV', 'SCRIN', 'MAN'
    )

I changed it so that VAL_DT should be earlier than the current date or the other field should equal the respective values. So to get a "true" result - either VAL_DT < "current date" or MVT_DS is one of those values. If it's one of those values it will therefore bypass the "current date" check, effectively.


Giving a MYSQL Database a "version" I am writing a game that uses an embedded mySQL database for keeping track of scores and   I am releasing an alpha soon.

I know there are going to be features added down the road which are going to require changes to the database structure.

Up to now, whenever I changed the database's schema, I just deleted what i had and had a new one generated.

But now that I'm releasing an alpha, I don't want to be so careless with my players' score data.

It feels like I would like to give the database a "version number", so when the game starts, I can check what version database it is, and if it's not the current version, automatically do whatever surgery on the database to give it the right schema.

My intuitive approach to doing this is adding a single row, single column table called "DB_VERSION" and then checking it each time on load.  This way, if I change the database, I can increase the number in my code, and when I come across a database in the wild that has a lower number, I can upgrade the database structure automatically.

While this will work, intuitively something is telling me this is The Wrong Way.

Is there a built-in way to give a database a version ID? Is there some other approach to solving this problem that I haven't thought of? I'm sure this isn't a unique problem.

Thanks!

Here is the code I would like you to review:

When the database is first built:

statement.execute ( "CREATE TABLE DBVersion ( Version INT NOT NULL )" );

and then each time the program starts:

statement.execute ( "Select Version from DBVersion");
int databaseVersion = rs.getInt ( "Version" );

if( Database.version != databaseVersion ) {
   //Update database schema
}

An alternative would be checking for each of the changes you want to make — does table foo exist, does column bar exist in table baz, and so on. Which sounds cleaner, perhaps, but means a lot more i/o.

So the best way would be something like

int databaseVersion = getDatabaseVersion(); // because of course it's a function

try {
   if (databaseVersion < 2) {
      // Update database schema to version 2
      databaseVersion = 2;
   }

   if (databaseVersion < 3) {
      // Update database schema to version 3
      databaseVersion = 3;
   }

   // and so on...
}
finally {
   setDatabaseVersion(databaseVersion);
}

Don't forget to write the version to the database.

If you do not tie the database version number to your program version number (so databaseVersion <> programVersion, but of course a specific version of your program has a specific version of the database), but instead increment the version number after each DDL statement you execute, this, coupled with the version being written in the finally, ensures that if for whatever reason the update crashes halfway through, the next time you can continue from that point on.


How to set auto growth in SQL Server 2016? How can I set the Auto growth values in SQL Server 2016? The usual place in the database Properties -> Files is not available.

I see the option on my UI? but I had to scroll, failing that have you tried running the TSQL

USE [master]
GO
ALTER DATABASE [YOURDB] MODIFY FILE ( NAME = N'TheFile', FILEGROWTH = 10240KB )
GO


Can I restore an SQL 2016 database mdf at an SQL 2014 Sp1 instance? I have a database file .mdf created in SQL server 2016. I am planning to move at a new web hosting provider who supports 2014sp1 version. Should I proceed or this is out of specs? Can I restore an 2016 version at a 2014 one?

No You cannot. You have to bcp out data and bulk inert into 2014 or you can create sql 2014 schema + Data script and run the same on sql server 2014.

See my answer here : https://dba.stackexchange.com/a/55063/8783


How to diagnose what is causing DB timeouts I'm a web developer which currently has the joy of managing a client website that is still using Classic ASP that uses SQL Server 2000.

The code is quite old and in the process of been updated to a completely new system but in the interim we have to keep the websites running as smooth as possible.

There are three sites, a commercial front end, UK backend and Singapore back end. The two backends have a nearly 95% identical code base and each has a database on the same server and are hosted on the same machine.

MY issue is that for the last two or three days there have been errors for users when they try to log in on the Singapore website, the error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired

The code in question is a simple UPDATE statement, updating a session variable for one entry and the code has been running fine for many years.

There have been no recent code changes either that would affect this. How can I diagnose what is causing the bottleneck, EventViewer, SQL Error logs and IIS logs seem to not show errors around the time the timeout occurred.

My guess that the error is occurring when this code tries to run the UPDATE but there was/is an earlier process choking the SQL Server, but not been a DBA I am only guessing at this point.

Many thanks
John

I struglled with this issue for one week.

A the beginning, I started with a 45 seconds query for which the timeout  error message was relevant. I reduced the execution time to 3 seconds and had still the error.

While seraching the Internet, I found out many cases telling the message itself is not relevant, and for this reason, my further investigations were made in other directions. I tried many workarounds I found in many forums without success.

Within my ASP code, I'm using a ADODB.Command object. This object has a CommandTimeout property expected to have a 30 seconds value. I tried to setup the value to 0, which means an unlimited timeout and I had no more the 80040e31 error. Then I tried a 30 value, and again, it worked.

For some reasons, it looks like the defalut value for the CommandTimeout of the ADODB.Command moved to another value. Setting up this value back to the expected default value solves the issue.

Hope it will work for you too.

Yvzs


Database design in MySQL I am designing a database for an web application, in this system there are multiple users. Consider,

Public (Buyer).
Merchant.
Tele Marketing Executive(TME).
Marketing Executive(ME).
Lead Generator...etc.

I have decompose the User table into UserType and UserRoles, according to me, this is known method.

The issue is when I trying to design for sales dashboard, I need to get the values of Tele Marketing, Marketing and Merchant.

The Sales board will be consisting of following fields.

Sale No.
TME Name.
ME Name.
Merchant Name.
Total Amount.
Payment Mode...etc.

For sales_dashboard table I need to use the User_id as a Foreign key, but my doubt is can I get the TME Name, ME and Merchant details from User table by using that foreign key.

FYI:

Also I need to find the Users who belongs to Merchant but same doubt both Users, Merchants and staffs in a same User table, how can I identify the particular staff is belongs to particular Merchant?

Any method to do this, I hope this is my mistake sorry for my lack of knowledge, I am new to Database.

Thanks in advance.

"The Data Model Resource Book", Volume 1, has a whole chapter on dealing with contacts and solves those kinds quite extensively. As does any CRM btw.

Basically:

Sorry, the userRole approach sucks - it is widely accepted - by anyone with experience as a mistake. So, your staff NEVER EVER BUY? Nice products. Roles and entity information are separate.
There are obviously relationships between entities - a is staff of b (from time to time, btw.). This is a m:n relationship and requires a second table.

Btw., I really hope you do not plan to store the password in the password field - beginner mistake, violation if basic security principles. Never ever store a password, only a salted hash - hashed many times. Read up on that topic.


finding text in a string I am trying to find records where the post code starts with a certain set of characters from some 10,000 records, 1300 or so of which meet the select below.
My code is

Select * from table where mid(postcode,1,2 ) IN ("CV DE LE MK NN")

This returns a zero result
A typical postcode would be something like CV1 9AD

If I write

Select * from table where mid(postcode,1,2 ) = "CV"

I get the full list of records for post codes beginning with "CV"
I would have thought that the IN statement would work. Can anyone tell me why it does not?

The IN operator expects a list of items, not a single string.
You need to structure your query as follows:

Select * from table where mid(postcode,1,2) IN ('CV','DE','LE','MK','NN');


How to check if the current time is between two times? postgresql documentation gives an example timerange definition:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
  subtype = time,
  subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

The problem is that when time crosses the day barrier, the range can no longer be used, e.g.

SELECT '[22:00, 06:00]'::timerange;

will produce an error:

ERROR:  range lower bound must be less than or equal to range upper bound
LINE 1: SELECT '[22:00, 06:00]'::timerange;

I need to be able to check if the current time is between 22:00 today and 06:00 tomorrow.

Solution 1:

Define the range column as 'hours since midnight'. Continuing with the 22:00 and 06:00 range example, the range value would be [22, 30] (22 hours since midnight, 1 day + 6 hours since midnight), i.e.

current_date::timestamptz + interval '22 hours' AND
current_date::timestamptz + interval '30 hours'

One could define a custom rangetype for this purpose.

Solution 2:

Alternatively, you can have two columns:

maintenance_window_starts_at time without time zone
maintenance_window_duration interval

and write the check as:

SELECT
  maintenance_window_starts_at IS NULL OR
  now() BETWEEN
    current_date + maintenance_window_starts_at AND
    current_date + maintenance_window_starts_at + maintenance_window_duration
FROM maintenance_task;

Both solutions were suggested by the members of Freenode #postgresql community.


ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host I am trying to create a logical replication between 2 postgres servers.
But when I try to create a subscription from the slave to the master I get the error

ERROR:  could not connect to the publisher: FATAL:  no pg_hba.conf entry for replication connection from host

Here are my configs.

Master server ip 192.168.100.21;
Slave server ip 192.168.100.22

Master server pg_hba.conf :

host  replication  replicate  192.168.100.22/32   md5

Slave server pg_hba.conf :

host  all   postgres   192.168.100.21/32  md5

User for replication is 'replicate'. On the master, I have granted database access to 'replicate'user and created the publication.

I have also done a schema dump from Master to Slave using pg_dump.

Have I missed anything here?

host replication replicate 192.168.100.22/32 md5

Logical replication needs to connect to an actual database.  The special name 'replication' is only used for physical replication, not logical replication.


mysql information schema database last used datetime I have a lot of tables in my database, almost 1,000. Most of them I am not using now.

Is there any way to know from the information schema tables when I have last used my tables?

No, that information is not available on INFORMATION_SCHEMA (it only stores metadata).

There are several options that I can recommend you:

The user stats plugin from Google: after installed and enabled, it will not tell you the last time a table was accessed, but it will tell you how many times it was
Using the slow log: you can activate the slow log and set long_query_time = 0 (be careful of the extra overhead and disk space and then analyze per table used, so you can detect the last time it was used. Tools like pt-query-digest --review aggregate by query, not by table, but they may be useful to look for specific queries.
With an audit plugin you can fine-tune what you register better than with the slow log, by activating it only for certain queries or objects.
The performance schema can gather information about everything, but you may need MySQL 5.6 and the configuration can be a bit cumbersome

All of them will tell you information since the point you activate them, and not past information. UNIX atime is very unreliable for access time, but you may also want to give that a try, depending on the engine.