...some deliberation, we decided to implement a "schema-less" storage system on top of MySQL rather than use a completely new storage system. This post attempts to describe the high...
Store schema-less data in MySQL...
You can find out about disk space usage of all tables within your database by running this: SELECT table_name...
When you work in the MySQL console and you want to see which database is used, type: SELECT database(); The result you see is the database you would activate with...
Given the problem you have a new column postion and that column should be updated for all existing rows with...
...be useful to have a Ruby expression like condition ? positive_case : negative_case in MySQL queries: UPDATE users SET monthly_debit = IF(subscriber...
The next version of MySQL will include a built-in memcached daemon. This daemon can quickly get and set key/value pairs from and to InnoDB tables while completely bypassing the...
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' order by table_rows;
For reasons that completely escape me, MySQL 5.x limits UTF-8 strings to U+FFFF and smaller...
Take care in queries where multiple AND or OR operators are used. In doubt, always use braces to enforce precedence...
Some advice for bulk loading many records into InnoDB and finishing before the sun burns out. Use with care.
They don't teach you this in college, but the fundamental theorem of the software industry is the idea that...
Bundler::GemRequireError: There was an error while trying to load the gem 'mysql2'. Gem Load Error is: Incorrect MySQL client library version! This gem was compiled for 5.5.46 but the...
...libmysqlclient.so.20: cannot open shared object file: No such file or directory": gem pristine mysql2 gem pristine re-installs a gem (without re-downloading), re-compiling all native extensions in...
...good news is that with statement_timeout for PostgreSQL and max_execution_time for MySQL (5.7.8 or higher) both databases offer a variable to limit the execution time of queries...
...set the statement globally for your PostgreSQL cluster or for a specific database user. MySQL (5.7.8 or higher) Timeout Type: max_execution_time Scope: Applies only to SELECT statements; other...
...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...
...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...
...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...
...of does not consider "username" and "USERNAME" to be a collision. If you use MySQL this will lead to issues, since string comparisons are case-insensitive in MySQL.
...sensitive: false end When you get an ActiveRecord::RecordNotUnique error (probably in combination with Mysql2::Error: Duplicate entry) for a string field, case sensitivity may be your issue...
...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 | +--------------+
...wide mutex on something that is not a row, check out Simple database mutex (MySQL lock...
...nice way to write strings that span multiple lines. Note that this example uses MySQL. Refer to this card if you use PostgreSQL. If you need more than one JOIN...
...support nested transaction, this behaviour will be simulated with Savepoints (this is done for MySQL and Postgres). If a custom transaction lives inside another transaction, which we can not control...
...AJAX request completes before launching the debugger. A starved server thread can cause unexpected MySQL deadlocks. E.g. your test triggers a server request, locks some rows (MySQL automatically locks rows...
...starves the server thread. If you now run a query on the locked table, MySQL will explode with a detected deadlock. Again you need to wait_until the AJAX request...
...engineering of your source code you might consider adding a comment. Both PostgreSQL and MySQL support comments in the DB schema: For new columns: https://guides.rubyonrails.org/active_record_migrations.html#comments Changing the comment...