Change column without interrupting service We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with InnoDB storage engine for the table.

However, the table must be fully available for SELECT and UPDATE queries while doing the change. Every value in the column is a lot smaller than 512 bytes (in fact, no value is larger than 11 bytes). Is it somehow possible to change the column type without any downtime or at least very small downtime?

There are at least two tools which attempt to mitigate the pain on online shema modifications - both by big hitters in the MySQL world. One is pt-online-schema-change from Percona and the other is oak-online-alter-table from Shlomi Noach. Could depend on whether you're more at ease with perl (percona) or python (Noach).

Needless to say, I urge you to test before implementing a solution on production.

[EDIT1]

It's unclear as to which of the toolkits (or which particular tool, if any) works with Windows - no mention of an install for oak-toolkit - there's one mention of Windows in the 366 page Percona pt-toolkit manual. In any case, for Windows, an install from source appears to be necessary.

There is however a tool by Facebook called OSC (Online Schema Change) which is a PHP script - could be suitable for Windows? Windows isn't really my thing, but there's an article here.

[EDIT2]

There's yet another solution available here - it involves replication, cutting over and cutting back. Appears to have the advantage of working under Windows. Again, you can test (and YMMV).

Found something even better - here. Fromdual are big on the MySQL scene. You're on 5.6 so this should apply.


Permission Denied on shared object (.so) file postgresql I'm trying to import a C-Language function to my postgresql database.

I have my shared object inside my home directory but I can't use it on my database.

CREATE OR REPLACE FUNCTION add_one(integer) RETURNS integer AS '/home/pedro/.../.../example.so', 'add_one' LANGUAGE C STRICT;

And this is my error message:

ERROR:  could not access file "/home/pedro/.../.../example.so": Permission denied

I think is probably my OS blocking access to this file but I don't want to give permission to all users.

tl;dr: I'm looking for a elegant solution to import my C-Language Function, as a Shared Object file, inside my postgresql database.

Thanks in advance!

Permissions on the path and file should be postgres:postgres.

ls -l /home/pedro/.../.../example.so
chown -R postgres: /home/pedro/.../.../

You will probably need sudo for changing the file ownership.


SQL Server 2016: Granting security to do everything but read / write data We have an offshore support team coming online to support our servers as DBAs for 24x7 support.  However, there are several instances that have a limitation due to contracts that limit offshore access to our data.

What security needs to be added to give them effective control over the servers as DBAs, but limits them to not being able to read/write data?

The instances are in mixed mode, but it would most likely be applied through a AD users group.  SQL Server 2016 and Windows Server 2016.

It is not possible. Everything includes granting access, which means they can change whatever access you give them.

There are some encryption methods that can be used so DBA's can have full access but not have readable access to the data. I believe Transparent Data Encryption (TDE) is one option, but I am not an expert on it.

Make a list of "everything" you want the DBA's to do, then look at the rights required to do that. IMHO, a DBA who does not have sysadmin, is not a DBA.


Replicate only some tables of MariaDB database I currently have a ready-made structure cluster with MariaDB Galera with two servers that keeps both updated database. However I now have the need to create a new similar structure but with one difference, keeping only a few tables updated in the other server (which will be master also put to another application access).

How can I do that?
Can I Keep two masters but with partial synchronization?
Do I have to use another tool?

In order of your topics:

You can't do that with cluster replication.
Multi-master is a cluster feature.
You should try to use the replicate_wild_do_table setting.

Slave threads will be restricted to replicating tables that match the specified wildcard pattern. For example replicate-wild-do-table=foo%.bar% will replicate only updates to tables in all databases that start with foo and whose table names start with bar.


How to list all constraints of a table in PostgreSQL by schema? Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.

Specifically for the "not null" part, thanks to psql -E to see what happens when you do \d+, and adapted/simplified from it, the following works:

test=# CREATE TABLE nonull (id integer NOT NULL);
CREATE TABLE
test=# \d+ nonull
                        Table "public.nonull"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id     | integer | not null  | plain   |              |

test=# SELECT a.attname, a.attnotnull FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='nonull') AND a.attnum > 0 and NOT a.attisdropped;
 attname | attnotnull
---------+------------
 id      | t
(1 row)


SSIS proxy/credentials not working from within SQL Agent job step I have been looking around and found similar questions but nothing specific to what I am having trouble with.
Problem:
I cannot get the SSIS proxy and SQL credentials configured correctly to impersonate a restricted-access service account for a SQL Agent job step that runs an SSIS package. The restricted-access service account has all the needed privileges and the package is designed correctly. I have read much material on this subject and would like some help in case I missed something.
Background:
(SQL 2012 SP3 server running on Windows Server 2012 R2.
SQL engine runs under Domain1\Admin1 and SQL Agent runs under Domain1\Admin2. both also are in the SYSADMIN server role.)
We have an SSIS package that works fine when run interactively by a SYSADMIN, and also runs fine when run within a job step as "SQL Server Agent Service Account". However our Security group wants us to run using credentials limited to what the job needs to do, and I understand this as a best practice anyway. Everything I have read indicates that an SSIS proxy and credential will help solve for this requirement; however I can't get the configuration to work so I must be doing something wrong.
The package is run via SSIS package job step. It is a local file and is not deployed to MSDB or SSISDB. The package connects to a network share, loads into a database that is local to the SQL server, truncates a table, runs a few stored procedures, then deletes the XLS file. The job step is set to use the 32-bit runtime.
Security created an account (Domain1\NewUser) that will have Modify rights to the network share. I also asked they add Domain1\Admin2 as Modify to the same share.
For setup, I created a SQL login for Domain1\NewUser using only the Public role, and added it as db_owner for the database in question because of the range of actions the package needs to perform. I created a credential (BatchLoad-credential) using as its identity the Domain1\NewUser account and the exact working password of the user account. I then created an SSIS proxy (BatchLoad-Proxy) using the BatchLoad-credential credential, active within the SSIS package subsystem, and added the SQL login for Domain1\NewUser as a proxy account principal. Then I changed the SQL Agent job step running the SSIS package to run as the BatchLoad-Proxy, and changed the owner of the job from Domain1\Admin2 to the Domain1\NewUser account.
When the job is run, we get this error (some masking on the log has been done):

Executed as user: Domain1\NewUser. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  12:46:17 PM
Error: 2016-06-24 12:46:28.56   Code: 0xC0202009     Source: xxxxxxx_xxxx Connection manager "yyyyyyyyyyy"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".  End Error

Again, the package works fine when a SYSADMIN runs it interactively within BIDS so we know the file is in the correct format and the package is designed ok.
I do not have permissions to the network share. When I try to connect manually I get a similar error:
Windows cannot access
\\<path to the network share>
Error code: 0x80004005
Unspecified error

Connecting to the network share using the Domain1\NewUser account and password, I can view the folder ok so I have verified the proper user account has access.

I appreciate the feedback on this, and I hope this helps people in the future. I finally narrowed down the true root cause of the problem. I had not provided enough detail around the fact that an Excel file was being used. After disabling everything in the package, If I only enabled the Data flow task using the Excel source, I would get the failure, but only when using the SSIS proxy (which launched the job using the Domain1\NewUser account). If I set the job step to use the SQL Server Agent account, everything would work fine.

After some time away from the problem, I tried logging into the server with the NewUser1 credentials, and ran Excel for the first time. It prompted me for initials and then I closed the program. I had also added the account to the server's local Administrators group so I could RDP.

Then I ran the job using the SSIS proxy and everything worked fine. When I removed the account from the local Administrators group it would fail again, but I determined it was the local policy "logon as a batch job" that was being granted in that membership.

Here is what I have learned from this experience:

SQL Credentials can only impersonate a user account, NOT a group.
SSIS proxy is a valid solution for granting necessary privileges for
a batch job. Excel (and possibly other applications) may need to be
launched once using the related credentials, just to complete
application settings in the user's profile on the server. Logon as a
batch job is required for a proxy that runs DTExec to launch a
package from the file system. Excel sources are problematic, and the
OLE DB driver may report this problem when it isn't a layout/format
issue:

  Hresult: 0x80004005 Description: "External table is not in the
  expected format."


Millions of reads for a single table used for reports I am currently looking into a query that retrieves lots of records, and it's used for reporting different balances during each month of the year(periods).
The original query joins a few extra tables but the main culprit is this one:

SELECT [GLHistory].[BranchID] AS [BranchID]
      ,[GLHistory].[LedgerID] AS [LedgerID]
      ,[GLHistory].[AccountID] AS [AccountID]
      ,[GLHistory].[SubID] AS [SubID]
      ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
      ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
  FROM [GLHistory] [GLHistory]
         INNER JOIN [FinPeriod] [FinPeriod]
                    ON [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
 WHERE [GLHistory].CompanyID = 7
   AND (   [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13)
 GROUP BY
       [GLHistory].[BranchID]
      ,[GLHistory].[LedgerID]
      ,[GLHistory].[AccountID]
      ,[GLHistory].[SubID]
      ,[FinPeriod].[FinPeriodID]
      ,[GLHistory].CompanyID

I need to get the information from last financial period and any other entry that exists in my table until the last financial period for an account.
Not sure what more, or how to structure the query in order to improve it.

Here is a script with the 2 tables schema and some data https://www.dropbox.com/s/swbqqzdey2tnkxz/script.sql?dl=0

And the full query can be found here: https://www.dropbox.com/s/scnav8flqpcrstd/SQLQueryGLB.sql?dl=0

Query plan presented here:queryplan

The output I am looking for is to get all the information for an account from the beginning period where I have an entry in GLHistory to the last period. And FinPeriodId might have 3 rows because its a row for each company

So it would be something like this, hope this time is right. You have an account for which there are values stored in GLHistory. For that account I need to get the information from GLHistory in a period, but also all other values until that period, so the values in GLHistory that are related to that account for each period until last activity period.  That is why I have FinPeriodId >= GLHistory.FinPeriodId.

[FinPeriod] [FinPeriod] : 180 rows

[GLHistory] [GLHistory] :75334 rows after applying those filter at the end of index spool

At Nested loop :Estimated rows 23483000 which is 75334*180 rows.

Final output Estimated rows 2987930 which is way above 251114.
This prove your query is wrong.you have to improve cardianility estimate.

In Findperiod table finperiodid is char(6) and in GLHistory table it is int.
Which is one of reason for poor cardianility estimate.

Make FinPeriodID to INT in both the table SAFELY.In million of record it does matter.

Alter table [FinPeriod]
Alter column [FinPeriodID] int not null

Experiment,

Declare @i int=201706
Declare @j char(6)='201706'
select DATALENGTH(@i),DATALENGTH(@j)

In FinPeriod table change the order of Clustered Index like this CompanyID (ASC), FinPeriodID (ASC)

Now in GLHistory your choice of Clustered index is wrong.

Firstly It will cause lot of index fragmentation.
Secondly  your index  is not being use in sample record of 700.

So i hv change index in GLHistory table in this manner,

CREATE  CLUSTERED INDEX RIIX ON GLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX RIIIX ON GLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

I  use your query without changing any logic in old index way .I got all index scan .

No need of any covering index.WHY ?Because CLUSTERED Index use in join is able to select those columns.

Now I  drop the index and created index like above.

In your query you will notice I have only change the order of column in join so that it utilise the index.
After Creating index don't forgot to update statistics.

UPDATE STATISTICS [GLHistory] ;
GO
UPDATE STATISTICS [FinPeriod] ;
GO

SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].[FinPtdDebit]
    ,[GLHistory].[CuryFinPtdCredit]
    ,[GLHistory].[CuryFinPtdDebit]
    ,[GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    ,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    ,[GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    ,[GLHistoryLast].[CuryFinPtdDebit]
    ,[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
    ,[GLHistoryLast].[YearClosed]
FROM (
    SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM [GLHistory] [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
    WHERE [GLHistory].CompanyID = 7
        AND ([GLHistory].BranchID = 13)
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN [GLHistory] [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN [GLHistory] [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

Result : I am getting all index Seek,minor Nested loop .Thats it.

Don't know how it will perform in million of records.

Alternative Solution,

Put [GLHistory]  filtered records in temp table because you are joining same table 3 times.Add all require column in temp table.I have added few only.

    CREATE TABLE #tmpGLHistory (
    id INT identity(1, 1)
    ,BranchID INT
    ,LedgerID INT
    ,AccountID INT
    ,SubID INT
    ,FinPeriodID INT
    ,CompanyID INT
    ,CuryID NVARCHAR(10)
    ,FinPtdCredit DECIMAL(19, 4)
    ,CuryFinPtdCredit DECIMAL(19, 4)
    ,FinBegBalance DECIMAL(19, 4)
    ,CuryFinYtdBalance DECIMAL(19, 4)
    ,CuryFinBegBalance DECIMAL(19, 4)
    )

CREATE  CLUSTERED INDEX TIIX ON #tmpGLHistory (
    [CompanyID]ASC,
    [FinPeriodID] ASC

    )

 CREATE NONCLUSTERED INDEX TIIIX ON #tmpGLHistory (
    [BranchID] ASC
    ,[LedgerID] ASC
    ,[AccountID] ASC
    ,[SubID] ASC
    )

INSERT INTO #tmpGLHistory
SELECT [BranchID]
    ,LedgerID
    ,AccountID
    ,SubID
    ,FinPeriodID
    ,CompanyID
    ,CuryID
    ,FinPtdCredit
    ,CuryFinPtdCredit
    ,FinBegBalance
    ,CuryFinYtdBalance
    ,CuryFinBegBalance
FROM [GLHistory] [GLHistory] WITH(NOLOCK)
WHERE [GLHistory].CompanyID = 7
    AND (
        [GLHistory].BranchID IS NULL
        OR [GLHistory].BranchID = 13
        )

    SELECT [GLHistoryByPeriod].[BranchID]
    ,[GLHistoryByPeriod].[LedgerID]
    ,[GLHistoryByPeriod].[AccountID]
    ,[GLHistoryByPeriod].[SubID]
    ,[GLHistoryByPeriod].[LastActivityPeriod]
    ,[GLHistoryByPeriod].[FinPeriodID]
    ,[GLHistory].[LedgerID]
    ,[GLHistory].[BranchID]
    ,[GLHistory].[AccountID]
    ,[GLHistory].[SubID]
    ,[GLHistory].[CuryID]
    ,[GLHistory].[FinPeriodID]
    ,[GLHistory].[FinPtdCredit]
    ,[GLHistory].FinPtdCredit
    ,[GLHistory].[CuryFinPtdCredit]
    --,[GLHistory].[CuryFinPtdDebit]
    --, [GLHistory].[FinYtdBalance]
    ,[GLHistory].[FinBegBalance]
    ,[GLHistory].[CuryFinYtdBalance]
    ,[GLHistory].[CuryFinBegBalance]
    --,[GLHistory].[YearClosed]
    ,[GLHistoryLast].[LedgerID]
    ,[GLHistoryLast].[BranchID]
    ,[GLHistoryLast].[AccountID]
    ,[GLHistoryLast].[SubID]
    ,[GLHistoryLast].[CuryID]
    ,[GLHistoryLast].[FinPeriodID]
    ,[GLHistoryLast].[FinPtdCredit]
    --, [GLHistoryLast].[FinPtdDebit]
    ,[GLHistoryLast].[CuryFinPtdCredit]
    --, [GLHistoryLast].[CuryFinPtdDebit],
    --[GLHistoryLast].[FinYtdBalance]
    ,[GLHistoryLast].[FinBegBalance]
    ,[GLHistoryLast].[CuryFinYtdBalance]
    ,[GLHistoryLast].[CuryFinBegBalance]
--, [GLHistoryLast].[YearClosed]
FROM (
   SELECT [GLHistory].CompanyID
        ,[GLHistory].[BranchID] AS [BranchID]
        ,[GLHistory].[LedgerID] AS [LedgerID]
        ,[GLHistory].[AccountID] AS [AccountID]
        ,[GLHistory].[SubID] AS [SubID]
        ,MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod]
        ,[FinPeriod].[FinPeriodID] AS [FinPeriodID]
    FROM #tmpGLHistory [GLHistory]
    INNER JOIN [FinPeriod] [FinPeriod] ON [GLHistory].CompanyID = [FinPeriod].CompanyID
        AND [FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID]
    WHERE [FinPeriod].CompanyID = 7
    GROUP BY [GLHistory].CompanyID
        ,[FinPeriod].[FinPeriodID]
        ,[GLHistory].[BranchID]
        ,[GLHistory].[LedgerID]
        ,[GLHistory].[AccountID]
        ,[GLHistory].[SubID]
    ) GLHistoryByPeriod
LEFT JOIN #tmpGLHistory [GLHistory] ON [GLHistoryByPeriod].CompanyID = [GLHistory].CompanyID
    AND [GLHistoryByPeriod].[finPeriodID] = [GLHistory].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistory].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistory].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistory].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistory].[SubID]
LEFT JOIN #tmpGLHistory [GLHistoryLast] ON [GLHistoryByPeriod].CompanyID = [GLHistoryLast].CompanyID
    AND [GLHistoryByPeriod].[lastActivityPeriod] = [GLHistoryLast].[FinPeriodID]
    AND [GLHistoryByPeriod].[branchID] = [GLHistoryLast].[BranchID]
    AND [GLHistoryByPeriod].[ledgerID] = [GLHistoryLast].[LedgerID]
    AND [GLHistoryByPeriod].[accountID] = [GLHistoryLast].[AccountID]
    AND [GLHistoryByPeriod].[subID] = [GLHistoryLast].[SubID]
WHERE [GLHistoryByPeriod].[finPeriodID] = 201706

--ORDER BY [GLHistoryByPeriod].AccountID asc oFFSET 0 ROWS FETCH NEXT 10000000 ROWS ONLY

DROP TABLE #tmpGLHistory

  Introduce Filter inside Inner sub query

when you can write WHERE [GLHistoryByPeriod].[finPeriodID] = 201706
Outside inner query then why not introduce same filter inside inner subquery so that inner subquery already give you less resultset imprving the query dramatically.

Like , [FinPeriod].[FinPeriodID] = 201706


Mysql Fulltext search doesn't return any results I have an issue with a full text search in a MySQL table.

MATCH AGAINST returns no results even I if have 7 records containing the words I'm looking for. What can I do to make it return the rows?

SELECT *
FROM site_plugin_products_cache_texts
WHERE MATCH(item_text) AGAINST ('+your +name' IN BOOLEAN MODE);

  No rows.

SELECT *
FROM site_plugin_products_cache_texts
WHERE item_text LIKE'%your name%'

  7 rows (0.071 s)

http://sqlfiddle.com/#!9/70c3fa/1/0

Thanks.

Did you know that MySQL has a stop word list ?
For MyISAM, there are 543 words that do not get included in a fulltext index ???
In the MySQL 5.6 Docs on this (Stopwords for MyISAM Search Indexes), you can see the list.

name is on line 55 column 4
your is on line 108 column 4

That why all attempts to search on those two words will fail.
If you convert the table to InnoDB, drop and create the FullText index, you will find them. There are only 36 stop words for InnoDB FullText Indexes.
If the table has to remain MyISAM, you have to provide an empty stopword list, restart mysqld, drop and create the FullText Index again.
Here are some of my past posts on this:

May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Apr 24, 2013 : MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'

Please refer to the Jan 26, 2012 post on how to setup and empty stopword list.
UPDATE 2018-05-04 22:44 EDT
If you run this in SQL Fiddle
SELECT * FROM site_plugin_products_cache_texts
WHERE match(item_text) AGAINST ('+CALL' IN BOOLEAN MODE);

This will work because CALL in not in the stopword list.


SQL Server Transactional Replication - what is sp_MSreleasesnapshotdeliverysessionlock? Occasionally when creating and finishing a snapshot to re-sync data, some of the subscribers seem to hang about for several hours afterwards before they start to take the snapshot. There's no blocking that I can see on the distributor. When I check on the subscriber for sessions against the subscription database, they are running: sp_MSreleasesnapshotdeliverysessionlock

This sometimes happens on a number of SQL Server versions, but all patched to recent CUs. What is this process? And what is likely to be going wrong?

It's important to understand where is the bottleneck...

Are the BCP-files for the snapshot actually getting created?
Are you using PULL or PUSH subscriptions?
Remote or Local DIST?
Can you setup a Profiler Trace or XE Session on each component...PUB, DIST and SUB and trace activity...plus maybe look at wait-stats and what SQL is waiting for?
Turn on Verbose-logging for LogReader-Agent and Distribution-Agent as well.
Query the History Replication Sys-Table to get additional information too...
sp_helptext on sp_MSreleasesnapshotdeliverysessionlock - what is in it?


Backup of VM having SQL Server in it I have SQL Server running on a virtual machine (VM). Weekly VM backup is done. My question is when we backup the VM, does it locks down the SQL Server or the databases residing in that SQL Server? I understand VM backup is at the OS level but still want to confirm since I am in middle of investigating an issue and I am not a database expert.

If the "SQL Server VSS Writer" service is running IO on the database files will be "frozen" while a snapshot is made of the databases and the database state (full database backup) will be included with the VM backup. This will not lock down the SQL server but will in some cases make it slower.

If the service is not running the database files will not be included in the backup and you have to use other means to backup them. This needs to be discussed with your DBA in many cases having full backups taken with the VM backups breaks the backup chain the DBA is maintaining and you should definitely make those backups more often than weekly and backup your transaction log as well. The VM backup (snapshot) can also affect performance of the SQL Server even though it's not making a snapshot of the database files.


Migrating Postgres from 9.2 to 9.3 I have 2 CentOS servers, one is running PostgreSQL 9.2 and the other, 9.3.

I'm trying to migrate the database but I'm getting 'relation  does not exist' errors.

What I did was on the 9.2 server:

pg_dump -h localhost -W -U postgres --clean DBName > dbname.dump

copied the dbname.dump file to the 9.3 server and then:

pgsql -h localhost -W -U postgres -f dbname.dump

Am I doing something wrong?

Other misc info, I am using the postgis extension and have some foreign key restraints.

I would do something like:

pg_dump -h localhost -W -U postgres --clean -f dbname.dump DBName

The move the file to the new server, create the new database with

createdb DBName

then restore with:

psql -d DBName -f dbname.dump -v ON_ERROR_STOP=1

Remember that if you have tablespaces created on the old DB you will have to manually recreate them in the new DB before importing the dump.


DB2, How to save all stored procedures as files How does one extract all stored procedures from a DB2 schema as individual files (without using Data Studio so it can be scripted).  I need them all so I can uploaded to source control.  I have found a way to do it via command line if one has access to the server, but do not have server access.

you can use the db2look clp command with the -e option, altough it does not have an option to only export procedures, so unless they are in a schema without other objects you will also get e.g. TABLE, VIEW and other DDL mixed in the output.
$ db2look -d BP -e -cor -nofed -z PAUL | grep -A 999 'DDL Statements for Stored Procedures'
-- DDL Statements for Stored Procedures
---------------------------------

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
SET CURRENT SCHEMA = "PAUL    ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","PAUL";

CREATE OR REPLACE PROCEDURE P ()
BEGIN
          RETURN   /* this */   2  ; -- comment
    /* this is a
    comment */
END
;

-------------------------------
-- DDL Statements for Triggers
-------------------------------

SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
...


posgresql9.4 remote connection fail We have issues connecting to our PostgreSQL server. Sometimes nobody can connect, sometimes some can and some can't. We have no idea why.

The PostgreSQL server is on a virtual machine with 4GB of memory. We have 3 poeple who want to connect to that server. Could it be possible that number of connections are over the default max_connection?

Error message:

  could not connect to server: Connection refused Is the server running on host "192.x.x.x" and accepting TCP/IP connections on port 5432?

This sounds to me like a network problem, restart the virtual machine and try again. The whole vm might be needing just a restart.


I need to figure out the last time that a function was used by SQL Server. How can I do this? I work with databases that contain thousands of stored procedures and hundreds of functions.  I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?

You can use Change Data Capture or Change tracking to analyse the required changes here

You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions

Also,

You can get a possible idea as mentioned here:using below query

SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')

So basically you have to find a proper way to analyse the usage.


Debugging a SSIS OleDbCommand I have an OleDb Command component in a dataflow that does an update(yes, I'm aware of the performance implications).

Running the pack in BIDS shows me that the component is being reached and runs correctly, turns green when complete, etc. However, I don't see the update in the database.

I am logging all events on the component, but there does not seem to be a way to get the actual command text sent to the server. I can see the ICommandText::SetCommandText, ICommandPrepare::Prepare and ICommandWithParameters::GetParameterInfo events complete, but nothing changes in the db. What else can I look at to find out what is really happening between these two systems?

You can check what's going on using SQL Profiler.  When you execute an OLE DB task, you should see something like this as an RPC:Completed event:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 int',N'update testtable set id = @P1',1
select @p1

In this case, I'm just updating a test table (called testtable).

Then, for each iteration, you'll see an RPC:Completed event which reads something like:

exec sp_execute 1,50

This is saying to execute the first prepared event (our update statement--sp_prepare's first parameter is "1" here), and send in 50 as the new ID for testtable.

If you don't see these calls on your database as you debug your flow, it might indicate that you're actually doing the process on a different instance (or maybe there's something like a trigger preventing your change from occurring?).


Need some help for choosing the replication type My target is to create a replication process for the following

Please suggest which type of replication is suitable for this architecture, See the image below

In the simple view, this pretty much looks like Transactional Replication from {Publisher1, Publisher2, Publisher3} to Subscriber.

I assume that Subscriber includes the Staging DB.  Therefore, as the Staging DB is updated it can, in turn, become a publisher using Transactional Replication to {Subscriber1, ..., Subscriber5}.

Of course, depending on the way data is maintained, you might need to use Merge Replication instead.

Paul Ibison has a brief article that discussed the differences in republishing with Transactional or Merge Replication at: http://www.replicationanswers.com/Republishing2005.asp

The big issue is being sure that you clearly understand which sources of data are authoritative, how the data flows (and what happens to it in the Staging DB), et cetera.


DB2 ROLLFORWARD ERROR when doing "TO A POINT IN TIME" option I do restore command of backup image which was made 03.03.2015 at 19:20:

db2 restore database maxdb71 from "/data/archiveBackup" taken at 20150303192002 without prompting;

Then I try to do the rollforward to a point in time (03.03.2015 at 21:05) so this is after the backup image:

db2 rollforward db maxdb71 to 2015-03-03-21.05.00.000000 using local time and complete;

But I receive error:

SQL4970N  Roll-forward recovery on database "MAXDB71" cannot reach the
specified stop point (end-of-log or point-in-time) on database partition(s)
"0". Roll-forward recovery processing has halted on log file "S0000189.LOG".

So like it cannot find log  S0000189.LOG.
But this log EXISTS in the archive log location (C0000004 currently subfolder is used for archive logs) /data/archive/ctginst1/MAXDB71/NODE0000/C0000004>:

these are the timestamps for some of archive log files:

ctginst1 db2iadm1 15765504 2015-03-03 19:22 S0000187.LOG
ctginst1 db2iadm1 16785408 2015-03-03 21:52 S0000188.LOG
ctginst1 db2iadm1 16785408 2015-03-04 00:15 S0000189.LOG
ctginst1 db2iadm1 16785408 2015-03-04 02:32 S0000190.LOG

Basically log file S0000188.LOG should be the one which contains transactions from 21.05, right?

So why I cannot do the rollforward of point in time when I have necessary logs?
I am really confused with this rollforward option.

When I do db2 rollforward database maxdb71 to end of logs and complete;
it is successful and I get information:

 Log files processed                    = S0000187.LOG - S0000188.LOG

So to end of logs works but why I cannot perform until some specific point in time?

If you archive logs to a location different from the active log directory (logpath database configuration parameter), you need to specify the clause overflow log path /path/to/archived/logs. The ROLLFORWARD command does not do it automatically.

PS. You really should start reading manuals -- they are full of useful stuff, including multiple examples...


Postgres long autovacuum halting database I have a fairly large table (1 million rows) and my database is stuck on an autovacuum (>30 mins) on the this table, causing the whole database to chock.  Application won't even load now.

-00:37:31.137859 autovacuum: VACUUM public.users

SELECT n_tup_del, n_tup_upd FROM pg_stat_all_tables WHERE relname = 'users';

These are my autovacuum settings on my users table:

autovacuum_vacuum_scale_factor=0.0,
autovacuum_vacuum_threshold=5000,
autovacuum_analyze_scale_factor=0.0,
autovacuum_analyze_threshold=5000

These suggested settings I used from Slow PostgreSQL Performance? Don't Forget to Vacuum your Database

Do I just have to wait it out? What are my options?

Update

I have upgraded to Postgres 9.5 and have also increased my RDS IOPS to 900 and the vacuum process still maxs out the IOPS and can't do anything else to the database.  The process was running for at one point 1 day before the upgrade.

I also have removed the custom autovacuum settings I had, and now just using the default.

Here is an attachment of results of these queries;

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;
SELECT * FROM pg_locks;

http://www.filedropper.com/output_5

VACUUM processes launched by autovacuum can be safely killed with:

SELECT pg_terminate_backend(PID_of_backend);

Actually, any client processes in Postgresql can be terminated this way. Uncommitted work by this backend will be simply discarded.

You could then re-run VACUUM manually at a low-traffic time:

VACUUM VERBOSE users;

Check whether Cost-based Vacuum Delay can help you. This would limit the amount of I/O your autovacuum process uses.

Maybe you simply hit your IOPS limit. You should be able to see the numbers on AWS interface. On standalone Linux use iostat -dtkxy 10 to measure I/O. (iostat is usually packaged in sysstat package).

Maybe the VACUUM kicks in again so often because of the aggressive settings in your config.


SQL Server 2005 xp_sendmail error I have come across a SQL 2005 server that uses SQL mail to send emails. This setup works fine as long as the MAPI profile points to an Exchange 2003 server, but breaks when the profile points to an Exchange 2010 server. The error: xp_sendmail: failed with mail error 0x80004005

Would updating the MAPI client help fix this, or is an exchange 2010 incompatibility?

A better option if possible would be to change out to using database mail instead of SQL mail.  This uses SMTP and takes the MAPI client out of the config.

It would require that anything that's calling xp_sendmail be changed to use sp_send_dbmail.  If you are only using this for SQL Agent emails then this is a very easy change.


Multiple volumes vs single volumes for SQL Server CHECKDB performance We have a ~10TB database with dozens of data files on SQL Server 2017 Enterprise CU21. We're seeing slower CHECKDB performance when all of the files are on a single volume as opposed to multiple volumes. (In both cases, the same high-end (Pure) storage is involved, same VM hosts, same storage network - just different numbers of volumes.)
Is there a documented advantage to spreading data files across multiple volumes, either for DBCC CHECKDB specifically, or for SQL Server generally?
The trace flag 2549 described in KB 2634571 tells CHECKDB to treat each database file as if it's on a unique drive. But is there any other official documentation outlining how SQL Server (or CHECKDB specifically) treats IO differently when there are multiple volumes involved?

Is this what you're looking for?
https://techcommunity.microsoft.com/t5/sql-server-support/a-faster-checkdb-8211-part-ii/ba-p/316882
At the end of this blog Bob Ward says:
There are also some factors that can affect whether these changes actually help make your execution of CHECKDB faster:
...
Spreading files across disks – As with the general performance of server queries, if we are spreading out our reads across separate physical disks for files, we can achieve better I/O performance.


Tables of a huge database When I work on a huge database, and after following normalization rules, I found that I have many tables for each part of my huge database. That makes me worry about the final number of tables, it will be in the hundreds.

What is the best choice for me?

make many tables with a small number of columns? or
make few tables with many columns?

In general, the more tables in a database, the more normalised it is.

If your table has many columns, the chances are that some of them will be empty a lot of the time, or repeating types, or other breaches of normalisation, so as you've discovered, normalisation leads to more tables. This is perfectly normal (no pun intended), is nothing to worry about and will lead to easier and more efficient querying


When does sp_executesql refresh the query plan? You'll have to forgive my naivety as I'm not a DBA but my understanding is that over time the statistics of a database change and a stored procedure must be recompiled to keep the query plan up to date with the latest statistics.

Assuming I have a stored procedure in my database that is recompiled against the latest statistics at some regular interval, what are the implications of in-lining the stored procedure in code and wrapping it in an sp_executesql statement?  Do I lose the refreshing of the query plan that used to happen as part of the recompilation of the procedure?

If there's anything else (other than permissions) that I need to consider before I make this change then I'd appreciate your insights.

I read this on MSDN:

  The ability of the SQL Server query optimizer to match the new
  Transact-SQL string with an existing execution plan is hampered by the
  constantly changing parameter values in the text of the string,
  especially in complex Transact-SQL statements.

So assuming the stored procedure I'm attempting to in-line and wrap in sp_executesql does indeed contain some parameters, is this saying that although my execution plan is cached, I'm making it more difficult for SQL Server to find and reuse it?

The line from MSDN is talking about using EXEC(), like this:

SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);

In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE clauses based on the presence of certain parameters - in which case that will generate a different plan).

If you use sp_executesql then the parameter values can still cause parameter sniffing issues (just like with normal SQL), but this has nothing to do with whether SQL Server can re-use the plan. This plan will get used over and over again, just as if you hadn't used sp_executesql at all, unless variables that would cause a direct query to get recompiled, in which case this one will get recompiled too (essentially, SQL Server doesn't store anything with the plan that says "this was executed from sp_executesql, but this one wasn't):

SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sys.sp_executesql @sql, N'@x varchar(32)', @x;

As a bonus, this has built-in protection against dynamic SQL, and avoids you having to worry about doubling up single quotes due to string delimiters. I blogged about some of this here and please read up on SQL injection here and here.

If you are having issues with plan re-use and/or parameter sniffing, some things you should look into are OPTION (RECOMPILE), OPTIMIZE FOR, optimize for ad hoc workloads and simple/forced parameterization. I addressed a few similar questions in response to a recent webcast here, it may be worth a skim:

Follow-up on Summer Performance Palooza 2013

The gist is: don't be afraid to use sp_executesql, but only use it when you need it, and only spend energy over-optimizing it when you have an actual performance issue. The example above is a terrible one because there's no reason to use dynamic SQL here - I've written this answer assuming you have a legitimate use case.


User Defined Aggregate Functions without DLL Is it possible to create a aggregate function without dll? Every aggregate function have to reference a dll? Thank you.

No. User-Defined Aggregates (UDAs) can only exist as SQLCLR objects. There is no T-SQL equivalent. The same holds true for User-Defined Types (UDTs).

That answer assumes the question is actually asking if UDAs require an Assembly as created via CREATE ASSEMBLY.

However, if one interprets the question as asking if a DLL file is required by the CREATE ASSEMBLY statement, then no: you can create an Assembly from either a DLL on the file system or from a VARBINARY literal or variable containing the same bytes that would be in the DLL:

CREATE ASSEMBLY [AssemblyName] FROM 0x1234A6B3......;


How do I get mysql to start for the first time? I want to get mysql to start for the first time.  I installed it on RHEL version 6.  All of the commands posted were executed as root.

As a test, I ran this:

# yum install mysql

This is the output:

  Loaded plugins: amazon-id, rhui-lb, search-disabled-repos Package
  1:mariadb-5.5.47-1.el7_2.x86_64 already installed and latest version
  Nothing to do

The directory /etc/init.d/ has no mysql or mariadb file inside it.

I tried these five commands (and posted the output after them):

# service mysql start

  Redirecting to /bin/systemctl start  mysql.service Failed to start
  mysql.service: Unit mysql.service failed to load: No such file or
  directory.

# systemctl start mysql.service

  Failed to start mysql.service: Unit mysql.service failed to load: No
  such file or directory.

# systemctl start mysql.service

  Failed to start mysql.service: Unit mysql.service failed to load: No
  such file or directory.

# systemctl start mariadb

  Failed to start mariadb.service: Unit mariadb.service failed to load:
  No such file or directory.

# systemctl start mysql

  Failed to start mysql.service: Unit mysql.service failed to load: No
  such file or directory.

I used

  whereis mysql

The resulting outputs gave me no leads of files to start to get the service to start.

What should I do to get MySQL running?

To setup mysql database server, follow step by step of below given URL

http://www.cyberciti.biz/faq/how-to-install-mysql-under-rhel/


how to use trigger to make SQL Job's step success or fail Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from table. The insert and delete SQL is issued from application.

Step 2. Check the running status of step 1. Since there is record when the process is running. Currently, I use two SELECT statement to check. When first SELECT statement gets the record, then the process is started. And then delay about 15 minutes, run another SELECT statement, when the record is deleted. I know the process is finished. Once process is finished, I will trigger step 3 to run another process.

Step 2's method has drawback. Because step 1's process's time will vary from time to time. So I need to adjust the delay time. I think trigger is a better way in this case. But I do not know how to link the trigger with SQL Job Step's success and fail. Would you please provide an example? Thanks.

If you are storing records in a permanent table, you can make use of a trigger below.

--to check status of inserted record

create trigger triggername
on tablename
for insert
as
begin
if exists(select 1 from inserted)
--do your logic here
end

--to check status of deleted record

create trigger triggername
on tablename
for delete
as
begin
if exists( select 1 from deleted)
--do your logic here to start another process
end

Just for your information, inserted and deleted tables can be used to know what records are inserted/deleted as the result of the trigger action, and in spite of how many rows are inserted or deleted, the trigger will fire only once per statement. Further, you can combine both of the above statements into one by just doing like below:

create trigger triggername
on tablename
for insert,delete
as
if exists(select 1 from inserted)
begin
--what you want to do when a record is inserted
end

if exists(select 1 from deleted)
begin
--what you want to do when a record is deleted
end


Introducing surrogate key in may-to-many table? I have 3 table:

brand: brand_id(pk), name

product: product_id(pk), name

brand_product: brand_product_id(pk), brand_id, product_id

And I have a report table, at the moment it looks like this:

product_report: product_report_id(pk), quantity, brand_id, product_id

My question is, what are the pros and cons of having the brand_product_id column instead of brand_id, product_id.

I would suggest that brand_product_id is redundant.
Yes, every table row must be uniquely identifiable.
Nobody ever said that had to be accomplished with a single field.
Logically, this is a "Weak Entity".  No record can exist in this table without corresponding entries in the other two tables.
It is highly unlikely that you would ever want to query this table without reference to one [or both] of the other two tables, i.e. you would always want to query it for a brand or a product.   Therefore, the [unique] combination of brand_id and product_id is sufficient for a Primary Key.
I would suggest that your biggest decision (if it's a decision at all) is whether to have the Primary Key on (brand_id, product_id) with an index on (product_id, brand_id) for the "reverse" look-ups, or the other way around.  YMMV.


Materialized view logs — with condition? I've created materialized view logs on a WORKORDER table in an Oracle 19c database.
create materialized view log on my_workorder_system.workorder with rowid;

The plan is to integrate the WORKORDER records to a separate GIS system (Oracle 18c) via a materialized view.
The catch:
The WORKORDER table has both spatial and non-spatial workorder records in it (ISGIS = 0 or 1). I only need to sync the records to the GIS datdabase WHERE ISGIS = 1.
If I understand correctly, my materialized view in GIS will tell the MV logs in the workorder system to purge the log records after they're synced. However, the sync will only ever happen for workorders where ISGIS=1. The workorders where ISGIS=0 will never get synced — and therefore are not needed in the MV logs.
Is there a way to only generate MV logs for records that meet a condition?
For example:
create materialized view log on my_workorder_system.workorder with rowid where ISGISS=1.

MV logs are all or nothing; they have no visibility of criteria for any given MV. Any MV Log records will be purged up to the oldest common refresh date as soon as the source DB determines that all registered MVs have refreshed.


SQL Server 2012 Performance Test I've been given a task to test where an SQL Server 2012 can achieve better performances - we have two options:

Use the SQL Server as an On-Premise machine (Standalone server that we have)
Use the SQL Server as a cloud-based server.

What is the best way to check which option gets better performance for our needs? (CPU + I/O performances), is there any recommended benchmark tool I can use? I thought about writing some SQL queries of various types and run them against both options, but I'm not sure which queries I should use.

Are there any recommended  type of queries that can give some good glance about the performance?

I would say using a local server would be better. At least that way you know the hardware and can simulate it as close to the production server as possible. With a cloud-based server you have no idea what the underlying hardware is, and with some services like Azure you will have to take the 3x write policy into account, different network connectivity, volume bursts for other activity within your "cloud", etc. So your performance picture will be quite different even if the hardware might be similar.


Insert Array of JSON-s into Postgres table I have an array containing JSON of the following form:

[
  {
    "country": "ANY",
    "category": "poetry"
  },
  {
    "country": "ES",
    "category": "science"
  }
]

What I currently do is - iterate over the JSON-s in the Array in Python and insert them into the DB table one by one like so:

sql = 'insert into table_name (country, category) values (%s, %s)'
vars = (country, category)
app_utils.run_query_pool_commit(con_pool, sql, vars)

I'm curious if there's a more elegant way of doing such kind of bulk inserts without having to make a loop and inserting the objects one by one.

Thanks.

there are many json functions built into postgresql

the function you are looking for json_to_recordset()  the command will look something like this

insert into table_name (country, category)
   select * from json_to_recordset( '[
  {
     "country": "ANY",
     "category": "poetry"
   },
   {
      "country": "ES",
      "category": "science"
    }
   ]') as x(country text, category text);

On how elegant this is does not matter,  something has to parse the json text and turn it into a record,  postgresql may be able to do it faster than python.
Neither side looks pretty and neither side is fast because of the casting and text parsing that is going on


Need to deny DML access through SSMS using web app credentials A user can login to some web application and can submit any application which will affect the data(insert/update) in database, but that specific user should not be able to manipulate the data through SSMS login with the same credentials.

How can we configure this role?

The behavior of a role can't be defined by the application being used.

There are a couple of approaches that might work, but both will require code changes:

Enforce all data manipulation to go through stored procedures (you can grant execute to the app credentials and deny direct insert/update/delete). The app would call the stored procedures. To ensure users aren't calling the procedures directly in SSMS (or from their own VBScripts or other local code), the procedures could check APP_NAME() to decide whether or not to proceed.
If you can't enforce all data manipulation through stored procedures, then you could have INSTEAD OF triggers on all (or the most important) tables, which would perform a similar check against APP_NAME(), and then either perform the operation or not.

Keep in mind that the application name can be spoofed, so anything that relies on the output of APP_NAME() is vulnerable to tampering.

A more foolproof way would be to create separate logins altogether - better guard the credentials for the app, which you trust, from your developers, who you don't. (And as TomV mentions below, use Windows authentication for the app, so developers will have a much harder time pretending to act on its behalf.)


PostgreSQL versus MySQL for EAV structures storage Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?

Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling.

In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an "index_merge" feature to substitute for that.

For PostgreSQL you may also be interested in the hstore extension for storing sets of key/value pairs.


List PostgreSQL extensions per database I have a Postgres 9.5 server with 30 databases.
I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin.
I know that SELECT * FROM pg_extension gives me the extensions for the database I am connected to.
I also know that SELECT * FROM pg_available_extensions lists, well, all available extensions.
I also know that SELECT * FROM pg_database lists all existing databases.
Question
How can I create a list that includes the database name and the extensions used in each database?

You connect to each database in turn and query pg_extension.


Can't find the client download for SSMS I need the SQL Server Management Studio client so I can use it to access a 2014 instance but am having a hard time finding it via Google. Where would I find only the client instead of the whole SSMS download? Any ideas on where to find it?

Highly recommend to use latest SSMS.

You can download the latest version of SSMS  from https://msdn.microsoft.com/en-us/library/mt238290.aspx

Previous versions are https://msdn.microsoft.com/en-us/library/mt238488.aspx


Postgres DB is extremely fast with a query with a certain parameter, but extremely slow for another I have a table called stats, where I store every action on a certain website (basically when the user clicks a link, data is saved in the db). The data is modeled with PostgreSQL as

(
id integer NOT NULL DEFAULT nextval('stats_id_seq'::regclass),
company integer NOT NULL,
layout integer NOT NULL,
browsername character varying(50) COLLATE pg_catalog."default",
lang character varying(5) COLLATE pg_catalog."default" NOT NULL,
...)

There are other fields that I don't think are relevant. Basically what I want to do is see which are the most used browsers for a certain company, by counting how many pages are visited by that browser and how many users there are for a given browser.

My query is:

select browsername, count(DISTINCT phpsessid) as visitors, count(*) as pages
from public.stats
where company=1 and createdate>'2017-01-01' and createdate<'2018-12-12'
group by browsername order by visitors desc;

The stats table contains around 80 millions rows. Now, this query is extremely fast if I set company=1 or company=222 (it's the company ID). It takes less than half a second to fetch around 20k rows. However, it is EXTREMELY slow if I set company=13549, for instance (we're talking about literal hours here). Obviously something is wrong, either in the data modeling or in the way I query.

How come there's such a difference for different companies? The DB was not done by me, so I apologize if I left something useful out, and feel free to ask.

The indexes are:

CREATE INDEX stats_new_company_13549_index
ON public.stats USING btree
(company, createdate)
TABLESPACE pg_default    WHERE company = 13549

CREATE INDEX stats_new_company_14863_index
ON public.stats USING btree
(company, createdate)
TABLESPACE pg_default    WHERE company = 14863

CREATE INDEX stats_new_company_createdate_cet_index
ON public.stats USING btree
(company, date(timezone('CET'::text, createdate)))
TABLESPACE pg_default;

CREATE INDEX stats_new_company_createdate_index_1
ON public.stats USING btree
(company, createdate)
TABLESPACE pg_default;

Here's the plan for the fast query: https://explain.depesz.com/s/uKmJ

And here's the plan for the slow one: https://explain.depesz.com/s/wysA

Just by looking at the plans you can see it took several minutes for the second one to even query the explain.

I also noticed that after running the explain a couple of times, it got done in reasonable amounts the third time. From that moment onward, the query would also drastically reduce its execution time, from hours to a mere 4 seconds.
It's the second time it happens, and I swear I'm not crazy.  If I change the company ID once again, the query takes hours again. I'm at lost here: is there some index problem?

The fast query retrieves and sorts 26229 rows - for that small number of rows, the sorting can be done in memory, so obviously this is going to be quick. First because retrieving the data only takes ~500ms and then the sorting is done in 50ms.

The slow query retrieves 560135 rows (20 times as many as the first query) but the time it wook - 149939ms - seems quite slow. Maybe your table (or index) is bloated - the number of blocks needed to read that number of rows is way too high I think.

You can run vacuum full analyze public.stats; and see if performance gets better after that.

Or maybe you simply have a very slow harddisk.

The sorting was done on disk, but that only added another 3 seconds to the total runtime.

The question why the same query is sometimes fast and sometimes quick (especially when run the second time) is more often that not answered with: caching effects. When you re-run the second query you will probably see a lot of the "shared read=318147" information turn to "shared hit=..." which means those blocks were already in the cache.


How to remove a table partition I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure and schema intact.

I tried:

select *
into [dbo].[new_Non_partitioned_Table]
from [dbo].[partitioned_table]

I then dropped the partitioned table and renamed the new table. However, it caused me to lose the data structure and some dependency (FK) that forced me to drop constraints. This is for production and loss of data is not an option.

Is there a better way to remove a partition from an existing table?

Rebuild the indexes on the table to a filegroup instead of a partition schema.

Do so in the following order:

CREATE INDEX  ON [MyFileGroup] WITH DROP EXISTING on the cluster index
CREATE INDEX  ON [MyFileGroup] WITH DROP EXISTING on all non clustered indexes (one at a time)

If you can take some downtime, consider scripting and dropping the non-clustered indexes first, rebuild the cluster index and then reapply the non-clustered indexes (targeted at the filegroup, not the partition schema of course)

This is faster than merging the partitions together because it can run at high concurrency using the SQL Server index build engine.

With this trick, the schema remains in place, no need to move it all to another table. Depending on your SQL Server edition, you may even be able to do this online.


How to attach an .mdf file that was not properly detached? One of our clients had a SQL Server 2000 database, on a system which is no longer running. They have no idea what was in the database. They have given me the .mdf and .ldf files, and want us to investigate and extract information if any.

I don't think the database was properly detached when these files were backed up, since I am getting the following error when I try to attach these files to my SQL Server 2008 express edition:

  Could not continue scan with NOLOCK due to data movement. Converting
  database 'GRMStemp' from version 539 to the current version 655.
  Database 'GRMStemp' running the upgrade step from version 539 to
  version 551. (Microsoft SQL Server, Error: 601)

What should I do to attach the database in this situation?

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

on your 2008 instance, create a database with a data file of the
exact same size (I wouldn't bother trying to put a 2000 instance
anywhere)
shutdown SQL Server
rename the new database's mdf file, and paste the old one in its place
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?


Combining previously different rows after changing value during query I'm currently running a query that is supposed to return a salesperson's aggregated revenue, grouped by individual clients.

Because of how user input was handled, there are a lot of near-duplicate rows with minor mispellings/additions to client names. As you can see in the code below, I'm trying to trim off one of the problem strings in order to prevent duplicate rows for the same clients.

However, though the CASE statement successfully removes "@iraPrefix", the rows with now-identical client names are still not grouped together because their revenue fields are not being summed.

How should I approach this?

DECLARE @iraPrefix VARCHAR(7)
DECLARE @iraLen INT
SET @iraPrefix = 'FOO BAR '
SET @iraLen = LEN(@iraPrefix)

SELECT [client] = (
           CASE SUBSTRING([name], 1, @iraLen)
               WHEN @iraPrefix THEN SUBSTRING([name], @iraLen+2, (LEN([name]) - @iraLen))
               ELSE [name]
           END
       )
       , CONCAT( sal.firstname
               , ' '
               , sal.lastname) AS [salesman]
       , [revenue] = SUM(net)

Here's an example of the query results without prefix removal:

An example of the current query results:

And an example of the results I want:

Just aggregate one more time.

You can do this by

Materialising your intermediate resultset (SELECT ... INTO #intermediate FROM...) and then aggregating the cached intermediate results
Wrapping your current query in a set of brackets and SELECTing against whatever alias you assign to it.

See this fiddle for an example. But for verbosity's sake, assuming your data is all stored in a table called barfoo, then one way you could do this using a Common Table Expression (or CTE) is...

DECLARE @iraPrefix VARCHAR(7);
DECLARE @iraLen INT;
SET @iraPrefix = 'FOO BAR ';
SET @iraLen = LEN(@iraPrefix);

WITH L1 AS (
   SELECT [client] = (
           CASE SUBSTRING([name], 1, @iraLen)
               WHEN @iraPrefix THEN SUBSTRING([name], @iraLen+2, (LEN([name]) - @iraLen))
               ELSE [name]
           END
       )
       , CONCAT( sal.firstname
               , ' '
               , sal.lastname) AS [salesman]
       , [revenue] = SUM(net)
   FROM barfoo as sal
   GROUP BY [name],CONCAT( sal.firstname
                      , ' '
                      , sal.lastname)
)
SELECT
        [client],
        [salesman],
        SUM([revenue]) AS [revenue]
FROM L1
GROUP BY [client],
        [salesman];


Is it a good practice to create tables dynamically in a site? A friend asked me to build a site with a few "static" and "dynamic" tables.

In fact he wants to have a few tables which can't be deleted, and some "dynamic" tables which can be created directly from site users, according to their needs.

I.E. if the user needs some "optional" that in the current db doesn't exist, he creates a new table for his specific need.

I think this is not a good way to do it, i think it is better to have a list of all possible optionals in a table and then flag them for each user, i.e.:

Table garden(id, camellia, californa_poppy, azalea) contains a generic garden with 3 flower species that the garden can contain

Table users(id, username, some_user_infos, garden_id) contains a generic user and it's garden id.

If garden_id value from table users is 1, and line with id 1 from table garden is (1, 1, 0, 1) It means that user with garden_id=1 has every allowed flower but California Puppy.

This is my idea.

My friend says it would be better to dinamically create tables and connect them to the user to dinamically define new flowers.

Wouldn't it be better to have every possible flower as a field of the garden table and flag it as i want to do?

Short answer: In general it is no good practice to dynamically create tables and columns. But there can be rare special cases there it is good practice.

Long answer:

You may run (at least) on the following problems with your solution:

A user can have only 1 garden, because the garden_id is in the table "users".
If you want to add some more infos to a flower, e. g. color or size you need one more column for each flower.
You must know all the flower names (column names) to write the sql-query.

If I understand your requirement correctly, this is a case for an intersection table (many to many relation between flowers and gardens).

Maybe this will solve your needs:

Create the following tables:

Create a table "Flowers" which contains all possible flowers with columns: "Id", "Flower". You may add additional columns which describe the flowers, e. g. color, size,...
Create a table "Users" with colums "Id", "username",... This table contains only the users and no info about his garden or flowers.
Create a table "Gardens" with colums "Id", "User_Id". You may add additional columns which describe the garden, e. g. size. This table contains infos about the garden and to which user the garden belongs. But no infos about the flowers in the garden.
Create a table "Garden_Flowers" with columns "Id", "Garden_Id", "Flower_Id". This table contains the information, which flowers are in which garden. It may contain additional infos, e. g. when the flowers was planted.

Now you can add new flowers into table "Flowers" without the need to add new colunns or tables.
You can define which flowers are in which garden with the intersection table "Garden_Flowers". Just insert one record for each flower with the flower_id and the garden_id.


relation "(table name)" does not exist in non-public schema Windows, pgAdmin, PostgreSQL server hosted on Amazon AWS. Logged in on PgAdmin 4 as admin/owner, accessing an admin created database called 'client'.

I have two identical post-gis enabled tables, one tabled is named 'testboundary' and stored in the public schema, the other is named 'testboundaryNW' and is stored in a new schema called 'NW'.

When I right click the table in public and query this:

SELECT *
FROM testboundary

I get the contents of the table returned as expected.

When I right click the table in the 'NW' schema and query this:

SELECT *
FROM testboundaryNW

I get an error:

ERROR:  relation "testboundarynw" does not exist
LINE 2: FROM testboundaryNW
             ^
SQL state: 42P01
Character: 15

Why is this? Usage/access rights are the same on public and NW schemas.

Turns out, when querying anything other than the public schema, you need to change the syntax of the query from:

SELECT *
FROM testboundaryNW

to

SELECT * FROM "NW"."testbonudaryNW"

if I renamed the table to testboundarynw (no caps on NW) I don't need the "" around it.


SQL Clustering options with 2012 standard Ed i need help to find the best solution (if one exists..) to build a sql cluster using two servers and two sql Standard Edition licenses (2012 or 2014 or 2016).
No shared storage is available
No need for an active/active configuration

I did try searching and reading but still cant understand how to do that, i'm clearly not a dba

thanks for your help!
RL

On a standard SQL Server license on SQL Server 2016 you can setup AlwaysOn Availability Groups on two hosts without the need for shared storage.

On SQL Server 2014 and 2012 the only high availability option you have with a standard license is to create Alwayson Failover Cluster Instances which depend on you using shared storage.

So using the standard edition the only option you have is to run SQL Server 2016 with AlwaysOn Availability Groups. Remember that a FCI or AG will only set you back a single SQL license if the secondary server is not queried. The setup is not that complicated but a bit out of scope, you can start here


How can I invoke an SSIS package from SQL Agent, via T-SQL, using a proxy Building an ETL framework with many packages and a "controlfile" table that orchestrates running them in the right order and managing errors. In order to control the overall operation of the data load I would like to do this:

Make a job(s) in SQL Agent
In the job steps invoke a custom stored procedure
In the stored procedure, call SSISDB.catalog.create_execution and related procs to
execute a package
Run this under a dedicated ETL Windows user account that is NOT a sysadmin and is not the SQL Agent service account, for security reasons.

Everything in steps 1, 2, and 3 works, but if I try to configure the agent job step to run as another account or as a proxy it doesn't work.

Background

Running a single package from an Agent job with the job step type "Integration Services"  works, because there's an option to set the step to run as a proxy user. (New Job > Steps > New Step > Type = Integration Services > RUN AS). The problem is that we need the "wrapper" logic to control all these individual packages.

Running a package from a stored procedure directly, from T-SQL, works.

Running a package from a stored procedure in a T-SQL job STEP in Agent, if the job is owned by a sysadmin works, but it runs as the SQL Server Agent Service Account which is a security problem.

Running a package from a stored procedure in a T-SQL job step in Agent, if the job is owned by a NON sysadmin user fails because of a problem in the Agent's EXECUTE AS code, where the SSIS procedures throw an error like 'could not revert' - which leads me to believe there's a problem in the Agent's EXECUTE AS logic running the T-SQL under the job owner account, then calling the SSIS procedures. I have found some information online which I hope I can re-locate an link to.

Running a package from a T-SQL Agent job step has no RUN AS capability to set the code to run under a proxy account. This is a connect item :-).

Running a package from a T-SQL Agent job where I manually put in EXECUTE AS logic in the job step code also doesn't work.

Stumper?

I ended up using a PowerShell step in SQL Agent to work around this: job step type "PowerShell" with a short script, which uses Invoke-Sqlcmd to call the stored procedure. Feels awkward, but it works under the alternate credentials.


Column definition for a SQL Server equivalent to Access BOOLEAN type The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL).  The SQL Server BIT type returns 1 for True and 0 for False and also allows NULLs.

I'm looking for a T-SQL column definition using a combination of smallint, check constraints, and NOT NULL to replicate the functionality of the Jet/ACE BOOLEAN datatype.  In other words, the column would only accept the values 0 and -1.

To address your concerns about BIT:

You can set your BIT column to NOT NULL.
You can use -1 when setting a BIT column to "true".
You can create a view that translates to -1, but +1 should be fine anyway unless your application explicitly checks for the numeric -1 (anything but zero should yield true in your client language).

CREATE TABLE dbo.foo(bar BIT NOT NULL, blat BIT NOT NULL);

INSERT dbo.foo SELECT -1, 0;

SELECT bar, blat, -CONVERT(SMALLINT, bar), -CONVERT(SMALLINT, blat) FROM dbo.foo;

Results:

bar   blat
----  ----  ----  ----
1     0     -1    0

The nice thing about BIT over TINYINT/SMALLINT is that if you have between 1-8 BIT columns, they can fit into a single byte.

In all of these cases, you still aren't going to be able to say

WHERE NOT BooleanColumn
-- or
WHERE !BooleanColumn

You will still have to say

WHERE BooleanColumn = 0


Joining mysql tables I am trying to join tables for a search form, and when I do the search exports the result x3 the output could you guys give me a hand please?

   $raw_results = mysqli_query($db,"SELECT * FROM inventoryuser, inventorypc
      WHERE (`firstname` LIKE '%".$query."%')
      OR (`lastname` LIKE '%".$query."%')
      OR (`department` LIKE '%".$query."%')
      ")
      or die(mysqli_error($db));

The output

I need to search inventorypc for pc_model and pc_serial that's under a different table.

My tables are:

CREATE TABLE `inventoryuser` (
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL,
  `lastname` VARCHAR(30) NOT NULL,
  `department` VARCHAR(30) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inventorypc` (
  `pcid` INT(11) NOT NULL AUTO_INCREMENT,
  `pc` VARCHAR(30) NOT NULL,
  `jcid` VARCHAR(30) NOT NULL,
  `job_number` VARCHAR(30) NOT NULL,
  `product_id` VARCHAR(30) NOT NULL,
  `pc_model` VARCHAR(30) NOT NULL,
  `pc_serial` VARCHAR(30) NOT NULL,
  `software` VARCHAR(30) NOT NULL,
PRIMARY KEY (`pcid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I the code I have is

  $query = $_GET['query'];
  // gets value sent over search form

  $min_length = 2;
  // you can set minimum length of the query if you want

  if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

      $query = htmlspecialchars($query);
      // changes characters used in html to their equivalents, for example:  0){ // if one or more rows are returned do following

          echo "

                    First Name
                    Last Name
                    Company Laptop
                    Company Mobile
                    Department
                    Actions

          ";

              while($results = mysqli_fetch_array($raw_results)){
              // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

              echo "

                      ".$results['firstname']."
                      ".$results['lastname']."
                      ".$results['pc']."
                      ".$results['mobile']."
                      ".$results['department']."

                        View
                        Edit
                        Delete

                ";
              }

          }
          else{ // if there is no matching rows do following
              echo "No results";
          }

      }
      else{ // if query length is less than minimum
          echo "Minimum length is ".$min_length;
      }

      echo "
      ";

Plain  JOIN a,b generate the cartesian product where each row from a is combined with each row of b, despite of related they are or no. There are not only duplicates for certain user but also the rows for PCs not related to the certain user. If you restrict the output by DISTINCT there is no guarantee you'll get the proper row from the cartesian set. You have to JOIN .. ON by some column but you have to provide your tables structure for further analysis.


Calculate storage size of rows? In a multi-tenant only one DB, one column is used to isolate customers data, that is customer_id.

Is it possible to get the storage size of a certain customer in all tables?

You can use pg_column_size() to get the size of a complete row, not only for a single column.

So to find the size of all rows for a specific customer, you can use something like this:

select sum(pg_column_size(t) + 24)
from the_table t
where customer_id = 42;

(The 24 is the storage overhead per row in a table)

You would need to do that for all tables you are interested in.

This does however not include the size of those rows in the indexes.


Getting the page name from a web URL in Oracle I have a list of page URL's in my database table under the column heading URL. There are many different prefixes on each page name for example:

Test1/Admin/Page7.asp
Test1/One/Page3.asp
Test1/Another/Page2.asp
Testing1/Admin2/Page12.asp

Currently I have created some SQL to get a distinct list of the above URL's. However what i need is just the page names. I need to get rid of any text before the last "/". How would I be able to do this within Oracle?

My SQL currently looks something like this:

SELECT DISTINCT URL FROM TABLE1 WHERE URL IS NOT NULL ORDER BY TABLE1.URL

For something more complex you might need a regular expression, but in this case, the following should suffice.

SELECT DISTINCT SUBSTR(URL,INSTR(URL,'/',-1)+1)
FROM TABLE1
WHERE URL IS NOT NULL
ORDER BY 1;

Sample data.

create table table1 (URL Varchar2(1000));
insert into table1 values ('Test1/Admin/Page7.asp');
insert into table1 values ('Test1/One/Page3.asp');
insert into table1 values ('Test1/Another/Page2.asp');
insert into table1 values ('Testing1/Admin2/Page12.asp');
insert into table1 values ('Page7.asp');
commit;


InfluxDB Downsampling I have a InfluxDB that is limited to 40 GB, it's being filled by AppMetrics .NET integration performance metrics on each event in the code, usually hundreds of events per second.
My problem is that this setup was running for a month and now has reached a state where it is at the limit and no new data/snapshots can be created.
My question is there a way to process the DB in a way only 15 min aggregations are left for data older than 1 day?

Yes, influx provides continuous queries(CQ).

Continuous queries (CQ) are InfluxQL queries that run automatically and periodically on realtime data and store query results in a specified measurement.

Syntax

CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
BEGIN
  <cq_query>
END

where cq_query is

SELECT <function[s]> INTO <destination_measurement> FROM <measurement> [WHERE <stuff>] GROUP BY time(<interval>)[,<tag_key[s]>]

Example:

CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END

P.S One thing needs to be noted here is that, the fields created into the new measurement are prefixed with the function name. In above case, mean_passengers would be inserted as column name. To overcome this you can use 'AS'.

Refer this link for detailed https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries/


Best of MyISAM and InnoDB Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?

The gen_clust_index (clustered index) under the hood of InnoDB houses entries of primary keys along with rowids. What is interesting about the use of the gen_clust_index is the fact that any non-unique indexes you create will always have a corresponding rowid for the gen_clust_index of a table. Thus, there are always double index lookups, one for the secondary index and one for the gen_clust_index.

Any attempts to improve the layout of a table or primary key gets nullified because of the gen_clust_index, or at least marginal results at best.

EXAMPLE

Some people attempt to sort a MyISAM in PRIMARY KEY order. According to MySQL Database Design and Tuning, Page 236 Paragraph 7, under the subheading "Storing a Table in Index Order":

  If you frequently retrieve large ranges of indexed data from a table
  or consistently sort results on the same index key, you might want to
  consider running myisamchk with the --sort-records option. Doing so
  tell MySQL to sort the table's data in the same physical order as the
  index, and can help speed these kinds of operations. Alternatively,
  you can combine the ALTER TABLE statement with an ORDER BY a
  particular column option to achieve the same results.

Granted, this works and works effectively FOR MyISAM. You could perform ALTER TABLE ... ORDER BY col1,col2,...,coln against InnoDB where the columns may or may not be that of the PRIMARY KEY. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.

Some people can make the table's row format FIXED using ALTER TABLE mydb.mytb ROW_FORMAT=Fixed; and can get a 20% increase in read performance without any other changes. This works and works effectively FOR MyISAM. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.

You could perform the following on an InnoDB table named mydb.mytb:

CREATE TABLE mydb.mytc LIKE mydb.mytb;
INSERT INTO mydb.mytc SELECT * FROM mydb.mytb ORDER BY col1,col2,...coln;
ALTER TABLE mydb.mytb RENAME mydb.mytd;
ALTER TABLE mydb.mytc RENAME mydb.mytb;
DROP TABLE mydb.mytd;

This will put the table in rowid order in the gen_clust_index. This may produce marginal results for InnoDB at best because ... that's right ... you must consult the gen_clust_index each time.

Now, let's get a little ridiculous. There is a NoSQL interface to query (SELECT only) MyISAM and InnoDB called the HandlerSocket (formerly called HANLDER) interface. This gives you access to data which lets you bypass all SQL, ACID, and MVCC protocols. Although it is possible, IMHO WAY TOO COMPLICATED TO CODE AND MAINTAIN. AFAIK there is nothing in print stating whether the HandlerSocket interface interacts with the gen_clust_index or not.

In summary, there are many ways to skin a cat. In this case, you cannot get a hold of the cat (the gen_clust_index). I guess this is why MyISAM continues to exist for its read performance, its pliability in table ordering, table row format, and the tools in support of it. InnoDB is will remain designed around its ACID-compliant nature until some brave soul takes the InnoDB source code and transforms it into something that has the best of both MyISAM and InnoDB.


Drop Table and recreate Permissions So we followed an best practice by Andreas de Ruiter (https://blogs.msdn.microsoft.com/andreasderuiter/2015/04/20/transforming-data-in-a-data-warehouse-through-sql-views)

The Idea behind it is, that you write SQL Views which are easy to maintain and deploy. You use stored procedures to use these views and create tables from it.
He suggests dropping or truncating the tables to maintain consistency over the data. I think it's the easiest approach without writing any complex update logic.

On these tables we have the need to set specific user permissions. By dropping the table we would lose them. So for us the question is, how could we drop the table and recreate the existing permissions on the newly created table?

Export the table permission before dropping:

    SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as +
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
--  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs


Types of ways to achieve Many-to-Many relationship between tables in DB? I have two tables namely 'City' and 'Travels'. City Table will have list of cities in each row with city_id as the Primary Key and Travels Table will have list of travels in each row with travels_id as the Primary key.

Scenario:

  One City has many travels for service and One Travels has many city destinations as their service. (i.e) Many-To-Many Relationship in the Table.

         city table                     travels table
+------------+------------+      +-------------+----------------+
|    city_id |  city_name |      | travels_id  | travels_name   |
+------------+------------+      +-------------+----------------+
| 1          | coimbatore |      | 1001        | KPN travels    |
| 2          | mumbai     |      | 1002        | Garude travels |
+------------+------------+      | 1003        | SRT travels    |
                                 +-------------+----------------+
  city_travels join table
+-------------+----------+
| travels_id  | city_id  |
+-------------+----------+
|  1001       |   1      |
|  1001       |   2      |
|  1002       |   1      |
|  1003       |   1      |
|  1003       |   2      |
|  1003       |   3      |
+-------------+----------+

This is how the Many-to-Many relationships between tables are made.
But my doubt question is that if either of city table or travels table row increases the city_travels join table row will increase enormously.

Is there any other way to achieve Many-to-Many relationship between tables. I searched a lot but i couldn't find any other solutions.

Please let me know, if there any other options or trick to achieve this. Moreover please comment if there is any wrong in this question.

Thanks a lot in advance

When you get to 100 million rows, we might need another chat.  Meanwhile, this describes the optimal schema for many:many.


PostgreSQL: Displaying inheritance relationship? I want to know if a certain PostgreSQL table is a part of any inheritance relationship (i.e. is a parent or a child of other table(s)).

Is there a query for that? Where is the inheritance data stored?

SELECT relname
FROM pg_class c,pg_inherits i
WHERE c.oid=i.inhrelid;

I am the guy that asked Adam about this, I found a solution so I added it here for future reference of others.


Constraining inserts/updates for summary tables with complex validation logic I am designing some MySQL tables and am running into an issue that I believe can be solved by a uniqueness constraint, but not sure exactly how. My actual use case is complicated and confusing, so I've simplified it down to a meaningful analogy involving schools, classrooms and test grades (but note: this is not homework - I'm a developer trying to solve a real-world problem here).

Some domain entities and business logic for a backstory:

There are Students
Students take Classes
In the Classes you get Tests which are graded
Each Quarter students have to sign up for a particular CourseLoad, which is a pre-determined sets of classes to take. A CourseLoad is a particular set of Classes that are all taken at the same time (e.g. the Core Math CourseLoad might consist of: MATH 205, MATH 210, and MATH 326, etc.)

Here are my tables as they currently stand, and although I'm 100% open to refactoring them, I am pretty happy with the given design (also please note: please don't linger on naming conventions here, its the design which is most important!):

students
========
student_id : INTEGER PRIMARY KEY AUTO INCREMENT (hereafter 'IPKAI')
student_name : VARCHAR(100)
student_dob : DATETIME

classes
=======
class_id : IPKAI
class_name : VARCHAR(50)    // Ex: 'CHEM 101'

courseloads
===========
courseload_id : IPKAI
courseload_name : VARCHAR(50)   // Ex: 'Core Math Courseload', 'General Ed Courseload', etc.

// Classes and Courseloads are many-to-many: The same Class can appear inside multiple
// Courseloads, and the same Courseload can consist of many Classes
classes_to_courseloads
======================
classes_to_courseload_id : IPKAI
class_id : FK INT (classes)
courseload_id : FK INT (courseloads)

quarters
========
quarter_id : IPKAI
student_id : FK INT (students)
courseload_id : FK INT (courseloads)

tests
=====
test_id : IPKAI
student_id : FK INT (students)
quarter_id : FK INT (quarters)
class_id : FK INT (classes)
test_date : DATETIME
test_grade : INT

So finally, my problem:

A Test occurs inside a given Quarter (reporting period). A Test also belongs to a Class, which itself belongs to 1+ CourseLoads. A given Quarter also has a CourseLoad assigned to it (since the student must select which CourseLoad they are going to tackle that particular quarter).

I'm worried about validation here. Let's take a living, breathing example to make this understandable:

Given a Quarter with a quarter_id=14...
A Student with student_id=508 signs up to take a 'Core Math CourseLoad' (courseload_id=5)
The Core Math CourseLoad consists of 3 Classes:

MATH 205 (class_id=8); and
MATH 210 (class_id=18); and
MATH 326 (class_id=92)

At some point during that quarter, the student takes a mid-term for MATH 210, which is test_id=38321 and the student receives a grade of 94

Given all this info, the tests record for this mid-term would look like:

test_id     student_id    quarter_id      class_id    test_date   test_grade
============================================================================
38321       508           14              18          2015-07-23  94

What I need to validate is: That it becomes impossible for the student to have a test record during a given quarter for a class that is not apart of that quarter's courseload. In other words:

test_id     student_id    quarter_id      class_id    test_date   test_grade
============================================================================
38326       508           14              21          2015-08-19  85

...should be an invalid entry and should violate some constraint or raise an error if we try to insert that as a new tests record. Because, class_id=21 is not a part of the Core Math CourseLoad.

I am looking for an elegant solution here. Again, I think this can be solved by introducing a UK to the tests table, but would consider any solution (even heavy table refactoring) if it means solving the problem correctly.

That sort of validation is not a candidate for a column or table level Constraint. You have two options depending on how you're interacting with this database:

Validation is done via the 'front end' interface, it won't let you make 'bad' picks. You can then validate them again within a SQL query to double check if so desired.
Validation only occurs within a SQL query, allowing the 'front end' to pass along potentially 'bad' data.

If you choose to validate in the SQL query you can either build explicit checks using typical IF THEN iterative logic or build it into your INSERT queries. To do the latter your tables need to contain the data in a relationship that mimics you validation scheme, which luckily it looks like your tables do. Something like the following should do what you're looking for (@ denotes variables, I'm assuming your passing those in or setting them ahead of time):

INSERT INTO Tests (student_id, quarter_id, class_id, test_date, test_grade)
SELECT student_id
       ,quarter_id
       ,class_id
       ,test_date
       ,test_grade
FROM (SELECT @student_id AS student_id
             ,@quarter_id AS quarter_id
             ,@class_id AS class_id
             ,@test_date AS test_date
             ,@test_grade AS test_grade ) AS InsertSet
        INNER JOIN quarters Q
            ON InsertSet.quarter_id = Q.quarter_ID
               AND InsertSet.student_ID = Q.student_ID
        INNER JOIN classes_to_courseloads CTC
            ON Q.courseload_ID = CTC.coarseload_ID
               AND InsertSet.class_id = CTC.class_id

By turning the variable set into an inline table we can join against the other tables to insure the data is valid. We INNER JOIN to the Quarters table to insure the quarter_ID and student_ID match up, thus verifying he is enrolled for the quarter. We then INNER JOIN onto Classes_to_Courseloads to verify that the class_ID the test grade is for matches up with his courseLoad_ID the student_ID was signed up for in the Quarters table. If any of the JOINs in that query fail you'll get no results and thus won't INSERT anything into the Tests table.

As for throwing back a message or error you can run a SELECT query against Tests using the variables to try and return the test_ID, if you get a result the INSERT was a success, if you don't it failed.


Logical page error while attempting to insert data I am getting the following error while trying to insert a row in a table:

  Attempt to fetch logical page (1:12032) in database 19 failed. It
  belongs to allocation unit 72057594069450752 not to 72057594051166208.

What could be the reason?

I think you have a database corruption so try the command

DBCC CHECKDB

And The command

 dbcc checktable ( 'dbo.TableName', REPAIR_REBUILD )

Before that Refer to BOL to see about the commands .


Database Encryption scan for database  was aborted. Reissue ALTER DB to resume the scan I disabled TDE on a database and once completed I dropped the DB certificate for it. Later I performed a manual failover to node2 and ran steps to enable TDE once again. However encryption state is stuck in state 2 with 0% of progress.

The error log shows:

  Database Encryption scan for database  was aborted. Reissue ALTER DB to resume the scan.

Every time I reissue the alter statement to turn encryption on it shows the same error. If I try setting encryption off I get an error that an encryption process is ongoing and can't get turned off.

I read this question but when I try to delete the certificate it gives me an error that the certificate is in use.

SQL Server 2016 TDE: Database encryption scan for 'DB_name' was aborted

Changing the account you use to run SQL Server means SQL Server is unable to unlock the service master key.

The service master key is protected (encrypted) using the Windows Data Protection API, which includes using the machine key and the account key for the GMSA.

When you change the service account, you prevent SQL Server from accessing the correct account key, and thereby prevent SQL Server from being able to decrypt the service master key, and in turn the TDE encryption key.

Changing the service back to the original account allows SQL Server to access the correct keys that allow it to decrypt the service master key, and thereby correctly decrypt all the other keys used in TDE.

I have a couple of related posts at SQLServerScience.com:

Filesystem Security and TDE keys
Cannot use Certificate, Private Key not Present


Why SQL Server 2008 R2 do full installation for just adding another instance on the same machine Why SQL Server 2008 R2 does a full installation for just adding another instance (only database engine) on the same machine? I thought it will be quick a installation, but it's not. What is SQL Server doing, copying/registering all the files again for this new instance?

Please clarify me.

Actually Sql server has two types of component\features.
1.  Instance specific features
2.  Shared Features
And Database Engine, Analysis Services, and Reporting Services are part of instance specific features which has to copy many files for installing new instance.

http://msdn.microsoft.com/en-us/library/ms143786.aspx


Primary filegroup is full SQL Server 2008 I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error:

  Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

There is another table in the database with around 25 million rows.  This database will only be used on a single machine, and it will be designed to mine data that already exists and it under no circumstances will ever grow beyond its current size.

For a situation such as this, what's the best way to tackle this so SQL Server doesn't complain?  Will the solution matter that this DB won't be exposed to multiple users?

Follow these steps:

Identify how much space you want to add to the database storage allocation:

Open windows explorer
Right click on the disk drive that your database files exist on
Select properties
Check how much disk space is available and decide how much of this you want to allocate for the database
(Suggestion: Leave at least 20% disk space free if you house the database files on the same disk as your OS {Sub-Suggestion: Don't do this! Rebuild/migrate your data to it's own disk; you're screwing yourself on I/O.} and leave at least 8% for a pure data disk; these numbers are estimates of what I think the actual percentage suggestions are.)

Update the storage allocation for the database.

Open SSMS
Click the "View" tab
Select "Object Explorer"
Expand the "Databases" folder
Right click the database your trying to bulk insert into
Select "Properties"
Click the "Files" list option from the "Select a page" area at the left of the properties window
Find the "Database files" row with the "Filegroup" as "PRIMARY"
Add whatever number of megabytes you want to add to the database allocation to the "Initial Size (MB)" number
Hit "OK"  (You might also want to consider your "Autogrowth" values while you're here.)

You want to give your database as much storage allocation as you can afford to give it. If it runs out of space you'll receive this error without auto-grow on and if auto-grow is on you'll take a performance hit each time it has to auto-grow. If you are simply out of disk space then that is your answer and you need a bigger disk.