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...
How to use pessimistic row locks with ActiveRecord
...wide mutex on something that is not a row, check out Simple database mutex (MySQL lock...
How Rails and MySQL are handling time zones
...calculations, e.g. 10 AM CEST minus 8 AM UTC is zero. A datetime in MySQL does not have a zone. It just stores the literal string...
...That means that Rails must make assumptions about timestamps loaded from and written to MySQL. Rails has two completely different modes of dealing with this. We also prefer to use...
Migrate gem tests from Travis CI to Github Actions with gemika
...If necessary (i.e. the tests use databases), add a spec/support/database.github.yml file with this content: mysql: database: test username: root password: password host: 127.0.0.1 port: 3306 postgresql: database: test host: localhost...
...version that is actually in the matrix. You might run into issues with the mysql2 gem, including issues bundling, and even segfaults. Use this card to fix these issues.
Threads and processes in a Capybara/Selenium session
...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...
How to load an SQL dump from a migration
...thought. While you can call ActiveRecord::Base.connection.execute(sql) to execute arbitrary SQL commands, the MySQL connection is configured to only accept a single statement per query. If you try to...
...error in your SQL syntax. You can work around this by opening a second MySQL connection that does accept multiple statements per call. Below is an example for a migration...
Deal with certain travis CI failures
...server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? Connection to the MySQL database does not work anymore Your travis-ci builds might have started failing on the...
mysql -e 'create database IF NOT EXISTS minidusen_test;' with an error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) The command "mysql...
ActiveRecord: Specifying conditions on an associated table
...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...
Tod: A Gem for handling daytime without a date
Tod is a gem for working with daytimes. Another additional gem? Thus SQL has a time datatype for storing time...
Regular tasks for long-running projects
...suffer from reduced performance. Check the table size by consumed disk space in PostgreSQL / MySQL/MariaDB, or by row count. Check storage usage Just like database tables, storage usage may increase...
Storing trees in databases
This card compares patterns to store trees in a relation database like MySQL or PostgreSQL. Implementation examples are for the ActiveRecord ORM used with Ruby on Rails, but the techniques...
Nested ActiveRecord transaction pitfalls
...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...
Case sensitivity in PostgreSQL
PostgreSQL, unlike MySQL, treats strings as case sensitive in all circumstances. This includes comparison with = and LIKE collision detection in unique indexes Usually this is fine, but some strings (like...
How to explain SQL statements via ActiveRecord
...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...
How to write complex migrations in Rails
...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...
Don't assign time values to date attributes
...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...
Why has_many :through associations can return the same record multiple times
...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
Use Time.current / Date.current / DateTime.current on projects that have a time zone
...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...
AppArmor in Linux
...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...
Testing if two date ranges overlap in Ruby or Rails
...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
Loading dumps via SSH, unpacking and sourcing them, all with a progress bar
...on the server (ls -l). In our case, it's 1234567890. Know your (local) MySQL root password. We'll use SECRET below. Know your (local) target database name. For us...
...my_project_development. Go wild: ssh user@example.com "cat /mnt/dumps/my_project.dump.bz2" | pv -s 1234567890 | bzip2 -d | mysql -uroot -pSECRET my_project_development That will: Print the (bzipped) file to stdout on the...
Geordi 1.9 released
...working directory and ~/dumps (for dumps created with geordi). geordi drop_databases Delete local MySQL/MariaDB and Postgres databases that are not whitelisted. Authentication is handled via PAM for Postgres and...
...MariaDB, via .my.cnf with fallback to mysql -p for MySQL. Different connection methods can be chosen via command line switches, e.g. for cleaning up multiple local database server installations in...
When Sass-generated stylesheets print a Encoding::CompatibilityError
We upgraded a Rails 2 application to Rails 3.2 and Ruby 2.1, changed the mysql adapter from mysql to mysql2, but did not activitate the asset pipeline. Instead we used...
Checking database size by row count
...public | pages | 3919 Please note that this does not work reliably on database slaves. MySQL SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = (SELECT database()) ORDER BY table...