Create an explain plan for query over a global temporary table I have a query which aggregates data from a global temporary table. The table will be filled right before the aggregation. My goal is a reliable explain plan for that query.

Is there a way in OracleDBMS to simulate a fill level of the temporary table, witout the necessity to insert anything?

Example

Normal query:

INSERT INTO gtt VALUES(..)
SELECT * FROM gtt

Explain plan:

EXPLAIN PLAN FOR SELECT * FROM gtt /* with hint for maybe 10k records */

Motivation: The goal is to find out whether the statement falls below the threshold values for costs and CPU. We want to find out, what kind or what amount of temporary data breaks our tuning concept.

For testing purposes, you can insert data in your GTT, gather statistics, and lock statistics. That will let you get an explain plan in another session as if your GTT had data there. You should unlock statistics after you're done with testing.

I do wonder why you have this request. As of Oracle 12c GTTs have session-private statistics by default. You can simply gather statistics after the insert. On Oracle 11g you can still safely gather statistics on the GTT if you're guaranteed to only have a single session querying the GTT at once. Otherwise you can delete statistics, lock them, and rely on dynamic sampling to get statistical information about the table. If your default level of dynamic sampling isn't sufficient you can set a level just for the GTT at the query level with the /*+ dynamic_sampling({alias} {level}) */ hint. Another option is to load a representative amount of data and to lock the statistics.


Permissions needed to call DBMS_DATAPUMP from within package I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a procedure I get the following error.

[Error] Execution (1: 1): ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
ORA-06512: at "UTILITY.MANAGE_SCHEMA", line 71
ORA-06512: at line 1

This error is being generated from the DBMS_DATAPUMP.OPEN call. Since it works in the anonymous block, I'm assuming this is actually a permissions issue (i.e. one or more  permissions granted via a role are required). However, I can't find any documentation about what permissions are necessary to use datapump.

This problem boils down to: which permissions need to be explicitly granted to a user/schema in order for a package in that schema to be able to call DBMS_DATAPUMP?

The owner of the package has both the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles, as well as the DBA role.

The procedure code is below.

   PROCEDURE copy_schema (p_source_schema VARCHAR2,
                          p_target_schema VARCHAR2,
                          p_asynchronous BOOLEAN := FALSE,
                          p_link_name VARCHAR2 := 'prddb') IS
      dph NUMBER;
      v_source_schema VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.simple_sql_name (p_source_schema));
      v_target_schema VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.simple_sql_name (p_target_schema));
      v_link_name VARCHAR2 (30)
         := UPPER (DBMS_ASSERT.qualified_sql_name (p_link_name));
      v_job_name VARCHAR2 (30) := UPPER ('IMPORT_' || p_target_schema);
      v_state VARCHAR2 (30);
   BEGIN
      DBMS_OUTPUT.put_line (
            'Starting copy: source_schema = '
         || v_source_schema
         || '; target_schema = '
         || v_target_schema
         || '; link_name = '
         || v_link_name
         || '; job_name = '
         || v_job_name);
      dph :=
         DBMS_DATAPUMP.open ('IMPORT',
                             'SCHEMA',
                             v_link_name,
                             v_job_name);
      DBMS_OUTPUT.put_line ('dph = ' || dph);
      DBMS_DATAPUMP.metadata_filter (dph,
                                     'SCHEMA_LIST',
                                     '''' || v_source_schema || '''');
      DBMS_DATAPUMP.metadata_remap (dph,
                                    'REMAP_SCHEMA',
                                    v_source_schema,
                                    v_target_schema);
      DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE');
      DBMS_DATAPUMP.create_job_view (dph, UPPER (v_job_name || '_vw'));
      DBMS_DATAPUMP.start_job (dph);

      IF NOT p_asynchronous THEN
         DBMS_DATAPUMP.wait_for_job (dph, v_state);
      END IF;
   END copy_schema;

I have found one solution to creating a procedure that calls datapump, but it solves the problem by putting the procedure in the SYSTEM schema, which I would prefer not to do.

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure.

Long Answer:

In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles assigned to those two roles). Attempting to explicitly grant all 2683 privileges resulted in 2566 failures, but the package was able to call DBMS_DATAPUMP successfully.

The question then became: which of the remaining 117 privileges are actually needed for DBMS_DATAPUMP? I then grouped the remaining privileges and revoked them one group at a time (I assumed that it was a group of related permissions that would ultimately be needed). After running through nearly all of the groups, only the set of CREATE ANY privileges remained. When I revoked that group, the package stopped working. I then granted each of those permissions, calling the package after each grant. It was finally CREATE ANY TABLE that restored functionality to the package. A little further tinkering determined that CREATE TABLE is, in fact, sufficient to call DBMS_DATAPUMP from within a package.

CREATE TABLE is the only permission that I've found needs to be granted for the functionality found in the procedure shown in the question. Other procedures/datapump settings may require additional privileges. dbms_datapump.create_job_view, for instance, requires CREATE VIEW. In that case, the error message when the privilege is missing is, more reasonably, ORA-01031: insufficient privileges.


Can a table column be written to treat two different values as the same value? The title is a bit generic, but the specific issue is that the United Kingdom of Great Britain and Northern Ireland has two commonly used 2-character codes. ISO 3166-1 specifies the code GB as the proper code, but many people still use UK for that country.

Creating a separate table to store alternate country codes and updating all my queries against the main table to also perform a secondary lookup on the alternate codes table seems like overkill for a single country. At this time, I only know of that one country where this may be a problem - at least with Alpha-2 country codes.

So what I'm wondering, is if there is a way to construct a table or column such that querying a single nchar(2) column with either UK or GB will return the same record?

I know how to write individual queries to do this, but that means updating many queries in many places (stored procedures, views, UDFs, and even application code outside the database). I'm almost wondering if I can code a custom collation, inheriting from an existing collation and just add something to make the two character sequences equal to each other..?

This isn't a perfect solution but may work for you.

Rename your source table.

EXEC sp_rename 'countries','countries_source'

Then put a view on top of it

CREATE VIEW countries AS
SELECT fields,
    CASE WHEN CountryCode = 'GB' THEN 'UK' ELSE CountryCode END AS CountryCode
FROM countries_source

Your existing SELECT queries won't have any problems.  I think you will have to modify your UPDATE queries and might have to update your INSERT and DELETE queries as well.  But some quick tests should let you know if that's the case.


What process would you use to determine references on a database that has no integrity? Imagine you inherit a database which has a bunch of tables, no documentation and zero referential integrity. Now you're trying to find out what are the various relationships between different tables.

What process would you go through to do this e.g. finding common data patterns, matching these across tables?

You could programmatically build queries to test which combinations of columns look like they might be keys. In order to test a given relationship you'd run a query of the form:

SELECT 1 AS [IsPossibleKey: ChildTable.KeyColumn => ParentTable.PrimaryKeyColumn]
WHERE NOT EXISTS (
 SELECT *
 FROM ChildTable c
 WHERE NOT EXISTS (
  SELECT *
  FROM ParentTable p
  WHERE c.KeyColumn = p.PrimaryKeyColumn
 )
)

This can give you a fairly raw list of key relationship candidates. I would write a SQL query to programmatically generate all possible queries of the above form, paste all of them into a new SSMS window and run time.

You probably should exclude candidates with zero non-NULL values in the child table column.

Another problem is that bad data might have slipped in. Heck, since there are no FKs it almost certainly has slipped in somewhere.

Also, this only finds single-column combinations.


How to connect to MongoDB on Windows 8 I'm trying to get MongoDB installed and running on my new Windows 8 computer. However, I cannot seem to be able to connect to MongoDB when using the mongo.exe shell.

I got the following error:

MongoDB shell version: 2.4.8
connecting to: test
Sun Dec 15 14:06:39.835 Error: couldn't connect to server 127.0.0.1:27017 at src/mongo/shell/mongo.js:146
exception: connect failed

How can I connect to MongoDB properly? I already have MongoDB installed, along with having the Data Directory all set up. I also created a specific directory for the MongoDB log files, and installed the MongoDB service.

In the directory that you ran mongo.exe you should also see the executable mongod.exe.

mongo.exe is the shell that connects to the server/daemon that allows you to issue commands and query your databases.
mongod.exe is the actaul mongo server/daemon.

First run mongod.exe to start the server then in a new command window run mongo.exe. The shell should now connect to the running server. For production you will want to look at setting mongod as Windows Service instead of just running in a separate command window.


Configure Oracle 11.2+ ACL to work with mail server having an internal IP address I configured a new Oracle 11.2.0.4 database on CentOS 7.

I verified that I can send email from the database server 192.168.0.1's linux command line, using an 192.168.0.2 mail server (e.g. mail.example.com) on port 26.

The /etc/hosts file on the database server (192.168.0.1) contains the line

192.168.0.2 host9.example.com mail.example.com

so the database server can map mail.example.com to 192.168.0.2.

I'm trying to send mail using the following command in SQLDeveloper worksheet for schema1,

exec utl_mail.send(sender => 'name@example.com', recipients => 'name@example.com', subject => 'My Title', message => 'my body');

and it works. Then I use the same command minus the exec in a function called by a stored procedure, both in schema1, and it gives error

stack trace: java.sql.SQLException: ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at "SCHEMA1.MY_FUNC", line 260
ORA-06512: at "SCHEMA1.MY_PROC", line 40
ORA-06512: at line 1

My configuration is as follows. If I select * from dba_network_acls; as SYS I get:

192.168.0.2 26  26  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 443 443 /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 80  80  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 25  25  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39

If I SELECT acl, principal, privilege, is_grant, TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date FROM dba_network_acl_privileges; as SYS I get:

/sys/acls/http_permissions.xml  SCHEMA1 connect true    13-JAN-2017
/sys/acls/http_permissions.xml  SCHEMA1 resolve true    13-JAN-2017

If I select * from V$PARAMETER where NAME = 'smtp_out_server' as SYS I get;

3314   smtp_out_server 2   mail.example.com      mail.example.com  FALSE   TRUE    IMMEDIATE   TRUE    FALSE   FALSE   FALSE   FALSE   utl_smtp server and port configuration parameter        3948483925

If instead I use a proven mail package from 11.2.0.1 to send mail via UTL_SMTP in schema1, it fails with ORA-24247: network access denied by access control list (ACL) from both SQLDeveloper and function SCHEMA1.MY_FUNC. It fails on this line:

mail_conn := utl_smtp.open_connection('mail.example.com', 26);

Any idea what could be wrong?

UPDATE

If I replace mail.example.com with 192.168.0.2 when setting smtp_out_server so that select * from V$PARAMETER where NAME = 'smtp_out_server' returns

3314   smtp_out_server 2   192.168.0.2   192.168.0.2   FALSE   TRUE    IMMEDIATE   TRUE    FALSE   FALSE   FALSE   FALSE   utl_smtp server and port configuration parameter        3948483925

then the UTL_MAIL.SEND command from both SQLDeveloper worksheet and function give the error ORA-29279: SMTP permanent error: 550 Access denied - Invalid HELO name (See RFC2821 4.1.3).

Here's what I finally (!) got to work. Simply set smtp_out_server equal to mail.example.com, then add mail.example.com to the ACL using, from SQLDeveloper:

-- leave <lower|upper>_port null to allow access to all ports
exec dbms_network_acl_admin.assign_acl (acl => 'http_permissions.xml', host => '*.example.com');
commit;

Notice above that I used *.example.com instead of mail.example.com because doing it this way also allows UTL_HTTP requests to www.example.com (kill 2 birds with one stone). Also, by not specifying upper and lower port bounds, we enable access for all ports. If you want, you can specify the exact ports as done in the original posting above.

Here's the UTL_SMTP related code:

g_mail_conn := UTL_SMTP.OPEN_CONNECTION('mail.example.com', 26);
UTL_SMTP.EHLO(g_mail_conn, 'mail.example.com');
UTL_SMTP.MAIL(g_mail_conn, p_sender_email);
...

Note that I'm using port 26 for SMTP, whereas most of the world uses port 25 (so just update your code accordingly). Also, there's no authentication (e.g. username/password) for the non-SSL port (26; no need, since I'm using an internal IP address).

The UTL_MAIL command also works from SQLDeveloper:

exec utl_mail.send(sender => 'name1@example.com', recipients => 'name2@example.com', message => 'Hi there');


Postgres speed up index creation for large table I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever for the b-tree indexes to be created. For one of the runs that I cut short, it had spent >12 hours creating the indexes.

Sample table and indexes that I'm trying to make:

        Column         |            Type             | Modifiers
-----------------------+-----------------------------+-----------
 name                  | character varying           | not null
 id                    | character varying           |
 lifecycle_id          | character varying           |
 dt                    | character varying           |
 address               | character varying           |
 ...

Indexes:
"name_idx" PRIMARY KEY, btree (name)

"id_idx" btree (rec_id)

"lifecycle_id_idx" btree (lifecycle_id)

The actual table has 18 columns. I've set the maintenance_work_mem to 15GB. This is running on Postgres 9.6.11 on RDS. The instance class is db.m4.4xlarge.

Since there are three indexes, it would be hard to sort the data before inserting. Would it be faster to just insert the data without dropping the indexes? Any other suggestions for speeding up the index creation?

Before I posted this question, I had two tests running but I wasn't sure if they would ever finish. I was hoping to get some insight into how to speed it up if they never finished. Thankfully, one of the tests did finish. I also got good results by trying the suggestions that jjanes posted.

Using Postgres 9, I ran two tests:

Inserting the 2 billion entries with indexes
Building indexes for a table that already had the 2 billion entries inserted

The first test has been running for a week and has only inserted 20% of the 2 billion entries. Inserting with index is definitely not the way to go.

The second test took 45 hours. Without knowing how long index creation was going to take, I prematurely cut off some of the previous tests and never got to see them complete. It takes a long time but for now, I'm happy that it doesn't run on forever.

Taking jjanes suggestions, I upgraded to Postgres 11, set the parallel workers to 8 and set the maintenance work memory to 7GB. I followed this guide to pick the parameters: https://www.cybertec-postgresql.com/en/postgresql-parallel-create-index-for-better-performance/.
This sped up the index creation and it only took 35 hours. Shaving off 10 hours was a nice surprise. Thanks!


Propper way to monitor SQL Server mirror status from service or job? We have a production DB which is in a mirror configuration. It is in synchronous high safety with automatic failover configuration. Right now we monitor it using windows service which pings it. Is there a better way to monitor mirror state using job or script?

As stated in Monitoring Database Mirroring MSDN

You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures.

  A database mirroring monitoring job, Database Mirroring Monitor Job,
  operates in the background, independently of Database Mirroring
  Monitor. SQL Server Agent calls Database Mirroring Monitor Job at
  regular intervals, the default is once a minute, and the job calls a
  stored procedure that updates mirroring status. If you use SQL Server
  Management Studio to start a mirroring session, Database Mirroring
  Monitor Job is created automatically.

Also, if you want you can use the sys.database_mirroring view. You can query this Catalog View and raise an email alert for each mirrored database that we find in an abnormal state using below script as explained here

DECLARE @state VARCHAR(30)
DECLARE @DbMirrored INT
DECLARE @DbId INT
DECLARE @String VARCHAR(100)
DECLARE @databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERT @databases
SELECT database_id, mirroring_state_desc
FROM sys.database_mirroring
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')

-- iterate through mirrored databases and send email alert
WHILE EXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
SELECT TOP 1 @DbId = DBid, @State = mirroring_state_desc
FROM @databases
SET @string = 'Host: '+@@servername+'.'+CAST(DB_NAME(@DbId) AS VARCHAR)+ ' - DB Mirroring is '+@state +' - notify DBA'
EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@mssqltips.com', @body = @string, @subject = @string
DELETE FROM @databases WHERE DBid = @DbId
END

--also alert if there is no mirroring just in case there should be mirroring :)
SELECT @DbMirrored = COUNT(*)
FROM sys.database_mirroring
WHERE mirroring_state IS NOT NULL
IF @DbMirrored = 0
BEGIN
SET @string = 'Host: '+@@servername+' - No databases are mirrored on this server - notify DBA'
EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@mssqltips.com', @body = @string, @subject = @string
END


Unable to change Agent Operators I am trying to change a SQL Agent Operator in my system and it will not let me.

I have sa but I think it's partially due to how I'm doing it.

I'm trying to modify the operator with the name NIT and Arts to NIT and ARTS and when I do that it comes back with the error of "The specified @name (NIT and ARTS) already exists even though it doesn't currently.

I'm running on SQL Server 2008R2. Any help with this would be great. Thanks.

On a case insensitive server, those two names are identical. I am not sure if you are using the UI for this, but I get the same error when I run the following code:

USE msdb;
GO
EXEC dbo.sp_add_operator @name = N'NIT and Arts';
GO
EXEC dbo.sp_update_operator @name = N'NIT and Arts', @new_name = N'NIT and ARTS';

Yields:

  Msg 14261, Level 16, State 1, Procedure sp_verify_operator, Line 38
  The specified @name ('NIT and ARTS') already exists.

If we look at the code for sp_verify_operator, we see that there is a weakness here as a penalty for not using a surrogate key: it checks all existing rows for a match on name, including the row you are trying to change:

  -- The name must be unique
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysoperators
              WHERE (name = @name)))
  BEGIN
    RAISERROR(14261, 16, 1, '@name', @name)

Ideally, it should ignore the row you are trying to change, but it can't because there is no way to filter except by the name. And in a case sensitive collation, the above check fails because that row already has that name.

How do we work around this? Well you could rebuild msdb or something in a case sensitive or binary collation, but I think this is easier, just run this query manually:

UPDATE msdb.dbo.sysoperators
  SET name = N'NIT and ARTS'
  WHERE name = N'NIT and Arts';


I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR)
If they do require CLR, is there an alternative I can use for my stored procedure parameter that serves the same functionality?

User-Defined Table Types are a TSQL feature, not requiring CLR to be enabled.  Nor are they CLR types "under the covers".  eg
exec sp_configure 'clr enabled', 0
reconfigure with override

go

create type tableType as table(id int)

go
declare @t tableType
insert into @t(id) values (1)

select * from @t

Then to show it's not a CLR type "under the covers"  run
select * from sys.dm_clr_appdomains
go
declare @t hierarchyid =  hierarchyid::Parse('/1/1/3/')
go
select * from sys.dm_clr_appdomains

to see how there's an AppDomain created when you create a hierarchyid but not a User-Defined Table Type.


Most efficient way to add a serial column to a huge table What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)?

EDIT:

I want the column to be incremented values for existing rows (NOT NULL).
I didn't set a fillfactor (which looks like a bad decision in retrospect).
I don't have a problem with disk space, just want it to be as fast as possible.

What's wrong with:
ALTER TABLE foo ADD column bar bigserial;

Will be filled with unique values automatically (starting with 1).
If you want a number for every existing row, every row in the table has to be updated. Or do you not?
The table will be bloated to twice it's size if it cannot reuse dead tuples or free space on the data pages. Performance of the operation might benefit a lot from a FILLFACTOR lower than 100 or just random dead tuples spread out over the table. Else you may want to run VACUUM FULL ANALYZE afterwards to recover disk space. This won't be quick, though.
pgstattuple
You may be interested in this extension. It helps you gather statistics on your tables. To find out about dead tuples and free space:
Install extension once per databae:
CREATE EXTENSION pgstattuple;

Call:
SELECT * FROM pgstattuple('tbl');

Alternative
If you can afford to create a new table, which would break depending views, foreign keys, ...
Create an empty copy of the old table:
CREATE new_tbl AS
SELECT *
FROM   old_tbl
LIMIT  0;

Add the bigserial column:
ALTER new_tbl ADD column bar bigserial;

INSERT data from old table, automatically filling the bigserial:
INSERT INTO new_tbl
SELECT *    --  new column will be filled with default
FROM   old_tbl
ORDER  BY something; -- or don't order if you don't care: faster

The new bigserial column is missing in the SELECT of the INSERT and will be filled with its default value automatically. You can spell out all columns and add nextval() to the SELECT list to the same effect.
Make sure you got all your data in the new table.
Add indexes, constraints, triggers you had in the old table now.
DROP TABLE old_tbl;
ALTER TABLE new_tbl RENAME TO old_tbl;

Might be quite a bit faster overall. This leaves you with a vanilla table (and indexes) without any bloat.
You need free disk space - around the size of the old table, depending on the state of the table - as wiggle room. But you may need just as much with the first simple method because of table bloat. Again, the details depend on the state of your table.


Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries? I am running MySQL 5.5.37.  I have a cronjob set to run every 6 hours to flush the query cache.  I realized that if I don't do that, then the cache becomes fragmented and average query response time could double or triple.  95% of my data is saved in MyISAM tables.

My average response time is between 15ms to 30ms as long as I have the cronjob run.

Is this normal behavior?  Or does it indicate that I have an underlying issue that should be fixed?  Perhaps my query_cache_min_res_unit value is too low, which is causing fragmentation?  Or is fragmentation unavoidable and flushing the cache is perfectly normal?

QUERY CACHE
Query cache is enabled
Current query_cache_size = 4.08 G
Current query_cache_used = 1.05 G
Current query_cache_limit = 3.24 G
Current Query cache Memory fill ratio = 25.79 %
Current query_cache_min_res_unit = 4 K

Flushing the query cache would be OK depending on Storage Engine

InnoDB spends time micromanaging data changes. This includes crosschecking the query cache.

I have a 2-year-old post (Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?) where I recommend running FLUSH QUERY CACHE via the crontab.

I also have more recent posts

Jun 07, 2014 : Why query_cache_type is disabled by default start from MySQL 5.6?
Jun 30, 2014 : Optimize MySQL query_cache_min_res_unit

explaining how one could try to set query_cache_size and query_cache_min_res_unit if one truly knows the data, the average size of a result set, and how many entries should be in the query cache.

Other than having this foreknowledge about the data, fragmentation is unavoidable.

On the flipside, if all your data is MyISAM, you should take the time to set set query_cache_size and query_cache_min_res_unit by getting to know your data. After all, MyISAM does not cache data. It only caches indexes.

EPILOGUE

Regardless of Storage Engine, running FLUSH QUERY CACHE; bypasses the need to set query_cache_size and query_cache_min_res_unit. It quickly eliminates fragmentation issues. If your dataset is not very large, make the time to size your data and result sets. You could eliminate the crontab.

What would be an even greater advantage would be the following

tune the queries
add proper indexes to support queries
tune MyISAM

If the MyISAM tables have VARCHAR columns, change row format to FIXED.
Make sure the key_buffer_size can accommodate all MyISAM indexes (See What are the main differences between InnoDB and MyISAM?)


no more data read from socket error with triggers I have created one trigger that calls one procedure, using oracle sql developper. Here is the code for trigger :

CREATE OR REPLACE TRIGGER noteTrigger
  BEFORE
    INSERT OR
    UPDATE OF valoare OR
    DELETE
  ON note
BEGIN
  CASE
    WHEN INSERTING THEN
      DBMS_OUTPUT.PUT_LINE('Inserting');
      updateBursa();
    WHEN UPDATING('valoare') THEN
      DBMS_OUTPUT.PUT_LINE('Updating valoare');
      updateBursa();
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
      updateBursa();
  END CASE;
END;
/

Now, the procedure is written here :

  CREATE OR REPLACE PROCEDURE updateBursa IS
v_countBursieri NUMBER := 0;
BEGIN
  UPDATE STUDENTI SET bursa = null;
  FOR v_i IN (SELECT nr_matricol from studenti natural join note group by nr_matricol having avg(valoare) =
          (select max(avg(valoare)) from studenti natural join note group by nr_matricol)) LOOP
          v_countBursieri := v_countBursieri + 1;
  END LOOP;
  FOR v_i IN (SELECT nr_matricol from studenti natural join note group by nr_matricol having avg(valoare) =
          (select max(avg(valoare)) from studenti natural join note group by nr_matricol)) LOOP

          UPDATE STUDENTI SET bursa = 1000/v_countBursieri where nr_matricol = v_i.nr_matricol;
  END LOOP;

END;
/

And when I try to modify note table by this :

INSERT INTO note VALUES ('111', '25',  5, TO_DATE('20/06/2015', 'dd/mm/yyyy'));

I get error :

Error report -
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An Oracle server session was in an unrecoverable state.
*Action:   Log in to Oracle again so a new server session will be created
           automatically.  Examine the session trace file for more
           information.
Error report -
SQL Error: No more data to read from socket

Please help me! Thank you!

This Error can happen with PL/SQL and came up with ORA-600 [kqlidchg0],ORA-00001 and/or ORA-01578 see MOS Doc ID 17210525.8.

For 12.1 and 11.2 install latest Patch (April 2016)

ORA-600 [kqlidchg0] with PL/SQL

ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

Try:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';


SQL Server - Why am I getting TCP connections without sessions? I enabled SSL with Forced Encryption enabled, using a self-signed certificate.
Then I've set up automated monitoring to detect not secured connections (seems redundant, I know, but bear with me here), using the following T-SQL query:
SELECT CONCAT('Not secured connection(s) detected of '
, ISNULL(QUOTENAME(COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)), 'an unknown login')
, ' from ', ISNULL(QUOTENAME(client_net_address), 'an unknown address')
, ' ', QUOTENAME(ISNULL(ses.host_name, 'unknown host'), '(')
, ', ', ISNULL(QUOTENAME(ses.program_name), 'unknown program')
, ', to ', ISNULL(QUOTENAME(DB_NAME(ses.database_id)), 'an unknown database')
, ', Session(s): ', COUNT(ses.session_id)
), COUNT(con.connection_id) AS NumberOfConnections
FROM sys.dm_exec_connections AS con
LEFT JOIN sys.dm_exec_sessions AS ses
ON ses.session_id IN (con.session_id, con.most_recent_session_id)
WHERE encrypt_option = 'FALSE'
AND net_transport = 'TCP'
AND client_net_address NOT LIKE '<%'
GROUP BY COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)
, client_net_address, ses.host_name, ses.program_name, ses.database_id

Later, I started getting alerts resulting from this query, with output that looks like this:
Not secured connection(s) detected of an unknown login from [x.x.x.x] (unknown host), unknown program, to an unknown database: 0
(the x.x.x.x replaces actual IP addresses that came from one of our customer's networks)
This means that we're detecting TCP connections (in sys.dm_exec_connections) without corresponding sessions (in sys.dm_exec_sessions).
Based on what I found in Microsoft documentation, these scenarios could happen as part of Availability Group, Database Mirroring, or Service Broker architectures... But we're not using any of those in our instance!
This should not be happening... Does anyone have any idea how to explain this? Could this happen due to some kind of port scanner perhaps?
Thanks!
Version details:
Microsoft SQL Server 2019 [Enterprise] (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
    Mar 14 2020 16:10:35
    Copyright (C) 2019 Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

I'll assume you've restarted SQL Server after enabling the force encryption option.
In addition to the reasons you mentioned in your question, you may see a connection without an associated session if your run the query while a client is in the process of connecting. The encrypt_option will show FALSE immediately after the physical connection is established but change to TRUE after the client sends a TDS PRELOGIN message as part of the handshake process. The session will be created once the login process completes.
You can filter out in-progress login noise in your monitoring query either by using an inner join instead of outer join to sys.dm_exec_sessions or by adding AND con.protocol_version > 0.


deleted postgres files on ubuntu I was cleaning out some files in /home/postgres, and accidentally deleted the directory named 10.  Now nothing works.  Here is the error I get when I try to run psql as the postgres user:
    psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What did I break, and how do I fix it?
Other info:
-This is a scratch database, so I am not very concerned about recovering old stored databases.  However, it would be easier for me if I could.
-I thought about doing an uninstall/reinstall using apt-get, but since things are in an unstable state, I thought it would be better to get some advice first.
-I am running postgres 10 on Ubuntu 18

That depends on what you deleted: was it the database data or the database software?
Since psql still works, I suspect that the database software is still there, and you deleted the data. Then the only way to recover is to restore from a backup.
No, there is no way to "undelete" data.


An entitiy with at least one weak entity I have the following design:

There is a list of parents in my database. A parent should have at least one child,  and a child must belong to a parent (forget about the husband/wife relations).
(a child is a weak entity)

(No need to add rules for deletion/ update)

How can I achieve this?

I write SQL but I have the problems:

Create Table Parent(
  parentName Varchar(255),
  -- Should a parent have an entity named child?
  -- But a parent can have more than one child
  PRIMARY KEY(parentName)
);

Create Table Child(
 parentName varchar(255),
 name varchar(255),
 PRIMARY KEY(parentName, name),
 FOREIGN KEY (parentName) References Parent(parentName));

I'm assuming that I have the following error:

A parent should not exist without a child. However, I could not manage to maintain this.

As an example

parent 1, child1

parent 1, child2

parent 2, child2 --Not allowed!

parent 3, null  -- Not allowed!

null, child 4 -- Not allowed!

Also, I need a child for other relations as well. So, in my opinion, combining child entity with a relation in a table is not a good solution for me.

As was mentioned in the comments, there could be name duplicates. It's better to have an id column in each table.

Second, adding NOT NULL constraint on the foreign key will enforce the rule that each child can have only one parent

Create Table Parent(
  id int NOT NULL UNIQUE,
  parentName Varchar(255),
  -- Should a parent have an entity named child? -- No.
  -- But a parent can have more than one child
  PRIMARY KEY(id)
);

Create Table Child(
 id int NOT NULL UNIQUE,
 parent_id int NOT NULL, -- NOT NULL - each child has a parent
 name varchar(255),
 PRIMARY KEY(id),
 FOREIGN KEY (parent_id) References Parent(id)
);

There's only one parent_id field, so each child has no more than one parent

Because each child can have only one parent (one-to-many) relationship, the two-table solution works.

When you have many-to-many relationship, you need a relationship table that contains foreign keys to both tables that represent entities in this relationship.

You might also want to add another UNIQUE to (parent_id, name) combination, to prevent one parent have multiple children with the same name. (Though, I have heard that some celebrity named all his sons "George")

You can't really enforce that each parent has at least one child, because then you wouldn't be able to add new parents to your database. Current process is:

insert a parent
insert that parent's children

You can't insert children first, because the constraints will prevent the insert of a child without a parent already existing.


Calculating stock quantity based on change log Imagine that you have the following table structure:

LogId | ProductId | FromPositionId | ToPositionId | Date                 | Quantity
-----------------------------------------------------------------------------------
1     | 123       | 0              | 10002        | 2018-01-01 08:10:22  | 5
2     | 123       | 0              | 10003        | 2018-01-03 15:15:10  | 9
3     | 123       | 10002          | 10004        | 2018-01-07 21:08:56  | 3
4     | 123       | 10004          | 0            | 2018-02-09 10:03:23  | 1

FromPositionId and ToPositionId are stock positions.
Some position ID:s have special meaning, for example 0. An event from or to 0 means that stock was created or removed. From 0 could be stock from a delivery and to 0 could be a shipped order.

This table currently holds around 5.5 million rows. We calculate the stock value for each product and position into a cache table on a schedule using a query that looks something like this:

WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0

Even though this completes in a reasonable amount of time (around 20 seconds), I feel like this is a pretty inefficient way of calculating the stock values.
We rarely do anything but INSERT:s in this table, but sometimes we go in and adjust the quantity or remove a row manually due to mistakes by the people generating these rows.

I had an idea of creating "checkpoints" in a separate table, calculating the value up to a specific point in time and using that as a start value when creating our stock quantity cache table:

ProductId | PositionId | Date                | Quantity
-------------------------------------------------------
123       | 10002      | 2018-01-07 21:08:56 | 2

The fact that we sometimes change rows poses a problem to this, in that case we must also remember to remove any checkpoint created after the log row we changed. This could be solved by not calculating the checkpoints up until now, but leave a month between now and the last checkpoint (we very very rarely make changes that far back).

The fact that we sometimes need to change rows are hard to avoid and I would like to be able to still do this, it's not shown in this structure but the log events are sometimes tied to other records in other tables, and adding another log row to get the right quantity is sometimes not possible.

The log table is, as you can imagine, growing pretty fast and the time to calculate will only increase with time.

So to my question, how would you solve this? Is there a more efficient way of calculating the current stock value? Is my idea of checkpoints a good one?

We're running SQL Server 2014 Web (12.0.5511)

Execution plan: https://www.brentozar.com/pastetheplan/?id=Bk8gyc68Q

I actually gave the wrong execution time above, 20s was the time that the complete update of the cache took. This query takes somewhere around 6-10 seconds to run (8 seconds when I created this query plan). There's also a join in this query that was not in the original question.

Sometimes you can improve query performance just by doing a little bit of tuning instead of changing your entire query. I noticed in your actual query plan that your query spills to tempdb in three places. Here's one example:

Resolving those tempdb spills may improve performance. If Quantity is always non-negative then you can replace UNION with UNION ALL which will likely change the hash union operator to something else that doesn't require a memory grant. Your other tempdb spills are caused by issues with cardinality estimation. You're on SQL Server 2014 and using the new CE so it may be difficult to improve the cardinality estimates because the query optimizer won't use multi-column statistics. As a quick fix, consider using the MIN_MEMORY_GRANT query hint made available in SQL Server 2014 SP2. Your query's memory grant is only 49104 KB and the max available grant is 5054840 KB so hopefully bumping it up won't impact concurrency too much. 10% is a reasonable starting guess but you may need to adjust it up and done depending on your hardware and data. Putting that all together, this is what your query might look like:

WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION ALL
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0
OPTION (MIN_GRANT_PERCENT = 10);

If you wish to improve performance further I recommend trying out indexed views instead of building and maintaining your own checkpoint table. Indexed views are significantly easier to get right than a custom solution involving your own materialized table or triggers. They will add a small amount of overhead to all DML operations but it may allow you to remove some of the nonclustered indexes that you currently have. Indexed views appear to be supported in the web edition of the product.

There are some restrictions on indexed views so you'll need to create a pair of them. Below is an example implementation, along with the fake data that I used for testing:

CREATE TABLE dbo.ProductPositionLog (
    LogId BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    FromPositionId BIGINT NOT NULL,
    ToPositionId BIGINT NOT NULL,
    Quantity INT NOT NULL,
    FILLER VARCHAR(20),
    PRIMARY KEY (LogId)
);

INSERT INTO dbo.ProductPositionLog WITH (TABLOCK)
SELECT RN, RN % 100, RN % 3999, 3998 - (RN % 3999), RN % 10, REPLICATE('Z', 20)
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

CREATE INDEX NCI1 ON dbo.ProductPositionLog (ToPositionId, ProductId) INCLUDE (Quantity);
CREATE INDEX NCI2 ON dbo.ProductPositionLog (FromPositionId, ProductId) INCLUDE (Quantity);

GO

CREATE VIEW ProductPositionLog_1
WITH SCHEMABINDING
AS
   SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
    FROM dbo.ProductPositionLog
    WHERE ToPositionId <> 0
    GROUP BY ToPositionId, ProductId
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
    ON ProductPositionLog_1 (PositionId, ProductId);
GO

CREATE VIEW ProductPositionLog_2
WITH SCHEMABINDING
AS
   SELECT FromPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
    FROM dbo.ProductPositionLog
    WHERE FromPositionId <> 0
    GROUP BY FromPositionId, ProductId
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V2
    ON ProductPositionLog_2 (PositionId, ProductId);
GO

Without the indexed views the query takes about 2.7 seconds to finish on my machine. I get a similar plan to yours except mine runs in serial:

I believe that you'll need to query the indexed views with the NOEXPAND hint because you aren't on enterprise edition. Here's one way to do that:

WITH t AS
(
    SELECT PositionId, Quantity, ProductId
    FROM ProductPositionLog_1 WITH (NOEXPAND)
    UNION ALL
    SELECT PositionId, Quantity, ProductId
    FROM ProductPositionLog_2 WITH (NOEXPAND)
)
SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0;

This query has a simpler plan and finishes in under 400 ms on my machine:

The best part is that you won't have to change any of the application code that loads data into the ProductPositionLog table. You simply need to verify that the DML overhead of the pair of indexed views is acceptable.


Comparison of SQL Server with MongoDB As first sorry if I'm wrong with this here. For forwarding to the Right Place I'm grateful.

To my issue: I'm in an internship as a programmer, no real experience with databases, and as I'm open-minded accepted to compare Microsoft SQL Server with MongoDB if there would be benefits for the company to change to it.

For the original data I've selected the kinda biggest 3 tables.

Table: Article with over ~1,2 Mrd records

x-----------------------------x
| article_id|  suppID | ...   |
x-----------|---------|-------x
|    1      |     5   | ...   |
|    2      |     6   | ...   |
|    3      |     7   | ...   |
x-----------------------------x

Table: Price with over ~2,3 Mrd records

x-----------------------------x
| price_id  |ArticleID| ...   |
x-----------|---------|-------x
|    1      |     1   | ...   |
|    2      |     2   | ...   |
|    3      |     3   | ...   |
x-----------------------------x

Table: Price_Archive with over ~5,8 Mrd records

x-----------------------------x
| article_id|  country| ...   |
x-----------|---------|-------x
|    1      |     DE  | ...   |
|    2      |     EN  | ...   |
|    3      |     EN  | ...   |
x-----------------------------x

What are the main differences of SQL Server and NoSQL did I understand. But now more for the Technical Tests how can I achieve a comparison?

As for migration I do a simple Copy Paste and use the $lookup for some test scenarios. Furthermore creating a main single collection (Articles) and integrate the other 2 Tables in it, as its wanted in MongoDB-Document-Oriented.

Are there free tools where can log how much memory/time and so on is used for queries? Which other cases should I check?

SQL and NoSQL are different in many ways but the gist of it is that SQL is better at relational and NoSQL is better at non-relational. Each technology is suited for different purposes.

There are plenty of existing benchmarks and comparisons between the two such as here, here and here. Be wary of the bias in comparison articles and note that when someone says NoSQL is faster it could mean with less reliability.

To benchmark SQL queries you can look up the following:

SET STATISTICS TIME ON

Or use something similar to this:

DECLARE @DateTimeStamp DATETIME
SET @DateTimeStamp = current_timestamp
... --do some stuff to measure here
PRINT CAST(DATEDIFF(ms, @DateTimeStamp, current_timestamp) AS VARCHAR) + 'ms'

To benchmark MongoDB queries you can look up the following:

.explain("executionStats")
mongodb executionTimeMillis

Here are some comparisons between the two:

SQL

More expensive initial cost
Relational
Better data integrity with transactions ( more overhead )
More common so more developers have experience with it
More proven security
Paid tools for metrics and monitoring such as Solar Winds and Idera
There are likely free tools but I am not aware of them

NoSQL ( MongoDB )

Less expensive initial cost
Non-relational
No transactions ( less overhead )
Asynchronous INSERT and UPDATE
Depending on Write Concern Values it could be faster with less reliability or slower with more reliability
Possibly more scalable
Less proven security
Paid tools for MongoDB server metrics and monitoring such as Server Density.


Index manteinance: does the order of processing make a difference? I have an 800GB SQL Server Enterprise database with very bad performance. So, based on a few scripts I found online I'm creating a job on my SQL database to maintain our indexes. Most of my tables have a clustered index and 3 or 4 non-clustered.

Does it make any difference the order in which the indexes are being
rebuild/reorganized?
Is it the same if I first reorganize the clustered index and then the
nonclustered ones or if I do it the other way?

Does it make any difference the order in which the indexes are being
  rebuild/reorganized?

As commented by @Dan Guzman it does not matter. But if your rebuilding and  free disk space is limited I suggest you start from small to bigger ones (among the one qualify based on whatever criteria you are using).  Reason explained here:

Disk Space Requirements for Index DDL Operations.

Very good tips for other considerations.

Should You Rebuild or Reorganize Indexes on Large Tables? (Dear SQL DBA Episode 19) by Kendra Little.

  Is it the same if I first reorganize the clustered index and then the
  nonclustered ones or if I do it the other way?

Yes it same because the clustered keys, which are the record locators in the NC indexes, aren’t changed because you rebuild the index.


Memory Usage by SQL Server How do i check memory usage by my SQL server in production box.
I am using SQL Server 2016.When ever i check task manager,it shows above 90%.
I don't think that is the real memory usage by sql server.

I have a SQL performance tool grafana which shows CPU usage very less than what i see in task manager.
I checked Resource Monitor,there is can see Average CPU value.I am confused as to which is the SQL server memory usage.
I am trying to determine if memory pressure is an issue to some of my problem.

Can someone direct to a good/proper explanation.

'I am trying to determine if memory pressure is an issue to some of my problem.'

very useful script:
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql

you see verbose memory utilization:

https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-november-2017/
SQL Server 2017 Diagnostic Information Queries:
see comment

-- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 46) (PLE by NUMA Node)
SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
------

-- PLE is a good measurement of internal memory pressure
-- Higher PLE is better. Watch the trend over time, not the absolute value

(Query 14)

-- Good basic information about OS memory amounts and state  (Query 14) (System Memory)
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],
       available_physical_memory_kb/1024 AS [Available Memory (MB)],
       total_page_file_kb/1024 AS [Total Page File (MB)],
       available_page_file_kb/1024 AS [Available Page File (MB)],
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You want to see "Available physical memory is high" for System Memory State
-- This indicates that you are not under external memory pressure

