Completely Disable InnoDB in MariaDB 10.6
Not really sure whether to ask this here or at dba.stackexchange.com but I figured here might be more appropriate since it's a server config issue.
I have a brand new MariaDB 10.6.5 install on Ubuntu 20.04
I've used MariaDB's own repositories that can be found at this URL - https://mariadb.org/download/?t=repo-config&d=20.04+%22focal%22&v=10.6&r_m=one
Following the instructions from the link above everything installs fine but things start to get messy after that.
I've got another machine with Ubuntu 20.04 and MariaDB 10.3.32 and things there are working fine, thanks to running these queries after install:
ALTER TABLE mysql.innodb_index_stats CHANGE table_name table_name VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE table_schema='mysql' AND engine='InnoDB';
and then adding the following lines in the [mysqld] section of /etc/mysql/my.cnf:
skip-innodb
default-storage-engine=MyISAM
I've tried doing the same for the 10.6 install (by adding the 2 lines inside the [mysqld] section in /etc/mysql/mariadb.conf.d/50-server.cnf) but for some reason InnoDB is still active and the default after I issue the SHOW ENGINES; query in the console.
Another thing I've noticed is the new sys database which also uses InnoDB and I can't find info anywhere on how to get rid of it. After deleting the database I had some problems and had to reinstall everything.
The reason I need this is mostly because of using cheap machines with 1GB RAM or less where MyISAM is still the king.
//EDIT:
Here's the output of my_print_defaults --mysqld --defaults-file=/etc/mysql/mariadb.cnf
--socket=/run/mysqld/mysqld.sock
--skip-innodb
--default-storage-engine=MyISAM
--user=mysql
--pid-file=/run/mysqld/mysqld.pid
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--lc-messages=en_US
--skip-external-locking
--bind-address=127.0.0.1
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
Here's systemctl status mariadb.service
mariadb.service - MariaDB 10.6.5 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Mon 2021-12-27 08:13:02; 3min 57s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 2884 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 2889 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2899 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (c>
Process: 2924 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2926 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 2907 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 10 (limit: 9451)
Memory: 63.1M
CGroup: /system.slice/mariadb.service
└─2907 /usr/sbin/mariadbd
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysql' as: /usr/bin/mysql
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Version check failed. Got the following error when calling the 'mysql' command line client
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: FATAL ERROR: Upgrade failed
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2941]: Checking for insecure root accounts.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 5 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost debian-start[2944]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
And here's journalctl -u mariadb.service -n 30
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27 8:13:01 0 [Note] /usr/sbin/mariadbd (server 10.6.5-MariaDB-1:10.6.5+maria~focal) starting as process 2907 ...
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27 8:13:01 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27 8:13:01 0 [Note] InnoDB: Number of pools: 1
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27 8:13:01 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Using Linux native AIO
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Completed initialization of buffer pool
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: 128 rollback segments are active.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: 10.6.5 started; log sequence number 33062; transaction id 4
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] InnoDB: Buffer pool(s) load completed at 211227 8:13:02
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] Server socket created on IP: '0.0.0.0'.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] Server socket created on IP: '::'.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 0 [Note] /usr/sbin/mariadbd: ready for connections.
Dec 27 08:13:02 testhost mariadbd[2907]: Version: '10.6.5-MariaDB-1:10.6.5+maria~focal' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
Dec 27 08:13:02 testhost systemd[1]: Started MariaDB 10.6.5 database server.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysql' as: /usr/bin/mysql
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Version check failed. Got the following error when calling the 'mysql' command line client
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: FATAL ERROR: Upgrade failed
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2941]: Checking for insecure root accounts.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27 8:13:02 5 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost debian-start[2944]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Daniel Black on the MariaDB Zulip tested this and it worked well enough in a container:
$ podman run --rm --env MARIADB_RANDOM_ROOT_PASSWORD=1 mariadb:10.6 --skip-innodb --default-storage-engine=Aria
2021-12-23 05:54:16+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.5+maria~focal started.
2021-12-23 05:54:17+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-12-23 05:54:17+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.5+maria~focal started.
...
2021-12-23 5:54:27 0 [Note] Plugin 'InnoDB' is disabled.
2021-12-23 5:54:27 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-12-23 5:54:27 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2021-12-23 5:54:27 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-12-23 5:54:27 0 [Note] Server socket created on IP: '::'.
2021-12-23 5:54:27 0 [Warning] 'proxies_priv' entry '@% root@9df410ee481f' ignored in --skip-name-resolve mode.
2021-12-23 5:54:27 0 [Note] mysqld: ready for connections.
Version: '10.6.5-MariaDB-1:10.6.5+maria~focal' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
show engines:
$ podman exec optimistic_kalam mariadb -p'4(V_6icy&6BPL4wt*cG!=vi"rh8gvW;Q' -e 'show engines'
Engine Support Comment Transactions XA Savepoints
CSV YES Stores tables as CSV files NO NO NO
MRG_MyISAM YES Collection of identical MyISAM tables NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
Aria DEFAULT Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables NO NO NO
MyISAM YES Non-transactional engine with good performance and small data footprint NO NO NO
SEQUENCE YES Generated tables filled with sequential values YES NO YES
InnoDB NO Supports transactions, row-level locking, foreign keys and encryption for tables NULL NULL NULL
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
The mysql.transaction_registry uses it as it needs a transactional storage engine.