What is the best way to upgrade a MySQL version in a Linux Server What is the best way to upgrade a MySQL version in a Linux Server

I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to move to 5.6 . I did similar activity in past for 5.1 to 5.5 upgrade .

Steps I followed is
1. Resolve any syntactical issue that can be faced in upgrader version of Mysql in code base  prior to upgrade.

at the day of upgrade
2.  Shut down the MySQL with application; take two backups (one with data and without data)
3. Uninstall and remove old MySQL instance and install new MySQL instance.
4. Import backup without data first and with data second and go live again.

I experience that during above process, it took very long time for importing backup file to database as some of our production servers run with less memory and during this remotely increase the risk as well.
This is the only best way I know, is there a best approach I can fallow to upgrade MySQL version in future with very less time  . Appreciate your help . Please note that I do not have facility to get another machine to do this, so I have to do in same production server where justifiable downtime is acceptable.

In most cases, in-place binary upgrade of MySQL 5.5 to MySQL 5.6 is possible without many problems. This is too small to detail all potential issues while performing the upgrade, but I will leave you the general steps:

Clone the MySQL server to a testing environment. I do not believe that you do not have 15GB on your laptop to test the upgrade. Worst case scenario, you could test it on the production machine, on a separate instance running on a different datadir, socket and port. Percona XtraBackup can be useful to create a MySQL clone with (almost) no downtime in a fast an efficient way.
Perform a test upgrade on your test machine. This is as "easy" as stopping the server, installing the new binary and restarting your environment. Just with this, you will find out about deprecated options that you will have to change in order avoid errors and warnings. Monitor the error log for that.
Once MySQL is up and running, you will need to execute mysql_upgrade from the 5.6 binaries. This is essential, or MySQL may crash. This will fix the system and privilege tables, and check if some tables need repair. Do as such (REPAIR TABLE ... for MyISAM, ALTER TABLE ENGINE=InnoDB, ALGORITHM=COPY for InnoDB tables). This will probably be the case at least for some time-related tables. You may even want to do that in some cases to get advantage of some of the new MySQL features. Make sure you read the documentation in order to know which variables and table structure may want to change on the new MySQL version. pt-config-diff is a great tool to identify differences on variable values between servers.
Now you can test freely for application incompatibilities: syntax changes and performance regressions (specially optimizer changes). As with any non-trivial upgrade, for every 10 queries that perform automagically better, you will find one that perform worse. Identify the underlying cause and fix your indexes/queries/table structure/configuration. You will find that 5.6 will perform generally better for subqueries, but I have identified certain default configuration change that may affect negatively your performance in some cases. pt-upgrade is a great open source tool to detect performance regressions and incompatibilities.
If you had enough hardware, maintaining the clone up-to-date with the MySQL replication would be ideal. Not only makes the process less painful, it allows to validate the upgrade by applying the master changes one by one. Remember that you still have the option of the 2 instances on the same machine, so do not just discard this option!
Once all testing has been successfully done, and assuming you have documented all the gotchas, you can proceed to perform the real upgrade. No surprises should happen now, if you have done your homework. Just 2 warnings:

Make sure that you perform a backup just after going into maintenance mode
Make sure you change the configuration so that a version rollback is possible (innodb_checksums, binlog options)

If everything works ok, you should just set mysql as read_only (and make the application fail gracefully), stop mysql, binaries upgrade, change configuration with the tested one, start mysql, mysql_upgrade, table repairs, additional fixes needed, battery of tests, monitor the error and slow log, read_only =0, and up again. Be careful, becase after a restart, the buffer pool will be completely empty (something that was fixed in 5.6 :-)), making the new queries very slow. It may take hours for the queries to fill the buffer again- there are ways to mitigate that problem.

There are cases in which an SQL dump may be needed or a better options, and there are some very-well-know issues that you may find on upgrade, but it is impossible to tell you absolutely everything you may find. Just read the official documentation on this and give me a ping if you need additional help.


Finding the first occurrence where id don't match I’m trying to find the first occurrence of SNum where the VerNum don’t match. In this case it should point to SNum = 300 as VerNum 12 is not present.

DROP TABLE #temp1
DROP TABLE #temp2

CREATE TABLE #temp1 (VerNum INT, SNum INT)

INSERT INTO #temp1
VALUES (11,100), (12,100), (11,200), (12,200), (11,300)

CREATE TABLE #temp2 (VerNum INT)

INSERT INTO  #temp2
VALUES (11), (12)

SELECT * FROM #temp1
SELECT * FROM #temp2

A full join is not working, i can loop through the table and get the result I am after, but there must be a better way. Any help is appreciated.

How about something like this?

select top 1 snum
from #temp1
group by SNum
having count(*) < (select count(*) from #temp2)
order by snum asc

This could work if you're sure that there are no duplicates vernum for the same snum

This by the way, i think is a more solid solution:

select top 1 coalesce(a.snum, b.snum) as result
from #temp1 a full join #temp1 b on a.SNum = b.SNum and a.VerNum <> b.VerNum
where a.VerNum is null or b.VerNum is null

I didn't test them much, only with the data you provided, so be careful!


Archive Relational OLTP Database by Year Our company is seeking to Archive old data.

After two years, we place from source: OLTP FinanceDB into ArchiveDB. We want to archive by Year of Customer Order Date. (eg Current year is 2018, any 2016 or below)

Order dates are different from Etlcreatedate and Etlupdatedates. Only one table dbo.SalesOrder has Order date. The rest of the related (parent, child, bridge) tables,  do not have Order date. This is a Inmon OLTP Relational database, so redundancy in order date will not occur.

Eg:

CustomerApplicationtable (shows customer information/name),

MktgTable (shows marketing material related to order),

Phonecall table (customer relation table),

FeeCommission table

These do not have order dates. How would I archive this? We have 100+ million rows?
I thought partition switching would not work unless every table has year column.

Should I add an archive partition indicator to each table and update, to find partition?

Should I utilize traditional etl, find all order dates with a join to SalesOrderdate table, and archive?

These do not have order dates. How would I archive this? We have 100+
  million rows? I thought partition switching would not work unless
  every table has year column.

To use partition switching to facilitate archivial by year, order-related tables don't necessarily need a new year column. Instead, you could add OrderDate to related tables and partition on OrderDate with yearly boundaries using a sliding window. All the tables would need the same partitioning OrderDate column and be aligned (indexes also partitioned).

This does introduce redundancy and would have significant implications on both the data model and OLTP query performance. The paritioning column needs to be part of the primary key, unique constraints, and indexes. Queries that don't specify the partitioning column (e.g. WHERE OrderID = @OrderID) would need to hit both partitions of the active tables.

My recommendation here would be to use a traditional ETL process for the archive rather than introduce significant changes that would only benefit the annual archive. If your business requirements allow, consider a daily or monthly archive to keep the process more manageable.


Sql join with three tables and multiple rows I have three tables like this:

    Table Runners:

  This table saves the runner info and the length of miles

ID|Name|BMP  |EMP  |Vendor
1 |Alex|12.25|22.25|Heinz
2 |Abby|0.00 |3.55 |Uni
3 |Marx|5.55 |10.55|Jam

  Table RaceDetails:   This table saves the direction(most races have up
  and down in which case the range switches)

ID|Dir|BMP  |EMP
1 |U  |12.26|12.33
1 |U  |12.33|12.47
1 |U  |21.12|21.99
1 |U  |22.00|22.44
1 |U  |22.15|22.36
2 |U  |0.00 |2.55
2 |U  |2.55 |4.56
2 |D  |4.56 |2.55
2 |D  |2.55 |0.00
3 |U  |5.55 |9.55
3 |U  |9.55 |11.69
3 |D  |11.69|9.55
3 |D  |9.55 |-0.12

  Table RaceSense:

ID|BMP  |EMP  |SENSOR
1 |12.25|20.33|1
1 |20.33|22.25|3
2 |0.00 |3.55 |2
3 |5.55 |9.55 |2
3 |9.55 |10.55|5

I am doing a join between them like this:

Select    distinct *
from      Runners a
left join RaceDetails b
on        a.ID=b.ID
left join RaceSense c
on        a.ID=c.ID

  This is giving me 22 rows because it duplicates data from Racedetails
  because of multiple rows for same ID in RaceSense table

I also tried this:

Select    distinct *
from      Runners a
left join RaceDetails b
on        a.ID=b.ID
left join RaceSense c
on        a.ID=c.ID
and      (b.BMP+b.EMP)/2>=c.BMP
and      (b.BMP+b.EMP)/2<=c.EMP

  This gives me 8 rows not 13 because some of the average fall outside
  the actual range from the RaceSense Table

I want 13 rows with no duplication, Like this:

ID|Name  |BMP  |EMP  |VENDOR|SENSOR
1 |Alex  |12.26|12.33|Heinz |1
1 |Alex  |12.33|12.47|Heinz |1
1 |Alex  |21.12|21.99|Heinz |3
1 |Alex  |22.00|22.44|Heinz |3
1 |Alex  |22.15|22.36|Heinz |3
2 |Abby  |0.00 |2.55 |Uni   |2
2 |Abby  |2.55 |4.56 |Uni   |2
2 |Abby  |4.56 |2.55 |Uni   |2
2 |Abby  |2.55 |0.00 |Uni   |2
3 |Marx  |5.55 |9.55 |Jam   |2
3 |Marx  |9.55 |11.69|Jam   |5
3 |Marx  |11.69|9.55 |Jam   |5
3 |Marx  |9.55 |-0.12|Jam   |2

The way I want to do it is:

1.if the BMP and EMP in Details table are within The Sense table BMP and EMP then that is good.
2.If either BMP or EMP in Details is outside either the BMP or EMP in Sense table then take the average of BMP and EMP in Details and ensure it is within the range of Sense table.
3.If the average value is also out of range then I want to expand the Sense table BMP and EMP by 0.1
4.Otherwise give me a null

Any ideas?

The data upload process should assign the accurate min and max values to BMP and EMP in the Sense table(they remain approximations). I would be working on the upload process to make it more accurate. However, to put this question to rest:

What I did to fix this was to find the min and max from the Details table and update the Sense table to match the actual range(how it should have been in the first place).
However, the problem I had was that when I use this:

  (b.BMP+b.EMP)/2>=c.BMP and (b.BMP+b.EMP)/2<=c.EMP

If the average is say 2.45 and the Sense table shows two ranges like this: 0 to 2.45 and 2.45 to 5 for the same ID then it would give me a duplicate.
So for now I changed the query to this and it works:

Select distinct *
from Runners a
left join RaceDetails b
on a.ID=b.ID
left join RaceSense c
on a.ID=c.ID
and (b.BMP+b.EMP)/2>=c.BMP
and (b.BMP+b.EMP)/2<c.EMP --removed = sign

If somebody comes up with a query that would work without updating the table in the long run. I would select that answer.


sp_Blitz, >1000 plans for CREATE PROCEDURE I'm using Brent Ozar's sp_Blitz, and one of its results is this:

Many Plans for One Query
1146 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.

The link in the result has this query:
SELECT q.PlanCount,
q.DistinctPlanCount,
qs.query_hash,
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY q.PlanCount DESC, q.query_hash;

...which shows the queries with the highest number of plans.
When I run this, one of the top results I get (with about 1150 plans for the same query hash) baffles me:

Maybe it's a bit hard to recognize on the screenshot - this is a complete CREATE PROCEDURE definition including comments, like this:
-- =============================================
-- Author:      my username
-- Create date: 14.09.2017
-- Description: blah
-- =============================================
CREATE PROCEDURE [dbo].[spCalcSomeStuff]
    @Orders OrderList readonly
AS
BEGIN

    -- do stuff (see below for more details what the SP does)

END

Plus, they're all the same.
I copied QueryText from multiple rows into text files and made diffs, and they're all 100% identical.
Any idea why this happens?
Our database objects are in source control, so I know that this particular SP was last changed about two months ago.
Even if we would delete and recreate it multiple times a day (which we don't do), I still don't understand why SQL Server creates that many plans for identical queries.

There's nothing special about this SP, except that it's one of the very few we have which uses Table-Valued Parameters.
Here's a simplified version of what the SP does:
create table #tmp
(
    [...]
)

insert into #tmp (...)
select ...
from tbOrders o
inner join @Orders x on o.Col1 = x.Col1 and o.Col2 = x.Col2

-- about 15 updates like this one (but more complex),
-- getting stuff from lots of different tables:
update t
set foo = o.foo
from #tmp t
inner join OtherTable o on t.bar = o.bar

-- and a few very simple updates:
update #tmp set ordertype = 'A' where producttype = 4
update #tmp set ordertype = 'B' where producttype = 2

select * from #tmp

When I run Aaron Bertrand's modified query, it returns the two simple UPDATE statements at the end.
I.e. I still get ~1150 rows for the same query hash, and half of them have this query text:
update #tmp set ordertype = 'A' where producttype = 4

...and the others have this one:
update #tmp set ordertype = 'B' where producttype = 2

Question 1: "What's with CREATE PROCEDURE?!?" When you execute a stored procedure, SQL Server stores the entire text of the stored procedure as the thing you called.

You weren't CREATING the stored procedure - you were only executing it - but this can be a little confusing for folks who are just getting started analyzing the plan cache.

So hey, you're now past that hurdle! Yay, you!

Question 2: "How can 1 stored procedure have multiple plans?" Without seeing the full text of it, it's hard to tell, but I'd start with Erland Sommarskog's epic post, Slow in the App, Fast in SSMS. In particular, check the section titled Different Plans for Different Settings.

I actually don't think that's the problem - I'm betting there's something dynamic inside the proc text - but I understand that you don't want to post your exact code here. Without seeing the exact code, it's tough for outsiders to answer that particular question.

Update: mystery solved. You mentioned casually that this stored procedure happens to use table-valued parameters. This is a known issue with how TVPs can be called. This is a great example of why it's so important to include the complete code that you have questions about - sometimes even the tiniest things can have a big impact on the question you're asking.


Oracle : How to grant to a ROLE the ability to affect a ROLE to a USER? I have a role : ADMIN_ROLE

I'm connected as ADMIN_ROLE and created a TEST_USER and TEST_ROLE

I would like to be able to affect TEST_ROLE to TEST_USER using ADMIN_ROLE

I did (using system user) :

Grant create role to ADMIN_ROLE with admin option;
Grant create user to ADMIN_ROLE with admin option;

but when executing from ADMIN_ROLE :

Grant TEST_ROLE to TEST_USER

I'm receiving error: ORA 1932 - ADMIN option not granted for role string.

Any ideas please ?

You must fist grant the role (test_role) to the user (admin_role) with admin option, like this:

grant TEST_ROLE to ADMIN_ROLE with admin option;

I suggest that you name your users in a different way. admin_role is a user in your case, it would be better to name it admin_user.


Are there side effects to Postgres 12's NOT MATERIALIZED directive? I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the same between PG10 and PG12.

My next experiment was to add the NOT MATERIALIZED directive to the CTEs and the result was astounding:  it cut query times dramatically (halved them in some cases).

I read here that MATERIALIZED was the default functionality prior to PG12. And that functionality would write all the contents of the CTE into a temporary location.

So my question is mainly around NOT MATERIALIZED:

What does the NOT MATERIALIZED functionality do with the data behind the scenes in contrast to MATERIALIZED?
Are there any side effects to NOT MATERIALIZED I should be aware of before refactoring our codebase?

It's explained very well in the documentation.

  A useful property of WITH queries is that they are normally evaluated
  only once per execution of the parent query, even if they are referred
  to more than once by the parent query or sibling WITH queries. Thus,
  expensive calculations that are needed in multiple places can be
  placed within a WITH query to avoid redundant work. Another possible
  application is to prevent unwanted multiple evaluations of functions
  with side-effects.

So far, so good, BUT:

  However, the other side of this coin is that the optimizer is not able
  to push restrictions from the parent query down into a
  multiply-referenced WITH query, since that might affect all uses of
  the WITH query's output when it should affect only one. The
  multiply-referenced WITH query will be evaluated as written, without
  suppression of rows that the parent query might discard afterwards.

So, as pointed out in the example given, if you have a query like this:

WITH w AS (
    SELECT * FROM big_table  -- big_table has an INDEX on a field called key!
)
SELECT * FROM w AS w1
  JOIN w AS w2 ON w1.key = w2.ref  -- w is called TWICE, so DEFAULT is MATERIALIZED
                                   -- PostgreSQL can't take advantage of big_table.key
WHERE w2.key = 123;

So, in this case:

  the WITH query will be materialized, producing a temporary copy of big_table that is > then joined with itself — without benefit of any index

Far better to have:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

So that the optimizer can "fold" the CTE query "into" the main query and make use of the INDEX on the key field of big_table!

Re. the DEFAULT of NOT MATERIALIZED:

  However, if a WITH query is non-recursive and side-effect-free (that
  is, it is a SELECT containing no volatile functions) then it can be
  folded into the parent query, allowing joint optimization of the two
  query levels. By default, this happens if the parent query references
  the WITH query just once, but not if it references the WITH query more
  than once.

So the DEFAULT is NOT MATERIALIZED if:

    the_query IS NOT recursive
AND the_query is_side_effect_free
AND the_query is_run_only_once

otherwise you have to tell PostgreSQL to use NOT MATERIALIZED.

The only small problem that I see is that testing will be required to see if
NOT MATERIALIZED is an improvement or not? I can see circumstances where the balance will swing between the two depending on table size, fields selected and indexes on the fields and tables used in the CTE - in other words, there's no substitute for knowledge and experience. The DBA isn't dead and gone yet! :-)


Generating sequences partitioned or grouped by field(s) Given a table like:

Id     Grouping    SequenceId
1         A            1
2         B            1
3         A            2
4         A            3
5         C            1
6         B            2

Where SequenceId increments based on Grouping, like the result of DENSE_RANK()

I would like to efficiently generate the next SequenceId on INSERT.  SQL Server has SEQUENCE, but I would need a sequence for each Grouping.  I could also create a table like the following (perhaps with a few more columns for flexibility):

Grouping     NextValue
   A             4
   B             3
   C             2

and a procedure to atomically fetch the next value and update the table, but this wouldn't be as effecient as a SEQUENCE and feels an awful lot like reinventing the wheel.  I just haven't found the wheel I'm looking for yet.

What is the efficient and scale-able way to generate sequences per grouping, when the number of sequences can be huge and I want to avoid the performance hit at read time of an aggregate function like DENSE_RANK()?

Ultimately, the best solution will depend on the specific details of what you need, and how various solution perform in your environment.

First: using a SEQUENCE or a table of virtual "sequences" will result in behavior closer to ROW_NUMBER than to DENSE_RANK - with DENSE_RANK, if the value you're ordering on (say, a record creation date) is the same for three records in your partition, they'd all get the same rank, but they'd each get a unique row number.

Second, even using a SEQUENCE, you cannot guarantee that there will be no gaps in your numbering. If you grab the next value from the sequence, but end up rolling back the transaction, the sequence number is still consumed.

An additional concern in your situation is managing the sequences. You would presumably need one sequence for each group you created. Creating and using the sequences would require dynamic SQL. It might not be vulnerable to SQL injection (if all the dynamic values are system-generated rather than user-input-generated), but this could still be a concern for some.

Also, you'd need a way to ensure that the Grouping values were properly tied to the sequences. If those values are user-facing, then someone will want to change one at some point - if the sequence names depend on that user-facing value, then the original sequence would be lost after a change, and a new one would presumably have to be started (or, you'd have to include code to update the sequence name after a change). More practically, you might need a table with a Grouping ID, and the name of the corresponding SEQUENCE.

Using a table of "virtual sequences" lets you avoid some of these problems, but can create another, much larger one - blocking. If you include retrieving the next value from the virtual sequence and updating it in the transaction to create your record, then, if the transaction is rolled back, then so is the sequence number change; however, you can't add another record to the table until that first transaction is completed. I worked with a table like that, and almost every deadlock we encountered came back to that table. And, if you grab the next ID outside the transaction where you insert a new record, then you're back to maybe having gaps in the ID range.

Of course, you should consider that gaps can happen naturally, as well - whenever a record is deleted, there will be a gap. Are those gaps tolerated/desired, or does the business owner of the process really want to see 1, 2, 3, 4, 5, even if the rows are actually 1, 3, 4, 6, 7 (because 2 and 5 were deleted)?

So, work out what the actual reasoning behind the requirement is. If there is an absolute need to have the report show sequences with no gaps whatsoever, then ROW_NUMBER (or, possibly, if you want the same value for ties, DENSE_RANK) is probably the best solution available to you, and I'd check on just how much of a performance hit it causes. If some gaps (especially for deleted rows) should be retained, you almost have to go with one of the options you suggested above. If rollbacks are rare in your application (and I've seen some where that's true), a SEQUENCE is probably your best bet, performance wise. The "virtual sequence" table has to be handled very carefully, or it can be a major performance bottleneck; but, it can work (I should note that most of those deadlock issues I mentioned earlier occurred in jobs processing bulk data inserts, not in normal user activity in the application, and additional routines to grab a range of values instead of one at a time were created - however, using those increased the likelihood of a skipped values again).

Make sure the business owner requesting this understands that providing permanent sequence values with no gaps can have significant impact on performance, etc., and see if the requirement is really a must-have, or a nice-to-have (or, actually requires ROW_NUMBER or DENSE_RANK).


DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS I had to run dbcc checkdb repair_allow_data_loss with all_errormsgs and it's been runing over 27 hours. DB size is over 15gb and the server (windows 2008) has 32gb memory. I did run this on SQL 2005 and as I said it's been going over 27 hours. Is this normal? If not, can I terminate it without causing any problem on the DB? I did take backup before I run it.

can I terminate it without causing any problem on the DB

You already caused the problem, simply by running repair_allow_data_loss. Allow data loss is really the very very very last resort, after all other options have been exhausted. The appropriate action is to restore from a correct backup and re-apply missed transactions.

Can DBCC take 27 hours? Yes, I know of DBCC commands that took well over 8 days to complete. You can investigate and see if DBCC is blocked (and Shark give you the right approach for that) and if you're lucky it is blocked and you can unblock it and let it finish. But if is making progress though there is not much you can do but wait it out

If you terminate it, will the database be any worse that it already is? In theory no, because DBCC, like any other operation, is transitionally consistent and any in-flight operation will be rolled back. However, in practice, the very fact that you're running DBCC repair means you're dealing with corrupted structures, and in such case there is always a risk.

  I did take backup before I run it.

Validate the backup by restoring it on a different machine or as a different DB on the same instance. If the backup is correct, then you're in a much better place because you can always return to this backup. Also, make sure no new business transactions occurred after you took the backup.


Is the keyword "ALIAS" actually used? According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved word. The old PostgreSQL docs do say "the presence of a key word does not indicate the existence of a feature." When aliasing a table or column I've seen AS, but never ALIAS.

Where is (or was) the SQL keyword ALIAS used? Was it ever in-use or only ever reserved for future-use?

PostgreSQL maintains a list of reserved and non-reserved terms in the appendix. ALIAS is absent from that list. You can verify PostgreSQL does not use ALIAS by checking out the YACC grammar. Even as far back as Postgres95 ALIAS was not a reserved word (the first version in the migration from QUEL to SQL)

 MySQL does NOT reserve ALIAS
 MariaDB does NOT reserve ALIAS
 Oracle does NOT reserve ALIAS
 SQL Server does (in T-SQL) reserve ALIAS (as a potential future keyword on their "reserved keyword" list)

SQL Standard

In SQL-92, ALIAS was marked as a <reserved word>; but, there was no use assigned for that <reserved word>.
In SQL-99 ALIAS was marked as an "Additional Reserved Word", and added to the list of <reserved word>; but, there was no use assigned for that <reserved word>. Perhaps they reserved the term with the intent to define meaning later, and then withdrew it at a different point. Or, perhaps they reserved the term for vendor defined implementation. PostgreSQL reflected the spec's reservation in the docs, and then removed that reservation with the spec.
In SQL-2011, ALIAS is no where to be found and the word "alias" only appears in reference to 'Feature T053, “Explicit aliases for all-fields reference”'

ℹ There is no digitized copy of SQL-86, or SQL-89


It is possible to get to the desired result in a simpler way.

First, you could start with this simple query:

SELECT
  t1.id,
  t2.env,
  CONCAT(t3.inftype,
         ': [',
         GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
         ']'
        ) AS inftypeandnames
FROM
  table1 AS t1
  INNER JOIN table2 AS t2 ON t1.id = t2.id
  INNER JOIN table3 AS t3 ON t2.infid = t3.infid
GROUP BY
  t1.id,
  t2.env,
  t3.inftype

This is what you would get as the result for your data samples:

id  env  inftypeandnames
--  ---  ---------------
1   D    Db: [kli]
1   D    Srv: [pop]
1   P    Db: [hgj, kjk]
1   P    Srv: [abc, xyz]

Next, you could use the above result as a derived table and group it by id, env, concatenating the inftypeandnames values, like this:

SELECT
  id,
  env,
  GROUP_CONCAT(inftypeandnames ORDER BY inftypeandnames DESC SEPARATOR ' ') AS inftypesandnames
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id,
  env

The result would be very close to what you want:

id  env  inftypesandnames
--  ---  ------------------------------
1   D    Srv: [pop] Db: [kli]
1   P    Srv: [abc, xyz] Db: [hgj, kjk]

In order to split the inftypesandnames values into separate columns based on the values of env, you could duplicate the above query, apply a filter on env to each copy (env = 'D', env = 'P' and so on if there can be others), join the results and return each subset's inftypesandnames value in its own column – same as Rolando has suggested, although I would argue that you might need a full outer join rather than an inner join if it is possible for some ids to have only D while for some others only P. Since MySQL does not support the FULL JOIN / FULL OUTER JOIN syntax and the usual workaround to that is a union of a left join and a right join, I suspect the final query in this case would make Rolando's brain hurt even more.

However, there is no need to resort to duplicating subqueries like that, because you can pivot the results using conditional aggregation. Instead of grouping by id, env in the outer query, you would be grouping by id only and conditionally apply GROUP_CONCAT to the inftypeandnames values based on env, like this:

SELECT
  id,
  GROUP_CONCAT(CASE env WHEN 'P' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS P,
  GROUP_CONCAT(CASE env WHEN 'D' THEN inftypeandnames END ORDER BY inftypeandnames DESC SEPARATOR ' ') AS D
FROM
  (
    SELECT
      t1.id,
      t2.env,
      CONCAT(t3.inftype,
             ': [',
             GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
             ']'
            ) AS inftypeandnames
    FROM
      table1 AS t1
      INNER JOIN table2 AS t2 ON t1.id = t2.id
      INNER JOIN table3 AS t3 ON t2.infid = t3.infid
    GROUP BY
      t1.id,
      t2.env,
      t3.inftype
  ) AS s
GROUP BY
  id
;

You can verify for yourself that the result of the last query would match your requirements:

id  P                               D
--  ------------------------------  --------------------
1   Srv: [abc, xyz] Db: [hgj, kjk]  Srv: [pop] Db: [kli]


Why can't I script out CLR assemblies with SMO in Powershell? I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most part my script works well and has helped identify a few gotchas in some old procs and functions, but I cannot get it to script out CLR functions.

There are only one or two and if I right click on them in SSMS and create script to new query editor window it works fine and I can then run that on the destination DB and it creates the assembly, but when I try and use SMO in Powershell it just does not script the assemblies (it doesn't error either). In essence, this is my script (left out the bits which create the connections/DB objects etc):

Write-Host "Getting DB objects..."
$assemblies     = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema  }

# Set scripter options to ensure only schema is scripted
$scripter.Options.ScriptSchema  = $true;
$scripter.Options.ScriptData    = $false;

#Exclude GOs after every line
$scripter.Options.NoCommandTerminator   = $false;
$scripter.Options.ToFileOnly            = $false
$scripter.Options.AllowSystemObjects    = $false
$scripter.Options.Permissions           = $true
#$scripter.Options.DriAllConstraints     = $true
$scripter.Options.DriForeignKeys        = $false
$scripter.Options.SchemaQualify         = $true
$scripter.Options.AnsiFile              = $true

$scripter.Options.Indexes               = $true
$scripter.Options.DriIndexes            = $true
$scripter.Options.DriClustered          = $true
$scripter.Options.DriNonClustered       = $true
$scripter.Options.NonClusteredIndexes   = $true
$scripter.Options.ClusteredIndexes      = $true
$scripter.Options.FullTextIndexes       = $true

$scripter.Options.EnforceScriptingOptions   = $true

function CopyObjectsToDestination($objects) {

    foreach ($o in $objects) {

        if ($o -ne $null) {
            try {
                Write-Host "Writing " $o.Name
                $script = $scripter.Script($o)

                $destDb.ExecuteNonQuery($script)
            } catch {
                #Make sure any errors are logged by the SQL job.
                $ex = $_.Exception
                $message = $ex.message
                $ex = $ex.InnerException

                while ($ex.InnerException) {
                    $message += "`n$ex.InnerException.message"
                    $ex = $ex.InnerException
                }
                Write-Error $message
            }
        }
    }
}

# Output the scripts
Write-Host "Create assemblies in destination database..."
CopyObjectsToDestination $assemblies

I feel like I'm missing something simple and obvious because I can get tables, procs, functions, views and the like without any problems at all. What have I missed?

The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results:

$assemblies     = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema  }

I'm not sure whether you need to use the owner property in the filter instead or ignore ownership entirely.


MySQL replication - slave hangs during ALTER table or BULK update - v5.1.71 We are facing the above mention issue. In this case if we try to STOP SLAVE then it hangs and we even can to restart DB.

As I search in google for the same it looks like a bug. Id 38205 in V5.1.

My question is if we upgrade to 5.6, is it fixed and if yes what should be the exact version of 5.6.

Thanks
Shyama Prasad

ALTER TABLE operations before 5.6 always required a full write table lock (which means the whole table could be read, but not written). Bulk updates depend on the kind of operations and the ENGINE used (for example, LOAD DATA will lock the whole table on MyISAM, but it will only create row-based locks on InnoDB tables.

If you are in a replication environment, executing an alter table before 5.6 will:

Lock the table on the master
Alter the structure and insert the original data of the table (on the master) using a temporary table, which may take a long time. This is not true for all operations- some name-only changes or secondary indexes manipulation on the InnoDB plugin may be faster and may not require a full table copy. While this is happening, replication is up and running.
Unlock the tables and log the statement (using STATEMENT binary log format) to the binary log on the master
The log is transmitted to the slave
The slave will apply the changes as in the same way it is done on the steps 1-3, with a big difference: the slave will not apply other changes while this is happening because replication is single threaded (so it does apply the changes in the same order than originally)

This 5th step is problematic (and probably the reason why you think that the server is hanged, because in the master, other actions could be applied in parallel to other objects, but not on the slave.

In case of bulk updates, a lock may or not be involved, but you have an additional problem: logging the changes though the binary log is usually very slow (slower than its direct application).

Then there is the issue with stopping a slave: when you try to stop slave or restart the server, any other ongoing operation would be killed, but application of slaves is allowed to finish to avoid replication issues (the ones you mention on your bug report). You can actually kill the threads, but it may only make things worse, breaking your replication on non-transactional tables (or non-transactional replication control files) or falling back to a rollback, that could take even more time that a commit.

Does 5.6 solve anything of this? Well, in some cases, it may help reducing the problems:

it introduces transactional on-table replication control to minimize problems when the slave crashes.
it allows certain ALTER TABLEs on InnoDB tables to run in a hot way, allowing concurrent writes
It has multithreaded replication, but it is only useful for multi-tenant databases (one thread per schema). Real multithreading will be available on 5.7.

So I would say that it will not provide you 100% solution to your problem.

However, what I would recommend you is to perform schema changes and data loads in a slave -> master order (assuming that they are changes that are backwards compatible) and editing directly every node instead of using the binary log.

For example, you would execute this:

SET SQL_LOG_BIN = 0
-- Blocking operation, like ALTER or LOAD DATA, etc.
SET SQL_LOG_BIN = 1

On the slave first, and then on the master, so that you write those independently. To minimize downtime, you can do a switchover between servers so that at any time, the active node is not blocked for reading nor writing.

Of course, in order for this to be consistent, you need to make sure the changes are compatible (adding a column is ok, deleting one is not), and that your application does not use the new version until both servers have been migrated. Basically, as locking mechanisms are avoided, you are in charge of making sure that your changes are consistent.


join value in a RETURNING which is not inserted I'm doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert. Is it possible? I get a "column doesn't exist" error.

INSERT INTO new_table (x,y)
select A.x,B.y
from A
  join B on A.w = B.z
RETURNING id,B.z;

The new_table has a unique constraint on (x,y).

The new_id and B.z are needed to insert into a second table.

Perhaps there's a better alternative but I can only think of joining back to the 2 tables.

This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:

with ins (id, x, y) as
( insert into new_table (x, y)
  select A.x, B.y
  from A join B on A.w = B.z
  returning id, x, y
)
-- insert into another_table (id, z)
select
    ins.id, B.z        -- whatever columns from the 3 tables
from ins
  join A on A.x = ins.x
  join B on B.y = ins.y and A.w = B.z ;


What are some important MySQL status variables to keep an eye on? I'm a fairly new DBA with a programming background. As such, I like to build tools to manage tasks to help me familiarize myself with the intricacies of a technology.

In this case, I am managing multiple MySQL servers and would like to build a UI to view the health of the servers.

What are the important status variables to watch out for that throw a red flag that something isn't right on a long-running server (>48 hours)?

An example of what I'm looking for is qcache_lowmem_prunes becoming larger could indicate a misconfigured query_cache

Here's a link to a bunch of links on "tuning best practices."

The short form is: It depends. What are you tuning for? Tuning read performance is different than write performance and everything changes when you move to a cluster. There is no one set of "magic variables" that can be quietly optimized for every situation.

From these, you can look at monitoring variables, as the variables that need to be tuned are strongly related to the variables that require monitoring.


How to grant permissions for User defined type in Oracle This is probably easy but i can't seem to figure it out. I created my user-defined type as CO_ADMIN. However, the application using the type runs using GS_USER database account.

I have granted permissions to GS_USER as CO_ADMIN but yet anytime my application attempts to use the user defined type, i always get:

: type ""."DOCPARAMETERTYPE_TABLE" not found    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Types.OracleUdtDescriptor..ctor(OracleConnection con, String schemaName, String typeName)

Here are the statements i have tried so far. All say "Grant succeeded" after running them but it's not true or perhaps i am using the wrong statements. Please help:

GRANT all on "CO_ADMIN"."DOCPARAMETERTYPE" to "GS_USER" with grant option;
GRANT all on "CO_ADMIN"."DOCPARAMETERTYPE_TABLE" to "GS_USER" with grant option;

GRANT EXECUTE ON "CO_ADMIN"."DOCPARAMETERTYPE" TO "GS_USER";
GRANT EXECUTE ON "CO_ADMIN"."DOCPARAMETERTYPE_TABLE" TO "GS_USER";

Here are the definitions for the types:

create or replace type DOCPARAMETERTYPE as object (
docID varchar2(20),
system varchar(50)
);

create or replace type DOCPARAMETERTYPE_TABLE AS TABLE of DOCPARAMETERTYPE;

I think you need to either qualify type with schema when using client account (CO_ADMIN.DOCPARAMETERTYPE_TABLE, not just DOCPARAMETERTYPE_TABLE ) or create a public synonym for the type.


nulls on pg_stat_get_progress_info('VACUUM') columns on pg_exporter role I'm trying to export the results of a query select * from pg_stat_progress_vacuum;. The problem is that the only column values I can see from the created user are:

pid
datid
datname

Other columns are constantly null. When query is run from superuser account there are values of course.
Based on pg_stat_progress_vacuum view definition I noticed that the problematic part is usage of pg_stat_get_progress_info function with VACUUM parameter (view names params returned by this function).
User has pg_monitor role assigned.
What am I missing?
Result of select * from pg_stat_progress_vacuum; from monitoring user
[
  {
    "pid": 33079,
    "datid": 16410,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  },
  {
    "pid": 38625,
    "datid": 121786502,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  }
]

Result from superuser for the same query
postgres=# select * from pg_stat_progress_vacuum;
  pid  |   datid   |     datname      |   relid   |       phase       | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
-------+-----------+------------------+-----------+-------------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 38625 | 121786502 | <database name> | 121786624 | vacuuming indexes |         7091104 |           7091104 |                  0 |                  0 |       178956970 |        73659774
(1 row)

A couple of seconds passed from one query execution to the other, so I assume that just autovacuum has finished thus the difference in row count.
Postgres version is 12.2

You are missing the required privileges.
To see all values in a line of pg_stat_progress_vacuum, one of the following must be satisfied:

you are a superuser

the VACUUM is run by the same user as your current connection

you are a member of the system role pg_read_all_stats


Primary key type change not reflected in foreign keys with MySQL Workbench I have a problem with MySQL Workbench and primary/foreign keys.

I have some tables with PKs involved in relationship with other tables. If I modify the type of the PK, the type of the FK doesn't automatically update to reflect the change.

Is there any solution? Do I have to manually modify all the relations?

Actually cascade updates for data types are not supported by Workbench.

One workaround, is to define User Data Types, use those in your tables, and if necessary, update the User Data Type. That will generate the correct DDL.

I usually create User Data Types for PrimaryKeys, Name, Description, Money, etc.

In your case will not work, as you should do that before creating the tables.
Now, if you must replace it in all your tables, do it for a User Type.

Other workaround for you will be to generate the database as is, go with the sugestion above, and then re-import the model to continue design.


Oracle Database Upgrade Assistant "Failed in bringing up..." Error So I was upgrading 3 databases from 10.2.0.5 to 11.2.0.4. Two of them went just fine. Long story short for the last one, the upgrade was interrupted near the end (let me know if more details are necessary). Tried a couple of things, then just decided to restore because I have never manually finished upgrading a database before. It would not have been time efficient.

Restored, ran through the steps again to prepare for an upgrade, run the upgrade assistant script for the 3rd database. The first screen correctly states that the Oracle Home = 10.2.0.5, but after hitting next was told that the database needed to be mounted before the upgrade began. Database was in startup mode. So I clicked next again to see if I would get a different message. The next message I got read,

  "The Upgrade Assistant filed in bringing up the database [name]. Oracle >Home /oracle/product/11.2.0.4/db_1 obtaining from the >file /var/opt/oracle/oratab was used to connect to the database. Either the >database is not running from  Oracle Home /oracle/product/11.2.0.4/db_1 or the >correct Initialization Parameter file (pfile) was not found."

It then prompted me to give the path for the init file. But before that, I checked the oratab; it correctly stated 10.2.0.5 for that database. Went ahead with the giving the path to the init file, returned the same error without the option of choosing the init file. Tried bringing over a good copy of the pfile, changed the parameters I needed to change, deleted the spfile and created a new one from the pfile, then tried again with the same results.

So I am guessing that someone along the line during that interrupted upgrade, some reference that I don't know about was changed that is telling the Upgrade Assistant that the original database Oracle Home is in 11.2.0.4 instead of what the oratab and listener actually say, 10.2.0.5. Any insight to what I need to change here would be greatly appreciated as I do not want to do another restore without a plan of what I would do differently than the last time I did so.

So I found the solution after much investigation and trial and error. I deleted the .log files from $ORACLE_BASE/cfgtoollogs/dbua/logs. After restarting the session and the UA, it worked fine. I'm not entirely sure WHY that worked (which bugs me), but there's the answer. Hopefully this will be helpful to others in this situation.

Thanks to those who commented on my question.


Accelerated Database Recovery with AlwaysON -- long recovery time for secondary unexpeccted So I was intrigued by the new SQL 2019 Feature Accelerated Database Recovery. It has some great use cases, such as being able to kill a long-running transaction and have it instantly rollback.
I had read that it's supposed to improve database recovery much faster.
I was shocked to find out that in a load test comparison of an ADR-enabled and NON-ADR (default) database, the recovery time for the SECONDARY to begin syncing with the NEW primary was order of magnitude slower.
To date, I have been unable to find much information on ADR with AlwaysOn -- except for a passing mention of it in a SQL 2019 talk he did, in which he claimed it improved the performance of AlwaysOn failover.
While the NEW Primary came up right away (although there was only milliseconds difference between the two databases), it took almost 3 minutes before the ADR-enabled database showed "Synchronized" in SSMS!
I ran several tests (which included serveral very large updates, and even included a large statement with a BEGIN TRAN, but no COMMIT.  All test iterations had similar result:  Recovery/Synchronizing of the SECODARY was order of magnitude long for the ADR-enabled database.
Has anyone else seen this behavior?

I belive that Everything in the persisten version store would be transaction-log-causing activity, right?
This means that everything affected by that would receive a performance hit:
– log size/backups
– checkdb (gets hit by db size too)
– ALWAYS ON – meaning it could get DELAYS FOR SYNCHRONOUS AG


SQL Always On automatic failover incase of multiple secondary I have three node setup for SQL Always on in SQL 2017 enterprise edition. All three are in same subnet and in synchronous commit mode. Now when there is a failover how do I make sure it should always choose node-2 to be made primary and stall node-3 for being primary.

Disable automatic failover for Node 3. This way it will always automatically failover between Nodes 1 & 2 only. If you encounter a scenario where one of these nodes dies and you need Node 3 to become an automatic failover node, simply change the setting.


SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index I'm running a 350GB database on my PC with ~40 million rows.

  SQL Server 2014, Win7, AMD 8350 @ 4.8GHZ, 16 GB of RAM, and a 500 GB
  SSD (database is hosted on it's own 500 GB SSD, with a throughput of
  500MB/500MB read/write).

The database is not being updated, I'm just analyzing/reading it. With the creation of a few indexes, any join, count(*) etc, takes less than 1 minute, which is ok for my purposes. I have been running some queries (after running a single join query, 40-50 times, it becomes slow) on the data, and now calls that took 1 minute, are still running 20 minutes later.

I keep a careful eye on system resources, and I can see the SSD kick in when the query starts, it reads for 20-30 seconds, then it reads at 121kB/second for the next 20 minutes. This is not a CPU problem, or disk problem. I am limited with my amount of RAM, however the calls run fine when I first loaded the database, now, nothing runs, 25 minutes later.

Effectively I cannot query the database anymore, any call takes excessively long even a basic SELECT statement. I have tried rebuilding the indexes and updating statistics, but no difference.

I don't have a lot of experience on this so it's entirely possible that my SQL query is incorrect, in which case I'd expect an error, or for it to finish executing with 0 results, but neither occurs.

What I am trying to do is count all instances of a 'TypeID', in the 5 seconds prior to a time based on the table ACALLS.

SELECT ACALLS.StartTime, ACALLS.Time, ACALLS.ServerIP, ACALLS.SRVR,   ACALLS.calls, ACALLS.TOKEN, COUNT(TypeID) as ExecRate
FROM ACALLS
INNER JOIN MAINVIEW ON
MainView.TimeStamp BETWEEN ACALLS.StartTime and DATEADD(ss,-5,ACALLS.StartTime)

WHERE DATEPART(hour,MainView.TimeStamp) BETWEEN 10 and 13 and
CAST(MainView.TimeStamp as date) = '2015-12-09' and
MainView.TypeID = '123456789'
GROUP BY Acalls.STartTime, ACALLs.TIME, ServerIp,SRVR, ACALLS.CALLS, ACALLS.TOKEN
ORDER BY Acalls.StartTime

After running "Who is Running",

You have a non-SARGable query - even if you have good indexes, you're not using them with that query.

First, a knee-jerk reaction to pageiolatch_sh is reading pages from disk into the buffer; you don't have enough RAM for the data it's trying to pull.

Second, you need to look at the execution plan and its use of indexes - or the lack thereof.

Quit using functions in your joins and your WHERE, and only get the data you absolutely need to start with.

"BETWEEN ACALLS.StartTime and DATEADD(ss,-5,ACALLS.StartTime)" - get rid of that DATEADD in the BETWEEN.

If this is a read only, reporting database, then create a reporting table with just the data you need, and then put compound indexes on it as required.  Use that one to get the primary keys/other unique keys of the ACALLS rows you need, then get the rest of the ACALLS data later.

WHERE DATEPART(hour,MainView.TimeStamp) BETWEEN 10 and 13 and
CAST(MainView.TimeStamp as date) = '2015-12-09'

same thing - get rid of the CAST - change '2015-12-09' to one or two @parameters of the right data type for MainView.TimeStamp >= @StartTimestamp AND MainView.TimeStamp < @EndTimestamp
and get rid of that DATEPART by restricting the @StartTimestamp and @EndTimestamp to include your hours criteria as well.

Perhaps load up a #temp table with just the primary/unique keys of rows that meet those MainView criteria before the join.

Hmm... also, if Mainview is a complex view, go straight to the base tables to load up that #temp table

Don't forget to use Profiler to check and see if adding (compound if need be) indexes on the #temp or other staging table is a net gain or a net loss :).


Prevent timing attacks in postgres? I'm looking into accepting an API token from a user, and I would like to prevent timing attacks. The conventional wisdom seems to be that one should hash the token server-side in order to make the comparison non-deterministic. If you have some other identifier you can look up the record by that and then do a constant-time in-memory comparison, as with Rails secure_compare. In my case, though, I was planning on using the token to look up the user. I'm wondering if, by any chance, Postgres has some facility for doing constant-time comparisons when looking up records. Something that might look like:
SELECT * FROM users WHERE secure_compare(token, 'abcdef')

Damn, my sleep() idea was oversimplistic!
SELECT * FROM users WHERE secure_compare(token, 'abcdef')
or...
SELECT * FROM users WHERE token = 'abcdef'

You'll want to index that to make it fast, which means timing information can be leaked not just from the string comparison, but also from the btree index walk. Can we actually do that?
CREATE UNLOGGED TABLE foo (t TEXT PRIMARY KEY);
INSERT INTO foo SELECT substr(sha256(n::TEXT::BYTEA)::TEXT,3) FROM generate_series(100000,199999) n;

...and python
def gettime(sql,N=300):
    t = time.time()
    for _ in range(N):
        cur.execute(sql)
    return (time.time()-t)/N

for sql in  ("select * from foo where t='3000000000000000000000000000000000000000000000000000000000000000';",
            "select * from foo where t='z000000000000000000000000000000000000000000000000000000000000000';",
            "select * from foo where t='3bb78535cc9555ff19fe3556aaa41c78a0a45c64d49ba2bc564507648a000000';"):
    print( sql )
    gettime(sql)
    r = [gettime(sql) for n in range(1000)]
    plt.hist(r,bins=30,label=sql)
plt.legend()
plt.show()

Oh yes, definitely a timing difference, about a microsecond between the case with no rows (hexadecimal hashes never start with a 'z') and the one that starts with a '3' which has plenty of matches in the table. I forgot to label the x axis as "time". Since this is most likely due to the path it takes walking through the btree index depending on the value of the hash, using a secure comparison function won't do a thing.

Let's try with a hash index instead of the btree:

Much more similar timings, in fact I had to set the histograms to be transparent since they overlap each other so much. Now, averaged over a much larger number of queries, there would probably be a detectable difference anyway. You could try it out...
What I'm saying is you're focusing on the string comparison you can see, in your query, but what about the rest of postgres code? As seen above, the btree index timing definitely leaks information. Hash doesn't seem to, the index only stores references to tuples that contain values having the same hash, and then pg goes to check the tuples. So perhaps with a bit of hacking, even the hash index could leak timing showing there are rows with the same hash in the bucket, depending how long it spends checking for tuples. And the postgres hash function isn't designed to be secure, so who knows. Besides, it will still use the equality operator, which is a string comparison. And if you want to index your own operator, you'll have to write a lot of C code.
What you could do to avoid too much hassle would be to index a BIGINT instead, which would contain some 64-bit hash of your API key. BIGINT comparisons should be constant time. And then index that with a hash index to make it fast, so you only need to run the secure_compare on the row matched by the index. That lets you use a super dumb version of secure_compare, like a bit of plpgsql that compares all characters in the string, and does not exit on the first character that does not match. Hell you could even use
SELECT sum(substr(str1,n,1) != substr(str2,n,1))
FROM generate_series(1,your_string_length) n;

But that doesn't guarantee that no part of postgres code will leak info.
I'd still hack it with a sleep(). But, instead of being random, I'd take the timestamp T_start of the beginning of the HTTP request, do all the work, and then sleep until say, T_start+100ms. That way, all requests will take about 100ms no matter what actually happens, even in code you didn't look at.


Optimize speed on 400GB MyISAM table We have MariaDB server with single table with image url information. The table is about 400GB on disk and contains probably 400M rows.

Table is partitioned in 1024 partitions.

All queries are similar to this one:

select * from container where id in (1234, 1235 ... );

The sql usually took 2 sec to be executed.

Each row contains a single image url, a title and keywords.

Keybufer is set to 8GB.

This set up works well, until we begin to insert aditioan rows. We tried normal inserts, also low_priority inserts. It is slow in both cases.

I wonder what else we can tweak in order to speed up the selects.

Update:
the table has no indexes, except the primary key on a bigint field e.g. primary key(id)

Update 2:
Here is some more information:

Create Table

CREATE TABLE `container` (
  `id` bigint(20) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=binary
/*!50100 PARTITION BY KEY (id)
PARTITIONS 1024 */

data field stores standard JSON. It is UTF8 text, but because of wrong encoding of the input data, we were forced to store it in blob + binary encoding.

Size on disk

# du -h /usr/local/mysql/var/mydb/
371G    /usr/local/mysql/var/mydb/

Count(*)

> select count(*) from container;
+-----------+
| count(*)  |
+-----------+
| 409036295 |
+-----------+
1 row in set (0.04 sec)

my.ini

[mysqld]
server-id = 1

port=3306
socket=/tmp/mysql.sock
skip_name_resolve

open-files-limit=64000

#Flush every 5 min (300 sec)
set-variable = flush_time=900

#Max Clients
set-variable = max_connections=5050
set-variable = max_user_connections=5000
set-variable = back_log=50

set-variable = table_open_cache=1024
set-variable = table_definition_cache=1024

#INSERT While SELECT-ing. Default is 1 (1 = On if have no hole, 2 = On if have hole)
set-variable = concurrent_insert=2

#Interactive timeout 60 min (from console)
set-variable = interactive_timeout=3600

#non-interactive timeout 3 hours
set-variable = wait_timeout=10800

set-variable = key_buffer_size=8192M
set-variable = max_allowed_packet=5M
set-variable = sort_buffer_size=256M

set-variable = tmp_table_size=512M
set-variable = max_heap_table_size=64M

#all updates will wait for selects
set-variable = low_priority_updates=1

#preforking
set-variable = thread_cache_size=64

#----- SLOW QUERIES -----

set-variable = long_query_time=2
set-variable = log_slow_queries=mysql-slow.log

#----- CASHE -----

# SELECT SQL_CACHE * from x
set-variable = query_cache_type=0
set-variable = query_cache_limit=1M
set-variable = query_cache_size=128M

Assuming you are SELECTing only by the PRIMARY KEY, then the following will speed it up:

NO PARTITIONing.  It only slows down such queries, especially because of 1024 partitions.
Switch to InnoDB.  The PRIMARY KEY is "clustered" with the data; this will save a disk hit on each row fetched.  Shrink key_buffer_size to 50M and raise innodb_buffer_pool_size to 70% of available RAM.  InnoDB also avoids table locks.
Compress, in the client, the JSON text; the datatype needs to be BLOB (as it mistakenly is now).

Note:  InnoDB will expand the disk space by a factor of 2-3; the compression will get that space back.  (Please test this with, say, a million rows.  Experiment with the different ROW_FORMATs; I don't know which one will work best for your situation.  And validate the SELECT speed and the lack of interference between reads and writes.)

Do you really have 5000 connections at the same time?  They are probably stumbling over each other and slowing down each other.

You have half turned off the Query cache; also do query_cache_size=0


EER Diagram - subclasses which are neither disjoint nor fully overlapping Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look something like:

In many cases you could request both services from the company, in which case we would use an "o" instead of a "d".

A person cannot request service B by itself. A person can request services as often as they want. But it's highly unlikely anyone would request services more than once every 10 years or so.

Suppose that for this company, you can either request service A and B together, or service A only. What is a good way to describe this constraint succinctly in the diagram?

Assumptions:

A service can be of either type A or B. Not both.
A request can be for either a service of type A or for two services (one of type A and one of type B).
A client can have many requests.

I think a clean option is to have a Service_Request entity which associates the entity named, say, Client and Service. It will have 2 attributes, service_a and service_b where the second is optional. In SQL:

CREATE TABLE service_request
( service_request_id INT NOT NULL
    PRIMARY KEY,
  client_id INT NOT NULL                    -- who ordered it
    REFERENCES client (client_id),
  request_ordered_at TIMESTAMP NOT NULL,    -- when it was ordered
  -- more details about the request
  -- (price, duration, etc.)

  service_a_id INT NOT NULL                 -- type A service
    REFERENCES service_a (service_id),      --   (mandatory)
  service_b_id INT NULL                     -- type B service
    REFERENCES service_b (service_id)       --   (optional)
) ;

The rest of the design, the entity Service and the two subtypes (Service_A and Service_B) should stay as they are in the question.


How to query tabular data that has thousands of columns I'm working in a module that at some point we will need to make queries to a tabular data that has ~25K columns and ~70K rows. Right now this data is persisted as a CSV file.

We've tried a few approaches to address it, but all of them failed or became too slow because of the number of columns we have. When we tried these approaches with a different file that has much more rows but not as many columns (~50M rows vs. 40~ columns), it work perfectly.

Having said that, I'd like to know if some of you has gone through this problem or has any idea on how to address it. There's no restrictions on technology or anything. I'm not listing what we've tried so far intentionally, in order not to bias your ideas. But if you'd like to know, I'll be happy to share our experiments with you.

25k columns? Hmm, that smells like some serious denormalisation. Do you have many sets of repeating, numbered columns, perhaps something like purchase_date_1, purchase_date_2, purchase_date_3 ,, purchase_date_99 or value_january, value_february .. value_december? Maybe there are recurring structured blocks such as residential_address, delivery_address, postal_address etc.

The way to handle these is to declare a separate table for each block and put each value in its own row. The above then becomes e.g.

TABLE Purchases
  CSV_row_key
  Purchase_date
  Ordinal      -- to capture the 1..99 if required; probably should be omitted

Transforming columns to rows is sometimes referred to as an unpivot.

Declare indexes on the keys and other predicate columns. Effective indexing is a separate subject for which we'd need the precise table design and the query.

It may be possible to pre-calculate aggregate values from these normalised tables. Running a batch job after loading and writing aggregates to a further set of tables is one approach. Materialized views is another.

1.75 billion values is large but not outrageous by current standards. Any of the usual RDBMSs on reasonable hardware should be able to handle it. Unfortunately the devil is often in the detail. It is possible to defeat the best software with contorted queries and convoluted schema. To avoid that we would need much more specific information.


Calculating percentage growth month by month I have a basic database for a retail store (4 tables) and am trying to run a query that will calculate the percentage growth (or decline) month by month in revenue for the store. The ORDERS table holds the OrderPrice info for each month.

I am struggling to find a query that will do this, does anyone know if one exists?

The DB is here: https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/0#&togetherjs=009rG6HwUe

I have come up with this so far to SUM the revenue totals month by month:

SELECT * FROM (
SELECT Coalesce(MONTHNAME(ORDERS.OrderDate), 'Grand Total') AS OrderDate,
SUM(ORDERS.OrderPrice) as SumOrderPrice
FROM ORDERS
WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY MONTH(ORDERS.OrderDate) with ROLLUP)
AS T;

Well, you're close:

I tweaked your statement, and came up with the following. I made one notable assumption - that you want the output sorted by month number, with the rollup value last. If you ultimately need to show a list that runs October of one year through September of the next, you'll need to put in a little more work.

SELECT Coalesce(OrderDate, 'Grand Total') AS OrderDate
      ,SumOrderPrice
FROM (
      SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate
            ,SUM(ORDERS.OrderPrice) as SumOrderPrice
            ,MONTH(ORDERS.OrderDate) as MonthNum
      FROM ORDERS
      WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
      GROUP BY
            MONTHNAME(ORDERS.OrderDate)
           ,MONTH(ORDERS.OrderDate) with ROLLUP
     ) AS T
ORDER BY COALESCE(MonthNum,13);

Here's the modified DB-Fiddle link, including my version of the query.

What I changed, and why:

First, the USE GUITARWORLD at the top of your DB-Fiddle made the set-up code not run. In the future, please ensure that what you've posted in a DB-Fiddle does run successfully - having that just flat-out fail is a bad start :-).
Your GROUP BY isn't grouping by a value in the SELECT list. That isn't always a mistake, but that meant your COALESCE(MONTHNAME()) didn't work. I changed the GROUP BY to GROUP BY the MONTHNAME() results alone, and moved the COALESCE to the outer query, to ensure that the results from that weren't causing problems with the grouping. Since I wasn't using SELECT *, I also needed to explicitly return SumOrderPrice.

Initially, the results listed April, then March, then the Grand Total. I was reasonably certain you'd want to see the months in numerical order, instead of alphabetical (or indeterminate) order, so I added MONTH(Orders.OrderDate) as a new column in the inner query; added it to the GROUP BY (no change to the results, as a given month number will always have the matching given name), and sorted the results by MonthNum (adding the COALESCE to force the Grand Total to show up last.

For the next step, we'll use a variable:

SET @Last_Month_SumOrderPrice := 0;

SELECT OrderDate, SumOrderPrice, Monthly_Pct_Change
FROM (
      SELECT OrderDate
            ,SumOrderPrice
            ,CASE WHEN @Last_Month_SumOrderPrice = 0
               THEN NULL
               ELSE (SumOrderPrice - @Last_Month_SumOrderPrice) * 100.00 / @Last_Month_SumOrderPrice
             END as Monthly_Pct_Change
            ,@Last_Month_SumOrderPrice := SumOrderPrice
            ,MonthNum
      FROM (
            SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate,
                   SUM(ORDERS.OrderPrice) as SumOrderPrice,
                   MONTH(ORDERS.OrderDate) as MonthNum
            FROM ORDERS
            WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
            GROUP BY MONTHNAME(ORDERS.OrderDate),
                     MONTH(ORDERS.OrderDate)
           ) AS T
      ORDER BY MonthNum) as SQ
ORDER BY MonthNum;

We take the basic query you already had, and add in a variable, @Last_Month_SumOrderPrice, to track the SumOrderPrice from the previous month as we go through the data. While the SELECT list data is being processed, the variable will retain its value from the last row.

This gives us access to the current and previous months' SumOrderPrice values. Calculating the percentage from there is trivial. I'm setting the value to NULL to avoid divide by zero errors.

We put another query outside the existing ones; otherwise, the value assigned to the variable would be output in every row.

Note that I've included an ORDER BY in the SQ sub-query, and in the outer query as well, both on MonthNum. The one in the sub-query guarantees the rows are processed in the correct order; the one in the outer query guarantees the final result is displayed in the correct order. It may be likely that the final result would be ordered correctly without the outer ORDER BY; however, the rule in SQL is order is only guaranteed when you specify an ORDER BY on your final result.

As a grand total would be misleading/irrelevant in this case, I've dropped that. If you have more data and want to see year-over-year percentage changes, I'd consider separate queries.

Here's the final DB-Fiddle link, showing both queries running.


How to spool output from SQL*PLUS to appear in single line with 1 space in between them? I have a table with 20 rows. I want to spool the rows into a text file where rows come side by side in one line and have one space in between to keep them separated. I intend to create a tar.gz file by keeping them in one line.

Sample data:

col1
------

file1
file2
file3

expected output:

tar cvzf tarfile.tgz file1 file2 file3

You can use the following query to get all rows in single row.

SELECT 'tar cvzf tarfile.tgz ' || LISTAGG(col1, ' ') WITHIN GROUP (ORDER BY col1)
FROM your_table_name;

Sample Data:

SQL> select * from tbl1;

COL1
--------------------
file 1
file 2
file 3

Output:

tar cvzf file 1 file 2 file 3

To spool-

SQL> set heading off
SQL> spool 'output.txt'
SQL> SELECT 'tar cvzf tarfile.tgz ' || LISTAGG(col1, ' ') WITHIN GROUP (ORDER BY col1)
     FROM your_table_name;
SQL> spool off

Here is the fiddle.


Tempdb ndf files on different drives So I inherited a server that has serious write latency issues according to a script provided by glen berry, I also noticed that the tempdb files are spread across multiple drives.

What could be the benefits of having Tempdb.ndf files on different drives? is there any? also the highest latency comes from the drives that host those tempdb files

When there are multiple data files in a database, all the writes to the database are striped across all files based on the proportion of free space that the file has to the total free space across all of the files. Now, each of the data files has its own set of allocation pages (called PFS, GAM, and SGAM pages) so as the writes move from file to file the page allocations occur from different allocation bitmap pages, spreading the work out across the files and reducing the contention on any individual page. This would be your benefit.

Again important factor is how many of them are there. As a general recommendation it should be based on logical processors you have on the server. So for 8 or less keep the number equal to processors. For more starting point should be 8 and then increase by one based on testing or contention issues.

Again for write latencies you might want to check with your storage team what's the threshold for the acceptance number in your environment. For faster temp db we in out environment keep them on SSDs and make sure log file is spiltted on different drive than data files.

Also as a side note those latencies number may or may not be a problem depending upon the nature of performance you are witnessing right now.

Plus I would suggest to check on trace flags 1117 and 1118 if you are on version of sql running 2014 or lower. From 2016 onwards it's enabled by default


Overloaded Constructor for SQL Server stored procedure? I'm making a stored procedure and I want it to accept a different number of parameters and have different behavior based on the number of parameters supplied. So let's say if I supply one parameter (for example a number) it returns the number. If I supply it with two numbers, I want it to add them together. If I supply it with a string, and a number I want it to concatenate the string with the number and display it--Similar to the behavior of overloaded constructors in Java.

Is there such a functionality with Stored Procedures in SQL Server, or am I better off just creating separate stored procedures. I only wanted to go this route to make my application structure simpler.

Thanks

<TL;DR>

Is overloading supported in SQL Server: Nope. But even if it was, you would likely have issues related to source control and/or debugging.
Can a subset of parameters be passed in, altering the behavior: Yep, but you will have a single piece of code that won't be easy to work with or maintain.

Conclusion: Just create one Stored Procedure or Function per behavior. It will be:

easier to work with via both T-SQL and app code
easier to maintain (hence less error-prone)
easier to debug
easier to integrate into source control
at least one thing you won't be blamed for after you leave or change teams / departments ;-)

There seems to be some confusion regarding how to interpret this Question with regards to the ultimate goal, and assumptions are being made on both sides, so let's start by clarifying what we are actually talking about.

Overloading

Overloading consists all, not some, of the following:

Code separation: Completely separate functions / methods.
Exact same function / method name for these separate pieces of code.
Unique number of, and/or combination of datatypes for, input parameters.
Execution of the shared name routes to the proper function / method based upon the signature -- the unique variation of input parameters.
Each separate function / method of the same name but differing input parameters can have a different datatype for the return value (note: this is merely an ability, not a requirement like the items noted above).

With this in mind, overloading is not possible (to any degree) in SQL Server, either for Stored Procedures or Functions (Scalar or TVF). Stored Procedures and Functions must have unique names (at least within a particular Schema). However, this restriction is just for user-defined objects as quite a few built-in Functions make use of overloading.

SQL Server does have "numbered" Stored Procedures (as pointed out in @Aaron's answer), but these should not be confused with overloads (not even partially) because they are missing everything that makes overloads different from those same, separate pieces of code having unique names:

While the "base" name of the Stored Procedure can be the same, this base name cannot be used to reference the code for execution; you are required to include the "number" (well, for ;1 it is optional), making it no different than having a vastly different name. Meaning, executing that name without any ;<number> following the name will always route to ProcName;1. Hence the "number" is actually part of the name.

In fact, the only time you can refer to the group without using the ;<number> syntax is in a DROP PROCEDURE statement, and then that is the only way to reference the group. Meaning, you cannot DROP PROCEDURE ProcName;3 but instead can only drop all of them.
The variations of input parameters are not required to be unique, as shown in this example:

CREATE PROCEDURE #NumberedProc (@Input INT)
AS
SELECT @Input AS [inp];
GO
CREATE PROCEDURE #NumberedProc;2 (@Input INT)
AS
SELECT @Input * 2 AS [h], 2 AS [g];
GO

EXEC #NumberedProc 55;

EXEC #NumberedProc;2 55;

Automatic routing to the appropriate module is not available, because it cannot be available due to the signatures being allowed to be the same. Hence the version number is required when executing.

True overloading is allowed in PostgreSQL for Functions, and it does work, but it also causes more pain than it is worth. It did, to a small degree, simplify the code from the app layer perspective, but it was very difficult to both manage in source control (assuming each object has a script named for the name of the object) as well as debug (you had to hunt down the particular signature from the error message). We eventually did away with the overloads and made every unique signature have a unique object name ;-).

Passing in a subset of parameters resulting in different behavior

You are allowed to have optional parameters that have default values (and the default can be NULL) for both Stored Procedures and Functions, but this is still a single Stored Procedure or Function. Meaning:

There is no separation of code; you will need a series of IF statements to properly route to the desired functionality. Assuming that the default values are set to NULL, you would be checking for which input parameters are NOT NULL.
Return types:

Scalar Functions can only have one return datatype. You could return SQL_VARIANT to avoid a specific return type, but there are issues with this approach: performance degradation and LOB types are not supported (i.e. VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML).
Table-Valued Functions can only have one return type, and it is determined at compile time.
Stored Procedures do allow for defining the result set structure at run-time, but that would be a very confusing interface to code to in the app layer.
Stored Procedures also allow for OUTPUT parameters, but you would need at least one per each datatype that you will be returning, and the calling code would need to know which one(s) to check based on which input parameters are being passed in. This would also be a confusing interface to code to.

Because this is a single object, you don't have the management and debugging issues like you would with true overloads, but you now have a large, messy, convoluted Stored Procedure or Function that is also hard to maintain, just for different reasons.

It would be something like:

CREATE PROCEDURE dbo.DoTooMuchInOneProcedure
(
  @Int1 INT = NULL,
  @Int2 INT = NULL,
  @String1 NVARCHAR(50) = NULL,
  @IntOut INT = NULL OUTPUT,
  @StringOut NVARCHAR(50) = NULL OUTPUT
)
AS
SET NOCOUNT ON;

IF (@Int1 IS NOT NULL AND @Int2 IS NULL AND @String1 IS NULL)
BEGIN
  SET @IntOut = @Int1;
  RETURN;
END;

IF (@Int1 IS NOT NULL AND @Int2 IS NOT NULL)
BEGIN
  SET @IntOut = @Int1 + @Int2;
  RETURN;
END;

IF (@Int1 IS NOT NULL AND @String1 IS NOT NULL)
BEGIN
  SET @StringOut = @String1 + CONVERT(NVARCHAR(50), @Int1);
  RETURN;
END;


SQL Transaction - use values before commit? So I am creating a stored procedure in SQL2014.  Towards the start it has:
Begin Transaction

Then  it does an insert into a table, which has an auto-incrementing primary key:
Insert into tNotice.

And then latter on in the stored proc, I need to write a query against the tNotice table to use to build up another insert statement into a different table.  My question is, will the newly inserted values be selected since this is going to be in a transaction, and the commit won't be until the end of the stored proc?

You can do a quick test to confirm this, I just ran a test out of curiousity, this is on sql server 2012. The current session will have visibility to a record that was previously inserted but not committed, however other sessions will not have visibility to that depending on their transaction isolation level.

Example

create table hs_test(
id int
)

begin transaction

insert into hs_test values (1)

select * from hs_test

--commit

I ran this in one ssms window, with the default isolation level of ReadCommitted. Without committing, the select will still see the value of 1 inserted.

Now if you open up another ssms window, and try to query that table, you won't see any records, because it hasn't been committed yet.


Handling variable fields in a CSV file with JSON? Im trying to figure out the engineering to get a CSV file into PostgreSQL.
Requirements:

The CSV has X number of predetermined required fields
The CSV file has Y number of dynamic fields

Example:

TenantCode,DomainCode,PersonCode,ExtraField1,ExtraField2,ExtraFieldN
"CUST1","STUD","C0001","Donald","Duck","M"
"CUST1","STUD","C0002","Diana","Duck","F"

into this format (in PostgreSQL 10)

TenantCode---DomainCode---PersonCode---ExtendedFields(JSONB)

CUST1---STUD---C0001---  {"ExtraField1":"Donald","ExtraField2":"Duck","ExtraFieldN":"M"}
CUST1---STUD---C0002---   {"ExtraField1":"Diana","ExtraField2":"Duck","ExtraFieldN":"F"}

My original thought would be to (in Python) use Pandas to convert the entire file to json, then use PostgreSQL to COPY into a staging table, then use PostgreSQL SQL to parse out the required fields and insert into the destination table.

I wouldn't do all that. I would just use python to transform the CSV file into another CSV where all the variable fields are a string of JSON.

Create the right table with the jsonb column and load direct into it. To do that use psql and \COPY. You can even create a program that reads from STDIN does the reformatting and outputs to STDOUT. And sits in the pipeline. Or you can write a quick perl script to get this done,

perl reformat.pl stupid1.csv stupid2.csv |
  psql -d test -e '\COPY mymastertable FROM stdin WITH ( format = csv )'

Or whatever.


Any issue in referencing a unique index from a foreign key table? Is there any concern in referencing a unique index from a foreign key table?

Using SQL Server 2008 R2

No, a FOREIGN KEY constraint can reference a column combination that is either the PRIMARY KEY or has a UNIQUE constraint or has a UNIQUE INDEX.

Copied from MSDN page, CREATE TABLE:

  FOREIGN KEY REFERENCES
  Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table. Foreign keys on computed columns must also be marked PERSISTED.

The only difference between the first (primary key) option and the other two is that unique* constraints and indexes can be defined on nullable columns. Primary key columns have to be NOT NULL.

* Note that there are some slight differences between these in SQL-Server (unique indexes offer some options that are not available with unique constraints): When should I use a unique constraint instead of a unique index?

For example, (as @gbn points), a unique index can be partial. But then, it can't be referenced by a FK.


Confusion about connection_id in SQL Server DMV I am confused about the connection_id column of sys.dm_exec_connections DMV:

SELECT connection_id FROM sys.dm_exec_connections;

If I connect to an instance of SQL Server using SSMS, should it be one physical connection only? And multiple New Query windows would represent different sessions represented by different session_ids or @@SPIDs?

In particular, why does the result set of the above query return an different uniqueidentifier for each session (and sessions that do seem to be currently open) of this same server connection?

Is this connection the same as the physical connection to an instance of SQL Server?

Thanks

If I connect to an instance of SQL Server using SSMS, should it be one physical connection only?

Well, I think it would help to first be clear about what you actually mean by "connect to an instance". I will assume that you are referring to starting up SSMS and it asking you to connect to an instance. When this happens, it is, at the very least, Object Explorer, that is making the connection. And when it first connects, it appears to use between 2 and 4 connections / sessions. After a while, it appears that all but one of those connections drops off. These connections are different threads of Object Explorer, gathering info to display. And the behavior of the connections sticking around for a while suggests that connection pooling is being used.

If you have the "Object Explorer Details" tab, then that can use another connection or two, especially if you are clicking on various objects to get per object details at the bottom.

Each connected query window will be yet another connection.

Open up a "properties" window (such as right-clicking on a database within Object Explorer and selecting "Properties" to open the "Database Properties" dialog) and that will be another 1 or 2 connections.

And if you have IntelliSense enabled (it is by default), then sometimes it will connect to gather info and then disconnect.

You can see these behaviors by doing the following test:

Close SSMS completely.
Open SSMS, but when prompted to "Connect to Server", hit the "Cancel" button.
Click the "New Query" button and now connect to the server.
Immediately paste in the following query and hit F5:

SELECT conn.[connection_id], sess.[program_name], txt.[text] AS [most_recent_batch],
       '---' AS '---',
       sess.*,
       '---' AS '---',
       conn.*
FROM   sys.dm_exec_sessions sess
LEFT JOIN sys.dm_exec_connections conn
       ON conn.[session_id] = sess.[session_id]
OUTER APPLY sys.dm_exec_sql_text(conn.[most_recent_sql_handle]) txt;

This query shows all sessions and their respective connections (if they have one). It also shows the most recent query batch to help see how that connection is being used.
Scroll to the bottom of the result list and you should see one row with the following value for "program_name":
Microsoft SQL Server Management Studio
(I believe it should actually be "Microsoft SQL Server Management Studio - Query", but this is how it is working in SSMS 17.0)
Now hit F5 again, scroll to the bottom of the result list, and you should see an additional row with the following value for "program_name":
Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
Don't wait too long between executions or else you might miss it.
Click on the "New Query" button, go back to the first query tab, hit F5 again, scroll to the bottom of the result list, and you should see an additional row with the following value for "program_name":
Microsoft SQL Server Management Studio - Query
Go back to the recently opened query tab, paste in the query shown above (in step # 4), close the original query tab, hit F5, scroll to the bottom of the result list, and you should see one row with the following value for "program_name":
Microsoft SQL Server Management Studio - Query
Go to the Object Explorer panel and connect to the instance.
Go back to the query tab, hit F5, scroll to the bottom of the result list, and you should see at least two rows with the following value for "program_name":
Microsoft SQL Server Management Studio
If you have the "Object Explorer Details" tab, then there should be another connection for that (i.e. at least three non-"- Query" connections).


writing sql procedure I wrote a sql procedure to get the name of the physician by entering the Eid in sql server.

create procedure doca(@eid int,@pname varchar  output)
as
begin
select @pname =p.name
from physician p
where p.eid =@eid
end

I used the procedure by following way

declare @pname varchar
exec doca 1,@pname output
print @pname

when I enter 1 I want to get the name of the physician whose eid is 1.

in this case it should be "John Dorian"
but this procedure prints just "J"

whats wrong with my code?

Declare the @pname as varchar(x)  where x is the length of the Physician.Name column.  Make the change in both pieces of code.

Declaring the variable as just varchar indicates you only want a single character at most.


SQL Server 2012 is database name mandatory for logon? is the database name mandatory SQL Server 2012 logon - like SERVERNAME\DBNAME ?
On 2005 Server the servername for logon with Windows credentional has been sufficient.

Thanks in advance,
 Tom

I think you are confusing database name and instance name. SQL Server can be installed as a default instance (in which case connecting to SERVERNAME is sufficient), or as a named instance (in which case you need to use SERVERNAME\INSTANCENAME or in some cases SERVERNAME,PORT_NUMBER). You would never connect to SQL Server using SERVERNAME\DBNAME, but you may wish to explicitly set the database name in the connection string (or in the Options >> section of the connection dialog) anyway, so that your user does not execute things in the wrong database by mistake (I suspect this is how a lot of user objects end up in master, model, tempdb, etc).

In SQL Server 2012, database name is only mandatory in the connection string when you are connecting to a partially contained database with a contained database user. For example:

EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
CREATE DATABASE contained;
GO
ALTER DATABASE contained SET CONTAINMENT = PARTIAL;
GO
USE contained;
GO
CREATE USER contained WITH PASSWORD = 'contained';
GO

If I try to connect to the instance with those credentials, but without specifying a database name explicitly under Options >>:

I get the following error:

  Cannot connect to SHELDON\SQL2014CTPn.
  Login failed for user 'contained'. (Microsoft SQL Server, Error: 18456)

In the error log:

  Error: 18456, Severity: 14, State: 5.
  Login failed for user 'contained'. Reason: Could not find a matching login matching the name provided.

I get the same error message if I use the <Browse server...> option; I need to type the database manually:

When I do, I can connect. Of course the Object Explorer tree is much less useful when I connect as a contained database user; by design, I only have access to that database:

Contained databases came about to help make migration of databases to different servers easier. There is no way to just say "well, I'm a contained database user, but why don't you tell me which database I'm allowed to connect to?" You need to be explicit, especially because different contained databases could have different users with the same username (and even the same password); nothing in the system prevents this (because it would make migration more difficult).

If you are not using contained databases (and, specifically, contained database users), then nothing is different from previous versions.


presentation of non-ascii characters (ñ) I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example.

What kind of setup do I need to perform at the database, in order to deliver non-ascci to a Python application?

To give you more detail. Until now, I changed all the character_set_* and collation_* variables to UTF8 . But is still not working.. Before these modifications, the charset and collation were configured to latin-1.

MySQL

`mysql> SHOW VARIABLES LIKE "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM tjs_stage.avisos WHERE  Collation="utf8_unicode_ci"\G;
*************************** 1. row ***************************
Field: Titulo
Type: varchar(32)
Collation: utf8_unicode_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: Aviso
Type: varchar(256)
Collation: utf8_unicode_ci
Null: NO
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
2 rows in set (0.00 sec)

mysql> SELECT * FROM tjs_stage.avisos \G;
*************************** 1. row ***************************
Id: 1
Titulo: PRIMERO AVISO
Aviso: MAÑANA EMPIEZAM VACACIONES
Creacion: 2016-04-06 18:12:52
Modificacion: 2016-04-06 18:12:52
*************************** 2. row ***************************
Id: 49
Titulo: Aviso de Vacaciones
Aviso: Mañana empezam las vacaciones!
Creacion: 2016-05-26 21:46:34
Modificacion: 2016-05-26 21:46:34
2 rows in set (0.00 sec)`

Python

Here is the code which I developed just for test purposes:

`#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
import sys

try:
    con = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "drootmysql", charset='utf8')
    print "\nMySQL connection: OK\n"

except:
    print "\nConnection failed...\n"
    sys.exit()

cur = con.cursor()

insert_query = "INSERT INTO tjs_stage.avisos (Titulo,Aviso) VALUES('Aviso de Vacaciones','Mañana empezam las vacaciones!')"
cur.execute(insert_query)
con.commit()

select_query = "SELECT * FROM tjs_stage.avisos"
cur.execute(select_query)
con.commit()

records = cur.fetchall()

for row in records:
    print row`

Here's the result:
    `$ python mysql_json_data.py

MySQL connection: OK

(1L, 'Aviso de Vacaciones', 'Ma\xc3\xb1ana empezam las vacaciones!', datetime.datetime(2016, 5, 26, 16, 38, 40), datetime.datetime(2016, 5, 26, 16, 38, 40))  `

I just want to retrieve the data as it is from the database.
Is there any other setup that should perform in MySQL in order to server the non-ascii characters as it is ?

Thanks in advance guys!

Is mysql_json_data.py input or output or code or a dump?

'MA\xd1ANA... has Ñ encoded in latin1 (and perhaps escaped for display?)
'Ma\xc3\xb1ana... has ñ encoded in utf8 (and perhaps escaped for display?)

If you have one file with both in it, you have a mess.

The former will probably be truncated as you attempt to put it into your MySQL table.  The latter will probably insert correctly.

See my notes on Python.


mysqldump: Does --lock-tables=false work with MyISAM tables? So, I have to check and come with a backup solution for a very busy mysql server (with tables over 5 GB). Due to resources constraints, they choose to set all tables with the MyISAM engine. Currently, they use a bash script with calls to mysqldump for every single table, and they use the command --lock-tables=false, because it is imperative for them to be always online, but I really think that's not working the way they intended.

My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?

Also, the only non-locking solution I can think of is to make a slave replication to properly lock the tables and use mysqldump or XtraBackup. My concern with this is getting a hit on performance, since it'll be writing twice and both instances will have to run on the same machine. Is there any other alternative for a non locking solution for MyISAM ?

Using lock-tables=false becomes irrelevant given MyISAM's table locking approach to DML.
You asked

My guessing is all their backups are inconsistent, since they don't lock all the tables, but one by one with every dump. Am I correct?

Yes, you are correct. Furthermore, when it comes to XtraBackup and MySQL Enterprise Backup, there must be a FLUSH TABLES WITH READ LOCK if MyISAM is present.
Oracle and I recently had dialogue. They asked me to change everything to InnoDB in order to use Enterprise Backup. When I explained I had 40GB and 2TB MyISAM (most of the tables had hundreds of partitions), they understood. I still that after all these years, you must still take matters into your own hands for point-in-time consistency with MyISAM.
SUGGESTION
Your best solution would be to use MySQL Replication and run the backups from the Slave
I have suggested this before

Oct 01, 2011 : Quick MySQL Backup
Dec 05, 2011 : Set up a MySQL slave using mysqldump to get the initial data

You may want to use binlog_format=ROW on Master and Slave to catch granular changes before running STOP SLAVE; FLUSH TABLES; on the Slave. Then, you can mysqldump from the Slave without worrying about any Storage Engine locking or consistency issues.
Give it a Try !!!