Upgrading MariaDB Galera cluster from 5.5 to 10.0 - rolling upgrade possible? I've had a good search around and cannot find much on the topic. We want to upgrade our live MariaDB 5.5 Galera cluster to the MariaDB 10.0 branch, ideally without any downtime. We installed originally (and upgrade on the 5.5 branch) using the official APT repos (on Ubuntu).

I've found the this helpful article on upgrading from 5.5 to 10.0 but it does not mention Galera anywhere. Does anyone know if we can do a rolling upgrade from the 5.5 branch to the 10.0 branch?

Thanks for your help.

I have been asking the same question for several months and have also been unable to find a clear answer on the internet. Perhaps that means that I'm more concerned than I should be; perhaps I should not be doing database server maintenance...

So I was forced to do this yesterday, and everything worked well. This is how I went about it:

I upgraded the servers one at a time, using a round-robin technique. So these steps would be repeated for each server in the cluster, one at a time.

Fail the node out

Shut down MySQL. (service mysql stop on CentOS)

Uninstall Galera server

yum remove MariaDB-Galera-server on CentOS. For me an uninstall and reinstall is necessary because the package manager gave me fits on upgrade. Your mileage may vary.

Update the repository if necessary

You will need to install or configure your system to point to the repository with the later version of Galera. See the Galera repository configuration page

Install Galera server 10.0

yum install MariaDB-Galera-server on CentOS. After completing the installation, I also upgraded all other packages on the system to help ensure, the necessary client libraries and such were up to date.

Also, if your package manager renames central configuration files when software is uninstalled, ensure that your undo this after installing Galera again. Otherwise, for me, /etc/my.cnf.d/server.cnf will not have the correct information.

Startup the new version of Galera

This seemed to be the most dangerous step for me, but goes well. The server logs will likely include a few lines about columns in some internal tables. Don't worry

Upgrade the internal schemas

This is the tricky part. You want to make sure that the updates applied to the internal tables are not replicated to the other nodes in the cluster. There are two wsrep settings to help with this. One is called wsrep_desync, which is new to version 10. It does something I don't quite understand that will help prevent this node from slowing down the rest of the cluster while the upgrade is underway. The other is wsrep_on which is used to configure if the local node should replicate changes to the other nodes in the cluster.

Adjust the settings by logging in to the database locally as root.

set global wsrep_desync = on;

set global wsrep_on = off;

Now you can drop back the command line and safely upgrade the internal schemas.

mysql_upgrade --skip-write-binlog -u root -p

I use the skip-write-binlog option to further assist in keeping from replicating the updates.

(Optionally) rebuild the database

I did this because of failed past upgrades and such. It doesn't take too much longer and helps ensure that all your data is consistent and in the new formats.

mysqlcheck --optimize --all-databases -u root -p

Bring the node back into the cluster

Just reverse the wsrep_desync and wsrep_on settings and the node will resynchronize with the cluster. Log back into the MySQL command line client and set those global variables back.

set global wsrep_desync = off;

set global wsrep_on = on;


How do you use pvkconverter.exe - not working for me? I tried to following this link to convert a .pfx file to the .cer and .pvk file pair.

When I run this command:

"C:\Program Files (x86)\Microsoft\PVKConverter\PVKConverter.exe" -i TDE_Cert.pfx -o Out_Cert -d mydecryptpassw

It does not produce any file, nor does it give any error. Could someone help?

I actually had the above command wrapped inside a .bat file.  This .bat file and the .pfx are both in the same directory.  I uninstalled the x86 install and re-installed the x64 version thinking I should be running the x64 version on my Win7 Prof SP1 machine.  Same issue.  When I deliberately misspelled the password or even omit it, no errors.  Nothing!  I don't know what's going on.

Finally got this to work.  A couple of issues.

I had to shell out to the command prompt "as administrator"
had to install "Visual C++ Redistributable for Visual Studio 2012".

Here's the weird part.  If I run "pvkconverter.exe' with the -d option, it gives me an error saying it requires a decryption password.  If omit the "-d" option, it generates the "PVK/DER certificate pairs" which i can see my .cer and .pvk files now.  I got my files now but not sure if they are legit.

I guess the ONLY definitive way to find out if the .cer and .pvk files are valid is to run them on SQL Server.


Inevitably I worked this out after posting and taking a bit of a break. I've also created a dbFiddle for it.

The solution appears to be to tidy up my part-solution so that it uses a common table expression (CTE):

WITH base_cte AS (
    SELECT
        latitude,
        longitude,
        generated_at AS arrived_at,
        LAG (latitude) OVER w AS lagLatitude,
        LAG (longitude) OVER w AS lagLongitude
    FROM locations
    WINDOW w AS (ORDER BY generated_at)
)
SELECT
    latitude,
    longitude,
    arrived_at
FROM base_cte WHERE
    latitude IS DISTINCT FROM lagLatitude
    or longitude IS DISTINCT FROM lagLongitude;

Then it became more obvious that I could add a second CTE which uses LEAD:

WITH grouped_cte AS (
    WITH base_cte AS (
        SELECT
            latitude,
            longitude,
            generated_at AS arrived_at,
            LAG (latitude) OVER w AS lagLatitude,
            LAG (longitude) OVER w AS lagLongitude
        FROM locations
        WINDOW w AS (ORDER BY generated_at)
    )
    SELECT
        latitude,
        longitude,
        arrived_at
    FROM base_cte WHERE
        latitude IS DISTINCT FROM lagLatitude
        or longitude IS DISTINCT FROM lagLongitude
    )
SELECT
    latitude,
    longitude,
    arrived_at,
    LEAD(arrived_at, 1) OVER (
        ORDER BY arrived_at
    ) left_at
FROM grouped_cte;

This outputs:

latitude    longitude   arrived_at              left_at
51.123456   -1.123456   2020-04-27 17:00:00     2020-04-27 17:10:00
51.654321   -1.654321   2020-04-27 17:10:00     2020-04-27 17:25:00
51.444555   -1.444555   2020-04-27 17:25:00     2020-04-27 17:30:00
51.123456   -1.123456   2020-04-27 17:30:00     2020-04-27 17:45:00
51.444555   -1.444555   2020-04-27 17:45:00     2020-04-27 17:55:00
51.654321   -1.654321   2020-04-27 17:55:00


Appropriate tempdb size? DBCC CHECKDB is failing on a particular database because of

Could not allocate space for object 'dbo.SORT temporary run storage:  143479689576448' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Tempdb is currently 5GB. The database in question is 56GB. The biggest table in that db is 15GB.

How big shall I make tempdb?

Thanks

You can use the "WITH ESTIMATEONLY" with your dbcc command.

DBCC CHECKDB WITH ESTIMATEONLY

This should give you an aprox. space in tempdb to run it. .


Find who will change a record in oracle DB Someone is updating a record in a custom Oracle table, is there a way to find who is updating the record and or at least the table?

You can audit updates to the table (assuming that auditing is enabled in your database).  Or you can create a trigger on the table that captures the user and the row being updated and writes the results to an audit table.  Or you can use the DBMS_LOGMNR package to go through the historical redo logs to see who executed an UPDATE in the past.

In any of these cases, you'll see the Oracle user that did the UPDATE.  That may or may not be what you want, however.  Most applications these days are three-tier applications where the user that is connecting to the database is shared by many different human users.  If your middle tier is handling the process of authenticating users and using a single shared Oracle user account in its connection pools, you would need to have the middle tier record who is doing the UPDATE or you would need for the middle tier to identify to the database who the current application user is for any request via a context, via calls to DBMS_APPLICATION_INFO, via calls to a stored procedure, or by just passing the user in with the UPDATE statement.


Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL Hi everyone hope you're doing well.
I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not work.
my first procedure is

  create or replace procedure p_get_student_detail(s_id  number,
     s_cur out sys_refcursor) AS

    Begin

     open s_cur for
     select student_name, age from student where student_id = s_id;

   end;

and my second procedure in which I'm using the output of the first procedure is :

Create or replace procedure s_print_data(s_id number)

   as

    type ref_cur is ref cursor;
    s_refcur       ref_cur;
    s_student_name student.student_name%type;
    s_student_age  student.age%type;

 begin

    p_get_student_detail(s_id, s_refcur);

     loop
        fetch s_refcur into s_student_name, s_student_age;
        exit when s_refcur%notfound;
        dbms_output.put_line(s_student_name || s_student_age);
     end loop;
 end;

when I try to execute the procedure exec s_print_data (1) I see this error :

ORA - 00900:invalid SQL statement

I was wondering if you could help me with that.
Thanks

OK I found where the problem is
the syntax for calling the procedure is incorrect
I have to call the procedure like this :

begin

   s_print_data(1);
  end;

and everything works fine


Finding two most recent payments for every sale Trying to get the two most recent payments for each sale number from a table of transactions (ARTRANS). Seems like a lateral join would work or maybe a window function.

Structure is fairly simple with ARTRANSID (the PK), SALENO, PAIDDATE, DUEDATE, ACCTNO (more, really, but what does that matter). Of course, with the lateral join, I'd really be doing a self join. Any suggestions on this code?

An aside: It would be cool if there were something like a GROUPLIMIT keyword, so you could limit the returned records to a certain number within the grouping. That would make this easy as pie.

A nice windowing function will do the job.

with phillovesCTEs as (
  select ARTRANSID, SALENO, PAIDDATE, DUEDATE, ACCTNO,
         ROW_NUMBER() over (
                            group by SALENO
                            order by PAIDDATE desc
                           ) as RN
  from ARTRANS
)
select *
from phillovesCTEs
where RN <=2;


mysql - 35GB binlog files are cached in OS cache I have a mysql database with 5.6.

Binary logs are enabled and it has 5 slaves.
GTID mode enabled.
Maximum binlog file size is 1GB.
sync_binlog = 0

When I check the free -gh

              total        used        free      shared  buff/cache   available
Mem:           117G         79G        1.4G         25M         37G         37G
Swap:            0B          0B          0B

37GB in Cache.

I wanted to check what are all related to Mysql. So I used fincore and I could see from mysql-bin.000557 to mysql-bin.000590 are in the OS Cache.

Total Cache from binlog is 35GB.

Is this expected behavior?

There is no replication lag in the slave. So why mysql needs to cache the old binlogs in Cache?

Yes its expected behaviour. MySQL doesn't control what goes in the buff/cache, that's purely down to the OS. It gets reclaimed quite quickly on any new file access or process memory requirement.

Linux can't really tell its not going to be used again soon, but its not doing anything else with the memory so it just uses it (or more correctly, doesn't immediately purge it).


How do I use a different domain and username in a SQL Server BACKUP LOG command? I looked here,

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017#security

I want to backup my log to a different server.  When I do this through the SSMS, I get access denied because my SQLServer Service user is not what the other machine expects, and I did not want to run the MSSQLServer service with a special domain account.

I tried a share that has everyone for SMB and NTFS, but it did not help.

How can I run the BACKUP LOG command to my mapped drive with a specific username and password that does have permissions?

I considered Powershell's SQL Server cmdlet, but I am on SQL Server 2012 and am unsure if the new PS libraries will corrupt anything.

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

I tried import-module SqlServer on the server, but it is not found.

Powershell won't help, it is just a wrapper for the BACKUP command. Your problem is that it is indeed the database engine that produces the backup file, and there is no option for impersonation here. So, you really need to sort out that service account, I'm afraid.

(There used to be something called a nullsession-share. I doubt it still exists, and basically you open that share to the whole world. Not recommended, I'm just mentioning here to recommend against it.)

Come to think of it, how about the computer account? I'm not sure if that will help, but if you grant privileges to that share for the computer account where your SQL Server is?


Move into another table or not? In my web application there exists a web form with certain questions on it. Some of these questions will be pulled / retrieved from the database.

Here is some sample data:

My question is, is there much gain in moving the Type column (determines what sort of HTML input element to display on the form) into its own table and have a foreign key to the question type?

You might argue that instead of the words 'Text, Select ... etc' I'll have numbers .. but they'll still repeat! is there any gain in doing this?

Any clarification would be greatly appreciated.

Thank You.

I think they should stay on the same table !
 If you have lots of row(questions) you can use this column(type) to partition your table by it as i think you might have 3-4 distinct values(select,text,checkbox) !

 This would make your query faster by doing this !

  As for the naming of your type column, yes they look better as number and save you some space on you database as Int comparisons are faster than varchar comparisons, for the simple fact that ints take up much less space than varchars.


Where has sysindex.rows gone? In SqlServer 2000, there used to be a table called sysindex which had a column rows.  This table is now:
-sys.indexes
-sys.partitions
-sys.allocation_units
-sys.dm_db_partition_stats

(see: http://msdn.microsoft.com/en-us/library/ms187997.aspx) but none of these tables contain the row columns?  So how do you find out how many rows there are for an index?

Thanks.

It is still there in the compatibility view sys.sysindexes.

You can also use

SELECT OBJECT_NAME(p.object_id), SUM(rows)
FROM sys.partitions p
WHERE index_id < 2 and OBJECTPROPERTYEX (object_id ,'IsUserTable' ) = 1
GROUP BY p.object_id

This should not be taken as in any way guaranteed accurate. It includes the effects of uncommitted transactions, forwarding pointers on a heap and can be manually updated with some undocumented parameters to UPDATE STATISTICS.


SQL Server stored procedure causing 100% CPU usage I'm using SQL Server 2008 R2

We have a single stored procedure that causes 100% CPU usage when ran. The said SP is ran once a month and performs heavy calculation for approximately 5 minutes.

Since the SP is ran once a month, we don't particularly care if it takes say 30 minutes or even longer to complete the process, but taking up all of the available CPU resource is problematic. We'd like to limit the CPU usage for this particular stored procedure.

Is there such an option in SQL Server?

If a stored procedure is maxing out your CPU, you really need to tune that query to fix the root cause rather than working around it. Analyse the query plan using native SSMS or SQL Sentry Plan Explorer.

Some things to look out for to improve the query plan from http://www.brentozar.com/ are

http://www.brentozar.com/archive/2012/07/identifying-correcting-sql-server-implicit-conversion/
http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
http://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

To answer your question however, yes, you can limit CPU using Resource Governor.


What is a good way to replicate code across multiple databases? SQL Server 2005

I have two databases (Source1 and Source2) with the same data model and I plan to merge the data into a third database (Target) with an "abstracted" data model. In the future, it's possible to have additional Source databases that will need to be merged into Target.

Originally, I was hoping to build views and stored procedures that would reside in the Target database that I could dynamically run again Source1 to load Target then switch to Source2. That doesn't seem to be possible without dynamic SQL.

So plan B is to build the views and procedures in the Target database then deploy them to Source1 and Source2. What is the best way to replicate code to multiple databases?

I'm open to any new ideas that address this situation.

SQL Server has replication built in. Have you looked into that?

If it's just a few views or procedures, I'll use a Central Management Server to deploy them to multiple servers. You just define the group of servers and then execute the script. Pretty simple.

For 3rd party options, Idera has Multi Query and Red Gate has Multi Script as well.


Setup MongoDB replica servers with different power? Is it a good practice to setup replicas with the same machine size (wrt. CPUs, RAM, Bandwidth, etc.) or can replicas be based on smaller machines? One usecase might be to have a small non-voting backup replica machine. Another is to save money on the replica "backend".

The only problem I see with voting replicas is that in case the primary (big machine) goes down the new primary would be weaker. Are there other problems with clusters using different machine sizes?

You can use machines of different sizes as long as the lesser machines can actually keep up with the more capable primary when replicating (particularly at peak traffic times).  You can use priorities to ensure that the more powerful machines are preferred when they are up and healthy and you can set any host that absolutely should not become primary to priority 0.  Caveats:

Do not mess around with votes unless absolutely necessary
Always keep the number of voting members to an odd number
If the only node left healthy in the set is priority 0 it will stay secondary (no primary, no writes)

One other note - if (for example) the nodes fall behind at peak, but catch up later that is usable but you will want to make sure your oplog window is big enough to cover the lag and you may want to make the lagging nodes hidden so that secondary reads do not see a lagging version of the data. Similarly, if you are seeing lag you will need to be careful with write concerns that require replication (w:2 for example) since they might end up requiring the lagging node to catch up to satisfy the concern and return successfully.

To summarize: yes you can do this, but you need to be aware of the possible implications of doing so and it can potentially complicate your deployment (versus homogeneous hosts).


'utf-8' codec can't decode byte ERROR when querying data I have created this foreign SQL Server using tds_fdw foreign data wrapper:

CREATE SERVER servprod_sqlprod_mydb
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'XX.XX.XX.XX', port '1501',msg_handler 'notice', database 'WHATEVER');

CREATE USER MAPPING FOR postgres
    SERVER servprod_sqlprod_mydb
    OPTIONS (username 'xxx', password 'xxxxxxxxxx');

CREATE FOREIGN TABLE tds_test (
    caclamun character varying,
    caclacal character varying,
    catipvia character varying,
    cadescri character varying,
    caidenti character varying,
    cafechal character varying,
    cafechba character varying)
    SERVER servprod_sqlprod_mydb
    OPTIONS (table_name 'dbo.MYSQL TABLE');

When opening the table I am getting:

'utf-8' codec can't decode byte 0xd1 in position 4: invalid continuation byte

I think it is a postgresql issue and not tds_fdw's, but I am not sure.

I fixed the problem adding character_set 'UTF-8' when creating foreign server.
Example:

CREATE SERVER myserver
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'localhost', port '1501',msg_handler 'notice', database 'mydb', character_set 'UTF-8');


Oracle PL/SQL does not work -- bind variable not allowed Oracle 12.2 PL/SQL
When running:
declare
    s_sql varchar2(200);
begin
    s_sql := 'dbms_hs_passthrough.execute_immediate@mylink' || '(''exec sp_get_names'')';
    execute immediate 'begin :x; end;' using in s_sql;
end;
/

Fails with:
PLS-00110: bind variable 'X' not allowed in this context

The procedure runs fine as
begin
     dbms_hs_passthrough.execute_immediate@mylink('exec sp_get_names');
end;
/

But I want to keep it dynamic.  So how (if at all) can the procedure dbms_hs_passthrough.execute_immediate@mylink() be executed in a dynamice fashion as in the first example?
NOTE:  It is not of any significance to understand what dbms_hs_passthrough.execute_immediate@mylink() does, just how to make it (or any other procedure that is called) dynamic.
Any suggestions?

You can't pass a procedure as a bind variable.
You should be able to dynamically build the entire anonymous PL/SQL block and execute that.  Something like this
declare
  l_plsql varchar2(1000);
begin
  l_plsql := 'begin
                dbms_hs_passthrough.execute_immediate@mylink ...
              end;';
  execute immediate l_plsql;
end;


Active Directory group as a schema in SQL Server In SQL Server 2008 R2 we have a database that is being maintained by a team of developers.
Each of them should have permissions to perform DDL operations such as adding a column or creating a table.
However, we do want to log all the DDL actions done by a developer.

Using one user for everyone is not an option due to poor audit, using multiple users (Active Directory users) results their tables having separate schema names and that's untidy.

Is there a way to use Active Directory group as the schema to enjoy both worlds?

No, an Active Directory group cannot be the schema, though I suppose you could name a schema with the same text as your AD group, but that does not get you more auditability.

If individual logins are all members of the AD group, they still appear in the database by default as their login name, not the AD group name.  (You can, of course, rename the database users to different names, but I do not see how that would really help you.)

However, using 'multiple users' (i.e one login and user per person) does not mean that they will all create their own schema.  You have considerable control over this behavior.

When you create the user's you can assign them to a default schema, such as dbo.  Likewise, if you need to set that up after the fact you can:

ALTER USER userName
     WITH DEFAULT_SCHEMA = schemaName

