troubleshooting Question

mysql 4.1 inc Eventum- Missing data - Failed restore

Avatar of Singnetsvc
Singnetsvc asked on
MySQL Server
12 Comments1 Solution552 ViewsLast Modified:
HI All,  Ok first of all i am new to both mysql and to this forum so bare with the stupidy.

Background-  Our company use mysql 4.1 to host two databases for our bug tracking system "Eventum" One of the sites ( database) is working fine but the other has missing data (issues to be percise). When we try to enter issues into the system, we recieve an error stating that "There was an error creating you issue". My first thought is that there is a problem with one of the tables in the database, as all the others seem to work fine. We have tried to repair the database with the mysql administrator program, but the database type InnoDB does not support this.  We do however create backups of these databases using the mysqldump program.

Problem- As stated, the data is missing and we have backups of the databases. We recreated the whole system from the begining so that we would not have to test these procedures on the live server. So we have installed mysql 4.1 and the administrator tools, setup a replica schema with the same name as the origional. ( configured our eventum webpage )  granted access rights to a user account called restore,( now we have the default webpage which we can access and change ) We then changed some fields, made a backup and tried to restore the test database using the following command  "mysqldump.exe -u root -p databasename< c:\databasename_db_20050819.sql.  This did not work, so we deleted the schema, created a replica again, did not add any data this time and tried to restore using the same process. A sample of the output is below

-- MySQL dump 10.9
--
-- Host: localhost    Database: databasename
-- ------------------------------------------------------
-- Server version       4.1.13a-nt-max

/*!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 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

So we have tried to restore a database which had data in it and one which did not using a backup of the database which had data in it. if you understand.  And the biggest problem is that we have been unable to restore any of our test databases.. I have been working on this for four days as it is extremely important we get it running, so any help would be appreciated, Oh and i am awarding generously for anyone who can help resolve this, hopefully today.

Regards, Alan
Systems Engineer

I thought i should add a print out of the mysqldump that was generated when trying to restore the backup onto a replica blank database
-- Table structure for table `eventum_user`
--

DROP TABLE IF EXISTS `eventum_user`;
CREATE TABLE `eventum_user` (
  `usr_id` int(11) unsigned NOT NULL auto_increment,
  `usr_grp_id` int(11) unsigned default NULL,
  `usr_customer_id` int(11) unsigned default NULL,
  `usr_customer_contact_id` int(11) unsigned default NULL,
  `usr_created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `usr_status` varchar(8) NOT NULL default 'active',
  `usr_password` varchar(32) NOT NULL default '',
  `usr_full_name` varchar(255) NOT NULL default '',
  `usr_email` varchar(255) NOT NULL default '',
  `usr_preferences` longtext,
  `usr_sms_email` varchar(255) default NULL,
  `usr_clocked_in` tinyint(1) default '0',
  PRIMARY KEY  (`usr_id`),
  UNIQUE KEY `usr_email` (`usr_email`),
  KEY `usr_email_password` (`usr_email`,`usr_password`),
  KEY `usr_grp_id` (`usr_grp_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `eventum_user`
--
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros