MySQL Database Error and how to fix

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).

ASKER

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

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.

ASKER

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

ASKER

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?

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.

ASKER

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?

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.

ASKER

"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?

ASKER

In the image above, why are some Primary/Unique / Index highlighted and others are not? What is the difference?

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.

ASKER

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?

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.

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Instead of Using VARCHAR what would you suggest?

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

ASKER

I'm a little confused. How many columns can you have in a table? And how do you perform the equation for the size?

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.