When MySQL refuses to use your index, there's a number of things that you may be doing wrong. One of them might be conditions with improper data types.
...assume you have a users table with an email field (varchar) which is indexed. MySQL will use the index when your query is well-formed: mysql> EXPLAIN SELECT * FROM users...
Create 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...
...by a password, you can remove the password. Replace existinguser with your desired username: mysql -uroot -p SET PASSWORD FOR existinguser@localhost=''; FLUSH PRIVILEGES; exit; Create a user with unix...
validates_uniqueness_of is not sufficient to ensure the uniqueness of a value. The reason for this is that in...
When selecting records in a date range, take care not to do it like this: start_date = Date.parse('2007-05...
Caution when using .where to exclude records from a scope like this: # Fragile - avoid User.where("id NOT IN (?)", excluded_ids...
Note: For PostgreSQL you should use advisory locks. For MySQL we still recommend the solution in this card. If you need to synchronize multiple rails processes, you need some shared...
...can be used as a mutex. One option is to simply use your existing (MySQL) database. The attached code provides a database-based model level mutex for MySQL. You use...
...databases are actually very complicated, and chances are, your making some incorrect assumptions. The MySQL innodb engine actually has four different modes for transactions: READ UNCOMMITTED READ COMMITTED REPEATABLE READ...
...are consistent nonlocking reads. The problem with consistent nonlocking reads If you open a MySQL transaction, the first SELECT statement will establish a "snapshot" of the database's state. All...
We had a card that described how to install multiple mysql versions using mysql-sandbox. Nowadays with the wide adoption of docker it might be easier to use a MySQL...
...docker image for this purpose. Create a new mysql instance docker run --name projectname_db -e MYSQL_ROOT_PASSWORD=secret -p "33008:3306" -d --restart unless-stopped mysql:5.7
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://dev.mysql.com/downloads/file/?id=480427 Make sure to choose "Generic Linux" instead of "Ubuntu" so you get a .tar.gz instead of .deb cd into the directory...
The Oracle mysql client has an odd behavior if your server uses latin1 as default character-set-server. Command mysql --version mysql Ver 8.0.31-0ubuntu0.20.04.2 for Linux on x86...
mysql --default-character-set utf8mb4 -e "SHOW VARIABLES LIKE '%char%';" Expectation utf8mb4 will be used as character set in this session. Reality The mysql client falls back to latin1...
So you're switching to PostgreSQL from MySQL? Here is some help... General hints on PostgreSQL \? opens the command overview \d lists things: \du lists users, \dt lists tables etc...
...Command comparison Description MySQL command PostgreSQL equivalent Connect to the database mysql -u $USERNAME -p sudo -u postgres psql Show databases SHOW DATABASES; \l[ist] Use/Connect to a database named...
...want to see how long your database queries actually take, you need to disable MySQL's query cache. This can be done globally by logging into a database console, run...
ActiveRecord::Base.uncached do yield end true end Don't forget to re-enable MySQL query caching later with SET GLOBAL query_cache_type=ON...
...works differently See PostgreSQL: Difference between text and varchar columns for PostgreSQL-specific info MySQL has 4 different column sizes. They are actually different data types under the hood:
CONCAT('foo', 'bar', NULL) = NULL the NULL always wins in MySQL. If you would rather treat NULL as an empty string, use CONCAT_WS (concatenation with separator) instead...
...Right after that we had a look into the logrotation-configuration and saw that the mysqladmin-command is used to check if the server is running in order to rotate...
...log flush-slow-log fi endscript } From the documentation of the parameter ping of the mysqladmin-command we can see that the command tries to access the database and if...
In MySQL comparing zero to a string 0 = "any string" is always true! So when you want to compare a string with a value of an integer column, you have...
...knowing a secret token: Potential Query Manipulation with Common Rails Practises CVE-2013-3211 MySQL madness and Rails
This article describes how to reset MySQL's or MariaDB's root password on your workstation. It's meant for local development purposes only, don't do this in production...
...you prefer using a password for root. Solution Step 1 is getting a root mysql shell that allows us to change user credentials. We need to stop the mysql daemon...
For string columns, MySQL indexes the left side of a string. That means an index can speed a like query that has a wildcard on the right side:
...in different order. Solving deadlocks is potentially complicated, so here are a few pointers: MySQL should always detect the deadlock right when it happens, and will throw an error to...
...still cannot see what actually happened, you might need to get better acquainted with MySQLs locking model. While it keeps out of your way usually, it's actually pretty complex...
...lives in another table, you need to JOIN both tables during the UPDATE. In MySQL you can do it like this: UPDATE employees LEFT JOIN departments ON employees.department_id = departments.id...
This should be fixed in the latest LTS-branches of our mysql2 fork, 0.2.x-lts and 0.3.x-lts. Use gem 'mysql2', git: 'https://github.com/makandra/mysql2', branch: '0.2.x...
...lts' # for Rails 2.x gem 'mysql2', git: 'https://github.com/makandra/mysql2', branch: '0.3.x-lts' # for Rails 3.x in your Gemfile, and do a bundle update mysql2 Background
MySQL and MariaDB have an SQL mode setting which changes how MySQL behaves. The SQL mode value is comprised of multiple flags like "STRICT_TRANS_TABLES, NO_ZERO_IN_DATE...
...or disables a particular behavior. The default SQL mode varies widly between versions of MySQL and MariaDB. In general, more recent versions of MySQL and MariaDB have stricter settings than...
Like in any language, a FLOAT will eventually corrupt data due to rounding errors. Please use DECIMAL, which has well...
The result might look like this: +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1219 | | | +------------------+----------+--------------+------------------+ Dump the database (in a new terminal): mysqldump -uroot -p -r...
...HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='replicator', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1219; MASTER_HOST and MASTER_PORT are set to...