Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => PostgreSQL and SQLite Support => Topic started by: GizmoPower on June 01, 2013, 05:29:49 PM

Title: [Paid]Help needed with database.
Post by: GizmoPower on June 01, 2013, 05:29:49 PM
Hello all!
I really need some serious help here.
I´ve been googling and trying for months now to get a solution to my problem.

Long story short....

I installed Smf on my synology nas at home for testing. It only supported Sqlite.
After a while I decided to get a external host.
I dident want to lose data, as several members had registered.

Now I had to convert Sqlite to Mysql, wich I dident manage.
I tried for a while, nothing worked,  but suddenly I could install  smf with Mysql settings and it worked great!

But then I realized that a file called forum_name.db kept on growing.
So what happened is that it still uses this file from sqlite and also uses Mysql.
In smf settings all data is directed to Mysql.

Now the problem is that I am always getting errors when trying to install mods and stuff, like: "Your database is out of date, please update" or simply "Error!"
I am on 2.0.4

So basicly what I need is to merge the Sqlite into existing Mysql
Because when I now point the path to Mysql I  loose a lot of data

I usually find a solution on things, but this one is hard! I am really lost here people. Please help me....
If anyone want to help I can give U access to cpanel. I am desperate   :)

Hoping for some positive answers.  :)

Title: Re: [Paid]Help needed with database.
Post by: Colin on June 01, 2013, 07:58:18 PM
What data do you lose when you change the path to point to the mySQL service?
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 01, 2013, 08:27:13 PM
Thank you for answering  :)
Everything. It says that there is an error in database. And i cant log in.
Title: Re: [Paid]Help needed with database.
Post by: bros on June 01, 2013, 10:07:43 PM
Does the MySQL database have any size?

What happens if you rename the sqllite DB?
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 02, 2013, 05:02:17 AM
If I rename sqlite file, I get:
QuoteSQL logic error or missing database
no such table: smf_sessions

The Mysql is only 0.34MB
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 02, 2013, 10:59:32 AM
I just realized that the forum is only using the Sqlite file. Not the Mysql at all.. If I point to the Mysql database the first time install of smf is there.

I duplicated the forum. Renaming the Sqlite.db file and changed name in rep_settings.php.
Running fine, but I still need to convert the Sqlite to a fresh Mysql database to avoid future problems.

Tried: sqlite_to_mysql.php, but when I import to Mysql it gives me this error:
QuoteError
SQL query:

-- ========================================================== -- -- Database dump of tables in `kontrast_forum2` -- 02-06-2013, 16:45:14 -- -- ========================================================== -- -- Dumping data in `smf_admin_info_files` -- INSERT INTO `smf_admin_info_files` (`id_file`, `filename`, `path`, `parameters`, `data`, `filetype`) VALUES (1, 'current-version.js', '/smf/', 'version=%3$s', 'window.smfVersion = \"SMF 2.0.4\";', 'text/javascript'), (2, 'detailed-version.js', '/smf/', 'language=%1$s&version=%3$s', 'window.smfVersions = {\n   \'SMF\': \'SMF 2.0.4\',\n   \'SourcesAdmin.php\': \'2.0\',\n   \'SourcesBoardIndex.php\': \'2.0\',\n \'SourcesCalendar.php\': \'2.0\',\n   \'SourcesClass-Graphics.php\': \'2.0\',\n   \'SourcesClass-Package.php\': \'2.0\',\n   \'SourcesDbExtra-mysql.php\': \'2.0\',\n   \'SourcesDbExtra-postgresql.php\': \'2.0\',\n   \'SourcesDbExtra-sqlite.php\': \'2.0\',\n   \'SourcesDbPackages-mysql.php\': \'2.0\',\n   \'SourcesDbPackages-postgresql.p[...]

MySQL said:

#1146 - Table 'kontrast_forum2.smf_admin_info_files' doesn't exist
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 02, 2013, 02:34:52 PM
This is the error while trying to update or upgrade to 2.0.4
It wount upgrade my database. wich is a sqlite 2.1.
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 02, 2013, 04:29:14 PM
Well, I will keep on answering my self as I go along.
Maybe some others can make use of this thread, or even help me :)

What I now did was:

upload and run: sqlite_to_mysql.php (Can be found here on forum)
Made a new Mysql database with same prefix as sqlite had Ex: "smf"

Imported the dump to the new mysql.
BUT... Now I have an error:

QuoteError
SQL query:

-- -------------------------------------------------------- -- -- Dumping data in `smf_boards` -- INSERT INTO `smf_boards` (`id_board`, `id_cat`, `child_level`, `id_parent`, `board_order`, `id_last_msg`, `id_msg_updated`, `member_groups`, `id_profile`, `name`, `description`, `num_topics`, `num_posts`, `count_posts`, `id_theme`, `override_theme`, `unapproved_posts`, `unapproved_topics`, `redirect`, `facebooklike_board_enable`) VALUES (1, 4, 0, 0, 10, 2535, 2535, '0,2,10,9,11', 1, 'Velkommen', 'Se her f�rst.', 3, 42, 0, 0, 0, 0, 0, '', ''), (6, 5, 0, 0, 28, 2232, 2232, '-1,0,2,9,10,11', 1, 'Data og komponenter', 'Alt om data software og hardware', 18, 91, 0, 0, 0, 0, 0, '', ''), (7, 6, 0, 0, 60, 1651, 1651, '-1,0,2,9,10,11', 1, 'Spill og konsoll', 'Favoritt spill, eller problemer med spill konsollen? Post her', 2, 2, 0, 0, 0, 0, 0, '', ''), (8, 5, 0, 0, 40, 2122, 2122, '-1,0,2,10,9,11', 1, 'Lyd, bilde og lys', 'Alt inne bilde og lyd komponenter eller programmvare her',[...]

MySQL said:

#1054 - Unknown column 'facebooklike_board_enable' in 'field list'

I disabled and unistalled the facebook like button a while ago.
How can i get rid of this?
Title: Re: [Paid]Help needed with database.
Post by: bros on June 02, 2013, 06:23:07 PM
Perhaps try converting it table-by-table? I've had to do that when having upgrade issues (Like I recently found a database from a board I ran in 2007, wanted to see the posts on it, had to convert from 1.0.7 to 2.0.4. That was a fun time)
Title: Re: [Paid]Help needed with database.
Post by: TheListener on June 02, 2013, 06:27:44 PM
@GizmoPower I have moved the topic to a more suitable board so hopefully the topic will get more noticed.

;)
Title: Re: [Paid]Help needed with database.
Post by: emanuele on June 03, 2013, 02:21:55 AM
Quote from: GizmoPower on June 02, 2013, 04:29:14 PM
Quote
MySQL said:

#1054 - Unknown column 'facebooklike_board_enable' in 'field list'

I disabled and unistalled the facebook like button a while ago.
How can i get rid of this?
Go to phpMyAdmin and create a column "facebooklike_board_enable" in the table smf_boards.
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 03, 2013, 04:23:58 AM
Thank you all for answering and moving topic to a more suitable place  :)
Good tips, I will definitiv try both of youre suggestions.
I will report back either way.

I am having great fun with this database issue, I can asure you..... NOT!!
But I will not give up.  8)
Title: Re: [Paid]Help needed with database.
Post by: emanuele on June 03, 2013, 07:15:38 AM
Well, converting from two different SQL dialects is not always straightforward.
What you are experiencing is simply due to the fact that my converter expects the destination database to have exactly the same schema as the original one.
Since in your original you have additional things (from old mods installations) there are only two paths you can follow:
1) remove the non-necessary columns/tables from your SQLite db,
2) add those columns to your MySQL db.
Since 1 requires you installing another script to edit SQLite dbs, I think it should be easier to re-create the schema in MySQL. ;)
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 03, 2013, 07:39:06 AM
Yes. I understand what you mean now.  :)
I am gong for solution 2
Will give feeddback When finished.  :)

Tanks again Emanuele!
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 03, 2013, 12:30:18 PM
Well I got it up and running  8)
Thank you all for the support. I definitive wouldent make this witouth you guys.
Thank you, thank you...  :)

Now i have problem with simple portal.
It dosent show anything, only blank....
Can i uninstall and install again without losing my settings.
It seems to be a path issue. In sp meny also som pics are missing.

And because I have a norwegian site, all my å, ø, å are ? In post. Not menu!
Is there a fix for that?
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 03, 2013, 12:42:00 PM
So I upgraded to 2.0.4 and sp disapeared.
Tried to install and got this error:
Quote
Incorrect table definition; there can be only one auto column and it must be defined as a key
Fil: /home/kontrast/public_html/forum/Packages/temp/install2.php
Linje: 515
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 03, 2013, 02:45:12 PM
Here is the line its complaining about.
Line 515 in red

Quote// We always need a fresh functions table.
$smcFunc['db_drop_table']('{db_prefix}sp_functions');

$current_tables = $smcFunc['db_list_tables'](false, '%sp%');
$real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
$info = '<ul>';

// Loop through each table and do what needed.
foreach ($tables as $table => $data)
{
   if (in_array(strtolower($real_prefix . $table), array_map('strtolower', $current_tables)))
   {
      $info .= '
   <li>"' . $table . '" table exists, updating table structure.
      <ul>';

      foreach ($data['columns'] as $column)
      {
         if (!isset($column['deprecated_name']) || !$smcFunc['db_change_column']('{db_prefix}' . $table, $column['deprecated_name'], $column))
515-->   $smcFunc['db_add_column']('{db_prefix}' . $table, $column);
      }

      $info .= '
         <li>Table columns updated.</li>';

      foreach ($data['indexes'] as $index)
         $smcFunc['db_add_index']('{db_prefix}' . $table, $index, array(), 'ignore');

      $info .= '
         <li>Table indexes updated.</li>
      </ul>
   </li>';
   }
   else
   {
      $smcFunc['db_create_table']('{db_prefix}' . $table, $data['columns'], $data['indexes'], array(), 'ignore');

      $info .= '<li>"' . $table . '" table created.</li>';
   }
}
Title: Re: [Paid]Help needed with database.
Post by: emanuele on June 03, 2013, 03:42:53 PM
Quote from: GizmoPower on June 03, 2013, 12:30:18 PM
And because I have a norwegian site, all my å, ø, å are ? In post. Not menu!
Is there a fix for that?
That may be an issue. I never found how to fix that...
Title: Re: [Paid]Help needed with database.
Post by: GizmoPower on June 08, 2013, 05:15:12 PM
So...
I moved my database from SQlite 2.1 to MYsql following whats been discussed in this thread.
Only issue, well for me beeing Norwegian, a big issue, is that some letter is the vocabulary shows as ? (Æ Ø Å)
Other than that, Simple Portal wount install.

Anyway, I am happy with the result.

Thank for the support emanuele   :)
Title: Re: [Paid]Help needed with database.
Post by: emanuele on June 08, 2013, 05:24:01 PM
A quick question just to be sure: when you installed the mysql version, did you set it UTF8 or not?