WITH x AS
(
SELECT
    Zone,
    Status,
    CASE WHEN
             COALESCE(LAG(Status)
             OVER (PARTITION BY Zone ORDER BY Zone, [From]), '') <> Status
         THEN 1 ELSE 0 END AS LS,
    [From],
    [To]
FROM
    t
), Y AS
(
SELECT
    Zone,
    Status,
    SUM(LS) OVER (PARTITION BY Zone ORDER BY Zone, [From]) Grp,
    [From],
    [To]
FROM
    x
)
SELECT
    Zone,
    Status,
    MIN([From]) [From],
    MAX([To]) [To]
FROM
    y
GROUP BY
   Zone, Status, Grp
ORDER BY
   Grp;

Zone | Status | From                | To
:--- | :----- | :------------------ | :------------------
z1   | Open   | 01/01/2015 00:00:00 | 10/01/2015 00:00:00
z1   | Closed | 10/01/2015 00:00:00 | 20/01/2015 00:00:00
z1   | Open   | 20/01/2015 00:00:00 | 26/01/2015 00:00:00

db<>fiddle here


Remove characters from PostgreSQL database causing encoding errors I have a PostgreSQL 10 database that uses WIN1252 encoding.

One of my columns has values that cause conversion errors when running a select from pgAdmin 4:

SELECT myfield FROM mydb.myschema."MYTABLE"

Returns:

ERROR:  character with byte sequence 0x9d in encoding "WIN1252" has no
equivalent in encoding "UTF8"
SQL state: 22P05

Setting the client encoding to WIN1252 in pgAdmin 4 causes it to drop the connection to the database (I remember reading somewhere that pgAdmin 4 has issues with setting the client encoding, but track down the link).

Using psql and setting the client encoding to WIN1252, selecting one of the offending features:

SELECT myfield
FROM mydb.myschema."MYTABLE"
WHERE oid = 12345

Returns:

â€œA sample comment

Looking at the data that was originally imported into the DB, it appears that “curly quotes” were incorrectly imported.

Is there a way to remove these characters from the column - either replacing them with regular quotation marks, or simply deleting them?

I think you're reading it backwards. Instead try to SET the client_encoding to utf8;.

SET CLIENT_ENCODING TO 'utf8';


Insert a file into PostgreSQL BYTEA field using PHP The previous answers have been horribly complex for the scope of this project.
I want to take the $_POST result as a variable and insert it into a BYTEA field of a table.

$_SESSION['username'] = $username;
$file = $_POST["file"];

file is the file itself submitted from the form on the previous page

pg_prepare($conn, "register_user_info", 'INSERT INTO fileDB.assignment(pawprint, name, number, section, file) VALUES ($1, $2, $3, $4, $5)');
pg_execute($conn, "register_user_info", array($username, "1", "1", "1", $file));

I need to insert the file itself into the file column of my database. How can I make this happen?

Your options are:

Use PDO, which properly supports binary parameters; or
Use pg_escape_bytea with string substitution into the query text.

See https://stackoverflow.com/q/17121791/398670 .


Connection to Amazon RDS MySQL instance fails with `init_connect command failed` I'm attempting to open a connection from my Amazon RDS instance to my local dev machine. I added my IP to the security policy in Amazon's panel (read: whitelisted myself in the firewall) and added my user in MySQL:

grant usage on bar.* to foo@0.0.0.0 identified by "foobar";
grant all privileges on bar.* to foo@0.0.0.0;

Assume that 0.0.0.0 is actually my IP.

I then attempt to connect via the command-line MySQL client:

[wk@batman ~]$ mysql  --host=1.1.1.1 --user=foo -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 66287753
Server version: 5.6.17-log

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    66287829
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 66287829 to db: 'unconnected' user: 'foo' host: 0.0.0.0 (init_connect command failed)

I'm not sure what could be causing this error - the results I could find on Google pertained to database replication. Does anyone have any idea what's wrong?

This is because the user does not have execute grant global, to fix it

GRANT EXECUTE ON *.* TO user_name;


How to select id of father from the child id recursively I have in the field cod_categorizacao_pai the ID of the father, and I need to mount a select that just enter an ID and it will list the paternal IDs of this ID until it reaches the parent of all. How do I set a select to do this? Will I need a procedure? I am using PL / SQL.

The table:

CREATE TABLE [dbo].[menu]
(
  id INT PRIMARY KEY
, name NVARCHAR(20)
, parent_id INT REFERENCES [dbo].[menu] ([id])
);

Suppose I am using the records below:

INSERT INTO menu VALUES (1,'Main Product',NULL);
INSERT INTO menu VALUES (2,'Next Main Product',NULL);
INSERT INTO menu VALUES (3,'Addon Product',1);
INSERT INTO menu VALUES (4,'Addon Product',1);
INSERT INTO menu VALUES (5,'Sub-Addon Product',3);
INSERT INTO menu VALUES (6,'Sub-sub-Addon Product',5);
INSERT INTO menu VALUES (7,'Addon Next Main Product',2);

I would like to have the following listing if I passed ID '6' as a parameter:
1,3,5,6

That is not Oracle syntax. Below is Oracle syntax:

drop table menu purge;
CREATE TABLE menu
(
  id INT PRIMARY KEY
, name NVARCHAR2(40)
, parent_id INT REFERENCES menu (id)
);

INSERT INTO menu VALUES (1,'Main Product',NULL);
INSERT INTO menu VALUES (2,'Next Main Product',NULL);
INSERT INTO menu VALUES (3,'Addon Product',1);
INSERT INTO menu VALUES (4,'Addon Product',1);
INSERT INTO menu VALUES (5,'Sub-Addon Product',3);
INSERT INTO menu VALUES (6,'Sub-sub-Addon Product',5);
INSERT INTO menu VALUES (7,'Addon Next Main Product',2);
commit;

select listagg(id, ',') within group (order by id) from menu
connect by id = prior parent_id
start with id = 6;

LISTAGG(ID,',')WITHINGROUP(ORDERBYID)
-------------------------------------
1,3,5,6


What slows down this histogram query? I have a table my_link_t with columns t.weight type number and categorical speedcat type integer values from 2 to 8. I want to split data in buckets from min=0, max=0.6, step = 0.001 and build a 3D plot to see weight distribution per each category.

initial data looks like
weight speedcat
0.0234 2
0.8643 6
0.1854 7
(hundred million more entries with weight between 0 and 0.6 and speedcat between 2 and 8)

These queries return correct results and complete in less than a minute each:

--repeat for each variable. Here we loook for speedcat =8
--It takes seconds to run this query
create table   histogram_tbl_8  as (
  select  ttt."Start" as bucket_index, ttt.hist_row as bin8  --here
  FROM ((
  SELECT  Bucket*1 "Start" , Bucket "End", Count(Bucket) hist_row
  FROM (SELECT WIDTH_BUCKET (weight, 0, 0.6, 601) Bucket FROM my_link_t  where speedcat=8)
      GROUP BY Bucket ORDER BY Bucket ) ttt   )   );

The above query is repeated seven times for speedcat in range  2..8

--if a bin is empty populate it with zero, don't skip it.
create table histogram_output as (
select  tr.bucket_index,
    CASE
        WHEN 1 > (select count(*) from histogram_tbl_2   htm where htm.bucket_index = tr.bucket_index)   THEN 0
        ELSE (select htm.bin2 from histogram_tbl_2   htm where htm.bucket_index =  tr.bucket_index and rownum = 1)
     END
     as b2,
     --same for b3-b7
      CASE
        WHEN 1 > (select count(*) from histogram_tbl_8   htm where htm.bucket_index = tr.bucket_index)   THEN 0
        ELSE     (select htm.bin8    from histogram_tbl_8   htm where htm.bucket_index =  tr.bucket_index and rownum = 1)
     END
     as b8
     FROM  (SELECT LEVEL as bucket_index, 0 as b2, /* 0 as b3, 0 as b4, 0 as b5, 0 as b6, 0 as b7, */ 0 as b8  FROM DUAL CONNECT BY LEVEL < 600)  tr
     )

Finally

      select sum(b2), sum(b3),sum(b4),sum(b5),sum(b6),sum(b7),sum(b8) from histogram_output
       select bucket_index,
              round(b2 * 1000000 / 12921) as  b2, --normalize so that total is 1000000 ppm
              -- repeat for b3-b7
              round(b8 * 1000000 / 6262) as  b8 --normalize so that total is 1000000 ppm
         from histogram_output

I get a table like

bin_end speedcat_2 speedcat_3 speedcat_4 .. speedcat_8
0.001
0.002
..
0.599
0.600

Showing ppm of objects within this category and this bin
Now, when I combine queries in

-- DONT USE THE EXAMPLE BELOW - it is ineefficient  (runs 2+ hours instead of seconds for the method above)
       SELECT  Bucket_2*1 "Start" , Bucket_2 "End",
       Count(Bucket_2) as b2,
       --same for b3 .. b7
       Count(Bucket_8) as b8

      FROM
      (
          SELECT WIDTH_BUCKET (t2.weight, 0, 0.6, 601) Bucket_2,
                 --same for t3,.. t7
                 WIDTH_BUCKET (t8.weight, 0, 0.6, 601) Bucket_8

           FROM (select weight from my_link_t where  speedcat = 2) t2,
           -- ..speedcat = 3) t3, .. speedcat = 4) t4, etc
            (select weight from my_link_t where  speedcat = 8 ) t8

      )
      GROUP BY Bucket_2 ORDER BY Bucket_2
      ------

Query runs several hours (some 500 times longer runtime than individual queries) until I kill it. Books recommend to do all data slicing in SQL. This example suggests that loading data to Java and slicing it there might be better in case of complex queries.

What can cause the difference?

Short Answer

Your 7-way Cartesian JOIN is going to have some serious performance problems.

Long Answer

Think in Sets.

I am assuming that your Data Set needs to contain:  speedcat, bucket_index, count(*)

The simple solution for that Data Set is straight forward:

select t.speedcat
  , WIDTH_BUCKET (t.weight, 0, 0.6, 601) as bucket_index
  , count(*) N
from my_link_t t
group by t.speedcat, WIDTH_BUCKET (t.weight, 0, 0.6, 601)

This is format (x,y,z) is the expect format for most graphing packages.

If you want the results in a "grid" format, then PIVOT the results.

