Why does InnoDB corrupt tables upon recovery? When setting

innodb_fast_shutdown=0

MySQL drops all incomplete transactions before shutting down/crash. The default value is 1, which is understandable to preserve incomplete transactions.

However, in the recovery mode (e.g., innodb_force_recovery = 6), MySQL treats all incomplete transactions as committed. This obviously damages the table as the transactions are incomplete.

What is the purpose of preserving incomplete transactions when it damages the whole table?

If it is necessary in some cases, why doesn't innodb_force_recovery have an option to simply drop all incomplete transactions (when they cannot be committed) like innodb_fast_shutdown=0 to save the table? Or am I missing something?

innodb_fast_shutdown controls the change buffer, not the redo log. No matter what value of innodb_fast_shutdown is, if a transaction wasn't committed at the time of shutdown it won't survive the restart.

If a transaction was committed then whether it's going to persist depends on  innodb_flush_log_at_trx_commit. If it's the default 1 the transaction will persist. If != 1, may not persist.

Now, innodb_force_recovery. Here's what possible. When you run an UPDATE in a transaction, the new record is written to a page (in the buffer pool, the page becomes dirty). Later on, the data page can be flushed to disk even if the transaction isn't committed yet. When you have to use innodb_force_recovery that means you are ready to sacrifice data consistency (is it what you call a damaged table?) just to get the some data back. With innodb_force_recovery=6 InnoDB tries to salvage as much data as possible. It cannot complete the crash recovery process, but the new record is already written to the page - why not make it available to a user?


Huge ASPState database log We're using SqlServer to store users session state of our webapps. We've recently noticed the ASPState.log_ldf file has reached 50GB.

Is it safe to put the web apps down and then drop and recreate the ASPState DB? I don't see the point of having backups of this DB so we would also set simple recovery mode in order to avoid this problem in the future and spare storage space.

Set Simple and shrink the .ldf without having to re-create...I've done it many times over the years, although its been awhile


Master-slave replication doesn't work on PostgreSQL 9.6 I tried to create a master/slave configuration with two servers and Postgres 9.6

I modified master and slave postgresql.conf in /etc/postgresql/9.6/main
Same for master and slave:

data_directory = '/srv/postgresql' # Because we wanted to store data elsewhere
wal_level = replica
synchronous_commit = local
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave001'

master-specific :

listen_addresses = ' <slave_ip>, localhost'

slave-specific :

listen_addresses = ' <master_ip>, localhost'

I then added a line on pg_hba.conf on master :

host    replication     rep     <slave_ip>/32  md5

And same on slave :

host    replication     rep     <master_ip>/32  md5

And then I restarted each postgresql services

I created a role rep on master as you can see below :

I removed existing /var/lib/postgresql/9.6/main repository on slave and then executed those commands :

service postgresql stop
pg_basebackup -D /var/lib/postgresql/9.6/main -h <master_ip> -U rep

And got this answer:

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

I then created a recovery.conf file on /var/lib/postgresql/9.6/main on slave

standby_mode = 'on'
primary_conninfo = 'host=<master_ip> port=5432 user=rep
password=<rep_password> application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

I then restarted slave postgresql and got those logs:

LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
[unknown]@[unknown] LOG:  incomplete startup packet

Last one is a bit strange but it seems to be 'normal'

but replication seems not to work, as if I create a table on master, it's not replicated

And command :
    psql -x -c "select * from pg_stat_replication;"

Gives me (0 rows)

I followed many tutorials but every time it's the same result.
My master db got some data (around 10 Go), maybe it's due to that?
What step did I miss? Is it due to an odd interaction with the fact that my data_dir is on /srv ?

My servers are on debian 9

Thanks for help, and don't hesitate to ask me for more details if needed! As I'm a beginner with Postgres, maybe I missed important informations

You told it look for the data in '/srv/postgresql'.  But then you told pg_basebackup to clone the data into '/var/lib/postgresql/9.6/main', not '/srv/postgresql'.

When you started postgresql up again, you (apparently) started up the database instance whose data is still in '/srv/postgresql', which is not the clone you had just made.

Also, turning "archive_mode" on but then providing it with a dummy "archive_command" is not helpful and can only cause confusion and mis-reported errors.  And "pg_basebackup" has a -R option for writing out the recovery.conf itself, it would probably be best to use that.


Oracle database not responding We have a Java application that uses a connection pool with Oracle database, during the week these conections are frequently refreshed (closed and opened again), the problem is at some point (after two or three days) the application is unable to acquire new connections, it get stuck on that line:

Runner004" prio=10 tid=0x00007f4b2c119800 nid=0x12b6 runnable [0x00007f4b00151000]
   java.lang.Thread.State: RUNNABLE
        at oracle.jdbc.driver.T2CConnection.t2cCreateState(Native Method)
        at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:530)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
        at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:54)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at TesteDB.getConnection(TesteDB.java:238)
        at TesteDB$Runner.run(TesteDB.java:285)

Version information:

Oracle Instant Client 12.1.0.2.0 on Linux.
The database version is 11.2.0 or 10.2.0.

Troubleshooting we did, when the database stopped to respond:

Connect through SQLPLUS doesn't respond anything. It just waits forever.
TNSPING doesn't respond anything.
lsnrctl status doesn't respond anything.
Nothing on alert.log
We rebooted the listener, same problem.
We rebooted the database service, it started to work.

We have absolutely no idea what we should look into, any suggestions?

I had a similar issue with an off the shelf three tier application where the app server would make a connection to the database when the user logged on.  The user activity was typically to log on in the morning and leave the app open on the desktop all day even if they did nothing for long periods.

The app server was not configured to use a shared pool and would commonly hit the maximum number of sessions after two or three days.  As you found the only resolution was to restart the database every night which is crazy unless the app owners say you can't change anything but you can restart it.  You may wish to verify if you are using shared connections  (pooling) and re examine your code to make sure connections are being dropped.

Here are some things you can check to see if this is a similar situation.  This answer here is also helpful.

Log onto the database and run this from sqlplus with a privileged account
--this gives the maximum amount of sessions

SELECT name, value
  FROM v$parameter
 WHERE name = 'sessions'

--number of users currently connected even if they are not doing anything

SELECT COUNT(*)
  FROM v$session;

I created this procedure on the database and scheduled it for every five or ten minutes.  Then you can examine the file and see if you are hitting the maximum number of sessions.  To use this you must also create a directory PROCESS_LOG_DIR where the user running this has at least write permissions on.

CREATE OR REPLACE PROCEDURE COUNT_PROCESSES
IS
   f                    UTL_FILE.file_type;
   v_processes          NUMBER (10);
   v_current_logons     NUMBER (10);
   v_current_sessions   NUMBER (10);
   v_highwater          NUMBER (10);
BEGIN
   SELECT   COUNT ( * ) INTO v_processes FROM v$process;

   SELECT   VALUE
     INTO   v_current_logons
     FROM   v$sysstat
    WHERE   name = 'logons current';

   SELECT   sessions_current, sessions_highwater
     INTO   v_current_sessions, v_highwater
     FROM   v$license;

   f := UTL_FILE.fopen ('PROCESS_LOG_DIR', 'Process.txt', 'a');

   UTL_FILE.put_line (
      f,
         TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
      || ','
      || v_processes
      || ','
      || v_current_logons
      || ','
      || v_current_sessions
      || ','
      || v_highwater
   );
   UTL_FILE.fclose (f);
EXCEPTION
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error (
         -20000,
         'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.'
      );
END;


SQL Server Report Manager 404 using HTTPS OK. I have scoured the Internet for information on trying to fix this and found nothing that deals with my situation.
I started here, that lead me to here, that lead me to looking at netsh commands for HTTP.

Understand: I have no problem what-so-ever accessing the Report Manager or Web Service using HTTP/80.

My problem is strictly getting HTTPS/443 to work and open up the exact same page that HTTP/80 uses. All I get is HTTP Error 404.

Build number for SQL and SSRS: 10.50.4270.0
My certificate is installed on the server
Windows Firewall is disabled, but I am just trying to bring HTTPS up locally on the server
I have configured everything as instructed by Microsoft in RSCM.
SSL configuration has been done through RSCM, and using that URL to attempt access
I have verified in netsh that the SSL certificate is
binded on IP:port 443.
The registry keys under
HKLM:\SYSTEM\CurrentControlSet\services\HTTP\Parameters\SslBindingInfo
are present for 0.0.0.0:443.
In netsh show iplisten was not showing anything, so I add iplisten ipaddress=0.0.0.0, but that did not affect anything
Within my rsreportserver.config the SecureConnectionLevel is set to value="0"

I am stuck, what am I missing here to do or check?

EDIT
I just completed a fresh install on a brand new server, same result.

The problem has to do with the certifcate being used with SSRS. If your certificate is created with hostname.whatever.com, you can only access the Report URLs through that path, https: //hostname.whatever.com/Reports. If you were to try https:// 10.xxx.xxx.xxx/Reports (where the IP is the one on the server) you will receive the 404 errors.

So the fix for my situation:
I found the friendly name used to generate the certifcate on the server was dorked up. I am in the process of getting that fixed to be the correct fully qualifed DNS name of the server in use. I found this out by installing SQL Server Report Server on a server that had the certificate created with the correct name of the server being used.

Once I have that certificate corrected and verify will simply mark this as the correct answer.

EDIT
Well another problem came up in trying to change the certificate. The admins of the server removed the certificate before I could properly remove it from RSCM. So now it will not bind to the new certificate because it already sees one binded. There is really no clean, easy way of fixing this since you don't have access to IIS (SSRS uses HTTP.sys). So following this KB, you have to go into NETSH to remove the binding(s). Which you will note in that KB article the link to NETSH does not provide much info, so use this one.
I had to use the two following commands to delete the bindings on Window Server 2008 R2:
WARNING: I am on a single, standalone server. If you are in a server farm or have multiple instances of SSRS running, be very careful with which SSL binding you delete.

This is after getting to the "netsh http>" prompt:
delete sslcert ipport=0.0.0.0.:443
delete sslcert ipport=[::]:443

I did that and then went into RSCM and selected the new certificate that was installed on teh server. That is not the end though, for some reason it kept removing the binding and showing "unknown" in the link. I stopped SSRS and then opened up the rsreportserver.config file and found the old ("bad") binding still was showing in the config file. I removed that went back into RSCM and started the service and it seemed to fix it.


How to programmatically find out where LocalDb is installed I'm writing a plugin for Cake Build tool where I need to start a LocalDB instance. But this code will be executed on different machines and LocalDB can be installed in different folders depending on the version of SQL Server installed in the build machine.

I know at least 3 paths possible:

c:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe
C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe

Though I'm not absolutely sure about the last path. And I can't install SQL Server 2016 on my machine, so can't confirm if

c:\Program Files\Microsoft SQL Server\140\Tools\Binn\SqlLocalDB.exe

would the correct guess.

I can iterate through a list of options and pick the one that works.

Are there any other possible folder where SqlLocalDb.exe can be installed?
Or is there a registry value somewhere that I can check?

Checking from the registry may not work all the time becasue if user uninstall the localDb then registry entries may still exist.

Here is the function I am using to identify the localDB installation from command line -

internal static bool IsLocalDBInstalled()
{
    // Start the child process.
    Process p = new Process();
    // Redirect the output stream of the child process.
    p.StartInfo.UseShellExecute = false;
    p.StartInfo.RedirectStandardOutput = true;
    p.StartInfo.FileName = "cmd.exe";
    p.StartInfo.Arguments = "/C sqllocaldb info";
    p.StartInfo.CreateNoWindow = true;
    p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
    p.Start();
    // Do not wait for the child process to exit before
    // reading to the end of its redirected stream.
    // p.WaitForExit();
    // Read the output stream first and then wait.
    string sOutput = p.StandardOutput.ReadToEnd();
    p.WaitForExit();

    //If LocalDb is not installed then it will return that 'sqllocaldb' is not recognized as an internal or external command operable program or batch file.
    if (sOutput == null || sOutput.Trim().Length == 0 || sOutput.Contains("not recognized"))
        return false;
    if (sOutput.ToLower().Contains("mssqllocaldb")) //This is a defualt instance in local DB
        return true;
    return false;
}


psql: Run commands in batch mode Consider a .sql file with a few selects and updates. I would like to run this file from a script using the psql utility:

psql -h whatever.rds.amazonaws.com -U user dbname -f commands.sql

The problem is that I get an interactive screen with the results of the query and an (0 rows)(END), which requires pressing esc in order to go back to the normal shell.

I have tried using --echo-all and/or --single-transaction following the psql manual, with no luck.

How do I run my sql file in non-interactive mode, only displaying the results an exiting the script?

For batch operation I generally use:

psql -v ON_ERROR_STOP=1 -P pager=off --single-transaction

... however, generally psql should detect that stdout is not a terminal and automatically turn pager off if you're redirecting its stdout.


Trouble with surname "Null" and LOAD DATA INFILE I'm given source data in a tab-delimited text file where the string columns are not quoted.

Among the columns is LAST_NAME, and naturally my source data contains a person with the surname Null.  Further, some of the records have redacted names, which I would like to store as NULLs, so I can't define my column as NOT NULL.  (A sentinel value is used for the redacted names, so I can identify them.)

I am trying to import this data via LOAD DATA INFILE.

I read each source column into a variable and pass those variables through a function that looks for the sentinel values and translates them to NULL.  When this function returns a NULL value, the column gets assigned as NULL.  But when this function returns the string 'NULL', the column still gets assigned as NULL, instead of being assigned the string 'NULL'.

How can I prevent the string 'NULL' from being interpreted as a NULL value in the context of a SET clause of a LOAD DATA INFILE command?  (Assigning to a nullable varchar column.)

Update with specific data and commands:

An input data file (/tmp/data.txt); note that the fields are TAB-delimited, but I don't know how to do that here:

    SMITH   Other text is
    REDACTED        "inconsistently quoted"
    NULL    REDACTED

My commands:

    CREATE FUNCTION despair(value varchar(255))
      RETURNS varchar(255) DETERMINISTIC
      RETURN
        CASE value
        WHEN  NULL  THEN 'NULL'
        WHEN 'NULL' THEN 'NULL'
        WHEN 'REDACTED' THEN NULL
        ELSE value
        END;

    CREATE TABLE `misery` (LAST_NAME varchar(50), TEXT varchar(100));

    LOAD DATA INFILE '/tmp/data.txt' INTO TABLE `misery`
      FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'
      (@name, @text) SET LAST_NAME = despair(@name), TEXT = despair(@text);

    SELECT LAST_NAME, TEXT, LENGTH(LAST_NAME), LENGTH(TEXT) FROM `misery`;

Current output.  Note that the TEXT column looks right, because quotation marks are removed as desired, but the last row's LAST_NAME is a NULL value, but should instead be the string 'NULL'.

    +-----------+-----------------------+-------------------+--------------+
    | LAST_NAME | TEXT                  | LENGTH(LAST_NAME) | LENGTH(TEXT) |
    +-----------+-----------------------+-------------------+--------------+
    | SMITH     | Other text is         |                 5 |           13 |
    | NULL      | inconsistently quoted |              NULL |           21 |
    | NULL      | NULL                  |              NULL |         NULL |
    +-----------+-----------------------+-------------------+--------------+
    3 rows in set (0.00 sec)

How can I prevent the string 'NULL' from being interpreted as a NULL value in the context of a SET clause of a LOAD DATA INFILE command? (Assigning to a nullable varchar column.)

Provide a FIELDS ENCLOSED BY from the docs

  If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

Essentially if you set FIELDS ENCLOSED BY '"' then you can provide surname "Null", as "Null", and nulls as null (no quotes).


field with multiple foreign keys I have researched this on my own but not really sure of the terms I should use and have not gotten far.  Basically I'm trying to figure out how to implements tags for a table row.  So if my table was musicFile, my tags would be artist, genre, etc....  But lets say some musicFiles would only have artist, or genre so its variable.

I think I would need a separate table for Tags but even then there would have to be foreign keys that are placed in the musicFile table and I end up at the starting point since its 1-1, meaning that its still variable.  I would not know at database creation time how many fields to dedicate to Tag foreign keys.

If a MusicFile has multiple Artists and an Artist has multiple MusicFiles then yes you need a link table between the two (as per the answer in your comment). However, if, for example, you only ever want to attach a single Genre to any given MusicFile, then you could just have a field for genre inside the MusicFile table.


Best practice to store and execute a list of queries to build a Fact table Good afternoon,

Junior DBA/Developer here:

I have a DataMart that I have to build with a bunch of Statistical information. Each entry will be a different statistic and I have 80 different queries that will be executed on top of the DW to build the Fact table.

My question is on the best way to store, maintain and execute these queries. I have thought about storing them in different .sql files, storing them all together in a table (each entry is a query), or even managing them all in the same file with a giant UNION ALL putting them all together. This seems like a nightmare if not done right, and I don't really know how to proceed in doing this cleanly.

SSIS is included as part of your SQL Server license. Depending on the size, logical grouping, and dependencies I'd break the statements apart, into their own packages consisting of Execute SQL Tasks. Name these appropriately to what they're doing! It will assist in maintenance and trouble-shooting.

Create a Master Package that uses an Execute Package Task to individually call your T-SQL execution packages. This allows clean way that anyone can see the order, or parallelism, of your process. It also gives you a single package to schedule and view native SSIS reports from.

Additionally, all of these tasks can now be stored in a single Visual Studio (SSDT) project and should ideally be stored in source control. Ensure you download the appropriate version of BI tools for SQL Server Data Tools for your server version... they have backwards compatibility issues when deploying SSIS projects.

There are plenty of resources for master package design and such... this should set you on the right path for additional binging.


Incorrect process utilization from System health extended events? I have been recently working on understanding the data or metrics being stored in system health extended events event file.

We are trying to implement the data collection of performance metrics using system health as provided here

There are reports that gives metrics like CPU UTILIZATION, other process utilization etc being collected from system health event named

scheduler_monitor_system_health_ring_buffer_recorded

I am not able to understand for few of my busy servers why the field "process_utilization" which is listed in report as SQL CPU utilization be most of time above 100. IN peak time it varies between 120-160 and because of which the report is always showing CPU above 100 even though when i checked from servers activity monitor that's never the case.

I've raised this issue in Github but seems like no fix or response.

Therefore, my question would be

How can i get accurate number for SQL CPU utilization for my servers using system health ring buffer recorded?
Report also shows counter for below 2 fields calculated per their report

  100-System_idle-process_utilization as OtherProcessUtil
  100-system_idle as SystemUtil

What are these OtherProcessUtil and SystemUtil needed/helpful for ?

I also see memory utilization every time always showing as 100. That does not seem correct as well. Has anyone noticed it?

Other tools like Idera & sentry [which i tested] do not show CPU usage above 100 % for the same servers. I did a side by side comparison for same load.

Values for disk and processor % counters that go over 100% Should be divided by the number of processors or disks present in the system.  So 100% means it is using 100% of one processor. 250% would mean it's using two and half processors. So if it's reporting 120%, see if that is the equivalent of 120 / <number of processors>.

Note that the latest change to tigertools was June 2018. You might check out sqlwatch.io as it appears to be a much more active project.

See social.msdn.microsoft.com/Forums/sqlserver/en-US/… and just change it to TOP(1) to get the current SQL CPU usage.

– Tony Hinkle


Which query performs better when searching a row where a column has maximum? Consider a simple table for example purposes:

person(name varchar, age int)

I have a task to retrieve the oldest person. Which query is better in general for such problems?

Query 1: criteria with subquery for maximum value

SELECT p.* FROM person p WHERE p.age = (SELECT MAX(age) FROM person);

Query 2: ordering and selecting first row

SELECT p.* FROM person ORDER BY age DESC LIMIT 1;

I'm currently using PostgreSQL, but the question is I think platform independent.

I'm not sure if this is as platform independent as you think but I've been doing some fairly extensive testing on this in SQL Server recently and the first query was interestingly the fastest.  This was comparing against a number of "tricks" in SQL Server including using CROSS APPLY and windowing functions. But again YMMV in PostgreSQL.

Unless I'm mistaken (I don't know PostgreSQL) your second query will actually come up faster but only because it's going to give you the age for one person.


Setting user permissions for different SQL Server schemas I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo.

I'm trying to do the following:

dbo schema functions as it would normally, has access to everything
schema1 schema has access to only schema1 objects
if a schema1 view or stored procedure accesses data in tables owned by dbo, the permissions chain appropriately
user1 has access to schema1, and nothing else; except in the case of #3

Here's what I've tried:

Create a user1 user mapped to a test login with a random password
Created a couple tables in the dbo schema with some test data in them
Created a schema1 schema
Created a schema1.get_profiles that selects from a view called schema1.profiles which accesses data in dbo.people, dbo.taglinks, and dbo.tags

However, using the following statement while logged in as user1:

EXEC get_profiles 1

results in:

The SELECT permission was denied on the object 'tags', database 'schema_test', schema 'dbo'.

I have tried WITH EXECUTE AS OWNER and can't begin to understand how "ownership chaining" is supposed to work.

I have also tried

GRANT EXECUTE ON SCHEMA::schema1 TO user1
GRANT INSERT ON SCHEMA::schema1 TO user1
GRANT SELECT ON SCHEMA::schema1 TO user1
GRANT UPDATE ON SCHEMA::schema1 TO user1
GRANT VIEW DEFINITION ON SCHEMA::schema1 TO user1

but I get the following error (despite being a user with dbo-level access):

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

What I need is user1 to be able to access the data via the stored procedures I give it, and nothing else.

Additionally, this is intended to eventually live on an existing SQL Azure database, but I'm testing against a local dummy database first.

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :


What type of SQL Server Replication Allows Modifications I have an application that uses a database for each instance of the application, one instance per client. Each client wants a database for reporting as we do not allow reporting from the production system, we use a simple script to backup and restore the database nightly for each client.

Now each client wants to modify their reporting copy of the database. They want to add tables and views to help with reporting. What type of replication would allow me to keep the data in sync from the publisher to the subscriber while also allowing the subscriber to be modified with new tables, views and sprocs? Also which type of replication would best fit this scenario of a reporting database?

I would suggest Transactional Replication.  This will be more of a "Real-Time" copy of the data than the one refresh per day that you have now.  You can add new tables into the database without affecting the replicated tables.


"Lambda" function returns type string instead of type record I am using PostgreSQL 9.5.4 and trying to create a lambda function based on this code: https://github.com/decibel/pg_lambda/blob/master/sql/lambda.sql

set client_min_messages to debug;

create or replace function lambda_record(
    code text,
    variadic inputs anyarray
) returns record language plpgsql as $code$
declare
    fname text;
    foid regprocedure;
    fargs regtype[];
    call_args text[];
    sql text;
    out record;
begin
    -- name of function
    fname := format('pg_temp.lambda_function_%s', md5(code));
    raise debug 'fname=%', fname;

    -- create function
    sql := format('create function %s%s', fname, code);
    raise debug 'sql=%', sql;
    execute sql;

    -- get function id and arguments
    foid := fname::regproc::oid;
    select proargtypes::regtype[] into fargs
        from pg_proc where oid = foid;

    call_args := array(
        select format('%L::%s', input, argtype)
            from unnest(inputs, fargs) as u(input, argtype)
    );
    sql := format(
        'select %s(%s)',
        foid::oid::regproc,
        array_to_string(call_args, ', ')
    );
    raise debug 'sql=%', sql;

    -- out is a string but should be type record
    execute sql into out using inputs;
    raise debug 'out=%', out;

    -- drop function
    sql := format('drop function %s', foid::text);
    raise debug 'sql=%', sql;
    execute sql;

    return out;
end;
$code$;

select lambda_record(
$lambda$(
    IN arg1 numeric,
    IN arg2 numeric,
    OUT out1 numeric,
    OUT out2 numeric
) returns record language sql AS $$
    select $1::numeric, $2::numeric;
$$
$lambda$, 1, 2);

The problem is that the out variable in lambda_record() returns a string instead of a record:

SET
Time: 0.539 ms
psql:/Users/ian/Desktop/lambda3.sql:55: LOG:  duration: 2.759 ms
CREATE FUNCTION
Time: 3.445 ms
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  fname=pg_temp.lambda_function_f1ee9780eb612939b06081fce4ba88cb
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=create function pg_temp.lambda_function_f1ee9780eb612939b06081fce4ba88cb(
        IN arg1 numeric,
        IN arg2 numeric
    ) returns record language sql AS $$
        select $1::numeric, $2::numeric;
    $$

psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=select pg_temp_2.lambda_function_f1ee9780eb612939b06081fce4ba88cb('1'::numeric, '2'::numeric)
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  out=("(1,2)")
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=drop function pg_temp_2.lambda_function_f1ee9780eb612939b06081fce4ba88cb(numeric,numeric)
psql:/Users/ian/Desktop/lambda3.sql:64: LOG:  duration: 7.511 ms
┌───────────────┐
│ lambda_record │
├───────────────┤
│ ("(1,2)")     │
└───────────────┘
(1 row)

Time: 7.625 ms
psql:/Users/ian/Desktop/lambda3.sql:69: LOG:  duration: 0.407 ms

For some reason the line execute sql into out using inputs sets out to a RECORD which contains a single value which is a TEXT representation of what it should be.

But it should work like this:

create or replace function pg_temp.test(in arg1 integer, in arg2 integer,
    out out1 integer, out out2 integer)
language sql as $$
    select $1, $2;
$$;
select pg_temp.test(1, 2);
CREATE FUNCTION
Time: 0.529 ms
psql:/Users/ian/Desktop/lambda3.sql:70: LOG:  duration: 0.193 ms
┌───────┐
│ test  │
├───────┤
│ (1,2) │
└───────┘
(1 row)

I've been using Postgres for a long time, but I'm relatively new to the PL/pgsql language so any would be very appreciated.

As lambda_record function returns RECORD and doesn't have OUT parameter, you must define the columns and types it will return when calling using an alias:

SELECT r.out1, r.out2
FROM lambda_record(...) AS r(out1 numeric, out2 numeric);

In this case r is the table alias, and out1/out2 the alias for the columns with the types defined (defining types is only necessary when it returns RECORD and doesn't have OUT parameters).

EDIT: even though it uses a RECORD, lambda_record still calls the function as it had only one column returned, using SELECT func_name(params...) method:

sql := format(
    'select %s(%s)',
    foid::oid::regproc,
    array_to_string(call_args, ', ')
);
raise debug 'sql=%', sql;

You could change that line to call it use SELECT * FROM func_name(params...):

sql := format(
    'select * from %s(%s)',
    foid::oid::regproc,
    array_to_string(call_args, ', ')
);
raise debug 'sql=%', sql;

That way you should already have it return the columns correctly (even for functions with a single column), and now you can call as I showed in the beginning.


Merge Replication API with SQL Server 2012 / 2014 We are using SQL Server 2008 with merge replication. Our business application uses the RMO API, which we have installed with SQL Server Feature Pack 2008 SP3 and there the package sql_rmo.msi.

Now we want use SQL Server 2012 or 2014. In SQL Server 2012 Feature Pack, I can not find such a package.

How can I install the RMO libs for SQL Server 2012 / 2014?

The RMO API is deprecated from SQL Server 2012 and onwards. Microsoft suggests using T-SQL instead.


integer variable for query string - option(optimize for variables) I have problem related to my post variable for query string (or dynamic query?).

I encountered a hurdle related to using an integer parameter. The query looks like this:

DECLARE @area NVARCHAR(255)
SET @area = 'here_is_the_name'    --i have specyfic names here
DECLARE @country NVARCHAR(255)
SET @country = 'here_is_the_country_name'     --and there
DECLARE @instance INTEGER
SET @instance = 41

DECLARE @SQLq NVARCHAR(MAX)  --declaring variable for query string
SET @SQLq ='

              SELECT
                  a.something,
                  b.somethingelse,
                  c.entirelysomethingelse,
                  c.diffrentthanthoseabove

              FROM
                  tablea a
                  LEFT JOIN tableb b ON ...
                  LEFT JOIN tablec c ON ...
                  LEFT JOIN tabled d ON ...
              ...

              WHERE
                  b.area = (@v1)  --variable 1 outside of query string
              AND
                  c.country = (@v2)   --variable 2 outside of query string
              AND
                  ((b.something = ''aaa'') OR (b.somethingelse = ''aaaa'')) --this is connected to the query so there is no problem with this part
              AND
                  a.problematic_integer_attribute != (@v3) -- problematic integer variable 3 outside of query string
              AND
                  d.something LIKE (''%@%'')  --not important in this case

              GROUP BY
              ...
              ORDER BY
              ...

--and here lies the biggest hurdle with option(optimize for variables)

OPTION(OPTIMIZE FOR (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '+@instance+'))'  --end of a string query

EXEC SP_EXECUTESQL @SQLq,
N'@v1 NVARCHAR(255), @v2 NVARCHAR(255), @v3 INTEGER',
@v1 = @area, @v2 = @country, @v3 = @instance

To be able to use variables from outside of the string query I had to "break" a string chain as @mortb wrote in related post. But even though I did the same with @instance (the integer variable) it's not working.
I'm getting an error:

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the nvarchar value '...here is the query...' to data type int.

The error is connected with @v3 or @instance in this part:

option(optimize for (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '+@instance+'))'

I can't find solution for this problem. I tried to "break" the string chain with the same method as I used for string/varchar variables like:

 option(optimize for (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '''+@instance+'''))'

But it's not working. Is there a different method for using integer variables?

I'm using query string (it's called dynamic query I suppose) because I have to make a research connected to parameter sniffing (here option(optimize....)) without creating stored procedures and being able to use variables. Does anyone encountered similar problem and found a solution? If someone does please help me with overcome this hurdle.

The problem is with the line that builds the dynamic query, specifically here:

'... @v3 = '+@instance+'))'

The @instance variable is an int variable. The int type has higher precedence than any string type, and so it changes the meaning of the + operation, which now stands for mathematical addition rather than string concatenation. In other words, you are effectively trying to add a string, which is your query, to an integer, which is the @instance variable. Since the string fails to convert to an integer, you get the error shown in your question.

Solution: change the @instance in that fragment to CAST(@instance AS varchar(11)) (or CAST(@instance AS nvarchar(11))), like this:

'... @v3 = ' + CAST(@instance AS varchar(11)) + '))'


How many LEFT JOINS make a VIEW unusable? I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected.

For example, with the query below I would expect a clustered index seek on the table:

SELECT id FROM dbo.TableName WHERE id = 256

Whereas when I use the view I get the clustered index seek on the table along with 3 nested loops (left outer join).

SELECT id FROM dbo.ViewName WHERE id = 256

Why would this occur?
The above is a very simple example but when selecting additional columns from the view more and more unnecessary LEFT JOINS appear in the execution plan.

The link provided by Martin Smith to an existing SO question provided me with the answer:

  The main restrictions are that foreign key relationships must be based
  on a single key to contribute to the simplification process


How could I fix this warning? SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 13:08:07 2018

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

Enter user-name: system
Enter password:
ERROR:
ORA-28002: the password will expire within 4 days

Every time I login into my database it shows this warning message of password gonna expire in .. days. Now its just 4 days left I don't know what to do to fix this warning.

Use the password command after logging in to change your password:

SQL> password
Changing password for SYSTEM
Old password:
New password:
Retype new password:
Password changed
SQL>


How to make IntelliSense understand temp tables, linked servers and synonyms? I'm using SSMS 17.6 which is the latest version at the time of posting. It is supposed to have the latest official IntelliSense version. However, it underlines my scripts with red squiggles where I'm using temp tables, linked server tables and synonyms. It's difficult to edit queries because I don't get correct error detection. Everything is filled with "errors", but queries run, and the results are what I want, as expected.

I've tried a few third-party addons like SQL Complete and SSMSBoost, but they don't seem to improve IntelliSense, but rather extend the SSMS in other ways like tooltips and popups. Or maybe I just haven't figured out how to use them for improving IntelliSense.

What can I do to make IntelliSense correctly handle these things? If there is no first-party way, then what third-party extensions exist and how to configure them to improve error highlighting?

In short, Intellisense isn't as intelligent as its name might suggest.

Temp Tables

If you're working with temp tables across different databases or separate query windows, Intellisense doesn't think the table exists, because, well, it doesn't as far as it knows. There are usually two scenarios this occurs:

At run time, we know that a first command will execute and by the time we get to running an additional command that the table will exist. But asking Intellisense to be aware of things that haven't happened yet is impossible. This usually involves the temp table being created by a different process, query window, etc. It generally only knows the scope of its own query window and objects in non-tempdb databases.
If it does already exist, being aware of everything already in the tempdb is probably a performance concern (just conjecture) because things move around in there fast, from moment to moment, and it'd be hard/unlikely to get a reliable enumeration of objects that was valid for more than a few seconds. Regardless, it just doesn't know about much outside of that single query window with regards to tempdb objects.

Linked Servers

This one sort of makes sense. Do you really want Intellisense going and querying your linked servers every time you are writing queries that involve remote objects? It just doesn't know and so assumes it doesn't exist.

Synonyms

If the synonyms are referring to linked server objects, then they'll suffer from the issue described above. On their own, however, they should work fine with local database objects.

Bottom Line

Learn to ignore it, or use a different IDE if possible (one that doesn't use Intellisense). Most people seem to like Intellisense despite its flaws, but you could always turn it off completely as well.

There is a hacky work-around for temp tables at this SO question but I personally would never do that, as you're increasing the complexity and reducing readability of your code for...a red squiggly line.


Copy database removed source - Cannot acces mdf file I attempted to copy a database, using Microsoft SQL Server Management Studio (SSMS), but right-clicking and following the wizard. I wanted to target server to be the same as the source, so I added "_new" to the database name.

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file.

However, when I try to attach the mdf file, I get the following error meesage:

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Luckily this was just a test database, so I didn't lose any data, that couldn't be easily reproduced. But is there a way to recover from this? If it ever happened on a production database, I would like to be prepared.

I have never tried to use SSMS to copy a live database as you have done.  But I have copied many, many databases without an issue.

Use the following approach in the future and you will not have the same issue again.

Take a full backup (use COPY_ONLY)

Then restore the backup with a new name.

Your live database will never be detached, so you won't have to try and figure out how reattach it


Secure postgres 9.5 database packed on Windows desktop application My desktop app includes a postgres database and a front app. I need the db to be accessed only by my front app. The credentials of this user are going to reside in the front app binaries.

I realized it's possible for anyone to use createuser.exe with -s to create a superuser. After that, it would be enough to create a db with createdb.exe, connect to it, and change ownership of my db to the new user, or do whatever.

The only effective way to avoid illegal access I know is to change template0 and template1 names, so that createdb.exe throws 'template0 not found'. That way anyone could create superusers, but no db's to connect to.

In addition, it would be needed to drop postgres database and postgres role, and execute these sentences:

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;

REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;

REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;

Can I do something else?

It is not necessary to break your database to make it secure.

Just configure pg_hba.conf to make sure that nobody except the operating system user postgres can log in, and restrict login to that account.

There is no way to protect the database from an administrative user who can change operating system passwords and override file permissions. Don't even try.


SQL Server falling below 5000 free pages I have set alerts on two SQL Server 2005 instances I have in production.
One of those alerts is sent whenever the Free Pages performance counter falls below the 5000 pages mark.

Both servers normally operate with more than 300000 free pages, but sometimes one of these servers momentarily goes well below 2000 or even 1000 free pages and then goes up again. Again, this only happens with one of the servers.

As far as I know, this behavior is not degrading the performance of this server, because it looks to me as if whenever the server is going to starve on free pages, it frees up older data pages from the buffers and populates the Free Buffer List. Theoretically speaking, at least.

What I don't understand is why the lazywriter isn't maintaining a larger free buffer list and is 'waiting' for SQL Server to get so short on free pages before freeing up more buffers.

So my question is actually twofold:
1. How can I determine what/who is causing SQL Server to become short on free pages?
2. How can I inspect what is actually going on here?

So what can happen that the free lists shrinks? For most purposes you can consider the formula free = total - (max(database, reserved)+stolen). So simply collect all the counters in the SQL Server, Buffer Manager Object and then see what the pattern is:

total stay the same, but stolen has grown. This indicate some memory consumption from code and caches. and usually does not heal itslef. You would investigate this by checking the memory clerks, see How to use the DBCC MEMORYSTATUS command to monitor memory usage. A possible explanation for a sudden spike in stolen that disapears quickly is a complex query compilation. Collect SQL Server, Memory Manager Object for additional info, look at Optimizer Memory.
database, reserved and stolen stay the same, but total shrinks. Lets just consider this as it doesn't happen. If you see this, post here and we can further digest.
total stays the same, database grows. Probably a scans on a large table. Look for a query with large logical_reads in sys.dm_exec_query_stats
total stays the same, reserved grows. Indicates a query that has a large memory grant request. Collect SQL Server, Memory Manager Object for additional info, look at Memory Grants Outstanding.


Mysql: disable unused schemas I have many schemas on mysql instance running on my development pc. I've noticed that mysql is taking a large amount of ram and cpu time.

Is there a way to "disable" schemas of project I'm not working for?

I like to avoid to drop schema and then re-import when I need it. I'm searching to a way for just disable them or something similar!

Thanks
Marco

Every subfolder under datadir is considered fair game to be registered as a database. You could just mysqldump that entire database and then drop the database.
SUGGESTION
If you have to leave the database present but inaccessible, here is something radical you can try:
EXAMPLE
Suppose you have a database called mydb and you want to disable access to it. Go into the Linux OS and do the following:
chown -R root:root /var/lib/mysql/mydb

That's it. Since /var/lib/mysql/mydb would be no longer owned by the Linux mysql user, mysqld cannot access anything in that folder.
If you want the database accessible again, just do the reverse:
chown -R mysql:mysql /var/lib/mysql/mydb

Now, if you are dealing with Windows, try shutting down mysql with
net stop mysql

You could then

Rename the folder from mydb to #mydb something mysqld would not like
Run attrib +a against that folder
Just about anything that can deny read/write access to the Windows folder

Finally, start mysql back up
net start mysql

Give it a Try !!!


Transaction log backup - Only full recovery model? I am reading this book from Microsoft - it says:
"A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed"

Is this correct? I know simple recovery model doesn't support transaction log backup. But think I have read somewhere that full and also bulk recovery model supports transaction log backup.

No, this is not correct.

See SQL Server Recovery Models

If you are utilising the BULK recovery model, this will function the same as the FULL recovery model apart from specific minimally logged operations (SELECT INTO, BULK INSERT, CREATE INDEX...)

If you do not set up log backups, you risk your log files filling.


Understanding Function Index Looking at Oracle's docs on the Function Index, what's the meaning of rivers in the following?

CREATE INDEX area_index ON rivers (area(geo));

SELECT id, geo, area(geo), desc
     FROM rivers
     WHERE Area(geo) >5000;

What's the importance of rivers with respect to this query?

Could dual have just been used?

It is the name of the table that the index is created on.

This:

CREATE INDEX area_index ON rivers (area(geo));

.. creates a functional index on the rivers table, that uses the function area() on the column geo.

In simple terms, Oracle creates an index that pre-calculates the value of area(geo), thus making any lookups faster.

The query you have posted:

SELECT id, geo, area(geo), desc
FROM rivers
WHERE Area(geo) >5000;

... can use the area_index functional index to select the pre-calculated value(s) of area(geo) without having to call the area() function for each row in the table.

Essentially, the query can look like this in pseudo-code:

SELECT id, geo, area_index_value, desc
FROM rivers
WHERE area_index_value >5000;

The Oracle documentation explains this well.


Faulty oracle11g regexp_replace on removing duplicates I have a comma separated list of values (as one single string), which contains duplicates, and I want to remove them. I tried with REGEXP_REPLACE but for certain values it gives me a wrong result:

/* CORRECT */
select '308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' as str,
       regexp_replace('308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001',
                  '([^,]+)(,*\1)+',
                  '\1') as str_uq
from dual
union all
/* FAULTY */
select '106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' as str,
       regexp_replace('106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701',
                  '([^,]+)(,*\1)+',
                  '\1') as str_uq
from dual

Running the above example, will provide the expected result for the first query, but for the second one, it just concatenates all the values without removing duplicates. I guess the fact that the values are starting with 1 has something to do with it.

Can you please help me out with the regex pattern? I cannot find the mistake...

Thank you!

In your 2nd example the values are unique, but you're searching for any repeated match and there's multiple matches:

'106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701'

It's not concatenating, it's removing some characters :-)

Usually adding a word boundary \b should work, but not in Oracle.

I found this is working '(.+?,)\1+', but you need to add a final comma to the string (fiddle)

/* CORRECT */
select '308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' as str,
       regexp_replace('308RONBADB046782001,308RONBADB046782001,308RONCRT0046782001,308RONCRT0046782001,308RONCRT0046782001,308RONMFRT046782001,308RONMFRT046782001' || ',',
                  --'(\b[^,]+)(,*\1)+', --should work, doesn't in Oracle
                  '(.+?,)\1+',
                  '\1') as str_uq
from dual
union all
/* FAULTY */
select '106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' as str,
       regexp_replace('106RONBADB0W2562701,106RONCRT00W2562701,106RPB0130350001,106RONBADB0W2562701' || ',',
                  --'(\b[^,]+)(,*\1)+', --should work, doesn't in Oracle
                  '(.+?,)\1+',
                  '\1') as str_uq
from dual


Replication SQL Server to a Cluster with error I have a source DB MSSQL Server and deploy transactional-replication
 to a cluster with 2 Node(A&B).
The subscriber was added using SQL IP of Cluster and using "sa" account.
It run fine when server A active and server B passive.
When i shutdow the server A the replication stop and show error:

The process could not connect to Subscriber 'AHTDWHSQL'. (Source:
 MSSQL_REPL, Error number: MSSQL_REPL0)
Get help: http://help/MSSQL_REPL0
Named Pipes Provider: Could not open a connection to SQL Server [1326].
(Source: MSSQLServer, Error number: 1326)
Get help: http://help/1326
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (Source: MSSQLServer, Error number: 1326)
Get help: http://help/1326
Login timeout expired (Source: MSSQLServer, Error number: HYT00)
Get help: http://help/HYT00

you got SQL Server error number "1326" which mean there is a connection issue to the destination server, how to troubleshoot that

ping the server and check if you can reach it, and connection allowed via firewall.
check if SQL Server services are up and running (you can do that from services window).
check if you can access the server remotely using SQL server, if not open SQL server configuration manager, choose "SQL Server Network Configuration", then "protocol for MSSQLSERVER", open TCP/IP, and make sure Sever allowing traffic to the destination.
Open SSMS, connect, right-click on the server, properties, connections, and check "Allow remote connections to this server". (you have to restart the service to make sure changes took effect).


slave mysql configuration is not stored EDIT
It seems that the problem is with my SSH connection, so I created a different question on serverfault. From what I understand so far, it that this problem is caused from the fact that the port forwarding is not established properly.

Original question
I am trying to create a master-slave sync between two Mysql instances. After a lot of work, I at least have two running servers (since I had so many problems that most of the times I was breaking something).
Now, on my slave my.cnf file I have

  server-id               = 155
  log_bin                 =
  /var/log/mysql/mysql-bin.log
  master-host = 127.0.0.1
  master-user =
  masterUser
  master-password = masterpassword
  master-port = 3336
  (I am using port forwarding, so that why I use 127.0.0.1 as host)

The problem is that when I ask for the slave's id I get 0

mysqld --help --verbose |grep server-id

  replicate-same-server-id                          FALSE
  server-id                                         0

and

mysql> SHOW VARIABLES LIKE 'server_id';

  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | server_id     | 0     |
  +---------------+-------+

The file I am using is the proper file, since when I added this

  [mysqld]
  collation-server = utf8_unicode_ci
  init-connect='SET NAMES utf8'
  character-set-server = utf8

the result of  mysqld --help --verbose |grep character-set-server
changed from

  character-set-server                              latin1

to

  character-set-server                              utf8

Thanks in advance

A few things to check:

Have you restarted the MySQL instance after setting the server-id variable in my.cnf? (Obvious, I know, but I have to ask!)
In which section of the my.cnf file have you added this variable? The [mysqld] section would be an appropriate place for it, I think.
Do you have any other .cnf files that might overwrite your setting? Assuming Linux, a command such as mysqld --help --verbose | head -n 20 should give you some output which includes the files where mysqld is looking for default settings.


Are there any best practices before moving SQL Server to another node (controlled failover)? Let's say there are multiple jobs running on the SQL Server - we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there any best practices before performing such operation, for example disabling the jobs? Is there anything which could be done to minimize any potential risks?

First make sure you have both logins and jobs fully synchronised between your instances.
there are different ways to achieve these.

  index maintenance jobs and backups

I had good results by using ola hallegren maintenance jobs for these.

  replication and service broker

Always point to the listener instead of individual IPs or server names.

  inside the jobs

inside every step of every job I test if I am in the primary server, in my current availability group called sqlprodag.
Something like this:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'SQLPRODAG'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

    EXEC MYDATABASE.[dbo].[usp_ins_APfactAPApplicationBreakdown]

end
else
begin
    -- this server is not the primary replica, (optional) do something here
    print 'Not a primary server'
end

and the job will only run on the primary server.

  best practices before performing a failover

planned manual failover of an availability group

--======================================================================================
-- set the availability group to synchronous mode
-- wait until all is synchronized
-- do the failover

--SQLPRODAG is the availability group name
--servers are SQLPROD1 and SQLPROD2
--======================================================================================

--ON THE PRIMARY
ALTER AVAILABILITY GROUP SQLPRODAG
     MODIFY REPLICA ON N'SQLPROD2'
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

--GO TO THE SECONDARY
ALTER AVAILABILITY GROUP SQLPRODAG FAILOVER;

     SELECT ag.name,
   drs.database_id,
   drs.group_id,
   drs.replica_id,
   drs.synchronization_state_desc
   --ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

     SELECT ag.name,
*
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;


'Ungroup' data based on column I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has to be in a specific format, but there is one catch: there can be multiple PseudoIds in the same row, separated by a semi-colon.

I have already figured out how to write the LIKE statement for 1 PseudoId, but I have some trouble to write the following query: retrieve all the Id's of A where all the PseudoIds (can be 1 or more) are in the correct format. So for 1, this is easy, but for more a little more tricky.

What I thought I had to was 'ungroup' my table A so that I return a new table A' where the following applies: if there is only 1 PseudoId on a row, then just return the row. Else, split the row on semi-colon and return the rows, which will all be identical but have a different PseudoId.

So for example:

ID | PseudoId
-------------
 1 | ABC
 2 | DEF
 3 | GHI;JKL
 4 | MNO
 5 | PQR;STU;VWX

'Ungrouping' this should yield:

ID | PseudoId
-------------
 1 | ABC
 2 | DEF
 3 | GHI
 3 | JKL
 4 | MNO
 5 | PQR
 5 | STU
 5 | VWX

I would probably use a table-valued function, but not entirely sure how to accomplish this... Much appreciated.

You can do this with a TVF, when you migrate to SQL Server 2016 or higher you can use STRING_SPLIT.

Function source : https://stackoverflow.com/questions/10914576/t-sql-split-string

(slightly modified to use ';' instead of ',' )

CREATE FUNCTION

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @stringToSplit)
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

TEST DATA

CREATE TABLE test(id int, pseudoid varchar(255))
insert into test
values(1 ,'ABC'),
(2 ,'DEF'),
(3 ,'GHI;JKL'),
(4, 'MNO'),
(5 ,'PQR;STU;VWX')

TEST

   select id , s.Name as PseudoID from
    test
    cross apply dbo.splitstring(pseudoid) as s

RESULT

id  PseudoID
1   ABC
2   DEF
3   GHI
3   JKL
4   MNO
5   PQR
5   STU
5   VWX


double date conversion when adding partition I want to create a procedure that adds new partition in a table partitioned by time. For this purpose I use following commands:

    s_until := to_char(sysdate+1,' SYYYY-MM-DD HH24:MI:SS');
    s_statement := 'ALTER TABLE '||s_table||' ADD PARTITION '||s_partition||' VALUES LESS THAN
    (TO_DATE('''||s_until||''', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))
    TABLESPACE '||s_tablespace;
    execute immediate s_statement;

As you see I perform unnecessary conversions of date.
Does anybody know how skip this dirty hack?
Performing this way without conversion gives and ORA error.

    s_statement := 'ALTER TABLE '||s_table||' ADD PARTITION '||s_partition||' VALUES LESS THAN
    (sysdate+1) TABLESPACE '||s_tablespace;
    execute immediate s_statement;

There is no way around it in Oracle 9i.

If you were using 11g, you could define the table to be interval partitioned in order to avoid having to create a new partition every day.  But that's not going to help you on an ancient version of Oracle.


What do the values after `execute fetch from` mean in postgresql query logs? We've turned on some slow query logging on a postgres 9.1 cluster and see
the following in the logs::

2016-08-08 13:05:51 AEST LOG:  duration: 24126.105 ms  execute fetch from S_26/C_775: SELECT ...(query continues)

What does the S_26/C_775 bit mean? Can I use this to find which client submitted the query?

S_26 is a statement name and C_775 a portal name in the extended-query protocol.

See https://www.postgresql.org/docs/current/static/protocol-overview.html

  In the extended-query protocol, execution of SQL commands is divided
  into multiple steps. The state retained between steps is represented
  by two types of objects: prepared statements and portals. A prepared
  statement represents the result of parsing and semantic analysis of a
  textual query string. A prepared statement is not in itself ready to
  execute, because it might lack specific values for parameters. A
  portal represents a ready-to-execute or already-partially-executed
  statement, with any missing parameter values filled in. (For SELECT
  statements, a portal is equivalent to an open cursor, but we choose to
  use a different term since cursors don't handle non-SELECT
  statements.)

To find the query bound to this statement, look upper in the log for the corresponding bind entry.

If it doesn't exist, you might have to increase the logging level to DEBUG2 through log_min_error_statement, or set log_min_duration_statement to 0.


MySQL UPDATE statement using a table created on the fly I want to update a table, say Table 1 using a table that I create on the fly (saytemp). To achieve this I have the following query:

UPDATE Table1 SET COUNT = temp.ctemp FROM ( SELECT A, SUM(UNO) AS ctemp FROM Table1
                                            GROUP BY A
                                            ORDER BY A)
                                      AS temp,Table1 t1
                                      WHERE temp.A = t1.A;

It works well in SQL Server 2008 but MySQL  throws out the error

syntax error, unexpected FROM, expecting 'END_OF_INPUT' or ';'

Any idea what I am missing here?

UPDATE syntax in MySQL is different than SQL-Server. This will do:

UPDATE Table1 AS t
  JOIN
      ( SELECT A, SUM(UNO) AS ctemp
        FROM Table1
        GROUP BY A                     -- no need for `ORDER BY` in either DBMS
      ) AS temp
    ON temp.A = t.A
SET
    t.COUNT = temp.ctemp ;


How should I tune Postgresql for 20 GB of RAM? I've been fortunate enough to have the use of a 20GB Linode instance running Ubuntu 64 bit. I want to try to optimize PostGres for this service, but I don't know what I should prioritize changing.

I have several datasets of 20,000 or so rows and the calculations that are being performed are memory intensive queries (spatial analyses) with a small number of rows being written after each request. The total number of users is very small (10 - 50).

I've read through this article on the Postgresql site but I don't know enough about how this works, to know what I should prioritize. I've also looked at advice on what to change for geo type work here.

For example, I tried changing the shared_buffers to 200MB (which is much less than 75% of 20GB). This resulted in the following error message:

  Restarting PostgreSQL 9.1 database server

  The PostgreSQL server failed to start. Please check the log output: 2013-03-10 12:21:58 EDT FATAL:  could not create shared memory
  segment: Invalid argument 2013-03-10 12:21:58 EDT DETAIL:  Failed
  system call was shmget(key=5432001, size=47742976, 03600). 2013-03-10
  12:21:58 EDT HINT:  This error usually means that PostgreSQL's request
  for a shared memory segment exceeded your kernel's SHMMAX parameter.
  You can either reduce the request size or reconfigure the kernel with
  larger SHMMAX.  To reduce the request size (currently 47742976 bytes),
  reduce PostgreSQL's shared memory usage, perhaps by reducing
  shared_buffers or max_connections.    If the request size is already
  small, it's possible that it is less than your kernel's SHMMIN
  parameter, in which case raising the request size or reconfiguring
  SHMMIN is called for.     The PostgreSQL documentation contains more
  information about shared memory configuration.

I returned this to it's original value and tried changing:

work_mem = 50MB
maintenance_work_mem = 256MB

My problem is that I don't know which values I should try changing, or how I should prioritize which values are key to experiment with and test. What should I specifically do to optimize this database?

I would recommend using pgtune written by Greg Smith.

Simply run it on your server as follows:

pgtune -i postgresql.conf -o postgresql-tuned.conf

It has few more options, but just doing that and using generated postgresql.conf will do wonders for your server performance.

I think PostgreSQL should include this in standard install, and even run it by default - such that people cannot complain about bad PostgreSQL performance.


Active/Passive to Active/Active In layman terms, what does it mean to have "Active/Active"?  What are the steps to achieve this configuration?

Edit:
We currently have an "Active/Passive" setup which isn't efficiently making use of the hardware so we need a "Active/Active" configuration.

Active/Active could mean two things (or maybe others depending on the project manager's imagination).

At least two clustered instances of SQL Server on two nodes of a Windows cluster. This allows both pieces of hardware to be used simultaneously and not wasted, one server for each instance, with the worst case scenario being one server fails and the other takes all of the load for both instances in that case.
More likely, a manager thinks that both nodes of a cluster could be actively running the same instance of SQL Server, with reads and writes going to both. This is not how clustering works; it is not for scale out but for high availability / disaster recovery. But it is a common misconception, especially for managers. :-)

Most people that work with clustering regularly cringe when they hear active / active. It's inaccurate, ambiguous, and to them it's like nails on a chalkboard.


How to Perform Two Simple Queries using Select Value from First Query I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select limit n id < selected.id and limit n id > selected.id

I am currently failing in getting the value from 1st query to perform union all queries. How can I fix it?

SELECT * FROM table WHERE column_b = 'a value' # this query returns ID.
union all
(select a.* from table as a where id < returned.id
order by id desc limit 5)
union all
(select a.* from table as a where id >= returned.id
order by id asc limit 15)

How about using a join in the unioned SELECTs:

(SELECT a.*
 FROM table AS a
  JOIN table AS t2 ON (a.id < t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id DESC LIMIT 5)
UNION ALL
(SELECT a.*
 FROM table AS a
  JOIN table AS t2 ON (a.id >= t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id ASC LIMIT 15)

Note that the order from the parts of UNION ALL might not be preserved in the result.


Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Express which version SQL server can Install Windows 10?

Is  SQLManagementStudio_x86_ENU.exe  free ?

As you said in comments you'd like a SQL Server + a SSMS.
I would suggest not using a SQL2012, but going for a more recent version.

Using this this link you can download the SQL Server itself.
The 2 free versions are express and Developer. More information about the differences between versions can be found here : SQL Editions and SQL Pricing

Developer you should use if you're just trying to develop some things at home, NOT COMMERCIALLY. It has all the features enterprise has.
Express will give you a basic sql server with some limitations.

Afterwards you can get SSMS using this link.
SSMS is free, and its just used to work on your SQL Server


Cannot open some tables in phpmyadmin Recently i get backup of my database from host in that backup i get table in .ibd,.frm,.myd format .

I placed this files in

  xampp->mysql->data->test

. Now i can open the tables in php myadmin. Now i can open some tables .But some files i can not open . When clicked it showing 932 - Table 'table.name' doesn't exist in engine . And also i noticed in the collection column it is showing in use

Why this happen . Please help to solve

You can copy frm and ibd files ONLY if the tablespace ID in idb file is the same as the tablespace ID in ibdata1, if it's not then the data will be corrupt and you will be missing tables.

When I had that problem I used this resource to recover files:

Recover InnoDB into MySQL

You may also find these links useful:

Recover InnoDB from ibd File
Manually Importing InnoDB MySQL Table Files


Composite index design where only a subset will be queried I have a table with columns A (typedate), B1 ... B24 (all boolean).

Generally, A will always be used in every query and only one of B1 ... B24. A large subset of the B columns (more than half) will generally be queried more than the rest, but all of them will be used fairly frequently.

Should I create indexes on 24 two-tuples of (A, B1) ... (A, B24)?
Or 1 huge composite index encompassing all the columns?

If I'm creating a lot of indexes, at what point am I adding too many indexes to the table and what is the approach to working that out? The table is used in a lot of core functionality and will be queried very frequently.

The B columns will only be filtered by true, and generally only one B column will be used in a single query. So the where clause of the query will generally look like:

... WHERE A = '2018-11-22' AND B12

Consider N partial indexes:

CREATE INDEX tbl_a_b1_idx ON tbl (A) WHERE B1;

All together only occupying slightly more space than a plain btree index on (A). You can have hundreds of indexes like that without problem. (I had a case with ~ 250.) The number is certainly not exhausted by the maximum of possible columns (250 - 1600 depending on column types).

Since each partial index is comparatively small, this will typically benefit performance as long as query patterns match.

Additional indexes may be useful and adding more index expressions may make sense to get index-only scans ....

Using a current version of Postgres is advisable in any case, but for this in particular, since there have been a number of optimizations for partial indexes lately.

To create all DDL statements:

SELECT format ('CREATE INDEX tbl_a_b%1$s_idx ON tbl(a) WHERE b%1$s;', g)
FROM generate_series(1, 24) g;

Returns:

CREATE INDEX tbl_a_b1_idx ON tbl(a) WHERE b1;
CREATE INDEX tbl_a_b2_idx ON tbl(a) WHERE b2;
...

Related:

PostgreSQL multi-column unique constraint and NULL values


Error when upgrading SQL 2016 to SP2 - "The given key was not present in the dictionary." I am trying to patch one node in a SQL Server 2016 Standard FCI.  I removed the node from the cluster, and ran the installer, but it failed.

The summary file contained the following exit code / message:

Exit code (Decimal):           -2146233079
Exit facility code:            19
Exit error code:               5385
Exit message:                  The given key was not present in the dictionary.

I cannot find anything obvious in the "Detail" file.

Can anyone recommend where I could go to get a clearer idea of what failed?

What I am understanding from your requirement to patch a sql clustered environment you are removing a node. This is definitely not the right way to proceed. You need to patch the current passive node first. Once everything goes well do a failover at node level.

Now the patched passive node becomes active, do your health check for the patching done if all look green. Once ok, proceed with patching on current passive node.

If there is any node preference you can failback to original active node and do health checks to ensure patching went well.


Time interval is returning all records or no records I am trying to run a query to return records in the last 24 hours and following the instructions here: time interval tutorial
After running the query at 18:30 UTC, my query looks as follows:
RETURN QUERY
        SELECT
            h.heartbeat_time
            ,h.message
            ,h.rec_created_by_user_id
        FROM backup.heartbeat AS h
        WHERE h.heartbeat_time  >= NOW() - INTERVAL '30 MINUTE';

and the return:
heartbeat_time     |message
-------------------|----------------------------------------------
2019-12-19 17:46:31|some random aws event
2019-12-19 17:48:17|{¶
2019-12-19 17:48:47|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:09:23|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:10:11|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:10:48|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",
2019-12-19 18:11:14|{"id": "89d1a02d-5ec7-412e-82f5-13505f849b41",

I would expect the anything older than 18:00 UTC to now show up, but 17:46-48 still shows up.
According to the provided link and many others, this is the correct syntax. Is there something I am missing?


Why is "INSERT INTO table;" a syntax error? I have this:
    CREATE TABLE people
    (
        id              bigserial,
        timestamp       timestamptz DEFAULT now() NOT NULL,
        PRIMARY KEY     (id)
    );

    CREATE TABLE "personal information"
    (
        id                  bigserial,
        "person id"         bigint NOT NULL,
        timestamp           timestamptz DEFAULT now() NOT NULL,
        "data's timestamp"  timestamptz,
        "field"             text NOT NULL,
        "value"             text,
        PRIMARY KEY         (id),
        FOREIGN KEY         ("person id") REFERENCES people (id) ON UPDATE CASCADE ON DELETE CASCADE
    );

    INSERT INTO people RETURNING id; -- Syntax error.
    INSERT INTO people; -- Syntax error.

Why do my INSERTS fail? It's a fact that all the columns in the "people" table are automatic: the id column is a bigserial, and the timestamp one is always set to the current time when it was inserted. Thus, there is no need for me to specify any columns to be inserted, as all of them are automatic.
So why is it a syntax error? What does it want me to do? This makes me feel as if I'm not doing this right somehow, but having a bunch of "personal data" fields in the "people" table would be redundant since that data will be stored in the "personal information" table. For example, the "field" might be "social security number" or "first name", so I just grab whatever record is the latest (based on timestamp) for that "person id" to get their social security number or first name.
If I were to store those values as columns in "people", it would be pointless redundancy, and it would make it a major PITA whenever I introduce a new kind of "personal information field" in the future, as I'd have to add columns to the "people" table. So I really hope that my efficient and smart (IMO) structure is not somehow incompatible with PostgreSQL, and that I'm just missing some minor syntax detail.

The syntax rules require at least one target column to be specified. If you want to let the defaults "kick in" for all columns, you can use the default values clause.
INSERT INTO people DEFAULT VALUES;

another alternative is to provide one column with the DEFAULT clause:
insert into people ("timestamp") values (default);


How to include a semicolon (";") in a MySQL pager command I've just discovered the pager command in the MySQL command-line client, and I'm having a tonne of fun. This blog post is great:

http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Unfortunately as a Perl programmer I find myself wanting to do more and more complicated pagers, and I've hit into a problem: I don't seem to be able to use a semicolon character in my pager command.

Simple:

mysql> pager md5sum
PAGER set to 'md5sum'
mysql> select id from listings limit 5;
cfbe10c0a46ea57f6fa199bc9e05ed3a  -

More complicated but still fine:

mysql> pager perl -nle 'print "    $_"'
PAGER set to 'perl -nle 'print "    $_"''
mysql> select id from listings limit 5;
    +---------+
    | id      |
    +---------+
    | 2003921 |
    | 7164746 |
    | 7166576 |
    | 7166605 |
    | 7166853 |
    +---------+

(That one is useful for dealing with Markdown!)

But unfortunately if I add a semicolon on the end of my statement it says I have a syntax error:

mysql> pager perl -nle 'print "    $_";'
    -> \c

mysql> pager perl -nle 'print "    $_";';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pager perl -nle 'print "    $_";'' at line 1

I've tried escaping the semicolon in the Perl command with a backslash or two (in case the backslash itself needed escaping) but nothing seems to work.

Is it possible to include a semicolon in a pager command?

Try to set delimiter to something else than ;

     mysql> DELIMITER endl
     mysql>

Then you can set pager with the semicolon:

     mysql> pager pager perl -nle 'print "    $_";'endl
     PAGER set to 'pager perl -nle 'print "    $_";''

Then you can come back to ;

     mysql> DELIMITER ;


Size of individual transactions in log file is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which fail and do not seem to release the log space any more. So I would like to find out the statements and how much space it used in the transaction log during a specific period in time.

I'm thinking of something like using the output from fn_dblog(null,null) and aggregate the output accordingly. But that seems to be quite daunting as there is no official documentation.

Any suggestions and help welcome.

Please refer following link to get answer to your question :

To get the size of each open transactions refer following link

https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

If you want to know the transaction log of any historical date then you can take the backup of database or just a transaction log backup and use third party tools to read those logs. It will also allows you to read the the logs taken by date.(ApexSQL Log tool, SQL Log Rescue)

Hope this helps.


MySQL very slow query when changing one WHERE field despite no index/key This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN games ON games.gameName = hits.gameName
WHERE games.type = 'R'
LIMIT 50

Returns:

/* 0 rows affected, 50 rows found. Duration for 1 query: 0.218 sec. */

But running this query:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN games ON games.gameName = hits.gameName
WHERE games.leagueLevel = 'mlb'
LIMIT 50

Hangs for a long time. The index on the games table is only games.gameName and nothing else.

SELECT DISTINCT type FROM games gives 8 single-character rows (VARCHAR 1) including one NULL.

SELECT DISTINCT leagueLevel FROM games gives 6 three-character rows (VARCHAR 5) including one NULL.

I have no idea why the second query would be extraordinarily slow while the first one runs just fine.

Thanks for your help.

VIEWPOINT #1 : You many need to take a look at column value population

SELECT COUNT(1) rowount,type FROM games GROUP BY type WITH ROLLUP;
SELECT COUNT(1) rowcount,leaguelevel FROM games GROUP BY leaguelevel WITH ROLLUP;

From your question, I gather two things:

The number of rows in games with type='R' must be a low number against the number of rows in the games table.
The number of rows in games with leaguelevel='mlb' must be a high number (greater than 5% of the table) against the number of rows in the games table. (5% is a rule-of-thumb number in the eyes of Query Optimizers)

VIEWPOINT #2 : You may need to refactor this query

Notice that query will perform the WHERE portion after all JOINs are complete. If the WHERE portion could  be performed earlier that could help reduce the time. Try reorganizing the query like this:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN (SELECT * FROM games WHERE leagueLevel = 'mlb') games
ON games.gameName = hits.gameName
LIMIT 50;

VIEWPOINT #3 : Retrieve only the columns you really need

I see you have SELECT * and you have four tables (hits, stadiums, players, games). You will have a lot of duplicate data to drag into the query, particularly when dragging the gameName column from all four tables.

You should reorganize the query to bring only one gameName column:

SELECT hits.gameName,hits.*,players.*,staduims.*,games.* FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN (SELECT * FROM games WHERE leagueLevel = 'mlb') games
ON games.gameName = hits.gameName
LIMIT 50;

Additionally, if you do not need every column from the hits tables, then only include the column you know you will access. The same goes for players, stadiums, and games.

In other words, as an example, if you only need the playerName from the player table, then you do not need player.* in the SELECT. You will need just player.playerName.

VIEWPOINT #4 : You may need to index the leagueLevel column

You will need to do the following to make the needed index:

ALTER TABLE games ADD INDEX (leagueLevel);

Before doing so, run this

SELECT COUNT(1) rowcount,leaguelevel FROM games GROUP BY leaguelevel WITH ROLLUP;

Any value for leagueLevel whose count is greater than 5% of the table will cause the MySQL Query Optimizer not to use the index.


for archive_command use an script rather than a command I want to create a configuration to get a PostgreSQL HA. In my Master I want to configure a postgresql.conf file with this:

archive_command = 'local_backup_script.sh "%p" "%f"'

instead of:

archive_command = 'cp "%p" /path/to/archive/"%f"'

Then I have a doubt: Where do I have to put the script?

You can put it wherever you like, so long as the postgres user has permission to execute it, and permission to cd into the directory it's in.

I usually put it in /usr/local/bin unless I'm working on a system that has a versioned repository of scripts, in which case it's in there - something like /opt/mycompany-scripts/postgresql/myarchivescript.sh.

I recommend using a fully qualified path to make its location obvious, e.g.

archive_command = '/usr/local/bin/pg_archive_wal.sh "%p" "%f"'

(calling it "backup script" is misleading; it isn't doing backups at all, it's just archiving WAL, which may be part of a backup or replication process).


mySQL throws error, but if copied from error message or die() into phpmyadmin it works I want to get a script running, which creates a column in a table, if does not exist. I am using the script of user abahet from mysql ALTER TABLE if column not exists

My php code (inside of the db-class) looks like this:

$sql = '
    SET @preparedStatement = (SELECT IF(
        (SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE table_name = "' . $this->t($table) . '"
                 AND table_schema = DATABASE()
                 AND column_name = "' . $column . '"
        ) > 0,
        "SELECT 1;",
        "ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';"
    ));

    PREPARE alterIfNotExists FROM @preparedStatement;
    EXECUTE alterIfNotExists;
    DEALLOCATE PREPARE alterIfNotExists;
';
//die($sql);
$this->db($sql,'utf8');

Once executed, it throws the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE alterIfNotExists FROM @preparedStatement;
            EXECUTE alterIfNotExists;
    ' at line 12

            SET @preparedStatement = (SELECT IF(
                (SELECT COUNT(*)
                        FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE table_name = "ddpos_2_user"
                         AND table_schema = DATABASE()
                         AND column_name = "id_signature"
                ) > 0,
                "SELECT 1;",
                "ALTER TABLE ddpos_2_user ADD id_signature mediumint(7) DEFAULT 0;"
            ));

            PREPARE alterIfNotExists FROM @preparedStatement;
            EXECUTE alterIfNotExists;
            DEALLOCATE PREPARE alterIfNotExists;

But when I copy the query from the error message into phpmyadmin, or take the output directly from die($sql) into phpmyadmin, the procedure works fine without errors, and the column id_signature is created.

Why?

It looks like you are having trouble executing multiple lines of SQL inside the one string. You need to find out of PHP Class can handle running multiple lines.

Instead of getting the SQL to run dynamic SQL, just execute it yourself

$sql = 'SELECT IF(
    (SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = "' . $this->t($table) . '"
             AND table_schema = DATABASE()
             AND column_name = "' . $column . '"
    ) > 0,
    "SELECT 1;",
    "ALTER TABLE ' . $this->t($table) . ' ADD ' . $column . ' ' . $params . ';")';

Execute this one line, take that resulting string, and execute that.

Hey, it may means running two SQL commands, but it will work.

An alternative would be to write a stored procedure with that code and then call the stored procedure.

Please look at my answer to the post MySQL: Create index If not exists where I used dynamic SQL to create an index if an index does not exist. You could frame a stored procedure for yourself and call it from PHP.


How to capture workload from production to replay on test? I want to perform performance testing on my test server.

How can I capture workload from production and replay on my test server?

Basically I'm looking for detailed steps so that I can replay the workload from production to my test environment.

You can record the production workload with SQL Server Profiler and replay it against the test environment. There might be a bottleneck because the Profiler only replays from one computer. If you need more load from several computers (and have SQL Server 2012) you could use Distributed Replay (as long as you don't run into the same bugs with TVPs in traces as I've run into).

Note: the distributed replay requirement for SQL 2012 is for the replay client, you can use a 2008 trace file and target a 2008 instance if you want. See Distributed replay requirements


Why one database is so much slower than other? The same query on postgresql takes 9 min on one database, on other only 5 sec.

I use the same postmaser, the same user. I connect directly with psql.

There seems to be no differences in database settings:

psql dbone -c 'SELECT * FROM pg_settings;' > pg_settings_dbone.txt
psql dbtwo -c 'SELECT * FROM pg_settings;' > pg_settings_dbtwo.txt
diff pg_settings_dbone.txt pg_settings_dbtwo.txt

EIDT:

Databases should be the same (scheduled copy)

query

explain analyze select clinic.code_8,
    (select count(distinct visit.id)
            from patients lpatient
            join visits_patients vp on (vp.hq_id = lpatient.hq_id and vp.patient_queue_id = lpatient.ext_id and vp.status='N' and vp.state <> 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state <> 'C' and date_trunc('day', visit.start) = date_trunc('day', slot.slot_start))
            join timetables vtimetable on (vtimetable.hq_id = visit.hq_id and vtimetable.ext_id = visit.timetable_id)
            join clinics vclinic on (vclinic.hq_id = vtimetable.hq_id and vclinic.ext_id = vtimetable.clinic_id and vclinic.code_8 = clinic.code_8)
        where ((lpatient.hq_id, lpatient.ext_id) in (select hq_id, ext_id from patient_links where patient_id = patient.id))),
    (select count(distinct visit.id)
            from patients lpatient
            join visits_patients vp on (vp.hq_id = lpatient.hq_id and vp.patient_queue_id = lpatient.ext_id and vp.status='N' and vp.state <> 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state='N')
            join timetables vtimetable on (vtimetable.hq_id = visit.hq_id and vtimetable.ext_id = visit.timetable_id)
            join clinics vclinic on (vclinic.hq_id = vtimetable.hq_id and vclinic.ext_id = vtimetable.clinic_id and vclinic.code_8 = clinic.code_8)
        where (lpatient.hq_id, lpatient.ext_id) in (select hq_id, ext_id from patient_links where patient_id = patient.id)),
        stype.allow_series
from patients patient,
timetable_slots slot
    join visit_types stype on (stype.hq_id = slot.hq_id and stype.ext_id = slot.visit_type_id and stype.status='N')
    join timetables timetable on (timetable.hq_id = slot.hq_id and timetable.ext_id = slot.timetable_id)
    join clinics clinic on (clinic.hq_id = timetable.hq_id and clinic.ext_id = timetable.clinic_id)
where patient.hq_id = 1 and patient.ext_id = 234223 and slot.hq_id = 3 and slot.ext_id = 313245

fast query http://explain.depesz.com/s/jjC2

slow query http://explain.depesz.com/s/e26V

EDIT2:

Somehow indexes where corrupted on second db. Second db was copied without indexes?

There could be a bunch of reasons.  Chances are that the data is different in the database and the planner is generating different query plans as a result.  Other differences may include caching differences (maybe the data in one query is in the cache and the other one is not?)....

There are two approaches you can take to troubleshooting here.  The first is to only look at performance when we know the data is cached to the extent possible and the second is to only look at performance when we know the data is not cached.  To do the first, you run each query twice and discard the timing on the first result.  To do the second, we cat a hard drive to /dev/null between each run.   I mention this though because it is very important to compare apples to apples here.  If you find that caching is the whole difference (which it probably is not) then you know the issue is in usage patterns in the two dbs.  So assuming we are looking at cache-active responses, here's my suggestion:

Run each query once by itself, followed by EXPLAIN ANALYZE [query] and post the explain analyze results in your question if you need further help (the first run is just to ensure caches are being used to their fullest).  Note you need to post the query along with the query plan for us to make much sense of it.
A very useful tool for looking at explain output is http://explain.depesz.com/ and I would suggest making use of that too.
In the explain output look for sequential scans returning a large number of rows that should be unnecessary (because only a few rows are searched for) and mismatches between estimates and actual return values.  For seq scans returning many more rows than needed (say more than 10x as much), then chances are the query might need some tuning or an index might solve the problem.

A second point is that a 5 sec query IME is usually a reporting query of some sort (people don't like to wait 5 sec for data entry).  If this is the case, then it may be aggregating a fair bit of data, and the data set size may be much larger on the db which is posing the problem.  More memory can obviously help here but indexes only get you so far before a physical order search is quite a bit faster.  I have a few 5-15 sec data entry queries but they are rare and much more like reporting queries ("show me all unpaid invoices between the following dates" which return thousands of rows for processing) and only 5-15 sec on db's with gigabytes of accounting data.  If 5 sec is sufficient it is usually either reporting or closely related.