Trace Flag 4199 - Enable globally? This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server.  For those that have used it, under what circumstances did you experience query regression?

It certainly seems like a potential performance benefit across the board, I'm considering enabling it globally in our non-production environment and letting it sit for a couple months to ferret out any issues.

Are the fixes in 4199 rolled into the optimizer by default in 2014 (or 2016)?  Although I understand the case for not introducing unexpected plan changes, it seems odd to keep all these fixes hidden between versions.

We're using 2008, 2008R2 and mostly 2012.

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions.

The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the differences between old and new version are a concern on their own and having to deal with plan regression is expected anyway, so I prefer fighting with it with TF4199 enabled.

AS far as existing databases is concerned, there is only one way to know: test it. You can capture a workload on the existing setup and replay it after enabling the flag. RML Utilities can help you automate the process, as described in this answer.

Obviously, the flag affects the whole instance, so you'll have to test all the databases sitting there.


Find the missing numbers in a given range Hi I have a table that has start and  end columns that has the values

Start   End
9       10
11      19
29      44
45      51
56      60

Now if I give the range like 15 to 60 I need to return the values that not being covered in the above range.

The output I am expecting is  - 20,21,22,23,24,25,26,27,28,52,53,54,55

How can I retrieve this?

In postgresql:

CREATE TABLE ranges
(n1 int,
n2 int);

INSERT INTO ranges
VALUES (9,10),(11,19),(29,44),(45,51),(56,60);

SELECT n FROM ranges
RIGHT JOIN generate_series(15,60) n
ON (n1 <= n AND n <= n2) WHERE n1 IS NULL;


Histogram bin width in MySQL I want to create an histogram based on price column values from equipment table.

I know I can count the frequency of prices in an interval I can set, but the problem is that I won't get the ranges with 0 frequency.

so if I want an interval of 200 and the values are:

0,55,150,420,490,550

I will only get the ranges:

0-200, count =   3

400-600, count =   3

as a solution I can write explicitly the ranges, like:

SELECT
count(CASE WHEN price>= 0 AND price < 200 THEN 1 END) AS '[0 - 200]',
count(CASE WHEN price>= 200 AND price < 400 THEN 1 END) AS '[200 - 400]'
...
...
from equipment
group by eq_type;

if I want to set multiple ranges, let's say from 0 to 10000 in steps of 200,
is there a way I can create an array/series and use it in the query instead of typing 50 rows of the above code?

You need to create synthetic table containing all the intervals in Your timeline range. Or the numbers table when range limits are easily calculated from the range number.

Example:

CREATE TABLE test (val INT);
INSERT INTO test VALUES (0),(55),(150),(420),(490),(550);

SELECT (nums.num-1)*200 mini, nums.num*200 maxi, COUNT(test.val) cnt
FROM (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3) nums
LEFT JOIN test ON (nums.num-1)*200 <= test.val AND test.val < nums.num*200
GROUP BY nums.num;

The numbers count is to be equal (or more, not less) to the ranges count.

If You want to change the amount of ranges dynamically influenced by data values range (with some reasonable limit) You may use the next method to create synthetic table nums (for example if ranges count guaranteed less than 1000):

SELECT nums1.num*100+nums2.num*10+nums3.num num
FROM (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums1,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums2,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums3

To cut off the upper empty ranges use additional condition like

WHERE ... AND nums.num * @step <= (SELECT MAX(val) FROM datatable)


How can I append a total to a table and split the table by group (order_id)? I am currently using:

select order_id as 'Order ID',
    DATE_FORMAT(date, '%d/%m/%Y') as 'Date',
    prod_title as 'Item',
    amount as 'Amount'
from transactions
where date >= '2016-10-01'
order by order_id,
    amount desc;

This first orders by order_id then by amount in descending order, which allows me to group orders regardless of date.

I'm wondering how I can split each grouping of order_id so I get something like this:

|3265|01|
|3265|02|
---------
|2613|03|
---------
.
.
.
|    |06|<-total

Is that clear? Also, how do I append a total?

Try this.  I may or may not work, depending on whether you have multiple rows for a given order_id:

select order_id as 'Order ID',
    DATE_FORMAT(date, '%d/%m/%Y') as 'Date',
    prod_title as 'Item',
    SUM(amount) as 'Amount'       -- changed
from transactions
where date >= '2016-10-01'
GROUP BY order_id  WITH ROLLUP    -- added
order by order_id,
    amount desc;

If that fails, then

( SELECT ... as you have, but without ORDER BY ... )
UNION ALL
( SELECT NULL, NULL, 'Total', SUM(amount)
       from transactions
       where date >= '2016-10-01' )
ORDER BY order_id,
    amount desc;

This will put the Total at the top; that can be fixed by changing to something like

ORDER BY order_id IS NOT NULL, order_id, amount DESC


What is the use of the select statement in SQL(ite) triggers? In SQLite, triggers may contain four types of statements: insert, update, delete, and select. What is the use of the select statement, namely, how can I access the result-set of the select statement?

For example, with the following setup,

create table "Data" ("data" Text);
create trigger "Data-AI" after insert on "Data" begin
    /* some insert, update, or delete statements */
    select "data" as "selected" from "Data" where length("data") < 4;
            -- assume that there at least two rows in the result set
    /* some insert, update, or delete statements */
end;

how can I use "selected" in the second batch of insert, update, or delete statements?

In a trigger body, the result of a SELECT statement is simply thrown away.

Using a SELECT statement makes sense only for its side effects, i.e., when using a user-defined function, or RAISE().


Unable to Add Trigger I have the following script which drops/ creates six triggers for a MySQL database; v5.5.52.

DROP TRIGGER IF EXISTS gene_lcase_insert;
DROP TRIGGER IF EXISTS morph_lcase_insert;
DROP TRIGGER IF EXISTS genus_lcase_insert;
DROP TRIGGER IF EXISTS species_lcase_insert;
DROP TRIGGER IF EXISTS python_lcase_insert;
DROP TRIGGER IF EXISTS python_extract_year_insert;

/* Enforce lower-case on INSERT into 'gene' table */

CREATE TRIGGER gene_lcase_insert BEFORE INSERT ON gene
FOR EACH ROW
    SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'morph' table */

CREATE TRIGGER morph_lcase_insert BEFORE INSERT ON morph
FOR EACH ROW
      SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case in INSERT into 'genus' table */

CREATE TRIGGER genus_lcase_insert BEFORE INSERT ON genus
    FOR EACH ROW
     SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'species' table */

CREATE TRIGGER species_lcase_insert BEFORE INSERT ON species
  FOR EACH ROW
      SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'python' table */

CREATE TRIGGER python_lcase_insert BEFORE INSERT ON python
  FOR EACH ROW
      SET NEW.sold_to = LOWER(NEW.sold_to),
          NEW.feeding = LOWER(NEW.feeding);

/* Extract year from date_of_birth on INSERT if not NULL */

DELIMITER //
CREATE TRIGGER python_extract_year_insert BEFORE INSERT ON python
   FOR EACH ROW
   BEGIN
          IF NEW.date_of_birth IS NOT NULL
              THEN
                  SET NEW.sequence_year =  YEAR(NEW.date_of_birth);
          END IF;
  END//
DELIMITER ;

Upon executing the script, I receive the following error - when the final 'CREATE TRIGGER' statement is reached:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

I then perform the following check, to see if the TRIGGER exists:

mysql> select trigger_name from information_schema.triggers;
+----------------------+
| trigger_name         |
+----------------------+
| gene_lcase_insert    |
| genus_lcase_insert   |
| morph_lcase_insert   |
| python_lcase_insert  |
| species_lcase_insert |
+----------------------+
5 rows in set (0.00 sec)

It appears that the final 'python_extract_year_insert' trigger is not being created; sanity check:

mysql> drop trigger python_extract_year_insert;
ERROR 1360 (HY000): Trigger does not exist

I am uncertain as to why this is occurring. Thanks in advance.

Note:

I have tried implementing the final trigger (python_extract_year_insert) utilizing a separate file, to see if it was a processing issue of some kind - to no avail.

The answer is in your question:

  ERROR 1235 (42000): This version of MySQL doesn't yet support
  'multiple triggers with the same action time and event for one table

For any given table you can only have one trigger per each event (insert/update/delete) AND per each time of execution of said event (after/before).


Which server actually runs the SSIS package when SQL Agent and SSISDB are on 2 different servers We have a SQL Server where we deployed the SSIS packages to its Integration Service Catalog (SSIS DB).
And we have another SQL Server where we scheduled SQL Agent to run those SSIS packages.
My question, where does the SSIS package execution actually happens? The SQL Agent server, or the Integration Service Catalog?
A little background of why we are doing/asking this: we have an old SQL Server where we deployed a lot of legacy SSIS packages. Now we have a new SQL Server where we plan to move to, but we don't want to move those legacy SSIS packages. So we are trying to just move some SQL Agent jobs to the new server to call those legacy SSIS. With some research, we thought the execution happens on the SQL Agent server, but the log says it's on the SSIS DB server. However, the execution failed and complain missing ZappySys component, which looks like the execution happened on the SQL Agent server, because we indeed don't have ZappySys component on the SQL Agent server. We are so confused now......

SSIS packages deployed to  to its Integration Service Catalog (SSIS DB) will run on the server where SSIS service and SSISDB in installed.


Is an Oracle 10g instance dependent on the windows account authentication? I have created an Oracle 10g (10.2.0.4) instance on a Windows 2003 Server with a domain user xxx having domain administrator level privileges.

Now I wish to change xxx's password. Will it impact the oracle instance functioning?

Oracle on Windows creates a service for database instances, called OracleService%ORACLE_SID%. By default, this service uses the Local System account so a password change of the installation user will not affect the database.

It is however possible and recommended to change the service user from Local System to a standard Windows account. If you performed this step, and you wish to change the password of that account, you must update the password in the service configuration.

Managing Oracle Home User

  You can change the password using Windows tools. However, when you
  change the password for this Windows User Account, you must also
  update the password for all Oracle services running under the Windows
  User Account.


Separate a column into its components based on another table I'm struggling with a very difficult and complicated query here and I need your help.

I have two tables as you can see below:

1) ACCOUNT1:(ATYPCODE int, ATYPDESC varchar(50), ATLASTFLAG varchar(50))

some example data of this table are:

ATYPCODE    ATYPDESC    ATLASTFLAG
 3000          A            0
 19            B            0
 1170          C            0
 1178          D            1
 4000          AA           0
 18            BB           0
 2020          CC           1

Column ATLASTFLAG shows whether this record is the last level .
Table number two is

2) ACCOUNT2:(ATYPCODE int, AGLTCODE varchar(50), AGLTLVL int)

some example data of this table are:

    ATYPCODE      AGLTCODE          AGLTLVL
      3000        3000                1
      19          3000  19            2
      1170        3000  191170        3
      1178        3000  1911701178    4
      4000        4000                1
      18          4000  18            2
      2020        4000  182020        3

Column ATYPCODE is exactly the same as ATYPCODE column in the first table.Column AGLTCODE is bult based on the concatenation of ATYPCODEcolumn as you can see. The point is that the second level added to the first level with two spaces . so we have 3000+space+space+19+Other_levels and we have to separate this column exactly with 4 characters.
and the column AGLTLVL shows the level of each ATYPCODE .

what we want to see as a result is this :

ATYPCODE    AGLTCODE   AGLTLVL   ATYPCODE1     ATYPCODE2    ATYPCODE3     ATYPCODE4   ATYPDESC1  ATYPDESC2    ATYPDESC3      ATYPDESC4

and values for each column

ATYPCODE    = 1178
AGLTCODE    = 3000  1911701178
AGLTLVL     = 4
ATYPCODE1   = 3000
ATYPCODE2   = 19
ATYPCODE3   = 1170
ATYPCODE4   = 1178
ATYPDESC1   = A
ATYPDESC2   = B
ATYPDESC3   = C
ATYPDESC4   = D

and also the next record is

ATYPCODE    = 2020
AGLTCODE    = 4000  182020
AGLTLVL     = 3
ATYPCODE1   = 4000
ATYPCODE2   = 18
ATYPCODE3   = 2020
ATYPCODE4   = 2020(last value is repeated)
ATYPDESC1   = AA
ATYPDESC2   = BB
ATYPDESC3   = CC
ATYPDESC4   = CC(Last Values is repeated)

as you can see some records have 4 levels and some have 3 levels
since the structure of the destination table is fixed,for those with 3 levels , last value ATYPCODE3 and ATYPDESC3should be repeated for columns ATYPCODE4  and ATYPDESC4 .

the only part of query I was able to write was this cause we only nees to store the last level and nothing comes to mind for the rest of the query

SELECT b.ATYPCODE , b.AGLTCODE , b.AGLTLVL
FROM ACCOUNT1 a inner join
       ACCOUNT2 b on a.ATYPCODE = b.ATYPCODE
where a.ATLASTFLAG = 1

The first thing I would do is create some keys on the source tables:

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.ACCOUNT1 (ATYPCODE);
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.ACCOUNT2 (AGLTLVL, AGLTCODE);

Then organize the data into a useful hierarchy using a recursive query to assign group and row numbers within the structure:

CREATE TABLE #Data
(
    grp integer NOT NULL,
    rn integer NOT NULL,
    ATYPCODE integer NOT NULL,
    AGLTLVL integer NOT NULL,
    AGLTCODE varchar(50) NOT NULL,

    PRIMARY KEY (grp, rn DESC)
);

WITH R AS
(
    -- Anchor: rows where AGLTLVL = 1
    SELECT
        grp = ROW_NUMBER() OVER (ORDER BY A.AGLTCODE),
        rn = 1,
        A.ATYPCODE,
        A.AGLTLVL,
        AGLTCODE = CONVERT(varchar(50), A.AGLTCODE + SPACE(2))
    FROM dbo.ACCOUNT2 AS A
    WHERE
        A.AGLTLVL = 1

    UNION ALL

    -- Recursive: find the next AGLTLVL row in sequence
    SELECT
        R.grp,
        R.rn + 1,
        A.ATYPCODE,
        A.AGLTLVL,
        A.AGLTCODE
    FROM R
    JOIN dbo.ACCOUNT2 AS A WITH (FORCESEEK)
        ON A.AGLTLVL = R.AGLTLVL + 1
        AND A.AGLTCODE LIKE R.AGLTCODE + '%'
        AND A.AGLTCODE = R.AGLTCODE + CONVERT(varchar(11), A.ATYPCODE)
)
INSERT #Data
(
    grp,
    rn,
    ATYPCODE,
    AGLTLVL,
    AGLTCODE
)
SELECT
    R.grp,
    R.rn,
    R.ATYPCODE,
    R.AGLTLVL,
    R.AGLTCODE
FROM R
OPTION (MAXRECURSION 0);

The contents of the #Data table at this point are:

╔═════╦════╦══════════╦═════════╦══════════════════╗
║ grp ║ rn ║ ATYPCODE ║ AGLTLVL ║     AGLTCODE     ║
╠═════╬════╬══════════╬═════════╬══════════════════╣
║   1 ║  1 ║     3000 ║       1 ║ 3000             ║
║   1 ║  2 ║       19 ║       2 ║ 3000  19         ║
║   1 ║  3 ║     1170 ║       3 ║ 3000  191170     ║
║   1 ║  4 ║     1178 ║       4 ║ 3000  1911701178 ║
║   2 ║  1 ║     4000 ║       1 ║ 4000             ║
║   2 ║  2 ║       18 ║       2 ║ 4000  18         ║
║   2 ║  3 ║     2020 ║       3 ║ 4000  182020     ║
╚═════╩════╩══════════╩═════════╩══════════════════╝

Then the final query becomes much easier. We take the values from the highest row number per group for some values, and pivot the others, while adding in the type descriptions from the other table:

SELECT
    SQ2.ATYPCODE,
    SQ2.AGLTCODE,
    SQ2.AGLTLVL,
    SQ2.ATYPCODE1,
    -- Fill in any missing type codes
    ATYPCODE2 = COALESCE(SQ2.ATYPCODE2, SQ2.ATYPCODE1),
    ATYPCODE3 = COALESCE(SQ2.ATYPCODE3, SQ2.ATYPCODE2, SQ2.ATYPCODE1),
    ATYPCODE4 = COALESCE(SQ2.ATYPCODE4, SQ2.ATYPCODE3, SQ2.ATYPCODE2, SQ2.ATYPCODE1),
    -- Fill in any missing type descriptions
    ATYPDESC2 = COALESCE(SQ2.ATYPDESC2, SQ2.ATYPDESC1),
    ATYPDESC3 = COALESCE(SQ2.ATYPDESC3, SQ2.ATYPDESC2, SQ2.ATYPDESC1),
    ATYPDESC4 = COALESCE(SQ2.ATYPDESC4, SQ2.ATYPDESC3, SQ2.ATYPDESC2, SQ2.ATYPDESC1)
FROM
(
    SELECT
        -- Same in every row anyway
        ATYPCODE = MAX(SQ1.ATYPCODE),
        AGLTCODE = MAX(SQ1.AGLTCODE),
        AGLTLVL = MAX(SQ1.AGLTLVL),
        -- Pivot type codes
        ATYPCODE1 = MAX(IIF(SQ1.rn = 1, SQ1.TypeCode, NULL)),
        ATYPCODE2 = MAX(IIF(SQ1.rn = 2, SQ1.TypeCode, NULL)),
        ATYPCODE3 = MAX(IIF(SQ1.rn = 3, SQ1.TypeCode, NULL)),
        ATYPCODE4 = MAX(IIF(SQ1.rn = 4, SQ1.TypeCode, NULL)),
        -- Pivot type descriptions
        ATYPDESC1 = MAX(IIF(SQ1.rn = 1, SQ1.ATYPDESC, NULL)),
        ATYPDESC2 = MAX(IIF(SQ1.rn = 2, SQ1.ATYPDESC, NULL)),
        ATYPDESC3 = MAX(IIF(SQ1.rn = 3, SQ1.ATYPDESC, NULL)),
        ATYPDESC4 = MAX(IIF(SQ1.rn = 4, SQ1.ATYPDESC, NULL))
    FROM
    (
        SELECT
            -- Values taken from highest row number per group
            ATYPCODE = FIRST_VALUE(D.ATYPCODE) OVER (
                PARTITION BY D.grp
                ORDER BY D.rn DESC
                ROWS UNBOUNDED PRECEDING),
            AGLTCODE = FIRST_VALUE(D.AGLTCODE) OVER (
                PARTITION BY D.grp
                ORDER BY D.rn DESC
                ROWS UNBOUNDED PRECEDING),
            AGLTLVL = FIRST_VALUE(D.AGLTLVL) OVER (
                PARTITION BY D.grp
                ORDER BY D.rn DESC
                ROWS UNBOUNDED PRECEDING),
            -- Pivot data
            TypeCode = D.ATYPCODE,
            A.ATYPDESC,
            -- Groups and row numbers
            D.grp,
            D.rn
        FROM #Data AS D
        JOIN dbo.ACCOUNT1 AS A
            ON A.ATYPCODE = D.ATYPCODE
    ) AS SQ1
    GROUP BY
        SQ1.grp
) AS SQ2;

db<>fiddle demo

╔══════════╦══════════════════╦═════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╗
║ ATYPCODE ║     AGLTCODE     ║ AGLTLVL ║ ATYPCODE1 ║ ATYPCODE2 ║ ATYPCODE3 ║ ATYPCODE4 ║ ATYPDESC2 ║ ATYPDESC3 ║ ATYPDESC4 ║
╠══════════╬══════════════════╬═════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╣
║     1178 ║ 3000  1911701178 ║       4 ║      3000 ║        19 ║      1170 ║      1178 ║ B         ║ C         ║ D         ║
║     2020 ║ 4000  182020     ║       3 ║      4000 ║        18 ║      2020 ║      2020 ║ BB        ║ CC        ║ CC        ║
╚══════════╩══════════════════╩═════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╝


Is there a max allowance for entries in a table in mysql? Is there a set maximum (or even a recommended maximum) for how many entries that can/should be stored in a mysql table? How much is really too much?

I took a look at MySQL documentation and there seems to be no maximum number for records in a table, but taking a look at MySQL features page:

  Scalability and Limits:

  Support for large databases. We use MySQL Server with databases that
  contain 50 million records. We also know of users who use MySQL Server
  with 200,000 tables and about 5,000,000,000 rows.

  Support for up to 64 indexes per table (32 before MySQL 4.1.2). Each
  index may consist of 1 to 16 columns or parts of columns. The maximum
  index width is 1000 bytes (767 for InnoDB); before MySQL 4.1.2, the
  limit is 500 bytes. An index may use a prefix of a column for CHAR,
  VARCHAR, BLOB, or TEXT column types.

According to this information, the recommended maximum number of records may vary a lot depending on your hardware limitations but would probably be around the boundaries of 5,000,000,000 rows.

EXTRA: if you plan to store really large numbers of records in a table, care to use the MAX_ROWS parameter in your CREATE TABLE data definition statements.


Percona cluster + Wordpress blogs, low performance We currently run ~ 900 WordPress blogs on several machines so was decided to move to load balanced environment with clustered db.

I followed instructions from http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/cenots_howto.html, installed on 3 nodes Percona's XtraDB and imported DB dump, total DB's size is around 40 GB.

Now if there are no inserts in DB queries work fast and all is ok but if we start to do some inserts (4 - 10 inserts per second) on nodes all queries begin to slow down very dramatically reads and writes.

Hardware :

Intel(R) Core(TM) i7 CPU 950 3.07GHz with 26 GB RAM

Xeon E5-2670 0 @ 2.60GHz  with 64 GB RAM

my.conf

[mysqld]
datadir=/var/lib/mysql
user=mysql
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.93,192.168.0.94,192.168.0.95
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.0.94

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

innodb_log_file_size = 50331648

# Bellow begins different testing values

wait_timeout=20
interactive_timeout=30

key_buffer_size=256M
query_cache_limit=256M
sort_buffer_size=128M
read_rnd_buffer_size=128M

tmp_table_size=1G
max_heap_table_size=1G

innodb_file_per_table=1

innodb_data_file_path = ibdata1:512M:autoextend

innodb_fast_shutdown = 0

innodb_buffer_pool_size        = 6144M
innodb_buffer_pool_instances   = 4
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64

# CACHES AND LIMITS #
max_connections                = 200
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DSYNC
#autocommit=OFF

sync_binlog=0

innodb_thread_concurrency = 0
innodb_io_capacity = 5000
bulk_insert_buffer_size = 256M

innodb_locks_unsafe_for_binlog=1
innodb_stats_on_metadata=0

What can be problem ?
Machines run in same LAN with ~ 0.1 ms latency

and cluster is deployed under OpenVZ virtualisation

Galera/Percona XtraDB Cluster is an solution that provides high availability, read scaling and full consistency. Although in some cases it can provide some write scaling (mainly due to poor SQL writing), it is not focused on it, as it does not provide sharding, so it has to write to all nodes in a (virtually) synchronous way. This is important to decide if Galera is for you.

Having said that, some comments in your setup:

Virtualisation, while not a problem per-se, can add some component of indeterminism, which is critical for Galera as it will perform as bad as the worst performing node. Make sure all nodes have a predictable performance.
you can reduce some durability parameters in the cluster (for example, setting innodb_flush_log_at_trx_commit = 2), as HA will be provided by node redundancy in the case of a crash. This will increase performance.
Certain workloads may need client changes, as they are hurtful for Galera performance. In particular, frequent updates on the same rows can case collision and frequent rollbacks of transactions, reducing the throughout. Identify hotspots with the Percona/XtraDB cluster statistics and  profiling and try to avoid them from client side. In some cases, some commercial applications may need to change some code slightly to adapt to a cluster infrastructure.
Network performance is critical. You say that the latency between nodes is 0.1 ms. A transaction will take an overhead at least equal to double the latency of the slowest node.


Could not allocate space for object sql server Can anyone give a solution for this error:

  Could not allocate space for object ‘object_name’ in database ‘database_name’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

How can I delete files from primary file group?

You need to do one or more of 3 things:

Increase the max file size of the file
Increase the disk space wherever your .mdf & .ndf files are stored
Delete data from your database to free up space for the new object

Right click on your Database > Properties > Files.

Here you will see the file names, file groups, path, and importantly the Autogrowth / Maxsize. If you have it set to Limited to ... then it can't grow beyond this limit. If the file is full, you will either need to change the Maxsize or delete data from your database. If the file isn't full, then your disk could is almost certainly full and you'll need to add space there.

Also, don't shrink your database in an attempt to fix this.


Query optimization: how to efficiently occlude overridden records? Imagine a table with roughly a couple of billion rows.  A simplified schema for the table would be

started_on: datetime
recorded_on: datetime
image_id: integer

It's indexed by started_on.

One quirk of the data is that there can be many records with identical started_on values but they may have different recorded_on and image_id values.  Further, for a given started_on, we only want the most recently recorded record.

Currently we get this by

1) pulling all the records within the started_on range, and then

2) using code to grab out the most recently recorded_on record for each started_on.

This works fine, but seems like a waste because we're serializing and sending all those extra records to our app.

Is there an efficient way to accomplish the job of occluding the overridden records in our initial query?

In Postgres, typically the most efficient way is to use distinct on ()

select distinct on (started_on) started_on, recorded_on, image_id
from the_table
order by started_on, recorded_on desc
where started_on between ...;


Ask about Inner Join & Union i want to ask about my script this is doesn't work. i need to help !

SELECT A.*,B.mat_nama,C.mats_nama
FROM pengeluaran AS A
INNER JOIN(material AS B) ON(A.mat_id = B.mat_id)
INNER JOIN(mat_satuan AS C) ON(B.mat_satuan_id = C.mats_id)
WHERE A.mat_id = '7'
   AND A.pengeluaran_tgl BETWEEN '01/01/2014'  AND '02/28/2014'

UNION

SELECT A.*,B.mat_nama,C.mats_nama
FROM penerimaan_sub AS A
INNER JOIN(material AS B) ON(A.mat_id = B.mat_id)
INNER JOIN(mat_satuan AS C) ON(B.mat_satuan_id = C.mats_id)
WHERE A.mat_id = '7'
  AND A.penerimaan_tgl BETWEEN '01/01/2014' AND '02/28/2014'

Error Notification in PhpMyadmin:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON(A.mat_id = B.mat_id) INNER JOIN(mat_satuan AS C) ON(B.mat_satuan_id = C.mats_' at line 1

Try This

SELECT A.*,B.mat_nama,C.mats_nama
FROM pengeluaran AS A
INNER JOIN material AS B ON A.mat_id = B.mat_id
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
   AND A.pengeluaran_tgl BETWEEN '2014-01-01'  AND '2014-02-28'

UNION

SELECT A.*,B.mat_nama,C.mats_nama
FROM penerimaan_sub AS A
INNER JOIN material AS B ON A.mat_id = B.mat_id
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
  AND A.penerimaan_tgl BETWEEN '2014-01-01' AND '2014-01-28'


MongoDB convert standalone mongod to sharded replica set cluster I have a single replica set having 3 mongod instance, collection in this instance has data already.

I want to convert this instance to sharded cluster. I followed Convert a Replica Set to a Sharded Cluster but when I access my mongod at the end of the setup it doesn't show the existing data.

Any suggestions?

You have config server replica set installed?
You are running at least one mongoS -service
You are connecting (with mongo) to mongoS when you are working with cluster
You have added your replica set(s) to the cluster with sh.addShard() -command
You have added (at least one) your databases to cluster with command sh.enableSharding("yourDBname")

This point your collection is still there, sitting on original location with all data. You still need to shard your collections, which data is needed to "spread" to other shards (nodes).


MySQL Cluster ndb_desc and parttions I have mysql cluster with two datanodes and noofreplicas=2. According to documentation there must be 1 nodegroup, 2 partitions and 4 parts at all (2 primary and 2 backup).
I have a table with 1000000 rows. I want to know how they distributed.

[root@localhost ~]# ndb_desc users -d test_1 -np
-- users --
Version: 10
Fragment type: HashMapPartition
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 289
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
HashMap: DEFAULT-HASHMAP-3840-4
-- Attributes --
user_id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
user_data Longvarchar(1024;latin1_swedish_ci) NULL AT=MEDIUM_VAR ST=MEMORY
-- Indexes --
PRIMARY KEY(user_id) - UniqueHashIndex
PRIMARY(user_id) - OrderedIndex
-- Per partition info --
Partition   Row count   Commit count    Frag fixed memory   Frag varsized memory    Extent_space    Free extent_space   Nodes
0           249356      249356          8028160             11304960                0               0                   1,2
2           250249      250249          8060928             11337728                0               0                   1,2
1           250663      250663          8060928             11370496                0               0                   2,1
3           249732      249732          8028160             11304960                0               0                   2,1

NDBT_ProgramExit: 0 - OK

Why there are 4 partitions with ~250000 rows at each instead of 2 partitions with ~500000 rows?

With noofreplicas=2 and two nodes, you will have 2/2 = 1 nodegroup, meaning that you have no sharding at all (or one shard, as you prefer). All your partitions will be in both nodes. Your cluster will provide you high availability and better read throughup, but not write scaling.

What you show are the statistics for the table users, if you have not defined a custom partitioned schema, NDB will manage the partitioning for you using a hash on the primary key, in this case 4 partitions are created. Out of those 4 partitions, probably 2 will be "primary" on one node and "backup" in the other, and vice versa. You can define your own partitioning schema with standard MySQL Syntax (although having less partitions may reduce concurrency -MaxNoOfExecutionThreads- and future scaling -making the adding nodes more painful). You can know which partition each individual row is with EXPLAIN PARTITIONS.

Think of NDB as an implementation of RAID 10, but as you only have 2 nodes, you only get a RAID1. Your "block level" is a partition, each the default partition selection uses the hashing used for KEY() partitioning, which I believe it is based on the PASSWORD() function.

I believe the number of partitions by default, as you say, should be equal to the number of nodes (2), but I believe that it can create it with a larger number if you setup a larger MAX_ROWS, so it is not fixed.

Edit thanks to extra information: The other reason why partitions can be set higher by default is because, to allow for higher concurrency -in case you are using ndbmtd-, the number of partitions get multiplied by the Local Data Manager instances (in a nutshell, how many threads can potential read and write to a data node in concurrency). When you set MaxNoOfExecutionThreads to 4, as in your case, 2 of them are dedicated to that in the multithreaded server.

I must apologize for not thinking about this before, but there are tons of small changes from version to version, and the number of partitions are usually 4 in shard-enabled 4-node setups.

Do not worry too much, as even if you had extra partitions of that size (as you can force manually, to some extent) is not a bad thing at all. Partitions (or fragments) are just the arbitrary unit that is indivisible at node level. As I commented earlier extra partitions can provide additional performance by pruning non-desired rows (assuming you are partitioning correctly and using mostly key-value reads), and make easier the addition of extra nodes.


What does GRANT CONTROL on a schema do in MSSQL? I'm using SQL Server 2016 for these tests.

The following did not allow me to create a table in schema S for user U

USE [D];
GRANT CONTROL ON SCHEMA :: [S] TO [U];

But this did:

USE [D]
GRANT ALTER ON SCHEMA :: [S] TO [U];
GRANT CREATE TABLE TO [U];

What is the GRANT CONTROL supposed to do, if you have to run these two additional commands to allow the user to create a table in a specific schema?

This description is nice, but what kind of a use case can I run that can confirm (with success or failure) the GRANT CONTROL command physically affected the user's security on the database with and without this schema, before and after running the command?

https://blogs.msdn.microsoft.com/lcris/2009/08/11/basic-sql-server-security-concepts-ownership-control-take-ownership/

  CONTROL permission: The CONTROL permission can be used to easily grant
  all permissions on an entity to some principal. It's the next best
  thing after ownership of the entity, but it's not quite as powerful as
  ownership. The main difference is that a grantee of CONTROL can still
  be denied some other permissions on the entity. For example, I can be
  granted CONTROL on a table, while at the same time I can be denied
  SELECT on that table, preventing me from selecting from it - this can
  never happen to the owner, because the owner cannot be granted or
  denied permissions.

To see what CONTROL on schema brings to you, create a test user without any permission, grant CONTROL on schema to it and check what permissions he has after it:
create user test;
grant control on schema::dbo to test;

execute as user = 'test';
select *
from sys.fn_my_permissions('dbo', 'schema');
revert;

Here is what you'll get:

When you want to CREATE TABLE you should open BOL article CREATE TABLE (Transact-SQL) and scroll down to PERMISSIONS:

Now you see that even if your table is not supposed to have columns of CLR user-defined type, you still need CREATE TABLE permission to grant to your user to make him be able to create a table.
Test user has already ALTER SCHEMA implied by CONTROL on schema, but CREATE TABLE should be granted (as well as REFERENCE permission in case of CLR user-defined type).


MS SQL Server Database Option - Transaction Log I have a database for staging purpose before move into another main database. It has 5-6 millions records on average. The transaction log keep growing after few days to 30-40 gig. The transaction record not important because I can retrieve from my source again if needed. There are some configuration details needed only.
What's the best way to manage transaction log ?
What database recovery mode should I set - Simple, Full, Bulk Logged ? By default, I selected Full.
Should I just schedule a job to purge the transaction log on a daily basis ?
Any recommendation ? Thanks.

The transaction record not important because I can retrieve from my source again if needed.
. . .
What database recovery mode should I set

SIMPLE.  Then shrink the log one time.


One to one relationship naming conventions I'm designing an ER diagram with MySQL Workbench.

I have two tables with a one-to-one relationship. Both tables have one column as primary key, but they don't have the same name.

Take a look:

But when I have added the relationship, it has added automatically the column Code_CodeId.

Is there a naming convention about how can I name primary columns with a one-to-one relationship? Do must have the same name? Why it has added that column?

Is there a naming convention about how can I name primary columns with a one-to-
  one relationship?

I am sure there are plenty of them. Choose one.

For example, I would name the reference

CodeREF

And if having multiple I would use describtive names as per the objects that I will manifest (RawCoderREF, ReviewedCodeREF)


PaaS Server - Random row order while using aggregate function with group by Result set of a query with an aggregate function and group by (no order by specified) returns different result or at least in different order almost in each execution.

I have a database on Azure PaaS server and it's behaving weirdly. The query in issue is something like this:

SELECT TOP 100 "Org Unit", count(*) FROM "paas-server-12032019"."dbo"."testTable" GROUP BY "Org Unit"

When I check the "Org Unit" order, it's different each time. If I omit, count(*) and just keep the query as

SELECT TOP 100 "Org Unit" FROM "paas-server-12032019"."dbo"."testTable" GROUP BY "Org Unit"

It works as expected. Order of the record is consistent each time.
What I noticed

It happened with a specific database, I have another one and in there it works as expected.
Secondly, I think it has something to do when the column in group by is nvarchar(max)

I have a limited access currently to DB setup but wondering if there is anything that can cause this issue.

Any sort of help/discussion is deeply appreciated!

Without ORDER BY, you tell SQL Server that you don't care about the ordering.

SQL Server can do GROUP BY and aggregation internally by either sorting data or through hashing. SQL Server decides this based on what it expects to be the more efficient way. For your case when the order isn't consistent, SQL server found it more efficient to use hashing, apparently. You can see this in the execution plan.

Bottom line is, as always, if you want some specific order for the rows, then you need to use ORDER BY.


How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to OFF/ON against each DB and if its OFF- alter it to ON.

ALTER DATABASE  DB
SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE  DB
SET AUTO_UPDATE_STATISTICS ON

How can i achieve this?

This uses a Dynamic SQL query. It excludes system databases. Checks for is_auto_create_stats_on or is_auto_update_stats_on 0 OFF and change it to ON

declare @sql    nvarchar(max);

; with
db as
(
    select  name, is_auto_create_stats_on, is_auto_update_stats_on
    from    sys.databases
    where   name    not in ('master', 'tempdb', 'model', 'msdb')
    and     (
                is_auto_create_stats_on = 0
            or  is_auto_update_stats_on = 0
            )
)
select  @sql    = isnull(@sql + char(13), '')
                + 'ALTER DATABASE ' + quotename(db.name) + ' '
                + 'SET ' + o.opt + ' ON;'
from    db
        cross apply
        (
            select  opt = 'AUTO_CREATE_STATISTICS'
            where   is_auto_create_stats_on = 0

            union all

            select  opt = 'AUTO_UPDATE_STATISTICS'
            where   is_auto_update_stats_on = 0
        ) o

-- print out for verification before execute
print   @sql
exec    sp_executesql @sql

You may mask the sp_executesql and verify the result before actual execution


Why would recompile query hint result in different plan for same adhoc statement after freeproccache? This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.

I've got an adhoc sql statement that gets a different query plan solely because of  OPTION(recompile). The batch statement is never (in practice) being reused as it is using non-parameterized dynamic sql generation where the statement changes with dates / other parameters. I've confirmed this is the case by checking the cached plan_handle, always a different ID as values in batch change.

When I do dbcc freeproccache I expect that the query plan I get for the statement would be exactly the same as one with "option (recompile)" query hint added. But its not, its quite different actually (much much faster).

Does anyone know why addition of a query hint would cause the engine to pick a different query plan?

The statement is something like this

dbcc freeproccache
go
sp_executesql '
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate
and exists(
select A
union all select B
union all select C
)'

all I have to do is change the statement to use recompile query hint and I get different (much better) query plan.

dbcc freeproccache
go
sp_executesql '
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate
and exists( select A
union all select B
union all select C
) OPTION (RECOMPILE)'

As code is passing in the date values I can't use a plan guide to override the default behavior, looks like code change will be required. But I wish I knew where to look on why recompile would force a different plan to be used.

The query you posted contains variables.

SQL Server doesn't do variable sniffing so without OPTION (RECOMPILE) it will compile a general plan as it would for OPTIMIZE FOR UNKNOWN.

I don't really follow your question though. At one point you seem to be saying that the version without the hint is "much much faster" and  then later you say the version with the hint is "much better". So which one is it?

Both are explicable however. If you find the version with the hint is better than this is because SQL Server can use statistics to estimate the number of rows that will be matched by the date predicate and choose an appropriate plan for that case.

If the version without the hint is better the statistics themselves may need updating. Perhaps when they were last updated there were few or no rows meeting that predicate and so SQL Server massively underestimates the number of rows that will be returned. See Statistics, row estimations and the ascending date column for more about this potential issue.


Best way to copy tables without a primary key or unique constraint? Looking into synchronizing tables in a database across multiple servers using a DML method like MERGE, only to realize that some of the largest tables that take up the majority of the total size of the database do not have a primary key or unique constraint and therefore have duplicate data. As a result, doing a MERGE results in a full table scan, which explodes tempdb. For business reasons, I cannot alter the table structure or data itself.
Is there a way to copy these tables on a routine basis other than backing up and restoring the entire database?

AMtwo's point is without a primary key defined you have no reliable way to consolidate a record between a table on both servers. A primary key is the only way to guarantee row 137 is the same exact row 137 you're comparing it against between server 1 and server 2.
The closest thing you can attempt is use the HASHBYTES() function with a minimal collision hashing algorithm to hash the entire row, and then compare hashes. You can even goes as far as creating a computed column or an indexed view so you can index the hashed value and do quicker comparisons. The downside to this is with no primary key on the table means it's entirely possible two rows have exactly the same values across all columns within the same table and result in a duplicative join when comparing across servers resulting in excessive row comparisons.
The better solution from an accuracy standpoint would be to use a feature like Snapshot Replication on an appropriate schedule (e.g. hourly if that synchronization timeframe is acceptable but you can theoretically schedule it to run as soon as every second - though I wouldn't recommend this frequently). This essentially dumps the table on the target server and re-inserts all the rows from the source server, only for the tables you choose to replicate. You may want to even consider using a hybrid replication solution by implementing Transactional Replication for your tables that do have primary keys which offers near realtime synchronization.


Query Rows That Have a Following Row That Meets Criteria Will try to do my best to explain this, as I don't really know how to word this to search for an answer.  I'm a constantly learning but still newish SQL user who thus far has been able to solve most questions by searching, but can't think how to word this.

I want to pull a query, which is all contained in one single table.  The database is logging rows as follows;

User    Login Type

Bob        1

Ryan       1

Tom        1

Tom        3

I want the query to show me the rows that have a "1" login type immediately followed by a "3" login type.  So my query would result would look like this:

User  Login Type

Tom    1
Tom    3

How would I format such a query? Normally something like this would be easy by doing a few joins, but I can't seem to wrap my head around how to do it with only one table involved.  I've looked into joining the table into itself, but can't seem to nail down how to get the results I want (if that is even what I should be doing, it is the only thing that seems logical to me)

I've used MySQL, but it should apply to virtually all database servers.

CREATE TABLE `test1`
(
  `user` varchar(10) DEFAULT NULL,
  `logintype` int(11) DEFAULT NULL
);

mysql> SELECT * FROM test1;
+------+-----------+
| user | logintype |
+------+-----------+
| Bob  |         1 |
| Ryan |         1 |
| Tom  |         1 |
| Tom  |         3 |  <<== ALSO WORKS IF YOU HAVE ANOTHER
+------+-----------+       RECORD TOM, 5
4 rows in set (0.00 sec)

mysql>

SELECT t1.user, t1.logintype
FROM test1 t1
INNER JOIN test1 t2
ON t1.user = t2.user
AND t1.logintype != t2.logintype
GROUP BY t1.user, t1.logintype
ORDER BY logintype;

+------+-----------+
| user | logintype |
+------+-----------+
| Tom  |         1 |
| Tom  |         3 |
+------+-----------+


Do fixed-width rows improve PostgreSQL read performance? I have a table articles:
                                                       Table "articles"
     Column     |            Type             |                     Modifiers                      | Storage  | Stats target | Description
----------------+-----------------------------+----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('articles_id_seq'::regclass) | plain    |              |
 user_id        | integer                     |                                                    | plain    |              |
 title          | character varying(255)      |                                                    | extended |              |
 author         | character varying(255)      |                                                    | extended |              |
 body           | text                        | default '--- []                                   +| extended |              |
                |                             | '::text                                            |          |              |
 created_at     | timestamp without time zone |                                                    | plain    |              |
 updated_at     | timestamp without time zone |                                                    | plain    |              |
 published_date | timestamp without time zone |                                                    | plain    |              |

Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "index_articles_on_published_date" btree (published_date)
    "index_rents_on_user_id" btree (user_id)
    "index_articles_on_user_id_and_published_date" btree (user_id, published_date)

We're on Postgres 9.4.4. The machine has 3.5 GB of memory and 150 GB of disk space on an SSD.
Note: The 'published_date' is always rounded, by the application, to the nearest date. All hours/minutes/seconds are always 00. Legacy. Needs fixed. Etc.
This table has hundreds of millions of articles. The table receives a great deal of read queries from (as many as 16) concurrent processes performing the following queries as quickly as our system will respond:

a count of the total number of articles
SELECT COUNT(*) FROM articles;

a select of all articles published for a given user
SELECT * FROM articles WHERE user_id = $1;

a select of the most recently published article for a given user
SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

I am finding that, with a large number of workers, these queries are quite slow. (At peak load, the first takes minutes to complete; the other two are on the order of 10 seconds.) In particular, it seems that queries are being enqueued.
The question
In the abstract, do tables with only fixed width values perform read queries better than those with varying widths? (Pretend disk space isn't an issue.) In my case, I'm wondering if I would see a performance improvement if I were to extract the 'body' text field to a separate table and transform the character varying fields into fixed width character fields.
I admit the question is a bit cargo cult-y. I simply don't know enough about the internals of the Postgres DB engine to construct an informed hypothesis. I do intend to perform real experiments with different schemas and configurations but I'd like to have a solid mental model of how Postgres actually works before I go much further.
Related question
Where can I learn more about the internals of the Postgres DB engine? I've Googled variations of the above question with little success. What are the correct terms to use for this search? Does this level of documentation exist only in source and the minds of Postgres DBAs? I also humbly invite the suggestion of good books on the topic.

Do tables with only fixed width values perform read queries better
than those with varying widths?

Basically no. There are very minor costs when accessing columns, but you won't be able to measure any difference. Details:

Does the order of columns in a Postgres table impact performance?

In particular:

There is no difference in performance between character varying(255) and text at all. You seem to be under the impression that varchar(255) (unlike text) might be a "fixed-width" type, but that is not so. Both are variable-length types, varchar(255) just adds a maximum length check:
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

The use of varchar(255) in a table definition typically indicates a lack of understanding of the Postgres type system. The architect behind it is most probably not a native speaker - or the layout has been carried over from another RDBMS like SQL Server where this used to matter.

Your most expensive query SELECT COUNT(*) FROM articles does not even consider row data at all, only the total size matters indirectly. Counting all rows is costly in Postgres due to its MVCC model. Maybe an estimate is good enough, which can be had very cheaply?
Fast way to discover the row count of a table

(Pretend disk space isn't an issue.)

Disk space is always an issue, even if you have plenty. The size on disk (number of data pages that have to be read / processed / written) is one of the most important factors for performance.

Where can I learn more about the internals of the Postgres DB engine?

The info page for the tag postgres has the most important links to more information, including books, the Postgres Wiki and the excellent manual. The latter is my personal favorite.
Your third query has issues
SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

ORDER BY published_date DESC, but published_date can be NULL (no NOT NULL constraint). That's a loaded foot-gun if there can be NULL values, unless you prefer NULL values over the latest actual published_date.
Either add a NOT NULL constraint. Always do that for columns that can't be NULL.
Or make that ORDER BY published_date DESCNULLS LAST and adapt the index accordingly.
"articles_user_id_published_date_idx" btree (user_id, published_date DESC NULLS LAST)
Details in this recent, related answer:

Extremely slow query on indexed column in Postgres

Convert published_date to an actual date
While 'published_date' is always rounded, it's effectively just a date which occupies 4 bytes instead of 8 for the timestamp. You would best move that up in the table definition to come before the two timestamp columns, so you don't lose the 4 bytes to padding:
...
body           | text
published_date | date   --     <---- here
created_at     | timestamp without time zone
updated_at     | timestamp without time zone

Smaller on-disk storage does make a difference for performance.

Configuring PostgreSQL for read performance

More importantly, your index on (user_id, published_date) would now just occupy 32 bytes per index entry instead of 40, because 2x4 bytes do not incur extra padding. And that would make a noticeable difference for performance.
Aside: this index is not relevant to the demonstrated queries. Delete unless indexes unless used elsewhere:
"index_articles_on_published_date" btree (published_date)


What happens with a synonym if the table which its based on gets deleted? we are working on a project to implement a large ERP system. We found out that one of the tables has a synonym to another database. Both databases are in the same instance. Our project leader has decided that one table has to be deleted. This table turns out to be the source table of the synonym.

In my opinion does this also effect the synonym. Am I right?
I think, because the source table is deleted, the synonym cannot retrieve any data anymore and will have empty columns.

In this case the synonym should be properly deleted, if not, it cannot be used but still exists on sys.synonyms catalog.

You can check it running next script:

IF OBJECT_ID('T1') IS NOT NULL
    DROP TABLE T1;
GO

IF OBJECT_ID('NEW_T1') IS NOT NULL
    DROP SYNONYM NEW_T1;
GO

CREATE TABLE T1(f1 int);
GO

INSERT INTO T1 VALUES (1),(2),(3);
GO

CREATE SYNONYM NEW_T1 FOR T1;
GO

SELECT * FROM NEW_T1;
GO

f1
-----------
1
2
3

DROP TABLE T1;
GO

SELECT * FROM NEW_T1;
GO

Msg 5313, Level 16, State 1, Line 23
Synonym 'NEW_T1' references an invalid object.

SELECT name, object_id FROM sys.synonyms WHERE name = 'NEW_T1';
GO

name        object_id
------      --------------
NEW_T1      1463676262

IF OBJECT_ID('NEW_T1') IS NOT NULL
    DROP SYNONYM NEW_T1;
GO


Grant Permission to Execute Set Transaction Isolation Level I'm a part-time accidental SQL Server 2005 DBA whose full-time job is developing business intelligence solutions, but where the BI intersects with the database, it falls in my 'stack'.

We have a number of scheduled reports that failed when another developer needed to add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in several stored procedures running against an enterprise application database.

It turns out that the login used by the reporting tool didn't have the correct permissions.  It could execute the stored procedures, but not with the SET TRANSACTION ISOLATION LEVEL added.

I was able to get around it temporarily by adding the db_owner role to the login properties for the database.  It already had db_datareader, db_datawriter, and public. But it seems like I should be able to grant a permission that would alleviate the problem without giving the login the db_owner role.

I've read through all the information on GRANT in the SQL Server books online, but can't find what permission to grant.

Can anyone help me out?  If you know of a resource, I'd love to know about it.

Thanks!

First a quick point .. It's pretty unusual someone "needs" to use read uncommitted unless the need is to randomly get bad data.

Lecture done, permissions are generally found under individual commands.  In this case you are looking at SET TRANSACTION ISOLATION LEVEL.

Typically you would find a Security/Permissions section, although in this case there isn't one.  I believe that is because this is a command that anyone can run (although some isolation levels have to be enabled).  Another place you can look for permissions is under the properties of the database or server.  You will see a Permissions tab that will have a list of available permissions.  But again I didn't find anything related to the command.

I'm mostly stumped by the fact that you granted db_owner and they worked.  My guess is that it isn't the SET ISOLATION LEVEL that is actually causing the issue.  Can you please include the actual error you are getting?


missing frm files for restore We backup a database daily with mysqldump using the following options:

--quick --hex-blob --lock-tables --skip-extended-insert  --flush-logs --max_allowed_packet=500M

Each database gets its own directory and that directory has a .sql file for each table in the database.

I recently tried to do a test restore and I get the following error over and over for each table in the .sql file I try to import:

  ERROR 1017 (HY000): Can't find file: './WhateverTableIQuery.frm'
  (errno: 13)

The .frm files are indeed on the source machine but all I'm getting in the backup is tarballs of .sql files.

Am I doing the backup wrong or the restore wrong?

$ perror 13
OS error code  13:  Permission denied

Fix the permissions so that "mysql" can read the file.  Keep in mind that the OS 'root' and the mysql 'root' are not the same.


Records in table not showing on form Access 2007 Excuse my ignorance in asking this question - I am a database novice and need help!

I will explain as best I can.

In the table on my DB I have 235 records.  I have created a form which references that table and which is pulling through only 226 of those records - for some reason it will not display the last 9.

What have I done wrong?

I input all the data using an import so I did them all the same way.

Thanks and please let me know if I have not made sense I can explain further!

If you import data and it seemingly disappears, you need to check the indexes on that table. You should do the following:

Switch the table into Design View
In the Table Tools Section, under the Design Tab, click on Indexes
You will see a list of Indexes pop up

If any of the Indexes have one or more columns that are unique, those rows may have vanished upon import. You may need to either remove all indexes (do not touch the PrimaryKey) or Change the Unique Property on all Indexes from Yes to No.

Then, just reimport the data.

If all 235 rows are in the table already, go to your form's Property sheet. Click the Data Tab. Look at the Record Source and see if it is a table name or query.

You need to also go back to the Property Sheet, Click the Format Tab. Look over the Default View and set it to Single Form. This will view the table rows one row at a time. Click the Last Record Navigation Button. It had better say 235 of 235.


MySql pivot table for scatter chart I would like to transform the following sql (e.g. select Name,X,Y from mytable) result:

Name  | X   | Y
bla1  | 0.1 | 0.2
bla2  | 0.3 | 0.4

Into the following form that is displayed correctly in an Excel scatter chart (i.e. with each row as a series and each name as a label):

Name  | 0.1 | 0.3
bla1  | 0.2 |
bla2  |     | 0.4

This is the only way a scatter chart will display x-y data correctly without fiddling with chart settings:

Eventually I figured this out with help from this stack overflow post.

    SELECT CONCAT(
      'SELECT `mytable`.Name', GROUP_CONCAT(CONCAT('
         ,    `t_', REPLACE(Y, '`', '``'), '`.X
             AS `', REPLACE(Y, '`', '``'), '`'
       ) SEPARATOR ''),
     ' FROM `mytable` ', GROUP_CONCAT(CONCAT('
         LEFT JOIN `mytable`   AS `t_', REPLACE(Y, '`', '``'), '`
                ON `mytable`.Name = `t_', REPLACE(Y, '`', '``'), '`.Name
               AND `t_', REPLACE(Recall, '`', '``'), '`.Y= ', QUOTE(Recall)
       ) SEPARATOR ''),
     ' GROUP BY `mytable`.Name'
    ) INTO @qry FROM (SELECT Recall FROM `mytable` WHERE ... ) t;
    PREPARE stmt FROM @qry;
    EXECUTE stmt;

Initially this was not working because the datatype of columns X and Y was FLOAT which I suspect was messing up the conversion to string. I changed the type to be DECIMAL(10,9) (i.e. a single digit, followed by up to nine decimal points), which made the code work.


How can I re-index a column using SQL Server Compact Edition? We have a table TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical.

The query requirement is that the column ListIndex has to start from 0 and be sequential for each parent / child(ren) relationship.

The query I came up with is as follows:

SELECT  Id,
        ParentId,
        Sub.NewIndex AS ListIndex

FROM    TemplateItem
JOIN    (
            SELECT  Id,
                    ROW_NUMBER() OVER
                    (
                        PARTITION BY ParentId ORDER BY ListIndex
                    )
                    - 1 AS NewIndex

            FROM    TemplateItem
        )
        AS Sub ON TemplateItem.Id = Sub.Id

( unnecessary bits of query removed for readability )

This works perfectly fine in SQL Server 2008 R2.

However, I now need to use this same functionality in SQL Server Compact Edition 4.0 but, as you may already know, it will fail.

It does so because there is no ROW_NUMBER() in SQL Server Compact Edition 4.0.

How can I accomplish this same functionality?

Since you can't generate the ID artificially with a windowing function, you will need to add a step and create it as an IDENTITY field in another table.

Something like this

            --temp table to store all the new data
    CREATE TABLE #Hierarchy
    (
          HId INT IDENTITY
        , ParentID INT
        , ListIndex INT
    )

    --distinct parentids in hierarchial model
    SELECT DISTINCT ParentID
    INTO #Parents
    FROM TemplateItem

    --looping variables
    DECLARE
          @loopCnt INT
        , @curParentId INT

    --set looping variables (not a fan of cursors)
    SELECT @loopCnt=COUNT(ParentID), @curParentId=-1
    FROM #Parents

    --loop through parentids and reset the ListIndex
    WHILE @loopCnt > 0
    BEGIN
        --get the next ParentID
        SELECT @curParentId=MIN(ParentID)
        FROM #Parents
        WHERE ParentID > @curParentId

        --temp table to store the refreshed ListIndexes by ID
        CREATE TABLE #NewIndex
        (
              NewIndex INT IDENTITY
            , Id INT
        )

        INSERT #NewIndex
            (Id)
        SELECT Id
        FROM TemplateItem
        WHERE ParentID = @curParentId
        ORDER BY ListIndex

        INSERT #Hierarchy
            (ParentID, ListIndex)
        SELECT  t.Id,
                t.ParentId,
                Sub.NewIndex - 1 AS ListIndex -->Reset to 0 here

        FROM    TemplateItem t
        JOIN    #NewIndex Sub ON Sub.Id = t.Id

        --CleanUp and continue/stop loop
        DROP TABLE #NewIndex
        SET @loopCnt=@loopCnt-1
    END


Best way for naming schemas in SQL Server 2019 Would like to ask about using of schemas in Sql Server 2019. For now we are using numbers as schemas for procedures and functions:

0,1,2 etc. to version procedures and functions. For example [1].[GetFirstName] is the newest version of procedure but we have also node where we has to use [0].[GetFirstName] procedure (for example to compare efficiency between old and new).

For tables and views we are not using schemas but I am thinking about adding below:

conf - Configuration - for table which store confiuration data
dict - Dictionary - for table which store dictionaries
log  - Logging - for table which store logs
proc - Processing - for tables which wille be use to massive updates and insert commands (~ 300 insert/updates per seconds)

My questions:

Do you think that using procedure version as schema is a good idea ?
Is it good idea to have different idea for schemas in tables/views (responsible of table) and in procedure/functions (versioning)
Do you think that using below schema preposition is a good idea ? What problems can I have in future in process of addministration/change on production ?
Do you have any idea (except schemas) how to name tables with config,dictionaries,processing ? Is it worth to divide them in groups by for example add substring at the end (StatusDict, EventDict, StatusConfig, EventConfig, TransactionProc) etc. ? I would add that I am using naming convenction from: SQL Server Name Convenction. Beacuse of naming tables like I describe above the ID for columns will be StatusDictID, EventDictID instead of StatusID, EventID.

Tank you in advance for answers.

My personal opinion is that it is not a good idea. I tend to think of the schema as a namespace, to logically group related things together.

Versioning should be handled (IMO) no different from other code, i.e. via a source control system. Changes to the data model should be handled via upgrades/migrations.

That aside, do you make a new copy of every procedure function when you change one procedure, or do you have an individual "latest schema" for every object? How does your code figure out what is the latest version of a particular procedure?


how to reduce transaction log size automatically? Possible Duplicate:
  Why Does the SQL Server Transaction Log Keep Growing or Run Out of Space?

how can i reduce the size of Transaction log programatically and automatically, because no one knows when the when it gets full since how can we manage this can any one suggest for this

You need to first understand what type of recovery (from a point in time standpoint) you want to get out of your SQL Server environment.

Do you want to be able to restore to a specific point in time? Then you want to remain in full recovery mode and you need to start taking transaction log backups (the log file will continue to grow in full mode until you take a log backup, as described in the linked blog post below)

If you are fine going to just the last full backup and not a specific point in time, then you can switch to simple as @andomar's answer suggests. (in this model, if you backup at 8PM each night and have a failure at 7:30PM, you have to go back to 8PM last night. This is fine in many applications but you need to be sure your business is okay with this. This blog post talks about customer expectations around recovery.

If you want to reduce the size of your transaction log file after it has grown due to mismanagement you can shrink. But I would NOT shrink the entire database as another answer suggests. That will cause other issues. It is generally speaking not a best practice to ever shrink your database. I would do a SHRINKFILE instead.

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

This command is described here.

If you want to understand the mechanics deeper, I wrote a blog post a few years ago that discusses transaction log growth. Proper management is best because then you won't have to worry about finding an automatic way to shrink it.


Basic ERD question: how many entities to use Say there are Buyers, Sellers, and Reviewers, all have common attributes to them such as:

name
address
login-info (username, password, email)

and all three have abilities unique to their roles.

e.g. buyer can buy, seller can sell, reviewer can review

Assuming a person can be only one of the three roles, what is the best way to design an ERD for this scenario?

Would having 3 separate entities for each role + an entity for LoginInfo having a relationship with all 3 of role entities be good?

If so, would it be redundant to have name, address in all 3 role entities?

Generally in these cases its best to have a single entity with flags for which type the person is. In many cases as your system is used, you will likely find someone can be any combination of these. And if they update their name/address/username/password, you want it updated for ALL of their roles simultaneously. This lends to using a single entity with flags for their types:

Person
-------
PersonID
FirstName
Surname
...
AddressLine1
AddressLine2
...
Username
Password
...
Seller Boolean
Buyer Boolean
Reviewer Boolean

EDIT:

Alternatively, as suggested by John M and depending on if you are going to create more Person Roles, you could have 2 entities with a lookup between them identifying the type of roles a person is able to perform:

Person
-------
PersonID
FirstName
Surname
...
AddressLine1
AddressLine2
...
Username
Password

A Role table

Role
------------
RoleID
RoleDescription

(this table would contain Seller/Reviewer/Buyer to start with)

Then this link table:

PersonRole
------------
PersonID
RoleID

This would enable you to add Roles easily in the future without modifying your Person table. So if you created a Role such as "Editor", you would only need to create the row in the Role table and Inserts for the people you want to add as Editors into the PersonRole table.


Mysql repetition of column in Criteria What happens when I repeat the same column in the criteria part but with different values . For example

col1 ='val1' or col1 = 'val2' or col1 = 'val3' .

Assume col1 is not indexed. Will mysql perform entire table scan for each comparison? What will be its behaviour?

If the column is not indexed then it does not matter how many times (or with how many values) it is used, a table scan seems inevitable (unless there is some other indexed condition).

But there is no need to scan multiple times. MySQL optimizer tries to find the most effective plan, if nothing better is possible, it iterates over the table and checks each row for all conditions at the same time.

If an index is available (and statistics say it is effective), then ORs (on the same column) and IN are treated the same way - a "range" scan on the index is executed, fetching only matching rows.


How can I filter query results based on whether they have ALL values from another table? For example, some actions that users can do, require a set of permissions.
Users are stored in the Users table:

UserId

1

2

and permissions in the Permissions table. Users can have permission A, B, or both.

UserId
Permission

1
A

1
B

2
A

Required permissions are stored in RequiredPermissions. From this, the POST action requires both permissions A and B

ActionType
Permission

POST
A

POST
B

GET
B

I want to query all users that can POST (user 1). This query would give users that have any permissions (that is, user 1 and 2):
select distinct u.UserId
from Users as u
join RequiredPermissions as rp
  on rp.ActionType = "POST"
join Permissions as p
  on u.UserId = p.UserId

How do I query users which have both permissions A and B with this schema?

Relational division is a less common type of query than the more typical joins and unions. It's a little more awkward in SQL.
You want to know the users who have all the permissions required by POST.
Another way to state this is to return users for whom no permission required for POST is missing from their permissions.
SELECT u.UserID
FROM Users AS u
WHERE NOT EXISTS (
  SELECT *
  FROM RequiredPermissions AS rp
  LEFT OUTER JOIN Permissions AS p
    ON rp.Permission = p.Permission AND u.UserId = p.UserId
  WHERE rp.ActionType = 'POST'
    AND p.UserId IS NULL)

The way it works is that the OUTER JOIN will make p.* NULL if there is no match. So if the outer query wants there to be no case where a required permission has no match for the respective user, then that user must have all the required permissions.
This automatically handles actions with 3 or more permissions. The other solutions for relational division that I've seen require you to make code changes to your query for the number of items you are searching for.


What is the language of my SQL Server setup files? Im trying to install SQL Server in my Windows Server in english . But got this error.

I look for a solution and say I have to change the setting on Control Panel to match my media.

The problem is not sure what is the lenguaje of my sql server install disk.

How can I know what is the lenguaje of my Setup Disk?

Within the root directory / folder of the install media, there should be several sub-directories/folders, including: x64, redist, resources, etc. One of those sub-directories will have a name starting with a 4-digit number and then 3 letters, possibly followed by another short group of letters:

####_AAA[_AA?]

That first group of 4-digits is the LCID, and the 3 letters are the language code. My installs for several versions now all have the following folder:

1033_ENU_LP

While the LCID is highly specific with regards to which language, the ENU portion at least matches the first pop-up you posted in the question that mentions:

  ... ENU localized files.

If you want to know what the LCID translates to, please see the following list on MSDN: Locale IDs Assigned by Microsoft. The number in the directory / folder name will match something in the far-right column: LCID Dec. For example, 1033 indicates a locale of: "English - United States".


Trigger for a particular row? I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like:

CREATE TRIGGER Trig
AFTER UPDATE ON Sells
FOR EACH ROW
WHEN (row_id="something")
    EXECUTE PROCEDURE sendNotif();

So with this only when my particular row is edited only then my sendNotif() is called.

Is this a good approach ?? Is there a benefit when compared to trigger on the whole table ??

I am doing this because I want to send notifications only for rows which have a subscription.

EDIT: Scenario explained:

So I have two tables SELLS and SUBSCRIPTION. When a user wants to subscribe to sells resource (row), it creates a subscription resource (row). Once subscription resource is created, notification needs to sent whenever CRUD operations are performed to the subscribed-to (Sells) resource. So subscription resource is created once and after that notification is sent based on events (CRUD) on Sells resource. That is why trigger was on Sells resource to track it's changes.

Dynamically creating multiple triggers for individual rows sounds quite nasty. This is not likely to scale well.

After reading your update I would suggest you implement 1 trigger that fires for all CRUD operations on the SELLS table. This trigger checks to see if the modified rows of SELLS are in the SUBSCRIPTION table. If they are send a notification, if not don't send notification.


pgsql - Delete data from huge table where not in I need to delete some rows from one large table. The rows to delete shouldn't be in another table, example:

DELETE FROM LargeTable WHERE id IS NOT IN (SELECT DISTINCT foreign_id from EvenLargerTable)

but my server can't handle such blunt query, because there are almost a million records in the LargeTable and few million records in the EvenLargerTable

How can I solve it?

For large tables used for test of existence NOT EXISTS frequently works faster then NOT IN. So try

DELETE FROM LargeTable
WHERE NOT EXISTS (
  SELECT *
  FROM EvenLargerTable
  WHERE EvenLargerTable.foreign_id = LargeTable.id);

It is hard to explain why it's happening without having actual data. But with simple playground we can see that NOT IN case does not used indexes to perform the operation:

Playground:

-- drop table if exists a;
-- drop table if exists b;
create table a as select (random()*1000)::int as x from generate_series(1,10000);
create index idx_a on a(x);

create table b as select (random()*1000)::int*10 as x from generate_series(1,1000000);
create index idx_b on b(x);

analyse a;
analyse b;

Tests:

nd@postgres=# explain (verbose) delete from a where a.x not in (select b.x from b);
╔══════════════════════════════════════════════════════════════════════════════════╗
║                                    QUERY PLAN                                    ║
╠══════════════════════════════════════════════════════════════════════════════════╣
║ Delete on nd.a  (cost=0.00..129160170.00 rows=5000 width=6)                      ║
║   ->  Seq Scan on nd.a  (cost=0.00..129160170.00 rows=5000 width=6)              ║
║         Output: a.ctid                                                           ║
║         Filter: (NOT (SubPlan 1))                                                ║
║         SubPlan 1                                                                ║
║           ->  Materialize  (cost=0.00..23332.00 rows=1000000 width=4)            ║
║                 Output: b.x                                                      ║
║                 ->  Seq Scan on nd.b  (cost=0.00..14425.00 rows=1000000 width=4) ║
║                       Output: b.x                                                ║
╚══════════════════════════════════════════════════════════════════════════════════╝

nd@postgres=# explain (verbose) delete from a where not exists (select * from b where a.x=b.x);
╔══════════════════════════════════════════════════════════════════════════════════╗
║                                    QUERY PLAN                                    ║
╠══════════════════════════════════════════════════════════════════════════════════╣
║ Delete on nd.a  (cost=0.42..5005.91 rows=1 width=12)                             ║
║   ->  Nested Loop Anti Join  (cost=0.42..5005.91 rows=1 width=12)                ║
║         Output: a.ctid, b.ctid                                                   ║
║         ->  Seq Scan on nd.a  (cost=0.00..145.00 rows=10000 width=10)            ║
║               Output: a.ctid, a.x                                                ║
║         ->  Index Scan using idx_b on nd.b  (cost=0.42..20.78 rows=999 width=10) ║
║               Output: b.ctid, b.x                                                ║
║               Index Cond: (a.x = b.x)                                            ║
╚══════════════════════════════════════════════════════════════════════════════════╝


Localization: One table or multiple tables? (MySQL) I need to be able to store string messages in various languages. I guess I need a column for each language string.
For example my columns could be:

ID | message_EN | message_DE | message_FR

Now, I have strings in various tables in the DB. Should I use one large string table with the above structure (or similar) where most other tables will be joined (via string ID), or have multiple tables with the same structure each related to one table?
Most probably there will not be much overlap between the data of multiple tables.
Are there any performance considerations with using one large table?

Thank you.

This is clear example of Many-to-Many relation (M:N).
Many messages can have (be translated into) many languages.

Date structure for that has three tables:

create table Message (
  ID int primary key,
  message text
);

create table Language (
  ID int primary key,
  language varchar(50)
);

create table Message_Language (
  message_id int,
  language_id int,
  contraint Message_Language_Message_fk foreign key(message_id) references Message(id),
  contraint Message_Language_Language_fk foreign key(language_id) references Language(id),
  constraint Message_Language_PK (message_id, language_id)
);

It's more modular and you can add more languages easily.

What kind of queries are you performing on the tables.
Provide more info about complete data structure (and other tables).

Are you aware of data normalization in RDBMS?


ERROR: database "dbname" does not exist According to the documentation, so long as I'm not connected to a database, I can either delete a database in the console using:

DROP DATABASE dbname;

Or I can drop it using the wrapper tool dropdb.

Both give me an error saying the database doesn't exist, yet when in the console and typing the command \l, I get a list of databases including the one I want to delete.

                                          List of databases
           Name            |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
---------------------------+-----------+----------+-------------+-------------+-----------------------
 Blog_development          | myusername | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Blog_test                 | myusername | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

The database name is Blog_development (and the one below it). I was playing with rails and trying to learn from the online documentation. I wanted to start over and delete everything.

When trying to delete it however, it says it doesn't exist.  I'm brand new to PostgreSQL so I'm a bit lost, nothing in the documentation about this error other than it popping up when it doesn't exist. Of course it exists, it's right there.

Your database was created using double quotes so its name is now case-sensitive. Therefore, you now need to always use double quotes when referring to it:

drop database "Blog_test";

More details about quoted identifiers (a database name is an identifier just like a column or table name) can be found in the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS


PLPGSQL catch any exception in loop I have the following function for geocoding, it works fine without the EXCEPTION WHEN OTHERS THEN part. However, I want the loop continue in case if there is an exception. So I am trying to do this by using EXCEPTION WHEN OTHERS THEN
CREATE OR REPLACE FUNCTION mygetcounty2() RETURNS void LANGUAGE PLPGSQL AS $$
DECLARE idVariable uuid;
begin
FOR i IN 1..100 LOOP

SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1;

UPDATE address_table set county =
(select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) ))
where id = idVariable;
EXCEPTION WHEN OTHERS THEN
     UPDATE address_table set parse_address_error = true where id = idVariable;
END LOOP;
return;
end;
$$;

But I am getting the following message when trying to create the function
ERROR:  syntax error at or near "EXCEPTION"
LINE 15: EXCEPTION WHEN OTHERS THEN
         ^
SQL state: 42601
Character: 511

Any idea what I am doing wrong?
EDIT:
I added a new begin, end block inside the loop. I no longer got an error when creating the function.
Create or replace function mygetcounty2()
returns void
language plpgsql
as
$$
DECLARE idVariable uuid;
begin
FOR i IN 1..100 LOOP

begin

SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1;

UPDATE address_table set county =
(select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) ))
where id = idVariable;
EXCEPTION WHEN OTHERS THEN
      raise notice 'oops %', sqlstate;
     UPDATE address_table set parse_address_error = true where  id = idVariable;

    end;
END LOOP;
return;
end;
$$;

You cannot have an EXCEPTION clause in a LOOP - END LOOP block.
Wrap the first UPDATE in its own BEGIN - END block and add the clause there.


change MySQL commands to PostgreSQL in a bash script I have a bash script to backup MySQL databases. I have pasted the relevant part below (ignore the variables).

The 2 lines I think I need to change to make this work in PostgreSQL are;

DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

and

$MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE

Can anyone advise as to how I can get the same functionality with PostgreSQL?

# get all database listing
DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                echo "BACKING UP $db";
        $MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE
        fi
done

I think I've cracked it...with a LOT of Googling and chopping of code!

DBS="$(sudo -u postgres psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')"

and;

sudo -u postgres $PGDUMP -Fc --verbose $db | $GZIP > $FILE

# get all database listing
DBS="$(sudo -u postgres psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')"

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                 echo "BACKING UP $db";
                      sudo -u postgres $PGDUMP -Fc --verbose $db | $GZIP > $FILE
        fi
done


Locking records in Oracle during update I had a deadlock issue which was easy to fix by changing application logic, but I was under impression Oracle would behave differently, and the deadlock would never happen. I have two tables, for example

table1( table1_id (PK), num1, num2);
table2(table2_id(PK), table1_id(FK to table1,indexed), low_cardinality_column, num3, num4);

Table2 also has row level after update trigger which updates table1.num (table1.num1 = table1.num1 + :NEW.num3 where table1.table1_id = :NEW.table1_id).

First process executes UPDATE table2 set num3 =1 where low_cardinality_column =:bind_var (no index on low_cardinality_column , typically couple thousands records affected).
Second process updates table2 and table1 in one transaction with

UPDATE table2
   SET num4 = :bind_var4
WHERE table2_id = :bind_var_id
RETURNING table1_id INTO :out_var

UPDATE table1
  SET num2 = :new_num2_val
WHERE table1_id = :out_var

Trace showed quite a few deadlocks between those 2 processes if they happen to run at the same time, and I'm a bit confused by that. I understand deadlocks must have happened in case second process updated tables in reverse order (table1, then table2), but in this particular case I thought the engine won't actually begin update until it gets RX lock on each record which has to be updated (thus one process would wait for another to finish) . If it's not guaranteed, then explanation of the deadlock is obvious : Process1 locks records in undefined order, and  Process 2 happened to update the row in table2 not yet locked and tries to update the row in table1 already updated by Process 1. In case of SQLServer I would be 100% sure that's the case, but I'm still quite new to Oracle...

I wonder if someone could clarify the issue. Thank you.
I use 10g if that matters.

You are right: process 2 begins with update on Table2, but encounters deadlock with update on Table1 because the row is being updated by process 1 (and not yet committed).

Your assumption that the database "knows" it will encounter a deadlock in process 2 and will deny the transaction is wrong. The database is unaware of future statements in a transaction, therefore it can not avoid a deadlock.


Multiple table or many columns? There is a user table which have many fields actually around 120-150. All these have only user_id as the primary key. What is the best approach for the DB design. To have a single table with all these fields or spiting up to many tables. One of my main concern is if I split it into many tables will it affect the performance since it require many JOIN queries to display the data.

If you have 100-150 columns, it is likely some of those are repeating groups.  Those should be normalized into their own child tables (PK: user_id, sqn). Review your design. You should normalize to at least 3rd normal form.

Joins are usually very efficient, and in many cases only rows required for the result are joined.  With that many columns, it is unlikely you would ever have to return all the columns, so you would likely be joining a subset of the tables.

You should be concerned with the cost of searching for matches on columns that aren't indexed.  The higher the column number, the more expensive it is to find the column to compare it.  This cost is likely to far outweigh the cost of joining.

The main reasons I use a one-to-one tables are:

Separate large tombstone data from volatile information.
Separate data with higher security requirements, from more public data.
Store sub-type data (one to zero-or-one).


Boot page flag for cleanly shut down database I have been watching the SQL Server: Advanced Corruption Recovery Techniques by Paul Randal on Pluralsights. During module 4, he discusses the fact that if you have a missing log file it is important to know if the database was shutdown cleanly or not. This would determine if SQL Server will run crash recovery or not.
He goes on to mention, that the flag for this can be found in the database boot page (1:9 is my assumption based on the course materials and backed up through this link on microsoft). However, I do not see any obvious flags inside the page using -
DBCC TRACEON (3604);
DBCC PAGE ('stack',1, 9, 2) WITH TABLERESULTS;
DBCC TRACEOFF (3604);

In looking for this online, I am not able to find the information about the Boot page (1:9). I was able to find that sys.databases has a column for is_cleanly_shutdown which stands for:

1 = Database shut down cleanly; no recovery required on startup
0 = Database did not shut down cleanly; recovery is required on startup

However, I would like to follow along with the class, can someone advise what I am looking for in the boot page? Also, how to get the database to show it was cleanly shutdown or the proper steps to do this?
I am running SQL Server 2012.

It's part of the dbi_status field, which as Sean Gallardy said aren't documented and no-one's blogged about them (yet - so I don't want to get into details here). You can experiment with open transactions, crashing the server and moving the log file to force a database into the RECOVERY_PENDING state to watch which bits get set (again, no blog post about that yet).


Is there any difference at all between NUMERIC and DECIMAL? I know that the NUMERIC and DECIMAL data types in SQL Server work the same: the syntax for creating them is the same, the ranges of values you can store in them is the same, etc.

However, the MSDN documentation describes the relationship between the two as this:

  numeric is functionally equivalent to decimal.

Normally, when I see the the qualifier "functionally equivalent", it means that the two things aren't exactly the same , but that they are two different types that are indistinguishable from the outside.

Is this implication true? Are there differences between NUMERIC and DECIMAL that just happen to behave the same to an outside observer? Or are they actually equivalent, e.g. is NUMERIC just a legacy synonym for DECIMAL?

They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?

What I interpret from the wording in MSDN is actually:

  These types are identical, they just have different names.

Other than the type_id values, everything here is identical:

SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');

I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.

  for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?

Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:

CREATE TABLE [dbo].[NumDec]
(
    [num] [numeric](18, 0) NULL,
    [dec] [decimal](18, 0) NULL
);

Now run these queries and capture the plan:

DECLARE @num NUMERIC(18,0);
DECLARE @dec DECIMAL(18,0);

SELECT
  CONVERT(DECIMAL(18,0), [num]), -- conversion
  CONVERT(NUMERIC(18,0), [dec])  -- conversion
FROM dbo.NumDec
UNION ALL SELECT [num],[dec]
  FROM dbo.NumDec WHERE [num] = @dec  -- no conversion
UNION ALL SELECT [num],[dec]
  FROM dbo.NumDec WHERE [dec] = @num; -- no conversion

As shown in SQL Sentry Plan Explorer*, the plan is not really interesting:

But the Expressions tab sure is:

As I commented above, we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too.

Go ahead and try this test, too (data and indexes).

CREATE TABLE [dbo].[NumDec2]
(
    [num] [numeric](18, 2) NULL,
    [dec] [decimal](18, 2) NULL
);

INSERT dbo.NumDec2([num],[dec])
SELECT [object_id] + 0.12, [object_id] + 0.12
  FROM sys.all_columns;

CREATE INDEX [ix_num] ON dbo.NumDec2([num]);
CREATE INDEX [ix_dec] ON dbo.NumDec2([dec]);

Now run this query:

DECLARE @num NUMERIC(18,2) = -1291334356.88,
        @dec NUMERIC(18,2) = -1291334356.88;

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = @num
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = @dec;

Plan has no conversions (in fact the Expressions tab is empty):

Even these don't lead to any unexpected conversions. Of course you see it on the RHS in the predicate, but in no case did any conversion have to occur against the column data to facilitate the seek (much less force a scan).

SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2), @num)
UNION ALL
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2), @dec)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,2), @num)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,2), @dec)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,2), @num)
UNION ALL
SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,2), @dec)
UNION ALL
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,2), @num)
UNION ALL
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,2), @dec);

Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.

* Disclaimer: I work for SQL Sentry.


Locks caused by async stats update SQL Server has an option for statistics to autoupdate asynchronously (AUTO_UPDATE_STATISTICS_ASYNC).

But, even if this option is ON, from time to time I see locks like this on pic below: say, spid 549 locks spid 42 and spid 42 locks 8 other sessions. As I can see, system spid tries to change old statistics to a newly computed one, so it waits for SCH-M lock to acquire.

Suddenly, it turns out, async stats update is not so async:  any long-running SELECT (which, of course, holds SCH-S lock) can freeze system spid, which, in turn, can gather queue of many other sessions. Even worse, whole queue wait time depends on of locker’s SELECT duration. It means, if I have a long OLAP query, all the stalled sessions could be timed out.

So, my question is: am I missing something or it works just by design? Can I somehow avoid these sudden locks, caused by autoupdated statistics?

It has nothing to do with SCH-S. Select queries which acquire S and IS locks are compatible with SCH-S, which is called Schema Stability and all it does, it prevents table during query execution from being modified which means SCH-S and SCH-M are not compatible. SCH-S is compatible with all other locks.

Your problem however is that you have auto update stats enabled, which are triggered whenever there is certain amount of data changed within a table (20% pre SQL2016 or with 2371 traceflag counts % depending on number of rows) and it does when you execute the query.

So what might have happened is that your table was pretty busy and query optimizer decided it should update statistics, so you had to wait.

You should disable auto update statistics and update it manually