Parallel Sequential Scan on SELECT statement, but not on SELECT ... INTO The following query

EXPLAIN
SELECT c.global_id
FROM centers_res17 AS c
LEFT JOIN regions_res17 AS r ON ST_Contains(r.wkb_geometry, c.wkb_geometry)
WHERE r.global_id IS NULL;

says that a Parallel Sequential Scan on centers_res17 will be used. This is good and the query is fast (8 workers are used, and the CPU is busy)

Now, if I do a SELECT c.global_id INTO foo the query plan indicates a Sequential Scan, which is much slower.

I tried to:

CREATE the foo table before (also as a TEMP)
Wrap the above statement in a WITH clause and then select its results into foo.
CREATE foo AS (^^Above statement^^)

None of the above uses multiple workers to do a Parallel Sequential Scan (according to the query plan and actually running the query). I could just make the select statement 'as is' and then copy-paste the values from my results window ... not optimal.

Please explain to me, why the Parallel Sequential Scan is not used, or how to modify my query to have the results selected into foo.

I am using PostgreSQl 9.6 on Win10.

Quote from the manual

  The planner will not generate them for a given query if any of the following are true

  The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within a CTE, no parallel plans for that query will be generated. This is a limitation of the current implementation which could be lifted in a future release.

As you are inserting rows into a table, the underlying query will never use parallelism, at least not in current version (10) and earlier.


SQL query to get monthly sales per product, including products with no sales Let's say we have a Product table (product_id, product_name) and a Sales table (product_id, date, qty, amount).

What sql query would return the monthly sales per product, and include products with no sales?

(I'm using sql server if that makes any difference).

Clarification: I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales

See if this is what you're looking for (adjust as needed):

set nocount on

--Declare a months table (or use a Date Dimension table)
Declare @Months Table (MonthNumber tinyint, MonthName varchar(20))
insert into @Months (MonthNumber,MonthName) values
(1,'January'),
(2,'February'),
(3,'March'),
(4,'April'),
(5,'May'),
(6,'June'),
(7,'July'),
(8,'August'),
(9,'September'),
(10,'October'),
(11,'November'),
(12,'December')

--Declare a products table
Declare @Products table (Id int, ProductName Varchar(20))
insert into @Products (Id, ProductName)
values(1,'Widgets'),(2,'Thingamabob')

Declare @Sales table (product_id int, [date] Date, qty int, amount decimal(11,2))
insert into @Sales (product_id, [date], qty, amount)
values(1,'2017-01-01',1,100.00),(2,'2017-02-01',1,200.00),(2,'2017-06-01',2,300)

--Select Months and CROSS JOIN to Products, then
--LEFT JOIN to Sales.  Use ISNULL to return zeros
--for months/products with no sales
SELECT m.MONTHNUMBER
    ,m.MonthName
    ,P.ProductName
    ,ISNULL(SUM(QTY), 0) AS QTY
    ,ISNULL(SUM(AMOUNT), 0) AS AMOUNT
FROM @Months m
CROSS JOIN @Products p
LEFT JOIN @Sales s ON DatePart(MONTH, [date]) = M.MonthNumber
    AND S.product_id = P.ID
GROUP BY m.MonthNumber
    ,m.MonthName
    ,P.ProductName
ORDER BY M.MonthNumber
    ,m.MonthName
    ,P.ProductName

| MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT |
|-------------|-----------|-------------|-----|--------|
| 1           | January   | Thingamabob | 0   | 0.00   |
| 1           | January   | Widgets     | 1   | 100.00 |
| 2           | February  | Thingamabob | 1   | 200.00 |
| 2           | February  | Widgets     | 0   | 0.00   |
| 3           | March     | Thingamabob | 0   | 0.00   |
| 3           | March     | Widgets     | 0   | 0.00   |
| 4           | April     | Thingamabob | 0   | 0.00   |
| 4           | April     | Widgets     | 0   | 0.00   |
| 5           | May       | Thingamabob | 0   | 0.00   |
| 5           | May       | Widgets     | 0   | 0.00   |
| 6           | June      | Thingamabob | 2   | 300.00 |
| 6           | June      | Widgets     | 0   | 0.00   |
| 7           | July      | Thingamabob | 0   | 0.00   |
| 7           | July      | Widgets     | 0   | 0.00   |
| 8           | August    | Thingamabob | 0   | 0.00   |
| 8           | August    | Widgets     | 0   | 0.00   |
| 9           | September | Thingamabob | 0   | 0.00   |
| 9           | September | Widgets     | 0   | 0.00   |
| 10          | October   | Thingamabob | 0   | 0.00   |
| 10          | October   | Widgets     | 0   | 0.00   |
| 11          | November  | Thingamabob | 0   | 0.00   |
| 11          | November  | Widgets     | 0   | 0.00   |
| 12          | December  | Thingamabob | 0   | 0.00   |
| 12          | December  | Widgets     | 0   | 0.00   |

Note: The OP did not indicate they wanted a solution that takes the year into consideration, so my solution does not deal with the notion of a year when aggregating the data.  If the month is January (regardless of year), the counts go in the January bucket.

UPDATE: To clarify a bit further, I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales


Question on Oracle OEM version and plugin version upgrade I have a OEM 12c (12.1.0.4) running in my setup in which I have registered over 70-80 databases. I see the plugin updates with versions of each target (Oracle database, MS-SQL, MySQL) and I have the downloaded the plugin updates as well.

My question is that can I update my plugin which is higher version than my OEM itself?
If yes, should I update my plugin one version at a time? Say, my deployed oracle database plugin version is 12.1.0.4 and available downloaded versions for the same I have is 12.1.0.6 and 12.1.0.7. So I should update the 6 release first and then the 7? Also how to go ahead with the update?
Are there upgrades for the OEM itself? from 12.1.0.4 to a higher version? If yes, how to go ahead with it? Are there any MOS if you could share?

Thanks!!

Plugins are deployed on the OMS and the agents as well. First you should upgrade the plugin on the OMS, then on the agents.
You don't need to upgrade between versions sequentially, you can upgrade to the highest available.
Yes, there are. Currently 12.1.0.5 is the latest version, and there are quarterly PSUs as well. Check the below MOS note:

Oracle Recommended Patches (PSU) for Enterprise Manager Base Platform (All Releases) (Doc ID 822485.1)

There is no PSU released for 12.1.0.5 yet.


What are user connections - when are the created and destroyed? We've been getting an intermittent error on out site:

  Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10)

Which prevents the site from being accesses & effectively it is down.

(obviously increasing the 'max_user_connections' would fix it but that is not an option through our hosting scheme)

I may have 'fixed' it, but I am not 100% sure I understand the issue & I would like to be clear. I thought the problem must be cause by too many cron jobs using up the connections (we have a couple running every minute), and I cut down the number of cron jobs & that seemed to lessen the amount of errors. I have created a separate user account for the cron jobs, so if that was the problem it should be solved now.

However, our logging system does not record the error when it occurs, so I am still concerned.

So I am asking: what constitutes a 'user_connection' in MySQL?

is there a separate connection for each DB call?
if not, when would DB calls require a separate connection?
why would this error only occur on the live production site? perhaps there is a separate connection for each unique ip or simialr?

I feel I am over-thinking it . . .

Please notice the makeup of mysql.user:

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Notice the column max_user_connections. It puts a moratorium on the number of connections a user may make per hour. Check and make sure the user '557574_prod' isn't connecting to mysql a ton of times within less than an hour.

This is used in conjunction with the option max_user_connections that you can set in /etc/my.cnf

Please change either max_user_connections in /etc/my.cnf or, if the individual user has this column set, that column mysql.user.max_user_connections. If you have no config control over my.cnf and you cannot do SQL against the mysql schema, then you beg and implore your hosting company to raise those limits for you.

I believe you can set the max_user_connections as a session variable, but you may need certain privileges, probably SUPER.

As for establish connections, you need to keep those connections open. Perhaps sending a heartbeat of some kind by doing a SELECT. But guess what ??? The db columns max_questions monitor SELECT queries per hour and max_updates monitors INSERTs, UPDATEs, DELETEs per hour. YOu may need to check these columns in mysql.user. You can quickly check those number with:

SHOW GRANTS;

I am not sure if the the user's per-hour connection limits are per thread or per user. You could answer that quickly by connecting to the DB aggressively using 2 or 3 DB connecitons at the same time and the error message should manifest itself. It is up you how to count the number of connections, perhaps "SHOW STATUS LIKE 'Connections';" It should reach either the max limits of the connection within the one connections or it could be the sum of all connections' connection status value.

Please also check the wait_timeout and interactive_timeout values. The default is 28800 in a standalone server. They may be set already. You cannot set them within a current connection. You must be able to set them as follows: (for new conenctions coming)

SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;

If you cannot run even these commands, my condolences !!! :(


Decompose Relation R(ABCDE) in 2nf and 3nf Given relation R(ABCDE) with functional dependencies

A->C
B->D
AB->E
Given: AB is the primary key.

I know that this relation is in 1NF because of partial dependencies A->C and B->D

Now how I decompose it to get 2NF or better form.
My concepts are weak , so please help me.

The following two FD's violate 2NF:

A->C
B->D

We can create new relationships based upon those FD's: (AC;A->C), (BD;B->D), (ABE;AB->E).  This set of relationships now satisfies 2NF (and also 3NF and BCNF).


Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+? We have 3 MS SQL Environments: Dev, Test, and Prod. There are many Dev servers, and 1 each in Test and Prod.
What is the best way to determine the "Environment" from a query? For instance, we want to watermark Dev and Test SSRS reports if it's dev or test.
I don't want to hard code a check for Prod's server name, in case the server name changes.
I also don't want to check a "settings" table in the database, because then I'm depending on the environment's restore scripts running successfully.
Are there any other solutions that will work from a query?

You could create a utility database on each server with a table(s) to hold metadata about that server (environment, purpose, build date etc). As each utility database is tied to a server/instance, it won't get refreshed along with any databases that get refreshed so you don't have to worry about it being inaccurate if a refresh fails.
Include the deployment of this DB in your standard build process and then you can simply reference this DB in your query to determine environment. If you keep the same name throughout your SQL estate, then your queries don't need to change between servers either.


MySQL Index creation failing on table is full UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space.

Original Q:

I'm trying to add an index to an InnoDB table, and getting a table is full error.
I have enough disk space and the MySQL configuration has a file-per-table=1. The table data is 85GB and I assume the index will be around 20GB - 30GB and I have much more disk space than that. I'm also using ext3 so I don't feel there's any problem with the file size limit from the OS point of view.

The logged error looks like this:

140616 13:04:33  InnoDB: Error: Write to file (merge) failed at offset 3 1940914176.
InnoDB: 1048576 bytes should have been written, only 970752 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140616 13:04:33 [ERROR] /usr/libexec/mysqld: The table 'my_table' is full

What is causing this and how can I resolve?

The create table:

`CREATE TABLE `my_table` (
 `uid_from` bigint(11) NOT NULL,
 `uid_to` bigint(11) NOT NULL,
 `counter` int(11) NOT NULL,
 `updated` date NOT NULL,
 PRIMARY KEY (`uid_to`,`uid_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8`

As is the Data is 87.4GB and I estimate there are around 1.5B rows.

SHOW GLOBAL VARIABLES LIKE 'tmpdir';
Variable_name   Value
tmpdir  /tmp

[root@web ~]# df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       40G   24G   14G  64% /

Please don't fooled by the error message The table 'my_table' is full. This rare scenario has absolutely nothing to do with diskspace. This table full condition has to do with the internal plumbing of InnoDB.

First, take a look at this diagram of the InnoDB Architecture

Please note that the system tablespace (the file ibdata) only has 128 Rollback Segments and 1023 Rollback Slots Per Rollback Segment. This places limits on the size of a transaction's rollback capacity. In other words, if a single rollback segment needs more than 1023 slots to support a transaction, the transaction will hit that table is full condition.

Think of the restaurant Red Lobster in New Jersey. It may have a capacity of 200 people. If the restaurant is full, a line of people may go outside to wait. If the people on the line get impatient, they may leave because the restaurant is full. Obviously, the solution would not be to make New Jersey bigger (or getting more diskspace). The solution would be to make the Red Lobster restaurant bigger. That way you can increase the seating capacity to, let's say, 240. Even with that, a line may form outside if more than 240 people decide to come to Red Lobster.

Just to give you an example, I had a client with 2TB of system tablespace and innodb_file_per_table was disabled. (346G for ibdata1, the reset for ibdata2). I ran this query

SELECT SUM(data_length+index+length) InnoDBDataIndexSpace
FROM information_schema.tables WHERE engine='InnoDB';

Next, I substracted InnoDBDataIndexSpace from the sum of the filesizes for ibdata1 and ibdata2. I got two things that shocked me

There was 106GB remaining inside the system tablespace.
I got this same Table is Full condition

This means that the 106GB was using for InnoDB's internal plumbing. The client was using ext3 at the time.

The solution for me was to add ibdata3. I discussed this in my old post How to solve "The table ... is full" with "innodb_file_per_table"?

I have discussed this in other posts as well

Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Nov 25, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

Keep in mind that this condition can happen even if innodb_file_per_table was enabled. How? Rollbacks and Undo Logs are the source of uncontrolled spikes is growth for ibdata1.

YOUR ACTUAL QUESTION

Since your are adding an index to a table and getting Table is Full, the table must be huge and cannot fit inside a single rollback segment. You must do the following:

STEP 01

Get the data into a dump file

mysqldump --no-create-info mydb mytable > table_data.sql

STEP 02

Login to MySQL and run this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD INDEX ... ;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

STEP 03

Load the table with the additional index with the data

mysql -Dmydb < table_data.sql

That's all.

See, the problem is that the ALTER TABLE will try to inject all the rows in your huge table as a single transaction. Using the mysqldump will insert the data into table (now with a new index) thousands of rows at a time, not all the rows in a single transaction.

Don't worry about what if this doesn't work? The original table will be named mytable_old in case of anything. it can serve as a backup. You can drop the backup when you know the new table works for you.

Give it a Try !!!

UPDATE 2014-06-16 11:13 EDT

If you are worried about the dump of the data bigger huge, just gzip it.

You can do the same steps, but as follows

STEP 01

Get the data into a dump file

mysqldump --no-create-info mydb mytable | gzip > table_data.sql.gz

STEP 02

Login to MySQL and run this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD INDEX ... ;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

STEP 03

Load the table with the additional index with the data

gzip -d < table_data.sql.gz | mysql -Dmydb

or

gunzip < table_data.sql.gz | mysql -Dmydb

UPDATE 2014-06-16 12:55 EDT

I just thought of another aspect with regard to this issue.

Since you are doing DDL and not DML, it is possible that this is not InnoDB internal plumbing. Since DDL cannot rollback for InnoDB, the issue has to be external plumbing. Where is this external plumbing getting clogged? I suspect the temp folder for the OS. Why?

140616 13:04:33  InnoDB: Error: Write to file (merge) failed at offset 3 1940914176.
InnoDB: 1048576 bytes should have been written, only 970752 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140616 13:04:33 [ERROR] /usr/libexec/mysqld: The table 'my_table' is full

See the disk quota exceeded ? Where is this disk quota being imposed ?

Run this query

SHOW GLOBAL VARIABLES LIKE 'tmpdir';

You said it's /var/lib/mysqltmp

Now, run this in the OS

df -h /var/lib/mysqltmp

Something tells me that the space for /var/lib/mysqltmp was running out After all, you only have 14G free. In the eyes of the DDL (to create the index), a rollback happened, not in the ibdata1 file, but in the tmpdir location. If /var/lib/mysqltmp is not mounted anywhere, then the temp data is being written in the root partition. If /var/lib/mysqltmp is mounted somewhere, then that mount is being filled with row data. In either case, there is not enough room to complete the DDL.

You have two options here

OPTION #1

You could also create a large disk (perhaps with 100+GB) and mount /var/lib/mysqltmp on that large disk.

OPTION #2

My 3-step suggestions should still work, even with the limited diskspace you have

COMMENTARY

It's a shame the error message you posted says

InnoDB: Operating system error number 0.
InnoDB: Error number 0 means 'Success'.

This means the OS is just fine. It also there doesn't exist any associated error number for this situation.

There is another thing you should know. The MySQL Documentation says that Fast Index Creation for InnoDB still goes to disk:

  During index creation, files are written to the temporary directory
  ($TMPDIR on Unix, %TEMP% on Windows, or the value of --tmpdir
  configuration variable). Each temporary file is large enough to hold
  one column that makes up the new index, and each one is removed as
  soon as it is merged into the final index.

  Due to a limitation of MySQL, the table is copied, rather than using
  “Fast Index Creation” when you create an index on a TEMPORARY TABLE.
  This has been reported as MySQL Bug #39833.

UPDATE 2014-06-16 13:58 EDT

[mysqld]
datadir                         = /mnt/cbsvolume1/var/lib/mysql
tmpdir                          = /mnt/cbsvolume1/var/lib/mysql

Please make sure /mnt/cbsvolume1/var/lib/mysql has 100G or more free


PL/SQL: Statement ignored in procedure I try to create one test procedure but it gets
Error(4,1): PL/SQL: SQL Statement ignored ** and ** Error(12,20): PL/SQL: ORA-00942: table or view does not exist when I try to compile. Then I test select statement in next session, it run successfully. Could you please help me.

create or replace PROCEDURE TEST_1
AS
BEGIN
SELECT
A.EMPLOYEE_ID AS "Employee ID",
A.FIRST_NAME||' '||A.LAST_NAME AS "Name",
B.DEPARTMENT_NAME AS "Department Name",
A.EMAIL AS  "Email Address",
A.PHONE_NUMBER AS "Contact Number",
C.STREET_ADDRESS||', '||C.CITY||', '||NVL(C.STATE_PROVINCE,C.CITY) AS "Address"
FROM HR.EMPLOYEES A
LEFT OUTER JOIN HR.DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
LEFT OUTER JOIN HR.LOCATIONS C
ON B.LOCATION_ID = C.LOCATION_ID;
END;

Looks like a right problem. Do you realy need "HR." ?

Try

create PROCEDURE TEST_1 AS BEGIN
CURRENT_USER
SELECT A.EMPLOYEE_ID AS "Employee ID",
         A.FIRST_NAME || ' ' || A.LAST_NAME AS "Name",
         B.DEPARTMENT_NAME AS "Department Name",
         A.EMAIL AS "Email Address",
         A.PHONE_NUMBER AS "Contact Number",
         C.STREET_ADDRESS || ', ' || C.CITY || ', ' ||
         NVL(C.STATE_PROVINCE, C.CITY) AS "Address"
    FROM EMPLOYEES A
    LEFT OUTER JOIN DEPARTMENTS B
      ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
    LEFT OUTER JOIN LOCATIONS C
      ON B.LOCATION_ID = C.LOCATION_ID;
END;

And have a look at GRANT (for example)

GRANT EXECUTE ON PROCEDURE <user>.test_1 TO <role>


History table implementation: "Tuple-versioning" vs Effective Date I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose.

The question is what are the advantages of having tuple-versioning (start and end dates) vs just effective date?

1) tuple versioning

CREATE TABLE HistoryTable (
   Column1   Type1,
      :        :
   Columnn   Typen,

   StartDate DATETIME,
   EndDate   DATETIME
)

2) effective date

CREATE TABLE HistoryTable (
   Column1   Type1,
      :        :
   Columnn   Typen,

   EffectiveDate DATETIME
)

For (1) so far I see disadvantages - I have to update [end date] on the previous history record while inserting new/current one.

For (2) I can only insert new record without searching and modification the previous one.

From query point of view both approaches look quite the same.

I know that SQL Server 2016 has temporal tables but it's not available to us so far.

Tuple versioning

Will require two updates for each new piece of data - one to set the end date of the old row & one to insert the new row.
The index(es) most likely will contain the interval dates making them wider and marginally less efficient.
Historical queries (AS OF < date >) are simpler.
DELETE can be a logical delete without further code.
The end of the live row must be marked with a "magic" value, typically 9999-12-31, or NULL. The latter will complicate code. The former often morphs into several magic values which become difficult to handle.

Effective date

Will require a MAX(), TOP(1) or similar functionality, on every single read.
Adding a single DATE column to indexes will cause a tiny less bit of bloat.
Logical DELETE is not possible without adding a place-holder "has expired" row to the table.

For both

All writes are INSERTS, never UPDATES.
Primary keys expand to include the date, with implications for referencing tables.
Best to use UTC for the effective dates to avoid time zone and DST issues.

I've tried both. Tuple versioning is easier to support overall.

It is relatively simple to reproduce the functionality of SQL Server's temporal tables by using triggers. Having a separate history table eliminates a lot of the complexities arising from having history and live values co-mingled in one table.


Oracle database Architecture I need to know to correct order between these following steps, it's hard to find information on the internet on what happens first, i'd be really grateful if someone could give me the answer

A. Oracle searches for a init.ora to determine the configuration
       parameters

B. The proces SMON sometimes executes an instance-recovery

C. memory for the SGA gets allocated and the  background processes that are necessairy get started

D. Oracle searches the database files and redo log files

E. oracle starts an instance

F. oracle searches for the control files with the parameter CONTROL_FILES

A. Oracle searches for a init.ora to determine the configuration
parameters

without a parameterfile, you will get an LRM-00109, ORA-01078 error, and nothing gets started

E. oracle starts an instance

we could argue what exactly starting an instance means, I have just simply put it here because this is the "exact" message that appears first when starting an instance

C. memory for the SGA gets allocated and the background processes that are necessairy get started

background processes like LGWR, DBWR, CKPT, SMON, PMON start in NOMOUNT, way before doing anything with control files...

F. oracle searches for the control files with the parameter CONTROL_FILES

thats the next one when going to MOUNT

D. Oracle searches the database files and redo log files

now tell me, how could the database perform an instance recovery without knowing where the datafiles and redo logs are

B. The proces SMON sometimes executes an instance-recovery

finally an instance recovery is performed, and database gets opened


Capturing errors in a transaction and rollback - declare not valid at this position Trying to craft a transaction from the code to send to MySQL.

START TRANSACTION

INSERT INTO EMPLOYEE (`firstname`,`lastname`)
 VALUES('john','Doe');

SET EMP_ID = last_insert_id();
 //do some inserts here that fail

COMMIT;

Now, when the other inserts into child tables fail I see that just the first insert being committed. TO avoid that I need to capture error and set rollback on error being triggered. When I include the declare statements for error it just fails saying

"Declare is not valid at this position. expecting EOF ;" Any pointers would be greatly appreciated

START TRANSACTION

DECLARE error BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

....other code

DECLAREs must be placed before all executable code in a BEGIN...END block.  `START TRANSACTION is 'executable'.

So...

DELIMITER //
CREATE ...
BEGIN
    DECLARE _rollback DEFAULT 0;
    START TRANSACTION;
    ... -- other executables
    IF _rollback = 0
    THEN  COMMIT;
    ELSE  ROLLBACK;
    END IF;
END;
//
DELIMITER ;

You can also have nested BEGIN..END blocks.  But the declarations in your example should probably go at the beginning of the stored routine.  This is because BEGIN..END blocks provide "scoping" for variables such as _rollback.


Bypassed, Empty Relay Log We have a simple 1:1 Master-Slave replication configuration. The Master crashed, but we recovered and replication seems fine (confirmed by pt-heartbeat and SHOW SLAVE STATUS) but we've picked up one small, strange issue: there is an old, empty, and bypassed relay log hanging out on the Slave.

We are on relay.000725, and this one is relay.000724, so we're past it. What's more, it doesn't contain any actual queries. Just the meta data.

Can I delete this? What caused this?

> /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET
> @@session.max_insert_delayed_threads=0*/; /*!50003 SET
> @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER
> /*!*/;
> # at 4
> #140704  3:36:04 server id 16  end_log_pos 107    Start: binlog v 4, server v 5.5.38 created 140704  3:36:04 BINLOG '
> ZFm2Uw8QAAAAZwAAAGsAAAAAAAQANS41LjM4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
> '/*!*/;
> # at 107
> #140721  4:22:51 server id 14  end_log_pos 1073741944     Rotate to bin-log.006142  pos: 4
> # at 148
> #140704  3:36:04 server id 16  end_log_pos 187    Rotate to relay.000725  pos: 4 DELIMITER ;
> # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET
> @@SESSION.PSEUDO_SLAVE_MODE=0*/;

You be able to delete it without issues. Why ?

Look for the file relay-log.index and display it

# cat relay-log.index

You will see a list of relay logs MySQL Replication needs and rotates. If relay.000724 is not in relay.index, you can delete relay.000724 without breaking anything.

Why is is still around ? It could be anything. Between setting up replication after an outrage, skipping SQL Errors, and just plain Starting and Stopping, along with a low expire_logs_days could leave one or more relay logs lingering.

In light of these things, if a relay log does not appear in relay.index, you can delete it.

Why is the relay log so empty ?

It has to do with the mechanism Replication uses to rotate to the next relay log. You will not see these annoying little files very often. The only time I usually see them is with one of the following:

when the SQL thread is broken
when the SQL thread is extremely busy with a long running query

In either case, the IO thread keeps collecting binlog events. Instead of triggering a rotation when the relay log reaches 1G or whatever max_binlog_size or max_relay_log_size is, two little relay logs with nothing in them mark the place where a rotation would have occurred.

UPDATE 2014-07-21 15:28 EDT

Since the relay-log.index both relay logs, there are one of two things you could do:

Leave it alone and let MySQL Replication rotate it away on its own
Reset all the relay logs back to relay-log.000001 with the following

Run STOP SLAVE;
Run SHOW SLAVE STATUS\G
Find Relay_Master_Log_File in the Status (say it is mysql-bin.001234)
Find Exec_Master_Log_File in the Status (say it is 123789456)
Run CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001234',MASTER_LOG_POS=123789456;
Run START SLAVE;


Create a mysql slave database from master database I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files are copied.

I am not sure, how to start replication on the slave. Any help would be appreciated.

I am still copying data into slave.

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mydbm1-bin.008524 |  1330529 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

By the way, many new rows have been inserted into the master after the snapshot is copied to slave.

Going by your question, I will like to review what I believe you did thus far:

You stopped mysql on the Master
You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
I surmise the binlogs on the Master were copied as well

Look at the Slave's last binlog. From the question, it should be

mydbm1-bin.008524
Filesize 1330529

Believe it or not, you have to do a few things:

1) On the Master, create a replication user like this:

GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';

2) Make /var/lib/mysql on the Slave owned by mysql user

chown -R mysql:mysql /var/lib/mysql

3) Make sure Master's server_id is explicitly set in my.cnf

[mysqld]
server_id = 1

4) Make sure Slave's server_id is explicitly set in my.cnf

[mysqld]
server_id = 2

5) Startup mysql on the Slave

service mysql start

6) Setup replication by running this on the Slave

CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

You will see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

If Slave_IO_Running and Slave_SQL_Running are both Yes, CONGRATULATIONS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql


Postgres blocking UPDATE on new column? For the life of me, I can't figure out why Postgres won't let me issue this update statement below. Assume any table called dim_dates where there's a column dateType:

ALTER TABLE dim_dates DROP COLUMN "dateType";
ALTER TABLE dim_dates ADD COLUMN "dateType" character(1);
update dim_dates set dateType='D' where year>2000;

It barfs on line 3, saying:

ERROR: column "datetype" of relation "dim_dates" does not exist
SQL state: 42703
Character: 126

Yet I just (re)added the column! I initially had already added the column through the designer, and was attempting to fill in the data for the new table and was only running what is now line 3, but it just doesn't want to let me update the value. I don't want 'D' as a default - but the existing data should have 'D' and I'm going to add further data.

This is a new database, 9.3, no triggers or other evil things to mess up developers (I'm the guy who installed it, and I'm old hat at MS-SQL and MySql, but new to Postgres and trying to get experience with it).

It's a case-sensitivity issue.

"dateType" including the quotes is a quoted identifier, which implies that it retains its case (as opposed to an unquoted identifier which is implicitly converted to lower case). Quoted identifiers allows other columns of the same table named for instance "DateType" or "datetype" or other variants that differ only by case (not that it would be a good idea).

The drawback is that every subsequent SQL instruction refering to that column must use "dateType" too, with the double quotes and the exact same case. In your UPDATE, the double quotes are lacking, which is why it errors out saying that "datetype" does not exist (note how it differs from "dateType").

In practice, an identifier should be either always double-quoted, or never. A mix and match is not allowed. When an identifier is not quoted, it can be written in camel case if you fancy that style: the SQL parser will ignore the case.

See Identifiers and keywords in PostgreSQL documentation for more.


Restore database from individual datafile backups I had a script that backs up all the individual datafiles like:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATAFILE 'datafile<1>.dbf'

So i have backupsets of all the datafiles. I'm confused as to whether it would be sufficient to use RESTORE DATABASE, as can be done after a full database backup using BACKUP DATABASE? Isn't the end result of both BACKUP operations the same?

Thank you in advance.

Jay, this isn't officially documented, so I'm speaking only from my own experience.

In RMAN, the command BACKUP DATABASE ... is synonymous with BACKUP DATAFILE 1, 2, ..., n-th ....

Also the command RESTORE DATABASE ...  is synonymous with  RESTORE DATAFILE 1, 2, ..., n-th ....

I'm not sure about RECOVER DATABASE; it might be also a synonym of RECOVER DATAFILE 1,2, ... n-th , although  I've never tested the latter. In your scenario, the former would work as expected.

In particular, neither RESTORE DATABASE or RECOVER DATABASE require all the datafiles to come from "a single backup of database" (from a single run of BACKUP DATABASE). Actually, RMAN doesn't even have a concept of "a single backup of database".


Does SQL Server Compact Edition 4 finally support Views? Just read about the release of SQL Server CE 4 and then searched all through their product website, I still cannot find a feature overview.

Previous versions of SQL Server CE didn't support views, I was hoping that they removed the limitations.

My question is:

Is there a page which lists the features and limitations of SQL Server CE 4?

(Edit: I found this feature comparison for 3.5, but not for 4.0)

I installed WebMatrix w/ CE 4.0 last night.  I can't confirm for sure that CE 4.0 can't do views, but I can tell you that there's no support for creating them in WebMatrix.


Unable to create UNIQUE Filtered index with IGNORE_DUP_KEY = ON I have a table , in SQL Server 2012 enterprise edition, for which I want to have a unique index with IGNORE_DUP_KEY = ON, however I only want this index (and ignoring the duplicates) to apply only to a subset of the table - only rows with FLAG = 1.

CREATE TABLE [dbo].[FOO]
([ID] NOT NULL,
[Value] [int] NULL,
[FLAG] [bit] NULL)

When trying to create the index using below query

CREATE UNIQUE NONCLUSTERED INDEX [IDX_UQ_FL_FOO_VALUE] ON [dbo].[FOO]
([Value] ASC)
WHERE [flag] = 1
WITH (IGNORE_DUP_KEY = ON)

I get an error:

  Msg 10618, Level 16, State 1, Line 1
  Cannot create filtered index 'IDX_UQ_FL_FOO_VALUE' on table 'dbo.FOO' because the statement sets the IGNORE_DUP_KEY option to ON. Rewrite the statement so that it does not use the IGNORE_DUP_KEY option.

Is it not possible to combine a filtered unique index with the IGNORE_DUP_KEY = ON?

Setting the index to non-unique is not an option. I need it to be unique to filter duplicate inserts, but only when inserting values with FLAG = 1. I need the insert of a duplicated row to be ignored, not to fail.

As already mentioned by the other answer, IGNORE_DUP_KEY is not supported for filtered indexes, which is explicitly stated in the manual:

  Filtered indexes do not allow the IGNORE_DUP_KEY option.

There is a workaround, however, if you can afford a small change to the table's structure. With the help of a uniquifier column you can create a unique index that would effectively allow you to have unique Values only for rows with FLAG = 1.

The following assumes that the ID column is already declared as unique (either being the primary key or having a UNIQUE constraint on it). First, add a computed column that would evaluate to NULL for the FLAG = 1 rows and to ID for all the others:

ALTER TABLE dbo.FOO
ADD Uniquifier AS CASE FLAG WHEN 1 THEN NULL ELSE ID END;

Now you can create a unique index on (Value, Uniquifier) with the IGNORE_DUP_KEY option:

CREATE UNIQUE NONCLUSTERED INDEX UQ_1
ON dbo.FOO (Value, Uniquifier)
WITH (IGNORE_DUP_KEY = ON);

Since Uniquifier will be NULL for rows with FLAG = 1, the uniqueness will be determined based on Value alone. All the other rows will be unique regardless of Value because Uniquifier is unique (since its value is taken from a unique column, ID).

A live demonstration of the method is available at SQL Fiddle.


PostGIS window function not picking up index? I am executing this query ST_ClusterDBSCAN with PostGIS extension and PostgreSQL. I have indexed point_data but an EXPLAIN ANALYZE shows me that the index is not being picked up. How can I rearrange my query so that it is indeed picked up?

Index:

CREATE INDEX index_point_data ON point_data USING GiST(geom)

Query:

SELECT id, ST_ClusterDBSCAN(geom, eps := 350, minPoints := 3)
OVER () AS cluster_id, geom
FROM point_data

Plan:

WindowAgg  (cost=0.00..148.64 rows=3984 width=44) (actual time=195.029..197.227 rows=3984 loops=1)
  ->  Seq Scan on point_data  (cost=0.00..98.84 rows=3984 width=40) (actual time=0.022..0.578 rows=3984 loops=1)
Planning time: 0.136 ms
Execution time: 197.593 ms

A query reading the whole table will not profit from the index. It's cheaper to read the whole table sequentially.

The spatial GiST index you display can be useful for other types of queries filtering a small percentage from the table, like:

SELECT * FROM point_data WHERE ST_DWithin($input_geometry, geom, 5000);

Understanding the decisions of the Postgres query planner requires intimate knowledge of Postgres. Start by reading this chapter of the manual and follow links from there:

How the Planner Uses Statistics

And more particularly, about spatial indexes in the PostGIS manual:

Taking Advantage of Indexes


Perform mysql limited select that wraps when it reaches the end of table I want to grab the next 2 rows from a table after a given id. This code works nicely however if the rows being selected right at the end of the database I need the select to wrap around so for instance in a table of rows 0 to N I will at some point want to pull out rows N and 0 and then a bit later pull out 0 and 1. Is it possible to make a MYSQL select wrap around like this?

SELECT `promo_id`
FROM `promotions`
WHERE (`expiry_date` >= '2017-03-21' AND `promo_id` > (SELECT `promo_id`  FROM `promoted_questions` WHERE `promo_id` = 8))
ORDER BY `promo_id` ASC
LIMIT 2

UPDATE for clarity:

At the moment my code just returns up to the end of the table rather than wrapping back around to get the first rows again. Here's an example of what I want:

ID
1
2
3
4
5

If I run the code above with the starting ID as 2 then get IDs 3 and 4 back - perfect. If I run it with the starting ID as 4 then all I get back is 5, not so perfect as I was 5 and 1. Likewise, if I use starting ID 5 then I want 1 and 2 back from the database.

I've set a rextester example whith this sample data:

drop table if exists promotions;
create table if not exists promotions (promo_id int, expiry_date timestamp);
insert into promotions values
(1, '20170101'),(2, '20170101'),(3, '20170101'),(4, '20170101'),(5, '20170101');

IMHO you should add two additional records and then limit final result to two rows. Use a UNION with your query and an addition query that simply returns the two first rows of promotions table.

Note: Add parentheses to both queries or you'll get an error.

(select  promo_id
from     promotions
where    promo_id > 4
order by promo_id
limit    2)
union
(select  promo_id
from     promotions
order by promo_id
limit    2)
limit 2
;

The final result:

promo_id  |expiry_date
----------|--------------------
   5      | 01.01.2017 00:00:00
   1      | 01.01.2017 00:00:00

Using you query

(SELECT `promo_id`
 FROM `promotions`
 WHERE (`expiry_date` >= '2017-03-21' AND `promo_id` > (SELECT `promo_id`  FROM `promoted_questions` WHERE `promo_id` = 8))
 ORDER BY `promo_id` ASC
 LIMIT 2
)
UNION ALL
(SELECT `promo_id`
 FROM `promoted_questions`
 ORDER BY `promo_id` ASC
 LIMIT 2
)
LIMIT 2;


Stop restoring backup in pgAdmin I am currently restoring a database and I tried to restore the database three times. The third time I succeeded but unfortunately it seems that the other two attempts at restoring the database are still running but never finish.

I have these two blue windows on the pgAdmin screen which I can not remove or stop executing. I tried a lot of things, I even uninstalled PostgreSQL two times but the restoring windows appeared again.

I am using Windows 8 and PostgreSQL 9.6.

I had the same problem and this helped me to solve it:

SQL command to clean job queue in pgAdmin 4

I just had to uninstall pgAdmin, delete all the files on C:Program Files directory and in App Data directory, as well.


Run calculation not working in sub query, "does not exist" PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

I have a query that provides the count of 2 things over 2 time periods:

time period of this week, start of the week (Sunday/Monday midnight)
-> today
time period of last week, start of last week (Sunday/Monday midnight)
-> today but last week

What I would like is to also perform a calculation on the two "car" count ints that are returned and provide this with the query result.
I can of course do this in the consuming app, but I'd love to be able to do it in my SQL.
Working SQL
This is the base SQL and is what I currently have and working:
WITH last_week AS (
  SELECT COUNT(car_name) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_name) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

Setup DB table
CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

Thing that causes error
I would like add the following code - to calculate difference_between_weeks:
ROUND(car_count_current_week/(car_count_last_week/100) - 100)
    AS difference_between_weeks

I have tried calculating in the columns, I have tried adding another sub query. But I don't seem to be able to calculate the difference_between_weeks as it tells me "does not exist" or ERROR:  division by zero
Full SQL that errors
An example of the SQL with the added code that errors:
WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week,
  ROUND(car_count_current_week/(car_count_last_week/100)) - 100 AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

Returned error
I get the following error:
ERROR:  division by zero
SQL state: 22012

I feel like I am close but I also feel this may not be possible?
Any pointers to what I am missing would be hugely appreciated.

Your error was that Postgres interpreted the column as Integer and performs a Euclidean division, which leads to the zero encountered.
Changing the car_count_last_week to a decimal, will give you the rational numbers division
 (car_count_last_week::decimal/100)

fixes the problem

CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_date  TIMESTAMP,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  ,

 CASE WHEN car_count_last_week = 0 then 1
 ELSE ROUND(car_count_current_week/(car_count_last_week::decimal/100)) * 100
 END
   AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

car_name | start_of_last_week  | today_but_last_week | start_of_current_week  | today      | car_count_last_week | car_count_current_week | difference_between_weeks
:------- | :------------------ | :------------------ | :--------------------- | :--------- | ------------------: | ---------------------: | -----------------------:
awesome  | 2021-05-03 00:00:00 | 2021-05-09 00:00:00 | 2021-05-10 00:00:00+01 | 2021-05-15 |                   1 |                      4 |                    40000

db<>fiddle here


Mysql join heavy query on large tables taking hours I'm trying to query our entire database to build a report and I've run into the problem that I have queries like the one below that are taking near 2 hours to run and some take even longer. I've changed the some of the names but the structure is the same.

Person_table has about 5 million rows

check_table is a subset of Person_table and has about 3 million rows

event_table has about 20 million rows

update  check_table as CT
    inner join
        ( SELECT  B1.id
            from  Person_table as PT
            inner join  event_table as B1  ON PT.id = B1.person_id
            inner join  event_type_table as E using (code_id)
            inner join
                ( SELECT  *
                    from  event_table as ET
                    where  ET.code_id in ('XYZ','ABC','DEF','ETC.')
                ) as F  ON ET.id = F.person_id
                       and  B1.Event_date = F.event_date
        ) as B2 using (id) set flag_1=1;

I've tried to get an explain on many of the queries I have to do but it always spits back a "Explain Data not available for statement".

It may (or may not) be possible to add some composite indexes to speed up the query.  It may (or may not) be possible to reformulate the query to speed it up -- perhaps by turning subqueries into JOINs.

But it seems like 3M possible updates will lead to a very slow query in any case.  I suggest chunking the UPDATE into no more than 1000 rows at a time.  This will avoid various locks that interfere with other traffic.  And it may actually run faster -- due to not needing to hang onto millions of rows for potential ROLLBACK.

Discussion of chunking:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

If you want me to check your indexes, please provide SHOW CREATE TABLE.


What is the request_id in sys.dm_exec_requests and how does it change? According to MSDN, the request_id in sys.dm_exec_requests is the

  "ID of the request. Unique in the context of the session. Is not
  nullable."

It always appears to be 0, from casual observation.

Can someone tell me how, for a given session, this id can ever be more than 0? Is it possible to reproduce this with some sql?

Thanks!

Can someone tell me how, for a given session, this id can ever be more
  than 0? Is it possible to reproduce this with some sql?

The request_id other than zero indicates a connection with Multiple Active Result Sets (MARS) enabled.  With MARS, a single connection may have multiple active statements, with statement execution interleaved.

From SSMS, open a new query window connection specifying MultipleActiveResultSets=Trueunder "Options-->Additional Connection Parameters".  Then run the query below in the window.  You should see a non-zero request_id value.

SELECT *
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;


My database dramatically increased in size. Why? I found that my database, specifically the USERS tablespace, dramatically increased in size.

How can I find what DML operations are performing on the objects located in this tablespace?

The query below will show you an overview of the schemas, tablespaces and objects in your database and their respective sizes.

- Change the group by to decrease the level of detail (you might want to see if the size is being caused by tables, indexes or other objects first)
- Add a where clause to only list the schema/tablespace/object_type you want to investigate.

select
  owner SCHEMA,
  tablespace_name TABLESPACE,
  segment_type OBJECT_TYPE,
  segment_name OBJECT_NAME,
  round((sum(bytes)/1024/1024), 3) MB
from dba_segments
group by owner, tablespace_name, segment_type, segment_name
order by owner, tablespace_name, MB desc;

It does not show you what DML is causing it, but it already shows you what objects are causing it. You can then go trough the query history in Enterprise Manager to find queries that work with these problematic objects.

In Enterprise Manager, go to the performance tab, and at the bottom select "Search SQL". Then search for queries with, for example, the large table in their "SQL text". Like this, for example: %large_table_name%

Good luck!


PostgreSQL: options for large table I'm working on a project dealing with genomic data in a Postgres database. There is one table in particular which is causing trouble. It is slowly approaching a billion rows and has three columns: snp_name varchar(32), genotype_id int, local_genotype char(2)

The first two are foreign keys and together form the primary key of the table.

Querying this data is still OK, response-time-wise. Inserting data takes ages, though. We are now at a point where we get new data faster than we can insert it, and we already COPY into a temporary table first and insert from there. Due to the server still running on spinning disks, inserting a genotype with a million rows takes about 5 hours.

Partitioning by genotype_id would be great, since this would result in ~ a thousand tables with a million rows each and a genotype is basically the package we get the data in. Then, querying by snp_name probably won't perform that well anymore, though. Whether we can live without it, has not yet been decided.

Do you have any suggestions for other options we could try? Are there any database systems, that allow indexing across partitions? Any NoSQL solutions that could fit?

EDIT: The table definition:

# \d user_snps
              Table "public.user_snps"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 snp_name       | character varying(32) |
 genotype_id    | integer               |
 local_genotype | character(2)          |
Indexes:
    "user_snps_new_genotype_id" btree (genotype_id)
    "user_snps_new_snp_name" btree (snp_name)

There is only one distinct value of genotype_id per insert, and all values of snp_name are unique per insert.

I'm thinking having two copies of that table, one partitioned by genotype_id, one partitioned by snp_name and adding a few constraints and triggers to keep it consistent might be a good enough solution.


Custom error code class and numbers for Postgres stored functions I'm writing a collection of stored functions in Postgres 11.5, and want to RAISE EXCEPTION when various pre-conditions are not met. For example, a null or empty string with a string is required, or an integer parameter that is out of range, etc.

I can RAISE EXCEPTION and provide details, hints, and a message...but what range should I use for the error code? I've checked the docs, but didn't find any guidance here:

https://www.postgresql.org/docs/11/errcodes-appendix.html

I searched on StackOverflow, found a similar question from years ago...but not a clear answer.

Is there some block or prefix that's safe or conventional to use for custom error codes coming back from stored functions/procedures?

The SQL standard gives you a guideline:

  The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed
  by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character
  encoding form and is restricted to <digit>s and <simple Latin upper case letter>s. Table 33, “SQLSTATE class
  and subclass values”, specifies the class value for each condition and the subclass value or values for each class
  value.

  Class values that begin with one of the <digit>s '0', '1', '2', '3', or '4' or one of the <simple Latin upper case letter>s
  'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other
  International Standard. The range of such class values is called standard-defined classes. Some such class codes
  are reserved for use by specific International Standards, as specified elsewhere in this Clause. Subclass values
  associated with such classes that also begin with one of those 13 characters are returned only for conditions
  defined in ISO/IEC 9075 or some other International Standard. The range of such subclass values is called
  standard-defined subclasses. Subclass values associated with such classes that begin with one of the <digit>s
  '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
  'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined conditions and are called implementation-defined subclasses.

  Class values that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s
  'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined
  exception conditions and are called implementation-defined classes. All subclass values except '000', which
  means no subclass, associated with such classes are reserved for implementation-defined conditions and are
  called implementation-defined subclasses. An implementation-defined completion condition shall be indicated
  by returning an implementation-defined subclass in conjunction with one of the classes successful completion, warning, or no data.

If your exception matches one of the SQLSTATEs in Appendix A of the PostgreSQL documentation, simply use that SQLSTATE.

If you need to use your own SQLSTATE, let it begin with any of 5 to 9 or I to Z.

If you need to define a custom warning, use an SQLSTATE that starts with 01 and whose third character is any of 5 to 9 or I to Z.


Backup of Job's List only Goal:
Reuse the SQL server agent jobs list.

Problem:
The list contains a lot of jobs and I need to reuse the jobs list in a another time without recreating it.

Based on the example of the picture, can I get the sourcecode of every job and enable reusing it when I establish a new database from scratch?

I would prefer not to recreate the job list from scratch.

Information:
*The list contains a lot of jobs.

I've built a command-line utility that creates a .sql script file containing T-SQL that will recreate all SQL Server Agent Jobs for the specified SQL Server instance.

The utility takes several command-line parameters, as below, which necessitates running it from the command prompt, cmd.exe.

MVCTSQLJobScripter.exe /server:localhost /outfile:C:\temp\AgentJobs.sql

Be aware that this tool comes with no warranties, and is use at-your-own-risk.  I have used it on my own SQL Servers, and it works for me.

The utility uses the Microsoft.SqlServer.Management.Smo.Agent.Job.Script method to output T-SQL, so the output should be precisely what is needed to recreate jobs.

You can download the binary and prerequisites from http://blog.mvct.com/technology/tool-for-scripting-sql-server-agent-jobs/

There are also many commercially available products that provide various features to move or copy jobs from server to server, such as the Idera SQL Admin Toolset.  I neither work for, nor am I associated with Idera in any way.


Read only replica part of always on availability group for Reporting As a DBA I would like to include a read only replica  into the always on availability group. XSLT / Crystal Reports will only be used.

Please see below

1) Primary Replica   (Same Data center)       Synchronous Mode Automatic Failover

2) Secondary Replica (Same Data center)       Synchronous Mode Automatic Failover

3) Secondary Replica (Read Only , Same Data center for Reporting ) Asynchronous Mode

4) Secondary Replica (Off Site , DR)         Asynchronous Mode

The will be a file share witness to form the quorum

My questions is will there be any problem if the read only replica for reporting  is included as part of the always on availability group instead of setting Transaction Replication with the Listener.

The secondary replica (Read Only) will be used for reporting only mainly XSLT and Crystal Reports , SSRS will not be used.

Any expert feedback will be highly appreciated.

My questions is will there be any problem if the read only replica for reporting is included as part of the always on availability group instead of setting Transaction Replication with the Listener.

Nothing you're not already dealing with such as an extra copy of the database, more networking IO, disk IO, etc., so that's kind of a moot point assuming the reporting server isn't massively underpowered for the reports you're running.

To be quite up front about it, I don't believe using transactional replication with Availability Groups is a good idea - it's technically possible, but beyond a pain to administrate over the long term unless you expect to never have a failover or swap in and out servers in the AG. The only time I'd go this route is if you only needed an extremely mall subset of data with vastly different indexes for reporting and couldn't do this on your primary. Outside of that I'd try my best to stay away from AGs + TR.


How can I recover all the Stored procedures, Functions, Events from previous server version Until recently I have been running MySQL5.5.8 - foolishly a couple of days ago I decided to upgrade to take advantage of new features to be found in version 8. Before the upgrade I did a backup, using MySQL Administrator of most, not all, databases - some were expendable. Perhaps foolishly I also backed up the mysql table which caused some issues when trying to import from the backup but after editing the sql file I appear to have all the databases back and accessible.

The problem though is there are no stored procedures ( of which there were many ) which means my development website will no longer load. The same is true of the functions, events and triggers - it appears that none of these were backed up by MySQL Administrator

The above shows the structure used for the datafiles. Within innoDB_datafiles folder is the ibdata1 file which looks to be approx 12Gb.

All the databases on the orginal server appear to have their own folder with various files for the tables.

Within the mysql database are the proc.myd and proc.myi files. There are similar files for events ( event.myd & event.myi ) and for functions (func.myd & func.myi ) - I can not see the equivalent for triggers so assume they are referenced elsewhere but not found that yet.

With the new MySQL8 server I created a new Stored Procedure to see if the proc.myd/myi file would be generated in the equivalent mysql directory - as per below screenshot

It appears that MySQL8 does things rather differently and I don't have a clue what to do next to be honest. I have tried running the following command in a gui (heidi)

select
    `r`.`specific_name` as 'name'
    from `information_schema`.`routines` as r
    where `r`.`routine_type` = "procedure";

This query would previously list all procedures perfectly well but on MySQL8 nothing! Similar story for functions:

select
  `r`.`specific_name` as 'name'
  from `information_schema`.`routines` as r
  where `r`.`routine_type` = "function";  // no results

There was success when I tried for triggers - they appear to have been within the backup file.

select `trigger_name` as 'name'
    from `information_schema`.`triggers`;

Again, success for a newly created event

select `event_name` as 'name'
    from `information_schema`.`events`; // :-)

The question is basically how can I restore my server to a healthy state with all the procedures, functions and events based upon the details within either the respective myd or myi files or within the original ibdata1file?

Any help and advice to help rectify this would be greatly appreciated!

Install the previous version of MySQL you have migrated from.
Import the dumpfile you have made.
Dump again in the proper way.
Install the newer MySQL version.
Import your proper backup.


SQL Server 2012 Standard Edition Disaster Recovery Options? Suggest me the best method for My DR plan. Currently we have set of databases (6) used for our application and we in the process of building DR and HA plan. As we are into small business we are using standard edition 2012 and we are unable to make use of AG feature.

Thanks for your help.

Could you please let me know the best option to proceed and least complexity to maintain

Logshipping is your best option for Disaster Recovery since you are on standard edition.

Make sure you test the disaster recovery - simulate it so that you are confident when a real disaster strikes.

Note: In my company, we use LogShipping as a primary DR solution involving 2500 databases globally and it works perfectly fine and fulfills our business continuity goal (it worked during Hurricane Sandy as well). We automate most of the things and test(simulate) every quarter per individual database and yearly the entire data center. THis gives us a huge confidence when doing an actual failover during a disaster.

Check out the things to keep in mind when going for Logshipping :

  Log shipping is a low-cost (poor-man) solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server and one or more secondary servers. A database is backed up and restored from the primary server to the secondary server. At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary server to keep the servers in sync.

Also, Kendra Little has an excellent post on Log Shipping Magic: Using A Differential Backup to Refresh a Delayed Secondary

Note: As per your question, it seems to me that you are mixing up HA and DR. They are different when it comes to RTO and RPO vs availability of your application.


How to backup large MongoDB database What is the recommended way to back up large data sets in MongoDB? Let's say we have a data size in the order of 10TB - how would you back that up?

We're considering a hidden, possibly delayed, replica set node. The delay would protect us from accidental drops of the whole database. Is this a viable solution, and what other options would you recommend investigating?

Thanks!

With the need of 10TB to be backed up this gets a bit complicated.

Replicas are no replacement for proper backups

While delayed replica set members can provide an relatively easy way to help you with accidental operations, there are no replacement for proper backups, very much like RAID isn't a replacement for file system based backups.

Recommendations

That heavily depends on how your setup looks like.

SAN snapshots

With 10TB, I assume you have some sort of SAN attached. The easiest way for backing up MongoDB in those environments is to make sure you have journaling activated both on the filesystem and MongoDB and simply take a snapshot on of the SAN volume of one of the secondaries, propably a hidden one to make sure your operations don't get interrupted. This usually takes mere seconds, but please _make sure_that your replication oplog window is sufficient. Otherwise, you might need to resync the secondary.

Do not use mongodump

I have to disagree with RolandoMySQLDBA about the usage of mongodump. First of all, it imposes locks on the server. Though they are lifted relatively fast, the shere number of locks might add up and interfere with your operations, unless run on a hidden node or when there is no read preference hitting the secondaries. Plus, it is not exactly fast. I'd expect it to run for hours, at least, most likely taking longer than your backup window. Side note: Always run mongodump with the --oplog option. Also keep in mind that mongodump does not backup indices, but the operations to create indices. Those indices have to be recreated during a restore, which may massively increase the time you need for it. From my experience, if you have to restore a database, you want to have it as fast as possible. Another point why mongodump isn't suited for backing up 10TB.

Notes on LVM snapshots

You can do an LVM snapshot on a running mongod instance provided that you have journaling enabled in mongod (and from my experience, it does not hurt to have it enabled on the FS level, too). However, LVM snapshots come with some implications. First, you obviously need to have enough disk space that can take the changes during the backup operations. Let me clarify that.

Let's assume you have an hourly change rate of 500GB. And that you want to have your backup blipped before it is uploaded to some storage. Even when using parallel bzip2, the compression of 10TB would need some hours to finish, simply because the fact that most likely you mass storage throughput would become your limiting factor. Let's assume it would take 2 hours to compress the data to 2TB. So by now we would need some 2TB + 2*500GB of free disk space total, 1TB needed for the LVM snapshot. This would create the need of over provisioning your filesystem by at least 30%. In case you want to have a proper safety margin, this could easily increase to 60-70% (20% for a utilization factor of 0.8 for the original file system, the same for the snapshot size plus the space needed for the bzipped backup itself). In most production environments, that would be inacceptable, since that over provisioning would be static (You would not want a backup script to mangle with your LVM dynamically, would you?).

MMS backup

While MMS backup has some awesome features (continuous backup, easy point in time recovery), it comes with some serious drawback: its's price tag for large deployments can easily be in the thousands. With an assumed hourly change rate of 500GB on those 10TB, it would be a medium six-figure sum for cloud backups. Monthly.

My suggestion he would be to take an enterprise subscription for your servers for being eligible to have an on premise MMS instance, including backup.

Summary

Here are the options I would take in descending order of preference.

SAN snapshots: easy to implement, relatively cheap
Enterprise subscription: Best features. Install it, configure it, forget it, it's there when you need it
LVM snapshots: easy to implement, but the costs of the necessary over provisioning may sum up over time.


mariadb 5.5.36 with innodb plugin I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to test the performance with it.

In my.ini I put under [mysqld] the following:

ignore_builtin_innodb
plugin-load=innodb=ha_innodb.dll

The plugin is located in BASEDIR/lib/plugin.

But when I start the service I get the following in the error log:

140315 18:03:04 [Note] Plugin 'FEEDBACK' is disabled.
140315 18:03:04 [ERROR] Plugin 'InnoDB' init function returned error.
140315 18:03:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140315 18:03:04 [ERROR] Unknown/unsupported storage engine: InnoDB
140315 18:03:04 [ERROR] Aborting

If I comment both lines out then everything runs fine.

Even when I try to run a plain MariaDB data dir (without the log files ...) the error occurs.

Is there someone who have a hint for me?

The method of using 'innodb_plugin' instead of default InnoDB is a MySQL 5.1 feature. The InnoDB plugin of 5.1 is the default of MySQL 5.5, and thus, in MySQL 5.5 we see this information for the 'ignore_builtin_innodb':

  In MySQL 5.1, this option caused the server to behave as if the built-in InnoDB were not present, which enabled InnoDB Plugin to be used instead. In MySQL 5.5, InnoDB is the default storage engine and InnoDB Plugin is not used, so this option has no effect. As of MySQL 5.5.22, it is deprecated and its use results in a warning.

This would be true for MariaDB 5.5 as well.


How to select the line with the max(date)? the data looks as following:

id group date
1  a     2013-01-01
1  b     2014-01-01
2  a     2012-01-01
2  b     2013-01-01

My database is mysql, for each id, I need to select the line with max(date), I wrote the SQL as following:

select id,group, max(date)
from table1
group by id;

But it does not work well.

SELECT B.* FROM
(
    select id,max(date) date
    from table1 group by id
) A INNER JOIN table1 B USING (id,date);

You should create this index to help this run faster

ALTER TABLE table1 ADD INDEX (id,date);

Sample Data Loaded

mysql> use test
Database changed
mysql> drop table if exists table1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table table1
    -> (id int not null,
    -> `group` varchar(10) not null,
    -> date date,
    -> key (id,date)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 (id,`group`,date) values
    -> (1,'a','2013-01-01'),
    -> (1,'b','2014-01-01'),
    -> (2,'a','2012-01-01'),
    -> (2,'b','2013-01-01');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+-------+------------+
| id | group | date       |
+----+-------+------------+
|  1 | a     | 2013-01-01 |
|  1 | b     | 2014-01-01 |
|  2 | a     | 2012-01-01 |
|  2 | b     | 2013-01-01 |
+----+-------+------------+
4 rows in set (0.00 sec)

mysql>

Here is the query executed

mysql> SELECT B.* FROM
    -> (
    ->     select id,max(date) date
    ->     from table1 group by id
    -> ) A INNER JOIN table1 B USING (id,date);
+----+-------+------------+
| id | group | date       |
+----+-------+------------+
|  1 | b     | 2014-01-01 |
|  2 | b     | 2013-01-01 |
+----+-------+------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Getting locked out of PostgreSQL databases One of the greatest fears I have is getting locked out of my own data due to the security features of a heavy weight database such as PostgreSQL (and PostGIS).  Whilst I can log in as the administrator, I would like to do things properly and create users with defined roles.  unfortunately in my preliminary testing, I have never been able to get this to work reliably.  I clearly do not understand the security model of PostgreSQL as managed by pgAdmin III.
Questions:

Is there a simple guide to setting up roles and users in PostgreSQL?
Can data be recovered in the case of lost passwords?
Can PostgreSQL be used with security off similar to ms-access?
In the PostgreSQL security model, the users are created at server level and can be assigned to different roles in each of the databases listed within a server?
Is there a trick with creating users and roles (see below)?

In ms-access if a user is created called 'ted' and given a password, then deleted and then a user called 'ted' recreated.  The first ted and second ted are not the same.  In addition to the visible name, there is a hidden unique code which is generated each time a user is created.  This makes it impossible to recreate a deleted user.  I just wonder if PostgreSQL has hidden logic like that that can bamboozle a new user.

The key issue I am having is that when I define permissions for a user on a particular database via PgAdmin III, I can't connect to the database using that users name and password from QGIS.

Currently, I am using SpatialLite to avoid this issue.

The documentation should cover that pretty well. See in particular pg_hba.conf. It's hard to make "simple" guides on security, because if you simplify in the wrong way you make it wrong.
Trivially so long as you have the ability to edit pg_hba.conf and reload the server. Just add an pg_hba.conf entry with trust authentication, reload PostgreSQL (pg_ctl reload, /etc/init.d/postgresql reload, whatever), change the password, and put pg_hba.conf back how it was.
Disabling hostbased and password login security in PostgreSQL can be done by setting trust authentication in pg_hba.conf. It's usually much saner to use peer authentication for local connections instead, but this only works on unix systems.
Users are just roles with the LOGIN attribute. Roles are global across the server. Most GRANTs of rights to users/roles are specific to particular databases or objects within databases. See the documentation for the GRANT command.
"trick?". I think you'll want to post a new, more detailed and specific question on https://dba.stackexchange.com/ with the details, including exact commands run, exact error messages, etc.

I'm kind of surprised by your expectation that dropping then re-creating a user would retain their settings. This would create the opportunity for colossal security holes. The sysadmin joe leaves, you drop their account. New data entry joe starts, you create their account. Oops! The new Joe has all the rights of the completely different old Joe.

If you drop a table then re-create it, does it still have its contents? Why would users/roles be any different?

If you don't want to drop a user account, don't drop the user account. Disable it  by REVOKEing CONNECT rights on the database(s), changing their password, etc.


Restore backup to different filegroup Using Sql Server 2008R2.

I have a backup of a filegroup. Is it possible to restore this to a different file group (possibly even on a different database)?

I have tried

RESTORE DATABASE my_database
   FILEGROUP = 'newFileGroup'
   FROM DISK='C:\restore\backupOfFileGroup.bak'
   WITH RECOVERY

But get the following error:

Filegroup 'newFileGroup' cannot be restored because it does not exist in the backup set.

Is it possible to restore this to a different file group

No.

  possibly even on a different database)

No.


CREATE SEQUENCE using expressions with psql variables for parameters I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression.
The specific goal is to automate create-scripts for disjoint ranges between servers to avoid key-conflicts in a multi-master setup.

For example, using psql:

CREATE SEQUENCE key_seq MINVALUE (:servernum * :stride)
                        MAXVALUE ((:servernum + 1) * :stride - 1);

This gives me a syntax error. Variables are interpolated verbatim, no calculations happen.

An alternative would be to set a variable based on the output of an expression:

\set minvalue (:servernum * :stride)

But the \set statement doesn't evaluate expressions.

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell commands, but I'll suggest two different approaches:

Option 1: Let Postgres calculate and set new variables with \gset

The manual about \gset:

  Sends the current query input buffer to the server and stores the query's output into psql variables

Then you can interpolate the calculated numbers as text and everything works.
psql code:

test=# \set servernum 5
test=# \set stride 300
test=# SELECT :servernum * :stride AS minvalue, (:servernum + 1) * :stride - 1 AS maxvalue \gset
test=# CREATE SEQUENCE key_seq MINVALUE :minvalue MAXVALUE :maxvalue;
CREATE SEQUENCE

A nice blog explaining \gset:

http://marxsoftware.blogspot.co.at/2015/08/postgresql-psql-variable-from-query.html

I calculated and set :minvalue and :maxvalue in a single SELECT to optimize performance. Still, the downside of this approach is that you need an extra round trip to the Postgres server.

Option 2: Function with dynamic SQL for repeated use

You could use a DO statement, but (the manual again):

  Variable interpolation will not be performed within quoted SQL literals and identifiers.

This would complicate string concatenation. I suggest to create a function and use format() for clean code. If it's for the current session only, you can make it a temporary function by schema-qualifying with pg_temp. So the function is only visible to your current session and dropped at the end of the session. Temporary functions are an undocumented feature - even though suggested by Tom Lane. Details:

How to create temporary function in Postgres

So:

test=# CREATE FUNCTION pg_temp.f_my_seq(_seq text, _servernum int, _stride int)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s'
                 , _seq, _servernum * _stride, (_servernum + 1) * _stride - 1);
END
$func$ LANGUAGE plpgsql;

test=# \set servernum 5
test=# \set stride 300

test=# SELECT pg_temp.f_my_seq('key_seq', :servernum, :stride);

We need dynamic SQL with EXECUTE. The manual about Variable Substitution:

  Variable substitution currently works only in SELECT, INSERT, UPDATE,
  and DELETE commands, because the main SQL engine allows query
  parameters only in these commands. To use a non-constant name or value
  in other statement types (generically called utility statements), you
  must construct the utility statement as a string and EXECUTE it.

Related answer on SO:

Error when setting n_distinct using a plpgsql variable


how to use a specific column for a day CREATE TABLE `csptargets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `cspcode` varchar(11) NOT NULL,
   `vol`decimal(20,3) NOT NULL DEFAULT '0.000',
   `day01` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day02` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day03` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day04` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day05` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day06` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day07` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day08` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day09` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day10` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day11` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day12` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day13` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day14` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day15` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day16` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day17` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day18` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day19` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day20` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day21` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day22` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day23` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day24` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day25` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day26` decimal(20,3) NOT NULL DEFAULT '0.000',
   `day27` decimal(20,3)  NULL ,
   `day28` decimal(20,3)  NULL ,
   `day29` decimal(20,3)  NULL ,
   `day30` decimal(20,3)  NULL ,
   `day31` decimal(20,3)  NULL ,
   `week1` varchar(11) NOT NULL,
   `week2` varchar(11) NOT NULL,
   `week3` varchar(11) NOT NULL,
   `week4` varchar(11) NOT NULL,
   `week5` varchar(11) NOT NULL,
   `week6` varchar(11) NOT NULL,
  `cnt` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UniqueTarget` (`cspcode`,`start_date`,`end_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1

In this table I want to access a particular column.

I can get the current date using

SELECT @day:=DATE_FORMAT(curdate(), '%d');
set @daily:=concat ('day',@day);

Now for today's date I have day28 .

But on using select @daily from csptargets it's giving value as 'day28', not the value I have in column.

Is there any other way to use the specific column name?

You really need to fix your design. Having 31 columns, one for every day and 6 columns, one for every week is not normalized and is going to cause a lot of pain on the road.

That aside, to solve the specific issue, you need dynamic SQL because the @daily = 'day28' is a string and you want it used as a column name:

-- the sql as a string
SET @sql = concat('select ', @daily, ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;

or:

-- the sql as a string
SET @sql = concat('select day',
                   date_format(curdate(), '%d'),
                  ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;


OK,lets try to write a query that:

doesn't use variables in any way:
is correct and produces the wanted output always:

SELECT q.*, 0 as aa_id
FROM table1 AS q
WHERE q.type = 0

UNION ALL

SELECT ans.*, q.acceptedanswerid as aa_id
FROM table1 AS q
  JOIN table1 AS ans
    ON ans.related = q.id
WHERE q.type = 0
  AND ans.type = 1

ORDER BY
    type,
    CASE WHEN id = aa_id THEN 0 ELSE 1 END,
    timestamp ;

or simplified with a LEFT JOIN:

    SELECT a.*
    FROM table1 AS a
      LEFT JOIN table1 AS q
        ON  q.id = a.related
        AND q.type = 0
    ORDER BY
        type,
        CASE WHEN id = q.acceptedanswer_id THEN 0 ELSE 1 END,
        timestamp ;

The real issue is the design of the table. The answers should be linked to their related question but they aren't. If there is some way to identify which is answer is related to which question, then the query shoudl be modified to take that into account. Until then, the query (and the table!) is only useful to store one question only and its answers.

As for the question about variables, no you shouldn't use them that way. you may get expected results but it's not safe and may break, either in a future release or due to some optimizer settings change or even because some index was added and a different plan was chosen.

You probably want this order in the results:

ORDER BY
    related,
    type,
    CASE WHEN id = q.acceptedanswer_id THEN 0 ELSE 1 END,
    timestamp ;


Replication problems after server upgrade In transactional replication - first 8000 rows of snapshot copies to subscriber snapshot then acts like it completed successfully.

Publication table has 21 million rows. Snapshot says completed successfully then we get the error "cannot find row at the subscriber" when transaction replication begins.

This is on a new 64 bit server running SQL Server 2005 Standard 64 bit SP3 with Cumulative Update 4. We have a standalone distributor also.

We upgraded the server / OS to Windows 2008 R2 64 bit and SQL Server from 32 to 64bit with the Cumulative Update 4 to take care of the paging issue/bug.

Has anyone ever ran into this situation?

Could any operations be altering the values of the primary key columns of the table?

That error can be generated when a replicated update or delete statement cannot find a specific PK value.


Tracing tabular refresh errors I have a tabular cube on an SQL Server 2017. The cube is refreshed with a full refresh command in a Server Agent job. The job runs on a schedule.

When I check the history of the job I see that it went through successfully, so everything is "green".

When I check the tables of the cube one of the tables is not processed well, it does not contain any data. Now with a manual processing of only this table the problem is solved. So it is not a table config/definition issue.

Where can I find more information about error message why this table is not processed in the automated full refresh command?

You can use Extended Events to track the processing history of a Tabular model.  The ProgressReportBegin and ProgressReportEnd events report processing information, per the documentation.  SSAS extended events are somewhat different than those in SQL Server.  After creation, an extended event is active.  To stop the event it must be deleted.  An extended event can be created through an XMLA request in SSMS using the XMLA query editor.  The following creates an extended event using the ProgressReportEnd event.  Multiple events can be included in single event session.  For instance, you could create a session with the both the ProgressReportBegin and ProgressReportEnd events.
 An XMLA command to delete this event is below as well.  There are several ways to access the results which the target property controls.  A couple options are viewing the results in SSMS (event_stream) and writing these to a file (event_file).
 In this example, the results are written to a local file using the event_file.  The contents of this file (processing activity) can be viewed in SSMS.  To do this right-click the file, select Open With then choose SSMS.  To delete an event use the ID of when the event was created as the TraceID in the delete request.

Create Extended Event:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ObjectDefinition>
    <Trace>
      <ID>XE_Process</ID>
      <Name>XE_Process</Name>
      <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
        <event_session name="XE_Process" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <event package="AS" name="ProgressReportEnd" />
          <target package="package0" name="event_file">
            <parameter name="filename" value="C:\Test\XE_Process.xel" />
            <parameter name="max_file_size" value="4096" />
            <parameter name="max_rollover_files" value="1" />
            <parameter name="increment" value="1024" />
          </target>
        </event_session>
      </XEvent>
    </Trace>
  </ObjectDefinition>
</Create>

Delete Extended Event:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <TraceID>XE_Process</TraceID>
  </Object>
</Delete>


Postgres table growing on massive updates I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing up.

The way that I'm restoring the size is making these queries:

VACUUM FULL [TABLE];
REINDEX TABLE [TABLE];

This will return the size of my [TABLE] to its original size.

That's working fine for me, but the problem is that I have to shutdown the application server to run those queries; if I run them with the server alive, the [TABLE] size is reduced, but just a bit.

My problem is reduced to 2 questions:
1. Why this table is growing if I'm just making updates?
2. How could I solve this problem without shutting down my application server?

Thank you very much for your time and your responses.

Update: Postgres versions are from 8.3.x up to 9.x

Update-2: The problem was because an IDLE connection was being kept alive by some application, by killing the connection and performing a vacuum all the problems were solved.

The reason that your table is growing while only making updates is that PostgreSQL is that "tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done."

Of course, while you are updating 1000 rows per second your updates are in contention with VACUUM FULL and perhaps REINDEX.

You should probably not be using the FULLoption.  See:
http://www.postgresql.org/docs/9.1/static/sql-vacuum.html

"Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained."

If that is not enough to resolve your problem, perhaps you could include the VACUUM and REINDEX in your job being driven by your application server.  Then periodically switch from updating rows to run a VACUUM.

EDIT: Note that 3 comments have pointed out autovacuum as a process that can run continually.  However, dezso mentioned that configuring it for a heavy load may not be straightforward.