Select all children from infinite parent-child relations I have a comments table, and rows have parent child relation, which means any comment can have children (responses) and any child itself can also have children (infinitely).

All rows are inside one table, and root row's parent_id is 0.

Now I want to select all children (in any deep level) of a given parent_id

Any idea ?

Regarding the "SELECT", i.e. how to recursively get all the descendants of a parent, there a lot of answers in this question:

Find highest level of a hierarchical field: with vs without CTEs

Rolando's method uses variables and a procedure.
a_horse_with_no_name's method uses a recursive CTE. This is not possible in MySQL yet but you could switch to MariaDB 10.2 which has them implemented.

Hierarchical queries in MySQL

The blog post by @Quassnoi has detailed explanation about writing recursive queries in MySQL.

For the recursive DELETE, there is a simple method available in all MySQL versions:

Add a FOERIGN KEY constraint - if you haven't already - with the ON DELETE CASCADE option. Then a simple delete of a node will delete all its descendants.
You could modify the methods in the above links, to do a DELETE instead of SELECT.


Using collate: When to use and when not to use In one of my applications we had tsql like shown below

SELECT DISTINCT [Dblist].[DbName] AS [DbName]
FROM [Common].dbo.[Dblist]
WHERE dbname not in (
    SELECT [name]
    FROM master.dbo.sysdatabases )

For one of my customers it gave an error related to collation as the database Common (part of my application) has default collation sql_latin1_general_cp1_ci_as whereas master db has collation latin1_general_ci_as. I found a solution using collate and it worked. I want the solution to work on all collations.

SELECT DISTINCT [Dblist].[DbName] AS [DbName]
FROM [Common].dbo.[Dblist]
WHERE dbname not in (
    SELECT [name] COLLATE DATABASE_DEFAULT
    FROM master.dbo.sysdatabases )

Now I am confused. Should I use COLLATE in all queries which involve system databases?
When to use collate and when not to use?

Also, is the above way of using collate proper (like COLLATE DATABASE_DEFAULT)?  Here DATABASE_DEFAULT  is latin1_general_ci_as itself as it is the collation of master database. Then how did the solution work as they again don't match with Common database? I want solution which will work on all collations.

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases.

In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables:

SELECT c.name, c.collation_name
FROM sys.all_columns AS c
INNER JOIN sys.all_views AS v
ON c.[object_id] = v.[object_id]
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE s.name = N'sys' AND v.name = N'tables'
AND c.collation_name IS NOT NULL;

Results:

name                    SQL_Latin1_General_CP1_CI_AS
type                    Latin1_General_CI_AS_KS_WS
type_desc               Latin1_General_CI_AS_KS_WS
lock_escalation_desc    Latin1_General_CI_AS_KS_WS
durability_desc         Latin1_General_CI_AS_KS_WS

So here we have columns with two different collations inside a single system object. No database can have a DATABASE_DEFAULT that matches both...

You don't have any control over the collation of your customer's columns or databases, or the server collation. So really the only ways to resolve the conflict are to:

use a method that doesn't hard-code a specific collation (like DATABASE_DEFAULT)
hard-code some specific, compatible collation on both sides

Since the latter is more work, makes for more complex queries, and introduces more opportunities to turn seeks into scans, I think the former is really your best option.


Postgres 9.3: How to update row that has max value of a timestamp in addition to other selection criteria? I want to allow users to cancel recurring subscriptions. The easiest way to do this given the other infrastructure I've got is to set the recurring field = 0 for the user's latest appointment (datewise). So I need to update the value of field recurring only in the row that matches a specific user's latest appointment.

Here's what I tried:

UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = max(time);

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ...ments set recurring = 0 where id = 433 and time = MAX(time);

and

 UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = SELECT MAX(time) FROM appointments WHERE id = 433;

Here's the relevant part of the appointments table:

                                           Table "public.appointments"
      Column      |            Type             |                           Modifiers
------------------+-----------------------------+----------------------------------------------------------------
 id               | integer                     |
 time             | timestamp with time zone    |
 recurring        | integer                     |

Where should I go from here? Is it necessary to make this into a two part call?

UPDATE appointments SET recurring = 0
WHERE id = 433
AND time = (select max(time) from appointments where id = 433);

... is the answer. You just needed brackets.


Why did my indexes drop in size? I've got the following SP which I wrote to calculate the size of all the tables on my database

SELECT
    name,dbo.nice(rows) as [rows],
    dbo.nice(CAST(total-indexes as DECIMAL)/1024) as [data (MB)],
    dbo.nice(CAST(indexes as DECIMAL)/1024) as [indexes (MB)],
    dbo.nice(CAST(total as DECIMAL)/1024) as [total (MB)]
FROM (
   Select A.name,rows
   ,SUM(CASE B.indid WHEN 1 THEN B.used ELSE 0 END)*8 as [total]
   ,SUM(CASE B.indid WHEN 1 THEN 0 ELSE B.used END)*8 as [indexes]
   from sysobjects A (nolock), sysindexes B (nolock)
   where A.id = b.id and A.type = 'u' and rows > 0
   group by A.name,rows
) as [d]
order by total desc

I can't recall where I got this exactly. I wrote the outer part and found the inner part online somewhere.

Anyway, it does the trick. It calculates the size of my data by subtracting the indexes. I got a shock today when I ran the SP and compared it to a result from a few days ago

table        rows             data (MB)   indexes (MB)   total (MB)
MyBigTable   240,106,454      35,428      33,001         68,429       // 4 days ago
MyBigTable   241,442,655      57,128      11,559         68,688       // today

My data had jumped in size! After inspecting the SP source code and remember how it works I realise of course the data size did not change (much). What happened was the indexes drastically reduced in size.

What causes this? I didn't make an edits to the indexes. I didn't shrink the DB or run anything on it that should affect the indexes. Why did my indexes decrease so dramatically? And why didn't it affect the overall size of the database?

UPDATE:

I looked into my indexes and found one was seriously fragmented, at over 80%. I rebuilt it and now the data has jumped again. Here are the new stats.

table        rows             data (MB)   indexes (MB)   total (MB)
MyBigTable   240,106,454      35,428      33,001         68,429       // 5 days ago
MyBigTable   241,442,655      57,128      11,559         68,688       // yesterday
MyBigTable   241,705,941      35,581      32,538         68,119       // today

Three possibilities come to mind immediately:

Maybe someone dropped an index? You should create a query that also breaks it down by index. A row missing from those results would be much more obvious than numbers changing.
A filtered index, and a data change that substantially reduced the number of rows contained in the index.
Fragmentation - was a defrag or rebuild run recently against this table or any of its indexes? This could drastically reduce the number of pages used by an index, even if the number of rows and the index itself have not changed.

Also, why didn't it affect the size of the database? Because the database is just a container, and it doesn't auto-shrink just because you delete data or shift data around (unless you have auto-shrink set, which you absolutely should not do!). Think about your house: if you clean up the clutter in your basement, your house doesn't shrink! You don't want your database to do this either. All this does is cause the data file to grow again as soon as you add more data. What could be more wasteful than that? You freed up some space, temporarily, only to make user transactions wait while you use it up again. Yay?

In any case, your overall data size seems to have remained the same, so I postulate that two things happened: you added a million rows that contributed significantly to data size, and some index maintenance happened with reduced the index size substantially. In the end, we can't tell you what caused this, because we don't have access to your system. We can only throw wild guesses into the air, and you have to figure out which ones stick.


SELECT and UPDATE in a single query while updating selective fields of duplicate records only I have a MySQL table named Contacts_table with, say, the following contents:

ID          NUMBER          NAME            CITY
001         2222222222      John Doe        Los Angeles
002         3333333333      Rich Malfoy     Houston
003         4444444444      Harry Potter    New York
004         5555555555      Billy Bones     Boston
005         6666666666      Joe Sanders     Chicago

*ID: Primary key; auto-increment

*NUMBER: Unique key

And I am using PHP to read a comma-separated text dump of values for these fields, parse those values, and add them to the table. Say, a sample text dump has the following contents (shown here as table for clarity):

NUMBER          NAME            CITY
1234567892      Earl Grey       Salem
3333333333      Rich Malfoy     Sacramento
7656453248      Bill Gates      New York
5555555555      James McGill    Boston
6666666666      NULL            Baton Rouge

Now, as you can see, there are some entries (based on NUMBER) in my TXT that do not exist in my table, i.e. 2nd, 4th, and 5th records. Then there are others that already have a corresponding entry in the table but with possibly different values on other fields. What I want my code to do is update new entries (with NUMBER values 3333333333, 5555555555, and 6666666666 in this example). As for other entries, the code would only update those fields where the TXT dump has a non-NULL value. Thus, this is what the updated table would look like:

ID          NUMBER          NAME            CITY
001         2222222222      John Doe        Los Angeles
002         3333333333      Rich Malfoy     Sacramento
003         4444444444      Harry Potter    New York
004         5555555555      James McGill    Boston
005         6666666666      Joe Sanders     Baton Rouge
006         1234567892      Earl Grey       Salem
007         7656453248      Bill Gates      New York

Like I said, wherever there's an existing entry, the fields have to be checked for values and only those fields are updated for that record which have a non-NULL values in the corresponding TXT dump.

I have a whole bunch of INSERT ON DUPLICATE KEY UPDATE queries (basically, one for each entry in the TXT dump) which is going to be a lot if the dump consists of hundreds of records. Is there any way to build a single query to handle this task in a single transaction?

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example:

INSERT INTO Contacts
    (number, name, city)
VALUES
    (1234567892, 'Earl Grey', 'Salem'),
    (3333333333, 'Rich Malfoy', 'Sacramento'),
    (7656453248, 'Bill Gates', 'New York'),
    (5555555555, 'James McGill', 'Boston'),
    (6666666666, NULL, 'Baton Rouge')
ON DUPLICATE KEY UPDATE
    name = COALESCE(VALUES(name), name),
    city = COALESCE(VALUES(city), city) ;

Test at SQLfiddle


SQL Server Mirroring (with Witness) and Client Timeouts Can you explain what happens in this situation.

Imagine I have a Sql Server Mirroring set-up with 2 databases and 1 witness.

Database clients have in their ConnectionStrings the Principal= and Failover=.

Now imagine if the clients can't connect to Principal for some reason (e.g. Network error) but can for the Failover.

Will they automatically try the failover? What happens from the SQL Server perspective. What happens from the clients perspective?

Thanks.

When to failover is decided by the Witness server not but the Primary or Secondary servers in a mirroring session.

The Secondary Server will come online if the Witness Server cannot see the Primary Server. The Secondary server only gets transactions from the Primary server and nothing else. If the Primary Server stops sending Transactions the secondary server will just simply sit there in restoring mode and do nothing, But if Primary servers becomes invisible for Witness Server thats when the failover happens and the Witness server will make Secondary server to come online.

Actually this the whole purpose of having a Witness server(Auto-Failover). If the secondary server could make these decisions on it own we would not need a witness server at all :) .


How to measure the performance improvement that NUMA brings? I am testing how one of our stored procedure is working on SQL Server Standard vs SQL Server Enterprise editions.

I have created two virtual machines and install a SQL Server(SQL Server 2014) and restore a database on each instance. Then I have generated some test data (large volume but the same on each database) and started testing if there is an execution time difference.

I have been told that there should be 4x better performance on the Enterprise edition when a large volume of data is used because there is NUMA Aware Large Page Memory and Buffer Array Allocation.

So far, there is no such difference and the execution time is almost the same (a - or + second difference).

I cannot say I understand completely what is NUMA and how it works, but I guess the hardware NUMA is on as the following query returns me 0 and 64:

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

I am not interested in the software Numa.

Could anyone tell if such 4x optimization really exists and should I continue generated more data in order to see it?

This is the VM set up:

You can't on a host with only 1 node. Your query on sys.dm_os_memory_clerks shows you only have 1 physical node (0). The other node (64) is a logical node for Dedicated Admin Access (DAC).

In older versions of SQL Server that were NUMA supported instead of NUMA aware/optimized, it is possible to tank performance when running on large NUMA hosts. Consider this: you have a large server with 4-NUMA nodes (say HPDL980). Query executed from node-0 retrieves a large amount of data which gets cached in node-2. To access that data, you now do at least 2 hops across the CPUs and perform cache-to-cache transfers. They're REALLY fast compared to disk but still a LOT slower than pulling directly from node-0 where the query originated.

Versions of SQL Server that are NUMA aware/optimized will try to keep memory usage within the same local NUMA node to avoid the hops. Even if soft NUMA is further layered on top of this, SQLOS memory node still works with the physical layout presented by the OS. That way, you SQL Server will still optimize for physical local nodes even when they look like different nodes logically. You can also track remote node access from perfmon using the SQL Server:Buffer Node\Remote node page lockups/sec counter.

These are very highly descriptions plus it assumes existing knowledge of NUMA, SQLOS, SQL Server memory management and query execution details. Lots of good write-ups on these areas in the CSS blogs and if you're keen on how all this started way back in 2005, read up Slava's blog (http://blogs.msdn.com/b/slavao/). It's really old but that work laid the foundation for what you see today.


Impact of table compression on HA Synchronous-commit mode scenarios I am working with a HA SQL 2014 instance with Synchronous-commit mode.

I did some table compression analysis with sp_estimate_data_compression_savings and I learned that table compression can be very beneficial for the overall performance. My servers have very powerful CPU setup.

However, It is unknown to me how table compression impacts a HA Synchronous-commit instance. My guess is that since data is compressed, it makes it easier for Synchronous-commit mirroring.

What is the impact of table compression on HA Synchronous-commit mode scenarios?

I've been using AGs for 3 years now, with table compression, and have not seen any material impact.

The data shunted between the servers is all log transport, and that itself is compressed, the compression of the pages themselves doesn't really factor in. Your only concern would be if you were under CPU pressure already and then added on that little extra for the table compression.


Reloading .sql file from java into PostgreSQL - Windows I am trying to reload a .sql script file into PostgreSQL from my sample Java application.

Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs...

This is the code I am using:

C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f "H:/Data/samplescript_postgres.sql"

Can anyone help me? I am using PostgreSQL 9.2

Java code:

String path = "H:/Data/samplescript_postgres.sql";
final String cmd = "C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f " + "\""+ path + "\" ";

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

My pg_hba.conf file:

# TYPE  DATABASE    USER    ADDRESS         METHOD
# IPv4  local   connections:
host    all         all     127.0.0.1/32    md5
# IPv6  local   connections:
host    all         all     ::1/128         md5

This is probably not the best way to do what you want to do.  The "proper" way would probably be to execute the SQL commands in your file via jdbc.

That said, that's a long way from where you are now, and we can probably make what you are trying to do work.

The likely problem is that there is a space in the path to psql.exe.

When you use that particular format of exec, java uses a vanilla string tokenizer to split your string into an array, each entry being separated by a space.  So it's going to be trying to execute a command "c:/Program" and pass it parameters, the first of which is "Files/PostgreSQL/9.2/bin/psql.exe" and, um, that isn't going to go so well.

Use exec (String[]) instead:
http://docs.oracle.com/javase/6/docs/api/java/lang/Runtime.html#exec(java.lang.String[])

This method doesn't apply the tokenizer, because it's accepting an array, so it doesn't think it needs to.

Try this:

String path = "H:/Data/samplescript_postgres.sql";
final String [] cmd = { "C:/Program Files/PostgreSQL/9.2/bin/psql.exe",
       "-U", "postgres",
       "-d", "sampledb",
       "-f", path
     };

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

You can see that I've created the array manually.  For readability, I've put the option "-U" and the value "postgres" on the same line, but they're separated by a comma.


Dynamic query for stored procedure I need query of looping in stored procedure for table like this:

Table Matrix Report

| Nama  | Motor   |
|-------|---------|
| Abdul | Vario   |
| Abdul | Beat    |
| Mei   | Supra   |
| Leli  | Supra   |
| Budi  | Megapro |
| Ali   | CB150   |

Output :

| Nama   | Vario | Beat | Supra | Megapro | CB150 |
|--------|-------|------|-------|---------|-------|
| Abdul  | 1     | 1    | 0     | 0       | 0     |
| Mei    | 0     | 0    | 1     | 0       | 0     |
| Leli   | 0     | 0    | 1     | 0       | 0     |
| Budi   | 0     | 0    | 0     | 1       | 0     |
| Ali    | 0     | 0    | 0     | 0       | 1     |

I need dynamic query because if I added new data, they automatically sum up.

Actually you don't need a dynamic query, you need a pivot query:

SELECT Nama,
SUM(CASE WHEN Motor = 'Vario' THEN 1 ELSE 0 END) as Vario,
SUM(CASE WHEN Motor = 'Beat' THEN 1 ELSE 0 END) as Beat,
SUM(CASE WHEN Motor = 'Supra' THEN 1 ELSE 0 END) as Supra,
SUM(CASE WHEN Motor = 'Megapro' THEN 1 ELSE 0 END) as Megapro
SUM(CASE WHEN Motor = 'CB150' THEN 1 ELSE 0 END) as CB150
FROM MatrixReport
GROUP BY Nama;

This query will automatically sum up whether new rows are added or not.

This is a SQL query, not an SP. You need to learn more about how to create a stored procedure. Take a look at these Stack Overflow Q & As to help you in creating your first SP:

Beginners' guide to stored procedures with MySQL
Creating a Procedure MySQL
MySQL create stored procedure syntax with delimiter

You should also learn more about GROUP_CONCAT function in MySQL:

Aggregate (GROUP BY) Function Descriptions - GROUP_CONCAT
How to use GROUP_CONCAT in a CONCAT in mysql


How to understand Composite Primary Key when it comes to storage (in Mysql)? I can image how rows will be arranged in a single database page as primary key order (example an auto-increment key) after reading this post. But I wonder how it will in the case of composite primary keys. For example consider a pk(a integer, b integer), whether the rows are arranged as per column a or by column b in the database page?

(This answer is focused on MySQL; in particular, the InnoDB Engine.  JCole's blog (which you linked to) is valid for InnoDB, but might be overly technical.  I'll try to give a lighter answer.)

Think of a composite key this way...  Concatenate the keys together, then the BTree structure is just as if it had a single-column (though larger) key.

The order of the columns specified in any composite index matters.  For PRIMARY KEY(a,b), the rows will be in this order:

a b
- -
1 1
1 2
1 5
1 7
2 1
2 2
2 3
2 8
3 2
...

This means, for example, that WHERE a=2 will find all the rows "clustered" together.  But WHERE b=2 will find them scattered.

"Clustering" is also used here to say that the data is right next to the PK in the same BTree.

The block structure (16KB blocks in InnoDB) is controlled by whatever happens.  That is, there is no obvious place in the pair (a,b) where one block ends and the next starts.

In InnoDB, the PRIMARY KEY is always clustered with the data.  As a side effect, there is virtually zero overhead for that one KEY.  All "secondary keys" are separate BTrees that have the PK in their leaf nodes.

In the above example, let's say (for conciseness) that there were only 3 rows per Block:

Block 0:  (1,1),(1,2),(1,5)
Block 1:  (1,7),(2,1),(2,2)
Block 2:  (2,3),(2,8),(3,2)

Note that the breaks in a are unrelated to the Block splits.  (Also, this example may be misleading -- Blocks rarely have as few as 3 rows; Blocks do not necessarily have the same number of rows each.)


Replace Yes with 1 and No with 0 I have a data set and an example of the field is below. The values of these fields is "Yes" or "No". I would like to replace the value of "Yes" with 1 and the value of "No" with 0.
This file will be exported from Sharepoint each month and at the moment it is 50,000 rows and will increase each month. I have 48 fields which I would like to do the replace of "Yes" and "No".
Example of the field
t1.'Domestic Violence'n,

I'm using the below to accomplish what I'd like, but was hoping there is a better way to do this. Ideally, I don't want to create another column.
I am using SAS. For example:
 t1.'Domestic Violence'n as DomesticViolence,
      case when(DomesticViolence='Yes') then 1
        else 0
        end as Domestic_Violence,

I would like a more efficient way (less coding). With the above I'll have a column called 'DomesticViolence' (values of "Yes" and "No") and  a column called 'Domestic_Violence'. Ideally I'd only want the column called 'Domestic_Violence'.

If you do not want to return both Yes/Nos and 1/0s, then exclude columns like

t1.'Domestic Violence' as DomesticViolence

from your query and have your CASE expression reference the source columns rather than their aliases:

case
  when (t1.'Domestic Violence' = 'Yes') then 1
  else 0
end as Domestic_Violence


Batch Delete with checkpoint still causes log growth I have a large table (600 million rows,  60GB data) that I need to migrate to another table. This is for a few reasons:

The current table is a heap and I want to convert it to having a clustered index
I have a column which is time(7) that only really needs to be time(4)
The table has previous deleted columns that are still reserving space from what I can see (they are just marked as is_dropped in the metadata.

The best way I could come up with doing all this in the fewest operations (i.e. not having to rebuild the table multiple times) was to create a new table with the column definitions I want and copy the data across into the new table while deleting the old data, followed by a name switch. I've split this into batches in a loop to prevent the log file growing continously, however this doesn't seem to be working and after about 70million rows, the log file has grown to around 110GB and there is no space left on the server...
The database is using simple recovery and I have a checkpoint after each batch, which I thought was meant to stop the log file growing! Am I missing something, or is there some other reason my log file continues to grow? This is testing on a dev server I'm doing, so there are no other operations currently running on the table.
My query is:
WHILE 1 = 1
BEGIN
    DELETE TOP(1000000) FROM RunningData with (tablock)
    OUTPUT
        deleted.[Data],
        deleted.[Date],
        deleted.Time,
        deleted.Category,
        deleted.SystemID
    INTO [dbo].[TmpRunningData] (
        Data,
        Date,
        [Time],
        Category,
        SystemID
    )

    IF @@ROWCOUNT = 0 BREAK
    CHECKPOINT
    RAISERROR ('Moving to next batch', 0, 1) WITH NOWAIT
    waitfor delay '00:00:01'
END

I really want to be able to sort this table out, but I'm struggling to find a way of processing this table due to the size! Any suggestions on how I can stop the log growing, or another way to do it would be very appreciated, thanks! In production the database can go down for a weekend, so there should be plenty of time to do it.

First step is to ask SQL Server why the log grows. You should find the answer in the log_reuse_wait_desc column in the sys.databases catalog view. Being in simple mode, it shouldn't be log backup. Then take it from there.
And don't do it all in one transaction, since SQL Server can't truncate past the oldest open transaction.


why use PostgreSQL clusterdb instruction I have come across recommendation for 1C:Enterprise on PostgreSQL. It says that we should periodically use clusterdb command but with no details.
PostgreSQL documentation says: "clusterdb is a utility for reclustering tables in a PostgreSQL database", but what does it mean "reclustering"?
As I come from SQL Server world I might assume that it is the same as rebuilding clustered index, but is that true?

The advice to cluster all tables to gain performance is wrong, ignore it.
What benefits from clustering a table (that is, rewriting the table in the order of one of its indexes) are index scans on that index that select many rows. Other than that, clustering is just unnecessary pain.


Linked tables versus stored procedure performance in MySQL Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form).

Option 1 is to simply create a linked table and set that as the record source for the sub-form. This of course requires little work.

Option 2 is to create a set of stored procedures for inserting, deleting, updating and reading records from the MySQL table to/from a local temporary table. The sub-form's record source is then set to the local table and the appropriate form events are tied to the appropriate stored procedure calls. Obviously this requires a lot more work than option 1.

Now the obvious question is why ever go for option 2? Well, I've been experimenting with replacing linked tables with stored procedures and have found the speed increase to be considerable, particularly over a slow network. I've done a bit of detective work too and dug around in the MySQL log files and I've noticed that the linked table approach generates a shocking amount of SQL calls. This explains the performance difference I suppose.

Anyway I'm now considering replacing all linked tables with the stored procedures option (well maybe just the important tables). Has anyone else had this experience? Any thoughts or advice?

When I use Access, I use both techniques as some form of Command Query Separation (CQS)

That is, the form can read the base tables but all writes are via stored procedures.

In my case, I chose this for

security/audit reasons
easier to deal data integrity
re-use of write code for other clients

Note: I used a view for Access not a base table also for some security.

Edit, for clarity, after comments:

"Read only" refers to server-side permissions that are transparent to Access. Access would try to write if we let it (instead of intercepting the call and invoking a stored procedure), and we'd get an error from the DB engine.


Copy huge amount from 1 table to other I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail.

So I made a query to do the import (Also using the build in Import tool) like that

SELECT * from TransactionEntryN
WHERE TransactionEntryN.TransactionEntryID
NOT IN (select TransactionEntryID FROM TransactionEntry)

The problem is that this operation takes 13 min. to copy just 50K records and I have 16 Million records there, it would take me a week to finish that...

Is there any faster way to do it?

btw the primary key TransactionEntryID is a uniqueidentifier that may slow it down? (I can't change it I'm just wondering if that the issue.

If you want the second table to contain the same data as the first table, why not simply wipe the table out and replace it?

BEGIN TRANSACTION;

  DROP TABLE TransactionEntry;

  SELECT * INTO dbo.TransactionEntry FROM dbo.TransactionEntryN
    -- other WHERE clauses?
  ;

COMMIT TRANSACTION;
-- create indexes / permissions etc.


PostgreSQL - Select only newer value I have this table
CREATE TABLE public.element (
    id serial NOT NULL,
    elementname varchar(25) NOT NULL,
    element_sequence int4 NOT NULL,
    element_type varchar NOT NULL,
    creationdate timestamptz NOT NULL,
    validitystart date NOT NULL,
    validityend date NOT NULL,
);

What i need is to group by element_name and element_sequence and extract only the newer of every element.
So for example in a table like this
id|element_name|element_sequence|element_type|creationdate       |validityend|validitystar
--|------------|----------------|------------|-------------------|-----------|-------------|
 1|ab>cd       |               1|simple      |2020-06-18 09:09:00| 2020-06-20|   2020-06-18|
 2|ab>cd       |               1|simple      |2020-06-19 10:02:13| 2020-06-20|   2020-06-18|
 3|ab>cd       |               2|composite   |2020-06-19 10:02:15| 2020-06-20|   2020-06-18|

only id 2 and 3 should be extracted because 1 and 2 have the same element_name and the same element_sequence but id 2 is newer
Real table have more column, but for this example i used only the main

maybe:
with latest as (
select element_name, element_sequence, max(creationdate) max_date from table group by element_name, element_sequence
)

select * --your column list
from table t
join latest lt on (lt.element_name = t.element_name and lt.element_sequence = t.element_sequence and lt.max_date = t.creationdate)

or
 select * from
 (
 select element_name, element_sequence
, row_number() over (partition by element_name, element_sequence order by creationdate desc) lp -- you can add other columns
  from table
  ) d
    where lp = 1


Okay to run sp_refreshview against all views on a regular basis? Microsoft SQL Server has a system stored procedure called sp_refreshview which updates the metadata of the view that's passed in.

Are there any downsides to running this procedure regularly on all of my views? (Such as performance statistics being affected, etc?)

Could I run it nightly, hourly, every few minutes?

If there's no repercussions in doing so, it seems like a good way to ensure my views metadata stays up to date when there's potential for the underlying entities they reference to change.

there's potential for the underlying entities they reference to change

This is the problem, and you should solve it at the source.

If you are updating objects in the database frequently enough that you have to automate frequent metadata corrections, there is probably an opportunity for better processes.

And you shouldn't be updating tables without also updating the views that reference them. So the views should become part of your schema modification workflow.

You can use WITH SCHEMABINDING on the views, which will explicitly enforce  that you can't modify referenced objects without knowingly removing the schema binding. (As an added bonus, your views can't use SELECT *, which is likely part of the problem here).

I wrote about this in the following article:

Benefits of SCHEMABINDING


Get row with max date from a joined table I have this schema of dossiers having 1 to N pages, and each page has a date:

CREATE TABLE dossiers
(id INT AUTO_INCREMENT,
code VARCHAR(50),
 PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE pages
(id INT AUTO_INCREMENT,
name VARCHAR(100),
date DATE,
hour TIME,
dossiers_id INT,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO dossiers (id, code)
VALUES (1, 'A1'),
(2, 'A2');

INSERT INTO pages (id, name, date, hour, dossiers_id)
VALUES (1, 'page1', '2018-09-01', '17:00:00', 1),
(2, 'page2', '2018-09-05', '17:00:00', 1),
(3, 'page3', '2018-09-10', '17:00:00', 1),
(4, 'page4', '2018-08-20', '17:00:00', 1),
(5, 'page1', '2018-09-01', '17:00:00', 2);

I want to select all the dossiers alongside the page with its maximum date.

This is what I tried (error):

SELECT *
FROM dossiers d1
INNER JOIN pages ON pages.dossiers_id = d1.id
WHERE CONCAT(pages.date, ' ', pages.time) = (SELECT MAX(GROUP_CONCAT(date, ' ', time)) FROM pages WHERE dossiers_id = d1.id GROUP BY dossiers_id)

I need to get the A1 dossier along with its "page3" page, and the A2 dossier with its "page1" page.

I'm sure it's not as twisted as I've done, but I can't figure it out.

Suggestions?

Link to the SQL Fiddle here.

A solution is to do an extra LEFT OUTER JOIN with 'newer pages', and filtering those with no newer pages:

SELECT d.*, p1.*
  FROM dossiers AS d
  INNER JOIN pages AS p1
    ON d.id = p1.dossiers_id
  LEFT OUTER JOIN pages AS p2
    ON d.id = p2.dossiers_id
   AND (p2.date > p1.date OR (p2.date = p1.date AND p2.hour > p1.hour))
  WHERE p2.id IS NULL

SQLFiddle


Tracking database and table size growth Is there way we can get the table size and DB size growth from last one month day-wise?

We are able to give the current size of DB and table size.

The only way you can do it properly is storing those two numbers at appropriate intervals (say, daily) somewhere.  There is no history kept.

With some limitations, you can also compare the sizes of your daily backups, until you start collecting the above mentioned metrics.


Database performance for determining entities assigned to users I am interested in insight in improving performance in the following instance:

I am currently working on an application that has a hierarchical entity structure where, depending on a user’s level within the organization, they would have an assignment to specific entity at a specific level – and thereby have access to all their children (in this case buildings within districts within regions throughout the U.S.). Most of the super users will have upward of 20,000 individual entities in their portfolio, some with as many as 40,000.

Instead of going into great detail, let’s suffice to say that a good bit of logic is needed to determine all the entities a user has access to. This logic is currently handled using a table function that is used in 95% of the stored procedures. The average stored procedure takes no more than 1 - 2 seconds to run. BUT, in an ASP.Net page that makes calls to 10+ different stored procedures, the performance hit quickly snowballs into 20+ seconds.

As an alternative, we were thinking of only calling this table function once (upon log in) and storing the results in a table (after clearing out any previous values for the same user). We would then have all the stored procedures reference this new table instead of the table function. A test revealed that a page which took 15 seconds to load could render in less than 3 seconds when selecting from the new table.

For example:

User logs in
system deletes all entities for the user in the table
system inserts all the user’s entities then
system sends them on their merry way and no longer runs the table function for that current session

Our concern is that, with the potential for hundreds of users consistently logging in and out of the application, deleting from and inserting into this table so often could result in a significant performance hit itself due to row level locking. Has anyone else used a SQL Table in such a manner? If so, should we be concerned with low performance due to the constant inserting and deleting from a single table.

The short answer...

This has the hallmarks of solving the wrong problem with the wrong solution. It sounds like you're looking for a workaround to a fundamental design flaw, introducing an entirely new set of problems on the way.

An attempt at a longer answer...

It's almost impossible to offer any practical advice without a better understanding of the database, the application and interaction between the two. That said, there are one or two warning signs.

Logic in a table function

It sounds like you have a hierarchy of entities, which could be represented in a relational model and navigated in a set oriented fashion. If complicated logic is required in the database to navigate the hierarchy, the model is probably wrong.

Do you have a relational database or an object store/bit bucket?

Your question refers to entities, rather than tables and records. Did you build an object model and dump it to tables, rather than map your objects to a relational model?

How much data are you returning to the user?

A 2 second stored procedure call could be sub-optimal, accessing a sub-optimal model or returning too much data. Requiring 10 calls to this procedure suggests it could well be all 3. Navigating a 40k record graph is not big data territory, unless you're application is loading 40k records from the database to determine what's navigable.

You already have a better model

Your workaround may actually be the solution, if applied differently. If the output from your table function is a model which works, perhaps it should be a permanent part of the model. Instead of building this temporary table every time a user logs in, why not keep the data in that form?


PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1 Is there any way to reset all the sequences of tables, when truncate a table on cascade.

I already read this post How to reset sequence in postgres and fill id column with new data?

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

It work only for one sequence, but my problem is to restart all the sequence of the truncated tables.

Consider when I use TRUNCATE sch.mytable CASCADE; It affect 3 related tables, which mean three sequences, Is there any solution to restart this sequence in one shot.

The TRUNCATE statement has an additional option RESTART IDENTITY which resets the sequences associated with the table columns.

TRUNCATE sch.mytable RESTART IDENTITY CASCADE;

If CASCADE is defined, then the sequences of all affected tables are reset.


Index Maintenance on Indexed View using Ola Hallengren Script I’m using Ola Hallengren's below script in my environment and it fails with the error:

USE DBA
EXECUTE dbo.IndexOptimize
    @Databases = ‘USER_DATABASES’,
    @FragmentationLow = NULL,
    @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = ‘ALL’,
    @PageCountLevel = 0 ,
    @LogToTable = ‘N’

  Msg 50000, Sev 16, State 1, Line 153 : Msg 2706, Table ‘_abcd’ does not exist. [SQLSTATE 42000]

‘_abcd’ actually is a view and the stored procedure is unable to update statistics on view and it fails.
we've indexes on this view
Please guide me how can I solve this issue

I run Ola's scripts from SQL Agent, and the jobs that use them are owned by an ID that has sysadmin server role.  You could also ask Ola for his thoughts.


Query For All Constraints in a SQL Database Diagram Is there a way to show constraints in a SQL Server db diagram?

Like for example, in a card number, it shows there is a CHECK constraint etc..

In SSMS, if you expand the tree of tables for your database, then expand the Columns folder, the icon next to a column will be a silver/gray key if it's part of a foreign key relationship, but unfortunately it won't show you the column to which column it's mapped.

You could also create a new diagram in SSMS by right-clicking the "Database Diagrams" folder underneath your database in the tree and choosing "New Database Diagram." You will get lines between the tables where foreign keys exists.

You could also use third-party tools to reverse engineer a diagram from your DB schema, like Microsoft Visio or Sparx Enterprise Architect.

I'm not sure SSMS Express supports these things, so you might be out of luck with anything fancy/visual.


SSAS Modelling - many to many with one dimension I'm having bit of a hard time modelling an OLAP cube.

My analogy is this: I have a list of students. Each one has one Major subject of study, and zero or more minors, up to five in total.

The raw data looks like this:

Student ID    | Major   | Minor 1    | Minor 2 | Minor 3 | Minor 4 | Minor 5
-----------------------------------------------------------------------------
12345           English   French       Null      Null      Null      Null
23456           French    Engineering  Null      Null      Null      Null
34567           Math      French       Biology   Physics   Null      Null
45678           Physics   Null         Null      Null      Null      Null
56789           French    Math         Physics   Chemistry Null      Null
67890           Math      French       English   Biology   Null      Null

Now as you can see, the subjects are all common, just that for some students, some are majors, the others are minors (where they have minors). This means I'd have a dimSuject dimension.

I can map this like so:

factStudent -> dimSubject

Such that I can drop a Measure that's a count of the Students, and see a list of Majors, with the number of students in each major.

What I'd like to do, is to be able to drop a dimMinor, which will drill down the majors into the count of each minor. Preferebly dimSubject is a unique reference of all subjects being taken, regardless if it's a major or minor. I have considered an intermediate factMinor table to store a reference to the student, and a reference to a subject, but does this maintain granularity? I couldn't also figure out how to use the intermediate fact as a dimension for drilling down.

I would normalize the fact table into this form:

StudentId Level    Subject
34567     Major    Math
34567     Minor 1  French
34567     Minor 2  Biology
etc.

and add the level as a dimension.


Indexing nvarchar(128) in SQL Server Have no idea how indexing works in SQL Server.

Working with a SQL Server 2012 Intel Xeon 2.2 GHz dual core 8 GB RAM.

Will indexing a nvarchar(128) in addition to int primary key cause any performance issue for a 1000 row table which has a nvarchar(max) as one of its columns?

This will be part of website that gets average 3000 hits a day.

When considering the impact of new index, the important things to think are how often rows are added to the table, is the indexed field getting updated (often) and how many distinct values there are in that field (selectivity).

For table this size, it's probably not going improve the performance that much, but if you're getting deadlocks it might help.

If you create the index, after it you can monitor the impact from "Index Usage Statistics" -report in SSMS:

http://blog.sqlauthority.com/2014/09/19/sql-server-ssms-index-usage-statistics-physical-statistics-user-statistics/

From there you can see the number of times it's being used (scans & seeks) and the number of times it was needed to update.


How I can check whether 2 databases despite being in different postgresql servers of versions are the same? I copied an existing posttgresql database from one server to another. The source of my data is a postgresql 9.6 whilst the newer server is in postgresql 11.
So I want to know how I can check that both databases have the same schema and data in a fast and easy way?

You cannot do that in a fast way.
The best you can do is to dump both databases using pg_dump from v11 and compare the files with diff.
If you want to do that just to ensure that the migration was successful, don't. If you don't trust PostgreSQL's upgrade process, why should you trust pg_dump?


How can I only load one function from an extension? In PostgreSQL, I am loading the uuid-ossp extension so I can use the function that creates random UUIDs in my queries:

create extension if not exists "uuid-ossp";

However, as well as making available the one function I need (uuid_generate_v4), it also makes nine other functions that I don't need available:

(Using \df on the command-line shows the same data). Is there any way I can, using programming terms, "import" just the uuid_generate_v4 function into my "namespace", without importing the ones I'm not using?

In PostgreSQL 13, the gen_random_uuid() function is now available globally, with no need to use the pgcrypto or uuid-ossp extensions.
It is still not possible to only load one function from an extension, but for this common case, it's possible to generate random UUIDs without having all those other UUID-related functions in scope.


I have a database design for an E-commerce website. Need to know how to display related product for each specific product For example: A customer is viewing a product on the website, say a laptop.
There's a section on the left where i need to display products from the database that are associated with this particular product. This might be a charger or an external hard disk, etc. but only one which WILL go with this specific laptop (Meaning the charger HAS to be compatible with the laptop) I don't understand how to relate the products. Thanks in advance!

You should create a table to hold the used-by relation between the, in this case, laptop and loader:

create table usedby (
parentid number not null,
childid  number not null);


How to use db2look to get the ddl of sequence? I was wondering how to use db2look to get the ddls of sequence.

db2look -d my_db_name -z my_proc_schema - I tried this but it is not showing any results.

Is there an option to directly mention the sequence name ?

You want to use the -e option. That will export sequences along with a few other things. Depending on your sequences you could either use the -z option for schema or the -a option for all schemas. You may also want to use the -o option to output to a file.

We tend to use the following a lot where I work

db2look -d my_db_name -e -o my_db_name_look.sql

  EDIT: Based on the comments you have below, you can use db2look, or you could also use the system catalogs to retrieve what you need.

  Why not run the following:

select seqschema,seqname from syscat.sequences where cycle = 'N'

  That will retrieve those same sequences for you, but through querying
  and you won't have to use the db2look utility to retrieve everything
  you want. Of course if you want the DDL, you'd still have to use
  db2look to do that, but at least you can then use -z better to narrow
  down you schemas or do a search to find the names that came from your
  query.


Tables for two type of users in database. How to solve this problem? This is the Entity Relationship Diagram I have created for my web application (based on Oracle APEX project).
Two type of users can sign up and login and each user has his/her own profile and privileges (Webbeheerder & Expert).

But the problem is how can I make a table called "TypeUser" for example and when the user wants to sign up, he will be asked what kind of user he/she is. So no separate sign up/login pages for each group/type user.
I know there must come a new table, but I don’t know how should I exactly solve this.

I'll appreciate your help and suggestions. Thanks a lot.

PS. Any other suggestions about this data model of a web application where Web masters can share the link of their website and ask Web designers for feedback. Web designers can comment on a link and rate it. Each of these user groups can view the other's profile ( so there's also a dedicated profile page)? Improvements?

Thanks again!

Edit

Incorporating the  the answer of LironCareto  results in the following diagram:

Why do you have two separated tables? Your model is faulty by design. You don't need in this case two different tables. Just create one table for user accounts and a different one for user account type.


Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts Normally in java or C# , I would use a following construct when try and catch errors.

Bad Exception Handling in C# and Java

try
{
  // do something
}
catch(Exception ex)
{
}

Good Exception Handling in Java and C#

try
{

}
catch(ArgumentException ex)
{
  log(ex);
  // java throw
  throw ex;
 // in C#, throw only, is better since it keeps stack trace
 throw;

}
catch(Exception ex)
{
  // this is catch ALL others block
  log(ex);
  // java throw
  throw ex;
 // in C#, throw only, is better since it keeps stack trace
 throw;
}

In C# and java , I would use logging libraries.
java : log4j,
see others
https://stackoverflow.com/questions/3042450/what-log4j-alternative-logging-libraries-are-available

.Net : log4net, Nlog etc

In PL/SQL

Bad Exception Handling

BEGIN
-- Do Something
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN
   null;
   WHEN OTHERS THEN
   null;
END;

Good Exception Handling

BEGIN
-- Do Something
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN
   LOG
   RAISE

   WHEN OTHERS THEN
   LOG
   RAISE
END;

a simple google search brings log4plsql package : http://log4plsql.sourceforge.net/

Are there any other libraries?
Are they prevalent in PL/SQL programming?

Oracle Documentation for Error Handling does not give hints about logging.

It gives following sentence and uses dbms_output.put_line for logging:

  "Sometimes, you want to reraise an exception, that is, handle it
  locally, then pass it to an enclosing block. For example, you might
  want to roll back a transaction in the current block, then log the
  error in an enclosing block."

Prevalent?  No.  It's not uncommon, however, to write a procedure like so:

create or replace procedure write_to_log
  (p_entry_point in varchar,
   p_log_type    in varchar,
   p_message     in varchar)
as
pragma autonomous_transaction
begin
  insert into my_log_file
    (entry_point, log_type, message, log_timestamp)
  values
    (p_entry_point, p_log_type, p_message, systimestamp);
  commit;
end;

Then you can add calls to write_to_log where needed throughout the code.


MySQL/MariaDB: Scaling 10 millions database in cluster This is for SaaS app (currently in MySQL-InnoDB). Each app contains about 150 tables. The database size is not big, but the number is high.

For each database, InnoDB create directory in /var/lib/mysql. Because too many database, I choose to have individual database per app to prevent file system bottleneck (Performance degrading badly if too many files/directories exist under a directory).

I will upgrade once it reach 5000 databases.

I need to know if MariaDB cluster or MySQL cluster able to deal with 10 millions database or more, or is there any other solution?

10 million databases is too many to have in one cluster and will likely hit a bunch of internal limits and be unable to balance workloads. You should look into containerizing the databases so you have one small DB associated with each client.


Install specific older version of postgresql centos I need to rebuild a production system with same version of installed packages.

It's a Centos 5.11 and it was using this repo http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm to install postgresql-server postgresql postgresql-odbc postgresql-plperl postgresql-contrib and postgis.

That repo doesn't exist anymore so I found this http://yum.pgrpms.org/8.3/redhat/rhel-5-i386/pgdg-centos-8.3-7.noarch.rpm that's versioin 8.3-7 and I need 8.3-6.

I can't seem to find the old version I need on the official website yum.pgrpms.org/repopackages.php

I don't know if old mirrors for the version I want exists or if I need to download each package individually matching the version that was listed on that one repo.

You probably want to install from the PostgreSQL YUM Repository as it has replaced both of those repositories. The repo rpm for CentOS 5 i386 is here for download.

The difference in the version on that particular package are from changes to the repository URL, and not changes to the versions of the related packages.

The last version of PostgreSQL 8.3 (8.3.23) and related packages are available there for use but that version and its packages are no longer supported.

If you need to build a very specific version of those packages outside of the last versions in that repository, you can always checkout the pgrpms git repository and rebuild from the .spec files there.


SQL 2016 AlwaysOn Database Health Level Detection I am researching SQL Server 2016 and AlwaysOn.

One of the new features which sounds awesome on paper is "Database Health Level Detection". From my understanding if a database in a AlwaysOn Group enters a state other than ONLINE than the databases would fail over to the secondary replica.

This got me pondering a few things and I can not find an answer yet and do not have our test lab setup yet either.

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

Also with AlwaysOn there is another feature called "Automatic Page Repair".  If "Automatic Page Repair" fails, my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

So, is "Database Level Health Detection" only recommended if your cluster is only being used for one application?  For example, one of our clusters is going to host about 50 different databases used by our Intranet; if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?

If I restore a database, the state of the database will be RESTORING; will that trigger a failover event?

No, because you can't restore a database that is in an AG. Thus, to restore it'd have to be out of the AG and wouldn't be subject to database level health detection.

  ... my understanding is that the database enters a Suspended state, which would make me expect the databases to failover as well.

Suspended isn't a database state - suspect is, not sure which you meant. Also, the key with this is that it may "attempt" to failover but doesn't mean it will be successful.

  So, is "Database Level Health Detection" only recommended if your cluster is only being used for one application?

It works at the Availability Group level, that's up to you on how and what you put into your AGs. You might have a single app in an AG or you might have multiple. Personally, I would only put the databases that need to be together in the same AG and not co-mingle any other application databases.

  For example, one of our clusters is going to host about 50 different databases used by our Intranet; if one of those databases needs to be restored or has corruption, I would not want the other 49 to have a fail over event and a small outage, or would that not be an issue?

If your entire intranet relied on all 50 databases being on the same instance, then yes I'd put them all together... whether or not you want the AG to failover if a single database (might be the most important one or the least important one) has a state change. I can't make that business decision for you. I also can't change the need to have all 50 together.


Foreign key to subtype or super types? I'm using SQL Server

and in this case i have the following tables:

Restock: Foreign key to an R&D Staff member
R&D_Staff: subtype from Staff
Sales_Staff: subtype from Staff
Shipping_Staff:  subtype from Staff
Staff: supertype

Should the foreign key be pointing to Staff (Supertype) or to R&D_Staff (Subtype)

Thanks in advance

Re-phrasing, do you mean:

  don't allow Sales or Shipping staff values in restock?

Then the foreign key is from Restock to R&D_Staff to allow only values from RnD staff in the Restock table


postgresql unable to login I created a user named 'client', witch refuses to connect.
Here's my client:

CREATE ROLE client LOGIN
  ENCRYPTED PASSWORD 'md5d5bc32b75da65fe60067f501a4bb6665'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '1970-01-01 00:00:00';
GRANT viewers TO client;

Here's how I changed his password:

ALTER USER client PASSWORD 'client';

Here's how I fail to connect:

psql -h localhost -d core -U client
Password for user client:
psql: FATAL:  password authentication failed for user "client"
FATAL:  password authentication failed for user "client"

What have I done wrong? (I did tried restarting)

Just in case there are some more dumps:

viewers group:

CREATE ROLE viewers LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '1970-01-01 00:00:00';

core database

CREATE DATABASE core
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE core TO public;
GRANT ALL ON DATABASE core TO postgres;
GRANT CONNECT, TEMPORARY ON DATABASE core TO viewers;

Issue ALTER ROLE client VALID UNTIL 'infinity' and it will remove the VALID UNTIL clause.


Selecting 2 column in an Inner join Below are my two tables :

I am trying to write a query that joins these 2 tables AND returns the Source_Title and Destination_Title for the given Source ID of Result Map table.

I have tried written the query, but I can only solve one part of the puzzle not the whole thing.

SELECT Title as SourceTitle
FROM Result Map AS RM
INNER JOIN Table_Result TR ON RM.SourceId = TR.Id

The above query gives me the title for the source, but not the Destination title.

Benjamin.
If I understood you correctly, you want something like this:

SELECT TR1.Title AS SourceTitle,
       TR2.Title AS DestinationTitle
FROM [Result Map] AS RM
INNER JOIN Table_Result TR1 ON RM.Source_Id=TR1.Id
INNER JOIN Table_Result TR2 ON tr2.Id=RM.destination_id;

The query will return

SourceTitle DestinationTitle
ABC         DEF


MySQL / DRBD / Pacemaker on EC2 I am trying to setup DRBD/MySQL/Corosync/Pacemaker on AWS EC2 instances, though I am not sure if it will work since how to integrate VIP is a problem for me.

Anyone have any thoughts on what to do ?

For amazon AWS you have two options:

Secondary Private IPs
Elastic IPs

Then you'll need to edit your IP2addr resource agent (under /usr/lib/ocf/resource.d/heartbeat/IPaddr2) to manage the IP failover in-front of amazon.

For Elastic IPs I found this solution.
For secondary IPs I guess you'll just need to include the AWS CLI
commands: ec2-assign-private-ip-addresses when promoting and
ec2-unassign-private-addresses when demoting.


The only issue seems to be the FILTER clause. You can  rewrite:

COUNT(*) FILTER ( WHERE tsrange(login_start, login_end, '[]')
                  && tsrange(time, time + '30 minutes'::interval, '[]')
                )

as:

COUNT(CASE WHEN tsrange(login_start, login_end, '[]')
                && tsrange(time, time + '30 minutes'::interval, '[]')
           THEN 1 END
     )

I would also use inclusive-exclusive intervals: [). Otherwise some rows will be counted twice.


Error on re-import: #1231 - Variable 'character_set_client' can't be set to the value of 'NULL' Using phpMyAdmin, I was getting an error when importing a MySQL dump file to which I'd made a simple global find-and-replace:

Error
SQL query:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
MySQL said:

#1231 - Variable 'character_set_client' can't be set to the value of 'NULL'

The strange thing is I had just exported the dump, and this is all I did.

So then I tried exporting a .zip again, and re-importing without making any changes. And I got the same error.

I was able to import the file, finally, by deleting these lines from the dump:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

But I'd like the export/import process to just work.

So my question is: How can I prevent this from happening with future imports, in a way that doesn't require looking up this issue again?

Note: I'm on a shared server setup; I don't currently have SSH access.

Not a user of phpMyAdmin, but mysqldump has an option to disable those lines from the dump:

--skip-set-charset

Unfortunately, this will work if you have access to mysqldump. Not sure if phpMyAdmin has configurable options for export, but hope this helps.


Multi-Subnet AG - DNS flips to secondary IP I have a 3-node FCI:  1 primary / 1 secondary co-located in same data center on same subnet, and 1 secondary in a DR data center on another subnet.

Periodically, the virtual IP (VIP) for the AG name flips to the DR site and clients can't connect the Primary site.  The quick fix is to remove the listener from the node in failover cluster manager, then remove the DNS entry that points to the DR site from AD.

I've checked most SQL and Windows logs and I can't find anything related to this.  In fact, the AG doesn't actually flip to the DR site; it's just the DNS entry that gets updated with the incorrect IP for the DR server.

Has anyone else experienced this?  I have 13 AGs, on a 9 node cluster, and only 2 AGs have experienced this.  One of those I tore down and completely rebuilt with a new IP on the AG listeners, but the issue returned a week or two later.

Thanks for any help or info!

This sounds a lot like you need to add MultiSubnetFailover=true to your connection strings.

See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover

That article explains fully why you need it enabled and even why you should specify it even if only using one subnet.

The main reason is that by default SQL Client will select the first IP address it learns about.  If DNS returns more than one IP address, it'll pick the first one.  Its possible for the the order from DNS to shift.  This setting overrides that behaviour.  Now SQL Client will attempt connections to all IP addresses and resolve which is the correct one to use.

You only need to update the connection string. No other code changes are required.


Can I use a timeseries DBMS to store video? From a research study involving participants with a camera, I have chunks of video recording. I have to split them as part of the preprocessing into short sequences of around 5 min long. Each file will be named after its time-stamp in the sequence. I can store them as binary blobs in a database.

In this study, I also have other sensors. It's clear that I should Use a time series DBMS to store these data (IoT sensing data). This will facilitate queries based on time. Do you see a problem with me storing the file paths and indexing them into a TimeseriesDB (like InfluxDB) based on their file blob's sequential time-stamps to facilitate time-driven queries?

The Q & A Should binary files be stored in the database? partially answers my question - from looking at that answer is sounds like storing video as a blob is a good solution. But my question is more about indexing video by the file's time-stamps.

The video is recorded in large (time) chunks at a time. as part of the cleaning/pre-processing, we split them into 5-minute long segments. Each segment is saved to a file that retains/maintains a date-time-stamp (a 50 min long video is split into 10). Putting it this way, it seems natural to store these file paths per their date-time-stamp. I just want to run my logic by others.

From a research study involving participants with a camera, I have chunks of video recording. I have to split them as part of the preprocessing into short sequences of around 5 min long. Each file will be named after its time-stamp in the sequence. I can store them as binary blobs in a database.

It doesn't make much sense to split apart a video, unless for security. It's a container with an audio and video stream. Normally they're stored in a container like AVI, MP4 or MKV (the open standard). In MKV-parlance, you have "Chapters". This is effectively a timeseries database.

Also, you may want to be careful if you're extracting and cutting them. Keep in mind most video streams are compressed, and de-compressing and re-compressing can introduce artifacts and such.

Here are the fields store in the Chapter's data

See Also

My Answer to Should binary files be stored in the database?


Can we allocate 10 gb RAM to postgres out of 30gb Team,
Can we allocate 10 gb RAM to postgres out of 30gb on Linux machine.
If yes can someone share me the steps

Yes one can allocate the Shared Memory with a Kernel Flag this is documented in Postgresql Documentation

https://www.postgresql.org/docs/current/kernel-resources.html

Also may want to look into Huge Page same link further down

note this is Shared Memory other settings eat up memory independently,  see the postgres.conf  for more details


How to update a column whose value is being calculated from the values exists in other columns of same table in MySql? I've a table User_Engagement having columns :-

I was trying to implement this below formula on my update Query to calculate TotalInteraction of all columns in a single query:-

totalInteraction =   value in totalComments + value in totalLikes * ( SUM of averageLikes / SUM of averageComments)

So far I've tried to implement this query :-

UPDATE User_engagement AS ue
SET
ue.totalInteraction = (ue.totalComments + ue.totalLikes) * (SUM(ue.averageLikes)/SUM(ue.averageComments))

This above query is giving me an error #1111 - Invalid use of group function in MySql.

A help is highly appreciated.

One approach:

SELECT @ratio := SUM(averageLikes)/SUM(averageComments)
    FROM User_engagement;
UPDATE User_engagement
    SET totalInteraction = (totalComments + totalLikes) * @ratio;

But that may be impractical...  What happens when anything changes in the table?  Won't you have to recompute and update all the rows in the table?

Instead, do the computation when you need it:

SELECT ue.totalLikes, ue.averageLikes, ue.averageComments,
       (ue.totalComments + ue.totalLikes) * x.ratio AS totalInteraction
    FROM User_engagement AS ue
    JOIN ( SELECT SUM(averageLikes)/SUM(averageComments) as ratio
            FROM User_engagement ) AS x


Will Blocking cause timeout issue When I check in sys.sysprocesses in SQL 2008R2, a number of blocking sessions are there. Most of them are insert, update, and delete statements, with lock-related wait types.
Will this cause time out issues? Can anyone confirm whether this will cause time out / performance issues?

There are two type of timeouts.
One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" in a SQL server trace. Default in many APIs is 30 seconds.
The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely.
Yes, if a blocking lasts longer than any of above timeouts values, then you will get a timeout.
Do the user consider waiting for a long time (blocking) and perhaps even getting a timeout as bad performance. Yes, absolutely.


Pros and cons of a particular DB design approach for single vs multiple tables I just got a new project (in JAVA) where I am thinking of all the pros and cons of a particular DB design approach. Some pointers:

Our app has many "content" entities like News, Blogs, CMS pages, Wikis, Suggestions etc. I feel that most of these entities are similar (having 75% same fields) but 25% are different. For example, blogs might have a few extra columns which News does not have.
Since every entity seems "similar", I am thinking of keeping them in the same DB table with all the columns covering each entity.
Data load: A customer might have 100K news, 50K blogs, 10K CMS pages, 100K wikis and 50K Suggestions. This is the top-end (maximum) a customer might have given our product. Some customers might have just 2K-5K items of each type in their DB. Lower end data load: 1K items of each type in DB.
There will be more "reads" than "inserts" in this app (customer usage).

My questions:

Will having all this data in a single table hurt performance too much? Note that we will be using Lucene for searching.
Having a single content table with an entity type column which specifies the entity type, we can have a single business layer handling these entities. This will reduce the code base considerably. Any cons of this approach?

Note, that I assume C# (since this language is present in tags) and Entity Framework.

Both approaches assumes, that entity types are inherited from abstract, non-persistent base entity type, which accumulates 75% of fields, so the code base discussion is not relevant.

Pros of Table-Per-Hierarchy (all descendants are stored in single table):

database structure is simpler (just one table)
queries are simpler (with Table-Per-Type you need joins to select descendant's row)
auditing is simpler

Cons of Table-Per-Hierarchy:

the table has low-density data (a number of nullable fields, filled dependent on row type). This isn't a problem, when there are 5-6 such fields, but what if there are 20-25 of them?
table relations and indexes structure are not obvious. Since your entities will have relations with other entities, this will lead you to foreign key madness. Just imagine foreign keys for you table.
database structure isn't obvious. Who can guess, that news are stored beside CMS pages?
usually, 90% of entity types have an Id and Name-like fields (Title, Header, etc). Are you going to store all of them, using single table?
table size will grow very fast. With the lapse of time some operations (e.g., deletion) will be slower.

IMHO, every inheritance scheme, mapped to a relational database, should be used very carefully. Definitely you shouldn't map all you entities to a single table.


Format a value from other values in the table I have identified a location for data integrity problems in a design for a database I'm planning on building. I have lots of two way connections between maps in a game. These connections cause from 2 to 8 duplicate strings per connection. Due to this I have decided to add a names table.

As an example we have two locations Beach and Town, to move between them you need to move into the "Beach to Town" or "Town to Beach" entity.
As I'm defining regions with a tree / graph like structure, there would be five regions in total. The four described earlier. And "Beach - Town Path" which is the parent to both "Beach to Town" and "Town to Beach".

In a table this would look like:

ID  NAME
1   Beach
2   Town
3   Beach to Town
4   Town to Beach
5   Beach - Town Path

I can solve this outside of SQL easily with Python. However I would prefer this logic to be inside the database.

import string

formatter = string.Formatter()

names = {
    1: "Beach",
    2: "Town",
    3: "{names[1]} to {names[2]}",
    4: "{names[2]} to {names[1]}",
    5: "{names[1]} - {names[2]} Path",
}

def get_name(id: int) -> str:
    name = names[id]
    while True:
        segments = list(formatter.parse(name))
        if len(segments) == 1 and segments[0][1] is None:
            break
        name = name.format(names=names)
    return name

print(get_name(1))  # Beach
print(get_name(2))  # Town
print(get_name(3))  # Beach to Town
print(get_name(4))  # Town to Beach
print(get_name(5))  # Beach - Town Path

How this works:

I have the table imported into Python. (I have used a literal for brevity)
We get the name from the table.

name = names[3]
# name = "{names[1]} to {names[2]}"

We check if the name has segments to be formatted.

segments = list(formatter.parse(name))
# segments = [('', 'names[1]', '', None), (' to ', 'names[2]', '', None)]
if len(segments) == 1 and segments[0][1] is None:
# if False:

We format the name with the linked names.

name = name.format(names=names)
# name = Beach to Town

We check if the name has segments to be formatted. Since it has we break out of the loop.

segments = list(formatter.parse(name))
# segments = [('Beach to Town', None, None, None)]
if len(segments) == 1 and segments[0][1] is None:
# if True:
    break

Return the desired name.

return name
# return "Beach to Town"

Back to SQL. This is an example table containing more examples:

ID NAME
1  foo
2  bar
3  {names[1]} - {names[2]}
4  {names[1]} -
5  {names[4]}{names[2]}

Whilst I can just use the exact code above and get the desired output.
Is it possible to use SQL with something like the following to get the desired output?

SELECT name FROM names WHERE id = 1 -- "foo"
SELECT name FROM names WHERE id = 2 -- "bar"
SELECT name FROM names WHERE id = 3 -- "foo - bar"
SELECT name FROM names WHERE id = 4 -- "foo - "
SELECT name FROM names WHERE id = 5 -- "foo - bar"

There are no limit on how the data is stored or retrieved, except that duplicate names are not allowed and the parsing must happen in SQL.

While clarifying your problem you say "how can I make it so if I ever rename Beach I don't have hundreds of potential problems?" This sounds like a normalization problem to me. Looking at the examples above I think you're mixing data and formatting inside the database, then trying to untangle that in processing.

Starting from an analysis of the problem domain it looks like you have two entity types - Location (or names) and Path, where a Path connects exactly two Locations. This I would implement as two tables. As you're concerned about changing natural keys without touching many rows I'd implement surrogate primary keys (using pseudo code since each DBMS's dialect will vary).

Location
  LocationId    int primary key
  LocationName  char(999) not null

Path
  FromLocationId int not null FK references Location.LocationId
  ToLocationId   int not null FK references Location.LocationId

  Primary key (FromLocationId, ToLocationId)

insert Location values (1, 'Beach');
insert Location values (2, 'Town');
insert Path values (1, 2);

The presentation can be taken care of in SQL if you really want to, though I'd suggest letting the persistence classes of the application take care of it:

select 1 as ID, l.LocationName
from Path p
inner join Location l
  on l.LocationId = p.FromLocationId

union all

select 2, l.LocationName
from Path p
inner join Location l
  on l.LocationId = p.ToLocationId

union all

select 3, f.LocationName + ' to ' + t.LocationName
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId

union all

select 4, t.LocationName + ' to ' + f.LocationName
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId

union all

select 5, f.LocationName + ' to ' + t.LocationName + ' Path'
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId;

Because of the UNION ALL each path will produce five rows of output. These are distinguished by the constant-valued ID column in each sub-query so a where ID = .. query will work as you imagine.

Likely you will have more than one path in your database. It may be worth adding the column PathId to Path. Then your application queries become where PathID = xxx and ID = 5.

Now if you rename "Beach" to "Banana" exactly one column of one row changes (just like 3NF says it should) but all results return the new name, guaranteed, with no possibility of inconsistent results.


SQL Text data type deprecated? I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max).

PostGreSQL already allows varchar (without a length) as an alternative for text.

The question is, what does the ANSI SQL standard have to say about deprecating text, and the best alternative? Also, does MySQL/MariaDB also follow this?

Thanks

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT.

The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their similarity is that they can hold very large strings.

SQL Server's TEXT is being deprecated. MySQL's TEXT is not deprecated. PostgreSQL's TEXT is well supported and recommended by many to be used everywhere, in place of VARCHAR(n). From Postgres documentation, Character Types:

  In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So, the questions:

  The question is, what does the ANSI SQL standard have to say about deprecating text, and the best alternative? Also, does MySQL/MariaDB also follow this?

are simple to answer: Nothing and No. There is no deprecation of TEXT as there is no TEXT type. TEXT isn't even a reserved keyword in ANSI SQL. One DBMS (MySQL) does not have to follow another DBMS's (SQL Server) deprecation decisions for a type that isn't in the standard.


Connection Timeout Expired without apparent network issue We have one particular SQL Server which is intermittently timing out when accepting connections.  The issue is consistent throughout the day, but occurs at a very low incidence.  How can I continue to troubleshoot?

  Connection Timeout Expired.  The timeout period elapsed while
  attempting to consume the pre-login handshake acknowledgement.  This
  could be because the pre-login handshake failed or the server was
  unable to respond back in time.  The duration spent while attempting
  to connect to this server was - [Pre-Login] initialization=0;
  handshake=15002;  (Microsoft SQL Server, Error: -2)

Server Configuration:

SQL Server 2016 SP1 CU5 Enterprise (issue also occurred prior to SP1)
Windows Server 2012 R2 on both server and client
VMware ESXi, 6.5.0 on HP ProLiant DL360 Gen9
VM has 8 vCPU, 64 GiB of memory (fully reserved)

Test Script (executed once per second):

$failed = $false;
$loginDuration = (Measure-Command {
    $ncon = New-Object System.Data.SqlClient.SqlConnection `
        @( 'Data Source=1.2.3.4,16143;Database=Test;User=Test;Password=****;Pooling=false;' );
    try
    {
        $ncon.Open();

        $cmd = New-Object System.Data.SqlClient.SqlCommand `
            @( 'SELECT @@VERSION', $ncon );
        $cmd.ExecuteNonQuery();

        $ncon.Dispose();
    }
    catch
    {
        $failed = $true;
    }
}).TotalMilliseconds;
Write-Metric -metric 'itp.dbserver.logintime' -unit 'milliseconds' `
    -value (&{if ($failed) { 120000 } else { $loginDuration }});

Observations:

Issue started occurring after OS Updates, SQL Server Updates, San move, and move from Hyper-V to VMWare
Most connections succeed (4 failures out of 1,440 attempts)
Failures are always listed with a low number in "[Pre-Login] initialization=0;" and a high number in "handshake=15002".  We do not get errors like "Not found" or "No such host is known", only "Connection Timeout"
No encryption is enabled for the listener
Pings show no loss over extended period (0 lost out of 96,045 sent)
All firewalls are disabled
Connections attempted using IPv6 and IPv4 addresses fail at the same rate
CPU is low-ish (<40%)
Active sessions is persistently around 400
Balloon driver is disabled
Connections once made are stable, no unexpected errors when executing queries, no odd disconnections.
Multiple clients are having problems connecting - both ODBC and ADO from multiple computers

Update: I finally got a client-side Wireshark trace of a failed connection.  No packet loss is apparent, client receives TCP ACK's in real-time (<10ms).  Client was using DNS name at time of failure, but failure does occur using the IPv4 address in the connection string.

Am I correct in thinking that the fact that I get immediate TCP ACK's to the pre-login request packets sent would localize the issue to the OS or SQL Server?

This was eventually identified as a side-effect of VMWare LRO.  Disabling host-based LRO resolved the issue.  See

Enable or Disable LRO on a VMXNET3 Adapter on a Windows Virtual Machine
Large Receive Offload
Poor network performance or high network latency on Windows virtual machines (2008925)
vmxnet3 adapter on windows server 2012 with MSSQL server bottleneck problem


Mariadb - Error declaring variable in trigger I'm trying to declare a variable to use later in my trigger, but I'm getting this error.

ERROR 1064 (42000)
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END' at line 7

DELIMITER $$
$$
CREATE TRIGGER clients_before_update
BEFORE UPDATE ON clients FOR EACH ROW
BEGIN

    declare var_test INT;

END;

$$

The special delimiter you have defined should be directly after the END. That is, replace your END; with END$$.


Questions about Database Design for a Web Application Consider a web application with 100,000 users. Each user can create blog posts.  Assume each user has 100 entries.

Would it be appropriate to store all the users in one table, with another table to store the entries made by each user?

Is this a bad design? What would be an efficient way to store all the information to facilitate quick access of the information?

100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately.

Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new  question for feedback.


MySQL determine day of week when working with multiple date fields So I have this existing case statement in my query:
SELECT
CASE
WHEN dl.list_id = 1000000
AND dl.date_inserted != ''
THEN dl.date_inserted
ELSE dl.entry_date
END AS date

Let me give some context here. So as soon as we have a record move to list_id 1000000 I need to reference date_inserted to determine when this record entered the table but for all else I need to reference entry_date.
Next I need to be able to identify which day of the week the above date amounts to for ex. Saturday.
So I created a 2nd table which lists each date for 2020 and the corresponding day so my plan is
CASE
WHEN mc1.day IS NOT NULL
THEN mc1.day
ELSE mc2.day
END AS day_of_the_week
LEFT JOIN "db"."meta_calendar" mc1 ON dl.date_inserted = mc1.date
LEFT JOIN "db"."meta_calendar" mc2 ON dl.entry_date = mc1.date AND dl.date_inserted = ''

The result seems to be producing the expected output but I have a feeling there could be a better way to achieve this. Basically I need this validated and if this is not the ideal way to achieve this then please advise

What you're doing is fine as far as a working solution goes, and helps avoid using functions to calculate the day of the week in memory every time the query runs. But MySQL has built in functions to calculate it as well which is just as fine of a solution too.
For example, the following returns the week day index using MySQL's WEEKDAY() function:
CASE
WHEN dl.date_inserted IS NOT NULL
THEN WEEKDAY(dl.date_inserted)
ELSE WEEKDAY(dl.entry_date)
END AS day_of_the_week

If you want the name of the week day, then you can use the DAYNAME() function instead.


Must-read Books for DBA This question is almost mandatory in any SE site for basic reference.

Please post only books with permanently value for DBAs in general.

The post should be a must read book, the classical ones, the foundation books, not books for specific products or dated.

The latest book for MS-SQL Server 2008 could be the state of the art but it is dated and specific.

I have a genuine interest. I'm basically a programmer and database knowledge is a flaw on my formation. I know lot of things about the databases just doing day-by-day tasks.

I started with an example of good must read book.

An Introduction to Database Systems by C.J. Date.

This is one of the most influential book to development of all concepts and technological available today. The book follows an academic style and has lots of real cases.

The primary target is not for DBAs but Database systems developers, but a real good DBA knows how your database works and how to handle with its limitations and hidden capabilities.


How can I display a mysql bit(64) column as a string of ones and zeros? We have a MySQL 5.5 database  with a table that holds a number of 64-bit values which are used for calculating the hamming distance.  The columns for these are defined as BIT(64) and are populated using the bit-field literal syntax like this (note the 0b prefix):

create table hash_values ( hash bit(64) );
insert into hash_values;
  (0b0000000011111111000000001111111100000000111111110000000011111111)

For debugging purposes we need to display the bit values as strings of ones and zeros, but have only been able to do this with what feels like an ugly hack:

select conv(cast(hash as unsigned), 10, 2) hash from hash_values;
+------------------------------------------------------------------+
| hash                                                             |
+------------------------------------------------------------------+
| 0000000011111111000000001111111100000000111111110000000011111111 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

This first uses CAST() to get an integer representation; then uses the CONV() method to covert it from base-10 back to base-2.  It seems to work, but I feel like we've overlooked a simple way to cast the bit value to a base-2 string.

You can also use BIN() function. You still have to LPAD() though, the higher order 0s are not displayed:

LPAD(BIN(hash), 64, '0')


Postgresql failback without resync the data directory Im using Master slave streaming replication in PostgreSQL. Repmgr is configured to handle automatic failover.

Once the failover happened I need to make the old master as a slave without doing any basebackup, also the new primary requires some changes to act as a master like need to enable wal logs and other things.

How can I avoid these changes in Secondary during replicate to Old master and sync Old master without basebackup?

Fundamentally, you can't without some kind of reset of the old master. Unless the old master is shut down before the new master is promoted and you ensure all WAL is synced from old to new master, the old will usually be slightly "ahead" of the newly promoted replica at the point where the replica is promoted onto a new timeline.

It will refuse to start replaying from the new master because doing so would corrupt the old master. The old master might have assigned transaction ID 1234 already. So what should it do if the new master says "I'm starting transaction 1234". It already has 1234! There are similar issues all over, at the low level crash safety parts of the system.

That's why you usually re-sync the old master from the new master before starting it up. This can be made a lot faster with rsync or one of the parallel variants.

However, you can instead use pg_rewind to "wind back" the old master to the point where its history diverged from the new master, so it can start replaying from the new master. I don't use it myself, so I can't do much more than point you at it. But one of my colleagues wrote a couple of blog posts that could help you:

Back to the Future Pt. 1: Introduction to pg_rewind

Back to the Future Pt. 2: How to use pg_rewind with PostgreSQL 9.5

repmgr can use pg_rewind with repmgr standby switchover. As far as I know it does not do so with auto-failover, but you might want to ask the repmgr team for clarification on that.

Personally I think auto-failover is a terrible idea unless you also trigger it randomly during your normal operations, so you always know it works. ChaosMonkey-style. Otherwise it's just a recipe for doubling your disasters. Prefer manually-triggered automated failover, with something like repmgr standby switchover, and alerting to tell you when something's wrong.


mysql fulltext query is very slow I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single latter/alphabet (e.g. a to z, single character), it is responding very slow in first time. It's taking about 5-6 seconds to respond. after that, it's come down to 800 milliseconds. EXPLAIN command seems ok as It shows "fulltext" in type column, but I couldn't find why it is reacting very slow.

my query is looks like as follows.

SELECT  SQL_NO_CACHE usr.id, usr.uname, ifnull(usr.fullname,'') fullname,
        ifnull(ct.City, '') city,
        MATCH(usr.fname,usr.lname) AGAINST('a*' IN BOOLEAN MODE) ordfld
FROM usertable usr
LEFT JOIN citymas ct ON ct.CityID = upm.CityID
WHERE usr.UserStatus IN(10,11)
AND usr.id <> 1
AND MATCH(usr.fname,usr.lname) AGAINST('a*' IN BOOLEAN MODE) > 0
ORDER BY ( CASE WHEN usr.fullname = 'a' THEN 1
                WHEN usr.fname rlike 'a%' THEN 2
                WHEN usr.lname LIKE 'a%' THEN 3
                WHEN usr.fname like '%a' THEN 6
                WHEN usr.lname LIKE '%a' THEN 7
                WHEN usr.fullname LIKE '%a%' THEN 8
                ELSE 10 END ),
 ordfld DESC,
( CASE WHEN ifnull(usr.cityid,0) = 234 THEN '0' ELSE '1' END ), usr.fullname
LIMIT 20

and explain show me following

1, 'SIMPLE', 'usr', 'fulltext', 'PRIMARY,IX_usertable_fname_lname', 'IX_usertable_fname_lname', 0, NULL        , 1, 'Using where; Using filesort'
1, 'SIMPLE', 'ct' , 'eq_ref'  , 'PRIMARY'                         , 'PRIMARY'        ,          3, 'usr.cityid', 1, NULL

above query is taking too much time, it is responding between 800-900ms.

Any guess?

Usually FULLTEXT searches need more than a single letter, there is actually a setting that prevents them from being used if less than X number of characters are used.

  ft_min_word_length: The minimum length of the word to be included in a
  FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing
  this variable

My guess is that you are not using your index when you search for a single character like that.


PostgreSQL read-only user for pg_dumpall I'm running a PostgreSQL instance which is used by several related applications, each of which has its own database. For backups I want to create snapshots of the complete data in the instance and I plan on using pg_dumpall to do that. The backups are done from another host and should use a separate PostgreSQL user for security reasons.

I am therefore trying to create a PostgreSQL user backup who

can read all data in the instance, both in existing databases/tables and in databases/tables that may be added in the future by other users
cannot modify anything

So far I have managed the following:

-- Revoke default privileges
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Allow user to connect and use database
GRANT CONNECT ON DATABASE db TO backup;
GRANT USAGE ON SCHEMA public TO backup;

-- Grant read privileges for existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;

-- Grant read privileges when new tables are created
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;

This works, but only for a single, fixed database. Is there a way to do this for all existing databases and databases that might be created in the future?

I'm currently on PostgreSQL 9.4 and I'm not using any schemas aside from public.

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).


How to update one column in table from select statement? I have a table that looks like this:

utc date                       est date
2018-03-01 07:06:38.000
2018-03-01 07:07:38.000

est date is blank. I have a select statement that calculates EST time.

How do i update the est date column from my select statement?

SELECT toCONVERT(datetime,
               SWITCHOFFSET(CONVERT(datetimeoffset,
                                    table."utc date"),
                            DATENAME(TzOffset, SYSDATETIMEOFFSET())))
       as "etc date"
FROM table where  "utc date" > '2017-10-01'

UPDATE syntax:

UPDATE [table]
SET [est date] = toCONVERT(datetime,
                           SWITCHOFFSET(CONVERT(datetimeoffset, table.[utc date]),
                           DATENAME(TzOffset, SYSDATETIMEOFFSET())))
WHERE [utc date] > '2017-10-01'


How Maxscale deals with "Select for update transactions" We are facing intermitent deadlock issues that doesnt got reflected in the maxscale log nor the mariadb log. Only in the applications triying to do sql transactions in the database.

We use mostly readwritesplit with 2 nodes (yes, i know, bad design) and we dont know exactly how maxscale deals with the select for update queries.

to the slave, because is a read query (select)
to the master, because is a write query (update)

Maxscale version 2.1.16, MariaDB 2.1.29

Thanks!

It would be routed to the master, because SELECT ... FOR UPDATE is only a part of a transaction, and according to the MaxScale 2.1 readwritesplit documentation, "all statements within an open transaction" are routed to the master.

You should be able to verify this with a query like this, which should consistently give you the hostname of the master:

START TRANSACTION;
SELECT * FROM t1 WHERE id = 1 FOR UPDATE;
SELECT @@hostname;
COMMIT;


SQL Server 2008 R2 Missing Index I am performing index maintenance on a database.  I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created.  I am not creating indexes just because the index is listed as missing.  My question, Why would the primary key column be listed as a missing index? The table is arranged in primary key order, correct?  Why would SQL Server beleive a noclustered index should be created on the primary key?

Thanks in advance.

The primary key in SQL Server is by default also the clustering key - and the data is physically ordered by the clustering key in a table.

So if this primary key is listed, maybe it's because it is a non-clustered primary key - something that you have to do explicitly - but it's entirely possible.

The primary key is a logical construct - it's job is to make sure each row in a table can be uniquely and reliably identified. It has no impact whatsoever on the physical structuring of the table.

The clustering key (and thus the clustered index) is what determines the physical ordering of the data in a SQL Server table. The table's primary key is the clustering key by default - but it doesn't have to be - you can have two totally separate sets of columns for the primary key and the clustering key.

But all these things aside: the DMV's that identify missing indexes aren't perfect - do take their recommendations with a grain of salt! Don't just blindly implement everything that DMV recommends - sometimes it recommends stuff that's already in place, or some of its recommendations are even contra-productive! Don't blindly trust it - it's a helping tool - but it's not perfect!