Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine?

Your decision on VM or not shouldn't be so much determined by the number of rows but rather the availability, scaling, technical expertise, company culture, and performance based on your hardware availability.

You can run billions of rows in VMs.  Obviously you'd want to find the database engines best practices doc for your hypervisor and follow them carefully.  You should conduct plenty of testing to ensure it meets your needs and also test your HA solutions if needed for your SLAs.

External and internal at the end of the day come down to availability and IOPS or MBps.  If you have the expertise to run a fast external source reliability, great!  If you don't, then it would be safer to go internal.  Internal will be faster anyways by default without any special configuration or external hardware anyways and you should be testing your perf. reqs. but with SSDs and the latest nand solutions you're a lot less likely to under perform.


SQL Server: Programatically trigger export of db schema

These days, by far the best way is both open source and from Microsoft (what a world we live in):

mssql-scripter

The README says it all, so I'll just quote it:

  mssql-scripter is the multiplatform command line equivalent of the
  widely used Generate Scripts Wizard experience in SSMS. You can use
  mssql-scripter on Linux, macOS, and Windows to generate data
  definition language (DDL) and data manipulation language (DML) T-SQL
  scripts for database objects in SQL Server running anywhere, Azure SQL
  Database, and Azure SQL Data Warehouse. You can save the generated
  T-SQL script to a .sql file or pipe it to standard nix utilities (for
  example, sed, awk, grep) for further transformations. You can edit the
  generated script or check it into source control and subsequently
  execute the script in your existing SQL database deployment processes
  and DevOps pipelines with standard multiplatform SQL command line
  tools such as sqlcmd.


Oracle does not record checkpoint information in tempfile. So, for the tempfile:

Oracle is able to start even if tempfile is missing. You will get a message in the alter log and you should recreate tempfile.
No backup is taken of tempfile so it's safe to loose it
There is no need to trick with any storage parameter for tempfile if resides on ephemeral storage.

About physical data structure are not tempfile, it is not safe to put them in the ephemeral storage. Oracle keep track of changes in the datafile even if you have disabled logging. Disabling logging will have only effect in the recoverability of the database, redo and overall performance. Does not permit you to put datafile on ephemeral storage. Opening the DB with a missing database (database does not take care of how important were data on it) will raise.

ORA-01116: error in opening database file %s
ORA-27041: unable to open file
ORA-01157: cannot identify/lock data file %s - see DBWR trace file
ORA-01119: error in creating database file '%s'

And you have to manually deal with the issue. Hence the use of ephemeral storage with normal datafile have to be avoided.


How to set SSRS parameter Default to uncheck specific options?

In the parameter preference pane, there's one tab for available, and one tab for default. You can pick completely different details for each set, including different datasets, one with the available options, and one selecting just the ones you want set as defaults.


check if linked server table column exists

You'll have to make a connection first using VBA and then check with sys.columns

SELECT COUNT(*)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('MyTable') AND c.name = 'ExpectedCol'

Or use COLUMNPROPERTY

That is, check the metadata separately trying to query the column


What is proper database design for a drupal table with an order field?

The problem is similar to this (SO) question:
How to swap values of two rows in mysql without violating unique constraint?

Unfortunately, the answer is the same. Due to how MySQL checks UNIQUE (and primary and foreign key) constraints, i.e. row by row and not at the end of statements or at the end of transactions, swapping or rearranging values that are part of unique constraints, cannot be done easily.

I would go with your 1st suggestion:

1. Update each row's order with some large number (i.e. 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).

This way you can have the minimal (2) statements required to do the update and any other connection that reads the table between the 2 statements will still read a consistent state of values.


Before jumping at one article you should do a little research, after a short Google search "windows storage pools vs raid" I found this article which goes more in-depth. As far as I can tell this new tech doesn't completely replace raid but could be considered an alternative to raid ONLY if you are using windows server 2012. It also may not make sense if you already have the hardware to support hardware raid as (I'm guessing) using this new storage mechanism will increase the cpu/memory loads on your server since it has to do the storage calculations in software. So I wouldn't say RAID is no longer relevant, it just has some healthy competition.


mysql 5.6 FEDERATED table and mysql.servers correlation

See manual

No data is stored on the local tables.

So if the original datatable has data the federated has too


SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database

After some more investigation i found that when a large log backup was required to be taken, the time taken produce the trn backup file was elapsing a timeout period of 5 minutes set by the lanmanserver service.

The error being reported was error 64 (error not found) which is actually quite a common issue apparently as described here; so even though the log backup was being created, the SQL engine couldn't verify that the backup had been successful.

In turn, the VLF's where not being set to 0, hench seeing the issues that were described in the Question.

thank you for your help guys, but i think the root of the issue was the error being reported (i didn't mention that as i could see the backup files and logshipping was still processing them, so i didn't think it was part of the same problem!)


Not able to access Oracle manager online. Port 5500 is closed

The problem was this part:

SQL> exec dbms_xdb.sethttpport(5500)
PL/SQL procedure successfully completed.

I configured 5500 on HTTP port but was trying to access it on HTTPS


SSMS: Restart the values on a IDENTITY field


How to know which DMVs relate to each other?

You could cheat and use Glen Berry's. The docs list the relationship cardinalities at the bottom for sys.dm_exec_requests, sys.dm_exec_connections, sys.dm_exec_cursors, sys.dm_db_session_space_usage.

Here's the map for 2012 that you're looking for. You'd just need to account for new DMVs Here is a mapping of system tables to system views. - scsimon


The equivalent in Oracle is user_tab_columns for tables owned by the current user or all_tab_columns for tables accessible to the current user.


SQL over TLS using Let's Encrypt on Raspberry pi

I'm a bit late replying here but I suspect the problem is that Let's Encrypt supplies certificates in PKCS #8 format, whereas MySQL needs them in PKCS #1 format. You will need to convert the key each time it is renewed. A hook along these lines should do the trick:

openssl rsa -in /etc/letsencrypt/live/example.com/privkey.pem -out /etc/mysql/privkey.pem && systemctl restart mysql


Maybe you want to limit the clients to those which present a given certificate, as described in Using client certificates:

  To require the client to supply a trusted certificate, place
  certificates of the certificate authorities (CAs) you trust in the
  file root.crt in the data directory, set the parameter ssl_ca_file in
  postgresql.conf to root.crt, and set the clientcert parameter to 1 on
  the appropriate hostssl line(s) in pg_hba.conf

On the other hand, sslmode=verify-full is a client-side feature. It benefits a client by ensuring that it connects to the intended server. It does not benefit a server as it's the server that is being checked.

Having a server being configured to "refuse to not being checked" doesn't seem to make much sense, as if a server would say "I don't trust myself".


What happens if a DC goes down in a Postgresql, Patroni, etcd multi DC setup

Let me just copy the answer and following remarks from the main Patroni developer and maintainer:

You got nearly everything correctly. If the Site1 is down, Site2 will not promote due to the lack of quorum.  There is no way to automatically recover from such a situation.

The only solution would be to run one etcd node somewhere else, say Site3. You don't have to run Postgres there.  Keep the nu

Please note that the Postgres master doesn't necessarily have to run on the same node where the etcd leader does. They are independent from each other.

In any case, with only two data centers it is sometimes better to have two independent etcd clusters on Site1 and Site2, and run a Patroni standby cluster on Site2. If the first site is down, you can manually promote the standby cluster.

Automatic promotion in this case is not possible, because Site2 will never be able to figure out the state of Site1.


Building a custom database in C with/without SQL?

You many want to look into using the Sphinx Search tools.

It allows you to create a MySQL instance that runs on port 9036 and only stores indexing info along with keys into real MySQL databases/tables.

There is a third party MySQL Storage Engine called SphinxSE.

Theoretically, the keys can you store in Sphinx do not have to be for MySQL. They could be for any DBMS. Sphinx is just a good key store that is built for FULLTEXT indexing.

Now, if you need to access the Sphinx DB using C/C++, then you need CodeBase.


Error installing Microsoft SQL server 2008 R2 with tools! Error code

Error code: -2068578303 translates into hex 0x84B40001 which corresponds to an error in the SQL Server setup command line.  You can likely find the answer to your problem at:

http://msdn.microsoft.com/en-us/library/ms144259%28SQL.100%29.aspx


Do databases make a delete and an insert when having to update rows?

Is this even true?

No, it is an implementation detail. A database may implement a suitable update in place if it so chooses.

  What are the benefits?

Splitting an update into a delete followed by an insert generally makes the implementation simpler. Potential side benefits include the ability to avoid transient key violations in a unique index, by suitably sorting the split delete/insert operations.

A split update may be a little slower and generate more log than a true in-place update (which is not always possible anyway).

As Kin noted in a comment, if you need an example (for SQL Server) see:

Are all updates split into delete-insert? by Gail Shaw; and
Do changes to index keys really do in-place updates? by Paul Randal

It is also related to implementing MVCC. In Wikipedia page about MVCC, it is mentioned:

  When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead mark the old data as obsolete and add the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else.

See also page 60 of PostgreSQL Internals pdf by Bruce Momjian (Postgres uses MVCC):  "UPDATE is effectively a DELETE and an INSERT."


mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (code)" when trying to connect

mysqldump -e -uNAME -pPWD -hHOST DATABASENAME > C:\winhostMySqlD.sql

I hope you gave this command for representational purpose, if you execute this command as it is, you will get an error. Below mentioned sample command for your reference, and possible reason for the connection error:
To backup database mydbname:
mysqldump -u username -p password -h localhost mydbname > /path/filename.sql

To backup all databases:
mysqldump -u username -p password -h localhost --all-databases > /path/filename.sql

-u username - like admin/root
-p password - like 'Admin@123'
-h hostname - where mysql server is running - like localhost/192.168.x.x (IP address)

Check my post for learn more about backup and restoration.
Some of the reasons for connection errors, listed down for your troubleshooting:

MySQL Server is not running on the server.
MySQL socket file is missing / deleted / different location.
Security Enhanced Linux is protecting mysqld process (Linux).
MySQL server is bound to single address (bind_address).
Firewall blocking MySQL port.
Access restricted to the server hosts.

Check my post for detailed troubleshooting steps for above mentioned reason and other possible causes.


Typically you would not restrict your invoice_line table to specifically products, instead create an exclusive subtype depending on what type each line is (product, fee, discount, tax, memo, etc.).
CREATE TABLE line_type
(
  line_type_cd  CHAR(1)      NOT NULL
 ,description   VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_line_type PRIMARY KEY (invoice_line_type_cd)
 ,CONSTRAINT AK_line_type UNIQUE (invoice_line_type_cd)
)
;

CREATE TABLE invoice_line
(
  invoice_id    INT           NOT NULL
 ,line_nbr      SMALLINT      NOT NULL
 ,line_type_cd  CHAR(1)       NOT NULL
 ,quantity      DECIMAL(9,2)  NOT NULL
 ,amount        DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_invoice_line_for_invoice FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id)
 ,CONSTRAINT FK_invoice_line_discriminated_by_line_type FOREIGN KEY (line_type_cd) REFERENCES line_type (line_type_cd)
 ,CONSTRAINT PK_invoice_line PRIMARY KEY (invoice_id, line_nbr)
)
;

CREATE TABLE invoice_product
(
  invoice_id        INT       NOT NULL
 ,product_line_nbr  SMALLINT  NOT NULL
 ,product_id        INT       NOT NULL
 ,order_id          UUID      NOT NULL
 ,CONSTRAINT FK_invoice_line_fulfills_order_line FOREIGN KEY (order_id, product_id) REFERENCES order_line (order_id, product_id)
 ,CONSTRAINT FK_invoice_product_is_invoice_line FOREIGN KEY (invoice_id, product_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, product_line_nbr)
)
;

CREATE TABLE invoice_fee
(
  invoice_id    INT       NOT NULL
 ,fee_line_nbr  SMALLINT  NOT NULL
 ,fee_cd        CHAR(6)   NOT NULL
 ,CONSTRAINT FK_invoice_line_charges_fee FOREIGN KEY (fee_cd) REFERENCES fee (fee_cd)
 ,CONSTRAINT FK_invoice_fee_is_invoice_line FOREIGN KEY (invoice_id, fee_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, fee_line_nbr)
)
;

CREATE TABLE invoice_tax
(
  invoice_id    INT       NOT NULL
 ,tax_line_nbr  SMALLINT  NOT NULL
 ,tax_auth_cd   CHAR(6)   NOT NULL
 ,CONSTRAINT FK_invoice_line_remitted_to_tax_authority FOREIGN KEY (tax_authority_cd) REFERENCES tax_authority (tax_authority_cd)
 ,CONSTRAINT FK_invoice_tax_is_invoice_line FOREIGN KEY (invoice_id, tax_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, tax_line_nbr)
)
;

The last step is to add a function/trigger to ensure the insert into each of the subtype tables corresponds to the appropriate line_type.
An invoice_product table isn't something to be avoided - you need a way to separate concerns/unique attributes while still associating the quantities/amounts to each invoice_line.
A few other observations:
There is a normalization error that will permit invalid order/customer combinations for an invoice.  So you'll need to either:

Restrict an invoice to one order - thus allowing one path to customer (inflexible).
Make customer_id part of the primary key of invoice and order and use the power of the composite key to enforce the relationship (most flexible).

The second option would look like this:
CREATE TABLE invoice
(
  customer_id  INT   NOT NULL
 ,invoice_id   INT   NOT NULL
 /* everything else */
 ,CONSTRAINT FK_invoice_for_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
 ,CONSTRAINT PK_invoice PRIMARY KEY (invoice_id, customer_id)
 ,CONSTRAINT AK_invoice UNIQUE (invoice_id)
)
;

CREATE TABLE order
(
  cust_id         INT          NOT NULL
 ,order_nbr       INT          NOT NULL
 ,cust_order_ref  VARCHAR(20)  NOT NULL
 /* everything else */
 ,CONSTRAINT FK_order_placed_by_customer FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
 ,CONSTRAINT PK_order PRIMARY KEY (cust_id, order_nbr)
 ,CONSTRAINT AK_order UNIQUE (cust_id, cust_order_ref)
)
;

At this point cust_id would migrate to your invoice_line table (and subtypes) and enforce only items will be billed to the customer that were placed by the customer  This also allows you to avoid the ugly UUID data type - much easier to reference a smaller composite key than break everything with a 16-byte row pointer:
CREATE TABLE invoice_line
(
  invoice_id    INT           NOT NULL
 ,cust_id       INT           NOT NULL
 ,line_nbr      SMALLINT      NOT NULL
 ,line_type_cd  CHAR(1)       NOT NULL
 ,quantity      DECIMAL(9,2)  NOT NULL
 ,amount        DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_invoice_line_for_invoice FOREIGN KEY (invoice_id, cust_id) REFERENCES invoice (invoice_id, cust_id)
 ,CONSTRAINT FK_invoice_line_discriminated_by_line_type FOREIGN KEY (line_type_cd) REFERENCES line_type (line_type_cd)
 ,CONSTRAINT PK_invoice_line PRIMARY KEY (invoice_id, cust_id, line_nbr)
)
;

CREATE TABLE invoice_product
(
  invoice_id        INT       NOT NULL
 ,cust_id           INT       NOT NULL
 ,product_line_nbr  SMALLINT  NOT NULL
 ,product_id        INT       NOT NULL
 ,order_nbr         INT       NOT NULL
 ,CONSTRAINT FK_invoice_line_fulfills_order_line FOREIGN KEY (cust_id, order_nbr, product_id) REFERENCES order_line (cust_id, order_nbr, product_id)
 ,CONSTRAINT FK_invoice_product_is_invoice_line FOREIGN KEY (invoice_id, cust_id, product_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, cust_id, product_line_nbr)
)
;

The appropriate choice will be dependent on your other requirements - if customers do not want multiple orders combined on a single invoice, the first option is perfectly acceptable and there are other ways to handle if multiple orders were fulfilled at the same time (you'd generally have a manifest or packing_list entity that could handle this).


Derived value not calculating as expected using combination of FK and Triggers (MySQL)

reviews_has_comments (rhc) exists because comments can be added to several other types of content.

If I understand correctly, that is not a justification for responses_has_comments table - you can have several tables with 1-to-many relationships with the same comments table. The only reason for introducing responses_has_comments would be if the same comment can be attached to more than one response, as well as more than one comment being attached to each response, ie it is a many-to-many relationship - I don't think that is what you are trying to achieve.

This does not answer your main question but may sidestep the problem in your updated trigger. Your comments trigger then becomes:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        UPDATE responses
        SET n_comments = n_comments - 1
        WHERE id_responses = @id_responses;
        -- rinse and repeat for all linked "commentable" table data...
    END;
$$


Indexing issues while partitioning with dbms_redefinition

Ok, minimal test case that reproduces the error, showing it's the intermediate table PK that is the problem:

SQL> CREATE TABLE SO33070_ORIGINAL
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  );

Table created.

SQL>
SQL> CREATE TABLE SO33070_NEW
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  )
  7  PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  8  (
  9    PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 10  );

Table created.

SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>FALSE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2026
ORA-06512: at line 5

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

OBJECT_TYPE  OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX        PHIL   SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110330" ON "PH
IL"."SO33070_NEW" ("SERIA

SQL>
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>

You have 2 options to fix this.

Option 1 - create the intermediate table without a primary key (best option!!):

SQL> CREATE TABLE SO33070_ORIGINAL
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE,
  5          PRIMARY KEY (SERIAL_ID)
  6  );

Table created.

SQL>
SQL> CREATE TABLE SO33070_NEW
  2  (
  3          SERIAL_ID NUMBER(15,0),
  4      INSERTED_TIME DATE DEFAULT SYSDATE
  5  )
  6  PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  7  (
  8    PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  9  );

Table created.

SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

no rows selected

SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>

Option 2 - simply tell DBMS_REDEFINITION to ignore the error (ignore_errors=>true).

It looks like there are some errors, but they don't matter as the PK constraint and index was created when the intermediate table was created:

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2  num_errors NUMBER;
  3  BEGIN
  4
  5  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
  6
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
  2  from dba_redefinition_errors;

OBJECT_TYPE  OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX        PHIL   SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110410" ON "PH
IL"."SO33070_NEW" ("SERIA

CONSTRAINT   PHIL   SO33070_ORIGINAL
ALTER TABLE "PHIL"."SO33070_NEW" ADD CONSTRAINT "TMP$$_
SYS_C00110410" PRIMARY KE

SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');

PL/SQL procedure successfully completed.

SQL>


Is there a way to access temporary tables of other sessions in postgres?

The lack of access to temporary tables in other sessions is not a matter of permissions, it's a technical limitation of the design. A PostgreSQL backend can't access temporary tables of another backend because none of the usual housekeeping to allow concurrent access is done for temporary tables.

In 9.2 you will want to use an UNLOGGED table instead; this can be visible from other sessions, but retains most of the performance benefits of a temporary table.


How does SQL bind rows in filestream-enabled table with their files

Just as caveat it is best not to disturb the physical files involved with FILESTREAM for a database.

Below are a few good links on the architecture:
Paul Randal White Paper - FILESTREAM Storage
Paul Randal Blog Post - FILESTREAM directory structure

As quoted from Paul's blog post above:

  The FILESTREAM file names are actually the log-sequence number from the database transaction log at the time the files were created. You can correlate these by looking at the data with DBCC PAGE, but first finding the allocated pages using sp_AllocationMetadata (see this blog post).

Note the stored procedure he mentions is an undocumented one that he provides some additional information to in the blog post he linked.


I think you can use CASE expression. Something like that:

SELECT Name,
  CASE Code WHEN 'Homeaddress' THEN Address ELSE NULL END AS Homeaddress,
  CASE Code WHEN 'Email' THEN Address ELSE NULL END AS Email,
  CASE Code WHEN 'homeemail' THEN Address ELSE NULL END AS homeemail
FROM yourtable


Possible to intercept and alter SQL Queries in the engine before it gets executed?

No, it is not possible to intercept and alter queries.

If somebody dangled millions of dollars in front of me to pull it off, I can imagine building a wire-compatible app that acted as if it was taking queries, then rewrite them and pass them off to another layer. However, that's not a small exercise, and it's left for the reader. This is the kind of thing GreenSQL (now Hexatier) was doing, sitting in between the apps and SQL Server, doing caching and auditing.


Is this product table schema good or worth editing?

After examining the type of database relationships (one-to-one, one-to-many, many-to-many) I made such a database structure. If something can be corrected here, correct me.


In SQL Server, How Does Parallelism Change Memory Grants?

Sup?

For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads.

Memory grant estimates are based on:

Number of rows (cardinality)
Size of rows (data size)
Number of concurrent memory consuming operators

If a parallel plan is chosen, there is some memory overhead to process parallel exchanges (distribute, redistribute, and gather streams), however their memory needs are still not calculated the same way.

Memory Consuming Operators

The most common operators that ask for memory are

Sorts
Hashes (joins, aggregates)
Optimized Nested Loops

Less common operators that require memory are inserts to column store indexes. These also differ in that memory grants are currently multiplied by DOP for them.

Memory needs for Sorts are typically much higher than for hashes. Sorts will ask for at least estimated size of data for a memory grant, since they need to sort all result columns by the ordering element(s). Hashes need memory to build a hash table, which does not include all selected columns.

Examples

If I run this query, intentionally hinted to DOP 1, it will ask for 166 MB of memory.

SELECT *
FROM
     (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
OPTION(MAXDOP 1);

If I run this query (again, DOP 1), the plan will change, and the memory grant will go up slightly.

SELECT *
FROM (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
JOIN (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);

There are two Sorts, and now a Hash Join. The memory grant bumps up a little bit to accommodate the hash build, but it does not double because the Sort operators cannot run concurrently.

If I change the query to force a nested loops join, the grant will double to deal with the concurrent Sorts.

SELECT *
FROM (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
INNER LOOP JOIN ( --Force the loop join
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);

The memory grant doubles because Nested Loop is not a blocking operator, and Hash Join is.

Size Of Data Matters

This query selects string data of different combinations. Depending on which columns I select, the size of the memory grant will go up.

The way size of data is calculated for variable string data is rows * 50% of the column's declared length. This is true for VARCHAR and NVARCHAR, though NVARCHAR columns are doubled since they store double-byte characters. This does change in some cases with the new CE, but details aren't documented.

Size of data also matters for hash operations, but not to the same degree that it does for Sorts.

SELECT *
FROM
     (
        SELECT TOP (1000)
                 u.Id          -- 166MB (INT)
               , u.DisplayName -- 300MB (NVARCHAR 40)
               , u.WebsiteUrl  -- 900MB (NVARCHAR 200)
               , u.Location    -- 1.2GB (NVARCHAR 100)
               , u.AboutMe     -- 9GB   (NVARCHAR MAX)
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
OPTION(MAXDOP 1);

But What About Parallelism?

If I run this query at different DOPs, the memory grant is not multiplied by DOP.

SELECT *
FROM (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
INNER HASH JOIN (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
ORDER BY u.Id, u2.Id -- Add an ORDER BY
OPTION(MAXDOP ?);

There are slight increases to deal with more parallel buffers per exchange operator, and perhaps there are internal reasons that the Sort and Hash builds require extra memory to deal with higher DOP, but it's clearly not a multiplying factor.


How do I restore 3 tables into a MySQL database from a single Windows dump file?

Unless some unusual options are used when it is invoked, the files produced by mysqldump contain the raw sql statements needed to drop and recreate your tables as they existed at the time the dump was done, uncompressed, and human-readable.  They can generally be manipulated with a text editor... although finding a text editor that works well with 20GB files is another matter.

But restoring an individual table essentially requires extracting the lines you want, and piping those statements into the server via the mysql CLI.  Of course, I would not recommend that you do this on your production server without testing elsewhere, first.

It is also possible that there are dependencies in your schema that may make restoring a single table much more difficult... such as foreign keys referencing the table.  You might have to modify the resulting SQL file to get the data back in, but this situation would exist even if you had a mysqldump file of the individual tables you're needing to restore.

But for the task at hand, the first two tools that came to mind are Perl and sed, both of which are free and available for Windows.  Either one should do what you need.

We can find the range of lines we're interested in by displaying the line numbers of strategically-placed SQL comments mysqldump adds to the file:

K:\backup>perl -n -e "print \"$. $_\" if (/^--\s+.*for\stable\s`/ || /^--.*Database/)" backup_file.sql

Output:

3 -- Host: localhost    Database:
19 -- Current Database: `mediawiki`
27 -- Table structure for table `archive`
56 -- Dumping data for table `archive`
       ...
272 -- Table structure for table `image`
302 -- Dumping data for table `image`
312 -- Table structure for table `imagelinks`
327 -- Dumping data for table `imagelinks`
       ...

The numbers at the left are line numbers in the dump file (we just now calculated them).  So, if we wanted to restore mediawiki.image we need lines 272 through 301 to drop and re-create the table ... and 302 through 311 to restore the data.

K:\backup>perl -n -e "print $_ if ($. >= 272 && $. <= 311)" backup_file.sql > my_new_file.sql

Examine my_new_file.sql's content for sanity:

You should find, in order: DROP TABLE, CREATE TABLE, LOCK TABLES your_table WRITE, INSERT INTO your_table VALUES (all_your_data), and UNLOCK TABLES.

Once you're satisfied with the content of the file, you can pipe it to the server via the command line interface:

K:\backup>mysql [normal CLI options here] target_database < my_new_file.sql

If you wanted to use sed instead of Perl, count the lines with:

K:\backup>sed = backup_file.sql | sed "N;s/\n/\t/" | sed -n "/-- Table structure for/p;/-- Dumping data for/p;/-- Current Database/p"

(My sed skills are a little soft.  This could probably be written better and/or to run faster, but it does work as written.)

Then extract the lines you want with:

K:\backup>sed -n 272,311p backup_file.sql > my_new_file.sql

Of course, these work on Linux, too, but the outer double-quotes would be single quotes, and the inner double-quotes in the first Perl example wouldn't be backslash-escaped.

If you have sed or Perl installed, you may also need to add them to your path, or replace "sed" or "perl" in the above examples with fully-qualified pathnames (with the quotes as shown).

"C:\Program Files (x86)\GnuWin32\bin\sed.exe"
"C:\Perl64\bin\perl.exe"


mysql (mariadb) won't start after power outage (MacOS)

it s clearly telling you that it looks for INNOdb driver and can not find it.
that means that your filesystem can not read the needed files. try to repair the installation, if they are missing or look for the reason.


Zabbix MariaDB Copy item data to another item

After some digging, I managed to successfully copy history and trends from one item to another.
The sort of syntax I used is this:
History table:
INSERT INTO history (`itemid`, `clock`, `value`, `ns`)
SELECT '<new_itemid>', `clock`, `value`, `ns`
FROM history
WHERE itemid = <old_itemid>;

Trends table:
INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`)
SELECT '<new_itemid>', `clock`, `num`, `value_min`, `value_avg`, `value_max`
FROM trends
WHERE itemid = <old_itemid>;

The secret was with INSERT INTO and the SELECT clause that added the literal itemid.
The actual <new_itemid> was inserted into every new row.
The result:
MariaDB [zabbix]> INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`) SELECT '32041', `clock`, `num`, `value_min`, `value_avg`, `value_max` FROM trends WHERE itemid = 30886;
Query OK, 5227 rows affected (0.14 sec)
Records: 5227 Duplicates: 0 Warnings: 0

MariaDB [zabbix]> INSERT INTO history (`itemid`, `clock`, `value`, `ns`) SELECT '32041', `clock`, `value`, `ns` FROM history WHERE itemid = 30886;
Query OK, 2008 rows affected (0.10 sec)
Records: 2008 Duplicates: 0 Warnings: 0

I bet there's a better way, but this is all I could come up with for now.


MySQL master to master race conditions, do I need to use transactions/locks for this code?

In short - yes, you will have to engineer your application in a way that makes it resistant to race conditions. Transactions won't help you with this.


Sqlite query slow whilst it should be fast

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes.

You have to create an R-tree index for your coordinates.


How do you generate a build script for all views

Among other things, have a look at the object_definition() function and the sys.objects catalog view. See them in action here.

But yea... you should probably just use your SQLCompare tool, most RedGate tooling is the gold standard for the functionality if you've got access to it.


You can certainly avoid a conditional here, but you also do not need either CEILING or conversion to numeric to get the total number of pages if both @totalrec and @pagesize are integers (and it seems safe to assume that they are). The following formula will give you the number of pages to accommodate @totalrec rows given the page size of @pagesize:

(@totalrec - 1) / @pagesize + 1 AS TotalPages


SELECT Student_Id,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Name' ) AS Name,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Age' ) AS Age,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Sex' ) AS Sex,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Area' ) AS Area
    FROM ( SELECT DISTINCT Student_Id FROM t ) AS u
    ORDER BY Student_Id;

Performance would benefit from

INDEX(Student_Id, Question, Answer)


"show create table" output with out "show"?

If you want something that's quick-and-dirty, may you should consider doing this:
STEP 01) Use mysqldump
mysqldump --no-data --all-databases > MySQLSchema.sql

STEP 02) Parse the text
Every CREATE TABLE would mark the next databse
Every USE dbname marks the name of the next database
Have each CREATE TABLE description in a text file
STEP 03) Do a LOAD DATA INFILE of each of the parsed files into a table of your choice (such as mydb.mytables)
STEP 04) Query for the structures
SELECT table_desc FROM mydb.mytables WHERE dbname="...' AND tbname='...';

Your your would look something like this:
CREATE TABLE mytables
(
    dbname VARCHAR(64) NOT NULL,
    tbname VARCHAR(64) NOT NULL,
    table_desc TEXT DEFAULT NULL,
    primary key (dbname,tbname)
);

Your mission is to parse the mysqldump I just mentioned and get each table description loaded into this table, along with the database anem table name.
Once you construct such a parsing program, you could do this process every hour and update your dynamic documentation.
Give it a Try !!!
UPDATE 2012-04-01 00:39 EDT
Here is modified suggestion: Given the mysqldump suggestion I made, your should load tMySQL Schema attained from the mysqldump into another DB server that contains no data. Set it up as a replication slave using replicate-do-db=mysql. That way, no data will collect in the slave. You can use this slave as the source of the mysqldumping of the DB schema. This separates the data from the schema. You can use your documentation system fetch the schema from the slave.


DB2, How to save all stored procedures as files

you can use the db2look clp command with the -e option, altough it does not have an option to only export procedures, so unless they are in a schema without other objects you will also get e.g. TABLE, VIEW and other DDL mixed in the output.
$ db2look -d BP -e -cor -nofed -z PAUL | grep -A 999 'DDL Statements for Stored Procedures'
-- DDL Statements for Stored Procedures
---------------------------------

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
SET CURRENT SCHEMA = "PAUL    ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","PAUL";

CREATE OR REPLACE PROCEDURE P ()
BEGIN
          RETURN   /* this */   2  ; -- comment
    /* this is a
    comment */
END
;

-------------------------------
-- DDL Statements for Triggers
-------------------------------

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
...


String that contains letters representing a negative number

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome.

DECLARE @x TABLE(col VARCHAR(15));

INSERT @x(col) VALUES
('00000000014545p'),('00000000012645n'),
('00000000012345p'),('000000004512345');

;WITH x AS
(
  SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END
  FROM @x
)
SELECT SUM(CONVERT(INT, LEFT(col,
  CASE switch WHEN 1 THEN LEN(col)-1 ELSE LEN(col) END))
   * CASE switch WHEN 1 THEN -1 ELSE 1 END)
FROM x
-- WHERE ...;


Generic way to determine if the table needs index (PostgreSQL)

You're missing the point of @a_horse_with_no_name's comments. You create indexes to satisfy queries but whether or not they're chosen is a result of statistics. Whether or not choosing them (or not) is correct action is a factor of the accuracy of statistics to model the workload, environment, and indexes.

Statistics take into account things like selectivity to determine block/page fetches. To determine how many blocks you must retrieve you need to have a value for table size (rows), row width (bytes per row), page size, and page fragmentation.

The point is it's complex, to make matters even more complex most of the values are configurable or can be tuned by either the distro, the user, or the person who compiles the database.

So you're working at the problem from the wrong angle, the right question isn't

  Are all of those factors correct to create an index?

But instead,

  For this given query, can I use an index?

That is to say, go ahead and create the index. Then if the index is not used in the query and you think it should be, you can come back here and paste the query, the schema, the result of EXPLAIN ANALYZE, and  your configuration parameters and hope someone will break it down for you as to why your index which could have been used was not chosen.

As an aside, one of the most powerful tools to determine that is set enable_seq_scan = 0;. By disabling sequential table scan, you can usually  force the planner to use an index. If it uses your index and it's slower, you know that PostgreSQL made the right plan. That's often the case. The planner is smarter than 98% of the users; and, this method is the "check that it's powered on" of query optimization.


SQL Server Profiler - How to filter trace to only display inserts, updates and deletes to one database?

You can narrow down the list by specifying DatabaseName or DatabaseID in Column Filters:

Note however that you need to check the box "show all columns" first.

As for inserts, updates and deletes you can filter by text column at the same place:


How to access SQL Server 2012 on EC2 server from my ASP.NET website published on the same EC2 server?

Change the data source parameter of the connection string to "localhost".  When everything is running on the same machine there's no need to specify the actual host name.  If you do need to specify the host name, when SQL Server is running on one server and the database is on another server for example, only specify the name of the server.  In your case the connection string would reference the internal name of the server.  In that case the connection string would look something like this.

connectionString="Data Source=ec2-46-137-228-121;Initial Catalog=MyDb;User ID=something;Password=something"

What ever the value in the data source you should be able to ping that name to make it work.


Migrating data from 11G Linux to 19C on Windows

Always exclude them. They contain version and instance-specific metadata that should not be migrated. When performing this kind of migration (porting applications to new DB versions), only ever export/import your application schemas and user accounts. Never do a "full" export/import.
If you have already overwritten some data dictionary objects and experienced errors, you may need to validate the state of your dictionary (see here: https://oracle-base.com/articles/misc/hcheck-sql-check-known-data-dictionary-problems) and consider a restore from backup to a point prior to your import.


If you have foreign keys to this table, add the option ON DELETE CASCADE.  Then when you delete a row in a parent table all the rows in the child tables that refference that row will be deleted as well.


Lots going on here. Why are you storing numbers in a varchar column? Why do you sometimes have a number that's only kind of a number? You can fix some of these complications of course by cleaning the data before inserting it and using the right data type. In the meantime, something like this is ugly but gets the job done.

DECLARE @encounters table(diag_1 varchar(50));

INSERT @encounters(diag_1) VALUES('240'),('250.8'),('V25'),('V26.2'),('Vurt'),('V303V2');

;WITH x AS
(
  SELECT diag_1,
    adj = CASE -- first, remove the leading V where it exists:
      WHEN diag_1 LIKE 'V%' THEN SUBSTRING(diag_1,2,255)
      ELSE diag_1 END
  FROM @encounters
)
SELECT diag_1, -- now, try to cast to int the remainder, ending right before the decimal
  adjusted = TRY_CONVERT(int,LEFT(adj, COALESCE(NULLIF(CHARINDEX('.',adj)-1,-1),255)))
FROM x;

Results:

diag_1   adjusted
------   --------
240           240
250.8         250
V25            25
V26.2          26
Vurt         NULL
V303V2       NULL


Postgresql query XMLTable with default namespace

As documented in the manual "A default namespace specification is not currently supported"

You need to give the namespace an alias and use that in the XPath expressions:

SELECT xmltable.*
FROM xmldata,
     XMLTABLE(
        xmlnamespaces('http://x.y.z' as x),
        '//x:ROWS/x:ROW'
        PASSING data
        COLUMNS
          id int PATH '@id',
          ordinality FOR ORDINALITY,
          "x:COUNTRY_NAME" text,
          country_id text PATH 'x:COUNTRY_ID',
          size_sq_km float PATH 'x:SIZE[@unit = "sq_km"]',
          size_other text PATH
                     'concat(x:SIZE[@unit!="sq_km"], " ", x:SIZE[@unit!="sq_km"]/@unit)',
          premier_name text PATH 'x:PREMIER_NAME' DEFAULT 'not specified'
     ) ;

Online example


Mysqld process not showing all options on command line in CentOs 7

You shouldn't be relying on the OS processlist to display mysqld options in the events other versions of the OS do not display them (as your questions points out)

What other methods can you use that are OS-independent ???

METHOD #1

If you enabled show_compatibility_56, then login to MySQL and run

SELECT * FROM information_schema.global_variables;

METHOD #2

If you did not enable show_compatibility_56, then login to MySQL and run

SELECT * FROM performance_schema.global_variables;

METHOD #3

From the OS command line, use mysqladmin utility with variables command

mysqladmin -h127.0.0.1 -P3306 --protocol=tcp -uroot -p variables

GIVE IT A TRY !!!


sp_Blitz Findings: SQL Server Last Restart is an hour before Server Last Restart

I would 'suggest' that when the server was restarted (and Sql Server was also restarted) back on February 11, 2017, the current Windows time, which was Standard time and not Daylight Saving time, was captured as the Creation Date of TEMPDB (and that is what SP_Blitz uses to determine Sql Server restart time).

Now, you are running SP_Blitz AFTER the change to Daylight Saving time and sys.dm_os_sys_info (which is used by SP_Blitz to report Server Last Restart and appears to be Daylight Saving time aware) is affected by the time change, thus making it 'look' like the server started AFTER Sql Server.


If the strings are all the same, and the column is called c1 in a table called t1 then SUBSTRING_INDEX() might get you there.  Don't update your table until you've verified it with SELECT.

Whitespace added for clarity.

SELECT CONCAT(
  SUBSTRING_INDEX(c1,'&',1),
  '"',
  SUBSTRING_INDEX(c1,'"',-3)
) FROM t1;

SELECT the substring left of the first '&' counting from the left, a literal " and then the substring right of the third " counting from the right.

The only caveat is that the link format you've shown seems wrong.  The first "&" seems like it should have been "?" instead, and you should be clipping it at the "?" which marks the start of the query string in a URL.  Perhaps youtube was doing something nonstandard with URLs to work around plugin or browser issues.


Stored procedure inserts 2 columns into a table with 3 columns that are not null, with no defaults on the table

Someone else figured it out. In production, there is a trigger on the view that was not in place in testing.

2 of us didn't think to look for a trigger on a view. Mystery solved.


How to exclude the entire result of a Grouped row based on a value of any of the rows it contains

You can use something like this:
Select Class, MAX(Item)
FROM myTable
WHERE class not in (
         select class from #demo where item  = 3
                    )
GROUP BY Class


It's the transaction isolation that guarantees consistent results.

In your example, if you are concerned about possible changes from other threads between the first and the second query, you should enclose your queries in a single transaction: this is implicit if you have autocommit disabled, otherwise can be explicitly declared:

START TRANSACTION;
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;
COMMIT;

But it must also be said that, in many real use cases (like a counter for visits on a web page), having a result which is consistent with the previous increment is not so important.

For a deep explanation about possible transaction isolation levels please refer to: http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html


Galera cluster node fails with error. Brand new cluster + master / node. Access denied or can't connect

It doesn't sound like the problem is related to Galera. Maybe the debian-sys-maint@localhost user had already been created with the unix_socket as the defined authentication plugin before you did the GRANT for it? And the error message is pretty clear on what is the issue with that: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded.

So, you can either recreate the debian-sys-maint@localhost user with the default authentication plugin - mysql_native_password (option 1), or you can install the unix_socket plugin (option 2):

Option 1

DROP USER debian-sys-maint@localhost;
CREATE USER debian-sys-maint@localhost IDENTIFIED BY 'your password'; `
GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost WITH GRANT OPTION;

Option 2

INSTALL PLUGIN unix_socket SONAME 'auth_socket';

The advantage of option 2 is that you don't need a password for the user, assuming debian-sys-maint is the name of your OS user. MariaDB will use that OS username as the MariaDB username.


Is it safe to let application administrators look at v$session, v$process, and v$sql?

"Safe" is relative and involves a tradeoff between access and privileges and application and data security.

The factors that you would night want to consider are:

have the app admins had this access before?
Are the app admins employees or contractors?
what do they say they need it for and are there other ways to accomplish this?
do you have a development stack where they could have access?
who "owns" the database or applications?
Does each admin take care of one app and there is a
higher corporate owner? If this is the case a brief to the corporate owner should be your starting point.

My opinion is that unless there are performance or other application defects that are only seen on the production stack then access should not be granted on production. A view of the v$ views you mention on development should be fine.

Security procedures vary wildly from company to company and factors that are external to Oracle security are more often than not the deciding factors.

In short, only give access when you are satisfied that there is a real documented need that is supported by management.

In answer to the OP's question of what can you do with v$session I didn't know this till I looked for it but here is an example of what you can do with v$session.
That's the thing about security, it's not what you can imagine people can do with information that is dangerous.  It's what you don't know about.  To a point, less access to interesting system information is more security.


select substr(
        substring('SAPCC_XX_ZZZ_Bit1T1Class_XXX_201506235T335656_XXX_XX_XXXXXX'
                  from
                  '_[0-9]+T[0-9]+_'
                 ), 2, 8
             )::date


How SQL Server determines the data transfer rate

The ASYNC_NETWORK_IO wait type indicates that SQL Server has finished doing the work it has been asked to do and is waiting for the consuming application to confirm that it has received all of the data.

This is normally due to the application consuming the data in an inefficient manner. The classic is if the application is processing the data row by row. Another possibility is that the server, that the application is running on, is under performing and causing the application to slow.

With that said it is possible the network is contributing to the problem. A ping from the application server to the SQL Server will show the time it takes to get information across the pipe. If this is near to your wait time then it may indicate your pipe is not fast enough.

  The issue is because sometimes tables are loaded fast, but sometimes
  one or two tables are loading very slow. Looks like the data transfer
  sometimes is really bad for those tables, transfering at 50 rows/s,
  instead of 2,000 row/s (should be normal).

At a guess, this sounds like the application is processing the data row by row.  The fact that the slow processing is intermittent I would say that it's very unlikely to be the network.


This solve my problem

    SELECT conrelid::regclass AS table_from
      ,conname
      ,pg_get_constraintdef(c.oid)
FROM   pg_constraint c
JOIN   pg_namespace n ON n.oid = c.connamespace
WHERE  contype IN ('f', 'p ')
AND    n.nspname = 'public' -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;


You can use a PostgreSQL Aggregate Function to form a JSON array from the set of subquery results.

Aggregate functions compute a single result from a set of input values.

Also, the function json_build_object expects key value pairs, but you can deal with that by expressly providing the column name as a string for the key.

For example, given this setup:

CREATE TABLE zone (
    id serial PRIMARY KEY,
    name varchar(150) NOT NULL,
    nb_stats integer NOT NULL,
    occupation integer NOT NULL
);

INSERT INTO zone VALUES (1, 'a', 136, 0);
INSERT INTO zone VALUES (2, 'b', 145, 1);
INSERT INTO zone VALUES (3, 'c', 120, 0);

And this query:

select json_build_object(
    'type', 'FeatureCollection', 'features',
    (select json_agg(p1) from (select 'feature' as type,
        json_build_object('id', id, 'name', name, 'nb_stats', nb_stats,
            'occupation', occupation) as properties
        from zone) p1));

You get this result:

=# \i query.sql
                                                              json_build_object
---------------------------------------------------------------------------------------------------------------------------------------------
 {"type" : "FeatureCollection", "features" : [{"type":"feature","properties":{"id" : 1, "name" : "a", "nb_stats" : 136, "occupation" : 0}}, +
  {"type":"feature","properties":{"id" : 2, "name" : "b", "nb_stats" : 145, "occupation" : 1}},                                             +
  {"type":"feature","properties":{"id" : 3, "name" : "c", "nb_stats" : 120, "occupation" : 0}}]}

Rows are joined by newlines.  If you need to get rid of the newlines, you can use the regexp_replace() function with the pattern E'\n' and the 'g' global modifier.  But json is an actual type in PostgreSQL, and regexp_replace() expects text type input.  So to use that function you'll need to cast the json to text, then cast the newline-free resulting text back to json, like so:

select json_build_object(
    'type', 'FeatureCollection', 'features',
    (select regexp_replace(json_agg(p1)::text, E'\n', '', 'g')::json from (select 'feature' as type,
        json_build_object('id', id, 'name', name, 'nb_stats', nb_stats,
            'occupation', occupation) as properties
        from zone) p1));

Alternatively, you can solve the problem programmatically by writing a script that connects to the database, selects rows from the zone table, and then marshals them into JSON.

For example, here's a simple Go script that does that:

package main

import(
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/lib/pq"
)

type ZoneRow struct {
    Id          int
    Name        string
    NbStats     int
    Occupation  int
}

type Feature struct {
    Type        string
    Properties  ZoneRow
}

type Result struct {
    Type        string
    Features    []Feature
}

func (res *Result) AddZoneRow(id int, name string, nbStats int, occupation int) {
    newZoneRow := Feature{`Feature`, ZoneRow{id, name, nbStats, occupation}}
    res.Features = append(res.Features, newZoneRow)
}

func main() {
    db, err := sql.Open(`postgres`,
        `host=/path/to/postgresql/ user=me dbname=mine`)
    if err != nil {
        fmt.Println(err)
    } else {
        rows, err := db.Query(`SELECT * FROM zone`)
        defer rows.Close()
        if err != nil {
            fmt.Println(err)
        } else {
            res := new(Result)
            res.Type = `FeatureCollection`
            for rows.Next() {
                var id int
                var name string
                var nbStats int
                var occupation int
                err = rows.Scan(&id, &name, &nbStats, &occupation)
                if err != nil {
                    fmt.Println(err)
                } else {
                    res.AddZoneRow(id, name, nbStats, occupation)
                }
            }
            err = rows.Err()
            if err != nil {
                fmt.Println(err)
            } else {
                empty := ``
                fourSpaces := `    `
                b, err := json.MarshalIndent(res,empty,fourSpaces)
                if err != nil {
                    fmt.Println(err)
                } else {
                    fmt.Println(string(b))
                }
            }
        }
    }
}

Which, given the same table and row setup above, prints this output:

{
    "Type": "FeatureCollection",
    "Features": [
        {
            "Type": "Feature",
            "Properties": {
                "Id": 1,
                "Name": "a",
                "NbStats": 136,
                "Occupation": 0
            }
        },
        {
            "Type": "Feature",
            "Properties": {
                "Id": 2,
                "Name": "b",
                "NbStats": 145,
                "Occupation": 1
            }
        },
        {
            "Type": "Feature",
            "Properties": {
                "Id": 3,
                "Name": "c",
                "NbStats": 120,
                "Occupation": 0
            }
        }
    ]
}


Where can I check my permissions as SQL Server user?

You're asking the wrong question.  Instead of wanting to mimic the permissions of another server principal, you should be granted the permissions and membership that you need to do in order to fulfill your job requirements (that goes for server logins that applications use as well).

So in other words, instead of copying the permissions from another login, just find out what permissions you need to do your task (sounds like a RESTORE).  Reference:  RESTORE:

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

  RESTORE permissions are given to roles in which membership information
  is always readily available to the server. Because fixed database role
  membership can be checked only when the database is accessible and
  undamaged, which is not always the case when RESTORE is executed,
  members of the db_owner fixed database role do not have RESTORE
  permissions.


While Percona XtraDB Cluster is great for Multimaster Replication and InnoDB, it is not good for every type of workload. Bill Karwin states MySQL's Async Replication could be better due to workload : What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

If you are still looking for synchronous replication, you could look into using DRBD with UCARP or Linux Heartbeat for DB VIP and Failover Management.

Under that setup, the Master (aka DRBD Primary) has a data volume mounted on DRBD, MySQL running, and a ucarp mechanism for automatic failover.

The Slave (aka DRBD Secondary) does not have the data volume mount. MySQL is not running. DRBD is doing everything in terms of having a Block Device synchronized with the DRBD Primary.

DRBD is best used when having failover set up withing a single data center. DRBD should never be setup to with DRBD Primary and Secondary in two different data center due to synchronization latency. I have discussed this before in these posts

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Mar 26, 2012 : 3 Databases, Best Replication Setup?
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication

I recommended this for doing PostgreSQL with DRBD : PostgreSQL Failover - What tools should I use?

The drawback in using MySQL/DRBD for High Availability is that fact there is only one instance of MySQL running. The benefit i shaving a full copy of the data on disk. Even a hard failover will have InnoDB crash recover bring back uncommitted data that is written in the double write buffer (inside ibdata1).

If you want it all, use DRBD Clusters inside a data center but have DRBD Primary in one data data center using MySQL Replication in Circular Fashion with a DRBD Primary in another data center.


How to retrieve granted privileges without access to the database “mysql”?

It seems that using SHOW GRANTS did work in this case:

SHOW GRANTS FOR CURRENT_USER;

Although the manual states that you still need to have access to the mysql database:

  SHOW GRANTS requires the SELECT privilege for the mysql database.

And accessing mysql.user directly actually didn’t work, probably because it does also contain further sensitive information like the passwords of other users:

mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'user'@'host' for table 'user'


How to Recover My Oracle 9i Database From Just The Datafiles

Seeing as you have stated that the original data files look like they're untouched, all you need to do is recreate the service with oradim, making sure you're using the 9i ORACLE_HOME service and tools.


Create view with dynamically selected tables - is it possible?

You cannot do that with a view, because a view is parsed when it is created, and the parse tree is stored. That will lead to an error if a table used in the view does not exist.

What you could do is to write a table function that searches for tables, constructs a query string for each and uses RETURN QUERY EXECUTE to add the results to the output. That would be equivalent to UNION ALL. If you need UNION, you'd use SELECT DISTINCT to query the table function.


Do you have a backup and restore disaster recovery solution in place?  If so, restore the database from the most recent backup.

Assuming you don't have a backup, you may be able to use CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, (see the Microsoft Docs page for details)

The general syntax for the command is:

CREATE DATABASE <db-name>
ON (NAME = '<logical-name>', FILENAME = '<source-to-mdf>')
FOR ATTACH_REBUILD_LOG;

Be aware that if the mdf file is corrupt, you may lose everything.  Ensure you keep a copy of the mdf prior to attempting to attach it in this way.

An example of creating a database, setting it to "emergency" mode, detaching it, then re-attaching it without an existing log file:

CREATE DATABASE AttachTest
ON (NAME = 'sys', FILENAME = 'C:\temp\AttachTest.mdf')
LOG ON (NAME = 'log', FILENAME = 'C:\temp\AttachTest.ldf');

ALTER DATABASE AttachTest SET EMERGENCY;

Detach the database:

EXEC sys.sp_detach_db @dbname = 'AttachTest';

Delete the log file from the filesystem:

EXEC sys.xp_cmdshell 'del "C:\temp\AttachTest.ldf"';

Attach the database, recreating the missing log file:

CREATE DATABASE AttachTest
ON (NAME = 'sys', FILENAME = 'C:\temp\AttachTest.mdf')
FOR ATTACH_REBUILD_LOG

You'll see this message:

  File activation failure. The physical file name "C:\temp\AttachTest.ldf" may be incorrect.
  New log file 'C:\somepath\AttachTest_log.ldf' was created.

Cleanup the test database:

DROP DATABASE AttachTest;


SQL join multiple "id" columns from a single row to another table

You could use a union. Fiddle

select * from (
  select t.teamID, a1.firstName, a1.LastName, 1 as AthleteNumber from teams t
  inner join athletes a1 on a1.athleteID = t.athleteID_1
  union
  select t.teamID, a2.firstName, a2.LastName, 2 as AthleteNumber from teams t
  inner join athletes a2 on a2.athleteID = t.athleteID_2
  union
  select t.teamID, a3.firstName, a3.LastName, 3 as AthleteNumber from teams t
  inner join athletes a3 on a3.athleteID = t.athleteID_3
  union
  select t.teamID, a4.firstName, a4.LastName, 4 as AthleteNumber from teams t
  inner join athletes a4 on a4.athleteID = t.athleteID_4
) s
order by teamID, AthleteNumber
;


Connection issues with Azure DB from SSMS

Let start by making sure you are using the current IP of your Azure SQL Database server. Let's ping the name of the Azure SQL server. The ping will fail but it should also return the current IP of the SQL Azure Server if DNS resolution is working.

C:\>ping <myserver>.database.windows.net

Pinging data.sn1-1.database.windows.net [65.55.74.144] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Ping statistics for 65.55.74.144:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)

Now try to telnet to port 1433 using the IP address returned.

C:\>telnet 65.55.74.144 1433

Connecting To 65.55.74.144...

If Telnet is successful, the window will change to a completely blank screen. Try connecting with that IP using SSMS again. If telenet is not successful, try tracert to identify at what step it fails to reach the Azure SQL Database server.

C:\>tracert 65.55.74.144

Tracing route to 65.55.74.144 over a maximum of 30 hops
1 2 ms 1 ms 1 ms 192.168.0.1
2 6 ms 5 ms 5 ms 10.33.34.50
3 5 ms 4 ms 5 ms 4-1-753.DLLSTX-LCR-07.verizon-gni.net [130.81.107.26]
4 8 ms 5 ms 5 ms so-6-2-0-0.DFW01-BB-RTR1.verizon-gni.net [130.81.28.208]
5 44 ms 43 ms 44 ms so-11-0-0-0.LCC1-RES-BB-RTR1-RE1.verizon-gni.net [130.81.17.40]
6 49 ms 44 ms 44 ms so-6-0-0-0.ASH-PEER-RTR1-re1.verizon-gni.net [130.81.10.90]


You need to use the DAYOFWEEK function

If the End of the Week in Saturday, any date can be morphed into a Saturday.

For example, this computes the upcoming Saturday

SELECT DATE(NOW() + INTERVAL (7 - DAYOFWEEK(NOW())) DAY);

This computes the upcoming Sunday (you must use WEEKDAY function instead)

SELECT DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY);

For a table called mytable with a Date Column called theDate, your summation would be

Saturday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (7 - DAYOFWEEK(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Sunday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (6 - WEEKDAY(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Give it a Try !!!

CAVEAT if you want any other end of week other than Saturday and Sunday, I wrote an insane algorithm back in Sep 22, 2011 for computing any week starting and ending whatever day.


Postgresql: newly created database does not exist

Ok.. After some investigation I found where was mistake. So after adding new database it is required to edit pgbouncer.ini file and manualy add this database in [databases] section. Example:

We have followig databases:

postgres
example1
example2

Than pgbouncer.ini should contain

[databases]
postgres = host=localhost dbname=postgres
example1 = host=localhost dbname=example1
example2 = host=localhost dbname=example2


Return values at more/less specific time points

I suggest a LATERAL subquery, best with a LEFT [OUTER] JOIN to preserve all input rows (displaying a NULL value in case there should be no match at all):

SELECT v.reference_ts, t.most_recent_x
FROM  (
   VALUES
      (timestamptz '2018-03-13 11:41:47.167973+00') -- type cast in first row
    , ('2018-03-13 11:41:47.198564+00')
    , ('2018-03-13 11:41:47.555668+00')
    , ('2018-03-13 11:41:57.766888+00')
   ) AS v(reference_ts)
LEFT JOIN LATERAL (
   SELECT x AS most_recent_x
   FROM   xs
   WHERE  ts <= v.reference_ts  -- ts is unique (?!); see below
   ORDER  BY ts DESC
   LIMIT  1
   ) AS t ON true;

A VALUES expression is one way to put your 100 timestamps into a derived table we can work with. The free-standing VALUES expression may require an explicit type cast in the first row. See:

Casting NULL type when updating multiple rows

There are others ways to pass your values, like an array (or an array literal cast to timestamptz[]) and unnest() in the query:

Optimizing a Postgres query with a large IN

About the LATERAL subquery:

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

Don't forget the ORDER BY or you get arbitrary results - like @a1ex07 commented.

If ts is not unique, it's undefined which of the duplicates is returned, unless you add more ORDER BY items to make it deterministic. Like: ORDER BY ts DESC, x DESC to get the row with the biggest x from any set of peers. And make the index match.

An index on (ts) (like you mentioned) works wonders on performance.
Or, ideally, on (ts DESC, x) if preconditions for index-only scans are met.

Does PostgreSQL use an index-only scan in this JOIN?

dbfiddle here


How to TRUNCATE table if the table doesn't exists

It depends on how you are executing those queries:

If you are loading an SQL file, execute then with --force, and errors will be ignored (all errors! So if you want to catch other possible issues, that is going to be a problem)
If run from an application, you can execute each command individually and only ignore on truncate for the specific table not found error
With some sql logic, you can check for table existence on information_schema.tables, although you may need to create a procedure just for that (raw command line doesn't allow for programming logic directly).

I think implementing that outside of SQL would be the preferred method (there is no "good" dynamic SQL execution on MySQL (other than tricks with prepared statements). E.g. a bash one liner (no error or escaping handling):

mysql -BN -e "SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
              FROM information_schema.tables
              WHERE table_schema in ('schemas', 'to', 'process')
              AND table_name like '\_%\_tmp'"
| while read table; do mysql -e "TRUNCATE TABLE $table"; done


How do I load just the data from SQL Server 2005 backup into a 2008 R2 database?

Is your new structure different of the structure in SQL Server 2005? You may restore the backup entirely, there is no need to create first the structure and then load the data.


postgresql: get last/newest value for each group of rows

This is a classic case where using the ROW_NUMBER() window function can be used to get the latest row within a grouping (PARTITION) like so:
WITH CTE_Notifications_Sorted AS
(
    SELECT notification_id, group_key, associated, timestamp,
    COUNT(notification_id) OVER (PARTITION BY group_key) AS notification_id_count,
ROW_NUMBER() OVER (PARTITION BY group_key ORDER BY notification_id DESC) AS PartitionSortId -- Generates a unique ID for each row within the grouping (partition) of group_key ordered by the notification_id descending
    FROM data.notifications
)

SELECT group_key, notification_id AS notification_id_max, notification_id_count AS total, associated, timestamp
FROM CTE_Notifications_Sorted
WHERE PartitionSortId = 1 -- Filter out everything but the latest row of each group_key partition

By using the COUNT() function in a window function as well in the above CTE, you're able to remove your GROUP BY clause in the final SELECT.
Now that you have the associated column for the correct row, if you need to parse out a specific value you can follow this StackOverflow answer or leverage one of these JSON Functions and Operators.


I'm hoping you're talking about MS-SQL Server.
In that case is the answer: Yes you can run it on the same server.
Is it a good idea, that depends on what you want to achieve with it and how many resources that you have on the server.

I would prefer, if one server is only possible, that you install 3 different MS-SQL Server instances on this server each with their own mry and own storage.
But understand, that in case the server goes off-line, all 3 instances are also off-line.

Proper evaluation is needed.


Use COUNT and DISTINCT

CREATE TABLE table1 (
  `user_id` INTEGER,
  `product` VARCHAR(1)
);

INSERT INTO table1
  (`user_id`, `product`)
VALUES
  ('1', 'A'),
  ('1', 'A'),
  ('1', 'B'),
  ('2', 'A'),
  ('2', 'B'),
  ('3', 'A'),
  ('3', 'B'),
  ('3', 'B'),
  ('3', 'C'),
  ('3', 'D');

SELECT `user_id`, COUNT(DISTINCT `product`) FROM table1 GROUP BY `user_id`

user_id | COUNT(DISTINCT `product`)
------: | ------------------------:
      1 |                         2
      2 |                         2
      3 |                         4

db<>fiddle here


Create log file with SQL statements for only one database

Carefully reading the Error Reporting and Logging documentation, and specially, the section When to Log, you will find the following configuration parameters, that allows you not to log anything:

log_min_messages = 'PANIC'

You should add this parameter to your postgresql.conf file; and (most probably) restart the service.

For the single database whose info you want to log, connect to that database and execute:

ALTER DATABASE database_where_you_want_to_log
    SET log_min_messages = 'WARNING';  /* or whichever level you need */

This allows you to have one default (not to log anything) for most of your databases and one exception for a single one.


You can use the query below;

SELECT
  unnest(i),
  count(*)
FROM (
  VALUES
    ('{a,b,c,a}'::text[]),
    ('{a,b,a,b}'),
    ('{b,a,a,b}')
) AS t(i)
GROUP BY 1, i   -- group by column value, you can use any unique field here
ORDER BY i


SSIS vs T-SQL Join Performance, Difference Databases on Same server

Given the same specs, would like to know performance

If your data is on a single SQL Server instance, TSQL joins should always be faster than an SSIS lookup.  This is not really close.  A TSQL query will leverage indexes and statistics, and the cost-based query optimizer.  The join will be performed inside the SQL Server instance, where it is not only more efficient, but it has greater memory and disk resources than an SSIS pipeline.

SSIS lookups are primarily intended for destination-side lookups, heterogenous data scenarios, and for sources without a query processing engine (like flat files).

Further, when your data source is a single SQL Server instance, and your destination is a single (possibly different) SQL Server instance, personally I would almost never use any SSIS data flow transformations.  Instead always using source-system SQL for extract-side transformation, and stage-and-merge for destination-side transformation.


MongoDB not using all memory in Windows server

Figured out why, here is the explanation.

wiredTiger has a default cache size limit of 60%RAM - 1GB, should be overwritten in a system with large memory.


ERD via Chen's notation: How do I organize multiple 'last done' entries?

First, 'Friend' is probably not a different entity but just a relationship from 'User' to 'User' but your case may be special, I don't know. So about "if I add a friendID, should I have it as a derived key from user", assuming a friend is just another user - yes.

Additional info like 'last voice chat' & 'last im' shouldn't be part of a composite attribute, each 'last ' is meaningful by itself and a composite attribute doesn't help. If you want to know when user AAA lastly called anybody? then such info should be attributes of 'User', but if you want to know when user AAA lastly called user BBB? then such info should be attributes of the relationship between users AAA & BBB.

The type of relationship probably should be a simple attribute (enumerated) but it depends. If a friendship could be both silver & gold then it should be a composite attribute of boolean isSilver, isGold etc. But if is only silver or gold (or maybe normal) but only one of them, it should be a simple enumerated attribute.

Lastly, you should consider the different ways of how to define friendships, you probably want to read more about it in questions like these:

https://stackoverflow.com/questions/3009190/mysql-friends-table
https://stackoverflow.com/questions/6618701/storing-friends-in-database-for-social-network
https://stackoverflow.com/questions/3120591/mysql-friends-table-structure-help

Good Luck!


Should we enable innodb_file_per_table for single tenant SaaS app (thousands of databases)

Answering one by one:

Performance: yes, this is the main problem. Not only in resource for file descriptors, but also for the mysql open table cache and data dictionary, InnoDB checks on start and regenerating table statistics when opened. To be fair, these last ones (except InnoDB checks) will be found no matter the value of innodb_file_per_table, only because a huge number of tables.

The largest problem, however -in my opinion-, was that in the case of a DROP TABLE with innodb_file_per_table = 1 older versions of 5.5 required a full buffer pool lock, basically stalling the whole server. Some patches applied in the latest versions of MySQL 5.5 and 5.6, plus the buffer pool partitioning (available in 5.5 and enabled by default in 5.6) made that a minor issue.

On the bright side, you can in 5.6 set a manual path of every table, which may help in some cases, for example, to put the most problematic tables on a separate disk or an SSD, etc.
Stability: I wouldn't say that it is more stable, but it is true that if you have disk corruption and a single huge file, you can only pray. Having different files makes  easier to "save" all but one or a few. Not convinced that this is a decisive point -InnodB is well regarded about its disk consistency (assuming there is no physical damage).
Concurrency: This is true, but only in specific cases. Ext filesystems had a limitation that if you were using O_DIRECT mode, you couldn't write in concurrency to the same file descriptor. That is completely true, but is a very specific case: you could use a different filesystem (XFS is very recommended when handling many files and does not have that problem), you can use the filesystem cache if you have enough ram and in some cases the high concurrency is not a problem if writes go to different files at the same time (thus the innodb_file_per_table=1 or several ibdata files).
I cannot see your link, I suppose it is this? Yes, I agree 100% that one thing that it is missing in InnoDB is more control about the tablespace management (funnily enough, it is done for the disk tables in the memory-only engine NDB). I have not seen such a thing implemented. You are supposed to do all optimizations at RAID/LVM/physical level.

I think that you miss one thing that is the number 1 reason for having innodb_file_per_table = 1 - the flexibility for backups, restores, management and moving tables around inidividually  (something made even easier with the transportable tablespaces in MySQL 5.6). With it enabled, you will be able to recover disk space when tables are dropped and make defragmentation possible. When you have thousands of tables in a single file, physical backup and restore is impossible, and you have to use logical backups (mysqldump/LOAD DATA).

So what are the alternatives? I have found this problem before, and I have helped solving it on hosts with literally millions of tables in several ways:

If disk space is not a problem, you can have some tables in the common tablespace and some separately (innodb_file_per_table is a dynamic variable, although you need to rebuild the table for each one you want to move in or out). This solution is ok if your problem is having some tables that are very dynamic -being created and destroyed frequently- while other are static, but you may end up with a very large common tablespace. However, if using innodb_file_per_table = 0 works better with you, then end of discussion.
You can have several common tablespace files (ibdata1;ibdata2;ibdata3). This is ok if you want to separate them in several disks, or you do not want to deal with huge files, but it will not solve the "having many tables" problem.
Change your schema- For example, wordpress multisite installation puts many tables together, and if I remember correctly (I may be wrong), it only creates a new table per site, not a full database. If your tables are going to be very small, this is the way to go- modify the structure so that you have larger and less tables
Choose a different engine - Wordpress.com uses MyISAM at scale and they do not care about corruption because they have a good backup/recovery system. Maybe this is your case if most of your tables are read-only (or almost read-only) or you put a good caching system in-between.
What is the number 1 way to deal with this problem? What I have been doing on behalf of my clients most of the time is what Wordpress, Facebook and other do- sharding. Do not try to handle a million of users in a single powerful machine- use smaller ones and divide load (and databases and users) between several servers. You do not have to handle the sharding all by yourself, there are tools to help you do that, like the Upstream MySQL Fabric (for Java and Python) or the engine Spider.

Please note that if the bottleneck is not on CPU/disk/memory, you can even use virtual machines in order to consolidate services.

In general, innodb_file_per_table is something that you want ON, for the flexibility reasons that I told you before. If you cannot have it on (test it first, maybe you would be surprised! -avoid premature optimization), you can disable it for all or some of the tables (but the change will be more painful than anything else. But be prepared for the consequences. Try to avoid that with some of the recommendations that I told you at the end.

Update: 5.7 labs releases have announced that may finally become obsolete and we will finally have better tablespace management thanks to a CREATE TABLESPACE syntax (search for "InnoDB: General Tablespaces"). With this, you will be free to create your own "innodb_file_per_database" or any other schema you want.


SQL TOP statement, how to make sure it's "random"?

I'm going to assume you're using SQL Server, as that's the version of SQL I know of that uses TOP rather than LIMIT.

Data in SQL Server is always in some sort of order; however, what that order is can only be guaranteed when you include an ORDER BY clause in your SQL statement.

Without an ORDER BY clause, the order of a dataset is determined by the process required to generate it, and the order in which is was stored.

If a table doesn't change, and you run the same SQL statement against it multiple times, you'll get the data back in the same order. That's because the query engine is applying the same process each time to generate the resulting data set.

So, if you were to issue your query against a table (with no inserts, updates, or deletes) multiple times, you'll get the same 2 rows each time.

Note: depending on the structure of the table (for instance, whether or not there's a clustered index), and activity against the table, your SELECT TOP 2 * might give different results on a table that's not basically static. That's why you need an ORDER BY to guarantee the order, even if today a query run without an ORDER BY returns the data in the order you want it consistently.

With TOP and ORDER BY, the query engine must at least partially sort your data (if it can locate and sort the top n rows, it doesn't have to sort the rest). With TOP but not ORDER BY, it simply selects the first n rows of the dataset it's generated.

If you want a random ordering, you (strange as it would sound) need to order the data by something random. ORDER BY NEWID() (as suggested in the comments by Lamak) would do this. NEWID() generate a new GUID each time it runs, and those are not generated in any sort of sequential order. This SO question explains how that works. It also describes how TOP works further.

As sp_BlitzErik points out, ORDER BY NEWID() is not fast, and is slower the larger the table is (it has to generate a GUID for each row in the table, regardless of the TOP number of rows to be returned). He links to How to Get a Random Row from a Large Table, an article on his company's website, for additional suggestions for getting truly random rows from a table.

Basically, the options are to use a relatively obscure query option called TABLESAMPLE, which can present problems; or, use the RAND function to generate a random ID value to return one row (dumping random numbers into a temp table if you need more than one or two rows, or a variable number of rows each time you run). You can also use RAND with the OFFSET ... FETCH clause to get a single random row in SQL 2012 or later.


What will be bytes occupied if we store 2 bytes value in 8 bytes column?

You can easily get the values you are after:

SELECT column_a, pg_column_size(column_a),
       column_b, pg_column_size(column_b),
       ...
  FROM your_table;

This gives you the byte size of the given values stored in the columns, alongside with the values themselves.

A bigint will always consume 8 bytes, whereas a varchar just the size needed for the given value.  (The latter is tricky, read upon TOAST.)


querying sys.database_principals accross a linked server

sys.database_principals and sys.server_principals are protected views.

From the MSDN page on sys.database_principals:

  Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

You need to ensure the login used by the linked server has at least the ALTER ANY USER or ALTER ANY ROLE privilege in order to see the other principals.


How to update value in inserted row in PostgreSQL?

There's no point in using UPDATE to rows INSERTed in the same statement, as the INSERT could set the missing column in the first place:
insert into t1(id, name, str2) values (38, 'www', 'aaa');

Besides the pointlessness, it doesn't work, as shown in the question.
The reason is mentioned in the doc in 7.8.2. Data-Modifying Statements in WITH:

All the statements are executed with the same snapshot (see Chapter
13), so they cannot "see" one another's effects on the target tables
[...]
Trying to update the same row twice in a single statement is not
supported. Only one of the modifications takes place, but it is not
easy (and sometimes not possible) to reliably predict which one. This
also applies to deleting a row that was already updated in the same
statement: only the update is performed. Therefore you should
generally avoid trying to modify a single row twice in a single
statement. In particular avoid writing WITH sub-statements that could
affect the same rows changed by the main statement or a sibling
sub-statement. The effects of such a statement will not be
predictable.


Does the target for Double-Take replication require an Oracle license?

We had a conference call with our Oracle Technology Sales Manager and an Oracle Solution Consultant.  We discussed the Double-Take setup and they were able to confirm that based on the way we have our DR site setup and our primary site licensed, we do NOT need to license the DR site.  This is of course assuming we do not deviate from the setup we discussed with them.  This somewhat surprised me and should in no way be looked at as an indicator Oracle's stance on Double-Take for any other installation than ours.


Client processing time is the amount of time spent in milliseconds between the first received response packet and the last received response packet by the client.

When you run the query in SSMS it render the data row by row to the GUI. Your application doesn't do that.

Setting the SSMS option to Discard results after execution will eliminate the time spent rendering the results to the grid, which should give you a comparable execution time:


Is it useful to split a table into a frequently used and a archive table?

Personally, I would split that table. Definitely consider Rick James' point about ensuring everything is Indexed properly and is being queried efficiently first, but, at the end of the day, you're removing 90% of the data that you have to sift through to get what you want.  Archiving that data will make queries faster by reducing row counts and shrinking the Index(es) considerably. There will be more Indexes because you'll have to duplicate them on your Archive table, so disk space will suffer a little, but my expectation is that performance will improve...

UNLESS:
The only part of this that jumps to mind that would KILL what I'm suggesting is if the data being moved to the Archive is accessed more than you think.  In that case, you're now querying two tables, totaling to MORE than what you had before (including duplicated Indexes/Keys); not only is there not a benefit, you've now taken more time (with more overhead) than your original setup would have taken.

In short: you need to be VERY sure about how often that Archived data is being accessed, and HOW it's being accessed, before you move it.


Postgres - Only allow insert if TWO columns match with a separate table

You can create a unique compound index

 CREATE UNIQUE INDEX "book_id_user" ON books (identifier_id,user_id);

And then you can create a F.K. constraint

 ALTER TABLE chapters
   ADD CONSTRAINT "FK_chapter_book"
     FOREIGN KEY (book_id,user_id)
       REFERENCES books (identifier_id,user_id);

If you have any bad data that will need to be corrected before the constraint can be created


evaluate execution plan with different statements

If you can run the query with the different plans (i.e. if it does not run forever) you can use this approach to see which step ran longer than the optimizer expected:
SELECT /*+gather_plan_statistics*/ *
FROM complicated_view v
WHERE v.day = DATE '2020-01-25';

select *
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

If you search for gather_plan_statistics you will see a number of articles that explain how to use it. I look for steps in the plan that take the most time and whose actual rows are very different from the optimizer's estimated row count.


Problem installing mssql-tools "ODBC Driver 17"

From ServerFault,

sudo dpkg -P msodbcsql
sudo apt -f install


How to avoid a cyclic dependency (circular reference) between 3 tables?

Business rules
Let us make some rewordings to the business rules you have presented:

A Person creates zero-one-or-many Posts.
A Post receives zero-one-or-many Likes.
A Person manifests zero-one-or-many Likes, each of wich pertains to one specific Post.

Logical models
Then, from such set of assertions, I have derived the two logical level IDEF1X[1] data models that are shown in Figure 1.

Option A
As you can see in the Option A model, PersonId migrates[2] from Person to Post as a FOREIGN KEY (FK), but it receives the role name[3] of AuthorId, and this attribute makes up, together with PostNumber, the PRIMARY KEY (PK) of the the Post entity type.
I assume that a Like can only exist in connection with a particular Post, so I have set up a Like PK that comprises of three different attributes: PostAuthorId, PostNumber and LikerId. The combination of PostAuthorId and PostNumber is a FK that makes the proper reference to the Post PK. LikerId is, in turn, a FK that establishes the suitable association with Person.PersonId.
With the aid of this structure, you ensure that a determined person can only manifest a single Like occurrence to the same Post instance.
Methods to prevent a Post Author from liking his own Post
Since you do not want to allow the possibility that a person can like his/her authored posts, once in the implementation phase, you should establish a method that compares the value of Like.PostAuthorId with the value of Like.LikerId in every INSERT attempt. If said values match, (a) you reject the insertion, if they do not match (b) you let the process continue.
In order to accomplish this task in your database, you can make use of:

A CHECK CONSTRAINT but, of course, this method excludes MySQL, since it has not been implemented in this platform so far, as you can see here and here.

Code lines inside an ACID Transaction.

Code lines within a TRIGGER, which could return a custom message indicating the rule violation attempt.

Option B
If the author is not an attribute that identifies in a primary way a post in your business domain, you could go with a structure similar to the one depicted in Option B.
This approach also makes sure that a post can only be liked by the same person one single time.

Notes
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST).
2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.
3. A role name is a denotation assigned to a foreign key attribute in order to express the meaning of such attribute in the context of its corresponding entity type. Role naming is recommended since 1970 by Dr. E. F. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates this procedure.


You can use a recursive CTE for this, provided you don't have more than 100 relations (since you can't add MAXRECURSION inside the function, so you'd have to apply it on any query that references the function - see an example here).

The first step is to find all the parent-less parents, then use that as an anchor for recursion.

CREATE FUNCTION dbo.my_func(@input nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN
  (
    WITH Parents(name) AS
    (
      -- find parent-less parents:
      SELECT name FROM dbo._test
      EXCEPT
      SELECT related_name FROM dbo._test
    ),
    children AS
    (
      -- use parents as anchor
      SELECT orig = name, name FROM Parents
      UNION ALL
      SELECT c.orig, related_name
        FROM dbo._test AS t
        INNER JOIN children AS c
        ON c.name = t.name
    )
    SELECT name
    FROM children AS o
    -- now filter only to a single, common anchor
    WHERE EXISTS
    (
      SELECT 1 FROM children WHERE name = @input AND orig = o.orig
    )
  );

Then you can query:

SELECT name FROM dbo.my_func(N'b');

Results:

name
----
a
b
c
d

Of course it is easy to break this with a circular reference. Don't do that.


Postgresql Create ForeignKey with repeated table name in value of ref contrain key

Can I create an FK with this type of structure?

No, you can't create a FK because the value of a FK column must match the value of the referenced PK column. But there is no value foo.1  in the table foo.
That design is horrible. I understand you didn't create that mess, but if you do have the chance to fix it, then do it as soon as possible.


Postgres: get total number of matched records using UNION

I think you just need to change your query...

(SELECT
  NULL AS id,
  'allTables' AS type,
  (SELECT COUNT(*) OVER() FROM table1) + (SELECT COUNT(*) OVER() FROM table2) AS matches)
UNION
(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT (1 + 3)

But I can't speak to how efficient it will be....

-- Additional

This may work, as well.  But again, efficiency will likely be poor (since it will get all results, regardless if you use it or not)

WITH results as (
  (SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
  UNION
  (SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
) SELECT NULL AS id, 'allTables' AS type, COUNT(matches) FROM results
UNION
SELECT id, type, matches FROM results
LIMIT (1 + 3)

Not very friendly to the DB, in either case.  But you only have to have your WHERE clause twice (as opposed to 4 times)


Get major and minor version of SQL Server

The ProductMajorVersion and ProductMinorVersion properties are only available in SQL Server 2012 and above (according to https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql). Your product version indicates that this is SQL Server 2008 SP1, which does not support the two other properties.