Mysql – Out of range errors while with insert statements

jsonMySQLmysql-workbench

I'm having an issue regarding every third insert line of the database I'm trying to create.

Some background, I have a huge .JSON file and used software (sqlizer) to turn it into input statements, however I'm having an issue as you can see here:

Every third statement or so, I run into a range error, that I'm not sure why is happening since I didn't set a range.

Here is a sample from my insert statements:
http://pastebin.com/j7A4ZJV4

Does anyone know what I can do to fix this issue? Keep in mind I'm very new to MySQL so forgive me if I've done something tremendously dumb.

Thank you

Best Answer

The values you're trying to insert are above the max allowed for a signed INT column in MySQL:

3088727691 > 2147483647

You can either switch it to an unsigned INT to make the max value 4,294,967,295 or a signed BIGINT to give you the room to hit up to 9,223,372,036,854,775,807.

Related Solutions

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G

MySQL Scalability – Insert/Update Scalability Solutions for Longer Lookups

Parse the JSON when the record in inserted, not later. (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.)

When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL. Be conservative. Don't extract many fields. Leave the JSON as is in case you need the details later. It would even be good to compress it and store it into a BLOB -- to save space, hence decrease I/O.

What queries will you be doing on the data? Studying them is critical to the design of a Data Warehouse.

Properly indexed (etc), a table that grows by a factor of 10 may be only 10% slower. Don't panic, but do look at the queries. I have seen billion-row tables that hum along nicely.

It sounds like you are inserting only about 2 rows per minute; is that correct? 100 per second is when it starts to get challenging.

Let's worry about the "archiving" after we have covered some of the issues I mentioned.

Best Answer

Related Solutions

Mysql – Repair with keycache in thesql runs EVERY time. Can’t even log why it’s happening

MySQL Scalability – Insert/Update Scalability Solutions for Longer Lookups

Related Question