MySQL Workbench team strategy I'm considering to use MySQL Workbench at work, mainly because of the modelling tools and to easily sync the development and the production DBs. I work with another developer on the same project, and we currently use GIT to version our work. In a perfect world it would be possible just to merge different changes in the models directly within GIT, but it's not possible, since the Workbench files are binary, so now I'm looking for a team strategy to work together with Workbench, share and update the same files and still be able to work on different features.

I also tried to extract the .mwb content to version the .xml inside it, but when it comes to merging it's very difficult to edit the XML involved without breaking/corrupting it.

Any suggestion? How did you solve this problem in your team?

We keep an .mwb file in the repo. Our team is small and changes to the schema are rare, so we can agree who modifies the file.

If the team were bigger I would introduce a lock file in the repo that's created by a person who wants to make a change and who deletes it when the change is committed.


How to create a new user in a remote offline database without elevating roles An enterprise C++ system that needs to be "translated", uses ODBC for connecting to SQL Server Database.

The system has one central main database, many distributed local databases, which are actually offline and are periodically kept updated with the central database by exchanging merging xml files, something like a star topology. The distributed databases are stored on many different OS and different local domains.

Each user has been registered in the SQL Server and with certain user roles on the database. The number of login users at the database were kept fixed.

My question is how the remote new user to create/update his own profile and/or credentials without the need to elevate his rights.

Use a signed stored procedure. The right to alter logins is encapsulated within the SP. The users only have rights to execute the SP, not amend logins directly. You may need several SPs to cover different aspects for administration.


SQL 2012 Replication Between Subscribers Across VPN I have been assigned with setting up replication for an instance(remote instance) at a geo location that we access across a VPN. The remote instance will need to send the transactions to a local subscription/distributor (subscription 1) and then the transactions from that subscription will need to be pulled/pushed to an on-premise instance (subscription 2).

I am having issues figuring out the steps for setting up the configuration from subscription 1 to subscription 2. Input is greatly appreciated.

This has been resolved as the replication will be scripted using stored procedures from the publisher (remote instance) to the subscriber (local instance) using a sql account setup for the subscription's linked connection to the remote instance.

The VPN is dedicated so there no major concerns with the VPN.

The publisher will use push method for the local subscription. Then from the local subscription a replication using the subscription (sub 1) will be pulled to the second local subscription(sub 2) that is read only.


MYSQL binary log file not capturing in slave machine mysql binary log files are not getting captured in slave machine, its available in master machine. When i check the binary log files its of size 1kb without any data in slave.

If you are not writing directly on your slave, no binary logs will get generated by default.

To capture the binary log events from the master into the slave's binary log, you need two things:

enable log-bin
enable log_slave_updates


Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time.  I am trying to get an Excel type report out of the DB using SQL Query to combine tables to give me all forms and members connected to said form. Our Member table holds all the customer data but only has one column (UID) that has the same data on other tables with different column names. I have used the query below to fix this mismatch:

SELECT *
FROM dbo.Answer a
INNER JOIN dbo.tasklist_data tld ON a.source_taskID = tld.TaskID
INNER JOIN dbo.Member m ON tld.createdby = m.uid
INNER JOIN dbo.Form f ON a.FormID = f.FormID
INNER JOIN dbo.Element_Answer ea ON a.ID = ea.AnswerID
INNER JOIN dbo.Form_Elements fe ON ea.ElementID = fe.ElementID

The problem I am having is one table (Element_Answer) has a column called Element_answer that holds the data of what was picked when the form was filled out.  The data in this column is mainly integers ranging from 1 to 9 which link to another table (Form_Element_groups) that has the same number data in a column called sort_order. The ea.Element_Answer column also has text data (*%text%*75193) that represents another table (Element_answer_text) that holds the actual text that was written on the form.  The number next to the %text% represents the ID on the Element_Answer_text table.

I am trying to combine the two tables(ea and feg).  When I try to INNER JOIN tables Element_Answer to Form_Element_Groups using the ea.Element_Answer and feg.Sort_order columns as the common, it gives me the above error.

My desired output would be to be able to combine these two tables, so that I can see all my data, possibly ignoring all the %test% entries or possibly having the actual text from the Element_answer_text table replace the %text% entries.

I apologize in advance if I gave too much information. The tables in question are the Element_Answer table, which holds 5 columns (ID, AnswerID, ElementID, Element_answer, and status).  The Element_answer column is important because it holds the integer data that I need that links to the other table called Form_Element_Groups that has 7 columns (ID, elementID, option_span, sort_order, group_title, group_text, and category). The sort_order column is the column I am trying to match up with the element_answer column because the data that matches up on these two columns will give me the desired result of only what I want to see from the feg table's group_title column. The ea.Element_answer column also has text entries that link to the Element_Answer_Text table that has two columns (ID, and Element_Answer_text). The eat.element_answer_text column holds the actual text.

If I can get a query that ignores the text in the ea.Element_answer column and only gives me my integer data that matches up, that would be great.  If I can get a query that can not only give me my integer data that matches, but the actual text from the Element_Answer_text table, that would be even greater.

I can't figure out how to match the text data in the ea.Element_answer column with the actual text located on eat.Element_answer_text table/column because it has this weird *%text%*4 data within the ea.Element_answer column referring to the ID on the eat table. (I just picked 4 as an example, there are many other *%text% entries in that ea.Element_answer column that start with *%text%*4 and end with *%text%*95042.)

The problem is that Element_Answer.Element_Answer column is referencing two distinct tables. The weird *%text%* prefix in some of the rows is apparently there just to tell whether the value references one table or the other. Nevertheless, the fact that two different tables are referenced from a single column is a violation of any normalisation rules.

So, I strongly believe that the best solution to this would include a redesign of the Element_Answer table, so as to split its Element_Answer column into two: one referencing Form_Element_Groups.sort_order and the other referencing Element_Answer_text.ID. Both columns would be numeric as they both are essentially just references to numeric IDs.

For the purposes of the scripts below, I am calling the column referencing feg.sort_order by the same name, sort_order, for consistency, and the other reference is called Element_Answer_text_ID. Feel free to replace the names with whatever you consider more appropriate.

So, first you create two new integer columns in Element_Answer:

ALTER TABLE dbo.Element_Answer
ADD sort_order int NULL
    CONSTRAINT FK_ElementAnswer_FEG
    FOREIGN KEY REFERENCES dbo.Form_Element_Groups (sort_order);

ALTER TABLE dbo.Element_Answer
ADD Element_Answer_text_ID int NULL
    CONSTRAINT FK_ElementAnswer_EAT
    FOREIGN KEY REFERENCES dbo.Element_Answer_text (ID);

Note: the above assumes that both feg.sort_order and eat.ID are either declared as PKs or have a UNIQUE constraint defined on them. Otherwise they cannot be referenced. You can still store references without a formally defined relationship between tables, but you cannot have guaranteed data integrity that way, so I recommend you make sure you can declare the new columns as foreign keys.

The next step would be to populate the new columns from the current ea.Element_Answer one. That can be done in one go with an UPDATE statement like this:

UPDATE
  dbo.ElementAnswer
SET
  sort_order = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN NULL ELSE Element_Answer END,
  Element_Answer_text_ID = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN SUBSTRING(Element_Answer, 9, 999999) END
;

Now that the references to different tables sit in different columns, you can drop the original:

ALTER TABLE dbo.Element_Answer
DROP COLUMN Element_Answer;

To retrieve data from Form_Element_Groups and Element_Answer_text, you can complete your query with outer joins to those tables in this manner:

SELECT
  ...
  feg.group_title,
  eat.Element_Answer_text,
  ...
FROM
  ...
  LEFT JOIN dbo.Form_Element_Groups feg ON ea.sort_order = feg.sort_order
  LEFT JOIN dbo.Element_Answer_text eat ON ea.Element_Answer_text_ID = eat.ID
;


View permission to SQL Server logs on SSMS GUI I would like to give a user see SQL Server logs from GUI, not through xp_readerrorlog. I was just wondering if this was possible?

Thank you.

Yes, this is possible by creating a user with 'securityadmin' server role and then if required Deny his access to SQL Server Query Window through LOGON Trigger

USE [master]
        GO

    ----Create Login
    CREATE LOGIN [DBA_ErrorLogUser]
    WITH PASSWORD=N'123', DEFAULT_DATABASE=[master],
    CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    --- Add server Add securityadmin
    --  To view SQL Server error logs user must be a part of securityadmin server role
    EXEC master..sp_addsrvrolemember
    @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
    GO

    --- Mapp user with login to grant access to logs
    CREATE USER [DBA_ErrorLogUser] FOR LOGIN [DBA_ErrorLogUser]
    GO

    --- Deny Alter to any Login
    DENY ALTER ANY LOGIN TO DBA_ErrorLogUser
    GO

    --- Grant permission to view Sql Server Logs
    Grant EXECUTE ON master.sys.xp_readerrorlog TO DBA_ErrorLogUser
    GO

    --- Create a log on Trigger to deny access to Query Window
    IF EXISTS ( SELECT  *
                FROM    master.sys.server_triggers
                WHERE   parent_class_desc = 'SERVER'
                        AND name = N'Deny_QueryWindowLogin_Trigger' )
        DROP TRIGGER [Deny_QueryWindowLogin_Trigger] ON ALL SERVER
    GO

    Create TRIGGER Deny_QueryWindowLogin_Trigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

    DECLARE @data XML
    SET @data = EVENTDATA()

    DECLARE @AppName SYSNAME,
            @LoginName SYSNAME

    SELECT  @AppName = [program_name]
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

    SELECT  @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

    IF @AppName= 'Microsoft SQL Server Management Studio - Query' AND @LoginName = 'DBA_ErrorLogUser'
        BEGIN
            ROLLBACK ; --Disconnect the session

        END
END ;

Note: Must Read securityadmin Server Role Permissions


Encrypt/Decrypt output & input for SSIS task I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system.  I have a new requirement to encrypt the CSV text files that are generated by the SSIS package, which means I will also need to decrypt those text files when importing into the other system.

This encrypting/decrypting could be done using some sort of batch processing outside of SSIS, but it seems like it would be more natural to do this all within SSIS.  What's the best way to do this?

Encrypting files through SSIS is possible, but there is no real easy native solution available with SSIS. There are a wealth of third party productions out there than can assist with this. Time is money, so it all depends on how much you want to spend on trying to come up with a customized solution or just go buy something that can help you do it quicker.

There is a good write up on this blog post that walks thorugh doing it with some custom .NET code and a ScriptTask. He also list out some of the third party products that offer a solution as well.


Dealing with CXPACKET waits - setting cost threshold for parallelism As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits.

I know the knee-jerk solution is to turn off all parallelism by setting MAXDOP to 1 - sounds like a bad idea. But another idea is to increase the cost threshold before parallelism kicks in. The default of 5 for the cost of an execution plan is fairly low.

So I was wondering if there's a query out there already written that would find me the queries with the highest execution plan cost (I know you can find those with the highest duration of execution and so on - but is the execution plan cost retrievable somewhere, too?) and that would also tell me if such a query has been executed in parallel.

Does anyone have such a script at hand, or can point me in the direction of the relevant DMV, DMF or other system catalog views to find this out?

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of work, which is why it is such a prevalent "fix."

If you can get an actual plan for a query that seems to be responsible for high CXPACKET waits, load it into SentryOne Plan Explorer. There's usually a reason behind this; we show which parallel operations led to thread skew, and you can easily correlate that to estimates that are off (we highlight operations with estimates that are off by a at least certain threshold). Usually the underlying problem is really bad/out-of-date (or unavailable) statistics.

Unfortunately what you'll find in sys.dm_exec_cached_plans are estimated plans. They won't tell you whether the plan went parallel when it was actually used, because the actual plan is not what's cached. In some cases you expect to see both a serial and parallel plan for the same query; this is not how SQL Server deals with the situation for parallel plans that might be parallel at runtime. (Lots of information about that here.)


Incorrect syntax near ')' when executing multiple select statement Incorrect syntax near ')'

How do I execute only one select statement based on user input i.e @id. Here is my syntax.

Create Procedure employee
@id int,
@year int
AS

SELECT CASE WHEN @id = 1 THEN

(select a.name, b.roles from employee a
INNER JOIN department b ON a.id = b.id
where b.years IN (@year)
OR b.roles IS NOT NULL  )

ELSE CASE WHEN @id = 2 THEN

(select a.name, b.dayoff from employee a
INNER JOIN department b ON a.id = b.id
where b.years IN (@year)
OR b.dayoff IS NOT NULL  )

One important thing to consider: CASE in T-SQL is an expression that returns a value, not control-of-flow logic like you might find in other languages (e.g. VB treats it like a switch()). In SQL Server, it can't be used this way; you can't say:

CASE WHEN something THEN
  -- run some query
ELSE
  -- run some different query

All output from the CASE expression must happen at the same scope and within the same query. I've written quite a lengthy article about some of the more common misconceptions about CASE here:

Dirty Secrets of the CASE Expression

In your scenario, if these are the only two potential outcomes, then you can probably follow @billspat's advice and just move the location of your CASE expression into the WHERE clause of a single query.

If your actual scenario is more complex (like you have 20 or 60 or 7000 different @id values that can lead to different forms of that WHERE clause, dynamic SQL might be a better solution (SQL Server will have a very hard time coming up with a single execution plan that is optimal for all variations of the query). Something like this:

CREATE PROCEDURE dbo.employee -- this is an odd name for a procedure
    @id   int,
    @year int
AS
BEGIN
  SET NOCOUNT ON; -- should always use this

  DECLARE @sql NVARCHAR(MAX) = N'SELECT e.name, d.roles, d.dayoff
    FROM dbo.employee AS e -- use logical aliases! What sense do a/b make?
    INNER JOIN dbo.department AS d
    ON e.id = d.id -- this join condition makes little sense
    WHERE d.years IN (@year)';

  IF @id = 1 THEN
    SET @sql = @sql + N' OR d.roles IS NOT NULL';
  IF @id = 2 THEN
    SET @sql = @sql + N' OR d.dayoff IS NOT NULL';

  EXEC sys.sp_executesql @sql, N'@year int', @year;
END
GO

I talk about this in a little more detail here:

The "kitchen sink" procedure

You could add logic to also dictate which output columns to show, but the application layer should be able to ignore output columns based on the @id value it passed in.

And please, always use the schema prefix.


Postgres Aurora RDS password auth fails except with master user I can successfully connect to a Postgres Aurora server using the master password thru the SSH tunnel on the bastion host I have setup.

However, when I create any other users, I can't connect with them even tho all other things are the same configuration wise. I get a DB level auth error.

password authentication failed for user "SomeUser"

I've added the users using these queries.

CREATE USER SomeUser LOGIN ENCRYPTED PASSWORD 'SoMePaSsWoRd';
GRANT CONNECT ON DATABASE postgres TO SomeUser;
ALTER USER SomeUser CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE postgres TO SomeUser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO SomeUser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO SomeUser;

What am I missing here?

Even tho PG is case sensitive in other cases, it requires lowercase usernames. Using a lower case name during login fixed the issue.


"SSIS Evaluation Period Has Expired" on Dev Instance? I recently started receiving the following error on a dev server. At one point SQL Server 2016 CTP was installed here but we've upgraded since then. I've tried uninstalling / reinstalling SSIS as well as repairing it. Reboots occurred along the way. For the life of me I can't figure out why this error persists.

I've verified that the DTExec.exe is located where SQL Server expects it to be, using:

SELECT * FROM msdb.dbo.syssubsystems

...and that it's not referencing some other version of DTExec.exe previously installed on the server.

Is there anything else I can attempt to clear this alleged evaluation period?

  Executed as user: (Domain\SQLServiceAccount).
  Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved.
  Started: 11:00:01 PM
  Error: 2017-01-03 23:00:54.81 Code: 0xC0000033 Source: XXXX ETL XXXX
  Description: Integration Services evaluation period has expired.
  End Error
  Error: 2017-01-03 23:00:57.39 Code: 0xC0000033 Source: XXXX ETL XXXX
  Description: Integration Services evaluation period has expired.
  End Error
  Could not execute package because of error 0xC0000033.

The rest of the instance is fine. It is only SSIS that is producing the "evaluation period expired" error message.

Maybe someone have accidentally used an Evaluation Edition to set up SSIS? Perhaps, but you'd think an uninstall and subsequent reinstall (using a dev edition install kit) of the service would correct that problem if that were the case.

Run regedit and change the value of CommonFiles REG_DWORD under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\ConfigurationState

...from 1 to 3.

Run the Edition Upgrade and this will "upgrade" the components.

If that doesn't resolve the issue, then we need to make sure the client components are on the same service pack/build version as SQL Server.  If you have applied SP1 to SQL Server, check and make sure SP1 successfully installed on the client components as well.  Looking at your setup bootstrap logs will help determine that.


How do I find out the "redo log file max size" in Oracle 11R2 I'm working with Oracle 11R2 and I want to see the max size of my redo log files (I didn't install Oracle so I'm not aware of the configuration).

I have searched the Internet but all I have found concerns the way to erase and replace it.

Is there anywhere in the data dictionary I can find this information?

I’ve already tried  v$log and v$parameter but I haven't found what I'm looking for.

If you want the size of the redo log members (files on disk) size, then use the query below:

-- Show Redo Logs info
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

The query output should look like:

GROUP   #THREAD#  SEQUENCE# ARC STATUS   REDOLOG_FILE_NAME      SIZE_MB
---------- ---------- ---------- --- ---------------- ---------------------
1       1       4 YES INACTIVE /ORACLE/oradata/orcl1/redo01.log   50
2       1       5 YES INACTIVE /ORACLE/oradata/orcl1/redo02.log   50
3       1       6 NO  CURRENT  /ORACLE/oradata/orcl1/redo03.log   50


Error installing EM13 templates in Oracle 12 C Linux OS version working with:

Oracle Linux Server release 7.3

Oracle Database and Grid Infrastrucutre version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Oracle Grid Infrastucture 12c Release 2

* All prerequisites met

I am getting the following error message when trying to create the database template in Oracle using dbca:

I am unsure if the error is related to a version issue or something I can't identify. If anyone has come across this issue, please will you provide a solution.

The column ACDRROWTSINTCOL# was added to a few dictionary tables, including TAB$, in version 12.2.

The EM templates are for version 12.1.0.2. The download page clearly states that the template should be used with 12.1.0.2.

http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/db-templates-3233309.html

  Note: You can use following DB templates only with EM 13.2.0.0 release. Refer to the EM 13.2.0.0 Install guide to learn how to use these DB templates to setup database that will house the EM repository 13.2.0.0 DB templates are only for 12.1.0.2 version of DB with config types [ Single Instance and PDB ]


Does row versioning increase the size of indexes? Row versioning maintains 14 bytes of internal versioning information for each row. But is this really a cost per row or is this a cost that also applies to each index on the table?

It seems the 14 bytes must be added to all index records as well so that index-only scans (and other index-only accesses) can see the versioning information and perceive a point in time snapshot of the data.

All information that I could find on the web only talks about a per row overhead of 14 bytes, though.

It seems the 14 bytes must be added to all index records as well

Why should it be added to all index records? Do you mean all indexes, or all rows of all indexes?

Only the affected rows of the affected indexes will get 14 bytes tag.

Here are 3 examples.

Suppose we have clustered table of 100 pages (with id int PK) and 1 nonclustered index on it (on another column Col1), and we change 10 keys (id) of clustered index.

All 10 rows of clustered table will get row version tag (10 rows, not 100 rows), and the 10 affected rows of nonclustered index will have this tag because every nonclustered index contains clustered index key at least at the leaf level, so 10 rows of nonclustered index will have their versions too.

You can prove it to yourself by changing 10 rows in clustered table having nonclustered index in db with RCSI and by checking sys.dm_tran_version_store: there will be 20 rows and you'll be able to distinguish clustered index rows from nonclustered index rows by their size.

...................................................

Now suppose we have the same table with 100 rows and nonclustered index on the same Col1 column. Now we make an update of id column of this heap.

If you check now sys.dm_tran_version_store you'll see only 10 rows, they are 10 rows of a heap, there is no index rows at all. This is because our index on Col1 was not changed when we updated 10 id. Our id in this case has no affect on nonclustered index that has RID as back pointer to a heap at the leaf level.

Of course if our nonclustered index had id as key field or included field, update of id would affect this index too, in this case we'd found 20 rows in row version store again.

...................................................

Example N3: table with structure Col1, Col2, Col3.
Indexes: ix_1 (Col1), ix_3(Col3).

We update Col2 that is not presented in any index. Only affected rows of the table will get row version tags, ix_1 and ix_3 will not be affected at all.

As Solomon Rutzky pointed out in his comment, these version tags will be presented in indexes until INDEX REBUILD.

Below is the cite from this book: Pro SQL Server Internals By Dmitri Korotkevitch

  Optimistic isolation levels contribute to index fragmentation. When a
  row is modified, SQL Server increases the row size by 14 bytes due to
  the versioning tag pointer. If a page is tightly packed and a new
  version of the row does not fit into the page, it will lead to a page
  split and further fragmentation. This is very similar to the
  insert/update pattern we discussed in Chapter 6, “Index
  Fragmentation.” Those 14 bytes will stay in the row, even after
  records are removed from the version store, until the index is
  rebuilt.

  If optimistic isolation levels are used, it is recommended that you
  reserve some space on the pages by using a  FILLFACTOR  of less than
  100. It reduces page splits due to row-size increases because of the version store pointers.


Designing a database structure for Partners and Websites I have two tables, Partners and Websites. I also have an "associative" table called Partners_Websites that retains the relationships "between" the rows of the tables Partners and Websites. In the Partners table, there are rows that I want to relate to all rows in the Websites table (even rows that can be added in the future).

Let's say I have P1, P2, P3 and W1, W2 and W3:

I want P1 related to W1 and W3
P2 related to W1 and W2
P3 related to all Websites

Then the Partners_Websites table will look like:

P1, W1
P1, W3
P2, W1
P2, W2
P3, W1
P3, W2
P3, W3

The question

If I add W4, I will need to add a new row in the Partners_Websites table to relate it to P3. How can I define "P3 is related to all Ws" without requiring the Partners_Websites table? I was thinking in adding a "flag" in Partners (that is, a boolean column named is_related_to_all) but I think it looks wrong (I may end up with hundreds of is_related_to_all = false).

Is there any way of doing this using relationships?

I would suggest that you would still want the relationships listed in your junction table, so that your data model is consistent. Also otherwise you end up having to UNION two results together all the time (one side for the "all"s and one side for the "some-or-none"s) or having filtering clauses of the form property1 = value OR property2 IN (<set>) - both of which can cause efficiency issues depending on the query planner of the engine you are using.

To enforce that P3 relates to all new entries in WebSites without needing to do that everywhere in your business logic that might add sites, you could perhaps define a trigger on that table which checks for Partner entries that are set as global and adds the relationship rows as needed. You need to take care with triggers as they can be quite inefficient (though for most database entities a performance hit on writing is worth a boost when reading, as read operations are far more numerous and concurrent). The other thing to be wary of with triggers is that magic actions behind the developer's back can make debugging more difficult, but if you make sure all is well documented and understood by your team that need not be an issue.


Exclude IP from being blocked by MySQL I have a mysql server running. Due to the uptime is not perfect, sometimes the HTTP server is blocked when mysql server is down intermittently.

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

Currently, I have to set up a cron job to run "mysql-admin flush-hosts" to ensure my HTTP server can connect to database after the block.

My question is, how can I get certain IP (eg my HTTP Server) from being blocked by MySQL server ? Is it possible?

https://dev.mysql.com/doc/refman/5.0/en/blocked-host.html

I have a 4-year-old post on how to schedule FLUSH HOSTS:

How do you tell which host is close to being blocked in MySQL?

In that old post, I mentioned two techniques

TECHNIQUE #1 : Use MySQL Event (See the event code from my old post)
TECHNIQUE #2 : Run perl script (See the perl code from my old post)

Setup Parameters to Heartbeat
Connect to the mysqld
Hold DB Connection open indefinitely
Run FLUSH HOSTS; on the set schedule

In either case, FLUSH HOSTS; can be executed regularly.

Notwithstanding, you should be polling status variables

SELECT * FROM information_schema.global_status
WHERE variable_name LIKE '%connect%';

to monitor connections attempts, dropped connections, and thing like these.


how to duplicate each row of a mysql table? I've got a table of Product records from an old db that I must change to import on a new php system. They have the form:

name, property1_IT, property1_EN, property1_FR

I need to transform them by duplicating each row and change it like:

name, language, property1

where property1 will have the related translated value.

In other words, I'll need one row for each product translation, thus adding a language column and deleting the property translation fields.

Is it possible with sql or should I just use php to cicle through the records and doing the duplication tasks?

You can do it like this:

create new_table as
select name, 'italian' as language, property1_IT as property1 from old_table
union all
select name, 'english' as language, property1_EN as property1 from old_table
union all
select name, 'french' as language, property1_FR as property1 from old_table;


MySQL Restore Large Dump I have a dump file of a single table which is 7GB. I try to restore it using

mysql -u root -p DB_NAME < .SQL FILE

But I don't see any activities in task manager. CPU is 00 and RAM is 5,700 K. I don't know if it is really restoring it or and error occurred. Is there any other way to restore this? I am running it on windows 7.

For this answer, let's use /root/MySQLData.sql as the SQL File.

Go into mysql via the client and run it like this:

mysql> source /root/MySQLData.sql

Give it a Try !!!

UPDATE 2011-12-16 21:08 EDT

I just caught on to your problem: Your program is not running because you did not supply the password. It is stuck in the background waiting for a password. That's your actual problem. Kill the job and run it again with the password on the command line:

mysql -u root -ppassword DB_NAME < .SQL FILE

or just use my original submitted answer up above.

UPDATE 2011-12-19 11:40 EDT

I just re-read the question. You said single table. Then it hit me !!!

You need to ramp up your bulk_insert_buffer_size. The default is 8M.

To see what your current setting is, run this:

mysql> show variables like 'bulk%';

Just add this to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

So as to not restart mysql, run this SQL command:

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

Give it a Try !!!


Changing the mysql.proc table directly after upgrade Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5.

This caused a problem because the table definition of mysql.proc is different as discussed here:

http://bugs.mysql.com/bug.php?id=50183

Basically, our stored procedure won't run because one column changed (comment) between 5.1 and 5.5  My question is, can I safely run this command to fix the problem:

alter table mysql.proc modify comment text;

I don't want to run mysql_upgrade as that will lock every table in the database on our production server.

The mysql_upgrade utility has an --upgrade-system-tables (only) option.

I tested it on a local machine that had already been done, but judging from the output and the documentation, it seems to be what you're looking for.

Of course, be sure you use the version of mysql_upgrade that exactly matches whatever version they have upgraded you to (e.g., 5.5.30).

sqlbot@dev:/usr/local/mysql$ bin/mysql_upgrade --upgrade-system-tables
Looking for 'mysql' as: bin/mysql
The --upgrade-system-tables option was used, databases won't be touched.
This installation of MySQL is already upgraded to 5.5.30, use --force if you still need to run mysql_upgrade
sqlbot@dev:/usr/local/mysql$


How to get the date part from a date with timestamp column in SQL Server? I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').

I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.

Edit:  I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:

concat
(
  CONVERT(varchar, DATEPART(dd,columndate)),
  CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
  CONVERT(varchar, DATEPART(year, columndate))
) AS trial

This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.

Is there any other way to achieve this?

Any ideas/suggestions much appreciated.

Here is an example using GETDATE():

SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');

Yields 12Nov2018. Three days ago, it would yield 09Nov2018.

To run this against your table:

SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
  FROM dbo.YOUR_TABLE_NAME_HERE;

In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).

SELECT FORMAT(GETDATE(), 'ddMMMyyyy');


MySQL - Force index degraded performance over time Last year we migrated data from our old product environment into a new environment with our new product.
Since the new product was still fresh, we found that some of the new queries were very slow after the migration. and we fixed several of them by adding the 'FORCE INDEX(SOME_INDEX)' to the queries join clauses. this immediately solved our problem and sped up the queries dramatically.
Lately we see some performance degradation in the new product again, and the same queries we optimized last year using the 'FORCE INDEX(SOME_INDEX)' are not faster without the force index part of it (basically using the original query from last year is now faster)
Someone at the office threw to the air the idea that mysql keeps his own statistics of its indexes and as the mysql server keeps working it become more efficient in creating the flow of the query. (meaning that at the beginning it has less statistics and thats why hinting to force index helps it, and after a year of production usage its own statistics are enough).
Is this correct? is there any other plausible explanation for this?

Without any index hint, the Optimizer uses statistics to decide which index to use.  And it updates the statistics in a reasonably (but not always) intelligent manner.
With a hint, such as FORCE INDEX, it ignores the statistics and uses the given index if at all possible.
Probably the FORCE was beneficial at first.  But later the data in the table shifted due to new rows, changing distribution of values, or the phase of the moon.  And now that index is no longer optimal.
Your experience is why I warn people against using hints, saying "it may help today, but hurt tomorrow."
If you would like to discuss the specific query, please provide SHOW CREATE TABLE and EXPLAIN SELECT...  It may be that a different composite query, without the hint, will work even better than what you had or have.
More on the history...  The Optimizer was heavily worked on in 5.6, 5.7, and 8.0.  A big change was moving to a "cost-based" model.  In theory, it produces better results.  In practice, it rarely changes anything.  And, as perhaps your case shows, it sometimes hurts.  The presumption is that the cost-based model is usually better than the previous algorithm.
The statistics are updated when any of these happens:

The table grows by 10%.  (This partially addresses your coworker's theory.)
You run ANALYZE TABLE.  In rare cases, this is a "fix" for a bad index choice, but it may or may not stick.
Some flavors of ALTER.


How to compare dates on minute level in plsql? If I'm correct, then date comparison on seconds level is as simple as

SELECT 1
FROM sample_table
WHERE sysdate <= sample_table_date_field;

but haven't found any simple or stable solution for date comparison on minute level.
If there is, then what would it be?

You can use TRUNC (date) function which returns date with the time portion of the day truncated to the unit specified by the format model.
Syntax:

TRUNC(date, fmt) or TRUNC(date).

Where fmt can be 'DAY', 'YEAR','HH','MI'.
For more format model please refer: ROUND and TRUNC Date Functions

Example:

SELECT 1 FROM sample_table WHERE TRUNC(sysdate,'MI')<=TRUNC(sample_table_date_field,'MI');

Otherwise you can also use EXTRACT function to extract the elements from datetime.
Example:

SELECT 1 FROM dual WHERE extract(minute from systimestamp)=extract(minute from systimestamp);


Datetime conversion fails when filtered index is placed on partition column I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.

The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.

Does anyone have any ideas on why this happens?

use YOUR_DATABASE ;

go

select @@VERSION ;

-- Microsoft SQL Server 2016 (SP2-CU1) ...

-----------------------------------------------------------------------------

-- CREATE DATETIME PARTITIONED TABLE

set language english ;

drop table if exists dbo.test_of_filtered_idx ;

drop partition scheme ps_test_of_filtered_idx ;

drop partition function pf_test_of_filtered_idx ;

go

set language english ;

go

create partition function pf_test_of_filtered_idx (datetime)

   as range right

   for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')

create partition scheme ps_test_of_filtered_idx

  as partition pf_test_of_filtered_idx

  ALL to ([primary])

create table dbo.test_of_filtered_idx

(

  id int not null identity (1,1) ,

  dt datetime not null ,

  payload char(127) not null default(replicate('A' , 127)) ,

  constraint PK__test_of_filtered_idx primary key clustered (id , dt)

) on [ps_test_of_filtered_idx] (dt) ;

go

-----------------------------------------------------------------------------

-- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

set language english ;

go

create index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

  (dt) include (id)

  where dt >= '1999-12-10 00:00:00.000'

    and dt < '2000-01-20 00:00:00.000' ;

go

-----------------------------------------------------------------------------

-- CHECK

set language english ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;

go

-- GET ERROR

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

-- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

-- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

go

-----------------------------------------------------------------------------

-- DROP INDEX AND GET ERRORFREE INSERTION

set language english ;

go

drop index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

;

go

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

-----------------------------------------------------------------------------

The error comes from converting the string 2000-01-20 00:00:00.000 (in the filtered index definition)

Changing the filtered index to use unambigous datetime format works.

create index IXF__test_of_filtered_idx__dt
  on dbo.test_of_filtered_idx
  (dt) include (id)
  where dt >= '19991210 00:00:00.000'
    and dt < '20000120 00:00:00.000' ;

It definitely seems buggy to me  that the filtered index range is interpreted according to the settings of the client and so the same date can be either included or not included in the filtered index dependant on the settings of the client at insert time (as in below demo)

CREATE TABLE T
  (
     dt DATETIME
  )

CREATE INDEX IXF__test_of_filtered_idx__dt
  ON T (dt)
  WHERE dt >= '1999-01-06' AND dt < '1999-02-06';

SET LANGUAGE ENGLISH

GO

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --6th Jan

GO

SET LANGUAGE RUSSIAN

INSERT INTO T
VALUES     ( DATEADD(DAY,5,'1999-01-01')); --Still 6th Jan

GO

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T

Returns two rows - both with the same date

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
| 1999-01-06 00:00:00.000 |
+-------------------------+

But

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T
WHERE  dt >= '1999-01-06'
       AND dt < '1999-02-06';

Uses the filtered index and just returns one of them

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
+-------------------------+

Running DBCC CHECKTABLE (T) WITH EXTENDED_LOGICAL_CHECKS at this point then fails for either language.

Msg 8951, Level 16, State 1, Line 4
Table error: table 'T' (ID 1045578763). Data row does not have a matching index row in the index 'IXF__test_of_filtered_idx__dt' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 4
Data row (4:24:1) identified by (HEAP RID = (4:24:1)) with index values 'dt = '1999-01-06 00:00:00.000' and HEAP RID = (4:24:1)'.
DBCC results for 'T'.
There are 2 rows in 1 pages for object "T".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'T' (object ID 1045578763).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (tempdb.dbo.T).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


SQL: shopping cart design for a marketplace I'm working on a marketplace that requires the user to be logged in to add items to the shopping cart. In most online examples, I can't see the benefits of creating a separate cart and cart_item tables.
Example: https://mysql.tutorials24x7.com/blog/guide-to-design-database-for-shopping-cart-in-mysql
Why not just create an empty order and set its status to cart and when the user submits the order, then just reduce the quantities from the product table and fill the other columns with the required values and change the status of the order?
Example with shopping cart

Example using the order table to act as a cart when setting its status to cart.

It really depends on your use cases. Having the cart and cart_item tables in the first example is just an extra level of normalization which may be needed in some contexts (depending on how different a cart_item is vs an ordet_item etc). But generally speaking, and based on the schema you've designed, it's totally acceptable to eliminate the idea of a cart and cart_item entity entirely, if the order and order_item can support all your use cases for that workflow without much complication in your backend code.
You could even take it one step further and not touch the database at all until the order has been placed. Rather than storing an "in-process order" in the database, you can take the route of just storing an object representation, such as JSON on the client side, until the order is submitted (which is when in the workflow your backend code can convert that JSON to your database entities and persist it in the order and order_item tables respectively). This is one implementation some websites take, and will temporarily persist it on the client side such as in session storage (which is why you'll find on some websites your items only temporarily stay in the cart).
But regardless, it really just depends on the use cases of your workflow whether you choose option A or B in your post, or option C that I just mentioned. They're all valid choices.


BULK INSERT: Trials and Tribulations I recently ran into several problems with a BULK INSERT task, so I thought I'd share my experiences. I'm not looking for help, just offering help for others that may experience something similar in the future.

I have a python script on a network drive, where the path on my "workstation" is something like z:\pythonscript\script.py and a subdirectory z:\pythonscript\data.  The network drive is a physical drive on the server (MS Server 2008 R2) with the database, but has a path on that computer of S:\blah\shared\pythonscript\script.py.

Lessons Learned

BULK INSERT takes a special permission set in SQL Server.
If using an autoincrement field (or not explicitly set field) not present in your data file, use a view and exclude that column from the View
The data file needs to be on-server for the DB server, and you have to use the local path from the server's perspective.
Set SQL Server service user(s) to be able to read from locations where data files are present.
If writing error files, they a) cannot already exist AND b) the target directory needs write permission for the SQL Server service users
SQL Server 2008R2 doesn't provide an interface for accessing system environment variables, including %TEMP%

The Circuitous Path to Learning (i.e. a semi-sequential log of what happened)

Sending a BULK INSERT query - referencing the z:\ path of my workstation - via the python script returns an error

The query is a variation of

BULK INSERT [dbo].[tStaging] FROM '/path/to/file.csv' WITH (
  FIELDTERMINATOR = ',', MAXERRORS = 1024, FIRSTROW = 1, KEEPNULLS,
  ERRORFILE = '/path/to/file.err', TABLOCK )

The error was

  Cannot bulk load because the file "Z:/pythonscript/data/file.csv"
  could not be opened. Operating system error code 3(The system cannot
  find the path specified.).

Switching to the S:\ based-path gives me

  Cannot bulk load because the file
  "S:/blah/shared/pythonscript/data/file.csv" could not be opened.
  Operating system error code 5(Access is denied.)

Hmm, okay.  I'll follow my own advice here and enable read and execute permission for the service accounts for the path specified.

Then if I specify an error file via the ERRORFILE = '{}' parameter, I may get:

  Cannot bulk load because the file "S:/blah/shared/pythonscript data/file.err" could not be opened. Operating system error code 80(The file exists.)

I cannot specify an "append" mode. As the MS documentation points out if I bother to read it, it just returns an error if the file exists.  That's frustrating. Okay...I say, I'll just delete the file (and the file.err.Error.txt that is also created) and re-run.

  Cannot bulk load because the file "S:/blah/shared/pythonscript/data/file.err" could not be opened. Operating system error code 5(Access is denied.)

So...write access too then? Silly me. Do this again for write access.

About now I hit an error that suggested that I didn't have access for BULK INSERT operations, so had to enable that feature

USE master
go
GRANT ADMINISTER BULK OPERATIONS TO pySqlUser

Solving that, I still get

  SQL ERROR in BULK INSERT. #4864 Severity: 16    State: 1        Message: SQL Server message 4864, severity 16, state 1, procedure , line 1:
  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (eID)

eID is an autoincrement primary key that I'm trying to add to my data set in a staging table.  I tried destroying the table and moving the eID column to the last. That doesn't appear to help.

  SQL ERROR in BULK INSERT. #4866 Severity: 16    State: 1        Message: SQL Server message 4866, severity 16, state 1, procedure , line 1: The bulk load failed. The column is too long in the data file for row 1, column 12. Verify that the field terminator and row terminator are specified correctly.

I suspect that something is wrong with my row terminator. Expanding the field size of the 12th column, immediately before eID, complains about

  Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

Playing around with the ROWTERMINATOR statement, I settle on, in python, ROWTERMINATOR = '{}'.format(chr(10))

Still problems.  Maybe if I add the KEEPIDENTITY keyword...

  Bulk load failed. User does not have ALTER TABLE permission on table 'dbo.tStaging'. ALTER TABLE permission is required on the target table of a bulk load if the target table contains triggers or check constraints, but the  'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified. ALTER TABLE permission is also required if the 'KEEPIDENTITY' bulk hint is specified.

That's weird, I say to myself, why would you need ALTER TABLE for KEEPIDENTITY? Then I look up KEEPIDENTITY again, and realize it's basically the opposite of what I want. I want it to "GENERATEIDENTITY" instead, but that's not an option. So, one option is to go with an XML file where I specify that the eID column is not coming from the imported data. Nah, I'll try a view instead.

Once the view was written, with all columns - minus eID - in same order as the underlying table, I could execute the following

qstring = "BULK INSERT {} FROM '{}' WITH ( FIELDTERMINATOR = '{}', ROWTERMINATOR = '{}', MAXERRORS = 1024, FIRSTROW = {}, KEEPNULLS, ERRORFILE = '{}', TABLOCK )".format(viewname, filename, fieldSep, chr(10), 1, ename)

which generates e.g.

BULK INSERT [dbo].[tStagingView] FROM 'S:/path/to/file.csv' WITH (
  FIELDTERMINATOR = ',', ROWTERMINATOR = '[newline here]',
  MAXERRORS = 1024, FIRSTROW = 1, KEEPNULLS,
  ERRORFILE = 'S:/path/to/file.err', TABLOCK )

I have to police the directory for error dump files and remove them before the SQL call, or I need to name my error files "{}.err".format(gmtime()) and trash them manually, but frequently. I had intended to use the system temp directory via ERRORFILE = '%TEMP%/file.err'. But that gives:

  Cannot bulk load because the file "%TEMP%/file.err" could not be opened. Operating system error code 3(The system cannot find the path specified.).

So despite SQL Server resolving linux-style path slash direction, it can't handle a simple environment variable expansion. Maybe I'll hard-code my server's temp directory path into my python code so I don't have to do any manual purges of my data directory. An alternative would be to create a cross-drive junction/symlink that points to the system temp, but that seems to be asking for trouble.


Can I execute an external process via MySQL on table change? When using triggers, if an update is done to a table then a trigger is executed. This is very convenient. But, what I would need is to execute an external script.
Is it possible to configure MySQL so that on a trigger/change of a value in a table an external process/script is executed?

It is possible, and it also makes for a security threat.

Anyway, to make that happen you would need to create (program, compile and install plugin) you own UDF - User Defined Function. It's a C/C++ code, so you can do anything you like in there.


Four-part distributed Query issue from 2014 to 2008R2 From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login.

select t.noshipid, y.nofolder
                        from distantserver.distantdatabase.dbo.sometable t (nolock)
                        inner join  somedatabase.dbo.sometable y (nolock)
                            on cast(t.nobill as bigint) = y.nobill
                        where t.type_payment = 'CE'
                        group by t.noshipid, y.nofolder

distantserver is in 2008 R2.

I get back an error

  Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction
  Coordinator (MS DTC) has stopped this transaction.

If I execute the same query on the production server which is 2008 R2, no errors.

1: The SQL login in question has the necessary access.

2: The provider being used to connect to the 2008 R2 server is SQLNCLI10

Questions:

1: Is it possible that upgrading the DEV server to 2014 is causing these issues?

2: Are there any know issues using a four-part identifier from 2014 to 2008 R2 ?

from distantserver.distantdatabase.dbo.sometable

instead of using openquery