-- Possible System Memory State values:
-- Available physical memory is high
-- Physical memory usage is steady
-- Available physical memory is low
-- Available physical memory is running low
-- Physical memory state is transitioning

(47)

-- Memory Grants Pending value for current instance  (Query 47) (Memory Grants Pending)
SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
------

-- Run multiple times, and run periodically if you suspect you are under memory pressure
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of internal memory pressure

(62)

-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure  (Query 62) (SP Logical Reads)
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure

(63)

-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure  (Query 63) (SP Physical Reads)
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,
qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

(64)

-- Top Cached SPs By Total Logical Writes (Query 64) (SP Logical Writes)
-- Logical writes relate to both memory and disk I/O pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_logical_writes > 0
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

P.S. this is not an exhaustive answer


1054 - Unknown column in subquery i have 2 table one store data of relation user other buy user

path table

+-------------+---------------+-------------+
| ancestor_id | descendant_id | lenght       |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           1 |
|           1 |             4 |           2 |
|           1 |             5 |           3 |
|           1 |             6 |           4 |
|           2 |             2 |           0 |
|           2 |             4 |           1 |
|           2 |             5 |           2 |
|           2 |             6 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           4 |             5 |           1 |
|           4 |             6 |           2 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           6 |             6 |           0 |
+-------------+---------------+-------------+

this is table buy

+--------+--------+
| userid | amount |
+--------+--------+
|      2 |   2000 |
|      4 |   6000 |
|      6 |   7000 |
|      1 |   7000 |

algoritm =
1- select and group buy for each user at last 1000 for descendant
2- join to path table with descendant_id = result step1
3- again select and group buy for each user at last 1000 for ancestor
4- must get  min path_length Except zero According to join in step 2 with ancestor have at last 1000 , aslo if just have one path_length set to null

this mysql code i need a where compare path_length with have ancestor

SELECT a.*
FROM
    ( SELECT userid
       FROM webineh_user_buys
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_d

JOIN
    webineh_prefix_nodes_paths AS a
    ON a.descendant_id = buys_d.userid

 JOIN
    (
        SELECT userid
        FROM webineh_user_buys
        GROUP BY userid
        HAVING SUM(amount) >= 1000
    ) AS buys_a on (a.ancestor_id = buys_a.userid )

  JOIN
     ( SELECT descendant_id
            , MAX(path_length) path_length
         FROM webineh_prefix_nodes_paths
         where a.ancestor_id = ancestor_id
        GROUP
           BY descendant_id
     ) b
    ON b.descendant_id = a.descendant_id
   AND b.path_length = a.path_length

 GROUP BY a.descendant_id, a.ancestor_id

Intended result

 +--------+--------++--------++------
| descendant | ancestor | path_length
+--------+--------++--------++-------
|     1      |   null   |    null
|     2      |   1      |      1
|     4      |   2      |      1
|     6      |   4      |      2

but have error please see sqlfidle
Unknown column 'a.ancestor_id' in 'where clause'

Try this

SELECT a.descendant_id
    , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id
    , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length
FROM webineh_prefix_nodes_paths AS a
JOIN (
    SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path
    FROM (
        SELECT a.*
        FROM (
            SELECT userid
            FROM webineh_user_buys
            GROUP BY userid
            HAVING SUM(amount) >= 1000
            ) AS buys_d
        JOIN
            webineh_prefix_nodes_paths AS a
        ON a.descendant_id = buys_d.userid
        JOIN (
            SELECT userid
            FROM webineh_user_buys
            GROUP BY userid
            HAVING SUM(amount) >= 1000
            ) AS buys_a
        ON a.ancestor_id = buys_a.userid
        ) tmp2
    GROUP BY descendant_id
    ) td
ON a.descendant_id = td.descendant_id
       AND a.path_length = CASE td.min_path WHEN 1000 THEN 0 ELSE td.min_path END ;


MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE in parallel, like this:

UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%'
UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
UPDATE lpr SET gate_id=3 WHERE gate_id IS null AND gate LIKE '%[3]%'
UPDATE lpr SET gate_id=4 WHERE gate_id IS null AND gate LIKE '%[4]%'
...

since the rows affected by all the UPDATEs are disjoint, and the table is on InnoDB, we wouldn't expect lock contention, but we get the error:

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

for all the UPDATEs. Increasing innodb_lock_wait_timeout to ~100 doesn't change anything, as well as setting FOREIGN_KEY_CHECKS to 0.

The gate column never contains more than one [VALUE] string.

If we do a single UPDATE by hand, no such error occurs.

Doing a SHOW FULL PROCESSLIST doesn't show any lock during the UPDATEs.

What are we doing wrong? Is there a table-level lock even if using InnoDB?
Or do the UPDATEs lock more rows than those strictly selected by the WHERE clause?

What are we doing wrong? Is there a table-level lock even if using InnoDB?

Your problem is the lack of a proper index to use. InnoDB does next-key locking, meaning it will lock only the rows that it will update, but also the gaps in-between using the look up index. Because no proper index can be used for the given filter gate LIKE '%[1]%' it doesn't technically do a table lock, but it setups a lock on every single row gap according to the query plan (locking all rows).

I've recreated your structure, and SHOW ENGINE INNODB STATUS gives us all the information we need:

mysql> create table lpr (gate_id int, gate varchar(10));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into lpr values (1, 'wqer[1]sd');
Query OK, 1 row affected (0.11 sec)

mysql> insert into lpr values (2, 'wqer[2]sd');
Query OK, 1 row affected (0.06 sec)

mysql> insert into lpr values (3, 'wqer[2]sd');
Query OK, 1 row affected (0.03 sec)

mysql> insert into lpr values (4, 'wqer[4]sd');
Query OK, 1 row affected (0.05 sec)

mysql> insert into lpr values (5, 'wqer[5]sd');
Query OK, 1 row affected (0.09 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SHOW ENGINE INNODB STATUS\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4126, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 8, OS thread handle 140628280551168, query id 21 localhost root starting
SHOW ENGINE INNODB STATUS

On another session:

mysql> UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
    -> ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 4127, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9, OS thread handle 140628236515072, query id 28 localhost root updating
UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `enwiki`.`lpr` trx id 4127 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000001011; asc       ;;
 2: len 7; hex 82000001060110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 9; hex 777165725b315d7364; asc wqer[1]sd;;

You have several options:

Increase your concurrency by relaxing your transaction isolation level (but ghost reads can happen):

mysql> SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

On another session, now the session succeeds:

mysql> SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
-> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Improve your design and/or queries so you only use properly indexed queries for improved concurrency.

More about concurrency and gap locking: https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/


How to grant access to a specific table across all databases in Mysql? I want to Grant access like so:
> GRANT INSERT, UPDATE ON `%`.my_table TO 'user'@'%';
ERROR 1146 (42S02): Table '%.my_table' doesn't exist

This is not possible per Mysql documentation on grant:

The _ and % wildcards are permitted when specifying database names in
GRANT statements that grant privileges at the database level

And nothing is mentioned for table level access.
I found similar questions on stackexchange, but they have different use cases. There is Mysql Bug from 2017 that seems to suggest a fix for this specific use case:

Accept statements which have a wildcard in the database name and a
fixed table name.

Is there a workaround?

You will have to script it in a stored procedure.
here is such a script
CREATE DATABASE IF NOT EXISTS grantor;
USE grantor
DELIMITER $$
DROP PROCEDURE IF EXISTS grant_table $$
CREATE PROCEDURE grant_table
(
    tb VARCHAR(64)
   ,userhost VARCHAR(128)
   ,grantlist VARCHAR(255)
)
BEGIN
    DROP TABLE IF EXISTS DBLIST;
    CREATE TABLE DBLIST
    (
        id INT NOT NULL AUTO_INCREMENT,
        db VARCHAR(64),
        PRIMARY KEY (id)
    ) ENGINE=MEMORY;
    INSERT INTO DBLIST (db)
    SELECT table_schema FROM information_schema.tables WHERE
    table_schema NOT IN
    ('information_schema','performance_schema','mysql','sys')
    AND table_name = tb;
    SELECT MAX(id) INTO @rcount FROM DBLIST;
    SELECT @rcount;
    SET @x = 0;
    WHILE @x < @rcount DO
        SET @x = @x + 1;
        SELECT CONCAT('GRANT ',grantlist,' ON `',db,'`.',tb,' TO ',userhost)
        INTO @sql
        FROM DBLIST WHERE id = @x;
        SELECT @sql;
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$
DELIMITER ;

Here is a sample call
CREATE TABLE tryout.thistable (id INT NOT NULL PRIMARY KEY,name VARCHAR(20));
CREATE TABLE DMP492.thistable LIKE tryout.thistable;
CREATE TABLE DMP551.thistable LIKE tryout.thistable;
CREATE TABLE DMP579.thistable LIKE tryout.thistable;
CREATE TABLE DMP756.thistable LIKE tryout.thistable;
CALL grantor.grant_table('thistable','ptqd@''%''','INSERT,SELECT');

Here was a sample test I did
MySQL://localhost/root/mysqld.sock/grantor> CREATE DATABASE IF NOT EXISTS grantor;
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> USE grantor
Database changed
MySQL://localhost/root/mysqld.sock/grantor> DELIMITER $$
MySQL://localhost/root/mysqld.sock/grantor> DROP PROCEDURE IF EXISTS grant_table $$
Query OK, 0 rows affected (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> CREATE PROCEDURE grant_table
    -> (
    ->     tb VARCHAR(64)
    ->    ,userhost VARCHAR(128)
    ->    ,grantlist VARCHAR(255)
    -> )
    -> BEGIN
    ->     DROP TABLE IF EXISTS DBLIST;
    ->     CREATE TABLE DBLIST
    ->     (
    ->         id INT NOT NULL AUTO_INCREMENT,
    ->         db VARCHAR(64),
    ->         PRIMARY KEY (id)
    ->     ) ENGINE=MEMORY;
    ->     INSERT INTO DBLIST (db)
    ->     SELECT table_schema FROM information_schema.tables WHERE
    ->     table_schema NOT IN
    ->     ('information_schema','performance_schema','mysql','sys')
    ->     AND table_name = tb;
    ->     SELECT MAX(id) INTO @rcount FROM DBLIST;
    ->     SELECT @rcount;
    ->     SET @x = 0;
    ->     WHILE @x < @rcount DO
    ->         SET @x = @x + 1;
    ->         SELECT CONCAT('GRANT ',grantlist,' ON `',db,'`.',tb,' TO ',userhost)
    ->         INTO @sql
    ->         FROM DBLIST WHERE id = @x;
    ->         SELECT @sql;
    ->         PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    ->     END WHILE;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor> DELIMITER ;
MySQL://localhost/root/mysqld.sock/grantor> CALL grantor.grant_table('thistable','ptqd@''%''','INSERT,SELECT');
+---------+
| @rcount |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP492`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.02 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP551`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.02 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP579`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `DMP756`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

+-------------------------------------------------------+
| @sql                                                  |
+-------------------------------------------------------+
| GRANT INSERT,SELECT ON `tryout`.thistable TO ptqd@'%' |
+-------------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

MySQL://localhost/root/mysqld.sock/grantor> show grants for ptqd@'%';
+------------------------------------------------------------+
| Grants for ptqd@%                                          |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ptqd'@'%'                           |
| GRANT SELECT, INSERT ON `DMP492`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `tryout`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP756`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP551`.`thistable` TO 'ptqd'@'%' |
| GRANT SELECT, INSERT ON `DMP579`.`thistable` TO 'ptqd'@'%' |
+------------------------------------------------------------+
6 rows in set (0.00 sec)

MySQL://localhost/root/mysqld.sock/grantor>

GIVE IT A TRY !!!


Connecting to a SQL server whose IP changes I want to connect to a server whose IP is dynamic via a VB.net application. I have configured the connection string in my VB application to a static IP.
I also gave a static IP to the database.

However, this work-around can be dangerous. Can someone please suggest an efficient way to connect to a SQL server in a remote machine whose IP is dynamic.

The most efficient way to connect to a server is to use it's hostname and not an IP address. The local DHCP should be updating the DNS records so connecting to a hostname should always work, if not talk to the people configuring your network.


How inaccurate is sys.dm_db_partition_stats? I am looking at ways to count rows in several tables on several databases.  In this particular case I am looking for unused and abandoned items.

Several sources source1, source2 recommend using sys.dm_db_partition_stats everything agrees that the count is not perfectly accurate.  MSDN describes row_count as

  approximate number of rows in the partition.

I understand if the value is always changing, it might be off by a bit. There are some good arguments on why this small discrepancy really doesn't matter.  But if the value is near zero or none, will it be accurate?

If I use sys.dm_db_partition_stats and I am getting no rows found, do I need to run a second query using COUNT (or something else) to make sure the table is really empty or is the inaccuracy limited to minor fluctuations over minor periods of time?

Clarify
I don't own the data, so I am ethically prohibited from looking at it. If sys.dm_db_partition_stats tells me a table is empty I can't just go open it and see if it really is empty. I need to know if I can depend on the results returned from it.  As pointed out in answers, more information than just this will be required, before deleting.  The table may be used as a temp table, maybe it gets rows written to it and removed, and just always happens to be empty when I look at it.

If you're looking for unused or abandoned tables, the number of rows won't be enough. You'd better look into index usage stats:

select database_id, object_id, index_id,
    last_user_seek, last_user_scan, last_user_lookup, last_user_update
from sys.dm_db_index_usage_stats

If you are interested in several methods for tracking table usage, I wrote about it some months ago.


Subscriber Node down in transactional replication 1) I have 2 node which node 1 is publisher and distributor in same server and subscriber in node 2. In case publisher is down for few hours and i need to bring up the subscriber for read and write by disabling replication, will the transaction and commands in node 1 distribution database will be rolled back or i will lose certain data?

2) I have node 1 is publisher and node 2 have distributor and subscriber, in case publisher is down for few hours and i need to bring up subscriber for read and write. What happen to transaction marked for replication in publisher but not has been sent to distributor, will it be rolled back or i will lose certain data?

Thank you

First, you shouldn't use Replication for High-Availability or Disaster Recovery.  It was never designed for that, and you'll see below that is not a very good solution

Instead you should really use AlwaysOn Availibility Groups instead of Replication if you plan on using the solution for High-Availability or Disaster Recovery.

  i need to bring up the subscriber for read and write by disabling replication, will the transaction and commands in node 1 distribution database will be rolled back

No.  The old Publisher/Distributor will come back at the point-in-time it went down.

  or i will lose certain data?

You will have data in each database that isn't present in the other.  You can either discard the data in one or the other, or manually add changes from one to the other, perhaps using the tablediff utility.

  What happen to transaction marked for replication in publisher but not has been sent to distributor, will it be rolled back or i will lose certain data?

The publisher will come back online, and any transactions committed, but not distributed, will be present in the Publisher, but the old Distributor is no longer reading its log file for transactions.  Again the previous subscriber now has data not present in the old publisher, and you have to decide what to do.

The typical operational response here is to save a copy of the old publisher, and then to re-create and re-initialize replication using the data from the old subscriber.  You do this either by configuring the old subscriber as the new publisher, or by restoring a backup of the old subscriber over the old publisher and re-creating the old replication topology.


How to Update Date in each row with different dates in MySQL I want to update date column of the table with dates in the increment of 1 for all rows.
How do I do it in MySQL?
Table: tbl_question_of_the_day
Column: date
So the output that I want is:
Row 1: Today
Row 2: Tomorrow
Row 3: Day after tomorrow
...and so on.
There is an ID column as primary key.
Thanks!

If the goal is to have the following:

id
date
message

1
2021-05-26
There is no spoon.

2
2021-05-27
I know kung fu.

3
2021-05-28
zOMG! A Nokia 8801!

4
2021-05-29
Anything you'd like, Mr. Reagan.

5
2021-05-30
Follow the white rabbit.

6
2021-05-31
Not like this.

... then I would suggest taking a look at the DATE_ADD() function and using the id value as the INTERVAL amount.


Using Text Fields As Booleans We are developing a product system and I was curious to what industry standards were for many Boolean statements.

In our system we have some, what you could call main, bit fields. E.G. isBackordered, isDiscontinued.

We also have a lot of internal processes and debated on using product tags to denote if this product should be processed that way or not. E.G. isManufacturable, isLaserable

I was wondering if it is better in the industry to just create everything as bit fields or acceptable in the industry to use tags for special circumstances like that.

If you have a system with a large number of tags it's probably more efficient management-wise to use a relational system between products and product-tags.

i.e.:

IF OBJECT_ID(N'dbo.ProductsTags', N'U') IS NOT NULL
DROP TABLE dbo.ProductsTags;
IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
DROP TABLE dbo.Products;
IF OBJECT_ID(N'dbo.Tags', N'U') IS NOT NULL
DROP TABLE dbo.Tags;
CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName varchar(100) NOT NULL
);

CREATE TABLE dbo.Tags
(
    TagID int NOT NULL
        CONSTRAINT PK_Tags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , TagName varchar(100) NOT NULL
);

CREATE TABLE dbo.ProductsTags
(
    ProductID int NOT NULL
        CONSTRAINT FK_ProductsTags_ProductID
        FOREIGN KEY
        REFERENCES dbo.Products(ProductID)
    , TagID int NOT NULL
        CONSTRAINT FK_ProductsTags_TagID
        FOREIGN KEY
        REFERENCES dbo.Tags(TagID)
    , CONSTRAINT PK_ProductsTags
        PRIMARY KEY CLUSTERED
        (ProductID, TagID)
);

Insert some data into the various tables:

INSERT INTO dbo.Products (ProductName)
VALUES ('wallet')
    , ('Paint');

INSERT INTO dbo.Tags (TagName)
VALUES ('IsLeather')
    , ('IsPlastic')
    , ('IsBlue');

INSERT INTO dbo.ProductsTags(ProductID, TagID)
VALUES (1, 1)
    , (2, 2)
    , (2, 3);

Query the tables:

SELECT p.ProductName
    , t.TagName
FROM dbo.ProductsTags pt
    INNER JOIN dbo.Products p ON pt.ProductID = p.ProductID
    INNER JOIN dbo.Tags t ON pt.TagID = t.TagID

╔═════════════╦═══════════╗
║ ProductName ║  TagName  ║
╠═════════════╬═══════════╣
║ wallet      ║ IsLeather ║
║ Paint       ║ IsPlastic ║
║ Paint       ║ IsBlue    ║
╚═════════════╩═══════════╝

This allows you to extend the tags at will without needing to redesign the database itself.

Using discrete bit columns in the products table is a strategy that will soon get very tedious.  Take for example:

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName varchar(100) NOT NULL
    , IsLeather bit NOT NULL
    , IsPlastic bit NOT NULL
    , IsBlue bit NOT NULL
);

SELECT p.ProductID
    , p.ProductName
    , p.IsLeather
    , p.IsPlastic
    , p.IsBlue
FROM dbo.Products p

INSERT INTO dbo.Products (ProductName, IsLeather, IsPlastic, IsBlue)
VALUES ('Wallet', 1, 0, 1);

Now, if you want to add products that might be red, you must alter the table via:

ALTER TABLE dbo.Products
ADD IsRed bit NOT NULL;

You must also modify any code that touches the dbo.Products table, to make that code aware of the new IsRed column.


INSERT ON DUPLICATE KEY UPDATE with IF I have sy_version table which has a 2-column primary key: (mod_id, sub_mod):

CREATE TABLE `sy_version` (
`mod_id` VARCHAR(2) NOT NULL,
`sub_mod` VARCHAR(30) NOT NULL,
`version` VARCHAR(50) NULL DEFAULT NULL,
`remark` VARCHAR(50) NULL DEFAULT NULL,
`update_date` DATETIME NULL DEFAULT NULL,
`download_link` VARCHAR(50) NULL DEFAULT NULL,
`file` BLOB NULL,
PRIMARY KEY (`mod_id`, `sub_mod`)
)COLLATE='utf8_general_ci'
ENGINE=MyISAM;

And my data is

CS;sbm_sl.exe;2015.11.01;IBS Sales App;2015-11-10 11:34:13;\N;0x73002E00000000

I want to update my data using insert into on duplicate key, for the first it is working fine. But everytime I executed I get my file column become NULL, result that I want is : file becomes NULL when version where I want to set is bigger the present(original) version. And the command is like below:

INSERT INTO sbm_sys.sy_version (mod_id,sub_mod,`version`,remark,update_date,file)
values ('CS','sbm_sl.exe','2015.11.07.1','IBS Sales App','2015-11-10 11:34:13', NULL)
ON DUPLICATE KEY UPDATE `version`='2015.11.07.1', remark='IBS Sales App',
update_date='2015-11-10 11:34:13', file=if(VALUES(version)>version,null,file) ;

It doesn't work fine, file column does not become NULL but the other columns is updated, so I tried changing the part become, and it works as I hoped:

file=if(VALUES(version)>version,null,file) ==> file=if(VALUES(version)>'2015.11.01',null,file) ;

The question is how I can't get "2015.01.01" from original version value?

Thanks for advice.

I think you should try this change:
INSERT INTO sbm_sys.sy_version
    (mod_id, sub_mod, version, remark, update_date, `file`)
VALUES
    ('CS', 'sbm_sl.exe', '2015.11.07.1', 'IBS Sales App', '2015-11-10 11:34:13', NULL)
ON DUPLICATE KEY UPDATE
    `file` = IF(VALUES(version) > version, NULL, `file`),    -- first this column
    version = VALUES(version),                               -- then this one
    remark = VALUES(remark),
    update_date = VALUES(update_date) ;

The order of the columns matters, rather unfortunately and in contrast with the SQL standard. The MySQL UPDATE documentation states:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
Single-table UPDATE assignments are generally evaluated from left to right. ...


Automate SQL Server restore from Prod to Dev I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance.  I currently have a job on the DEV box that uses the UNC path to the prod backup location to restore from.  However, anytime I want to run this restore job I need to go in and manually change the file so that it's pointing to the latest one.  Is there a method, preferably using T-SQL, which will grab the latest .bak file to use in the restore job?  If there is a better method for doing this then I'm open to suggestions.  I definitely don't want to reinvent the wheel.

Below is the current code I'm using to perform the restore.  I have it narrowed down to where I only need to change the database name and backup file location.

DECLARE @DB nvarchar(max)   /** Database to be restored **/
, @SQL nvarchar(max)        /** Dynamic SQL **/
, @disk nvarchar(max)       /** Backup location **/
, @l_mdf nvarchar(max)      /** Logical primary data file **/
, @p_mdf nvarchar(max)      /** Physical primary data file **/
, @l_ldf nvarchar(max)      /** Logical Log data file **/
, @p_ldf nvarchar(max)      /** Physical Log data file **/
, @dp nvarchar(200)         /** Datafile path **/
, @lp nvarchar(200)         /** Logfile path **/

/*** These are the only variables to manually set ***/
SET @db = 'PRODBackup'
SET @disk = '\\PROD_UNC_PATH\MSSQL10.SQL2008\MSSQL\Backup\PRODbackup_2015_04_05_220001_1759944.bak'

/*** This builds a file list table to be populated by executing filelistonly for the backup file ***/
declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32) -- remove this column if using SQL 2005
)
/*** Insert into aforementioned table ***/
insert into @fileListTable exec('restore filelistonly from disk =''' +@disk+'''')

/*** Set variables based on this table using filters and string manipulation ***/
    /** datafile **/
select @l_mdf = logicalName from @fileListTable where Type='D'
select @p_mdf = PhysicalName from @fileListTable where Type='D'
select @p_mdf = RIGHT(@p_mdf, CHARINDEX('\',REVERSE(@p_mdf))-1)

    /** logfile **/
select @l_ldf = logicalName from @fileListTable where Type='L'
select @p_ldf = PhysicalName from @fileListTable where Type='L'
select @p_ldf = RIGHT(@p_ldf, CHARINDEX('\',REVERSE(@p_ldf))-1)
/*** Set default data and log paths ***/
--select @dp = CAST(serverproperty('instancedefaultdatapath') AS nvarchar(200))
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dp output, 'no_output'
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @lp output, 'no_output'

/*** Execute dynamic SQL ***/
SET @SQL = ('USE [master]
            RESTORE DATABASE ['+@DB+']
                FROM  DISK = N'''+@disk+'''
                    WITH  FILE = 1
                ,  MOVE N'''+@l_mdf+''' TO N'''+@dp + @p_mdf+'''
                ,  MOVE N'''+@l_ldf+''' TO N'''+@lp + @p_ldf+'''
                ,  NOUNLOAD,  REPLACE,  STATS = 5;')
EXEC (@SQL);

Assuming your full backups are always done WITH INIT:

  SELECT TOP (1) @disk = f.physical_device_name
    FROM msdb.dbo.backupmediafamily AS f
    INNER JOIN msdb.dbo.backupset AS s
    ON f.media_set_id = s.media_set_id
    WHERE s.database_name = @db
      AND s.[type] = N'D'
    ORDER BY backup_finish_date;


What are the SQL standards Some job descriptions mention required knowledge of SQL standards. What do they mean by that?

I found something on wiki http://en.wikipedia.org/wiki/SQL#Standardization but I don't really know what to think of it.

There are some general ISO/ANSI standards that should act as guidelines for all RDBMS that use the SQL language. The standards define features of the language, such as data types. The current standard is SQL 2011, which added a standard for temporal support.

Ideally, all RBDMS vendors that use SQL should develop language functionality according to the standards, making porting databases and user skills between the various implementations much easier.

Unfortunately, this is not really the case. There are many features that RBMS vendors are slow to implement (e.g. SQL Server not having DATE until 2008, despite it being in the SQL:92 standards) and many things that the standards do not cover (e.g. indexing)

The full standards are not available freely online (I don't think), however you can read part one here SQL Part 1


SQL Server Replication causing locks on table we have three MSSQL 2005 servers (database is set to compatibility level 80 for legacy reasons) which we have just set up Transactional Replication w/ Updateable Subscriptions on.  Two which are located in the US, and 1 located in Asia.  Two of the 3 servers are busy servers and 1 is not.  The one which is not is the Distributor and Publisher.

What we are seeing is that the table which is part of the Replication finds itself being locked by the sp_MSupd_dbo stored procedure.  We notice this because other very simple select statements (just a lookup of the PK) are blocked during the execution time.  This stored procedure is doing a simple UPDATE statement.  This table has approx. 120,000 records in it.  When running the sp_MSupd_dbo stored procedure manually, it seems to run very fast without issue.  Additionally, the select statements which seem to get blocked are also very quick.  We are seeing some locks as much as 20-30 seconds long.

Also, the majority of the traffic are these quick select statements which do a lookup straight from the primary key.

Has anyone else seen anything like this before?

That procedure is what replication uses to update the table (you probably figured this out already).  I'd take a look at the execution plan and the waits for the procedure when it is running to see what the waits are showing.

I haven't specifically seen that before, but I can imagine it when the procedure is being run from a very remote distributor with a crappy network link in between.


MySQL Innodb - Truncate Partition. Does it reclaims space? Once a month I need to remove all the old data from the table. Previously I was copying all the needed data to another table, then drop the original table and rename the new table as original. When I ran drop table the space is reclaimed.

But now I am deciding to use Partitioning in the table. So my question is when I will do:

ALTER TABLE mytable TRUNCATE PARTITION partition

Will it reclaims the space? If it does not reclaims the space then am I left with only choice to OPTIMIZE the table or is there anything else I am missing? Since doing OPTIMIZE PARTITION on innodb table will lock the whole table.

You have not said what value innodb_file_per_table was set to when you created the table.  If OFF, you have no chance of shrinking any file on disk.  Instead, you can only send it off to be 'free' and potentially reused by inserts.  With ON...

A third option is ALTER TABLE REORGANIZE PARTITION ... where you essentially state the same partition as the 'from' and 'to'.  This degenerate version effectively does what OPTIMIZE PARTITION should do (but fails to).

Fourth option, which would work in some partition types:  DROP PARTITION and add it back.

Yes, OPTIMIZE PARTITION rebuilds the entire table.  After 5.6.17 (5.7.4), you may may be able to do it non-blocking (ALGORITHM=INPLACE).

Even if TRUNCATE does not free the space, will you be adding data back in?  Of so, then one could argue that it is "not a problem".


MySQL ssl setup failed I am following word by word of http://dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html for creating SSL certificates to setup SSL enable replication but it is not working for me. Steps which I am following:

Using above reference I have created all required certificates in /var/opt/mysql_certs/
Changed the ownership of all the certificates chown -R mysql:mysql /var/opt/mysql_certs
Then I edited my /etc/my.conf and put these 3 lines in mysqld section:

ssl-ca=/var/opt/mysql_certs/ca-cert.pem
ssl-cert=/var/opt/mysql_certs/server-cert.pem
ssl-key=/var/opt/mysql_certs/server-key.pem

Lastly I am restarting mysqld with /etc/init.d/restart mysqld. But, I am getting following errors:

[Warning]: Failed to setup SSL`
[Warning] SSL error: SSL_CTX_set_default_verify_paths failed

FYI: OS is centOS 6.

I recently solved this issue on my own system and it turned out to be SELinux blocking the reads for the certificates.  I fixed my problem by running a restorecon -v /path/to/certs/* but it could also be solved by disabling selinux by running setenforce 0 or by editing the /etc/selinux/config file and setting 'SELINUX=DISABLED'.

If you haven't disabled SELinux already that is.


How do I translate this query from MS Access into SQL Server How do I translate this into SQL Server:

select
-Sum((12=[GroupNum])*[BoardStatus]) AS G12
FROM
 QryAllBoardsByPacketIDs
INNER JOIN
 tBsReportGroups
  ON QryAllBoardsByPacketIDs.NominalLengthMetric=tBsReportGroups.NomLenMet
GROUP BY
 QryAllBoardsByPacketIDs.ParentPacketID,
 QryAllBoardsByPacketIDs.GradeID,
 QryAllBoardsByPacketIDs.NominalWidthMetric,
 QryAllBoardsByPacketIDs.NominalThicknessMetric;

Kind regards,

Martin

Not quite sure I follow as I rarely use Access, but I believe this is what you're looking for:

SELECT -SUM(
   CASE WHEN 12=[GroupNum] THEN 1 ELSE 0 END
   * [BoardStatus]
   ) AS G12
[...]

CASE...WHEN is the inline "if" of T-SQL.


Mapped drive for Microsoft SQL Server 2008 I've noticed strange issue after this weekend. Maybe someone could explain this situation.
I've got SQL Server 2008 placed on Windows Server 2008 with read/write permission to catalog ( let say Workgroup ) on network drive ( it is used to make backups )

To make catalog Workgroup visible for SQL Server, I've used this syntax:

  xp_cmdshell 'net use Y: \\network-disk-server\Workgroup [password here] /[user here] /persistent:no'

And everything was fine. I could make backups on Workgroup. But after Sunday SQL Server 2008 couldn't see mapped drive anymore. After making net use syntax again, I get error:

  Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed. Disconnect all previous connections to the server or shared resource and try again.

So I think that this mapped drive still exists, but somehow it is not seen by SQL Server anymore.

I am using Domain user service with read/write permissions to workgroup folder and sysadmin fixed role on MSSQL.

I've got explanation ( tricky ). Maybe someone could get use of it:

Administrator might set maximum number of connections from one account to external resources ( in this example - drive ). When the number of connections is reached, You don't have access to it ( and from SSMS it is not visible ).

Recommendation:

Check, using for example sp_who, do You have any sleeping processes on Database Engine Service Account. That requests could hang on external resources, and block access for other requests.


COUNT() and link between tables I have some difficulties to make a query.

Basically, I have a 3 tables: member, build and up:

member
--+---
id|...

build
--+-----------+---
id|member (fk)|...

up
--+-----------+---------
id|member (fk)|build (fk)

How can I get the total of "up" for each member (build owner)? I only know how to get it for each build:

SELECT up.build, COUNT(*) as totalUp FROM up GROUP BY build

To be clear, I'm not asking about this query:

SELECT up.member, COUNT(*) as totalUp FROM up GROUP BY member

More details

In my application, a member can "up" (it's a kind of "like") a build. When a member up a build, there is a new entry in the table up with the id of the build liked and the id of the member who liked the build. Each member can have a build.

Globally I want to get the total of likes for each build owner.

Example:

member
--+---
id|
0 |
1 |
2 |

build
--+------+---
id|member|...
--+------+---
0 | 1
1 | 1
2 | 2

up
--+------+-----
id|member|build
--+------+-----
0 |0     |0
1 |0     |1
2 |0     |2

The result of the query should be:

memberID|totalUp
--------+-------
1       |2
0       |0
2       |1

SELECT
    A.id build_owner,
    COUNT(C.build) build_member_count
FROM member A
INNER JOIN build B ON A.id = B.member       -- or LEFT joins
INNER JOIN up    C ON B.id = C.build        --
GROUP BY A.id ;


Error creating scalar function "Invalid Object Name dbo.fnMyFunctionName'" I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions.

When I try to modify function, function name is underlined in red and the mouseover text says

  Invalid Object Name 'dbo.fnGetXName'

If I write a query and try to ref any function, intellisense shows no functions after typing dbo.. It doesn't seem to matter what I name the new function, the same issue arises

Try refreshing the Intlisense cache.

Hit crtl+shft+R

From SQL Server Management Studio Keyboard Shortcuts

  Refresh local cache

Or under

  EDIT->IntelliSense->Refresh Local Cache


Check constraint only one of three columns is non-null I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other columns are NULL. What is the simplest check constraint to achieve this?

The context for this is trying to retrofit the ability to capture non-numeric results into an existing system. Adding two new columns to the table with a constraint to prevent more than one result per row was the most economical approach, not necessarily the correct one.

Update: Sorry, data type snafu. Sadly I wasn't intending the result types indicated to be interpreted as SQL Server datatypes, just generic terms, fixed now.

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO


SQL Server 2012+ Agent Job with Parameter to determine job flow Is it possible to have an Agent Job with an input parameter?

What I'm looking to do is have a flag to determine how the job flow will occur.
For example: I have a 10 step job. Steps 1-5 are always going to be executed. However, if the flag is set to 1, then then next step is going to be 6, and if the flag is not set, then execute step 8.

Can anyone please suggest how I could go about doing this?
Thanks.

Expanding a bit on what @Nic said in his comment.  Create a quick table in an applicable database.

CREATE TABLE JobParam AS (Param1 varchar(50))

Then set it appropriately before you start your job.

UPDATE JobParam SET Param1 = 'JobPath1'

As Nic suggested I would put some code at the top of steps 6+ like such.

IF (SELECT 1 FROM JobParam WHERE Param1 = 'JobPath1')
BEGIN
    T-SQL statements
END

Another option is to put code in the job that causes a failure if the param has a specific value and use the fail/succeed logic of the job steps to branch your job.

Either way I would make sure to put in a step at the end that always runs and re-sets your value to a default in case the job is run but someone forgot to update the parameter.  Maybe have the default bypass the entire job.

UPDATE JobParam SET Param1 = 'FailJob'