DIRAC and Mysql 5.7.15

[andresailer]

The CERN DBonDemand test instances servers where recently(?) updated to mysql version 5.7.15

mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.15-log |
+------------+

I now notice that the JobManager is failing to insert new entries in the JobDB.JobJDLs table with the error:

2017-03-22 10:07:37 UTC WorkloadManagement/JobManager/JobDB  DEBUG: _update: Execution failed. 1364: Field 'JDL' doesn't have a default value
2017-03-22 10:07:37 UTC WorkloadManagement/JobManager/JobDB  ERROR: Can not insert New JDL MySQL Error ( 1131 : Execution failed.: ( 1364: Field 'JDL' doesn't have a default value ))

Is this because the __insertNewJDL function only fills the OriginalJDL field? https://github.com/DIRACGrid/DIRAC/blob/rel-v6r17/WorkloadManagementSystem/DB/JobDB.py#L918

Edit: Change version

[fstagni]

Good, thanks for reporting (it's version 5.7.15 not 5.17, and the upgrade was done about 3 weeks ago IIRC). Anyway, there's also another fix which is in https://github.com/DIRACGrid/DIRAC/pull/3291 for fixing this:

The REFERENCES privilege is unused before MySQL 5.7.6. As of 5.7.6, creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

Which is found at https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html.

This new error probably comes from http://feed.askmaclean.com/archives/upgrading-to-mysql-5-7-beware-of-the-new-strict-mode.html Which means that we may probably need to allow NULL in JDL in JobJDLs which now is:

CREATE TABLE `JobJDLs` (
  `JobID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `JDL` MEDIUMBLOB NOT NULL,
  `JobRequirements` BLOB NOT NULL,
  `OriginalJDL` MEDIUMBLOB NOT NULL,
  PRIMARY KEY (`JobID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The dbod used for DIRAC certification has also been updated, so we may test there. I will also go through https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade now and do the Jenkins tests.

[fstagni]

I can reproduce this in Jenkins now.

There are also issues with 'GROUP BY' in FTSDB (cc @chaen): https://lhcb-jenkins.cern.ch/jenkins/view/DIRAC/job/DIRAC_v6r18_INTEGRATION/ws/ServerInstallDIR/runit/DataManagement/FTSManager/log/current/*view*/

[chaen]

I'll give it a look tomorrow (Friday). However, I see mentions of FTS2 in the jenkins logs. We should be using FTS3.

[andresailer]

I applied this on my test instance:

$ diff JobDB.py~ JobDB.py
924c924,926
<     result = self.insertFields( 'JobJDLs' , ['OriginalJDL'], [jdl] )
---
>     result = self.insertFields( 'JobJDLs' ,
>                                 ['JDL', 'JobRequirements', 'OriginalJDL'],
>                                 ['', '', jdl] )

And job submission is working again

[chaen]

I fixed the view in the DIRAC certif DB. I will correct the code in PR for newly created instances, but the existing one must be modified. It is a change related to a mysql version, not to a dirac version, so where do we document that ?

ALTER VIEW  FTSHistoryView as select `FTSJob`.`Status` AS `Status`,sum(`FTSJob`.`Files`) AS `Files`,`FTSJob`.`TargetSE` AS `TargetSE`,(sum(`FTSJob`.`Completeness`) / count(distinct `FTSJob`.`FTSJobID`)) AS `Completeness`,sum(`FTSJob`.`FailedSize`) AS `FailedSize`,sum(`FTSJob`.`Size`) AS `Size`,sum(`FTSJob`.`FailedFiles`) AS `FailedFiles`,count(distinct `FTSJob`.`FTSJobID`) AS `FTSJobs`,`FTSJob`.`SourceSE` AS `SourceSE` from `FTSJob` where (`FTSJob`.`LastUpdate` > (utc_timestamp() - interval 3600 second)) group by `FTSJob`.`SourceSE`,`FTSJob`.`TargetSE`,`FTSJob`.`Status` ;

[andresailer]

Does it work for 5.6 and 5.7 ?

[chaen]

yes

[chaen]

Since we put fixes for v6r18 containing changes for DB, I propose we link it all to that version

[chaen]

Btw, just a useful piece of info. If you have a MySQL 5.6 DB and want to test changes related to the group by, you can enable the "mysql 5.7 mode" on your interactive session by doing this:

set session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 

If instead of session you do global, then of course....

[chaen]

I just checked the code of teh DMS and RMS, and I think we are safe for these 2

[fstagni]

Should be fixed (#3295 + #3296 )