Mysql Can't create test file + 1067 Error

Question

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.

• Michael - sqlbot: Well, no. You still have exactly the same problem as before. Errcode: 2 means "no such file or directory" so either you do not in fact have a "C:\WebSerer\MySQL\data" directory (no "v" in the "Serer" is correct?) or you still have references to it without forward slashes or double backslashes in your configuration file, so MySQL still can't read it.
• Michael - sqlbot: Also it looks like you're playing with more than one version of MySQL Server. You said this was 5.5.30 but the timestamp warning (which you can disregard for now, since it's not stopping your server from starting -- it's just a warning) was not added until MySQL 5.6.6. Perhaps at this point you are not editing the correct configuration file.

Answer 1

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"

or

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

also remove the trailing slash

Please try it now...

• Michael - sqlbot: innodb = FORCE was a 5.1 option that would prevent the server from starting if the InnoDB plugin wouldn't initialize to keep it from falling back to built-in InnoDB. dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-36.html
• RolandoMySQLDBA: @Michael-sqlbot Never dealt with that issue before. Thank you for the education on it. I had gotten people to upgrade rather than using the plugin, thus never came across it. Again, thank you.
• Aviel Fedida: Hey Rolando, Thank you for your answer i tryed but i getting the same error using (net start mysql (1067) as above), And a bit different error using (mysqld --console (Edit my question with the new error output)), And about the my.ini file i was using tools.percona.com/wizard to generate the configuration file.

Answer 2

I'm not a Windows admin but a quick search turned up a couple of hints in the documentation.

Note that Windows path names are specified in option files using (forward) slashes rather than backslashes. If you do use backslashes, double them.

C:\Users\Omfsm\Desktop>perror 2
OS error code 2: No such file or directory
Win32 error code 2: The system cannot find the file specified.

• Aviel Fedida: Hey Muchael thank you for you answer, I was trying using double backslashes and single forward slashes none worked.

Answer 3

Folder Permissions

If the path is not your issue, and the data folder exists, you may be getting the error because MySql does not have access to write to the data folder. This can happen if the folder was moved, copied, etc.

Check the user MySQL runs as

Check the service (MySql or MariaDB) by running services.msc

Right click the service and choose "Properties" and then click on the "Log On" tab. You can see here that the service will run as NT SERVICE\MariaDB:

MariaDB Service Properties

Verify Folder Permissions Match

Now find the data folder in the Windows Explorer, right click it, and select "Properties"

Choose the "Security" tab and scroll down the list of "Group or User names" and make sure that you see a match for the account listed in the previous step - for example MariaDB.

If this is listed, and the account has Allow checked for Full control then you are done and the folder permissions are probably not your issue.

data folder security properties

Add Permissions

If there is not a match then you need to add permissions for the account the service will be running as. Click on the "Advanced" button.

On my system with a default MariaDB 10.8 install the Owner is set to SYSTEM and MariaDB has full access:

data folder advanced security

To add MariaDB click "Add" and then click "Select a principal" and in the text box use the same name from the service properties, eg: NT SERVICE\MariaDB

add user

Clicking "Check Names" should work and change the name to an underlined version meaning it found it:

user name found

After clicking OK select Full control on the previous dialog, and then click on OK to apply the new permissions.

Done

You can now try restarting the MySQL / MariaDB service again and hopefully this has solved your permissions issue.