search

roxlukas / lmeve

LMeve - industry manager and contribution tracker for EVE Online
https://pozniak.pl/wp/
Other
51 stars 13 forks source link

contracts invalid date sqlstate 22007 error 1292 #58

Closed dstevens79 closed 5 years ago

dstevens79 commented 5 years ago

fresh install

working...

but contracts entering into the db error out with invalid date time : https://i.imgur.com/Aep1a5e.png

date and time comes across liek this : https://i.imgur.com/rYMLI9O.png

sorry for my fail posting of error codes, but these should be the parts you need i think

thank you for this project, no one else seems to care about creating any kind of industry tools that can be used to organize people in a corp...

roxlukas commented 5 years ago

Thanks for the report, I've seen it before, but was sure it's fixed already. Will investigate further.

dstevens79 commented 5 years ago

is there a way for me to circumvent this ?

roxlukas commented 5 years ago

When did you install and which code version did you download?

To make sure you have the most recent code installed(simply overwrite the php files with the most recent ones from here: https://github.com/roxlukas/lmeve/archive/master.zip

dstevens79 commented 5 years ago

i did a git clone for the repository yesterday.

roxlukas commented 5 years ago

Ok then it's up to date. Gonna have to reproduce this and check what's causing it. I'll contact you when I find something.

dstevens79 commented 5 years ago

ok so i downloaded that and dumped and recreated the database off the schema.sql contained within and i get the same error.

json parsed time and date have a t and z in them - this is causing the error i think? https://i.imgur.com/nZcCFSq.png

(these are not new but unreported initally) im also getting an error on the settings -> esi token screen : https://i.imgur.com/WE190cz.png and the timesheet page is displaying like this : https://i.imgur.com/wNmIH4R.png and the wallet looks like this : https://i.imgur.com/OoSLPmI.png industry reports look like this : https://i.imgur.com/WrvdQTv.png

dstevens79 commented 5 years ago

finally managed to get an error text copy - 

<b>29.12.2018 10:04:15 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381512953,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1949897374,983,'Badger Blueprint',1029320610336,1026979982819,2,15270,0,10,1,648,'Badger','delivered',11098,'2018-12-28T19:24:34Z','2018-12-28T22:29:32Z','','2018-12-29T03:35:31Z',2112986376,2,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T03:35:31Z',completedCharacterID=2112986376,successfulRuns=2,productTypeID=648,productTypeName='Badger' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-28T19:24:34Z' for column 'startDate' at row 1<br>
<b>29.12.2018 10:13:53 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381512953,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1949897374,983,'Badger Blueprint',1029320610336,1026979982819,2,15270,0,10,1,648,'Badger','delivered',11098,'2018-12-28T19:24:34Z','2018-12-28T22:29:32Z','','2018-12-29T03:35:31Z',2112986376,2,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T03:35:31Z',completedCharacterID=2112986376,successfulRuns=2,productTypeID=648,productTypeName='Badger' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-28T19:24:34Z' for column 'startDate' at row 1<br>
<b>29.12.2018 10:50:27 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381512953,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1949897374,983,'Badger Blueprint',1029320610336,1026979982819,2,15270,0,10,1,648,'Badger','delivered',11098,'2018-12-28T19:24:34Z','2018-12-28T22:29:32Z','','2018-12-29T03:35:31Z',2112986376,2,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T03:35:31Z',completedCharacterID=2112986376,successfulRuns=2,productTypeID=648,productTypeName='Badger' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-28T19:24:34Z' for column 'startDate' at row 1<br>
<b>29.12.2018 10:55:57 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381512953,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1949897374,983,'Badger Blueprint',1029320610336,1026979982819,2,15270,0,10,1,648,'Badger','delivered',11098,'2018-12-28T19:24:34Z','2018-12-28T22:29:32Z','','2018-12-29T03:35:31Z',2112986376,2,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T03:35:31Z',completedCharacterID=2112986376,successfulRuns=2,productTypeID=648,productTypeName='Badger' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-28T19:24:34Z' for column 'startDate' at row 1<br>
<b>29.12.2018 11:03:46 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'singularity',109,'1438930','2018-12-29T05:04:28Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T05:04:28Z' for column 'startTime' at row 1<br>
<b>29.12.2018 11:05:17 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'singularity',109,'1438930','2018-12-29T05:04:28Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T05:04:28Z' for column 'startTime' at row 1<br>
<b>29.12.2018 11:05:48 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',0,'1438831','2018-12-29T11:03:32Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T11:03:32Z' for column 'startTime' at row 1<br>
<b>29.12.2018 11:09:33 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381512953,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1949897374,983,'Badger Blueprint',1029320610336,1026979982819,2,15270,0,10,1,648,'Badger','delivered',11098,'2018-12-28T19:24:34Z','2018-12-28T22:29:32Z','','2018-12-29T03:35:31Z',2112986376,2,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T03:35:31Z',completedCharacterID=2112986376,successfulRuns=2,productTypeID=648,productTypeName='Badger' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-28T19:24:34Z' for column 'startDate' at row 1<br>
<b>29.12.2018 11:11:55 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5279068999,722133377,1027176887407,3,2,1,0,12743,'region',1,90,0,245999999,0,'2018-12-20T00:24:17Z',935317045); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>
<b>29.12.2018 11:12:15 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139692867,722133377,935317045,2114556553,2114556553,1026931774306,1026931774306,'item_exchange','finished','',1,'personal','2018-12-01T04:07:12Z','2018-12-29T04:07:12Z','2018-12-01T04:07:38Z',0,'2018-12-01T04:07:38Z',0,0,0,0,20000,935317045) ON DUPLICATE KEY UPDATE dateExpired='2018-12-29T04:07:12Z',dateAccepted='2018-12-01T04:07:38Z',dateCompleted='2018-12-01T04:07:38Z',acceptorID=2114556553,status='finished'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-01T04:07:12Z' for column 'dateIssued' at row 1<br>
<b>29.12.2018 13:31:06 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381512953,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1949897374,983,'Badger Blueprint',1029320610336,1026979982819,2,15270,0,10,1,648,'Badger','delivered',11098,'2018-12-28T19:24:34Z','2018-12-28T22:29:32Z','','2018-12-29T03:35:31Z',2112986376,2,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T03:35:31Z',completedCharacterID=2112986376,successfulRuns=2,productTypeID=648,productTypeName='Badger' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-28T19:24:34Z' for column 'startDate' at row 1<br>
<b>29.12.2018 21:25:09 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381592927,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1014812162214,826,'425mm AutoCannon I Blueprint',1029214163427,1026979982819,14,15686,0,100,1,491,'425mm AutoCannon I','delivered',14566,'2018-12-29T15:55:52Z','2018-12-29T19:58:38Z','','2018-12-29T20:32:49Z',722133377,14,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T20:32:49Z',completedCharacterID=722133377,successfulRuns=14,productTypeID=491,productTypeName='425mm AutoCannon I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T15:55:52Z' for column 'startDate' at row 1<br>
<b>29.12.2018 21:28:32 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5279068999,722133377,1027176887407,3,2,1,0,12743,'region',1,90,0,245999999,0,'2018-12-20T00:24:17Z',935317045); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>
<b>29.12.2018 21:35:56 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381592927,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1014812162214,826,'425mm AutoCannon I Blueprint',1029214163427,1026979982819,14,15686,0,100,1,491,'425mm AutoCannon I','delivered',14566,'2018-12-29T15:55:52Z','2018-12-29T19:58:38Z','','2018-12-29T20:32:49Z',722133377,14,935317045) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2018-12-29T20:32:49Z',completedCharacterID=722133377,successfulRuns=14,productTypeID=491,productTypeName='425mm AutoCannon I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T15:55:52Z' for column 'startDate' at row 1<br>
<b>29.12.2018 21:38:11 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139692867,722133377,935317045,2114556553,2114556553,1026931774306,1026931774306,'item_exchange','finished','',1,'personal','2018-12-01T04:07:12Z','2018-12-29T04:07:12Z','2018-12-01T04:07:38Z',0,'2018-12-01T04:07:38Z',0,0,0,0,20000,935317045) ON DUPLICATE KEY UPDATE dateExpired='2018-12-29T04:07:12Z',dateAccepted='2018-12-01T04:07:38Z',dateCompleted='2018-12-01T04:07:38Z',acceptorID=2114556553,status='finished'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-01T04:07:12Z' for column 'dateIssued' at row 1<br>
<b>29.12.2018 21:41:08 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',32610,'1438831','2018-12-29T11:03:32Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T11:03:32Z' for column 'startTime' at row 1<br>
<b>29.12.2018 21:43:55 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381619172,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1023949357993,23058,'Dragonfly I Blueprint',1029320937730,1026979982819,1,17710,0,50,1,23057,'Dragonfly I','active',4266,'2018-12-29T21:41:41Z','2018-12-29T22:52:47Z','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=23057,productTypeName='Dragonfly I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T21:41:41Z' for column 'startDate' at row 1<br>
<b>29.12.2018 21:44:03 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139692867,722133377,935317045,2114556553,2114556553,1026931774306,1026931774306,'item_exchange','finished','',1,'personal','2018-12-01T04:07:12Z','2018-12-29T04:07:12Z','2018-12-01T04:07:38Z',0,'2018-12-01T04:07:38Z',0,0,0,0,20000,935317045) ON DUPLICATE KEY UPDATE dateExpired='2018-12-29T04:07:12Z',dateAccepted='2018-12-01T04:07:38Z',dateCompleted='2018-12-01T04:07:38Z',acceptorID=2114556553,status='finished'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-01T04:07:12Z' for column 'dateIssued' at row 1<br>
<b>29.12.2018 21:44:19 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',32459,'1438831','2018-12-29T11:03:32Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T11:03:32Z' for column 'startTime' at row 1<br>
<b>29.12.2018 21:44:32 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'singularity',143,'1438930','2018-12-29T05:04:28Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T05:04:28Z' for column 'startTime' at row 1<br>
<b>29.12.2018 21:46:23 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',32361,'1438831','2018-12-29T11:03:32Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T11:03:32Z' for column 'startTime' at row 1<br>
<b>29.12.2018 21:47:49 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',32361,'1438831','2018-12-29T11:03:32Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T11:03:32Z' for column 'startTime' at row 1<br>
<b>29.12.2018 21:48:13 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'singularity',134,'1438930','2018-12-29T05:04:28Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T05:04:28Z' for column 'startTime' at row 1<br>
<b>29.12.2018 21:56:07 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381620006,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,5,1949897417,950,'Merlin Blueprint',1029320610336,1029320610336,3,574,0,5,1,950,'Merlin Blueprint','active',39015,'2018-12-29T21:50:49Z','2018-12-30T08:41:04Z','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=950,productTypeName='Merlin Blueprint' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T21:50:49Z' for column 'startDate' at row 1<br>
<b>29.12.2018 21:56:15 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5279068999,722133377,1027176887407,3,2,1,0,12743,'region',1,90,0,245999999,0,'2018-12-20T00:24:17Z',935317045); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>
<b>29.12.2018 21:56:21 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139692867,722133377,935317045,2114556553,2114556553,1026931774306,1026931774306,'item_exchange','finished','',1,'personal','2018-12-01T04:07:12Z','2018-12-29T04:07:12Z','2018-12-01T04:07:38Z',0,'2018-12-01T04:07:38Z',0,0,0,0,20000,935317045) ON DUPLICATE KEY UPDATE dateExpired='2018-12-29T04:07:12Z',dateAccepted='2018-12-01T04:07:38Z',dateCompleted='2018-12-01T04:07:38Z',acceptorID=2114556553,status='finished'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-01T04:07:12Z' for column 'dateIssued' at row 1<br>
<b>29.12.2018 21:56:44 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',32032,'1438831','2018-12-29T11:03:32Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T11:03:32Z' for column 'startTime' at row 1<br>
<b>29.12.2018 22:40:43 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (381620006,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,5,1949897417,950,'Merlin Blueprint',1029320610336,1029320610336,3,574,0,5,1,950,'Merlin Blueprint','active',39015,'2018-12-29T21:50:49Z','2018-12-30T08:41:04Z','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=950,productTypeName='Merlin Blueprint' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T21:50:49Z' for column 'startDate' at row 1<br>
dstevens79 commented 5 years ago

PHP Warning: count(): Parameter must be an array or an object that implements Countable in /opt/lmeve/bin/Industry.php on line 93

PHP Warning: count(): Parameter must be an array or an object that implements Countable in /opt/lmeve/bin/Status.php on line 26

dstevens79 commented 5 years ago

so i have gotten the poller to complete sucessfully

i havd to alter the database where it was complaining about date time errors and some station id int cells to varchar and poof no errors...

i also had to comment out the following in status.php...


       if ($this->getStatus()=='fresh') {
 #           if (count($status) > 0) {
                   if ($this->v($c,'vip',false) === true) $vip = 1; else $vip = '0';
                    $sql="INSERT INTO esiserverstatus VALUES (".
                            "DEFAULT" . "," .
                            "NOW()" . "," .
                            $this->s($this->ESI->getDatasource()) . "," .
                            $this->v($status,'players') . "," .
                            $this->s($this->v($status,'server_version')) . "," .
                            $this->s($this->v($status,'start_time')) . "," .
                            $vip .
                        ")" .
                    ";";
                    db_uquery($sql);

            }
        #}
        #else {
         #   inform(get_class(), 'Route ' . $this->getRoute() . $this->getParams() . ' is still cached, skipping...');
          #  return TRUE;
     #   }
        return TRUE;
    }

i receive database errors after doing this on the site still, but it does poll and i think i have the tasks working again but not the time sheet and several other pages still complain about a broken database

roxlukas commented 5 years ago

Good job!

It seems the db auto updater I wrote is doing a lousy job. Db schema is old, but dbcatalog.php should update it the first time you log in to GUI. For some reason it's not working correctly and DB schema ends up incomplete/broken.

I'll export the current DB schema and the initial values when I can get to the PC

roxlukas commented 5 years ago

Please try to use the schema.sql I've uploaded in commit 36b041f

dstevens79 commented 5 years ago

Failed to execute SQL : SQL source /opt/lmeve/data/schema.sql; failed : '/opt/lmeve/data/schema.sql': 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 '$$

roxlukas commented 5 years ago

Doh, copied output of phpmyadmin a little too quickly and didn't test in plain mysql properly. Forgot about DELIMITER $$.

This one should import from plain mysql shell: 97c79f2

dstevens79 commented 5 years ago 
<b>30.12.2018 19:01:18 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES 
' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-29T23:55:28Z' for column 'startDate' at row 1<br>

<b>30.12.2018 19:18:51 [CLI]:</b> Error in query: INSERT INTO apiindustryjobs VALUES 
MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-20T00:24:17Z' for column 'issued' at row 1<br>

<b>30.12.2018 19:23:14 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES 
MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-01T04:07:12Z' for column 'dateIssued' at row 1<br>

ALL the date time columns are invalid in all these tables - eve is returning :

["birthday"]=> string(20) "2011-07-03T00:19:00Z"

The T and Z need to be removed with formatting or the columns need to be changed to varchar (i did option 2)


<b>30.12.2018 19:28:42 [CLI]:</b> Error in query: INSERT INTO apicorpsheet VALUES 
MySQL reply: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'description' at row 1<br>

extending the length of this varchar to 4098 resolved this too.

all that working and polling resolves with a success.. i have even uncommented all the lines in status and industry php and polling works however .....

1 timesheet webpage is returning database errors, 2 the statistics sheet comes up white and blank and no visable statistics tracked showing under any button 3 The wallet shows white with no css, graphing for transactions shows but the page returns a database error 4 settings -> esi api tokens takes you to a page where the css fails to load and the database is also broken

and the webpage returns these errors (from the polling?): 

 ESI error on route /v1/status/: {"error":"Timeout contacting tranquility","timeout":10}
Warning ESI error on route /v1/corporations/: {"error":"Failed to fetch access data"}
Warning ESI error on route /v1/corporations/98549646/assets/names/: {"error":"Failed to fetch access data"}
roxlukas commented 5 years ago

Hi, I agree, dates need to be converted from JS format (yyyy-mm-ddThh:mm:ssZ) before INSERT. It should be easy to implement now that everything is in one set of classes. I'll write a patch for that as soon as I can. Converting types to VARCHAR in DB is not an option, because we rely on date deltas (it is why most GUI stuff doesn't work for you right now).

What is really curious is that this error does not happen on my host. Must have something to do with older PHP version, or specific configuration.

roxlukas commented 5 years ago

Fixed in a64e077 . Please verify.

dstevens79 commented 5 years ago

so i wiped the db and used fresh files and sql schema in the release

let it run for a day with some use and some manual polling and repolling (cause it works at different parts due to skipping cached data sucessfully entered or not?)

then i cleaned out some of the error logs to make it ez to read :),

first i receive a string of :

PHP Warning: count(): Parameter must be an array or an object that implements Countable in /opt/lmeve/bin/Industry.php on line 93

and status.php on line 26 errors break the polling completely still which i bypass that whole function and returns "working".. 



<b>03.01.2019 10:43:53 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 10:47:03 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382033006,722133377,'Greviouss',1026941704533,0,'',1026941704533,1,1019127643207,26073,'Large Cargohold Optimization I Blueprint',1026978889628,1026978889628,2,39174,0,40,1,26072,'Large Cargohold Optimization I','active',5202,'2019-01-03 10:18:19','2019-01-03 11:45:01','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=26072,productTypeName='Large Cargohold Optimization I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 10:48:56 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 10:54:32 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382033006,722133377,'Greviouss',1026941704533,0,'',1026941704533,1,1019127643207,26073,'Large Cargohold Optimization I Blueprint',1026978889628,1026978889628,2,39174,0,40,1,26072,'Large Cargohold Optimization I','active',5202,'2019-01-03 10:18:19','2019-01-03 11:45:01','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=26072,productTypeName='Large Cargohold Optimization I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 10:54:50 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 11:08:59 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382033006,722133377,'Greviouss',1026941704533,0,'',1026941704533,1,1019127643207,26073,'Large Cargohold Optimization I Blueprint',1026978889628,1026978889628,2,39174,0,40,1,26072,'Large Cargohold Optimization I','active',5202,'2019-01-03 10:18:19','2019-01-03 11:45:01','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=26072,productTypeName='Large Cargohold Optimization I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 11:09:09 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5279068999,722133377,1027176887407,3,2,1,0,12743,'region',1,90,0,245999999,0,'2018-12-20 00:24:17',935317045); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>

