SQL Server 2017 I can't make a procedure Im quite new to this.
Im trying to make a procedure with recovery. But instead of making the procedure. It just recovers a database instead.

Use sde_sql
Go

Create Procedure RecoveryScript
as
    Alter Database SDE_SQL
    SET Single_User with
    Rollback IMMEDIATE

    use master
    go

    Restore Database SDE_SQL
    FROM DISK = 'C:\Users\HF\Desktop\SQL\SQLDatabaseBackup.bak'
    WITH REPLACE, STATS = 5

First off, the word "GO" is the default batch separator. That tells SQL Server, "Alright, I'm done with this command, and go execute it."

So when you run this:

Create Procedure RecoveryScript
as
    Alter Database SDE_SQL
    SET Single_User with
    Rollback IMMEDIATE

    use master
    go

    Restore Database SDE_SQL
    FROM DISK = 'C:\Users\HF\Desktop\SQL\SQLDatabaseBackup.bak'
    WITH REPLACE, STATS = 5

What you're really doing is this:

Create Procedure RecoveryScript
as
    Alter Database SDE_SQL
    SET Single_User with
    Rollback IMMEDIATE

    use master
    go

SQL Server does that, and then thinks you're done, and starts this in a separate command:

Restore Database SDE_SQL
FROM DISK = 'C:\Users\HF\Desktop\SQL\SQLDatabaseBackup.bak'
WITH REPLACE, STATS = 5

If you want to create a stored procedure to restore a database, put that stored procedure in master itself (or in another database, but just not the one that's about to get restored, since you can't kill a connection while still running a query from it.) You won't need any "use database" commands in there at all.

Finally, as long as we're talking about restoring databases, check out sp_DatabaseRestore in the open source First Responder Kit. If you're using Ola Hallengren's excellent maintenance scripts to take your backups, you can just point sp_DatabaseRestore at your backup folder, and it'll automatically restore the newest available backups of your database. (Disclaimer: I'm one of the contributors on the First Responder Kit repo.)


Will FK ON UPDATE CASCADE work? Intuitively, adding the option ON UPDATE CASCADE to a foreign key constraint will have the effect of updating all referencing columns with the updated value of the key. Would that really work?

Example lifted from manual:

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

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

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    ## HERE'S THE LINE THAT I MODIFIED FROM THE MANUAL, IT USED TO SAY 'ON DELETE CASCADE'
    order_id integer REFERENCES orders ON UPDATE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

I am using Postgres 9.1.

Possible options for the ON DELETE and ON UPDATE clauses are explained in the CREATE TABLE page of the manual.

The default is NO ACTION, so if you have any foreign keys defined without explicitly specifying what action to be performed on updates of the referenced primary or unique key, it is the same as if ON UPDATE NO ACTION was specified:

  NO ACTION

  Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

If you have specified ON UPDATE CASCADE on a foreign key, the referencing rows will be updated accordingly when a referenced row is updated:

  CASCADE

  Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.

Primary keys are usually not updated (and for good reasons) but there are situations where it can be useful. Plus, the foreign key may be referencing not a primary key but a (rarely updated) unique key.


Firebird monitoring tables I am working with a Firebird 2.1.1 database and I started reading about the Monitoring tables.

I am trying to see somehow in real-time what queries does each client performs in the database.

I had a look at the MON$STATEMENTS table but it does not provide much info. I would have expected more content inside the table.

Am I looking in the wrong place or did I not understand the purpose / use of the Firebird monitoring tables?

There are a number of things you need to know when using the monitoring tables:

The monitoring tables (like MON$STATEMENTS) provides you a list of the active or prepared statements at the time a query was run.
The query result stays stable until the transaction is committed! This means that repeatedly executing the query without committing will not yield new information.
Only SYSDBA, the databaseowner or a user with role RDB$ADMIN can see all information, other users can only see information about their own connections.
Your Firebird installation contains basic documentation on the tables in  doc/README.monitoring_tables.txt

Now as to your screenshot: all statements are from the same connection (your own): MON$ATTACHMENT_ID = 1521. Only one statement is currently active (MON$STATE = 1) and participating in transaction MON$TRANSACTION_ID = 90964 and was started at 2012-12-06 10:08 (MON$TIMESTAMP), the rest is inactive (MON$STATE = 0) and therefor has no transaction-id and timestamp. The first statement is also the only one that currently has an actual query (MON$SQL_TEXT), which is the query that actual produced the result in the screenshot. In Firebird statements are allocated separately from the actual query to be executed using that statement.

That is not much, but if you had joined to MON$RECORD_STATS, MON$IO_STATS or MON$MEMORY_USAGE based on MON$STAT_ID you would have received statistics on the query (ie the number of reads, records update, number of pages read or written, memory use). The monitoring tables show information at a specific moment in time, and if a query has already finished when you execute it, you don't get any info (I am not 100% sure, but it might remain available until the next execute of a statement, or until the statement is unprepared or closed).

If instead you need a stream of information and statistics, than you might get more out of the Firebird Trace API. I haven't used it much myself yet, but the article Audit and Trace Services in Firebird 2.5 by Thomas Steinmauer provides a good introduction to it.

In your question you say you expected 'more content', but didn't actually specify what content you need or expected; I hope this provides sufficient info.


How to design this simple DB? How to design this simple DB?

I need to add some things to a DB I use to keep track of servers.

Table looks something like this.

Matrix
| Name        | Description    | Etc  | Etc    |
------------------------------------------------
| Server01    | First Server   | Data | Data   |

I want to add a table called Updates. It should look something like this:

Updates
| Name        | Q1 Updates | Q2 Updates | Q3 Updates |
------------------------------------------------------
| Server01    | Done       | Incomplete | Incomplete |

What is the best way to "link" these tables? I need everything in the Name field in Matrix to be in the Updates table as well. I'm a fundamental noob when it comes to databases, so I need some 101 help with this.

I have some triggers set up for another table that keeps track of other information and that works very well... I could just add more triggers for the table I am wanting to create... but I'm not sure if that's the best method.

What would you do?

EDIT: I want the Name field from Updates to always be an exact replica of what is in Matrix.

Based on what you've got here, it looks like you should be able to JOIN Matrix and Updates together based-on the "Name" field.  If you wanted to see all servers from Matrix that had updates, you could to something like this:

SELECT m.Name, m.Description, u.*
FROM Matrix m
INNER JOIN Updates u ON u.Name = m.Name;

That's based-on a few assumptions:

"Name" is your primary key in both tables.
"Name" is unique in the Updates table (no server would have more than one entry).

"What would you do?"

If it were me, I would create an Updates table that looked something like this:

table: Updates
Name varchar(16)
TimeFrame varchar(3)
Status varchar(10)

Row data would look like:

name     TimeFrame Status
Server01 Q1        Done
Server01 Q2        Incomplete
Server01 Q3        Incomplete

It would have a composite (primary) key of Name and TimeFrame to ensure uniqueness (because in this scenario, "Name" will be repeated).

The advantage here, is that if your time frame for updates is altered/increased at any point (ex: if you start doing updates every 2 months, instead of quarterly) you have the flexibility of adding more updates without having to add a column to the Updates table.  Of course, I'm saying that without knowing what the rest of the fields on Updates are, so maybe that wouldn't make sense.  Or there could be other opportunities for normalization/optimization as well.


Can I find out what version of MySQL from the data files? I have a very odd situation going on here. I had a linux box running ubuntu 8.10 and MySQL server with a Wordpress installation on it hosting an internal blog for our company. The machine that this was running on has crapped out, but I was able to put the drive into an enclosure and gain access to the files and recover the MySQL data folder. We are mostly a Windows company and I have very limited linux experience. Before this machine crashed we were experimenting with moving this blog to a Windows Virtual Machine running PHP for IIS; and we have an older copy of the Wordpress database up and running on MySQL 5.1. I am currently trying to retrieve the latest data from the linux MySQL data folder and import it into the current database but I am having problems.

Problems so far:
1) I do not know what version of MySQL was installed on the linux box
2) I see ib_logfile0, ib_logfile1, and ibdata1 on the linux MySQL data folder, but am not sure if it was truly running InnoDB or not as I also have .frm, .myd, and .myi files in the wordpress folder

If I can figure out the version of the linux box MySQL I might be able to create a virtual machine to run linux and MySQL so I can copy the data folder and then do an export dump; unless someone else knows of an easier way to get the data into my current database.

Any help or guidance on this issue would be greatly appreciated.

Thanks,
Nathan

The cleanest method I can think of is to examine only one table : mysql.user

If you can login to mysql at all, run this query:

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

Here are the answers you should get:

If you get 43, MySQL 5.6
If you get 42, MySQL 5.5
If you get 39, MySQL 5.1
If you get 37, MySQL 5.0

If you cannot login to mysql, copy the table

user.frm
user.MYD
user.MYI

to another machine running mysql.

On that other machine run CREATE DATABASE junk;

Go to the datadir in that other mysql server

Change directory to junk

Copy the three files for the user table into it. If the other machine is a Linux box, remember to run chown mysql:mysql user.*

Login to MySQL on that other machine and run

DESC junk.user;

It will tell the same numbers I mentioned before.

However, if you get 28 that's MySQL 4.1. If you get 17, that's MySQL 4.0. Have fun with that one.

Give it a Try !!!


Will I need to create a pivot or can I alter this query The following Query that I wrote is working to a point.  The problem that I found is that in each pump, cable, etc can be listed in each of the part columns.  Example:

What I would like to get is the well listed with each of the pumps, cables, etc that are there instead of what I am currently getting which is just the first pump, cable, etc.

Here is the query I am running:

        With CTE AS(
SELECT Pull_Date, Well_Name, Part1 Part, Part1_PN PartPN, Part1_SN PartSN FROM testtest
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from testtest
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from testtest
)

Select Pull_Date, Well_Name,
    MIN(CASE WHEN Part='BODH' THEN 'BODH' ELSE NULL END) [BODH],
    MIN(CASE WHEN Part='BODH' THEN PartPN Else NULL END) BODH_PN,
    MIN(CASE WHEN Part='BODH' THEN PartSN ELSE NULL END) BODH_SN,
    MIN(CASE WHEN Part='Cable' THEN 'Cable' ELSE NULL END) [Cable],
    MIN(CASE WHEN Part= 'Cable' THEN PartPN Else NULL END) Cable_PN,
    MIN(CASE WHEN Part= 'Cable' THEN PartSN ELSE NULL END) Cable_SN,
    MIN(CASE WHEN Part= 'Pump' THEN 'Pump' ELSE Null END) [Pump],
    MIN(CASE WHEN Part= 'Pump' THEN PartPN ELSE NULL END) Pump_PN,
    MIN(CASE WHEN Part= 'Pump' THEN PartSN ELSE NULL END) Pump_SN,
    MIN(CASE WHEN Part= 'MLE' THEN 'MLE' ELSE NULL END) [MLE],
    MIN(CASE WHEN Part= 'MLE' THEN PartPN ELSE NULL END) MLE_PN,
    MIN(CASE WHEN Part= 'MLE' THEN PartSN ELSE NULL END) MLE_SN,
    MIN(CASE WHEN Part= 'AGH' THEN 'AGH' ELSE NULL END) [AGH],
    MIN(CASE WHEN Part= 'AGH' THEN PartPN ELSE NULL END) AGH_PN,
    MIN(CASE WHEN Part= 'AGH' THEN PartSN ELSE NULL END) AGH_SN
FROM CTE
GROUP BY Pull_Date, Well_Name

And here is an example of what I am currently receiving:

I am not very familiar with SQL Server PIVOT or if this is even needed in this case.

I appreciate any assistance on this and thank you in advance.

EDIT:

Unfortunately I cannot add a picture of what I would like so I will try to explain it.

I would like to have it all in one row so it would look somewhat like this:

Pull Date| Well_Name | BODH1 | BODH1_PN | BODH1_SN | BODH2 | BODH2_PN | BODH2_SN

Pump, Cable, MLE, etc would also repeat.  I have 20 part columns and would like to pull them so I could easily sort the pump1 through possibly pump5 (some wells have 5 some less).  Eventually this data will be inserted into another table to update it.

EDIT 2:

Link to test data I created: SQLFiddle

This should work.  I took the original CTE, partitioned by part and date and numbered those.  This gave every part on each date it's own number starting with 1.

Then in the case statement, adding WHERE Number = N allows it so that each part is unique.

Finally, the column names were modified to have their respective numbers.

Since this is unwieldy, dynamic sql could be used to generate the repetitive code.

    With CTE AS (SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Part + Pull_Date ORDER BY PartSN) AS [Number],
  Pull_Date,
  Well_Name,
  Part,
  PartSN,
  PartPN
FROM (
SELECT Pull_Date, Well_Name, Part1 Part, Part1_PN PartPN, Part1_SN PartSN
  FROM test
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part4, Part4_PN, Part4_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part5, Part5_PN, Part5_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part6, Part6_PN, Part6_SN from test
) subquery) new
  )

 Select Pull_Date, Well_Name,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN 'BODH' ELSE NULL END) [BODH1],
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartPN Else NULL END) BODH_PN1,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartSN ELSE NULL END) BODH_SN1,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN 'BODH' ELSE NULL END) [BODH2],
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartPN Else NULL END) BODH_PN2,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartSN ELSE NULL END) BODH_SN2,

    MIN(CASE WHEN Part='Cable' AND Number=1 THEN 'Cable' ELSE NULL END) [Cable1],
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartPN Else NULL END) Cable_PN1,
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartSN ELSE NULL END) Cable_SN1,
    MIN(CASE WHEN Part='Cable' AND Number=2 THEN 'Cable' ELSE NULL END) [Cable2],
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartPN Else NULL END) Cable_PN2,
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartSN ELSE NULL END) Cable_SN2,

    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN 'Pump' ELSE Null END) [Pump1],
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartPN ELSE NULL END) Pump_PN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartSN ELSE NULL END) Pump_SN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN 'Pump' ELSE Null END) [Pump2],
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartPN ELSE NULL END) Pump_PN2,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartSN ELSE NULL END) Pump_SN2,

    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN 'MLE' ELSE NULL END) [MLE1],
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartPN ELSE NULL END) MLE_PN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartSN ELSE NULL END) MLE_SN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN 'MLE' ELSE NULL END) [MLE2],
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartPN ELSE NULL END) MLE_PN2,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartSN ELSE NULL END) MLE_SN2,

    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN 'AGH' ELSE NULL END) [AGH1],
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartPN ELSE NULL END) AGH_PN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartSN ELSE NULL END) AGH_SN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN 'AGH' ELSE NULL END) [AGH2],
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartPN ELSE NULL END) AGH_PN2,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartSN ELSE NULL END) AGH_SN2

FROM CTE
GROUP BY Pull_Date, Well_Name


Error code 1290 and inability to import data on table I am running a 5.7.16 version of MySQL on my Windows 8 operating system. I am facing troubles when trying to load datas into a table. I tried with the script

LOAD DATA INFILE 'C:\Users\xxx\Dropbox\LAVORO\xxx\MySqlFiles\xxx.csv' INTO TABLE xxx.vce4 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r' ;

... but the error code 1290 appears:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

If I execute the statement:

SHOW VARIABLES LIKE "secure_file_priv";

... the result is 'secure_file_priv', 'C:\Users\xxx\Dropbox\LAVORO\xxx\MySqlFiles\'.

I am unable to solve the error code 1290, I tried to use the Table Data Import Wizard, which seemed to work (Import results: "Table xxx.vce4 has been used") BUT "0 rows imported".

What is wrong with this? I really can't figure out how to make this work.

As per MySQL documentation

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

So one solution is to place your files under the value displayed under this variable

if the directory doesn't exist create it as being displayed and make sure mysql user have access on it.

 show variables like '%secure_file_priv%';

or change the value in my.ini/cnf file as this variable is read-only and would require a restart

if you want to disable this restriction and can use any directory set it to empty value or change it according to your need

[mysqld]
secure_file_priv=new_path

if you want to disable the feature of loading-unloading set it's values to NULL

Hope it helps


Persisting Prepared Statements I know that the documentation states:

  Prepared statements only last for the duration of the current database session.

Is there any way way to either

save a prepared statement
create code which will regenerate a prepared statement?

If I understand you correctly, you want to use functions.

Those are stored (with their code), their execution plans (may be) cached and so on.  Plan caching is not so simple as it first sounds.  Read https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING and the linked page about PREPARE.

For the additional questions from your comments, PostgreSQL functions don't necessarily protect you from SQL injection.  Building dynamic SQL statements the wrong way (using concatenation without proper quoting) can still open the possibility for such attacks.


mysql insert into indexed table taking long time after few million records I have a table like this

CREATE TABLE IF NOT EXISTS `dnddata` (
  `numbers` varchar(10) NOT NULL,
  `opstype` char(1) NOT NULL,
  PRIMARY KEY (`numbers`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (numbers)
PARTITIONS 25 */;

i have to insert 300 million records. i am inserting 10 million records each time using load data in file from csv file.

To insert 10 million records taking nearly 5 min first time. Time is increasing each time gradually. after 30 million records it stops inserting and memory using 100% server not responding.

below my my.cnf file setting

bulk_insert_buffer_size = 100M
key_buffer = 100M
sort_buffer_size = 50M
read_buffer = 50M

i am using cpu with 2 G memory.

details for 30 million records

    Space usage
Type    Usage
Data    545.3   MiB
Index   694.8   MiB
Total   1,240.1 MiB

MySQL client version: 5.5.14

with out index it is inserting fine 10 million in 50 sec.

Please tell me what kind of setting need to change.

Edit based on user answers

I have changed my.cnf setting to below

key_buffer_size = 1G
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 256M

no use. issue is not resolved.

I have tried below mentioned methods to load data

set autocommit = 0; //for innodb
load data infile into …
COMMIT;

START TRANSACTION;
load data infile into …
COMMIT;

ALTER TABLE dnddata DISABLE KEYS;
load data infile into …
ALTER TABLE dnddata ENABLE KEYS;

No luck..

You should load such a huge file in chunk for faster loading of data,
Here it is mentioned problems loading huge Load local data file and solution how to solve it and make it faster.

http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

MyISAM tables you should follow this steps to fasten loading data:

With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes.

Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.
Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to remove all use of indexes for the table.
Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.
Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.
Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

LOAD DATA INFILE performs the preceding optimization automatically if the MyISAM table into which you insert data is empty. The main difference between automatic optimization and using the procedure explicitly is that you can let myisamchk allocate much more temporary memory for the index creation than you might want the server to allocate for index re-creation when it executes the LOAD DATA INFILE statement.

You can also disable or enable the nonunique indexes for a MyISAM table by using the following statements rather than myisamchk. If you use these statements, you can skip the FLUSH TABLE operations:

ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;

For more information refer these:

http://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html

https://wiki.rice.edu/confluence/display/~as43/Make+LOAD+DATA+INFILE+run+even+faster+for+a+MyISAM+table


Error 8/40 PLS-00364: loop index variable 'I' use is invalid When I execute this stored procedure:
create or replace procedure "delete-archive"
is
begin
for i in (select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
    where FILENAME like 'JDE%')
    loop
    UTL_FILE.FREMOVE ('DATA_PUMP_DIR', i.filename );
    end loop;
end;

I get this error:

8/40      PLS-00364: loop index variable 'I' use is invalid

The owner has execute permission. It was like that GRANT DBA TO pqp;. Do you think that the problem may be permissions?

Answers originally left as comments:

Gerard H. Pille: A stored procedure can't use permissions granted via a role. You need:
GRANT EXECUTE ON RDSADMIN.RDS_FILE_UTIL TO PQP

Albert Godfrind: As an aside, granting DBA to a regular user or application is a very bad idea. It should only ever be granted to an actual DBA account.


Select all most recent records I have this two tables (and their main fields):

    Target:                        Message:

    Id     IdMessage               Id        IdMain        IdReply        DatePosted
    ================               =================================================
    1          7                   7          null          null          2014-12-11 13:45:38.633
    2          8                   8           7             7            2014-12-20 13:45:38.633
    3          9                   9           7             8            2015-01-05 13:45:38.633
    4          11                  11          null         null          2014-12-17 13:45:38.633
    5          13                  13          null         null          2014-12-17 13:45:38.633
    6          14                  14          13            13           2015-02-11 13:45:38.633

And I need to select the most recent messages/replies for each main message:

    Target:                        Message:

    Id     IdMessage               Id        IdMain        IdReply        DatePosted
    ================               =================================================
    3          9                   9           7             8            2015-01-05 13:45:38.633
    4          11                  11          null         null          2014-12-17 13:45:38.633
    6          14                  14          13            13           2015-02-11 13:45:38.633

This's my code:

select targt.IdMessage as Id,
       msg.Subject as Title,
       msg.IdMain as IdReply,
       msg.DatePosted as DatePosted,
       targt.IdTarget as IdUserTarget,
       targt.DateReceived as DateReceived,
       targt.IdUserCreate as IdUserCreate,
       usr.FirstName as UserCreateFirstName,
       usr.LastName as UserCreateLastName
from Target targt
inner join Message msg on targt.IdMessage = msg.Id
inner join [User] usr on msg.IdUserCreate = usr.Id
where targt.IsActive = 1 and msg.IsActive = 1 and
msg.DatePosted = (select max(msgComp.DatePosted) from Message msgComp
                 where msgComp.IsActive = 1 and ((msgComp.Id = msg.IdMain and msgComp.IdMain is null)
                        or msgComp.IdMain = msg.IdMain))

but this code only return the most recent record at all... How can I solve this problem and recent records for each main message?

Your query includes a lot of tables and columns that aren't listed in your sample data. From this query you can join and pull in other things:

;WITH lastMessage AS
(
    SELECT Id, IdMain, IdReply, DatePosted, rn = ROW_NUMBER() OVER
      (PARTITION BY COALESCE(IdMain, Id) ORDER BY DatePosted DESC, Id)
    FROM dbo.Message
)
SELECT t.Id, t.IdMessage, m.IdMain, m.IdReply, m.DatePosted
  FROM lastMessage AS m
  INNER JOIN dbo.Target AS t
  ON m.Id = t.IdMessage
  WHERE m.rn = 1;

SQLfiddle demo


INSERT INTO from 2 unrelated tables I need to INSERT INTO a new table (FinalTable) from 2 unrelated tables, but values from Table1 will need to be repeat for every value pulled from Table2.

Example:

Table1                      Table2
AK                          ID1
AL                          ID2
AR                          ID3
AZ                          ID4
...                         ...

FinalTable
Column1       Column2
ID1           AK
ID1           AL
ID1           AR
ID1           AZ
ID2           AK
ID2           AL
ID2           AR
ID2           AZ
...

Is it possible to do this without being able to JOIN the two SELECTed tables?

This is called a cross join.

INSERT dbo.FinalTable(Column1, Column2)
SELECT t1.col, t2.col
  FROM dbo.Table1 AS t1
  CROSS JOIN dbo.Table2 AS t2;


Issues (Other Than Disk Space) For Using Operating System Partition Other than accidentally filling your entire C:\ drive with data, are there any other considerations for using the OS drive for data files?  Most answers are simply "don't do it", but I'd like to know any technical reasons.
I'm on a dev box and will gladly trade risk for extra IO bandwidth.  I'm refactoring a very large dataset, which generates 10-20 GB (simple) log files and a ton of tempdb activity.  I'm moving the source, read-only tables to sata SSDs.  I'd like to give the refactored data, tempdb data, and log files their own NVMe, but that means one of them would share C: with the OS.

For a production solution it's better to put OS, data, log, and tempdb on different volumes, even if those volumes share a single disk, storage pool, or SAN array.
This limits the blast area of running out of space, and provides separate visibility to the different IO types through the Windows Logical Disk performance counters.
However, it works fine, and is absolutely supported to put everything on C.


Error using convert MySQL in Python I'm practically newbie with MySQL.
I'm trying to do a query to retrieve a preformatted date, obtained from a subtraction, using CONVERT.
This is the query:

sql = f"SELECT CONVERT(timestamp - {sqlToday}, getdate(), 23), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

timestamp is a column of the database, and sqlToday is a date in format datetime.date(2020, 2, 13).
Unfortunately this error returns, and I don't know how to handle it:

  mysql.connector.errors.ProgrammingError: 1064 (42000): You have an
  error in your SQL syntax; check the manual that corresponds to your
  MariaDB server version for the right syntax to use near 'getdate(),
  23), count_issue FROM total_unresolved_issue WHERE project = 'Graphic'
  at line 1

Thanks in advance!

In fact, it is not easy to find the right command we need for queries in the MySQL documentation.
However, by testing, I saw that this should be fine:

sql = f"SELECT DATEDIFF(NOW(), timestamp), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

DATEDIFF(NOW(), timestamp) returns the difference between two dates in days.


Is there anything I can do to increase performance in the future once table gets bigger? I created a table importing an Excel sheet with 600,000 rows. Most of the queries I will run will be using C_CustomerID, they will be like the example below:

select * from testtable where C_CustomerID = 12345678

The table definition is:

CREATE TABLE [dbo].[testtable](
    [id_card] [int] IDENTITY(1,1) NOT NULL,
    [C_CustomerID] [int] NOT NULL,
    [C_AccountID] [nvarchar](255) NULL,
    [C_ProductID] [varchar](20) NULL,
    .......... more columns here ...
) ON [PRIMARY]

I created an index:

CREATE NONCLUSTERED INDEX [inx_profitability] ON [dbo].[testtable]
(
    [C_CustomerID] ASC
)

I will only import more records later on from an Excel sheet. Will export about 500,000 new records every 6 months.

Is there anything I can do to increase performance in the future once table gets bigger?

I am running SQL Server 2012 and Microsoft SQL Server Management Studio 11.0.3128.0.

If all queries are of the form:

select * from testtable where C_CustomerID = 12345678

then what you have already is not entirely unreasonable. There is a cost to retrieving columns not covered by the nonclustered index from the base table (currently a heap), but unless there are many rows per C_CustomerID, this may not be a practical concern.

That said, the current arrangement is not ideal. There is a chance that SQL Server will decide to scan the whole heap for matches, rather than seek into the nonclustered b-tree index and then perform multiple single-row lookups.

In the absence of any other considerations, I would probably replace the nonclustered index with a (non-unique) clustered index:

DROP INDEX inx_profitability ON dbo.testtable;
GO
CREATE CLUSTERED INDEX [CX dbo.testtable C_CustomerID]
ON dbo.testtable (C_CustomerID);

The clustered index defines the logical ordering of the rows in the table, and provides direct access to the whole row (so no lookups needed). To a large extent, a clustered index is largely 'free', since it is not a separate structure with a copy of (some of) the data.

For rows with the same C_CustomerID, SQL Server will add a hidden integer 'uniquifier' to ensure each row is uniquely identifiable internally. You cannot reference this value directly, but it does add a small overhead.

Making this a clustered table (by adding the clustered index) will also likely improve unused future space management for the table, in the event you delete rows (the question mentions an export).

You might choose to specify a fill factor on the clustered index build above (and when performing maintenance) e.g. by adding WITH (FILLFACTOR = 70). This will reserve a proportion of free space at the leaf levels for future inserts (remember the rows and pages are logically ordered by C_CustomerID). The choice of 70 is entirely arbitrary. The value you choose (if any) is entirely situation-dependent. If all queries are as shown above, there is probably little need to specify a fill factor.

In principle, every table should have a key (primary or otherwise) that enforces whatever real-world combination of columns uniquely identifies each row. This could be a nonclustered primary key or unique constraint in your case.


Easy way to get DB Measurements? My senior management would like to have some counts of database objects (databases, tables, rows, stored procedures, etc) to show complexity to the executive committee. Does anyone know of a quick way to get this information? I'm going after system tables to get the info and some use of sp_MSforeachdb, but I'm hoping maybe someone has a query stored somewhere they can share. Of course it's needed by tomorrow and it's already 3:43 pm. We're using Microsoft SQL Server 2008 R2. And if anyone has ever been in this boat before, is there anything else your management wanted to see?
TIA

Try sp_BlitzIndex with the @Mode = 2 switch for detailed inventories per database including object counts, row counts, and sizes. You can also use the @GetAllDatabases = 1 parameter for system-wide results. (Disclaimer: I’m one of the open source script’s authors.)


There is not a real need to have a dates table. However, there are some cool things you can do with a dates table such as add columns to that table that have different pieces of information about that day such as holidays or weekends or week of the year, etc. Instead of doing a function to get that information.


Different Indexes with different query duration I had the following table that holds phone contacts of users.

CREATE TABLE `contacts_phone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playerPhone` varchar(45) NOT NULL,
  `friendPhone` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `by_contact` (`playerPhone`,`friendPhone`),
  KEY `by_id` (`playerPhone`),
  KEY `by_phone` (`friendPhone`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

I built the next query that returns mutual friends of my friends:

SELECT SQL_NO_CACHE phones.*, COUNT(phones.friendPhone) AS mutual_friends
FROM (SELECT cp2.friendPhone
    FROM contacts_phone cp1
    JOIN contacts_phone cp2 ON cp1.friendPhone= cp2.playerPhone
    WHERE cp1.playerPhone = 'PHONE_NUMBER') phones
WHERE phones.friendPhone IN (SELECT friendPhone
        FROM contacts_phone
        WHERE playerPhone = 'PHONE_NUMBER')
GROUP BY phones.friendPhone

Until this point everything was ok :)

As our product grew we have collected 13M rows to this table and had a product requirement to add the users id to this table. So I made the next change:

ALTER TABLE `contacts_phone`
ADD COLUMN `cid` VARCHAR(12) NULL DEFAULT NULL AFTER `id`,
DROP INDEX `by_contact` ,
ADD UNIQUE INDEX `by_contact` (`playerPhone` ASC, `friendPhone` ASC, `cid` ASC),
ADD INDEX `by_cid` (`cid` ASC);

I added the user's id (cid) to the table and filled the missing data. part of the rows didn't have id so I had to use  DEFAULT NULL.

I built a different but pretty similar query but now using cid instead.

SELECT SQL_NO_CACHE phones.*, COUNT(phones.friendPhone) AS mutual_friends
FROM (SELECT cp2.friendPhone
    FROM contacts_phone cp1
    JOIN contacts_phone cp2 ON cp1.friendPhone = cp2.playerPhone
    WHERE cp1.cid = 'USER_ID') phones
WHERE phones.friendPhone IN (SELECT friendPhone
        FROM contacts_phone
        WHERE cid = 'USER_ID')
GROUP BY phones.friendPhone

I expected to get a similar query duration but the result was that when querying with cid the duration is doubled.

What am I missing out? Do I have something wrong in my design?
Could it be because of the nullable index?

Assuming

A "user" is uniquely associated with a "phone"
A "user" having a "friend" (also a "user") is indicated in a table that includes UNIQUE(personPhone, friendPhone).  (UNIQUE is used to prevent redundant entries.)  (in table contacts_phone)

Then the number of friends that 'Rami' and 'Rick' have in common is found

SELECT COUNT(*)
    FROM contacts_phone AS me
    JOIN contacts_phone AS you  USING (friendPhone)
    WHERE  me.personPhone = 'Rick'
      AND you.personPhone = 'Rami'

And the UNIQUE index above serves this query well.

Another approach:

SELECT friendPhone FROM (
    ( SELECT friendPhone FROM contacts_phone WHERE personPhone = 'Rick' )
    UNION ALL
    ( SELECT friendPhone FROM contacts_phone WHERE personPhone = 'Rami' )
                        ) AS x
GROUP BY friendPhone
HAVING COUNT(*) = 2

This finds all our common friends.  Wrap a SELECT COUNT(*) ( ... ) AS y to get the count.  (This is likely to be slower.)

How many different friends Rami's friends have.  First, let's decide on some details using this example:

Rami's friends:  A, B, C, X

A's friends:     D, Rami, C
B's friends:     E, F, A   (guess B doesn't like Rami back)
C's friends:     A, D, Rami
X's friends:     Y, Z

Rami's friends' friends:  D(twice), Rami(twice), C, E, F, A(twice)

Should the count be:

9 -- but that includes dups
6 -- but that goes back to Rami
5 -- excludes Rami, but includes 1st level A and C
3 (D, E, F) -- Maybe this is the count you want??

The desired output is:

A  2
B  0
C  1
X  0

Friend of friend

Let's break the problem into 2 steps.  First, see if this gives you the list of friend-of-friends, together with their counts:

SELECT  c2.friendPhone AS friend_of_friend,
        COUNT(*) AS cnt
    FROM  contacts_phone AS c1
    JOIN  contacts_phone AS c2  ON c2.personPhone = c1.friendPhone
    WHERE  c1.personPhone = 'Rami'
    GROUP BY  c2.friendPhone;

That should include people that are not directly your friends.  So, let's filter those out.  One way is thus:

SELECT  fof.friend_of_friend, fof.cnt
    FROM  ( ... the above query ... ) AS fof
    JOIN  contacts_phone AS c1  ON x.personPhone = c1.friendPhone
    WHERE  c1.personPhone = 'Rami';

(Other approaches might involve HAVING or EXISTS.)


How to prevent seek pagination from doing sequential scan over entire table I have 3 tables and a materialized view:

resource_categories contains all category names and metadata

create table if not exists resource_categories (
    category_id INT,
    title VARCHAR(255),
    content TEXT,
    icon VARCHAR(50)
);

resources contains all resources under each category

create table if not exists resources (
    resource_id INT,
    title VARCHAR(255),
    content TEXT,
    link VARCHAR(1000),
    category_id INT REFERENCES resource_categories(resource_id),
    icon VARCHAR(255),
    created_at DATE,
    updated_at DATE
);

resource_votes contains user liking a resource or disliking it

create table if not exists resource_votes (
    resource_id INT REFERENCES resources(resource_id),
    user_id INT,
    vote BOOLEAN
);

resource_votes_aggregate a materialized view with likes per resource_id

CREATE materialized view resource_votes_aggregate AS
SELECT
   resource_id,
   COUNT(
   CASE
      WHEN
         vote = TRUE
      THEN
         1
   END
) AS likes
FROM
   resource_votes
GROUP BY
   resource_id;

I want to execute the following queries
Find all resources sorted in descending order of likes (find most liked resources)
Find all resources sorted in descending alphabetical order of their titles
I want to use SEEK / KEYSET pagination for efficiency

Query to find resources in descending order of their likes page 1

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes
FROM
   resources r
   LEFT JOIN
      resource_votes_aggregate a
      ON r.resource_id = a.resource_id
ORDER BY
   likes DESC,
   resource_id DESC LIMIT 5;

Execution Plan

QUERY PLAN
Limit (cost=74.50..74.52 rows=5 width=157) (actual time=0.058..0.060 rows=5 loops=1)
-> Sort (cost=74.50..76.80 rows=918 width=157) (actual time=0.058..0.058 rows=5 loops=1)
Sort Key: (COALESCE(a.likes, '0'::bigint)) DESC, r.resource_id DESC
Sort Method: top-N heapsort Memory: 25kB
-> Hash Right Join (cost=12.03..59.25 rows=918 width=157) (actual time=0.032..0.046 rows=50 loops=1)
Hash Cond: (a.resource_id = r.resource_id)
-> Seq Scan on resource_votes_aggregate a (cost=0.00..30.40 rows=2040 width=12) (actual time=0.001..0.004 rows=38 loops=1)
-> Hash (cost=10.90..10.90 rows=90 width=149) (actual time=0.021..0.021 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on resources r (cost=0.00..10.90 rows=90 width=149) (actual time=0.003..0.010 rows=50 loops=1)
Planning Time: 0.050 ms
Execution Time: 0.075 ms

This and all the other queries generate a sequential scan which beats the entire purpose of seek pagination

How do I fix this. Help is super appreciated

LINK TO DB FIDDLE

There may be more potential for improvement. But the obvious big issue is this:

SELECT
   r.resource_id,
   title,
   COALESCE(likes, 0) AS likes
FROM
   resources r
   LEFT JOIN
      resource_votes_aggregate a
      ON r.resource_id = a.resource_id
ORDER BY
   likes DESC,               -- refers to output column!
   resource_id DESC
LIMIT 5;

The manual:

  If an ORDER BY expression is a simple name that matches both an output
  column name and an input column name, ORDER BY will interpret it as
  the output column name. This is the opposite of the choice that GROUP BY
  will make in the same situation. This inconsistency is made to be
  compatible with the SQL standard.

So even if there is an applicable index including resource_votes_aggregate.likes, it cannot be used as the column is hidden behind the expression COALESCE(likes, 0) AS likes - reflected in the query plan as:

  Sort Key: (COALESCE(a.likes, '0'::bigint)) DESC

Fix with:

SELECT
   r.resource_id,
   r.title,
   COALESCE(a.likes, 0) AS likes
FROM
   resources r
   LEFT JOIN
      resource_votes_aggregate a
      ON r.resource_id = a.resource_id
ORDER BY
   a.likes DESC NULLS LAST,  -- refers to input column!
   r.resource_id DESC
LIMIT 5;

This refers to the input column now and qualifies for index usage. It's substantially cheaper to evaluate in any case, even without index, so you can't go wrong with this.

Since likes obviously can be NULL, be sure to add NULLS LAST. See:

Sort NULL values to the end of a table

Table-qualification for all input columns (like I added) is a good way to avoid confusion - not only for the problem at hand.

MATERIALIZED VIEW

The MV in your fiddle would be more efficient like this:

CREATE MATERIALIZED VIEW resource_votes_aggregate AS
SELECT resource_id
     ,(count(*) FILTER (WHERE vote))::int AS likes  -- faster
FROM   resource_votes
GROUP  BY resource_id;

The aggregate FILTER is typically faster.
count() returns bigint (8 bytes). Cast to integer (4 bytes).

This is the all-important index (missing in your fiddle):

CREATE INDEX ON resource_votes_aggregate (likes DESC, resource_id DESC);

With two integer columns, index tuples fit the minimum size of 8 bytes. (With bigint for likes, it would be 16 bytes (12 + 4 padding). See:

Is a composite index also good for queries on the first field?
Configuring PostgreSQL for read performance

Obviously, you also need an index on resources(resources_id). If that's the PK, then it's there.

If you use this query a lot, and preconditions for index-only scans are given, a multicolumn index on (resources_id) INCLUDE (title) may pay. See:

Can Postgres use an index-only scan for this query with joined tables?

Optimized query

If Postgres is not smart enough to give you the best query plan, this equivalent query will:

(
SELECT r.resource_id, r.title, a.likes -- COALESCE not needed
FROM   resource_votes_aggregate a
JOIN   resources r USING (resource_id)
ORDER  BY a.likes DESC                 -- NULLS LAST not needed
        , a.resource_id DESC           -- perfect for above index
LIMIT  5  -- logically redundant, but may help with best plan
)
UNION ALL
(
SELECT resource_id, title, 0 AS likes
FROM   resources r
WHERE  NOT EXISTS (  -- only rows without likes
   SELECT FROM resource_votes_aggregate a
   WHERE  a.resource_id = r.resource_id
   )
ORDER  BY r.resource_id DESC
LIMIT  5  -- logically redundant, but may help with best plan
)
LIMIT 5

The second SELECT will typically be never executed, if there are at least 5 resources with likes. See:

Way to try multiple SELECTs till a result is available?
Select first record if none match

Aside

You mentioned pagination. Be sure to avoid LIMIT / OFFSET for big tables. See:

What is the recommended way to join junction tables for efficient ordering/pagination?


How to profile MySQL during a period of time I want to know during the execution of sysbench --time=100 ..., the bottleneck (function) of MySQL. So I want to profile MySQL when executing sysbench --time=100 ....
I know by set profiling = 1;, I can get such profile for a specific query, but not for a period of time (100 seconds). What can I do?
Thanks.

In my experience, PROFILE always said that 99% of the time was spent in some "useless"  category, such as "sending data".  Such categories 'never' translated into anything actionable.  PROFILE is being deprecated -- good riddance.
EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON ... have some use.  So does the "optimizer trace".
Use the slowlog: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog And you can do that on a live system.
Another technique for looking into what is going on is with the "Handler" counts.  This works even when a table is [currently] so small that timings are not very useful.  Details:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts


Linked servers: Transaction context in use by another session I'm using linked servers to perform some queries in a distributed transaction. It doesn't work in a distributed transaction. I get the following error:

  Transaction context in use by another session.

Not even this simplified example will work:

IF EXISTS(SELECT server_id from sys.servers WHERE name = 'SomeServer')
BEGIN
       EXEC sp_dropserver  @server = N'SomeServer';
END

EXEC sp_addlinkedserver
   @server = N'SomeServer',
   @srvproduct = N'',
   @provider = N'SQLNCLI',
   @datasrc = N'localhost';

EXEC sp_serveroption
       @server = N'SomeServer',
       @optname = 'rpc out',
       @optvalue = 'on';
GO

BEGIN DISTRIBUTED TRAN;

EXEC('SELECT TOP 100 * FROM [SomeServer].[SomeDatabase].dbo.tblFoo')

ROLLBACK;

EXEC sp_dropserver  @server = N'SomeServer';

Removing the work distributed, or removing the begin/rollback tran entirely also works. Running sp_whoisactive returns no rows. I have had to restart the server (on my laptop) several times because there were distributed transactions I was unable to kill even if I killed all the spids returned by sp_whoisactive.

In those possible related instances, killing thos spids more then once gives me:

  SPID X: transaction rollback in progress. Estimated rollback completion: 0%.

http://msdn.microsoft.com/en-us/library/ms188716.aspx

  Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.


TDE Encryption on database tripled size of backup files After enabling TDE Encryption my database backup file .bak is 3x larger than before. Is there a way to improve this? Is this due to compression not being as efficient on an encrypted file?

After enabling TDE Encryption my database backup file .bak is 3x larger than before.

Correct.

Is there a way to improve this?

Without removing TDE? No.

Is this due to compression not being as efficient on an encrypted file?

Yes.
You don't mention your version, but this has apparently been fixed/improved/added in SQL 2016:
Backup Compression for TDE-enabled Databases

As illustrated in Figure 1, in SQL Server 2014 (and prior versions) backup compression doesn’t help reduce the backup size of a TDE-enabled database. However, due to the time spent in attempting the compression, the backup takes much longer to complete.
Things change completely in SQL Server 2016. You not only get great compression of the backup file size, but the backup time is reduced significantly as well, illustrated in Figure 2.

Take note of the warning on this post, there were apparently some potential for corruption that was resolved with a later patch.


Get wall_post and profile data for several users I'm trying to build a query for fetch wall_posts and profile fields for several user_id. First see the images at: http://www.dropmocks.com/mBjQ7P. The table with id_user_rq, id_user_ap is the table where I handle the relations. Meaning 1 and 2 are friends and 1 and 3 are friends too because the directionality is 1,2 | 2,1 and 1,3 | 3,1. So what I need here is to get the wall_post for users 1,2,3 and also the profile data for the same users. Wall_posts is image #2 and profile is the first image. I've tried this query:

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid)
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

But it's not working because I only get the wall_posts and profile fields for user 1. What I'm doing wrong?

Cheers and thanks in advance

Here is your original query

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid)
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

The WHERE is impossible to fulfill because default_wall_posts.userid not be 1 and 2 and 3 at the same time.

Try these changes

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid)
WHERE
    default_wall_posts.userid IN (1,2,3)
ORDER BY
    default_wall_posts.date_created DESC;


How to have PostgreSQL running forever with constant insert and delete per day Question:

Lots of rows are getting inserted every second. Batch deletion is performed when the disk is close to full. This causes two problems:

It is impossible to examine actually how much space in how many pages are reusable. All I see is the disk is almost full, because the database does not return disk space to OS after deletion.
Fragmentation happens. The disk usage slowly grows, and eventually becomes full, crashing the machine.

Solutions I have considered:

Monitor disk usage, and perform a VACCUM FULL when the disk is 48% full. But reserving 52% (yes, the disk is mostly occupied by database) of the disk just to delete records sound very wasteful.
Partition my tables by hour of record creation time. Only delete old partitions and hope that the deleted paritions free up complete pages and so autovacuum will return disk space of those completely empty pages.
Use a on-file database like SQLite. Separate data into files by hour. Only load the most updated 24 hours unless otherwise specified. When I need to delete, just delete the files. I haven't tried this, but it sounds like lots of overhead to attach all the 24 databases every hour.

I really appreciate any suggestion. Below are the additional details. Many many thanks!

Context:

I am currently using PostgreSQL to host a streaming of live social media posts. There is a streamer that perform batch inserts, and a middleware that perform purely-read queries for client requests. The whole application is delivered using Docker image.

As the streamer inserts records, some day the disk will be full. So we need to delete records. But deleting records doesn't reclaim disk space and can cause fragmentation. So ultimately a VACCUM FULL is needed to compact the tables again.

Requirement:

The users who deploy this app is non-technical. They don't even know what is a shell/terminal.
The users will perform no maintenance for the database. So VACUUM FULL is an option only when the disk have enough space for the new tables.

based on the requirements.

Option 2 looks like the best solution,  creating new partitions at X internal and dropping the older data

Partitions in Postgresql creates independent tables when a partition is dropped the file is deleted reclaiming disk space

You can do this with a PgAgent task,  to pre-create the partitions for the next 24 hours, then a task that runs every hour dropping the older partitions.

You want to use Range type partitions then use Alter Table Detach Partition or Drop Table

this code will get you close the partition rule will have to tweaked

create table  table_of_records(
insert_date  timestamp default now(),
insert_hour integer default date_part('hour', now())
other_columns) PARTITION BY RANGE (insert_hour);

 -- pre create the next days tables
 create or replace function create_partitions();
returns boolean
language 'plpgsql'
as $code$
declare
 _counter integer = 0;
 begin

loop
   if _counter > 24 then
      exit;
   end if ;

     execute 'CREATE TABLE table_of_records_' || _counter::text
         || ' PARTITION OF table_of_records
          FOR VALUES FROM (_counter) TO (counter+1);';

   _counter = _counter +1;
end loop;
return true;
end;
$code$

create or replace function drop_partition(phour integer);
 returns boolean
 language 'plpgsql'
 as $code$

 begin
 execute 'drop table table_of_records_' || phour::text;
 return true;

end;
 $code$

https://www.postgresql.org/docs/10/ddl-partitioning.html


Do I need to perform db.fsyncLock() when running a replica set without journal enabled I'm running MongoDB 3.2.9 with WiredTiger on 3 EC2 instances.  When backing up the database volumes using elastic block store snapshots, do I need to perform db.fsyncLock() when running a 3 node replica set where all nodes are running without journal enabled?  Additionally, for reference, my storage.dbPath maps to a dedicated EBS volume for /data on each mongod instance on each EC2 server.  There are also separate dedicated volumes for logs.

I'm working off of the below documentation for reference, but it's not clear how these steps map from single instance deployments to replica set deployments:
https://docs.mongodb.com/ecosystem/tutorial/backup-and-restore-mongodb-on-amazon-ec2

If I do need to run db.fsyncLock(), does the lock apply to all instances on all EC2 servers?  The documentation says that it locks the 'entire' mongod instance, but it's not clear to me whether the instance in this case refers to an instance on a single EC2 server, or if this applies to all instances in the replica set spread over all EC2 servers (3 EC2 servers in my case).

Yes, to get a consistent backup (when journaling is not enabled) you need to db.synclock your mongod. You use synclock only at that node from where you take the backup (and you only need to make the backup from one node; you can restore whole replica set from one backup).
db.synclock locks only that node where you give this command, other nodes work as they were, so lock (and make the backup) one of the SECONDARY nodes.

Addition, you DON'T need to make the backup of arbiter, because there is no data to be backuped. Arbiter is just "voter" or "observer" at replica set.


How to remove temp tables from SQL Server? I want to remove temp tables that I was using in my script in SQL Server 2008. I can't find a way to remove it without writing a few lines of code. I have not been able to find code that works properly. Is there any way to do this without writing code at all? If not, what should be my approach? Will copying my code in a new script and then creating same name temp tables help?

Any help/advice/suggestion much appreciated.

Temporary Tables are dropped when the session ends. So, they will be dropped when you close your SSMS window, or your application session ends.

If you are working in SSMS developing code (and keeping the session open) then you'll need to drop them explicitly via...

drop #tempTable

Another common method, to prevent you from attempting to drop a Temporary Table that doesn't exists, is to check if it exists first.

if object_id('tempdb..#tempTable') is not null
drop table #tempTable

create table #tempTable...
or
select into #tempTable

A note on some helper functions from Aaron Bertrand should be mentioned.


Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)? SQL server 2008 R2 query optimizer puzzle

