MySQL Database Error and how to fix

Robert Granlund
Robert Granlund used Ask the Experts™
on
My SQL Table Error.  I have been creating new rows in MySQL and I received an error that I do not understand. Can you explain and how do I fix this?
I have attached an image with the error
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Image is missing.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Also, best to do a cut + paste of the entire text of your error, as images are very difficult to read + refer too (no way to cut + paste snippets).

Author

Commented:
Sorry about that.  What a waste of time for me.  Geez! I look forward to your response.

table_error.JPG
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

David FavorFractional CTO
Distinguished Expert 2018

Commented:
The dreaded "Row size too large" error.

This almost always relates to using many variable length columns in keys.

Everything works well, until the total size of keys exceeds a certain size (varies between MySQL + MariaDB versions).

The easiest fix is to setup row compression for you new database (where you're restoring data). This roughly increases potential row size by 30%-50%+.

This may be sufficient to fix your problem + may not.

First you must to a temp fix, like row compression to get your data into a database.

Then the real/permanent fix will be to redesign your database to have sensible schemas... which produce small rows...

Tip: The bad news is, likely some or all your current data is corrupt.

If you look in your database instance logs, where you made your original backup, you'll likely see many errors of the form...

2017-09-09  0:49:13 140494491080448 [Warning] InnoDB: Cannot add field `nextofkinemail` in table `sometable`.`FHOttawa_clients` because after adding it, the row size is 8142 which is greater than maximum allowed size (8126) for a record on index leaf page.

Open in new window


Each of these errors means some data has been lost in some row.

This problem is common for custom database projects, where the database designer was unaware of this limitation.

So... even after you can import your data (using row compression), likely much or all your data is corrupt in some way.

You'll have to determine how to fix these corrupt records, which will be unique to your database schema + work flow of your data.

Author

Commented:
"This almost always relates to using many variable length columns in keys."
What does that mean exactly.

Author

Commented:
Is there a way to change this so there are not so many Keys?  The DB has an additional 50 rows.  That does not seem like a lot to me.  But the Primary/Unique / Index on all of those rows seems like a lot?
table_error.JPG
David FavorFractional CTO
Distinguished Expert 2018

Commented:
You asked, "Is there a way to change this so there are not so many Keys?"

Yes.

And...

What you're describing is a full database audit + redesign.

This is far beyond the scope of a simple EE comment, as all your code + how indexes are used... along with App speed + resource usage requirements will require review.

Best to hire someone to assist you with this work, as this work can be complex + time consuming.

Author

Commented:
The DB is new.  Can I just drop those rows and re-create them? Like the rows Data and Sellar?  Or do I need to rebuild the db from scratch with all of my fields but only one key and one index?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Looking at your schema, it seems a re-design is in order. You have user_id and id. I would guess that you only need user_id and then use that as your Primary. You may want to add other INDEX columns depending on your needs (UNIQUE for email address etc.). You should also add an INDEX to any fields you use as a Foreign Key, or that you often search on.

For the datatype, use the most restictive that you're going to need (type and length).

Having an entire record as a Primary key does seem very 'odd' to say the least.

If this is a new table with very few records, then now is the time to get it right.

Author

Commented:
"For the datatype, use the most restictive that you're going to need (type and length)."
Which is what?

How do I remove The Primary Key from certain fields / Columns?

Author

Commented:
In the image above, why are some Primary/Unique / Index highlighted and others are not?  What is the difference?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
The problem is all the TEXT + LONGTEXT type indexes.

LONGTEXT can be 4G in size.

So your first step (as both I + Chris suggested) is a full database redesign.

You'll have to change all TEXT + LONGTEXT to VARCHAR (as a start).

Then do the math (based on your MySQL/MariaDB variant) to determine max row index size + max row size, then go from there.

This is a very long + complex answer.

As I said before the real answer revolves around a full redesign + also a code redesign, as your code will have to change to match your new SCHEMA.

Author

Commented:
I'm kind of stuck here in a fast and hard learning curve because I am going to have to do this.  Can you give me a brief description/explanation of:
Then do the math (based on your MySQL/MariaDB variant) to determine max row index size + max row size, then go from there.

ALSO, some of those TEXT Indexes are filled with a lot of HTML.  Can I change those to VARCHAR and can I remove the INDEX from them?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
OK.

By most restrictive, I mean the least amount of storage space for any given column. For example, don't use a 50 byte column for something that will only ever hold 5 bytes of data. If you're storing a date, use a date field and not a text one. If you want to store a boolean, use a BIT. VARCHARs only actually take up the amount of data that is stored, so the definition is less of a problem - that's more about data integrity (i.e NOT allowing 50 characters to be stored in a Phone Number column!)

Looking at your screenshot, I think I made an error earlier. It doesn't actually show which columns are INDEXed or PRIMARY keys. The column that says Primary Key is just there to allow you to set the Primary. The reason some are highlighted and others aren't is because you can't add an index to a TEXT column, so those options are greyed out.

From within PHPMyAdmin, locate your table in the left hand column, and you should see the Indexes icon. Expand that and it'll show you what INDEXes you have set.

Earlier, you showed the screen shot and followed up by sating the DB has an additional 50 rows. In DB terms, a row is akin to a record. Did you mean to say it has 50 additional columns. It may be that you're just trying to store too much data in the row.

The maximum size of a row is about 8k, so for example, if you tried to design a table that had 7 VARCHAR(10000) columns it would fail as that would exceed the max row size. When using Text columns, they only actually take a up a few bytes as the data isn't actually stored in the DB.

It may be that you need to reconsider the design of your Database. One of the fundamentals of Database Design is something called normalisation. It may be that you need to go through this process and break your one big table into several smaller, related tables.

Without seeing your entire table Schema, I can't be sure what you current row size is.

Author

Commented:
I have dumped the DB and want to re-install it as COMPRESSED but I am getting an error.

Here is the Create:
CREATE TABLE `data` (id INT PRIMARY KEY) ROW_FORMAT=COMPRESSED; (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `surgeon` varchar(255) NOT NULL,
  `ii_address` varchar(256) NOT NULL,
  `ii_email` varchar(256) NOT NULL,
  `ii_phone` varchar(50) NOT NULL,
  `ii_pi_name` varchar(256) NOT NULL,
  `ii_pi_phone` varchar(50) NOT NULL,
  `ii_pi_email` varchar(256) NOT NULL,
  `date` varchar(255) NOT NULL,
  `seller` varchar(255) DEFAULT NULL,
  `data` longtext NOT NULL,
  `si` text NOT NULL,
  `hsi` text NOT NULL,
  `di` text NOT NULL,
  `pi` text NOT NULL,
  `osi` text CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
  `oi` text NOT NULL,
  `cai` text NOT NULL,
  `sign` longtext NOT NULL,
  `ii_pi_name2` text NOT NULL,
  `ii_pi_phone2` text NOT NULL,
  `ii_pi_email2` text NOT NULL,
  `ii_pi_name3` text NOT NULL,
  `ii_pi_phone3` text NOT NULL,
  `ii_pi_email3` text NOT NULL,
  `oi_hospitallist` text NOT NULL,
  `oi_unitname` text NOT NULL,
  `oi_unitnumber` text NOT NULL,
  `oi_payermix` text NOT NULL,
  `oi_unitnumber2_m` text NOT NULL,
  `oi_unitnumber2_mc` text NOT NULL,
  `oi_reimbursement_m` text NOT NULL,
  `oi_comment_mc` text NOT NULL,
  `oi_comment_md` text NOT NULL,
  `oi_unitnumber2_pi` text NOT NULL,
  `oi_reimbursement_pi` text NOT NULL,
  `oi_comment_pi` text NOT NULL,
  `oi_unitnumber2_pp` text NOT NULL,
  `oi_comment_pp` text NOT NULL,
  `hi_name` text NOT NULL,
  `hi_address` text NOT NULL,
  `hi_contact` text NOT NULL,
  `hi_email` text NOT NULL,
  `hi_phone` text NOT NULL,
  `hi_name2` text NOT NULL,
  `hi_address2` text NOT NULL,
  `hi_contact2` text NOT NULL,
  `hi_email2` text NOT NULL,
  `hi_phone2` text NOT NULL,
  `hi_name3` text NOT NULL,
  `hi_address3` text NOT NULL,
  `hi_contact3` text NOT NULL,
  `hi_email3` text NOT NULL,
  `hi_phone3` text NOT NULL,
  `di_name1` text NOT NULL,
  `di_contact1` text NOT NULL,
  `di_email1` text NOT NULL,
  `di_phone1` text NOT NULL,
  `di_name2` text NOT NULL,
  `di_contact2` text NOT NULL,
  `di_email2` text NOT NULL,
  `di_phone2` text NOT NULL,
  `di_name3` text NOT NULL,
  `di_contact3` text NOT NULL,
  `di_email3` text NOT NULL,
  `di_phone3` text NOT NULL,
  `di_name4` text NOT NULL,
  `di_contact4` text NOT NULL,
  `di_email4` text NOT NULL,
  `di_phone4` text NOT NULL,
  `di_name5` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here is the error:
Error
Static analysis:

2 errors were found during analysis.

Unexpected beginning of statement. (near "`id`" at position 4)
Unrecognized statement type. (near "int" at position 9)
SQL query:

( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `surgeon` varchar(255) NOT NULL, `ii_address` varchar(256) NOT NULL, `ii_email` varchar(256) NOT NULL, `ii_phone` varchar(50) NOT NULL, `ii_pi_name` varchar(256) NOT NULL, `ii_pi_phone` varchar(50) NOT NULL, `ii_pi_email` varchar(256) NOT NULL, `date` varchar(255) NOT NULL, `seller` varchar(255) DEFAULT NULL, `data` longtext NOT NULL, `si` text NOT NULL, `hsi` text NOT NULL, `di` text NOT NULL, `pi` text NOT NULL, `osi` text CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL, `oi` text NOT NULL, `cai` text NOT NULL, `sign` longtext NOT NULL, `ii_pi_name2` text NOT NULL, `ii_pi_phone2` text NOT NULL, `ii_pi_email2` text NOT NULL, `ii_pi_name3` text NOT NULL, `ii_pi_phone3` text NOT NULL, `ii_pi_email3` text NOT NULL, `oi_hospitallist` text NOT NULL, `oi_unitname` text NOT NULL, `oi_unitnumber` text NOT NULL, `oi_payermix` text NOT NULL, `oi_unitnumber2_m` text NOT NULL, `oi_unitnumber2_mc` text NOT NULL, `oi_reimbursement_m` text NOT NULL, `oi_comment_mc` text NOT NULL, `oi_comment_md` text NOT NULL, `oi_unitnumber2_pi` text NOT NULL, `oi_reimbursement_pi` text NOT NULL, `oi_comment_pi` text NOT NULL, `oi_unitnumber2_pp` text NOT NULL, `oi_comment_pp` text NOT NULL, `hi_name` text NOT NULL, `hi_address` text NOT NULL, `hi_contact` text NOT NULL, `hi_email` text NOT NULL, `hi_phone` text NOT NULL, `hi_name2` text NOT NULL, `hi_address2` text NOT NULL, `hi_contact2` text NOT NULL, `hi_email2` text NOT NULL, `hi_phone2` text NOT NULL, `hi_name3` text NOT NULL, `hi_address3` text NOT NULL, `hi_contact3` text NOT NULL, `hi_email3` text NOT NULL, `hi_phone3` text NOT NULL, `di_name1` text NOT NULL, `di_contact1` text NOT NULL, `di_email1` text NOT NULL, `di_phone1` text NOT NULL, `di_name2` text NOT NULL, `di_contact2` text NOT NULL, `di_email2` text NOT NULL, `di_phone2` text NOT NULL, `di_name3` text NOT NULL, `di_contact3` text NOT NULL, `di_email3` text NOT NULL, `di_phone3` text NOT NULL, `di_name4` text NOT NULL, `di_contact4` text NOT NULL, `di_email4` text NOT NULL, `di_phone4` text NOT NULL, `di_name5` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `surgeon` varchar(255' at line 2
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
You've got your syntax slightly wrong at the start:

CREATE TABLE `data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
   ...
   ...
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Open in new window

And there's nothing in your table that suggests your row is too large. The only real space taken up are the varchar() fields (plus a little for the TEXT columns), and they're clearly not that large, so adding compression will only likely slow down your app rather than solve any problems.

The biggest issue I see when looking at your table is the complete lack of normalisation. All those address2, address3, contact 2, contact 3 columns should all be split out into other tables. The way you've prefixed your fields (ii, oi, hi, di) also suggests that you're trying to wrap several datasets all into one table. If you normalise your DB now, it will pay dividends down the road - it makes it more performant and scalable.

You might also want to consider a UTF character set. Using Latin will limit the type of data (characters) you can store (foreign letters etc)

Author

Commented:
Instead of Using VARCHAR what would you suggest?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
I wouldn't - there's nothing wrong with using VARCHAR.

Author

Commented:
I'm a little confused.  How many columns can you have in a table? And how do you perform the equation for the size?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
How many columns can you have in a table?

Column count isn't the issue.

Is the number of characters as total row size + sometimes index (total bytes in all keys size).

This is super complex to get a grip on + varies from database to database.

For your situation problem is the massive number of TEXT + LONGTEXT fields you have in one row.

For rows only a few bytes over, you can use info like https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large to fix the problem.

In your case unlikely this will work.

You will have to research this problem (search for - mysql 8126 row size - understanding the problem) then redesign your database.

So in your case you'd change the TEXT + LONGTEXT index columns to VARCHAR to attempt a fix + again, likely this won't work.

Fixing this problem is complex. Dig through all the docs first + understand the problem. Once your level of understanding increases, you can consider how to best to redesign your table SCHEMA + related code.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Tip: Take a look at the WordPress database table design.

One of the reason WordPress is so prevalent, is the database works in all cases. The SCHEMA is a bit ugly + it also trades lower performance for tables that always work.

https://codex.wordpress.org/Database_Description provides an overview of tables used by WordPress Core.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial