Anemometer - query on analysing the slow logs I'm new to SQL anemometer. I need to know whether this tool would be able to query to the source database and analyze the slow query logs on real time or not?

From the documentation:

Anemometer is a tool for visualizing collected data from the MySQL Slow Query Log. The name comes from the instrument in a weather station that measures wind speed. SQL queries are like the wind -- ephemeral and hard to get a hold of. Anemometer helps you get a handle on your queries; it makes it easier to figure out what to optimize and how to track performance over time.
Anemometer relies on the Percona Toolkit to do the slow query log collection. Specifically you can run pt-query-digest. To parse your slow logs and insert them into a database for reporting.

From the documentation of pt-query-digest

Usage
pt-query-digest [OPTIONS] [FILES] [DSN]
pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from SHOW PROCESSLIST and MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no FILES are given, the tool reads STDIN. The optional DSN is used for certain options like --since and --until.
[...]
Capture MySQL protocol data with tcppdump, then report the slowest queries:
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

tcpdump monitors real-time data... that is, in principle, the answer is yes, although not by looking at the slow log. However the actual setup doesn't look easy, and requires running from the server machine (as tcpdump works locally), not by remotely querying the database.


SSRS access for AD-trust *groups* not working I have the following setup:
AD domain "developer.local" has a two way external non-transitive trust with AD domain "customer.local".  There is a 2014 SQL Report server in the developer.local domain.

If I assign SSRS permissions to a group from customer.local, group members receive a "does not have required permissions" when logging in the the SSRS WebUI.  If I assign permissions to individual users then it seems to work.

Is this not a supported scenario, or what should I check to get this resolved?

I don't have enough reputation to post comments because I'm new here, but this is resolved for me.

I think the root problem is groups are scoped as "domain local" in the foreign AD but that's probably off topic for here.

I created a new group in the developer.local domain and added users from the customer.local domain as members.  This allows the users to access SSRS as expected.


SQL Server 2012 Local SSD TempDB Slow I have a production deployment using local SSDs for tempDB. I have 2 SSDs in a RAID1 configuration. I am seeing average reads of 1-2ms but the average writes are showing as 1377ms on all four of my tempdb data files.

Each tempdb data file is 2GB with a 1GB growth setting (They haven't grown since deployment 5 months ago)

The tempdb log is showing average read 67ms and average write 215ms.

The SSDs are Samsung 840 pros.

The following code is what I use to get my stats

SELECT a.database_Id,
a.file_id,
db_name(a.database_id) AS dbname,
b.name,
db_file_type =  CASE
                WHEN a.file_id = 2 THEN 'Log'
                ELSE 'Data'
                END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location,
a.io_stall,
a.io_stall_read_ms / Case When a.num_of_reads = 0 Then 1 Else a.num_of_reads end AvgRead,
a.io_stall_write_ms / Case When a.num_of_writes = 0 Then 1 Else a.num_of_writes end AvgWrite,
Cast(Round(((( a.size_on_disk_bytes / 1024 ) / 1024.0 ) / 1024), 2) as float) AS size_on_disk_gb

FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id AND a.database_id = b.database_id

ORDER BY a.io_stall DESC

Below are the top 5 rows returned

database_Id file_id dbname  name    db_file_type    disk_location   io_stall    AvgRead AvgWrite    size_on_disk_gb
2   1   tempdb  tempdev Data    F:  19782846713 2   1377    2
2   3   tempdb  tempdev2    Data    F:  19782655021 2   1377    2
2   5   tempdb  tempdev4    Data    F:  19782364070 2   1377    2
2   4   tempdb  tempdev3    Data    F:  19782151571 2   1377    2
2   2   tempdb  templog Log F:  378829065   67  215 1

So my tmepdb files on SSDs are the slowest drives I have. Anything I should be looking at from a configuration/infrastructure point of view? I am currently studying the applications usage of tempdb and any memory spills but I'm not seeing anything terrible.

We cracked this a while back by replacing the RAID controller on the server. The disks and server configuration were fine but it appears that the RAID controller couldn't deal with the IO.

We are now in the good place of reads ~ 2ms and Writes at <= 5ms


What am I doing wrong here? I am trying to fetch User's Role Name by traversing through multiple tables in MySql. If I pull User.Id everything works but right after I add one more level for UserRole.Name it throws an error.

Error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON Opportunity.AccountId = Account.Id' at line 2

DB Tables

Opportunity has AccountId as column.
Account has UserId  as column.
User has UserRoleId as column.
UserRole has Name as column.

Query

SELECT UserRole.Name FROM Opportunity
LEFT JOIN Account JOIN User JOIN UserRole ON User.UserRoleId = UserRole.Id ON Account.UserId = User.Id ON Opportunity.AccountId = Account.Id LIMIT 1\G

The syntax is valid SQL. I'm formatting a bit differently and adding parentheses to show how it is / should be parsed:

SELECT UserRole.Name
FROM
    Opportunity
  LEFT JOIN
    (   Account
      JOIN
        (   User
          JOIN
            UserRole
          ON
            User.UserRoleId = UserRole.Id
        )
      ON
        Account.UserId = User.Id
    )
  ON
    Opportunity.AccountId = Account.Id

It seems that the version of MySQL you use does not parse this correctly when the internal joins are INNER ones. If they were all LEFT joins, it wouldn't have a problem.

There are 2 solutions:

A) Use the above, with the parentheses.

B) Rewrite, changing the order of the joins, so the they not nested. You'll have to replace the LEFT to a RIGHT join but you don't need parentheses:

SELECT UserRole.Name
FROM
            User
          JOIN
            UserRole
          ON
            User.UserRoleId = UserRole.Id
      JOIN
        Account
      ON
        Account.UserId = User.Id
  RIGHT JOIN
    Opportunity
  ON
    Opportunity.AccountId = Account.Id


Where to install Oracle client software on SharePoint 2010 Server We have our ASP.net web application hosted on a SharePoint Server 2010. Our application uses oracle database to save and retrieve data.For our application to work on SharePoint server we need to install Oracle client software.

Our SharePoint Server architecture consists as following

2 web front ends
Index Server
Crawl Server
Application server
Load Balancer.

I am not the admin on the SharePoint server and trying to figure out if i have to install Oracle client on server where do i install it? Do i need to install it on all our servers or just the web fronends. Can some one please help me with this?

I do not know much about Sharepoint but in usual cases Oracle client has to be installed into server where application which connects to Oracle is located. So if your application will be running on Application server you should install Oracle client here.
Just for the information you can also try Instant Client. It uses less disk space and you do not have to install it. Just unpack where you prefer and point TNS_ADMIN environment variable to the directory where you unpacked it.
You can find variables by right clicking Computer icon then select Properties, Advanced System Settings, Environment Variables. And then add TNS_ADMIN to system variables.


MySQL replication - independent data update on slave I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.

I have a database that is hosted on one server, A, where new records are created, and these are accessed and modified on the second server B (but no new records are created).

I have set up A as master, and B as slave to ensure that B has access to new records created by A, but A does not need access to the amended record data that are changed on B.

I know there are lots of warnings about not changing data on the slave, but it seems to me that this should work OK (until I finish the migration, at which point I will move the record creation facilities to server B and break the slave link).

Any thoughts?

On this scenario replication should not face any issues.

MASTER (A): INSERT

SLAVE (B): UPDATE,DELETE

The only issue i can think is to change a unique constraint on B and try to insert the same record on A.


Can we disable binary logging for one specific session or user in AWS RDS Currently we have a situation to disable binary logging for one set of queries on AWS RDS. We thought to set log-bin=0 for that specific session. As per Amazon, this is not possible. Is there any work around for us ?

We want binary logging to be working for the remaining activity that happens in parallel
to this session.
If needed, we can stop and restart slave.

Thanks in advance.

One solution proposed in https://forums.aws.amazon.com/thread.jspa?threadID=45258 requires setting backup retention period to zero. Which should mean no automatic backup! But you can still take manual snapshots.


sp_add_jobstep add more than 4000 Character in @command parameter sp_add_jobstep is well documented here.  I understand parameter @command is type NVARCHAR(MAX).

I am creating a SQL Agent Job using a Stored Procedure.  Passing the value for @command parameter via a variable.  I am getting truncated at 4000 character.

My variable is also declared as NVARCHAR(MAX)

Any workaround to add more than 4000 characters in @command parameter?

'This' is a varchar string.

N'This' is an nvarchar string.

I'll guess that, after @tblUpdateStats_List, the remainder of your string was more than 4000 characters.

I did some experiments (in SQL 2016), and it looks like when a varchar string is implicitly converted to nvarchar, it is converted to nvarchar(4000), not nvarchar(MAX).

I ran the following query:

DECLARE @myNVar NVARCHAR(MAX);
SET @myNVar = 'ABCDEFG';

SET @myNVar = 'QQQ' + REPLICATE(@myNVar, 1000)
             +<literal>
             +'QQQ';

SELECT LEN(@myNVar), SUBSTRING(@myNVar, 6990, 25), RIGHT(@myNVar, 20);

With <literal> initially being an nvarchar string constructed as follows:

The character 'Z', repeated 3 times
the character 'z', repeated 1027 times
the character 'X', repeated 3 times
EOL
The resulting string copied, and pasted in 3 more times (wit the fourth EOL removed)

This string was 4,138 characters long (on Windows, where EOL is CR+LF), so the total string length should have been 3 + 7000 + 4138 + 3, or 11,144 characters

When run with the literal as an nvarchar string, got the following results:

11144     ABCDEFGABCDEFGZZZzzzzzzzz     zzzzzzzzzzzzzzXXXQQQ

So, correct length, and expected values at end of replicated string, at start of literal, and at end of literal.

Then, I changed the literal to a varchar string, and got this:

11006     ABCDEFGABCDEFGZZZzzzzzzzz     zzzzzzzzzzzzzzzzzQQQ

So, length shows 3 + 7000 + 4000 + 3 = 11006 - what we'd expect if the varchar value was truncated down to just 4,000 characters. And, we don't see XXX before the final QQQ, also as we'd expect under these circumstances.

FYI - If I CAST the varchar literal to nvarchar(MAX), my results went back to the first ones, so an explicit cast avoids this problem.


How to Sum Each Enum Value in a Different Column and Don't Repeat Person's Name? Let's say I have a table with SellerId, DateTime, ProductType to register each time a a Seller sells a product. The ProductType is from ENUM type with { 0=Phone, 1=Recharge, 2=SimCard }. Then I have a table with SellerId and SellerName.

I want to write a stored procedure that shows this result:

  ID | NAME | Phones | Recharges | SimCards | Total
 ----|------|--------|-----------|----------|-------
  05 | Eddy |      6 |         7 |        0 |    13

I know how to join the two tables to get the ID and NAME in this result set, but I do not know how to SUM each ProductType into a different column. And I also wanted only one row for each ID/NAME.

Can anyone show me how to achieve this in this simple example?

This is pretty simple to code using standard SQL if that is what you prefer.

CREATE TABLE dbo.seller
(SellerID INT,
 SellerName NVARCHAR(100));

CREATE TABLE dbo.productsold
(SellerID INT,
 SaleDate DATE,
 ProductEnum TINYINT);

-- Sample sellers
INSERT INTO dbo.seller VALUES (1, 'Frank');
INSERT INTO dbo.seller VALUES (2, 'Sally');
-- Sample transactions
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 1);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 1);
INSERT INTO dbo.productsold VALUES (1, GETDATE(), 0);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 2);
INSERT INTO dbo.productsold VALUES (2, GETDATE(), 0);

-- Query to pivot and sum
SELECT s.SellerID, s.SellerName,
  SUM (CASE WHEN p.ProductEnum = 0 THEN 1 ELSE 0 END) AS Phones,
  SUM (CASE WHEN p.ProductEnum = 1 THEN 1 ELSE 0 END) AS Recharges,
  SUM (CASE WHEN p.ProductEnum = 2 THEN 1 ELSE 0 END) AS SimCards,
  COUNT (p.ProductEnum) AS Total
FROM dbo.seller AS s
   JOIN dbo.productsold AS p ON s.SellerID = p.SellerID
GROUP BY s.SellerID, s.SellerName
ORDER BY s.SellerID;

-- Drop test tables
DROP TABLE dbo.seller;
DROP TABLE dbo.productsold;

RESULTS:

SellerID  SellerName    Phones  Recharges   SimCards  Total
1         Frank        2        2           1         5
2         Sally        2        0           2         4


Script fails to run as SQL Server Agent Job, completes successfully when run from SSMS I have two T-SQL scripts that are scheduled to run once every day. Both do cleanup of some old data from different tables. Both scripts are run as the same user, on the same database.

Script A will execute successfully both in SQL Server Management Studio and when launched as a job, but Script B will only execute successfully in SQL Server Management Studio, even when logged in as the same user as is set up to run the script under SQL Server Agent.

Script B fails with the following error:

  Executed as user [username].
  DELETE failed because the following SET options have incorrect settings:   'QUOTED_IDENTIFIER'.

  Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

  [SQLSTATE 42000] (Error 1934). The step failed.

I do have a filtered index on one of the tables I'm trying to clean, but why does it then work through SSMS?

The default for QUOTED_IDENTIFIERin SSMS is ON while it is OFF for SQLCMD, OSQL, BCP and in your case especially SQL Server Agent.

For reference, these are the different default options per application

+-------------------------+------------+------+-------------------+------------+
|                         |  ADO .Net, | SSMS | SQLCMD,OSQL, BCP  | ISQL       |
|                         |  ODBC      |      | SQL Server Agent  | DB-Library |
|                         |  OLE DB    |      |                   |            |
+-------------------------+------------+------+-------------------+------------+
| ANSI_NULL_DFLT_ON       | ON         | ON   | ON                | OFF        |
| ANSI_NULLS              | ON         | ON   | ON                | OFF        |
| ANSI_PADDING            | ON         | ON   | ON                | OFF        |
| ANSI_WARNINGS           | ON         | ON   | ON                | OFF        |
| CONCAT_NULL_YIELDS_NULL | ON         | ON   | ON                | OFF        |
| QUOTED_IDENTIFIER       | ON         | ON   | OFF               | OFF        |
| ARITHABORT              | OFF        | ON   | OFF               | OFF        |
+-------------------------+------------+------+-------------------+------------+

The above table is copied from  Erland Sommarskog

So you would need to add SET QUOTED_IDENTIFIER ON as the first line in your job step.


Insert data through linked server into two remote related tables I want insert data through linked server into two remote related tables as below:

(SQL 2008R2 on both machines)

SERVER_AAA:

create table #server_AAA_table_1
(
        [var_1] [int] NULL,
        [var_2] [bigint] NULL,
        [var_3] [varchar](64) NULL
)

create table #server_AAA_table_2
(
        [var_4] [char](1)NULL,
        [var_5] [int] NULL,
        [var_6] [int] NULL,
        [var_7] [int] NULL,
        [id_1] [int]  NULL
)

SERVER_BBB:

create table server_BBB_table_1
(
        [id_B_1] [int] IDENTITY(1,1) NOT NULL,
        [var_1] [int] NULL,
        [var_2] [bigint] NULL,
        [var_3] [varchar](64) NULL,
constraint [PK_server_BBB_table_1] primary key CLUSTERED ([id_B_1] ASC)
)

create table server_BBB_table_2
(
        [id_B_2] [int] IDENTITY(1,1) NOT NULL,
        [var_4] [char](1)NULL,
        [var_5] [int] NULL,
        [var_6] [int] NULL,
        [var_7] [int] NULL,
        [id_B_1] [int] NULL,
constraint [PK_server_BBB_table_2] primary key CLUSTERED ([id_B_2] ASC)
)

alter table server_BBB_table_2 with check add  constraint [FK_server_BBB_table_2_server_BBB_table_1] foreign key([id_B_1])
references server_BBB_table_1([id_B_1])
on update cascade
on delete cascade
go

SERVER_AAA:

Linked server: LNK_AAA_2_BBB

declare @id_1 int

insert into LNK_AAA_2_BBB.server_BBB_DB.server_BBB_table_1 select * from #server_AAA_table_1

set @id_1 = scope_identity();

/*
 some sql code: @id_1 + #server_AAA_table_2
*/

insert into LNK_AAA_2_BBB.server_BBB_DB.server_BBB_table_2 select * from #server_AAA_table_2

Unfortunately, in any way I can't get scope_identity() value from first insert, I always get NULL.

I tried solution with OUTPUT and procedure, no success.

Is this possible to do via linked server?
Maybe I need some other solution?

You have to use sp_executesql or openquery ..

E.g. pseudo code ..

declare @remoteIdentity (ID int)
insert into @remoteIdentity
EXEC LNK_AAA_2_BBB.master..sp_executesql N 'select * from #server_AAA_table_1
select SCOPE_IDENTITY() AS ID';
SELECT * FROM @remoteIdentity;

Read up : Best way to get identity of inserted row?


What am I doing wrong in mysql database? I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message:

  MYSQL ERROR: Can't update table 'player_viplist' in stored
  function/trigger because it is already used by statement which invoked
  this stored function/trigge

What do I need to do for this trigger to work?

DROP TRIGGER dele;

DELIMITER |

CREATE DEFINER = 'root'@'localhost' TRIGGER dele
  AFTER INSERT
  ON player_viplist
  FOR EACH ROW
BEGIN DELETE FROM player_viplist ;

END|

DELIMITER ;

If you are having a trigger on a table, You can not operate the same table which is used in the trigger.

As you mentioned you want to delete records from the table after inserting the records. Instead, why can't you skip inserting records to the table.

Can you mention your exact requirement in brief.


I broke replication. Could this have been fixed? I ran mysql_upgrade on both my master and slave after upgrading to a later version of mysql. Somewhere during this process replication broke for the mysql table. I was able to fix it manually, but after resetting slave I was greeted with more issues causing the SQL thread to abort. The following includes the first example right after I've fixed the mysql table and executed reset slave.

141006  8:53:08 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.020292' at position 54986, relay log '/var/log/mysql/relay.010967' position: 38174
141006  8:53:08 [ERROR] Slave SQL: Error 'Duplicate column name 'character_set_client'' on query. Default database: 'mysql'. Query: 'ALTER TABLE proc ADD character_set_client
                      char(32) collate utf8_bin DEFAULT NULL
                      AFTER comment', Error_code: 1060
141006  8:53:08 [Warning] Slave: Duplicate column name 'character_set_client' Error_code: 1060
141006  8:53:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.020292' position 54986
141006  8:53:08 [Note] Slave I/O thread: connected to master 'replication@mysql07:3306',replication started in log 'mysql-bin.020293' at position 2146
141006  8:54:03 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141006  8:54:03 [Note] Slave I/O thread killed while reading event
141006  8:54:03 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.020293', position 2146
141006  8:56:45 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/log/mysql/relay.000001' position: 4
141006  8:56:45 [Note] Slave I/O thread: connected to master 'replication@mysql07:3306',replication started in log 'FIRST' at position 4
141006  8:56:45 [ERROR] Slave SQL: Error 'Duplicate entry '323812407' for key 'PRIMARY'' on query. Default database: 'im_production'. Query: 'INSERT INTO messages
                         (timestamp, seq, conversation_id, sender_id, body)
                         VALUES (1410872583925, 194935,
                           (SELECT id
                           FROM conversations
                           WHERE owner_id=123456
                           AND with_user_id=1245678), 3069096, 'lol')', Error_code: 1062
141006  8:56:45 [Warning] Slave: Duplicate entry '323812407' for key 'PRIMARY' Error_code: 1062
141006  8:56:45 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.019466' position 1568
141006  8:59:23 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.019500', position 72441800

At this point I panicked and immediately brought production down and began transferring tablespace files from master to slave.

After looking at these logs and doing some more research, I'm starting to think that was very silly of me. I'm seeing that after I fixed the original replication issue, I executed reset slave. That seems to have reset the slave's log position back. It went from using mysql-bin 2020292 at position 54986 to trying to start the I/O thread at mysql-bin.019500 at position 72441800. It now seems like the correct thing to do would have been to reset master to the last known good position before replication broke again, instead of panicking and recovering all of our tablespace data.

Thoughts? Was as I as silly as I'm thinking I am now?

Because you ran the mysql_upgrade on both master and slave, the ALTER TABLE was happening twice on slave side. The changes on master was propagated to slave, trying to add columns which you already added with manual mysql_upgrade.

I don't think reset slave has anything to with the problem, unless you were looking to skip the entire block of statements done by mysql_upgrade, which IMO is very dangerous.


Overriding default datetime output format By default, my Postgres database outputs a DateTime in the current format YYYY-MM-DD hh:mm:ss. However, I want to override this and output it in this format YYYY-MM-DDThh:mm:ss with the T in between the date and the time.
Is there a way to override this with any format? I don't want to use to_char or anything similar, I want any DateTime returned by my database to be in that format (or any format I specify) by default.
Thanks

There is no way to do that in PostgreSQL.
The only configuration parameter available is DateStyle, which allows you to choose between a handful of pre-defined output formats.


Postgres 9.4 wal_level=logical increases disk space I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance.

Database Migration Service requires the wal_level to be set as logical in the source Postgres DB. But when done, Postgres started consuming a lot of disk space to the point where the instance became unusable.

How long are the WAL logs retained? Are there other settings that can be enabled so disk space use is limited?

This is an AWS DMS issue.

DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL logs which may result in storage full situations on the source. This heartbeat keeps restart_lsn moving and prevents storage full scenarios.

Login to DMS Console.
Stop all the tasks related to the source endpoint
Click on "Endpoints" from left side selection pane.
Select the source Endpoint which you are using, as per the task.
Click on "Modify" from the top of the screen.
Expand "Advanced".
Under extra connection attribute, please add this:

heartbeatenable=Y;heartbeatFrequency=1

HeartbeatEnable – set to true (default is false)
HeartbeatSchema – schema for heartbeat artifacts (default is public)
HeartbeatFrequency – heartbeat frequency in minutes (default is 5 and the minimum value is 1)

Click on "Modify". You will be automatically redirected to the Endpoints screen.
Select the endpoint again and click on "Test Connection".
It should automatically start the testing. Wait for it to show "Successful".

Stopping the task alone will not clear replication slot, so the storage usage will still increase when the task is in the stopped state, you will need to delete task to clear the slot.

To clear up the replication slots use the following commands -

SELECT * FROM pg_replication_slots ; --finds all replication slots
select pg_drop_replication_slot('NameOfSlot'); -- you will get the name of the slot from the first command

References:
[1] PostgreSQL Source WAL Heartbeat
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html#CHAP_ReleaseNotes.DMS230


Alternative query to this (avoid DISTINCT) Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines
I have this table "Users":

UserID  User    CountryID
1     user 1    1
2     user 2    2
3     user 3    3
4     user 4    4
5     user 5    4
6     user 6    3

And this table "Countries"

CountryID   Country
1           MX
2           USA
3           CAN
4           ENGLAND

As you can see, every user belongs to a country.

If I want to know, all the different countries where I have at least, one user on the Users table, right now I do this query:

select distinct country
from Users inner join
countries on users.CountryID=countries.CountryID

And achieve the next result set:

CAN
ENGLAND
MX
USA

Which is indeed, all the different countries, where I have at least one user on muy table Users.

My doubt is, is possible to achieve the above result set, with out using "DISTINCT", I mean only using JOINS and conditions ?

Here it's de DDL scripts:

USE [TEST]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
    [UserID] [int] NULL,
    [User] [nvarchar](50) NULL,
    [CountryID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (1, N'user 1', 1)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (2, N'user 2', 2)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (3, N'user 3', 3)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (4, N'user 4', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (5, N'user 5', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (6, N'user 6', 3)
/****** Object:  Table [dbo].[Countries]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
    [CountryID] [int] NULL,
    [Country] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (1, N'MX')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (2, N'USA')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (3, N'CAN')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (4, N'ENGLAND')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (5, N'BRAZIL')

You could get that same result set with the following:

select country from countries where exists
    (select countryid from users
        where users.countryid = country.countryid)

OR

select country from countries where countryid in
    (select countryid from users)

Neither of these use distinct but other than that I don't know how much "better" they are.


Simple table normalisation In raw form, I have data with  2 columns: skills in welsh and stats for that skill. For storing these information in a database, would it be best to combine the stats with the categories, or split them up as below?

WELSH_SKILLS_TABLE
----------------------------------------------------
|  SKILL_ID   |                SKILL
----------------------------------------------------
|      0      |            CAN'T SPEAK
----------------------------------------------------

WELSH_STATS_TABLE
----------------------------------------------------
|  SKILL_FK   |                COUNT
----------------------------------------------------
|      0      |                 235
----------------------------------------------------

Instinctively, the laid out method seems to be correct, but for such a simple task, also seems like overkill?

Thanks!

Without knowing anything else about your system, that seems like total overkill.  You'd be better off with a single table that has ID, Skill, and SkillCount.

Having two tables for such a simple schema will result in unnecessary work for both the DBA and developer.  The database engine will need to perform extra work to JOIN the two tables whenever you require reading both Skill and SkillCount at the same time.

If you had a really, really large dataset (think hundreds of millions or billions of rows) and you regularly needed to access just the Skill and rarely needed to show the SkillCount field, then perhaps you might consider using your design.


How to print the information of all tables using Oracle? I'm studying DDBB and we are using Oracle. Using too much tables can be really difficult for me because I have to remember table1.content2, table3.content1, etc.

So because of that, I would like to print (in paper) the header tables to make it easier.

I know my explanation is not very good so I tried to "draw it" below:

select * from user_tables;

↓

table_name|
-----------
table1    |
-----------
table2    |
-----------
table3    |

What I want:

table1
-----------------------------
content1| content2| content3|

table2
-----------------------------
content1| content2| content3|

table3
-----------------------------
content1| content2| content3|

How can I get this in paper?

Thanks!

If you're a student and can't install SQL Developer locally, maybe you could use SQuirreL SQL, a more lightweight (but still excellent) JDBC database client. I've not used the graphical stuff which I believe they have, but you can just simply navigate the tree like structures through the tables and easily get any info you may want, table structure, row count and data.


Does postgres have any configuration similar to mysql innodb_thread_concurrency? InnoDB provides an innodb_thread_concurrency option  to limit the number of concurrent storage engine operations MySQL can perform at a time. Seems like InnoDB controls (schedules) execution of its thread.

Does PostgreSQL provide any configuration that allow us to limit the number of concurrent operations performed at a time?
Does postgresql controls execution of its child processes or does it delegate this task to the OS?

PostgreSQL doesn't control execution of its child processes explicitly. If I borrow your words, PostgreSQL delegates that task to the OS.

PostgreSQL allows multiple clients to connect simultaneously. Each client connects a child process (referred to as a backend process), and each running backend  process handles all queries issued by the connected client. It is a very simple and classical server-client type multi-process architecture.

(I add more information.)

PostgreSQL can control the maximum number of connected clients by the configuration parameter max_connections (in the same way as MySQL),
but does not control the activities of the running backend processes depending on the server activity.

In addition, some helper processes, such as autovacuum related processes and checkpointer,  can be controlled their activities by themselves depending on the server activity.


MySQL: Is there a better way to do this query? I'm not sure if this is the most appropriate place for this question, but I thought I'd give it a shot.

The scenario is wanting to retrieve records from a table where a column is null, but if an exact match is found, preference that row instead.

Here's the table structure:

CREATE TABLE TABLE_A
(
  id INT,
  S_ID INT,
  C_ID INT
);

CREATE TABLE TABLE_B
(
  S_ID INT,
  C_ID INT
);

INSERT INTO TABLE_A VALUES(1, 10, NULL);
INSERT INTO TABLE_A VALUES(2, 10, 50);
INSERT INTO TABLE_A VALUES(3, 12, NULL);

INSERT INTO TABLE_B VALUES(10, 50);
INSERT INTO TABLE_B VALUES(10, 60);
INSERT INTO TABLE_B VALUES(12, 50);
INSERT INTO TABLE_B VALUES(12, 60);

When using a c_id of 50, I'd expect records 2 and 3 to come back. If I used c_id of 60, I'd expect records of 1 and 3 to come back.

At the moment, I have this query to solve the issue, but I'm positive there is a better way to do it, so I'm asking for ideas :)

SELECT ca.*
FROM TABLE_B AS clc
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = 50)
WHERE (
  ca.C_ID IS NULL AND ca.S_ID NOT IN (
     SELECT S_ID FROM TABLE_A WHERE C_ID = clc.C_ID
  )
) OR (
  ca.C_ID = clc.C_ID
);

Unfortunately, the tables have no indexes, and no foreign keys. The table structure cannot be changed (legacy).

if add indexes:

CREATE TABLE `table_b` (
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `table_a` (
  `id` int(11) DEFAULT NULL,
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and little modify the query:

set @v_npref = 50;

SELECT ca.id, ca.S_ID, ca.C_ID
FROM TABLE_B AS clc
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = @v_npref )
WHERE
ca.C_ID IS NOT NULL OR NOT EXISTS (SELECT id FROM TABLE_A ca2
                                 WHERE ca2.S_ID = ca.S_ID AND ca2.C_ID = @v_npref )

group by ca.S_ID;

we can compare plan of new and original queries,
original:

modified:

this is just my variant, what better 2 FULL scan (as You can see total number of scanned rows bigger) or file sort at the final possible decide only on real data (depend from size of sorted data)


How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days? Perhaps I'm overthinking this Oracle SQL query, but I'm attempting to display the number of days elapsed between two column data-sets attributed as "order-date" and "ship-date", which will be displayed within a new alias column, "Number of Days elapsed"- so far, I've constructed the following query:

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders;

Evidently, an alias column can't be sorted or filtered- so what options are available to filter the resulting output for the number of orders exceeding 30 days to ship?

Either restate the calculation in the predicate

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders
WHERE shipped_date - order_date > 30
ORDER BY (shipped_date - order_date) desc;

or nest the query

SELECT order_id, "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) AS "Number of Days elapsed"
          FROM orders)
 WHERE "Number of Days elapsed" > 30
 ORDER BY "Number of Days elapsed" desc

If you are going to nest the query, though, it probably makes sense to pick a case-insensitive alias in the inner query and to do the case-sensitive aliasing later on

SELECT order_id, days_elapsed "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) days_elapsed
          FROM orders)
 WHERE days_elapsed > 30
 ORDER BY days_elapsed  desc


Is it possible to have 2 backup plans for a single database? Is it possible to have two backup plans ?

allow sysadmins to have their own SQL Server 2008 set of backups for a database
allow DBAs to have their own separate backup plan

We have had issues with the sysadmin backups not working and would like to have a separate set for recovery.

You can have sysadmins doing COPY_ONLY backups and DBA's doing regular FULL, DIFF and/or Transaction log backups.

The key is COPY_ONLY backups for allowing ad-hoc backups. But they cannot be used for point in time recovery.

Note that taking a random Transactional log backup (without using COPY_ONLY) will break the log chain and you wont be able to do a point in time recovery.

I dont understand the reasons for taking separate backups, as your entire goal should be to be able to restore your backups and bring up your system (RTO and RPO) thereby meeting your SLAs.

Alternatively, if only full backups are taken by your sysadmins, then you can Restrict users to COPY ONLY backups.


Add additional disks to FRA diskgroup in ASM We're running Oracle 11.2.0.3 with a two node RAC and our FRA diskgroup is running low on space.  I need to allocate some more disks to it through ASM.
I'm not sure how to go about doing this.
I can login to a node and get to asmcmd or other asm command line utilities.  What do I need to do?

EDIT:  Didn't know it was relevant till Phil's answer: we're using raw disk devices.

This assumes that you've already partitioned the presented disk(s) (and will be using /dev/sd[whatever]N), and that you're using asmlib. There will be a kernel module loaded if you are:

[root@oel61 disks]# lsmod | grep oracle
oracleasm              53865  1
[root@oel61 disks]#

As root, scan for candidate disks:

[root@oel61 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@oel61 ~]#

Then, check to see if the disk has already been "discovered" by ASM:

oracle@oel61 ~]$ asmcmd -p
ASMCMD [+] > lsdsk
Path
/dev/oracleasm/disks/DISK1
/dev/oracleasm/disks/DISK2
/dev/oracleasm/disks/DISK3
ASMCMD [+] >

If not, we need to stamp the device:

[root@oel61 ~]# /etc/init.d/oracleasm createdisk NEWFRA /dev/sdc1
Marking disk "NEWFRA" as an ASM disk:                       [  OK  ]
[root@oel61 ~]#

Scan for candidate disks again, then list - the new device should be there:

[root@oel61 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@oel61 ~]#

# /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
NEWFRA
#

Or use asmcmd:

oracle@oel61 ~]$ asmcmd -p
ASMCMD [+] > lsdsk
Path
/dev/oracleasm/disks/DISK1
/dev/oracleasm/disks/DISK2
/dev/oracleasm/disks/DISK3
/dev/oracleasm/disks/NEWFRA
ASMCMD [+] >

Now do a scandisks on the other node and check that everything is as it is on the first node (should be fine if you're using the same /dev device names).

Now the disk is ready to be added to the group.

List the groups:

[oracle@oel61 ~]$ export ORACLE_SID="+ASM"
[oracle@oel61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 31 15:35:27 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select name
  2  from V$ASM_DISKGROUP;

NAME
------------------------------
DATA
FRA

SQL>

Add disk to the group:

SQL> ALTER DISKGROUP FRA ADD DISK '/dev/oracleasm/disks/NEWFRA';


Two rows into two columns I have this table:

Name        Value      Sequence
-------------------------------
test        A1         1
test        A2         3
test2       A20        5
test2       A10        8

I am after grouping the Name and get Value+ and Value- like this:

Name        Value+         Value-
-----------------------------------
test        A1             A2
test2       A20            A10

Rules are:

The Value from row with lower Sequence  goes to Value+
The Value from row with higher Sequence goes to Value-
There are only two rows per name

How can I do it?

Here's a solution with a crosstab:

DECLARE @SampleData TABLE (
    Name varchar(50),
    Value varchar(50),
    Sequence int
)

INSERT INTO @SampleData
VALUES
 ('test'        ,'A1',         1)
,('test'        ,'A2',         3)
,('test2'       ,'A20',        5)
,('test2'       ,'A10',        8);

WITH RankedData AS (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence)
    FROM @SampleData
)
SELECT Name,
    [Value+] = MAX(CASE WHEN RN = 1 THEN Value ELSE '' END),
    [Value-] = MAX(CASE WHEN RN = 2 THEN Value ELSE '' END)
FROM RankedData
GROUP BY Name


Combine many table into one table in mysql table design I'm grappling to design table relation. Version is Mysql 5.6.10.

My case is like below

bill table, payment table, contract table, recruitment tables... are existing.

Currently we're considering new function it will use bill or payment table. So I have designed new_function_table.

CREATE TABLE new_function_table`(
  id int unsigned not null auto_increment,
  bill_id int unsigned null default null,
  payment_id int unsigned null default null,
  primary key(id),
  index fk_new_function_table_bill_id_idx (bill_id asc),
  index fk_new_function_table_payment_id_idx (payment_id asc),
  constraint fk_new_function_table_bill_id foreign key (bill_id) references bill (id) on delete no action on update cascade,
  constraint fk_new_function_table_payment_id foreign key (payment_id) references payment (id) on delete no action on update cascade
)engine=innodb

Personally now is ok, but contract, recruitment, etc table be included into new_function_table in the future.

If new_function_table use 10 tables(bill table, payment table, contract table, recruitment tables...), then new_function_table has 10 index.
So i have designed like below It ignore data integrity.

CREATE TABLE new_function_table`(
  id int unsigned not null auto_increment,
  category tinyint unsigned not null comment '1: bill, 2: payment...',
  reference_table_id int unsigned not null comment 'bill_id or payment_id...';
)engine=innodb

What is the good design for this case? If you have an any idea please help me! Thank you.

I have designed with @Walter Mitty opinion.

CREATE TABLE new_function_table(
  id int unsigned not null auto_increment,
  category tinyint not null comment '1: bill, 2: payment...'
  primary key(id)
)engine=innodb

CREATE TABLE bill(
  id int unsigned not null auto_increment,
  new_function_table_id int unsigned default null,
  index fk_bill_new_function_table_id_idx (new_function_table_id),
  constraint fk_bill_new_function_table_id foreign key (new_function_table_id) references new_function_table (id) on delete no action on update cascade,
  primary key(id)
)engine=innodb

CREATE TABLE payment(
  id int unsigned not null auto_increment,
  new_function_table_id int unsigned default null,
  index fk_payment_new_function_table_id_idx (new_function_table_id),
  constraint fk_payment_new_function_table_id foreign key (new_function_table_id) references new_function_table (id) on delete no action on update cascade,
  primary key(id)
)engine=innodb
...etc

If it is wrong, then please feedback! Thank you.


Stuck pt-archiver query during purge I am purging a table of size 1.2T using pt-archiver. In the show processlist i can see the query stuck for 650 secs in Sending data state. The query is quick when I execute independely. Any help will be appreciated.
[shell]# pt-archiver  --source h=localhost,D=dsm,t=subscriber_event -u XX -pXXX    --where="created <= DATE_SUB(now(), interval 1 year)" --limit 10000  --purge --statistics --progress 10000 --max-lag=100 --check-interval=15 --check-slave-lag h=X.X.X.X,S=/data/mysqllog/mysql_logs/mysql.sock --no-check-charset --why-quit  --primary-key-only --retries=5
TIME                ELAPSED   COUNT
2021-01-16T00:34:27       0       0
DBD::mysql::st execute failed: Lost connection to MySQL server during query [for Statement "SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873782070') ORDER BY `id` LIMIT 10000"] at /bin/pt-archiver line 6631.

Explain of the query
mysql> explain SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873782070') ORDER BY `id` LIMIT 10000;
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref  | rows       | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
|  1 | SIMPLE      | subscriber_event | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 1450620612 |    33.33 | Using where |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

same query
mysql>SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873802696') ORDER BY `id` LIMIT 10000;
| 3205169561 |
| 3205169562 |
| 3205169563 |
| 3205169564 |
+------------+
10000 rows in set (0.10 sec)

Please help me finding out the exact cause.

A much better way to purge a table by date is by using DROP PARITION.  See http://mysql.rjweb.org/doc.php/partitionmaint  Unfortunately, it requires adding partitioning to the table and changing some indexes.  This will be a very long (one-time) task for 1.2TB.
Meanwhile...  If 10000 is tunable, lower the number to, say 1000.  I suspect that other activities on the table are conflicting with the archiving scan.  By decreasing that number, the will be less chance of conflict, at the expense of the archive taking longer.  (No, not 10 times as long.)
("Sending data" is useless information.  Alas, that is probably the only clue you can get.)
(id < '3873782070'):  Is id INT UNSIGNED?  If so, might it overflow soon?


How do you restore a particular table in MySQL? One of our users has deleted a table by mistake. How can I restore only that table?

I'm not familiar with MySQL, so don't have a straight line of directions. What I'd do is to restore the last backup (do you have one?) of the database on the same or a different server and copy the missing table table to the main on-line database.

PS: oh, and also remove the write permission for that user :-).


SQL Server sp_configure options? Does anybody know where I can find a list of all the parameter options for sp_configure?  It isn't really jumping out at me when I search online for possible variations and the capabilities of sp_configure.

Thanks!

At least three times on the sp_configure page, you have "Setting Server Configuration Options".

In the table for the row "config_value"
Under "Advanced Options"
At the bottom

This page also shows what happens if you run sp_configure with a blank @configname

  If not specified, the complete list of options is returned.

This applies from SQL Server 2000 to SQL Server 2008 pages


ODBC connection for testing Availability Group Listener I am having difficulty verifying my Availability Group Listener configuration.  I would like to create an ODBC connection which points to the listener.  When I configure the system DSN and try to test the connection at the end, I get timeout errors.  However, when I change the server to point to the instance name instead of the listener name, the connection works.  How can I test the listener using ODBC? What other method can I use to test the listener?

Note: I do not have access to an application to test client connectivity.

Converting my comment to answer :

You can use SQLCMD.exe -S <AG listener> -D <AG database> -E.

You may want to specify AGlistener,port_no to see if you can connect to the listener.


How to know the locale that has been used to initialize a Postgres server? I need it in order to create a new database with initdb with the same locale, otherwise pg_upgrade will fail.

One option is to use psql -l, but it outputs many lines if there a lot of databases.


MS SQL Server Express CPU The specs say "limited to the lesser of 1 socket or 4 cores" however if you have two express instances on the same server and that server has 8 cores CPU 0-7, is it possible to assign instance 1 to CPU 0-3 and instance 2 to CPU 4-7?

SQL Server Express Edition will honor the specified processor affinity settings but not more than the 4 core edition limitation.  I don't think it's defined which 4 cores are used if you specify more than 4.  Note that unlike other SQL Server editions, a service restart is required for changes to take affect.


Wait time differences in Oracle I'm trying to track the different wait time classes for current Oracle sessions but I must be misunderstanding something.  When I compare the sum(time_waited)/1000 for a given session in gv$session_wait_class its often greater than the value of seconds_in_wait from gv$session and sometimes even greater than its last_call_et which I thought would be the total time...

What am I missing here?  Why aren't they the same?  I'm comparing numbers with this query:

select last_call_et,wait_time,seconds_in_wait,wait_time_micro/1000000,
(SELECT SUM(time_waited)
  FROM gv$session_wait_class ot WHERE
  ot.sid = ses.sid
  AND ot.serial#                          = ses.serial#
  AND ot.inst_id                          = ses.inst_id
  )/1000 as wait_time_class from gv$session ses where sql_id is not null;

From the description of v$session:

  SECONDS_IN_WAIT
  If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.
  This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO.

  WAIT_TIME_MICRO
  Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait.

These aren't cumulative measures, they're only related to the current or last wait event.

v$session_wait_class on the other hand sums up the times a session has been in different wait classes.

  V$SESSION_WAIT_CLASS displays the time spent in various wait event operations on a per-session basis.

  TIME_WAITED
  Amount of time spent in the wait class by the session

So it's understandable that the times indicated in [g]v$session_wait_class are larger than the ones you're looking at in [g]v$session.
The opposite could be true too though, depending on when a wait is accounted in the wait class view. If it's accounted once the wait event is over and not dynamically (I think that's what happens), the current wait could be larger than the sum of previous waits.


impdp expects wrong file path Summary:

I'm using expdp / impdp to export a database from a test / demo database (Oracle 11.2) and import it into an instance of Oracle Express (11g) on my own machine.

The import fails because it tries to create imported database files in locations from the source server.  Literally, my source temo / test database is on a machine with an E:\ drive.  But my computer only has one C:\ drive.  And I get errors because it can't find things on the E:\ drive.

This error feels like a red herring.  Either I've missused my tools, or I'm getting a slightly bogus error message.

Questions:

Did I run expdp wrong, and include full file paths somehow
Is my real problem that the SIDs don't line up ('demo' vs 'XE')
If I need to remap something, what specifically woudl that be?

Many thanks.

Full explanition

I run this command on the demo server:

expdp name/password@localhost:1521/demo full=y directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=exportDemo.log

I've coppied the file to my notebook, to the appropriate folder within Oracle (C:\oraclexe\app\oracle\admin\XE\dpdump).  I then run this command:

impdp SYSTEM/password@localhost:1521/XE  directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=impdpDemo.log full=y

The command runs for a while, but with errors:

;;;
Import: Release 11.2.0.2.0 - Production on Sat Jun 11 10:32:21 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    ;;;
    Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/********@localhost:1521/XE directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=impdpDemo.log full=y
    Processing object type DATABASE_EXPORT/TABLESPACE
    ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
    ORA-31684: Object type TABLESPACE:"TEMP" already exists
    ORA-31684: Object type TABLESPACE:"USERS" already exists
    ORA-39083: Object type TABLESPACE failed to create with error:
    ORA-01119: error in creating database file 'E:\ORACLE\PRODUCT\11.2.0\ORADATA\DEMO\MOBILE01.DBF'
    ORA-27040: file create error, unable to create file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.

This is the start of the log, but variations of this error happen throughout the import process, and it ultimately fails.

path to data files is included in full dump.
No, SIDs don't have to match
You may want to add REMAP_DATAFILE option (https://docs.oracle.com/database/121/SUTIL/GUID-7C9DDBED-91AC-4FA2-AB91-1FF214DA89B0.htm#SUTIL926 ) to impdp .  Alternatively, you can just pre-create all tablespaces on target database.


SQL Server compatibility with New TLS Standards The major browsers are moving beyond SSL3.0 and TLS1.0 .
The PCI Security Council has declared an end-of-life date for these
protocols to be considered sufficiently strong encryption.

We need to move away from these protocols, to use newer and stronger ones.
On Windows servers, you can very easily disable these old protocols, and instead offer only TLS1.1 or greater.  However, as noted elsewhere, Microsoft SQL Server 2008 R2 and SQL Server 2012 (Standard, at least) both will not start if those lower protocols are disabled.  However, there are a growing number of versions of MS SQL Server.  There are SQL Server Standard, Business Intelligence, Enterprise, Express, Web, and Compact editions.  And of course there is SQL Server 2008, 2012, 2014, and (in pre-release) 2016.

Which of these editions support or will support the use of only TLS1.1 or greater protocols?

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server.

UPDATE 2016-01-29 : Microsoft has announced official support for TLS 1.2 in 2008, 2008 R2, 2012, & 2014. Downloads and other info can be found in KB #3135244.

I blogged about a few of the issues that have been mentioned, as well as a warning if you are using encrypted endpoints in 2014:

SQL Server support for TLS 1.2 – Read This First!

The post also points to the correct build to download (or other action) depending on @@version.

Whether this move will affect all existing versions, just 2014 and above, or just 2016, remains to be seen. The quote below seems to imply at least 2014 will be part of the work - and I suspect much of the investment will be in the client libraries, not in the engine, so it is feasible that it will work for any version that the next release of the ODBC/Native Client drivers will support.

I got this from a PowerPoint deck by Kevin Farlee of Microsoft, and was given permission to share the information, though I don't know how much of it has been redistributed at this point. Here is the exact quote from the deck:

  Encryption in flight: Protects data between client and server against snooping & man-in-the-middle attacks. Upgrading to TLS 1.2 in CY 15, phasing out SSL.

Also if you look at KB #3052404, it seems there are patches to make it work with 2012 SP+ and 2014 (patches won't be required for 2016), but no indication there will be any back-porting to SQL Server 2005, 2008, or 2008 R2 (and frankly, I'd be quite surprised).


Count the maximum number of clients and their manager ID using three tables I need to find name, age, bonus and the maximum number of clients for a manager. I managed to get everything except for the maximum number of clients. I need to get the maximum number of clients from the client table, and match the client id with the building id in the building table and lastly match the building id with the manager id in the manager table. With the manager id I'll be able to retrieve the manager details.

This is what I have done:

select mfname, mlname, TIMESTAMPDIFF(year, m.mbdate, CURDATE()) AS "Age",
mbonus, MostClient = count(*)
from apartment a, manager m, building b
where MostClient = b.buildingid and
  b.buildingid = b.managerid and
  b.managerid = m.managerid
group by MostClient
having count(*)
order by max(MostClient)

but I'm facing an error as the MostClient in the where clause is showing unknown field list.

  Error Code: 1054. Unknown column 'MostClient' in 'field list'
  How can I proceed with this? Any hints would be appreciated.

APARTMENT

CREATE TABLE apartment
(   buildingid      CHAR(3)     NOT NULL,
    aptno       CHAR(5)     NOT NULL,
anoofbedrooms   INT     NOT NULL,
ccid        CHAR(4),
rent INT NOT NULL,
PRIMARY KEY (buildingid, aptno),
FOREIGN KEY (buildingid) REFERENCES building(buildingid),
FOREIGN KEY (ccid) REFERENCES corpclient(ccid) );

BUILDING

CREATE TABLE building
(   buildingid  CHAR(3)     NOT NULL,
bnooffloors INT     NOT NULL,
bmanagerid      CHAR(4)     NOT NULL,
PRIMARY KEY (buildingid),
FOREIGN KEY (bmanagerid) REFERENCES manager(managerid) );

MANAGER

CREATE TABLE manager
(   managerid       CHAR(4)     NOT NULL,
mfname      VARCHAR(15)     NOT NULL,
mlname      VARCHAR(15)     NOT NULL,
mbdate      DATE        NOT NULL,
msalary     NUMERIC(9,2)    NOT NULL,
mbonus      NUMERIC(9,2),
mresbuildingid  CHAR(3),
PRIMARY KEY (managerid) );

You cannot name a column in mysql with a statement like MostClient = count(*)
Use the AS keyword count(*) AS MostClient.
I think the next problem you are going to encounter is the other columns that are not aggregates are missing from your group by.
Also, I don't see how the count(*) is going to equal the b.buildingid.
Best of luck!


Problem with MySQL Master/Slave reboot when in replication There is a master and number of slaves connected to it.

Due to some patches, we have to Reboot the Master and Slave.

Afterwards, we have to make sure replication is running fine.

While doing this process, what precautions should I take so that I don't get stuck anywhere? After rebooting, I also have to validate that all the slaves are working fine.

Step 01) STOP SLAVE; on all Slaves
Step 02) service mysql stop on the Master
Step 03) Reboot Master
Step 04) For each Slave

Reboot each Slave
If /etc/my.cnf has skip-slave-start, then run START SLAVE;

Step 05) Check that each Slave is fully running

Run the following on the command line

SLAVETHREADCOUNT=`mysql -u... -p... -AN -e"SHOW PROCESSLIST" | grep -c "system user"`
echo ${SLAVETHREADCOUNT}

If SLAVETHREADCOUNT is 2, replication is fully operational.

Give it a Try !!!


DB2 10.5 stored Procedure question I'm having an issue with a stored procedure in DB2 LUW 10.5 using Data Studio 4.1.3 when trying to call more then one statement at a time.  I've narrowed the problem down to the way semi-colon's (;) are being treated.  For easier trouble-shooting I created a really simple example of what I am trying to do.

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";
    insert into "DB2ADMIN"."TestTable"
    values('a','a','a');
    insert into "DB2ADMIN"."TestTable"
    values('b','b','b');
    select * from "DB2ADMIN"."TestTable";

If I run the code above it completes fine but when I look at the sp I only see:

--<ScriptOptions statementTerminator=";"/>

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";

It only reads to the first semi-colon and then stops.  If I remove all semi-colons but the last one the procedure will not get created.  My code in real life is much longer but is behaving the same.  Any help or ideas would be greatly appreciated.

Inside a stored procedure body you have to use a semi-colon to delimit statements. But semi-colons are also the default for delimiting statements in most clients so you end up needing to change your client's delimiter to something else (I use @). You end up with something like:

create procedure cleanup()
begin
    delete from mytable;
    delete from myothertable;
end @


Get nested procedure and sql execution oracle In my Oracle 11g environment, I got performance issue with a procedure named : myProc().
In myProc() it's called to others proc named myChildProcA(), myChildProcB(),myChildProcC()......( ~10 child procedures)
In Postgresql, I have some userful tool like pg_stat_user_functions, pg_stat_statements, auto_explain...
it's provide detail information about child function excutions.
But I don't known much about Oracle.
I tried with v$sql, sql_trace and tkprof to get statistic information but it's not provide child procedure information.
My question :
Are there any way or tool to get elapsed executions time for child procedures in Oracle?

v$sql gives you the information at a SQL child cursor level and sql_trace gives you the information at a SQL execution level. v$sql also tells you the program_id and the program_line# of the compiled pl/sql code that last hard parsed it.
That said, you are probably looking for the Hierarchical Profiler


How to validate column while creating Table? Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns,

Example :

create table tab_1 (
a integer,
b integer,
Audit1 integer,
audit2 integer)

Should not create the tables without the audit columns , Is it possible by trigger or any other concept.

PostgreSQL does not support triggers on system tables, so there's no way you can write a normal trigger that fires when table definitions are changed.

PostgreSQL 9.4 introduces a new feature called event triggers. (More options have been added for event triggers in the coming 9.5 release too). You could use an event trigger to validate that table definitions follow rules you set out by writing suitable validation code in PL/PgSQL. You can query information_schema and/or pg_catalog to find details of the tables, and you can abort the transaction with an ERROR if you don't like what you see.

That said, this seems like a complicated technical solution to a simple people problem. I wouldn't advise going down this route when your code review process can be fixed to catch these sorts of problems, your app can be modified to query the schema and emit warnings on tables that aren't defined how you require, etc.


Clustered vs non-clustered tables It happened I had to work with both SQL Server and Oracle for quite a while (thankfully not at the same time).

What still puzzles me is the approach to storing tables as balanced trees. In Oracle-like RDMS heap is default, in SQL Server (and many others ) the reverse (clustered, IOT) is true. Adepts of each approach claim their way is the only 'correct' and support chosen point of view with bunch of tests/presentations. However, in my opinion, the only point they proved is that implementation of "non-default" approach is poor, and it shouldn't be used for the most cases...

I'm pretty sure both approaches are good enough (just because they still exist on the market and show comparable performance) and have some math underneath, but I've failed to find any good references.

I realize the topic may be too broad to answer, and good links are very welcome, but I really want to know why two seemingly controversial approaches have proven they are both valid.

I was in the same position when I started my career as a SQL Server DBA, but studied mostly ORACLE (10g) at my university .. and that OCA stuff as well...

I agree to @KennethFisher that both RDBMS are different and comparing them is like - comparing Apples and Oranges.

To answer your question regarding - storing tables as balanced trees ...

Both Oracle & SQL Server support B-Tree Indexes which keeps data sorted and allows searches, sequential access, insertions and deletions in logarithmic time.
B-Tree is well optimized for systems that read and write large blocks of data. Note that SQL Server uses this structure for Non-clustered Indexes and Oracle
uses it as a default to store the storage location of the table internally.
Oracle has a concept of ROWID (row id) and UROWID (universal Row ID) where in the key values and a Unique reference to the storage location of the record is contained and this represents the physical location on the disk that the record is stored.

e.g. Reference Overview of ROWID and UROWID Datatypes

SELECT ROWID, last_name FROM employees WHERE department_id = 20;

ROWID              LAST_NAME
------------------ ----------
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG

  An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

  OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.

  FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).

  BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

  RRR: The row in the block.

Hence the concepts are completely different in both RDBMS - ORACLE and SQL Server. Also, PK's created in Oracle are nothing but Balanced non-clustered indexes with ROWID for fast access and therefore no concept of either clustered or non-clustered indexes.

Now, this gets more interesting and different when it comes to B+Tree indexes:

B+Tree structures are similar to B-Tree structures, but the table records (actual data) is stored in the leaf nodes of the Primary key Index allowing fast access for exact match or range scan searches on the PK of the table.
Oracle uses what is called IOT (Index Organized Tables) and SQL Server uses what is called Clustered Indexes.

Lets look at Clustered Indexes and Index Organized tables (IOT) ...:

From Oracle Doc,

  An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

From SQL Server Doc,

  In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.

Statistics for IOT's includes the Physical scatter of rows whereas SQL Server does not include Physical location of the rows in statistics and hence Clustered Index in SQL Server is better than a HEAP - data is sorted by the clustered key and good estimates are obtained for the data to be searched.

At last some good references :

Statistics in Oracle and SQL Server - by Jonathan Lewis and Grant Fritchey
Oracle Heap Tables or SQL Server Clustered Indexes? - by Jonathan Lewis and Grant Fritchey
Index Organized Tables – the Basics - Excellent series !
Fixing heap fragmentation
Internals and Deletes

I will add more points when I come across that are worth mentioning...


MySQL BETWEEN command - performance (order) I want to create a table which will contain approx 20,000 records of three fields each.  Two of the fields will contain numeric values.

I want to be able to issue a SELECT BETWEEN command. One record will be returned.

I anticipate that certain records will be returned more frequently than others.  Will I gain any performance increase by placing these records near the start of the table? (I'm assuming that MySQL reads a table sequentially).

The answer is kind of "no" but more "your question does not have an answer because it's based upon incorrect assumptions".

1) Tables are not really stored in a particular order.

2) Even if they were, how would the DB know that the question had been answered until it had finished reading the entire table.  Putting things "up front" would not help.

3) What you ask (range of values) is in the realm of an index.  An index has more structure and roughly the concept of "beginning and end" so would be the way to go.  Build an index on the values you intend to query.

4) The way indexes are structured means that individual rows can be selected from billions with just a handful of I/O reads.  However you need to have the right values indexed to match the queries.  Look at the plan to see if it's working for you.


Bulk Insert - (0 row(s) affected) I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the file. However, I have data in the file itself.

Sample CSV File (Header + One Line below header)

.CSV Header

  changedate,deactivate,lockout,noemail,empno,orighire,salary,salut,fname,mi,lname,username,hstreet1,hstreet2,hcity,hstate,hcountry,hzip,busphone,busext,cellular,empemail,jobtitle,jobcode,jobgroup,orglevel1,orglevel2,orglevel3,orglevel4,orglevel5,company,perfdate,supervisor,misc1,misc2,misc3,misc4,active

.CSV Content

  2014-04-03 00:00:00,NO ,NO ,NO ,1133,2014-04-03 00:00:00,1111.1111,,test,test,test,user.test,111 test way,,Sacramento,CA,USA,11111,,,,test@test.org,test test,057,02,sss,261,SAC,,test,ttt,1911-11-11 00:00:00,1111,N,,test,0,NULL

.SQL

BULK
INSERT dbo.Archive
FROM 'C:\scripts\User.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n',
FIRSTROW = 2
)
GO

I tried to achieve this through ps and this was the outcome and code:

# Database variables
$sqlserver = "dbserver"
$database = "db"
$table = "tb"

# CSV variables
$csvfile = "C:\scripts\User.csv"
$csvdelimiter = ","
$firstRowColumnNames = $true

################### No need to modify anything below ###################
Write-Host "Script started..."
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()
[void][Reflection.Assembly]::LoadWithPartialName("System.Data")
[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

# 50k worked fastest and kept memory usage to a minimum
$batchsize = 50000

# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Integrated         Security=true;Initial Catalog=$database;"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize

# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable

# Open the text file from disk
$reader = New-Object System.IO.StreamReader($csvfile)
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
if ($firstRowColumnNames -eq $true) { $null = $reader.readLine() }

foreach ($column in $columns) {
$null = $datatable.Columns.Add()
}

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {
$null = $datatable.Rows.Add($line.Split($csvdelimiter))
$i++; if (($i % $batchsize) -eq 0) {
    $bulkcopy.WriteToServer($datatable)
    Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
    $datatable.Clear()
}
}

# Add in all the remaining rows since the last clear
if($datatable.Rows.Count -gt 0) {
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}

# Clean Up
$reader.Close(); $reader.Dispose()
$bulkcopy.Close(); $bulkcopy.Dispose()
$datatable.Dispose()

Write-Host "Script complete. $i rows have been inserted into the database."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()

ERROR

  PS C:\scripts> C:\scripts\Import-CSVtoSQL.ps1
  Script started...
  Exception calling "WriteToServer" with "1" argument(s): "Received an invalid column length from the bcp
  client for colid 38."
  At C:\scripts\Import-CSVtoSQL.ps1:51 char:2
  +     $bulkcopy.WriteToServer($datatable)
  +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException

The original question had \n\r as the row terminator. On Windows, a row terminator is typically \r\n, with the carriage return first and the linefeed second (for obscure but interesting historical reasons).  Try that in your BULK INSERT command.

Next, I would next try loading the file in a hex editor to validate the exact content of the line breaks.  For example, if the data is Unicode you may need \0\r\0\n as a separator, and/or the DATAFILETYPE ='widechar' flag. Note 0x0A is decimal 10, 0x0D is decimal 13: the ASCII codes for line feed (\n) and carriage return (\r), respectively.


PostgreSQL concurrency control and Serializable transactions Im building an application that needs to do some concurrent read/write operations on specific tables. Even after reading the official documentation (and many, many articles), the trouble persists, no one talk about the problem I have (and I think it's VERY common)
My DB:

Table accounts with a boolean column called disabled (the other columns can be ignored)

Table request, that represents some relation between 2 accounts. An account can "request" some action to another account.

The issue:

Accounts cannot request actions to disabled accounts. It is a business rule, so I don't want to put any logic related to this in the database (using triggers/constraints). The database should only guarantee things related with the data (FK's, unique indexes, etc)

Since the database will not handle this business rule, my application will. To do it, every time a new request is issued, the following steps happen:

Open a transaction
Read target account disabled value
// Concurrency issues happens here
// What happens if the target account update it's disabled value here?
If the target account is not disabled, create a new "request" row

Do you see the concurrency issue?
At first, I tried to use Explicit Locking, but it is too complexity to handle (my real case is a little bit more complex, but the above example is sufficient). It works, but its not maintainable.
After, I tried to use Serializable transactions, but the problem above persists, although everyone saying "When using Serializable transactions you don't have to think about concurrency"
What do I have to do to ensure this consistency? The explicit locking strategy is the only way?
Thanks!

You don't get a serializable violation, because there is not one.
If the actual order is:

T1 begin serializable
T2 begin serializable
T1 see that account is not disabled
T2 disable account
T1 insert request
T2 commit
T1 commit

The outcome of the above is compatible with the serial order of:

T1 begin serializable
T1 see that account is not disabled
T1 insert request
T1 commit
T2 begin serializable
T2 disable account
T2 commit

Because the outcome is compatible with some serial execution, it is not a violation.  If the serial execution it is compatible with is not the one you want, that is an error in your expectations, not in the execution.
If T2 were both to disable the account, and delete all pending requests for that account, in one serializable transaction, then that would be a violation.  One of the two would get served an ERROR when it tried to commit.  But if T2 only disables the account without deleting pending request against it, your business logic allows that outcome, so that is not a violation.


How to get all column names of a table in in multiple rows I have used the following to just view the column names of a table (without any values) Is there a way to view all column names in one column but separate rows? Maybe do a lateral join? Open for suggestions. It is for exploratory purposes only.

This is what I used so far:

select *
from fi.questionnaire
where false;

The folling query will provide one column, where each column name of the respective table is displayed in a separate row. The order seems random.

select column_name
from information_schema.columns
where table_name = 'questionnaire'
  and table_schema = 'fi';


Oracle: Inserting into table error - either duplicate column name or invalid datatype I'm trying to insert data from an external table.
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)
SELECT *
    FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

But now i get this error:
Error at Command Line : 171 Column : 7
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

Line 171 is the last line of this part
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)

Which is clearly not a duplicate column.
If I do:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT *
    FROM EXTERNAL ((
      ext.construct_id NUMBER (10),
      ext.n_term VARCHAR2 (50),
      ext.enz_name VARCHAR2 (50),
      ext.c_term VARCHAR2 (50),
      ext.cpp VARCHAR2 (50),
      ext.mutations VARCHAR2 (50),
      ext.mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

I get
Error at Command Line : 174 Column : 10
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Line 171 is ext.construct_id NUMBER (10),

External table name is construct_ext
CREATE TABLE construct_ext
  (    construct_id NUMBER (10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input

    ACCESS PARAMETERS
  (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        TERRITORY AMERICA
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        LOGFILE bad_files:'logflie_insert_into_construct_from_construct.log'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
   ) REJECT LIMIT UNLIMITED;

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
FROM
      construct_ext ext

WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
);

Edit:-After creating external table verify if you're able to query the table
Edit:- As per @a_horse_with_no_name you can access external table without creating using inline external table for Database version 18c and above
The problem arises when you try to insert target table without creating external table(hope someone can shed light on this) work around is to create staging table then insert into target table
    CREATE TABLE construct_stg
    AS
    -- select part works without creating external table
    SELECT *
        FROM EXTERNAL (
        (
          construct_id NUMBER(10),
          n_term VARCHAR2 (50),
          enz_name VARCHAR2 (50),
          c_term VARCHAR2 (50),
          cpp VARCHAR2 (50),
          mutations VARCHAR2 (50),
          mw_kda NUMBER (7,3)
          )
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY data_to_import
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
            TERRITORY AMERICA
            SKIP 1
            BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
            --LOGFILE bad_files:'logflie_insert_into_construct_from_construct.log'--create different directory for log file or skip this line
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
            (
              construct_id,
              n_term ,
              enz_name,
              c_term ,
              cpp ,
              mutations ,
              mw_kda
              )
            )

        LOCATION ('CONSTRUCT.CSV')
        REJECT LIMIT UNLIMITED)ext_tab;

   CREATE INDEX idx_construct_id ON construct_stg(construct_id);

   ALTER TABLE construct_stg ADD CONSTRAINT pk_construct_id_stg PRIMARY KEY (construct_id) USING INDEX;  -- to enforce unique or skip this

    INSERT /*+ ignore_row_on_dupkey_index ( consruct,idx_construct_id) */
    INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
FROM
      construct_stg ext

WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
);


Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level? The MySQL doc for START TRANSACTION says:

  The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

I'm surprised, as I thought that the SERIALIZABLE isolation level, being above REPEATABLE READ, would also offer a consistent read.

What prevents WITH CONSISTENT SNAPSHOT from working with SERIALIZABLE transactions?

I even found this bug report from 2012 where the doc originally said that this worked with both REPEATABLE READ and SERIALIZABLE, and they explicitly changed it to REPEATABLE READ only, so it looks like there is a technical reason for this. I tested it and MySQL does generate a warning when used with SERIALIZABLE.

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for
  storage engines that are capable of it.

  consistent read A read operation that uses snapshot information to
  present query results based on a point in time, regardless of changes
  performed by other transactions running at the same time.

With SERIALIZABLE isolation level you don't need to use a snapshot because all necessary data is already locked by your transaction.

SERIALIZABLE isolation level should be more ACID compliant than REPEATABLE READ because the second one allows non-locking reads which may lead to a violation of the I (Isolation) rule of ACID.


SSIS job -- how to create stored procedure that repeats SSIS jobs? Hey I realize I can probably create a modular parent package that repeats SSIS jobs in Visual Studio itself .... but I know you can execute SSIS packages using a stored procedure in SSMS -- 'exec ssisdb.catalog.create_execution @package_name=N'myPackageName' ..... etc

For a quick and dirty way to execute an SSIS package like this say ... 'X' number of times ...

Is there way to do this? Like ... on package end, repeat ... for X times?

Or even simpler, if the job duration is known/ predictable ... "execute task, wait 5 minutes .... execute again ... for X times?"

This may be a little less elegant than you are looking for but I would imagine this would work ok for you.

CREATE PROCEDURE dbo.ExecuteSSISPackageOnLoopWithWaitTimer
(
    @PackageName CHAR(100),
    @NumberOfLoops INT,
    @WaitTimer VarChar(20) = '00:05' --five minutes
)
AS
BEGIN
    DECLARE @InstanceCount INT = 0
    IF(@NumberOfLoops < 0 OR @NumberOfLoops IS NULL)
    BEGIN
        SET @NumberOfLoops = 0
    END

    WHILE (@InstanceCount < @NumberOfLoops)
    BEGIN
        exec ssisdb.catalog.create_execution @package_name=@PackageName

        IF(@WaitTimer IS NOT NULL AND @WaitTimer <> '')
        BEGIN
            WAITFOR DELAY @WaitTimer
        END

        SET @InstanceCount = @InstanceCount + 1
    END
END

Taken from a previous Stack Overflow question about SQL Wait Timers

WAITFOR DELAY '02:00' --Two hours
WAITFOR DELAY '00:02' --Two minutes
WAITFOR DELAY '00:00:02' --Two seconds
WAITFOR DELAY '00:00:00.200' --Two tenths of a seconds

Hopefully that give you what you need.


repeatable read and nolock Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock.

Does the nolock become invalid because the isolation level has already been set?

The table hint in the query takes precedence, as I would have expected.

Here's my test script:

CREATE TABLE t1 (a int);

INSERT INTO t1(a) SELECT TOP 1000 message_id FROM sys.messages;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

    SELECT * FROM t1 WITH(NOLOCK); /* Try it with and without the hint */

    EXEC sp_lock;

ROLLBACK;

With the NOLOCK hint included, no locks are taken by the SELECT statement.

In other words, setting the isolation level on the transaction makes that isolation level the default within the transaction, but that default can still be overridden by table hints, as I demonstrated.


Complex SQL query - need help I have a really hard SQL question! I've inherited a system that allows people to enter dog shows. I have inherited the database with its present architecture, and since there are office systems that rely on the database, I can't unfortunately change the architecture.

I have the following tables:

users
event
event_rules

The first two tables are self explanatory. The third table ('event_rules') contains the following columns:

event_id,
targetTable,
targetField,
allowableValues,
min_value,
max_value

The table basically records rules stating who can and can't enter a sports event. There may be more than one rule per event. So an example from the table might be:

__event_id__|__targetTable__|__targetField__|___allowableValues___|__min_value__| __max_value__|
      1     |     users     |      age      |                     |     12      |              |
      1     |     breed     |      name     | labrador, chihahua  |             |              |
      1     |     dog       |      age      |                     |             |      6       |

So for the above event, the rules are that the dog handler must be over 12 years old, the dog must be a labrador or chihahua and the dog must not be older than 6.

All the relations are in place, so for example, dog.breed_id=bred.id and so forth. I can pass in all required variables.

I want to know, for a given user, which events they can enter. My problem is this: it would be computationally huge to iterate each row in order to check which events can be entered for a given set of criteria. It would also involve (in my limited knowledge) evaluating the strings in 'targetTable' and 'targetTable'  columns and then using the values as table and column names. Can anyone suggest an approach that might complete in a reasonable timeframe? The database is running on MySQL but could be changed to PostgresQL if required.

I'm afraid I can't alter the existing schema as the current systems rely on it, but I could add columns or tables where required.

Do any experts out there have any suggestions?

Thanks
Matt

Create one or more new tables that capture the data in event_rules in a more useable form.  Then materialize those tables from the existing table.    Without more details, I can't say exactly what those tables should look like.  But here are two design problems that ought to be solved:

The column for allowable values violates first normal form, because it contains a list of values.  Decompose the tables in order to achieve first normal form, and create the right indexes to speed up the join that will be required to recompose the data.

The column for target table contains metadata.  In general, mixing metadata with data creates a confusing mess, unless you really know what you aere doing.  Consider decomposing the event_rules table into separate tables for each target table.  This looks complicated, but the resulting queries will be much much simpler, and probably faster.


Restore back up strategy for history tables I have historical/log tables which are:

very large
holds data that is not rarely queried
only inserts are performed (constantly, very often)

I want to either compressed them or move them to cheaper(slow) storage.

Because of the tables structure (there are nvarchar/varchar max columns) I was not able to apply or achieved good results using row, page, column store or column store archive compressions.

So, I have decided to move them to cheaper storage. I guess I have to options here,  but let me know if I am wrong about something:

The tables are stored on slow storage. The database is under FULL
recovery model and transaction log backup is made each 15 minutes.
The issue is I am not able to perform partiatl backup (restoring
only important data from the fast storage) and apply transaction
log chain as well. Right?
To create mirror tables on the slow storage and continue to store
the history table on the slow storage. Each day a job is migrating data
from the primary history  tables to the mirror tables. So can I now
restore a partial backup and apply the transaction log chain as
well, as the tables on the slow drive are not same?

SQL Server supports piecemeal restores.  From that link:

  Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

So you should be OK with the FULL recovery model.

I'd suggest you partition your tables.  The log tables especially should be amenable to partitioning by write date.  Direct the older partions to a filegroup on the slower, cheaper storage.  Then you can restore the important (i.e. recent) portions first and the ancient logs later.


How to install old version of mongodb? This started happening today for absolutely no reason. Worked perfectly last week. I didn't touch the machine.

I have an issue described here and need to downgrade in order to be able to upgrade. Currently I'm not able to enter the shell as it exits with error #4

need to upgrade database admin with pdfile version 4.22, new version: 4.5

If I run it with --upgrade I get:

ERROR: error: exception cloning object in admin.system.users system.users entry must have either a 'pwd' field or a 'userSource' field, but not both obj:{ _id: "nod$
exception in initAndListen: 2 system.users entry must have either a 'pwd' field or a 'userSource' field, but not both, terminating

I'm trying sudo apt-get install mongodb-10gen=2.2.10but it doesn't work.

Can anyone direct me to a command I can use to install version 2.2.10?

You have to add the repo list

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10
echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list
sudo apt-get update

Then re-run  sudo apt-get install mongodb=2.2.7

That should work. The repo list file was the differentiating step which you've missed