Today I discovered sequences in MariaDB. Gonna take them for a spin!
This came up back on August 9th 2020 in #lobsters on freenode. They were doing a system upgrade and providing a report by using `watch` to `cat` together a HTML header and footer with `mysql -e ‘select * from information_schema.processlist’` to provide a status report. Thought that was a neat hack.
To generate an SSL private key for use with MySQL/MariaDB and PDO:
openssl genrsa -out client-key.pem 4096
By default when you run a dump with ‘mysqldump’ the date of the dump is appended to the file, e.g.:
jj5@love:~/desktop/experiment$ udiff * --- dbt__jj_dev_1__svn_jdrepo.1.sql 2019-06-11 18:11:13.267758230 +1000 +++ dbt__jj_dev_1__svn_jdrepo.2.sql 2019-06-11 18:12:03.856075974 +1000 @@ -32,4 +32,4 @@ /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2019-06-10 21:59:44 +-- Dump completed on 2019-06-10 12:06:49
This causes dumps for a single database that has not changed to have two dumps which differ. It’s better to have dumps from the same unchanged database to be the same. To facilitate that add the –skip-dump-date option when running ‘mysqldump’.
See here for the back-story.
See Example #5 here.
/* Execute a prepared statement using an array of values for an IN clause */ $params = array(1, 21, 63, 171); /* Create a string for the parameter placeholders filled to the number of params */ $place_holders = implode(',', array_fill(0, count($params), '?')); /* This prepares the statement with enough unnamed placeholders for every value in our $params array. The values of the $params array are then bound to the placeholders in the prepared statement when the statement is executed. This is not the same thing as using PDOStatement::bindParam() since this requires a reference to the variable. PDOStatement::execute() only binds by value instead. */ $sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)"); $sth->execute($params);
Use the ‘source’ command:
mysql> source path/to/file.sql
Today I read this article and found this technique for calculating a median value:
SELECT x.val from data x, data y GROUP BY x.val HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5 LIMIT 1
Turns out MySQL doesn’t load timezone info by default! As you can read about here.
To load MySQL timezone info:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql