Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)? I experienced very strange problem today with TSQL function.

The function has several parameters (int and bit). One of the (int) is set with value NULL.

When calling a function with NULL inside the params function return ERROR (the error is ok, because I try to convert '%%' to int value). When the function is called with param @p1 which value is NULL the function is working.

Can someone explain to me why it works like that?

We are using:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

MY CODE IS:
The code that causes error:

((@p IS NOT NULL) AND (CONS.IDp = @p)) OR
((@p IS NULL) AND (UFCFD.IDUser IS NOT NULL) AND
 (CONS.IDp = CAST(UFCFD.Value1 AS INT))

REMARK: I edit "causing error" part because my colleague put a protection check. I remove that part to show the original state of the function (The source was: (CONS.IDp = CASE WHEN ISNUMERIC(UFCFD.Value1) = 1 THEN CAST(UFCFD.Value1 AS INT) ELSE NULL END))

The call that causes error:

SELECT * FROM dbo.myFunc(NULL);

The call that works:

DECLARE @p INT = NULL;
SELECT * FROM dbo.myFunc(@p);

P.S. If the question is unclear please let me know via comment.

EDIT2:

This is the full text of the function:

CREATE FUNCTION dbo.myFunc
(
    @IDUser INT,
    @p INT,
    @IDGrid INT,
    @IsWithLead BIT,
    @IDLeadValueResource INT,
    @IDLanguage INT,
    @IsAllPresent BIT,
    @IDAllValueResource INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        -99999999 AS ID,
        dbo.funs_GetResourceText(@IDLanguage,@IDAllValueResource) AS [Text],
        -99999999 AS Sort
    WHERE (@IsAllPresent = 1)

    UNION ALL

    SELECT
        0 AS ID,
        dbo.funs_GetResourceText(@IDLanguage,@IDLeadValueResource) AS [Text],
        0 AS Sort
    WHERE (@IsWithLead = 1)

    UNION ALL

    SELECT
        CONS.IDConsumerType AS ID,
        CASE
            WHEN @IDLanguage = 40001 THEN CONS.ConsumerType_Name_en
            WHEN @IDLanguage = 40002 THEN CONS.ConsumerType_Name_it
            WHEN @IDLanguage = 40003 THEN CONS.ConsumerType_Name_de
            WHEN @IDLanguage = 40004 THEN CONS.ConsumerType_Name_fr
            ELSE CONS.ConsumerType_Name_en END AS [Text],
        ROW_NUMBER() OVER(ORDER BY CONS.SegmentOrder ASC) AS Sort
    FROM
        dbo.V_ConsumerTypes AS CONS
        LEFT JOIN dbo.Grids GRD ON (GRD.ID = @IDGrid)
        LEFT JOIN dbo.CONFieldCollection_FieldDefinition FCFD ON
            (FCFD.IDFieldCollection = GRD.IDGridFieldCollection) AND
            (FCFD.FieldName LIKE '%IDp%')
        LEFT JOIN dbo.CONUser_FieldCollection_FieldDefinition UFCFD ON
            (UFCFD.IDFieldCollection = GRD.IDGridFieldCollection) AND
            (UFCFD.IDUser = @IDUser) AND
            (UFCFD.IDFieldCollections_FieldDefinitions = FCFD.ID)
    WHERE
        ((@p IS NOT NULL) AND (CONS.IDp = @p)) OR
        ((@p IS NULL) AND (UFCFD.IDUser IS NOT NULL) AND
         (CONS.IDp = CAST(UFCFD.Value1 AS INT))
)

The call that works is:

DECLARE @IDUser INT = -100;
DECLARE @p INT = NULL;
DECLARE @IDGrid INT = 17;
DECLARE @IsWithLead BIT = 0;
DECLARE @IDLeadValueResource INT = 0;
DECLARE @IDLanguage INT = 40002;
DECLARE @IsAllPresent BIT = 1
DECLARE @IDAllValueResource INT = -177;

DECLARE @dt DATETIME = GetDate();
SELECT * FROM dbo.fun_ClROME_MdFillComboConsumerTypes(@IDUser, @p, @IDGrid, @IsWithLead, @IDLeadValueResource,
    @IDLanguage, @IsAllPresent, @IDAllValueResource)
ORDER BY Sort

If we use NULL instead of @p then I receive error, because I try to convert '%%' to int - this is correct.

The problem is that obviously function behaves differenly which is a big issue.

I think you're trying to solve for the wrong problem. As I suggested above, I think you're getting different results simply because you currently have different plans (one for the literal and one for the variable). The plan you're currently getting that causes an error is attempting the cast before the rows are filtered out, but both methods could cause an error if the plan that currently "works fine" gets recompiled.

What you should be doing is using a CASE expression to ensure that only numeric values that can be cast to an INT are actually attempted to be converted. You need to control this explicitly because you can't always rely on SQL Server filtering rows before attempting calculations.

In 2012 and up, you can simply replace this:

(CONS.IDp = CAST(UFCFD.Value1 AS INT))

With this:

(CONS.IDp = TRY_CONVERT(INT, UFCFD.Value1))

If you also have to support older versions, you can do this (since we know ISNUMERIC() is not enough):

(CONS.IDp = CASE
  WHEN UFCFD.Value1 NOT LIKE '%[^0-9]%'
  AND
  (
    LEN(UFCFD.Value1 < 11
    OR
    (
      LEN(UFCFD.Value1) = 11
      AND LEFT(UFCFD.Value1,1) IN ('0','1','2')
    )
  ) THEN CONVERT(INT, UFCFD.Value1) END)

A CASE expression is pretty reliable for forcing SQL Server to evaluate expressions in a particular order, but do note that there are exceptions.


System test Database user management We are in development stage and to be able to give the developers an integration test environment, we need to give them access to the database, they can share and test their code in the system against.

The application is written in .net (MVC 4) and we are using SQL server 2008 r2 for database. We have restored the database they need to conect on the server, but I'm not quite sure what is the best approach in user management for this case?

Do we need to create an account for each developers, or it would be easier to create a generic account and give them this user/password to use? What would be the advantages and disadvantages of each option?

For example, If more than one person uses an account, would it be possible to differentiate who did what?

Also if we want to go with creating a generic account, is it better to create a SQL authentication account or windows authentic one?

I would recommend the following approach:

a) Each developer has their own database that they can work with, try out different things,  destroy and restore at will independent of the other team members.

b) Have a shared database instance for integration testing so that the whole team can see the shared progress at any one time

c) Use windows authentication to speed up the connection process, and the devs do not have to remember new passwords, and easier to track and audit

Of course all this depends on the size of the database and what resources are available on the test server.


Figuring out the physical size of a group of tables in SQL server I have a group of about 30 tables, and I want to know the physical size on disk of all of these tables (plus indexes).

Is there an easier way of doing this than through the GUI in SQL server 2008 R2?

If you are looking to get the size information for all tables in the database you can use this query:

SELECT O.type_desc,
       IndexSize.obj_name,
       I.name AS index_name,
       IndexSize.reserved_MB,
       IndexSize.used_MB,
       IndexSize.row_count,
       IndexSize.object_id,
       IndexSize.index_id
  FROM (
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(DDPS.object_id))+'.'+QUOTENAME(OBJECT_NAME(DDPS.object_id)) obj_name,
           SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
           SUM(DDPS.used_page_count)/128.0 AS used_MB,
           SUM(row_count) AS row_count,
           DDPS.object_id,
           DDPS.index_id
      FROM sys.dm_db_partition_stats AS DDPS
     GROUP BY DDPS.object_id,DDPS.index_id
   )IndexSize
   JOIN sys.objects AS O
     ON IndexSize.object_id = O.object_id
   JOIN sys.indexes AS I
     ON IndexSize.object_id = I.object_id
    AND IndexSize.index_id = I.index_id;

It returns the reserved megabytes and the used megabytes as well as the row count for every index in the database. The first column tells you the object type. This includes system_tables as well as indexed views. If you want a subset, filter on that column.

If you do not need the per-index detail you can use this query instead:

SELECT O.type_desc,
       ObjectSize.obj_name,
       ObjectSize.reserved_MB,
       ObjectSize.used_MB,
       ObjectSize.row_count
  FROM(
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(IndexSize.object_id))+'.'+QUOTENAME(OBJECT_NAME(IndexSize.object_id)) obj_name,
           SUM(IndexSize.reserved_MB) AS reserved_MB,
           SUM(IndexSize.used_MB) AS used_MB,
           MAX(IndexSize.row_count) AS row_count,
           IndexSize.object_id
      FROM(
        SELECT SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
               SUM(DDPS.used_page_count)/128.0 AS used_MB,
               SUM(row_count) AS row_count,
               DDPS.object_id
          FROM sys.dm_db_partition_stats AS DDPS
         GROUP BY DDPS.object_id,DDPS.index_id
      )IndexSize
     GROUP BY IndexSize.object_id
  )ObjectSize
  JOIN sys.objects AS O
    ON ObjectSize.object_id = O.object_id

It gives the same information, but on an object basis. Again, filter on the object type if you are for example not interested in system_tables.

If you are just interested in normal tables, you can get the information in SSMS too by using the "Object Explorer Details" tab:

You can open it by selecting the table folder in the database in Object Explorer and then pressing F7.

You might have to add the size columns. For that just right-click on the column list and select the columns you want to see:


COUNT and SUM at the same time after removing duplicate rows I'm using PostgreSQL and I can't figure out a query for doing a COUNT and a SUM of two different columns. Basically I'm looking for something like a SUM(hits DISTINCT record_id)

The table:

status      record_id      hits
     0            123         1
     0            123         1
     0            124         1
     0            125         1
     0            126         0
     1            123         0
     1            124         0
     1            125         1
     1            125         1

The desired result:

status   unique_records_count  hits_sum
     0                      4         3
     1                      3         1

Doing a

SELECT status, COUNT(DISTINCT record_id), SUM(hits)
FROM table
GROUP BY status

returns

status   unique_records_count  hits_sum
     0                      4         4
     1                      3         2

Select A.status,count (A.record_id),sum(A.hits)
from
(Select distinct status,record_id,hits from YourTable) A
group by A.status


Inserting variables into another variable I've been working on the code below. I'm trying to insert results of a declared variable into a sql statement. I've highlighted the section below that I'm talking about. What am I doing wrong here? I've tried a few different things. Do I need to create a temp table and import from the temp table to the live table?

Thank you for you time and help.

DECLARE @counter INT
    ,@dimtablename NVARCHAR(50)
    ,@dimKey INT
    ,@processdatekey INT
    ,@DimCount INT
    ,@sqlstmt VARCHAR(8000)

WHILE @counter <= (
        SELECT MAX(DimKey)
        FROM dbo.audit_HostDimensionName(NOLOCK)
        )
BEGIN
    SET @dimtablename = (
            SELECT DimName
            FROM dbo.audit_HostDimensionName WITH (NOLOCK)
            WHERE DimKey = @counter
            );
    SET @dimkey = (
            SELECT DimKey
            FROM dbo.audit_JHostDimensionName WITH (NOLOCK)
            WHERE DimKey = @counter
            );
    SET @processdatekey = (
            SELECT cast(convert(VARCHAR, dateadd(d, - 1, getdate()), 112) AS INT) AS datekey
            );
    SET @DimCount = '(SELECT COUNT(*) from''' + @dimtablename + ''' with(nolock)) ';
    SET @sqlstmt = 'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (''' + @processdatekey + ',' + @dimkey + ',' + @DimCount + ')'''

    PRINT @sqlstmt

    EXEC (@sqlstmt)

    SET @counter = @counter + 1
END

From the comments it sounds like you already have this fixed but I thought I would put in my 2 cents anyway.

You have a handful of problems based on the code you have listed.

@DimCount is declared as an int where you are trying to use it as a string.
You are putting quotes around @dimtablename.  I understand what you are trying to do there but you should be using quotename() with the default []s instead.
In the line where you set @sqlstmt you have ''' at the end of the INSERT part of the string.  This is going to put in an extra ' that you don't want.  The same is true at the end of the string.
Last but not least if you are going to do it this way you will need to convert the int variables to varchar explicitly otherwise it tries to convert the whole thing to an int and fails.

Here is the corrected bit of code

DECLARE @counter INT = 1
    ,@dimtablename NVARCHAR(50) = 'dimtablename'
    ,@dimKey INT = 1
    ,@processdatekey INT = 1
    ,@DimCount VARCHAR(8000)
    ,@sqlstmt VARCHAR(8000)

SET @DimCount = '(SELECT COUNT(*) from ' + quotename(@dimtablename) + ' with(nolock)) ';
SET @sqlstmt = 'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (' + cast(@processdatekey as varchar(30)) + ',' + cast(@dimkey  as varchar(30)) + ',' + @DimCount + ')'

print @sqlstmt

All of that said you probably should look into using sp_executesql instead of just EXEC.  This will let you parameterize your queries.  Here is the same code using parameters.

DECLARE @counter INT = 1
    ,@dimtablename NVARCHAR(50) = 'dimtablename'
    ,@dimKey INT = 1
    ,@processdatekey INT = 1
    ,@DimCount NVARCHAR(4000)
    ,@sqlstmt NVARCHAR(4000)

    SET @DimCount = N'(SELECT COUNT(*) from ' + quotename(@dimtablename) + N' with(nolock)) ';
    SET @sqlstmt = N'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (@processdatekey,@dimkey,' + @DimCount + N')'

PRINT @sqlstmt

EXEC sp_executesql @sqlstmt, N'@dimKey INT, @Processdatekey INT', @dimKey, @processdatekey

This is particularly important when using character parameters to help avoid SQL Injection but it's also a good practice to get into in general.


You need to normalize your schema.  Your Employee_Owns_Asset table is trying to do too many things.  Consider this schema:

Note that your individual assets need to be normalized out into their own table.  This lets you separate the two semantically independent pieces of information:

What are the properties (brand, model, etc.) of each asset?
Who (if anyone!) owns this asset at a particular point in time?

This is a case of a requirement for fourth normal form (4NF), where it is important to isolate independent many-to-many relationships.

Note that if you don't want to track an asset's owner over time, then you could simplify the illustrated model and make ASSET.owning_employee_key a foreign key on the ASSET table and eliminate the EMPLOYEE_OWNS_ASSET table from the model.


DBCC on SSAS Cube fails DBCC on the Cube fails(SQL version 2014 SP2, 12.0.5000.0).
My XMLA query:

Error message:
Executing the query ...
The DBCC element at line 7, column 88 (namespace https://schemas.microsoft.com/analysisservices/2016/engine) cannot appear under Envelope/Body/Execute/Command.
Run complete

I can find anything wrong with a query.Tried 2003 and 2014. According to books online, it should just work:
https://docs.microsoft.com/en-us/sql/analysis-services/instances/database-consistency-checker-dbcc-for-analysis-services?view=sql-server-2017

Did you read the rest of the docs?

  Output when running DBCC against an earlier version of Analysis
  Services

  DBCC is only supported on databases running on a SQL Server 2017
  instance. Running the command on older systems will return this error.

Executing the query ...
The DBCC element at line 7, column 87 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command.
Execution complete

Updated
Here is a link that explains things a bit more in detail than the docs you're are referencing.

https://www.mssqltips.com/sqlservertip/4077/consistency-checks-for-sql-server-analysis-services/


Change Data Types large Live table I have a table in SQL Server 2008 R2 with close to a billion rows with Composit PK Column. I want to change the Datatype of Columns from Date to Datetime. Two times ALTER TABLE zzz ALTER COLUMN yyy works, but it's very slow. How can I speed the process up? I was thinking to copy the data to another table, drop, create, copy back and switching to simple recovery mode or somehow doing it with a cursor a 1000 rows a time but I'm not sure if those will actually lead to any improvement.

Summary

For large tables, it is nearly always preferable to move the data instead of altering the table. To get maximum speed, use one of the following patterns. Let us call the table you want to change S (for source).

Create an empty copy of S. Let us call that T
ALTER TABLE on T. This is very fast because T has no rows
If T has a cluster index, remove all other indexes but the cluster
If T is a heap, remove all indexes
Put the database in SIMPLE recovery mode (if you can, but be aware of the backup implications)
Use one of the two options provided in the Copy Data section below
Rebuild the indexes on T
Drop S and rename to T

Copy Data

In step 6 above, you will need to move data from S to T at highest possible speed. The following are two options:

Use INSERT ... SELECT
Use SSIS

Of these two, SSIS is the fastest. With INSERT ... SELECT you are restricted to a single thread. However, INSERT ... SELECT is much easier. See below for more details.

Option 1: INSERT ... SELECT

If T is a heap, do this:

INSERT INTO T WITH (TABLOCK) SELECT ... FROM S

This is minimally logged.

If T has a clustered index, do this:

DBCC TRACEON (610)
INSERT INTO T SELECT ... FROM S

This is also be minimally logged. The problem with this option is that you can only run one INSERT statement in parallel. Because SQL Server does not do parallel INSERT ... SELECT, this restricts you to around 40-80MB/sec - which is quite slow.

Option 2: Use SSIS

This option is a lot more trouble, but also faster. The idea is this:

Drop all indexes on T
Create an SSIS package that takes a parameter that selects a subset of the rows in S (using some filter that is supported by an index). The SSIS package then moves the rows to T using the TABLOCK hint in bulk mode
Execute multiple copies of the SSIS package, each operating on their own, distinct subset of S (so you don't duplicate rows in T)

This is MUCH faster because the packages can run in parallel. With this technique, I can typically move tables at GB/sec.


how can we merge sum and count? My result of the request of group by is :

ref       state       quantity     total
PO3253    assinged    300.0        323400
PO3253    done        300.0        323400

When I delete state from group by using:

SELECT   ref, sum(quantity),sum(price*quantity)  as total
FROM     myTable
GROUP BY ref

I get:

ref       quantity     total
PO3253    600.0        646800

I want the result to be:

ref       quantity     total
PO3253    300.0        323400

I want to do something like this operation :

SELECT   ref, sum(quantity)/count(*),sum(price* sum(quantity) / count(*))
FROM     myTable
GROUP BY ref

SELECT   ref, AVG(quantity), AVG(price*quantity)
FROM     myTable
GROUP BY ref


SQL Server Join/where processing order After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be as small as possible before joining (inner joins for this question) in order to make our queries that tiny bit faster.

Example, should this:

SELECT *
FROM   ( SELECT * FROM table1 WHERE col = @val ) t
INNER JOIN table2 ON col = col2

Be better/faster than:

SELECT *
FROM table1
INNER JOIN table2 ON col = col2
WHERE table1.col = @val

My theory is as follows (this might not be the correct implementation, I am trying to remember from a SQL Server 2008 internals book I read (MSFT Press)):

The query processor first gets the left table (table1)
Joins the second table (table2) and forms a cartesian product before filtering out the necessary rows (if applicable)
Then performs the WHERE, ORDER BY, GROUP BY, HAVING clauses with the SEELCT statement last.

So if in statement #1 above, the table is smaller, the SQL engine has less work to do when forming the cartesian products. Then when you reach the where statement, you have a reduced result set from which to filter in memory.

I could be so far off the mark it's unreal. Like I said, it's a theory.

Your thoughts?

Note: I've only just thought of this question and haven't had chance to run any tests my self yet.

Note 2: Tagged as SQL Server as I don't know anything about the implementation of MySql etc. Please feel free to answer/comment anyway

The logical processing of a query is on MSDN (written by Microsoft SQL Server team, not 3rd parties)

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

A derived table follows this, then the outer query does it again etc etc

This is logical though: not actual. No matter how SQL Server actually does it, these semantics are honoured to the letter. The "actual" is determined by the Query Optimiser (QO) and you avoid the intermediate Cartesion product you mentioned.

It's worth mentioning that SQL is declarative: you say "what" not "how" like you would for a procedural/imperative programming (Java, .net). So saying "this happens before that" is wrong in many cases (eg assumption of short circuits or L-to-R WHERE order)

In your case above, the QO will generate the same plan no matter how it is structured because it is a simple query.

However, the QO is cost based and for a complex query it may take 2 weeks to generate the ideal plan. So it does "good enough" which actually isn't.

So your first case may help the optimiser find a better plan because the logical processing order is different for the 2 queries. But it may not.

I have used this trick on SQL Server 2000 to get 60x speed performance improvement on reporting queries. As the QO improves version to version it gets better at working these things out.

And the book you mentioned: there is some dispute over it
See SO and the subsequent links: https://stackoverflow.com/q/3270338/27535


What is the sense to use server_lifetime with PgBouncer? I've found server_lifetime option in PgBouncer config, and I'm trying to undestand if it is really needed in our case.

https://pgbouncer.github.io/config.html

  The pooler will try to close server connections that have been
  connected longer than this. Setting it to 0 means the connection is to
  be used only once, then closed. [seconds]

  Default: 3600.0

What is the sense of closing and reopening connections?

Why does the doc say "will try"? May PgBouncer fail on this operation?

This is base of pgBoouncer or any pooler software - the main work of pgBouncer is reusing PostgreSQL connections. Creating lot of connections can be very expensive for short sessions - with fast queries. When physical connection can be shared (reused) for more clients, you can get much faster execution.

Second important role of pgbouncer and similar software is a protection against overloading. PostgreSQL has not internal pooling - any connected user can run any SQL. There are lot of applications with long idle connections. These applications requires high MAX_CONNECTION number. Hidden assumption is low activity on these connections. But sometimes can be pretty high activity, pretty high requests on new connects and the database can be overloaded. The MAX_CONNECTION should be about 10 x CPU cores. Inadequate value doesn't protect database against overloading and finish by failure. For these application can be pgbouncer safe solution - this software is able to share physical idle connections for lot of clients.

The server_lifetime enforces returning allocated sources to operation system. Some sources (like memory) is internally managed and it is newer returned to operation system (usually). Some memory expensive operations can require lot of memory. First time this memory is allocated from system. Next times this memory is managed by PostgreSQL only - it is much cheaper and faster. But sometimes it can be ineffective too. pgbouncer has not information which process has preallocated memory - and sometimes memory can be used better. The safeguard is server_lifetime. One hour is conservative value - and can be shorter - 10 minutes. pgbouncer close only not active connection - so if the connection is used by any client: opened transaction, long active command, .. server_lifetime cannot be applied.


Cannot login to SQL Server as a member of AD group I have created a login for an AD group:

CREATE LOGIN [MYDOMAIN\Development Admins] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

One of the members of that AD group is a user named DBGuy. I can see the DBGuy user in this AD group if I execute

xp_logininfo 'MYDOMAIN\Development Admins', 'members'

But if I try to login using the DBGuy account, I get an error:

  Error Number: 18456
  Severity: 14
  State: 1
  Line Number: 65536

And in the error logs I see:

  Login failed for user 'MYDOMAIN\DBGuy'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.50.127]

Some other info....

Other AD accounts seem to work fine as Windows logins, I am only
having an issue with this group.
I found an article from Aaron Bertrand
describing a similar issue regarding the default database in the
connection- I have set the default database to master in the
connection settings.
One blog post suggested using exec sp_change_users_login Report to look for orphaned users; this returns zero rows for me.

This is an old thread, but it was the first hit on Google when I encountered the same issue.

My two cents:
In my case it turned out that the AD group was a Distribution group, instead of a Security group, hence it could not be used to grant access to SQL Server.


Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file Full error

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
InvalidArgumentException: There was a problem connecting to the database: SQLSTATE[HY000] [2019] Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)

mysql  Ver 14.14 Distrib 5.7.26

I'm trying to fix this error, but can't get it sorted on a live server. I have had to go through the procedure of adding the following to the /usr/share/mysql/charsets/Index.xml file on a local virtual machine which worked.

<charset name="utf8mb4">
  <family>Unicode</family>
  <description>UTF-8 MB4 Unicode</description>
  <collation name="utf8mb4_general_ci" id="45">
    <flag>primary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="utf8mb4_bin"     id="46">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

I've restarted mysql a couple of times since updating this to no avail. My database connection is listed like so

<?php
$db = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $db_username, $db_pass);

And all my database variable seem to check out based on other posts I've seen

my.cnf has the following set too

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Is there any kind of cache that could be failing to clear even after a service mysqld restart command? (CentOS 6)

I've found a fix for this, which was related to the package php71w-mysqlnd.x86_64 / php71w-mysql.x86_64

My local machine had php71w-mysqlnd.x86_64 installed whereas the live server had php71w-mysql.x86_64

After running the following two commands, it's now working

yum erase php71w-mysql.x86_64
yum install php71w-mysqlnd.x86_64

service httpd restart

Ref: https://stackoverflow.com/a/38006719/871150


Is there a practical limit on the number of PostgreSQL TYPES? We are using Postgresql 9.6  in an application where we are considering using the TYPES system to extend new object types

The system would start with 10-20 types and then users would have a UI to create new types, probably composite types.

Question - is there a practical limitation on the number of TYPES?

From a system design alternative perspective we could use MongoDB and JavaScript objects which would give us more expressiveness - it would also require us to support MongoDB which is not our core expertise or we could go in other directions like using F# type system but that is going astray from this question....

and we believe the PG types will give us better mileage....

Practical, no. All internal types are represented by an oid. That's an unsigned four-byte integer.

SELECT oid, *
FROM pg_type;

PostgreSQL uses OIDs as an internally unique ID. When you exhaust them you're out.

Creating 1,000 types: NOT A PROBLEM.
Creating 4.3 billion types: PROBLEM.

Update

As a side note, I don't see how you can expose the types usefully to a user. PostgreSQL has a type jsonb. It's indexable and stored as binary, like Mongo's BSON. I would be using that.


Calculate Total Visits I am trying to write a query where I have to calculate number of visits for a customer by taking care of overlapping days. Suppose for itemID 2009 start date is 23rd and end date is 26th therefore item 20010 is between these days we will not add this purchase date to our total count.

Example Scenario:

Item ID Start Date   End Date   Number of days     Number of days Candidate for visit count
20009   2015-01-23  2015-01-26     4                      4
20010   2015-01-24  2015-01-24     1                      0
20011   2015-01-23  2015-01-26     4                      0
20012   2015-01-23  2015-01-27     5                      1
20013   2015-01-23  2015-01-27     5                      0
20014   2015-01-29  2015-01-30     2                      2

OutPut should be 7 VisitDays

Input Table:

CREATE TABLE #Items
(
CustID INT,
ItemID INT,
StartDate DATETIME,
EndDate DATETIME
)

INSERT INTO #Items
SELECT 11205, 20009, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20010, '2015-01-24',  '2015-01-24'
UNION ALL
SELECT 11205, 20011, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-28',  '2015-01-29'

I have tried so far:

CREATE TABLE #VisitsTable
    (
      StartDate DATETIME,
      EndDate DATETIME
    )

INSERT  INTO #VisitsTable
        SELECT DISTINCT
                StartDate,
                EndDate
        FROM    #Items items
        WHERE   CustID = 11205
        ORDER BY StartDate ASC

IF EXISTS (SELECT TOP 1 1 FROM #VisitsTable)
BEGIN

SELECT  ISNULL(SUM(VisitDays),1)
FROM    ( SELECT DISTINCT
                    abc.StartDate,
                    abc.EndDate,
                    DATEDIFF(DD, abc.StartDate, abc.EndDate) + 1 VisitDays
          FROM      #VisitsTable abc
                    INNER JOIN #VisitsTable bc ON bc.StartDate NOT BETWEEN abc.StartDate AND abc.EndDate
        ) Visits

END

--DROP TABLE #Items
--DROP TABLE #VisitsTable

There are a lot of questions and articles about packing time intervals. For example, Packing Intervals by Itzik Ben-Gan.

You can pack your intervals for the given user. Once packed, there will be no overlaps, so you can simply sum up the durations of packed intervals.

If your intervals are dates without times, I'd use a Calendar table. This table simply has a list of dates for several decades. If you do not have a Calendar table, simply create one:

CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
    [dt] ASC
));

There are many ways to populate such a table.

For example, 100K rows (~270 years) from 1900-01-01:

INSERT INTO dbo.Calendar (dt)
SELECT TOP (100000)
    DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '19000101') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

See also Why are numbers tables "invaluable"?

Once you have a Calendar table, here is how to use it.

Each original row is joined with the Calendar table to return as many rows as there are dates between StartDate and EndDate.

Then we count distinct dates, which removes overlapping dates.

SELECT COUNT(DISTINCT CA.dt) AS TotalCount
FROM
    #Items AS T
    CROSS APPLY
    (
        SELECT dbo.Calendar.dt
        FROM dbo.Calendar
        WHERE
            dbo.Calendar.dt >= T.StartDate
            AND dbo.Calendar.dt <= T.EndDate
    ) AS CA
WHERE T.CustID = 11205
;

Result

TotalCount
7


How to concatenate old string and new string in mysql procedure I want to get string from concatenate in while loop by step

1.get value(TmpAttrTH)

2.concatenate string(AttrTH) and value(TmpAttrTH)

My code like this

SET AttrTH = ''; -- First set AttrTH to blank
while i < CountAttr DO -- CountAttr is 2  and i set start at 0
    SET TmpAttrTH = (select thainame from attribute  where code = (select SPLIT_STR(atrMapping,'|',i) from attrcontrol where atrcode = AtrSet));
 -- TmpAttrTH  it getdata of loop example "stringloop1" , "stringloop2"
    SET i = i + 1;
 -- AttrTH in Concat function is old AttrTH string
    SET AttrTH = CONCAT(AttrTH,TmpAttrTH);
 -- and SET AttrTH is new string will CONCAT(AttrTH,TmpAttrTH);
END WHILE;
    select AttrTH  as str;

My result Expect "stringloop1stringloop2" but i don't know why i get null result.

I try to explain this problem too hard for me. pls help.

CONCAT will return NULL if any of the inputs are NULL. This suggests that your select thainame... is returning NULL in at least one iteration.

CONCAT_WS will skip NULLs. Try using that instead and see what it gives you. Note the first argument is the desired separator, so if you want to duplicate CONCAT() but with NULL skipping just set that to an empty string, CONCAT_WS('',AttrTH,TmpAttrTH);

This should help you debug the problem. Alternately you can also use COALESCE on your SET TmpAttrTH if you want to set a default in cases where the result might be NULL. If you use COALESCE to set a default empty string you shouldn't get NULL back from the subsequent CONCAT.

Presuming the thainame is in Thai script, you may very well have collation/character set issues, which could be why it is returning null in the first place, your comparison may be failing. Check that all the columns you are comparing have the same character set/collation.

@Rick James is also entirely right that looping in SQL should be avoided, and likely is not necessary in the first place. So ideally rewrite the whole thing without the loop. But the above hopefully will help you get to the bottom of where the problem is.


Adding more space + bandwidth to a MongoDB cluster I've read a lot about sharding and have gone through cluster setup process as well. What I can't get my head around is the actual database capacity after its sharded.

So say for instance I have one master server + 3 config servers + 5 shards.

If master has 30 GB available, each shard has 40 GB and each of the config servers have available space of 20 GB, what is my total capacity for a sharded collection in the cluster?

Is it 230 GB? How do I find this out? Is there a command that I can run?

Also, as I scale out, wouldn't that put more and more stress on the master node in terms of resources/bandwidth? Can I have multiple masters? Or replication across two sets of clusters?

Thanks in advance :D

The config servers only hold metadata about which chunk is stored on which cluster, no actual data.

There is no such thing called a "master server" in mongoDB, but I think what you are talking about is the shard router (mongos). When that is the case, then that server doesn't store any data at all.

So the total capacity of the cluster is the sum of the capacity of your shards, which is 40GB * 5 = 200GB.

Should the mongo router become a bottleneck, you can add more of them. A cluster can be accessed by any number of routers. By the way: it's not uncommon to put the mongos process(es) on the same machines where the application(s) are running.

  "Or replication across two sets of clusters?"

Replication in MongoDB is on shard-level. Each shard can (and in a production setup should) be a replica-set of two or more nodes.


Why is MySQL Access denied after .bashrc edit? I added this line
#mysql password
alias mysql='mysql -umilenko -p*myrealpass*** -hlocalhost'

Anyway,it does not work
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'milenko'@'localhost' (using password: YES)

Also
mysql -u root -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Version
mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

I am not admin,I just need to use sql for my project.
How to solve this problem?

Never put your password into any script.
The accepted way to do this for MySQL is with a configuration file.

Use a separate configuration file to hold the password.
Protect that file so that you are the only one that can access it.
alias mysql to use that configuration file.

Something like this:
$ ls -ld ~/.milenko.cnf
-rw------- 1 ... ~/.milenko.cnf

$ cat ~/.milenko.cnf
[mysql]
username=milenko
password=REALPASSWORD

In .bash_profile
alias mysql="/usr/lib/mysql --defaults-extra-file=~/.milenko.cnf --umilenko hlocalhost"

Related answer.


How to provision the SQL Server Agent service after SQL Server Edition upgrade I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped.

I have tried to start but it fails.

Only when I have changed his built in account from "Network Service" to "Local System" it started.

Looking at MSDN How to: Upgrade to a Different Edition of SQL Server 2008 R2 Procedure 13 it says:

  Provision the SQL Server Agent service account by using SQL Server Configuration Manager

What does it mean provision it?
What built in account should the SQL Server Agent run with?

Thank you

Let me answer your question point wise

1.SQL Server express has SQL server agent code built in but disabled. When you go to sp_configure you can see Agent XP enable/disable option this is so because if any point of time you are planning to upgrade SQL Server Express Microsoft upgrade process does not have to do much change but just a minimum to enable SQL server agent functionality. So before upgrading to Standard it wants you to use proper service account as a start up for SQL server agent so that after upgrade when database engine tries to bring agent services up it can do it eaisly. By default NT Authority/System account has admin privileges so it was able to bring Agent services up. Also please make sure that you always change SQL Server/Agent service account using SQL Server configuration manager it preserves the ACL's

2.As a good security practice SQL server should not run with Built in account instead a domain account should be created and granted least privileges to run it. Below Microsoft web resource will tell you what rights are required. The article is bit lengthy but beautifully written

http://msdn.microsoft.com/en-gb/library/ms143504.aspx


Jobs for cycling error logs Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs:

cycle the error log once it reaches a size in Mb or expands to say 2 weeks
move the oldest error log to a different location once we reach the limit of archived error logs files (now we have the default, 6)

On the answer to my previous question I got the powershell snippet I'm posting at the end to get the size of the error logs. But is the first time I will attempt to do something with powershell so I do not know even where to start with this. Any help to setup these jobs? Maybe powershell is not needed and can be done using CmdExec or tsql? Is not that I know CmdExec, just asking on other approaches. The best one would be tsql as I've more experience with it.

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YOUR_SQL_SERVER_NAME")
$SqlServer.EnumErrorLogs() |
    Select-Object Name, @{Name = "SizeMB"; Expression = {[System.Math]::Round($_.Size/1024/1024,2)}}

If it were me I would create a SQL Agent job for cycling the error log based on size or date. Run it a few times a day and you can even set up alerts to notify if the error log was cycled.

CREATE TABLE ##Temptable
(
[Archive #] tinyint,
[Date] datetime,
[Log File Size (Byte)] INT
)
insert into ##Temptable exec xp_enumerrorlogs
IF (
    SELECT [Log File Size (Byte)] from ##Temptable where [Archive #] = 0
    ) > 5242880 --5MB
    BEGIN
    Exec sp_cycle_errorlog
    END
drop table ##Temptable

You could also change the IF statement or add an OR statement to look at the date and cycle if it is older than 2 weeks or however long you want.

IF (
    SELECT [Date] from ##Temptable where [Archive #] = 1
   ) < GETDATE() -14

EDIT: Archive for the date based cycle needs to be the previous error log. Log 0 will always return current date, since it is active, duh!

As for moving them, you can keep up to 99 log files. Do you think your extended information needs will exceed 100 logs? If not, I would move the error log location to a disk that can hold 100 full logs and change the maximum number of logs.


mysqlbinlog recovery table wise I am going to restore my MySQL database table using log files. My database name is "decsys" .

I have already created sql using log files

C:\wamp\bin\mysql\mysql5.5.8\bin>mysqlbinlog  -d decsys C:\wamp\bin\mysql\mysql5.5.8\data\mysql-bin.0000xx > sql1.sql (file size 8GB)

I want recover not whole database, only single table. (table name :cm).

The most error-proof approach is to restore all databases -- the entire server -- to a different machine, by loading a backup (dump) file and playing back the appropriate logs, beginning at the binlog coordinates where the dump began... and then using mysqldump to extract the one table of interest and copy it to the desired destination.

In any event, you do have to restore something from a backup, first -- which I assume you understand, but have not mentioned.  Playing back the binary logs allows you to capture the events that occurred between the restored backup and the end of the log.

Playing back a single table's row events is, to my knowledge, not supported.  But, even restoring a single database (schema) this way is a complex operation.

The --database or -d option for mysqlbinlog can only definitively identify the desired database with sufficient accuracy when the binlog_format on the server that created the log entries was set to ROW, or, in the case where the binlogs were generated by a replica with log_slave_updates enabled, at least one of the servers in the cascade, including the last one, had its binlog_format set to ROW, because this coerces the replication events into ROW format, which is a one-way process.

If the events were not logged in ROW format, then playing back the binary logs can't be guaranteed to work correctly without all of the other data on the server also being present.  It is also not possible to be certain that the --database or -d option will catch all of the statements, if they were not logged in ROW format, because of interactions with the USE statement and other intricacies of how MySQL interprets replication events.

This does not mean that mysqlbinlog is broken, only that it's an advanced tool that can only be used within the constraints of the way replication works.

The binary log format was designed for replicating changes from one server to another, and only contains enough information to do that when the two servers in question begin with the same data set -- hence the reference to restoring from a backup.

Also, playing back a binary log into your production system is really not something you would ever want to do unless your production system had been damaged so badly that you had no choice but to completely reload it from a backup and wanted to play-forward through the available logs to minimize the amount of lost data.


Create Table with Int-attribute which can only use values between 6 and 36 Hello I'm learning for my databases examination and in one exercise they asking for creating a table with a attribute which can only use values between 6 and 36. How do I solve it?

We used in the course always Oracle databases and not mysql.

Here is the original text of the exercise (in german language):

Erstellen Sie eine Tabelle PROJEKTE. Projekte haben einen Namen, eine
Projektnummer, die Personalnummer eines Professors, der die Rolle des Projektleiters
übernimmt, einen Starttermin und eine Laufzeit (ganze Zahlen zwischen 6 und 36).

Most likely, the intention is for you to create a table and a CHECK constraint on the table

CREATE TABLE foo (
  col1 integer check( col1 between 6 and 36 )
);

And a demonstration that it works

SQL> CREATE TABLE foo (
  2    col1 integer check( col1 between 6 and 36 )
  3  );

Table created.

SQL> insert into foo values( 5 );
insert into foo values( 5 )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0047240) violated

SQL> insert into foo values( 37 );
insert into foo values( 37 )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0047240) violated

SQL> insert into foo values( 12 );

1 row created.


Are there any Database functions which allow access to other network services in PostgreSQL I have set up a new postgres installation and have to meet the security requirements from my company.
There is one requirement were i'm not quite sure:

Database functions which allow access to other network services (e.g. ,SMTP, HTTP, SNMP, FTP etc.), must be deleted or deactivated.
Motivation: Some database systems provide functions that are normally offered by an application server. For instance it is possible via special stored procedures to send e-mails or launch web queries to external systems.

I have installed postgres (PostgreSQL) 9.3.4. Are there any functions like this in a standard installation on Red Hat Enterprise Linux Server release 6.5 (Santiago)?

I have never seen that personally, so I looked up all the functions in Postgres found in the docs here.

The only thing it showed remotely close was 'network address functions' in this section.  Outside of that the docs don't show anything for network access functions.  Note this doesn't mean you can't write your own functions that access the system or OS which then can access the network.


Increment value in row if it exists, otherwise create row with value? Below is the MySQL table layout that I'm working with.  Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website.  My question is how would I go about having a new _views row for instance created upon the first visit to ID: 1106 or 1107 with a MySQL insert?

ID          key         value
-----       -----       ------
1104        _reserve    10000
1104        _views      100
1104        _watchers   2
1104        _inquiries  1
1105        _reserve    1500
1106        _reserve    24000
1106        _views      236
1107        _reserve    45300

INSERT INTO mytable (ID,`key`,`value`) VALUES (1106,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

and

INSERT INTO mytable (ID,`key`,`value`) VALUES (1107,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

If your table has value defined as

CREATE TABLE mytable
(
   ...
   value INT DEFAULT 1,
   ...
);

then the INSERTs would be

INSERT INTO mytable (ID,`key`) VALUES (1106,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;

and

INSERT INTO mytable (ID,`key`) VALUES (1107,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;


What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field? What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008?

It appears that I could store up to 4000 characters according to Microsoft Technet. Windows Dev Centre at MSDN seems to suggest that 260 characters will be sufficient.

If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to NVARCHAR(4000) on the way out as that is the most that can be stored by a SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since this scenario concerns storing the value in a local variable or returning in a result set (as opposed to how it is stored in the table), there is no reason to use less than 4000, and no use in specifying MAX since the value stored in the table couldn't hold that data anyway.

However, why would a path be stored as a SQL_VARIANT in the first place? Even if the total length never exceeds even the default 260 characters, it would still be best to store as NVARCHAR(500), or NVARCHAR(1000), or something like that.


Default constraint issue I have a table TempTable with one column having a default constraint

ALTER TABLE [dbo].[TempTable]
ADD  CONSTRAINT [DF_TempTable_Version]
DEFAULT ((1)) FOR [Version]

Version is defined as decimal (18,1)

Most of the time the value 1.00 is inserted into the column but in some cases its inserting a null value.

How we can identify if the constraint is failing or if there is some other issues causing this?

How we can identify if the constraint is failing or if there is some
  other issues causing this?

The most likely cause is that a client is simply inserting a null, or updating the column to null, neither of which is prevented by a default constraint.

If you don't want nulls, just declare the Version column as NOT NULL, or add a check constraint.


How to shard a database like that of SO? I know a bit about sharding but I cannot wrap my head around that fact that say eventually SO cannot scale vertically then how would they shard their SQL DB? Even if we assume SO will not reach that day consider this a theoretical problem.

We have questions by timestamp, by vote, by answers, by users and so on. How this kind of database be sharded, if possible at all?

Sharding, or database partitioning, is usually done to allow parallel processing of chunks of data. Groups of records residing in different shards (partitions) can be processed independently of one another, thus effectively multiplying the database server capacity.

The biggest problem to solve when deciding the partitioning scheme is to ensure that data that are usually processed together (e.g. sets of frequently joined records) reside in the same partition. As a result the columns used to join tables (or their subsets) are often used as the partitioning key. If this principle is not followed partitioning usually turns into a big performance issue, because parallel processing of partitions becomes dependent on retrieving related sets of records from other partitions.

Subsequently, for effective partitioning the database needs to be designed from the beginning with this possibility in mind. You can't usually just "slap" partitioning on an existing database, unless the data model and queries are very simple.


How to enable missing Tempspc column in Oracle explain plan I am trying to debug "ORA-01652: unable to extend temp segment" from a data warehouse ETL batch job, but my plan_table_output view from explain plan does not show the "Tempspc" column. But I do not seem to have the "'PLAN_TABLE' is old version" problem.

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0

Query: select * from table(dbms_xplan.display_awr(sql_id, null, null, 'ALL'));

Columns returned: Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time

Notes returned:
- dynamic statistics used: dynamic sampling (level=2)

The following query shows that all columns in the global temp plan_table match the @catplan.sql admin script:

select * from all_tab_columns
where owner = 'SYS' and table_name = 'PLAN_TABLE$'
order by column_name;

And this query shows the public synonyms looks ok:

select owner, object_type, object_name from all_objects
where object_name like 'PLAN_TABLE%';

OWNER   OBJECT_TYPE  OBJECT_NAME
------  -----------  -----------
SYS     TABLE        PLAN_TABLE$
PUBLIC  SYNONYM      PLAN_TABLE

Does anyone have advice on how to enable the missing Tempspc column in my explain plan?

This TempSpc column will only be shown if Your query will need any Temporary space like if you are sorting a big chunk of records and viewing it.

If query don't need any temporary space, this column will not be shown.
This varies from explain plan of one query to another, so you will not find it in every explain plan result.

As per this Oracle Page;

  Temporary space, in bytes, used by the operation as estimated by the
  query optimizer's approach. For statements that use the rule-based
  approach, or for operations that do not use any temporary space, this
  column is null.


How to check the last time a table has been modified? I regularly backup tables individually via mysqldump. To avoid unnecessary backup, I would like to check if a table has been modified since the last backup. By "modified", I mean any change (INSERT, UPDATE, DELETE, ALTER, ...).
Using the schema, we can get UPDATE_TIME like
SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'db' AND TABLE_NAME = 't1'

but that's not enough.
A practical way is to check the last modified time of /var/lib/mysql/t1.ibd via common Linux commands such as stat, ls, etc. The problem is that I should give the script running user permission to access /var/lib/mysql, which I prefer not to do so for security reasons.
Is there any way to check the last time a table or t1.ibd has been modified through MySQL (without giving permission to another user for accessing /var/lib/mysql)?

Well this turned out to be quite an interesting side project. There are a couple of ways that this can be accomplished, and Rolando shared four common methods on this site back in 2014. That said, something told me that there had to be a better way ...
You're right that looking at UPDATE_TIME in information_schema is not enough, because this value is not updated when the base tables are InnoDB. The only reliable way to determine if an InnoDB table has been modified — to the best of my knowledge — is to query the file system. However, like you said, it's not a great idea to grant access to the MySQL data directory to just anybody.
So, with this in mind, if a database is small, consisting of perhaps a dozen or so tables, the fastest way to accomplish the goal would be to use a trigger that updates a specific table, let's call it TableTracker, to say that an INSERT, UPDATE, or DELETE was committed as a given time. However, this is not particularly scalable if a database consists of hundreds of tables, nor does it take into account new tables that are created unless people remember to also include the necessary trigger. Looking at this logically, we must query the file system. It's the only way to automate a lot of the potential problems away.
And here's a possible way of doing it:

Create a TableTracker table in a MySQL database somewhere
Write a shell script that will query the file system and record last modification times to TableTracker
Automate it in a cron job

The TableTracker table does not need to be in the same database (or databases) that is being tracked, but it does need to exist. Here's a very rough table:
DROP TABLE IF EXISTS `TableTracker`;
CREATE TABLE IF NOT EXISTS `TableTracker` (
    `table_name`    varchar(120)    NOT NULL,
    `created_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Now we need a shell script. This was written and tested on Ubuntu Sever 20.04, though it should work on any modern Linux platform:
#!/bin/bash
srcpath="/etc/mysql/my_dbname/*.ibd"
username="watcher"
password="superSecretPassword!123"
dbname="my_dbname"

sql=""
sql+="INSERT INTO TableTracker (table_name, updated_at) "
sql+="SELECT tmp.table_name, tmp.updated_at as updated_at "
sql+="FROM ("

for name in $srcpath; do
    ts=$(date -r $name "+%Y-%m-%d %H:%M:%S")
    fname="${name##*/}"

    tbl="SELECT '${fname%.*}' as table_name, '$ts' as updated_at UNION ALL "
    sql=$sql$tbl
done

sql+="SELECT '' as table_name, '2000-01-01 00:00:00' as updated_at) tmp "
sql+="WHERE tmp.updated_at > '2000-01-01 12:00:00' "
sql+="ON DUPLICATE KEY UPDATE updated_at = tmp.updated_at;"

# Record the Updates to MySQL
mysql -u $username -p$password $dbname <<< $sql

echo "All Done!"

Remember to set the file as executable with chmod +x.
From there, you can throw this file into a cron job and have it run as often as you'd like.
Notes:

this crude tool could be set up to watch multiple databases
this tool could be set up to watch any database file, not just InnoDB files
if this is running on the main database server, then no MySQL password will need to exist in the file, as root in Ubuntu can (usually) access root on MySQL directly
there is no error checking, so be sure to clean up the shell script if this is used in a production environment


Copy database to local from server As I show , I have server database version-10.50.1600 and local database version-10.0.1600 .
How can I copy database (with all tables and data(s)) from server to local which has different version ? I use MS sql server 2008 R2 .


When can I clean up archived WAL files? I need some help understanding the how archived WAL files are used and when I can clean them up.

Currently we have a master database replicating to a slave, but every so often our master database server runs out of disk space because the files archived using archive_command build up and we have to manually remove them.  I feel like we're missing something if we have to manually remove old archived WAL files because there are too many that the file system runs out of disk space...

Having said that, I'm not sure I understand the replication process enough to start deleting files and be confident that I'm not screwing anything up.  Does our slave use the archived files created by archive_command or does it rely only on the files in $PG_DATA/pg_xlog directory to maintain a consistent state with the master?

If that's the case, and we're doing a pg_dump of our master every six hours, could I update our backup script to also clear out all archive files that exist at the start of the pg_dump (once the pg_dump is complete of course)? from the docs:

  Note: pg_dump and pg_dumpall do not produce file-system-level backups
  and cannot be used as part of a continuous-archiving solution. Such
  dumps are logical and do not contain enough information to be used by
  WAL replay.

I just want to make sure that however we clean up the archived files, we still have what we need for replication to continue as well as be able to perform a PITR in since the last base backup.

Our archive command is cp %p /var/lib/postgresql/9.2/archive/%f and here is our recovery.conf file from the slave:

standby_mode = 'on'
primary_conninfo = 'host=<ip-address> port=<port> user=rep password=<password> sslmode=require'
trigger_file = '/tmp/postgresql.trigger.5432'

WAL Files are a record of all the transactions that have taken place .. but that's it - a delta between state before and state after, essentially.  Which means you have to have a BASE to start off of before you can apply any WAL Files.

If you clear out WAL archives without creating a new BASE, then you will be in trouble when you try to restore your database (via PITR) from them.

If you are going to delete WAL files, then you need to make sure you have a valid base to build from again afterward.

Read the documentation on WAL archiving for more information.

In terms of streaming replication, I believe that only uses files located in pg_xlog...  (files are not archived before they are sent, IIRC)

  If that's the case, and we're doing a pg_dump of our master every six hours, could I update our backup script to also clear out all archive files that exist at the start of the pg_dump (once the pg_dump is complete of course)?

Not entirely ... pg_dump creates a file that contains the information of your whole database that can be given to pg_restore...  For PITR, you actually need to create a file-level "backup" by copying your database files and WAL files (after issuing a pg_start_backup)


Best method to give two Oracle databases on the same server access to common data With a production and a test database on the same server, how should access be provided to some common quarterly update tables for both databases?

It is possible to put these tables into a third database, let us call it catalogs, but then  the tables within catalogs can't be accessed as efficiently as if the tables where directly in production or test. On the other hand, space is saved, as there is only one copy of the data.

I see the following two options

add a user and schema for catalog to
both database instances. That gives efficient access, but
duplicates space requirements and work to maintain the catalogs
add a third database instance for
catalogs and access this via database
links. This saves space, but what about performance.

Which of these solution is better?

Edit:

Some further information:
Catalog has about 60 tables using less than 1GB tablespace.
There are functions to be defined in the katalog schema and indexing is important.

You could store the catalogs tables in a separate schema on production.  test could then have a schema with the same name that uses views that select from the production tables using a database link.  This would make production fast because the data is local without requiring extra space.

If you decided that a particular table was too slow to access over the database link or having too great of an impact on production, then you could create a materialized view on test instead of a view.  You could create as many or as few of these as necessary to give the best balance of space and performance.

On the other hand, 1GB isn't that much space, so you might be better off importing into both databases even though it would double the space requirements.  It would have a speed advantage and would make test more like production.


set integrity not enforced in DB2 I am a bit confused with the SET INTEGRITY concept in DB2, why we can OFF the set integrity check to put the table in PENDING state ? does that mean that we can neglect the constraint definitions on the table and insert invalid rows into table arbitrarily ? if that is the case why we create the constraint at the first place ?

further more, there is options to turn on and off access. I am further confused, does that mean even before the integrity of table data being checked, we can allow the data to be read accessed ? is that same as saying we allow dirty read here?

+another question is, if that integrity check is put off, how to bring the table out of pending state ? does this always requires manual intervention?

According to IBM's documentation: "Typically, you need to manually perform integrity processing for a table in three situations: After loading data into a table; when altering a table by adding constraints on the table; and when altering a table to add a generated column."

That would be the why you need to do it. We use it right after we use the LOAD command as this bypasses referential integrity to load (for lack of a better word) a ton of records into tables. We end up needing to use SET INTEGRITY when we are done to have it check the integrity and verify our tables can be used.

In our case we use:

SET INTEGRITY <schema>.<table> ALLOW NO ACCESS IMMEDIATE CHECKED;

And yes, you do have to run the SET INTEGRITY command for each table that is in a pending state. (We use scripts to do it in our case to simplify things.)

For more information about the command see SET INTEGRITY for all available options.


Indexed varchar column very slow (40M+ rows) I've got an events table with over 40 million rows. It has a btree index on type: "index_events_on_type" btree (type).

Any equality look up takes ages! I tried tuning up the work_mem to get rid of the lossy value but it had little to no effect.

dev=# EXPLAIN (ANALYZE, BUFFERS) SELECT "events".* FROM "events" WHERE "events"."type" = 'contact.card.added';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on events  (cost=56176.20..1633783.13 rows=1614405 width=296) (actual time=252.846..13832.331 rows=1550851 loops=1)
   Recheck Cond: ((type)::text = 'contact.card.added'::text)
   Rows Removed by Index Recheck: 10414231
   Heap Blocks: exact=39335 lossy=758338
   Buffers: shared read=807020
   ->  Bitmap Index Scan on index_events_on_type  (cost=0.00..55772.60 rows=1614405 width=0) (actual time=243.096..243.096 rows=1550851 loops=1)
         Index Cond: ((type)::text = 'contact.card.added'::text)
         Buffers: shared read=9347
 Planning time: 0.100 ms
 Execution time: 13924.612 ms
(10 rows)

...and the table

dev=# \d events
                                            Table "public.events"
      Column       |            Type             | Collation | Nullable |              Default
-------------------+-----------------------------+-----------+----------+------------------------------------
 id                | bigint                      |           | not null | nextval('events_id_seq'::regclass)
 type              | character varying           |           | not null |
 aggregate_root_id | uuid                        |           | not null |
 entity_id         | uuid                        |           |          |
 parent_id         | bigint                      |           |          |
 created_at        | timestamp without time zone |           | not null |
 body              | jsonb                       |           |          | '{}'::jsonb
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "index_events_on_aggregate_root_id" btree (aggregate_root_id)
    "index_events_on_created_at" btree (created_at)
    "index_events_on_entity_id" btree (entity_id)
    "index_events_on_parent_id" btree (parent_id)
    "index_events_on_type" btree (type)
Foreign-key constraints:
    "fk_rails_68f023eb25" FOREIGN KEY (parent_id) REFERENCES events(id)
Referenced by:
    TABLE "events" CONSTRAINT "fk_rails_68f023eb25" FOREIGN KEY (parent_id) REFERENCES events(id)

Any guidance would be appreciated.

Update

Looks like the most time spent is in I/O as the query returns 1.5M records:

dev=# EXPLAIN (ANALYZE, BUFFERS) SELECT "events"."type" FROM "events" WHERE "events"."type" = 'bank_account.payout.added';
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on events  (cost=6324.92..520827.82 rows=182239 width=32) (actual time=283.119..15719.744 rows=1550851 loops=1)
   Recheck Cond: ((type)::text = 'contact.card.added'::text)
   Rows Removed by Index Recheck: 10414212
   Heap Blocks: exact=39336 lossy=758337
   Buffers: shared hit=75 read=806945
   I/O Timings: read=13783.176
   ->  Bitmap Index Scan on index_events_on_type  (cost=0.00..6279.36 rows=182239 width=0) (actual time=272.302..272.302 rows=1550851 loops=1)
         Index Cond: ((type)::text = 'contact.card.added'::text)
         Buffers: shared hit=75 read=9272
         I/O Timings: read=67.302
 Planning time: 0.069 ms
 Execution time: 15807.701 ms
(12 rows)

Rows returned:

dev=# SELECT COUNT(*) FROM "events" WHERE "events"."type" = 'contact.card.added';
  count
---------
 1550851
(1 row)

Total rows:

dev=# SELECT COUNT(*) FROM "events"
dev-# ;
  count
----------
 36447779
(1 row)

Do you really need to select every column?  If not, then don't.  It might be generating extra TOAST table lookups, or might be inhibiting index-only scans.

You are selecting 1.5 million rows.  Indexes are awesome, but they are not magical.  1.5 million is a lot, especially when scattered randomly throughout a giant (how big is it?) table.

You can almost certainly speed this up by clustering the table on the "index_events_on_type" index.  But this a resource intensive operation which will probably require a maintenance window to do, and it won't stay clustered upon future inserts/updates.  Alternatively, you could partition the table by "type".  This has more or less the same effect as clustering (in this context!) but it stays partitioned in the face of future actions.

What version of PostgreSQL are you using?  I would have expected you to get a parallel query here if it is recent version (not that it will necessarily be effective, but still I would have expected one).

Given that you are retrieving ~4% of the table, a seq scan might be more effective than an index scan.  You can do set enable_bitmapscan=off and see how that does.


Get multiple column and row values in a sigle row I have a table structure as:

KEY     language_id        language_value
------------------------------------------
SELECT      1               Select Lang 1
SELECT      2               Select lang 2
GET         2               get lang 2

I want a result like

KEY     language_val_1        language_val_2
----------------------------------------------
SELECT   Select Lang 1         Select lang 2
GET                            get lang 2

If Search for a key "GET"

KEY     language_val_1        language_val_2
----------------------------------------------
SELECT   get lang 1

Thanks

This process is called pivot.
This is mostly done with GROUP BY and using MAX or SUM.

Create table/insert data.

CREATE TABLE t
    (`KEY` VARCHAR(6), `language_id` INT, `language_value` VARCHAR(13))
;

INSERT INTO t
    (`KEY`, `language_id`, `language_value`)
VALUES
    ('SELECT', 1, 'Select Lang 1'),
    ('SELECT', 2, 'Select lang 2'),
    ('GET', 2, 'get lang 2')
;

Query

SELECT
   t.key
 , MAX(CASE WHEN t.language_id = 1 THEN t.language_value END) AS language_val_1
 , MAX(CASE WHEN t.language_id = 2 THEN t.language_value END) AS language_val_2
FROM
 t
GROUP BY
 t.key
ORDER BY
 t.key ASC

Result

key     language_val_1  language_val_2
------  --------------  ----------------
GET     (NULL)          get lang 2
SELECT  Select Lang 1   Select lang 2

Searching for KEY "GET".

You need to put the pivot query as a delivered table so you can filter with a WHERE statement.

Query

SELECT
    t_pivot.key
  , t_pivot.language_val_1
  , t_pivot.language_val_2
FROM (

  SELECT
     t.key
   , MAX(CASE WHEN t.language_id = 1 THEN t.language_value END) AS language_val_1
   , MAX(CASE WHEN t.language_id = 2 THEN t.language_value END) AS language_val_2
  FROM
   t
  GROUP BY
   t.key
  ORDER BY
   t.key ASC
)
 AS
   t_pivot
WHERE
 t_pivot.key = 'GET'

Result

I got a different result as your example.
But it looks like your example result is wrong.

key     language_val_1  language_val_2
------  --------------  ----------------
GET     (NULL)          get lang 2


Map auto increment ids on multi-row insert I have the following table with an AUTO_INCREMENT column.

CREATE TABLE animals (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
);

Usually I load multiple rows with one INSERT like this:

INSERT INTO animals (name)
VALUES  ('dog'),('cat'),('cow');

Sometimes though I want assign to each value its ID. Like this:

1 -> dog
2 -> cat
3 -> cow

If I insert simple values I can select them back. But when I insert JSON documents I'm worried about using syntax like SELECT WHERE json IN (...).

I thought to get MAX(id) before selection, then get last_insert_id after selection and using count of inserted rows assign id to each. But I haven't found in documentation anything about order that rows in insert are processed, so I'm worried I won't be able to know what the ID's are.

Inserting rows one by one and get last_insert_id for each is not good solution. Is there a way to insert multiple rows at once and return to the application the IDs for each row?

Don't use MAX(id), that could be wrong if to another connection doing inserts.  last_insert_id, on the other hand, is local to the session, so it is unaffected by other connections.

INSERT INTO animals (name)
    VALUES  ('dog'),('cat'),('cow');
SELECT last_insert_id();  -- Let's say you get 34
-- Then dog=34, cat=35, cow=36

But -- beware of innodb_autoinc_lock_mode -- the setting of this can affect what I discussed above.  This is especially true if, for whatever reason, some of the rows fail to insert.  (Example: you have UNIQUE(name) and already have cat in the table.)  In at least one of the modes, 35 is 'burned'.

The following goes beyond what you have asked for...

I decided is the best way to deal with a similar problem involves two steps.  First, let me explain what is going on.  I am 'normalizing' the names of animals.  Instead of using "dog", I want to use the id associated with dog.  I have lots of data coming in, and I want to batch insert a bunch of animal names into the table, plus get the ids for them.  I am very likely to have lots of dups, but I don't want to 'burn' an id for every dup.

So...

INSERT ... SELECT ... LEFT JOIN ...  -- insert only the 'new' animals.
UPDATE ... JOIN ...  -- pull the ids into another table.

The details are spelled out here .


Table size and table partitioning on a single drive server Suppose I have a big table on a server with only one drive. The table may grow up to a half TB (hundred million rows). I think the purpose of the partitioning is mainly for maintenance purpose because of one drive.

At which size should the table be partitioned?

Kendra Little just released an excellent article on the topic: How To Decide if You Should Use Table Partitioning.

  This is usually the first question people have:  “Is my table big enough?”  My response is: “Let’s talk about why
  you’re interested in table partitioning. What is the problem you’re
  experiencing?”

Go over the article linked, is very good. It even has pictures of Unicorns! Hopefully it will shed some light for you.


SQL Server Profiler - How to filter trace to only display inserts, updates and deletes to one database? How do I limit a SQL Server Profiler to trace only inserts, updates and deletes to a specific database?

You can narrow down the list by specifying DatabaseName or DatabaseID in Column Filters:

Note however that you need to check the box "show all columns" first.

As for inserts, updates and deletes you can filter by text column at the same place:


Do I need to reindex mysql table after bulk delete? I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using 'innodb' as the storage engine. Do I need to change it? I think its better for concurrent insert and select. Please give your suggestions. Do I need to do reindexing?

Thanks in advance..

Do you need to optimize the tables when using InnoDB? Yes and no, depending on your workload and if you are encountering performance problems or not.

A shameless copy-paste from the MySQL documentation :

  For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which
  rebuilds the table to update index statistics and free unused space in
  the clustered index. This is displayed in the output of OPTIMIZE TABLE
  when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

  This operation does not use fast index creation. Secondary indexes are
  not created as efficiently because keys are inserted in the order they
  appeared in the primary key. See Section 14.14.6, “Limitations of Fast
  Index Creation”.

  InnoDB stores data using a page-allocation method and does not suffer
  from fragmentation in the same way that legacy storage engines (such
  as MyISAM) will. When considering whether or not to run optimize,
  consider the workload of transactions that your server will process:

  Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.
  Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.
  Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is
  available. See Section 14.10.5, “How Compression Works for InnoDB
  Tables” and Section 14.12.1, “Overview of InnoDB Row Storage”.
  High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC
  mechanism. See Section 14.5.12, “InnoDB Multi-Versioning”.


Efficient SQL database design for recording examples of a word I am designing a dictionary-like program which stores (and displays) a few example sentences of the given word. One way to design a SQL database for such problem is to create a table with two columns word and example and then for each example sentence add a new row with the repeated word in the column word. Is this approach common and efficient? I was also thinking of dynamically creation of a table of examples for each word and then relate the table to the word somehow. Is this approach better than previous one?

I would have 2 tables: Word: word_id, word

Then Example: word_id,example_sentence

This will likely be more efficient in terms of storage since you only need 4 bytes to store an id and possibly many more to store duplicates of a word.


Why is my Mac install of Postgres not tokenising Thai words? On my Mac install of PG:

=# select to_tsvector('english', 'abcd สวัสดี');
 to_tsvector
-------------
 'abcd':1
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | hello | {english_stem} | english_stem | {hello}
 blank     | Space symbols   |  สวัสดี | {}             |              |
(2 rows)

On my Linux install of PG:

=# select to_tsvector('english', 'abcd สวัสดี');
    to_tsvector
-------------------
 'abcd':1 'สวัสดี':2
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |    description    | token |  dictionaries  |  dictionary  | lexemes
-----------+-------------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII   | hello | {english_stem} | english_stem | {hello}
 blank     | Space symbols     |       | {}             |              |
 word      | Word, all letters | สวัสดี  | {english_stem} | english_stem | {สวัสดี}
(3 rows)

So something is clearly different about the way the tokenisation is defined in PG. My question is, how do I figure out what is different and how do I make my mac install of PG work like the Linux one?

On both installs:

# SHOW default_text_search_config;
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

# show lc_ctype;
  lc_ctype
-------------
 en_US.UTF-8
(1 row)

So somehow this mac install thinks that thai letters are spaces... how do I debug this and fix the "Space Symbol" definition here.

Interestingly this install works with Armenian, but falls over when we reach Hebrew

=# select * from ts_debug('ԵԵԵ');
 alias |    description    | token |  dictionaries  |  dictionary  | lexemes
-------+-------------------+-------+----------------+--------------+---------
 word  | Word, all letters | ԵԵԵ   | {english_stem} | english_stem | {եեե}
(1 row)

=# select * from ts_debug('אאא');
 alias |  description  | token | dictionaries | dictionary | lexemes
-------+---------------+-------+--------------+------------+---------
 blank | Space symbols | אאא   | {}           |            |
(1 row)

Only significant diff I am seeing is that one is compiled with clang and one with gcc

PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit

VS

 PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit

My question is, how do I figure out what is different

show lc_ctype;

in both databases

  and how do I
  make my mac install of PG work like the Linux one?

Recreate the database on the mac with a proper utf-8 lc_ctype.

Related: Postgres to_tsvector is case sensitive on mac os


Many to Many Link Table Return full chain from one record I am looking to run a query on a many to many link table which chains records together and displays all the linked records. I've included a simplified data set for copy/paste as an example.

I have a table of objects that include a code and a description which are linked to each other using a link table. I want to pass in a code from one object and return all the objects in the chain that includes it. I can pull back results directly linked to the input, but I am struggling to extend this to include all links in the chain. Any help would be much appreciated.

Thanks, D.

-- Create Tables
DECLARE @TestObject TABLE(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Code NVARCHAR(8) NOT NULL,
Description NVARCHAR(30) NOT NULL
)

DECLARE @TestRelationships TABLE(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Link1 INT NOT NULL,
Link2 INT NOT NULL
)

-- Populate Object Data
INSERT INTO @TestObject VALUES ('TEST1111', 'Test Object 1')
INSERT INTO @TestObject VALUES ('TEST2222', 'Test Object 2')
INSERT INTO @TestObject VALUES ('TEST3333', 'Test Object 3')
INSERT INTO @TestObject VALUES ('TEST4444', 'Test Object 4')
INSERT INTO @TestObject VALUES ('TEST5555', 'Test Object 5')
INSERT INTO @TestObject VALUES ('TEST6666', 'Test Object 6')
INSERT INTO @TestObject VALUES ('TEST7777', 'Test Object 7')
INSERT INTO @TestObject VALUES ('TEST8888', 'Test Object 8')
INSERT INTO @TestObject VALUES ('TEST9999', 'Test Object 9')

-- Populate Link Chain 1
INSERT INTO @TestRelationships VALUES (1, 2) -- Links 1 AND 2
INSERT INTO @TestRelationships VALUES (1, 3) -- Links 1 AND 3
INSERT INTO @TestRelationships VALUES (1, 4) -- Links 1 AND 4
INSERT INTO @TestRelationships VALUES (3, 5) -- Links 3 AND 5
INSERT INTO @TestRelationships VALUES (5, 6) -- Links 5 AND 6

-- Populate Link Chain 2
INSERT INTO @TestRelationships VALUES (7, 8) -- Links 7 AND 8
INSERT INTO @TestRelationships VALUES (8, 9) -- Links 8 AND 9

-- Select Records (inc. some results)
-- TEST1111: Shows 1, 2, 3, 4 (Required to show 1, 2, 3, 4, 5, 6)
-- TEST9999: Shows 8, 9 (Required to show 7, 8, 9)

DECLARE @input NVARCHAR(8)
SET @input = 'TEST1111'

SELECT DISTINCT O1.* FROM @TestObject O1
JOIN @TestRelationships TR
ON O1.ID = TR.Link1
OR O1.ID = TR.Link2
JOIN @TestObject O2
ON (O2.ID = TR.Link1 AND O2.ID <> O1.ID)
OR (O2.ID = TR.Link2 AND O2.ID <> O1.ID)
WHERE O1.Code = @input OR o2.Code = @input

Just to follow up with a solution, with thanks to Akina in the comments.

I had to convert the above example to a proper table, and use the following select:

WITH Test (ID, Link1, Link2) AS
(SELECT O.ID, R.Link1, R.Link2
FROM TestObject AS O
LEFT OUTER JOIN TestRelationships AS R
ON R.Link1 = O.ID
WHERE O.ID NOT IN (SELECT Link2 FROM TestRelationships)
 AND O.Code = 'TEST1111'
UNION ALL
SELECT T.ID, R1.Link1, R1.Link2
FROM TestRelationships AS R1
INNER JOIN Test AS T
ON T.Link2 = R1.Link1)
SELECT * FROM Test


Inserting a new lookup value for the SSIS Lookup Transformation This may be obvious, but I've been banging my head for a while... please point me in the direction of an article if there is one.

I'm importing a series of Excel files into a SQL database.

They will have a list of text items with a value:

Name   |   Value
----------------
Name1  |   100
Name2  |   140
Name3  |   200

The target database has a lookup table of "Name" mappings.

What I want to do is find a corresponding NameID for a row, and put that into a column (this is the easy part), however, if there is no corresponding entry in the Name table it should insert the row and get the new ID.

Is there an accepted way to do this?  I'm looking at low volumes, so I don't need it to be particularly performant, just simple and easy to follow.

Option 1

It's been awhile since I have done it but basically this is what I did.

After the lookup, do a conditional split.
Split the NULL values off on their own path.
Use an OLE DB Command to insert your rows.  Be careful here because you don't want to insert the same row over and over again.
Re-lookup your value (or get it back from the OLE DB Command if you can).
Use a Merge to merge the two data flows back together again.

Option 2

Do it with 2 data flows. In the first one, find all of your data that doesn't match and do the insert. In your second one, do the lookup.

The latter option may be simpler for your purposes.


INSERT take too much time I have in my MySQL DB a few tables
Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections inserting data in the same table at the same time.

In one of my table, I'm doing requests like : INSERT INTO ... VALUES <1000 tuples> ON DUPLICATE KEY UPDATE < do some basic operation on columns which are not in the PK> .
This table does not have any index, it only has on PK on 2 columns. The row format is fixed (only fixed size column). The table is using MyISAM, and has about 3 million rows (The total size of the table is about 400Mb, and the PK takes about 40Mb.)

Most of the time, the UPDATE part of the query is applied, since we may have a lot of lines already existing in the DB in our queries.

My problem is that doing an insert for 10000 rows take about 10s, which is a real problem for my application.

I tried to OPTIMIZE the table but it didn't change anything.

Here are the config parameters I changed:

key_buffer_size = 2G
max_allowed_packet = 64M
table_open_cache = 256
Binary logs are disabled (it's not

I put this big key_buffer_size since other tables might be using it too.

So, do you guys have any suggestion about how to fix that problem ?

If you need more infos, plz tell me :)

Thanks !

Edit: I'm using JDBC to run my queries, is there something specific to my case to enable in jdbc parameters ?

You should increase your bulk_insert_buffer_size to 512M because it accommodates bulk loading of MyISAM tables. According to the MySQL Documentation on bulk_insert_buffer_size:

  MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

In MySQL 5.6, the max value for bulk_insert_buffer_size is 4G.

If your MyISAM table has TEXT/BLOB data, I would also increase the max_allowed_packet. What is a MySQL Packet used for ? See my SuperUser Post What does the MySQL “max_allowed_packet” setting actually control?

UPDATE 2014-02-15 19:49 EST

Your last comment was

  The table has only fixed size data (char[60], int, ...) One line takes 126b. About the bulk_insert_buffer_size, since I'm using INSERT ... ON DUPLICATE ..., would it help ? Also, why did you choose 512M, and not a slower value, similar to the max_allowed_packet ? (or maybe 128M)

My choice of 512M was arbitrary. You can set it to whatever you are comfortable with. Just don't leave it at the default value of 8M.

Since you gave the row size, let's to the math.

10,000 rows X 126 bytes/row = 1,260,000 = 1.2 MB

OK, bulk insert buffer may not be an issue.

OBSERVATION

I don't think MySQL likes INSERT IGNORE  combined with ON DUPLICATE KEY update. Why ?

INSERT IGNORE says INSERT but reject the incoming row if the PRIMARY KEY already exists.
ON DUPLICATE KEY says INSERT but perform some UPDATE on specific columns if the PRIMARY KEY already exists.
Logically, this does not make sense to use both of them. Which one do you want?

IGNORE duplicates
UPDATE columns on duplicates

The INSERT should either be

INSERT IGNORE

or

INSERT ... ON DUPLICATE KEY

Since you have a meaningful ON DUPLICATE KEY, ditch the word IGNORE.

You could also change INSERT ... ON DUPLICATE KEY into REPLACE INTO if you are replacing all non PRIMARY KEY columns.


Postgres file increment size I'm COPY-ing 300 tables / 400G to PG10@win2016server using a few threads to speed things up, but because the table files are incremented in 64-kb units (or something there-about) they get fragmented on disk which hurt read performance.

Ideally I would like to make sure that files are expanded in larger increments (eg. 8 mb), alternative would be to make initial size = max segment size (1gb). Is there any way to accomplish this?

And I know, single threaded copy would solve the issue at hand, but I would still have the same problem after the copying, as tables grow continually and still only in small fragmented chunks.

EDIT:
As a "proof" of sorts, i created a simple table

CREATE TABLE testtable
(
  id bigint primary key,
  somestring varchar(4000)
);

BEGIN
  FOR i IN 1..2000 LOOP
   insert into testtable values(i, repeat('Jeg kan en sang der kan drive dig til vanvid.', 40));
 END LOOP;
END

which produces a table of 4096kb, which contig.exe reports to be of 25 fragments (in this instance), or 160kb on average. For 4 mb this is not an issue, for 400gb it is.

NTFS is, in my experience, typically pretty good about cleaning up fragmentation as files grow. I would wait until you actually have the large files and have run defragmentation on the volume before worrying about it.

Just because small files tend to be easily fragmented does not mean that large files will be a problem.


How to do Incremental statistics on auto_created stats(_WA_) in SQL server How to do Incremental statistics on auto_created stats(WA) in SQL server? I know I can do this for the non auto_created stats (indexed column) either by rebuilding index on the table or at DB level.

Is there any way to achieve this on auto_created stats(WA) also?

The underlying issue is-

On a good day query runs in 10 sec and on a bad day it take 5 min. This query uses 15 columns in a join on a large table. This table is partitioned. The only difference I found that on the bad run stats were also updated on those 15 columns at the same time query is running. please advise.

SQL Server 2017:
is_auto_create_stats_on= True;
is_auto_create_stats_incremental_on= False;
is_auto_update_stats_on= True;
is_auto_update_stats_async_on= False;

You have the default settings.

is_auto_update_stats_on= True;
is_auto_update_stats_async_on= False;

So when you run a query that needs updated stats, the stats will update before your query runs.  You could update stats more agressively, or allow the query to run with the old stats while the stats are updated by turning on 'is_auto_update_stats_async_on'.

And yes, modifying an auto-created statistic to be incremental appears to work fine.  eg in AdventureWorksDW after partitioning FactInternetSales:

ALTER DATABASE current SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON)
GO
UPDATE STATISTICS [dbo].[FactInternetSales]([_WA_Sys_00000004_4E88ABD4]) WITH INCREMENTAL = ON
GO
UPDATE STATISTICS [dbo].[FactInternetSales]([_WA_Sys_00000004_4E88ABD4]) WITH RESAMPLE ON PARTITIONS(2)
GO


Migrate SQL Server database with instance level logins Is there a way to migrate database along with all users and logins at the instance and database levels?

I am aware of contained databases and orphan users and solution for that.

I am using SQL Server 2012, 2014, and 2016. I came across another blog that Import Export functionality can be used, but yet have to try it.

There are some fantastic Powershell scripts located at dbatools.

You could use Copy-SqlDatabase to migrate your databases.

Then, you could take advantage of Copy-SqlLogin by generating the Powershell commands via T-SQL script for the logins you're interested in.

select 'Copy-SqlLogin -Source sqlserver -Destination sqlcluster -Logins ' + name + '' from sys.database_principals where type_desc='sql_user'

After migration, make sure you take care of orphan users.  You could use something like this:

declare @cmd varchar(max)=''
select
    @Cmd=@cmd + 'ALTER USER ' + dp.name + ' WITH LOGIN = ' + dp.name + ';' + char(10)
from
    sys.database_principals dp left join
    master.sys.server_principals sp on
        sp.name=dp.name
where
    dp.type='s' and
    dp.default_schema_name<>dp.name and
    sp.name is not null
order by dp.name
--print @Cmd
exec (@cmd)


PostgreSQL 9.5 database showing signs of corruption in logs, though clients work normally We have a database server running PostgreSQL 9.5 on a Ubuntu 16.04 LTS box, with the total data size just under 30 GB.
We've set archive_mode to on, and archive_command is rsyncing WAL files to another server that is running Barman. To prepare for an eventual PostgreSQL upgrade using pg_upgrade, we've set up a disposable server instance also running PostgreSQL 9.5 that we're using as the target for barman recover. The idea is to get a snapshot of the DB there that we can test pg_upgrade on, to a PostgreSQL 12 instance running on the same server.
It is in this process, doing barman recover to the disposable server, that I discovered an issue. The target DB was stopped, and after Barman had finished rsyncing the contents of the data directory there, I restarted the server. It came online just fine and accepted queries as usual, but this error started appearing in the log file:
2020-12-08 12:05:20 EET ERROR:  could not access status of transaction 79509466
2020-12-08 12:05:20 EET DETAIL:  Could not open file "pg_clog/004B": No such file or directory.
2020-12-08 12:05:20 EET CONTEXT:  automatic vacuum of table "template0.pg_catalog.pg_statistic"

It got printed four times on server startup, and then again every 60 seconds.
I first assumed that this was an issue with Barman or the data on its server, but since no operational problems could be found there, I turned my eye to the production server itself. Lo and behold, ever since the Oct 21, that error has appeared on the production server. So this was never about something being wrong about the backup or restoration process, it was the actual production data that was wrong!
This has gone unnoticed. Nothing happened on the server (a VPS on Upcloud) on Oct 21 that we can discern: all of our web applications–for which we have robust error alerts–relying on the server have kept working normally throughout. Nobody can remember any manual operations having been run there on the day. It's possible that this was an issue with the VPS provider, who on Oct 28 reported having detected a storage backend problem, which was quickly resolved afterwards. This was a week after our problem first appeared in the logs, but I suppose that the symptoms may have started earlier.
Alphabetically the first file inside pg_clog is 004C. Based on some googled advice, I tried creating a 256k file of all zeroes called 004B. After I did, the error changed to:
2020-12-08 13:35:25 EET CONTEXT:  automatic vacuum of table "template0.pg_catalog.pg_statistic"
2020-12-08 13:35:40 EET ERROR:  found xmax 79509466 from before relfrozenxid 80163082

(This was on the disposable server that was the barman recover target, I haven't dared touch production.) This gets printed at 15 second intervals.
I have no idea how to further analyze, let alone fix this issue. Everything is working normally as far as our database clients are concerned, but this needs to be corrected. Any help is appreciated. Note that my knowledge of PostgreSQL data storage internals is close to zero.

If only pg_statistic is affected, you might get out of this cheap:
Set allow_system_table_mods = on in postgresql.conf and restart PostgreSQL. Then run
TRUNCATE pg_catalog.pg_statistic;
ANALYZE;

Don't forget to reset allow_system_table_mods when you are done.
But you shouldn't ever trust a PostgreSQL database cluster that has had database corruption. Create a new cluster, export the old one with pg_dumpall and import it into the new cluster. Then discard the cluster that had data corruption.
You might want to create the new cluster with --data-checksums so that you get an error message if data change while on disk.
Now you should investigate the possible causes. Upgrade to the latest minor release and test your hardware for problems.