I altered the trigger so that it prints the content of EVENTDATA(). In case of a CREATE LOGIN with a password, the event data contains the whole statement (under TSQLCommand/CommandText). In case there is an empty password, the event CommandText only contains --*CREATE LOGIN-----------------------------------------. Since the trigger checks the CommandText, which in this case contains only some kind of placeholder, it would not match.

SELECT COALESCE(t1.ID, t2.ID) ID,
       t0.`KEY`,
       COALESCE(t1.FILTER_NAME, t2.FILTER_NAME) FILTER_NAME,
       COALESCE(t1.FILTER_VALUE, t2.FILTER_VALUE) FILTER_VALUE,
       COALESCE(t1.VALUE, t2.VALUE) VALUE
FROM ( SELECT @filterName FILTER_NAME,
              @filter_value FILTER_VALUE ) criteria
CROSS JOIN ( SELECT DISTINCT `KEY`
             FROM test ) t0
LEFT JOIN test t1 ON criteria.FILTER_NAME = t1.FILTER_NAME
                 AND criteria.FILTER_VALUE = t1.FILTER_VALUE
                 AND t0.`key` = t1.`key`
LEFT JOIN test t2 ON '' = t2.FILTER_NAME
                 AND '' = t2.FILTER_VALUE
                 AND t0.`key` = t2.`key`

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

Make sure you are applying patches because https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/gradual-database-password-rollover-applications.html is possible since 19.12 (backported from 21c)

This allows you to update the password in the DB and have a grace period in which your applications can still connect using the older password.

Alter profile my_profile limit password_rollover_time 7;
Password my_user
Enter new password: ***

You can still use the old password for 7 days so you can just work through your application config and servers and restart them one at a time.

We have some really strange behaviour on MySQL (8.0.29) that we can't explain.

We have 2 tables + 1 link table in-between, illustrated by this schema :

We run a query whereby we need to fetch the id and organization_id from table1 for all the records that have a linked record in table2. However, if there are 2 linked records in table2, we still only want a single record from table1.

We use Doctrine (PHP) integrated in a complex application so the query is generated for us. The resulting query is :

SELECT
  table1.organization_id,
  table1.id AS id_0
FROM
  table1
  LEFT JOIN table2 ON (
    EXISTS (
      SELECT
        1
      FROM
        link1
      WHERE
        link1.table2_id = table2.uuid
        AND link1.table1_id IN (table1.id)
    )
  )
WHERE
  table1.location_id = 605
  AND table1.status IN ('confirmed')
  and table1.organization_id=1
ORDER BY table1.id DESC
LIMIT
  1000

This query is supposed to return 260 rows, but it returns only 1. Unless we just restarted MySQL, then it returns 0 and will continue to return 0 until we remove either the LIMIT clause or the ORDER BY clause.

On MariaDB it gets worse : the query just uses CPU and we killed it after a few minutes.

If you want to give it a go, the data dump is at https://gist.github.com/wimg/c8af87bd30b036c4de5e386e095f6416

Tried it on MySQL 8.0.29 (currently the most recent version).

Anyone have any idea what's going on here ?

I have to find out the "EXACT" row count of 5000 tables running MySQL 5.5 on RHEL5. As far as I know the only method is "SELECT COUNT(*) from table_name". But this is taking too much time.

So I tried to run the queries in parallel. I split the queries in 10 different files each containing 500 queries and run them from 10 different terminals.I also tried by logging with different uid/pwd. Every time it is taking same time. What I am missing? What to do to run these queries in parallel.

It is a 16 vCPU and 94GB RAM system. Throughput is also decent, I think. Almost every table has a primary key . It takes more than 6 hrs in all.

Never say never, but since backups aren't executable files and contain no directly executable code (they're about the data, not the SQL Server software itself) I would think the risk is very, very low. It would be more likely that your backup files would be the target of the ramsomware rather than the agent of infection. Anything executable would have to be executable from within the database, like a stored procedure. There are far more effective and direct ways for ransomware to spread.

You need an index on orders (o_orderdate), which should avoid the expensive sequential scan.

If it doesn't, and the sequential scan is actually slower than the index scan, PostgreSQL is probably not configured properly: either random_page_cost is to high for your hardware, or effective_cache_size is too low. Disabling parallel query (max_parallel_workers_per_gather = 0) will also favor an index scan.

Nowadays, there are at least two levels of abstraction, often more, between the data file the DBMS works with and the physical storage device blocks. What appears as a contiguous file to the reader is not necessarily contiguous in the filesystem, and even if it it, that's not necessarily a contiguous span of blocks on the underlying device(s). So, no, the database can't possibly know how sequential its sequential read actually is.

As to why, firstly, there's history. When storage architectures were simpler, DBMSes used to operate much closer to the hardware, often working with raw devices, where they had exact control over physical placement of data file pages/blocks and could guarantee the sequential nature of I/O.

Secondly, there's no harm in attempting sequential reads. If the underlying physical blocks happen to be in fact sequential, we get some benefit out of it; if not, well, at least we tried.

One way of achieving what you want might be to use the https://docs.databricks.com/sql/language-manual/functions/row_number.html to order matching event dates within each year (window), then pick only the most recent date each year.

-- Use a common table expression to pre-select and order dates we want
with past_events as ( 
   select 
      Date, Event, 
      -- within each year, order by the day since the beginning of that year
      -- in the descending order, and assign row numbers accordingly
      row_number() over (partition by year(Date) order by dayofyear(Date) desc) rn
   from some_table
   -- only pick days from each year in the past that's no later than today
   where dayofyear(Date) 

This is "paper code" as I have no way of testing it with Databricks, but it should give you the basic idea.

You may need to https://docs.databricks.com/sql/language-manual/sql-ref-identifiers.html#delimited-identifiers if Date or Event happen to be reserved words in Databricks.

If you have data first make a backup.

To completely uninstall MySQL follow below steps:

sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo apt-get dist-upgrade
sudo rm -rf /etc/mysql
sudo rm -rf /var/lib/mysql*

To install MySQL 8.0.22 add the MySQL Software Repository

sudo apt update
sudo apt install gnupg

Download mysql-server_8.0.22-1debian10_amd64.deb-bundle.tar from https://downloads.mysql.com/archives/community/ or using wget :

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-server_8.0.22-1debian10_amd64.deb-bundle.tar

Once the download is completed install the package :

sudo dpkg -i mysql-apt-config*

During the installation, you’ll be presented with a configuration screen where you can specify which version of MySQL you’d prefer, along with an option to install repositories for other MySQL-related tools. Choose the one you need.

Refresh your apt package cache to make the new software packages available:

sudo apt update

Having added the repository and with your package cache freshly updated, now you can use apt to install the latest MySQL server package:

sudo apt install mysql-server

MySQL should be installed and running now. Check by using systemctl:

sudo systemctl status mysql

Do not forget to secure MySQL using

mysql_secure_installation

Refrences

https://www.digitalocean.com/community/questions/how-can-i-properly-downgrade-from-mysql-8-to-5-7-on-ubuntu-20-04

https://www.digitalocean.com/community/tutorials/how-to-install-the-latest-mysql-on-debian-10