Mysql Can’t create test file + 1067 Error

MySQLwindows

I have installed mysql-5.5.30-win32. All works fine until i create my own my.ini configuration file. When I am trying to start the service using CMD (I do run the cmd as administrator, and install the service before):

Error using mysqld --console

[Warning] Can't create test file C:\WebSerer\MySQL\data\x-PC.lower-test

[Warning] Can't create test file C:\WebSerer\MySQL\data\x-PC.lower-test mysqld: Can't change dir to 'C:\WebSerer\MySQL\data\' (Errcode: 2)

[ERROR] Aborting

[Note] mysqld: Shutdown complete

Error using NET START MySQL

The MySQL service is starting...

The MySQL service could not be started.

A system error has occurred.

System error 1067 has occurred.

The process terminated unexpectedly.

The configuration (my.ini) file:

[mysql]

# CLIENT #
port                           = 3306
socket                         = C:\WebSerer\MySQL\data\mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = C:\WebSerer\MySQL\data\mysql.sock
pid_file                       = C:\WebSerer\MySQL\data\mysql.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now                 = 1
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = C:\WebSerer\MySQL\data\

# BINARY LOGGING #
log_bin                        = C:\WebSerer\MySQL\data\mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

# INNODB #
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

# LOGGING #
log_error                      = C:\WebSerer\MySQL\data\mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = C:\WebSerer\MySQL\data\mysql-slow.log

I was looking the internet for solution for both of this errors. If anyone can help me please I will be very thankful, Thank you all and have a nice day.

Edit

This is the new Error using mysqld –console

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --ex
plicit_defaults_for_timestamp server option (see documentation for more details).

[Warning] Can't create test file C:\WebSerer\MySQL\data\iSession-PC.lower-t
est

[Warning] Can't create test file C:\WebSerer\MySQL\data\iSession-PC.lower-t
est

mysqld: Can't change dir to 'C:\WebSerer\MySQL\data\' (Errcode: 2 - No such file or directory)

[ERROR] Aborting

[Note] Binlog end

[Note] mysqld: Shutdown complete

Also i tryed to change the location of the data dir at the my.ini file, The new path for the datadir is located at my desktop, Now i have another different error i have seen previously when i remove the datadir line, This is the error:

**[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --ex
plicit_defaults_for_timestamp server option (see documentation for more details).**

**mysqld: File 'C:\WebSerer\MySQL\data\mysql-bin.index' not found (Errcode: 2 - No such file or directory)**

**[ERROR] Aborting**

**[Note] Binlog end**

**[Note] mysqld: Shutdown complete**

So as i understand, The first error was becouse of permission problems, There is mysql user inside the my.ini and i should change the mysql user permission so it can write, The second error is after i changed the location of the data folder to my desktop so it can write now, but now there is another error appear, If anyone can help me please with the permission to mysql user and the new error, and have a nice day.

Best Answer

I looked over the list of InnoDB options

You have one option that does not appear in the list of Startup Options : innodb

sysdate_is_now                 = 1
innodb                         = FORCE <---- No such option called innodb
innodb_strict_mode             = 1

I think you meant innodb_force_recovery instead. Before you use it, you need to know the values allowed it.

Here are the options from the MySQL Documentation on Forced Recovery

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the master thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the redo log roll-forward in connection with recovery.

With this value, you might not be able to do queries other than a basic SELECT * FROM t, with no WHERE, ORDER BY, or other clauses. More complex queries could encounter corrupted data structures and fail.

If corruption within the table data prevents you from dumping the entire table contents, a query with an ORDER BY primary_key DESC clause might be able to dump the portion of the table after the corrupted part.

The database must not otherwise be used with any nonzero value of innodb_force_recovery. As a safety

Try replacing those lines in my.ini:

sysdate_is_now                 = 1
# SRV_FORCE_IGNORE_CORRUPT 
innodb_force_recovery          = 1 
innodb_strict_mode             = 1

and run

net stop mysql
net start mysql

Give it a Try !!!

UPDATE 2013-02-22 21:29 EDT

@Michael-sqlbot had just pointed out to me that innodb is a MySQL 5.1-specific plugin option to force an all-or-nothing situation: If InnoDB plugin fails to start, mysqld just dies rather than use the built-in InnoDB.

Your solution would simply be to remove that line

sysdate_is_now                 = 1
innodb_strict_mode             = 1

and then restart mysql with

net stop mysql
net start mysql

UPDATE 2013-02-23 08:36 EDT

You may have to resort to putting all DOS paths in double quotes

datadir                        = "C:\WebSerer\MySQL\data"

datadir                        = "C:/WebSerer/MySQL/data"

also remove the trailing slash

Please try it now...

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

Mysql – How to move a MySQL data directory to an external hard drive

The new directory must be accessible by mysqld. In step 2, did you copy all ownership and permissions?

But probably you started mysqld as a user without permissions to the new directory? Note that it can't even do a cd: "Can't change dir".