One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable.
Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for InnoDB-internal reasons).
We want to know about FULLTEXT indexes. With recent InnoDB versions also supporting FULLTEXT we need to check not just whether a table has such an index, but actually which engine it is.
Spatial indexes. While both InnoDB and MyISAM can deal with spatial datatypes (POINT, GEOMETRY, etc), only MyISAM has the spatial indexes.
Naturally, checking a schema in the server is more effective than going through other sources and possibly missing bits. On the downside, the only viable way to get this info out of MariaDB is INFORMATION_SCHEMA, but because of the way it’s implemented queries tend to be slow and resource intensive. So essentially we do need to ask I_S, but do it as efficiently as possible (we’re dealing with production systems). We have multiple separate questions to ask, which normally we’d ask in separate queries, but in case of I_S that’s really something to avoid. So that’s why it’s all integrated into the single query below, catching every permutation of “not InnoDB”, “lacks primary key”, “has fulltext or spatial index”. We skip the system databases and any VIEWs.
We use the lesser known mysql client command ‘tee’ to output the data into a file, and close it after the query.
We publish the query not as a work of art – I don’t think it’s that pretty! We’d like you to see because we don’t care for secrets and also because if there is any way you can reach the same objective using a less resource intensive approach, we’d love to hear about it! This is one of the very few cases where we care only about efficiency, not how pretty the query looks. That said, of course I’d prefer it to be easily readable.
If you regard it purely as a query to be used for Galera, then you can presume it’ll be run on MariaDB 5.5 or later – since 5.3 and above has optimised subqueries, perhaps you can do something with that.
If you spot any other flaw or missing bit, please comment on that too. thanks!
CONCAT(t.table_schema,’.’,t.table_name) as tbl,
IF(ISNULL(c.constraint_name),’NOPK’,”) AS nopk,
IF(s.index_type = ‘FULLTEXT’,’FULLTEXT’,”) as ftidx,
IF(s.index_type = ‘SPATIAL’,’SPATIAL’,”) as gisidx
FROM information_schema.tables AS t
LEFT JOIN information_schema.key_column_usage AS c
ON (t.table_schema = c.constraint_schema AND t.table_name = c.table_name
AND c.constraint_name = ‘PRIMARY’)
LEFT JOIN information_schema.statistics AS s
ON (t.table_schema = s.table_schema AND t.table_name = s.table_name
AND s.index_type IN (‘FULLTEXT’,’SPATIAL’))
WHERE t.table_schema NOT IN (‘information_schema’,’performance_schema’,’mysql’)
AND t.table_type = ‘BASE TABLE’
AND (t.engine <> ‘InnoDB’ OR c.constraint_name IS NULL OR s.index_type IN (‘FULLTEXT’,’SPATIAL’))
ORDER BY t.table_schema,t.table_name;
Credit: the basis of the “find tables without a PK” is based on SQL by Sheeri Cabral and Giuseppe Maxia.
Filed under: Uncategorized by Arjen Lentz on Tuesday, April 23, 2013 | Social tagging: cluster > Galera > InnoDB > replication
2 Responses to “Galera pre-deployment check”
Patryk Pomykalski, on April 23rd, 2013 at 9:24 am Said:
You can also dump table structure and run it locally. I use common_schema that way because it doesn’t run on mysql 5.0.
Arjen Lentz, on April 29th, 2013 at 3:01 am Said:
Yes sure, absolutely and thanks for noting this as I didn’t mention it in my original post.
To clarify: for us, this is not always an option with clients, so we do require the query in the way I described.
Copying stuff (even just structure without data) outside of the client environment is not always possible/allowed.
Leave a Comment
Mail (will not be published) (required)
Notify me of followup comments via e-mail. You can also subscribe without commenting.
reCAPTCHA challenge image
Type the text
Privacy & Terms Get a new challenge
Get an audio challenge
« Query pattern: OR across different tables Fedora 19 – MariaDB Test Day 2013-04-30 «