how to chain postgres RULEs? I have implemented data denormalization strategy using postgresql RULEs. I picked rules instead of triggers for performance reasons.

Schema is structured like this:

Application has many clients
Client has many projects
Project has many users

One part of the system is storing hits for every user in stats table. Hit is an imaginary metric, it is not really relevant. System can collect many of these metrics. There are a lot of records in stats table (> 1,000,000 per day).

I want to know how many hits are per user, per project, per client and per application for given day.

To make it work fast, I've groupped stats by day and stored the output into user_hits table. During this process, also the application_id, client_id and project_id has been added (as columns), and appropriate indexes created.

I want to further optimise the process by grouping things by project_id, client_id and finally application_id. The data pipeline is like this:

stats -> user_hits -> project_hits -> client_hits -> application_hits

I want to make sure when I delete the data from user_hits for given day, that the data in project_hits for that same date is also deleted. This process should propagate to last table in chain.

I defined these simple rules:

CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  DELETE FROM client_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  DELETE FROM application_hits WHERE day = OLD.day;

However, when I issue statement like this:

DELETE FROM user_hits WHERE day = current_date;

I expect it to run these 3 queries in return:

DELETE FROM project_hits WHERE day = current_date;
DELETE FROM client_hits WHERE day = current_date;
DELETE FROM application_hits WHERE day = current_date;

However, it doesn't.

It completes the operation, but it takes couple of minutes to do that (with test data). With real data it takes hours, while running those 3 queries by hand takes couple of milliseconds.
The time it takes seems proportional to number of combinations (users x projects x clients x applications).

What is the problem here? Am I missing something? Can this be implemented with triggers in an optimised way?

Included sample script which reproduces the problem:

https://gist.github.com/assembler/5151102

UPDATE: Transition from user_hits to project_hits (and so forth) is done by worker process in background (since it involves contacting 3rd party services for additional info). It is smart enough to recalculate everything for missing dates. So the only thing i need is a way to DELETE records cascadingly in optimised way.

UPDATE: stats table is filled on daily basis. The only possible scenario is to unconditionally delete the data for whole day and then replace it with new values.

UPDATE: I noticed that the number of affected rows (extracted from explain statement) is exactly equal to the product of affected rows in user_hits, project_hits, client_hits, and application_hits tables (hundreds of millions of rows).

It turns out that it works like this:

I run DELETE FROM user_hits WHERE day = current_date;
For each row in user_hits table, RULE is triggered which deletes EVERY row from project_hits
For each row of project_hits, RULE is triggered which deletes EVERY row from client_hits
For each row of client_hits, RULE is triggered which deletes EVERY row from application_hits

So, the number of operations is equal to the product of count of affected rows in these tables.

Next time, please include the EXPLAIN output rather than making us dig for it in your scripts.  There's no guarantee my system is using the same plan as yours (although with your test data it is likely).

The rule system here is working properly.  First, the I want to include my own diagnostic queries (note I did not run EXPLAIN ANALYSE since I was just interested in what query plan was generated):

rulestest=# explain DELETE FROM user_hits WHERE day = '2013-03-16';
                                              QUERY PLAN

--------------------------------------------------------------------------------
----------------------
 Delete on application_hits  (cost=0.00..3953181.85 rows=316094576 width=24)
   ->  Nested Loop  (cost=0.00..3953181.85 rows=316094576 width=24)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..128.53 rows=6352 width=22)
               ->  Nested Loop  (cost=0.00..96.78 rows=6352 width=22)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..2.49 rows=16 width=16)
                           ->  Nested Loop  (cost=0.00..2.41 rows=16 width=16)
                                 ->  Seq Scan on application_hits  (cost=0.00..1
.10 rows=4 width=10)
                                       Filter: (day = '2013-03-16'::date)
                                 ->  Materialize  (cost=0.00..1.12 rows=4 width=
10)
                                       ->  Seq Scan on client_hits  (cost=0.00..
1.10 rows=4 width=10)
                                             Filter: (day = '2013-03-16'::date)

 Delete on client_hits  (cost=0.00..989722.41 rows=79023644 width=18)
   ->  Nested Loop  (cost=0.00..989722.41 rows=79023644 width=18)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..43.83 rows=1588 width=16)
               ->  Nested Loop  (cost=0.00..35.89 rows=1588 width=16)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..1.12 rows=4 width=10)
                           ->  Seq Scan on client_hits  (cost=0.00..1.10 rows=4
width=10)
                                 Filter: (day = '2013-03-16'::date)

 Delete on project_hits  (cost=0.00..248851.80 rows=19755911 width=12)
   ->  Nested Loop  (cost=0.00..248851.80 rows=19755911 width=12)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..16.91 rows=397 width=10)
               ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 width=10
)
                     Filter: (day = '2013-03-16'::date)

 Delete on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
   ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
         Filter: (day = '2013-03-16'::date)
(39 rows)

rulestest=# select distinct day from application_hits;
    day
------------
 2013-03-15
 2013-03-16
(2 rows)

rulestest=# select count(*), day from application_hits group by day;
 count |    day
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from client_hits group by day;
 count |    day
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from project_hits group by day;
 count |    day
-------+------------
   397 | 2013-03-15
   397 | 2013-03-16
(2 rows)

If your data is anything like your existing data, neither rules nor triggers will work very well.  Better will be a stored procedure which you pass a value and it deletes everything you want.

First let's note that indexes here will get you nowhere because in all cases you are pulling half of the tables (I did add indexes on day on all tables to help the planner but this made no real difference).

You need to start with what you are doing with RULEs.  RULEs basically rewrite queries and they do so using ways that are as robust as possible.  Your code also doesn't match your example though it matches your question better.  You have rules on tables which cascade to rules on other tables which cascade to rules on other tables

Therefore when you delete from user_hits where [criteria], the rules transform this into a set of queries:

DELETE FROM application_hits
 WHERE day IN (SELECT day FROM client_hits
               WHERE day IN (SELECT day FROM user_hits WHERE [condition]));
DELETE FROM client_hits
  WHERE day IN (SELECT day FROM user_hits WHERE [condition]);
DELETE FROM user_hits WHERE [condition];

Now, you might think we could skip the scan on client_hits in the first, but that isn't what happens here.  The problem is that you could have days in user_hits and application_hits that are not in client_hits so you really have to scan all tables.

Now here there is no magic bullet.  A trigger isn't going to work much better because, while it gets to avoid scanning every table, it gets fired every row that gets deleted so you basically end up with the same nested loop sequential scans that are currently killing performance.  It will work a bit better because it will delete rows along the way rather than rewriting the query along the way, but it isn't going to perform very well.

A much better solution is to just define a stored procedure and have the application call that.  Something like:

CREATE OR REPLACE FUNCTION delete_stats_at_date(in_day date) RETURNS BOOL
LANGUAGE SQL AS
$$
DELETE FROM application_hits WHERE day = $1;
DELETE FROM project_hits WHERE day = $1;
DELETE FROM client_hits WHERE day  = $1;
DELETE FROM user_hits WHERE day = $1;
SELECT TRUE;
$$;

On the test data this runs in 280 ms on my laptop.

One of the hard things regarding RULEs is remembering what they are and noting that the computer cannot, in fact, read your mind.  This is why I would not consider them a beginner's tool.


How to improve performance if index is not Most SELECTIVE? As per suggestion index must be most selective to improve the performance.
As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows
The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.

Example with good selectivity:

A table having 100K records and one of its indexed column has 88K distinct values, then the selectivity of this index is 88K / 100K = 0.88.

Now I come to the point.
I have one table having 180K records.
Fields that are being frequently used in search criteria are:

Search record using Name of user.
Field Type :-> Not null , nvarchar(32).
Unique records are 627
Search record using Active_date.
Field Type :-> DateTime ,Null.
Unique records are 85627
Search records using  Current_state
Field Type :-> Not Null , nvarchar(32).
Unique records are only 2  that are "Pending" and "Closed".

Currently all above fields are indexed.
In terms of selectivity, case (1) and (3) are not the most selectives. What should I do with them in order to improve performance?

Without having much detail, I can't recommend much.

One thing that does jump out at me is that it's very likely you can improve performance on the table by normalizing it! The presence of so many duplicated (so few unique) values in the columns you listed suggests that perhaps many others in the table are not normalized, as well. I'm suggesting making the Name column an int (or even smallint) with a foreign key to a Names table, and the Current_state column bit (or alternately a tinyint) with a foreign key to a WhateverStates table. You would have to, of course, change your data access code to deal with this indirection, but that is nothing more than the basic job relational database developers have always had to do.

Normalizing will reduce the number of bytes per row, increasing the number of rows per page, reducing the number of pages that have to be read to satisfy any particular query, helping performance across the board! Right now the columns given require likely close to 34 bytes each. After the change I suggest, those columns will only require 11 bytes each. Of course, I haven't seen your whole table--your rows may be so big that it doesn't matter.

What columns and datatypes are in the clustered index (if there is one)? This can radically affect the size of the nonclustered indexes, again affecting performance in exactly the way I described (rows per page).

When you do query based on non-selective columns such as Current_state, what other columns are always or almost always included? It may be okay for you to have a nonselective column in an index if the index also contains a more selective column (or that in conjunction with the less selective column is more highly selective). If on the other hand you generally query often for rows based on the single column Current_state = 'Pending', then you can add a filtered index:

CREATE INDEX IX_YourTable_Pending ON dbo.YourTable (ClusteredColumnsInOrder)
WHERE Current_State = 'Pending'; --SQL 2008 and up only

This technique could help you even when you also include other columns: you would want to put those in the index instead of the ClusteredColumnsInOrder columns I suggested (which was just a tricky way to not put any additional columns into the nonclustered index, since--remember, now--nonclustered indexes always have all the columns of the clustered index implicitly included). Or, if you only pull a very few other columns, you can make your nonclustered index cover the query by adding INCLUDE (AdditionalColumn1, AdditionalColumn2) so that the query engine doesn't have to go back to the clustered index to satisfy the query.

You haven't provided very much information such as full table schema, sample data, and sample queries, and without those it's going to be pretty hard to give you very specific advice about what to do.

One thing I can say, though, is that indiscriminately throwing indexes at the table may not improve things much and could in fact hurt performance of your system overall.

If the hints I have given you here don't seem to help much, then I recommend that you do come back with some of the additional info I mentioned so that we can do a better job of assisting you.


How can a trigger be bypassed? Assuming I have the following table:

Create Table [dbo].[Test_IP]
(
  [IP] varchar(40),
  [IPType] varchar(6)
)

There are more triggers on this table, but all of them are AFTER triggers, and it is large DB with many tables, views and stored procs, and it been used from several processes.

I added a log table and triggers like this

Create Table [dbo].[Log_Test_IP]
(
  [Action] varchar(10),
  [IP] varchar(40),
  [IPType] varchar(6)
)
GO
Create Trigger MYTR_Log_Test_IP_INS On [dbo].[Test_IP] After insert AS
BEGIN
   insert into  [dbo].[Log_Test_IP]([Action],[IP],[IPType])
       select 'Insert', [IP], [IPType] from inserted
END
GO
Create Trigger MYTR_Log_Test_IP_DEL On [dbo].[Test_IP] After delete AS
BEGIN
   insert into  [dbo].[Log_Test_IP]([Action],[IP],[IPType])
       select 'Delete', [IP], [IPType] from deleted
END
GO

Now I'm getting to the interesting part: when I directly insert to the Test_IP table I can see the Insert trigger is working, but when it works regulary from an application or service (which I have no idea what it does) I don't see any Insert records in the log table, I can only see the 'Delete' records even though it was enpty in the start.

My conclusion is that there is some way to bypass the triggers, there are many triggers and stored procedures in the DB and I have no idea where to look.

So, my question is how can you bypass the trigger?

Triggers will not run in a couple of scenarios.

1) Obviously if the trigger is disabled

2) If the trigger is marked as NOT FOR REPLICATION and the DML is issued by the replication Distribution Agent.

3) If the user has ALTER TABLE permissions and is performing a BULK INSERT, BCP or SqlBulkCopy, etc with the FIRE_TRIGGERS option disabled.


OLE DB error 0x80004005 using Copy Database Wizard Background: freshly installed SQL Server 2012 SP3 on Windows Server 2012R2.

Problem: When I use the Copy Database Wizard to copy a database from server2 to server2 I receive:

  While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).

Yes, I use the wizard to make a database copy on the same server. (Accounting software and testing purpose.)  I receive this error when the wizard tries to create the Integration Services package.  Also what's important, it happens when I use SMSS from my computer. I'm a sysadmin on this SQL Server. I even added my user to this Windows Server's admin group.

We've got an old server1, which is configured in very similar way as server2. I checked every setting that came to my mind and both configs looks the same. No proxies, no users in SSIS Package Execution.

I can copy a database from server1 to server1 and from server2 to server1 without any problem.

It looks like I don't have sufficient right to location: server2_server2\DTS Packages\Copy Database Wizard Packages whatever it is.

Theoretically I could use RDP to server and domain admin account every time but that's pain in the... back. Also there's accounting software support that has high SQL rights but can't have domain admin.

I googled this and usually it's about SQL Server 2008 and running some custom packages.

Community wiki answer:

Use backup and restore instead.

You can write dynamic SQL to do what you said, or just use Copy-DbaDatabase in the PowerShell dbatools to fully automate the task.


Backup and restore "mysql" database I use the following command to backup some normal databases:

mysqldump --host=host --user=user --password=passwd --result-file=result.sql -R --single-transaction database_name

My question is : do I need to also dump the "mysql" database ?
Note that I will create new users for each database after "restoring" them. So I don't need the "mysql" database for user information.

Thanks

Most people that perform mysqldumps just use the --all-databases option. That will include the mysql database. There are two schools of thought as to whether one should include the mysql database.

Why not to include mysql ???

When you mysqldump the mysql schema, you should make yourself aware of differences in MySQL versions, particularly the mysql.user table.

In MySQL 5.0, mysql.user has 37 columns
In MySQL 5.1, mysql.user has 39 columns
In MySQL 5.5, mysql.user has 42 columns

Restoring a mysqldump from one version can result in certain privileges disappearing when restored :

Make sure you handle dumping user grants as a special script. There are two methods for this:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

I have discussed this before

Apr 09, 2012 : Mysql users deleted
Apr 12, 2012 : Cannot GRANT privileges as root
Jun 13, 2012 : Fastest way to move a database from one server to another

Why include mysql ???

The only case for which you can mysqldump the mysql schema is to restore it to the same version of mysql

UPDATE 2014-12-30 17:42 EST

My original answer covered just the MySQL Grants since this particular aspect of the mysql schema is the easiest to break and overlook.

There are other reasons not to just backup the mysql schema unless it is necessary

If you want to retain the timestamps for all Stored Procedures, you can mysqldump the mysql.proc table by itself and load it. You can also just copy the proc.frm, proc.MYD, and proc.MYI then reload it by copying it back into the mysql subsfolder and runninf FLUSH TABLES;
If you use table based logging for the general log (mysql.general_log) and slow log (mysql.slow_log), you do not want to backup these logs and move them to another server where the logs do not semantically apply. It is your choice if you want to restore them.
If you set any timezone info in the mysql, be very careful if you do not want to restore the mysql schema in a data center that is in another timezone.

If you want to be careful about what you want to backup and restore in the mysql schema, you should mysqldump the mysql schema in a separate file.

The number of tables in the mysql schema, as well as the number of columns, will vary from version to version. So, you should login to mysql and run

SHOW TABLES FROM mysql;

The list of tables may not include events (like MySQL 5.0), may not have InnoDB system tables (like MySQL 5.6 has), may not include tablespace grants (like MySQL 5.6), and so forth. Sp, if you feel you must backup the mysql schema, you be very conservative (ok very picky and careful) about what tables you truly need to backup from the mysql schema.


MariaDB does not start after restoring incremental backups with mariabackup OS: CentOS 7

MariaDB: mariadb  Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

I am creating a full backup every 7 days on a replication slave, and an incremental backup every hour on top of that.

This script is used to create backups: run-mariabackup.sh

To restore the backup I am using this process.

However, after restoring the backup MariaDB does not start.

systemd: Starting MariaDB 10.4.12 database server...
mysqld: [Note] /usr/sbin/mysqld (mysqld 10.4.12-MariaDB) starting as process 11363 ...
mysqld: [ERROR] mysqld: File '/var/lib/mysql/aria_log_control' not found (Errcode: 13 "Permission denied")
mysqld: [ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
mysqld: [ERROR] Plugin 'Aria' init function returned error.
mysqld: [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
mysqld: [Note] InnoDB: Using Linux native AIO
mysqld: [ERROR] InnoDB: Operating system error number 13 in a file operation.
mysqld: [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
mysqld: [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
mysqld: [ERROR] Plugin 'InnoDB' init function returned error.
mysqld: [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
mysqld: [Note] Plugin 'FEEDBACK' is disabled.
mysqld: [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
mysqld: [ERROR] Failed to initialize plugins.
mysqld: [ERROR] Aborting
systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE

Of course I have already done chown -R mysql:mysql /var/lib/mysql prior to that, so this error puzzled me. After this error I tried chmod -R 777 /var/lib/mysql to see if there is some permission that was missing, but starting MariaDB again provided me with the same error.

Here are the exact steps I took to restore the backup:

cd /home/mysqlbackup/
for i in $(find . -name backup.stream.gz | grep '2020-03-12_12-08-44' | xargs dirname); do
        mkdir -p $i/backup;
        zcat $i/backup.stream.gz | mbstream -x -C $i/backup/;
done
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_13-24-20/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_13-54-25/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_14-00-02/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_15-00-01/backup/
mariabackup --prepare --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --incremental-dir incr/2020-03-12_12-08-44/2020-03-12_16-00-01/backup/
systemctl stop mariadb
mv /var/lib/mysql/ /var/lib/mysql_bak/
mariabackup --copy-back --target-dir base/2020-03-12_12-08-44/backup/ --user backup --password mypass --datadir /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mariadb

When the permissions look correct, but you're still getting Errcode: 13 "Permission denied", then the next thing to check is whether a security system is causing the problem. On CentOS that system is SELinux. To check if it's running in enforcing mode, execute this:

sestatus

You can do setenforce 0 to temporarily change it to permissive mode.

To avoid the problem:

When you initially install MariaDB, the SELinux security context should be set correctly for all the files. However, when you do mv /var/lib/mysql/ /var/lib/mysql_bak/, then you remove it. I think you can get it back by simply executing restorecon -Rv /var/lib/mysql.

For reference, read this page in the MariaDB Knowledgebase: Security-Enhanced Linux with MariaDB


Display table names in titles in SQL Server Management Studio Does anyone know how to get SQL Server Management Studio to display the Table names in the Title.  Right now they are next to useless and every time I need to look at a table I have to reopen it.

In the good old days I used to use to Enterprise Manager and that was much more useful.

I found Brent Ozars article here, and the corresponding entry in Options... doesn't seem to be anything useful in there.  Maybe a 3rd Party Product?

No, there is no way to make Management Studio do that. You've opened a query window, and you can customize what is displayed there, but table name is not one of the choices. After all, a small number of queries involve only one table. And think about these questions:

If you add another query to that window, what should the title become?
If you change the query it produced to be a join, what then?
If you've saved the query as some file, should the table name override the name you gave?

These questions are rhetorical, since the functionality is not possible anyway, but there are many other technical considerations that go far beyond right-clicking a table and saying SELECT TOP...

SSMSBoost seems to give you some control over these things, but I don't see table name as being an option here either (probably for the same technical challenges I raised in my bullets).


Extract start and end per group of rows, where only the end can be identified I have a table that looks like this.

id  date_from           date_to             description           vehicle
1   2015-10-21 08:00    2015-10-21 10:00    GARAGE TO CLIENT 1    1
2   2015-10-21 12:00    2015-10-21 13:00    CLIENT 2 TO GARAGE    1
3   2015-10-21 14:00    2015-10-21 15:00    RET GARAGE            1
4   2015-10-21 18:00    2015-10-21 19:00    GARAGE TO CLIENT 1    1
5   2015-10-21 20:00    2015-10-21 21:00    CLIENT 2 TO GARAGE    1
6   2015-10-21 21:00    2015-10-21 22:00    RET GARAGE            1

I need to get the total time that the vehicle is in use.
He stops being in use on first line and stops on the first description that starts with "RET".

A table like that would return

vehicle   starts              stops
1         2015-10-21 08:00    2015-10-21 15:00
1         2015-10-21 18:00    2015-10-21 22:00

You can use count() as window function to identify groups like this:

SELECT vehicle, min(date_from) AS starts, max(date_to) AS stops
FROM  (
   SELECT vehicle, date_from, date_to
        , count(description LIKE 'RET%' OR NULL)
             OVER (PARTITION BY vehicle ORDER BY date_from DESC) AS grp
    FROM  tbl
   ) sub
GROUP  BY vehicle, grp;

Count in descending order, since the end of each group is significant. Then just extract minimum start and maximum end per grp in the outer SELECT.

SQL Fiddle.

Assuming all timestamp columns are defined NOT NULL.

Since timestamps are in ascending order (due to the logic of the problem), we don't need the id column at all for this.

This works for any number of vehicles, not just the one you demonstrate in your example.

Related answer with more details:

Select longest continuous sequence

About my counting method count(<expression> OR NULL):

For absolute performance, is SUM faster or COUNT?


Are db_recovery_file_dest and db_recovery_file_dest_size parameter dynamic or static? Please have a look at the output below:

As I know we can check if the parameter is static or dynamic in Oracle using this query. Please correct me if I am wrong.

But I have seen something strange that "Even if db_recovery_file_dest and db_recovery_file_dest_size parameter are dynamic parameter, the data dictionary is showing FALSE value". As I understand from this SQL query, the mentioned parameters are static parameters.

I am not able to understand which one is correct or I am getting something wrong.

The isinstance_modifiable flag only tells you whether that parameter can be set differently for different instances (for RAC) or not. For the parameters you're looking at, it is false, meaning that all instances in a single RAC cluster must have the same values.

If you want to know when you can change a parameter, look at two other columns in that table:

isses_modifiable: if TRUE, the parameter can be changed with alter session
issys_modifiable: tells you about changes that can be made with alter system.

IMMEDIATE means yes and changes take effect immediately,
DEFERRED can be changed, but will only take effect at next start,
FALSE can only be changed if you used an spfile to start the instance, and changes only take effect on next start.

Complete details are available in the v$parameter documentation.


Splitting a column in two and returning a summary of results Lets take a very simple table

Department           | Staff Name
---------------------|---------------
Web - Electronics    | Matthew
Store - Electronics  | Mark
Web - Home           | Luke
Store - Home         | John
Store - Home         | Paul
Store - Home         | Simon
Web - Electronics    | Peter

I need to work out some SQL to return the following:

Deparment    | Web  | Store
-------------|------|---------
Electronics  | 2    | 1
Home         | 1    | 3

So I need to do a count of web and store for each department. Is it possible to do something like this in SQL?

In C# I would loop through the results. I would split the deparment into two strings on the -. I would add an entry in a dictonary so the deparment name is the key. For the entry I would have a small object with a field of Web and Store. I would then increment the value by one if the entry is stor or web.

After this I would then loop throgu hmy dictiaonry to output the result by priting the dictionary key then printing the count for web and store.

I am not even sure where to begin trying to do this in just SQL...

You could do something like this.

declare @T table
(
  Department varchar(50),
  [Staff Name] varchar(50)
)

insert into @T values
('Web - Electronics',    'Matthew'),
('Store - Electronics',  'Mark'),
('Web - Home',           'Luke'),
('Store - Home',         'John'),
('Store - Home',         'Paul'),
('Store - Home',         'Simon'),
('Web - Electronics',    'Peter')

select T3.Department,
       sum(case T3.WebStore when 'Web' then 1 else 0 end) as Web,
       sum(case T3.WebStore when 'Store' then 1 else 0 end) as Store
from @T as T1
  cross apply (select replace(Department, ' - ', '.')) as T2(Department)
  cross apply (select parsename(T2.Department, 1),
                      parsename(T2.Department, 2)) as T3(Department, WebStore)
group by T3.Department


Same query but different plan on different server I have recursive CTE query, it was taking 30s to execute, after investigation I found out that a column (Name) with datatype nvarchar(MAX) was the problem so I changed it to nvarchar(1024). After which execution time reduced to 2s on DEV env. However, when I perform same alter on QA environment I don't see any improvement. Strange thing is that both servers are generating different execution plan, both server has same configuration, same schema. In fact DEV has more data.

Query:

WITH Ancestor (Id, TreeLevel, Branch, Name) AS
(
    SELECT
        I.Id, 0 AS TreeLevel,
        CAST(I.Name AS VARCHAR(1024)) AS Branch,
        I.Name
    FROM
        dbo.Items AS I
    WHERE
        (I.Parent_Id IS NULL)

    UNION ALL

    SELECT
        D.Id, A.TreeLevel + 1 AS TreeLevel,
        CAST(A.Branch + ' -> ' + CAST(D.Name AS VARCHAR(1024)) AS VARCHAR(1024)) AS Branch,
        D.Name
    FROM
        dbo.Items AS D
    INNER JOIN
        Ancestor AS A ON A.Id = D.Parent_Id
    WHERE
        (D.Parent_Id IS NOT NULL)
)
SELECT
    A.Id, A.TreeLevel, A.Branch, A.Name
FROM
    Ancestor AS A

Execution plan on DEV:

Execution plan on QA:

Is there a way to know why plans are different on both server, and how to force QA to follow same plan as DEV?

Solved the problem by altering the index on parent_id

CREATE NONCLUSTERED INDEX [IX_Parent_Id] ON [Items] ([Parent_Id]) INCLUDE (Name) WITH DROP_EXISTING


Can we commit the transaction when the sql server trigger fails? I am trying to avoid data loss when the insert trigger on a table fails. I am trying this with the following scenario and my code is failing.

When an insert happens on the Customers table, I want to insert the same data into the Archive table. When the trigger fails, I don't want to roll back the entire transaction, which would lead to data loss on the Customers table.

Even if the trigger fails, I want the data to be inserted in the Customers table, and the stored procedure should return the Customer_ID as usual.

ALTER TRIGGER [dbo].[Customer_Insert_Trigger_Test]
   ON  [dbo].[Customers]
   AFTER INSERT
AS
BEGIN

BEGIN TRY
    begin transaction;
    set nocount  on;
    SAVE TRANSACTION InsertSaveHere;
    --Simulating error situation
    RAISERROR (N'This is message %s %d.', -- Message text.
       11, -- Severity,
       1, -- State,
       N'number', -- First argument.
       5); -- Second argument.

   Insert into Archive select * from Inserted;
    commit transaction;

  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION InsertSaveHere;
  END CATCH
END

My question is mostly around how to avoid the actual insert on customers table to roll back if the trigger fails. How to change my code for that?

The Question mentions that the "code is failing" but there is no indication of any error message or what specifically is failing. Including at least one, if not both, of those pieces of information always helps get better answers.
For the moment, I see something that appears to be an incorrect assumption about Triggers and Transactions: you increment the @@TRANCOUNT by calling BEGIN TRAN; but only decrement @@TRANCOUNT when there is no error and the COMMIT TRAN; line executes within the TRY block. In the case of an error, the COMMIT is skipped and a ROLLBACK of the Save Point occurs. But rolling-back a Save Point does not decrement @@TRANCOUNT in which case the INSERT operation ends and the Transaction is still active.
Triggers exist within an internally started Transaction that binds it to the DML operation that fired the Trigger. This is how you are able to call ROLLBACK within a Trigger to cancel that DML operation.
Option 1 (prevent the error in the first place — PREFFERED if the error condition can be reliably tested for)
(Added 2020-10-21: Not sure how or why I didn't think of this option when I posted this answer)
If at all possible, if the condition(s) that cause the error can be tested for, then best to test for that condition prior to attempting the operation that sometimes fails. If the error never actually happens, then you don't need to alter default behavior and/or add custom transaction handling. In your case (i.e. adding the new record(s) to the archive table), you might could do one of the following:
SET NOCOUNT ON;

BEGIN TRY

    INSERT INTO dbo.Archive
        SELECT *
        FROM   inserted ins
        WHERE  NOT EXISTS (SELECT *
                           FROM   dbo.Archive arc
                           WHERE  arc.[Login] = ins.[Login])
                 -- assuming [Login] field exists and should be unique
END TRY
BEGIN CATCH
    DECLARE @DoNothing INT;
END CATCH;

There are times when this won't catch everything and there still might be the occasional violation, in which case you might still need to also use Option 2. But, even if using Option 2 and it always works, still best to attempt preventing the error because work had to be done in order to arrive at the condition causing the error, and that is just wasted time, IO, contention, etc.
Option 2A (prevent errors from cancelling the Transaction — PREFERRED if there are multiple DML statements in the Trigger)
With this in mind, you should be able to remove the BEGIN TRAN; and COMMIT TRAN; lines in order to get this working. The net-effect will be that if there is no error, the INSERT into the Archive table will commit as expected, but if there is an error, it will do the ROLLBACK to the Save Point and carry on.
HOWEVER, after removing those two pieces, you are still left with the sticky situation of getting the following error:

Msg 3931, Level 16, State 1, Procedure Customer_Insert_Trigger_Test, Line XXXXX
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

The reason for this behavior seems to be an implicit setting of XACT_ABORT ON by the system as it calls the Trigger. The effect of XACT_ABORT ON is to cancel the Transaction (and the query batch) for most errors (except compilation errors or from RAISERROR). The remedy? Just set XACT_ABORT OFF at the beginning of the Trigger.
For example, the following works for me:
CREATE
--ALTER
TRIGGER [dbo].[Customer_Insert_Trigger_Test]
   ON  [dbo].[Inserts]
   AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT OFF;

BEGIN TRY
    PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR(10), @@TRANCOUNT); -- for debug only
    SAVE TRANSACTION InsertSaveHere;

    -- Simulating error situation
    DECLARE @Error INT = 1 / 0; -- runtime error

   -- Insert into Archive select * from Inserted;
END TRY
BEGIN CATCH
    PRINT 'Entering CATCH block...'; -- for debug only
    ROLLBACK TRANSACTION InsertSaveHere;
END CATCH;

END;

Please note that this method does not alter the expected behavior of Triggers on this table with respect to: 1) the actual COMMIT happening at the outer-most layer (either the initial DML statement or beyond that if an explicit Transaction had been started prior to that statement), 2) the ability of other potential Triggers on this table from issuing a ROLLBACK to cancel the operation, and 3) the ability of an explicit Transaction, started prior to the DML statement on this table, from issuing a ROLLBACK to cancel all changes including the DML operation on this table.
Option 2B (prevent errors from cancelling the Transaction — PREFERRED if there is a single DML statement in the Trigger)
Of course, if the only thing that could error here is the INSERT into the Archive table, then you could probably also get rid of the SAVE TRAN and ROLLBACK TRANSACTION InsertSaveHere; and just do something in the CATCH block so that it isn't empty, something like DECLARE @Test INT; might work. The reasoning here is that a single DML statement that errors never really happened, so there is nothing to roll-back ;-).

Option 3 (NOT PREFERRED)
To answer the Question as stated in the title: you should be able to COMMIT within the Trigger, but I would be eXtreeemely cautious about doing such a thing as it alters the expected behavior of when the Transaction will Commit or Rollback, which might prevent proper operation of other Triggers on this table (they won't be able to issue a ROLLBACK to cancel the operation if this Trigger runs first), and would prevent intended operation of an explicit Transaction started prior to the DML operation on this table.
To do this (NOTE: you need to have read the paragraph directly above before continuing to read this paragraph), you would issue a COMMIT TRAN; (since the Trigger already exists within a Transaction), and then execute a BEGIN TRAN;. The COMMIT TRAN; will commit the initial DML operation, and the BEGIN TRAN; will put the @@TRANCOUNT back to 1 so that when the Triggers execution ends, you don't get the error stating that the Trigger ended with a different @@TRANCOUNT than it had when it began.


SQL Server : database recovery from snapshot files We have completely lost our SQL Server 2000 database in an accident. The only thing left is a snapshot folder which contains bunch of .sch, .idx and .bcp files.

The sch and idx files have a clear text format and holding the database schema.

The .bcp files looks to be binaries encoded somehow.

Is there any way to recover the data from the .bcp files?

Depends on what these bcp files are. If there is a plenty of them and they are named after tables in your database, then chances are, they are table dumps created with (probably) bcp.exe. Also looking at their creation dates might provide some insight.

If the naming pattern does not correlate with tables, maybe these are database backups. You can check this by running restore headeronly against them - if they are indeed MSSQL backups, you will see some data about when they were created and what kind of backups they are.


Physical Server or a Virtual Machine SQL Server? Is there a anyway to figure out via a SQL Script to know whether SQL Server is running on a Physical Server or Virtual Server ?

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model.
OR
If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio.

DECLARE @result int
EXEC @result = xp_cmdshell 'SYSTEMINFO'

  System Manufacturer:       VMware, Inc.
  System Model: VMware Virtual Platform

New columns (virtual_machine_type, virtual_machine_type_desc) in DMV sys.dm_os_sys_info are introduced in SQL Server 2008 R2 Service Pack 1

http://blogs.msdn.com/b/sqlosteam/archive/2011/05/11/more-on-dmv-changes-in-sql-2008-r2-sp1.aspx


Oracle schema import is not importing all the tables present in the schema dump file I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported.

Here are the export and import commands i have used.

Export Command:
---------------
exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log

Import Command:
---------------
imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y

The Oracle we are using is 10g EE.

What could be going wrong ? Can you please suggest a solution to this issue.

Use following statement to see what is exactly in export file:

imp sreeni/sreeni@agentrics1:1524/ezmodc file=SreeniTrunkDump.dmp indexfile=SreeniTrunkDump.sql full=y

This statment will not try to import data, just dumps DDL to indexfile.
Check the generated file to see if table is in export.

Alternatively if you are on unix

strings SreeniTrunkDump.dmp|grep <missingtable>

If table is in export file something goes wrong during import. Missing tablespace for example. Check the logfile of import statement (BadriSchemaImport_Import.log) for that.


Move DTC from one node to another in Windows 2003 R2 Clustering I have a Windows 2003 R2 two node cluster that is running MSDTC as part of the Cluster Group.  Is it possible for me to change the current MSDTC owner without moving the entire Cluster Group to the new owner?

It is my understanding if I run the following command I will move the entire Cluster Group to Node to and not only the MSDTC.

cluster group "Cluster Group" /move:Node2

I don't see anything under the cluster resource command here that allows me to change the resource owner.

You can only move groups, not individual services - if the msdtc has other resources in the same group, then they will need to move as well. To get around this, create a new clister group and include just the msdtc, then you're free to move it without impacting any other cluster resources or services.


Permissions required to execute UDF Scalar Function inside a trigger I have a scalar function that user don't have execute permissions on. However the users have select , insert, update , delete permissions on a table that has a trigger and the trigger calls the scalar function. The user gets an error when they try to run the function saying that they don't have execute permissions on the function which is fine. But when the user runs a update query on the table that fires off the trigger, the functions runs fine . How does that work?

Permissions on indirectly referenced objects are not checked as long as the objects involved have the same owner. This is known as ownership chaining. Users need permission on only the object used directly (table in this case), not on the objects used by the trigger (e.g. function) as long as the ownership chain is unbroken.

One can leverage ownership chains to encapsulate functionality in views, stored procedures, triggers, and functions without granting direct permissions.

See the Ownership Chains topic in the Authorization and Permissions in SQL Server documentation for more information.


sql server: get all the databases in which a login is being used I'm creating a few migration scripts to generate a sql server database from scratch.

In my '0005-create_login.up.sql' script I have something like:

CREATE LOGIN [mylogin] WITH PASSWORD=N'xxx=',
GO

And in my '0005-create_login.down.sql'

DROP LOGIN [appws]
GO

Now I'd like to drop the login only if it is not being used in another database. I had a look at sp_helplogins code with:

sp_helptext 'sp_helplogins'

but it's really complex. I wonder if there's and easier way to get a login with the databases it is being used.

Check out my answer on a similar question. Code below will return a list of logins and a flag if they're currently mapped to a database user.

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] <> 'SERVER_ROLE'
    AND [login].[name] <> 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins


Is there any way to run the definition of multiple stored procedure at a time? I have created the definition of more than 200 stored procedures. My problem is that I have to execute them on a different server and on a different database.
The process which I am following is that first I check each stored procedure that it exists on that server and database or not. If not then I create them else I drop the earlier definition and create the new one.

So I just wanted to know that is there any other way out. As I have to perform this activity for each stored procedure. I want to create a script or so, which can run and create the definition of all my stored procedure on the server and database.

Try right clicking on the database in Management Studio and selecting Tasks > Generate Scripts. The wizard that runs will let you create scripts for any objects in the database. In your case you'll want to select all "Stored Procedures".

In SQL Server 2008, you can click the "Advanced" button and change the value of the "Script DROP and CREATE" property to "Script DROP and CREATE" if you want to generate both scripts.

In SQL Server 2005, you may need to generate one set of DROP scripts and then another set of CREATE scripts (sorry I don't have an instance handy to test with).

See this link for more info:
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/


Limit left join for multiple rows Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables.

Let's overview simple example.

Assuming there two tables:

CREATE TABLE `items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `sub_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `child` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `parent_id` INT NOT NULL,
  PRIMARY KEY (`id`));

With the following content:

INSERT INTO `items` (`parent`) VALUES ('Main item1');
INSERT INTO `items` (`parent`) VALUES ('Main item2');
INSERT INTO `items` (`parent`) VALUES ('Main item3');

INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item11', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item12', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item13', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item21', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item22', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item23', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item24', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item31', '3');

Now let's create query something like this:

SELECT i.*, si.child, si.parent_id FROM items as i
LEFT JOIN sub_items as si
ON i.id = si.parent_id;

The result will be:

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'1', 'Main item1', 'Child Item13', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'2', 'Main item2', 'Child Item23', '2'
'2', 'Main item2', 'Child Item24', '2'
'3', 'Main item3', 'Child Item31', '3'

Now the challenge is to limit join table rows (in this example limit is 2 rows):

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'3', 'Main item3', 'Child Item31', '3'

If we try to use query bellow to achieve desired outcome MySQL will throw error:

SELECT i.*, si.child, si.parent_id FROM items as i
LEFT JOIN (SELECT * FROM sub_items WHERE parent_id = i.id LIMIT 2) as si
ON i.id = si.parent_id;

Then what is alternative approach to get result described above with 2 (or more) limit rows for each left join subquery?

What version of MySQL are you using? MySQL 8 supports LATERAL which you need to be able to refer to i from the derived table:

SELECT i.*, si.child, si.parent_id
FROM items as i
LEFT JOIN LATERAL (SELECT *
                   FROM sub_items
                   WHERE parent_id = i.id
                   LIMIT 2) as si
    ON i.id = si.parent_id;

Concider adding an ORDER BY to your sub-select.

Other ways to do something similar is to use a window function such as:

row_number() over (partition by ... order by ...) as rn

and filter rn at the outer level.

Window functions are however only supported in MySQL 8, but you can mimic them using variables:

SELECT i.*, si.child, si.parent_id, si.rn
FROM items as i
LEFT JOIN (
    SELECT c.*,
        @row_number:=CASE WHEN @parent_id = parent_id
                          THEN @row_number + 1
                          ELSE 1
                     END AS rn,
        @parent_id := parent_id
    FROM sub_items as c
    CROSS JOIN (select @row_number := 1) as x
    CROSS JOIN (select @parent_id := -1) as y
    ORDER BY parent_id
) as si
    ON i.id = si.parent_id
    AND si.rn <= 2
;

The idea is to order the sub-query by parent_id, increment row_num by 1 for each row, and as soon as parent_id changes, reset row_num to 1.

You can read more about the idea at for example: mysql-row_number


How to keep history for a one-to-many relationship? I have a simple one-to-many (1:M) relationship but I need to keep a history for the changes.

For example, a task can have many employees, but each employee can only have one task at a time.

I need to keep a history for the task assignments in order to know the past tasks that were assigned to each employee.

I know that I can convert it to a many-to-many (M:N) relationship and add a “flag” column, but this will not keep the relationship constraint that stipulates that only one task can be assigned.

I'm pretty sure that there is a design pattern or best practice for the case but I can't find it. I'm using Oracle DBMS.

Employee Columns:

ID
Task_ID  (FK_Task_ID)
Employee Name
Other Employee Data

Task Columns:

ID  (PK)
Task Details

This is a direct (1:M) relation. The required is what if I want to keep log for all the tasks assigned for a certain Employee? The trivial solution would be make a many to many relation and adding intermediate table as follows:

Employee_Task Columns:

ID
Employee_ID (FK_Employee_ID)
Task_ID (FK_Task_ID)

This will remove the constraint that each employee should have only one assigned task at a time.

Suppose we have 3 tables - one for employees, one for tasks, and one for "task_assignments", like so:

create table employees(
  employee_id number primary key
, employee_name varchar2(64)
, additional_information varchar2(64)
);

create table tasks(
  task_id number primary key
, task_details varchar2(64)
);

create table task_assignments(
  employee_id number references employees(employee_id)
, task_id number references tasks(task_id)
, start_date date not null
, end_date date
, unique (employee_id, start_date)
);

The task_assignments table stores "historical" data.  We assume that each employee can only work on one task at a time (as stated in your question).  The task has a start_date (or time) and end_date.  We can use a trigger for preventing INSERTs if an employee already has an "open" task (ie the end_date of the task is null).  NOTE: the example trigger does not cover UPDATEs.

create or replace trigger one_active_task_only
before insert on task_assignments
for each row
declare
  open_tasks number := 0 ;
begin
  select count(*) into open_tasks
  from task_assignments
  where employee_id = :new.employee_id
    and end_date is null ;

  if open_tasks >= 1 then
    raise_application_error (-20500,'This employee already has an open task');
  end if;
end one_active_task_only;
/

alter trigger one_active_task_only enable;

Testing and more details etc: see dbfiddle


Merge Replication From Multiple Express to Central So I am trying to setup a merge replication from multiple remote SQL 2016 Express installations to a central SQL 2016 Server. This would be for reporting purposes so I would need all the express installations to report information to the central server daily.

The trouble I am having is that the main server is setup as the distributor and publisher (publisher is a blank database with the 41 tables we are wanting to report information into), and the express installs are all subscribers. Is this even possible and if it is, can someone point me in the right direction for documentation as I am not finding any for this purpose.

Thanks for all help!

Is this even possible

Yes.  Once you have the subscriptions created and initialized then you insert your data into the tables created on the subscribers.  The next time the subscriptions synchronize, the data will be sent to the publisher.


Badly-formed histogram causes bad estimates on Nested Loop We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This caused that the Sort operator spilled to TempDB.
The estimates on the inner (index seek) and outer input (index seek) of the Nested Loops Join are correct.
I added trace flags 2363 (Selectivity Computation) and 3604 (redirect the output to the messages window) and here I found that there was a badly-formed histogram:

Plan for computation:

  CSelCalcExpressionComparedToExpression( QCOL: [Object1].Column1 x_cmpEq QCOL: [Object3].Column18 )

Loaded histogram for column QCOL: [Object1].Column1 from stats with id 1  *** WARNING: badly-formed histogram ***

Loaded histogram for column QCOL: [Object3].Column18 from stats with id 9

Selectivity: 1.07973e-009

Stats collection generated:

  CStCollJoin(ID=4, CARD=1 x_jtLeftSemi)

      CStCollBaseTable(ID=1, CARD=5.01133e+007 TBL: Schema1.Table2 AS TBL: AA)

      CStCollFilter(ID=3, CARD=108210)

          CStCollBaseTable(ID=2, CARD=2.00511e+006 TBL: Schema1.Table1 AS TBL: A)

End selectivity computation

Above is only a part of the output, the full text can be found here
When I updated the badly-formed histogram with fullscan the estimates are correct (without fullscan this issue is not resolved).
But as soon as one record is inserted in the table, the histogram is badly-formed again.
The query plan (with badly-formed histogram) can be found here and here you can find the query plan after update statistics.
No query optimizer fixes are enabled.
When I enable the original cardinality estimator for this query, using trace flag 9481, I'm getting the same query plan as after update statistics.
What can cause the badly-formed histogram?
Is there a way to solve this issue?
I tried the PERSIST_SAMPLE_PERCENT option but it didn't make a difference, the histogram gets also badly-formed.

What can cause the badly-formed histogram?

This is a bug of course. It occurs when SQL Server scales the inexact histogram and encounters a unique column with apparently more than one value per key. This was partially addressed for SQL Server 2016, but it seems to me the full fix is only present on SQL Server 2017 (I tested on CU21).

Is there a way to solve this issue?

Use FULLSCAN statistics, the original CE, upgrade to SQL Server 2017, or wait for the full fix to be back-ported.
Demo
Using my standard Numbers table creation script:
DROP TABLE IF EXISTS dbo.Numbers;
GO
-- Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 10 * 1000 * 1000
OPTION (MAXDOP 1);
GO
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

Update the statistics to be sampled:
UPDATE STATISTICS dbo.Numbers;

Trivial query (request an estimated plan only):
SELECT *
FROM dbo.Numbers AS N1
WHERE EXISTS
(
    SELECT *
    FROM dbo.Numbers AS N2
    WHERE N2.n = N1.n
)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363);

The trace output includes:
Begin selectivity computation

Input tree:

  LogOp_LeftSemiJoin
      CStCollBaseTable(ID=1, CARD=1e+007 TBL: dbo.Numbers AS TBL: N1)
      CStCollBaseTable(ID=2, CARD=1e+007 TBL: dbo.Numbers AS TBL: N2)
      ScaOp_Comp x_cmpEq
          ScaOp_Identifier QCOL: [N2].n
          ScaOp_Identifier QCOL: [N1].n

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [N1].n x_cmpEq QCOL: [N2].n )

