Join Log in
Score:0
Sledge Hammer avatar Server

Completely Disable InnoDB in MariaDB 10.6

tm flag
Sledge Hammer

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)
567
0 + 0
Score:1
markusjm avatar Server
cn flag
markusjm

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.

0 + 0
danblack avatar
dz flag
danblack
note containers run the mysql_install_db under the same configuration and there's enough smarts there to detect the lack of innodb and adjust accordingly. If you are doing a fresh installation, remove /var/lib/mysql and `mysql_install_db --user mysql` after your configuration changes.
3
Reply
danblack avatar
dz flag
danblack
Not also in my example that [~markusjm] posed that I've used Aria instead of MyISAM for crash safety. Should also contain similar memory friendliness options.
1
Reply
Sledge Hammer avatar
tm flag
Sledge Hammer
@markusjm and @danblack - Thank you both for the help, but I still have no idea how to disable the engine through the config files. I tried the example above in the console after stopping the service and InnoDB is disabled indeed, but when I add the options in the config file I mentioned in the question - nothing happens. I got curious and replaced the whole `/etc/mysql` folder with the one from 10.3 and it works fine, but those files were from an even older version and I'm afraid they'll stop working at some point and would prefer to have a more up-to-date config.
0
Reply
danblack avatar
dz flag
danblack
Include information in your question. "nothing happens" is probably it failing to start however I can't tell why without further information. Show the server log `journalctl -u mariadb.service -n 30` and `systemctl status mariadb.service`. Replacing with a 10.3 configuration isn't advisable as deprecated options may have happened as suggested. For the clarity of the question include `my_print_defaults --mysqld` to see what configuration items are set.
0
Reply
Sledge Hammer avatar
tm flag
Sledge Hammer
@danblack - By "nothing happens" I mean that everything is the same as I've already stated in the question - I'm adding the changes in the file, the service starts fine but for some reason those config options are ignored and InnoDB is still active and default when I run `SHOW ENGINES;`. I've added the output of the logs you wanted... I've set pass for root, so it must be the reason for the root errors. I'm planning to wipe the whole machine and start from scratch in the next few days to see if somehow I've managed to screw something up, though I doubt the outcome will be any different.
0
Reply
danblack avatar
dz flag
danblack
Follow the remove datadir in the first comment and reinitialize using `mysql_install_db`. I looked though the code and innodb seems to initialize if those its files are there.
0
Reply
Sledge Hammer avatar
tm flag
Sledge Hammer
@danblack - Already tried that before my previous comment and the result was the same. I had the edited config with the 2 added options and did `service mariadb stop`, `rm -rf /var/lib/mysql/*` followed by `mysql_install_db --user mysql` and `service mariadb start` and still had innodb running.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.