In addition, you can create an audit table and use a DDL trigger to track the changes being made to the database by tracing the details you want to know about.  You can also filter out nuisance changes, such as disabling and enabling indexes and so forth.

Sample DDL Audit Trigger:

CREATE TRIGGER [AuditDDLChanges]
    ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @Data XML

DECLARE
    @EventType NVARCHAR(128),
    @DatabaseName NVARCHAR(128),
    @SchemaName NVARCHAR(128),
    @ObjectName NVARCHAR(128),
    @ObjectType NVARCHAR(128),
    @LoginName NVARCHAR(128),
    @TSQLCommand NVARCHAR(MAX),
    @IgnoreMarker NVARCHAR(1)

SET @Data = EVENTDATA()

SELECT
    @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
    @SchemaName =   @data.value('(/EVENT_INSTANCE/SchemaName)[1]',   'nvarchar(128)'),
    @ObjectName =   @data.value('(/EVENT_INSTANCE/ObjectName)[1]',   'nvarchar(128)'),
    @ObjectType =   @data.value('(/EVENT_INSTANCE/ObjectType)[1]',   'nvarchar(128)'),
    @EventType =    @data.value('(/EVENT_INSTANCE/EventType)[1]',    'nvarchar(128)'),
    @LoginName =    @data.value('(/EVENT_INSTANCE/LoginName)[1]',    'nvarchar(128)'),
    @TSQLCommand =  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',  'nvarchar(max)')

-- Clause to avoid tracking things you do not want in the event table
IF ( (IS_SRVROLEMEMBER('sysadmin',@LoginName) = 1) OR
     (@EventType LIKE '%STATISTICS')
   )
   BEGIN
      SELECT @IgnoreMarker = 1
   END
ELSE
   BEGIN
        INSERT AuditDatabase.dbo.ChangeLog
           (EventType,
            PostTime,
            SPID,
            ServerName,
            LoginName,
            UserName,
            DatabaseName,
            SchemaName,
            ObjectName,
            ObjectType,
            TSQLCommand)
           VALUES
           (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
            @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
            @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)'),
            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') ) ;
      END
GO


Unclear oracle problem with getting response of query The problem is very unclear,

I have an application server, that constructs queries and sends it to Oracle to get response.

The Oracle database is installed on a high perfomance server:
Linux 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

The application server was tested on different locations inside of the intranet, including that high perfomance server, where the database is located.

Several versions of the application server (from old to newest) were tested also.

The problem:

When the application server sends a query to the database, it has to wait for about 300 seconds to get response and construct logged output.

But when I'm connecting to the database with PL/SQL Developer and running that query using that IDE, I'm getting a response in less than one second.

Is there something familiar for you, guys?

We're just cracked our heads trying to determine where the problem could be located.

If you need more information - I'll update this post immideately.

Any query which returns more than 50 rows could seem to execute faster on SQL Developer. This is because SQL developer only fetches 50 rows (and more after you press Page Down), while your application possibly fetches many more rows.

The proper way to test is to use sqlplus with SET AUTOTRACE TRACEONLY.


How to use mysql live update on a mysql table between localhost and remote server? I have mysql table created/modifying in localhost, and I need to update those in my remote database.

I have gone through lot of post regarding this scenario and I found out that I need to use mysql replication for overcome this situation.I went through some posts about mysql replication but I couldn't understand how would I do this.

Is this the method I need to use? If yes, please mention the simplest way on how to use this method?

  NOTE:- my localhost contains Inventory stock system that I have
  coded.I just need to link or maintain the finish good list on my
  remote server as product list. Any update of finish good must update in
  remote server,this is what I need to do.

Thank a lot.

Check this : Link

That is Below one :

Can't you use Triggers?

  22.5.5:  Is it possible for a trigger to update tables on a remote server?
  Yes. A table on a remote server could
  be updated using the FEDERATED storage
  engine.

From : http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-22-5-1-5

I wouldn't recommend this because you will be consuming bandwidth for every small change you do. You can try a scheduled job instead.


Trace flag 2861 and what a 'zero-cost' plan actually means I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?

The blurb says:

  SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of generating a new plan for such a simple query.

That seems to be patently untrue since every 'trivial' query I run seems to get cached. So I'm wondering what the point of 2861 is, unless I'm misunderstanding what a trivial plan actually is. When I query the cached plan and it says it's ad hoc and trivial then I have no reason to doubt it.

Hoping someone can enlighten me.

Cost is an overloaded term, as is trivial.

When talking about execution plans, the estimated cost is computed by the query optimizer, as a way to choose one plan, or one small part of a plan, over another. The final plan has an associated cost computed by summing all the subtree estimated operator costs. A trivial plan is one obtained without going through cost-based optimization.

When talking about the plan cache, a separate cache cost is computed that is an input into how long the plan will survive in cache (normally as a response to memory pressure). The details of this computation, and what is considered a "zero cache cost" (or "trivial") plan has changed many times over the years. There are many exceptions, caveats, and restrictions, which again have changed quite often.

I have no idea if trace flag 2861 is still effective in modern builds.

You can find some of the details (that were correct at the time they were written, to the best of my knowledge) in resources like:

3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2 by the SQL Programmability & API Development Team
Plan Caching and Recompilation in SQL Server 2012 by Greg Low


Teaching Slony replication to select slave nodes There is a system with up to 256 nodes in db cluster. We need to replicate some data from one of them across to all others. But for every row to replicate there is a list of nodes where it must be replicated while on other nodes same row becomes orphan without one of parent table keys. To filter them out, or to prevent them from replicating needlessly, I'm trying to find a way to teach Slony to select nodes for replication however can't find such feature readily available. Has anyone tried it (customizing source code of its functions, etc.)?

_id,_text,_nodes
----------------
1   'one' {1,101}
2   'two' {2,102}
256 '...' {0,256}

Row id=1 is then replicated to node 1 and 101, etc.

Similar discussion Replicating only significant records to specific database however doesn't point in the right direction yet.

I don't see a way to do this directly.  As I understand it, Slony sets replication destinations on the table, not row, level.

How I would solve this specific problem would be to materialize views based on replication destination group, and then use triggers to maintain those.  Slony should then be able to replicate these tables to the slaves.  I don't know if you can have triggers on the slaves that populate the main tables, but if not, there are ways around that. This requires decoupling the initial tables from the replication, and adds some complexity, of course.


Not able to login on MySQL in AWS RDS using root I am trying to connect my MySQL RDS from EC2. Under security groups I have made sure permissions are in place.

$ mysql -h xxx.xyz.ap-southeast-1.rds.amazonaws.com -P 3306 -p
Enter password:

I am sure password is correct; because i reset it using AWS console. I am getting following error:

 Access denied for user 'root'@'ip-xx-xxx-xxx-xxx.ap-southeast-1.compute.internal' (using password: YES)

Looks like

I am 99% sure that Amazon RDS does not give you a 'root' access. i.e. You can't create a user as 'root'@'localhost' or 'root'@''.

Can you confirm user you have used while launching Amazon RDS instance? and make sure that Password matches with user created.


Database not naturally shrinking log file I have a database that is set to Simple recovery mode. There is 99% free space in the log file and I can manually truncate the log file right down to 500 MB.

The next day, after a daily import process runs, the log file will be back up to ~70GB.

If I check log_resuse_wait in sys.databases it returns 0 which would indicate nothing is blocking a shrink operation.

Why isn't the log file naturally shrinking? Also, how can I find what SQL queries are causing the log file to grow to such a size?

You must check log_reuse_wait during the import process. Most likely you have a some long running transaction started at the beginning of the import that is pinning the log for the entire import duration. Simple Recovery model log is automatically truncated, not shrunk. See How to Shrink the SQL Server log.


MySQL - Changing DB CHARACTER SET I have a 30GB MySql DB, made of innoDB tables.
currently the tables character set is: "utf8_unicode_ci" and I want to change it to "utf8_general_ci", what the best way to do it? currently i'm going through each table and running ALTER TABLE some_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; and it takes for ever... is there a better way?

I know what you mean. Alter table sometimes has to rebuild the whole table when you use it.

Assuming that the Alter table statement is annoying you because it takes too long AND it locks your table, then you can use 2 tools to make it an "online" alter table.

One if from facebook https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

And one is from openark http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html (MySQL 5.1+ only)

Both these tools basically create a new table, copy the data over and keep it updated by updating both the old and the new table with new queries until the new table is completed.
This means that you need to have the space available to do this operation.

Hope it helps.


SQL Server Agent fails to start in Failover cluter The SQL server agent in a cluster (as a cluster resource) fails to start. I'm looking for error logs to give me a clue, but so far, all it says is: "failed to start". What could be the possible issues? Where can I see a more detailed error log?

Update: I've checked the sql server logs, the cluster event logs and the windows event logs.

There are logs in a few places. For the Agent logs please see the screenshot:

In Object Explorer expand SQL Server Agent \ Error Logs.


How to import big sql file in Phpmyadmin I am having a big sql file( 16mb) and all i need is to upload it to the Phpmyadmin to work with it in my localhost. I'm a newbie here in Php/MySql and i want to know about the details regarding the work.So, please suggest me the best.

Cheers and waiting for response.

Maximum file size you can upload is specified in your php.ini [ the configuration file for php ]. You would like to look for upload_max_filesize = 2M and change it to something else, for example 20M. You can also try to compress your file size, phpadmin accepts many different archives, like zip, gz etc.


Lock Timeouts with low Lock Waits On a production SQL Server 2012 database we are getting some odd locking statistics that I don't understand.

Lock Waits/Min: 0.2
Lock Timeouts/Min: 197.4
Deadlocks/Min: 0.0

@@lock_timeout is set to -1

How can I have Lock Timeouts without having Lock Waits? I recently added some missing indexes, could that be affecting these numbers?

It seems you are monitoring a Lock Timeouts/sec Performance Monitor counter or perhaps querying sys.dm_os_performance_counters directly.  The Lock Timeouts/sec class for Profiler has the following notes in the documentation:

Most likely the vast majority of the events you are seeing are explained that way.  Try monitoring Lock:Timeout (timeout > 0) instead.


SSRS- database and Report Server - can they be different? I have inherited a few database servers. I am currently planning for a Server upgrade, but this DB server has a ReportServer and ReportServerTempDB database in it.
When I open Reporting Service configuration Manager I am not able to connect to the above mentioned database server.

Is it possible that the Reporting Server for the above databases is configured in a different server other than the database server? How should I go about identifying these for further information.

I am asking because I have to move the ReportServer and ReportServerTempDB but I am not able to access the ReportServer Configuration Manager for  the encryption keys.

Yes, the report server can be installed on another machine than the server where the databases reside, there can even be more than one report server instances connected to the same reportserver database (this is called scale out deployment).

  Two or more report server instances sharing a single report server database.

You can run

exec ListInstallations

In the reportserver database to list the Report servers connected to this database.


Scheduled snapshots of views (without using materialized views or Oracle Golden Gate)? I have 40  views in an Oracle 18c GIS database that are used in a map in a workorder management system (WMS).

The views are served up to the WMS map via a web service.
There are an average of 10,000 rows per view.

The views have joins to dblink-tables in a separate Oracle database, and as a result, are not fast enough for use in the WMS map (3-second map refresh delay). Furthermore, it seems like a bad idea to compute the views each time a user refreshes the map--since the map does not need to be up-to-date in real-time.

As an alternative, I would like to take snapshots of the views on a weekly basis. The snapshots would be static tables that would perform well in the WMS map.

The Catch:

Unfortunately, due to office politics issues, using technology like materialized views or Oracle's Golden Gate to solve this problem is not an option.

What are my options for taking scheduled snapshots of Oracle views (without using materialized views or Golden Gate)?

For example, I could make an .SQL script that truncates static tables and inserts the rows from the views into the tables (although, as a novice, I don't know how efficient or risky that option would be, or if there are better alternatives).

You could create a function-based index on the sdo_geometry column in this view. This might help the query speed.
If you can't create an MV (have you tried?), create a table (create table as select...), then write a procedure that executes a MERGE from the view in to the table, then schedule it to execute every X minutes using dbms_job or dbms_scheduler.


NoSQL approach: design table "scheme" We need to store simple log data in a database, but we also need to get and filter data in realtime, based on 'user', simple 'tag' (ie. redis, memcache, php, mysql, etc.) and timestamp. We need to scale horizontally and real fast data access on billions rows.

In a SQL approach, table can be like this:

ID | timestamp | tag | user       | log text
1  | 19543532  | 1   | root       | { text log }
2  | 19543532  | 3   | redis-user | { text log }

where tag 1 and 3 are different and related to another table (ie. tag_id | tag_name). I think this is a relational approach and we can create three index (timestamp, tag and user) in order to speed up data access.

What is a good practice to reproduce this in a NoSQL database like DynamoDB (AWS) where we can create only HASH or RANGE index? Does a SQL database fit better than a DynamoDB?

My first attempt is:

First table: ID hash index

ID | log text
1  | { text log }
2  | { text log }

Second table: USER, TIMESTAMP range index

user | timestamp | id
root | 123145122 | 1
redis| 123491241 | 2

Third table: TAG index

tag        | id
debug      | 1
production | 2

Thank you in advice!

w/o any secondary index I can think of PIVOT. (Data in Key-Value pair way)

Depending on the usage and query you are expecting you need to decided if key "LogText" needs to go in to another table or ok to put in Table-2.

You can have key on both columns and that will give you option of defining local secondary index to improve performance for complex queries like

ALL DATA where (TimeStamps is between x and y) and User = "ABC"

this can be done in two steps,

select ALL data where KeyIDs are IN
( get unique KeyIDs for that satisfy this WHERE conditions.)


Clustered Table Scan Because of "SELECT *" I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths:

CREATE NONCLUSTERED INDEX [IX_Records_CustomerID]
ON [dbo].[Records] (
    [CustomerID] ASC, -- int
    [IsInvalid] ASC, -- int
    [IsProcessed] ASC, -- bit
    [IsRejected] ASC, -- bit
    [RecordName] ASC, -- varchar(12)
;

The 5 fields do not include the primary key RecordID, which is the column in the clustered index.

Here is my poorly performing query:

SELECT * FROM Records WHERE CustomerID IN (181, 283, 505)

The execution plan shows that it performs a Clustered Index Scan, which I understand is because I'm selecting columns that are not included in the index. In Management Studio, I change the query to:

SELECT CustomerID, IsInvalid, IsProcessed, IsRejected, RecordName FROM Records
    WHERE CustomerID IN (181, 283, 505)

And the execution plan shows an Index Seek, and the query execution time drops from 44 seconds to 2 seconds. However, I lack the liberty in the application to replace the * with only the columns I need and have included in my index.

Is there any way around the clustered index scan when I'm locked into SELECT *?

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice:

Perform a table / clustered index scan (therefore all columns are there)
Perform a seek, then perform lookups to retrieve the columns not covered

Which way it will choose depends on a variety of things, including how narrow the index is, how many rows match the predicate, etc. You can force a seek with the FORCESEEK hint, but I suspect it will end up performing the same or worse than the scan SQL Server has chosen in your case.

Some options:

Change the app to run a proper query. I listed this first for a reason.
Create a view that selects only the columns you need:

CREATE VIEW dbo.myview
WITH SCHEMABINDING
AS
  SELECT col1, col2, col3 FROM dbo.tablename;

Then you can change the app to SELECT * from this view. Or you can get even more creative and rename the original table, and change the name of this view to what the name of the table used to be. Breaking change, obviously; proceed with caution.
Add all of the other columns to the key or INCLUDE list for the index. If these are hard-coded values and always the ones used, you may consider a filtered index.


Using CROSS APPLY OPENJSON causes Azure to hang I have a table with around 8 million rows with a schema of:

CREATE TABLE [dbo].[Documents](
    [Id] [uniqueidentifier] NOT NULL,
    [RemoteId] [int] NOT NULL,
    [Json] [nvarchar](max) NULL,
    [WasSuccessful] [bit] NOT NULL,
    [StatusCode] [int] NULL,
    [Created] [datetime2](7) NULL,
 CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Created]  DEFAULT (getdate()) FOR [Created]
GO

The json documents are of a structure of

{
    "Id": 1,
    "Data": [
        {
            "Id": 99,
            "Name": "Person 1"
        },
        {
            "Id": 100,
            "Name": "Person 2"
        }
    ]
}

I am attempting to extract the JSON array from within the document and insert it into a new table using the following query:

;WITH CTE (Json) AS
(
    SELECT TOP 10
        JSON_QUERY([Json], '$.Data')
    FROM
        Documents
    WHERE
        ISJSON([Json]) > 0
)
INSERT INTO [dbo].[ParsedDocuments] (Id, Name)
SELECT
    JSON_VALUE([Value], '$.Id') AS [Id],
    JSON_VALUE([Value], '$.Name') AS [Name],
FROM
    CTE
CROSS APPLY
    OPENJSON([Json]) as X

What I'm finding is that if I extract and query a sample of the data, say 1000 rows.  Everything works as expected and the data is inserted into the destination table.  However, when I query the main table the server appears to just hang and become unresponsive.

I suspect that it is attempting to run a cross apply across all the rows before inserting data.  Is there any way to improve performance?  Or allow the job to begin "streaming" results into the destination table rather than attempting to batch them up?

Finally, as you can see I am employing the use of "TOP 10" results.  I am however still experiencing the performance hanging.  And I am unsure as to why.

Query plans for large table are never generated how when running against a sample of the data the query plan can be found here.

The fact that you cannot obtain even an estimated execution plan for the query against the large table suggests that compilation is waiting on statistics creation (or update) for the nvarchar(max) column.

When running against the smaller sample table, statistics creation completes quickly, so an execution plan is quick to create.

I can reproduce your situation on a low-powered (S0) Azure SQL Database. On a slightly less anaemic S3 instance, the compilation still takes around 30 seconds due to the large amount of I/O involved in reading the nvarchar(max) column to generate statistics. The premium tier is needed to get good I/O performance - even a cold P1 can get these statistics created in 3 seconds or so.

You can confirm the cause by temporarily disabling automatic statistics creation:

ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS OFF;

The SQL server will then produce a plan quickly, albeit with warnings about missing statistics:

Be sure to re-enable this feature after testing because good statistics are vital to general execution plan quality. Or commit to creating necessary statistics manually.

If the issue is instead automatic refresh of stale statistics, you could choose to have these updated asynchronously:

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Alternatively, scale your Azure SQL Database up to a performance level that suits your workload and budget.


Field types database design best practice I am creating a cms system where the user can create fields to use in the templates of the website. I want to create different types like 'string', 'html', 'integer' etc. The user must also have the possibility to add these fields in different values, so I will be needing at least two tables.

My question is this: Should I create a table for each field value type and catch this in the code or should I store all field value types in one table?

Option 1:
Table for each type

+---------------+    +-----------------+    +--------------------+
| Fields        |    | field_int_value |    | field_string_value |    etc...
+---------------+    +-----------------+    +--------------------+
| id            |    | field_id        |    | field_id           |
| field_type_id |    | language_id     |    | language_id        |
+---------------+    | value (int)     |    | value (varchar)    |
                     +-----------------+    +--------------------+

Option 2:
Store all values in one table. keep the unused values NULL.

+---------------+    +-----------------+
| Fields        |    | field_value     |
+---------------+    +-----------------+
| id            |    | field_id        |
| field_type_id |    | language_id     |
+---------------+    | value_int       |
                     | value_string    |
                     | etc...          |
                     +-----------------+

Some examples:

A user could for example create a string with his or her phone number. In the code the template would call this field to display the phone number. If the user would ever change this, they can just edit the value in the admin without having to worry about editing the template.

Another example is a boolean to allow the user to show a certain 'call to action' on a page. This boolean would be used to hide something in the template without having to update the template.

My Field model would be the one handling all this. In here I would create functions to get the field of a specific type.

I have been breaking my head over what would be the best option, but I can't seem to find the better one. Does anyone have any ideas?

Not discounting @gbn's suggestion (in a comment on the question) to possibly store this data somewhere other than in a RDBMS, I will say that if you do decide to go the RDBMS route, you are better off using a single table with a column per each "type" such that they can be strongly-typed. Or, you could use a single table with a single string field as all data can be converted/serialized to a string, but then you have to be careful to do the conversion properly in both directions for all operations, and that might prove problematic if some uses of that table either incorrectly convert or forget to convert altogether.

The reason for choosing a single table is mostly logistics: it is far easier to dynamically select a different column based on a COALESCE or CASE (or CASE-like) clause than it is to dynamically pull from different tables in a query based on the TypeID of the row (at least not without having to LEFT JOIN all "type" tables in all queries (yikes!).

Depending on which RDBMS you are using, there might even be vendor-specific features to assist in this model. For example, SQL Server, starting in version 2008, I believe, has a SPARSE option that can be added to NULLable columns. This allows for a NULL value to take up 0 bytes whereas normally, for fixed-length fields (i.e. pretty much all of them that aren't [N]VAR{something} or {something} VARYING or XML or generically text or SQL_VARIANT), they would always take up their specified number of bytes. The downside is that for non-NULL values, they now take up 4 extra bytes per row. But it can be a huge savings when the column is 75% or more NULL across all rows. This feature was designed specifically to address this type of model.

Just make sure to have a CHECK CONSTRAINT to ensure that for all of the value_ fields, either none, or only one, of them is NOT NULL.

Then you can access via something like:

COALESCE(value_int, value_string, value_datetime, etc.)

And again, if you are using SQL Server (and maybe one or more other vendors have this feature), you can make use of "filtered indexes" to index each specific value_ field where that value is not null:

CREATE NONCLUSTERED INDEX [IX_FieldValues_ValueInt]
  ON dbo.[FieldValues] ([ValueInt])
  WHERE [ValueInt] IS NOT NULL;

This will allow you to target rows that do have values, although probably not when using the generic COALESCE access method, but when targeting a specific Value% field.

AND, looking again at the two options, I would actually go with Option 3, which is a single table. In Option 2 it does not seem like there is much purpose in breaking field_type_id out as that data is not really a parent to the field_value data. A field_id can only have a single value, right? So just put field_type_id in the field_value table. And make use of it in the CHECK CONSTRAINT that ensures a single field at most is NOT NULL, to ensure that the one field, if any, that is NOT NULL matches the field_type_id.


Proper names for SUM(col) fields I have worked on MySQL databases for a long time, but one question I have never been able to get an answer to that seemed formal is: In the query SELECT *, SUM(col1) FROM tbl, what is the proper name for the field SUM(col1). The closest I have been able to determine is it is a computed column, is this correct or does it have another name?

The MySQL Documentation refers to the SUM() function as an aggregate or group function. In light of this, I would call it an aggregated column since the MySQL Documentation starts with:

  This section describes group (aggregate) functions that operate on sets of values. Unless otherwise stated, group functions ignore NULL values.

As for the CASE statement, the book

page 82 paragraph 1 and pages 88-92 refers to CASE as a conditional control statement. Personally, I would call it a conditional control column or, simply, a conditional column.


Logon Trigger Unexpected Result I have only server logon trigger and it is being used to prevent logins coming from an application/server combination. It works appropriately for that application/server combination and gives the print statement errors in the error log.

However, I'm also getting sql errors for 2 other users that say "Severity 020 occurred, Logon failed for login 'randomuser' due to trigger execution." which don't come from that server/application combination and the received error message isn't what I've used in the trigger.

Any ideas what could be causing this?

I know that this isn't ideal and there are ways to bypass the checks but the use case is to prevent ssrs data sources coming from a dmz server being created that aren't using a predetermined restricted user account. The only ones that can create the data sources are internal employees with elevated permissions.

CREATE TRIGGER [LogonTrigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
    BEGIN TRY

        DECLARE @host NVARCHAR(128) = ISNULL(HOST_NAME(), ''),
                @user NVARCHAR(128) = ISNULL(ORIGINAL_LOGIN(), ''),
                @app NVARCHAR(128) = ISNULL(APP_NAME(), '')

        IF (   @host = 'myhost'
               AND @user <> 'acceptableuser')
            BEGIN
                PRINT 'LogonTrigger Deny User:' + @user + ' Host:'
                      + @host + ' App: ' + @app
                ROLLBACK;
            END
    END TRY
    BEGIN CATCH
        PRINT 'LogonTrigger User:' + @user + ' ErrorNumber: '
              + CAST(ERROR_NUMBER() AS VARCHAR) + ' ErrorMessage: '
              + ERROR_MESSAGE()

    END CATCH
END

"Errors with a severity of 20 or higher that cause the Database Engine to terminate the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not terminated."

Could it be because of this?

Source


Effect on msdb transaction log of running sp_delete_backuphistory for first time? Have a server with regular db backups but strangely sp_delete_backuphistory has never been scheduled so msdb is bigger than expected.  Backup history goes back 5 years in that table but only needs last 30 days of backup history. If we run sp_delete_backuphistory keeping only 30 days of history, is there any risk for the transaction log running out of space? MSDB is using simple recovery model. MSDB log file is currently 388544kb on C drive.  C drive has 45gb free.

You may want to run the deletes in smaller chunks to avoid excessive log file growth for this one-time cleanup, particularly because your msdb database is on the C:\ drive.
If you have 5 years of data to clean up, the easiest way would likely be to simply call sp_delete_backuphistory several times, to clear out the backup history in 6 month or 1-year chunks.
For example:
EXEC sp_delete_backuphistory @oldest_date = '20150101'; --Jan 2015
EXEC sp_delete_backuphistory @oldest_date = '20160101'; --Jan 2016
EXEC sp_delete_backuphistory @oldest_date = '20170101'; --Jan 2017
EXEC sp_delete_backuphistory @oldest_date = '20180101'; --Jan 2018
EXEC sp_delete_backuphistory @oldest_date = '20190101'; --Jan 2019
EXEC sp_delete_backuphistory @oldest_date = '20200101'; --Jan 2020

Simply by running this and experimenting with the @oldest_date, you could pretty easily determine how quickly you can chunk through. Depending on the number of databases and frequency of backups, doing yearly chunks will likely be sufficient. If this is a one-server, one-time cleanup, this would be the method I use.
You could also script out the definition of sp_delete_backuphistory and create your own copy of it that does the cleanup more efficiently. Erik Darling has created an alternative that is available on GitHub distributed as part of my own DBA Database project. I would use this option for ongoing/long term cleanup, and if the problem is more pervasive than a single server needing a one-time cleanup.


Create multiple columns from a single column filtering criteria? thanks in advance for your time, what I need to achieve is generate multiple columns result from a single query, the thing is this:

I got a table were is storing the next:

id | user_id | revision
1       1      Approved
2       1      Rejected
3       1      Pending
4       1      Pending
5       1      Pending

What I need is to get the next result:

Total | User | Pending | Rejected | Approved
  5       1       3         1           1

I have tried using Group by like this:

SELECT count(id) Total, user_id User, revision FROM table1 GROUP BY user_id, revision

But the output is way to far from what I am spectating to get:

Total | User | revision
  3      1      Pending
  1      1      Rejected
  1      1      Approved

The idea is to get the proper object returned to avoid over cycling the server side code with unnecessary processes, so If the database can do the handling in one call this would be great.

I tried googling for hours and all I could get were a lot of results for 'single column from multiple rows' which is something I do not need, thanks for your help in advance.

The final result set with many users should look like this:

Total | User | Pending | Rejected | Approved
  5       1       3         1           1
  5       2       4         0           1
  5       7       2         3           0

Updated:
After more researching I found this:
https://stackoverflow.com/questions/14172282/sql-query-needed-to-get-result-in-two-columns-grouped-by-type-and-percentage-in

This is almost what I need after some editing I still can't get it to send it in solid numbers instead percentages, I lack the knowledge for working group cases, I will keep trying if any of you can help it is greatly appreciated.

Solution
Hey I managed to do this, it was easy, but my lack of knowledge was in the middle, I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, so this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.

I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, this is known as statistical queries and then this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.


Cannot take backup into NAS storage at specific time I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error:

11/07/2019 22:45:00,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
11/07/2019 22:45:00,spid60,Unknown,BackupDiskFile::CreateMedia: Backup device 'B:\SDSOFT-DB\EXPRESS\LOG\SDSOFT-DB_EXPRESS_LOG_20191107_224500.trn' failed to create. Operating system error 5(Access is denied.).
11/07/2019 22:45:00,spid60,Unknown,Error: 18204<c/> Severity: 16<c/> State: 1.

I asked sysadmin to check all settings regarding NAS storage and he said that everything ok and he doesn't know what is the cause of this problem. From my side, I mapped NAS storage network path into 'B' using xp_cmdshell(SQL SERVER 2014) by the help of the following command:

EXEC xp_cmdshell 'net use B: \\192.*.*.*\DBbackups /user:<username> <password> /persistent:yes'

I would like to point out again that I get no error during the rest of the time, only on Thursday and Friday at 22:45. Maybe someone had faced with this issue before and help me to tackle this problem.

Operating system error 5(Access is denied.)

This error is somewhat cryptic, but it is helpful. This error is coming from Windows when it tries to write to file. Windows then passes the error message along back to SQL Server, who dutifully tells you exactly what it was told--unfortunately without a better explanation.

"Operating system error 5" generally means that you have access to that location, but not write access. For example, if your SQL Agent service account is only granted read permission on a folder, and you try to write backups there, you would see this message.

Except in your case, permissions work most of the time.

Your backups only fail late at night, then recover and start working again. That means it's probably not a "normal" permissions issue. Your target folder is on a NAS, so probably there is a scheduled process on the NAS that is impacting your backups.

Given the timing, I might suspect that your NAS is being backed up at that time, and doing something that is affecting your ability to write there. A lot of commercial file system backup software will do something to momentarily pause writes so that it can have a consistent image to back up, then quickly snapshot the storage and begin allowing writes again. It's plausible that you are hitting a backup process like this, or similar.

If that's the case, then I'd first evaluate adjusting the backup schedule to minimize impact. Depending on your RPO/RTO requirements, it may be perfectly ok to have one transaction log backup fail, so long as the next execution runs ok. If that's the case, just document the reason, and start ignoring the 22:45 errors.


Optimize IN clause in where query with order by - MySQL I am trying to optimize  a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query:

SELECT *
FROM `test`
WHERE user_id = 9898
AND status IN (1,3,4)
order by id
limit 30;

Here is the result of explain, as you can see the query is filesort

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  test    range   user_id     user_id     8   NULL    3   Using where; Using index; Using filesort

Here is my table structure

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `status` int(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `user_id`, `status`) VALUES
(5, 9797, 2),
(6, 9797, 3),
(4, 9898, 0),
(1, 9898, 2),
(2, 9898, 3),
(3, 9898, 4);

How can I optimize the query? In my real table I can see the following information in error log:
# Query_time: 26.498180  Lock_time: 0.000175 Rows_sent: 100  Rows_examined: 4926

I was looking at a very similar problem today. After doing a ton of searching online, I found this great article by Percona.

Using "UNION ALL", you can join together a list of queries, like so:

(SELECT * FROM `test` WHERE user_id = 9898 AND status = 1 ORDER BY id LIMIT 30)
UNION ALL
(SELECT * FROM `test` WHERE user_id = 9898 AND status = 3 ORDER BY id LIMIT 30)
UNION ALL
(SELECT * FROM `test` WHERE user_id = 9898 AND status = 4 ORDER BY id LIMIT 30)
ORDER BY id LIMIT 30

It looks gnarly compared to select ... status IN (1,3,4), but it's effective in avoiding the filesort.

As long as the inner "SELECT..." statements are efficient, the performance is good.


What is a good option to replace a daily SQL server snapshot replication job? I am looking for a good alternative to replace SQL Server Snapshot Replication that replicates some tables to a reporting server everyday.

I'd prefer an agent job that I can schedule to run every night.

I want to completely replace replication. I am thinking of a process that can be automated to copy the tables to a new database (ETL maybe). I was hoping to get some ideas here and create a plan that can effectively do what the replication has been doing.

We'd like to consider reducing our server footprint from two servers down to one server, with a database for the production OLTP load, and a separate database for the reporting workload.

Edit/Update: I have created an SSIS package to repopulate the tables, it has been working well and updating changed records (In DEV). I will check if having both PROD & Reporting Databases on the same host adversely impact performance.

ETL for SQL Server can be accomplished natively using SQL Server Integration Services.  That can be scheduled using SQL Server Agent.

If you have a linked-server setup between the prod server and the reporting server, you could use it to truncate the target table(s), and repopulate them via the linked server.  I would schedule this at the reporting server end.

There are a large number of commercial and open-source products that can be used for ETL, unfortunately we frown upon creating "shopping lists" of products since those lists are subject to going out of date extremely quickly.

It's vastly simpler to setup this kind of scenario if you have two separate databases on the same server. Simply create a SQL Server Agent job that truncates the destination table, then does something like INSERT INTO reportdb.dbo.tablename SELECT * FROM proddb.dbo.tablename.  Don't get me wrong, there are a lot of subtle corner-cases around doing this on a single server, not least of which would be evaluating the impact of running the reporting workload on the same server as your transactional production workload.


Upgrade my server or migrate it to a new server I have a two servers, one is application server that runs with Windows Server 2003 and one SQL server runs with SQL 2008 and windows server 2003 OS. I would like to upgrade the both servers' OS to Windows Server 2012 and the SQL server to use SQL 2016.

My understanding is that I can just upgrade the OS and the SQL server, but my IT said we need to buy new servers with new OS and new SQL server version and then migrate from the old to new ones.

Just ask which way is better?

Thank you

There's a good chance this question will be closed due to 'primarily opinion-based' and a lot depends on your individual circumstances, but here's mine...

I think you'll be better off going with fresh new servers with the latest OS and Sql Server versions you can tolerate.

You'll be able to do side by side testing between the old current environment and the new one in a more leisurely pace and minimize the risk of things going south due to a buggy upgrade.

Before you make your decision, I'd like to share an article by Michael K. Campbell entitled Reasons to Avoid SQL Server In-Place Upgrades.  Following are his arguments against in-place upgrades. (I'm basically copying the entire article because I think he's 'spot-on'.)

Gremlins

  Call me nuts, paranoid, or just plain crazy, but I simply don't trust
  Windows or SQL Server installations. Yes, most of the time, they go
  completely as expected. But upgrades commonly bump into ugly problems.
  And if we're talking about production workloads, running into one of
  the random problems that can crop up when trying to run an upgrade on
  a production server can be insanely expensive—not just in terms of
  additional downtime, but also potentially in terms of your
  credibility.

  Furthermore, even if you want to argue that an in-place upgrade where
  you reformat the server back to bare metal, spin up a new OS, and then
  install SQL Server (the new version) over the top of your old hardware
  (after grabbing db backups, etc.) can work, I'll still argue against
  such an approach. First of all, I've seen too many strange or weird
  cases where the installation of either Windows Server or SQL Server
  just doesn't take correctly. Yes, those instances are increasingly
  rare. But, when we're talking about a production workload, I almost
  never find the potential risk to be acceptable. Furthermore, even if
  you delude yourself into thinking that you can practice or rehearse
  the upgrade process, you're doing that in a controlled environment and
  not against the actual hardware or metal where a driver problem or
  issue might crop up.

  Instead, I find that it's much easier and effective to stand up a new
  replacement host, configure it without any stress or duress (i.e.,
  without worrying about a ticking clock counting down to when you need
  to be back online or running), and double-check all best practices and
  security configurations, and the like. Then, you can just copy
  security information (such as logins, etc.) on to the server and load
  on copies of your databases (i.e., via backups) to then start pointing
  application servers at this old/stale data to begin acceptance
  testing. Then, once you're sure everything is working correctly, you
  can just schedule a time to take the current production server down
  and switch over to the new server. And, if something goes wrong during
  that transition (as long as you haven't started writing data to the
  new location), then you can always abort your migration and switch
  back to the old server and try again another time. Whereas, if you try
  an in-place upgrade and something goes wrong, then, well, you're
  screwed and are stuck scrambling to get something back up and running.

Artifacts

  If we assume that gremlins represent show-stopper or fatal problems
  that completely thwart upgrades, then what about something even worse?
  Like, something that fails to upgrade or install correctly as part of
  your upgrade—but which you don't detect right away. Then, imagine the
  cost (in terms of pain/manageability) of having to workaround that
  issue more or less indefinitely. While such artifacts are rare, the
  costs they impose ensure that they're something you want to avoid like
  the plague. And while you CAN miss these kinds of issues or problems
  when you stand-up a brand new server to replace your old server, my
  experience is that you have much more time and ability to test and
  interact with this server (and hopefully, therefore, find such
  problems or issues) as opposed to being in a serious hurry to get core
  functionality up when doing an in-place upgrade.

Missing out on Hardware Improvements

  If you're upgrading to a new version of SQL Server, it's likely been
  about 2-3 years since your previous host was provisioned. Hardware
  capabilities have, effectively, doubled in that time. And the size and
  demands of your workloads have likely increased over that same time—so
  attempting to upgrade on the same/old hardware can and will work (each
  new version of SQL Server usually picks up around a 15-20 percent
  performance boost due to increased efficiencies in the underlying
  engine)—but just doesn't make as much sense as upgrading to new
  hardware.

  Of course, while that argument makes instant sense to tech heads, it
  doesn't always make great sense to bean-counters (i.e., if your CIO or
  manager comes from a background more related to bean counting. or, if
  funds are sparse, then hardware benefits and improvements alone might
  not be enough to justify). Still, I'll always argue that disruptions
  caused by anything listed above also come with costs (which can
  sometimes be unacceptably costly in terms of negative user
  interactions or linger maintenance costs), and that new hardware is
  almost always worth it. Besides, when you take this approach, it's not
  like you throw away your old production hosts when you're done. The
  old production hosts can easily be repurposed for other tasks (for
  other workloads) or can be reused as staging or dev servers, if
  they're still in decent-enough working order.

Testing

  Unless you're just assuming that you can do an in-place upgrade and
  everything will work—which is just plain dumb—then you're going to
  need to spin up some sort of additional host or server to act as a
  testing server that you can use to see how your applications behave
  when they've been upgraded.

  In other words, if you're going to go through this effort anyhow (and
  you really would be stupid not to), then WHY test this on anything BUT
  the new hardware where you're going to actually be hosting your
  databases going forward? In other words, with an in-place upgrade,
  you're going to have to go 'fake' an upgrade/testing environment, and
  then, you'll just assume that everything you tested and validated
  there will work on your current/production hardware after you
  magically manage to upgrade that hardware without any issues. Whereas,
  if you perform a side-by-side upgrade, not only are you removing the
  guess work, but you can also actually put your new hardware under load
  (in many cases) to fully verify how it will behave.

Downtime

  Another great benefit of side-by-side upgrades is that you can
  decrease downtime. In fact, with enough preparation and staging (along
  with rehearsals when/where needed), you can actually upgrade in 5
  minutes or less. With in-place upgrades, you're forced to wait while a
  Wizard churns along and upgrades various components, databases,
  binaries, and the like. But with a side-by-side migration, you can
  verify that the new server is configured and working as needed, then
  load copies of recent FULL backups on to the box, restore (but not
  recover them), and then just keep applying T-Logs through the day (or
  use DIFFs + T-Log backups if that makes more sense). Then, you've got
  a warm spare configuration so that when your maintenance window
  arrives, you can just shut down the current production host, take
  tail-of-the log backups, and then apply them to your new server and
  then spin up your applications (with connection strings pointing at
  the new server) after recovering the database. All in all, very easy
  to handle, very easy to practice and rehearse, and very easy to pull
  off with minimal down-time.


Add Postgresql server to postgres-common I've created postgresql cluster using initdb. Now I can't use it with common tools.
Can i somehow add this cluster or if no, how can i delete it to create new one with pg_createcluster. I know there's a DROP SERVER command but it requires a name and i don't know how to look it up.

Plain initdb creates a PostgreSQL cluster in the normal fashion, with all files contained in a single directory. Consequently, it is very easy to get rid of that new cluster: just delete the created directory and all files in it.


Using case and group by in select statement not working I'm using a case and group by in a select statement and I would like for the case field name (item number) to not be apart of the group by clause statement. I've calculated the invoice weight as well as the shipping weight. This is what I have thus far. The issue is when the calculation is done with the case and group by on the item number, providing multiple rows however, I would like only one row.

    SELECT TOP 10 S.SOPNUMBE, CONCAT(RTRIM(LTRIM(S.CUSTNAME)) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS1]))) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS3]))))AS CUSTNAME,

            S.[CSTPONBR],S.DOCDATE, CAST(round(Sum(QUANTITY),2) as numeric(12,0))TotalOnInvoice,  (CASE

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-KILOM-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 30 ) as decimal(10,0))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-20.0K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 20 ) as decimal(10,2))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-2.25K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 2.25 ) as decimal(10,2))as float)

            ELSE CAST(cast(Sum([ITEMSHWT] * Quantity) as decimal(10,0))as float)/100

            END) AS TotalShippingWeight

            FROM [TWCL].[dbo].[SOP30200] S inner JOIN twcl.dbo.SOP30300 P ON S.SOPNUMBE=P.SOPNUMBE inner join [TWCL].[dbo].[IV00101] I on

            P.ITEMNMBR= I.ITEMNMBR WHERE CSTPONBR='1343231 '

            GROUP BY S.SOPNUMBE, S.CUSTNAME, S.[CSTPONBR], S.DOCDATE, S.[ADDRESS3], S.ADDRESS1,I.ITEMNMBR

This is the outcome I'm receiving now

This is the outcome I would like to receive.

New Query

    SELECT TOP 10 S.SOPNUMBE, CONCAT(RTRIM(LTRIM(S.CUSTNAME)) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS1]))) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS3]))))AS CUSTNAME,

            S.[CSTPONBR],S.DOCDATE, CAST(round(Sum(QUANTITY),2) as numeric(12,0))TotalOnInvoice, SUM (CASE

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-KILOM-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 30 ) as decimal(10,0))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-20.0K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 20 ) as decimal(10,2))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-2.25K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 2.25 ) as decimal(10,2))as float)

            ELSE CAST(cast(Sum([ITEMSHWT] * Quantity) as decimal(10,0))as float)/100

            END) AS TotalShippingWeight

            FROM [TWCL].[dbo].[SOP30200] S inner JOIN twcl.dbo.SOP30300 P ON S.SOPNUMBE=P.SOPNUMBE inner join [TWCL].[dbo].[IV00101] I on

            P.ITEMNMBR= I.ITEMNMBR WHERE CSTPONBR='1343231 '

            GROUP BY S.SOPNUMBE, S.CUSTNAME, S.[CSTPONBR], S.DOCDATE, S.[ADDRESS3], S.ADDRESS1,I.ITEMNMBR

Error message:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Look at this demo code :

125342', 'Some Fake Name', 1343632, GETDATE(), 3, 'FG-PD-TJ-PQ-20.0K-FORN'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 11, 'FG-PD-TJ-PQ-2.25K-FORN'),
('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 2, 'FG-PD-TJ-PQ-2.25K-FORN'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 7, 'FG-PD-TJ-PQ-2.25K-FORN'),
('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 2, 'Other'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 3, 'Other')

select SOPNUMBE, CUSTNAME, CUSTNBR, DOCDATE,
SUM(QUANTITY) "TotalOnInvoce",
sum(Case when ITEMNBR='FG-PD-TJ-PQ-KILOM-FORN' then QUANTITY*30
when ITEMNBR='FG-PD-TJ-PQ-20.0K-FORN' then QUANTITY*20
when ITEMNBR='FG-PD-TJ-PQ-2.25K-FORN' then QUANTITY *2.5
end) as "TotalShippingWeight"
from #demo
group by SOPNUMBE, CUSTNAME, CUSTNBR, DOCDATE

drop table #demo

I hope this will help you.


Get-SqlAgentJobHistory Invoked By I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account.
I'm running this cmd:

 Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddMinutes(-15))

I want to exclude these:

InstanceID       : 146788
SqlMessageID     : 0
Message          : The job failed.  The Job was ***invoked by User domain\user***.  The last step to run was step 3 (packagename Pkg).
StepID           : 0
StepName         : (Job outcome)

but include these

   InstanceID       : 146789
  SqlMessageID     : 0
Message          : The job failed.  The Job was ***invoked by User domain\serviceaccount***.  The last step to run was step 3 (packagename Pkg).
StepID           : 0
StepName         : (Job outcome)

Try filtering returned objects based on the message text.

Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddMinutes(-15)) | Where-Object {$_.message -Like "*invoked by User domain\serviceaccount*"}


Execute DDL statements from a query in stored procedure I would like to do this in a stored procedure:

Query a specific database, dump results in a table
The results contain DDL statements such as alter, create, IF exists...
Execute the results from step 2 as if executing them in a query window via SSMS

Example of step 2 results are:

Alter database.....
exec sp_addmember....
If not exists....

I'm couldn't find a good way to perform step 3, any thoughts?

Use a cursor to loop through the table, then use EXEC sys.sp_executesql for each row.

The following code relies on a temporary table, named #t, which contains various DML statements:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    cmdOrder int NOT NULL IDENTITY(1,1)
    , cmd nvarchar(max) NOT NULL
);

INSERT INTO #t (cmd)
VALUES (N'SELECT 1')
    , (N'SELECT 2');

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t
ORDER BY t.cmdOrder;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    EXEC sys.sp_executesql @cmd;
    PRINT N'';
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The output looks like:

Compound statements, like IF EXISTS (...) must occur in a single statement.  If you need to run all rows as if they were a single statement, simply concatenate the output from each row, then use sys.sp_executesql on the result, as in:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    cmdOrder int NOT NULL IDENTITY(1,1)
    , cmd nvarchar(max) NOT NULL
);

INSERT INTO #t (cmd)
VALUES (N'IF (1=2)')
    , (N'PRINT N''yes''')
    , (N'ELSE')
    , (N'PRINT N''no''');

DECLARE @line nvarchar(max);
DECLARE @cmd nvarchar(max) = '';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t
ORDER BY t.cmdOrder;
OPEN cur;
FETCH NEXT FROM cur INTO @line;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = @cmd + CHAR(13) + CHAR(10) + @line;
    FETCH NEXT FROM cur INTO @line;
END
CLOSE cur;
DEALLOCATE cur;
EXEC sys.sp_executesql @cmd;

The output from the above code:

no

If you are allergic to cursors, you could use a set-based approach, such as:

DECLARE @cmd nvarchar(max) = '';
SELECT @cmd = CASE WHEN @cmd = '' THEN '' ELSE @cmd END + CHAR(13) + CHAR(10) + t.cmd
FROM #t t
ORDER BY t.cmdOrder;
EXEC sys.sp_executesql @cmd;

Understand there is a slight chance that using the set-based approach with string concatenation may cause the commands to be executed in the wrong order, even though there is an ORDER BY clause.  The SQL Server query optimizer can do strange things under the hood if it thinks the query will perform faster.


Data modeling, double foreign key problem I have a certain problem, while creating logical/relational model of database. I get duplicate foreign key in a table. This table is referencing 2 other tables, thorugh identyfing relationship, which reference this particular key and they have to be primary keys because they are cross-reference tables.
Something like this:

How can i fix this so i don't have duplicate keys on PARAM_VAL: id_quali1 and id_quali?

Like @J.D. mentions in his comment it is not obvious what problem you are trying to solve. If I were to guess PARAM_VAL could be consructed as:
CREATE TABLE param_val
( id_quali ... NOT NULL
, id_param ... NOT NULL
, id_cand ... NOT NULL
, PRIMARY KEY (id_quali, id_param, id_cand) -- I guess
, FOREIGN KEY (id_quali, id_param)
      REFERENCES param_weight(id_quali, id_param)
, FOREIGN KEY (id_quali, id_cand)
      REFERENCES quali_participation(id_quali, id_cand)
);


Select one column with same value and check another column's value I have a table with following structure (rest of the columns omitted for brevity) :

+------------+-------+
 | Cust_ID    | Status|
 +------------+-------+
 | 312        | A     |
 | 312        | D     |
 | 312        | D     |
 | 345        | A     |
 | 345        | D     |
 | 345        | A     |
 | 678        | D     |
 | 678        | D     |
 | 678        | D     |
 +------------+-------+

We can observe that same Cust_IDs have multiple Status in different rows.
Now I want to pick Cust_IDs for which all Status row is D. So in the above case, the Cust_ID 678 should be listed.

Can anybody help me out in writing such query?

I only use T-SQL, but I don't think anything used below is specific to SSMS:

SELECT DISTINCT Cust_ID
FROM Tbl t
WHERE
    Status = 'D'
AND NOT EXISTS (SELECT * FROM Tbl WHERE Status <> 'D' AND Cust_ID = t.Cust_ID)


Which driver should I choose for ODBC connect from Windows 10 => SQL Server 2014 What driver to use for ODBC connection from Windows 10 host to SQL Server 2014?
I have a choice of 3 SQL Server clients:

ODBC Client 11
SQL Server
SQL Server Native Client.

I've tried the ODBC client and the native client they both seem to work.

Is there any reason to prefer one over the others?

Found this post after doing some research on SQL ODBC drivers for a business user today and wanted to clarify. The SQL native client is not supported beyond 2012. The new hotness (in your case) is the MS ODBC driver 11 for SQL server. However, both SNAC11 and ODBC11 should work just fine for you.

MS - "ODBC Driver 11 for SQL Server contains new features as well as all the features that shipped with ODBC in SQL Server 2012 Native Client."

The "SQL Server" driver in ODBC drivers (SNAC6) is obviously very very old

https://technet.microsoft.com/en-us/library/ms131321(v=sql.120).aspx


How to simplify schema for multi database / hundreds of millions of rows data set We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month.  The people that developed our system started separating the data into months, however they did this by creating an entirely new database with that month name (e.g. jan2001, mar2009, aug2012), and one table in each database named "detail".  Each month a cronjob kicks off and spins up and entirely new database, with the table "detail", and the application starts writing the data to said table.

At this point the combined number of rows in all the "detail" tables is 448,231,722.  This data is only used for analytics purposes at this point, so the speed of the query returning the data does not need to be instantaneous.  One assumption we have is that the data will never be deleted.

I am wondering if you could suggest a better method of condensing or consolidating the data into a more manageable schema?

When it comes to databases, less is more.

You are doing the right thing by archiving off old data as this will speed up the performance and reduce the maintenance time of the active database.

The archived data needs to be considered. Keeping it in a separate database instance will be beneficial because backups/restoration and other maintenance activities will be completed separately - reducing the effect on the live data.

You should consider keeping the archived data on a physically different disk to the live data - this will avoid conflicts if both are being used at the same time. Also consider the type of disk this the archive is on as this has cost implications - does it really need to be on a raid type array or is a single disk and backup tape/dvd all that is needed? Storage may be relatively cheap, but it is still a cost in $, IO, time and network.

Next take a look at the archived data - do you really need to keep all the records? do you really need to keep all the columns of data? Would changing the structure of the data result in a smaller disk footprint/faster read query time? Can you summarize the data? Archived data for data mining does not always need to have the same structure as live data.

Keeping separate months data in separate databases may be a good way to go, but there other options that may make the analysis easier/quicker. Your options here include keeping the data in one database instance, but with table per month. Another is to have one set of archive tables and use table partitioning (read the manuals).


Actual rows read in a table scan is multiplied by the number of threads used for the scan I'm running into quite a strange problem. I'm running the same script to generate data and do some matching later on, on an older 2008R2 instance. The last query (an UPDATE) does a single table scan and returns all of the 250.000 rows, whereas on a newer 2017 instance, the table is scanned in parallel and each of the 4 threads reads 250.000 rows, and returns 1 million "actual rows read".

I changed the compatibility mode to 2008 in my 2017 instance and the actuals stayed the same, at 1.000.000.

Is there any valid reason for why this would happen or does this seem like it should be a Connect item?

Plans contain the same operators, but one of them does the scan in parallel and instead of splitting the 250.000 rows per each of the 4 threads (and only read 62.500 rows on each thread) all the threads read 250k each *4 = 1.000.000

Both execution plans can be found at pastetheplan:

2017 version here
2008R2 version here

Also, the full script I was running can be found down below:

create table #targets (id int identity(1,1), start_point int, end_point int, refference_type_id int, bla1 int, bla2 int, bla3 int, bla4 int, bla5 int, bla6 int, bla7 int, bla8 int, bla9 bit, assignedTouch varchar(10));

;with cte as (
    select
        1 sp
        , abs(checksum(newid())) % 11 + 2 ep
        , 1 rn
        , abs(checksum(newid())) % 3 b
        , abs(checksum(newid())) % 3 c
        , abs(checksum(newid())) % 3 d
        , abs(checksum(newid())) % 3 e
        , abs(checksum(newid())) % 3 f
        , abs(checksum(newid())) % 3 g
        , abs(checksum(newid())) % 3 g2
        , abs(checksum(newid())) % 3 h
        , abs(checksum(newid())) % 3 x3
    union all
    select
        sp
        , abs(checksum(newid())) % 11 + 2 ep
        , rn + 1
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
    from cte
    where rn < 250000)
insert into #targets
select *
    , 'Unassigned' [Default State]
from cte
option (maxrecursion 0)

select top 250 *
    , char(abs(checksum(newid())) % 85 + 65) [class]
into #matching
from #targets
where end_point in ( 11, 14, 22, 33 )
order by abs(checksum(newid())) % 13

update #matching
set bla8 = case
            when bla1 - bla2 > 0
                then NULL
            else bla8
        end
    , bla7 = case
            when bla2 - bla3 > 0
                then NULL
            else bla7
        end
    , bla6 = case
            when bla4 - bla5 > 0
                then NULL
            else bla6
        end

create nonclustered index nc_assignedTouch on #targets (assignedTouch);

update t
set assignedTouch = m.class
from #targets t
    inner join #matching m
        on t.start_point = isnull(m.start_point, t.start_point)
            and t.bla1 = isnull(m.bla1, t.bla1)
            and t.bla2 = isnull(m.bla2, t.bla2)
            and t.bla3 = isnull(m.bla3, t.bla3)
            and t.bla4 = isnull(m.bla4, t.bla4)
            and t.bla5 = isnull(m.bla5, t.bla5)
            and t.bla6 = isnull(m.bla6, t.bla6)
            and t.bla7 = isnull(m.bla7, t.bla7)
            and t.bla8 = isnull(m.bla8, t.bla8)
            and t.bla9 = isnull(m.bla9, t.bla9)
where t.assignedTouch = 'Unassigned';

sp_configure info:

Parallel heap scan

You might be expecting distribution among parallel threads as in the following toy example:

SELECT TOP (5 * 1000 * 1000)
    n = ROW_NUMBER() OVER (ORDER BY @@SPID)
INTO #n
FROM sys.columns AS C
CROSS JOIN sys.columns AS C2
CROSS JOIN sys.columns AS C3;

SELECT COUNT_BIG(*)
FROM #n AS N
GROUP BY N.n % 10
OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE'));

In that plan, the heap table is indeed parallel scanned, with all threads coordinating to share the work of reading the whole table:

or in SSMS view:

Your case

But this is not the arrangement in your uploaded plan:

The heap scan is on the inner side of a nested loops join*, so each thread runs a serial copy of the inner side, meaning the there are DOP (degree of parallelism) independent copies of the Table Spool and Table Scan.

At DOP four, this means there are four spools and four scans, as evidenced by the Number of Executions = 4 on the table scan. Indeed, the heap table is fully scanned four times (once per thread), giving 250,000 * 4 = 1,000,000 rows. The lazy spool caches the result of the scan per thread.

So the difference is that your parallel scan is four serial scans in parallel, rather than four threads cooperating to parallel scan the heap once (as in the toy example above).

It can be challenging to conceptualize the difference, but it is crucial. Once you see the branch between the two exchanges as DOP separate serial plans, it becomes easier to decode.

Of course the plan is wildly inefficient, and the spool adds little value. Notice the join predicate is stuck at the nested loops join, rather than being pushed down the inner side (making the join an apply). This is due to the complex join conditions involving ISNULL.

You might get a slightly better-performing plan by making your nc_assignedTouch index clustered rather than nonclustered, but the bulk of the work would still be happening at the join, and the improvement would almost certainly be minimal. A query rewrite is probably necessary here. Ask a follow-up question if you want assistance expressing the query in a more execution-friendly way.

For more background on the parallelism aspects, see my article Understanding and Using Parallelism in SQL Server.

Footnotes

* There is one general exception to this, where one can see a true co-operative parallel scan (and exchanges) on the inner side of a nested loops join: The outer input must be guaranteed to produce at most one row, and the loop join cannot have any correlated parameters (outer references). Under these conditions, the execution engine will allow parallelism on the inner side because it will always produce correct results.

You may also notice that operators below an Eager Spool on the inner side of a parallel nested loops join also execute only once. There are still DOP copies of these operators, but the runtime behaviour is that only one thread builds a shared indexed structure, which is then used by all instances of the Eager Spool. I do apologise that all this is so complicated.


Grabbing SQL Dump of Master DB Without Downtime I'm curious whether downtime will be necessary to grab a SQL dump of my master database.

Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that is being replicated onto slave. All tables in that database are InnoDB. This is the command I want to run:

mysqldump --master-data --single-transaction --hex-blob dbname | gzip > dbname.sql.gz

I'm running MySQL 5.1 and here is a redacted version of my my.cnf file:

[mysqld]
default-storage-engine=InnoDB
character-set-server=UTF8
lower_case_table_names=1
transaction_isolation=READ-COMMITTED
wait_timeout=86400
interactive_timeout=3600
delayed_insert_timeout=10000
connect_timeout=100000
max_connections=750
max_connect_errors=1000
back_log=50
max_allowed_packet=1G
max_heap_table_size=64M
tmp_table_size=64M
bulk_insert_buffer_size=128M
innodb_buffer_pool_size=10000M
innodb_data_file_path=ibdata1:256M:autoextend
innodb_file_per_table=1
innodb_additional_mem_pool_size=32M
innodb_log_file_size=1G
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=240
innodb_flush_log_at_trx_commit=2
innodb_open_files=8192
innodb_support_xa=ON
thread_cache_size=500
expire_logs_days=2
server-id=1
log_bin=1
binlog_format=MIXED
sync_binlog=0

[mysqldump]
max_allowed_packet=128M

Am I good without downtime or not? I'm concerned about a possible read lock being placed on tables.

When seeking a dump which provides binlog position you will instruct MySQL to call Flush Tables With Read Lock. The acquisition this lock can block. This where you could impact the system. If you have long running transactions you may see problems but you can check this before starting the dump. Your command looks fine to me.


How to switch from MariaDB to MySQL in WAMP server? I installed wamp server on my PC and by default wamp server is connected to MariaDB as showed below

How can i switch to MySQL?
Thank you in advance for your help.

To pick MySQL or MariaDB, right click on the wampmanager icon in the system tray and you should see this this menu

Please Select Components like I did please check the 2nd Screenshot