FROM    OPENQUERY([distantserver],

because using OPENQUERY, the query does not return errors.

3: Are there any sp_configure options or linked server options I need to configure because of 2014?

Update 14:41

If I add

BEGIN DISTRIBUTED TRANSACTION

at the top, the distributed part works fine. I just want to emphasize the fact that before upgrading, this was not necessary.

Ideally, you should be using the SQLNCLI11 and not SQLNCLI10.  SQLNCLI11 since that connects back to SQL 2008R2/2008/2005 versions.

There is a known bug with SQLNCLI10 that is worth knowing (even though shows that there is a problem when connecting from SQL Server 2008R2 to 2000, but it shows that you are encountering it from SQL Server 2014 to 2008R2).

Also, since you are using linked servers - refer to : Which one is more efficient: select from linked server or insert into linked server?


DB Design for Event Dependencies I have a series of events (potentially hundreds) segregated into applications. Each one of these events has a dependency on others (except the first one, of course). I'm looking to map all of these dependencies in a table, but I'm not sure the best way to do so. One event can have multiple predecessors, or multiple successors, or both. There are also cases where A is a prereq for B, B is a prereq for C, but A is also a prereq for D, and C and D are both prereqs for E. (I can't assume that the dependency chain is linear.)

I already have a table with all of the events listed with some other details, and the event names are unique. My first thought was to have a "dependency" table that had an ID (PK) field, 'event' field containing the ID from the events table, and a "dependenton" field that also contained an ID from the events table. Obviously each event could have multiple entries if they are dependent on more than one other event. I believe this follows first normal form, but I'm trying to think if there is another design that would be more efficient or easier to work with.

Thank you.

Sounds like a "many:many" relationship.

See my tips on writing an optimal many:many table.

I assume you have a PRIMARY KEY for the Event table; perhaps the event name?  It should be used in the many:many table.


How do delete duplicate key row in mssql server? I'm not a great mssql admin so this may be a lot easier, I just don't know where to start looking.

ADO Err Description: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot insert duplicate key row in object 'dbo.posALL'
with unique index 'PK_tposALL'.
Originated at: Business.Cls.DayChange
ADO Err Number: -2147217900
Source: Microsoft OLE DB Provider for ODBC Drivers
SQL State: 23000
Native Err: 2601

When using a program that detects the current day and requests to change it on a new day, fails with the report above.

I'm looking at the database, I can see the table and rows, but I don't know how to see key rows or determine where or why there may be a duplicate, especially for something like which day of the year it is.

Cannot insert duplicate key row in object 'dbo.posALL' with unique index 'PK_tposALL'

Breaking down this error message a little:

You have a table named dbo.posALL
There is a unique index named PK_tposALL defined on this table
The program you are using tries to insert a record with values for the key columns defined in PK_tposALL that already exist in the table

To understand which columns constitute a duplicate, you can go to the table in SSMS, expand the "Keys" folder, and then choose "script key as."  Check out this screenshot:

ALTER TABLE [dbo].[Badges]
ADD CONSTRAINT [PK_Badges__Id] PRIMARY KEY CLUSTERED
(
    [Id] ASC
);

Note that in this case, the unique index is defined using a primary key constraint.  I imagine this is the case in your scenarios as well.  Yours may or may not be CLUSTERED, and that part doesn't matter for this situation.

In any case, my dbo.Badges requires the Id column to be unique in the whole table.  Your column list will be different.

To find the duplicate data, you'll write a query that specifies each column from the unique index in the WHERE clause:

SELECT b.*
FROM dbo.Badges b
WHERE
    b.Id = 128;

In your case, it will look something like this:

SELECT *
FROM dbo.posALL a
WHERE
    a.ColumnA = 123
    AND a.ColumnB = 456
    AND a.ColumnC = 'abc';

Where,

ColumnA, ColumnB, and ColumnC are the columns in PK_tposALL
123, 456, and 'abc' are the values your program was trying to insert into the table

In the end you'll have to decide what to do from there, possibly either deleting the existing row (so that you can insert the new row) or updating the existing row with the values from the new row.


Mixture of byte and character semantics in Oracle DB Are there any issues having a mixture of byte and character semantics in a table?

E.g. Location table:

 location_type varchar2(3 byte)
 location_name varchar2(50 char)

How about a schema having a mixture of tables with byte and character semantics?

Any implication in terms of performance?

Length Semantics is an option to define the SQL character types in terms of either bytes or characters. Before Oracle 9i we had only one option that is byte semantic, we used to define CHAR, VARCHAR, CLOB etc in terms of bytes. This is fine for single-byte character sets where one character is equal to one byte. But in multi-byte character sets this could be a problem because a character that you wanted to store could be larger than a byte.
For example: If you defined a column as VARCHAR2(10) and the character is multi-byte(Chinese characters for example) then it may take more than 10 bytes to store 10 characters.
After Oracle 9i we have two option we can now define SQL characters either in Byte or in Character length semantics. Byte is default length semantic.

Is there any issues if a table having mixture of byte or character semantics in a table?

The main reason to specifying the length semantics first it properly documents the expected semantics in DDL statements and second it makes the statements independent of execution environment.
If you define the column as CHAR length semantic then a single character might take a different number of bytes to store, the width of the character in bytes is variable.
If you define the column as BYTE length semantic then it takes only one byte for one character and if you try to store a character which is multi-byte then you will get an error.

Any implication in terms of performance?

No, AFAIK. Because length semantics has nothing to do with how the data is interpreted.

Caution:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length >semantics, resulting in runtime errors, including buffer overflows.

Reference: Length Semantics


What is the best practice for the msdb database owner? In the course of reading up on SQL Server security best practices, I learned that databases should generally be owned by a dedicated, low-privileged login (sometimes a separate one for each DB or each application), per these articles.

I've also learned that you can't change the owner of master, model, and tempdb, but you can change the owner of msdb.  My question is: should you?

The main concern with high-privileged database owners is that the TRUSTWORTHY setting could be enabled for that DB, and msdb has to have TRUSTWORTHY enabled.  It seems like that's a good argument for changing the owner of msdb to a low-privileged account, but I don't know if that could cause problems with instance functionality, and I haven't been able to find any articles or whitepapers even discussing the topic.  The closest I've seen is this one, which stops short of making any recommendations.

Can anyone provide any insight as to the possible effects of changing the owner of msdb?

Brent Ozar's sp_blitz check on this sums it up well, I think:

  ...there’s no short answer for best practice.

And he does reference Andreas' article as well, acknowledging that is an ideal state. But often times ideal states are not feasible or reasonable in practice.

I don't know that taking the best practice approach (in this case) is indeed reasonable or feasible when looking at many enterprise scale deployments or when managing any large number of instances. I'm often satisfied enough to just use sa personally.

A lot of the community's best practice checks default to looking for sa as a database owner in general, because having a commonly used one does indeed help from an administrative perspective. For example, the popular PowerShell module dbatools's functon Test-DbaDbOwner defaults to looking for sa (citing Dan Guzman as the best practice source to do so) as the owner of all databases (though you can certainly specify a custom one if desired) just like Brent's scripts do.

I think in this case whether or not to do it comes down to personal opinion/choice based on circumstance. If you can handle the additional burden, then by all means it is ideal to do so. But most of the time in my experience, I think the extra work, complexity, and potential for time consuming problems outweighs the narrow benefits.

Finally, when you ask:

  Can anyone provide any insight as to the possible effects of changing the owner of msdb?

I'm not sure what you mean, but I think Andreas covers the topic fairly well to show what the benefits/effects of changing the owner are.

Further Reading:

Is Execute As Owner in MSDB a security risk?


Dynamic database reference in T-SQL scripts We have a set of databases (development, QA and production, dev-foo & dev-bar, qa-foo & qa-bar and just foo & bar respectively).

Inside foo family of databases there's a view that should use respective bar database to do cross-database joins. In other words,

select f.*, b.* from foo f inner join <bar-db>.dbo.bar b on f.barid = b.id

The question is: is there any way I could write this script once and then have a database-level variable <bar-db> that would point to an appropriate "instance" of a bar database?

The answer is Synonyms.

Repeat for each database in foo family:

create synonym bar for [srv-db].<bar-db>.dbo.bar;

And then use it as follows:

select f.*, b.*
from foo f inner join bar b on f.barid = b.id ;


Why does creating database snapshot fail unless user is db_owner or sysadmin? MSDN suggests it should work MSDN suggests that CREATE DATABASE permissions are all that is necessary to create a snapshot.

  Any user who can create a database can create a database
  snapshot; however, to create a snapshot of a mirror database, you must
  be a member of the sysadmin fixed server role.

This seems not to be the case.  When trying to create a snapshot via a user with this suggested minimal permission requirement, I get the following error:

Msg 1817, Level 14, State 1, Line 4
Only the owner of database "example" or the system administrator can create a database snapshot on it.

As I'd like to set up an automated process to create snapshots on a schedule, I obviously would like to avoid giving it db_owner or sysadmin rights.

Is it possible to set up as suggested by MSDN or are their docs wrong?

Many thanks for any suggestions.

/*as sysadmin*/

USE [master];
CREATE DATABASE example;
CREATE LOGIN testuser WITH PASSWORD='xxx';
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [example];
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [master];
GRANT CREATE DATABASE to testuser;
go

/*as testuser*/

USE [master];
create database example_snapshot_01
on (
name = example,
filename = 'C:\...\example_snapshot_01.ss'
)
as snapshot of example;
go

/*
Error:
    Msg 1817, Level 14, State 1, Line 4
    Only the owner of database "example" or the system administrator can create a database snapshot on it.
*/

In order to be able to create a database snapshot, you will need CREATE DATABASE permissions as well as db_owner on the database you want to snapshot. I have tested this and it works perfectly fine.

The other solution is to grant dbcreator server role to the login that needs this privilege. This works too.

It seems that with 'Any user who can create a database can create a database snapshot' this BOL means login having dbcreator server role.


change mysql datatype column from string to json I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON.

I already put a {" before and "} after the strings. So now the values of the column looks like:

{"/users/57/user-11.png"}

When I try to update the table by:

ALTER TABLE ah_website_dev2.vendor_horses MODIFY COLUMN image json;

I get following error message:

Error Code: 3140. Invalid JSON text: "Missing a colon after a name of object member." at position 27 in value for column '#sql-1d30_44.image'.  0.011 sec

Whilst a JSON field may do what you want, consider proper Data Normalisation rules first.

As soon as you want "more than one" of something, you should be thinking about a new table that contains those multiple instances, with a foreign key back to the table that contained "the one" thing previously.

select * from vendor_horses ;

+----+---------+
| id | name    |
+----+---------+
|  1 | Red Rum |
+----+---------+

select * from vendor_horse_images ;

+----+-------+-----------------------+
| id | vh_id | path                  |
+----+-------+-----------------------+
| 22 |     1 | /users/57/user-11.png |
| 33 |     1 | /users/57/user-73.png |
+----+-------+-----------------------+


Why is a Shared lock blocking on an IX lock when Read Committed Snapshot is enabled? Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot of the deadlock graph.

For reference: The left node is a basic insert into the sync_entities table, where the right node is:
update sync_entities
set A=B, C=D
from sync_entities e
join sync_revisions r on e.id=r.entity_id
where r.id in (<some very large list of IDs>)

Based on my understanding of lock compatibility, requesting an S lock when an IX lock exists would block only if Read Committed Snapshot is off, but it is turned on for this database, and has been for a very long time. Am I missing something?
The deadlock XML says that both processes were using isolationlevel="read committed (2)".

Read Committed Snapshot don't affect the lock compatibility matrix.
For reads it means that shared locks aren't acquired, and reads will use row versioning instead. Assuming you are using read committed, that is, since this is the only isolation level that is "reconfigured" by the Read Committed Snapshot database knob.
You evidently have shared locks, and such will block/be blocked by IX locks, according to the log compatibility matrix at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)?redirectedfrom=MSDN.
So the question for you to investigate is why you have shared locks. Perhaps some parts of your app don't use Read Committed?
Paul mentions in a different thread that S locks are used for FK validations when you modify data in RCSI, which can be an explanation for the S locks:
Shared locks taken under Read Committed Snapshot Isolation (RCSI)


Select query is too slow I am facing a really strange problem with my database. I have a table with less than 20 columns. When i run a select * query, it's taking almost 6 to 7 seconds to execute. I couldn't identify the issue and tried selecting each columns one by one. By the way, total number of rows are just 269.
Finally i found 2 columns which causes the issue. Both these columns store some kind of description and the datatype is MEDIUMTEXT.
SELECT LocalDescription FROM test.hotels where Id > 0;
SELECT LocalFacilitiesDescription FROM test.hotels where Id > 0;

It took between 5 to 6 seconds to finish the execution. There is no delay for the rest of the columns.

It has to do with the way VARCHAT/TEXT/BLOB field are handled.
I personally observed this in an RDS instance
I had taken a table with 25 million rows which had a TEXT field in it and ran this
SELECT DISTINCT LENGTH(colname) FROM tblname;

and I saw just 2 numbers, 3 and 10.
I converted the field from TEXT to VARCHAR(10). What this did was increase the amount of RAM occupied in the InnoDB Buffer Pool. I verified this from a CloudWatch graph, see Free RAM dip right after doing this and the graph stayed that way. The number of free buffer pool pages dropped accordingly.
I am not the only to have seen this phenomenon. Someone once posted a blog entitled "Drain your InnoDB Buffer Pool by moving large fields out of MySQL" who had observed similar Buffer Pool weirdness.
What is interesting is that Bill Karwin had commented on this in a DBA StackExchange post (MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?). He mentioned that BLOBs will occupy InnoDB Buffer Pool Pages. Shrinking them can make data fit in the InnoDB Buffer Pool better could work for you or against you depending on the size of the data.
In your particular case, you only have 269 rows. This should let you know that handling TEXT fields requires some extra I/O in RAM or on disk. You are using MEDIUMTEXT, which can hold up to 16M. Given innodb_page_size = 16384 (16k), a MEDUIMTEXT value can occupy up to 1024 pages. This potential means you are dragging a lot of space from disk based on how much data is in each MEDIUMTEXT field. You may have to live with it.
SUGGESTIONS

If you keep MEDUIMTEXT, You may have to increase innodb_buffer_pool_size to accommodate
If you would like to change from MEDUIMTEXT, please run SELECT LocalDescription,LocalFacilitiesDescription FROM test.hotels where Id > 0 PROCEDURE ANALYSE();. This will suggest the proper datatype for those columns. (BTW I have discussed the use of PROCEDURE ANALYSE()).


Can we have more than one arbiter in monogodb replica set I am setting up cluster of my webserver which uses mongodb. I am doing replica set of mongodb.I have only two machines. So can I have two arbiters in replica set ?

There is no need in having 2 arbiters.

The minimum recommended replica set consists of THREE servers and is:

a primary
a secondary
an arbiter

the arbiter can be on either machines, but it is recommended to be on a separate machine (VM or anything) so that it won't go down with one of the servers if something goes wrong.

You could use multiple arbiters but if you have only two machines, it will do you no good because you always need n/2 + 1 servers for a succesful election.


In SQL Server Mgmt Studio, why don't triggers automatically get updated when tables get renamed? I ran into an issue last week where we renamed a bunch of tables in a SQL Server and it broke a bunch of triggers. I would have though SQL Server Mgmt Studio would have been smart enough to update those triggers but clearly not. Am I expecting too much? Is there a way to detect these issue where a trigger is referencing a table (or column as well) that no longer exists?

Trying the following in SSMS..

CREATE TABLE [dbo].[T]
  (
     X INT
  );

GO

CREATE TRIGGER [dbo].[TR]
ON [dbo].[T]
FOR INSERT
AS
    SELECT *
    FROM   [dbo].[T]

Then renaming the table as T2 and scripting out the trigger returns the following so I assume you are referring to the second table reference.

CREATE TRIGGER [dbo].[TR]
ON [dbo].[T2]
FOR INSERT
AS
    SELECT *
    FROM   [dbo].[T]

If you use SQL Server Data Tools to manage your database schema instead of making schema changes directly in SSMS then this does have a rename refactoring. It isn't 100% comprehensive as you would still need to review for any possible usages in dynamic SQL or references to the table in external scripts but it would fix the issue above.


Need to run select statement across all databases I am trying to run a query like the one below across multiple databases

select ParamValue from Table1 where paramname = 'SYSTEM_LICENSE'

Each DB is named something like the one below and some DB should be excluded.

  "Customer_123"

I looked at using sp_MSforeachdb but it isn't supported and it wasn't working well. I tried figuring out how to write a cursor but was unsuccessful. At this point just looking for some help from someone who can actually write what I am looking to do.

Here's the "do something on each db" template I use, slightly adapted to your example:

--Create a table to hold the query results
CREATE TABLE #licenselist
    (dbid smallint, dbname sysname, system_license varchar(99));

DECLARE @fixedname nvarchar(150);

--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
    SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
    FROM master.sys.databases
    WHERE state_desc = 'ONLINE'
    AND HAS_DBACCESS(name) = 1                  --Added at @SolomonRutzky's suggestion
    AND name NOT IN ('master','msdb','model','tempdb');

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @fixedname;

WHILE @@FETCH_STATUS = 0
BEGIN
       EXEC ('USE ' + @fixedname + ';' +
        'INSERT INTO #licenselist
         SELECT DB_ID() as dbid, DB_NAME() as dbname, ParamValue as system_license
         FROM Table1
         WHERE paramname = ''SYSTEM_LICENSE'';');

       FETCH NEXT FROM dbCursor INTO @fixedname;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #licenselist

DROP TABLE #licenselist

EDIT: streamlined a bit, removed the raw @dbname variable and instead fixed it in the original query.


Query not returning items in the original order Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it:

Notice how items are sorted by media_type_id, image_weight, image_id and finally image_width DESC.
If I run the following query:

SELECT -- <images>
         pcs.image_type AS [@Type]
        ,pcs.image_type_id AS [@ImageTypeId]
        ,(
            SELECT -- <imageCollection>
                 pcc.image_id AS [@Guid]
                ,pcc.image_angle AS [@Angle]
                ,pcc.image_weight AS [@Weight]
                ,(
                    SELECT -- <image>
                         pci.mime_type AS [@MimeType]
                        ,pci.content_guid AS [@Guid]
                        ,pci.url AS [@Url]
                        ,pci.media_type_id AS '@MediaTypeId'
                        ,t.media_type_description '@Description'
                        ,(
                            SELECT DISTINCT -- <attribute>
                                a.meta_attribute_id '@Id'
                                ,a.meta_attribute_name  '@Name'
                                ,v.meta_value_name AS '@Value'
                            FROM [ADS2].dbo.ads_digital_content_meta m
                            JOIN [ADS2].dbo.ads_digital_content_meta_atr_voc a ON a.meta_attribute_id = m.meta_attribute_id
                            JOIN [ADS2].dbo.ads_digital_content_meta_value_voc v ON v.meta_value_id = m.meta_value_id
                            WHERE m.content_guid = pci.content_guid
                            FOR XML PATH('Attribute'), TYPE)
                    FROM
                        @ProductContent pci

                    JOIN [ADS2].dbo.ads_digital_content_media_type t ON t.media_type_id = pci.media_type_id

                    WHERE
                        pci.image_id = pcc.image_id

                    GROUP BY pci.content_guid, pci.mime_type, pci.url, pci.media_type_id, pci.image_width, t.media_type_description

                    FOR XML PATH('Image'), TYPE)

            FROM @ProductContent pcc

            WHERE pcc.image_type_id = pcs.image_type_id
            AND pcc.product_id = pc.product_id

            GROUP BY
                    pcc.product_id
                ,pcc.image_weight
                ,pcc.image_id
                ,pcc.image_angle

            FOR XML PATH('ImageCollection'), TYPE)

    FROM @ProductContent pcs

    WHERE pcs.media_type_id = 15
    AND pcs.product_id = pc.product_id

    GROUP BY
        pcs.image_type,
        pcs.image_type_id

    FOR XML PATH('Images'), TYPE

For some reason the the images don't come out in the right order. For instance, the image with width 400 comes first, instead of the one with width 640. My question is, what part of this query is changing the order from the way it is in @ProductContent? I'd rather just sort once before inserting into @ProductContent as the order by clauses in the XML query are a big performance hit.

Without an ORDER BY clause, the order the values are returned in will be unreliable, as they can be influenced by indexes and the data pages the individual rows are stored on.

If you require the data to be in a particular order, use the ORDER BY clause.


SQL Server Import/Export: cannot copy geo columns I'm trying to copy data from a SQL Server 2014 Express database to a SQL Server 2008R2 Express database.

Compatibility level for the 2008R2 Express database is set to 100.

As soon as I hit Next in the Select Source Tables and Views window, I get the following error:

  [dbo].[Client] -> [dbo].[Client]: The data type could not be assigned
  to the column "Address_Coords" in "SQL Server Native Client 11.0"

The column is a geometry column.

How can I copy data from one database to another when the database contains geo columns?

It appears Microsoft forgot to include the geometry and geography datatypes in the SQL Server Import and Export Wizard.

There is an excellent explanation and fix here which I will summarize below:

Open the SQL mapping file. In my case I was doing am Export from SQL
Express 2014 to SQL Express 2008R2 using the SQL Client method. So
the file I had to edit is: C:\Program Files\Microsoft SQL
Server\120\DTS\MappingFiles\SqlClientToMSSql10.xml (In some cases
you might have to run Notepad first as administrator and then select
the file otherwise it doesn't allow you to save the changes)
Copy the following to the file, just before the line that reads </dtm:DatatypeMappings>:

    <!-- geography -->
      <dtm:DataTypeMapping >
        <dtm:SourceDataType>
          <dtm:DataTypeName>geography</dtm:DataTypeName>
         </dtm:SourceDataType>
        <dtm:DestinationDataType>
          <dtm:SimpleType>
            <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
          </dtm:SimpleType>
        </dtm:DestinationDataType>
      </dtm:DataTypeMapping>

      <!-- geometry -->
      <dtm:DataTypeMapping >
        <dtm:SourceDataType>
          <dtm:DataTypeName>geometry</dtm:DataTypeName>
        </dtm:SourceDataType>
        <dtm:DestinationDataType>
          <dtm:SimpleType>
            <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
          </dtm:SimpleType>
        </dtm:DestinationDataType>
      </dtm:DataTypeMapping>


How can I ensure that in a insert trigger a column gets filled before other column? The bigger picture:
tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each pipeline into segments of maximum 1 meter distance. Then, for each segment I am doing a spatial query that selects the closest street (in a maximum 20 meter range). Then, by finding out which street appears the most in all the segments that belong to that specific pipeline(by id), I am inserting the found street name into the pipeline feature.

In a trigger (on insert, update) the function is making a join based on the unique id column.

Having tableX (id, name)

the function that gets triggered looks like this:

CREATE or REPLACE function T_function() RETURNS trigger AS $T_function$
BEGIN
NEW.name =(SELECT ......... WHERE other_table.id = NEW.id .....);
RETURN NEW;
END;
$T_function$ LANGUAGE plpgsql;

As you can see, the function does a join based on the newly created id. It works only on update, probably because the NEW.name does not use the new id. How can I enforce saving/using/generating NEW.id before NEW.name ..?

later edit describing the tables (with postgis geometry) and what I am trying to do:

CREATE TABLE tableX (
    id integer,
    name varchar,
    shape geometry
);
--   geometry - linestrig

CREATE TABLE tableY (
    id integer,
    name varchar,
    shape geometry
);
-- geometry - linestring

CREATE or REPLACE function T_function RETURNS trigger AS
$T_function$
BEGIN
    NEW.id = NEW.id;
    NEW.name = (
        SELECT name FROM (
            SELECT id, name, cnt
            FROM (
                SELECT
                    id,
                    name,
                    COUNT(*) AS cnt,
                    RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
                FROM (
                    SELECT loc.id, r.name, ST_Distance(r.shape, loc.shape) AS dist
                    FROM (
                        SELECT id, (st_dumpPoints(st_segmentize(shape, 1))).geom AS shape
                        FROM tableX
                    ) as loc
                    INNER JOIN tableY AS r ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE loc.id = NEW.id
                    ORDER BY loc.id, ST_Distance(loc.shape, loc.shape)
                ) AS coco
                GROUP BY id, name
            ) AS tg
            WHERE rnk = 1
        ) as b
    );
    RETURN NEW;
END;
$T_function$
LANGUAGE plpgsql
;

CREATE TRIGGER T_function_trigger
BEFORE INSERT OR UPDATE ON tableX
FOR EACH ROW EXECUTE PROCEDURE T_function()
;

I am trying to do a spatial query and to fill tableX.name column with the name from tableY.name column based on the nearest neighbor approach. It only works on update for now.

If I understood correctly, this query will return the streets and counts you are looking for (for all pipelines and streets):

SELECT x.id, y.name, count(x.id) AS cnt
FROM
    (
        SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
        FROM
            tableX x
            INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
    ) t
GROUP BY x.id, y.name
ORDER BY cnt DESC
FETCH FIRST ROW ONLY
;

Now let's pack it in a trigger procedure:

CREATE or REPLACE function T_function RETURNS trigger AS
$T_function$
BEGIN
    SELECT name
    INTO NEW.name
    FROM
        (
            SELECT x.id, y.name, count(x.id) AS cnt
            FROM
                (
                    SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
                    FROM
                        tableX x
                        INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE x.id = NEW.id -- we count the relevant pipeline only
                ) t
            GROUP BY x.id, y.name
            ORDER BY cnt DESC     -- we don't care the other
            FETCH FIRST ROW ONLY  -- streets in the list
        )
    ;

    RETURN NEW;
END;
$T_function$
LANGUAGE plpgsql
;

the line NEW.id := NEW.id did nothing useful
I flattened the innermost subquery into a simple JOIN (no need for the subquery)
to decrease the level of nested parentheses, I used SELECT name INTO NEW.name instead of NEW.id := (...)


Need to setup a sql alert on "waiting tasks" - similar to "waiting tasks" in Activity Monitor My manager has asked me to work upon a request. I am not sure if it is reasonable or weird but he wants me to setup an alert based on the "waiting task" that we see in the activity monitor. Basically, if the value in the "waiting task" goes above 10 then we need to be alerted upon. I am not sure as to how I would replicate the same information that we see in Activity monitor as this seems to be live data. Closest DMV and the column I could think of is "waiting_tasks_count" in sys.dm_os_wait_stats. However, this does not seem to be in line with what he is looking for and the column consists of cumulative values over a period of time since last time SQL was restarted. Can someone shed some light on where I can find a query or a counter that I can alert upon?

I have thought about a query at this time but I think I can't calculate the delta correctly it always shows me 1. Can someone help me out exactly where I am going wrong here.

select  so1.wait_type,
        so1.waiting_tasks_count as no_of_waits,
        so1.waiting_tasks_count - so2.waiting_tasks_count as delta_wait_count,
        so1.signal_wait_time_ms,
        so1.wait_time_ms - so2.signal_wait_time_ms as resource_wait_time,
        getdate() as sample_time
from  sys.dm_os_wait_stats as so1
inner join sys.dm_os_wait_stats as so2
on so2.waiting_tasks_count = so1.waiting_tasks_count - 1

you can trace out the exact query in profiler..below is the query you are referring to

WITH profiled_sessions as (
    SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT
   [Session ID]    = s.session_id,
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [Login]         = s.login_name,
   [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end,
   [Task State]    = ISNULL(t.task_state, N''),
   [Command]       = ISNULL(r.command, N''),
   [Application]   = ISNULL(s.program_name, N''),
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''),
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  =
        CASE
            -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
            WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
            -- session is either not blocking someone, or is blocking someone but is blocked by another party
            ELSE ''
        END,
   [Total CPU (ms)] = s.cpu_time,
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024,
   [Memory Use (KB)]  = s.memory_usage * (8192 / 1024),
   [Open Transactions] = ISNULL(r.open_transaction_count,0),
   [Login Time]    = s.login_time,
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''),
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = ISNULL(g.name, N''),
   [Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
    -- waiting for several different threads.  This will cause that thread to show up in multiple rows
    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
    -- and use it as representative of the other wait relationships this thread is involved in.
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;


Can I take backup of those tables which are created with NOLOGGING in Oracle? Can I take backup of those tables with data which were created with NOLOGIING option in Oracle.

Is it true that backup can not be taken for these tables and I can not restore my data if I have some crash or corruption.

You can. From backup (rman) perspective there is no difference between LOGGING and NOLOGGING tables.

What you cannot do is restore through NOLOGGING operation. That means that if recover procedure will encounter such operation in archive log it is currently applying table will be marked as INVALID.


MySQL nested queries I'm working on a project using a MySQL database and we'd like to combine a query which was previously made in several steps into one step.
The queries are created in a Java-App using Hibernate.

Goal
The goal of the query (or queries up to now) is the following: Loading a subset of data from several different tables (according to some filters and sorted).

Database Structure
The structure of the database can not be changed. I'll show here the needed tables with some example entries.

Table t1

id         class    counts      ... other parameters
-------    -----    ------
1          B5.2     124831
2          A7.9     83482
3          M5.5     53124812

Table t2

event_id    reconstruction_algorithm       ... other parameters
--------    ------------------------
1           0
1           0
1           0
1           1
1           1
1           1
1           2
1           2
1           2
1           3
2           0
2           0
2           0
2           0
2           1
2           1
2           1
2           2
2           2
2           2
2           3
2           3
...         ...

Desired Result
I'm trying to find a query which can (in one step) do the following:

Get selected entries of table t1 (e.g. id and class) for some rows which meet some filter criteria (e.g. counts between 100000 and 150000)
Get the maximum amount of entries withe the same reconstruction_algorithm which belong to a single entry in t1 (e.g for the entry with id 1 this would be 3)

The problem

Up to now we used two requests. The first was used to get the entries of t1 which meet the filter criteria:

SELECT class, counts FROM t1 WHERE parameterX <= 123 AND parameterY >= 20;

The second was used to get the according amount of entries from t2:

SELECT MAX(tm.tNum) FROM (SELECT COUNT(*) as tNum from t2 t, t1 q WHERE q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm;

Now I'd like to combine those two queries into one query. I tried doing this the following way:

SELECT q.id, q.class, MAX(tm.tmNum) AS nmbr
FROM t1 q, (SELECT COUNT(*) as tmNum from t2 t, t1 q WHERE
                q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm
WHERE q.parameterX >= 123 ORDER BY q.counts DESC;

The problem is now, that this query (tried in different ways) does return only a single row, although the outer query returns several rows. So I'd like the inner query to be run for every result of the outer query.

Is there a way to achieve this?

I hope somebody knows the answer to this problem.

I think this query does what you are asking. The sub select gets the counts for each id. I then joined it back to t1. The outer query gets the max for each id. I didn't put the WHERE clause since I don't know the criteria but you can easily add it.

Here's a link to SQL Fiddle

select id,
  class,
  counts,
  max(numOcc) as Occ
from t1
left join
(SELECT event_id, reconstruction_algorithm, count(1) as numOcc from t2
  group by event_id, reconstruction_algorithm) a
on t1.id = a.event_id
group by t1.id, t1.class, t1.counts


Which MySQL files in InnoDB are made of pages and how are they structred? Definition of InnoDB page is here : https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

  InnoDB stores all records inside a fixed-size unit which is commonly called a "page" (though InnoDB sometimes calls it a "block" instead). Currently all pages are the same size, 16KB.
  A page contains records, but it also contains headers and trailers. I'll start this description with a high-altitude view of a page's parts, then I'll describe each part of a page. Finally, I'll show an example. This discussion deals only with the most common format, for the leaf page of a data file.

And based on their documentation, database tables and their tuples are stored in pages, for example a part of each page contains the row data which is the tuples

what i don't understand is :

which files inside the mysql folder are made of these pages? only the .ibd files or..? and do these files contain any meta data at the beginning of them to show info about these pages and their addresses?
when i want to read a particular row, lets say with the PRIMARY id of 1, what are the files that DBMS reads and which parts of them? I'm asking about the DBMS interaction with files, i want to know for example does it first read the .frm file to know the metadata of that table then reads the .ibd file of that table? and then goes down in the B+tree aka that .ibd file and finds the correct page in the clustered Index? or am i wrong?

With innodb_file_per_table = ON and 5.7 or older:

Each DATABASE is manifested on disk as a directory (with the database name).
In that directory are a few files for each TABLE in that Database.
The .frm file contains (effectively) the schema for the table, and essentially nothing else.
The .ibd file contains one or more B+Tree.  One B+Tree contains the data, organized by the PRIMARY KEY.  Each secondary key lives in a similarly structured B+Tree, but organized by the key, and containing a copy of the PK in the leaf nodes.

Meanwhile, there is some meta information about the table in the common ibdata1 file.  (This implies that moving the .ibd file by itself will screw up the integrity of the database.)

File_per_table=OFF and "tablespaces" are variants on the above.  In the old days, before .ibd files, all tables were in ibdata1.

MySQL 8 moves a lot of the meta info, plus the schema, into the "Data Dictionary".  This is implemented using InnoDB.  (I cringe at the bootstrap issues.)

To find the row uniquely identified by PRIMARY KEY = 1234, here is what happens:

If not already opened, the table is "opened" and put in a cache of open tables.  Some information from the .frm (or DD) is copied there.  The .ibd file is located (or the table is found in ibdata1), etc.
Locate the PK in the table.  Get its 'root' node.
Drill down that B+Tree until you find the 'row' in the leaf node (aka page, aka block) that has id=1234.  On average, there will be 100 rows in that block, some before 1234, some after.  So, in a million-row table, the "drill-down" might hit 3 blocks.  Since blocks are cached in the buffer_pool, this involves between 0 and 3 disk hits (usually toward the 0 end).
Deliver all the columns (or whatever is requested).

Note:  If the lookup is by a secondary key (that is, not 'clustered'), there are more steps.

Also contained in each data block are a few things to facilitate traversing the rows and the blocks.  (Think the + in B+Tree; read Wikipedia if necessary.)

Within each row in the block there are other things:

Fields, and their lengths, possibly pointers to off-record large chunks for TEXT and BLOB.  (This gets around the 8K pseudo-limit on a row.)
Transaction ids for MVCC
A "history" of the revisions (again think MVCC) of each row that has been modified, but has not yet been commited/resolved (think ROLLBACK and transaction_isolation).  The Undo log is also involved in these things.

Each block anywhere in the running MySQL system is uniquely identified identified by a rather large number.  Part of it the 'tablespace' (ibdata1, foo.ibd, etc.)  Part of it is the block number.

("Tuples" is a rather general term; I don't know what you are referring to here.)