Optimizing ORDER BY for simple MySQL query I'm trying to optimize this really simple query, and it gives me grief for a day now :(

Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a particular order. Here's the query:

SELECT * FROM `po`
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10

However, it runs really slow (half a second to 2 seconds). Here are table structures:

CREATE TABLE `po` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB AUTO_INCREMENT=457790 DEFAULT CHARSET=latin1

CREATE TABLE `po_suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9386 DEFAULT CHARSET=latin1

Running EXPLAIN reveals the issue:

+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref          | rows | Extra                                        |
+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | index | PRIMARY       | PRIMARY     | 4       | NULL         |  480 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | po    | ref   | supplier_id   | supplier_id | 4       | sergiis.s.id |    1 | Using index                                  |
+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+

Can you help me figure out how I can optimize this to run faster? I have index on the column I sort by. I have index on columns I join by. If I remove the JOIN - it's crazy fast. If I remove ORDER BY, it's crazy fast. Why am I getting this dreaded temporary + filesort?

The EXPLAIN SELECT you posted definitely seems counter-intuitive.

If your query included WHERE s.id = ... then the query plan you're seeing might make a little bit more sense, but I'm assuming you're not.

It looks like the optimizer is getting distracted by the facts that supplier is a smaller table and that the supplier_id index in the po table can be used as a covering index... and with those facts in hand, it's overlooking the seemingly-obvious fact that the tables should be read in the opposite order than the one it chooses.

Here are two alternatives.

-- use the STRAIGHT_JOIN directive to insist that the optimizer process the tables in only the listed order:

SELECT STRAIGHT_JOIN * FROM `po`
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

-- use the FORCE KEY index hint to direct the optimizer to prefer the primary key of the po table:

SELECT * FROM `po` FORCE KEY (PRIMARY)
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

The first option is probably the better option, since FORCE KEY, in spite of the name, is still only a "hint" that the optimizer can choose to ignore, while STRAIGHT_JOIN genuinely does force the hand of the optimizer to join the tables in the order they're listed.


Clustered index size requirement I have a HEAP table with no indexes (on SQL Server 2012 Std). I need to create a clustered index on an INT column. There are ~1.2 billion rows and the int column is nullable and not unique. I'm struggling to figure out how much space this will take up in the filegroup. Could someone advise please?

Edit: I'll sort in the tempdb by the way

When you create a clustered index this "replaces" the heap so there are no extra index pages as such (aside from the upper levels of the b-tree), and as you have absolutely no other indexes there is nothing else to affect the size of.

Therefore if your target column already exists I wouldn't expect very much extra space to be permanently taken unless you have fill factor set to something other than full (for example with a fill factor of 80% the new index would leave approximately 20% of each leaf page spare for later additions).

Some extra space may be used as the heap is rebuilt as a clustered index so your database may need to grow to accommodate this, but any such extra space will be free for future allocation once the process is complete.

For an example, see Index Disk Space Example in the product documentation.

There is even more detailed information in the Online Indexing Operations in SQL Server 2005 technical article (despite the name, it covers offline operations as well).

Extra

It could take less space than before as you have noticed. You may have had a lot of part-used or empty pages due to deletes and updates over time. Heaps do not release the space used by empty pages created by deletes unless TABLOCK was specified at the time of the deletion, and even then not always. From DELETE (Transact-SQL):

  When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

There may also have been a small amount of space used by forward record pointers (which don't exist in clustered indexes), and if your rows are of variable length blocks of space in pages that are too small to use for new rows. Your newly reorganised rows will be more tightly packed (fill factor settings permitting) so may well use fewer pages.


Transaction Identifier - possible with MySQL? I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if everything goes OK.

What I need is to detect if the connection at the end is still within the same transaction, as the user code can query commit/rollback and start a new transaction.

What is the industry practice for this? Can MySQL database return some transaction ticket/ID? Or MySQL store transaction only variable, which will be detectable after commit/rollback?

I doubt if there is a transaction id.  But here are some "good practices":

Turn off auto-reconnect.  (This is a parameter on certain APIs when establishing the connection.)  A disconnect terminates transactions and rolls them back.  With auto-reconnect OFF, you will get an error on whatever happens next.
Check for errors after ever SQL statement.
Avoid spaghetti code.  Arrange the code so that it is obvious that there is no way to sneak out and do a COMMIT or ROLLBACK.  I like to have the START TRANSACTION at the beginning of a subroutine and the COMMIT and ROLLBACK near the end.  Then make sure that any subroutines it calls assume that they are inside a transaction.

This might help you discover if you have started another transaction in your connection:

SHOW SESSION STATUS LIKE 'Com_begin';

or

SELECT  Variable_Value
    FROM  information_schema.SESSION_STATUS
    WHERE  Variable_name = 'Com_begin';

Collect that right after doing the BEGIN or START TRANSACTION; collect it again just before your COMMIT to see if it has changed.  ("Changed" == something is wrong.)  Note:  I am using SESSION, not GLOBAL.


Design for active/passive Master-Master mysql Setup I have a dozen virtual machines running application + mysql instances (version 5.1 up to 5.5). Due to the bad I/O of the virtualization I want to buy a physical server for mysql and move all mysqldata to the new server. I would like to install Percona Server or Cluster (5.6).

The new hardware: Xenon E5-2620 (6 Cores), 32GB RAM, 400GB SAS serial 12Gb/s

For higher availability I am thinking about a active/passive Master-Master Setup and install mysql also in one virtual machine. A load balancer would forward all requests (read and write) to the new physical host. If the server would go down, all requests would be send to the virtual machine.

All hosts are connected to one switch so network latency should be no problem.

Use Percona Cluster

pro: working multi-master setup; synchronous replication;
con: the fast new server would be waiting for the slow (vm) node at each insert/update?
con: i need a 3rd node which would be an arbitrator on my load balancer?

Use mysql regular replication with bin_log

pro: active master has the best performance?
pro: no arbitrator needed
active/passive master-master should not be problematic for replication?
con: slave could be out of sync? would the master resync automatically?

Any other ideas I am missing? Does anyone have experiences with these setups regarding availability/stability/performance?

I will start with only 2 server with an active/passive master-master setup and a loadbalancer in front. I don't see any benifits for the cluster and I don't want a 3rd node or the active master waiting for any other nodes.


How to migrate between schemas in bulk in SQL Server? We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas.

In MS SQL Server 2008 R2, how can I relocate all the contents of one schema into another in bulk?

For example, all of the tables, views, procedures, indices, etc... that we created into the dbo schema will now live in the foo schema.

EDIT: I wanted to clarify based on AaronBertrand's great comments. This is not a multi-tenancy situation. Our situation is where internal tools plugins were developed in isolation by developers who didn't merge their tables into the tool's database.

The basic concept is actually quite simple: you generate a script from sys.objects and sys.schemas that builds ALTER SCHEMA TRANSFER statements. So for example, you have three objects in the dbo schema, and you want to move all of them to the blat schema:

Table: dbo.foo
Table: dbo.bar
View:  dbo.vFooBar

The following code:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA blat TRANSFER dbo.' + QUOTENAME(o.name) + ';'
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo';

PRINT @sql;
-- EXEC sp_executesql @sql;

Will yield this script (but perhaps not in this order):

ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;

(You may want to add additional filters to leave out objects in the dbo schema that you don't want to move, leave out certain object types (e.g. maybe all of your functions are utility functions and don't need to move), generate the script ordered by object type, etc.)

But there are a couple of problems with moving all of your objects to a new schema:

Probably a lot your code will still reference these objects as dbo.object - there is no easy way to fix this except brute force. You can probably find all of the occurrences of dbo. pretty easily, but these can also return false positives, such as EXEC dbo.sp_executesql, dbo. in comments, true references to objects that remain in the dbo. schema, etc.
Your dependencies will probably be completely out of whack, but I haven't thoroughly tested this. I do know that in this scenario:

CREATE SCHEMA blat AUTHORIZATION dbo;
GO

CREATE TABLE dbo.foo(a INT PRIMARY KEY);
CREATE TABLE dbo.bar(a INT FOREIGN KEY REFERENCES dbo.foo(a));
GO

CREATE PROCEDURE dbo.pX AS
BEGIN
  SET NOCOUNT ON;
  SELECT a FROM dbo.bar;
END
GO

CREATE VIEW dbo.vFooBar
AS
  SELECT foo.a, bar.a AS barA
    FROM dbo.foo
    INNER JOIN dbo.bar
    ON foo.a = bar.a;
GO

ALTER SCHEMA blat TRANSFER dbo.foo;
ALTER SCHEMA blat TRANSFER dbo.bar;
ALTER SCHEMA blat TRANSFER dbo.pX;
ALTER SCHEMA blat TRANSFER dbo.vFooBar;

The foreign keys actually migrate more smoothly than I expected (with the caveat that I'm testing on a much more recent version than you). But because the code in blat.pX still references dbo.bar, obviously executing the procedure:

EXEC blat.pX;

Is going to yield this error:

  Msg 208, Level 16, State 1, Procedure pX
  Invalid object name 'dbo.bar'.

And dependency queries, such as:

SELECT * FROM sys.dm_sql_referenced_entities('blat.pX', N'OBJECT');

Will yield this error:

  Msg 2020, Level 16, State 1
  The dependencies reported for entity "blat.pX" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.  Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

And querying the view:

SELECT a, barA FROM blat.vFooBar;

Yields these errors:

  Msg 208, Level 16, State 1, Procedure vFooBar
  Invalid object name 'dbo.foo'.
  Msg 4413, Level 16, State 1
  Could not use view or function 'blat.vFoobar' because of binding errors.

So, this could involve a lot of cleanup. And after you've fixed all the object references, you'll probably want to recompile all of the modules and refresh all of the views in the new schema. You can generate a script for that quite similar to the example above.


TSQL script returning wrong result I'm trying to find out if a particular index name "Test" exists in all databases using below code. The result I'm getting is always "NOT EXIST" for all databases in an instance. However, the index does indeed exist on few of the databases. Can someone please point out where the code is wrong?

DECLARE @Name AS VARCHAR(100)
DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases where name not in ('master','msdb','model','tempdb')
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
--Check if index exist
   EXECUTE('USE [' + @Name + ']')
   If 1 = ((SELECT COUNT(*) as index_count
        FROM sys.indexes
        WHERE object_id = OBJECT_ID('dbo.TestTable')
        AND name = 'Test'))
    Print 'INDEX EXIST ' + @Name;
    ELSE
    Print 'INDEX DOES NOT EXIST ' + @Name;
   FETCH NEXT FROM dbCur
INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur

Result:

  INDEX DOES NOT EXIST DB1
  INDEX DOES NOT EXIST DB2
  INDEX DOES NOT EXIST DB3
  INDEX DOES NOT EXIST DB4

Your problem might be related to issuing a USE inside of a cursor. Instead, I would suggest using the following approach, so you can get rid of the cursor.

EXEC sp_MSforeachdb
'USE ?
 IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
 BEGIN
      IF EXISTS(SELECT 1 FROM sys.indexes ind WHERE object_id = OBJECT_ID(''dbo.TestTable'') AND name = ''Test'')
    PRINT ''Exists in '' + DB_NAME()
ELSE
    PRINT ''Not Exists in '' + DB_NAME()
END'

Please note that technically, sp_MSforeachdb is an undocumented feature but it simply works.


PostgreSQL PSQL client-side commands and new lines (\COPY) The error message is the same with super user using COPY. The files are  located on the same server as the postgres server. Saw many similar posting but not a single one answer my question.

\copy table_name from '/path/to/csv/file.csv'
with format csv, header true ;

ERROR: syntax error at or near "format"

Tied with parenthesis or not.  Read the manual at version 10 for postgres carefully.  Not helping.  I must be missing some minor point, please point out.

psql "meta-commands"

Commands in psql that start with back-slash (\) are called "meta-commands" and they don't follow the usual semicolon rules but are instead terminated by a newline. A small excerpt from man psql

  Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

Much more said on this in the man pages. Yes, it's weird and annoying. It's because historically psql commands were a sort of metacommand you'd want to run without interrupting any query you were building, like \d to see columns of a table. But for pseudo-statements like \copy it gets confusing.

What's happening here

In your case, what's happening is that psql runs the first line, then sends the second to the postgres server, which recognises WITH as a valid CTE statement token then gets confused by FORMAT.

What you want is,

\copy table_name from '/path/to/csv/file.csv' with format csv, header true


Why does DataContext.SubmitChanges() Exist? I'm not sure if this goes here or on Stack Overflow, but I figure someone here might know it.

Why did the developers of LINQ decide that not only do you need

ctx.InsertOnSubmit(item);

but you also need

ctx.Items.SubmitChanges();

Did they do this so you could execute several queries on one trip to the database or am I missing something?

I'm not 100% sure about LINQ but i would say that the seperation is based on transaction control. You apply any number of updates, inserts or deletes. That are then held as a unit of work, then once you've finished your changes you simply commit them to the database as a single action or roll all of the changes back, maintaining the integrity of your database.


Mysql high CPU and RAM usage I have a mysql server which has 60gb of ram and it uses too much system resources. Top command result;

Mem:   64555285k total, 64151125k used

I think there is something terribly wrong with my mysql conf.Here is my mysql conf;

[mysqld]
max-allowed-packet=400M
max_connections=8000
query_cache_size = 50M
thread_cache_size =32
table_open_cache = 1000
innodb_buffer_pool_size = 35G
innodb_log_buffer_size = 70M
join_buffer_size= 8G
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 1

Is there anything wrong with my mysql conf?

join_buffer_size= 8G is dangerously high.  If you have a lot of queries running, all needing the "join buffer", you could quickly run out of RAM, as you appear to have.

8M is a better value for that setting.  Keep in mind that if a query 'wants' a join buffer, it will either use the space you provide or perform the query a different way.

max_connections=8000 is quite high.  What is the status value of Max_used_connections?  If it is more than a few hundred, we should discuss why it is so large.  That could have an impact on memory usage -- many things are "per connection".

Another possibility...  What else is running?  Could it be chewing up RAM?

For a more thorough review, provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (perhaps on some postit site).


MariaDB Encryption - unknown variable 'plugin-load-add=file_key_management.dll' I'm really new to MariaDB and I need to set up encryption at rest. I'm running MariaDB 10.3 on Windows Svr 2012R2.

I have created a key and added the following to my config file (my.ini)

plugin-load-add=file_key_management.dll
file-key-management
file-key-management-filename = "C:/MariaDB/data/keys.txt"
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

The service starts fine. However when I go to open the MySQL Client I get the following error:

  C:\Program Files\MariaDB 10.3\bin\mysql.exe: unknown variable 'plugin-load-add=file_key_management.dll'

The dll is present and in the plugin-dir location specified in the config file.

Can anyone help me please?

mariadb from version 10.3 use other command in config:
[mariadb]
plugin_load_add = file_key_management
like show in  here


Getting error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value I am getting the error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. for the following query.
After checking some questions here I was able to know the date format of my sql server by running SELECT GETDATE() and what I got was 2015-06-11 15:58:01.473 which seems like the format i am using to insert the data. What am I doing wrong?

SET IDENTITY_INSERT cms_clients ON
INSERT INTO cms_clients (id, user_id, firstname, lastname, middlename, pseudoname, dob, photo, careplan1, careplan2, gender, program, csscentre, managing_csc, unique_ref_no, cur_weight, tar_weight, benf_ent_number, med_number, drugs_alert, prescriber_particulars, special_considerations, vaccinations, dom, doa, status, address, medicare_expiry_date, telephone, date_of_annual_health_care_plan, first_language, interp_req, religion, religious_considerations, diagnosis_and_associated_health_conditions, communcation, challenging_behaviours, medicare_number, pension_number, private_health_insurance, person_responsible, person_res_name, person_res_address, person_res_telephone_h, person_res_telephone_w, person_res_telephone_m, person_res_email, decision_making_function, relative, guardian_name, guardian_address, guardian_telephone_h, guardian_telephone_w, guardian_telephone_m, guardian_email, health_number, entry_date, exit_date, mobile_no, spoken_lang, known_address, written_directives, significant_other, additional_info, school_program, school_address, school_contact, school_phone, school_transport, families_social, families_email, families_snr, families_supervisor, families_fsa, families_address, families_phone, families_fax, curfew_fsa, curfew_mpr, youth_allowance, pocket_money, bail_cond, details, court_date, transport_via, location, pract_name, pract_relationship, pract_phone, pract_fax, pract_address, pract_email, approved_name, approved_relationship, approved_phone, med_expiry_date) VALUES
(1, '', 'test', 'test', '', '', '2015-06-05', '', '', '9', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '2015-06-04 06:00:50', '0000-00-00 00:00:00', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00'),
(180, '', 'test 2', 'test', '', '', '2015-06-05', '', '', '10', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '2015-06-04 06:00:50', '0000-00-00 00:00:00', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00'),
(181, '', 'test 3', 'test', '', '', '2015-06-05', '', '', '8', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '2015-06-04 06:00:50', '0000-00-00 00:00:00', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00'),
(182, '', 'Ted', 'Mosbey', '', '', '2015-06-10', '', '', '10', 'M', '', '', '', '', 0, 0, '', '', '', '', '', '', '0000-00-00 00:00:00', '2015-06-09 05:54:50', 'A', '', '1970-01-01', '', '1970-01-01', '', 0, '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '0000-00-00');

The values '0000-00-00' are causing this error. You could for example use NULL instead.


Predicate pushdown on view to optimize query? I have a view that references a fact table Cost with 300M records. That view also has a couple joins. The fact table has a clustered index on Date and the view definition is something like this:

select *
from FactCost f
INNER JOIN SomeOtherTable b on a.id = f.id
where f.Date < getdate()-7

If I query the view like below, the optimizer first processes the predicate within the view and then the date in my query. So it actually reads 300M records and returns only 1 million. I can't figure out what kind of workaround I can try to optimize this.

SELECT
              *
    FROM [dbo].vwFactCost
    WHERE Date >= '2011-07-01'
        AND Date <= '2011-07-31'

see below

EDIT

:

I encountered this other scenario where the solution was unintuitive.

table: FactRegistration with 300m records, 30 columns,  RegDate is date column

below is view definition:

SELECT <columns>
FROM FactRegistration fr
WHERE FR.RegDate < CAST(DATEADD(DAY,-7,GETDATE()) AS DATE)

query:

SELECT <columns>
INTO #tmp
FROM edw.dbo.vwDemo_slow fr
WHERE fr.RegDate >= CAST('20140501' AS DATE)
    AND fr.RegDate <= CAST('20140531' AS DATE)

Results in this plan (https://gist.github.com/gflores1023/f0f0089315841d21ab072837cf12145d):

If I change the view definition to use this WHERE clause instead:

WHERE FR.RegDate < CAST(CAST(DATEADD(DAY,-7,GETDATE()) AS DATE) AS DATETIME)

I get a much better plan (https://gist.github.com/gflores1023/e3904609c98babbbbc646eaec76ebba4):

Running SQL Server 2016 SP1

I would suggest:

change the unrecommended shorthand getdate()-7 to the proper and explicit CONVERT(datetime2(2), DATEADD(DAY, -7, GETDATE()))
avoiding SELECT * everywhere, especially in the view
change your predicate to an open-ended range (>= July 1 and < August 1)
explicitly convert literals to avoid implicit conversions
avoid regional, unsafe formats like yyyy-mm-dd

So:

WHERE SpendDate >= CONVERT(datetime2(2), '20180601')
  AND SpendDate <  CONVERT(datetime2(2), '20180701');

Not only does this guard against both implicit conversions and ydm interpretation, it is much easier to find the beginning of the next month than the end of the current one.


Query to retrieve SQL Server login password length I've been asked by corporate security to review our Azure SQL Databases and find out if there are any SQL Logins which have "short" passwords. I am not aware of a way to achieve this - I know I could alter the login and force them to use the windows password policy - but wouldn't this have the side effect of disabling the logins and making our applications fail ?
Does anyone have a suggestion - TSQL or Powershell preferably

I think, your corporate security meant by week passwords. One way of doing that (without any third-party tools) with help of in-built function PWDCOMPARE, but for this you need to have real week-passwords list available perhaps in database table from where every password looked-up and compared with SQL Login hashes one-by-one. following is the example:
Declare @pwText nvarchar(128);
Declare @WeekPassword_List table (pw nvarchar(128));
Declare @WeekPasswords_Active table (login_name sysname, pw nvarchar(128));

insert into @WeekPassword_List
values
('password'),
('passwordpassword'),
('password123'),
('P@ssw0rd'),
('Pa55word'),
('Pa55w0rd'),
('Temp@password'),
('Temp123'),
('temp123'),
('temp@123'),
('Temp@123'),
('123'),
('1234'),
('123456'),
('12345678'),
('');

while exists (select 1 from @WeekPassword_List)
begin
    SET @pwText = (select top 1 pw from @WeekPassword_List);

    INSERT INTO @WeekPasswords_Active
    SELECT name, @pwText
    FROM   sys.sql_logins
    WHERE  Pwdcompare(@pwText, password_hash) = 1;

    DELETE FROM @WeekPassword_List where pw = @pwText;
end

select * from @WeekPasswords_Active;


PostgreSQL: schema diff/patch tool Consider the following setting:

A production DB
A dev db, on which schema changes are made to enable new features

When the development of a new feature is complete, I have to manually update the prod db schema until pg_dump --schema-only on both DBs is identical. This process is error prone and tedious.

So, I am looking for a tool that can:

Show a summary of the differences between two schemas (like diff). Note that I am not looking for a mere textual diff of the schema, but a more elaborate tool that can draw conclusions like "Table X has a new column Y".
Auto-generate the SQL code that would convert one schema to another (like patch)

Is there a schema diff/patch tool that can help me convert prod schemas to the more advanced dev schemas?

Sorry to resurrect an old question

Recently I have been using the 0xDBE DataGrip Database management tool by JetBrains.

It supports multiple database engines, in the excellent Jetbrains IDE, and a key feature I have found useful is the ability to diff 2 tables (DEV and PROD).

Below is a screenshot of the diff in action (in this case there is only one column difference). The screenshot is the result of the "Merge Right" button at the top, generating the SQL required to bring the right table up to scratch.

Hope this new tool helps.


MySQL Slave SQL Running State - “Waiting for dependent transaction to commit” meaning Quite often the Coordinator thread on our replica database has the status "Waiting for dependent transaction to commit".
I'm a little confused as to what this means, and I can't see this status in the list of thread states in the MySQL documentation.
I'm mainly curious if the "dependent transaction" is a transaction currently running on one of the worker threads on the replica (as in a table is potentially locked), or if the "dependent transaction" is something occurring on the Master that the replica is waiting for?

If slave_parallel_workers is changed from default 0 value then a number of events applier threads will be launched. Independent events are applied in parallel. If some event is dependant on another one (say INSERT and then an UPDATE to the same row), an UPDATE event will be applied sequentially after the INSERT. Threads dependant on some other event becomes in "Waiting for dependent transaction to commit" state.
This is not a problem but rather a sign that some queries are long enough to create the trailing queue of dependant queries. Some optimization may be requiered but that is not mandatory.


How do we restore database configured with Always Encrypted between environments and enable web app to encrypt/decrypt data? I have a SQL Server 2016 database configured with AE in Production environment. Column Master key Certificate is available(under Windows Certificate Store -> Local Machine location) in a different server other than database server and the web server hosting ASP.NET application also has the Certificate to encrypt/decrypt data.

For troubleshooting or operational support purpose, we usually backup and restore this Production database to our staging/UAT environment on need basis.

After restoring the database, web app is NOT working as expected since it's missing the Certificate used to configure AE in Production environment.

As per the security policy, client is NOT willing to bring the AE Certificate from Windows Certificate Store to the target environment.

In this regard, what are the best practices to be followed to make the web app working when we restore database from one environment to another? Or is there any other approaches to make the web app working without bringing the Certificate?

See this description of how Always Encrypted works. Basically, SQL Server does not have access to the keys required to decrypt data into plain text. This work is performed by the client driver and in your scenario, the client driver will go looking for the certificate to decrypt the data for the web application but on failing to find the correct certificate, the data will not be decrypted.

This won't cause a failure in itself, as you can see from this example if you don't have column encryption support enabled in the driver you still get results back, but when that data isn't decrypted the client driver simply returns the encrypted binary value.

It is likely, however, that your web application is relying on unencrypted data to do more than simply present a result set, and for this, you will need the certificate. If the client refuses to allow the certificate to be shared in lower environments, then you cannot use restored database copies from production in your non-production environments.


How do I debug an Idle Query? I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on.

The query is an aggregation on a table that is simultaneously getting inserted, which I'm guessing somehow relates to the issue. (The aggregation is on the previous days data, so the insertions shouldn't affect results.)

Clues

I'm running this inside a python script using sqlalchemy. However, I've set transaction level to autocommit, so I don't think things are getting wrapped inside a transaction. On the other hand, I don't see the query hang when I run it manually in sql terminal.
By querying pg_stat_activity, the query initially comes into the database as state='active'. After maybe 15 seconds, the state changes to 'idle' and additionally, the xact_start is set to NULL. The waiting flag is never set to true.
Before I figured out the transaction level autocommit for sqlalchemy, it would instead hang in state 'idle in transaction' rather than 'idle'. And it possibly hangs slightly less frequently since making that change?

I feel like I'm not equipped to dig any deeper than I have on this. Any feedback, even explaining more about different states and relevant postgres internals without giving a definite answer, would be greatly appreciated.

First thing you have to separate here are the words query, transaction and connection.

Clue: your query gets executed - it is in the active state. After that the query ends but the connection stays on - the idle state. There is no transaction (it has been committed) so the xact_start is null. So you have to close the connection after the query is succesful.
Clue: Before the autocommit was on the query was left in the middle of the transaction so first you would have to commit and then close connection.


Best way to model the relationship of unique pairs I have two tables; one for storing thing and one for storing the relationship between two thing objects.

dbfiddle example

Assume:

AB == BA.  Storing both would be redundant
A != B.  The relationship of one thing to itself is not useful
Calculating the relationship between AB is expensive but idempotent

CREATE TABLE thing (
    id INT PRIMARY KEY
);
CREATE TABLE relationships (
    thing_one INT REFERENCES thing(id),
    thing_two INT REFERENCES thing(id),
    relationship INT NOT NULL,
    PRIMARY KEY (thing_one, thing_two),
    CHECK (thing_one != thing_two)
);

In order to ensure we don't INSERT AB and BA:

CREATE UNIQUE INDEX unique_pair_ix
    ON relationships (
        least(thing_one, thing_two),
        greatest(thing_one, thing_two)
    );

Is there a better or more efficient way to store/model this data than the example?

EDIT: There are multiple DBMS being considered for the greater application.  They include PostgreSQL, MariaDB, and MySQL.  PostgreSQL is the current preference.

This may not be needed and I am not totally sure I have the best answer.  I was not able to come up with anything that utilized some kind of hash or other type of comparison mechanism.  I am also running in SQL Server so I don't have the ability to use a least() and greatest() function.  See this DBA Stack Exchange Question.
I have however done some performance testing with a few different methods.  What data I captured is below:
+---------------------------------------------------------+-----------+--------------------+--------------------+--------------+----------------+-----------------------------+
|                                                         |           | Instead of Trigger | Instead of Trigger |              | After Trigger  | Sum and Absolute Difference |
|                          Event                          | Baseline  |  Case Statements   |     Not Exists     | Indexed View | Only Completed | Computed Persisted Columns  |
+---------------------------------------------------------+-----------+--------------------+--------------------+--------------+----------------+-----------------------------+
| Execution Time                                          | 07:06.510 | 13:46.490          | 08:47.594          | 22:18.911    | 30:38.267      | 11:24:38                    |
| Query Profile Statistics                                |           |                    |                    |              |                |                             |
|   Number of INSERT, DELETE and UPDATE statements        | 125250    | 249500             | 499000             | 250000       |                | 249999                      |
|   Rows affected by INSERT, DELETE, or UPDATE statements | 124750    | 249500             | 374250             | 124750       |                | 124750                      |
|   Number of SELECT statements                           | 0         | 0                  | 0                  | 0            |                | 0                           |
|   Rows returned by SELECT statements                    | 0         | 0                  | 0                  | 0            |                | 0                           |
|   Number of transactions                                | 125250    | 249500             | 499000             | 250000       |                | 249999                      |
| Network Statistics                                      |           |                    |                    |              |                |                             |
|   Number of server roundtrips                           | 1         | 250000             | 250000             | 250000       |                | 250000                      |
|   TDS packets sent from client                          | 6075      | 250000             | 250000             | 250000       |                | 250000                      |
|   TDS packets received from server                      | 462       | 250000             | 250000             | 250000       |                | 250000                      |
|   Bytes sent from client                                | 24882190  | 62068000           | 62568000           | 59568000     |                | 61567990                    |
|   Bytes received from server                            | 1888946   | 76910970           | 8782500            | 67527710     |                | 69783720                    |
| Time Statistics                                         |           |                    |                    |              |                |                             |
|   Client processing time                                | 420901    | 269564             | 18202              | 240341       |                | 238190                      |
|   Total execution time                                  | 424682    | 811028             | 512726             | 1325281      |                | 665491                      |
|   Wait time on server replies                           | 3781      | 541464             | 494524             | 1084940      |                | 427301                      |
+---------------------------------------------------------+-----------+--------------------+--------------------+--------------+----------------+-----------------------------+

This data would suggest one of 3 options:

Assuming you are ok with the use of an IDENTITY column as the Primary Key, then the best performing method appears to be the INSTEAD OF Trigger - NOT EXISTS.
Assuming you are not ok with the use of an IDENTITY column as the Primary Key, and you are ok with the existence of 2 other Persistent Computed Columns then the best performing method appears to be the Persistent Computed Columns.
Assuming you are not ok with the use of an IDENTITY column as the Primary Key, and you are not ok with the existence of 2 other Persistent Computed Columns then the best performing method appears to be the INDEXED VIEW

Background on the overall testing method
I created a thing table with the same schema as you provided, and populated it with each INT from 1-500.  Then I scripted out a bunch of single INSERT statements for each combination of thing (a GO was utilized so that when a given entry failed for either of the checks that the rest of the script would run and we could collect the statistics for the entire process).
INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(1, 1, 1 * 1)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(1, 2, 1 * 2)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(1, 3, 1 * 3)
GO
...
INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(500, 498, 500 * 498)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(500, 499, 500 * 499)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(500, 500, 500 * 500)
GO

Done correctly this results in 124,750 total records added from 250,000 total attempts.
This process was repeated for 4 different methods to see what performance was like.  I also ran a "baseline" query which only had the INSERT statements for the unique combinations.  That way we had some idea on how fast it could possibly get in the framework that the test is setup.
Details on Each Method

INSTEAD Trigger - Case expressions to sort input values for thing_one and thing_two.

With this implementation we were going to take the provided data, ensure that the smaller value of thing_one and thing_two was ultimately placed into the thing_one column and the other (the larger of the two) was placed into the thing_two column.  From there the Primary Key would ensure only the unique values would remain.
NOTE: Because with this implementation we are doing uniqueness on the Primary Key (which I think is the correct table structure), it is difficult to handle UPDATE statements via an INSTEAD Trigger.  This was asked in a previous Stack Overflow Question and the basic outcome is that you either need a different method, or an Identity column.  I don't personally think it is a great idea to add an Identity column if there is a natural primary key as it sounds like you have here.
The Trigger Code:
CREATE TRIGGER InsteadOfInsertTrigger on [dbo].[relationship]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[relationship]
(
    thing_one,
    thing_two,
    relationship
)
SELECT
CASE
    WHEN I.thing_one <= I.thing_two
        THEN I.thing_one
    ELSE
        I.thing_two
    END
,CASE
    WHEN I.thing_one <= I.thing_two
        THEN I.thing_two
    ELSE
        I.thing_one
    END
,I.relationship
FROM inserted I
GO

INSTEAD Trigger - NOT EXISTS check

This is a trigger that stopped the INSERT if the relationship already existed.  The values going into thing_one and thing_two are not sorted, but hopefully that isn't a problem.  Just like with the previous Trigger this still has the same pitfall when it comes to UPDATES.
The Trigger Code:
CREATE TRIGGER InsteadOfInsertTrigger on [dbo].[relationship]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[relationship]
(
    thing_one,
    thing_two,
    relationship
)
SELECT
I.thing_one
,I.thing_two
,I.relationship
FROM inserted I
WHERE NOT EXISTS
(
    SELECT 1
    FROM [dbo].[relationship] t
    WHERE (t.thing_one = i.thing_two AND t.thing_two = i.thing_one)
    --This one shouldn't be needed because of the Primary Key
    --AND (t.thing_one = i.thing_one AND t.thing_two = i.thing_two)
)
GO

Unique Indexed View

With this method, we created a View and put a Unique Clustered Index on it.  If a duplicate record is added then it fails this check and the change is rolled back.  I saw 2 ways of doing this, either with a CASE expression like I have below, or some kind of UNION.  In my testing the CASE performed much better.
The View and associated Index Code:
CREATE VIEW dbo.relationship_indexedview_view
WITH SCHEMABINDING
AS
    SELECT
    CASE
        WHEN thing_one <= thing_two
            THEN thing_one
        ELSE
            thing_two
        END as thing_one_sorted,
    CASE
        WHEN thing_one <= thing_two
            THEN thing_two
        ELSE
            thing_one
        END as thing_two_sorted
    FROM [dbo].[relationship_indexedview]
GO

CREATE UNIQUE CLUSTERED INDEX relationship_indexedview_view_unique
    ON dbo.relationship_indexedview_view (thing_one_sorted, thing_two_sorted)
GO

AFTER INSERT and UPDATE Trigger

Here we have another TRIGGER implementation that will handle both INSERT and UPDATES.  After the INSERT or UPDATE has finished, it checks to see if a duplicate value has been added, and performs a ROLLBACK if it finds one.
NOTE: This method performed super poorly.  I stopped it after ~30 minutes of running and it only added 51,378 of the expected 124,750 rows (~24% of the INSERT commands executed).
Trigger Code:
CREATE TRIGGER AfterTrigger ON [dbo].[relationship]
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[relationship] T1
            INNER JOIN [dbo].[relationship] T2
                ON T1.thing_one = T2.thing_two
                AND T1.thing_two = T2.thing_one
    )
    BEGIN
        RAISERROR ('Duplicate Relationship Value Added', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert/Update
    END
END
GO

Sum and Absolute Difference Comparison using Physical Computed Columns

After getting confirmation from this Math Stack Exchange Question.  We know that a given relationship (thing_one, thing_two) or (thing_two, thing_one) can be tested as unique by looking at the sum and the absolute value of their difference.  We can create 2 Computed Persisted Columns and create a Unique Constraint.
With a small modification to the table schema we can ensure the uniqueness without having to modify the INSERT script.
The only downside is having to maintain 2 more columns on the table. As long as that is ok, this appears to be one of the smallest amounts of overhead and does not have the same pitfalls of having to deal with the Primary Key changes, as found with the TRIGGER based methods.
This presumably could be pushed to a separate table, or some other indexed view, but I have not done any testing with that.
CREATE TABLE relationships (
    thing_one INT REFERENCES thing(id),
    thing_two INT REFERENCES thing(id),
    thing_one_thing_two_sum AS thing_one + thing_two PERSISTED,
    thing_one_thing_two_absolute_difference AS ABS(thing_one - thing_two) PERSISTED,
    relationship INT NOT NULL,
    PRIMARY KEY (thing_one, thing_two),
    CHECK (thing_one != thing_two),
    UNIQUE(thing_one_thing_two_sum, thing_one_thing_two_absolute_difference)
);

Hopefully this helps with the design decision or is at least an interesting read.


Custom Postgres function not working I wrote this function that is supposed to iterate through a text array and convert each element (which is a typecasted integer) to hex, append to a text variable, then return the hex. However, I get nothing back.

Does anybody see any issues with the function that would cause it not to return anything?

CREATE OR REPLACE FUNCTION array_convert_to_hex(text[])
RETURNS text
AS
$$
DECLARE
   arrInts ALIAS FOR $1;
   retVal text;
BEGIN
   FOR I IN array_lower(arrInts, 1)..array_upper(arrInts, 1) LOOP
    retVal := retVal || lpad(cast(to_hex(arrInts[I]::integer) as varchar), 2, '0');
   END LOOP;
RETURN retVal;
END;
$$
LANGUAGE plpgsql
   STABLE
RETURNS NULL ON NULL INPUT;

Peter's quite right about the problem, of course. However, this entire function is unnecessarily slow and complex - that repeated string concatenation will be horrible for performance, and PL/PgSQL loops are best avoided when you can anyway.

You can do the same job with an ordinary SQL function using generate_subscripts:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT
  string_agg(
    lpad(to_hex($1[x]),2,'0'),
    ''
    order by x
  )
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;

The above version expects an integer array, but you can accept text arrays with an overload:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(text[]) RETURNS text AS $$
SELECT intarr_to_hex_string($1::integer[])
$$ LANGUAGE sql;

Note that no sanity check is done to make sure the hex string is two digits or less. You should really add a check where you use a CASE statement to execute a PL/PgSQL function that raises an error if there's bad input. Here's a complete example:

CREATE OR REPLACE FUNCTION raise_exception(text)
  RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
   RAISE EXCEPTION '%', $1;
END;
$BODY$;

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT
  string_agg(lpad(
    CASE WHEN $1[x] < 256 THEN
      to_hex($1[x])
    ELSE
      raise_exception('Argument to intarr_to_hex_string contained value '||coalesce($1[x]::text,'NULL')||', expected all values in i[] to be 0 <= i < 256. Full argument was '||quote_literal($1)||'.')::text
    END,
    2, '0'),
  ''
  order by x)
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;


Shrinking and consolidating to only one log file I've restored a database from production to QA, on SQL Server 2012. One of the databases has two log files:

logfile.ldf - 6 GB
logfile2.ldf - 200 GB

Due to space issues and data not being truncated through the years, there is data dating back from 10 years ago. However, I would like to only retain data from 2015 onwards.

I did the following:

Set Recovery Mode to Simple;
Issued DBCC SHRINKFILE multiple times in this order:

200 GB > 100 GB > 50 GB > 25 GB > 12 GB

DBCC SHRINKFILE(EMS_1_4_3_log, 12288); -- unit is set in MBs

Used a table for min and max dates and the dates remain the same.

Questions

Although the shrink was successful, I believe I can shrink it more but I'll stop here. How would I know that it shrunk efficiently?

I didn't use TRUNCATEONLY, should I have used this?

  TRUNCATEONLY

  Releases all free space at the end of the file to the operating system
  but does not perform any page movement inside the file. The data file
  is shrunk only to the last allocated extent. target_size is ignored if
  specified with TRUNCATEONLY. The TRUNCATEONLY option does not move
  information in the log, but does remove inactive VLFs from the end of
  the log file. This option is not supported for FILESTREAM filegroup
  containers.

What does it mean that "it does not move information in the log"?
To get the data dates back to retention period, do I truncate data in the tables themselves? What's a better way of doing this? I would lose the LSN, which we don’t care right now, but I still would like to recover from the retention dates.
How do I consolidate multiple logfiles?

I'd consolidate the log files first, before worrying about the right size to set the log.

To do that, you would need to use the EMPTYFILE clause in a DBCC SHRINKFILE operation, so that the second file can be emptied out and then deleted.

Once you are back to a single log file, you can then shrink right back down to zero (to remove any fragmentation), and then resize it appropriately, to ensure good distribution of Virtual Log Files (VLFs) inside the transaction log file.

There's no way to know what the "right" size is for your log file until you run a normal workload against the database, and see how big it gets. Once you know that, you shrink it down again, and then resize back to that value.

Make sure you set a fixed growth setting appropriate for the performance of your underlying I/O subsystem, and not a percentage file growth. You might want to set it to 100 MB, or 1 GB. It's entirely up to you.

Regarding TRUNCATEONLY, all it does is free any inactive portion, at the end of the transaction log file, back to the operating system. No data inside the log is moved. If there's no inactive portion of the log at the end of the file, no space will be released.

The Simple Recovery Model does not allow point-in-time recovery, but I think you know that. If you want to do point in time recovery, you'll need the Full Recovery Model, and perform regular transaction log backups.


Basic Question on input to PostgreSQL I have installed PostgreSQL on a Windows XP laptop so I can learn about using PostgreSQL. My problem is that the PostgreSQL tutorial starts of with the instruction

To create a new database, in this example named mydb, you use the following command:

$ createdb mydb

My problem is that it doesn't tell me how/where to input the command.  I have found access to an SQL Shell but it doesn't perform as expected.

What is the 'proper' way to input commands?

No GUI ?

createdb is a commandline tool which should be run in a Windows commandline window ("Start -> Run -> "cmd.exe"). You have to have PostgreSQL's bin directory in the windows PATH in order for this to work without supplying the full program path.

As an alternative you can run SQL statements using the program psql (which is also a command line program).  You probably also have a start menu entry in the PostgreSQL folder name "SQL Shell" (or something similar) which starts psql directly.

A list of GUI tools (apart from pgAdmin) is available in the Community Guide to PostgreSQL GUI Tools.


Decode URL encoded characters in inline table valued function? I'm looking for a inline table valued function that decodes URL encoded characters (like %20 for space). reference

I see other functions but haven't seen one that is an inline table valued function. I'm tempted in just writing a bunch of REPLACE statements in a function but I'm wondering if there's a better way.

CREATE FUNCTION dbo.itvfURLDecode
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (
    SELECT REPLACE(REPLACE(@StringValue,'%2B','+'),'%20',' ') /* etc..*/ AS DecodedValue
)

;WITH cteData AS
(
    SELECT 'This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25' AS example
)
SELECT
    example, q.DecodedValue  /* returns: This+is a test<> ok? 100% */
FROM cteData c
CROSS APPLY dbo.itvfURLDecode(example) q

While I'm not sure about the performance for you particular needs, here is an example of using a TranslationTable CTE along with a recursive CTE which will find and replace values in the incoming string to the TVF.

DROP FUNCTION IF EXISTS dbo.itvfURLDecode
go
CREATE FUNCTION dbo.itvfURLDecode
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT x.FindValue,x.ReplaceValue,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM (SELECT NULL AS a) a
    CROSS APPLY (
        --list of Find/Replace values
        VALUES ('%2B','+')
            ,('%20',' ')
            ,('%3C','<')
            ,('%3E','>')
            ,('%3F','?')
            ,('%25','%')
        ) x(FindValue, Replacevalue)
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

--Verify translation
SELECT *
FROM dbo.itvfURLDecode('This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| StrValue                  | cnt |
|---------------------------|-----|
| This+is a test<> ok? 100% | 0   |

You can actually see the progression of the recursive CTE if you comment out the --where cnt = 0 towards the bottom of the TVF

| StrValue                                    | cnt |
|---------------------------------------------|-----|
| This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25 | 6   |
| This%2Bis%20a%20test%3C%3E%20ok%3F%20100%   | 5   |
| This%2Bis%20a%20test%3C%3E%20ok?%20100%     | 4   |
| This%2Bis%20a%20test%3C>%20ok?%20100%       | 3   |
| This%2Bis%20a%20test<>%20ok?%20100%         | 2   |
| This%2Bis a test<> ok? 100%                 | 1   |
| This+is a test<> ok? 100%                   | 0   |


Deleting rows from Oracle table user_scheduler_job_run_details Oracle Database 12c
Red Hat Linux 7

I want to delete the last 1000 records on the Oracle table user_scheduler_job_run_details because it is getting too large.
How best to delete rows from the Oracle table user_scheduler_job_run_details?
Are any precautions needed?
Is it simply a matter of doing a DELETE FROM ... DML statement?
Is there an existing job or procedure that handles this?

You can specify it in days, not number of rows.

DBMS_SCHEDULER.PURGE_LOG

  By default, the Scheduler automatically purges all rows in the job log
  and window log that are older than 30 days. The PURGE_LOG procedure is
  used to purge additional rows from the job and window log.

  log_history

  This specifies how much history (in days) to keep. The valid range is
  0 - 1000000. If set to 0, no history is kept.

DBMS_SCHEDULER.PURGE_LOG (
   log_history             IN PLS_INTEGER  DEFAULT 0,
   which_log               IN VARCHAR2     DEFAULT 'JOB_AND_WINDOW_LOG',
   job_name                IN VARCHAR2     DEFAULT NULL);


The PostgreSQL database cannot start after modifying the IP The following is the operation process:
[root@freelab ~]# su - postgres
Last login: Sat Jul  4 10:16:58 EDT 2020 on pts/1
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /postgres -l logfile start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$
-bash-4.2$ date
Sat Jul  4 10:35:13 EDT 2020

But the log does not seem to be updated accordingly
[root@freelab log]# tail -f postgresql-Sat.log
2020-07-04 09:23:14.930 EDT [1832] LOG:  background worker "logical replication launcher" (PID 1840) exited with exit code 1
2020-07-04 09:23:14.930 EDT [1835] LOG:  shutting down
2020-07-04 09:23:14.943 EDT [1832] LOG:  received SIGHUP, reloading configuration files
2020-07-04 09:23:14.943 EDT [1832] LOG:  parameter "data_directory" cannot be changed without restarting the server
2020-07-04 09:23:14.944 EDT [1832] LOG:  configuration file "/postgres/postgresql.conf" contains errors; unaffected changes were applied
2020-07-04 09:23:15.052 EDT [1832] LOG:  database system is shut down

When restarting the server with a specific logfile (see your restart command: /usr/pgsql-12/bin/pg_ctl -D /postgres -l logfile start) you need to look at that specific logfile, not at postgresql-Sat.log.
Moreover, this line in the previous log shows that you modified the data directory:

2020-07-04 09:23:14.943 EDT [1832] LOG:  parameter "data_directory" cannot be changed without restarting the server

Presumably log_directory is relative to the data directory, so after this change you need to look at the new location now to find the new logs.
From the documentation:

log_directory (string)
When logging_collector is enabled, this parameter determines the directory in which log files will be created. It can be specified as
an absolute path, or relative to the cluster data directory. This
parameter can only be set in the postgresql.conf file or on the server
command line. The default is log.


Generate an exception with a Context When PostgreSQL throws an exception, there is a line "CONTEXT" like:

    ERROR:  INSERT has more target COLUMNS than expressions
    LINE 3: ...
                                                         ^
    QUERY:  INSERT INTO ...
    CONTEXT:  PL/pgSQL FUNCTION "XXXXX" line 4 at SQL statement

But when I throw an exception, this line is not there. I didn't find how to add it.

    RAISE EXCEPTION 'blablabla' USING HINT = 'blablablabla';

Is it possible to add this line to my exception?

There is no direct way to output the the CONTEXT line with a user-defined exception. This option is not implemented (yet) in PostgreSQL 9.1.
However, I found a ...
Workaround
... that should perform flawlessly. Call another function that raises the error (warning, notice, ..) for you. Optionally with custom message and CONTEXT. Works with PostgreSQL 9.0 or later:
CREATE OR REPLACE FUNCTION f_raise(_lvl text = 'EXCEPTION'
                                 , _msg text = 'Default error msg.')
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE upper(_lvl)
      WHEN 'EXCEPTION' THEN RAISE EXCEPTION '%', _msg;
      WHEN 'WARNING'   THEN RAISE WARNING   '%', _msg;
      WHEN 'NOTICE'    THEN RAISE NOTICE    '%', _msg;
      WHEN 'DEBUG'     THEN RAISE DEBUG     '%', _msg;
      WHEN 'LOG'       THEN RAISE LOG       '%', _msg;
      WHEN 'INFO'      THEN RAISE INFO      '%', _msg;
      ELSE RAISE EXCEPTION 'f_raise(): unexpected raise-level: "%"', _lvl;
   END CASE;
END
$func$;

COMMENT ON FUNCTION f_raise(text, text) IS 'Raise error or given level with msg and context.
Call from inside another function instead of raising an error directly
  to get plpgsql to add CONTEXT (with line number) to error message.
$1 .. error level: EXCEPTION | WARNING | NOTICE | DEBUG | LOG | INFO
$2 .. error message';

Use the function to raise an error like this:
CREATE OR REPLACE FUNCTION test_err(text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- do stuff

   IF TRUE THEN  -- some condition here?
      -- instead of raising error like this:
      -- RAISE EXCEPTION 'unexpected parameter: "%"', $1;
      PERFORM f_raise('EXCEPTION', 'My message "' || $1 || '"');
   END IF;
END
$func$;

Call:
SELECT test_err('wrong parameter');

The function comes with default values, that kick in when missing in the call. In combination with named parameters, you can do pretty much anything. Examples:
SELECT f_raise();
SELECT f_raise('WARNING');
SELECT f_raise(_msg := 'boohoo');
SELECT f_raise(_lvl := 'WARNING');


Use Netbackup 7.7 `WITH KEEP_CDC` I'm using SQL Server Change Data Capture in a production database. To my surprise, I found that when restoring that database to a lower environment the CDC data is not kept—all the tables, functions, and so on are gone because SQL Server removes the CDC information during the recovery phase of a restore to any other database besides the one that originated the backup.

I learned that there is a RESTORE option, KEEP_CDC, that can be used in conjunction with the RECOVERY option (as the last step of a restore chain) to prevent the CDC information from being removed.

  Transact-SQL REFERENCE: RESTORE Arguments

  KEEP_CDC

  Supported by:  RESTORE

  KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.

Our organization uses NetBackup as its primary way to backup and restore databases. Getting ahold of a backup file outside of that is not something that will be easy to do and likely could not ever be routine. We have a web site that lets developers restore production backups to other environments without having to touch the files themselves.

Question: So what do I really need—to find out if there is a way in NetBackup 7.7 to specify additional restore options so that WITH_CDC can be passed to the RESTORE command? Or does NetBackup not use a true RESTORE statement? It seems like it would have to somehow use the built-in SQL Server restore functionality, otherwise it couldn't trigger the recovery phase of a restore properly. Or maybe I'm just out of my depth, here.

I can't find much information on NetBackup's abilities, and have been assured by a Senior Database Engineer that NetBackup 7.7 can't do this. However, I'm wondering how deeply he searched and whether someone perhaps more intimately familiar with the product might know of a way to hack such a thing in. Apparently version 8.0 is coming out soon-ish, but again I can't seem to find online a solid documentation source for how this might be accomplished. (It seems like "additional command-line arguments" could be possible, even if the option is not explicitly supported through a named switch or other setting.)

https://www.veritas.com/support/en_US.html

you'll have to find the SQL Agent Guide for that version of Netbackup and see what it supports. Technically you need 7.5 to backup SQL 2012 but I think that version supports only the bare features. I think they only introduced some of the advanced features in version 8, but I can't say with 100% certainty. You will need to find the doc for your version and check to see what it supports


What steps are required to setup PostgreSQL and create a db in Windows? I have installed PostgreSQL 12 from https://www.2ndquadrant.com/en/blog/pginstaller-install-postgresql/. and put \C:\...\PostgreSQL\12\bin in the PATH.
In an admin powershell I execute psql.exe -U postgres and enter the password I chose in the installation wizard. Now I'm in a shell. I want to create a database so I run createdb but it seems the database is not created:
postgres-# \l
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
(3 rows)

postgres-# createdb demo
postgres-# absolute nonsense
postgres-# \l
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
(3 rows)

I assume that my command createdb demo fails without error response as the intentionally wrong next command absolute nonsense fails as well. But I don't know how else to create a db, and moreover I'd like to know why this doesn't work and how to debug this.

In the psql client, you can only run SQL statements and psql commands (which start with a backslash).
So, to create a database, you would have to run
CREATE DATABASE demo;

The semicolon in the end is required to tell psql that the statement is complete.

The prompt you see:
postgres-#

shows that you didn't do that and psql is waiting for a continuation line. You see that from the -.
To delete the input buffer and get back to the prompt
postgres=#

simply press Ctrl+C. Then you can enter the CREATE DATABASE statement.

createdb is not an SQL statement, but an executable (program) that you would call from the shell (or from cmd.exe on Windows).
So the alternative is not to start psql, but type
createdb -U postgres demo


How to get rid of a Bitmap Heap Scan if the table already has the proper indices I am running PostgreSQL 9.6. These are the relevant definitions:

CREATE TABLE IF NOT EXISTS instagram.profiles_1000 (
    id                          SERIAL PRIMARY KEY,
    username                    VARCHAR(255) NOT NULL UNIQUE,
    followers                   BIGINT,
    tsv                         TSVECTOR
);

CREATE UNIQUE INDEX IF NOT EXISTS instagram_username_index
    ON instagram.profiles_1000(username);
CREATE INDEX IF NOT EXISTS instagram_followers_index
    ON instagram.profiles_1000(followers);
CREATE INDEX IF NOT EXISTS instagram_textsearch_index
    ON instagram.profiles_1000 USING GIN(tsv);

And the text vector is updated by a trigger:

CREATE FUNCTION instagram_documents_search_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
        setweight(to_tsvector(COALESCE(new.username, '')), 'D') || ' ' ||
        setweight(to_tsvector(COALESCE(new.full_name, '')), 'C') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_country, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_region, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.biography, '')), 'A') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_city, '')), 'A');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER instagram_tsvectorupdate BEFORE INSERT OR UPDATE
    ON instagram.profiles_1000 FOR EACH ROW
    EXECUTE PROCEDURE instagram_documents_search_trigger();

This is the query:

select instagram.profiles_1000.*, categories, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
left outer join instagram.profile_categories_agg on instagram.profiles_1000.username = instagram.profile_categories_agg.username
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

This is the output of EXPLAIN (ANALYZE, BUFFERS):

https://explain.depesz.com/s/ceCd

The culprit is the Bitmap Heap Scan which makes up the bulk of the total execution time. Frankly I don't understand why it's needed, especially since the Bitmap Index Scan on instagram_textsearch_index already filters the rows according to the search term.

Can someone shed some light?

EDIT It was pointed out that I misread the explain output. Indeed, the left outer join was taking a lot of time. I tried to remove it as follows:

select instagram.profiles_1000.*, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

But the query still takes 13 seconds! This is the EXPLAIN (ANALYZE, BUFFERS) output:

https://explain.depesz.com/s/awfH

Now the bottleneck seems to be the full-text search. Is it really that slow? The table has just 5 million rows, and the tsv (which has type TSVECTOR) is indexed by the following index:

CREATE INDEX IF NOT EXISTS instagram_textsearch_index_1000
    ON instagram.profiles_1000 USING GIN(tsv);

EDIT 2 I realized that I can write a leaner query if I only process the profiles that match the search (which are always 50 at most). Using this query:

select p.*, categories
from
    (select id
    from instagram.profiles_1000, plainto_tsquery('arts') as q
    where q @@ tsv and followers is not null and followers > 0
    order by (followers, -id) desc
    limit 50) as ids
inner join instagram.profiles_1000 as p on
    p.id = ids.id
left outer join instagram.profile_categories_agg as c on
    c.username = p.username;

I am able to obtain this result:
https://explain.depesz.com/s/OvG

Which puts the search at ~3 seconds. It would be nicer to reach 1 second at least though.

The "culprit" here takes up less than 1/4 of the total time.  The real bottleneck is Index Only Scan using instagram_categories_username_category_agg, which takes 0.200 * 118453 = 23690.6 ms, almost 24 seconds, which is most of the time.

It looks like every user has exactly one category, unless that is just an amazing coincidence, so why is there a separate table for user category rather than it being an attribute of profiles_1000?  That design appears to be the true culprit.

Anyway, the reason it has to do the Bitmap Heap Scan is because that is how it arrives at the correct answer.  If it only did the Bitmap Index Scan, it wouldn't have any data about the matching rows, just their address.  And it also wouldn't know if that address was really to a matching row, because of visibility, rechecks, and lossy bitmap compression.

Regular index scans also visit both the index and the table, they just don't separate out those operations as two different entries in the EXPLAIN plan the way a bitmap scan does.


powershell Write-Output format I am finding what seems to be an inconsistency to the way a Write-Output line (which prints out a variable) should be formatted. I can't find any information on this, which makes me guess that I am misunderstanding what I am seeing.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Statement:

For most simple variables, the below will work:

Write-Output " This $testValue will print just fine."

However, I need to add a $() to the Write-Output line if the variable is part of an object:

Write-Output $("Removing old software from directory: " + $site.INSTALLDIR)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Question: Is the above statement correct?

When you want to print object property values, then it should be used with special syntax $() shown as below:-

Write-Output "Removing old software from directory: $($site.INSTALLDIR)"

In case you want to print the variable name as well, put a backtick before the variable usage. Example:-

Write-Output "Removing old software from directory (`$site.INSTALLDIR): $($site.INSTALLDIR)"


Encrypt MySQL database on a Linux VPS? I'm working on a rails app for our church, which stores some fairly sensitive information about our people in a MySQL database.

We have our own windows server running Hyper-V so the original intention was to spin up a VM and host on there.

Long term this sounds like the best solution, but at the moment our church has fairly poor internet connectivity (home ADSL) and I don't want to waste time now maintaining infrastructure and talking to service providers when I could be putting more time and energy into building a great app.

In this initial period I'd like to host our app on a VPS with Linode, Rackspace or Amazon.

Our leadership's concern (quite rightly) is security of our people's data, and while I can assure them all communication will be SSL encrypted over HTTPS they are still concerned about storing our data with a 3rd party.

In the scheme of things our data isn't a prime target for attackers, but I'd like to be able to give them the assurance that even if our server was wide open for any member of the public to log in and browse around the data would remain secure.

Is this possible?
Can a MySQL database be encrypted on disk to this level, but stil be accessible over a MySQL connection (via a socket or TCP port)?

On second thoughts I'm thinking it'd be a bit redundant anyway as the database connection credentials will need to be stored on disk for the rails app anyway!

On second thoughts I'm thinking it'd be a bit redundant anyway as the database connection credentials will need to be stored on disk for the rails app anyway!

You've essentially summarised your problem.

Even if you could encrypt the MySQL store - "all" they have to do is compromise your web server, and they get your DB credentials and can happily dump your database. The fact really is that if they can get the same level of access as you at any point - all your security efforts have gone down the drain.

Here are some general tips, but they don't replace your professional security advice:

Minimise access. Never have a user that can access the database server from any IP address, you're just begging for a brute-force attack. Instead, tweak the external firewall (e.g. the one EC2 called "Security groups") so only your web server can even connect to your DB server. The less people that know your VPS is a DB server, the better.
Make sure everything else is secure. That means your email passwords, security answers, and so on. If they can get access to your EC2 account or your email account, you've just completely wasted your time securing the instance. Social engineering attack are all the rage these days, don't let yourself be next.
Ensure your VPS instance is bare-metal. Don't have software on there that doesn't need to be, that even goes for sendmail (this is just an example of how, back then, you might have though "sendmail can't hurt to leave on!"). Anything that is there, keep updated frequently and watch out for security notices.
Try to avoid having any services in general that are public facing, especially something like SSH. If you must, disable password authentication and put SSH on a non-standard port (then connect to it like ssh -p <port> user@host as normal.

Having said all of this, it won't make your case to the client any better. You'll still say "it's as secure as it can be" but not "the data is encrypted on disk".


Problems connecting remotely to a RAC DB via sqlplus When I create a user in an 11204 single instance DB and afterwards grant the sysdba privilege to that user I can connect remotely without any problems using:

sqlplus testuser/testuserpwd@testdb as sysdba

However, when I do this against a RAC DB I keep getting an invalid username and password error for some reason. I can login locally with that username and password so they are correct. I can login remotely as well using:

sqlplus testuser/testuserpwd@racdb

It's only when I add 'as sysdba' at the end that it gives me the invalid username/password error with the RAC DB.

Any ideas? Many thanks.

Remote SYSDBA login uses password file authentication. A 11g RAC database uses local password files on each node in the local filesystem ($ORACLE_HOME/dbs). When you grant SYSDBA to a user, or change the password of this user, the password file is automatically updated on the node where the command was executed, but not on the other nodes. You need to manually copy the updated password file to the other nodes as well, otherwise you will not be able to log in to the other nodes.

Starting with 12c, the database password file can be stored in ASM, and it is not needed to perform such manual maintenance steps anymore.


Recursive query to find shortest path in graph I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the combination of all three values).

I'm supposed to use a recursive select query to find the shortest path between (for example) Bob Ross and Celine Dion. I know Bob is director of Painters, the manager of Painters is Carl, Carl is also an administrator in Sunny-D, and the director for Sunny-D is Celine Dion. So every name and company is a node, and the lines connecting them are the jobs. I'm not very good in SQL (we use PostgreSQL), and on top of that, I'm terrible at recursion, so if anyone could point me the right direction, I'd very much appreciate it.

What I have so far:

WITH RECURSIVE path(company_name, job, name, step) AS (
        SELECT c.company_name, c.job, c.name, 1
        FROM Company c
        WHERE person = 'Bob Ross'

        UNION ALL

    SELECT p.company_name, p.job, p.name, c.step+1
    FROM path p JOIN Company c ON p.person = c.person
    WHERE c.person = 'Celine Dion'
)
SELECT * FROM path;

I don't really know what to put in the recursive step where clause, or if I'm even joining them correctly

A recursive solution where the connections alternate between names and company_names.

The output shows all paths between the starting and ending node:

WITH RECURSIVE
conn AS
( SELECT
      name, company_name, job,
      (ROW_NUMBER() OVER (ORDER BY company_name, job))::text
          AS rn,
      CONCAT_WS(', ', name, company_name, job)::text
          AS node,
      1 AS lvl
  FROM
      company
  WHERE
      name = 'Bob Ross'
  UNION ALL
  SELECT
      b.name, b.company_name, b.job,
      CONCAT(a.rn, '-', (ROW_NUMBER()
          OVER (PARTITION BY a.rn
                ORDER BY b.name, b.company_name, b.job))::text),
      CONCAT(a.node, ' - ',
             CONCAT_WS(', ', b.name, b.company_name, b.job)),
      lvl + 1
  FROM
      conn AS a
      JOIN company AS b
      ON (  (  a.company_name = b.company_name
           AND a.name <> b.name AND a.lvl % 2 = 1
            )
         OR (  a.company_name <> b.company_name
           AND a.name = b.name AND a.lvl % 2 = 0
            )
         )
      AND a.name <> 'Celine Dion'
      AND b.name <> 'Bob Ross'
)
SELECT r.*
FROM conn AS f
     JOIN conn AS r
     ON f.rn LIKE CONCAT(r.rn, '%')
WHERE f.name = 'Celine Dion' ;

Test at dbfiddle.uk


Can DBA revoke access privilege from the owner of a table? If I create a table T, I become the owner of the table.

Can DBA access T?

Can DBA also revoke my access or grant privileges on T?

A user with DBA privillege can do anything. He can access or grant privillege of any table or database objects.


Incorrect unit allocation size. error comes when Tuning the Database Here I am Using the SQL IDERA tool for checking the Index and performance of the Database, then i found below error, can any one tell what is it talking about.

  Incorrect unit allocation size. Drive C: has a queue length average of
  7.26. This drive is also responsible for writing the transaction logs sequentially and performing the read/write of SQL Server pages which
  is typically random

  This results in latency currently measured at 0.0036ms as the head
  moves between sequential transactional log writing and the random
  Reading/Writing of SQL Server pages.  The unit allocation size has
  been detected to be 4KB.  SQL Server works far more efficiently with
  unit allocation sizes of 64KB and larger.

  Recommendation: It is recommended that for busy databases the
  Transaction logs reside on a different physical drive than the data
  files and that a unit allocation size of 64KB or larger be used.

For the first point:

  Incorrect unit allocation size. Drive C: has a queue length average of 7.26. This drive is also responsible for writing the transaction logs sequentially and performing the read/write of SQL Server pages which is typically random*

This indicates that some of the user databases are hosted on the C: Drive which as per the best practice should not be the case. It is preferred that mdf should be on a drive with its respective RAID level for higher reads medium writes, while LDF should be on a different drive apart from MDF. and per the error message it seems you're MDF and LDF reside on C drive:

So check for those DB's whose files are on C: drive and move them to their respective data drives, if you have any, if not then get them attached.

For allocation of unit size in SQL Server read  This article it will help:

And also as recommended and mentioned above in point a , you should move the heavy DB's files to their physical drives like D and L as per you're naming convention on the servers.


In mysql how to identify what caused a table lock We are using MySQL 5.6 on rhel 7. Planning to migrate to version 8.xx soon.

We have a mysql database of size 4TB. It has around 3000 tables with few tables having size of 200-300GB. Sometimes queries takes longer than 60 to 100 seconds on these bigger tables.

Sometimes due to less search conditions,  select query executes for longer time but sometimes regular queries also take long times.

It could be due to MySQL was busy doing something else .. we tried using Oracle MySQL enterprises monitor for few days. It shows long running queries but it doesn't have time stamp when a query was started. So we are not able to identify what other queries were running when one query took long time. MySQL monitor didn't report any issues too with configuration or settings..

how to identify what caused a table lock or what caused delayed response ?

The best way to do this is to enable the slow query log. It will log in a file queries that took more than X number of seconds to perform, as well as, the lock time it took.

You need to edit your /etc/my.cnf and add the following lines (you'll need to restart your mysql service for the changes to take effect):

slow_query_log = 1
slow_query_log_file = /var/log/slowmysql.log
long_query_time = 2
log-queries-not-using-indexes

A small explanation of each:

The first line enables the log.
The second tells the server where to store it.
The third one tells it how many seconds to wait in order to log the
query. I recommend you start with a high number (say 10 seconds),
and once your queries get optimized, reduce that number
The fourth line will enable you to log every query not properly
using an index. Although this might log quick running queries, it
will also help you spot which queries are not truly optimized using
the correct indexes.


Is GROUP BY select-list-ref less optimized than GROUP BY colname? Just wondering if GROUP BY 1 is slower than GROUP BY col?

SELECT x, count(x)
FROM foo
GROUP BY 1;

It demonstrably can be in the event x is not a simple column name. If it is a simple column name it doesn't matter. Take for instance this sample set.

CREATE TABLE foo
AS
  SELECT now()::date AS x
  FROM generate_series(1,1e6);

That creates a sample set of 1,000,000 rows. Now look at the following.

SELECT to_char(x,'YYYY-MM-DD'), count(*)
FROM foo
GROUP BY 1;

The above takes 820.066 ms, however..

SELECT x, count(*)
FROM foo
GROUP BY x;

The above takes only 190ms, need to get it back to the preferred format? Wrap it another SELECT

SELECT to_char(x,'YYYY-MM-DD') AS x, count
FROM (
  SELECT x, count(*)
  FROM foo
  GROUP BY x
) AS t;

And, it still just takes 190ms. Things get even more hairy if you have an index, and useful data. Then you could perhaps be visiting a table when you wouldn't otherwise need to, or you could be sorting in seq scan when an index walk would otherwise work.

It's important to note that this caveat on GROUP BY # only applies if the column of the resultset is an injective function. It's proof that it can matter, not proof that it does matter.


How to group items by path (graph structure) and leave only those with highest score using t-sql? I have a table with records and their scores (table 'Offers'):

OfferId     OfferScore
----------- -----------
1           1
2           20
3           3
4           4
5           5
6           6
7           7
8           8

These records might be related. All relations are stored in the table 'OffersRelation':

OfferId1    OfferId2
----------- -----------
1           2
1           3
1           4
1           5
2           3
3           5
7           8

I want to remove all related Ids (within the group) from table Offers and leave only ones with the highest score.

In my case :

Offers with Ids: 1,2,3,4,5 are related ("group") and the offer with id:2 has the highest score:20 so I want to remove ids: 1,3,4,5 and leave only the one with id:2 from this "group".

Offer with Id:6  doesn't have any related items (no relations in the table OffersRelations) so it stays in the table Offers.

Offers with Ids:7,8 are related and Offer with id:8 has higher score than 7 so i want to remove the offer with id:7 and leave the offer with id:8 from this "group".

Expected result in the table Offers:

OfferId     OfferScore
----------- -----------
2           20
6           6
8           8

How to do it in t-sql?

Data from examples:

drop table if exists dbo.Offers
drop table if exists dbo.OffersRelation
CREATE TABLE [dbo].[Offers](
    [OfferId] [int] NOT NULL,
    [OfferScore] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[OffersRelation](
    [OfferId1] [int] NOT NULL,
    [OfferId2] [int] NOT NULL
)
GO

INSERT INTO [dbo].[Offers]
           ([OfferId],[OfferScore])
     VALUES (1,1),(2,20),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8)

INSERT INTO [dbo].[OffersRelation]
           ([OfferId1]
           ,[OfferId2])
     VALUES
            (1,2),
            (1,3),
            (1,4),
            (1,5),
            (2,3),
            (3,5),
            (7,8)

I have completely redone my answer (based on the additional information you provided in your question).

You indicated that you tried recursion and it was too slow on a large set of data.  I've created a cursor based solution that works on your test data, but I'm wondering if it gives you the correct answer on your production data.  With such a small set of test data, it's difficult to spot flaws in my logic.

Can you give it a try and let me know if I'm any closer to a proper solution?

DECLARE @OfferId INT
DECLARE @OffersToDelete TABLE (id INT)

--Declare cursor for all Offers
DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT [o].offerid
FROM Offers [o]
ORDER BY [o].OfferId

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @OfferId

DECLARE @Related TABLE (OfferId INT)

WHILE @@FETCH_STATUS = 0
BEGIN
    --For each Offer, initialize @Related table
    DELETE
    FROM @Related

    --Insert into @Related the Offerid that we are currently processing
    INSERT INTO @Related
    SELECT [or].OfferId2
    FROM OffersRelation [or]
    WHERE [or].OfferId1 = @OfferId

    --Now, loop through all offers related to the one we're processing
    --When @@ROWCOUNT = 0, you have processed all related rows and need
    --to break out of the while loop
    WHILE 1 = 1
    BEGIN
        INSERT INTO @Related
        SELECT [or].OfferId2
        FROM OffersRelation [or]
        JOIN @Related [r]
            ON [r].OfferId = [or].OfferId1
        WHERE NOT EXISTS (
                SELECT *
                FROM @Related
                WHERE OfferId = [or].OfferId2
                )
            AND [r].OfferId <> @OfferId

        IF @@ROWCOUNT = 0
            BREAK;
    END;

    --With all related rows, including the offer we're processing,
    --add a row number ordered by OfferScore desc and insert all rows
    --not = 1 into the @OffersToDelete table
    WITH OfferAndRelated
    AS (
        SELECT Offerid
            ,offerscore
        FROM offers
        WHERE offerid = @OfferId

        UNION

        SELECT [o].offerid
            ,[o].offerscore
        FROM @Related [r]
        JOIN Offers [o]
            ON [o].OfferId = [r].OfferId
        )
        ,OfferAndRelatedWithRowNumber
    AS (
        SELECT *
            ,ROW_NUMBER() OVER (
                ORDER BY OfferScore DESC
                ) AS rn
        FROM OfferAndRelated
        )
    INSERT INTO @OffersToDelete
    SELECT OfferId
    FROM OfferAndRelatedWithRowNumber
    WHERE rn <> 1

    --  SELECT *
    --  FROM @Related
    FETCH NEXT
    FROM _CURSOR
    INTO @OfferId
END

CLOSE _CURSOR

DEALLOCATE _CURSOR

--Delete from Offers where the OfferId is on the @OffersToDelete table
DELETE [o]
FROM Offers [o]
JOIN @OffersToDelete otd
    ON otd.id = [o].OfferId

SELECT *
FROM Offers

| OfferId | OfferScore |
|---------|------------|
| 2       | 20         |
| 6       | 6          |
| 8       | 8          |


Transactional replication reports ok, but tracer token not arriving I've got an odd problem here.  I was trialling the use of 'initialize from lsn' in our test replication setup and now have broken all publications from one of our databases, rebuilding from scratch (applying a new snapshot) has not fixed the issue.

In Replication monitor, distributor agent running continuously and reporting:

  No replicated transactions are available

Tracer token is reporting that:

Publisher to Distributor: 00:00:02
Distributor to Subscriber: Pending...
Total Latency: Pending...

When I investigated the distribution logs using:

SELECT time,
       CAST(comments AS XML) AS comments,
       runstatus,
       duration,
       xact_seqno,
       delivered_transactions,
       delivered_commands,
       average_commands,
       delivery_time,
       delivery_rate,
       delivery_latency / (1000 * 60) AS delivery_latency_Min,
       agent_id
FROM dbo.MSlogreader_history WITH (NOLOCK)
WHERE agent_id = 5
ORDER BY time DESC;

SELECT *
FROM dbo.MSdistribution_history
WHERE agent_id = 125
ORDER BY time DESC;

I can see that the latest xact_seqno from:

LogReader agent is: 0x00000378000384880003
Distribution agent is: 0x000CA68B000010A8000A000000070000

As a comparison on one of the other databases (going to the same subscriber), I get:

LogReader agent is: 0x00000D140001F52A0003
Distribution agent is: 0x00000D140001F52A0003000000000000

To me it looks like the distribution agent is remembering the old broken xact_seqno (from when I made a mistake when testing 'initialize from lsn'), but now it appears to have permanently broken all replication from that database.

Servers:

PubA - One publisher hosting several databases that are replicated
Dist - A separate distributor
SubA - First subscriber
SubB - Second subscriber

Databases:

PubA.DB1 = Publisher DB that is working fine
PubA.DB2 = Publisher DB that is broken
SubA.PullWorking1 = 1st subscriber has a working pull subscription from DB1
SubA.PullBroken2 = 1st subscriber also has a broken pull subscription from DB2
SubA.PushWorking1 = 2nd subscriber has a working push subscription from DB1
SubA.PushBroken2 = 2nd subscriber also has a broken push subscription from DB2

Publications:

PubA.DB1.PushPub_works
PubA.DB1.PullPub1_works
PubA.DB1.PullPub2_works
PubA.DB2.PushPub_broken
PubA.DB2.PullPub_broken

Ok, in case anyone ever has this problem I found the fix (though not the root cause).

Fix

Stop the distribution agents for all subscribers to publications from this database
Get the LSN that is causing the problem from one of the subscribers:

SELECT
        publication,
        '0x' + CONVERT(VARCHAR(32),MAX(transaction_timestamp),2) as LastPubLSN
    FROM
        dbo.MSreplication_subscriptions
    GROUP BY publication

Find a valid LSN to start from:

DECLARE @Publisher sysname = N'PubA',
            @PubDB sysname = N'DB2',
            @Publication sysname = N'PushPub_Broken';
    SELECT TOP 1000 trans.entry_time, trans.publisher_database_id, trans.xact_seqno
        FROM dbo.MSpublications AS p
            JOIN master..sysservers AS srv
                ON srv.srvid = p.publisher_id
            JOIN dbo.MSpublisher_databases AS d
                ON d.publisher_id = p.publisher_id
                AND d.publisher_db = p.publisher_db
            JOIN dbo.MSrepl_transactions AS trans
                ON trans.publisher_database_id = d.id
        WHERE p.publication = @Publication
              AND p.publisher_db = @PubDB
              AND srv.srvname = @Publisher
              AND trans.xact_seqno >= p.min_autonosync_lsn
    ORDER BY trans.entry_time DESC

Check what invalid commands are in the repl queue:

DECLARE @PubDB INT = 5  /* use the publisher_database_id retrieved above */
    select * from MSrepl_commands with (READPAST)
              where
                 publisher_database_id = @PubDB and
                 command_id = 1 and
                 type <> 30 and  /* 30 = normal repl commands */
                 type <> -2147483611 /* -2147483611 = is ignored by sp_MSget_repl_commands (it's snapshot related?) */

Backup and delete the commands that are causing the blocking, this should be the same LSN that is causing the problem (see step 2)
Reset the LSN on each of the subscriber DBs

EXEC sp_setsubscriptionxactseqno
        @publisher = 'PubA',
        @publisher_db = 'DB2',
        @publication = 'PushPub_Broken',
        @xact_seqno = 0x0000...  /* New valid LSN to start from (step 3) */

Start up your distribution agent and verify that all is working.

How I found it

So after finding and trialling sp_setsubscriptionxactseqno in conjunction with knowing how to get the LastLSN applied to the subscriber, I ran a trace on the distributor, I was able to see that the sequence was:

exec sp_MSget_repl_commands 126,/* good LSN that I was telling it to use */,0,10000000
go
exec sp_MShelp_distribution_agentid 6,N'PushPub_broken',N'DB2',8,N'PushBroken2',0
go
exec sp_MSget_repl_commands 126,/* bad LSN that it was resetting too */,0,10000000
go

Running the first command myself, I could see 2 resultsets returned, the 2nd of which included the bad LSN.  Obviously something inside this proc was causing it to overwrite the LSN I wanted it to apply.

Finding the code to sp_MSget_repl_commands, I was able to identify that it was:

select @max_xact_seqno = max(xact_seqno) from MSrepl_commands with (READPAST)
  where
     publisher_database_id = @publisher_database_id and
     command_id = 1 and
     type <> -2147483611

From there, I found the culprit records and was able to restart replication (after resyncing my data)


How to import excel to MS Access table design? NOTE: This is NOT asking how to export MS Access table design to excel. This is the opposite direction.

Given a excel sheet, it has field info (name, start position, and width). I want to export this excel sheet into MS Access table design.

The excel file:

Field 1 len = 3 start = 1
Field 2 len = 2 start = 4
......

The MS Access design table

Field name Field length
Field 1    3 (to be appeared)
.......

If you want to create a table using DDL (data definition language) you can use a create table statement

You don't need the start or end position. But you do need a data type and field length.

With a bit of clever work in excel, you can probably generate a create statement.

Unless you are repeating this for lots of tables it might be easier just to create the table directly in Access.

Another option is to mock up a sample table in excel with your field names and a sample row of data and import it into access. That way access will select suitable data types.


select fieldvalues and column-description of a table is it possible to retreive the value of a column AND the descirption of each column? I want to see the description behind wach value.

Like:

SELECT
    a.*,
    (SELECT
        d.description
    FROM
        information_schema.COLUMNS AS c
        LEFT JOIN pg_description as d ON
        d.objsubid = c.ordinal_position
    WHERE
        TABLE_NAME = <a.tablename> AND column_name = <colname>) as description
FROM table as a
WHERE true;

The table should by look like this:

id: serial 'no description';
col1: int2 'Column One';
col2: int2 'Column Two';

Content:

1, 10, 11
2, 20, 21
3, 30, 31

The result should look like this:

1, 10 (column one), 11 (column two)
2, 20 (column one), 21 (column two)
3, 30 (column one), 31 (column two)

Best regards

Christian

The only way I can think of is something like this:

select t.id, x.remarks ->> 'id',
       t.col1, x.remarks ->> 'col1',
       t.col2, x.remarks ->> 'col2'
from the_table t
 join lateral (
    select jsonb_object_agg(a.attname, col_description(a.attrelid, a.attnum)) as remarks
    from pg_attribute a
    where a.attrelid = t.tableoid
    and a.attnum > 0
 ) x on true

The inner query aggregates all column descriptions of the table into a single JSON value that can be accessed easily in the outer query.