These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

need some help php json to my sql

First post
Author
Previous Topic Next Topic
birdman313
Doomheim
#1 - 2016-07-12 03:59:27 UTC
ok it works to a point it will insert rows but i get a error
Quote:
Error : 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 '' at line 2


code here


it will add the rows but it cutes off 181 out of 256181
Mr Mac
Dark Goliath
#2 - 2016-07-12 04:47:21 UTC
Line 51: 'Volume Entered'
The column name has whitespace?
birdman313
Doomheim
#3 - 2016-07-12 04:49:01 UTC
Mr Mac wrote:
Line 51: 'Volume Entered'
The column name has whitespace?

not the issue 256000 row add just fine
Salgare
Center for Advanced Studies
Gallente Federation
#4 - 2016-07-12 05:54:23 UTC  |  Edited by: Salgare
birdman313 wrote:
ok it works to a point it will insert rows but i get a error
Quote:
Error : 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 '' at line 2


code here


it will add the rows but it cutes off 181 out of 256181


1024*1024*1024*2/256181 = 8382 ... I wonder if that's about the size of a row?

i.e. you ran out of memory per line four of your code?
birdman313
Doomheim
#5 - 2016-07-12 06:13:17 UTC
Salgare wrote:
birdman313 wrote:
ok it works to a point it will insert rows but i get a error
Quote:
Error : 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 '' at line 2


code here


it will add the rows but it cutes off 181 out of 256181


1024*1024*1024*2/256181 = 8382 ... I wonder if that's about the size of a row?

i.e. you ran out of memory per line four of your code?


Php will give a error if you run out of memort and if i change the batch size to 100 it will do up to 256100 and drop 81
Salgare
Center for Advanced Studies
Gallente Federation
#6 - 2016-07-12 06:40:21 UTC
birdman313 wrote:
Salgare wrote:
birdman313 wrote:
ok it works to a point it will insert rows but i get a error
Quote:
Error : 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 '' at line 2


code here


it will add the rows but it cutes off 181 out of 256181


1024*1024*1024*2/256181 = 8382 ... I wonder if that's about the size of a row?

i.e. you ran out of memory per line four of your code?


Php will give a error if you run out of memort and if i change the batch size to 100 it will do up to 256100 and drop 81


Just guessing, I don't know/use php. I do know that, that particular message wording from a jdbc driver is from literally a bad sql syntax given in a prepared statement. (programming error), when that prepared statement (static memory for the life of the driver) has worked several hundred thousand times before dying it implies corrupted memory. Out of memory issues can rear their heads in some very ugly ways.

If it's not an out of memory issue causing other nasty pre/post mortem problems, it sounds like a bug in the driver. First up that 2g by 1 to eliminate that as a possibility, second start looking for driver updates.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2016-07-12 13:04:50 UTC
I _highly_ recommend you move to using PDO for your imports.

I suspect the reason you have a failure is because of something having a quote in it.

Probably station, or type (I don't know where your data is coming from. But if it had a single quote in it, then your code will break on that line.

While you can do prepared statements with mysqli ( http://www.w3schools.com/php/php_mysql_prepared_statements.asp ), PDO is generally preferable, as the syntax remains the same, regardless of which database you're using (the sql will change. but the php side doesn't)

Anyhoo, use prepared statements, so you don't have to worry about quoting stuff. It's the sensible way to do things.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
birdman313
Doomheim
#8 - 2016-07-12 14:41:23 UTC
Steve Ronuken wrote:
I _highly_ recommend you move to using PDO for your imports.

I suspect the reason you have a failure is because of something having a quote in it.

Probably station, or type (I don't know where your data is coming from. But if it had a single quote in it, then your code will break on that line.

While you can do prepared statements with mysqli ( http://www.w3schools.com/php/php_mysql_prepared_statements.asp ), PDO is generally preferable, as the syntax remains the same, regardless of which database you're using (the sql will change. but the php side doesn't)

Anyhoo, use prepared statements, so you don't have to worry about quoting stuff. It's the sensible way to do things.


that data is coming form the marked dump API for jita
David Davaham
Deep Blue Logistics
#9 - 2016-07-12 17:29:20 UTC
Steve Ronuken wrote:
I _highly_ recommend you move to using PDO for your imports.

I suspect the reason you have a failure is because of something having a quote in it.

Probably station, or type (I don't know where your data is coming from. But if it had a single quote in it, then your code will break on that line.

While you can do prepared statements with mysqli ( http://www.w3schools.com/php/php_mysql_prepared_statements.asp ), PDO is generally preferable, as the syntax remains the same, regardless of which database you're using (the sql will change. but the php side doesn't)

Anyhoo, use prepared statements, so you don't have to worry about quoting stuff. It's the sensible way to do things.



I agree. Move to PDO. Some additional troubleshooting. Right before the Die, output the array of information that is being inputted and causing the error. This will allow you to see what exactly is causing it. Can you post a PasteBin of the Data File?

Developer of EVEmail
birdman313
Doomheim
#10 - 2016-07-12 20:21:18 UTC
David Davaham wrote:
Steve Ronuken wrote:
I _highly_ recommend you move to using PDO for your imports.

I suspect the reason you have a failure is because of something having a quote in it.

Probably station, or type (I don't know where your data is coming from. But if it had a single quote in it, then your code will break on that line.

While you can do prepared statements with mysqli ( http://www.w3schools.com/php/php_mysql_prepared_statements.asp ), PDO is generally preferable, as the syntax remains the same, regardless of which database you're using (the sql will change. but the php side doesn't)

Anyhoo, use prepared statements, so you don't have to worry about quoting stuff. It's the sensible way to do things.



I agree. Move to PDO. Some additional troubleshooting. Right before the Die, output the array of information that is being inputted and causing the error. This will allow you to see what exactly is causing it. Can you post a PasteBin of the Data File?

cant pasteBin to big but here it is form CCP
Here is the last file with the data
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#11 - 2016-07-12 20:49:52 UTC
If you don't shift over to pdo, at least dump out the sql.

It's also possible it's an 'empty' value. or you're trying to insert something which won't fit.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
birdman313
Doomheim
#12 - 2016-07-12 21:40:08 UTC
fix all issue by adding
Quote:
$sql =rtrim($sql, ",");

on line 63
Salgare
Center for Advanced Studies
Gallente Federation
#13 - 2016-07-13 03:53:33 UTC  |  Edited by: Salgare
birdman313 wrote:
fix all issue by adding
Quote:
$sql =rtrim($sql, ",");

on line 63


hate those for loops that append a separator! what ever put it there is the first place should likely be consider a bug. Kind of surprised you would end up with a ',' after the ';'. but then all of that is greek to me.

Glad you found the problem.
Dragonaire
Here there be Dragons
#14 - 2016-07-14 07:14:58 UTC
In addition to all of the good suggestion above about using PDO etc I think what you are running into is too large of a INSERT for MySQL. I use to run into it with Yapeal as well because maximum client queries are usually limited to between 1MB and 16MB in size. You'll have to break it up into multiple INSERTs to make it work. You may also find that multiple smaller queries is faster as well plus if you have any SELECT going on to the same table from other connection they can be stall waiting for the INSERT to finish and unlock the tables or rows. I found through testing that 1000 rows seems to be about the max you want to go before you'll start running into size issue just for your info.

An easy way to break it up since you already have it as one big array from json_decode() would be to wrap it in a array_chunk() right there then process each chunk as an INSERT.

Also instead of trying to expand everything into named var like $Buy, $issued etc just to put them all back together for the row in the SQL just do ksort($row) to insure they are in a known order (you'll need to sort the column name in the INSERT above too manually) and use something like implode(',', $row) it'll be faster and a lot cleaner making the SQL row.

I'll stop there I'm sure I'd see some more things to improve things but already give you lots of work Blink

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#15 - 2016-07-14 11:54:41 UTC
Starting a transaction before the inserts should also help.

Otherwise you'll be flushing to disk every time you do an insert.

https://gist.github.com/fuzzysteve/da6aea99659741150a6d63fb7c32aee2

may be of some interest too. Though it is a bit sqlite specific. and in python.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Dragonaire
Here there be Dragons
#16 - 2016-07-14 19:01:08 UTC
Steve code is go of course but as he pointed out is of Python so might be hard to translate to PHP so here's some code from Yapeal-ng which deals with the XML API but once you have your data as an array it really doesn't matter.

https://github.com/Yapeal/yapeal-ng/blob/master/lib/Sql/PreserverTrait.php#L120-132

That and the following flush() method will show you kind of where I was trying to lead you with my other comments.

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.