Can't add server user to database role I'm trying to create a new user login with sql authentication and also an application role on a specific database. I can create the role and user fine, but when I try to alter the role to add the user as a member I get an error. I must be missing something obvious?

use ciq;

create role rl_saga_ciq_loader;

grant select on ciq.dbo.ciqcountrygeo to rl_saga_ciq_loader;

CREATE LOGIN usr_saga_ciq_loader_dev WITH PASSWORD=N'ItGoesOnForever!', DEFAULT_DATABASE = ciq;

alter role rl_saga_ciq_loader add member usr_saga_ciq_loader_dev;

EXEC sys.sp_addrolemember @rolename = 'rl_saga_ciq_loader', @membername = 'usr_saga_ciq_loader_dev'

Gives

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35
User or role 'usr_saga_ciq_loader_dev' does not exist in this database.

You missed CREATE USER command after creating the login:

USE ciq;
CREATE USER [usr_saga_ciq_loader_dev] FOR LOGIN [usr_saga_ciq_loader_dev];


Duplicate table names after restoring a backup I got this weird issue today when I restored a full .sql backup from one server to another server using dbForge Studio for MySQL (third party software).

Both are having same properties like
table type: MYISAM
latin1_swedish_ci: latin1_swedish_ci
latin1: latin1
I am not using any temporary tables
Is it possible to save same table names in MySQL?

Here is an illustration of the issue:

You problem is incredibly straightforward.

In Linux Systems, the file extensions for a MyISAM table tablename must be

tablename.frm
tablename.MYD
tablename.MYI

The file extensions are case-sensitive

Windows does not check case-sensitivity for .myd and .myi file extensions, so a MyISAM table in a Windows MySQL instance will allow for this. Such is not the case with Linux.

In your case, let's look at the UsersInGroups table. You evidently have two tables with two different filenames and/or extensions:

UsersInGroups.frm
UsersInGroups.MYD
UsersInGroups.MYI
usersingroups.frm
usersingroups.myd
usersingroups.myi

You will have to rename the files in the OS so that there are only .MYD and .MYI extensions. You can then query each table using the case-sensitive name UsersInGroups and usersingroups.

If you simply want to replace one with the other. delete the old files, keep the new files, and make sure you only have .MYD and .MYI extensions. BTW .frm must always be lower case.

If you are using dbForge to make a backup of MyISAM tables from a Windows machine and you restore that backup on a Linux machine, you must always remember to change the file extensions after restoring from backup.

I talked about this 5 years ago (How can I move a database from one server to another?)


Oracle dump to csv and keep NULL as empty string I have the following sql file:

SET COLSEP ';'
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET PAGESIZE 32766 -- Maximum number we can set.
                   -- i.e there will be duplication of headers.
SET LINESIZE 32766 -- Maximum number we can set.SET NEWPAGE NONE
SET VERIFY OFF
SET TERM OFF
--SET TRIMS ON
SET TRIMSPOOL ON
SET UNDERLINE OFF

SPOOL dump_audit_table.csv

SELECT timestamp, PROXY_SESSIONID,sessionid FROM dba_audit_trail where rownum < 10;

set trimspool off
SPOOL OFF
EXIT

Which exports the following CSV:

TIMESTAMP          ;PROXY_SESSIONID; SESSIONID
04-29-2014 21:46:40;               ; 707304320
04-29-2014 21:46:40;               ; 707304320
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228

My Problem is: PROXY_SESSIONID nulls are represented as '         ' a string with column width of spaces.
I could not find a way to completely terminate this. I would like the file to be exported in  the following way:

 04-29-2014 21:46:46;;707452228

e.g: NULL should be ;; read: empty string!
     SESSIONID should not have a leading space.

This is how I normally make .csv files.
You should be able to open the file with Excel.

SET TERMOUT OFF
SET FEEDBACK OFF
SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 2048
COLUMN text format A2048
SET VERIFY OFF
SET TRIMSPOOL ON
SET TAB OFF
SET UNDERLINE OFF
SET TERMOUT ON

SPOOL dump_audit_table.csv

SELECT '"'||timestamp||'","'||PROXY_SESSIONID||'","'||sessionid||'"' text
FROM dba_audit_trail where rownum < 10;

SPOOL OFF
QUIT


Multiline UDF or Stored Procedure for SSIS Data Source We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow.  Would utilizing a stored procedure produce any faster results when streaming data from one place to another?

Table Valued Function Very Slow

It will always depend on the logic being implemented but I'd consider at least the following items when choosing between the two object types as a SSIS data source...

Are you planning to modify data in a permanent table as part of the logic? You may be restricted to using a stored procedure if you need to perform interim DML when preparing the data source.
Will your final result come from a SELECT from a temp table? It becomes more tedious to use a stored procedure as a data source as the temp table isn't created until sproc runtime; SSIS therefore can't see the table structure for column mappings in the data flow. Workarounds include using retaining connections at package level, utilizing global temp tables and delaying/bypassing validation of the package and affected data flows. It's a bit of headache compared to a table-valued UDF where you can define the output table structure up-front.


Postgres Automated Install Is it possible to automatically create a database after a postgres one click install (from Enterprise DB)?

It doesn't look like the one-click installer does this directly (see http://www.enterprisedb.com/resources-community/pginst-guide#noninteractive) but if you are calling this from another installer, you could start up the db afterwards and call createdb on it.

I assume you are trying to install from an installer for another program.  if so that's how I would do it.


Speeding Inserts of longtext or longblob columns i'm currently migrating a DB2 database to MySQL, using
SQL WORKBENCH Tool

It basically does a select from DB2 and Insert to DB2.

It works fine, but on the tables with longblob or longtext columns (for storing xml or images) it takes a huge amount of time. (about 40 hours for a table of 10M rows).

Facts:

Server: 2CPU CORES and 8GB of RAM OS: Linux RHEL 6.5
MySQL Server 5.6.13
Tables are InnoDB.
MySQL Server and the Migration tool are on the same computer so there
isn't network overhead.
Tables on MySQL have PRIMARY KEYS but not any other indexes.
I disabled binary log for the massive insert.
I configured the migration tool to do a batch insert of 10
rows and commit every insert (10 rows).

Two questions here:

What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?
Is there any tuning recommended on my.cnf to improve the insert performance?

Here's the contents of my.cnf file:

[mysqld]

open_files_limit=2048
table_open_cache=1024
query_cache_type=1
query_cache_size=64M
tmp_table_size=32M
thread_cache_size=9
default-storage-engine=INNODB
back_log=80
flush_time=0
join_buffer_size=1M
max_allowed_packet=1G
max_connect_errors=100
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
log-bin
log_bin_trust_function_creators=1

#INNODB
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=1
innodb_data_file_path = ibdata1:5G:autoextend
innodb_log_buffer_size=16M
innodb_buffer_pool_size=6144M
innodb_log_file_size=1GB
innodb_thread_concurrency=3
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0

InnoDB Architecture (from Percona CTO Vadim Tkachenko)

  What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?

Increase innodb_log_buffer_size to 256M
Increase innodb_log_file_size to 2G
I wrote about increasing these to accommodate TEXT/BLOB fields in the past

Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?

  Is there any tuning recommended on my.cnf to improve the insert performance?

Increase the Write I/O Threads (For more info, see Configuring the Number of Background InnoDB I/O Threads)
Change innodb_thread_concurrency to default setting of 0

Proposed Changes

[mysqld]
innodb_log_buffer_size    = 256M
innodb_log_file_size      = 2G
innodb_write_io_threads   = 16
innodb_thread_concurrency = 0

GIVE IT A TRY !!!


When are dimension tables still needed using clustered columnstore indexing? I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database.

In initial designs I was thinking star schema but then I started playing with CCI. Now I have discarded many dimension tables in favor of flattening the strings directly into the "fact" table.  The only place I retain the dimension table is when that dimension has attributes that change frequently AND the requirement is to have the changed attribute made applicable to all historical records.  I have done this much to the dismay of a colleague who has more DW experience but no free time to explore CCI.

It appears that flat tables stored on disk as individual columns (and the massive compression that affords) need not be narrow at all.  When does one still need dimension tables when using CCI?

I don't think that your question is applicable to any RDBMS that supports columnar storage. I'm writing my answer from the point of view of SQL Server and most of the reasons depend on implementation details specific to SQL Server.

  When does one still need dimension tables when using CCI?

1. The volume of changes to the dimension table makes updating the CCI fact table impractical

With fact tables of 500 M rows you might need to update hundreds of millions of rows in a CCI if some of the dimension columns change in an unlucky way. The only practical ways that I know to do this are to rewrite the whole table or to do a delete + insert. For the delete + insert approach you'll likely need to write the data for all columns to a staging area, wait for the serial delete queries to finish (unless you can delete by partition), read all of the columns for all of the rows for rowgroups which might contain a row that needs to be changed, and so on. It can be a hassle to code and pretty expensive to convert the data. The problem gets worse as your fact table gets wider.

2. The length and number of string columns makes CCI compression impractical due to memory limits

Memory grant requests for string columns can get out of control depending on how you're building the CCI. For example, a REBUILD of a VARCHAR(8000) column requests 6.5 GB per DOP and scales down with column length. Memory grant requests time out for CCI inserts at 25 seconds (as far as I know there's no way to change this). This means that some of your CCI insert queries could start directly writing to the delta store (along with deadlocking and other bad things) if you don't have enough memory to perform the compression.

3. Your ETL or maintenance processes aren't designed to prevent or clean up delta stores

You mention "massive compression" in your question, but data in delta stores isn't compressed. If your ETL process creates a heap and later compresses that data into columnstore format then you could be using a lot more temporary space for staging than you're used to. If you do a lot of parallel inserts into partitioned tables you could end up with thousands or more delta stores for which data won't be compressed, and so on.

4. The dimension table has a lot of unique, long strings

SQL Server 2016 is limited to 16 MB dictionary sizes per column. If a column has too many unique values then you can exceed that limit and the rowgroup will be split up due to dictionary pressure. Adding string columns to an existing CCI fact table can result in smaller compressed rowgroups which can reduce the effectiveness of compression and querying performance.


Does this trigger make sense? I've written a trigger for my database which is supposed to conduct the following business rule: if a customer doesn't have a valid driving licence, they are not allowed to rent a car. They are allowed to come back with a valid one (obviously).

Does this make sense? I have a column called status, but the receptionist will have to fill out the status anyway? Also, would you recommend doing a trigger for age separately?  Thanks.

DELIMITER //

CREATE TRIGGER check_licence
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
DECLARE Status ENUM('Approved','Unapproved');
IF NEW.valid_licence = 'Yes' THEN
SET Status='Approved';
ELSE
SET Status='Unapproved';
END IF;
END //

DELIMITER ;

I'll create a small example, that you perhaps can use:

create table customers
( customer_no int not null primary key
, valid_licence char(3) default 'No' not null
, status varchar(10) default 'Unapproved' not null
);

insert into customers (customer_no) values (1),(2),(3);

So now we have 3 customers:

select * from customers;
+-------------+---------------+------------+
| customer_no | valid_licence | status     |
+-------------+---------------+------------+
|           1 | No            | Unapproved |
|           2 | No            | Unapproved |
|           3 | No            | Unapproved |
+-------------+---------------+------------+
3 rows in set (0.000 sec)

If I get it right your trigger should look like:

delimiter //
create trigger check_licence
BEFORE UPDATE ON customers
FOR EACH ROW BEGIN
    SET NEW.Status = CASE WHEN NEW.valid_licence = 'Yes'
                          THEN 'Approved'
                          ELSE 'Unapproved'
                     END;
END //
delimiter ;

Test:

update customers set valid_licence = 'Yes' where customer_no = 2;

select * from customers;
+-------------+---------------+------------+
| customer_no | valid_licence | status     |
+-------------+---------------+------------+
|           1 | No            | Unapproved |
|           2 | Yes           | Approved   |
|           3 | No            | Unapproved |
+-------------+---------------+------------+
3 rows in set (0.000 sec)

As for your question "Also, would you recommend doing a trigger for age separately?". First of all, don't use age, it will change as time passes by. Use birth_date and calculate the age. Not sure what you intend to do there though, can you clarify?


Importing LARGE quantity of data into a PostgreSQL database I am importing a large amount of data into a PostgreSQL server (across multiple databases on the server).

When I last attempted to import the data, it effectively "crashed" my machine (Ubuntu 10.0.4), because /var/log/postgres contained over 360G of files (and I was only about 1 half way through the import).

I am not sure what the log files are for (I am guessing it must be for ACID compliancy etc). But I would like to know if there is a way to reduce the size of the log files generated during import of a large amount of data.

Log files in /var/log/postgres are only for your information (in a standard installation) and do not serve the system itself, especially not "ACID compliancy". They need to be writable once configured, that's all the server needs.

There are a number of settings in your postgresql.conf that govern what is logged. Most of them can also be set at the command line to overrule the setting in the config file. We are not talking about WAL files, which are not usually placed in /var/log/postgres.

I would drastically reduce the verbosity in your case. Among others, I would set (and reload the server):
log_statement = none

Depending how you import the data, you probably need to do more than that. Look at:
log_min_messages

To skip writing log files altogether you can set:
log_destination = 'stderr' ...

More in the manual.


Indexing for query using CASE for join I'm trying to optimize a query on Oracle 10g (I know!), and I have a 4 column table which contains varchar2 values similar to this structure:

CREATE TABLE Settings (
    Client VARCAR2(50)
    , Class VARCHAR2(50)
    , Setting VARCHAR2(50)
    , Setting_Value VARCHAR(20)
)

The query is meant to use static values for Client and Class, these have been hardcoded, while Setting varies in two sections, and Setting_Value should join to different columns in another table, depending on the value of Setting.

I've been setting up the join using this structure:

Edit: It appears there's been confusion. The Setting column effectively points to the column name of the other_table. As such, Settings records act as filters for a given Client/Class operation. As such, adding Setting to the other_table is counterproductive.

WHERE Settings.SettingValue =
    CASE Settings.Setting
        WHEN 'Column1' THEN other_table.Column1
        WHEN 'Column2' THEN other_table.Column2
        WHEN 'Column3' THEN other_table.Column3
        ...

The problem is that I can't get Oracle 10g to hit any indexes on other_table for the values in the CASE statement. Is there a strategy I'm missing or a structure that would work for Oracle 10g to use an index as opposed to a full table scan?

You should probably try with OR like:

select *
  from myTable join otherTable
on (setting = 'A' and value = column1) or
   (setting = 'B' and value = column2)

Also UNION ALL may work for you

select * from myTable join otherTable on setting = 'A' and value = column1
union all
select * from myTable join otherTable on setting = 'B' and value = column2


Not able to delete transaction log backup file I have a big SQL Server database (~1.5 TB) and i didn't take log backup of that database for last two weeks for some reason.
Today i found that the .ldf file of that database became huge around 1.5 TB.
So i took the transaction log backup and then shrinked the .ldf file to fix the space issue.
I used the sql job(from maintenance plan) for taking transaction log backup and the transaction log backup taken after two weeks came up to 2TB.
Now that the space issue is solved,i tried to delete the log backup(~2TB) but
it says that i dont have permission to delete.
I took another log backup which is like 200MB and i am able to delete those.
But the first huge log backup i am not able to delete.
I checked the permission,and the domain account using which i logged into the server belongs to admin group.

I even used the sql job to scrub old backup,but even that fails.

Only this huge log backup has the security part as shown in the screenshot.

All the other log backup taken after that huge lock backup has the security tabs as shown in the second screenshot below.

Any idea what is going on.?

If i stop the sql service/sql agent service ,then can i delete this .trn file?
I wanna try that sometime when no one is using the database.

I was able to delete the 2TB log backup file after i restart the server.
Not sure why such a lock happened.

The underlying partition was initialized using MBR (master boot record) instead of GPT (GUID partition table), and you've broken the 2TB file size limit.

I'm willing to bet that if you ever replay that log backup, it will fail because the backup would have stopped writing when it reached the 2TB boundary.

You will need to reinitialize the partition using GPT. As far as I recall, this is a destructive operation, meaning that you will lose any data on the drive, so back up all files on the D: drive (except that big one) before doing anything else.

Once it is reinitialized, format it as NTFS with 64KB block size (perfect for SQL Server backups), and turn on backup compression inside SQL Server to prevent this kind of problem in the future.

If SQL Server is using this drive for its data files as well, you will definitely need to arrange some downtime for this maintenance. Good luck.


SQL Server Partially Contained Databases and Linked Servers What is the work around for using linked servers with partially contained DBs?  Additionally, how would this work with Always on and a secondary replica?

There does not seem to be an issue for me querying a Linked Server from a partially contained database when logged in as a contained user. I would always recommend using a synonym. I did this on a stand alone SQL 2014 instance, using 4 part naming or the synonym worked fine. When logged in as a contained user you cannot see the Linked Server, but it works fine to call it.

Don't see why it would be a problem with Always On Availability Groups...just create the Linked server on each replica, and call it. Not sure what you mean by secondary replica, do you just mean in a 2-node AG? Give it a try on your instances and see what happens.


SQL Server AlwaysOn Error_number 35206 I've joined a Ecommerce business where they use SQL Server 2012 AlwaysOn for HADR and this is the first time I've come across supporting this technology.

The Sql Server log on both nodes gets spammed almosy daily with the error messages shown below.

What do these errors mean? Are they benign?

I asked the Infrastructure guys to take a look at system and network logs - and they couldnt see anything odd at the times correlating with when the below messages are logged. The AlwaysOn Dashboard records error 35206 against the connection timeout message and the BOL explanation does not make clear whether I should be addressing these timeouts.

  Message
  A connection timeout has occurred on a previously established connection to availability replica 'SQLWEB02' with id [A794F13A-6FDA-4E7C-B418-A70B320D1AB3].  Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

  Message
  AlwaysOn Availability Groups connection with primary database terminated for secondary database 'DB1' on the availability replica with Replica ID: {a794f13a-6fda-4e7c-b418-a70b320d1ab3}. This is an informational message only. No user action is required.

  Message
  BACKUP failed to complete the command BACKUP LOG DB1. Check the backup application log for detailed messages.

  Message
  A connection for availability group 'AG2' from availability replica 'SQLWEB01' with id  [CC17411E-D3E1-4B32-95D8-8D69BB94E7E0] to 'SQLWEB02' with id [A794F13A-6FDA-4E7C-B418-A70B320D1AB3] has been successfully established.  This is an informational message only. No user action is required.

  Message
  AlwaysOn Availability Groups connection with primary database established for secondary database 'DB1' on the availability replica with Replica ID: {a794f13a-6fda-4e7c-b418-a70b320d1ab3}. This is an informational message only. No user action is required.

  Message
  Error: 35285, Severity: 16, State: 1.

  Message
  The recovery LSN (187402:6392:1) was identified for the database with ID 11. This is an informational message only. No user action is required.*

Thanks

These errors mean you had a connection timeout.  The subsequent messages are a result of that.   In a multi-subnet environment, each AG will have an IP address in each subnet and be registered in DNS.  The DNS server returns multiple IP addresses when queried. Only one will be online. SQL tries all the ip address, serially, until connection is established.  The TCP timeout is 21 seconds.  If the first ip is not online, it will wait 21 seconds before it tries the next one. The default connection timeout period for .NET client libraries is 15 seconds.  You can see where that could be a problem. You need to read this article http://blogs.msdn.com/b/alwaysonpro/archive/2014/06/03/connection-timeouts-in-multi-subnet-availability-group.aspx

If you don't have a multi-subnet environment, then I would look at the network logs again and see how long it was taking for your tcp connections.


Move live SQL Server database to a new server I have a production server running SQL Server 2008 Web Edition, and it has to keep running 24x7 with almost no down time, and it's the time for us to move to a more advanced server hardware, also it's a good chance to move to SQL Server 2012 Enterprise.

Now my question, how can I do this migration without taking the database down at all? Like for example setting something between the two servers as replication or so, so I have exactly identical database up-to-second on both servers, then I can just take the main services down for 1 minute, changing the IP address of the database connection from old server to new server and I be sure that the new server has identical copy of the old one.

I can't go with backup/restore approach as it will take time more than acceptable period, that's why I need something which guarantee identical databases exactly as if I am doing backup/restore. 5 minutes could be the max affordable downtime.

The database size is about 100GB.

New server is running 2012 Enterprise, and old server is running 2008 Web

I can exceptionally archive records from the database and minimize its size down to 20GB like.

I have the option to upgrade my SQL Server 2008 Web edition to Enterprise edition if this will help with more solutions, like replication maybe?

since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING.

Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to set up initial logshipping.

SO your steps should be :

Backup 2008 Web edition database and restore it on 2012 enterprise WITH NORECOVERY.
Launch Logshipping wizard and make sure to select secondary is already initialized. Configure logshipping to be every 1 min(this is the minimum you can get).

Move all the logins, Jobs, SSIS packages.

During the switch to new server :

stop all the webservers connecting to the database
stop all the backup, copy and restore jobs of logshipping.
take a final Tail log backup and restore it on secondary WITH RECOVERY.
Sync the logins.
Change the web.config of the application to point it to the new server. This way you dont have to change the IP address of the new server to match the old one.

I have given the post restore steps in more detailed manner here.


why is this SQL query failing to run? SELECT gs.h hours, severity."alarmSeverity", COUNT(t1.eventtime)
     FROM GENERATE_SERIES(0, 23, 1) gs(h)
     CROSS JOIN (SELECT DISTINCT t1."alarmSeverity"
                FROM (SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION
                      SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION
                      SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3)t1
                      WHERE t1."alarmSeverity" != 0) AS severity
     LEFT JOIN (t1 ON extract(hour FROM t1.eventtime) = gs.h AND t1."alarmSeverity" = severity."alarmSeverity"
     AND t1.eventdate+t1.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z'
     GROUP BY gs.h, severity."alarmSeverity"
     ORDER BY gs.h, severity."alarmSeverity";

this is supposed to give a list of hours from 0-23 along with the count of alarms triggered grouped by severity like

    {
        "hours": 0,
        "alarmSeverity": 1,
        "count": "0"
    },
    {
        "hours": 0,
        "alarmSeverity": 2,
        "count": "0"
    },
    {
        "hours": 0,
        "alarmSeverity": 3,
        "count": "0"
    },...

the error is

Query 1 ERROR: ERROR:  syntax error at or near "ON"
LINE 14:      LEFT JOIN (t1 ON extract(hour FROM t1.eventtime) = gs.h...

1st : We JOIN tables/view or sub-query that returns a result-set.

2nd : ON condition is used to get the result matching given condition.
      Note: CROSS JOIN don't need ON condition.

I tried to correct you query. Hope this query will help you out to get desired result.

WITH CTE AS

(SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3
)

SELECT gs.h hours, severity."alarmSeverity", COUNT(CTE.eventtime)
     FROM GENERATE_SERIES(0, 23, 1) gs(h)
     CROSS JOIN (SELECT DISTINCT "alarmSeverity"
                    FROM CTE
                    WHERE "alarmSeverity" != 0
                ) AS severity
LEFT JOIN CTE
ON          extract(hour FROM CTE.eventtime) = gs.h AND CTE."alarmSeverity" = severity."alarmSeverity"
            AND CTE.eventdate+CTE.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z'
GROUP BY gs.h, severity."alarmSeverity"
ORDER BY gs.h, severity."alarmSeverity";

Thanks!


Primary node becomes secondary before application is deployed and moved into AAG We have a small database that was created on a primary node. But by the time the application was deployed and it was time to move the db into the AG I noticed that it became the secondary node.  For client connectivity I gave the listener name. The application has been installed successfully. Is is still possible to move the database into the AG (because the former primary is now the secondary)

Please advice

You can only add a database to an AG from the Primary node.  Your options are to back and restore the database to the new primary, or fail back over to SQL06 to make it the primary, then add it to the AG.


Server requested authentication method unknown to the client [mysql_old_password] Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password]
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

I get the error above while trying to connect to my MySQL database server on Hostgator, using a PHP script on my local server. I understand it's a password hashing issue usually between pre MySQL 4.1 and MySQL 4.1+.

I've changed my local server to use the old_password following the instruction I found here.

After changing it to 16 bit hash, I still can not connect, and the error message remains the same. And more so, I can no longer login to phpmyadmin. I'm using XAMPP on windows. Please any suggestions on how to solve these problems?

I had the same error, trying to debug locally some application that connects to remote DBs.
When using php < 5.3, everything worked.
When using php 5.3 or greater, error shows up.

After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user.
All databases i use have new password length (41).

I changed the password for that user, and everything worked fine, even after changing it again to the previous value.

Hope this helps someone.


Buffer usage for index with no reads Using SQL Server 2008 R2, the main transactional table in a vendor database is massive compared to all others and has about 14 indexes. Some of these indexes don't get used in our environment, but we can't remove them. That's not a problem, it's just something we have to live with.

The question I have is about why some of these low-or-no-read indexes seem to be using memory - more than other indexes on the same large table that get used often. I would have thought that most of the buffer cache would go towards objects that are read from frequently. The only thing happening on these indexes is write overhead.

For example, one of these low-read indexes is being allocated about 2 GB of memory (58% of the index's total size) and another has 1.7 GB of memory (27% of its size). Meanwhile, the monster-sized and well-used clustered index itself only has 4 gigs (2% of its size). A different NC index with lots of reads only has 100 MB in the buffer cache (5% of its size).

Looking at the physical stats, I can see the fragmentation is pretty bad. That's understandable from all the writes on this table and the non-sequential inserts. I'm not sure if it could be related to memory usage, though.

Looking at the operational stats for these indexes is also interesting.

The leaf_ghost_count reminds me that there are just about as many deletes taking place on this table as there are inserts (from a regular cleaning process).
One of these low-read indexes has some of the highest page_lock_wait values in the database. Perhaps that's only because of the writes?
2 others have some of the highest page_io_latch_wait values. I understand that latch usage would be related to memory usage, so that makes sense.

I realize this is an abstract question and that I'm not providing many actual stats. I'm just curious about how SQL Server comes to these buffer cache usage decisions and wonder if anyone out there understands it.

Sql Server's Buffer Pool is a wonderful thing.  It's smart enough to handle all sorts of situations in a fairly intelligent way.  Here are a couple examples showing how at first glance the buffer pool behavior seems strange, but is actually fairly clever.

Consider a 400 GB clustered index on a server with 64 GB of Memory available for the buffer pool.  If a query scans the entire 400 GB index, how much buffer pool should the index use after the query is complete?

Probably not very much.  If Sql Server aggressively cached all the data it accessed maybe the last 50GB that it read during the scan is in memory. However, the next time you run the same query, that 50GB is useless because Sql will have to read the first 350GB from disk before it gets to the last 50GB.  This is a common enough scenario that Sql handles it intelligently.  Instead it is more likely that all of the intermediate level index pages will be cached along with a few of the leaf pages.

I've got a server with 512GB of memory, but it never seems to use all of it, or it uses it..strangely?

Sql Server is NUMA aware.  On NUMA (Non-Uniform Memory Access) systems, some memory is closer/faster to a CPU than other memory.  For example, a 4 socket system might have 4 memory controllers (1 per socket).  It is faster for a CPU to access memory on the controller attached to it vs accessing memory through some other cpu's controller. (I've seen 1.4-1.7 times slower access).

Sql Server knows this, and will prefer to allocate local node memory before remote node memory. Since requests can run on any cpu (they don't typically jump between them) and hence allocate memory on any node, the first request to access an index will cache it's pages in local memory.  In most cases, you'll end up with a fairly equal distribution of cached pages, however, each node is managed individually by sql server, so effectively, your 512 GB is really four 128GB buffer pools.  You could have the situation where node 1 is using all 128GB, node 2 is using 80GB, and pages are getting discarded from node 1 to make room while node 2 has lots of free space.

Why would my heavily read from indexes be cached less than indexes only receiving writes?

One possible reason is checkpoints.  Depending on your recovery interval, it may be several minutes after a data change occurs before a checkpoint writes the page to the data file (it's already in the log file on disk).  During this time, the page can't be discarded to make room in the buffer pool.

Besides setting the size, are there other ways to change how the buffer pool behaves?

There isn't a whole lot of control over the buffer pool and the decisions it makes. That's fine because you typically don't need to tweak it.  Watching perfmon counters for "SQLServer:Buffer Manager" and "SQLServer:Buffer Node" can provide insight into what the buffer pool is doing, whether it is sufficient in size, whether your IO subsystem can keep up with the checkpoints.

Regarding a couple of items you mentioned:

Fragmentation on disk doesn't affect memory usage directly. However, if an index has 50% free space on the pages due to deletes, it will take up twice a much room on disk and in memory, but that's related to free space, not fragmentation.
PAGE_IO_LATCH Waits measure the time it takes for the IO subsystem to load pages into memory.  They are an indicator of disk performance and not memory.
You are correct, high page lock waits are due to the writes. The writes make pages dirty, and dirty pages have to stick around in the buffer pool until they get checkpointed to disk.


Group by and average from related tables Ok so this is kinda weird I'm not sure how to exactly tackle it without it being a massive query, that might be slow so this is the tables as it is

Concerts:
name | country_id | ticket_price
foo    2            50
bar    1            40

Country:
id | name
1    UK
2    US

Region:
id | name
1    EU
2    America

Country_Region:
id | country_id | region_id
1    1             1
2    2             2

of course this is fictitious data/ schema but it does exactly what the real one does, I need to keep the country/region as a many to many so now what I need to do is get an avarage of say the ticket_price per month per region.

I'm really not sure how to get that query.

Thanks

Edit: I should also point out that there aren't just 2 regions and more can be added the whole time and country within them changed.

Are you looking for something along the lines of:

SELECT r.name as Region,
    avg(conc.ticket_price) as AverageCost
FROM Region r
INNER JOIN Country_Region cr
    ON cr.region_id = r.id
INNER JOIN Concerts c
    ON c.country_id = cr.country_id
GROUP BY r.name

This should not be a slow query at all - especially if the id fields are all indexed.

I recommend reading up on MySQL Aggregate Functions which use Group By from here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html


How to find the list of SQL Server jobs using PowerShell? I need to create Windows PowerShell Scripts that will check and list down all SQL Jobs scheduled on a given database server.

Is it possible to find out the list using PowerShell Scripts? If yes, how to do it?

The easiest way is to install the DBATools library and use the command Get-DbaAgentJob.

  To return all SQL Agent Job on the local default SQL Server instance.

Get-DbaAgentJob -SqlInstance localhost

There is no explicit command to filter all scheduled jobs on the server, so we can use common features at the end of the script.

Get-DBAAgentJob -SqlInstance localhost -NoDisabledJobs | Where-Object {$_.HasSchedule -Match "True"}


sp_send_dbmail is populating 00 bits between all characters in a result set I'm using sp_send_dbmail in a SQL agent job triggered nightly. The job queries our database and checks for product price updates, if there are some, it will send them in an email as an attachment to our e-commerce vendor. They have some automatic processes they will update our e-commerce platform, however their automated process cannot deal with the file provided by sp_send_dbmail.
sp_send_dbmail seems to be putting null characters between all characters in the result set.

See result of opening the csv in a hex editor:

Viewed through a text editor I see as expected:

sp_send_dbmail query here:

    SET @FileName = 'Update_' + CONVERT(VARCHAR(12),GETDATE),105) '.csv'
    SET @Query = 'Some Query'

    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'me@domain.com'
        @query = @Query
        @attach_query_result_as_file = 1
        @query_attachment_filename = @FileName
        @query_result_separator = ','
        @query_result_no_padding =  1
    END

What's going on here?

****EDIT FOR ADDITIONAL CLARIFICATION****

Using Microsoft SQL Server 2008 R2
Collation is Latin1_General_CI_AS

Leaving no_padding out leaves several trailing spaces in each returned field. each space (20) separated by a null (00)

The file encoding for the attachments is standard UTF-16 Little Endian (LE) -- one of the various Unicode encodings. The first two bytes, shown in the first image, are FF FE. These are the Byte Order Mark (BOM), which is an explicit indication of the files encoding. And you can see in the chart on that linked Wikipedia page that FF FE indicates an encoding of UTF-16 Little Endian.

UTF-16 uses 2 or 4 bytes per "character", so the 00 null bytes are really just half of each of those characters. And, because it is Little Endian instead of Big Endian, the bytes in each pair are swapped. Meaning, the highlighted 00 in the top image is paired with the preceding 50 (for the capital P). But the pair 50 00 is just Little Endian for 00 50, which is Unicode Code Point U+0050 (there is a chart at the bottom of that page that shows the encoding variations).

If you were to open that file in any editor that auto-detected the file encoding via the BOM, it would know that it was a UTF-16 LE encoded file and display it correctly, as you would expect to see it. Just try it out in something like Notepad++.

The first thing I would do is contact the vendor and/or check the documentation to see if either the e-commerce platform already has a means of dealing with UTF-16 LE encoded files (a.k.a. "Unicode" in Microsoft-land), OR if they can make a small change to handle Unicode files. I would be surprised if they both don't already handle this in some fashion and won't do it if not already able to. Given that this world is moving towards more internationalization rather than less, you can't be the only client needing to pass in a Unicode file. Also, converting the file to ASCII Extended could lead to data loss if your data naturally has characters in it that do not fit into a single code page.

The last thing I would do is update the definition of sp_send_dbmail. I am not saying to never, ever do that, but in most cases it is unnecessary. If anything, it would be preferable to convert the encoding of the attachment after it is sent (which will probably need to be done by the recipient of the email). An intentional conversion will allow for specifying the correct encoding, whether it be UTF-8 or Extended ASCII using one of the many available code pages.


Passing parameters for dynamic SQL in a function I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In outline, it looks something like this:
CREATE OR REPLACE FUNCTION return_result_table(
    _param1 integer[],
    _param2 text[])
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$
DECLARE
    _table_name TEXT;
    _select TEXT;
BEGIN
    _table_name := '_XYZABC_' || replace(current_date::text,'-','_') || '_' || 'ZXCVBN'; -- not the real code
    _select := '(select * from some_other_function(_param1, _param2))';
    execute 'create table some_schema.' || _table_name || ' as ' || _select;
    return _table_name;
END;
$BODY$;

The code is accepted, and it appears to be forming the command correctly as
execute 'create table some_schema._XYZABC_2020_10_07_ZXCVBN as (select * from some_other_function(_param1, _param2))'

but when I try to execute it with:
SELECT return_result_table(ARRAY[0,1,5,19],ARRAY['AA,'BB','CC'])

I get the error

ERROR:  column "_param1" does not exist

LINE 1: ...ect * from return_result_table(_param1...

It appears that the code is trying to substitute in the value of a column (of what?) rather than using the parameter.
How can I prevent this translation happening, so that I can pass the parameters through into the called function?

To make it work dynamically, do something like this:
CREATE OR REPLACE FUNCTION return_result_table(_param1 integer[], _param2 text[])
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _table_name text := to_char(current_date, '"_XYZABC_"YYYY_MM_DD"_ZXCVBN"');  -- equivalent, but still not the real code
   _select     text := $$(select * from some_other_function($1, $2))$$;  -- !
BEGIN
    EXECUTE format('CREATE TABLE some_schema.%I AS ', _table_name)
         || _select
    USING _param1, _param2;

    RETURN _table_name;
END
$func$;

db<>fiddle here
This passes parameters as values to EXECUTE with the USING clause, which is cheapest and safest.
$N ($1 and $2) in the string passed to EXECUTE refer to appended arguments, not to function parameters like it would in SQL code outside of EXECUTE. (Ordinal numbers are in sync only by chance.)
Note this restriction:

Another restriction on parameter symbols is that they only work in
SELECT, INSERT, UPDATE, and DELETE commands. In other
statement types (generically called utility statements), you must
insert values textually even if they are just data values.

The manual is a bit fuzzy there. The above command CREATE TABLE AS is just such a utility statement, but parameter passing still work because the SELECT statement is evaluated on its own before results are passed to CREATE TABLE AS.
format() is instrumental to make it simpler and safer, too.
The used format specifier %I preserves the name as given (case sensitive) adding double quotes as needed automatically. (No SQL injection possible.)
Note that I only use nested dollar-quoting to fix the currently broken syntax highlighting of the site. Else, it can just be:
  _select     text := '(select * from some_other_function($1, $2))';  -- !

There are quite a few related answers with more details here on dba.SE. Try a search for [postgres] [dynamic-sql] format code:EXECUTE code:USING
Prominently:

How to use function parameters in dynamic SQL with EXECUTE?

Also:

Table name as a PostgreSQL function parameter


pt-online-schema-change trigger problem I need to run an alter with Percona tool but it gives error:

  The table gts.xml_bookings has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers

So I checked the triggers like this:

  show triggers like 'xml_bookings'\G;

output:

Trigger: booking_status_update2
           Event: UPDATE
           Table: xml_bookings
       Statement: BEGIN
IF (OLD.backofficestatus <> NEW.backofficestatus
       AND left(OLD.booktime,10) <> curdate()) THEN
   insert into booking_status_changes
       set processid=new.processid,
           oldstatus=OLD.backofficestatus,
           newstatus=NEW.backofficestatus,
           website=NEW.website;
END IF;
END

         Timing: BEFORE
         Created: NULL
         sql_mode:
         Definer: root@localhost
         character_set_client: utf8
         collation_connection: utf8_general_ci
         Database Collation: latin1_swedish_ci
         1 row in set (0.00 sec)

  ERROR:
  No query specified

What are the options I have? Eventually I need to use pt-online-schema-change to altering.

As you may know, pt-online-schema-change works in this way:

Creates a new table identical to the original one, but empty
Alters the new table
Creates triggers on the original table to start keeping the tables in sync (update/delete of rows already copied, insert of new rows)
Copies the rows in bunches
Swap the table names

So triggers are a fundamental part of the process (unless the table is readonly or you are definitely sure that no one is going to modify it now). The problem is that, before MySQL 5.8 and MariaDB 10.3, you can have only one triggers for each combination of table + timing + event. For example, your table can have only one trigger BEFORE UPDATE.

One thing you can try is, turn your existing BEFORE UPDATE triggers to AFTER UPDATE - unless you already have an AFTER UPDATE trigger, this will do the trick. And do the same with INSERT and DELETE triggers.

Or try to use gh-ost from GitHub, as someone already suggested. This would work because this tool doesn't create any triggers: instead, it reads the binary log and applies the changes to the new table manually.


PostgreSQL Geo Spatial Query is slow After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only.

I need to fetch records from a Table by distance (must not be exactly) and sort by distance. The table has 10 million records.

As I got for my Query on PostgreSQL slower speed then on MySQL I think I must do something wrong.

What I can do better?

Table:

id | hash_id | town | geo_pt2

geo_pt2 is geography

Index:

CREATE INDEX geo_pt2_gix ON public.member_profile USING gist (geo_pt2)

Query:

SELECT hash_id, town
     , ST_Distance(t.x, geo_pt2) AS dist
FROM   member_profile, (SELECT ST_GeographyFromText('POINT(47.4667 8.3167)')) AS t(x)
WHERE  ST_DWithin(t.x, geo_pt2, 250000)
ORDER  BY dist
limit 100 offset 1000;

Explain:

Limit  (cost=9.08..9.08 rows=1 width=53)
  ->  Sort  (cost=9.07..9.08 rows=1 width=53)
        Sort Key: (_st_distance('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, member_profile.geo_pt2, '0'::double precision, true))
        ->  Index Scan using geo_pt2_gix on member_profile  (cost=0.42..9.06 rows=1 width=53)
              Index Cond: (geo_pt2 && '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
              Filter: (('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography && _st_expand(geo_pt2, '250000'::double precision)) AND _st_dwithin('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, geo_pt2, '250000'::double precision, true))

I'm using PostgreSQL 10 on a modern Server with high IOPS (NVMe) and the Query need 35 sec.

After suggestions of @Evan Carroll much better performance:

EXPLAIN ANALYZE SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
OFFSET 10000
FETCH NEXT 100 ROWS ONLY;

Limit  (cost=9.31..18.21 rows=1 width=61) (actual time=392.608..394.138 rows=100 loops=1)
  ->  Index Scan using geo_pt2_gix on member_profile  (cost=0.42..9.31 rows=1 width=61) (actual time=26.624..392.776 rows=10100 loops=1)
        Index Cond: (geo_pt2 && '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
        Order By: (geo_pt2 <-> '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
        Filter: (('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography && _st_expand(geo_pt2, '250000'::double precision)) AND _st_dwithin('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, geo_pt2, '250000'::double precision, true))
Planning time: 89.020 ms
Execution time: 395.039 ms

If the user paginate to end it got slow:

EXPLAIN ANALYZE SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
OFFSET 1000000
FETCH NEXT 100 ROWS ONLY;

Limit  (cost=9.31..18.21 rows=1 width=61) (actual time=28872.156..28873.239 rows=100 loops=1)

->  Index Scan using geo_pt2_gix on member_profile  (cost=0.42..9.31 rows=1 width=61) (actual time=32.441..28764.569 rows=1000100 loops=1)
    Index Cond: (geo_pt2 && '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
    Order By: (geo_pt2 <-> '0101000020E610000088855AD3BCBB474052499D8026A22040'::geography)
    Filter: (('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography && _st_expand(geo_pt2, '250000'::double precision)) AND _st_dwithin('0101000020E610000088855AD3BCBB474052499D8026A22040'::geography, geo_pt2, '250000'::double precision, true))
Planning time: 50.979 ms
Execution time: 28875.403 ms

First, use EXPLAIN ANALYZE (not mere EXPLAIN) and show the result of \d on the table.
(psql). As a first point this,

ST_GeographyFromText('POINT(47.4667 8.3167)')

should be written as ST_MakePoint(47.4667, 8.3167)::geography

Your problem here is this pattern,

SELECT ST_Distance( ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
...
ORDER  BY dist
LIMIT 100 OFFSET 1000;

Every time you do that, you must at least calculate the distance to 1100 rows. That said, it shouldn't be slow. It's slow because in order to that like that you're having to calculate the ST_Distance on all rows. We can stop that using KNN there using the <-> operator. MySQL doesn't support KNN.

SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
FROM   member_profile
WHERE  ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
ORDER  BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
OFFSET 1000
FETCH NEXT 100 ROWS ONLY;

As a style critique, I personally prefer OFFSET/FETCH (the standardized method limit/offset).

Pagination

I'm not sure this will work. but, it may be worth trying (keep us updated).

SELECT hash_id, town
     , ST_Distance(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
     , ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2 AS myknn
FROM member_profile
WHERE ST_DWithin(ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2, 250000)
  AND ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2 > OLD_VALUE
ORDER BY ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2
FETCH NEXT 100 ROWS ONLY;

So the first time you run this you save the value of last value of myknn then the second time you run this, you can replay that value back in this clause as OLD_VALUE,

AND ST_MakePoint(47.4667, 8.3167)::geography <-> geo_pt2 > OLD_VALUE

So each time you run it you're saving the new point from which to continue at, and using FETCH NEXT x ROWS ONLY.

myknn and dist may be the same for you, if so you can just remove one of them.


Extract data from an xml fragment In SQL Server 2014, I have the following xml fragment :

<salesorder>
   <ordnum>123</ordnum>
   <orddate>2017-04-17</orddate>
   <details>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>5</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H1</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>1.50</value>
            </specification>
         </specifications>
      </detail>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>10</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H2</value>
            </specification>
            <specification>
               <name>Colour</name>
               <value>Red</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>2.75</value>
            </specification>
         </specifications>
      </detail>
   </details>
</salesorder>

I would like to extract the following data :

ordnum   itemtype     qty   price
123      Stationery   5     1.50
123      Stationery   10    2.75

Is this possible to accomplish in T-SQL?

Thanks in advance,
MP

Below t-sql code will surely help you.

DECLARE @vXML XML;
SET @vXML = '<salesorder>
   <ordnum>123</ordnum>
   <orddate>2017-04-17</orddate>
   <details>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>5</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H1</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>1.50</value>
            </specification>
         </specifications>
      </detail>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>10</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H2</value>
            </specification>
            <specification>
               <name>Colour</name>
               <value>Red</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>2.75</value>
            </specification>
         </specifications>
      </detail>
   </details>
</salesorder>';

SELECT
       salesorder.Col.value('ordnum[1]', 'int')
       , detail.Col.value('itemtype[1]', 'varchar(255)')
       , specification_qty.Qty AS Qty
       , specification_price.Price AS Price
FROM @vXML.nodes('//salesorder') AS salesorder(Col)
    CROSS APPLY salesorder.Col.nodes('details') AS details(Col)
    CROSS APPLY details.Col.nodes('detail') AS detail(Col)
    CROSS APPLY detail.Col.nodes('specifications') AS specifications(Col)
    CROSS APPLY (SELECT specification.Col.value('value[1]', 'int') AS Qty FROM specifications.Col.nodes('specification') AS specification(Col) WHERE specification.Col.value('name[1]','VARCHAR(255)') = 'Qty') AS specification_qty
    CROSS APPLY (SELECT specification.Col.value('value[1]', 'decimal(3,2)') AS Price FROM specifications.Col.nodes('specification') AS specification(Col) WHERE specification.Col.value('name[1]','VARCHAR(255)') = 'Price') AS specification_price

Little description about the solution:

As the provided XML is a multilevel XML, we will need to process the data in chunk. Hence we need to segregate the multiple levels of xmls in multiple tables and then cross apply them to get the required result. If it was a single level XML data then a single SELECT * FROM Tbl method could have worked.

Reference Links:

Single level XML solution provided by Sanchitos on Stack Overflow

Multilevel XML solution provided by George T on Stack Overflow

Query XML in SQL Server for Different Hierarchy Levels using Cross Apply

Hope this will help.


docker and postgres: using password_file leads to no pg_hba.conf entry for host I'm aware of the following solution for the no pg_hba.conf entry for host error: connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

But I wonder why this error happens when using Postgres environment variable POSTGRES_PASSWORD_FILE in Docker, and not when setting the password directly with an environment variable such as POSTGRES_PASSWORD.

Here are the most useful bits of code. If you want to see a complete example, please take a look at this repository.

This version will trigger the error:

services:
  database:
    image: postgres:10
    volumes:
      - ./pass/db/db_pass:/run/secrets/db_pass:ro
      - data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD_FILE=/run/secrets/db_pass

While this version will work fine:

services:
  database:
    image: postgres:10
    volumes:
      - data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=db_password

The ./pass/db/db_pass file contains exactly the same password: db_password.

Basically, the only thing that changes between the two configuration is that setting the password for the database container is 1) done with an environment variable POSTGRES_PASSWORD and 2) done reading the file pointed by POSTGRES_PASSWORD_FILE, file which is bind-mounted inside the container as read-only.

If we look at the Postgres' entrypoint, POSTGRES_PASSWORD_FILE is used to fill POSTGRES_PASSWORD: https://github.com/docker-library/postgres/blob/master/10/docker-entrypoint.sh#L8-L24.

# usage: file_env VAR [DEFAULT]
#    ie: file_env 'XYZ_DB_PASSWORD' 'example'
# (will allow for "$XYZ_DB_PASSWORD_FILE" to fill in the value of
#  "$XYZ_DB_PASSWORD" from a file, especially for Docker's secrets feature)
file_env() {
    local var="$1"
    local fileVar="${var}_FILE"
    local def="${2:-}"
    if [ "${!var:-}" ] && [ "${!fileVar:-}" ]; then
        echo >&2 "error: both $var and $fileVar are set (but are exclusive)"
        exit 1
    fi
    local val="$def"
    if [ "${!var:-}" ]; then
        val="${!var}"
    elif [ "${!fileVar:-}" ]; then
        val="$(< "${!fileVar}")"
    fi
    export "$var"="$val"
    unset "$fileVar"
}

I don't get why then Postgres throws this no pg_hba.conf entry for host error if the database has been correctly created with the given password.

Looking at the entry point, the configuration should be the exact same, but I verified both with the command docker-compose -f no-file.yml run --rm database /bin/bash -c 'cat $PGDATA/pg_hba.conf' and the line host all all all md5 does not appear in the configuration using POSTGRES_PASSWORD_FILE.

Why does the default configuration changes when we make use of the POSTGRES_PASSWORD_FILE variable, and how to fix this elegantly? Do I have to provide a custom Postgres configuration file as described in https://hub.docker.com/_/postgres/, section "Database configuration"?

It was a permission issue.

database_1   | /usr/local/bin/docker-entrypoint.sh: line 19: /run/secrets/db_pass: Permission denied

My password file had -rw------- permissions. Since the docker-entrypoint.sh script sets the -e option, it was exiting early, right after this error, preventing to append the host all all all md5 line to pg_hba.conf.

I added r for group and others, as well as r-x for group and others on its parent directory. That fixed the issue.


Exhaustive SQL stored proc validation make insert slow So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job.

The requirement are as follow:
- Exhaustive validation should be done for each column, and the validation error message should be descriptive according to requirement specification
eg: Error in Column [x] due to invalid date format/exceed max length.
- Error in each column should be inserted into database (not log file)
- Insertion should be done ROW by ROW, no bulk insert. Any fail insertion description (due to invalid data) should be inserted into database.

The proposed solution is as follow:
So, first we will insert excel data row by row into excel file list. If it pass all validation, it will include all value except ErrorDesc. If it is failed it will include Error Desc and other excel row value except line will be null.

As what we observed over here, the validation (easily go to 30 to 40 depending on column number and validation requirement), as well as checking whether the memberclassname and membercountryname has corresponding ID in the database makes the insertion slow, ranging from 0.2 to 0.5s per insertion in ExcelContentList. We are currently performing select statement for each insertion to check whether memberclassname and membercountryname exist and has corresponding ID.

We appreciate if there is any suggestion/improvements.

Insert all the data into a staging table without verifying anything.  This should go quickly, but your mileage may vary.  This also assumes that you are only ever going to be doing one insert at a time.  Multiple inserts may require multiple staging tables, but you might be able to use a temp table for it or have a session identifier in the staging table to indicate discrete insert operations.

In any case, once the data is loaded into the staging table, perform your validation checks.  Something like this (I don't know your database structure so this is just an example).

UPDATE StagingTable
SET ErrorDesc = ISNULL(ErrorDesc,'') + 'MemberClassName mismatch'
WHERE NOT(MemberClassName IN (SELECT MemberClassName FROM MemberClass))

Repeat this for all your validation rules.  This should go much faster than your row by row check.

Once done, move all rows that had no validation errors into the final tables, then output all the errors for whatever you want to do with them.


This is one way to accomplish this, but I agree with JNK in the comments that it will scale very poorly and perform the same:

    SELECT CASE WHEN ISNULL([PackWeight/Vol], '') = '' OR PATINDEX('%[a-Z]%', [PackWeight/Vol]) = 0
THEN ''
ELSE CAST(SUBSTRING([PackWeight/Vol], 1, PATINDEX('%[a-Z]%', [PackWeight/Vol]) - 1)  * [Units/Pk] AS VARCHAR(256)) +
               SUBSTRING([PackWeight/Vol], PATINDEX('%[a-Z]%', [PackWeight/Vol]),  LEN([PackWeight/Vol]) + 1 - PATINDEX('%[a-Z]%', [PackWeight/Vol]))
    END
        FROM table


Oracle password has expired, however I have no way of changing it When I connect to my local development database, I get an error saying the password has expired.  I've been trying to change it with sqlplus to no avail:

C:\>sqlplus TPMDBO/password@localhost/global

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 6 15:41:02 2013

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

ERROR:
ORA-28001: the password has expired

Changing password for TPMDBO
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied

Password unchanged

I get this error no matter what new password I type in.

My main question is how I can reset this password.  However, I'm also curious if there's a way to make the password never expire.  This is a local dev database, I really don't care about the security or anything and none of the data on it is important.

If the account is locked, as far as I know, the user can't change it. The Oracle output in your question shows the account is not locked, but expired.

Log in to your database as a user who has privilege to alter other users (for example, SYS) and issue the following command:

ALTER USER tmpdbo IDENTIFIED BY new_password;

You can change the expiration behaviour. There already was a question about that on Stack Overflow:

Make Oracle Password Never Expire


Why can my index seek estimate the right number of rows and the sort operator can't? I've a query that uses a function on the predicate, something like this:

commentType = 'EL'
AND commentDateTime >= DATEADD(month,datediff(month,0,getdate()) - 13,0)

I have a filtered index on commentType that has 40K rows and when I run the query, the estimated number of rows for the Index Seek is very accurate (around 11K), but for the next step (sort operator) it completely ignores the statistics and just estimates the total number of rows in the filtered index.

Why is this happening? I know the basics about sargability, and I've tested just for the sake of sanity replacing the dateadd by an actual date (2014-01-01) and voila... The sort started guessing the number of rows correctly...

Why is this happening and how can I fix it? I can't pass a fixed date...

I believe your estimates are wrong because of an estimator bug that swaps two of the DATEDIFF arguments. I talk about this here:

Performance Surprises and Assumptions : DATEDIFF

A workaround is to calculate the first day of 13 months ago without using DATEDIFF (2008+):

DATEADD(MONTH, -13, DATEADD(DAY, 1-DATEPART(DAY,GETDATE()), CONVERT(DATE, GETDATE()));

I'm not positive that will address the estimate (I haven't tested with filtered indexes, and I'm not sure what the sort is actually doing or why it has a different estimate without the plan and/or the rest of the query).

The fix Microsoft recommends is to use TF 4199, but I'm not so sure that's what you'll need to do here:

Connect #630583 : Incorrect estimate with condition that includes > datediff

Another option would be to make sure you're on the absolute latest SP/CU for whatever version of SQL Server you're using, as they claim it is fixed in the following KB article (though this will still require the use of TF 4199 unless you are on 2014 or better):

KB #2481274 : FIX: A query that uses the DATEDIFF function may run slowly in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2

The fix can be obtained with the following builds:

2005 SP3 CU 15 (>= 9.00.4325 AND <= 9.00.4999)
2005 SP4 CU 2 (>= 9.00.5259)
2008 SP1 CU 13 (>= 10.00.2816.00 AND <= 10.00.3999)
2008 SP2 CU 3 (>= 10.00.4279.00 AND <= 10.00.5499)
By extension 2008 SP3 & SP4 (>= 10.00.5500)
2008 R2 CU 7 (10.50.1777.0)
2008 R2 SP1 CU 3 (>= 10.50.2769.0 AND <= 10.50.3999)
By extension 2008 R2 SP2 & SP3 (>= 10.50.4000)
By extension 2012, 2014, 2016 (>= 11.0)

(Next time, please include the results of SELECT @@VERSION in your question.)

I will note that the KB article says that DATEDIFF can underestimate the number of rows, which is the opposite of what is happening in your scenario. That doesn't mean the fixes don't apply to you; I think the KB article wording is inaccurate, as the estimates can go either way depending on the data and the range you're looking at.

My blog post above confirmed that the swapping no longer occurs in 2014 and up. To be safe, I would probably just scrap DATEDIFF from your predicate and use a different method to calculate the beginning of your range. I don't suggest the overkill of 4199 or using dynamic SQL to prevent the bad swap.


any better way for data archiving? I'm in one security company and our history data grows fast, causing data archiving sqls run slower and slower. Say, for the sake of data real-time need, the scripts must run in a high frequency for one time per 100s, causing performance issue, and what worse is that data stored in different DB engine(MySql and SqlServer), So I wonder is there any better way to archive financial DB data except traditional way(ODS way), which run archiving data sql scripts frequently?

This is a common issue with systems that need to retain large/indefinite amounts of historical data online. You have a few options depending on your predilections/restrictions.

Stretch Database in SQL Server 2016 is designed specifically to
solve this type of problem. Historical data gets moved the Azure so the local dataset is much smaller plus computations on the historical data is done in Azure, not your local server. However, it requires Azure. It can
be a secure as you need it to be but if you are subject to
regulations that prevent you from using public clouds, then this
won't work for you.
Partition the large tables and place cold data in different files/filegroups. That helps with some operations but it does add sizeable complexity to your regular maintenance tasks. Lots of good guidance published by Microsoft, MVPs and others on designing and managing partitioned tables for cold data so you're not breaking new ground here.
Put everything in solid state storage devices. This does not solve
the problem but will sufficiently mask the performance problems so
you'll breathe easy for a while. If SSDs become bottlenecked, switch
to Fusion IO. When that gets pegged, there'll probably be some newer tech.
Btw, this is not a recommended approach but if you need a bandaid
until you can implement a real solution, this does work. The other minor issue here is the high cost.

Highly recommend you look at #1 first followed by #2.


Stock quantity Trigger I am trying to create a Trigger to reduce the quantity of stock from my Products table when an order is received in my Orders table.

So far I have come up with the code on this DB Fiddle, which includes my query attempt. It seems to run without error but it does not reduce the Stocklevel.

I have read the standard documentation on Triggers but can't find a solution to this. Any ideas?

Taking your code,

DELIMITER //

CREATE TRIGGER UpdateStock
AFTER INSERT ON ORDERS.Quantity
FOR EACH ROW

BEGIN
   UPDATE PRODUCT.Stocklevel
   SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity
   WHERE ORDERS.Quantity = NEW.Quantity;

END;

// DELIMITER;

, I can hardly believe that you've read and understood any documentation -- not on triggers and not on updates at least. And it surely does not compile, let alone run even with errors...

(And by the way, why not posting the relevant code here but instead on an other platform? Makes things unnecessarily harder, I think.)

The ON clause of a trigger takes a table, not a column. So change ON ORDERS.Quantity to ON ORDERS.

After UPDATE follows a table, not a column. So change UPDATE PRODUCT.Stocklevel to UPDATE PRODUCT.

Via the the NEW pseudo table the values of the inserted row can be accessed in a FOR EACH ROW trigger. But not any other table or their columns or the table on which the trigger is executed, for that matter. So New.PRODUCT.StockLevel and New.ORDERS.Quantity are nonsense. Change SET New.PRODUCT.StockLevel = New.PRODUCT.StockLevel - New.ORDERS.Quantity to SET StockLevel = StockLevel - New.Quantity.

And finally, there is no column Quantity in your table ORDERS. So WHERE ORDERS.Quantity = NEW.Quantity; is just wrong either. And even if there were such a column, it would mean the stock level was set at all products having the same quantity (whatever that should mean in products) as the row inserted into order. I guess you want to update the stock level of the product in the order, so use the product id: WHERE ProductID = NEW.ProductID;

That should do the trick.


Error using sp_trace_create with parameters I'm running the following SQL on SQL Server 2000:

exec sp_trace_create @options = 2,
         @tracefile = N'h:\\trace.trc',
         @filecount = 2

and am getting the following error and can't understand why:

  Procedure expects parameter '@tracefile' of type 'nvarchar(128)'

If I pass all of the parameters in like so:

declare @id int

exec sp_trace_create @id output,
         @options = 2,
         @tracefile = N'h:\\trace.trc',
         @filecount = 2,
         @maxfilesize = 5,
         @stoptime = '2013-8-15'

I get the error:

  Procedure or function sp_trace_create has too many arguments specified.

Martin Smith has answered https://dba.stackexchange.com/a/24728/8783

This will work :

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @someinteger = @new_trace_id OUTPUT,
  @someinteger = 0,
  @someinteger = N'C:\trace\TestTrace.trc';

  -- get trace status
  SELECT * FROM ::fn_trace_getinfo(NULL)
  -- stop trace
  EXEC sp_trace_setstatus @traceid = 1 -- CHANGE THIS AS PER YOUR TRACE id
                    , @status = 0
  go
  -- delete trace
  EXEC sp_trace_setstatus @traceid = 1  -- CHANGE THIS AS PER YOUR TRACE id
                    , @status = 2


"Unable to display page" with Oracle 12c Enterprise Manager Database Express i'm using redhat 6, installed 12.1.0.2 of oracle and created a database.

database (dev12ee1) is a non-container database, there is also an 11g database on the server (dev11ee1)

lsnrctl status shows standard port of 1521 and 5500

lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-JUN-2015 11:46:54

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                02-JUN-2015 12:28:48
Uptime                    14 days 23 hr. 18 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app12/oracle12/diag/tnslsnr/csya-oradev5/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=csya-oradev5.xxxx-dmz.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=csya-oradev5.xxxx-dmz.local)(PORT=5500))(Security=(my_wallet_directory=/u01/app12/oracle12/admin/dev12ee1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dev11ee1.csya-oradev5" has 1 instance(s).
  Instance "dev11ee1", status READY, has 1 handler(s) for this service...
Service "dev12ee1.csya-oradev5" has 1 instance(s).
  Instance "dev12ee1", status READY, has 1 handler(s) for this service...
The command completed successfully

the port settings in sqlplus look like this

SQL> select dbms_xdb_config.gethttpsport() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5500

SQL>

the local_listener and dispatchers database parameters are both blank (which should be ok as everything is standard and default).

but when i go to the URL

https://csya-oradev5.xxxx-dmz.local:5500/em/

i get "This webpage is not available"

Try browsing to
https://[IP-address]/em

For me this works, but everything is -very- -very- slow (a few minutes just to load the login page).

I'm suspecting the slowness has something to do with the certificate. Why the IP-address works and the hostname doesn't... probably some configuration issue.

At least now anyone with more experience has something to go on...

For reference, all these posts seem to be about the same issue to me, but so far none has been answered:

Unable to open/connect to Oracle 12c Enterprise Manager Express
Start Oracle 12c enterprise manager in Windows 7
How to run ORACLE 12C EM?
Oracle 12c Express Enterprise Manager on Centos 6.5 is not reachable


Add column taking too long I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website:

alter table MyTable
add MyNewColumn varchar(max) not null
default ('')

The table contains binary data in a different column and is quite large.

Will cancelling by using the red "Cancel Executing Query" button cause additional problems? I'm just trying to figure out if I should attempt to cancel the query at this point, and what will happen since it's been running for so long.

Several problems with your approach:

adding a not null MAX column with default value. Make the column nullable and don't specify a default. Adding a default '' instead of letting it be nullable is incorrect semantically and a waste of significant space per-row. Make you code handle NULLs correctly.
adding a not null with default column to a table without any testing/preparation. This is a size-of-data operation as every single row in the table has to be updated. Has the potential to generate a huge amount of log because is as a single transaction. With a MAX type not even online column add in SQL Server 2012 could help, but again there is little point on having a MAX column not null with default ''.
even if the non nullable MAX with default is truly required, preparing for such an operation would require a mandatory evaluation of the log space needed (between 1.5 and 2.5 times the size of data) and pre-growth of the log file. Most likely, this is what most of the time is spent right now on, growing and zeroing the log.
doing it live.
asking for real-time advice on forums...

If you attempt to cancel the query you may or may not succeed, and the cancellation can be fast or can itself take another 25 minutes or more, depending on where is the current progress at (could be blocked on locks or could be progressing). In the later case resorting to desperation and attempting to restart the server will cause the DB recovery to last even longer.


What will happen to SQL Service Account when SQL Server is uninstalled? We are planning to downgrade from Enterprise/Standard version of SQL Server to Developer Edition. We will move the existing system and user database to the Dev Edition.

Are we going to need to request for a new Service Account to be created for the SQL Services of the Developer Edition? The existing account was created by the previous DBA. The account details were not shared with us.

We are looking into two options that reuse the old account, but we will ask the security team to reset the password, or if it will be deleted when we downgrade we will request new accounts.

Are we going to need to request for a new Service Account to be created for the SQL Services of the Developer Edition?

You can re-use the service account, assuming you know the information or can reset the information (password, for example) if it's not known. Managed Service Accounts (MSA's) can be re-used on the same server, but if moving between servers or renaming servers would need to have their metadata changed in active directory and uninstalled from the previous server, first.


Extract insertions that failed because of size limit from SQL Server Express Transaction Log I have a SQL Server Express database with Full Recovery Model.  Several months ago, the database hit it's size limit (10GB) and some data did not successfully get added to the database.  Several weeks after that point, the database began receiving monthly full backups, but never any transaction log backups.  The transaction log is still an overly sized file (>200GB). Both files are free to grow without limit.
Is it possible that this transaction log will contain the data that failed to be inserted several months ago, or is the file just that big because of no truncation/shrinking?
I appreciate that monthly backups don't make any sense with a Full Recovery Model. That was just the as-found condition.

The size of the Transaction Log is just due to its normal growth pattern based on the amount of data that's been transacted between backups. It'll grow to whatever size it needs to hold transactions until a backup is taken on the log which flushes those hardened transactions BUT does not shrink the log file. Rather that space remains as reserved empty space that the Transaction Log will re-use for new transactions.
So there is no correlation between the log file's size and if your failed to insert data is in it. At best it may be in one of the previous backups of it, as a rolled back transaction, and in a non-human readable form. Unfortunately that failed data insert is likely gone, at least in the context of this question.


MySQL: Remove bad GTID from GTID Set MySQL 5.6.35-log (community)

How can I remove a incorrectly manually injected GTID from a GTID set without doing a RESET MASTER?

STOP SLAVE;
SHOW MASTER STATUS;

278bfda1-b93d-11e4-801b-14feb5d284bc:1-129116182,
69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661,
708bb615-d393-11e3-a682-003048c3ab22:1-1009669227,
819c985c-d384-11e3-a621-00259002979a:1-234906555,
9204e764-d379-11e3-a5d9-0013726268ea:1-360176454,
c32252c2-1ce5-11e6-8f4b-c80aa91f9ec4:1

We need to remove GTID 1009231661 from this set:

69cf02cd-1731-11e3-9a19-002590854928:1-649285403:1009231661,

Does anyone know where/how the GTID sets are stored? I read in the 5.7 documentation that the GTIDs are stored in table. But where are they stored in 5.6? I would like to shutdown the server, edit the file, remove the bad GTID, and then restart so the server can pickup and continue.

For those who find this later in life..

This was a catastrophic mistake. There is no way to fix this problem. A RESET MASTER / CHANGE MASTER TO must be performed farm wide. Why is it so catastrophic? Because, in a Multi-Master circular replication, ALL the MySQL servers need to have the RESET MASTER / CHANGE MASTER TO performed.

In MySQL 5.6, the GTID sets are stored in the master BINLOG files - hence why the RESET MASTER must be done. However, when you do a RESET MASTER the command also resets the servers last used GTID to 0. Now the slaves have GTID's larger than the master server. The slave will skip transactions until the GTID exceeds the slaves GTID_EXECUTED_SET value for the master server. That is why you have to perform RESET MASTER / CHANGE MASTER TO on all the slave servers too.

After performing the RESET MASTER / CHANGE MASTER TO we should only need to dump/restore the databases that were being updated on the affected master server (aka master databases) so that they slaves can be in sync.


Selecting two values from the same table but under different conditions I want to grab a value from a table into two different columns for different values from the same table. Use this query as an example (notice how the select is on the same table aliased as 2 different tables):

SELECT a.myVal, b.myVal
FROM MyTable a, MyTable b
WHERE
  a.otherVal = 100 AND
  b.otherVal = 200 AND
  a.id = b.id

When I run a relatively simple query like this on my dataset, it works - it just takes a long time. Is there a better/smarter way of writing this query?

For readability I would rewrite the query using the more modern join syntax.  This will clearly separate your join conditions from your filters.

select a.myVal,
       b.myVal
  from MyTable a
  join MyTable b on b.id = a.id
where a.OtherVal = 100
  and b.Otherval = 200

For performance, ensure you have proper indexes.  In this limited example, ideally you would have a clustered index on ID and a non-clustered index on OtherVal.

After looking at your query, however, I cannot tell just what it is you are trying to accomplish.


Select most recent inspection, break tie between inspections from same year I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_date date,
    length number(10,2)
);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 1, 100, to_date('01/JAN/2013 6:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 2, 100, to_date('01/JAN/2014 7:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 3, 100, to_date('01/JAN/2015 8:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 4, 100, to_date('01/JAN/2016 9:00:00AM','DD/MON/YY HH:MI:SSAM'), 750);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 5, 100, to_date('01/FEB/2016 10:00:00AM','DD/MON/YY HH:MI:SSAM'), 250);

commit;

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from road_insp;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         1        100 01/JAN/13 06:00:00AM       1000
         2        100 01/JAN/14 07:00:00AM       1000
         3        100 01/JAN/15 08:00:00AM       1000

         4        100 01/JAN/16 09:00:00AM        750
         5        100 01/FEB/16 10:00:00AM        250

I'm able to select the most recent inspection of each road_id using this query:

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from
(
      select
        r.insp_id
        ,r.road_id
        ,r.insp_date
        ,r.length
        ,row_number() over (partition by road_id order by insp_date desc) rn
    from user.road_insp r
)
where
     rn =1
;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         5        100 01/FEB/16 10:00:00AM        250

However, rather than simply just select the most recent inspection, I want to do do something a bit more complex:

Get the most recent inspection
But if there is more than one inspection in that inspection year, break the tie by getting the longest inspection

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         4        100 01/JAN/16 09:00:00AM        750

In other words, if there are multiple inspections in the latest inspection year, it doesn't really matter which one came first (within the context of the year). It's more important which inspection was longest.

How can I do this?

You can modify the order by clause in the window function:

row_number() over
    (partition by road_id
     order by extract(year from insp_date) desc,   -- latest year
              length desc                          -- higher length
    ) as rn

Tested in dbfiddle.uk (only in version 11g).

In version 12c, you could also use the FETCH FIRST syntax.

The wanted PARTITION BY makes things more difficult than a simple FETCH though. We'll have to use a LATERAL inline view or CROSS APPLY:

select
    r.*
from
    ( select distinct road_id
      from road_insp
    ) d,
  lateral
    ( select
          ri.insp_id,
          ri.road_id,
          to_char(ri.insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
          ri.length
      from road_insp ri
      where ri.road_id = d.road_id
      order by extract(year from ri.insp_date) desc,
               ri.length desc
      fetch first 1 rows only
    ) r
;


Survey database design: associate an answer to a user I'm doing the conceptual model for a survey database.

The goal is store the answers given by users (it's going to be an Android app).

I have three entities: user, question and option.

A question will have one or more options (for example: How many employees do you have? 1-40, 40-1000, +1000).

Options will have a text (1-40) and a value (the value selected by user).

User will select one (or more) of these options.

My conceptual design is:

I don't know how to associate an answer with an user.

How can I represent that relation?
Do I have another entity to represent option value?

This model will store questions and pre-made answers (offered answers) and allows them to be re-used in different surveys.

I have to represent question like this one:

This question is related to this one: Survey database design: first version. Are there errors?

Survey Database Schema.
This is a real classic, done by thousands.  They always seems 'fairly simple' to start with but to be good it's actually pretty complex. To do this in Rails I would use the model shown in the attached diagram.  I'm sure it seems way over complicated for some, but once you've built a few of these, over the years, you realize that most of the design decisions are very classic patterns, best addressed by a dynamic flexible data structure at the outset.
More details below:

Table details for key tables

answers
The answers table is critical as it captures the actual responses by users.
You'll notice that answers links to question_options, not questions.  This is intentional.
input_types
input_types are the types of questions.  Each question can only be of 1 type, e.g. all radio dials, all text field(s), etc.  Use additional questions for when there are (say) 5 radio-dials and 1 check box for an "include?" option or some such combination.  Label the two questions in the users view as one but internally have two questions, one for the radio-dials, one for the check box.  The checkbox will have a group of 1 in this case.
option_groups
option_groups and option_choices let you build 'common' groups.
One example, in a real estate application there might be the question 'How old is the property?'.
The answers might be desired in the ranges:
1-5
6-10
10-25
25-100
100+
Then, for example, if there is a question about the adjoining property age, then the survey will want to 'reuse' the above ranges, so that same option_group and options get used.
units_of_measure
units_of_measure is as it sounds.  Whether it's inches, cups, pixels, bricks or whatever, you can define it once here.
FYI: Although generic in nature, one can create an application on top of this, and this schema is well-suited to the Ruby On Rails framework with conventions such as "id" for the primary key for each table.  Also the relationships are all simple one_to_many's with no many_to_many or has_many throughs needed.  I would probably add has_many :throughs and/or :delegates though to get things like survey_name from an individual answer easily without.multiple.chaining.


Restriction on self referencing on insert in MYSQL? How to restrict insert on adding self-referencing rows in a recursive relation table (a table contains foreign key points itself)?

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

An employee can't be the BOSS of themselves. Hence

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");
Query OK, 1 row affected (0.04 sec)

should be declined by some constraint. How can one add such a constraint?

If possible suggest without using a trigger.

This would be easily solved with a check constraint but these are not yet implemented in MySQL (they are just parsed for "compatibility reasons" during the CREATE TABLE definition and completely ignored after that.)

So, solution 1 is rather obvious. Move to a DBMS that has CHECK constraint implemented, like SQL-Server, Oracle, Postgres, etc. (even MS-Access has them!):

ALTER TABLE Employee
  ADD CONSTRAINT Employee_cannot_be_Boss_of_himself
    CHECK (mssn <> ssn) ;

Solution 2 is to drop the designation column altogether. From your description, all 'Owners' have null mssn and all others have their "manager's" ssn stored in the mssn column.

After dropping that column, you can always calculate it in a view:

CREATE VIEW EmployeeDesignated AS
  SELECT ssn
       , name
       , CASE WHEN (mssn IS NULL OR mssn = ssn)
                THEN 'OWNER'
              WHEN EXISTS (SELECT * FROM Employee ew WHERE ew.mssn = e.ssn)
                THEN 'BOSS'
              ELSE 'WORKER'
         END AS designation
       , mssn
  FROM Employee AS e ;

This just enforces that when a new employee is inserted, if the mssn is same as the ssn, the employee is assigned as 'Owner'.

You may have performance issues though, depending on how you use the designation column. Views inside views or complex queries are not the best deal for MySQL's optimizer.

Solution 3 is to emulate the CHECK constraint using the method described in this answer: Check constraint does not work?

This requires that you upgrade to either MySQL version 5.7 or to MariaDB 5.5.

Solution 4 is to wait for them to be implemented. There are rumors (it's 2016 now) that they will be in the next version.


SQL Server logins and users Experts,
While migrating a database from one server to another, I need to transfer the logins as well. My question is when I script them out and create them on different servers, do those logins have their old password?
If no - Is there a way to transfer the logins between servers without losing the passwords?

There something that I have for a long time, to transfer logins with password:
 You should first create the procedure, and then run it and just copy and paste ( or for sure you can run  sp_BlitzErik's script, so it will do it automatically, just don't forget to create a linked server ):

--EXEC sp_help_revlogin
--The output script that the sp_help_revlogin stored procedure generates is the login script.
--This login script creates the logins that have the original Security Identifier (SID) and the original password.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO