Google Contacts MYSQL table

toplisek · June 18, 2021, 11:09am

I try to create a table using Google contacts.
How to fix an error as table does not create?
2 errors were found during analysis.

A comma or a closing bracket was expected. (near "`Given Name`" at position 57)
Unrecognized statement type. (near "MAX" at position 78)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX) NULL Given NameVARCHAR(MAX) NULLAdditional Name VARCHAR(MAX) NUL...' at line 2

CREATE TABLE GoogleContacts (
`Name` VARCHAR(MAX) NULL
`Given Name` VARCHAR(MAX) NULL
`Additional Name` VARCHAR(MAX) NULL
`Family Name` VARCHAR(MAX) NULL
`Yomi Name` VARCHAR(MAX) NULL
`Given Name Yomi` VARCHAR(MAX) NULL
`Additional Name Yomi` VARCHAR(MAX) NULL
`Family Name Yomi` VARCHAR(MAX) NULL
`Name Prefix` VARCHAR(MAX) NULL
`Name Suffix` VARCHAR(MAX) NULL
`Initials` VARCHAR(MAX) NULL
`Nickname` VARCHAR(MAX) NULL
`Short Name` VARCHAR(MAX) NULL
`Maiden Name` VARCHAR(MAX) NULL
`Birthday` VARCHAR(MAX) NULL
`Gender` VARCHAR(MAX) NULL
`Location` VARCHAR(MAX) NULL
`Billing Information` VARCHAR(MAX) NULL
`Directory Server` VARCHAR(MAX) NULL
`Mileage` VARCHAR(MAX) NULL
`Occupation` VARCHAR(MAX) NULL
`Hobby` VARCHAR(MAX) NULL
`Sensitivity` VARCHAR(MAX) NULL
`Priority` VARCHAR(MAX) NULL
`Subject` VARCHAR(MAX) NULL
`Notes` VARCHAR(MAX) NULL
`Language` VARCHAR(MAX) NULL
`Photo` VARCHAR(MAX) NULL
`Group Membership` VARCHAR(MAX) NULL
`E-mail 1 - Type` VARCHAR(MAX) NULL
`E-mail 1 - Value` VARCHAR(MAX) NULL
`Phone 1 - Type` VARCHAR(MAX) NULL
`Phone 1 - Value` VARCHAR(MAX) NULL
`Phone 2 - Type` VARCHAR(MAX) NULL
`Phone 2 - Value` VARCHAR(MAX) NULL
`Address 1 - Type` VARCHAR(MAX) NULL
`Address 1 - Formatted` VARCHAR(MAX) NULL
`Address 1 - Street` VARCHAR(MAX) NULL
`Address 1 - City` VARCHAR(MAX) NULL
`Address 1 - PO Box` VARCHAR(MAX) NULL
`Address 1 - Region` VARCHAR(MAX) NULL
`Address 1 - Postal Code` INT NULL
`Address 1 - Country` VARCHAR(MAX) NULL
`Address 1 - Extended Address` VARCHAR(MAX) NULL
`Organization 1 - Type` VARCHAR(MAX) NULL
`Organization 1 - Name` VARCHAR(MAX) NULL
`Organization 1 - Yomi Name` VARCHAR(MAX) NULL
`Organization 1 - Title` VARCHAR(MAX) NULL
`Organization 1 - Department` VARCHAR(MAX) NULL
`Organization 1 - Symbol` VARCHAR(MAX) NULL
)

droopsnoot · June 18, 2021, 11:56am

Do you need commas after each column description?

toplisek · June 18, 2021, 12:24pm

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX) NULL, Given NameVARCHAR(MAX) NULL,Additional Name VARCHAR(MAX) N...' at line 2

CREATE TABLE GoogleContacts (
`Name` VARCHAR(MAX) NULL,
`Given Name` VARCHAR(MAX) NULL,
`Additional Name` VARCHAR(MAX) NULL,
`Family Name` VARCHAR(MAX) NULL,
`Yomi Name` VARCHAR(MAX) NULL,
`Given Name Yomi` VARCHAR(MAX) NULL,
`Additional Name Yomi` VARCHAR(MAX) NULL,
`Family Name Yomi` VARCHAR(MAX) NULL,
`Name Prefix` VARCHAR(MAX) NULL,
`Name Suffix` VARCHAR(MAX) NULL,
`Initials` VARCHAR(MAX) NULL,
`Nickname` VARCHAR(MAX) NULL,
`Short Name` VARCHAR(MAX) NULL,
`Maiden Name` VARCHAR(MAX) NULL,
`Birthday` VARCHAR(MAX) NULL,
`Gender` VARCHAR(MAX) NULL,
`Location` VARCHAR(MAX) NULL,
`Billing Information` VARCHAR(MAX) NULL,
`Directory Server` VARCHAR(MAX) NULL,
`Mileage` VARCHAR(MAX) NULL,
`Occupation` VARCHAR(MAX) NULL,
`Hobby` VARCHAR(MAX) NULL,
`Sensitivity` VARCHAR(MAX) NULL,
`Priority` VARCHAR(MAX) NULL,
`Subject` VARCHAR(MAX) NULL,
`Notes` VARCHAR(MAX) NULL,
`Language` VARCHAR(MAX) NULL,
`Photo` VARCHAR(MAX) NULL,
`Group Membership` VARCHAR(MAX) NULL,
`E-mail 1 - Type` VARCHAR(MAX) NULL,
`E-mail 1 - Value` VARCHAR(MAX) NULL,
`Phone 1 - Type` VARCHAR(MAX) NULL,
`Phone 1 - Value` VARCHAR(MAX) NULL,
`Phone 2 - Type` VARCHAR(MAX) NULL,
`Phone 2 - Value` VARCHAR(MAX) NULL,
`Address 1 - Type` VARCHAR(MAX) NULL,
`Address 1 - Formatted` VARCHAR(MAX) NULL,
`Address 1 - Street` VARCHAR(MAX) NULL,
`Address 1 - City` VARCHAR(MAX) NULL,
`Address 1 - PO Box` VARCHAR(MAX) NULL,
`Address 1 - Region` VARCHAR(MAX) NULL,
`Address 1 - Postal Code` INT NULL,
`Address 1 - Country` VARCHAR(MAX) NULL,
`Address 1 - Extended Address` VARCHAR(MAX) NULL,
`Organization 1 - Type` VARCHAR(MAX) NULL,
`Organization 1 - Name` VARCHAR(MAX) NULL,
`Organization 1 - Yomi Name` VARCHAR(MAX) NULL,
`Organization 1 - Title` VARCHAR(MAX) NULL,
`Organization 1 - Department` VARCHAR(MAX) NULL,
`Organization 1 - Symbol` VARCHAR(MAX) NULL
)

droopsnoot · June 18, 2021, 12:29pm

That (and a google search where someone asked for the MySQL equivalent to VARCHAR(MAX)) suggests that you have to specify a length for the field. Further reading suggests that using MAX is a feature of MS SQL Server.

toplisek · June 18, 2021, 12:56pm

Varchar in MySQL is a data type used for storing text whose length can have a maximum of 65535 characters.

I have done the code at the bottom and it is without an error. Why is a table GoogleContacts not created and pushed inside QUERY as an error does not exists?

CREATE TABLE GoogleContacts (
`Name` VARCHAR(65535) NULL,
`Given Name` VARCHAR(65535) NULL,
`Additional Name` VARCHAR(65535) NULL,
`Family Name` VARCHAR(65535) NULL,
`Yomi Name` VARCHAR(65535) NULL,
`Given Name Yomi` VARCHAR(65535) NULL,
`Additional Name Yomi` VARCHAR(65535) NULL,
`Family Name Yomi` VARCHAR(65535) NULL,
`Name Prefix` VARCHAR(65535) NULL,
`Name Suffix` VARCHAR(65535) NULL,
`Initials` VARCHAR(65535) NULL,
`Nickname` VARCHAR(65535) NULL,
`Short Name` VARCHAR(65535) NULL,
`Maiden Name` VARCHAR(65535) NULL,
`Birthday` VARCHAR(65535) NULL,
`Gender` VARCHAR(65535) NULL,
`Location` VARCHAR(65535) NULL,
`Billing Information` VARCHAR(65535) NULL,
`Directory Server` VARCHAR(65535) NULL,
`Mileage` VARCHAR(65535) NULL,
`Occupation` VARCHAR(65535) NULL,
`Hobby` VARCHAR(65535) NULL,
`Sensitivity` VARCHAR(65535) NULL,
`Priority` VARCHAR(65535) NULL,
`Subject` VARCHAR(65535) NULL,
`Notes` VARCHAR(65535) NULL,
`Language` VARCHAR(65535) NULL,
`Photo` VARCHAR(65535) NULL,
`Group Membership` VARCHAR(65535) NULL,
`E-mail 1 - Type` VARCHAR(65535) NULL,
`E-mail 1 - Value` VARCHAR(65535) NULL,
`Phone 1 - Type` VARCHAR(65535) NULL,
`Phone 1 - Value` VARCHAR(65535) NULL,
`Phone 2 - Type` VARCHAR(65535) NULL,
`Phone 2 - Value` VARCHAR(65535) NULL,
`Address 1 - Type` VARCHAR(65535) NULL,
`Address 1 - Formatted` VARCHAR(65535) NULL,
`Address 1 - Street` VARCHAR(65535) NULL,
`Address 1 - City` VARCHAR(65535) NULL,
`Address 1 - PO Box` VARCHAR(65535) NULL,
`Address 1 - Region` VARCHAR(65535) NULL,
`Address 1 - Postal Code` INT NULL,
`Address 1 - Country` VARCHAR(65535) NULL,
`Address 1 - Extended Address` VARCHAR(65535) NULL,
`Organization 1 - Type` VARCHAR(65535) NULL,
`Organization 1 - Name` VARCHAR(65535) NULL,
`Organization 1 - Yomi Name` VARCHAR(65535) NULL,
`Organization 1 - Title` VARCHAR(65535) NULL,
`Organization 1 - Department` VARCHAR(65535) NULL,
`Organization 1 - Symbol` VARCHAR(65535) NULL
)

droopsnoot · June 18, 2021, 5:39pm

Reading a bit more, it appears that the maximum row size in MySQL is 64kb. So you can’t have more than one column of that size without overflowing, and in practice it’ll be a little bit less than that. I’m surprised you didn’t get an error message when you tried to create the table, but that does seem to be why it won’t create.

Why on earth do those columns need to be so massive? Do you know anyone with a first name 64kb long? You’d certainly never write to them if you did.

MySQL :: MySQL 5.7 Reference Manual :: 8.4.7 Limits on Table Column Count and Row Size

Dr_John · June 18, 2021, 6:44pm

Then there’s the small problem called normalisation.
So all your contacts will have only one hobby, only one email, only one occupation (at several points in my life I have had several occupations at the same time, each with a different organisation) and so on. And none of the occupations or organisations had a name that was 65535 characters long - at 100 characters per line that’s 655 lines of 100 characters for an email, an organisation name. Etc. Shorten everything to say 100 or 150 characters, and you will never have to worry about lengthening anything.

r937 · June 18, 2021, 6:51pm

then there’s the smell test

`Initials` VARCHAR(65535) NULL,
`Nickname` VARCHAR(65535) NULL,
`Short Name` VARCHAR(65535) NULL,
`Maiden Name` VARCHAR(65535) NULL,
`Birthday` VARCHAR(65535) NULL,
`Gender` VARCHAR(65535) NULL,

really? 65 thousand bytes for initials? short name? gender?

toplisek · June 19, 2021, 9:09pm

I have actually tested the length and it will be tested lower length. So, it is only testing at the start.
CRM system uses PERSONAL and COMPANY dividers. In my case it is important COMPANY view.
You quoted very important point: So all your contacts will have only one hobby, only one email, only one occupation…
Google Contact uses COMPANY as a very important part. Occupation is not important as usually it is connected and related to an updated record and company ID.
It means it can be added the same company and several personal names. Even one company does not need personal data and an email is a general company email. IMHOP it should be added inside Google Contacts also another important field: Industry.
For a gender it is really not requested 65535…it needs optimization.

toplisek · June 21, 2021, 10:24am

I have tested and modified 65535 into 250.

What should be modified in this line as I should not modify the structure from Google Contacts table?

CREATE TABLE GoogleContacts (
`id` INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`Name` VARCHAR(250) NULL,
`Given Name` VARCHAR(250) NULL,
`Additional Name` VARCHAR(250) NULL,
`Family Name` VARCHAR(250) NULL,
`Yomi Name` VARCHAR(250) NULL,
`Given Name Yomi` VARCHAR(250) NULL,
`Additional Name Yomi` VARCHAR(250) NULL,
`Family Name Yomi` VARCHAR(250) NULL,
`Name Prefix` VARCHAR(250) NULL,
`Name Suffix` VARCHAR(250) NULL,
`Initials` VARCHAR(250) NULL,
`Nickname` VARCHAR(250) NULL,
`Short Name` VARCHAR(250) NULL,
`Maiden Name` VARCHAR(250) NULL,
`Birthday` VARCHAR(250) NULL,
`Gender` VARCHAR(250) NULL,
`Location` VARCHAR(250) NULL,
`Billing Information` VARCHAR(250) NULL,
`Directory Server` VARCHAR(250) NULL,
`Mileage` VARCHAR(250) NULL,
`Occupation` VARCHAR(250) NULL,
`Hobby` VARCHAR(250) NULL,
`Sensitivity` VARCHAR(250) NULL,
`Priority` VARCHAR(250) NULL,
`Subject` VARCHAR(250) NULL,
`Notes` VARCHAR(250) NULL,
`Language` VARCHAR(250) NULL,
`Photo` VARCHAR(250) NULL,
`Group Membership` VARCHAR(250) NULL,
`E-mail 1 - Type` VARCHAR(250) NULL,
`E-mail 1 - Value` VARCHAR(250) NULL,
`Phone 1 - Type` VARCHAR(250) NULL,
`Phone 1 - Value` VARCHAR(250) NULL,
`Phone 2 - Type` VARCHAR(250) NULL,
`Phone 2 - Value` VARCHAR(250) NULL,
`Address 1 - Type` VARCHAR(250) NULL,
`Address 1 - Formatted` VARCHAR(250) NULL,
`Address 1 - Street` VARCHAR(250) NULL,
`Address 1 - City` VARCHAR(250) NULL,
`Address 1 - PO Box` VARCHAR(250) NULL,
`Address 1 - Region` VARCHAR(250) NULL,
`Address 1 - Postal Code` INT NULL,
`Address 1 - Country` VARCHAR(250) NULL,
`Address 1 - Extended Address` VARCHAR(250) NULL,
`Organization 1 - Type` VARCHAR(250) NULL,
`Organization 1 - Name` VARCHAR(250) NULL,
`Organization 1 - Yomi Name` VARCHAR(250) NULL,
`Organization 1 - Title` VARCHAR(250) NULL,
`Organization 1 - Department` VARCHAR(250) NULL,
`Organization 1 - Symbol` VARCHAR(250) NULL
)

r937 · June 21, 2021, 11:13am

have you inserted any data into that table yet? did it work?

toplisek · June 21, 2021, 1:09pm

I try to execute QUERY CREATE TABLE. I’m not sure why it is not created my table and structure. without any error. Later it will be added also a record. I do not see programming mistake. Strange, maybe you know why…Thank you.

r937 · June 21, 2021, 1:47pm

so you ran CREATE TABLE and no table was created?

what did you use to run your CREATE TABLE statement?

and how do you know the table wasn’t created?

try running this –

SELECT COUNT(*) FROM GoogleContacts

toplisek · June 21, 2021, 2:42pm

It will do anything. I’m using PHPMyAdmin. Is it blocked `` using spaces for a field in updated MYSQL?
Table is not created when I execute my Query but code at the bottom will work and also execute with the result:SELECT COUNT(*) FROM MyGuests
It will work: Source: https://www.w3schools.com/php/php_mysql_create_table.asp

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

r937 · June 21, 2021, 3:00pm

i tried your CREATE TABLE GoogleContacts from post #11 and it worked fine, no errors

i also tried CREATE TABLE MyGuests and it worked fine too

so everything is okay now, yes?

toplisek · June 22, 2021, 7:39am

I have solved. Thank you for all help. It is an error if it length: 200, but 150 works.

system · September 21, 2021, 2:40pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.