Meta-relationships (Relationships between relationships) versus tuples I am designing a database which needs to store some fairly complex relationships among many different types of entities.

In the simplest example, let us say I have three types of entities - "Student", "Tutor", and "Course", each with their own tables (student, tutor, course).  First of all, I need to represent simple many-to-many relationships between each possible pair of entity types:

student_course - Each student can be enrolled in zero or more courses, and each course can have zero or more students.  (ex: "Alex is enrolled in Biology").
student_tutor - Each student can be working with zero or more tutors, and each tutor can be assigned to zero or more students. (ex: "Emma is assigned to Alex").
course_tutor - Each tutor can be approved to teach zero or more courses, and each course can have zero or more approved tutors. (ex: "Emma knows how to tutor Physics").

Easy enough.  However, I also need to represent some more advanced relationships, like:

"Tutor John is working with students Alex and Maria on course Chemistry"
"Tutor John is working with student Alex on course Algebra"

Notice that these are not captured by the binary many-to-many relationships I described earlier - John can be tutoring Maria, and Maria can be enrolled in History, but that doesn't mean that John is tutoring Maria in History.

The way I see it, I have two options:

Option 1 - A single table containing triplets

Table student_course_tutor:

| student_id | course_id | tutor_id |
|------------|-----------|----------|
| 1          | 1         | 1        |
| 1          | 1         | 2        |
| 1          | 2         | 5        |

This is nice because it explicitly lays out the full relationship in a single row.  However, I now need to maintain this table in addition to my simple relationship tables.

Option 2 - Meta-relationships

Table student_course:

| id | student_id | course_id |
|----|------------|-----------|
| 1  | 1          | 1         |
| 2  | 1          | 2         |

Table student_course__tutor:

| student_course_id | tutor_id  |
|-------------------|-----------|
| 1                 | 1         |
| 1                 | 2         |
| 2                 | 5         |

Since I was going to have a student_course table anyway, adding this "meta-relationship" table is less redundant than representing the full triplet.  However, it now becomes a lot more abstract and difficult to understand.

Which way is more correct?

I like option 1 better. My first thought is always maintaining data integrity so my question is, "Could it be possible to relate a student to a tutor for a course the tutor is not approved for?" With table student_course_tutor, the field student_id would, as you say the student needn't be attending the course, be a FK to the Students table and the fields (course_id, tutor_id) would be a FK to the course_tutor table. If the requirement that the student must be enrolled in the course as well as the tutor must be approved for the course, that would require only that the fields (student_id, course_id) be a FK to the student_course table.

So you cover the existing specifications and you have an easy solution for a not unreasonable future requirement.


SSISDB user "AllSchemaOwner" is not SQL Server Login Did some research in SSISDB and found a user AllSchemaOwner which is not associated with any SQL Server login:

SELECT p.name, p.type_desc
FROM SSISDB.sys.database_principals as p
LEFT JOIN sys.sql_logins as l ON p.sid = l.sid
WHERE p.principal_id > 4 and p.type = 'S' and l.sid is Null;

However, there are plenty of objects in the database, which were created with credentials of that account.

I've tried to run one of these procedures and it was executed successfully.
When I've tried to recreate that scenario in my test database it returned me an error:

USE [master]
GO
CREATE LOGIN [Test2] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [MyTestDB]
GO
CREATE USER [test3] FOR LOGIN [test2]
GO
SELECT 1 as test INTO tbl_Test;
GO
GRANT SELECT ON tbl_Test TO [test3]
GO
CREATE PROCEDURE sp_Select_TEST
WITH EXECUTE AS 'Test3'
AS SELECT * FROM tbl_Test;
GO
EXECUTE sp_Select_TEST;
GO
DROP LOGIN [test2];
GO
EXECUTE sp_Select_TEST;
GO
DROP PROCEDURE sp_Select_TEST
GO
DROP TABLE tbl_Test
GO
DROP USER [test3];

Msg 15517, Level 16, State 1, Procedure sp_Select_TEST, Line 0 [Batch Start Line 29]
Cannot execute as the database principal because the principal "test3" does not exist, this type of principal cannot be impersonated, or you do not have permission.

So, the question is: How it is possible that procedure created with an option WITH EXECUTE AS 'AllSchemaOwner' can be successfully executed while SQL Server login does not exist for that user?

Finally found an answer:
You can create database user, which won't have a SQL Login and won't be able to authenticate, but will have permissions and can be specified in WITH EXECUTE AS clause for stored procedures and functions.

The simple way to create such a user is just to use clause WITHOUT LOGIN during user creation:

Use SSISDB;
GO
CREATE USER 'AllSchemaOwner' WITHOUT LOGIN;
GO


How to select datas for a particular user between a date range I am querying a table with where condition in which i want to choose between a particular date range at present my query is like this with a where condition.

WHERE entered_by = '1' AND date_created = '2018-08-23'

I want to use a particular date range from to to how can i do this?

Don't use functions on indexed columns.

Don't put dates in VARCHAR; use DATE, DATETIME, TIMESTAMP, etc.  MySQL does not understand tz info, as in 2018-07-16T09:48:30+00:00; deal with it before storing into the date column.

Do test ranges this way:

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 7 DAY

to test for a week.  Note that you don't need to worry about leap years, etc.  For a single day, do use

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Do not use this (or CAST):

WHERE DATE(date_created) = '2018-08-23'    -- inefficient

because no index can be used.

So, if your query is

WHERE entered_by = '1' AND date_created = '2018-08-23'  -- original

change it to

