Errors on creating tables and adding/dropping columns needed by the merge system on PostgreSQL and SQLite databases

[fdutheil]

Hi, I tried to migrate a small (~40k posts) FluxBB 1.5 forum (PG 9.5 DB) to MyBB (PG 9.5 DB) with merge-system, and it failed at first step (right after it successfully connected with the FluxBB PG DB) with this error message:

MyBB SQL Error
MyBB has experienced an internal SQL error and cannot continue

SQL Error: 0 - Error: syntax error at end of line 1: ALTER TABLE mybb_users ^
Query: ALTER TABLE mybb_users

My understanding is that MyBB now supports PostgreSQL quite fine as an operationnal DB backend (and I tested it quickly to verify this), but merge system fails when PostgreSQL is the destination of the merge operation. I found these references in the MyBB forum, as it seems a long standing issue:

• https://community.mybb.com/thread-215650.html
• https://community.mybb.com/thread-134529.html
• https://community.mybb.com/thread-80056.html
• https://community.mybb.com/thread-107366.html

I validated that trying MySQL 5.6 instead of PostgreSQL 9.5 as destination DB allows the merge software to process till the end (with other issues, like borked users passwords, but nothing unrecoverable). As a PostgreSQL setup is really preferred here (backup&restore operations, ansible playbooks and so on) to MySQL (nothing automated), I'm willing to help troubleshoot this issue.

[rvbiljouw]

Hi @fdutheil,

Did you ever get anywhere with this? I'm experiencing the same issue.

[euantorano]

@rvbiljouw Are you using the current version of the merge system from Git (e.g. from here)?

Which system are you trying to merge from?

I somehow managed to miss this issue in the past, but would be interested in getting it fixed - as would @kawaii I imagine 😄

[rvbiljouw]

hey @euantorano, using the version provided on the website. will try the latest git version now and see if the same occurs..

[rvbiljouw]

@euantorano Can confirm it still occurs. The situation is as follows: I have a vB5 install (using MySQL, as you'd expect) that I want to migrate over to MyBB, which is installed with Postgres.

The migration code appears to assume that when migrating from MySQL, the target is also MySQL. This opens up some strange situations such as:

• ALTER TABLE with no columns fails on Postgres, but presumably doesn't on MySQL (causing the above error)
• Script attempts to create a mybb_trackers table with MyISAM as the engine

Undoubtedly there's more, and I'm getting past the hurdles one by one to find more errors.

[euantorano]

Interesting, thanks for following up. I'll have to do some testing and see what we can do to iron things out. Some of them should be easy enough to fix, some may not.

I would imagine the same will happen when merging from other platforms too (and likely when merging to other DB engines too such as SQLite).

[yuliu]

An incomplete list for fixing the db engine compatibility issue:

• alter table may need to check whether there's any column to alter (errors might be more common in converted forums or forums during converting): https://github.com/mybb/merge-system/blob/d453ec2b922a088cfa41a81753c37b7f038ecb77/resources/functions.php#L237 https://github.com/mybb/merge-system/blob/d453ec2b922a088cfa41a81753c37b7f038ecb77/resources/functions.php#L317 https://github.com/mybb/merge-system/blob/d453ec2b922a088cfa41a81753c37b7f038ecb77/resources/functions.php#L342
• create table needs to be compatible with pqsql and sqlite: https://github.com/mybb/merge-system/blob/d453ec2b922a088cfa41a81753c37b7f038ecb77/resources/functions.php#L267-L272

As a note, the debuglogs table creation seems compatible with different db engine types: https://github.com/mybb/merge-system/blob/d453ec2b922a088cfa41a81753c37b7f038ecb77/resources/class_debug.php#L180-L211

@euantorano I think you may change the issue title to a more specific one.

[euantorano]

@yuliu Yes, would probably make sense to change the title. Will do so now.

[fdutheil]

Hi @fdutheil,

Did you ever get anywhere with this? I'm experiencing the same issue.

Sorry for the late reply, but as you already guessed, I couldn't get past this (I had to migrate to MySQL and rewrite all my playbooks). I'm happy to see the merge system gets some love again, and wish you all success :)

[yuliu]

Here's a possibly incomplete list of catches we need to take care of:

• [ ] (PostgreSQL) ALTER TABLE .. DROP .. in delete_import_fields() may be queried without any column specified, PostgreSQL doesn't support this syntax.

• [ ] (PostgreSQL) ALTER TABLE .. ADD .. and ALTER TABLE .. ADD .. in create_import_fields() may be queried without any column specified, PostgreSQL doesn't support this syntax.

• [ ] (PostgreSQL) CREATE TABLE trackers in create_import_fields() doesn't work for PostgreSQL because of the KEY and ENGINE syntax.

• [ ] (SQLite) ALTER TABLE .. DROP .. in delete_import_fields() may be queried with several columns specified, SQLite doesn't support this syntax. SQLite's ALTER TABLE ADD/DROP COLUMN works with one column at one time.

• [ ] (SQLite) ALTER TABLE .. ADD .. and ALTER TABLE .. ADD .. in create_import_fields() may be queried with several columns specified, SQLite doesn't support this syntax. SQLite's ALTER TABLE ADD/DROP COLUMN works with one column at one time.

• [ ] (SQLite) CREATE TABLE trackers in create_import_fields() doesn't work for SQLite because of the KEY and ENGINE syntax.