Unknown column 'type' in privatemsg table

I started getting this on the latest dev version dated 7th August.

user warning: Unknown column 'type' in 'field list' query: SELECT id, subject, p.timestamp, u.uid, u.name, newmsg, type FROM privatemsg p INNER JOIN users u ON p.author = u.uid WHERE p.recipient = 1 AND folder = 0 AND p.recipient_del = 0 ORDER BY timestamp DESC LIMIT 0, 20 in /home/daihatsu/public_html/includes/database.mysql.inc on line 172.
user warning: Unknown column 'type' in 'field list' query: SELECT DISTINCT type FROM privatemsg WHERE folder = 0 AND recipient_del = 0 AND recipient = 1 in /home/daihatsu/public_html/includes/database.mysql.inc on line 172.

I got same errors after an upgrade of module.
It seems to be caused by the privatemsg.install file :
There is no ALTER TABLE on {privatemsg} adding the field "type".

A second error is that "db_query("CREATE TABLE {privatemsg_mails} )" should be replace by
"db_query("CREATE TABLE {privatemsg_mails} (" in the code.
This table is not added in the hook_update_version.

To use this module, I run this SQL :

CREATE TABLE privatemsg_mails (
  description varchar(255) NOT NULL,
  type varchar(255) NOT NULL,
  subject varchar(255) NOT NULL,
  body varchar(255) NOT NULL,
  notification_subject varchar(255) NOT NULL,
  notification_text_body varchar(255) NOT NULL,
  notification_html_body varchar(255) NOT NULL,
  notification_group_text_body varchar(255) NOT NULL,
  notification_group_html_body varchar(255) NOT NULL,
  mailkey varchar(255) NOT NULL,
  PRIMARY KEY  (mailkey)
) /*!40100 DEFAULT CHARACTER SET utf8 */;


ALTER TABLE `privatemsg` ADD `type` VARCHAR( 255 ) NOT NULL ;
ALTER TABLE `privatemsg` ADD INDEX ( `type` ) ;

I don't really have investigate nor test all functionnalities but errors are disappears.

i have the same problem as matrix (after updating to the latest version: 5.x-2.x-dev (2007-Aug-17)).

    * user warning: Table 'mysqltable.main_privatemsg_block_user' doesn't exist query: SELECT COUNT(*) FROM main_privatemsg_block_user WHERE author = 1 AND recipient = 24 in /home/files/public_html/drupal5/includes/database.mysql.inc on line 172.
    * user warning: Table 'mysqltable.main_privatemsg_block_user' doesn't exist query: SELECT COUNT(*) FROM main_privatemsg_block_user WHERE author = 24 AND recipient = 1 in /home/files/public_html/drupal5/includes/database.mysql.inc on line 172.

How to apply webastien's 'SQL':

You can run an SQL query within the program 'phpMyAdmin':

- Select the database used for your website in the left dropdwon menu.
- In the main part of the program window on the right, click on the tab named 'SQL'.
- There you find a text field where you can enter so-called SQL queries. Paste in the code webastien posted and click 'ok'.
If there are problems with the query, phpMyAdmin will show it afterwards.

You should always make a backup of your database before doing such querie! In case something goes wrong, you can simply reinstall this backuped version.

How to backup:
You can make a backup within 'phpMyAdmin' by clicking on the tab 'Export'. To install the backuped (former) version of a database, click on the 'import' tab.
--> Here you find a short guideline on how to do this: http://drupal.org/node/120630
--> There's also a module for Drupal that helps to backup, manage and load your database: http://drupal.org/handbook/modules/dba

I just posted a solution the other issue ('user warning: Table 'mysqltable.main_privatemsg_block_user' doesn't exist ') here: http://drupal.org/node/167756

sorry for opening duplicate in http://drupal.org/node/168948
after applying the code in #2 everything started to work. thnx!

thanks olio.

but for the table privatemsg_block_user: how do i define the table structure? i am a little hesitant to do the mysql instructions as i am not familiar with the table create commands.

my drupal database only contains the following tables (for privatemsg):

privatemsg
privatemsg_archive
privatemsg_folder
privatemsg_mails

no privatemsg_block_user...

and what about -

privatemsg_mail_edit

anything else?

We're mixing two different issues here!

OP through #3 are concerned with the missing privatemsg.type column, which was added in 1.5.2.4.2.2 on August 1. Unfortunately, no update function was provided, and I don't know how to check for the presence of a column. To resolve this issue, you should probably disable and uninstall the module and then reinstall the current version. However, you'll then encounter the following issue (until chx commits the patch):

#4, #5, and #8 talk about the missing privatemsg_block_user table. That issue is discussed in http://drupal.org/node/167756. Please go there!

now i am confused. where should i go with the following error message:
Table 'mydb.privatemsg_block_user' doesn't exist query: SELECT COUNT(*) FROM privatemsg_block_user WHERE author = 1 AND recipient = 1 in /home/mysite/public_html/includes/database.mysql.inc on line 155.

everything seems to work except blocking users' messages, but log contains lot's of above mentioned error.

Hi!
I think it's the best to follow salvis link and to wait until there's a solution to all mentioned issues! It seems that there are some more problems with the install file than the one I realized (by far, I'm not a pro developer, so I better keep my hands off). In the meantime, if you need a stable version of privatemsg, then you should install the version 1.8 and wait until the 2.0 version is fixed.

For those having problems to go back to older versions of a module, here is the way to clean the drupal database before installing the desired module version. I don't know if there's a better, easier, more secure... way? At least, it's the way I always do it :
- backup the database!!!
- disable the module in admin/build/modules
- move the folder of the module out of your modules folder
- check if all tables the module had installed are really gone. To do this, go in PHPMyAdmin and search for the tables created by the .install file (that you find in the folder of the module).
- if they are still there, delete these tables
- in phpadmin, go to the table 'system' and look for the entry of the module. If you have a lot of modules installed, click on the tab 'search' and enter the module's name in the field 'value' of the row called 'name' (name... varchar...LIKE...), then click o.k. to search.
- click on the red cross in the row of the system table entry of the module to delete it.
- I don't know if it's necessary, but I always clean the cache (I use the devel.module for that) before installing the new module version.
- now install the desired version of the module.

I'm sorry, I should have changed the issue title. If you want to say something about the missing privatemsg.type column, then say it here, but we already know it's broken. Anything else should go elsewhere.

There are a few other things broken in privatemsg.install, and it doesn't make sense to open up an issue for each one and develop separate patches. I'm working to fix them all in one sweep under the http://drupal.org/node/167756 issue, which is the issue about the missing privatemsg_block_user table, and I'll report back here when it's done.

Re #11: Disabling a module should NOT remove any tables, so that you can reenable it again without losing any data.

However, well-written modules should include uninstall functionality (if they store data). Check admin/build/modules/uninstall after disabling your module, and if it shows up on that list, you should be able to remove its tables and variables right there.

My patch in http://drupal.org/node/167756 also updates the uninstall function, so that it should clean up properly after Privatemsg. (It doesn't hurt that the record in the system table remains, as long as schema_version is reset to -1.)