texts:
I know I can't have a direct answer to this previously written somewhere, because this is somehow an special situation.

I installed a SQL Server 2012 Business Intelligence last month. I have found some issues, and happly noticed there's a service pack 1 already.

On the other hand, I was asked to perform an Edition Upgrade from SQL Server 2012 BI to the Enterprise version.

Well, I'm not sure if the provided Edition Upgrade option that comes with the SQL Server 2012 installation will work after I applied the path.

So, seems like I should run the upgrade to Enterprise first, then install the patch, but I'm getting an error while trying to do this:

  SQL Server Setup has encountered the following error:

  Attributes do not match. Present attributes (Directory, Compressed) ,
  included attributes (0), excluded attributes (Compressed, Encrypted).

  Error code 0x84B40000.

If I go to the link I get:

  We're sorry
  There is no additional information about this issue in
  the Error and Event Log Messages or Knowledge Base databases at this
  time. You can use the links in the Support area to determine whether
  any additional information might be available elsewhere.

Well, I would be glad to get the Edition Upgrade first, but I can't find a lot of information about this error.


texts: Can I find all JSON scalars in PostgreSQL 9.3?

texts:
We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see this by comparing http://www.postgresql.org/docs/9.4/static/functions-json.html with http://www.postgresql.org/docs/9.3/static/functions-json.html

9.4 has a function, json_typeof which returns the type of a JSON object (as a string: 'string', 'number', 'array', 'object', etc.) I would like to know if there is any practical way in 9.3 of retrieving all rows which are scalars (or, equally useful for us, which are not objects).

We would like the comment column of a given table to always be a JSON object. This means that we can do queries like:

SELECT comment->>'author' as author_name
FROM our_table

If for some row the JSON in the comment column is an object, but which does not have any such key, you simply get back a NULL.

However, if there is even one scalar in the table, the query fails with the error

ERROR: cannot extract element from a scalar
SQL state: 22023

This is a pain, as we have had strings written to this column some of the time, mainly due to errors. Is there any reasonable way of either filtering these out in a query, or identifying them all so that they can be removed in one go?


texts: How can I minimize impact of expensive queries in Database A on Databases B, C, D

texts:
How can I configure SQL Server so that activity in 'Database A" has  minimal impact on users in 'Database B'

The Context

Our (very large) production SQL Servers have a lot of databases on them running various "legacy apps", each database for a particular customer's instance of an application.

The Problem

If a "rogue user" runs a very expensive query--sometimes from a report, other times from the application--it will cause "the other databases" to slow to a crawl.

[I understand that the 'root problem' is the expensive queries; programmers should not allow them to run. Intensive reports should run off a separate reporting databases. Programmers should test on large data set, etc. My question pertains to 'isolating' impact of the databases on each other.]

I suspect, but I have not confirmed that the problem lies in the 'tempdb', the temporary area/database SQL Server uses for sorts it can't fit in memory.

I've seen posts pointing to spreading tempdb over multiple files. Would this help?

https://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files
http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-multiple-files-w01/#.VdzGxrNv4Uw


texts: Is it normal that a data flow task's OnError event handler fires 3x and how to avoid that?

texts:
In an SSIS package there are event handlers on every data flow tasks which send an email with a brief description of the error. I fail the first task by introducing an error condition and get 3 emails.

Is there any way to avoid spamming the support staff with 3x the error messages and just send the first one (or if not possible, any one of the three)?

The first one is related to the actual SQL error condition:

SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"
Hresult: 0x80004005
Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"
Hresult: 0x80004005
Description: "Violation of PRIMARY KEY constraint 'PK_Amount'. Cannot insert duplicate key in object 'dbo. Amount'. The duplicate key value is (2013, 10, 25000.00).".

It is followed by the second one:

SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Report.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Report.Inputs[OLE DB Destination Input]" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.

And finally the last one:

SSIS Error Code DTS_E_PROCESSINPUTFAILED.
The ProcessInput method on component "Report" (29) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (42).
The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
There may be error messages posted before this with more information about the failure.

I already experimented with replacing each task's OnError with the single package's OnError but that one does not seem to have access to the error's pertinent information.

Worst part is that the 1st two emails are arriving in random order. The 1st one arrives 1st slightly more often, but still randomly. The 3d one seems to be always last.


texts:
I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012).

The script is around 500mb and I added a GO every 100 lines.
When I start the script with:

sqlcmd -E -S [server] -d [db] -i "[path to file]"

the connection is made but it is stuck on the first sql query made by sqlcmd and not the script with the error message:

  SET TEXTSIZE 4096

I tried to let it run one afternoon just to see if I get an error or a timeout etc but I get nothing it just get stuck on it. All I can do is stop sqlcmd (CTRL+C in the console).

I tried with Management Studio 2008 and 2012.

Does anyone have an idea why I get this error?

Update :
I tried running it with the -o but nothing was written to the file even after 3 hours.
These are the first lines of the generated script :

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
[delete update or insert...]


texts: I cannot find the answer to question what I wrote in the title. Postgres automatically wake vacuum by autovacuum, but when it's slave does slave wake up autovacuum process?


texts: Is there a more efficient way to run this query for first/max transaction?

texts:
For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only access (no schema or index changes). The table has well over a million rows, so creating and updating a temp table is way too slow; cross apply seems inefficient as well. With a CTE and window function I only have to hit the table twice. But is there a better approach? Simplified example of the table and my query follow:

CREATE TABLE [dbo].[trans](
    [Tran_ID] [int] IDENTITY(1,1) NOT NULL,
    [Account_ID] [varchar](50) NULL,
    [Tran_Date] [datetime] NOT NULL,
    [Tran_Amount] [money] NOT NULL
) ON [PRIMARY];

EDIT: @Peter the table has the below clustered index. This is the only index, there are no primary or foreign keys or other constraints.

CREATE CLUSTERED INDEX [idx_trans_ID_DATE] ON [dbo].[trans]
(
    [Account_ID] ASC,
    [Tran_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

WITH HighestTran AS (
            SELECT
                Account_ID,
                Tran_Date,
                Tran_Amount,
                ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Date ASC) AS FirstTranRow,
                ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Amount DESC) AS MaxTranRow
            FROM
                dbo.trans
            WHERE
                Tran_Amount > 0
            )

SELECT
    t1.Account_ID,
    t1.Tran_Date,
    t2.Tran_Date,
    t1.Tran_Amount,
    t2.Tran_Amount
FROM
    HighestTran t1
    INNER JOIN HighestTran t2 ON t1.Account_ID = t2.Account_ID AND t1.FirstTranRow = 1 and t2.MaxTranRow = 1
ORDER BY t1.Account_ID;


texts:
How can I get ASSY_EXT_QTY to be the quantity of all the path?
IE:
LVL   QTY   ASSY_EXT_QTY
1     1     1
2     3     3 1*3
3     2     6 1*3*2
...

I thought making a path but for the quantities and then somehow multiply them but this seems far reached. This is the code:
select
  LEVEL,
  SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE,
  CASE
    LEVEL
    WHEN 1 THEN COMPONENT_QTY
    ELSE (PRIOR COMPONENT_QTY) * COMPONENT_QTY <--- RIGHT NOW I'M DOING THIS BUT NOT ENOUGH AS WORKS IN THE CURRENT SUB-LEVEL ONLY.
  END ASSY_EXT_QTY
from
  (
    select
...
    FROM
...
    WHERE
...
  ) START WITH FATHER_ITEM_NUMBER = :ASSY CONNECT BY PRIOR CHILD_ITEM_ID = STR_ITEM_ID
) bom

I'd be just SQL, no functions or something like that as I'm not allowed by the environment.
Thanks!


texts: How to update SQL Server 2012 from Evalutation to Developer edition?

texts:
During the installation of a new development infrastructure, I had to install many SQL Server instances on different machines. Some were of course dedicated for production and some other for development. As we did not had licence at the moment, I installed everything with the Evaluation edition as it is written in the MSDN documentation that the Evaluation edition can be upgraded to any other version : SQL Server Edition Upgrades

I have no problem upgrading to the Business Intelligence edition as we bought the licence keys from Microsoft, but the issue is that I have no idea how I can upgrade the remaining instance to Developer edition as there is no way to get a licence key. Even on the MSDN Subscription website it is written that it does not require any licence key to be installed. The documentation does give any clue either about this issue.

So where can I get a valid product key to upgrade from Evaluation to Developer edition as stated in the MSDN documentation?

Thanks.


texts: Foreign key in main table or supplementary table?

texts:
I have two tables with one to one relationship b/w student and Student_detail(kind of supplementary table) table

Design 1

Student
 student_id
 name
 ....

Student_detail

 student_detail_id
  student_id(foreign key to student_id in student table)
  ...

Design 2

Student
 student_id
 name
 student_detail_id (foreign key to student_detail_id in Student_detail table)
 ....

Student_detail

 student_detail_id
   ...

My question is which approach is better ? From design perspective is it better to keep the foreign key in main table(in this case student table) or
in supplementary table (student_detail table ) ?

Per my understanding approach 2 is better as if we add more supplementary tables we don't have to modify main table ?


texts:
This is the issue:
I am reading rows with a timestamp column from a table and I need to make sure that the database returns the timestamp with a fixed width. But I am encountering instances where the PostgreSQL database returns the timestamp without the trailing zeros.

The table I am working on contains UUID, Varchar, Smallint and Timestamp.
I understand that I can use "to_char" as explained here, but what I am really looking for is a way tell PostgreSQL to always return the Timestamp in a given format, regardless of zeros.

Here is an example of the output from the Timestamp column I get, which contains a trimmed Timestamp:

"2014-09-26 17:17:45.999"
"2014-11-26 17:17:46"
"2015-03-26 17:17:46.001"
"2015-04-26 17:17:46.002"
"2015-05-26 17:17:46.003"

This will naturally cause a problem for me since the function that is reading from the database expects to get a timestamp in a given format.

Is there a way to make the PostgreSQL always return a timestamp with a fixed width on SELECT * FROM [table] queries?

Below you see how the fields are created:

"created_UTC" timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'),
"last_modified_UTC" timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')


texts:
The MySQL documentation (5.5) for Lock tables states:

Let's say that I'm trying to lock table1 and table2 in a single query, but table2 is already locked. Will MySQL hold onto a lock on table1 and block until it can lock table2 or will it only hold onto a lock on table1 when it can get a lock on both tables?

In case it matters, assume the InnoDB storage engine.

Update
I just found the following statement on the same page:

  When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released.

Is this confirming that MySQL will block whilst holding the lock on table1? If that is the case, is there a way to query MySQL such that it will immediately return with a failure rather than block if it fails to grab all of the table locks?


texts: Prevent Primary Key increment when an INSERT statement fails in PostgreSQL

texts:
I have a simple table in my PostgreSQL database like this:

CREATE TABLE person_type
(
  id serial NOT NULL,
  name character(55) NOT NULL,
  CONSTRAINT person_type_pkey PRIMARY KEY (id),
  CONSTRAINT person_type_name_key UNIQUE (name)
)

As you can see the id is automatically incremented and the name must be unique. Every time I run an INSERT sql statements, even when it fails, the id is incremented by one.

How can I prevent this? In order that each time a row is inserted successfully, the primary keys are correlative


texts: Another "log_min_duration_statement is ignored" question

texts:
Following the recommendations in log_min_duration_statement setting is ignored I set these two values and then reloaded.

log_min_duration_statement = 2000
log_statement = 'none'

However, I'm still getting lots of sub-second BINDLOG, BEGINLOG and PARSELOG entries in the log file.  What am I doing wrong?


texts: How can I track if a trace is executing?

texts:
I'm sure there's a trace here running and creating logs.

I have no idea who did this in the past ( Its here since my first day in this company ).

There's no trace of it on sp_whoisactive.

Activity monitor shows nothing.

Theres no job running.

There's no job about something like this ( I deleted all old jobs and created my own ones ).

But it's still creating this files. I can't delete if because SQL Server is using it. I can't stop this server because it's the production one. Maybe if I stop the server I can delete the files but they will be created again.

I'm sure there's no sign of a trace running.

This error log file is the same error log we can see on error logs > current


texts: Sql server 2008 R2 installation fails with Error: 17204

texts:
Sql server 2008 R2 installation fails with the below error message in the log message,

This is a part of the sql server log

  Starting up database 'msdb'. Error: 17204, Severity: 16, State: 1.
  FCB::Open failed: Could not open file
  e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf
  for file number 1.  OS error: 21(failed to retrieve text for this
  error. Reason: 1815).

Has anyone come across such an issue. Please help on how to resolve this issue.

EDIT: the sql server error log can be found at http://pastebin.com/TED7vJWX

EDIT:
This is the latest log,
2012-09-20 14:34:29.74 spid6s      Starting up database 'mssqlsystemresource'.

2012-09-20 14:34:29.78 spid6s      The resource database build version is 10.50.1600. This is an informational message only. No user action is required.

2012-09-20 14:34:30.51 spid6s      Error: 15209, Severity: 16, State: 1.

2012-09-20 14:34:30.51 spid6s      An error occurred during encryption.

Same as before.


texts: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists

texts:
I discovered that CREATE TABLE IF NOT EXISTS will fail with insufficient_privilege if the role executing the statement does, in fact, not have the necessary privileges, but does so regardless of whether the table exists or not.  I would expect that if the table exists, the statement succeeds without making any modifications.  Is that reasonable, or am I missing something?

The docs (for 9, 10, 11, and 12) all say simply

  IF NOT EXISTS

  Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the one that would have been created.

I don't know whether a notice about the privilege check taking precedence over the existence check is appropriate here, but I feel like either it should be documented (maybe it is, somewhere else?), or that the behavior should change (could this be a backwards compatibility issue?).

I've worked around it by

DO $$
BEGIN
    CREATE TABLE IF NOT EXISTS tbl();
    EXCEPTION WHEN insufficient_privilege THEN
        IF NOT EXISTS (
            SELECT FROM pg_catalog.pg_tables
            WHERE schemaname = current_schema() AND tablename = 'tbl'
        ) THEN
            RAISE;
        END IF;
     WHEN others THEN RAISE;
 END;
 $$;

but in the end, that query is not in code I own, so my ability to change it is limited, and if I can't get that change accepted, I believe I either have to get Postgres changed (which is admittedly likely a larger lift) or accept that I have to give the user more privilege than I want it to have.


texts: looking for a cleaner way to select from multiple tables

texts:
I'm currently using this code to select from 2 tables using a stored procedure.

DECLARE @now as varchar(26)
set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9)

--Select 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_')

DECLARE @table1 AS varchar(12)
SET @table1  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -0, GETDATE()),102), '.', '_')

DECLARE @table2 AS varchar(12)
SET @table2  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -1, GETDATE()),102), '.', '_')

DECLARE @table3 AS varchar(12)
SET @table3  = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -2, GETDATE()),102), '.', '_')

DECLARE @SQLQuery AS varchar(MAX)
SET @SQLQuery = '

insert into SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP)
SELECT
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP
FROM ' + @table1 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE, UPC2
union all

SELECT
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP
FROM ' + @table2 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

Order by weekrange desc
'

EXECUTE(@SQLQuery)

SELECT * from SMDTemp
where QDATE = @now
and UPC2 = @search

delete from SMDTemp
where QDATE = @now

I now need to add a 3rd table, and potentially up to 12 tables. The problem is when I add a 3rd table with

UNION ALL

SELECT
''' + @now + ''' as QDATE, UPC2,MAX(RTG_WKD.AWEEK),RTG_WKD.WEEKRANGE, SUM(SMVNUM) AS MOVEMENT, ROUND(AVG(SMVCOST), 2) AS COST, ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS RTL, CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS LBS, (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL ELSE round(AVG(smvsugs / smvsugns), 2) END AS GP
FROM ' + @table3 +'  INNER JOIN RTG_WKD ON SMVDATE = RTG_WKD.WKDDATE6
where UPC2 = ''' + @search + '''
and SMVStore = ''' + @store + '''
GROUP BY RTG_WKD.WEEKRANGE,  UPC2

I can execute the query in SQL Server Management Studio but when I run it through a stored procedure the request times out.

Is there a cleaner or more efficient way of doing this? Sample code will suffice, I can plug the data in myself.


texts: SQL Server Secure and Unsecure Traffic Configuration Help

texts:
I have a SQL Server 2017 instance that I have configured to listen on multiple ports 1433 and 1234. I would like to know if its possible to have one port (1234) accept only secure connections while the default port accept both?
I have already set up the instance, verified both ports are working, and installed the certificate but it seems my only option is to force SSL for the entire instance in config manager. Am I missing something? All help is appreciated.


texts: Maxing out the shared pool's memory by executing many UPDATE statements (ORA-04031)

texts:
I was executing a bunch of UPDATE statement. Around ~32.8K got executed and committed, then got the error:

  cx_Oracle.DatabaseError: ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","UPDATE COHORTS_A SET COHORT5...","sga heap(1,0)","kglsim object batch")

This made me think that the SQL area on Oracle DB grew too much to the point of maxing out the shared pool's memory.

Running the follow query would confirm it, but ironically and unsurprisingly I get the ORA-04031 error on it as well:

SELECT * FROM (
SELECT name, bytes/(1024*1024) MB FROM v$sgastat
WHERE  pool = 'shared pool'  ORDER BY bytes DESC)
WHERE rownum < 11;

What could have caused the shared pool to be full? If there's no room left in the SQL area cache, shouldn't older statements be removed?

I am running Oracle 11gR2 11.2.0.3.

Here are the SQL UPDATE statements I was executing and committing (I know I can optimize them):

UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22800
UPDATE COHORTS_A SET COHORT2 = 5 WHERE HADM_ID = 22801
UPDATE COHORTS_A SET COHORT2 = 5 WHERE HADM_ID = 22802
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22803
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22804
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22805
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22806
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22807
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22808
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22809
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22810
UPDATE COHORTS_A SET COHORT2 = 6 WHERE HADM_ID = 22811


texts:
I want to group table entries for each userId only if they are consecutive entries in the table.
I have a table like this.

id userId      transactionTime

1  1           2016-04-13 22:15:44
2  1           2016-04-13 22:40:22
3  1           2016-04-13 22:41:34
4  2           2016-04-13 22:41:34
5  2           2016-04-13 22:42:25
6  1           2016-04-13 23:43:55

Expected Result:

userId    count
1         3
2         2
1         1

Thanks :)

Update:
The transactionTime is ordered in asc. Considering this, we can check the consecutive occurence of same userId and make it a single entry with number of counts.

Consider this, userId=1 occurs three consecutive times from id=1,2,3 this will be first result with userId=1 and count=3. Similarly at id=4 and 5 userId is 2 which will be second row in the result with userId=1 and count=2.


texts: Why can mysqlfrm not read from my .frm file? See debug output

texts:
I'm working on a project at home and learning databases as I go.  Because of my inexperience I've just learned the hard way that cut/pasting .ibd files is an extremely bad idea.  The tables have disconnected from the database and I'm trying to recover them using the methods suggested previously (ie. https://hoststud.com/resources/steps-to-restoring-table-structure-from-frm-and-ibd-files.465/ and http://www.chriscalender.com/tag/innodb-error-tablespace-id-in-file/) but I'm stuck getting the CREATE TABLE details from the .frm files.  I've spent so much time building all the tables I'd rather not start from scratch.

Using mysqlfrm to read the files by spawning a server I got this response:

ERROR: Failed to correctly read the .frm file. Please try reading the file with the --diagnostic mode.

So obediently running the --diagnostic command instead I got this response:

mysqluc> mysqlfrm --server=root:pass@localhost:3306 D:/Databases/MariaDB/smt_a100/actions.frm --diagnostic -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for D:/Databases/MariaDB/smt_a100/actions.frm:
# The .frm file is a TABLE.
# Skipping to header at : 2
# General Data from .frm file:
{'IO_SIZE': 86,
 'MYSQL_VERSION_ID': 100315,
 'avg_row_length': 0,
 'charset_low': 0,
 'create_options': 9,
 'db_create_pack': 2,
 'default_charset': 45,
 'default_part_eng': 0,
 'extra_size': 16,
 'frm_file_ver': 5,
 'frm_version': 10,
 'key_block_size': 0,
 'key_info_length': 33,
 'key_length': 505,
 'legacy_db_type': 'INNODB',
 'length': 1113,
 'max_rows': 0,
 'min_rows': 0,
 'rec_length': 162,
 'row_type': 0,
 'table_charset': 45,
 'tmp_key_length': 505}
# Skipping to key data at : 56
# Reading key part 0.
# Index (key) Data from .frm file:
{'key_names': ['PRIMARY'],
 'keys': [{'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 0,
           'key_length': 1,
           'key_parts': [{'field_num': 1,
                          'key_part_flag': 0,
                          'key_type': 2560,
                          'length': 1,
                          'offset': 0}],
           'num_parts': 1}],
 'num_key_parts': (1,),
 'num_keys': 1}
# Skipping to default data at : 250
# Skipping to keys at : 2f3
# Engine string: InnoDB
# Partition string:
# Skipping to column data at : 45e
EXCEPTION: unpack requires a string argument of length 26
ERROR: Cannot read column header.

Execution of utility: 'mysqlfrm --server=root:pass@localhost:3306 D:/Databases/MariaDB/smt_a100/actions.frm --diagnostic -vvv' ended with return code '1' but no error message was streamed to the standard error, please review the output from its execution.

From other tables I get similar responses with different required string lengths.

My question is how to I get around this exception and finally get the CREATE TABLE code?


texts: Microsoft sql server management studio (SSMS) shortcut for changing size of sql/result panes

texts:
In the good old days there was a shortcut ctrl-B for changing the size of the Query & Result panes in the Query Analyzer sql editor.

In Query Analyzer, just press ctrl-B and the screen splitter handle was in focus. Then repeatedly press up/down arrows until the size was right.

Is there a shortcut for that nowadays, in SSMS?


texts: Does MySQL Replication hamper the performance of my DB?

texts:
I am not exactly a 'Qualified' DBA, but yes i am in charge of my own DB which we use in our social app.

I have recently implemented Master/Slave Replication on my Database for obvious reasons.

What I wish to know is whether MySQL Replication kills the performance of my DB since for every user writing on my Master DB creates an additional write on Slave DB (I might be wrong here).

There is no lag between my slave & master. So, Replication is almost instantaneous.


texts: How to find the time taken to execute a script

texts:
As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert.

Is there any way to find out the time taken to execute that insert statement.
Since its a huge data it's not possible to populate data again.


texts:
My database schema is as follows

  Department (dept_id, dept_name)

  Student (student_id, student_name, major, level, age)

  Professor (prof_id, prof_name, dept_id)

  Class (name, meets_at, room, prof_id)

  Enrolled (student_id, class_name, semester)

I want to find the age of the oldest student who is either a “History” major or enrolled in a course taught by “Michael Miller”.

My attempt was:

SELECT MAX(s.age)
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT max(s.age) from student as s where s.major ='history' ;

The result is:

MAX(s.age)

NULL

20

How can I select the maximum of this query? I would like to get the value 20.

Any help is very much appreciated.


texts: table type parameter vs temp table vs table variable

texts:
I am trying to create a function that will take a table and return a VARCHAR(MAX).

The source table the function will be called against is not constant so I cannot hard-code a table name into the function. I know I can use a stored procedure with an output variable but I am trying to do this with a function so I can call it from within a SELECT (SELECT myfunc(...), ...).

So I figured that my function will need to accept a variable of table data. Based on what I am reading I have three two options:

Use a table type parameter

CREATE TYPE test AS TABLE ( Col1 VARCHAR(50) )
DECLARE @t1 AS test
INSERT INTO @t1 ...
SELECT myFunct(@t1)

Use a table variable

DECLARE @t1 TABLE ( Col1 VARCHAR(50) )
INSERT INTO @t1 ...
SELECT myFunct(@t1)

Use a temp table I thought I could use a temp table but, as far as I can tell, you can't send the temp table as a parameter to the function.

I have two issues and I'm not sure how to address them with either of the three two options:

within the function, I need to be able to get the column names of the passed in table
the input table may be populated with data using a normal SELECT (in my above examples) or an INSERT INTO ... EXEC stored_procedure

Any idea how to do what I need using either option (or any other option that may exist)?


texts:
I am working on a system, there is a cisco load balancer that can handle 40K requests.

There are two app servers. There are 12 or so web services.

and there is a SQL server with 2TB of data.

Bottle neck here is SQL server, it is on a single machine with Quad Core CPU, and 80 GB ram.

There are about 2.5 million items stored in database.

The kind of queries are , select product with some properties which are not indexed.

There is a huge index and we don't want to index everything, because index fragmentation is really hurting performance.

I thought about caching, distributed caching, but some of the queries are not very convenient for caching, such as give me companies that starts with foo. There is a huge combination there.

How would you go working on this issues?

There is more select than insert. mostly select. There are several small select. and there are some joins with 3 tables.


texts: Postgres: multiple selects that exclude results from previous ones

texts:
I have a table of widgets that have a number of connections with potentially overlapping modes. For instance, widget A may have 10 connections, and 5 of those can run in mode A and all 10 can run in mode B. A connection has at least one supported mode. I'm trying to figure out how to design a query that returns a list of devices that support a given number of connections with particular modes.

The simplified schema looks like this:

table widgets
  id
  name

table connections
  id
  widget_id

table modes_connections
  connection_id
  mode_id

table modes
  id
  name

I need to return widget_ids that satisfy filters similar to:

2 connections with mode A AND
2 connections with mode B AND
1 connection with mode C

I can't just join everything together because the first filter for mode A must exclude those results from the other filters, similarly the mode B filter must exclude those results from the mode C filter, etc.

Also, I'm not sure how to prioritize results so connections with the least number of modes have preference. Consider the case where there are 3 connections that support modes A, B, C and two that support mode B. In the filter example above, the mode B filter should select the only-mode-B connections, allowing the A,B,C mode connections to satisfy the requirements for modes A and C.

I'm totally at a dead end. Any suggestions or pointers would be appreciated. Redesigning the schema is also an option.


texts: MongoDB latency decreases as throughput increases

texts:
I am trying to benchmark MongoDB and obtain a throughput vs. latency plot. I am using YCSB as the client and I increase the number of client threads to increase the load on the database. I consistently obtain a plot such as below.

The average latency of a request first decreases as I increase the number of client threads (and thus the throughput), as opposed to increasing monotonically with throughput. I am trying to figure out the reason behind this.
MongoDB runs on 8 CPUs and I have observed this behaviour on different machines. I use the official docker for MongoDB but the behaviour is the same with bare-metal.


texts:
I provide below the raw MySQL query and also the code in which I do that programatically. If two requests are being done at the same time results in the following error pattern:

  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: update user_chats set updated_at = 2018-06-29 10:07:13 where id = 1)

If I execute the same query but without transaction block around it will work without error with many concurrent calls. Why ? (The transaction aquires lock, right ?)

Is there any way to solve this without locking the entire table ? (Want to try avoid table level locks)

I know that a lock is acquired for inserting/updating/deleting tables in MySql with InnoDB but still do not understand why the deadlock happens here and how to solve it in the most efficient way.

    START TRANSACTION;

    insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`)
        values (1, 2, 'dfasfdfk);
    update `user_chats`
        set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

    COMMIT;

Above is the raw query, but I do it in PHP Laravel Query Builder as follows:

    /**
     * @param UserChatMessageEntity $message
     * @return int
     * @throws \Exception
     */
    public function insertChatMessage(UserChatMessageEntity $message) : int
    {
        $this->db->beginTransaction();
        try
        {
            $id = $this->db->table('user_chat_messages')->insertGetId([
                    'user_chat_id' => $message->getUserChatId(),
                    'from_user_id' => $message->getFromUserId(),
                    'content' => $message->getContent()
                ]
            );

            //TODO results in lock error if many messages are sent same time
            $this->db->table('user_chats')
                ->where('id', $message->getUserChatId())
                ->update(['updated_at' => date('Y-m-d H:i:s')]);

            $this->db->commit();
            return $id;
        }
        catch (\Exception $e)
        {
            $this->db->rollBack();
            throw  $e;
        }
    }

DDL for the tables:

CREATE TABLE user_chat_messages
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_chat_id INT(10) unsigned NOT NULL,
    from_user_id INT(10) unsigned NOT NULL,
    content VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id),
    CONSTRAINT user_chat_messages_from_user_id_foreign FOREIGN KEY (from_user_id) REFERENCES users (id)
);
CREATE INDEX user_chat_messages_from_user_id_index ON user_chat_messages (from_user_id);
CREATE INDEX user_chat_messages_user_chat_id_index ON user_chat_messages (user_chat_id);

CREATE TABLE user_chats
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);


texts: How do I GRANT for all tables across all schemas

texts:
I want to

GRANT SELECT
    ON ALL TABLES
    TO foo;

But postgres tells me I have a syntax error at "TO". It expects a GRANT like:

GRANT SELECT
    ON ALL TABLES
    IN SCHEMA public
    TO foo;

We have a handful of tables that have been sharded such that public, shard123, and shard124 all have a table my_table, and public.my_table is the parent of both shard123.my_table and shard124.my_table. I've GRANTed SELECT to foo for public schema, but foo doesn't have permission for any of the shard schemas.

I've also done:

ALTER DEFAULT PRIVILEGES
    FOR ROLE dba
    GRANT SELECT
    ON TABLES
    TO foo;

...which should automatically grant for any new tables and schemas made from now on.

There are 1000 < n < 10000 shards/schemas, so GRANTing separately for each shard/schema is impractical.

How can I GRANT to a role for all existing tables across all existing schemas (all shards)?

Or even better, can I GRANT for all schemas LIKE 'shard%'?


texts: Comparing Two Values when one or more could be NULL

texts:
I am writing a trigger and want to see if one column has changed could be NULL and trying to avoid the following:

IF     NEW.value != OLD.value
    OR (NEW.value IS NULL AND OLD.value IS NOT NULL)
    OR (NEW.value IS NOT NULL AND OLD.value IS NULL) THEN
...
END IF;

This is postgres 9.5 in a function (stored procedure)


texts: PostgreSQL: How to create full copy of database schema in same database?

texts:
How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc.
My version of Postgres is 8.4
P.S. I need to copy schema NOT database


texts: Snowflake: how to allow only Key Pair Authentication connections?

texts:
Is there any way to configure Snowflake to allow only key pair authenticated users' connections?
At the moment you can add RSA_PUBLIC_KEY_FP to any user. But he can connect easily without specifying the key file.


texts:
I have a dedicated server only built to hold MySQL databases.

Currently I have a database that has 173 million records and increasing by 5-15+ million records very day.

My question is what and why I should change in my database configuration to use more resources of my machine.

Machine:

2x Intel E5506 @ 2.14 Ghz
2 x 1000GB SATA 3 RAID 1
24 GB RAM

Software:

CentOS 6.4
MySQL Server 5.5

MySQL configuration file:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 1
query-cache-size               = 10M
max-connections                = 15000
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 18G

# INNODB MULTITHREADED #
innodb_thread_concurrency      = 0
innodb_concurrency_tickets     = 10
innodb_thread_sleep_delay      = 5000
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_replication_delay       = 5000
innodb_flush_log_at_trx_commit = 2
innodb_use_sys_malloc          = ON
innodb_doublewrite             = OFF

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

I have noticed that my queries are getting slower.

Some update queries take about 48-50 seconds.

Here's how it looks:

[root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log
# Time: 131113 17:09:48
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 61.013294  Lock_time: 0.000409 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380588;
UPDATE b SET nf=0, lt=0, ud=1384380501, a='Trademarks'
  WHERE parent_id=36019263 AND link=3780235;
# Time: 131113 17:09:49
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 18.145112  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380589;
SELECT id FROM b WHERE parent_id=62921670 AND link=4808881;
[root@db-1 ~]#


texts: Why MySQL not using index with where clause

texts:
I know many people have asked similar questions but i've gone through many of them and cannot find a similar one or at least not one that could help me understand the issue here.

Here's the query -

select * from FundTxn force index(fundRRN)
where fundRRN = 110030234585 and action = 'capture';

Here's the DDL -

create table FundTxn
(
    fundTxnId bigint auto_increment primary key,
    action varchar(32) null,
    addedOn datetime null,
    tuCreatedOn datetime null,
    tuStatus varchar(32) null,
    fundRRN varchar(255) null,
    fundId bigint null,
    pgdId int null,
    updatedOn datetime null,
    version int default 0 not null,
    constraint fundId
        unique (fundId, txnUpdateId)
);

create index fundRRN
    on FundTxn (fundRRN, action);

create index pgId
    on FundTxn (pgdId, action, tuStatus, tuCreatedOn);

Here's the explain -

This explain doesn't change regardless of whether i use 'use index' or 'force index' or neither. And table size is not in any way less either.

Thanks in advance!


texts: Counting duplicate instances within column in SQL when grouping by another column

texts:
My DB looks something like the following:

id |  Name  | JOBID
----+--------+------
  1 | Sam    | a
  2 | Sam    | b
  3 | Ali    | b
  4 | Tim    | c
  5 | Tim    | d
  6 | Fin    | a
  7 | Mona   | c
  8 | Sam    | e (edited) e is not a duplicate job
  9 | Sam    | f (edited) f is not a duplicate job
  10| Ali    | x (edited) x is not a duplicate job
  11| Fin    | z (edited) z is not a duplicate job

(assume other jobs are all unique and not double-vote (duplicate)

Basically each person will do a job (marked by JobID) once, but the same job can go to two people (max). I want to group by name, but count the instances of duplicate jobIDs that each person got (in column JOBID). How can I do this in SQL?

*Edits - Sorry for editing in the comment section - this is my first time here. You're right, I had made an error in giving the logic. Jobs are either single vote (goes to only one operator) or double vote (go to two operators - hence show as duplicates in JobID column). Any person may have done hundreds of jobs, but I just want to know how many duplicates (double vote jobs) they did.  TO clarify now:

Sam has done 4 (a,b,e,f) jobs but I want to display 2 since he did 2 double vote jobs (a,b).
Ali has done 2 (b,x) jobs but I want to display 1 since he did 2 double vote jobs (b).
Tim has done 2 jobs (c,d) but I want to display 1 since he did 1 double vote jobs (c).

Basically count of double vote is done within the JobID column, but I want it to reflect when I group by name - which person got how may of these jobs. Hope I made this clearer now :)


texts: Backup job doesn't write backup when run on schedule

texts:
We have a database that is backed up to a network drive by a job scheduled to run at 7 AM everyday. The issue is the backup doesn't get created; however, when I execute the job manually, it gets created just fine.

It's not like the scheduled job fails either. If I look at the job history, it indicates that the job ran fine and the backup database operation succeeded.

I don't think it's a permissions issue writing to that folder either since all the other backup jobs also execute using the same user and they run just fine.

Any suggestions?


texts: How to check if column exists in case statement?

texts:
I think a case statement would be the best approach to this but am open to any other suggestions. What I am trying to do is this. The query is generated based on a users input regarding the type report that they are seeking. The thing is all possible tables except for one contain a message_id column. On the odd table, I can get the data though another field which is time_sent. Here is an example of a generated query after user input.
  SELECT
                ps.account_id,
                ba.account_name,
                NULL AS total_users,
                COUNT(DISTINCT(ps.uid)) AS unique_users,
                COUNT(DISTINCT r.msg_timestamp) AS messages_received,
                NULL AS total_clicks,
                NULL AS unique_clicks,
                COUNT(DISTINCT s.message_id) AS messages_sent, // THIS IS WHERE I NEED TO USE A CASE OR SOME OTHER MEANS TO DETERMINE IF THE message_id COLUMN EXISTS
                COUNT(DISTINCT CASE WHEN ps.added BETWEEN '2020-01-01' AND '2020-06-30' THEN ps.uid END) AS new_users
            FROM conversation_facebook_page_subscribers ps
                LEFT JOIN conversation_facebook_page_received r ON r.account_id = ps.account_id AND r.uid=ps.uid AND r.received_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN conversation_facebook_page_sent s ON s.account_id = ps.account_id AND s.uid=ps.uid AND s.send_time BETWEEN '1577836800000' AND '1593561599999'
                LEFT JOIN business_assets ba ON ba.business_id = '3' AND ba.account_type = 'facebook_page' AND (ba.account_id = ps.account_id OR SUBSTRING_INDEX(ba.account_id, '_', -1) = ps.account_id)
            WHERE
                (ps.account_id='104522080903905' OR ps.account_id='180004075382887' OR ps.account_id='85825440048')
                AND
                (r.account_id IS NOT NULL OR s.account_id IS NOT NULL)
            GROUP BY ps.account_id, ba.account_name

I have tried to use a case statement to check the name of the table and based on what the table name is select the appropriate field, but that didn't work. I have searched numerous questions on SO to see if I could find something that would work, but nothing I have found works.
Can someone please point me in the right direction? Thank you in advance for any assistance.


texts: Migrating Production SQL Servers (Availability Group) from one data center to another - questions

texts:
We have two SQL servers combined into Availability Group in data center 1 (dc1):

dc1 server 1: SQL Server 2012 SP3 on Windows Server 2016
dc1 server 2: SQL Server 2012 SP3 on Windows Server 2012 R2

and async replica in Azure

azure server 3: SQL Server 2012 SP4 on Windows Server 2012 R2

We have a new data center (dc2) where we need to migrate production SQL availability group to

The end goal is to have two SQL Server 2017 machines
(server 4 and server 5)  in new data center (dc2) on Windows Servers 2016, combined in AG

After successful migration to dc2 (server 4, server 1 and server 2 in dc1 are going to be abandoned, we do not need to keep server 1 and 2 as replicas in AG after production db is switched to be server 4 and 5

We have below questions:

if deploy server 4 and server 5 at dc2 as SQL Servers 2017, and add them to existing AG as secondaries, will they be kept synchronized with primary (server 1) without any problem, despite difference in SQL Server and Windows versions ?
will adding server 4 and server 5 to AG and then manual failover to server 4 to be new primary (when we are ready to switch applications to use server 4 / listener new IP as production) work fine ?
And then server 4 and server 5 will be synchronizing, and server 1,2,and 3 will break and stop syncing ?
Is my understanding correct ?
are there any better options of migrating production from dc1 to dc2 with short downtime?
is it OK to deploy server 4 and server 5 as virtual machines in dc2 ?
Or it is better to buy dedicated physical machines ?
We want a potential automatic failover from server 4 to server 5 to work perfectly in future. Can having SQL Servers virtualized introduce potential problems with automatic failover function?


texts: Set bit in Table A if values are present in Table B

texts:
I have two tables, Containing up to ~10 million entries.

TableA which has 3 columns: IDA (bigint), IDB (int) and CHK (bit).
(IDA, IDB) is the primary key.

TableB which has 2 columns:  IDA (bigint), IDB (int). (IDA, IDB) is the primary key.

Now I need to set the CHK bit for every IDA+IDB that is in TableA and TableB.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00,
 i.e. SQL Server 2005 Service Pack 2


texts: MySQL my.cnf performance tuning recommendations

texts:
Most of the day, the performance of the server is good, but once per day it stops responding totally even I can't access via SSH, and I have to restart it to make it up again.
I have a dedicated server with the following configuration:
Server Information

16 Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz
64GB Ram
Linux Centos7 / cPanel/WHM + MySQL

MySQLTuner results and recommendations

Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

How can I implement these improvements?

[!!] Maximum possible memory usage: 56.9G (90.76% of installed RAM)

Why the maximum possible memory was increased in that way? Is there a value that needs to be adjusted?
my.cnf file:
[mysqld]
performance_schema = ON
local-infile=0
skip-name-resolve

join_buffer_size=2M
read_buffer_size=2M
tmp_table_size=1G
max_heap_table_size=1G
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

innodb_buffer_pool_size=3800M
innodb_log_file_size=512M
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method= O_DIRECT
max_allowed_packet=100M
open_files_limit=40000
default-storage-engine=InnoDB
#innodb_file_per_table=1
#innodb-thread-concurrency=8
#innodb_lock_wait_timeout = 100
max_connections=200
query_cache_type=0
query_cache_size=0
query_cache_limit=1G
# thread_concurrency= 32
thread_cache_size= 8
key_buffer_size=256M
#Slow Query Log
slow_query_log = ON
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
table_open_cache=3000
[mysqldump]
max_allowed_packet=4G

How could I improve or fix this configuration?
Any suggestions? Thanks folks.


texts:
There aren't any Aggregate functions for Range types in PostgreSQL.

How to aggregate ranges using a merge operation?

This solution works but does not look optimal:

CREATE TEMPORARY TABLE test_ranges (r int4range);
INSERT INTO test_ranges VALUES ('[4, 8)'), ('[12, 45)'), (NULL);

SELECT int4range(min(lower(r)), max(upper(r))) FROM test_ranges;
=> '[4,45)'


texts: Roles and policies in Postgresql 9.5 row-level security

texts:
Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role.
I want to model the following properties but i can't come up with a role hierarchy.

Consider the following tables:

companies(id, name)
users(id, name)
projects(id, name, company_id)
users_companies(user_id, company_id, type)
users_projects(user_id, project_id)

each user can have a different type within a company (admin/employee/customer) and depending on that the policy should change.
So when a user is authenticated, we know his user_id and company_id
Those can be provided id needed in policies with things like

set local user_vars.user_id = 10
set local user_vars.company_id = 20

What would be the roles and policies needed in order to get this

you can see only your company row in the companies table
admins can see all the projects for their company
employees can see only the projects they are assigned to
no one can see/alter entries in users_companies (that is done somehow on signup and with triggers)
admins can see the users_projects entries but only related to the users and project in their company
employees can see users_projects entries only for projects that they are assigned to
admins can see all the users in their company
employees can see the  users in their company that participate in the same projects as they do

I am looking for a base case that i can build on. My main problem is the fact that a user can have different roles/types depending on the current company


texts:
This is my query:

select c.InFavourOf as name,
       c.ChequeDate,
       p.Amount as amount,
       m.No as CNo
from Cheque c
  inner join PaymentDetail p
    on c.MasterID= p.PaymentMasterID
  inner join PaymentMaster m
    on p.ID=m.ID

Result:

I want the date values in column ChequeDate formatted as dd mm yyyy without the time part.

How can I do that?


texts: How to Replace Hash Match (Partial Aggregate) with Stream Aggregate

texts:
My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a suggestion to create another non-clustered index. I do not want to create another index as the table has high I/O.
How do I convert the hash match into a stream aggregate?


texts: MySQL: Rank by Department in an employees table

texts: I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.


texts: mongodb 2.4.1 instance shutdown without log

texts:
I am running mongodb in a replica set of 3 nodes (no sharding). But since 5 days the secondary instance shuts down intermittently, once in 2 days.

Log does not indicate any event related to shutdown, but shows error related to secondary running TTL expiry.

Thu Dec 26 16:28:25.404 [conn12625]  authenticate db: local { authenticate: 1, nonce: "**", user: "__system", key: "***" }
Thu Dec 26 16:28:25.404 [conn12626]  authenticate db: local { authenticate: 1, nonce: "**", user: "__system", key: "***" }
        Thu Dec 26 16:28:29.652 [TTLMonitor] Assertion: 13312:replSet error : logOp() but not primary?
0xdc7f71 0xd8963b 0xa63ca3 0xa60f69 0xa72fd4 0xc3d4c1 0xc3e725 0xd8c233 0xd8cce4 0xe10879 0x35040079d1 0x35038e88fd
 /MONGO/dir/product/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xdc7f71]
 /MONGO/dir/product/bin/mongod(_ZN5mongo11msgassertedEiPKc+0x9b) [0xd8963b]
 /MONGO/dir/product/bin/mongod() [0xa63ca3]
 /MONGO/dir/product/bin/mongod(_ZN5mongo5logOpEPKcS1_RKNS_7BSONObjEPS2_Pbb+0x49) [0xa60f69]
 /MONGO/dir/product/bin/mongod(_ZN5mongo13deleteObjectsEPKcNS_7BSONObjEbbbPNS_11RemoveSaverE+0x10d4) [0xa72fd4]
 /MONGO/dir/product/bin/mongod(_ZN5mongo10TTLMonitor10doTTLForDBERKSs+0xfe1) [0xc3d4c1]
 /MONGO/dir/product/bin/mongod(_ZN5mongo10TTLMonitor3runEv+0x345) [0xc3e725]
 /MONGO/dir/product/bin/mongod(_ZN5mongo13BackgroundJob7jobBodyEN5boost10shared_ptrINS0_9JobStatusEEE+0xc3) [0xd8c233]
 /MONGO/dir/product/bin/mongod(_ZN5boost6detail11thread_dataINS_3_bi6bind_tIvNS_4_mfi3mf1IvN5mongo13BackgroundJobENS_10shared_ptrINS7_9JobStatusEEEEENS2_5list2INS2_5valueIPS7_EENSD_ISA_EEEEEEE3runEv+0x74) [0xd8cce4]
 /MONGO/dir/product/bin/mongod() [0xe10879]
 /lib64/libpthread.so.0() [0x35040079d1]
 /lib64/libc.so.6(clone+0x6d) [0x35038e88fd]
Thu Dec 26 16:28:29.655 [TTLMonitor] ERROR: error processing ttl for db: my_db 13312 replSet error : logOp() but not primary?

There is a mongodb bug reported in ver 2.4/2.4.1 about this error.
https://jira.mongodb.org/browse/SERVER-9261

My question is will this error ultimately cause a replica to crash?

Note: Due to some setting I'm unable to get the core dump of crash.

UPDATE
On Primary node which also shutdown, I studied the /var/log/messages of that date. Found below logs

kernel: INFO: task mongod:11514 blocked for more than 120 seconds.
Dec 28 03:24:33 <hostname> kernel: "echo 0 >
/proc/sys/kernel/hung_task_timeout_secs" disables this message.
Dec 28 03:24:33 <hostname> kernel: mongod        D 0000000000000001
0 11514      1 0x00000080

It seems like the mongod process is being blocked for more than 120 seconds during flush of cache. And during this block the mongod instance is assumed to be shutdown


texts: How to setup authentication for connections on MongoDB?

texts: How to configure authentication for MongoDB when using mongo Shell or any other sort of client?


texts: MySQL multiple index columns have a full cardinality?

texts:
I have noticed indexes like:
+-------------------+------------+-----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name              | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            1 | city_id        | A         |        7851 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            2 | item_id        | A         |      266502 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            3 | vote_date      | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            4 | ip_address_str | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            5 | month          | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |
| archive_city_item |          1 | ARCHIVE_CITY_ITEM_IDX |            6 | year           | A         |     4530535 |     NULL | NULL   | YES  | BTREE      |         |               |

I guess since column 3-6 have a full cardinality this index is not goodly constructed, that it shall be deleted and the new indexes should be built (queries should be analyzed to see if it's (city_id, item_id) or maybe (city_id, item_id, votedate) or some other combination starting with (city_id, item_id...) ?
Correct? Or I'm getting this wrong?


texts: What is the best practice for keeping and maintaining MySQL users?  Say for example I have 5 programmers and a PHP application connecting to a database.  Should I create 6 different users 1 for each of the users and 1 for the application?


texts:
I am new to web development field and particularly very poor at database management. I am developing a web application which resembles Project Management System where new companies can add their profile, create a project under them, under each project they can create a new task and so on....

Hence, my question is right now is should i use one common table for all the new profile (abc & co) created and one common table for all the users registered under profile (abc & co) created, or should i create new tables for each when a new profile is created and new table for each users and project created ?


texts: Why does LIKE not work with CONCAT but works with STRING value

texts:
Please look on the following scenario;

my_db=# select version();
                                                              version
------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Debian 9.6.9-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

CREATE TABLE binary_data ("data" BYTEA NOT NULL);
CREATE OR REPLACE FUNCTION random_string(lengh integer)
RETURNS varchar AS $$
  SELECT array_to_string(ARRAY(
          SELECT substr('abcdefghijklmnopqrstuv',trunc(random()*21+1)::int,1)
             FROM generate_series(1,$1)),'')
$$ LANGUAGE sql VOLATILE;
insert into binary_data ("data") select CONVERT_TO(random_string(64), 'utf8') FROM generate_series(1,1000000);

Now if we run following query it works really well;

select CONVERT_FROM(data, 'utf8') from binary_data where "data" like '%me%' limit 10;

Bu if we run following query;

select CONVERT_FROM(data, 'utf8') from binary_data where "data" like CONCAT('%', 'me', '%') limit 10;

It failed with following error

Kernel error: ERROR:  operator does not exist: bytea ~~ text

Can't understand what's the difference between '%me%' and CONCAT('%', 'me', '%').

First guess was the type must be different to check it I run:

SELECT pg_typeof('%me'), pg_typeof(CONCAT('%', 'me', '%'));

And yes my guess was right;

unknown text

So '%me%' is treated as unknown and CONCAT('%', 'me', '%') is treated as text. To use the same query with CONCAT('%', 'me', '%')::UNKNOWN result to another error.

Kernel error: ERROR:  failed to find conversion function from unknown to bytea

Any one have any idea what's going wrong with above queries?


texts: Query runs in serial and parallel mode in two identical databases on the same instance

texts:
I have two databases in the same SQL Server instance.
One is a test database with a smaller number of records and total size of 500GB.
Other one is production database with a size of 2.2 TB.
Both database are similar with same database objects.
The server is enabled for parallel execution with MAXDOP = 8 and Cost Threshold for Parallelism = 20. There are 40 logical CPUs on SQL Server 2016 Standard edition.

When I run a somewhat complex query that has multiple joins and views, on the production database the query executes fast in parallel mode.

When I run the same query in the test database (on the same instance) the query runs in serial mode. Data in test database is static too.

I do not understand why it is not running in parallel mode.

From my research, since the test database is small compared to the production database, maybe the cost of the query in the test db does not exceed 20 (cost threshold for parallelism). How can I calculate the cost of the query to confirm?

I made the query run in parallel by removing a scalar function and replacing it with an inline function, but the same query runs differently in two identical databases, that is why I am confused. I have gone through the post which explains why SQL Server does not engage parallelism.

Estimated Subtree Cost is different in both. The one running in serial has a low value of 44. The parallel one has a higher value of 312.

Since the parallel one has to deal with more data, its individual node subtree cost is higher and adds up to 312. So maybe cost is the real issue?

My cost threshold for parallelism is 20 and the one running in serial has an estimated subtree cost of 44 (> 20). So shouldn't it run in parallel?


texts:
I know that there have been many opinions/sides concerning NULL values in a database.
I have not understood though what is the best practice for this.

I.e. if I have a relational table with an optional attribute i.e. it can take NULL value -so we can end up with a table with many NULLs on that column- is it best to make the attribute a new relational table?

What is the best approach on this?


texts:
I'm using MySQL 5.7.10
I'm checking a new query for an audit report.
I'll execute it in a simple background Unix process, whick invoke mysql from the console.
To check the query, I use a worksheet in HeidiSQL.

The table is:

CREATE TABLE `services` (
`assigned_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`service_id` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
...
`audit_insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
...
INDEX `idx_audit_insert` (`audit_insert`),
...

);

The simple worksheet is:

SET @numberOfMonths:=6;
SET @today:=CURRENT_TIMESTAMP();
SET @todaySubstractnumberOfMonths=TIMESTAMP( date_sub(@today, interval @numberOfMonths MONTH) );

EXPLAIN SELECT service_id from services where audit_insert between @todaySubstractnumberOfMonths and @today;

The explain output for that query is:
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,services,[all partitions],ALL,idx_audit_insert,,,,47319735,21.05,Using where

So, index 'idx_audit_insert' is not used.

If I change the query to:

EXPLAIN SELECT service_id where audit_insert between '2020-01-01 00:00:00' and '2020-03-10 23:59:59';

The output is:
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,tdom_transitos,[all partitions],range,idx_audit_insert,idx_audit_insert,4,,4257192,100.00,Using index condition

Now, the index is used and the rows value is dramatically reduced.

So, my questions are:

How can I force the variables to be timestamp? Is there any wrong in my worksheet?

or maybe

How can I use the index (trying to avoid hints like USE INDEX, FORCE INDEX...)?

(Excuse me if this question is in stackoverflow too. But this related web is more properly for DB questions)
Thanks a lot.


texts:
I'm converting a procedure to use a Linked Server, because the databases that it references have now been migrated to separate Clusters. I've almost finished this work, but I'm not stuck on the following part;

declare @CustomerId int

set @CustomerId = 2

exec (N'
USE [DatabaseName]

SELECT  @DiskUsageDataObjectsKB = SUM(alloUni.used_pages) * 8
FROM        sys.tables sysTab
            INNER JOIN sys.indexes ind
            ON sysTab.OBJECT_ID = ind.OBJECT_ID  and ind.Index_ID<=1
            INNER JOIN sys.partitions parti
            ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id
            INNER JOIN sys.allocation_units alloUni
            ON parti.partition_id = alloUni.container_id
WHERE       SCHEMA_NAME(sysTab.SCHEMA_ID) = CAST('+ @CustomerId +' AS NVARCHAR(MAX))') AT [LinkedServer]

select @DiskUsageDataObjectsKB

As you can see, I'm passing the CustomerId variable to this using Dynamic SQL, but I'm not sure how I get the @DiskUsageDataObjectsKB value back to the procedure, so that It can be added to our reporting database.

Would anyone have any suggestions to how this can be done, please?

Many thanks!
Tom


texts: Inherited Permissions: SQL Server 2005 vs 2016

texts:
Question:

I have a SQL Server 2005 server and a SQL Server 2016 server.
On each server is a database (A) with a stored procedure that selects data from a view in Database (A)
The view in database (A) is referencing a table in Database (B) on THE SAME SERVER
I am using a logon that has ONLY got execute permission on the stored procedure on database (A).
This setup is the same for BOTH servers.
On the 2005 server, I can run the SP as the described logon and retrieve data via the view in database (A) from the table in database (B) - as I would expect.
On the 2016 server, I get an error because from the database (A) I cannot select, via the SP and the view, data from the table in database (B).

Both servers are running SQL Server Standard Edition - (though one is 2005 and one is 2016).

To summarize:

SQL Server 2005

DATABASE A = VIEW = TABLE in DATABASE B
DATABASE A = STORED PROCEDURE = select from VIEW
LOGON = ONLY EXECUTE ON STORED PROCEDURE
RESULT = SUCCESS

SQL Server 2016

SET UP APPEARS IDENTICAL
RESULT = FAIL

Why is this?
What is different between these two versions of SQL with regards to inheritance of permissions?
Is it something very obvious that I am missing?

Any pointers would be very helpful!


texts: How can I separate some collections to another server?

texts:
If my server disk is fulled, I want to move some collections to another server.

Is it possible?

Is there another way from sharded cluster implementation?


texts: Insert RECORD into a table in a trigger function

texts:
I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like?
The following attempts were unsuccessful:

EXECUTE 'INSERT INTO my_table VALUES ' || NEW;
EXECUTE 'INSERT INTO my_table VALUES ' || NEW.*;
EXECUTE 'INSERT INTO my_table SELECT * FROM ' || NEW;


texts: How does security context propagate between schemas within a database?

texts:
We have a SQL Server 2008 database that contains two schema's.  The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership.

I have created a user ID and have granted it access to only SQL Views created in both of these schema's but not the underlying tables.  In the example below I've created two views, one in each schema, that select data from one table in one of the schema's.

If I login as the schema owner (either testdata or testcntrl) it works regardless of the schema I execute the view from.

If I execute the View as my new user in the schema that the same table exists in (in the example testdata) the select is successful, but if I try from the other schema (testctrl) the select fails giving me the following permission error: The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'..

How does security propagate between schema's within a single database?  Why is this successful from one schema but not the other?

Here is a rough example of what I am trying to do, please let me know if I can explain this better, as for Im not remotely close to a dba.

USE [DEV_DATABASE]

-- Create tables and insert dummy data
CREATE TABLE [DEV_DATABASE].[TESTDATA].[UserData]
    (
        COL1 CHAR(10),
        COL2 CHAR(10),
        COL3 CHAR(10),
        COL4 CHAR(10),
        COL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('1','2','3','4',5');
 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('11','22','33','44',55');

CREATE TABLE [DEV_DATABASE].[TESTCTRL].[ControlData]
 (
        CTRL1 CHAR(10),
        CTRL2 CHAR(10),
        CTRL3 CHAR(10),
        CTRL4 CHAR(10),
        CTRL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('a','b','c','d',e');
 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('aa','bb','cc','dd',ee');

-- Create views
CREATE VIEW [TESTDATA].[DATA_TEST_VIEW] AS
SELECT
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]

CREATE VIEW [TESTCTRL].[CTRL_TEST_VIEW] AS
SELECT
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]

-- permissions
DENY SELECT ON [TESTDATA].[UserData] to JOEUSER;
GRANT SELECT ON [TESTDATA].[DATA_TEST_VIEW] to JOEUSER;
GRANT SELECT ON [TESTCRL].[CTRL_TEST_VIEW]  to JOEUSER;

EXECUTE AS USER = 'JOEUSER';
-- tests
SELECT * FROM [TESTDATA].[UserData];
-- fails as expected

SELECT * FROM  [TESTDATA].[DATA_TEST_VIEW];
-- passes as expected

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
-- fails, and should pass?

REVERT;

Error message received:

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.


texts:
I have constructed the following syntax but I keep getting an error of:

  Msg 156, Level 15, State 1, Line 26
  Incorrect syntax near the keyword 'INSERT'.

What do I need to change in my syntax so this executes appropriately?

    SET @SQL = N';WITH CTE AS
        (SELECT *
         FROM OPENQUERY(XXX.XXX.X.X,
        ''Select
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempid = jb.arempid
               WHERE xr.hiredate BETWEEN ('+ CHAR(39) + CHAR(39)
               + CAST(@startdate AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
               AND (' + CHAR(39) + CHAR(39) + CAST(@enddate AS NVARCHAR(500)) + CHAR(39)
            + CHAR(39) + N') GROUP BY cm.empName'')
            INSERT INTO Data (EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE';

            EXEC sp_executesql @sql;

This is actual syntax:

    ;WITH CTE AS
                    (SELECT *
                     FROM OPENQUERY(XXX.XXX.X.X, ,
        ''Select
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempID = jb.arempID
               WHERE xr.hiredate BETWEEN BETWEEN (''01/01/2017'')
               AND (''01/31/2017'') GROUP BY cm.empname')
            INSERT INTO Data(EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE;

Insert is occurring on the local server. The table named Data exists on the same server I am executing the syntax on.


texts:
I have a database that is full of doping results for athletes.

+-----------+------+----------+
| athleteId | test | outcome  |
+-----------+------+----------+
|    100011 |    1 | Pending  |
|    100012 |    1 | Positive |
|    100012 |    2 | Negative |
|    100013 |    2 | Positive |
|    100013 |    3 | Negative |
|    100014 |    2 | Positive |
|    100022 |    1 | Negative |
|    100022 |    2 | Positive |
|    100022 |    3 | Positive |
|    100023 |    2 | Negative |
|    100030 |    1 | Negative |
|    100031 |    2 | Disputed |
|    100033 |    4 | Pending  |
|    100036 |    5 | Positive |
|    100037 |    5 | Positive |
|    100038 |    5 | Positive |
|    100051 |    1 | Pending  |
|    100054 |    1 | Positive |
+-----------+------+----------+

What I'm needing to return is the athleteId where a test returned a positive, and then another test returned a negative.
That narrows it down to athleteIds 100012, 100013, 100022, but I'm not sure how to structure a query to return this.

Any help is greatly appreciated!


texts:
I want to store stock data from the FTSE 100 every 10 seconds but am confused how I should set up my tables.

I need to save the price, increase and % increase as well as some way of identifying which stock is which.

Should I have one massive table with 300+ columns. (3 for each stock)?

Separate tables per stock or something else?

I will be querying the data using PHP and displaying it on a website.


texts: MySQL restore dump takes too long to complete, how DBAs deal/replicate large databases?

texts:
Due to coronavirus my company is forced to scale down and cut costs. Long story short, I went the route of least disruption I set the original database server (that's going to be turned off for good in 2 weeks) as a MASTER replicator, locked all tables for reading, dumped the data which resulted in a 34GB .sql file and unlocked the tables. Afterwards I copied the file over to the other server and ran mysql import through mysql-client using the following code

cat default-start-import.sql newdatabase.sql default-end-import.sql | mysql -uroot -p newdatabase

With the contents of default-start-import.sql and default-end-import.sql being the following

default-start-import.sql

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

default-end-import.sql

COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

The problem is, this 34GB dump has been running for 2 days and by looking at the processlist and the amount of tables I have, I'd say its at about 50% import. And this supposedly slave server is not doing anything else at the moment. It's idling.

The CPU usage my MySQL is at 9% at worst and memory usage is at 44% at worst. Looking at the queries it takes about 1-2 seconds to insert a row. I have tables with millions of rows. If each row takes a second this import will take years to complete. I've set vm.swappiness to 10 in sysctl to reduce hard drive swapping and the server runs with steady 2.4 load, which is largely - fine. I wouldn't say it's overburdened.

I've disabled slow query log

I've set innodb_flush_method = O_DIRECT I've given innodb a buffer of 20 gigabytes

Is there anything else I could do to speed up the process. This feels like an insane amount of timewaste. It's just 34GB, I'm sure there are organizations that bring up slave servers with hundreds of gigabytes of databases in no time and here I am struggling on 30gb database. How?!?


texts: how do i create Periods and weeks (think business reports) using 2 date ranges

texts:
Basically Fromdate is every Sunday and ToDate is every saturday, i need to programatically create the PERIOD column on the fly through a stored procedure where period is the month and week is the week of that month,

--but there shouldnt be 5 weeks in a period.
FromDate    ToDate      PERIOD
2015-11-29  2015-12-05  P12-WK1 <-- see how it goes to the next period
2015-11-22  2015-11-28  P11-WK4
2015-11-15  2015-11-21  P11-WK3
2015-11-08  2015-11-14  P11-WK2
2015-11-01  2015-11-07  P11-WK1

I tried using the code below but my results are slightly off,

for example

2016-01-17  2016-01-23  P1-WK4 should be

2016-01-17  2016-01-23  P1-WK3 (notice the WK)

CASE datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate) + 1
WHEN 5 THEN 'P' + CAST(DATEPART(M, dbo.GP_MASTER.FromDate + 1)AS varchar(20)) + '-WK'+ CAST(datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate)  AS varchar(20))
WHEN 6 THEN 'P' + CAST(DATEPART(M, dbo.GP_MASTER.FromDate + 2)AS varchar(20)) + '-WK'+ CAST(datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate) - 4 AS varchar(20))
ELSE        'P' + CAST(DATEPART(M, dbo.GP_MASTER.FromDate)AS varchar(20)) + '-WK'+ CAST(datediff(week, dateadd(month, datediff(month, 0, dbo.GP_MASTER.FromDate), 0), dbo.GP_MASTER.FromDate) + 1 AS varchar(20))
END AS PERIOD

2016-02-21  2016-02-27  P2-WK4
2016-02-14  2016-02-20  P2-WK3
2016-02-07  2016-02-13  P2-WK2
2016-01-31  2016-02-06  P2-WK1
2016-01-24  2016-01-30  P1-WK4
2016-01-17  2016-01-23  P1-WK4
2016-01-10  2016-01-16  P1-WK3
2016-01-03  2016-01-09  P1-WK2

This is my desired results

FromDate    ToDate      PERIOD (Period# - Week#)
2016-02-21  2016-02-27  P2-WK4
2016-02-14  2016-02-20  P2-WK3
2016-02-07  2016-02-13  P2-WK2
2016-01-31  2016-02-06  P2-WK1
2016-01-24  2016-01-30  P1-WK4
2016-01-17  2016-01-23  P1-WK3
2016-01-10  2016-01-16  P1-WK2
2016-01-03  2016-01-09  P1-WK1


texts: Durability of storage in second generation google cloud sql

texts:
How durable should I expect data for a Google Cloud SQL instance to be? Just a single instance, with the high-availability option disabled.

AWS explicitly mention that Amazon Aurora data is stored in six copies over multiple availability zones. Is any such information available for Google Cloud SQL?


texts: Configured values different to Running values, even after full restart

texts:
I am trying to change the FILESTREAM Access Level property on a SQL Server Express database. The Configured values section of the Advanced tab of my Server Properties dialogue box looks like so:

The Running values section looks like this:

This is before and after a full restart of the computer. Am I right in thinking that SQL Server Express just doesn't support this option, or could there be another reason this is happening?

Thanks


texts:
I have a big Access database, with aobout 150 Tables/Queries/Linked txt files/Macros/Modules.

I was working today, and then from out of nowhere the database got corrupted. I googled, and forund that exporting all the Objects into another database solves the Problem. I did that, and it worked on Objects and Queries, but Macros and Modules i can't Export... I alwasy get an error message: "id" is not an index in this table. And then Nothing happens. I also cant open the modules or the Visual Basic Editor.
I tried the compact and repair tool, but It gives me the same error message multiple times.
I tried to open the module in design mode, but get this error message "The module Name 'XY' is misspellen or refers to a module that does't exist.
Everything I tried doesn't seem to work… The Macros are not so important, but the modules where a lot of work to put together…

Does anyone have an idea how I can repair These modules?
Thank you so much for your help!


texts: Do I need a "downtime" to update a stored procedure? (Due to "definition changed" error)

texts:
I always thought that updating a stored procedure was something I could do while users were actively using the system.

But I am currently testing out an update to a stored procedure and while a long running call to the sproc was being executed (it was 4+ min into the call), I updated the sproc.  (I was planning to try a different query plan in a seperate window.)

When I went back to the long running one, I had this error:

The definition of object 'MySprocName' has changed since it was compiled.

This error seems to say that any sproc that is being actively executed will fail if the definition of that sproc is updated. (I thought that once a sproc started running, it would use that plan for the rest of the run despite changes to the definition.)

Is this true?  Do I need to have downtimes to update definition of a sproc?


texts:
Is there a method or syntax that would allow a table that just has a rowversion/timestamp column to be filled with data?

CREATE TABLE [dbo].[TimeStamp_Test]([row_version] [timestamp] NOT NULL)


texts: How to suppress activation procedure output from the error log?

texts:
I found so many below informational messages (bulk messages with no any error) are logged in the SQL Server error log.

  The activated proc 'x' running on queue 'y' output the following:  'z'

I want to stop this logging. Is there any trace flag that can help?


texts: Searching large dataset, best way to speed this up

texts:
i'm querying a table full of vehicles, however each one of these vehicles MUST have another row within another table. This query is currently taking way too long!
Do I need to index the table, or is there another way I can do the query to improve this?
SELECT *
FROM vehicles v
WHERE v.group = 0
AND EXISTS (
    SELECT count(id) FROM quotes quote
    WHERE quote.vehicle_id = v.id
    AND quote.term = 60
    AND quote.annual_mileage = 8000
    AND quote.deposit = 250
    AND quote.credit_rating = "Excellent"
) IS NOT NULL

vehicles table:
CREATE TABLE `vehicles` (
    `id` INT(11) NOT NULL DEFAULT '0',
    `group_id` INT(11) NOT NULL DEFAULT '0',
    `title` VARCHAR(8) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci'
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

quotes table:
CREATE TABLE `quotes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vehicle_id` INT(11) NOT NULL,
    `credit_rating` VARCHAR(20) NOT NULL COLLATE 'latin1_swedish_ci',
    `deposit` DECIMAL(7,2) NOT NULL,
    `term` TINYINT(2) NOT NULL,
    `annual_mileage` INT(11) NOT NULL
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `vehicle_id` (`vehicle_id`) USING BTREE,
    INDEX `covering_ndx1` (`term`, `annual_mileage`, `deposit`, `credit_rating`, `vehicle_id`) USING BTREE,
    CONSTRAINT `FK_quotes_vehicles` FOREIGN KEY (`vehicle_id`) REFERENCES `database`.`vehicles` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

Edit:
Updated the create SQL above to reflex the changes I have made. This reduces my query from 0.516 to 0.032


texts: Autocomplete too slow: possible optimizations?

texts:
The autocomplete search functionality of my website searches a varchar field which contains the model number of sale items.

This field can contains strings varying from 1 to 75 characters and the table contains 400 000 rows. I have come up with a query that searchs only from the start of the string and that takes about 150-250ms to execute which is acceptable but now my manager wants the query to search for any substrings which makes the query 3-10 times slower (around 1000-2000ms).

I have built a JS fiddle to give you an example of what the data looks like and what the two query are like.

http://sqlfiddle.com/#!6/9efa3/2/0

There already are a few indexes on the table. What would be the best practice to speed-up this auto-complete search field ?
(database version is SQLSERVER 2008R2)

Here is a brief example of the data I'm dealing with :

CREATE TABLE [Products](
    [productid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [model] [nvarchar](75) NOT NULL
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [productid] ASC
));

insert into products values ('UMPX1AA0011 danish e-315 woot');
insert into products values ('P27y719VC');
insert into products values ('VG2y439m-LED');
insert into products values ('UMUyX165AAB01');
insert into products values ('U28y79VF');
insert into products values ('U52417HJ');
insert into products values ('VA25746M-LED WITH FLYING CORNERS');
insert into products values ('S19F350HNN 1pc california storage');
insert into products values ('VA211917A');
insert into products values ('PM2500X2');
insert into products values ('E22470SWHE');
insert into products values ('V22465WLYDP');
insert into products values ('I129LMH1HKC');
insert into products values ('OM5EN X 35 the new version');
insert into products values ('DLS3060WDB');
insert into products values ('PVW');
insert into products values ('LI23721S');
insert into products values ('V173516LBM');
insert into products values ('VX2376-SMHD-A');
insert into products values ('GUM5FX1AA1001');
insert into products values ('GPM300X11');
insert into products values ('GUM-WH6AA002');
insert into products values ('2435V5LSB');
insert into products values ('P2418HZ');
insert into products values ('Stylish sectional one of a kind y-5151');

and these are the two queries I'm comparing

--runs acceptably fast, about 100-250ms
select * from products where model like 'y-5151'+'%';
--takes too long, around 1000-2500ms
select * from products where model like '%' + 'y-5151' +'%'


texts: How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

texts:
I have a function that includes:

SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS

This is useful, for example, to remove accents in French; for example:

UPPER(CAST('Éléctricité' AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS)

gives ELECTRICITE.

But using COLLATE makes the function non-deterministic and therefore I cannot use it as a computed persisted value in a column.

Q1. Is there another (quick and easy) way to remove accents like this, with a deterministic function?

Q2. (Bonus Question) The reason I do this computed persisted column is 1)to search 2)to detect duplicates. For example the user may enter the customer's last name as either 'Gagne' or 'Gagné' or 'GAGNE' or 'GAGNÉ' and the app will find it using the persisted computed column (and the update/insert will balk at the duplicate). Is there a better way to do this?

EDIT: Using SQL Server 2012 and SQL-Azure.


texts:
I use RazorSQL as an interface to my PostgreSQL database server, which contains three databases.

If I use pgAdmin, I can list all three databases (namely, mimic, postgres, and pubmed):

However, with RazorSQL, it seems that I have to specify the database when connecting:

As a result I only see the that the database I specified, e.g. mimic:

How can I list all the databases of a PostgreSQL database server with RazorSQL? I would like to graphically navigate between databases.

I use RazorSQL 6.3.14 with Windows 7 SP1 x64 Ultimate.


texts: Will an hourly T-Log backup in a Maintenance plan write a file if there are no changes to a DB?

texts: I have an hourly maintenance plan the runs a T-Log backup. Some of my databases may have every hour represented in the file system, while others have some hours "missing" or even none at all. Is it possible that the maintenance plan won't write a T-Log backup if there have been no transactions? If this is not the case, do I have a problem that doesn't seem to be called out in the error log? I'm concerned with some DBs that are marked as full recovery.


texts: MySQL InnoDB issue not using indexes correctly [Percona 5.6]

texts:
I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good

My old server is a bit less powerful, and was running MySQL 5.1 still. So this was quite an upgrade. But I'm having some issues with InnoDB, it is not using the indexes correctly on some tables it seems. Where on my old machine the same queries were running fine.

Both servers have the same database. I did a mysqldump on the old machine and imported it onto the new Percona 5.6 server. Indexes stayed the same. Both servers use the same my.cnf config settings.

Table items has indexes on: item_id, item_format, item_private and contains about 40 million rows. Table formats has index on: format_id and contains about 250 rows.

SELECT
i.item_name, i.item_key, i.item_date, f.format_long
FROM
items i, formats f
WHERE
i.item_format = f.format_id
AND
i.item_private = 0
ORDER BY
i.item_id DESC LIMIT 8

On my old server this query takes about 0.0003 seconds. On the new server it takes over 100 seconds.

Query with EXPLAIN on OLD server.

+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table |  type  | possible_keys |   key   | key_len |         ref          | rows |    Extra    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | i     | index  | item_format   | PRIMARY |       4 | NULL                 |    8 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY |       4 | dbname.i.item_format |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+

Query with EXPLAIN on NEW [problem] server.

+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys |     key     | key_len |        ref         | rows |              Extra              |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | f     | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  219 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | item_format   | item_format | 4       | dbname.f.format_id | 3026 | Using where                     |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+

You can see that it's using temporary and filesort. This seems to be the reason for the slowness.

Any idea how I could resolve this issue?


texts: Should I care about physical columns in SQL Server?

texts:
So far, I only ever looked at the logical columns of my table but never at the physical columns. My motto was : "let SQL Server deal with it".

After reading that article, I wonder if I should not pay some attention to it.

  By now, our 5 column table has actually 15 columns in the physical
  storage format, 10 dropped columns and 5 usable columns. The table
  uses 412 bytes of fixed space for the 3 fixed length columns that have
  a total length of only 112 bytes. The variable length columns that now
  store the first_name and last_name are stored in the record after
  these 412 reserved bytes for fixed columns that are now dropped. Since
  the records always consume all the reserved fixed size, this is quite
  wasteful. How do we reclaim it? Rebuild the table:

Physical columns seems to take useless space but can it also affect performance?


texts:
I'm using Postgres 9.5 on Amazon RDS (2vCPU, 8 GB RAM).
I use pganalyze to monitor my performance.
I have around 200K records in the database.

In my Dashboard I see the following queries are taking 28 and 11 seconds in average to execute:

UPDATE calls SET ... WHERE calls.uuid = ?   telephonist 28035.41    0.01    100%    0.03%

UPDATE calls SET sip_error = ? WHERE calls.uuid = ? telephonist 11629.89    0.44    100%    0.69%

I already tried VACUUM, found and clean 7,670 dead rows.
Any ideas how to improve UPDATE performance? This is the query:

UPDATE calls SET X=Y WHERE calls.uuid = 'Z'

How can I improve the query above? Can I add another field? Example:

UPDATE calls SET X=Y WHERE calls.uuid = 'Z' AND calls.campaign = 'W'

The column uuid is not indexed.
https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm suggests that indexes are not recommended for UPDATE operations.

CREATE TABLE public.calls (
    id int4 NOT NULL DEFAULT nextval('calls_id_seq'::regclass),
    callsid varchar(128),
    call_start timestamp(6) NOT NULL,
    call_end timestamp(6) NULL,
    result int4 DEFAULT 0,
    destination varchar(256),
    campaign varchar(128),
    request_data varchar(4096),
    uuid varchar(128) NOT NULL,
    status varchar(64),
    duration int4,
    recording_file varchar(256),
    recording_url varchar(256),
    recording_duration int4,
    recording_text varchar(4096),
    recording_download bool DEFAULT false,
    description varchar(4096),
    analysis varchar(4096),
    is_fax bool DEFAULT false,
    is_test bool,
    hangup_cause varchar(128),
    media_detected bool DEFAULT false,
    sip_callid varchar(256),
    hangup_cause_override varchar(256),
    is_blacklisted bool DEFAULT false,
    sip_error varchar(256),
    hangup_cause_report varchar(128),
    summary varchar(1024)
);

EXPLAIN ANALYZE
    SELECT * FROM calls
    WHERE calls.uuid='e2ce9eb4-v1lp-p14u-7kkk-lruy-e2ceaae46d';

Seq Scan on calls  (cost=0.00..16716.25 rows=1 width=3301)
                   (actual time=81.637..81.637 rows=0 loops=1)
  Filter: ((uuid)::text = 'e2ce9eb4-v1lp-p14u-7kkk-lruy-e2ceaae46d'::text)
  Rows Removed by Filter: 99970
Planning time: 0.482 ms
Execution time: 81.683 ms


texts:
I wonder if anyone would be able to help by giving me a few scenarios/tasks to complete on SQL Server 2012/16 please?

I am learning SQL Server to move from a desktop role to a junior DBA. I have my own lab at home, but only has adventureworks running on a single SQL instance. I can write basic/intermediate queries with inner joins, aggregates etc. What I'm looking for is some basic DBA tasks to practice with. Unfortunately, the interwebs is full of tutorials on how to write SQL, but I can't find much on then putting it into real life use.

Any help would be GREATLY appreciated!

Happy New Year!!