21 June 2012, 18:42

This issue seems to be MySQL based so what if I was to dump my mahara DB and somehow convert it to postgres? then perform the upgrade on a postgres DB?

Anyone have any tips on converting mySQL to Postgres? there seems to be a lot of different and confusing opinions on this.

21 June 2012, 20:18

Hi Simon, I'm not going to argue against anyone who wants to move to Postgres!

But it's probably quite a simple problem, and you might find you can run the upgrade with a simple tweak, like some on the other thread.

I'd really like to know if your problem with the "INSERT INTO..." query is a bug that needs fixing in Mahara, because even if you move your own data to Postgres, others may still be running into the same problem.  Is there any chance you could look into the webserver log and see if there's an error from MySQL, right before the 'Could not execute command: INSERT INTO "artefact"'?

R.

24 June 2012, 7:51

Hi Richard,

The problem is fixed and thank for your great advice.

Because I changed all url string in mahara prod db from "mahara.xxx.edu.hk" to "maharadev.xxx.edu.hk" and then dump to testing site to upgrade it . all textbox string is increased if it have ulr string and thenfore error is shown.

Now i just dump the original darabase without changing anything to another testing site and do upgrade.

No problem found when I use both CLI and GUI upgrade it.

However, It still need use 40 mins to upgrade it although I increaese the following in lib/db/upgrade.php

max_execution_time to 25000

raise_memory_limit = 2048M.

What another values should we change to decrease the upgrade time?

Also, which upgrade method is better (GUI or CLI)?

Regards,

22 June 2012, 2:42

I have the similar problem of getting so much

(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?),

in my log when my mahara is upgraded from v1.4.2 to v1.5.1.

I tried to change the memory_limit in php.ini and max_allowed_packet in my.cnf, however, it couldn't help.

Finally, I make some changes in the file below.

~mahara/artefact/internal/blocktype/textbox/db/upgrade.php

-------------------------------------

function xmldb_blocktype_textbox_upgrade($oldversion=0) {

    if ($oldversion < 2011082200) {
        ........
        $limit = 100;           // $limit = 5000;
       ......

}

-------------------------------------

I think the problem is caused by containing too much data in the insert statment, however, it exceeds the allowed packet size. Now, I update the value of $limit so that max. 100 records can be inserted to table each time.

I don't know if it is a proper way to solve your problem but it seems work on my side.

23 June 2012, 9:15

Bingo.... Thanks so sm, that has resolved it for me too. Now to update the live site.

Thanks again, very happy now :D

25 June 2012, 18:55

Did you try anything between 100 and 5000?  I'm just wondering whether it's a good idea to put out a new release that will change this limit to 100 for all MySQL DBs (I think it's ok to leave it at 5000 for postgres).  Presumably the best value for the limit is going to be a function of max_allowed_packet, so it's probably best to detect that setting before the upgrade, and set the limit accordingly.

26 June 2012, 2:35

I just changed it to 100, it only took 30-40 seconds or so to update. I did ponder something in between but did not want to have to drop, create and try again. That did eventually allow an upgrade from 1.3.6 straight to 1.5.1 too.

26 June 2012, 3:15

If I changed limit from 5000 to 1000. I need use more than 2 hours to upgrade it.

10 July 2012, 3:12

I updated the source code so that the server configuration will be checked after upgrade.
It may be better than updating the limit to 100 directly.

~mahara/artefact/internal/blocktype/textbox/db/upgrade.php

-----------------------------------------------

...
        $limit = 5000;

         // ---- updates ----
        if (is_mysql()) {
            // get max_allowed_packet
            $tmp_max_allowed_packet = get_field_sql('SELECT @@global.max_allowed_packet');

            // get the greatest data length
            $cur_max_configdata_length = get_field_sql("
                select max(length(title)+length(configdata))
                from block_instance
                where blocktype = 'textbox'");

            // calculate the possible limit 
            //   where 200 and 50 are the string length of the sql statements used in process,
            //   such as "INSERT INTO .....", "(?,?,?,?,?,?,?,?,?,?,?,?)..."
            $est_limit = ((int)($tmp_max_allowed_packet) - 200) /((int)($cur_max_configdata_length) + 50);

            // update $limit, if the estimated limit is lower than default limit, 5000
            if ($est_limit < $limit ) {
                $limit = $est_limit;
            }
        }
        // ---- end of updates ----

        while ($records = get_records_sql_array($sql, array($lastid, 'textbox'), 0, $limit)) { 

.....

-----------------------------------------------

So 

10 July 2012, 3:34

Dear So,

It works on my Mahara!!  I 've been using Mahara for a few years and there are about 20,000 records in the block-instance table.  The maximum length of the configdata field is 61421 (for a record) causing the MySQL error during the upgrade form 1.4.2 to 1.5.1!  

After upgrade to 1.5, this lengh is cut down to 4000.  Anyway, the problem is fixed now.  Thanks.

Regards,

Murphy