mysql restore...

beesatmsu · Apr 12, 2014

my mysql seems to be corrupted and all the mysqldump files did not contain anything...but I used phpmyadmin today and "exported" a sql file. however, phpmyadmin wont allow me to upload back (file too large). if i use the command line, i got an error the database already exists. I commented the first line "create xxx" but this creates another error.

i used phpmyadmin to delete all the databases and was able to restore a copy in Jan. all dumps made after that adds nothing (a bit strange the web worked fine till the HD had a SMART error today, so where did all the data were from? somehow the command line dumps were no good (created by cron daily). but todays phpmyadmin had 238 meg (regular dumps were 86k).

so all my wordpress sites has only posts in Dec....

obsigna · Apr 12, 2014

beesatmsu said:
... a bit strange the web worked fine till the HD had a SMART error today ...

You WANT TO ADHERE to the following recovering strategy:

1. remove the failing disk from the machine immediately, and put it into an USB case.
2. setup a new system on another disk, or for the time being, use a Live FreeBSD USB-Stick.
3. use ddrescue(1) to recover as much data as possible from the failing disk to a dedicated volume of a pristine disk.
4. put the failing disk to a safe place and use the ddrescued data on the pristine disk for your desaster recovery trials.

Don't use the failing disk for anything else than for ddrescuing your data.

Once you got your data ddrescued, come back with your SQL questions.

beesatmsu · Apr 12, 2014

thank you. but that HD is now totally messed up, I worked all day yesterday and did not eat lunch...that did not help me to think clearly.
I used to use gmirror (instead of DD, i thought it was easier) to back up things...but yesterday it went wrong. Despite of me insuring the slave was not working (it wont boot directly, but goes to the mountroot prompt) and then in bios also make it a slave and 2nd booting device (next time i guess not as a booting device at all). still it went ahead and booted successfully to the slave (!). it probably started gmirror to the master, i rebooted after seeing the domain was not correct but still too late). now it has the same mountroot prompt and i was not able to do single user and reboot it.

but still i think the database exported file should be ok.
i should have let it run yesterday instead of trying to make a copy. now I have only one slave drive with data from the 1st week of this year. i need to figure out a way to restore/import that one to the server. I tried a recent dump and it did not seem to contain data. but restoring the phpmyadmin one (it saved on my PC and i uploaded to it) did not work (saying one database existed already). I dumped one database but somehow it contained at lease 2 other small one also (for moodle, gallery), the main one was for wordpress sites (about 6 different sites).

I had a slave rsync daily to retrieve backups...but i think i was too aggressive or my cron script should have deleted old backups (e.g. usr/home was 20 G per week, after 50 weeks 1TB was full) and the HD filled up for both and that might have caused a database error? anyway all dumps are 10% of real size after 1st week.

obsigna · Apr 12, 2014

beesatmsu said:
thank you. but that HD is now totally messed up, I worked all day yesterday and did not eat lunch...that did not help me to think clearly.
I used to use gmirror (instead of DD, i thought it was easier) to back up things...but yesterday it went wrong. Despite of me insuring the slave was not working (it wont boot directly, but goes to the mountroot prompt) and then in bios also make it a slave and 2nd booting device (next time i guess not as a booting device at all). still it went ahead and booted successfully to the slave (!). it probably started gmirror to the master, i rebooted after seeing the domain was not correct but still too late). now it has the same mountroot prompt and i was not able to do single user and reboot it.

So, you accidentally mirrored a backup disk to the working disk (which showed SMART issues), and for this reason, the original data has gone. Is that correct?

beesatmsu said:
but still i think the database exported file should be ok.

Thinking is not the same as knowing. Show us the first 100 and the last 100 lines of that file, i.e. the respective output of each of the following 2 commands:

head -100 /path/to/exported_file.sql
tail -100 /path/to/exported_file.sql

beesatmsu · Apr 12, 2014

here u go. I also did a tar for all the web files (static) and they all untar-ed ok, so i think the HD was still ok with some reading errors.
the error I got was "beedle" database already exists yesterday. I commented the "create database 'beedle' out and it created another error. else where i saw mentioning repair the table, this was related to a wordpress plugin..i need to remove that one..

i suspect the strange characters are in Chinese. the phpmyadmin interface came up in Chinese and I did not change it to English yesterday.

Code:

head -100  beepress-phpmyadm.sql
-- phpMyAdmin SQL Dump
-- version 3.4.7
-- http://www.phpmyadmin.net
--
-- ä¸»æº: localhost
-- çå¡å¨çæ¬: 5.1.5404 æ 11 æ¥ 14:05
-- PHP çæ¬: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--    ®åº: `beedle`
-- æ°æ
CREATE DATABASE `beedle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `beedle`;

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

--
-- è¡¨ç
       ç»æ
           `adodb_logsql`
--

CREATE TABLE IF NOT EXISTS `adodb_logsql` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `sql0` varchar(250) NOT NULL DEFAULT '',
  `sql1` text,
  `params` text,
  `tracer` text,
  `timer` decimal(16,6) NOT NULL DEFAULT '0.000000',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='to save some logs from ADOdb' AUTO_INCREMENT=1 ;

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

--
-- è¡¨ç
       ç»æ
           `bdl_assignment`
--

CREATE TABLE IF NOT EXISTS `bdl_assignment` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `course` bigint(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `format` smallint(4) unsigned NOT NULL DEFAULT '0',
  `assignmenttype` varchar(50) NOT NULL DEFAULT '',
  `resubmit` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `preventlate` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `emailteachers` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `var1` bigint(10) DEFAULT '0',
  `var2` bigint(10) DEFAULT '0',
  `var3` bigint(10) DEFAULT '0',
  `var4` bigint(10) DEFAULT '0',
  `var5` bigint(10) DEFAULT '0',
  `maxbytes` bigint(10) unsigned NOT NULL DEFAULT '100000',
  `timedue` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timeavailable` bigint(10) unsigned NOT NULL DEFAULT '0',
  `grade` bigint(10) NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `bdl_assi_cou_ix` (`course`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Defines assignments' AUTO_INCREMENT=1 ;

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

--
-- è¡¨ç
       ç»æ
           `bdl_assignment_submissions`
--

CREATE TABLE IF NOT EXISTS `bdl_assignment_submissions` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `assignment` bigint(10) unsigned NOT NULL DEFAULT '0',
  `userid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timecreated` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  `numfiles` bigint(10) unsigned NOT NULL DEFAULT '0',
  `data1` text,
  `data2` text,
  `grade` bigint(11) NOT NULL DEFAULT '0',
  `submissioncomment` text NOT NULL,
  `format` smallint(4) unsigned NOT NULL DEFAULT '0',
  `teacher` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timemarked` bigint(10) unsigned NOT NULL DEFAULT '0',
  `mailed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `bdl_assisubm_use_ix` (`userid`),
  KEY `bdl_assisubm_mai_ix` (`mailed`),
  KEY `bdl_assisubm_tim_ix` (`timemarked`),
  KEY `bdl_assisubm_ass_ix` (`assignment`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Info about submitted assignments' AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
>

end:

Code:

> tail -100 beepress-phpmyadm.sql
  `user_allowbbcode` tinyint(1) DEFAULT '1',
  `user_allowsmile` tinyint(1) DEFAULT '1',
  `user_allowavatar` tinyint(1) NOT NULL DEFAULT '1',
  `user_allow_pm` tinyint(1) NOT NULL DEFAULT '1',
  `user_allow_viewonline` tinyint(1) NOT NULL DEFAULT '1',
  `user_notify` tinyint(1) NOT NULL DEFAULT '1',
  `user_notify_pm` tinyint(1) NOT NULL DEFAULT '0',
  `user_popup_pm` tinyint(1) NOT NULL DEFAULT '0',
  `user_rank` int(11) DEFAULT '0',
  `user_avatar` varchar(100) DEFAULT NULL,
  `user_avatar_type` tinyint(4) NOT NULL DEFAULT '0',
  `user_email` varchar(255) DEFAULT NULL,
  `user_icq` varchar(15) DEFAULT NULL,
  `user_website` varchar(100) DEFAULT NULL,
  `user_from` varchar(100) DEFAULT NULL,
  `user_sig` text,
  `user_sig_bbcode_uid` char(10) DEFAULT NULL,
  `user_aim` varchar(255) DEFAULT NULL,
  `user_yim` varchar(255) DEFAULT NULL,
  `user_msnm` varchar(255) DEFAULT NULL,
  `user_occ` varchar(100) DEFAULT NULL,
  `user_interests` varchar(255) DEFAULT NULL,
  `user_actkey` varchar(32) DEFAULT NULL,
  `user_newpasswd` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `user_session_time` (`user_session_time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- è½¬åè¡¨ä¸ç   ® `phpcyb_users`
--             æ°æ

INSERT INTO `phpcyb_users` (`user_id`, `user_active`, `username`, `user_password`, `user_session_time`, `user_session_page`, `user_lastvisit`, `user_regdate`, `user_level`, `user_posts`, `user_timezone`, `user_style`, `user_lang`, `user_dateformat`, `user_new_privmsg`, `user_unread_privmsg`, `user_last_privmsg`, `user_login_tries`, `user_last_login_try`, `user_emailtime`, `user_viewemail`, `user_attachsig`, `user_allowhtml`, `user_allowbbcode`, `user_allowsmile`, `user_allowavatar`, `user_allow_pm`, `user_allow_viewonline`, `user_notify`, `user_notify_pm`, `user_popup_pm`, `user_rank`, `user_avatar`, `user_avatar_type`, `user_email`, `user_icq`, `user_website`, `user_from`, `user_sig`, `user_sig_bbcode_uid`, `user_aim`, `user_yim`, `user_msnm`, `user_occ`, `user_interests`, `user_actkey`, `user_newpasswd`) VALUES
(-1, 0, 'Anonymous', '', 0, 0, 0, 1329853477, 0, 0, '0.00', NULL, '', '', 0, 0, 0, 0, 0, NULL, 0, 0, 1, 1, 1, 1, 0, 1, 0, 1, 0, NULL, '', 0, '', '', '', '', '', NULL, '', '', '', '', '', '', ''),
(2, 1, 'zhiyong', 'd9d1b877287301a9ae10c991e954a51f', 1330025975, 1, 1330014819, 1329853477, 1, 4, '0.00', 1, 'english', 'd M Y h:i a', 0, 0, 0, 0, 0, NULL, 0, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 1, '', 0, 'bees.msu@gmail.com', '', '', '', '', '', '', '', '', '', '', '', ''),
(3, 1, 'tgiray', 'f1aeddcf0c75bb59b3f879d3a337844c', 1329969872, 0, 1329933212, 1329932564, 0, 1, '0.00', 1, 'english', 'D M d, Y g:i a', 0, 0, 0, 0, 0, NULL, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, '', 0, 'tgiray2@yahoo.com', '', '', 'Puerto Rico', 'Tugrul Giray', '1a372d450d', '', '', '', '', '', '', NULL),
(4, 1, 'wuj', '2121dd1d98b49c7d0d80d36c291371d9', 1329942766, -4, 1329942700, 1329942650, 0, 0, '-8.00', 1, 'english', 'D M d, Y g:i a', 0, 0, 1329942700, 0, 0, NULL, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0, '', 0, 'jianwu2nd@gmail.com', '', '', '', '', '', '', '', '', '', '', '', NULL);

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

--
-- è¡¨ç
       ç»æ
           `phpcyb_vote_desc`
--

CREATE TABLE IF NOT EXISTS `phpcyb_vote_desc` (
  `vote_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `vote_text` text NOT NULL,
  `vote_start` int(11) NOT NULL DEFAULT '0',
  `vote_length` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`vote_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- è¡¨ç
       ç»æ
           `phpcyb_vote_results`
--

CREATE TABLE IF NOT EXISTS `phpcyb_vote_results` (
  `vote_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `vote_option_id` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `vote_option_text` varchar(255) NOT NULL,
  `vote_result` int(11) NOT NULL DEFAULT '0',
  KEY `vote_option_id` (`vote_option_id`),
  KEY `vote_id` (`vote_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- è¡¨ç
       ç»æ
           `phpcyb_vote_voters`
--

CREATE TABLE IF NOT EXISTS `phpcyb_vote_voters` (
  `vote_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `vote_user_id` mediumint(8) NOT NULL DEFAULT '0',
  `vote_user_ip` char(8) NOT NULL,
  KEY `vote_id` (`vote_id`),
  KEY `vote_user_id` (`vote_user_id`),
  KEY `vote_user_ip` (`vote_user_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- è¡¨ç
       ç»æ
           `phpcyb_words`
--

CREATE TABLE IF NOT EXISTS `phpcyb_words` (
  `word_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `word` char(100) NOT NULL,
  `replacement` char(100) NOT NULL,
  PRIMARY KEY (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

further checking my backedup files, I am still baffled why the mysql dump had all different sizes...it seems only 1st week of 2014 had a good copy (larger size). i can post the smaller files here if you want to see them...it does not seem to be related to /usr running low on space since tar file was much larger and it was written.

Code:

> ls -l year/week40
total 13430726
-rw-r--r--  1 huang  user      48821509 Oct 12  2013 Mysql-Sat.sql.gz
-rw-r--r--  1 huang  wheel       118272 Oct 12  2013 Named-master-Sat.tar
-rw-r--r--  1 huang  user          1024 Oct 12  2013 drone-usr-home-Sat.tar
-rw-r--r--  1 huang  user   13697345024 Oct  6  2013 drone-usr-home-Sun.tar
> ls -l year/week50
total 13443462
-rw-r--r--  1 huang  user      49335473 Dec 21 04:30 Mysql-Sat.sql.gz
-rw-r--r--  1 huang  wheel       118272 Dec 21 05:04 Named-master-Sat.tar
-rw-r--r--  1 huang  user          1024 Dec 21 00:18 drone-usr-home-Sat.tar
-rw-r--r--  1 huang  user   13709853184 Dec 15 00:37 drone-usr-home-Sun.tar
> ls -l year/week00
total 13669606
-rw-r--r--  1 huang  user     290424582 Jan  4 04:30 Mysql-Sat.sql
-rw-r--r--  1 huang  wheel       118272 Jan  4 05:04 Named-master-Sat.tar
-rw-r--r--  1 huang  user          1024 Jan  4 00:18 drone-usr-home-Sat.tar
-rw-r--r--  1 huang  user   13700221440 Dec 29 00:38 drone-usr-home-Sun.tar
> ls -l year/week05
total 15428038
-rw-r--r--  1 huang  user      52560330 Feb  8 04:30 Mysql-Sat.sql.gz
-rw-r--r--  1 huang  wheel       118272 Feb  8 05:04 Named-master-Sat.tar
-rw-r--r--  1 huang  user          1024 Feb  8 00:18 drone-usr-home-Sat.tar
-rw-r--r--  1 huang  user   15737836544 Feb  2 00:41 drone-usr-home-Sun.tar

obsigna · Apr 12, 2014

beesatmsu said:
here u go. I also did a tar for all the web files (static) and they all untar-ed ok, so i think the HD was still ok with some reading errors.
the error I got was "beedle" database already exists yesterday. I commented the "create database 'beedle' out and it created another error. else where i saw mentioning repair the table, this was related to a wordpress plugin..i need to remove that one..

i suspect the strange characters are in Chinese. the phpmyadmin interface came up in Chinese and I did not change it to English yesterday.

.

As a matter of fact, your dump file contains lines that are not understandable by the mysql interpreter, and for this reason it cannot be imported as is. For example, I saved your output of the head command into a file beedle.sql and feeded this into the mysql client:

mysql --user=root --password=THIS_IS_SECRET < ~/beedle.sql

Code:

ERROR 1064 (42000) at line 27: 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 'ç»æ
           `adodb_logsql`


CREATE TABLE IF NOT EXISTS `adodb_logsql` (
 ' at line 1

The problem here are not exactly the wrongly encoded chinese characters, but the additional line breaks in the comment lines.

Code:

-- è¡¨ç
       ç»æ
           `adodb_logsql`
--

Using a text editor, I manually removed these additional line breaks, and I added a line DROP DATABASE `beedle`;. The corrected import file then reads:

Code:

-- phpMyAdmin SQL Dump
-- version 3.4.7
-- http://www.phpmyadmin.net
--
-- ä¸»æº: localhost
-- çå¡å¨çæ¬: 5.1.5404 æ 11 æ¥ 14:05
-- PHP çæ¬: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--    ®åº: `beedle`
-- æ°æ
DROP DATABASE `beedle`;
CREATE DATABASE `beedle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `beedle`;

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

--
-- è¡¨ç ç»æ `adodb_logsql`
--

CREATE TABLE IF NOT EXISTS `adodb_logsql` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `sql0` varchar(250) NOT NULL DEFAULT '',
  `sql1` text,
  `params` text,
  `tracer` text,
  `timer` decimal(16,6) NOT NULL DEFAULT '0.000000',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='to save some logs from ADOdb' AUTO_INCREMENT=1 ;

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

--
-- è¡¨ç ç»æ `bdl_assignment`
--

CREATE TABLE IF NOT EXISTS `bdl_assignment` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `course` bigint(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `format` smallint(4) unsigned NOT NULL DEFAULT '0',
  `assignmenttype` varchar(50) NOT NULL DEFAULT '',
  `resubmit` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `preventlate` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `emailteachers` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `var1` bigint(10) DEFAULT '0',
  `var2` bigint(10) DEFAULT '0',
  `var3` bigint(10) DEFAULT '0',
  `var4` bigint(10) DEFAULT '0',
  `var5` bigint(10) DEFAULT '0',
  `maxbytes` bigint(10) unsigned NOT NULL DEFAULT '100000',
  `timedue` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timeavailable` bigint(10) unsigned NOT NULL DEFAULT '0',
  `grade` bigint(10) NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `bdl_assi_cou_ix` (`course`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Defines assignments' AUTO_INCREMENT=1 ;

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

--
-- è¡¨ç ç»æ `bdl_assignment_submissions`
--

CREATE TABLE IF NOT EXISTS `bdl_assignment_submissions` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `assignment` bigint(10) unsigned NOT NULL DEFAULT '0',
  `userid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timecreated` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  `numfiles` bigint(10) unsigned NOT NULL DEFAULT '0',
  `data1` text,
  `data2` text,
  `grade` bigint(11) NOT NULL DEFAULT '0',
  `submissioncomment` text NOT NULL,
  `format` smallint(4) unsigned NOT NULL DEFAULT '0',
  `teacher` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timemarked` bigint(10) unsigned NOT NULL DEFAULT '0',
  `mailed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `bdl_assisubm_use_ix` (`userid`),
  KEY `bdl_assisubm_mai_ix` (`mailed`),
  KEY `bdl_assisubm_tim_ix` (`timemarked`),
  KEY `bdl_assisubm_ass_ix` (`assignment`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Info about submitted assignments' AUTO_INCREMENT=1 ;

Using the same command as above, this one is imported without a problem.

Conclusion:

Make a copy of the exported_file.sql so you can work on the copy.
Open the copy in a text editor and either remove or correct the broken comment lines (comment lines begin with '--' and must not contain line breaks).
Right before each CREATE DATABASE `dbname`; directive, add a DROP DATABASE `dbname`;
Import the manually corrected .sql-file into the MySQL server.

beesatmsu · Apr 12, 2014

Obsigna,

thank you so much!

I used vi to see the file, and it seems a bit different: [possibly due to head and tail not able to interprete these escape characters? but vi did]

this line (#6 below) seems to have a break in line,

Code:

-- \xe7\x94\x9f\xe6\x88\x90\xe6\x97\xa5\xe6\x9c\x9f: 2014 \xe5\xb9\xb4 04 \xe6\x
9c\x88 11 \xe6\x97\xa5 14:05

but it actually does not by using cursor in VI (it does not beep but moves to next, suggesting no hard return before "9c".
do i still need to do that? or perhaps adding drop line in front of each database might work?

I am now paranoid so either have to install a new copy (did not really want to use gmirror again! although tempted to do so with a partition deleted disk so it wont boot and overwrite my good one). last time I installed a brand copy (system, php, mysql, php and xwin took at least 5 hrs or longer). trying to do a proposal so no time to mess with it now.

Zach

Code:

-- phpMyAdmin SQL Dump
-- version 3.4.7
-- http://www.phpmyadmin.net
--
-- \xe4\xb8\xbb\xe6\x9c\xba: localhost
-- \xe7\x94\x9f\xe6\x88\x90\xe6\x97\xa5\xe6\x9c\x9f: 2014 \xe5\xb9\xb4 04 \xe6\x
9c\x88 11 \xe6\x97\xa5 14:05
-- \xe6\x9c\x8d\xe5\x8a\xa1\xe5\x99\xa8\xe7\x89\x88\xe6\x9c\xac: 5.1.54
-- PHP \xe7\x89\x88\xe6\x9c\xac: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- \xe6\x95\xb0\xe6\x8d\xae\xe5\xba\x93: `beedle`
--
/*CREATE DATABASE `beedle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `beedle`;

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

--
-- \xe8\xa1\xa8\xe7\x9a\x84\xe7\xbb\x93\xe6\x9e\x84 `adodb_logsql`
--

CREATE TABLE IF NOT EXISTS `adodb_logsql` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,

beesatmsu · Apr 12, 2014

I also noticed that the ' I typed in seems to be different than the ` in the file...possibly due to chinese interface?
''''''''``````` : seemed very different.

Code:

DROP DATABASE 'beepress';
CREATE DATABASE `beepress` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c
i;

beesatmsu · Apr 12, 2014

i put "DROP DATABASE 'name'" in front of each one.
tried restore

Code:

 mysql -h localhost -u root -p beepress <bee.sql

I got:

ERROR 2005 (HY000) at line 433: Unknown MySQL server host 'table.MsoNormalTable<br' (1)

this seems to be the same error I got yesterday after commenting out the create database "beedle'.

obsigna · Apr 12, 2014

beesatmsu said:
I used vi to see the file, and it seems a bit different: [possibly due to head and tail not able to interprete these escape characters? but vi did]

this line (#6 below) seems to have a break in line,

Code:

-- \xe7\x94\x9f\xe6\x88\x90\xe6\x97\xa5\xe6\x9c\x9f: 2014 \xe5\xb9\xb4 04 \xe6\x 9c\x88 11 \xe6\x97\xa5 14:05

vi does not interpret anything, it is a horrible editor. We don't need to go into training how to use an editor, do we? Use something modern for editing this file -- even MS Notepad does a better job than vi in this respect.

obsigna · Apr 12, 2014

beesatmsu said:
i put "DROP DATABASE 'name'" in front of each one.
tried restore

Code:

mysql -h localhost -u root -p beepress <bee.sql

I got:

ERROR 2005 (HY000) at line 433: Unknown MySQL server host 'table.MsoNormalTable<br' (1)

Click to expand...

this seems to be the same error I got yesterday after commenting out the create database "beedle'.

You need to put the database name in backticks ` and not in single-quotes '.

You need to put a semicolon at the end of the statement.

obsigna · Apr 12, 2014

beesatmsu said:
Code:

... /*CREATE DATABASE `beedle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

I see this only now, you started a multi line comment with /* and you forgot to close it using */. That alone would lead to completely unpredictable results.

beesatmsu · Apr 12, 2014

thank you. I did have ";' after each line.

I did not get the same error you got, suggesting that the potential line breaks are not causing problem?

should i use ascii mode or binary to transfer the file (to my PC to edit it)? I probably used binary yesterday to ftp from the bad HD to the current server.

I replaced all ' with `, still the same error.

the offending line it complained about:

Code:

Name="List Table 6 Colorful Accent 6"/><w:LsdException Locked="false" Priority="52"<br />\r\n   Name="List Table 7 Colorful Acc
ent 6"/></w:LatentStyles></xml><![endif]--><!--[if gte mso 10]><style> <br />\n
/* Style Definitions */<br />\r\n table.MsoNormalTable<br />\r\n        {mso-sty

obsigna · Apr 12, 2014

beesatmsu said:
the offending line it complained about:

Name="List Table 6 Colorful Accent 6"/><w:LsdException Locked="false" Priority="52"<br />\r\n Name="List Table 7 Colorful Acc
ent 6"/></w:LatentStyles></xml><![endif]--><!--[if gte mso 10]><style> <br />\n
/* Style Definitions */<br />\r\n table.MsoNormalTable<br />\r\n {mso-sty[/code]

That is the line with the close comment sequence ... Style Definitions*/<br />

beesatmsu · Apr 12, 2014

I tried downloading it by ftp (binary transfer)...
opened in notepad..

no hard returns, a big mess..I wont be able to know where to add end of line:
oops, when pasted here, it looked fine!

I tried to turn on "word wrap" and notepad died...(file too large? perhaps).

how do i fixed that error?

Code:

-- phpMyAdmin SQL Dump
-- version 3.4.7
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2014 年 04 月 11 日 14:05
-- 服务器版本: 5.1.54
-- PHP 版本: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- 数据库: `beedle`
--
/*CREATE DATABASE `beedle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `beedle`;

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

--
-- 表的结构 `adodb_logsql`
--

CREATE TABLE IF NOT EXISTS `adodb_logsql` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `sql0` varchar(250) NOT NULL DEFAULT '',
  `sql1` text,
  `params` text,
  `tracer` text,
  `timer` decimal(16,6) NOT NULL DEFAULT '0.000000',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='to save some logs from ADOdb' AUTO_INCREMENT=1 ;

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

--
-- 表的结构 `bdl_assignment`
--

CREATE TABLE IF NOT EXISTS `bdl_assignment` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `course` bigint(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `format` smallint(4) unsigned NOT NULL DEFAULT '0',
  `assignmenttype` varchar(50) NOT NULL DEFAULT '',
  `resubmit` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `preventlate` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `emailteachers` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `var1` bigint(10) DEFAULT '0',
  `var2` bigint(10) DEFAULT '0',
  `var3` bigint(10) DEFAULT '0',
  `var4` bigint(10) DEFAULT '0',
  `var5` bigint(10) DEFAULT '0',
  `maxbytes` bigint(10) unsigned NOT NULL DEFAULT '100000',
  `timedue` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timeavailable` bigint(10) unsigned NOT NULL DEFAULT '0',
  `grade` bigint(10) NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `bdl_assi_cou_ix` (`course`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Defines assignments' AUTO_INCREMENT=1 ;

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

--
-- 表的结构 `bdl_assignment_submissions`

obsigna · Apr 12, 2014

beesatmsu said:
I tried downloading it by ftp (binary transfer)...
opened in notepad..

no hard returns, a big mess..I wont be able to know where to add end of line:

I tried to turn on "word wrap" and notepad died...(file too large? perhaps).

how do i fixed that error?

Windows expects CRLF line ending. You would need to convert it from Unix LF line ending before open it into Nodepad.

beesatmsu said:
oops, when pasted here, it looked fine!

Code:

/*CREATE DATABASE `beedle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Yes this looks fine, with exception of this bloodily misplaced comment sequence, remove this, and never put a comment sequence neither here nor at any other place anymore.

DROP the databases before importing by means of phpmyadmin.

beesatmsu · Apr 12, 2014

Thank you!

I deleted that line, the error again for the next line, so I deleted everything between "if and endif " and the <style> </style> also.

still getting the same error on another line:

ERROR 2005 (HY000) at line 1461: Unknown MySQL server host 'font-size:10.0pt;rnfont-family:"Times' (1)

there are too many font-size: 10 pt: there i did not find the right one to delete... the line number 1461 does not seem to match, as was last time.

why it would try to interpret 'font-size' as server host though?

beesatmsu · Apr 12, 2014

i deleted a 2nd instances of "Style Definitions"..

then I got an error similar to your last one...

Code:

ERROR 1064 (42000) at line 10227: 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 '*', 'keep-alive', 'cyberbee.net', 'http://cyberbee.net/class/index.php/Assignmen' at line 1

I deleted all the comments there...
tried restoring again...again, the same error.

Code:

ERROR 1064 (42000) at line 20110: 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 '*', 'keep-alive', 'beesmsu.cyberbee.net', 'http://beesmsu.cyberbee.net/gallery/i' at line 1

making progress since line 10227 to 20110 now?

but...i found it has 1,412,641 lines! 1 million lines? come on...

now my eyes hurt...deleted a lot of spams (10k lines?), now at 38421. I think this will never end!

Code:

ERROR 1064 (42000) at line 38421: 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 '$buttons[] = ''sup''' at line

beesatmsu · Apr 13, 2014

now the file size is 40meg instead of 234 meg...90% junk!
it puts me to sleep...do not know at the end it will work or not.

all the akismet stuff..."transient" stuff...I deleted them.

Code:

-rw-r--r--  1 root  user  234655770 Apr 12 20:22 bee.sql
-rw-r--r--  1 root  user  233926519 Apr 12 18:48 bee2.sql
-rw-r--r--  1 root  user  233827396 Apr 12 20:31 bee3.sql
-rw-r--r--  1 root  user  106837558 Apr 12 22:13 bee4.sql
-rw-r--r--  1 root  user   40119222 Apr 12 22:20 bee5.sql

obsigna · Apr 13, 2014

beesatmsu said:
now the file size is 40meg instead of 234 meg...90% junk!

I have to admit that I have no idea, what you are doing there. I asked you to delete 2 bytes, namely the comment sequence "/*" in front of that CREATE DATABASE directive, and you deleted 200,000,000 bytes. This does look like a misunderstanding of epic dimensions, doesn't it?

Well, I hope that you know what you are doing, good luck!

beesatmsu · Apr 13, 2014

well, it kept having the same error, each time referring to a different line.

i deleted mostly useless stuff.

now it is a bit more difficult because it says '*' is causing the problem..

Code:

ERROR 1064 (42000) at line 65117: 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 '*' at line 1

beesatmsu · Apr 13, 2014

f**K! now finally with a size of 39909793...no error!!

but......nothing was changed on the web....cry cry.

beesatmsu · Apr 13, 2014

Obsigna,

Thank you! I got some back...so this morning I started messing with it again.
1). Used phpmyadmin to drop some useless tables.
2). Found week11 dump was good. and it got the pages to Feb 20th.
3). Tried a few more recent dumps, no go.
4). Tried reediting the phpmyadmin exported file. this time careful to copy the correct ' (my keyboard does not even have the other one!).
5)...It worked! but only for one major site...my own lab page, it went back from Feb to 2012!

6)...So I restored the week11 again..everything went back to Feb20. I exported only my tables for my page..
7). Restored a dump made today, so the main site is working (April posts are there!).
8). Tried to restore my own...I got this error:

Did not find a duplicate...

Code:

password:
ERROR 1062 (23000) at line 40: Duplicate entry '2-1' for key 'PRIMARY'

All other web seems work fine, except my own!! Darn....
It seems my own web had some junk in it. Not sure why restoring the most recently made phpmyadmin (April 11 2pm) would push my site back to 2012?
I do not know a way to update my own page's tables without affecting the other. Restore seems to wipe out newer data...is there a way of import, instead of restore?

beesatmsu · Apr 13, 2014

Ok, I exported my outside server (on justhost.com) a copy using also phpmyadmin..how can theirs works fine? My own export did not work. Over there, I also only had bl_** tables, same as here. the only difference is I saved on their server and FTPed to mine here. My own phpmyadmin saved to my PC and I had to FTP to server...

So, now most of my stuff is back..a few entries in gallery lost...this can be relatively easily down (move photo back, upload again...for some reason without mysql knowing it is there, it won't see them even though they are on the server)! /var/resizes folder worked fine so my old blogs still work.

beesatmsu · Apr 14, 2014

It is still a mystery why most of the dumps were no good...

Zach

mysql restore...

beesatmsu

obsigna

Profile disabled

beesatmsu

obsigna

Profile disabled

beesatmsu

obsigna

Profile disabled

beesatmsu

beesatmsu

beesatmsu

obsigna

Profile disabled

obsigna

Profile disabled

obsigna

Profile disabled

beesatmsu

obsigna

Profile disabled

beesatmsu

obsigna

Profile disabled

beesatmsu

beesatmsu

beesatmsu

obsigna

Profile disabled

beesatmsu

beesatmsu

beesatmsu

beesatmsu

beesatmsu