MySQL corrupts database. on reboot

[EchoSeven]

hello. my MySQL database corrupts the database when ever i start, stop, or restart the service. i am on a laptop.. and this is for testing.. so, i have to stop the server to be able to do other kinds of work and take my laptop to school, work, and back. so, i can't just leave it on a lot.. so i need help.

the problem: when i start the MySQL server, and then go to phpmyadmin, i see the database and there is a folder in the mysql server data directory with the database's name on it... but i am unable to access the tables or even delete the database. all the default databases (that came with the mysql server.. like "mysql", "schemas", "test", and "cd collection") all work.. but all of mine get corrupted and unusable. i don't know if i can recover them.. and it sucks having to back them up every time i change something, then restore them every time i turn the server on. i have the same version of mysql (though, my laptop is windows 7, and my server is debian linux) on my server.. and it doesn't mess up at all. i have XAMPP on both my server and my laptop. both are the most current versions i believe. if there's any other thing you need to know, please ask.

[rch1231]

Hello,

Based on what you are saying I believe the issue is with how you are starting mysql. You should be using the script which is normally in /etc/init.d to start the database as it sets several variables telling the sysem where to store the data etc.

[root@hpsrvr ~]# ls -la /etc/init.d/mysql
-rwxr-xr-x 1 root root 6305 Apr  9 11:28 /etc/init.d/mysql

and you would start it with something like:

/etc/init.d/mysql start

or

service mysql start

If you are just running the server from the command line then you would see issues like the ones you are describing. NOTE I believe debian uses the same name for the server as the client (both called mysql) where on redhat releases (Red Hat, CentOS, Fedora, etc.) is is called mysqld ( for mysql daemon) and you use:

/etc/init.d/mysqld start

or

service mysqld start

Hope I guessed correctl if not can you tel us how you start the server.

[rch1231]

I read that wrong and thought you were having trouble with the debian installation. But I still come back to how are you starting mysql. Make sure that you run the startup script for XAMP as the adminstrator ( right click the Icon and sleect "Run as administrator") so that it has the proper permissions to acces the data base files.

[EchoSeven]

i'll try that right now.

it did the same thing. no luck

[EchoSeven]

I can't replace the database.. it gives this error when trying to replace a simple DB for a Minecraft authentication server using ComputerCraft.

SQL query:




--
-- Database: `Minecraft`
--

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

--
-- Table structure for table `Users`
--

CREATE TABLE IF NOT EXISTS `Users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `password` varchar(512) NOT NULL,
  `accesslevel` int(11) NOT NULL,
  `DateCreated` datetime NOT NULL,
  `LastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
MySQL said: Documentation

#1813 - Tablespace for table '`minecraft`.`users`' exists. Please DISCARD the tablespace before IMPORT. 

also, the ONLY configuration i did to the server at all, was add "lower_case_table_names = 2" to turn off lowercase table names. i also did this on the production server and it works perfectly. its the default XAMPP config with just that one line in the MySQL config. i also, ran the security script to put a password on the database.. but all that did is put a password on the root and pma users.. i'm pretty sure that was all it did to the database.

[EchoSeven]

on another forum, i was told to try to put "innodb_file_per_table" in the config. i did this and deleted the ib_logfile0, ib_logfile1, and ibdata1 files. it let me actually delete the database from the MySQL interface. though, once i added the database back to check if everything was fixed, it did the same thing, and went back to being corrupted and unable to be removed.. giving this error:

 #1010 - Error dropping database (can't rmdir '.\minecraft', errno: 41)

also added:

character-set-server=utf8
collation-server=utf8_unicode_ci

to my config. didn't help, but it was worth a try.

also, something i noticed. when deleting the corrupted files, it saves as users.ibd and the one i can access is saved as Users.frm if that means anything...

[rch1231]

Try starting your script like this instead:

USE minecraft;
DROP TABLE IF EXISTS `Users`;
CREATE TABLE `Users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(40) NOT NULL,
`password` varchar(512) NOT NULL,
`accesslevel` int(11) NOT NULL,
`DateCreated` datetime NOT NULL,
`LastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[EchoSeven]

didn't work

[EchoSeven]

innodb_force_recovery = 1

???