DevOps  Software Development 

MySQL

August 1, 2022


Table of Contents


  1. MTOP

  2. User Account Management

  3. Database Design


    1. Database Normal Forms

  4. MySQL Tuning


    1. MySQL Configuration (my.cnf)

    2. MySQL Option Files

    3. MySQLTuner


      1. MacOS

      2. CentOS

  5. Table ‘query_log’ is marked as crashed and should be repaired

  6. ERROR! The server quit without updating PID file

  7. MySQL – Convert all Tables of one or more Database(s) from MyISAM to InnoDB and vice-versa

  8. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

  9. Index column size too large. The maximum column size is 767 bytes.

  10. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

  11. ERROR 1839 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON

  12. InnoDB Engine Tables shows incorrect table_rows counts.

  13. ERROR 1366 (HY000) at line 3019: Incorrect string value: ‘\xF0\x9F\x98\x8A

  14. MySQL 5.7 won’t start after brew upgrade

  15. Workbench won’t connect to my localhost

  16. Table ‘performance_schema.session_variables’ doesn’t exist

  17. How to change password policy

  18. ERROR 1067 (42000) at line 57: Invalid default value for ‘comment_date’

  19. Find all my.cnf by order of preference

  20. Workbench Connections using old authentication protocol

  21. Workbench look and feel

  22. MySQL is down and won’t start

  23. MySQL is down and server running at 100% capacity

  24. Converting all Tables of one or more Database(s) from MyISAM to InnoDB and vice-versa

  25. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

  26. mysql-upgrade access denied

  27. [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files

  28. [ERROR] Unix socket lock file is empty /tmp/mysql.sock.lock

  29. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

  30. MySQL won’t allow ROOT to login remotely

  31. MySQLi does not seem to retain db connection from query to query

  32. MySQL Workbench wants to use your confidential information

  33. MySQL Process List

  34. [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

  35. Error Code: 1292. Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘order_date’ at row 111

  36. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’

  37. Installing MySQL 5.7 on MacOS

  38. How to import MyISAM databases from physical files

  39. How to import InnoDB databases from physical files

  40. Escaping a reserved symbol with regular expressions

  41. install_driver(mysql) failed: Can’t load module DBD::mysql

  42. Error Code: 1215. Cannot add foreign key constraint

  43. Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint

  44. SQL Queries with Joins are very slow

  45. Error Code: 1292. Truncated incorrect datetime value: ‘0000-00-00’

  46. [ERROR] InnoDB: The Auto-extending innodb_system data file…

  47. [ERROR] Can’t find error-message file ‘/usr/local/share/mysql/errmsg.sys’…

  48. ERROR 1018 (HY000): Unknown error 1018

  49. [Warning] File Descriptor 1725 exceeded FD_SETSIZE=1024

  50. Migrating dbs from server to server

  51. Unable to connect to CentOS MySQL server via SSH

  52. Shared object “libreadline.so.8” not found, required by “mysql”

  53. Error: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

  54. mysqli_connect: authentication method unknown to the client [caching_sha2_password]

  55. Uninstalling MySQL 8.0

  56. Unable to access my websites while MySQL is recovering from crash

  57. MySQL Keeps Crashing due to out of memory

  58. Time is off

  59. mysqldump: Got error: 1449: The user specified as a definer (‘puma’@’localhost’) does not exist when using LOCK TABLES

  60. mysqldump: Couldn’t execute ‘show events’: Unknown error 1044 (1044)

  61. Database 4 byte UTF-8 support – Enabled, but database tables need conversion

  62. Error Code: 1093. Unknown error 1093

  63. Locations

  64. Access denied for user ‘rom’@’205.211.94.206’ (using password: {{ PASSWORD }}

  65. time_zone inconsistencies

  66. Creating databases permissions

  67. Master-Slave Replication


    1. a.  Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

    2. b.  [Warning] Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=www-db-relay-bin’ to avoid this problem

    3. c.   Replication not working

    4. d.  Disabling replication

  68. Binary Log Cleanup

  69. Binary Log Management

  70. MySQLDump excluding tables

  71. Granting privileges


    1. 7.1.4 GRANT Statement


      1. GRANT General Overview

      2. Object Quoting Guidelines

      3. Privileges Supported by MySQL

      4. Account Names and Passwords

      5. Global Privileges

      6. Database Privileges

      7. Table Privileges

      8. Column Privileges

      9. Stored Routine Privileges

      10. Proxy User Privileges

      11. Implicit Account Creation

      12. Other Account Characteristics

      13. MySQL and Standard SQL Versions of GRANT

  72. Error Code: 1147. There is no such grant defined for user

  73. mysql-connector-java-5.1.26-bin.jar, but this file/folder was not found.

  74. Storing root password

  75. Restoring performance_schema

  76. Unable to restore Drupal on CentOS

  77. dyld: Library not loaded

  78. Reinstalling MySQL 5.7 on MacOS

  79. MySQL on CentOS running at max memory capacity

  80. Unable to restore Drupal on CentOS (2)

  81. InnoDB vs MyISAM

  82. Implement SSL/TLS encryption


    1. DB: 127.0.0.1


      1. WordPress Solution

      2. Drupal Solution (Not yet found)

    2. DB: victordiaz.ca

    3. DB: development-{{ HOST_NAME }}

  83. [Warning] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed

  84. [ERROR] SSL error: Unable to get private key from SSL/TLS certificate

  85. Access denied for user ‘test’@’10.1.0.102’

  86. Error Code: 1138. Invalid use of NULL value

  87. ERROR 1273 (HY000) at line 145: Unknown collation: ‘utf8mb4_0900_ai_ci’

  88. mysqldump some tables

  89. Unknown system variable ‘validate_password_policy’

  90. Case Sensitivity Issues

  91. Resetting Root Password

  92. Error [InnoDB] Unable to lock /var/lib/mysql/ibdata1 error: 11

  93. Unable to login without sudo

  94. Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’

  95. slow.log analysis

  96. SQL-MODE

  97. utf8mb4_general_ci VS utf8mb4_unicode_ci

  98. .my.cnf conficts

  99. mysqldump best practices

  100. mysql options

  101. Error Code: 1267. Illegal mix of collations

  102. MySQL 8.0


    1. MacOS Upgrade

    2. CentOS Upgrade

  103. MySQL 8.0 – Failed! Error: File ‘./mysql/user.MYD’ not found

  104. MySQL 8.0 – Failed! Error: Can’t find file: ‘proxies_priv’

  105. MySQL 8.0 – Host ‘127.0.0.1’ is not allowed to connect to this MySQL server


    1. Problem 1:

    2. Solution 1:

    3. Problem 2:

    4. Solution 2:

    5. Problem 3:

    6. Solution 3:

  106. MySQL 8.0 – Error Code: 1193. Unknown system variable ‘validate_password_policy’

  107. MySQL 8.0 – [Warning] [MY-012152] [InnoDB] Open files 1030 exceeds the limit 1024

  108. MySQL 8.0 – victordiaz@127.0.0.1 on using SSL/TLS – Access denied

  109. MySQL 8.0 – ERROR 1064 (42000) at line 1: You have an error in your SQL syntax

  110. MySQL 8.0 – Please confirm O_DIRECT is supported and remove the file /var/lib/check_sector_size if it exists

  111. MySQL 8.0 – MY-001126 – Can’t open shared library ‘/usr/lib/lib/mysql/plugin/component_validate_password.so’

  112. MySQL 8.0 – Validate Configuration

  113. MySQL 8.0 – Host ‘10.3.0.14’ is blocked because of many connection errors: unblock with ‘mysqladmin flush-hosts’

  114. MySQL 8.0 – PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client in drupal_is_denied()

  115. MySQL 8.0 – mysqld: Can’t read dir of ‘/etc/my.cnf.d’

  116. MySQL 8.0 – mysql.cnf for mariadb.cnf

  117. MySQL 8.0 – PDOException: SQLSTATE[42000]: Syntax error or access violation


    1. With MySQL 5.7 this is not a problem

  118. Uninstalling MySQL


    1. On MacOS

    2. On CentOS

    3. On Debian

  119. MySQL 8.0 – Logs

  120. MySQL Table Format does not work with SSH

  121. group_concat has max of 1024 characters

  122. MySQL with SSH – Escaping

  123. MySQL 8.0 – Package ‘mysql-server’ has no installation candidate

  124. [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

  125. MySQL 8.0 – System tables (TO BE COMPLETED)

  126. Master-Slave Replication – Appearances are deceiving

  127. mysql not found

  128. Unable to connect from romlx777 to 10.3.5.10

  129. MySQL INSERT … ON DUPLICATE UPDATE – Adds one to the autoincrement

  130. Transpose Output

  131. Connecting to GE MySQL Container


, ,
Written By victord On August 1, 2022