News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Try to restore database

Started by Coola_Daffy, July 12, 2009, 02:20:11 PM

Previous topic - Next topic

Coola_Daffy

Hi.
Can someone help me?
I`m getting error while importing the database.SMF2
RC1-1

SQL-Q:

CREATE TABLE `smf_log_reported_comments` (
id_comment mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
id_report mediumint( 8 ) NOT NULL default 0,
id_member mediumint( 8 ) NOT NULL default,
membername tinytext NOT NULL default '',
COMMENT tinytext NOT NULL default '',
time_sent int( 10 ) NOT NULL default,
PRIMARY KEY ( id_comment ) ,
KEY id_report( id_report ) ,
KEY id_member( id_member ) ,
KEY time_sent( time_sent )
) TYPE = MYISAM ;

MySQL sa: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
  membername tinytext NOT NULL default '',
  comment tinytext NOT NULL defaul' at line 4


Dont get this. A brand new resent safecopy and cant import it...
?

Norv

Please replace the lines with

CREATE TABLE `smf_log_reported_comments` (
id_comment mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
id_report mediumint( 8 ) NOT NULL default 0,
id_member mediumint( 8 ) NOT NULL default 0,
membername tinytext NOT NULL default '',
COMMENT tinytext NOT NULL default '',
time_sent int( 10 ) NOT NULL default 0,
PRIMARY KEY ( id_comment ) ,
KEY id_report( id_report ) ,
KEY id_member( id_member ) ,
KEY time_sent( time_sent )
) TYPE = MYISAM ;

and try again.

I will look into replicating the error, if possible.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Coola_Daffy

Thank you that helpt but yet another error.
I`m no guru what so ever in SQL so I must ask, should the charset be set to UTF8 while importing the file ?

(next error)
Error

SQL-q:

-- -- Dumping data in `smf_members` -- INSERT INTO `smf_members` (`id_member`, `member_name`, `date_registered`, `posts`, `id_group`, `lngfile`, `last_login`, `real_name`, `instant_messages`, `unread_messages`, `buddy_list`, `pm_ignore_list`, `message_labels`, `passwd`, `email_address`, `personal_text`, `gender`, `birthdate`, `website_title`, `website_url`, `location`, `filter_date`, `filter_topics`, `filter_paginate`, `icq`, `aim`, `yim`, `msn`, `hide_email`, `show_online`, `time_format`, `signature`, `time_offset`, `avatar`, `pm_email_notify`, `karma_bad`, `karma_good`, `usertitle`, `notify_announcements`, `notify_regularity`, `notify_send_body`, `notify_types`, `member_ip`, `member_ip2`, `secret_question`, `secret_answer`, `id_theme`, `is_activated`, `validation_code`, `id_msg_last_visit`, `additional_groups`, `smiley_set`, `id_post_group`, `total_time_logged_in`, `password_salt`, `mod_prefs`, `warning`, `ignore_boards`, `passwd_flood`, `new_pm`, `pm_prefs`, `openid_uri`)[...]

MySQL said: Dokumentation
#1367 - Illegal double '9e45032019' value found during parsing

I find it hard to beleve that such error is found in what ever creates the database copy so I figure I`m doing something really wrong..

Norv

Is that '9e45032019' in the file, and can you please copy the lines that contain it? This is an error reported very very rarely along years, and I don't know what is its cause.


Perhaps if the forum is utf8, and the database the same, you could tell phpMyAdmin to consider the imported file as utf8.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Coola_Daffy

Actually I don't have a clue..
I have the Swedish UTF8 lang. as default.
What I posted above is the whole of it (in the error message in phpmyadmin).

Not sure if I know what you ment..?

Sure I have older copys but it feels that I just making more mess than good :)
(I run maintance tools every day including safe copying the DB aswell.)

edit:spelling

Norv

About the error: please open if possible (if not too big) the backup .sql file in a simple text editor like Notepad++, and search for the text 9e45032019. If you find it anywhere, then please copy those lines here.
(It may be nowhere in the file, I must admit this is a shot in the wild, but checking it would be the first step.)

About utf8: I'd say it may be better if you do use "import as utf8" or so (I don't remember the exact option in phpMyAdmin offhand) when trying to import the file.

Eventually, if you want, I can look at the backup file and try cleaning/correcting it until it can be imported in a database. Please feel free to let me know if you agree, in that case I will PM you an account to use.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Coola_Daffy

#6
The string looks like this. (unmarked IP, email and username)

(50, 'XxxxxxX', 1224692384, 1, 0, '', 1231637084, 'XxxxxxX', 16, 11, '', '', '', 'db0a6a1e84b37dff1cbbd2600f55798071d64d96', '[email protected]', '', 2, '0001-01-01', '', '', '', 0, 0, 0, '', '', '', '', 1, 1, '', '', 0, '', 1, 0, 0, '', 1, 1, 0, 2, '##.###.###.##', '##.###.###.##', '', '', 3, 1, 9e45032019, 9159, '', '', 4, 6950, 'e95d', '', 0, '', '', 1, 0, ''),:


I searched the file but there are no double values.

I must consult my co-administrators regarding the safe copy.
I really apreciate the exelent help you and other kind people are providing me. Hope you understand.

Sinserely.

edit: added

I tried a three days old copy but same error as before..
As of now the forum is unaccessble.. (usernamne does not exist) my admin account

Norv

Ah, so it's a validation code! But it should be with ' and ', not without them.
Please replace the code between ( and ) that you present in the previous message, with:

(50, 'XxxxxxX', 1224692384, 1, 0, '', 1231637084, 'XxxxxxX', 16, 11, '', '', '', 'db0a6a1e84b37dff1cbbd2600f55798071d64d96', '[email protected]', '', 2, '0001-01-01', '', '', '', 0, 0, 0, '', '', '', '', 1, 1, '', '', 0, '', 1, 0, 0, '', 1, 1, 0, 2, '##.###.###.##', '##.###.###.##', '', '', 3, 1, '9e45032019', 9159, '', '', 4, 6950, 'e95d', '', 0, '', '', 1, 0, '')

That is, 9e45032019 was replaced with '9e45032019'
Also, can you please find in the backup file the first occurrence of smf_members, where the table is created, and copy paste here the entire creation line? (it's only structure of the table, it's no data)
Something like (this is the structure of smf_members of a database of one of my forums):

DROP TABLE IF EXISTS `smf_members`;
CREATE TABLE IF NOT EXISTS `smf_members` (
  `id_member` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `member_name` varchar(80) NOT NULL DEFAULT '',
  `date_registered` int(10) unsigned NOT NULL DEFAULT '0',
  `posts` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `id_group` smallint(5) unsigned NOT NULL DEFAULT '0',
  `lngfile` tinytext NOT NULL,
  `last_login` int(10) unsigned NOT NULL DEFAULT '0',
  `real_name` tinytext NOT NULL,
  `instant_messages` smallint(5) NOT NULL DEFAULT '0',
  `unread_messages` smallint(5) NOT NULL DEFAULT '0',
  `new_pm` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `buddy_list` text NOT NULL,
  `pm_ignore_list` tinytext NOT NULL,
  `pm_prefs` mediumint(8) NOT NULL DEFAULT '0',
  `mod_prefs` varchar(20) NOT NULL DEFAULT '',
  `message_labels` text NOT NULL,
  `passwd` varchar(64) NOT NULL DEFAULT '',
  `openid_uri` text NOT NULL,
  `email_address` tinytext NOT NULL,
  `personal_text` tinytext NOT NULL,
  `gender` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `birthdate` date NOT NULL DEFAULT '0001-01-01',
  `website_title` tinytext NOT NULL,
  `website_url` tinytext NOT NULL,
  `location` tinytext NOT NULL,
  `icq` tinytext NOT NULL,
  `aim` varchar(16) NOT NULL DEFAULT '',
  `yim` varchar(32) NOT NULL DEFAULT '',
  `msn` tinytext NOT NULL,
  `hide_email` tinyint(4) NOT NULL DEFAULT '0',
  `show_online` tinyint(4) NOT NULL DEFAULT '1',
  `time_format` varchar(80) NOT NULL DEFAULT '',
  `signature` text NOT NULL,
  `time_offset` float NOT NULL DEFAULT '0',
  `avatar` tinytext NOT NULL,
  `pm_email_notify` tinyint(4) NOT NULL DEFAULT '0',
  `karma_bad` smallint(5) unsigned NOT NULL DEFAULT '0',
  `karma_good` smallint(5) unsigned NOT NULL DEFAULT '0',
  `usertitle` tinytext NOT NULL,
  `notify_announcements` tinyint(4) NOT NULL DEFAULT '1',
  `notify_regularity` tinyint(4) NOT NULL DEFAULT '1',
  `notify_send_body` tinyint(4) NOT NULL DEFAULT '0',
  `notify_types` tinyint(4) NOT NULL DEFAULT '2',
  `member_ip` tinytext NOT NULL,
  `member_ip2` tinytext NOT NULL,
  `secret_question` tinytext NOT NULL,
  `secret_answer` varchar(64) NOT NULL DEFAULT '',
  `id_theme` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `is_activated` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `validation_code` varchar(10) NOT NULL DEFAULT '',
  `id_msg_last_visit` int(10) unsigned NOT NULL DEFAULT '0',
  `additional_groups` tinytext NOT NULL,
  `smiley_set` varchar(48) NOT NULL DEFAULT '',
  `id_post_group` smallint(5) unsigned NOT NULL DEFAULT '0',
  `total_time_logged_in` int(10) unsigned NOT NULL DEFAULT '0',
  `password_salt` varchar(255) NOT NULL DEFAULT '',
  `ignore_boards` text NOT NULL,
  `warning` tinyint(4) NOT NULL DEFAULT '0',
  `passwd_flood` varchar(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`id_member`),
  KEY `memberName` (`member_name`(30)),
  KEY `date_registered` (`date_registered`),
  KEY `id_group` (`id_group`),
  KEY `birthdate` (`birthdate`),
  KEY `posts` (`posts`),
  KEY `last_login` (`last_login`),
  KEY `lngfile` (`lngfile`(30)),
  KEY `id_post_group` (`id_post_group`),
  KEY `warning` (`warning`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;


And please don't worry, of course your decision is respected.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Coola_Daffy

This is really strange..
Of no reasson at all, the forrum is online at last.

My guess is that the host is to blaim..

Many thanks for your time Norv.
Sorry to say but I have no clue what so ever why this happend.
I installed another forum intended for testing and after that installation, the first forum came online.
Have one DB per each but same table-prefix in both..
Before I installed the new forum I erased all old tables.
(first installation was a mess. Had to do it again)
anyway.. Thank you.

Norv

Ah I see, good to hear it's solved!

Please let me explain though that the two issues here, with the backup, are submitted to bug reports for 2.0 RC1-1 (I tested them on fresh test installations and they're bugs, clearly enough). They can be manually avoided, as above. But for the time being, you might run into them when restoring a backup, without doing anything wrong.
(the second should be very very rare though, only when a member forgot his password and asked for it, until they do reset their password, or so)
Please see here if you want on the Bug reports board: No default values for not null int fields (causing issues with MySQL) and Varchar(10) field incorrectly dumped in the database backup file.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Coola_Daffy

Norv: I can not apresiate your effort enoght..
No wonder this forum is "the right place to go"..
Doh My English isn't good enught but your bug report seemed 100% acurat and familiar to what I encountered.

Again, Thank You and your fellow helpers.

Sincerely //CD

JimM

Looks like Norv has solved another one.  Great job!  I'll mark this solved for now.  :)
Jim "JimM" Moore
Former Support Specialist

Advertisement: