| Pages: [1] :: one page |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
birdman313
Tactical Stability Union
Salt the Earth
0
   |
Posted - 2016.07.12 03:59:27 -
[1] - Quote
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
159
|
Posted - 2016.07.12 04:47:21 -
[2] - Quote
Line 51: 'Volume Entered'
The column name has whitespace? |
birdman313
Tactical Stability Union
Salt the Earth
0
|
Posted - 2016.07.12 04:49:01 -
[3] - Quote
Mr Mac wrote:Line 51: 'Volume Entered'
The column name has whitespace?
not the issue 256000 row add just fine |
Salgare
Satan's Gut
0
|
Posted - 2016.07.12 05:54:23 -
[4] - Quote
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 hereit 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 two of your code? |
birdman313
Tactical Stability Union
Salt the Earth
0
|
Posted - 2016.07.12 06:13:17 -
[5] - Quote
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 hereit 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
Satan's Gut
0
|
Posted - 2016.07.12 06:40:21 -
[6] - Quote
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 hereit 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
6100
|
Posted - 2016.07.12 13:04:50 -
[7] - Quote
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
Tactical Stability Union
Salt the Earth
0
|
Posted - 2016.07.12 14:41:23 -
[8] - Quote
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
6
|
Posted - 2016.07.12 17:29:20 -
[9] - Quote
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? |
birdman313
Tactical Stability Union
Salt the Earth
0
|
Posted - 2016.07.12 20:21:18 -
[10] - Quote
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
6100
|
Posted - 2016.07.12 20:49:52 -
[11] - Quote
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
Tactical Stability Union
Salt the Earth
0
|
Posted - 2016.07.12 21:40:08 -
[12] - Quote
fix all issue by adding
Quote:$sql =rtrim($sql, ",");
on line 63 |
Salgare
Satan's Gut
0
|
Posted - 2016.07.13 03:53:33 -
[13] - Quote
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
82
|
Posted - 2016.07.14 07:14:58 -
[14] - Quote
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 
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
6105
|
Posted - 2016.07.14 11:54:41 -
[15] - Quote
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
82
|
Posted - 2016.07.14 19:01:08 -
[16] - Quote
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.
|
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |