Php can easily exceed its maximum allowed RAM allocation when trying to retrieve mediumtext fields from mysql.

Background:

Through some obscure misunderstanding/bug, I have from time to time ended up with a MySQL database that has mediumtext instead of the text fields that I had intended this leads to a massive performance problem when using php.

I usually avoid text fields to begin with, preferring varchars where possible because they are easily indexed and rapidly searched, but for larger pieces of text >255 characters long such as webpages, reviews etc, it is often appropriate to use text fields. But sometimes, when creating text fields from php or when changing character encoding either from php or via phpMyAdmin I have inadvertently created the dreaded mediumtext.

Wait… what’s wrong with mediumtext?

Well nothing really, but somewhere deep down in one of the php mysql fetch commands, php ends up allocating enough ram to handle the maximum amount of text that could be held in the datatype being fetched. For a text field, this is 64kiB. No problems here. But if we try to fetch a mediumtext field, we now are looking at around 16MiB per row per mediumtext column! It only takes a few rows and/or columns in the result to soon push this above even some fairly generous php ram limits. One’ s ram bandwidth also rapidly becomes a very real and very unexpected bottleneck. Remember, we’re just calling up relatively short strings of text here!

NOOOO!!!!! I’ve accidentally turned all of my text fields into mediumtexts and don’t want to manually fix them all!!!

Some searching around revealed a solution here (scroll down to find the solution from “Ivan”) to changing the character encoding of all your tables easily (how I originally created the problem!!).

The code is easily modified to instead search for fields with mediumtexts and provide a set of queries to change these back to texts. BE SURE TO CHECK THAT NONE OF YOUR MEDIUMTEXTS WERE DELIBERATE THOUGH!
Just substitute in your problematic table into “your_table_name_here” and be sure to execute the queries on the correct database (after checking them).

SELECT CONCAT('ALTER TABLE `', t.`TABLE_NAME`,'` CHANGE `', 
t.`COLUMN_NAME`,'` `', t.`COLUMN_NAME`, '` TEXT CHARACTER SET utf8') 
as stmt FROM `information_schema`.`COLUMNS` t 
WHERE t.`TABLE_SCHEMA` = 'your_table_name_here' 
AND t.`DATA_TYPE` = 'mediumtext'

As for changing character encoding, I’m not sure how to do it without this side effect of creating mediumtext fields. My suggestion would be to first search for any fields that are deliberately mediumtext and note them down (substitute your_database_name_here):

SELECT t.`TABLE_NAME`, t.`COLUMN_NAME` 
FROM `information_schema`.`COLUMNS` t 
WHERE t.`TABLE_SCHEMA` = 'your_database_name_here' 
AND t.`DATA_TYPE` = 'mediumtext' 

then run the original queries to change character encoding (substitute your_database_name_here):

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', 
t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 
COLLATE utf8_general_ci;') as stmt 
FROM `information_schema`.`TABLES` t 
WHERE t.`TABLE_SCHEMA` = 'your_database_name_here'

and finally use my solution above to put any unwanted mediumtexts back to text (skipping any that were noted down as being mediumtext for good reason).

Leave a Reply

Your email address will not be published. Required fields are marked *