texts: Live monitoring SQL statements on SQL Server

texts:
How can I monitor running SQL Statements for my SQL Server, is it possible using only SQL Server without 3rd party tools?

As an alternative can anybody recommend any free tool which can monitor running statements over ODBC connections?


texts: Get the last ID after INSERT the PostgreSQL database with PgBouncer

texts:
Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL.
INSERT INTO "table" ("column") VALUES ('ABC');
SELECT pg_get_serial_sequence('table', 'tableid') AS seq;
SELECT CURRVAL('public.table_tableid_seq') AS ins_id;

But the use of the Session mode of PgBouncer proved to be extremely problematic for us, with several operations per second, the Pool easily reached the capacity of connections, so we switched to PostgreSQL 12 with PgBouncer in Transaction mode.
One problem that has begun to occur with this mode is that using operations that require being in the same session does not work, not allowing to use CURRVAL.
What is the best alternative to continue working in Transaction mode and get the ID after INSERT, without the risk of returning the ID generated by the same operation by another user?
An important detail is that we use a PHP Framework that does not allow  to add an INSERT ... RETURNING for each operation.


texts: 'You cannot add or change a record because a related record is required in table 'Booking'.' error when removing a PKFK value in a SQL database in C#

texts:
System.Data.OleDb.OleDbException: 'You cannot add or change a record because a related record is required in table 'Booking'.'

My sub-routine to get rid of the booking with a given BookingID in the Textbox 'TxbID' is given by:
Cmd.CommandText = "SELECT BookingID, Occupied FROM Room WHERE Room.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "DELETE FROM Booking WHERE Booking.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "DELETE FROM Occupant WHERE Occupant.CustomerID = '" + CID + "'";

My table creation code is:
Cmd.CommandText = "CREATE TABLE Occupant(CustomerName VARCHAR, CustomerNumber CHAR(11), CustomerID VARCHAR, PRIMARY KEY (CustomerID))";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "CREATE TABLE Booking(BookingID VARCHAR, DateIn DATE, DateOut DATE, TotalPrice FLOAT, CustomerName VARCHAR, CustomerID VARCHAR, FOREIGN KEY(CustomerID) REFERENCES Occupant(CustomerID), PRIMARY KEY (BookingID))";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "CREATE TABLE Room(BookingID VARCHAR, RoomNumber VARCHAR, RoomSize VARCHAR, RoomPrice FLOAT, Occupied BIT, PRIMARY KEY (RoomNumber), FOREIGN KEY(BookingID) REFERENCES Booking(BookingID))";
Cmd.ExecuteNonQuery();

I'm working on my computer science A-Level project so help is very appreciated.
Edit:
This function only allows number 0-9 to be input so injection shouldn't be an issue here? (The booking ID is an integer)
private void TxbID_TextChanged(object sender, EventArgs e)
{
     if (System.Text.RegularExpressions.Regex.IsMatch(TxbID.Text, "[^0-9]"))
     {
          MessageBox.Show("Please enter only numbers.");
          TxbID.Text = TxbID.Text.Remove(TxbID.Text.Length - 1);
     }
 }

And UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "' is where my error occurs.
My goal is to be able to delete a booking from the database by deleting the row from Occupant and Booking. My Room table is filled with the information for each room along with a BookingID which I wanted to set blank when the booking is deleted. How do I go about that?


texts: PostgreSQL closing connection during requests

texts:
I have a PostgreSQL 10.12 server running on an Ubuntu 18.04 Server. Since today, I am suddenly getting errors when querying data from a Python script running on the same machine, and in some edge cases also in pgAdmin 4.

In about 50% of the cases, my Python script will fail to return any data because the DB server closed the connection. I am using SQLAlchemy and it is giving me the following error:

psycopg2.errors.AdminShutdown: terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Interestingly, the same queries (which are simple "SELECT * FROM table" statements) run perfectly fine in pgAdmin. However, when I try to run the following query in pgAdmin, I get the message Connection to the server has been lost.

select * from mytable where mycolumn <> ''

This query specifically worked fine a few days ago. I couldn't find any other examples where this happens in pgAdmin, but there is surely something wrong.

What is weird is that the database has been running for over a month without any issues, and this only started today. I did try to restart the service to no avail.

The logs are not making much sense to me, but there are several things wrong.

The last few days, it constantly gave me the following message:

2020-04-06 19:43:26.959 CEST [7820] postgres@postgres FATAL:  password authentication failed for user "postgres"
2020-04-06 19:43:26.959 CEST [7820] postgres@postgres DETAIL:  Password does not match for user "postgres".
    Connection matched pg_hba.conf line 102: "host    all             all             all                     md5"

I am certain that I am using the correct password, as it does work 50% of the time of the queries. It is showing this message for the default postgres database and not my own, but it still confuses me why this happens as I'm using the default postgres user for both databases. I got this message a lot during the night where I was not doing anything with it either.

For the actual error regarding the query from pgAdmin, this is what I get:

2020-04-07 11:25:18.891 CEST [5497] LOG:  server process (PID 20569) was terminated by signal 11: Segmentation fault
2020-04-07 11:25:18.891 CEST [5497] DETAIL:  Failed process was running: SELECT * FROM transactionoutputs WHERE fileheader <> ''
2020-04-07 11:25:18.891 CEST [5497] LOG:  terminating any other active server processes
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin WARNING:  terminating connection because of crash of another server process
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin HINT:  In a moment you should be able to reconnect to the database and repeat your command.

I can't seem to find any errors in the logs for the regular SELECT queries from my Python scripts, so I don't know why the connection is closed for this all the time. What is so confusing about this is that it worked perfectly fine just a yesterday, and I have not changed anything. Restarting PostgreSQL also did not help.

I'm not an expert with databases or Linux, so any help why this might happen and how I can figure out what exactly is happening is highly appreciated, thank you.


texts: MySQL get_lock timeout misunderstanding?

texts:
Am I missing something here. I'm looking at using GET_LOCK to allow two applications to co-ordinate which data to wok on.

From reading the manual it suggests that I need to apply a timeout to the GET_LOCK command.

e.g.

SELECT GET_LOCK('testlock1',10);

So I'm thinking that this means I am creating a lock called testlock1 that will remain in place until either I release it, or 10 seconds has passed.

If I then open another session (simulating a second application), and try to run SELECT GET_LOCK('testlock1',10); it just returns a 0.

No matter how long I leave it, the lock remains in place.

I would presume the full process would be something like:

SET @id = "SELECT id from dbname.tablename WHERE . . . "
SELECT GET_LOCK(CONCAT(@id,'-dbname-tablename'),10);
# DO STUFF HERE . . .
UPDATE dbname.tablename . . . WHERE id = @id;
RELEASE GET_LOCK(CONCAT(@id,'-dbname-tablename'));

But if something happens, and the RELEASE_LOCK doesn't run (and the session remains open), that row will, in essence, be locked from any further updates.
Hence the reason I presumed the timeout was there?


texts: Should I always make an attribute id that's an primary key?

texts:
I was thinking, sometimes several columns in a table uniquely specify the whole row.
In that case, is there a need to create a special column for a primary key?
Is that good for something?


texts: SSAS 2016 tabular : Discrepancy on countrows between measures visual studio vs Excel

texts:
Is anyone aware of a SSAS (tabular) 2016 in which the number of records in a table are not fully shown in Excel?

Say that when working in Visual Studio, a table has a rowcount of 740,000 records.
We process and deploy that table, but in Excel it shows only 12,000 records.
also funny enough, when we deploy the model in VS it says that it only processed 12,000 records (yet the rowcount in the model is clearly 740,000 records).

(we ensure that Excel is connected to the deployed Tabular Model and not the user's workspace copy in Visual Studio)

We then change the table properties in VS to apply a filter, then we remove the filter, to see if this somehow "resets" this strange behaviour. It seems to do it.
However the problem seems to arise again during the next data load on the underlying database.

This only seems to happen with ONE table (all other tables are fine). So we tried recreating the table, but the same problem persists. Any idea of what might be causing this issue?


texts:
I'm redesigning my client's website in order to add about 16 mini sites for their web site. It's a website using PostgreSQL. And my question is:

Performance wise, what is the better database design:

1 (one) database, with about 190 tables, or
17 databases, one with 30 tables, and the rest with about 10 tables each.

It's for a low to middle traffic web site, but 4 or 5 times a year the traffic will be high.

It's a Centos 6 VPS, with 3 GB of RAM. Other softwares are Apache and PHP.


texts:
So i have a Ad group on a database which i want to deny from viewing a table but i want give access to one person from AD Account.

I'm not sure if it is possible.
I highly think its not.
but maybe i could be wrong so i am asking. Is it possible for that to happen without removing the username from the ad account?could you provide me with the best solution on how to approach this ?
note i have already created the roles.


texts:
Can anyone please guide me what's wrong in this SQL Server query:
SELECT
    A.*,
    (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) Name
FROM A;

It seems that A.Name is inaccessible in the query.
I want to populate a column in select query with data from another table. I can't use INNER JOIN since there isn't any FK relationship between tables.
Actually I want to insert data from table A into another table C, but for insert I need to populate a NOT NULL Column in table C, that I am trying to populate with this query (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) as column.
Any help would be really appreciated.


texts: How to add comment to a column in a table in Postgres?

texts:
I'm new to Postgres. I would like add a comment to my column mod in a table called app-user-bookings

I have tried this sql code:

alter table app_user_bookings
modify column mod
int default 1
comment "1# mobile booking,    2# admin booking,    3# web booking, 4# tell call";

But which didn't help me, can anyone help me?


texts:
I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression :

=CountRows("DataSet1")
But I have the following error message:

"The expression used for the calculated field '=CountRows' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions." What should I do ? thanks


texts: Is it possible to use cross database queries in both Azure and SQL Server 2016?

texts:
I'm working on a project that is designed to run on Microsoft's Azure platform. At present we have a few developers working locally on their own machines with the intent of being able to publish the solution to Azure when we go live. We have been using dynamic stored procedures (sprocs that are essentially quoted strings with the database name replaced) so that we can make cross-database calls, however it turns out that these don't work on Azure.

One solution seems to be to use elastic queries, using the SQL methodology below:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'encryptionpassword';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = 'username',
SECRET = 'password';

CREATE EXTERNAL DATA SOURCE mydb WITH
    (TYPE = RDBMS,
    LOCATION = 'path-to-database.database.windows.net',
    DATABASE_NAME = 'dbname',
    CREDENTIAL = SQL_Credential
) ;

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = mydb)

select * from dbo.tc_City

This solution seems the most elegant, as it removes the need for dynamic stored procedures, which can be horrible to debug and modify. There is one major caveat: it doesn't seem possible to use the elastic query methodology on our local SQL server 2016 installs without adding a third party database engine. We upgraded from 2012 recently because we thought it would be possible to use the same technique, but it turns out that we have to install something called PolyBase to do elastic queries, and that is only supported with external databases of different types, such as Hadoop, rather than being capable of referencing another database within the SQL 2016 engine.

PolyBase methodology:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = 'user',
SECRET = 'pass';

CREATE EXTERNAL DATA SOURCE Central WITH
    (TYPE = HADOOP,
    LOCATION = 'localhost',
    CREDENTIAL = SQL_Credential
);

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = Central)

select * from dbo.tc_City

As you can see, the code for accessing an external database via PolyBase ends up being mostly the same, with only the data source setting being different - which would mean we could essentially use one core set of SQL scripts/stored procedures and just run the relevant changes when publishing to Azure. Having to install Hadoop servers on our local machines just so that we can access another database within the same server without having to rewrite masses of SQL seems somewhat excessive and unnecessarily complicated.

Is there any way we can use the same methodology to access another database running on the same SQL server 2016 engine without having to write different SQL for the stored procedures running locally and those running in Azure? e.g. we don't want to have to write the "create external table ..." on Azure and reference the data in that table via dbo.external_tablename, whilst having to locally refer to databasename.dbo.external_tablename - as that would mean writing two different sets of stored procedures.

Can the external data source be referenced by file instead, if that allows all of the subsequent SQL to be the same?


texts: Force Sql server to use index seek in merge join

texts:
I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN:

ID in Personal Table is a Primary key
ID in Abteilung Table is a primary key
There is no other Indexes in these tables

 select * from  [dbo].[Personal] as P inner join [dbo].[Abteilung] as A
 on  P.[ID]= A.[Personal_ID]   OPTION (MERGE JOIN)

then Optimizer uses this plan to run my query:

I would like to force optimizer to use index seek instead of Table Scan

What I did to achieve this aim:

Defining a grouped index in the Table Abteilung on columns ID, Personal_ID. If I run the query, the execution plan is changes like this:

here I have Clustered index scan, but not clustered index seek

Using FORCESEEK hint. If I am using this hint, an error occures by running the query:

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any
  hints and without using SET FORCEPLAN.

If I add a where clause in the query, then the query would be change to:

  select * from  [dbo].[Personal] as P inner join [dbo].[Abteilung] as A
  on P.[ID]= A.[Personal_ID] where A.[Personal_ID]=2
  OPTION (MERGE    JOIN)

in this situation I have only Clustered index scan.

My question:

How do I change my query to have Clustered Index Seek in the bottom line?


texts: Why is my auto-restore procedure not dropping the databases when I expect it to?

texts:
I wrote a stored procedure to loop through my databases in a cursor, restore the most recent backup, run checkdb, drop the restored database, then move on to the next one. I have found that during the process, there are several databases restored at any given time, which uses too much disk space for this to work on my larger databases. When the procedure completes, all the databases are dropped and it does not leave anything behind. I have checked and double checked that I have steps inside the cursor to drop the database, but it does not help.

I have this code inside the cursor:

