Category Archives: MySQL

Stored Function (Routine)

Reference:

Mysql dump and restore on windows

Steps:

  1. Dump
    1. cd c:\program files\mariadb 10.4\bin
    2. mysqldump -u root -p –all-databases > d:\backup\dump20190712.sql
      Notice:

      1. Execute this command in “command promp” not in “mysql client”
      2. No “;” in the end!
  2. Restore
    1. mysql u username p dbname < filename.sql
    2. mysql -u root -p < backup.sql

Reference:

  1. https://john-dugan.com/dump-and-restore-mysql-databases-in-windows/
  2. https://code.yidas.com/mysqldump/

Page log sequence number error of MariaDB

Cause

After reinstall mariadb, replace all DB files in folder “data” directly could cause this error and service “MariaDB” will never started.

Solution

  1. Check error log:
    https://mariadb.com/kb/en/library/what-to-do-if-mariadb-doesnt-start/
    C:\Program Files\MariaDB 10.4\data\REX-PC.err
  2. Create an option file and put it in %WINDIR%/my.ini
    https://dev.mysql.com/doc/refman/8.0/en/option-files.html

    1. Why 6?
      https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
      https://mariadb.com/kb/en/library/innodb-recovery-modes/
  3. Run command to start mariadb: (under folder bin)
  4. You should be able to connect to DB server now
  5. Export tables in standard way.
  6. Uninstall DB server
  7. Install DB server

Reset MySQL root password on Windows

Steps

  1. Open command line with administrator permission
  2. Stop mysql service (2 ways)
    1. net stop mysql
    2. administrator tools -> services
  3. Create a mysql_init.txt file with this command line:
    ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘new password’;
  4. cd C:\Program Files\MariaDB 10.3\bin
  5. mysqld –init-file=C:\\mysql-init.txt
  6. Done, password of root has been reset

Reference:

  1. https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.html
  2. https://dev.to/oneearedmusic/access-denied-reset-mysql-root-user-password-2hk4

Change Mysql (MariaDB) for table name lowercase

Stop Mysql service

  • run services.msc to open windows service manager
  • Stop MySQL service

Update my.ini (C:\Program Files\MariaDB 10.3\data\my.ini)

Add lower_case_table_names = 2

Start MySQL

  • Stop MySQL service

Reference:

SQL 錯誤 (1071):Specified key was too long; max key length is 767 bytes

Key too long issue

utf8 時,會使用 255 * 3 = 765 (bytes)
utf8mb4 255 * 4 = 1020 (so limit length is 191)

Replace varchar(200) with varchar(191)

Reference: