Securing Your MySQL Server within your team? Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things:

OLD_PASSWORD = 1

(So all passwords on our development server were encrypted with a 16 bit hash, I think, which is really bad)

The GENERAL_LOG is off. This is important as some team members access MySQL via a GUI (MySQL Workbench) and are then able to change the root password by:

SELECT * FROM mysql.user;

And then manually changing the password, and clicking apply.

In my opinion the general_log should be on so we can place the blame on team members. Also could backups of the entire database be made nightly? How would that be done? How does this affect performance?

I know that when accessing via command line, the server stores all MySQL commands in a hidden file in the home directory of a user in .mysql_history. Is this the best option or is there another way of logging MySQL cli commands?
This is also a security threat if someone has access.

What are the best practices in ensuring that other people, cannot change any of the root password or permissions/grants. Would this mean not allowing any users to change permissions entirely. Would they still be able to manually change the mysql.user table.

The team members access MySQL using the root password so I have contemplated creating a new user, that does not have the ability to change permissions and password and even drop tables. Then changing the root password and telling them to use that account.

Also in terms of logging all MySQL command line and the MySQL general log how can I implement log rotation on these files.

Also do you have any other best practise suggestions for a development and production server?

Some best practices:

Create a DBA user for yourself. This should be the only user with "WITH GRANT OPTION" in their permissions. This should also be the only user with select privileges on *.* because that includes mysql.user.
Every user should have their own username and password, no shared accounts. They should each have permissions to specific databases; again, not *.*.
And then delete that shared root user.
Don't let non-DBAs have root access on your db server.
Symlink your ~/.mysql_history file to /dev/null and it won't record anything.

There are others; this will get you to a better place.

You can also write a little script that outputs the grants (e.g., using pt-show-grants) and diffs to the previous output, and then emails you when there's a difference.

btw, it's hard to have enough space to store a general log on an active production system. It can get very big very quickly.


Group by with unrelated counts I have three tables, one, two and three.

one

two

three

With this query

select one.id, count(two.id) as num_twos, count(three.id) as num_threes from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id

I expect to get

1 2 1
2 1 4
3 0 0
4 0 0

because there are

two records in two that relates to one_id 1 and one record in three that relates to one_id 1.
one record in two that relates to one_id 2 and four records in three that relates to one_id 2.

but I get

Why? The counts seems to get multiplied. Can I get the desired behavior without subqueries?

Image this :

select one_id,count(id) as num_Twos
from Two
group by one_id

with the output :

one_id      num_Twos
1           2
2           1

And then join to the main table (this way, the main records, from table One, are not duplicated,and no need of distinct ):

select
    id, num_Twos, isnull(num_Twos,0) as numTwos
from One
    left join
    (
        select one_id,count(id) as num_Twos
        from Two
        group by one_id
    )as two
        ON id =two.one_id

output: (used isnull - to put 0 instead of NULL)

id          num_Twos    numTwos
1           2           2
2           1           1
3           NULL        0
4           NULL        0

the same thing , with table 3
From comments: this way, you don't need the top level group by , because the all the information are already aggregated in the () as Two and () as Three

or with distinct:

select one.id
        , count(distinct two.id) as num_twos
        , count(distinct three.id) as num_threes
from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id

output :

id          num_twos    num_threes
1           2           1
2           1           4
3           0           0
4           0           0

the source:

with One
as (select 1 id union all select 2 union all select 3 union all select 4)
,Two
as (select 1 as id, 1 as one_id Union all select 2,1 union all select 3,2)
,Three
as (select 1 as id,1 as one_id union all select 2,2 union all select 3,2 union all select 4,2 union all select 5,2)

also , you can rewrite it as :

select id
        , isnull(num_twos,0) as num_twos
        , isnull(num_threes,0) as num_threes
from one
left join (select one_id ,count(id) as num_twos from two group by one_id) as two on one.id = two.one_id
left join (select one_id ,count(id) as num_threes from three group by one_id) as three on one.id = three.one_id


How to select from 3 tables while using count on 1, and sum on another? I'm stuck on this one query. Here is my setup:

table 1 (shows)

show_user
show_count

table 2 (views)

view_user
view_views

table 3 (users)

user
user_name

What I need to is produce results that contain:

sum(show_count), count(view_views), user_name

So far I have tried the following:

select sum(s.show_count), count(v.view_views), u.user_name
from users u
left join shows s on u.user = s.show_user
left join views v on u.user = v.show_user
group by u.user

The query is taking an eternity to run and returns neither the right count nor the sum.

Individually, the following queries work properly:

select sum(show_count), show_user from shows group by show_user;
select count(view_view), view_user from views group by view_user;

I assume you have multiple entries in shows, views table for each user. When joining all three together, you get more rows than you expect: if for some user you have x rows in table shows and y rows in table views, then the result set contains x*y rows for that user, so you sum each entry in shows y times, and each entry in views table gets counted x times.

You need to aggregate each table separately, and then join the results:

select t1.sum1, t2.cnt, u.user_name
from users u
left join (select show_user, sum(show_count) sum1 from shows group by show_user) t1 on u.user = t1.show_user
left join (select view_user, count(view_views) cnt from views group by view_user) t2 on u.user = t2.view_user

SQLFiddle


Setting up Mongo with clustering I'm going to start using Mongo as a database but I will not have it clustered at first.  Can I use Mongo on a single machine, then later come back, cluster it and capitalize on the sharding?  Or can I set it up for a cluster with only one machine until I have more to add to the cluster?

You can do it either way.

There isn't much benefit to starting all of the overhead for sharding now, unless you simply want to try it out to see how it works. It is important to pick the correct shard key, so you would want to make sure your schema and insert/update usage is firm enough to know what to pick.

Kristina Chodorow has a great blog entry about how to pick a shard key: http://www.snailinaturtleneck.com/blog/2011/01/04/how-to-choose-a-shard-key-the-card-game/

For general information about how sharding works:
http://www.snailinaturtleneck.com/blog/2010/03/30/sharding-with-the-fishes/

For some instructions on how to set up clustering on a single machine, see here:
http://www.mongodb.org/display/DOCS/A+Sample+Configuration+Session
and here:
http://www.snailinaturtleneck.com/blog/2010/08/30/return-of-the-mongo-mailbag/


How to remove multiple redundant indexes? I have a legacy app.
I wanted to find out which tables are the largest ones using script from Percona blog. It turned out some of my tables have indices that are larger than actual data by factor of 3 and more.
I've dumped schema off the MySql server and found that some tables have lots of what seem to be redundant indices.

Here's how one of the worst tables is created:

CREATE TABLE `pictures_relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pic_id` int(10) DEFAULT NULL,
  `url_id` int(10) DEFAULT NULL COMMENT 'id урла, с которого файл был/будет скачан',
  `is_stub` tinyint(1) NOT NULL DEFAULT '0',
  `item_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `item_type` varchar(8) NOT NULL,
  `is_file` tinyint(1) NOT NULL,
  `is_hide` tinyint(1) NOT NULL,
  `is_cover` tinyint(1) NOT NULL,
  `image_only` tinyint(1) DEFAULT NULL,
  `is_custom` tinyint(1) NOT NULL DEFAULT '0',
  `skip` tinyint(1) NOT NULL DEFAULT '0',
  `order` int(11) NOT NULL,
  `author` int(11) NOT NULL,
  `add_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_hide` (`is_hide`),
  KEY `is_cover` (`is_cover`),
  KEY `add_date` (`add_date`),
  KEY `item_id_2` (`item_id`,`module_id`,`item_type`,`is_file`,`is_cover`),
  KEY `order` (`item_id`,`module_id`,`item_type`,`is_file`,`order`),
  KEY `item_id` (`item_id`,`module_id`,`item_type`,`is_file`,`is_custom`),
  KEY `pic_id` (`pic_id`),
  KEY `item_id_3` (`item_id`,`module_id`,`item_type`,`is_file`,`skip`),
  KEY `item_id_4` (`item_id`,`module_id`,`item_type`,`is_file`),
  KEY `pic_id_2` (`pic_id`,`skip`),
  KEY `skip` (`skip`,`pic_id`),
  KEY `stub_url` (`is_stub`,`url_id`),
  KEY `stub_skip_url` (`is_stub`,`skip`,`url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6794944 DEFAULT CHARSET=utf8;

I can clearly see that indices order, item_id, item_id_2, item_id_3 and item_id_4 are mostly similar, but they differ a bit. Using convenience views for easily detecting redundant indexes from this DBA.StackExchange answer helps, but not much — it does show that item_id_4 is redundant to all others, but doesn't help with splitting/merging them into more sane indexes.

The question is:
Is there a Rule of Thumb to refactor these indexes into something sane, with less duplication?

Like, if I have idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), idx3('a', 'b', 'e'), then I have to make it into idx1('a', 'b'), idx2('c'), idx3('d') and idx4('e')?

The question cannot be answered without seeing the SELECTs.

Here are some partial answers...

The only obviously redundant queries are pick_id and item_id_4. The rule is simple:  If all the column(s) of an index are exactly the first column (s) of some other index, then toss it.  (Note: This applies only to non-unique indexes.)
The next set of apparent duds are (is_*).  These all seem to be true/false "flags".  The Optimizer won't use such (with rare exceptions).  It will, on the other hand, consider INDEX(is_foo, ...).  That is a composite index starting with a flag.  (And, no, the low cardinality of the first column is not bad.)
5 columns in an index is pushing the max that it "should" have.  I would be tempted to keep one of the 5-column indexes starting with (item_id,module_id,item_type,is_file).  The ones I tossed would still be benefit, at some level, by the one I keep.
Reordering the columns depends on what the SELECTs look like.  If the user can request "any" combination of columns to filter, then punt.  Well, not totally punt...  Make a few combinations of 2 or 3 columns.
INDEX(a,b,c) is not the same as INDEX(a,b), INDEX(c).  Some queries benefit from one; some from the other.

See also: Cookbook on generating indexes


SQL Server incremental Log backups We are taking COPY_ONLY (non-truncating) transaction log backups every 2 hours. Our requirement is to take the incremental log backup every 2 hours and not cumulative. I did not find an option in BACKUP LOG TSQL command. Is there a way to do this?

We are taking COPY_ONLY (non-truncating) transaction log backups every
  2 hours.

Reference: Copy-Only Backups (SQL Server)

You need to understand the difference between COPY_ONLY and without COPY_ONLY?

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose. +

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore. For an example of this, see Example: Online Restore of a Read-Write File (Full Recovery Model).

  Our requirement is to take the incremental log backup every 2 hours
  and not cumulative.

Schedule transaction log backup every 2 hours.  Make sure your business stack holders understand the consequence of taking transaction log backup every 2 hours.

Read this answer (What's a good SQL Server backup schedule?) for more a better understanding.  Here is (Transaction Log Backups (SQL Server) ) Microsoft's official standing on this.

  I did not find an option in BACKUP LOG TSQL command. Is there a way to
  do this?

There is enough example at the bottom of this page.

BACKUP (Transact-SQL)

As a side note ( I am advocating this) many Database professionals use Ola Hallengren's solution.

https://ola.hallengren.com/sql-server-backup.html


Postgres inplace PITR (point in time recovery) or rollback I have a Postgres 12.x database (that is cloud hosted).  This particular database instance is a nightly copy of production.  That is used for debugging etc ...
But during the debugging process we might want to roll it back to the time that it was created.
Is there an elegant way to do this?
I imagine like some uber transaction, that I can rollback to.  Is this crazy-talk -- or will postgres continue to amaze me.

There is no such facility in PostgreSQL but maybe a template database could help.
Create the template database as a copy of the production system. Then, whenever you need a test database, clone it with
CREATE DATABASE testdb TEMPLATE templatedb;


Does a primary key contain exactly one Null value...? My work

A primary key is unique and only one contains for a table.
There are many unique keys that can contain in a table.
In SQL Server, the primary key is not Nullable. But, a unique key may contain only one null value.

My question:

Practically in SQL Server, the primary key is not nullable. But when it comes in theoretical, why don't we can have exactly only one Null value in the primary key when it allows a Null value in the unique key?

Thanks for answering my question

Sorry for asking a general question, after reading the answer I got a clear idea about null values in DB.

Also, I confused with programming languages like in C++, Jave, where the null value can be comparable.
After doing some work, I understand that Null is not comparable in SQL.

  In programming languages like in C++, Jave...

Null==Null

returns TRUE

  But, in SQL

Null==Null

returns FALSE, because Null value is unknown and it could be any value.

No attribute that is part of a key can contain nulls; if a column is nullable then it isn't part of any key. It is also the case that the set of columns defined by a UNIQUE or PRIMARY KEY constraint is NOT a key unless it is a minimal superkey and also non-nullable.
Why can't keys permit nulls? There are lots of potential reasons:

Keys are a relational concept whereas nulls (as we know them today) are essentially a SQL invention which came along years later. SQL DBMSs are different to Relational DBMSs.
Nulls are not values so they are not key values. Nulls are markers for unspecified values.
Keys are supposed to be irreducible (minimal) - in other words all the components of the key are required to make a superkey. Since null means that some components are missing it contradicts this most basic definition of a key.
In standard SQL a uniqueness constraint does not enforce uniqueness if any column permits nulls.
SQL DBMSs also permit nulls in other constraints (except PRIMARY KEY): FOREIGN KEY, CHECK, ASSERTION. Nulls never violate the constraint so the null acts as a kind of "get out clause" for data integrity. Hypothetically, if nulls were permitted in keys then that would seem to imply a stricter form of referential integrity that didn't permit nulls in foreign keys unless there was a corresponding null key in the parent table. In that case every nullable foreign key would have to reference a nullable key and could not reference a non-nullable one.
There are no nulls in reality, in everyday mathematics or logic; they are just a programming feature in SQL and are not particularly "mathematical" or even consistent in the way they work. Don't look for more reasons: nulls work the way they do just because the designers of SQL say so.


big db for managing user files and settings I'm about to start a project that will hopefully eventually hit milion+ users. It is some kind of a site managment. Problem is how to set the db in MySQL? Some people suggest way 1 and others way 2. I hope you can help me decide the right path to start.

needs to be stored:

all users info:
user | pass | account status
user files:
path to compiled htm(in user folder with uid) | http
request path | file seg1 | file seg2 | file seg3
user settings:
personal settings (email..) | site settings (colors..) |

my ideas to store the data are:

path 1:
have main db with table for usernames and passwords.
create each user with db which inside has a table for user files and a table for user settings.
path 2:
have main db for system which inside has:
table for users and passwords and 2 more tables for each user: user_files, user_settings.

edit, add another path:

path 3:
3 DB.. one for users\passwords second for user_files and third for user_settings. This way it would separate the rows' weight evenly between the user_files and user_settings. Is that true?

And would a flat file system without the DB (beside the user password) be a better option here?

thank you.

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas.  Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;


How to create a row for every day in a date range using a stored procedure? I would like to create a stored procedure that will create a row in a table for every day in a given date range.  The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user.

So, let's say I have a table like so:

SELECT Day, Currency
FROM ConversionTable

Day is a DateTime, and Currency is just an integer.

To keep things simple, let's just say I always want the Currency column to be 1 for each of these inserted rows.  So, if someone inputs 'March 5, 2017' as the start date and 'April 11, 2017' as the end date, I would like the following rows created:

2017-03-05 00:00:00, 1
2017-03-06 00:00:00, 1
...
2017-04-11 00:00:00, 1

What's the best way to code the stored procedure to do this?  I am using SQL Server 2008 R2 in my test environment, but our real environment uses SQL Server 2012, so I can upgrade my test machine if there is new functionality introduced in 2012 that makes this task easier.

One option is a recursive CTE:

DECLARE @StartDate datetime = '2017-03-05'
       ,@EndDate   datetime = '2017-04-11'
;

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT theDate, 1 as theValue
  FROM theDates
OPTION (MAXRECURSION 0)
;

(MAXRECURSION hint added thanks to Scott Hodgin's comment, below.)


SQL Server backup failing. Error: 3041, Severity: 16, State: 1 First of all, I'm very new at stackexchange, so please bear with me.

I'm running a SQL Server 9.0.4060.

My problem is: my SQL Server backup keeps failing on a lot of the databases.

The Maintenance plan runs to different backup jobs:

DIFF backup everyday at 20:30 and FULL backup every Sunday at 20:30.

I get these two errors foreach database that fails to complete the backup:

Date        06-11-2012 20:31:06
Log     SQL Server (Current - 07-11-2012 11:43:00)
Source      Backup
Message
BACKUP failed to complete the command BACKUP DATABASE (dbname) WITH DIFFERENTIAL. Check the backup application log for detailed messages.

Date        06-11-2012 20:31:06
Log     SQL Server (Current - 07-11-2012 11:43:00)
Source      Backup
Message
Error: 3041, Severity: 16, State: 1.

Extra errors.

Date     06-11-2012 17:53:27
Log      SQL Server (Current - 07-11-2012 11:43:00)
Source   Logon
Message  Error: 18456, Severity: 14, State: 16.

Date     06-11-2012 17:53:27
Log      SQL Server (Current - 07-11-2012 11:43:00)
Source   Logon
Message  Login failed for user 'sa'. [CLIENT: <local machine>]

Date     06-11-2012 20:30:11
Log      SQL Server (Current - 09-11-2012 11:27:00)
Source   spid15s
Message
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

There are to other similar errors to the one above, with 'SQL Plans' and 'Object Plans'. Instead of 'Bound Trees'.

If you guys need any other information, just say the word.

As Max pointed out, the sa login error doesn't corrolate to the backup time as it happens several hours before, however the 2nd one does.  Have you looked into:
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx

http://www.sqlservercentral.com/Forums/Topic377116-146-1.aspx#bm380502

Do any of the items in the MSDN blog play a factor in your environment?  Most common one would be the AUTO_CLOSE ON.  Let us know, thanks.


Data migration: copy data with insert select and fill unique column I have some tables in Postgres like this.

create table foo (
    txt text
);
insert into foo (txt) values ('a');
insert into foo (txt) values ('b');
insert into foo (txt) values ('c');

create table bar (
  txt text,
  n integer not null unique
);

I'm doing a schema migration. I would like to migrate data from foo to bar. I'd like bar to look like this.

=# select * from new;
txt | n
----+--
a   | 1
b   | 2
c   | 3

n doesn't have to be sequential, but it does have to be unique and not null.

I thought this query would work.

insert into bar (txt, n)
select txt, generate_series(1, (select count(*) from foo))
from foo;

But I think that's generating rows 1-N over and over again for each 'a', 'b', 'c', which violates the unique constraint.

SQL Fiddle: http://sqlfiddle.com/#!17/06e92c

Update

Okay, I found one hacky way to do it.

insert into bar (txt, n)
select txt, trunc(random() * 999999 + 1)
from foo;

Of course this isn't guaranteed to be unique. :/ But, if the data set is small enough, you might be able to scrape by with this.

Also, I understand why the first approach failed now. generate_series returns multiple rows, whereas with random I only return 1 row.

Why not use row_number()

insert into bar (txt, n)
select txt, row_number() over ()
from foo;

I left out an order by which would slow down the query and wouldn't really be an advantage in your case as you just want a unique number.

An alternative would be to create a sequence for it, if the performance of the window function isn't sufficient.

create sequence seq_foo_nr;

insert into bar (txt, n)
select txt, nextval('seq_foo_nr')
from foo;

drop sequence seq_foo_nr;

If you need an auto-generated number anyway, then define nr as an identity column:

create table bar (
  txt text,
  n integer not null unique generated always as identity
);

insert into bar (txt)
select txt
from foo;


Why doesn't MySQL coerce the collation to the column-specified, when comparing to a literal? I have a table with a collation defined on a column:

CREATE TEMPORARY TABLE test_table (
  utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);
INSERT INTO test_table VALUES('ã');

According to the MySQL coercibility rules, when a value from that column is compared to a literal:

SELECT utf8_col < _utf8mb4'ñ' FROM test_table;

the column collation coercibility value is 2;
the literal CCV is 4

this should cause the comparison to fail, because the column CCV has higher priority, and should use the utf8_bin collation, which can't handle utf8mb4 character sets.

The comparison is successful, however. Why is that?

Why does the above comparison succeed?

Because MySQL is being nice and auto-correcting a user error, but only because it can.

You are not wrong that:

  the column CCV has higher priority

and:

  the utf8_bin collation can't handle utf8mb4 character sets.

However, you are not seeing what you think you are seeing in your test because you are not testing with data that requires the "mb4" version of the "utf8" collations. You are testing with a "safe" value that fits into the non-supplementary character-capable utf8 charset. In this situation, MySQL simply uses the charset of the column — utf8 — and the utf8_bin collation is just fine.

The world looks a little different when you use a 4 byte character (i.e. a supplementary character). In this case, the character cannot exist in the utf8 charset (because the utf8 charset can only handle BMP characters / the first 65,536 code points / U+0000 through U+FFFF / characters that are 1 to 3 bytes each). And so, since the charset of the string literal cannot be changed to the one that does work with the column's collation — utf8_bin — you would get an error stating:

  Illegal mix of collations (utf8_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '<'

I tested with code point U+1F369 (i.e. the donut emoji: "🍩"):

CREATE TABLE test_table (
  utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);

INSERT INTO test_table VALUES('ã'); # any utf8 character from U+0000 to U+FFFF

# Test against any utf8 character from U+0000 to U+FFFF in _utf8mb4
SELECT utf8_col,
       CHARSET(utf8_col) AS 'charset',
       COLLATION(utf8_col) AS 'collation',
       CONCAT(utf8_col, _utf8mb4'ñ') AS 'concat1',
       CHARSET(CONCAT(utf8_col, _utf8mb4'ñ')) AS 'charset_concat1'
FROM test_table;

# Test against any utf8 character from U+10000 to U+10FFFF in _utf8mb4
SELECT utf8_col,
       CHARSET(utf8_col) AS 'charset',
       COLLATION(utf8_col) AS 'collation',
       CONCAT(utf8_col, _utf8mb4'') AS 'concat2',
       CHARSET(CONCAT(utf8_col, _utf8mb4'')) AS 'charset_concat2'
FROM test_table;
# FAIL

SELECT utf8_col < _utf8mb4'ñ' FROM test_table; # Success

SELECT utf8_col < _utf8mb4'' FROM test_table; # FAIL

See the above example code in action on dbfiddle.uk

The result set from the first SELECT is:

utf8_col    charset    collation    concat1    charset_concat1
ã           utf8       utf8_bin     ãñ         utf8

which shows that the utf8mb4 string was coerced into utf8, and everyone was happy. Was happy, until Mr Donut showed up in the second SELECT statement (you would think that donuts would make everything better, though, wouldn't you? I generally do 😸).


sp_blitzfirst results to table on a linked server I have been all over looking for the solution to logging sp_blitzfirst results to table on a linked server. I see github posts where a contributor was adding this to sp_blitz, but that's been closed for a while. I see vague rumblings that this might already be in sp_blitzfirst, but am ready to be punished for my inability to find the solution on my own.

Can someone point me to a definitive answer?

There is a note in the script on GitHub:

  Known limitations of this version:

  ...

  @OutputServerName is not functional yet.

And indeed, that parameter is not used in the proc.  So for now, storing the output to a table across a linked server appears to not be supported.

Since you included the SQL Server 2008 tag in your question, I'll also mention that they don't go out of their way to keep the scripts working on unsupported versions of SQL Server, and you're only a few months away from that end of support date (July 2019!).  I imagine the scripts will continue to work for a while, but there are no guarantees at that point.


How to get the function name of a regprocedure in PostgreSQL? I'm trying to write a function to get the name of the function associated with a given regprocedure variable, as follows:

CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
  RETURNS text AS
$func$
  SELECT proname::text
  FROM pg_catalog.pg_proc AS p
  JOIN pg_catalog.pg_namespace AS ns
    ON p.pronamespace = ns.oid
  WHERE p.oid = _fn;
$func$  LANGUAGE sql;

I expect to get my_func in return for

SELECT get_funcname('my_func(text, variadic text[])');

However, the above is giving me the error:

ERROR:  syntax error at or near "variadic"
LINE 1: SELECT get_funcname('my_func(text, variadic text[])');
                                ^
CONTEXT:  invalid type name "variadic text[]"

Can anyone help explain how to fix the function or syntax?

You have

ERROR:  syntax error at or near "variadic"

From the docs,

  VARIADIC parameters are input parameters, but are treated specially as described next.

That means you don't do anything special for VARIADIC parameters beyond what you would otherwise specify for input params. In your case, just drop that token,

SELECT get_funcname('my_func(text, text[])');

  Can anyone help explain how to fix the function or syntax?

Sure.

WHERE p.oid = regexp_replace(_fn, ' VARIADIC ', ' ', 'i');


Is there any reason I shouldn't delete orphaned database users? I've inherited a business application which has been around for c. 15 years composed of multiple databases that are all hosted on the same SQL Server (2012 SP4) instance.

In all of the databases there are SQL Server database users (not ones that are related to Windows Users or Groups) that are not mapped to any server login. These have accreted over the time the system has been in use. Specifically database users here not server logins. My suspicion is that they've been orphaned over time as the databases have been migrated from one server to the next over the last 15 years.

Some of these users are members of the db_owner database role and have thus been flagged up by sp_blitz. Whilst change control and approval are important; they're not the main thrust of this question. I identified the orphaned users due to sp_blitz flagging them because they have db_owner rights; the fact that they have db_owner is tangential to the core question here.

No database or agent jobs are associated with them - for the most part they look like utility accounts that were created for various tools and solutions which were subsequently deprecated or superseded - which explains why they didn't get re-created as server logins when the databases moved from one server to the next.

As best I can tell from my research, these users are entirely inert and won't have any access to the system (due to not being associated with a login), so: Is there any reason I shouldn't remove these users?

As pointed out in a comment by eckes you should contact management and stakeholders for approval before deleting them.

Additionally if there are test or development instances that have copies of the DB, there may be logins on those instances where the users have access. You will want to consider impact there before making changes.

Part of reaching out to stakeholders is identifying who is currently the responsible business user(s).  It is not unusual to find a database that everyone forgot about and reaching out to these people that used to have access can be helpful.


Very slow insert statement Oracle 11.2.0.4 on OEL 6.5

Our app admin run some update script from app vendor,
and this script has been running for several hours and no one know when it will end. Also database now generates lots of archivelogs.
I started sql trace for 15 minutes and found that there is almost the only query.

INSERT INTO house_t
VALUES
 (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16,
 :17, :18, :19, :20, :21, :22, :23)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       70      0.00       0.00          0          0          0           0
Execute     71     23.86     633.03      83564      41375    4597750      355735
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      141     23.87     633.03      83564      41375    4597750      355735

Why one simple insert needs to use this huge amount of blocks in current mode 64757=4597750/71 ???

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     0          0          0  LOAD TABLE CONVENTIONAL  (cr=584 pr=1443 pw=0 time=11388214 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
SQL*Net more data from client                9523        0.00          0.08
db file sequential read                     82252        0.60        603.94
SQL*Net message to client                      70        0.00          0.00
SQL*Net message from client                    70        0.00          0.15
log file switch completion                      4        0.08          0.18
undo segment extension                          4        0.00          0.01
log file sync                                   1        0.00          0.00

Table has simple structure:

SQL> desc house_t
Name                 Null?    Type
-------------------- -------- --------------
POSTALCODE                  VARCHAR2(6)
IFNSFL                      VARCHAR2(4)
TERRIFNSFL                  VARCHAR2(4)
IFNSUL                      VARCHAR2(4)
TERRIFNSUL                  VARCHAR2(4)
OKATO                       VARCHAR2(11)
OKTMO                       VARCHAR2(11)
UPDATEDATE                  DATE
HOUSENUM                    VARCHAR2(20)
ESTSTATUS                   NUMBER(5)
BUILDNUM                    VARCHAR2(10)
STRUCNUM                    VARCHAR2(10)
STRSTATUS                   NUMBER(5)
HOUSEID                     VARCHAR2(36)
HOUSEGUID                   VARCHAR2(36)
AOGUID                      VARCHAR2(36)
STARTDATE                   DATE
ENDDATE                     DATE
STATSTATUS                  NUMBER(5)
NORMDOC                     VARCHAR2(36)
COUNTER                     NUMBER(10)
CADNUM                      VARCHAR2(100)
DIVTYPE                     NUMBER(5)

There are no triggers on this table, no mviews, no defined fga policies, no fgac policies.

Also table has 3 indexes

CREATE UNIQUE INDEX HOUSE_T_IDX0 ON HOUSE_T(HOUSEID);
CREATE INDEX  HOUSE_T_IDX1 ON HOUSE_T (T_HOUSEGUID);
CREATE INDEX HOUSE_T_IDX2 ON HOUSE_T (T_AOGUID);

Raw trace file is full of 'db file sequential read' waits on this 3 indexes and undo blocks.

Table now has about 50M records, size is about 12Gb.
Indexes are about 3-4GB and have blevel=3 in dba_indexes.

I expect that this insert statement can take 20-30 blocks to run, but why thousands ?

Steps to reproduce:

create table house_t
(
POSTALCODE                  VARCHAR2(6),
IFNSFL                      VARCHAR2(4),
TERRIFNSFL                  VARCHAR2(4),
IFNSUL                      VARCHAR2(4),
TERRIFNSUL                  VARCHAR2(4),
OKATO                       VARCHAR2(11),
OKTMO                       VARCHAR2(11),
UPDATEDATE                  DATE,
HOUSENUM                    VARCHAR2(20),
ESTSTATUS                   NUMBER(5),
BUILDNUM                    VARCHAR2(10),
STRUCNUM                    VARCHAR2(10),
STRSTATUS                   NUMBER(5),
HOUSEID                     VARCHAR2(36),
HOUSEGUID                   VARCHAR2(36),
AOGUID                      VARCHAR2(36),
STARTDATE                   DATE,
ENDDATE                     DATE,
STATSTATUS                  NUMBER(5),
NORMDOC                     VARCHAR2(36),
COUNTER                     NUMBER(10),
CADNUM                      VARCHAR2(100),
DIVTYPE                     NUMBER(5)
);

Sample data:

insert into house_t (HOUSEID, HOUSEGUID, AOGUID)
with g as (select * from dual connect by level <= 1000)
select sys_guid(), sys_guid(), sys_guid() from g,g,g
where rownum <= 50000000;
commit;

SQL> select count(*) from house_t;

  COUNT(*)
----------
  50000000

CREATE UNIQUE INDEX HOUSE_T_IDX0 ON HOUSE_T(HOUSEID);
CREATE INDEX  HOUSE_T_IDX1 ON HOUSE_T (HOUSEGUID);
CREATE INDEX HOUSE_T_IDX2 ON HOUSE_T (AOGUID);

SQL> select segment_name, bytes/1024/1024 from user_segments;

SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
HOUSE_T                                   6336
HOUSE_T_IDX0                              2368
HOUSE_T_IDX1                              2432
HOUSE_T_IDX2                              2432

Now to simulate your script:

SQL> alter session set tracefile_identifier=BALAZS1;
SQL> exec dbms_session.session_trace_enable(true, false);

declare
  TYPE T_HOUSE_T_ARRAY IS TABLE OF VARCHAR2(36);
  L_DATA T_HOUSE_T_ARRAY;
  CURSOR c IS SELECT sys_guid() from dual
    connect by level <= 50000 order by dbms_random.value;
BEGIN
  for i in 1..70
  loop
    begin
      OPEN c;
      LOOP
      FETCH c BULK COLLECT INTO L_DATA;
      FORALL i IN 1..L_DATA.COUNT
      INSERT INTO house_t (HOUSEID, HOUSEGUID, AOGUID)
        VALUES (L_DATA(i), L_DATA(i), L_DATA(i));
      EXIT WHEN c%NOTFOUND;
      END LOOP;
      CLOSE c;
  commit;
end;
  end loop;
END;
 21  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:58.97
SQL> exec dbms_session.session_trace_disable;

And the tkprof output:

SQL ID: 4h4byjv3yd590 Plan Hash: 0

INSERT INTO HOUSE_T (HOUSEID, HOUSEGUID, AOGUID)
VALUES
 (:B1 , :B2 , :B3 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     70     82.17     105.82      23693     359178   43342331     3500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71     82.17     105.82      23693     359178   43342331     3500000

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     24243        0.01         11.82
  ...

And an enormous amount of leaf node splits:

select sn.name, ss.value from v$statname sn join v$sesstat ss
  on (sn.statistic# = ss.statistic#)
where sid = sys_context('userenv', 'sid')
  and name in ('leaf node splits', 'leaf node 90-10 splits',
    'branch node splits', 'root node splits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                      82981
leaf node 90-10 splits                                                   18
branch node splits                                                      363
root node splits                                                          0

1) I am using an SSD here, that is why my demo took much less time, because of the much lower disk response time. You should look at the amount of current blocks, not the time spent on this.

2) I am inserting data in randomized order, this causes leaf block splits. leaf node 90-10 splits are caused by inserting data to the rightmost part of the index, in a sequentially increasing order. leaf node 90-10 splits can be simply done by adding new index blocks to the "rightmost" part of the index, then copy the highest value into them. Sometimes it is unofficially referred as a 99-1 split instead of 90-10. leaf block splits means adding new blocks in the "middle" of the index, copying and rearranging data in several blocks, this is much more work.

3) Performance-wise I find it very a bad idea to use GUIDs as identifiers. They consume multiple times more storage space compared to simple NUMBER identifiers.

This is the same, but with NUMBER types:

EGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
HOUSE_T2                                  2432
HOUSE_T_IDX02                             1088
HOUSE_T_IDX12                             1088
HOUSE_T_IDX22                             1088

Also: Limitations of the Oracle Cost Based Optimizer (Doc ID 212809.1)

  Limitations of Histogram on Character Columns

  Histograms only store the first 32 characters of a character string (5
  characters pre 8.1.6 See Bug:598799 ). If histograms are gathered on
  character columns that have data that is longer than 31 characters and
  the first 31 characters are identical then column histograms may not
  reflect the cardinality of these columns correctly as these values
  will all be treated as if they are identical. There are also similar
  limits with numeric data which is normalised to 15 digits in histogram
  endpoints.

  As of 12c, the maximum possible number of characters considered for a
  histogram on a string column increases from thirty-two to sixty-four.

This has caused some seriously incorrect cardinality misestimates with histograms on GUID columns.

This is how this behaves when values are inserted in an increasing order (removed order by dbms_random.value):

INSERT INTO HOUSE_T (HOUSEID, HOUSEGUID, AOGUID)
VALUES
 (:B1 , :B2 , :B3 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     70     21.13      23.39       3266     167674    2496874     3500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71     21.13      23.39       3266     167674    2496874     3500000

As you can see, it is less than 1 current block per row.


WAL files not being deleted and pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive My database has stopped deleting WAL files and pg_xlog grows to eventually take all my disk space.  I have to use pg_controldata and pg_archivecleanup to delete them manually.

I'm almost certain it's to do with my tinkering with replication, but I thought I removed it, and the only remnant of that I can see is the replication slot, but when I try to remove it: pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive.  Which I find strange because I'm trying to remove it, not use it.

$ barman list-server shows nothing

SHOW wal_level minimal

SHOW archive_command (disabled)

SELECT * FROM pg_replication_slots
barman,,physical,,,false,,,,23/DF000000

SHOW server_version 9.5.14

My postgresql.conf is absent of all WAL settings except max_replication_slots = 1, which I tried to set to 0, but the database will not start if I do.

How can I remove my replication slot, or otherwise troubleshoot postgres not deleting its WAL files?

Set "wal_level" back to "archive", and restart.  Then drop the slot.  Then if you don't want leave it at "archive", set it back to "minimal" again, set max_replication_slots to 0, and restart again.

  Which I find strange because I'm trying to remove it, not use it.

Removing a slot requires access to the slot machinery.  It is a form of "use".


Is there a reliable way to determine how long it took the optimizer to generate a query plan? I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the statistics? Or can I calculate it somehow if I include live statistics or the actual execution plan? Maybe in the query store?

There are many ways :

If you can run the query

you can use set statistics time on which will give you:

  SQL Server parse and compile time:
  CPU time = xx ms, elapsed time = xx ms.

If you cant run the query, then mine the plan cache to check for query compile time along with many other significant insights.

There are many other ways like profiler, server side trace or XEvents - but they are expensive and might have negative perf impact on your sql server.

You can use sp_BlitzCache to find out Query Compilation Timeout from plan cache.


Restore user in DataBase that in master have mapped the child database but in child database doesn't have the user created I don't know why te following database arrive to this point but this is the situation

In master security you can see the defcon\axadmin user with DAXTest database mapped

But if you go to the database secury the user AXAdmin doesn't exist.

I can't edit any information about mapping of AXadmin user because throw the following error:

Also I can't delete this user in master because when I create again have the same mapping.

And if you check with te following sql:

use master
go
select * from syslogins where name='DEFCON\axadmin'
go

use daxtest
go
select * from sysusers where name='DEFCON\axadmin'
go

You can see the user in master database but doesn't exist in DAXTest

I try to create in DAXTest

CREATE USER [defcon\axadmin] FOR LOGIN [defcon\axadmin];

The user have another session start name

But if I try to edit

ALTER USER [DEFCON\axadmin] WITH LOGIN=[DEFCON\axadmin]

You can't edit the user because doesn't exist

Error:

Your login DEFCON\axadmin is sysadmin on the current server.

This means that it cannot be mapped to any database as DEFCON\axadmin user, it's already mapped and it's mapped as dbo in any database.

To see this just impersonate this login and ask for a user in that database:

use DAXTest;
execute as login = 'DEFCON\axadmin';
select user;
-- you'll see 'dbo' here
select select IS_SRVROLEMEMBER('sysadmin');
-- you'll get 1 here
revert;


Natural join output doesn't appear in table I used this query:

select *
into airbnb_parcels
from pcpao_res
natural join AIRBNB_PARCELS;

and got:

  Error: relation "airbnb_parcels" already exits.

In DBeaver I used the following query:

SELECT * FROM pcpao_res
NATURAL JOIN airbnb_parcels;

But the resulting data only appears in the "output" window below the query panel, not the actual table. I've saved the project, committed the query, etc. but it won't update the table.

It's a large dataset so in the screenshots I opened up a filter on the same column in both the query window and the table window to show how values are present in the former but not the latter.

What am I doing wrong?

Let me suggest to read carefully the Postgres docs:

SELECT INTO

SELECT INTO — define a new table from the results of a query

Description

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Notes

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

That is:

SELECT INTO creates a new table, the new table must not exists previously.
INSERT INTO requires an existent table.

Example:

CREATE TABLE t1 (id int, name text);
INSERT INTO t1 VALUES
(1, '1111'),(2, '2222'),(3, '3333');

-- Correct, t2 doesn't exists.

SELECT id, name INTO t2 FROM t1;

SELECT id, name FROM t2;

id | name
-: | :---
 1 | 1111
 2 | 2222
 3 | 3333

CREATE TABLE t3 (id int, name text);

-- Error, t3 already exists
SELECT id, name INTO t3 FROM t1;

  ERROR:  relation "t3" already exists

-- Error, t4 doesn't exists
INSERT INTO t4
SELECT id, name FROM t1;

  ERROR:  relation "t4" does not exist
  LINE 1: INSERT INTO t4
                      ^

-- Correct, t3 already exists
INSERT INTO t3
SELECT id, name FROM t1;

SELECT * FROM t3;

id | name
-: | :---
 1 | 1111
 2 | 2222
 3 | 3333

db<>fiddle here


Is It normal to keep log file larger than data file? Is It normal to keep log file larger than data file?

I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be large.. as i'm using log shipping, I normally take log backups every 10 minutes.

What I'm asking is "Is it normal to see Log file larger than data file as my data file is about 7,216 GB and my log file is about 9,930 GB?" I'm afraid there is a standard ratio between log and data file? I don't want to shrink my log file because I have enough space on my hard disk.

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.
You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage.

I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.


ORA-14400: inserted partition key does not map to any partition I try to create table from ORACLE setup script
After I run the script

SQL> @home/oracle/Desktop/Setup.sql

Following output appears:

Table dropped.
Table created.
Index created.
INSERT INTO tab1
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Commit complete.
PL/SQL procedure successfully completed.

And the table is created but it's empty.

It is an older article and it's assuming the year is 2015.
Change the script so partitions look like this:

PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);

The whole script should look like this:

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id                NUMBER,
  code              VARCHAR2(20),
  description       VARCHAR2(50),
  created_date      DATE,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);

CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;

INSERT INTO tab1
SELECT level,
       CASE
         WHEN MOD(level,2)=0 THEN 'CODE1'
         ELSE 'CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'Description for CODE1'
         ELSE 'Description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)
       END
FROM   dual
CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

This is because of this:

         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)

SYSDATE will be inserted into tab1_part_2017 and ADD_MONTHS(SYSDATE, 12) will be inserted into tab1_part_2018 and you will get equal number of rows in both partitions (50000 in each to be exact).


mysql innodb_flush_log_at_trx_commit clarification I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years)

So moving to a new engine is a little bit of a scary operation.

I am apprehensive about the move, as it has been on myISAM for so long without issue, but if it gives me peace of mind long term, then it is better for the DB.

I have read the whitepapers on InnoDB and it seems fairly straight forward, The one thing that I want to clear in my mind is the innodb_flush_log_at_trx_commit option

By default this is set to 1, however from my reading this causes additional overheads, if the data was credit card transactions or something like that, I can understand its needs to be there, but it seems when dealing with non life changing data, that innodb_flush_log_at_trx_commit=2 is a better option.

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

What I want to know is, this does not affect the time that the query
  is actually committed does it? It only affects its recovery ?

It only affects recovery.

  I just want to make sure that when I do an insert or update that the
  query will run right at the time of processing and not 1 second later,
  no matter what the flush_log is set to.

Correct.  There may be reasons to hide changes that other transactions see (as part of multi-version concurrency control).  If you're trying to build tests to prove things - make sure you understand how transaction-isolation levels work :)

  My understanding is that in the case of a crash , setting to 1 will
  allow it to recover all queries run on the server, where as setting it
  to 2 may lose the last second or two of data when trying to recover
  from the crash, is this correct?

Correct.  To be able to recover all (setting: 1) ti also assumes that the underlying hardware is not adding additional buffering to improve IO performance.  I have no reason to assume that Amazon doesn't do this, so I think setting it to 2 is a good cloud practice.

  Also, if there is slow periods of updates/inserts (ie, not much
  happening on the server) does setting it to 2 add additional overheads
  on the server, or is it a case of the benefits of applying
  innodb_flush_log_at_trx_commit=2 to the database when the database is
  busy outweighs any additional overhead caused during slow times?

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance.  It doesn't create more work.

Shameless plug for additional reading - When does MySQL perform IO?


PostgreSQL's record type returning null? I don't understand why this result is possible. Can someone explain why the first assert returns true and the second false? generate_token() is a function that generates a random bytea and inserts it into a certain table, then the inserted row is returned. The function itself is working fine. Running through a .pgsql script.
DO $$
DECLARE
    test record;
BEGIN

select * into test from generate_token('test');

--returns true
assert test.token IS NOT NULL;
--returns false, which breaks.
assert test IS NOT NULL;
END;$$;

... then the inserted row is returned.

It's safe to assume there are multiple fields in that returned row. If at least one additional field is NULL, then the row value is neither NULL nor NOT NULL. The manual:

If the expression is row-valued, then IS NULL is true when
the row expression itself is null or when all the row's fields are
null, while IS NOT NULL is true when the row expression itself is
non-null and all the row's fields are non-null. Because of this
behavior, IS NULL and IS NOT NULL do not always return inverse
results for row-valued expressions; in particular, a row-valued
expression that contains both null and non-null fields will return
false for both tests.

Bold emphasis mine.
db<>fiddle here
Related:

NOT NULL constraint over a set of columns
(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL


create trigger which inserts rows depending on enum values? I'm quite new to triggers and I ran into general problems. I'd like to achieve this:

CREATE TABLE `searcharticles` (
 `articleID` int(11) unsigned NOT NULL,
 `ean` char(13) COLLATE utf8_unicode_ci DEFAULT NULL,
 `manufacturerNumber` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`articleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `addresses` (
 `articleID` int(11) unsigned NOT NULL,
 `shop` enum('shop1','shop2') COLLATE utf8_unicode_ci NOT NULL,
 `url` varchar(255) COLLATE utf8_unicode_ci NULL,
 `status` tinyint(3) NOT NULL DEFAULT '0',
 `createTime` datetime NOT NULL,
 `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`articleID`,`shop`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

When a new row is inserted in searcharticles, the table addresses should be filled, but not only once. I'd like to make a record for every shop that is in enum shop (I know this is not well designed). I'm stuck at this:

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN
  INSERT INTO addresses(articleID, shop, createtime) VALUES(NEW.articleID, ?, CURRENT_TIMESTAMP);
END

How could I iterate through the possible enum values to trigger an insert with 'shop1' and another with 'shop2' without writing a possible shop name in the insert?

It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS.

DELIMITER $$

DROP TRIGGER ins_addresses $$

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN

DECLARE column_list char(200);
DECLARE current_value char(200);
DECLARE counter INT DEFAULT 0;
DECLARE num_enums INT;

  /* Format goes from "enum('shop1','shop2')" to "shop1,shop2" */

  SELECT REPLACE(SUBSTR(COLUMN_TYPE,6,LENGTH(COLUMN_TYPE)-6),'''','')
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'stackex'
  AND TABLE_NAME = 'addresses'
  AND COLUMN_NAME = 'shop'
  INTO column_list;

  SET num_enums = LENGTH(column_list)- LENGTH(REPLACE(column_list, ',', ''))+1;

  WHILE counter < num_enums DO
    SET current_value = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(column_list, ',', counter+1),
          LENGTH(SUBSTRING_INDEX(column_list, ',', counter )) + 1), ',', ''));

    INSERT INTO addresses(articleID, shop, createtime)
    VALUES (NEW.articleID, current_value, CURRENT_TIMESTAMP);

    SET counter = counter + 1;
  END WHILE;
END$$
DELIMITER ;

Test:

mysql> use stackex;
Database changed
mysql> insert into searcharticles values ( 1, 'foo','bar' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from addresses;
+-----------+-------+------+--------+---------------------+------------+
| articleID | shop  | url  | status | createTime          | updateTime |
+-----------+-------+------+--------+---------------------+------------+
|         1 | shop1 | NULL |      0 | 2012-08-17 09:32:33 |       NULL |
|         1 | shop2 | NULL |      0 | 2012-08-17 09:32:33 |       NULL |
+-----------+-------+------+--------+---------------------+------------+
2 rows in set (0.00 sec)

mysql>

I actually hate myself for answering this! Change the data model to use a lookup table, shops.

It'll break horribly if the shop enum names have commas or quotation marks in them (probably).


Accesing a nested table column If I have for example this table:

CREATE TABLE table1 OF TYPE type1
(
    PRIMARY KEY (col1)
);

And a second table that containts the nested table:

CREATE TABLE table2
(
    Col2 table1
);

How do I access col1 of nested table col2?

That's not a "nested table" (at least not in the sense as e.g. Oracle uses that term). The column still only contains a "single value" (which is a record though), not multiple rows.

The second table contains a single column with the type table1 which is a composite type (a.k.a. "record").

To access the fields of the composite type, you need to enclose the column name in parentheses:

select *
from table2
where (col2).id = 42;

Assuming that the type type1 has a field named id


Reducing Memory Pressure When Performing Parallel Staging Table Loads I'm attempting to migrate several very large tables in my data warehouse to new partition scheme (the older partition scheme was based on a RANGE LEFT function, that wasn't regularly split, and the most recent data is top-heavy in the last filegroup). The tables are sufficiently large (5 billion rows, 400 GB data space), that I'm leery to try a clustered index rebuild for fear of insufficient logging space.

I'm attempting to load the new table by running several INSERT...(WITH TABLOCK) SELECT...WHERE <check constraint predicate> (OPTION MAXDOP 4) statements into identical staging tables, one per filegroup in the scheme which I'll later switch into the replacement table. I coordinate execution of this faux bulk load by individual SSA jobs to take advantage of read-ahead/merry-go-round.

All works pretty well on the relatively small tables will all jobs running simultaneously. However, on the first large table, most of the SSA jobs have RESOURCE_SEMAPHORE waits while the job with the first partition is chugging along.

If my server has 768 GB RAM, 24 cores (hyperthreaded) and it's working on a 400 GB table, should I adjust my MAXDOP downward to free up more grants or enable resource governor to reduce the maximum grant to get more SSA jobs to kick in and work in parallel?

I initially tried the BULK_LOGGED recovery model, but went to SIMPLE. Transaction size is still huge; I generated over 1 TB of log copying 15 GB (must be due to the page splits if the data set wasn't sorted, I'm using page compression, and I was running other than MAXDOP 1).

I'm working on a FTDW rig (local SSD array running about 5 GB/s throughput, PureStorage array peaking at 11 GB/s) so hopefully disk latency won't be an issue. I'm writing the SSA jobs with SMO.

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
    Oct 28 2016 18:17:30
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit)
      on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

If you take a look at my answer over here, I mention a new(ish) query hint called MAX_GRANT_PERCENT that you can use to further control memory grants at the query level.

Documentation is here.

Assuming queries are in the default pool and you haven't altered it, right now they can ask for 25% of your 768 GB of RAM. That's a lot of memory.

You'll have to play with reducing the percentage until you find a balance between concurrency and not spilling to disk-ency.

Hope this helps!


Simple Syntax Error - CASE WHEN DISTINCT(Column) Trying to do a case on a distinct value and it isn't going well:

SELECT
CASE WHEN DISTINCT(Response_Master_Incident.Battalion) = '' THEN 'Unknown'
    ELSE DISTINCT(Response_Master_Incident.Battalion) END AS 'Zone'
...

and I get the error "Incorrect syntax near the keyword 'DISTINCT'."

Answer looks like this.  Thanks Randolph!

SELECT DISTINCT
    CASE WHEN Response_Master_Incident.Battalion = '' THEN 'Unknown'
    ELSE Response_Master_Incident.Battalion END AS 'Zone'


SQL Server transaction log Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the database is backed up?

Scenario:

Auto backup job in SQL Server takes a backup of database XYZ and also of it's transaction log. When this backup has completed SQL Server will reset the transaction log (log file size will be increased to starting point)

Thank you.

Quoting Paul Randal here:

  In the FULL or BULK_LOGGED recovery models, the only thing that
  clears the log is a log backup.

http://www.sqlskills.com/blogs/paul/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx


How to avoid warning with file system cache in mongodb? OS Windows 10 x64. Version of MongoDB 3.6.5

When I'm starting mongo process, I'm always getting:

MongoDB shell version v3.6.5 connecting to: mongodb://127.0.0.1:27017/
MongoDB server version: 3.6.5 Server has startup warnings: 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten] 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten] ** WARNING: The file system cache of this machine is configured to be greater than 40% of the total memory. This can lead to increased memory pressure and poor performance. 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten] See http://dochub.mongodb.org/core/wt-windows-system file-cache 2018-07-02T20:56:09.470+0300 I CONTROL  [initandlisten]

Probably, same issue was here, but there isn't normal solution. I suppose that I need to add something in config file, for example - https://docs.mongodb.com/manual/reference/configuration-options/#storage-inmemory-options.

Now I have in config:

systemLog:
    destination: file
    logAppend: true
    path: c:\data\log\mongod.log
storage:
    dbPath: c:\data\db
    journal:
        enabled: true
replication:
   replSetName: "rs0"
net:
   bindIp: 127.0.0.1
   port: 27017
security:
     authorization: enabled

If I'm trying to add snippet:

inMemory:
      engineConfig:
         inMemorySizeGB: 10

The whole config looks like:

systemLog:
    destination: file
    logAppend: true
    path: c:\data\log\mongod.log
storage:
    dbPath: c:\data\db
    inMemory:
        engineConfig:
                inMemorySizeGB: 10
    journal:
        enabled: true
replication:
   replSetName: "rs0"
net:
   bindIp: 127.0.0.1
   port: 27017
security:
     authorization: enabled

Then getting the error Unrecognized option: storage.inMemory.engineConfig.inMemorySizeGB

P.S. I didn't use tab.

Any ideas how to avoid warning? Thanks.

Any ideas how to avoid warning?

This warning was added for some older implementations of MongoDB's WiredTiger storage engine, but is longer relevant in MongoDB 3.6. The warning has been removed in MongoDB 4.0 and the removal has been backported for the upcoming MongoDB 3.6.6 server release. See SERVER-33706 for more background.

You can ignore this warning until you upgrade to a version of MongoDB newer than 3.6.5.

  Then getting the error Unrecognized option: storage.inMemory.engineConfig.inMemorySizeGB

This configuration option is for the In-Memory Storage Engine which is included in MongoDB Enterprise, and not relevant to the filesystem cache warning. The option will not be recognized unless you are running MongoDB Enterprise server.


Why does Recursive CTE estimate just 1 row? Given two cascading, self-contained (no real tables) recursive CTE's:

create view NumberSequence_0_100_View
as
with NumberSequence as
(
    select 0 as Number
    union all
    select Number + 1
      from NumberSequence
     where Number < 100
)
select Number
  from NumberSequence;
go

create view NumberSequence_0_10000_View
as
select top 10001
       v100.Number * 100 + v1.Number as Number
  from Common.NumberSequence_0_100_View v100
 cross join Common.NumberSequence_0_100_View v1
 where v1.Number < 100
   and v100.Number * 100 + v1.Number <= 10000
    -- please resist complaining about "order by in view" for this question
 order by v100.Number * 100 + v1.Number
go

Then generate estimate/actual plans for:

select * from NumberSequence_0_10000_View

Estimate

Actual

Runtime 23ms but estimating just one row for final output (2 rows for just the first view).

The problem is that when this is used as a subquery to join with real data (by "DaysAgo" for instance), the plan is usually a very slow nested loop and I often need to add a join hint/reverse order etc.

Is there anyway to improve the estimate while keeping the CTE approach?  Has there ever been a request for a "with (AssumeMinRows=N)" hint?  That seems like a great general purpose helper for many cases (not just CTEs).

Why does Recursive CTE estimate just 1 row?

Cardinality estimation for recursive common table expressions is extremely limited.

Under the original cardinality estimation model, the estimate is a simple sum of the cardinality estimates for the anchor and recursive parts. This is equivalent to assuming the recursive part is executed exactly once.

In SQL Server 2014, with the new cardinality estimation model enabled, the logic is slightly changed to assume three executions of the recursive part, with the same number of rows returned by each iteration.

Both of these are uneducated guesses, so it is no surprise that the use of recursive CTes usually results in poor quality estimations. More generally, estimating the result of a recursive process is nigh-on impossible, so the optimizer doesn't even try. This is not changed by using a particularly simple recursive structure, clearly intended to produce sequential numbers - the optimizer has no logic to detect this pattern.

In your particular case, the final estimation is one because the optimizer makes further guesses about the selectivity of predicates like [Recr1007]<(100) (in the Filter at Node ID 3) and ([Recr1003]*(100)+[Recr1007])<=(10000) (the residual predicate on the Nested Loops Join at Node ID 2). Again, these are guesses, and the results are unfortunate, though not surprising.

  Is there anyway to improve the estimate while keeping the CTE approach?

Not that I am aware of.

  Has there ever been a request for a "with (AssumeMinRows=N)" hint?

Not directly in those terms. There have been plenty of requests for materializing a CTE, which would help if such materialization came with automatic statistics generation. I won't list the others because you seem to have commented on most of those suggestions already :)

There have also been suggestions for selectivity hints, but nothing like this has made its way into the product yet.

As noted in comments on the question, your best bet right now is to use a real numbers table instead of generating one on-the-fly using a recursive CTE. A second option is to use manual materialization - a temporary table - as I am sure you are aware.


Table statistic vs estimated rows query plan Can anyone explain why despite having inaccurate table statistics, the actual query plan "estimated number of rows" is correct? E.g table has 30 rows but all table statistics show 50 rows. However a query on the table produces a plan that apparently correctly estimates 30 rows. How did it know there are actually 30 rows?

CREATE TABLE dbo.TestPerf
  (
     ID   INT IDENTITY(1, 1),
     Col1 VARCHAR(100)
  )
GO

INSERT TestPerf
SELECT 'badger'

GO 1000

CREATE STATISTICS stat_TestPerf_Col1 ON dbo.TestPerf (Col1) WITH FULLSCAN;

--lose some rows
DELETE dbo.TestPerf
WHERE  ID > 750 --Check the stat not updated - still shows 1000 rows
DBCC SHOW_STATISTICS (N'dbo.TestPerf', stat_TestPerf_Col1) --now run a query and see the estimatedRows
SET statistics profile ON

SELECT *
FROM   dbo.TestPerf

SET statistics profile OFF --estimatedRows = 750

You are checking the statistics for changes in the wrong place in your code. They won't be auto updated immediately after the delete. This will happen when a query is executed or compiled that needs them (and in fact your code contains no query that needs them anyway apart from the delete itself).

But SQL Server does maintain cardinality information for the partitions of the table/indexes as a whole after every insert/delete etc.

You can see this with the below (only returns 1 row as the table is not partitioned and has no indexes)

SELECT row_count
FROM   sys.dm_db_partition_stats
WHERE  object_id = object_id('dbo.TestPerf');

This shows it that the current row count is 750. The query

SELECT *
FROM   dbo.TestPerf

Doesn't benefit from column statistics. It can just use that directly and even if statistics are disabled will be accurate.

A query with WHERE  ID > 0 would use the statistics and return the whole table. As it happens the statistics don't get updated in your case anyway when such a query is executed as you haven't hit the threshold for modifications for an auto update to occur. But the estimates are still accurate.

The statistics records that there were 1,000 rows at sampling time.

So it would be simple to just scale the estimates down to adjust for the new table cardinality by multiplying by 0.75.

The scaling down works fine for WHERE Id>0 as 1,000 * 0.75 gives the correct result but if you try querying the 250 rows that you just deleted this will not show correct estimates.

The statistics indicate that it will return 250 rows, multiplying that by 0.75 gives 187.5.


Slow query / Indexes creation (PostgreSQL 9.2) I have the following Query:

explain analyze
SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE
      i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0;
  split_part(s.full_path, '/', 4)::INT IN (

SELECT account.id
        FROM public.ja_clients AS account
        WHERE
        NOT (
                ((account.last_sub_pay > EXTRACT('epoch' FROM (transaction_timestamp() - CAST('4 Months' AS INTERVAL)))) AND (account.price_model > 0)) OR
                (account.regdate > EXTRACT('epoch' FROM (transaction_timestamp() - CAST('3 Month' AS INTERVAL)))) OR
                (((account.price_model = 0) AND (account.jobcredits > 0)) AND (account.last_login > EXTRACT('epoch' FROM (transaction_timestamp() - CAST('4 Month' AS INTERVAL)))))
        ) LIMIT 100
);

Explain analyze link: http://explain.depesz.com/s/Oc6

The query is taking ages, and I can't get the problem solved.

These are the index I've already created on the inode_segments table:

Indexes:
    "ix_account_id_from_full_path" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "full_path"::"text" ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'::"text"
    "ix_inode_segments_ja_files_lookup" "btree" ((
CASE
    WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text"))
    ELSE NULL::"text"
END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
    "ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
    "ix_inode_segments_notes_clientids2" "btree" ("full_path")
    "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
    "ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")

These are the index I've already created on the inodes table:

 Indexes:
    "ix_inodes_checksum_st_size" "btree" ("checksum_md5", "st_size") WHERE "checksum_md5" IS NOT NULL

Question:

What else can I do to improve the Performance of the Query?

This is related to my previous question; index creation - Slow Query - PostgreSQL 9.2

UPDATE 1:

Explain analyze: http://explain.depesz.com/s/UBr

The index and function have been created as mentioned on the answer below.

UPDATE 2:

Explain analyze: http://explain.depesz.com/s/LHS

Using the Query provided on the answer below

Perhaps this will help.

If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it:

CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$
SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int
$body$ LANGUAGE SQL IMMUTABLE SECURITY DEFINER RETURNS NULL ON NULL INPUT;

CREATE INDEX ON gorfs.inode_segments (gorfs.f_get_account_from_full_path(full_path));

Ensure gorfs.inodes has an index (or key much better if applicable) on st_ino!

You run the function split_part several times for each row, this is likely taking a significant toll. I've replaced it with string_to_array, and then fetch the individual pieces as needed. I also didn't understand what you intended to obtain for the field field_name using reverse? The query below returns the last element for it.

Your query returns many million rows. Even if PostgreSQL processes the query reasonably quickly, your client application (especially if you use PgAdminIII) will struggle allocating enough memory and receive and format the results, and probably be what takes the most time. So you may want to create a temporary table with the results, and then query against the temporary table:

CREATE TEMP TABLE myresults AS
WITH
  accounts AS (
    SELECT id
    FROM public.ja_clients
    WHERE NOT (
               (last_sub_pay > EXTRACT('epoch' FROM now() - '4 Months'::INTERVAL) AND price_model > 0) OR
               regdate > EXTRACT('epoch' FROM now() - '3 Month'::INTERVAL) OR
               (price_model = 0 AND jobcredits > 0 AND last_login > EXTRACT('epoch' FROM now() - '4 Month'::INTERVAL))
              )
    ORDER BY 1 LIMIT 100 -- first 100 accounts for testing purposes; comment out this line once the query is proven performant enough
    )
SELECT r.parts[4]::INT AS account_id, r.parts[6]::INT AS note_id, r.parts[9] AS variation,
       st_size, segment_index, r.parts[array_upper(r.parts, 1)] AS file_name, st_ino, full_path, size_mb
FROM (
  SELECT string_to_array(full_path, '/') AS parts, st_size, segment_index, i.st_ino, full_path,
         (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
  FROM gorfs.inode_segments s
  JOIN gorfs.inodes i ON (i.st_ino = s.st_ino_target)
  WHERE gorfs.f_get_account_from_full_path(s.full_path) IN (SELECT * FROM accounts)
    AND i.checksum_md5 IS NOT NULL
    AND i.st_size > 0
  ) r;

SELECT *
FROM myresults
LIMIT 100;


Rows already DELETEd on MySQL slave; How to DELETE on master? I have a handful of rows that exist in my MySQL master but not in my slave.
Replication is running ok and these rows are unlikely to ever be referenced as it is a store-and-forget type of table. However, I want to delete these orphaned rows in order to have everything in sync and prevent any future problems.
If I issue a DELETE statement on the master to remove the offending rows, the slave obviously won't be able to replicate it as those rows don't exist at the slave.
What are the implications of doing this?

Will this break replication or will the slave simply ignore the problem and carry on?
What is the recommended way to resolve this?

Background
A DELETE statement was interrupted on the master. Replication stopped as a result. I believe the same DELETE statement was then executed on the slave before restarting replication.  However, it ran to completion on the slave, resulting in more rows being deleted there.

Here is something quick and dirty you can do

Run the DELETE like this on the Master:

SET SQL_LOG_BIN=0;
DELETE FROM ... ;

The first line tells the DB Session not to record the SQL that follows. Thus, when you run the DELETE on the Master, the SQL will not be written in the Master's binary logs. Consequently, the Slave will never receive the DELETE in its relay logs.

This will only affect the session you use to run these two lines. All other DB Connections will replicate as usual. Once you disconnect, any new session will replicate properly.

Give it a Try !!!


AWS ORACLE Linux 7.4 - Connection issue I tried to install oracle 12c in Oracle Linux 7.4 instance upon AWS ORACLE Linux 64 EC2 instance,
I could make the installation and connect to the database from the ec2 server without any issue however when I try connecting to the database from my desktop I get generic TNS Protocol adapter error.

Please find more information about my instance:
1. I have set following rule for my EC2 instance
instance rule

Please note,  I am not sure why i am getting every roles twice also I am not sure why type is 'Oracle -RDS', i always check the type as 'Custom TCP'

hostname value from my unix

ip-xx-x-x-xxx.ap-south-1.compute.internal

tnsnames.ora

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT = 1521)) (CONNECT_DATA
= (SERVER = DEDICATED) (SERVICE_NAME = orcl.ap-south-1.compute.internal) )
)

listener.ora

LISTENER =   (DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)   )

lsnrctl status

eLSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2018 13:53:11'
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT=1521)))
STATUS of the LISTENER
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                30-JAN-2018 13:25:18
Uptime                    0 days 0 hr. 27 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ip-10-0-0-253/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ip-xx-x-x-xxx.ap-south-1.compute.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.ap-south-1.compute.internal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.ap-south-1.compute.internal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.ap-south-1.compute.internal" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...

Value from v$instance table

  SQL> select host_name, instance_name from v$instance;

  HOST_NAME
  ---------------------------------------------------------------- INSTANCE_NAME
  ---------------- ip-xx-x-x-xxx.ap-south-1.compute.internal orcl

I am not sure why I cannot connect from outside of ec2  instance; Please help me out of this issue

Did you check firewall enabled or not on your Linux machine? If it is enabled then you should need to add your port 1521 for accepting requests just like the following command.

sudo iptables -I INPUT -p tcp --dport 1521 -j ACCEPT


Oracle data transfer rate determined by rows instead of data size Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db?

Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL Developer. I ran some tests and found that as the row number increases, the time to transfer increases in a linear fashion (100K rows in 90 seconds, 1M rows in 903 seconds). I then ran another test where the row count remained the same (500K) but the amount of data in each row increased with each trial. For this test, the time was almost flat (~480s), even though the amount of data increased from 5MB to 45MB over 10 trials.

The issue does not seem to be a throughput (VPN tunnel) problem since data speed increased with data/row.

So, my question is, what in Oracle would be causing this consistent 1000 rows/sec value, regardless of the amount of data in each row?

We don't manage the Oracle db, but I'm hoping to go back to the dbas with some ideas to speed up our month end loads.

Test 1: Increase Row Count, Maintain Data/Row

Test 2: Maintain Row Count, Increase Data Per Row

More likely limited by the fetch size, which is a client side setting.

For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/

Quoting some content of the blog post:

  Batch size controls how many rows SSIS retrieves with each “batch”.
  After retrieving all the rows in a batch, SSIS signals to Oracle that
  it is ready for another batch.   Of course, network latency is
  introduced during this signaling process.  Thus, decreasing the number
  of total batches also decreases the number of round-trips SSIS has to
  make.  The default batch size is only 100;  increasing this to a
  reasonable number should boost performance.

And:

https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/

  If you are experiencing the same or similar issue, then you may wish
  to experiment with different settings for the FetchSize parameter on
  the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
  ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
  means that the Oracle OLE DB provider will request and fetch 100 rows
  of data. SQL Server will then read 100 rows of data. Once all 100 rows
  have been read, the Oracle provider will request another 100 rows of
  data. This will continue untill all rows are read.


What causes "lock request time out period exceeded" when droping a column? I created some columns in our production database but now I need to drop one.  However this seems impossible and I do not know why.  I dropped the column in our development database just fine.  I created a table in production and dropped that.  But when I try to remove this one column I get the error.  I used management studio (which fails quickly) and T-SQL (which takes longer but still fails).

ALTER TABLE VEN DROP COLUMN RI_VEN_Approved

Any idea whats going on?

I checked for locks using a query found here: https://stackoverflow.com/questions/1511675/how-to-check-if-a-table-is-locked-in-sql-server

Using this query specifically:

SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description, o.object_id, o.name, o.type_desc
FROM sys.dm_tran_locks l, sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
and resource_database_id = DB_ID()

The table did not appear in the results.

I ran sp_who2 and did not see anything in the BlkBy column.

Drop the table using T-SQL as then the query will not timeout but instead get blocked. Note down the spid of this query window (it should be visible in the bottom of SSMS, right next to username). Once this goes into "executing" stage, open another query window and do this,

use master
go
select * from sysprocesses where spid = <spid of the query which is dropping the table\column>

Look at the blocked column and note down the spid number. Next query to see who the owner of the spid is (this will tell you who owns the conflicting lock on this table).

select * from sysprocesses where spid = <spid # from above blocked column>

Once you identify this, you can use dbcc inputbuffer(spid) to find out what query/command it is doing that has taken a lock on this table. If you deem that query inconsequential, then you can kill it using kill <spidnumber>. After this your query for the drop table/Column should complete.

Updated: Try to drop column  ALTER TABLE table_name DROP COLUMN column_name;

Check this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/77fc7e09-ed14-481f-89d5-d5e143dab7bd/trying-to-delete-a-table-in-sql-server-management-studio-and-getting-a-strange-error?forum=sqldatabaseengine


Postgres 9.4 won't accept remote connections I'm running postgres 9.4 on Ubuntu server 14 which is running on virtualbox.  After a reboot no remote connections would work to postgres.

I have other services on server that still take remote connections (Rstudio server, Shiny server, SSH) and that machine can still connect to the internet, and to samba shares.

Here's my pg_hba.conf

    local   all             postgres                                peer
    host    all           scraper         localhost             trust
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            md5
    host    all             all             ::1/128                 md5
    hostssl all             all             0.0.0.0/0               md5

In trying to connect with pgadmin from another machine, I get this message

An error has occurred:

03:23:06 PM: Error: Error connecting to the server.
FATAL: password authentication failed for user "postgres"

It fails if I use R, psql, or windows odbc connections so it doesn't appear to have anything to do with pgadmin

If I ssh in then I can run psql locally...

If I run \du I find that all users show Password valid until infinity

EDIT

I forgot to say that I am indeed using the correct password.  I can be sure I'm using the right password because I when I was in psql I reset the password of the users in question using ALTER USER user WITH PASSWORD 'MYPASSWORD';

With your edit, you are probably running something like this, which will default to the user postgres:

psql -h 123.123.123 -d mydb

when you should be using the user flag:

psql -h 123.123.123 -d mydb -U whoever

you will be prompted for the password for user whoever.


MySQL: Is there a way to update a field so that all foreign key related fields are updated as well? I'm assuming the answer to this question is yes but it is hard to Google this type of question and not get overly ambiguous results.

I am working with a database that was dumped from a Magento instance - EAV architecture. It stores orders with the column entity_id and uses this field throughout the DB, something close to 20 tables by my count. Magento uses foreign keys on this entity_id column to keep track of its relationship to other tables.

My Question: Is there any way to run an update query so that the MySQL DB will automatically update every foreign key related column in the database? I need to "shift" about 1000 of these incremental entity IDs in order to successful merge orders from another Magento instance. Thank you.

Example of update: Change entity_id from 9567 to 10567.

It is not possible to update the multiple tables interlinked with foreign keys. The constraints in MySQL only updates the related column.

Hope this helps!


Why do we have Top N Sort in this example? In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?
use tempdb;
go

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
(v)
select top (1000)
row_number() over (order by 1/0)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
(v)
select top (10000)
row_number() over (order by 1/0) + 10000
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

select
sum(a.v + b.v)
from
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;
go

drop table dbo.t1, dbo.t2;
go

It's reproducible in all versions starting from 2008 R2 (I just have no server with earlier versions to test)

It's a somewhat annoying limitation of the current optimizer.

I wrote about this a little as part of The Eager Index Spool and The Optimizer:

  Index spools do not tell the optimizer they support output ordered by the spool's index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.


11g dataguard broken after primary ran out of space I have Data Guard setup with Primary (A) and read-only standby (B).

primary server (A) ran out of space on the data mount point as auto extend was turned on. I managed to revive it by adding additional files to tablespace on another mount point (there were existing data files on here already, looks like it was added at some point as a stop-gap to give db more space). There are matching mount points on (B) with some other data files there already. The StandbyFileManagement is set to auto.

The standby was erroring with ORA-16766: Redo Apply is stopped.
I ran the following on the standby:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

Got the following error:

ORA-10458: standby database requires recovery;
ORA-01157: cannot identify/lock data file 71 - see DBWR trace file
ORA-01111: name for data file 71 is unknown - rename to correct file
ORA-01110: data file 71: '/u00/oracle/product/11.2.0/db_2/dbs/UNNAMED00071'

Could someone suggest what the best way to recover from this would be? is it easier just to recover by re-initalizing Data Guard? how would I do that?

Why would you re-initalize everything just because 1 datafile is missing?

On the primary database, create a backup of the datafile:

rman target /
backup datafile 71 format '/tmp/whatever.bkp';

Copy the backup to the standby, then use it:

rman target /
catalog start with '/tmp/whatever.bkp';
run
{
  set newname for datafile 71 to '/path/to/datafiles/whatever.dbf';
  restore datafile 71;
  switch datafile 71 to copy;
}

Then try again.


Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' which is also just comprised of a primary key and a Toy's foreign key.

I'm required to use a cursor to loop through all held_toys, inserting Toys which are not yet on sale into the toys_on_sale table, and deleting any Toys from held_toys which are already on sale.

While debugging my cursor, I noticed that the LOOP will only iterate once unless the Toys in held_toys are found (by Toy ID) in the toys_on_sale table. Thus the issue must come down to having the following SELECT statement within my LOOP:

DROP PROCEDURE IF EXISTS sp_Toys_On_Sale
$$
CREATE PROCEDURE `sp_Toys_On_Sale`(inToyIds text)
begin

DECLARE finished INTEGER DEFAULT 0;
DECLARE heldId BIGINT;
DECLARE heldToyId BIGINT;
DECLARE existingSaleId BIGINT;

DECLARE curHeldToys
    CURSOR FOR
        select ht.ID, ht.TOY_ID
        from held_toys as ht
        where find_in_set(ht.TOY_ID,inToyIds);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN curHeldToys;

    getHeldToys: LOOP

        SET existingSaleId = 0;

        FETCH curHeldToys INTO heldId, heldToyId;
        IF finished = 1 THEN
            LEAVE getHeldToys;
        END IF;

        #LOOP only runs once if heldToyId is not found (even though there are 500 records in held_toy), I want the loop to continue regardless if the select below returns no results
        select
            tos.ID
        from toys_on_sale tos
        where tos.TOY_ID = heldToyId INTO existingSaleId;

        IF existingSaleId > 0 THEN
            DELETE FROM held_toys WHERE ID = heldId;

        ELSE
            INSERT INTO toys_on_sale (TOY_ID) VALUES (heldToyId);

        END IF;

        DELETE FROM held_toys WHERE ID = heldId;

    END LOOP getHeldToys;
    CLOSE curHeldToys;

end
$$

I know the issue is in:

#LOOP only runs once if heldToyId is not found (even though there are 500 records in held_toy), I want the loop to continue regardless if the select below returns no results
        select
            tos.ID
        from toys_on_sale tos
        where tos.TOY_ID = heldToyId INTO existingSaleId;

But I lack the knowledge of how to embed a SELECT such as this into a CURSOR which can be ignored if it returns zero results. Instead of causing the CURSOR to cease looping..

Any help is appreciated!

I did some research and found official guidance on this situation. The issue results from the SELECT INTO query as suspected, this is because it is within the scope of the first BEGIN and END statement which has the following HANDLER declared:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Thus, when the SELECT INTO does not find any rows, finished is set to 1 and this is the condition that causes the loop to cease. Thus, to fix this issue the SELECT INTO must be scoped into its own BEGIN and END statement to allow for another HANDLER to be declared to essentially ignore NOT FOUND conditions. This is what that should look like:

BEGIN

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

         select
            tos.ID
        from toys_on_sale tos
        where tos.TOY_ID = heldToyId INTO existingSaleId;

END;

Now, if the SELECT INTO statement finds no rows it will simple set finished to 0 which does nothing as the loop only terminates if finished is 1. Luckily, our existingSaleId declared variable in the other BEGIN and END statement is still within the scope of this SELECT INTO statement and we can obtain those Ids as expected.