Migration of messages does not work under PostgreSQL [#396498]

2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18354, 122, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18355, 2240, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18355, 122, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18356, 3223, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18356, 93, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18357, 3244, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18357, 3975, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18358, 2006, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18358, 1791, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18359, 3975, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18359, 1791, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18360, 1575, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18360, 1791, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18361, 2648, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18361, 1791, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18362, 0, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18362, 1791, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18363, 1838, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18363, 1, 0, 0)
2009-03-09 21:41:00 CET ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »
2009-03-09 21:41:00 CET INSTRUCTION :  INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (18364, 3625, 0, 0)

When a value is not given, PostgreSQL and other databases assume "NULL" which means "no value" in SQL.
Thefore, as thread_id is described as NOT NULL, the query will not be accepted.

To work, just pass-on "0" for thread_id or any value.

I was not able to find how to do it in phpbb2privatemsg_import

Comments

Comment #1

grub3 commented 9 March 2009 at 20:52

Or maybe thread_index could be describe as NOT NULL default 0 in the schema.

Comment #2

naheemsays commented 9 March 2009 at 20:59

Thread_id should never be 0 in the privatemsg module. It should = the lowest mid from the thread, in the case of migrating from phpbb2, it should be equal to the mid.

Looking at that error, it seems that the thread_id is set for all the messages?

PS thanks for using this on pgsql - Not had it tested on there, so there may be bugs in places and its good to see them ironed out.

Thanks. The nice thing with PostgreSQL is that it is a reference SQL92 database and than once it runs under PostgreSQL, it runs anywhere. Also, you have access to the SQL parser and analyser results. I log any query > 30 milliseconds and it shows me where Drupal is slow.

Could you pass me on the corrected script? I need to import 36.000 private messages.

Comment #4

naheemsays commented 9 March 2009 at 21:25

Are you importing from phpbb2?

On line 152 of phpbb2pivatemsg.module can you try replacing

$message['thread_id'] = $message_id;

with

$message['thread_id'] = $message['mid']

As far as I can see, that result you are getting should not be possible as the thread_id should be being set correctly.

Comment #5

grub3 commented 9 March 2009 at 21:33

This is line 142. Still the same error:
2009-03-09 22:31:44 CET INSTRUCTION : INSERT INTO pm_index (mid, uid, is_new, deleted) VALUES (55103, 1652, 0, 0)
2009-03-09 22:31:44 CET ERREUR: une valeur NULL viole la contrainte NOT NULL de la colonne « thread_id »

I cleared the cache.

Comment #6

grub3 commented 9 March 2009 at 21:35

Sorry my fault.

Comment #7

grub3 commented 9 March 2009 at 21:38

It still fails. I don't understand why. Here is my code:

        $result = drupal_write_record('pm_message', $message);
        $message_id = db_last_insert_id('pm_message', 'mid');
        $message['mid'] = $message_id;
        // Hack to give each message its own thread. ToDo - replace all this with proper import with threading etc.
        $message['thread_id'] = $message_id;
        $recipients = explode(":", $pm->to_address);
        $recipients_count = count($recipients);
        $count = 0;
        while ($count < $recipients_count) {
          $recipients[$count] = (int)str_replace("u_", "", $recipients[$count]);
          $recipient = db_result(db_query($query, $recipients[$count]));
          $message['uid'] = $recipient;
          $result = drupal_write_record('pm_index', $message);
          $count++;
        }
        $message['uid'] = $from; 
        $result = drupal_write_record('pm_index', $message);

Comment #8

naheemsays commented 9 March 2009 at 22:08

Can you try this:

	    //save message if no duplicates
	    $dupe = db_result(db_query('SELECT COUNT(*) FROM {pm_message} pm, {pm_index} pmi  WHERE author = %d AND timestamp = %d AND pm.mid = pmi.mid AND pmi.uid = %d', $message['author'], $message['timestamp'], $message['uid']));
	    if ($dupe <> 0) {
        $message['mid'] = $dupe['mid'];
      }
      else {
        $result = drupal_write_record('pm_message', $message);
        $message_id = db_last_insert_id('pm_message', 'mid');
        $message['mid'] = $message_id;
      }
  
      //thread ID is the same as the mid as there is no pre existing threading.
      $message['thread_id'] = $message_id;
      $message['deleted'] = 0;
      $result = drupal_write_record('pm_index', $message);
      $message['uid'] = $from;
      $message['is_new'] = 0;
      $result = drupal_write_record('pm_index', $message);
    }

If there is still a problem, then there may be a bug with drupal_write_Record.

Try replacing

  $result = drupal_write_record('pm_index', $message);

with

  $result = db_query('INSERT INTO {pm_index} VALUES (%d, %d, %d, %d)', $message['mid'], $message['thread_id'], $message['uid'], $message['is_new'], $message['deleted']);

In both places of the first quote code bit (all should be around line 140).

I think the problem is not the thread_id, but the lack of a deleted being set.

If it works, let me know which one did.

Comment #9

grub3 commented 11 March 2009 at 12:31

Status:

Active

» Fixed

In fact, I manually deleted entry in the SQL database, updated to latest SVN (the fix was published) and importing works. As a result, I set Status to "Fixed". Thanks for you work and immediate response. This is very kind of you. I hope that we can work further to migrate other issues in PhpBB. You can contact me on IRC at jmpoure.

Comment #10

naheemsays commented 11 March 2009 at 14:11

Just to be clear, are there any (further?) changes required to be made to the module? Do I need to commit the above suggestions, or does it work without them too?

Comment #11

25 March 2009 at 14:20

Status:

Fixed

» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Migration of messages does not work under PostgreSQL

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

News items

Our community

Documentation

Drupal code base

Governance of community