Moodle
  1. Moodle
  2. MDL-11810

adodb-mssql_n converts empty string to NULL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.9
    • Fix Version/s: 1.8.4, 1.9, 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Database:
      Microsoft SQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE
    • Rank:
      30051

      Description

      I don't know if this is specific to the mssql_n driver, but I can reproduce it with the following:

      $enroldb = ADONewConnection($CFG->dbtype);
      $enroldb->Connect($CFG->dbhost, $CFG->dbuser, $CFG->dbpass, $CFG->dbname, true);
      $enroldb->SetFetchMode(ADODB_FETCH_ASSOC);
      $enroldb->execute('set ansi_nulls on;
      set ansi_warnings on;
      set ansi_padding on;
      set concat_null_yields_null on;');
      // then examine the contents of
      $enroldb->execute('select * from mdl_config')->getArray();

      Fields that show up as being '' from tsql and MSSQL Server Management Express are NULL in the script.

      This causes a problem upgrading to 1.9 because the admin page keeps on thinking that their are new configuration settings.

      1. freetds_dblib.c.patch
        0.8 kB
        Eloy Lafuente (stronk7)
      2. php_mssql.c.patch
        0.5 kB
        Eloy Lafuente (stronk7)
      3. test.php
        0.8 kB
        Eloy Lafuente (stronk7)

        Issue Links

          Activity

          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi Alan,

          I remember that there was some bug in FreeTDS or PHP causing that. After a looking for it a bit, I've found this:

          The FreeTDS bug tracker (I ignited!):

          http://sourceforge.net/tracker/index.php?func=detail&aid=1547066&group_id=33106&atid=407806

          The PHP bug that Freddy found and submitted to PHP bug tracker:

          http://bugs.php.net/bug.php?id=39213

          Note that the bugs is closed as fixed for PHP > 5.1.6

          So it's beyond our capabilities to perform any change, in fact the mssql_n ADOdb driver hasn't any way to differentiate those false NULLs from the real ones, so the correct behaviour should be to re-compile PHP / FreeTDS, or to use one PHP version with the bug fixed or to use the FreeTDS alternative (ODBTP) that doesn't present this problem.

          Closing this now, it would be great if you can test it with higher versions and confirm if it's working.

          Show
          Eloy Lafuente (stronk7) added a comment - Hi Alan, I remember that there was some bug in FreeTDS or PHP causing that. After a looking for it a bit, I've found this: The FreeTDS bug tracker (I ignited!): http://sourceforge.net/tracker/index.php?func=detail&aid=1547066&group_id=33106&atid=407806 The PHP bug that Freddy found and submitted to PHP bug tracker: http://bugs.php.net/bug.php?id=39213 Note that the bugs is closed as fixed for PHP > 5.1.6 So it's beyond our capabilities to perform any change, in fact the mssql_n ADOdb driver hasn't any way to differentiate those false NULLs from the real ones, so the correct behaviour should be to re-compile PHP / FreeTDS, or to use one PHP version with the bug fixed or to use the FreeTDS alternative (ODBTP) that doesn't present this problem. Closing this now, it would be great if you can test it with higher versions and confirm if it's working.
          Hide
          Alan Trick added a comment -

          We're using PHP 5.2.0. The functions both start with:

          if (dbdatlen(mssql_ptr->link,offset) == 0)

          { ZVAL_NULL(result); return; }

          Does this need to be patched and recompiled?

          The reason I thought this was an ADOdb issue was because we're only having issues with it on 1.9. We're running 1.8 on our production site and it works fine.

          Show
          Alan Trick added a comment - We're using PHP 5.2.0. The functions both start with: if (dbdatlen(mssql_ptr->link,offset) == 0) { ZVAL_NULL(result); return; } Does this need to be patched and recompiled? The reason I thought this was an ADOdb issue was because we're only having issues with it on 1.9. We're running 1.8 on our production site and it works fine.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Uhm... really strange. PHP 5.2.4 has, in php_mssql_get_column_content_with_type()

          if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0)

          { ZVAL_NULL(result); return; }

          And that should fix the problem, according with Freddy and the PHP bug.

          But I've recompiled my PHP 5.2.4 (with that change applied also to the other function) and the problem persists

          Also, I've tried under Moodle 1.8 and the same problem happens so I really think it isn't a Moodle related thing.

          Just being really tired now. Tomorrow I'll re-re-compile everything, not sure if I've cleaned everything properly.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Uhm... really strange. PHP 5.2.4 has, in php_mssql_get_column_content_with_type() if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0) { ZVAL_NULL(result); return; } And that should fix the problem, according with Freddy and the PHP bug. But I've recompiled my PHP 5.2.4 (with that change applied also to the other function) and the problem persists Also, I've tried under Moodle 1.8 and the same problem happens so I really think it isn't a Moodle related thing. Just being really tired now. Tomorrow I'll re-re-compile everything, not sure if I've cleaned everything properly. Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Although I'm pretty sure it isn't Moodle, I reopen this to remember I need to test recompling my PHP box.

          Show
          Eloy Lafuente (stronk7) added a comment - Although I'm pretty sure it isn't Moodle, I reopen this to remember I need to test recompling my PHP box.
          Hide
          Alan Trick added a comment -

          You're right, for some reason I never noticed this before. I'll talk to the sysadmin and see if he can upgrade PHP.

          Show
          Alan Trick added a comment - You're right, for some reason I never noticed this before. I'll talk to the sysadmin and see if he can upgrade PHP.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Yep, confirmed. I've:

          1) Downloaded php 5.2 4 source code
          2) Downloaded freeTDS 0.64 sorce code
          3) Compiled and installed freeTDS
          4) Compiled and installed PHP with --with-mssql=/usr/local (that way it uses freeTDS).
          5) Performed tests and everything worked fine NULLs are returned as NULLs and empty strings are returned as empty strings.

          So the bugfix is correct and everything should work after PHP 5.1.6. Forget the modification I suggested above, it isn't necessary for PHP5 (in fact I've deleted such comment to avoid confusion).

          Going to add one comment in the Installing MSSQL Moodle Docs page to inform people about the PHP > 5.1.6 req.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Yep, confirmed. I've: 1) Downloaded php 5.2 4 source code 2) Downloaded freeTDS 0.64 sorce code 3) Compiled and installed freeTDS 4) Compiled and installed PHP with --with-mssql=/usr/local (that way it uses freeTDS). 5) Performed tests and everything worked fine NULLs are returned as NULLs and empty strings are returned as empty strings. So the bugfix is correct and everything should work after PHP 5.1.6. Forget the modification I suggested above, it isn't necessary for PHP5 (in fact I've deleted such comment to avoid confusion). Going to add one comment in the Installing MSSQL Moodle Docs page to inform people about the PHP > 5.1.6 req. Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          I've updated http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix to inform about the PHP bug.

          It would be great, Alan, if you can confirm that your newer PHP version (>5.1.6) fixes the problem.

          TIA and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - I've updated http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix to inform about the PHP bug. It would be great, Alan, if you can confirm that your newer PHP version (>5.1.6) fixes the problem. TIA and ciao
          Hide
          Alan Trick added a comment -

          We just upgraded to 5.2.4 yesterday, but the problem still exists. I checked mssql.c and it has the proper changes in it. We have php configured with --with-mssql=/usr (that's where freeTDS is installed).

          Show
          Alan Trick added a comment - We just upgraded to 5.2.4 yesterday, but the problem still exists. I checked mssql.c and it has the proper changes in it. We have php configured with --with-mssql=/usr (that's where freeTDS is installed).
          Hide
          Alan Trick added a comment -

          This might explain some things:

          create table nulltest (test varchar(5));
          create table nulltestnvar (test nvarchar(5));
          create table nulltestntext (test ntext);
          create table nulltestnvarmax (test nvarchar(max));

          insert into nulltest values('');
          insert into nulltest values(null);
          select * from nulltest;

          – (repeat above three for all tables)

          nulltest and nulltestnvar will yield '' and null, but the nulltestntext and nulltestnvarmax will give null for both values

          Show
          Alan Trick added a comment - This might explain some things: create table nulltest (test varchar(5)); create table nulltestnvar (test nvarchar(5)); create table nulltestntext (test ntext); create table nulltestnvarmax (test nvarchar(max)); insert into nulltest values(''); insert into nulltest values(null); select * from nulltest; – (repeat above three for all tables) nulltest and nulltestnvar will yield '' and null, but the nulltestntext and nulltestnvarmax will give null for both values
          Hide
          Alan Trick added a comment -

          Also, this is a problem with the mssql extension cause the mssql* functions give the same result

          Show
          Alan Trick added a comment - Also, this is a problem with the mssql extension cause the mssql* functions give the same result
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Uhm... really strange,

          after recompiling FreeTDS and PHP 5.2.4 here, I performed tests against the config table (whose "value" field is a ntext column). And I got '' (empty strings) for a lot of settings.

          Are you sure that you've recompiled PHP with FreeTDS support? The phpinfo page should show "FreeTDS" in the mssql section.

          Going to perform your tests here now.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Uhm... really strange, after recompiling FreeTDS and PHP 5.2.4 here, I performed tests against the config table (whose "value" field is a ntext column). And I got '' (empty strings) for a lot of settings. Are you sure that you've recompiled PHP with FreeTDS support? The phpinfo page should show "FreeTDS" in the mssql section. Going to perform your tests here now. Ciao
          Hide
          Alan Trick added a comment -

          Yeah, we're using FreeTDS: https://courses.mytwu.ca/enrol/twu/info.php#module_mssql

          I reported this on the PHP bug tracker: http://bugs.php.net/bug.php?id=43043

          Show
          Alan Trick added a comment - Yeah, we're using FreeTDS: https://courses.mytwu.ca/enrol/twu/info.php#module_mssql I reported this on the PHP bug tracker: http://bugs.php.net/bug.php?id=43043
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Wow,

          I cannot understand why yesterday I got empty and nulls 100% diferenciated. In fact my test was really simple:

          var_dump($CFG);

          and I got proper empty strings for a lot of config settings! Going to test it again (I switched to ODBTP after tests, grrr).

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Wow, I cannot understand why yesterday I got empty and nulls 100% diferenciated. In fact my test was really simple: var_dump($CFG); and I got proper empty strings for a lot of config settings! Going to test it again (I switched to ODBTP after tests, grrr). Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          And, after 10 more recompilations.... I'm not able to get it working!

          So it seems that yesterday I confused, once again, my php extensions. Sorry for that.

          Also, Alan, I've found this: http://bugs.php.net/bug.php?id=41539

          Seems to be exactly the problem you describe in your PHP bug and it isn't fixed in PHP CVS (although the reported provides a working hack). Going to see what happens with that hack.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - And, after 10 more recompilations.... I'm not able to get it working! So it seems that yesterday I confused, once again, my php extensions. Sorry for that. Also, Alan, I've found this: http://bugs.php.net/bug.php?id=41539 Seems to be exactly the problem you describe in your PHP bug and it isn't fixed in PHP CVS (although the reported provides a working hack). Going to see what happens with that hack. Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi Alan,

          I've been playing a bit the the cool explanations of the problem at http://bugs.php.net/bug.php?id=41539 and I think I've one initial workaround for the problem (a patch that requires recompiling both FreeTDS and PHP).

          I've attached two patch files:

          • freetds_dblib.c.patch: to be applied to FreeTDS 0.64 src/dblib/dblib.c
          • php_mssql.c.patch: to be applied to PHP 5.2.4 ext/mssql/php_mssql.c

          With those two patches applied and recompiling both FreeTDS and PHP and getting correct NULLs and empty-es in my tests and with the code you sent to http://bugs.php.net/bug.php?id=43043

          Also, I've opened one bug request in FreeTDS tracker, to explain the problem and document it AFAIK. I'm not sure if that's the best solution but I hope it's the basis for a final (soon) one. You can find it at:

          http://sourceforge.net/tracker/index.php?func=detail&aid=1817075&group_id=33106&atid=407806

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Hi Alan, I've been playing a bit the the cool explanations of the problem at http://bugs.php.net/bug.php?id=41539 and I think I've one initial workaround for the problem (a patch that requires recompiling both FreeTDS and PHP). I've attached two patch files: freetds_dblib.c.patch: to be applied to FreeTDS 0.64 src/dblib/dblib.c php_mssql.c.patch: to be applied to PHP 5.2.4 ext/mssql/php_mssql.c With those two patches applied and recompiling both FreeTDS and PHP and getting correct NULLs and empty-es in my tests and with the code you sent to http://bugs.php.net/bug.php?id=43043 Also, I've opened one bug request in FreeTDS tracker, to explain the problem and document it AFAIK. I'm not sure if that's the best solution but I hope it's the basis for a final (soon) one. You can find it at: http://sourceforge.net/tracker/index.php?func=detail&aid=1817075&group_id=33106&atid=407806 Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi Alan,

          it seems that Freddy (the FreeTDS man) has fixed it in current 0.64 download. I'm going to install PHP from scratch (without any hack at all) and try to compile current FreeTDS 0.64 to see what happens.

          Complete info at: https://sourceforge.net/tracker/?func=detail&atid=407806&aid=1817075&group_id=33106

          Show
          Eloy Lafuente (stronk7) added a comment - Hi Alan, it seems that Freddy (the FreeTDS man) has fixed it in current 0.64 download. I'm going to install PHP from scratch (without any hack at all) and try to compile current FreeTDS 0.64 to see what happens. Complete info at: https://sourceforge.net/tracker/?func=detail&atid=407806&aid=1817075&group_id=33106
          Hide
          Alan Trick added a comment -

          Thanks, let me know how it goes. I want to get this fixed before we deploy 1.9, it's not a blocker for me, but it would be nice.

          Show
          Alan Trick added a comment - Thanks, let me know how it goes. I want to get this fixed before we deploy 1.9, it's not a blocker for me, but it would be nice.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Seems to be working. I've followed these steps:

          1) Download latest stable (0.64) FreeTDS from http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
          2) Patched it with the proper "post 0.64" patch from http://freetds.sourceforge.net/
          3) configure; make; make install
          4) Compiled PHP 5.2.5 with the option "--with-mssql=/usr/local"

          Then I've executed the test.php script (attached above) and empties are empties and nulls are nulls for all the text types (varchar, nvarchar, ntext and nvarchar(max).

          Cool!

          Show
          Eloy Lafuente (stronk7) added a comment - Seems to be working. I've followed these steps: 1) Download latest stable (0.64) FreeTDS from http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz 2) Patched it with the proper "post 0.64" patch from http://freetds.sourceforge.net/ 3) configure; make; make install 4) Compiled PHP 5.2.5 with the option "--with-mssql=/usr/local" Then I've executed the test.php script (attached above) and empties are empties and nulls are nulls for all the text types (varchar, nvarchar, ntext and nvarchar(max). Cool!
          Hide
          Alan Trick added a comment -

          I had our sysadmin patch and compile this stuff and now the tests all work

          I think you can close this if you want.

          Show
          Alan Trick added a comment - I had our sysadmin patch and compile this stuff and now the tests all work I think you can close this if you want.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Thanks for feedback, I've added some information in http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix to try people know about this problem with 0.64 FreTDS and the need to apply patches before compiling it.

          Closing, ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Thanks for feedback, I've added some information in http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix to try people know about this problem with 0.64 FreTDS and the need to apply patches before compiling it. Closing, ciao
          Hide
          David Mudrak added a comment -

          For the record: FreeTDS 0.82-r3 + PHP 5.3.5 works for me out of the box without the patch (at gentoo)

          Show
          David Mudrak added a comment - For the record: FreeTDS 0.82-r3 + PHP 5.3.5 works for me out of the box without the patch (at gentoo)

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: