MySQL with Glusterfs Storage vs MySQL replication with hostPath storage I am trying to search right road in a project.

We have currently three nodes openshift cluster(okd 3.11) and deployed MySQL with Glusterfs storage. its working fine but look like over engineered to me.

I am thinking to replace above with MySQL Replication with hostpath storage so 1 node be master and other node will act as slave. this way no need for extra steps to deploy glusterfs storage.

We may not extend this cluster it will be three and if we may have to deploy two node OKD cluster MySql replications will be ideal choice as Glusterfs dont recommend to use less then 3 nodes.

Main goal is HA so graceful failover can be achieved. Any thoughts?

The minimum for an HA setup is 3 nodes, geographically distributed.

If you put all the nodes in a single data center, you are vulnerable to a datacenter crash.  (They do happen.)
If you use Master + Slave(s), failover is not well automated.  See MHA as a possible alternative.
With Galera cluster, you can write to all nodes, thereby helping minimize downtime.  (I don't know about Glusterfs.)


Is it possible to detach some specific database from MongoDB Server? I have to keep several copies of production databases serving by a dockerized MongoDB server.

I do NOT want to backup, then to drop, then, when I need it again, to restore it. It is not as quick process as I would want it to be.

But when I keep those DBs online, MongoDB server eats a lot of my host system memory (16Gb total physical RAM).

I would like to detach some specific databases in order to free some memory and attach them again when I need them.

I tried to search the answer with google, but without success.

MongoDB 4.0, LinuxMint 19.3 Tricia

I tried to search the answer with google, but without success.

I'm pretty sure MongoDB does not offer "offlining" or "detaching" databases like traditional RDBMS products do, at this time (hence you not being able to find anything on it).

Have you set storage.wiredTiger.engineConfig.cacheSizeGB
 and hostInfo.system.memLimitMB to limit the RAM being used by MongoDB? If not set it will default to 50% of (RAM - 1 GB), which might be your actual problem.

AFAIK Mongo shouldn't be loading unused database data into RAM, and the RAM to keep databases online should be negligible.


Optimization for aggregation and grouping with MySQL I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc..

I'm having some issues avoiding Using temporary and Using filesort with some of the queries being run on a database.

Now, I know that sometimes this is unavoidable. Should I even bother changing it in this situation?

The application caches the requests that run the most of these queries, so they won't be run often at all. However for the sake of best practices and learning I want to make sure that I'm running things optimally.

Here's what's causing it. There's an assortment of tables containing various types of data. Assume a table structure where most of the columns are data that can be aggregated (SUM, AVG, etc) and any other columns are either ignored or grouped. One column consistent across the report data tables is a DATE column. As the data is pulled daily from APIs, the data in each table is being stored for said date.

The majority of the front end components are currently displaying data to the end users at a monthly detail level (daily is needed though for upcoming features -- this level of detail cannot be compromised).

I'm currently using MySQL to group and aggregate data.

Here's a basic illustration:

SELECT
    DATE_FORMAT(c.date, '%Y-%m') as date,
    SUM(c.conversions) as conversions
FROM data_ga_conversions c
WHERE c.goal_id = 1 AND c.date BETWEEN '2014-10-01' AND '2015-02-28'
GROUP BY YEAR(c.date), MONTH(c.date)

Note the GROUP BY clause using date functions. As I'm sure you know -- this is what's causing the query executor to use temporary and filesort. (Note: I'm aware that I can get rid of filesort by adding ORDER BY NULL, however I'm trying to keep these basic sorting and aggregation calculations in one place, application code or SQL.)

Am I approaching this the wrong way? Here's a couple of alternate methods I had considered:

Storing year, month and day in separate columns.
Aggregate tables (à la materialized views... switching to postgresql is a potential option too).
Let the application do it

Thanks!

Using temporary and filesort are not as evil as everyone makes them out to be.

Do not split a DATE into year, month, and day columns; it will cause more pain than it is worth.

I prefer (in your specific case) to do GROUP BY LEFT(c.date, 7), although it may not speed up anything.

"Summary Tables" is the way to go for that type of query, assuming you have large tables.  More discussion in my blog.  You would add one (maybe more) row to the summary table each night, then a query against that table could summarize any day range, including your month.

Do not store AVG(...) in a Summary table; it is probably more 'correct' to store SUM(foo) AS sum_foo and COUNT(*) AS foo_ct, then have the report

SUM(sum_foo) / SUM(foo_ct) AS 'Average foo'

Probably the optimal summary table would have

PRIMARY KEY(goal_id, date)

and have sum_conversions calculated from SUM(c.conversions), plus anything else relevant.  Often, one summary table can handle a small set of standard 'reports'.


How to see query executed for a specific user from V$SQL I am trying to have the queries used for a specific user at the end of the day. But it seems the queries in this view  V$SQL are deleted every short time. I am not sure how much time. Also i dont know why they are deleted if i still see some records with older LAST_ACTIVE_TIME is there a way to save them before they are deleted. For that when are they deleted?or other suggestion. Hope you can help me.

Thank you!

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is DBA_HIST_SQLTEXT. Keep in mind that for every dynamic (V$) view, there is a corresponding dba_hist table... But since dba_hist views are often maintained by AWR, you might need to be licensed for diagnostic/ tuning pack for complete reports.


You don't need subqueries. You only need to use both columns in the ON condition of the FULL JOIN:

SELECT
    -- t1.*,     -- adjust, keep only
    -- t2.*,     -- needed columns
    COALESCE(t1.date, t2.date)
                  AS date,
    COALESCE(t1.identifier, t2.identifier)
                  AS identifier,
    t1.attribute1 AS a_attribute1,
    t1.attribute2 AS a_attribute2,
    t2.attribute1 AS b_attribute1,
    t2.attribute2 AS b_attribute2
FROM table1 AS t1
     FULL JOIN table2 AS t2
     ON  t1. date = t2. date
     AND t1. identifier= t2. identifier
-- WHERE
--     (t1.date = '2000-01-01' OR t2.date = '2000-01-01')
 ;

If we want date and identifier combined in one column each, we can use COALESCE() function.


Need help to compare pricing using SQL queries Thank you for help in advance.

I have 2 sets of data that show bus journeys and there relevant price from 2 different systems that i need to compare.

Table1
-Brand
-Origin
-Destination
-Price

Table2
-Brand
-Origin
-Destination
-Price

So what i need todo is to match all the following fields and return the price from table2 which i have done using the following:

SELECT Table1.Brand ,
       Table1.Origin,
       Table1.Destination,
       Table1.Price,
       Table2.Price
FROM Table1
  LEFT JOIN Table2
         ON Table1.Brand = Table2.Brand
        AND Table1.Origin = Table2.Origin
        AND Table1.Destination = Table2.Destination

ResultTable:

Brand  |  Origin  |  Destination  |  Price  |  Table2 Price  |

So up to now I have the correct data however i need to the following which i could really do with some help:

Compare Table1.Price against Table2.Price and display the difference in the ResultTable above
Filter out all prices that are the same so all we have left is the different prices
A new query where we compare the Brand, Origin and Destination and if any are missing from table 2 we display those

Sorry for the long winded explanation but just thought i would try to make it as explicit as possible.

Disclaimer, everything is nullable in your tables so IS NULL predicates may give false positives.

Your query comes a long way, all you need is to add certain predicates:

1 and 2.

SELECT Table1.Brand ,
       Table1.Origin,
       Table1.Destination,
       Table1.Price,
       Table2.Price,
       Table1.Price - Table2.Price as diff
FROM Table1
  LEFT JOIN Table2
         ON Table1.Brand = Table2.Brand
        AND Table1.Origin = Table2.Origin
        AND Table1.Destination = Table2.Destination
WHERE Table1.Price - Table2.Price <> 0;

3 .

SELECT Table1.Brand ,
       Table1.Origin,
       Table1.Destination,
       Table1.Price,
       Table2.Price,
       Table1.Price - Table2.Price as diff
FROM Table1
  LEFT JOIN Table2
         ON Table1.Brand = Table2.Brand
        AND Table1.Origin = Table2.Origin
        AND Table1.Destination = Table2.Destination
WHERE Table2.Price IS NULL;


INSERT data on MySQL to specific position based on its value (similar to ORDER BY) I have a MySQL table with 3 columns for simplicity sake:

Datename     |Date    |Name
-------------+--------+-----
30/06/15test |30/06/15|test
30/06/15other|30/06/15|other

Note: Column Datename is unique

I would like all new data to be inserted into the table based on its value.

Example:

If the Date is 29/06/15, it would be inserted at bottom of table.
If the Date is 01/07/15, it would be inserted at the top of the table

Or preferably sorted by Datename column so it will sort by date then name.

Is this easily achievable, or do I need to just create a page that SELECT and ORDER BY?

A table is, by definition, an unordered bag of rows. There is no guarantee that if you say SELECT * FROM table you will get the rows back in the same order you inserted. Think about throwing a bunch of popsicle sticks on the ground while blindfolded; now take off the blindfold and tell me which one hit the floor last.

No ORDER BY is essentially telling the database you don't care about order.

If you do care about order, you need two things:

At least one column that can dictate order (an auto increment column, a date/time column populated with now(), or something you manually specify).
An actual ORDER BY expression on the outermost part of the query that presents the data (and don't forget to include a tie-breaker, if the first column might not be unique).


Aggregating statistics from child rows I have a parent row and then many child rows, and I must aggregate statistics from these child rows.

For a more concrete example let's imagine I have one round of golf (parent row) and then child rows with the score of each hole. I need to aggregate statistics on these rows, like number of birdies, pars, bogies, putts and so forth. And then put these numbers in a round_detail table.

What would be the best solution to this?

I'm using postgres if it matters, but this is probably just a general sql propblem.

EDIT: As requested http://sqlfiddle.com/#!1/6549c

The concept you consider is known as materialized view. Since you comment:

  rounds are rarely edited afterwards

.. I think this should be good idea for your case. Some RDBMS' have distinct objects for that. In Postgres it's just a matter of writing the results from a view or SRF (set returning function) to a table. You can find a basic code example for refreshing MVs in the manual here.

Refer to this related answer on SO for the more sophisticated regime I use for that purpose.

Your query to (re-)create a materialized view could look something like this:

SELECT c.name AS course
      ,r.user_id
      ,s.round_id
      ,COUNT(NULLIF(s.strokes = h.par, FALSE))     AS pars
      ,COUNT(NULLIF(s.strokes = h.par - 1, FALSE)) AS birdies
      -- more columns ..
FROM   score  s
JOIN   round  r ON r.id = s.round_id
JOIN   course c ON c.id = r.course_id
JOIN   hole   h ON h.id = s.hole_id
GROUP  BY 1,2,3

I simplified your test case and added the query: ->sqlfiddle.


Query does nothing except context switches I run Adam Mechanic's WhoIsActive every so often on my server and log results to a table.  I noticed a peculiar case while looking at some of the results and would like help interpreting what is happening.

There was a query that had been executing for about 30 seconds, but it seems it was not doing any work.  There were no wait stats, no reads, no writes, no CPU usage, but there were several thousand context switches.

   wait_info  CPU    tempdb_allocations tempdb_current  blocking_session_id  reads  writes context_switches physical_io physical_reads  query_plan  used_memory
   NULL       0      0                  0               NULL                 0      0      8,404            0           0               NULL        4

The query responsible is a pretty simple Select ... Into a temp table. And unfortunately I cannot reproduce the scenario, because if I run the query now, it finishes in under a second.  But but my curiosity is killing me and I'd like to understand what could have been happening during the 30 seconds when it seems to have been doing nothing.

I'd like to understand what could have been happening during the 30 seconds when it seems to have been doing nothing.

If a query isn't using CPU, it's in a wait, which are tracked as the system-level in sys.dm_os_wait_stats.

SQL Server 2016 introduces a DMV for session-level wait stats with sys.dm_exec_session_wait_stats.

SQL Server 2017 introduces wait tracking at the query level in Query Store with sys.query_store_wait_stats.

Earlier versions can use Extended Events to see the waits for a particular session.  See eg: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/20/troubleshooting-slow-running-query-using-extended-events-wait-info-event/


Given the same input, is the HASHBYTES function guaranteed to return the same output? If I use the SQL SERVER HASHBYTES() function in a script on my machine, is it guaranteed to produce the same output if run on another machine/instance?

For example, will

SELECT  HASHBYTES('SHA1', 'Hello World')

generate the same output regardless of SQL version, processor architecture etc?

and will

SELECT  HASHBYTES('SHA1', column1) from table1

generate the same output if the column definition is slightly different - varchar(50) vs varchar(100) - for example?

Is there anything that needs to be considered here?

In terms of different instances, the HASHBYTES should return the same values if the same script is run, regardless of the server version, processor or architecture:

I've just run the following on both SQL Server 2005 and SQL Server 2012, on completely different hardware configuration. Both have produced the output 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(100)));

However, if the collation is different, the output will be different.

In terms of column definition, the output should be the same regardless of the length of the field.

Taking your example of VARCHAR(50) and VARCHAR(100), we can test the output like so:

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(50)));
SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(100)));

Both SELECT statements return the same value:

0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

However, be aware that VARCHAR and NVARCHAR will not produce the same HASHBYTES value, even with the same string:

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(50))); -- Returns 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA
SELECT  HASHBYTES('SHA1', CAST('Test' AS NVARCHAR(100))); -- Returns 0x9AB696A37604D665DC97134DBEE44CFE70451B1A


Error parsing DDL for schema.table in mysql workbench I have production dbs using Mysql 5.5.31-0ubuntu0.12.04-1 (I know its old, will be upgraded soon).

I used a too advanced version of mysql workbench which allowed me to add a comment to key. (Now reverted to WB 6.3 which should be compatible with mysql 5.5)

Now when I 'Alter table' on one of the tables I get Error parsing DDL.
When I click it shows the error is in the comment of the key (see below)

 KEY `for cron (product ready)` (`a`,`b`),
  KEY `subproducts` (`a`,`b`,`c`) COMMENT 'find products by the subproducts joiner'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The question is how can I remove the comment of the key without mysql rebuilding the table and without access to the mysql workbench alter table UI.
This is an important production server and I would like to avoid alter table which will scan all the table.
Thanks

There were no "fast" index ALTERs in 5.5.  Check out Percona's pt-online-schema-change to drop and re-add the index (without the comment).  It should work with only a small downtime.

A newer product is gh-ost, but that depends on the binlogs, and may not work for such an old version.


SQL Server Trace Stopped Messages Displaying Random Login Name When traces are started and stopped we get messages similar to the following in the SQL Server error log:

  SQL Trace ID 2 was started by login "DOMAIN\loginA".

and

  SQL Trace stopped. Trace ID = '2'. Login Name = 'loginB'.

The problem I've noticed is that the loginB name doesn't always correspond to the loginA name that started the trace.

This has become a problem since we have started using Extended Events sessions more in our environments.  We schedule traces to start and stop as the service account but we cannot properly filter these messages out of our error reporting systems because loginB randomly changes to some other user.  We also can't reliably filter on trace ID because it could change.

My questions are:

Is this a known bug or is it expected behavior?  I have only found one article from someone posting evidence of this issue in their environment (http://www.360sqldba.com/2015/08/sql-error-log-shows-random-user-and.html).  I haven't found anything official from Microsoft.
Is there a way to reliably set the trace ID so that I can filter by trace ID rather than by login name?

I am seeing this happen in our SQL Server 2012 and SQL Server 2016 environments.

Found an answer here: http://www.360sqldba.com/2015/08/sql-error-log-shows-random-user-and.html

Conclusion: SQL error log message about which user and SPID stopped a timed trace is misleading. It appears that SQL uses a random active session to stop timed trace. It may be something that MS SQL team overlooked.


What happens when an auto grow event is triggered to the database? I want to know what happens when an autogrow event is triggered...
Does it hold up all the database processing including reads and writes during that time?

For instance, our database encounters for up to 10 autogrowth avg per day.

Also, what are the cons for not using autogrow and rather pre sizing the database to use the maximum disk space?

Yes, autogrow is expensive, and yes, many transactions will need to wait for it to complete. Search this site and you will see many experts advising to avoid it and many ways to minimize the impact.

The only cons for not using autogrow and instead pre-sizing the file to fill the disk:

you can't lease out that space in the meantime (if you know the database will grow, why are you waiting to fill the space?)
if you have to grow again, you'll be stuck (but you'd be the same stuck as if you grew incrementally, you just wouldn't get any warning)

I would suggest pre-sizing your files in such a way that there is room for one or two autogrow events before they fill the disk (or exceed quota or what have you). This way you can set up alerting on those events and potentially react in time to prevent downtime. Users shouldn't be waiting for autogrow events unless you are responding to an emergency (and you should obviously avoid those).


Best solution for PostgreSQL partial table replication We have the following architecture of our system (PostgreSQL 9.6 is used on all servers):
1. there is a bunch of little servers ("sensors") storing data in some table
2. there is a big server ("management console") that collects data updates from all those servers and stores them in the table with the same name and structure
3. sensors can create new rows and update existing ones, the changes must be replicated to the console
4. console can update or delete rows that are relevant for particular sensors, these changes must be replicated to those servers
5. sensors must store only those rows that belong to them (there is a field "source" in the table storing the sensor's id)

My questions are:
1. This is a case of multimaster replication, right?
2. What replication technology would be most suitable for this use case, considering that we don't need autofailovers and sensors must store only a part of data, not the whole table that the console has?

Our company works with a technology called symmetric-ds to replicate our tables, from server to client, and from client to server. Take a look at the site.


Postgresql query XMLTable with default namespace I use PostgreSQL 12 to query XML data.

My data is as below

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS xmlns="http://x.y.z">
  <ROW id="1">
    <COUNTRY_ID>AU</COUNTRY_ID>
    <COUNTRY_NAME>Australia</COUNTRY_NAME>
  </ROW>
  <ROW id="5">
    <COUNTRY_ID>JP</COUNTRY_ID>
    <COUNTRY_NAME>Japan</COUNTRY_NAME>
    <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
    <SIZE unit="sq_mi">145935</SIZE>
  </ROW>
  <ROW id="6">
    <COUNTRY_ID>SG</COUNTRY_ID>
    <COUNTRY_NAME>Singapore</COUNTRY_NAME>
    <SIZE unit="sq_km">697</SIZE>
  </ROW>
</ROWS>
$$ AS data;

When query, my SQL is as below:

SELECT xmltable.*
  FROM xmldata,
       XMLTABLE(namespaces(default 'http://x.y.z'), '//ROWS/ROW'
                PASSING data
                COLUMNS id int PATH '@id',
                        ordinality FOR ORDINALITY,
                        "COUNTRY_NAME" text,
                        country_id text PATH 'COUNTRY_ID',
                        size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
                        size_other text PATH
                             'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
                        premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;

It happened this error:

DEFAULT namespace is not supported

Please instruct how to fix it ?
Many thanks.

As documented in the manual "A default namespace specification is not currently supported"

You need to give the namespace an alias and use that in the XPath expressions:

SELECT xmltable.*
FROM xmldata,
     XMLTABLE(
        xmlnamespaces('http://x.y.z' as x),
        '//x:ROWS/x:ROW'
        PASSING data
        COLUMNS
          id int PATH '@id',
          ordinality FOR ORDINALITY,
          "x:COUNTRY_NAME" text,
          country_id text PATH 'x:COUNTRY_ID',
          size_sq_km float PATH 'x:SIZE[@unit = "sq_km"]',
          size_other text PATH
                     'concat(x:SIZE[@unit!="sq_km"], " ", x:SIZE[@unit!="sq_km"]/@unit)',
          premier_name text PATH 'x:PREMIER_NAME' DEFAULT 'not specified'
     ) ;

Online example


Create an AD user from a SQL Server trigger Running SQL Server 2008 (10.0.5520) - I have a trigger that is executed when a new record is added to the security table. I would like to extend this trigger, and create an entry in the AD system with a couple of the parameters from the insert.

Question is: can this be accomplished? If so, how?

It looks like a command line tool does exist:

Technet reference

To create a contact named Jeff Hay in a top-level OU named Service Dept in the fabrikam.com domain, at a command prompt, type the following command, and then press ENTER:

dsadd contact "cn=Jeff Hay,ou=service dept,dc=fabrikam,dc=com"

Can this command line be executed from the trigger?

What @Mike_Fal said. This is a huge gaping security hole. Not only does SQL need xp_cmdshell, but it will need Domain Admin rights. I also agree with the other sentiments around not using a trigger.

You can still tie it in with your existing process and make it database driven with the security table you are using. I'd probably write a separate, asynchronous process to read from the security table and add the AD users that way. This is a great job for PowerShell; connecting multiple systems and technologies.


What does standard SQL or any of the non-PostgreSQL SQLs do instead of RETURNING? Common code for me:

if ($new_id = dbcall('INSERT INTO table (column) VALUES ($1) RETURNING id', 'meow'))

$new_id then contains the new id for the freshly inserted record.

According to https://www.postgresql.org/docs/current/sql-insert.html :

  INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension

That makes me feel bad about using RETURNING, and it also makes me wonder what standard SQL and non-PG SQL databases do to let the user "grab" the id in a safe manner.

I remember doing things like:

SELECT lastval();

directly after the INSERT, but that could easily turn into a "race condition" and thus give the wrong id! That's why I started doing RETURNING, after having it recommended to me by PG guys, but since it's nonstandard, I don't feel good about using it.

On the other hand, I have given up on ever writing "standard SQL" because such a thing doesn't seem to exist outside of the meeting room at some organization that charges money for this massive SQL standard which they somehow make money from even though nobody follows it.

The Standard SQL Way™ of generating IDs is to

create a sequence for the table;
read the next value with SELECT NEXT VALUE FOR seq_name¹; and then
insert a complete row, including the ID value.

Before SQL:2003, there were no autogenerated IDs. And in SQL:2003, there appears to be no method to read the last GENERATED AS IDENTITY value.

¹ Postgres does not support this, and requires SELECT nextval('seq_name').


Optimize query with a subquery (index is not used) I have following query:

SELECT
`status`, `is_client_coordinator`, id, first_name, last_name
FROM `users` `t`
WHERE t.status!="CLOSED"
and t.is_client_coordinator
or t.id in (select client_coordinator_id from projects where status not in ("DELETED", "ARCHIVED"))
ORDER BY concat(first_name, last_name)

The explain gives this:

Individually queries take less than 0.1 seconds (first one 0.04 seconds and the subquery 0.07 seconds). Put together it takes more than a second to perform the query.
Both status columns are the type of ENUM.
What am I doing wrong here? Any advice about how to optimize the query?

In order for the in clause to be evaluated, the subquery is likely being evaluated for every row. My first performance suggestion would be to use a left join and then filter:

select t.status, t.is_client_coordinator, t.id, first_name, t.last_name
from users t
left join projects p on t.client_coordinator_id = pl.id
where t.status != "CLOSED"
and t.is_client_coordinator
or (
exists(p.client_coordinator_id)
and p.status != "DELETED"
and p.status != "ARCHIVED"
)
ORDER BY concat(t.first_name, t.last_name)


ORA-00338: log # of thread # is more recent than control file We are facing the following error on our production database:

  ORA-00338: log # of thread # is more recent than control file

The database crashed and there was no recovery action. What could I do to resolve the problem?

It could be that you are opening using the wrong control file[s]. This is easily caused when using for example a parameter file in ASM - the correct one - and one in the default location $ORACLE_HOME/dbs/ that still has the old references in place after a migration to ASM. In that case search in ASM to the current control files.

In such a setup, normally the parameter is found by using srvctl config database -d YOUR_DB

During every opening attempt the log# is increased .... so after many attempts, the error can go away and the problem can still exist ....


View and Synonym hirarchy oracle The Dba of our company has created views for the tables for all the users.
We have 230 users, he has created 230 views for each tables!
I want to replace this by public synonym and grant select to the users.
Example:
Create public synonym emp to scott.emp
grant select on scott.emp to public;
When I query synony after droping view it works.
Without dropping the view If I query emp oracle does the select on the view.
When we have a view and synony in the same name, what is the hirarchy between this?
Is there a workaround for this problem.
Because when I modify a table I have to correct 230 view scripts!

The resolution order is described in the documentation:

  Oracle Database attempts to qualify the first piece of the name
  referenced in the SQL statement. For example, in scott.emp, scott is
  the first piece. If there is only one piece, the one piece is
  considered the first piece.

  A) In the current schema, the database searches for an object whose name
  matches the first piece of the object name. If it does not find such
  an object, it continues with step b.

  B) The database searches for a public synonym that matches the first
  piece of the name. If it does not find one, it continues with step c.

  C) The database searches for a schema whose name matches the first piece
  of the object name. If it finds one, it returns to step b, now using
  the second piece of the name as the object to find in the qualified
  schema. If the second piece does not correspond to an object in the
  previously qualified schema or there is not a second piece, the
  database returns an error.

  If no schema is found in step c, the object cannot be qualified and
  the database returns an error.


Missing DatabaseMail.exe.config after applying SQL Server 2016 SP2 CU1 After applying SQL Server 2016 SP2 CU1 (SP2 was already applied) to SQL Server 2016 on several different Windows Server 2016 installations, we noticed that Database Mail stopped working. Mails were being queued but not sent and we could find no error messages on the servers, neither in the SQL Server logs nor in the Windows Server event logs.

After verifying that none of the settings for Database Mail had changed and that everything was as it should be with regards to mail relaying server and other external factors, we could see that an old problem seemed to have resurfaced:

The DatabaseMail.exe.config configuration file for DatabaseMail.exe, allowing it to work without having .NET Framework 3.5 installed, had been removed by the SQL Server 2016 SP2 CU1 update.

On all instances where the patch had been applied, the config file above was missing from the binaries directory for the SQL Server installation. It was present before the patch was applied.

Example configuration file path:
C:\Program Files\Microsoft SQL Server\MSSQL13.instance_name\MSSQL\Binn\DatabaseMail.exe.config

The main point of the question was to check if other people had the same issue and to help spread the information on issues with the SQL Server 2016 SP2 CU1 patch. Since these objectives have been reached and there is a solution presented in the post, I will close this as answered.

The solution was as follows:

We recreated the file based on the following KB article:
https://support.microsoft.com/en-us/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe

After that we had to restart the Database Mail queues to start processing queued mails:

USE msdb;
EXEC sysmail_stop_sp;
EXEC sysmail_start_sp;

This cleared the issue and any queued messages were successfully sent.

Patch link: https://support.microsoft.com/en-us/help/4135048/cumulative-update-1-for-sql-server-2016-sp2


Full text search over multiple CPU processes? Is it possible to configure Postgres to split out a full text search over multiple CPU processes in an attempt to complete quicker?

I'm running a full text search on 2 million records on a GIN indexed tsvector column, where the source text is about 10,000 characters long.

I have way more CPU than is being used during the search, so I feel like splitting the search over 4 processes in batches of 500k would allow it to run the search concurrently and therefore complete faster.

I'd be interested to know if anyone has tried this or implemented their own equivalent programatically in SQL.

The default setting of "max_parallel_workers_per_gather" is 2, which won't spread work over all 4 CPUs for any one query. But that doesn't matter if you aren't getting parallel plans in the first place.

Parallel query is a relatively new feature to PostgreSQL, and is still being improved.  You should use the newest version you can to give yourself the best chance of benefiting from it.

I believe the index consultation cannot be parallelized (in any version).  The table consultation can be, but it often doesn't make sense to.

If the indexed part of the query is highly selective and returns few rows, then "parallel_setup_cost" will exceed the benefit of parallelizing the table access for just a few rows.

On the other hand, if you return a lot of rows, then "parallel_tuple_cost" (multiplied by rows returned) will exceed the benefit.  If you access a lot of rows, but don't return them (like count(*) or some other aggregate, or a filter which the index is unable to address) that is the optimal case for parallelization to work well.


MySQL/MariaDB stored procedure inserts even with syntax error createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail?

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
)
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  )
  VALUES
    (
      email,
      hashedPassword,
      (SELECT
        id
      FROM
        Role
      WHERE `Code` = 'CUSTOMER'),
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      createdAt <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    ) ;
END $$

DELIMITER ;

Nope, it's not a bug.  It's a situation that would otherwise have been an ambiguity, that got resolved in a different way in the absence of the declared variable.  MySQL Server does exactly the same thing.
Watch this:
mysql> select @@version_comment, @@version;
+------------------------------+-----------+
| @@version_comment            | @@version |
+------------------------------+-----------+
| MySQL Community Server (GPL) | 5.5.30    |
+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> USE test;
Database changed

mysql> CREATE TABLE ambiguity (c1 DATETIME NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO ambiguity (c1) VALUES (c1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ambiguity;
+---------------------+
| c1                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>

All you did was reference a column name from the row you were inserting, which, "at the time" you referenced it (even though there is theoretically no such concept as a flow of time or order of operations during an insert operation) the column "already contained" (had been initialized to) its default value.
It's pretty unusual that you'd do that intentionally, but it's a theoretically legitimate operation, at least in MySQL.
Best practice is to not use the same names for stored program ("local") variables as you're using for column names, because of exactly this situation, and others like it... because it's ambiguous.

“A local variable should not have the same name as a table column.   If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.”
— http://dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html

...and, of course, as a column name, when possible, when there's no such variable.


Merge two SELECT queries with different WHERE clauses I have one table of services. I need to merge two SELECT queries. Both have different where clauses. For example

SELECT
  U_REGN as 'Region',
  COUNT(callID) as 'OpenServices',
  SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC'
FROM OSCL
WHERE
  ([status] = - 3)
GROUP BY
  U_REGN
ORDER BY
  'OpenServices' desc

This gives me result

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13           | 3
Islamabad | 10           | 4

I have another query

SELECT
  U_REGN as 'Region',
  COUNT(callID) as 'ClosedYesterday'
FROM OSCL
WHERE
  DATEDIFF(day, closeDate, GETDATE()) = 1
GROUP BY
  U_REGN
ORDER BY
  'ClosedYesterday' desc

It gives me result

Region    | ClosedServices
Karachi   | 8
Lahore    | 7
Islamabad | 4

I need to merge both results, and show ClosedServices beside the DFC column.

Treat your two current queries' resultsets as tables and join them:

select
    FirstSet.Region,
    FirstSet.OpenServices,
    FirstSet.DFC,
    SecondSet.ClosedYesterday
from
(
    SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices',
    SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC'
    FROM OSCL
    WHERE ([status] = - 3)
    GROUP BY U_REGN
    --ORDER BY 'OpenServices' desc
) as FirstSet
inner join
(
    SELECT U_REGN as 'Region',
    COUNT(callID) as 'ClosedYesterday'
    FROM OSCL
    WHERE DATEDIFF(day, closeDate, GETDATE()) = 1
    GROUP BY U_REGN
    --ORDER BY 'ClosedYesterday' desc
) as SecondSet
on FirstSet.Region = SecondSet.Region
order by FirstSet.Region

Not the prettiest bit of SQL I've ever written but hopefully you'll see how it works and understand how to maintain it.

I suspect a better-performing query would be a single SELECT from OSCL, grouped by U_REGN, with each of your three counters as separate SUM(CASE ...) statements akin to what you do currently for DFC.  This will be a single table scan, at most, depending you your indexes & schema.


Oracle Package in Data Dictionary that can't be dropped I've encountered a very interesting problem that I can't figure out how to fix.  I have a package that I wanted to create on my database with a wrapped body.  I mistakenly omitted the word "BODY" from the create or replace statement for the package body.  Running the script that way created the package in such way that it shows up as invalid but cannot be dropped.  The package shows up in queries to user_objects, etc but when attempting to drop the package I get ora-04043 not exist errors.

Anyone ever seen this before and know how to drop that corrupted package?

Select * from user_objects where object_type = 'PACKAGE'

OBJECT_NAME = 'POSTING_PROCS'
SUBOBJECT_NAME = NULL
OBJECT_ID =  134412
DATA_OBJECT_ID =    NULL
OBJECT_TYPE = 'PACKAGE'
CREATED = 12/29/2014 9:57:32 PM
LAST_DDL_TIME = 1/22/2015 11:08:52 AM
TIMESTAMP = 2015-01-22:11:08:52
STATUS =    INVALID
TEMPORARY = N
GENERATED = N
SECONDARY = N
NAMESPACE = 2
EDITION_NAME = NULL

in addition to not being able to drop the package I also can't run another create or replace because I get an error that the object already exists

select dump(object_name) from dba_objects where object_id=134412:

Typ=1 Len=13: 80,79,83,84,73,78,71,95,80,82,79,67,83

More info - I can run a create or replace for the package spec, but get the object with that name already exists error when doing create or replace package body

I am suspecting with the case sensitive name or special character(i.e. ' or white-space) included with the package name unknowingly while creating package. I tried to get object name from dictionary itself which ensures correct object name in your drop command in the following script, it can help in your context:

select
'DROP PACKAGE ' || '"' || OBJECT_NAME || '";' as "Command"
from
USER_OBJECTS
WHERE
OBJECT_NAME LIKE '%POSTING_PROCS%'
AND OBJECT_TYPE='PACKAGE';

CAUTION - REMEMBER THAT ABOVE SCRIPT GENERATES DROP COMMANDS WHICH CAN CONTAINS POSTING_PROCS AS THE PART OF OTHER PACKAGE NAME, PLEASE CHECK EACH DROP STATEMENTS BEFORE EXECUTING THEM IN YOUR SERVER.

Alternatively - You might be using client tool like TOAD, SQL Tool, SQL Developer which display your invalid object name in a GUI format. You can try to delete your object by right click on the object and follow with the drop option.


Scramble / masking data from SQL Server I need to share a SQL Server 2008 R2 DB with the development team.
The data is classified but the schema and the volumes are not.
The DB includes all primitive types (long, decimal, nvarchar etc.) but also:
XML- some of the XMLs have schemas outside of the DB (application is writing to it). I would expect a masking process to only go on the value and the attribute values, leaving the elements and the attributes as is.
Geometry and Geography- the expected behaviour is for the data to maintain the following characteristics: shape and complexity, number of rings (polygon), number of vertices, coordinate system, area. This does not have to be one to one, but if we have a very complex polygon the developers should have something similar in their environment.

How can I scramble the data to declassify it?

I'm going to suggest the following so that you can 1) Share the entire DB definition with developers and 2) allow them, or you, to recover after they mess it up.

Download SSDT and create a database project. Then import your database and remove any configuration/settings/accounts/etc. that shouldn't be seen. You can now simply Publish the database (or just share the visual studio project!) to any server with no data included for your developers to work with. They can populate it with fake data for dev/testing as needed. If you need to provide XML schemas then include that along with your other development requirements.

Developers are smart... if you truly don't want them to have the data then don't give it to them. Even developers with access to production data sets will often generate test data for various reasons, not least of which is that production data may not fulfill test cases.


What is a "partitioned view"? What is a "partitioned view"? I googled but could not get a satisfactory result.

Partitioned views are a (very) old technique for partitioning data that are very rarely used today.  Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete.  The only reason to consider using partitioned views would be if you can't afford a license for the partitioning option and you're willing to accept the reduced functionality and extra maintenance required to use partitioned views.  The documentation on partitioned views will pretty much all date back to the Oracle 7 days.

In order to use partitioned views, you would define individual tables for each logical partition, create a CHECK constraint on whatever column you want to partition on so that the optimizer knows which table a particular row must be in, and then create a view that does a UNION ALL on each table to create the partitioned view.  Queries against the partitioned view using the partition key can then be resolved by the optimizer to access only one of the physical tables comprising a particular logical partition.

Of course, if you use this technique, you have to create a new physical table and rebuild the partitioned view every time you add a new partition which is quite a bit of maintenance.  Getting the partitioned view to work correctly and making sure that partition pruning is happening correctly is much, much more challenging than when you're using a partitioned table.  Plus you end up with a ton of separate tables with very similar names polluting your namespace.


How to fix "Access denied for user 'root'@'localhost'" error for MySQL using Mac OS X? After reading a few of the similar problems to mine and none of them working, I thought I'd attempt to be more specific to see if my root problem needed to be fixed differently than the others.

I've been using MySQL for about a year now and it's always worked fine -- but I got a new Macbook Pro (10.9.3) yesterday, and everything migrated and runs great except for MySQL. It's running:

My root user is enabled and I checked to see that the password is the same as my user login (and the same as it's always been) -- but every time I try to run it, whether it be through Terminal or through Sequel Pro, I always get the same:

Access denied for user 'root'@'localhost' (using password: YES)

I also traditionally use Sequel Pro -- which migrated over from my old computer without any problems, and retained the connection details its always had:

But I'm getting the same error message through Sequel Pro as I am through Terminal:

Does anybody have any ideas of things I can try? After attempting some of the other similar questions on here, I've unfortunately come up with nothing.

Thanks!

Run this /usr/bin/mysql_upgrade -u root -p
This should reset the rights and grant access with u logged in as 'root'@'localhost'
Thereafter run this /usr/bin/mysql -u root -p... et viola!


64k AU size - master, model, msdb Should master, model and msdb reside on drives with 64k AU size? Is it necessary? Recommended?

All of our SQL servers are on VMware. If necessary, I would like to be able to recover a VM from a snapshot that restores OS, SQL executables and system databases. Then restore application databases from SQL backups.

I'm trying to determine if I should install SQL server to C: (4k AU) and place the system databases on D: (64k AU). And application databases on E:, F:, etc. (64k AU). Log files on G: (64k AU).  TempDB on T: (64k AU).  Would snap C and D.

Should master, model and msdb reside on drives with 64k AU size? Is it necessary? Recommended?

In general, make a standard and use it. To directly answer your question, if all other filesystems are going to be formatted for 64KB, then why not stick to it - though, regardless, I'd go with the storage vendor's recommendation. Let's look per DB:

Master: In general it doesn't have much "extra" data put in it and shouldn't really be growing or heavily transactional. Allocating 64k or 4k isn't going to make much of a difference.
Model: There should be no transactional activity happening in this database, in general, by default. There might be some additional objects added but this should be similar to master where it's largely static.
MSDB: This database is actually used more than both of the previous database. It holds things such as Jobs, Mail, Backup Information, etc. This database could grow to be very large when not properly looked after.

Without going into a large amount of technical detail, you should choose whatever the storage behind the virtualization layer likes as it's preferred or most optimized transfer size, that's what I'd use for my allocation unit size. This is because it matches what the storage layer likes the most and will be the most optimized.

If you want to get a little more technical, uniform extents are 8 contiguous pages which just happens to be 64k. This happens to nicely line up with a 64k allocation unit size. The largest possible log block can be 60k, which again fits nicely... but generally is much less, which results in space waste, and could not be the optimized size that the storage layer likes.

The allocation unit size is just the smallest possible fragment given to a file. Thus an AU of 64k means a file, holding just a single bit would still have an on disk size of 64k. Normally, larger AU sizes result in lower on disk fragmentation which may or may not matter, depending on the storage array's physical storage medium. In an example of this, SSDs and NVME hold internal translation tables for pages (not the same as DB pages) for wear leveling and write amplification. In this case, there is no idea of where the physical data will be saved, and thus may or may not be physically sequential.

Thus, again, I'd go with the value specified by the storage layer vendor. If you don't happen to know what that is, 4k and 64k are both well supported (though larger AU sizes may not support certain NTFS technologies, which shouldn't be used with SQL Server anyway - but for completeness I added it).

Really, what will make the largest difference is whether the storage layer has mixed 512 and 512e/h storage representations (which is the physical block size). This will cause more issues that allocation unit size of the filesystem.

  If necessary, I would like to be able to recover a VM from a snapshot that restores OS, SQL executables and system databases. Then restore application databases from SQL backups.

In a highly unrelated note, I'd double and triple check that this meets your disaster recovery or business continuity requirements/SLAs. Allocation Unit sizes should not impact this.


Checking lock granularity I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB.

Forgetting about lock escalation, I just want to check the default level of lock granularity in SQL Server 2008? How do I do that?

After comments on questions

You can query sys.indexes, looking at the allow_row_locks and allow_page_locks columns. Also, sys.tables has a column lock_escalation

I think that will allow you to find all non-default options about locks.


How to optimize Mysql database of 250 million rows for bulk inserts and selects I am a student, who have been given the task of designing a database for sensor data. My university currently has a large database which is being filled with these data, but a lot of what is being stored is not necessary. They want me to extract some of the fields from the existing database, and insert it into a new one, which will only hold the 'essentials'. I will need to extract every row from the old one, as well as fetching new data once a day.

There are 1500 sensors.
They generate a reading every minute.
Approximately 2.1 million readings every day
The current database have about 250 million rows.

The queries which will be performed will typically be to select sensor readings for a set of sensors between a given time span.
I was initially naive with respect to the added complexity large amounts of data introduces, so I grossly underestimated the time needed for this task. Because of this, and the fact that I don't have access to the server from home, I am here asking for help and input.
The initial design looks like this:
CREATE TABLE IF NOT EXISTS SENSORS (
    ID smallint UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME varchar(500) NOT NULL UNIQUE,
    VALUEFACETS varchar(500) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS READINGS (
    ID int UNSIGNED AUTO_INCREMENT,
    TIMESTAMP int UNSIGNED INDEX NOT NULL,
    VALUE float NOT NULL,
    STATUS int NOT NULL,
    SENSOR_ID smallint UNSIGNED NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (SENSOR_ID) REFERENCES SENSORS(ID)
);

Design Question
My first question is whether i should keep an auto-incremented key for the readings, or if it would be more beneficial to a have a composite key on TIMESTAMP(UNIX epoch) and SENSOR_ID?
This question applies both to the fact that I have to insert 2.1 million rows per day, as well as the fact that I want to optimize for the aforementioned queries.
Initial Bulk insert:
After a lot of trial and error and finding a guide online I have found that inserting using load infile, will best suit this purpose. I have written a script that will select 500 000 rows at the time from the old db, and write them (all 250 million) to a csv file, which will look like this:
TIMESTAMP,SENSOR_ID,VALUE,STATUS
2604947572,1399,96.434564,1432543

My plan is then to sort it with GNU sort, and split it into files containing 1 million rows.
Before inserting these files, I will remove the index on TIMESTAMP, as well as running these commands:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation='READ-UNCOMMITED';
SET sql_log_bin = 0;

After inserting, I will of course revert these changes.

Is this plan at all viable?

Can the inserts be quickened if i sort the csv based on SENSOR_ID and TIMESTAMP instead of TIMESTAMP and SENSOR_ID?

After turning indexing back on after the bulk insert, will the insertion of 2 million rows each day be possible?

Is it possible to do the daily inserts with regular insert statements, or will I have to use load infile in order to keep up
with the input load?

my.cnf
Every configuration is default except for these:
  innodb_flush_log_at_trx_commit=2
  innodb_buffer_pool_size=5GB
  innodb_flush_method=O_DIRECT
  innodb_doublewrite = 0

Are there any other optimizations I need for this particular purpose?
The server has 8GB of ram.
mysqld  Ver 8.0.22
Ubuntu 20.04
Any thoughts, ideas or inputs would be greatly appreciated.

Your should increase the innodb_write_io_threads to 8.
This setting configures how many threads are dedicated to writing to .ibd files from the Buffer Pool.


Error 1236 'Found old binary log without GTIDs' after master restart So after quite a few attempts to build a slave and getting the error above, I decided to start with no binlogs from scratch so on the latest slave rebuild I issued PURGE ALL BINLOGS BEFORE NOW() on the master to get rid of all the possible binlogs without GTIDs, then made a full dump with innobackupex and moved it to the slave.
Started the slave up and it worked fine...
Up to the point where I always get this exact error which is when I restart the master....
so after a mysql restart on the master is executed the slave lost connection of course, after reconnecting I got greeted with the usual:

"Got fatal error 1236 from master when reading data from binary log: 'Found old binary log without GTIDs while looking for the oldest binary log that contains any GTID that is not in the given gtid set'"

This is getting ridiculous now, I have rebuilt the slave 4 times now in 3 days.

Here's my my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
open_files_limit        = 65535

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /srv/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

bind-address    = xxxxxxxxx

key_buffer              = 64M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 16
myisam-recover         = BACKUP
table_open_cache                = 512
open_files_limit        = 65535
interactive_timeout=180
wait_timeout=180

query_cache_limit       = 1M
query_cache_size        = 256M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10
max_binlog_size         = 100M
expire_logs_days        = 10

log-error       = /var/log/mysql/error.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

symbolic-links=0

innodb_file_per_table
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size         = 24G
innodb_file_format              = "Barracuda"

binlog-format=MIXED
log-slave-updates=true
log-bin
gtid-mode=on
server-id=1
enforce-gtid-consistency=true

[mysqldump]
quick
quote-names
max_allowed_packet      = 32M

[mysql]

[isamchk]
key_buffer              = 16M

At the time of master restart, the slave was about 4000 seconds behind already (that's what I was trying to solve somehow, but I got this error - again -)

I case you are wondering last time I rebuilt the whole thing was last night, so it is not a case of missing binlogs due to expiry.

Does anyone have any idea?

Thanks

Something is not right about your process.

Usually, when I see error 1236, such the following I used in my old post How can you monitor if MySQL binlog files get corrupted?

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

here was the situation: When doing MySQL Replication without GTID, the IO Thread examines the position from the latest Master binlog. If Read_Master_Log_Pos is bigger than the actual filesize of the binlog, you get error 1236.

When doing MySQL Replication with GTID, the situation is somewhat similar. The IO Thread is looking for some kind of closure with regard to the GTID it was last using. When you restarted MySQL on the Master, you closed the last binlog on the Master and opened a new binlog upon startup. The IO Thread on the Slave was still active. Thus, the same error number is coming up.

The next time you restart a Master, remember the Slaves are active.

The slave should have reconnected after a minute, but that is not happening for you.

To play it safe, you should do the following

On the Slave, STOP SLAVE;
On the Master, service mysql restart
On the Slave, START SLAVE;

You should not have to do this. As an alternative, try setting up replication with heartbeat set at one tenth of a second:

CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 100;

This should make the IO Thread on the Slave a little more sensitive


avoid insert xml duplicate data how can to prevent duplicate inserts for every Execute?

insert into  IQ (XMLDT)values('<row>sdsdf</row>')

Given this table:

CREATE TABLE IQ
(
    XMLDT xml NULL
);

We first need a scalar function to extract the value of interest from the XML:

CREATE FUNCTION dbo.GetRow(@XMLDT AS xml)
RETURNS nvarchar(10)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @XMLDT.value('(./row/text())[1]','nvarchar(10)');
END;

Now we can add a computed column that uses the function:

ALTER TABLE dbo.IQ
ADD RowValue AS dbo.GetRow(XMLDT);

And a constraint to enforce the desired uniqueness:

ALTER TABLE dbo.IQ
ADD CONSTRAINT UQ__IQ_XMLDT_RowValue
UNIQUE (RowValue);

Demo:

-- Succeeds
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');

-- Msg 2627, Level 14, State 1, Line 1
-- Violation of UNIQUE KEY constraint 'UQ__IQ_XMLDT_RowValue'
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');


MSSQL Spatial performance (point in polygon) Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused

I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.

The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText - and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.

Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).

The code as it is now:

DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377  -27.385831))', 4326));

SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;

.

The data in the shape field is stored like such (Well Known Binary, I'm guessing)

SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';

    Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340

Firstly, make sure there is a spatial index on the [Shape] column.

Secondly, there is no need to call STAsText() in the WHERE clause since that will force each row to convert to string unnecessary (and disqualify the use of any spatial indexes I think).

Lastly, inspect the execution plan to see if the spatial index is used. You may have to use a index hint since SQL Server don't always choose the spatial index in my experience. See the last example here.


MySQL: Using DATE_SUB to subtract business days instead of all calendar days I am looking for a robust solution to subtract the number of business days from a date. I am using DATE_SUB however it subtracts all calendar days.

For example, if today is Monday and I subtract 2 days from today, I would like for the result to be Thursday last week and not Saturday.

You can use DAYOFWEEK to calculate the right number of days to subtract based on that. This also works for date_add. For the DAYOFWEEK index, 1 is equal to Sunday and 2 is equal to to Monday.

case
when DAYOFWEEK({date}) = 1 then date_sub({date}, Interval 2 Day)
when DAYOFWEEK({date}) = 2 then date_sub({date}, Interval 3 Day)
else date_sub({date}, Interval 1 Day)
end

http://www.w3resource.com/mysql/date-and-time-functions/mysql-dayofweek-function.php


Are duplicates in Read Commited standard behaviour? This refers to this question: Duplicate records returned from table with no duplicates

The "read commited" isolation level assumes that one transaction could read same record differently, but it does not explicitly imply that records could be duplicated. I agree that two statements in the same transactions could return different data sets, but returning same data set with record duplication sounds insane.

As far as I know only MS SQL Server behaves this way.

Please prove or disprove if it conforms to "Read Committed" isolation level defined by ANSI SQL standard.

The SQL Standard isolation level definitions are (probably deliberately) imprecise. They are given in terms of allowable concurrency phenomena:

P1 - dirty read
P2 - non-repeatable read
P3 - phantom

At READ COMMITTED isolation, only P1 is disallowed.

extract from ISO/IEC 9075-2

Reading the "same record" more than once due to movement within a physical structure is therefore not disallowed, so long as each read is not a dirty read (i.e. the read is performed on committed data).

Therefore, SQL Server's locking implementation of READ COMMITTED isolation conforms with the standard.

I understand the behaviour may be surprising, but the root of that surprise is in misunderstanding what this isolation level does, and does not, guarantee. For example, one might expect a concurrent UPDATE never to change the number of records returned by a SELECT query. This is a reasonable expectation, but not one that must be met to conform with the standard.

If you want a point-in-time (stable) view of the data, locking read committed is not the isolation level implementation you need. While not mandated by the standard, read committed using row versioning (RCSI) happens to provide a statement-level point-in-time consistent view of the committed state of the database. For a transaction-level point-in-time consistent view of the committed state of the database, you would need snapshot isolation (SI).

  As far as I know only MS SQL Server behaves this way.

It would be possible in any database that implements read committed using locks (specifically short-term locks for reads) and where a concurrent modification might cause a record to move ahead of the current scan position. In other words, it is implementation-specific.

Related reading:

A Critique of ANSI SQL Isolation Levels by Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil, and Patrick O'Neil.
The Read Committed Isolation Level by me


MySQL - INSERT ... SELECT .. FROM .. - Single transaction or transaction per row? Question:

I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup).
I am using the following syntax to do that:

INSERT `new_table` (`new_column`,`more_columns`)
SELECT `old_column`,`more_old_columns` FROM `old_table`;

I am wondering if MySQL/InnoDB will perform this as one giant transaction, or will MySQL/InnoDB create a transaction per row it encounters from the select? (I would basically would like to know if it will perform a COMMIT on each row).

It will all fail or it will all be succesfull meaning one giant transaction.


Big size difference in ibdata file from live to staging I have a mysql server running on AWS Instance. The server has a schema of around 8 GB which is the main schema.

I have two environments staging and live. The live has only one schema i.e. live_schema where as the staging database has multiple schemas with different time stamp like live_copy_03207... which are copy of live. So the total database size on staging is around 50 GB.
I am using InnoDB engine for the database.
Here's the issue. The live ibdata file is 4.2 GB where as the staging ibdata files is only 187 MB.

What could be the reason for this huge difference between ibdata size from live to staging? I know it only grow and there no shrinking, I know to shrink/cleanup the file I am required to backup the database and delete everything and restoring the database.

One possibility is that you have or had in the past innodb_file_per_table=0, and despite separating them at a later stage into separate files (changing it to 1+rebuilding the tables), the space, as you said, has not been reclaimed.

In most cases, ibdata1 growing too much is caused by extra temporary space needed in the UNDO area (which before 5.6, was always part of the ibdata1 file). The usual problem happens this way: A long transaction starts (a long running query, a non-closed transaction by mistake, etc.)- all subsequent edits cannot purge the old data because in repeatable read mode, a transaction after the first select goes into "snapshot mode". As a consequence, the UNDO space keeps growing and growing, and your queries start getting slower and slower. This is very typical in production hosts that have high write activity but also have to backup consistently the database or perform statistics-like queries.

There are other reasons why this could happen, but the long running transactions is the #1 cause (aside from former bad configuration). If the long running transactions are no longer active, know that data is available for new pages within the file, just not returned to the filesystem- so unless it happens again it may not grow again. As you said, either export+import is the only way to shrink it before MySQL 5.6.

You have more information on how and what ibdata1 is used for at: https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/ Many of the pains of tablespace handling were fixed on 5.6 (UNDO on an external file, importing/exporting .ibds, querying ibdata structure contents,...) so give at some point a chance to 5.6 testing, it may be worth in your case.


SQL Server: Index and Include Columns Assume that I have a table Foo with columns A, B, C, D, and E. All are integers. Let's assume a million records, and that we're on SQL Server 2017.

The following index exists:

create Nonclustered index IX_Foo on Foo ([A], [B]) include ([C], [D], [E])

If I run the following query:

Select A, B, C, D, E from Foo where A = 1 and B = 2

Will this query trigger a Key Lookup in Foo because A and B are not included in the index's Include listing, or will SQL Server realize that A and B are located in the key itself and avoid a Key Lookup?

A simple test with a little over 1M values

Data

CREATE TABLE dbo.FOO([A] int , [B] int,[C] int, [D] int, [E] int);

INSERT INTO dbo.FOO([A]  , [B] ,[C] , [D] , [E] )
VALUES(1,1,1,1,1);
GO 1000

INSERT INTO dbo.FOO
SELECT * FROM dbo.FOO;
GO 10

INSERT INTO dbo.FOO([A]  , [B] ,[C] , [D] , [E] )
VALUES(1,2,1,1,1); --one matching record

This dataset is not a real world dataset, as aside from one record, all the other records are the same.

Amount of rows

select COUNT(*) from dbo.FOO
   (No column name)
1024001

Query

Select A, B, C, D, E from dbo.FOO where A = 1 and B = 2;

No key lookup

Which is normal as this index is perfect considering the query and the dataset.

key column <> included column

The main difference between the key columns and included columns is that
these key columns are ordered [A] --> [B] (B-Tree) and the other columns [C], [D], [E] are not ordered (leaf level).

As a result seeking only on [A] is possible, seeking on [AB] is possible, but seeking only on [B] will create a residual predicate if the index is used.

Additionally

You cannot add a column as both the key column and the included
column
Some datatypes cannot be added to the key column but could be included (all datatypes except text, ntext, and image)

More info

Example of a key lookup happening as a result of a different query

create Nonclustered index IX_Foo_2 on dbo.FOO ([B]) include ([C], [D], [E]);

Select A, B, C, D, E from dbo.FOO where  B = 2;

A key lookup to get [A] happens, but it is not very expensive (one row).


Grant usage to a schema from another database In PostgreSQL, how can I GRANT USAGE to a certain schema for a certain user in a certain database while I am in another database?

I am connected to a database (let's say database first) as a user with CREATEDB privilege. I want to create another database second for user normal_user and grant a usage on public schema to this user. I can do CREATE DATABASE second; from my current connected database but AFAIK I can't do GRANT USAGE ON SCHEMA public TO normal_user; since that will only grant usage to the public schema in my current connected database.

I want to do this to automate database creation, user creation, and permission granting in a single SQL script.

I was hoping for a hacky way to do this. I want to do this to automate the db and user creation as well as permission granting. I am aware that some tools out there lime ansible or terraform might help in doing automation. But I am hoping if we can do this solely from Postgres script alone.

I am using AWS RDS as my Postgres and I don't think I have a way to ssh there to run the Postgres command from cmd line. I am trying to do this from dbeaver script page. So I think SQL is all I have to do this. If I can switch user or switch db easily using SQL, that helps.

After a thought, I think using psql -u user -p -h hostname I might be able to connect to the instance and switch around database in the shell. But let me know if I am mistaken.

You cannot change an object in one database while you are connected to the other database. You'll have to connect to the database where you want to grant the permission.

You can still do it in a single script if you use psql, because that has the \c command to connect to a different database. So your script could look like that:

CREATE DATABASE second;
\c second
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA newschema;
GRANT USAGE ON SCHEMA newschema TO normal_user;

You should always revoke the CREATE privilege on schema public from PUBLIC for security reasons, and you don't need to grant USAGE on that schema, since that is already granted to PUBLIC.


Select using values from another table I want to select specific columns from a table, let's say table A.  Table B has the names of the names of the columns I would like to select. How would I write a MSSQL query that will use the distinct values from Table B as the column names that I want to query from table A?

Can't you just use the COALESCE function like this?

DECLARE @columnList VARCHAR(MAX), @sql VARCHAR(MAX)
SELECT @columnList = COALESCE(@columnList+',' ,'') + columnName
FROM TableB

SET @sql = 'SELECT ' + @columnList + ' FROM TableA'

EXEC(@sql)


Errors in the OLAP storage engine: A duplicate attribute key has been found when processing When I try to process my cube and specifically the Employee_DIM, I get the following error :

  Errors in the OLAP storage engine: A duplicate attribute key has been
  found when processing: Table: 'dbo_Employee_DIM', Column: 'Firstname',
  Value: 'Aurélie'. The attribute is 'Firstname'.

I think that duplicate values for the column Firstname are considered as keys. After checking with the Employee_DIM :

 SELECT [Firstname], COUNT([Firstname]) AS dup_count
     FROM [Database].[dbo].[Employee]
        GROUP BY [Firstname]
             HAVING (COUNT([Firstname]) > 1)
                ORDER BY [Firstname]

Is there any alternative to leave only the business key in Employee_DIM and get Lastname and Firstname without adding them in the dimension for a future use?

You can find the solution here : Errors in the OLAP storage engine: A duplicate attribute key has been found when processing


CSS-like dynamic properties cascading configuration table? Have an idea in mind, not interested in inventing an existing wheel, but can't find info on the subject so I'm turning to the community.
My goal is to define a "dynamic properties cascading configuration table", which means the following:
I would like to maintain a metadata table with dynamic properties for a certain database table entities.
There are many properties and they may or may not exist for the entity. That is why they should be kept on a separate table.
Moreover, I would like to have configuration levels for that metadata table, e.g.: SYSTEM, TOP_LEVEL, MID_LEVEL, etc.
Let's say we have an online store system where we have brands, each having customers, each having shops.
We'd like to define a system-wide default property of a logo image URL.
This property may be different on the brand leve, customer level or the individual shop level.
The design for this table would be something like:
  id          // unique row id
  configType  // SYSTEM, BRAND, CUSTOMER, SHOP
  entityId    // ID of a user, a post, etc.
  key
  value
  valueType   // string, number, etc. - will be used for validations and parsing

Possible rows would look something like:
+----+------------+----------+----------+-----------------+-----------+
| id | configType | entityId |   key    |      value      | valueType |
+----+------------+----------+----------+-----------------+-----------+
|  1 | SYSTEM     | SYSTEM   | logo-img | sys-logo.jpg    | string    |
|  2 | BRAND      | brnd2    | logo-img | brnd2-logo.jpg  | string    |
|  3 | CUSTOMER   | cust81   | logo-img | cust81-logo.jpg | string    |
|  4 | SHOP       | shp76    | logo-img | shp76-logo.jpg  | string    |
+----+------------+----------+----------+-----------------+-----------+

So when searching for the logo image of shop shp76, which its parent is cust81, which its parent is brnd2, we would like to get the most nested level of configuration possible. shp76-logo.jpg in the above case.
If SHOP and BRAND config types wouldn't exist for this configuration, we would still prefer the CUSTOMER level value (cust81-logo.jpg) over the SYSTEM default.
+----+------------+----------+----------+-----------------+-----------+
| id | configType | entityId |   key    |      value      | valueType |
+----+------------+----------+----------+-----------------+-----------+
|  1 | SYSTEM     | SYSTEM   | logo-img | sys-logo.jpg    | string    |
|  2 | CUSTOMER   | cust81   | logo-img | cust81-logo.jpg | string    |
+----+------------+----------+----------+-----------------+-----------+

A postgres query would look something like:
  SELECT "value"
  FROM "ConfigTable"
  WHERE "key" = 'logo-img'
    AND "configType" IN ('SYSTEM','BRAND','CUSTOMER','SHOP')
    AND "entityId" IN ('SYSTEM','brnd2','cust81','shp76')
  ORDER BY (CASE status
            WHEN 'SYSTEM' THEN 1
            WHEN 'BRAND' THEN 2
            WHEN 'CUSTOMER' THEN 3
            WHEN 'SHOP' THEN 4
            END) DESC
  LIMIT 1

The config types we query are dynamic on a per-case basis. So if we'd like to query the value for the CUSTOMER level, we'll be able to use a different set of config types (e.g. without the SHOP type in this case)
This will allow us creating and maintaining different levels of configuration nesting for totally different configuration types as well (e.g. SYSTEM, WEEKLY, DAILY, HOURLY)

Issues with this solution:

The valueType and key do not change between rows, and are both duplicated.
The thought behind this is that this is already a pretty complex subject so introducing another id;key;type table would be another level of complexity.
How to maintain the data? (manually? write a configuration tool?)
How to query for multiple keys at the same time?
Is it possible to achieve with a pure SQL query or would code be required?
Performance
Should results be cached somewhere else (e.g. Redis)?
Is this a known subject which already has academy-level solutions?

I think you are describing an EAV model.
Data driven systems arent uncommon. But if the design allows the application to change without altering the schema then you're talking about EAV.
The advantage of this is that non-technical users can extend or customise an application and add attributes or fields without changing the actual schema.
The downside is that you rely on the app to enforce integrity and datatyping. Performance suffers too.
In my experience you see less of these as xml & json have become more prolific.
Have a read of the various database design models at the bottom of this wikipedia page


What are the drawbacks with using UUID or GUID as a primary key? I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this design since the UUID/GUID is quite large and they are almost random. The alternative is to use an auto-incremented INT or LONG.

What are the drawbacks with using UUID or GUID as a primary key for my tables?

I will probably use Derby/JavaDB (on the clients) and PostgreSQL (on the server) as DBMS.

It depends on your generation function and size of the final tables

GUIDs are intended to be globally unique identifiers. As discussed in the Postgres 8.3 documentation there are no methodologies that are universally appropriate to generate these identifiers, but postgreSQL does ship with a few more useful candidates.

From the scope of your problem, and the need for offline writes, you've quite neatly boxed out the use of anything but a GUID, and therefore there are no compensatory advantages of other schemes.

From a functional standpoint, the key length is usually not an issue on any kind of modern system, depending on the number of reads and size of the table. As an alternative methodology, offline clients could batch new records without a primary key and simply insert them when reconnecting. As postgreSQL offers the "Serial" datatype, clients will never need to determine the ID if they can perform a simple write to the database.


SQL Server Management Studio - Debugger buttons greyed I have for mission to enable Transact SQL from a remote computer. My database is on a server and i'm trying to use the debug from a computer on the same network. My firewalls are all open on both side. Both of my machines are in a domain, i'm trying to use the debugger with a user registered in the security connection and who gets the sysadmin rights. Everything looks OK, i can open the debugger but the buttons to step into the object are greyed.

When I try to debug on the machine itself it works without problem, but remotely I can't use F10 F11 etc .. You can see the pictures attached, where you can see the differences between local debugger screen and remote debugger screen:

When it works on local: https://gyazo.com/5a900c7f961f9f43af16be20843162d3
Doesnt work remotely: https://gyazo.com/7bd375164ca0ccf84dcb2fd8349fda04
User configuration: https://gyazo.com/81a9ff4cd00adedb07791595ec8984ce

Does anybody have an idea of what's going on ? Let me know if you need any information.

You need to open the network ports & firewall ports between your desktop running SSMS, and the remote computer.

Just generally speaking, though, I'd highly recommend against running the debugger on a live server. You're going to force SQL Server to hold your locks open for longer periods of time, and you can easily cause a blocking storm. Instead, I'd strongly recommend debugging your code locally on your own dedicated instance of SQL Server. (Developer Edition is free.)


SQL Server 2008 R2 DMV Question I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV.

SELECT TOP 10
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(qt.TEXT)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1),
db_name(qt.dbid) as db_name,
qs.execution_count,
qs.total_logical_reads,
qs.total_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
SUBSTRING(CONVERT(varchar(19),qs.last_execution_time),1,19)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

The query returns all of the information requested, except for the name of the database that the query came from or was directed at.  Whether I use dm_exec_sql_text or dm_exec_query_plan the result is the same.

  db_name(qt.dbid) as db_name  dm_exec_sql_text

or

  db_name(qp.dbid) as db_name  dm_exec_query_plan

Both return NULL or tempdb as the database name.

The same problem occurs when selecting Reports -> Performance Top Queries by Average IO.

The database name is empty.

If I add the query plan to the query however, and then open up the query plan in SSMS, I can see the name of the originating database by hovering over the various Index Seeks, Scans or RID lookups.

I have noticed that there are several databases referenced in the query plan, such as mssqlsystemresource along with the trackit database

If the query plan was able to display the name of the database or databases affected by the query in my top 10 list, then it stands to reason that I should be able to obtain the names of those databases using a DMV.

How can I modify the top 10 query to retrieve the name of the database for each query?

Or is there a better way to go about getting the top 10 queries by CPU/IO/Memory usage and get the database name or names for each of the top 10?

use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();

This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is due to db1 and 15% is due to db2. But ultimately you just can assign queries to a database. The fact that some (ok, most) queries are 100% contained inside a db does not mean that all query resources can be assigned deterministically to a db.

However, for practical means is relatively simple to automate exactly what you did in your post: inspect the plans and identify all physical access operators locations and use this info to assign the query resources to a DB.

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select x.value(N'@NodeId',N'int') as NodeId
    , x.value(N'@PhysicalOp', N'sysname') as PhysicalOp
    , x.value(N'@LogicalOp', N'sysname') as LogicalOp
    , ox.value(N'@Database',N'sysname') as [Database]
    , ox.value(N'@Schema',N'sysname') as [Schema]
    , ox.value(N'@Table',N'sysname') as [Table]
    , ox.value(N'@Index',N'sysname') as [Index]
    , ox.value(N'@IndexKind',N'sysname') as [IndexKind]
    , x.value(N'@EstimateRows', N'float') as EstimateRows
    , x.value(N'@EstimateIO', N'float') as EstimateIO
    , x.value(N'@EstimateCPU', N'float') as EstimateCPU
    , x.value(N'@AvgRowSize', N'float') as AvgRowSize
    , x.value(N'@TableCardinality', N'float') as TableCardinality
    , x.value(N'@EstimatedTotalSubtreeCost', N'float') as EstimatedTotalSubtreeCost
    , x.value(N'@Parallel', N'tinyint') as DOP
    , x.value(N'@EstimateRebinds', N'float') as EstimateRebinds
    , x.value(N'@EstimateRewinds', N'float') as EstimateRewinds
    , st.*
    , pl.query_plan
from sys.dm_exec_query_stats as st
cross apply sys.dm_exec_query_plan (st.plan_handle) as pl
cross apply pl.query_plan.nodes('//RelOp[./*/Object/@Database]') as op(x)
cross apply op.x.nodes('./*/Object') as ob(ox)


How to have a one-to-one relation to either one of two other tables Suppose I have three tables

orders  table, fields: name, address, country, paid, shipped, amount
paypal payment table, fields: order_id
stripe payment table, fields: customer_id, cvc_valid, address_valid, zip_valid

Each order should be associated with only one payment table, paypal or stripe
Question is: How do I associate an Order with its payment when it is possible that the payment is either in the Paypal table or in the Stripe table?
I thought of adding an enum field to the Orders table to decide whether to look up the payment in the Paypal table or to look up in the Stripe table. Are there better methods than this?
I would like to use either MariaDB, MySQL, or SQLite.

There are several options:

Combine Paypal and Stripe tables into a single Payment table, with fields to hold either type of payment;
Have both a paypal_id and a stripe_id in the Order table; use a condition on the table to ensure that at least one of them is NULL.
Have a single Payment_ID with a flag to indicate whether it's an id from Paypal or Stripe as you propose.

The first two options allow for the payment ID(s) to be foreign keys to the payment table(s), the third does not.

I would lean towards a single table with all payment data, but that may be a personal preference. Which of the first two options is better would depend on whether there are any common columns between Paypal and Stripe, and just how large the distinct columns are.

UPDATE: joanolo's answer lays out how to actually implement my option 2; take a look!


How to check if integer array overlaps with int4range? PostgreSQL version: 9.5

I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the 1980's OR the 2000's. Let's say I have two date columns: start_date and end_date. Here's my best attempt so far:

SELECT *
 FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
                EXTRACT(DECADE from end_date)::int) && (int4range(198, 200))

This query will check for anyone employed in the 1980s, 1990s, or the 2000s. I don't want to compare a range to another range; instead, it should be checking if any of the values in the array is contained in the range. How can I accomplish this? Is there another way I could be approaching this problem?

UPDATE

Thanks to the answer by joanolo, my working query is:

SELECT *
 FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
                EXTRACT(DECADE from end_date)::int, '[]') @> ANY(ARRAY[198, 200])

You can use the ANY and ALL array element quantifiers, together with the @> contains element range operator.

If you want to know if ANY element of the array lies within your range, which is what, according to your comments, is what I interpret you want, you can use:

SELECT
    int4range(5, 8, '[]') @> ANY(ARRAY[1, 3, 5, 7]) AS some_are_in

(in this case, this will return TRUE, because both 5 and 7 are part of the range)

This nearly translates your request (but reversing it): Does the range contain ANY of the elements of the array?

If you would like to know that they're all in, you'd use ALL instead of ANY:

SELECT
    int4range(5, 8, '[]') @> ALL(ARRAY[1, 3, 5, 7]) AS all_are_in

(in this case, this will return false, because 1 and 3 are not part of the range)

You can find these and some other examples at dbfiddle here


Sql Server Plan - what is a Filter after an Index operation? I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL Server to do so, instead of doing all in the Index scan?

For an example:

The index already contains all the fields used by the WHERE. I expanded the Index Scan operation, and it execute part of the where with some parameters involved (totally expected). I then expanded and inspected the Filter operation, and what it does is to apply some other filtering on a couple datetime fields already evaluated by the Index Scan, plus some other conditions on some other SP parameters.

What's going on here? I obviously don't care about my specific query, that's not the point, I'm trying to understand what conditions can cause this to happen.

Filter operators are often used for non-SARGable predicates, or when the predicates are too complicated to be pushed effectively to the index access operation.

In your case, I'm willing to bet that it's a series of optional predicates. These are not SARGable: Conor vs. Optional Parameters .

  ...plus some other conditions on some other SP parameters...

If your code is doing something like col = @var or @var IS NULL, it would fall into that category, and may result in the filter operator.


MSQL - Switching rows to columns I've been stuck on the following for quite a while, it seems like it should be relatively simple but I can't quite get my head round it.

What you are missing is a row that can be grouped by. Row_NUMBER is a good solution

CREATE TABLE OCCUPATIONS
    (Name varchar(255), Occupation varchar(255))
;

INSERT INTO OCCUPATIONS
    (Name, Occupation)
VALUES
    ('Samantha', 'Doctor'),
    ('Julia', 'Actor'),
    ('Maria', 'Actor'),
    ('Meera', 'Singer'),
    ('Ashely', 'Professor'),
    ('Ketty', 'Professor'),
    ('Christeen', 'Professor'),
    ('Jane', 'Actor'),
    ('Jenny', 'Doctor'),
    ('Priya', 'Singer')
  ;
GO

SELECT Row#,Doctor, Actor, Singer, Professor
from
(
  select Name, Occupation,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY name ASC) AS Row#
  from OCCUPATIONS
) d
pivot
(
  max(Name)
  for Occupation in (Doctor, Actor, Singer, Professor)
 ) piv;
GO

Row# | Doctor   | Actor | Singer | Professor
---: | :------- | :---- | :----- | :--------
   1 | Jenny    | Jane  | Meera  | Ashely
   2 | Samantha | Julia | Priya  | Christeen
   3 | null     | Maria | null   | Ketty

WITH CTE as (SELECT
Name, Occupation,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY name ASC) AS Row#
FROM OCCUPATIONS)
SELECT Row#,Doctor, Actor, Singer, Professor
from
(
  select Name, Occupation,Row#
  from CTE
) d
pivot
(
  max(Name)
  for Occupation in (Doctor, Actor, Singer, Professor)
 ) piv;
GO

Row# | Doctor   | Actor | Singer | Professor
---: | :------- | :---- | :----- | :--------
   1 | Jenny    | Jane  | Meera  | Ashely
   2 | Samantha | Julia | Priya  | Christeen
   3 | null     | Maria | null   | Ketty

db<>fiddle here


sp_setapprole and creating users / logins We have set up SQL Server and our Application in such a way that an SQL Server login has … well no permissions when accessing the database using any tool, except our application. This is needed because some people might fire up access (or SQL Server Management Studio) and would be able to manipulate the data in that way.

So we set up an Application Role and gave that Application Role the necessary permissions. Everything works nicely, users can't access our tables unless they use our Application.

Now comes the problem. In our application it should be possible to Create additional logins / users, or set some specific users as being Administrators (only admins can do user related stuff). I'm not 100% certain but in that case I think the user needs SecurityAdmin role. So we have some code in our application which looks like this :

IF NOT EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
    BEGIN
        SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
            + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
            + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
        EXECUTE(@SQL);
    END

IF NOT EXISTS ( SELECT  name
                FROM    sys.database_principals
                WHERE   name = @LoginName )
    BEGIN
        SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
            + @LoginName + ']';
        EXECUTE(@SQL);
    END

IF EXISTS ( SELECT  name
            FROM    sys.server_principals
            WHERE   name = @LoginName )
    BEGIN
        EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
    END

The problem is of course that the server_principals is in the Master Database, and there is no way I can give my Application Role those permissions. Is there any way to work-around this problem or an easy way to solve this ?

Well … actually my problem is I will need a good example on how to set this up I guess. For now I think I can set everything up with an AppUser application role and give that role all insert / select / update / execute rights for an application.

But what permissions should I give to the AppAdmin then in order to allow it to Create LOGINs and USERs on the SQL Server and allow them to set others as Admins and change other users passwords. Any suggestions on that, since that involves some stuff in the Master database which is outside of the scope of my Application Role. Or am I missing something?

With my current setup I'm getting : Msg 15151, Level 16, State 1, Line 1 Cannot alter the login 'test.gebruiker', because it does not exist or you do not have permission.

to do it correctly is not very simple, although once setup, your done.

Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.

Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.

You have 2 options:

Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option TRUSTWORTHY to ON
However if you don't fully understand what this option does first read:
SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
Use signed stored procedures to accomplish your task.

I'll first give you the example of option 1:

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                            @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
    IF NOT EXISTS ( SELECT  name
                    FROM    sys.server_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
                + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
                + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
            EXECUTE(@SQL);
        END

    IF NOT EXISTS ( SELECT  name
                    FROM    sys.database_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
                + @LoginName + ']';
            EXECUTE(@SQL);
        END

    IF EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
        BEGIN
            EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
        END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO

sp_setapprole   @rolename='appRole',
                @password='Passw0rd!'

EXEC usp_testproc 'testlogin','password23@','stefaan'

Now for an example of option 2 I'd like to point out a answer a gave on another question:
Msg 4834 “You do not have permission to use the bulk load statement”