Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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.

share|improve this question
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 Feb 23 at 20:19
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. – Michael - sqlbot Feb 23 at 20:24

2 Answers

up vote 1 down vote

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...

share|improve this answer
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 – Michael - sqlbot Feb 22 at 23:47
@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. – RolandoMySQLDBA Feb 23 at 2:21
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. – uBlankText Feb 23 at 7:21
up vote 0 down vote

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.
share|improve this answer
Hey Muchael thank you for you answer, I was trying using double backslashes and single forward slashes none worked. – uBlankText Feb 23 at 7:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.