...wide mutex on something that is not a row, check out: Simple database mutex (MySQL lock). PostgreSQL: How to use with_advisory_lock to prevent race conditions
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...
...json and array take away a lot of the pain that you had on MySQL projects. Example use cases for array are tags or storing foreign keys (instead of a...
MMM (MySQL Master-Master Replication Manager) is a set of flexible scripts to perform monitoring/failover and management of MySQL Master-Master replication configurations (with only one node writable at any...
Occasionally some complex query must be processed on the database because building thousands of Ruby objects is impracticable.
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...
This might be due to AppArmor denying the MySQL server access to most of the filesystem. You can instead use LOAD DATA LOCAL INFILE ... to pipe data through the MySQL...
To clear the query cache in your MySQL database manually, e.g. for database profiling, execute the following command in your MySQL console: RESET QUERY CACHE...
...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...
...be useful to have a Ruby expression like condition ? positive_case : negative_case in MySQL queries: UPDATE users SET monthly_debit = IF(subscriber...
...mix dates and times incomparisons, mind to not compare datetimes with date ranges in MySQL. Visualisation 1. ------- ------- AAA AAA BBB BBB ------- ------- 2. ------- AAA B ------- 3. ------- A BBB ------- 4. ------- AAA
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.
...will resemble your database's EXPLAIN style. For example, it looks like this on MySQL: User.where(id: 1).includes(:articles).explain EXPLAIN for: SELECT `users`.* FROM `users` WHERE `users`.`id...
...will make a single query. It uses a sub-query, which is slow in MySQL, but fast in PostgreSQL: SELECT * FROM posts WHERE user_id IN (SELECT id FROM users...
...invoice.products # => [# ] How the :uniq option works In the example above it changes the MySQL query for Invoice#products to something like SELECT DISTINCT * FROM products ... Issues with PostgreSQL
They don't teach you this in college, but the fundamental theorem of the software industry is the idea that...
...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...
...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...
...always use Time.current instead of Time.now, etc. Why? Because of the way Rails and MySQL deal with time zones you would need to take care to use Time.zone.now in projects...
...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...
...not found", "Can't open file or directory", ...) after configuration changes, e.g. when changing MySQL's data directory. Remember to have a look at AppArmor's daemon configuration (usually at...
...because those times will also be converted using to_s(:db) and sent to MySQL. The database itself does not care about the (longer) value and just stores the date...