<b>03.01.2019 11:09:15 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 11:10:18 [CLI]:</b> Error in query: INSERT INTO apicorpsheet VALUES (98549646,'Ashfell Celestial Academy','--ACA',90957378,'Maistus StarPwner',60004948,'Josekorn VI - Republic Justice Department Tribunal','<font size=\"12\" color=\"#bfffffff\"></font><font size=\"12\" color=\"#fff7931e\"><a href=\"recruitmentAd:98549646//140542\">--ACA - Learn to live the good EvE life! </a></font><font size=\"12\" color=\"#ffff0000\"> <br><br><b><u>Who we are:</u><br></font><font size=\"12\" color=\"#ff00ffff\">- We respect the priority of real life<br>- An experienced and friendly group of players<br></font><font size=\"12\" color=\"#bfffffff\">- </font><font size=\"12\" color=\"#ff00ff00\">ACA is a HIGH SEC &amp; NON AGGRESSIVE</font><font size=\"12\" color=\"#bfffffff\"> </font><font size=\"12\" color=\"#ff00ffff\">corporation aimed at training pilots for eve. <br>- We particularly frown upon those who pick on the weak and defenseless.<br>- Great environment for both new and established High and low sec players<br>- An anti-pirate &amp; Anti-scam corporation</b><br><br></font><font size=\"12\" color=\"#bfffffff\">    </font><font size=\"12\" color=\"#ffffa600\"><a href=\"showinfo:2//98549646\">ACA</a></font><font size=\"12\" color=\"#bfffffff\"> offers :                        </font><font size=\"12\" color=\"#ffffa600\"><a href=\"showinfo:2//98549646\">ACA</a></font><font size=\"12\" color=\"#bfffffff\"> seeks :<br></font><font size=\"12\" color=\"#ff00ffff\">- Mission running                    - Mission Runners<br>- Incursion running                   - Explorers<br>- Wormhole Ops                     - PVE Players<br>- Great amount of knowledge         - PVP players<br>- High-sec moon mining              - Miners<br><br></font><font size=\"12\" color=\"#bfffffff\">All capsuleers that join --ACA :<br>Will be provided with assistance, training, information knowledge, and help by our more aged players who enjoy helping the new pilot overcome, and grow from, the trials faced by a newer player.  Please hang out in  </font><font size=\"12\" color=\"#fff7931e\"><a href=\"joinChannel:2139933173//None//None\">--ACA</a></font><font size=\"12\" color=\"#bfffffff\"> public chat and get to know our members and enjoy the atmosphere.<br><br>Join our public channel:   </font><font size=\"12\" color=\"#fff7931e\"><a href=\"joinChannel:2139933173//None//None\">--ACA</a></font><font size=\"12\" color=\"#bfffffff\"> </font>','http://ash-online.net',99006384,2,77,100,1000,0,0,0,0,0,0,0) ON DUPLICATE KEY UPDATE `corporationName` = 'Ashfell Celestial Academy',`ticker` = '--ACA',`ceoID` = 90957378,`ceoName` = 'Maistus StarPwner',`stationID` = 60004948,`stationName` = 'Josekorn VI - Republic Justice Department Tribunal',`description` = '<font size=\"12\" color=\"#bfffffff\"></font><font size=\"12\" color=\"#fff7931e\"><a href=\"recruitmentAd:98549646//140542\">--ACA - Learn to live the good EvE life! </a></font><font size=\"12\" color=\"#ffff0000\"> <br><br><b><u>Who we are:</u><br></font><font size=\"12\" color=\"#ff00ffff\">- We respect the priority of real life<br>- An experienced and friendly group of players<br></font><font size=\"12\" color=\"#bfffffff\">- </font><font size=\"12\" color=\"#ff00ff00\">ACA is a HIGH SEC &amp; NON AGGRESSIVE</font><font size=\"12\" color=\"#bfffffff\"> </font><font size=\"12\" color=\"#ff00ffff\">corporation aimed at training pilots for eve. <br>- We particularly frown upon those who pick on the weak and defenseless.<br>- Great environment for both new and established High and low sec players<br>- An anti-pirate &amp; Anti-scam corporation</b><br><br></font><font size=\"12\" color=\"#bfffffff\">    </font><font size=\"12\" color=\"#ffffa600\"><a href=\"showinfo:2//98549646\">ACA</a></font><font size=\"12\" color=\"#bfffffff\"> offers :                        </font><font size=\"12\" color=\"#ffffa600\"><a href=\"showinfo:2//98549646\">ACA</a></font><font size=\"12\" color=\"#bfffffff\"> seeks :<br></font><font size=\"12\" color=\"#ff00ffff\">- Mission running                    - Mission Runners<br>- Incursion running                   - Explorers<br>- Wormhole Ops                     - PVE Players<br>- Great amount of knowledge         - PVP players<br>- High-sec moon mining              - Miners<br><br></font><font size=\"12\" color=\"#bfffffff\">All capsuleers that join --ACA :<br>Will be provided with assistance, training, information knowledge, and help by our more aged players who enjoy helping the new pilot overcome, and grow from, the trials faced by a newer player.  Please hang out in  </font><font size=\"12\" color=\"#fff7931e\"><a href=\"joinChannel:2139933173//None//None\">--ACA</a></font><font size=\"12\" color=\"#bfffffff\"> public chat and get to know our members and enjoy the atmosphere.<br><br>Join our public channel:   </font><font size=\"12\" color=\"#fff7931e\"><a href=\"joinChannel:2139933173//None//None\">--ACA</a></font><font size=\"12\" color=\"#bfffffff\"> </font>',`url` = 'http://ash-online.net',`allianceID` = 99006384,`taxRate` = 2,`memberCount` = 77,`memberLimit` = 100,`shares` = 1000,`graphicId` = 0,`shape1` = 0,`shape2` = 0,`shape3` = 0,`color1` = 0,`color2` = 0,`color3` = 0; MySQL reply: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'description' at row 1<br>

<b>03.01.2019 11:15:08 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382008883,2114556553,'xKillua',1026941704533,0,'',1026941704533,1,1027626337742,11628,'Magnetometric ECM I Blueprint',1029327629053,1027431015019,1,205,0,100,1,1948,'Magnetometric ECM I','delivered',1151,'2019-01-03 02:38:39','2019-01-03 02:57:50','','2019-01-03 03:04:29',2114556553,1,98549646) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2019-01-03 03:04:29',completedCharacterID=2114556553,successfulRuns=1,productTypeID=1948,productTypeName='Magnetometric ECM I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 11:16:05 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382033006,722133377,'Greviouss',1026941704533,0,'',1026941704533,1,1019127643207,26073,'Large Cargohold Optimization I Blueprint',1026978889628,1026978889628,2,39174,0,40,1,26072,'Large Cargohold Optimization I','active',5202,'2019-01-03 10:18:19','2019-01-03 11:45:01','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=26072,productTypeName='Large Cargohold Optimization I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 11:16:14 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 11:16:29 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5284068160,90957378,1027176887407,22,22,1,0,32772,'region',4,90,0,1357910,0,'2018-11-06 07:46:10',98549646); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>

<b>03.01.2019 11:17:08 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (140307163,824418619,98549646,99006384,0,60003760,1027542010872,'courier','outstanding','',0,'personal','2018-12-18 04:54:34','2019-01-15 04:54:34','0000-00-00 00:00:00',2,'0000-00-00 00:00:00',0,3000000,15000000,0,600,98549646) ON DUPLICATE KEY UPDATE dateExpired='2019-01-15 04:54:34',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='outstanding'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 11:21:58 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382033006,722133377,'Greviouss',1026941704533,0,'',1026941704533,1,1019127643207,26073,'Large Cargohold Optimization I Blueprint',1026978889628,1026978889628,2,39174,0,40,1,26072,'Large Cargohold Optimization I','active',5202,'2019-01-03 10:18:19','2019-01-03 11:45:01','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=26072,productTypeName='Large Cargohold Optimization I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 11:22:06 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 11:22:31 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382008883,2114556553,'xKillua',1026941704533,0,'',1026941704533,1,1027626337742,11628,'Magnetometric ECM I Blueprint',1029327629053,1027431015019,1,205,0,100,1,1948,'Magnetometric ECM I','delivered',1151,'2019-01-03 02:38:39','2019-01-03 02:57:50','','2019-01-03 03:04:29',2114556553,1,98549646) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2019-01-03 03:04:29',completedCharacterID=2114556553,successfulRuns=1,productTypeID=1948,productTypeName='Magnetometric ECM I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 11:22:47 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (140307163,824418619,98549646,99006384,0,60003760,1027542010872,'courier','outstanding','',0,'personal','2018-12-18 04:54:34','2019-01-15 04:54:34','0000-00-00 00:00:00',2,'0000-00-00 00:00:00',0,3000000,15000000,0,600,98549646) ON DUPLICATE KEY UPDATE dateExpired='2019-01-15 04:54:34',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='outstanding'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 21:51:05 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382055474,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1015026819029,12258,'Medium Energy Nosferatu I Blueprint',1029214163427,1026979982819,10,6971,0,100,1,12257,'Medium Energy Nosferatu I','active',10404,'2019-01-03 15:53:51','2019-01-03 18:47:15','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=12257,productTypeName='Medium Energy Nosferatu I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 21:51:14 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5279068999,722133377,1027176887407,3,2,1,0,12743,'region',1,90,0,245999999,0,'2018-12-20 00:24:17',935317045); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>

<b>03.01.2019 21:51:36 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>03.01.2019 21:53:54 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382008883,2114556553,'xKillua',1026941704533,0,'',1026941704533,1,1027626337742,11628,'Magnetometric ECM I Blueprint',1029327629053,1027431015019,1,205,0,100,1,1948,'Magnetometric ECM I','delivered',1151,'2019-01-03 02:38:39','2019-01-03 02:57:50','','2019-01-03 03:04:29',2114556553,1,98549646) ON DUPLICATE KEY UPDATE status='delivered',completedDate='2019-01-03 03:04:29',completedCharacterID=2114556553,successfulRuns=1,productTypeID=1948,productTypeName='Magnetometric ECM I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'pauseDate' at row 1<br>

<b>03.01.2019 21:54:13 [CLI]:</b> Error in query: INSERT INTO `apimarketorders` VALUES (5284068160,90957378,1027176887407,22,22,1,0,32772,'region',4,90,0,1357910,0,'2018-11-06 07:46:10',98549646); MySQL reply: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'stationID' at row 1<br>

<b>03.01.2019 21:55:23 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (140307163,824418619,98549646,99006384,0
```,60003760,1027542010872,'courier','outstanding','',0,'personal','2018-12-18 04:54:34','2019-01-15 04:54:34','0000-00-00 00:00:00',2,'0000-00-00 00:00:00',0,3000000,15000000,0,600,98549646) ON DUPLICATE KEY UPDATE dateExpired='2019-01-15 04:54:34',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='outstanding'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>
dstevens79 commented 5 years ago

96.92.102.204:82 feel free to come and login with the admin admin to see what the broken pages look liek

dstevens79 commented 5 years ago

new error while polling

<b>04.01.2019 4:38:17 [CLI]:</b> Error in query: INSERT INTOapiassetsVALUES (1029498400727,1027184860227,1027184860227,1225,1502,115,0,NULL,NULL,935317045); MySQL reply: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1029498400727' for key 'PRIMARY'<br>

roxlukas commented 5 years ago

Thanks for the reports. Will investigate and correct.

roxlukas commented 5 years ago

Plan of fix (under way, I have little time this weekend)

dstevens79 commented 5 years ago

heya,

so i tried allowing nulls in the areas of db it was complaining about but i still get errors with entries like these

<b>05.01.2019 21:52:43 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateAccepted' at row 1<br>

<b>05.01.2019 22:04:57 [CLI]:</b> Error in query: INSERT INTO apiindustryjobs VALUES (382273959,1026941704533,1027431098491,1029219902238,1027431098491,1,0,0,200,1027431015019,2114556553,1,200,0,1027431098491,0,0,0,0,25267,25266,0,0,0,0,0,0,0,1,0,'2019-01-05 21:30:18','2019-01-05 21:30:18','2019-01-05 21:37:58','0',98549646) ON DUPLICATE KEY UPDATE completed=0,completedSuccessfully=0,completedStatus=0,successfulRuns=0,outputTypeID=25266 MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0' for column 'pauseProductionTime' at row 1<br>

<b>05.01.2019 22:06:42 [CLI]:</b> Error in query: INSERT INTO `apicontracts` VALUES (139919024,768839766,935317045,935317045,0,1025742467811,1027176887407,'courier','deleted','',1,'personal','2018-12-07 14:28:25','2019-01-04 14:28:25','0000-00-00 00:00:00',14,'0000-00-00 00:00:00',0,0,0,0,4.16,935317045) ON DUPLICATE KEY UPDATE dateExpired='2019-01-04 14:28:25',dateAccepted='0000-00-00 00:00:00',dateCompleted='0000-00-00 00:00:00',acceptorID=0,status='deleted'; MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateCompleted' at row 1<br>

<b>05.01.2019 21:55:04 [CLI]:</b> Error in query: INSERT INTO `apiindustryjobscrius` VALUES (382274373,2112986376,'Sarcaustic Severasse',1026941704533,0,'',1026941704533,1,1019127643207,26073,'Large Cargohold Optimization I Blueprint',1026978889628,1026979982819,2,38702,0,40,1,26072,'Large Cargohold Optimization I','active',5202,'2019-01-05 21:35:48','2019-01-05 23:02:30','','',0,0,935317045) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=26072,productTypeName='Large Cargohold Optimization I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'completedDate' at row 1<br>

some responded to nulling out and some stil give errors and need to b varchar to poll ..

and i get this 6000 times : PHP Warning: count(): Parameter must be an array or an object that implements Countable in /opt/lmeve/bin/Industry.php on line 93 -- what is it looking for there?

i also got an error with station id value and fixed that by changing the int size

all in all so many less errors / fixes required to get a successful polling.

roxlukas commented 5 years ago

I have fixed the datetime problem (hopefully), which is weird, because my MySQL version allowed 0000-00-00 00:00:00 as empty value (and it was working prior to this patch)

I've changed all routes that had datetimes to either insert a correct date or NULL

I've changed database schema to allow NULL in datetime fields

I've added dbcatalog function to change datetime fields from NOT NULL to NULL DEFAULT NULL if necessary - this will fix already established installations without the need to wipe database clean

This also causes first login to LMeve to last about 60 seconds, because dbcataalog does changes at login time and 69 columns need to be changed.

dstevens79 commented 5 years ago

starting to feel like a thorn in ur side, so i decided to go back over every step of the install guide both simple and complex. I have for all i can tell everything that is outlined in there as it is outlined installed and working.

it is a bit hard as i am admittedly not extremely linux savvy - and my version is ubuntu bionic 18.04, and not centos, but i am fairly sure that i have installed all the components it is requesting and they are all operational.

im sure im not the only person to have had downloaded and installed this not using the vm since you starting working on it with esi am i? Cuz i dont see other people with this issue either.... atleast not reported.Then again i do everything the hard way i guess so maybe im the one guy who decided to not go the easy route with the vm?

is there a series of commands i can do that will spit out useful info about my config that you can use to tell if this problem lies with me somwhere as opposed to being an issue with your project?

dstevens79 commented 5 years ago

i posted errors earlier but then i decided to start fresh again so i did a full wipe of all of it and a full reinstall of the whole shebang including a sql drop / source and ....

industry.php line 93 errors, (warnings - doesnt break it) wants an array or something that inherits from countable status.php line 26 errors (breaks it have to comment out) corp status resize to 4096 apimarketorders station id needs a bigint esi error on route /v4/characters/: 0 does not meet minimum of 1, 'character_id is required'

database error on esi token data, industry products, industry characters, timesheet, web GL stats, and wallet pages

and then theres 2 tz issues still

09.01.2019 5:28:57 [CLI]: Error in query: INSERT INTO apiindustryjobscrius VALUES (382299132,2114556553,'xKillua',1026941704533,0,'',1026941704533,1,1014812301552,11619,'Co-Processor I Blueprint',1029327629053,1027431015019,10,2459,0,200,1,3887,'Co-Processor I','active',4947,'2019-01-06 04:03:38','2019-01-06 05:26:05',NULL,NULL,0,0,98549646) ON DUPLICATE KEY UPDATE status='active',completedDate='0',completedCharacterID=0,successfulRuns=0,productTypeID=3887,productTypeName='Co-Processor I' MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0' for column 'completedDate' at row 1

<b>09.01.2019 5:34:22 [CLI]:</b> Error in query: INSERT INTOapimarketordersVALUES (5279068999,722133377,1027176887407,3,2,1,0,12743,'region',1,90,0,245999999,0,'2018-12-20T00:24:17Z',935317045); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-20T00:24:17Z' for column 'issued' at row 1<br>

roxlukas commented 5 years ago
roxlukas commented 5 years ago

Speaking of users I know for sure of one corp which is using LMeve currently in production and they didn't report anything similar to your experience. Your reports have very good value, because you test in on current version of Ubuntu LTS (18.04), which was previously untested. Thank you very much for that and sorry for the bumpy ride!

dstevens79 commented 5 years ago

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); had to look this up to resolve an error with .... orgainizing pilot data?

<b>09.01.2019 15:12:08 [CLI]:</b> Error in query: INSERT INTO apiindustryjobs VALUES (382610270,1026941704533,1029214402148,1029195962435,1029214402148,1,0,0,200,1026980831529,2112986376,3,200,0,1029214402148,0,0,0,0,33077,33076,0,0,0,0,0,0,0,1,0,'2019-01-09T12:57:08Z','2019-01-09T12:57:08Z','2019-01-09T13:23:09Z','0',935317045) ON DUPLICATE KEY UPDATE completed=0,completedSuccessfully=0,completedStatus=0,successfulRuns=0,outputTypeID=33076 MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-01-09T12:57:08Z' for column 'installTime' at row 1<br>

<b>09.01.2019 15:25:07 [CLI]:</b> Error in query: INSERT INTO esiserverstatus VALUES (DEFAULT,NOW(),'tranquility',23635,'1440766','2019-01-09T11:03:14Z',0); MySQL reply: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-01-09T11:03:14Z' for column 'startTime' at row 1<br>

got another string of industry line 93 warnings in polling

roxlukas commented 5 years ago

Try 48f940d please... I hope this is the last one

roxlukas commented 5 years ago

The best is... the code was working on my server all along, before and after changes:

example from table esisatus:

  statusID date server players version startTime vip
12195 2019-01-09 20:30:23 tranquility 30542 1440766 2019-01-09 11:03:14 0
12196 2019-01-09 20:30:23 singularity 91 1442567 2019-01-09 17:47:37 0
12194 2019-01-09 20:19:30 singularity 86 1442567 2019-01-09 17:47:37 0
12193 2019-01-09 20:19:29 tranquility 30591 1440766 2019-01-09 11:03:14 0
12192 2019-01-09 20:00:31 singularity 81 1442567 2019-01-09 17:47:37 0
12191 2019-01-09 20:00:30 tranquility 30280 1440766 2019-01-09 11:03:14 0
12190 2019-01-09 19:45:26 singularity 67 1442567 2019-01-09 17:47:37 0
12189 2019-01-09 19:45:25 tranquility 30185 1440766 2019-01-09 11:03:14 0
12187 2019-01-09 19:40:55 tranquility 30077 1440766 2019-01-09 11:03:14 0
dstevens79 commented 5 years ago

https://i.imgur.com/I0G1pTn.png

https://i.imgur.com/trrm49j.png

https://i.imgur.com/J11iib6.png

it did all work for 1 second..(ok like an hour... and it was epic and there were pilots partying and drinking champagne) there were some polling warnings but all the pages loaded and everything appeared to on the surface function....

Polling now completes without erroring out. Just warnings, however the above functioning and thus partying has since stopped. Many pages are currently broken again including adding tasks, wallet, stats, timesheet..

dstevens79 commented 5 years ago

FIXED FIXED FIXED !!!!!!

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); had to look this up to resolve an error with .... orgainizing pilot data?

i ran this again in my sql and now its working!

dstevens79 commented 5 years ago

ok so not fixed - the generate tasks page is broken and will not load

but all else works after running that command!!!

i was on this page : https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/

and it references the above sql statement and others such as zeros in date? maybe these flags need to be set? i dont know how to do this myself, but... im willing to bet that is the difference between our installs when it comes to date times

dstevens79 commented 5 years ago

ok working 👍 but errors in polling still and no generate tasks page still 👎

https://i.imgur.com/I0G1pTn.png https://i.imgur.com/trrm49j.png https://i.imgur.com/fvOOiWV.png

none of these errors break the actual polling,

and the only core page that appears not to work is the create task page which generates this error :

11.01.2019 10:23:32 98.250.158.207: Error in query: SELECT FROM ( SELECT apf.,apl.itemName FROM apifacilities apf JOIN apilocations apl ON apf.facilityID=apl.itemID UNION SELECT apf.*,map.itemName FROM apifacilities apf JOIN apiassets apa ON apf.facilityID=apa.locationID JOIN mapDenormalize map ON apf.facilityID = map.itemID WHERE apa.typeID=27 ) AS apf WHERE TRUE ORDER BY apf.itemName MySQL reply: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'lmeve.mapDenormalize' doesn't exist

dstevens79 commented 5 years ago

all major functions are working now on my server

views.zip

this is the mapdenormalize view

it was extracted with a dump from an earlier image of my server when things were working and source inserted the output sql into this database this worked!

some minor pages do not work but really the issue of nonfunctionality this was opened for is resolved for me 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯 👯‍♂️ 👯

parties are back on and pilots are dancing again and doing shots to the hero ceo and the hero dev who did the undoable (from their view lol )

Thank you for this and please let me help in any way i can. I know im not the sharpest coder or sql guy but im down for whatever - testing fixing enhancing idea storming whatever i can help with.

roxlukas commented 5 years ago

mapdenormalize is a view from table of the same name in SDE. It should be created automatically on first login (dbcatalog should handle this). It seems however, that for some reason it's missing from dbcatalog. I will correct this.

roxlukas commented 5 years ago

mapdenormalize corrected in 34b2ee7

roxlukas commented 5 years ago

Please let me know if we can close this issue

dstevens79 commented 5 years ago

yea man this is resolved thank you so much...

expect suggestions and enhancements from me :D

roxlukas commented 5 years ago

Sure :-)