Avatar of Robert Granlund
Robert Granlund
Flag for United States of America asked on

MySQL Database Error and how to fix

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
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
David Favor

8/22/2022 - Mon
slightwv (䄆 Netminder)

Image is missing.
David Favor

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

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

table_error.JPG
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
David Favor

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.
Robert Granlund

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

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?
table_error.JPG
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
David Favor

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.
Robert Granlund

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?
Chris Stanyon

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Robert Granlund

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?
Robert Granlund

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robert Granlund

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?
Chris Stanyon

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.
Robert Granlund

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Robert Granlund

ASKER
Instead of Using VARCHAR what would you suggest?
Chris Stanyon

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

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
David Favor

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 Favor

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.