SET @DropCommand = 'IF DB_ID(''' + @CursorRestoreName + ''') IS NOT NULL
BEGIN
    ALTER DATABASE [' + @CursorRestoreName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [' + @CursorRestoreName + '];
END';

EXEC sp_executesql @DropCommand

I added a PRINT @DropCommand statement as well to verify this code block is being executed, and it is.

Since that wasn't working, I added another cursor inside my first cursor to go through the full list of databases to attempt the drop command, to clean up previous restores. This still has the same effect: multiple database restores exist on the server at a given time during the process, then at the end all have been dropped.

Do you have any recommendations that I could implement so that the restored database is actually dropped before moving on to the next restore? Or perhaps suggestions for troubleshooting this?


texts: What are the alternatives for an ORDER BY clause in a View?

texts:
This question just had to be in this site :)

ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.

I know that there are ways of bypassing this limitation, for example TOP 99.999999 PERCENT, but I would like to know what is the best practice, not how to hack it.

So, if I want to create views in my database for personal use, meaning I want to connect to the database and just see the data fixed and sorted, how should I do it if I can't order a view?

Currently in my SQL Server DB I have views with the TOP hack, and I'm using them a lot, but it feels wrong.


texts:
I'm trying to find the difference in terms of modeling a ternary relationship v/s a binary relationship in the following case. Any piece of advise would help me a lot.

Company has editors who are responsible for 1 or more papers.
An Author works with an editor on each paper, furthermore, he may be working in another paper with another editor.
Each Author may have none or more papers.

I managed to model this problem in two ways

First using a Ternary Relationship.

Second one is using two binary relationships and enforcing that each paper has 1 editor.

Is there going to be a difference in the tables that will follow any of this ways? I've tried to find any but failed to do so.


texts: Central stored procedure to execute in calling database context

texts:
I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on one of my databases, it does what I want it to do and pulls down a listing of all records regarding any database. When I place it on a different database it pulls down a listing of all records relating to only that DB.

For example (code at bottom):

Query run against Database 6 shows [requested] information for databases 1-10.
Query run against Database 3 shows [requested] information for only database 3.

The reason I want this procedure specifically on database three is because I'd prefer to keep all maintenance objects within the same database. I'd like to have this job sit in the maintenance database and work as if it were in that application database.

Code:

ALTER PROCEDURE [dbo].[GetFragStats]
    @databaseName   NVARCHAR(64) = NULL
    ,@tableName     NVARCHAR(64) = NULL
    ,@indexID       INT          = NULL
    ,@partNumber    INT          = NULL
    ,@Mode          NVARCHAR(64) = 'DETAILED'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @databaseID INT, @tableID INT

    IF @databaseName IS NOT NULL
        AND @databaseName NOT IN ('tempdb','ReportServerTempDB')
    BEGIN
        SET @databaseID = DB_ID(@databaseName)
    END

    IF @tableName IS NOT NULL
    BEGIN
        SET @tableID = OBJECT_ID(@tableName)
    END

    SELECT D.name AS DatabaseName,
      T.name AS TableName,
      I.name AS IndexName,
      S.index_id AS IndexID,
      S.avg_fragmentation_in_percent AS PercentFragment,
      S.fragment_count AS TotalFrags,
      S.avg_fragment_size_in_pages AS PagesPerFrag,
      S.page_count AS NumPages,
      S.index_type_desc AS IndexType
    FROM sys.dm_db_index_physical_stats(@databaseID, @tableID,
           @indexID, @partNumber, @Mode) AS S
    JOIN
       sys.databases AS D ON S.database_id = D.database_id
    JOIN
       sys.tables AS T ON S.object_id = T.object_id
    JOIN
       sys.indexes AS I ON S.object_id = I.object_id
                        AND S.index_id = I.index_id
    WHERE
        S.avg_fragmentation_in_percent > 10
    ORDER BY
        DatabaseName, TableName, IndexName, PercentFragment DESC
END
GO


texts: Connect to PostgreSQL via a ssh tunnel using Cygwin

texts:
I would like to connect to a Postgres server installed on a remote server using an ssh tunnel.
I have Windows so I use cygwin for SSH.

I do the following on cygwin:

# Create the tunnel
ssh -f user@remote_server -p ssh_port -L 5433:127.0.0.1:5432 -N

On pgAdmin, I set the host to localhost and the port to 5433 as well as the username/password/db.

When I try to connect, it is running, and then it displays an error message:

Error connecting to the server: server closed the connection unexpectedly.

On cygwin, I see this message:

Write failed: Broken pipe

The ssh process has actually been killed so I guess it crashed? But why?

How could I get more information to know what the problem is and especially where (pgadmin or ssh tunnel on cygwin...) ?

Thanks


texts:
I installed MySQL cluster and every thing is working well, however I don't know how to implement horizontal sharding in my database.
Detail:

I have 2 data node: Node1 and Node2.
I created database Test with one table T1(key(int), field1..).

I want to write 50% of the records of T1 into Node1 (odd value of key), and 50% of the records into Node2 (even value of key).
I was looking through the documentation but have not found the answer to my problem.


texts: Does replication with SSL encryption work on MySQL 5.5?

texts:
I am trying to set up replication with slave on Amazon EC2 and master in our local environment. The setup went fine without SSL encryption. But when I set up the replication to be SSL encrypted, it is failing to connect to Master.

MySQL version on Master : Server version: 5.5.19-log MySQL Community Server (GPL)
MySQL version on Slave  : Server version: 5.5.14-log MySQL Community Server (GPL)

I have SSL encrypted replication set up and going fine where the Master's MySQL version is Server version: 5.1.56-log Source distribution. But it is failing for the other Master machine which has MySQL 5.5.

So my question is, does SSL encrypted replication work for MySQL 5.5?

Anyone here faced the same problem? Please throw some light on how to get around this problem. Note that I have changed the permissions for my SSL certificates used in Slave machine to be mysql instead of root (Both failed anyway)

Thanks.


texts: Archivelog file deleted, resulting in 'ORA-01113: file 1 needs media recovery'

texts:
(This is not a production database. I'm practicing in a staging enviroment.)

I'm trying to set up RMAN for backup/recovery.

I did a full backup, which seems to be in a good state.

I then did an archivelog backup, which went well. And then, (here's where I f***ed up), I...

Created a new tmp directory.
Ran an RMAN archivelog only backup with the tmp directory as destination
Changed my mind and deleted that tmp directory.

I now realize that RMAN keeps track of paths to previously written backups, so I can't just go around and delete these files!

Now, after I do

RUN {
    RESTORE DATABASE;
    RECOVER DATABASE;
}

I get

ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/19/2018 20:21:01
RMAN-20506: no backup of archived log found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 1361136 found to restore

And when I try to open the database, it looks like follows:

2018-01-19 20:23:04 SYS@... AS SYSDBA> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/path/to/a/file.dbf'

I've tried the solution posted here: ORA-01113: file # needs media recovery ORA-01110

But I can't get that to work. (I run into ORA-00308: cannot open archived log.)

Update: I tried CROSSCHECK BACKUP; followed by DELETE EXPIRED BACKUP; but this unfortunately results in this.


texts: Update non key ID column for multiple rows at once

texts:
I've inherited of a poorly designed database running on Webdev18.
Most of the tables are not related at the database level but rather in the code.
I ought to insert and update new values in some of the tables, however, due to poor design the table I'm working on doesn't have an explicit primary key, but use the id column as such with no uniqueness being implemented.
--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
---------------

The id is populated via the code for each insert and is not auto-incremented. The function evaluate the current Max() and increment it by 1.
The default value is O.
Each year, we're supposed to register the patients including those of previous year at are still present.Up to now this was done through the application by users this being cumbersome and adding no added value.
So a new record of the same patient is created for each year, thus resulting in duplicate id.
--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
0 |Carine|2017
0 |Prince|2017
---------------

I'm to update the new rows id with new values so as to a result as below:
--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
5 |Carine|2017
6 |Prince|2017
---------------

However, I've got the following constraints in doing so:
Constraints
Approaches that can't be used:

No code update,re-writing, and triggers to be added to the table.
No looping construct allowed in the SQL Query command interface.
No variable declaration in the SQL Query command interface.

RDBMS
WebDev 18 - HyperfileSQL
Therefore is it possible to achieve this result within the constraints mentioned above with simply using an
INSERT INTO ... SELECT FROM... .
Sql Fiddle


texts: UPDATE a column by COUNT of another table

texts:
It might be a naive question, but what is the difference of these two queries and which is preferred?

UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = B.idcount WHERE table1.id=B.id

and

UPDATE table1 A INNER JOIN
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) B USING (id)
SET A.Freq = B.idcount


texts:
I've been doing some experimentation/testing with VACUUM ANALYSE and table sizing.

I've been using the queries here to get a figure for table size, and I've been using this to get approximate rowcount....

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'TABLE_A'

I have one table with 22 thousand rows sized at 700 Mbytes or approximately 32k per row. The table consists of 4 timestamps, 4 integers, 2 booleans and one 6 character field as a unique primary key.

I have a 2nd table with a very similar structure, albeit only 18 thousand rows, but that occupies 6Mbytes or 333 bytes per row.

Why the enormous discrepancy? 32Kbytes per record for the 1st one, and 333bytes per row for the 2nd one!

The tests were run immediately after doing a VACUUM ANALYSE on each table and no other processes were accessing the database at the time. The only other factor that may be important is that the first table is continuously being updated, whereas the 2nd is largely static, however I would expect that after shutting down all other sessions and running a VACUUM ANALYSE, the way the tables are used wouldn't make any difference.


texts: Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?

texts: If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000 and the like typically also be much faster (of course when they are not cached/buffered etc)?


texts: How to set simple parameterization for a json_value query (without using plan guides)?

texts:
I'm trying to run this query, but it fails it fails with The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.

select  mt.pk

from    My_Table mt

where   json_value(mt.json_data, '$.group_id') != ''

As you can see, my parameter 2 is a string literal. The error is due a bug in SQL Server 2016, when forced parameterization is on for the database.

I have confirmed that forced parameterization is turned on in my database, using:

select name, is_parameterization_forced from sys.databases

I am generally aware that you can force simple parameterization by creating a plan guide, then using it for the query. However, reviewing this documentation, I think that may be too complicated for my actual situation. This is partly because in reality, these queries are be generated on the fly and it's not realistic to also generate plan guides for all of them.

Reviewing Query Hint docs, it says

  The PARAMETERIZATION query hint can only be specified inside a plan guide. It cannot be specified directly within a query.

Upgrading to SQL Server 2017 is also not currently viable. Turning off forced parameterization for these databases is also not viable.

I am thinking that if I can set simple paramerization on for the query or session, that would be a workaround. Is such a technique possible? Is there any other workaround?

(PS: I realize that looking for solutions with simple parameterization is a little bit X/Y... any help is appreciated)


texts: Postgres role does not inherit permissions from its member role

texts:
I want to group my postgres database permissions to set of roles and then assign users to these parent roles to manage all permissions from a central place. I created a role called admins and granted permission as below (had to use EXECUTE command since my statement is dynamic. role_name is 'admins' and schem is 'core').
    EXECUTE format('CREATE ROLE %I WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION', role_name);

    EXECUTE format('GRANT ALL ON ALL SEQUENCES IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO %I WITH GRANT OPTION', schem, role_name);

    EXECUTE format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %I WITH GRANT OPTION', schem, role_name);

    EXECUTE format('GRANT ALL ON ALL FUNCTIONS IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO %I WITH GRANT OPTION', schem, role_name);

After creating the role, I created a user call 'kasun' and assign to the above created 'admin' role.
CREATE USER kasun WITH PASSWORD 'password' INHERIT;
GRANT admins to kasun

I could login to the database from user 'kasun', but still I can't run even a select statement. (this schema and tables are already there created by application using a high privileged user)
ERROR:  permission denied for schema core
LINE 1: select * from core.partners;

When I check the user settings it shows user 'kasun' has been created correctly and it is a member of role 'admins'. When I check the permission using following two queries, I could see 'admins' role has all the permission I granted, but role 'kasun' doesn't have any record.
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'admins';
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'kasun';

I can't think of anything I did wrong. Next option is to grant the permissions to each individual user. I don't like to go on that path when there is a clean way of doing this. I hope someone could shed some light to see what is going on here.
(I am running PostgreSQL 11.6 on Amazon RDS)
Thanks in advance!
Kasun


texts: enum vs boolean in case of multiple yes/no fields

texts:
I want to store information from a form where there are multiple yes/No question. For those how should I create table fields? I have experienced mysql doesn't support multiple columns to have same ENUM values. So I can't assign 'yes','no' for more than one column.

If I use boolean? how should I do it? Is it mandatory to have default values for boolean type field?


texts: Stacked instance can't see certificates on main instance, but I can't add them to stacked instance, help?

texts: I have a named instance of SQL Server 2016 stacked on the same server as an existing instance.  The stacked instance can't see the certificates the main instance has, but when I try to add the certificates to the stacked instance, it won't let me because it says they already exist.  Can anyone help?


texts: How can I create a view with self referencing table and a cyclic condition?

texts:
I have a self-referencing table.

ID BIGINT NOT NULL PRIMAY KEY,
DELETED TIMESTAMP NULL,
PARENT_ID FK

How can I create a view of this table which each row's DELETED is NULL and any of its parent's DELETED is also NULL?

So far, I tried and it doesn't work as expected.

CREATE VIEW `OPERATOR_` AS
SELECT c.* from OPERATOR AS c LEFT OUTER JOIN OPERATOR AS p ON c.PARENT_ID = p.ID
WHERE c.DELETED_ IS NULL AND p.DELETED_ IS NULL;

example 1

ID    DELETED   PARENT_ID
0     2017...   NULL         NOT SELECTED
1     NULL      0            NOT SELECTED
2     NULL      1            NOT SELECTED

example 2

ID    DELETED   PARENT_ID
0     NULL      NULL             SELECTED
1     2017...   0            NOT SELECTED
2     NULL      1            NOT SELECTED

example 3

ID    DELETED   PARENT_ID
0     NULL      NULL             SELECTED
1     NULL      0                SELECTED
2     2017...   1            NOT SELECTED


texts: Hosting ASP.Net session database on same same instance as the user database?

texts:
We have a web farm using SQL Server sessions.  We are seeing occasional timeouts connecting to the session DB even though overall utilization is low.  The ASPState session database is on the same instance as the application database.  I suspect this is not a good idea because the user database queries are closely correlated with the session database queries.  What do you think?

If this is a bad idea, would it help to move it to another SQL Server instance on the same machine? Or is it best practice to run the session DB on another machine?


texts: Manually specifying candidate masters in Maxscale

texts:
Looking over the documentation, I haven't quite been able to find if its possible to manually set the candidate master status of a server in Maxscale.  The reason I am looking for this is because some of the slaves we have hooked up the the master/slave server architecture have inferior hardware to the others, and we don't want them to become the master.  Looking at the documentation, it is possible to set the status of a sever as master or slave, but not specify that we don't want it to become a master.

Is there a setting to do this in Maxscale?  Possibly in maxadmin or a .cnf file or something?

Thanks.


texts: SQL Server injection - how much damage in 26 chars?

texts:
I'm testing for resilience against injection attacks on an SQL Server database.

All table names in the db are lower case, and the collation is case-sensitive, Latin1_General_CS_AS.

The string I can send in is forced to uppercase, and can be a maximum of 26 characters in length. So I can't send in a DROP TABLE because the table name would be in uppercase and thus the statement would fail due to the collation.

So - what's the maximum damage I could do in 26 characters?

EDIT

I know all about parameterised queries and so forth - let's imagine that the person who developed the front end that builds the query to send in didn't use params in this case.

I'm also not trying to do anything nefarious, this is a system built by somebody else in the same organisation.


texts: Query 'optimizing' state is taking too long

texts:
I have a MySQL 5.6 on Amazon RDS that I'm using for testing some data archiving scripts. I'm removing oldest data based on a "updated_date" column and index. Curiously, after removing a few million rows, my script gets stuck on the initial query it does for determining data bounds.

I run a query like this:

SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

The explain command on this query shows:

'1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'

So, it's supposed to hit the index and run almost instantly, and it did when the testing started, but now, after millions of rows were removed, it gets stuck in the 'optimizing' state for several minutes.

The script is the only thing running on the database.

Any ideas on what's wrong with it? Am I supposed to do something when removing lots of rows like that? Do I have to run optimize table, even though I'm not using delete quick?

Update #1

The result from show table status like 'order':

Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment
order,InnoDB,10,Compact,568037197,280,159252496384,0,180806041600,37692112896,4052226884,"2015-01-26 17:27:20",NULL,NULL,utf8_general_ci,NULL,,

The result from select count(*) from order is 618376777 rows.

Unfortunately, I can't post the whole schema here, but where it bears on the issue, the result from show create table order is:

CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  // 31 data columns here
  `updated_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_order_updated_date` (`updated_date`),
  // 9 indexes here
) ENGINE=InnoDB AUTO_INCREMENT=4052226884 DEFAULT CHARSET=utf8;

Update #2

By separating the min() and max() calls in two queries, I noticed only the min() query is affected. The max() returns almost immediately, so it looks like the min() is traversing the index for all index entries that existed but are now empty. Is there any way to prevent that from happening other than rebuilding the index?

Update #3

RickJames nailed the problem with the hint about change buffering, but disabling it entirely compromises performance for all inserts, deletes and updates. Eventually, I figured out the time it took to flush the changing buffer was reasonable with the production server, so problem solved for me, but if you run into the same issue with a low-end server with magnetic storage, good luck with it.


texts: How to estimate the storage needed for a database?

texts:
I ran across this questions trying to dive into a computer measurements worksheet online and for the life of me could not figure it out. This is the question:

Assume each record in a database requires 1Kib and there should be approximately 5 million records that need to be stored. Estimate the amount of storage needed in gibibytes for the database.

Any help would be extremely appreciated because I'm completely lost on this question even though it sounds like it should be simple. Thanks in advance.


texts: Strange MySQL replication error 1146 (Table doesn't exist)

texts:
I'm getting a very weird MySQL replication error No. 1146 for a REPLACE INTO query on a slave host replicating all tables in all databases from a master, and I'm having a bit of a hard time understanding why.

Here's my scenario:

New data is generated solely on the master server, MySQL 5.5.40.
Slave A, MySQL 5.5.38, has been replicating all tables in all databases from this master just fine for a long while, producing no errors of any kind.
IO_THREAD was paused on slave A. Value of Relay_Master_Log_File was confirmed to match that of Master_Log_File and value of Exec_Master_Log_Pos was confirmed to match that of Read_Master_Log_Pos.
A FLUSH TABLES WITH READ LOCK was issued on slave A and a dump of all db's was subsequently produced from it with mysqldump -v -h localhost -u root -p --all-databases --opt --single-transaction --hex-blob --no-autocommit > dump.sql. Lock was released only after the dump completed, and then the slave IO_THREAD was restarted; replication from master resumed without any problems, and continues to run smoothly to this day on slave A.
The dump was transferred to slave B, MySQL 5.5.34, and loaded successfully into it through a simple mysql -h localhost -u root -p < dump.sql command, after confirming non of the target databases existed on this second host (and actually no db's other than the mysql db and information & performance schemas). I can also confirm integrity of the dump file after the transfer to slave B thanks to matching RMD160 checksums for it on both hosts.
Slave B was pointed to the master server for replication, with MASTER_LOG_FILE and MASTER_LOG_POS coordinates set to the values of Relay_Master_Log_File and Exec_Master_Log_Pos recorded in step 3 above from slave A, respectively.
Replication was started on slave B, and data started flowing in just fine.

However, after about a day of smooth operation, slave B produced the following error in its SQL_THREAD:

Error 'Table 'knet.course_location_tracks' doesn't exist' on query. Default database: 'knet'. Query: 'REPLACE INTO `course_location_tracks` (`userid`,`courseid`,`lesson_location`,`datestamp`) VALUES (val1,val2,val3,val4)'

(actual row values have been redacted out)

I can't make much sense of this error because I can confirm not only that the knet.course_location_tracks table exists on slave B, but also that its definition is identical to that of the corresponding table on slave A. Slave A, as I pointed out above, continues to replicate from master just fine to this day, without any problems of any kind.

If replication started fine on slave B, leading me to believe that initial replication coordinates were calculated correctly for it off of the state of slave A at the point of the dump, why am I then getting this error for a table that does exists on the host? Moreover, why am I getting the error on slave B while slave A is still replicating smoothly?

Other than having mismatching MySQL versions across my three hosts (something I only noticed recently in trying to debug the problem, and that I will see to correct ASAP), what could I be doing wrong?

And, finally, once the problem is determined and corrected, how could I get slave B to resume replication at the correct point so that it is again fully in sync with master?

Thanks in advance for any help!

PS: If it matters, replication on slave A was initially set up by transferring all db's & tables in master to it in a similar fashion, i.e. by first flushing & blocking all tables on master with the read lock, dumping the data with mysqldump (same flags), and finally loading them into the slave with the same mysql command-line client call.


texts: How to get the last inserted id using scope identity without using a stored procedure in Mysql?

texts:
My project contains  2 tables one is profile_table and another is address_table

Profile_ table contains:

+------+---------+----------+
| sid  | sname   | saddress |
+------+---------+----------+
|  1   | xyz     |   india  |
|  2   | abc     |     U.S  |
+------+---------+----------+

‘sid’ field in profile_table is made as primary key and autoincremented field.

The address table also  contains 'sid':

+------+---------+----------+-------
| sid  | squalification    | smarks |
+------+---------+----------+-------
|  1   |    B.E            | 80     |
|  2   |    M.S            |  85    |
+------+------------------ +--------+

The sid column in 'address_table' is made as foreign key in the address table and when I inserted the first record in the profile_table that sid value must be inserted in the   address_table along with squalification,smarks values.I want to use 'last inserted id' concept here and i dont want to use the stored procedure. please explain me how to get the last inserted id using scope identity without using a stored procedure in Mysql.


texts: Could Stack Overflow run on SQL Server Web Edition?

texts:
I am starting a new web venture that may need to scale to a high number of users.

I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in upgrading to Standard, Enterprise or DataCenter (pretty sure it won't be this one).

I know one shouldn't think about scaling before he needs to, but this will affect the architecture of the site and the business plan.

I know the processor limit is per physical processor, not thread, so that doesn't worry me. However some of the mirroring and backup features would worry me. Does SO rely on these features?

tl;dr:
Could a site like Stack Overflow run on SQL Server Web? What aspects of maintenance and high availability would be impossible to achieve?


texts: How to insert values into Bridge Table in star schema?

texts:
I am modelling my database with respect to star schema with bridge tables.
My master Table looks like below. I need to convert this format to star schema.

MasterTable

StudentNumber | Subject1 | Subject 2 | Subject 3

1000   |  Biology | Mathematics | Physics
2000   |  Science | Mathematics | NULL
3000   |  Biology | Mathematics |NULL

My db design has 3 more tables now.

Fact Table
Bridge Table
Dimension Table

The structure and Data of these tables are like below

FactTable - Students

StudentNumber | SubjectGroupID

1000   |  1
2000   |  2
3000   |  3

BridgeTable - SubjectGroups

SubjectGroupID  | SubjectID

1  | 100
1  | 200
1  | 300
2  | 400
2  | 200
2  | 100
3  | 100
3  | 200

Dimension Table - Subjects

SubjectID  | Subject

100 | Biology
200 | Mathematics
300 | Physics
400 | Science

  Inserting values into the Dimension Table

insert into Subjects(Subject) (
select distinct Subject1 from MasterTable
UNION
select distinct Subject2 from MasterTable
UNION
select distinct Subject3 from MasterTable)

Now, How to insert values into the Bridge table? Do we need to find all combinations of the 'Subjects' and form a group?.

How to do find possible combinations of the dimension table? IS there any other way to insert values into the bridge table?
Please temme how to insert values into the fact table as well in Microsoft SQL Server.
Thanks in anticipation.


texts: SQL Server 2012 Powershell script to generate database with schema and data

texts:
Using Sql Server 2012, could somebody be so kind and show me a simple powershell 3 script that generates my entire database (schema and data)?

I tried this one:

$Filepath='d:\b\t' # local directory to save build-scripts to
$DataSource='HH' # server name and instance
$Database='HMovies'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$transfer.Options.ScriptBatchTerminator = $true # this only goes to the file
$transfer.Options.ToFileOnly = $true # this only goes to the file
$transfer.Options.ScriptData = $true;
$transfer.Options.Filename = "$($FilePath)\$($Database)_Build.sql";
$transfer.ScriptTransfer()
"All done"

But I get an error

Exception calling "ScriptTransfer" with "0" argument(s): "This method does not support scripting data."

If I disable the ScriptData option it works but then I get the schema only of course.
I have googled for hours but I just can't find a simple script (no bells and whistles for stored procedures, etc) that performs this simple task.

My time is running out. Please help.


texts: how to decrypt one encrypted column with lost password

texts: one column in sql server 2008 database is encrypted with symmetric key, master key password not found, how  to decrypt this column? I create a new database with different name in the same server and transfre that table by generating script. When I tried o run query, it said symmetric key and certificate not found. Error 3102 and 3013


texts: How does direct path load work when dataguard is enabled?

texts:
There's a few posts about direct path and dataguard but I just want to understand exactly how it actually works.

How maybe I made an incorrect assumption, but if direct path means it writes to the data files  (as per the docs) then doesnt that mean no redo is generated?

And if no redo is generated then how does dataguard work?  ( which is soley based upon transferring redo to the standby machine right? )


texts: Online Index Rebuild shows self blocking

texts:
I've been doing some testing on rebuilding some larger clustered indexes (with online + sort_in_tempdb on) and I'm observing something that I can't immediately explain.  I have a hunch that this is some sort of intra-query blocking due to the rebuild going parallel but can't 100% confirm.  Running SQL 2016 EE - SP2 + CU8 (13.0.5426)

The query I'm running:

USE [UserDB]
GO
ALTER INDEX [PK_Order] ON [dbo].[Order] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)
GO

Whoisactive says:

Digging further into that session and it's ecids, I see the following:

Now, with sort in tempdb (dbid 2) being on, my concern here is that the resource_description column is showing page locks on dbid5 (the DB that I'm rebuilding the index on).

This looks like a page lock on the user database that may cause blocking or disruptions.  Can anyone shine some light on what exactly is happening here? Is this just unbalanced parallelism and threads have to sit on their hands until the others finish?

Worth noting that these locks do clear after a while (5-10 mins) and we revert back to just CXCONSUMER waits.


texts:
Please understand this is not my field. I have inherited an access database from a previous employee.  I have a database which logs training so I have 1 table with employees names trade etc.  I then have a table with all the training each person has had.  What I need to do is search to find the names of employees who have not had a specific type of training logged in the second table.  I would like to enter the training description and then it return a list of employee names who have not had this training.

Can anyone help please
Thanks
Regards
Derek


texts: Slow performance matching columns to lists of values

texts:
I've made a stored procedure to get all results which match specific students and objectives.

CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY
AS
SELECT Id, UserId, ObjectiveId, DateUploaded, TrafficLight
FROM [Results]
WHERE
  [UserId] IN (SELECT [Id] FROM @students)
  AND [ObjectiveId] IN (SELECT [Id] FROM @objectives)
  AND [TrafficLight] IS NOT NULL
ORDER BY [UserId] ASC, [ObjectiveId] ASC, [DateUploaded] DESC

It uses a user-defined table type for passing in arrays of students and objectives:

CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] [int] NULL
)

Typically @students contains ~30 IDs and @objectives contains ~100 IDs. The Results table has about 500,000 rows and a nonclustered index on UserId, ObjectiveId, TrafficLight.

At the moment it's taking a long time (up to 5 seconds) to search 500,000 rows. I'm new to stored procedures and think I must be doing something wrong. How can the performance be improved?


texts: Should you use the schema name in WHERE, ON, and other clauses?

texts:
I know it is good practice, and a performance boost, in SQL Server to prefix the table name with the schema (dbo.TableName) in the FROM clause of a SELECT statement but should you also use it on other clauses like the WHERE or ON? Should you also specify the TABLE name for columns in the where clause if the column name is unique?

Take two scenarios:

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

and

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

In the first scenario should I add dbo.Table to each column name?

IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?


texts: How to select rows that have multiple relations in common

texts:
I apologize if this is a duplicate, I tried to search for an answer, but possibly do not have the right keywords to help my search.

Fiddle: http://sqlfiddle.com/#!9/ea5723/6

I have the 3 tables:

places

+----+---------+
| id | name    |
+----+---------+
| 1  | Place A |
| 2  | Place B |
| 3  | Place C |
| 4  | Place D |
| 5  | Place E |
+----+---------+

tags

+----+-------+
| id | slug  |
+----+-------+
| 1  | tag-z |
| 2  | tag-y |
| 3  | tag-x |
| 4  | tag-w |
| 5  | tag-v |
+----+-------+

place_taxonomy

+----------+--------+
| place_id | tag_id |
+----------+--------+
| 1        | 1      |
| 1        | 3      |
| 1        | 5      |
| 2        | 3      |
| 3        | 1      |
| 3        | 2      |
| 3        | 3      |
| 3        | 4      |
| 3        | 5      |
| 4        | 2      |
| 4        | 4      |
+----------+--------+

I have been able to query what places have a single tag with

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  LEFT JOIN place_taxonomy
    ON places.id=place_taxonomy.place_id
  LEFT JOIN tags
    ON place_taxonomy.tag_id=tags.id
WHERE
  tags.id=1
GROUP BY
  p_id;

And I can select which places have either of multiple tags with:

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  LEFT JOIN place_taxonomy
    ON places.id=place_taxonomy.place_id
  LEFT JOIN tags
    ON place_taxonomy.tag_id=tags.id
WHERE
  tags.id=1 OR
  tags.id=2
GROUP BY
  p_id;

But, the trouble I am having is how to select places that have a combination of tags such as tag-z and tag-y

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  LEFT JOIN place_taxonomy
    ON places.id=place_taxonomy.place_id
  LEFT JOIN tags
    ON place_taxonomy.tag_id=tags.id
WHERE
  tags.id=1 AND
  tags.id=2
GROUP BY
  p_id;
#Returns No Results!


texts: What is an equivalent MySQL function to match CRYPT of PostgreSQL?

texts:
In PostgreSQL there is a function that can be used to encrypt passwords, like so:
crypt('123456', gen_salt('bf'))

What would be an equivalent function for MySQL?
Background
I am amending code that used just a bit of PostgreSQL.  I am used to MySQL and want to continue development with MySQL in mind. My larger goal is to encrypt passwords with MySQL, so a different method will work for me as well.


texts: Truncation error creating clustered index on schema-bound view

texts:
I was just creating a schema-bound view that I wanted to put an index on (to try out some computed column variations).

I created the view WITH SCHEMABINDING, then I had to create a clustered index before I could create my other non-clustered ones.

The underlying table has an INT primary key column, so I was going to make the view's clustered index based on that.  So I ran

Create UNIQUE CLUSTERED INDEX [cix_viewEvents_EventID] ON [dbo].[viewEvents] (EventID)

Much to my surprise, after 4-5 minutes I got a

Msg 8152, string or binary data would be truncated

error.

The only column in the index is an int; what could be getting truncated?

I couldn't find anything relevant on google.

EDIT:

The original table is like this,

CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EventTitle] [varchar](max) NOT NULL,
[EventContent] [varchar](max) NULL,
[EventDate] [datetime] NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

while the view I created looked like this

CREATE VIEW dbo.viewEvents
WITH SCHEMABINDING
AS
SELECT EventID, EventTitle,
EventContent, EventDate, HASHBYTES('SHA1', EventContent) as ContentHash, HASHBYTES('SHA1',
EventTitle) as TitleHash
from dbo.Events;

I was hoping to speed up comparisons on the title and content by having hashes to compare against instead of big blobs of text.

Rather than add the computed columns straight to the table, I thought I'd try the schema bound view first, but I ran into the "you need a clustered index on the view before you can create non-clustered indexes on it".  So I tried to create a clustered index, as noted above.

We're talking an int in both places, but creating the clustered index on the view  bombs with a truncation error, which doesn't seem to make sense.


texts:
Sub query with not equal

table_1

col1
SV0362
RU0177CSPG
NO0124CSPG
VN3582
AT9923CSPG
BZ0324
PE0309
AS4293EXPT
ML0331OMKT

table 2

colA
OO56128665
OO56128665
OO56128665
OO56128665
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
OO56128665
OO56128665

I want to compare col1 of table1 with colA of table 2 and return the values which are not qual

something like this

select * from table1  where col1 <> (select colA from table_2 a,table1 b where  b.col1=a.colA)

but this would give single row returns more than one subquery


texts:
I have a big database, about 250GB, and recently our system had to be transfered to a new machine.

The old machine:

Has a Windows Server 2008 R2 Standard Edition
Runs SQL Server 2012
32GBs of RAM, with 20GBs defined for SQL MAX RAM.
22 VCPUs

The new machine:

Windows Server 2012 R2 Standard
Runs SQL Server 2012
40GBs of RAM, with 24GBS defined for SQL MAX RAM
22 VCPUs

As you can see, the new server should be enought to run the system with the same performance that it ran in the old one, but it is slower. The same Procs takes 2 to 5 times more to run sometimes (it seems to vary).

Things that I tried to solve the problem:

Enable flag 1118 (wasn't enabled in the old one)
Turn on Instant File Initialization (also wasn't active in the old one)
Set the MAX RAM to 20GBs, as in the old one
Reindexing and checking integrity with the Ola Hallegren's SQL Server Maintenance Solution
Changing SQL Process priority

The Database was transfered by detaching on the old server and attaching on the new server.

Any more information that should be needed do identify my problem I'll be glad to provide.

And, to finish, I am not a DBA, I'm just a programmer in charge of a task that I shouldn't be.


texts: How to SELECT a column in a row and return another row if it doesn't exists?

texts:
I want to write a query with two SELECT Statements in an order that if the first SELECT Statement doesn't return anything then only the second select statement runs.

SELECT col1 FROM MyTable WHERE col2=5

and if this doesn't return anything(there is no col2=5) then:

SELECT col3 FROM myTable WHERE col4=10

How should it be done?


texts: T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

texts:
I am trying to write a function which will split a varbinary(max) field into a table with 16 byte GUIDs, but am struggling. I haven't been able to find any examples or walkthroughs on how to do this using varbinaries - so any help appreciated. An example of the data in the varbinary field I am trying to split is:

0x09B57E7B1E19CD448698ECDA170B080F7C98FD411E5D264EB6D83AD6D4067C0D336E73299D2408438B625B09BED408D2D8E6C6DE4310194ABD90CF90F1558838731B2B740B508E44843FA98125ADF599669FC433BD4C074294C64E13D880A3ED78D25157C1E7A946B5B9C78E8DA0B5A8ECA73BB5CE59E6439586C445F25F2CDCA2B69483A7AE184A922DBC895C2EEB47910B88E915B7B94FB0EFC84568E2C83F0EF3168F62B6CB4EBA849486164F6DA7805C85383FBF694DB606904E0BA1364DC2DECDC1D00DED4BA28D0F1AA3DD9D1E1AA98697B914924995A26512C7EBD7C1

Thanks


texts:
I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a MyISAM table in my database that stands central to my site. There are many reference tables created around it and many other tables that link directly to row ids from this table. The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints. I have about 15 indexes on it (the data is about 70 MiB, indexes are about 80 MiB). There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.
Now my question is; would be be beneficial to alter this table to a InnoDB table? I am working on extending my systems to include API traffic as well, meaning that the table will even the queried even more and I want to make sure that everything runs as smooth as possible. Because of this, I'm slightly leaning towards converting it to InnoDB. However, since the table requires many indexes, I'm thinking MyISAM might work more effectively. Can anyone help me make up my mind? Thanks in advance1
Edit: (added by jcolebrand from a comment)
I have added the indexes a long time ago and I changed some things to the PHP code base, so maybe some indexes are out-dated, but overall; yes, they are all used. I run many different types of queries on the table. Different query, different index. But my main question here does not concern the table read performance, but how it performs with many indexes. InnoDB indexes are 'leaved'/ stored with the data, instead of MyISAM's key-file principle. I am just worried that this might cause enough of a performance drop not to outweigh the table lock issue.


texts: How to find the MySQL query that got rolled back

texts:
I want to know if a certain transaction got rolled back. If we analyze

  SHOW ENGINE INNODB STATUS\G;

we get the list rolled back transactions with transactionid , but how to get the actual query from this

eg I have

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 89504501
Purge done for trx's n:o < 88503595 undo n:o < 0 state: running but idle
History list length 2341
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 531230733218221, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733221873, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733213665, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

I get some transcation id = 531230733218221, but what is the INSERT/UPDATE query corresponding to this.
Here is my mysql config:

Version: 5.7
Binary Log: ON
Slow Query Log: ON
General Query Log: OFF
Performance Schema: ON
Profiling: OFF
Transaction Isolation Level: REPEATABLE-READ

Is there any table in performance_schema that we can explore. Since MySQL do not have any mechanism to store the last updated timestamp at row/column level, though it exists at the table level.


texts: MySQL cannot create file (permission denied). even my folder permission is 1777 (Ubuntu)

texts:
hello I have some issue with my MySQL db. I've try so many ways to fix it, but it cannot solve my problem. I tried this Cannot output MySQL data to file. and still have same error.

this is my query and error result :

  mysql> select * from categories into outfile
  '/var/www/html/project_pfm/output/file2.txt';

  ERROR 1 (HY000): Can't create/write to file
  '/var/www/html/project_pfm/output/file2.txt' (Errcode: 13 - Permission
  denied)

and this is my file permission in OS :

  root@WebPFI01:/var/www/html/project_pfm# ls -al

  drwxrwxrwx  3 mysql mysql      4096 Apr  6 09:35 output

thank you for your help and answer.


texts: Deleting Record from Linked Table Slow in MS Access

texts:
I have a Microsoft Access database that is linked to a SQL Server 2000 backend. It has one form with several subforms, all using information from linked tables. When I try to delete a record from one of the subforms, Access sometimes hangs for 60 seconds before prompting me to confirm that I want to delete the record. After the 60 seconds, it proceeds normally without delays. I haven’t been able to find any pattern as to when it hangs; it seems to happen randomly about half the time. The only pattern is that the delay is always the same length.

The delay occurs somewhere between the Delete and Before Delete Confirm events. We do not have any macros or VBA code behind either event. There is a trigger on the table that records a timestamp of when a record was deleted and some other information about the values of the fields. When I delete a record, the timestamp matches when I originally hit delete (before confirming the delete in the dialog box 60 seconds later), so that suggests that transferring the data TO the server is fine, but perhaps there is a delay in either transferring information BACK to Access, or else some other delay on Access’s end after it sends the delete command. The problem still occurs when the trigger is disabled, so the trigger itself isn’t causing the delay.

I have seen similar questions about slow deletes for thousands of records, but this delay occurs when I am just trying to delete one record.

Any ideas about what's causing the delay or suggestions for how to track it down would be greatly appreciated!


texts:
SELECT
    contacts.email, emails_stats.id FROM contacts
LEFT JOIN
    emails_stats ON contacts.id = emails_stats.contact_id
WHERE
    contacts.client_code = 121212121212
GROUP BY
    contacts.email;

This query taken 10.2s

Table contacts:

Rows: ~74k; Indexes: primary and client_code

Table emails_stats

Rows: ~20k; Indexes: primary and contact_id

The query explain:

1   SIMPLE  contacts    ref client_code client_code 9   const   2104    Using temporary; Using filesort
1   SIMPLE  emails_stats    index   NULL    contact_id  36  NULL    19394   Using where; Using index; Using join buffer (Block Nested Loop)

SHOW CREATE TABLE contacts:

CREATE TABLE `contacts` (
  `id` char(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `client_code` bigint(20) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE emails_stats:

CREATE TABLE `emails_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_event_id` int(11) NOT NULL,
  `contact_id` char(36) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email_event_id` (`email_event_id`),
  KEY `contact_id` (`contact_id`),
) ENGINE=InnoDB AUTO_INCREMENT=19194 DEFAULT CHARSET=latin1

Expected Result:

email                       id
aaaaaaaaa99@yahoo.com.br    136
bbbbbbbbb99@yahoo.com.br    365
ccccccccc99@yahoo.com.br    765


texts: Tables for Product Components AND Component Combinations

texts:
I have a basic understanding of relational databases and would like to assign a number of attributes to an assortment of products. Products examined consistently have four components: model, fabric, color and size; each of which has a corresponding vendor-supplied identifier (ie. 'SSSS.MMMM.CCCC.ZZZZ' = a single product at the size level).

The most streamlined solution would seem to be having a product table that is at the size level, then a table for model, fabric, color and size to assign relevant attributes under each. The problem I am facing is that I would like to assign certain attributes to, say, a given fabric/color combination. Should I then also have tables of each fabric/color combination in this case?

I have laid out roughly what I am referring to above with the below table diagram:


texts: SQL Server: Identifying object closest to the end of a datafile

texts:
TL\DR

I'm looking for a way to efficiently identify the object located closest to the end of a SQL Server data file.  This approach needs to remain performant against large data files.

What I have so far

The following query utilizes an undocumented Dynamic Management Function that shipped with SQL 2012: sys.dm_db_database_page_allocations; this DMF provides a rough equivalent to the DBCC IND command.

The following query identifies the last object in a given data file (Warning: Don't run this against a database larger than 25 GB unless you want to cancel it at some point):

-- Return object with highest Extent Page ID
SELECT   files.name as logical_file_name
        , files.physical_name as physical_file_name
        , OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS object_name
        , alloc.*
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, NULL) alloc
    INNER JOIN sys.database_files files
        ON alloc.extent_file_id = files.file_id
WHERE is_allocated = 1
    AND files.name = 'Logical_FileName'
ORDER BY files.name , files.physical_name, extent_page_id DESC

What's wrong with this approach

As the Warning above implies, this query will run slower as the size of the database increases because the function is really designed for a pointed approach to look at a specific object and not look at a specific data file in question.  When passing in the NULL parameters as I did, this function likely iterates through all objects within the database behind the scenes and spits out the combined output.  This accomplishes what I need, but it does so in a very brute-force way that doesn't lend itself to optimizations.

What I'm asking for

I'm hoping there's a way to iterate through the GAM, SGAM, and/or IAM chains to quickly identify the object at the end of a given data file.  I'm assuming I have to push this approach outside of TSQL to something like PowerShell and go back to using DBCC PAGE calls, or something of that nature, traversing page allocation maps to find what the last object for a given data file is.  ... and I'm hoping someone has already thrown that code together or knows these structures and/or the output of these undocumented procedures better than I do.

Why do I need this?

This is the inevitable question many will ask, so I'm just going to answer it out of the gate.  The project I've been working on is to bring a legacy system up to speed, but after consolidating a bunch of heap tables together (which was a nightmare for other reasons), I am now left with a bunch of free space within my data files.  I want to release this space back to the OS, however, the traditional approach of migrating objects to a different data file isn't viable at this stage because I don't have enough free space to work with on the system (until I am able to release more space from this data file).

I've resorted to disabling file growth and running a DBCC SHRINKFILE TRUNCATEONLY command nightly to free up any open pages toward the end of the data file, but this is a slow and arduous process that may work as often as it doesn't.  I'm hoping to identify what the objects toward the end of the file are so I can rebuild them manually and free up space in a quicker timetable.

In Summary

Is there a way to quickly identify the name of the object located at the end of a given data file?  The method I'm employing now is not meeting my needs and I'm open to using any approach available.


texts: How to run sql agent jobs in a sequence order.with T-sql

texts:
My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 completes.

Thank you.


texts: Can you import a Postgresql 9.5.10 db into a Postgresql 10.9 server?

texts:
I would like to know if it is safe to import a dump made from a database running on a PostgreSQL server 9.5.10 to a PostgreSQL server 10.9.

Basically I am able to do the import. It does not fail. But how can I be sure nothing in the schema has been lost somehow ? We are talking Production here.

What I have done:

I have dumped the db from the current RDS server (9.5):

/usr/lib/postgresql/9.5/bin/pg_dump -h server-psql-9.5.amazonaws.com -U xxxxxx --no-password -Fc xxxxxxx -f psql-9-data.dmp

Using pg_restore version 9.5 I have created the restore file:

pg_restore -l xxxxxxxxxxx | sed '/COMMENT - EXTENSION/s/^/; (filtered out) /' > db_restore

I have dropped the database at the new RDS server (this worked using psql version 9.5 on a psql server version 10):

psql -X -h server-psql-10.amazonaws.com -U xxxxxxxxxxxx -d xxxxxxxxxxx -f drop_all.sql

Using pg_restore version 10 I have restored the database  using the dump of the 9.5 db:

pg_restore -h server-psql-10.amazonaws.com -U xxxxxxxxxxx -d xxxxxxxxxxxxx --no-password --no-owner --no-privileges -L  db_restore psql-9-data.dmp

Using pg_dump version 10 I have dumped the schema of the 10 db:

pg_dump -h server-psql-10.amazonaws.com -U xxxxxxxxxx --no-password -Fp xxxxxxxxxxx -s -f psql-10-schema.dmp

Using pg_dump version 9 I have dumped the schema of the 9.5 db:

/usr/lib/postgresql/9.5/bin/pg_dump -h server-psql-9.5.amazonaws.com -U xxxxxxxxxxxxx --no-password -Fp xxxxxxxxxxx -s -f psql-9-schema.dmp

Then I used diff to compare the two schema dumps and it shows there is a lot of difference between the schema of 9.5 and 10. That is why I don't feel safe AT ALL doing this. Why arent the schemas identical ?

If I eliminate the step of creating a restore file and I import the dump directly (as suggested in the comments) I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3613; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: [archiver (db)] Error from TOC entry 3614; 0 0 COMMENT EXTENSION citext
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension citext
    Command was: COMMENT ON EXTENSION citext IS 'data type for case-insensitive character strings';

pg_restore: [archiver (db)] Error from TOC entry 3615; 0 0 COMMENT EXTENSION "uuid-ossp"
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension uuid-ossp
    Command was: COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';


texts: Multi master replication with MySQL and Django

texts:
I am working on scaling out a webapp and providing some database redundancy for protection against failures and to keep the servers up when updates are needed. The app is still in development, so I have chosen a simple multi-master redundancy with two separate database servers to try and achieve this. Each server will have the Django code and host its own database, and the databases should be as closely mirrored as possible (updated within a few seconds).

I am trying to figure out how to set up the multi-master (master-master) replication between databases with Django and MySQL. There is a lot of documentation about setting it up with MySQL only (using various configurations), but I cannot find any for making this work from the Django side of things.

From what I understand, I need to approach this by adding two database entries in the Django settings (one for each master) and then write a database router that will specify which database to read from and which to write from. In this scenario, both databases should accept both reads and writes, and writes/updates should be mirrored over to the other database. The logic in the router could simply use a round-robin technique to decide which database to use. From there on, further configuration to set up the actual replication should be done through MySQL configuration.

Does this approach sound correct, and does anyone have any experience with getting this to work?


texts:
I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy.

It contains Tweets, and the id of each Tweet is one of the fields, but it is not used as the primary key, instead it has a non-clustered index on it & there is a separate field for the primary key "localPkId" (also the clustered index) that is just an identity and isn't actually used anywhere (so all other tables will still join on the Tweet ID, ignoring the "localPkId" field).

The Tweets that get inserted will come into the system out of order, so by not using Tweet ID as the clustered index, it means new rows will always get put at the end, speeding up inserts.

This had been working fine for some time as the table had a predominantly write heavy workload, but now it's being used for a lot of reads as well and I'm concerned that as the workload continues to grow this will become a problem.

First question: Does having "localPkId" offer any advantage over the table being a heap/having no clustered index?

Second question: Should the Tweet ID be the Primary Key & Clustered Index & then set a low fillfactor? If so, how would I go about choosing an appropriate value for the fillfactor in a case like this?


texts:
The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use --single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other transactions. Unfortunately, this only works if you can somehow prevent any other connection from executing a schema change during the dump:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

While a --single-transaction dump is in process, to ensure a valid
dump file (correct table contents and binary log coordinates), no
other connection should use the following statements: ALTER TABLE,
CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent
read is not isolated from those statements, so use of them on a table
to be dumped can cause the SELECT that is performed by mysqldump to
retrieve the table contents to obtain incorrect contents or fail.

What is a good way of ensuring that no schema changes happen during a dump? My use case is that I periodically (cronjob) back up my database every hour using the mysqldump utility.
I thought that maybe using mysqldump's default of --lock-tables instead would give me transactional consistency, but:

The --single-transaction option and the --lock-tables option are
mutually exclusive because LOCK TABLES causes any pending transactions
to be committed implicitly.

Is that saying that if someone runs a mysqldump --lock-tables against an InnoDB that it can actually cause an ongoing transaction in another session to no longer be transactional? I hope not -- that seems insane. Elsewhere:
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-and-transactions

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as
follows:
LOCK TABLES is not transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.

This makes sense within the context of a single DB session. If you in the middle of a transaction on your session and do this, then it implicitly commits your open transaction first. It isn't clear what it does, if anything, to transactions on other sessions. I'm hoping that it wouldn't do anything, or rather that it would be consistently ordered with respect to any ongoing transactions on other sessions.
Am I just reading too much into the mysqldump docs and using --lock-tables achieves transactional consistency?
Are the mysqldump docs simply explaining that doing --single-transaction together with --lock-tables is nonsensical because --lock-tables would immediately and implicitly close the single transaction? (Couldn't the reverse order work? But maybe it would be pointless?)
Is mysqldump --lock-tables otherwise transactionally consistent?
Thanks!


texts: Creating one stored procedure for all CRUD operations

texts:
I'm very inexperienced at writing stored procedures with conditional logic and I could really use some help. I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table. I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.  You'd think there would be more examples online but still struggling to find a good one.

Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?

I recently found this post on this website: (Separate stored procedures for inserts and updates?) but I don't know how it would be modified to also include the other two operations for selecting data and deleting data.

I have attached my data model for reference. Thanks in advance for your help!


texts: Why PostgreSQL volatile function is faster than inline subquery?

texts:
The following examples have been tested in PostgreSQL versions 11 and 12.
A few of the tables involved have around 2m rows, others not more than 250k.
I have very big and nested queries like this (not the actual query, just an example):
SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM
  (SELECT table2.some_id AS label4,
                       table2.id AS label5,
                       table3.some_date AS label6,
                       CASE
                           WHEN (table2.some_value IS NOT NULL
                                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
                           ELSE table4.table4
                       END AS label6
   FROM table3
   JOIN
     (SELECT coalesce(table5.table5) AS table4,
             coalesce(table5.table5_1) AS table5_1,
             coalesce(table5.table5_2) AS table5_2,
             coalesce(table5.table5_3) AS table54_3
      FROM
        (SELECT table2.some_value * 1 AS table5,
                table2.some_id AS table5_some_id,
                table2.id AS table5_id,
                table3.some_date AS table5_some_date
         FROM table3
         JOIN table2 ON table2.some_other_id IN
           (SELECT unnest(something_nested.ids) AS unnest_1
            FROM
              (SELECT ...

The query above takes around 49s to execute.
Then I decided to convert some of these subqueries into functions. It was literally just a cut/paste of some pieces of the big query, resulting in a final query that reads like this:
SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM some_function()

-- some_function created as "STABLE":
SELECT table2.some_id AS label4,
       table2.id AS label5,
       table3.some_date AS label6,
       CASE
           WHEN (table2.some_value IS NOT NULL
                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
           ELSE table4.table4
       END AS label6
FROM some_other_function()

The query above takes the same 49s to execute.
However, as soon as I changed one function (any of them) to VOLATILE, the query magically executes in less than 250ms.
My questions are:

Why VOLATILE is WAY faster than STABLE in this case? Is it safe to use VOLATILE?
Why declaring subqueries as function executes faster than just inlining them? Is there a flag or some way to optimise this kind of scenario?

EDIT
I tried to add the execution plan here but the text body is limited to 30000 characters and one plan alone is bigger than that.
This is the obfuscated execution plan for the first query (inlined subqueries)
https://explain.depesz.com/s/A3mG
This is the obfuscated execution plan for the query with volatile functions that runs super fast
https://explain.depesz.com/s/Yhs1
EDIT 2
This configuration did the trick, the first query now runs in 230ms.
SET enable_nestloop = off;
I'm interested to know, behind the scenes, what Postgres is doing that is making these queries run so quickly.


texts: Is it wise to create indexes for the replication server only?

texts:
At my workplace we have a requirement to have extra indexes on the replication server (we replicate data only). This is because we a bunch of reporting that runs off the replication server so some more indexes will help this run faster. These indexes would slow down actual transactions for our customers so we don't want them on the live DB.

I'm not an expert with databases and it's my first job, and so I'm turning here for a bit of advice, since I don't know whether my workplace is headed down the right path.

I know this is a somewhat broad question, but what I would like to know is:

are there better ways of achieving what we want
is what we want a really bad idea and we should avoid it

I am happy to implement what my workplace has asked me to do, but thought it might be better to seek some wisdom from people who have a lot more experience.


texts: Hyperthreading & MySQL InnoDB Thread Concurrency Performance

texts:
I have a dedicated DB Server with the following specs"

Ubuntu 12.04 LTS Server
Intel(R) Xeon(R) CPU X5670

@ 2.93GHz
12 Cores

MySQL 5.5.29
64 GB RAM
RAID10

We are currently doing Hyperthreading so we have 24 logical cores. Currently running between 2k-4k transactions/sec.

I have set the following :

innodb_thread_concurrency = 48
innodb_read_io_threads = 24
innodb_write_io_threads = 24

This is strictly OLTP load (70% reads). Queries are relatively fast (milliseconds). Query cache is ON but not used too much. Temp disk tables are not created often. The InnoDB Buffer Pool is set to 48GB which covers almost the whole data set (60 GB data+index).

Transactional load will double to 4k-8k transactions/sec. Currently CPU load is around 1000% so I anticipate that this will be the bottleneck.

How can I better tune my CPU threading and how MySQL uses these threads? Let me know if anyone has done any benchmarks on innodb_thread_concurrency.

I would appreciate any suggestions.

UPDATE

After updating from innodb_thread_concurrency = 48 to:

innodb_thread_concurrency = 0

On a production system during peak times (4-5k transactions/sec) to see the behavior this is what I observed:

Load average increase
mysqld process CPU usage went from 400% - 1000% to 400% - 1400%
About 4-10 CPUs are idle while the other 14-20 CPUs are around 70%-90%
%wa is 0% - 0.1% for each CPU so I/O is not the problem here. (From linux top output)

It seems like setting innodb_thread_concurrency to a non-zero value throttles your CPUs so that they don't go overboard. I set it back to innodb_thread_concurrency=48 and CPU usage decreased.

I would appreciate any thoughts on these results.


texts: SQL Job step history to display DML changes

texts: A vendor provided a script to run with many DML statements and i dump this in a stored procedure to run it from SQL job. When running the script via SSMS, it returns affected row counts after going through each DML statements such as "update". How can i quickly get the row counts to appear in SQL job step or dump to a text file via job step without setting using @@rowcounts after each of the DML statements?


texts: Limit columns to certain width in MySQL command

texts:
The mysql command gives nice tabular outputs that take into consideration half and full width spacing.

For example:

> select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０';
+------------------------------------+
| text                               |
+------------------------------------+
| The quick brown fox jumps over the |
| ０１２３４５６７８９０             |
+------------------------------------+
2 rows in set

Given this, how do I truncate a column to a certain width?

e.g. How do I get this?

+----------------+
| text           |
+----------------+
| The quick brow |
| ０１２３４５６ |
+----------------+

Edit: In terms of what I've tried.

If I attempt this by changing the query to use something like LEFT():

> select left(text, 7) text from (select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０') x;
+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| ０１２３４５６        |
+-----------------------+
2 rows in set

What I get isn't exactly what I wanted. I don't want 7 left characters of each string, I want both strings to show as many characters as optimally fit into a given width.  I want a result similar to what's under "e.g. How do I get this?", if say this width was 14 (half-width characters).

MySQL does cope with different widths, as the first example establishes. (Look at it with a monospaced editor and the pipes line up.) Any solution like SQL Plus' column foo format a10, or using ENCODE() and BIT_LENGTH(), or user functions could work. The lack of answers probably means there isn't an easy solution?


texts: PostgreSQL Update and Delete are not (logically) replicated while having REPLICA IDENTITY

texts:
I was trying to sync a table with Logical Replication. This needs to cover Insert, Update and Delete. However the replication only worked for Insert and not the rest.
As documented here

A published table must have a “replica identity” configured in order
to be able to replicate UPDATE and DELETE operations, so that
appropriate rows to update or delete can be identified on the
subscriber side.

Here's the schema of the publishing table
CREATE TABLE mytest.point_a (
gid serial NOT NULL PRIMARY KEY,
the_value numeric(10,2),
name TEXT NOT NULL,
geom geometry(Point,4326) );

CREATE INDEX test_point_a_geom_idx
ON mytest.point_a USING gist (geom);
ALTER TABLE mytest.point_a REPLICA IDENTITY DEFAULT;

The above  table I was trying to replicate has the REPLICA IDENTITY clause and a serial integer column as Primary Key.
The corresponding column in the subscribing table is just a plain bigint without Sequence.
Here's the subscribing table:
CREATE TABLE mytest.point_a (
gid bigint,
--column order is different, this is on purpose
name TEXT,
address TEXT, --not exists in publishing
geom geometry, --more flexible than the one in publishing
the_value(10,2)
);
--this one does not have index

All inserts are replicated but Updates and Deletes are not.
So what's missing here? Did I add REPLICA IDENTITY incorrectly?
Another relevant question: How do we know if a (publishing) table has REPLICA IDENTITY clause? In pgAdmin III and DBeaver, the DDL view of my publishing table in the above do not show any hint about it.


texts: How to configure Ola Hallengren backup script for encryption

texts:
How can I add encryption to ola.hallengren backup script.

In short i want to achieve this

BACKUP DATABASE [MyTestDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'
WITH
  COMPRESSION,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = MyTestDBBackupEncryptCert
   ),
  STATS = 10
Go


texts: which one is faster: "insert on duplicate update" OR "update with case"

texts:
Table:

CREATE TABLE t5 (
 col1 VARCHAR(36) NOT NULL PRIMARY KEY,
 col2 int(11) DEFAULT NULL,
 col3 int(11) DEFAULT NULL,
 UNIQUE (col2)
);

Records already inside:

insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);

Which of these two queries are expected to perform better:

Query-1:

insert into t5(col1, col2, col3) values
 ('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
 ('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
on duplicate key update
 col2=if(values(col2) IS NULL, col2, values(col2)),
 col3=if(values(col3) IS NULL, col3, values(col3));

Query-2:

update t5 set
 col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END),
 col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');

Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.

Condition of the table and the frequency the above query is performed as below:

number of records is in hundreds of thousand
query is performed once every x-seconds, each with ~150 keys

The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)


texts:
I am trying to restore 2 databases. One is adventureworks database downloaded from msdn(its .bak file), and another one is created by me from one databases from my old server. Both have .bak extension, are in the same folder where the new instance where they should be restored, has all the access privileges. Now while restoring adventureworks database everything goes alright. While trying to restore the other .bak file made by me I get an access error, it wants access to its original server's DATA folder. Here is the error

Now, I want to know how to create .bak backup in order to restore it to any other server without this error.

Thanks for your time!


texts:
I am building a config table and want one row to be the default value. For example, I have a list table of all my databases and a table called msg which has all my return values. In the msg table, I have one row that is 'alldbs' and I want that value returned if there isn't another row for the joined database.

So the output should be:

--LIST TABLE
dbname                                             createdate
-------------------------------------------------- -----------------------
master                                             2015-06-23 10:05:14.363
model                                              2015-06-23 10:05:14.363
msdb                                               2015-06-23 10:05:14.363
tempdb                                             2015-06-23 10:05:14.363
dummy                                              2015-06-23 10:05:14.363

--MSG Table
dbname                                             msgval
-------------------------------------------------- --------------------
alldbs                                             Message 1
dummy                                              Message 2

--DESIRED OUTPUT
dbname                                             msgval
-------------------------------------------------- --------------------
dummy                                              Message 2
master                                             Message 1
model                                              Message 1
msdb                                               Message 1
tempdb                                             Message 1

Now I can get the result, but the query is clunky and it seems like there should be a better way:

select
    a.dbname
    ,b.msgval
from
    list a
    join msg b on a.dbname = b.dbname
union all
select
    a.dbname
    ,b.msgval
from
    list a
    ,msg b
where
    a.dbname not in (select dbname from msg)
    and b.dbname = 'alldbs'

Is there a more efficient way to write this query?

SQLFiddle Link using SQL 2014.


texts: I am trying to create a trace in Extended Events filtering with logins, in profiler we can filter with login names but I do not see that option in XE. How do I do that?


texts: Microsoft SQL Server 2008 encryption over multiple DB versions

texts:
I have about 5 clients deployed running different versions of SQL Server 2008 (Some standard, some enterprise, etc.) And I am being tasked with encrypting the data so:

The software we are using can still query the data with decent efficiency.
DBA's can still get into the data through Management Studio.
If someone were to obtain a copy of the backup files (being stored on an external HDD), they would be unable to restore the database.

It seemed as if Transparent Data Encryption would be perfect, and I have it functioning, but "This feature is only available in Enterprise and Developer Editions of SQL Server 2008".

I want to have the method of encryption standard over all of the deployed servers.
What is a good method to do so?  Should I simply encrypt the HDDs?

Also, am I missing any other way that a person could potentially get a copy of database (Assuming no passwords are breached)?