PostgreSQL compatibility issues [#2261293]

We are a PostGreSQL shop. I am documenting this issue we are having with the module and uploads of the standard content. The file is 100% uploaded but when processing of the content begins we get the following error. The error is handled a number of times. I believe it to be directly related to the processing of the package after the upload. The site stops on a single line error message but in the logs there are numerous errors posted.

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer = character varying LINE 4: LEFT OUTER JOIN sessions s ON sap.sid = s.sid ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT sap.sid AS sid FROM {session_api} sap LEFT OUTER JOIN {sessions} s ON sap.sid = s.sid WHERE (sap.timestamp < :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 1396889101 ) in session_api_cron() (line 116 of /home/profilees/public_html/sites/all/modules/session_api/session_api.module).

Either myself or one of the technical partners will be adding to this post when we have time to investigate. If someone has some feedback please let us know.

Comments

Comment #1

paalj commented 14 May 2014 at 12:52

This seems to be related to a bug in the session_api module. You should try disabling that module, and see if the H5P-upload is successfull. If so is the case, you should create an issue here: http://drupal.org/project/issues/session_api

Comment #2

paalj commented 14 May 2014 at 12:52

Assigned:

Unassigned

» paalj

Comment #3

ben coleman commented 14 May 2014 at 22:19

I don't think this has anything to do with session_api. I disabled Session API, and got the same results. Also, I created a bare Drupal 7 site, and installed H5P, and got the same thing.

The full errors produced are:

Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->loadLibrary() (line 374 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
Notice: Undefined index: libraryId in H5PDrupal->saveContentData() (line 282 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).
PDOException: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "main_library_id" violates not-null constraint: INSERT INTO h5p_nodes (content_id, nid, json_content, embed_type, main_library_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( ) in H5PDrupal->saveContentData() (line 284 of /home/profilees/public_html/sites/all/modules/h5p/h5p.classes.inc).

Note that the last I in libraryId in the Undefined Index messages is capitalized. I lower-cased it and retried, and while the upload succeeded, I get a lot more Undefined Index notices, and the uploaded file does not display.

I did a bit of digging, and the problem appears to be the use of mixed-case AS names in SQL. E.g., the loadContent function in h5p.classes.inc at line 429:

  public function loadContent($id) {
    $content = db_query(
      "SELECT hn.json_content AS params
            , hn.embed_type AS embedType
            , hl.library_id AS libraryId
            , hl.machine_name AS libraryName
            , hl.major_version AS libraryMajorVersion
            , hl.minor_version AS libraryMinorVersion
            , hl.embed_types AS libraryEmbedTypes
            , hl.fullscreen AS libraryFullscreen
       FROM {h5p_nodes} hn
       JOIN {h5p_libraries} hl ON hl.library_id = hn.main_library_id
       WHERE content_id = :id", array(':id' => $id))->fetchAssoc();

    return ($content === FALSE ? NULL : $content);
  }

PostgreSQL will lower-case those AS field names, so that while MySQL will return $content with indexes of 'params', 'embedType', 'libraryId', 'libraryName', 'libraryMajorVersion', 'libraryMinorVersion', 'libraryEmbedTypes', and 'libraryFullscreen', PostgreSQL returns $content with indexes of 'params', 'embedtype', 'libraryid', 'libraryname', librarymajorversion', 'libraryminorversion', libraryembedtypes', and 'libraryfullscreen'.

I don't see any way of making this cross-platform compatible except changing the AS field name to lower-case, and fixing the index references to them all through the code.

Comment #4

ben coleman commented 14 May 2014 at 22:20

Title:	Errors on upload of standard content	» Postgres compatability issues
Version:	7.x-1.0-beta4	» 7.x-1.0-beta5

Comment #5

ben coleman commented 14 May 2014 at 22:39

Title:

Postgres compatability issues

» PostgreSQL compatibility issues

Comment #6

chris pergantis commented 29 May 2014 at 22:11

Issue tags:

+PostgreSQL compatability

the real problem that is indicated by Ben above is that the code within the H5P module has mixed case AS field names. PostgreSQL handles the field names completely different than MySQL. It takes the mixed case and turns them all to lower case. This one simple fact makes the entire module unusable by postgresql shops. PostgreSQL is one of the standards for Drupal.

We really need to have the code to be PostgreSQL compliant. Currently it is not.

I will work up a patch next week and post it here. We are able to test the PostgreSQL but someone else will need to test the MySQL side.

Comment #7

falcon commented 2 June 2014 at 11:10

Please do, we'll test your patch against mysql and commit it

Comment #8

chris pergantis commented 5 June 2014 at 21:26

It seems the patch also effect the importable content types. I cannot import the provided ones after making the changes I believe would help. I will have to regroup. The project itself may have this issue embedded in it. I am going to git the h5p project and try examining the code and the sql calls before i move forward.

Comment #9

chris pergantis commented 3 July 2014 at 19:33

I have devised a way to get the returned information restructured to your format. I will drop a patch here next week. The patch will be more for a proof of concept than a final solution.

Comment #10

icc commented 18 July 2014 at 09:47

Status:

Active

» Needs review

A patch has been submitted at http://h5p.org/node/746 , it has been reworked to better help other systems and commit-ed to the 7.x-1.x branch. Works well on my local Drupal 7 with PostgreSQL. Feel free to test.