NVARCHAR issue when migrating from SQL Server to MySQL with MySQL migration wizard My issue is similar to this one but and I have tried what has been suggested but I still get the error. I have set the target column from VARCHAR(5120) to TEXT (tried LONGTEXT too) but the issue still keeps appearing of those exact columns. I even went into the schema and checked and it says they are TEXT. Am I getting this issue because the source column is VARCHAR(5120)? If so, is there any way to solve this. I simply want to use this migrated database as a local development copy with proper data.

The error message is as following:

  ERROR: gruppergister.Endring:SQLExecDirect(SELECT [id], CAST([endretFra] as NVARCHAR(8000)) as [endretFra], CAST([endretTil] as NVARCHAR(8000)) as [endretTil], CAST([endringskilde] as NVARCHAR(255)) as [endringskilde], CAST([sistEndretBruker] as NVARCHAR(255)) as [sistEndretBruker], CAST([sistEndretFulltNavn] as NVARCHAR(255)) as [sistEndretFulltNavn], [sistEndretTidspunkt], CAST([felt] as NVARCHAR(255)) as [felt], CAST([handling] as NVARCHAR(255)) as [handling], [aliste_id], [utvalg_id] FROM [gruppergister].[dbo].[Endring]):
  42000:1131:[Microsoft][ODBC SQL Server Driver][SQL Server]
  The size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000).

It occurs during the "Bulk Data Transfer"

You are trying to execute this statement on the SQL Server side:

SELECT  [id],
        CAST([endretFra] AS NVARCHAR(8000)) AS [endretFra],
        CAST([endretTil] AS NVARCHAR(8000)) AS [endretTil],
        CAST([endringskilde] AS NVARCHAR(255)) AS [endringskilde],
        CAST([sistEndretBruker] AS NVARCHAR(255)) AS [sistEndretBruker],
        CAST([sistEndretFulltNavn] AS NVARCHAR(255)) AS [sistEndretFulltNavn],
        [sistEndretTidspunkt],
        CAST([felt] AS NVARCHAR(255)) AS [felt],
        CAST([handling] AS NVARCHAR(255)) AS [handling],
        [aliste_id],
        [utvalg_id]
FROM    [gruppergister].[dbo].[Endring]

That statement tries to convert two columns to the NVARCHAR data type with a length of 8000. However,the maximum allowed length of NVARCHAR in SQL Server is 4000. So, whatever tool you are using is producing invalid SQL Server syntax.

As a side note, if you are trying to create a development environment for a SQL Server hosted database, you probably should use SQL Server. The syntax for some statements is quite different and using the from DBMS can only cause confusion and grief.

You can install a trial version of SQL Server for free, if your project is less than 180 days, or you can buy the developer edition for about $50 (e.g. here). If your database is less than 4GB and does not use advanced features, you could also go with the free SQL Server Express Edition.


How to know the user doing a delete action on an audit table, when using a shared login? Background information:

I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app.
Audit records are created via triggers.
DML in my app's database will generally come from a login that a service uses to get into the database. Because of this, I think the result from SYSTEM_USER will always be the same when called in a trigger.
My app does not store user data currently, though a string UserId is given to it each time DML is to done (done exclusively in stored procedures).

The problem I ran into is that when a user deletes a record, I want to know who did it. Because it will done by the same login, I don't want to see that all action were done by service, I want to see which user did it. This isn't an issue on an update, because we have ModifiedBy columns that will be updated via a sent in UserId on updates.

The question is: Is there a way to set the SYSTEM_USER or otherwise get the user information into the trigger when a delete is run?

The "best" idea I have right now, though I'm not sure if it is a good idea yet, is that in the service I check to see if the current UserId is in the database as a user, and if not create a user object for them. Then run stored procedures with EXECUTE AS User = @UserId. Then when DML is done in the stored procedure and the trigger fires, SYSTEM_USER should return the user from the EXECUTE AS.

While using EXECUTE AS User = @UserId may be your best option (depending on other issues), here is an alternative approach:

In you stored procedures, or at anytime in your SQL session before you do the DELETE execute the following command:

SET CONTEXT_INFO @UserId

Then in your Trigger you can retrieve this value with

SELECT @var = CAST(CAST(CONTEXT_INFO() As Varbinary(4)) As Int)

This has some disadvantages, the most important of which is that you cannot readily use CONTEXT_INFO for more than one thing at a time.


create table script with constraints in sql server 2008 I have create table scripts and alter table scripts with some constraints  for that database.

It take time as twice to create the table and alter the table .. Is this possible to combine

create table and alter table constraints with create table script..in sql server 2008

Here's an example of creating a constraint at the same time as a table:

create table Table1
(
multiplier1 int,
multiplier2 int,
answer int,
constraint PK_Table1 primary key(multiplier1, multiplier2),
constraint CHK_Table1_Answer check (answer = multiplier1 * multiplier2),
check (answer / multiplier1 = multiplier2)
)

Per @Martin Smith's comment, the constraint name is optional.  The database will generate a name for the third constraint, like CK__Table1__22AA2996.

Constraints that are introduced on their own line can can refer to multiple columns.


Service Broker and procedure which contains Execute as We have queue on Service Broker:

ALTER QUEUE [dbo].[my_queue] WITH
        STATUS = ON ,
        RETENTION = OFF ,
        ACTIVATION (
                      STATUS = ON ,
                      PROCEDURE_NAME = [dbo].[my_detect_event] ,
                      MAX_QUEUE_READERS = 1 ,
                      EXECUTE AS N'dbo'
                    ),
        POISON_MESSAGE_HANDLING (STATUS = OFF)

Procedure has code (i cut some text with RECEIVE, because it's not linked to the issue):

ALTER PROCEDURE dbo.my_detect_event
        AS
            BEGIN
                ......cut text with RECEIVE TOP (1) message_body FROM my_queue INTO @NotificationStore
                --EXECUTE AS LOGIN = 'user_sa'
                INSERT INTO dbo.log
                        ( time, text)
                VALUES  ( GETDATE(),
                          'test'
                          )
                ......
            END

user_sa - has SA rights.
If the service fires an event, then the table data is inserted successfully.
But if you uncomment the line EXECUTE AS LOGIN = 'user_sa', then appears the error:

  Cannot execute as the server principal because the principal "user_sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Pls help to find solutions.
Thanks in advance!

Technical info:
Microsoft SQL Server 2012 - 11.0.5582.0 (X64)  Enterprise Edition

Answer is here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95f5ee43-d3b2-4a2d-bdd2-6fb0aed56458/service-broker-and-procedure-which-contains-execute-as?forum=sqlservicebroker&prof=required
In short: I perform in the context of the current database and treated in a nearby base. And it requires action from the links above.


Unexplained sustained space used in the TempDB log file A few days ago the amount of used space in both the tempdb data and log files started growing rapidly.  After 30 minutes, the tempdb log remained around 99% space used for about 7 hours.  Towards the end of the 7 hour period, I:\Databases\templog.ldf: Operating system error 112(There is not enough space on the disk.) encountered. was logged followed by about 30 instances of The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' over a 3 second period, and then suddenly the tempdb log file returned to 1% used space.  This usage data is recorded in logs every 3 minutes via a SQL agent job.
I also have a SQL agent job that runs sp_WhoIsActive every 1 minute and captures the output.  I don't see anything that stands out in this data that explains what was consuming or holding space in the tempdb.  There are some maintenance jobs running for several minutes at a time, typically running no longer than 5 or 10 minutes each.  Even if one of those jobs or another session used up tempdb log space, I would expect the space to be reclaimable after any transactions complete or at least after the session ends.

It's a 200 GB drive dedicated for tempdb data and log files.
Both the data and log files grew during this time until there was no more space on disk.
There's a tempdb_2.ndf file I don't recall seeing before (maybe it's always been there...).  tempdb.mdf and tempdb_2.ndf are the same exact size, around 29 GBs each.  templog.ldf is using 137 GBs.
Running Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)

I'm trying to understand what happened and how to prevent it again.

Is it possible for tempdb log space to not be reclaimable for hours even if there are no sessions running for anywhere near that long?
What caused tempdb log space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and tempdb transaction log is full)?
Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do?
If the tempdb log space used was actually reclaimable but a glitch in SQL Server prevented it from clearing out, is there a T-SQL command I can run to try and free up available space in the log?

Any thoughts or suggestions are very appreciated.

Is it possible for tempdb log space to not be reclaimable for hours even if there are no sessions running for anywhere near that long?

While it's probably possible, I would say under normal circumstances the answer is no.  In the absence of long-running transactions or long-lived sessions, the tempdb log should clear pretty quickly.

What caused tempdb log space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and tempdb transaction log is full)?

The most likely explanation is that there was a long-running transaction.  It finally encountered this error and was killed, which allowed the log space to free up.

Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do?

I would expect that to capture it.  One thing that might have caused you to miss it is if the process had executed statements in a transaction and then just stopped doing anything (without committing, rolling back, or disconnecting).
This would be the case if someone ran a query in SSMS that used a ton of tempdb, and then just left that SSMS window open all day.  These queries (that are holding resources, but not doing anything actively) won't show up in sp_WhoIsActive by default.  You need to add the @show_sleeping_spids = 2 option to see those.  Here's an excerpt from the built-in docs:

If the tempdb log space used was actually reclaimable but a glitch in SQL Server prevented it from clearing out, is there a T-SQL command I can run to try and free up available space in the log?

Not that I can think of.  You might try issuing a CHECKPOINT, to force the VLFs to clear:
USE [tempdb];
GO
CHECKPOINT;


How to determine MAXDOP setting when using DTU in Azure SQL I have previous experience of setting MAXDOP settings for on premise OLTP & OLAP servers when you know the number of cores at your disposal. e.g. if I knew I had 16 at my disposal I'd set my MAXDOP to 2 or 4 for my OLTP system.

I just joined a project and noted the core production database is set to MAXDOP of 1, prohibiting any parallel operations.

It is a dedicated server (so no elastic pool) Premium P11 server so DTUs abstract the exact amount of CPU, memory capability. Without being able to tell how many cores you are dealing with, is there a sensible starting config? finding it a little tricky to port on prem best practices to Azure SQL.

The test servers are a different config with a shared elastic pool, so no easy way to test specific execution plans there either (especially as everything is written through Entity framework ORM)

DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O…. This means that there is no definitive measure of equating x DTU = x CPU, X Memory etc.

Since, you have Premium P11 tier - this means that you have 1750 DTUs. Based on this blog post - By Andy Mallon, below is the CPU-to-DTU-to-Service Tier mapping :

+--------------+------+---------------+
| Number Cores | DTUs | Service Tier  |
+--------------+------+---------------+
| 1            |  100 | Standard – S3 |
| 2-4          |  500 | Premium – P4  |
| 5-8          | 1000 | Premium – P6  |
| 9-13         | 1750 | Premium – P11 |
| 14-16        | 4000 | Premium – P15 |
+--------------+------+---------------+

You should use Query Performance Insight for Azure SQL Database to review and adjust the db workload settings. Since you have db scoped configuration as non default (0 is default) - set to 1, you will need to review it and potentially change it so that you can utilize all the cores.


Enabling Service Broker on a database involved in an availability group I'm trying to enable Service Broker on a SQL Server 2012 database that is part of an availability group and keep getting this error message:

  The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1468)

Even attempting to enable Trustworthy on the database generates the same error.  According to this article, it seems that Service Broker could be enabled on databases configured for AG.  What could I be missing?

The listener has been configured, endpoints created with public permission, targets reference the listener name.  The error occurs when I try to enable service broker or set Trustworthy via management console or T-SQL.

I wanted to add that the database is a restore from a SQL Server 2005 version with a existing Service Broker implementation.

I wanted to add that the database is a restore from a SQL Server 2005

When you restore a database the Service Broker in it gets disabled and must be explicitly enabled (there are reasons why this is necessary, but I won't go into them). If you restored the DB and then set up the AG w/o enabling broker first, you have set up the AG w/o the Service Broker enabled and now you cannot run the ALTER DATABASE SET ENABLE_BROKER.  Unfortunately to set it up correctly you must enable broker before adding it to the AG, which means you must tear apart the AG, enable broker, then set set up the AG again.


Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2 I am noticing different behavior in mariadb 10.1 & 10.2 reg.connection aborts.
I noticed connection getting aborted after 7 minutes(wait_timeout) of any query execution. There are few random connection aborts happening in my application too. But I don't see this behavior(connection abort logs) happening in mariadb 10.1.
I am aware that the default value for variable "log_warnings" has been changed from 1 to 2 in MariaDB 10.2. But even with log_warnings value as 2 in MariaDB 10.1, I don't see connection aborts happening.
For eg. In below query connection Id : 2218 is created
MariaDB [dummy]> show global variables like '%log%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2218
Current database: dummy

From general query logs, the query executed time is 11:53:15
201222 11:53:15     6 Query     SHOW MASTER STATUS
                    6 Query     SHOW SLAVE STATUS
                    6 Query     UPDATE `oam`.`heartbeat` SET ts='2020-12-22T11:53:15.000830', file='mariadb-bin.000001', position='58352014', relay_master_log_file=NULL, exec_master_log_pos=NULL WHERE server_id='1'
                 2218 Connect   root@localhost as anonymous on dummy
                 2218 Query     show databases
                 2218 Query     show tables
                 2218 Field List        mytable
                 2218 Query     show global variables like '%wait%'

From Error logs, the connection closed at 12:00:16
2020-12-22 12:00:16 140673402672896 [Warning] Aborted connection 2218 to db: 'dummy' user: 'root' host: 'localhost' (Got timeout reading communication packets)

wait_timeout is set to 7mins(420s)
MariaDB [dummy]> show global variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 420   |
+---------------+-------+
1 row in set (0.01 sec)

I could see the same behavior happening for any query execution (Connection aborting after 7mins) in MariaDB 10.2 but it is not happening in 10.1 even when the log_warnings level set to 2
Am I missing any configuration in 10.1 to observe these logs? or Any behavioral change that happened in 10.2 regarding this?
--EDIT--
We have the same wait_timeout configured in 10.1 as well (420s). In fact, we have kept most variable's values as same which might be the reason for the error as per the link https://dev.mysql.com/doc/refman/5.6/en/communication-errors.html.
| max_allowed_packet  | 33554432   |
| max_connections       | 516   |
| thread_cache_size      | 256   |
| interactive_timeout    | 420   |

According to the general consensus that answers shouldn't be posted as comments, following my short answer.
You have set a timeout of 7 minutes and your database instance is aborting the connection after 7 minutes. Works as designed.
General information is logged in the General query log and contains:

Established client connections and statements received from clients

The Error log contains:

Problems encountered starting, running, or stopping mysqld

Reference: 5.4 MySQL Server Logs (MySQL Documentation)
So if your 10.1 installation is not displaying any errors, then you possibly have a larger timeout value set.


SQL Server 2012 Support on Windows Server 2008 R2 Is it possible to install Microsoft SQL Server 2012 on Windows Server 2008 R2 Datacenter?

On the Windows Server 2008 R2 Datacenter - I have .Net Framework 3.5.1 Features(.Net Framework 3.5.1) and I even manually installed .Net Framework 4.

  The operating system on this computer does not meet the minimum requirements for SQL Server 2012. For Windows Vista or Windows Server 2008 operating systems, Service pack 2 or later is required. For windows 7 or windows server 2008 R2, Service pack 1 or later is required. For more information, see Hardware and software requirements for installing SQL Server 2012 at: http://go.microsoft.com/fwlink/?LinkID=195092

I tried to install Microsoft SQL Server 2012 Service Pack 1 & 2?

Yes, you can install SQL Server 2012 on Windows Server 2008 R2 (the matrix here - which is exactly where the link in your screenshot goes, if you had clicked it - shows the supported edition/OS combinations).

The error message you posted tells you exactly what you need to do without even going to that page: Apply Service Pack 1 to the operating system.

(Though - especially if setting up a new system - you should consider a more modern operating system, and a newer SQL Server version for that matter.)


No privileges on tablespace 'USERS' I am creating to the user after create table, but not insert the data why?
Then display the message NO PRIVILEGES ON TABLESPACE 'USERS'

The cause of the error is SEGMENT CREATION DEFERRED.

This feature of Oracle was introduced ~11gR2.  It allows large applications to CREATE TABLE for everything in the application without using disk space.  The application will use 0 bytes for the tables that the customer won't use. (eg they aren't licensed for the feature(s) that uses those tables)

This feature has the side-effect of allowing you to CREATE TABLE on a tablespace which you don't have a quota.

to fix

Your CREATE USER needs to include a QUOTA clause in order to add data to that tablespace.  You could easily ALTER USER to add a quota.

There is also the over powering UNLIMITED TABLESPACE system privilege.  It grants the user QUOTA UNLIMITED on all tablespaces (including SYSTEM and SYSAUX -- which you shouldn't use).  This privilege seems to go against the security concept of "least privileges".  Use it sparingly.


Postgresql and UTF8 to Latin1 conversion? We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like:

SELECT symptoms from client
- -
"huvudvÃ¤rke"
- -

where "Ã¤" should be "ä". I could handle it with replace, ie.:

UPDATE client SET symptoms=replace(symptoms, 'Ã¤', 'ä');

I tried a couple of solutions I found, like using convert and convert_to but that leaves the data looking like "huvudv\303\244rke".

Is there an existing solution to my problem?

EDIT:

SELECT symptoms, symptoms::bytea from client
- -
"huvudvÃ¤rke";"huvudv\303\203\302\244rke"
- -

After the UPDATE above, query with symptoms::bytea outputs:

SELECT symptoms, symptoms::bytea from client
- -
"huvudvärke";"huvudv\303\244rke"
- -

EDIT2:

select version();
"PostgreSQL 9.2.9, compiled by Visual C++ build 1600, 64-bit"

show client_encoding; --I'm using pgAdmin III 1.18.1
"UNICODE"

show server_encoding;
"UTF8"

SELECT symptoms, convert(convert_to(symptoms, 'utf-8'), 'latin-1', 'utf-8')::text from client;
"huvudvÃ¤rke","huvudv\303\203\302\203\303\202\302\244rke"

It looks like whatever client you are using is confused about the text encoding; it's sending utf-8 bytes as if they were latin-1, probably.

Check:

SHOW client_encoding;
SHOW server_encoding;
locale command in your terminal, if using psql

Your update is substituting the octal bytes \303\244 which are the utf-8 encoding for "ä" (U+00E4). You're not substituting latin-1 encoded data where you think you are.

Observe:

regress=> SELECT convert_from(BYTEA 'huvudv\303\244rke', 'latin-1');
 convert_from
--------------
 huvudvÃ¤rke
(1 row)

regress=> SELECT convert_from(BYTEA 'huvudv\303\244rke', 'utf-8');
 convert_from
--------------
 huvudvärke
(1 row)

Not only that, but your replace could only have matched in the first place if the replace target was the utf-8 encoded byte sequence for Ã¤ interpreted as latin-1, i.e. \303\203\302\244.

It's hard to be more specific without details about the Pg version, the client being used, etc, but the root cause is clearly your client doing something totally borked with encodings on I/O.

Your original text is totally mangled, it's not valid in UTF-8 or latin-1. It looks like someone's taken some UTF-8 data, decoded it as latin-1, and then encoded it as utf-8 again.

Yep, sure enough:

regress=> SELECT convert(convert_to('huvudvärke', 'utf-8'), 'latin-1', 'utf-8');
          convert
---------------------------
 huvudv\303\203\302\244rke
(1 row)

there's your explanation.

I'd say you probably have a bunch of mis-encoded data in the DB already, and you noticed this one because it got mangled twice. You're probably doing something like routinely jamming utf-8 bytes into latin-1 encoded fields, but you usually get away with it because you decode them as utf-8 again - and this time you did something different.

If you want to get from your mangled text to the original, you simply have to reverse the incorrect encoding process. Decode the utf-8 and output latin-1, then re-interpet the latin-1 as utf-8 and decode again, e.g:

regress=> SELECT  convert_from(convert(BYTEA 'huvudv\303\203\302\244rke', 'utf-8', 'latin-1'), 'utf-8');
 convert_from
--------------
 huvudvärke
(1 row)


CMEMTHREAD waits during Index Optimisation in Availability Group In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation:

Troubleshoot: Changes on the Primary Replica are not Reflected on the Secondary Replica

In my experience, the issues that I see most often appear to be:

  A long-running transaction on the primary replica prevents the updates from being read on the secondary replica.

and

  The redo thread on the secondary replica is blocked from making data definition language (DDL) changes by a long-running read-only query. The redo thread must be unblocked before it can make further updates available for read workload.

I can observe this by looking at the Extended Events session "AlwayOn Health":

When an application issues read-only queries to the secondary replica, if a heavily logged operation (like Index Optimisation) is running on the primary, sync lag becomes pronounced and I see a huge backlog in uncommitted log records on the secondary, as is described in the above MS docs.

The question I have is why I see CMEMTHREAD waits on the secondary replica when Index Reorganisation is taking place on the primary:

Is this normal/expected behaviour or something else?

Whilst there is some read activity on the secondary replica, those queries are most often <1 second in runtime, with the occasional <10 second query. CPU usage around 5%.

Output of @@VERSION: Microsoft SQL Server 2016 (SP1-CU10-GDR) (KB4293808) -
13.0.4522.0 (X64)   Jul 17 2018 22:41:29   Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

CMEMTHREAD wait is only observed on the secondary replica
The replica that shows this wait (and the lag) is an actively queried synchronous replica

Update:
I just spotted this WAIT occurring again during index optimisation. I killed the index job and that then obviously stopped the sync lag from increasing, however the CMEMTHREAD wait continued and redo seemed quite slow. I also noticed occasioinal PARALLEL_REDO_FLOW_CONTROL waits on the redo thread, so I simply executed DBCC TRACEON (3459, -1) and suddenly redo speed increased and the backlog started to clear extremely quickly.

You can see I stopped index optimisation at 1:20pm and applied the trace flag at 1:45pm. Note that the SQLSentryOne wait graph is in UTC whilst the latter graph is in BST.

Update

I have just observed this exact behaviour again on an ASYNC replica with nothing running on it. The same trace flag resolved this again. I'm surprised as I had thought this was caused by read-only queries causing contention on SYNC replicas with heavily-logged operations (like index maintenance) occuring on the primary. On this occasion, we have index maintenance on the primary, a SYNC replica with no redo issue but an ASYNC replica with this issue. Here you can see the WAITstats showing CMEMTHREAD and the point when the trace flag was enabled, the CMEMTHREAD wait is gone and the redo contention is resolved.

I don't have access to an Availability Group secondary that's really cooking :-)
but i can share some things that'll get you a little farther.
When you have parallel redo on the secondary, activity like redo of heavy index maintenance is accompanied by CMEMTHREAD waits.  These waits disappear when Trace Flag 3459 is globally enabled.  And you are wondering if the CMEMTHREAD wait behavior is due to a bug.
It is unlikely the CMEMTHREAD wait behavior is due to a bug.
The post linked below is a few years old but still my favorite in explaining the intent of CMEMTHREAD waits as memory object serialization control.  Bob explains those memory objects with access potentially controlled by CMEMTHREAD waits may be global, partitioned per NUMA node, or partitioned per CPU.
How It Works: CMemThread and Debugging Them
Bob Dorr & Rohit Nayak
2012 December 20
https://techcommunity.microsoft.com/t5/sql-server-support/how-it-works-cmemthread-and-debugging-them/ba-p/317488
One of the important enhancements in SQL Server 2016 was the introduction of dynamic memory object partitioning.  Prior to SQL Server 2016, as contention was observed to be a significant issue on individual global memory objects, individual trace flags would enable partitioning that object by NUMA node(trace flag 1236 is an example).  And trace flag 8048 would promote many of the memory objects partitioned at the NUMA node level to being partitioned at the CPU level.
In SQL Server 2016, many of the memory objects became eligible for dynamic object partitioning: they'd start with a single global partition but as contention was detected they'd be promoted to NUMA node partitioned.  If further contention is detected, the object would be promoted again to CPU partitioned.
More about that in this blog post.
Dynamic Memory Object Scaling
Ajay Jagannathan
2016 June 27
https://techcommunity.microsoft.com/t5/sql-server/dynamic-memory-object-scaling/ba-p/384755
So while parallel redo activity is seeing high CMEMTHREAD waits, watching the results of the query below over time will reveal which memory objects are under contention. I think its likely to be a global object that is not eligible for dynamic partitioning.
SELECT omo.type, memory_node_id, omo.pages_in_bytes, omo.partition_type, omo.partition_type_desc,
omo.waiting_tasks_count, omo.exclusive_access_count, omo.contention_factor
FROM sys.dm_os_memory_objects omo
WHERE omo.waiting_tasks_count > 0
ORDER BY omo.waiting_tasks_count DESC;

So, enabling global trace flag 3459 mitigates the CMEMTHREAD wait time.  In this case, that's to be expected.  Global trace flag 3459 disables parallel redo, leaving the AG secondary with serial redo.  With a single redo thread for the Availability Group secondary redo activity, the contention for the memory object that was present with parallel redo is suddenly gone.
Whichever memory object is under contention in this scenario, it may be possible for enhancement to allow it to be dynamically promoted as many other objects are, which would alleviate the CMEMTHREAD waits even with parallel redo.  But some work is harder or more expensive to partition than to accomplish as a single global object.


How does one connect to a schema within a PDB (other than schema SYS)? Using Oracle 18c on RedHat Linux 8

Connecting to CDB as 'SYS', I cannot figure out how to connect to a schema within the PDB other than as 'SYS'.  Maybe I don't understand the CDB/PDB architecture well enough.

Here is what I have tried after logging into the Oracle 18c CDB as 'SYS' (using sqlplus sys as sysdba):

ALTER SESSION SET CONTAINER= PDB;
conn myschema/mypasswd

and I also tried (right from the CDB)

conn myschema/mypasswd@PDB;

Both approaches fail to get me connected to the schema myschema in the PDB.  That is, I am still 'SYS' in the PDB.

Are there any suggestions/explanations on how to resolve this?
Do I have to setup some kind of access from the CDB-to-PDB for the schema myschema?

Turns out there was a typo error in the tnsnames.ora file - the HOST ip address field was invalid for the system in question.
Example below (values have been anonymized for security reasons)

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.11)(PORT = 1555))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB)
    )
  )

The file was probably copied from another server (as a template) and the HOST ip never updated.

Given the above, the correct way to connect would be:

conn myschema/mypasswd@PDB;


optimise join query with group by SUM I have 2 tables:
sales transaction tablem payment (reference_no) can be duplicate

-----------------------------------
| id | reference_no | invoice_amt |
| 1  | inv001       | 100.00      |
| 2  | inv001       | 30.00       |
| 3  | inv002       | 150.00      |
| 4  | inv003       | 50.00       |

payment tablem (reference_no) also  can be duplicate.

-----------------------------------
| id | reference_no | payment_amt |
| 1  | inv001       | 130.00      |
| 2  | inv002       | 30.00       |
| 3  | inv002       | 50.00       |
| 4  | inv002       | 50.00       |
| 5  | inv002       | 20.00       |
| 6  | inv003       | 20.00       |

I want to match so those payment_amt not tally with invoice_amount will appear.
Example:

inv001 total sum invoice_amt is 130 and payment_amt is 130

inv002 total sum invoice_amt is 150 and payment_amt is 150

inv003 total sum invoice_amt is  50 and payment_amt is 20

So it will only display inv003 since the SUM of the invoice amount is not same with total payment_amt.

How to write the sql for this display?

I'm answering from a SQL Server perspective; however, something similar should work in most environments.

/* SET-UP  */

CREATE TABLE #inv (id INT IDENTITY(1,1), refno varchar(20), inv_amt money);
INSERT INTO #inv (refno, inv_amt)
VALUES ('inv001', 100)
      ,('inv001', 30)
      ,('inv002', 150)
      ,('inv003', 50)
;

CREATE TABLE #pay (id INT IDENTITY(1,1), refno varchar(20), pay_amt money);
INSERT INTO #pay (refno, pay_amt)
VALUES ('inv001', 130)
      ,('inv002', 30)
      ,('inv002', 50)
      ,('inv002', 50)
      ,('inv002', 20)
      ,('inv003', 20)
;

/* ANSWER */

CREATE TABLE #inv_total (refno varchar(20) PRIMARY KEY, inv_amt money);
CREATE TABLE #pay_total (refno varchar(20) PRIMARY KEY, pay_amt money);

INSERT INTO #inv_total
SELECT refno, SUM(inv_amt) FROM #inv GROUP BY refno;

INSERT INTO #pay_total
SELECT refno, SUM(pay_amt) from #pay GROUP BY refno;

SELECT COALESCE(i.refno, p.refno) as refno
      ,COALESCE(i.inv_amt, 0) as inv_amt
      ,COALESCE(p.pay_amt, 0) as pay_amt
  FROM #inv_total i
         FULL OUTER JOIN #pay_total p ON (i.refno = p.refno)
 WHERE COALESCE(i.inv_amt, 0) <> COALESCE(p.pay_amt, 0)
;

Executed on SQL Server 2008R2, this does indeed only bring back inv003.

NOTE: Depending on what version of SQL you're working in, you may be able to use the queries that populate the two _total temporary tables as sub-queries in the final query.

If you do need the temp tables, setting refno as a primary key isn't required; however, for very large amounts of data, it might speed up the final query.

ALSO: In my test data, I assumed that the two payment rows with the same id value was a typo. However, it's not relevant to the solution as far as I can tell, so if it was deliberate, change the set-up to not make the id column an IDENTITY column, and to explicitly populate that column in the INSERT statements.


How to manually set Code Page and Collation for my SSAS Cube? When I try to Process my SSAS2012 Olap Cube I get a lot of erros like this

Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing: Table: 'dbo_ART'
,Column: 'NAZIV'
,Value: 'ARMATURA ZA KERAMIĆKI VODOKOTLIĆ'.
The attribute is 'Art Naziv'.
Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate.
Attribute: Art Naziv of Dimension: ART from Database: robot, Record: 2451.

When I investigate this error I found that at my transactional database at table ART exists two totally different rows with whit value of column NAZIV ='ARMATURA ZA KERAMIĆKI VODOKOTLIĆ' only differences from this two records is that one do not have letters with 'Ć' and 'Č' insead on that placed is letter 'C'.

If I do

select naziv
from art
group by naziv
having count(*)>1

I do not get any duplicated rows.
Because some how transactional database know difference between letters 'Č','Ć','C'.
But OLAP cube when Processing Dimension ART reading all this letters as "C" and getting duplicate keys.
My transactional Database is at SQL 2012 R2 standard server with Collation =Croatian_CI_AS server is generally set to Latin1_General_CI_AI.
My SSAS 2012 Enterprise server have Windows Collation set to Latin1_General
and all Sensitive is Unchecked.

How I should set my language settings for SSAS server to avoid this error.
Is that "accent sensitive" setting.
Or I need to change my Windows Collation to something else ?

You have to change the collation settings either in your queries or in SSAS. There are several different options so the important question is do you use SSAS for other databases than the Croatian_CI_AS one.

If so you can pepper all your queries with collate Latin1_General_CI_AS to make sure that you get the data in the Latin1 collation into the cubes. If you are only using SSAS for the Croatian data, then change the SSAS collation to the same.


mysql workbench can't write to sql history file I'm running mysql workbench on my local machine, connected to a remote sql server.

I am able to apply changes to the database, however, I see this error every time I apply a change

I can't seem to find any help online regarding this error, so I can't provide any more details

Error: There was an error while applying the SQL script to the database.

and in the message log
ERROR: Can't write to SQL history file

The sql_history directory under ~/.mysql/workbench was owned by root. I'm not sure how that happened in a default install.

However, running sudo chown -R <username>:<group> ~/.mysql/workbench/sql_history did the trick


Database Files and Filegroups In my case wherein I have a file file_01.mdf and file_02.ndf under 1 filegroup, if ever file_01.mdf was already full (no autogrowth enabled), will it give me error on file_01.mdf if data is attempted to be added in it?

No, you won't get an error until both datafile are full.. The error is thrown when there is no more space in the filegroup (not for every file in it).

You can test it pretty easily by creating a database with 2 data file (one with the auto-growth enable) and inserting data in it until it fills the first data file.

You will see the the first file fills up to 100% and you won't get any error.
If you disable the auto-growth on the second file and continue inserting, we will get an error when both file will be filled.

Thanks.


Error Updating Record on Table with Partition on a Read-Only Filegroup We are working to partition our data warehouse (SQL Server 2012 Enterprise): moving old records we don't expect to change to a read-only filegroup on a monthly basis. We're getting errors that records cannot be updated even when the record is in the read-write filegroup and would not move to the read-only partition (we are not updating the partitioning column) due to the update.

The partitioning column is ETL_VERS_END_DTM. The following update statement fails as if the row needed to be moved to partition 1 (values less than 1899-12-31 23:59:59 which is expected to be an empty partition).

UPDATE  DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT
SET     ETL_JOB_SEQ_NUM = 1027140 ,
        ETL_IUD_CD = 'D' ,
        ETL_UPD_DTM = CONVERT(DATETIME, '03/16/2016 01:01:17')
WHERE   ETL_VERS_START_DTM = CONVERT(DATETIME, '12/27/2014 00:26:24.137')
        AND ACCT_NO = '5001194157'
        AND ACCT_TYPE = 'ILN';

Error:

  Msg 652, Level 16, State 1, Line 1 The index
  "PK_STG_PHX_LN_ACCT_INT_OPT" for table "dbo.STG_PHX_LN_ACCT_INT_OPT"
  (RowsetId 72057594415022080) resides on a read-only filegroup
  ("ARCHIVE"), which cannot be modified.

Explicitly setting the partitioned column to its own existing value or putting anything in the where clause that gets us the correct partition elimination removes the condition that causes this error. Neither solution is considered a 'fix' for the issue by the vendor or our data warehouse admin. What is causing SQL Server to attempt to move the record if the column value is not changing?

UPDATE:
So my problem is me misunderstanding that if the execution plan thinks it might touch the read-only partition it will error out (even though no records have been touched). The problem ultimately is that the database was not built originally (or loaded) with a partitioning as a consideration as well as lack of desire to customize update processes by our DW team. Partitioning is being built in to manage maintenance times as the database continues to grow due to a business requirement to never delete anything ever. [Did that sound a little bitter?]

Ordinarily, SQL Server only checks for a read-only filegroup just before attempting to write to the partition. No error is thrown when reading from a read-only filegroup, as you would expect.
SQL Server can therefore touch any rows it likes, so long as only rows located on read-write filegroups qualify for the update. To put it another way: SQL Server can fully scan all partitions (e.g. using a Clustered Index Scan) looking for rows to update, so long as the only rows that arrive at the Clustered Index Update are on read-write partitions.
In this scenario, there are two separate storage engine rowsets involved: one for the read, and one for the update. The reading rowset will not throw an error if it encounters a read-only partition because it is only reading. The writing rowset will throw an error, because it is configured to perform changes.
The catch
In execution plan shapes where there is no blocking operator between reads and writes to the base table, SQL Server may apply an optimization ("Rowset Sharing"), whereby the two operators share a single storage engine rowset.
In this situation, reading from a read-only partition will throw the error reported in the question. The single rowset is configured for both read and write - touching a read-only partition results in the error.
Adding a key from the unique clustered index to the set clause means a split, sort, collapse combination is required to avoid transient key violations. As a side-effect, the sort (a blocking operator) prevents the rowset sharing optimization from being applied.
For more details, see my article Changes to a Writable Partition May Fail Unexpectedly


Different MySQL Datafile Sizes After Restoration I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86.

Some weeks ago, we upgraded the company's main app. This upgrade added a column to all the tables of its database. Before the upgrade, the size of our ibdata was ~3.1GiB. After the upgrade, it is ~5.2GiB.

Today, I've done a full backup-restoration test with mysqldump. After restoration, ibdata size is ~3.6 GiB. Standard selects show the same data in restoration as in original database.

I've checked the data size through a select to information_schema (CUSTODIA is the name of our main app):

select SUM(DATA_LENGTH+INDEX_LENGTH) from TABLES where TABLE_SCHEMA='CUSTODIA';

this is the result in master database:

+-------------------------------+
| SUM(DATA_LENGTH+INDEX_LENGTH) |
+-------------------------------+
|                    5683345068 |
+-------------------------------+

this is the result in restored database:

+-------------------------------+
| SUM(DATA_LENGTH+INDEX_LENGTH) |
+-------------------------------+
|                    3735748608 |
+-------------------------------+

My questions:

Why is there this difference between original and restored database size?
Is it safe to asume that restored database is OK, although this difference in size?
How does MySQL calculate data_length? Is it an estimate?
Can I safely reduce production's ibdata file size to 3.6GiB with no down-time?

Any thoughts about this will be kindly appreciated. Thank you.

This makes all the sense in the world to me.

InnoDB creates data pages and index pages that are 16K each. If rows of data are being inserted, updated, deleted, committed, and rolled back, you are going to have FRAGMENTATION !!!

There are two cases where you can have internal fragmentation:

A single row could be written in multiple data pages because certain column values make a row too big to fit in the data page.
Having a TEXT column with 32K of data in it.

In those two cases, a single row spanning multiple data pages would have to be chained like a linked list. The internally generated list of data pages would always have to be navigated when the row is read.

Giving credit where credit is due, PostgreSQL implemented a very brilliant mechanism called TOAST (The Oversized-Attribute Storage Technique) to keep oversized data outside of tables to stem the tide of this kind of internal fragmentation.

Have used mysqldump to make a file with CREATE TABLE statements, followed by lots of INSERTs, you get a fresh table with no unused space along with contiguous data and index pages when loading the mysqldump into a new server.

For my explanantions, let's assume you have an InnoDB table in the CUSTODIA database called userinfo

If you would like to compress a table, you have three(3) options

Option 1

OPTIMIZE TABLE CUSTODIA.userinfo;

Option 2

ALTER TABLE CUSTODIA.userinfo ENGINE=InnoDB;

Option 3

CREATE TABLE CUSTODIA.userinfo2 LIKE CUSTODIA.userinfo;
INSERT INTO CUSTODIA.userinfo2 SELECT * FROM CUSTODIA.userinfo;
DROP TABLE CUSTODIA.userinfo;
ALTER TABLE CUSTODIA.userinfo2 RENAME CUSTODIA.userinfo;

CAVEAT : Option 3 is no good on a table with constraints. Option 3 is perfect for MyISAM.

Now for your questions:

Question 1. Why is there this difference between original and restored database size?

As explained above

Question 2. Is it safe to assume that restored database is OK, although this difference in size?

If you want to make absolutely sure that the data on both servers are identical, simply run this command on both DB servers:

CHECKSUM TABLE CUSTODIA.userinfo;

Hopefully, the checksum value is identical for the same table on both servers. If you have dozens, even hundreds, of tables, you may have to script it.

Question 3 : How does MySQL calculate data_length? Is it an estimate?

You are using the correct method in summing up the data_length and index_length. Based on my explanation of fragmentation, it is an estimate.

Question 4. Can I safely reduce production's ibdata file size to 3.6GiB with no down-time?

GOOD NEWS !!! You absolutely can compress it !!! In fact, how would like to compress it to a fraction of that number ??? Follow these two links because I addressed this issue in StackOverflow and ServerFault.

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400

BAD NEWS !!! Sorry, but you will have a 3-5 minute window of downtime for rebuilding ib_logfile0 and ib_logfile1 as well shrinking ibdata1 once and for all. It's well worth it since it will be a one-time operation.


Report model data source Does anybody know how i could use a linked server as a data source for a report model in SQL server 2008 r2? or how i could make this linked server a data source on a report model? Please help. Thank you.

Why not just put the datapull for the linked data source in a stored procedure and then access it from a "local" database?


Creating rows in a table based on columns from another I have an MS SQL 2012 DB with a table in the following format:

account_id   site_id   IRA   RFI   XSS   SQLi   BDP   Month
32501        112233    1     4     4     3      0     August,2016
32501        112233    5     2     6     1      0     September,2016
32501        110025    3     1     5     3      0     September,2016
32501        112233    4     5     2     7      0     October,2016

The table may have multiple lines per account (as the number of sites it contains) as well as multiple accounts. At most each site may have 3 lines, one for each month in a 3-months period.

I'm looking for a way to create a table that is based on the one above but in the following format:

AccountID    SiteID    ThreatName    08/2016    09/2016    10/2016    Quarterly_Total
32501        112233    IRA            1            5        4            10
32501        112233    RFI            4            2        5            11
32501        112233    XSS            4            6        2            12
32501        112233    SQLi           3            3        7            13
32501        112233    BDP            0            0        0            0
32501        110025    XSS            0            3        0            3
[...]

Basically it's a matter of making columns into rows from the source table and I've read somewhere that there is a PIVOT function that can help but didn't really understood how to use its syntax in my case. Also I should point out that the source table is actually a view that I create based on several different tables using a stored procedure.

A PIVOT statement, given all its hard-coding, is a glorified CASE statement. It was easier to build out a UNION query with all the counters, and run CASE statements against the values.

You can definitely convert this into dynamic SQL for future reference, but this is a good start.

SELECT [AccountID],
    [SiteID],
    [ThreatName],
    SUM(ISNULL([08/2016], 0)) as [08/2016],
    SUM(ISNULL([09/2016], 0)) as [09/2016],
    SUM(ISNULL([10/2016], 0)) as [10/2016],
    SUM(ISNULL([08/2016], 0) + ISNULL([09/2016], 0) + ISNULL([10/2016], 0)) AS [Quarterly_Total]
     FROM (
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'IRA' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [IRA] IS NOT NULL THEN [IRA] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'RFI' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [RFI] IS NOT NULL THEN [RFI] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'XSS' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [XSS] IS NOT NULL THEN [XSS] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'SQLi' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [SQLi] IS NOT NULL THEN [SQLi] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
UNION
SELECT
    [account_id] AS [AccountID],
    [site_id] AS [SiteID],
    'BDP' AS [ThreatName],
    SUM(CASE WHEN [Month] = 'August,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [08/2016],
    SUM(CASE WHEN [Month] = 'September,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [09/2016],
    SUM(CASE WHEN [Month] = 'October,2016' AND [BDP] IS NOT NULL THEN [BDP] END) AS [10/2016]
FROM
    [dbo].[pivoter]
    GROUP BY [account_id],[site_id],[Month]
) a
GROUP BY [AccountID],
    [SiteID],
    [ThreatName]


Fail to login on Sql server with DNS but not with IP I'm facing a strange problem :

I have a sql server on the IP 80.248.XXX.XXX

I've also create an Alias on SQL Server like servername.sql.mydomain.com with this tutorial :
https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

When I use SSMS, if :

I use the IP with user sa, it's working like a charm
I use the domain name servername.sql.mydomain.com, with user sa

I have an error : (in French)

Impossible de se connecter à servername.sql.mydomain.com.

===================================

Échec de l'ouverture de session de l'utilisateur 'sa'. (.Net SqlClient Data Provider)

------------------------------
Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
Nom du serveur : servername.sql.mydomain.com
Numéro de l'erreur : 18456
Gravité : 14
État : 1
Numéro de la ligne : 65536

And I do not understand why...

I've also create an Alias on SQL Server

An alias is a client-side concept.  To be effective you must configure the alias on every client computer connecting to SQL Server.  The alternative is to create a Hostname Alias to enable that name to be resolved by all clients on the network.

There are four easy steps to creating a Hostname Alias, but they are poorly documented and often misunderstood. All you have to do is:

1) Create a DNS Record pointing to the IP address of the target server

2) Configure SQL Server to listen on port 1433 on that IP address

3) Add SPNs to enable the SQL Server Service account to use Kerberos Authentication (optional if you don't use Kerberos).

4) Add BackConnectionHostNames entries to enable NTLM Authentication

https://blogs.msdn.microsoft.com/dbrowne/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance/


Mysql Multiple Joins Slow Performance I am working on a search app that queries a large amount of info (100M+ rows) with multiple filters that can be used and I am having trouble getting it to perform when multiple filters are combined. When I have three or more joins the query takes hours to finish. Is there any way to make multiple joins a lot faster or am I doing something wrong? The following SQL fiddle explains the predicament:

http://sqlfiddle.com/#!9/db1eab8/1

For reference the table structure and slow query is also below:

-- ----------------------------
-- MariaDB 10.1 Table structures (simplified names and columns for this case)
-- ----------------------------
-- Contains the main information rows - this normally has 100M+ rows
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
-- Other columns would normally be here but were removed to simplify this
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains the text content for a - This has the same amount of rows as `a`
DROP TABLE IF EXISTS `a_content`;
CREATE TABLE `a_content` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(2000) NOT NULL,
  `description` varchar(2000) NOT NULL,
  PRIMARY KEY (`id_a`),
  FULLTEXT `f` (`title`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains whether an `a` ID is hidden for a user ID - This normally has 30M+ rows
DROP TABLE IF EXISTS `a_hidden`;
CREATE TABLE `a_hidden` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  PRIMARY KEY (`id_a`,`user`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains an `a`'s country ID's - This normally has 300M+ rows
DROP TABLE IF EXISTS `a_country`;
CREATE TABLE `a_country` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `country` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_a`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Sample Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1');
INSERT INTO `a` VALUES ('2');
INSERT INTO `a` VALUES ('3');
INSERT INTO `a` VALUES ('4');
INSERT INTO `a` VALUES ('5');
INSERT INTO `a` VALUES ('6');
INSERT INTO `a` VALUES ('7');
INSERT INTO `a` VALUES ('8');
INSERT INTO `a` VALUES ('9');
INSERT INTO `a` VALUES ('10');

INSERT INTO `a_content` VALUES ('1','aa sadd','aa');
INSERT INTO `a_content` VALUES ('2','aasdadd sads','aa');
INSERT INTO `a_content` VALUES ('3','aa daa','aaf df');
INSERT INTO `a_content` VALUES ('4','aa sad','aadf ');
INSERT INTO `a_content` VALUES ('5','aa v','aaxcv');
INSERT INTO `a_content` VALUES ('6','aa c','aavv');
INSERT INTO `a_content` VALUES ('7','aa df','aaaa');
INSERT INTO `a_content` VALUES ('8','aa df','aa s');
INSERT INTO `a_content` VALUES ('9','aa vvv','a a');
INSERT INTO `a_content` VALUES ('10','ss','aaaa');

INSERT INTO `a_hidden` VALUES ('1','1');
INSERT INTO `a_hidden` VALUES ('3','1');
INSERT INTO `a_hidden` VALUES ('5','1');
INSERT INTO `a_hidden` VALUES ('7','1');
INSERT INTO `a_hidden` VALUES ('9','1');

INSERT INTO `a_country` VALUES ('1',1);
INSERT INTO `a_country` VALUES ('1',2);
INSERT INTO `a_country` VALUES ('2',1);
INSERT INTO `a_country` VALUES ('3',2);
INSERT INTO `a_country` VALUES ('4',4);
INSERT INTO `a_country` VALUES ('5',112);
INSERT INTO `a_country` VALUES ('6',33);
INSERT INTO `a_country` VALUES ('7',44);
INSERT INTO `a_country` VALUES ('8',66);
INSERT INTO `a_country` VALUES ('9',88);
INSERT INTO `a_country` VALUES ('10',99);

Query:

# Extremely slow and takes hours even though it's using indexes all over. If I remove one join it's instantly fast though
SELECT a.id
FROM  `a`
LEFT JOIN a_hidden ON a_hidden.id_a = a.id && a_hidden.user=1
LEFT JOIN a_content ON a_content.id_a = a.id
INNER JOIN a_country ON a_country.id_a = a.id && a_country.country IN (1,2,3)
WHERE a_hidden.id_a IS NULL && MATCH (a_content.title, a_content.description) AGAINST ('search' IN BOOLEAN MODE)
ORDER BY a.id DESC
LIMIT 0 , 50

The Explain of the above query and table structure:

Well.. MySQL doesn't work well as a search engine. I went with a search engine and selects with multiple filters are instant.


mysql out of available memory We have a dedicated MySQL server with 16GB of RAM that's serving 8 web workers with relatively high traffic (over 1000 requests per second at peak traffic)

At peak we're seeing the following errors in the web server's error logs:

SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Here's a pastebin of my.cnf

I've also uploaded some graphs from collectd to an imgur album as well as a screenshot from mysqlcalculator.com with my settings (you might want to open them in a new window because the background is transparent and the text isn't very visible on imgur's dark background)

As you can see, very little memory is actually used (under 6GB if you exclude cached). mysqlmemorycalculator says 10GB.

For every new MySQL connection from a Client, some amount of memory will be used. So once the number of connections (which uses RAM) exceeds, you will be getting this error.

So check your application whether the connections are properly closed after using.

Also check what is the max_used_connections status variable and check whether that many connections are really getting connected from application. If application is not using that many number of connections, then it is definitely connection leak.


Real time preview of SQL queries generated by applications I need to know what SQL queries are generated by some application.

Is it possible to see it in some kind of sniffer?
I tried to look in Management Studio, but it shows only most expensive queries.

Profiler Trace has been deprecated but you can still use it. Additionally, there are still a few ways to see what's going on:

sp_WhoIsActive from Adam Machanic ... this is an excellent tool to use if you want to know what's going on right now
because I don't have sp_WhoIsActive created on all instances right now, I also use a less elegant script that I put together here http://gist.github.com/swasheck/11379471 that uses a combination of DMVs that may be of interest to you (also for right now).

Depending on your SQL Server version, you can use the system_health Extended Event session.

Here's a way to query system_health in SQL Server 2008:

        SELECT
            td.r.value('@name','sysname') event_name,
            td.r.value('@timestamp','datetime2(7)') event_timestamp,
            td.r.value('(data[@name="wait_type"]/text)[1]','sysname') wait_type,
            td.r.value('(data[@name="duration"]/value)[1]','bigint') wait_duration,
            td.r.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text,
            td.r.query('.') event_data
        into #xe
        FROM (
                    SELECT
                    CAST(target_data AS XML) target_data
                    FROM sys.dm_xe_sessions s
                    JOIN sys.dm_xe_session_targets t
                        ON s.address = t.event_session_address

                    WHERE s.name = N'system_health'
                        and target_name = 'ring_buffer'
                    ) base
        CROSS APPLY target_data.nodes('/RingBufferTarget/*') td(r);
        GO

        select
            event_name,
            dateadd(HOUR,-5,event_timestamp) event_timestamp,
            wait_type,
            wait_duration,
            sql_text,
            event_data
            from #xe
            order by event_timestamp desc
        GO
        DROP TABLE #xe;

SQL Server 2012 offers more information in it's system_health event session. I have a set of queries here that will parse most of the the constituent components.

Depending on what you're seeking, though, the information may not be contained in system_health unless it's waited longer than 15 seconds for a latch or other resource, it's waited for longer than 30 seconds on a lock, or if it's been waiting for something outside of the SQL Server API for longer than 5 seconds (a preemptive wait).


Query runs fast but occasionally never completes I have messed things up at work (oh no!). Everything was running fine until about two days ago; queries executed instantly, and the database tended to sit around 20% load. Then I got told that some of our historical data in the database was entered incorrectly, and needed to be removed and re-entered from the following tables:

CREATE TABLE TrackerStates(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TrackerId] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Latitude] [float] NOT NULL,
    [Longitude] [float] NOT NULL,
    [Altitude] [float] NOT NULL,
    -- Some other fields
    CONSTRAINT [PK_dbo.TrackerStates] PRIMARY KEY NONCLUSTERED  (Id)
)

CREATE CLUSTERED INDEX IX_TrackerId_DateRecorded_Processed ON TrackerStates (TrackerId, DateRecorded, Processed)
CREATE NONCLUSTERED INDEX IX_TrackerId_Processed ON TrackerStates (TrackerId) WHERE (Processed = 0)

CREATE TABLE TrackingPoints(
    [DatabaseId] [int] NOT NULL,
    [TrackerStateId] [int] NOT NULL,
    [RoadId] [int] NOT NULL,
    [Distance] [float] NOT NULL,
    PRIMARY KEY CLUSTERED (DatabaseId, TrackerStateId),
    FOREIGN KEY (DatabaseId) REFERENCES RammDatabases (Id),
    FOREIGN KEY (TrackerStateId) REFERENCES TrackerStates (Id)
)
CREATE NONCLUSTERED INDEX IX_DatabaseId ON TrackingPoints (DatabaseId)
CREATE NONCLUSTERED INDEX IX_TrackerStateId ON TrackingPoints (TrackerStateId)

CREATE TABLE TrackingSegments(
    [DatabaseId] [int] NOT NULL,
    [StartingStateId] [int] NOT NULL,
    [EndingStateId] [int] NOT NULL,
    [EntityId] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED (DatabaseId, StartingStateId, EndingStateId),
    FOREIGN KEY (DatabaseId) REFERENCES RammDatabases (Id),
    FOREIGN KEY (StartingStateId) REFERENCES TrackerStates (Id),
    FOREIGN KEY (EndingStateId) REFERENCES TrackerStates (Id),
    FOREIGN KEY (DatabaseId, StartingStateId) REFERENCES TrackingPoints (DatabaseId, TrackerStateId),
    FOREIGN KEY (DatabaseId, EndingStateId) REFERENCES TrackingPoints (DatabaseId, TrackerStateId)
)
CREATE NONCLUSTERED INDEX IX_DatabaseId ON TrackingSegments (DatabaseId)
CREATE NONCLUSTERED INDEX IX_DatabaseId_StartingStateId ON TrackingSegments (DatabaseId, StartingStateId)
CREATE NONCLUSTERED INDEX IX_DatabaseId_EndingStateId ON TrackingSegments (DatabaseId, EndingStateId)

My first attempt to delete the data simply took far too long - it's historical data so there are a few million rows. After that failed, I asked around and someone suggested disabling check constraints, deleting, and then re-enabling the constraints for those specific tables (which I will NEVER do again, terrible idea). The disable and delete ran fairly quickly, I had to leave the enable running all night but it did succeed.

From then on, the database has been at 100% CPU, and one of the queries occasionally never completes. It's the same query every time:

SELECT TOP 500 * FROM TrackerStates WHERE TrackerId = @TrackerId AND Processed = 0 ORDER BY DateRecorded

This query uses the index IX_TrackerId_DateRecorded_Processed, and usually takes no time at all to execute. Occasionally though, a query will just sit there executing until it times out (after 30 seconds).

So far, I have tried:

Scaling the database up to 5 times the size (less timeouts, but still 100% CPU usage)
Rebuilding the indexes, as they were fragmented to about 80% (no difference)
Changing the query to isolation level READ UNCOMMITTED (no difference)

What could I possibly do to fix this?

It looks like the statistics are off. From the plan you posted it's estimating that it's going to read 64,000 rows, but it's actually reading zero. That's a very wide disparity. I'd suggest a few things. First, update the statistics with a full scan. Any index rebuilds ought to have taken care of that, but with this disparity I'm wondering if something is up there. Next, make sure that the constraints are all in place (although, this plan isn't referencing constraints since it's a straight index seek with a TOP operation). Finally, capture the wait statistics for the system to see what's actually causing things to run slowly. You can use extended events to capture the wait metrics for just this query, so that's an even better approach. Also, a 64,000 row range scan from a seek is a little excessive unless you have millions of rows. However, this could still be a part of the statistics being off.


How do you represent VIEWS in an ERD? Are VIEWS (CREATE VIEW) represented in an ERD?   If so, how do you represent them and specify what tables they came from?

There is a view object available in several ERD tools that I've seen (TOAD, Agilian, Visio).  I'm not entirely sure that it is formally a part of any notation, but could be construed as a stereotype, as it is effectively just another entity.

Demonstrating the relationship to the source tables is a bit more difficult, IMO. I would just use a generic connector, especially as the relationship is likely much more complex than can be described visually (I just created a view that is populated in part by functions that run complex queries on multiple tables).  I would absolutely not use the relationship connectors.


Will redirecting queries to dev/null warm up indexes? I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice?

e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt

Thanks

I see one of the comments in the question mentions caching indexes

  please note that only one index will be cached in the buffer pool (most probably PRIMARY, but check EXPLAIN to be sure). You may need secondary indexes in the cache as well, so you have to run additional SELECTs

It just so happens I have post from Feb 04, 2012 that lets you make those queries (MySQL warm procedure). From that post, here are the queries to show all queries you need to run:

LOADING INNODB BUFFER POOL

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb

LOADING MyISAM KEY CACHE

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER
BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT
B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema
NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb
;

LOADING INNODB BUFFER POOL AND MyISAM KEY CACHE

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (
                SELECT engine,table_schema,table_name
                FROM information_schema.tables
                WHERE engine IN ('InnoDB','MyISAM')
            ) B USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

Simply redirect the output to a text file like /root/IndexWarmup.sql

Then, login to mysql and run

mysql> source /root/IndexWarmup.sql

OPTIONAL SUGGESTION

I would highly recommend upgrading to MySQL 5.6. Why? You can do the following:

Dump and Load a map of the InnoDB Buffer Pool Shutdown and Startup

[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Dump and Load a map of the InnoDB Buffer Pool on Demand

mysql> set global innodb_buffer_pool_dump_now = 1;
mysql> set global innodb_buffer_pool_load_now = 1;

That way, you never have to read index pages into the Buffer Pool you never access.

Please see the MySQL Documentation

innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_now
innodb_buffer_pool_load_now


sql bulk delete is much slower? I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete

DELETE TOP (n) syntax, then loop until now more needs to be deleted

it seems we controlled the log size problem. however,  the performance downgraded a lot. for example, if I delete the data from the first table A in one batch (delete from ...), it takes about 9 minutes. Now, if I delete in a batch of 2000, after the whole night, it does not finish yet. If I get the # of rows that will be deleted, then batch delete 10% of it in each loop (so, about 200,000 for each batch), it takes about 40 minutes.

Is this normal? one thing I want to point out is: there is no primary key in these 2 tables, although they have non-cluster index. it was designed by previous developer who never thought we would have so much data to process. Now, we have to live with this, change database structure is not an option. Could it because of this that makes the delete slow?

Any suggest of what can cause the slow?

Considering the symptomp of gradual degradation after each batch I would guess is caused by DELETE leaving behind ghosted records, see SQL Server “empty table” is slow after deleting all (12 million) records? Subsequent deletes have to skip over all those ghosts, until they are reclaimed. For a one time operation it would make more sense to copy the data to be kept into a new table, then use partition switch tricks to switch it in instead of the old data, then truncate the old data. If you can't do ALTER TABLE .. SWITCH you can use the old sp_rename trick, but bear in mind that sp_rename does not preserve security permissions etc on the table (it is a different table with the same name).

If this deletion is to be done regularly, then you may look into a sliding window scenario.


mysql duplicate entry error 1062 when restoring backup Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.

Requirement: To create an exact replica 'db4' of an existing DB 'db3'.

Procedure followed:

mysqldump -uuser -ppass db3 > db3.sql (size is 6G)
mysql -uuser -ppass db4 < db3.sql (db4 was a newly created blank database)

The 2nd step throws in the error:

ERROR 1062 (23000) at line 5524: Duplicate entry '600806' for key 1"

I ran the 2nd step  again with --force. The restore completed but with 2 additional similar errors:

ERROR 1062 (23000) at line 6309: Duplicate entry '187694' for key 1
ERROR 1062 (23000) at line 6572: Duplicate entry '1567400' for key 1

On completion when I queried certain tables of db4 database, I was able to see missing records.

Question:

Does this indicate a corrupted/problematic db3 database?
How to proceed to create a 'consistent/working' replica (db4) of db3?
If (2) fails, how to possibly troubleshoot and find the reason behind why it occurs?

Thanks,

I would suggest that this is indeed indicative of a problem with db3.  By default, mysqldump generates "extended" insert statements, containing more than one row's worth of insert per line.

INSERT INTO table_name VALUES (...), (...), (...), ...;

This is an optimization, since multiple inserts in a single statement are much faster than executing individual insert statements.

But, you can disable this behavior using the --skip-extended-insert option.

Using that option for backups isn't a good idea, since they restore much more slowly, but this option does make dump files that are much easier to read with your eyeballs and much easier to search through for a specific record using grep or a similar tool.

Dump the database using this option and then search through the file for the duplicate keys that are throwing errors and it seems likely that you'll find duplicate rows or rows with duplicate keys that should have been unique... which means underlying table trouble in db3.

I can't think of a way this could happen with InnoDB but with MyISAM it's distinctly possible.

There are a couple of other mysqldump options that might also be useful:

--replace generates a file writing the statements as REPLACE INTO instead of INSERT INTO which would result in the duplicate key records occurring later in the file replacing the conflicting records that occurred earlier in the file, without generating an error
--insert-ignore generates a file with the statements written as INSERT IGNORE INTO instead of INSERT INTO, which would cause the duplicate key records occurring earlier in the file to be persisted in the restored tables, as the later conflicting records would be ignored, not inserted, and would not generate an error.


Tempdb Splitting File/Filegroups among different Databases A common problem placing databases on one server Instance is they all share same tempdb. Is it possible to allocate tempdb file/and filegroups for separate databases. Does Resource Governor allow this?

Database 1, only gets access to one file/filegroup 1 in tempdb.

Database 2, only gets access to one file/filegroup 2 in tempdb.

No.

Resource Governor is for throttling CPU, query workspace memory, and storage throughput, but there is no feature in SQL Server that restricts which files/filegroups a database can access in TempDB.

Plus, think about cross-database queries - if you had a cross-database query, how would that even work?


Almost Empty Data File is still taking time to Shrink in SQL Server I had one file in a filegroup FG1 which was orginally of size 130 GB and almost full. It was having partitioned tables in them. I have moved all the big tables to other filegroup FG2 and now FG1 is having 93.5% internal free space.
Purpose of doing all this was based on below theory:
"when filegroup FG1 was almost full, shrinking was taking too much time. So after moving almost all the data to other filegroup FG2, shrinking of FG1 should be faster."
When I tried to shrink almost entire FG1 with below command it still took 1 Hr 56 Mins to shrink.
DBCC SHRINKFILE('FG1',10240)
Am I missing something that would have made this shrinking more faster? or it is expected to still take it this much of time?
NOTE: I tried DBCC SHRINKFILE(FILEID,TRUNCATEONLY) after moving the data, but unforuntately it didn't help.

Three things that can make shrink of data file very slow are:
Heaps tables. For every page moved, SQL server has to adjust every index, for all rows.
LOB pages. For every page moved, SQL Server has to do a table scan of the table (there's no back-pointer).
Shrink being blocked - it has an endless timeout.
My guess is that you encountered one or both of the first two.


How long does it take for powershell to restore a SQL Backup? I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name:

    $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)")
    $res = new-object Microsoft.SqlServer.Management.Smo.Restore
    $backup = new-object Microsoft.SqlServer.Management.Smo.Backup

    $backup.Devices.AddDevice("D:\AWS_CM.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $backup.Database = "Arizona_CM"
    $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
    $backup.Initialize = $TRUE
    $backup.SqlBackup($srv)
    $srv.Databases["Arizona_CM"].Drop()

    $res.Devices.AddDevice("D:\AWS_CM.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $res.Database = "AWS_CM"
    $res.NoRecovery = $TRUE
    $res.SqlRestore($srv)

The script worked. However 10 minutes have passed and when I see the Database in SQL Server it is still in "Restoring" mode even though the size is only 80 mb.

Does anyone know how long restoring Database in SQL Server through powershell usually takes?

You are setting the restore to not recover your database.

$res.NoRecovery = $TRUE

Remove this line or set this value to $FALSE and the database will restore as you expect.

This setting is useful when restoring log backups. This includes when you restore the tail of the log. Books Online has more info here

As for the duration of a RESTORE it depends on your storage and the size of your database. With that said, 80MB shouldn't take very long at all.


Cannot restore postgres dump file When i try to restore a database from a dump file using the following command:

 \i C:/pathname/dumpfile.txt

from within my sql shell, it runs the first few lines of the dump file and then I get the following error:

  ERROR:  permission denied to create "pg_catalog.databasename"
  DETAIL:  System catalog modifications are currently disallowed.

This shows the beginning of the dump file. I think the CREATE TABLE command triggers the error.

I'm using postgreSQL Version 11.1.
Any ideas how to solve this?

Ok I solved it: in the following line:

SET search_path = car, pg_catalog;

apparantly, car is the name of the schema. As that schema didn't exist, it tried to create a table in the pg_catalog schema. I manually created a schema called car, and, tada..
Thanks to everybody for their support.


MySQL binlog limitations I have a MySQL instance running on AWS, with around 5000 inserts per second. Any idea what the performance impact will be if I use binlog (row) and a binlog tailer?

Check this link

From my understanding, a bin log tailer polls the MySQL binlog periodically in order to make a 'realtime' data connection possible. The binlog tailer runs in NodeJS.

The point is, I have to use MySQL and I want to use Meteor to get the data in realtime to my clients. Hence my idea of using this binlog tailer.

Because the MySQL db is gonna be filled quite intensively (5000 inserts per second) , I want to know at which point the binlog/binlog tailer gets severe performance issues.

Grts, Tom

I have developed software with similar functionality, the ability to use the MySQL replication stream (binary log, binlog) to capture events in near-real-time in response to inserts/updates/deletes in the database.

Here are some observations I've made regarding performance.  Luckily, the potential hot spots are largely independent of each other.

I will assume, since I was unfamiliar with the Node package you cited and have only just now given their code a cursory review, that they are not actually "tailing" the binlog via polling, but are actually emulating a slave/replica server and connecting to the master and requesting the replication stream.

The first potential bottleneck is the master's ability to write the amount of Binlog data required (I/O throughput is the primary cobstraint).  If your master is already logging in ROW format, then this problem is already solved. If not, then switch your Binlog format, and see. I prefer ROW format, anyway, because it's very useful for data recovery when queries go bad or the application does something to the data it shouldn't have.  It is possible (using 3rd party tools) to capture what happened and reverse it -- in the default configuration, when a delete occurs (for example) the deleted data is actually written to the binary log.

The next point of resource consumption is the slave connection made by such a tool to the master, where the master pushes the data.  A common misconception is that a slave "polls" the master.  In fact, the slave initiates the connection, but the master pushes the data.  This is actually a load on the master that has very little impact on performance when the number of slaves connected is small (say, 5 or fewer).  This load can be eliminated from the master entirely by connecting the "binlog tailer" not to the master, but to an existing slave of the master, with log_slave_updates configured.

Transport of the data from the master to the pseudo-slave can eat significant network bandwidth, so your external utility should support the MySQL client/server compression protocol to reduce this bandwidth.  Enabling this capability can achieve compression ratios of 10:1 depending on the payload.

The final pain point is the external utility itself.  The MySQL Binlog format is a very tightly packed binary format (hence "binary log") that must be parsed and decoded.  The efficiency with which the external utility can unpack and manipulate this data stream will determine how close to real-time the events detected can be emitted, since inefficient code will cause your decided event stream to lag further and further behind the master, though this factor won't have any performance impact on the master server itself.

In short, if your master can handle the workload of generating row-format binlogs for the volume of traffic you expect, the rest of the potential issues are still potential issues, but they should have no meaningful performance implications on the master server, itself.


MySQL Cluster Node Connections I am a brand new user to the MySQL cluster world (literally hours). I was following a tutorial just to get a simple cluster set up (one management node on one machine, a second machine with a SQL node and a data node). I seem to have a functioning management node and I believe I have a functioning SQL node, but my data node acts like it's connecting, but the management node says it isn't connected.

Management Node Config:

[ndbd default]
NoOfReplicas= 1
MaxNoOfConcurrentOperations= 10000
DataMemory= 3072M
IndexMemory= 256M
TimeBetweenWatchDogCheck= 30000
DataDir= /var/lib/mysql-cluster
MaxNoOfOrderedIndexes= 512

[ndb_mgmd default]
DataDir= /var/lib/mysql-cluster

[ndb_mgmd]
NodeId=1
HostName= 192.168.0.5

[ndbd]
NodeId= 2
HostName= 192.168.0.6

[mysqld]
NodeId= 4
HostName = 192.168.0.6

# choose an unused port number
# in this configuration 63132, 63133, and 63134
# will be used
[tcp default]
PortNumber= 63132

SQL Node Config:

[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.5
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.0.5

Data Node Config:

[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.0.5

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.5

When I run sudo /usr/local/mysql/bin/ndbd I get:

2012-04-22 22:56:44 [ndbd] INFO     -- Angel connected to '192.168.0.5:1186'
2012-04-22 22:56:44 [ndbd] INFO     -- Angel allocated nodeid: 2

But when I check its status on the management node, I get:

Node 2: not connected

I'm sure as a total noob I'm just missing something, but I would really appreciate if someone could point me in the right direction.

That is weird. Two things I would check that can cause problems (in my experience):

1> make sure iptables is not running

2> make sure selinux is disabled (or permissive)

...at least in the beginning.

You can also try watching the network to see if things are going as suggested although I've never done this for mysql cluster specifically:

tcpdump -s0 -A -i any port 63132

Finally,

I noticed you forced a port number in the ndb mgmt config but not in the node configs. Is that going to be required info on the nodes? Try commenting that out in the mgmt node.


Looking for a snippet illustrating ambiguous result of a join in a where clause A while ago (around 2004) I worked as a back-end developer on a team where everyone used the where clause joins. As a proponent of using inner/outer joins instead, I showed them an example of a where clause join that illustrated how it could produce ambiguous results depending on data, and how the outer joins were free from that problem.

I have to do the same presentation but cannot find that snippet anymore. It is somewhere in my file archive which I unfortunately do not have access to. Does anyone have an example handy?

This can only be demonstrated on databases set to 80 compatibility level (or SQL Server <= 2000).

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT, name VARCHAR(32));

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id, name) SELECT 1,'hi' UNION SELECT 2,NULL;

-- returns all three rows - should it?
SELECT *
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.name IS NULL;

-- returns rows 2 and 3
SELECT *
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.name IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Don't want to use a nullable name column to demonstrate? Ok, how about a nullable foreign key column?

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT);

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT NULL;

-- returns all three rows - should it?
SELECT *
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.splunge_id IS NULL;

-- only returns row 3 (because it was the only row truly missing)
SELECT *
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.splunge_id IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Not sure why you need to demonstrate this today - you just simply shouldn't be using *= / =* joins at all, regardless of whether you can reproduce some ambiguous behavior - they are deprecated and won't work in any database that doesn't support 80 compatibility level. And if you are using compatibility level 80, you need to be prepared for a ton of other functional differences and potential breaking changes when you do finally upgrade.

What is the actual behavior of compatibility level 80?
Bad habits : Clinging to old compatibility levels
Bad habits to kick : Using old-style joins


Get all of the rows involved between tables relationships I have a SQL Server 2016 with the following tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            VARCHAR(20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

Aggregation and AggregationChildren represent relations between rows in table Code.

An aggregation will have 1 or more aggregation children. And also, a code in aggregation children could have 0 or more aggregation children.

It is something like this:

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

All of the above are Code.Serial values.

All of the Code.CodeId values for those serials are in Aggregation table for Code1, Code2 and Code3.

And, for those serials, Code2, Code3, Code4, Code5, Code6, Code7, and Code8 are in AggregationChildren table.

I need to update Code.CommissioningFlag for all of those serials.

I have created a stored procedure to called recursively. I pass Code1 as argument, and it will do a recursive call for Code2 and Code3.

This method works fine with a few codes, but now, I have to update 3,000 codes and it blocks SQL Server.

I've thought I have navigate throw the 'tree' and get a temp table with all of the codes, and the do and update for all of them, but I don't know how to 'navigate the tree' in SQL.

How can I get all the codes to update all of them at once?

According to your explanation I think you could use a recursive solution. I've set up a minimal example:

INSERT INTO dbo.Code VALUES
(1,  'Code1',  1, 1),
(2,  'Code2',  2, 1),
(3,  'Code3',  2, 1),
(4,  'Code4',  3, 1),
(5,  'Code5',  3, 1),
(6,  'Code6',  3, 1),
(7,  'Code7',  3, 1),
(8,  'Code8',  3, 1),
(9,  'Code9',  1, 3),
(10, 'Code10', 2, 3),
(11, 'Code11', 2, 3);
GO

INSERT INTO dbo.Aggregation VALUES (1),(2),(3),(9);
GO

INSERT INTO dbo.AggregationChildren VALUES
(2,  1, 1),
(3,  1, 2),
(4,  2, 1),
(5,  2, 2),
(6,  3, 1),
(7,  3, 2),
(8,  3, 3),
(10, 9, 1),
(11, 9, 2);
GO

DECLARE @Serial VARCHAR(20) = 'Code1';

SELECT CodeId FROM dbo.Code WHERE Serial = @Serial;

SELECT    AggregationId Id
FROM      dbo.Aggregation
WHERE     AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial);
GO

| CodeId |
| -----: |
|      1 |

| Id |
| -: |
|  1 |

The anchor selects first row from dbo.Aggregation, and the recursive query rows from dbo.AggregationChildren where AggregationId match AggregationId of dbo.Aggregation.

Then simply update Code table using the list of ID's returned by the recursive query.

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
UPDATE c
SET    CommissioningFlag = 5
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO

8 rows affected

SELECT * FROM dbo.Code;
GO

CodeId | Serial | AggregationLevelId | CommissioningFlag
-----: | :----- | :----------------- | :----------------
     1 | Code1  | 1                  | 5
     2 | Code2  | 2                  | 5
     3 | Code3  | 2                  | 5
     4 | Code4  | 3                  | 5
     5 | Code5  | 3                  | 5
     6 | Code6  | 3                  | 5
     7 | Code7  | 3                  | 5
     8 | Code8  | 3                  | 5
     9 | Code9  | 1                  | 3
    10 | Code10 | 2                  | 3
    11 | Code11 | 2                  | 3

db<>fiddle here


Change default collation for character set utf8mb4 to utf8mb4_unicode_ci When creating a database without specifying a character set or collation the servers defaults are used (as expected).

MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ;
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| character_set_server | utf8mb4            |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+

MariaDB [(none)]> CREATE DATABASE `test-without-charset` ;
MariaDB [(none)]> SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` LIKE 'test-without-charset';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_unicode_ci     |
+------------------------+

However, when specifying the character set within the CREATE DATABASE-query, the default collation changes to utf8mb4_general_ci.

MariaDB [(none)]> CREATE DATABASE `test-with-charset` CHARACTER SET utf8mb4 ;
MariaDB [(none)]> SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` LIKE 'test-with-charset';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+

I already found out that (mysql-manual)

  If CHARACTER SET charset_name is specified without COLLATE, character
  set charset_name and its default collation are used. To see the
  default collation for each character set, use the SHOW CHARACTER SET
  statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.

And indeed it shows utf8mb4_general_ci, so it is following the rules

MariaDB [(none)]> SHOW CHARACTER SET LIKE 'utf8mb4';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

So my question is: How do I change this default collation for the character set utf8mb4. Is there some configuration-file I can change to alter this behaviour? I'd really like those two to be consistent.

Off course I tried Google to find anything relevant, but all I can find is changing the collation_server-setting.

  Server version: 10.3.15-MariaDB-log MariaDB Server

I don't think there is a way to change that DEFAULT.

Anyway, it would be better to use utf8mb4_unicode_520_ci, which is based on a later Unicode standard.

Just get into the habit of specifying CHARACTER SET and COLLATION on all connections and CREATE TABLEs.  MySQL and MariaDB are gradually changing from latin1_swedish_ci to utf8mb4_0900_ai_ci.  MariaDB is not there yet, but I expect them to move soon.  And "900" is probably not the last Unicode standard.

By explicitly specifying the charset and collation, you maintain control and consistency, even if it is an out-dated pair.

A compromise...

But charset and collation on CREATE DATABASE.  Then any tables built without specific settings will inherit those settings.  And columns within that table will inherit from the table's settings.


Is there a way to query SQL Server 2005/2008 for location of its log files? I there a way to query SQL Server 2005+ for the file system location of its various log files. By that I mean the text log files, not database transaction log files?

For example my SQL Agent, Error Logs and maintenance plans all write to a folder called:

D:\MSSQLData\MSSQL.1\MSSQL\LOG

It's this folder I need to find the name of.

Updated:

As well as hunting SQL Books I had a poke around the registry under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

and

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER`

but nothing jumps out at me.

I'd also like an "official" way to achieve this and would rather not rely on undocumented features.

SELECT REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(512)), 'ERRORLOG', '')

EDIT: Cleaned it up a bit with a little more googling to verify; Also to note that this particular argument "ErrorLogFileName" to the SERVERPROPERTY function is undocumented and therefore unsupported by Microsoft. Use at your own risk. etc. etc.


SQL case statement not executing else part Problem statement:

Create a function to search a student with roll_no; if exists show "Student Name and Course" else show "Roll No not exist".

CREATE FUNCTION search(roll_num VARCHAR(50))
RETURNS VARCHAR(150)
RETURN
(SELECT CASE
        WHEN `roll_no`= roll_num  THEN CONCAT (`student`.`student_name`,`student`.`course_id`)
        ELSE 'Roll No not exist '
        END
FROM `student`WHERE `student`.`roll_no` = roll_num);

SELECT search(31219005) ; //since this is an existing roll num everything went correct.
SELECT search(21219005) ; //this roll is not an existing one in the DB, but it simply shows NULL , instead of existing my else part.

This returns the wanted result, but i don't think that you wnat only the course id.

But this should help further

DELIMITER $$
CREATE FUNCTION search(roll_num VARCHAR(50))
RETURNS VARCHAR(150)
DETERMINISTIC
BEGIN
    IF (EXISTS(SELECT 1 FROM `student` WHERE `student`.`roll_no` = roll_num)) THEN
        RETURN (SELECT CONCAT (`student`.`student_name`,`student`.`course_id`) FROM `student`WHERE `student`.`roll_no` = roll_num);
    ELSE
        RETURN 'Roll No not exist ';
    END IF;
END$$
DELIMITER ;