Performance considerations for using MySQL table as a queue? I'm working on a system which reports certain events to an external service.
Whenever a relevant change is made in the database, we want to guarantee that at least one message is sent to the external service.

I'm thinking this could be implemented with a message_queue table as follows:

BEGIN a transaction
Make the relevant database change
Insert a message about the change into the message_queue table, including a UUID
COMMIT the transaction
Later (eg on a cron job), iterate through the message_queue table, attempting to send each message to the external service. Whenever we get a successful response, delete that row. When we don't, try again later. If the external service actually received that message the first time but the reply was lost, it can discard it based on the duplicate UUID.

Assuming we can send and delete message_queue rows faster than they are created, this table would tend to have very few rows at any moment.

Given this usage pattern of frequent inserts and deletes, I think we would not want to index the table.
Is that correct? What else could we do to minimize the impact on overall database performance?

My first encounter with "using MySQL as a queue" ended in disaster.  The team was very wedded to tossing things into a table, then pulling it out to work on.  They were limited to how many things the could achieve per hour.

After studying not only the queuing mechanism but the enqueue and dequeue API and the worker threads, I decided on:

  "Don't queue it, just do it".

I estimated (for their case) that they could increase the throughput 10-fold by removing the queue.

Here are some random lessons:

If the task to perform is fast enough, you may be spending more time on enqueuing/dequeuing than the task.
If the tasks arrive in a steady stream, the "buffering" that queuing gives you is unnecessary.  On the other hand, if the tasks are "bursty", queuing may be beneficial.
Replication (HA was a requirement in the case) significantly complicates the queuing code.
Using AUTO_INCREMENT for queuing with replication adds a complication because the ids don't always arrive in the Slave in order!  This happens randomly (but not very frequently) with InnoDB.
Deletion of queue items leads to fragmentation.  This was especially bad with MyISAM.
Note how switching Engines solves one problem but creates another?
UUIDs are terrible for performance if the table gets bigger than can be cached.
Transactional integrity gets messy -- the item in the queue is mostly independent of the task it represents.  Issues to resolve: requeuing a task that fails; crashing in the middle of a task; etc.
Sure, the queue will have very few rows at any normal moment.  But there will come a time when something hiccups and there is a million tasks queued up.  The system will croak for any of several reasons that you failed to plan for.  You have a crisis on your hands and the looming job of analyzing and planning for such eventualities.
Based on my previous comment, do you really want to leave off any index?  No.  The dequeuing mechanism will get slower and slower, further exasperating the crisis.
If a queued task is assigned to a worker, but that worker crashes, then the item needs to stick around somewhere (in the queue or some other place) and some separate task needs to eventually discover an unfinished task after some timeout.  More messy code to write.

An alternative (that may or may not apply to your situation):  Leave information around and have a continuously running job (not a cron) that looks for the info and acts on it.  This can be handy if the items arrive rapidly and they can be processed in batches.


Simple query not using indexes My query is not using indexes.
It is a simple query.
Can someone explain why that's the case?

The query:

select TIME_FORMAT(start_time,'%H:%i') start_time,
       TIME_FORMAT(end_time,'%H:%i') end_time,
       rule_id, day_of_week, long_message,
       short_message, rotation_weight, mode
from timed_delivery_messages order by rule_id asc;

Explain Plan:

+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table                   | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | timed_delivery_messages | index | NULL          | PRIMARY | 4       | NULL |   22 |       |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

Table:

mysql> show create table timed_delivery_messages\G
*************************** 1. row ***************************
       Table: timed_delivery_messages
Create Table: CREATE TABLE `timed_delivery_messages` (
  `row_create` datetime DEFAULT NULL,
  `row_mod` datetime DEFAULT NULL,
  `rule_id` int(11) NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `day_of_week` varchar(7) DEFAULT NULL,
  `rotation_weight` varchar(50) DEFAULT NULL,
  `mode` varchar(10) DEFAULT 'active',
  `long_message` varchar(256) DEFAULT NULL,
  `short_message` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

As far as I can tell, your query is using an index, the one that backs your primary key.

Look at the EXPLAIN Output Format documentation for the "join type" column. The second bullet point for the index join type is what your select is using:

  A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.


How do I create a PRIMARY KEY/UNIQUE constraint that considers trailing space significant? I would like to be able to store records about things that are identified by strings where space is significant. I know that T-SQL follows rules about string comparison to implicitly pad up to the length of either string when performing comparisons and that I can work around that in WHERE clauses by also comparing DATALENGTH. But I don’t know how to make a PRIMARY KEY behave similarly. How do I store data so that

tools like SSMS display it as text without me needing to CAST it
it is mostly just treated like a string type in a normal programming language (with case/space being significant in comparisons)
it is stored in a column with UNIQUE/PRIMARY KEY constraints?

I would expect the following to work if T-SQL didn’t do the weird padding-during-comparison thing:

CREATE TABLE #x (
  Id NVARCHAR(16) COLLATE Latin1_General_BIN2 PRIMARY KEY
)
INSERT INTO #x VALUES ('asdf');
INSERT INTO #x VALUES ('Asdf');
INSERT INTO #x VALUES ('asdf '); -- error here
DROP TABLE #x

But on the third insert, I get:

Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK__#x________3214EC0761C21167'. Cannot insert duplicate key in object 'dbo.#x'. The duplicate key value is (asdf ).

Since I mentioned that I’m using DATALENGTH in my WHERE clauses, I’m going to try extending that to PRIMARY KEY creation as well. To do this, I’m just going to add a computed column with the DATALENGTH of the first one and then include that column in the PRIMARY KEY:

CREATE TABLE #x (
   Id NVARCHAR(16) COLLATE Latin1_General_BIN2 NOT NULL
  ,IdLength AS DATALENGTH(Id) PERSISTED NOT NULL
  ,PRIMARY KEY(Id, IdLength)
)
INSERT INTO #x VALUES ('asdf');
INSERT INTO #x VALUES ('Asdf');
INSERT INTO #x VALUES ('asdf ');
INSERT INTO #x VALUES ('asdf '); -- Error, proving that UNIQUE works
SELECT '"'+Id+'"' QuotedId, * FROM #x;
DROP TABLE #x;

I get a table like this:

QuotedId           Id               IdLength
------------------ ---------------- -----------
"Asdf"             Asdf             8
"asdf"             asdf             8
"asdf "            asdf             10

The attempt to insert 'asdf ' twice resulted in and error, proving that I am still benefiting from UNIQUE behavior:

Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK__#x________A3E5142C3CFA2452'. Cannot insert duplicate key in object 'dbo.#x'. The duplicate key value is (asdf , 10).


Need separate columns that calculate percentage I have the following table:

NAME
Alex
Bob
Bob
Tim
Alex
Roger

I need to produce the following:

 Name    Count Percentage
 Alex    2     33%
 Bob     2     33%
 Roger   1     16.6%
 Tim     1     16.6%

I have no idea where to start.  I can do the initial query for the count which is simple.

Possible with a single SELECT:

SELECT name, count(*), to_char((count(*) * 100.0
                          / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent
FROM   t
GROUP  BY 1
ORDER  BY 1;

count(*) is a separate form of the function  and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the latter.

You can run the window aggregate function sum(count(*)) OVER () to get the total in the same SELECT. Consider the sequence of event in a SELECT:

How will the order of operations in this query affect my results?

This uses one sequential scan instead of two and is typically twice as fast as using a subquery for the total count.

to_char() prettifies the output:

name  count  percent
-----------------------
Alex  2      33.33 %
Bob   2      33.33 %
Roger 1      16.67 %
Tim   1      16.67 %


How do I get SQL Server agent to show up in SSMS for a non-sysadmin user? I have already mapped the user to the msdb database and assigned him the role of SQLAgentOperatorRole, but the user still can't see the SQL Server Agent listed when he connects.

The SQL Server Agent does show up fine when I log in as a sysadmin.

There are several points to consider.

Make sure that person connecting to SQL Server 2012 is using SSMS 2012, I have seen compatibility issues when some features are not showing up when connecting with wrong version.
Remove all roles from the user and grant them again, go over these steps to make sure that you have done it correctly first time.

Script for creating account and assigning role:

USE [master]
CREATE LOGIN SQlSATest WITH PASSWORD = 'test', CHECK_EXPIRATION=OFF, CHECK_POLICY= OFF;
USE [msdb]
CREATE USER [SQLSATest] FROM LOGIN SQLSATest;
EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = 'SQLSATest'


How can I 'fake' stats for generate_series data? With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.1.4
*/
explain analyze
    select foo, generate_series(1,10)
    from (select 'A' as foo) z
;
/*
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Subquery Scan on z  (cost=0.00..0.02 rows=1 width=32) (actual time=0.010..0.014 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
*/

note: (cost=0.00..0.02rows=1width=32) (actual time=0.010..0.014rows=10loops=1)

This is improved in 9.2, giving the standard 1000 estimate:

select split_part(version(),' ',2);
/*
 split_part
------------
 9.2.1
*/
explain analyze
    select foo, generate_series(1,10)
    from (select 'A' as foo) z
;
/*
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Subquery Scan on z  (cost=0.00..5.02 rows=1000 width=32) (actual time=0.014..0.020 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
*/

note: (cost=0.00..5.02rows=1000width=32) (actual time=0.014..0.020rows=10loops=1)

How can I 'fake' this behaviour in 9.1 and earlier?

The problem occurs when using the Set Returning Function in the SELECT target-list, but that way of calling these functions is kludgy in the first place.

See for example this thread on pg-hackers list on various reasons why experienced PG users tend to avoid it.

On the other hand, when the SRF is called in the FROM list, without the fake cross join, 9.1.6 produces the standard estimate too:

 explain analyze
 select * from (select 'A' as foo) z, generate_series(1,10) b;

results in:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..22.52 rows=1000 width=36) (actual time=0.018..0.041 rows=10 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Function Scan on generate_series b  (cost=0.00..12.50 rows=1000 width=4) (actual time=0.014..0.021 rows=10 loops=1)
 Total runtime: 0.076 ms
(4 rows)


Batch Inserts and Batch Deletes I have two tables with same structures in a MS Sql server : T1 and T2, T1 has around 4 Billion rows and T2 is an empty table. Need to insert T1 data in to T2 in batches, After each Batch Insert in to T2 from T1, I have to Delete that data(batch Delete) from T1.
FYI: Tables contains 1 Primary Key and 4 non-clustered indexes

Please suggest me the best options/best query.

You can try this query to get good performance without using temp table or joining to delete from T1. And it's very simple.

SET ROWCOUNT 10000

WHILE EXISTS (SELECT TOP 1 'EXISTS' FROM T1 WHERE COLUMN=VALUE)
BEGIN
WAITFOR DELAY '00:00:10' -- You can pass it if you want interval between next delete
DELETE FROM T1
OUTPUT DELETED.* INTO T2
WHERE COLUMN=VALUE
END

SET ROWCOUNT 0

You can comment or remove WHERE clause if not required.

Thanks


Tuning a query to fetch all the records in a day with certain status We are currently using Oracle Database:

Consider a table TRANSACTION with the fields TRAN_ID, STATUS, CREATED_TIME, ....

The below query gets executed multiple times in a day.

select tran_id
from TRANSACTION table
where created_time <= TO_DATE(:1,'YYYY-MM-DD HH24:MI:SS')
and created time > TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS')
AND STATUS = :3`

Possible values are I- InProgress, F- Failed, S - Successful. Currently only TRAN_ID is Indexed, so the table goes through a full table scan.

Have the following options:

Introduce an index on status, this will solve the problem for handling 'In Progress' transaction as they can be low, but failed and success would be higher (However query on 'failed' or 'success' would run once or twice in a day).
Introduce a composite index(status,created_at) but  as I understand the data would get skewed as time progresses and index table will also grow rapidly.
Move the data from the in progress table to another history table at regular intervals so that it can work coupled with option a. coupled with quite some application changes.

I am not sure if there are other options available.

B-tree Index on Status won't help - it has 3 distinct values, and full table scan will be much faster. Bitmap index should work better in such case, but TRANSACTION table name implies lots of DML , so it shouldn't be used.

Some improvement may be gained by partitioning transaction table by status, and then create local index on created_time. In a sense it's similar to (3), but don't require application changes, and moving rows to proper partition is done by the engine itself. However, it is quite an expensive option, and should be used for really huge databases .

In my opinion, index on created_time will work just fine for most of the cases.


Set permissions on all objects for SQL users I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to create new databases so I have to run this every time I create a new database. Here is an idea of what it looks like (note: there is a chunk missing from the beginning that drops all users and recreates the necessary users; this is necessary since the SID's change):

CREATE PROCEDURE usp_SetPermissions
AS
 BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects
(
   ID INT IDENTITY(1,1),
   ObjectName NVARCHAR(128),
   Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed
FROM sys.objects
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
 BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME())
    EXEC sp_sqlexec @Sql

    -- Update object completion
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed
FROM sys.objects
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
     BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME())
EXEC sp_sqlexec @Sql

    -- Update object completion
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

This scripts continues on to do similar things for all the views, functions, etc. in the database. Any ideas for speeding this thing up or is there a better way to do this?

Single line to give permissions at the schema level to a role

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

And a second line to add users to the role

EXEC sp_addrolemember 'SomeRole', 'whatever user'

And do this in model so all new databases inherit.

Reasons, you should set permissions once only:

A schema is a container for objects.
New objects inherit permissions from the schema
A role is container for users
New users are added to a role and inherit

As you've found, migrating or restoring a database can lose object permissions when assigned directly to users. So why put yourself in that position?

You can also CREATE LOGIN with a SID so it is the same on all your servers and you don't get orphaned users either.

If you'd asked the correct question we could have saved you some coding...

Personally and finally, I'd say this is bad practice on "need to have", not "blanket do anything"


Percona: Do I need apply-log switch for a weekly full backup and hourly incremental? I am planning a script to work on our percona. Our strategy is to do full backup weekly but incremental backups every day/hour. I have read innobackupex manual and got confused further as to whether I really need it or no.

A post here on dba.stackexchange.com , to my understanding suggests that I do NOT need this if I am also doing the incremental backup. This was also confirmed on a percona forum

To be on the safer side this is how I planned to achieve this:

#first day of the week
innobackupex --user=myuser --password=mypassword /path/to/backup/dir

#Every day/hour
innobackupex --incremental .... ... ...

I hope I got it right, if not kindly advise.

Thank you

You do not need apply-log for the backup, but you need it for the restore. Depending if you perform differential or incremental backups, you will need to execute one apply-log for rolling forward the backup and application of the changes since the last full or incremental backup. --apply-log --redo-only --incremental-dir

Whether you perform that after the backup or before the restore is entirely up to you and your backup policy. Some people prefer to have a ready-to-copy-back rolling-forward full backup, so they execute the --apply-log just after the backup. Others do the whole process just in case of the restore is needed. Both approaches have different properties in mean time to recovery, detection of faulty backups, space needed, time to perform the backup, etc.

Think on your recovery policy (not your backup strategy) and make the tool suit to it, not the other way round.

More info: http://www.percona.com/doc/percona-xtrabackup/innobackupex/incremental_backups_innobackupex.html


Referential Integrity - Indirect Foreign Key In "Depth" Question

What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship trees.

Details Since That Question Doesn't Likely Make Sense

I am attempting to build a database for keeping track of the execution status of automated processes running in our environment.

In general we have a "Job" which triggers one or more "Executables" and those "Executables" can run tasks for one or more customers.  We will then have 2 logging tables, one that tracks when a "Job" was started, and another that will log the Success vs Failure status of each "ExecutableCustomer" instance.

A Planned Simplified Schema is below:

When we right the record to the JobExecutableCustomerExecutionLog, I would like to ensure that the Job.ID value associated with JobExecutionLog.ID value matches the Job.ID value associated with JobExecutableCustomer.ID.

Normally I would handle this with a Foreign Key but since Job.ID is not stored on JobExecutableCustomer, JobExecutableCustomerExecutionLog nor JobExecutionLog.  The Relationship is indirect.

Example:

I have 2 jobs, "Send Email" and "Send Text Message".  "Send Email" initiates a single executable which belongs to 1 Customer. "Send Text Message" has 2 executables (both of which execute for the same customer). I want to make sure that when the record is written to JobExecutableCustomerExecutionLog for "Send Email" the Job.ID associated with JobExecutableCustomerExecutionLog.JobExecutableCustomerID and JobExecutableCustomerExecutionLog.JobExecutionLogID (after walking the relationships up) actually belong to the Job.ID for "Send Email" not "Send Text Message".

As I see it I have 2 options:

Push the value from Job.ID into all the child tables, and make it part of the Foreign Key
Have another process (Trigger or Indexed View) ensure the relationships for me

I personally don't like the idea of pushing the Job.ID value on all the other child tables, so I am leaning towards using a Trigger or something else to handle it.  I didn't know if those were my only two options or if I have the ability to configure a "normal" Foreign Key to traverse the relationships all the way up.  In some kind of Cascade or something else.

I personally don't like the idea of pushing the Job.ID value on all the other child tables

Why not?  The obvious solution is to make JobID the leading PK/clustered index column on all the child tables.  And it ensures optimal performance for accessing all these tables by JobID.

Generally whenever you have an "identifying relationship" aka "child table" aka "weak entity" the child table should use a compound primary key whose leading columns are also the foreign key to the parent table.  Something like this:

create table Parent
(
  ParentId int not null identity,
  constraint pk_Parent
    primary key(ParentId)
)

create table Child
(
  ParentId int not null,
  ChildId int not null identity,
  constraint pk_Child
    primary key (ParentId,ChildId),
  constraint fk_Child_Parent
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)
create table GrandChild
(
  ParentId int not null references Parent,
  ChildId int not null,
  GrandChildId int not null identity,
  constraint pk_GrandChild
    primary key (ParentId,ChildId,GrandChildId),
  constraint fk_GrandChild_Child
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)


MongoDB : How to estimate max number of connections supported? We have a sharded cluster with 2 replica sets. Each replica set is a 3 member cluster with one primary and 2 secondaries. Each instance is a r3.large (aws-ec2) instance with 15G ram.

mongos's are in app servers and can have minimum 3 and can auto-scale to 10. Is there a way to find out number of max connections supported by sharded cluster. That means number of connections between mongos and cluster. (Like For MySQL max connections are based on size of RAM)

mongo version - 2.6.8

Each connection eats up about 1MB of RAM on the server side. That includes the connections to the config servers and the replica set members. Please keep in mind that those communicate with each other.

So, let's say you want to use 1/3 of the RAM of your shards for connections, that'd be 5GB or 5000MB.

Then, each server connects to two members of it's replica set. These are actually 3 physical connections each. Furthermore, we have connections for managing the sharded cluster, which are 3*3=9. So we have 5000 - 6 (for the replica set) - 9 (for the management) = 4985 connections available. Since we want each mongos be relatively equaling terms of connections, we divide this by 10 (the maximum number of mongos instances we can expect) and get 498, rounded down. That is a relatively normal value, but this of course depends on the use case. If you have a lot of or very big indices, your 15GB of RAM may actually be a bit small.

As per the rule that there should be at least as many database connections available as application threads, your AppServers should be configured accordingly.


How to give precedence to rows from one table over matching rows in another? I've got (key, value) pairs stored in two different tables - company_settings and branch_settings. A key can exist in none, one or both of the tables.

In case the key exists in both tables, I'd like to use the value stored in the branch_settings table. Otherwise, if available, I'd like to fall back to the value stored in the company_settings table.

company_settings
-------------------------
| key    | value
-------------------------
| key.A  | 4             |
-------------------------
| key.B  | 5             |
-------------------------

branch_settings
-------------------------
| key    | value
-------------------------
| key.A  | 1             |
-------------------------

So, if I query for key.A the result should be 1 since key.A exists in both tables and I want to give higher precedence to records from branch_settings table.

If I query for key.B the result should be 5 since key.B exists only in company_settings table.

If you need the value for only one "key", you can use COALESCE() function with 2 subqueries:

-- query 1a
SELECT COALESCE(
           (SELECT value FROM branch_settings WHERE key = prm.key),
           (SELECT value FROM company_settings WHERE key = prm.key)
       ) AS value
FROM
    (VALUES ('key.A')) AS prm (key) ;

You could also expand the list of keys inside the VALUES, to search more than one key:

-- query 1b
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key) ;

If you want to search many keys however, it might be more efficient to convert the subqueries to joins. Either two LEFT joins:

-- query 2
SELECT prm.key, COALESCE(b.value, c.value) AS value
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key)
  LEFT JOIN branch_settings AS b ON b.key = prm.key
  LEFT JOIN company_settings AS c ON c.key = prm.key ;

or a FULL JOIN, similar to @James' answer:

-- query 3
SELECT key, COALESCE(b.value, c.value) AS value
FROM
    (SELECT * FROM branch_settings
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  FULL JOIN
    (SELECT * FROM company_settings
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  USING (key) ;

There is a subtle difference between queries 1b, 2 and query 3. The last query will give you in the results only the (parameter) keys that appear in at least one of the tables. Query 1b and 2 will have all the (parameter) keys in the results, with NULL in value for keys that can'e be found in either table.


Sql, get category tree for values from one table, names and ids What I need
I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category).

Example
On web there is product with category tree.

In table "category" I have parent category id as 0, and other children categories ids are defined by numbers as this.

How can I in sql show all categories tree in one field? For example.

Úvod > Bydlení > Barvy a Laky > Vnitřní barvy

What is the simplest way to do that?

DBMS = PostgreSQL ver. 10.9.

Thank you very much.

BR.

Edited, updated, for ids main category id is not present.

By Akina solution.

with recursive

cte as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte.category_name, ' > ', s_category.category_name)
from s_category,cte
where cte.category_id = s_category.category_parent
),

cte2 as(
select category_id,category_parent,category_name::text
from s_category
where category_parent = 0
union all
select
s_category.category_id,
s_category.category_parent,
concat(cte2.category_id, ' : ', s_category.category_id)
from s_category,cte2
where cte2.category_id = s_category.category_parent
)

select
cte.category_name as tree_category,
cte2.category_id as tree_id,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand

from
s_product

left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join cte on s_product.category_id = cte.category_id
left join cte2 on s_product.category_id = cte2.category_id

where
s_product.product_id = '133471'

Example.

Structure and data:

CREATE TABLE tree (id INT, parent INT, name VARCHAR);

INSERT INTO tree VALUES
(1,0,'root'),
(2,1,'branch 1'),
(3,1,'branch 2'),
(4,2,'branch 3'),
(5,2,'branch 4'),
(6,4,'branch 5'),
(7,6,'branch 6'),
(8,3,'leaf 1'),
(9,5,'leaf 2'),
(10,7,'leaf 3');

Query:

WITH RECURSIVE cte AS ( SELECT t1.id, t1.parent, t1.name
                        FROM tree t1
                        LEFT JOIN tree t2 ON t1.id = t2.parent
                        WHERE t2.parent is null
                        UNION ALL
                        SELECT t1.id, t1.parent, CONCAT(t1.name, ' > ', t2.name)
                        FROM tree t1
                        JOIN cte t2 ON t1.id = t2.parent
                      )
SELECT name
FROM cte
WHERE parent = 0;

Output:

                    name
---------------------------------------------------------
root > branch 2 > leaf 1
root > branch 1 > branch 4 > leaf 2
root > branch 1 > branch 3 > branch 5 > branch 6 > leaf 3

fiddle

UPDATE:

WITH recursive cte AS ( SELECT t1.category_id, t1.category_parent,    t1.category_name::text
                    FROM s_category t1
                    LEFT JOIN s_category t2 ON t1.category_id = t2.category_parent
                    WHERE t2.category_parent is null
                    UNION ALL
                    SELECT t1.category_id, t1.category_parent,     CONCAT(t1.category_name, ' > ', t2.category_name)
                    FROM s_category t1
                    JOIN cte t2 ON t1.category_id = t2.category_parent
                  )
select

cte.category_name AS category,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand,
-- as category,
s_category.category_id as categoryID
-- as hierarchy,
-- as params,
-- as name,
-- as value,
-- as labels

from

public.s_product
left join s_product_image on s_product.product_id =             s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join cte on s_product.category_id = cte.category_id
left join s_producer on s_product.producer_id = s_producer.producer_id

where

s_product.product_id='133471'
and s_pricelist_generate

UPDATE2:

Alternative CTE - producing URL for each node.

WITH RECURSIVE
cte AS ( SELECT id, parent, name
         FROM tree
         WHERE parent = 0
       UNION ALL
         SELECT tree.id, tree.parent, CONCAT(cte.name, ' > ', tree.name)
         FROM tree, cte
         WHERE cte.id = tree.parent
       )


How can I increase performance for select in select statement The item table is like this

id    category_id    name    registered

I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items before 2013-05-01 and add that to count of 2013-05-01 's items. The result have to be grouped by registered and category_id. I have to do this in one query. This is my query

SELECT registered, me.category_id,
    COUNT( * ) + (select count(*) from items where me.category_id = category_id
    and registered < '2013-05-01 00:00:00') AS Value FROM items me

WHERE (
    (
      category_id IS NOT NULL
      AND
     (me.registered BETWEEN '2013-05-01 00:00:00' AND '2013-05-02 00:00:00')
    )
)
GROUP BY registered, category_id

It takes almost 28 secs to generate the result. How can i increase the performance.

Update
I am using pgSQL. The registered and category_id is indexed. There are 568814 rows. I will be also very grateful, if any one can tell me that my sql is correct or not.

Update
Sorry for late reply.

CREATE TABLE items
(
id uuid NOT NULL DEFAULT uuid_generate_v4(),
registered timestamp without time zone NOT NULL,
modified timestamp without time zone NOT NULL,
published timestamp without time zone,
owner_id uuid,
status_id integer NOT NULL,
category_id uuid NOT NULL,
location_id uuid,
title text,
description text,
quantity integer NOT NULL DEFAULT 1,
unit text,
price_low bigint,
price bigint,
currency text NOT NULL DEFAULT 'BDT'::text,
ip text,
attributes_json text,
from_mobile boolean NOT NULL DEFAULT false,
CONSTRAINT items_pkey PRIMARY KEY (id),
CONSTRAINT items_fk_category_id FOREIGN KEY (category_id)
        REFERENCES categories (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_location_id FOREIGN KEY (location_id)
        REFERENCES locations (id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_owner_id FOREIGN KEY (owner_id)
        REFERENCES users (id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT items_fk_status_id FOREIGN KEY (status_id)
        REFERENCES item_statuses (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)

SQL Fiddle

with items as (
    select registered, category_id
    from items
    where
        category_id is not null
        and
        registered < '2013-05-02'
), items_restricted as (
    select registered, category_id
    from items
    where registered >= '2013-05-01'
), category_id as (
    select distinct category_id
    from items_restricted
)
select distinct ir.registered, ir.category_id, "value"
from
    items_restricted ir
    inner join
    (
        select
            category_id, count(*) as value
        from items
        where exists (
            select category_id
            from category_id
            where category_id = items.category_id
        )
        group by category_id
    ) s using(category_id)
order by registered, category_id


revoking mysql table level privileges I have a mysql user account who has following Global privileges

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER,
SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EVENT,
TRIGGER ON *.* TO 'maintenance'@'host' IDENTIFIED BY PASSWORD 'password';

I need to revoke insert, delete or update privileges on the certain tables  for this user. So, I tried this:

mysql> REVOKE INSERT, DELETE, UPDATE ON  Hotels.AllHotels FROM 'user'@'host';

An I am seeing the following error

  ERROR 1147 (42000): There is no such grant defined for user 'user' on host 'host' on table 'AllHotels'

I am performing the above actions as the mysql root user.

Could someone help please?

Use this script to help you create the grant script syntax :
Run a root od mysql admin user.

  select 'grant all privileges on ', table_name, 'to
someuser@somehost;' from information_schema.tables where table_schema = 'database name' and
table_name not in ('table without access', 'table without access');

This will create an output that you can run after.

Just to replicate the error :

mysql> grant select on BASE_BIB.* to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use BASE_BIB;
Database changed
mysql> show tables ;

Now revoke select from a table form inside the BASE_BIB database:

mysql> revoke select on BASE_BIB.users from test123@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test123'

on host '%' on table 'users'

No the right way to do it :

mysql> grant select on BASE_BIB.users to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on BASE_BIB.users from test123@'%';
Query OK, 0 rows affected (0.00 sec)

No error now !!

Managing access in mysql can be quite dificult !!

Once you gave him database.* you cannot revoke access for an object that is in that class.
MySQL doesn't expand the Hotels.* wildcard to the individual tables
 The permissions tables store the granted permissions. Therefore, since you didn't actually grant anything on Hotels.AllHotels , there's nothing for MySQL to revoke.
In this case you need to do it granular form the start !

Remove all privileges on database, table, column levels, etccc.

Grant privileges to EACH table, except 'you choose'.
Grant privilege to specified fields in table 'you choose'.


I came up with a solution for your data set based on some work that I was doing and some modifications to the solution posted by Serge Rielau and Rick Swagerman on IBM's developerWorks.

Data Setup queries:

DECLARE GLOBAL TEMPORARY TABLE sample_data (id INTEGER, otherid integer, data VARCHAR(255)) WITH REPLACE ON COMMIT preserve rows NOT logged;
INSERT INTO session.sample_data SELECT 5059, 73831, '5103,5107' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 5059, 73941, '5103,5104,5107' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 5059, 73974, '5103,5106,5107,5108' FROM sysibm.sysdummy1;

Solution Select Query:

WITH
split_data AS
(
    SELECT
        id as group_by_1,
        otherid as group_by_2,
        data AS split_string,
        ','  AS split
    FROM
        session.sample_data
)
,
rec
(
    group_by_1,
    group_by_2,
    split_string,
    split,
    row_num,
    column_value,
    pos
) AS
(
    SELECT
        group_by_1,
        group_by_2,
        split_string,
        split,
        1,
        VARCHAR(SUBSTR(split_string, 1, DECODE(INSTR(split_string, split, 1), 0, LENGTH(split_string), INSTR(split_string, split, 1) - 1)), 255),
        INSTR(split_string, split, 1) + LENGTH(split)
    FROM
        split_data
    UNION ALL
    SELECT
        group_by_1,
        group_by_2,
        split_string,
        split,
        row_num + 1,
        VARCHAR(SUBSTR(split_string, pos, DECODE(INSTR(split_string, split, pos), 0, LENGTH(split_string) - pos + 1, INSTR(split_string, split, pos) - pos)), 255),
        INSTR(split_string, split, pos) + LENGTH(split)
    FROM
        rec
    WHERE
        row_num < 30000
    AND pos > LENGTH(split)
)
SELECT
    group_by_1 as id,
    group_by_2 as otherid,
    column_value AS data
FROM
    rec
ORDER BY
    group_by_1,
    group_by_2,
    row_num;

Results:

ID  OTHERID DATA
5059    73831   5103
5059    73831   5107
5059    73941   5103
5059    73941   5104
5059    73941   5107
5059    73974   5103
5059    73974   5106
5059    73974   5107
5059    73974   5108

Comments:

The solution select query can be modified to fit your specific result needs by including as many GROUP_BY_X rows (O to many) in the REC table definition and matching rows in the two unioned sub-selects.


replace all references to a drive inside the jobs to another drive I have a server where all my sql server backups are currently being saved to the G: drive.

Now there is no enough disk space on drive G: to keep backups for the last 5 days.
I have asked to add another 100 GB to this G: drive but that was not possible, so they gave me another drive instead, the I: drive with extra disk space, as you can see on the picture below.

Now I need to replace all the references to the G: drive in the sql server jobs below to point to the new I: drive.

Suppose I have 3 servers in the same situation, how can I achieve this by T-SQL?

You could try this cursor approach to read rows from dbo.sysjobsteps and execute sp_update_jobstep after doing a search/replace for the string you want to change.

This could also be improved to ONLY update job steps that actually have the string.

DECLARE @cmd VARCHAR(max)
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @step_id INT
DECLARE @command NVARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT job_id
    ,step_id
    ,command
FROM msdb.dbo.sysjobsteps

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @job_id
    ,@step_id
    ,@command

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @command = replace(@command, 'g:', 'I:')

    EXEC msdb.dbo.sp_update_jobstep @job_id = @job_id
        ,@step_id = @step_id
        ,@Command = @command

    FETCH NEXT
    FROM _CURSOR
    INTO @job_id
        ,@step_id
        ,@command
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR


Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index? Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index?

I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes.

When I try creating both a Clustered Columnstore and Nonclustered Columnstore
I receive error:

Msg 35339, Level 16, State 1, Line 7
Multiple columnstore indexes are not supported.

No, you can't:

CREATE TABLE dbo.cs_test (id INT NOT NULL)

CREATE CLUSTERED COLUMNSTORE INDEX cs_whatever ON dbo.cs_test

CREATE NONCLUSTERED COLUMNSTORE INDEX nc_whatever ON dbo.cs_test(id);

Returns the error message:

  Msg 35339, Level 16, State 1, Line 7 Multiple columnstore indexes are
  not supported.

The same result for multiple nonclustered columnstore indexes, as well.


Dedicated mysql server slow response I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ...

With pingdom.com test i have for
 server: 10 sec
 and simple hosting: 2sec

is there a problem in my mysql configuration ? I dont need change query limit for 2gb (2000M) ?

my.cnf

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

query_cache_limit       = 1M
query_cache_size        = 16M

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

With your initial feedback (you are using innodb), I can tell you that your innodb_buffer_pool_size is too small (2M), so most of your queries may be using disk instead of memory. As a rule of the thumb, for a dedicated server, the usual recommendation is reserving between 60-85% of the available memory for the innodb buffer.

Increase it by setting it in the my.cnf file. I have also added extra changes (as it can be seen below), mainly making the transaction logs larger and setting a lower durability by default. Plese make sure to follow the following procedure if you change the log file size:

Shutdown mysql
Change the configuration file, usually found at /etc/my.cnf
Delete the transaction log files, usually found at /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1. Do not delete anything else.
Start mysql again, it may take some extra time to recreate the log files

[mysqld]
# I am assuming you have 500M of memory spare for your data
innodb_buffer_pool_size=500M

# This option is ignored by modern OS, no need to set it
#innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K

# let innodb handle concurrency, comment this or it may create a bottleneck
#innodb_thread_concurrency=2

# Add a bigger transaction log, assuming you have 100MB extra disk for it
innodb_log_file_size = 50MB

# The following assumes that durability is not important, but it may provide
# a huge boost in perfomarnace
innodb_flush_log_at_trx_commit = 2

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

query_cache_limit = 1M
query_cache_size = 16M

# the following lines are duplicated, remove them:
#innodb_buffer_pool_size=2M
#innodb_additional_mem_pool_size=500K
#innodb_log_buffer_size=500K
#innodb_thread_concurrency=2

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

Additional comments:

You may tune the query cache to better manage your load, but only if you have mostly reads
Setting a lower durability may make you lose transactions in case of a crash or a loss of power
You have less than 1MB of indexes, please make sure that your query performance problem is not due to the lack of proper indexing (all tables have primary keys and secondary keys as needed).

Edit: for more automatic examples of my.cnf configuration, have a look at Percona's configuration wizard. For a full review of your queries and configuration, please hire expert mysql consulting services.


How to tell which windows group login I used when logging in via windows authentication I created two SQL Server Logins which correspond to two windows groups:

MachineName\MyAppAmdin
MachineName\MyAppUser

Then in the database, I created two users with the same name and mapped them to the logins.

In the windows server, I added my domain account MyDomain\MyAccount to the MachineName\MyAppAmdin group.

Now I can pass windows authentication via MyDomain\MyAccount.

The thing is, I would like to know exactly which windows group login I'm using, but I'm not able to find out how.

I tried:

ORIGINAL_LOGIN()
SYSTEM_USER
SUSER_SNAME
SELECT * FROM dm_exec_sessions

All the above return MyDomain\MyAccount, but what I need to know is if I was logging in via the group membership in MachineName\MyAppAmdin.

To sum up, my question is:

Is there a way to tell exactly which Windows Group Login (or user) the current connection is using?

Or is there any way I can check if MyDomain\MyAccount is associated with a particular user or login?

I know I can use C# or command to resolve if a domain account belongs to specific windows group, but we have some new IT policies, so I'm thinking of a way to achieve a similar result by using TSQL.

Windows Authentication in SQL Server doesn't work exactly like that. When you log in as a domain user who has an associated login, but also has access via a domain group that has an associated login, the access is determined by the combined DENY/GRANT permissions and SQL\DB role membership assigned to both the user login and the group login.

There is no concept of being 'logged in' as the group, the group is simply a container to provide access to a collection of domain users based on their membership in this group in Active Directory.

You can check the various access paths for a particular user by running this command:

EXEC xp_logininfo '<DOMAIN\user>', 'all'

This will list out the various access paths for a user, i.e. all the group logins and user logins this Windows account is linked to.


Where does [reflection.assembly] reside? In a disconnected DEV box, I attempt to load the reflection assembly, "Microsoft.SqlServer.SMO". Then I try to call for a SMO.Server object, like this

[Reflection.assembly]::("Microsoft.SqlServer.SMO") | Out-Null

Powershell responds with this error message

  New-Object : Cannot find type {Microsoft.SqlServer.SMO.Server]: verify that the assembly containing this type is loaded.

So I get it. I need to find a copy of the assembly, download it to a USB device, and upload it to my disconnected DEV server.  Where do I find the assembly to download?

https://support.timextender.com/hc/en-us/articles/210437663-Error-Could-not-load-file-or-assembly-Microsoft-SqlServer-ConnectionInfo-
Cause
You have not installed the SQL Shared Management Objects (SMO). They are needed to create tables and other SQL objects from TX.
Resolution
Download the SQL Shared Management Objects from the SQL Server Feature Packages and install it. The download sites are linked below - the file you need is called SharedManagementObjects.msi.
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
SQL Server 2017
Many thanks to Tobias Eld and my apologies to the net for not finding this resource before I posted this question.


How to Limit Access in a Database System I have a question related to the limiting of the access to a database network for the users that are allowed to log on to it.

The problem is that the databases are huge (they contain hundred of millions of records) and without a few precautions, letting the logged-in users do whatever they want creates chaos.

For instance, allowing a simple SELECT query to run without any constraints might unnecessarily clog the access to the databases for other users.

So far, the following recommandations have been made in order to limit the access:

Using the "SET QUERY_GOVERNOR_COST_LIMIT 900" clause in the query windows in order to limit the usage of the database up to a quarter of hour.
Limiting any SELECT queries using "TOP 15" clauses in order to limit the retrieval of records from those hundred milion records to a bare sample of fifteen records.
Using the "WITH (NOLOCK)" and "WITH (READUNCOMMITTED)" clauses in order to prevent the databases from being locked. Of course, there are other differences between these two clauses that have been already discussed elsewhere.
Using scheduled tasks that programatically kill any unatended connections at a specific moment of time when supposedly the users should have already closed all their work, so that the relational database management system does not have any unnecessary load left over the termination of the user interaction with it.

Are there any other suggestions that might affect the speed of the database usage?

Of course, most of the allowed usage is strictly limited to querying. Manual updating and inserting of records is very infrequent. The creation of temporary tables is not recommended and it is almost prohibited.

Any other type of usage can be remotely isolated by using other relational database management systems, where - through link servers - data can be brought in and manipulated without further restrictions.

Allowing even trusted users to login to what appears to be a production database is in my mind a big no-no. The solutions you mention above may provide you with a sense of security/relief but they their own have side effects.

I would be particularly worried about your statement

  Manual updating and inserting of records is very infrequent

If you have users who are updating/insert records then I am sure they are also deleting records most likely with no or a very limited audit trail.

If this was my database I would

Immediately prevent all users from accessing the database tables and running any SQL statements such as Select, Insert, Delete, Update etc
Sit down with the end users who are requesting data and determine exactly what data is required. Based on the end user requirements create a set of SPs that produce the required data.
A DB account should be created that would only have execute permissions on the SPs
If possible determine if the reports really have to be run in real time on a production database. If not I would suggest a snapshot be created on a regular basis on which the reports are run.
I would also ensure that the sa account has a strong password and is not used on a day to day basis and that the password is know to only 1-2 people.
Monitor the database over a period of time to ensure users have not gained additional privileges


How can I convert a string to a double precision in PostgreSQL? How can I convert a string to a double precision in PostgreSQL ?

I tried something like :

update points set latitude2 = cast(latitude as double) ;

where latitude is a string and latitude2 is a double. I just can't get it to work.

double is not a postgres data type:

select cast('324342' as double);

ERROR:  type "double" does not exist
LINE 1: select cast('324342' as double);
                                ^

but double precision is:

select cast('132342' as double precision);

| float8 |
| :----- |
| 132342 |

So try:

update points set latitude2 = cast(latitude as double precision) ;

if you prefer, you can use float * instead, because according to the docs:

  float with no precision specified is taken to mean double precision

select cast('132342' as float);

| float8 |
| :----- |
| 132342 |

db<>fiddle here

* or float(n) where 25<=n<=53 because that is also taken to mean double precision


Could not allocate a new page for database because of insufficient disk space Using the SQL Server Import and Export Wizard, I get this error:

  Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I'm trying to upload a series of 2GB files and while loading the 4th file, I continue to get the above error message even after I go to Database properties > Files and change the Filegroup's autogrowth to 2500 MB and maxsize unlimited.

What is the best workaround? This data will ultimately take up around 60-80GB.

Sql express database will top out at 10g. I believe this is similar to the error message that you get.

I ran into this problem once at a client and it took me a while to figure out.

what version of sql are you running?


SqlDeveloper: ora-12505 TNS listener does not currently know of SID given in connect descriptor Info

 connection name: ziv
 username:sys
 password: xxxxxxx
 connection type: basic
 role: sysdba
 host name : localhost.localdomain
 port: 1522
 sid: ziv

The QUAST is to connect to the database ziv using Sql Developer.

This database was created using dbca.

I am new to both Linux and oracle.

What I need to do... ora-12505 ^^

I think the problem is at bash

  [oracle@localhost Desktop]$ tnsping ziv1

   TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on
   09-DEC-2015 12:38:44

   Copyright (c) 1997, 2009, Oracle.  All rights reserved.

   Used parameter files:
   /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

   Used TNSNAMES adapter to resolve the alias
   Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
  (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1523)))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
   ziv1.localdomain)))
   OK (0 msec)
   [oracle@localhost Desktop]$ lssnrctl services
   bash: lssnrctl: command not found
   [oracle@localhost Desktop]$ lsnrctl services

   LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-DEC-2015
   12:39:59

   Copyright (c) 1991, 2009, Oracle.  All rights reserved.

   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
  (HOST=localhost.localdomain)(PORT=1521)))
   The listener supports no services
   The command completed successfully

lsnctl status:

I got a 3 LISTENER name LISTENER port 1521 ziv port 1522 ziv1 port 1523

lsnrctl start listener:

 [oracle@localhost Desktop]$ lsnrctl start listener
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-DEC-2015
 02:15:16
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 TNS-01106: Listener using listener name LISTENER has already been
 started
 [oracle@localhost Desktop]$ lsnrctl status ziv
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-DEC-2015
 02:15:32
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
 (HOST=localhost.localdomain)(PORT=1522)))
 STATUS of the LISTENER
 ------------------------
 Alias                     ziv
 Version                   TNSLSNR for Linux: Version 11.2.0.1.0 -
 Production
 Start Date                10-DEC-2015 02:05:28
 Uptime                    0 days 0 hr. 10 min. 4 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network
 /admin/listener.ora
 Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost
 /ziv/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
 The listener supports no services
 The command completed successfully

OK what now?

Did you:

configure listener properly with service-name...,
then start your listener with ~]# lsnrctl start <listener-name>;
and then check status of listener using ~]lsnrctl status <listener-name> ?


Allowing a user to view/edit only a specific table in a database with ~50 tables The database I'm working with has about 50 tables in it.

A single user requires edit permissions on one of the tables, but should not have access to any other tables.

I know how to deny her the ability to edit any of the tables:
properties > permissions then denying her all the options.

Using this method, she can still view the tables.

Is there a way I can make it so she can only view the one table?

It'll involve 1) removing the user from any database fixed roles like db_datareader (only public will remain), 2) creating a custom role in the DB, 3) granting the new role specific permissions on the one table, and 4) adding the user as a member of the new role. You'll also want to make sure public has no grants to user objects as all users in a DB are a part of the public role.


Can you have more than 65536 sessions on an Oracle RAC database? I put this same question on Oracle's community site but this site gets a lot of activity so I am posting here also. Apologies to anyone who is on both.
Other post: https://community.oracle.com/tech/developers/discussion/4482456/can-you-have-more-than-65536-sessions-on-a-rac-database
On Oracle 19c you cannot set the sessions parameter to more than 65536 so I believe that means that you can have at most 65536 sessions on a single node database.
If I had a two node RAC database could each node have 65536 sessions or are we limited to 65536 total for both nodes?
Bobby

Each Oracle instance in a RAC configuration maintains its own resources and sessions; you would be able to have up to 65536 sessions for each node in the cluster.
https://docs.oracle.com/database/121/RACAD/GUID-59DEC066-5743-4EDE-9A02-E8896005F658.htm#RACAD818


Speeding up index scan backwards query My app is making the following psql query, and it is running extremely slow:
SELECT COUNT(*)
FROM (
  SELECT 1 AS one
  FROM "large_table"
  WHERE "large_table"."user_id" = 123
  ORDER BY "large_table"."id" desc
  LIMIT 1 OFFSET 30
) subquery_for_count;

When I change the ORDER BY to ASC, it runs like 100x quicker. I have the default primary key index on id, and I've experimented with adding an additional index for the id in desc order, however it didn't seem to make a difference.
When I run Explain Analyze, I see that it is using an index scan backwards on the slow query (desc). I tried manually disabling index scans for my session, and found that the query ran in 40seconds instead of 2 minutes, which is a noticeable improvement.
Any idea on what I can do to try and improve the speed of this query when sorting by DESC? I've read that for b-tree indices, it should generally give you the same performance irregardless of sort order, but that does not seem to be the case.

Your query must be using an index on "id" to scan the index in the implied order, and then filtering out everything where "user_id" does not equal 123, stopping after it finds 31 rows which survive the filter.  Going in one direction it quickly finds 31 such rows, going in the other direction needs to filter out a large number of rows before 31 survive (because none/few of the rows starting at that end have user_id=123).
You could readily confirm this theory by doing an EXPLAIN (ANALYZE, BUFFERS) of the queries.
This is not fundamentally about the order of the index scan.  If you picked a value for 123 which had the opposite property (they all occurred at the logical end of the index rather than the logical beginning) then the situation would be reversed. Specifying DESC would fix the problem, rather than causing it.

Any idea on what I can do to try and improve the speed of this query when sorting by DESC?

Your query seems pointless.  Counting is not an order-dependent activity.  This is probably not your real query.  So who knows if our suggestions would transfer over to your real query?  The most straighforward fix for this query would be to build a multicolumn index on (user_id, id).  Then no rows would get filtered out one by one, as they would be removed in wholesale through the operation of the index.


Is it possible to create a PostgreSQL RLS Policy that depends on values from the table the policy applied on? I'd like to create a policy that depends on other records from the same table.
For example, let's say there's the following junction table:
 project_id | user_id
------------+----------
     1      |     1
------------+----------
     1      |     2
------------+----------
     2      |     2

I'd like to prevent a user from selecting records related to projects it's not related to, and allow the selection of the rest.
Something like:
CREATE POLICY project_user_read ON project_user FOR SELECT
USING(
      project_id IN(
      SELECT project_id
      FROM project_user
      WHERE user_id = current_setting('app.current_user_id')::bigint ---> I use runtime variables
   )
);

That will result this way:
SET app.current_user_id = 1;
SELECT * FROM project_user;

--  project_id | user_id
-- ------------+----------
--      1      |     1
-- ------------+----------
--      1      |     2

This obviously doesn't work because it's a circular condition.
Is there any way to implement such logic with RLS?

You could create a function that avoids the endless loop:
CREATE FUNCTION is_ok(project_user) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   /* avoid recursion if the "user_id" is correct */
   IF ($1).user_id = current_setting('app.current_user_id')::bigint THEN
      RETURN TRUE;
   END IF;
   /* otherwise, recurse */
   RETURN EXISTS (SELECT 1 FROM project_user AS pu
                  WHERE ($1).project_id = pu.project_id
                    AND pu.user_id = current_setting('app.current_user_id')::bigint);
END;$$;

Then create the policy like this:
CREATE POLICY project_user_read ON project_user FOR SELECT TO PUBLIC
   USING (is_ok(project_user));


Couchbase - Get document creation date Is there a way we can get a document's creation date in Couchbase (2.2.0).

We are currently not storing creation date in our documents, so wanted to know if couch stores that as part of document meta data.

We plan to query documents based on time, and currently the only option that we think will work is to change the server code to store additional creation date field as part of the document itself.

Couchbase does not track the created date in the meta-data of an object. You would have to add this to your application to add to each document.


difference between root and ALL PRIVILEGES I want to create a user with restricted grants compared to root.
If I do

GRANT ALL PRIVILEGES ON * . * TO 'non-root'@'localhost';

am I assigning all the same privileges as for the root user? Or are there some privileges in the root user that are not included in the ALL PRIVILEGES privilege?

For example: Is the new user able to create other users?

Otherwise I'll explicitly list the grant I want but this question is more for my learning.

To answer your question:

  Is the new user able to create other users?

The answer is NO, the new user is not able to create other users nor grant/revoke privileges to other users.

If you want to create a root similar user you must issue the following statement:

GRANT ALL PRIVILEGES ON *.* TO 'non-root'@'localhost' WITH GRANT OPTION;


Need help with reducing cost of view's group by I have a view which joins couple tables and returns a list of available book copies. My view creation code is as follows:

CREATE VIEW AvailableCopies AS
SELECT Product.title, CONCAT(Author.firstName, ' ', Author.lastName) as Author_name, ProductType.pType as Product_type, Product.lendable, count(BookCopy.bisbn) as Copy_count
FROM Product
INNER JOIN BookCopy on BookCopy.bisbn = Product.isbn
INNER JOIN Author on Product.authorId = Author.id
LEFT JOIN ProductType on ProductType.id = Product.typeId
WHERE BookCopy.Available = 'true'
GROUP BY Product.title, Author.firstName, Author.lastName, Product.lendable, ProductType.pType;

The problem that I cannot get rid of, is GROUP BY line. I cannot omit it, if I do, I get the following error:

Error: Column 'Product.title' is invalid in the select list
because it is not contained in either an aggregate function
or the GROUP BY clause.

I spent quite a lot of time online, trying to solve this issue in some cheaper way than adding all my selected columns into GROUP BY, but sadly no luck. Execution plan says that my group by statement costs 40-41% of whole process! Is there a way to solve this? This view returns 37,5K results, at the moment for education purposes, I am only returning TOP 20 in my nodeJs application, but it is really frustrating to wait 8-10 seconds for a simple page to load, with a size of 20 in a recordset. I am aware of caching, but I would like to keep this solution as a last option for now.

pastetheplan is not accepting my execution plan xml so I cannot provide it.

After view creation I call Select TOP 20 from AvailableCopies . My database is hosted on Azure, I was trying to find version, but I couldn't. But as it is Azure, I assume SQL server 2017.

I can't see your indexes, but something worth trying is to grab the copy count in a subquery or CROSS APPLY:

SELECT Product.title, CONCAT(Author.firstName, ' ', Author.lastName) as Author_name, ProductType.pType as Product_type, Product.lendable, BC.Copy_count
FROM Product
CROSS APPLY (
  SELECT COUNT(*)
  FROM BookCopy
  WHERE BookCopy.bisbn = Product.isbn
  AND BookCopy.Available = 'true'
  ) AS BC(Copy_count)
INNER JOIN Author on Product.authorId = Author.id
LEFT JOIN ProductType on ProductType.id = Product.typeId
WHERE BC.Copy_count>0;


Only Show Data For Min(woID) With my data I am only wanting to show the SUM(ISNULL(dolamt,0)) for the MIN(woID) - however my query shows the SUM(ISNULL(dolamt,0)) for every row.  Below is sample DDL -

Declare @Info Table
(
    customer varchar(500)
    ,dolamt decimal(10,2)
    ,pddate date
)

Declare @Custs Table
(
    customer varchar(500)
    ,active varchar(10)
)

Declare @WorkOrders Table
(
    customer varchar(500)
    ,woID int
    ,sitename varchar(250)
    ,companycost decimal(10,2)
)

Insert Into @WorkOrders (customer, woID, sitename, companycost) Values
('Customer A', 1, 'Site 1', '10.23'), ('Customer A', 2, 'Site 1', '23.33'),
('Customer A', 3, 'Site 1', '44.12'), ('Customer B', 4, 'Site 2', '44.33'),
('Customer B', 5, 'Site 2', '12.12'), ('Customer C', 6, 'Site3', '22.11'),
('Customer D', 7, 'Site 4', '22.11'), ('Customer D', 8, 'Site 5', '100.12'),
('Customer D', 9, 'Site 6', '23.14')

INSERT INTO @Custs (customer, active) Values
('Customer A', 'Yes'), ('Customer B', 'Yes')
,('Customer C', 'Yes'), ('Customer D', 'Yes')
,('Customer E', 'No'), ('Customer F', 'No')

Insert Into @Info (customer, dolamt, pddate) Values
('Customer A',  '3286.12',  '7/17/2016')
,('Customer A', '9665', '11/25/2016')
,('Customer A', '154.13',   '6/29/2016')
,('Customer B', '73575.37', '12/30/2016')
,('Customer A', '134491.15',    '9/30/2016')
,('Customer A', '112.5',    '11/30/2016')
,('Customer C', '1140.9',   '8/31/2016')
,('Customer C', '5512', '2/8/2016')
,('Customer C', '82323',    '7/31/2016')
,('Customer C', '450',  '10/23/2016')
,('Customer D', '6050.91',  '3/13/2016')
,('Customer B', '520.84',   '8/19/2016')
,('Customer D', '1554.35',  '8/31/2016')

Select
cs.Customer
,[Work Order ID] = COALESCE(wo.woID,0)
,[Company Cost] = SUM(ISNULL(wo.companycost,0))
,[Total Amt] = COALESCE((Select Top 1 SUM(ISNULL(dolamt,0)) FROM @Info nf WHERE nf.Customer = wo.Customer),0)
FROM @WorkOrders wo
RIGHT JOIN @Custs cs
ON cs.Customer = wo.customer
GROUP BY cs.Customer, wo.woID, wo.Customer
ORDER BY cs.Customer ASC

Which produces this result set

I want this to be returned result set (only update MIN(woID))

What do I need to change in my query so that I get the desired result set returned?

Here's another alternative which admittedly has an additional cost of ~0.005 but I find much easier to follow.

;WITH i AS
(
  SELECT Customer, dolamt = SUM(dolamt)
  FROM @info GROUP BY Customer
),
w AS
(
  SELECT Customer, woID, companycost,
    rn = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY woID)
  FROM @WorkOrders
)
SELECT c.Customer,
  [Work Order ID] = COALESCE(w.woID, 0),
  [Company Cost]  = COALESCE(w.companycost,0),
  [Total Amt] = COALESCE(CASE w.rn WHEN 1 THEN i.dolamt END,0)
FROM @Custs AS c
LEFT OUTER JOIN w ON c.Customer = w.Customer
LEFT OUTER JOIN i ON c.Customer = i.Customer
ORDER BY c.Customer;


MariaDB configuration for many updates, writes Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 million rows, and we are running updates every second. So I am trying to create an optimal configuration for writing. A simple update record sometimes need 2-3 seconds. Should I change something to increase update queries speed?

[mysqld]
# MyISAM
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY
innodb                         = FORCE
innodb_strict_mode             = 1
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve

# BINARY LOGGING
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS
max_connections                = 500
max_heap_table_size            = 32M
open_files_limit               = 65535
query_cache_type               = 0
query_cache_size               = 0
table_definition_cache         = 4096
table_open_cache               = 10240
thread_cache_size              = 50
tmp_table_size                 = 32M

# INNODB
innodb_buffer_pool_size        = 12G
innodb_buffer_pool_instances   = 12
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_log_files_in_group      = 2
innodb_log_file_size           = 1G

# LOGGING
log_error                      = /var/lib/mysql/srv.defrop.com.err
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
long_query_time = 2

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                 = 0

#custom
performance_schema = on

Example log of query

# Time: 190415 10:36:59
# User@Host: defrop_defdb[defrop_defdb] @ localhost []
# Thread_id: 2420  Schema: defrop_defrop  QC_hit: No
# Query_time: 2.404021  Lock_time: 0.000053  Rows_sent: 0  Rows_examined: 2
# Rows_affected: 1
SET timestamp=1555317419;
UPDATE `backlinks`
SET
`backlinks`.`crawler_id` = '10.0.0.28',
`backlinks`.`used_time`=NOW()
WHERE
`backlinks`.`campaign_id`=710
AND `backlinks`.`googlebot_id` IS NULL
AND `backlinks`.`used_time` IS NULL
LIMIT 1;

If the server is solely used by MariaDB then you can dedicate 16-24 GB of RAM to the DB service (now it is limited to the 12 GB). That allows to make bigger InnoDB pools. If your tables/indexes are fit into the RAM then your service need no expensive additional disk I/O.

You can reduce the max_connections variable to the reasonable value but that require clients to release the connections immediately after use. That can decrease the buffers consumption and overall peak RAM requirements.

Also you have to set up the join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size variables to the appropriate values to ensure your queries do not use on-disk temporary tables for the intermediate datasets. But that values shouldn't be too big as far as they are defined for each connection and cumulative RAM consumption can reach (max_connections * all_buffers) + innodb_buffer_pool_size and even more.

The good starting point is the
SET @@global.slow_launch_time = 0.5, @@global.slow_query_log = 1;
This command will start logging of the queries running for more than 0.5 second for further analysis. If the global variable log_queries_not_using_indexes is set to 1 then queries with no indexes will also be logged there. As far as your DB isn't too big it's more probable that slowdown is caused by poor indexing than bad server configuration.


Options for archiving old MySQL 5.7data I have an web application that has a 1TB MySQL 5.7 production database.
We are running into performance issues due to table data size. We are planning to archive old data from different tables. Checking for best options.
Requirement
Users do CRUD operations every day with minimal data so we plan to remove old data (1 year back data) to separate archive DB server and remove the old data from the production DB, which will make the users CRUD operations fast.
Challenge
Production DB to archive separate DB sync will happen every day. Production DB to separate Archive DB server sync only Create, Updated data should sync. Where users use separate archive DB server when required to see all data.

You need not move the data into another server just for performance improvement.
You could just move the data into another table (named sometable_20201023, assuming the archive/purge is happening on 2020/10/23 and you are OK with such a naming convention) and delete from the existing table. Since the original table is small, performance should improve.
Performance takes a hit when active data set does not fit in memory and disk I/Os are triggered. Having  inactive table(s) in your server will not cause any significant performance issue - your data dictionary size may go up by a bit.
In case you are concerned that your back-up/recovery time is also going up and want to fix it, this approach is not the best way. Moving the data to another server is best.


Why is this second query more expensive than the first? While reading Graph Databases book, I came across this excerpt:

I can't figure out why the second query is more expensive than the first.
Indeed, both filter on the Bob person.

May anyone explain it?

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID.  It also seems to assume that Person indexes PersonID and Person independently.

If this is the case, the first query comes back as

{INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID}
{INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice
and Zack get back FriendID}
{LOOP {INDEX UNIQUE SCAN Person on PersonID} for Alice and Zack}

Then the second comes back as

{INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID}
{TABLE ACCESS FULL PersonFriend FOR FriendID of Bob get back
PersonID}
{LOOP {INDEX UNIQUE SCAN Person on PersonID} for friend(s)}

This would be an elementary relational database design error.  With a relationship table like this you would always index all keys, and the performance profile for both queries would be indistinguishable (ignoring variations in value distributions, etc).

At this point it sounds like the author is trying to show that relational databases are intrinsically deficient.


What database should I use? I am writing a log analysis program in bash. As part of it, I am compiling a list of known errors, so that I can search and reference those errors as part of the known list. I originally was going to just write them in a .sh file and use source to reference it. Then I realised that I have hundreds of thousands of error messages and that's going to be REALLY hard to do without killing myself.

So. I want to use a small and simple database where I can store error messages, some data associated with them (bug report, who found it, how to fix it, etc.), and it has to be easily searchable. I don't feel like dealing with dynamic queries and all that jazz, I want to be able to use static queries.

I know I can use Access, Excel, and other things like that, but this is CLI Linux. There are just so many options out there that I'm a little overwhelmed as to what would be perfect for something like this. I don't even know what type of database I should use, I just want it to be lightweight and common so it can be supported after I eventually move out of my current role.

What database engine should I use?

PS: I know Python, Perl, Ruby, C (and it's variants), and pretty much anything else would be better than bash for this. My hands are tied.

All I can offer you at this point are my deepest sympathies!

Doesn't the person/company who/which is getting you to write a tool under this mind-boggling constraint realise that you'll have to do far more work for a far lesser return than if you used (as you say) Python or Perl or even Brainfuck :-).

Anyway, what you wrote rang a very old bell in my memory cells - I remembered having read about a database system written in shell which I thought might have been interesting for, say, a student project (sort of like those C obfuscation competitions that challenge people to be clever without actually doing anything useful).

I visted Christopher Browne's linux databases site and found what I was looking for here (the kshSQL DBMS link). I was unable to find the source code for that one, but it is based on another programming effort, shql, a DBMS written in bash - it's github, so you have the source code.

The only bright spot on the horizon for you IMHO, is that it was written by Bruce Momjian who is a major player in the PostgreSQL world and knows a thing or two about databases. I've never used it, but there's just a chance that you might be able to do something with this. Good luck, you're going to need it! :-)


dbcc cleantable batch size explanation I have a very large table with 500 mil rows and a Text column that I will be dropping.
In my Dev environment, I have dropped the column and began the reclaim process, but im not sure what the batch size on the “DBCC CLEANTABLE (MyDb,'dbo.LargeTbl, 100000)” statement actually does.

I have tried setting it to 5, expecting it to check the first 5 rows and end.  “DBCC CLEANTABLE (MyDb,'dbo.LargeTbl, 5)” and it took 28 hours.
So I restored the db, set it to 100,000 and it took 4 hours

Actual Question:
Does the batch size tell the dbcc cleantable how many rows to do at a time and continuously keep running 100K at a time till it goes thru all 500mil rows?
Or once I run the 100,000 do I have to run it again till I do all 500 mil rows?

On my second test, (running the 100K once) I was able to reclaim 30GB. Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB..

In addition to the great answer by armitage you probably do not need to use DBCC CLEANTABLE in your scenario.

You state

  Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB..

The best practices in the Microsoft documents says:

  DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space. Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.

It seems like time and space are your biggest goals.  Generally rebuilding an index is quicker (but more resource intensive) than a reorg.

As you are working on a Development server.

Just rebuild your indexes and you will get the benefits of the index reorg and the DBCC CLEANTABLE at the same time, and probably much quicker.

Note Rebuild and Reorganize are not the same thing:

Reorganize and Rebuild Indexes (Microsoft)
Rebuild or Reorganize: SQL Server Index Maintenance (Brent Ozar)
SQLskills SQL101: REBUILD vs. REORGANIZE(Paul Randal)


How do you list all items from all databases located in a specific tablespace in postgresql? I'm trying to clean out some old tablespaces in my postgres instance, and I can't seem to figure out how to list everything in a specific tablespace.

SELECT * from pg_tables WHERE tablespace ='cold_storage' returns items from the current database in the tablespace, but short of iterating over every database and querying within that database for the tablespace contents, I haven't figured out how to do this.

pg_tables seems to be db-specific, whereas (I think) tablespaces are global for a postgres instance.

pg_tables seems to be db-specific, whereas (I think) tablespaces are global for a postgres instance.

That's true. But there is no way of knowing what's inside of a tablespace without connecting to the specific databases and checking their catalogs. Iterating is the only option. Think of it like a glorified virtual storage system.

You may consider running pg_dumpall --schema-only and trying to parse that output if you're interested.


Should we manually failover again if the node which was originally a primary role and went down recovered? We have an SQL Server 2016 standard edition with Basic Availability Groups(BAG) setup between dbserver1 and dbserver2.
This is our first time to implement the BAG and wanted to know what to do in a failover scenario like below:

In the beginning, dbserver1 is acting as a primary, dbserver2 is acting as a secondary
dbserver1 went down and an automatic failover takes place, and dbserver2 has become the primary
dbserver1 is recovered and came up, acting as the secondary since dbserver2 is now the primary
Should we manually trigger a failover to the dbserver1 to make it a Primary again?

The reason is that any database objects (SQL agent jobs, maintenance jobs and such) only exist in the dbserver1, and if we keep dbserver2 running as a primary node, then it can potentially miss the jobs that were supposed to run.
Should we always target to have a particular node to be Primary (in this case dbserver1), or each node should be exactly the same including all the objects outside of Availability Groups so that any node can take primary role at any time, and should be kept being primary node once a failover occurred? (possibly using DBAtools to keep objects in-sync)

Should we manually trigger a failover to the dbserver1 to make it a Primary again?

Since you said that DBserver2 does not have the jobs and other server level objects you must failback the Basic Availability Group to DBServer1. Your worry about missing a job run is correct.
I would also like to say that this is a wrong configuration, you MUST have all jobs and logins created on DBServer2 ( which was originally acting as secondary) missing these would defeat the overall purpose of having HA/DR.

Should we always target to have a particular node to be Primary (in this case dbserver1), or each node should be exactly the same including all the objects outside of Availability Groups so that any node can take primary role at any time, and should be kept being primary node once a failover occurred?

You are correct both nodes should be exactly same. You need to re-create all the logins and jobs and any ssis packages. In short both the SQL Servers should be same in all the aspect


type recognition with MonetDB, *create* tables from CSVs I am confused whether you can copy records into a new table in MonetDB.

The official documentation seems to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows.

A StackOverflow answer seems to work for SQL Server 2000 with OpenRowset, though even they are not concerned about data-type inferencing.

Another answer here on DBA recommends BCP before calling BULK INSERT in Server 2000, neither of which is discussed in the MonetDB documentation.

Other answers also suggest that this is impossible in MySQL without external scripts.

Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious.

Indeed, the COPY INTO requires a CREATE TABLE beforehand. As of right now, there is no generic way to infer a schema. The main issue here is the massive diversity of CSV encodings.

That being said, the MonetDB.R connector does support schema "guessing" through the monetdb.read.csv function.

Also, I have been working on a Python script to automate the CSV loading task. The script (importer.py) is available from my CWI page. It works as follows:

./importer.py -h
usage: importer.py [-h] [--database DATABASE] [--port PORT] [--user USER]
                   [--password PASSWORD] [--header] [--yes]
                   files [files ...]

A "smarter" CSV loader for MonetDB, v.0.3, hannes@cwi.nl, 2014-05

positional arguments:
  files                One or many CSV files to be imported

optional arguments:
  -h, --help           show this help message and exit
  --database DATABASE  Database name to connect to
  --port PORT          MonetDB TCP port, defaults to 50000
  --user USER          MonetDB username, defaults to "monetdb"
  --password PASSWORD  MonetDB password, defaults to "monetdb"
  --header             set if given CSV file has a header in the first line
  --yes                if set, assume Yes on all questions

Usage example:

./importer.py --database acs ~/Desktop/adult.csv
Now probing '/ufs/hannes/Desktop/adult.csv':

OK, I am going to run the following commands on your DB acs:

CREATE TABLE adult5 (C1 DOUBLE, C2 STRING, C3 STRING, C4 STRING, C5 STRING, C6 STRING, C7 STRING, C8 STRING, C9 STRING, C10 STRING, C11 DOUBLE, C12 STRING, C13 BIGINT);
COPY 35469 OFFSET 1 RECORDS INTO adult5 FROM '/ufs/hannes/Desktop/adult.csv' USING DELIMITERS ',','\n','"' NULL AS '' LOCKED;

Proceed? [Y/n] y
imported 30162 rows from /ufs/hannes/Desktop/adult.csv in 3 second(s)

If there are any problems, please let me know.


Pivot on datetime with matching multiple column I have the following table with column school, state, subject and timedate.

I would like to have output with column school, state, act and english

filter subjects to only Act and English
pivot table to match school and state

I did tried but all queries are not work. Hopefully someone may help. Thanks.

Quickly:

select school, state, Act, English, History
from (select school, state, subject, timedate
      from data) as pivotdata
         pivot (max(timedate) for subject in (Act, English, History)) as P

Should work


Oracle regular expression replace in string ignoring first and last characters regular expression replace in string ignoring first and last characters if they matched

my try

SELECT REGEXP_REPLACE ('TLYU TK0T23T', '[^(\A.)]T[^(.\Z)]', 'Qq' ) contain FROM dual;

result

TLYUQqQq3T

expected result

TLYU QqK0Qq23T

Thanks in advance

This returns the answer you want:

SELECT REGEXP_REPLACE
('TLYU TK0T23T', '([^(\A.)])T([^(.\Z)])', '\1Qq\2' ) contain
FROM dual;

Bobby


Revoke write privileges vs setting database to readonly I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options.

Revoke write privileges:

REVOKE INSERT, UPDATE ON `mydb`.* FROM 'apache'@'localhost';
FLUSH PRIVILEGES;

Set database to read-only mode:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;

Are there any practical considerations for choosing one over the other?

I would choose Option 2

If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up.
Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant.
With Option 2, grants remain unchanged on disk and in memory.

Option 2 could also be written as
FLUSH TABLES;
SET GLOBAL read_only = 1;

That way, you only have to run SET GLOBAL read_only = 0; to get writes going again. FLUSH TABLES WITH READ LOCK does not halt InnoDB writes to Transaction Logs, Redo Logs, an Undo Logs. That's why SET GLOBAL read_only = 1; is your friend.
CAVEAT
In my early days a a DBA, I used to do this:
CREATE TABLE mysql.usercopy LIKE mysql.user;
CREATE TABLE mysql.root     LIKE mysql.user;
INSERT INTO mysql.usercopy SELECT * FROM mysql.user;
INSERT INTO mysql.root SELECT * FROM mysql.user WHERE user='root';
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.root;
FLUSH PRIVILEGES;

When I was done, I did this
TRUNCATE TABLE mysql.user;
INSERT INTO mysql.user SELECT * FROM mysql.usercopy;
FLUSH PRIVILEGES;

Then, I discovered SUPER.
EPILOGUE
Go with Option 2 as is, or change FLUSH TABLES WITH READ LOCK; to FLUSH TABLES;


How dependent are backups on msdb tables like backupset? I am trying to get some practice making a backup maintenance plan and restoring those backups. I have a script that churns through the most recent full database backup and any subsequent transaction log backups, all of which are in a single appended backup file.

My question is in two parts. First, is it possible to determine everything I need to know to restore a database from my backup file itself, without querying the msdb backup tables? Second, If I was to accidentally run sp_delete_backuphistory and wipe out all history, how bad of a day would I have?

Bonus question: How do expiration dates work when I have a backup that I continually append to? Does the expired data get deleted on subsequent backups, or do I need to create a new backup file, or something else? If I was creating a new file for every backup, I could simply check that files expiration date and delete it, but I am more confused with the appended backup sets.

If you have a single file with all of a set of backups in that single file, you should be able to use the command:

 RESTORE HEADER ONLY FROM backup device ...

This will provide you all of the details within that file, including the log file LSNs and other details you might need.

http://technet.microsoft.com/en-us/library/ms178536.aspx

The EXPIREDATE and the RETAINDAYS are just data that you include with the backup.  You have to decide how to use that information.  In an appended file, it is mystifying indeed. You could perhaps use the information to skip over backups that expired.  (For me, each backup is a separate file.) See Tibor Karaszi's comments at:

http://sqlblog.com/blogs/tibor_karaszi/archive/2012/07/08/backup-and-the-evil-retaindays-option.aspx


Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine.  Our main database server has 16 physical cores with hyper threading and 2 numa nodes.  We have set the maxdop to 16 for this machine.
Edit: I had previously set it to 8 and saw the same issues.
The mirror host has a much weaker cpu, so we had to set the maxdop to 4.
When we are running an intense query on the principle database, we sometimes get an error:
Unexpected job failure.: System.Data.SqlClient.SqlException (0x80131904): The query processor could not start the necessary thread resources for parallel query execution.

I am wondering if could be due to the underpowered mirror server running out of resources while it is trying to commit all the transactions that are being fed across to it from the principal server.
I'm not a DBA, and we currently do not have one on staff (so I guess I am the DBA), so a lot of these settings and errors are foreign to me.
Edit2:  This question is different from the one suggested because I originally followed the directions and scripts in that post to set my MAXDOP to appropriate values.

It sounds like the issue is your queries are exhausting the available threads of the new underpowered server, interestingly even with you tuning the MAXDOP setting. Please see this DBA.StackExchange answer for more details. You may need to limit your MAXDOP on certain queries further, or this could be a hint to up the server's provisioning to increase performance more.


SQL Performance Large Variable Varchar into Small Column Are there any performance issues placing larger varchar variable into small column?
I did not see any performance difference in the execution plan. Just want to validate.

Example,

create table dbo.Product
(
    ProductId int identity(1,1),
    ProductName varchar(25),
    ProductDescription varchar(255),

    constraint pk_ProductId Primary Key (ProductId)
)

Case 1 Large varchar(4000)

declare @ProductNameVar varchar(4000),@ProductDescriptionVar varchar(4000)

set @ProductNameVar = 'Table'
set @ProductDescriptionVar ='manufactured , oak table, round edges..'

INSERT INTO dbo.Product(ProductName, ProductDescription)
VALUES (@ProductNameVar, @ProductDescriptionVar)

Case 2 Small Varchars

declare @ProductNameVar varchar(25),@ProductDescriptionVar varchar(255)

set @ProductNameVar = 'Table'
set @ProductDescriptionVar ='manufactured , oak table, round edges..'

INSERT INTO dbo.Product(ProductName, ProductDescription)
VALUES (@ProductNameVar, @ProductDescriptionVar)

Is performance really your main concern here? Or are you just looking for an excuse to not bother making sure the parameter data types match the column data types? (This happens to be a common reason for just declaring everything as nvarchar(max).)

Your parameters should absolutely match the underlying column types. If one changes, you change both.

Declaring larger data types for variables that will never hold that much data is probably wasteful, performance-wise, since memory has to be reserved in case you update the variable later. But this performance difference (if it even exists) is completely irrelevant, IMHO.

The reason? If someone puts 26 characters into the first variable, the insert will fail. And the error message is useless:

  Msg 8152, Level 16, State 14

  String or binary data would be truncated.
  The statement has been terminated.

Imagine you have 10 columns of varying lengths, and 10 varchar(4000) parameters, and then you call this stored procedure 100 times, and get the error 20 times? Happy hunting!

Why invite errors that are preventable (or that can at least happen further up the chain)? You should be validating your input all the way up the stack:

Make sure the form field can't be more than 25 characters (HTML supports this);
make sure the client-side validation check that it is not more than 25 characters (JavaScript supports this);
make sure the .NET data type is declared as 25 characters; and, for the love of all things you deem holy,
make that parameter 25 characters.

Yes, that makes for more work when the length changes, but anything else is inexcusable IMHO.


detaching database results in error "SQL Server detected a logical consistency-based I/O error" I'm trying to detach a database and I'm getting this error :

  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:234; actual 0:0).
  It occurred during a read of page (1:234) in database ID 5 at offset 0x000000001d4000 in file 'D:\Path\MyDB.mdf'.
  Additional messages in the SQL Server error log or system event log may provide more detail.
  This is a severe error condition that threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (Microsoft SQL Server, Error: 824)

What is the problem with this database file ? Other DBs are working well, only this one gives me this error.

Torn page usually. Do you have backups? DBCC may be able to fix it though

The king of DBCC is Paul Randal: to avoid my copy/paste, read these from him

SQL Server 2008: Automatic Page Repair with Database Mirroring
Whitepaper

And search his site too for "logical consistency"