Where can I find a list of tables that SMF installs?

[mjschiff]

I have install on a Raspberry Pi the following:

apache2  2.4.25 (Raspbian)
php 7.0.30-0+deb9u1
mysql/MariaDB 10.1.23 ...
SMF 2.0.15

When I pointed my browser to SMF, I got a text message about table smf_smf.messages missing.  I have searched and I am unable to find any information about that table.  Is there a SMF document that shows table list to be installed and the table's structure?  Is there a location in SMF to get this missing table?

Help

[Arantor]

The real question: what database and tables do you actually have? Take a look at what is in there - because I have a feeling that messages is not necessarily the only thing missing - and I have feeling it is there under another name.

[vbgamer45]

Also it's found in .sql file for mysql in smf directory for new isntalls.

[shawnb61]

As vbgamer45 says, look at the .sql file for the install for the tables.

At first glance, I would focus on the period in the table prefix.  I don't think that will work ("smf_smf.").  I would reinstall with a simpler prefix.

[Aleksi "Lex" Kilpinen]

It does look odd, unless that is just an error on the OP's part and actually should say smf.smf_messages then I might understand that error message.

[aegersz]

phpmyadmin is a cool tool to get the lowdown on SMF tables

[Kindred]

Aegersz...   he is saying that he knows that tables are missing. So, phpmyadmin won't help.

[Arantor]

Actually, it will help - you'll notice I asked for a list of what tables are actually there - because I want to see what prfixes are relevant etc.

[mjschiff]

Wow!  Thank you all for your responses.

I have installed smf 2-0-15 at least three times and I now am working on a fresh install of MySQL, php, Apache2, smf_2-0-15 and Raspbian Stretch.
I still get the following:


hxxp:localhost/forum/index.php [nonactive]

Table 'smf.smf_messages' doesn't exist

Shown below is the tables from the install:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use smf
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [smf]> show tables
    -> ;
+---------------------------+
| Tables_in_smf             |
+---------------------------+
| smf_admin_info_files      |
| smf_approval_queue        |
| smf_attachments           |
| smf_ban_groups            |
| smf_ban_items             |
| smf_board_permissions     |
| smf_boards                |
| smf_calendar              |
| smf_calendar_holidays     |
| smf_categories            |
| smf_collapsed_categories  |
| smf_custom_fields         |
| smf_group_moderators      |
| smf_log_actions           |
| smf_log_activity          |
| smf_log_banned            |
| smf_log_boards            |
| smf_log_comments          |
| smf_log_digest            |
| smf_log_errors            |
| smf_log_floodcontrol      |
| smf_log_group_requests    |
| smf_log_karma             |
| smf_log_mark_read         |
| smf_log_member_notices    |
| smf_log_notify            |
| smf_log_online            |
| smf_log_packages          |
| smf_log_polls             |
| smf_log_reported          |
| smf_log_reported_comments |
| smf_log_scheduled_tasks   |
| smf_log_search_messages   |
| smf_log_search_results    |
| smf_log_search_subjects   |
| smf_log_search_topics     |
| smf_log_spider_hits       |
| smf_log_spider_stats      |
| smf_log_subscribed        |
| smf_log_topics            |
| smf_mail_queue            |
| smf_membergroups          |
| smf_members               |
| smf_message_icons         |
| smf_moderators            |
| smf_openid_assoc          |
| smf_package_servers       |
| smf_permission_profiles   |
| smf_permissions           |
| smf_personal_messages     |
| smf_pm_recipients         |
| smf_pm_rules              |
| smf_poll_choices          |
| smf_polls                 |
| smf_scheduled_tasks       |
| smf_sessions              |
| smf_settings              |
| smf_smileys               |
| smf_spiders               |
| smf_subscriptions         |
| smf_themes                |
| smf_topics                |
+---------------------------+
62 rows in set (0.00 sec)

MariaDB [smf]>

I agree, the period in the error message is of concern.  But not seeing a table with the name:  smf_messages is of more concern. 

[Illori]

MariaDB is not fully supported, it is possible that something in the install for that table is not compatible with MariaDB.

can you run this and post any output?

Code Select Expand

CREATE TABLE smf_messages (
  id_msg int(10) unsigned NOT NULL auto_increment,
  id_topic mediumint(8) unsigned NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  poster_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_msg_modified int(10) unsigned NOT NULL default '0',
  subject varchar(255) NOT NULL default '',
  poster_name varchar(255) NOT NULL default '',
  poster_email varchar(255) NOT NULL default '',
  poster_ip varchar(255) NOT NULL default '',
  smileys_enabled tinyint(4) NOT NULL default '1',
  modified_time int(10) unsigned NOT NULL default '0',
  modified_name varchar(255) NOT NULL default '',
  body text NOT NULL,
  icon varchar(16) NOT NULL default 'xx',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_msg),
  UNIQUE topic (id_topic, id_msg),
  UNIQUE id_board (id_board, id_msg),
  UNIQUE id_member (id_member, id_msg),
  KEY approved (approved),
  KEY ip_index (poster_ip(15), id_topic),
  KEY participation (id_member, id_topic),
  KEY show_posts (id_member, id_board),
  KEY id_topic (id_topic),
  KEY id_member_msg (id_member, approved, id_msg),
  KEY current_topic (id_topic, id_msg, id_member, approved),
  KEY related_ip (id_member, poster_ip, id_msg)
) ENGINE=MyISAM;

INSERT INTO smf_messages
(id_msg, id_msg_modified, id_topic, id_board, poster_time, subject, poster_name, poster_email, poster_ip, modified_name, body, icon)
VALUES (1, 1, 1, 1, UNIX_TIMESTAMP(), 'new subject', 'Simple Machines', '[email protected]', '127.0.0.1', '', 'new message body', 'xx');


but this is not the only table you are missing, unless it is not showing all tables.

[SpacePhoenix]

Is it possible that SMF is looking for a MySQL server for the database, when it appears that the database has been installed onto a MariaDB server (possibly something about the smf_messages table isn't liked by MariaDB)?

[shawnb61]

I suspect the period in the first post was just a typo (smf_smf.messages).  The later version looks better (smf.smf_messages).

Looks like all 63 initial install tables are there except messages.  (EDIT: some other tables are added later, depending on options selected, e.g., log_search_words.)

I'd hunt down the MariaDB error log or query log.  There is an error encountered somewhere we're not seeing...

[mjschiff]

Hello Illori;

Here is the result of your requested sql code to try:

root@raspberrypi:/var/www/html/forum# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use smf
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [smf]> CREATE TABLE smf_messages (
    ->   id_msg int(10) unsigned NOT NULL auto_increment,
    ->   id_topic mediumint( unsigned NOT NULL default '0',
    ->   id_board smallint(5) unsigned NOT NULL default '0',
    ->   poster_time int(10) unsigned NOT NULL default '0',
    ->   id_member mediumint( unsigned NOT NULL default '0',
    ->   id_msg_modified int(10) unsigned NOT NULL default '0',
    ->   subject varchar(255) NOT NULL default '',
    ->   poster_name varchar(255) NOT NULL default '',
    ->   poster_email varchar(255) NOT NULL default '',
    ->   poster_ip varchar(255) NOT NULL default '',
    ->   smileys_enabled tinyint(4) NOT NULL default '1',
    ->   modified_time int(10) unsigned NOT NULL default '0',
    ->   modified_name varchar(255) NOT NULL default '',
    ->   body text NOT NULL,
    ->   icon varchar(16) NOT NULL default 'xx',
    ->   approved tinyint(3) NOT NULL default '1',
    ->   PRIMARY KEY (id_msg),
    ->   UNIQUE topic (id_topic, id_msg),
    ->   UNIQUE id_board (id_board, id_msg),
    ->   UNIQUE id_member (id_member, id_msg),
    ->   KEY approved (approved),
    ->   KEY ip_index (poster_ip(15), id_topic),
    ->   KEY participation (id_member, id_topic),
    ->   KEY show_posts (id_member, id_board),
    ->   KEY id_topic (id_topic),
    ->   KEY id_member_msg (id_member, approved, id_msg),
    ->   KEY current_topic (id_topic, id_msg, id_member, approved),
    ->   KEY related_ip (id_member, poster_ip, id_msg)
    -> ) ENGINE=MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
MariaDB [smf]>
MariaDB [smf]> INSERT INTO smf_messages
    -> (id_msg, id_msg_modified, id_topic, id_board, poster_time, subject, poster_name, poster_email, poster_ip, modified_name, body, icon)
    -> VALUES (1, 1, 1, 1, UNIX_TIMESTAMP(), 'new subject', 'Simple Machines', '[email protected]', '127.0.0.1', '', 'new message body', 'xx');
ERROR 1146 (42S02): Table 'smf.smf_messages' doesn't exist
MariaDB [smf]>

[Arantor]

Try this

Code Select Expand

CREATE TABLE smf_messages (
  id_msg int(10) unsigned NOT NULL auto_increment,
  id_topic mediumint(8) unsigned NOT NULL default '0',
  id_board smallint(5) unsigned NOT NULL default '0',
  poster_time int(10) unsigned NOT NULL default '0',
  id_member mediumint(8) unsigned NOT NULL default '0',
  id_msg_modified int(10) unsigned NOT NULL default '0',
  subject varchar(255) NOT NULL default '',
  poster_name varchar(255) NOT NULL default '',
  poster_email varchar(255) NOT NULL default '',
  poster_ip varchar(255) NOT NULL default '',
  smileys_enabled tinyint(4) NOT NULL default '1',
  modified_time int(10) unsigned NOT NULL default '0',
  modified_name varchar(255) NOT NULL default '',
  body text NOT NULL,
  icon varchar(16) NOT NULL default 'xx',
  approved tinyint(3) NOT NULL default '1',
  PRIMARY KEY (id_msg),
  UNIQUE topic (id_topic, id_msg),
  UNIQUE id_board (id_board, id_msg),
  UNIQUE id_member (id_member, id_msg),
  KEY approved (approved),
  KEY ip_index (poster_ip(15), id_topic),
  KEY participation (id_member, id_topic),
  KEY show_posts (id_member, id_board),
  KEY id_topic (id_topic),
  KEY id_member_msg (id_member, approved, id_msg),
  KEY current_topic (id_topic, id_msg, id_member, approved),
  KEY related_ip (id_member, poster_ip(15), id_msg)
) ENGINE=MyISAM;

INSERT INTO smf_messages
(id_msg, id_msg_modified, id_topic, id_board, poster_time, subject, poster_name, poster_email, poster_ip, modified_name, body, icon)
VALUES (1, 1, 1, 1, UNIX_TIMESTAMP(), 'new subject', 'Simple Machines', '[email protected]', '127.0.0.1', '', 'new message body', 'xx');


I rather suspect other tables will have a similar problem where this is operating in what looks like utf8mb4 mode without SMF explicitly declaring so...

[shawnb61]

I'm confused.  I thought the old db was mb4, & the new smf one was utf8.

The message (specified key too long) would make sense if he was trying to create the new smf db in mb4...

[Arantor]

The query provided did not specify which collation, therefore it would use whatever the system default is, which is likely mb4.

[mjschiff]

Arantor;

That worked, thank you.  I will redo the install using utf8mb4.

Mark

[Arantor]

If you install with utf8mb4, the installer will fail because of overlong indexes like the one given above. There are definitely a few others but I'm not sure how many of them are in 2.0 and how many are in 2.1 (since I know I just fixed this recently)

You might actually be better off installing with utf8 NOT mb4 mode (also known as utf8mb3) which doesn't have this issue, and which SMF knows how to deal with - it doesn't know about mb4 properly until 2.1. (The problem of storing emoji etc in 2.0 was solved a different way.)