A slow query on MS-SQL-Server 2019 cannot be optimized In the following thread I received some important and valuable information on how I can improve a slow query on MS-SQL-Server 2019.
However, that didn't do very much.
First I deleted all non-clustered indexes and replaced them with clustered indexex. With the clustered index, some queries seem to be at least a little faster.
First question: It always seemed to me as if MS-SQL had not used the non-clustered index at all because creating a non-clustered index did not improve performance. Is that possible?
I kept getting the tip from you that I should absolutely avoid data conversions when accessing the index.
I tried that in a view. In the following script, the date values are ALL nvarchars.
However, the script runs for 78 minutes! Without the SUBSTRINGs it doesn't run any faster.
Second question: Is there a reason why this script has been running for so long?
The following query is the old version of the same script, just with different tables, but the table structure and the amount of data is roughly the same:
The script from the Second Execution Plan runs for 7 minutes, although here "DATETIME date values" are linked with "NVARCHAR date values".
I don't understand why this script runs so fast, although it is obvious that type conversions must take place here when the index is accessed.
The latter query is then integrated as a view in the following script:
This insert runs for 29 minutes. Previously, on MS-SQL 2008, the same insert ran for 10 minutes.
In this script I also removed the data conversions when accessing the index, but that did not bring any performance gain at all.
This script always runs for about 30 minutes, no matter what I do, I can't get it any faster.
Third question: Do you see a bottleneck in the third execution plan?
After all, you can see in all three execution plans that clustered index accesses take place.

After carefully reviewing your fourth question on the same topic, I'm afraid my diagnosis is exactly the same I provided in your first question. Let's look at your first query here.

You can see it executes in 29 seconds. But let's look at compile time (In SSMS: F4 to enable properties and click on the SELECT node):

The number above is in miliseconds. Converting to minutes it's 77. Adding both times together you get  close to observed 78 minutes runtime.
Let's look at the second, "good" query:

6 minutes compilation time.
Now you might ask yourself is this a lot? The answer is yes in both cases. I would consider compilation time in SECONDS slow. Your cases are patological.
I cannot answer what causes it. I will only give some ideas you could try. First a question: is the problem affecting other queries in your system? If yes then maybe something on the instance is wrongly configured. Do you have any funny trace flags enabled?
Your issue actually existed in original database. It must have been carried over. Like schema. Script out all the tables from the query and everything related to it. Is there anything funny? 100 statistics on one table? 100 indexes? hypotethical indexes?
Try cutting up the query. Remove one join or predicate at the time. You will get garbage results, but at some point the reduced query should start compiling in normal times. That will give you a clue what could be causing it.
That's all I got. Good luck.


Mysqld.exe memory usage I am using mysql 5.6.14, For a long time i got large memory usage by mysqld.exe, i was trying many different solutions and my.ini settings to fix this up but didn't succeed, This is my config file(my.ini), When i start mysqld via command line(mysqld --console) this is the output:

2013-11-14 18:30:36 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --ex
plicit_defaults_for_timestamp server option (see documentation for more details).
2013-11-14 18:30:36 4440 [Note] Plugin 'FEDERATED' is disabled.
2013-11-14 18:30:36 4440 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-14 18:30:36 4440 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-11-14 18:30:36 4440 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-14 18:30:36 4440 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-14 18:30:36 4440 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-14 18:30:36 4440 [Note] InnoDB: Completed initialization of buffer pool
2013-11-14 18:30:36 4440 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-14 18:30:36 4440 [Note] InnoDB: The log sequence numbers 1796484 and 1796484 in ibdata files
 do not match the log sequence number 1796504 in the ib_logfiles!
2013-11-14 18:30:36 4440 [Note] InnoDB: Database was not shutdown normally!
2013-11-14 18:30:36 4440 [Note] InnoDB: Starting crash recovery.
2013-11-14 18:30:36 4440 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-11-14 18:30:37 4440 [Note] InnoDB: Restoring possible half-written data pages
2013-11-14 18:30:37 4440 [Note] InnoDB: from the doublewrite buffer...
2013-11-14 18:30:37 4440 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-14 18:30:37 4440 [Note] InnoDB: Waiting for purge to start
2013-11-14 18:30:37 4440 [Note] InnoDB: 5.6.14 started; log sequence number 1796504
2013-11-14 18:30:37 4440 [Note] Server hostname (bind-address): '*'; port: 3306
2013-11-14 18:30:37 4440 [Note] IPv6 is available.
2013-11-14 18:30:37 4440 [Note]   - '::' resolves to '::';
2013-11-14 18:30:37 4440 [Note] Server socket created on IP: '::'.
2013-11-14 18:30:38 4440 [Note] Event Scheduler: Loaded 0 events
2013-11-14 18:30:38 4440 [Note] mysqld: ready for connections.
Version: '5.6.14'  socket: ''  port: 3306  MySQL Community Server (GPL)

Usually i am using net start mysql to start mysql server but so you may notice some problem i used mysqld --console.

As you can see this is the capture from my resource monitor:

If anyone know how to solve/reduce memory usage I will be very thankful.

Edit:
Thank you for your answer, Without changing anything at my.ini this is the value i get for SHOW GLOBAL VARIABLES like 'join_buffer_size';:

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

If i understand you i should set all my my.ini settings up to 4mb, So After i set:

innodb_buffer_pool_size = 4M
join_buffer_size = 4M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

This is the new memory usage:

About SHOW ENGINE PERFORMANCE_SCHEMA STATUS; i was amazed by the numbers: http://pastebin.com/J6GfV591

About per-connection memory, I am using a Singleton pattern for mysql connections so after i search for pre-connection memory i am not sure if it is pre-query memory or pre-connection memory, If its pre-query memory so my singleton pattern is not related to the subject, So if you can some me where do i set the pre-connection memory because i don't know if it is mysql variable or my.ini directive.

Please note that your server seems to have recovered from a crash, according to the init log. Something strange may have happened there related to your problems (a crash, a non-clean stop, a memory leak, etc.)

There are several reasons why MySQL 5.6 may be consuming a lot of memory (specially compared to 5.5).

You have a 128Mb buffer pool (default), that may consume around 150 MB of memory having into account the extra buffers. Aside from other global buffers, you have to add the per-connection memory. You have a huge join_buffer_size on your configuration (128M), but it is commented. Make sure that it is not set to that value by executing:

SHOW GLOBAL VARIABLES like 'join_buffer_size';

Anything bigger that 4MB is normally not recommended.

The first one I would check, however, -which several people have reported memory problems with- is the performance_schema, a new feature that has been enabled by default in 5.6. You can check its memory consumption by doing:

SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

For more information, we would need the runtime statistics of the server, namely:

SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS;

Edit to your edit

Do not set the Buffer Pool to 4 MB! That is a global value, and the most important cache buffer if you are using InnoDB.  That is too little even for an embedded system. The 4Mb was just for the join_buffer_size. If you don't know what to put there, and as you seem to be aiming for low memory usage, set it to something around the 50-80% of the total memory you aim to consume.

Your performance_schema consumption is certainly worrying. Set performance_schema = off on your configuration and restart MySQL Server.

Regarding per-connection-memory: simply you must know that there are some variables that reserve memory per action or per session. join_buffer_size, sort_buffer_size, etc. are session variables; innodb_buffer_pool_size is global to the server and is only reserved once. If you are using a single connection to the server, you may not have to worry too much. But please share the complete output of the previous commands, and your hardware specs/memory usage aims.


Why does 'sysfiles1' show up in the sys.objects catalog view in SQL Server 2008? We were recently using this query to find tables in our database that didn't have clustered indexes and found that one of the results it reported back was the 'sys.sysfiles1' table. We're running SQL Server 2008 and I was under the impression that this table wasn't used anymore (as some answers have pointed out there seems to be a misconception that this table only exists for databases upgraded from SQL Server 2000). Furthermore, I'm not able to directly select anything out of the sys.sysfiles1 table (though I am able to select directly out of the sys.sysfiles view).

Running the following to create a fresh database against a local installation of SQL Server 2008 (@@version = Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64), though we've seen it on our production instance of SQL Server 2008 as well) illustrates what I mean:

CREATE DATABASE SysFilesTesting
--returns a row:
SELECT * FROM SysFilesTesting.sys.objects where name = 'sysfiles1'
--throws 'invalid object name' error:
SELECT * FROM SysFilesTesting.sys.sysfiles1

Why is sys.objects reporting the existence of a sysfiles1 table?

There are lots of them

SELECT * FROM sys.objects where name LIKE 'sys%'

These really are objects in your databases
Internally, other useful objects like sys.objects don't actually exist in your database.


Read mysql config (my.cnf) using mysql console I have a mysql server that is not under my control (I don't have ssh on that machine). I only have access to one database using mysql client.

How can I have access to config options that are declared in my.cnf?

Depending on how your access was implemented, you may be able to look at my.cnf with the \! command.

mysql> pager less
mysql> \! cat /etc/mysql/my.cnf
....
mysql> nopager

If you want to know what variables are actually running on the server, look at SHOW GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES;


Delay Trigger Execution Let me explain the scenario first before asking the question.

I have a database with a set of tables. One table, say Table A, contains username details, and other table, say Table B, contains user activity log. I want to delete the specific user log from Table B when that specific user entry is deleted from Table A. I can do this with a trigger, but there is a requirement that entries from Table B should be deleted after sometime (say after 5 minutes), not immediately.

Is it possible to delay trigger execution for 5 minutes? What could be the possible design for trigger. Whether deferred trigger will be helpful in this scenario.

No, deferred trigger execution will not help. It only defers triggers until the end of the transaction.

PostgreSQL doesn't have a built-in task/job scheduler, either, so you'll need to do this in your app.

In the trigger, insert a row into a table that keeps track of which entries should be deleted and when. Scan that table every few minutes and, in a single transaction, records that're past their expiry time and their corresponding entry in the delete queue table.


Best way to store an unordered list? I apologize if this question has (undoubtedly) been asked before, however I've been hearing conflicting advice from colleagues and the internet.

I have a table of "groups" and each group has a list of members.  My initial implementation plan was to have a table that mapped user IDs to group IDs.  However, a colleague suggested using a different method, where members would be stored in a blob of information related to a "group" entry.  I cannot remember why this was suggested, but he seemed to think it would be more efficient because a member-group mapping table could grow very large, very quickly.

I can't see why it would be a better approach, but I suppose I'm just asking if there is a better alternative to simply having a user ID - group ID map table?

Thanks for your time!

Within a database, what you're describing is a table. Tables do not have order, but the data can be indexed to allow easier maintenance and retrieval.

You could always store data as a CSV list, XML, or a variety of other methods, but you should also consider what you're going to do with your data, and you're likely to find that a table ticks more boxes than most. If you're concerned about space, then compression strategies will help a lot, without losing the searchability.


STATISTICS IO for parallel index scan Assume that there is a table with clustered index

create table [a_table] ([key] binary(900) unique clustered);

and some data

insert into [a_table] ([key])
select top (1000000) row_number() over (order by @@spid)
from sys.all_columns a cross join sys.all_columns b;

By inspecting storage statistics of this table

select st.index_level, page_count = sum(st.page_count)
from sys.dm_db_index_physical_stats(
    db_id(), object_id('a_table'), NULL, NULL, 'DETAILED') st
group by rollup (st.index_level)
order by grouping_id(st.index_level), st.index_level desc;

one can see

index_level page_count
----------- ----------
8           1
7           7
6           30
5           121
4           487
3           1952
2           7812
1           31249
0           125000
NULL        166659

that table takes 166659 pages in total.

However table scan

set nocount on;
set statistics io, time on;
declare @cnt int;
select @cnt = count(1) from [a_table];
set statistics io, time off;

produces

Table 'a_table'. Scan count 5, logical reads 484367, ...
CPU time = 1757 ms,  elapsed time = 460 ms.

almost three times higher number of logical reads in comparison to space taken by table. When I examined query plan, I noticed that SqlServer used parallel index scan. And this is where first part of the question arises.

How parallel index scan is performed that makes SqlServer to do so much logical reads?

Specifying option (maxdop 1) to suppress parallelism

set nocount on;
set statistics io, time on;
declare @cnt2 int;
select @cnt2 = count(1) from [a_table] option (maxdop 1);
set statistics io, time off;

resulted to

Table 'a_table'. Scan count 1, logical reads 156257, ...
CPU time = 363 ms,  elapsed time = 367 ms.

Comparing statistics for parallel and non-parallel index scan in this case leads to a conclusion that sometimes it is better to avoid parallel index scan. And this is where the second part of the question arises.

When should I worry about parallel index scan? When should it be avoided/suppressed? What are the best practices?

The above results are obtained on

  Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

If you add a TABLOCK or READUNCOMMITTED hint to the table, you will get an allocation-ordered scan, which will report the same number of logical reads as the number of pages in the table.

With an allocation-ordered scan, SQL Server uses allocation structures to drive the distribution of pages among threads. IAM page accesses are not counted in STATISTICS IO.

For a non-allocation-ordered scan, SQL Server breaks up the scan into subranges using index keys. Each internal operation to find and distribute a new page, or range of pages (based on a key range), to a worker thread requires accessing the upper levels of the b-tree. These accesses are counted by STATISTICS IO, as are the upper-level accesses made by worker threads when locating the start point of their current range. All these extra upper-level reads account for the difference you see.

Too much weight is given to I/O statistics in my opinion. Tune your queries according to what is important to you and your workload. This is often elapsed time, but resource utilization can also be a factor. No one metric is 'best' - you should take a balanced view.


How to force a sysadmin to store data in schema other than dbo? I am using a GUI tool to upload data in my SQL Server 2016 database.
The user being used for above task has been granted sysadmin permission
I created a dedicated schema to store above data. This schema is owned and default to above said user.
The issue is, when i upload data it stores in dbo schema instead of dedicated schema and i couldn't revoke sysadmin priv from this user.

Kindly let me know how to upload data to this dedicated schema without revoking sysadmin priv ?

Thanks

The only way for a member of the sysadmin fixed server role to use a non-dbo schema is to explicitly qualify their object names with the schema name:

CREATE TABLE MySchema.TableName (...

instead of:

CREATE TABLE TableName (...

There is no way around this. The documentation for ALTER USER (in the "Remarks" section) even has a note for:

  🛈 Important

  The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

This is most likely due to the fact that Logins (instance-level) that are members of the sysadmin fixed server role become the dbo User (database-level) in all databases. To verify this, log in as any non-sa Login that is a sysadmin and execute the following:

SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User];


Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine? (Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?)

PostgreSQL 9.5.

I can't divulge full details, but table has 22 columns and 5 indexes:

primary key ('pk'), text (btree)
another text (btree)
a timestamp with time zone (btree)
a tsvector (gin)
my latest one, a bigint (btree)

(From the previous post you know that I was trying to avoid creating this additional column, and just use an expression index--adding two integer columns together--without success.  The bigint column here probably could have been just 'integer' but I made a mistake creating it; it took about an hour to add the column, populate it, and reindex, so I'm hoping this isn't relevant but mentioning it just in case it is.)

All are btree except the tsvector.

The following queries all take only 12ms and use just an Index Scan:

SELECT pk FROM table ORDER BY pk DESC LIMIT 10
SELECT pk FROM table ORDER BY text_column DESC LIMIT 10
SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10

But if I try to use my new bigint index for ORDER BY:

SELECT pk FROM table ORDER BY bigint_column DESC LIMIT 10

...it takes 2.7s and uses Limit -> Sort -> Seq Scan.

My "cheating" method is the closest I seem to be able to get to making use of the index:

SELECT pk
FROM table
WHERE bigint_column > 1000000
ORDER BY bigint_column DESC LIMIT 10

This takes 12ms and uses Limit -> Sort -> Bitmap Heap Scan (bigint_column > 1000000) -> Bitmap Index Scan (bigint_column > 1000000).

This is after a VACUUM ANALYZE after adding the index.

I thought it strange that my expression index was not being used in the other question.  Now it's just a plain old column (I haven't even added what would be the necessary triggers to actually go this route.)

Why isn't my newest index being used, when the other three work "just fine"?  (As pointed out in the comments at https://dba.stackexchange.com/a/183290/28774 , an Index-Only Scan would be even better.  I fail to see why all of these queries wouldn't use at least an Index Scan, let alone an Index-Only Scan, instead of a full Seq Scan.)

The index definition has DESC NULLS LAST (although it's a non-nullable column.)

In PostgreSQL, an index which is DESC NULLS LAST cannot be used to satisfy an ORDER BY which is DESC NULLS FIRST (which includes ordering by simply DESC because that implies NULLS FIRST).  This is the case even if the column is defined to be NOT NULL.

You could either rebuild the index, or (since you know the column is not null) you can add NULLS LAST to your query's ORDER BY to make it match the existing index.

Note that PostgreSQL does know how to follow an index backwards, so a default index (which is implicitly ASC NULLS LAST) would also be able to satisfy your DESC NULLS FIRST query.  Because of this, it is rarely important to specify DESC in an index, but it can be important to specify which end the NULLS sort to.


How to make sure change_tracking statistics stays updated I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )
I have multiple databases with CHANGE_TRACKING enabled on high insert/update tables. My retention period is 2 DAYS, with auto cleanup enabled. Database compatibility is set to 130 (SQL 2016)

Some of the tables tracked are huge and can have thousands of insert/update every day. This is the content of the change_tracking tables.

Here is an example of query we do to get the changes.

SELECT Columns
FROM CHANGETABLE(CHANGES MyTable, @TrackingKey) AS CT
INNER JOIN MyTable b ON b.Key=CT.Key
WHERE b.Status = 1

I have seen from time to time that the queries to get the latest changes on some of the tables take an awful long time to complete and generates a lot of CPU. To help with this, I've setup a daily update statistics on change tracking tables that run every night. And it helps a lot, but sometimes, during high user activity days, I have to run this update statistics even during the day. When I run the update statistics on those table, the situation is back to normal and the queries to get the latest changes run fine for some time.
We use the change tracking for some vital parts of our applications, so it has to work.
Is there any option, trace flag that I can enable to help with change tracking statistics? Anyone have experience with change tracking on high activity databases can give me some advice?

So I finally decided to do a job to check if change tracking table has changed more than 20k rows since last statistic update, then the job will update the statistics on the change tracking table.
I'll put the query here if it can help someone else. This query gives you all the statistics for change tracking tables where the table changed more than 20k times since last update statistics. It gives you a "Update Statistics" with the object_name to update. You just need to run results from the query on your database. I'll fine tune it for my workload and see if 20k is the best number.

SELECT
 -- st.object_id                          AS [Table ID]
 --, OBJECT_NAME(st.object_id)             AS [Table Name]
 --, st.name                               AS [Index Name]
 --, STATS_DATE(st.object_id, st.stats_id) AS [LastUpdated]
 --, modification_counter                  AS [Rows Modified]
'UPDATE STATISTICS sys.[' + OBJECT_NAME(st.object_id) + ']' as QueryToRun
FROM sys.stats st
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE OBJECT_NAME(st.object_id) like 'change_t%'
AND OBJECT_NAME(st.object_id) in (SELECT  'change_tracking_' + convert(nvarchar(50),st.object_id) FROM sys.change_tracking_tables ctt inner join sys.tables st ON st.object_id = ctt.object_id inner join sys.schemas ss ON ss.schema_id = st.schema_id)
AND modification_counter > 20000

Since you are on SQL Server 2016 with DB compat mode, the behavior of TF 2371 is the default behavior.

  You no longer have to enable trace flag 2371 in SQL Server 2016 or later because the corresponding behavior is enabled by default.

You should enable auto update async database option so that when sql server updates the stats, it does not affect your workload. Changing this db option is an online operation.

What you can do is to leverage sys.dm_db_stats_properties - new DMV to decide if you must manually update statistics for a table.  You can have a sql agent job configured to check and update stats every x min or hour for certain key tables.


Select historical prices for an exact datetime - MySQL I have a price_list table where I store historical prices for each item.

columns are -  id, name, price, start_date, item_id

When a price is changed, a one new row is inserted with a current start_date. (Start_date is a datetime format).

Now I need to select prices of all items for an exact datetime. Problem is that this query is slow (1.058 sec):

SELECT * FROM (SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id

Very slow is a GROUP BY. Without it it takes 0.075 sec, but each item (item_id) is there a multiple times.

bad: SELECT * FROM price_list h WHERE start_date <= '2013-12-15 12:14:18' ORDER BY item_id, start_date DESC

Please, can this query be rewritten to be quicker?

Duration goes to 1 sec when I'm selecting name column. But when I use this query, time is only 0.094 sec:

SELECT h.* FROM (SELECT * FROM (SELECT id, item_id FROM price_list x WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id) b LEFT JOIN price_list h ON h.id = b.id

Thank you, Ryan!


unable to start psql : no pg_hba.conf entry for host "::1" , user "postgres", database "postgres", SSL off I am unable to connect using psql to PostgreSQL 9.6 on Windows 10. I saw almost all the StackExchange questions regarding this error. While installing Postgres it asked for superuser password and I set it. Added host all all 0.0.0.0/0 trust and  host all all ::0/0 trust to pg_hba.conf but still no luck. This is the pg_hba.conf contents on my machine:

host    all             all             localhost               trust

host    all             all             127.0.0.1/32            trust

IPv6 local connections:

host    all             all             ::1/128                 trust

host    all             all             ::0/0                   trust

host    all             all             0.0.0.0/0               trust

Any help would be appreciated.

Here is what I did , I have no idea why it worked but it may help you lol.

The error message is something like :

FATAL: no pg_hba.conf entry for host “fe70::3069:d1cc:6120:27e5%15” ...bla..bla

I don't even know much about IP6 but I assume that long weird number is my ip6, so I added it to my pg_hba.conf

  host    all             all             fe70::3069:d1cc:6120:27e5/96  trust

That /96 is what I took from the manual. I can care less about what it mean since it is dev env. and ABOVE ALL it works on my


mongodb - mongos - query a mix of sharded and unsharded collections / databases? I am using mongodb (3.4). I have six databases. I am in the process of sharding a particularly large collection (call it 'user_posts') in one of them. I like the idea of sharding all collections in all six databases, because it improves redundancy and allows for horizontal scaling. But because I am currently terrified of shard keys, I do not want to rush this process. So, I want to shard the collection that I must, and deal with the other collections later.

So, I will have two mongo servers, where one is just a shard of the 'user_posts' collection, and the other is a shard of the 'user_posts' collection but also contains some standalone databases.

Will mongos be able to query all six databases in this scenario?

Thanks

A MongoDB sharded cluster deployment can contain collections that are either unsharded (the default when created) or sharded (based on your chosen shard key). Sharding a collection is (as at MongoDB 3.4) a two-step process: enable sharding on a database using sh.enableSharding() and then shard specific collections using sh.shardCollection(). Sharded and unsharded collections coexist in the same sharded cluster and your application generally does not have to be aware of whether collections are sharded.

If you have a sharded cluster deployment (irrespective of whether you have enabled sharding on individual collections) you should always make your queries and updates through a mongos instance. The mongos instances handle details of routing queries to the correct shard(s) based on the current configuration and status of backing mongod servers.

The "generally unaware" application design caveat alluded to earlier is that a few operations (for example: $lookup and $graphLookup) may not support using sharded collections as the source or destination. There are also some operational restrictions in a sharded cluster which are worth reviewing.

  I like the idea of sharding all collections in all sixdatabases, because it improves redundancy and allows for horizontal scaling

Sharding alone does not improve redundancy. Sharding is used to partition data across multiple servers so you can have multiple MongoDB instances presented as a single logical deployment. Ideally your shards should be backed by replica sets so you have data redundancy and failover (which both rely on replication). I would strongly recommend upgrading a production deployment to a replica set before moving to a sharded cluster.

If you plan on eventually sharding it would be reasonable to get your sharded cluster infrastructure in place so you can minimise disruptions in future. For example, moving from a replica set to a sharded cluster deployment involves setting up config servers, mongos instances, and changing your connection seed list from replica set members to mongos instances.

  because I am currently terrified of shard keys, I do not want to rush this process. So, I want to shard the collection that I must, and deal with the other collections later.

Is the collection that you must shard stressing your current deployment? Before moving to a larger deployment with more moving parts I would encourage you to try to understand and tune your existing deployment. There may be some easy wins that could improve performance ... or conversely, unfortunate design choices that won't scale well in a sharded environment. Helpful starting points include the MongoDB Production Notes, Operations Checklist, and Security Checklist.

If you have good understanding or estimation of your data model and distribution patterns, I would suggest sharding in a test environment using representative data. There are many helpful tools for generating fake (but probabilistic) data if needed. For an example recipe, see: Stack Overflow: duplicate a collection into itself.

  So, I will have two mongo servers, where one is just a shard of the 'user_posts' collection, and the other is a shard of the 'user_posts' collection but also contains some standalone databases.

Assuming you are describing a sharded cluster with two shards, that is correct. Sharded collections will be distributed across shards while unsharded collections will live on a primary shard for each database.

  Will mongos be able to query all six databases in this scenario?

mongos provides access to all databases in the same sharded cluster.


Should I invest the time to change the column type from CHAR(36) to UUID? I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema.

One of the tables has 16M rows (about 3.5M to 4 M records per shard), growing at about 500K records per day. I still have the luxury of taking the production system down for a few hours if required. I won't have this luxury in one or two weeks.

My question is, will it be worthwhile to do so? I'm wondering about JOIN performance, disk space use (full gzip'd dump is 1.25 GiB), things of that nature.

Table schema is:

# \d twitter_interactions
                Table "public.twitter_interactions"
         Column          |            Type             | Modifiers
-------------------------+-----------------------------+-----------
 interaction_id          | character(36)               | not null
 status_text             | character varying(1024)     | not null
 screen_name             | character varying(40)       | not null
 twitter_user_id         | bigint                      |
 replying_to_screen_name | character varying(40)       |
 source                  | character varying(240)      | not null
 tweet_id                | bigint                      | not null
 created_at              | timestamp without time zone | not null
Indexes:
    "twitter_interactions_pkey" PRIMARY KEY, btree (interaction_id)
    "twitter_interactions_tweet_id_key" UNIQUE, btree (tweet_id)
    "index_twitter_interactions_on_created_at" btree (created_at)
    "index_twitter_interactions_on_screen_name" btree (screen_name)
Triggers:
    insert_twitter_interactions_trigger BEFORE INSERT ON twitter_interactions FOR EACH ROW EXECUTE PROCEDURE twitter_interactions_insert_trigger()
Number of child tables: 9 (Use \d+ to list them.)

I would consider changing to the UUID type.  char(36) takes 40 bytes, uuid takes 16, so you'll save 24 bytes per row, which for you will equate to 12 MB a day, 4 GB after a year.  Plus indexes.  Depending on what hardware you have, that isn't much, but it could be.  And it adds up if you have more improvement opportunities like this.

Also, I see no constraint in you schema that ensures that interaction_id is actually in the right format.  Using the right type will give you that as well.

If you like this, however, than using bigint would save even more and have even better performance.  It's very unlikely that your application is so large that a bigint for an ID column won't work.


Cannot process cube using SQL agent job in SSMS I have an issue where we have a user who has used his own AD account to create and deploy the cube back when he was the sole DBA.

I would like to have an proxy account with a password known internally instead of his own credentials.

For this I have created a new AD account, ProcessCube, which I have given sysadmin rights on the underlying SQL Engine. I have also created a role called Process cube on the SSAS database with read and write rights on every cube and read rights on data sources.

In SSAS I have double clicked the data sources and under Impersonation mode I have replaced the old credentials with the credentials of the newly created AD account ProcessCube.

I have not deployed the cube with the new credentials, but rather let the SQL SSAS command in the SQL agent do the trick, do I need to deploy the cube first?

In the SQL Agent I've made ProcessCube the Owner of the job which is a SQL Server Analysis Server Command.

When I try to execute the SQL agent job I get the error:

The following system error occurred:  The user name or password is incorrect.  Source="Microsoft SQL Server 2016 Analysis Service

I assume I need some more credentials, what more do I need? What have  I missed?

The command I try to execute in the agent is as follows:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
    <KeyErrorLimit>-1</KeyErrorLimit>
    <KeyNotFound>IgnoreError</KeyNotFound>
    <NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>
  </ErrorConfiguration>
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
      <Object>
        <DatabaseID>DataBaseID</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

I got this to work now.

In Management Studios I extended Databases->theDatabase->Data source and from there I had the option ImpersonateInfo with the value ImpersonateAccount.

I clicked at ImpersonateAccount and then on .... From there I entered the credentials of the newly created account, and voilá.


Table compression, Row or Page? Need to compress a giant table of 1.5TB in sql server 2008, which is the best option Page or row compression, please let me know the steps to do it.

There is no such a thing as "best option". If page would always be superior, why bother offering row as sub-par alternative or vice versa? It really depends on a lot of things, so further analysis is needed. Start by reading some materials about data compression. MS has published a technical article Data Compression: Strategy, Capacity Planning and Best Practices that's a bit old, but fits your Sql Server version.

By the way, Sql Server 2008 has been out of mainstream support since Q2 2014 (and this is assuming you are running the latest SP4 patchlevel). You really should consider upgrading into more recent a version of Sql Server.


mysqldump―output details I am about to write a little program to process database dump files created by mysqldump and I would like to make sure that my expectations about the »anatomy« of mysqldump's output are right. So please check the statements below and correct the if they are wrong.

When dumping text mysqldump uses a single quote (') to wrap the text to insert.*
Inside these quotes mysqldump escapes single quotes, double quotes and Backslashes (',",\) with a Backslash.*
»newline« characters are dumped as: \n, \r\n
the value of each INSERT of a String is only in one line of the dump-file.

*if not specified differently in command-line arguments

In General my problem is that I could not find any documentation or resource about the »anatomy« of the generated output, I know that these are SQL statements, but especially the way String are handled is the part I am interested in, so if there exists any good Stuff read out there dealing with that, please let me know.

Thanks in ahead!

mysqldump is capable of dumping in three ways

SQL
CSV
XML

SQL
The standard format has directives in addition to regular table creation and INSERTs
The paradigm is always the same

CREATE TABLE
DISABLE KEYS
LOCK TABLE
INSERT INTO ...
INSERT INTO ...
...
INSERT INTO ...
UNLOCK TABLES
ENABLE KEYS

Even with the characters you are coding and delimiting around, please consider mysqldump also processes TEXT, BLOB, BINARY, and VARBINARY fields. Unless you remember to use the --hex-blob option, you could find your delimiter processing somewhat flimsy in the presence of those datatypes.
Another aspect to deal with is if you make each row a separate INSERT using --skip-extended-insert, thus increasing the amount of delimiters to parse and count.
CSV
You could export data in CSV format. You could even set the options for delimiting.

lines-terminated-by
fields-terminated-by
fields-enclosed-by
fields-optionally-enclosed-by
fields-escaped-by

This would eliminate the need to guessing the delimiters by presetting them.
XML
If you export the data as XML (using --xml), the anatomy of the dump may still be subject to the  issues around TEXT, BLOB, BINARY, and VARBINARY fields.
EPILOGUE
If you need to examine how data is batched by mysqldump, see my post How we take dump with batch of records option?
MySQL (eh, Oracle) has basically left it to the discretion of the Community Users to parse mysqldumps. Using --hex-blob has probably saved many developer headaches in order to simplify text processing of mysqldumps. This would relieve MySQL of having to document mysqldump output characteristics.
As for your utility you are coding, you are either going to see strings delimited or hex encoded. No need to sweat details past these two possibilities.
I just thought of something else: doing mysqldump from Linux vs. Windows. Keep in mind that whichever OS platform you use to run mysqldump. code for that platform only. Otherwise, you may need two little programs, one for Linux and one for Windows. (I won't mention MacOS at this point)


Exception while inserting records in SQL Server (Error message id : 586) I am getting an exception when doing a batch insert operation using a prepared statement from Java.

  java.sql.BatchUpdateException: The prepared statement handle 5 is not
  valid in this context.  Please verify that current database, user
  default schema, and ANSI_NULLS and QUOTED_IDENTIFIER set options are
  not changed since the handle is prepared.

While analyzing this error message all I could find out was its error message id : 586. I could not gather any more information about this issue.

Can anyone help me with the reason for this exception, what it means and how to resolve it ?

Machine Details :

SQL Server Version - SQL Server 2014

jTDS JAR Version - 1.2.4

PS : This exception only occurs on a particular remote machine. We cant reproduce this issue locally. Any tips on how to reproduce this issue would also be helpful.

It seems that this server has been configured in a different way than the others.

Using SSMS, right click over server instance in some of the servers where the job is running and check that ANSI NULLS and quoted identifier have the same value on this server.

If you prefer to use an script, have a look at:

SET ANSI NULLS
SET QUOTED_IDENTIFIER


Should I have both Transaction Log Backups and Log Shipping I set my database to Full Recovery a few months ago (to allow point-in-time restores) and set up Transaction Log backups with Maintenance Plan.

Now we want to conduct Log Shipping with the same database. In Log shipping setup, it asks for Source Transaction Log Backups, and then the usual copy to Destination Server and Restores, etc.

If I set up Log Shipping Transaction Log Backups, then I will have two transaction log backups. From setting the database to full recovery, and then log shipping.

(a) Is this is not repetitive? Should I remove the original transaction log backup in Maintenance Plan or the one from Log Shipping?

(b) Or perhaps the Log Shipping Transaction Log Backup does not clear out/truncate the Transaction Log ldf file like Maintenance Plan does?

You will only have one stream of log backups, and that would of course in this situation be your log shipping backups. If you keep doing log backups in your maint plan, then the log shipping restore will fail as soon as maint plan log backup was done "in between" two log shipping log backups. A: Remove the log backups from your maint plans. B: Yes, it does - it is a regular log backup, after all.


Compressed Backup size value is incorrect in msdb I am not sure if in am checking this correctly.

But i can see the discrepancies in the value of compressed backup size from msdb.The value of compressed size is different what i see on the drives.

These backups are taken from Litespeed. e.g Litespeed backup compresses the backup of database to 1 TB for 8 TB and msdb shows that value in column as 4 TB.

Is there a way to get the correct information using SQL for third party backups?

Query used:

Declare @FromDate as datetime
-- Specify the from date value
set @FromDate = GETDATE() -10

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SQLServerName,
   msdb.dbo.backupset.database_name,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
   END AS backup_type,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
   msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024/1024) AS 'Compress Backup Size in GB',
   CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

This is just conjecture, but it sounds like LiteSpeed is compressing the backup after SQL Server has performed it's part of the work.  I doubt you can get accurate compression numbers from msdb in this scenario.

If Quest is using deduplication in their SQL Server backup product then SQL Server will certainly not be able to understand the real size of the backup, since the data stored in the backup will be opaque to SQL Server.

If you are trying to retain a history of backup sizes for growth tracking, you could use the numbers stored in MSDB as a rough guide to percentage growth over time.  If you need exact sizes of the backups saved by Quest LiteSpeed, I would recommend contacting Quest's technical support to see if there is a way to do that.


Vertica-cluster architecture I know that MongoDB has master-slave architecture and Cassandra has peer to peer architecture.

I didn't find any resources about Vertica's architecture.

Is that master slave or peer to peer?
Does Vertica cluster allow multi-read and multi-write?

There is no concept of Master in Vertica.

But there is the concept of Initiator - this node will be the node who receives the connection and will compute the query plan, divide the workload among the nodes and then gather the result sets and format the output.

As for the multi-read and multi-write. Vertica is fully ACID complaint, this articles will give you more info.
https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/ConceptsGuide/Other/Transactions.htm


Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged? If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?

TRUNCATE TABLE and DROP TABLE are both metadata-only operations.  They make changes to the system catalog, including which extents are allocated to a particular object.  All these changes are logged.  But the cost is very small, regardless of the size of the table.

The only thing that will create log records for each row is DELETE.


How to detach corrupt database in SQL Server I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database.

Is this safe way to do that:

 DECLARE @DB_NAME SYSNAME = 'Database_name';

 BEGIN TRY

        -- Check if db has correct structure
        DBCC CHECKDB (@DB_NAME)  WITH  ALL_ERRORMSGS ;

        -- Stop using db
        EXEC( 'ALTER DATABASE ' +@DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ');

        -- Detach db
        EXEC sp_detach_db @dbname= @DB_NAME , @skipchecks= 'true';

    END TRY
    BEGIN CATCH

        -- DB files are missing - drop database
        IF ERROR_NUMBER() = 945
        BEGIN
            EXEC ('DROP DATABASE  ' + @DB_NAME);
        END
    END CATCH;

Like gbn commented, if the database is on storage that is not accessible, it is going to be set to suspect and the attempt to set it to single-user will always fail. The call to sp_dbremove will properly remove the entry from the database list, and it won't be attempted to be brought online next time you start the SQL service.

Be aware that sp_dbremove is deprecated, and DROP DATABASE should work just as well if the database is suspect.


T-SQL Count in single select I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited.

I've spent hours looking for alternatives for this but couldn't find anything that works. This works and I get correct results but is there a simpler way to do this?

SELECT l.UserName
     , US.Used
     , US.Created
     , US.MostRecent
     , US.Edited
    FROM [Users] US
    JOIN    (SELECT UserName
                  , COUNT(UserName) AS Used
                  , MAX([TimeStart]) AS MostRecent
             FROM [Log]
             WHERE ReportAction = 1
             GROUP BY UserName) AS l
        ON (Us.UserName = l.UserName)

    JOIN    (SELECT CreatedByID
                  , COUNT(CreatedByID) AS Created
             FROM [Catalog]
             GROUP BY CreatedByID) AS Cat
        ON (Us.UserID = Cat.CreatedByID)

    JOIN    (SELECT ModifiedByID
                  , COUNT(ModifiedByID) AS Edited
             FROM [Catalog]
             GROUP BY ModifiedByID) AS Cat2
        ON (Us.UserID = Cat2.ModifiedByID)

I would like to get both COUNT(CreatedByID) and COUNT(ModifiedByID) in a single select. For example I can get how many documents user has created with this:

SELECT CreatedByID, COUNT(CreatedByID) AS Created
FROM [Catalog]
GROUP BY CreatedByID

And how many documents user has edited with this:

SELECT ModifiedByID, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY ModifiedByID

But how to do those on a single select? I've tried something like this, but the results are not correct any more for ModifiedByID.

 SELECT CreatedByID, COUNT(CreatedByID) AS Created, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY CreatedByID

What you are trying to do can be resolved using two operations: an unpivot followed by a pivot.

The table that is most principal to the query you are looking for, Catalog, is document-centric. It stores data in this form:

What | Created by Whom | Edited by Whom

You, however, want a user-centric report. So, first of all, you need to transform the original set to a user-centric one:

Who | Did What | To What

The To What part is not strictly necessary in this case, perhaps, but I leave it for a better illustration of how the data are rearranged in the course of the sought-for transformation. And the transformation itself is called unpivot. Assuming the t-sql tag on your question means SQL Server rather than Sybase, an unpivot in your case can be implemented like this:

SELECT
  u.UserID,
  Action = LEFT(u.Action, LEN(u.Action) - 4)
FROM
  dbo.Catalog AS c
  UNPIVOT
  (
    UserID
    FOR Action IN (CreatedByID, ModifiedByID)
  ) AS u
;

The LEFT() trick simply cuts off the ByID bit from each name, leaving just the verb.

Another way would be like this:

SELECT
  u.UserID,
  u.Action
FROM
  dbo.Catalog AS c
  CROSS APPLY
  (
    VALUES
      (CreatedByID,  'Created'),
      (ModifiedByID, 'Modified')
  ) AS u (UserID, Action)
WHERE
  u.UserID IS NOT NULL
;

I am assuming that a document that has not been modified will have a null in ModifiedByID. Filtering out the Modified entries with a null user ID is what the WHERE clause in the above query for.

Now you could simply group the results by UserID, Action and count the rows in each group. That would give you the output values in this form:

UserID  Action    Count
------  --------  -----
100     Created   10
100     Modified  15
110     Created   8
...     ...       ...

But as you want Created and Modified as separate columns:

UserID  Created  Modified
------  -------  --------
100     10       15
...     ...      ...

you will need to group the results with a pivot. One way to do so is to use the native PIVOT syntax:

SELECT
  UserID,
  Created,
  Modified
FROM
  (
     ... /* the unpivot query (either option) */
  ) AS s
  PIVOT
  (
    COUNT(Action)
    FOR Action IN (Created, Modified)
  ) AS p
;

You could also achieve the same using the older syntax called conditional aggregation:

SELECT
  UserID,
  Created  = COUNT(CASE Action WHEN 'Created'  THEN 1 END),
  Modified = COUNT(CASE Action WHEN 'Modified' THEN 1 END)
FROM
  (
     ... /* the unpivot query (either option) */
  ) AS s
GROUP BY
  UserID
;

In the latter case the derived table can be avoided. Instead you would just need to use the FROM clause and, if necessary, the WHERE clause from either unpivoting method. That is, the final conditional aggregation query would look either like this:

SELECT
  u.UserID,
  Created  = COUNT(CASE u.Action WHEN 'Created'  THEN 1 END),
  Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
  dbo.Catalog AS c
  UNPIVOT
  (
    UserID
    FOR Action IN (CreatedByID, ModifiedByID)
  ) AS u
GROUP BY
  u.UserID
;

or like this:

SELECT
  u.UserID,
  Created  = COUNT(CASE u.Action WHEN 'Created'  THEN 1 END),
  Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
  dbo.Catalog AS c
  CROSS APPLY
  (
    VALUES
      (CreatedByID,  'Created'),
      (ModifiedByID, 'Modified')
  ) AS u (UserID, Action)
WHERE
  u.UserID IS NOT NULL
GROUP BY
  u.UserID
;

You can use the final query as a derived table to join the results to the Users table to further connect them with the results from the Log table – in much the same way as you are doing now.


MySQL table with 100,000 records queried often I have a single database of about 100 tables to store various kinds of information.

The most important table is our order table which is used to store customers orders and is over 100000 records as of now and growing.

This table is the most queried table in our database, for various parts of information needed from real time order dashboards, statistics, analytics etc.

I monitor the database on a regular basis and have slow queries enabled on the database to track issues.

I use scripts like mysqltuner to spit out query on a daily basis.

I also use mysqlsla to gather info about the top 10 slowest queries in our database.

sample stat
Count         : 11.48k  (30.66%)
Time          : 19.623758 s total, 1.709 ms avg, 239 µs to 2.475017 s max  (18.64%)
  95% of Time : 5.246833 s total, 481 µs avg, 239 µs to 1.095 ms max
Lock Time (s) : 14.460071 s total, 1.259 ms avg, 53 µs to 2.462555 s max  (41.38%)
  95% of Lock : 806.43 ms total, 74 µs avg, 53 µs to 137 µs max
Rows sent     : 1 avg, 0 to 9 max  (0.99%)
Rows examined : 6 avg, 1 to 28 max  (0.15%)

Most of the slowest queried involve the order table mentioned above. I use MyISAM as my storage engine so possible issues could be:

Table locking
Indexing issues

How could I improve these stats, I have indexing in place for these tables and have kept tweaking them to improve read queries.

Table schema

`orderid` int(11) NOT NULL AUTO_INCREMENT,
`cityid` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `model_type` tinyint(1) unsigned DEFAULT '1',
`userid` int(11) DEFAULT NULL,
`usertype` char(1) DEFAULT NULL,
`time` time DEFAULT NULL,
`ordercode` char(8) DEFAULT NULL,
`restid` smallint(3) unsigned NOT NULL,
`areaid` smallint(3) unsigned DEFAULT NULL,
`restname` varchar(50) DEFAULT NULL,
`date` date NOT NULL,
`del_time` time NOT NULL,
`status` tinyint(3) unsigned NOT NULL,
`amount` float NOT NULL,
`deliverycharge` smallint(4) unsigned DEFAULT '0',
`tax` float NOT NULL,
`total` float NOT NULL,
`extras` varchar(255) DEFAULT NULL,
`requests` varchar(255) DEFAULT NULL,
`discount` float DEFAULT NULL,
`rdiscount` float DEFAULT NULL,
`reason` varchar(255) DEFAULT NULL,
`rest_order` tinyint(1) unsigned DEFAULT NULL,
`admin_user` varchar(25) DEFAULT NULL,
`mode` char(1) NOT NULL,
`priority_order` tinyint(1) unsigned DEFAULT '0',
`payment_mode` tinyint(1) unsigned DEFAULT '0',
`km` tinyint(3) unsigned DEFAULT NULL,
`order_type` tinyint(1) NOT NULL DEFAULT '1',
`coupon_discount` smallint(3) DEFAULT '0',
`pickup_time` time NOT NULL,
PRIMARY KEY (`orderid`),
KEY `cityid` (`cityid`),
KEY `date_3` (`date`,`status`,`mode`),
KEY `orderid` (`orderid`),
KEY `time` (`time`),
KEY `userid` (`userid`,`usertype`),
KEY `restid` (`restid`,`date`,`status`)

slow log query

SELECT `a`.`orderid`, `a`.`date`, `a`.`status`, `a`.`restname`, `a`.`admin_user`, `a`.`model_type`, `b`.`name` as cityname
FROM `tk_order_queue` AS a
INNER JOIN `tk_cities` AS b ON `a`.`cityid` = `b`.`id`
WHERE `a`.`date` =  '2012-06-30'
AND `a`.`status` =  0
AND `a`.`mode` =  1
ORDER BY `a`.`orderid` desc;

You will have to compare the WHERE clauses and GROUP BY and ORDER BY statements of all your queries to make sure your current indexes can support them in their EXPLAIN plans.

Yesterday, I answered this question : InnoDB vs MyISAM with many indexes

In that question I suggested doing something to the MyISAM table that you can do as well

ALTER TABLE orders ROW_FORMAT=Fixed;

This will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. Your table will bloat to the maximum size for the row layout times the number of rows. You table could double or triple in size.

Where is the benefit? Your MyISAM table will then be read from/written to anywhere from 20% - 30% faster without changing anything else.

I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)


How to model an activity feed I'm having a hard time modeling an activity feed that should show events based on user roles. I'm using Postgresql 9.4.

I have a projects table. Each project has many users through a memberships table. The memberships table has a role column that stores a user's role in a given project. Standard stuff.

memberships
-------------------------------------
| id | user_id | project_id | role  |
|  1 |       1 |          5 | admin |
|  2 |       2 |          5 | user  |

I have a buckets, uploads, and documents tables. A bucket is essentially a folder, and it has many uploads, and an upload has many documents. A bucket has many documents through the uploads table.

buckets
-----------------------
| id | name  | public |
|  1 | Stuff | true   |

I need to track activities on memberships, buckets, uploads, and documents. This is fairly easy. I'm planning to use a polymorphic association. In an activities table I'll have these columns.

activities
----------------------------------------------------------------------
| id | user_id | project_id | action | trackable_id | trackable_type |
|  1 |       1 |          5 | create |            3 | Upload         |

Trackable id and type store the record id and table name of the trackable object, in this case an upload. This lets me easily retrieve, in a single query, a user's activities, and a project's activities, and render view templates based on the trackable object type, and the action type.

Problem

My problems start with buckets. Any activity on private buckets should only be visible to admins. This includes activity on descendents of private buckets: uploads, and documents that belong to those uploads.

Solution 1

The easiest way I can think of is to add a private column to the activities table. The lets me do something like this (this is Ruby pseudo-code, specifically Rails, but logic stands):

project = Project.find(1)
activities = if project.admins.include?(current_user)
  project.activities.all
else
  project.activities.public
end

This is not scalable though. If we add a table to track access to folders, and change permissions so that they go from:

  Admins see all activity, users see public bucket activity, activity on their uploads and documents that belong to public buckets, and membership activity.

To something like

  Admins see all activity, users see public bucket activity like above, and in addition they see activity that belongs to private buckets they can access.

Then the private column becomes meaningless in the activities table.

Solution 2

Another way is to add a bucket_id column to the activities table, and separate activities by ones that belong to projects or buckets.

activities
---------------------------------------------
| id | user_id | project_id | bucket_id | ...
|  1 |       1 |          5 |         3 | ...

For non admin users I can then run two queries. One for project activities, and one for bucket activities that are either public, or in the future, on buckets that the users have access to.

I don't like this approach because it's also not scalable. If we add a tasks table where tasks can also be restricted by access, we would have to add another column to the activities table, task_id, and so on.

Approach 3

Create a table for each activity type. Much like approach 2, but with normalized data. I still don't feel comfortable creating what essentially is three different tables with almost identical data.

Any ideas on the best way to approach this problem?

By having three tables in the database (e.g. activities, user, and user_activities) and a simple LEFT JOIN SQL query as the one below can be executed:

SELECT SELECT user.name AS user_name , activities.name AS activity_name
FROM user_activities
LEFT JOIN user ON user.userid = user_activities.user_id
LEFT JOIN activities ON activities.activityid = user_activities.activity_id

As for your approach:

Activities table,

id - Unique Stream Item ID
user_id - ID of the user who created the stream item
object_id - Internal ID of the object (currently either the buckets, uploads, or documents ID)
action_name - The action taken against the object
activity_date - Timestamp that the action was created.
hidden - Boolean of if the user has chosen to hide the item.
privacy - An id to a table with Privacy Groups Options


Query Cache utilisation ~50% Qcache_lowmem_prunes still high I ran two tests, each running for about a week

Test 1

Query Cache Settings

query_cache_limit: 50K
query_cache_size: 128M
query_cache_min_res_unit: 1K

Results

Qcache_free_blocks: 6,004
Qcache_free_memory: 93,598,504
Qcache_hits: 377,204,411
Qcache_inserts: 13,056,941
Qcache_lowmem_prunes: 384,610
Qcache_not_cached: 5,794,907
Qcache_queries_in_cache: 16,006
Qcache_total_blocks: 38,724

Results (Calculated Values)

Utilisation: 40.7%
Removal Ratio: 2.9%
Hit Rate: 95.2%
AVG Query Size: 4,012
Frag Indicator: 15.5%

Utilisation = 100-Qcache_free_memory*100/query_cache_size
Removal Ratio = Qcache_lowmem_prunes*100/Qcache_inserts
Hit Rate = Qcache_hits*100/(Qcache_hits+Qcache_inserts+Qcache_not_cached)
AVG Query Size = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
Frag Indicator = Qcache_free_blocks*100/Qcache_total_blocks

Test 2

Query Cache Settings

same as above except

query_cache_min_res_unit: 2K

Results

Qcache_free_blocks: 1,287
Qcache_free_memory: 70,082,320
Qcache_hits: 223,742,107
Qcache_inserts: 7,104,530
Qcache_lowmem_prunes: 186,235
Qcache_not_cached: 3,218,864
Qcache_queries_in_cache: 20,802
Qcache_total_blocks: 43,424

Results (Calculated Values)

Utilisation: 55.6%
Removal Ratio: 2.6%
Hit Rate: 95.6%
AVG Query Size: 4,217
Frag Indicator: 3.0%

now for the question....

As expected, when I lowered query_cache_min_res_unit, fragmentation went up. What really has me confused is why is the removal ratio still so high even when utilisation is low?

To use least amount of QC space, use

query_cache_min_res_unit=512

for less waste of QC RAM  and qcache_lowmem_prunes expected to decrease as well over time.


SQL Server mistaken update rows without backup I did a mistake UPDATE query yesterday. Database engine is MSSQL and the version is 2017.

I have not any backups. All I have is the database itself. Do you know any ways to redo my modifications to one second before the time which the damn query was triggered?

In addition, I tried transaction logs but every time I want to restore database to time before accidentally UPDATE I face to this message: No packupset is selected in SQL Server. I think it's because my TLog has not period of time due to I dad not backup any transaction log before.

DISCLAIMER
  Please be advised that these steps are by no way complete and do not guarantee a restore of your database and/or data. Please use them as a guideline in your specific situation and be aware that a multitude of unknown causes, could result in complete data loss.

  Step 1.) and Step 2.) can be used in any situation

  Further additional steps may be required to bring your data back.

Welcome to DBA.SE. Sorry to hear you're having a bad time.

Handling Your Current Situation

If FULL Recovery model is enabled for this database do the following:

Stop all transactions/connections to the database. NOW !!
Think about your next steps.
Take the database OFFLINE and make a copy of the *.MDF and *.LDF files.
Bring the database back ONLINE.
Perform a Transaction Log Backup (TLOG backup) with the option WITH ... NO_TRUNCATE .... This ensure the data isn't deleted from the TLOG.
This step will only work if the database has previously been backed up with a FULL backup
Restore database to point-in-time on a different database server using the initial FULL backup (before step 1.) and TLOG backup from the second step. The time should be before the accident happened.
Search for the modified data.
Extract data.
Import data into production server after confirming it is correct.
Bring production back online.

If you didn't have the database in FULL recovery model, then changing it now will have no impact. You won't be able to rescue the data, because it probably has already been committed to the database and is no longer available in the TLOG file. If no initial FULL backup has been performed, then these steps won't work.

Cleaning Up

Talk to management about what happened.
Implement a Change Management process.
Implement a solid backup procedure for your database systems.

DBA Tips

Here a couple of basic tips:

Always think about your next steps.
Ensure you have a backup, before you modify data.
If the data is important/productive, ensure you have FULL recovery model enabled and Transaction Log backup is being performed.
Only change data in a productive environment after management has told you to do so and if you have an official Change Request that has been signed off.
Never touch productive data manually, unless it is during a down-time and you have met the requirements for 1. and 2. and 3.


PLS-00306 dbms_scheduler.create_job I'm not an expert about DBMS_SCHEDULER.CREATE_JOB and i've been asked to create a job. When i run it i get these errors:

ORA-06550: line2,column 2
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'

My statement is:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'ME.Delete_org',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'BEGIN delete_org_sre_package.process_org_sre_deletions; END;',
   start_date         =>  '23-NOV-17 11.00.00 PM',
   repeat_interval    =>  'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=0; BYSECOND=0;',
   auto_drop          =>   FALSE,
   end_date           =>   NULL,
   enabled            =>   TRUE,
   comments           =>  'Every Friday at 11pm');
END;
/

Any ideas of what i'm doing wrong? Thanks

You need to review each of the parameters in the docs, located at https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72300.  Specfically, take a look at 'job_action' for your specified 'job_type' of "STORED_PROCEDURE".

"The action is the name of the stored procedure. "  (emphasis mine).  You are saying the action is a command -- "execute".

Also, does your procedure "delete_org_sre_package.process_org_sre_deletions" have any input or output parameters?


How to fix my Merge Replication upload order? I have several Merge Replication instances in production on SQL 2008 R2.  One of them has started to behave oddly in that it appears to attempting to upload the children tables before it uploads the parent.  This fails due to the foreign key of the children not having a valid parent row to reference.  Eventually the parent row is uploaded and then the children are retried, but often this can take an hour or so of retrying the rows in the children tables before the parent row is uploaded.  How can I track down and fix what is wrong with my merge replication so that it consistently uploads the parent rows before the children?

There are cases in Merge Replication when parent and child rows can be processed out of order on synchronization resulting in constraint violations and conflicts.

To understand how and why this can occur and how it may apply to your specific scenario, I recommend reading through:

How to understand Merge Replication article processing order
Merge Article Processing Order and using the "Not for Replication" Attribute

To immediately alleviate the constraint violations and conflicts from occurring on synchronization you can mark the foreign keys as NOT FOR REPLICATION.  This means that the foreign key constraint will not be enforced when the Merge Agent performs an operation.  To mark the foreign key constraint as NOT FOR REPLICATION please see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

Another approach is to increase -UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch Merge Agent parameters to avoid splitting parent and child changes across generation batches.


Read only/Standby database - Set user permissions Example database: WarehouseDB
Example user: reporting
Database state: Read only/Standby mode

How do I give the user 'reporting' access to only WarehouseDB without giving them sysadmin privileges?

The problem occurred when I restored a bak file to create WarehouseDB in norecovery mode and then restored another transaction log file to put it in read only/standby mode for doing select queries for reporting purposes. And the reason I do log shipping then onwards is because both production and warehouse database can be synced at all times without any manual intervention. Now if I give this user sysadmin permissions then of course they can query the database but due to security concerns, I can't allow them. Also because it's a read only database, I can't create another user either and map them to WarehouseDB.

Another thing I forgot to mention is, the source database name is for example 'ProductionDB' and when restoring it's changed to WarehouseDB because I have mirroring setup for ProductionDB on the mirrored server where WarehouseDB is then restored.

Any ideas on how to resolve this?

The user has to be granted permissions in the source copy of the database.

The restored copy is just that -- a copy -- of the source, so there's no way to change one and not the other.

Since this is a SQL Server login, you'll need to create the logins on both servers with identical SIDs so the mapping from the database user to the login matches in all cases. You can do this by recreating the login on the secondary, specifying the SID manually in the CREATE LOGIN statement.

If you went with a solution such as replication (not saying you should, just giving an example), this works using two read/write databases, where you could grant permissions completely independently.


How to prevent null foreign key during database normalization? I'm rather new to databases and am having a hard time trying to figure out how to structure my database. This database contains students, classes and all their grades both overall and in categories (such as tests, homework, etc.)

So far I have created the following tables:

Student: id|firstname | lastname (pk = id)

Class: id|name|room num (pk = id)

Categories: id|name|weight (pk = (id, name, weight))

Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val

My question is if I wanted to save the students overall grade with this schema how would I do it without having a null foreign key and preserve normalization?

Currently to store their grade I would have to have an entry like Grade: 1, 1, null, 'Overall Grade', 95 in the database.

What is the correct way to do this? Add another table, an extra row in category like: Category: 1, 'Overall', 100 or something else?

Edit:

Currently the primary key on the grades table is just the combination of the other columns.
    pk = (student_id, class_id, category_id, name)

NOTE

Many-to-Many relationships are represented by a linking table.

You are missing the link between Student(s) and Class(es)

create class_enrollment (
  student_id int not null references Student( id ),
  class_id   int not null references Class( id ),
  constraint class_enrollment_pk primary key ( student_id, class_id )
);

Adjustment

Your Grades table can then be modified to reference the PK of class_enrollment.

create table GRADES (
  student_id  int,
  class_id    int,
  category_id int,
  grade_val    number,
  constraint grade_pk primary key
     (student_id, class_id, category_id), -- a surrogate key is recommended
  constraint grade_fk1 foreign key (student_id, class_id)
     references class_enrollment(student_id, class_id)
);

Overall Grade

If you can always calculate a Summary value, You can hide the calculation in a VIEW (or a MATERIALIZED VIEW).

But, since teachers can override the calculated value, you'll want to store the Summary in the "parent table".

In your case, the "parent table" to Grades would be Class_Enrolement.

alter table class_enrollment add ( overall_grade  number );


Sort by partial key name in json column I have column document of data type json with logging data like:

CREATE TABLE foo(document)
AS
  VALUES (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288960": {
            "first_name": "First name"
        }
      }
     }'::jsonb
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288969": {
            "first_name": "Joe ZZZ"
        }
      }
     }'
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288169": {
            "first_name": "AAA ZZZ"
        }
      }
     }'
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__1288169": {
            "first_name": "kkk"
        }
      }
     }'
  );

Key name 'participant-information_primary__Primary__39011__2288960' is dynamic and can vary by the trailing integer number. I want to do something like:

ORDER BY document->'blocks'->'participant-information_primary*'->>'first_name'

How can I do it?

Using json_object_keys() like you found yourself already (or jsonb_object_keys() for jsonb), you can do this with simple inline SQL. I suggest a LATERAL subquery.

If, like your example suggests, there is exactly one key inside the key  'blocks', the solutions is simple:

SELECT *
FROM   tbl, json_object_keys(document->'blocks') AS part
ORDER  BY document->'blocks'->part->>'first_name';

The comma is short notation for CROSS JOIN LATERAL.

If there can be any number of keys, you need to be more verbose:

SELECT *
FROM   tbl
LEFT   JOIN LATERAL (
   SELECT part
   FROM   json_object_keys(document->'blocks') part
   WHERE  part ~ '^participant-information_primary__Primary__'  -- your pattern here
   LIMIT  1  --  just to be safe
   ) s ON true
ORDER  BY document->'blocks'->part->>'first_name';

The LEFT JOIN preserves rows where no matching key is found at all.

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?


Optimize table and query, aggregate by date or date-hour I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows:

CREATE TABLE sensor_data (
site INT NOT NULL,
station INT NOT NULL,
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
reading1 FLOAT DEFAULT NULL,
reading2 FLOAT DEFAULT NULL,
reading3 FLOAT DEFAULT NULL,
reading4 FLOAT DEFAULT NULL,
reading5 FLOAT DEFAULT NULL,
reading6 FLOAT DEFAULT NULL,
reading7 FLOAT DEFAULT NULL,
reading8 FLOAT DEFAULT NULL,
reading9 FLOAT DEFAULT NULL,
reading10 FLOAT DEFAULT NULL,
reading11 FLOAT DEFAULT NULL,
reading12 FLOAT DEFAULT NULL,
reading13 FLOAT DEFAULT NULL,
reading14 FLOAT DEFAULT NULL,
reading15 FLOAT DEFAULT NULL,
reading16 FLOAT DEFAULT NULL,
reading17 FLOAT DEFAULT NULL,
reading18 FLOAT DEFAULT NULL,
reading19 FLOAT DEFAULT NULL,
reading20 FLOAT DEFAULT NULL,
KEY Index_sst (site, station, time)
) ENGINE=InnoDB;

I need to query readings of a specific station aggregated by day or day-hour for a user defined interval. The queries are as follows:

Query 1

SELECT AVG(reading1), DATE_FORMAT(time, '%Y-%m-%d 00:00:00') AS daily
FROM sensor_data
WHERE site=1 AND station=1 AND time>='2010-00-00 00:00:00'
GROUP BY daily
ORDER BY daily;

Query 2

SELECT AVG(reading1), DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS hourly
FROM sensor_data
WHERE site=1 AND station=1 AND time>='2010-00-00 00:00:00'
GROUP BY hourly
ORDER BY hourly;

These queries don't use index, but use temporary table and filesort.

How should I get rid of long execution time of these queries? Adding a separate date column and indexing site-station-date seems to improve Query 1, but no idea about Query 2. I am wondering how the optimal way could be in this case.

SQL Fiddle

Create a new column, update it with the daily value,and add this column in the index.
Here is an example.This is just to remove the function-on-column situation,where an index can`t be used.Be patient,it will take a while on 100 M rows.

SQL Fiddle


User is in multiple Active Directory groups with multiple roles in database I have a user that is a member of multiple Active Directory (AD) groups.  Within SQL Server 2012, these AD groups have been assigned to different database roles.  This user is not getting the correct privileges unless I add the AD user directly to the role (they are still a member of the groups as well). Is there an issue if a person is a member of different roles or would this be an AD issue when adding to SQL Server?

Preferably I would like only AD groups and not individual AD users within the databases.

One user in-particular is in a db role that has read access to a group of tables, another role that allows read write access to all tables.  Both roles are based on AD groups she needs to be a member of.  But because she is in both roles and AD groups, it is not allowing read write access unless her AD user is added to the read write role. (the AD group won't work)

First step is to validate that the user is seen as a member of both AD groups by SQL Server by running the below as a sysadmin:

EXEC xp_logininfo 'DOMAIN\UserName', 'all';

The returned table should show all paths of server access for this user, i.e. one row for each AD group the user is a member of that provides access to SQL Server. If the group does not appear, verify in AD that they are in fact a member.

Next, have the user log out and back in from the machine from which they're trying to access SQL Server. Their AD Group membership is contained in a security token issued by the DC when they log in. If they haven't logged out and back in since being added to the AD Group then the security token will not reflect their current membership status and so the write access won't be granted.


MySQL warnings while loading integer data into table (SOLVED) For some reason, after loading a large amount of data into the following table (from a single file), all of the values register as 0.

CREATE TABLE secondTable
(
freq int NULL,
w1 int NULL,
w2 int NULL,
w3 int NULL
)

Then loading the data:

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE secondTable

For some reason, all of the data comes up as 0. The data file is 1.0GB (hardly unprecedented). The datafile also has appropriately formatted headers in the same style as other data files that have successfully been loaded into other tables.

EDIT: The problem is clearly tied to some sort of warnings during loading:

Query OK, 18976277 rows affected, 65535 warnings (5 min 49.74 sec)
Records: 18976277  Deleted: 0  Skipped: 0  Warnings: 75905108

Here are the type of warnings I'm getting:

************* 1. row *************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'freq' at row 1
************* 2. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w1' at row 1
************* 3. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w2' at row 1
************* 4. row *************
  Level: Warning
   Code: 1261
Message: Row 1 doesn't contain data for all columns
************* 5. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'freq' at row 2
************* 6. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w1' at row 2
************* 7. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w2' at row 2
************* 8. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w3' at row 2
************* 9. row *************

In Python, the lines look like this:  \x001\x00\t\x006\x00\t\x002\x008\x001\x005\x001\x00\t\x002\x007\x004\x003\x002\x004\x00\r\x00\n

UPDATE: UPDATE: I solved the problem by using

file = codecs.open('data.txt','r','utf16')

in Python, then writing to a new file to open in MySQL. Apparently, it was formatted in Little Endian UTF-16.

Based on the error messages provided.  It would appear that MySQL is reading the values as an empty string '', in other words NULL which is why you are seeing the 0 values.

I would look closely at the data format in the file.  Providing a small sample of the actual data would be helpful.


SSRS 2008 Security - can't run reports I have SSRS2008 set up and running locally on the server (SRV0100).  It is set up to run under the localsystem account for the service account (default).

I changed the rsreportserver.config to use RSWindowsNTLM.

I have set domain users up to have browser access to the reports.

However - on other PC's, when I try to get to the report, it says:

Error Message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the web server's administrator to give you access.

I added 'Everyone' to the report as well as domain\Domain Users.  Still the same issue even when restarting the SSRS service.

Any help?  I feel like I have read and tried everything :(

thanks in advance!

Andy

ok, so called a buddy.  He mentioned another post that mentioned to grant the appropriate AD user(s) read and execute privilidges on the Reporting Services directory.

i did that and it worked.  Hopefully this helps someone else as well.


ORACLE- Database unable to START/OPEN When I'm trying to start a database (after a server patch was applied), I get the following error and I am unable to open the database.

SQL> startup;
ORACLE instance started.

Total System Global Area 7465926656 bytes
Fixed Size                  2267744 bytes
Variable Size            1241515424 bytes
Database Buffers         6207569920 bytes
Redo Buffers               14573568 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1:
'/u00/oracle/app/oracle/product/oradata/XRO/sy_XRO.dbf

NOTE:

SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

Generally you can open standby databases as read only. Specifically you can open data guard standby databases as read only. Did you check to see if all of the data files exist? That a database is mounted, means that the control files are where they are supposed to be, not corrupted and your init/spfile is valid.

Did you patch the Oracle Home for the standby database with the same patch as the parent? Can you force database recovery on the child to get the databases in sync.


issues with creating varchar returning functions I have an issue while creating functions in sql server, am very new to sql server
Below mentioned is the code which i used, please any issues with syntax.

CREATE FUNCTION Fn_Check_Customer_Credit (@AcctCode VARCHAR(20), @CurrentAmount NUMERIC(20,2), @CurrentDate DATETIME, @CompCode VARCHAR(20), @CurrentUser VARCHAR(20))
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @Str_Block_Limit     VARCHAR(100)
  DECLARE @Str_Check_Days      VARCHAR(100)
  DECLARE @Str_Acct_Lock       VARCHAR(10)
  DECLARE @Str_Return          VARCHAR(100)
  DECLARE @Num_Credit_Limit    NUMERIC(20,8)
  DECLARE @Num_Credit_Days     NUMERIC(20,8)
  DECLARE @Num_Current_Credit  NUMERIC(20,8)
  DECLARE @Num_Total_Pending   NUMERIC(20,8)
  DECLARE @Num_Temp            INT
  DECLARE @Num_CurrentBal      NUMERIC(20,8)

  SELECT @Num_Temp = COUNT(*) FROM GN_USER_SETTINGS_DETAIL WHERE USER_NAME = @CurrentUser AND SETUP_CODE = 3;  -- Credit Limit Exception
  SELECT @Str_Block_Limit = CONFIG_VALUE FROM GN_ELIXIR_CONFIG WHERE CONFIG_NAME = 'BLOCK_CUSTOMER_CREDIT_LIMIT' AND COMP_CODE = @CompCode;
  SELECT @Num_Credit_Limit = CREDIT_LIMIT, @Num_Credit_Days = CREDIT_DAYS, @Str_Acct_Lock = LOCK_ACCT FROM AC_SL_MASTER WHERE ACCT_CODE = @AcctCode;
  SELECT @Num_CurrentBal = (SUM(A.AMOUNT) + SUM(A.DN_AMOUNT))
            FROM (SELECT SUM(ISNULL(DOC_BALANCE, 0)) AMOUNT, 0 DN_AMOUNT
                  FROM Fn_Aging(@CurrentDate,@CompCode,@AcctCode)
                  UNION ALL
                  SELECT SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) AMOUNT, SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) DN_AMOUNT
                  FROM VW_PENDING_DNOTE
                  WHERE ACCT_CODE = @AcctCode) A;
  IF @Str_Acct_Lock = 'Y'  -- CURRENT CUSTOMER IS LOCKED.
    RETURN 'Account is Locked';
  IF @Str_Block_Limit = 'N'  -- NO NEED TO CHECK THE CREDIT LIMIT.
    RETURN NULL;

  IF ISNULL(@Num_Temp, 0) > 0  -- CUSER IS ALLOWED TO ENTER BEYOND THE CREDIT LIMIT OF THE CUSTOMERS.
    RETURN NULL;
  IF (@Num_Credit_Limit  0)  ---- CURRENT CREDIT IS GREATER THAN OR EQUAL TO THE CREDIT LIMIT.
    RETURN 'Credit Limit Exceeded.';
  ELSE
    RETURN '0';
END;

From the documentation:

      --Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

So, the return statement must be the last one. Use a variable to store your return value and add in the end return @retval.


Problem with InnoDB "per table" file sizes I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file.

I have truncated the table now it has zero record. But the watchdog.ibd have same size.

What's the issue.? Does truncate does not work for removing the data from the .ibd file..?

I don't want to drop the table because in need that table.

Please keep in mind that TRUNCATE TABLE is a DDL command that tells the InnoDB storage engine to pretend there are no records.

According to the MySQL Documentation on TRUNCATE TABLE :

  When fast truncation is used, it resets any AUTO_INCREMENT counter to
  zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to
  zero by TRUNCATE TABLE, regardless of whether there is a foreign key
  constraint.)

However, doing TRUNCATE TABLE does nothing for space already allocated.

To quickly drop the space down to zero rows, you can do the following:

#
# Algorithm 1
#
CREATE TABLE watchdog_new LIKE watchdog;
ALTER TABLE watchdog RENAME watchdog_zap;
ALTER TABLE watchdog_new RENAME watchdog;
DROP TABLE watchdog_zap;

Since TRUNCATE TABLE tells mysql that the table is considered empty, you can do this:

#
# Algorithm 2
#
TRUNCATE TABLE watchdog;
ALTER TABLE watchdog ENGINE=InnoDB;

These two steps behave the same way as Algorithm 1.

These steps also can be done with

#
# Algorithm 3
#
TRUNCATE TABLE watchdog;
OPTIMIZE TABLE watchdog;

Internally, OPTIMIZE TABLE would perform Algorithm 1 and then perform ANALYZE TABLE. For InnoDB, the ANALYZE TABLE phase of OPTIMIZE TABLE is bypassed. In fact, it is unnecessary because index statistics are recomputed on each query executed against an InnoDB table.


Database Stuck in "Restoring" in SSMS After Running From an Outside Process I am running a RESTORE DATABASE procedure with RECOVERY and REPLACE from an outside process (Windows Batch using a Python script) and when I do so, it makes my database stuck in "Restoring..." mode every time.  But if I run the procedure manually, it works just fine.  Any thoughts on what might cause this?  I have come up blank in my research [I wasn't sure whether to put this here or the Python community, so I started here].

For example:

Python executes this:

exec DB_RESTORE 'MyDatabase','MyDatabase_BACKUP'

Which runs this:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE DB_RESTORE
    @FROMDB VARCHAR(MAX) = '',
    @TODB VARCHAR(MAX) = ''
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX)
    DECLARE @FILENAME VARCHAR(MAX)
    DECLARE @PATH VARCHAR(MAX)

    SET @PATH = 'C:\temp\'
    SET @FILENAME = @PATH + @FROMDB + '_' + 'backup.bak'
    SET @SQL = ''

    SET @SQL ='RESTORE DATABASE ' + @TODB
    SET @SQL = @SQL + ' FROM DISK = ''' + @FILENAME + ''''
    + ' WITH RECOVERY, REPLACE, STATS=10'

    PRINT @SQL
    EXECUTE(@SQL)
END
GO

The problem is that you need to give it time so the database restores.

When you run your python code to execute the stored procedure, don't close the connection instantly, but pop a time.sleep(10) after it. This will give the database some time to prepare it to the online state (the time you need to sleep will vary based on db size).


Scripting a maintenance plan I created a maintenance plan for my SQL Server 2008 R2 using SQL Server Management Studio (SSMS).

However, I am unable to export/convert it to a script, in case I need to redo it on another server.

Is there a way?

If no, will there be a way with a newer version of SQL Server?

When you create job using maintenance plan, a job is automatically created in SQL Server Agent under Jobs. So now go to SQL Server Agent, expand jobs, and look for your job. Right click on it and select Script job as... Drop and recreate to... New query editor window.

Edit:

You can export the maintenance plans as well. You need to connect to Integration Services of the instance. Expand the stored packages then MSDB then expand Maintenance plans. Right click on MP and select a export package. Then you can move the package to another SQL Server.


Why is the backup sizes of AlwaysON replicas different? We have SQL Server 2014 Always ON set up with a primary and just one secondary replica. I have been using Dell LiteSpeed to take Database backups (full & tlog) on secondary replica in order to off load that traffic from Primary. We had one developer truncated an important table so we had to restore the primary database from the full backup and tlog backups. I took a full database backup on primary first before restoring and noticed that it is just 34 GB as opposed to the backup on secondary which is almost 70 GB. After restore, people complaint that performance is slow. My question is that why the database backup on secondary replica is double the size of primary. TIA.

The size of the database is always exactly the same on both primary and secondary replicas. The difference you are noticing on the backup file size could be because one of the two reasons

You didn’t use compression on secondary and applied compression when you took the backup on primary server.
As you already mentioned you were using Dell Lite Speed, which has different algorithm and compression level which also make a difference in your backup file size.

The performance issue could be because of different reasons. After you restore the database, make sure to check and if necessary to update the statistics, Index fragmentation level, and the query plan cache which usually has significant performance influence after a database restore.


SQL Server (2012) grant right to regular user to set database in read-only mode i would like to grant right to specific regular users on an sql server 2012 the right to set the database from read-write mode into read-only mode and vice versa. but i would not like to grant them any other administrative privileges.

how do i achieve that? do i have to create a stored procedure or a new database schema? or is there a schema that incoorporates that already?

the code needed to set the database into read-only mode goes as follows:

ALTER DATABASE database-name SET READ_ONLY

or

ALTER DATABASE database-name SET READ_WRITE

cheerio :)

The simple answer is:

GRANT ALTER ON database::[yourDB] TO [yourLogin];

That may, however, give more permissions than you intend. If you want to lock that down, you could look into module signing. That is, you'd create a stored procedure that, given a database name (or perhaps none at all depending on your use case) flips the state. Then, you "bless" the procedure such that it can run in an elevated context.


Updating TNS Entry across multiple terminals Is there anyway I could update Host in a specific TNS entry, by way of an Batch File(sqlplus via command prompt)?

A change in the IP address have been raised and we need to update the TNS across several terminals. Any easy way to handle it rather than editing the TNSnames.ora file?

Your expertise in this is very much appreciated?

You will do  it one time; but i suggest to use dns names entry in the tnsnames and if a change have to be made it will be on a centralized place.


Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each  day is divided into 24hrs/2 = 48. There is a time slot.

Now I want output like this :-


Unable to import mysql.connector in a Python venv session I've used venv to create a virtual environment for Python 3.5.2. I have installed the mysql.connector. The connector works fine as long as I am not in an activated venv environment.

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

With these statements I get the following:

Traceback (most recent call last):
  File "fetchone.py", line 1, in <module>
    from mysql.connector import MySQLConnection, Error
ImportError: No module named 'mysql.connector'

I have searched for a solution but most are dated several years ago. Like I said, the mysql.connector is located and works fine outside of the venv environment

You might want to check out the following article:

Can not get mysql-connector-python to install in virtualenv (Stackoverflow)

The accepted answer states the following:

  Several things. There is an inconsistency in package naming so you may want to do:

pip search mysql-connector

  to find out what it is called on your platform. I got two results mysql-connector-python and mysql-connector-repackaged.

  so try this first:

pip install mysql-connector-python

  this may additionally give an error like this:

Some externally hosted files were ignored (use
  --allow-external mysql-connector-python to allow).

  so finally this should do the job:

pip install mysql-connector-python --allow-external mysql-connector-python


Is there a way to connect to postgres when have error "sorry, too many clients already "? I want to see what application is the cause, but even I change to user postgres and run psql, it still throw error

Slots reserved for postgres superuser
PostgreSQL reserves a few connections for superusers. If your application is not using superuser connections you can connect as a superuser (eg postgres). Then run diagnostic queries like:
select * from pg_stat_activity;

superuser_reserved_connections setting
See the superuser_reserved_connections setting in the Postgres configuration. Defaults to 3 reserved slots.
To quote the version 11 documentation:

Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted.
The default value is three connections. The value must be less than max_connections minus max_wal_senders. This parameter can only be set at server start.