with data as (
  -- same SELECT as previous answer
  select t.speedcat
    , WIDTH_BUCKET (t.weight, 0, 0.6, 601) as bucket_index
    , count(*) N
  from my_link_t t
  group by t.speedcat, WIDTH_BUCKET (t.weight, 0, 0.6, 601)
)
select *
from data
  pivot (
    sum(N)
    for speedcat in ( 2 as "speedcat_2"
                     ,3 as "speedcat_3"
                     ,4 as "speedcat_4"
                     ,5 as "speedcat_5"
                     ,6 as "speedcat_6"
                     ,7 as "speedcat_7"
                     ,8 as "speedcat_8"
                     )
  )


Behavior of transactions on multiple connections I have a MariaDB database with InnoDB tables.  I have two connections to the same database.  One connection is using a transaction around a large chunk of work.  The other connection is tasked with updating a table in the same database (but outside the scope of the transaction's work) to track the progress of the transaction.

All of this is happening in the same script, but again, with separate connections: one for the actual job being performed (that includes a transaction), and another for keeping meta info on the progress of the job.

I expected that this would work such that the job tracking record would update as the job progressed - 10%, 20%, 50%, etc because the transaction work is being done on a separate connection and impacting other tables.  However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

As far as I can tell, it doesn't deadlock because the "progress monitoring" connection isn't using a transaction at all.  The "worker" connection wraps its queries in a transaction, but the other connection doesn't.

I check progress by just looking at a 'percent complete' field I fill in as the work progresses. Order of operations is:

Connection 1: start transaction
Connection 1: add and update rows
Connection 2: update progress
Connection 1: add and update more rows.

Then, in another HTTP connection I'm polling and outputting the progress value, but it never updates until the transaction on connection 1 is committed.

Is there any way to get the behavior I want, outside of moving the job-tracking table into an entirely different database?

What you see is the correct behaviour for the default isolation setting (REPEATABLE READ) and even for lower (READ COMMITTED) setting. More details can be found in MariaDB and MySQL docs:

SET TRANSACTION
Transaction Isolation Levels
Consistent Nonlocking Reads

which basically sums up to:

What one transaction is writing, other transactions should not be able to read. Not before the one transaction commits.

When a transaction is writing something to the database, there is no guarantee that the write will persist. The transaction may get an error and all of the writes it has done be rolled back. So, what you observe

  However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

is correct from that point of view. There is nothing to be seen from the outside, progress is 0, until something is committed. That's what the Isolation property of transactions is all about.

I see 2 solutions to the issue:

A. use the "worker", transaction 1 for progress report, as well. Who's better to know how much its work has progressed, than the worker itself?
B. In the 2nd, "progress report" connection, use the lowest isolation setting (READ UNCOMMITTED):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

Now, the transactions from this connection will be able to do "dirty reads", i.e. see uncommitted writes from the 1st transaction and will report correctly the (uncommitted) progress.


Query for a month works faster then for a week I have strange situation when query for a month works faster then for a week:

There is a request for a month (2016-06-01 - 2016-07-08) that works quickly and uses proper index:

SELECT  `t1`.* FROM `t1`
  INNER JOIN `t2` ON `t2`.`t1_id` = `t1`.`id`
  WHERE
   `t1`.`type` IN ... AND
   `t1`.`site_id` = ... AND
   (t1.created_at >= '2016-06-01 07:00:00') AND
   (t1.created_at <= '2016-07-08 06:59:59')
   ORDER BY `t1`.`id` DESC LIMIT 30 OFFSET 0;

returns 48 rows in 85ms

it uses INDEX: site_id, type, created_at, subtotal

The same request for a week (2016-07-01 - 2016-07-08 or 2016-06-01 - 2016-06-08) work in 1000 times slower and does not use index

returns 7 rows in 420 sec
returns 13 rows in 420 sec

Table has ~170M records

MySQL 5.6.23
analyze table t1, t2 has not fixed the issue
USE INDEX(...) solves the issue, but it's not a solution for me, I'can't manage indexes manually instead of MySQL
if remove LIMIT 30 OFFSET 0 it works fast, but I need it for pagination
ORDER BY t1.created_at works faster, but not enough - 1-10 sec. It also uses wrong index

Is there any solutions?

UPDATE:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Update 2:

There is explain for a week (slow query):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: PRIMARY,index_t1_on_site_id_and_c1_and_type_and_c2,index_t1_on_created_at_and_site_id,index_t1_on_type,index_t1_on_site_id_and_type_and_created_at_and_subtotal,index_t1_on_site_and_type_and_c3_and_created_at
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8991557
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: i1,i2
          key: i1
      key_len: 4
          ref: production.t1.id
         rows: 1
        Extra: Using index

There is explain for a month (fast query):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: PRIMARY,index_t1_on_site_id_and_c1_and_type_and_c2,index_t1_on_created_at_and_site_id,index_t1_on_type,index_t1_on_site_id_and_type_and_created_at_and_subtotal,index_t1_on_site_and_type_and_c3_and_created_at
          key: index_t1_on_site_id_and_type_and_created_at_and_subtotal
      key_len: 88
          ref: NULL
         rows: 7709
        Extra: Using index condition; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: i1,i2
          key: i1
      key_len: 4
          ref: production.t1.id
         rows: 1
        Extra: Using index

(Some of this is redundant, but I will spell it out anyway.)

I think 5.6.23, with MRR, is smart enough to use all 3 columns of INDEX(site_id, type, created_at) in that order.  (Older versions would not work as well.)

If you are using a 'range' of created_at values, nothing after that column will be used in optimization.  (Unless it is a 'covering' index.)

Instead of using OFFSET, "remember where you left off":  pagination blog.

With 170M rows, you should consider tightening up the datatypes wherever possible.  For example, can type changed to a 1-byte ENUM or normalized to a 1-byte TINYINT UNSIGNED (1 byte) instead of VARCHAR(25)?  subtotal decimal(25,2) takes 11 bytes.  If you don't need the precision of DECIMAL, consider an 8-byte DOUBLE.  Are there more than 16M sites?  If not, consider a 3-byte MEDIUMINT UNSIGNED instead of a 4-byte INT SIGNED.  Etc.

In place of USE INDEX.. this might be safer: IGNORE INDEX(PRIMARY).


Referencing multiple tables from a single table and store additional data I have a database where I want to store lap time information from a racing game. I have tables for car, track, game, tuning and upgrade data and want to put all these together to create a single dataset, so that I can e.g. display all times for a track, or detailed information about a lap time on a specific track in a specific car.

My schema is:

Game
-------------
GameID (PK), GameName

Cars
-------------
CarID (PK), Manufacturer, Model, GameID (FK)

Tracks
-------------
TrackID (PK), TrackName

Parts
------------
PartID (PK), PartName, GameID (FK)

Tuning
-----------
TuneID (PK), ElementName, GameID (FK)

TrackGame (-- A Track can appear in multiple Games)
-----------
TrackID (FK), GameID (FK)

To achieve that, I created a table TimeSet where I want to put everything together, but since a car can have multiple Parts installed, and many different Tuning values, I'm having a hard time integrating this properly into a TimeSet. I though of splitting the data over three tables (see below), but I don't think this looks like a good solution, because inserting data cleanly will probably be difficult to do.

TimeSet
-----------
TimeSetID (PK), GameID (FK), TrackID (FK), CarID (FK), (TimeSetID, TuneID) (FK), (TimeSetID,PartID) (FK), time

Time_Tune
-----------
(TimeSetID (FK), TuneID (FK)) (PK), value

Time_Part
-----------
(TimeSetID (FK), PartID (FK)) (PK)

What is the proper way to do this? I'm working with SQLite 3 on Android.

I think what you're missing is a car_configuration table. This will have an FK to the cars table and have car_config_parts and car_config_tunings as children, like so:

create table car_configuration (
  config_id integer primary key,
  car_id integer references cars (car_id)
);

create table car_config_parts (
  config_id integer references car_configuration (config_id),
  part_id integer references parts (part_id),
  constraint car_config_part_pk primary key (config_id ,part_id)
);

create table car_config_tunings (
  config_id integer references car_configuration (config_id),
  tuning_id integer references tuning (tuning_id),
  constraint car_config_tuning_pk primary key (config_id, tuning_id)
);

You can then include the config_id as a reference in the timeset table (with no reference to cars). You'll need to ensure that you can't change the parts and tunings for a configuration or implement some form of versioning on this table to ensure you can reconstruct the exact details used to set a given time in the past.


SQL Server error 3417 The message is

  The log scan number (1166:312:1) passed to log scan in database
  'master' is not valid. This error may indicate data corruption or that
  the log file (.ldf) does not match the data file (.mdf). If this error
  occurred during replication, re-create the publication. Otherwise,
  restore from backup if the problem results in a failure during
  startup.

Can you help in resolving the issue.

Restore your master database from a backup.  This is a good guide:

http://thomaslarock.com/2014/01/restore-the-master-database-in-sql-server-2012/


Best database system to store and compare date ranges I'm working on a project which is dealing with a lot of date ranges.
I'm starting completely from scratch; there is no existing code, infrastructure or database. Therefore, I'm searching for the best technology for each job.

I need to store a lot of date ranges in a database. Later on I would like to compare those data ranges to find overlaps.

I´ve already read about elasticsearch and influxdb.

Do you know any database system which is specialized to store and work with a great amount of date ranges?

The date ranges could be stored as single days or as range like from 01.01.2016 to 08.01.2016. I don't want to store any other data besides an identifier, to identify each date range record.

PostgreSQL has the datatypes daterange, tsrange (timestamp range) and tstzrange (time stamp with time zone range), along with a number of operators and functions to process, cast and extract information from them.

It also has EXCLUSION constraints, which can be used to ensure no two rows have overlapping ranges. E.g.

CREATE TABLE reservation (
  room_id int not null,
  customer_id int not null,
  during tsrange not null,
  EXCLUDE USING gist (room_id, during WITH &&)
);

The above table would not allow the same room having overlapping reservations.


getting mysql error when running the query alognwith ssh I am getting " You have an error in your SQL syntax; check the manual that corresponds to your MySQL server" error when running alongwith ssh command :

ssh user@localhost echo "delete from partner_company_association where colId = \"default.col \";" | /usr/bin/mysql -h localhost dbname

But the same works fine without doing ssh and running directly in the box:

 echo "delete from partner_company_association where colId = \"default.col \";" | /usr/bin/mysql -h localhost dbname

Kindly help me in figuring out the mistake here.
thanks in advance.

I may be wrong here - but isn't the echo command running on the server you're reaching via ssh - and the /usr/bin/mysql running on your local machine?

Whereas, both commands are running on the remote machine when issued on the remote machine, without the ssh?

What if you moved the ssh call to after the pipe, on the /usr/bin/mysql command?

NOTE: It's been a while since I used Unix/Linux, but a quick Google check on ssh seems to indicate this is how it works....


DB2 ODBC inconsistent connectivity My ASP.net application uses ODBC(64 bit) to connect to DB2 Database(9.5.3). I am using 64 bit IBM DB2 Client 10.5. on Windows Server 2008 R2. Connection pool is turned on.

It works fine(immediate connectivity) for most of the time, but occasionally too much time is consumed to establish connectivity. By too much time I mean to say 10 to 11 minutes. No error is reported. No issue with database, as it can be accessed from other servers at same timestamp.

All database request issued during this time via DB2 Client are kept on hold and then once connectivity is established, all are executed immediately.

When the issue is going on, I tried to connect to database through Windows server CMD and that too waits for around 10 mins. No error is reported. Network team says they dont see network traffic from Windows server to Database server when issue is going on. And no errors from network side. Which means DB2 client is not making connectivity request.

What could be causing delay in connectivity? This is not a consistent issue. It automatically resolves after around 10 mins. Is there any issue with DB2 driver ? Was any resource withheld during that time? I am closing all connections properly.

I can't give you a definitive answer, but I would suspect slow authentication, both client and server being Windows-based: because of the client-server version mismatch there may be some extra authentication back-and-forth between them, and at some point either (or both) will traverse the Active Directory on the domain and possibly all trusted domains for user ID and group lookup.

Try setting the authentication method on the client to match that on the database server (SERVER would be the default), this might speed things up. For this you'll need to uncatalog and re-catalog the database in question.

Ask your sysadmins to check AD status if (when) you experience this issue again, may be there's a spike in load or some other issue.


Restoring MySQL database with db dump and binlogs I have a database running on our production web server that I have set up as a master with a single slave in another datacenter. When reading through the MySQL replication setup instructions, they advice to take the server offline, place a read lock, do the dump, and then create the slave with it. I did that, got the replication set up and we were happy.

Today I decided that having that replicate in clear over the WAN probably isn't good practice. So I looked into setting up replication through an SSH tunnel (temporary). This required me to change master_host to 127.0.0.1 instead of my Master's IP. In doing this, I borked my replication and now I have to start over. Problem is, the Master only had expire_logs_days=1, so I can't repeat the original process since it was last week. I do have backups of the other binlogs, but using mysqlbinlog to restore all of them keeps failing due to temporary tables problems.

So now I'm trying to get the slave backup and running without taking the master down. Every 3 hours, on the master, we do a database dump for backups. We use mysqldump -v --flush-logs --single-transaction --routines .... so the current binary log is cut off and a new one created with every backup we do. However, if I restore a database dump, then try and start the replication backup with the new binary log that was created with the last --flush-logs command, I still run into key collisions, just like they warned.

Given this information, is there way that I can successfully start the slave back up with dumps we have without taking the server down again? I'm not going to be in a good place if I have to go ask for more down time.

They are two things that can be good for you in this instance

GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
GOOD THING #2 : The two MyISAM tables are never changing

If either one of these good things apply, then I have good news for you

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the MySQLSlaveReload.sql to the Slave

STEP04) Load MySQLSlaveReload.sql on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.

Give it a Try !!!


Is it possible to combine these three join queries into one? I would like to combine the counts of the stats by different hit_types into one query. Is that possible?

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| aaa |            1 |
| cnn |           16 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 1 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            1 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 2 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            4 |
+-----+--------------+

I tried to combine the first two but the numbers are all messed up and it eliminated the first result 'aaa'.

MariaDB [db]> select allurls.id, count(s1.id), count(s2.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 inner join stats s2 on allurls.id = s2.allurl_id and s2.hit_type = 1 where s1.hit_date >= '2018-01-15' and s2.hit_date >= '2018-01-15' group by allurls.id;
+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| cnn |           16 |           16 |
+-----+--------------+--------------+

I expected to see

+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| aaa |            1 |            0 |
| cnn |           16 |            1 |
+-----+--------------+--------------+

Ultimately I want to also include count(distinct(s4.source_id)).

Here is a fiddle: https://www.db-fiddle.com/f/tGP5SbC2AdGgeEwWTAgobf/0

Not sure if there is anything that I miss here, but you can count conditionally like:

select allurls.id
    , count(case when s1.hit_type = 0 then 1 end)
    , count(case when s1.hit_type = 1 then 1 end)
    , count(case when s1.hit_type = 2 then 1 end)
    , count(distinct s1.source_id)
from allurls
join stats s1
    on allurls.id = s1.allurl_id
where s1.hit_date >= '2018-01-15'
  and s1.hit_type between 0 and 2
group by allurls.id;

 id     c1  c2  c3  c4
aaa     1   0   0   1
cnn     16  1   4   3

Some things to note:

I moved hit_type predicate to where, it does not make a logical difference, but I prefer to have only the relationship between tables in the join clause if possible.

distinct is not a function, count(distinct(s1.source_id)) is confusing. Better use count(distinct s1.source_id)


notes / description string in another table in a relational database I'm primarily a non-database developer, however I am familiar with normalization techniques.

A coworker was suggesting that it is correct for a table to store 'description' and 'notes' as a foreign key rather than in the table for performance reasons, given that some of the records will contain NULL (or empty string in this case), and presumably will be split across pages.  This conversation occurred after we noticed that the integrity of the one-to-one table design was broken with a duplicate record.  It seems to me that if the developer who designed the column put it in the source table, all of the code that handles the one-to-one integrity would be unnecessary (and would have not caused this bug)

Pros of putting Nvarchar in a table

never have the risk of duplication
easier to work with, easy queries

Cons of putting Nvarchar in a table

after insertion, if the notes field grows, might cause a page split
and worsened performance when querying the table.

Additional question: when putting notes in a seperate table, is the design closer to 1st normal form?

related, which seem to defend putting the strings in the source table instead of a FK:
https://stackoverflow.com/questions/9540738/nchar-vs-nvarchar-performance
Are relations slower than a big, inefficient table?
https://stackoverflow.com/questions/1412040/do-i-need-a-separate-table-for-nvarcharmax-descriptions

A coworker was suggesting that it is correct for a table to store 'description' and 'notes' as a foreign key rather than in the table for performance reasons, given that some of the records will contain NULL (or empty string in this case), and presumably will be split across pages.

Possibly, depending on RDBMS, the exact data types, and what the performance goal is.

For SQL Server, which it sounds like you're using, you can use sp_tableoption with large value types out of row to keep a LOB (nvarchar(MAX) in this case) column in the table you want (logically), while pushing the bulk of the data storage onto separate pages (physically).

This will make accessing the LOB data slower, but speed up access to only the base data (probably moreso for scanning) as the tradeoff. Depending on the average LOB size, this setting may not make a significant difference. You will have to test this for your exact scenario to see if it improves your workload.

In any event, as long as you want to keep the one-to-one relationship, there's no reason to logically separate the values from the base table because there is an available way around the problem in SQL Server, assuming you're using the MAX type variant. (Note: if you're currently using a fixed-length field, you could switch to the MAX type and add a check constraint to limit the length.)

  This conversation occurred after we noticed that the integrity of the one-to-one table design was broken with a duplicate record.

It seems like the design of these structures is smelly. Are you keeping a single table to store all of these fields, which are then referenced from the parent tables?

A better design would be to only separate the tables where there's an actual need (again, as I mentioned, this may not even be required) with a declarative one-to-one relationship, such as repeating the primary key of the parent table as the primary key in the LOB storage table.

  It seems to me that if the developer who designed the column put it in the source table, all of the code that handles the one-to-one integrity would be unnecessary (and would have not caused this bug)

Quite possibly. The fewer moving parts there are in a system, the more likely it is to be reliable, probably with fewer bugs as a result of complexity.


Query returning all posts by friends and friends of friends I'm currently representing friends in a table called relations, which has two columns (userA, userB) which are the user IDs of two users. If user 1 and user 3 are friends, I'd insert (1, 3) and (3, 1) into this table.

I also have a table called posts, which has, among other fields, an id and an ownerId.

So something like this:

relations:
 userA | userB
   1   |   3
   3   |   1
   3   |   4
   4   |   3
   4   |   2
   2   |   4

posts:
 id | ownerId | ...
  1 |    2    | ...
  2 |    3    | ...
  3 |    4    | ...

I'd like to select every post whose owner is a friend or friend of friend.

For example, if the user the query was selecting for was userId=1, the query should return posts 2 and 3, since userId=3 is a friend, and userId=4 is a friend of a friend).

I've come up with the following, but was wondering if there are better ways to do this (that don't involve a UNION).

SELECT posts.id FROM posts
INNER JOIN relations ON relations.userA=[userId]
WHERE relations.userB=posts.ownerId
UNION
SELECT posts.id FROM posts
INNER JOIN relations AS r1 ON r1.userA=[userId]
INNER JOIN relations AS r2 ON r2.userA=r1.userB
WHERE r2.userB=posts.ownerId AND r2.userB<>[userId];

It is possible to solve this with a query that does not have a single UNION in it:

SELECT
  p.id
FROM
  relations AS r1
  INNER JOIN relations AS r2 ON r1 ON r1.userB = r2.userA
  INNER JOIN posts AS p ON p.ownerId IN (r1.userB, r2.userB)
WHERE
  r1.userA = @userID
  AND r2.userB <> @userID
;

The optimiser, however, might still internally rewrite your query to something very similar to a UNION query, so in the end the performance might not be much better, if at all, than that of query with an explicit UNION.


No rows in sys.dm_exec_procedure_stats for a particluar stored procedure I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats.

Please suggest what could be various reason for this as I can see my stored procedure has been executed multiple times.

By using this DMV, you can returns metrics for stored procedures, but
  it does not directly identify the stored procedure by name.  Instead,
  the DMV only identifies the object_id, and a database_id for each
  stored procedure:  Therefore to identify the actual stored procedure
  name, that the performance figures belong to, you should either join
  the output of this DMV with one of the system views within the
  appropriate database, or use a few metadata functions

Therefore you should consider using sys.dm_exec_query_stats along with one you mentioned in you're question to get stats.

Also,

You can look in the plan cache to get a pretty good idea of stored procedure usage.

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts)

There are various other queries via which you can analyse the metrics for your stored procedures. Monitoring stored procedure usage

Also, read here


Multiple aggregations in select slow on postgres I have a table with columns: id, antenna_id, latitude, longitude. There are two composite indexes on (antenna_id, latitude) and (antenna_id, longitude). When I do a max(latitude) for a specific antenna id(s), the speed is acceptable, but doing a min and max for both latitude and longitude at the same time is very slow.
Using PostgreSQL 12.3
Query

EXPLAIN (analyze, buffers, format text)
SELECT max(latitude)
FROM packets
WHERE antenna_id IN (1,2)

Finalize Aggregate  (cost=443017.21..443017.22 rows=1 width=32) (actual time=4373.679..4373.679 rows=1 loops=1)
  Buffers: shared hit=10812 read=16887
  ->  Gather  (cost=443017.10..443017.21 rows=1 width=32) (actual time=4373.412..4389.032 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=10812 read=16887
        ->  Partial Aggregate  (cost=442017.10..442017.11 rows=1 width=32) (actual time=4313.576..4313.577 rows=1 loops=2)
              Buffers: shared hit=10809 read=16887
              ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..433527.51 rows=3395835 width=7) (actual time=0.375..3435.488 rows=2201866 loops=2)
                    Index Cond: (antenna_id = ANY ('{1,2}'::integer[]))
                    Heap Fetches: 0
                    Buffers: shared hit=10809 read=16887
Planning Time: 5.992 ms
JIT:
  Functions: 8
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 6.236 ms, Inlining 0.000 ms, Optimization 1.549 ms, Emission 32.058 ms, Total 39.842 ms
Execution Time: 4706.406 ms

The explain looks almost identical for max(longitude), min(latitude) and min(longitude) on their own. Speed is acceptable.
But when I combine the queries
SELECT max(latitude), max(longitude), min(latitude), min(longitude)
FROM packets
WHERE antenna_id IN (1,2)

Duration
[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)
Explain
Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)
  ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)
        Workers Planned: 1
        ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)
              ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)
                    Filter: (antenna_id = ANY ('{1,2}'::integer[]))
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN (analyze, buffers, format text)
SELECT max(latitude), max(longitude), min(latitude), min(longitude)
FROM packets
WHERE antenna_id IN (1,2)

has been running for 24h now, and isn't done yet
Indexes
create index idx_packets_antenna_id_time
    on packets (antenna_id, time);

create index idx_packets_antenna_id_longitude
    on packets (antenna_id, longitude);

create index idx_packets_device_id_time
    on packets (device_id, time);

create index idx_packets_antenna_id_latitude
    on packets (antenna_id, latitude);

Data stats
select count(*) from packets
136758098

select count(distinct (antenna_id)) from packets
17558

select antenna_id, count(*) as records
from packets
where antenna_id in (1,2)
group by antenna_id
order by records desc

1,4361049
2,42683

Question
Why does the second query which does the min and max on the latitude and longitude field not use the indexes? And how can I rewrite the query so that it is faster?

Let's create some test data. Looks like your query has about 1% of rows per antenna_id so let's replicate this.
CREATE UNLOGGED TABLE foo( lat FLOAT NOT NULL, lon FLOAT NOT NULL, aid INTEGER NOT NULL );
INSERT INTO foo SELECT random(), random(), random()*100
    FROM generate_series(1,10000000) s;
CREATE INDEX foo_aid_lat ON foo( aid, lat );
CREATE INDEX foo_aid_lon ON foo( aid, lon );
VACUUM ANALYZE foo;

SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid IN (1,2);

 Finalize Aggregate  (cost=71572.35..71572.36 rows=1 width=32) (actual time=119.907..125.118 rows=1 loops=1)
   ->  Gather  (cost=71572.12..71572.33 rows=2 width=32) (actual time=119.648..125.110 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=70572.12..70572.13 rows=1 width=32) (actual time=95.595..95.596 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on foo  (cost=4886.47..69687.39 rows=88473 width=16) (actual time=9.532..90.336 rows=66524 loops=3)
                     Recheck Cond: (aid = ANY ('{1,2}'::integer[]))
                     Heap Blocks: exact=26477
                     ->  Bitmap Index Scan on foo_aid_lon  (cost=0.00..4833.39 rows=212336 width=0) (actual time=20.022..20.023 rows=199572 loops=1)
                           Index Cond: (aid = ANY ('{1,2}'::integer[]))
 Planning Time: 0.499 ms
 Execution Time: 125.202 ms

This is really slow. Let's try for one antenna_id.
SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid=1;
 Result  (cost=1.88..1.89 rows=1 width=32) (actual time=0.192..0.196 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1)
           ->  Index Only Scan using foo_aid_lat on foo  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.057..0.057 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lat IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=1)
           ->  Index Only Scan Backward using foo_aid_lat on foo foo_1  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.043..0.044 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lat IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 3 (returns $2)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.038..0.038 rows=1 loops=1)
           ->  Index Only Scan using foo_aid_lon on foo foo_2  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.037..0.037 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lon IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 4 (returns $3)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=1)
           ->  Index Only Scan Backward using foo_aid_lon on foo foo_3  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.041..0.041 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lon IS NOT NULL))
                 Heap Fetches: 0
 Planning Time: 0.504 ms
 Execution Time: 0.277 ms

That's the correct plan, that uses the multicolumn index to compute the max and min. This requires only 1 index lookup per min() or max(), because
SELECT max(lat) where aid=...

is equivalent to
SELECT lat WHERE aid=... ORDER BY lat DESC LIMIT 1

...which is optimizable using an index which contains the rows in pre-sorted order.
The above optimization of max() and min() is basically syntactic sugar, it turns the query into an ORDER BY+LIMIT and puts that into an InitPlan in order to use the index.
But, apparently, it doesn't do this when querying several antenna_ids using "WHERE IN()". Adding a "GROUP BY aid" at the end of the first query doesn't help.
So... Let's query the antenna_ids one at a time then.
SELECT * FROM
(VALUES (1),(2)) AS v
CROSS JOIN LATERAL (SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid=v.column1) x;

It makes a nested loop over the VALUES, and inside the nested loop is the above fast query. It returns the max() and min() for each antenna_id, so to get the global max() and min() you have to wrap that in a subquery and apply max() and min() over the result.
This shouldn't take more than a millisecond unless there is another problem.
Replacing the VALUES above with a generate_series(1,100) that gets the max for the 100 aid's in the table takes about 5ms. Doing it the old fashioned way:
select aid,min(lat),max(lat),min(lon),max(lon) FROM foo group by aid;

takes about 100x longer.


Masking Certain Parts of Specific Records Is there a way in SQL Server to mask only certain records for a given column based on a primary key value? I know the new Dynamic Data Masking function automatically masks the whole field that you select. Is there a way to just do certain records?

If you control the T-SQL (like if you're dealing with views or a reporting system), then you're probably looking for a user-defined function. With UDFs, you can code your own logic into something like this:

SELECT dbo.fn_Mask(MySecretField) FROM dbo.tbl_PreciousData

(Having a little fun with the naming geeks there.)

Then, inside fn_Mask, you'd write your own logic to take MySecretField as your input (and you could pass in multiple fields if you like), and even look at the logged-in user's security information to decide whether or not to mask the data.

But danger, Will Robinson: scalar UDFs make your query go serial, as opposed to parallel. Between that, and the fact that people can still directly select MySecretField, this is only a solution for limited scenarios.


How to remove XFS filesystem warning in mongodb shell? Every time I start a mongodb shell, I get this annoying warning:

Server has startup warnings:
2020-02-01T08:54:27.220+0100 I  STORAGE  [initandlisten]
2020-02-01T08:54:27.220+0100 I  STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2020-02-01T08:54:27.220+0100 I  STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem

I understand that in some environments, this is useful. But in my particular case, I'm using mongodb on a VPS server that has pre-installed ext4 filesystem. There is no option to change that. And it is not needed anyway - in this particular application, there are no performance problems and there will never be. The server is already overpowered for this application.

I was going through the documentation to find a command line option for suppressing this warning message, but there is none. Or maybe there is, but I couldn't find it?

To permanently suppress this server warning you currently need to use the XFS filesystem. The warning was added due to observed stalls with EXT4 during WiredTiger checkpoints.

You may want to upvote & watch SERVER-19790: Provide mechanism to clear/acknowledge startup warnings in the MongoDB issue tracker.

If you want to suppress displaying this warning (and all other startup warnings), start your mongo shell session with --quiet.

The quiet option is typically only used for non-interactive scripts as otherwise you may miss an important warning. Startup warnings only appear once per mongo shell session and highlight potentially serious issues like having access control disabled (or in your case, a filesystem which can result in stalls during checkpoints).


Simple way to use SP_EXECUTESQL for loop through all databases I have a BIG problem running queries on all databases. I have a lot of scripts that works, but I would like to understand better how to use SP_EXECUTESQL instead of SP_MSFOREACHDB.

Normally when I'm in a hurry I do this:

Then I just copy the results, and I execute it. It works great.

I would like to know how to transform this in a SP_EXECUTESQL loop statement.

I have this that uses a cursor, but is there a way to use SP_EXECUTESQL without a cursor?

create table #temp (
    Dbase varchar(100),
    Feature_Name varchar(100),
    feature_id int
                    )
go

DECLARE @DB_NAME NVARCHAR(100)
DECLARE @CMD NVARCHAR(MAX)
DECLARE CR_FEATURE CURSOR FOR
SELECT NAME
    FROM SYS.sysdatabases

OPEN CR_FEATURE

FETCH NEXT FROM CR_FEATURE INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @CMD='insert into #temp  select '''+@DB_NAME+''' ,* from sys.dm_db_persisted_sku_features'
    EXEC SP_EXECUTESQL @cmd

FETCH NEXT FROM CR_FEATURE INTO @DB_NAME
END

CLOSE CR_FEATURE
DEALLOCATE CR_FEATURE

DROP TABLE #temp

I could even create a simple query to run that simple select, but I would like everything in the same query window:

With sp_ineachdb from here created in master. With this #temp table:

CREATE TABLE #temp
(
    Dbase varchar(100),
    Feature_Name varchar(100),
    feature_id int
);

The code is simply:

DECLARE @cmd nvarchar(max) = N'INSERT #temp
  SELECT db_name(), feature_name, feature_id
    FROM sys.dm_db_persisted_sku_features;';

EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

Alternatively:

DECLARE @cmd nvarchar(max) = N'SELECT db_name(), feature_name, feature_id
    FROM sys.dm_db_persisted_sku_features;';

INSERT #temp EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

There isn't really a way to execute a command across multiple databases without using some kind of loop. You could create a view, perhaps, across all the current databases:

USE master;
GO

CREATE VIEW dbo.AllPersistedSkuFeatures
AS
  SELECT 'db1', feature_name, feature_id FROM db1.sys.dm_db_persisted_sku_features
  UNION ALL
  SELECT 'db2', feature_name, feature_id FROM db1.sys.dm_db_persisted_sku_features
  UNION ALL
  ...

But there are multiple problems with this. You have to maintain this view to account for databases that get added, dropped, or renamed. You have to account for cases where any single database can be in any state other than online. And permissions. You could build code to generate the view (or a CTE) at runtime, but that will still require a cursor or a loop.

So if your goal is to simplify your runtime code, no problem. If your goal is to eliminate a cursor or loop at any step in the process, not likely.


Optimizing Postgres query I've got a one to one relation from users to addresses table.
Where one user can have one search address and one verified address.

I've got two indexes on addresses table :

Index on state field
Index on user_id

I'm trying to fetch addresses for certain users only, and those addresses whose state is other than manual_verification.

Here is my query:

SELECT users.id
FROM "users" INNER JOIN addresses
     ON  addresses.user_id = users.id
     and addresses.type = 'VerifiedAddress'
WHERE ("users".deleted_at IS NULL)
  AND (users.id in (11144,10569,21519,783,15671,21726,17787,11665,
                    19579,12226,1324,9413,5461,20981,12906)
  and addresses.state != 'manual_verification')

Explain for the query above :
http://explain.depesz.com/s/rTj

It takes 37 ms. Sometimes more depending on the number of users.

I think this is a good query, however our team needs to investigate around this, and I'm looking for some optimization tips. I mean I did the one field select, there is a index on user_id (addresses) and state (addresses).

Is there anything else I can do/try?

Update

I found out that this query works much faster:

 SELECT "addresses"."user_id"
    FROM "addresses"
    WHERE "addresses"."type" IN ('VerifiedAddress')
    AND (user_id in (9681,23824,23760,20098,962,14730,12294,9552,534,
                     553,5837,6768,6583,956,24179) and state != 'manual_verification')

Explain for this query:
http://explain.depesz.com/s/nHrr

I'm trying to fetch addresses for certain users only ...

IN does not scale well with big lists. Compare performance to unnest() / JOIN

SELECT a.*
FROM   unnest(ARRAY[9681,23824,23760,20098,962,14730,12294,9552,534,
                    553,5837,6768,6583,956,24179]) AS t(user_id)
JOIN   addresses a USING (user_id)
WHERE  a.type  =  'VerifiedAddress'
AND    a.state <> 'manual_verification'

I hope state and type are at least enum types. Otherwise you should normalize your database design. Have lookup-tables for state and type and only use a small IDs referencing entries there. Makes the addresses table smaller and everything faster.

Assuming all columns NOT NULL for lack of information.

Partial index

If you need it faster still, and if there are more than a few rows with type <> 'VerifiedAddress' or state = 'manual_verification', a partial index would help some more. The gain grows with the percentage of rows you can exclude from the index.

CREATE INDEX addresses_selection_idx ON addresses (user_id)
WHERE  type  =  'VerifiedAddress'
AND    state <> 'manual_verification';

Be sure to use matching WHERE conditions in your query to allow Postgres to use this index. And weigh the benefit of tailored indexes against their cost (maintenance, extra disk space, small penalty for write operations).


Can we configure logins to only connect to read-only replicas in Azure SQL Database automatically? For Read-Only routing, rather than explicitly using "ApplicationIntent=ReadOnly" into our application connection string or for individual users to specify it in additional properties, can this be configured while creating a login?
For Example: Any user trying to connect to the instance should be routed to read-only replica, even if they don not provide "ApplicationIntent=ReadOnly" in their connection string.
Is this possible?

No.  But you can disable their logins on the instance that normally hosts the primary replica and have them connect directly to an instance that normally hosts a read-only replica.


mysql optimize group by having count I have 2 mysql table:

table t1:

CREATE TABLE IF NOT EXISTS `t1` (
  `id` bigint(20) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

table t2

CREATE TABLE IF NOT EXISTS `owa_request` (
  `id` bigint(20) NOT NULL,
  `date` int(11) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `d_id` bigint(20) DEFAULT NULL,
  `u_id` bigint(20) DEFAULT NULL,
  `browser` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `d_id` (`d_id`),
  KEY `u_id` (`u_id`),
  KEY `date` (`date`),
  KEY `ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I want to make such a query:

select
t1.id,t1.url,t1.type,
t2.d_id,t2.date,t2.ip
from t1,t2
where t1.id=t2.d_id and t1.type='article'
group by t2.ip
HAVING COUNT(t2.ip)>10

get all the data group by t2.ip and each t2.ip should have at least 10 results.

But the query is very slow, 300k rows data (70MB), cost 12.5290 sec, have I make bad indexes? how to optimize let query under half second? Thanks.

I think that your type key is the main problem for you ! as you have it set to the max index size in mysql !
 Try to see the real need of those 255(767 byte index) that is big for a table with that many rows.
 And check the distinct values in this column 'type' maybe a partition can be used in order to get rid of unnecessary reads.


Why is the optimizer doing a table scan vs Index Seek Probably a straight forward answer, but I'm too bleary eyed to see it.

Have a simple query that is pulling the top 100 rows based on a char column.

The column has an index on the where column.
Returning all the columns (*)
There are ~100 millions rows
Statistics have been updated

The optimizer thinks a table scan is more efficient than running an index scan.

Sample Table:

CREATE TABLE [dbo].[tst](
[Mon] [char](6) NULL,
COL1 [varchar](50) NULL,
COL2 [varchar](50) NULL,
COL3 [varchar](50) NULL,
COL4 [varchar](50) NULL,
COL5 [varchar](50) NULL
)

Sample Query:

select
   top 100 *
from
   <table>
where
   mon = '201509'

This will take > 30 seconds to return since the optimizer chooses a table scan vs using the index seek. An index scan is < 1 sec.
Interestingly if a symbol is added in the where
e.g. '2015.09' or '2015 09'

It will use the index.

Searching straight '201509', it uses a table scan.

Add in a dot or space, it uses an index scan.

Can someone explain to me why the optimizer is choosing a table scan vs using the index?

Edit: Thanks all for the excellent feedback and information. I didn't know about traceflag 9130 and from that I can see the estimated rows are a lot less than the actual rows which is likely why the optimizer is choosing the table scan vs the index.

Here is the Statistics Output:
Without index hint:

Table 'TestTable'. Scan count 1, logical reads 982046, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 17437 ms,  elapsed time = 17792 ms.

With Index hint:

Table 'TestTable'. Scan count 1, logical reads 104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

The optimizer is convinced that if it's going to have to go back to the disk for retrieving column data anyway, it might as well scan the table in the first place, since that'll be less work for it to do.  It'll use the seek with the CHAR( 7 ) scalar because the statistics for the index know it's not going to find anything, but if data needs to be returned, it has to consider both CPU and I/O weights.

USE tempdb;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'tst'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.tst;
    CREATE TABLE [dbo].[tst]
    (
        Mon                     [char](6) NULL,
        COL1                    [varchar](50) NULL,
        COL2                    [varchar](50) NULL,
        COL3                    [varchar](50) NULL,
        COL4                    [varchar](50) NULL,
        COL5                    [varchar](50) NULL
    );

    INSERT INTO dbo.tst ( [Mon] )
    SELECT  TOP 100000000
            CONVERT( CHAR( 6 ), DATEADD( DAY, ( ABS( CHECKSUM( NEWID() ) ) % 10000 + 1 ),
                '20000101' ), 112 )
    FROM    sys.all_objects so
    CROSS APPLY sys.all_objects sp;

    CREATE NONCLUSTERED INDEX IX__tst__Mon
        ON dbo.tst ( Mon )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 );
END;

SELECT  Mon, COUNT( 1 )
FROM    dbo.tst
GROUP BY Mon
ORDER BY Mon;

SET STATISTICS IO, TIME ON;

SELECT  Mon, COL1
FROM    dbo.tst
WHERE   Mon = '201509'

SELECT  Mon, COL1
FROM    dbo.tst WITH ( INDEX = IX__tst__Mon )
WHERE   Mon = '201509'

SELECT  *
FROM    dbo.tst
WHERE   Mon = '201509'

SELECT  *
FROM    dbo.tst WITH ( INDEX = IX__tst__Mon )
WHERE   Mon = '201509'

SET STATISTICS IO, TIME OFF;

Specifying the hint, in both cases, does reduce the time required for the query to resolve, but the index seek + RID lookup actually results in a significant increase in the number of reads necessary ( my test indicated a 60% increase ).  Obviously it's not a 1:1 trade off, since the time difference is about 6x, but regardless, the optimizer is choosing the scan instead.

If you can INCLUDE the columns you need in the index, you'll get the best of both worlds, eliminating the RID lookup and the additional reads.

--DROP INDEX dbo.tst.IX__tst__Mon
CREATE NONCLUSTERED INDEX IX__tst__Mon
    ON dbo.tst ( Mon )
INCLUDE ( COL1, COL2, COL3, COL4, COL5 )
WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 );

SET STATISTICS IO, TIME ON;

SELECT  *
FROM    dbo.tst
WHERE   Mon = '201509'

SET STATISTICS IO, TIME OFF;


DBI1011I Error when using db2iupdt to enable Text Search I'm trying to enable Text Search in my DB2 instance. I'm running this command:

# /opt/ibm/db2/V10.5/instance/db2iupdt db2inst1 -j "TEXT_SEARCH,db2j_db2inst1,55000"

I get this error:

DBI1011I  An invalid argument was entered for the db2iupdt command.

Explanation:

You can use the db2iupdt command to update a DB2 instance to a higher
level within a release, convert a DB2 instance to a DB2 pureScale
instance, or manage DB2 members or cluster caching facilities (CFs) of a
DB2 pureScale instance.

This message is returned when the db2iupdt command is invoked with
incorrect or missing parameters.

User response:

Enter the command again using valid parameters.

   Related information:
   db2iupdt - Update instances command
   Updating an instance to a higher level within a release using the
   db2iupdt command
   Updating an instance to a DB2 pureScale instance with the db2iupdt
   command
   Dropping a member or cluster caching facility
   Adding new members or an additional cluster caching facility

DBI1011I documentation just says there's something wrong with the parameters. I'm not seeing anything obviously wrong with them when looking at the documentation for db2iupdt.

I'm running DB2 10.5:

$ db2licm -l
Product name:                     "DB2 Advanced Workgroup Server Edition"
License type:                     "CPU Option"
Expiry date:                      "Permanent"
Product identifier:               "db2awse"
Version information:              "10.5"
Max amount of memory (GB):        "128"
Enforcement policy:               "Soft Stop"

How do I successfully enable Text Search in my DB2 instance?

According to the syntax diagram at http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002060.html, you should enclose the text search options in double quotation marks:

-j "TEXT_SEARCH,db2j_db2inst1,55000"

Don't forget to escape them in the shell.


SQL Server - Select parent row or child row if no parent I have a single table, Issue. This table has Id and ChildId columns.

=============
Id | ChildId
1  | NULL
2  | 3
3  | NULL
4  | 1
5  | NULL
6  | NULL
=============

I need to select from this table so that the parent row is returned if it exists else return the child row. So selecting from every row would get:

=============
Id | ChildId
4  | 1
2  | 3
2  | 3
4  | 1
5  | NULL
6  | NULL
=============

I know that this table layout is not good (using ChildId instead of ParentId) but I cannot change the scheme unfortunately.

If you know how to return this data that would be great. Thanks in advance.

Join the same table twice and use a CASE statement:

select
    case when t2.id is not null then t2.id else t1.id end as id,
    case when t2.id is not null then t2.childid else t1.childid end as childid
from
    tbl t1
left join tbl t2
    on t2.childid = t1.id

id | childid
-: | ------:
 4 |       1
 2 |       3
 2 |       3
 4 |       1
 5 |    null
 6 |    null

db<>fiddle here


How do I copy a view from one server to another? I want to copy a view from server S1 --> Database D1 to Server S2 --> Database D2.

I generated the script by right clicking the database(D1)--> tasks--> generate script.  However, when I execute this script in Database D2 it says that D1 doesn't exist.  The error is on line1.

USE [D1]  -- Line 1
GO

Change the script so that you use the correct database before running it or remove the use statement completely either manually or by not scripting it out to begin with.


PostgreSQL Date Check Costraint How could I add a Check-costraint for Date type in PostgreSQL?

For example,

 CHECK 'DateXYZ>01/02/98'

A typecast is needed?

create table foo
(
    some_date date not null check (some_date > date '1998-02-01');
);

More details in the manual:

http://www.postgresql.org/docs/current/static/datatype-datetime.html
http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS


Check database size in previous day I want to ask how do I get database size in previous day (example: Today is January 31st, and I want to get database size in January 22nd).
Note: I do not have regular backups.

I have tried this query:

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = 'my_own_schema'

But I only have the size of current time.

Thank you for any helps!

I want to ask how do I get database size in previous day (example: Today is January 31st, and I want to get database size in January 22nd). Note: I do not have regular backups.

That's impossible. You could easily script a daemon (with something like du) that recorded that. Or you could use something an lvm snapshot or XFS to get that information, if you were using a stack like that. But MySQL doesn't just record how big the database was at an arbitrary time.


Restore a SQL Server database to an offline copy I have SQL Server Management Studio. I need to access a backup file (*.bak).

How do I create a new database engine that is OFFLINE (running from my PC not the server) and then restore the database to that offline database engine?

Install SQL Server, if it isn't already there.
Get the backup file onto you local box.
Open SSMS and connect to your local instance
Right-click the "Databases" folder and pick "Restore Database..."
Follow the wizard until you're done


Is there a way to minimally log insert under Always on? I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model?

I have a process that is executed each day and insert few millions of records in a table. While the operations continued the transaction log file size is increased dramatically ( from 1 GB to 40 GB).

As I have read I can used some variations of INSERT which are not fully logging the operation but I am concern about the effect of switching the recovery model?

No.  Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery.

Quote from the Data Loading Performance Guide:

  Minimally logged operations are available only if your database is in
  bulk-logged or simple recovery mode.

Re trace flag 610, what that added was the ability to do minimally logged inserts to tables with clustered indexes ( and/or non-clustered indexes ) with data already in them.  This was not previously available.

The thing to bear in mind about minimally logged transactions as that as soon as a transaction log backup contains one of these, you lose the ability to do point-in-time restore with that backup.  So hopefully now you understand why these are not available in Full recovery model


Optimal table with N:1 foreign key column I have an MS SQL table like this:

TABLE myLog(
  [LogID] [int] IDENTITY(1,1) NOT NULL,
  [ThingID] [int] NOT NULL,
  [Action] [varchar](100) NOT NULL,
CONSTRAINT [PK_myLog] PRIMARY KEY CLUSTERED
(
  [LogID] ASC
)

ThingID is a foreign key for a myThings table identity column.

myLog can contain many rows for any ThingID.

What are the best ways to index or key myLog given that a typical query will request all rows for a particular ThingID?

I would just put a non-clustered index on ThingID.  A and declare the relationship.


ERROR: improper qualified name (too many dotted names) when trying ALTER FUNCTION Working with Amazon Aurora Postgres Version 2.0, which is basically  PostgreSQL 10.4 with a modified storage layer.

Trying to restore a dump that contains multiple lines like:

ALTER FUNCTION myschema.f_myfunc(anyarray, anyelement) OWNER TO myrole;

I get an error message for every ALTER FUNCTION like that:

ERROR:  improper qualified name (too many dotted names)

Trying the same in psql produces the same error. Even with the simplest form:

ALTER FUNCTION foo() OWNER TO myrole;

There are no dotted names. I found similar complaints here:
https://forums.aws.amazon.com/thread.jspa?messageID=872096&tstart=0

Must be a bug in Aurora - or am I missing something?

Response from AWS Support:

On further researching, I was able to find that this was a known issue in Aurora Version 2.0 (compatible with 10.4) and the issue has been fixed on Aurora Version 2.1 ( compatible with PostgreSQL 10.5). I tried testing on my Aurora PostgreSQL 10.5 test cluster to confirm if the issue has been fixed and I was able to successfully execute the statement without any error message.

tl;dr Bug in Aurora 2.0 (PostgreSQL 10.4), fixed in Aurora 2.1 (PostgreSQL 10.5).

EDIT: Along with JohnC, I saw this error arise again in Aurora 3.0.0 (PostgreSQL 11.4). Support response Jan 11, 2020:

Upon further researching, I can confirm that this issue is also affecting Aurora PostgreSQL 11.4 engine version. After a thorough internal research around this error, I was able to find out that the Aurora PostgreSQL team is aware of this specific issue with 11.4 version as well and please be assured that we are already working on a fix for this that should be available in a later release.
Having said that, at the moment, I do not have an exact ETA for when this issue will be resolved for Aurora PostgreSQL 11.4.
Since the error is seen for ALTER FUNCTION command, the suggested workaround by the internal team is to recreate the function with the intended owner.


In what file does SQL Server Store the configuration data? Where does SQL Server 2014 store the configuration data? Such as the certificate references for SSL and the size limits and allocations?

Basically there is not a single place where ever configuration can be found.

The more generally used configurations can be found here Server Configuration Options. But they do not necessarily cover everything that you might want to configure.

For example:  Enable SSL Encryption for SQL Server using Microsoft Management Console

Also you can find some information on size limits and allocations at: Features Supported by the Editions of SQL Server 2014

Perhaps these examples will get you started.


Generate Data Only Script without Identity SQL 2017 I am using the script generator in SQL 2017 to create an INSERT script.

When generated, it includes the Identity which I don't want.

I have set Script Foreign Keys and Script Primary Keys to false but it is still including them.

My table is:
ID (Identity), machnineno, amount

what I want to create is an INSERT script that looks like

INSERT tablename (machineno,amount) values (1789,15)
INSERT tablename (machineno,amount) values (3894,22)
INSERT tablename (machineno,amount) values (2097,9)

Currently it is doing

INSERT tablename (id,machineno,amount) values (22,1789,15)
INSERT tablename (id,machineno,amount) values (45,3894,22)
INSERT tablename (id,machineno,amount) values (77, 2097,9)

How can I generate a script without the ID?

Disabling to script the primary key and foreign key only restrict the generation of scripts creating these constraints. Identity is a table property. I would suggest (not efficient since duplicating data) to copy the column you required to a new table and script that table.

You can use,

Select machineno,amount into tmp_table
from tablename

Note the new table created will not create constraints and triggers
or you can script the DDL of the original table and create a dummy table without identity column in it and copy the data to the new table so it will retain the constraints

Then script the tmp_table using generate scripts. This will not have the identity column. You can change the table name as required after you have scripted it.

OR
Scripting dynamically

SELECT 'INSERT INTO tablename (machineno,amount) values ('+convert(varchar(28),machineno)+','+convert(varchar(28),amount)+')'
FROM tablename


datetime row created I have sqlserver DB , and there I have few tables.

I have 1 table that not save each row what is the datetime the each row created.

Is there any way to know when which row was created?
Is there any way (without changing the application that created the row
on this db, I can't add any SP to do it) that from now, when the
row is created, it will automatically add the created dateTime?

For the first question, you can't get data that isn't stored in your system. There is no way to know the date from which each row was inserted or updated if you don't have it stored somewhere.

For the second question, you can create a new column with a default constraint that will automatically set the current datetime value (if it wasn't supplied by the client), or an AFTER INSERT trigger that updates this column also. On both cases you need a column to store this value.

ALTER TABLE SchemaName.YourTable ADD CreatedDate DATETIME

Using a constraint with default value:

ALTER TABLE SchemaName.YourTable ADD CONSTRAINT DF_SchemaNameYourTable_CreatedDate
DEFAULT GETDATE() FOR CreatedDate

Using a trigger:

CREATE TRIGGER SchemaName.utrYourTableUpdateCreatedDate ON SchemaName.YourTable
    AFTER INSERT
AS
BEGIN

    UPDATE T SET
        CreatedDate = GETDATE()
    FROM
        inserted AS I -- special table that holds only new inserted rows (in this case)
        INNER JOIN SchemaName.YourTable AS T ON I.KeyColumn = T.KeyColumn

END


Dynamically REVOKE CONNECT FROM guest in SQL Server 2005 I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts:

DECLARE @database_id int, @database_name nvarchar(100);

DECLARE database_cursor CURSOR FOR
SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('master', 'tempdb')
AND state = 0

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

while (@@FETCH_STATUS <> -1)
        BEGIN
                Print @database_name
                exec('USE [' + @database_name + ']')

REVOKE CONNECT FROM 'guest'

                FETCH NEXT FROM database_cursor
                INTO @database_name
        END

CLOSE database_cursor
DEALLOCATE database_cursor

But apparently the REVOKE was still being applied to the master database.

So then I modified the script to this:

DECLARE @database_id int, @database_name nvarchar(100);

DECLARE database_cursor CURSOR FOR
SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('master', 'tempdb')
AND state = 0

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

while (@@FETCH_STATUS <> -1)
        BEGIN
                Print @database_name
                EXEC('USE [' + @database_name + '];'+

'REVOKE CONNECT FROM GUEST;'

                    );

                FETCH NEXT FROM database_cursor
                INTO @database_name
        END

CLOSE database_cursor
DEALLOCATE database_cursor

The script runs, but the changes aren't be saved for some reason because the guest account is not revoked when I check for it afterwards.

Sorry, if I'm missing any other details.  I'm so unfamiliar to this realm, I'm not even sure how to frame the question.

Thanks in advance!

Update 1:
I'm checking that it's been revoked using this script from the DISA STIGs:

SELECT COUNT(grantee_principal_id)
FROM sys.database_permissions
WHERE grantee_principal_id = 2
AND state = 'G'
AND permission_name = 'CONNECT'

I ran your script with no problems so my guess is that something is wrong with your conformation script.  I don't see anything specifically wrong with it but here are a couple of links with additional information about disabling guest.

http://blog.sqlauthority.com/2012/02/24/sql-server-guest-user-and-msdb-database-enable-guest-user-on-msdb-database/

http://blog.sqlauthority.com/2012/02/20/sql-server-disable-guest-account-serious-security-issue/

Here is a a modified version of Pinal Dave's script that should help you confirm.

EXEC sp_msforeachdb 'USE [?];
    SELECT ''?'' DBName, name, permission_name, state_desc
    FROM sys.database_principals prin
    INNER JOIN sys.database_permissions perm
        ON prin.principal_id = perm.grantee_principal_id
    WHERE name = ''guest'' AND permission_name = ''CONNECT''';


MySQL Allows ODBC With Any Username I have a MySQL Community database on a remote server and I am trying to get Microsoft Access to read data from it. This data is sensitive. The problem is, MySQL doesn't care about the username that I give it in the .DSN file. It lets me read the ENTIRE database no matter what! How do I get it to actually check the username that I give it in the .DSN file?

edit: I'm not a DBA, I'm some intern who is over his head. Please don't assume that I know anything about anything  :)

I strongly suspect this is not an issue of MySQL "not checking" the username, but rather permissions which are too broad.

Start with SELECT * FROM mysql.db WHERE Db = 'yourdb'; and ensure that only the desired permissions are set for your DB.


How much memory will allocated for a mysql view How much memory will allocated for a mysql view,if not how it works,if allocated how much will be allocated.

When views use the TEMPTABLE algorithm, the memory allocated will be relative to the size of the temp table, which depends on the number of rows and lengths of columns.

I don't know any more specific answer than that.


Constant writing operations on google cloud database I have a MySQL 8.0 cluster database and so far I'm using it to test a couple of things, so I only write from time to time. Problem is that I have realised that there are write operations all the time. Even if my apps are not writing to it.
This is a screenshot of the dashboard.
Just to mention, I have another cluster in Digital Ocean and this issue is not happening there.
Any help will be appreciated

