Find the highest graded student in each state, using Relational Algebra Suppose I have a table of students, containing their ID, grade and state:

-------------------------
| id  |  grade  | state |
------------------------
|  1  |    83   |   CA  |
|  2  |    94   |   TX  |
|  3  |    92   |   WA  |
|  4  |    78   |   CA  |

And I want the ID of the students in each state with the highest grade (ex. 1, 2 and 3), how would I go about doing that?

I know how to find the maximum (can do the cross product (renaming as R1 and R2) and then select R1.grade < R2.grade for those who aren't the top, and subtract that from the original database). But I'm confused at how to do that for each state.

I don't actually feel very comfortable with relational algebra, so, I'll do it first using standard SQL and then use a tool called RelaX - relational algebra calculator 0.18.2 to do the translation.

First, the table you wrote, I'll call it students, and define it and fill it with:

CREATE TABLE students
(
    id INTEGER PRIMARY KEY,
    grade INTEGER,
    state TEXT
) ;

INSERT INTO students
    (id, grade, state)
VALUES
    (1, 83, 'CA'),
    (2, 94, 'TX'),
    (3, 92, 'WA'),
    (4, 78, 'CA') ;

RelaX will translate this into a dataset, represented by the following tuples:

group: Joan (imported from SQL)

students = {
    id:number, grade:number, state:string
    1        , 83          , 'CA'
    2        , 94          , 'TX'
    3        , 92          , 'WA'
    4        , 78          , 'CA'
}

In order to find what you're looking for, we first need a table with tuples in the form (state, grade), having the maximum grade of each state. This query is done, in SQL with a MAX(grade) per state using a GROUPs BY state. You would write it like:

SELECT
    state, max(grade) AS grade
FROM
    students AS s2
GROUP BY
    state ;

Next, you need to JOIN this table (that is named max_grades) to the students one, and you do it ON equal states, and equal grade (i.e.: the max grade per state)...

SELECT
    s1.id
FROM
    students AS s1
    JOIN
    (
        SELECT
            state, max(grade) AS grade
        FROM
            students
        GROUP BY
            state
    ) AS max_grades
    ON s1.state = max_grades.state AND s1.grade = max_grades.grade

... and this gets translated by RelaX to the following relational algebra expression and reponse:

   π  s1.id  ρ  s1 students ⨝  s1.state = max_grades.state and s1.grade = max_grades.grade  ρ  max_grades ( π  state, grade  γ  state; MAX(grade)→grade  ρ  s2 students)

  s1.id
  1
  2
  3

NOTE1:

If several students of one state would have the max grade, this expression would return ALL of them, not just an arbitrary one of that state.

Alternative:

If you cannot GROUP BY, you can use another construct:

SELECT DISTINCT
    id
FROM
    students
EXCEPT
SELECT
    s1.id
FROM
    students AS s1
    JOIN students AS s2 ON s1.state = s2.state AND s1.grade < s2.grade

This goes a bit more in-line with your original thinking, although I personally find it less clear...

The translation to relational algebra is:

   π  id students -    π  s1.id  ρ  s1 students ⨝  s1.state = s2.state and s1.grade < s2.grade  ρ  s2 students


MySQL Replication not working properly on Ubuntu Lucid Lynx Server I have two Linux boxes. On the master following is the configuration file (my.cnf)

log-bin       = master-bin
log-bin-index = master-bin.index
binlog-do-db  = test_db
server-id     = 1

master status is as follows:

mysql> show master status;
+-------------------+----------+----------------+------------------+
| File              | Position | Binlog_Do_DB   | Binlog_Ignore_DB |
+-------------------+----------+----------------+------------------+
| master-bin.000026 |      106 | test_db        |                  |
+-------------------+----------+----------------+------------------+
1 row in set (0.00 sec)

The configuration file on slave is:

server-id = 2
relay-log-index = slave-relay-bin.index
relay-log   = slave-relay-bin
replicate-do-db=test_db

The slave status is:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 207.216.74.213
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000027
          Read_Master_Log_Pos: 276
               Relay_Log_File: slave-relay-bin.000141
                Relay_Log_Pos: 422
        Relay_Master_Log_File: master-bin.000027
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test_db
                   Last_Errno: 0
          Exec_Master_Log_Pos: 276
              Relay_Log_Space: 723
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
1 row in set (0.00 sec)

I've removed some empty entries from status above

Slave does connect and is showing correct master log file, but I am not getting any data from master to slave.

The only thing I can found is that master has version 5.1.41-3ubuntu12.10-log or MySQL but slave has 5.1.41-3ubuntu12.10.

Is the version or something other is creating problem?

:D it was binlog_do_db. I removed it and now it is working fine.


Does Postgresql create a new tuple for an no-change update statement? I need to fix some data issues in a Postgresql database, the primary of which is trimming whitespace from text columns. I'm doing this using a statement like:
UPDATE app.products
SET "description" = TRIM(BOTH FROM "description")

Issuing this on a test database returns UPDATE 2000. The test table contains 2000 rows of which only 1 actually would change with whitespace trimming. Is this resulting in Postgres creating new, unnecessary tuples? Would there be a benefit to adding a WHERE clause? Most of the target columns aren't indexed.
UPDATE app.products
SET "description" = TRIM(BOTH FROM "description")
WHERE "description" LIKE ' %' OR "description" LIKE '% '

Yes, this will modify all rows, even if the value doesn't change.
If you want to avoid that, either use a WHERE condition:
UPDATE app.products
SET "description" = TRIM(BOTH FROM "description")
WHERE "description" IS DISTINCT FROM TRIM(BOTH FROM "description");

or use the suppress_redundant_updates_trigger() trigger function to define a BEFORE UPDATE trigger on the table.
Avoiding the unnecessary updates will improve the performance and reduce table bloat.


Azure SQL Database - dedicated administrator connection (DAC) If you check the setting in any of your Azure SQL databases, you will see that the value_in_use column value is zero for Remote admin connections. Meaning ‘Remote admin connections’ are not allowed from remote clients. There is no way to change that at the time of writing this question. sp_configure is not available for Azure SQL Database.
SELECT * FROM sys.configurations
WHERE NAME = 'remote admin connections'
ORDER BY NAME;

Does that mean Remote admin connections are not allowed for Azure SQL Databases?

As per Microsoft documentation, Diagnostic Connection for Database Administrators is available for the Azure SQL database. If you are not familiar with DAC, read this official documentation from Microsoft.

By default, the connection is only allowed from a client running on
the server. Network connections are not permitted unless they are
configured by using the sp_configure stored procedure with the remote
admin connections option.

Ref: Diagnostic Connection for Database Administrators
There is a tip in the same document.

In my test, I can still connect to the Azure SQL database with DAC using SSMS or SQLCMD. I did make the Microsoft Azure SQL team aware and suggested changing the value from zero to one.
In SSMS, follow the same process that you use to connect to on-premises SQL Server. Do not use the ‘Connect Object Explorer’ connections. Open a ‘New Query’ window and then connect. Prefix server name with ‘ADMIN:’.

During my testing, I get an error message but the connection was successful (SSMS 18.8).

If you do not mention the database name, you will only connect to the master database. You cannot switch context to any user databases. You will get an error if you try to change the context from Master to another database. Every database in an Azure SQL server has to be connected separately depending on which one you want to work with.

To connect to any database other than master type your database name in the connection string.

For SQLCMD, you can use this command. You must use -d switch for the database name. If you are using Azure Active Directory for authentication use -G switch.
sqlcmd -S servername.database.windows.net -U userName -P StrongPassword -d master -G -A

For On-Premises SQL Server, you will use sys.endpoints system catalog view to see details about a DAC. This system catalog view does not exist in Azure SQL Database.

Caption: Output of sys.endpoints from SQL 2019 CU 9
For Azure SQL Database, use the below query to determine who is using DAC and all other details about the DAC session. Dimitri Furman from Microsoft provided this query.
SELECT s.*
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE wg.name = 'DACGroup';


Using SUBSTR and CONCAT to get the required Result I Am trying to get the results for the 'CENTRE' whereby if the 'DEP2' does not exist it has to use 'DEP'

Have done this by using SUBSTR and CONCAT in my query
But only get back 3 from 'DEP' when using it like this

My Query:

SELECT
DEP,
DEP2,
SUBSTR(DEP2 || DEP,0,4)CENTRE

FROM DEPARTMENT

I Get the results back fine but with 'DEP' only showing 3 numbers instead of the 4

----------------------------------
DEP    | DEP2      | CENTRE      |
----------------------------------
0132    | 7000     | 7000        |
0177    | 7000     | 7000        |
7051    | 7000     | 7000        |
7051    |          |  705        |
7051    |          |  705        |
----------------------------------

if the 'DEP2' does not exist it has to use 'DEP'

Typical NVL/COALESCE situation, not something that should be handled with string functions.

SELECT DEP, DEP2, COALESCE(DEP2, DEP) CENTRE
FROM DEPARTMENT


Use a value for a Postgresql setting in a trigger We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them.

I'd like to be able to use SET LOCAL for this, because it will also serve as a useful check that a transaction has been properly created. So the idea is to insert a row in a requests table and then use SET LOCAL pubsubpull.request_id to set the ID for the request which can then be used in the INSERT statement in the update log table generated in the trigger (if available).

I can see what the value is using SHOW pubsubpull.request_id, but how do I capture that value so it can be used in the INSERT table? SHOW doesn't seem to be legal in SELECT in any way. These user defined options don't seem to show up in the pg_settings view so I can't select it from there.

I have looked at using txid_current() but would prefer not to need to.

You want current_setting:

SELECT current_setting('pubsubpull.request_id');


How to deny drop, delete permission for a table? I want to give Create , alter , read permission on object to user but not insert , update ,delete,drop permission to user , How can i do it in sql server 2008?

To CREATE a table, stored procedure or function in SQL Server you need to GRANT the following

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO [someuser/somerole]

to a user or role. I added VIEW in since you probably wanted that too.

You also need to

GRANT ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schemaname TO [someuser/somerole]

to that user so that they can modify or use what they created.

With ALTER however that user would have the ability to DROP and ALTER anything in the schema which is not what you want based on your question.

ALTER PERMISSION SQL SERVER 2008

  ALTER Confers the ability to change the properties, except ownership,
  of a particular securable. When granted on a scope, ALTER also bestows
  the ability to alter, create, or drop any securable that is contained
  within that scope. For example, ALTER permission on a schema includes
  the ability to create, alter, and drop objects from the schema.

You could restrict that user to their own schema but that would probably create a lot of work for you since any application using your database would have to be aware of that new schema.

Outside of permissions, another way I know of to prevent dropping objects from a database is with a database trigger. Something like this: How to deny drop permission for a table?

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Dropping tables are not allowed'
ROLLBACK


Call a stored procedure from a trigger I have created a stored procedure in mysql using the following syntax.

DROP PROCEDURE IF EXISTS `sp-set_comment_count`;

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - AllCount
   DECLARE AC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END $$
DELIMITER ;

FYI I've greatly simplified the stored procedure but I do know it works without any issues.

What I'd like to be able to do is set up a trigger from usergroup_comments that works like this.

DROP TRIGGER IF EXISTS `usergroups_comments_insert`

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

But for some reason every time I do mysql throws an error at me that's less than helpful stating that there's a syntax error on line 4.

I've combed through the mysql documentation and found some information on restrictions of triggers but found it to be fairly convoluted.

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

Any ideas would be helpful.

There is great reason why you should never call stored procedures from within triggers.
Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.
Don't forget that Triggers require overhead. In fact, According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:

It is important to remember that, by necessity, triggers add overhead
to the DML statement to which they apply. the actual amount of overhead
will depend upon the nature of the trigger, but --- as all MySQL
triggers execute FOR EACH ROW --- the overhead can rapidly accumulate
for statements that process large numbers of rows. You should
therefore avoid placing any expensive SQL statements or procedural
code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The concluding point from that section states the following:

The lesson here is this: since the trigger code will execute once
for every row affected by a DML statement, the trigger can easily
become the most significant factor in DML performance. Code inside the
trigger body needs to be as lightweight as possible and -- in
particular -- any SQL statements in the trigger should be supported by
indexes whenever possible.

I explained other nasty aspects of Triggers in an earlier post.
SUMMARY
I would strongly recommend not calling any stored procedures from a Trigger, even if MySQL allows it. You should wlays check out the current restrictions for MySQL 5.5.


Does requiring SSL matter if using AWS RDS and AWS EC2 in a VPC I have a MySQL RDS DB and am connecting it through an EC2 instance.

My RDS isn't publicly accessible, so I think it can only be connected to through my EC2 instances.

I see at this link https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html that you can set up SSL with your RDS.

Does setting up SSL with your RDS matter if your RDS is protected?

This question -- and its answer -- are primarily regulatory/compliance matters.

If you need to comply with a standard that mandates encryption in transit between systems on the network, then you need SSL on RDS because the AWS network only isolates your VPC's traffic from the machines in all other VPCs -- the traffic is not actually encrypted on the wire as it traverses the cables, routers, and switches within the availability zone or region.

Traffic between systems in a VPC is still traveling unencrypted over the AWS infrastructure within the region, where it is theoretically possible for the traffic to be intercepted by a malicious actor who was able to compromise the AWS infrastructure. Such a compromise is considered highly unlikely, as there are protective controls in place such that it should almost necessarily require physical access and a conspiracy among multiple AWS insiders, but "highly unlikely" isn't the same as "impossible."


Can I kill these MSCRM related SQL processes? The SQL backups have been failing for a few days... I tried to just do a quick manual full backup and got an error:

sp_who2'active' (screenshot below):  Is it ok to kill SPID 186 and/or what's the general course of action here needing to be taken? I also feel perhaps a restart of the server wouldn't hurt. (Our VSS backups have been failing on this server too).

I am on SQL server 2014 SP1 / Windows server 2012 R2

The SQL backups have been failing for a few days... I tried to just do a quick manual full backup and got an error:

It seems like operation which conflict with backup are running at the time which backup was started so the backup failed. And as per your comment SPDI 186 is running which is showing backup which means that database backup is already running when new backup was being started. Concurrent full backup is not allowed when full backup is already running.

Also make sure no database shrink operation is running or Auto Shrink is not enabled. This can also interfere in backup operation.

select is_auto_shrink_on, name from sys.databases

let us see how many databases have auto shrink enabled

Yes you an kill backup operation (SPID 186 here) but you would loose all the time spent in taking backup and you would have to start all over again. Before doing that let us see how much backup has finished, what is output

select percent_complete,session_id,status,total_elapsed_time
from sys.dm_exec_requests where session_id=186

EDIT: There is NO restart required

Edit:

After discussing on chat it was identified that Veeam backup was running for past 2-3 days and was somehow hung. OP killed the process and it went into rollback state where it was again stuck OP has to ultimately restart the VM and the issue seems to have resolved.

Also advised OP to log a ticket with Veeam because that was definitely a third party Veeam backup issue.


Restore of a database failed - Table does not contain an identity column I've created a local SQL Server instance (using SQL Server 2017). I'm trying to restore a backup of a database created on a SQL Server 2012 server, for testing purposes.

The problem is that it gives me an error when I try to restore it:

  TITLE: Microsoft SQL Server Management Studio

  Restore of database 'XXXX' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  ------------------------------ ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: 'MyTable' does not contain an identity
  column. (Microsoft.SqlServer.SmoExtended)

Does anybody have any idea of what can be done to restore it? Does this may have something to do with server configurations? Thanks a lot.

@Max Vernon, I checked the log following your suggestion and I was surpried do see that, although message said "Restore failed", the log says something a bit different: Error: 3165, Severity: 16, State: 1. Database 'XXXX' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

So, I refreshed the Databases node just to find out that the DB was really there, offline. I've just brought it online, changed from "single user" to "multi user" and it's working fine :) About the actual error (3165..) I don't now why it happened, but te DB is just for tests and it's working, so I'll not worry about it for now. Thanks a lot for your help. All these "stack exchanges" communities are amazing.

Do the restore using T-SQL with the RESTORE DATABASE ... statement - that should provide a more meaningful error.

In brief, restore database works like this:

RESTORE DATABASE [database_name]
FROM DISK = N'\Path\to\the\backup\file.bak'
WITH RECOVERY
   , MOVE N'file name' TO N'new\path\for\this\file.mdf'
.
.
.

I wrote a blog post with code that automates creating the RESTORE DATABASE statement.  You simply point the code at an existing database backup file, and it will generate a valid restore command which you can then use to restore the database.

Using T-SQL to restore the database will provide a much more fine-grained set of error messages than using the SSMS GUI.

Also, I'd check the SQL Server Error Log to see if there are any pertinent details.  I also wrote a blog post with a quick script that can be used to show details from the Error Log.


Can I create a stored procedure in mysql using a txt file? My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server:

mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt

The content of the .txt file is:

call procedure1(date(now()));
call procedure2(date(now()));
call procedure3(current_Date());

Can I create or load a new stored procedure from a .txt file into my database the same way?

If you can execute SQL and you have sufficient privileges to create a stored procedure then you can create the stored procedure.

I'm assuming you can execute SQL with the .sh file above since you are currently using it to run jobs.  Since it looks like you are using this to perform maintenance on a scheduled basis I am going to further assume that you want to update the database/deploy changes using the same mechanism.  If this is true I would caution against this plan.  While it might seem nice to automate a deployment at midnight while you're sleeping if anything goes wrong, you won't enjoy the fallout when your boss/customer/angry person X wakes you up because the world is on fire....


What is background job queue in sql server? How to create a background job? While exploring the SQL server I came up with following views:

sys.dm_exec_background_job_queue;
sys.dm_exec_background_job_queue_stats;

Official site tells that

It Returns a row for each query processor job that is scheduled for asynchronous (background) execution.

So how can I create a background (Asynchronous) jobs what will populate these system views to see queued jobs in these tables or somewhere else?

PS: I am new to SQL Server I don't know if there is something like background job! Correct me if I am wrong.

You can't create one of these jobs. They aren't SQL Server Agent jobs or anything you can create manually, they're background tasks that SQL Server performs (like asynchronous updates to statistics).

You could trigger temporary population of the DMV by updating enough rows in a table and then running queries that force stats to be re-calculated, but this would be a very hunt-and-peck operation that will rely on incredibly lucky timing.


Dynamic Data Masking Issue when Concatenating Fields You can reproduce the issue here:
CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Select
    EmployeeId,
    LastName,
    FirstName,
    LastName + ', ' + FirstName
From dbo.EmployeeDataMasking

Notice the LastName and FirstName fields are partially masked (as expected). However, the combined name field contains the default mask. I don't know if this is considered a bug. However, I would think the combined field would retain the mask of the two fields it comprises. At least that's what I would prefer, since I don't know how to provide a mask for the combined field.

Documentation is shamefully silent in regard to what is the behavior when masked column is the part of an expression.
This is how masked column is represented in execution plan:
Scalar Operator(DataMask([LastName],0x05000000,(3),(2),'XXXX',(2)))

here (3) denotes masking function type and correspond to partial masking.
And this is how LastName + ', ' + FirstName expression is represented:
Scalar Operator(DataMask([LastName]+', '+[FirstName],0x05000000,(1),(0),(0),(0)))

As you may see, the behavior is "compute, then mask". Masking function type in this case is (1), which correspond to default masking. This is the result of masking function adjustment and intentional modification.
With the "compute, then mask" approach, this (mask adjustment) is the necessary measure apparently, because of otherwise simple expressions involving LEFT, RIGHT or SUBSTRING functions could unmask data easily. And determining whether expression is "safe" or not would be too complex probably.
I can only guess why approach is "compute, then mask" and not "mask, then compute", but I think that the latter, being implemented, would suffer from its own problems. Some, that I can think of, include possibility of arithmetic errors for numeric types (such as division by zero, for example, if something is divided to masked column), or logic skew possibility (if there is a conditional expression that depends on masked column).


MariaDB (MySQL) Replication Slave Lags Behind a Certain Amount I have 4 MariaDB replication slaves sync'ed with single master via SSL.  Slaves keep up-to-date, until somehow they permanently lag behind a certain amount of updates.  Has anyone seen this before?  Here's what I mean.  On the master:

SHOW MASTER STATUS \G

displays its current log position as 881134, then on each slave:

SHOW SLAVE STATUS \G

displays the master's Exec_Master_Log_Pos: 862183.  As you can see, the 2 numbers are quite different.  It never catches up on its own, no matter how long I wait.

If I issue a few more updates to the master, eventually the slaves will catch up to where I'm expecting them to, but they still remain several updates behind the master.

If I restart the slave MariaDBs, they catch up immediately and stay caught up for a few days until it happens again.

Things I have checked: No errors reported by the replication.  There is no CPU usage and no slow queries holding things up.  There is plenty of free memory and disk space on all MariaDB hosts.  No major network latency or outage.  Nothing in the MariaDB logs.  All slaves have separate server_id settings.  All are running INNODB. All system clocks are synchronized within a second of each other.

Please show us all of SHOW MASTER STATUS; and SHOW SLAVE STATUS;.

Sometimes this helps:

STOP SLAVE;
START SLAVE;


Does SQL Server 2017 CU1 break clusterless availability groups? Background:

My division is is doing an upgrade from SQL Server 2008R2 with mirroring to SQL Server 2017 with clusterless availability groups. Until recently the testing has surfaced no issues or red flags. Then we installed CU1, had issues, uninstalled CU1 and the issues went away. The OS is server 2016 with the latest patches.

Observed behavior after CU1:

Using either SSMS or tsql we could create a 2 replica clusterless synchronous availability group, and add one database to it. The group could be failed over multiple times without issue. Ah, but add a second database and issues would arise on the failovers. One of the databases would invariably wind up in a not synchronizing state. No amount of fiddling could resurrect it. If I dropped and recreated the whole thing, it might be the other database that went to not syncing. A pertinent error message in the logger was "Failed to update Replica status due to exception 35222." This seems to be a message related to clusters, but since we are clusterless I was confused. After we uninstalled CU1 on both replicas, I was able to create the AG and add 22 databases (including the two original). Failovers were without issue. On a side note, automatic seeding did not always work with multiple databases. The operation would fail with a "Seeding Check Message Timeout". Dropping those databases from the AG and adding them one at a time was successful.

My question is:

Has anyone else experienced issues with clusterless AGs after CU1? If so, were you successful where I was not?

Comment/opinion:

I thought CUs were going to be tested at the same level as SPs. While I know that bugs creep in no matter how thorough the testing, having this happen on the first one is troubling. It will cause us to really stress test each CU before deploying, which will mean we do not deploy them as they come out. We will deploy them only when we think there is a need to. We are a small organization without a dedicated dba, and need to be selective about what take on.

My division is is doing an upgrade from SQL Server 2008R2 with mirroring to SQL Server 2017 with clusterless availability groups.

So you're upgrading versions but REMOVING high availability and disaster recovery? Clusterless AGs are called "Read-Scale" AGs and do not give high availability and you can argue on the disaster recovery part...

  The group could be failed over multiple times without issue. Ah, but add a second database and issues would arise on the failovers. One of the databases would invariably wind up in a not synchronizing state. No amount of fiddling could resurrect it.

I've been seeing this when a configuration only replica (came in CU1) isn't used with Read-Scale AGs that are being used to fail over. Read-Scale wasn't made to fail over and all that jazz, it was made to horizontally scale out read copies for intense read situations (or as a way to replica across Windows/Linux for migrations). I must reiterate, "clusterless" AGs are not made for HADR. If this is part of your use case, use WSFC or Pacemaker (Linux). Info on Configuration Only Replica.

  A pertinent error message in the logger was "Failed to update Replica status due to exception 35222." This seems to be a message related to clusters, but since we are clusterless I was confused.

There should be an error directly before this, that's the actual error you want to look into. This doesn't have anything to do with clustering and is not a clustering error, it's a replica error.

  After we uninstalled CU1 on both replicas, I was able to create the AG and add 22 databases (including the two original). Failovers were without issue

This goes back to pre-configuration only replicas for adding into Read-Scale replicas. Again, surprised you didn't run into a few different issues as Read-Scale isn't made for HADR.

  On a side note, automatic seeding did not always work with multiple databases. The operation would fail with a "Seeding Check Message Timeout".

Seems unrelated, but you never know - could be a side effect of whatever was going on. Impossible to say at this point.

  I thought CUs were going to be tested at the same level as SPs.

You're correct. Changes in behavior between CUs (much like also were involved in SPs) can and do happen given the newer model (even in the SP + CU days this happened). I'd be interested to see if the configuration replica solves your issue since it was specifically added in CU1 for metadata safety as metadata issues for the replicas can and did happen since Read-Scale again wasn't made for HADR.


Not getting current IP address in my oracle database I have installed Oracle database in my local machine and my local machine IP address is - 192.168.1.55 but in Toad its showing different

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
----------------------------------------------------------
127.0.0.1

SQL>

For that I restarted Database but still it's showing the same.What changes need to do for reflecting current IP in the Database.

SYS_CONTEXT('USERENV','IP_ADDRESS') returns the ip-address of the client currently connected to the server.
In your case your toad is connected to Oracle server using loopback ip address(localhost).
I dont have toad demostrate it but I have tested it in my linux machine.

[oracle@testsrv Desktop]$ sqlplus sys/oracle@192.168.1.50/orcl as sysdba

SQL> select sys_context('USERENV','IP_ADDRESS') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.1.50

SQL> exit

[oracle@testsrv Desktop]$ sqlplus sys/oracle@localhost/orcl as sysdba

SQL> select sys_context('USERENV','IP_ADDRESS') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
127.0.0.1

SQL>


Oracle database link via ODBC to SQL Server does not recover automatically I have a test environment with an Oracle 10XE database which is linked to an SQL-Server 2005 via an ODBC data source. For reference here I named the connection MSSQL_LINK. The production environment will have an Oracle 11g and an SQL-Server 2008. The connection once set up works fine.

As a test of error conditions in production we unplugged the network cable. This broke the connection. A subsequent executed statement (e.g. select * from test_table@MSSQL_LINK;) failed with an error ORA-28500, which is a wrapper for the original ODBC error:

[Generic Connectivity Using ODBC][S1000] [9013]General error in nvITrans_BeginT - rc = -1. Please refer to the log file for details.
ORA-02063: preceding 2 lines from MSSQL_LINK

This was somewhat expected. Unfortunately plugging the cable back into the machine does not lead to an automatic reconnect of the link. The problem persists until I drop the link and create it anew with the commands

drop database link MSSQL_LINK;
create database link MSSQL_LINK connect to "user" identified by "passwd" using 'MSSQL_LNK';

Also the same happens if I just disconnect the cable for a short time and reconnect it before I execute any statements over the link. The interim breakup is still detected by the ODBC driver but not fixed on its own.

As mentioned by Ste, creating a link will tell Oracle only about the connection parameters like host, user name, password, but not create a connection by itself. The link can be created without the network cable connected to the Oracle machine. If I create the link without the cable connected, then connect it to and execute a simple select on the linked server, the same Oracle error occurs.

Obviously in production mode this is an unacceptable situation which needs to be fixed, preferably by an automatic procedure.

Three questions do I have about this:

Is there a way to test a database link other than e.g. a select on a test table which resides on the linked server?
Is dropping an re-creating the database link on my oracle machine the best option I have when this problem occurs?
What is the best automatic procedure for recovering from this situation.

Step by step:

There is no way to test a dblink other than select a dummy over it
A network timeout affects only the running transaction that is currently the dblink. There is no recorded state about the dblink, there is no status like ACTIVE or BROKEN. DBLink creation simply instruct the DB about remote host, port, tns, user and password. Hence drop and recreate the dblink does not affect or change the network or ODBC problem. A pratical case is: select sysdate from dual@dblink. A firewall is in place between both databases. Source DB opens the connection, retrieve data and leave connection opened. After 15mins the firewall drop the connection. At minute 20 another user performs the same query. The database will reopen the connection.
The best automatic way should be to query a table; this only if we are experiecing a simple network issue.

Apart from this I understand that you get the connection dropped and you have to way to re-establish it without drop and recreate the database. Metalink (formerly My Oracle Support) suggest to check if HS=OK is correctly set in the tnsnames.ora. Attention: the HS=OK argument needs to be outside of the CONNECT_DATA portion of the entry. Here an example:

MSSQL =
(DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS =
              (PROTOCOL = tcp)
              (HOST = 192.168.36.18)
              (PORT = 1522)
             )
        )
   (CONNECT_DATA =
        (SID = MSSQL)
   )
   (HS=OK)
)

Moreover I suggest you to check (and eventually post) logs on both sides. You can also increase the level of HS_FDS_TRACE_LEVEL parameter.
In addition MOS reports some issue with DG4ODBC and suggest to debug them (and fill a bug if necessary) using trace on the ODBC layer.


multiple archive_command in PostgreSQL I have replication with PostgreSQL, so I want to set archive_command = 'cp %p /var/lib/postgresql/11/main/archive/%f' but I also want to save WAL files to another safe place: S3.

So is it possible to have multiple archive_commands? And can I put this command on the slave to push wal files to S3 instead of on the master - I want master to have less performance impact.

You don't need multiple archive_commands:

archive_command = 'cp %p /.../%f && (command to archive to S3)'

The && is interpreted by the shell. If the cp has a non-zero return code, that will be the result of archive_command. If the result is 0 (success), the second command is executed, and its return code will be the result of archive_command.

You can also defer archiving to S3 to the standby server, but then you have to set archive_mode = always there. The disadvantage is that the configuration on primary and standby will be different, which makes failover more complicated. Does the command to archive a file to S3 really cause a substantial performance impact?


deduct 60 minutes/ 1 hour from punch time (Start time) of attendance Suppose there are 300 employees different kinds of shifting duty they are doing but on 15th October Fingertec machine's clock was stop, so now I want to deduct 60 minutes or 1 hour from their in time. How can I update this in SQL query? Please help me.

Something like this? If you share your table structure the answer could be more precise.

-- TEST TABLE
CREATE TABLE dbo.employees(id int identity(1,1),
                           StartTime datetime2);

-- TEST VALUE
INSERT INTO dbo.employees (StartTime)
VALUES(getdate());

--CHECK
SELECT * FROM dbo.employees;

-- ACTUAL UPDATE
UPDATE dbo.employees
SET StartTime = DATEADD(hour,-1,StartTime);

-- CHECK AFTER UPDATE
SELECT * FROM dbo.employees;


Reusing literal string in different parts of query I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings:

with cte as ( select 'The character between the xes is evil: xx' as my_bad_string )
select substring( s.my_bad_string, n.i, 1) char_i
  , ascii(substring( s.my_bad_string, n.i, 1)) ascii_char_i
from
  cte as s
  cross join (
    select generate_series(1, length( 'The character between the xes is evil: xx' ))
  ) n(i)

It works. It helped me to find the evil character 141.

Question: what is a more elegant way to write this query so that my hard-coded string appears exactly once instead of twice?

Use unnest() after converting string to array with string_to_array():

SELECT char_i, ascii(char_i) AS ascii_char_i
FROM   unnest(string_to_array('The character between the xes is evil: xx', NULL)) char_i;

NULL as separator splits after every character in string_to_array().

There is also regexp_split_to_table(), it's more versatile, but also more expensive.

To guarantee original order of characters throw WITH ORDINALITY into the mix:

SELECT pos, char_i, ascii(char_i) AS ascii_char_i
FROM   unnest(string_to_array('The character between the xes is evil: xx', NULL)) WITH ORDINALITY t(char_i, pos)
ORDER  BY pos;

How to preserve the original order of elements in an unnested array?
How get all positions in a field in PostgreSQL?

But while the query is that simple the order is preserved anyway - by the current implementation.

About tricky whitespace and non-printing (UNICODE) characters:

Trim trailing spaces with PostgreSQL
ERROR: "sql " is not a known variable


Lost admin user ; Update not allowed on current user in MySQL I work on MySQL. For making my database more secure I created new user and than deleted 'root' user which had all privileges. I think I did not give full privileges to new user. That's why it is giving error whenever I try to update a table in database.
UPDATE command denied to user 'crm_user'@'localhost' for table

In case I lost the superuser (in my case it was root) how can I retrieve or get all privileges to get control on my DB. DB is installed on my VPS so I have full control whatever need to do but I have no idea what to do. Please help. Thanks

I think the answer of the question can help hackers or something.
You can reinstall your MySQL. Before the reinstall, take a backup if you can or copy your data files.
There is an answer to how can do you recover your data files. If you want to guarantee your data healthy, you can install new MySQL and try that.


Help configuring MySQL install for remote connections / root accounts understanding I installed mysql/phpmyadmin/apache on a linux box for internal network use only. I don't have the strongest understanding of MySQL.

Hostname: server.domain.lan
IP: 192.168.0.54

After I installed MySQL I ran mysqladmin -u root password STRONGPASSWORD
I edited /etc/my.conf and set the bind-address to 192.168.0.54

I am trying to set up a system to run on my LAN that I can upload websites to that need to create databases.

Screenshot from phpMyAdmin of the Privileges menu:

So my questions are:

Why are there 3 root accounts?
What do I need to do to configure MySQL to allow remote connections from the root account so I can create databases, etc?

(Sorry I'm kind of new to MySQL so I'm not certain that I'm even asking the correct questions...)

root@localhost allows a root user to connect to mysql locally from the DB server using the mysql socket file.

root@127.0.0.1 allows a root user to connect to mysql locally from the DB server using the TCP/IP protocol. (Trust me, you will be needing this one. MySQL has a nasty bug in it. The bug sometimes causes the mysql socket file to disappear rendering root@localhost useless. You will need to connect using root@127.0.0.1 if this ever happens to you)

root@server.domain.lan allows a root user to connect to mysql from server.domain.lan using DNS (Dynamic Naming Services)

You could create root@'%' but I highly recommend you do not.

I would also recommend using a different password for each root user.


Exporting table data into csv format from server to local I have connected to remote server and trying to export a table data to local using the command:

copy finance.coa to 'localusername@172.16.1.44:/home/kedarisettyakhiles/akki.csv' DELIMITER ',' CSV HEADER;

I am getting the error:

  relative path not allowed for COPY to file

There are two problems:

COPY writes to a file on the database server.

If you want to write to a file on the client machine, use psql's \copy which runs COPY ... TO STDOUT under the hood.
You cannot specify a host and user name in the file name in the hope that the file will be transferred to another machine. It has to be a path on the file system (for COPY, it has to be an absolute path on the server's file system, hence the error message).

Start psql on the machine where you want the file to end up and use \copy:

\copy finance.coa to '/home/kedarisettyakhiles/akki.csv' (FORMAT 'csv', DELIMITER ',', HEADER)


Recommended model for application level event logging I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption.

Below is an oversimplified model of the tables and their associations.

users
-------
id (PK)

event_type
-------
id (PK)
description

events
-------
id (PK)
user_id (FK)
event_type_id (FK)
created (timestamp)

The events will be reported in a timeline format (newest events at the top) for each user.

I'd also like to add custom uri parameters and the events context (User A updated "My Visa Card").

Am I overlooking any potential pitfalls in this design?

Your basic model is fine.  It gives you a standard, normalized intersection (many to many) of events of specific types associated with particular users.  Without knowing more about your intentions or requirements, it's difficult to offer any criticism of the model.

I would make these observations:

You can use events.id for your PK on the events table if you like.  That is probably the way that I would do it.  You have a choice, however, to consider making the PK of event the combination of events.user_id and event_type_id.  This will be just as good, possibly slightly better, if you will have no other tables directly referencing the events table.  The potential advantage has to be considered in light of how you plan to manage the space for this table and how fast events will accumulate.  Using events.id as a clustering index could create a hotspot.  On the other hand, using the compound key could create long delays in your inserts if you don't manage your page fills properly.
You've mentioned that the model is simplified, but you haven't said in what ways.  One thing that I would say is that the model as presented gives very little information about events.  You know who did what (and when) - but the what is limited to a static description string.  Other event logging systems I have seen (and built) include places to record specific details of the object of the event.  If I were you I would ask myself questions like:

Would it help if there were a text field on the events table that could be filled with specific details of what happened during the event?
Would it help to have columns that record which instance of another item were impacted by the action (i.e.: events.object_type and events.object_id)?
Are there complex events that have multiple parts which should be grouped into a single logical event?  Would having a field that allows multiple events records to be tied together be helpful?

If you can tell us more about how you plan to use the events, from the perspective of how the information is meant to be helpful, I might be able to give you more concrete advice.


Please tell me my data isn't gone... lie to me even In a moment of ignorance I setup my webserver to auto backup all data to another machine.

not a problem except I just mearly cp -R /var/lib/mysql so all I ended up getting was hundreds and hundreds of backups of DB.opt and table.frm files

From my understanding I didn't pull any data in these files. Is this true?

If so are these any good to me at all?

I'm not sure I understand your question, or even what you did, but let's walk this through.

If all you did was to issue a cp -R /var/lib/mysql /path/to/backup, then this does not harm your existing data.

It is also an incorrect backup method. Your backup itself is probably useless (unless all tables are MyISAM and you issued a FLUSH TABLES WITH READ LOCK).

What's further unclear is your description of .opt and .frm files. Is that all you got? That's strange, because you should have also got some *.MYD files, and some *.ibd files or an ibdata1 file.

Which storage engines are you using for your tables? What is the output of

mysql> select @@datadir;
mysql> show global variables like 'innodb_data_file_path' ;
mysql> show global variables like 'innodb_data_home_dir' ;

?

Last, I don't understand from your question whether data has been deleted from your webserver (containing your MySQL data).


Why query execute faster after first execution I have mysql (5.7) innodb table, and run a select query and using an Id foreign key. for the first query on this Id foreign key condition, the query took a lot of time to execute, and If I run another condition on select with the same id foreign key, it run so fast.

Why is that so? and how could I optimize my queries select to run fastely in the first execution?

No, you cannot optimize a query to run as fast on the first run as on the second.  This is because during the first run the database caches information in the system.  The second time it is run, if the information is still in the cache, then the database will reuse that info to make the second query faster.


Chaning tempdb settings on production database I have to change the initial size and autogrowth of my tempdb on production. Along, with that I have to add new files to tempdb. What is the right way to do it? As it is production and I don't want to take any risk.
Can I directly go to temdb properties and can change the settings/T-SQL or do I have to consider other things also?

Sure, you can do it with little risk, but it depends on the state of your current tempdb.  If tempdb has grown quite large, you might need to shrink it first before you have enough space for perhaps 4 good size tempdb files.

Because of how active tempdb is and how hard it is to shrink that you might need to schedule a little downtime to make the clean change with all files of exactly the same size.

See a post outlining a few issues at: http://www.brentozar.com/blitz/tempdb-data-files/

Notice that there are a couple of subtle decisions to make.  In the sample scripts it describes four 8 GB files, but the sizing naturally needs to support your space requirements. Consider whether tempdb auto-growth should be allowed; Brent recommends correct sizing with no auto-growth.


Why is Primary Key not present at the end of Unique Key in MySQL? I have a table with following definition.

CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `g` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `h` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b_UNIQUE` (`b`),
  KEY `single` (`c`),
  KEY `double` (`d`,`e`),
  KEY `triple` (`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

For some queries when I check optimizer trace I notice key parts for indices.

For example:

{
  "index": "single",
  "usable": true,
  "key_parts": [
    "c",
    "a"
  ]
},
{
  "index": "double",
  "usable": true,
  "key_parts": [
    "d",
    "e",
    "a"
  ]
}

Notice for indices single and double the key part contains the Primary Key a at the end. That is what InnoDB says. So thats good.

{
  "index": "b_UNIQUE",
  "usable": true,
  "key_parts": [
    "b"
  ]
}

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part. Is it really not present and pointer to record is present? What implications does this have with respect to query optimization and Page reorganisation?

EDIT 1: It seems to be present in MySQL 8.0 version. It is highly likely to be present in MySQL version 5.7 as well. So require a way to check its presence in this version.

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part.

It is a snippet problem.

show index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null

show extended index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            2 | DB_TRX_ID   | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            3 | DB_ROLL_PTR | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            4 | b           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            5 | c           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            6 | d           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            7 | e           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            8 | f           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            9 | g           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |           10 | h           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | single   |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            3 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            4 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null


Oracle full expdp/impdp but skip data in some tables I'm trying to export a schema and then import it, but there are some huge tables that I must skip all the data from (that is, I need the tables and all stuff related to them, just not the rows).

So I thought I'd do

1) expdp SCHEMAS=X CONTENT=METADATA_ONLY DUMPFILE=meta.dmp
2) exportSCHEMAS=X CONTENT=DATA_ONLY EXCLUDE=TABLE:"IN \('FOO','BAR'\)" DUMPFILE=data.dmp

and then

4) impdp SCHEMAS=X EXCLUDE=REF_CONSTRAINT DUMPFILE=meta.dmp
5) impdp SCHEMAS=X DUMPFILE=data.dmp

The plan is in step 4 to get all the meta data, and then in step 5 import all data but the rows in the large tables FOO and BAR,
But how can I get the foreign keys?

Is this a way to do it, or are there other ways?

No, it is not a good approach, there are a lot of problems, e.g.

Doing a data import after the indexes are created will slow down the import considerable.
the import will fire all insert triggers. That may slow down the import, too, and it may have unwanted side effects
The current values of the sequences do not match to data.

You should use the QUERY parameter. So if you want to exclude the LOG and MESSAGE table of the APP schema add

QUERY=APP.LOG:"where 0=1"
QUERY=APP.MESSAGE:"where 0=1"

to the parameter file of your export.

Another way to accomplish this task is to split this into two export. This is a little bit more complex but avoids reading table data.

export the schema and exclude the large tables. his task is independent from the size of the two large tables
export the structure of the two large tables. The fime for this task is also independent of the size of the two tables

first export (schema export with data)

DUMPFILE=schema.dmp
SCHEMAS=APP
EXCLUDE=TABLE:"in ('LOG', 'MESSAGES')
CONTENT=ALL  # this is the default, so it must not be specified

second export (table exports without data)

DUMPFILE=table.dmp
TABLES=APP.LOG,APP.MESSAGE
CONTENT=METADATA_ONLY

At first you import the schema dump, then you import the table dump. For both imports you can use the FULL=Yparameter.

schema import:

DUMPFILE=schema.dmp
FULL=Y

table import:

DUMPFILE=table.dmp
FULL=Y


Help creating this query I'd like to integrate the following query:

SELECT
    parent.id
  , COUNT(child.id) AS child_count
FROM
             messages parent
  INNER JOIN messages child
    ON child.parent_id = parent.id
WHERE parent.parent_id = 0
GROUP BY parent.id;

into this query:

SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain )
AS address
FROM messages m
LEFT JOIN message_address_fields maf ON maf.message_id = m.id
LEFT JOIN addresses a ON a.id = maf.address_id
WHERE maf.field_id =4 AND m.user_id =1

Here's a sql fiddle: http://sqlfiddle.com/#!2/bef27/1

Here's my schema:

 CREATE TABLE IF NOT EXISTS `addresses` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `user_id` int(10) NOT NULL,
   `name` text NOT NULL,
   `localpart` text NOT NULL,
   `domain` text NOT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `fields` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `name` text,
  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `account_folder_id` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `texthtml` text NOT NULL,
  `textplain` text NOT NULL,
  `uid` int(10) NOT NULL,
  `seen` tinyint(1) NOT NULL,
  `flagged` tinyint(1) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `message_address_fields` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `field_id` int(10) NOT NULL,
  `address_id` int(10) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Thanks for the help!

QUESTION: This query limits the data to only messages that contain an address with field id of 4. How would I make it optional to have a correlated address with a field id of 4 or not

SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain )
AS address,COUNT(child.id) AS child_count
FROM messages m
INNER JOIN messages child ON child.parent_id = m.id
LEFT JOIN message_address_fields maf ON maf.message_id = m.id
LEFT JOIN addresses a ON a.id = maf.address_id
WHERE maf.field_id =4 AND m.user_id =1 LIMIT 0 , 30


MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations I had a table with ~0.5M rows and tried to execute a simple query, something like:
UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999

but the result was

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

I have MaxNoOfConcurrentOperations=100000 and slightly more than 100000 rows which satisfy the where condition, so I have changed to this query:
UPDATE table
SET col1=-90 + RAND()*90, col2=-180+RAND()*360
WHERE col1=9999 ORDER BY ID LIMIT 100000

...and got the same error. And same error with LIMIT 1000.
I have updated rows using a query with LIMIT 100 in loop, but I think that it is a bad solution.
Questions

Why does MySQL Cluster behave this way?
How can I update many rows in a single table?

To solve the error message ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER you can use one of the possible solutions:

1) Increase MaxNoOfConcurrentTransactions even more until it works (note it take more resources (RAM))

2) Select the data from innodb into an outfile and load infile into
cluster (will be faster than 2).

Note: you faced this error message  since there is not enough resource on Dedicated for the cluster evnironment.


Triggers not being run on a partitioned table I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and the master table remains empty.

My problem is that one of the triggers on the master table is no longer run on insert or update. Other triggers are run correctly and this trigger worked correctly prior to partitioning.

I suspected this might be similar to constraints and indexes and the trigger needed to be applied to child tables, however this doesn't work either.

Other triggers work, and if they are run alphabetically as the documentation suggests, all should be run before the partition-finding trigger.

The trigger that is causing me grief:

CREATE FUNCTION set_expiry_timestamp() RETURNS trigger AS $BODY$
BEGIN
    -- Don't re-generate if the timeleft value has not changed
    IF (TG_OP = 'UPDATE') THEN
        IF OLD.expires IS NOT NULL AND OLD.timeleft = NEW.timeleft THEN
            RETURN NEW;
        END IF;
    END IF;

    IF (NEW.timeleft = 'SHORT') THEN
        NEW.expires := NEW.updated + interval '30 minutes';
    ELSIF (NEW.timeleft = 'MEDIUM') THEN
        NEW.expires := NEW.updated + interval '2 hours';
    ELSIF (NEW.timeleft = 'LONG') THEN
        NEW.expires := NEW.updated + interval '12 hours';
    ELSIF (NEW.timeleft = 'VERY_LONG') THEN
        NEW.expires := NEW.updated + interval '48 hours';
    ELSE
        RAISE EXCEPTION 'timeleft was unrecognised. Expected SHORT, MEDIUM, LONG or VERY_LONG.';
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER set_expiry AFTER INSERT OR UPDATE ON auction FOR EACH ROW EXECUTE PROCEDURE set_expiry_timestamp()

Query expected to run the trigger:

INSERT INTO auction (created, timeleft) VALUES (NOW(), 'SHORT');

updated is set in a BEFORE INSERT/UPDATE trigger, however it still doesn't fire when updated is supplied.

Any suggestions?

The trigger is AFTER INSERT OR UPDATE. As you can see in your function, it ends with:

RETURN NULL;

That means that the row is discarded and any changes to the row are void. Actually, the row returned from an AFTER trigger is discarded in any case. The RETURN NULL is just a reminder.

You need to do this in a BEFORE trigger or issue an explicit UPDATE statement. The second option is very tricky, however, because you could end up with an endless loop. Better make it a BEFORE trigger.

As an aside: the conditional expression could be simplified with a CASE statement (since version 8.4):

CASE NEW.timeleft
WHEN 'SHORT' THEN
    NEW.expires := NEW.updated + interval '30 minutes';
WHEN 'MEDIUM' THEN
    NEW.expires := NEW.updated + interval '2 hours';
...
ELSE
    RAISE EXCEPTION 'timeleft was unrecognised. ...';
END CASE;


SQL Maintenance Tasks on Azure [Ola Hallengren Scripts] I am trying to implement the SQL maintenance scripts from Ola on my Azure elastic pool databases. I did the steps as provided in this link.

I could create the job, but I always get this error after running the job manually:

Does anybody have some experience with these maintenance tasks on Azure?

As mentioned on that article, you are expected to create Hallengren schema and its related database objects on each database on the pool, instead of using the Master database to create the schema and objects. Steps 1 to 4 on that article tell you how to perform that setup using jobs.

After that the article shows you how to use Hallengren scripts.


Developer's query performing very poorly in custom database We have an in-house-developed web application with a very small database (~20MB) and small number of users (~20 max) it is unfortunately a very high-visibility application used by executives. They have found that late in the afternoon every week, when multiple users are making changes simultaneously, performance is EXTREMELY poor - sometimes taking 5 minutes to "save an update" which sometimes fails completely.

The table design is not ideal and I've recommended the developer make some changes which would take some time to implement. The main table has a number of varchar(max) columns in which they store delimited lists of users. All of the tables in the database have a single clustered index on an ID column - another thing I've recommended improving to the developer.

Some "projects" in this application are significantly slower than others, there is one in particular that is almost always slow to save, but outside the heavy-usage period, we can typically see queries perform well.

I am not a developer, but this query looks poor/inefficient to me as a DBA - this is typical of the application's "save project" queries. It looks auto-generated by some tool - the query is generated by the application and it passes parameters for each column in the table, it does not use stored procedures at all.

UPDATE Actions SET Name = @Name,
Start_Date = @Start_Date,
End_Date = @End_Date,
Status = @Status,
Comments = @Comments,
Comp_Date = @Comp_Date,
Owner = @Owner,
Owner_EmplID = @Owner_EmplId,
Status_Name = @Status_Name
WHERE ID = @ID;

The server is SQL 2012 Standard and it has 4 CPUs, 18GB RAM (pretty typical for our environment) - it does run other application DBs but the overall server load is "normal" during peak usage times for this application, and no jobs/loads are running in other DBs at peak usage time. I have not found any deadlocks or locking at all while we monitor peak usage performance.

We do have a Tableau report connected to this same database that's auto-generating some other poor reporting queries, the entire database has about 25,000 rows among all tables, and the query Tableau runs generates millions of rows; I suspect cross-joins in it somewhere but none of my peers want to focus on that aspect of this issue.

Do these update queries actually follow good development practice? Can you give any advice that would help me convince the developer to redesign some of this, or is there something I can do to improve performance without involving them?

Definition of the Actions table:

TABLE [dbo].[Actions](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Project_ID] [int] NULL,
    [Name] [varchar](1000) NULL,
    [Owner] [varchar](500) NULL,
    [Owner_Emplid] [varchar](500) NULL,
    [Comments] [varchar](max) NULL,
    [Start_Date] [date] NULL,
    [End_Date] [date] NULL,
    [Comp_Date] [date] NULL,
    [Status] [int] NOT NULL,
    [Status_Name] [varchar](50) NULL

varchar(max) columns with delimited lists is an anti-pattern, particularly if you split those lists out.  Properly designed relational tables provide much better performance.  However, that is likely secondary to your performance problem.
I'd bet money on the problem being blocking.  From Microsoft:

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.

Blocking is probably occurring when the Tableau queries take a long time to complete, blocking the updates from occurring, and creating the impression of slowness.
Brent Ozar has an excellent set of tools that can help understand performance problems.  Specifically around blocking, this page contains a load of great resources.
By the way, it looks to me like that update statement in your question looks perfectly well-formed to me.  I see nothing wrong with it as shown.  If you provide query plans for the queries and update statements that are problematic, I'll provide more definitive advice.
If you do find that blocking is a significant problem, you may want to look into SQL Server's multi-version-concurrency-control option that can be enabled at the database level, READ COMMITTED SNAPSHOT.  From that document:

When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks. That is, the SQL Server Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions. A user-defined function can return data that was committed after the time the statement containing the UDF began.

Be aware, read committed snapshot isolation (RCSI) is not the silver bullet it can appear to be at first glance.  Like most things, it has positive and negative effects.  By design, it changes the concurrency profile of the database, such that you can no longer rely on locking to enforce select-then-update semantics, since the select will no longer block other transactions from accessing the row that will soon be updated.  Kendra Little has a [great article(https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/) talking about RCSI.


Safe to start Mirroring on Production Server? We have a 350 Gig DB on SQL Server 2008. We intend to backup and restore it then immediately start mirroring.

Is starting a mirror a dangerous operation to do on a production database ? Can I expect time-outs and do I need to do this in a maintenance window or is it a relatively safe operation to perform at any time ?

Thanks !

Assuming you have everything properly configured, starting mirroring simply means the log rows for transactions not already at the mirror will be sent to the mirror database; once that has occurred the only further overhead would be writing to the mirror database.  If the mirror is not very far behind the principal, then this step is generally very quick.

If you have mirroring configured in Synchronous (high safety) mode, that overhead might be enough to show delays in commits. Generally, transactions would be slightly slower since data must be written to both the primary log, and the mirror log before control is returned to the user.

If you have a maintenance window, it would be preferable to start mirroring during that window.


SELECT with WHERE and CASE I do not really know if this is possible to do this but I will expose my problem.

I have two tables cases and progress

cases

+----------+--------------+---------------------+---------+------+
| id_cases |     name     |    date_surgery     | archive | done |
+----------+--------------+---------------------+---------+------+
|        1 | Cranioplasty | 2016-02-01 00:00:00 |       1 |    0 |
|        2 | Cranioplasty | 2016-02-02 00:00:00 |       0 |    0 |
|        3 | Other        | 2016-02-03 00:00:00 |       0 |    0 |
|        4 | Osteotomy    | 2016-02-04 00:00:00 |       0 |    0 |
|        5 | Bone Tumor   | 2016-02-05 00:00:00 |       1 |    1 |
+----------+--------------+---------------------+---------+------+

progress (which contains thousands of records)

+-------------+---------+---------+---------+
| id_progress | task_id | case_id | current |
+-------------+---------+---------+---------+
|           1 |  103006 |       1 |       0 |
|           2 |  103002 |       1 |       1 |
|           3 |  103003 |       1 |       1 |
|           4 |  201006 |       5 |       0 |
|           5 |  201007 |       5 |       1 |
|        .... |     ... |     ... |      ...|
+-------------+---------+---------+---------+

The link between the tables is cases.id_cases = progress.case_id

I want to select all cases with archive and done = 0. I also want to get some progress that are linked to this case

I tought about a condition to select a specific range of task_id related to the result of cases.name.

So basically I want this

SELECT id_cases, name, date_surgery, task_id, current
FROM cases
LEFT JOIN progress on progress.case_id = cases.id_cases
WHERE archive = 0 AND done = 0

But if name is Cranioplasty I just want progress.task_id that are equal to 103006, 103002. For Bone Tumor I want 201006 and 205003. And this for all the different name.

There is no logic between the id_cases and the task_id. I must hardcode this.

I tried differents things but none suceeded

SELECT id_Cases, name, date_surgery, task_id, current
from cases
left join progress on progress.case_id = cases.id_cases
where archive = 0 and done = 0
and case when name='Cranioplasty' then task_id=103006 and task_id=103002 else
case when name='Bone Tumor' then task_id=201006 else
case when name='Osteotomy' then task_id=301002 else
case when name='MBIO' then task_id=401006 end end end end
order by name, date_surgery

In fine I try to get this result (task_id is not important, I just want to result of current)

+------+--------------+-----------+--------+-------+--------+-------+--------+-------+
| case |     name     | date_surg | task_1 | 1_res | task_2 | 2_res | task_3 | 3_res |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+
| 1    | Cranioplasty | date      | 103006 |     0 | 103002 |     0 | 105002 |     1 |
| 1    | Cranioplasty | date      | 103006 |     1 | 103002 |     1 | 105002 |     0 |
| 1    | Cranioplasty | date      | 103006 |     1 | 103002 |     0 | 105002 |     1 |
| 2    | Cranioplasty | date      | 103006 |     0 | 103002 |     1 | 105002 |     0 |
| 2    | Cranioplasty | date      | 103006 |     1 | 103002 |     0 | 105002 |     1 |
| 2    | Cranioplasty | date      | 103006 |     0 | 103002 |     1 | 105002 |     1 |
| 3    | Bone Tumor   | date      | 201006 |     1 | 205003 |     0 | 205005 |     0 |
| 3    | Bone Tumor   | date      | 201006 |     0 | 205003 |     1 | 205005 |     1 |
| ...  |              |           |        |       |        |       |        |       |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+

You have several problems with the query you listed:

In the first test, you use and AND instead of an OR or an IN (I perfer IN -- in fact, I would prefer it in all cases so that it is easy to extend later, but that is your choice)
You are nesting your case statements. You only want a single case statement, with multiple conditions. What you have is basically a nested if statement.
You never terminated the last case statement with an ELSE clause (I use ELSE 0 below, but in some cases, ELSE 1=0 OR ELSE FALSE might be needed to render a boolean value -- ELSE NULL should work too).
You have a LEFT JOIN that is converted into a JOIN because of the WHERE clause... I think that is what you actually want, but you should double check

The query should look more like this:

SELECT id_Cases, name, date_surgery, task_id, current
FROM cases
JOIN progress ON progress.case_id = cases.id_cases
WHERE archive = 0
  AND done = 0
  AND CASE name
    WHEN 'Cranioplasty' THEN task_id IN (103006, 103002)
    WHEN 'Bone Tumor' THEN task_id = 201006
    WHEN 'Osteotomy' THEN task_id = 301002
    WHEN 'MBIO' THEN task_id = 401006
    ELSE 0
  END
ORDER BY name, date_surgery;

I think this is what you want. If you really want the LEFT JOIN, it would look like this:

SELECT id_Cases, name, date_surgery, task_id, current
FROM cases
LEFT JOIN progress ON progress.case_id = cases.id_cases
    AND CASE name
      WHEN 'Cranioplasty' THEN task_id IN (103006, 103002)
      WHEN 'Bone Tumor' THEN task_id = 201006
      WHEN 'Osteotomy' THEN task_id = 301002
      WHEN 'MBIO' THEN task_id = 401006
      ELSE 0
    END
WHERE archive = 0
  AND done = 0
ORDER BY name, date_surgery;


How to migrate subset of data from production to develop by scrambling sensitive data? I like to create a tool which scrambles the production sensitive data and added it into another dev DB. I've built a couple of scripts that make random social security numbers, shift birth dates, scramble emails, etc. But I faced a problem while we have a reference for that column.

For example:- if we scramble tables tableA and TableB,
TableB have a forieng key from tableA(ssn)

 TableA
    ssn firstName                  lastName
    .......................................
    111   arun                        prasanth
    222   ann                         antony
    333   sruthy                      abc
    666   new                         abc

TableB(ssn Foreign key reference)
id  ssn   Place
................
1   111   kerala
2   222   usa
3   333   ekm
5   666   chennai

Desired output should be

 TableA
    ssn firstName                  lastName
    .......................................
    999   gdfsgs                    trttr
    956   hsggghhgh                 pipip
    855   hdsgfgsddd                lklkl
    233   ghfgfggfgf                mkmk

TableB(ssn Foreign key reference)
id  ssn   Place
................
1   999   kerala
2   956   usa
3   855   ekm
5   233   chennai

Since your development environment evidently enforces foreign key relationships, changing the value of a key is a problem.

Options include:

Designing the database so that an important fact like SSN isn't an internal key in the first place (too late to do that).

(If SSN in your database doesn't represent an important fact, then it may not be necessary to change it in the development copy, since it only exposes meaningful data when connected to the real personal data in production, which you are removing.  But you and your colleagues may disagree with me.)

Changing data for one SSN at a time - for instance input a row of 000, Proxy, Proxy in TableA; change all SSN of 111 in other tables to 000; change SSN 111 in TableA to 999; change all SSN 000 in other tables to 999.  I have done this in production data, when someone was recorded with two different service IDs in different databases and we needed to merge together the data from two IDs.

Copying data to a database that does not have referential constraints on the tables; change the data however you like, but finishing with correct relationships; copy the data again into another database where there are  constraints.

If the language and design allows, delete or turn off the foreign key enforcement, change your data, then create or turn on the foreign keys.  But beware of constraints in the database that were already disabled.  If at the end you turn on all constraints that were disabled, you may also turn on the constraints that were already disabled before you started.

Standard tools to produce anonymous identity data may be useful to you.  For names, I've used a method of taking a list of reasonable and distinct forenames, and a list of surnames, two different prime numbers in length (or "co-prime").  Take names in (semi random) order from one list, and from the other, in turn, returning from end of list to the start.  Combinations of names shouldn't be repeated until all pairs have been used once.  My list included one spelling of "Mohamed", and as a surname, because I thought it would be provocative to produce a "Mohamed Singh".  It also didn't occur to me to use "hsggghhgh", but you could use "User101first" and "User101last", etc.


Unpredictable performance of queries against snapshot I've got a basic reporting solution working where we have a collection of views that get exported to CSV files on a daily basis. For numerous reasons, we don't run the reports against the transactional db, but against a snapshot that we temporarily create and then drop once the reports are complete.

These reports are pretty simple and run fine on dev machines, even with millions of rows.

On our test environment, the performance is all over the place, sometimes taking a couple of seconds (same as dev) but often taking over a minute. I've run a few scripts to try and identify the cause, and the problem is always a wait type of PAGEIOLATCH_SH.

The disk IO at these times is low (2-5mb/sec), the CPU is at about 5%, no other queries need to be happening for the problem to occur, and the database is small (2gb) compared to server memory (32gb). The reports are run on the same machine, but write to a different physical disk.

Is the fact that the database is in an Always On Availability Group likely to be causing an issue? Would it help for the snapshot to be on a different disk from the master?

Any pointers on what the problem might be or how to investigate would be much appreciated!

PAGEIOLATCH_SH means waits for reading database pages.  When you create a database snapshot, none of the database pages are in the cache, and you will have to fetch them from disk.

  disk IO at these times is low (2-5mb/sec),

The query plans may be driving small, random IO, and 2-5mb/sec is all your disk can supply.

  Would it help for the snapshot to be on a different disk from the master?

A Database Snapshot is a NTFS copy-on-write snapshot of the target database.  There is only one copy of the unchanged pages shared between the database and its snapshot.  If you use a backup/restore instead of a Database Snapshot, then you could put it on a different disk.


Storing Recurring Datasets I'm trying to store the following arrays into a database, but due to my inexperience, I don't know how to set it up.

It's for a scheduler. A day entry holds three arrays:

The 1st array holds the basic entry ['prefix', begin, increment, 'suffix'], that displays:

'prefix' begin - (begin + increment) 'suffix'
The 2nd array holds periodic entries that displays every number. For example, every 5 basic entries, display a new type.
The 3rd array holds the days to display on.

There will also be week entries, that can contain up to 7 day entries.

I organized it as the following:

Day entry:

'day' => [
    ['prefix', begin, increment, 'suffix'],
    [
        [every, ['prefix', begin, increment, 'suffix']],
        [every, ['prefix', begin, increment, 'suffix']],
        [every, ['prefix', begin, increment, 'suffix']],
        (Haven't decided on limit)
    ],
    [list of days]
]

I thought of just sticking all of that data into one column as it is, but then I remembered that I also needed to store week entries.

'week' => [
  'day' => [
      ['prefix', begin, increment, 'suffix'],
      [
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          etc...
      ],
      [list of days]
  ],
  'day' => [
      ['prefix', begin, increment, 'suffix'],
      [
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          etc...
      ],
      [list of days]
  ],
  (Possibly 5 more 'days')
]

I'll have an id column and a few other irrelevant columns for each entry, but that's the primary data. I haven't written much code yet, so I'm willing to change anything.

My question is, what's the most maintainable, efficient, etc. way to store this data in a mySQL database?

Edit: The data would look something like this:

'day' => [
    ['Lesson', 1, 2, ''],
    [
        [5, ['Test', 1, 0, '(study)']],
        [10, ['Investigation', 10, 0, '(study)']]
    ],
    [1, 2, 3, 4, 5]
]

And a week entry could hold up to 7 of these day entries.

A database table has only two dimensions:  rows and columns.  Your JSON-like stuff is not amenable to RDBMS, unless you want to store that hierarchical stuff in a blob that the database engine does not need to look into.  When you store a jpeg in a table, all you can do is retrieve the jpeg; you cannot ask the database to look for yellow clowns among all the jpegs.

(Actually there are ways to peek inside JSON and XML, but that is not the "real" way to use a RDBMS.)

Reformulate the data as one or more sets of similar rows and dissimilar columns; then we can talk further.


Order By with duration strings having time units I have a SQLite database with a table "Rate" who has a column "timeToMaturity". This column contains strings such as :

7Months
26Days
3Years

I'd like to order a query results by timeToMaturity (i.e. 26Days < 7Months < 3Years). However, because they are strings, I have no idea how I should proceed.

Thank you for your help

If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit:

CASE
    WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1
    WHEN timeToMaturity LIKE '%Weeks' THEN cast(rtrim(InterestRate.timeToMat,'abcdefghijklmnopqrstuvwxyz')as INT) * 7
    WHEN timeToMaturity LIKE '%Months' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 30
    WHEN timeToMaturity LIKE '%Years' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 365
        ELSE 0
    END AS TotalDaysToMaturity

Now, I realize that you may need to do something different when it comes to months as all months are not equal, but it may fit your needs. Perhaps someone would come by to figure that out dynamically.


is "create schema" the same way of saying "create database"? I'm a beginner in DBMS, sorry if my question sounds dumb. I often see some textbooks says: create a schema . does it that mean "create a database"?

But isn't that a schema is also a skeleton of a table? don't we often describe a table's structure such as column name/column type using schema? so does schema a table concept or database concept?

This is for SQLServer, databases and schemas are different in other DBMS like Oracle.

A database and a schema are two different concepts.
A database can contain multiple schemas, for example the default schema 'dbo'

You can address a table by specifiying database.schema.tablename.

Schemas are often used for security reasons, cause you can have one database, multiple schemas, and you can give permissions on the schemas. That way you don't have to specify permissions for each of the objects in the database.
Eg. HR users have select permissions on the entire HR schema, while IT only has select permissions on the IT schema

It also gives you a nice grouping of objects that belong together.

Some more useful information:
http://www.abstractnew.com/2014/11/database-vs-schema-in-database-systems.html
https://www.brentozar.com/archive/2010/05/why-use-schemas/


Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error I recently upgraded from SQL Server 2005 to 2012. During validation however, a bug was discovered.

A certain trigger was coded as follows:

CREATE TRIGGER [dbo].[trigger] on [dbo].[foo]
FOR UPDATE, UPDATE
AS
    UPDATE foobar
    SET datetime = GetDate()
    FROM bar
    WHERE foobar.id = bar.id
GO

I can safely execute this (oddly) on SQL Server 2005.

However on SQL Server 2012 it throws (what I would expect) a syntax error.

  Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

Why does this not throw a syntax error on SQL Server 2005? My google-fu on this has failed me.

Why does this seemingly work on SQL Server 2005?

John is right that the updated syntax error message you get:

  Msg 1034, Level 15, State 1, Procedure trigger
  Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

This is absolutely because the trigger is specified as FOR UPDATE, UPDATE. This syntax was allowed in older compatibility levels but not in modern ones.

In fact, I have tested this and the syntax was allowed in 80 compatibility mode (SQL Server 2000), but not in 90+. I've updated my canonical answer about compat level 80.

(As for why it's not a documented breaking change, well, shrug. You'll need to approach Microsoft directly if you want a meaningful answer to that one.)

As for the trigger itself, here's how I would write this:

UPDATE t
  SET t.datetime = GETDATE()
FROM dbo.table1 AS t
INNER JOIN dbo.table2 AS t2
ON t.id = t.id;

The syntax error might be a more strict parsing of the word table, not sure. If that really is the name of your table, you should change it, or [put it in square brackets].


What happens when you alter a table column's length, but that length is already that length? Say we run an alter table command to change the table column length from 5 to 6. If we run the same command again, does Oracle alter the table again?

Yes it does because database doesn't check the previous value it only sets the new value. The following demonstrates it.

SQL> conn jay
Connected.
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter table test modify name varchar2(20);

Table altered.

SQL> alter table test modify name varchar2(20);

Table altered.

SQL> alter session set sql_trace=false;

Session altered.

SQL> exit

The following is formatted output of trace file generated by tkprof.

update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
  decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
  audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
  rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
  analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
  null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
  flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
  spare6=:35
where
 obj#=:1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          4           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          4           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  TAB$ (cr=3 pr=0 pw=0 time=175 us)
         1          1          1   TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=11 us cost=2 size=142 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

SQL ID: 6vqvn8ya0xybh Plan Hash: 829209562

update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
  182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
  decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
  183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
  property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
  deflength=decode(:19,0,null,:19),default$=:20
where
 obj#=:1 and name=:2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          4           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          4           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  COL$ (cr=3 pr=0 pw=0 time=74 us)
         1          1          1   TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=19 us cost=2 size=61 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

SQL ID: 4yyb4104skrwj Plan Hash: 2683643009

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
  dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
where
 owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname
  is null and subname is null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          2           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          2           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  OBJ$ (cr=3 pr=0 pw=0 time=114 us)
         1          1          1   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=39 us cost=3 size=87 card=1)(object id 37)

********************************************************************************

As you can see the tab$, col$and obj$ base tables are updated two times(evidenced by number of executes which is 2). These base tables stores the objects meta data.


How do you get the graph plugin for Squirrel SQL? I installed Squirrel SQL and the website claims the Graph plugin is part of the install.  It is not available nor is it in the jar.  How do I get the graph plugin?

As per Installation documentation the correct version of each available plugin is always provided in the SQuirreL SQL Client install jar.

Graph Plugin by Gerd Wagner
Installer Category: Standard
This plugin allows you to create and print charts of database tables and their foreign key relations. If you use it together with the SQL Scripts plugin you are able to script tables from within the charts.

You can download it from http://mvnrepository.com/artifact/net.sf.squirrel-sql.plugins/graph/3.2.1