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
LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Image is missing.
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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).
rgranlundAuthor Commented:
Sorry about that.  What a waste of time for me.  Geez! I look forward to your response.

table_error.JPG
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
rgranlundAuthor Commented:
"This almost always relates to using many variable length columns in keys."
What does that mean exactly.
rgranlundAuthor 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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
rgranlundAuthor 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?
Chris StanyonWebDevCommented:
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.
rgranlundAuthor 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?
rgranlundAuthor Commented:
In the image above, why are some Primary/Unique / Index highlighted and others are not?  What is the difference?
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
rgranlundAuthor 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?
Chris StanyonWebDevCommented:
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.
rgranlundAuthor 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
Chris StanyonWebDevCommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgranlundAuthor Commented:
Instead of Using VARCHAR what would you suggest?
Chris StanyonWebDevCommented:
I wouldn't - there's nothing wrong with using VARCHAR.
rgranlundAuthor 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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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 FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.