Documentation by Black Sheep Research

User Tools

Site Tools

Trace:
useful:mysql-mariadb

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
useful:mysql-mariadb [2022/11/27 15:38] – adminuseful:mysql-mariadb [2024/03/15 14:36] (current) – admin
Line 60: Line 60:
  
 The solution I have now used is well described at [[https://gist.github.com/drmalex07/c0f9304deea566842490|this Github article]]. The solution I have now used is well described at [[https://gist.github.com/drmalex07/c0f9304deea566842490|this Github article]].
 +
 +Except that I had a big problem with one connection, and it seems the fix is to change "LOCAL_ADDR=localhost" to "LOCAL_ADDR=127.0.0.1".
 +
  
 ===== Data files under /home directory ===== ===== Data files under /home directory =====
Line 71: Line 74:
      [Service]      [Service]
      ProtectHome=false      ProtectHome=false
 +
 +It should be possible to achieve this without knowing exactly where the file is by using:
 +
 +     systemctl edit mariadb
 +     
 +but when I tried, it seemed not to work.
 +
 +===== Change collation for all tables in a database =====
 +
 +Remember to set the correct name for the database in this code and make sure output file does not exist:
 +
 +     SET @MY_SCHEMA = "myremos_j41";
 +     SELECT DISTINCT
 +      CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
 +     FROM INFORMATION_SCHEMA.TABLES
 +     WHERE TABLE_SCHEMA=@MY_SCHEMA
 +       AND TABLE_TYPE="BASE TABLE"
 +     INTO OUTFILE '/tmp/convert.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
 +     
 +Then run this command (changing database name as appropriate):
 +
 +     mysql myremos_j41 < /tmp/convert.csv     
  
useful/mysql-mariadb.1669563534.txt.gz · Last modified: 2022/11/27 15:38 by admin