Open a MySQL shell using credentials from database.yml

In order to open a MySQL shell without the need to enter user and password, you can say the following in any Rails 2 project: script/dbconsole -p

MySQL: For each group, retrieve a comma-separated list of values in a given column

...database server your application runs on. Like MAX or COUNT, GROUP_CONCAT is a MySQL aggregation function you can use whenever your query contains a GROUP BY. You can use...

Function to return the minimum or maximum value per row with MySQL

MySQL's MIN and MAX functions are for aggregations only. This will not work and produce an error: SELECT id, MIN(birthday, '1978-01-01') FROM users;

Export CSV from MySQL directly

If you need to export data from MySQL to a CSV, you can profit from really fast built-in methods. This query writes the data to /tmp/geodb_coodinates.csv. And it's...

Show a MySQL table's charset, collation and engine

Use this MySQL command to show further info about a table: SHOW CREATE TABLE tags; This will output a table schema like this: CREATE TABLE `tags` ( `id` int(11) NOT...

Inline if-then-else in MySQL queries

...be useful to have a Ruby expression like condition ? positive_case : negative_case in MySQL queries: UPDATE users SET monthly_debit = IF(subscriber...

Delete from joined MySQL tables

...to delete rows from a table, and the delete conditions require a joined table, MySQL needs to know which table you want to delete from. Let's say that Post...

MySQL / MariaDB: Show disk usage of tables and columns

You can find out about disk space usage of all tables within your database by running this: SELECT table_name...

SQL: Find out number of rows of all tables within a MySQL database

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' order by table_rows;

MySQL operator precedence

Take care in queries where multiple AND or OR operators are used. In doubt, always use braces to enforce precedence...

Loading half a billion rows into MySQL

Some advice for bulk loading many records into InnoDB and finishing before the sun burns out. Use with care.

Mysql::Error: SAVEPOINT active_record_1 does not exist: ROLLBACK TO SAVEPOINT active_record_1 (ActiveRecord::StatementInvalid)

Possible Reason 1: parallel_tests - running more processes than features If you run old versions of parallel_tests with more...

How to update a MySQL column with ascending numbers

Given the problem you have a new column postion and that column should be updated for all existing rows with...

Delete all MySQL records while keeping the database schema

You will occasionally need to clean out your database while keeping the schema intact, e.g. when someone inserted data in...

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...

Take care when joining and selecting on scopes

Occasionally some complex query must be processed on the database because building thousands of Ruby objects is impracticable.

I Can't Wait for NoSQL to Die - Ted Dziuba

They don't teach you this in college, but the fundamental theorem of the software industry is the idea that...

When reading model columns during class definition, you must handle a missing/empty database

...rake db:migrate" on an empty # database, we swallow such errors. end end For MySQL, rescue Mysql2::Error, ActiveRecord::StatementInvalid might be fitting...

Differences between transactions and locking

...when committing a transaction. E.g. you might see an error like this: ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction This has nothing to do with the...

...application-level locks that we talk about below. What happens here is that when MySQL makes changes, it locks rows in the database to ensure that the change will applied...

How DECIMAL columns deal with numbers exceeding their precision or scale

...in a DECIMAL field, and that number exceeds that column's precision or scale MySQL (strict mode) The database connection will throw an error. MySQL (no strict mode) Warning

...t use your production application in no strict mode. DON'T! MySQL will store the closest representable value. Here are some examples for a DECIMAL with a precision of...

Don't sum up columns with + in a SQL query if NULL-values can be present.

...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...

MySQL: mysql> select 1 + 2 + 3; +-----------+ | 1 + 2 + 3 | +-----------+ | 6 | +-----------+ 1 row in set (0,00 sec) mysql> select 1 + NULL + 3; +--------------+ | 1 + NULL + 3 | +--------------+ | NULL | +--------------+

Invoices: How to properly round and calculate totals

...note that while this note has a number of code examples in Ruby and MySQL, the concepts apply to all programming languages and data stores. When to round

...of rounded values. Don't ever use the float data type in Ruby or MySQL The data types Float (Ruby) and FLOAT (MySQL) are not suited for calculating or storing...

Running Rails 2 apps with modern MariaDB SQL server

...with both old and new Rails applications. Switch to MariaDB Remove MySQL: sudo apt remove mysql-common mysql-client mysql-server mysql-apt-config Install MariaDB: sudo apt install mariadb...

The mysql and mysql2 gems installed on your machine are still built against the mysql-client package you just uninstalled, so you will probably get this error when booting...