Link to home
Start Free TrialLog in
Avatar of Robert Granlund
Robert GranlundFlag for United States of America

asked on

MySQL Error

My SQL Error.  Can anyone help me fix this error?  I'm not sure what MySQL is trying to tell me:

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

My Create Table:
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;
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

It's probably the ';' after COMPRESSED.  Remove it.
Avatar of Robert Granlund

ASKER

When I do that, I get this error:
Error
Static analysis:

2 errors were found during analysis.

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

-- -- Database: `rom32825_dev` -- -- -------------------------------------------------------- -- -- Table structure for table `data` -- 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

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 12
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@dave @Chris
I'm a little confused.  How many columns can you have in a table? And how do you perform the equation for the size?
Theoretically, you can have up to 4096 columns in a table, but in reality you'll likely hit the maximum row size before ever hitting the column limit.

To calculate the maximum row size, you need to know how much storage space is needed for each column. This is defined by the datatype and the length of the field. For example, a column set to a datatype of INT will take 4 bytes, a type of DATE will take 3. The VARCHAR type will take up a variable amount of space, depending on the length of the field and the character set. For example, a VARCHAR(255) will take up 255 bytes if the character set is Latin (1 byte per character). If the character set is UTF8, then it can take twice that space as each character can take up 2 bytes - so a VARCHAR(50) could actually take up 100 bytes. Something like UTF8MB4 can take upto 4 bytes per character. There is also a little overhead to each field, so a VARCHAR column can take an extra couple of bytes, which is used to store the length of the data.

You can look up all the data storage requirements here -> https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
What is the max size it bytes a table can have in columns?
Not sure what you mean.

The maximum number of columns a table can have is 4096. The maximum row size is 64k.