Cannot set character_set_database and character_set_server to utf8mb4 I'm trying to convert my MySQL server and its already existing schemas to the utf8mb4 character set.

Preliminary Notes:

I'm using Xampp (with MySQL 5.6.24). Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\xampp\mysql\my.ini C:\xampp\mysql\my.cnf, and none exists at the moment.
character-set related variables are as follows before creating any option file:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | cp850             |
| character_set_connection | cp850             |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | cp850             |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | cp850_general_ci  |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

Now I convert one schema to utf8mb4 (one ALTER SCHEMA and multiple ALTER TABLEs), everything works fine. Then I create the following C:\xampp\mysql\my.cnf file:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Next step, I stop mysql with xampp control panel, start it again, and go check the character-set related variables:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | latin1             |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | latin1_swedish_ci  |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+

According to the guide I follow, character_set_database and character_set_server should be set to utf8mb4, and collation_database and collation_server to utf8mb4_unicode_ci. What am I missing?

I have already tried to move my my.cnf option to other paths among those read by MySQL (see above), but nothing changes.

OK, I found what caused the problem. If mysqld is installed as a service,  it sets the path to the executable to:

C:\xampp\mysql\bin\mysqld.exe --defaults-file=c:\xampp\mysql\bin\my.ini mysql

(if XAMPP is installed in C:\xampp, of course)

When mysqld is started with the --defaults-file argument, it reads the specified option file only. And as this path doesn't show up in mysql --help's list of options files, I had no idea of its existence.


Filter rows, but only select most recent row for each Id I have some code that is processing a lot of tasks. These tasks are either connected to a user or an account. Each Task go through many statuses -> Queued, Started, Error, Postponed, Queued, Started, Finished ...

Since there will be processed a lot of these Tasks very fast, the goal was to only do writes. As you can see from the comments, the other goal of saving these statuses is to be able to show the Task status in a user interface.

This was solved like so:

-- Used to store processing data for a Task
drop table if exists task;
create table task (
    id uuid,
    data text,
    type text,
    primary key (id)
);

-- Used to find all statuses for a specific task
-- Use case: View one Task i Admin interface
drop table if exists task_status;
create table task_status (
    task_id uuid,
    time_id timeuuid,
    status_enum text,
    status_text text,
    primary key ((task_id), time_id)
) with clustering order by (time_id desc);

-- Used to find Tasks for an Account
-- Use case: Show Account Details > Tasks
drop table if exists account_tasks;
create table account_tasks (
    account_id bigint,
    task_id uuid,
    time_id timeuuid,
    primary key ((account_id), time_id)
) with clustering order by (time_id desc);

-- Used to find Tasks for a User
-- Use case: Show User Details > Tasks
drop table if exists user_tasks;
create table user_tasks (
    user_id bigint,
    task_id uuid,
    time_id timeuuid,
    primary key ((user_id), time_id)
) with clustering order by (time_id desc);

When a Task is created, a row will be written to "task" and depending on which task is it, also to "user_tasks" or "account_tasks".
During processing of the Task multiple rows will be written to "task_status" with different statuses and a user friendly text describing what happened in the step.

I also need to be able to find all the Tasks that currently have a specific status, but also using a filter on type.

I have tried something like this:

-- Used to find tasks with a specific status and type
-- Use case: Admin Task overview showing all tasks
-- select * from task_overview where status_enum = 'Failed' and type = 'ActivateAccount'
drop table if exists task_overview;
create table task_overview (
    status_enum text,
    type text,
    task_id uuid,
    time_id timeuuid,
    primary key ((status_enum, type), time_id)
) with clustering order by (time_id desc);

I would write to this at the same time as "task_status", however when filtering on this table, I am not sure that was actually the last status the Task had. It just shows that the task had this status at some point. I only want the row returned, if for all rows with a specific task_id this is the one with the highest time_id.

I have spend a lot of time thinking of other data structures that could support this only doing writes, but I have not been successful :-(

Said in other words, I need to be able to do a query where I filter on status_enum and type, that only returns the Tasks that currently have that status_enum.

One thing you can do is create another table which will store the current status of every task. So whenever you are updating your task_overview you will also update this new table keeping only the latest status. This will make table task_overview more or less like a log.


SQL Server 2008 - Two FULL backups in one day There is a database backed up by NetBackup once a day (Full backup 6PM to tape + 3/4x log backup per day). Database is in FULL recovery mode.
Vendor of application has remote access and he scheduled his own FULL backup to the file system (at midnight, probably directly in application).

Would be there a problem with POINT IN TIME recovery? Does his backup broke our chain?

In this picture, yellow are vendor's FULL backups to filesystem. Thank you guys

Would be there a problem with POINT IN TIME recovery? Does his backup broke our chain?

No, Full Backup has no affect on transaction log backup chain. You would still be able to restore full backup and all log backups in sequence. So let us say you have following

Full backup 1 taken by you
Log backup 1
log backup 2
Vendor Full backup 1
Log backup 3
Log backup 4
Vendor Full backup 2
Log backup 5
In case of disaster you would be able to restore Full backup 1 and then log backups 1,2,3,4,5 in sequence.

Having said that you can ask vendor to take copy_only full backups just to be on safer side and simplify things

Hope this is what you are asking.


Capture Extended Events Histogram only during time window We have a problematic time window during night on the weekends. I want to set up multiple Extended Events sessions to capture some data and get insight.

Most of them will target a file, which I can read later, but my problem is with Histogram.

I can configure a job to alter XE session to start at specific time, but I can't stop the session, because it would delete the Histogram data from memory.

AFAIK I cannot save the Histogram results from memory to disk, while it's running.

Maybe there is a way to only capture data in histogram if I set up a timestamp predicate on the actual Event (haven't tested this) and let the XE session run during the weekend and read the results on Monday.

Is there any other way how to capture the Histogram result only during a specific time window?

So I've solved it.

I created a table with xml column and I'll query the sys.dm_xe_session_target directly with a scheduled job right before stoping the session and save the xml result to my table (which will be persisted).


select  ara.ApprovalStatus
    from  `approval_request_approvers` as ara
    where  ara.ApprovalRequestID = rt.ApprovalRequestID
      and  ara.IsDeleted = 0
    order by  ara.ModifiedDate desc
    limit  1

is executed in 7 places.  See if you can make it only once.

And it could use

INDEX(IsDeleted, ApprovalRequestID,  -- in either order for WHERE
      ModifiedDate,                  -- 3rd; for ORDER BY
      ApprovalStatus)                -- for "covering"

You have some LEFT JOINs.  Could the be changed to JOIN?

Change from

or
            (
                select count(*)
                from ...
            ) > 0

to

or EXISTS ( SELECT * from ... )

For this:

p_createdBy is null or p_createdBy = ''

Consider deciding on NULL or '', then sticking with it.  This gets rid of one OR.

Any place where you use OR, see if you can reformulate the query.

Testing against NULL always fails in inequalities, so

(p_createdDateFrom is not null and p_createdDateFrom <= t.CreatedDate)

-->

p_createdDateFrom <= t.CreatedDate

The main WHERE has at least 11 tests AND'd together.  Are all of them TRUE most of the time?  If not, then rearrange them so that the most likely to fail are first.

Rather than passing in NULL/blank for things you don't want tested, have your client software construct the query with just the things you do want tested.  Then we could discuss other options for indexes.

Please provide EXPLAIN SELECT ... to discuss further.


How to return decimals in the right number format with Oracle JSON_VALUE The decimal separator in the JSON format is the period (.). However, when the number format of a system uses comma as a decimal separator (e.g. 10,3 instead of 10.3), the Oracle json_value expects that in order to return a valid number.

Example:

SELECT
json_value(METADATA, '$.Amount' RETURNING NUMBER(18,6)) As AMOUNT
FROM TABLE

This will return (null) for any JSON value that contains a decimal. I cannot save strict JSON with comma decimals. How can I then get a number back from valid JSON if Oracle cannot handle that conversion? Is there some keyword I can use in my RETURNING statement?

Note1: I have used to_number(Replace(...)) but that feels wrong and slows down some queries.

Note2: I do not want to change the number format in my session, since I want to parse the JSON and use it in a view.

You can use TO_NUMBER() without a REPLACE(). For example:

select to_number('12345,67', '9999999999D9999', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER() documentation.


Multiple left join where not in another table I have the following tables (there is more detailed data in each one, I just put the minimum to create a sample):

__________  _____________  _____________  _____________
| person |  | provider  |  | client    |  | user      |
| id     |  | id        |  | id        |  | id        |
| name   |  | person_id |  | person_id |  | person_id |
----------  | name      |  | name      |  | name      |
            -------------  -------------  -------------

I'm trying to create a list where I got all the providers together with all clients but not the users (only the ones that are providers and/or clients).

I started with this query:

SELECT person.id, person.name,
IF(client.id IS NOT NULL, 'Cliente', '') as cliente,
IF(provider.id IS NOT NULL, 'Fornecedor', '') as fornecedor
FROM person
LEFT JOIN provider ON person.id = provider.person_id
LEFT JOIN client ON person.id = client.person_id

But with this I also list users that are not providers nor clients, so I add this part to the code:

JOIN user ON person.id = user.person_id
WHERE user.id IS NULL

But now I lost the people that are User + provider/client.

BASIC DATA (Mock data)

person {
    id: 1,
    name: "test 01";
    id: 2,
    name: "test 02";
    id: 3,
    name: "test 03";
    id: 4,
    name: "test 04";
}
client {
    id: 1,
    person_id: 3
    name: "cli 01";
}
provider {
    id: 1,
    person_id: 3
    name: "prov 01";
    id: 2,
    person_id: 4
    name: "prov 02";
}
user {
    id: 1,
    person_id: 1
    name: "user 01";
    id: 2,
    person_id: 3
    name: "user 02";
}

The Data that I want is:

person_id: 3 -- it's a client
person_id: 4 -- it's a client and a provider

How could I list all providers + clients and not the users (but show the users that have provider/client)

According to comment you are not interested in users at all, you just want the person to be client or provider (boolean logic,  so it can be both):

WHERE (client.id IS NOT NULL OR provider.id IS NOT NULL)


SQL Server 2008 commit overhead warnings I have four mirrored user DB's on a pair of Server 2008 R2 machines. My Db engine is SQL Server 2008. Of the four Db's one is warning me of overhead commit latencies ( > 20ms). From some research on the web I see many references of a "redo queue".

I was hoping some one could explain to me what is a redo queue and how can I monitor its behavior.

Using performance monitor in windows you can for example watch the redo queue.
Also any other db monitoring tool would help.

The next sentence explains the redo queue, since I can't do it better than msdn here the quote:
"The mirror server immediately writes the incoming log to disk, where it is held until it is applied to the mirror database. The log waiting on the mirror's disk is known as the redo queue."

( https://technet.microsoft.com/en-us/library/ms189901%28v=sql.105%29.aspx )


Simultaneous READ COMMITTED transactions updates a select for update columns I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below

CREATE PROCEDURE `testproc`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'An error has occurred, operation rollbacked and the stored procedure was terminated' ;
    END;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

--- SOME LOGIC HERE ---
START TRANSACTION;
IF(SELECT IF(CurrentSize<BucketSize,TRUE,FALSE) FROM TestTable WHERE id = someId)
THEN
--- SOME INSERTIONS HERE ---
IF(SELECT IF(CurrentSize=BucketSize-1,TRUE,FALSE) FROM TestTable WHERE id = someId FOR UPDATE)
THEN
UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = someId;
--- SOME LOGIC HERE ---
ELSE
UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = someId;
END IF;

--- BELOW IS ADDED AS A EXTRA PRECAUTION ---
IF((SELECT COUNT(1) FROM TestTable WHERE CurrentSize > BucketSize AND id = someId) > 0)
THEN
ROLLBACK;
END IF;
END IF;
END

This works fine under normal load. But when we reach a high traffic I don't know for what reason the above logic fails and The CurrentSize column reaches a greater value than BucketSize.

It would be great if someone could help me with the same.

You can try to modify the testproc in the following way. I have tryed to be more simple as possible:

DELIMITER $$
CREATE PROCEDURE `testproc`()
BEGIN
  START TRANSACTION;
  select 'try to acquire lock';
  SELECT CurrentSize, BucketSize into @cursize, @bucketsize
  FROM TestTable WHERE id = 1 FOR UPDATE;
  select 'acquired lock';  -- only one thread is permitted here
  select sleep(1);  -- some work here
  IF @cursize < @bucketsize THEN
    select concat('CurrentSize < BucketSize (', @cursize, '<', @bucketsize, ')');
    UPDATE TestTable SET CurrentSize = CurrentSize+1 WHERE id = 1;
  ELSE
    select concat('CurrentSize >= BucketSize (', @cursize, '>', @bucketsize, ')');
  END IF;
  COMMIT;
  select 'lock released';
END $$
DELIMITER ;

The main point is: only one thread can get a lock on the row. Only after the lock is acquired, it can update the counter. (You can use double check design pattern for performance reason but it is a little more complex). You don't need to read uncommited transactions.

To get an insight, I have called testproc from two or more mysql clients in a loop. So I have defined a loop:

create table TestTable( id int primary key, CurrentSize int, BucketSize int ) engine=innodb;
insert into TestTable (id, CurrentSize, BucketSize) values (1, 1, 5);

delimiter $$
create procedure test_loop()
begin
   declare i int default 30;
   while i >0 do
     call testproc();
     set i = i -1;
   end while;
end $$
delimiter ;

I have launched two mysql clients:

mysql1> call test_loop();

mysql2> call test_loop();

mysql1> select * from TestTable;
+----+-------------+------------+
| id | CurrentSize | BucketSize |
+----+-------------+------------+
|  1 |           5 |          5 |
+----+-------------+------------+
1 row in set (0.00 sec)

You can try to comment out FOR UPDATE into the testproc:

SELECT CurrentSize, BucketSize into @cursize, @bucketsize
FROM TestTable WHERE id = 1;  -- FOR UPDATE;

If you try several times the previous test, you could get 6 into CurrentSize column.

mysql1> call test_loop();

mysql2> call test_loop();

mysql1> select * from TestTable;
+----+-------------+------------+
| id | CurrentSize | BucketSize |
+----+-------------+------------+
|  1 |           6 |          5 |
+----+-------------+------------+
1 row in set (0.00 sec)

Hope it is help.


MySQL column that auto generates data, longer than UUID My friend has the problem as shown below. He asked at StackOverFlow and there is no reply yet till now so I wish to help him to try the luck here. Thanks for everyone that helps.

"I would like to ask if its possible to set up a database table that has a column that auto generates random alphanumerical data that is longer that what UUID can do. I've been using UUID for a while now but I would like to have a longer string of random data in my columns, something similar to that of a token authenticator (around 300+ characters). So when I insert values in columns, this particular column will auto generate data by itself. Thanks in advance."

Quoted from Reuben Tan
Source: https://stackoverflow.com/questions/43461771/mysql-column-that-auto-generates-data-longer-than-uuid

You can create the pair of triggers ON INSERT and ON UPDATE that perform any desired transformations 'on the fly'

CREATE TRIGGER `myuuid_autoinserter`
BEFORE INSERT ON `table` -- or BEFORE UPDATE
FOR EACH ROW
BEGIN
  SET loooonguuid = CONCAT(OLD.uuid, OLD.uuid, OLD.uuid, OLD.uuid)
  SET NEW.uuid = loooonguuid;
END

When you run some INSERT or UPDATE query on the table trigger will replace submitted value of the uuid by string stored in the loooonguuid variable.

Sure you can do as complex and randomized transformation as you want.


How to prevent pg_dump from generating "SET default_table_access_method=heap;"? I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too.
Now I need to reload the file into a PostgreSQL v11 server, and I get a error message:
unrecognized configuration parameter "default_table_access_method"

I guess that this parameter is introduced by PG12, so PG11 can't recognize it, even though I use the 12 version of pg_restore.
Because the file is huge and in binary format, I can NOT edit it to remove the line manually.
I tested that pg_restore test_file -f plain.sql with a test dump file, and it looks like work, and I can remove the line from the plain SQL file.
But the real dump file is about 30GB, the plain SQL file expanded from the binary might be bigger (the working database occupies 300GB disk space).
Is there a way in it, I can cleanly reload it into PG11, only without the line "SET default_table_access_method = heap;"?
In fact, the following all can be ignored, because they are all default values:
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';

No, there is no such way, because downgrading PostgreSQL is not supported.
You'll have to manually edit the dump and remove lines such as that until the dump loads without error. There could be other, more complicated problems than new parameters, see this question for an example in an older version.
If the dump is large, you could split it in three parts:
pg_dump --format=plain  --section=pre-data  --file=dbname-pre.sql  dbname
pg_dump --format=custom --section=data      --file=dbname-data.dmp dbname
pg_dump --format=plain  --section=post-data --file=dbname-post.sql dbname

You will only have to edit the first and last file, which are always small. The "data" section can be dumped in custom format and will be compressed. After editing, load the files in the same order.


PostgreSQL - Grant privileges as superuser I have a question about the rights in PG (version 10).

I did 2 tests.

The scenario is the following:

create a db with the superuser
create a schema owned by a user
create a read only role
grant privileges to the role
grant role to the read only user
create a table in the specific schema with the user that owns it

When I grant the privileges as a superuser, the "testuser" can't read the table: test KO.

When I grant the privileges as the owner of the schema, "testuser" can read the table: test OK

Why the superuser can't grant the privileges on a schema owned by another user ?

TEST KO

pg_ctl stop -D .
rm -rf *
pg_ctl init -D .
pg_ctl start -D .
psql -p 5432 -d postgres -c "create database testdb"
psql -p 5432 -d postgres -c "create user testuser"
psql -p 5432 -d postgres -c "create role role_read_testdb"

psql -p 5432 -d testdb -c "create schema authorization testuser"
psql -p 5432 -d testdb -c "GRANT SELECT ON ALL TABLES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "GRANT SELECT ON ALL SEQUENCES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "GRANT USAGE ON SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON TABLES to role_read_testdb"
psql -p 5432 -d testdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON SEQUENCES to role_read_testdb"

psql -p 5432 -d postgres -c "create user user_read"
psql -p 5432 -d postgres -c "grant role_read_testdb to user_read"

psql -p 5432 -d testdb -U testuser -c "create table t1(col1 int)"
psql -p 5432 -d testdb -U user_read -c "select count(1) from testuser.t1"
ERROR:  permission denied for relation t1

TEST OK

pg_ctl stop -D .
rm -rf *
pg_ctl init -D .
pg_ctl start -D .
psql -p 5432 -d postgres -c "create database testdb"
psql -p 5432 -d postgres -c "create user testuser"
psql -p 5432 -d postgres -c "create role role_read_testdb"

psql -p 5432 -d testdb -c "create schema authorization testuser"
psql -p 5432 -d testdb -U testuser -c "GRANT SELECT ON ALL TABLES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "GRANT SELECT ON ALL SEQUENCES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "GRANT USAGE ON SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON TABLES to role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON SEQUENCES to role_read_testdb"

psql -p 5432 -d postgres -c "create user user_read"
psql -p 5432 -d postgres -c "grant role_read_testdb to user_read"

psql -p 5432 -d testdb -U testuser -c "create table t1(col1 int)"
psql -p 5432 -d testdb -U user_read -c "select count(1) from testuser.t1"
 count
-------
     0
(1 row)

Thanks for your help !

ALTER DEFAULT PRIVILEGES without a FOR ROLE clause alters the default privileges for objects created by only the current role (the role executing the ALTER DEFAULT PRIVILEGES).  So in the first case, you altered the superuser's default privileges, which has no effect on testuser's default privileges.


Using Database Engine Tuning Advisor in production I have a SQL Server 2008R2 OLTP database which is experiencing performance issues during work hours. I would like to run Database Engine Tuning Advisor on this instance but I don't know how it will impact the instance itself.

When can I use this tool and are there any risks involved (Is it possible to overload the DB or significantly impact the environment)?

EDIT: I have identified a few issues with the environment and came up with a few ideas to test.

I would like to further the question here:
If it is generally unadvisable to use DETA in production, when can and should it be used? Microsoft created this tool for a reason. If it may help DBAs and developers, how, when and why should we use it?

Don't go to DTA.  It can have severe performance impacts in production and can really do more harm than good (I learned that lesson the hard way when I started out as a DBA).

The first step is to find out what exactly is causing your performance issues.  Is it a CPU issue? Memory? Improper indexes? Also, how do you know you are experiencing a performance issue?  Are users complaining of slowness? Are you seeing CPU spike high and stay there?  What about memory? Is Page Life Expectancy dropping often and you can't explain why?  These are the questions you need to ask.  You need to find out where it hurts first, then go from there.

So, to answer your question, no you shouldn't use DTA [in production, during business hours].

EDIT:

This answer may help you as well: Should I rely on Database Engine Tuning Advisor for creating indexes?

The important thing to realize is that just because DTA recommends something, there is still ALOT to be said for using your knowledge of the DBs and data in conjunction with it.  Same goes for the wonderful Missing Index Warnings.  Can DTA get you started? Absolutely, but there's much more to consider when making changes that it recommends and the performance impact of running it in production.

I, personally, have used it less than 5 times in my career.  I try to use DMVs/queries in conjunction with our 2 monitoring solutions to troubleshoot performance issues.


Mongodb consumes too much RAM during replica resync I have a mongodb cluster with 2 data nodes and 1 arbiter.
I use mongodb 4.0.7 for all my centos VMs.

A few days ago one of my servers (lets call it data-2) had a fatal crash and requested a complete resync of the data. After restarting mongodb on data-2 the resync started. However immediately after that the RAM usage on data-1 (primary) started to skyrock.

However on data-2 the memory consumption was near constant:

During normal usage time the memory consumption stays close to that of data-2 during the resync.

After a few hours the worst case scenario happened and the last remaining  data holder (data-1) got OOM killed by the kernel after using the entire ram + swap (~50GB). I could restore data-1 as primary with little effort, but I every time I try to start the resync the same happens again.

This behavior seems to be unrelated to actual database usage during the resync. (Taking a prod db down for a resync is definitely a no go).

Total data size ~500GB, biggest database 480GB with ~300 collections.

Now my questions:

What uses that much memory during the resync?
How do I effectively analyze the memory consumption?
How do I prevent the primary from going down because of the resync?

What uses that much memory during the resync?

There is so many things uses the memory during the resync time, As Mr. SpiXel has clearly defined here The WiredTiger cache settings only controls the size of memory directly used by the WiredTiger storage engine (not the total memory used by mongod). Many other things are potentially taking memory in a MongoDB/WiredTiger configuration, such as the following:

WiredTiger compresses disk storage, but the data in memory are
uncompressed.
WiredTiger by default does not fsync the data on each commit
WiredTiger keeps multiple versions of records in its cache
WiredTiger Keeps checksums of the data in cache
MongoDB itself consumes memory to handle open connections,
aggregations, serverside code and etc.

The maximum size of the internal cache that WiredTiger will use for all data.

Changed in version 3.4: Values can range from 256MB to 10TB and can be a float. In addition, the default value has also changed.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of (RAM - 1 GB), or
256 MB.

For example, on a system with a total of 4GB of RAM the WiredTiger cache will use 1.5GB of RAM (0.5 * (4 GB - 1 GB) = 1.5 GB). Conversely, a system with a total of 1.25 GB of RAM will allocate 256 MB to the WiredTiger cache because that is more than half of the total RAM minus one gigabyte (0.5 * (1.25 GB - 1 GB) = 128 MB < 256 MB).

  How do I effectively analyze the memory consumption?

As per MongoDB blog here some of the  by @CASEY DUNHAM, MongoDB performance is a huge topic encompassing many areas of system activity.
By default, MongoDB will reserve 50 percent of the available memory for the WiredTiger data cache. The size of this cache is important to ensure WiredTiger performs adequately. It’s worth taking a look to see if you should alter it from the default. A good rule of thumb is that the size of the cache should be big enough to hold the entire application working set.

How do we know whether to alter it? Look at the  cache usage statistics:

> db.serverStatus().wiredTiger.cache
{
    "tracked dirty bytes in the cache" : <num>,
    "tracked bytes belonging to internal pages in the cache" : <num>,
    "bytes currently in the cache" : <num>,
    "tracked bytes belonging to leaf pages in the cache" : <num>,
    "maximum bytes configured" : <num>,
    "tracked bytes belonging to overflow pages in the cache" : <num>,
    "bytes read into cache" : <num>,
    "bytes written from cache" : <num>,
    "pages evicted by application threads" : <num>,
    "checkpoint blocked page eviction" : <num>,
    "unmodified pages evicted" : <num>,
    "page split during eviction deepened the tree" : <num>,
    "modified pages evicted" : <num>,
    "pages selected for eviction unable to be evicted" : <num>,
    "pages evicted because they exceeded the in-memory maximum" : <num>,
    "pages evicted because they had chains of deleted items" : <num>,
    "failed eviction of pages that exceeded the in-memory maximum" : <num>,
    "hazard pointer blocked page eviction" : <num>,
    "internal pages evicted" : <num>,
    "maximum page size at eviction" : <num>,
    "eviction server candidate queue empty when topping up" : <num>,
    "eviction server candidate queue not empty when topping up" : <num>,
    "eviction server evicting pages" : <num>,
    "eviction server populating queue, but not evicting pages" : <num>,
    "eviction server unable to reach eviction goal" : <num>,
    "internal pages split during eviction" : <num>,
    "leaf pages split during eviction" : <num>,
    "pages walked for eviction" : <num>,
    "eviction worker thread evicting pages" : <num>,
    "in-memory page splits" : <num>,
    "in-memory page passed criteria to be split" : <num>,
    "lookaside table insert calls" : <num>,
    "lookaside table remove calls" : <num>,
    "percentage overhead" : <num>,
    "tracked dirty pages in the cache" : <num>,
    "pages currently held in the cache" : <num>,
    "pages read into cache" : <num>,
    "pages read into cache requiring lookaside entries" : <num>,
    "pages written from cache" : <num>,
    "page written requiring lookaside records" : <num>,
    "pages written requiring in-memory restoration" : <num>
}

There’s a lot of data here, but we can focus on the following fields:

wiredTiger.cache.maximum bytes configured: This is the maximum cache
size.
wiredTiger.cache.bytes currently in the cache – This is the size of
the data currently in the cache. This should not be greater than the
maximum bytes configured.
wiredTiger.cache.tracked dirty bytes in the cache – This is the size
of the dirty data in the cache. This value should be less than the
bytes currently in the cache value.

Looking at these values, we can determine if we need to up the size of the cache for our instance. Additionally, we can look at the wiredTiger.cache.bytes read into cache value for read-heavy applications. If this value is consistently high, increasing the cache size may improve overall read performance.

  How do I prevent the primary from going down because of the resync?

As per MongoDB blog documentation here There might be a situation when primarily becomes inaccessible. When a primary does not communicate with the other members of the set for more than 10 seconds, an eligible secondary will hold an election to elect itself the new primary.
The first secondary to hold an election and receive a majority of the members’ votes becomes primary.
Although the timing varies, the failover process generally completes within a minute. For instance, it may take 10-30 seconds for the members of a replica set to declare a primary inaccessible. One of the remaining secondaries holds an election to elect itself as a new primary. The election itself may take another 10-30 seconds.
While an election is in the process, the replica set has no primary and cannot accept writes and all remaining members become read-only.

For further your ref here , here and here


"ORA-01017: invalid username/password" : Why is that? What is the difference between the two :

SQLPLUS / AS SYSDBA

and

SQLPLUS
Enter user-name: SYSDBA
Enter password:

If I use the first command I get connected and see the prompt SQL> but upon using the second command I always get ORA-01017: invalid username/password; logon denied , when I enter the correct password. Why is this ?

Also how do I get connected as the SYSDBA using the first command , when I didn't enter any password.

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0

SYSDBA isn't a user but a way to connect to the DB.

Connecting as sysdba is based on the OS authentication which means that as long as you connect to your machine with a user which is in the oradba group you will be connected with this user AS SYSDBA no metter what user/password you'll write (as long as you try to connect as sysdba of course)

Read more in this Tom Kyte's thread

So, this-

SQLPLUS / AS SYSDBA

and this

SQLPLUS a/b AS SYSDBA

and even this

SQLPLUS
Enter user-name: as SYSDBA
Enter user-name: a
Enter password: b

Are pretty much the same


WHERE Clause with pattern matching to find rows with any characters NOT in a list I am in a SQL Server 2008 environment.

I am trying to use pattern matching in the WHERE clause to find rows where a certain column's value contains characters that are not alpha-numeric, underscore, dash, period, or space.

This is my code, and sample data, but I am not getting the results expected.

In the example data, I want to return rows 7, 8, 9, and 12, but I am getting rows 5 and 6.

If this isn't the best way to achieve the goal, I am open to hearing other methods.

I am not in an environment where I can implement regex so my solution limited to out-of-the-box functionality.

CREATE TABLE PATTERN_TEST
(
ID INT NOT NULL,
STRING NVARCHAR(40) NOT NULL
)

INSERT INTO PATTERN_TEST
SELECT 1, 'string' UNION
SELECT 2, 'STRING' UNION
SELECT 3, 'string space' UNION
SELECT 4, 'STRING SPACE' UNION
SELECT 5, 'string-dash' UNION
SELECT 6, 'string-dash space' UNION
SELECT 7, 'string "otherchar"' UNION
SELECT 8, 'string "other char"' UNION
SELECT 9, '"string"' UNION
SELECT 10, 'string_underscore' UNION
SELECT 11, 'string_underscore space' UNION
SELECT 12, '"'
;

SELECT * FROM PATTERN_TEST WHERE STRING LIKE '%[^a-zA-Z0-9_ -.]%';

This will work as well:

SELECT *
FROM PATTERN_TEST
WHERE STRING LIKE '%[^a-zA-Z0-9_ .-]%' ;

Tested at rextester.com

The only difference is that dash (-) is put at the end of the [...] pattern. It has to do with the special meaning of the dash character (A-Z is interpreted as "any character from A to Z").

In your pattern the last three characters (space-dash-dot: -.) are interpreted as "any character from space () to dot (.)", and the results are not the wanted/expected.

You could also escape the dash:

WHERE STRING LIKE '%[^a-zA-Z0-9_ $-.]%' ESCAPE '$' ;

Note though that using ESCAPE may adversely affect cardinality estimation.

See the MSDN documentation for some details: LIKE:

  If there is no character after an escape character in the LIKE pattern, the pattern is not valid and the LIKE returns FALSE. If the character after an escape character is not a wildcard character, the escape character is discarded and the character following the escape is treated as a regular character in the pattern. This includes the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.

To be clear: special characters need escaping when they are used as non-special. Inside [], the three (^, -, ]) are special. Putting the dash at the end is more like a hack.


How to run INSERT INTO AS SELECT in parallel on Oracle? I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP.

INSERT INTO TEST_USER.AUD_BACKUP
    SELECT * FROM SYS.AUD$;
COMMIT;

How do I use parallel for INSERT as well as for SELECT ?
I can afford to run in 100 parallels. I am on Oracle db 12.2.

You should use direct path INSERT.
For select you can use PARALLEL hint in the same statement.  you may use PARALLEL without any parameter or use PARALLEL(n) with n number of thread.
Let me know if you need more explanation on this.


Change sa password for SQL Server on a GNU/Linux machine Please enter a password for the system administrator (SA) account:
    Please confirm the password for the system administrator (SA) account:

    Setting system administrator (SA) account password...
    sqlservr: Debugger.cpp:531: static void Debugger::Print(const void *, unsigned int): Assertion `m_IsAttached' failed.
    find: `/usr/share/icons/Numix/numix-icon-theme-circle-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/rhythmbox-plugins-open-containing-folder-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/alternative-toolbar-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/rb-fileorganizer-master': Permission denied
    find: `/usr/lib/rhythmbox/plugins/suspend-plugin-master': Permission denied
    nohup: redirecting stderr to stdout

    Do you wish to start the SQL Server service now? [y/n]: y
    Do you wish to enable SQL Server to start on boot? [y/n]: n

You can use sqlservr-setup --enable-service to enable SQL Server to start
at boot.

Setup completed successfully.

I can make a connection to SQL Server at first, using it with DBeaver for 1 week. Today I wanted to change the sa password, so I ran

sudo /opt/mssql/bin/sqlservr-setup

...but it seems to have failed. I removed the SQL Server package and installed it again, but the password for sa always gives me that kind of error. I can't make any connection again because of password failure.

How can I perform a fresh installation? I have deleted all mssql on /opt but no luck. Or how can I change the current sa password?

What OS are you using?

To get a fresh installation you have to remove the data directory, after removing the mssql-server package:

  sudo yum remove mssql-server
  OR
  sudo apt-get remove mssql-server

And then:

  sudo rm -rf /var/opt/mssql

This will delete all your user databases, but will also remove the system databases, which will allow you to reinstall and, hence, re-specify the SA password upon reinstall.

Let me know if this works.


Allow user to do anything within his own schema but not create or drop the schema itself I have created a schema in SQL Azure and granted the following permissions to a database role:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW
DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

Through the above defined permissions myuser can create/drop his own schema, so to overcome the problem I tried the ALTER ANY SCHEMA permission. But this permission also denies the user to create/drop tables.

What permissions are required in order to allow the user to do anything within his own schema but not be able to create or drop the schema itself?

There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).

The required permission to CREATE ALTER and DROP other objects is the CREATE permission for the object type (table\procedure\function\view) combined with ALTER permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL permission. For reference, here is a BOL list of DDL statements where You can find the required permission for all object types.

For the lazy, here is your code after removing the unnecessary permission:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;


Writing select result to a csv file We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.

I tried to use the suggested proc in the article below, but when I run the proc, SQL complains that can't run sys.sp_OACreate and sys.sp_OADestroy which are called in this proc.

Do you know how we can turn on these components or know any better way to write to a file using T-SQL?

Thanks in advance.

Use BCP utility

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

The -c argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but -t , changes the field terminator to commas.  -T specifies Windows authentication ("trusted connection"), otherwise use -U MyUserName -P MyPassword.

This doesn't export column headers by default. You need to use a UNION ALL for headers

OR

Use SQLCMD

SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable"
-s "," -o "D:\MyData.csv"

OR

Use Powershell

Here is a link to an article. If you end up using this, you might want to append -notype after Export-Csv $extractFile to get rid of the unnecessary column on the output file.


Need to understand the drastic difference in execution plan between two queries I have 2 queries.
    select guid=NEWID()
    into #guid

--update statistics #guid with all
--create statistics s1 on #guid(guid)

select * from Party a join #guid b on a.[Party_GUID] = b.guid --first select
select * from Party a join #guid b on a.[Party_GUID] = b.guid or b.guid is null --second select

drop table #guid

There is an index on Party.Party_GUID. The query execution plan for the first select is excellant. For the second select statement is horrible. I need to understand the reason for this. There is only one row in the #guid table and it has a value. So the query optimizer should be creating a similar execution plan for the second query too. Am I expecting too much of the optimizer ? I have tried this on 2008R2 as well as a 2012.

Admittedly this is a contrived reproduction of an issue that I am currently encountering in our system. The developers have coded a stored procedure with a table valued parameter which holds various combination of search values to be applied on a table. E.g the table valued parameter can have 3 nullable fields, GUID, LastName, FirstName and the application can populate it with values ((N'89241068-7068-4728-9CD0-A565FC2BFDEB', null, null), (null, "smith", "john"), (null, null, "jane")). The expectation is that the stored procedure apply it as a filter. e.g
    select * from Party a join @tablevar b on a.Party_GUID = b.guid OR b.guid is null
                                              and (a.LastName = b.LastName or b.LastName is null)
                                             and (a.FirstName = b.FirstName or b.FirstName is null)

One can argue that this is tough query to optimize but it-is-what-it-is currently and am trying to look at avenues to help the optimizer come up with the best query plans. I do understand that some inputs can result in a horrible plan. What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

I know there are dbcc traceflags which will help me understand why the optimizer chose a particular plan but i find those hard to comprehend.

Any help appreciated.

To summarize: the application sends a TVP where each row is a set of search parameters, where a null value in the TVP functioning as a "wildcard" indicate no filtering on that attribute.  The goal of the query is to return all the rows in the target table that match any of the rows in the input TVF.

So if the TVP sends (Id=123,Name=null),(ID=null,Name='Joe'), the procedure should return all rows match either the first set of criteria or the second.

  Am I expecting too much of the optimizer ?

Yes.  For this to work well, the QO would neeed to create a seperate plan for each row in the input TVF, and it simply was never build to do that.  For each row in the TVF a table scan will be required, as no single index can be used to evaluate the join criteria.

So you actually need to run a separate query for each row in the input TVF.  You can cursor over them and load a temp table, reducing this to an iterative form of a classic dynamic search query, which you can use Dynamic SQL or OPTION RECOMPILE to get tailored execution plans.

This

  What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

Is simple and not too relevant.  In the sample you posted if b.guid is null, then the query returns every row in Party.


FILEPROPERTY - SPACEUSED I used SELECT FILEPROPERTY('file_name', 'SpaceUsed'), and it returns NULL, what does it mean?

I am correctly using it in the correct database.

I cannot assume that NULL means empty since:

I tried to create a new database (test2), then query SELECT FILEPROPERTY('test2', 'SpaceUsed'). It already has a value.

The FILEPROPERTY is expecting a file (not a filegroup).
Make sure the the name you pass in parameter is on one of those :

select name from sys.database_files

This query will give you information about the data files of the selected database:

With A as
(SELECT db_name() as [Database],fg.name as [FilegroupName],  RTRIM(f.name) AS [Segment Name], f.groupid AS [Group Id], f.filename AS [File Name],
CAST(f.size/128.0 AS DECIMAL(10,2)) AS [Size in MB],CAST(FILEPROPERTY(f.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(f.name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(f.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(f.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles f join sys.filegroups fg on f.groupid=fg.data_space_id )
select 1 as [order], * from A union  select 2 as [order], REPLICATE('-',10), REPLICATE('-',10), REPLICATE('-',10), 0, REPLICATE('-',50),0,0,0,0
union
select 3 as [order], [Database],[FilegroupName],  '', [Group Id], '', sum([Size in MB]), sum([Space Used]), sum([Available Space]), Cast(sum([Space Used])/sum([Size in MB])*100 AS DECIMAL(10,2))
from A
group by [Database],[FilegroupName], [Group Id]
ORDER BY [order], [Segment Name]

Thanks


Granting SA privileges for Developers on the development box In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still responsible for maintaining this box.

We have now been entrusted the task of coming up with a list of Dos and Don'ts for the development teams with these enhanced privileges.

Please add to this list:

DO --
    confine activities to the DB under development

DO NOT --

change any SQL instance settings
sp_configure (including cmdshell)
add/change/delete any security settings
add/change/delete database objects
add/change/delete server objects like backup devices and linked servers
add/change/delete replication
add/change/delete maintenance plans
touch any database that doesn't belong to your team

Any pointers to tools available for tracking these users activities will be greatly appreciated.

If it's not too late, one compromise option that I've seen work well is rather than upgrade the permissions or replacing the developers' existing accounts, create a separate account that is only used when they need the elevated permissions.

So normally they work under individual "restricted" accounts (which I use loosely because these restricted accounts still need some hefty permissions — ie create, drop, alter for tables).  But for that rare occasion when they think they need sa, they can log in using this account.  Then you can flag the account in your logs and do extra monitoring on it.  You've given the developers the access they asked for, but in a way that's a little more controllable.

Eventually, if there's abuse, the logs on this account can be used as evidence to take it away.


SSIS Foreeach Container Not Failing I have a foreach container which is looking in a share directory for files to then send on to another location.  When I ran this as part of a job the package was running successfully however the files were not being sent.  The reason was because the agent account needed access to the share.

My question is how can I get the package to fail should it not have permissions to access the directory?

Thanks

Surprise, surprise, surprise!  SSIS doesn't fail the Foreach File loop container if the path is inaccessible – wow... how fun.  You'll have to add a script task to check before the loop fires.


How do I select the value with SERIAL data type in postgres I want to know how to select values from a column that has SERIAL data type.

I already try to do that but the output is like this 'Resource id #3', and not just '3'

I use SERIAL so I dont have to input ID like when I use integer

There is no actual serial data type. serial is just a notational convenience.
The resulting data type is integer for serial or bigint for bigserial

Related answer on SO with more details:
Auto increment SQL function

You'd have to be more specific for a more specific answer.


Index Maintenance on Clustered PK Index I know there are similar question to this one, but I couldn't find one dealing with Index Maintenance...
I have some PK Clustered Indexes [int (auto increment).] I always notice that these Indexes frequently have two issues:

Fragmentation is quickly over 10%
Fragmentation is sometimes over 90%

Do I need to apply the same logic (REBUILD/REORGANIZE) to these indexes?
This is how I'm retrieving the fragmentation:
SELECT dbschemas.[name] as 'Schema',
                    dbtables.[name] as 'TableName',
                    dbindexes.[name] as 'IndexName',
                    indexstats.avg_fragmentation_in_percent as 'AvgFragmentationInPct',
                    indexstats.page_count
                    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
                    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
                    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
                    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                    AND indexstats.index_id = dbindexes.index_id
                    WHERE indexstats.database_id = DB_ID()
                      AND indexstats.avg_fragmentation_in_percent >= 8
                      AND indexstats.page_count >= 100
                    ORDER BY indexstats.avg_fragmentation_in_percent desc

I wouldn't worry so much about the fragmentation over 10% but over 90% fragmentation on a clustered index is a lot...but are you seeing any performance issues with that specific table when it has that much fragmentation? (If it's not a big table, it may not even be a concern.)
If you are experiencing performance issues with higher fragmentation then you can look into rebuilding (or oftentimes more recommended reorganizing because of its similar benefits while being less heavy of an operation) the index.
For a point of reference, if you had a table with only 10 rows in it, each row on its own data page, then you'd have 90% fragmentation  but the performance difference would be negligible compared to if you rebuilt it and it had 0% fragmentation. (This is a very basic example, and holds true for much larger tables to a point.)
By the way, I find this Brent Ozar Article by Kendra Little a good read on rebuilding vs reorganizing indexes.


Why does CURRENT_USER() show the root@::1 when I login to MySQL? I've set 3 different passwords for the following accounts: 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1'.
By typing: mysql -u root -p I can only login with my 'root'@'::1' account password and CURRENT_USER() returns root::1. Why is that so? How can I login with the password that I set for the 'root'@'localhost' account?

I think the problem stems from how MySQL determines how to authenticate a user.
I mentioned this Jan 18, 2012 in MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
I quote from MySQL 5.0 Certification Study Guide

Pages 486,487 state the following on mysql's authentication algorithm:

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:
In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

Now look at the strings ::1 and localhost. What is the first letter's ASCII value of each ?
mysql> select ORD('::1'),ORD('locahost');
+------------+-----------------+
| ORD('::1') | ORD('locahost') |
+------------+-----------------+
|         58 |             108 |
+------------+-----------------+
1 row in set (0.00 sec)

mysql>

Since ::1 is alphabetically first, you get authenticated as root@::1


Map login to user in restored database in SYBASE ASE 16 After restoring a database from other environment is there a way to map a user in the database to a login in the server.  I can drop and recreate or alias but in MS SQL Server there is a way:
EXEC sp_change_users_login 'Auto_Fix', 'user'
I was looking for something similar in SYBASE ASE 16

Sybase (ASE) does not provide any method for syncing system tables between a user db and the master db.  It's up to the DBA/dbo to manually fix this after loading a database from a different dataserver (or loading an old db dump after the local dataserver's logins/roles/users/aliases has been (heavily) modified).
There are a few issues you'll want to consider:

users mapped to the wrong login (need to remap suid)
users with no login to map to (need to delete)
(db) roles mapped to the wrong (srvr) role (need to remap id)
(db) roles with no (srvr) role to map to (need to delete)
aliases mapped to the wrong login (easier to delete and re-run sp_addalias)
aliases with no login to map to (need to delete)
permissions with no associated grantee/granter (after cleaning up sysusers/sysalternates/sysroles)

Assuming you're looking for input/suggestions on how to go about patching sysusers ...

Do NOT perform these operations without first:

testing/verifying in a test database
making sure all changes are performed within a transaction and said changes are double-checked/verified before issuing a commit tran

Anyone proceeding with the following steps is solely responsible for any adverse outcomes!!

NOTE: sysusers contains data on users, groups and roles; the following applies to the entries for users.
Assumptions:

db user names are the same as the associated login name (ie, sysusers.name should match syslogins.name)
if logins have been mapped to users with different names (on purpose) ... the following will not work; the DBA will need to have a master list of what logins are supposed to be mapped to what users

General steps involved in patching user entries in sysusers:
-- mismatched sysusers entries

select  suid,suser_name(suid),name
from    sysusers
where   suid > 1
and     isnull(suser_name(suid),'DOESNOTEXIST') != name
and     name not in ('dbo','guest','probe','usedb_user')  # operator should review and update this list as necessary
go

-- assuming sysusers needs to be patched ...

-- enable updates of system tables

exec sp_configure 'allow updates to system tables',1
go

-- perform all updates inside a transaction !!!

begin tran
go

-- update sysusers based on sysusers.name = syslogins.name

update  sysusers
set     suid = suser_id(name)
where   suid            > 1
and     name            not in ('dbo','guest','probe','usedb_user')
and     suser_id(name)  != suid
and     suser_id(name)  is not NULL
go

-- delete sysusers where there is no syslogins.name = sysusers.name

delete  sysusers
where   suid            > 1
and     name            not in ('dbo','guest','probe','usedb_user')
and     suser_id(name)  is NULL

/* if any errors, or rowcounts are greater than expected:

   rollback tran

else

   commit tran
*/

-- disable updates of system tables

exec sp_configure 'allow updates to system tables',0
go


Is there a cross-platform way to compare data in one columnd on each sitde of replicated data, like a checksum or hash? I have an Oracle 12 database with lots of tables, and I am replicating several of the tables (a subset of rows) into a SQL Server 2016 database.   The subset of rows can be established with a WHERE clause on the Oracle side.
I have two web services that can expose anything I want to from that data, one on each side.
Do you have a suggestion for an approach of what I can expose, then compare to find out if the data between the systems matches?
I am currently exposing from one table, which has a few million rows the COUNT(*), which is a no-brainer since it is very efficient. So far, so good.
I'm also exposing the SUM of each of a few NUMBER(18,2) columns and comparing it to the corresponding SUM on the SQL Server side.  However, this is problematic, as it has to scan the whole table in SQL Server; it is sometimes blocked, and sometimes might cause other processes to block.  I imagine similar problems could occur on the Oracle side too.
Also, the SUM will not tell me if the rows match--it will only tell me that the totals match; if an amount was improperly added to one row and subtracted from another, I wouldn't catch it.
I've pondered whether Oracle's STANDARD_HASH might help me, but it seems cumbersome or error-prone to try to generate the exact same HASH on the SQL Server side, and also this doesn't help with the inefficiency/blocking nature of the call.
So is there any way to have both databases keep track of a hash, checksum, CRC code, or other summary of a column's data, that is efficient to retrieve, and that I can then use to compare to have some idea whether data is the same on both sides?  It need not be a perfect solution--for example, comparing SUMs is close but perhaps not quite good enough.
As a first stab, I created an "summary" indexed view, with columns derived from SUMs, on the SQL Server side.  This makes querying the view very fast, but incurs additional penalty on every write to the large table underneath.  Still, I think it will work, but I'd like to improve on it.  Other, better ideas?

The Oracle function for hashing is called
Standard_hash()
which supports these methods :- SHA1, SHA256, SHA384, SHA512, and MD5
https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647
In SQL Server you can use Hashbytes()
which supports these methods :- MD2, MD4, MD5, SHA, SHA1, or SHA2_256, SHA2_512
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
As they both support SHA2_256 or SHA2_512 I would start by setting up a few rows of test data and check that they produce the same result.
a search also throws up the following link
http://roelantvos.com/blog/hashing-in-sql-server-and-oracle-for-the-same-output/
EDIT :  I reread the comments & saw that you were concerned that standards for conversions of decimals to strings etc were not standard - I think you could fairly easily address this by using the schema & type views to dynamically construct conversion queries using CASE statements etc, & at least it only has to be done on one side.


Should I use UUIDs or integer primary keys to optimize for massive writes of relational data? I am working on a computer vision data pipeline and am unsure of how to structure my database to optimize for writes.

I have massive amounts of image data that is being collected on an ongoing basis. Image frames are used to build 1-3 second video clips which are to be labelled by a remote workforce. Workers label each clip (using a web application I built) with various properties (does the clip contain object x?).

My current pipeline generates the video clips and sends them to S3. An Amazon Aurora (with MySQL compatibility) database is used to track each image frame, clip, and associated tags.

The 'frames' table contains an entry for every single image frame, with associated metadata.

The 'clips' table contains an entry for every clip, has a field 'start_frame_id', which is a foreign key defining the first frame from the 'frames' table in the given clip. The associated clips are accessed from S3 by the remote workforce, using the sha256 hash of the clip as a key.

The 'labels' table contains an entry for each label created by a worker, and is related to the 'clips' table.

Both the 'clips' and 'frames' table contain a sha256 hash of the original file.

This database needs to be heavily optimized for writes, as the number of frames and clips will be massive (approximately 500K frames will be added per day, clips are 20fps). All uploads to S3 and writes to the database are done from local machines.

The prototype that I have built uses auto-incrementing integers for primary keys. However, this requires the client to execute database writes in small chunks. Since each clip needs to have a reference to its start frame, it is necessary that I commit all the frames for a given clip in order to obtain the primary key of the first frame, before I can commit the clip. This solution also makes it tricky/impossible to later add insert-only write replicas. For this reason, I am debating using UUIDs instead of integers, but I know this can cause performance problems with joins.

Should I use UUIDs or integers?

UUIDs are useful when you have clients independently generating unique identifiers.

id INT UNSIGNED AUTO_INCREMENT is smaller, faster, 'ordered', etc.

Use UUIDs only if you don't have a viable alternative.  More discussion:  http://mysql.rjweb.org/doc.php/uuid

In my opinion, sha256 is overkill for a 'digest'.

500K rows INSERTed per day?  That's 6/second?  Not a problem.  When you get to 100/sec, we should talk further.


How to set MSDN in order to open on a certain version of Microsoft SQL Server? In a network, there are certain different installed versions of Microsoft SQL Server, starting with 2005, 2008 and 2008 R2.

Is there a specific option of MSDN that can fix the opening of new pages on the version of SQL Server that is being worked on?

For instance, can I set MSDN to open on Microsoft SQL Server 2008 R2, if I work only on that version?

EDIT in order to clarify what I am asking:

When a user searches the Internet using a search engine for the syntax of a specific SQL statement, he might get to a MSDN web page such as the following one, which presents the "SELECT (Transact-SQL)" SQL statement:

https://msdn.microsoft.com/en-us/library/ms189499.aspx

Even if the user is logged in the MSDN system using a user name such as "UserName123", there are no apparent settings - or maybe I have not found them yet - so that the version of the Microsoft SQL Server can be pre-set. Currently, always the pages open with the latest version, which is "SQL Server 2014".
In this situation, the user has to select from the "Other Versions" drop-down list his preferred Microsoft SQL Server version, for instance "SQL Server 2005". There are cases when the user forgets to do that and reads the syntax for a version of Microsoft SQL Server that he is not interested in.
Finally, the question: Is it possible to automatically pre-set somehow, using any MSDN user configuration settings, the Microsoft SQL Server version that is automatically chosen when displaying information?

I came up with a thrown-together hack with what I had on hand. If you don't have Fiddler installed, you'll need to get it from http://www.telerik.com/fiddler

Set it to Keep: 100 sessions then go to the AutoResponder tab and add this rule:

If request matches: regex:https://msdn.microsoft.com/en-us/library/ms(\d+)(\(v=sql.(100|105|110)\))?.aspx

Then respond with: *redir:https://msdn.microsoft.com/en-us/library/ms$1(v=sql.90).aspx

If you'd like to use a different version of SQL Server, then change the number at the end of the Respond With string and make sure to take that number out of the If Request Matches string or you'll end up in a redirect loop.

Enable rules and check Unmatched requests passthrough. Fiddler will have to capture traffic (F12) in order for this to function. The configuration screen should look like this:


Foreign key reference to column that is no primary key I know, foreign keys should uniquely identify rows and therefore reference a primary key.

However, consider my example: There are 4 pre-defined categories, and for some combinations of values for these categories, there is a hierarchy of modules. The hierarchical structure is implemented by a parent module column.

My approach would be to implement it in a single table with these columns:

category 1 (e.g., region)
category 2 (e.g., kind of school)
category 3 (e.g., class)
category 4 (e.g., subject)
module title
parent module title
and a primary key consisting of all categories + the module title.

I would want the parent module title to reference the module title column. Now because a foreign key needs to reference the whole primary key, I would need to insert columns like parent category 1, parent category 2, parent category 3, parent category 4. But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

It would be ideal if I could write something like this in my table declaration:

--- fictitious SQL! ---

create table modules (
  ...,
  module_title varchar(50),
  foreign key parent_module_title varchar(50)
    references modules(module_title) where
      modules.category1 = category1 and modules.category2 = category2 and
      modules.category3 = category3 and modules.category4 = category4
  )

Or something like:

--- fictitious SQL! ---

  ...
  parent_module_title varchar(50)
    check (exists
    (select * from modules where modules.module_title = parent_module_title))
  ...

These are both in fictitious wishfulSQL (the latter throws cannot use subquery in check constraint), but is there a way of achieving my aim? I'm using Postgres if that matters.

First a note about hierarchies. The technique you are currently trying to use for modules is called adjacency list. I would encourage you to look-up few other, more appropriate, SQL techniques like: path enumeration, nested sets, closure table.

However, something seems to be wrong with this sentence:

  But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

This sounds unusual at best, a hierarchy may look like:

1        region/
1.1      region/school/
1.1.1    region/school/class/
1.1.1.1  region/school/class/subject/

I will go out on a limb and claim that the problem here is not about foreign keys, but about implementing a hierarchy in SQL.

To simplify the problem, I would suggest to experiment with path enumeration for your project and explore other techniques if required.

-- Module titled TITLE is categorized as CAT_PATH.
--
module {TITLE, CAT_PATH}
    PK {TITLE}

Where CAT_PATH is one of:

region/
region/school/
region/school/class/
region/school/class/subject/

This will likely result in a different set of problems and questions, but those are going to be easier to solve.


How to insert into junction table using triggers Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.

Artist and Song are base tables and SongArtist is the junction table. Everything in SongArtist is PK and FK.

CREATE  TABLE IF NOT EXISTS `Artist` (
  `artistID` INT NOT NULL AUTO_INCREMENT ,
  `artistName` VARCHAR(150) NOT NULL ,
  PRIMARY KEY (`artistID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `Song` (
  `songName` VARCHAR(150) NOT NULL ,
  `songID` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`songID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `SongArtist` (
  `songID` INT NOT NULL ,
  `artistID` INT NOT NULL ,
  PRIMARY KEY (`songID`, `artistID`) ,
  INDEX `fk_Artist_Artist_idx` (`artistID` ASC) ,
  INDEX `fk_Song_Song_idx` (`songID` ASC) ,
  CONSTRAINT `fk_Song_Song`
    FOREIGN KEY (`songID` )
    REFERENCES `Song` (`songID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Artist_Artist`
    FOREIGN KEY (`artistID` )
    REFERENCES `Artist` (`artistID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

I created some triggers like this, but they don't seem to work as I can't do INSERT INTO and add a new row when I only know one field of the junction table because I have two columns that are PK.

CREATE
TRIGGER after_song_insert AFTER INSERT
ON Song
FOR EACH ROW
BEGIN
    INSERT INTO SongArtist (songID) values (songID);
END;
CREATE
TRIGGER after_song_update AFTER UPDATE
ON Song
FOR EACH ROW
BEGIN
    INSERT INTO SongArtist (songID) values (songID);
END;
CREATE
TRIGGER after_song_delete AFTER DELETE
ON Song
FOR EACH ROW
BEGIN
    DELETE FROM SongArtist (songID) values (songID);
    END;
$$

DELIMITER ;

What should I do?

You have to think in terms of the design.

After triggers on the Song table cannot help populate the SongArtist table since Song and Artist have no immediate relationship.

Although you should not need this, you could have before triggers on SongArtist to check for validity of SongID and ArtistID.

DELIMITER $$
CREATE TRIGGER before_songartist_insert
BEFORE INSERT ON SongArtist
FOR EACH ROW
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
CREATE TRIGGER before_songartist_insert
BEFORE UPDATE ON SongArtist
FOR EACH ROW
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
DELIMITER ;

In reality, the ON DELETE CASCADE and ON UPDATE CASCADE clauses should make using triggers unnecessary. You should insert rows into the Song and Artist as a single transaction. Then, using songIDs and artistIDs from the completed transaction, you can insert them into SongArtist and let any defined CONSTRAINT check the validity.


MySQL running out of memory I am running a VPS with 1024 mb ram that is hosting a single WordPress site. Lately I have been having a lot of database problems where WordPress is giving me "Error establishing a database connection". I can make the server crash just by crawling it with Screaming Frog SEO tool.

Sometimes the issue resolves itself, sometimes I need to manually restart MySQL, and other times I have to completely reboot the server.

I thought the error was related to MySQL itself, but after examining the various logs, it seems like my server is running out of memory and therefore simply killing MySQL.

Using MySqltuner, I am getting the following results:

[--] Physical Memory     : 992.6M
[--] Max MySQL memory    : 804.0M
[--] Other process memory: 306.2M
[--] Total buffers: 225.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 409M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 643.1M (64.79% of installed RAM)
[OK] Maximum possible memory usage: 804.0M (80.99% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

Settings in my.cnf:

[mysqld]
max_allowed_packet=268435456
open_files_limit=10000
innodb_buffer_pool_size = 128M
innodb_log_file_size = 128M
key_buffer_size = 64M
innodb_file_per_table
slow_query_log=1
slow_query_log_file="/var/log/slowqueries"
long_query_time = 3
general_log=1
general_log_file="/var/lib/mysql/generalqueries"

Would really appreciate some suggestions as how to proceed from here to figure out what is consuming my memory and causing the issues.

1GB is "tiny" today.

Swapping is terrible for MySQL performance, so it is important not to tune things too big.

max_allowed_packet -- dangerously high, change to 30M; if you really need more than that, then either get more RAM, or we can dig deeper to find some other solution.

Change max_connections to, say, 40.

Change open_files_limit to, say, 1000.

Can you ferret out the specific error when "establishing a database connection"?  That might help, too.


Trying to start a newly created cluster in PostgreSQL on Windows I just created a new cluster using PostgreSQL 8.3 and looks like everything worked.  What I am having issues with is trying to start the server of this new cluster.  My two clusters are on different ports.  When looking online to start the new cluster, I tried executing the following line:

postgres -D C:\my\new\cluster\data\directory

This is the error I get:

  The server must be started under an unprivileged user ID to prevent
  possible system security compromises.  See the documentation for more
  information on how to properly start the server.

So I know I am running the command from administrator but I don't know how to run it from an "unprivileged user ID".  Any help would be great!

Aside from the upgrade recommendation (which I second btw but will not solve your current problem) your basic issue is that PostgreSQL will not run as an administrative user.  This is a security feature.

Now, if this is a desktop not on a domain model, you need to create a limited user, and run initdb as that (or assign ownership to all files under the existing second cluster to that user if it already exists which it may).  Then read the runas documentation.

Then run something like:

 runas /user:<computername>\postgres postgres -D C:\my\new\cluster\data\directory

So if your computer name is mycomputer, it would look like:

runas /user:mycomputer\postgres postgres -D C:\my\new\cluster\data\directory

You can also:

pg_ctl register -N postgresqlc2 -U postgres -P [password] -D C:\my\new\cluster\data\directory ....


If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered? If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the cursor is finished?

So per @LowlyDBA's suggestion I decided to test this for myself (to a degree).
I was actually in the middle of debugging a hot production issue so couldn't really stop to write out the code for a test case. But the code I'm debugging is this scenario. So I made my update to the procedure and looked at the running queries and saw the query text changed from before and after my change to the procedure as the cursor continued to iterate.


Changing the owner of a read-only database which is configured with Log Shipping Is there a way to change the owner of a read-only database which is configured with Log Shipping without actually re setting up LS?

Per this link it's not, but I'm checking if anything has changed since then.

When you say "without re setting up LS", are you able to have an outage to fix this?

If so, then look at using tail-log backups and recovering the secondary database to make this change. The high-level process would be:

Stop LSBackup, LSCopy and LSRestore (after ensuring there are no pending copies or restores)
Perform a tail-log backup. The primary DB will now be in recovery and inaccessible
Restore the tail-log backup on the secondary and recover the database. It will now be online.
Change the database owner as required.
Perform a tail-log backup on the secondary. It will now be in recovery and unavailable.
Restore the tail-log to the primary and recover it.
Re-enable your log shipping jobs and manually execute LSBackup, LSCopy and LSRestore

After this, your primary should be back online and your secondary back in a read-only state. As stated, this will cause an outage on your primary but this may be preferable to reinitialising log shipping on a large DB. Also, make sure you test this process in a non-Production environment first, and have the entire process scripted. SQLCMD mode is a great way to run this as you can script the whole process and run from a single script using the CONNECT option to change between your primary and secondary machine.

Tail-Log Backups

SSMS in SQLCMD Mode


Format decimal day as 24-hour HH:MM:SS How, w/o rounding up, do I get 'Decimal Day' into HH:MM:SS format using SQL?
Using MariaDB 5.x, one of the DB fields contains a pseudo date-time, as day.time.

DB 'Time' col
Day
Decimal 24-hour time
HH:MM:SS Time

0.1234567890
0
.1234567890
02:57:46

1.4567890123
1
.4567890123
10:57:46

2.8901234567
2
.8901234567
21:21:46

I'm able to pull the day out from the TIME column using:  SELECT dbTime - MOD(dbTime, 1) AS 'Day'
And I can separate the decimal time using:  MOD(dbTime, 1) AS 'Decimal Day'
I can't figure out how to get 'Decimal Day' into HH:MM:SS format? I have played around with two different numbers using the following SQL, which gives me the individual HH:MM:SS numbers, but the numbers always round up whenever I try using format, cast, or convert.

Decimal Day
HH:MM:SS Time

.51923075705577
12:27:41

.52923075705577
12:42:05

SELECT .51923075705577 * 24 AS 'hour',
       mod((.51923075705577 * 24), 1) * 60 AS 'minute',
       mod((mod((.51923075705577 * 24), 1) * 60), 1) * 60 AS 'seconds';

The times shown above are the desired result.
Thanks for any assistance on this one.


Interpret DBT2 results I'm testing experimental storage (so I expect the numbers to be bad / far off).

I ran the test on the experimental storage and got this:

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.01     42.087 :    55.219           11                0   0.00
   New Order  37.70     11.001 :     7.547          138                2   1.45
Order Status   2.19      0.157 :     0.619            8                0   0.00
     Payment  35.79      4.602 :     5.585          131                0   0.00
 Stock Level   1.37      0.777 :     1.675            5                0   0.00

-0.64 new-order transactions per minute (NOTPM)
-212.7 minute duration
73 total unknown errors
2.00 rollback transactions
-1 second(s) ramping up

And then I ran the same test on Azure 30 GB HDD (not a powerhouse either...) and got this:

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   2.31      0.068 :     0.096           10                0   0.00
   New Order  47.45      0.026 :     0.033          205                1   0.49
Order Status   3.94      0.002 :     0.003           17                0   0.00
     Payment  43.98      0.011 :     0.018          190                0   0.00
 Stock Level   2.31      0.005 :     0.009           10                0   0.00

41.07 new-order transactions per minute (NOTPM)
5.0 minute duration
0 total unknown errors
1.00 rollback transactions
0 second(s) ramping up

But I don't understand what am I seeing in any of the columns (well, sans the name of a transaction).  Can anyone kindly explain what do they show?

I'll give you my guesses, just to show I tried to solve the mystery :)

% relative portion of time the transactions of this type spent cumulatively.
Average must be the average latency (i.e. number of seconds it took for an average transaction to go through).
90 % The same as above, but for the 10% of the worst transactions in this group.
Total is the number of transactions performed.
Rollbacks is the number of rollbacks per all (?) transactions in this group.

My Perl skills are nonexistent, so I wouldn't be able to edit the mix_analyzer.pl to make it work with these data.  It's kind of funny how it went into negative space, though it would've been also useful if it worked even with such slow devices...

PS. This is tagged "MySQL" because the test is endorsed by MySQL, and the actual database under test was MariaDB.

% would be a percentage of number of transactions (check against Total column).

90th is a percentiles - 90% of transactions finished in this time (so maximum rather than average).

Your dodgy negative results could be result of running ntpdate as a cron job or some other extreme time adjustment so discard those results and try again.


Can I choose which CPUs / Cores to be used by MySQL? I have a Windows VPS (Windows 2008 R2 x64) that runs a couple WordPress blogs and other few ASP.NET things. The VPS runs 2 CPUs (for example).

With ASP.NET, in such situation, I usually set the app pool to use one CPU / Core, and SQL Server to use the other one (just change it in the server properties).

In MySQL, can I tell it which CPU(s) / Core(s) to use?

Maybe not the answer you were looking for, but you could use a hypervisor like vmware esxi and use processor affinity to lock the virtual machine to any number of specific cpus.


Recommendations for a SQL Server 2014 Database Administrators Course I am after some personal recommendations for a SQL Server 2012 Database Administrators Course with exams.

Exam 70-461: Querying Microsoft SQL Server 2014
Exam 70-462: Administering Microsoft SQL Server 2014 Databases
Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2014

I have done previous training with

•   Training Camp (FireBrand) this course they want £ 5,275 (6,330 Inc.
   VAT) this is too costly, yes I know they give Food and accommodation
   and exams. Good but currently out of my budget.

•   Learning tree this was designed to cover everybody was diluted a bit and the
   certification was their own.

•   Parity Training now have gone bust.

•   StayAhead training they mainly do Oracle but not bad.

I am after somewhere quality in that includes instructor lead training and exams the closer to Oxford (UK) the better.

I know this info will become out of date but will be useful as a guide for others, so will close in May 2014.

I would recommend signing up for a trial of PluralSight. They have a TON of content, including courses specifically aimed at 70-461 and 70-462. I have been a few of their courses and they are excellent: http://pluralsight.com/training/Courses#sql-server

Also, you may want to check out the MCM Readiness videos on Microsoft's site. They are a little out of date, but for things like index internals it won't matter a whole lot: http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx


Wordpress site has a lot of connections Recently my wordpress site is having troubles and is stopping from working due to heavy load in the mysql processes. I need to know what is causing all this traffic and how to narrow the issue so I can troubleshoot the error. I ran some commands and I am getting this results

show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 143   |
| Connections              | 17736 |
| Max_used_connections     | 198   |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 6     |
+--------------------------+-------+

mysqladmin status
Uptime: 3711  Threads: 3  Questions: 17872313  Slow queries: 13  Opens: 344
     Flush tables: 1  Open tables: 335  Queries per second avg: 4816.036

and sometimes the cpu consumption by mysql reaches the 300%

5 Connections/sec is slightly high.
5K QPS is high.
Max_used_connections is already very high.  Part of this could be due to nothing being cached in the buffer_pool after the restart, which was barely an hour ago.  What is Max_connections?
Aborted_connects/sec is quite high -- which is what you are asking about.  On the other hand, Aborted_connects/Connections is moderately high.  Any chance a hacker is trying to get in?
If some of those 'slow' queries run a long time and/or block other actions, that could explain some of the issues.  What is long_query_time?  (It defaults to a virtually-useless 10 seconds; change it to 1.)  Are the slow queries being logged?  300% CPU usually indicates inefficient queries (indexing, formulation, etc)

Recommendations:

Turn on the slowlog.
Run for several hours before analyzing.
Digest the slowlog to see what is naughtiest.
See which of these you would like to pursue:  http://mysql.rjweb.org/doc.php/mysql_analysis


Building a ColumnStore index hogs system resources I am experimenting with the columnstore and I am finding that while it is building the process hogs all the system resources. I have a table with 2.3 billion rows and while I build the columnstore on it all other queries come to a complete stand still. Is there a way to limit the amount of resources used by the columnstore build so that my other queries can continue to run. The other queries do not involve this table.

Run the index build in a resource governor group that limits the resources you want to give. Columnstore building is CPU and memory intensive. Resource governor should be your preferred way to throttle resources, no matter the scenario.


WorldWide site - should I save UTC or in fixed time? Let's say I have a world wide website ( I mean it has uses from all around the world).

I have 1 server which is in germany ( at some city). it will never be moved from germany - ever.

My question is :

When users saves data  , I need to save the action date :

Should I save ( and I think I do) it as UTC ?

Or since - the server is in one location - it is ok to save all the dates as GetDATE()
so the point of relativity is the same point.

Am I wrong ? If I do , can you please supply a simple scenario where it's gonna fail ?

Always store data in the database in UTC and then use the appropriate timezone to convert it during display, in the application. Is the only sensible approach when it comes to consider data mobility (geo-replication), user location and , most importantly, daylight savings. If you store local time there will be times when you cannot distinguish between column values and you cannot order them properly due to daylight savings. Think that, in Germany, Oct. 27th 2:05 am will happen twice this year... And that is an easy case compared to, say, Morocco.


repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB Working through a database corruption issue. I am working on a copy in a development environment. Some were corrupt indexes in which case I just dropped and recreated it. These however are data corruption issues that are of concern.

When I run:

DBCC CHECKDB ('DATABASE', REPAIR_ALLOW_DATA_LOSS);

...it says:

  the error has been repaired

Does that actually mean it fixed the problem or that it deleted the page which was the "repair" and data was lost?

  Object ID 1899231358, index ID 0, partition ID 124468026277888, alloc unit ID 124468026277888 (type In-row data):  Page (1:10429642) could not be processed.  See other errors for details.
  The error has been repaired.

  Table error: Object ID 1518653945, index ID 1, partition ID 72057594166444032, alloc unit ID 71875645566156800 (type LOB data).
  The off-row data node at page (1:10429980), slot 0, text ID 954358824960 is referenced by page (1:6660634), slot 10, but was not seen in the scan.
  The error has been repaired.

As the option you used states...you lost data with the repair

REPAIR_ALLOW_DATA_LOSS

In your case you lost index 0, in-row data.  The repair completed successfully, but that doesn't make your lost data come back.  About the only scenario of corruption that is fixable without data loss using the option is if all of the corruption was on a non-clustered (not in-row) data.

If you have a previous backup, you may be able to restore the damaged pages. It depends upon your backups not having the same corruption for the affected pages, which is highly likely if you weren't regularly running checkdb, didn't use checksum page verification, and the with checksum option for the backups.

Review the table involved and try to figure out if you can reconstruct otherwise what was lost.  Otherwise, good luck and you can try a page restore, see:

How to Restore a Page in SQL Server Standard and Enterprise Edition by Jes Schultz Borland


pt-table-checksum and row-based replication The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it happens to be on our same server.

We had replication stop recently, and I want to make sure we have recovered properly.

I downloaded and installed the Percona Toolkit, but it doesn't look like I'll be able to use pt-table-checksum in this case, specifically because of the row-based replication:

  2.27.5 LIMITATIONS Replicas using row-based replication pt-table-checksum requires statement-based replication, and it sets
  binlog_format=STATEMENT on the master, but due to a MySQL limitation
  replicas do not honor this change. Therefore, checksums will not
  replicate past any replicas using row-based replication that are
  masters for further replicas. The tool automatically checks the
  binlog_format on all servers. See
  --[no]check-binlog-format . (Bug 899415)

Are there any other options for doing a checksum on row-based replication tables?

I have found this on the Percona Forum which says it will work, but I cannot have it change my cnf file as it claims it will do. It also warns, when I attempt to run it, the following:

  Replica mysql-b.example.com has binlog_format MIXED which could cause
  pt-table-checksum to break replication.  Please read "Replicas using
  row-based replication" in the LIMITATIONS section of the tool's
  documentation.  If you understand the risks, specify
  --no-check-binlog-format to disable this check.

I do not understand why an application requires the MIXED replication format, as MIXED is equal to STATEMENT-based, but changing to ROW format when executing "unsafe" queries. So, a MIXED-compatible application should be compatible with both STATEMENT and ROW.

As you can see here, if the the format detected (server-side) is ROW or MIXED, it tries to change it dynamically for the session only. In your case, it complains about one of the slaves here (it requires SUPER privileges).

Assuming you do not have a 3-node chain replication, or you use the slave's binlog for anything, you can ignore the warning with --no-check-binlog-format. No need to change the my.cnf. The question is, will the session-only change to statement affect your "application that requires MIXED"? pt-table-checksum requires its own calculations to be done as statements, as a hash has to be calculated independently on the master and the slave independently and synchronously (not in time, but in relation with other queries).


combining 2 sql queries based on different conditions I have a table :

Person :

(integer) (varchar)  (Date)         (date)
  id       state       d1            d2
  1       'ALIVE'   '2015-08-07'  '2015-08-08'
  2       'DEAD'    '2015-08-08'  '2015-08-10'
  3       'DEAD'    '2015-08-08'  '2015-08-08'

And so on..

What I want is the count of all people when :

  person is alive and d1 <=someDate
  person is dead and d2 > someDate and d1 < = someDate.

So, in the above case, if someDate = '2015-08-08', then the output should be 2. i.e, id 3 should not be considered.

I can of course do this as 2 seperate queries and add the result. How to do this in just one query.

Note : This is an example of what I am trying to do. The table names, columns are different in my code.

That's a simple OR condition:

select *
from person
where (state = 'ALIVE' and d1 <= DATE '2015-08-08')
   or (state = 'DEAD' and d2 > DATE '2015-08-08');

The parentheses combining the AND operators are required, otherwise you'd get a wrong result.