mysql


An example of MySQL code that executes TRIM() to remove a prefix and/or a suffix from all entries that match a WHERE clause

The following code will remove the prefix http://wow.example.com from all rows that match the where clause:

Update `my_table`
set
`my_column` = TRIM(
  LEADING 'http://wow.example.com'
  FROM `my_column`)
WHERE (`my_column` LIKE '%http://wow.example.com/%');

The next block will remove the suffix index.php from all entries that match the where clause:

Update `my_table`
set
`my_column` = TRIM(
  TRAILING 'index.php'
  FROM `my_column`)
WHERE (`my_column` LIKE '%/index.php%');

In case we need to remove the string needle both from the prefix and the suffix while using a where clause, we can use the following code:

Update `my_table`
set
`my_column` = TRIM(
  BOTH 'needle'
  FROM `my_column`)
WHERE (`my_column` LIKE '%needle%');

Incorrect definition of table mysql.column_stats: expected column

2022-06-07  7:17:02 3051 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2022-06-07  7:17:02 3051 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

While checking the logs of a MariaDB docker container, we found the above error lines repeating thousands of times. It appears that there was an issue during the migration of the database to a newer version. The solution was to manually execute the command mysql_upgrade. To execute it, we first had to gain access to a shell inside the container, we did that using docker exec -it CONTAINER_NAME /bin/bash as below:

# Gain shell access to the database container
docker exec -it mariadb_alpha /bin/bash;
# In the shell of the container, we executed the following to automatically fix a variety of problems/errors
mysql_upgrade --user=root --password;

Unknown/unsupported storage engine: InnoDB

2022-05-30 06:09:48+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.8.3+maria~jammy started.
2022-05-30 06:09:48+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-05-30 06:09:48+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.8.3+maria~jammy started.
2022-05-30 06:09:49+00:00 [Note] [Entrypoint]: MariaDB upgrade information missing, assuming required
2022-05-30 06:09:49+00:00 [Note] [Entrypoint]: MariaDB upgrade (mariadb-upgrade) required, but skipped due to $MARIADB_AUTO_UPGRADE setting
2022-05-30  6:09:49 0 [Note] mariadbd (server 10.8.3-MariaDB-1:10.8.3+maria~jammy) starting as process 1 ...
2022-05-30  6:09:49 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-05-30  6:09:49 0 [Note] InnoDB: Number of transaction pools: 1
2022-05-30  6:09:49 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-05-30  6:09:49 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2022-05-30  6:09:49 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOSYS: check seccomp filters, and the kernel version (newer than 5.1 required)
2022-05-30  6:09:49 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
2022-05-30  6:09:49 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2022-05-30  6:09:49 0 [Note] InnoDB: Completed initialization of buffer pool
2022-05-30  6:09:49 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
2022-05-30  6:09:49 0 [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.5.4.
2022-05-30  6:09:49 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-05-30  6:09:49 0 [Note] InnoDB: Starting shutdown...
2022-05-30  6:09:49 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-05-30  6:09:49 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-05-30  6:09:49 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-05-30  6:09:49 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-05-30  6:09:49 0 [ERROR] Aborting

Recently, we were working on a MariaDB installation in Docker which was using the latest version of the container. The definition in our configuration file was as follows:

# An excerpt from our docker-compose.yml
mariadb_alpha:
    depends_on:
      - another_container
    image: mariadb
    container_name: mariadb_alpha
    networks:
      - mariadb_alpha
    volumes:
      - /alpha/mysql:/var/lib/mysql
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: qwerty
      MYSQL_DATABASE: aplha
      MYSQL_USER: user
      MYSQL_PASSWORD: password

After an update, the database stopped working and the logs were giving the above errors. Specifically, we got the error that InnoDB was an unknown or unsupported storage engine which is really bad! The command we used to view the logs is the following:

# We used the following docker command to view the logs of the container
docker container logs mariadb_alpha;

We noticed the following line from the records, which was extremely useful:

InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.5.4.

From this information, we were able to understand that the last time the database functioned properly, it was using MariaDB version 10.5.4. By visiting the official docker image website for MariaDB, we were able to see that there was a version tagged 10.5. We modified our YML file and changed the image of the container to the one below which uses the 10.5 tagged image:

# An excerpt from our docker-compose.yml
mariadb_alpha:
    ...
    image: mariadb:10.5
    ...

Then, we rebuilt our container using the docker-compose command:

#We increase the timeout to avoid issues
export COMPOSE_HTTP_TIMEOUT=180;
docker-compose up -d --remove-orphans;

After the setup was complete, we were able to see that the container was working as expected! We reverted the change in the YML file back to image: mariadb and executed docker-compose once more. The MariaDB container was updated to the latest version and was working as expected again!


How to reset root password in XAMPP MySQL

Recently a client got locked out of a MySQL deployment managed by a XAMPP. They requested us to reset the password for the root account. To do so, we followed the following procedure:

Using a text editor, we opened the following file, which is the MySQL configuration file:

C:\xampp\mysql\bin\my.ini

We found the section for the MySQL daemon, which starts after the line that contains the following tag:

[mysqld]

Right below the tag, we added a new line and added the following directive:

skip-grant-tables

Example of a configuration file:

[mysqld]
skip-grant-tables
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp"
...

The skip-grant-tables directive causes the server not to read the grant tables in the MySQL system schema and, thus, start without using the privilege system. This option gives anyone unrestricted server access to all databases.

After saving the file, we restarted the MySQL server from the XAMPP interface. This action forced the new settings to be loaded and take effect.

Next, using the command line, we connected to the MySQL server using the CLI client for MySQL:

C:\xampp\mysql\bin\mysql.exe --user=root

In the above command, we issued the mysql.exe application with only one parameter, the --user=root, which instructs the client to ignore the default login username and use root instead.

Once we activated the MySQL command line, we switched the usage to the mysql database using the following command:

USE mysql;

Using the following command, you can set the password for the root:

UPDATE user SET password = 'newPassword' WHERE user = 'root';

Note: In case you want to leave the root password blank, then you can use the following:

UPDATE user SET password = '' WHERE user = 'root';

Please note that there might be more than one entry for root in the system, as it could contain different IPs etc. To see all available options, execute the following:

SELECT * FROM user \G;

After we finished messing with the MySQL internals, we executed the following command to exit the MySQL terminal:

exit;

Then, we edited the MySQL configuration file again:

C:\xampp\mysql\bin\my.ini

We found the section for the MySQL daemon again, which starts after the line that contains the following tag:

[mysqld]

We removed our skip-grant-tables directive and saved the file.

We restarted the MySQL server via the XAMPP interface after saving the file. The restart forced the updated settings to load and take effect due to this action.

Success!! The root user had the password we set for it and we could access the database as expected.

The same guide can be used for other installations of MySQL so long you know where the MySQL configuration file is.


Quick notes on optimizing a mysql/mariadb database in a docker container

First, to avoid opening the network of the container to another network, you need to open a shell to the docker container itself. To do so, use the following command:

docker exec -it website_db /bin/bash;

After executing the exec command, you will get a shell to the docker container. Use the following command to optimize all databases and their tables:

mysqlcheck -p -o --all-databases;

The -p parameter instructs the command to ask for a password (which probably and hopefully have set).

To exit the console and close the connection just type exit; after you are done with the above command.

Note: Where we used the word website_db you need to use the name of your container. If you are not sure of the name of the container, you can list all of the containers with their names using the following command:

docker container ls;