Trouble when accessing old after deleting a row in postgrsql

To have a realistic demo case, you need to populate the tables, not just create them.
Your title says "after deleting a row", but your trigger is clearly a "before" trigger.
If you (attempt to) delete a row from people which has no corresponding entry in accounts, then athleteSum naturally becomes NULL.  What else would it do?  If you wish to avoid this, you need to take steps to avoid it.  Convert to zero?  Throw an error?  I don't know, only you can know.

addBalance := ROUND(athleteSum / peopleCount::INTEGER);

You are dividing two integers.  The result of integer division is already an integer.  No rounding is necessary.  Shouldn't it be (peopleCount-1)?  Otherwise you are distributing the value back to the row that was just (attempted to be) deleted.

FOR person IN 0..peopleCount LOOP

WHERE peopleID = person;

Why would you expect the peopleID to be densely packed between 0 and peopleCount?  Especially since you just attempted to delete one of those intervening values?

RETURN NEW;

For a delete trigger, NEW is NULL. Returning NULL prevents the delete from actually happening.  Is that what you want?


Mysql - How to Optimize Retrieval Time in a Table

Maybe GROUP BY can help. See this MySQL reference on DISTINCT optimization

SELECT COUNT(*)
FROM (
  SELECT 1
  FROM [tablename]
  WHERE [columnname] >= 3
  GROUP BY id
) q


Suboptimal query plan when updating partitioned table

Reduced the run time to 1-2sec by using a third CTE:

WITH x AS (
  SELECT t."p1", t."p2", t.”b2”, f(t."b1") OVER "win_x" AS "c1"
  FROM parent_table AS "t"
  WHERE t."p1" IN ('val1','val2')
  WINDOW "win_x" AS (PARTITION BY "p1" ORDER BY "p1","p2")
), y AS (
  SELECT x."p1", x."p2", x.”b2”, f(x."c1") OVER "win_y" AS "c2"
  FROM x
  WINDOW "win_y" AS (PARTITION BY "p1" ORDER BY "p1","p2")
), z AS (
  SELECT y.”p1", y.”p2", y.”b2”*y.”c2” AS “a1”
  FROM y
)
UPDATE parent_table AS "t”
SET “a1"=z.”a1”
FROM z
WHERE t.”p1"=z.”p1" AND t.”p2"=z.”p2";


Query results that not are in previous query result

You are missing the single quote closure after motherboard in both of your queries

AND value != 'Motherboard AND

AND a.value != 'Motherboard

The first query can be more organized as

SELECT id_product
FROM Atr_basic
WHERE attribute = 'Product Type'
AND category = 140
AND value NOT IN ( 'Motherboard', 'Intel Motherboard')


Why does updating a large VARBINARY break replication (MSSQL_REPL21100)?

After much frustration and fruitless googling, I finally figured out the problem. It was not that the accounts for the 'SQL Server' and 'SQL Server Agent' services did not have access to the directory. The problem was that the account for the 'Log Reader Agent' did not have access. Should have been obvious from the error message, but I just didn't get it...

On the publication server, run this:

USE DatabaseName
EXEC sp_helplogreader_agent

The job_login column will tell you which account runs the Log Reader Agent. Grant that account read/write access to the directory and everything works fine.


How can I get PostgreSQL to not advance sequences when COPY fails?

The sequences are designed like that, the are not supposed to produce contiguous numbers.

However, you can reset them before (re)trying the COPY with

SELECT setval('sequence_name', (SELECT max(id) FROM your_table));


Why monitor bytes sent and received on mysql

First, let's identify each status variable:

Bytes_received: The number of bytes received from all clients.
Byte_sent: The number of bytes sent to all clients.

The only place I can think of where these number would make sense would be in the network. These status values represent the amount of data passing in and out of DB Connections. These bytes would most likely be visible from another perspective: the Operating System.

You could measure the amount on incoming/outgoing traffic in netstat against MySQL's view of it. If the amount of incoming data is low, or if the amount of outgoing data from MySQL is significantly higher than netstat says, check MySQL and/or the network. You may also want to look for any signs of dropped packets along any interfaces.

In light of this, when it comes to tuning, the only thing I can think of that you may want to tune is setting two things:

net_buffer_length : Connection and result buffer size
max_allowed_packet : Maximum packet size used internally by MySQL

What is a MySQL Packet ?

According to the page 99 of the Book

here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Given this information, you may want to

raise net_buffer_length to its max value of 1048576
raise max_allowed_packet to 256M, 512M, or 1G (especially if you have BLOB data)


Does an Oracle nvarchar2 take twice as much space as varchar2?

Not exactly. Oracle accommodates both byte semantics and character semantics for VARCHAR. The default for character types is byte semantics; a column defined as VARCHAR2(10) holds 10 bytes, which might not be the same as 10 characters. The default for new columns is controlled by the parameter NLS_LENGTH_SEMANTICS.

But in a UTF-8 database, you can define a column as VARCHAR2(10 CHAR), which will hold 10 characters. Even if five of them are single-byte characters and five of them are multi-byte characters, all 10 will fit.

NCHAR and NVARCHAR2 always use character semantics. A column of NVARCHAR(20) will hold 20 characters, but it might take 60 bytes to hold all of them.

NVARCHAR2 is limited to 4000 bytes, which is 4000 single-byte characters, but only a third that many multi-byte characters. (Assuming a maximum of three bytes per character.)

Disk space is much less expensive than it used to be. But you still have to get data off the disk for it to be useful. More bytes, more time; more time, more expense. IME, cost of disk space doesn't matter only to people who don't have to pay for it.

More details in Oracle Database Concepts.


Table with contacts: how to differ between people and organizations?

I suggest you create them as separate tables.

Create an Individuals table and an Organizations table so that each
can have the proper fields for their respective types.
Draw from the same sequence to populate the ClientID field in both
tables.
Create a view to UNION ALL both tables for code that needs
information about both.
Create foreign keys to the tables as necessary.


Those are core dumps which are generated when PostgreSQL crashes.  You can use them with gdb to perhaps figure out why it is crashing.  You can delete them.  You can save one to use it to figure out why PostgreSQL keeps crashing, as that should absolutely not be happening.  Also look in the PostgreSQL log file for info on why it is crashing.


Select count of timestamp formatted field in short date format to give number of rows

Since the column type is a datetime and this is SQL Server 2016, converting it to a date is easy :
SELECT CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated]) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated])
ORDER BY 1;

If you really want to do it via an VARCHAR field, just do it like that :
SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105)
ORDER BY 1;


MS SQL Server - Increase allocated memory

SQL Server will not use memory for buffer pool (its biggest use) until it needs to load pages from storage because they are referenced. Unless you are doing something odd to make memory allocation slow[†] then your delay is unlikely to be that but reading the data that it is being allocated in order to hold. I suspect that at the same time as those memory allocation waits, you have matching IO related waits.
To force SQL server to load things into memory, and therefore to allocate memory if available and needed, access things so that they need to be loaded. After starting your SQL instance, run a read-only workload so that your application's normal common working set is loaded into the buffer pool.
Don't just SELECT * FROM HugeTable as this is likely to load a bad balance of data (not the applications core working set) so the core working set will still need to be loaded from disk/network on next access anyway so you'll have the same IO delays. Also if HugeTable is truly huge SQL Server may see that it'll not all fit and not bother keeping a significant amount of it in memory anyway[‡].
[†] Running in a VM on an overloaded host so what looks like real RAM is really on-disk pages?!
[‡] Under normal circumstances you don't want an unexpected table/index scan on a large object to evict everything else from memory. A limit on how much is kept from one object for one query is an optimisation to try stop this from happening).


As per the Oracle documentation, by default, InnoDB creates two redo log files (or just log files) ib_logfile0 and ib_logfile1 within the data directory of MySQL.
In MySQL versions 5.6.8 and above, the default size of each redo log file is 48MB each. This can be configured by the user by making use of innodb_log_file_size server option.

The generated redo log record will contain information necessary to repeat the same operation later during a database recovery. So the redo log record will contain information about the original set of pages and what has been changed in them. Using the redo log record the set of dirty pages will be re-created during database recovery.

So, as shortly I am explaining, to maintain the log sequence number(LSN) in Innodb, it is important.

For your Ref Redo Logging in InnoDB


You can add the fields Price and Description to the Orders Product table and copy the values when you create the row. This allows you to read the values as they were by the time of the order execution

Orders Product Table

ID  | OrderID  | Product  | Qty   | Description | Price
45  | 14       | 452SB    | 1     | Black       | 45.65
46  | 14       | 752SA    | 2     | Green       | 17.12
47  | 15       | 452SB    | 1     | Black       | 45.65

And yes, using Keys is always preferable

UPDATE

About foreign keys. Display them like this

Products Table

ID | StockNum | ProductDetailsID |
4  | 452SB    | 8                |
5  | 752SA    | 9                |

Products Details Table

ProductDetailsID | Description | Price
8                | Black       | 45.65
9                | Green       | 17.12


Database insanely (and weirdly inconsistently) slow on 8.0

Welcome to the growing query planner insanity heisenbug. It started in 5.7 and got worse in 8.0. I suspect what you will find is that EXPLAIN for that query looks strikingly different between 5.7 and 8.0.

Your options are:

1) See if it regains some sanity if you change optimizer_% variables to the same values as on 5.7

2) Run ANALYZE TABLE accounting; repeately and hope it'll change it's mind. Note that this is subject to change as statistics estimation runs periodically in the background.

3) Use index hinting (USE/FORCE INDEX(indexname)). This is stable, but you'll be whack-a-mole-ing queries throughout your code base.

4) Switch to MariaDB which doesn't seem to suffer from this kind of query plan instability.

Don't use MyISAM unless you have an overwhelmingly good reason for doing so.


Clustered Index with nightly ETL process

There are few ways to work around this problem:

As you mention, partitioning will help. If you partition on the leading column in the index and make sure all partitions are equally sized, you can gain super scale. However, the total CPU required to build the index wont be much lower if you partition. But it will save you memory (if you have a slow I/O system, this can be useful)

If this is a traditional data warehouse fact table, you might be better off with a columnstore index

Instead of doing a mega update - which is slow. Consider this pattern:

Load the changes you want to make into a staging table with the same schema and cluster index as the main table
Join the staging table with the old table and use INSERT INTO a new table with a cluster index. Enabled Trace Flag 610 while doing this.

This way, you can keep the benefit of the old table being sorted and avoid resorting all the rows (only the ones that have changed).


how do you convert a non Oracle Managed files into an Oracle Managed file

The following parameters enable the Oracle to use OMF(Oracle Managed File).
DB_CREATE_FILE_DEST- For Datafiles
DB_CREATE_ONLINE_LOG_DEST_n- For redo log files.
DB_RECOVERY_FILE_DEST- For controlfiles.

To convert non-OMF datafiles to OMF-

1) Set the db_create_file_dest parameter.

alter system set db_Create_file_dest='+DATA';

2) select file#, name from v$datafile;--to list all the datafiles and their file number.

3) alter database move datafile 1; --repeat this for all datafiles.

To convert non-OMF redo log files to OMF

1) set the DB_CREATE_ONLINE_LOG_DEST_n

2) Create new OMF redo log files.

 alter database add LOGFILE group 5 size 100M;

3) Drop the old redo log files.


I don't care much about the cluster column just need ids properly aggregated.

In that case we can make use of the uniq and sort functions in the intarray extension:

with recursive a as (
  select id, array_agg(distinct clst) clst from w group by id)
, t(id,pid,clst) as (
  select id,id,clst from a
  union all
  select t.id,a.id,t.clst|a.clst
  from t join a on a.id<>t.pid and t.clst&&a.clst and not t.clst@>a.clst)
, d as (
  select distinct on(id) id, clst from t order by id, cardinality(clst) desc)
select array_agg(id), clst from d group by clst;

array_agg | clst
:-------- | :------
{6}       | {5}
{2,3,4}   | {1,2,3}
{1,5}     | {0,4}

db<>fiddle here

Bear in mind that this is unlikely to perform well on millions of rows.


SQL Server 2008 log directory has too much dump file

Start checking the log - the dump files are that - dump files. Logs should indicate why the server crashs so often. Check hardware issues. Are you properly patched up? Do that - install the last CU from SQL Server Release Services.

If nothing helps, then a MS PSS ticket needs to be opened, they can analyze the dump files. This likely leads to some hotfix, in which case it is a free ticket.

But first check logs and patch up the server.


For SQL Server, how to fix simultaneous parallel table updates?

Given the code in your answer, you would most likely improve performance by doing the following two changes:

Start the query batch with BEGIN TRAN and end the batch with COMMIT TRAN:
Decrease the number of updates per batch to less than 5000 to avoid lock escalation (which generally occurs at 5000 locks). Try 4500.

Doing those two things should decrease the massive amount of tran log writes and lock / unlock operations that you are currently generating by doing individual DML statements.

Example:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 1;
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 2;
        ...
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 4500;
        COMMIT TRAN;
        ", conn));

UPDATE

The Question is a bit sparse on the details. The example code is only shown in an answer.

One area of confusion is that the description mentions updating two columns, yet the example code only shows a single column being updated. My answer above was based on the code, hence it only shows a single column. If there really are two columns to update, then both columns should be updated in the same UPDATE statement:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;
        UPDATE [TestTable]
        SET    Column5 = 'some unique value',
               ColumnN = 'some unique value'
        WHERE  ID = 1;
        UPDATE [TestTable]
               SET Column5 = 'some unique value',
               SET ColumnN = 'some unique value'
        WHERE  ID = 2;
        ...
        UPDATE [TestTable]
               SET Column5 = 'some unique value',
               SET ColumnN = 'some unique value'
        WHERE  ID = 4500;
        COMMIT TRAN;
        ", conn));

Another issue that is unclear is where is the "unique" data coming from? The Question mentions that the unique values are GUIDs. Are these being generated in the app layer? Are they coming from another data source that the app layer knows about and the database does not? This is important because, depending on the answers to these questions, it might make sense to ask:

Can the GUIDs be generated in SQL Server instead?
If yes to #1, then is there any reason to generate this code from app code instead of doing a simple batch loop in T-SQL?

If "yes" to #1 but the code, for whatever reason, needs to be generated in .NET, then you can use NEWID() and generate UPDATE statements that work on ranges of rows, in which case you do not need the BEGIN TRAN / 'COMMIT` since each statement can handle all 4500 rows in one shot:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"UPDATE [TestTable]
        SET    Column5 = NEWID(),
               ColumnN = NEWID()
        WHERE  ID BETWEEN 1 and 4500;
        ", conn));

If "yes" to #1 and there is no real reason for these UPDATEs to be generated in .NET, then you can just do the following:

DECLARE @BatchSize INT = 4500, -- this could be an input param for a stored procedure
        @RowsAffected INT = 1, -- needed to enter loop
        @StartingID INT = 1; -- initial value

WHILE (@RowsAffected > 0)
BEGIN
  UPDATE TOP (@BatchSize) tbl
  SET    tbl.Column5 = NEWID(),
         tbl.ColumnN = NEWID()
  FROM   [TestTable] tbl
  WHERE  tbl.ID BETWEEN @StartingID AND (@StartingID + @BatchSize - 1);

  SET @RowsAffected = @@ROWCOUNT;
  SET @StartingID += @BatchSize;
END;

The code above only works if the ID values are not sparse, or at least if the values do not have gaps larger than @BatchSize, such that there is at least 1 row updated in each iteration. This code also assumes that the ID field is the Clustered Index. These assumptions seem reasonable given the provided example code.

However, if the ID values do have large gaps, or if the ID field is not the Clustered Index, then you can just test for rows that do not already have a value:

DECLARE @BatchSize INT = 4500, -- this could be an input param for a stored procedure
        @RowsAffected INT = 1; -- needed to enter loop

WHILE (@RowsAffected > 0)
BEGIN
  UPDATE TOP (@BatchSize) tbl
  SET    tbl.Column5 = NEWID(),
         tbl.ColumnN = NEWID()
  FROM   [TestTable] tbl
  WHERE  tbl.Col1 IS NULL;

  SET @RowsAffected = @@ROWCOUNT;
END;

BUT, if "no" to #1 and the values are coming from .NET for a good reason, such as the unique values per each ID already exist in another source, then you can still speed this up (beyond my initial suggestion) by supplying a derived table:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;

        UPDATE tbl
        SET    tbl.Column5 = tmp.Col1,
               tbl.ColumnN = tmp.Col2
        FROM   [TestTable] tbl
        INNER JOIN (VALUES
          (1, 'some unique value A', 'some unique value B'),
          (2, 'some unique value C', 'some unique value D'),
          ...
          (1000, 'some unique value N1', 'some unique value N2')
                   ) tmp (ID, Col1, Col2)
                ON tmp.ID = tbl.ID;

        UPDATE tbl
        SET    tbl.Column5 = tmp.Col1,
               tbl.ColumnN = tmp.Col2
        FROM   [TestTable] tbl
        INNER JOIN (VALUES
          (1001, 'some unique value A2', 'some unique value B2'),
          (1002, 'some unique value C2', 'some unique value D2'),
          ...
          (2000, 'some unique value N3', 'some unique value N4')
                   ) tmp (ID, Col1, Col2)
                ON tmp.ID = tbl.ID;

        COMMIT TRAN;
        ", conn));

I believe the limit on the number of rows that can be joined via VALUES is 1000, so I grouped two sets together in an explicit transaction. You could test with up to 4 sets of these UPDATEs to do 4000 per transaction and keep below the limit of 5000 locks.


Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn't end in that character

How about this :

select t1.key
from table1 t1
inner join table1 t2
      on   t1.key = t2.key || 'P'
where t1.key like '%P'
order by t1.key ;

Querying t1 gives you a list of all of the rows with keys that end in 'P'.

The join to t2 is based on rows where t2's key, plus a trailing 'P', matches the key in t1  (or, alternatively, the original key without the trailing 'P').


I think this is a good candidate for using LATERAL join:

SELECT id,
       shared.views     AS views_count,
       is_shared,
       shared.views > 0 AS tracked
FROM   pages
       LEFT JOIN LATERAL
                 (SELECT sum(shares.view_counter) AS views
                  FROM   shares
                  WHERE  shares.page_id = pages.id
                 ) AS shared
              ON TRUE
WHERE pages.id IN (1, 2, 3) ;


This isn't really possible in general.

The full range from 0x00 to 0xFF is valid for varchar and from 0x0000 to 0xFFFF for nvarchar so it is impossible to distinguish between two bytes that represent 2 varchar characters or a single nvarchar character.

0x4141 could be 'AA' or N'䅁'

If DATALENGTH is odd then you know it is varchar though.

Also if the text is primarily standard ASCII as in your example then it will contain a lot of 0x00 when stored as binary if nvarchar.

This is probably not a character you expect to reasonably find at all in varchar strings so the presence of that could be taken as evidence that the string is nvarchar.


Why should pupbld.sql be run as SYSTEM (and not as SYS)?

The script creates certain objects, and it will put them in the current schema. Oracle wishes that these objects be created on SYSTEM schema, hence the requirement.

There are two Oracle schemas, SYS and SYSTEM, because they have two purposes. In short, SYS truly is the "special" user in respect to database internals, while SYSTEM works normally.

The SYS is the owner of the database, the only user with access to X$ tables, the only user which acts as SYSDBA (internally), that bypasses logon triggers, and that, for some obscure reason, cannot obtain read-consistent view of data. As a schema it holds crucial Oracle stuff, mainly the data dictionary.

The SYSTEM is fairly normal schema with DBA privilege (database administrator, not the same as SYSDBA), only that it is built-in and contains some additional (but also quite important) Oracle stuff. You shouldn't put your own stuff in this schema, if not instructed so by Oracle.


How to remotely connect SQL Server 2016. Since we have 2012 and 2016 in same server

Sometimes you need to open firewall rule for UDP 1434 SQL Browser port. Be sure SQL 2012 and 2016 don't use same TCP port (1433). If multiple instance on same machine they should be configured to use separate TCP port.


I would verify the integrity of your backup files using the following commands.

Restore HEADERONLY

SQL BOL: Returns a result set containing all the backup header information for all backup sets on a particular backup device.

RESTORE HEADERONLY FROM DISK ='\\sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak'

You might find some information why your backup file is not working.

Restore FILELISTONLY

SQL BOL: Returns a result set containing a list of the database and log files contained in the backup set.

RESTORE FILELISTONLY FROM DISK = '\\sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak'

There was a rare occasion where the logical filename of the database was truncated in SQL Server 2008 R2

You might find more information in the ERRORLOG of the server you are restoring to. Otherwise I would consider running the restore script outside of the scheduled task in Query Window to catch any other errors.


SQL command to remove prefix from a set of tables

Wow I answered a similar question a year ago.

Nevertheless, your question is unique.

Here it goes:

If you are renaming all tables in the database mydb, here is the query you need:

SELECT
CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';')
FROM
(
    SELECT
        table_schema db,
        table_name old_tblname,
        substr(table_name,4) new_tblname
    FROM
        information_schema.tables
    WHERE
        SUBSTR(table_name,1,3)='dr_'
        AND table_schema = 'mydb'
) A;

Run this in the OS and capture it to a SQL file. Then, execute the SQL file.

mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';') FROM (SELECT table_schema db,table_name old_tblname,substr(table_name,4) FROM information_schema.tables WHERE SUBSTR(table_name,1,3)='dr_' AND table_schema = 'mydb') A" > BigRename.sql
mysql -u... -p... < BigRename.sql

If you are renaming all tables in all databases, here is the query you need:

SELECT
CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';')
FROM
(
    SELECT
        table_schema db,
        table_name old_tblname,
        substr(table_name,4) new_tblname
    FROM
        information_schema.tables
    WHERE
        SUBSTR(table_name,1,3)='dr_'
) A;

Run this in the OS and capture it to a SQL file. Then, execute the SQL file.

mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',old_tblname,' RENAME ',db,'.',new_tblname,';') FROM (SELECT table_schema db,table_name old_tblname,substr(table_name,4) FROM information_schema.tables WHERE SUBSTR(table_name,1,3)='dr_') A" > BigRename.sql
mysql -u... -p... < BigRename.sql


How to change SESSION_CACHED_CURSORS for all sessions

The ALTER SYSTEM SET clause for this parameter is only valid with the SCOPE=SPFILE option, so you can not change the parameter value with this dynamically.

You can change the value for all other sessions, e.g as below:

begin
for c in (select sid, serial# from v$session) loop
   dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100);
end loop;
end;
/

This however will not have any effect on the sessions opened after this point.
For that, you have to use ALTER SYSTEM as above and restart the database.


Moving the MySQL data directory Ubuntu 16.04

Verify the existing MySQL installtion:

mysql -u root -p

From the MySQL prompt, do:

select @@datadir;

You should see the current data directory:

/var/lib/mysql/

Exit the MySQL mode:

exit

Stop MySQL:

sudo systemctl stop mysql

Verify that MySQL is stopped:

sudo systemctl status mysql

Copy the data to the new location:

sudo rsync -av /var/lib/mysql /data/newlocation

(or whatever volume you've created to store the data)
Create a backup of the existing data volume

sudo mv /var/lib/mysql /var/lib/mysql.bak

Tell MySQL about the new location. Edit the file:

/etc/mysql/mysql.conf.d/mysqld.cnf

and change the datadir directive, to reflect the new location:

datadir=/data/newlocation

Tell AppArmor about it: edit the file /etc/apparmor.d/tunables/alias  and add:

alias /var/lib/mysql/ -> /data/newlocation/,

to the bottom of that file (the comma at the end of the line is NOT a typo).

Then do:

sudo systemctl restart apparmor

Create the minimal directory structure, to pass the mysql-systemd-start checks:

sudo mkdir /var/lib/mysql/mysql -p

Restart MySQL

sudo systemctl start mysql

Check status:

sudo systemctl status mysql

Verify that the new data directory is active:

mysql -u root -p

At the MySQL prompt, enter:

SELECT @@datadir;

Remove backup, once everything is verified to work:

sudo rm -Rf /var/lib/mysql.bak

Restart MySQL

sudo systemctl start mysql

Check status:

sudo systemctl status mysql


Well at least DBAs should be able to connect to such database using SQL Management Studio, or nothing would be possible like backup/restore/checks...

just do not give anybody the sa password and create a user for your application and configure it accordingly and also, do not give anybody the password of this application user.


CTE where to place the where clause to filter rows sooner (in postgresql)?

CTEs pose as optimization fences in Postgres.

Each CTE is planned, executed and materialized separately. The predicates of a later CTE are not visible in earlier CTEs. Place conditions to eliminate rows as early as possible (a or b in your example).

Or rewrite with subqueries to allow more aggressive optimizations in Postgres. (But you should still eliminate irrelevant rows as early as possible, optimization only goes so far ...)

Details in Craig's blog post:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/


You could use Flashback to return the table data back to its original state.

Is Flashback enabled?

Database needs to be in archive mode:

select log_mode from v$database;

Should return "ARCHIVEMODE"

Check if flashback is enabled:

select flashback_on from v$database;

Should return "YES"

If "NO", you can enable flashback by runing:

alter database flashback on;

Check undo retention:

show parameter undo

Default is 900 seconds (15-minutes). So you can Flashback a table to 15-minutes ago using this command:

Alter table t enable row movement ;

FLASHBACK TABLE t TO TIMESTAMP TO_TIMESTAMP ('2015-aug-11 12:00:00', 'YYYY-MON-DD HH24:MI:SS');

Other Flashback commands:

Flashback database = > enables you to take the entire database to a past point in time (using flashback logs, db_flashback_retention_target).
Flashback drop => lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
Flashback table => lets you recover a table to a time in the past (using undo data).
Flashback query => lets you query and restore data rows to a point in time (using undo data).

For Flashback table using SCN see this link:
https://oracle-base.com/articles/10g/flashback-10g#flashback_table


Merge jsonb objects and sum their values over multiple rows

As I pointed out in comments I think this job would be easy to accomplish working with a disaggregated data, because now IMHO you must disaggregate it again:

For example to get aggregated values for referrers:

SELECT
    hostname,
    r.key as ref_name,
    r.value::int as ref_value
FROM
    visits_agg
CROSS JOIN LATERAL
    jsonb_each(referrers) r;

hostname    | ref_name     | ref_value
:---------- | :----------- | --------:
example.com | example2.com |         4
example.com | google       |         6
example.com | google       |         6
example.com | facebook     |         1
example.com | example2.com |         1

Now you can get acumulated values grouped by ref_name, and then you must build the jsonb array again.

SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
          FROM   visits_agg
          CROSS JOIN LATERAL jsonb_each(referrers) r
          GROUP BY hostname, r.key) t1
GROUP BY hostname;

This is my approach, perhaps some Postgres guru can give you a better answer.

SELECT row_to_json(t0, true)
FROM
    (
    SELECT t1.hostname, t1.visits, t2.referrers, t3.paths, t4.widths
    FROM   (SELECT   hostname, SUM(visits) as visits
            FROM     visits_agg
            GROUP BY hostname) t1
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
            FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(referrers) r
                      GROUP BY hostname, r.key) t1
           GROUP BY hostname) t2
           ON t1.hostname = t2.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(path_name, path_value)) as paths
            FROM     (SELECT hostname, p.key as path_name, SUM(p.value::int) as path_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(paths) p
                      GROUP BY hostname, p.key) t1
            GROUP BY hostname) t3
           ON t1.hostname = t3.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(width_name, width_value)) as widths
            FROM     (SELECT hostname, w.key as width_name, SUM(w.value::int) as width_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(widths) w
                      GROUP BY hostname, w.key) t1
            GROUP BY hostname) t4
           ON t1.hostname = t4.hostname
    ) t0;

And this is the result:

{
 "hostname":"example.com",
 "visits":25,
 "referrers":[{"google": 12}, {"facebook": 1}, {"example2.com": 5}],
 "paths":[{"/about": 6}, {"/article/title2": 5}, {"/article/title1": 3}, {"/": 11}],
 "widths":[{"1680": 3}, {"1398": 1}, {"2560": 1}, {"360": 7}, {"1280": 7}, {"1440": 2}, {"1920": 2}, {"1283": 1}]
}

db<>fiddle here


How do I create a table whose values are based on queries of same table?

You are not off-base at all.  Actually pretty close.  In t-sql you can just use code like this:

CREATE TABLE [dbo].[Sample2](
    [myIntColumn] [int] NULL,
    [myOtherIntColumn] [int] NOT NULL,
    [myBigIntColumn] AS ([myOtherIntColumn]+[myIntColumn])
)

GO

    INSERT INTO Sample2 VALUES (365,256),(10,25000), (1,2)

    SELECT * FROM Sample2

    --myIntColumn   myOtherIntColumn    myBigIntColumn
    --365           256                 621
    --10            25000               25010
    --1             2                   3

If you are looking for an aggregate as the default value, I do not believe you can do that simply.
To have an aggregate as a column in the a table, a view is a viable option, like so:

CREATE VIEW Sample3
AS
WITH CTE AS (
SELECT SUM(myIntColumn) as mySUM
From Sample2 )
SELECT Sample2.*, CTE.mySUM FROM Sample2, CTE
GO

SELECT * FROM Sample3

--myIntColumn   myOtherIntColumn    myBigIntColumn  mySUM
--365           256                 621             376
--10            25000               25010           376
--1             2                   3               376

These queries assume your platform is MS SQL Server.

If your tag sql was meant to only indicate the language, it would be helpful both for this question and future questions to tag with the platform.  For example, you could additionally tag as sql-server.

Depending on your RBDMS and your requirements you might be able to leverage an indexed/materialized view.  This could additionally help performance, but there are some limitations on an Indexed View in SQL Server.


I use HeidiSQL, its free and open source.

Only for MySQL, and only on Windows.

Its fast, and supports multiple servers, multiple databases, and lets you edit:

Users
Tables, columns, indexes
Views, procedures, triggers, events
Data

It also has good data management features:

Filtering, searching
Export to SQL
Export/import XLS

And it looks nice!


Postgres 9.5 deadlock with many updates on a table + many "select for update" on another table

There is no deterministic ORDER BY in:
SELECT FROM * machines FOR UPDATE;

So multiple transactions might try to obtain row locks in different (arbitrary) order and interlock.
Also, why would you lock the whole table machines for a single update in magictools? If it's indeed a single UPDATE on magictools per transaction, there is no need to lock rows machines manually at all. Just drop the command. (If there are multiple, then your question is lacking.)
If there is a good reason to lock many or all rows in machines, either do it in deterministic order, like:
SELECT FROM * machines ORDER BY id FOR UPDATE;

Or lock the whole table instead, that's also cheaper:
LOCK TABLE machines IN SHARE MODE;

SHARE MODE is enough for the FK case you have. There is even an example in the manual suggesting as much.
Locking the table also disallows INSERT, of course, unlike the row level locks in your example.


The simplest way to think about normalizing data is that you want to get to where all the data in the table is either your primary key or dependent on the primary key.  It's also important to know how your entities relate to each other, because you'll have a different model for one-to-many versus many-to-many relationships.

I think the relationship modeling is where you've gone wrong. You're moving every relationship into its own table, which is not appropriate for a one-to-one or one-to-many relationship.  For instance, unless a single order can belong to multiple customers, the Customer_Order table is not the correct way to model this.  What you'd want instead is a CustomerNo column in your Order table, with the CustomerNo as a foreign key to the Customer table.  That identifies the customer who placed the order.

Same thing with your shipping info.  A table that contains only ShipperNo and DateShipped doesn't do you any good.  If the entire order must go out in a single shipment, then adding ShipperNo and DateShipped to the Order table would work.  If an order may be split into multiple shipments, then I'd create a Shipment table that contains (ShipmentNo, OrderNo, ShipperNo, DateShipped), as well as a ShipmentItems table that links the Shipment to the actual products from the order that were sent in that shipment.  Now the shipments that resulted from an order can be identified by selecting all Shipment rows where OrderNo matches the order you're viewing.

Your Invoice_Order table is appropriate if you may issue multiple invoices for a single order, else I'd just add OrderNo to the Invoice table as a foreign key.

Is that enough to help you see the general pattern?  You've done a good job separating your entities, if we can get you over the hump on modeling the relationships between the entities.


Empty frm files won't work because they have a specific syntax which is being checked by mysql.

[root@server test]# touch test_one.frm
[root@server test]# chown mysql test_one.frm
[root@server test]# mysql test

Although it is showing up in show table because it only checks for the existence of the file ...

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
....
| test_one       |
...
+----------------+

... any operations on it will fail.

mysql> show create table test_one;
ERROR 1033 (HY000): Incorrect information in file: './test/test_one.frm'

mysql> drop table test_one;
ERROR 1051 (42S02): Unknown table 'test.test_one'

The documentation says:

  Create a matching .frm file in some other database directory and copy
  it to the database directory where the orphan table is located.

Now, matching (same structure) is actually not mandatory if you only want to drop it but you need to have a valid frm file.

If you do this you should be able to drop the table afterwards:

mysql> create table test_recoverer (id int unsigned primary key);
Query OK, 0 rows affected (0.31 sec)

[root@server test]# cat test_recoverer.frm > test_one.frm

mysql> drop table test_one;
Query OK, 0 rows affected (0.11 sec)


Stored procedure in master slave replication

It seems what is happening is that your stored procedure deletes rows from products.dial, and these DELETE statements are simply replicated to the slave. It doesn't matter that the stored procedure itself is in a non-replicated schema.


A Word on the Performance of Different Types of Functions

Generally speaking, Scalar Functions and mTVFs (Multi-Statement Table Valued Functions) are a bit of a built in performance problem.  It's better, if you can, to use iTVFs (Inline Table Valued Functions) because their code is actually included in the execution plan (much like a VIEW but parameterized) instead of being executed separately.  Rather than replicate an entire article here, please see the following for some proof even when an iTVF is used as an iSF (Inline Scalar Function).  In many cases, Scalar functions can be 7 times slower than iTVFs even when the code is identical.  Here's the link, which you can now get to without having to sign up to be a member.  To summarize, if your function has the word "BEGIN" in it, it's NOT going to be a high performance iTVF.

How to Make Scalar UDFs Run Faster (SQL Spackle)

Test Data for SQL Server 2008 or Later:

Shifting gears to the original problem posted by @Reza and borrowing heavily from the code that @stefan provided to generate some sample rows of data, here's the test data we'll use.  I do this in a Temp Table so that we can easily drop the test table without having to worry about accidently dropping a real table.

--===== If the test table exists, drop it to make reruns in SSMS eaiser
     IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
   DROP TABLE #CSV
;
--===== Create the test table.
 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
        (
         ID         INT IDENTITY(1,1) --or whatever your PK is
        ,AString    VARCHAR(8000)
        )
        ;
--===== Insert some test data
 INSERT INTO #CSV
        (AString)
 SELECT AString
   FROM (
        VALUES   ('123,456,88789,null,null')
                ,('123,456,99789,1234,null')
                ,('123,456,00789,1234,null')
                ,('1,2222,77789,null,null')
                ,('11,222,88789,null,')
                ,('111,22,99789,,')
                ,('1111,2,00789,oooo,null')
        ) v (AString)
;

Test Data for SQL Server 2005 or Later:

If you're still using SQL Server 2005, don't despair.  All of the code will still work there.  We just need to change the way we generate the test data because the VALUES Clause couldn't handle the way we generated the data above until 2008.  Instead, you need to use a series of SELECT/UNION ALL statements.  There's virtually no performance difference between the two if you need a larger volume of data and the SELECT/UNION ALL method still works just fine through 2016.

Here's the test table generation code modified to work for 2005.

--===== If the test table exists, drop it to make reruns in SSMS eaiser
     IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
   DROP TABLE #CSV
;
--===== Create the test table.
 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
        (
         ID         INT IDENTITY(1,1) --or whatever your PK is
        ,AString    VARCHAR(8000)
        )
        ;
--===== Insert some test data
 INSERT INTO #CSV
        (AString)
 SELECT AString
   FROM (
         SELECT '123,456,88789,null,null' UNION ALL
         SELECT '123,456,99789,1234,null' UNION ALL
         SELECT '123,456,00789,1234,null' UNION ALL
         SELECT '1,2222,77789,null,null'  UNION ALL
         SELECT '11,222,88789,null,'      UNION ALL
         SELECT '111,22,99789,,'          UNION ALL
         SELECT '1111,2,00789,oooo,null'
        ) v (AString)
;

Picking a Splitter

First of all, SQL Server isn't the greatest tool to use for string manipulations.  It's been proven many times that a properly written SQLCLR will smoke just about any attempt at string splitting compared to pure T-SQL solutions.  The problems associated with SQLCLR solutions are 1) finding one that is properly written (properly returns the expected result set) and 2) it's sometimes difficult to convince the resident DBAs to allow SQLCLR to be used.  If you do find one that's properly written to return the expected results, it's definitely worth spending the time trying to convince the DBA because it will usually handle MAX data-types and cares little if you pass it VARCHAR or NVARCHAR data not to mention being faster than any T-SQL solution I've ever seen.

If you don't have to worry about using NVARCHAR and you don't have to worry about multi-character delimiters (or can change the delimiter "on the way in") and you don't have to worry about MAX datatypes (limited to 8K strings), then then DelimitedSplit8K function may be for you.  You can find the article for it along with the proper "high cardinality" testing for it at the following URL.

Tally OH! An Improved SQL 8K “CSV Splitter” Function

I'll also tell you that the high cardinality test data generation code in that article broke when SQL Server 2012 came out because of changes to how XML can be used to concatenate.  Wayne Sheffield did some separate testing to compare it to the new splitter function in 2016 and made a repair to the test data generation code at the following article.  Note that there are also 3 other splitters that are available in the article, 2 of which are only available in 2016.  He also tests against an SQLCLR and lists some of the functional differences (shortcomings in some cases) of each splitter.

Splitting Strings in SQL Server 2016

There's also an article by Eirikur Eiriksson that not only explores a serious performance improvement but also explores splitting "True CSVs" that can be included delimiters between "text qualifiers" (although at considerable expense of performance).  Here's the link to that article.

Reaping the benefits of the Window functions in T-SQL

All that, notwithstanding, even he demonstrates that an SQLCLR is a whole lot faster than anything you can do using only T-SQL.  I recommend that method instead of any T-SQL method but DO extensive functionality testing to ensure it's going to return what you expect in some of the "odd" cases.

Solving the Posted Problem

Ok.  Sorry for the long winded diversion about splitters.  Let's get back to the original problem.

We need to do two things:

Split the CSVs in such a fashion where we know which row each split
out element came from.
Reassemble the split out elements into separate columns all in the
same row for each row we split.

First, we need to create a splitter function.  Here's a modification of DelimitedSplit8K that I'm working on (well, not lately ;-) ) that will still support SQL Server 2005.  The big difference is using a binary collation for the extra performance.  This rendition isn't yet fully tested (I've personally been using it since Sep 2013) but, considering the simplicity of the changes and the fact that none of them actually change the functionality, I believe that you'll find it satisfactory for your needs.

CREATE FUNCTION [dbo].[DelimitedSplit8K]
/**********************************************************************************************************************
 Purpose:
 Given a string containing multiple elements separated by a single character delimiter and that single character
 delimiter, this function will split the string and return a table of the single elements (Item) and the element
 position within the string (ItemNumber).

 Notes:
 1. Performance of this function approaches that of a CLR.
 2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.

 Revision History:
 Note that this code is a modification of a well proven function created as a community effort and initially documented
 at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing
 tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to
 ensure that this function is suitable for whatever application you might use it for.
 --Jeff Moden, 01 Sep 2013
**********************************************************************************************************************/
--===== Define I/O parameters
        (@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000).
  WITH E1(N) AS (--==== Itzik Ben-Gan style of a cCTE (Cascading CTE) and
                     -- should not be confused with a much slower rCTE (Recursive CTE).
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                                      --10E+1 or 10 rows
       E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max
 cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting
                   FROM cteTally t
                  WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN
                ),
cteLen(N1,L1)AS ( --=== Return start position and length (for use in substring).
                     -- The ISNULL/NULLIF combo handles the length for the final of only element.
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter ,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO

Once that bad boy is in place, then problems like those in the original post become trivial to solve using either a PIVOT or a CROSS TAB.  I prefer the rather ancient CROSS TAB method because it's frequently faster (sometimes significantly) than an actual PIVOT.  Please see the following article on testing done there (is pretty old and should probably be updated for performance measurements, though it does explain how to use both methods).

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Here's the code that uses the function above and a CROSS TAB to solve the original problem posted on this thread.

--===== Do the split for each row and repivot to columns using a
     -- high performance CROSS TAB. It uses the function only once
     -- for each row, which is another advantage iTVFs have over
     -- Scalare Functions.
 SELECT  csv.ID
        ,Col1 = MAX(CASE WHEN ca.ItemNumber = 1 THEN Item ELSE '' END)
        ,Col2 = MAX(CASE WHEN ca.ItemNumber = 2 THEN Item ELSE '' END)
        ,Col3 = MAX(CASE WHEN ca.ItemNumber = 3 THEN Item ELSE '' END)
        ,Col4 = MAX(CASE WHEN ca.ItemNumber = 4 THEN Item ELSE '' END)
        ,Col5 = MAX(CASE WHEN ca.ItemNumber = 5 THEN Item ELSE '' END)
   FROM #CSV csv
  CROSS APPLY dbo.DelimitedSplit8K(csv.AString,',') ca
  GROUP BY csv.ID
;
GO

Here's the result set from the code above using the given test data.

If you have any questions or need clarification on any of this, please don't hesitate to ask.


Working under the assumption that you are dealing with some sort of code generation (mostly because this is the only time I have encountered this sort of issue), here is the general strategy I would employ:

But first,

All initial code should follow strict formatting.  TSQL is on the best TSQL parser, so specific limitations need to be followed to simply the process.  The limitations I am assuming are:

The views do not have leading comments (if you want to tweak my
example to incorporate them, I strongly suggest using either single
line comments, as they are easier to work with).
The create view will have "CREATE VIEW .view" as the first
line, follow by "AS" on the second line, and the remaining code on
the following lines (no extra spaces, tabs or CRLF)
Unioned statements will be seperated by "UNION ALL", on it's own
line.
All the views that can be regenerated will be under the schema 'Regenerate'
All table references will be in the form of "[schema].[table]"

With that in mind,

I suggest that you add a drop table trigger.  Inside that tirgger I suggest that you get all views within the appropriate schema that have a reference to the table that was just dropped.  I would fetch the definition for each and split out the view body based using "UNION ALL as the deliminator, using XML.  I'd then remove all statements that have reference to the removed table.  I would recombine the statements into a Create Views, then execute the whole set as a block of dynamic SQL.

Here is some sample code that follows that approach.  Note, I didn't test the trigger, nor the final generated code (you get what you pay for [grin], so it may need some tweaking), but I simulate a trigger call and did a quick inspections of the resulting SQL.

CREATE TRIGGER db_Drop_Table
ON db
FOR DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    DECLARE @Removed NVARCHAR(MAX) = CONCAT(QUOTENAME(@Schema), QUOTENAME(@Table))

    -- These may need to be tweak to accomdate how the underlying views are written
    DECLARE @UnionDelim NVARCHAR(100) = 'UNION ALL'
        , @AsDelim NVARCHAR(100) = CONCAT(CHAR(13), 'AS', CHAR(13))
        , @CreateView NVARCHAR(100) = 'CREATE VIEW '
        , @RegenerateSQL NVARCHAR(MAX);

    WITH ViewDefinitions AS (
        SELECT
          OBJECT_DEFINITION(object_id) as ViewText
        FROM
     sys.views as v
        WHERE OBJECT_SCHEMA_NAME(object_id) = 'Regenerate'
            AND OBJECT_DEFINITION(object_id) LIKE CONCAT('%', @UnionDelim ,'%')
     )
    , NormalReturnsViewDef AS (
        SELECT REPLACE(ViewText, CONCAT(CHAR(13), CHAR(10)), CHAR(13)) ViewText FROM ViewDefinitions
    )
    , ViewParts AS (
        SELECT
            SUBSTRING(ViewText, CHARINDEX(@CreateView, ViewText)+ LEN(@CreateView) + 1,  CHARINDEX(@AsDelim, ViewText) - (CHARINDEX(@CreateView, ViewText)+ LEN(@CreateView)))  AS ObjectName
            , LEFT(ViewText,  CHARINDEX(@AsDelim, ViewText) + LEN(@AsDelim) - 1)  AS CreateText
            , RIGHT(ViewText, LEN(ViewText) - (CHARINDEX(@AsDelim, ViewText) + LEN(@AsDelim) - 1)) AS ViewBody
        FROM NormalReturnsViewDef
    )
    , XmlViewBody AS (
    SELECT
        ObjectName
        , CreateText
        ,  CAST(CONCAT('<def><statement><![CDATA[', REPLACE(ViewBody, @UnionDelim, ']]></statement><statement><![CDATA['), ']]></statement></def>') AS XML) as ViewBodyXml
    FROM ViewParts

    )
    , Statements AS (
         SELECT
            ObjectName
            , CreateText
            , XmlStatementText.value('.', 'NVARCHAR(MAX)') StatementText
         FROM XmlViewBody
            CROSS APPLY ViewBodyXml.nodes('/def/statement') Statements(XmlStatementText)
    )
    , StatementsFiltered AS (
        SELECT *
        FROM Statements
        WHERE StatementText NOT Like CONCAT('%',  @Removed, '%')
    )
    , RebuiltStatements AS (
        SELECT
            CONCAT(
                'DROP VIEW IF EXISTS ', ObjectName, ';', CHAR(13),
                CreateText, CHAR(13),
                STRING_AGG(StatementText, @UnionDelim)

            ) as RecreateView
        FROM StatementsFiltered
        GROUP BY ObjectName, CreateText
    )
    SELECT @RegenerateSQL = STRING_AGG(RecreateView, CONCAT(CHAR(13), CHAR(13))) FROM RebuiltStatements

    EXEC (@RegenerateSQL)

END

Notes:  The query optimizer will not produce a nice plan... however this should not be running often, so that is less of a concern.  You may be able to reduce the number of CTEs, but I find it easier to follow the logic. This should work for 2008 R2 if you replace STRING_AGG with a UDF.


The easiest is to wrap your query in a derived table or CTE and then do SUM :

SELECT sum(ProfitRecycle) AS SumProfitRecyxle
FROM
    (
    --
    -- your query here
    --
    ) AS dv ;


SQLite JOIN ... USING - rowid works, but then it doesn't

It seems that such query works fine for explicit primary keys.
I run this code:

CREATE TABLE Demographics (rowid int PRIMARY KEY);
CREATE TABLE MeasuresofBirthAndDeath (rowid int PRIMARY KEY);

insert into Demographics VALUES (1);
insert into MeasuresofBirthAndDeath VALUES (1);
insert into MeasuresofBirthAndDeath VALUES (2);

SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid);

And get correct result: 1

In case of using implicit rowid it really doesn't work. Let's see code:

CREATE TABLE Demographics (name text);
CREATE TABLE MeasuresofBirthAndDeath (name text);

insert into Demographics VALUES ('demo');
insert into MeasuresofBirthAndDeath VALUES ('birth');
insert into MeasuresofBirthAndDeath VALUES ('death');

SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid);
Error: cannot join using column rowid - column not present in both tables

We get error because rowid is not real column (https://www.sqlite.org/lang_createtable.html#rowid):

  Except for WITHOUT ROWID tables, all rows within SQLite tables have a
  64-bit signed integer key that uniquely identifies the row within its
  table. This integer is usually called the "rowid". The rowid value can
  be accessed using one of the special case-independent names "rowid",
  "oid", or "rowid" in place of a column name. If a table contains a
  user defined column named "rowid", "oid" or "rowid", then that name
  always refers the explicitly declared column and cannot be used to
  retrieve the integer rowid value.

In first select we explicit add condition to join and it works

ON Demographics.rowid = MeasuresofBirthAndDeath.rowid

But in second select there are no column in both table to apply USING to it.

You can ALTER your tables and create INTEGER PRIMARY KEY columns which would be alias for rowid and use JOIN USING on these new column:

  if a rowid table has a primary key that consists of a single column
  and the declared type of that column is "INTEGER" in any mixture of
  upper and lower case, then the column becomes an alias for the rowid.
  Such a column is usually referred to as an "integer primary key". A
  PRIMARY KEY column only becomes an integer primary key if the declared
  type name is exactly "INTEGER". Other integer type names like "INT" or
  "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
  key column to behave as an ordinary table column with integer affinity
  and a unique index, not as an alias for the rowid.


MySQL: Why queries with subqueries are much faster than single query?

I did an upgrade to percona mysql 5.6 and it solves a problem. Both type of queries runs equally. Fortunately - equally fast.


Slow query log of wp_options WHERE autoload = 'yes'

It is impossible that a query like that SELECT ... FROM table WHERE column = value
examines 1663532 rows and sends to the SQL layer 1425838 when the table has only 650 rows. Worst case scenario, it would do a full table scan, examining 650 rows and sending all of them if all of them have autoload = yes.

So:

The query log is incorrect (seems to be truncated, maybe it corresponds to another query?)
The table had that number of rows at some time in the past, on a different session, or as a temporary table over the real query (it was locked for 2 seconds, so probably there is some write-like activity there).

Please check the result of that select with EXPLAIN and with its handler operations.

Also you can check on the slow log or binary log to detect some ongoing write activity at that time.


Grant access to only one key in azure keyvault

Permissions for keys are at the vault level. In order to get the granularity that you want, you would have to create another key vault.

From the documentation (emphasis added):

  Key vault access policies grant permissions to keys, secrets and certificates separately. For example, you can give a user access to only keys, but no permissions for secrets. However, permissions to access keys or secrets or certificates are at the vault level. In other words, key vault access policy does not support object level permissions. You can use Azure portal, the Azure CLI tools, PowerShell, or the key vault Management REST APIs to set access policies for a key vault.


SQL Server 2008 query planner failing after index drop & recreate

Well, the issue is now resolved:

While it would seem logical to use filtered indexes (NOT NULL), to reduce database size and as so many sources on the web say, increase performance, the reality it seems is something else entirely.

In layman's terms, SQL Server query planner resolves even your basic inner joins without making any assumptions as to the content of the columns. Even though NULL values do not form a join, they must be included in the column index in order for query planner to use it, unless otherwise specified with predicates such as WHERE joinCol_ID IS NOT NULL. Basically, SQL Server does not use filtered indexes for joins at all, unless the queries themselves are modified to account for the filter value. Instead, it will create new statistics on these columns and / or use a clustered index scan or other indexes including the column, whichever it deems most effective. Using filtered indexes on foreign keys is therefore an absolutely horrid idea.

We still have no idea how months worth of testing this in multiple other environments never produced the same results outside of this one, single DB, but this is the way it's supposed to work. Apparently something that as far as we know is not related to cache, statistics or configurations, caused the production DB to behave differently and correctly detect and use the filtered indexes, while all of the testing environments simply used the old indexes (seeing as the indexes were dropped and recreated with the same name, this seems a valid theory even if there is no real proof).

So the lesson of the story: The web is filled with examples of how underused filtered indexes are, how awesome they can be. But this serious downside never popped up except as a nagging thought in the back of my head saying "if these are so great, then why aren't NULL values filtered out of indexes by default, since they only take up space and only serve a purpose in special circumstances"? Well, now I know why. :)


tempdb on raid1 isolated or raid10 shared with high write db

I found these articles helpful:

Why RAID10 (0+1) superiority is a myth (specifically versus RAID1).
And this is an additional guide on raid level comparison.

Further reading suggests that the benefits of using RAID1 for log files (assuming isolated from data files) is lost when more than 1 log file exists on that RAID1 array. This is due to the sequential nature of the transaction log writes to disk. The sequential write benefit is lost when multiple log files are accesses on the same RAID1 array due to the random nature of access on a spinning disk. This would suggest that RAID10 is the better choice in a multiple DB environment unless you have the disks to isolate each log file.

These stats below sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1.  Basing much of this on RAID1s ability to maintain good WRITE speed.

TEMPDB is clearly under more stress than I have realised.

These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.

TOTAL IO:

db.tempdb.mdf = 144,747,290,352

db.2.mdf =         100,482,243,080

db.2.ldf =           2,571,065,773

db.s.mdf =         1,702,508,040

db.s.ldf =           223,032,162

TOTAL READS:

DB.2.mdf =         84,851,614,280.00

db.tempdb.mdf = 72,271,813,552.00

db.s.mdf =         1,691,504,864.00

db.2.LDF=          93,822,304.00

TOTAL WRITES:

db.tempdb.mdf = 72,475,476,800

db.2.mdf =         15,630,628,800

db.2.ldf =           2,477,243,469

db.tempdb.ldf =   222,946,079

One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.

These are helpful links that explain TEMPDB usage, what it does and how it may effect my apps:

http://bradmcgehee.com/wp-content/uploads/presentations/Optimizing%20tempdb%20Performance_SQLCONN.pdf
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx


As with most things, it depends.

First of all, if you're not 100% certain that the data will always be valid JSON, use a normal text type instead.

If the data is indeed always JSON, I would use a JSON type if for no other reason that as documentation that the column contains JSON data.

If there will ever be any chance of requiring indexing of data, go for the JSONB datatype. If insert performance is critical, choose JSON (or even a text type) instead. The JSON datatype stores the JSON data as-is, while the JSONB datatype removes empty nodes and whitespace and even duplicate keys (the last one is kept). This means that JSONB is storing less data, but requires minor reconstruction on retrieval.

For all other cases, I would go with JSONB as the "best" generic JSON type in PostgreSQL at present, and this is also the recommendation in the documentation. See http://www.postgresql.org/docs/9.4/static/datatype-json.html


How can I create a stored procedure of inheritance tables in SQL Server

If you're trying to mimic inheritance in MS SQL Server, you'd technically create three procedures:

RegularCustRegistration: insert a regular customer
BusinessCustRegistration: insert a business customer
CustRegistration: insert common data to Customer

NOTE: Since the ContactID in Regular_Customer and Business_Customer must match the one in Customer, you need to remove the IDENTITY attribute on those two tables. All ContactID values must be generated from Customer.

The code for CustRegistration and RegularCustRegistration would look something like this:

CREATE PROCEDURE CustRegistration
      ( @ContactID int OUTPUT
       ,@CustomerTypeId int
       ,@FirstName varchar(25)
       ,@LastName varchar(25)
       ,@DOB date
       ,@Telephone varchar(18)
       ,@Notes varchar(250)
       ,@AddDate date
      )
AS
BEGIN
    DECLARE @CustOut TABLE (ContactID int);

    INSERT INTO Customers (CustomerTypeId, FirstName, LastName, DOB, Telephone, Notes, AddDate)
    OUTPUT INSERTED.ContactID INTO @CustOut
    VALUES (@CustomerTypeId, @FirstName, @LastName, @DOB, @Telephone, @Notes, @AddDate);

    SELECT TOP (1) @ContactID = ContactID
      FROM @CustOut;
END;

CREATE PROCEDURE RegularCustRegistration
      ( @FirstName varchar(25)
       ,@LastName varchar(25)
       ,@DOB date
       ,@Telephone varchar(18) = NULL
       ,@Notes varchar(250) = NULL
       ,@AddDate date
       ,@Specification varchar(45) = NULL
       ,@Date_Joined date = NULL
      )
AS
BEGIN
    DECLARE @ContactID int;
    DECLARE @CustomerTypeID int = 1; -- regular

    EXECUTE CustRegistration
            @ContactID OUTPUT
           ,@CustomerTypeID
           ,@FirstName
           ,@LastName
           ,@DOB
           ,@Telephone
           ,@Notes
           ,@AddDate
    ;

    INSERT INTO Regular_Customer (ContactID, Specification, Date_Joined)
    VALUES (@ContactID, @Specification, @Date_Joined);
END;

See this dbfiddle.uk link to see this work. It also has corrected versions of your table creation statements (IDENTITY removed from Regular_Customer and Business_Customer; PRIMARY KEY has to come after IDENTITY(1,1); need to use SET IDENTITY_INSERT <table> ON to insert explicit values to an IDENTITY column).

Since we need the identity value from the Customers table to create our row in Regular_Customer, we use the OUTPUT clause of the INSERT statement to capture the ID created by the INSERT, and mark the @ContactID parameter of the CustRegistration procedure as an OUTPUT parameter, so the value put in that parameter is passed back to the specified variable in the calling procedure.

You should be able to create BusinessCustRegistration based on this.


Window function not working on non selected rows

LEAD() is calculated after the WHERE clause has limited the result to one row (I'm assuming that (PNumber) is unique). That's why you always get NULL for @NextPartitionRowNum.

You could use your select, only if you are sure that there are no gaps in the PNumber values:

SELECT @NextPartitionRowNum = MaxRows
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1 ;

Alternatively, you could use this (gaps or not, doesn't affect it):

SELECT @vcPartitionFunction    = p.PFName,
    @vcBoundaryValue        = p.Value,
    @NextPartitionRowNum    = n.MaxRows
FROM #MaxPartitionRows AS p
  OUTER APPLY
    ( SELECT TOP (1) n.MaxRows
      FROM #MaxPartitionRows AS n
      WHERE n.PNumber > p.PNumber
      ORDER BY n.PNumber
    ) AS n
WHERE p.PNumber = @iMinPartitionNumberToSwitchOut ;

If you prefer window functions, this would work, too. First calculate LEAD() over the entire table, then restrict with WHERE:

WITH cte AS
  ( SELECT PFName, Value, PNumber,
           NextMaxRows = LEAD(MaxRows) OVER (ORDER BY PNumber)
    FROM #MaxPartitionRows
  )
SELECT @vcPartitionFunction    = PFName,
       @vcBoundaryValue        = Value,
       @NextPartitionRowNum    = NextMaxRows
FROM cte
WHERE PNumber = @iMinPartitionNumberToSwitchOut ;


Msg 39011 SQL Server was unable to communicate with the LaunchPad service

In order to move TempDB to a disk with more space, I had to restart the server.
This stopped the SQL Server as well as SQL Agent, and the new "SQL Server Launchpad".

It was easy to restart SQL Server and SQL Agent.
To restart LaunchPad, I needed to go to

Administrative Tools -> Services
and find "SQL Server LaunchPad (MSSQLSERVER)"

right-click it and select "Start"
and my scripts run again.


How to create a conditional index in MySQL?

MySQL currently doesn't support conditional indexes.

To achive what you are asking (not that you should do it ;) ) you can start creating an auxiliary table:

CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

Then you add three triggers in the main table:

delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

  We need delimiter // because we want to use ; inside the triggers.

That way, the auxiliary table will contain exactly the IDs corresponding to the main table rows that contain the string "ACTIVE", being updated by the triggers.

To use that on a select, you can use the usual join:

SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

If the main table already contains data, or in case you make some external operation that changes data in an unusual way (E.G.: outside MySQL), you can fix the auxiliary table with this:

INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

About the performance, probably you'll have slower inserts, updates and deletes. This can make some sense only if you really deal with few cases where the condition desired is positive. Even that way, probably only testing you can see if the space saved really justifies this aproach (and if you are really saving any space at all).


I think you do not need the last column in your GROUP BY clause:

SELECT     Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode,
           SUM(Asset.Value) AS HHValue,
           SUM(CASE WHEN financialinst.name = 'National Financial Services' THEN Asset.Value ELSE 0 END) AS NationalValue,
           SUM(CASE WHEN financialinst.name <> 'National Financial Services' THEN Asset.Value ELSE 0 END) AS DirectValue
FROM       PlanFirmSpecCode INNER JOIN
           FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
           Household INNER JOIN
           Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
           Asset ON Account.AccountID = Asset.AccountID INNER JOIN
           FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER JOIN
           FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE      (Account.Closed <> 'Y') AND (Asset.Value > '0') and (Account.Managed <> 'N')
GROUP BY   Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode
Order by   household.Name


MariaDB optimizing geo IP lookup with Maxmind GeoLite2 database

OR is inefficient.  It can be turned into UNION.  An example:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or
Why use INET6_ATON on IPV4 values?
Finding something in a range is poorly optimized, primarily because the Optimizer does not know whether there are overlapping ranges.  One solution is given here:  http://mysql.rjweb.org/doc.php/ipranges


How to change the FROM clause of multiple views at once

I am not sure if I fully understood your question however if it is about changing source table of selected views or all views, you can do the same using SSMS(SQL Server Management Studio).

You can generate scripts from database as below:

In the screen, either you can select all view or select the one that you need as below:

You can save them to a new query window:

Alternatively you can save this as a SQL file. Now, using simple text editor, you can do replace all from existing table name to new table name and either you can run them directly in the new query window or can execute them from SQL file. This file can be handy whenever there is change in table name, you can just find and replace table and would work for you.

One great point made by Aaron Bertrand, if you create another view(only one) wherein you just include the definition as :

create view NotChangingName
as
Select * from changingName

You don't need to change each view, just one change will be sufficient.

Hope this helps.


How to add the Debug button to SSMS v18?

Debugger is deprecated in SSMS18 as @LowlyDBA said in his answer.

For those who look for an alternative, you can use SQL Server Data Tools (SSDT)/Visual Studio to debug a query.

Connect to a SQL server (in Tools menu)
Open a new query (Server explorer, right click, new query)
Type in your query
Execute with Debugger

From the SQL menu

From the Drop down list next to the execute icon in the query window

From the context menu in the query wondow


Cleanse your data before storing it.  Otherwise, INDEXes may be useless.

I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless.

This SELECT would run a lot faster because of the index:

SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG
    FROM entry_log
    WHERE card_no = '2948'

OK, you don't like the Cleanse advice.  And your card_no is always an 8-digit number.  In that case, this would allow the use of the index:

WHERE card_no = RIGHT(CONCAT('00000000', '2948'), 8)

Please provide SHOW CREATE TABLE so we won't have to guess any more.

Edit

If a card_no is just a number, but with some extra zeros in front, it could be stored more compactly as an INT, BIGINT, or DECIMAL(...).  Then do the LPAD to prefix it with the needed zeros when fetching it.

SMALLINT (2 bytes) can handle 4 digits, as with your example.
INT (4 bytes) can handle 9 digits.
BIGINT (8 bytes) can handle 18 digits.
DECIMAL(32,0) (15 bytes) can handle 32 digits.  A smaller number could be used if you never need a full 32.

You would need to pick whatever size will handle the largest card_no you can have.

VARCHAR(50) takes 2 + N bytes, where N is the number of digits.  If card_no varies a lot is length, this might still be the best bet.

Edit2

If you have a lot of rows for a card_no, it will run a lot faster if you do these:

Combine date (date) and last_entry (time) into a single column of DATETIME datatype.  I'll call the column last_log.
SELECT MAX(last_log) AS LAST_LOG
INDEX(card, last_log)

Rationale:  The INDEX can do essentially all the work of the MAX -- this will run a lot faster.

(Note:  This comment about last_log is independent of the discussion on leading zeros for card_no.  Be sure to formulate the WHERE clause as WHERE card_no = ... in order to make use of the index.)

Edit 3

In the newest versions of MySQL 8.0 and MariaDB 10, you can index "virtual" columns.


InnoDB errors and crashes with MyISAM database?

I have some annoying news for you. MySQL 5.7 has InnoDB enabled permanently. It's in the Docs:

  Important

  InnoDB cannot be disabled. The --skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth).

The number of InnoDB tables have increase from version to version.

I mentioned this Apr 04, 2018 in my old post Restoring XtraBackup from MySQL 5.6 to MySQL 5.7

  MySQL SCHEMA FILES

  The number of InnoDB tables in the MySQL schema also changes between major releases.

  Back on Dec 09, 2017, I answered ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option and discussed when InnoDB tables starting being used in the MySQL schema.

  In MySQL 5.6, it was 5 tables. In MySQL 5.7, it went to 19. In MySQL 8.0, all 31 tables are InnoDB

From my old post, I stated that MySQL 5.7 has 19 InnoDB Tables in the mysql schema.

In order to hunt down your InnoDB properly, run this

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

The 19 tables from the mysql schema should appear. If you have additional InnoDB tables, you will have to get rid of or rebuild those tables. If the 19 tables do not show up from this query, you must fix this.

I just noticed you said in your question : Since it moved to mysql 5.7.27 there are InnoDB related errors and even crashes

If you did not upgrade the system tables correctly, mysqld is probably looking for the mysql schema tables to be InnoDB.

Please read MySQL 5.7 Docs on mysql_upgrade and look at --upgrade-system-tables. It should convert the mysql schema tables from MyISAM to InnoDB.

Please do this in a test environment first beforehand.

UPDATE 2019-09-20 18:09 EDT

After a nice chat session, it turns out that there is a lot of temp tables being generated. All the temp tables were using InnoDB, consuming 482MB of the InnoDB Buffer pool. The InnoDB buffer Pool was at default (128M). Increase above 512M solved it.

I also recommended switching every to InnoDB.


Schema to make a Payment History/Viewing History

Yes, logs tend to become big.  But that is not necessarily a problem.  (More in a minute.)

I would be tempted to treat View History as if it were another page, and log it in the same table.

The log does not really need an id.  PRIMARY KEY(user_id, view_id, timestamp) (all the columns) serves two purposes:  Having a PK; and facilitating View History because the PK starts with user_id.  One drawback:  If the user views the same page twice in one second, the INSERT would get "duplicate key" error.  So I would do INSERT IGNORE to avoid the error (and lose the record of the extra page load).

The best way to handle purging old log entries (should you decide to do such), is to PARTITION BY RANGE using the timestamp.  The PK is already prepped for that.  If you want a 30 day retention period, use 32 partitions and do DROP PARTITION nightly.  If you want a bigger retention, then use weekly partitions.  The details of "why", plus sample code for doing the DROP and REORGANIZE are in my Partition Maintenance blog.

Assuming that you use InnoDB for the log, it would be beneficial to have enough RAM so you can set innodb_buffer_pool_size bigger than the size of one partition.

(All InnoDB and MyISAM indexes are BTrees, so the comment about Heap is irrelevant.)


change optimizer's plan on using indexes

Your two queries are different, and have different execution plans. While they may both use a particular index, that index was chosen independently. The fundamental tasks the queries are doing are different (one has one table, one uses two), and thus they cannot possibly use the same plan.

Once the optimizer has identified a plan for a given query, it will reuse that plan as you indicate. However, the queries have to be the same. Even changing a hard-coded value will often cause a new plan to be generated (see Parameters and Execution Plan Reuse in the product documentation).

As a general rule, plans that are good sometimes but bad others are normally due to queries with variables in them, or queries in procedures and functions that use passed-in parameters in queries.

The best way to help the optimizer make the best choices is to ensure that your statistics are up-to-date. When the optimizer makes good estimates as to how many rows will be found, its plans are more likely to be good.

There are relatively rare cases where the optimizer will refuse to pick the best possible query; in those rare instances, you can use various query hints to (for example) force the use of a particular index.

That said, hints should generally be used very sparingly, especially in recurring code (scripts run regularly, applications, etc.). Using a query hint like this will prevent the optimizer from considering certain options, and the odds are good that what's the best option today may not be the best in a month or a year, as your data changes. Your "perfectly planned" query today may become a crippling bottleneck next month, next week, or even tomorrow. Instead of query hints, you can also use plan guides (I know no more about those than that they exist).

Most often, the best solution is to tune the query so a better index will be chosen, or so less work needs to be done. This may sometimes feel like finding a way to "trick" the optimizer into doing what you want. However, it's really a matter of explaining to the optimizer what you want to do better. If you're speaking a foreign language, and you're asking to get to a bathroom and use the toilet, but what you actually say is "I need to use your bathtub," whose fault is it if the person you're talking to goes to get you a towel instead of taking you straight to the nearest toilet?


Combining two similar queries into one (PostgreSQL)

Use the CASE function:

SELECT c.id,
       CASE WHEN COALESCE(bv.affidavit, '') = '' THEN False
            ELSE True
       END
FROM   component c, base_vee bv
WHERE  c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
AND    c.id = bv.component_id;


Postgres security for functions with "security definer"

pg_temp is always in the search path.  If you don't add it to search_path parameter explicitly, it will behave as if it were there at the beginning of the setting.  That means the user could create tables which masked the ones the function was supposed to operate on.


How to split a list of number into ranges with a fixed interval with SQL?

select product, count(*) as frequency,
       50 * floor(price / 50) as range_start,
       50 * (floor(price / 50) + 1) as range_end
group by product, 50 * floor(price / 50), 50 * (floor(price / 50) + 1)
order by product, min(price)


You could create a stored procedure and use Dynamic SQL and the INFORMATION_SCHEMA tables to generate a SELECT statement dynamically.

Stored Procedure:

CREATE PROCEDURE spGenerateSelect (@SchemaName NVARCHAR(255),
    @TableName NVARCHAR(255),
    @ExcludeColumns NVARCHAR(MAX)
)
AS
BEGIN

    SELECT @ExcludeColumns = '''' + REPLACE(@ExcludeColumns, ', ', ''', ''') + ''''

    DECLARE @SqlCmd NVARCHAR(MAX),
        @ParamDefinition NVARCHAR(MAX),
        @ColumnsOut NVARCHAR(MAX)

    SET @ParamDefinition = '@SchemaName NVARCHAR(255), @TableName NVARCHAR(255), @Columns NVARCHAR(MAX) OUTPUT'

    SET @SqlCmd = 'SELECT @Columns = COALESCE(@Columns + '', '', '''') + QUOTENAME(c.COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
    WHERE t.TABLE_SCHEMA = @SchemaName AND t.TABLE_NAME = @TableName AND c.COLUMN_NAME NOT IN (' + @ExcludeColumns + ');'

    EXEC sp_executesql @SqlCmd, @ParamDefinition, @SchemaName = @SchemaName, @TableName = @TableName, @Columns = @ColumnsOut OUTPUT

    SELECT 'SELECT ' + @ColumnsOut + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
END

Execution:

EXEC spGenerateSelect @SchemaName = 'dbo', @TableName = 'SubjectPeriod', @ExcludeColumns = 'SubjectID, TeacherID'

This outputs a SELECT statement for all columns, excluding the ones supplied in @ExcludeColumns, which is a comma-separated list of column names to exclude.


Can't connect to PostgreSQL instance using Python

Since you did not specify the host argument of che connect call, you are connecting via a unix named pipe instead of TCP/IP, so the error message might be slightly incorrect. BTW, the database should have opened a pipe named /var/run/postgresql/.s.PGSQL.5432. Can you check if it exists using command ls -la /var/run/postgresql/.s*? You might find there a pipe with a different number, or you might find and empty directory. In the first case, just add port=number (using the right number) to the connect call; in the latter case, find the correct directory for your postgresql and add host=path (using the right path) to the connect call.

In order to find the correct path, connect via psql and give command \conninfo. In will display the correct path, as in:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

P.S. It might also be a problem of the syntax you used. It should be psycopg2.connect(dbname="tournament")


Monitor volume of data returned to the client application

Warning: this is more of an soft-skill, wholistic approach answer than a technically oriented one.

I'm not aware of anything that will make quick work of this problem - you did say it was a problem, right? I'm not saying don't  pursue this, but if everything is basically working it may not be worth dying on this hill. That is often why developers are had pressed to care at all, in my experience. However, if there are actual problems and you've narrowed down this is the cause...

It should be pretty easy to find out the queries that are your worst offenders (by time, execution frequency, etc) based on your workload. Because if there is a problem, it should be obvious.

Then, just look at what they do. Are they doing a ton of SELECT * on wide tables? Did someone decide to store a ton of XML into an NVARCHAR(MAX) column? Run them a few times and use Client Statistics in SSMS to view the actual size of the data returned. These are all well researched and documented areas of best practice that you can communicate are necessary to ensure smooth operation of the database.

But, it may not even be that the data size is hyuuge. It could be just how the client is handling the data (not uncommonly RBAR is at play in this scenario). In these cases, I try to convince the developers to give me read access to their repositories so I can tunnel in and try to do some light reverse-engineering of the code to understand myself what is going on. I haven't had much push back from this since it is an easy ask and doesn't require much of them.

Depending on your language experience, this may be easy or hard, but it definitely can't hurt to start to understand how the ORM and/or code is traversing through the data it receives. From there, if there are any big flashing red lights, you can probably find more resources or patterns to describe the ideal way to handle the data.

But until you can narrow down the problem more clearly, it isn't surprising no one wants to volunteer their time to help on a wild goose chase.

It can be hard to get development teams to take the concerns of a DBA seriously, so the more you understand the better. The blame game between developers and DBAs can get tense fast and usually doesn't end up benefiting anyone, so just make sure you're not accidentally making them feel attacked or blamed as the source of a problem.

Finally - there are other things that can cause this, but they are very rarely the cause in 2018, so be sure to rule those out just in case.


Create copy of Azure SQL Database using SQL Server Management Studio

Try reviewing DMVs sys.dm_database_copies and sys.dm_operations_status which are Azure SQL Database-only, and meant for reviewing progress on these type of operations eg

SELECT *
FROM sys.dm_operation_status

SELECT *
FROM sys.dm_database_copies

Review the state_desc and error_desc columns.  Post the output here if required.


Community wiki answer:

In general, nothing exists built in that automagically keeps count like this. Some people tend to use indexes on a table as last access but that's sketchy at best.

The honestly best thing you can do is create an audit, trace, or extended event session and garner the object usage from that. This will also require extra work on things such as stored procedures where the procedure is executed and then may touch multiple or no tables, views, etc., based on logical flow.


Postgres serializable snapshot isolation for jsonb

You can't do this with JSONB.  To get this level of separation, you would have to have a parent and child table, where the child table would be something like:

(parent_id whatever, key text, value text, primary key (parent_id, key))

If your JSON can be nested and not just a flat key-value store, then it is ambiguous at what level you want the locking to occur.  If it always occurs at the first level, then this would still work but the value column would be of type JSONB.

If the locking occurs deeper, then that would really be a mess.  What if someone wants to change a value 4 levels down, and someone else wants to remove the entire sub-tree from the 2nd level?

EAV is the design which fits your desired concurrency/locking model. Obviously there are other considerations than that, but I wouldn't know what those are.  You could always use a single row, block until the other guy commits, then "manually" re-check that the value you want to update wasn't changed by the other guy.


It seems you are in RDBMS instance
to check use the following query:
select value from v$parameter where upper(name) = 'INSTANCE_TYPE';

or
show parameters instance_type

If you get RDBMS value, go to operational system shell and check the SID of ASM instance in /etc/oratab
cat /etc/oratab

After cat the file, you will get something like it:
+ASM:/u01/app/19.0.0/grid:N

The rows are delimited by :. The first field is the SID (in my case +ASM), the second field is the GRID_HOME (also ORACLE_HOME)
You can use oraenv to set the variables needed to access the ASM instance or you can set the variables manually with export command.
With oraenv:
. oraenv

after issuing the . oraenv command, type your SID
or export the variables manually
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/19.0.0/grid
export PATH=$ORACLE_HOME/bin:$PATH

NOTE: to create asm diskgroup you need access the instance with sysasm role
sqlplus / as sysasm


Can SHOW TABLE STATUS query performance on MySQL be improved ? - Amazon RDS

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

  When this variable is enabled (which is the default, as before the
  variable was created), InnoDB updates statistics during metadata
  statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing
  the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are
  similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does
  not update statistics during these operations. Disabling this variable
  can improve access speed for schemas that have a large number of
  tables or indexes. It can also improve the stability of execution
  plans for queries that involve InnoDB tables.

Since you are using Amazon RDS, ask your DB Administrators to disable it. If your DBs are in a Shared Resource, my condolences...

UPDATE 2012-07-25 14:45 EDT

If you can download the RDS CLI, you can adjust innodb_stats_on_metadata in a customized DB Parameter Group. You will have to do the following:

mysqldump data out of RDS Server
destroy DB Server Instance
create DB Server Instance using your custom DB Parameter Group adjusted for innodb_stats_on_metadata
reimport the mysqldump into new DB Instance

Since Amazon RDS does not give away SUPER privilege, that's the only way to change a GLOBAL static option.

UPDATE 2012-07-27 16:34 EDT

Without SUPER Privilege, you can modify a DB Parameter Group for a given MySQL RDS Instance, and launch a reboot with one of the following:

rds-reboot-db-instance <DBInstanceName>
Click Reboot Button in the RDS Management Console


psql's \set command unable to save json file's content in a variable

There here-doc feature in bash treats backticks as command invocations, so it executes the cat /tmp/jsonfile.json, which wrongfully injects the json contents into the psql commands:

  If word is unquoted, all lines of the here-document are subjected to
  parameter expansion, command substitution, and arithmetic expansion,
  the character sequence \newline is ignored, and ‘\’ must be used to
  quote the characters ‘\’, ‘$’, and ‘`’.

You may quote the EOF word to remove all expansions, as in :

psql [...] << 'EOF'
... psql script...
\set var `cat /tmp/jsonfile.json`
...
EOF

That's the safest method when you don't need to inject anything from bash into the here-doc script.

Or, if any kind expansion in the here-doc string is needed, the above-mentioned characters must be quoted with backslash, as in:

myfile="/tmp/jsonfile.json"

psql [...] << EOF
... psql script...
\\set var \`cat $myfile\`
...
EOF


Extract MIN and MAX values related to datetime values on Postgres 9+

There are various ways to do it. An index on columns used in order/filter/join (user_id and grade_date + grade) will play an important role on a large table.
Performances must be tested with real data and table/index design.

Using a window function (ROW_NUMBER()):

SELECT f.user_id, f.grade, f.grade_date, l.grade, l.grade_date
FROM (
    SELECT user_id, grade, grade_date
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY grade_date) as n
    FROM data
) f
INNER JOIN (
    SELECT user_id, grade, grade_date
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY grade_date DESC) as n
    FROM data
) l
ON f.user_id = l.user_id
    AND f.n = 1 AND l.n = 1;

ROW_NUMBER gives each row a number from 1 to N by grade_date up and down and only the first one of each is kept (n=1).

Using subqueries:

SELECT  user_id
    , ( SELECT grade FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date LIMIT 1
    )
    , ( SELECT grade_date FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date LIMIT 1
    )
    , ( SELECT grade FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date DESC LIMIT 1
    )
    , ( SELECT grade_date FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date DESC LIMIT 1
    )
FROM (SELECT DISTINCT user_id FROM data) d
;

Each subquery only keep the first row and returns it.

Using MIN and MAX:

SELECT d.user_id, mn.grade, mn.grade_date, mx.grade, mx.grade_date
FROM (
    SELECT user_id, MIN(grade_date) as min_grade_date, MAX(grade_date) as max_grade_date
    FROM data
    GROUP BY user_id
) d
INNER JOIN data mn
    ON mn.grade_date = d.min_grade_date AND mn.user_id = d.user_id
INNER JOIN data mx
    ON mx.grade_date = d.max_grade_date AND mx.user_id = d.user_id
;

It may generate duplicate lines if a user has more than 1 grade on a first or last date.

See SQL Fiddle.


Postgresql: invalid record length at 29/E4624520 but replication looks fine

I cannot do anything else than quoting an entry from the pgsql-admin mailing list:

  Just after startup, the standby tries to read and replay the WAL files
  which exist in the standby, and then if it finds invalid WAL record,
  IOW, it can no longer read valid WAL record locally, it starts
  replication and tries to read the WAL files from the master. So the
  above log message "invalid  record ..." means that there is no valid
  WAL record in the standby, which triggers the replication. Since valid
  WAL records are streamed from the master, you don't need to worry
  about that log message.

It's worth noting that that line is only a LOG level message.


Oracle setup required for heavy-ish load

Firstly, I will say that this workload is by no means heavy by Oracle standards; thousands of commits/sec are possible, easily. Secondly, however, what matters here is not your database and it's not your server: it's your storage. There are many options here; you won't go too wrong with something like NetApp (I don't work for them, just a satisfied user) and the question is, what size? Here ORION is your friend. Whatever storage array you choose, this will take care of your first level of resilience, if your first node fails you simply mount the disks on another and start back up again, and Oracle will perform crash recovery so no data will be lost.

My advice is, get some numbers at a lower level - MB/s, IOPs - by performing a representative benchmark and take those to your nearest storage vendor and ask them what they've got. With NetApp at least, you can start fairly small and grow, adding another head for more resilience/better performance, adding shelves for more capacity, etc etc. Then test the crap out of it with Orion!