postgreSQL: pb with SHOW TABLES syntax. [#78866]

Running PostgreSQL (postgresql-7.4.7-3.FC2.1)
Fedora Core 2
phpBB2 version 2.0.21
Table prefix for phpbb2: phpbb_
The phpBB2 installation is on one database, and Drupal is on another (both PostgreSQL). I configured phpBB2Drupal appropriately for this setup.

Whenever I go to the phpBB2drupal configuration, I get a flurry of errors. They're all obviously because the script can't seem to find the appropriate phpBB2 tables.

   * Table phpbb_categories does not exist!
    * Table phpbb_forums does not exist!
    * Table phpbb_posts does not exist!
    * Table phpbb_posts_text does not exist!
    * Table phpbb_topics does not exist!
    * Table phpbb_users does not exist!

Some tables couldn't be found. Please correct the errors so that the phpBB2 tables can be accessed.

However, when I display the phpbb tables from within PostgreSQL, I get:

phpbb=> \dt
                      List of relations
 Schema |             Name              | Type  |   Owner
--------+-------------------------------+-------+------------
 public | phpbb_auth_access             | table | phpbb_user
 public | phpbb_banlist                 | table | phpbb_user
 public | phpbb_categories              | table | phpbb_user
 public | phpbb_config                  | table | phpbb_user
 public | phpbb_confirm                 | table | phpbb_user
 public | phpbb_disallow                | table | phpbb_user
 public | phpbb_easymod                 | table | phpbb_user
 public | phpbb_easymod_processed_files | table | phpbb_user
 public | phpbb_forum_prune             | table | phpbb_user
 public | phpbb_forums                  | table | phpbb_user
 public | phpbb_groups                  | table | phpbb_user
 public | phpbb_posts                   | table | phpbb_user
 public | phpbb_posts_text              | table | phpbb_user
 public | phpbb_privmsgs                | table | phpbb_user
 public | phpbb_privmsgs_text           | table | phpbb_user
 public | phpbb_ranks                   | table | phpbb_user
 public | phpbb_search_results          | table | phpbb_user
 public | phpbb_search_wordlist         | table | phpbb_user
 public | phpbb_search_wordmatch        | table | phpbb_user
 public | phpbb_sessions                | table | phpbb_user
 public | phpbb_sessions_keys           | table | phpbb_user
 public | phpbb_smilies                 | table | phpbb_user
 public | phpbb_themes                  | table | phpbb_user
 public | phpbb_themes_name             | table | phpbb_user
 public | phpbb_topics                  | table | phpbb_user
 public | phpbb_topics_watch            | table | phpbb_user
 public | phpbb_user_group              | table | phpbb_user
 public | phpbb_users                   | table | phpbb_user
 public | phpbb_vote_desc               | table | phpbb_user
 public | phpbb_vote_results            | table | phpbb_user
 public | phpbb_vote_voters             | table | phpbb_user
 public | phpbb_words                   | table | phpbb_user
(32 rows)

Anyone seen this problem before?

Thanks.
Jack

Comment	File	Size	Author
#12	phpbb2drupal.module	76.89 KB	beginner

Comments

Comment #1

JoeIsuzu commented 17 August 2006 at 13:17

Title:

"Some tables couldn't be found..."

» Did I do this right?...

Or should I have posted it in the "Converting to Drupal" forum?

Comment #2

beginner commented 17 August 2006 at 14:54

Title:

Did I do this right?...

» postgreSQL: pb with SHOW TABLES syntax.

Hello.
I am checking for the presence of the tables using aquery like the following:
SHOW TABLES LIKE 'phpbb_categories'
Obviously, it works for MySQL but not for postgreSQL.
Can you tell me what is the equivalent for the query above, in postgresql?

Comment #3

JoeIsuzu commented 17 August 2006 at 15:53

Looks like the "show" command is entirely different in PostgreSQL, but I haven't found an equivalent yet.

phpbb=> \h show
Command:     SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

Jack

Comment #4

beginner commented 17 August 2006 at 16:22

Both drupal and phpBB are on postgresql, aren't they?

How would you check the existence of a table, in postgresql?

Comment #5

JoeIsuzu commented 17 August 2006 at 16:34

I could "describe" all tables, along with the "list of relations" with:

phpbb=> \dt
                      List of relations
 Schema |             Name              | Type  |   Owner
--------+-------------------------------+-------+------------
 public | phpbb_auth_access             | table | phpbb_user
 public | phpbb_banlist                 | table | phpbb_user
 public | phpbb_categories              | table | phpbb_user
 public | phpbb_config                  | table | phpbb_user
 public | phpbb_confirm                 | table | phpbb_user
 public | phpbb_disallow                | table | phpbb_user
 public | phpbb_easymod                 | table | phpbb_user
 public | phpbb_easymod_processed_files | table | phpbb_user
 public | phpbb_forum_prune             | table | phpbb_user
 public | phpbb_forums                  | table | phpbb_user
 public | phpbb_groups                  | table | phpbb_user
 public | phpbb_posts                   | table | phpbb_user
 public | phpbb_posts_text              | table | phpbb_user
 public | phpbb_privmsgs                | table | phpbb_user
 public | phpbb_privmsgs_text           | table | phpbb_user
 public | phpbb_ranks                   | table | phpbb_user
 public | phpbb_search_results          | table | phpbb_user
 public | phpbb_search_wordlist         | table | phpbb_user
 public | phpbb_search_wordmatch        | table | phpbb_user
 public | phpbb_sessions                | table | phpbb_user
 public | phpbb_sessions_keys           | table | phpbb_user
 public | phpbb_smilies                 | table | phpbb_user
 public | phpbb_themes                  | table | phpbb_user
 public | phpbb_themes_name             | table | phpbb_user
 public | phpbb_topics                  | table | phpbb_user
 public | phpbb_topics_watch            | table | phpbb_user
 public | phpbb_user_group              | table | phpbb_user
 public | phpbb_users                   | table | phpbb_user
 public | phpbb_vote_desc               | table | phpbb_user
 public | phpbb_vote_results            | table | phpbb_user
 public | phpbb_vote_voters             | table | phpbb_user
 public | phpbb_words                   | table | phpbb_user
(32 rows)

Or just the ones with "user" in their name with:

phpbb=> \dt *user*
               List of relations
 Schema |       Name       | Type  |   Owner
--------+------------------+-------+------------
 public | phpbb_user_group | table | phpbb_user
 public | phpbb_users      | table | phpbb_user
(2 rows)

But I don't think the "\" commands are sql. They're just PostgreSQL internal commands. I don't know if the "\" commands work from php (I know verrry little about php).

Jack

Comment #6

havran commented 17 August 2006 at 17:21

SHOW TABLE in Postgresql (PostgreSQL docs, in user comment):

1.4. Accessing a Database

Comment #7

JoeIsuzu commented 17 August 2006 at 18:27

Ahh, good catch. I suspected the "\*" commands wouldn't work from php. I hadn't had time to dig into the PostgreSQL docs due to a day job that sometimes interferes with my hobby.

So a rough equivalent would be:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Although the output does not exactly match what comes from MySQL, it does list every public table and nothing else on the lines (but it produces some header and footer information).

Jack

Comment #8

havran commented 17 August 2006 at 18:38

MySQL variant:

SHOW TABLES LIKE 'phpbb_categories'

And PostgreSQL variant:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'phpbb_categories'

Comment #9

JoeIsuzu commented 17 August 2006 at 23:15

Yes, I was just trying to display them all. Your query matches the one given by beginner.

So, what does that get us? I wouldn't know where to start with the php coding.

Thanks.
Jack

Comment #10

JoeIsuzu commented 18 August 2006 at 17:49

So...

- Is this a verifiable bug?
- If so, is it likely to get "assigned" and patched/fixed?

I'd love to help fix it, but that's well beyond my present level of ability, and would demand more time than I have. If this isn't fixed, I would find it much easier to just find a CMS that will play nicely with my phpBB2 forum.

Thanks.
Jack

Comment #11

beginner commented 19 August 2006 at 04:23

I'll provide a patch later this afternoon.

Comment #12

beginner commented 19 August 2006 at 06:15

Status:

Active

» Needs review

Status	File	Size
new	phpbb2drupal.module	76.89 KB

I am using the posgresql query kindly provided by havran (thanks!).

Please test this module. You should have 'pgsql' printed at the very top of your settings/phpbb2drupal page.
Please report back if it works for you so that I can commit the changes.

Comment #13

JoeIsuzu commented 19 August 2006 at 19:17

I'd be willing to test the patch, too. Where can I get it? I don't currently use CVS, so if that's required, I'll have to set it up first.

Thanks.
Jack

Comment #14

beginner commented 20 August 2006 at 05:46

the pre-patched module is attached above.

Comment #15

JoeIsuzu commented 20 August 2006 at 19:39

DOH! I'm still getting used to this forum layout, I missed the attachment.

I put this into the "modules" directory, and then attempted to configure it. This got rid of most of the error messages. All of the remaining messages pertain to the "Import Private Messages?" section. It appears that if I uncheck this, these error messages will go away. But I had hoped to import the private messages, as well as the rest of the forum content and membership data. If I don't import them now, it may be difficult to do later (?).

Below is the error text from the top of the screen:

    * warning: pg_query(): Query failed: ERROR: syntax error at or near "LIKE" at character 13 in /var/www/isuzupup.com/includes/database.pgsql.inc on line 84.
    * user warning: ERROR: syntax error at or near "LIKE" at character 13 query: SHOW TABLES LIKE "phpbb_privmsgs" in /var/www/isuzupup.com/includes/database.pgsql.inc on line 103.
    * warning: pg_query(): Query failed: ERROR: syntax error at or near "LIKE" at character 13 in /var/www/isuzupup.com/includes/database.pgsql.inc on line 84.
    * user warning: ERROR: syntax error at or near "LIKE" at character 13 query: SHOW TABLES LIKE "phpbb_privmsgs_text" in /var/www/isuzupup.com/includes/database.pgsql.inc on line 103.
    * warning: pg_query(): Query failed: ERROR: syntax error at or near "LIKE" at character 13 in /var/www/isuzupup.com/includes/database.pgsql.inc on line 84.
    * user warning: query: SHOW TABLES LIKE "privatemsg" in /var/www/isuzupup.com/includes/database.pgsql.inc on line 103.
    * warning: pg_query(): Query failed: ERROR: syntax error at or near "LIKE" at character 13 in /var/www/isuzupup.com/includes/database.pgsql.inc on line 84.
    * user warning: query: SHOW TABLES LIKE "privatemsg_folder" in /var/www/isuzupup.com/includes/database.pgsql.inc on line 103.
    * warning: pg_query(): Query failed: ERROR: syntax error at or near "LIKE" at character 13 in /var/www/isuzupup.com/includes/database.pgsql.inc on line 84.
    * user warning: query: SHOW TABLES LIKE "privatemsg_archive" in /var/www/isuzupup.com/includes/database.pgsql.inc on line 103.

Also, from the "Import Private Messages?" section:

    * Module privatemsg is enabled. OK!

    * Table phpbb_privmsgs does not exist!
    * Table phpbb_privmsgs_text does not exist!

Couldn't find the phpBB privatemsg tables.

    * Table privatemsg does not exist!
    * Table privatemsg_folder does not exist!
    * Table privatemsg_archive does not exist!

Some tables couldn't be found. Please make sure you have properly installed the privatemsg.mysql on your Drupal data base.

Thanks for the assistance so far. I'm closer...

Jack

Comment #16

havran commented 20 August 2006 at 21:08

For beginner: i recomended change this

    if (substr($db_url['default'], 0, 5) == 'pgsql') {
      $query = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE '%s'";
    }
    else {
      $query = 'SHOW TABLES LIKE "%s"';
    }

for

    if ($GLOBALS['db_type'] == 'pgsql') {
      $query = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE '%s'";
    }
    else {
      $query = 'SHOW TABLES LIKE "%s"';
    }

This is way used in .install files.

For JoeIsuzu: maybe you can try find this and replace in your installed module. Good luck!

Comment #17

JoeIsuzu commented 21 August 2006 at 01:05

That got rid of all the error messages. I'm now getting the good kind of message:
"Check again all the settings above, and if everything is ok, you can now proceed with the migration"

I haven't yet begun the actual migration, I'm waiting for some dead time on the phpbb2 forum.

Jack

Comment #18

JoeIsuzu commented 21 August 2006 at 15:10

I tried the migration. It did not go smoothly, but I'd still call it "progress". I got a lot of errors during the migration, but I'll do some research and only post the ones that stump me.

The memberlist seemed to migrate okay.
Forum posts migrated to somewhere, but they're not showing up in the forums. They DO show up in the "New Forum Topics" block, and I can read them from there. I saw them somewhere immediately after the migration, but can't find them now. All of them (except mine) showed "anonymous" as the author (yes, I imported the memberlist BEFORE the forums).

I really like Drupal, so if I can get my phpBB2 migrated, and my Gallery integrated, I'm happy. phpBB2Drupal is obviously a "work in progress" but I knew that going in. I appreciate the kind help so far!

Thanks.
Jack

Comment #19

beginner commented 21 August 2006 at 17:46

Status:

Needs review

» Fixed

Thanks to havran for your help on this issue. You saved me a lot of time :)

I committed the changes, so this issue is now fixed.

Jack, if you still have problems (it seems you have! none of what you describe is supposed to happen), please open another issue, one per specific problem, and give me as much details as you can. It may be that some other queries do not work well with postgresql.
http://drupal.org/node/add/project_issue/phpbb2drupal

Comment #20

JoeIsuzu commented 21 August 2006 at 18:28

Thanks, I'll do that. It looks like I'm still a long way off from having it working.

Jack

Comment #21

(not verified) commented 4 September 2006 at 18:30

Status:

Fixed

» Closed (fixed)

Comment #22

hugeknot commented 20 June 2008 at 14:31

Hello
it is 2 years later and I have had exactly the same issue while trying to migrate my phpbb2 board, as far as I know, both databases are mysql.

postgreSQL: pb with SHOW TABLES syntax.

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

Comment #12

Comment #13

Comment #14

Comment #15

Comment #16

Comment #17

Comment #18

Comment #19

Comment #20

Comment #21

Comment #22

News items

Our community

Documentation

Drupal code base

Governance of community