Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: 1.7, 1.8, 1.9
-
Fix Version/s: None
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Environment:Instances using PostgreSQL
-
Database:PostgreSQL
-
Difficulty:Easy
-
Affected Branches:MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
Description
If you alter a PostgreSQL table field using the XMLDB editor from a Binary type to a Text type and then apply that change through the upgrade.php mechanism, PHP throws the following notice...
Query failed: ERROR: column [columname] is of type text but expression is of type bytea
HINT: You will need to rewrite or cast the expression
The upgrade actually continues, the new field is created but there is no data copied over from the old field to the new one because the step during which XMLDB tries to copy the old data to the temporary field fails as PostgreSQL does not allow for Binary data to be copied to a Text field using a SET in an UPDATE sql statement.
I have attached a patch for your consideration which modifies the PostgreSQL generator class for XMLDB to add a check for this specific Binary-Text conversion and if so adds an "ENCODE()" funtion call to the sql statement (note: you cannot use CAST() in this case). This has worked for us in a devel/test environment but should be confirmed by others if possible.
This conversion from Binary to Text may not sound all that wise, but it is possible to configure using the XMLDB editor and if supported then we probably need a fix for it under PostgreSQL to actually handle the conversion in the database. I hope this patch helps.
Attachments
Activity
- All
- Comments
- History
- Activity
- Source
- Test Sessions
Great Dean,
I've moved this to MDL-14679 (where all the DB related tasks are grouped). Will be implemented soon.
Ciao 
Uhm... Dean,
well spotted! Unluckily, main problem with this conversion is that it cannot be done successfully in all DBs and in all cases, because we cannot guarantee that original binary contents are "text-able" and also some DB store binaries in encoded format (not raw).
So I think it would be better to stop such type of conversions. In 2.0 we can do it easily, by throwing corresponding exceptions if "invalid" ones are detected. In 1.9.x it isn't easy to fix.
So I'd propose to:
How does that sound?
- address this for 2.0
- make any binary (from/to) conversion to drop an exception always.
- prevent in XMLDB editor to change the type of binary columns.
How does that sound?