We're using MS-SQL (yes, I know...) so there's various issues with comparing ntext columns. I've fixed some of these by casting to nvarchar(max), but some code is more abstract.
These issues seem to be resolvable by changing the column type in the relevant tables to nvarchar(max).
Will this break anything? Will it cause moodly confusion when we upgrade moodle later? Will the upgrades try to change the columns back? Do I need to make modifications to the XMLDB definitions?
ta,
Hi Gregor,
It's very unlikely that changing the column type will break anything (as long as you're not doing things like changing strings into integers obviously). The upgrades will usually never touch your existing tables, but they may add new columns of the type you described.
By the way, you can also perform a test first with a cloned database, right?
Regards,
Onno
I would strongly disagree - changing the schema is generally a very bad idea. You may not run into problems immediately, you may not run into problems 6 months from now, but you will run into problems and it will most likely happen during an upgrade or immediately following one.
Gregor - It isn't clear to me whether the columns you need to change are for your own edifices, or whether they would benefit all Moodle institutions using MSSQL. It might help the conversation if you could list some examples of fields that you want to change.
Kris,
All database specific datatypes are eventually converted to php datatypes anyway. Granted, in cases where database columns are directly compared to other database columns you may run into trouble. This is why you need to test your changes thoroughly.
There just aren't that many Moodle upgrades that change the database column types.
I'd agree it's a "hacky" solution, though.
Which version of Moodle are you using? Most / all such problems should be fixed by Moodle 2.3.2. As someone else said, if you find some that aren't fixed in the current version, it would be best to create a tracker issue with your proposed fix and let other people look at that specific case.
If you're fixing an old version, have a look at how it has been fixed in newer versions. Look at the diff in the tracker issue.
It needs a close look because changing from ntext to nvarchar will make the column comparable against other nvarchar columns, but make it not comparable against the columns it used to be comparable to! So you could fix one problem, but create three more in the process. Submitting a tracker issue and letting other people look at it will reduce those problems, and it'll get the fix into the next version so that it continues to work when you upgrade.
We're using Moodle 2.4.1
MSSQL issues are like playing whack-a-mole. There's always more of them-- it even causes a number of unit tests to fail. Oh well, we're living with it.
I filed the tracker report before posting here as https://tracker.moodle.org/browse/MDL-37788 -- I just thought I'd get quicker response here to the suggested workaround-- it comes up quite a bit.
The affected tables in this case are mdl_scale, and mdl_grade_outcomes.
Generally in the past I've posted the error, and locally done a cast, until the problem is fixed properly then I patch our instance.
I'm happy to change core code, but not so happy to change the core schema.
Of course we have a development instance-- that's where I tested that changing the schema would solve the immediate import outcomes problem-- it's the "I'll just wait here quitely and jump out at you later" problems I'm want to avoid.
Obviously there is not agreement on this point; I'll eschew altering the schema, and wait for a proper fix.
