Getting @@ROWCOUNT when there is an insert query in SQL-SERVER? Hope you are doing well.
I have a procedure as you can see below :

ALTER Procedure [dbo].[OpenExcel]
  @TableName varchar(2000),
  @FileName  varchar(2000),
  @ResultOpenExcel tinyint output

AS
Begin

 Declare @Rowflag Bit=0
 Set     @ResultOpenExcel=0

   DECLARE @Provider Nvarchar(2000);
   SET @Provider = 'Microsoft.ACE.OLEDB.12.0';

   DECLARE @SQL Nvarchar(MAX)=
   ' SELECT * INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName +';hdr=yes'+''',''select * from [Sheet1$]'' );

     insert into ' + @TableName+'
     select *
     from #TTT
     drop Table #TTT'

   EXEC (@SQL)

IF(@@ROWCOUNT>0)
SET @RowFlag=1

Print @@Rowcount

if(@RowFlag=1)
               set @ResultOpenExcel=1
            else
               set @ResultOpenExcel=0

               Return @ResultOpenExcel
END

The problem is that @@ROWCOUNT won't work for the insert statement I guess !!! How Can I solve this issue?
Thanks in advance

@@ROWCOUNT is set after each statement so the value printed is for the SET statement instead of the INSERT statement.

Save the @@ROWCOUNT in a local variable in order to use it later in code:

EXEC (@SQL);

DECLARE @RowCount int = @@ROWCOUNT;

IF @RowCount > 0
BEGIN
    SET @RowFlag = 1;
END;

PRINT @RowCount;


How to set IN values on an update (SQL Server)? Quick question, I did try to google but couldn't find an answer.

I have 3 separate queries here, I want to do SET IN ('price1', 'price2', 'price3') but when I tried I got an error. How would I do this?

Update apotable
set price1 = '0.00'
where po_number IN ('x', 'y', 'z')

Update apotable
set price2 = '0.00'
where po_number IN ('x', 'y', 'z')

Update apotable
set price3 = '0.00'
where po_number IN ('x', 'y', 'z')

Update apotable
   set price1 = 0,
       price2 = 0,
       price3 = 0
where po_number IN ('x', 'y', 'z');

Note that numbers should not be enclosed in single quotes 0 is a number, '0.00' is a character literal (aka "string").

There is no need to search the web for the syntax of a SQL statement.
That is all documented in the manual:
https://msdn.microsoft.com/en-us/library/ms177523.aspx


PostgreSQL not using varchar index when ordering by primary key My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it grew, queries to it became slower and slower. This table is used for calculating global statistics periodically and for showing last 20 stats of a selected device.
At the moment, there is >150m rows in this table, and in the place where we pull the last 20 rows for a device, the query takes more than a minute to execute and causes a huge memory usage spike on the server.

The structure of the table is like this:

       Column        |            Type             |

---------------------+-----------------------------+
 id                  | bigint                      |
 datetime            | timestamp without time zone |
 device              | character varying(225)      |
 file                | character varying(225)      |
 duration            | character varying(225)      |

Indexes:
    "statistics_pkey" PRIMARY KEY, btree (id)
    "statistics_device_idx" btree (device)

There are a few more columns with informations, but they aren't included in any queries and hold no interesting data, mostly text.

When searching for the statistics, we select the device and the date, the query fetches 20 rows of that device that are before the selected date:

SELECT * FROM statistics WHERE device = '7329_218550' and datetime < NOW() ORDER BY id DESC LIMIT 20;

This query causes the trouble. Explain of it:

Limit  (cost=0.57..1029.06 rows=20 width=553)
  ->  Index Scan Backward using statistics_pkey on statistics  (cost=0.57..11129443.84 rows=216424 width=553)
        Filter: (((device)::text = '7329_218550'::text) AND (datetime < now()))

As you can see, the device index is not being used at all, I was surprised as it's the main column we use for filtering and it had an index.
I found out, that by just removing the ORDER BY from the query, the index is being used properly, and queries take ~0.004s which is a tremendous difference:

Limit  (cost=0.57..78.00 rows=20 width=553)
  ->  Index Scan using statistics_device_idx on statistics  (cost=0.57..837920.42 rows=216424 width=553)
        Index Cond: ((device)::text = '7329_218550'::text)
        Filter: (datetime < now())

I  found out, that using the datetime field for ordering (since we want the last 20 rows anyway) and although the explain is more complex, it actually USES the index again! Which surprised me even more, because ordering by a primary key should be better than by some time field:

Limit  (cost=669994.92..669994.97 rows=20 width=553)
  ->  Sort  (cost=669994.92..670535.98 rows=216424 width=553)
        Sort Key: datetime
        ->  Bitmap Heap Scan on statistics  (cost=4138.01..664235.95 rows=216424 width=553)
              Recheck Cond: ((device)::text = '7329_218550'::text)
              Filter: (datetime < now())
              ->  Bitmap Index Scan on statistics_device_idx  (cost=0.00..4083.91 rows=216445 width=0)
                    Index Cond: ((device)::text = '7329_218550'::text)

Queries ordering by datetime are executing in ~3.5s, which is a lot more than without ordering, but it's still much better than ordering by the primary key.

Why is it like that, and is there any way to use the index properly and keep the ordering?

PostgreSQL thinks that it can avoid the expensive sort by using an index that returns the rows in sorted order and filter out the rows that do not match the conditions. This strategy turns out to be bad, either because of mis-estimates or an adversarial distribution.

Sorting by primary key is in no way faster than sorting by other indexed criteria. And in this case you are better off not having an index on the ORDER BY expression.

You should at least upgrade to the latest 9.4 bugfix release, but better to a recent version, because it is dangerous to use old and unsupported versions. They can contain bugs that may eat your data. Just because no such bug has manifested so far is no guarantee that it won't in the future.


How to hide the partition child tables in PostgreSQL? I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users.
I tried many searches but this kind of concept is not explained anywhere.
This is my hierarchy,

Master_table
  child_01_2013
  child_02_2013
  ....
  child_07_2019

so child table is in Monthly partition. I dont want to show all child table in my database. I am using Pg-admin 4 and DB visualizer(Free) tool for my works.
Kindly help me to resolve this.

If all you want is to remove the partitions from the schema view, you could simply move the partitions to another schema:

ALTER TABLE child_01_2013 SET SCHEMA otherschema;

Everything will continue to work as before, but the visual clutter will be gone.


Allow only unique combination of values for a composite primary key I created the following table called books:

CREATE TABLE IF NOT EXISTS `books` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I created another tablet called compare to compare any 2 books from the books table:

CREATE TABLE IF NOT EXISTS `compare` (
`id_1` BIGINT UNSIGNED NOT NULL,
`id_2` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`id_1`,`id_2`),
FOREIGN KEY (`id_1`) REFERENCES books(`id`),
FOREIGN KEY (`id_2`) REFERENCES books(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The previous works as expected but I need to force MySQL to allow ONLY unique combination of values in the compare table.

For example if I have the following row in compare table:

id_1 | id_2
------------
  1  |  2

I want to force MySQL to NOT allow me to insert the following row:

id_1 | id_2
------------
  2  |  1

So I want MySQL to allow only either 1,2 or 2,1 NOT both.

I am using 10.2.14-MariaDB - MariaDB Server

Since you use MariaDB 10.2, you can add a CHECK constraint, enforcing that the first id is less than the second (id_1 < id_2). CHECK constraints were added in version 10.2.1.

This has the limitation that you'll have to provide the ids for the INSERT statements in correct order (you won't be able to insert the (3,2) combination, even if (2,3) is not there):

ALTER TABLE compare
  ADD CONSTRAINT ids_unique_combination_chk
    CHECK (id_1 < id_2) ;

Tested at dbfiddle.uk.

If you don't want to have to hassle with your INSERT statements and procedures, you can use two VIRTUAL (or PERSISTENT) columns and a UNIQUE constraint.

Virtual columns are available in MariaDB since version 5.2 and several limitations were lifted in 10.2.1:

ALTER TABLE compare
  ADD small_id BIGINT AS (LEAST(id_1, id_2)) VIRTUAL,
  ADD big_id   BIGINT AS (GREATEST(id_1, id_2)) VIRTUAL,
  ADD CONSTRAINT ids_unique_combination_uq
    UNIQUE (small_id, big_id);

Tested at dbfiddle.uk, too: fiddle-2.


sql server authentication login failed I have SQL Server 2014 on my Windows Server 2012 R2. I can connect locally to SQL Server from Management Studio using Windows Authentication.

I decided to switch to SQL Server Authentication (because I need access from other computer). I created new Login using Management Studio, but get error: Login failed when trying to connect.

Then I changed password for sa and tried login as sa, but again get the same error:

I restarted SQL Server, but it didn't help. What I do wrong?

From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

Connect to SQL Server using Windows authentication. Run sp_readerrorlog and see the complete message which would have come in errorlog when you tried connecting with SQL Authentication. This message is not of much help. You can update the question with message

  From error log: Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

What is output of this query.

select serverproperty('IsIntegratedSecurityOnly')

It seems like authentication mode is Windows only. If above query returns 1. It means authentication mode is windows go to server . Right click on SQL Server select property and then click on security. Change authentication it to mixed mode by selecting radio button SQL Server and windows authentication mode and restart SQL Server service again.


Poor query performance We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do a statistics update with FULLSCAN. When it slows down, a sp_recompile "fixes" the issue, until the nightly statistics job runs again.

By comparing the slow and fast execution plans, I have narrowed it down to a specific table/index. When it runs slow it is estimating ~300 rows will be returned from a specific index, when it runs fast it estimates 1 row. When it runs slow it uses a Table Spool after doing a seek on the index, when it runs fast it doesn't do the Table Spool.

Using DBSS SHOW_STATISTICS, I graphed out the index histogram in excel. I would normally expect the graph to be more "rolling hills", but instead, it looks like a mountain, the highest point being 2x-3x higher than most other values on the graph.

If I update statistics on it, without FULLSCAN, it looks more normal. If I then run it with FULLSCAN again it looks like I described above.

This feels like a parameter sniffing issue, and specifically related to the (seemingly) weird index distribution above.

The proc takes in a table valued parameter, can parameter sniffing occur on a table valued parameter?

EDIT: The proc also takes 12 other parameters, some of which are optional, two of which are a start and end date.

Is the histogram odd, or am I barking up the wrong tree?

I am certainly comfortable trying to adjust the query and/or try to adjust my indexing. If that is the fix that is great, at that point my question is more about the skewed histogram.

I should mention that this is a PK IDENTITY clustered index. We have two systems that talk to each other, one a legacy system, one a new home-grown system. Both systems store similar data. To keep them in sync the PK on this table in the new system is incremented when things are added to the old system, even if the data doesn't come over (a RESEED is done). So there could be some gaps in the numbering in this column. Records are rarely, if ever, deleted.

Any thoughts would be greatly appreciated. I am more than happy to gather/include more info.

This ended up being related to parameter sniffing. It just so happened that some oddly formed versions of this query were being executed RIGHT AFTER the stats were rebuilt. So the cached plan was not representative of the majority of the calls. I used the trick of copying the date parameters to local variables and this is working just fine, with little to no impact on performance. This doesn't answer why the histogram looks so "off", but it does explain my performance issues.


Find accounts with passwords that are not complex I can see all the SQL Login passwords that enforce the password policy with this command

SELECT * FROM sys.sql_logins WHERE is_policy_checked = 1

However, it seems that when the enforce password policy is checked, it requires the next password change to enforce the policy and doesn't mean that current password does:

This MS article states

  Password complexity policies are designed to deter brute force attacks
  by increasing the number of possible passwords. When password
  complexity policy is enforced, new passwords must meet the following
  guidelines:

I can verify the above as follows

Create login with weak password:

CREATE LOGIN [TestLogin] WITH PASSWORD=N'weak', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Enforce the policy

ALTER LOGIN [TestLogin] WITH CHECK_POLICY=ON

at this point, the following query returns 1

SELECT is_policy_checked FROM sys.sql_logins WHERE name = 'TestLogin'

So is there anyway I can check which SQL accounts have a weak password? My guess is "no" as I would have thought this was a bit of a security vulnerability and the only way would be to change all the passwords when the policy has been set to check.

Maybe that will help
PWDCOMPARE (https://docs.microsoft.com/en-us/sql/t-sql/functions/pwdcompare-transact-sql?view=sql-server-2017)?

Some example:
https://dzone.com/articles/use-pwdcompare-to-find-sql-logins-with-weak-passwo

Credits @SQLRockstar link

First, let's build a list of weak passwords and save it to file:

$pwdList = Get-Content .\password_list.txt

Build a query and execute against SQL Server:

$SQLText = "SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('$password', password_hash) = 1;"
Invoke-Sqlcmd -Query $SQLText -ServerInstance $SQLServer
}


Strange behaviour with sample sizes for statistics updates I've been playing around investigating sampling thresholds with statistics updates on SQL Server (2012) and noticed some curious behaviour. Basically the number of rows sampled seems to vary under some circumstances - even with the same set of data.

I run this query:

--Drop table if exists
IF (OBJECT_ID('dbo.Test')) IS NOT NULL DROP TABLE dbo.Test;

--Create Table for Testing
CREATE TABLE dbo.Test(Id INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED, TextValue VARCHAR(20) NULL);

--Insert enough data so we have more than 8Mb (the threshold at which sampling kicks in)
INSERT INTO dbo.Test(TextValue)
SELECT TOP 1000000 'blahblahblah'
FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d;

--Create Index on TextValue
CREATE INDEX IX_Test_TextValue ON dbo.Test(TextValue);

--Update Statistics without specifying how many rows to sample
UPDATE STATISTICS dbo.Test IX_Test_TextValue;

--View the Statistics
DBCC SHOW_STATISTICS('dbo.Test', IX_Test_TextValue) WITH STAT_HEADER;

When I look at the output of the SHOW_STATISTICS I'm finding that the "Rows Sampled" varies with each full execution (i.e. the table gets dropped, recreated and repopulated).

For example:

Rows Sampled

318618
319240
324198
314154

My expectation was that this figure would be the same each time as the table is identical. By the way, I don't get this behaviour if I just delete the data and re-insert it.

It's not a critical question, but I'd be interested in understanding what's going on.

Background

Data for the statistics object are gathered using a statement of the form:

SELECT
    StatMan([SC0], [SC1], [SB0000])
FROM
(
    SELECT TOP 100 PERCENT
        [SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
    FROM
    (
        SELECT
            [TextValue] AS [SC0],
            [Id] AS [SC1]
        FROM [dbo].[Test]
            TABLESAMPLE SYSTEM (2.223684e+001 PERCENT)
            WITH (READUNCOMMITTED)
    ) AS _MS_UPDSTATS_TBL_HELPER
    ORDER BY
        [SC0],
        [SC1],
        [SB0000]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

You can collect this statement with Extended Events or Profiler (SP:StmtCompleted).

Statistics generation queries often access the base table (rather than a nonclustered index) to avoid the clustering of values that naturally occurs on nonclustered index pages.

The number of rows sampled depends on the number of whole pages selected for sampling. Each page of the table is either selected or it is not. All rows on selected pages contribute to the statistics.

Random numbers

SQL Server uses a random number generator to decide if a page qualifies or not. The generator used in this instance is the Lehmer random number generator with parameter values as shown below:

Xnext = Xseed * 75 mod (231 - 1)

The value of Xseed is computed as the sum of:

The low integer part of the (bigint) base table's partition_id e.g.

SELECT
    P.[partition_id] & 0xFFFFFFFF
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND P.index_id = 1;

The value specified in the REPEATABLE clause

For sampled UPDATE STATISTICS, the REPEATABLE value is 1.
This value is exposed in the m_randomSeed element of the access method's internal debugging information shown in execution plans when trace flag 8666 is enabled, for example <Field FieldName="m_randomSeed" FieldValue="1" />

For SQL Server 2012, this calculation occurs in sqlmin!UnOrderPageScanner::StartScan:

mov     edx,dword ptr [rcx+30h]
add     edx,dword ptr [rcx+2Ch]

where memory at [rcx+30h] contains the low 32 bits of the partition id and memory at [rcx+2Ch] contains the REPEATABLE value in use.

The random number generator is initialized later in the same method, calling sqlmin!RandomNumGenerator::Init, where the instruction:

imul    r9d,r9d,41A7h

...multiplies the seed by 41A7 hex (16807 decimal = 75) as shown in the equation above.

Later random numbers (for individual pages) are generated using the same basic code inlined into sqlmin!UnOrderPageScanner::SetupSubScanner.

StatMan

For the example StatMan query shown above, the same pages will be collected as for the T-SQL statement:

SELECT
    COUNT_BIG(*)
FROM dbo.Test AS T
    TABLESAMPLE SYSTEM (2.223684e+001 PERCENT)  -- Same sample %
    REPEATABLE (1)                              -- Always 1 for statman
    WITH (INDEX(0));                            -- Scan base object

This will match the output of:

SELECT
    DDSP.rows_sampled
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.[object_id], S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND S.[name] = N'IX_Test_TextValue';

Edge case

One consequence of using the MINSTD Lehmer random number generator is that seed values zero and int.max should not be used as this will result in the algorithm producing a sequence of zeroes (selecting every page).

The code detects zero, and uses a value from the system 'clock' as the seed in that case. It does not do the same if the seed is int.max (0x7FFFFFFF = 231 - 1).

We can engineer this scenario since the initial seed is calculated as the sum of the low 32 bits of the partition id and the REPEATABLE value. The REPEATABLE value that will result in the seed being int.max and therefore every page being selected for sample is:

SELECT
    0x7FFFFFFF - (P.[partition_id] & 0xFFFFFFFF)
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND P.index_id = 1;

Working that into a complete example:

DECLARE @SQL nvarchar(4000) =
    N'
    SELECT
        COUNT_BIG(*)
    FROM dbo.Test AS T
        TABLESAMPLE (0 PERCENT)
        REPEATABLE (' +
        (
            SELECT TOP (1)
                CONVERT(nvarchar(11), 0x7FFFFFFF - P.[partition_id] & 0xFFFFFFFF)
            FROM sys.partitions AS P
            WHERE
                P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
                AND P.index_id = 1
        ) + ')
        WITH (INDEX(0));';

PRINT @SQL;
--EXECUTE (@SQL);

That will select every row on every page whatever the TABLESAMPLE clause says (even zero percent).


Is it possible to spin up a free development / test instance of an AWS Redshift server to run test queries on? I have a traditional MS SQL Server background and am interested in looking into AWS Redshift. Microsoft offers a free download of their development version installer of SQL Server, and I was wondering if there's an equivalent for AWS Redshift so that I can run some test queries and check out the performance.

Redshift is a service so you cannot run it locally unless you want to buy AWS Outposts.

Nevertheless, you can try RS during Free Tier - trial period. After that price can be as low as $0.25 per Hour (+traffic+tax), billed hourly.


Install MongoDB on Debian I tried to install MongoDB using this command:

apt-get install mongodb=3.0.1

But the version 3.0.1 was not found.

How can I install it?

To install specific version of mongodb you need to follow certain steps documented on MongoDB site:

In my opinion it includes:

sudo apt-get install -y mongodb-org=3.0.1 mongodb-org-server=3.0.1 mongodb-org-shell=3.0.1 mongodb-org-mongos=3.0.1 mongodb-org-tools=3.0.1

You can opt for packages by omitting it from above list.

You can find detailed document here


What does this SQL Server icon mean? What does the icon of a server with a blue database symbol mean?

It's definitely SQL Azure. You can easily recognize it as the icon is azure, unlike the green one for a regular SQL Server instance

You can see the the video by Thomas LaRock here

How To Connect to SQL Azure database using SSMS


Foreign Data Wrappers & Windows Authentication I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw but after a successfull install I couldn't make it work, possibly due to the same issue I detail below.

After this i went to Multicorn and things where a bit better since there actual documentation for the usage of it.

After attempting to add a table from SQL Server I got the message:

 ERROR:  Error in python: OperationalError DETAIL:  (OperationalError)
 SQL Server message 18456, severity 14, state 1, line 1: **Login failed
 for user 'postgres'**. DB-Lib error message 18456, severity 14: General
 SQL Server error: Check messages from the SQL Server DB-Lib error
 message 20002, severity 9: Adaptive Server connection failed

So I got it that its an authentication error, the SQL Server works with Windows Authentication so my connection string (mssql+pymssql://postgres:postgres@10.0.0.10/ForeignDatabase) its rendered useless. After some more search i got to this answer:

  The check_mssql_server.py plugin will NOT work with Windows
  Authentication and requires SQL authentication.

  As of this time I do not believe anyone has created a MSSQL plugin
  that can use Windows Authentication.

Clearly not the same scenario but its also a Data wrapper operation giving a extremely similar error message.

So my question is: Is it true that there's no way of using a foreign data wrapper from PostgreSQL to SQL Server using Windows Authentication?. I did add a new user (postgres:postgres) to the SQL Server.

I will also like to add that the PostgreSQL 9.3 Server lives on an Ubuntu Server 14.04 and the SQL Server 2005 on a Windows Server 2003.

Ok, thanks to the guidance of the community I manage to got it to work... I believe its important for me to mention a few things you should take in account when attempting this (at least under the same environment I been trying).

My setup:

Ubuntu 14.04 <-> PostgreSQL 9.3
Windows Server 2003 <-> SQL Server 2005

Additional components:

SQL Alchemy 0.9.9
Multicorn 1.1.0

Im not gonna go into details on how to install this components since there's plenty of documentation online.

Once this is setup I took a_horse_with_no_name advice on changing SQL Server authentication to mixed authentication and created a new account, for the sake of the example I use postgres:postgres. Additionaly still on SSMS I defined SELECT permissions for user postgres in the table I wanted to get the data from tblTest.

Back in my PostgreSQL I created a new database (pgTest) and setup multicorn:

CREATE EXTENSION multicorn;

Created the server for our FOREIGN DATA WRAPPER:

CREATE SERVER alchemy_srv FOREIGN DATA WRAPPER multicorn options (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

And then (what it was supposed to be the last step) create the table that is going to get data from MS SQL Server:

CREATE FOREIGN TABLE pgTable (LogTime varchar,
                              LogUser varchar,
                              LogData varchar)
server alchemy_srv OPTIONS   (tablename 'tblTest',
                           db_url 'mssql+pymssql://postgres:postgres@10.0.0.0/RemoteDBName');

This shows no error, but once you throw a simple SELECT * FROM pgTable; I was getting the error I OP up there.

So I search a bit more and stumbled on this Google Groups posts labeled on a response as a BUG.

One of the offered solutions is getting rid of pymssql, as in:

apt-get uninstall python-pymssql; pip install pymssql

After this I tried again to create the table and getting data but nothing happened, i was just getting a blank table.

So I pip install pymssql --upgrade, once again create the table within PostgreSQL and voila! this time worked perfect.

Hopefully this will help someone else in the future, thanks for your guidance!


Postgresql: Select a table from different database and different host I know this is not new, but I have not found a solution, even though I have followed several posts in this forum.

I'm trying to show table from different database using dblink extension.
Installing dblink using :

CREATE EXTENSION dblink

And run successfully. In db1 then I try querying, like:

SELECT *
    FROM dblink('dbname=db_name2 port=5432
            host=xx.xx.x.x user=username_db2 password=password_db2',
    'SELECT * FROM tableFrom_Db2');

I always get notice like this:

  ERROR:  a column definition list is required for functions returning "record"

When I try replace SELECT * with a list of columns,  I get:

  ERROR: invalid input syntax for type boolean: "SELECT column FROM tableFrom_Db2"

To make sure that the connection is going well, I tried

SELECT dblink_connect('host=xx.xx.x.x user=username_db2 password=password_db2 dbname=db_name2');

And I get notice like :

  ERROR:  could not establish connection
  DETAIL:  FATAL:  no pg_hba.conf entry for host "IP_db1", user "username_db2", database "db_name2", SSL on
  FATAL:  no pg_hba.conf entry for host "IP_db1", user "username_db2", database "db_name2", SSL off

By the way What's wrong?

The second error message means, that your Postgres on db_name2 is not configured to allow connections from IP_db1. Unrelated to this problem: with modern Postgres versions it's usually better to use a foreign table, rather than the dblink module. – a_horse_with_no_name

You need to edit your pg_hba.conf file on the far host (x.x.x.x) to allow the connection. Don't forget to reload the server config files after for it to take effect. It is probably better to debug connection issues with "psql" rather than using dblink or postgres_fdw, those are just going to confuse the issue. – jjanes


Get all partition names for a table I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1.
I was able to generate a count of partitions using this related answer by Frank Heikens.

I have a table foo with an insert trigger that creates foo_1, foo_2 etc. dynamically. The partition for insert is chosen based on the primary key id, a range based partitioning.

Is it possible to display all partitions currently in place for table foo?

Use the first query from the answer you linked and add a simple WHERE clause to get the partitions of a single table:

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';


IN Clause causes Execution plan to change from Nested Loops to Hash Match I'm tuning a query and have discovered some behaviour I'm not clear about.

If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.

There only 7 rows returned in the result since there are only 7 items in the IN clause.

Thinking this was a bit odd, I started trying to get both parts of the query behaving individually, however no matter what I've tried the IN clause causes the long execution time.

Looking at the execution plan I can see that it's taking some joins and converting them from Nested Loops to Hash Match.

I've diced up the SQL so the single SELECT is now split between a View, CTE and SELECT.  I would have expected the IN clause to not impact what is happening inside of the View, but it is.

The execution plans are: with the IN clause and without the IN clause.  Please be gentle regarding the quality of the query; it's a work in progress on an inherited project and I'm by no means a proper DBA.

Selecting all 1.8 million rows from the View takes ~1 minute; executing the CTE by itself takes ~3 seconds; executing the full query takes ~3 minutes.

How can I convince SQL Server to maintain the Nested Loops join instead of switching to Hash Match when I add in the IN clause?  Or is there something else that I should be trying?

Well, you can force nested loops by using OPTION(LOOP JOIN) however, I would advise using OPTION(LOOP JOIN, MERGE JOIN) this is working with the optimiser rather than against it and is saying do what you want as long as its not a hash match.

But I would also dig a little deeper. With the IN clause it is possible that SQL is performing multiple index scans as opposed to a single table scan, so you may need to use an WITH(INDEX(0)) instead.

If you add SET STATISTICS IO at the top of the batch and run it with and then without the IN clause and check the number of physical and logical reads for each, as well as the read-ahead reads, this will prove or disprove my theory.


SSMS 2016 Maintenance Plan Designer Perms We have some older maintenance plans that were built with SSMS 2014 on a Sql Server 2014 instance.  The connection defined for the plan uses a Sql Server account with sysadmin perms.  When you edit with SSMS 2014, all works just fine.  On my local box, I have SSMS 2016, also have sysadmin perms.  When I edit the plan, the "Notify Operator Task" has a blue indicator that something is wrong

When I try to edit the task, I get the following error:

The sql error log shows the following errors:

Error: 18456, Severity: 14, State: 8.
Login failed for user 'SqlAccount'. Reason: Password did not match that for the login provided. [CLIENT: xx.x.x.x]

The SqlAccount is the one defined in my "manage connections" from the maintenance plan.  Is there some new permission that SSMS 2016 needs?  Are plans created in SSMS 2014 not fully compatible with the SSMS 2016 editor?

In your maintenance plan's "Manage Connections" define a new connection with integrated Windows authentication. Use that connection with your Notify Operator Task (set it here right-clik->Properties->Connection) and it should work.


Optimizing table/indices for getting the latest row (filtering by one additional column) I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this:

[ConfigurationID]       INT IDENTITY (1,1) NOT NULL,
[OrganizationID]        INT NOT NULL,
[TimestampUtc]          DATETIME NOT NULL,
[ConfigurationData]     NVARCHAR (MAX) NOT NULL,
[ChangedBy]             NVARCHAR (256) NOT NULL,
[Comment]               NVARCHAR (MAX) NOT NULL,
[ChangeType]            INT NOT NULL

The TimestampUtc will always be increasing (I won't ever be INSERTing "back-dated" entries into the table), and the rows won't ever be UPDATEd (I'm only INSERTing new rows). For some OrganizationIDs there will be lots of rows, for some very few, and a new row for any OrganizationID may be INSERTed at any time.

If needed, I can guarantee uniqueness of TimestampUtc (but it would be great to have a solution that didn't need that).

INSERTs are relatively rare (at most dozens of times per day, but typically much less than that), reads are very frequent (essentially on every web request to my application).

My goals are:

Getting the ConfigurationData with the latest TimestampUtc for a given OrganizationID should be extremely fast regardless of the size of the table
INSERT performance doesn't matter too much, but I'd like to avoid horrible index fragmentation if at all possible (so my first idea of a unique clustered index on OrganizationID ASC, TimestampUtc DESC is probably not a great idea).

Questions

I know I can denormalize and just store the latest ConfigurationData in one table and the historical log of previous values in another table, but is it possible to meet my goals with just one table? What's the best way to do it? (I.e. what's the best index structure? do I need to change anything about the table schema, etc.?)

Given the low rate of inserts, your proposed index will be completely fine, and perfect for the usage goal.

Given a fresh index with fill factor 100%, and sufficient history for each organisation to fill a page, there will be a page split on the first subsequent insert for each organisation. But then there won't be another page split for that organisation until the new page is filled up.
Even these splits and fragmentation can be mitigated by starting with a <100 fill factor, and regular reorganise.

Aaron's thought regarding using non-clustered was probably because that would allow you to cluster on the always ascending identity column, ensuring the only splits and fragmentation you will get is in a very compact separate index. But I suspect he was only mentioning that in the context of you wanting very much to avoid fragmentation, and not as something really necessary in this scenario.


Assign to NEW by key in a Postgres trigger In the trigger body, how can I assign a value to NEW by it's field name?
That's what I want to do:

some_key = "some_column";
NEW[some_key] = 5;

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also, misleadingly, called "procedure") with a function body and 0-n triggers (without body) calling this function.
The special variable NEW in plpgsql trigger functions is neither a map nor an array; it's a record holding the new row:

NEW
Data type RECORD; variable holding the new database row for
INSERT/UPDATE operations in row-level triggers. This variable is
unassigned in statement-level triggers and for DELETE operations.

Assigning to a field (or column) of NEW is simple. The documented assignment operator is :=. (Since Postgres 9.4 also =.)
NEW.some_key := 5;

What you seem to be looking for is to parameterize the column name, which isn't quite as simple.
The additional module hstore provides the #= operator. (It's included in pretty much all standard distributions.) Install the module once per database with:
CREATE EXTENSION hstore;

Then you can:
NEW := NEW #= '"some_key"=>"5"'::hstore;

Sets the column some_key to '5' - if the column exists.

An explicit cast to hstore is optional. The operator #= coerces a string literal to the right data type automatically.
hstore only stores text strings, so a given literal for the value may have to be cast twice - a very minor drawback compared to alternative solutions.
The given string literal has to fit the data type of the column, or an exception is raised.
If no column with the given name exists, nothing is changed, no exception raised.

Related answer with details and an alternative solution:

How to set value of composite variable field using dynamic SQL

Code example
CREATE OR REPLACE FUNCTION trg_tbl_ins_bef()
  RETURNS trigger AS
$func$
BEGIN
   NEW := NEW #= '"some_key"=>"5"';
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER ins_bef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_ins_bef();

Postgres 11 or later allows to replace the misleding term PROCEDURE (the old syntax will keep working for the foreseeable future):
...
FOR EACH ROW EXECUTE FUNCTION trg_tbl_ins_bef();


Free space of mdf and ldf does not match with database free space In SSMS I saw file size related properties and found below details for one database. Here values does not match with other properties. Here size of the mdf, ldf and total size matches with other values under each window. But Available free space of mdf and ldf if added then it does not equal to Available free space shown in shrink database window and free space shown in database properties. This is true for any database. Why is it so? Please can anyone explain the logic behind this?

Under database properties:
Size:               91.31 MB
 Space available:13.40 MB
Under database file properites:
mdf size: 17 MB
ldf size: 75 MB
under shrink database:
Currently allocated size: 91.31 MB
Available free space:     13.40 MB
under shrink file-for data file:
currently allocated size: 16.38 MB
Available free space:     12.63 MB
under shrink file-for log file:
currently allocated size: 74.94 MB
Available free space: 55.62 MB

This doesn't really seem all that crazy, but note that some of the UI dialogs might not have completely up-to-date information (this is why we have things like DBCC UPDATEUSAGE), and rounding can also be involved in some of those calculations. Finally, the dialogs show you total space for the whole database, but unallocated space is only calculated for the data files, not the log.

Let's coalesce some things.

Database properties and shrink database show the same thing (not that you should ever be in the shrink database UI anyway!).
Database file properties show 17 + 75 = 92 which, with rounding before addition, is probably the same 91.31 in 1.
For space allocated, shrink for individual files show 16.38 + 74.94 = 91.32 - again, probably some rounding there, otherwise exactly matching 1.
For space available, shrink for individual files is the only place where I suspect a real discrepancy, and this is because the UI is inconsistent about where it gets its data, and some of these places are subject to the caching that necessitates DBCC UPDATEUSAGE.

Let me take a look at what these different dialogs run for my local copy of AdventureWorks2012 (with certain tables enlarged from this script).

EXEC sp_spaceused;

This returns (first resultset only):

database_size    unallocated space
-------------    -----------------
   1545.81 MB          6.67 MB

Essentially runs this, which - I've confirmed via trace - is roughly the same query executed from the database properties and database shrink dialogs (I've carved out the irrelevant parts from the stored procedure, and added an outer query to represent the math that SSMS does for display):

SELECT database_size = DbSize*8.0/1024 + LogSize*8.0/1024,
  [unallocated space] = (DbSize-SpaceUsed)*8.0/1024
FROM
(
  SELECT
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
       WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
    SUM(a.total_pages) AS [SpaceUsed],
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
       WHERE df.type in (1, 3)) AS [LogSize]
  FROM sys.partitions p
    join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
) AS x;

This returns a match:

database_size    unallocated space
-------------    -----------------
    1545.8125             6.671875

These dialogs all show this information correctly. Database Properties dialog:

Shrink Database dialog:

The shrink file dialogs, on the other hand, run a slightly different query (again this is carved/adapted for convenience):

SELECT SUBSTRING(name, CHARINDEX('_',name)+1, 4),
  [Currently allocated space] = size/1024.0,
  [Available free space] = (Size-UsedSpace)/1024.0
FROM
(
  SELECT s.name,
    CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)*CONVERT(float,8) AS [UsedSpace],
    s.size * CONVERT(float,8) AS [Size]
  FROM sys.database_files AS s
  WHERE (s.type IN (0,1))
) AS x;

Note, too, that in addition to getting size data from a function instead of a DMV, the predicates have not been updated for new file types, like filestream/hekaton.

Results:

        Currently allocated space    Available free space
----    -------------------------    --------------------
Data                         1517                  7.9375 -- wrong
Log                       28.8125               25.671875 -- wrong

The problem is the FILEPROPERTY() function, which is not guaranteed to be up to date (even after DBCC UPDATEUSAGE(0); is run; more below). This ends up with this misleading information on the dialogs:

Note, again, that 6.67 MB was never really accurate, since this is only measuring the total database size - the number of pages allocated, completely disregarding the log.

In all honesty, if you want accurate reporting of space used in the database, stop using the mickey mouse UIs which run all kinds of different queries to figure this out, and stop using the shrink file dialogs for retrieving information. These are clearly subject to stale data problems in certain cases. Run an actual query against a source you can trust. Here is what I prefer:

DECLARE @log_used DECIMAL(19,7);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;

DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.[partition_id] = a.container_id;

;WITH x(t,s) AS
(
  SELECT [type] = CASE
    WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END,
    size*8/1024.0 FROM sys.database_files AS f
)
SELECT
  file_type = t,
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END
FROM x;

This query returns three numbers that should look very familiar, and one that should not:

file_type    size           available
---------    -----------    ----------
data         1517.000000     6.6718750
log            28.812500    17.9008512

Note that DBCC SQLPERF is also slightly prone to issues with space usage, for example after running:

DBCC UPDATEUSAGE(0);

The above query yields this instead:

file_type    size           available
---------    -----------    ----------
data         1517.000000     8.0781250
log            28.812500    17.8669481

sp_spaceused now yields matching numbers as well (1545.81 MB / 8.08 MB), even though - again - that is only the space available in the data file(s), and the database property and database shrink dialogs are "accurate" as well (but the shrink file dialogs are still way off - FILEPROPERTY() does not seem to be affected by UPDATEUSAGE at all):

Oh, and might as well show what Windows Explorer thinks of these files, so you can relate to the calculations made to determine MB:

How accurate this all needs to be, of course, depends on what you're going to do with the information.


adding ORDER BY decreases speed by 15x I have a simply query:

SELECT DISTINCT images.*
FROM   `images`
WHERE  `images`.`user_id` IN (SELECT following_id
                                FROM   `follows`
                                WHERE  `follows`.`follower_id` = 9
                                       AND `follows`.`accepted` = 1)
LIMIT  15 offset 0

This executes in less than 1 ms.

Adding an ORDER BY on column that is indexed:

    SELECT DISTINCT images.*
    FROM   `images`
    WHERE  `images`.`user_id` IN (SELECT following_id
                                    FROM   `follows`
                                    WHERE  `follows`.`follower_id` = 9
                                           AND `follows`.`accepted` = 1)
ORDER  BY id DESC
LIMIT  15 offset 0

takes 15 ms to execute. There are a mere 7,000 images in the table. Should it really take 15 fold time to order an indexed column? What can I do to decrease such a performance hit?

Running on:

Server version: 5.6.11-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

EXPLAIN:

+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+
| id | select_type  | table       | type        | possible_keys                     | key                  | key_len | ref                      | rows | Extra                                                        |
+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | ALL         | NULL                              | NULL                 | NULL    | NULL                     | NULL | Using where; Using temporary; Using filesort                 |
|  1 | SIMPLE       | images    | ref         | user_id                           | user_id              | 5       | <subquery2>.following_id |    3 | NULL                                                         |
|  2 | MATERIALIZED | follows     | index_merge | accepted,follower_id,following_id | follower_id,accepted | 5,2     | NULL                     |   19 | Using intersect(follower_id,accepted); Using where; Distinct |
+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+

Thanks

Should it really take 15 fold time to order an indexed column?

Well it depends where this column is being used. Just because it's indexed (and presumably in some order) doesn't mean that using it in a query will magically make it fast.

Assume there are two indexes in the table, for id (ordered by id) and for user_id (ordered by user_id). If MySQL uses the latter to quickly look up the rows that satisfy the WHERE predicate then the result is that it might have all the rows in user_id order.

You want them in id order, so MySQL has to order them. Your previous query didn't have to do ordering. It should come as no surprise that more work == more time.

  What can I do to decrease such a performance hit?

Look at the execution plan. MySQL has EXPLAIN.

You should also make your queries as simple as you can. I assume your images table has a primary key, so the DISTINCT in SELECT DISTINCT images.* FROM images ... doesn't really make sense.

Also by taking all the columns (images.*) you're forcing MySQL to acquire every attribute for each row that is returned, whereas being more selective (no pun intended) with your columns might result in less data needing to be returned. Simplifying somewhat: less columns gives MySQL an opportunity to satisfy the query using only small indexes, rather than the table data.


MySQL sharding and stored procedures Is there a way to dispatch stored procedure invocation on particular shard, based on sharding key (passed as procedure argument or somehow separately)?

For example we have a table, sharded between 3 nodes using HASH sharding strategy. All rows with key A are stored on node 1. I'd like to be sure that when I run procedure(A), it applies on node1.

We are going to use Mysql Fabric tool, but other options considered as well.

Off the wall...

Turn the Procedure into a function.  Simply return 0, or something irrelevant.
Instead of CALLing the Procedure, invoke the Function this way:

SELECT 0 WHERE key = 'A' AND routine(key);

The hope is that the sharding will kick in for the first part of the WHERE and send the SELECT only to the desired shard.

BTW, it appears that Fabric is being decommissioned.


Error 1239: Incorrect foreign key definition While creating primary keys and foreign keys for my tables I got the error 1239 and apparently I'm the only one having this error as I could not find anything with a Google search. I was wondering if anyone can tell me what went wrong?

The foreign key error happened specifically when I was trying to link invoice.CUS_CODE to customer.CUS_CODE with:

alter table invoice add constraint CUS_CODE
    foreign key (CUS_CODE) references customer.CUS_CODE

I also tried disabling foreign_key_checks but no luck so far.

Here's the tables, and some sample data:

CREATE TABLE CUSTOMER(
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-
2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-
1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-
2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-
2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-
1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-
3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-
1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-
2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-
7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-
3809','0');

CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
INSERT INTO INVOICE VALUES('1001','10014','2016-01-
16','24.90','1.99','26.89');
INSERT INTO INVOICE VALUES('1002','10011','2016-01-
16','9.98','0.80','10.78');
INSERT INTO INVOICE VALUES('1003','10012','2016-01-
16','153.85','12.31','166.16');
INSERT INTO INVOICE VALUES('1004','10011','2017-01-
16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1005','10018','2017-01-
16','70.44','5.64','76.08');
INSERT INTO INVOICE VALUES('1006','10014','2017-01-
16','397.83','31.83','429.66');
INSERT INTO INVOICE VALUES('1007','10015','2017-01-
16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1008','10011','2017-01-
16','399.15','31.93','431.08');

Syntax error:

alter table invoice add constraint CUS_CODE
    foreign key (CUS_CODE) references customer.CUS_CODE;

Correct syntax:

alter table INVOICE add constraint CUS_CODE
    foreign key (CUS_CODE) references CUSTOMER(CUS_CODE);

customer.CUS_CODE is treated as dbname.tablename.  What is needed is tablename(columnname) or dbname.tablename(columnname)


Windows Server 2012 R2 Essentials and SQL Server 2014 First off, I am not a professional IT person. I purchased a server from Dell for our extremely small business. It came with Windows Server 2012 R2 Essentials already installed. Please note, the main reason I got this server was for a piece of design software we use that must utilize SQL Server in order for all the workstations to share the data files. The software company came to set things up for me and said we can’t install SQL Server 2014 on a domain controller (still a bit over my head as to why).

My question is as follows:

– Is there a way to format the server and reinstall WS 2012 R2 E without making it a domain controller so the SQL Server 2014 will work?

Additional info, in case it may be relevant:

This server is new with no pertinent info or data on it so it could be wiped (assuming this means re-installing WS on it).
This is an AutoDesk product.
We are using Microvellum.

I would ask your vendor why they can't install SQL Server on a domain controller because the documentation seems to indicate otherwise.

See the Installing SQL Server on a Domain Controller section of Hardware and Software Requirements for Installing SQL Server 2014

  For security reasons, we recommend that you do not install SQL Server
  2014 on a domain controller. SQL Server Setup will not block
  installation on a computer that is a domain controller, but the
  following limitations apply:

  You cannot run SQL Server services on a domain controller under a local service account.
  After SQL Server is installed on a computer, you cannot change the computer from a domain member to a domain controller. You must
  uninstall SQL Server before you change the host computer to a domain
  controller.
  After SQL Server is installed on a computer, you cannot change the computer from a domain controller to a domain member. You must
  uninstall SQL Server before you change the host computer to a domain
  member.
  SQL Server failover cluster instances are not supported where cluster nodes are domain controllers.
  SQL Server Setup cannot create security groups or provision SQL Server service accounts on a read-only domain controller. In this
  scenario, Setup will fail.

So there are a few limitations, but I don't think these will apply in your situation. Using a domain account instead of a local service account shouldn't be a problem and I don't think you will use a cluster if you only have one server. Your domain controller probably isn't read only either.

The process is also documented in the Autodesk Vault knowledge base so I suppose it's supported for at least some of their software.

Microvellum's documentation seems to suggest the installer will check and you must not install SQL Server on a domain controller, but in fact that will only generate a warning but still allow you to continue, and it will work as long as you don't try to use a built-in service account.

Windows 2012 R2 essentials is also a supported operating system if you look at the Operating System Requirements on that page as long as it's 64bit.

That being said. If your vendor insists, you don't need to reinstall the operating system to remove the domain controller role from the installed operating system. The steps to demote a domain controller are documented.

Be warned though, if your workstations use a domain account to log in to the network and you remove the domain controller, your users may lose access to their PC's and the network, so you should be very careful when considering that route.

Also note one of the limitations of installing SQL Server on a domain controller is that you can not demote or promote (i.e. remove the domain controller role, or make your server a domain controller) once SQL Server is installed on the machine. In order to do so you must first uninstall SQL Server and then reinstall it afterwards.

As a last note, if you really can't install SQL Server on your machine because it's a domain controller, and you still need the domain controller role for client authentication, you could consider running a virtual machine on your server and run SQL Server inside that VM.

You should always verify licensing with your vendor, but this seems to suggest it could be a viable option without having to purchase a new server or an additional license:Understanding Licensing for Windows Server 2012 R2 Essentials and the Windows Server Essentials Experience role

  The Hyper-V role and Hyper-V Manager console are now included with
  Windows Server 2012 R2 Essentials! The server licensing rights have
  been expanded, allowing you to install an instance of Essentials on
  your physical server to run the Hyper-V role (with none of the other
  roles and features of the Essentials Experience installed), and a
  second instance of Essentials as a virtual machine (VM) on that same
  server with all the Essentials Experience roles and features.


performance issue on using ip2location for group of ips In admin panel I want to show counting users by country. I have in database these tables

CREATE TABLE `cities` (
  `id` int(11) UNSIGNED NOT NULL,
  `country_id` int(11) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `country` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ips` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ip_city` (
  `id` int(10) UNSIGNED NOT NULL,
  `ip_from` int(10) UNSIGNED DEFAULT NULL,
  `ip_to` int(10) UNSIGNED DEFAULT NULL,
  `city_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_fk` (`country_id`);

ALTER TABLE `country`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ips`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ips_user_id_foreign` (`user_id`);

ALTER TABLE `ip_city`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `city_index` (`id`),
  ADD KEY `idx_ip_from` (`ip_from`),
  ADD KEY `idx_ip_to` (`ip_to`),
  ADD KEY `idx_ip_from_to` (`ip_from`,`ip_to`),
  ADD KEY `city_index_fk` (`city_id`);

ALTER TABLE `cities`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `country`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ips`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ip_city`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cities`
  ADD CONSTRAINT `country_fk` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ips`
  ADD CONSTRAINT `ips_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ip_city`
  ADD CONSTRAINT `city_index_fk` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I write this query:

select
country.name,
count(*)

from (
    /*get last ips */
SELECT
    INET_ATON(m1.ip) AS ip
FROM
    ips m1
LEFT JOIN ips m2 ON
    (
        m1.user_id = m2.user_id AND m1.id < m2.id
    )
WHERE
    m2.id IS NULL
    ) a
/*end of getting users ips (around 3500) */
inner JOIN ip_city

on ip between `ip_from` and `ip_to`

inner join cities on ip_city.city_id = cities.id
inner JOIN country on  cities.country_id = country.id

group by country.id

but it very slow.
Problem is  in :

inner JOIN ip_city

on ip between `ip_from` and `ip_to`

for every ip it consuming around 1 second.
I found this query

SELECT
    *
FROM
    (
    SELECT
        *
    FROM
        ip_city
    WHERE
        ip_to >= INET_ATON('8.8.8.8')
    LIMIT 1
) AS tmp
WHERE
    ip_from <= INET_ATON('8.8.8.8')

It work very fast but it work only for one ip.
How to combine these 2 querys or how to optimize first query ?

Please qualify all columns with what table they are in.  Meanwhile, I will guess that ip_from and ip_to are in ip_city.

A problem is that the tables are over-normalized.  ip_city -> cities -> country instead of having a single table slows down the query.

But the main problem is that there is no good index for ip between ip_from AND ip_to.  Any index with ip_from and/or ip_to can, at best, limit the effort down to scanning half the table.  That's O(N).  To get to O(1), see http://mysql.rjweb.org/doc.php/ipranges .  Sorry, it is rather involved.

A first cut at improving your existing schema:

CREATE TABLE `cities` (
  `id` MEDIUMINT UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `country_code` CHAR(2) CHARACTER SET ascii NOT NULL,
  `latitude` FLOAT DEFAULT NULL,
  `longitude` FLOAT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ips` (
  `ip` varchar(39) CHARACTER SET ascii NOT NULL,
  ip_packed BINARY(16) NOT NULL
  `user_id` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY(ip)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE `ip_city` (
  `ip_from` BINARY(16) DEFAULT NULL,
  `ip_to` BINARY(16) DEFAULT NULL,
  `city_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY(ip_from, ip_to),
  INDEX(city_id)  -- if desired
) ENGINE=InnoDB DEFAULT;

That included getting rid if id when there were good 'natural' PKs, handling IPv6, using standard 2-letter country_codes, tossing the country table, using ascii where appropriate, etc.

See my link for further discussion, including why FLOAT is better.


Update year alone in date (Oracle 11g) When I checked one of my client's database, I found some mistakes which will make problems.
The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the result is 01-01-0001. How can I get rid of this problem. Is it possible to update the year alone in these dates.

I can find the dates with this problem by using this query

  select from_date from date_table where length(extract(year from from_date))='2';

Can I update this using the logic that

Find the dates using the above query
Update the year by checking the condition that if year is between '00' and '13' prefix '20' with the year else prefix '19'

How can I do this? Can I do it using Oracle query or should I write code for doing so?

Database is Oracle 11g R2 working in RHEL5.

I would recommand always to use the appropriate datatypes, operators and functions and explicit conversion  and not to use string literals and string processing and implicit conversion.

Look at your statement

select from_date
from date_table
where length(extract(year from from_date))='2'
/

The return type of the length statement is clearly a number. So why do you compare it with a string literal? Something has to be converted implicitly. The number result of the length function to a string or the string literal '2' to a number. Do you know which one is converted? Do you know if there can any problems arise from this conversion? Is there a special reason why you take a string constant instead a number constant?

As far as I know the string constant will be converted to a number and I can't see any problems with this conversion. I also think that there is no special reason why you take a string constant instead of a number constant. But why bother about that questions?

Use a number literal and write

select from_date
from date_table
where length(extract(year from from_date))=2
/

I think you want to find all rows where from_date by the implicit conversion to a very early date and therefore is before 1st January 100. So why string processing? Use date processing and write

select from_date
from date_table
where from_date<to_date('0100-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

If there is an index on the from_date column this query may be able to use the index.

This query will also find rows with a from_date before '0010-01-01 00:00:00' and even dates before '0000-00-00 00:00:00'. Also it can be easily adjusted to other timestamps. But if you want to explicitly use the year ( and you are sure that there are no dates before 0 B.C.) you can

select from_date
from date_table
where extract(year from from_date) between 0 and 99
/

If there is an index on the from_date column this query may not be able to use the index.

Adding 2000 years to a date can be done by adding INTERVAL '2000' YEAR(4)

UPDATE table_date
SET from_date  = from_date+INTERVAL '2000' YEAR(4)
WHERE to_date <to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

UPDATE table_date
SET from_date  = from_date+INTERVAL '1900' YEAR(4)
WHERE to_date >=to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

Because you started with string processing by inserting rows with the date represented as strings you can also make your correction using string processing. If you did not use a time part when inserting the rows you could/should omit the HH24:MI:SS format part in the following statements

UPDATE table_date
SET from_date  = to_date('20'||substring(to_char(from_date,'YYYY-MM-DD HH24:MI:SS'),2),'YYYY-MM-DD HH24:MI:SS')
WHERE to_date <to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

UPDATE table_date
SET from_date  = to_date('19'||substring(to_char(from_date,'YYYY-MM-DD HH24:MI:SS'),2),'YYYY-MM-DD HH24:MI:SS')
WHERE to_date >=to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/


Do you have to use a shared disk with AlwaysOn FCI? Do you have to use a shared disk with AlwaysOn FCI in a 2-Node FCI? If not and if you have a 2-Node FCI and each node has a local SAN Disk can failover be automatic and will the whole instance failover automatically? My concern is the single point-of-failure with a single shared disk.

Thanks!

There's two main forms of failover you can use with SQL post 2012,

The Windows Failover Cluster (WFC) which has been around for a while and the Always on High Availability Group(AO)

WFC uses a single database which can move between multiple machines this method requires the database to be installed on shared drives (such as a SAN) so that when the cluster moves the node the other machine has direct access to the information that makes up that database

AO uses two completely seperate instances of SQL Server, both instances run at all times, (depending on how close they are you can set them up to be synchronous or a-synchronous, (if they're on the same network synchronous is better if they're separated globally a-sync is a better choice), due to there being two databases you do not require any shared storage, the two machines work completely independently of each other, we currently have ours set up with a quorum witness which is actually on a shared drive but is not technically required (it is advisable to have a witness however)

Worth Noting about using AO, if you use the secondary node for any data access / backups (which you can as it can be accessable through readonly) you will require the extra licences to access it, if it sits there purely as a redundancy for when your primary node fails you do not require any extra licences as only one database will be accessible at any one time, with WFC since there is only actually one instance you only require one set of licences for that instance as it moves, you do have the disadvantage of only one disk failure will cause problems but using a RAID 10 on those disks will limit this from becoming a problem

Ste


How to create a blank version of a PostgreSQL database I've been using pgAdmin to create a database and test it.

I'm at a point now where I need to install this db on different servers.  I've been using pg_dump to do this and I've been able to muddle my way through.

But I'm wondering if there's a way to "reset" all the "current" values in any sequence that I have set up?
If you have any other general suggestions on how to create "empty" or "blank" databases, can you let me know?

how to create "empty" or "blank" databases

I would use pg_dump with the --schema-only option to create the dump for an empty database:

pg_dump mydb -s > /mypath/myfile.sql

Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster:

createdb mytemplate; pg_dump mydb -s | psql mytemplate

If you should create the database in a different db cluster, remember that roles or tablespaces are cluster-wide objects and not included in the dump of a single db. So, any custom roles (or other cluster-wide objects) have to be present in the new cluster before restoring to it.

This is much faster than truncating existing tables.

  a way to "reset" all the "current" values in any sequence

There is a dedicated command for that:

ALTER SEQUENCE RESTART;

Unless defined otherwise, this resets a sequence to 1. To define otherwise, use:

ALTER SEQUENCE RESTART WITH 123;

Or save the desired start value with the sequence:

ALTER SEQUENCE my_seq START WITH 123;

And use the simple form to RESTART.

To restart all sequences in the database, retrieve them from the catalog table pg_class:

DO
$do$
BEGIN

EXECUTE (
   SELECT string_agg('ALTER SEQUENCE ' || oid::regclass || ' RESTART;', E'\n')
   FROM   pg_class
   WHERE  relkind = 'S'  -- 'S' for sequences
   );

END
$do$

Sufficient privileges required. Superusers pass this test automatically.

Careful with that! You normally don't want to lose the current value for all sequences in your database. In any case, you may want to check what the SELECT cooks up before executing it.

So, I would create an empty copy and then run the above DO command. Then pg_dump again and restore to different servers.


Transfer rows into a new table, forcing to generate unique primary keys till its insertable Lets say we have the following scenario...
+-----------------+-------------+---------------+
| Table           | PK          | Additional    |
+-----------------+-------------+---------------+
| playerinventory | id (int)    | amount, level |
+-----------------+-------------+---------------+
| newinventory    | id (BIGINT) | amount, level |
+-----------------+-------------+---------------+

We are in the middle of a architectural change of our game-database.
"amount, level" should get extracted from "playerinventory" and get inserted into "newinventory".
Limitations

"newinventory" has no auto-increment pk, therefore we need to
generate a unique long for each... we also can not add
"auto-increment" to the table
"newinventory" could possible
already contain those identities, so we need to check if the
generated id already exists and if so repeat the process for the
affected set of extracted columns till it was inserted
successfully.
We CANT simply insert each row from
"playerinventory" into "newinventory" with its current id... that's
simply not possible (if I would explain why this post would be much,
much longer), that's why we NEED to generate a new unique
ID

In nearly any programming language this process would look like this
for row in playerInventory
   id = generate new ID
   while(id in newInventory) id = generate new ID
   insert into newInventory( id, row.amount, row.level )

How can we achieve this in MySQL/SQL ?
The highest ID in "newinventory" is already 9223372036854775806 and therefore we cant increase anymore... we need to generate and test till it fits.

use a BEFORE INSERTTRIGGGER to generate UNIQUE numbers.
With SET @NEW_ID = 1; you define the first new value
Schema (MySQL v8.0)
CREATE table playerinventory ( id int PRIMARY KEY ,    amount INT , level int);
CREATE table newinventory ( id bigint PRIMARY KEY ,    amount INT , level int);

INSERT INTO playerinventory VALUEs (5,1,1),(6,2,2),(7,3,3),(8,4,4);

DELIMITER $$
CREATE TRIGGER before_newinventory_insert
BEFORE INSERT
ON newinventory FOR EACH ROW
BEGIN

    SELECT MAX(id) + 1
    INTO @NEW_ID
    FROM newinventory;
    IF @NEW_ID IS NULL THEN
        SET @NEW_ID = 1;
    END IF;
    set NEW.id = @NEw_ID;
END $$

DELIMITER ;

INSERT INTO newinventory SELECT * FROM playerinventory;

Query #1
SELECT * FROM newinventory;

| id  | amount | level |
| --- | ------ | ----- |
| 1   | 1      | 1     |
| 2   | 2      | 2     |
| 3   | 3      | 3     |
| 4   | 4      | 4     |

View on DB Fiddle


Alwayson + FCI with independent Storage I have setup with FCI+AG solution but with independent disks. There is no listener, and the applications are connecting to the FCI. Is this setup even reasonable to use? The only option I see here is manual fail over. If I need to make any changes, what can be done? Can I add a listener to the AG because both the nodes are in sync? . I did not build this solution but inherited it when I joined the company I work for.

From a storage perspective was this supposed to have a Shared SAN instead of independent disks? I went through the below given link but my doubt has still not being clarified

Do you have to use a shared disk with AlwaysOn FCI?

I have setup with FCI+AG solution but with independent disks

The FCI doesn't work without shared storage, so I'm going to assume you meant to say, "I have setup with FCI+AG solution but with asymmetric storage".

  There is no listener, and the applications are connecting to the FCI. Is this setup even reasonable to use?

The no listener part is going to severely impact transparent DR, but this setup is possible although not wise. Is it reasonable to use? Sure, just not in its current form - which is mostly due to the connecting through the FCI name and not a listener. Everything else is fine.

  The only option I see here is manual fail over.

The AG will be manual failover, yes. However, there is a huge asterisk here which is that whenever AGs are used in conjunction with FCIs, there is no automatic failover at the AG level. The automatic failover will happen at the FCI level and the only failover method for AGs would be manual.

  If I need to make any changes, what can be done?

What kind of changes are you looking to make? Personally, at the minimum I'd setup a listener and have the applications use that. Otherwise it's going to be an even more painful AG failover experience.

  Can I add a listener to the AG because both the nodes are in sync?

Adding a listener does not require the AGs to be set to a specific availability mode. AGs can have 0 or more listeners (only one available to be added via the GUI) and be in any availability mode.

  From a storage perspective was this supposed to have a Shared SAN instead of independent disks?

The FCIs are using shared storage... it's a requirement. It's not a requirement to have shared storage for AGs.


Sync of Master Master replication when Primary node comes up MySQL 5.7 Enterprise I have a Master (say Node A) - Master (Say Node B) asynchronous replication setup with ROW based logging method.

Node A has been setup as Write/Read node and Node B has been setup as Read Node Only.

From JBOSS , i am doing switch over that , when A node goes down , all the Write operation will get forward to B node and whenever Node A comes up , JBOSS will forward all the Writes to A node.

Now suppose Node A remains down for long time , then all the Writes will go to B node . When A node comes up , it will remain un-sync for a while till it get complete sync with B . While it is getting sync , write operation is also happening on Node A which might result in locking issues as well.

So my question is :

Is there any method in MySQL that when Node A comes up , it will not be accessible by outer application ( like JBOSS , i have mentioned) till it gets complete sync with Node B .
Can we control it via MySQL_Safe mode so that other application can perceive Node A as still down or is there any other method

Do not write to the formerly-down side until replication is "caught up".

In your scenario, the formerly-down Master will eventually catchup.  Wait.  Only if it can't catch up, or you find corruption of some kind, should you go to the effort of copying the data over and rebuilding.  If you want to seriously worry about that, have a Slave hanging off each Master for such recovery purposes.

SET GLOBAL read_only = ON whenever a server is not the writable master.  In fact, have that as the default in my.cnf on both servers.  That way, if there is, say, a brief power failure that causes the writable master to reatart, you won't be able to write to it until you get in there to see what the situation really is.

"Split brain" is the worst thing that can happen.  This is when the network is dead, but both Masters are alive and each thinks that the other Master is dead (because of the network).  Both happily take writes that will cause duplicate key problems later.  Avoid that at all cost.

In general Master-Master, even when writing to only one Master at any time, is the least desirable HA solution.  See "Group Replication" and "Galera" for better solutions.  (But not "Fabric", it seems to be dying.)


MySQL: Does SET influence FOR UPDATE? I have the following set of MySQL queries:

SET @domain := (
    SELECT server_domain
    FROM engine.storage
    WHERE (size_bytes - bytes_inuse) >= 40000
    FOR UPDATE
);

UPDATE engine.storage
SET bytes_inuse = bytes_inuse + 40000
WHERE server_domain = (SELECT @domain);

The question is if the SET statement influences the functionality of FOR UPDATE? Will the SELECT statement be executed everytime I call SELECT @domain or once on assignment and before the UPDATE statement? Or is there even a better solution to manage this?

Thanks in advance!

Unless the MySQL lords know better than me, it's equivalent to the query:

UPDATE engine.storage
SET bytes_inuse = bytes_inuse + 40000
WHERE (size_bytes - bytes_inuse) >= 40000;

I imagine in your real query you have a more restrictive WHERE clause etc.


Mysql fulltext search my.cnf optimization I have open a question on https://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here.

We built a news site. Every day we will input tens of thousands data from web api.

In order to provide a precision search service, our table uses MyISAM, building a fulltext index (title, content, date). Our site is in testing on Godaddy VDS with 2GB RAM, 30GB space (No swap, because VDS do not allow to build swap). The CPU is Intel(R) Xeon(R) CPU L5609 @ 1.87GHz

After running a ./mysqltuner.pl

We get some results:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.20
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 396M (Tables: 39)
[--] Data in InnoDB tables: 208K (Tables: 8)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[!!] User '@ip-XX-XX-XX-XX.ip.secureserver.net'
[!!] User '@localhost'

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 27m 58s (1M q [20.253 qps], 31K conn, TX: 513M, RX: 303M)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 573.8M (28% of installed RAM)
[OK] Slow queries: 0% (56/1M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/162.5M
[OK] Key buffer hit rate: 100.0% (2B cached / 882K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 17K sorts)
[!!] Temporary tables created on disk: 49% (32K on disk / 64K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 298K opened)
[OK] Open file limit used: 41% (421/1K)
[!!] Table locks acquired immediately: 77%
[OK] InnoDB data size / buffer pool: 208.0K/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 400)

And here is my.cnf

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 16M
max_connections = 1024
wait_timeout = 5
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size= 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len = 2
read_rnd_buffer_size=2M
tmp_table_size=128M

I am not sure how to optimization my.cnf depend on ./mysqltuner.pl return results.

I have an interesting surprise for you.
The only Optimizing for FullText Indexing you can do is not something at the my.cnf level. It is all about two things:

The Stopword List
The Query

STOPWORDS
There are 543 stopwords that you may or may not want filtered out of FULLTEXT indexes. The list of stopwords was built at compile time. You can override that list with your own list as follows:
OK, now let's create our stopword list. I usually set the English articles as the only stopwords.
echo "a"    > /var/lib/mysql/stopwords.txt
echo "an"  >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt

Next, add the option to /etc/my.cnf plus allowing 1-letter, 2-letter, and 3 letter words
[mysqld]
ft_min_word_len=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

Finally, restart  mysql
service mysql restart

If you have any tables with FULLTEXT indexes already in place, you must drop those FULLTEXT indexes and create them again.
QUERY
Here is a little known fact about MySQL queries using a Full Table Index: There are occasions when the MySQL Query Optimizer stops using FULLTEXT indexes altogether and perform full table scans.
Here is an example:
use test
drop table if exists ft_test;
create table ft_test
(
    id int not null auto_increment,
    txt text,
    primary key (id),
    FULLTEXT (txt)
) ENGINE=MyISAM;
insert into ft_test (txt) values
('mount camaroon'),('mount camaron'),('mount camnaroon'),
('mount cameroon'),('mount cemeroon'),('mount camnaroon'),
('mount camraon'),('mount camaraon'),('mount camaran'),
('mount camnaraon'),('mount cameroan'),('mount cemeroan'),
('mount camnaraon'),('munt camraon'),('munt camaraon'),
('munt camaran'),('munt camnaraon'),('munt cameroan'),
('munt cemeroan'),('munt camnaraon'),('mount camraan');
select * from ft_test WHERE  MATCH(txt) AGAINST ("+mount +cameroon" IN BOOLEAN MODE);

Here is that sample data loaded:
mysql> use test
Database changed
mysql> drop table if exists ft_test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table ft_test
    -> (
    ->     id int not null auto_increment,
    ->     txt text,
    ->     primary key (id),
    ->     FULLTEXT (txt)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into ft_test (txt) values
    -> ('mount camaroon'),('mount camaron'),('mount camnaroon'),
    -> ('mount cameroon'),('mount cemeroon'),('mount camnaroon'),
    -> ('mount camraon'),('mount camaraon'),('mount camaran'),
    -> ('mount camnaraon'),('mount cameroan'),('mount cemeroan'),
    -> ('mount camnaraon'),('munt camraon'),('munt camaraon'),
    -> ('munt camaran'),('munt camnaraon'),('munt cameroan'),
    -> ('munt cemeroan'),('munt camnaraon'),('mount camraan');
Query OK, 21 rows affected (0.00 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql>

Here is a sample query and its EXPLAIN plan
mysql> select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE);
+----+----------------+
| id | txt            |
+----+----------------+
|  4 | mount cameroon |
+----+----------------+
1 row in set (0.00 sec)

mysql> explain select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ft_test
         type: fulltext
possible_keys: txt
          key: txt
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql>

OK Great the FULLTEXT Index is used.
Now, let's change the query a slight bit
mysql> select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE) = 1;
+----+----------------+
| id | txt            |
+----+----------------+
|  4 | mount cameroon |
+----+----------------+
1 row in set (0.00 sec)

mysql> explain select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE) = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ft_test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 21
        Extra: Using where
1 row in set (0.00 sec)

mysql>

OMG What happened to the FULLTEXT index? The MySQL Query optimizer basically barfed at it. If you were performing a JOIN with the ft_test table, once the WHERE clause on the fulltext search is issued and it does the same then, who knows what on earth will happen to the rest of the query.
The solution would be to refactor the query ans attempt to isolate the FULLTEXT search and gather the keys only. Then LEFT JOIN those keys to the original table.
EXAMPLE
SELECT B.*
FROM (SELECT id from ft_test
WHERE MATCH(txt) AGAINST ("+cameroon" IN BOOLEAN MODE)) A
LEFT JOIN ft_test B USING (id);

For this query, here is the result and its EXPLAIN
mysql> SELECT B.*
    -> FROM (SELECT id from ft_test
    -> WHERE MATCH(txt) AGAINST ("+cameroon" IN BOOLEAN MODE)) A
    -> LEFT JOIN ft_test B USING (id);
+----+----------------+
| id | txt            |
+----+----------------+
|  4 | mount cameroon |
+----+----------------+
1 row in set (0.00 sec)

mysql> explain SELECT B.*
    -> FROM (SELECT id from ft_test
    -> WHERE MATCH(txt) AGAINST ("+cameroon" IN BOOLEAN MODE)) A
    -> LEFT JOIN ft_test B USING (id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: B
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: ft_test
         type: fulltext
possible_keys: txt
          key: txt
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
3 rows in set (0.00 sec)

mysql>

Notice that in the DERIVED2 part of the EXPLAIN plan, the FULLTEXT index was indeed used.
MORAL OF THE STORY
You will have to get into the habit of deciding how many stopwords your database will have, creating that stopword list, configuring it, and then create/recreate all FULLTEXT indexes. You must also get into the habit of refactoring your FULLTEXT search queries in such a way that the MySQL Query Optimizer does not generate a bad EXPLAIN plan or nullify indexes for the rest of the query participating in the EXPLAIN plan.


SSAS Cube Process Manually works but MDX query doesnt work I am new to SSAS.

Here's a cube which is rebuilt by a SSIS package everyday but it seems to cause duplication. We manually 'Process' the cube using the GUI, it then updates this table with the correct date and time and the data is correct after we do this:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES

However, we have been trying to automate the 'Process' at cube level and have added this code to a SQL Agent Job which runs as type = SQL Server Analysis Services Command and as the SQL Server Agent Service Account:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
      <Object>
        <DatabaseID>DBNAME</DatabaseID>
        <CubeID>CUBENAME</CubeID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

This does NOT fix the cube data and also doesn't update the data in table:

SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES

Is there anything else I can check or do to get the MDX query to work?
I've also used the same code in an SSMS window to process the dimensions, then the measure group and then the ProcessFull on the Cube. To no avail.

Any help will be appreciated.

If you want to make sure the code you are running is exactly the code run when you process manually you should click the script button in SSMS and use that XMLA script. If you execute a series of steps you should script each step.

This will generate the scripts as XML so you can execute in SSMS and that will 100% do the same as clicking the OK button on the dialog.

If that works you should get output like this (empty result)

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
  </results>
</return>

If that doesn't work you should get a response indicating an error like this

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">
    <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
      <Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
      <Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
        <Error ErrorCode="3239313412" Description="Errors in the metadata manager. Either the database with the ID of 'testA' does not exist in the server with the ID of 'MES-TVG-80-U15', or the user does not have permissions to access the object." Source="Microsoft SQL Server 2016 Analysis Services" HelpFile="" />
      </Messages>
    </root>
  </results>
</return>

Once you have the correct script you can paste that in your agent job. If that doesn't work you should be looking for either errors in your agent job, or checking to see if the agent job is connecting to the right server, processing the right database, using the correct credentials etc.

Side note: If you do have multiple steps, and as such multiple XMLA scripts you can combine them using a  tag like I show here: how to delete multiple ssas database using xmla script


Live backup without lock In our company we have a lot of MySQL databases. Some of these are critical and we want to take a back-up of all databases. These databases can't be locked because the application can change these database at every moment. I want to write a script that makes a dump of the database an store it somewhere on a backupdisk. What is the best way to achieve this without locking databases and get a consistent dump off all databases from a remote server.

Thanks

Easiest option, if you aren't relying on features specific to MyISAM or other storage engines: change your tables to the InnoDB storage engine, and use the --single-transaction option with mysqldump.

This tells mysqldump to perform all the work within a single transaction, causing InnoDB to give it a consistent point-in-time snapshot of the data, without blocking other data access (including writes).

However, MySQL DDL operations are apparently not transactional, and altering table structures during a backup can cause them to be missing from the backup with this option.

http://www.mysqlperformanceblog.com/2012/03/23/best-kept-mysqldump-secret/


How do I detect execute permission granted to a role (when no ON clause was used) I just spent hours trying to figure out how an app had privilege to execute stored procedures.

TIL from StackExchange: Turns out the ON clause is optional for GRANT, allowing a role to be granted execute on everything. I had to laugh at the very bottom of this MSDN page after I had tried my best to validate the syntax.

GRANT Object Permissions (Transact-SQL)

CREATE ROLE Test
GRANT EXECUTE TO Test

Ok, so that's cool and all (not very principle-of-least-privilege-esque).

However: How am I to discover or detect that grant?

I tried looking through all the SQL Server Management Studio login, role, user, and schema privilege GUIs and don't see this type of Grant identified anywhere.

While debugging, I found questions about enumerating privileges in a query and while those are related, this scenario is neither mentioned nor covered by the answers there:

How do I determine using TSQL what roles are granted execute permissions on a specific stored procedure?
List all permissions for a given role?
can exec permissions be granted to a role in SQL Server 2008 / 2012

I'm looking for the most reliable way of identifying this method of granting (or denying) privileges.

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal];

You can check this using the following:

SELECT dp.name
    , perms.class_desc
    , perms.permission_name
    , perms.state_desc
FROM sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id
WHERE dp.name = 'MyRole'


What locking can I expect with DBCC CHECKIDENT(RESEED) I'm working on a project that has hit a bit of a snag: I'm replacing some replication with a home built system so that if replication goes down we don't blow our SLAs with our customers and shell out hundreds of thousands of dollars.

In my research, I learned that inserting records alongside identity values can cause the next identity for the table to be reset, which is something I want to avoid due to key range management issues.

It then seems like the obvious solution would be something like this:

DECLARE @Identity VARCHAR(15), @SQL VARCHAR(MAX)
SELECT @Identity = IDENT_CURRENT( 'dbo.My_Table' )

SET IDENTITY_INSERT dbo.My_Table ON
-- Do my inserts here

SET @SQL = 'DBCC CHECKIDENT ( ''dbo.My_Table'', RESEED,' +  @Identity + ')'
EXEC (@SQL)

Would I be killing myself with locking if this happens on a highly used table?
Alternatively, has anyone figured out a way to insert identities like replication does without causing the identity value to get reset?

Its always tricky when you try to replicate Identity columns.

You should set NOT FOR REPLICATION option on the identity column when you replicate identity field column.

This way only user inserts cause the value to be incremented at the subscriber/s.

You can even assign chunks of identity values and have them self maintained at subscribers. WHen doing this, you have to be careful as

  During a large batch insert the replication trigger is fired only once, not for each row of the insert. This can lead to a failure of the insert statement if an identity range is exhausted during an large insert, such as an INSERT INTO statement.

You can see what types of locks are generated using sys.dm_tran_locks DMV. Doing a reseed is very instantaneous and is not a big overhead. You just have to thoroughly test your idea for all possible cases to avoid any surprises.

Also, out of curiosity, how is your solution more efficient than T-Rep ? As T-Rep is tested since many many years to be more efficient than any custom/home grown solution.