MySQL: CONCAT with NULL fields
In MySQL,
CONCAT('foo', 'bar', NULL) = NULL
the NULL always wins in MySQL.
If you would rather treat NULL as an empty string, use
CONCAT_WS
Show archive.org snapshot
(concatenation with separator) instead:
CONCAT_WS('', 'foo', 'bar', NULL) = 'foobar'
PostgreSQL
In PostgreSQL the NULL is not viral in CONCAT:
CONCAT('foo', 'bar', NULL) = 'foobar'
Related cards:
MySQL: Select a default value for NULL fields
If you need to do calculations inside the database and can not use Ruby objects you may run into problems when encountering fields with NULL values:
SELECT foo, bar, foo - bar AS baz FROM plop;
+-----+------+------+
| foo | bar | baz...
Joining PDFs with Linux command line
There are several ways to merge two (or more) PDF files to a single file using the Linux command line.
If you're looking for graphical tools to edit or annotate a PDF, we have a separate card for that.
PDFtk (recommended)
...
How to coordinate distributed work with MySQL's GET_LOCK
The linked article explains how to get a database-wide lock without creating table rows:
This article explains how I replaced file-based methods to...
How to add a user with all privileges to MariaDB
Add a user without password (recommended)
Replace newuser with your desired username:
mysql -uroot -p
CREATE USER 'newuser'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
exit;
``...
Installing multiple MySQL versions on the same Linux with mysql-sandbox
Ubuntu has a package mysql-sandbox that lets you install multiple MySQL versions into your user home:
- Install
mysql-sandbox
sudo apt install mysql-sandbox
- Download the version of MySQL you want to use from mysql.com:
<https://...
uninitialized constant MysqlCompat::MysqlRes (NameError)
If you get a stacktrace complaining about uninitialized constant MysqlCompat::MysqlRes a system library update might broke your gem.
You might have [switched from MySQL to MariaDB](https://makandracards.com/makandra/51171-running-rails-2-apps-w...
Don't sum up columns with + in a SQL query if NULL-values can be present.
Don't sum up columns with + in a sql-query if NULL-Values can be present.
MySQL and PostgreSQL cannot sum up NULL values with the + value. The sum value will be NULL.
MySQL:
mysql> select 1 + 2 + 3;
+-----------+
| 1 + 2 + 3 |
+--...
cucumber_factory 1.14 lets you set array fields, has_many associations, numbers without quotes
Setting array columns
When using PostgreSQL array columns, you can set an array attribute to a value with square brackets:
Given there is a movie with th...
ActiveRecord::StatementInvalid: Mysql2::Error: closed MySQL connection
I recently experienced the error ActiveRecord::StatementInvalid: Mysql2::Error: closed MySQL connection. Apparently this happens when there is a timeout during query execution. In order to fix this you can reconnect to your db.
Therefore either...
mysqltuner.pl
This Perl script will run diagnostics on your MySQL database and recommend changes to your MySQL configuration.