Permissions to delete large objects Since PostgreSQL 9.0, each large object has its own access rights that are granted with:

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
  ON LARGE OBJECT loid [, ...]
  TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

The SELECT permission allows for opening the object in read-only mode, and for UPDATE, the doc says:

 For large objects, this privilege allows writing or truncating the object.

But if a large object belongs to user1, I can't find how to grant the permission to user2 to delete (lo_unlink) this object.
If user1 says GRANT UPDATE on LARGE OBJECT xyz TO user2; and user2 runs SELECT lo_unlink(xyz);, it's denied with:

 ERROR:  must be owner of large object xyz

Is it really the case that only the owner a large object is ever able to delete it, or I'm missing something?

My use case is a db that stores mailboxes that are shared between different db users. All the tables with mail contents and large objects with mail attachments are owned by a "master" user, and other users may or may not delete messages depending on their database role.

Deleting a message means deleting everything related to it, including attachments stored in large objects. So it would make sense that a non-owner should be able to delete large objects, just like he can delete from other tables if he has been granted the DELETE privilege on these tables.

That's consistent with other objects, which cannot be DROPped except by the owner. It seems kind of painful for large objects, though; maybe bug -hackers?

I'd work around this by using oid references instead of lo, and then using vaccumlo to remove them once the references to them are removed.


How to update mysql my.cnf without getting error? I updated the MySQL my.conf file and and restarted my server without a problem.

As soon as I tried to access a table I received the error

mysql #1033 - Incorrect information in file:

I reverted the my.cnf file back to the original and the server is working properly.

I am looking to update some of the innodb settings:

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

I think I am seeing that I have to update the my.cnf file before creating any databases on the server, but that doesn't seem right.

Is there a way to update the my.cnf file after a database has been created on the server?

CentOS 5.6

MySQL 5.0.77

Original:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

New:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# This is my configuration!
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M

innodb_log_file_size=1G
innodb_log_buffer_size=8M

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

You mentioned these four options

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

The one that worries me is innodb_log_file_size

Upon initial startup, innodb_log_file_size is 5MB. You need to make new InnoDB log files. There are two InnoDB log files that got created 5MB each:

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

All you have to do is the following:

service mysql stop
rm -f ib_logfile*
service mysql start

The two log files will be regenerated as 1GB files. I wrote about this last year.

Give it a Try !!!

BTW DO NOT TOUCH /var/lib/mysql/ibata1 !!!


Constant values in columns I have a SQL Server 2000 database used by our ERP system.

There are many columns that always have the same value. A table row is really huge, potentially exceeding the maximum row size in SQL Server 2000 (8kB).

I'm thinking about replacing these columns with constant calculated columns. This should decrease the size of the table. I expect better performance, because there is less data to read from disk, when the server needs to load the table in memory.

Is this a good idea ? Will I obtain the expected performance gain ?

If you use a normal (non-persisted) computed column - then yes, you might save a bit on disk space - BUT always constantly re-computing those values every time you access the row will likely cost you more in terms of processing time than you save by loading less data.

There's really no easy way to predict this - how much performance will you save by loading less data vs. how much performance is needed for constantly re-computing those column values. You'll need to run some performance tests to find out.

If you use persisted computed columns, then those values are stored just the same as with a normal column - so you won't have the negative performance impact from constantly re-computing the values, but you also won't be saving anything on disk space.

PS: I'm not entirely sure that SQL Server 2000 even had the PERSISTED keyword for computed columns (that SQL Server version is just too old for me to really remember...). I believe however that PERSISTED was a new feature in SQL Server 2005, so it wouldn't apply to you.


Formatting Tabular mysql results in mail on unix I am trying to email mysql query results through mail command in unix as below.

mysql -uroot -p*** -e "select query" --table|mail -s "test" abc@example.com

but format is little bit creepy kind of below.

+--------------+-------------+
| PARTNER_NAME | ORDER_COUNT |
+--------------+-------------+
| Legacy       |           0 |
| NextJump     |           0 |
| Provide      |           0 |
+--------------+-------------+

Can you help me with formatting the output if you have any idea

The formatting is down to the client in which you are reading the result. If the client is set to fixed width font then the display formatting will be as you expect.


Register dll to use in SSIS Script Component I've been asked to register an existing dll file so that it can be referenced in an SSIS script component.

I have a dim and distant memory of doing this using gacutil.exe so that was my initial go to.

However, gacutil.exe is not present on the integration server, presumably because the full version Visual Studio is not installed there, only the Data Tools shell. I have also tried to copy the file into C:/windows/assembly as I read this would work, but nothing happens when I drop the file in it just cancels out.

How can I do this without gacutil.exe? Is there a way or will have to install full VS SDK to do it.

You don't need Visual Studio, you need the Windows SDK. You can use MSDN to find the SDK matching your platform. The assembly has to be strongly named and can only be added to the GAC using the gacutil /i your.dll command or by using a Windows installer package. Prior to .NET 4.0 you could drag and drop the dll into the C:\Windows\Assembly folder but this is now obsolete.


Can I combine SELECT TOP() with the IN operator? I was wondering if there is a way for a SELECT query to be made on each matched value found within IN.

Consider that the table below contains 100 records for each of the values included referenced by the INoperator, I'd like only 50 to be returned for each match.

SELECT TOP (50) COLUMN
FROM TABLE
WHERE COLUMN IN (1,2,3)

Is there an efficient way I can do this?

The only way I could think of is to execute the query for each value within IN which would be way too slow considering the actual query retrieves many column from various tables.

One method is with CROSS APPLY:

SELECT top_50.ColumnName
FROM (VALUES(1),(2),(3)) AS id_list(ID)
CROSS APPLY(SELECT TOP(50) t.ColumnName
    FROM TableName AS t
    WHERE t.ID = id_list.ID
    ORDER BY t.ColumnName) AS top_50;

To avoid returning random rows with TOP, ORDER BY is needed and the ordered by columns should be unique. An index with a composite  key on ID and ColumnName will be useful in this example query for efficiency.


SQL Server how to test filegroup IO We have a customer who is switching from SAN storage to directly attached storage. Our SQL Server database has several filegroups all of which has been deployed on the same LUN.

In order to determine the best setup for the filegroups on the directly attached storage I want to test the loading on the existing file groups. For directly attached storage I would normally have tested loading by looking at performance counters like disk queues etc. but this assumes that the file groups have been setup on separate arrays. For SANs we would normally have got the IO throughput for each LUN from the SAN vendor.

However, because all the filegroups are on the same LUN, how can I test how heavily each filegroup is loaded? Is there any way to do this with SQL profiler?

Thanks
Chris

Look at sys.dm_io_virtual_file_stats. It will give you per-file (you can aggregate them manually per-filegroup) for things like number of reads, number of writes, IO read stalls and io write stalls. These server lifetime aggregates, so you cannot analyze spikes and trends, but is none the less a good starting point to differentiate hot files vs. cold files.


SQL Server Management Studio does not load I am working on SQL Server 2012 for the past 15 days it was working perfectly but now I am having an issue:

  Cannot find one or more components. Please reinstall the application

Please try:

  • Start regedit.exe

  • Go to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management
  Studio

  • Delete 11.0_Config

  • Restart ssms.exe

There is a similar problem in this feedback to Microsoft, please refer the below link..

http://connect.microsoft.com/VisualStudio/feedback/details/690773/after-installing-visual-studio-11-my-sql-server-denali-cannot-be-opened


To see everything with aggregation, you could do this

SELECT src,CONCAT(dst,':',dstport) dsp_port,COUNT(1) hits
FROM logs
GROUP BY src,dsp_port;

To see subtotals

SELECT
    IF
    (
        ISNULL(src)=1,
        'Total',
        IF
        (
            ISNULL(dst_port)=1,
            CONCAT(src,' Total'),
            CONCAT(src,' ',dst_port)
        )
    ) Statistic,hits
FROM
(
    SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    FROM logs
    GROUP BY src,dst_port
    WITH ROLLUP
) A;

Using your sample data

mysql> DROP DATABASE IF EXISTS rob_d;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE rob_d;
Query OK, 1 row affected (0.00 sec)

mysql> USE rob_d
Database changed
mysql> CREATE TABLE logs
    -> (
    ->     src CHAR(15) NOT NULL,
    ->     dst CHAR(15) NOT NULL,
    ->     dstport  INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logs (src,dst,dstport) VALUES
    -> ('10.110.0.10',   '10.2.9.124'    ,53),
    -> ('10.110.0.10',   '192.168.9.124' ,53),
    -> ('10.192.31.23',  '10.192.1.120'  ,8082),
    -> ('10.192.31.19',  '10.192.1.186'  ,8081),
    -> ('192.168.31.131','192.168.31.130',80);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----------------+----------------+---------+
| src            | dst            | dstport |
+----------------+----------------+---------+
| 10.110.0.10    | 10.2.9.124     |      53 |
| 10.110.0.10    | 192.168.9.124  |      53 |
| 10.192.31.23   | 10.192.1.120   |    8082 |
| 10.192.31.19   | 10.192.1.186   |    8081 |
| 192.168.31.131 | 192.168.31.130 |      80 |
+----------------+----------------+---------+
5 rows in set (0.00 sec)

Here is the query's output

mysql> SELECT
    ->     IF
    ->     (
    ->         ISNULL(src)=1,
    ->         'Total',
    ->         IF
    ->         (
    ->             ISNULL(dst_port)=1,
    ->             CONCAT(src,' Total'),
    ->             CONCAT(src,' ',dst_port)
    ->         )
    ->     ) Statistic,hits
    -> FROM
    -> (
    ->     SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    ->     FROM logs
    ->     GROUP BY src,dst_port
    ->     WITH ROLLUP
    -> ) A;
+----------------------------------+------+
| Statistic                        | hits |
+----------------------------------+------+
| 10.110.0.10 10.2.9.124:53        |    1 |
| 10.110.0.10 192.168.9.124:53     |    1 |
| 10.110.0.10 Total                |    2 |
| 10.192.31.19 10.192.1.186:8081   |    1 |
| 10.192.31.19 Total               |    1 |
| 10.192.31.23 10.192.1.120:8082   |    1 |
| 10.192.31.23 Total               |    1 |
| 192.168.31.131 192.168.31.130:80 |    1 |
| 192.168.31.131 Total             |    1 |
| Total                            |    5 |
+----------------------------------+------+
10 rows in set (0.00 sec)

mysql>


Database Design for Maintaining Supplier Invoice I am not sure whether it is right to ask such question here. However, It comes to my mind this would be the best place to start. My needs are quite simple. As a distributor, I want to maintain all invoices that come from my suppliers. So I stored the invoices in like below:

  +----+------+------------+-----------+---------+---------+---------+--------+
  | id | spid |  transdate | transtype | transno |  debit  |  credit | userid |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  1 |   2  | 2017-04-04 |  Invoice  |   MI09  | 2500.00 |    0.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  2 |   2  | 2017-04-05 |    Pay    |   B098  |    0.00 | 1000.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  3 |   2  | 2017-04-07 |  Invoice  |   MI89  | 5000.00 |    0.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  4 |   2  | 2017-04-12 |    Pay    |   BI09  |    0.00 | 4000.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+

In this table I stored the invoices from all my suppliers. But to make things simple, I only showed one supplier with id=2. The problem here is: if I keep storing invoices and my payment in this way, it will soon be too heavy for my table when I have thousands of invoices. I would like to make a printout every month also. So I want to manage the closing and opening of this transaction every month, but I have no idea and google does not help me much.
My question is: How do I manage the closing and opening of this transaction? Do I need another field/table for that? What would that be? Please help.

http://sqlfiddle.com/#!9/eed9b0

Don't worry about having too many transactions in your table.

Relational Database Management Systems like MySQL are designed to handle amounts of data that would overwhelm other formats, like a spreadsheet for example.

You can easily store millions of records in your table and MySQL will find you the ones you want quickly, especially if you define indexes on the fields that will be part of your WHERE clauses in your queries.  For example, if you want to do monthly reporting, make sure you have an index on transdate.


Optimising some SQL to get all rows corresponding to the minimum value of a column I've worked out how to get some rows from a table where a column has the lowest possible value. Note this can return multiple rows, which is what I want. It works just fine.

SELECT col1, col2
FROM TableA
WHERE col4=0
    AND col5=1
    AND col3 IN
       (SELECT MIN(col3)
        FROM TableA
        WHERE col4=0 AND col5=1)

But is the nested select overkill, i.e. can this be optimised? Maybe there's a simple SQL command I'm missing...

I tried

SELECT col1, col2, MIN(col3)
FROM TableA
WHERE col4=0
    AND col5=1

but this will only ever return one row.

The documentation provides a couple ways to get what you want, which will perform well.

SELECT TableA.col1, TableA.col2
FROM TableA
INNER JOIN (
    SELECT
    MIN(col3) AS col3
    FROM TableA
    WHERE col4 = 0
    AND col5 = 1
) lowest_col3_TableA
ON lowest_col3_TableA.col3 = TableA.col3
WHERE TableA.col4=0
AND TableA.col5=1

Or

SELECT TableA.col1, TableA.col2
FROM TableA
LEFT OUTER JOIN TableA lower_col3_TableA
ON lower_col3_TableA.col4 = 0
AND lower_col3_TableA.col5 = 1
AND lower_col3_TableA.col3 < TableA.col3
WHERE TableA.col4=0
AND TableA.col5=1
AND lower_col3_TableA.col3 IS NULL

For this second query, the last line of AND lower_col3_TableA.col3 IS NULL doesn't necessarily need to be col3, but DOES need to be a column that is defined with NOT NULL. Usually the leftmost part of the primary key is best.


Mysql How to update n rows using count function? In the following table, I want to set delete = true if the total records for same orgid exceed 500
and I want to do it according to createdate such that if records exceed 500 the old records get deleted and make total records 500 for that orgid.
here is my table
Table A
+----+-------+------------------+--------+------------+
| id | orgid | transactionvalue | delete | createdate |
+----+-------+------------------+--------+------------+
|  1 |     1 |              123 | false  | 05-16-2020 |
|  2 |     1 |              412 | false  | 07-16-2020 |
|  3 |     2 |              762 | false  | 07-16-2020 |
+----+-------+------------------+--------+------------+

Here is the query I am trying
update A set delete = true where orgid = 1 and (select count(*) as records from (select * from A order by createdate) as pseudotable)) >500

This query will update the oldest records that exceed the count of 500, so you keep 500 newest records for orgid = 1
update A
set `delete` = true
where orgid = 1 AND id IN (
SELECT id FROM  A where orgid = 1
order by createdate DESC
LIMIT 500,18446744073709551615);


Find over-payment amount using a three-table join How to find Overpayment value of only Client No 1 from below 3 tables using inner join? Each client wise I need to retrieve query.

For example 1:

Once I retrieved client 2 only, answer should be 0 because total he invoiced 300 and total he paid for invoice(s) 100 only so overpayment is 0 .

For example 2:

Once I retrieved client 1 only, answer should be 500 because total he invoiced 1500 and total he paid for invoice(s) 2000 only so overpayment is 500.

There is no straight-forward way, just linking these tables, to give the result.

The code below gives you the answers, with the bonus that, letting @ColId null, every Client will be processed.

You may think: it is computationally expensive!

Yes, it may seems so. BUT, check the complementary comments after the code snippet, plz.

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo OR @CliNo IS NULL
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay

At first sight, it seems we are asking the SQL Server to SUM every invoice, SUM every payments, and only then computes any overpayment.

But, SQL Server 2012 is a hell of a smart beast.

Check the actual execution plan:

The SQL Server query engine is filtering the payment and invoice records BEFORE sumarizing them.

Hence, no performance penalty, although this is not exactly what you asked for - a simple query with tree direct INNER JOINS.

The complete code for this POC follows:

CREATE TABLE [Client TBL]([Client No] INT NOT NULL PRIMARY KEY)
CREATE TABLE [Invoice TBL]([Invoice No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
CREATE TABLE [Payment TBL]([Payment No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
go

INSERT INTO dbo.[Client TBL] VALUES(1), (2)
INSERT INTO dbo.[Invoice TBL] VALUES(1, 200, 1), (2, 100, 2), (3, 500, 1), (5, 300, 1), (6, 200, 2), (7, 500, 1)
INSERT INTO dbo.[Payment TBL] VALUES(1, 1000, 1), (2, 100, 2), (4, 1000, 1)
GO

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay
go


Missing options in Feature Selection when installing SQL Server 2016 on existing server I have a Windows 2008R2 64-bits server that already has 2 instances of SQL Server 2008R2 installed, and working fine. I want to add a new instance, but for SQL Server 2016, while keeping the existing instances in 2008R2. When I get to the Feature Selection, there is no option to install the Database Engine, and all the rest. All I get is this screen:

I can't figure out why the "normal" feature selection doesn't appear, so that I can add a new instance. Any tips? Thanks

I don't think Sql Server 2016 will run on a Windows 2008 R2 server.

See the requirements for OS here
https://msdn.microsoft.com/en-us/library/ms143506.aspx#TOP_Principal


restore table and populate new data from transaction log I use mysqldump nightly to backup a database. I'm trying to find a way to do a partial restore on the data from one table. I use the --master-data flag so I know where in the transactions log my data was at the generation. Is there a command I can run for my one table to populate from X in the the logs to the current log position?

There already is a slave process running on this machine. I'm trying to create this as a second table with a similar name do run some tests with.

You're restored a table to a different name and you want to run replication against that table?

Given replication filters that change mapping between master/slave table names are global, you'll need a separate slave instance to test against this table. Once you do this, you don't even need replication filters again to test the new table structure.


Empty CDC scans on highly active SQL Server We have a SQL Server which has highly active databases which will have number of transactions in an hour. CDC is enabled on this database to carry forward the committed changes to another server. But recently we see that the changes were not reflecting on the target server. There are no errors in the dm_cdc_errors table and also the log_scan_sessions is also returning all empty scans. I mean the empty_scan_count is around 3500. That means that the  capture job is running fine and it is scanning the log for any changes.

I even did an update on a table and queried the corresponding CT table but no records were popped up there. Is there any other setting/configuration that I need to check ?

Can you check if any table is enabled for cdc. You can query sys.tables dmv.

I suspect db is enabled for cdc but no tables are configured for cdc that might be the reason cdc couldn't fetch transactions.


SQL Server MCSA Database Administration Certification retires on June 30, 2020 - is there going to be a replacement? Looking at exams 70-764 and 70-765

  This exam retires on June 30, 2020. A replacement exam may be available. To learn more, visit our blog: https://www.microsoft.com/en-us/learning/community.aspx and review our Exam Retirement posts.

Updated competency exams and certifications for 2020 (pdf)
Microsoft Certified: Azure Data Engineer Associate

Is there going to be a SQL Server specific replacement for exams 70-764 and 70-765?

Am I getting it wrong, or this thing "Microsoft Certified: Azure Data Engineer Associate" going to replace "MCSA SQL Server 2016"?

If true, that is a shame, because it looks like passing its exams (DP-200 and DP-201) would require learning a lot of stuff that I actually do not need...

If you are focused on DB administration I would rather recommend certification "Microsoft Certified: Azure Database Administrator Associate"

https://docs.microsoft.com/en-us/learn/certifications/azure-database-administrator-associate

This one requires DP-300 exam and it is focused on Azure and on-perm administration.


Returning auto-generated primary key after SQL insertion I am trying to submit data to a database and then have it return the value of the user_id (primary key) created by the insertion, but I can't get it to return the value. I use the same query in phpAdmin and it works just fine. I know this should be simple, but I just can't get it. Here is my code:

(I do have values to connect to the database, just didn't include them for privacy.)

$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_SITE)
OR die ('Could not connect to MYSQL: ' . mysqli_connect_error() );
$nameQuery="INSERT into nameReunion
    (username, password,email,firstName,middleName,lastName, maidenName,     lastUpdate)
VALUES
('$username', '$password', '$email', '$fname', '$miname', '$lname', '$maname', CURDATE());";

$r = @mysqli_query ($dbc, $nameQuery);

if(!$r) {
echo "The database was not updated.";
} else {
echo 'The connection was successful<br/>';
};

$getUserID="SELECT user_id FROM nameReunion WHERE username='$username';";
$r2=@mysqli_query ($dbc, $getUserID);
echo $r2;
echo $getUserID;

Please help. I have spent wayyy too much time on something I fear should be very simple.

Nevermind. I used mysqli_insert_id and it worked.  The documentation for this solution is available here.


Need help in creating MySQL trigger I am writing a trigger in a MySQL to conditionally create a new record in another table based on the value of field in newly added record in a table. Here is the script for the trigger (which should be self explanatory):

CREATE TRIGGER TRG_CREATE_INT_SRV_ROW
AFTER INSERT ON survey_details
FOR EACH ROW
BEGIN
 IF(New.LossTo == 'Vehicle') THEN
    INSERT INTO survey_vehicle (RefNo) VALUES (New.RefNo);
 ELSE
    INSERT INTO survey_other (RefNo) VALUES (New.RefNo);
 END IF;
END;

While running the query, the error message displayed is:

  1064 - 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 '= 'Vehicle') THEN
      INSERT INTO interim_survey_vehicle (RefNo) VALUES (New.RefNo' at line 5

I am writing this kind of conditional trigger for the first time.

What is wrong with code please?

This is very straightforward.

There is no == operator.

Please change

IF(New.LossTo == 'Vehicle') THEN

to

IF(New.LossTo = 'Vehicle') THEN

UPDATE 2018-02-26 11:04 EST

You need to change the delimiter before and after the trigger:

DELIMITER $$
DROP TRIGGER IF EXISTS TRG_CREATE_INT_SRV_ROW $$
CREATE TRIGGER TRG_CREATE_INT_SRV_ROW
 AFTER INSERT ON survey_details
 FOR EACH ROW
BEGIN
 IF(New.LossTo = 'Vehicle') THEN
    INSERT INTO survey_vehicle (RefNo) VALUES (New.RefNo);
 ELSE
    INSERT INTO survey_other (RefNo) VALUES (New.RefNo);
 END IF;
END $$
DELIMITER ;

Please see my old post Unique combination key MySQL for an example


MySQL - Like with index I found many question, But no solutions.

An index can not be used to speed up queries where the search criteria starts with a wildcard:

LIKE '%text%'

An index can (and might be, depending on selectivity) used for search terms of the form:

LIKE 'text%'

So this means I simply cant use a full wildcard in a big table?

Exactly. Which is why some databases use alternative indexing mechanisms for text. SQL Server has a full text indexing capability that works by breaking the string into WORDS, removing bad ones (like "in" in english - happens too often) and then uses a different syntax for finding words in the text index.

Standard SQL Indices are simply not suited for the task.

MySQL, from a 30 second run in google, also seems to have a full text search mechanism.


Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE? Per this post, in order to optimize the storage of a MyISAM table, you need to:

OPTIMIZE TABLE ;
ALTER TABLE ENGINE=MyISAM ;
ANALYZE TABLE ;

It's obvious what OPTIMIZE does for disk space but what does ALTER and ANALYZE do when used after OPTIMIZE? I am asking specifically about disk storage, so for example, I don't care that ANALYZE improves join performance, etc.

That was my old ServerFault post from two years ago. Forgive me, it is slightly misworded. It should read:

As for MyISAM, you need to periodically run one of the following:

OPTIMIZE TABLE myisam-tablename;
ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename;

OK, I just fixed it in ServerFault.
CLARIFICATION
OPTIMIZE TABLE tblname; performs the following:
ALTER TABLE ENGINE=MyISAM tblname;
ANALYZE TABLE tblname;

According to the MySQL Documentation on OPTIMIZE TABLE

For MyISAM tables, OPTIMIZE TABLE works as follows:
 If the table has deleted or split rows, repair the table.

 If the index pages are not sorted, sort them.

 If the table's statistics are not up to date
(and the repair could not be accomplished by sorting the index), update them.

The last part on the table's statistics is what ANALYZE TABLE does.
If you do not want OPTIMIZE TABLE tblname; to perform ANALYZE TABLE tblname;, then you must execute ALTER TABLE tblname ENGINE=MyISAM; instead.
WARNING : This may not be a good idea for a MyISAM that has heavy INSERTs, UPDATEs, and DELETEs because

This will make table and index statistics stale very quickly
Stale statistics will cause the Query Optimizer to make bad choices for query EXPLAIN plans

Please just stick with OPTIMIZE TABLE.


No missing index requests on SQL Server 2012 I am trying to optimize the database by creating missing indexes, but the dm_db_missing_index_details table does not have any data.

I have the same database on different server and there are many records in that table. So it looks there may be a configuration problem causing the missing index requests to not be saved.

Why are there missing index requests on one server, but there aren't any on the other server (for the same database)?

We have not restarted the database server for the last four days. I am sure there should be index issues, because the same db is running at a different location and that one has many missing index warnings (and we fixed those).

Depending on which version of SQL Server you're on, missing index (and other index metadata) may disappear when you rebuild indexes.

See Kendra Little's post for more information.


POSTGRES declaring variable containing date array in functions I am trying to declare a date array.

I have tried this:

DECLARE
dateVal DATE[] := ['2018-01-01','2018-02-01'];

I get:

ERROR:  syntax error at or near "["

How do I properly declare it?

You can use ARRAY keyword:

create table tbl (dateval date[]);
insert into tbl values(array ['20180101','20180102'::date]);

Or in a block of code:

do $$
declare
    dateval date[];
begin
    dateval := array ['20180101','20180102'::date];
end;
$$;

Or cast the expression as an array of dates.

do $$
declare
    dateval date[];
begin
    dateval := '{2015-4-12, 2015-4-19}'::date[];
end;
$$;

db<>fiddle here


Replicating different set of tables to subscribers I have transaction pull replication to several subscribers. Publisher has 2008 and distributer and subscribers have 2014.

I need one of the subscribers to replicate more tables.
Is it possible to have different set of tables in subscribers?

Couldn't find such a scenario.

Thanks in advance

Liya

Yes, you just need to have a different publication created for a specific Subscriber with the required articles. This will help you in customizing your subscriber as per the required articles.


Cross-Domain SQL Server Service Accounts I am working on a project in which we wish to have SQL Servers in a test domain make use of accounts in a development domain so that we don’t have to manage multiple forests of accounts.

We’ve established a one-way trust so that the test domain trusts accounts from the dev domain.  We granted ‘log in as a service’ rights to a machine in the test domain for an account on the dev domain.  When we try to install SQL Server and use that account as the service account, the install fails.

Has anyone encountered this issue and is there a resolution?

This is most likely an accounts permission issue.

There are several install logs and utilities that you could check that would help you identify the required permissions.

Sql Server Fails if the setup account doesn't have rights. This article also links to a utility to check the account rights.

The installation log may also be available to view and may give some hints as to why it is failing.

Have you tried the Windows Server Event logs?


Can't start postgres server, SHMMAX too small When I try to start the postgres server with sudo /etc/init.d/postgresql start I get this error:

  Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server
  failed to start. Please check the log output: 2012-10-31 17:52:46 EDT
  FATAL: could not create shared memory segment: Invalid argument
  2012-10-31 17:52:46 EDT DETAIL: Failed system call was
  shmget(key=5432001, size=188080128, 03600). 2012-10-31 17:52:46 EDT
  HINT: This error usually means that PostgreSQL's request for a shared
  memory segment exceeded your kernel's SHMMAX parameter. You can either
  reduce the request size or reconfigure the kernel with larger SHMMAX.
  To reduce the request size (currently 188080128 bytes), reduce
  PostgreSQL's shared_buffers parameter (currently 19200) and/or its
  max_connections parameter (currently 103). If the request size is
  already small, it's possible that it is less than your kernel's SHMMIN
  parameter, in which case raising the request size or reconfiguring
  SHMMIN is called for. The PostgreSQL documentation contains more
  information about shared memory configuration. ... failed!

What should I do here?  Shared_buffers is actually set to 150MB and max_connections is actually set to 100 in the only postgresql.conf file available to it...

I don't know if the request is unusually high, SHMMAX unusually low, or if postgres grabbed some config parameters from the Aether, either seem equally likely...

What am I supposed to make of this, and how do I fix it?

This question has been asked many times. Try a search. Or Google.

This chapter of the manual deals with it comprehensively.


Why would suser_name() not reflect an AD account name change? One of our users' names was legally changed, so we changed their Active Directory username to match - from domain\oldname to domain\newname. However, when suser_sname() is called by this user in a stored procedure, it returns the old name, not the new one.

Googling led me to KB 946358 which suggests that their name is being cached on the server and not updated, presumably because suser_name() is calling LsaLookupSids. However, the workaround in that article involves restarting the server, and even if it was I'd still like to understand the problem.

If I change my context to theirs, the correct name comes back:

EXECUTE AS LOGIN = 'domain\newname'
GO
SELECT suser_name()   --returns 'domain\newname'

...I would have assumed that this would also call LsaLookupSids, and so would return the incorrect name. It seems likely that I don't really understand the mechanisms at work here.

Some observations that may matter:

This user doesn't have an explicit login on the server. But they are a member of an AD group that does. The changed name (domain\newname) appears in the result set for exec xp_logininfo 'domain\ADGroupName', 'members';  domain\oldname does not.
The user is calling suser_name() from within a stored procedure, called from a passthrough query in an Access 2003 MDB.
We've changed plenty of users' account names in the past, but have only observed this issue in the last week (two changes were made in last week, both seem to exhibit the issue).
The server is running Sql Server 2008 SP3 x64 on Windows 2008 R2 Datacenter edition.

What's going on? As the DBA, what might I do or where might I look to solve this?

Could this be related to caching with Kerberos?  (just a guess though might be unrelated)
http://blogs.technet.com/b/tspring/archive/2014/06/23/viewing-and-purging-cached-kerberos-tickets.aspx


Unique pair checking query improving in pivot table First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill.

I have seen an example online.

So I have a pivot table which contains two column: profile_id, followed_id, each column a reference to the Profile.id column, this is a friend list.

I created a query to check for self-loops like:

profile_id | followed_id
         3 | 5
         5 | 3

As in the link this is a "duplication" because it represents the same relation between the profiles.

I created a query to check for duplication, this is a challenge for me. Yes, I added unique constraints to the table, but I was curious about I can write a checker query or not.

This is what I figured out:

SELECT *
  FROM
    tbl_profile_follow A,
    tbl_profile_follow B
  WHERE
    (B.profile_id = A.profile_id AND B.followed_id = A.followed_id)
    AND
    (B.profile_id = A.followed_id AND B.followed_id = A.profile_id)

I have thinking about there are any more effective query for this problem or not. For me it seems I cannot transform this query to join because my pivot table does not use primary key.
However if I use primary key in this pivot table and try to user join will I get better performance?

Of course I apply postgres specific query to learn postgresql :)

Thanks for the answers

The query can be made easier:

select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*)
from tbl_profile_follow
group by least(profile_id,followed_id), greatest(profile_id, followed_id)
having count(*) > 1;

Using this approach you can also create a unique index on the table

create unique index idx_unique_pair
   on tbl_profile_follow (least(profile_id,followed_id), greatest(profile_id, followed_id));

This above will however not prevent or detect multi-level loops:

profile_id | followed_id
         3 |           5
         5 |           6
         6 |           3


select all rows with a minimum value In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value.  I think that I'm limited by not knowing enough of the related terminology to effectively search google.

The table looks like:

num         text        num2
----------  ----------  ----------
0           a           1
0           a           2
1           a           3
1           b           4

I want to get the rows where num2 is 1, 2, and 4.  I want to do the selection based on the minimum value of num for each unique value of the text column.

So, for text = 'a', the minimum value of numis 0, so I want rows 1 and 2.  For text = 'b', the minimum value of num is 1, so I want row 4.

Using various combination of group by, I'm able to get either rows 1 and 2 or rows 1 and 4.  I feel like I'm missing a SQL component that would do what I want, but I haven't been able to figure out what it could be.

What is the proper way to do this type of query?

Possible Solution

I've found a way to do this.  I'm not reputable enough to answer my own question, so I'm doing the update here.  I'm not sure if it is always correct or what the efficiency is like.  Any comments are welcome.

I used a compound select statement, where one query finds the minimum value of num for each unique value of text:

sqlite> select num, text from t group by text having num = min( num );
num         text
----------  ----------
0           a
1           b

Then I joined this with the full table to get all rows matching these two columns.

sqlite> with u as
      ( select num, text from t group by text having num = min( num ) )
        select t.* from t join u on t.num = u.num and t.text = u.text;
num         text        num2
----------  ----------  ----------
0           a           1
0           a           2
1           b           4

As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine.
For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)


Oracle 10gR2 to 11gR2 upgrade - performance hit We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and after the upgrade. Allocation of more memory did improve the performance but just slightly. We also tried to set optimizer mode to 10204 at session level but queries produced no better results. The only difference we've noticed is when enabling autotrace the physical reads on 10gR2 were 0 whereas on 11gR2 it was of the magnitude of tens of thousands.

Including stats demonstrating the physical reads discrepancies:

10gR2 Stats
~~~~~~~~~~
Statistics
--------------------------------------------------------
          0  recursive calls
          0  db block gets
      21189  consistent gets
          0  physical reads
          0  redo size
       1014  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

11gR2
~~~~~~
Statistics
------------------------------------------------------
          0  recursive calls
          0  db block gets
      11243  consistent gets
      11232  physical reads
          0  redo size
       1049  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Any ideas/suggestions/right-directions would be greatly appreciated.

I think you have AMM - Automatic Memory Management - enabled. This quickly brings you in the situation where all memory goes to the shared_pool and leaves you a very small database cache. Looking at the number of IO's this could fit your case.

Start with a memory setup like you had while running 10g. Forget about AMM on a rdbms instance.


Help with tuning this UPDATE Query or possible re-write I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
   Declare @status_D INT,
      Declare @StatusIP INT,
      Declare @Status_F INT,

    SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
    SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
    SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

 UPdate U
        SET
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

  COMMIT TRAN

I am getting warning from blitzcache as Forced serialization,
Expensive sort, non-sargable and Merge join "Mant to Many" = True

The only index we had is on id which is a NC index with id as leading key column.
I will try to update my question if execution plan if i can. Table in SELECT is around 100 million as of now. Also it has PK/CI on col1.
I have asked users why we do * in that TOP which pulls almost 50+ columns but in update use just fewer, may b i am thinking wrong but just came to my mind.
Adding index to modified time also does not seem to help.
Please suggest what else can be checked. Thanks
plan-
Index seek has happened on NC index id

Warnings

The forced serialization is because of the OUTPUT (with no target or to a @table variable will cause this, but not a #temp table)
The expensive
sort is because you're ordering by ModifiedTime without a supporting index and query pattern
The non-SARGable
warning is because (if I remember how I wrote the check correctly),
of the local variables.

How to fix it
You'll need a good supporting index:
CREATE INDEX super_awesome
ON dbo.BatchChangeTable
    (id, ModifiedTime)
INCLUDE
    (EnabledDate);

But the way the query is written, IN will be interpreted as a range predicate, and the ORDER BY ModifiedDate will likely still cause a Sort operation. You'll need to change the form to use two equality predicates with a UNION ALL in order to take full advantage of the index.
You're also going to need to rewrite your query to use dynamic SQL so that you don't get hit with the side effects of local variables.
The finished product will look something like this:
BEGIN TRAN;

/*I'm guessing here, address local factors*/
CREATE TABLE #output(col1 int, col2 int, col3 int, col4 int, id int)

/*Use parameterized dynamic SQL*/
DECLARE @sql nvarchar(MAX) = N''

/*Hello I am your friend*/
SET @sql += N'
UPDATE u
SET
    u.id = @StatusIP,
    u.EnabledDate = GETDATE()
OUTPUT
    deleted.col1,
    deleted.col2,
    deleted.col3,
    deleted.col4,
    Inserted.id
INTO #output
FROM
(
    SELECT TOP (5000)
        b.*
    FROM
    (
         SELECT
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @status_D

         UNION ALL

         SELECT
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @Status_F
         ORDER BY b2.ModifiedTime
    ) AS b
    ORDER BY b.ModifiedTime
) AS u;
';

EXEC sp_executesql @sql,
                   N'@status_D int,
                     @StatusIP int,
                     @Status_F int',
                     @status_D,
                     @StatusIP,
                     @Status_F;

SELECT
    o.*
FROM #output AS o

COMMIT TRAN;

You'll need to tailor this a little to suit your actual situation, but it should get you going in the right direction.


"Incorrect Syntax near S" in Stored Procedure I'm creating a SQL Server stored procedure and getting error

  Incorrect Syntax near S

I'm not able to find the exact reason of the error.

CREATE PROCEDURE RPT_IVR_CALL_SUMMARY
    (@i_APPLICATION_ID     VARCHAR(50),
     @i_SESSION_ID         VARCHAR(50),
     @i_UNIQUERIGHT_ID     VARCHAR(50),
     @i_CALLINGPROC        VARCHAR(50),
     @i_FROM_DATE          VARCHAR(50),
     @i_T@o_DATE           VARCHAR(50),
     @i_CATEGORY           XML,
     @i_CIRCLE             XML,
     @i_SUMMARIZED_BY      VARCHAR(50),
     @i_REPORT_USER        VARCHAR(50),
     @o_ERRORCODE          INT OUTPUT ,
     @o_ERRORDESCRIPTION   VARCHAR(4000) OUTPUT)
AS
    DECLARE @v_ENABLEAUDIT VARCHAR(15),
            @v_QUERY VARCHAR(15);
    DECLARE @I_SYSDATE      DATETIME;
BEGIN
    SET NOCount ON;

    BEGIN TRY
        SET @I_SYSDATE = GETDATE()

        SELECT
            CODE Code,
            CODE Description,
            SUM(OFFEREDCALLS) CallCount
        FROM
            (SELECT
                 (CASE WHEN @i_SUMMARIZED_BY = 'Circle' THEN CALLCIRCLECODE
                                  ELSE VXMLSERVER END)                CODE,
                 OFFEREDCALLS
             FROM
                 TBL_SUMMRESOLUTION
             WHERE
                 (CAST(CONVERT(VARCHAR(13), SUMMDATE, 103) AS DATETIME) - CAST(CONVERT(VARCHAR(13), @i_FROM_DATE, 103) AS DATETIME)) >= 0
                 AND (CAST(CONVERT(VARCHAR(13), SUMMDATE, 103) AS DATETIME) - CAST(CONVERT(VARCHAR(13), @i_T@o_DATE, 103) AS DATETIME)) <= 0
                 AND (CATEGORY IN (SELECT ROLEGROUPLIST.ID.query('Code').value('.','VARCHAR(50)') AS CATEGORYCODE
                                   FROM  @i_CATEGORY.nodes('/Categories/Category') AS ROLEGROUPLIST(ID))
                 AND (CALLCIRCLECODE IN (SELECT  ROLEGROUPLIST.ID.query('Code').value('.','VARCHAR(50)') AS CIRCLECODE
                                         FROM  @i_CIRCLE.nodes('/Circles/Circle') AS ROLEGROUPLIST(ID))
                ) S
   GROUP BY
       CODE;
 END TRY
 BEGIN CATCH
        SET @o_ERRORCODE = ERROR_NUMBER()
        SET @o_ERRORDESCRIPTION = ERROR_MESSAGE()
          IF UPPER(@v_ENABLEAUDIT) = 'ENABLED'
            EXEC PROC_AUDIT_TRAIL @i_APPLICATION_ID,@i_SESSION_ID, @I_SYSDATE, @o_ERRORDESCRIPTION, @v_QUERY, @i_REPORT_USER , @i_UNIQUERIGHT_ID,'RPT_IVR_CALL_SUMMARY'

          SELECT NULL
END CATCH
END

You should add 2 more parentheses -- ))) S. Try it!


Group by specific value and return results in generated column I am attempting to figure out how I can pull this off, but I have no clue how to do it.

I have a table (below) that has object_id's, types and values. The values are always unique and the type is always type1, type2 or type3.

id   object_id   type    value
---------------------------------
1    1           type1   123
2    1           type2   456
3    2           type3   789

The end result I'm trying to get is to consolidate all object_id's and return the types associated in "virtual columns" which are type1, type2, and type3.

object_id   type1  type2  type3
-----------------------------
   1        123    456    null
   2        null   null   789

Not sure if this is possible in mySql or if i should just try and build this in PHP instead. Any help would be greatly appreciated

SELECT
   object_id,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type1') as Type1,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type2') as Type2,
   (SELECT b1.value FROM below b1 WHERE b1.object_id=b0.object_id and b1.type='type3') as Type3
FROM below b0

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=98618f2a316933c6c73260b13e33f15c
or

SELECT
  b0.object_id,
  b1.value as Type1,
  b2.value as Type2,
  b3.value as Type3
FROM below b0
LEFT JOIN below b1 ON b1.object_id=b0.object_id and b1.type='type1'
LEFT JOIN below b2 ON b2.object_id=b0.object_id and b2.type='type2'
LEFT JOIN below b3 ON b3.object_id=b0.object_id and b3.type='type3'

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0437e739f9a9d4cd9923feae6ecd5593


Check lock count for PostgreSQL Up to now we used check_postgres.pl to monitor our database.

We used this to check the count of the locks:

https://bucardo.org/check_postgres/check_postgres.pl.html#locks

We often see more than 150 locks.

I was told this in a different question: How to Debug check_postgresql

  A single DML statement can easily obtain millions of locks. There is no
  relation to the number of statements and the number of locks. But why are
  150 locks a bad thing? Unless they are held for a long time (minutes,
  hours) I don't see any reason to check this.

How can I check for the lock count and ignore all locks which are only N seconds old?

How can I check for the lock count and ignore all locks which are only N seconds old?

You can't... but I think you're asking the wrong question.

PostgreSQL has many different kinds of locking. The three main ones visible to users are:

Row locks
Heavyweight locks
Advisory locks

A row lock is a lock taken by UPDATE, DELETE, SELECT ... FOR UPDATE, SELECT ... FOR SHARE, etc, indicating a claim on a row. You cannot see these in the pg_locks view and they don't consume resources. This is what "a single DML statement can easily obtain millions of locks" refers to.

Heavyweight locks are what people usually mean when they say "lock" without qualifying with the lock type. They're seen in pg_locks. There are many kinds: locks on relations (tables, views, etc); locks to allow one transaction to wait on another, etc. They're only "heavyweight" compared to PostgreSQL's internal lightweight locking mechanism (LWLocks); you don't have to worry unless you start exceeding max_locks_per_transaction.

Advisory locks are a utility locking mechanism for applications. They're done with function calls. They're visible in pg_locks.


Why use foreign keys if cascade action is not need? I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like:

CREATE TABLE user
(
  id int(11) UNSIGNED AUTO_INCREMENT,
  name varchar(30) NOT NULL,
  PRIMARY KEY id
);

CREATE TABLE photo
(
  id int(11) UNSIGNED AUTO_INCREMENT,
  user_id int(11) UNSIGNED,
  filename varchar(50) NOT NULL,
  PRIMARY KEY id
);

A query like:

SELECT p.filename, u.name FROM user AS u
INNER JOIN photo AS p ON u.id = p.user_id

Would result in all records of photos and their user's name.

So if it is possible to create a relation on query, why would I use foreign keys and define NO ACTION, considering I want to keep a history of photos, if there is no need for that? Foreign keys helps on performance or just make data more organized?

The basic reason in my opinion for using constraints - and not only foreign key ones -  is to enforce integrity at the database level and do not rely on the possibly several applications that will use the database in its entire lifetime.

Defining that FOREIGN KEY enforces that every photo - to be inserted - has to be related to an existing user.
Defining NO ACTION enforces that a user cannot be deleted if any related photos are not deleted first.
Defining ON CASCADE DELETE enforces that the photos are deleted along with the user's deletion.
Defining a UNIQUE constraint on username does not allow two users with same name, etc...

In all of the above, the DBMS is entitled to maintain the integrity according to the constraints defined and not allow the database to fall into an inconsistent state.


Database schema for large possible product combinations and associated price I'm new to database schemas and need some help.

I have to draw up a database schema from a large, (2601) possible product combination, with width and height and associated price.

Is creating columns form all the combinations the correct way?

Is creating columns for all the combinations the correct way? Based on the little information you have given us: No, columns for all combinations is probably not the right solution. Imagine if you suddenly get more products, then you will need to add more columns. Or if some products are removed. Designing your schema so you have to alter a table to deal with something as trivial is bad practice. No, you should probably use records instead.

Let's say you have a combination table, something like this:

CREATE TABLE combination (
  combination_id int unsigned auto_increment,
  product_id int unsigned,
  ...
  PRIMARY KEY (combination_id),
  CONSTRAINT combination_fk1 FOREIGN KEY (product_id)
    REFERENCES product(product_id)
);

So you will insert a record for each product in a certain combination.

You can then select from this table, and join with your product table, to get a listing with all the combinations where a certain product is involved:

SELECT c.combination_id, p.product_name, p.width, p.height, p.price
FROM combination c
  JOIN product p USING(product_id)
WHERE p.product_id = 56
ORDER BY c.combination_id, c.product_id;


Does this plan work as a Failover strategy-SQL Server 2016 Standard Edition We have two SQL Server 2016 Standard edition (standard edition due to budget issues) configured one as primary sql server and another one as fail-over server.
I have implemented Transactional replication from primary to secondary server.

To do some maintenance and patching in primary,we have decided to do failover to the other server.But since SQL Server is standard edition,we have no other option but to break replication.I know that Replication is not a Failover solution.
Anyways after patching/maintenance is done we will switch the application back to primary server.But to bring the records which went to failovr server i am thinking to implement Log shipping.So i will restore a latest backup from fail-over to primary and then implement log shipping to synch with the data.

I don't get huge maintenance window in between,that is why i have to do this way.
Do you think this plan will work ?

Do you think this plan will work?

Without a lot of headaches, no I don't. Most times I have witnessed an attempt to use replication for DR it has failed. Every time I've witnessed an attempt to 'fail-back' from a replication 'failover' it has failed miserably.

As you've mentioned, replication is not a HA/DR technology and shouldn't be used as such. If you're looking for a simple solution you can implement quickly, and you're going to log ship back to the primary for fail-back already, why not log ship to the secondary now and use an actual DR technology?

This gives you the option of performing tail log backups on the primary to leave it in a RESTORING state and makes fail-back much simpler. Your whole process is this -

Tail-log backup the primary and disable log shipping jobs on primary
Run the LSCopy and LSRestore jobs on secondary then apply tail-log backup to the secondary. Disable log shipping jobs.
Bring secondary Dbs online. Ensure a log backup job is running against the DBs on the secondary.
Point apps at secondary
Patch the primary
Tail-log backup the secondary
Apply logs and tail-log from secondary to primary
Bring primary online
Point apps at primary
Re-enable all log shipping jobs.

Lastly, if you're on SQL Server 2016 Standard Edition - implement Basic Availability Groups. They're available in Standard Edition, you're paying for an additional server anyway with Replication or Log Shipping, and the whole process is much, much simpler.

Since you have an outage for the apps to re-direct them to the secondary, why not take the opportunity to point them at an AG listener and eliminate this whole problem going forward?


MySQL - Backup new and modified entries only I am developing a very huge system in Java using Hibernate and MySQL. Now, I want to automate the daily, weekly, and/or monthly replication (backing up) of the data that are stored from the database using the system. I am very aware that this could be done easily using the mysqldump command, which could be easily incorporated in Java. But since the data that are handled and stored are very enormous, it came to my mind lately the possibility of backing up only those records that are newly added or modified from the last accomplished dump. Now I'm soliciting for your ideas or procedures on how could this be done. Thanks.

We have a similar process in place, where we run a script to

TRUNCATE TABLE backupTable;
INSERT INTO backupTable SELECT * FROM mainTable WHERE datefield >= xxxx

WHERE x is the time the backup ran the day before. Then we run MySQLDump against backupTable.

Alternatively you could do something similar and set a 'backedup' flag in the main table, and then just

INSERT INTO backupTable SELECT * FROM mainTable WHERE 'backedUp' = 0;

and then update the backedUp flag in the main table.


Nearest neighbours (KNN) from a selected point I have a small point dataset, I perform the following I get the 10 KNN from the coordinate. How do I first select a point from the dataset and with that select the 10 nearest neighbours (not including the original point - if possible)?

SELECT spatial_id, geom
FROM rhp_tvp.spatial
ORDER BY geom <-> 'SRID=23033;POINT(280100 4683200)'::geometry
LIMIT 10

With a SELF JOIN, in the form of an inner join if you know the distance. Or a cross if you don't.

SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
JOIN rhp_tvp.spatial AS rt2
  USING ST_DWithin( rt1.geom, rt2.geom, MAX_DISTANCE )
WHERE rt1 ... -- SELECT POINT.
ORDER BY rt1.geom <-> rt2.geom
LIMIT 10

If you don't know the max distance of one point to another,

SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
CROSS JOIN rhp_tvp.spatial AS rt2
WHERE rt1 ..  -- SELECT POINT
ORDER BY rt1.geom <-> rt2.geom
LIMIT 10;


Can I optimize a left join for a single match? Consider a join A LEFT JOIN B ON A.id = B.a_id:

B.a_id is nullable -- most but not all B records point to an A record.
A can, at most, only have one matching B record (business logic).
Not all A records have a match in B.

Is there a way to tell MSSQL that there will never be more than one A:B match, thus allowing it to shortcut the join once a match is found for a given A record?

I would think that such an optimization could happen if B.a_id had a unique index, but that's not possible since it's nullable.

Complicating the issue: A and B are in different databases (same MSSQL instance).

If it matters, I'm using SQL Server 2008 R2.

Wiki answer from a comment on the question by @ypercube:

You can use a filtered index:

CREATE UNIQUE INDEX a_id_uq
ON b (a_id)
WHERE a_is IS NOT NULL;

See Filtered Index Design Guidelines


Best approach on storing/displaying sanitized data Surfing the web I find two approaches on storing data in databases.

Approach 1:
Store data in the database with the html-specialcharacters sanitized

$userdata = $_POST['field'];
store_in_db_via_PDO(htmlspecialchars($userdata), 'field');
display_on_page(get_from_db('field'));

Approach 2:
Store data as raw as possible in database and sanitize on display:

$userdata = $_POST['field'];
$store_in_db_via_PDO($userdata, 'field');
display_on_page(htmlspecialchars('field'));

BTW I'm not speaking about sqlinjection atacks, these are 'caught` by the PDO-driver

Is there an industry standard on storing data, which shouldn't include html-code, in a database?

I would generally store data in its purest form.

You should verify the data as you read it before pushing it out to the client anyway, in case a bug elsewhere has allowed data that is badly formed (either accidentally or maliciously) to be added to the database, so escaping the data on the way in does not save you any significant processing on the way out.

Storing the data pure also saves you a lot of hassle if you find a bug in the encoding routine or change your mind how you want things encoded (not likely an issue here as you are working to a pre-defined standard with HTML text, but good habits are easier to keep to if you use them religiously even when they make no difference). If you change you encoding standard then data that is stored pure doesn't need to be touched, but existing encoded data needs to be read, decoded, re-encoded using the new rules, and saved again.


what is difference between RDBMS and NoSql on basis of how information is store in them? I am searching web to understand the underlying concept of how actually information is stored in RDBMS and Nosql databases and found this:

  In the world of database technology, there are two main types of
  databases: SQL and NoSQL—or, relational databases and non-relational
  databases. The difference speaks to how they’re built, the type of
  information they store, and how they store it. Relational databases
  are structured, like phone books that store phone numbers and
  addresses. Non-relational databases are document-oriented and
  distributed, like file folders that hold everything from a person’s
  address and phone number to their Facebook likes and online shopping
  preferences.

Here i can understand that in SQL based databases data is stored in form address and data and in NoSql everything is stored in one document.

Please correct me if I am wrong.

Can someone explain this with help of example.

RDBMS (Relational database management system) : is using SQL (Structured Query Language) to define and manipulate data. and it is used for large and complex query handling. in-order to work with RDBMS you need to define a structure (schema, table, constraints, ...etc). as an example (out of  a town in-order for the people to live together they need to speak the same language, have a infrastructure, laws,..etc

NoSQL (Not only SQL "none relational database"): it is like a blog, you can store the data any way you like (some use a something like table called column oriented, document oriented, graph-base, KeyValue (one of the most common uses of keyValue to call data and store them in JSON file). as an example earth since there is no common structure to be used or a define language that all people speak, the way they build houses, and infrastructure is different but differences between one place and another doesn't effect other places.

I found this image as an example in https://www.upwork.com it explain a the differences between RDBMS and NoSQL in another way.

Useful URLs:
https://www.upwork.com/hiring/data/sql-vs-nosql-databases-whats-the-difference/
https://medium.com/xplenty-blog/the-sql-vs-nosql-difference-mysql-vs-mongodb-32c9980e67b2   (this can be used as an exmaple)
What are the differences between NoSQL and a traditional RDBMS?


Improve performance by combining two quieries I have following submission table. A submission is an image.

create table submission
(
    md5 varchar(255) not null
        constraint submission_pkey
            primary key,
    user_name varchar(255)
        constraint fkmc6ld10rt5dgfsypoanfxifaj
            references users,
    copy_of varchar(255),
    create_date timestamp
);

copy_of is the submission_pkey (md5) of another user who has posted the same submission (image).
In other words if two users post the same image the later submission will contain an copy_of to the submission_pkey of the first image.

Now I am looking for a query which given one user_name will return a set of other usernames who have posted the same submission.
In this list I also want the usernames which the current user_name has copied the submissions from.

For this I have created two queries which together return the desired result set:

First:

select (select name
        from users
                 inner join submission s2 on users.name = s2.user_name
        where s2.md5 = s.copy_of) as other
from users
         inner join submission s on users.name = s.user_name
where copy_of is not null
  and name = ?1
group by name, other;

Second:

select (select user_name from submission where copy_of = s.md5 limit 1)
from users
         inner join submission s on users.name = s.user_name
where name = ?1
  and md5 in (select copy_of from submission s2);

Both (especially the second) queries run quite slow. Is there a way to improve those queries and ideally combine them to return one distinct list of user_name? I feel like the join with the users table should not be needed since all the relevant data is in the submission table but I have not found a way without it.

Can it be?

select distinct s2.user_name
from submission s1
  join submission s2
    on s1.md5 = s2.copy_of
    or s1.copy_of = s2.copy_of
    or s1.copy_of = s2.md5
where s1.user_name = ?1
 and s2.copy_of is not null;

s1.md5 = s2.copy_of will match submission which is copy from this user.
s1.copy_of = s2.copy_of or s1.copy_of = s2.md5 will match submission which current user copy from.


Merging two Access tables into one I have mostly identical tables, however there are slightly differences like missing columns in older ones. I want to merge 5 tables into one. Is there any easy way to do this?

Create a new table that have all the columne in all your 5 tables, then create the needed SQL statment like SELECT * FROM Tb1 INTO TableWithAllColumns
eventually add some WHERE to exclude the duplicate rows from the insert.

This will work if your table doesent have similar rows.
If you need to merge similar rows together, there is no a simple  way to tho this.
The best you can do is find, or write, a tool that can compare all the value in all the columns and act in the right way.


MySQL thinks Master & Slave have same server-id, but they don't Trying to setup MySQL to run one-way replication. I have the master set to server_id = 1, a replication user setup, binary logging enabled, the Slave server_id = 2 and is connected and waiting for an event. I am also using Workbench.

However, the Master server has the following error...

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

I can't figure out any reason for this error. I have looked at dozens of tutorials, manuals, etc and none of them is explaining to me what is happening.

The server-id is stored in the /var/lib/mysql/auto.cnf in addition to the standard my.cnf config file.

When cloning the data directory from master to slave, you need to delete the auto.cnf file in the data directory (i.e. /var/lib/mysql) before starting the slave.


Possibly inconsistent database? Suppose I have a database schema with 3 tables - Area, Work and Author.

Area is a table which contains the list of Areas of interest of an Author / Areas in which a Work has been published, for example Math, Economics, Computer Science.

Author is a list of authors.

Work is a list of published articles.

Work_Area is a many to many relationship describing the area(s) of a Work. For instance Work with ID 1, can be on Math and Economics.

Work_Author is a many to many relationship between Work and Authors. For instance Work with ID 1 can have Authors with ID 1 and 2.

Author_Area is the list of interests of an Author.

Suppose An Author has Math has an interest and he publishes an Article on Math then there is no problem.

Instead an Author who has Math as an interest publishes an Article on Economics. Then my database becomes inconsistent in the following sense:-

When I look up the interests of this Author in the table Author_Area then I get ONE result Math.
When I join the Author with Work_Author and further join this with Work_Area and extract the list of all Areas in which the author has published work then I get TWO results - Math and Economics.

My query is : How can I prevent this inconsistency in my database?

Also, what is this type of inconsistency called ? Does it have a name in the literature?

A quick solution might be to make a trigger in the Work_Area table that checks the Interests table to see if that field already exists, and if it does not then insert it as a new record. This would prevent the need to manually add an author's new interests, just add their new article and let the interests table take care of itself


How to structure db where main records are large I was wondering if I could ask for some quick opinions.
I am prototyping an app which seems simple but I can’t readily arrive at an architecture that feels right.
We have data that is simple and fits well into standard sql format, eg users, organisations, projects, reports.
However, the ‘reports’ are the problem. They can typically be between 1000 and 100,000 'line items'. Each item with around 10 fields. A bit like a large spreadsheet, and in fact this is where they originate usually.
A user (lets say 100 of them) generates say 10 projects a year, and each project has say 10 reports, and each report has say 10000 items (or rows) .. then if there was one massive table quickly growing to millions of rows, with each row ‘belonging’ to a report etc .. well this doesn’t feel right.
To make this more tricky, the reports themselves can almost be considered 'mini-databases' in themselves, in that although the meat of the data is thousands of 'line items' (with uniform column requirements), they are structured into 'zones', 'sections', 'subsections', and there is also an 'overview/meta' section as well.
In theory, I could normalize this right from 'user' to 'item' something like users->projects->reports->zones->sections->subsections->items ... but again this just doesn't feel right to me; what the DB has to do to simply go and get a report seems overkill. Also all of this report info would then be all mashed together for all the different user accounts.
Every report query would have to go a collect thousands of rows out of millions, and assemble the client json from different tables, and there would be no other real benefit from being able to query the table in this way. All the reports are self-contained really.
As such, I am wondering whether this a case for a nosql route, like mongo or documentDB, where I can just throw thousands of lines of json in a Report Collection or similar. This would have further implication though, as the backend frameworks I like to use don’t play all that nice with nosql, and we lose a bit of the standard easy relational schema that would still seem to fit the other tables of the app (e.g. user accounts, RBAC, users belonging to organisations, etc).
.. but there would be a similar question with a json store .. is it better to maintain a 'tree' structure, and keep all the line items within their sections, and sections within their zones etc, or normalize out so there is one big load of lineitems, that have references to their sections within them, etc.
I’ve messed around with using MySQL for everything, and using the json field type to store the reports as one big json, but I don’t know whether this is pushing the intention of the field-type a little, and it’s hard to query into.
A consideration here is that on the frontend, users will update one (or a few) row(s) at a time, and I need to be able to handle that. In this way, I suppose there's a similarity with google sheets, or airTables or something. Do these big apps tend to use rdbms?
I notice there seem to be more and more database options appearing all the time now, eg fauna supabase etc .. makes it even more tricky to choose the right solution.
Thanks in advance for any tips

NoSQL is not a direct solution to an amount of data problem, rather it is mainly a solution for a schema-less or highly variable schema problem (as you've experienced when you said "we lose a bit of the standard easy relational schema" regarding using NoSQL). Because you admit your data is highly relational, fitting a standard schema, then you'll likely want to continue to use a RDBMS like MySQL.
That being said, millions of rows in a table is on the small side and certainly no concern for a relational database when architected and indexed appropriately. If I play out the calculation with your example's numbers above, it seems like we're actually talking about 100 million records per year, which is now what I subjectively consider a medium sized table. The fact the width of the table is only 10 columns helps keep it compact as well. This is still no problem for any modern RDBMS. I've personally worked with larger tables in the 10s of billions of records, about 50 columns wide, and regular B-Tree indexing still works amazingly well even at that scale. I could query thousands of rows from that table in a second or less and millions of rows from that table in about 10 seconds, on very modest hardware.
Aside from appropriately indexing and architecting your database, you can use things like Partitioning, and routinely archiving old data that is less frequently queried into a separate table. Long story short, it sounds like your architecting things correctly and I wouldn't stress too much on the amount of data you plan to support yet, until it becomes problematic (which I don't think it will).


MySQL - Master to master replication without downtime in Live production I want to setup 2 node master-master replication. Im aware of the point of failiures in this replication. Its a huge database around 1.5TB.
Heavy OLTP is going on. But its mandatory to implement right now.

I have 2 servers.

 1. Master1
 2. Master2

Initially what I did is,

Master1:

Make changes my.cnf for replication.
Take mysqldump with --master-data=2 (For getting exact binlog
position)

Master2:

Restore dump to Master2.
Make changes on my.cnf.
set replication to Master1 (Change master='Master1').
Start Slave.

  Now Master1 is replicating to Master2.

Then I need to replicate the Master2 to Master1.
Just take the binlog position and set replication on Master1 will work, but the problem is binog file and position is changing frequently.
So before execute the Change master command on Master1 the binog get changed.

How can I achive this without downtime.

I am glad you were able to mysqldump 1.5TB database. You are sure patient.

Now for the reality check. You will some downtime for reboots and pausing.

No need to mysqldump anymore.

STEP 01

Did you put this line in /etc/my.cnf on both Master1 and Master2 ?

log-slave-updates

If you not sure, run this on both Master1 and Master

select @@global.log_slave_updates;

If you get this on both Master1 and Master2

mysql> select @@global.log_slave_updates;
+----------------------------+
| @@global.log_slave_updates |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

then goto STEP 02.

This needs to be in /etc/my.cnf on both Master1 and Master2.

If you did not, please add it and restart mysqld on both Master1 and Master2.

STEP 02

Did you enable binary logging on Master2 ?

You should have a line in Master1's /etc/my.cnf like this

log-bin = ....

Please make sure Master2 has it.

If you are not sure if it has it, login to MySQL on Master2 and run

mysql> show binary logs;

If it echoes

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

Then, add that log-bin line to /etc/my.cnf in Master2 and restart mysql in Master2.

Once log-slave-updates and log-bin have been enabled on both Master1 and Master2, you are ready to setup replication in the other direction

STEP 03

On Master2

STOP SLAVE;
FLUSH TABLES;
FLUSH LOGS;
SHOW BINARY LOGS;

STEP 04

On Master1

CHANGE MASTER TO
    MASTER_HOST='IP or Hostname of Master2',
    MASTER_PORT=3306,
    MASTER_USER='...',
    MASTER_PASSWORD='...',
    MASTER_LOG_FILE='First Binary Log On Master2',
    MASTER_LOG_POS=4
;
SHOW SLAVE STATUS\G

STEP 05

Test Connectivity from Master1 to Master2

On Master1

START SLAVE IO_THREAD;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Look for Slave_IO_Running. It should be 'Yes'.

Run SHOW SLAVE STATUS\G like 3-5 times. Relay_Log_Space should be increasing.

STEP 06

Start processing SQL from Relay Logs

Run this on Master1

START SLAVE SQL_THREAD;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Look for Slave_SQL_Running. It should be 'Yes'.

Look for Seconds_Behind_Master. It should be 0. If it greater than 0, keep running SHOW SLAVE STATUS\G until Seconds_Behind_Master is 0.


How can I automatically have a node become primary in an Availability Group? In my environment, I have Db01 and Db02 setup with SQL 2012 AlwaysOn. Db01 is the preferred master as that is where all of the backup jobs reside. Typically, the only time that Db01 is unavailable is during maintenance period of installing Windows Update.

Is there a way to always have Db01 be the primary when it is a member of the cluster? Currently, when Db01 rejoins the cluster after a reboot, it is marked as the secondary, since Db02 has become the primary. I would like to see Db01 join the cluster and then automatically (or via a command executed on startup) be designated primary.

Db01 and Db02 are both set to SYNCHRONOUS_COMMIT and Automatic Failover Mode

You need to set that in the AG ROLE properties as below :

Windows server 2012


Include parent node ID in hierarchical recordset of child rows I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a specified parent, but I would like to also include the parent id on each row.

WITH level_1 (category_id, category_name) AS (
  SELECT
   category_id,
   category_name
  FROM category
  WHERE
   parent_category_id = 1
  UNION ALL
  SELECT
    c.category_id,
    c.category_name
  FROM category AS c CROSS JOIN level_1
  WHERE
    c.parent_category_id = level_1.category_id
)
select * from level_1

The query as written returns the following:

category_id | category_name
         64 | Cows
         6  | Pigs
         11 | Holstein
         23 | Bantu

What I want though is below because I want to group on those parent categories but I won't necessarily know what they are except that they have a parent_category_id = 1.

 root_id | category_id |  category_name
      64 |          64 |  Cows
       6 |           6 |  Pigs
      64 |          11 |  Holstein
       6 |          23 |  Bantu

Category table is like below.

   category_id         |  unique row identifier
   category_name       |  varchar
   category_parent_id  |  foreign key to category table

It's hard to understand your requirements without seeing the definition of category - Presumably, there is a root_id column in that table?

If there is, perhaps you want this?

;WITH level_1 (category_id, category_name, root_id) AS (
    SELECT category_id,
        category_name,
        root_id
    FROM dbo.category
    WHERE parent_category_id = 1
    UNION ALL
    SELECT c.category_id,
        category_name,
        root_id
    FROM dbo.category AS c
    CROSS JOIN level_1
    WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
    , category_name
    , root_id
FROM level_1;

Just FYI, it's good practice to begin a CTE with a semicolon, to specify the schema, and avoid using SELECT *


SQLite - Get all other rows with any duplicate column value I have a table of users, with information about them and the combination of the fields and UserName is unique (A row as a whole, not only on one field)

Users

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum1 | AAABBB | CCCDDD | EEEFFF |
| UserNum2 | AAABBB | IIIJJJ | KKKFFF |
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ |
| UserNum4 | RRRSSS | TTTUUU | VVVWWW |
+----------+--------+--------+--------+

Given one column value to find, I have to find other users with ANY same column values for all columns dynamically.

For example, I only provided the username "UserNum1" as UserName, It should return User 2 and 3 because of the values of their Field1 and Field2 respectively.

Desired output:

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum2 | AAABBB | IIIJJJ | KKKFFF | # Field1 has same value on User1 & User2
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ | # Field2 has same value on User1 & User3
+----------+--------+--------+--------+

Is there ways to do this in one query? Or do I really have to query each column and do the other processing in my backend

FYI: The initial problem is, finding possible user duplicate accounts or "alts" based on similar fields

sqlite> select
   ...>    Username,
   ...>    Field1,
   ...>    Field2,
   ...>    Field3,
   ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field1=u1.field1)  as F1check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field2=u1.field2)  as F2check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field3=u1.field3)  as F2check
   ...> from User u1;
Username    field1      field2      field3      F1check     F2check     F2check
----------  ----------  ----------  ----------  ----------  ----------  ----------
UserNum1    AAABBB      CCCDDD      EEEFFF      UserNum2    UserNum3
UserNum2    AAABBB      IIIJJJ      KKKFFF      UserNum1
UserNum3    LLLMMM      CCCDDD      PPPQQQ                  UserNum1
UserNum4    RRRSSS      TTTUUU      VVVWWW
sqlite>

NOTE: if you have more values that are double, only the first one is show, i.e. if you have also this record:

insert into User values ('Usernum5','AAABBB','XXXYYY','XXXYYY')

then the output will show:

sqlite> select
   ...>    Username,
   ...>    Field1,
   ...>    Field2,
   ...>    Field3,
   ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field1=u1.field1)  as F1check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field2=u1.field2)  as F2check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field3=u1.field3)  as F2check
   ...> from User u1;
Username    field1      field2      field3      F1check     F2check     F2check
----------  ----------  ----------  ----------  ----------  ----------  ----------
UserNum1    AAABBB      CCCDDD      EEEFFF      UserNum2    UserNum3
UserNum2    AAABBB      IIIJJJ      KKKFFF      UserNum1
UserNum3    LLLMMM      CCCDDD      PPPQQQ                  UserNum1
UserNum4    RRRSSS      TTTUUU      VVVWWW
Usernum5    AAABBB      XXXYYY      XXXYYY      UserNum1
sqlite>

Where you see (on the line 'Usernum5') only a reference to Usernum1, and not to Usernum2.


How to prevent SQL Server Job History deadlocks Out of the ~50 instances I currently manage, I have two that each have one deadlock almost every night involving "INSERT INTO msdb.dbo.sysjobhistory" (always the victim) and "DELETE FROM msdb.dbo.sysjobhistory" called by "EXEC msdb.dbo.sp_purge_jobhistory", both sessions run by the SQL Server Agent service account. I have never had such a deadlock on any of the other instances, nor at any previous jobs. One each of these servers, the deadlocks occur at different times during the night (1:30 Saturdays run by a weekly schedule for sp_purge_jobhistory or 2:01 run by a step in the nightly schedule for syspolicy_purge_history).

There doesn't seem to be anything significantly different between these two "problem" instances and any of the others. One is a 2012 SP3 CU3 test server and the other is 2012 SP2 production instance.

Any ideas as to how I can prevent this?

I wrote this (a much simplified version of the code in @Taiob's blog post he linked to), and then call it from a job instead of the Microsoft-provided sp_purge_jobhistory:

--  Create Procedure PurgeJobHistory.sql

USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (SELECT 1 FROM sys.procedures p JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE s.name = N'dbo' AND p.name = N'PurgeJobHistory') BEGIN
   EXEC('CREATE PROCEDURE [dbo].[PurgeJobHistory] AS BEGIN SET NOCOUNT ON; END');
END;
GO

ALTER PROCEDURE [dbo].[PurgeJobHistory]
    @numberOfDaysToKeepSucceeded    SMALLINT    =   7
    ,@numberOfDaysToKeepOther       SMALLINT    =   30
AS
/*
Purpose:        Purges old rows from msdb.dbo.sysjobhistory, providing for different retention for success vs. other statuses
Author:         Mark Freeman (@m60freeman)
Last Edited:    2017-03-29
Compatibility:  SQL Server 2008 and newer
Adapted from:   http://sqlworldwide.com/manage-sql-agent-job-history/
License:        This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
                    http://creativecommons.org/licenses/by-nc-sa/3.0/
*/
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

DECLARE @LastDateToRetainSucceeded  INT
        ,@LastDateToRetainOther     INT
        ,@RowsDeleted               INT
        ,@RowCount                  INT;

--Get the actual date of the most recent rows that we wish to maintain (converted match sysjobhistory.run_date)
SELECT  @LastDateToRetainSucceeded  = CONVERT(INT, CONVERT(VARCHAR(200), (GETDATE() - @numberOfDaysToKeepSucceeded), 112))
        ,@LastDateToRetainOther     = CONVERT(INT, CONVERT(VARCHAR(200), (GETDATE() - @numberOfDaysToKeepOther), 112))
        ,@RowCount                  = COUNT(1)
  FROM  msdb.dbo.sysjobhistory sjh WITH (NOLOCK);

RAISERROR('Starting rows in history: %i', 0, 1, @RowCount) WITH NOWAIT;

--DELETE sysjobhistory rows 1,000 at a time in a loop until we have nothing left to delete
SET @RowsDeleted = -1;

WHILE @RowsDeleted != 0 BEGIN
    --Handle rows with a Succeeded status
    BEGIN TRAN;
        DELETE  TOP (1000)
          FROM  msdb.dbo.sysjobhistory WITH (READPAST)      --Ignore locked rows
         WHERE  run_status  =   1                           --Succeeded
           AND  run_date    <   @LastDateToRetainSucceeded;

        SELECT  @RowsDeleted = @@ROWCOUNT;
        RAISERROR('Succeeded rows deleted: %i', 0, 1, @RowsDeleted) WITH NOWAIT;
    COMMIT;

    WAITFOR DELAY '00:00:01';
END;

SET @RowsDeleted = 1;

WHILE @RowsDeleted != 0 BEGIN
    --Handle rows with all other status values
    BEGIN TRAN;
        DELETE  TOP (1000)
          FROM  msdb.dbo.sysjobhistory WITH (READPAST)      --Ignore locked rows
         WHERE  run_status  IN  (0, 2, 3)                   --Failed, Retry, and Canceled are all treated as failures.
           AND  run_date    <   @LastDateToRetainOther;

        SELECT  @RowsDeleted = @@ROWCOUNT;
        RAISERROR('Other rows deleted:     %i', 0, 1, @RowsDeleted) WITH NOWAIT;
    COMMIT;

    WAITFOR DELAY '00:00:01';
END;

SELECT  @RowCount   = COUNT(1)
  FROM  msdb.dbo.sysjobhistory sjh WITH (NOLOCK);

RAISERROR('Ending rows in history: %i', 0, 1, @RowCount) WITH NOWAIT;
GO

It cut us down from having over 1 million rows in msdb.dbo.sysjobhistory to only about 1/4 as many, and the deletes are no longer involved in deadlocks.

Thanks to @Taiob and @ScottHodgin for their input!


update most recent rows only? I have a database of a garage of rented cars with a table called Cars with the following columns :
Type,First_seen_in_garage,Last_seen_in_garage
The table initially have the following rows :
Ferrari,2002-10-13 07:12:58,2002-10-13 10:12:58
Lambo,2002-10-13 08:12:58,2002-10-13 11:12:58
Renault,2002-10-13 09:12:58,2002-10-13 12:12:58
Ferrari,2002-10-13 10:12:58,2002-10-13 15:12:58

Now I have the following row from which I have to update the initial table :
*Ferrari,2002-10-14 08:12:58,2002-10-14 10:12:58*
*Renault,2002-10-13 23:12:58,2002-10-14 23:12:58*
*Lambo,2002-10-13 23:12:58,2002-10-14 23:12:58*

in order to become :
Ferrari,2002-10-13 07:12:58,2002-10-13 10:12:58
Lambo,2002-10-13 08:12:58,2002-10-14 23:12:58
Renault,2002-10-13 09:12:58,2002-10-14 23:12:58
Ferrari,2002-10-13 10:12:58,2002-10-14 10:12:58

Note that for the Ferrari rows I want to keep the first instance as it is and update only the second one
I could do it with an UPDATE .. SET .. WHERE if there was only one Ferrari instance however the previous query updates the two rows which is not what I want ..
Help Please.

As you don't tell us ´,w hat for an Update you will have,
I selected one . where the lst of a type get 5 Minutes more on his End time, this is of corse only an example for an   update, to show how it works.
The latest seen time we get from the WINDOW Function  MAX OVER PArtition
And the update only update the types with the latest time (i choose Last_seen_in_garage) because the update only updates the rows , that are joined together
In the new query i also check out the same data from the temptaböe(see that Ferrair has now 2020 date as Last_seen_in_garage and use those data to update the CARS table

CREATE TABLE Cars  (
  "Type" VARCHAR(7),
  "First_seen_in_garage" timestamp ,
  "Last_seen_in_garage" timestamp
);

INSERT INTO Cars
  ("Type", "First_seen_in_garage", "Last_seen_in_garage")
VALUES
  ('Ferrari', '2002-10-13 07:12:58', '2002-10-13 10:12:58'),
  ('Lambo', '2002-10-13 08:12:58', '2002-10-13 11:12:58'),
  ('Renault', '2002-10-13 09:12:58', '2002-10-13 12:12:58'),
  ('Ferrari', '2002-10-13 10:12:58', '2002-10-13 15:12:58');

✓

4 rows affected

  CREATE TABLE Tmp_table  (
  "Type" VARCHAR(7),
  "First_seen_in_garage" timestamp ,
  "Last_seen_in_garage" timestamp
);

INSERT INTO Tmp_table
  ("Type", "First_seen_in_garage", "Last_seen_in_garage")
VALUES
  ('Ferrari', '2002-10-13 07:12:58', '2002-10-13 10:12:58'),
  ('Lambo', '2002-10-13 08:12:58', '2002-10-13 11:12:58'),
  ('Renault', '2002-10-13 09:12:58', '2002-10-13 12:12:58'),
  ('Ferrari', '2020-10-13 10:12:58', '2020-10-13 15:12:58');

✓
4 rows affected

 UPDATE Cars
SET "Last_seen_in_garage" = maxtempcar."maxdateime"
FROM (SELECT DISTINCT Cars."Type",
       MAX("Last_seen_in_garage") OVER (PARTITION BY Cars."Type") "maxdateime"
       FROM Cars) maxcar
INNER JOIN (SELECT DISTINCT Tmp_table."Type",
MAX("Last_seen_in_garage") OVER (PARTITION BY Tmp_table."Type") "maxdateime"
FROM Tmp_table) maxtempcar ON maxtempcar."Type" = maxcar."Type"
WHERE Cars."Type" = maxcar."Type" AND Cars."Last_seen_in_garage" = maxcar."maxdateime";

3 rows affected

SELECT *
FROM Cars
;

Type    | First_seen_in_garage | Last_seen_in_garage
:------ | :------------------- | :------------------
Ferrari | 2002-10-13 07:12:58  | 2002-10-13 10:12:58
Lambo   | 2002-10-13 08:12:58  | 2002-10-13 11:12:58
Renault | 2002-10-13 09:12:58  | 2002-10-13 12:12:58
Ferrari | 2002-10-13 10:12:58  | 2020-10-13 15:12:58

db<>fiddle here


Change tables from one user to another I created some objects in MS SQL Server 2008 using SubSonic 3. It created them based on the user specified in connection string like:

test_user.table1

but for some reason I am not able to test_user account. Now I am not able to access the tables within test_user schema. Is there any way to transfer schema from test_user to dbo so that any login can access it?

ALTER AUTHORIZATION ON test_user.table1 TO dbo

OR

ALTER SCHEMA dbo TRANSFER test_user.table1

The older sp_changeobjectowner is deprecated

Some FYI: SQL: transfer database schema


How to Audit any change in Permission? Is there any way how to know "Who did change the permission on users"

Like:

who give read permission or owner on a database for a user?

Using SQL Server 2014

You can find sys.database_permissions for all the permissions of all objects in database including who did grant permission.


Do stored procedures prevent SQL injection? Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection even if we only use stored procedures. However, this problem does not exist in PostgreSQL.

Does the stored procedure implementation in PostgreSQL core prevent SQL injection attacks or is it something else? Or is PostgreSQL also susceptible to SQL injection even if we only use stored procedures? If so, please show me an example (e.g. book, site, paper, etc).

No, stored procedures do not prevent SQL injection.  Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection:

This sql server code:

CREATE PROCEDURE [dbo].[sp_colunmName2]
    @columnName as nvarchar(30),
    @type as nvarchar(30),
    @searchText as nvarchar(30)
AS
BEGIN
    DECLARE @SQLStatement NVARCHAR(4000)
    BEGIN
        SELECT @SQLStatement = 'select * from Stations where '
            + @columnName + ' ' + @type + ' ' + '''' + @searchText + ''''
        EXEC(@SQLStatement)
    END
END
GO

roughly equivalent to postgres:

CREATE or replace FUNCTION public.sp_colunmName2 (
    columnName  varchar(30),
    type varchar(30),
    searchText  varchar(30) ) RETURNS SETOF stations LANGUAGE plpgsql
AS
$$
DECLARE SQLStatement VARCHAR(4000);
BEGIN
    SQLStatement = 'select * from Stations where '
            || columnName || ' ' || type || ' ' || ''''|| searchText || '''';
    RETURN QUERY EXECUTE  SQLStatement;
END
$$;

The developer's idea was to create a versatile search procedure, but the result is that the WHERE clause can contain anything the user wants, allowing a visit from little Bobby Tables.

Whether you use SQL statements or stored procedure doesn't matter.  What matters is whether your SQL uses parameters or concatenated strings.  Parameters prevent SQL injection; concatenated strings allow SQL injection.


how to speed up cpu limited mysql database backup I have a database that has a structure that unfortunately requires having a lot of tables (10k+). It's not possible to change the structure now.

The database itself is not very large (<1GB), but the backups are taking a very very long time, something like 40 minutes. It is a CPU limited process as it runs one core on the server at 100% for the entire duration.

The command used to do the backup is:

mysqldump -u root -password --single-transcation --skip-lock-tables --all-databases > dump.sql

Are there any tricks to speed up this backup?