Tuesday, September 26, 2017
Finding suitable problems with MySQL MariaDB to focus on
Finding suitable problems with MySQL MariaDB to focus on
My last blog post ended with me having just set up a rudimentary VM environment and hoping to turn it into a viable automatic system run with scripts. However, before I get ahead of myself I should first look into what the actual requirements of the problem subject are. Also in the last post I chose MySQL as the target service for my assignment. I should try to find actual reported problems to do with MySQL and note their causes, solutions and prerequisites if any.
MySQL is a software project with a somewhat unusual history. Started back in the 90s in Sweden, MySQL became a Free Software project under the GPL licence in 2000 and steadily gained popularity the following years. In 2008 it was acquired by Sun Microsystems, a large software corporation, and then again acquired in 2010 by Oracle, an even larger software corporation, which caused its userbase and its founder/maintainer some misgivings. Thus MariaDB was born, a fork of MySQL and intended to work as free software drop-in replacement for it. MariaDB has been gaining quite a following, often at the expense of MySQL, and is intentionally similar to MySQL. Because of this I will include it in my search alongside MySQL.
Seeing as this assignment is meant to be limited in scope only problems with the databases themselves will be considered. Covering problems with applications and services that interface or use the databases would introduce too many new variables and possibilities to research and test. Likewise, bugs in the database software itself shall also be filtered out - we want to collect problems that an average system administrator can fix by way of corrected configuration, not by recompiling source code. This may actually pose a problem for this assignment - it is logical to assume that most practical problems users come upon are tightly coupled with the specific domain and surrounding applications - not only the databases. This potential problem will be dealt with when/if it occurs.
The first place that came to mind when thinking of sites where users report problems and (hopefully) get help is the official forum. The MySQL forum was to be the first I visit:
- New Installation and root cannot login with or without password
- Using passwordless option file with mysql client
- Cant increase max_allowed_packet
The MySQL official documentation has a section dedicated to common problems. There is useful information on a number of relevant problems:
- Problems with File Permissions
- Out of memory
- Cant create/write to file
Having combed the official sources of information for common problems that fit my criteria I turned to one more potential source, one that had me feeling a bit more hopeful going in: Server Fault. Using its tagging system one can easily browse only questions tagged mysql and/or only questions that are voted as high quality.
- MySQL [Warning] IP address could not be resolved
- MySQL connection works with localhost but not with 127.0.0.1
- InnoDB: Error: log file ./ib_logfile0 is of different size
- `mysql_upgrade` is failing with no real reason given
- New Installation and root cannot login with or without password:
- Prereq. : Newly installed MySQL 5.7 on a Fedora 25 machine.
- Cause: Root password is not supplied to MySQL client but also not known by user
- Solution: Running "grep temporary password /var/log/mysqld.log" returns the temporary root password with which the user can log in.
- Using passwordless option file with mysql client
- Prereq. : Not mentioned. Presumably works with any reasonably recent MySQL version.
- Cause: User has tried to specify two passwords in a single config file. This is not supported and also not recommended for security reasons,but that is beside the point.
- Solution: Using "mysql_config_editor" to set up login_paths which will create an encrypted file with the credentials, then running "mysql --login-path=<login_path>"
- Cant increase max_allowed_packet
- Prereq. : MySQL 5.1.73
- Cause: User is using packets of size greater than 1024MebiBytes. After setting the max_allowed_packet option to 1500MebiBytes the problem persists.
- Solution: Within MySQL there is a hard upper limit for max_allowed_packet of 1024MebiBytes. A higher value is not possible and the program will not even acknowledge the setting. Informing the user of this limitation is the best resolution of this issue.
- Problems with File Permissions
- Prereq. : MySQL 5.7
- Cause: The UMASK or UMASK_DIR environment variable is set incorrectly.
- Solution: For MySQL the default UMASK and UMASK_DIR values are 0640 and 0750. Setting them to these values before starting the MySQL daemon should fix the problem.
- Out of memory
- Prereq. : MySQL 5.7
- Cause: There is not enough memory to store the entire query result.
- Solution: Not definitive. Providing the program with more memory should fix the problem.
- Cant create/write to file
- Prereq. : MySQL 5.7
- Cause: MySQL cannot create a temporary file for the result set in the temporary directory because it does not exist or i doesnt have enough free space or MySQL hasnt got the required permissions.
- Solution: Starting the MySQL daemon with the "--tmpdir" option and making sure the daemon has the needed permissions or that directory.
- MySQL [Warning] IP address could not be resolved
- Prereq. : MySQL5.6.3 on a CentOS 6.1
- Cause: MySQL daemon is using DNS to resolve IP address when it should not.
- Solution: Adding skip-host-cache and skip-name-resolve to the MySQL daemon config file.
- MySQL connection works with localhost but not with 127.0.0.1
- Prereq. : MySQL 5.5.37 on Debian Wheezy.
- Cause: "hosts.deny" and "hosts.allow" files by default have a file mode of 600. MySQL cannot access them to determine whether to allow connections.
- Solution: Change the modes of "hosts.deny" and "hosts.allow" to 644.
- InnoDB: Error: log file ./ib_logfile0 is of different size
- Prereq : MySQL converted to use the InnoDB engine (its default engine).
- Cause: InnoDB is very picky about its config and log files.
- Solution: Delete both "ib_logfile0" and "ib_logfile1" in /var/lib/mysql
- `mysql_upgrade` is failing with no real reason given
- Prereq. : Upgrading from MySQL 5.1 to 5.5
- Cause: Many, but one specific is "user didnt pass username and password"
- Solution: Pass username and password correctly "mysql_upgrade -u root -p"
download file now
