Log file shrinking automatically due to UserShrinkSize property? We have a SQL Server 2014 system that is apparently shrinking one or more log files automatically.  We are getting a low free disk space alert (triggered at <5% free) at 1:00 AM, but when we check the system in the morning there is about 50% free.  We manage about 160 SQL Servers between the various environments and they're all set up with a good degree of standardization.  Auto shrink is NOT enabled.  I have found what was causing all of the database writes to cause the log files to grow, and that has been addressed, so the cause of the log file growth is not the subject of this question.

I found this SQL Server 7 era post, Transaction log shrinking mysteriously, which states that transaction logs will be shrunk automatically if UserShrinkFile is set to anything other than -1.  This seems so explain what we've noticed, but with a bit of a wrinkle.  There is no UserShrinkFile property in the header, but there is a UserShrinkSize property, and on three of the databases it is set to a positive integer.

Oddly, I cannot find anything salient about UserShrinkSize.  Anywhere.  The only place I've found reference to it is posts that just list it along with everything else in the header.  I assume it is the newer version of UserShrinkFile, and that it works that same way as stated in the linked article above.  However, getting some confirmation  of these assumptions would be fantastic, and this would be a lovely addition to utilities that check for configuration items that might cause unwanted behavior.

It would be good to know how this property affects the system, as it obviously doesn't just shrink the log files immediately all the time.  Right now all three of the databases that have a positive UserShrinkSize value have log files ranging from 40 - 200 GB with 99% free space, so it's leaving me wondering whether it only shrinks them when it can't grow the log file.

I couldn't add a comment so I will try to comment here:

There are 2 ways how SQL Server clear the t-log:

database using SIMPLE recovery model - when a checkpoint occurs, log will clear
database using FULL or BULK_LOGGED recovery model - when you issue a log backup

the only time the t-log will shrink if you issue SHRINKFILE. Maybe you are confused about log shrinking and log clearing?..

Update:

I was able to reproduce the positive value on UserShrinkSize column. The value on UserShrinkSize is the number of (8kb) pages needed to be shrink. -1 means default (none). So when you see a positive value, that means it tries to shrink the log but it can't because there's active transaction going on.

Tested on SQL Server 2016:

USE master
GO
DROP DATABASE IF EXISTS [UserShrinkSizeTest];
GO
CREATE DATABASE [UserShrinkSizeTest]
 ON  PRIMARY
( NAME = N'UserShrinkSizeTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER16\MSSQL\DATA\UserShrinkSizeTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'UserShrinkSizeTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER16\MSSQL\DATA\UserShrinkSizeTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 8192000KB )
GO
ALTER DATABASE [UserShrinkSizeTest] SET COMPATIBILITY_LEVEL = 130
GO
ALTER DATABASE [UserShrinkSizeTest] SET RECOVERY FULL
GO

Build your table:

USE [UserShrinkSizeTest];
GO

DROP TABLE IF EXISTS [dbo].[RecordsToProcess];
GO
CREATE TABLE dbo.RecordsToProcess
(AccountID char(8000)
,UserID int
,ServerID int
,HomeID int
,UserTypeID int
,UTCDateCreated DATETIME2(7))
GO

open a new query window and execute the insert:

USE [UserShrinkSizeTest];
GO

BEGIN TRAN
INSERT INTO dbo.RecordsToProcess (AccountID, UserID, ServerID) VALUES (REPLICATE('abcd', 5000000),1,1)
GO 50000

While the insert window is executing, open another window for shrink and execute it at the same time:

USE [UserShrinkSizeTest]
GO
DBCC SHRINKFILE (N'UserShrinkSizeTest_log' , 80)
GO

Now, you can check the UserShrinkSize:

DBCC TRACEON(3604)
DBCC fileheader([UserShrinkSizeTest])
--DBCC PAGE ([UserShrinkSizeTest],1,0,3)
DBCC TRACEOFF(3604)

as you can see below, the value is now positive. 65024 pages needed to be shrink.

you will see the change in value when you issue the SHRINKFILE and the t-log can't be shrink due to active transaction.

you can also check the changes using the fn_dblog function:

USE [UserShrinkSizeTest]
GO
SELECT Operation, Context, [Description] FROM fn_dblog(NULL,NULL)
WHERE [Description] LIKE '%Shrink%' AND Context = 'LCX_FILE_HEADER'

There is no magic about the UserShrinkSize, the value have changed because we issued/attempted DBCC SHRINKFILE during active transaction. Before shrinking the log, make sure to check the log_reuse_wait_desc and see what's holding the t-log. Investigate on why the t-log did not clear and why t-log expanded unexpectedly.

We should not worry about the UserShrinkSize value rather we should focus on how to properly manage the t-log. properly size the log, check long running transaction that cause log to grow, etc. If we take care of t-log the value will always be -1. :)

To bring back the value to -1, from my example I need to rollback the inserts. Then issue a SHRINKFILE on t-log (pre-size it to an appropriate size) and the log_reuse_wait_desc is now showing as NOTHING.

HTH


How do I row count into groups? I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them in set 1. Next three are 0,0,1 and I want them in set 2.

Order is important as I'm matching against a pattern table to get an overall count of the binary patterns.

I can see that Row would help but I don't know how to make row count by 3'ss.

Thank you.

Rows in a table don't have order. You will need an additional column to enforce the order, if order is important for you.


Extended Events XEL file reader; Is there a FileName property avaialble in QueryTableXEventData or SqlBulkCopy Using the QueryTableXEventData class in Powershell I am able to parse 100's of XEL files very quickly and export their content to a table in SQL Server using SQLBulkCopy
Example:
$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData
("\\some_file_path\XELog_Files*.xel")

I don't see though a File property in the the $fields array.
$event | Select-Object -ExpandProperty Fields

is_cached     System.Boolean              True
is_recovered  System.Boolean             False
is_dac        System.Boolean             False
database_id   System.UInt32                 73
packet_size   System.UInt32               8000
options       System.Byte[]  {32, 0, 0, 40...}
options_text  System.String
database_name System.String

Or in $events
Name      : login
UUID      : 13e22e12-3cb8-49bf-a3e1-131faa95601c
Package   : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropPackage
Metadata  : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropEventMetadata
Timestamp : 18/08/2020 03:41:37 +00:00
Fields    : {is_cached, is_recovered, is_dac, database_id...}
Actions   : {server_instance_name, database_name, database_id, client_hostname...}
Location  : Microsoft.SqlServer.XEvent.Linq.EventLocator

Is there a property I can use in SQLBulkCopy or QueryTableXEventData that has the file name currently being read or exported?

I don't believe the file name is exposed via a property.
Instead of passing a wildcard path to the QueryableXEventData constructor, you could get a list of the files and process each individually:
$eventFiles = Get-Item "\\some_file_path\XELog_Files*.xel"
foreach($eventFile in $eventFiles) {
    $events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($eventFile.FullName)
    foreach($event in $events) {
        # event processing here, using $eventFile.FullName or $eventFile.Name
    }
}


How to check what type of lock an UPDATE statement uses? How can I determine if an update statement puts a table lock, page lock, or row lock on a table?

You can see the locks from DMV called sys.dm_tran_locks. The type is of lock can be seen from resource_type column (f. ex. OBJECT, PAGE etc).

More information is available in MSDN

Also, maybe this query by Jonathan Kehayias is useful:

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id


Hashtags and synonyms table design So what I'm trying to do is setup a tag and synonyms structure. Where you can assign a tag as a synonym to another tag.

E.g for tag: photographer

I'd want to add 'photography, photographers, photo' as synonyms for 'photographer'.

The way I've proposed is that all synoynms are created as tags & that I associate a tag with a synonym by doing a 1<->1 relationship on a second table.

Here is what I'm proposing:

Tag:

tag_id
title
More meta data fields here...

Synonyms:

In this table we can attach a tag as a synonym to another tag (e.g photo can be a synonym for photography)

tag_id (the tag which we're attaching a synonym to)
synonym_tag_id (the tag_id which will be a synonym)

So I can add many synonyms to one tag.

Is this solution viable? Could it be improved somehow? I will be writing this schema in postgres. Appreciate any tips :)

Use cases:

Users of our app will be able to attach tags to there articles. If the user adds photopgraphy as the article title, our search will also show articles that have the photopgraphy synonyms attached as a tag.
Note that we aren't using SQL for search, we're using elaticsearch. The sql will just be used for storing the data so it can later be imported into elasticsearch.

So - each tag can have one or more synonyms, and each synonym belongs to a particular tag.  Seems straightforward.  A few things that I'd suggest, are:

(1) Make sure that the synonyms as such are thoroughly CHECKed before they get INSERTed.  I have seen situations where photography / Photography / photography{blank} was in a stored in a lookup table - although these 3 words have one and the same meaning for an end user (you could use UPPER() or LOWER() and RTRIM()/LTRIM() to prevent this).  Write the CHECKs in your DDL code wherever possible.

(2) I'd use a UNIQUE constraint on the "description" column in the SYNONYMS table (eg if you don't want to have duplicate synonyms - because the FK alone will probably not prohibit this).

(3) Also: are you absolutely sure that a synonym that has a rather broad meaning (something like the name of a supertype) cannot be used for several tags?  In your synonyms example photography, photographers, photo - suppose we have a TAG called "images", then "photo" could be a synonym for this, too (apart from being a synonym for "photographer").
You can still model this, but you'd probably need an "intersection" table for this purpose, as you suddenly end up with a many-to-many relationship between TAGS and SYNONYMS.  (Please feel free to ask some more questions about this, if it is not clear to you).


pg restore - where is the schema? I successfully restored a dump from Postgresql 11 into 9.3.9.
postgres=# drop database ambari
DROP DATABASE
[dumps]$ pg_restore -U postgres  -h db ambari.dump
-- Dumped from database version 11.7
-- Dumped by pg_dump version 11.7
-- Name: ambari; Type: SCHEMA; Schema: -; Owner: ambari
CREATE SCHEMA ambari;
ALTER SCHEMA ambari OWNER TO ambari;
SET default_tablespace = '';
-- Name: adminpermission; Type: TABLE; Schema: ambari; Owner: ambari
CREATE TABLE ambari.adminpermission (
  permission_id bigint NOT NULL,
  permission_name character varying(255) NOT NULL,
  resource_type_id integer NOT NULL,
  permission_label character varying(255),
  principal_id bigint NOT NULL,
  sort_order smallint DEFAULT 1 NOT NULL
);
ALTER TABLE ambari.adminpermission OWNER TO ambari;

etc
-- PostgreSQL database dump complete

When I list the databases, there is no ambari even there was in the original database.
[dumps]$ psql -U postgres  -h db
postgres=# \l
                               List of databases
Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
jiradb7     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

I cannot find it between the schemas either:
postgres=# \dn
List of schemas
Name  |  Owner
public | postgres

postgres-# select nspname from pg_catalog.pg_namespace;
  nspname
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

My question where the ambari schema is?
Update:
[dumps]$ psql -U postgres  -h db
psql (11.7, server 9.3.9)
postgres=# create database ambari;
CREATE DATABASE
[dumps]$ pg_restore -U postgres  -h db -d ambari ambari.dump
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized configuration parameter "idle_in_transaction_session_timeout"
Command was: SET idle_in_transaction_session_timeout = 0;
pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized configuration parameter "row_security"
Command was: SET row_security = off;
WARNING: errors ignored on restore: 2

postgres=# \l
                               List of databases
Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
ambari      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

postgres=# \c ambari
psql (9.2.24, server 9.3.9)
You are now connected to database "ambari" as user "postgres".
ambari=# \d
No relations found.
ambari=# \dtvs ambari.*
                List of relations
Schema |             Name              | Type  | Owner
ambari | adminpermission               | table | ambari
ambari | adminprincipal                | table | ambari
ambari | adminprincipaltype            | table | ambari

You didn't restore the dump. Your pg_restore invocation output the contents of the dump to standard output. To restore the dump into a database, you have to add the -d option:
pg_restore -U postgres -h db -d databasename ambari.dump

The second attempt seems to have succeeded. You probably don't see the tables because they are not on your search_path. Try \dt *.*.


How to reference a table called `\` (simply just backslash character) in MySQL in a query? Through some unknown circumstances I have a table called "\" in a database.

mysql> show tables like '\\';
+-----------------------------+
| Tables_in_in_realestate (\) |
+-----------------------------+
| \                           |
+-----------------------------+

On the disk the files are called @005c.

user@host:mysql/database/$ ls @005c.*
@005c.frm  @005c.ibd

I want to rename it to something more manageable, but I can't reference it in a RENAME TABLE query.

mysql> RENAME TABLE `\` TO `backslash`
    `> `          <-- it thinks there is an open quote
    -> \c         <-- so I just cancel the query

I thought I could escape the "\" with another "\" but that doesn't seem to work either:

mysql> RENAME TABLE `\\` TO `backslash`;
ERROR 1017 (HY000): Can't find file: './in_realestate/@005c@005c.frm' (errno: 2 - No such file or directory)

How can I reference the table in my RENAME TABLE query?

Somehow this query actually works...

RENAME TABLE `\` TO backslash; `;

Two statements, three backticks in total - neither statement would work by itself but somehow together they do the job. Despite giving an error.

To test this in a vacuum you can do:

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `\` ( `foo` int(10) ) ENGINE=InnoDB CHARSET=utf8; `;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`' at line 1
mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| \             |
+---------------+
1 row in set (0.00 sec)

mysql> DROP TABLE `\`; `;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`' at line 1
mysql> show tables;
Empty set (0.00 sec)

Please note that the syntax for this command is quite peculiar: it is the word "DROP" followed by "TABLE" followed by the string "\; `;" - that is backtick, then backslash, backtick again and then semi-colon. This is further followed by space, then (another) backtick and finally semi-colon.


Hide column values for some rows in auto-updatable views while retaining the "write" capability for them in PostgreSQL Suppose i have a view defined like this

create or replace view clients as
    select
    id, name,
    case app_user_type() -- hide column content for everyone except the administrator
        when 'administrator' then address
        else null
    end as address
from data.clients

The purpose is to hide the contents of the address column in some cases (current_user <> 'administrator').

The problem is that the "address" column becomes readonly.
Is there a way to have the same effect (hide the column value) while retaing the updatable property?

I went with something like this

create or replace view clients as
    select id, name, address
    from data.clients;
grant select (id, name) on clients to employee;

The only downside is that for employee role select * will fail, but for my use case, specifying the columns is ok


How to update only part of a field A user entered a batch of records with the wrong suffix in the record name - FY15-Q2 instead of FY16-Q1. So, the records are something like:

-LAG-FY15-Q2
-AN-FY15-Q2
-OY-FY15-Q2
-RV-FY15-Q2
etc

when they should be

-LAG-FY16-Q1
-AN-FY16-Q1
etc

There are a fairly limited number of prefixes, so I could always do multiple update statements like SET record name = LAG-FY16-Q1 WHERE record name = LAG-FY15-Q2.

It strikes me there might be a more elegant way to do this with a single update statement.  Something with trim, perhaps?  Complicating this is the fact that some of the prefixes are three letters and others are two.

You can try something like this:

UPDATE Set name = REPLACE(t.name, r.bad, r.good)
FROM yourTable t
INNER JOIN (values
    ('FY15-Q2', 'FY16-Q1')
    , ('FY25-Q2', 'FY36-Q1')
    , ...
) as r(bad, good)
    On t.name like '%'+bad

You can add multiple pattern after FY25-Q2.

It should work fine if you don't have multiple matches. It may not be really efficient if you have tons of rows.

If your pattern is in the middle of the string, add a % at the end as well: On t.name like '%'+bad+'%'.

Before running the full update, you should first replace UPDATE by SELECT and look at what should be done:

SELECT t.name, newname = REPLACE(t.name, r.bad, r.good), r.bad, r.good
...


SQL Server columnstore metadata (pushdown) filter AFAIK columnstore indexes store min/max values for segments (1 million rows), so this information can be used to prune not needed data.
This feature seems to work with equality predicates, but not with more complex cases like joins or subquery filters.
In SQL Server 2014, is it possible to use columnstore metadata filtering with joins or subqueries?

Below is my test case:

--create table generate_series with 67_108_864 rows (runs about 5 mins)

set nocount on;

create table generate_series(c bigint);
insert into generate_series select 1;

declare @i int = 26;
while @i != 0
begin
    insert into generate_series WITH (TABLOCK)
    select
        (select count(*) from generate_series)
        + row_number() over (order by (select null)) as c
    from generate_series;

    set @i = @i - 1;
end;

create nonclustered columnstore index cstore on generate_series(c);

--Test queries

-- 0 seconds (very fast)
select * from generate_series where c = 100;

-- 8 seconds (slow)
select 100 as c into #tmp;
select * from generate_series where c in (select c from #tmp);

The performance optimization that you are describing is known as rowgroup elimination. You can get rowgroup elimination with both queries in both SQL Server 2016 and SQL Server 2014 but SQL Server 2014 has additional restrictions.

Let's test in SQL Server 2016 first. The following query finishes very quickly:

select *
from generate_series
where c = 100
OPTION (MAXDOP 1);

SQL Server 2016 offers additional diagnostic information about rowgroup elimination through SET STATISTICS IO ON; so let's take advantage of that:

  Table 'generate_series'. Segment reads 1, segment skipped 68.

  SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 9 ms.

This query is also fast:

select *
from generate_series
where c in (select c from #tmp)
OPTION (MAXDOP 8);

Statistics output:

  Table 'generate_series'. Segment reads 1, segment skipped 68.

  SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 11 ms.

This query is fast as well:

select *
from generate_series
where c in (select c from #tmp)
OPTION (MAXDOP 1);

  Table 'generate_series'. Segment reads 1, segment skipped 68.

  SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 5 ms.

For each query SQL Server is able to skip 68 rowgroups. It only reads data from the rowgroup that contains an ID of 100. In the plan for the final query we can see that an optimized bitmap was applied:

That bitmap allows rowgroup elimination through a join.

In SQL Server 2014 I get similar performance results for the queries except for the last one that has a join and MAXDOP of 1. That one takes 21 seconds to run. Here is the query plan:

There is no bitmap and the scan isn't running in batch mode. We send back all 67 million rows to the hash join.

In SQL Server 2014 serial plans are not eligible for batch mode. If your query is running in serial for some reason that could explain the poor performance that you experienced. Note that the query that directly filters against c = 100 also runs in row mode but it still finishes quickly.


Self-referencing tables uses What is the use of self-referencing tables ?

I came across the concept lately and I cannot find any simple example of it (every links on Google explain how to use it with many frameworks).

Self referencing tables can be used to store hierarchical data.  For example, see my answer to this question.

You can also use self referencing tables to record non-hierarchical relationships which are one-to-one.  For example, let's say you have a PERSON table and you want to have a relationship is currently married to.  You might model this with an intersection table, or you might use a simpler self referencing relationship.


Unable to load mysql dump file I recently had to deal with migrating some servers over (rather was forced to by the hosting company) and we took too long to migrate our data so they did it for us. Unfortunately they just copied /var/lib/mysql for the database. Which created all sorts of errors when trying to get the data working again. So I ran mysql in innodb_force_recovery = 1 mode and was able to take dumps of all but 1 of my databases.

Then I completely uninstalled mysql and reinstalled and tried to import the dump files into the newly created databases.

Then I got this error:

ERROR 1030 (HY000) at line 810: Got error -1 from storage engine

So line 810 is the first data insert. It seems there is not problem creating tables, but anytime I try to insert I get that error. I can't seem to find any documentation on what that error means.

How do I go about resolving this error?

Note: After a bit more digging, it seems to be an error that only occurs with innodb tables. In fact I tried converting a myisam table to innodb and got the same exact error.

Solved:

ERROR 1030 (HY000) at line 810: Got error -1 from storage engine

The above error means you have innodb_force_recovery set in your my.cnf
I'm not sure why I couldn't find this easily in mysql documentation, but it's simple enough.


MySQL: Algebra in Union operation I have below query:

select entity_id from table1 exc
UNION
select entity_id from tabl2 inc

On execution, I got 24419 rows.

When I execute select entity_id from table1 exc I got 16235 rows and on the execution of select entity_id from tabl2 inc I got 35943 rows.

Below is the explain result of above query:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: exc
         type: index
possible_keys: NULL
          key: IDX_ENTITY_ID
      key_len: 4
          ref: NULL
         rows: 15603
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: inc
         type: index
possible_keys: NULL
          key: IDX_ENTITY_ID
      key_len: 4
          ref: NULL
         rows: 32697
        Extra: Using index
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.00 sec)

Table structure of table1

Create Table: CREATE TABLE `table1` (
  `entity_id` int(11) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entity_id`,`rule_id`),
  KEY `IDX_ENTITY_ID` (`entity_id`),
  KEY `idx_rule_id_jos_tag_rules_content_exclude` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

Table structure of table2

Create Table: CREATE TABLE `table2` (
  `entity_id` int(11) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entity_id`,`rule_id`),
  KEY `IDX_ENTITY_ID` (`entity_id`),
  KEY `idx_rule_id_jos_tag_rules_content_include` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

If we see on algebric calculation behind Union operation then it should be max(No(a)+no(b)).
Now, I am unable to understand behind above result. How MySQL perform Union operation?

union returns distinct results.
try UNION ALL

select entity_id from table1 exc
UNION ALL
select entity_id from tabl2 inc


when do i usually optimize an innodb table? I have a table that's always used 24/7. There is always a stream of update and/or insert  of 10-20 rows per 30 to 60 seconds.

As stated on the MYSQL documentation:
"After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system."

I am not sure if what im doing with the table is "substantial" enough for me to optimize it regulary.

Also, what is the impact on performance with doing optimize table regulary?

In almost all situations, OPTIMIZE TABLE is a waste of time and effort.  So, in my opinion, "substantial" is really, really, big.

10-20 changes per minute -- not very busy.  Not substantial.


Converting string to date with timezone adjustment I am attempting to convert stirngs to dates in mysql

Thu Oct 23 16:46:47 2014 +02:00

Im unsure how to handle the +02:00, The rest is simple enough

 update logs
 set date = str_to_date(date_raw, '%a %b %e %T %Y')

This returns

Truncated incorrect datetime value: 'Thu Oct 23 16:46:47 2014 +02:00'

Any ideas?

How about a stored function?

DELIMITER $$

DROP FUNCTION IF EXISTS `ts_from_offset` $$
CREATE FUNCTION `ts_from_offset`(in_ts TINYTEXT) RETURNS datetime
NO SQL
DETERMINISTIC
BEGIN

-- Thu Oct 23 16:46:47 2014 +02:00

-- this function takes an input timestamp value with an offset formatted as above,
-- and converts it to the equivalent MySQL datetime value, expressed in the current session's
-- time zone.  Since this is also the timezone that columns in the TIMESTAMP data type expect,
-- this causes the input value to be stored correctly in the native TIMESTAMP format, which is.
-- UTC under the hood.

-- if you are taking the value here and stuffing it into a non-UTC DATETIME column, you need to have
-- session @@time_zone set to the same zone in which that column should be stored, or use
-- CONVERT(ts_from_offset('input value'),'UTC','Your Desired Time Zone');

-- http://dba.stackexchange.com/questions/83898/converting-string-to-date-with-timezone-adjustment/84041#84041

DECLARE offset_string TINYTEXT DEFAULT NULL;
DECLARE date_string TINYTEXT DEFAULT NULL;
DECLARE offset_sign TINYINT DEFAULT NULL;
DECLARE offset_hours TINYINT DEFAULT NULL;
DECLARE offset_minutes TINYINT DEFAULT NULL;

SET offset_string = SUBSTRING_INDEX(in_ts,' ',-1);

SET in_ts = LEFT(in_ts, LENGTH(in_ts) - 1 - LENGTH(offset_string));

SET offset_sign = IF(SUBSTRING(offset_string FROM 1 FOR 1) = '+', -1, +1); # we need to flip the sign, to "back out" the offset to get a time in UTC
SET offset_hours = CAST(SUBSTRING(offset_string FROM 2 FOR 2) AS SIGNED) * offset_sign;
SET offset_minutes = CAST(SUBSTRING(offset_string FROM 5 FOR 2) AS SIGNED) * offset_sign;
RETURN CONVERT_TZ(DATE_ADD(DATE_ADD(STR_TO_DATE(in_ts,'%a %b %e %T %Y'), INTERVAL offset_hours HOUR), INTERVAL offset_minutes MINUTE),'UTC',@@time_zone);

END $$

DELIMITER ;

Example output...

mysql> SET @@TIME_ZONE = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00');
+---------------------------------------------------+
| ts_from_offset('Thu Oct 23 16:46:47 2014 +02:00') |
+---------------------------------------------------+
| 2014-10-23 14:46:47                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

No warranty, but it seems like this should do the trick.


How to reorder rows in Postgresql I've got the following table

And I have to change the order of the rows in an unpredictable way for example:

Set the position of the row with id = 1 after the row with id = 3 and preserve the ordering of the other rows.
Add new row at position 3 right after row with id = 2.
Delete row from
position 4

What is the best solution for this problem?

There is no solution, because table rows in PostgreSQL don't have a fixed position in the table. Every UPDATE will change the position, and every new row will be inserted wherever there happens to be enough room.

Even if there are no modifications, sequential scans of the table don't necessarily start at the beginning.

The way to order rows is to use an appropriate ORDER BY clause with SELECT.


MySQL MyISAM REPAIR TABLE lost connection to MySQL server during query So I've been building up over time a database, and unfortunately I was using MyISAM which didn't have any problems until the database became huge.

After massive CPU spikes, I created a script to OPTIMIZE all the tables, and ran it. And everything was fixed and working fine and not using nearly as much CPU. This was done through PHP. I also have a jailed shell that I can use to access the database.

The next thing I did was really stupid. I took this working database and decided to run a REPAIR operation on all the tables. For most tables the operation was successful and returned a success status, however it timed out and broke several of the tables. The remaining tables that weren't working show as 'in use' in PHPMyAdmin and when I try to perform any operation on those tables it says 'table is marked as crashed and last (automatic?) repair failed'.

A couple of the tables recovered fully by simply repeating the REPAIR operation. Over time by running REPAIR operations over and over I managed to recover most of those tables. However, there is one table which is still timing out always. I have access to run queries via PHP, PHPMyAdmin, or the jailed shell, all of which time out, as well as cPanel. The CHECK operation also times out. I am certain that the actual data is fine, and that the operations are simply being killed before they can finish by some higher-level restriction. (All of which I do not have access to change.) I'm pretty sure the data is probably even just fine and it's an in-use flag somewhere which was set because the operation didn't finish. Obviously the export function of PHPMyAdmin does not work in this scenario. What can I do to get this data off the server and attempt a proper recovery?

First, I need to ask "Does the OPTIMIZE actually help?"  I ask because it is very rare that it makes enough difference to bother with.  Please describe the activity on the table.  One case that might need the optimize is a huge amount of deletes and/or updates (with different lengths).  If the deletes are of "old" data, then we should discuss partitioning as a much better way.

What setting do you have for myisam_recover_options?  That would let the server deal with the REPAIR automatically.

You really should consider changing to InnoDB which will mostly eliminate the need for OPTIMIZE and REPAIR.  Also MyISAM is going away.

On another topic,...  Have you identified the query/queries that are involved in the CPU spike?  It could be that we can speed them up enough to avoid the problem.  Sometimes it is as simple as adding a suitable composite index.


Table just for grouping Is it a common case to have a table with a single column for the purpose of grouping rows in another table?

I'm inserting data in batches and I want to have an autoincrement key for each batch to be able to group data based on generated id.

Concretely I want to get from this

A
id, x, y, b_id
id PRIMARY KEY
b_id FOREIGN KEY REFERENCES B.id

B
id, timestamp
id PRIMARY KEY

SELECT count(*) as number, B.timestamp FROM A inner join B on A.b_id=B.id
                     where A.x='value' and A.y='value'
                     group by B.id;

to

A
id, x, y, timestamp, b_id
id PRIMARY KEY
b_id FOREIGN KEY REFERENCES B.id

B
id
id PRIMARY KEY

SELECT count(*) as number, A.timestamp FROM A
                         where A.x='value' and A.y='value'
                         group by A.b_id, A.timestamp;

So basically move timestamp to B (denormalize) and use foreign key only for grouping. I want to avoid having join only for the timestamp placed in B. Tables are quite big (60M of rows) and join is very slow. If I still filter on A and have foreign key only for grouping then that would speed up things a lot.

Concretely, I'm using MySQL.

Is it common to mark rows with a batch number? Yes it is. In accounting contexts I have seen this used widely for audit and reconciliation purposes.

Is having a separate table to store batch-related values a good idea? Yes it is. Good practice in relational DB design is to have one table per entity type. Since your problem domain recognises "batch" as a concept, with its own values, it should have a table.

Should this table use an autoincrement column? Well, this is very much an implementation detail. Does your problem domain allow for gaps, negatives, re-use after delete, out of order values etc. etc? Does your DBMS software guarantee the necessary features? If so then, sure, go for it. If not, write your own generator with the needed guarantees.

Will denormalising timestamp from the "batch" table to the "data" table improve performance? Can't say. It will make the data table wider, which should cause more IO. There may be enough spare space in each page that this doesn't happen, however. If all the data, or at least the woking set, fits in memory, this consideration is void.

If there are good indexes in place and they are being used I'd guess denormalisation won't change much. You could try rewriting the query. Sometimes if the optimizer starts from a different place it ends up with a different plan. Put select..from a group by b_id in a subquery, then join that to b to get the timestamp.

You need to understand where the time is consumed and address that. And of course test, test, test.


Auto Update Stats not updating Statistics Am using SQL SERVER 2012 I have my Auto Update Stats ON in my database.

From the below link I learned that, the Auto Update stats will fire for every SQRT(1000 * Table rows) change in table rows.
https://blogs.msdn.microsoft.com/srgolla/2012/09/04/sql-server-statistics-explained/

I created a table with 1000 records

SELECT TOP 500 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                name
INTO   stst
FROM   sys.objects

Creating statistics

CREATE STATISTICS rn
    ON stst (rn)

CREATE STATISTICS name
    ON stst (name)

Checking the created Statistics

DBCC show_statistics('stst', rn)  -- Rows 500

DBCC show_statistics('stst', name) -- Rows 500

As per the formula

select SQRT(1000 * 500) -- 707.106781186548

So If I add/modify 707.106781186548 records in my table auto update statistics should fire

Add 1000 more records to my table which should be more than enough to fire auto update stats

INSERT INTO stst(rn,name)
SELECT TOP 1000 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                a.name
FROM   sys.objects a

To fire the auto update stats

Select * from stst

Checking the statistics

DBCC show_statistics('stst', rn)  -- Rows 500

DBCC show_statistics('stst', name) -- Rows 500

Unfortunately still the Rows is 500 only.

Even after inserting 1000 records into my table which is obviously greater than 707.106781186548 while performing SELECT why Auto Update stats didn't fire ? What am I missing here

The new calculation is only used if trace flag 2371 is enabled, except on SQL Server 2016 when the context database compatibility level is set to 130, where it is the default behaviour. See Microsoft KB 2754171:

Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

Statistics are not updated when data is modified. A statistics update is triggered when cost-based optimization finds that interesting statistics for the query are stale.

The optimizer does not enter cost-based optimization for very simple ("trivial") queries, where a single obvious plan is always optimal. No statistics updates occur in that scenario.

See the Microsoft White Paper Plan Caching and Recompilation in SQL Server 2012 by Greg Low.


How to Combine Insert, Update, and Delete in the same `Instead Of` Trigger, in Oracle db? When creating an Instead Of trigger, Sql Developer allows to include all 3 DML actions in the same trigger:

The result SQL looks like:

CREATE OR REPLACE TRIGGER TRIGGER1
INSTEAD OF DELETE OR INSERT OR UPDATE ON PO_COSTED_BOM_V
BEGIN
  NULL;
END;

I'm able to combine Insert and Update like this:

CREATE OR REPLACE TRIGGER TRIGGER1
INSTEAD OF INSERT OR UPDATE OR DELETE ON PO_COSTED_BOM_V
BEGIN
  UPDATE SRC_TBL SET CFG_VALUE = :NEW.CFG_VALUE
  WHERE CFG_NAME = :NEW.CFG_NAME;
  IF SQL%NOTFOUND THEN
      INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE)
         VALUES(:NEW.CFG_NAME, :NEW.CFG_VALUE);
  END IF;
END;

But how do i get the Delete statement into the trigger?
How does the trigger know which DML action was executed?

thx

From Coding the Trigger Body

  Detecting the DML Operation that Fired a Trigger

  If more than one type of DML operation can fire a trigger (for
  example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can
  use the conditional predicates INSERTING, DELETING, and UPDATING to
  check which type of statement fire the trigger.

  Within the code of the trigger body, you can execute blocks of code
  depending on the kind of DML operation that fired the trigger:

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;
IF DELETING THEN ... END IF;

in your case

...
if inserting then

INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE)
         VALUES(:NEW.CFG_NAME, :NEW.CFG_VALUE);

elsif updating then

 UPDATE SRC_TBL SET CFG_VALUE = :NEW.CFG_VALUE
  WHERE CFG_NAME = :NEW.CFG_NAME;

elsif deleting then

 ...

END IF;


Select rows from two joined table that not in a third table I have 3 table A,B,C,D with the same columns and I'm trying to retrieve the
user_id that are in table a and b but not c.

For instance:
Table A
user_id     closed acct
10102345     Yes
12456786     Yes

Table B
user_id  closed acct
10102345     Yes
12456786     Yes

Table C
user_id  closed acct
45345696     No
45698788     Yes
12456786     Yes

Here is what the code that I try but it taking so long to execute:
   SELECT user_id  FROM TableA WHERE user_id  NOT IN (SELECT DISTINCT user_id
   FROM TableB) OR user_id  NOT IN (SELECT DISTINCT ID FROM Table C)

SELECT A.*, B.*
FROM A
JOIN B ON A.user_id = B.user_id
WHERE NOT EXISTS ( SELECT NULL
                   FROM C
                   WHERE C.user_id = B.user_id )

or

SELECT A.*, B.*
FROM A
JOIN B ON A.user_id = B.user_id
LEFT JOIN C ON C.user_id = B.user_id
WHERE C.user_id IS NULL


Edit Application Name to Log Connection once for Logons on Pooled Connections I am wondering if I can change the application name of a connection in SQL Server itself, e.g. in the logon trigger? Is that possible or is the application name "read only"?

Edit to explain the motivation: I would like to mark all audited connections in my logon trigger, so that I write only one record per connection through its many logon events. I thought I could modify some info, e.g. the app name, of a connection so that next time around the logon trigger sees that it can ignore this connection attempt because it already logged the info around this connection (like user, host etc.).

No, the connection properties should be immutable. However, even if you could edit them, there is still an easier (and better) way to correlate Logon Events to the same Connection: the connect_time field in sys.dm_exec_connections. You just look up that field based on the session_id (which correlates to SPID in the XML returned from the EVENTDATA() function). If your audit table does not already have fields for SessionID and ConnectionTime you will need to add one or both of them. And it probably would be wise to create a Non-Clustered Index on (ConnectionTime DESC, SessionID). Finally, in your Logon Trigger after you do the lookup for @ConnectionTime, do an IF NOT EXISTS to check your audit table for that combination of @ConnectionTime and @SessionID and only INSERT if not found.

The following is a basic example of how to capture this info, but does not include the Index or the IF NOT EXISTS logic:

Create the audit Table

--DROP TABLE Test.dbo.LogonEvent;
CREATE TABLE Test.dbo.LogonEvent
(
    LogonEventID INT NOT NULL IDENTITY(-2140000000, 1),
    SessionID INT NOT NULL,
    ConnectionTime DATETIME NOT NULL,
    EventInfo XML NULL
) ON [Tables];
GO

Create the Logon Trigger

ALTER TRIGGER CaptureLogonEvents
ON ALL SERVER
FOR LOGON
AS
SET NOCOUNT ON;

DECLARE @SessionID INT = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'INT');

INSERT INTO [Test].[dbo].[LogonEvent] (SessionID, ConnectionTime, EventInfo)
    SELECT  @SessionID AS [SessionID],
            sdec.connect_time AS [ConnectionTime],
            EVENTDATA() AS [EventInfo]
    FROM        sys.dm_exec_connections sdec
    WHERE   sdec.session_id = @SessionID;
GO


Was SSRS installed in SharePoint Integrated Mode? I've inherited an old QA database server with SQL Server 2008R2 (yes, yes, I know it's beyond EOL; there's nothing I can do about it!) and SSRS installed.

It's the back end of a SP server on a different VM.

How do I tell whether or not SSRS was installed in Native Mode or SP Integrated Mode?

Did you try looking in the Reporting Services Configuration on the server in question?

https://social.msdn.microsoft.com/Forums/en-US/a856488b-fbcd-479e-9167-34ddf7ba3e83/how-do-i-find-out-if-ssrs-is-intregrated-or-native?forum=sqlreportingservices


Database Mirroring setup does not work when the database is above a certain size, logs out of synch I have a Microsoft database running in production of about 300gb, I need to transfer and set it up in a mirrored configuration on two different servers.

This has worked fine in my tests using smaller databases, but now I run into the following issues while trying to use the actual production database. It works about 1 out of 3 times, the rest of the times it ends in errors.

When trying to enable mirror on the primary it gives this error:
"The remote copy of database "myDatabase" has not been rolled forward to a point in time that is encompassed in the local copy of the database log."

If I then try and take a new backup of the database log and restore on the mirror:
"Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 64612000000218000001, which is too recent to apply to the database. An earlier log backup that includes LSN 64612000000217800001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally"

Here is what I am doing, it is also very time consuming and will require a lot of downtime, so if there is an easier way I would be very happy:

Server A is the current production server where a single instance of the db is running.
Server B is the initial primary database server
Server C is the initial mirror database server

I break the mirror from Server B and delete the databases on Server B and C.

ALTER DATABASE produktion SET PARTNER OFF

I take a backup from Server A and store it in a shared folder on Server B.

BACKUP DATABASE [myDatabase] TO  DISK = N'\\Server B\share\myDatabase.bak' WITH NOFORMAT, INIT,  NAME = N'myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

I restore from the backup on Server B.

RESTORE DATABASE [myDatabase]    FROM DISK = '\\Server B\share\myDatabase.bak'    WITH
MOVE 'mdflogicalfilename' TO    'D:\DEFAULT_2012_DATA\mdffile.mdf',
MOVE 'extralogicalfilename_extra' TO    'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf',
MOVE 'logfilename_log' TO    'E:\DEFAULT_2012_LOG\logfilename_log.ldf',
NOUNLOAD, REPLACE, STATS =5;

I take a new backup on Server B (unsure if this can be avoided?)

ALTER DATABASE 'myDatabase' SET RECOVERY FULL;
BACKUP DATABASE 'myDatabase'
TO DISK = '\\Server C\share\myDatabase_full.bak'
WITH INIT, FORMAT, STATS = 10;

BACKUP LOG 'myDatabase'
TO DISK = '\\Server C\share\myDatabase_log.bak'
WITH INIT, FORMAT, STATS = 10;

I restore from the new backup and log on Server C with NORECOVERY

RESTORE DATABASE "myDatabase"    FROM DISK = '\\Server C\share\myDatabase.bak'    WITH
MOVE 'mdflogicalfilename' TO    'D:\DEFAULT_2012_DATA\mdffile.mdf',
MOVE 'extralogicalfilename_extra' TO    'D:\DEFAULT_2012_DATA\extralogicalfilename_extra.mdf',
MOVE 'logfilename_log' TO    'E:\DEFAULT_2012_LOG\logfilename_log.ldf',
NOUNLOAD, REPLACE, NORECOVERY;

RESTORE LOG "myDatabase"    FROM DISK = '\\Server C\share\myDatabase_log.bak'  WITH
NORECOVERY, STATS = 10;

Enable Mirroring on the initial mirror:

ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_B_FQN:myPort'

Enable Mirroring on the initial primary (this is where it fails):

ALTER DATABASE myDatabase SET PARTNER = 'TCP://Server_C_FQN:myPort'

The whole procedure takes about 7-8 hours, I have created each step as agent jobs to start the next one, but if something can be done faster it would help a lot.

It works about 1 out of 3 times, the other times I get the above errors from which I cannot seem to do anything but start over and hope it works.

Issue doesn't happen with small databases, so the issue is probably that the restore takes so long on Server C, that the DB on Server B some times creates new logs that are then out of synch, this would also explain that it works some times, but there are no changes comitted to the database doing the time.

Is there any way to restore all incremental logs and make sure the databases are in synch for mirror without restoring a full backup of the database?

As has already been noted there is likely to be a third party tool taking backups which you weren't aware of.  Stopping that (or at least having control over it) is going to assist no end (obviously be aware that stopping log backups may invalidate the RPO/RTO targets and taking adhoc full and log backups will invalidate the log chain and may make dr recovery more complex or result in larger data loss during this period).

You've mentioned that you are keen to reduce the downtime required; I believe you have a few options available to you to assist.

I note that you state SQL Server 2012.  Backup compression is available in standard edition upwards, enabling this will reduce IO (and therefore time) for both your backup and restore at a relatively small cost of CPU.  I have seen backups reduce by nearly half using compression (depending on what is stored in the DB).  Simply add compression to your with options.  This can be used in conjunction with the below to reduce the outage window further.

Option 1 - Use a differential backup

Rather than taking a full backup at your outage time, take a full backup (or last nights if done daily) and restore that during the day of migration to your other servers.  You could then take a differential at your maintenance window.  As a differential backup only contains the changes since the last full backup it will be much smaller and faster than a full backup.  It also doesn't have any dependency on log backups so as long as your full backup is good you can apply a differential to get it in sync.  Be aware though, the differential must be taken before the next full backup so either know that the planned window is before the nightly backups or suspend the full backup for this database.

Therefore you could:

Take the full backup on A and restore it to both B (NORECOVERY) and C (NORECOVERY) with the application still up (and log backups continuing).
Wait until your outage time, stop log backups and take a differential backup
Update your connection string (or CNAME)
Restore differential to A (with RECOVERY) and B (with NORECOVERY)
Initiate the mirror between B and C
Start log backups on B

Option 2 - Mirroring

As you are not in a mirrored configuration currently you could look to create the mirror between A and B first; even if going up SQL Server versions mirroring will still work one way (from older to newer versions).  Restore and mirror would be done with the application still online, without requiring downtime.

This might look something like:

Backup Server A and restore to B (NORECOVERY)
Stop log backups on A, take differential and restore to B
Initiate mirror (A --> B)
Restart log backups
When your outage window comes up, stop the application services, change the DNS CNAME (or connection string)
Failover the mirror (from server A alter database [mydb] with partner failover;
start the application services again
Start log backup schedule on B
Backup full db on B
Restore to C (NORECOVERY)
If log backup has been taken in time between backup and restore from B to C then take differential on B and restore to C
Initiate mirror

Option 3 - Log shipping

By utilising log shipping you can actually seed both B and C simultaneously.

The process would be similar to below:

Take full backup and restore to B and C (NORECOVERY)
Stop log backups (permanently on third party tool)
Take and restore differential to B and C (NORECOVERY)
Start log shipping configuration with 2 secondaries (B and C, NORECOVERY).  This will be creating the log backup files so may need to mention this to the backup guys so they capture the location, note that the SQL Agent services will need to have read/write permissions over the share(s) for this to work.
When downtime outage comes, take final log backup and restore to secondaries (jobs found in agent for backup/restore jobs on respective instances)
Change CNAME (or connection string)
Bring server B database out of restoring state restore database [mydb] with recovery
Initiate mirror
Start log backups on Server B


Need scalable database solution for very big automated data entry database I've been working on MySQL database in simple web applications for a long time and i now have a project which should maintain about 1,000 up to 10,000 query per minute (it is some logging database system) and a lot of theme are Insert statement.

First thing worried me is i don't think MySQL could handle that much query per second,I know i should buy stronger Server but i don't know if MySQL can be good solution for that.Is there any way to calculate that ability before buying servers?

Second important thing is that the database will grow too fast and it will be use all server resources like hard disk or CPU usage sooner or later and i should have an expanding solution,my question in this part from experienced database admins is :

"Is MySQL suitable for distributing or cluster databases ?"

or

"Is there any easy to use database which support clustering and distributing systems?" (I know MySQL have a cluster version but i don't know if it is appropriate in my project).

Edit:

The project is about logging users(by their RF-ID tags) as they pass throw the antenna range or rf-id readers or Electronic gates and read their permission to enter or leave the area which specified in database for that tag ID, and record there activity in database.The operator should access all these data and be able to manipulate theme.

P.S:

I know there are very professional servers with large capacity of hard disk like SAN or NAS(Network-attached storage),but as it is not national or very big company project i need easy to distribute database to distribute it over normal servers.

"Is MySQL suitable for distributing or cluster databases ?"

While you certainly can do manually sharding, the truth is that the most common MySQL cluster technologies are focused in high availability. NDB Cluster is based mainly on a memory database with lots of throughput, but not necessarily low-latency and large datasets. Galera does not support autosharding (only does read scaling).

  "Is there any easy to use database which support clustering and distributing systems?" (I know MySQL have a cluster version but i don't know if it is appropriate in my project).

Other people may give you better advice about other databases, but beware:

Many vendors that criticize the relational and transactional database model sometimes forget to tell you ideas like "eventually consistent" or the possibility of data loss in exchange for better performance (I look at you, NoSQL).
Can you use a non-distributed database? There are datawarehousing/highly compressed solutions for MySQL (InfiniDB, TokuDB). Even InnoDB has a memcached interface (allowing you even 1M Querys Per Second), which may help in reducing latency for the SQL (although not memory requirements and disk space).

If you tell us more about how you are going to use your logs (statistics, historic data?), we may be able to tell you if a relational database is better for you, or you have to go for other paradigm like column-based databases, or map-reduce, etc. Even then, you should try any product for yourself.


Refer to renamed columns in WHERE clause SELECT name, gdp/population as capita
FROM world w1
WHERE continent = 'Europe'
AND capita >
(
    SELECT gdp/population
    FROM world
    WHERE name = 'United Kingdom'
);

How come I can't refer to capita in the WHERE clause? It returns this error:

Unknown column 'capita' in 'where clause'

I can however, run the query if I replace capita with gdp/population.

I don't want to do gdp/population > .... How can I do capita > ...? Or is this not possible?

You should use a View in your database

USE mydb;
CREATE VIEW name_capita AS
SELECT name, gdp/population as capita,continent FROM world;

Then, apply the WHERE clause to the View

USE mydb;
SELECT * FROM name_capita
WHERE continent = 'Europe' AND
capita > (SELECT gdp/population FROM world WHERE name = 'United Kingdom');

or even re-using the view:

SELECT * FROM name_capita
WHERE continent = 'Europe' AND
capita > (SELECT capita FROM name_capita WHERE name = 'United Kingdom');

GIVE IT A TRY !!!


pg_restore fails for a pg_dump from a db that worked just fine Any idea how this can happen? I have a table which I created by loading data into it via the COPY command. A column in it was declared as VARCHAR(200). I dumped the table, moved it to another computer, and tried to restore it but got an error

pg_restore: [archiver (db)] Error from TOC entry 4154; 0 259261 TABLE DATA foo postgres
pg_restore: [archiver (db)] COPY failed for table "foo": ERROR:  value too long for type character varying(200)
CONTEXT:  COPY foo, line 22556, column a: "สถานีอนามัยเฉลิมพระเกียรติพระบาทส?..."
pg_restore: *** aborted because of error

Update: Interestingly, when I check the length of this record, I get 82, safely less than 200.

SELECT Length(a) FROM foo WHERE a LIKE 'สถานีอนามัยเฉลิมพระเกียรติพระบาทส%'
82

And, yes, the new database is also set with UTF8 encoding.

I got a notification that my answer was migrated to dba.stackexchange.com. But all that migrated was the question. I'll answer again.

Make sure the character types are the same, or at least that they're compatible. An 80-character UTF8 string might be 240 bytes long.

Collation should probably be the same on both computers, too.


Disable dbo schema fallback when table doesn't exist in default schema I've properly performed these SQL commands in SQL server 2017:

CREATE DATABASE DataTeamDatabase

USE DataTeamDatabase

CREATE LOGIN DataTeamUser
    WITH PASSWORD = 'YourStrong!Passw0rd',
    DEFAULT_DATABASE = DataTeamDatabase;

CREATE SCHEMA DataTeamSchema

CREATE USER DataTeamUser FOR LOGIN DataTeamUser WITH DEFAULT_SCHEMA = DataTeamSchema

ALTER ROLE db_datareader add member DataTeamUser
ALTER ROLE db_datawriter add member DataTeamUser
ALTER ROLE db_ddladmin add member DataTeamUser
GRANT CONNECT TO datateamuser

CREATE TABLE dbo.TestTable (column_1 int)

ALTER USER DataTeamUser WITH DEFAULT_SCHEMA = DataTeamSchema;

Now, let's say that I want to query the TestTable (which I know is not existing in DataTeamSchema):

SELECT * FROM TestTable

Since the table TestTable is not existing in the DataTeamSchema, SQL server is looking it up in the dbo schema and uses it.

Is there a way to disable this behavior? I know that prepending the schema name to the table name will work, but I would like to avoid that because of different reasons.

I searched through the SQL server documentation but didn't find anything so far.

Thanks,

Mattia

In a word, no this is not possible.

I've struggled to find an official line on this, but the name resolution algorithm in SQL Server is something like the following, as defined by Bob Beauchemin in Dude, Where's my table?

If batch or dynamic SQL:

Look in 'sys' schema for system objects
Look in user's default schema
Look in dbo schema

If procedural code:

Look in 'sys' schema for system objects
Look in procedure schema
Look in dbo schema

In your case, SQL Server is failing to find an MS Shipped object firstly, then failing to find the object in the user schema (DataTeamSchema), and finally it finds the object in the dbo schema.

One way around this would be to change the original object to a schema other than dbo, as the dbo schema is a special case in terms of object-name resolution.

There does not appear to be a documented way to avoid this behaviour.


duplicate entries from mysql LIKE query with this sample dataset

I have this query

SELECT products.id,
                        products.title AS product_title,
                        products.code,
                        products.created,
                        products.modified,
                        products.active,
                        attributes.title AS attr_title,
                        attribute_entries.title AS attr_entry_title
                        FROM products
                        INNER JOIN attributes ON attributes.model_id = products.id
                        INNER JOIN attribute_entries ON attribute_entries.FK_attribute_id = attributes.id WHERE ( products.id LIKE '%" . $model['value'] . "%' OR products.code LIKE '%" . $model['value'] . "%' OR products.title LIKE '%" . $model['value'] . "%') limit 10

For some reason I get duplicates in my result set.  I have done this type of query before but I never got duplicates.  Although this is the first time I query the id as well and treat it as string.  Is this my problem?

I just added GROP BY products.id and it is fine now.  dumb, dumb.

IF this isn't proper or a better solution please feel free to post.

SELECT products.id,
                    products.title AS product_title,
                    products.code,
                    products.created,
                    products.modified,
                    products.active,
                    attributes.title AS attr_title,
                    attribute_entries.title AS attr_entry_title
                    FROM products
                    INNER JOIN attributes ON attributes.model_id = products.id
                    INNER JOIN attribute_entries ON attribute_entries.FK_attribute_id = attributes.id WHERE ( products.id LIKE '%" . $model['value'] . "%' OR products.code LIKE '%" . $model['value'] . "%' OR products.title LIKE '%" . $model['value'] . "%') GROUP BY products.id limit 10


Combining data from multiple large tables I have a customer survey dataset split across 25 different tables. I need to combine all the tables and produce one file with all the columns from all tables. All the tables have the SurveyID key in common.
For example

table1: SurveyID, Region, Gender

table2: SurveyID, AnswerNumber, Rating

table3: SurveyID, AnswerNumber, Expediture.

I want the output like this and don't want any duplicate rows:

Final table: SurveyID, Region, Gender, Rating, Expenditure...and so on.

One issue is that not all tables contain data for the same SurveyID. For example table 1 has 114000 rows whereas table 2 only has 50000 rows, indicating not everyone answered those survey questions.

I've looked through the site and tried using left joins and inner joins as per the suggestions but not getting the result I want.

UPDATE - Here is the code I tried based on the response:

;with surveys as
(
select * from dbo.vw_IVSSurveyMainHeader s
),
responses as
(
select s.SurveyID, t1.["AnswerNumber"], t1.["AccommTypeUsed"],
t2.["ActivityCategory"], t2.["Activity"], t2.["ActivitySubType"],
t3.["AirlineUsed"], t3.["AirlineRating"],
t4.["AppealReason"],
t5.["CycleOwnership"],
t6.["InformationSourceUsed"],
t7.["WebsiteForPlanning"], t7.["WebsiteForBooking"], t7.["UsefulnessRating"],
t8.["AmenityType"], t8.["EnvPracticeRating"],
t9.["ItemisedCostAmt"], t9.["ExpenditureType"], t9.["ItemisedCostCurr"],
t10.["WhereStayed"], t10.["NoNights"],
t11.["SatisfactionRatingReason"],
sat.["SatisfactionRating"],
t12.["SocialMediaUsage"], t12.["SocialMediaType"], t12.["TripStage"],
t13.["SocialMediaUsed"],
t14.["TechnologyType"],
t15.["TechnologyUses"],
t16.["TransportMethod"],
t17.["TravelledWith"]
from dbo.vw_IVSSurveyMainHeader s
left join dbo.vw_IVSAccommodationUsed t1 on s.SurveyID = t1.SurveyID
left join dbo.vw_IVSActivities t2        on s.SurveyID = t2.SurveyID
left join dbo.vw_IVSAirlineRating t3     on s.SurveyID = t3.SurveyID
left join dbo.vw_IVSAppealReasons t4     on s.SurveyID = t4.SurveyID
left join dbo.vw_IVSCycleOwnership t5    on s.SurveyID = t5.SurveyID
left join dbo.[vw_IVSDecisionMakingProcess - After arriving] t6 on s.SurveyID = t6.SurveyID
left join dbo.[vw_IVSDecisionMakingProcess - Planning and booking] t7 on s.SurveyID = t7.SurveyID
left join dbo.vw_IVSEnvironmentPracticeRating t8 on s.SurveyID = t8.SurveyID
left join dbo.vw_IVSExpenditure t9 on s.SurveyID = t9.SurveyID
left join dbo.vw_IVSItineraryPlaces t10 on s.SurveyID = t10.SurveyID
left join dbo.vw_IVSSatisfactionRatingReasons t11 on s.SurveyID = t11.SurveyID
left join dbo.vw_IVSSatisfactionRatings sat on s.SurveyID = sat.SurveyID
left join dbo.vw_IVSSocialMediaUsage t12 on s.SurveyID = t12.SurveyID
left join dbo.vw_IVSSocialMediaUsed t13 on s.SurveyID = t13.SurveyID
left join dbo.vw_IVSTechnologyTypes t14 on s.SurveyID = t14.SurveyID
left join dbo.vw_IVSTechnologyUses t15 on s.SurveyID = t15.SurveyID
left join dbo.vw_IVSTransport t16 on s.SurveyID = t16.SurveyID
left join dbo.vw_IVSTravelParty t17 on s.SurveyID = t17.SurveyID
)
select surveys.*, responses.*
from surveys su left join responses r
on su.SurveyID = r.SurveyID

This throws the error that the column prefix for surveys and responses does not match with a table name or alias name used.

You need to have information for all surveys, even the ones that have no answers, right?

;with surveys as
    (
        select SurveyID, Region, Gender from Table1
    )
, responses as
    (
        select t1.SurveyID, t2.AnswerNumber, t2.Rating, t3.AnswerNumber, t3.Expediture -- add other columns from the other tables
        from Table1 t1 left join Table2 t2 on t1.SurveyId = t2.SurveyID
                   left join Table3 t3 on t1.SurveyId = t3.SurveyID
                   -- add other tables up to the 25th
    )
select s.SurveyId, s.Region, s.Gender, r.*
from surveys s left join responses r on s.SurveyId = r.SurveyID


PostgreSQL ODBC and Microsoft Access date/time in aggregate functions I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3.  The first step is to use our MS Access front end to the upsized databases in PostgreSQL.  Everything seems to work very well but I have one problem.

We have a time clock program where a user clocks in at one time and clocks out at another.  The row in the database records this start time and end time.  The data looks like this:

The query looks like this:

SELECT emphours.id, emphours.empid, emphours.department, emphours.weekending, emphours.paytype, emphours.starttime, emphours.endtime, ([endtime]-[starttime])*24 AS hours
FROM emphours;

The starttime field and the endtime field are both timestamps without timezone in PostgreSQL.  Up to this point all is good.

If I try to make an aggregate query to sum all the hours in a week for a given employee and pay type, I lose the precision in the hours.  It not only rounds it but It truncates the decimal.  Take the following query for example:

SELECT emphours.empid, emphours.department, emphours.weekending, emphours.paytype, Sum(([endtime]-[starttime])) AS hours
FROM emphours
GROUP BY emphours.empid, emphours.department, emphours.weekending, emphours.paytyp

e

It results in:

PostgreSQL gives 47 which is definitely not the sum.  Access gives 47.8702777776634.

Any ideas?

I gave up on making the ODBC driver work in access to calculate this sum properly.

My solution was to create a view in PostgreSQL which calculated the sum of the hours.  See below:

    SELECT employees.empid,
    employees.startdate,
    employees.payrate,
    employees.surname,
    employees.first_name,
    emphours.weekending,
    emphours.department,
    emphours.paytype,
    sum(date_part('epoch'::text, (emphours.endtime - emphours.starttime) / 3600::double precision)) AS hours,
    sum(date_part('epoch'::text, (emphours.endtime - emphours.starttime) / 3600::double precision)) * employees.payrate AS totalpay
   FROM employees
     JOIN emphours ON employees.empid = emphours.empid
  GROUP BY employees.empid, employees.startdate, employees.payrate, employees.surname, employees.first_name, emphours.weekending, emphours.department, emphours.paytype;

Although I needed to change some access queries slightly, this seemed to be an effective workaround.

I also considered creating pass though queries but it seemed to be more work than creating the view.


Identify slow query without slow query logs in mysql server I am wondering is there any other way to to check our slow queries without logging slow query. Suppose, I have a highily busy server can't afford to log much to save memory and I/Os. Then, is there any other way available to check if I have a slow query? I know, we can do profiling of the query but still not sure what exactly to do to identify which query is the one taking most of the time and memory.

Just started mysql administration and not sure how to handle this. Any guidance will be highly appreciated.

If you do not want to enable the slow query log at all, I have a suggestion
You can use pt-query-digest over an interval of time.
I have suggested this a few times in the DBA StackExchange

Nov 24, 2011 : MySQL general query log performance effects
Apr 24, 2012 : Investigate peak in MySQL throughput
Jul 26, 2012 : What is running right now?

If you look at my Nov 24, 2011 link, I provided a shell script you can crontab to launch pt-query-digest.
GIVE IT A TRY !!!


Starting Search From a Specific Index I am not a pro in the database sector so pardon me if i am asking a basic question.
Scenario
I have a table in which all the recorded are stored chronologically.
Number of rows of that table is 1,445,248 (1.4 Million).
Desire
I want to optimize my query which looks like following: -
Select * from SalesInvoice where S_Date Between 20190401 And 20190501

Note

Yes, i need all the columns in the table and instead of typing out all the columns, i just used the all symbol (astrix).
S_Date is stored as numeric instead of DateTime (because it was developed a long time ago in this format)
The total time taken to execute this query is only 4 seconds but because of some calculations performed it is looping through ~80-90 time.

Question
I want to know, is there any way through which i can start the searching of rows from Nth position.
My Approach

Edit - Currently i am using SQL Server 2008 R2 but i will switch to MySQL that is why the query is written in SQL Server 2008 R2 format.
Step 1: Get first index of data by following query: -

Select Top 1 Id from SalesInvoice where S_Date Between 20190401 And
20190501 order by S_Date ASC

Step 2: Start Searching from the returned Id lets say 500,000

My Assumption if this technique exists
According to me, this technique might save table scanning time by starting the search from that ID which removes scanning of 500,000 rows.
BUT
I don't know how to start the lookup from a particular ID.
If anyone can provide me with a code snippet that will be very helpful

As for "all the recorded are stored chronologically", the only control you have over this is the PRIMARY KEY in InnoDB.

A simple rearrangement will provide the optimal access:

PRIMARY KEY(S_Date, id),  -- to order the data in the order desired
INDEX(id)   -- to keep AUTO_INCREMENT happy

(TOP 1 is not a MySQL construct; see LIMIT.  But it is not needed with the above change.)

(The technique above is even better than INDEX(S_date).)

Explanation

In MySQL, the data rows are ordered by the PRIMARY KEY.  Said another way: The PRIMARY KEY is "clustered" with the data.  This implies that you you fetch the rows in PK order, you will be fetching the data in the most optimal order.

With the change I proposed, the data will be ordered by S_Date (with dups ordered by id).  This is exactly the order you want.

To change the current table definition, do

ALTER TABLE SalesInvoice
    DROP PRIMARY KEY,
    ADD PRIMARY KEY(S_date, id),
    ADD index(id);

The auto_increment column must occur first in some index.  This allows the 'next' value can be efficiently found after a restart.

Stop after

  start the searching of rows from Nth position.

This performs such, but not efficiently:

ORDER BY ...
LIMIT 1000 OFFSET 20000

will skip (that is, read and discard) 20K rows, then deliver 1000.

It is more efficient to "remember where you left off".  With PRIMARY KEY(id), it is straight-forward and efficient:

WHERE id > $leftoff
ORDER BY id

With a composite index it gets messier, but still possible.  More discussion: http://mysql.rjweb.org/doc.php/pagination


How to manage log file size in large, heavy-use database We are a team of mathematicians (i.e., no DBA experience).

We have a large database in SQL Server 2012.  It has more than 2 TB of data (hundreds of tables, each with millions of rows and hundreds of columns wide).  Each month, we receive a bundle of additions and revisions to the data, requiring us to perform extensive updates to our database by deleting, replacing, or updating most or all of the tables.

Our work is mostly focused on crafting the SQL logic to calculate the results that we need.  We're not running a real-time call center.  We apply a few indexes as needed, and we're pretty happy with the performance.

The problem is the log file.  Naturally, the log file grows and grows with so much data manipulation.  Our log file currently stands at about 1 TB.  We have a good amount of disk space, but it is not infinite.

From what we read on the Internet, we understand that the log file is necessary for transaction integrity, rollbacks, and recovery.  But for our specific purposes, we don't care about any of that.  We will probably never perform a rollback, nor would we ever attempt a recovery.  Worse comes to worse, we would simply download the data files again and create a new database from scratch.

We really just want the log file to go away and never come back.

We set the database recovery mode to Simple, naively thinking that this meant "no recovery mode", but we were quickly cured of those illusions.

We also understand that there are many wrong things not to do (detaching, shrinking, etc.).  We just don't know the right thing to do.

Maybe someone is going to suggest that we set a limit to log file growth.  However, that leaves two issues:
(1)  How do we get rid of the 1 TB which is already there?
(2)  We previously tried that, and as we approached the specified limit we started getting Error 9002 (log file full) here, there, and everywhere.  So now we are afraid of applying a size limit.

How do we tell the database "No log file, please" without any hurt feelings?

If you never anticipate doing a point-in-time restore, then you just need to do 2 things:

Change the database to SIMPLE recovery mode
Do large changes in small batches.

In SIMPLE recovery mode, the log file is used only for transactions in process. When the transaction completes, the space in the tran log will be marked for re-use, and the next transaction will just re-use the space*.

(* This isn't strictly true, there are "checkpoints" that run occasionally that actually clear the space for re-use, but for the most part it works without intervention; only finicky DBAs will likely care about the inner details here.)

So keep your transactions bite-sized:

If you need to delete all rows of a table, don't do a DELETE of 100m rows (that will blow up the log), do a TRUNCATE instead, or just DROP the table and recreate it.
If you can't do that (you need to delete just some rows), do it in smaller batches
When doing large data imports, learn about bulk imports or an SSIS Fast Loads that load data quickly and commits the data every so often
Same thing for updates, don't update every row of a massive table, use more selective WHERE clauses to limit your updates to ~100k rows at a time. Learn how to use cursors in SQL

The exact details are going to depend on your actual database and actual activity, of course, but a database with 1TB of data shouldn't have a tran log file any larger than, say, 100Gb, depending on how big your batches are.

With regard to shrinking the existing file, you can use the following script to see the free/used space in your data and log files:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

If the used space in the log is high, then you likely still have open transactions. Look for the 3rd-party tool sp_WhoIsActive for digging into your current activity/open trans.

When you are ready to shrink, pick a reasonable target size, and execute a SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mylogfile', SIZE = 100000)

(That size is in MB).

If your log file still grows, go back to sp_WhoIsActive to see what transactions are running so long, and figure out how to do them in batches.


Date of last change of an InnoDB table structure I was wondering how to determine when was the last change (ALTER TABLE...) done to a given table.

An old answer is available but unfortunately that doesn't work for InnoDB tables.

Would this work, looking at the frm file date time?

-rw-r-----   1 mysql    mysql       18622 Oct 24 21:07 mysql/data/mydb/mytable.frm
-rw-r-----   1 mysql    mysql     2114688 Oct 28 06:26 mysql/data/mydb/mytable.ibd

Oct 24 21:07 seems to roughly correspond to the last time a change in the structure was performed.
Is that reliable information?

I would say yes because any ALTER TABLE must change the timestamp of the last write to .frm.

To pull the UNIXTIME out, you would do this

ls -l --time-style=+"%s" mysql/data/mydb/mytable.frm | awk '{print $6}'

or to get the date time

ls -l --time-style=+"%Y-%m-%d %H:%M:%S" mysql/data/mydb/mytable.frm | awk '{print $6,$7}'

MySQL does provide an UPDATE_TIME column, but that's for changes to data or indexes.


Sharding Mongodb while limiting the number of documents to be migrated I have little experience working with databases, but at my work I was tasked to deploy a sharded Mongodb cluster and the constrain on the shard is:

database with multiple collections, 7 collections will live on primary shard, 25 will be sharded evenly and one ( that has over 2.5 billion documents, 2.3Tb) will have 2Tb on primary and 0.3Tb divided withing the other clusters.
I have been reading the doc's from Mongodb website, tutorials on the internet and posted questions on different forums with no luck so far.

I understand that if you don't enable shard on the collection, it will live on only one collection. this will take care on the collections that will stay on the primary server.

Also, adding shard on the collection, will make the collection to shard evenly (or close to it), and that takes care of the 25 collections that can live on any shard.

Now for the last one I was thinking of a compound key using the id field (which is a unique field) together with "in_Use" field which is a boolean to divide the collection as in B=true stays on the primary shard and MOST, but not all, B=false goes tho the other shards.

A second problem with this collection is that the B=true values are changed to false after 24 hours and new documents with the value true are created, so they would needed to be swapped everyday. This solution I couldn't find on any documentation or tutorial, so I am not sure if it is doable.

Thirdly, since we have a size restriction on all shards except the primary one, we will have to restrict the size of the shards, according to the documentation, this can be accomplished by adding "maxSize" option to the addShard command, but i believe that would be set to all collections for the database and it might create a problem if more space are added dynamically. The shard might not increase it's "maxSize" dynamically.

Any ideas or suggestions are welcomed.

For your 7 collections on the primary shard:

Enter as administrator on the primary replica (if you shard your replica) and create your collection there by inserting 1 document or creating an index there. When you create a collection via the mongos - shard client - then the collection is started on a random shard; if you create it on the shard itself first, then you know it's on the one you want.  shard1$ mongo --port 27018 localhost/mydb --eval 'db.mycol.insert({firstdocument:'hello'})'
For the 25 collections:

Standard sharding indeed as you mention. Be patient with the distribution. The first shards will start distributing after a certain amount of data, can be 100k documents if the documents are small.
For the big collection:

Have a look at shard-tags. So, you have to tag your collection on shard1 with let's say : {status:'in_use'}; shard2 is tagged {status:'freeBeer'}. Then depending on the status in the document, the shard is chosen.
For the disk issue, I think you gonna have to write a script. Get a warning when 60% of a disk is used, and another warning at 80% to take action. Then you'll need to upgrade your disk or redistribute the chunks. Certainly the 2TB collection could give you space via the tag, to send more to shard2. Or, add a shard3 ... what is actually the purpose of sharding, to grow horizontally.
Choose shardKeys well, practice configuration and setup on a small server, practice moving chunks, adding tags, ....
Have a look at the AllChunkInfo script of Adam Comerford for you scripts and testing : https://github.com/comerford/mongodb-scripts


WHERE...LIMIT much slower than WHERE...ORDER BY...LIMIT in MySQL 5.5 I'm using MySQL 5.5 (but i see the same behavior on 5.7)
Here is the table schema:
CREATE TABLE `so_table` (
  `p` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c1` int(10) DEFAULT NULL,
  `c2` smallint(5) NOT NULL,
  `c3` tinyint(3) NOT NULL,
  `c4` int(10) NOT NULL,
  PRIMARY KEY (`p`),
  INDEX `so_table_index_1` (`c1`,`c2`),
  INDEX `so_table_index_2` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

The table contains around 2 million rows. The query below is considered a slow query and sometimes takes 20 seconds to execute:
SELECT p, c1 FROM so_table WHERE c1 IS NOT NULL AND c3 = 1 AND c4 < 195217615 LIMIT 100;
Running EXPLAIN on the query above returns the following:

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra

1
SIMPLE
so_table
NULL
range
so_table_index_1,so_table_index_2
so_table_index_1
5
NULL
1,281,736
Using index condition; Using where

I was reading about optimizing LIMIT and it seems that adding ORDER BY makes things better, so the following query indeed has a better execution plan (i haven't tried it on the real data though)
SELECT p, c1 FROM so_table WHERE c1 IS NOT NULL AND c3 = 1 AND c4 < 203322189 ORDER BY p ASC LIMIT 100;
And EXPLAIN shows the following:

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra

1
SIMPLE
so_table
NULL
index
so_table_index_1,so_table_index_2
PRIMARY
48
NULL
428
Using index condition; Using where

So it seems that this query is going to be faster. My guess is that ORDER BY forces the optimizer to use the primary index which is unique and maybe this helps the optimizer decide to not actually run through all rows. I was reading MySQL docs, but i couldn't figure out if my theory is right or what's the real reason is.

By adding the ORDER BY clause to your query, it resulted in the query optimizer choosing a different execution plan that uses the index to seek out the rows (which was a lot less rows to seek) as opposed to scanning the range of a lot more rows in your first query.
This isn't a guarantee that ORDER BY will always cause this to happen, but in this case it influenced the query optimizer to choose a different operation that happened to be more efficient (likely because the index it used is already sorted on the field you're ordering by).


It is possible to take incremental backup in mongoDB Shard cluster? without opsmanager Shard MongoDB Cluster:

3-router
3-config
2-shard (3-replica's per shard)

We are tring to take incremental backup for every one hour. We gone through few google links, but no luck. As per our above setup, to achive incremental backup we need to take mongodump with oplogs seperatly from config, shard1 and shard2 secondary servers and we will store it in s3. But the problem is with restoration part. To restore, all those dump (config, shard1 and shard2) needs to be downloaded from s3 and restore it to its specific replicaset. which means config dump to config and shard dump to shard.

Is this the correct process or we have any other option to take incremental backup with single query?

Thanks

Consider using Percona tool if you are using MongoDB version above 3.6. Taking dump of individual shards and trying to stitch it together while restoring is not advisable.


Where does the magic column "name" come from? I got this by accident:

db=> select name from site;
ERROR:  column "name" does not exist
LINE 1: select name from site;
               ^
db=> select site.name from site;
     name
---------------
 (1,mysitename)
(1 row)

The second query return a tuple containing a whole row. Using postgres 9.0.1.

Edit: the definition of site by request. I doesn't really matter, this quirk works for any table.

db=> \d site
                         Table "public.site"
 Column |  Type   |                     Modifiers
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('site_id_seq'::regclass)
 title  | text    | not null

NAME is actually a function. It's a quirk of Postgres that a function with one argument e.g. function(arg) can also be called as arg.function. From the docs:

  The equivalence between functional
  notation and attribute notation makes
  it possible to use functions on
  composite types to emulate "computed
  fields".

NAME is an internal type for object names, and this function is casting its argument to that type and returning it.


How to search hyphenated words in PostgreSQL full text search? I have to search for hyphenated words like 'good-morning', 'good-evening', etc.

My query is:

select id, ts_headline(content,
                       to_tsquery('english','good-morning'),
                       'HighlightAll=true MaxFragments=100 FragmentDelimiter=$')
from table
where ts_content @@ to_tsquery('english','good-morning');

When executing this query I also get results of 'good' and 'morning' separately. But I want exactly matching words and fragments.
(For ts_content I used the same default config english to create the tsvector.)

How can I search such hyphenated words in PostgreSQL full text search?

The key word here is phrase search, introduced with Postgres 9.6.
Use the tsquery FOLLOWED BY operator <-> or one of the related <N> operators. Or better yet, use the function phraseto_tsquery() to generate your tsquery.
Quoting the manual, it ...

produces tsquery that searches for a phrase, ignoring punctuation

And:

phraseto_tsquery behaves much like plainto_tsquery, except that it
inserts the <-> (FOLLOWED BY) operator between surviving words instead
of the & (AND) operator. Also, stop words are not simply discarded,
but are accounted for by inserting <N> operators rather than <->
operators. This function is useful when searching for exact lexeme
sequences, since the FOLLOWED BY operators check lexeme order not just
the presence of all the lexemes.

Your query would work like this:
select id
     , ts_headline(content, phraseto_tsquery('english', 'good-morning')
                          , 'HighlightAll=true MaxFragments=100 FragmentDelimiter=$')
from   tbl
where  ts_content @@ phraseto_tsquery('english','good-morning');

phraseto_tsquery('english', 'good-morning') generates this tsquery:
'good-morn' <-> 'good' <-> 'morn'

Since "good-morning" is identified as asciihword (hyphenated ASCII word), the stemmed complete word is added before the components. The manual:

It is possible for the parser to produce overlapping tokens from the
same piece of text. As an example, a hyphenated word will be reported
both as the entire word and as each component: (followed by an example)

to_tsvector() basically does the same on the other end, so everything matches up. This allows for fine-grained options with hyphenated words. The above only finds "good-morning" with a hyphen (or variants stemming to the same). To find all strings with "good" followed by "morn" (or variants stemming to the same) use phraseto_tsquery('english','good morning') generating this tsquery: 'good' <-> 'morn'
OTOH, you can enforce exact matches by adding another filter like:
...
AND content ~* 'good-morning'  -- case insensitive regexp match

Or:
...
AND content ILIKE '%good-morning%'

Seems a bit redundant to the human eye, but this way you get fast full text index support and exact matches.
The latter is mostly equivalent, but different (fewer) characters have special meaning in the LIKE pattern and might need escaping. Related:

PostgreSQL: Regular Expression escape function
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Example to demonstrate the operator <N>:
phraseto_tsquery('english', 'Juliet and the Licks') generates this tsquery:
'juliet' <3> 'lick'

<3> meaning that lick must be the third lexeme after juliet.


Oracle - Clause where in case when all!
I want to know how wrote this code of SQL, I already trying but i get message error.
I wrote two way different this SQL. See you below

SELECT *
  FROM tabela
 WHERE coluna IN (CASE WHEN 1 = :parametro THEN 1 WHEN 0 = :parametro THEN (2, 3) END)

And

SELECT *
  FROM tabela
 WHERE (CASE WHEN 1 = :parametro THEN coluna = 1 WHEN 0 = :parametro THEN coluna <> 1 END)

I tried to run but it get error. Please! you help to tick or fix this code.

Thank a lot.

Regards,
Jeison Pereira

One option is

SELECT *
  FROM tabela
 WHERE  'OK' =
         CASE WHEN   1 = :parametro and coluna = 1 then 'OK'
              WHEN   0 = :parametro and coluna in (2, 3) THEN 'OK'
         END


character encoding issues in mysql I have some legacy mysql files of the format frm, MYI and MYD that i wish to export to csv. Using sequel pro on OSX, i created a new database and copied those files over.

All the data can be accessed fine but i have major issues with the query results due to unknown encoding. I have no idea what format the previous programmer used.

This database contain English, Chinese, Japanese and Korean song titles but as i have no idea what format these names are encoded in, so i get weird characters using utf8 format:

ÄÐÉù
ÂèÂèºÃ+Éµ´ó½ã+ÆÃË®¸è
Àä¿ÕÆøµÄ¶À°×
À¶É«µÄÃÎ

Most of them begins with A with an accent though, i wonder if they do provide any clue as to which encoding format the programmer used.

I've tried putting these text onto safari/chrome browser and ran through the various encoding options but to no avail.

Will anyone please help me?

Is your client talking utf8?
Run

SET NAMES utf8;

and see if your selects output correctly?


How do you create a relationship to a non-primary key in SQL Server? I have a Users table which has two columns a primary key called UserID and another column called UserName.

UserID (int) PK
UserName (varchar(256)

They are both unique but I decided for reasons to use the UserName as a reference in other tables. So the order table for example has a reference to user by UserName not userid.

OrderID
UserName

I want to create a relationship between all tables that reference the UserName and the Users table so that I get the cascade update/delete feature of SQL Server.

But SQL Server doesn't allow me to create a relationship on a non primary key column. Is there any way I can get the cascade update/delete feature without changing the users table so that UserName is the primary key and not UserID?

Create a unique index or unique constraint on UserName then you can reference it in a FK constraint fine.

Your statement that

  Sql Server doesn't allow me to create a relationship on a non primary
  key column

is incorrect. SQL Server only cares that the column(s) participating in the FK relationship have a unique index defined.


Creating a row for each value in a comma-separated string I'm currently trying to create a View from a Table that does the following:

Table A
╔═════════════╦═══════════════╗
║ PART NUMBER ║   FILE NAME   ║
╠═════════════╬═══════════════╣
║  PN0001-01  ║      FOO      ║
║  PN0002-01  ║      BAR      ║
║  PN0003-01  ║    FOO,BAR    ║
║  PN0004-01  ║  BAR,FOO,FOB  ║
╚═════════════╩═══════════════╝

To create

View B
╔═════════════╦═══════════════╗
║ PART NUMBER ║   FILE NAME   ║
╠═════════════╬═══════════════╣
║  PN0001-01  ║      FOO      ║
║  PN0002-01  ║      BAR      ║
║  PN0003-01  ║      FOO      ║
║  PN0003-01  ║      BAR      ║
║  PN0004-01  ║      BAR      ║
║  PN0004-01  ║      FOO      ║
║  PN0004-01  ║      FOB      ║
╚═════════════╩═══════════════╝

So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.

First, create a string splitting function:

CREATE FUNCTION dbo.SplitString
(
    @List      nvarchar(max),
    @Delimiter nvarchar(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN
  (
    WITH x(x) AS
    (
      SELECT 1 UNION ALL SELECT x+1 FROM x
      WHERE x < (LEN(@List))
    )
    SELECT Item = SUBSTRING(@List, x,
      CHARINDEX(@Delimiter, @List + @Delimiter, x) - x)
    FROM x WHERE x <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, x, 1) = @Delimiter  );
GO

Then you can simply cross apply your source with the function:

DECLARE @x TABLE(PartNumber nvarchar(255), FileName nvarchar(max));

INSERT @x(PartNumber, FileName) VALUES
('PN0001-01','FOO'),
('PN0002-01','BAR'),
('PN0003-01','FOO,BAR'),
('PN0004-01','BAR,FOO,FOB');

-- INSERT dbo.ViewName(col1,col2)
SELECT x.PartNumber, f.Item
FROM @x AS x
CROSS APPLY dbo.SplitString(x.FileName, N',') AS f;

In SQL Server 2016, you can use the native STRING_SPLIT() function.


Convert Character Encoding, and Check Encoding for Issues I'm planning to convert a table from utf8 (_general_ci) to utf8mb4 (utf8mb4_unicode_ci). I want to make sure I don't have any malformed data after the conversion though so I was planning to duplicate the data, run a join on the two tables, and see if there are any differences.

Is this a good way to go about it?

1.

Create table Email_utf8mb4  like Email;

2.

insert into Email_utf8mb4 select * from Email;

3.

ALTER TABLE Email_utf8mb4
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

4.

select * from Email as old
join Email_utf8mb4 as new
on old.notificationid =new.notificationid
and (old.subject <> new.subject OR old.content <>
    new.content)

4.5. Assuming no rows are returned...

5.

drop Email;

6.

RENAME TABLE Email_utf8mb4 to Email;

subject and content are the only alpha columns in my table.

That won't fix or recognize any malformed data already in the table.

Yes, that is a good technique.  ALTER..CONVERT TO.. does the bulk of the work.  And, since utf8 is a subset of utf8mb4, there should be no differences discovered in step 4.

However, there is still a possibility of step 4 showing something.  This is because the definition of "equal" (hence <>) is different for _general_ci versus _unicode_ci.

For example, in German, ss and ß are unequal in _general_ci (either utf8 or utf8mb4), but equal in virtually all other collations.  If, for example, you currently have a UNIQUE (or PRIMARY KEY) with a value that differs in just ss vs ß, the conversion will have a problem with "duplicate key" error.

Another problematic pair:  ae vs æ

A different issue - not equality, but ordering - Ð < E for _unicode_ci, but not for some other collations.

Meanwhile, as long as you are changing the COLLATION, you may as well go  to the newer Unicode Algorithm in utf8mb4_unicode_520_ci.