How to connect to a local SQL Server instance by IP Address in SQL Server Management Studio? I'm writing some test code that is required to connect to a local SQL Server instance (SQL Server 2017 - Developer Edition) by IP address. I have the SQL Server instance up and running on my machine, but can't figure out the correct IP + Port to use for my connection string's Server property.
As a starting point, I'm trying to at least manually connect to my server instance with SQL Server Management Studio (via the IP address) but I can't seem to figure it out.

How do I find the IP Address and Port of my local SQL Server instance?
What is the correct format I should be inputting the IP Address and Port into the Server Name field of SSMS (and is it the same format as a connection string Server property)?

I've tried locating the IP address executing the following SQL code on it:
SELECT
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

But the local_net_address and local_tcp_port fields are NULL and the client_net_address is set to <local machine>:

I've also tried going into the SQL Server Configuration Manager, under the SQL Server Network Configuration section, and enabling TCP/IP. Then I tried using a mix of the IP4 and IP6 addresses with Port 1433 (which is blank by default in the TCP/IP Properties, but I've tried leaving it blank and setting it to 1433) and that didn't work.

(I made sure to restart the SQL Server service and SQL Server Browser service every time I made changes in here.)
I've received the following errors when trying to connect via SSMS for the different combinations of the IP4 address with and without Port specified:
No Port Specified:

Port Specified w/ Colon:

Port Specified w/ Comma:

I've also tried connecting via 127.0.0.1 with the same combinations of Port 1433 above to no avail. (I receive similar errors except for the following combinations.)
127.0.0.1 w/o Port:

127.0.0.1 w/ Port via comma:

One thing you could try is to set Enabled to "no" for all individual Ip Addresses, and at the very bottom specify the desired port. I generally use the same port for all IPs, so I catch them all with just one config. If for nothing else, as a troubleshooting measure.


Looking for a Trigger to change inserting strings? I have an MSSQL Server 2008 Database.
There are some tables in my Database like below :
table1, table2, table3, ....
each table has a NVARCHAR(50) field (Name)
Now, I wanna change the strings that each sql query inserts in this field
e.g :
We have a query that wanna insert "Sample Text" in Name field.
I wanna change it to "Sample Name" with a SQL Trigger

Is it Possible with sql triggers ? if so, How? I'm naive in T-SQL
Is there a better way to do it with SQL Server ?

if you want to use triggers thats your call, i am just suggesting a different approach

you can use the Default Value or Binding feature of a table

e.g in your table1, you can make the default value of Name column = 'Sample Name' by below script

ALTER TABLE table1 ADD CONSTRAINT
    DF_Table1_Name DEFAULT N'Sample Name' FOR Name
GO

Look at image below

This code below adds a trigger to your table that works after insert, it updates the name added and replaces the 'x' with '*'

CREATE TRIGGER ReplacingTrigger
ON  table1
AFTER INSERT
AS
BEGIN
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.name = table1.name
END

i suggest if you have an ID in the table to use it in the joins to find the exact record inserted to make it accurate like below

CREATE TRIGGER ReplacingTrigger
ON  table1
AFTER INSERT
AS
BEGIN
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.id= table1.id
END


The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way:

SELECT o.*                  -- Change this to only the columns needed.
FROM objects AS o           -- If there are data needed from other tables,
                            -- join here.
WHERE EXISTS
      ( SELECT 1
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE nc.name = 'orange'
          AND n.object_id = o.object_id
      )
  AND NOT EXISTS
      ( SELECT 1
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE n.name = 'banana'
          AND nr.object_id = o.object_id
      ) ;


How to filter column output based on specific format value Is there any way to filter by the output format in sql server.

Below is my output :

  SQLOutput

  AB-123456-99 Checker

  123456-10000-00 2016

  111119-20000-00 2016

How can I get the expected output as below,

the query should exclude all the column values which are in the format

  123456-10000-00  -- xxxxxx-xxxxx-xx

If the column output has this format that value should be excluded from results.

and my final output should be as below :

  SQLOutput

  AB-123456-99 Checker

Please help if there is any way to get this.

Assuming that your output is all one column, I would go with the conditional clause below. If you output is more than one column, then you can make the adjustments accordingly.

DECLARE @table TABLE
(Column1 VARCHAR(50))

INSERT INTO @table
        ( Column1 )
VALUES  ( 'AB-123456-99 Checker')
    ,('123456-10000-00 2016')
    ,('111119-20000-00 2016')

SELECT t.Column1 FROM @table AS t
WHERE t.Column1 NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%'

So, you would essentially replace the x with [0-9] for numeric. If it was alpha, you would replace the x with [a-z] for case insensitive collations and [A-Za-z] for case sensitive collations. If it's alpha or numeric, it would be [0-9a-z] or [0-9A-Za-z]


Can a surrogate key and a primary key be in the same table? For example we have:

Surrogate key: ABC123
Primary key: 1
Name: James

Is this legit in a Data Warehouse table?

A table can and should have as many keys as it needs. Usually when a surrogate key is used it means you will also want some alternative key as well (variously called a domain key, natural key or business key).

The practice of designating any one key as "primary" is of no great significance. It is simply a convention to mark one of the keys as "preferred" or important in some way that has meaning for the database user. Typically the primary key is the one referenced by foreign key constraints in other tables.


SQL Server Page Life Expectancy sharp drop I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far.
It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of VMware 5.0. The VM has 16GB of memory with max server memory in SQL set to 8GB.
On seemingly random days i see Page Life Expectancy drop off a cliff from 42,000+ sec to 200-300 sec for a few minutes. It does not decrease with time. It literally falls of a cliff. A complete 90 degree drop on the chart when i plot the values. then i it starts to build up again

The Target Server Memory and Total Server Memory counters are consistent at 8GB throughout. There also no spikes in batch requests/sec or user connections at these times.

On the VM level, Memory Mapped is consistently at 16GB and Memory Ballooned is 0, so the VM is not under memory pressure.

I'm trying to think where i should look next. What counters or areas of interest should i put my eyes on as a next step to understand these performance drops on the server.

How about Database pages, Free list stalls/sec, Page reads/sec, Page lookups/sec, Readahead pages/sec? Their behavior, correlated with the event, would be very useful to know. Similar, all the counters in Memory Manager category.

Could be a query that scans a large amount of cold data hits the server. As it reads the cold data, it evicts pretty much everything else in the BP, including its own just-read pages. After the event the BP slowly start building up the hot pages and PLE start to increase. Counters in Access Methods object could confirm this.


How do I find the day of week of a date-time field in postgres? How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While

SELECT EXTRACT(DOW FROM '2011-07-07')

works, the following query does not work and I can't figure it out:

SELECT EXTRACT(DOW FROM TIMESTAMP date) as day
FROM purchases GROUP BY day;

It's like postgres won't let me find the day of the week for fields but only constants. How do I find the day of week for a field?

The whole issue revolves around CASTing and quirky PostgreSQL syntax.

CASTing strings as DATEs:

It's very simple - your first line of code:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07');
ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique
LINE 1: SELECT EXTRACT(DOW FROM '2011-07-07');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
ntest=#

does not work. This is because, as far as PostreSQL  is concerned, '2011-07-07' is just a string literal, not a DATE. This does work:

ntest=# SELECT EXTRACT(DOW FROM DATE '2011-07-07');
 date_part
-----------
         4
(1 row)

Note the extra DATE that CASTs the string as such. You can also CAST it this way:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07'::DATE);
 date_part
-----------
         4

This double colon (::) is very much a PostreSQL thing. It's really quite "obvious", once you grasp the PostgreSQL syntax (it had me stumped for a couple of minutes!)

A date field:

ntest=# \d orders;
       Table "public.orders"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 ldate       | date    |
 customer_id | integer |
 amount      | integer |

So, we have a date field - with records as below:

ntest=# SELECT * FROM orders;
   ldate    | customer_id | amount
------------+-------------+--------
 2009-10-08 |           3 |   3000
 2009-10-08 |           3 |   1500
 2009-11-20 |           2 |   1560
 2008-05-20 |           4 |   2060
(4 rows)

So, to get the "day of week" from a date (note the two FROMs - doesn't work without the FROM between brackets):

ntest=# SELECT EXTRACT(DOW FROM ldate) FROM orders;
 date_part
-----------
         4
         4
         5
         2
(4 rows)

Now, for a timestamp field:

ntest=# \d timestamptest;
           Table "public.timestamptest"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 x      | timestamp without time zone |

And:

ntest=# SELECT * FROM timestamptest;
             x
----------------------------
 2016-04-18 22:52:30.020979
(1 row)

And DOW (again, note the two FROMs):

ntest=# SELECT EXTRACT(DOW FROM x) FROM timestamptest;
 date_part
-----------
         1

Quirky syntax (the double colon also OK, if you want to be really explicit), but it works fine once you've mastered it.


0kb backup using mysqldump Same question has been asked here but its not helping me out.

In my python utility, I'm trying to take MySQL database backup using mysqldump. It runs successfully but I'm getting 0kb file in my backup folder.

This is my code;

DB_HOST = '192.168.1.1'
DB_USER = 'root'
DB_USER_PASSWORD = '_root_user_password_'
DB_NAME = 'db_name'
BACKUP_PATH = '/backup/dbbackup/'

DATETIME = time.strftime('%m%d%Y-%H%M%S')
TODAYBACKUPPATH = BACKUP_PATH + DATETIME

dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"

os.system(dumpcmd)

Can anyone guide me out that what I'm missing here??

Some troubleshooting:

Run the command (dictated by dumpcmd) from the command-line and see if there are any errors
Does the password have any special characters in it that could mess up in a shell
Try changing mysqldump to the full path, eg: /usr/local/bin/mysqldump
Check the return value from os.system() in your script

Changing to the full path appears to be what you were looking for.


Reordering a postgresql timescaledb hypertable Is the standard postgresql way also possible on a timescaledb hypertable?

CLUSTER [VERBOSE] tablename [ USING indexname ]

Example:

Creating table & hypertyble

CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data VARCHAR(8) NOT NULL,

SELECT create_hypertable('l1', 'timestamp', chunk_time_interval => interval '1 day');

Produces an index like this (can be viewed using this command):

l1        | l1_timestamp_idx                                               | CREATE INDEX l1_timestamp_idx ON public.l1 USING btree ("timestamp" DESC)

And than CLUSTER the data like this

CLUSTER l1 USING l1_timestamp_idx;
stdout: CLUSTER

It looks like it works.. But is this recommended? And is it possible to reverse the order of the CLUSTER command? Meaning, sorting it with the lowest timestamp first.

We recommend instead to use TimescaleDB's reorder_chunk command (or a reorder policy).

CLUSTER will take an ACCESS EXCLUSIVE lock on the entire hypertable, which prevents any operations (reads or writes) from occurring during the entire cluster process (https://www.postgresql.org/docs/current/sql-cluster.html).

We re-implemented similar functionality in reorder_chunk to not acquire require such a heavy weight lock:  It operates chunk-by-chunk, and you can still read from the table/chunk while the reordering/clustering is occurring.

But to answer your other questions:

You can order in either timestamp order.  Define your index to be
clustered/reordered as having either timestamps DESC (descending) or ASC (ascending) order.
Reordering chunks can be really great for performance. You don't typically need just for timestamp if you are inserting data is loose timestamp order.
But we often see them in composites, so if you have a device &
timestamp, and you often do a query like:

  SELECT foo from table where device_id = X and timestamp > now() - interval '1 week'

then its very useful to reorder based on a composite index like (device_id, timestamp).

https://docs.timescale.com/latest/api#reorder_chunk
https://docs.timescale.com/latest/api#add_reorder_policy


Totals on multple case conditions - mySql I am attempting to translate the query in this question into mySql, as we moved the data from an access db to a new structure.

I have come up with the following, since there are multiple conditions (Value of '' or 'Did Not Compete'), and I was getting the following error (Since resolved, see edit 2 below).

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'then 0 else 1 end) AS 'Total Weapons',
        SUM( case when ( tourney_info.c_we' at line 7

The query is as follows:

SELECT school_num, 'Total Form and Sparring', 'Total Weapons', 'Total Combat', 'Total Form and Sparring' + 'Total Weapons' + 'Total Combat' AS 'Total Entries'
FROM
(
  SELECT
  tourney_info.school_num,
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Compete') AND tourney_info.spar IN ('','Did Not Compete')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Compete') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('', 'Did Not Compete') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE school_num = '546' AND tourn_date > '2018-04-20' AND tourn_date < '2018-04-22'

) AS derived

Once the error was resolved as per the comments, the query gives the following results:

school_num  Total Form and Sparring     Total Weapons   Total Combat    Total Entries
    546     Total Form and Sparring     Total Weapons   Total Combat    0

Edit #2, after looking back at the field specifications, as a test query showed me that someone set the field to be 10 chars, and my 'Do Not Compete' was truncated on import:

If I take the interior query out as follows, I get individual totals, however I do not get a cumulative total:

  SELECT
  tourney_info.school_num, 'Total Form and Sparring' + 'Total Weapons' + 'Total Combat' AS 'Total Entries',
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE tourney_info.school_num = '546' AND tourney_info.tourn_date > '2018-04-20' AND tourney_info.tourn_date < '2018-04-22'

Result set:

 school_num     Total Entries   Total Form and Sparring     Total Weapons   Total Combat
546               0                   36                      17                    25

In the latest iteration (edit #5), the problem would be that you can't use column aliases within the query where they're declared. You can either go back to the original query (get the total for each category in a sub-query, and add them together using the aliases in the main query), or do this:

SELECT
  tourney_info.school_num,
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end)
  + SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end)
  + SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Entries',
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE tourney_info.school_num = '546' AND tourney_info.tourn_date > '2018-04-20' AND tourney_info.tourn_date < '2018-04-22'


In Oracle, if parallel_max_servers is set to be significantly less than the DOP can the explain plan be sub-optimal? For tables that have DEGREE DEFAULT, the degree of parallelism (DOP) is calculated by Oracle using number of CPUs and number of threads per CPU amongst other things. As I understand it, when costing execution plans Oracle will assume the default DOP for tables with DEGREE DEFAULT.

At execution time, however, if parallel_max_servers is less than the default DOP then the number of parallel servers will be restricted and - I presume - the final execution may not go as intended by the costed execution plan and a less parallel execution plan may be preferable.

Is this correct?

It would be a very strange thing if parallel_max_servers is set to a value below cpu_count * parallel_threads_per_cpu. I would call that a mistake by the DBA even. Let me bring to your attention a new feature of 11g (you should always mention your version, by the way), called Automatic DOP, that I have introduced in a posting here:
http://uhesse.wordpress.com/2009/11/24/automatic-dop-in-11gr2/


How make DBMS_OUTPUT? I know in Oracle

DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(in_COMP);

Now I writing procedure in Teradata

create procedure SANDBOX.CreateNewUser ()
begin
    DECLARE errstr VARCHAR(1000);
    begin
        --begin
--  SET return_code='Success';
--end;
--begin
    /* here have to your code*/
--end;
    end;
end ;

How make same in Teradata?

You can't do this in Teradata like in Oracle.

As a workaround you might Insert into a temporary table and either run a Select on it at the end of the SP or Select from the client after the CALL.

-- create the message table once
CREATE GLOBAL TEMPORARY TABLE DBMS_OUTPUT
 (
   TS TIMESTAMP(2) DEFAULT Current_Timestamp(2),
   Step VARCHAR(100),
   ErrorMsg VARCHAR(200)
 ) ON COMMIT PRESERVE ROWS;

-- now create your SP
REPLACE PROCEDURE mySP (...)
DYNAMIC RESULT SETS 1
BEGIN

   ...

    -- clear messages from previous CALL at start of SP
   DELETE FROM DBMS_OUTPUT;

   ...

   -- add a message
   INSERT INTO DBMS_OUTPUT (Step, ErrorMsg) VALUES ('step X', 'bla bla bla');

   ...

   -- finally return all messages (also available after the CALL)
   BEGIN
      DECLARE qry VARCHAR(100);
      DECLARE ErrorMsgs CURSOR WITH RETURN ONLY FOR c;
      SET qry = 'SELECT * FROM DBMS_OUTPUT ORDER BY TS;';
      PREPARE c FROM qry;
      OPEN ErrorMsgs;
   END;

END;


Postgresql join removal I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL.

If a view is doing joins that don't:

add any columns
eliminate rows
duplicate rows
introduce nulls

will the joins be ignored for the queries that don't need them. Or is it a SQL Server thing only ?

There is an example about this feature in the PostgreSQL "Whats new in 9.0". If the optimizier detects, that a join can not add any additional rows, it will be dropped. The PostgreSQL Feature does not remove joins from views if they are not needed because the requested columns are not selected.

Also, the feature is limited on LEFT JOINs right now ( version 9.4 ). This may change in future versions.


According to:

 https://www.sqlite.org/windowfunctions.html

SQLite supports window functions. You can therefore easily enumerate the resultset like:

SELECT date_time, value
     , row_number() over (partition by DATE(date_time), HOUR(date_time)
                          order by dt desc) as rn
FROM T

I'm not sure what date functions there are, so you might have to change those. Now, since we ordered them in descending order, we can just pick the first one:

SELECT date_time, value
FROM (
    SELECT date_time, value
         , row_number() over (partition by DATE(date_time), HOUR(date_time)
                              order by dt desc) as rn
    FROM T
) AS X
WHERE rn = 1;


MySQL rebuild after Corrupt ibdata1 I have a MySQL server that serves a couple of databases including a Magento e-commerce store.
We have been suffering with corruption in the ibdata1 file for a while, but it has now reached a critical point.
The only database that uses innodb tables is the Magento webstore back-end.
I have an up to date SQL extract of the entire magento database. I thought that I could shut down MySQL, rename the ib* files, restart MySQL and re-create the database from the SQL file - however I am having a major problem restarting MySQL after renaming the ib* files.
I keep getting 'cannot connect to mysql ..... mysql.sock'.
It appears as though the ib* files are all re-created as I would expect, but I can't restart and then connect to the sever...
Any ideas??
EDIT
Have already tried all values of innodb_force_recovery. Error log shows various issues including
difficult to find free blocks from the buffer pool

and now also
InnoDB: Error: page 131073 log sequence number 17 3671003941
InnoDB: is in the future! Current system log sequence number 17 3670939210.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files

When referring to ib* files, are you also referring to the ib_log files? They also need to be removed when you remove the ibdata* files. Have a look at https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql that might be of some help.


Analyst MSDB Access SQL Server 2017 i'm an accidental DBA for a company and having trouble figuring out this permissions situation.

We have a new 2017 SQL server that I would like to get our report writer / analysts access to a few of the databases. She is in an "analyst" AD group with another analyst.  That group is setup at the server level login's with a few of the databases checked off (not MSDB) in the user mapping.

While trying to query the databases she should have access to, she gets the same exact errors described in this post (including the MSDB datababase part), but the other analyst does not: https://www.codeproject.com/Articles/548424/The-Server-Principal-xxxx-is-Not-Able-to-Access-th

We can't find the 2 users setup individually anywhere on the server where one would have different access than the other. We also checked other groups to make sure they were not in them. Is it possible it's a way her computer/SSMS is configured? We changed her default SSMS database to a database she should have access to.

The resolution in the article I linked suggests to grant connect access to the public role or individual users/groups in MSDB but when testing that, she could view data in that DB and had more access than we would like. I thought I read connect should only grant her enough access to connect to the DB but not see anything, however that didn't seem to be the case.

Any suggestions on what we're missing? Let me know what what additional details you may need.

Thank you,
Dave

There are several things that could cause SSMS to attempt to connect to a certain database at logon:

If the user is in an AD group, and the login for that group has the msdb database set as the default.  If the user belongs to multiple groups that have logins, I don't know which login it uses to decide which database to connect to, but nonetheless it will use one of them.
On the connection dialog in SSMS, click the options button and note that the database to connect to can be specified.  This setting is persistent, so if it was set to msdb in the past, it stay that way for the user until changed.
On the connection dialog in SSMS, click the options button and select the Additional Connection Parameters tab.  This setting is also persistent, and if "database=msdb" was specified there at some point, removing it will resolve the issue.


Nested subquery giving eror of unknown column I am facing uknown column 'id' issue which is exist in db_emp.test_etl. Then also it is giving uknown column error why?.This query successfully working in mysql version 8.0.14 but not mysql version 8.0.11.

18:30:48
update db_emp.test_etl
   set name='abc'
 where id in( select max(SETNO)
                from ( select sum(SETNO)
                         from AUC1
                        where SETNO=id
                     ) a
            )
Error Code: 1054. Unknown column 'id' in 'where clause' 0.0024 sec

  db_emp.test_etl table structure :

CREATE TABLE `test_etl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

  Above query work for single level sub query as below :

12:02:57
update capitalmarket.test_etl
   set name='abc'
 where id in(select sum(SETTLEMENTNO)
               from capitalmarket.AUCTION
              where SETTLEMENTNO=id
            )
0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0  0.0020 sec

The problem here must be with the id reference in the innermost SELECT. For a long time MySQL has had trouble with resolving outer references in correlated subqueries if those references were placed inside derived tables in the correlated subqueries.
Apparently the problem has been resolved in 8.0.14:

Optimizer Notes

Previously, derived tables and common table expressions could not contain outer references. Outer references are now permitted.

If you want the query to compile both in 8.0.14 and in 8.0.11, apparently you need to rewrite it to somehow avoid referencing the id column in a derived table. Before making any suggestions, I must note that your example has other issues than the unresolved id reference. The very first line in this correlated subquery:
select max(SETNO)
from ( select sum(SETNO)
       from AUC1
       where SETNO=id
     ) a

already has a problem, because it references an unknown column SETNO. The derived table a does not have such a column.
So, for the sake of showing how to resolve the id reference issue, I am going to assume that sum(SETNO) was meant to be written as sum(SETNO) AS SETNO. That way at least* the outer level SETNO reference becomes valid.
Basically, the solution is to get the MAX(SETNO) in an alternative way, such that a derived table is actually not needed at all and thus the id column can be referenced directly in the correlated subquery. For that you will use ORDER BY and LIMIT 1. Specifically, you rewrite your correlated subquery as follows:
SELECT sum(SETNO)
FROM AUC1
WHERE SETNO = id
ORDER BY sum(SETNO) DESC
LIMIT 1

This way the correlated subquery is still getting the same value but no longer references outer columns from a derived table.

*The query still does not make much sense as it is, though. But at least it should compile now and allow us to demonstrate the solution.


How to find the AD user that logged in to SQL Server using SQL Login Some users are logging into database servers locally, opening SSMS and logging in to SQL Server using SQL logins to run queries.

How can I find the AD user that logged in to SQL Server using a SQL login on the local database server using SSMS?

It's not possible I'm afraid.  This is one of the down sides of using SQL Server logins.  The only thing you can do is to look at the Hostname.  Most people connect using their local machine and if your company has a list of user to machine name then you can track them that way.

Unfortunately this information is not normally logged.  You will have to set up a trace, extended events session or server audit and collect login events and make sure you include the hostname property.


Do triggers affect DB performance I have a database with 250 small and medium sized tables. These tables are related with both MyISAM and InnoDB storage engines. There are multiple triggers that have been created on different tables. These triggers are getting executed on Insert, Update and Delete events. My system has started to sometimes perform bad. On check, I found that there are a huge number of DB connection threads running.

Now my question is:

Do these triggers affect my system performance? If yes, Is there
any better alternative for these triggers?

A trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.
There are three different strategies to
ensure execution of data integrity code when a window on data application (WoD) attempts to execute a transaction:

Declarative : Table constraint implementation, using primary & unique keys, check constraints, foreign
keys.
Triggered procedural: Using triggers.
Embedded procedural: Implementing data integrity logic in applications business layer. This is probably by far the most used procedural strategy for implementing DI code in WoD applications.

You can state only two types of table constraints declaratively: uniquely identifying
attributes (keys) and subset requirements referencing back to the same table, in which case a subset requirement is a table constraint (foreign key to the same table).
Implementing all other types of table constraints (like a multi-tuple constraints or transition constraints) requires you to develop procedural DI code.
So the answer is No, not without a heavy cost to implement the logic in application level.
As I know, triggers will affect performance in insert, update and delete operations. but doesn't affect read performance.
To tune your RDBMS first see what are the DB connection threads doing, and what are the most costly waits.


Does HOT Update re-arrange existing rows on a page to create a contiguous space? For example, on a 8kb page:

4 rows are created, each 2kb
Row 3 is deleted
Row 1 is updated to a size of 3kb

There is no contiguous chunk of 3kb space on the page, so what happens?

Do rows on a page get re-arranged to make a contiguous chunk of space for the update?
If so, then do indexes need to be updated to point to the slightly different locations? Or does it not matter as long as they're still on the same page?

No, HOT doesn't defragment pages. The cost of doing the needed index updates would be excessive.

PostgreSQL would write the new version of row 1 to a new page.


Transpose multiple columns into 2 column and single row to multiple row in sql Using Select top 1 statement I have to retrieve data from an 8 column table without any unique key.  Now I want to transpose this table into two columns: original column name to the first and its respective value to second.

An original table like this:

Column1 | Column2 | Column3 | Column4
55      | 108     | 555     | 85

And the expected result:

ColumnName | Value
Column1    | 55
Column2    | 108
Column3    | 555
Column4    | 85

There's a T-SQL construct called UNPIVOT which is designed for this. But in my experience, you'll have more luck with APPLY as per my code below:

SELECT *
FROM YourTable t
CROSS APPLY
    (SELECT 'Col1', Column1
    UNION ALL
    SELECT 'Col2', Column2
    UNION ALL
    SELECT 'Col3', Column3
    UNION ALL
    SELECT 'Col4', Column4
   ) u (columnName, Value)
  ;


Export sql azure DB as bacpac file without including data for certain tables When I export a sql azure database as data tier application from sql server management studio I get the option to tick off certain tables but then these tables are not included in the resulting file.

I want to export these tables schemas, but ignore their data (import empty tables essentially). For all the other tables I also want the data.

Is it possible to do that?

You can create a perfect copy of a table and the whole schema by doing:
SELECT TOP 0 * INTO new_table FROM old_table

If you have a linked server or a database on the same server that is called, let's say, database_copy you can actually iterate all tables and you will end up having the same number of tables, all with the same schema.
Otherwise, if you can mask the data or encrypt them. But in this case they will still be on the database


Why does adding ORDER BY makes the select much faster? First query, 900ms:

SELECT * FROM geoip.isp_ipv4
WHERE INET_ATON(?) BETWEEN start_ip AND last_ip LIMIT 1

Second query, 0.5ms:

SELECT * FROM geoip.isp_ipv4
WHERE INET_ATON(?) BETWEEN start_ip AND last_ip ORDER BY start_ip DESC LIMIT 1

I wonder why there is so much performance difference.

There could be only one result for a given ip, so the sorting doesn't change the returned row in any way...But why is it so faster?

It's the maxmind geoip db (1.59M rows), this is my table structure:

CREATE TABLE `isp_ipv4` (
  `start_ip` int(10) unsigned NOT NULL,
  `last_ip` int(10) unsigned NOT NULL,
  `asn` mediumint(8) unsigned DEFAULT NULL,
  `isp` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `osn` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`start_ip`),
  KEY `last_ip` (`last_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I'm on MySQL 5.7.17, the same happens in both dev (Windows 10) and production (Ubuntu trusty)

The EXPLAINs are identical:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'isp_ipv4', NULL, 'range', 'PRIMARY,last_ip', 'PRIMARY', '4', NULL, '795214', '50.00', 'Using where'
'1', 'SIMPLE', 'isp_ipv4', NULL, 'range', 'PRIMARY,last_ip', 'PRIMARY', '4', NULL, '795214', '50.00', 'Using where'

What was the IP?  1.1.1.1 would do best by starting from the beginning.  255.255.255.255 would work better by starting at the end.

The EXPLAIN does not say whether the "range" was scanned ascending or descending.  I don't even think EXPLAIN FORMAT=JSON SELECT ... would point this out.

So... I think your fast one had a high IP address and did start from the end due to the ORDER BY.

More discussion of the intractability of queries like this, plus a solution if the ranges are non-overlapping.


View with parameters in Sybase SQL Anywhere A view with Sybase engine cannot take parameters. Is there a way to do this?

Solution is to create procedure with use of dynamic query:

CREATE PROCEDURE view_with_parms( in str1 varchar(255), in str2 varchar(50) )
RESULT(
  myID integer,
  someName varchar(255))
BEGIN
    EXECUTE ( 'select myID, someName from ' || str1 || ' order by ' || str2 || ' asc' );
END

then:

select * from view_with_parms('test_table','myID');

Tested with:

SQL Anywhere 9
SQL Anywhere 12


Does oracle database have a mechanism to replace specific character when storing data I'm using oracle 11g database, does oracle database have a mechanism to replace specific character with other characters over all tables when storing data. For example i want to go to specific configuration place in oracle and specify these characters ﻙ, ﻱ to be replaced with these ک, ی that are Arabic characters.

I'm beginner in oracle database.

The database does not offer such an option that you asked for.

It is the responsibility of the client/application to insert correct data into the database.

If you want to change existing data, you can use the TRASNLATE function to replace each occurence of specific characters.

update table1
set column1 = translate(column1, 'AB', 'XY')
where column1 != translate(column1, 'AB', 'XY');


Get all rows where specific column value occurs more than once (filter out single occurences) I am trying to filter all sessionIds that occur once out of an existing result set.
This query is being used in a web application and runs on a big dataset (~ 35 million rows), so I want to prevent having subqueries here.
I tried this, which provides a filtered result, except that I now only get one row for each sessionId (and I want every request and response):
CREATE TABLE `api_log` (
  `id` varchar(50) NOT NULL,
  `clientId` varchar(100) DEFAULT NULL,
  `inserted` int(11) DEFAULT NULL,
  `sessionId` mediumtext DEFAULT NULL,
  `stage` varchar(120) DEFAULT NULL,
  `request` longtext CHARACTER SET utf8mb4 DEFAULT NULL,
  `response` longtext CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO api_log
  VALUES
    ("1", "abc", 1621008484, "session1", "production", '{"key":"value"}', '{"key":"value"}'),
    ("2", "abc", 1621008494, "session2", "production", '{"key":"value"}', '{"key":"value"}'),
    ("3", "abc", 1621008584, "session1", "production", '{"key":"value"}', '{"key":"value"}'),
    ("4", "abc", 1621008684, "session2", "production", '{"key":"value"}', '{"key":"value"}'),
    ("5", "abc", 1621008784, "session3", "production", '{"key":"value"}', '{"key":"value"}'),
    ("6", "abc", 1621008884, "session4", "production", '{"key":"value"}', '{"key":"value"}'),
    ("7", "abc", 1621008984, "session5", "production", '{"key":"value"}', '{"key":"value"}'),
    ("8", "abc", 1621009084, "session6", "production", '{"key":"value"}', '{"key":"value"}'),
    ("9", "abc", 1621009184, "session7", "production", '{"key":"value"}', '{"key":"value"}'),
    ("10", "abc", 1621009284, "session8", "production", '{"key":"value"}', '{"key":"value"}');

SELECT
    `clientId`,
    `sessionId`,
    `inserted`,
    `stage`,
    `request`,
    `response`
FROM
    `api_log`
WHERE
    (stage = 'production')
    AND (clientId = 'abc')
    AND (
        `inserted` BETWEEN 1621008482 AND 1621009285
    )
GROUP BY
    `clientId`,
    `stage`,
    `sessionId`
HAVING
    COUNT(sessionId) > 1

Is there any trick to get all rows where a sessionId occurs more than once?
In this case, I get two rows, one for session1 and one for session2, but I am missing two more because both mentioned sessionIds have an additional row that should match.
SQL fiddle: http://sqlfiddle.com/#!9/f87bb54/3

The first approach is to create an index by sessionId. But its datatype is MEDIUMTEXT...
So the approach is: normalize your data, move sessionId values into separate table and refer to it by foreign key. The reference column will be compact (4 or 8 bytes) and indexed, so its usage in the query as GROUP BY expression will improve.
The columns clientID and stage seems to be candidates to such normalization too.


MySQL data retained after OS reinstall Something strange happened today.

I reinstalled my OS and then when I reinstalled MySQL workbench some of my old data was still there. I think the actual local db is empty, but all the connections, models, and listed servers are still there. I can't view which users still exist because I get an connection error. The server is running, though.

I have a backup. I do a dump nightly. I was going to restore from that, but I expected a clean slate when I reinstalled. Should I delete everything before moving ahead with the restore?

Does anyone have an idea why/how that happened?

I would expect the OS to be cognizant of already installed MySQL packages, but not the files under /var/lib/mysql. Sure, rpm and yum repository info gets reset with the OS. The best thing that I know of a mysql uninstall is the renaming of /etc/my.cnf.

What you could have have done was something like this:

STEP 01

service mysql stop
rpm -qa | grep i mysql

You might see the following output

MySQL-shared-compat-5.6.12-1.rhel5.x86_64
MySQL-devel-5.6.12-1.rhel5.x86_64
MySQL-client-5.6.12-1.rhel5.x86_64
MySQL-shared-5.6.12-1.rhel5.x86_64
MySQL-server-5.6.12-1.rhel5.x86_64
MySQL-python-1.2.3-0.3.c1.1.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64

STEP 02

You would then rpm -e or yum uninstall all these packages.

That still won't nix the files for MySQL. What next ?

STEP 03

Move the old datadir aside:

chown -R root:root /var/lib/mysql
mv /var/lib/mysql /var/lib/mysql_old

Now, the old mysql data is out of the way.

If you did not do these things before installing MySQL, a new install of MySQL will not overwrite the previous /var/lib/mysql. In light of this, I would not expect any OS installations to do this either.

After the OS reinstall, you probably tried to rpm or yum install MySQL again. This won't shove aside /var/lib/mysql. If you don't want the old /var/lib/mysql, no worries. Just redo Steps 1-3,


How to change default security on SQL Server database so that everyone can use it? I have a database hosted on my machine that I want co-workers to be able to access and edit through a program I created.

My current connection string is:

Server=MyMachine;Database=MyDatabase;Integrated Security=True;

And to let another user access it I have to:

Add a user under database security in SQL Server Management Studio
Add a user under server security in SSMS
Run the script:

USE Database
    GRANT SELECT, INSERT, UPDATE, DELETE TO [Username]
GO

How can I configure it so that everyone on my work network (or everyone whose windows username starts with "MyCompany\") can have these privileges by default? That way I don't have to manually add every new person.

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase.  After creating the domain group, you could do something like:

USE [master]
GO
CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS
GO
USE [YourDatabase]
GO
CREATE USER [DOMAIN\AllUsersForYourDatabase]
FOR LOGIN [DOMAIN\AllUsersForYourDatabase]
GO

You can then make the logins on your domain members of that group. All the logins will inherit the permissions granted to the group that gives them access. (You could include Domain Users in the group if you truly wanted everyone in the domain to have access.)

Of course, you need to grant the needed rights to the YourDatabase user [DOMAIN\AllUsersForYourDatabase] so that everybody in the group gets the same rights.

If you need some users to have additional rights, those can be individually granted.  Or you can create another Group that would include only the users with the extra rights.  The users will get the aggregate of all rights from all groups for that login.


How do I know what my username/password is for SQL PLUS? I am trying to set up an Oracle SOA Suite environment with docker containers according to this website -> https://svgonugu.com/2018/03/12/docker-containers-for-oracle-soa-suite/

I got to a point where I have to connect to a database with this command: sqlplus sys/fusion@//172.17.0.1:1521/soadb as sysdba

But it gives me the following error:

ERROR:
ORA-01017: invalid username/password; logon denied

So what am I doing wrong here? I have not given a username or password during the whole process.

I have not given a username or password during the whole process.

Why not? It is clearly written on that site, right before the step where you try logging in with sqlplus.

  Execute docker exec <<container id>> /opt/oracle/setPassword.sh <<pwd>> to reset password for SYS user.


Update main and second table with a before update trigger for message logging I currently have the following testing schema:
CREATE TABLE userMessage (
    message_id BIGSERIAL PRIMARY KEY,
    creator_id BIGINT NOT NULL REFERENCES "User" (id),
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE userMessageLog (
    message_id BIGINT PRIMARY KEY,
    modified_by BIGINT NOT NULL REFERENCES "User"(id),
    modified_at BIGINT NOT NULL,
    content TEXT NOT NULL,

    foreign key (message_id) references userMessage (message_id)
);

Where who created the message, when and it's actual content. Then another table where I keep track of who modified (can be another user) the message, when and the previous content.
I also created the following trigger for when the message is first created:
CREATE OR REPLACE FUNCTION new_message() RETURNS trigger AS $$
    BEGIN
        INSERT INTO userMessageLog(message_id, modified_by, modified_at, content) VALUES (NEW.message_id, NEW.creator_id, NEW.created_at, NEW.content);
        RETURN NEW;
    END
    $$
    LANGUAGE plpgsql;

CREATE TRIGGER new_message AFTER INSERT ON userMessage FOR EACH ROW EXECUTE PROCEDURE new_message();

Which works as expected. As soon as something is inserted on userMessage a new log entry will appear on userMessageLog.
My question is, when the user or another user edits that message, how can I create a trigger and modify the content, modified_at columns from userMessage table and add a new entry to the userMessageLog? Is there a more better approach to this schema/trigger design?
My only approach resulted in a failed attempt of creating a trigger on BEFORE UPDATE for userMessage which would make the entry on userMessageLogand then UPDATE the userMessage table, but that would result in recursive calls because of the trigger and I also have no way of letting it know who modified it for modified_by and when it was modified for modified_at column.

First of all, if you want to add a new entry log for each insert/update operation, you should change userMessageLog's primary key. Otherwise you'll receive an error message due to duplicity.
Then, you can deal with trigger recursion by using: pg_trigger_depth(). You can get some examples in this questions:

Prevent recursive trigger in PostgreSQL.
Or at this fiddle I've set up using your table definitions.

Having said that, you can find another questions with some discussions about the use of pg_trigger_depth(), with points for or against of using it. Have a look at the answers of Evan Carrol and Erwin Brandstetter in this question:

Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)?

Personally, I'll use a function instead of a trigger.


Manage multiple short-term tables with same schema or use single table for short-term data? My team has proposed to store session state data for each open client session in a table in the database.  The application server creates a new table for each client session as the user logs in via web browser.  The data that needs to be kept for the session is created along with the table, and is not modified (except in rare circumstances).  When the user logs out, the table is dropped.  Orphan tables are purged regularly.

The application server manages these short-term tables.  There won't be any database code that manages the tables or the data.

The data in these tables has relational links to other tables in the database.  The tables will be joined in queries to other tables in the database.

I'd like to find out the pro's and con's of this design, in contrast to others.  For example, all of the same functionality could be implemented in a single table that stored state data for all sessions.  If done this way, there would be one clustered index on the session ID.  Each access to the state data would read all of the data for exactly one session ID.

What are the scalability concerns for creating/dropping multiple tables rapidly?  Assume this occurs on the order of 1K-10K time per hour.

I don't see much benefit of storing Web App Sessions per table:

Creating and dropping tables is a little more expensive than inserting and deleting rows
Depending on how many sessions can exist (current + inactive that have not been cleaned up yet), 10,000 tables is a bit more to deal with than 100,000 rows in a table
Temp tables are not an option. http is a connectionless / stateless protocol, hence the need for sessions ;-). Since temp tables -- local and global -- are automatically dropped when the session in which they were created ends, they wouldn't persist between calls.
Any app-based mechanism that can clean up obsolete tables can just as easily delete obsolete rows (i.e. an OnSessionEnd event and/or job that runs periodically).

IF, you are using ASP.NET, there is a somewhat built-in option to store Session State in SQL Server:

Session-State Modes
Session Management
Potential enhancements to ASPState (some tips from @Aaron B. to improve performance if you are using ASP.NET with the SQLServer Session-State mode)

That might work with JAVA / JSP, but probably not?

If not using ASP.NET, then you can:

use a single table to store the serialized Session object. (this is one of the options mentioned in the question).
OR, possibly use a cache server, such as memcached, redis, or even Windows Server AppFabric Caching. They might be typically intended for longer-term caching, but I see some benefits to them, such as:

they are based in memory, not on disk (i.e. faster, which is why they exist in the first place)
they have built-in mechanisms for key expiration so you probably won't need app-based clean-up logic. Something to at least consider / look into.


minimum privileges required for a user I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server.  There are many other things to do to harden the database but a good place to start is user privileges.
I know the user needs Connect and Create Session privileges.  All the tables, procedures and packages are owned by them.

Are there any other privileges required for basic CRUD operations on their own schema?

There is no need to grant the CONNECT role.  In 10.2, Oracle finally reduced the set of privileges assigned to that role to just CREATE SESSION but in previous versions, that role has many more privileges than the name would imply.

It would be more secure to create one users-- one that owns the tables, procedures, etc. but that does not have CREATE SESSION privileges and one that has appropriate privileges on the various objects that the application can use to connect to the database.  That allows you to do things like prevent the application user from dropping tables or from deleting data from logging tables.

Additionally, the owner of the tables is going to need to be granted quota on whatever tablespace or tablespaces that user's tables are going to be created in.  You could grant the owner the UNLIMITED TABLESPACE privilege but it is more secure to grant them a smaller quota on whatever tablespaces they actually need to use.


dbo.SORT temporary run storage: 148643705716736' What does this number means? I totally understand what this error means and how to resolve.
My question is what does this number (148643705716736) referring to?

Date      5/7/2018 1:36:58 AM
Log        SQL Server (Current - 5/7/2018 2:31:00
AM)
Source        spid125
Message
Could not allocate space for object 'dbo.SORT temporary run
storage:  148643705716736' in database 'tempdb' because the 'PRIMARY'
filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the
filegroup, or setting autogrowth on for existing files in the
filegroup.

That is most likely the allocation_unit_id that was attempting to be allocated when it ran out of space. You can check by running the following query to see if tempdb is currently using similar IDs:

SELECT * FROM tempdb.sys.allocation_units;


Alternative Way to Import SQL to MYSQL Before, I asked a question in here how to linked the SQL to MYSQL. It was a success and I can also query from it. The problem is upon searching why "I can't find SQL SERVER AGENT" in SQL SERVER MANAGEMENT STUDIO EXPRESS EDITION is because it is "EXPRESS". All the ideas that I will be working using SQL Scheduler will unable to continue because the management studio that I have is not capable of it.

Can I have alternative way where in I can create scheduler or scheduled time for Transferring the SQL to MYSQL.

Thank you.

This solution could be of help. For scheduling, you'll have to do what is outlined here. You can connect to an Express instance using MySQL WorkBench as outlined here, but you can't schedule jobs.

Also, have you considered using ODBC to connect and send the data to another (i.e. MySQL) server. You can schedule many of these systems also!

Or try JDBC using SQuirreL SQL or DBeaver? Personally, I can recommend SQuirreL SQL! If you want to schedule a job using Java, try the solution here! Just be aware that your dumps may not be internally consistent unless you are careful about your transactions!


Index on a query with order I have the following table with >1M rows:

CREATE TABLE `wishlist_place` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wishlist_id` int(11) DEFAULT NULL,
  `place_id` int(11) DEFAULT NULL,
  `city_id` bigint(20) DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wishlist_place_unique` (`wishlist_id`,`place_id`),
  KEY `IDX_20B83B35FB8E54CD` (`wishlist_id`),
  KEY `IDX_20B83B358BAC62AF` (`city_id`),
  KEY `new_places_on_wishlist` (`wishlist_id`,`place_id`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3079689 DEFAULT CHARSET=utf8;

And I'm doing the following query to get the newest places added to that wishlist:

SELECT place_id
FROM wishlist_place as wp
WHERE wishlist_id = 113
ORDER BY wp.id desc
LIMIT 0, 9

I thought the index 'new_places_on_wishlist' would work for this query, but it uses the index 'wishlist_place_unique' instead:

id  select_type table   type    possible_keys                                                       key                     key_len ref     rows    Extra
1   SIMPLE      wp      ref     wishlist_place_unique,IDX_20B83B35FB8E54CD,new_places_on_wishlist   wishlist_place_unique   5       const   28      Using where; Using index; Using filesort

UPDATE

I've noticed that the index that works best is:

KEY `new_places_on_wishlist` (`wishlist_id`,`id`,`place_id`)

but I still have to use the USE INDEX statement:

SELECT place_id
FROM wishlist_place as wp
USE INDEX(new_places_on_wishlist)
WHERE wishlist_id = 113
ORDER BY wp.id desc
LIMIT 0, 9

Why does MySQL takes the UNIQUE index first? The order for the indexes are fields in where, fields in order, fields in select?

--

And another related question, if I do a JOIN with the city table to get the wishlists of that city, is the index IDX_20B83B358BAC62AF the best one or it would be better (city_id, wishlist_id)?

Try switching the index to be:

KEY `new_places_on_wishlist` (`wishlist_id`, `id`, `place_id`)

You are effectively doing the part of order by optimization manual that says it can't use the index because of ordering by non-consecutive parts of the key, although their example is different than your situation.

Basically you are currently doing

SELECT key_part2 FROM foo WHERE key_part1=constant ORDER BY key_part3


Not searching GIN index when querying ANY(column) In a Postgres 9.4.11 database, I have a large table of books, which has a one-to-many relationship with authors. Theoretically to improve performance, I've cached the author names in the ebook record for quick searching. Properties of the column authors_cache:

Sample rows:

I've created a GIN index on this column:

CREATE  INDEX  "index_ebooks_on_authors_cache" ON "ebooks" USING gin ("authors_cache")

It's not using the index when searching authors_cache and takes a unacceptable amount of time:

EXPLAIN ANALYZE
SELECT  * FROM "ebooks"
WHERE ('Charles Bukowski' = ANY (authors_cache))
LIMIT 60 OFFSET 0;

Result:

Limit  (cost=0.00..172334.67 rows=30 width=124) (actual time=71.962..10067.070 rows=59 loops=1)
  ->  Seq Scan on ebooks  (cost=0.00..172334.67 rows=30 width=124) (actual time=71.960..10067.015 rows=59 loops=1)
        Filter: ('Charles Bukowski'::text = ANY ((authors_cache)::text[]))
        Rows Removed by Filter: 894504
Planning time: 0.188 ms
Execution time: 10067.112 ms

10 seconds is not an acceptable amount of time. I'm open to design changes, as I'm not "married" to this column, yet.

Use array operators which are supported by your GIN index. The = ANY () construct is not.

SELECT *
FROM   ebooks
WHERE  authors_cache @> '{Charles Bukowski}'  -- array literal
LIMIT  60;

Detailed explanation:

Can PostgreSQL index array columns?

Not sure your de-normalization will buy you much. A standard 1:n implementation with simple btree indexes should perform nicely, too.

If you are going with this approach, I would consider storing an array of integer (int[]) with author_id's. Substantially smaller array column and GIN index. Potential problems with referential integrity are about the same. (Array elements cannot currently have FK constraints. Attempts to implement this in Postgres were unsuccessful so far.)

Oh, and consider upgrading. Performance with GIN indexes has been improved substantially since Postgres 9.4.


How to perform multiple inserts into postgresql database in one query I have an orders, recipient and items tables.

I want to insert data into each of these tables based on the response from the previous query.

insert into recipient(name, address) values(name, address);
insert into orders(recipient_id, total_price, total_quantity) values(recipient_id, 2000, 20);
insert into items(order_id, item, price, quantity, total) values(order_id, item1, 230, 2, 260);
insert into items(order_id, item, price, quantity, total) values(order_id, item2, 500, 2, 1000);

I want to execute this in a single PostgreSQL query.

This is what I have tried

insert into users (fullname, email, phone, address, status)
  values ('Mine Essien', 'davidessienshare@gmail.com', '08167462431', 'Andela Epic tower Lagos')
  returning id as recipient_id
)

new_order AS(
  insert into orders(user_id, recipient_id, quantity, total_cost)
  values(1, recipient_id, 5, 9500)
  returning id as order_id
)
new_item as (insert into items(order_id, item, quantity, price, total) values(order_id, 'Pepper soup', 2, 2500, 5000), values(order_id, 'Ishiewu', 1, 5000, 5000), values(order_id, 'Fried yam', 2, 2000, 4000);

It seems your tables are defined with a serial or identity column and you want to use the generated value from one insert as a foreign key in the next statement. This can indeed be done using a data modifying CTE combined with the returning clause:

with new_recipient as (
  insert into recipient
    (name, address)
  values
    (name, address)
  returning recipient_id
), new_order as (
  insert into orders
     (recipient_id, total_price, total_quantity)
  values
     ((select * from new_recipient), 2000, 20)
  returning order_id
)
insert into items
  (order_id, item, price, quantity, total)
values
  ((select * from new_order), 'item1', 230, 2, 260),
  ((select * from new_order) , 'item2', 500, 2, 1000);

However this could also be done using three different insert statements:

into recipient
  (name, address)
values
  (name, address);

insert into orders
   (recipient_id, total_price, total_quantity)
values
   (lastval(), 2000, 20);

insert into items
  (order_id, item, price, quantity, total)
values
  (lastval(), 'item1', 230, 2, 260),
  (lastval(), 'item2', 500, 2, 1000);

The function lastval() returns the most recently generated sequence number. So in the second insert that would be the recipient_idand in the third statement it would be theorder_id`.


Shell script for backup binlog files for every 8 hours in MySQL I'm writing a shell script for point in time recovery for MySQL Instance. Our requirement is backing up Binlog for every 8 Hours (Daily 3 times) using mysqlbinlog and it should backup binlog starting from last day full database backup time to current time.

Suppose if full database backup happens at 2:00 AM morning and binlog backup happens at 6 AM, 12 PM and 6 PM, then first binlog backup should backup from 2:00AM to 6:00 AM etc.

After completing daily full database backup, it will store output of "Show master Status" in a file called "master_status.txt" and I'm calling it from binlog backup script as below, but whenever there are more than 1 binlog file generated b/w full db backup and binlog backup then script is not taking binlog backup for all binlogs.

binlog=`cat master_status.txt | awk '{print $1}'`
dat="$(date +'%d_%m_%Y_%H_%M_%S')"
mysqlbinlog -u$DB_USER -p$DB_Password "$bin_log" --to-last-log >> "binlog_file_$dat".sql

Version : 5.5.x
Engines: InnoDB and MyISAM

For those who still need this. May try using binlog puller module by percona folks. It works perfect and you may add a watchdog for it.


How to unify aggregating function with Postgres per different group by my_data table is like this (one row example):

 total_cost | total_exp | ind1 | ind2 | ind3 | flag
------------+-----------+------+------+------+------
 1000       | 2000      | A    | A1   | A2   | F

And I have a function like this:

CREATE OR REPLACE FUNCTION create_my_aggregation() RETURNS VOID AS $$
        DECLARE
            import_date date;
        BEGIN
            FOR import_date IN
            SELECT generate_series('2005-01-01'::date, '2015-04-01'::date, '1 month'::Interval)
            LOOP
                insert into my_timeseries

                       select
--                       ind1 as ind,
--                       ind2 as ind,
                       ind3 as ind,
                       import_date,

                       -- size

                       array_agg(total_cost) filter(where flag = 'F' and total_exp > 0) as f_cost_a,
                       array_agg(total_cost) filter(where flag = 'S' and total_exp > 50) as s_cost_a,
                       array_agg(total_cost) filter(where flag = 'D' and total_exp > 100) as d_cost_a,
                       array_agg(total_cost) filter(where flag = 'T' and total_exp > 20) as t_cost_a,

                       from my_data
                       where date_trunc('month', date_of_transfer) = date_trunc('month', import_date)
--                       group by ind1, import_date;
--                       group by ind2, import_date;
                       group by ind3, import_date;
            END LOOP;
        END
$$ LANGUAGE plpgsql;

The expected outcome is an array aggregation with the ind1,ind2,ind3 renamed to ind and the relative aggregations.

The problem with that functions is that I have to switch (and run 3 times) select ind1 as ind....group by ind1,import_date.

Is there a way to unify it in one go?

Use grouping sets:

select
  coalesce(ind1, ind2, ind3) as ind,
  ...
group by grouping sets (
  (ind1, import_date),
  (ind2, import_date),
  (ind3, import_date));

But its was introduced only in 9.5 version.


SQL Server Agent Job - Adding Version Number I would like to know how to add a version number to a SQL Server Agent job without using the description field, and with it being an attribute of the job.

A similar question was asked at StackOverflow Sql Server Agent Job - Adding Version Number  but that question did not include the criteria of not using the description field.  (accepted answer implies the description field was viable solution)

I have a script to update jobs, and I want to capture version information without overwriting any existing descriptions and being able to search for current version on a single field (without combing existing/new comments with version info)

I can use sp_update_job to update most fields, but the only one not in use that will take strings is @category_name and it is limited to values in sp_help_category. (Edit following day >) It can be updated with sp_add_category but that presents the value in the GUI drop down as available for all jobs. Possible, but suboptimal.

I can use sp_update_job this would be suboptimal as steps are parts of jobs.  I don't see any reasonable solutions in there.  I did consider creating a step named "Version 1.0.0" or similar but that was wrong on many levels. EDIT After much research and testing it became clear that this was the optimal approach.

You can not use sp_addextendedproperty to hold a version number in a job. Doing so would require changing the value of 'level1_object_type' to 'JOB' which is not an option. Source

I can use a table to list modifications. But that would not be an attribute of the job, and is subject to human error insertion.  Possibly I might use a table, where a hash of the command field (@command) and/or schedule is used as a unique identifier.  This would/should be unique to job version, while not a direct attribute it would be a derived attribute.

Solution to apply to SQL Server 2008R2 and later by preference, SQL Server 2012 and later by requirement.

This is a very interesting question. I think I agree with @Gareth Lyons in some way. My suggestion is that you append your version information in the step name of the first job step (after all, all jobs have at least a job step to be meaningful)
So what you can do is like this:
For the first time, say my job [test_job] has the following steps

Now I can run the following code to append the version info to the 1st step name
-- initially appending version name to step 1 name
declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 1.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name + ' - ' + @ver --adding version string to the step 1 name
from msdb.dbo.sysjobsteps s
inner join msdb.dbo.sysjobs j
on s.job_id = j.job_id
and j.name=@job_name
and s.step_id = 1;

exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

After running the script, you will see the job step name changed to the following

Later, if we need to update the version, we can easily operate on the 1st step name as the following
-- updating version name later when it is necessary

declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 2.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name
from msdb.dbo.sysjobsteps s
inner join msdb.dbo.sysjobs j
on s.job_id = j.job_id
and j.name=@job_name
and s.step_id = 1;

-- replace old version with new version number
set @step_1st_name = substring(@step_1st_name, 1, charindex('<V', @step_1st_name)-1) + @ver;
exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

If we run the script, we can see the job 1st step name is changed to the following

It is also very easy for you to extract the version number from any job's first step name.
I hope this may help you.


Inserting documents via standalone connection to MongoDB replica set I have a MongoDB standalone server, which I am converting to a replica set by adding other servers. However, some clients are still connecting to the original server without specifying any other servers or the ?replicaSet= parameter in the URI, and some of these clients insert new documents.

What happens to the documents that are inserted? Will they be propagated correctly to the rest of the replica set? What happens if the original mongo switches to Secondary status and the clients keep trying to insert documents?

As long as your clients are connecting to the current replica set primary they will be able to work with documents as usual and those writes will be replicated. This is useful as a transitional approach when upgrading a standalone deployment to a replica set.

However, without a replica set connection string your clients will be unaware of any changes in the replica set status. If your original mongod changes role from primary to another state those clients will be unable to write data: the server will return a "not master" error which will be mapped to an appropriate exception by your MongoDB driver.

For more information on expected behaviour for officially supported MongoDB drivers you may want to review the Server Discovery and Monitoring (SDAM) spec. In particular, the Rationale section addresses some implementation choices and common questions on topology discovery and monitoring.

Note for completeness: if you were upgrading to a sharded cluster you would definitely have to change your connection string. In a sharded cluster all client write operations should go via a mongos router.


Query with column data for a max In Postgres, I have a table like this:

    Column    |            Type             |                                 Modifiers
--------------+-----------------------------+--------------------------------------------
 problem_pk   | bigint                      | not null default nextval(...)
 version      | bigint                      | not null default 1
 problem_type | e_problem_type              | not null
 building_fk  | bigint                      | not null
 dpoint_fk    | bigint                      |
 native_name  | character varying(256)      |
 event_date   | timestamp without time zone | not null default now()
 count        | bigint                      |

and e_problem_type looks like this:

\dT+ e_problem_type;
                                         List of data types
  Schema  |      Name      | Internal name  | Size |             Elements             | Description
----------+----------------+----------------+------+----------------------------------+-------------
 ecorithm | e_problem_type | e_problem_type | 4    | nil                             +|
          |                |                |      | empty_string                    +|
          |                |                |      | nan                             +|
          |                |                |      | dash                            +|
          |                |                |      | other_bad_data                  +|
          |                |                |      | unmapped                        +|
          |                |                |      | missing_header                  +|
          |                |                |      | low_data_volume                 +|
          |                |                |      | attachment_with_no_valid_headers |

And I have a query like this:

select
    problem_type, native_name, max(event_date)
from
    point_problem_history
where
    building_fk = 3
and
    problem_type = 'nil'
group by
    native_name, problem_type
order by native_name;

I want to add the "count" field to my output, but have been unsuccessful in figuring out how to do this.  If I add it, I have to group by it and I end up with duplicate native names.  What I want is, for each native name,  the value of the 'count' field for the most recent event_date for that native name and problem type = (the string 'nil').  I've tried a lot of different queries and subqueries with no success.  Any and all help appreciated.

You can use DISTINCT ON:

select distinct on (native_name)
    'nil' as problem_type, native_name, event_date, count
from
    point_problem_history
where
    building_fk = 3
and
    problem_type = 'nil'
order by
    native_name, event_date desc ;

which does exactly what you want. Similar to GROUP BY, it will keep one row per distinct value of native_name but - differently that group by - you don't need to use max and min functions. The row selected will be the first according to order by event_date desc, so the maximum event date per native name.


cascade delete from child table I have a existing database with several tables, unfortunately there ins't any data load functionality provided for said database and I am trying to rectify this issue.

I have an insert function that processes and inserts the data we need as needed into the relevant tables and sets up the interlinking fkeys that link all the new data together.

the insert statement is set up to return the new primary keys of all the new rows its created a records table to allow rollback. What I want to do is set up a function where if a row in the records table is deleted it will also delete all its referencing rows in the rest of the database (without having to change all the other tables)

example : http://rextester.com/SKJUJ87661

basically when I run delete from t where tid = 1; I want corresponding data in t(upload records) and u(tables where data was created) to be deleted and v(existing reference tables) untouched

and yes, I know I can just run delete from u where uid = (select t.uid from t where t=1); for the relevant tables, but I'm looking for a more elegant solution then writing a massive delete statement (in the actual system t references 18 rows)

update

using triggers i can do this http://rextester.com/MCDGR84399

but how do I make the trigger scroll through all the Fkeys that t is linked to instead of just one

update = 2

my basic question is there a prettier way to do this :

CREATE OR REPLACE FUNCTION delete_cascade() RETURNS trigger AS
$$BEGIN
   delete from u
   where u.uid = OLD.uid;
   delete from c
   where c.cid = OLD.cid;
   delete from b
   where b.bid = OLD.bid;
   delete from a
   where a.aid = OLD.aid;
   return OLD;
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER t_cascade
   AFTER DELETE ON t FOR EACH ROW
   EXECUTE PROCEDURE delete_cascade();

example code http://rextester.com/UAZU30980

You can cascade action by defining a Foreign Key to those tables.
Here is an example from Postgresql documentation

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE CASCADE,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Here, deleting product or order deletes all corresponding items in order_items. You can apply the same for your case.

In case you want to delete all items in primary table and delete corresonding record, it is possible with Truncate cascade. Something like

TRUNCATE TABLE products CASCADE

This will delete all data in products plus all dependent tables.

Apart form these two, you have to somehow manage the deletion yourself. I would in that case use scripting language, and I don't know much about PL/SQL


SQL Server Plan cache size only 4GB on a server with 500GB RAM I have a SQL server 2016 SP1 that has been up and running over a year and using the query below, I see that the plan cache size is approx only 4GB where as the physical server has 500GB and there is no max memory setting on the sql instance and there is only one sql instance on the server and no other application.

My understanding is based on how sql server sizes the plan cache using the formula below, the plan cache on my server should be atleast 30GB or more but sql server allocated only 4GB and i have a mix of SP's, sp_executesql and exec(sql) queries on the db. Can experts please help me understand why the plan cache would be so small. Thanks!

SQL Server 2005 SP2 and higher
     75% of visible target memory from 0-4GB
    + 10% of visible target memory from 4GB-64GB
    + 5% of visible target memory > 64GB

SELECT
        [objtype] AS [CacheType],
        COUNT_BIG(*) AS [Total Plans],
        SUM(CAST([size_in_bytes] AS DECIMAL(18,2)))/1024/1024 AS [Total MBs],
        AVG([usecounts]) AS [Avg Use Count],
        SUM(CAST((CASE WHEN [usecounts] = 1 THEN [size_in_bytes] ELSE 0 END)
        AS DECIMAL(18,2)))/1024/1024 AS [Total MBs – USE Count 1],
        SUM(CASE WHEN [usecounts] = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
    FROM [sys].[dm_exec_cached_plans]
    GROUP BY [objtype]
    ORDER BY [Total MBs – USE Count 1] DESC;

SQL Server uses memory only as it needs to... with in-memory technology as the exception, it won't jump to using up all of the memory indicated in the configuration for max server memory (or even min server memory), until you actually request data or plans that require memory.

So if SQL Server has not been up for a long time (maybe you restart every night, or recently installed patches), it won't use that memory until your workload makes it happen. And it may never happen, depending on your available memory and the size of your data. As an example, if you allocate 30GB to SQL Server, but the sum of your data is only 10GB, you're unlikely to see all of your max server memory ever being used, regardless of uptime.

Several of these aspects of SQL Server memory management are widely misunderstood.


How to make count in join I have three tables.

user
user_preferences
user_subscription_plan

User could have no user_subscription_plans

I would like to make a query that just gives a count with users that do not have a single entry in the user_subscription_plan

I have come up with this query:

SELECT
    user.id,
    COUNT( DISTINCT usp.user_id) AS user_sub_plans
FROM user
RIGHT JOIN user_preferences AS up ON up.user_id = user.id
LEFT JOIN user_subscription_plan AS usp ON usp.user_id = user.id
AND user.inactive = 0
GROUP BY 1

It returns the result like this:
**Result of the query **

id   user_sub_plan
---  -----------
100  0
101  1
102  1
103  0
104  1
105  0

Which means user with the id of 100 has no entries, and user 101 has entries.

If we observe the result set of the query, it shows there are three users that have entries.
How could I expand this query, to return me user count of three?
Something like this:

user_count
    ---
3

The simplest way would be to use a subquery:

SELECT count(*) AS user_count FROM
(
    -- Original query, untouched
    SELECT
        user.id,
        COUNT( DISTINCT usp.user_id) AS user_sub_plans
    FROM user
    RIGHT JOIN user_preferences AS up ON up.user_id = user.id
    LEFT JOIN user_subscription_plan AS usp ON usp.user_id = user.id
    AND user.inactive = 0
    GROUP BY 1
    -- end of original query
) AS original_query
WHERE user_sub_plans >= 1;

If you use recent version of MySQL, you can also use the WITH construct if it looks simpler to you:

WITH original_query AS ( -- original query ...)
SELECT count(*) AS user_count
FROM original_query
WHERE user_sub_plans >= 1;

Given you want to do a nested count, I don't see an obvious way to do it without a subquery/WITH (nested querying). However, if you would only want to filter our some columns, without counting again, you can use HAVING, which is basically a post-GROUP BY, late executing WHERE:

SELECT
    user.id,
    COUNT( DISTINCT usp.user_id) AS user_sub_plans
    ...
    GROUP BY ...
    HAVING user_sub_plans >= 1;

Outside of your query, there are a couple of not-wrong-but-weird decisions you took, like the RIGHT JOIN, and the AND user.inactive = 0 as part of the ON clause. I am not saying those are bad, but pointing you to double check those are things you want to do, and not unintentional bugs.


What does the alias after DELETE mean? What does the alias after DELETE mean?
E.g. I have a query:
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p2.Id < p1.Id

What does it mean DELETE p1?
I understand it as follows. First we find a cartesian product of the Person table with itself. Then we remove all the rows where the Email from the both tables (p1 and p2) is not the same. Then we remove all the rows where the Id from the p2 is greater or equal than the Id from p1. Now we are left with some rows. We pick all the Ids from the p1 part of the left rows and remove all the entries from the initial Person table, which has the Id contained in the picked Ids.
Is my understanding correct?
If it is correct, then DELETE p1 means delete all entries in Person where Id is in the resulting p1 part of the rows.

Imagine that the deletion is a process which consists from 2 steps. At the 1st step the server performs according SELECT * selection, and marks the rows which must be deleted. At the 2nd step it deletes marked rows.
DELETE p1 in your query means that only rows selected from a table aliased as p1 must be marked for deletion. If you'd use DELETE p1, p2 or simply DELETE FROM then selected rows from all table copies 'd be marked.


Query State hangs on sending data For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why.
This is the table creating script:

CREATE TABLE IF NOT EXISTS `esp_game` (
  `gameID` int(10) unsigned NOT NULL,
  `gameType` tinyint(3) unsigned NOT NULL,
  `mapID` tinyint(3) unsigned NOT NULL,
  `createDate` int(11) NOT NULL,
  PRIMARY KEY (`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`gameID`,`summonerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `gameTypeMap` (
  `gameTypeID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `gameTypeName` varchar(50) NOT NULL DEFAULT '',
  `gameTypePortugueseDesc` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`gameTypeID`),
  UNIQUE KEY `gameTypeName` (`gameTypeName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

This is the query that gets stuck:

SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;

Extra info:

esp_game had about 10M entries (~1 Gig), esp_gameplayer about 100M (~7 Gigs) entries and gameTypeMap 10.

Explain before the deletion:

EXPLAIN SELECT g.gameID, g.gameType, g.mapID, g.createDate, gt.gameTypePortugueseDesc FROM esp_game g INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129 INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID ORDER BY g.createDate DESC LIMIT 10;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows    | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
|  1 | SIMPLE      | g     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 | 7706280 | Using filesort |
|  1 | SIMPLE      | gt    | eq_ref | PRIMARY       | PRIMARY | 1       | teemo.g.gameType     |       1 |                |
|  1 | SIMPLE      | gp    | eq_ref | PRIMARY       | PRIMARY | 8       | teemo.g.gameID,const |       1 | Using index    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+---------+----------------+
3 rows in set (0.00 sec)

Server configuration:

[mysqld]
(...)
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 1536M
innodb_lock_wait_timeout = 25

The only way I've managed to make it work again is truncate everything from those tables - Thankfully I'm able to do that, but soon I won't be able to truncate everything anymore!
Any Idea how to solve this problem?

First, look at the EXPLAIN plan again.

The first line says that the Query Optimizer will do the following:

Full table scan of at least 7,706,280 rows without using any keys (thus the root cause of the Sending data query state)
Each row in passes through from esp_game will do a lookup to esp_gameplayer and gameTypeMap

I would like you to try the following:

Refactor Your Query

Your original query is:

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    esp_game g
    INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

If the summonerID drives which gameIDs to retrieve, then the query needs to have esp_gameplayer be the lead table in the query

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    (SELECT gameID FROM esp_gameplayer WHERE summonerID=401129) gp
    INNER JOIN esp_game g ON g.gameID = gp.gameID
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

Change the esp_gameplayer PRIMARY KEY

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To reverse the PRIMARY KEY do the following:

CREATE TABLE IF NOT EXISTS `esp_gameplayer_new` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO esp_gameplayer_new SELECT * FROM esp_gameplayer;
ALTER TABLE esp_gameplayer RENAME esp_gameplayer_old;
ALTER TABLE esp_gameplayer_new RENAME esp_gameplayer;

Since you asked for a specific summonerID, reversing the PRIMARY KEY gathers all the corresponding gameIDs faster for summoner 401129

Make the changes, rerun the EXPLAIN plan. It should improve a great deal.

Give it a Try !!!


The CPU utilization spikes to 80+ even with just one user on Amazon RDS (MYSQL INNODB) These are my RDS instance configs

Instance Class: db.m3.xlarge
Storage 40 GB
Engine MySQL 5.6.34
Multi AZ : No

I have a the node.js based backend hosted in EC2 instance and also have a cloud front with 10 minutes cache window which hosts one of our intense query with takes around 20-30 seconds (i know this is intense and i am optimizing this) but on the mean time with say minimal number of user say 2-3 the CPU spikes to around 85-100% and i am not able to figure out why. any suggestion and ideas are much appreciated.

What you describe is the expected behavior.

An m3.xlarge has 4 vCPUs (virtual CPUs -- and having 4 of these typically means two cores, each with two hyperthreads).  100% utilization means all 4 vCPUs are completely busy, while 75% means 3 are busy, 50% means 2, and 25% means 1.  (Below 100%, this doesn't necessarily mean e.g. exactly 3 are working and one is idle, but refers to the total idle time of each vCPU, divided by the number of vCPUs, subtracted from 100%).

In MySQL Server, any single query can use only the equivalent of one vCPU (it's not physically locked to one specific vCPU by the OS, but it can't consume more than the equivalent of one) ... so with 2 or 3 long running queries, 50%-75% CPU utilization would be exactly normal, assuming the thing that causes the query to require a long amount of time in the first place is the amount of CPU work required to process it -- anything less than that means the CPU cycles aren't the primary factor that causes the query to be long-running.  (The usual other cause of long-running queries is the amount of disk I/O that must be done to satisfy the query.  Performance of queries is typically bounded by available disk or available CPU, with the nature of the query, and the size of the buffer pool relative to the working data set being significant factors).

If CPU cycles are indeed the primary cause for the query to require so much time, you can see why the numbers on the CPU are what they are -- it would not make sense to artificially throttle down the query to use even less, thus making it even slower, still... but this is what would have to happen.

As it stands, MySQL will consume the resources it needs, as quickly as the operating system will allow it to, in order to execute any individual query as quickly as possible. If you had more long running queries running at a given moment in time than there are vCPUs on the system, and the queries are CPU-bound, the OS thread scheduler is the entity responsible for allocating each of them a reasonable share of whatever is available --so on a 4 vCPU system, with 4 long-running queries already running, if a 5th query starts, it doesn't sit and wait for one of the others to finish -- it actually would slow each of them down by about 20% as it claimed its share of the total available CPU cycles -- all running queries get a share, based on how much work they are trying to do.

On a machine with only 1 vCPU, you'd find that only one single long-running query would spike the utilization to 100%, and this also would be completely normal, if the need for CPU cycles is the main factor influencing the query's overall execution time.


Change column values but keep order in MySQL Lets consider I have a table:
CREATE TABLE student (
id int,
 name varchar(128)
);

INSERT INTO student (id,name) VALUES
(1,'John'),
(2,'Bob'),
(3,'Alice'),
(4,'Don'),
(5,'Eve');

I need the 'names' column to exchange places with next one: 1 with 2, 3 with 4 and etc, but keep the order of 'id'. The selected output should be something like this:
id  names
1 | Bob
2 | John
3 | Don
4 | Alice
5 | Eve

Is there an effective way to write query that would give me expected result?

This sounds like an activity that a college teacher might come up with to see if someone understands math and has a little bit of imagination, as this sort of thing would almost never be required outside of an educational setting ... and I say this as someone who works in academia.
That said, one way this could be accomplished would be with a subquery that first determines the "proper" id. An UPDATE statement can then be used to match on the "proper_id" to fix the data. Here is the sub-query:
SELECT CASE WHEN id % 2 = 0 THEN id - 1 ELSE id + 1 END as proper_id,
       name
  FROM Student
 ORDER BY id

I'll leave the rest to you


postgres single rows vs array I have an application that saves gps data every second and the user is able to query a variable timespan of data at once. So a select statement potentially needs to return many thousand rows. How is a small EC2 instance going to handle such requests? Would it help if I saved the coordinates of several hundred rows as an array in a single row like this:

instead of single rows:

timestamp, lon, lat
1,11,12
2,11,12
3,11,12
4,11,12
...

batches:

timestamp-start,timestamp-stop,lon,lat
1,300,[11,43,23,...],[11,43,23,...]
301,600,[11,43,23,...],[11,43,23,...]

or is the performance equal for many rows with little data and few rows with a lot of data.

Regardless of EC2,

do what makes sense for the data
don't pre-optimize

Columns are not for similar-data that serve the same purpose. If you're writing entries use rows. If you're describing them use columns. Let aggregates do the voodoo of taking the data from the rows and rolling it up to something useful.

Now for your application, if I was doing GPS stuff, I would not be using separate columns for lat and long but instead I would use

PostGIS and the GEOGRAPHY type,
or, if not possible, the point(long, lat) (at least it supports <@> (distance)) as well as some other useful operators and you can upgrade later


Forbid user to query internal SQL server information I'm trying to forbid database user to get SQL server internal information.

Script that creates DB user:

CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: sys TO [testuser]
DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: INFORMATION_SCHEMA TO [testuser]
GRANT SELECT on SCHEMA::dbo to [testuser]
DENY INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: dbo TO [testuser]

The problem is that user is still able to run queries like these:

EXECUTE AS USER   = 'testuser';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token

Do you have any ideas?

I am afraid that there is no way to deny anyone permissions to see that kind of information. However, impersonating a database user is not enough to see the effects on server-scoped DMVs or functions such as sys.login_token: you need to impersonate a login instead.

CREATE LOGIN ZeroCool WITH PASSWORD = 'ICantSeeCoolInfo', CHECK_POLICY = OFF;
GO

EXECUTE AS LOGIN = 'ZeroCool';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token
GO

REVERT;

Results:

------------------------------------------------------------------------ -----------------------------------------------
master                                                                   Microsoft SQL Server 2012 - 11.0.5613.0 (X64)
    May  4 2015 19:05:02
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

(1 row(s) affected)

--------------
Shared memory

(1 row(s) affected)

principal_id sid                                  name         type          usage
------------ ------------------------------------ ------------ ------------- ---------------
301          0x34FA109FE0F7694E866DF0F9E82BB99B   ZeroCool     SQL LOGIN     GRANT OR DENY
2            0x02                                 public       SERVER ROLE   GRANT OR DENY

(2 row(s) affected)

I don't see how preventing users from seeing those info will help you tighten security: just implement security best practices and you should be ok.


Move content between filestream containers I have a server running SQL Server 2012 with Multiple Filestream containers on a single table.

I need to split one of the existing filestream containers into two parts by creating a new filestream container and moving the data across.

How can reliably move content between filestream containers?

I have the first container set to not grow any further but how can I move the files to another container without writing to a whole new table? Is there a way to do an UPDATE statement that will cause SQL Server to write the file to disk again? - I have thought of appending an additional byte of data and then removing it afterwards to make SQL Server write the content as new files, but is there a better way?

According to Paul Randal (who is as reliable as they come) there is no simple way to do this and you essentially have to recreate the table in a new filegroup that is pointed to your new filestream location.  The link is there so you can see for yourself, but this looks like the only real alternative.  It is not an ideal one, obviously but it should get you the end result that you need.   But not in a simple fashion, I am sorry to say.