Loaded histogram for column QCOL: [N1].n from stats with id 1
    *** WARNING: badly-formed histogram ***

Selectivity: 1

Validated on Microsoft SQL Server 2016 (SP2-CU14).
The statistics histogram obtained via:
DBCC SHOW_STATISTICS
    (Numbers, [PK_Numbers_n])
    WITH HISTOGRAM;

has entries like:

on SQL Server 2017 the histogram is:

Note the EQ_ROWS steps are pre-scaled rather than relying on the CE to do it.


Why is the OCR&Vote disk group servicing SCAN name I have installed 11g R2 Grid Infrastructure followed by database software only installation. I have not created any database yet.

When I open asmca->diskgroup, I can see the diskgroup which I have created during grid installation. When I select that diskgroup->View Serviced Databases, what is the expected result? I am assuming it to be empty. But, in my case, I am seeing my Cluster name( SCAN name) as database name and used space is .26GB. Can somebody clarify on this. Thanks in advance.

That is the Oracle Cluster Registry (OCR) where information about clusterware components is stored.

http://docs.oracle.com/cd/E11882_01/rac.112/e41959/intro.htm#CWADD90952

Its size is 256 MB, or 0.26GB, you can check this with ocrcheck:

# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2948
         Available space (kbytes) :     259172
         ID                       : 1703252243
         Device/File Name         :       +OCR
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded


How do I add read/write permissions on a directory for an SQL Server account I have created an account in SQL Server that I use in a connection string for a c# application.

I am trying to export data from my c# database programmatically,

INSERT INTO OpenRowSet(
'Microsoft.ACE.OLEDB.12.0'
, 'Excel 12.0;Database=C:\Temp\TestExcel.xlsx;'
, 'SELECT * FROM [categoryData$]'
)
SELECT * FROM categoryData;

When i do this I receive an error

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

So I did some research and came across the following guide:

https://visakhm.blogspot.co.uk/2013/12/how-to-solve-microsoftaceoledb120-error.html

I'm on step 2 where I have to add permissions to the Temp folder to the account accessing the database.

  This is needed because the provider uses the temp folder while
  retrieving the data. The folder can be one of the below based on
  whether you use a local system account or network domain account.

  For network accounts, folder is

  :\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

Here is my connection string (wrapped for readability):

SqlConnection con = new SqlConnection("Data Source=x.x.x.x,1433; Network Library=DBMSSOCN;
Initial Catalog=footfall; User ID=access; Password=pw;connection timeout=0; ");

Am I correct in assuming that the user account I need to add permissions for is "access" - Which is a login I setup myself.  If so, how to I add this account as I can't find it in the security dialog.

Edit: Using answer below from Max Vernon I completed STEP 2 of the guide but STEP 3 I cannot as I am using a 64 bit version of SQL Server.  Does anyone else know how to get round this error as I would really like to be able to export my data to excel.

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

EDIT: I managed to get this working yesterday by adding a domain admin account as the log in account for the sql server service, then outputting the file to a network drive.  I still cant get it to work locally.  I can get it to write rows into the spreadsheet by manually editing the column headings to match those in the table.  I've written some new code to create the spreadsheet using System.Runtime.InteropServices; &
Microsoft.Office.Interop.Excel; Then I can export the data.

The rights need to be provided to the SQL Server Service Account, which is typically an Active Directory account setup specifically for SQL Server to use.   Microsoft has a great Docs page that describes Service Accounts for SQL Server.

Check with SQL Server Configuration Manager to determine the name of that account.

Assign rights to the temp folder for that account.

In the image above, you can see SQL Server Express is running under the NT AUTHORITY\NetworkService account - any account that begins with NT AUTHORITY is an account local to the machine running SQL Server.


In SSMS, is "Navigate to" (Ctrl+,) of any use? In standard Visual Studio 2012 or higher, IDE window called Navigate to... (Ctrl+,) offers all useful objects in scope of currently open project (see samples) getting increasingly filtered as I am typing so I can quickly navigate to what I need without expanding the tree with hundreds of items.

The same Navigate to... I can find in SQL Server Management Studio 2014 (perhaps since SSMS 2012?) but as I type, I can see only some backup files in SSMS %TEMP% directory. Is this everything what can be get from Navigate to...?

I would like to see it offering me names of tables, stored procedures and SQL functions etc. so I can jump to them in Object Explorer or view their definition. Any tricks to make Navigate to... more useful in the SSMS?

(Currently I have SSMS 2014 connecting to SQL Server 2008 R2.)

Note: I'm aware of various ways of navigating in SSMS, but now I would like to learn more about Navigate to... which is convenient typing-oriented  search&go tool.

In SSMS 2016, Navigate To... is gone.

So the answer is that temporary presence of non-functioning Navigate To... feature in SSMS 2014 looks like forgotten artifact of adopting SSMS to Visual Studio platform. Perhaps we should seek nothing more serious behind it.

Comment: SSMS 2016 seems to be much more polished, it fixes many weird technical and design errors seen in SSMS 2014, including non-standard keyboard shortcuts. After leaving good SSMS 2008 and 3-of-5-stars experience with SSMS 2014 I recommend installing standalone SSMS 2016, which can get 4.5. They even can be installed and used side-by-side, if someone is hesitant to uninstall that 2014 thing on the first day. So far, I found only one issue with SSMS 2016.


Why doesn't MySQL installation on an Ubuntu linux 16.04 OS require user to specify LD_LIBRARY Why doesn't MySQL installation on ubuntu 16.04's linux require the user to specify LD_LIBRARY_PATH to resolve its runtime dependencies?

I tried googling to find the answer to this question, but I found nothing.

The expert @roaima says, "Because MySQL is a packaged application that stores its libraries in one of the "well known places".


What is the best practice for storing large data when nvarchar(4000) is sufficient 99% of the time? I have a table with about 100MM rows that stores information about a user.

CREATE TABLE [dbo].[UserData](
        [UserDataID] [int] IDENTITY(1,1) NOT NULL,
        [UserID] [int] NOT NULL,
        [DataId] int NOT NULL,
        [DataValue] [nvarchar](4000) NOT NULL,
        [EncryptedDataValue] [varbinary](max) NULL)

I need to be able to store data in DataValue that is > nvarchar(4000). But, only a very small percentage of the rows actually need this column as nvarchar(max). I know that as soon as DataValue is over nvarchar(4000), internally, sql will store the data as a blob[?], substantially increasing the time it takes to make this change. (not sure how read/write time will be affected later).

I thought of a few potential options...

Change DataValue from nvarchar(4000) to nvarchar(max) and just eat the time it takes to make the change; not worrying that only 1% of the rows are using MAX?
Alongside DataValue, add a DataValueXL column that is nvarchar(max) and introduce application logic to save in the appropriate column depending on the size of the data? (Marking both as NULL)
Create a new table FKed to UserDataId to store only large DataValues > 4000?

Which--if any--should I go with?

thanks

as soon as DataValue is over nvarchar(4000), internally, sql will store the data as a blob

This is incorrect. SQL Server will use off-row storage for both max and non-max types. In other words your NVARCHAR(4000) may also be stored off-row. The actual storage location will depend on whether the row fits or not on a page and on the exact combination of sp_tableoption values for the large value types out of row and text in row settings.

Your best bet is to change it to max. SQL will store the value in-row whenever possible, offering fast access. This makes application programing much simpler as you don't have to deal with the actual location (Column) based on size. You should only be concerned if you have an objection on adding the first max type column to a table (eg. it will prevent online rebuild operations), but you are already willing to add one so that should not be an issue.


Oracle 11gR2 - Dynamic Registration Database version: Oracle 11gR2
We want to use DYNAMIC_REGISTRATION_LISTENER parameter with off option.
Our listener.ora file like this:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = oracle)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

DYNAMIC_REGISTRATION_LISTENER = OFF

But when we use this parameter, our web service request cannot connect to the database.
SQL> SELECT dbms_xdb.gethttpport FROM dual;

GETHTTPPORT
-----------
       3010

Without this parameter:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-FEB-2021 03:21:59
Uptime                    0 days 0 hr. 2 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=3010))(Presentation=HTTP)(Session=RAW))

With this parameter:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-FEB-2021 03:21:59
Uptime                    0 days 0 hr. 2 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

We need to use this parameter. Web services also need to be connected. How is that possible?
I could not find any clear solution.
Best regards,

why do you "need" to use this parameter?  I suspect yet another x-y question - that you are asking the wrong question to solve the wrong problem. Alternatively, why can't you just add the necessary entry to the listener configuration?
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=3010))(Presentation=HTTP)(Session=RAW))
    )
  )


How can I log the way in which a job was started? Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database.

The Job is run on a couple of schedules and is also occasionally started by hand.

On the log row, I'd like to be able to record what it was that started the Job. In other words, I'd like to detect whether the Job was started by schedule 1, schedule 2, or ad hoc.

Is this possible? If so, how?

Here is script to get running jobs status along with Schedule information

SELECT  sj.name JobName,
        sj.start_step_id StartStepID,
        s.name JobScheduleName
FROM    msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysschedules AS s ON js.schedule_id = s.schedule_id
WHERE   sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NULL


Executing local scripts against remote server? I am in a situation where I would like to execute a number of sql scripts that are on my local machine against a remote MySQL instance.  I found this question which seems to point me in the right direction.  After reading this I tried the following:

$ ssh -L 12341:127.0.0.1:3306 root@myServer &
$ mysql -h 127.0.0.1 -p 12341

But it's been throwing an "Access denied..." error for 'abe'@'localhost'.  What I would like to do is connect as root from the destination server.  So instead of running as abe@localhost I'd like to run as root@myServer.  I tried:

$ mysql -h 127.0.0.1 -p 12341 -u root@myServer

but this only returns the same error for user, 'root@myserver.com'@'local'. I also tried -u root and got access denied again.

Is there any way for me to specify that I want to connect at root on the destination server in my above example?

Obligatory security disclaimer: You shouldn't be ssh-ing into a remote machine as root, neither should you be connecting to MySQL as root.  But I digress.  At least the question implies that the remote MySQL server isn't directly accessible from the Internet.

It's a little bit difficult to be sure whether you're doing this incorrectly or you simply made an error in typing the question.

  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).

You need a capital 'P', not a lowercase 'p', before the port number on the 2nd line... otherwise, you're probably connecting to your local mysql server on port 3306, not the remote one tunneled back to port 12341.

Assuming for a moment that the ssh tunnel is actually being set up correctly, the correct syntax would be:

$ mysql -h 127.0.0.1 -P 12341 -u root -p

That is, connect to the MySQL server listening at 127.0.0.1 on port 12341 with username 'root' and a password which will be prompted for.  Since your local port 12341 is tunneled to the far-end machine, and the destination address in the tunnel is 127.0.0.1, you will be known to the far-end machine as 'root@localhost' where "localhost" means the far end machine -- not your machine.  It's "that" localhost, not "this" localhost.

Now, let's go back and look at the tunnel.  The correct form would be this, assuming ssh version 2:

$ ssh -N -L 127.0.0.1:12341:127.0.0.1:3306 root@remoteserver &

The -N option stops ssh from spawning a remote shell, which you don't need, and which may cause problems when you try to send it to the background with &.

Your environment might be different, but if I omit the -N, my tunnel doesn't work, and I see the following:

[1]+  Stopped                 ssh -L ...

After the -L, the first mention of 127.0.0.1 means "only listen on 127.0.0.1 of my local machine" as opposed to listening on all ipv4 interfaces.  If you don't do this, then every IP address on your machine is exposing the remote MySQL server to anybody who connects on your port 12341.


Invalid syntax near RECURSIVE when creating a routine I'm trying to restore an mysqldump file but it complains about an syntax error related to my procedures.

ERROR 1064 (42000) at line 2860: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RECURSIVE emptree (id, username, owner_id) AS (SELECT id, username, owner_id FRO' at line 3

Here is the routines syntax on the generated file:
--
-- Dumping routines for database 'XXXX'
--
/*!50003 DROP FUNCTION IF EXISTS `str_rand` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `str_rand`(u_count INT UNSIGNED,
    v_chars TEXT
) RETURNS text CHARSET latin1
    NO SQL
    SQL SECURITY INVOKER
BEGIN
    DECLARE v_retval TEXT DEFAULT '';
    DECLARE u_pos    INT UNSIGNED;
    DECLARE u        INT UNSIGNED;

    SET u = LENGTH(v_chars);
    WHILE u_count > 0
    DO
      SET u_pos = 1 + FLOOR(RAND() * u);
      SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
      SET u_count = u_count - 1;
    END WHILE;

    RETURN v_retval;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `getSubResellerIds` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `getSubResellerIds`(ABC INT(11) )
BEGIN
    WITH RECURSIVE emptree (id, username, owner_id) AS (SELECT id, username, owner_id FROM reg_users WHERE id = ABC UNION ALL SELECT c.id, c.username, c.owner_id FROM reg_users c JOIN emptree p ON p.id = c.owner_id)
    SELECT id FROM emptree;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

Any help?

It helps to pay attention to what error messages say (emphasis mine):

check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RECURSIVE"

meaning this particular version of the server has trouble understanding what "RECURSIVE" means. When we do check what the manual says about "recursive", we indeed find that

Recursive Common Table Expressions have been supported since MariaDB 10.2.2.

Generally it's a bad idea to use different DBMS versions for development and production. As mentioned in the comments, "grade your desktop up".


Many to Many association table PK as FK in other table I have the following tables: school, teacher, school_teacher and student.

school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher.

A student belongs to a teacher of a particular school and a teacher of a school has many students.

My question is whether I should create the foreign key in the student table to point to school_teacher table PK or to the two tables PKs (school, teacher) individually?

I believe it would be best for referential integrity to reference school_teacher to ensure that transaction records are only inserted if there is a relationship between the school and the teacher. In terms of best practices, what is the best option or the best way to address this problem? Is there any other possibility better than the ones I mention?

In general, is there a problem in which the primary key of an association table is a foreign key in another table?

This is simply an example. In a real case a student could have more than one teacher. It is to simplify the example.

The culprit is the autogenerated key: school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher..

Why do you need it? If school and teacher is a candidate key I would concider that as the p.k.

EDIT: sketch

CREATE TABLE SCHOOLS
( SCHOOL_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE TEACHERS
( TEACHER_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE SCHOOL_TEACHERS
( SCHOOL_ID ... NOT NULL
      REFERENCES SCHOOLS (SCHOOL_ID)
, TEACHER_ID ... NOT NULL PRIMARY KEY
      REFERENCES TEACHERS (TEACHER_ID)

,     PRIMARY KEY (SCHOOL_ID, TEACHER_ID)
);

I find the rule "A student belongs to a teacher of a particular school" a bit odd, but if that is what you like to enforce:

CREATE TABLE STUDENTS
( STUDENT_NO NOT NULL PRIMARY KEY
, SCHOOL_ID ... NOT NULL
, TEACHER_ID ... NOT NULL

,     FOREIGN KEY (SCHOOL_ID, TEACHER_ID)
      REFERENCES SCHOOL_TEACHERS (SCHOOL_ID, TEACHER_ID)
, ...
);


SQL Server 2012 + SQL Server 2008R2 on the same VM I can't immediately find reference to it, but I remember reading somewhere that it's best practice not to mix versions of SQL Server on the same host. I believe the rationale was that often times, especially with clustering, you will need to install (Windows) hotfixes and those hotfixes might affect the different versions of SQL Server in unexpected ways.

However, it appears that mixing versions of SQL Server is supported by Microsoft.

I've tried searching on this, but it's been difficult to find a definitive source telling me that it would be OK to mix versions of SQL Server.

Can anyone definitively say it is OK to mix versions, or instead show strong evidence for why you should not?

We have mixed instances of SQL 2008R2 and 2012 on the same server running either windows server 2008R2 or 2012.

there are no problems at all and it is supported by Microsoft.

note that we are a company that make software for investment banks and our software is used in biggest and critical deals like Facebook, twitter, etc globally and we do hosting for different investment banks that use our software.


Missing rows (gaps) on range read from the end of the table On a data processing pipeline, getting gaps in returned data.

Process A - inserts sequentially into a table. Table has an auto-increment primary key id.

Process B - selects max(id) [into X] and then attempts "select * between id X-100 and X".

On occasion the number of rows returned is less than 100, but on rerun the query returns them all.

Tried adding "lock in share mode" to the select. Did not seem to help with getting all the data. [Expected it to have the reader wait until all current writes are finished, and then return all rows.]

Reads are done on a MySQL replica. Table is InnoDB.

You are inserting things into a single table.  That table is acting as a "queue".  Each 'insert' is effectively (even if it is not written this way):

BEGIN;
get an AUTO_INCREMENT id -- but not yet visible, nor replicated
do some stuff (including INSERTing the row)
COMMIT;
send to Slave(s) -- only after COMMIT

Since you have multiple threads doing this, the getting of the ids is not synchronized with the replication, so the ids arrive out of order at the Slave(s).

Instead of depending on AUTO_INCREMENT values to arrive in order, devise some other way to discover "new" items in the queue.  Note that no technique can precisely keep the items in order.

One technique it to use an UPDATE to flag, say, 100 rows as being handled by your Slave 'worker'.  But, beware, if the worker dies, you will need a 'reaper' to figure out what really got processed.

If you would like to discuss this further, please describe the data flow more precisely ("processing pipeline" is too vague).  I encountered your specific problem more than a decade ago, and have since dealt conjured up multiple solutions.

My main solution is "Don't queue it, just do it."  This came from analyzing a system that was running too slow; it turned out that the queuing (especially in a cross-country replication environment) was more overhead than the processing!  Ripping out the queue allowed nearly 10x capacity!


How do I switch master with slave and vice versa on MySQL? I have two instances of MySQL, one master and one slave.

How can I switch them to make the slave the master and vice-versa while avoiding downtime?

If you use a DBVIP, you could remove the DBVIP from the Master and bring it up on the Slave.

EXAMPLE

Let's make up a DBVIP, like 10.1.2.30.

Put this up on the Master in the OS

ip addr addr 10.1.2.30/24 dev eth1

Replace your IP in your application with 10.1.2.30, Let the application continue to run for a while, making sure the DBVIP is being used by the application.

Here is something aggressive:

service httpd stop on all Apache Servers
service mysql stop on the Master
ip addr del 10.1.2.30/24 dev eth1 on the Master
ip addr add 10.1.2.30/24 dev eth1 on the Slave
service httpd start on all Apache Servers
SHOW PROCESSLIST; on the Slave to make sure there are incoming DB Connections

If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover.

UPDATE 2012-09-19 14:28 EDT

If you cannot use a DBVIP, you must do more work instead

STEP 01) Activate Binary Logging on the Slave

Add this to /etc/my.cnf on the Slave

[mysqld]
log-bin=mysql-bin

STEP02) service mysql restart on the Slave

Binary Logging should be enabled in the Slave

STEP03) Run the CHANGE MASTER TO command on the Master using the Slave as its Master

Use mysql-bin.000001 as the Master_Log_File
Use the following number as the Master_Log_Pos

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0

STEP04) Run START SLAVE; on the Master

At this point

the Master is the Slave's Slave
the Slave is the Master Slave

This configuration is better known as

Master/Master
Circular Replication

STEP05) service httpd stop on all Apache Servers

STEP06) Change the IP address in the App to Connect to the Slave

STEP07) service httpd start on all Apache Servers

STEP08) SHOW PROCESSLIST; on the Slave to make sure there are incoming DB Connections

If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover without MySQL Downtime. The only downtime there is comes from the Window of time STEP05 - STEP07.


How can we implement "A student can enroll in up to 4 units in a semester " I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into. The relationship between Units and Students is such that a student can enroll in up to 4 units per semester but a unit can be enrolled into by many students. My problem is, how can I implement this limitation of a student enrolling in up to only 4 units per semester in mysql using either CHECKs or triggers?
If it was one to many relationship between Student and Units that would have been straight forward but here we have a one to four(?) relationship?

I see at least 2 possibilities.
First - BEFORE INSERT / UPDATE triggers which checks the amount of enrolls per student and forbids insertion / updation if the restriction fails. UPDATE trigger needed for to restrict errorneous data while updating with student ID change.
Second - add enroll_number column which is NOT NULL, ENUM('1','2','3','4') and UNIQUE in the combination with student ID.
I am not considering the option where the table contains 4 fields for 4 separate enrolls - this is denormalized structure and bad practice. Formally the same for a variant when all enrolls are stored in one column of some serialized datatype (JSON, XML).


How to upgrade a MySQL database using numbered scripts based on a version field I have a database that contains a version table with one value stored in it which is the database version number.

I would like to be able to update the database version automatically using numbered scripts.

If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below:

001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql

What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence?

Should I use an SQL script or create a bash script?

I am a little stuck on how to approach this so any pointers or advice will be much appreciated...

Create "main script" which detects current DB version and shells only scripts proper for it. For example, it can look like

drop procedure if exists main_proc;
delimiter @@;
create procedure main_proc
    select version into @version from service_table;
    if @version < 2 then
        source 001.sql;
        update service_table set version = 2;
    end if;
    if @version < 2.5 then
        source 02.sql;
        update service_table set version = 2.5;
    end if;
    if @version < 3 then
        source 3.sql;
        update service_table set version = 3;
    end if;
    -- and so on
    end;
@@;
delimiter ;
call main_proc;
drop procedure main_proc;

Don't forget error handlers (no script file or access error, error during script execution, etc.).


AWE memory usage growing with SQL Server 2012 I've got a problem with my SQL Server 2012 installation on my Windows 2008 R2 x64 box.

Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see any programs using it, I downloaded RAMMap to get a better picture of what's going on.  Turns out all of my memory was being chewed up by AWE.

My memory gets eaten up by SQL Server fairly quickly, up to the maximum that I allow the instance to have (currently 3GB).

When I try to research this, however, everyone seems to say that AWE isn't enabled for SQL Server 2012 64 bit systems, since it isn't needed; the expanded memory that AWE would enable is directly accessible.  But the system still will happily chew up memory in AWE until it reaches its limit.

Not sure what other information would be useful to help diagnose this...or even if I'm totally misinterpreting something...I'm at the limits of my current knowledge about this now. Please don't assume a lot of knowledge on my part...while I am tech savvy in general, I am absolutely not a DBA by any stretch.

Max memory for SQL server is set to 3GB.
Physical memory on the Server is 10GB.
I cannot give SQL Server any more memory - I have a virtual machine that takes up 4GB, Visual Studio, Outlook, IIS, etc.
Somewhat unusually, it is not really being used as a server, but as a development machine, so the DB workload tends to be quite light (though I say that without any hard metrics).

SQL Server is using the AWE memory allocation API to allocate buffer pool memory if "lock pages in memory" is enabled.

If SQL Server's max server memory setting is not set, SQL Server will eventually use as much memory as it needs. That will largely show up as AWE memory in RAMMap. A little more detail can be found here and all the SQL Server memory settings are explained here.

You should set the max server memory setting to allow an adequate amount for the operating system. If not enough memory is available for the OS, paging can occur and cause performance issues. A good starting point is 3 to 4 GB reserved for the OS, but watch the appropriate counters to see if the OS is under memory pressure.

You can get more information about how to select the right max server memory setting at the following links:

A Sysadmin’s Guide to Microsoft SQL Server Memory by Brent Ozar
Importance of setting Max Server Memory in SQL Server and How to Set it by Sakthivel Chidambaram

Full details of how SQL Server uses AWE and Locked Pages can be found in the Microsoft CSS SQL Server Engineers article:

Fun with Locked Pages, AWE, Task Manager, and the Working Set


Updating a column after expiration of specific time in MySQL I have a table where I have a column named state(int(2)) and modify_time (time_stamp on update current timestamp).
I have an update trigger which changes the value of state column to 0 based on some condition.
I want to set the value of state column to 1 after 24 hours of modify_time, if it is still 0.
I tried below method to test :

CREATE EVENT myevent1
 ON SCHEDULE AT current_timestamp + interval 1 minute
 DO UPDATE test.mytabletable SET state = 0;

This did not doing anything. Is there any alternative method?

SET GLOBAL event_scheduler = ON; -- enable event scheduler.
SELECT @@event_scheduler;  -- check whether event scheduler is ON/OFF
CREATE EVENT e_store_ts  -- create your event
    ON SCHEDULE
      EVERY 24 HOURS  -- run every 24 hours
    DO
      UPDATE myschema.youtable set mycolumn='1'


How do you create a new database with new sid on Oracle's pre-built 11g virtual machine? I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.

I logged in as "oracle" user and I can see the desktop.

From within the virtual machine, I opened Firefox and navigated to:

http://localhost:8080/ords/hrrest/employees/ and successfully received a list of employees in JSON format. So I know the database is up and running.

I noticed the local pre-built database is on port 1521 using SID:XE by default.

Also, in order to access it from an external oracle Java driver I had to use the credentials "hr"/"oracle", which is the default credential for this virtual machine.

My question: Is there a wizard I can use to create a brand new database? Or, if there are commands that do this, what documentation shows how to do this?

I want a new database, with a new username and password, with a new ORACLE_SID.

How do I do this? Is there a link to oracle documentation that shows me how to do this? This virtual machine uses Oracle 11g.

Ideally, I would like to connect to this new database with a Java program using new credentials like:

username:newuser
password:newpass
localhost
1521
ORACLE_SID=newsid

Are you sure that your Developer Days VM 2016-06-02_13 runs Oracle 11g?  I'm using the same, and it reports:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0  Production                                                                  0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Reading your questions/explanations, I cannot tell how familiar you are with 12c's "multitenant option", which "allows a single container database (CDB) to host multiple separate pluggable databases (PDB)" (see eg oracle-base.com).  For starters, a new PDB may be just what you need ... an overview of Oracle Multitenant can be found here.

In this article, section "Where to get started", you can see that the SERVICE NAME (not the SID) is used for connecting to a PDB.  More about PDBs (cloning etc), with screen shots, can be found here.  SQLDeveloper (already installed on the Developer Days VM) is probably the best tool for getting started.  I'm not aware of any "wizards" that one could use.

Using Java: When connecting to a PBD, using a JDBC driver, make sure that you use the service name (again: not the SID).

SID (for connections): it seems that the SID is only used for connecting to CDBs (not PDBs) nowadays.  Although - I have also used the "service name" successfully for accessing CDBs.