News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

[Paid]Help needed with database.

Started by GizmoPower, June 01, 2013, 05:29:49 PM

Previous topic - Next topic

GizmoPower

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.  :)


Colin

What data do you lose when you change the path to point to the mySQL service?
"If everybody is thinking alike, then somebody is not thinking." - Gen. George S. Patton Jr.

Colin

GizmoPower

#2
Thank you for answering  :)
Everything. It says that there is an error in database. And i cant log in.

bros

Does the MySQL database have any size?

What happens if you rename the sqllite DB?

GizmoPower

If I rename sqlite file, I get:
QuoteSQL logic error or missing database
no such table: smf_sessions

The Mysql is only 0.34MB

GizmoPower

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

GizmoPower

#6
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.

GizmoPower

#7
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?

bros

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)

TheListener

@GizmoPower I have moved the topic to a more suitable board so hopefully the topic will get more noticed.

;)

emanuele

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.


Take a peek at what I'm doing! ;D




Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

GizmoPower

#11
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)

emanuele

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. ;)


Take a peek at what I'm doing! ;D




Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

GizmoPower

Yes. I understand what you mean now.  :)
I am gong for solution 2
Will give feeddback When finished.  :)

Tanks again Emanuele!

GizmoPower

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?

GizmoPower

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

GizmoPower

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>';
   }
}

emanuele

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...


Take a peek at what I'm doing! ;D




Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

GizmoPower

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   :)

emanuele

A quick question just to be sure: when you installed the mysql version, did you set it UTF8 or not?


Take a peek at what I'm doing! ;D




Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

Advertisement: