Unable to create a new group

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Thu Dec 14, 2017 9:31 pm

richxrich wrote:
Wed Dec 13, 2017 9:06 pm

I just tried running the SQL commands, but, unfortunately, ran into this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
Never mind. This is a user lack-of-knowledge error... I was trying to run each command line separately without removing UNION before its execution.

The executed result is below.

Code: Select all

ALTER TABLE phpbb_acl_options MODIFY auth_option_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 119;
ALTER TABLE phpbb_acl_roles MODIFY role_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 25;
ALTER TABLE phpbb_attachments MODIFY attach_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 7683;
ALTER TABLE phpbb_banlist MODIFY ban_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 21;
ALTER TABLE phpbb_bots MODIFY bot_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 51;
ALTER TABLE phpbb_disallow MODIFY disallow_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 5;
ALTER TABLE phpbb_drafts MODIFY draft_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 224;
ALTER TABLE phpbb_extensions MODIFY extension_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 29;
ALTER TABLE phpbb_extension_groups MODIFY group_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 8;
ALTER TABLE phpbb_forums MODIFY forum_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 38;
ALTER TABLE phpbb_groups MODIFY group_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 1458;
ALTER TABLE phpbb_icons MODIFY icons_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 11;
ALTER TABLE phpbb_lang MODIFY lang_id tinyint(4) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 3;
ALTER TABLE phpbb_log MODIFY log_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 12697;
ALTER TABLE phpbb_modules MODIFY module_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 200;
ALTER TABLE phpbb_posts MODIFY post_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 164852;
ALTER TABLE phpbb_privmsgs MODIFY msg_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 139294;
ALTER TABLE phpbb_privmsgs_folder MODIFY folder_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 981;
ALTER TABLE phpbb_privmsgs_rules MODIFY rule_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 1;
ALTER TABLE phpbb_profile_fields MODIFY field_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 4;
ALTER TABLE phpbb_ranks MODIFY rank_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 20;
ALTER TABLE phpbb_reports MODIFY report_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 5;
ALTER TABLE phpbb_reports_reasons MODIFY reason_id smallint(4) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 5;
ALTER TABLE phpbb_search_wordlist MODIFY word_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 107125;
ALTER TABLE phpbb_sitelist MODIFY site_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 1;
ALTER TABLE phpbb_smilies MODIFY smiley_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 46;
ALTER TABLE phpbb_styles MODIFY style_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 8;
ALTER TABLE phpbb_topics MODIFY topic_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 16830;
ALTER TABLE phpbb_users MODIFY user_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 3048;
ALTER TABLE phpbb_warnings MODIFY warning_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 1;
ALTER TABLE phpbb_words MODIFY word_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 20;
However, I ran the SHOW CREATE TABLE command again and it doesn't look like the AUTO_INCREMENT was added to any of the tables.



There was an alert that I noticed in the middle of the page, too: "This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available." I'm not clear which table it refers to as it's not explicitly stated. Screenshot attached.
Screen Shot 2017-12-14 at 1.28.05 PM.png

User avatar
AmigoJack
Registered User
Posts: 5241
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Unable to create a new group

Post by AmigoJack » Fri Dec 15, 2017 7:59 am

richxrich wrote:
Thu Dec 14, 2017 9:31 pm
The executed result is below.
Looks fine. Now execute each line of that separately.
The worst thing about censorship is ███████████

richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Fri Dec 15, 2017 8:07 pm

I don't know if I'm doing this correctly. I'm executing the commands separately as you suggested, but I'm not seeing changes in the tables. It looks identical, if not exactly the same, as when I started. Am I missing something?

Screen Shot 2017-12-15 at 12.00.38 PM.png
Screen Shot 2017-12-15 at 12.01.27 PM.png

User avatar
warmweer
Registered User
Posts: 974
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Unable to create a new group

Post by warmweer » Fri Dec 15, 2017 9:41 pm

Your tab is still the SQL, and not the result.
Check the tab STRUCTURE (immediately on the left).
A bug is a feature that hasn't made it to the manual (yet)

richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Sat Dec 16, 2017 8:36 am

There are no visible changes there either.
Screen Shot 2017-12-16 at 12.35.06 AM.png

User avatar
warmweer
Registered User
Posts: 974
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Unable to create a new group

Post by warmweer » Sat Dec 16, 2017 10:59 am

Something I noticed in one of the previous post, the calculated autoincrement value was 119, which corresponds with the number or rows (118) in your table (acl_options).
I just checked a freshly installed 3.2.1 board (for testing purposes) and I have 123 rows. (totally new board, unmodified).

I can't think of a reason why rows would be removed from that table so my first action (at this stage withnthis new realisation) would be to find out what has been removed (and why). And also check the other tables.

It's almost lunchtime here and some chores are waiting but I'll try to make a list of all tables with their number of rows.
In the meantime, do you have quick access to an older backup in order to compare the number of rows in that old backup to the number of rows in the current (broken) board?

added a bit later: you board was upgraded from 3.0.12. On my very first upgrade (also from 3.0.12) I had a couple of errors and the result was incompletely filled tables (which ones I can't remember). My question now is: did the upgrade run without errors (and dit you remove all database modifications before upgrading)? And the next question : would redoing the upgrade make you lose a lot? If not I would seriously contemplate re-upgrading (even just for testing purposes). (re-upgrading from 3.0.12 after checking that the tables weren't modified).
A bug is a feature that hasn't made it to the manual (yet)

User avatar
AmigoJack
Registered User
Posts: 5241
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Unable to create a new group

Post by AmigoJack » Mon Dec 18, 2017 8:01 am

richxrich wrote:
Fri Dec 15, 2017 8:07 pm
I'm executing the commands separately as you suggested
From the screenshots you made I guess you wrongly separate the first query into multiple ones (that with the UNION) - that would be wrong. The output of that first query gives you 31 separate queries and you should execute them separately. Which means, executing i.e.

Code: Select all

ALTER TABLE phpbb_acl_options MODIFY auth_option_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, AUTO_INCREMENT= 119;

warmweer wrote:
Sat Dec 16, 2017 10:59 am
I can't think of a reason why rows would be removed from that table
Most likely for the same unknown reason why all tables lost their AUTO_INCREMENT.

warmweer wrote:
Sat Dec 16, 2017 10:59 am
I'll try to make a list of all tables with their number of rows
/install/schemas/schema_data.sql has already the answer, but as said before: one step at a time.
The worst thing about censorship is ███████████

richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Mon Dec 18, 2017 8:47 am

LOL... in hindsight, that makes so much sense! I'm executing the wrong code.

The ALTER TABLE sql was what I needed to execute after the SELECT set of statements, which were to determine where the next sequence in AUTO_INCREMENT would start.

Just executed the first of 31 ALTER TABLE and it worked perfectly. I feel like such an idiot right now. :oops: :lol:

I appreciate your continued patience!

richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Mon Dec 18, 2017 9:07 am

Alrighty, all ALTER TABLE statements have been executed. I've run the SHOW CREATE TABLE phpbb_xxx and all tables now show AUTO_INCREMENT in the columns. Did you want me to post the results?

User avatar
AmigoJack
Registered User
Posts: 5241
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Unable to create a new group

Post by AmigoJack » Mon Dec 18, 2017 9:21 am

No. Now the next thing:
richxrich wrote:
Tue Dec 12, 2017 9:15 pm
phpbb_notification_types, phpbb_notifications, and phpbb_teampage were not found among the tables in our db
Create the tables:
  1. Code: Select all

    CREATE TABLE phpbb_notification_types
    ( notification_type_id smallint(4) unsigned NOT NULL AUTO_INCREMENT
    , notification_type_name varchar(255) COLLATE utf8_bin NOT NULL DEFAULT ''
    , notification_type_enabled tinyint(1) unsigned NOT NULL DEFAULT '1'
    , PRIMARY KEY(notification_type_id)
    , UNIQUE KEY type(notification_type_name)
    ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  2. Code: Select all

    CREATE TABLE phpbb_notifications
    ( notification_id int(10) unsigned NOT NULL AUTO_INCREMENT
    , notification_type_id smallint(4) unsigned NOT NULL DEFAULT '0'
    , item_id mediumint(8) unsigned NOT NULL DEFAULT '0'
    , item_parent_id mediumint(8) unsigned NOT NULL DEFAULT '0'
    , user_id int(10) unsigned NOT NULL DEFAULT '0'
    , notification_read tinyint(1) unsigned NOT NULL DEFAULT '0'
    , notification_time int(11) unsigned NOT NULL DEFAULT '1'
    , notification_data text COLLATE utf8_bin NOT NULL
    , PRIMARY KEY(notification_id)
    , KEY item_ident(notification_type_id, item_id)
    , KEY user(user_id, notification_read)
    ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  3. Code: Select all

    CREATE TABLE phpbb_teampage
    ( teampage_id mediumint(8) unsigned NOT NULL AUTO_INCREMENT
    , group_id mediumint(8) unsigned NOT NULL DEFAULT '0'
    , teampage_name varchar(255) COLLATE utf8_bin NOT NULL DEFAULT ''
    , teampage_position mediumint(8) unsigned NOT NULL DEFAULT '0'
    , teampage_parent mediumint(8) unsigned NOT NULL DEFAULT '0'
    , PRIMARY KEY(teampage_id)
    ) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Make sure they were created.

Fill the tables:
  1. Code: Select all

    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.topic', 1 );
    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.approve_topic', 1 );
    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.quote', 1 );
    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.bookmark', 1 );
    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.post', 1 );
    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.approve_post', 1 );
    INSERT INTO phpbb_notification_types( notification_type_name, notification_type_enabled ) VALUES( 'notification.type.group_request', 1 );
  2. Code: Select all

    INSERT INTO phpbb_teampage(group_id, teampage_name, teampage_position, teampage_parent) VALUES(5, '', 1, 0);
    INSERT INTO phpbb_teampage(group_id, teampage_name, teampage_position, teampage_parent) VALUES(4, '', 2, 0);
Make sure both tables now have these rows.


If that succeeded we'll find out which rows are missing in your other tables.
The worst thing about censorship is ███████████

richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Mon Dec 18, 2017 9:51 am

Done. Tables created and values inserted.

User avatar
AmigoJack
Registered User
Posts: 5241
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Unable to create a new group

Post by AmigoJack » Mon Dec 18, 2017 10:05 am

Look up for each of these tables how many rows it has - if the value is different from the one listed here export the full table:
  1. phpbb_acl_options = 123 rows
  2. phpbb_acl_roles = 24 rows
  3. phpbb_bots = 45 rows
  4. phpbb_extension_groups = 6 rows
  5. phpbb_extensions = 55 rows
  6. phpbb_groups = 7 rows
  7. phpbb_modules = 207 rows
  8. phpbb_profile_fields = 12 rows
  9. phpbb_reports_reasons = 4 rows
Either combine all your table exports into one text file or put separate files into one archive (i.e. ZIP or RAR or 7Z) and attach that to your reply. As warmweer pointed out: at least the first table's content should differ.
The worst thing about censorship is ███████████

User avatar
warmweer
Registered User
Posts: 974
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Unable to create a new group

Post by warmweer » Mon Dec 18, 2017 3:47 pm

AmigoJack wrote:
Mon Dec 18, 2017 8:01 am
/install/schemas/schema_data.sql has already the answer, but as said before: one step at a time.
Ah thanks, I didn't even know that (well, let's say it's one of those many things I didn't know - and the list isn't exhausted yet).
A bug is a feature that hasn't made it to the manual (yet)

richxrich
Registered User
Posts: 47
Joined: Mon Nov 21, 2016 9:17 am

Re: Unable to create a new group

Post by richxrich » Mon Dec 18, 2017 8:01 pm

AmigoJack wrote:
Mon Dec 18, 2017 10:05 am
Look up for each of these tables how many rows it has - if the value is different from the one listed here export the full table:
  1. phpbb_acl_options = 123 rows
  2. phpbb_acl_roles = 24 rows
  3. phpbb_bots = 45 rows
  4. phpbb_extension_groups = 6 rows
  5. phpbb_extensions = 55 rows
  6. phpbb_groups = 7 rows
  7. phpbb_modules = 207 rows
  8. phpbb_profile_fields = 12 rows
  9. phpbb_reports_reasons = 4 rows
Either combine all your table exports into one text file or put separate files into one archive (i.e. ZIP or RAR or 7Z) and attach that to your reply. As warmweer pointed out: at least the first table's content should differ.
Table rows with differences:
  • phpbb_acl_options = 118 rows
  • phpbb_bots = 50 rows
  • phpbb_extension_groups = 7 rows
  • phpbb_extensions = 28 rows
  • phpbb_groups = 17 rows
  • phpbb_modules = 199 rows
  • phpbb_profile_fields = 2 rows
Attachments
sfbaaps table rows.txt
(23.35 KiB) Downloaded 4 times

User avatar
warmweer
Registered User
Posts: 974
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Unable to create a new group

Post by warmweer » Mon Dec 18, 2017 9:17 pm

I've attached 4 csv files with the records for new phpBB 3.2.1 tables (the tables where your number of records is lower than expected)

phpbb_acl_options 118 vs 123
phpbb_extensions 7 v 55
phpbb_modules 199 vs 207
phpbb_profile_fields 2 vs 12

This doesn't imply that there is nothing wrong with the other tables, (but while I'm making the other tables, you could have a look at the differences - if in your tables there are no unknown additions and changes, only deletes, then the simplest thing to do could be to delete those tables and to give you a create and insert SQL for these tables. )
But, at this time, look only, no actions !! AmigoJack has a much better insight in these things than I have.

edit: have you been able to check whether the number of topics, posts, members in the 3.2.1 database is the same as in the originating board? If you've lost records there then I would really start thinking about re-upgrading (not trying to scare you or anything, but if you did lose records in those tables then I doubt they can be restored in a simple way, if at all).
Attachments
phpbb_acl_options.csv
(3.58 KiB) Downloaded 4 times
phpbb_extensions.csv
(879 Bytes) Downloaded 3 times
phpbb_modules.csv
(18.66 KiB) Downloaded 5 times
phpbb_profile_fields.csv
(1.98 KiB) Downloaded 4 times
A bug is a feature that hasn't made it to the manual (yet)

Post Reply

Return to “[3.2.x] Support Forum”

Who is online

Users browsing this forum: Baidu [Spider], hoarybat and 15 guests