MYSQL Indexes when looking up on alternating sets of columns I have a table that looks like:

CREATE TABLE `connections` (
`connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`service_id` smallint(5) unsigned DEFAULT NULL,
`parent_id` bigint(20) unsigned DEFAULT NULL,
`child_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`connection_id`),
UNIQUE KEY `primary_child` (`parent_id`,`child_id`),
UNIQUE KEY `child_primary` (`child_id`,`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I need to do lookups on child_id and get information about parent_id and vice versa.

Ie:

SELECT COUNT(`parent_id`) FROM `connections` WHERE `child_id` = 'x'

or

SELECT COUNT(`child_id`) FORM `connections` WHERE `parent_id` = 'x'

And some more advanced queries like:

SELECT DISTINCT `parent_id`
FROM `connections` `c1`
INNER JOIN `connections` `c2`
ON `c1`.`parent_id` = `c2`.`parent_id`
WHERE `c1`.`child_id` = 'x'
AND `c2`.`child_id` = 'y'

I'm looking to get a better idea of the most efficient index structure to get quick responses from all the above query examples.

It should be noted that the child/parent pairs will be unique.

EDIT: This table currently holds 40M rows, likely to be much larger in the near future.

Thanks,

You already have indices on both colums with the unique constraint. No need for new ones.

I hope you have a good reason avoiding a foreign key. I hope you pay attention to keep your data consistent...

btw: parent_id in your join query is ambiguous.


What would be possible reasons for a DeadLock to occur in Mysql Database? If some one has a Table structure some thing like this,

CREATE TABLE IF NOT EXISTS `domain_source` (`nid` int(11) NOT NULL DEFAULT '0', `domain_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`nid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores the canonical domain for each node.';

On Heavy load i get Deadlock  on Insert to this Table Structure. I usually delete and insert a row in the table rather than update as the engine is Innodb.

Why DELETE+INSERT instead of UPDATE ??

Do you have BEGIN...COMMIT around the statements?

Are you hitting multiple rows in the DELETE and/or INSERT?

Are you doing DELETE ... WHERE domain_id = ... ?  If so, notice that you have no index, hence it will have to scan the whole table!

(Sorry, that does not even begin to answer the question you posed.  But it may lead to a solution to the real question.)


Fast way to drop schema with large amount of objects for the purpose of testing I have to drop a schema with a large amount of objects often. It consists of > 2000 tables, a large amount of foreign key and check constraints, triggers views and so on. All tables are within the same tablespace, but the tablespace is used by other schemas too.

I did try admin_drop_schema procedure at first, but it takes a lot more time than parsing all schema catalogs, generate all drop statements and execute it as one sql script. The script runs under 45 minutes and the procedure more than a hour. Further more the procedure locks to many things.

What is the fastest way to drop such a schema?

Several years ago I did put together a package of db2utils which included a procedure called DROP_SCHEMA. This was written before ADMIN_DROP_SCHEMA became available in DB2, and uses a fairly crude mechanism for determining what to drop, and in what order. To whit: it uses a large UNION query to get all objects belonging to the schema from the various SYSCAT views:

WITH DROP_LIST (CREATE_TIME, SCHEMA_NAME, DDL) AS (
    SELECT
        CREATE_TIME,
        TABSCHEMA AS SCHEMA_NAME,
        'DROP ' || CASE TYPE
            WHEN 'A' THEN 'ALIAS'
            WHEN 'H' THEN 'TABLE'
            WHEN 'N' THEN 'NICKNAME'
            WHEN 'S' THEN 'TABLE'
            WHEN 'T' THEN 'TABLE'
            WHEN 'U' THEN 'TABLE'
            WHEN 'V' THEN 'VIEW'
            WHEN 'W' THEN 'VIEW'
        END || ' ' || QUOTE_IDENTIFIER(TABSCHEMA) || '.' || QUOTE_IDENTIFIER(TABNAME) AS DDL
    FROM SYSCAT.TABLES
    UNION
    SELECT
        CREATE_TIME,
        TRIGSCHEMA AS SCHEMA_NAME,
        'DROP TRIGGER ' || QUOTE_IDENTIFIER(TRIGSCHEMA) || '.' || QUOTE_IDENTIFIER(TRIGNAME) AS DDL
    FROM SYSCAT.TRIGGERS
    UNION
    SELECT
        CREATE_TIME,
        ROUTINESCHEMA AS SCHEMA_NAME,
        'DROP ' || CASE ROUTINETYPE
            WHEN 'F' THEN 'SPECIFIC FUNCTION'
            WHEN 'M' THEN 'SPECIFIC METHOD'
            WHEN 'P' THEN 'SPECIFIC PROCEDURE'
        END || ' ' || QUOTE_IDENTIFIER(ROUTINESCHEMA) || '.' || QUOTE_IDENTIFIER(SPECIFICNAME) AS DDL
    FROM SYSCAT.ROUTINES
    UNION
    SELECT
        CREATE_TIME,
        TYPESCHEMA AS SCHEMA_NAME,
        'DROP TYPE ' || QUOTE_IDENTIFIER(TYPESCHEMA) || '.' || QUOTE_IDENTIFIER(TYPENAME) AS DDL
    FROM SYSCAT.DATATYPES
    UNION
    SELECT
        CREATE_TIME,
        SEQSCHEMA AS SCHEMA_NAME,
        'DROP SEQUENCE ' || QUOTE_IDENTIFIER(SEQSCHEMA) || '.' || QUOTE_IDENTIFIER(SEQNAME) AS DDL
    FROM SYSCAT.SEQUENCES
    WHERE SEQTYPE <> 'I'
    UNION
    SELECT
        CREATE_TIME,
        SCHEMANAME AS SCHEMA_NAME,
        'DROP SCHEMA ' || QUOTE_IDENTIFIER(SCHEMANAME) || ' RESTRICT' AS DDL
    FROM SYSCAT.SCHEMATA
)
SELECT CREATE_TIME, DDL
FROM DROP_LIST
WHERE SCHEMA_NAME = ASCHEMA!

(Full source; the QUOTE_IDENTIFIER function is another utility in the package)

It then orders the result in descending order by creation time, running the SQL DROP statements with EXECUTE IMMEDIATE:

BEGIN ATOMIC
    FOR D AS
        SELECT DDL
        FROM TABLE(X_DROP_LIST(ASCHEMA))
        ORDER BY CREATE_TIME DESC
    DO
        EXECUTE IMMEDIATE D.DDL;
    END FOR;
END

This is simple, but crude especially in light of DB2's later introduction of deferred revalidation of objects. This can result in creation timestamp orderings for which the drop sequence is no longer correct. In practice, it mostly worked for me and on the rare occasions it didn't I could fall back to running the query manually to figure out what needed dealing with.

This brings me to the other major difference to ADMIN_DROP_SCHEMA: error handling. ADMIN_DROP_SCHEMA takes several parameters which are used to construct and fill a table with error codes and messages for post-analysis.

While comprehensive, I found this didn't work well with most of my use-cases. Most of the time I was using these tools to tear down development / testing schemas, then tweaking a creation script and re-running it, or using it to destroy relatively simple personal schemas when users were deleted. Hence I designed DROP_SCHEMA deliberately to be atomic: it either works and the schema disappears in its entirety, or it fails with an error and the schema remains in its entirety (assuming you rollback the containing transaction).

I did generally find it faster than ADMIN_DROP_SCHEMA but that wasn't an intention of the design, and given I haven't maintained it for several years there may well be new schema-based objects in DB2 which it doesn't correctly handle. Still, it may serve as a useful starting point for others' efforts.


Map Oracle roles to Active Directory groups? Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?

You want to manage your users, roles and user to role mappings in AD?

I think Enterprise User Security is what you want, see http://docs.oracle.com/cd/E11882_01/network.112/e10744/concepts.htm#autoId10

Basically you'll need Oracle Enterprise Edition, and, after you've configured your database to use your directory (see http://docs.oracle.com/cd/E11882_01/network.112/e10744/getstrtd.htm#CBHDBFGC ), you can create your users and roles as
CREATE USER x IDENTIFIED GLOBALLY;
CREATE ROLE y IDENTIFIED GLOBALLY;

You can assign privileges to role y, but you should manage user to role mappings in AD.


SQL Server Backup Failure This one has me stumped and I'm sure there is a simple explanation that I cannot fathom at present.

A few informational pieces:

vCentre Database - SIMPLE Recovery Model
SQL Server 2008 R2 Standard Edition

We have a FULL Database backup (driven by Ola's scripts) that is failing as a result of a full transaction log.   We are running the backup WITH CHECKSUM which I suspect may be the cause, but I don't entirely understand why the FULL Backup would need to perform a write operation against the database log file.

I'm conscious that the FULL Backup will need to read from the transaction log to the LSN of the oldest active transaction for consistency. However, does the FULL BACKUP task write a minute amount into the log file to note that it's happened / happening as well?

Error from the Ola Log:

Date and time: 2017-03-13 21:30:40
Command: BACKUP DATABASE [DB] TO DISK = N'M:\Folder\file.bak' WITH CHECKSUM, NO_COMPRESSION

Processed 1579192 pages for database 'DB', file 'DB_dat' on file 1.

Processed 855886 pages for database 'DB', file 'DB_log' on file 1.

Msg 9002, Level 17, State 3, Server SERVER\INSTANCE, Line 1

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Msg 3013, Level 16, State 1, Server SERVER\INSTANCE, Line 1

BACKUP DATABASE is terminating abnormally.

Outcome: Failed

Duration: 00:13:19

Sean Gallardy provided the information that led to the answer when he commented about the Backup forcing a Checkpoint:

Paul Randall Blog on Checkpionts


Is there a slave configuration that stops replication of mysql.mysql and grants? I have a mariadb slave that cannot have the same permissions and users as the the mysql master. Is there a way to configure the slave so that it does not replicate changes to the mysql system tables and does not try to run GRANT statements?

I am currently using replicate_wild_do_table=mydb.%, and I tried using replicate_wild_ignore_table=mysql.% but that did not seem to ignore the GRANT statements.

I noticed replicate_wild_do_table=mydb.% seems to be sufficient on a mysql slave to not replicate changes to the mysql system table, but it is not sufficient on a mariadb slave.

Use binlog-ignore-db instead. This post explains the behaviour and answers your question. If not, comment.


Missing Database Mail Configuration Wizard in SQL Server 2008 R2 I was able to verify that Database Mail is installed and running but when I expand the management folder there is no Database Mail Configuration Wizard.

The login is a sysadmin.  I am very green to using SQL but I have to fight my way through this first project before I can step back and actually learn it.

I figured out the problem.  I found that I was unknowingly running Express.  I had incorrectly assumed the Express designation would be more visible withing the initial result of the @@version query

In SSMS run the SELECT @@version query
Grab the column edge and drag it all the way to the right. Within the copyright information it will list Express.

From what I am finding online there are features of Database mail that exist in Express and that is why I was able to enable and run it.


What happens at the data page level when the record length changes I've been digging around the net at work when I can for a few days now trying to make sense of how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.

At a high level you could think: I can just put a pointer at the end that points to the new column. However, at the page level aren't data pages filled with individual records? Wouldn't adding a column mean that every page that was already full would require a split?

Even pages that weren't full would require a lot of data juggling to add that column to the end of each record, update all the slot arrays, then cascade all the pointer changes through any existing indexes and/or the IAM and GAM pages?

The only thing I can think of is that all new column data is added to new pages, without the rest of the record, and pointers are added throughout the table tree structure to reference the new column pages. However, this seems like it would ruin spatial locality. If this is it, does the DBMS juggle data behind the scenes even when we don't specifically request a REBUILD?

I'm talking about the bit level of DBMS memory management with pages and asking how DBMSs are able to add a column (with or without allowing NULL values) to a set of existing records so quickly, even though the records already exist as a set of bits in a data page.

how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.

Well, there is a false assumption here, that being that adding a new column is always done quickly. This is not a true statement.

Now, when adding a column that allows NULLs, that can be done quickly as the meta-data of the Table definition gets updated but the NULL isn't physically added to the datapages in that moment. SQL Server can return the correct NULL to queries since it is logically obvious what the "value" is. When rows are inserted or updated, the records that are written to the datapages do include the NULL (for fixed-length columns, unless the SPARSE option was used for the new column or Data Compression is enabled on the Clustered Index). But the rest of the rows that have not been updated will not have the NULL physically added until an index REBUILD.

However, when adding a column marked as NOT NULL, then, prior to SQL Server 2012 (and even then, only if the new value was a runtime constant), then the actual value was written physically to the datapages in that moment, and that operation could take a looooooooooong time, depending on how many rows and/or how much data was in the table. You can find plenty of questions and articles about trying to overcome this issue as tables with many GBs of data and/or hundreds of millions of rows could take hours to add a new NOT NULL column.

Then came a truly wonderful new feature in SQL Server 2012 (Enterprise Edition-only, which also implies Developer Edition) whereby adding a new NOT NULL column with a default value could be an instantaneous, meta-data only operation, just like adding a column marked as NULL. The only caveats are that the datatype not be a LOB (e.g. MAX-types, XML, etc) or CLR-based type, and that the value be a runtime constant (i.e. mainly literal values). Something like NEWID() would not be instantaneous since it would need a different value per each row. But for values that are runtime constants, SELECT operations can easily get the correct value by just looking at the meta-data of the DEFAULT which gives the logically-obvious value.

The MSDN page for ALTER TABLE, in the Locks and ALTER TABLE section (under "Adding NOT NULL Columns as an Online Operation"), talks about this behavior.


mysql db backup and restore withought mysqldump. only tar? on my linux system all databases reside in /var/lib/mysql.
Let's say my db is /var/lib/mysql/test1_db

Is it possible and/or advisable to just tar the db directry?
And then use the resulting .tar file to restore it.
Yes, I am stopping mysqld during the process.

If this is ill advised is there something like sql instant file init for linux?

Yes.

Stop mysqld
tar that directory
Start mysqld

It will backup everything except the config file(s) such as my.cnf.

LVM snapshot is much faster, but requires setup.


Pass parameter to trigger dynamically I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport.

I have another table that lists some calculated statistics based on those values.

I managed to do the calculcations that end up on the second using stored procedures.

I use as input parameter to the stored procedure the athlete's name.

So when in the first table, an athlete is inserted (with his/her avg/min/max times) or his/her values are updated and I run the stored procedure, the later updates the statistics table.

My question is how to achieve the same result with triggers.

I guess it is feasible/easy to update the entire table on each insert or update of the first table.

What would be more efficient performance-wise, would be on each

INSERT into table1 values (..) where athlete_name="John Do"

(...)

ON DUPLICATE KEY UPDATE (...)

run a trigger in the pseudocode form

INSERT into statistics_table values (..) where athlete_name="John Do"

ON DUPLICATE KEY UPDATE (...)

How can the the athlete_name="John Do" be passed to the trigger dynamically, to avoid update the entire statistics table?

Since recalculating rankings for a large table can take a long time, and doing that frequently can mean a really long time, start with this question:

Will the recalculating during SELECT be "fast enough"?  If the answer to this question is 'yes', then simply do it.
Will the recalculation will be "fast enough" to do during INSERTs.  Also, ask whether the burden on the system is "too much".  If this is OK, then simply do it.
If both of those don't pass muster, periodically recompute the rankings.

Note that MySQL 8.0 and MariaDB 10.2 have ranking and other "windowing functions".  However, you need to test them -- they may not be "fast enough"; after all 'ranking' probably requires reading and sorting the entire table.


Oracle equiv of T-SQL UNION ALL query In T-SQL I can accumulate the results of two unioned queries using the following syntax:

SELECT Q.a, SUM(Q.b)
FROM (
SELECT f1 as a, f2 as b FROM TBL1
UNION ALL
SELECT f1 as a, f2 as b FROM TBL2
)
GROUP BY Q.a

What is the equivalent in Oracle?

Assuming that you alias the inline view to Q, that syntax should be perfectly valid in Oracle.  I'm assuming that you actually have the alias in the SQL Server version as well.

SQL> ed
Wrote file afiedt.buf

  1  select q.a, sum(q.b)
  2    from (select deptno a, sal b from emp
  3          union all
  4          select 40, 10000 from dual
  5         ) q
  6*  group by q.a
SQL>
SQL> /

         A   SUM(Q.B)
---------- ----------
        30       9406
        20      10880
        40      10000
        10       8753


Unable to change servername after VM Clone For reasons out of my control I must find a solution to this issue. Simply reinstalling the instance is not an option. The server name that appears as server_id 0 in sys.servers is still showing the old servername.

I'm getting an error when running the below command:

sp_dropserver 'OLD_INSTANCE'
GO
sp_addserver 'NEW_INSTANCE', Local
GO

Error message:

  Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 67
  There are still remote logins or linked logins for the server 'OLD_INSTANCE'.
  Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 87
  The server 'NEW_INSTANCE' already exists.

The strangest thing is, the remote login is a 'null' login.

exec sp_dropremotelogin @remoteserver = 'OLD_INSTANCE'
go

Error message:

  Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 70
  There is no remote user '(null)' mapped to local user '(null)' from the remote server 'OLD_INSTANCE'.

No logins exist for the old instance.

sp_helpremotelogin 'OLD_INSTANCE'

  Msg 15201, Level 16, State 1, Procedure sp_helpremotelogin, Line 37
  There are no remote logins for the remote server 'OLD_INSTANCE'.

How do I rename this instance if I can't drop the non-existing login? Is there a way to flush the logins?

You need to check both sys.remote_logins and sys.linked_logins. You should be able to build a script dynamically to drop the set you find:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC sys.sp_droplinkedsrvlogin @s, N''' + remote_name + N''''
  FROM sys.linked_logins
  WHERE server_id = 0 AND remote_name IS NOT NULL;

SELECT @sql += N'EXEC sys.sp_dropremotelogin @s, N''' + remote_name + N''''
  FROM sys.remote_logins
  WHERE server_id = 0 AND remote_name IS NOT NULL;

PRINT @sql; -- spot check will only show first 8K of command

DECLARE @s NVARCHAR(512) = N'old server name';
--EXEC sys.sp_executesql @sql, N'@s NVARCHAR(512)', N'.\SQL2014';


Should we add extra 5 columns or build a separate table? Currently we have a table with 35 fields in it.

Now, we got a requirement to add 5 extra columns but these columns will not be fill always.

So, I was thinking just add one column and have that id as the foreign in another table with 5 or less rows depends on the availability of data.

Which is better

add the column ?
add a new table ?

The decision does not depend on the number of columns you already have, but on whether those 5 columns do belong to what you are trying to model.
There is no rule that says: "if you have 10 columns, keep in same table; use a separate table when you reach 36 columns".
If those 5 columns are like a person's street, town, region, country, then no; they should be on a separate table "location" or similar.
If those 5 columns are like a person's 1st name, last name, 2nd last name, ID-number, and email address, then they should probably be there.
However, if your table already has 35 columns, then you probably haven't done much normalization to begin with and it looks like you're just stuffing everything on that table...
Update

first we have a table called association then based on that table we have the gps data linked to the assocition. In addition we also got alert tables linked to the association too. So association is the main table here. Each device also is table by itself but it will be linked to a association when and where it is being used.
…
Basically the association is just like a trip from a to b. So moving from a to b there is a number of gps data which is kept in this main table [?]. If in this trip there are alerts then is kept in the alert table. This 5 new column are indicating the status of the rfid devices which are linked to the main gps device. But in a trip not necessary to have 5 it can be less or even none too.

Assumptions:

For each trip there can be 0..N alerts
as I understand it, what you call association is the description of a journey
multiple RFID devices are used per journey or trip.
A GPS Device has 0..N RFID devices associated to it; in other words, each rfid device is associated to one GPS device (not sure I understand the logic of this, though; why are RFID devices used? what's the role of the GPS if you are )
Given that you are using a GPS device, I assume there will be multiple readings during a Journey, in combination with RFID readings (?)
When you say moving from a to b there is a number of gps data which is kept in this main table, I'm not sure I understand what info is stored, or why it is stored in the table called association / journey instead of in some associative table (given that there seems to be "a number of gps data" and not just "one gps datum per journey")

Given this, I would add your 5 columns to the RFIDStatus table, since This 5 new column are indicating the status of the rfid devices. Below is a first attempt at modeling something similar to what you might be modeling...
[     Journey    ]<——————[  Alert  ]
| fk_from        |       | fk_trip |
| fk_to          |
|                |—(from)—>[    Location    ]
|                |–—(to)—–>| long/lat/name… |
|                |
|                |<—————[    RFIDStatus    ]—————>[RFIDDevice]—————>[ GPSDevice ]
|                |      | fk_rfiddevice    |      | fk_gps   |      |           |
|                |      | fk_journey       |                        |           |
|                |      | datetime         |                        |           |
|                |      | *status details* |                        |           |
|                |<———————————————————————————[ GPSStatus  ]———————>|           |
|                |                            | fk_journey |
|                |                            | fk_gpsdev  |
|                |                            | datetime   |
|                |                            | latitude   |
|                |                            | longitude  |

However, as I mentioned in the comments, it all depends on what you're modeling. Please update your question with more details so other people can give you a better answer. Also, you should read something about database modeling so you start from a good basis and a good understanding of what's needed when modeling a database; it will also help you to ask better questions regarding database design.


Select multiple rows based on date interval from two fields I am still learning SQL and I would like to do something that I dont know if it is possible. I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date.
It would be something like this:

Original:

    Product|price|initial_date|final_date
    A|20.50|2014-08-10|2014-10-01
    B|50|2015-01-15|2015-02-20

Resulting view:

 Product|price|Date
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01

I would like to do it, because this dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.

Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.

By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.

Do you have a calendar table ?
I am building a kind of calendar table with object cteSeries.
Then adjust the Initial_Date and Final_Date to hold BOM (beginning of Month YYYY-MM-01) in cteSample , fields: Initial_Date_M,Final_Date_M

DECLARE @tSample TABLE
(
    Product VARCHAR(10)
    ,Price  DECIMAL(18,2)
    ,Initial_Date DATE
    ,Final_Date DATE)
INSERT INTO  @tSample (Product,price,initial_date,final_date)
VALUES  ('A',20.50,'2014-08-10','2014-10-01')
    ,('B',50,'2015-01-15','2015-02-20');

DECLARE
@dStart AS DATE  ='20140101'
,@dEnd AS DATE ='20160101'

-- building a calendar table  -
;WITH cteSeries
AS
(SELECT @dStart AS cteDate
    UNION ALL
 SELECT DATEADD(MONTH,1,cteDate)
 FROM cteSeries
 WHERE
    cteDate < @dEnd
 )

--SELECT    * FROM  cteSeries OPTION (MAXRECURSION 0)

, cteSample
AS
(SELECT Product ,Price ,Initial_Date ,Final_Date
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Initial_Date), 0) AS  Initial_Date_M
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Final_Date), 0) Final_Date_M
    FROM @tSample
)

SELECT
  S.Product
  ,S.Price
  ,cteDate AS Date
  --,*
FROM
   cteSample AS S
   INNER JOIN cteSeries AS C
   ON S.Initial_Date_M<=C.cteDate
   AND S.Final_Date_M >= C.cteDate

and the output :

 Product    Price   Date
 A  20.50   2014-08-01
 A  20.50   2014-09-01
 A  20.50   2014-10-01
 B  50.00   2015-01-01
 B  50.00   2015-02-01


For those who encountered the same problem. I did not answer this but someone provided the answer, I'll leave the link here: https://stackoverflow.com/questions/52773824/recursive-query-in-oracle-to-find-children-and-sibling/52776124#52776124


SQL Server equivalent of Oracle USING INDEX clause Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle?
Specifically for the construct:
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

In the Sql Server Documentation on Unique Indexes it states (emphasis added):

Unique indexes are implemented in the following ways:
PRIMARY KEY or UNIQUE constraint
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

Which seems to imply that there is a way of specifying what index should be used for a Primary Key.

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle?

No. When you create a primary key or unique constraint in SQL Server, a unique index to support that constraint is created automatically, with the same keys.

  Which seems to imply that there is a way of specifying what index should be used for a Primary Key.

No. The documentation is only attempting to explain whether the automatic supporting index will be created as clustered or nonclustered, if you do not specify. It is confusingly worded, I agree.

To clarify, when you add a primary key constraint to an existing table without expressing a preference, the supporting index will be clustered if there is no pre-existing clustered index on the table. The supporting index will be created
as nonclustered if there is already a clustered index

You can specifically request a clustered or nonclustered primary key using: PRIMARY KEY CLUSTERED or PRIMARY KEY NONCLUSTERED.

In fairness, the documentation is much clearer on the subject at:

table_constraint (Transact-SQL)


Can't Connect to MySQL database through PuTTY I have a database that runs on pythonanywhere.com, and am trying to connect Tableau to it, and need to set up an SSH Tunnel in order to do so.

The directions on pythonanywhere's website mention PuTTY, and many people on Tableau forums also use it, so this is what I'm going with.  I've been trying for about 2 weeks to get this working, but no luck.

So here's what happens:

1).  I enter ssh.pythonanywhere.com as the host connection on the main page:

2).  I then go to SSH --> Tunnels, and enter the following information:

The information in the Destination field has the form my-username.mysql.pythonanywhere-services.com:3306

Then I hit Open, and am prompted for my sign in credentials to Python Anywhere:

Then I go to Tableau and enter the following information:

In the password field I type in my database password.

The message that always comes up is:

[MySQL][ODBC 8.0(w) Driver]Access denied for user 'myusername'@'localhost' (using password: YES)
Invalid username or password.

Now obviously, there's a natural response to this:  double check my password.

For now just take my word for it that I've double checked this several times and it is correct.

I've tried to find help on PythonAnywhere's forums, and Tableau's forums, but no solutions have been found.

Also:

I've tried switching out the port 127.0.0.1 with localhost, but this did not work.

I have connected to my database externally in a Jupyter Notebook using this same information, so I have some confirmation that this info has been used successfully.

I've tried connecting to MySQL Workbench using the same info and I get the same error message, which makes me think there's something about my database configuration which is not working, but I have no idea where to look.

UPDATE:

In my PuTTY Bash terminal the command telnet 127.0.0.1 3306 returns the message:

Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

In my DOS terminal I get the following:

In my git BASH terminal I type in netstat -an|grep 3306 and get:

UPDATE 2:

I was on codementor trying to get this resolved, and we had the following issue:

after signing in throuhg PuTTY, I entered the following command:

mysql -h 127.0.0.1 -u jonathanbechtel -p

Which returned the following error message:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

We also tried switching out 127.0.0.1 with localhost and got a different error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

To be honest I don't understand the significance of this, but was told by him that this is unusual and might signal some security settings on my account with PythonAnywhere.  I started this forum message on their forums to try and troubleshoot it.

If you are connecting on "localhost", then GRANT ... TO user@localhost ... applies.  "127.0.0.1" may be a synonym for "localhost".

If you are connecting via TCP/IP, then GRANT ... TO user@'11.22.33.44' ... applies.  (Replace with suitable IP or hostname or wildcard.)

It is OK to have both GRANTs in order to allow access either way.


SQL Server stored procedure that would enforce atomicity? Let's say I have following insert statements

INSERT INTO tEmpl  (lastName)  VALUES('Smith')
INSERT INTO tEmpl  (lastName) VALUES('Jones')
INSERT INTO tEmpl  (lastName) VALUES('Smitty')
INSERT INTO tEmpl  (lastName) VALUES('Smithson')
GO

What stored procedure would enforce atomicity? I mean, all inserts have to be executed at once or none of them. Please give examples. I am still learning SQL Server.

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO tEmpl  (lastName)  VALUES('Smith'),('Jones'),('Smitty'),('Smithson')

COMMIT TRANSACTION
PRINT 'Transaction committed successfully'

END TRY

BEGIN CATCH
ROLLBACK
PRINT 'Transaction rolled back'

END CATCH


Change database and execute update query (T- Transact) Could anyone face with issue like me?

I need to update some rows in table, but I don't know exact name of database. I am able to figure out name with:

Select name
from sys.databases
where name like '%%'

and then I need to execute update query, but I can't because I don't know how to force SQL server change database.

use "name of database"

update table
set column1 = column1 + 1

I tried to declare variable and set it as exact name of database.

*EXEC ('use ' + '@dbname')
update table
set column1 = column1 + 1*

In this case, I got error about invalid object "table"

Does anyone know how to do it?

One solution would be to use SQL Servers sp_executeslql function. Utilising this function you could write some sql like the sample below.

DECLARE @DatabaseToUse  VARCHAR(100),
        @SQLToRun       VARCHAR(200);

SELECT  @DateBaseToUse = 'MyDataBaseName';

SELECT @SQLToRun = 'UPDATE ' + @DatabaseToUse  + '.dbo.Table SET Column1 = Column1 + 1';

Execute sp_executesql @SQLToRun;

You should replace the .dbo. with the name of your Schema.


Reference a newly created computed column in the same query where we create it SELECT
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE ComputedColumn !="dec"
INTO Table2
FROM Table1;

This is an example where I have a computed column (which is essentially created by using an expression). If I run this, I get a "column name invalid" error, because I make a reference to the computed column in the same query where I created it.

In an application called SAS, this is solved by prefixing the computed column name in the WHERE clause with the "(CALCULATED)" keyword. Is there a similar solution in Sql Server.
Other than doing this:

SELECT
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE (SUBSTRING(Column1,1,3)) !="dec"
INTO Table2
FROM Table1;

Which I know it is possible but very confusing when the expression is very huge. There must be another way to REFERENCE A NEWLY CREATED COMPUTED COLUMN in the same query where we create it.

Thanks a billion!
Anna

You can use CROSS APPLY for this:

SELECT ComputedColumn
INTO Table2
FROM Table1
CROSS APPLY (SELECT SUBSTRING(Column1,1,3)) CxA(ComputedColumn)
WHERE ComputedColumn !="dec"


How to create a Windows user suitable for installing db2 server with extended security? I'm trying to install db2 10.5 on Windows Server 2008 not as built-in "Administrator", but as another local user, but it fails to create instance with the symptoms similar to http://www-01.ibm.com/support/docview.wss?uid=swg1IC85663

First it complains it cannot create DB2ADMNS and DB2USERS groups (in fact, the groups appear after installer dies)

Then there's a another mesage:

  The creation of the process for the partitioned instance failed.
  Return value is "4294965337".

I don't have Active Directory. All users are local. Here's how I create the user for installing db2:

net user db2installer s3cret /ADD
net localgroup Administrators db2installer /add

I don's see any difference between "Administrator" and "db2installer". Perhaps "db2installer" should be added to some security policy?

Problem solved by setting

 Security options/User Account Control: run all administrators in admin approval mode = Disabled

in Local Security Policy


SSIS packages running forever without completing. OLEDB Source query suspended We have several packages that will intermittently run until they are killed.

When looking at the Activity Monitor we can see the source query for one of the data flows is in a suspended state with an ASYNC_NETWORK_IO wait. The task will remain in the state until the process is killed.

This has been happening to several different packages on several different servers. The query that becomes suspended is always a really simple query that will run in under a second if run in SSMS.

There are 2 queries we see that get suspended often. One is an inner join from one table in a database to a table in another database. It returns ~500 rows with ~10 columns in under a second usually. The second one is just Select * from a table with ~100,000 rows.

I have been reading up  on what the ASYNC_NETWORK_IO means and it looks like it is typically caused by the client application not signalling to SQL that it has consumed the data.

I have also read that it could be a resource/NIC issue but that seems unlikely to me. Some of these servers are overspec'd and the package is running on the server that holds all the data it manipulates. There should be no need for any network usage.

How can I ensure that my packages will not be suspended indefinitely?

EDIT:

The two servers that have had this issue recently have both been SQL Server 2014 Standard Edition. Version 12.0.5605.1 and Version 12.0.4213.0 .

EDIT2:
I am pretty sure the cause is back pressure caused by a whole bunch of expensive transformations. The package has around 10 Oledb commands and a few slowly changing dimensions. I think It just needs to be re-written.

Based upon your description my guess is that the rest of the SSIS data flow is the problem in that it can’t process rows as fast as the source query sends them. Thus SSIS applies backpressure so that you don’t get a huge amount of rows in memory in SSIS. This backpressure causes the SQL query to be suspended. Can you describe the full data flow and all the transforms/components/destinations?

I would recommend installing the free BI Developer Extensions and running the Pipeline Component Performance Breakdown feature to confirm which component is your bottleneck.


MySQL Timezone Resets after exiting server I am running this code to set my database's timezone:

set time_zone = "US/Eastern"

This successfully changes the time to the correct time. However, as soon as I "exit" the database and re-enter, it reverts back to what I had originally changed it from. I have also tried this code:

set time_zone = "-04:00";

The same thing happens, it reverts to the servers default time. Any suggestions?

Abstract taken from another Stack Overflow Answer - Adjusting for the
  default time-zone setting on RDS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

$ rds-describe-db-parameters default | grep "time_zone"
DBPARAMETER  default_time_zone                                                                   engine-default  string   static   false

To set the global value via SET GLOBAL you need to have the SUPER privilege which is not granted to you as a RDS user.

The only way to set the time_zone is on a per-connection basis

mysql> SET time_zone = timezone;

On my machines I've tried US/Eastern successfully but I got a quite old generation running.

To determine the timezones you have available log into your box

mysql -h yourboxhost.rds.amazonaws.com -u <youruser> -p

and type

mysql> SELECT * FROM mysql.time_zone_name;

You should get a list of installed and valid timezone names you can set on your instance

+----------------------------------------+--------------+
| Name                                   | Time_zone_id |
+----------------------------------------+--------------+
| Africa/Abidjan                         |            1 |
| Africa/Accra                           |            2 |
| Africa/Addis_Ababa                     |            3 |
| Africa/Algiers                         |            4 |
| Africa/Asmara                          |            5 |
| Africa/Asmera                          |            6 |
| Africa/Bamako                          |            7 |
| Africa/Bangui                          |            8 |
| Africa/Banjul                          |            9 |
| Africa/Bissau                          |           10 |
| Africa/Blantyre                        |           11 |
| Africa/Brazzaville                     |           12 |
| Africa/Bujumbura                       |           13 |
| Africa/Cairo                           |           14 |
etc...

You have to set the time_zone each time you connect to your database server

For example if you use the php Mysqli extension you can do this

$mysqli = mysqli_init();
mysqli_options($mysqli,MYSQLI_INIT_COMMAND,"SET time_zone = 'Africa/Brazzaville'" );
mysqli_real_connect($mysqli,$host, $user, $pass,$dbName) or die ('Unable to connect');

Otherwise just manually ( in terms of let your database connector do it ) execute the SET time_zone = '<YOUR_DESIRED_TIMEZONE>' Query right after you've connected to your database


Moving a large table from Localhost to remote server Postgres 11, Windows 10, pgAdmin 4

I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost).

I want to move this table to an AWS hosted server instance (also Postgres). The table has 2 columns, and id column and a geometry column.

Attempt 1: Using dblink, the transfer doesn't work:

CREATE TABLE destschema.dest_table AS
SELECT fid, wkb_geometry FROM dblink('host=localhost
                     user=postgres
                     password=password
                     dbname=sourcedb',
                     'select fid, wkb_geometry
                    from sourceschema.sourcetable') as linktable(
                    fid VARCHAR,
                    wkb_geometry geometry)

Error is: 'FATAL: password authentication failed for user "postgres"'

Even though the localhost connection credentials are correct. Why does it throw this error? I have previously had success using dblink to transfer tables from one remote cloud server to another remote cloud server. It just seems to not like moving a table from localhost to a remote server.

Attempt 2: using pg_dump to create a .backup file of the table (works ok), then upload it to the destination server using psql:

psql -U your_user_name your_db_name < your_dump_file

This method took 12 hours to insert 5million rows, so I cancelled the process. I don't want to have to wait 72 hours to move this table...

Attempt 3: Using PgAdmin's interface to backup the table to a .backup file and then restoring that .backup file to the destination database. However, after waiting 6 hours it was still uploading... I stop the process.

Attempt 4: Eventually I got around this issue by purchasing DataGrip (by Jetbrains) and exporting the 29mil rows to 3 CSV files, then importing the data back into the destination db. All the while using the DataGrip GUI. The exporting phase took around 8 mins in total, the uploading took 2.5 hours in total. Then I had to manually add a spatial index to the new table.

What is the quickest, simplest way of moving a large table from localhost to remote? Surely I've missed a trick or two...

CREATE TABLE destschema.dest_table AS
SELECT fid, wkb_geometry FROM dblink('host=localhost
                     user=postgres...

This statement could be used to transfer between databases on the same machine, but not across different machines. When you're logged to the AWS database, localhost designates the AWS host, not your PC. This is why the credentials of your PC don't work.

Copying the output of pg_dump to the remote, as a file and playing it there locally is probably the fastest way to achieve the transfer. Steps:

on the PC: pg_dump.exe -Fc [other options] dbname -f dumpfile
-Fc as an option selects the custom format, which has the advantage of compressing the dump.
transfer dumpfile to the remote AWS host, with a method of your choice. On Unix scp would probably be used: scp dumpfile username@remotehostname.amazonaws.com:. On Windows, it may also be available if it got installed, but otherwise any sftp client should work.
in a shell on the remote host, restore the dump: pg_restore dumpfile | psql [options] dbname.

If you only need to dump one table (as opposed to an entire database), see the -t option of pg_dump.

For the method executing SQL remotely (that is, on your PC, psql.exe -h remotehostname.amazonaws.com [other options] or the equivalent with pgAdmin connecting to the remote), note that compression is not used unless it happens at the network layer, and with 20GB to transfer, you really want to benefit from compression.


Primary key advice for simple table I'm making a sample database to test myself in MySQL workbench and was wondering the following:

I have five tables, Customer, Product, Stock(Quantity), Warehouse and Purchase Order.

For primary keys I have customerId, productId, warehouseId, and invoiceNo...but what would the stocks primary key be? Apologies if the answer is obvious, but as I understand it the foreign key would be the productNo.

Very new to this!

Any input would be greatly appreciated!

Given with STOCK the same product could be multiple warehouses, the combined (productCode, warehouseCode) is the unique element for this table and that should be the primary key.


Is ELSE 0 implied in my COUNT CASE WHEN statement? What's the difference between COUNT(CASE WHEN [Column A]  = ____ THEN 1 END
and COUNT(CASE WHEN [Column A]  = ____ THEN 1 ELSE 0 END?

I've been using the former and I haven't seen the difference thus far; what is the reason for adding the ELSE 0 - are there situations where SQL Server will incorrectly count?

Simply enough in the first case you are counting 1s & NULLs.  (NULL is returned if none of the conditions in the CASE statement match and there is no ELSE clause.) NULLs don't get counted.  In the second case 1's and 0's.  0's can be counted.

Quick example:

CREATE TABLE #CountMe (Col1 char(1));

INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');
INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');

SELECT
    COUNT(CASE WHEN Col1 = 'A' THEN 1 END) AS CountWithoutElse,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE NULL END) AS CountWithElseNull,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE 0 END) AS CountWithElseZero
FROM #CountMe;

Output:


Make a (copy of a) postgis database available to customer? Our application uses a PostgreSQL database (with the PostGIS spatial extension). Now one of our customers has requested direct access to the database; because, as they claim, it’s their data.

We’d rather not provide direct access to the database, because a wrong query might end up killing (or temporarily disabling) the database server, resulting in downtime for our application, or even a corrupt database.

Instead, we were thinking of setting up a slave database, and replicate the master. The slave would then only be used for read-only querying, so doesn’t need to write back to the master.

What is the easiest method of doing this with Postgres, while working with PostGIS as well? Or is a daily data dump easier to accomplish?

If it's one time setup, I think dump would be easiest. But if You need your update be available on customer, then I think streaming replication would be easier, set up once and no need to update in the future as the replication will handle the data updates.

http://www.postgresql.org/docs/current/static/high-availability.html


PostgreSQL 8.3 - issues with autovacuum I posted this on StackOverflow and it was suggested this query was better suited here.

I'm trying to encourage the use and monitoring of autovacuum in some PostgreSQL 8.3 databases.

One objection I hit often is that people don't "trust" autovacuum or there are bugs in autovacuum in 8.3 which mean that it's ignored in preference to scheduling vacuuming. Mostly our tables are small and this approach appears to work. However, with our larger (& also heavily updated tables) this really doesn't work (dead tuple counts increase, exceed max_fsm_pages, and the tables don't get cleaned up etc etc).

I'm just wondering if anyone has a reference for autovacuum in 8.3 being buggy or not working. My own experience has shown that autovac works fine and, where necessary, adding entries to the pg_autovacuum table does the trick.

I'd like to understand the problem with autovacuum (if one exists).

I can't find any evidence that 8.3 autovacuum is buggy. Autovacuum has been improved in 8.4 with free space maps which obsoletes some parameters which:

  (when set incorrectly) could make vacuum less effective

so with 8.3 it is important to set them correctly.
The postgres wiki says (and I'm inclined to agree):

  As of 8.3, autovacuum is turned on by default, and you should keep it that way.

and:

  The answer to almost all vacuuming problems is to vacuum more often,
  not less, so that each individual vacuum operation has less to clean up.

As you say, "adding entries to pg_autovacuum does the trick" when autovacuum needs to be tweaked - but just be aware that "pg_autovacuum [is] not saved in database dumps"


Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change? If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?
If I have a table where some of the rows will be updated, but I'm only selecting rows that are guaranteed not to change, is this also a completely safe scenario to use the NOLOCK hint?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?

In practice, yes (although not documented as such), but also useless.  If your data never changes, then using dirty reads won't cause any observable behavior or material performance difference.

  If I have a table where some of the rows will be updated, but I'm only selecting rows that are guaranteed not to change, is this also a completely safe scenario to use the NOLOCK hint?

No.  Absolutely not.  The query plan to read the target rows may read changing data structures without proper concurrency controls.  So you are back to hoping that your queries don't return bizarre, incorrect results or simply fail.


restarting postgresql quickly I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do to speed up restart: 1) issue a normal checkpoint before shutdown checkpoint, 2) flush all dirty shared_buffers to
disk, 3) record the contents of the database cache prior
to shutdown and then warm the cache again immediately after restart. Therefore, the book seems to recommend:

psql -c "CHECKPOINT"
psql -c "select pg_cache_save('mycache')"
pg_ctl -D datadir -m immediate restart
psql -c "select pg_cache_warm('mycache')"

It also showed this method to:

pg_ctl -D datadir restart -m fast

Which one should I use when restarting a busy database which gets inserts very often? One of the above or is there a better method?

If your clients cope well with being disconnected, and don't lose data, you should probably use the first process: Force a checkpoint, then perform an immediate restart. That'll get the server back up and running faster than a fast restart will, at the cost of greater disruption to currently active clients.

Client applications will get errors when they next run an SQL statement. Well written applications will throw away the database connection and retry, so the user won't notice/see anything.

Less well written applications will report an error to the user.

Truly badly written applications will silently lose data (if they swallow exceptions) or perform only part of an operation (if it isn't properly using transactions). These clients will also have problems under other circumstances, and must be fixed.


Semantic of SQL Query for summing up values I am new to SQL and not getting even simple things. I am wondering if the following code does not add a real column?

select *
sum(value) over (partition by prename order by NumberOfDays) SumValue
from (select *, row_number() over (order by NumberOfDays) rn from
tablenames) as t
order by rn

I thought with this code I would create a new column called SumValue. When I execute the code above it works fine and the column SumValue with all its values is shown but when I try to calcualte with this column, let's say:

ALTER TABLE tablenames
ADD AvValue float;
UPDATE [DatabaseXY].[dbo].[tablenames]
SET AvH = [SumValue]/[NumberOfDays]

SSMS tells me that SumValue is an invalid column name. Why is that? Even if I refresh or restart SSMS and look into the object explorer there is no column called SumValue..
I guess with my presented code I only create kinda temporary columns but how do I add those values to a real column?

Thank you so much!

No column is added to the table during the select. Adding columns to a table is usually restricted to a lot fewer people, than those that are allowed to select from a table. You can do this as:

ALTER TABLE tablenames
    ADD AvValue float;

MERGE INTO tablenames x
USING (
    select t.*
         , sum(value) over (partition by prename order by NumberOfDays) SumValue
    from tablenames t
) y
    ON x.<key> = y.<key>
WHEN MATCHED THEN
    UPDATE SET x.AvValue = y.SumValue/y.NumberOfDays

All untested since I don't know what the key for tablenames is.

You can probably calculate AvValue directly in the USING clause, but I'll leave that as an exercise.

EDIT: The implementation of MERGE in SQL-server seems to have several problems, see use-caution-with-sql-servers-merge-statement/ (link provided by Aaron Bertrand in comment. Using an update instead would be something like (still untested):

WITH y AS (
    SELECT t.*
     , SUM(value) OVER (PARTITION BY prename
                        ORDER BY NumberOfDays) SumValue
    FROM tablenames t
)
UPDATE y
SET y.AvValue = y.SumValue/y.NumberOfDays;


MSSQL: Generate a 8 digit serial/Barcode number from a prefix number I have a complicated task ahead of me and I really could use some of your brains to help me out.

At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix for an 8 digit barcode, were we currently manually calculate the last 2 digits of the barcode in Excel.

Here are the steps of how we manually calculate the last 2 digits:

Barcode prefix example:

declare @prefix int
set @prefix = 573071

1) Each prefix should be generated 10 times, where the 7th digit auto increments from 0 to 9

5730710
5730711
5730712
5730713
5730714
5730715
5730716
5730717
5730718
5730719

2) After adding the 7th digit, we use the following formula to calculate the 8th digit for each row:

First we start calculating the first row((5730710). Then we begin with calculating the first, third, fifth, seventh digit and multiply by 3 and add them together

5730710:
5 x 3
3 x 3
7 x 3
0 x 3

Then we take the second, forth, sixth digits and mutiply by 1 and add them together:

7 x 1
0 x 1
1 x 1

We add both results using SUM (45 + 7 = 53)
Then we round up 53 to nearest 10 (upwards only) in this case it has to be 60

DECLARE @num int = 53;
SELECT @num + (10 - (@num % 10)); = 60

After we round up we subtracts the round up value with the original value:
(60 - 53 = 7)

....And finally we have generated an 8 digit barcode from a 6 digit prefix:
    Result: 57307107

So my question is, how do i do this through sql?

Here is how I am calculating the 8th digit:

declare @prefix int
set @prefix = 5730710
Select
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)
+
(10 - ((
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)%10))

-
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)

But I'm still missing how to auto generate the 7th digit from 0 to 9 and put it into the above formula,
and lastly once the 7th and 8th digits are calculated I need them to be added to the prefix to finally
see the final result of 8 digit barcode.

DECLARE @prefix INT;
SET @prefix = 573071;
WITH
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 9)
SELECT num,
@prefix*100+num*10+9-((@prefix/100000+(@prefix/1000)%10+(@prefix/10)%10+num)*3+((@prefix/10000)%10+(@prefix/100)%10+@prefix%10)-1)%10 barcode
FROM cte1;

fiddle

The option without variable declaration:

WITH
cte0 AS (SELECT 573071 AS prefix),
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 9)
SELECT num,
prefix*100+num*10+9-((prefix/100000+(prefix/1000)%10+(prefix/10)%10+num)*3+((prefix/10000)%10+(prefix/100)%10+prefix%10)-1)%10 barcode
FROM cte0, cte1;


ORDER BY optimization for a large table I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest.

Consider a table posts

posts
{
    id  int(11),
    user_id  int(11),
    url varchar(256),
    image varchar(256),
    source int(11),  // Social Network Source
    created bigint(20), // Publish time of the post
    visible int(1) // Public or Private Posts
}

Using InnoDB

Indexes :

primary key on id
user_id,source
user_id,created

Space usage

Type    Usage
Data    45,876.0    MiB
Index   4,959.0 MiB
Total   50,835.0    MiB

Using Amazon RDS with 7.5 GB RAM.

The queries I am executing are as follows

select id
from posts
where user_id={user_id}
  and visible=1
order by created desc
  LIMIT 20 ;

select id
from posts
where user_id={user_id}
  and source={your_network}
  and visible=1
order by created desc
  LIMIT 20 ;

For users with > 500k posts sometimes my queries take a really long time.

I have run explain and you can see the results below

EXPLAIN SELECT id
FROM  `posts`
WHERE user_id =123529745 and source=1 and visible=1
ORDER BY created DESC
LIMIT 20

id    select_type       table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE          posts   ref     user_id_2,user_id_4,user_id_3,created   created 4       const   954174  Using where; Using index

id    select_type       table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE          posts   ref     user_id_2,user_id_4,user_id_3,created   created 4       const   742308  Using where;

Can you please suggest me any optimizations?

This is your query:

SELECT id
FROM  `posts`
WHERE user_id =123529745 and source=1 and visible=1
ORDER BY created DESC

You are doing only equality comparisons in the where and then sorting by created.

A single index on:  posts(user_id, source, visible, created, id) should allow the entire query to be satisfied only by the index -- first finding the right rows, then sorting by them, and then fetching the id.


How can I trace the root cause of a MySQL database table that crashed? Are there any standard practices or methods for tracing the cause of a MySQL table crash?

If I repair a table that had crashed, how do I determine what caused it to crash in the first place?

Storage Engine: MyISAM
MySQL Version: 5.6.38

I'm specifically interested in learning how to troubleshoot this common issue, not particularly in the methods to avoiding it.

The cause is almost always a non-graceful shutdown -- abort, power failure, etc.  So, I suggest you already know what is causing each occurrence.

It goes something like this...

When a MyISAM table is opened, it is flagged (in the .MYI file) that it is open.
In a graceful shutdown, the index blocks from the key_buffer are flushed (to the .MYI) and the the flag is cleared.  Almost always the index file is now intact.

So, if MySQL is aborted or there is a power failure or ..., the flag is still set.  When restarting the flag(s) is noticed.  Often there is no real damage, but you still need to do at least a REPAIR...quick.  But in some cases, the only real recovery is to rebuild the index(es).

For this (and many other reasons), MyISAM is deprecated and destined for the scrap heap.  Meanwhile, little or no effort is being put into MyISAM, especially by Oracle, which has it disabled in version 8.0.  (MyISAM is likely to live a little longer in MariaDB.)

Another nasty in MyISAM is with something like

UPDATE tbl SET x = x + 1;

and that is being applied to all rows.  If there is a crash in the middle of the execution of the UPDATE, some rows will be changed, some will not.  You have no way to know how far the update got before the crash.  The table is not corrupt, but the data is effectively corrupt.  There is no flag indicates this kind of corruption.

Another nasty:

UPDATE tbl SET str = CONCAT(str, 'xyz');

That is, increase the size of some column in some row.  This is likely to require two writes to the data (.MYD) because the row is now longer and won't fit where it had been.  If only one write gets to disk before a crase, then the data file is corrupted.  REPAIR may or may not be able to fix this, and is likely not to recover at least one row.

Etc.

Note:  Everything I have said in this Answer probably applies to all versions of MyISAM even back to before version 4.0.  MyISAM is antique technology that has not had much done to it in a loooong time.


The data don't fit your wanted resuLt, so i had to change the date
Schema (SQLite v3.30)
CREATE TABLE table1 (
  `ID` INTEGER,
  `Quantity` INTEGER,
  `DATE` DATE
);

INSERT INTO table1
  (`ID`, `Quantity`, `DATE`)
VALUES
  ('1', '40', '2020/10/10'),
  ('2',  '0', '2020/10/11'),
  ('3', '10', '2020/10/12');

Query #1
SELECT
*,
    Quantity - LAG ( Quantity, 1, 0 ) OVER (
        ORDER BY `DATE`
    ) PreviousYearTotal
    FROM table1;

| ID  | Quantity | DATE       | PreviousYearTotal |
| --- | -------- | ---------- | ----------------- |
| 1   | 40       | 2020/10/10 | 40                |
| 2   | 0        | 2020/10/11 | -40               |
| 3   | 10       | 2020/10/12 | 10                |

View on DB Fiddle


DDL trigger permission Error I have a two users, user1 and user2.

User1 has sysadmin rights and I have created a database level trigger through sa.

Now I am trying to create a table through user2 who has db-ddladmin rights and db_datareader.

I'm getting the error:

 'Msg 297, Level 16, State 1, Procedure TR_CaptureDBChanges, Line 35
The user does not have permission to perform this action.

Code:

CREATE  TRIGGER [TR_CaptureDBChanges]
ON DATABASE
FOR  CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TABLE, ALTER_TABLE,DROP_TABLE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_FUNCTION, ALTER_FUNCTION,
         DROP_FUNCTION
BEGIN
DECLARE @ed XML
SET @ed = EVENTDATA()
DECLARE    @ip VARCHAR(32) =   (SELECT client_net_address FROM sys.dm_exec_connections  WHERE session_id = @@SPID);

    INSERT INTO DBChangesLogs ( EventDate,DBName)VALUES(GetDate(),@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'))
END

Most likely the problem is that testuser does not have the VIEW SERVER STATE server-level permission. That permission (assigned to the Login in the [master] database, not to the User in the database with the DDL Trigger) is required to access the sys.dm_exec_connections DMV. So you need to do one of the following:

GRANT the testuser Login the VIEW SERVER STATE server-level permission, or

Create a Certificate-based Login that is then assigned the VIEW SERVER STATE server-level permission, and then sign the DDL Trigger with that Certificate using ADD SIGNATURE. The steps for this are provided in the follower answer:
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

From a security stand-point, I would recommend doing the little bit of extra work to handle the Certificate-based Login and signing the Trigger. This approach doesn't grant any actual Login / User any extra permissions, and will apply to any User that has the proper DDL permissions to perform the actions that would cause the DDL Trigger to fire. Also, assuming that testuser is merely a test case and that these permissions will need to apply more broadly in the final system, then you probably don't want to grant VIEW SERVER STATE to all of these Users or even to a Role.
Along these same lines, I thought that Ownership Chaining would take care of the INSERT permission on DBChangesLogs such that the permission did not have to be explicitly granted to any User(s). If this is not the case, then rather than granting this permission to one or more Users -- or certainly better than adding any User to the db_datawriter Role that grants access (and more than just INSERT) to all tables -- you can simply do two additional, simple steps beyond what is already outlined in that other answer to grant this Trigger the ability to insert into the DBChangesLogs table:

(start with the steps outlined in the "What minimum permissions..." answer linked above, the last part of it being to create the Certificate in the database where the DDL Trigger exists)
Create a User from the Certificate-based Login created in those prior steps (or from this same Certificate that is now also in this database -- the User created will be the same in either case)
GRANT this User the INSERT permission on the DBChangesLogs table

CREATE USER [MrDoStuff] FROM LOGIN [MrDoStuff];
GRANT INSERT ON dbo.DBChangesLogs TO [MrDoStuff];

PS: you declare and set the @ip variable. I assume you will use it once this permission issue is settled?


Create database on new partition I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package.

I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be entirely dedicated to this database).

My pgdata is located in var/lib/postgresql/9.1/main. I plan to stop the postgresql service, copy the pgdata content to the new partition, then make a symbolic link to the new partition, chown the new directory to postgres user, restart postgresql... but I'm afraid this all looks more like a hack.

Is there a way to create a database specifically on a specified partition ? Something more "canonical" (not a play of word with ubuntu)

CREATE TABLESPACE could be used.
create tablespace dedicated_datastore owner postgres location '/my/mount/point/'

and now the tablespace can be used as parameter when creating a database, (or a table, or an index)
create database my_database tablespace dedicated_datastore


Is this table-backed-queue implementation safe? We've a system where kinds of orders should be processed in batches, by advancing them from Status 0 to Status 1. There's a max number of a particular kind of order we can process in any one go, and it depends on the kind of the order, so we might be able to process 10 OrderKind 3's but only 7 OrderKind 5's.

Waiting to go in the orders table might be 20 of OrderKind 3, and 70 of OrderKind 5 etc. So in any one batching operation we only want to pick up max 10 of the OK 3's plus max 7 of the OK 5's, giving a batch of 17 rows total.

Here's the stored procedure that finds the oldest (according to when the order was approved) X orders, updates them to Status 1 and returns them to the front end app.

ALTER   procedure [ProcessPendingOrders]
as
begin transaction

declare @Processing table
(
    Id int,
    CustomerId uniqueidentifier,
    OrderControlId int,
    Destination nvarchar(256),
    Quantity int
);

update  Orders
set     StatusID = 1 --Being processed
output inserted.Id,
       inserted.CustomerID,
       inserted.OrderControlId,
       inserted.Destination,
       inserted.Quantity
into   @Processing
from    Orders o
where   o.Id in (   select  topN.Id
                    from    OrderControl oc
                    cross apply (   select  top(oc.OrdersPerBatch)
                                            oo.Id
                                    from    Orders o2
                                    join    OrderControl oc2 on o2.OrderControlId = oc2.Id
                                    where   o2.StatusID = 0       --waiting
                                    and     oc2.ProcessOrders = 1
                                    and     oc2.ID = oc.ID
                                    order by o2.ApprovalDate asc
                                ) topN
                )

if @@error <> 0
 begin
    rollback
    raiserror ('Error processing orders', 16, 1)
    return
 end

 select * from @Processing

commit

My question is; what would happen if two external processes were to execute this stored procedure concurrently? Is it possible that the same order rows would be given to each process? I've a concern it will because of the extra legwork doing a top N per kind of order. Is there anything that can be done to apply locks such that two different processes can't get the same set of rows out of this procedure?

Would the same problem afflict the earlier iteration of this code that didn't have top N?  It was literally just a single UPDATE orders SET status = 1 OUTPUT blah INTO @abc WHERE status = 0 query.

Check out the locking that occurs in your stored procedure - the following is a simple example, you can adapt it to test your query and others.  In case it needs to be clear, go to a quiet server for this, like a development enivironment or better yet, a local install.  The last thing you want to be doing is holding transactions open for giggles in a production environment.

USE tempdb;
GO

IF ( OBJECT_ID( 'dbo.TestTable', 'U' ) IS NULL )
BEGIN
    --DROP TABLE dbo.TestTable;
    CREATE TABLE dbo.TestTable
    (
        Foo                 INTEGER,
        BAR                 INTEGER
    );

    INSERT  INTO dbo.TestTable( Foo, Bar )
    SELECT  TOP 10 1, 1
    FROM    sys.objects;
END;
GO

IF ( OBJECT_ID( 'dbo.TestProcess', 'P' ) IS NULL )
BEGIN
    --DROP PROCEDURE dbo.TestProcess;
    EXEC( 'CREATE PROCEDURE dbo.TestProcess AS SET NOCOUNT OFF;' );
END;
GO

ALTER PROCEDURE dbo.TestProcess
AS BEGIN
    SET NOCOUNT ON;

    --SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    SELECT  isolation_level = CASE transaction_isolation_level
                WHEN 0 THEN 'Unspecified'
                WHEN 1 THEN 'Read Uncommitted'
                WHEN 2 THEN 'Read Committed'
                WHEN 3 THEN 'Repeatable'
                WHEN 4 THEN 'Serializable'
                WHEN 5 THEN 'Snapshot'
                ELSE 'UNKNOWN ( ' + CONVERT( NVARCHAR( 8 ),
                    transaction_isolation_level ) + ' )'
            END
    FROM    sys.dm_exec_requests
    WHERE   session_id = @@SPID;

    SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
            request_session_id, resource_type,
            request_mode, request_type, request_status
    FROM    sys.dm_tran_locks
    WHERE   request_session_id = @@SPID
        AND resource_type = 'OBJECT';

    UPDATE  dbo.TestTable
        SET Foo = 1
    OUTPUT  INSERTED.Foo, INSERTED.Bar
    WHERE   Bar = 1;

    SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
            request_session_id, resource_type,
            request_mode, request_type, request_status
    FROM    sys.dm_tran_locks
    WHERE   request_session_id = @@SPID
        AND resource_type = 'OBJECT';

    IF ( @@ERROR <> 0 )
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR( 'Rolled Back Update', 16, 1 ) WITH NOWAIT;
    END ELSE BEGIN
        COMMIT TRANSACTION;
    END;

    SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
            request_session_id, resource_type,
            request_mode, request_type, request_status
    FROM    sys.dm_tran_locks
    WHERE   request_session_id = @@SPID
        AND resource_type = 'OBJECT';

    SET NOCOUNT OFF;

    SELECT  isolation_level = CASE transaction_isolation_level
                WHEN 0 THEN 'Unspecified'
                WHEN 1 THEN 'Read Uncommitted'
                WHEN 2 THEN 'Read Committed'
                WHEN 3 THEN 'Repeatable'
                WHEN 4 THEN 'Serializable'
                WHEN 5 THEN 'Snapshot'
                ELSE 'UNKNOWN ( ' + CONVERT( NVARCHAR( 8 ),
                    transaction_isolation_level ) + ' )'
            END
    FROM    sys.dm_exec_requests
    WHERE   session_id = @@SPID;
END;
GO

With that set up, open a new session, which opens a transaction and runs the "guts" of the procedure:

USE tempdb;
GO

BEGIN TRANSACTION;

UPDATE  dbo.TestTable
    SET Foo = 1
WHERE   Bar = 1;

--COMMIT TRANSACTION;

Open another session and do the same thing, closing the transaction this time:

USE tempdb;
GO

BEGIN TRANSACTION;

UPDATE  dbo.TestTable
    SET Foo = 1
WHERE   Bar = 1;

COMMIT TRANSACTION;

This example will hang, as update locks are being held on the test table.  In yet another session, run the following:

USE tempdb;
GO

SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
        request_session_id, resource_type, request_mode,
        request_type, request_status
FROM    sys.dm_tran_locks
WHERE   resource_database_id = DB_ID()
    AND resource_type = 'OBJECT';

SELECT  session_id, wait_type, resource_description
FROM    sys.dm_os_waiting_tasks
WHERE   session_id IN ( SELECT  request_session_id
                        FROM    sys.dm_tran_locks
                        WHERE   resource_database_id = DB_ID()
                            AND resource_type = 'OBJECT' );

Here you will see that when the "guts" are run, transaction locks are granted to both the uncommitted session and the hanging session, but the hanging session is waiting for the modify update lock to be granted ( LCK_M_U ) while it waits for the original session to finish.  Go back to the first session and commit the transaction, then go ahead and run the procedure.

USE tempdb;
GO

EXECUTE dbo.TestProcess;

You should see that same intent-exclusive ( IX ) lock having been granted while the procedure runs.  This will lead to the same scenario as outlined above, where further transactions against the affected objects will "hang" while the active transactions complete / the procedure runs.

This is all using the default SET TRANSACTION ISOLATION LEVEL READ COMMITTED, well, transaction isolation level.  With the TOPs and CROSS APPLYs, it's potentially possible that a READ UNCOMMITTED or even a READ COMMITTED isolation level would allow concurrent transactions to effectively SELECT the keys about to be operated on before granting the LCK_M_Us, but in a single statement such as in your procedure, I would be somewhat surprised if that were the case.  If so, then a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; may be the best option to ensure asynchronous access to the objects.


"For each group of results fullfilling condition X, select only result with smallest column value A" I have a table with data and dates like so:

DECLARE @p TABLE (P_key int, P_Data char(1), P_ValidUntil datetime)

INSERT @p VALUES (20, 'T', '2003-02-28')
INSERT @p VALUES (21, 'U', '2005-05-31')
INSERT @p VALUES (30, 'V', '2006-09-30')
INSERT @p VALUES (30, 'W', '2008-04-30')
INSERT @p VALUES (31, 'X', '2007-06-30')
INSERT @p VALUES (32, 'Y', '2005-01-31')
INSERT @p VALUES (32, 'Z', '2007-06-30')
INSERT @p VALUES (33, 'A', '2005-06-30')

Given: qKey (only multiples of 10), qDate

Find: All entries that match qKey in the first digit and have a date larger then qDate. Return only one result per P_key (the one with the next higher P_ValidUntil to qDate).

The current solution is:

DECLARE @qKey AS int;
DECLARE @qDate AS datetime;

SET @qKey=30;
SET @qDate='2006-01-01';

SELECT * FROM @p WHERE
@qKey = (P_Key/10)*10
AND @qDate <= P_ValidUntil

which returns:

30    V    2006-09-30 00:00:00.000
30    W    2008-04-30 00:00:00.000
31    X    2007-06-30 00:00:00.000
32    Z    2007-06-30 00:00:00.000

This is basically correct, except that there are two entries for 30 (both are bigger than @qDate). I only want the smallest date of these multiple hits in the result:

30    V    2006-09-30 00:00:00.000
31    X    2007-06-30 00:00:00.000
32    Z    2007-06-30 00:00:00.000

Thanks in advance!

A first fix would be:

SELECT p2.*
from @p p2
JOIN (
    SELECT min(p_validUntil) Min_P_ValidUntil, P_Key
    FROM @p p
    WHERE @qKey = (P_Key/10)*10
    AND @qDate <= P_ValidUntil
    group BY P_Key
) p3 ON p3.Min_P_ValidUntil = p2.P_ValidUntil AND p3.P_Key = p2.P_Key

thanks.


Best way to move functions from postgresql to another I'm not familiar with postgres but I'm sure there has to be a way to source control its functions.
Currently my team adds functions on a postgresql database directly, and when it's time to deploy to production they just move the functions manually, so it's natural to forget to move certain functions.

Is there a tool similar to git that would allow them to branch out, merge and deploy?

You can use a custom PostgreSQL extension created with CREATE EXTENSION.

Put all of your extension code in a build system and have your developers work on that. Then you can install the extensions easily and version control them, roll them back, upgrade them, etc.

Here is a tutorial to get started.


Mongodb - save image as bytes or as files I'm developing a website which allows the user to upload an image per item. I guess there will be hundreds per user.

What is more preferred?

Save the images:

as bytes in the DB (mongodb)
as files in the server

I don`t really know where to place this question in the "Stack Exchange" forums.

MongoDB will happily store binary data such as files, using its GridFS system. That way you can keep the images and other user data together in one place, which is an advantage over storing the images on the file server.


Splitting array of string values into Rows Numbers Table Ref Error I am trying to create a table that queries individual database records, but splits out values from an associated array into individual rows for the individual database record. So far I have created the numbers table that has numerical rows up to 255, but when I try to join that table to a table where I am splitting the array by string in the number table order, I receive an error function split_part(character varying, "unknown", double precision) does not exist. Any idea what is off with my setup?

Note: I am using Amazon Redshift

Numbers Table:

Sample query results

number
1
2
3
4
...
255

Query

SELECT
    p0.n
    + p1.n*2
    + p2.n * POWER(2,2)
    + p3.n * POWER(2,3)
    + p4.n * POWER(2,4)
    + p5.n * POWER(2,5)
    + p6.n * POWER(2,6)
    + p7.n * POWER(2,7)
    as number
  FROM
    (SELECT 0 as n UNION SELECT 1) p0,
    (SELECT 0 as n UNION SELECT 1) p1,
    (SELECT 0 as n UNION SELECT 1) p2,
    (SELECT 0 as n UNION SELECT 1) p3,
    (SELECT 0 as n UNION SELECT 1) p4,
    (SELECT 0 as n UNION SELECT 1) p5,
    (SELECT 0 as n UNION SELECT 1) p6,
    (SELECT 0 as n UNION SELECT 1) p7

Parser table query:

Ideal query results

row_id, record_id, medium_name
1, 24, work
2, 24, test
3, 24, success

Query

SELECT row_number() over (order by 1) as row_id
        , test_lead.id as lead_id
        , split_part(test_lead.test_mediums, ', ', numbers.number) as medium_name
      FROM ${lead.SQL_TABLE_NAME} test_lead
      JOIN ${marketing_model_numbers.SQL_TABLE_NAME} numbers
      ON numbers.number <= regexp_count(test_lead.test_mediums, ',\\s') + 1

Array value structure:

["work", "test", "success"]

Your numbers.number is a double precision. But split_part() expects the third parameter to be an integer and the value isn't implicitly cast. Try to explicitly cast numbers.number.

...
, split_part(test_lead.test_mediums, ', ', numbers.number::integer) as medium_name
....


Creating dump file with use command I am taking backup of 2 tables from different schema.
I need to create the "use dbname" statement at the beginning of each table.

mysqldump --databases company --tables loadme --databases new_company --tables new_load

use company
...
usual loadme dump
...

use new_company
...
usual new_load dump
...

This does not seem to be possible with mysqldump command.

The mysqldump utility can dump the following ways from a MySQL Instance:

All Databases
One Whole Database
Part of a Database

It is possible to mysqldump a space-separated list of tables from one Database

One Whole Table

It is not possible to mysqldump a table from one Database and a table from another

Part of a Table Using --where Option
Stored Procedures
Schema Only
Data Only

You cannot mysqldump two tables from two different schema. You must script the tables you want dumps one at a time.

Here is a script to dump every table from a MySQL Instance into separate files

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat /tmp/ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump ${MYSQL_CONN} --hex-blob --triggers ${DB} ${TB} > ${DB}_${TB}.sql &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

In your case, you can create the /tmp/ListOfTables.txt file manually. In order to inject use dbname, just create the dump file with the use dbname as the first line. Then, append the output of mysqldump to it. Keep in mind that each table is described as dbname.tablename:

rm -f /tmp/ListOfTables.txt
echo "company.loadme" >> /tmp/ListOfTables.txt
echo "new_company.newload" >> /tmp/ListOfTables.txt
MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat /tmp/ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    echo "use ${DB}" > ${DB}_${TB}.sql
    mysqldump ${MYSQL_CONN} --hex-blob --triggers ${DB} ${TB} >> ${DB}_${TB}.sql &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Give it a Try !!!


Grouping and Row limit in SSRS I want to have row limitation(for example, display only 10 rows in every page) to my table in the report. I could limit the rows with Ceiling method where I could find refer. However, I have to group based on two criteria before using row limit. One is I want to group by category by category name, after that group by based on Region. And have my row limit after that. How could I achieve this? And I want to do all of these in report level. Kindly share any insight on that matter.

Thanks.

As you said you want to do this at report level here is what i can think of:
You can try reducing the value in Height section in Interacivesize property of your report according to your needs,which is set to 11in by default and shows whatever records can fit in that much space :

Note: In case you have a small database and want to show all the values in 1 page itself setting the Height value to 0 will do the trick for you. Hope this helps.


How to find what prevented in getting a lock? Sometimes my update statements fail after 3000 milliseconds with 'Lock request time out period exceeded' error. That 3000 limit is coming from my application through SET LOCK_TIMEOUT 3000 setting, which I guess is not a problem by itself.

My question is: How do I retrieve what else had already locked the same row or table that made it unavailable to this failed update?

I am able to see all the exceptions related to Error: 1222 through traces but it is not clear to me what made it fail.

Is there a way to retrieve that or do I only have to continuously watch through Activity Monitor? Since it doesn't deadlock, everything happens pretty quick there and hard to see it live.

I need to find that by using the existing traces and any information that sql server 2008 R2 keeps.

Please help.

I recommend zespri's approach. Use profiler to find the query that is taking an inordinate amount of time.

If you've somehow had no joy with that, you can try the following query which will show a process with a particularly large waittime value.

It filters out any system processes and SQL which has contains WAITFOR DELAY '00:00:05' ... etc

SELECT *
FROM master.dbo.sysprocesses
WHERE spid > 49
AND (blocked <> 0 OR (waittype <> 0x0000 AND lastwaittype <> 'WAITFOR'))

You can then dig a little deeper with the spid against the row  that looks like it is causing the problem and inspect the SQL code that is running/locking:

DECLARE @Handle varbinary(50), @Spid int = <your spid value/>
SELECT @Handle = sql_handle from master.dbo.sysprocesses where spid = @spid
DBCC INPUTBUFFER (@spid)
SELECT * FROM master.sys.fn_get_sql(@Handle)

And the resulting output will look like this:

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------------------
Language Event 0          SELECT GETDATE()

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbid   objectid    number encrypted text
------ ----------- ------ --------- ---------------------------------------------------
13     NULL        NULL   0         SELECT GETDATE()

This should help you narrow down the problem query.


SQL Server - Migration script testing - Refresh table definition manually without dropping it How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table?

This is normally handled by the publish process (e.g. SSDT), but during testing of schema alterations and their migration scripts, I need to revert the table definitions, and the managed rebuild/publish process through Visual Studio (and getting specific versions from TFS) takes quite a long time.

For example, if I have this new version of the table after updating the schema

CREATE TABLE [dbo].[MyAssociativeTable]
(
    [SurrogateID]   INT NOT NULL IDENTITY(1, 1)
    ,[ForeignID1]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable1]([ID])
    ,[Flag1]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag1] DEFAULT(0)
    ,[ForeignID2]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable2]([ID])
    ,[Flag2]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag2] DEFAULT(0)
    ,[DataColumn1]  REAL NULL CONSTRAINT [CK_MyAssociativeTable_Data1] CHECK(ISNULL([DataColumn1], 0) >= 0)
    ,[ExtraColumn1] VARCHAR(MAX) NULL
    ,CONSTRAINT [PK_MyAssociativeTable] PRIMARY KEY
    (
        [ForeignKey1]
        ,[ForeignKey2]
    )
    ,CONSTRAINT [CK_MyAssociativeTable_Case1] CHECK
    (
        [Flag1] = 0
        OR [Flag2] = 0
    )
)
GO
CREATE UNIQUE INDEX [AK_MyAssociativeTable_F1]
ON [dbo].[MyAssociativeTable]([ForeignID1])
WHERE [Flag1] = 1

and I want to revert it to the original version (removing ExtraColumn1 is ok) so I can insert invalid rows to test migration scripting on

CREATE TABLE [dbo].[MyAssociativeTable]
(
    [SurrogateID]   INT IDENTITY(1, 1)
    ,[ForeignID1]   INT
    ,[ForeignID2]   INT
    ,[Flag1]        BIT
    ,[Flag2]        BIT
    ,[DataColumn1]  REAL
    ,PRIMARY KEY([SurrogateID])
)

what are my options?

Note that columns are also being re-ordered. Handling this would be nice, but is unimportant.

One approach that works (only if there are no foreign keys referring to the table) is to create a backup of the original table with its original data.

Before starting to test the migration:

Grab a copy of the old table definition to define a copy of it, with a unique name (e.g. append "_bak" to the table name) and all named constraints (using sp_rename on just the table name will not automatically update named constrains).
SET IDENTITY_INSERT [bak] ON
Write a simple INSERT INTO [bak] (\every column\) SELECT \every column\ FROM [original] (the column list must be specified, otherwise you will get error 8101 for not explicitly writing out the identity column (why?))
SET IDENTITY_INSERT [bak] OFF

Note 1: Make sure to not enable the advanced SSDT publish option "drop objects in target not in source" or this table will get dropped if you publish.

Note 2: Computed columns must not be included in the \every column\ list.

Then each time you want to revert the table definition and populate it with the preexisting records:

DROP TABLE [target]
Copy the original table definition and run that CREATE TABLE ... code
SET IDENTITY_INSERT [target] ON
INSERT INTO [target](\every column\) SELECT \every column\ FROM [bak]
SET IDENTITY_INSERT [target] OFF

This is still far too manual of a process however (and breaks on foreign keys).


Linux kernel parameters for oracle I wonder what are the optimal Linux kernel parameters for oracle 11g db on 64-bit system.

I didn't find any detailed, good or full recommendations or best practices for this issue. Just found one recommendation that on 64-bit system it's good to set shmax half of the RAM.

The installation guide contains minimum (or recommended) values for each kernel parameter. On Metalink you can find at least one article (including formula) per each kernel parameter.


How to share resources among accounts? Using google doc, I am able to share my documents with others. I wonder how they implement it underlying in terms of DB design?

The simplest way I imagine is to use a joining table which keeps a many-to-many relationship between resource and accounts to share.

However, I wonder if there is any well-known pattern for this? If the account has hundred resources, then hundred joining tables seem not a scale way, as for each resource you have to write specific code. Is there any thing like RBAC(role based access control) for this sharing problem?

I don't have any insider knowledge of how Google Docs is implemented.  However, if you look at things like Windows file permissions, this type of thing usually works on the these principles:

Inheritance - To grant access to many objects with the same scope, grant the access at the highest level (e.g. folder, not file) and all children of the granted node get the same permission as the parent.
Group and Individual Privilege - Rights can be granted to groups and to individuals.  Individuals can belong to multiple groups and you might be in more than one that has access.  Alternatively, access can also be granted to an individual.
Explicit Grant - Assume access is denied unless there is an explicit grant in place.  The explicit grant can be to the individual or to one or more groups that the individual is a part of.  The grant can be to the specific resource or the resource can inherit the grant from an ancestor.  However, no access is allowed unless there is a qualifying grant somehow.

By applying these three principles you can manage the amount of data that must be maintained to control access to resources.

EDIT:

See my answer to a similar question over on Stack Overflow.  The basic idea is to map resources and principals into hierarchies or networks.

The resources (documents) will probably fit neatly in an un-level hierarchy of directories and documents.  How you model the actor (user/group) depends on your business rules.  Generally groups are one level deep, but users can be in multiple groups.  This means you will probably want a sub-typing approach to principals such that they can be users or groups.  You will have an intersection table (m:n) between principals to indicate membership.

To make the traversal of the resource hierarchy more efficient, you might consider one of the common relational hierarchy tricks.  My usual favourite is "visitation numbers".  See also my answer to this question for more about working with visitation numbers.

Consider the following (mostly logical) ERD:

Using this type of model, the query to find out if a user has access to a given document is a matter of joining Membership to Resource through Grant where the matched left and right are greater than or equal to the left and right of the resource in question.  This gives you the answer based on group membership.  To get direct individual grants you would do the same thing but leave out Membership and just use the ID from Individual to filter the Grants.


postgresql bigserial or sequence using jpa I'm a java developer working with jpa, before to define the indexes in my entities i would like to known  which one is faster o better to use?.

bigserial is just shorthand for creating a sequence. So if you include the time it takes to type, bigserial has better "performance" :)

CREATE TABLE tablename (
    colname BIGSERIAL
);

Equals

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename (
    colname biginteger DEFAULT nextval('tablename_colname_seq') NOT NULL
);

Also, if you these are your primary keys, then they are already indexed, so you don't need to index them (foreign key columns aren't indexed automatically though, FYI).

For JPA, you should use @GeneratedValue(strategy=GenerationType.IDENTITY) for PostgreSQL serial/bigserial columns.


What is the difference between "OPTION (OPTIMIZE FOR UNKNOWN)" and "OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))" Can you tell me what is difference between OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and OPTION (OPTIMIZE FOR UNKNOWN)?

The documentation on query hints says

DISABLE_PARAMETER_SNIFFING ... is equivalent to trace flag 4136.

That trace flag was documented in KB980653 which tells us

Enabling trace flag 4136 disables parameter sniffing, which is equivalent to adding an OPTIMIZE FOR UNKNOWN hint to each query which references a parameter.

The documentation on supported trace flags says about 4136

... to accomplish this at the database level, see the PARAMETER_SNIFFING option in ALTER DATABASE SCOPED CONFIGURATION

To accomplish the same result at the query level, add the OPTIMIZE FOR UNKNOWN query hint. The OPTIMIZE FOR UNKNOWN hint doesn't disable the parameter sniffing mechanism, but effectively bypasses it to achieve the same intended result.

.. a second option to accomplish this at the query level is to add the USE HINT 'DISABLE_PARAMETER_SNIFFING' query hint instead of using this trace flag.

It seems to me that these are all a single feature, wrapped in several syntactic formulations, to be applied at different granularity. It can be applied on the instance, a database, the session, a stored procedure, a query or a single parameter within that query. They all do the same thing, just use a different form of words to achieve it. This opinion is supported by the steps to apply when addressing sniffing issues which uses OPTIMIZE FOR and DISABLE.. in turn.
Some of the duplication can be explained in the general move from opaque trace flags to developer-friendly transparent hints. I find "disable sniffing" easier to reason about than "optimize for unknown".
I suppose we may never know if there is any actual code-path difference internally, unless someone with source code access happens to becoming interested in answering. Practically, I don't believe there is.

I looked at the plans produced for very simple queries
DBCC TRACEON(3604);
dbcc freeproccache;
declare @c int = 1;

select * from dbo.Skewed where c = @c
OPTION (QUERYTRACEON 8605, USE HINT('DISABLE_PARAMETER_SNIFFING'));
-- OPTION (QUERYTRACEON 8605, OPTIMIZE FOR UNKNOWN);

The query plans for both hints are indistinguishable. Both the actual execution plan and the bound tree (trace flag 8605) are the same.


Unexplained sort operation in execution plan I am running the following query on a database with 7.5 million records:

UPDATE TestTable
SET TestCol='1234', TestCol2='1234', TestCol3='1234', Anonymised=1
WHERE [Date] >= '25 June 2016'
AND [Date] <= '25 August 2016'

For some reason the execution plan includes a sort operation on the primary key.

I wouldn't expect it to have to sort the primary key given that there is already a clustered index on that column.

Can anyone shed some light on why this is happening and what I might do to avoid the sort operation?

Edit:

Table creation script

CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestCol] [nvarchar](50) NOT NULL,
    [TestCol2] [nvarchar](50) NOT NULL,
    [TestCol3] [nvarchar](50) NOT NULL,
    [Anonymised] [tinyint] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Your plan is using a nonclustered index keyed first on, presumably, Date to find values that qualify for your WHERE clause.

In order to optimize IO access of the clustered index update, data coming out of that index access operation is sorted to match key order of the PK/CX.

As an aside, you shouldn't be updating that many rows in one go.