WHERE entered_by = '1'
  AND date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Note further:  This will work regardless of whether date_created is DATE, DATATIME, or even DATETIME(6).  And it avoids the clumsy 23:59:59 (which won't be adequate for DATETIME(6)).

Also, have this "composite" index:

INDEX(entered_by, date_created)   -- in this order

A further note:  BETWEEN leads to the "midnight bug", wherein you accidentally include an extra midnight.  Note that my code has >= and <, not <=.


What is the meaning of different roles available(DB Admin etc.) I have recently installed MySQL and also created the basic user as scott but my doubt is regarding the roles. I don't understand different roles available for the user which I have created.

I want the user to simply create tables, must be able to perform delete and update operations but restrict him from able to change the password and view the complete database available (all the records).
Based on my above requirement which kind of role should be given to scott?

Roles and groups are not supported by MySQL Server. Groups are sets of users. The concept of roles is a group of permissions that can be assigned to a group or a user.

Workbench emulates roles by handling them at client side: Adding a role on workbench. You can access this tab on the Users and privileges tab.

  I want the user to simply create tables, must be able to perform
  delete and update operations but restrict him from able to change the
  password and view the complete database available (all the records).

In order to do what you want, you can add a new role on the previous mentioned tab with no administrative privileges and "all object rights" and "DDL rights" for a particular schema (Add entry...). You can fine-grain it more by not adding INSERT rights or DROP right, as you see it conveniently. If you restrict it to a single schema, it won't be able to see or modify any other table from others, except the administrative schema information_schema.

If you feel like creating the user by using SQL (when connected as an administrator):

CREATE USER <username>@<host> IDENTIFIED BY '<password>';
GRANT ALL ON <schema>.* TO <username>@<host>;

Obviously, change <username>, <host>, <password> and <schema> for the right values.

Please note that a user in MySQL can always change its own password, but not that of others (unless it is an administrator).


Database for opening times of locations I'm designing a database for opening times and created this solution.

The specifications of the database which will be a MySQL are, that a location have standard opening times in a week and can have special opening times for example on christmas or something like that.

So if I want the opening times of an entry I would first search in 'special_opening' for the given day and if it given no results back, I would load the data from 'opening'.

Is this a legit way to realize the database?
Are there better ways to do it?

You could simplify your design and make it more powerful by keeping all of the opening times in a single table which includes a date range and a priority code for resolving conflicting records.

It would look something like this:

create table OPENING
( id INT
, entry_id INT
, from_date DATE
, to_date DATE
, priority INT
, weekday_id INT
, start TIME
, end TIME
, pause_start TIME
, pause_end TIME
, editor_id INT
, timestamp TIMESTAMP
)

The differences to note between your model and this one are:

You keep one set of records for each date range that applies instead of just having the current data.
You can keep a date range history of opening hours.  This also allows you to future date ranges which is handy for getting data set up in advance.
You keep regular hours and exceptions in a single table, so you don't need to have conditional logic in retrieving the opening hours.
Exceptions have a date range also, often a range of one day if that is what applies.
All records have a priority level.  Regular records have a low priority, exceptional records have a higher priority.  When reading just select top 1 order by priority descending.


Migrate SQL Server - server name in stored procedures references old server I will migrate an SQL server from 2000 to 2008 R2. It contains two instances. I have many stored procedures that are referencing the instance names like:

select ... from [old_server\instance1].db1.dbo.table1

What is the best way to deal with these stored procedures?

Modify all stored procedures and change these occurrences
from [old_server\instance1].db1.dbo.table1 to [new_server\instance1].db1.dbo.table1
Do not change the stored procedures, but create a reference from the old server to the new one using sp_setnetname
Rename the new server after the migration to keep the old name

You could create a synonym, say:

CREATE SYNONYM dbo.remotetable1 FOR [new_server\instance1].db1.dbo.table1;

Then the server name is just in the synonym for the object, and not littered throughout the code - the code just references dbo.remotetable1.

When you need to change where it points, you just change the synonym once, and that can include consolidation (where the server prefix is no longer required).

I wrote up a little about synonyms here:

#BackToBasics : The Beauty of the Synonym


MySQL binary log reporting execution time as UINT_MAX Looking through the MySQL Binlogs, I see some data that just does not make sense.  There are queries that say they have been running for 4294967295 seconds (which also happens to be UINT_MAX on this system).

The queries that are reported with these execution times vary, but they are typically updates to small tables. There is no way that these queries have been running for that long.

Percona has a blog stating that the exec_time value can be different than one would expect if the server is a MySQL replication slave, but this is not the case.

Are these strange values for exec_time in the binary log expected, or is this a bug in MySQL?

The unsigned value 4294967295 has the same binary representation as signed -1.  So I suggest that it is reporting "-1 seconds".

Either your machines are not in sync on time, or it is a bug in MySQL.

I have several seen other cases where "the clock ran backwards", even on machines that are synced with the time service.

Consider finding or filing a bug with bugs.mysql.com


When I try to update/set a datetime I get an invalid mysql syntax 1064 I copy a value from the pre-existing values.

I update the date.  I do an update query.

It has an issue and the issue seems to be the time.

Here's my query:

UPDATE xxxxx_eb_events
Set event_date 019-04-01 00:00:00;

event_date is of type datetime

And the error

    Error
    Static analysis:

    2 errors were found during analysis.

    Unexpected character. (near ":" at position 52)
    Unexpected character. (near ":" at position 55)
    SQL query:

        UPDATE gsti1_eb_events Set event_date 019-04-01 00:00:00

        MySQL said: Documentation

        #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '019-04-01 00:00:00' at line 2

It's complaining about the colons in the time but I copied and pasted them from the already existing entry.

I know 019 looks like a strange year, I have tried it with

UPDATE gsti1_eb_events
Set event_date 19-04-01 00:00:00;

And I have tried

UPDATE gsti1_eb_events
Set event_date 2019-04-01 00:00:00;

And I have tried enclosing all of them in single quotes

9.1.3 Date and Time Literals

Enclosing that datetime string in single quotes, 'YYYY-MM-DD HH:MM:SS' should work:

UPDATE xxxxx_eb_events
Set event_date = '2019-04-01 00:00:00';


Postgresql revert back database value I have postgresql database.
My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table.
When I get the data file using following commands.

select * from pg_class where relname = 'tblvoippolicy' ; I get the following result

    relname    | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
 tblvoippolicy |        17234 |  407227 |      100 |     0 |      407226 |             0 |        0 |         0 |             0 |             0 | t           | f           | r       |        8 |         0 |           0 |        0 |        0 |       0 | t          | t          | f           | f              |

Now, when I look that data into data file of database using cat /var/pgsql/data/base/17230/407226. I get following result.

[root@manage upload]# cat /var/pgsql/data/base/17230/407226
ôu\"(Ô  Ÿà Ÿà¨žð8žàÔÈM ï¡test EC250
@%
ÿ10.106.1.228Q   ï–BEC200_Post_250      EC200
ú%
ú10.106.1.228?   ï”BBS_Service_Pre_250bs_service_1
@&
ú10.106.1.2286   ï’BEC200_Pre_500       EC200
@&
ô10.106.1.228+   ïBEC200_Pre_250       EC200
@&

I can not understand the described values and I want to revert back the records which I have deleted from table, how can I get back that records ?

See if this helps.  I would take a backup right away because if vacuum cleans out the deleted row it's gone and you won't have any chance to recover it.

Recover Deleted Row


MySQL ORDER SELECT based on a score I have a table 'member' and want to be able to search the table and order results by relevance. I have a single string to search on and want to search multiple fields. Example, given_name and family_name (like the Facebook member search).

The fields in in the member table are strings so I think a full test search will not be the best option.

Table Member

+----+------------+-------------+----------------+
| id | given_name | family_name | mutual_friends |
+----+------------+-------------+----------------+
|  1 | Levi       | Putna       | 4              |
|  1 | Levi       | Levi        | 1              |
|  2 | Leviother  | Somename    | 4              |
|  3 | Boo        | Somelevi    | 4              |
|  4 | Lala       | Smith       | 4              |
|  5 | Assem      | Jones       | 4              |
+----+------------+-------------+----------------+

I have been trying to build a query that creates a score based on matching some criteria and then ordering by the score. I am using a case to do this.

For this example we will use the search query of 'levi'

SELECT id, given_name, family_name,
CASE
    WHEN given_name = 'levi' THEN score + 5
    WHEN given_name like 'levi%' THEN score + 3
    WHEN family_name = 'levi' THEN score + 4
    WHEN family_name like 'levi%' THEN score + 3
    WHEN given_name like '%levi%' THEN score + 1
    WHEN family_name like '%levi%' THEN score + 1
END AS score
FROM (select *, 0 AS score FROM member) AS member_search

ORDER BY score DESC

This seems like a bad way to do this and will only score based on the first match in the case. How would I need to rewrite this query so that it could continue to add to the score based on multiple matches of case? Is their a better way to manage this type of query?

To respond to your question, if you want to validate multiple conditions for a case statement, you can use coalesce or nest case statements.

However judging by what you want to do, it might be best to handle this within your application.
SQL Server is not made for computing, it's a database, inherently meant to store and produce data. As such it's been highly optimized for just those types of tasks (storing and handling data) and not for handling the data after it's been found.

Quick edit to link MySQL case nesting after noticing you're talking about MySQL not MSSQL.


Pros and cons of accessing sql server using ssms locally over on the actual server I need your help DBAs, I need to know the pros and cons of my question.

We have this new "DBA", who doesn't know about DBA really, he got promoted to DBA temporarily to become an IT supervisor, He does not know any sql and no scripting skills.

He just took out all our developers' access to sql server to connect locally. I want to justify to our director that whatever this guy is doing is not comfortable for us..accessing the server and running the ssms.

Btw, he just changes access without even sending any courtesy emails. I need your advise on whether I'm wrong. I'm a developer for this company and for my past 10yrs, I've never worked in a company where you actually run your SSMS on the actual server.

Here's what I gather and my opinion.

Risk to accidentally rebooting the SQL Server.
Data security on the server.

I wish to add more so I can present this to my Director, I'm just not comfortable doing non-server stuff on the server period.

As a general practice we never even install SSMS on the server hosting SQL Server.

You got the first two already

1.Risk to accidentally rebooting the SQL Server.

2.Data security on the server.

However there are more:

First of all each session that goes to the server will take up resources (Memory/Cpu/Diskspace,DiskIO) needed by the SQL Server engine, this means a server might get under pressure by allowing people to directly connect on the Server. Which will slow down performance and might even cause timeouts to your applications.

When accessing the SQL Server Server, you can copy and duplicate data on the disks if there is not a good security structure, meaning you can easily fill up the diskspace where your sql server database files are located. Meaning your files won't be able to grow any more

If you have not set up the security rules well, you are able to install other software on the servers also taking away precious memory from the SQL Server server.


DROP DATABASE fails due to foreign key constraints This is weird and I haven't ever encountered this.

I'm simply dropping a whole database.

$ mysql -u $RDS_USER -p$RDS_PASS -h $RDS_HOST -e "DROP DATABASE dev_test"

And yet it fails with this...

  ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails

I then go to the database itself and I see that most of the tables have been dropped except for two.

Why does this happen?

I'm using MySQL 5.6.

Try the DROP DATABASE a second time.  Since most tables are gone, that may have relieved enough constraints to allow the last two tables to go.

Or...  Do those two tables have FKs to each other?  In this case, neither can be dropped "first".  Hence, Greenforest35's answer (turn off FKs) seems to be the only solution.

Consider filing a bug report with http://bugs.mysql.com saying explaining that DROP DATABASE should be smarter -- such as ignoring the FKs automatically.  (Or at least the ones that will be removed later by later tables -- this would take some analysis.)

Worse yet...  Are there constraints between your remaining tables and table(s) in other database(s)?

(So many reasons not to bother with FKs.)


Standards for storing a rating as a range (1-5, 1 is best, etc.) I am designing a ROAD_INSPECTION table that has a CONDITION field:

+---------+-----------+
| ROAD_ID | CONDITION |
+---------+-----------+
|       1 |         2 |
|       2 |         5 |
|       3 |         1 |
|       4 |         1 |
|       5 |         4 |
+---------+-----------+

Are there any standards or conventions that suggest how to format ratings as ranges?

Example, what's the best format for the CONDITION rating?

0 to 5, zero is best
0 to 5, five is best
1 to 5, one is best
1 to 5, five is best

Rule of thumb:  The higher, the better.

All that said, I'd suggest to use a lookup table to store rating definitions. Maybe for an specific question you want to use a rating of 0 to 5, but may be tomorrow you need a bigger or a lower ratio.

Or simply you want to change the name of each option.

Elated
Happy
Indifferent
Unhappy
Miserable

Using a lookup table you can choose different options for different purposes. Sometimes you need 5 options, sometimes 10 and sometimes 2.

Do not restrict yourself with a 5 options.

  Life has a lot of flavors:

  o  True
     o  False
     o  Do not know/ Do not answer


Oracle materialized view refresh schedule Since I see that it is possible to specify an expression like sysdate + 1, here are my questions:

Is it possible to specify the exact time of the refresh for materialized views in oracle?
Is it possible that it all starts at 2 a.m. while there is nobody using the db from my application?

Your expression can be as complicated as you'd like so you can certainly specify that the refresh should happen every day at 2 AM, i.e.

NEXT trunc(sysdate+1) + interval '2' hour

will specify that the refresh happens every day at 2 AM.  The expression is evaluated at the conclusion of each refresh so you just need to ensure that the expression evaluates to whatever time you want at that particular instant in time.


Where I run mysqldump or pg_dump, Both services (mysql or postgres) freeze? I need to create a database without stopping the services and I don't know this answer.
I need my web pages to continue working properly in that time.
Can someone answer me?

MySQL answer because I don't know pg_dump.
To allow SQL to continue with mysql during a mysql dump the following is required:

Tables needs to be innodb. Check with show create table {tablename}, change with alter table {tablename} Engine=innodb if it isn't (will cause the table to be unavailable during the change).

Use mysqldump with --single-transaction --master-data=2. Single transaction is the main point to get a consistent snapshot. --master-data enables a point in time recovery if you have binary logs enabled.

The transactional dump will enable the website to continue to read and update date while the mysqldump is occurring. The data in the mysqldump will be the data on the server at the point the backup began.
Note you cannot modify tables during a mysqldump. In MySQL table structure changes aren't transactional.


How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans? Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the development team.

SELECT
        c.Start
        ,c.[End]
        ,c.Word
        ,doc.UniqueDocumentNumber
        ,doc.EID
        ,c.CUI
        ,c.Concept
        ,a.OID
        ,doc.DocumentTypeName
        ,doc.ActivityDtTm
        ,CAST(doc.DocumentTypeId AS INT) AS MedCode
        ,CASE WHEN c.[Count] = 0 THEN  CAST(0.00 AS REAL)
           ELSE CAST(LOG(c.TotalCount / c.[Count]) AS REAL) END AS 'idf'
        ,c.[Count]
        ,c.TotalCount
FROM ECHO..AEID201 a
INNER JOIN ALPHA..XADocuments doc (NOLOCK) ON a.EID = doc.EID
CROSS APPLY (SELECT t.start,t.[end],t.word,t.cui,t.eid,
   t.UniqueDocumentNumber,cu.[Count],cc.TotalCount,core.Concept
FROM HOTEL.dbo.Htf_Index AS t
INNER JOIN HOTEL..Doc_CUI_Counter AS cu ON cu.CUI=t.CUI AND cu.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..Doc_Counter AS cc ON cc.DocumentTypeID=t.DocumentTypeID
INNER JOIN HOTEL..CUI_CORE AS core ON core.CUI=cu.CUI
WHERE t.eid = doc.eid AND doc.UniqueDocumentNumber=t.UniqueDocumentNumber
) AS c
WHERE a.OID='00005159-9567-4187-a278-5b8e1e2ed20c'
AND ISNUMERIC(doc.DocumentTypeId) = 1

On Server B, review of the execution plan indicates that the criteria for table [a] at bottom is part of an index seek operation at the start of the execution plan, which makes the subquery [c] resultset execute rapidly.

On Server A, review of the same execution plan indicates that subquery [c] is executing first with full index scans due to outer criteria not being applied.

Indexes utilized in both execution plans are identical. Table rows counts are slightly larger on Server A since the restore due to normal operations but index fragmentation is nearly identical. A participating index on table [t] in the subquery on Server B has double the number of pages as on Server A but identical row count. Statistics are updated nightly at the same time on both servers.

I've attempted index rebuilds on table [t] and manually updating statistics to attempt to get both execution plans to match. What other factors may be causing this change in the order of execution?

I suggested to the developer replacing the subquery with a UDF that takes the EID and UniqueDocumentNumber fields from [doc] as arguments. What other options can I explore with the developer to increase the probability of the execution plans on QA being utilized on Prod?

Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but...

  How Can the Same Query in Two Nearly Identical Instances Generate Two
  Different Execution Plans?

Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics.

  Table rows counts are slightly larger on Server A... Statistics are
  updated nightly at the same time on both servers.

Row counts are different and the statistics update was probably a default sample, rather than FULLSCAN. I've witnessed some comically unfortunate stats histograms as the result of sampling, each of which has been corrected by a FULLSCAN update.


Why can SELECT see updated row before commit? If I run this without a commit statement...

begin transaction

update dateranges set DateRangeTypeId = 1 where DateRangeId = 154

In another tab of Management Studio, I can select the range with Id 154 and it shows the old value for the type column.

Why doesn't the SELECT block on the updated row?

Assuming the second SSMS window is using the default READ_COMMITTED isolation level, you will see the before value instead of being blocked by the uncommitted transaction if the database has the READ_COMMITTED_SNAPSHOT setting turned on.

SELECT name, snapshot_isolation_state_desc
FROM sys.databases
ORDER BY name;

The READ_COMMITTED_SNAPSHOT database setting uses row versioning instead of locking for read consistency so writers don't block readers and visa-versa.


Take advantage of monotonic columns in PostreSQL I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like:

SELECT * FROM table WHERE col1 >= val1 AND col2 <= val2;

(for certain val1 and val2 that I know a query time).

If I put btree indices on col1 and col2 PostgreSQL tries to execute the query performing an index scan on one of the two columns and then filtering on the other. This means that in most cases it has to sweep through around half of the table, even when the number of matching rows is very little. Adding a multicolumn index is useless, because PostgreSQL can effectively use it only when at least one of the two columns is tested for equality.

One important assumption that I can make on the values, though, is that the two columns are monotonic one respect to the other. This means that if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries.

This means that in line of principle the query execution could be sped up by performing an index scan on one of the two columns, filtering on the other and stopping the execution as soon as a non matching value is found on the second column. In this case the query would read just exactly the rows to be returned.

Is there any way to setup indices or whatever other invariant in PostgreSQL so that the query planner is able to detect this?

(of course the problem can be easily solved performing two queries, the first one to translate the inequality on col2 to an inequality on col1; I am asking if there is a way to avoid this workaround and let PostgreSQL manage the mess by itself)

if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries

In which case you can reformulate your query to look like:

SELECT * FROM table WHERE col2 >= val1 AND col2 <= val2;

because you can find the lower bound for col2 from the lower bound for col1, like this:

schema:

create schema stack;
set search_path=stack;
--
create table t(foo integer, bar integer);
insert into t(foo,bar) select 10*g, 20*g from generate_series(1,100000) g;
create index on t(foo);
create index on t(bar);

method:

explain analyse select min(foo) from t where foo>500; -- assuming val1=500

  ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                              QUERY PLAN                                                               │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Result  (cost=0.38..0.39 rows=1 width=0) (actual time=0.063..0.064 rows=1 loops=1)                                                    │
│   InitPlan 1 (returns $0)                                                                                │
│     ->  Limit  (cost=0.29..0.38 rows=1 width=4) (actual time=0.059..0.060 rows=1 loops=1)                                             │
│           ->  Index Only Scan using t_foo_idx on t  (cost=0.29..2803.63 rows=33167 width=4) (actual time=0.058..0.058 rows=1 loops=1) │
│                 Index Cond: ((foo IS NOT NULL) AND (foo > 500))                                                                       │
│                 Heap Fetches: 1                                                                                │
│ Total runtime: 0.087 ms                                                                                │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

  select min(foo) from t where foo>500;

  ┌─────┐
│ min │
├─────┤
│ 510 │
└─────┘

  select min(bar) from t where foo=510;

  ┌──────┐
│ min  │
├──────┤
│ 1020 │
└──────┘

  explain analyse select * from t where bar>=1020 and bar<= 1100; -- assuming val2=1100

  ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                     QUERY PLAN                                                      │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on t  (cost=13.42..485.00 rows=500 width=8) (actual time=0.011..0.013 rows=5 loops=1)              │
│   Recheck Cond: ((bar >= 1020) AND (bar <= 1100))                                                                   │
│   ->  Bitmap Index Scan on t_bar_idx  (cost=0.00..13.29 rows=500 width=0) (actual time=0.008..0.008 rows=5 loops=1) │
│         Index Cond: ((bar >= 1020) AND (bar <= 1100))                                                               │
│ Total runtime: 0.030 ms                                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

  select * from t where bar>=1020 and bar<= 1100;

  ┌─────┬──────┐
│ foo │ bar  │
├─────┼──────┤
│ 510 │ 1020 │
│ 520 │ 1040 │
│ 530 │ 1060 │
│ 540 │ 1080 │
│ 550 │ 1100 │
└─────┴──────┘

clean up:

drop schema stack cascade;


Single SQL query to retrieve data in a particular order (not ASC/DESC) I have a MySQL table, let's say called reviews
review_id, product_id, language_id, rating, review, author
language_id ranges from 1 to 10 for 10 different languages.

Let's say I determine that my visitor is browing the site using language whose id is 4

Is there a way to write a single query that:
1. First grabs the reviews for that product in current language
2. Next, grabs the reviews for that product in other languages sorted by language

So, basically does the job of the following 2 queries run one after the other:

SELECT * FROM reviews WHERE product_id = 723 AND language_id = 4;
SELECT * FROM reviews WHERE product_id = 723 AND language_id != 4 ORDER BY language_id ASC;

You can simply do

SELECT * FROM reviews
WHERE product_id = 723
ORDER BY language_id = 4 DESC, language_id ASC;

language_id = 4 returns true or false, 1 or 0. Sort descending and you have language_id = 4 first, then all others.


MySQL 5.7 - B-Tree indexes, performance and fragmentation I have a large table with a timestamp column and it holds a very large amount of data (a few million rows).

Whenever running a filtered query by range on the timestamp column for this table, depending on the size of the range, it takes a considerable amount of time (2+ seconds). This column has an index created for it which would lead me to believe that the query return should be faster.

I noticed that by destroying the index and re-adding it, the query performs easily 40% better.

Bellow is a simple example of how the table is structured:

create table foo (
  id int(11) not null auto_increment,
  fk_id int(11) not null,
  datecolumn timestamp,
  primary key (id, fk_id),
  key idx_timestamp (datecolumn)
  constraint fk_fk_id foreign key (fk_id) references bla (id)
 ) engine = InnoDB;

Any reason why the re-creation of the index would make the query run faster and how can I make this as part of a maintenance ? I understand that MySQL InnoDB default index is BTree and it can get messy but it should have a way to optimize or re-organize these so the query performance is not so bad.

Note: I have ran

optimize table foo;
flush tables;

And these didn't seem to solve anything related to the index.

When you use explain it shows some indexes being used but mainly, it shows the type ranged and under this type that there is most of number of rows analyzed to return just a few of them.

fk_id is part of the where clause, needs to be, it is also part of a composite key which means that it is deterministic to return the results.

The query is basically this:

select id, fk_id, datecolumn, bar.id, bar.name, bar.type
    from foo
    join bar on bar.id = foo.fk_id
   where foo.datecolumn between '2016-01-01 05:00:00' and '2017-01-01 04:59:59'
   and foo.fk_id in (1, 2, 4)

Even if the query was a simple select where datecolumn between range I get the same result.

BTree blocks inherently go from 100% full (actually about 15/16) to about 69% full after a lot of random INSERTs.  This may account for the 40% you are seeing.  But note, it won't get any slower over time.  Hence, I see frequent OPTIMIZE TABLE as a waste of time, especially if you need the table to be accessible all the time.

And, your 40% faster will rapidly degrade.

I would expect FLUSH TABLES to actually hurt -- since it will remove cached info from RAM.

In order to discuss the query, please show us the query, its EXPLAIN SELECT ..., and perhaps SHOW CREATE TABLE bla.

foo is essentially two tables, one ordered (id, fk_id, datecolumn), the other ordered (datecolumn, id, fk_id).  Plus, I think, a secondary index of (fk_id, id) (no datecolumn).

If fk_id is part of the WHERE clause, none of them may be optimal.

Addenda

(From OP's comment:)

select  id, fk_id, datecolumn, bar.id, bar.name, bar.type
    from  foo
    join  bar  ON bar.id = foo.fk_id
    where  foo.datecolumn between '2016-01-01 05:00:00'
                              AND '2017-01-01 04:59:59'
      and  foo.fk_id in (1, 2, 4)

The optimal indexes are

foo:  INDEX(fk_id, datecolumn) -- 3 disjoint ranges
bar:  PRIMARY KEY(id) -- I assume it is already the PK; if not, then INDEX(id)

Note that you do not currently have the optimal index for foo.  Add it, and your need for OPTIMIZE TABLE should diminish.

As for why the optimize seemed to help -- probably caching.  How much RAM do you have?  What is the value of innodb_buffer_pool_size?

I prefer this pattern:

    where  foo.datecolumn between '2016-01-01 05:00:00'
                              AND '2016-01-01 05:00:00' + INTERVAL 1 YEAR

(No hassles with leap years, datatypes, etc)

More on indexing.


If a person's name is Null then how would it break the database? I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc.

I am not well versed in databases and I do not use it very often. When I made a website for learning, the server side form validation used regular expressions. From what I remember it would happily accept the name "Null". I have not tried it though.

Could someone explain the technicalities when this situation would occur? Is the form validation just doing a string == NULL or something? Even so I do not think NULL is same as "NULL".

I have seen database interfaces (e.g.  framework libraries) that return 'null' as a string for null columns.  I believe there was a flag that would turn this on or off for debugging.  This flag allows developers to easily determine if the empty field was a result of a null value or an empty value.  This is a bad setting, especially in production, and would explain the issues explained in the article.

The reverse processing of converting 'null' to a null value should generate an application error for a name field.  I would expect this to be rather quickly resolved.


Enabling Fragmentation Manager in SQL Sentry Enabling Fragmentation Manager in SQL Sentry causes slowness on SQL instance ? does it take additional resources ?

It depends on which elements of the Fragmentation Management component you enable. There are three different levels of capability that can be enabled;

Collect Index usage stats & Buffer utilization capture
Collect Index Fragmentation details
Enable Index defragmentation capability

As Erik said, if you are collecting fragmentation data then that will have an overhead because if the way that the sys.dm_db_index_physical_stats() DMF works.

Rather than go into more details on the config options and other capabilities in more depth here. Please feel free to open a support ticket with us by emailing Support@SentryOne.com or contact me directly on Jmartin@SentryOne.com.

John Q. Martin
Product Manager
SentryOne


SQL Server Always-On Availability Groups Patching My SQL server farm has been neglected with patching OS level & SQL server level (because they are critical systems, outages are hard to come by).

An option is to patch the secondary nodes of our AOAG clusters up to the latest patch one month, then the next month the business agree to schedule a fail over out of hours.. I can then patch the new secondary (old primary). This will mean the nodes wont be on the same patch level for a month.. is this “a no no”?

This is an unsupported configuration as per the docs

  Mixing versions of SQL Server instances in the same AG is not supported outside of a rolling upgrade and should not exist in that state for extended periods of time as the upgrade should take place quickly. The other option for upgrading SQL Server 2016 and later is through the use of a distributed availability group.

What does this mean practically? It might be completely fine - there may be zero compatibility issues and it could go swimmingly. It also might not. Microsoft has not tested the configuration you are running if you choose to mix versions between instances. At that point for me personally, the risks far outweigh the advantages.

It's also worth noting that using the rolling upgrade process as defined in the link I posted reduces downtime to a minimal figure anyway. If that's still not good enough, why not build two new servers and a new AG, and migrate to them, rather than patching? It's a lot more work but you should be able to minimise downtime even further.


When were OLEDB based linked servers added to SQL Server? I guess what I'm asking is in which version of SQL Server were linked servers added to SQL Server?

I believe this was introduced in SQL 7. If memory serves me right, I was able to access a SQL 6.5 server from a SQL 7 server, but I could not access SQL 6.5 from/to another SQL 6.5 (I don't think the sp_addlinkedserver stored procedure existed prior to SQL 7).


Postgres function to merge two json objects with overlapping keys into one object I've got the following JSON object:

{
    "a" : {
        "0" : 2,
        "1" : 4,
        "3" : 6,
    },
    "b" : {
        "2" : 8,
        "1" : 10, /*note this key exists in "a" too*/
        "4" : 12,
    }
}

I'd like to generate the following object and then be able to extract an element from it like so:

{
        "0" : 2,
        "1" : 10,
        "2" : 8,
        "3" : 6,
        "4" : 12,
}

Extraction: object->>'1' should return '10'

Basically, I have two arrays with potentially overlapping keys and I want to merge the two, giving one array precedence.

How can I accomplish this? Ideally I'd call a function like arrayMerge(a, b) and it gave 'a' higher precedence than 'b'

If you're using PostgreSQL 9.5 (at least), you can use the concatenation operator (||) on jsonb types (and you can convert json to jsonb if necessary first).

For example:

WITH test(data) AS (
    VALUES ('{
        "a" : {
            "0" : 2,
            "1" : 4,
            "3" : 6
        },
        "b" : {
            "2" : 8,
            "1" : 10,
            "4" : 12
        }
    }'::jsonb)
)
SELECT (data->'a') || (data->'b') FROM test

will produce:

{"0": 2, "1": 10, "2": 8, "3": 6, "4": 12}

(Please note that, in this particular example, the parentheses around (data->'a') matter.)

You can modify the example above to get a specific value, as you requested, for example:

SELECT (((data->'a') || (data->'b'))->'1')::text::integer FROM test


Log Sql Server Transactions (Queries + username) I need to see which user has made what query with a lookback of ~90(?) days.

I have activated change tracking on the database with 90 days.

I have found several solutions:

SELECT
    deqs.*,
    dest.*,
    deqs.last_execution_time AS [Time],
    dest.TEXT AS [Query]
FROM
    sys.dm_exec_query_stats AS deqs
CROSS APPLY
    sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
    deqs.last_execution_time DESC

But this query doesn't give me any information about the user who made the query.

Then I got this query

SELECT
    *
FROM
    FN_TRACE_GETTABLE('E:\MSSQLServer\MSSQL12.DBNAME\MSSQL\Log\log_29.trc', default)
WHERE
    TextData IS NOT NULL
ORDER BY
    TransactionID DESC

This give me the username but no statements about DELETE/UPDATE etc.

I read about the SQL Profiler but I can't copy/read the _log.mdf-files because it is always in use.

I need a tip what s the right way to do this.. :/

Change tracking provides an efficient, light-weight data change tracking mechanism.  In other words, this only tracks UPDATE, INSERT, DELETE statements; it does not track SELECT statements.  It does not track who made the change.

If you truly need to track all DML and DDL statements made against the server, you'll need to implement an Extended Events monitoring solution, but be aware for a server that is heavily used, you will likely affect performance in a negative way.

If you only are interested in DML changes, then change tracking will allow you to understand what changes were made to the data using the CHANGETABLE T-SQL statement.  This sample code shows how that works.  The code drops any database named ChangeTrackingTest first, then creates that same database with a single table that has change tracking enabled.

USE master;
GO
DROP DATABASE ChangeTrackingTest
GO
CREATE DATABASE ChangeTrackingTest;
ALTER DATABASE ChangeTrackingTest
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE ChangeTrackingTest;
GO
CREATE TABLE dbo.t
(
    id int NOT NULL
        PRIMARY KEY
        CLUSTERED
);
ALTER TABLE dbo.t ENABLE CHANGE_TRACKING;

INSERT INTO dbo.t (id) VALUES (1);
UPDATE dbo.t SET id = 2 WHERE id = 1;

SELECT current_id = t.id
    , ctc.*
FROM dbo.t t
    CROSS APPLY CHANGETABLE(CHANGES dbo.t, NULL) ctc
GO

The results:

╔════════════╦════════════════════╦═════════════════════════════╦══════════════════════╦════════════════════╦════════════════════╦════╗
║ current_id ║ SYS_CHANGE_VERSION ║ SYS_CHANGE_CREATION_VERSION ║ SYS_CHANGE_OPERATION ║ SYS_CHANGE_COLUMNS ║ SYS_CHANGE_CONTEXT ║ id ║
╠════════════╬════════════════════╬═════════════════════════════╬══════════════════════╬════════════════════╬════════════════════╬════╣
║          2 ║                  2 ║                           1 ║ D                    ║ NULL               ║ NULL               ║  1 ║
║          2 ║                  2 ║                           2 ║ I                    ║ NULL               ║ NULL               ║  2 ║
╚════════════╩════════════════════╩═════════════════════════════╩══════════════════════╩════════════════════╩════════════════════╩════╝

Cleanup:

USE master;
GO
DROP DATABASE ChangeTrackingTest


Type conversion in expression try_cast may affect cardinality estimate in query plan choice I have a query that is running very slow so I started looking into the Execution Plan. TRY_CAST is a safe way of finding out if the data can be cast to a type. And there is a warning in the plan regarding TRY_CAST.

 Type conversion in expression try_cast may affect "CardinalityEstimate" in query plan choice

Please note that I am not a DBA but a coder. Treat me as a novice when I comes to database stuff.

What should be best approach to improve the speed of the query (based on the warning in the title of this question)? Is there anything other than TRY_CAST that might help speed up the query, for example, CAST on its own is prone to exceptions and the only way to safe cast that I know of is TRY_CAST?

Basically, this could mean that the type conversion cannot effectively use an index. Cardinality is a important property of the indexes that you might use.  For performance, the ideal is to have the data types match, instead of trying to convert them on the fly.

You will find Thomas LaRock's post on this useful: http://thomaslarock.com/2012/08/why-datatypes-matter-3-ways-they-can-hurt-performance/


Starting oracle rac node2 error: ORA-29701: unable to connect to Cluster Synchronization Service I have a oracle RAC /12c/. I got these error when I try start the base from node2.
```ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/mydborcl/spfilemydborcl.ora'

ORA-17503: ksfdopn:2 Failed to open file +DATA/mydborcl/spfilemydborcl.ora

ORA-29701: unable to connect to Cluster Synchronization Service```

I am very new to oracle. How can I start the mount the database on node2 and solve the problem?

ORA-29701: unable to connect to Cluster Synchronization Service

You need to make sure the the Grid Infrastructure services are started first. They provide all of the networking and ASM storage for the cluster, and you can't start a database instance without them.
On Linux, as root, run crsctl start crs
The command crsctl stat res -t should show you the state of all cluster services.


First-In and Last-Out times for Night Shift and Day Shift Employees Kindly share suitable technique to handle night shift as well as day shift.
Below are the shifts,
Night,  2020-07-20 17:00 to 2020-07-21 02:00
Night,  2020-07-20 17:00 to 2020-07-21 02:00
Day,    2020-07-20 08:00 to 2020-07-20 17:00
Day,    2020-07-20 08:30 to 2020-07-20 17:30

In Database there are only two fields
1: SSN (which is employee Code)
2: CheckTime e.x. (2020-07-20 16:48:30.000)

in Day shift i can get first in last out from Min entry and Max entry.
but in night shift I can't get any accurate data because of the date change.

Could you write a query to use BETWEEN the shift start date time and the shift end date time (both held in variables) that returns all the SSN and check times in that period? If you insert the results of that into a temporary table, you could then have another query on that temporary table for your MIN and MAX values. Doing it like that means you don't need to worry about a change of date because your only concerned with the data in your temporary table. Also, using variables would let you reuse the script for different date and times for shifts.


Masking PII columns in Oracle I am using Oracle 11g R2 on Linux, and I have multiple tables in my database that has got columns that needs to be masked.

I have tried the OEM to mask the data as mentioned in the below hyperlink, it worked fine.
    http://www.oracle.com/webfolder/technetwork/tutorials/obe/em/emgc10205/data_masking/datamask.htm

But I couldn't find any blog that gives manual ways to mask data in oracle, like executing packages, functions and so on via SQLPLUS prompt.

Could someone please suggest me how else can this be achieved

If you want to mask data but still store it unmasked, and you don't want to use the Oracle provided tools (chargeable extra!) then you need to develop an API that does the masking for you.

You store the data as normal in the database.

Clients don't get any access to the tables directly.  When they request data, use a "GET" call, which masks out the data to your spec, and when they insert data they use a "SET" call.

This all relies on access to the data being through this API though, so you need to carefully design that side of things.  This means using one schema for storage of data and another user as the API owner/data access path.

There's lots of things to take into consideration here and it's not a small job.


Optimizing IP Range Search? I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query.

In my table I have 3 columns

CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, url NVARCHAR(255))

and I am running this simple query.

SELECT url
FROM MyTable
WHERE ipto <= somevalue AND ipfrom >= somevalue

I have also created indexes on all 3 columns clustered on ipFrom and non-clustered on rest of 2. But this query giving me really poor performance in terms of CPU & reads.

Any suggestions.

What i have implemented is i like to redirect user based on the IP address.
I have stored several IP ranges from different areas and states and redirect users on the basis of their IP to appropriate URL.

Yes i think i inserted the data in wrong manner in both columns(and will do column rename later), but point here is to minimize the CPU.

When i look into the execution plan there it converts data in where clause, i dont know why it is converting data in where clause. There is something like this

|--Clustered Index Seek(OBJECT:([T].[TC]), SEEK:([T].[C] > Convert([@V])

I'm assuming there is a typo in the question as per Martin's comment.

A problem with a single range seek on either ipFrom or ipTo is that on average it will need to seek half the table. An alternative approach is

1) Add a computed column, on something like:

 ALTER TABLE MyTable
  ADD granule
AS CASE WHEN (ipTo-ipFrom)<16 THEN 1 WHEN (ipTo-ipFrom)<256 THEN 2 ELSE 3 END;

2) Index the computed column and ipFrom

CREATE ClUSTERED INDEX IX ON MyTable(granule, ipFrom );

3) Query like this:

SELECT url
FROM   MyTable
WHERE  granule = 1
       AND ipFrom BETWEEN @somevalue - 16 AND @somevalue
       AND ipTo >= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 2
       AND ipFrom BETWEEN @somevalue - 256 AND @somevalue
       AND ipTo >= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 3
       AND ipFrom <= @somevalue
       AND ipTo >= @somevalue;

Precisely how you define the 'granule' function will depend on the spead of ipto-ipfrom in your data.


Creating a temporary copy of a table with partitions I've got a little issue and wonder if anyone has come across it themselves or seen a solution.

In one of my reporting scripts, it creates a temporary copy of a table before inserting some rows in order to a bulk update on the source table. To create a copy I used the SQL

CREATE TEMPORARY TABLE myTable LIKE source.myTable;

Which works fine, apart from one table which is partitioned. MySQL can't create temp tables with partitions.

I can't do the alternative which would be to do...

CREATE TEMPORARY TABLE myTable SELECT * from source.myTable;

If you do that it a) only creates the column definitions to fit the data in the select (i.e. a VARCHAR column will only be defined according to the max length of the column in the select statement) and b) I don't want to copy all 4 million rows simply to truncate them in the next statement!

Is the only other option to create it manually from the information schema? That really feels like using a sledgehammer to crack a walnut, surely there must be a simpler way to do it? Not too mention a lot of work.

CREATE TEMPORARY TABLE myTable
    SELECT * from source.myTable
        LIMIT 0;           -- Add this

Still creates the temp table, but puts no rows in it.


How can I speed up PgAdmin III Queries? I've been noticing that PgAdmin III isn't as responsive as I expected it to be.

For instance a query to select * ... from a table containing 20,397 rows takes 19.531 seconds in PgAdmin III and 7.383 seconds in DbVisualizer 9.0.7. I initially thought this was a coincidence, but it has been consistent across all queries run.

As such it appears that PgAdmin III takes approximately twice as long to query my database over DbVisualizer. So how can I speed up PgAdmin III Queries?

I am using Heroku Postgres 9.1.9 on the Crane package and PgAdmin 1.16.0.

--EDIT--

I upgraded PgAdmin to version 1.16.1 and query time is 19.038 seconds, so no change with software revision.

It looks like you see a difference between the clients you use.

In my experience the different PostgreSQL clients need very different times for displaying the data they got from the server.  Far the fastest was psql and then pgAdminIII, EMS SQL Manager and phppgadmin in who-remembers-what order.

When tried with commands which return no or few rows, the difference is usually negligible or at least very small.  Furthermore, you should see the same actual time consumed (with the usual small differences) when you issue an EXPLAIN ANALYZE ... command instead of the actual query which returns the data much slower in one client than the other.


In a Serial 3-Server Replication Scenario (A>B>C), can server B preserve binlogs that server A has purged? This is in the service of a live migration, where server A has heavy performance and space demands, so I would like to keep down the amount of disk-space used by logs - let's say expire_logs_days = 10.

If I set server B, which is a slave of server A, to expire_logs_days = 30, will it then work to use a 20 day old snapshot (of B, for argument's sake) to make a new slave of B, called C, that will be able to catch up, or does the fact that server A has binlogs purged on the 10th day somehow break that scheme?

I am using row-based GTID replication on Percona 5.6, b.t.w.

No problem.

A (as a Master) writes to its binlog
That stuff is copied (usually immediately) to the "relay-log" on B (as a Slave).
That stuff is written to B's binlog
That stuff is copied to the relay log on C.

If the network from A to B goes down for more than 10 days, you will lose data.  For B to C, you have 30 days to repair the network, even though some stuff may no longer be in A's set of binlogs.

Another thing to note:  I think the purging of old binlogs does not occur until the current binlog exceeds its limit.  If that limit is large (in comparison to the activity), it could take days to roll over.  You might want to shrink the limit to get better granularity.  (And it is OK if A and B have different limits.)


How to remove line breaks in SSMS? I am dealing with SQL that looks as follows

create procedure

as

begin

Notice the large gaps e.g. between as and begin.

How can I remove these? I used SQL formatter but this is not working.

In SQL Server Management Studio (SSMS) there is a command to Delete Blank Lines, though it is not available on a menu, toolbar icon, or keyboard shortcut by default.

If you have to do this regularly, edit your menus, toolbars, or shortcuts to add this command. For example, to add this command to the Edit/Advanced menu:

From the Tools menu, choose Customize:

In the window that appears, choose the Commands tab, then drop down the Menu Bar list to select Edit | Advanced:

Then click Add Command

Now select the Edit category and scroll the Commands list to select Delete Blank Lines:

Once this is done, Delete Blank Lines will appear in the Edit/Advanced menu. There are other options in the Customize dialogs shown above to position the command in the menu, add a keyboard shortcut, or add an icon for it to a toolbar.


ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C) Consider these two functions:

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

As far as I understand, they produce exactly the same result.
In other words, the order in which you list the columns in the PARTITION BY clause doesn't matter.

If there is an index on (A,B,C) I expected the optimiser to use this index in both variants.

But, surprisingly, optimiser decided to do an extra explicit Sort in the second variant.

I've seen it on SQL Server 2008 Standard and SQL Server 2014 Express.

Here is a full script that I used to reproduce it.

Tried on Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

and Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64)
    May 27 2016 15:33:17
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

with both old and new Cardinality Estimator by using OPTION (QUERYTRACEON 9481) and OPTION (QUERYTRACEON 2312).

Set up table, index, sample data

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

Queries

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

Execution plans

PARTITION BY A,B

PARTITION BY B,A

Both

As you can see, the second plan has an extra Sort. It orders by B,A,C.
The optimizer, apparently, is not smart enough to realise that PARTITION BY B,A is the same as PARTITION BY A,B and re-sorts the data.

Interestingly, the third query has both variants of ROW_NUMBER in it and there is no extra Sort! The plan is the same as for the first query.
(The Sequence Project has extra expression in the Output List for the extra column, but no extra Sort). So, in this more complicated case the optimiser appeared to be smart enough to realise that PARTITION BY B,A is the same as PARTITION BY A,B.

In the first and third queries the Index Scan operator has property Ordered:True, in the second query it is False.

Even more interesting, if I re-write the third query like this (swap two columns):

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

then the extra Sort appears again!

Could somebody shed some light? What is going on in the optimiser here?

It seems that there is no good definitive "answer" to the question "what is going on in the optimiser", unless you are its developer and know its internals.

I'll put together the comments here.

Overall, it seems that it would be too harsh to call it a bug, because the end result of the query is correct. In some cases the execution plan is simply not optimal. ypercubeᵀᴹ, Martin Smith and Aaron Bertrand call it "missed optimization".

  Seems like GROUP BY a,b and GROUP BY b,a yields identical plans
  but PARTITION BY can't use the same transformation

  There are other missing optimisations too where window functions with
  the same window specification can have an extra sort operation if
  separated in the select list by one with a different specification.

  Yeah, this seems like another missed optimization, and there are
  plenty of those. The optimizer is written by humans and isn't perfect

There is a somewhat related article Descending Indexes. Index ordering, parallelism, and ranking calculations by Itzik Ben-Gan. There Itzik discusses descending indexes and also gives an example how direction of the index definition affects window functions with partitions. He shows examples of queries and generated plans with ROW_NUMBER that have extra sort operator that optimiser could have avoided.

To me the practical outcome would be to keep this peculiarity of optimiser in mind. When using PARTITION BY in window functions always try to match the order in which you list the columns in PARTITION BY with the order in which they are listed in the index. Even though it should not matter.

Another side of this precaution is when you review your indexes and decide to swap some columns around in the index definition. Be aware that you may inadvertently affect some existing queries that seemingly should not be affected. This is actually how I noticed this peculiarity of the optimiser.

If you don't, optimiser may not be able to use the index to its full potential. Even if optimiser does choose an optimal plan, such plan may change to less optimal with a slightest innocent change to the query, like changing the order of columns in the SELECT statement.


SQL Server VM in Azure with In Memory I am running azure and considering deploying a SQL Server 2014 instance due to the in memory feature, however, I was looking through the documentation and couldn't seem to find how to configure In-Memory, is it enabled by default, what considerations for data do I need to take?

Does any one have any experience with the In memory feature in SQL Server

In-Memory is for making your heavily accessed table as memory optimized.
For a memory optimized table, all data is stored in memory and hence unlike disk based tables, pages don't need to be brought into the buffer pool or cache.
In order to create a memory optimized table, you need to create a database with a filestream filegroup (along with the use of CONTAINS MEMORY_OPTIMIZED_DATA)

-- enable for in-memory OLTP - change file path as needed
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod

GO

Now for creating memory optimized tables you have to create table with WITH (MEMORY_OPTIMIZED=ON)

Getting Started with SQL Server 2014 In-Memory OLTP will give you all the detailed steps to get you started.

For more details, check this BOL article : In-Memory OLTP (In-Memory Optimization)

Update:

@cat you have to follow 3 step process

Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP ,
Determining if a disk based Table or Stored Procedure Should Be Ported to In-Memory OLTP -- informs you about which tables in your database will benefit if ported to use In-Memory OLTP and finally
After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor to help you migrate the disk-based database table to In-Memory OLTP.


How large space will be occupied by mysql for a varchar utf8 column? Tip: To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible length. For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.

From:http://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html

Does this mean if I use a varchar utf8 column other than char utf8 column in mysql, mysql will physically store characters using 1 byte for latin  and 3 bytes for CJK(Chinese、japanese、Korean) ？

CHAR(...) CHARACTER SET utf8 always takes 3 bytes per character -- CHAR(100) occupies 300 bytes (no length needed).

VARCHAR occupies 1-2 bytes for a length, plus only as many bytes as needed.  So VARCHAR(100) with hello will occupy 7 (2+5) bytes in any character set.

Señor, in CHARACTER SET latin1, take 5 bytes (plus length).  In utf8, it takes 6 bytes (plus length).  This is because ñ is the 1-byte hex F1 in latin1 or the 2-byte C3B1 for utf8.

Some Chinese characters and some Emoji, need 4 bytes, so utf8mb4 is a better choice for them.

There is very little use for ever using CHAR instead of VARCHAR.  They are usually for consistent-length fields such as postal code, country code, md5, uuid, etc.  And all of those may as well be CHARACTER SET ascii or possibly latin1, but not utf8.

Back to your question...  With CHARACTER SET utf8 (or utf8mb4):

English letters take 1 byte.
Western European accented letters take 2 bytes.
Greek, Hebrew, and other languages in that general area, take 2 bytes.
Japanese and Korean take 3 bytes.
Most Chinese characters are 3 bytes; some are 4 and need utf8mb4.
A longer list

"mysql will physically store characters using 1 byte for latin and 3 bytes for CJK" is not phrased correctly.  Change 'latin' to 'English' to make it correct.  ('Latin' tends to include various accented characters.)


How do long columns impact performance and disk usage? In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.

In reality, how much does it really matter? How good is this optimized? What is the impact of just allowing 100 or 200 or even 500 chars for normal "input" fields? An email can only have 320 chars, so ok - there is a good limit there. But what do I gain if I set it to 200, because I do not expect longer e-mail addresses than that.

Usually our tables will not have more than 100.000 rows, and up to 20 or 30 such columns.

We use SQL Server 2008 now, but it would be interesting to know how different DBs handle this issues.

In case the impact is very low - as I would expect, it would help to get some good arguments (backed up with links?) to convince my DBA, that this long-field-paranoia isn't really necessary.

In case it is, I'm here to learn :-)

The specific answer to your question (at least for Oracle and probably other databases) is that the length of the field doesn’t matter, only the length of the data.  However, this shouldn’t be used as a determining factor concerning whether to set the field to its maximum allowable length or not.  Here are some other issues you should consider before maxing out field sizes.

Formatting
Any client tool that formats the data based on the size of the fields will require special formatting considerations.  Oracle’s SQL*Plus for example by default displays the maximum size of Varchar2 columns even if the data is only one character long.  Compare…

create table f1 (a varchar2(4000), b varchar2(4000));
create table f2 (a varchar2(5), b varchar2(5));
insert into f1 values ('a','b');
insert into f2 values ('a','b');
select * from f1;
select * from f2;

Bad Data
Field length provides an additional mechanism to catch/prevent bad data.  An interface shouldn’t attempt to insert 3000 characters into a 100 character field, but if that field is defined to be 4000 characters, it just might.  The error woudn’t be caught at the data entry stage, but the system may have trouble further down when another application tries to process the data and chokes.  As an example, if you later decide to index the field in Oracle you would exceed the maximum key length (depending on block size and concatenation).  See…

create index i1 on f1(a);

Memory
If the client application allocates memory using the maximum size, the application would allocate significantly more memory than is necessary.  Special considerations would have to be done to avoid this.

Documentation
The size of the field provides another data point of documentation about the data.  We could call all tables t1, t2, t3, etc. and all fields f1, f2, f3, etc., but by specifying meaningful names we better understand the data.  For example, if an address table for a company with customers in the U.S. has a field called State that is two characters we expect the two character state abbreviation to go in it.  On the other hand if the field is one hundred characters we might expect the full state name to go in the field.

All that being said, it does seem prudent to be prepared for change.  Just because all your product names today fit in 20 characters doesn’t mean they always will.  Don’t go overboard and make it 1000, but do leave room for plausible expansion.


"Fire" a trigger to populate columns that are NOT NULL? Working out of my SQL book at the moment. The question is, Create a trigger based upon the ENROLLMENT table.  The trigger should “fire” before an INSERT statement.  The trigger will populate all the columns that are NOT NULL and columns that have foreign key constraints.

CREATE OR REPLACE TRIGGER triggerOne
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
  --loop to get all of our columns that are are checked to be not NULL
  FOR i in (SELECT STUDENT_ID ,SECTION_ID ,ENROLL_DATE ,FINAL_GRADE ,CREATED_BY ,CREATED_DATE ,MODIFIED_BY , MODIFIED_DATE
            FROM ENROLLMENT
            WHERE created_by || created_date || enroll_date || final_grade || modified_by || modified_date || section_id || student_id IS NOT NULL)
  --loop for CHECK constraints from ENROLLMENT table
  LOOP
    IF (CHECK(:NEW.SECTION_ID = ENR_SECT_FK) AND CHECK(:NEW.STUDENT_ID = ENR_STU_FK)) THEN
      INSERT INTO ENROLLMENT(9,9,'30-JAN-99',9,'9','09-SEP-99','9','01-JAN-00');
    END IF;
  END LOOP;
END;

I'm getting an error on line 9

  Error(9,5): PLS-00103: Encountered the symbol "CHECK" when expecting one of the following:     ( - + case mod new not null          continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date       pipe

We have a ENROLLMENT table with constraints for the columns that are NOT NULL and constraints ENR-SECT_FK and ENR_STU_FK as the foreign key constraints.

Not sure where I'm going wrong here... going to re-read the chapter, but any help would be greatly appreciated!

If you want to set values for NULLable columns, then just set those values, like this:

CREATE OR REPLACE TRIGGER enrollment_bi
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
  IF :NEW.STUDENT_ID    IS NULL THEN
    :NEW.STUDENT_ID    := 9;
  END IF;
  IF :NEW.SECTION_ID    IS NULL THEN
    :NEW.SECTION_ID    := 9;
  END IF;
  IF :NEW.ENROLL_DATE   IS NULL THEN
    :NEW.ENROLL_DATE   := TO_DATE('30-JAN-99','DD-MON-YY');
  END IF;
  IF :NEW.FINAL_GRADE   IS NULL THEN
    :NEW.FINAL_GRADE   := 9;
  END IF;
  IF :NEW.CREATED_BY    IS NULL THEN
    :NEW.CREATED_BY    := '9';
  END IF;
  IF :NEW.CREATED_DATE  IS NULL THEN
    :NEW.CREATED_DATE  := TO_DATE('09-SEP-99','DD-MON-YY');
  END IF;
  IF :NEW.MODIFIED_BY   IS NULL THEN
    :NEW.MODIFIED_BY   := '9';
  END IF;
  IF :NEW.MODIFIED_DATE IS NULL THEN
    :NEW.MODIFIED_DATE := TO_DATE('01-JAN-00','DD-MON-YY');
  END IF;
END;
/

There is no need to read the table before (certainly not in a FOR loop) - and that is actually forbidden. The trigger executes for each row modified by any SQL statement, and gives you the context of the operation it is invoked on: variable :OLD contains the previous values of the columns (for UPDATE and DELETES) and :NEW the new values (for UPDATE and INSERT). So all you need is to change the values in :NEW if any of them is NULL.

Then again, you do not need a trigger at all: just specify the values you want as defaults when you create the table:

CREATE TABLE ENROLLMENTS (
  STUDENT_ID    NUMBER        DEFAULT 9,
  SECTION_ID    NUMBER        DEFAULT 9,
  ENROLL_DATE   DATE          DEFAULT TO_DATE('30-JAN-99','DD-MON-YY'),
  FINAL_GRADE   NUMBER        DEFAULT 9,
  CREATED_BY    VARCHAR2(20)  DEFAULT '9',
  CREATED_DATE  DATE          DEFAULT TO_DATE('09-SEP-99','DD-MON-YY'),
  MODIFIED_BY   VARCHAR2(20)  DEFAULT '9',
  MODIFIED_DATE DATE          DEFAULT TO_DATE('01-JAN-00','DD-MON-YY')
);

I am guessing the datatypes here - you may have different ones.


Microsoft recommends using Rowversion over timestamp, but I can not find "rowversion" data type inside my sql server 2008 R2 Microsoft here mentioned that we should use the rowversion data type over Timestamps. Now inside my SQL Server 2008 R2, I want to create a column with the Rowversion data type. But in my SQL Server 2008 R2 Management Studio, I cannot find such a data type:

Also when I tried altering existing column named timestamp which is of type timestamp using this statement:
ALTER TABLE [SkillManagement].[dbo].[Customer]
ALTER COLUMN timestamp rowversion

I get this error:
Msg 4927, Level 16, State 1, Line 1
Cannot alter column 'timestamp' to be data type timestamp

Can anyone advise?
EDIT

rowversion is just an alias for timestamp. Under the covers, they are the exact same thing.

If you're using the GUI (prior to 2012), you have to use timestamp. (Peripheral hint: please stop using the GUI.)

But it doesn't stop there. Even if you generate this table using DDL:

CREATE TABLE dbo.x(y ROWVERSION);

The data type in the metadata will be timestamp:

SELECT t.name
  FROM sys.types AS t
  INNER JOIN sys.columns AS c
    ON c.system_type_id = t.system_type_id
  INNER JOIN sys.tables AS st
    ON c.[object_id] = st.[object_id]
  INNER JOIN sys.schemas AS s
    ON st.[schema_id] = s.[schema_id]
  WHERE st.name = N'x'
    AND s.name = N'dbo'
    AND c.name = N'y';

And if you generate a script for this table, it will also include timestamp:

CREATE TABLE [dbo].[x](
    [y] [timestamp] NOT NULL
) ON [PRIMARY]

And in fact:

SELECT * FROM sys.types WHERE name = N'rowversion';

Results:

----
0 row(s) affected.

So there isn't even a type in the system named rowversion - any DDL that includes it gets parsed and translated before the DDL is even executed against the server.

Microsoft has been a walking contradiction on this since they first introduced the poorly-named data type (which also happens to violate the SQL standard, which dictates that the timestamp data type should be used for date/time data). I asked back in 2007 for this to be properly deprecated. My Connect item is still active (archive)- feel free to vote and, more importantly, comment about how the documentation contradicts the possible usage of the product.

Others have written about this too.


Group by data by month with selected fields not to be included in group by class Is there any way to group by the data by the only month but I need the no_inv and date_inv columns to be selected too.
Mysql query :
SELECT no_inv, date_inv, SUM(total_unpaid) AS unpaid, SUM(total_paid) AS paid
FROM invoice
GROUP BY MONTH(date_inv)
LIMIT 3

It doesn't make sense (even a as concept) to be able to SELECT non-aggregated fields that aren't part of the GROUP BY clause, because when you group on a field, you're literally condensing many rows into one row for each unique value of that field you're grouping on. So it wouldn't be possible to display a non-grouped (and non-aggregated) field because that field may have multiple different values within the grouping of the field in the GROUP BY clause, so it would not be possible to show two different values of the non-grouped field in the same row for the grouped field.
That being said, I think I understand the reasoning behind your question, which I assume is you're more so looking for a way to aggregate across a grouping and display that aggregation with the other non-grouped fields. This is possible to accomplish with window functions via the OVER and PARTITION BY clause like this:
SELECT no_inv, date_inv, SUM(total_unpaid) OVER (PARTITION BY MONTH(date_inv)) AS unpaid,
    SUM(total_paid) OVER (PARTITION BY MONTH(date_inv)) AS paid
FROM invoice
LIMIT 3

Please note the OVER() clause (and  window functions) was introduced in MySQL 8.0.


What risks are there if we enable read committed snapshot in sql-server? I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?

eg.
Will this affect recovery of the database?
Is there anything else we need to do to take advantage of this?

I plan to execute these commands:

ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

I am looking into this because I am sick of locking problems in SQL Server 2005. I am hoping this might reduce the the occasional deadlocks our users see, help overall performance of our application and encourage our developers to do more than one operation per transaction without fear.

Summary

If you have locking problems then you have a problem with your code: it isn't the database engine
It isn't a magic bullet
You may add more problems

Load

It will also increase load on your tempdb and CPU. Also see:

"Performance Impact: The Potential Cost of Read_Committed_Snapshot" (Linchi Shea)

Safety

Most important, snapshot isolations are not safe in many cases by default. Read "Snapshot isolation" (Wikipedia) for more on write-skew anomalies. The next section is "Making Snapshot Isolation Serializable" to get around this.

  In general, therefore, snapshot isolation puts some of the problem of maintaining non-trivial constraints onto the user, who may not appreciate either the potential pitfalls or the possible solutions. The upside to this transfer is better performance.

Also see:

"The Potential Dangers of the Read Committed Snapshot Isolation Level" (JimMcLeod, disputed in comments by Alex Kuznetsov)
Deadlocked!: "read committed snapshot" Explained (Nick Berardi)
Serializable vs. Snapshot Isolation Level, the Marble problem (Craig Freedman)
Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent (Alex Kuznetsov)


getting MySQL error 1064 but very (absurdly) simple query in MySQLWorkbench I'm getting a MySQL 1064 error for the simplest query when run with \G at the end. This works:

select * from locations;

and this doesn't:

select * from locations\G

hmmm... is there any way to see what the proper vertical representation is?

\G is a delimiter exclusive to the mysql client

I personally do not use MySQL Workbench, but many others have done the following

Mar 12, 2010 : How to get a table creation script in MySQL Workbench?
Mar 28, 2012 : \G like vertical output in MySQL Workbench for show engine innodb status

But here is the Blog for MySQL Workbench Team ( MySQL Workbench: Vertical Query Output )

Give Them a Try !!!


What is the distinction between ALL_PROCEDURES and DBA_PROCEDURES, and other schema tables? I recently failed to find a known procedure when searching ALL_PROCEDURES, and on a whim tried DBA_PROCEDURES: lo, there it is.  The doc pages (ALL_; DBA_) both say "...lists all functions and procedures along with their associated properties."  Oracle seems to have a curious definition of "all".

I'm posting this on DBA rather than SO, though it's a pretty basic question, on the basis that schema management is generally a DBA role rather than a developer role.

From here

USER_TABLES describes the relational tables owned by the current user.
ALL_TABLES describes the relational tables accessible to the current user.
DBA_TABLES describes all relational tables in the database.

Accessible != all!


Deadlock -- Way to make this better? I have this query that deadlocks every once in a while. It is doing a self update to a table. ( update, from ) Is there any better way to handle the concurrency?
Here is the plan:
https://www.brentozar.com/pastetheplan/?id=r19CZMC8-

Deadlock:

UPDATE j SET StatusId=@StatusId -- SELECT *
FROM jobmanager.JobRun j with(rowlock) WHERE PrintBatchId=@PrintBatchI

Proc [Database Id = 11 Object Id = 191196327]

I would try to understand why your query leads to a deadlock. It would be useful to look at deadlock graph info. Try enabling trace flags (1204, 1222) to get more details from the SQL Server log.
Since you are using the row level locking and get deadlocks I suspect that PrintBatchId might have a large number of records associated. So SQL Server has to escalate the lock to page/table level. And as a result, you get a deadlock with an update on different PrintBatchId. (BTW. Do you have an index on PrintBatchId? W/o proper index row level locking won't work). Try to make query filter smaller.
If you can't make your filter more precise I would suggest to catch deadlocks and retry update attempt. Here is a sample code:

DECLARE @Retries INT = 5
    , @ErrorMessage nvarchar(max)
    , @ErrorLine INT
    , @ErrorSeverity INT
    , @ErrorNumber INT

WHILE (@Retries > 0)
BEGIN
    BEGIN TRY
        --UPDATE Command....
        SELECT @retries = 0
    END TRY
    BEGIN CATCH
        SELECT
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorLine    = ERROR_LINE()
            , @ErrorSeverity= ERROR_SEVERITY()
            , @ErrorNumber  = ERROR_NUMBER();

        IF (@ErrorNumber = 1205 AND @Retries > 0) -- If Deadlock, then retry
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock. Retrying... ';
        ELSE
        IF (@ErrorNumber = 1205 AND @Retries <= 0) -- If Deadlock, and max number of retries reached. Fail.
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock after max number of retries ... ' + CHAR(13) + ' Original Error: Msg. ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;
        ELSE
            SELECT @ErrorMessage = 'Execution Failed: Msg ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;

        SELECT @Retries-=1;

    END CATCH;
END


As a sample:

CREATE TABLE main (id INT, "date" INT);
CREATE TABLE old (id INT, "date" INT);

CREATE TRIGGER old_to_old
ON main
FOR INSERT
AS
BEGIN
    INSERT INTO old (id, "date")
        SELECT id, "date" FROM inserted WHERE "date" < 5;
    DELETE FROM main
        FROM main
        JOIN inserted ON main.id=inserted.id
        WHERE inserted."date" < 5;
END;

or

CREATE TRIGGER old_to_old
ON main
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO old (id, "date")
        SELECT id, "date" FROM inserted WHERE "date" < 5;
    INSERT INTO main (id, "date")
        SELECT id, "date" FROM inserted WHERE "date" >= 5;
END;

INSERT INTO main VALUES (1,1), (4,4), (7,7), (10,10);

SELECT *, 'main' from_where FROM main
UNION ALL
SELECT *, 'old' FROM old;

Output:

id | date | from_where
-: | ---: | :---------
 7 |    7 | main
10 |   10 | main
 4 |    4 | old
 1 |    1 | old

fiddle (FOR)

fiddle (INSTEAD OF)


Joining very distant tables in the absence of a direct relationship Consider a database of the following structure:

The schema above is just made up, but assume that this is what the DBA inherits and can't be modified further. Then, hypothetically, if the DBA is asked to provide a selection of the two most distant tables table1 and table7, at least to me, this appears to be no easy feat.

Question: If we wanted to SELECT table1.name, table7.name using a JOIN wouldn't we need a ton of AND clauses (by ton I mean 6)? Would the answer change if the tables were not just 6 tables apart as in my example, but rather were 1,000 tables apart?

I'm not sure if SQL has a special tool for this, or if we just use n-1 number of AND clauses. If the latter is the case, I'd imagine we'd want to write a batch or python file to automate a selection with 999 AND's.

SELECT
    table1.name
    ,table7.name
FROM table1
INNER JOIN table2 on table2_id = table2.id
INNER JOIN table3 on table3_id = table3.id
INNER JOIN table4 on table4_id = table4.id
INNER JOIN table5 on table5_id = table5.id
INNER JOIN table6 on table6_id = table6.id
INNER JOIN table7 on table7_id = table7.id

That is what your query would end up looking like. I would probably use dynamic SQL to piece this together if I needed to regularly.

I would look at the workload on the server. If it happened to be an OLTP server with lots of transactions, it may be semi difficult to adjust things.

If the data does not change regularly or frequently, you could look into creating a mapping table based on master data management / triggers / ETL processes (SSIS / BIML if you are using SQL Server) / Constraints that would piece together a singular table of static data from those tables. Essentially like a physical view.

Another idea is you can create an indexed view joining those tables together as well.

  Question: If we wanted to SELECT table1.name, table7.name using a JOIN
  wouldn't we need a ton of AND clauses (by ton I mean 6)? Would the
  answer change if the tables were not just 6 tables apart as in my
  example, but rather were 1,000 tables apart?

If you use the old syntax join, yes essentially. I would strongly urge you to use the new join as I show above as it is the new standard and is a) easier to read and b) less prone to error.

  I'm not sure if SQL has a special tool for this, or if we just use n-1
  number of AND clauses. If the latter is the case, I'd imagine we'd
  want to write a batch or python file to automate a selection with 999
  AND's.

There is no 'automated' tool, but I gave some suggestions above. There are other third party BI tools out there that can assist with this, especially those that specialize in master data management / ETL / BI practices.


How does the QUEUE_DELAY option for Server Audits work I am working through how SQL Server Audits work and would like some clarity on how the QUEUE_DELAY options works.  BOL defines the options as:

  Determines the time, in milliseconds, that can elapse before audit
  actions are forced to be processed. A value of 0 indicates synchronous
  delivery. The minimum settable query delay value is 1000 (1 second),
  which is the default. The maximum is 2,147,483,647 (2,147,483.647
  seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying
  an invalid number will raise the error MSG_INVALID_QUEUE_DELAY.

Where are these actions queued tempdb, a system table, etc?  Can I query that location to see how many items are in the queue?  If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

SQL Server Audit leverages the new Extended Events architecture introduced in 2008.

If you look at the following DMVs, you can see the related structures appear when the audit is started.

select * from sys.dm_server_audit_status
select * from sys.dm_xe_session_events
select * from sys.dm_xe_session_targets

Note that audits have their own set of special targets, which cannot be used directly through Extended Events. If you start generating some audit events, you'll see the events show up in the actual target, but unlike a ring buffer target, they don't appear in the target_data column in the DMV for security reasons.

Having said that, it's a bit unclear (and apparently undocumented) how the Audit session targets work internally. Based on the fact that Audit is built on Extended Events, and given the behaviour options for when auditing fails, I assume the Audit targets are simply modified ring buffer targets that don't expose the event data back to SQL Server. While this explanation makes the most sense to me, I'm honestly not 100% sure. Hopefully someone with more internals knowlege can comment.

  Can I query that location to see how many items are in the queue?

Not as far as I can tell; again, likely for security reasons.

  If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

There are many scenarios wherein the event could not be recorded. If my assumption about hitting a memory buffer is correct, the failed event(s) will be dropped on the floor as a shutdown will release all SQL Server memory. Anything that didn't make it to disk will be lost.


Import Windows MySQL dump on a Debian Linux server I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records.
Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, also MySQL 5.7).

During the import of the dump I received several error messages, which were not helpful.
Therefore I looked at the dump file with the nano editor and found out that the content cannot be displayed correctly:

��-^@-^@ ^@M^@y^@S^@Q^@L^@ ^@d^@u^@m^@p^@ ^@1^@0^@.^@1^@3^@ ^@ ^@D^@i^@s^@t^@r^$
^@
^@-^@-^@
^@
^@-^@-^@ ^@H^@o^@s^@t^@:^@ ^@l^@o^@c^@a^@l^@h^@o^@s^@t^@ ^@ ^@ ^@ ^@D^@a^@t^@a^$
^@
...

But if I open the dump under Windows in e.g. Notepad++, everything can be displayed perfectly.

So I thought about an error during the FTP upload. However, if I transfer a file from Windows to Linux and then transfer it back, the new file will still be readable under Windows, but not under Linux.

Can anyone give me a hint?
Thanks!

Little update:
This is how I made the dump:

.\mysqldump.exe -u root --databases m_data > D:\m_data_dump.sql

Update 2:
I also tried exporting with explicit charset:

.\mysqldump.exe -u root --default-character-set=utf8mb4 --databases m_data > D:\m_data_dump.sql

And imprted with:

mysql -u root -p --default-character-set=utf8mb4 < m_data_dump.sql

But sam result.

The contents of the dump file you show appear to be UTF16 - this may be the result of the redirect operator on Windows (particularly if you're using PowerShell).

To avoid the Windows shell redirection operator interfering with the encoding/formatting of data emitted by the mysqldump program, use the --result-file=filename when dumping.

See the MySQL docs for this command line option at https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_result-file


Keep track of time intensive stored procedure with prints I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = 0, I have multiple SELECTs and an UPDATE on giant tables. (The procedure works, I've checked with a colleague)

Now in a script or a program I'd have a simple print @current_cursor or print a counter in every WHILE just to have some feedback.

How could I do something like this in MSSQL? (Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

Could I do a print @current_cursor, or would that be a no-go in my case?

edit

I've tested the RAISEERROR with the following statements:

DECLARE @cur_id int;
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
    SELECT ID FROM TableA
    WHERE ID > 100  ORDER BY ID DESC

OPEN Cur
FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(@cur_id, 0, 0) WITH NOWAIT
    FETCH NEXT FROM Cur INTO @cur_id;
END

But this leads to some time of waiting without output and then an endless wall of (I assume one for every RAISERROR):

Msg 18054, Level 16, State 1, Line 17
Error 983700, severity 0, state 0 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

In SQL Server (and I think this should work in the ancient version you say you're using), the normal method for this is to use RAISERROR. PRINT won't display any output until the process finishes which is not very useful. I use this technique a lot in batch scripts for say updates and deletes where I want to keep the batch size small.

In the example below, I set a @Batchsize variable and my loop just works on that many rows at a time, then I output that with a RAISERROR. It's important to use a severity of 0, which means nothing will treat it as an error, just as an informational message.

DECLARE @MsgStr varchar(200) = 'Inserted ' + CAST(@BatchSize AS varchar(10)) + ' rows...'

RAISERROR(@MsgStr, 0, 0) WITH NOWAIT


PostgreSQL: Recommendation size for shared_buffers I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.

I am looking for help about the proper memory values for the configuration file.

Currently, I have a modest dedicated server (2GB of RAM) running Apache, MySQL and PostgreSQL under Linux. The main MySQL database are about 3 times the size of the main one in PostgreSQL, and the same could be said for the data traffic.

I still have the default configuration file for PostgreSQL, having the parameter  shared_buffers = 32MB. I assume that I should increase this value, but I do not want to penalize the other databases. I have read something about dedicating a 25% of physical available memory to PostgreSQL, but I am not sure about that.

The new database contains seven tables, having the biggest one about 3 MM rows. The machine has currently a low traffic (serving about 10000 ~ 20000 selects a day), but these values are expected to grow.

I was thinking about increasing shared_buffers to 256MB, but I would like to hear some opinions from users having more experience with this RDBMS.

I would avoid placing MySQL and PostgreSQL on the same server. They compete for the same resources. If you can, port everything to one RDBMS. My obvious choice would be PostgreSQL.

Then you can set shared_buffers to something like 500 MB and effective_cache_size to something like 1.5 GB. Be sure to read hints in the manual.

But I would also recommend to add more physical RAM. 2 GB is not much. Hardly enough for good performance with millions of rows. A few more GB of RAM shouldn't cost much.

If you have to stick with your setup 250 MB for Postgres seems reasonable. If MySQL has three times as much traffic, less might be better overall. Like 128 MB.

Basics for performance optimization in the Postgres Wiki.


Does AS/400 Login attempt reset? I'm currently trying to log into a remote AS/400 server. I was given credentials to obtain some data and do a couple tests. After 2 failed attempts, the iAccess Client showed me an alert saying my user would be blocked if the third attempt failed, so I stopped trying.

I'm reporting the event, but I need to know if the "attempt counter" resets after some time.

It will automatically reset AFTER a successful login is made.