We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.

This is fast, 13 seconds,

select * from bigtable1
except select * from similar_bigtable2

This sorts the output and is also fast, 13 seconds as well,

select * into #q from bigtable1
except select * from similar_bigtable2
select * from #q order by sort_column

While this is enormously slow:

;with q as (
    select * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

And even a "trick" that I sometimes use to hint SQL Server that it needs to precalculate a certain part of the query before it moves on, doesn't work and results in slow query as well:

;with q as (
    select top 100 percent * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

Looking at the query plans the reason is not hard to find:

SQL Server places two sorts of 9 million rows before the hashmatch, while I would prefer it to have added only one sort of 70.000 rows after the hashmatch.

So the question: how can I instruct the query optimizer to do that?

The main difference between these two query plans is in fact in the difference of Hash  Match and Merge Join. Hash Match is more efficient and as you can see the query runs faster in option 1 (not using CTE).

CTE is a great tool, but it seems to be not efficient in two cases,Complex Predicates or Non-Unique Parent/Child Key. In your case there is no unique key and SQL server has to sort the data sets first to be able to fulfill your requirement.
Have a look at the link below which tells you more on this issue:
http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx

So it seems either you have to accept its slowness or rewrite the logic with WHILE loop which can be more efficient.


Insert sum of the row data of each record into separate column in postgreSQL? I have a table

   id   name     class  social  science math  Total
    2   Max      Three  85        56     85
    3   Arnold   Three  55        40     75
    4   Krish    Four   60        50     70
    5   John     Four   60        80     90

I have to find total of each student's marks and insert to column Total.

While running this query -

INSERT INTO student(Total)
SELECT (social + math + science ) as Total from student;

I am getting in this format -

   id   name     class  social  science math  Total
    2   Max      Three  85        56     85
    3   Arnold   Three  55        40     75
    4   Krish    Four   60        50     70
    5   John     Four   60        80     90
    6                                          226
    7                                          170
    8                                          180
    9                                          230

How to correct this I am not getting.

Use an UPDATE statement.

update student
   set total = social + math + science;

But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values.

In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the total.

You should rather remove the column completely and then create a view doing this for you. Otherwise you need to update that column each time one of the other three columns are changed.

create view total_score
as
select id, name, class, social, science, math,
       social + math + science as total
from student;

Plus: your name column should be defined as NOT NULL.


SQL Oracle export error I'm trying to export a database content to a dump file, so I'm doing the following:

[root@serverId TEST]# ps -ef|grep pmon|grep -v grep
oracle   25371     1  0 Sep29 ?        00:02:22 ora_pmon_SIDPA

[root@serverId TEST]# echo $ORACLE_HOME
/home/oracle/product/10.2.0

[root@serverId TEST]# echo $ORACLE_SID
SIDPA

[root@serverId TEST]# echo $PATH
/home/oracle/product/10.2.0/bin:/home/oracle/product/10.2.0//bin::/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/cca/CCA_SRV:/root/bin

[root@serverId TEST]# exp userId@//serverId:1521/SIDPA FILE=dumpuserId.dmp LOG=dumpuserId.LOG OWNER=userId

Export: Release 10.2.0.3.0 - Production on Wed Oct 21 09:10:56 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

EXP-00056: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
EXP-00000: Export terminated unsuccessfully

[root@serverId TEST]#

As you can see I already set the env vars $ORACLE_HOME and $ORACLE_SID as far as I understand correctly. I also set the PATH, so I think it should work but unfortunately, I does not.

Any comments about what to try will be welcomed.

Thanks in advance!

The syntax should be exp username/password@instance , please also check if the listener is running ps -ef|grep tns and if it has the service listed lsnrctl services (the error "ORA-12154: TNS:could not resolve the connect identifier specified" points out that there is a problem related to that). You can also use the new tool expdp which in my opinion is more easy to use. Ex: expdp sys/password schema=userID dumpfile=userID.dmp directory=data_pump_dir logfile=userID_dmp.log  . You can see where the data_pump_dir points to from sqlplus select * from dba_directories and even create your own directory CREATE DIRECTORY dmpdir AS '/opt/oracle'; and use it in the directory clause. But if you want to use exp, check the listener and also the syntax of your command.


Can we Configure Change Data Capture in another Database? I want to apply Change Data Capture(CDC) to database DB1's tables and want to log everything in DB2.

So, could it be possible to capture table DB1's changes to DB2?

No, it will only work within the same database. After all, what do you expect to happen if the second database goes offline, gets dropped, becomes corrupt, has permissions changes, etc.?

Now, once the CDC tables are created, I guess one potential workaround would be to place INSTEAD OF INSERT triggers on them, and divert the data to identical tables in another database that you manually create. But I haven't tested this, it would hardly be supported, and you would also lost a lot of the inherent CDC functionality (think about all the LSN-based functions that would now obviously not work). And again, I don't know what would happen when the other database goes offline, gets dropped, etc...


What is my database name? I'm trying to connect to a microsoft SQL database but I dont know its name.

I suspect it will be something of the form servername\databasename

It would be good if I could just use SELECT DB_NAME() but since I don't know what its called, I cant run the query in the first place.

I can see that there is a SQL port open in zenmap and when I look at the server I can see that the process is running in the task manager.

EDIT

When just using the server name I get an error message

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (.Net SqlClient Data Provider)

Ok, there are a few things to explain first.

(1) The Connect to Server prompt within the Login properties is only asking for authentication. You cannot specify a database name within the Server Name field of that window in order to connect to it. For the server name you specify either server or server\instance name. You would connect to a specific database by setting this via the Connection Properties tab:

(2) Folks that reference using the <browser for more...> option within the login portion should understand that in a default installation of SQL Server 2012 the Browser service is disabled. So even on a local installation you will not be able to see your server via this method.

Ok, now if you need to find out the "name" of the SQL Server instance you want to connect to you can follow any of the methods below (this is not an exhaustive list, just the most common ones to use):

SQL Server Configuration Manager

Via the Start Menu you would find SQL Server Configuration Manager and open it to look at the Services to see the list of SQL Server components installed on the server. You can also access this, if on the server, via the Computer Management console (you may actually see it show up twice if more than one version of SQL Server is installed, as there is on my  machine):

PowerShell

A simple one liner can provide the same information you will see in SSCM or the Services Management Console (services.mcs) if you are local to the server:

If you happen to be remote you can try to use this command but it will not work if proper access is not granted: Get-Service *SQL* -Computer Server1

To connect to the instances you find with SSMS if you see the services listed as SQL Server (MSSSQLSERVER) this is a default instance and if you are on the physical server you can use the server name, simply put a period (.), or localhost.

If you find a named instance it will be noted as SQL Server (instance name), as on my machine I have a named instance of SQL14 so I would put localhost\SQL14 since I am on my machine. If I had this on a server and was connecting remotely it would be the actual server name instead of localhost.


Build Restore History table We restore a database daily that we download from an FTP site.  I would like to build a history table that tracks when the database is restored on our SQL Server.  This is easy.  My issue is sometime the download is incomplete and therefore the restore does not work and throws an error.  I would like to add logic to my history process to insert failed if the restore fails.  Not sure how to handle this logic and am looking for assistance.

This is the error we get most frequently when the failure occurs.

Error: 3266, Severity: 16, State: 1.

The backup data at the end of "F:\Backups\DB.bak" is incorrectly formatted. Backup
sets on the media might be damaged and unusable. To determine the backup sets on the
media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run
RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using
BACKUP WITH FORMAT, which destroys all the backup sets.

What about using the tables already in the MSDB database, Restorehistory, BackupSet and BackupMediaFamily?

These databases will already have all of the backup and restore history. I would check though that in your particular case what the behaviour looks like in the Restorehistory table. You should be able to check in these tables that after your restore process whether it was successful or not and provoke the appropriate action you want.

http://msdn.microsoft.com/en-us/library/ms188062.aspx


Personal Instance on Personal Computer I finally got my hands on a copy of SQL Server 2008 R2 Developer Edition.  I have installed an instance on my server at home.  I wanted to get all the Management Tools (SSMS, Profiler, etc.) on my laptop so I removed my copy of SQL Server Express.  Then I installed the Management Tools (Complete) on my laptop.  It's great, I can hit the instance and everything.

But for the times when I'm away from my local network and not able to connect to my server's instance, what is recommended?  Should I reinstall an install of SQL Server Express just for testing purposes?

I guess what my question is, is SQL Server Express less taxing on my laptop than if I was to install a full version instance?  Or is it equivalent to installing my Developer Edition?

I could always install the Developer Edition (Enterprise) on my laptop and have the SQL Server services start manually so I'm not hogging resources when I am connected to my local network (and server that houses my active instance).

What is advisable here?

First impression I have, if you're needing your laptop for testing code you're running against a full version of SQL 2008, you'd want your laptop running a full version of SQL 2008.

You hit the nail on the head with setting the services to start manually. You could then have a simple .bat file to start and stop the SQL services when needed. This is exactly what I do on my home PC when needing to play with some databases.


Error restoring Postgres DB I'm trying to do a pg_restore on some SQL files. Here's the command I used when I dumped from the other database:

pg_dump -f /home/mctools/public_html/cp/upload/manufacturers.sql \
        -Fc -i -v -O -x -t manufacturers \
        -h localhost -p 5432 -U aztools_tools aztools_dbuser

And here is the restore command:

pg_restore -d mctools_dbuser -Fc -c -i -O -x  \
           -h localhost -p 5432 -U mctools_user \
           /home/mctools/public_html/cp/upload/manufacturers.sql

Here is the error I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2165; 2606 27769168
                            CONSTRAINT manufacturers_pk aztools_tools
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop constraint
                            manufacturers_pk on table manufacturers
                            because other objects depend on it
DETAIL:  constraint products_fk01 on table products depends on index manufacturers_pk
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: ALTER TABLE ONLY public.manufacturers DROP CONSTRAINT manufacturers_pk;

What do I need to do to fix this?

The -c option of pg_restore does this:

  Clean (drop) database objects before recreating them.

As you seem to only migrate a single table from here to there, you can safely omit -c from your command line.  If you only need the data, you should tell it to pg_dump instead, by using the -a option.  If your table structure is different as well, then you should prepare an ALTER TABLE statement manually to reflect those changes in the target database.  (You could as well use a few tools for the latter, but for only one table the manual solution is usually much faster.)


How can I have intensive stored procedures run on a different machine to where tables are stored? For a home air quality monitoring project I am building, I am collecting sensor readings and storing them on my Synology server, which includes a MariaDB instance.

The Synology is great for managing things like RAID arrays and automated backups, but it doen't have a particularly impressive CPU. I also use it for all sorts of other purposes, so I don't want to burden the CPU too much.

I am also building a web server (actually a Shiny Server) on an Ubuntu machine to serve a web page of some analytics on top of the data. I want part of these analytics to be calculated on demand by a stored proc. However, I want the stored proc to use the CPU resources of the Ubuntu machine. But I want the data to be managed and backup up by the Synology.

So my plan is to have a DB in the Synology for storing the data, and have another DB with federated tables in the Ubuntu. The stored proc runs on the Ubuntu. The stored proc only reads data from the Synology, transforms it, and returns it to the caller. It doesn't write anything back to the Synology.

Does this seems like a good idea? How would you do it?

For the last 20 years, CPUs have only slightly improved in performance.
MySQL rarely tops out a CPU these days.  When it does, there is usually an index that can be added or some SQL changed to make it quite down.
Hence, I suggest that the CPU you have is not that bad.

"Calculating analytics on demand" is a common problem in Data Warehousing.  There is a common solution:  build and maintain Summary table(s)  You can still use a Stored Proc to interface with those table(s) to provide the analytics, but many times as fast (elapsed time).

Federated?  Ouch.  That can be terribly slow.  There are cases where fetching each row is a roundtrip to the other machine.  Consider connecting to the other machine(s) and querying them directly.

Once you have gotten further in your design, start another Question for further advice.  Please provide data sizes, RAM and disk sizes, what the analytics are, schema, etc.

In particular, be sure to minimize disk footprint from the start.  Don't use BIGINT except in rare cases.  Don't use DOUBLE; pick something smaller, normalize 'names', etc.

Now you have me worried that you are at the mercy of whether Synology has the smarts to follow the above hints??


Normalization and Breaking down tables for InnoDB I'm designing a web application, something small but deals with lots of read and inserts.
Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table
called User which has a bunch of stuff in it. I broke it down into smaller tables such as
address table and bio table. Here is an example :

Original table

User table
----------
user_id   PK
user_email
user_pass
user_dob
user_gender
user_country
user_state
user_city

After breaking it down

User table
----------
user_id   PK
user_email
user_pass

Bio Table
----------
user_dob
user_gender

Address Table
-------------
user_country
user_state
user_city

So do you guys think, having smaller table but several of them is better for a high
traffic database or should I keep them all in one table ?

This is how I see it, it's better to have it separate, that's relational database

If N User can have 1 address, the design should be:

if N user can have M address (user with more address, and an address for multiples user), should be:

For both design, the table USER, Column ID is the Primary key of that table, and User_id is just a FK.
In the second design, Table Address, column ID, it's another PK, that make a relation N:M with user, and create "user_has_table" with 2 foreign keys (user PK and address PK).


MySQL Workbench Loses Saved Connections I've been using MySQL Workbench as my go-to management application for quite some time. However, recently (namely, in my most recent install), it will every so often lose the saved connections I've put in. It doesn't happen every time, but sometimes, when I open it up, there won't be any saved connections and I have to re-enter them.

Does anyone know of a way to fix this? The only thing I've been able to find are the release notes, which claim to have fixed this issue over a year ago (ironically, I never had an issue with that until recently).

I'm currently running Lubuntu 11.04 64bit.

Did you get the very, very latest?

The latest stable build (here) is 5.1.19.  However, the bug fix has been put in place for 5.2.21 (per here).

The absolute latest version available is 5.2.34, which should have the bug fix.  However, that's not the latest stable version; it's just the latest generally available (GA) version.  (It's available here, for what it's worth.)

Edit

Since you have the latest version (with the alleged bug fix), I would report this as a new bug.

My only advice... Try to figure out if there is an exact sequence of steps that you can perform to make this happen every time.  Then give them all the information you can (including the version number you're using).


Proper database structure for storing multiple foreign keys I am using MySQL 5.7.25 and need to store multiple foreign keys for multiple columns.

Scheme of my table:

CREATE TABLE `tours` (
  `id` int(10) UNSIGNED NOT NULL,
  `countries` text NOT NULL,
  `themes` text NOT NULL,
  `code` char(5) NOT NULL,
  `type` tinyint(3) UNSIGNED NOT NULL,
  `difficulty` int(10) UNSIGNED NOT NULL,
  `paceLevel` int(10) UNSIGNED NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The countries column should store country IDs like: 10,12,14.
Storing data that way forces me to use LIKE query which I have to avoid for better performance.
How you would design this kind of tables?

UPDATE:

I created another table to store meta table of tours:

CREATE TABLE `tour_meta` (
  `id` int(10) UNSIGNED NOT NULL,
  `metakey` int(10) UNSIGNED NOT NULL,
  `metaval` int(10) UNSIGNED NOT NULL,
  `tour_id` int(10) UNSIGNED NOT NULL,
  `status` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

metakey -> is something predefined, like 4 for countries,
metaval -> is real data id, for example country id

But it will be useless when I need to filter the data like:

SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

Brazil ID = 5, Discovery ID = 3

SELECT *
FROM tour_meta
WHERE metakey = 4 -- (predefined meta key for country data)
  AND metaval = 5 -- (country ID)
  AND metakey = 5 -- (predefined meta key for theme data)
  AND metaval = 3 -- (theme ID)

UPDATE 2:

@Akina helped me a lot and the following query solved my problem:

SELECT tour_id, COUNT(tour_id) as counted from metas WHERE (metakey, metaval) IN ((3, 5), (4, 6)) group by tour_id having counted = 2

But now I am very confused about how to count different meta keys.

Hypothetically, let's say we have the following resultset:

|--------|-------------|
|tour_id |  countries  |
|--------|-------------|
|   1    | 15, 18, 23  |
|--------|-------------|
|   2    | 19, 23, 25  |
|--------|-------------|

How can I get the following resultset?

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|

The countries column should store country IDs like: 10,12,14.

This is a poor design, which you've already realised, (through your dislike of "LIKE").

Normalise your data and split these repeated values out into separate tables.

select * from tours ;
+------+
| id   | ...
+------+
| 1234 | ...
+------+

select * from countries ;
+----+--------+
| id | name   |
+----+--------+
| 10 | Brazil |
| 12 | Peru   |
| 14 | Chile  |
+----+--------+

select * from tour_countries ;
+---------+------------+
| tour_id | country_id |
+---------+------------+
|    1234 |         10 |
|    1234 |         12 |
|    1234 |         14 |
+---------+------------+

select * from themes ;
+-----+-----------+
| id  | name      |
+-----+-----------+
| 111 | Discovery |
+-----+-----------+

select * from tour_themes ;
+---------+----------+
| tour_id | theme_id |
+---------+----------+
|    1234 |      111 |
+---------+----------+

Finding data?

  SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

select t.*
from       tours          t
inner join tour_countries tc  on  t.id = tc.tour_id
inner join countries      c   on  tc.country_id = c.id
inner join tour_themes    tt  on  t.id = tt.tour_id
inner join themes         t   on  tt.theme_id = t.id
where c.name in ( 'Brazil' )
and   t.name in ( 'Discovery' )
order by t.id ;

Regards,   Phill  W.


migrating from SQL server 2005 to 2008 I had SQL server 2005 installed on my windows server 2008 R2.

Recently i installed SQL server 2008  and SQL server management studio 2008 r2 (SSMS) on my virtual server and i configured the instance with SSMS.

But SSMS is using SQL server 2005 instead of 2008 and i am facing some problems with datatypes like Date.

Please not both SQL server 2005 and 2008 are express editions.

can anyone please suggest how to make use 2008 edition.

Thanks in advace.

Even if you are using the newest version of SSMS, and SQL server 2008, that does not automaically upgrade any databases that you have attached to it. I can see SQL Server 2000 databases in SSMS for SQL Server 2008. Any database that is not upgraded cannot use SQL Server code from the newer version. If you are in compatibility mode, you may not be able to use all functionality of the newer version. I would suggest you connect to the database you want to use new features in and see what version the database is:

Select @@version

Note - do not upgrade the database to SQL Server 2008 unless the production databse has been upgraded or you will write code that can't be moved to production. Upgrading a database to a new version is delicate and time-consuming process requiring much testing. Many companies take years to upgrade to new versions as a result.


Will the indexing affect insert/update/delete operation in Mysql Will the indexing affect insert/update/delete operation in Mysql 5.1
Will it take down performance of insert update delete operations,
If yes, why it will happen so.

So are indexes always meant for static table only?

Of course.

The choice of primary key can greatly influence all select/update and insert operations. It's because your data will be PHYSICALLY placed on the disk by the order of the primary key.

Modern hard drives provide great transfer but only if you read sequentially. When it comes to reading random spots on the drive, your HDD  can only access 75-150 different areas per second. So when it has to "jump" around when reading/writing data that can't be written/read sequentially ( due to bad primary key chosen ) - it will significantly affect performance.

Additionally, when inserting, mysql server has to recalculate indexes and when you overindex your table, this can kill your performance.

Choosing your indexes - and particularly your primary key - is one of the most important choices when designing your tables. Most performance problems can be fixed by addressing indexes.


how to approach user acceptance testing for merged constituent records in a CRM system? I've been Googling to read up and understand what UATs are and how to conduct them, but I'm not quite understanding how to apply it in this scenario.

My acceptance criteria would be that all historic data from duplicate profiles that are transactions, interactions, and custom fields like booleans and strings get merged into the master record.

I'm confused about the "testing" part of UAT...what is there to test? don't we just look at the profiles and confirm that the data is there? Does that count as testing?

I'm struggling to build a test plan because I don't really see what there is to test.

Any help/suggestions/readings/articles is immensely appreciated.

Background:
We have about 3 million records (record being the constituent profile in the CRM), and we have identified at least a quarter of the records are duplicates (common cases being a person has 5+ profiles).

We've gone through the activities of building our criteria and identifying which of these duplicates would be marked as the master record, the duplicate records would then be merged into the master record.

Our next step is to validate the master records to ensure the historic profile data from duplicate records remains intact (transactions, interactions, and any custom fields like booleans and strings that were added on as part of their registrations, merged into the master record).

Task:
I am to build a UAT plan for multiple teams to validate the merge (I did express that my background is not related to what is being asked...but I'll try, because no one else in the company has any idea what to do at this point, budget is really really tight so cannot hire contractors)

With 3 million records, and 25% duplicates, you've got 750,000 constituent records that need to be merged & verified. That's a lot of manual work to merge & verify all 750,000 constituents individually.

Presumably there is some automation that you are going to use to identify & merge all of these constituent records. For your User Acceptance Testing, you don't need to verify every merge, you just need to verify a sampling of different scenarios. Your task is to devise a list of test cases that represent those different scenarios. In short, you are trying to do comprehensive (not random) spot-checking

Usually, you can devise the scenarios based on the same business rules that were used to create the automation.

For example:

Ensure that for a given merge set, a single constituent record is active, and the duplicates are deactivated/deleted.
You merge two constituents, each with different home addresses. Both constituents have that address set to preferred/primary. Should the merged record have both addresses? Which address should be the preferred/primary address?
You merge two constituents with the same last name, and same address, but different first names. How should these names be combined? Are they treated as a single person (perhaps a nickname & formal name), or as a couple/spouses/household?
You have two constituents with different first & last names, but the same address. Should they be treated as a couple/household & merged? Should they remain separate, but both active? Should you assume that one moved out & another moved in at the same address, and simply invalidate the address on the older of the two records?
etc etc... for any scenarios you care about.

As part of your test cases, you would need to provide answers for the questions I pose above, in order to tell testers what they are looking for. Essentially, you provide a scenario, and desired result. Testers need to validate that the actual result matches with the desired result. If it matches, the test case passes. If it doesn't match, then the test case fails.

In some cases you might want to combine scenarios, to sort out possible conflicts in your criteria.

Given that you have an existing database to work with, you may want to identify specific records to use as part of UAT. Your test case could identify the specific records, the pre-merge data, and the expected post-merge results. Testers would then look up those records and verify the results match what is expected.1

Once you've devised your comprehensive list of scenarios & desired results, you'll have a comprehensive test plan to use for your User Acceptance Testing!

1In software development, even the testing is automated, rather than doing this check manually--but for a one-time project that level of automation is probably not necessary--it's more work than just doing it manually.)


As an application what are differences between sqlplus.exe and sqlplusw.exe? Currently i am using Oracle 10g and i can use both sqlplus and sqlplusw. But is there any difference in using them. And, is any of the is deprecated in newer versions?

sqlplusw was a GUI version of the command line tool sqlplus, with its own Edit menu etc.

sqlplusw was removed in Oracle 11g.


Select with a preffered where condition, but if missing use another I have the following select in SAP HANA DB:

SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang IN ( 'S', 'E' )
  INTO TABLE @DATA(lt_texts).

It will select multiple lines for a given name if translations exist for different languages.

How do I say that I want texts with language = 'S', but if it doesn't exist, then select ones with lang = 'E'. I want it in a single request to the DB and no processing on application level.

Thank you in advance

One way of accomplishing this might be a self-join (assuming (name, lang) uniquely identifies rows):

SELECT e.name, coalesce(s.text, e.text) AS text, coalesce(s.lang, e.lang) AS lang
FROM (
  SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang = 'E') AS e
LEFT JOIN (
  SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang = 'S') AS s
ON e.name = s.name


Big Data vs Relational Database on making structure of Data-ware house for telecom data analysis I'm currently doing my final year project on telecommunication mining. On starting project I got confuse on choosing database. As data on mobile communication is going on increasing so I think Big data is useful on reducing time complexity of the project. But I stuck my head on finding better option. I had no experience on Big Data except its general concept but I'm quit familiar with relational database (oracle).

So my question is:- Which data will be best option on doing such project and why? So I appreciate any suggestion regarding the database selection and design.

  Project Background:-
  We have 100's of thousand of data of call details of customer along with their personal information and we have to perform various mining technique for pattern analysis.

Rule of Thumb: Does your workload fit with "traditional" approaches? Like oracle? Oracle can handle "00's of thousand of data of call" so does other databases like MySQL and SQL Server. You start looking into "Big Data Tools" when these solutions start to break. It's a pretty old video but it has some good insights: http://www.youtube.com/watch?feature=player_embedded&v=5keh0UU0mNo

Also I like the answer by Jeff Hammerbacher asnwer to similar question. Start at 27:01 http://www.youtube.com/watch?v=SVxc9lhMA0M

You could also consider reading the book "Planning for Big data" (free)
http://parasdoshi.com/2012/05/18/want-to-learn-about-bigdata-read-oreillys-book-planning-for-bigdata/


Postgres - 2 database mirror I have 1 DB called - DB_A. I would like to create read only database DB_B from DB_A which updates itself every 10 mins. Is their any easy way to do it in postgres? I am using Postgres 11. Also any data deleted from DB_A should get removed from DB_B on next update.

It sounds like you want the recovery_min_apply_delay option of streaming replication.

This will give you a rolling 10 minute window, not a leaping window.


SSIS Package Duration is big before it begins executing hello i have this question.
From Visual Studio i have deployd package in Sql server and i am manualy executing Package.
BUt i have noticed that package need 6 sec to do all things  but Duration is 8 sec Like on pictures so can you tell me what is  this 2 sec?

I think it's hard to predict where those 2 extra seconds are coming from without seeing your SSIS Package and how you're running it. A rough guess is it's some additional runtime that added up from starting the package execution + starting execution of each step. I wouldn't worry much about it unless you start seeing huge discrepancies (but if it's consistently off by a few seconds, then I would lean to what I previously guessed).
According to this StackOverflow, those reports aren't the most accurate way to track and analyze execution times too: SSIS job taking 'hours' -- All Executions report not adding up or showing errors -- where to find cause?
Specifically see Nick.McDermaid's comments:

Everything you need should be in the logs / reports. No need to run it locally. It's a
pain, introduces network traffic, is not a true representation and
doesn't give you much more information. There is a package 'total run
time' in the top level report (displayed in seconds).

Here are some alternative ways you can track SSIS Package execution times: Tracking Execution Times for SSIS Packages


Selecting/Matching cells that hold the same values as opposed to ID in MySQL/phpMyAdmin Can someone point me in the right direction?

Given tables:

actor_uri_address

----------------------
| ID | URI | Address |
----------------------
|    |     |         |
----------------------

actor_full_name_opc

-------------------------------------------------
| ID | Given Name | Surname | Outward Postcode  |
-------------------------------------------------
|    |            |         |                   |
-------------------------------------------------

I am trying produce a table that contains actor URI, first names, surnames and addresses of actors living at the same addresses on the list (I do not need to supply the address at query time, rather the duplicate values in the address column) on phpMyAdmin.

Below is my query construct so far. Despite all efforts, I have only been able to get it to run and return the entire URIs, first names, surnames and addresses. I am totally stuck on how to have it narrow down to only the actors living at the same address.

SELECT   `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`
FROM     `actor_uri_address`
    LEFT JOIN `actor_full_name_opc` ON `actor_full_name_opc`.`ID` = `actor_uri_address`.`ID`
GROUP BY `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`

SELECT  GROUP_CONCAT(CONCAT(Given_name, ' ', Surname)),
        Address
    FROM actor_uri_address
    JOIN actor_full_name_opc USING(ID)
    GROUP BY Address
    HAVING COUNT(*) > 1


MySQL Workbench table organisation Well, I'm interested is there a way to (someway)automatically organize database tables when you make EER Model from existing database.

In my schema I have 30(and something) tables and 50(and something) relationships and when I do thing noted above all my tables are placed on one spot. So, you could imagine how would it look with bigger schema. This is how it looks in my situation:

Also, I know that there is Arrange on the menu bar but it doesn't give much option.

I'm using 5.2.44 CE version of MySQL Workbench

Go to Model -> Diagram Properties and Size and increase the number of pages for the model
Select Arrange -> AutoLayout which evenly spreads out the tables in the model.

You can repeat the above as you add more pages to get the best fit


bind-address how does it work in mysql I have a mysql running with bind-address "10.4.0.211".

From the documentation, I understand it like, the server accepts connections only from the IP - 10.4.0.211.

But I could able to connect to this mysql from another new server with the below configurations.

eth0      inet addr:10.8.0.7
eth1      inet addr:10.11.0.50
lo        inet addr:127.0.0.1

None of the above  has the bind address in them, but how come I am able to connect to the service. Please clarify.

I am suing the below connections string from the new server.

mysql -u<<username>> -p<<password>>  -h<<hostname of the mysql running server>> -A

From the documentation, I understand it like, the server accepts connections only from the IP - 10.4.0.211.

Your understanding is incorrect.

It has nothing to do with where the connection is coming from.

The server listens and accepts connections only on (not "from") the stated address.  Where you can connect from is determined by the entries in the `mysql`.`user` table, which are created by GRANT statements, and, of course, whether you have network connectivity to the address where MySQL is listening.