Wondering how to change mariadb to innodb? With the support of our MSSQL support services at Bobcares, we can give you a detailed note on how to do it.

Change mariadb to innodb

To change the database engine of a MySQL database table, open phpMyAdmin from the Site Tools menu.

if we have a database table called my table that is running the MyISAM engine and want to switch to InnoDB, we must:

Select the database in phpMyAdmin. Then, choose SQL, enter the following query, and press Go:

ALTER TABLE my_table ENGINE = InnoDB;

If the query is correctly run, the table’s database engine will be converted to InnoDB.

Converting Tables from MyISAM to InnoDB

It is typical to experience minor troubles with the process in this instance. Let us go through these issues and how to manage them.

mysqldump --no-data --all-databases >schemas
egrep 'CREATE|PRIMARY' schemas # Focusing on PRIMARY KEYs
egrep 'CREATE|FULLTEXT' schemas # Looking for FULLTEXT indexes
egrep 'CREATE|KEY' schemas # Looking for various combinations of indexes

INDEX Issues

Every InnoDB table has a PRIMARY KEY. If we do not provide one, then the first non-NULL UNIQUE key is used.

Finding tables without a PRIMARY KEY is much better. Even if it’s an artificial AUTO_INCREMENT, explicitly declare a PRIMARY KEY.

This is a stronger warning for InnoDB than for MyISAM, while it is not a strict necessity.

1. Check for redundant indexes with this in mind.

PRIMARY KEY(id),
INDEX(b), -- effectively the same as INDEX(b, id)
INDEX(b, id) -- effectively the same as INDEX(b)

2. Note subtle things like

PRIMARY KEY(id),
UNIQUE(b), -- keep for uniqueness constraint
INDEX(b, id) -- DROP this one

3. Also, since the PK and the data coexist:

PRIMARY KEY(id),
INDEX(id, b) -- DROP this one; it adds almost nothing

Contrast. This feature of MyISAM is not available in InnoDB; the value of ‘id’ will start over at 1 for each different value of ‘abc’:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (abc, id)

A way to simulate the MyISAM ‘feature’ may something like: What you want is this, but it won’t work because it is referencing the table twice:

INSERT INTO foo
(other, id, ...)
VALUES
(123, (SELECT MAX(id)+1 FROM foo WHERE other = 123), ...);

Instead, you need some variant on this. (You may already have a BEGIN…COMMIT.)

BEGIN;
SELECT @id := MAX(id)+1 FROM foo WHERE other = 123 FOR UPDATE;
INSERT INTO foo
(other, id, ...)
VALUES
(123, @id, ...);
COMMIT;

Having a transaction is mandatory to prevent another thread from grabbing the same id.

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (abc, id),
UNIQUE(id)

A long PK would make the Secondary keys bulky.

FULLTEXT  and SPATIAL indexes are not available in InnoDB.

The maximum length of an INDEX is different between the Engines. MyISAM allows 1000 bytes; InnoDB allows 767 bytes, just big enough for a

VARCHAR(255) CHARACTER SET utf8.

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The PRIMARY KEY is included in the data. Hence, SHOW TABLE STATUS will show and Index_length of 0 bytes  for a table with no secondary indexes.

The fields of the PRIMARY KEY are included in each Secondary key.

Same as MyISAM. Almost always

INDEX(a) -- DROP this one because the other one handles it.
INDEX(a,b)

Contrast. The data is stored in PK order. This means that “recent” records are ‘clustered’ together at the end.

This may give you better ‘locality of reference’ than in MyISAM.

Same as MyISAM. The optimizer almost never uses two indexes in a single SELECT.

SELECT in subqueries and UNIONs can independently pick indexes.

Subtle issue. When you DELETE a row, the AUTO_INCREMENT id will be burned. Ditto for REPLACE, which is a DELETE plus an INSERT.

Same as MyISAM. “Prefix” indexing is usually bad in both InnoDB and MyISAM. Example: INDEX(foo(30))

Non-INDEX Issues

Disk space for InnoDB is likely to be 2-3 times as much as for MyISAM.

MyISAM and InnoDB use RAM radically differently. If you change all your tables, you should make significant adjustments:

• key_buffer_size — small but non-zero; say, 10M;
• innodb_buffer_pool_size — 70% of available RAM

Backup scripts may need checking. A MyISAM table can backed up by copying three files.

Change to mysqldump. Since MariaDB 10.0 a hot copy can create.

Before MariaDB 5.5, the DATA DIRECTORY table option was not supported for InnoDB.

Since MariaDB 5.5 it is supported, but only in CREATE TABLE. INDEX DIRECTORY has no effect, since InnoDB does not use separate files for indexes.

To better balance the workload through several disks, the paths of some InnoDB log files can also be changed.

Understand autocommit and BEGIN/COMMIT.

• (default) autocommit = 1: In the absence of any BEGIN or COMMIT statements, every statement is a transaction by itself. This is close to the MyISAM behavior, but is not really the best.
• autocommit = 0: COMMIT will close a transaction and start another one. To me, this is kludgy.
• (recommended) BEGIN…COMMIT gives you control over what sequence of operation(s) are to be considered a transaction and “atomic”. Include the ROLLBACK statement if you need to undo stuff back to the BEGIN.

Perl’s DBIx::DWIW and Java’s JDBC have API calls to do BEGIN and COMMIT.

Test for errors everywhere! Because InnoDB uses row-level locking, it can stumble into deadlocks that you are not expecting.

The engine will automatically ROLLBACK to the BEGIN. The normal recovery is to redo, beginning at the BEGIN.

LOCK/UNLOCK TABLES — remove them. Replace them (sort of) with BEGIN … COMMIT.

In 5.1, ALTER ONLINE TABLE can speed up some operations significantly.

The “limits” on virtually everything are different between MyISAM and InnoDB.

Mixture of MyISAM and InnoDB

This is OK. But there are caveats.

• Firstly,RAM settings should adjust to accordingly.
• JOINing tables of different Engines works.
• A transaction that affects tables of both types can ROLLBACK InnoDB changes, but will leave MyISAM changes intact.
• Replication: MyISAM statements are replicated when finished; InnoDB statements are held until the COMMIT

PRIMARY KEY in PARTITION — Since every key must include the field on which you are PARTITIONing:

• This works: PRIMARY KEY
• This does not work for InnoDB: PRIMARY KEY

That is, an AUTO_INCREMENT will correctly increment, and be unique across all PARTITINOs, when it is the first field of the PRIMARY KEY, but not otherwise.

Conclusion

To conclude we have now learned more about how to change mariadb to innodb. We have also learned how to use it with the support of our MSSQL support services at Bobcares.