UPDATE (2021-03-09)
Executing query as Vérace suggested:

As I see that time_zone_transition is the one growing the most

For a university course in Data Visualisation, I used the Prometheus/Grafana stack  (great s/ware) to present images of various database metrics while running various HammerDB benchmarks (also an excellent tool).
As part of this, to provide a baseline, I looked at my (single) server "at rest" and noticed (as you have) that there was activity - albeit a very small amount (~ 10 writes/second) - on an "inactive" server.
Basically, this is "house-keeping" functionality which happens even if there is no ongoing external DDL or DML.
There is an interesting article here and here (more recent and probably more accurate) showing the impact of the number of idle connections on the NOTPM (No. of Transactions Per Minute) results from the DBT2 benchmark tool.
As you can see from the results displayed in the second article, there is a small (but not non-existent) effect from just having idle connections.
You say that you are running a cluster - I would imagine that there is a  communications overhead there with nodes pinging each other even in the absence of any serious workload (or even any workload at all).
You don't mention what cluster (NDB? Galera?), but I'm sure that a delve into the code (way above my pay grade... :-) ), a search, or a question on your cluster's architecture/guru list would be able to provide more detail.
If you're interested and have the time and the inclination to test what's going on, you may like to leave your "inactive" cluster running for a while (say over a weekend?) and run (some variant of) this query before and after you begin the test (adapted from here):
SELECT
     table_schema AS the_db,
     table_name AS the_table,
     round(((data_length + index_length) / 1024 / 1024), 2) `size_in_mb`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

You might like to include the information_schema (and the mysql schema) as well since some/much/all of this activity that you are seeing might be occurring there.
This would presumably be dependent on the architecture of your cluster. Inserting the results of these queries into tables of their own should render any comparisons which you may wish to do relatively trivial.
Also, bear in mind that there may not be a whole lot to see, as these writes may simply be UPDATEs of a single or just a few records?
p.s. welcome to the forum and +1 for an interesting question!


How to give a table a "primary selection" to a table it is many-to-many with? Description of Issue / Question

I want to be able to specify a default or primary relationship of a table to a row in the database that the first table already has a many-to-many relationship with.

Example / Usecase:

A user can be a member of multiple organizations. We do this by creating a many-to-many relationship via an association table between "user" and "organization". I want the user to be able to specify a "primary" organization

What I've tried

Schema:

organization:
  - id (integer, pkey)
  - name (varchar)

user
  - id (integer, pkey)
  - email (varchar)
  - primary_org_id (fkey to organization table) <-- "Primary Organization"

user_organization_assoc:
  - organization_id (integer, fkey to organization table)
  - user_id (integer, fkey to user table)
    * This also has a unique constraint on (organization_id, user_id)

My initial issue was DROP dependency issues - this was fixed by properly naming the foreign key constraints. It now "works", but several other theoretical issues arise with this solution.

The most obvious issue is the lack of integrity between the user_organization_assoc table and user.primary_org_id. When a user is removed from an organization, we have to remember to manually update the primary_org_id value of the user table.

Questions

How would you do this?
Am I going about this the wrong way?
This is a concept that is seen a lot in SaaS and other applications, are there any resources on this?

Disclosure

This development work was initially being done in python + sqlalchemy, but now it is mainly a question of principles / theory.

Thanks!

The fact that some relation is "primary" is an attribute of the relation. So it must be stored in relation table. For example, with the name is_primary.

Variant 1.

The field is defined as BOOLEAN (or INT(1) if no BOOLEAN datatype exists).

To ensure that there is only one primary relation per user we may create unique index by (user_id, is_primary). In such case all another, non-primary, records must have NULL in is_primary field. And when we need to change the primary relation we must alter two records - set NULL to previous primary and set TRUE to current one. Of course, the field is defined as nullable with DEFAULT NULL.

Variant 2.

The field is defined as DATETIME (or TIMESTAMP).

To ensure that there is only one primary relation per user we may create unique index by (user_id, is_primary) again. But now we do not need to "clear" the field value in previous primary - we only set this field in new primary to current timestamp, and, when we need the current primary, we simply select the record with the greatest value in this field.

The field do not need to be defined as nullable.

When we insert new record which is not primary, we may use an expression similar to COALESCE(MIN(is_primary) OVER (PARTITION BY user_id) - INTERVAL 1 SECOND, '1980-01-01'). The number of seconds in 10 years practically guarantees that there won't be some problems with set this field value to the new record.

Moreover, some kind of "change history" will exist - for each record we can see does it was somewhere primary one, and if it was - when it was assigned to primary the last time.


Find results with greatest difference I have a database which has a user's real age and the age that people have guessed them to look like in an uploaded photo.

I want to find the photos which have the greatest difference between guessed age and actual age.  What Query could I run?

For example, the two rows that I am interested in are as follows.

age | avg
18 | 34.45
32 | 33.23
45 | 28.21

Thank you

The query below will return the maximum of the difference age from the guessed age:

SELECT MAX(DiffAge) AS MaxDifference
FROM (
    SELECT (`avg` - `age`) AS DiffAge
    FROM `AgeTest`
) AS Result

Sample execution with given sample data:

CREATE TABLE `AgeTest` (`age` INT, `avg` numeric(18,2));

INSERT INTO `AgeTest` (`age`, `avg`) VALUES (18,34.45);
INSERT INTO `AgeTest` (`age`, `avg`) VALUES (32,33.23);
INSERT INTO `AgeTest` (`age`, `avg`) VALUES (45,28.21);

SELECT MAX(DiffAge) AS MaxDifference
FROM (
    SELECT (`avg` - `age`) AS DiffAge
    FROM `AgeTest`
) AS Result

Working Demo: http://rextester.com/BSEEG25743


Best place to keep backup scripts Where is the best place to leave the Database Backup scripts, currently I let them on the Backup Job itself, however I wonder if it's best to leave on a Database

Source control. This way you have a centralized store for all of your scripts so that any DBA/Developer can access them and use them if needed. Tracking changes to core scripts used day to day in your environment is critical.

You could convert them to stored procedures so that they are included in the database backup it's self but this doesn't provide an easy way to view changes to the script that happened at some time in the past.


Online index rebuild higher fragmentation on intermediate level I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on leaf level.
I noticed when I'm running the statement with Online=on the intermediate pages (level 1) of my index are higher fragmented as before (89% in stead of 3%).

The intermediate pages only get defragmented when I'm setting MAXDOP=1.
With the options SORT_IN_TEMBP=ON,ONLINE=OFF the level 2 fragmentation jumps from 0 to 100.

This are the statements that caused an increase of fragmentation on level 1:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=off,online=on,maxdop=1)
ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=off,online=on)
ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=on,online=on)

This statement caused the fragmentation on level 2 go from 0 to 100 but level 1 stays the same:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=on,online=off)

Is fragmentation on intermediate pages something to worry about and what is causing the increase in fragmentation?

Try this:
ALTER INDEX pk_test ON dbo.test REBUILD WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = ON,
    ONLINE = ON,
    ALLOW_ROW_LOCKS = OFF,
    ALLOW_PAGE_LOCKS = OFF,
    FILLFACTOR = 90,
    OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)

This way you will have a FILLFACTOR of 90% and you would probably avoid to have the table fragmentation to spike up again.
Keep in mind that your table size will increase of 10%


Optimising Marksheet Database I am trying to design a database for mark sheet data of a school having different types of exams of different levels or class.
Is it possible to have single table where I can store data in string (array serialize PHP) and perform a search based on subjects easily (Maximum/Minimum/Average).

The table look something like this with bit variations-

Class 5

XXXXX ------ Math ----- Hindi ----- English
Amit -------- 10 ------- 20 ----------31
Alok -------- 10 ------- 20 ----------31

Class 8

XXXXX ------ Math ----- Hindi ----- English ---- Science
Amit -------- 10 ------- 20 ----------31 ----------20
Alok -------- 10 ------- 20 ----------31 ----------23

Using PHP's array serialize I will have marks data in string as

a:4:{s:4:"math";i:20;s:5:"hindi";i:30;s:7:"english";i:23;s:7:"science";i:10;}

Should I have separate table or can I have the condensed table so that database does not become too heavy with too many rows?

You really should not hide the data you want to do arithmetic inside any kind of structure.

Use a table with 4 columns:  class, student, subject, score.

Then things like each student's avg score:

SELECT student,
       AVG(score)
    FROM tbl
    GROUP BY student;


NLS_TIMESTAMP_FORMAT changing automatically and randomlly I am using Oracle 11gR2. My nls_timestamp_format is changing randomly from dd-mon-rr to yyyy/mm/dd.

Does anyone have any clue why this is happening?

First change the parameter at the system level:

alter system set nls_timestamp_format = 'dd/mm/yyyy' scope='both';

This will also update the spfile so even if you bounce the db you'll keep your definition.

Second, check for any specific definitions in your client.
If you're using windows go to

the registry - check if you have the nls_timestamp_format set differently.
the glogin.sql file - this is a file that executes every time you log into sqlplus. there might be an alter session... there that changes the system setting.


Oracle 11g install problem on Ubuntu I ran the runInstaller script in the root dir of the extracted zip files. After some pages of the graphical installer it sent me to run

/tmp/CVU_11.2.0.1.0_macbook/runfixup.sh

as root. It gave me the following errors:

# ./runfixup.sh
Response file being used is :./fixup.response
Enable file being used is :./fixup.enable
Log file location: ./orarun.log
./orarun.sh: 186: [: true: unexpected operator
./orarun.sh: 848: [: unexpected operator
./orarun.sh: 864: [: unexpected operator
./orarun.sh: 882: [: unexpected operator
./orarun.sh: 903: [: true: unexpected operator
./orarun.sh: 1052: [: unexpected operator
./orarun.sh: 1057: [: unexpected operator
./orarun.sh: 1075: [: unexpected operator
./orarun.sh: 1085: [: unexpected operator
./orarun.sh: 1115: [: unexpected operator
./orarun.sh: 1143: [: unexpected operator
./orarun.sh: 1189: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 1228: [: unexpected operator
./orarun.sh: 1284: [: unexpected operator
./orarun.sh: 1342: [: unexpected operator
./orarun.sh: 1426: [: unexpected operator
./orarun.sh: 1451: [: unexpected operator

What shall I do?

a) Use an OS that is certified for installing and running Oracle Database (Ubuntu is not amongst them). Native or virtual machine, your choice.

or

b) here is an unofficial guide for installing Oracle 11.2 on Ubuntu Linux
https://forums.oracle.com/thread/1117155

I have never tried myself, but several people succeeded installing Oracle 11.2 on Ubuntu with the help of this.


Query to get total time logged in per week I need to find the number of hours each user is logged into an application per week. The table structure is as follows:

[ID]
[UserID]
[Time] (datetime field)
[EventType] ("login" or "logout")

There can be many logins/logouts per day. Since it would probably be significantly easier to get the first login and last logout of the day, that is also acceptable (although less accurate).

Any suggestions where to start? I'll probably have to do some partitioning, right?

The table logs the launch of a desktop application. The application will time out on its own without activity. Launching another instance of the application will log out your active session and start a new one, so I don't think there could be an overlap. I do not have control over the data types as they are part of the application. EventType is a string.

There's probably a more elegant solution but using your criteria from your comment to @Erik that the logout will be recorded this should get you what you need, or very close:

DECLARE @UserID sysname;
DECLARE @LastUserID sysname;
DECLARE @Time datetime;
DECLARE @EventType varchar(10);
DECLARE @CurrentSessionRowID int;

CREATE TABLE #results (
        ID int IDENTITY
               PRIMARY KEY
      , UserID sysname
      , LoginTime datetime
      , LogoutTime datetime
      , [Duration(Hours)] AS DATEDIFF(mi, LoginTime, LogoutTime) / 60.  --  datediff by minutes so partial hours can be displayed in the final results
       );

DECLARE userSessions CURSOR FAST_FORWARD
FOR
        --  find all the user sessions in the order they were input for each user
        SELECT  t.UserID
              , t.[Time]
              , t.EventType
        FROM    dbo.test AS t
        ORDER BY t.UserID
              , t.ID;

OPEN userSessions;
FETCH NEXT FROM userSessions INTO @UserID, @Time, @EventType;
SET @LastUserID = @UserID;

WHILE @@FETCH_STATUS = 0
      BEGIN
            --  same user, determine if this is a login or logout and record it in the #results table
            IF @UserID = @LastUserID
               BEGIN
                     IF @EventType = 'login'
                        BEGIN
                              INSERT    INTO #results
                                        (UserID, LoginTime)
                              VALUES    (@UserID, @Time);
                              SET @CurrentSessionRowID = SCOPE_IDENTITY();

                        END;
                     IF @EventType = 'logout'
                        BEGIN
                              UPDATE    #results
                              SET       LogoutTime = @Time
                              WHERE     ID = @CurrentSessionRowID;
                        END;

               END;
            ELSE
               BEGIN
                     -- new user, make sure the first record is login and start recording sessions
                     IF @EventType = 'login'
                        BEGIN
                              INSERT    INTO #results
                                        (UserID, LoginTime)
                              VALUES    (@UserID, @Time);
                              SET @CurrentSessionRowID = SCOPE_IDENTITY();

                        END;
               END;

            SET @LastUserID = @UserID;
            FETCH NEXT FROM userSessions INTO @UserID, @Time, @EventType;
      END;
DEALLOCATE userSessions;

--  Query the results however you want with grouping and aggregates
SELECT  *
FROM    #results AS r;

DROP TABLE #results;


MySQL Insert/Update across 3 tables with 1m+ rows To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works.
I have a database with 1m+ rows, currently organised as follows:
Table: MasterTable
Rows: ID, FirstName, LastName, PetName, PetAge
I would like to split it as follows:
Table: People
PersonID (PK), FirstName, LastName
Table: Pets
PetID (PK), PetName, PetAge
Table: Records
RecordID (PK), MasterTable.ID, People.PersonID, Pets.PetID
PKs in all cases auto-increment so that more records can be added later.
The people and pets tables have been populated using:
INSERT INTO Pets(PetName, PetAge)
SELECT PetName, PetAge
From MasterTable
WHERE 1

INSERT INTO People(FirstName, LastName)
SELECT PetName, PetAge
From MasterTable
WHERE 1

INSERT INTO Records(ID)
SELECT ID
From MasterTable
WHERE 1

So I have three tables. When I try to create the Records table, I can't get anything to work.
I have tried:
INSERT INTO Records(PersonID, PetID, ID)
SELECT People.PersonID, Pets.PetID, MasterTable.ID
FROM MasterTable
LEFT JOIN People ON MasterTable.FirstName = People.FirstName AND People.LastName = MasterTable.LastName
LEFT JOIN Pets ON Pets.PetName = MasterTable.PetName AND Pets.PetAge = MasterTable.PetAge
WHERE 1

I think the WHERE clause might be the problem. I have tried
WHERE Pets.PetName = MasterTable.PetName
and almost every kind of WHERE I can think of.
I have a few questions I'd really appreciate some help with as I'm going out of my mind here.

Does it matter the order of the LEFT JOIN clauses? Does it matter which table is specified first and which is specified last?

I initially tried INNER JOIN but I figure it's just going to join more columns than is necessary, is that right?

If I am inserting firstname and lastname, I can't match on firstname and lastname, right? As in, create the firstname lastname entries and then use that ID to match the next join?

It seems simple enough to split this into three, assign a PK to each, and then create a finale table where PKs relate to PKs, but apparently it's not.
When I add '''LIMIT 5''' the select returns the correct info. Without the limit clause, all my attempts have run for over 24h and not finished. Either they have been stuck copying everything to temp tables, or they have just said "selecting data" as the status.
Can someone please help?
Sorry if something doesn't make sense, I'll clarify as I go.

LEFt JOIN increases the Number of Records, you will have only have 2 two people with the same name and the same pets name and age, but hopefully you get not much of them.
But still you want only a INNER JOIN, vecause you only want records, that are actual in the MasterTable and have a pet.
so you should switch to inner joins
INSERT INTO Records(PersonID, PetID, ID)
SELECT People.PersonID, Pets.PetID, MasterTable.ID
FROM MasterTable
INNER JOIN People ON MasterTable.FirstName = People.FirstName AND People.LastName = MasterTable.LastName
INNER JOIN Pets ON Pets.PetName = MasterTable.PetName AND Pets.PetAge = MasterTable.PetAge
WHERE MasterTable.ID BETWEEN  0 AND 50000

But you limit the number of rows inserted, by only choosing a number records, try as i shown in theexampe with 50000 at first, and see if that is fast enough
And the take the next.
In case you want all people, regardless if the have a pet.
INSERT INTO Records(PersonID, PetID, ID)
SELECT p.PersonID, pe.PetID, m.ID
FROM People p
LEFT JOIN MasterTable m ON m.FirstName = p.FirstName AND p.LastName = m.LastName
INNER JOIN Pets pe ON pe.PetName = m.PetName AND pe.PetAge = m.PetAge
WHERE p.PersonID BETWEEN  0 AND 50000

Here you see that all pöople are choosen to be in the records, even when they have no pets, that is what LEFT JOIN mean.
But here you must limit the number of Entries to be inserted with person.PersonID
With a million records, you need a lot of buffer and tmp space to pluu that of, but you can serialize  by dividing the number of MasterTable.
FYI: If something like this occurs, look into the error logs of MySQL. It will show you what you have to do and what MySQL has encountered for a problem