Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => Topic started by: Jelle Mees on April 26, 2008, 08:07:38 PM

Title: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 26, 2008, 08:07:38 PM
Every mod that requires a table in the database doesn't work simply because the mods can't create the tables in the database for some reason.

For example, if I install the bookmarks mod I get this:
QuoteTable 'Trackmania.smf1_bookmarks' doesn't exist
File: /mounted-storage/home30c/sub002/sc26341-IINL/www/FlemishArmySite/Trackmania/Sources/Bookmarks.php
Line: 115
Title: Re: Mods can't create tables in MYSQL database
Post by: Oldiesmann on April 26, 2008, 09:26:21 PM
Are there any errors in your forum error log related to table creation?
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 27, 2008, 03:41:05 AM
I have 5 database errors and they are all "doesn't exist" errors. No creation or connection errors.

The forum installation went fine, and every mod I install works fine, as long as it doesn't require a new table.

I get these "doesn't exist" errors on every page that requires the table.

I am using default theme and default language so It can't be any more compatible then that.
Title: Re: Mods can't create tables in MYSQL database
Post by: SwapsRulez on April 27, 2008, 06:29:06 AM
Quote from: Jelle Mees on April 27, 2008, 03:41:05 AM
I have 5 database errors and they are all "doesn't exist" errors. No creation or connection errors.

The forum installation went fine, and every mod I install works fine, as long as it doesn't require a new table.

I get these "doesn't exist" errors on every page that requires the table.

I am using default theme and default language so It can't be any more compatible then that.

I think try installing the MODS in the manual way & check out for the changes in the file. After completing the files editing part. Just go to phpmyadmin, there in your database. run the query given in the instructions of the MOD. You'll find it working in that way ;)
Title: Re: Mods can't create tables in MYSQL database
Post by: H on April 27, 2008, 08:16:11 AM
Does the SMF MySQL user have permissions to create new tables? You can check in cpanel or whichever control panel you have
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 28, 2008, 01:00:31 PM
@SwapsRules:
There is no query givin, almost nobody does that. What I do have is this:

<?php
global $smcFunc;

$smcFunc['db_create_table']('bookmarks',
array(
array(
'name' => 'id_member',
'type' => 'mediumint',
'size' => 8,
),
array(
'name' => 'id_topic',
'type' => 'mediumint',
'size' => 8,
),
),
array(
'name' => 'bookmark',
'type' => 'unique',
'columns' => array('id_member''id_topic'),
),
array(),
'ignore');
?>


My SQL knowledge is to low for me to rewrite as a SQL command.

@H:
Eeuhm, I don't know what you are talking about. I created mysql table and installed SMF, what are these permissions you talk about???
Title: Re: Mods can't create tables in MYSQL database
Post by: H on April 28, 2008, 01:02:40 PM
The bookmarks mod didn't fully install which is why you're getting these errors. Most likely the SMF user may not have permissions to create tables?
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 28, 2008, 01:05:55 PM
Quote from: H on April 28, 2008, 01:02:40 PM
The bookmarks mod didn't fully install which is why you're getting these errors. Most likely the SMF user may not have permissions to create tables?

I am the SMF user, the admin. There is only one account on my SMF forum and that's the admin account.
Title: Re: Mods can't create tables in MYSQL database
Post by: Dragooon on April 28, 2008, 01:18:54 PM
H meant MySQL User admin, not SMF permissions, MySQL users got there separate permissions for various things like creating, altering tables etc etc.

They should be in host control panel.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 29, 2008, 11:50:20 AM
Quote from: Dragooon on April 28, 2008, 01:18:54 PM
H meant MySQL User admin, not SMF permissions, MySQL users got there separate permissions for various things like creating, altering tables etc etc.

They should be in host control panel.

Again, don't really understand what you are talking about. I am admin and have FULL rights to everything. SMF, MYSQL, PHP settings, etc...
I can go to phpmyamin and create tables.

And I don't understand why you ask this.

Installing SMF = creating tables in MYSQL database, without that permission you can't eaven install SMF, or am I mistaking...

---------------------------------------------------

Maybe this is the problem.

Look at the code:

<?php
global $smcFunc;

$smcFunc['db_create_table']('bookmarks',


I see "Global", does that mean that I need to enable global variables in order for the installation to succeed?
Title: Re: Mods can't create tables in MYSQL database
Post by: H on April 29, 2008, 03:31:50 PM
global variables cannot be disabled (if they could then none of SMF would be working!).

Which bookmark mod are you using? You did install through the Package Manager (http://docs.simplemachines.org/index.php?board=49.0;sort=subject)?
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 29, 2008, 04:42:19 PM
Quote from: H on April 29, 2008, 03:31:50 PM
global variables cannot be disabled (if they could then none of SMF would be working!).

Which bookmark mod are you using? You did install through the Package Manager (http://docs.simplemachines.org/index.php?board=49.0;sort=subject)?

It doesn't mather wich mod it is, every single mod that requires new MYSQL tables does not work, simply because the tables are not getting created.

And yes, I use the package manager for all my mods.

PS: Thanks for not giving up on me :)
Title: Re: Mods can't create tables in MYSQL database
Post by: rsw686 on April 29, 2008, 08:57:16 PM
Check the mysql user permissions. Are you sure they didn't get changed after you installed SMF. Thats the only thing I can think of.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 30, 2008, 11:36:24 AM
Quote from: rsw686 on April 29, 2008, 08:57:16 PM
Check the mysql user permissions. Are you sure they didn't get changed after you installed SMF. Thats the only thing I can think of.

I am admin, 100000000% sure.

Have you guys installed mods that require new MYSQL tables? I don't mind that you assume that I am doing something rong, but have you guys actually tried it yourself?
Title: Re: Mods can't create tables in MYSQL database
Post by: rsw686 on April 30, 2008, 11:43:47 AM
Quote from: Jelle Mees on April 30, 2008, 11:36:24 AM
Have you guys installed mods that require new MYSQL tables? I don't mind that you assume that I am doing something rong, but have you guys actually tried it yourself?

I have, I just use a create table sql statement with the query function. If I have some time later I'll test out the create table function.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 30, 2008, 11:47:29 AM
Just know that I don't have this problem with phpbb.
Title: Re: Mods can't create tables in MYSQL database
Post by: karlbenson on April 30, 2008, 11:48:27 AM
Its unlikely that using create table will work for postgresql or sqlite.
At the moment very few mods that work for 2.x have needed to create a table.

This probably isn't stylised as much as I'd like. But I'm successfully using
Quote// Columns for our table
$table_columns = array(
   array('name' => 'id_member', 'type' => 'mediumint', 'size' => 8, 'null' => false, 'default' => 0),
   array('name' => 'id_topic', 'type' => 'mediumint', 'size' => 8, 'null' => false, 'default' => 0),
   array('name' => 'date_ignored', 'type' => 'int', 'size' => 10, 'null' => false, 'default' => 0)
);

// Indexes for our table
$table_indexes = array(
   array('name' => 'topic', 'columns' => array('id_member', 'id_topic'), 'type' => 'unique')
);

// Create the table
$smcFunc['db_create_table']('ignore_topics', $table_columns, $table_indexes);
On one of my unreleased mods.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 30, 2008, 11:52:13 AM
Can I trust someone with admin acces on my forum? Maybe if I give one of you guys admin acces on my forum + MYSQL connection data, you might be able to see what's happening.
Title: Re: Mods can't create tables in MYSQL database
Post by: H on April 30, 2008, 01:24:33 PM
A reminder about admin / ftp passwords... (http://www.simplemachines.org/community/index.php?topic=228940.0)

Generally anyone with a staff 'badge' or someone with the "<Insert team> Team Apprentice". Karl seems to have a few ideas up his sleeve :)
Title: Re: Mods can't create tables in MYSQL database
Post by: rsw686 on April 30, 2008, 01:35:17 PM
Okay this is slightly strange. I put together a short script using Karl's code and including the SSI file. The db_create_table is not a valid index in the array. I did a var_dump of smcFunc and it is not there. The other functions like db_query work as expected. Has anybody gotten this to work with SSI. I guess I could stick this in an SMF page somewhere to test. I just know that some people prefer to run the scripts separate from the package manager.

<?php
// If SSI.php is in the same place as this file, and SMF isn't defined, this is being run standalone.
if (file_exists(dirname(__FILE__) . '/SSI.php') && !defined('SMF'))
include_once(dirname(__FILE__) . '/SSI.php');
// Hmm... no SSI.php and no SMF?
elseif (!defined('SMF'))
die('<b>Error:</b> Cannot install - please verify you put this in the same place as SMF\'s index.php.');

// This works fine
$request $smcFunc['db_query']('''
SELECT ID_PROFILE FROM {db_prefix}profile'
);
$rows $smcFunc['db_num_rows']($request);
$smcFunc['db_free_result']($request);

// Columns for our table
$table_columns = array(
   array(
'name' => 'id_member''type' => 'mediumint''size' => 8'null' => false'default' => 0),
   array(
'name' => 'id_topic''type' => 'mediumint''size' => 8'null' => false'default' => 0),
   array(
'name' => 'date_ignored''type' => 'int''size' => 10'null' => false'default' => 0)
);

// Indexes for our table
$table_indexes = array(
   array(
'name' => 'topic''columns' => array('id_member''id_topic'), 'type' => 'unique')
);

// Look at the var dump
var_dump($smcFunc);

// This doesn't work
// Create the table
$smcFunc['db_create_table']('ignore_topics'$table_columns$table_indexes);
?>

Title: Re: Mods can't create tables in MYSQL database
Post by: karlbenson on April 30, 2008, 02:03:15 PM
RSW686, The install file that I'm using it in can be done via ssi (AND using sqlite).

I think it might need
At the top of your page (after the including ssi.php bit)

Add
// Make sure we have access to install packages
if(!array_key_exists('db_add_column', $smcFunc))
db_extend('packages');

// Globals
global $db_prefix;


Btw I've attached the exact install file that I'm using (and just tested on b3p and works)
Title: Re: Mods can't create tables in MYSQL database
Post by: rsw686 on April 30, 2008, 02:14:08 PM
Ah the db_extend('packages'); is what I needed to add. I was able to create a table without a problem.

You don't need the $db_prefix global anymore with SMF 2 functions. It parses out {db_prefix} itself and doesn't need {$db_prefix}.
Title: Re: Mods can't create tables in MYSQL database
Post by: karlbenson on April 30, 2008, 02:28:12 PM
Probably. ;)
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 30, 2008, 07:14:29 PM
Don't really understand all that much, but are you saying that there IS a problem with SMF table creations?
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on April 30, 2008, 08:20:00 PM
My mod can create tables in MySQL and SQLite (haven't tested Postgre yet). So I don't think the problem is with the functions.
Title: Re: Mods can't create tables in MYSQL database
Post by: karlbenson on April 30, 2008, 08:38:09 PM
Quote from: Jelle Mees on April 30, 2008, 07:14:29 PM
Don't really understand all that much, but are you saying that there IS a problem with SMF table creations?

Not in smf no.  Its just some mods, might not be creating them properly due to not knowing the full way to create them.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on April 30, 2008, 08:54:14 PM
Quote from: karlbenson on April 30, 2008, 08:38:09 PM
Quote from: Jelle Mees on April 30, 2008, 07:14:29 PM
Don't really understand all that much, but are you saying that there IS a problem with SMF table creations?

Not in smf no.  Its just some mods, might not be creating them properly due to not knowing the full way to create them.

Aah, ok. Do you have a SMF 2 mod that requires table? So that I can test if your does work.
Title: Re: Mods can't create tables in MYSQL database
Post by: rsw686 on April 30, 2008, 09:26:36 PM
Quote from: karlbenson on April 30, 2008, 02:03:15 PM
Btw I've attached the exact install file that I'm using (and just tested on b3p and works)

One thing about your attached file. You are setting the default value with

'default' => 0

Take a look at the table after creation and notice the default value is not set. You need to set it with

'default' => '0'

as the SMF function checks it against null

      elseif (isset($column['default']) && $column['default'] != null)
         $default = 'default \'' . $column['default'] . '\'';

Just pointing this out as I just ran into it converting the table creation over for my mod.
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 02, 2008, 09:58:31 AM
Quote from: Jelle Mees on April 30, 2008, 08:54:14 PMAah, ok. Do you have a SMF 2 mod that requires table? So that I can test if your does work.
Here's mine:

$smcFunc['db_create_table']('pretty_topic_urls', array(
array('name' => 'id_topic', 'type' => 'mediumint'),
array('name' => 'pretty_url', 'type' => 'varchar', 'size' => 80),
), array(
array('type' => 'primary', 'columns' => array('id_topic')),
array('type' => 'unique', 'columns' => array('pretty_url')),
));

$smcFunc['db_create_table']('pretty_urls_cache', array(
array('name' => 'url_id', 'type' => 'varchar', 'size' => 255),
array('name' => 'replacement', 'type' => 'varchar', 'size' => 255),
), array(
array('type' => 'primary', 'columns' => array('url_id')),
), array(), 'overwrite');


Or if you want the full mod: http://custom.simplemachines.org/mods/index.php?mod=636
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 02, 2008, 11:28:38 AM
Quote* Database modifications
          o Creating the pretty_topic_urls table
          o Fixing any old topics with broken quotes
          o Creating the pretty_urls_cache table
    * Adding the default filters
    * Adding some settings
    * Running maintenance tasks
          o Updating the array of actions
          o Fix old boards which have broken quotes
          o Update board URLs
          o Update the filters

The package was installed successfully. You should now be able to use whatever functionality it adds or changes; or not be able to use functionality it removes.

www.tm-belgium.info

As you can see, doesn't work on my forum. Wth is rong...  :'(
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 02, 2008, 11:30:51 AM
I am using MYSQL 5, can that be it?
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 02, 2008, 10:09:28 PM
Well that looks like it worked... again, nothing in the error log?

MySQL 5 should be fine.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 03, 2008, 04:02:35 AM
Quote from: Dannii on May 02, 2008, 10:09:28 PM
Well that looks like it worked... again, nothing in the error log?

MySQL 5 should be fine.

You say it looks like it worked, are you sure. Your mod works? I don't eaven know what you pretty url mod does with the url's.
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 03, 2008, 09:12:27 AM
Well if there had been a PHP error that page would not have displayed correctly like that. Can you check in phpMyAdmin to whether the tables exist or not?

What the mod does is rewrite the URLs. That's not important though for checking whether the tables installed...
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 03, 2008, 11:20:46 AM
Quote from: Dannii on May 03, 2008, 09:12:27 AM
Well if there had been a PHP error that page would not have displayed correctly like that. Can you check in phpMyAdmin to whether the tables exist or not?

What the mod does is rewrite the URLs. That's not important though for checking whether the tables installed...

Nope, no tables. It's so strange.
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 03, 2008, 10:56:50 PM
Can you make tables with phpMyAdmin using the same account your forum does?
Try this query:
CREATE TABLE IF NOT EXISTS smf_pretty_topic_urls (
`ID_TOPIC` mediumint(8) NOT NULL default '0',
`pretty_url` varchar(80) NOT NULL,
PRIMARY KEY (`ID_TOPIC`),
UNIQUE (`pretty_url`))
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 04, 2008, 03:56:18 AM
Quote from: Dannii on May 03, 2008, 10:56:50 PM
Can you make tables with phpMyAdmin using the same account your forum does?
Try this query:
CREATE TABLE IF NOT EXISTS smf_pretty_topic_urls (
`ID_TOPIC` mediumint(8) NOT NULL default '0',
`pretty_url` varchar(80) NOT NULL,
PRIMARY KEY (`ID_TOPIC`),
UNIQUE (`pretty_url`))


Yep, that works just fine. Table is there when I do this.
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 04, 2008, 04:50:53 AM
Just to confirm, you could do so using the MySQL account that SMF uses, and not a MySQL admin account?
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 04, 2008, 07:42:07 AM
Quote from: Dannii on May 04, 2008, 04:50:53 AM
Just to confirm, you could do so using the MySQL account that SMF uses, and not a MySQL admin account?

My host is servage.net, when I create a database, I can only create one user, and that user has full rights.
When I create servage mysql database I have:
- server
- databasename
- user
- password

That's it. So yeah, it's the same account I used for the SMF installation...
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 05, 2008, 12:27:39 PM
Can you turn on database debugging and then check the queries when you are installing a mod?
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 10, 2008, 11:28:35 PM
Are you still having problems?
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 11, 2008, 12:53:17 AM
Quote from: Dannii on May 10, 2008, 11:28:35 PM
Are you still having problems?

Yep, but I am doing just fine with all mods that don't require MYSQL tables...
I install every single mod that gets released :)
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 11, 2008, 12:57:05 AM
Oh dear... you must have too many of them by now... maybe that's the problem? :P

Did you try turning on debugging?
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 11, 2008, 12:58:52 AM
Quote from: Dannii on May 11, 2008, 12:57:05 AM
Oh dear... you must have too many of them by now... maybe that's the problem? :P

Did you try turning on debugging?

That can't be the problem because I already had the problem from the start.

Where can I turn on debugging?
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 11, 2008, 01:21:12 AM
Try adding $db_show_debug = true; to Settings.php
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 11, 2008, 05:30:16 PM
Quote from: Dannii on May 11, 2008, 01:21:12 AM
Try adding $db_show_debug = true; to Settings.php

Danni, can I trust you with complete acces to my forum and the database? I am willing to give you 10$ ( I know it's not much ) for your time.
Only someone with good SFM knowledge will be able to find out what's rong.
Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 11, 2008, 08:22:17 PM
I'll do it for free, but I don't have much time. I'll see if I can squeeze it in tonight. PM me the details. (change your passwords first if you like, and then change them back once I'm finished.)
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 12, 2008, 06:44:49 AM
Quote from: Dannii on May 11, 2008, 08:22:17 PM
I'll do it for free, but I don't have much time. I'll see if I can squeeze it in tonight. PM me the details. (change your passwords first if you like, and then change them back once I'm finished.)

I've sent you an email and a PM.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 13, 2008, 05:07:41 PM
Anyone else willing to help me. I've had problems with my forum for 17 days now. 3 people offered to help, none of them showed up yet.
I know I have no right to complain because it's not their job to help but 17 days is long, very long...

Look at it this way, the problem that I'm having is pretty unique, look at it as a challenge lol :D
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 13, 2008, 07:52:58 PM
The problems are solved. The majority of the problems I was experiencing were due to "crappy" coding in a couple of modification files. Some modifications contain the rong code to create and alter tables and others don't have a proper uninstallation file.

Carl from www.realapplets.com worked for hours on my forum and managed to fix every single error and every problem.

He configured my forum so that I get an email every time a mod alters or creates a new table, this way I see if the SQL querry is correct.

Title: Re: Mods can't create tables in MYSQL database
Post by: Dannii on May 14, 2008, 10:46:50 AM
Glad to hear it, sorry I couldn't help, been flat out with uni work until now. Deleting your PMs now.
Title: Re: Mods can't create tables in MYSQL database
Post by: Jelle Mees on May 14, 2008, 11:53:36 AM
Quote from: Dannii on May 14, 2008, 10:46:50 AM
Glad to hear it, sorry I couldn't help, been flat out with uni work until now. Deleting your PMs now.

Np, you DID help me. Thanks. ;)