MySQL Bugs: #68584: MySQL 5.6 service gives error 1067 when modifying datadir

Bug #68584	MySQL 5.6 service gives error 1067 when modifying datadir
Submitted:	6 Mar 2013 10:47	Modified:	15 Mar 2013 9:41
Reporter:	david trillo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	datadir error 1067

Description:
I have a Windows XP SP3 with MySQL 5.5.28 installed.

I decided to upgrade to MySQL 5.6.10 GA, downloaded from the mysql.com.

I backed up my databases, my.ini file, and everything from MySQL 5.5.

I uninstalled the MySQL 5.5 server.

I installed MySQL 5.6.10 GA, and everything was working OK.

I stop the service to modify ONLY the datadir property, trying to change where my databases should be, like this:

#datadir="C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\data\"

datadir = "H:/mysql_db/data/"

With the original datadir, the server goes up.
With the new datadir, the server gives error 1067.

I try this / with \ with // with \\ , ending with / \ // \\ ... without success.

The MySQL 5.528 datadir was: datadir = "H:/mysql_db/data/"

The error log i got is:

2013-03-05 16:43:09 5236 [Note] Plugin 'FEDERATED' is disabled.
2013-03-05 16:43:09 5236 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld: Table 'mysql.plugin' doesn't exist
2013-03-05 16:43:09 5236 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-03-05 16:43:09 5236 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-05 16:43:09 5236 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-03-05 16:43:09 5236 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-05 16:43:09 5236 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-05 16:43:09 5236 [Note] InnoDB: Initializing buffer pool, size = 55.0M
2013-03-05 16:43:09 5236 [Note] InnoDB: Completed initialization of buffer pool
2013-03-05 16:43:09 5236 [Note] InnoDB: The first specified data file .\ibdata1 did not exist: a new database to be created!
2013-03-05 16:43:09 5236 [Note] InnoDB: Setting file .\ibdata1 size to 12 MB
2013-03-05 16:43:09 5236 [Note] InnoDB: Database physically writes the file full: wait...
2013-03-05 16:43:09 5236 [Note] InnoDB: Setting log file .\ib_logfile101 size to 48 MB
2013-03-05 16:43:10 5236 [Note] InnoDB: Setting log file .\ib_logfile1 size to 48 MB
2013-03-05 16:43:12 5236 [Note] InnoDB: Renaming log file .\ib_logfile101 to .\ib_logfile0
2013-03-05 16:43:12 5236 [Warning] InnoDB: New log files created, LSN=45781
2013-03-05 16:43:12 5236 [Note] InnoDB: Doublewrite buffer not found: creating new
2013-03-05 16:43:12 5236 [Note] InnoDB: Doublewrite buffer created
2013-03-05 16:43:12 5236 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-05 16:43:12 5236 [Warning] InnoDB: Creating foreign key constraint system tables.
2013-03-05 16:43:12 5236 [Note] InnoDB: Foreign key constraint system tables created
2013-03-05 16:43:12 5236 [Note] InnoDB: Creating tablespace and datafile system tables.
2013-03-05 16:43:12 5236 [Note] InnoDB: Tablespace and datafile system tables created.
2013-03-05 16:43:12 5236 [Note] InnoDB: Waiting for purge to start
2013-03-05 16:43:12 5236 [Note] InnoDB: 1.2.10 started; log sequence number 0
2013-03-05 16:43:12 5236 [Note] Recovering after a crash using H:/mysql_db/DTRILLOPH-bin
2013-03-05 16:43:12 5236 [Note] Starting crash recovery...
2013-03-05 16:43:12 5236 [Note] Crash recovery finished.
2013-03-05 16:43:13 5236 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 63fbe518-85ab-11e2-a529-e02a82cc0b34.
2013-03-05 16:43:13 5236 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-05 16:43:16 5236 [Note] IPv6 is not available.
2013-03-05 16:43:16 5236 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2013-03-05 16:43:16 5236 [Note] Server socket created on IP: '0.0.0.0'.
2013-03-05 16:43:16 5236 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

How to repeat:
Install MySQL 5.6.10 on Windows

Stop the service with NET STOP mysql56

Modify the datadir in the my.ini file

Try without success to start the service again with NET START mysql56

Thank you for the report.

Error log contains record:

> 2013-03-05 16:43:16 5236 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

Do you start it with datadir H:/mysql_db/data?

Please provide output of 

dir H:/mysql_db/data
dir H:/mysql_db/data/mysql

Please also check if system user, which is the user mysqld.exe is running as, has access to H:/mysql_db/data

Thank you very much for your help, but the MySQL cannot be started!

I copied the data dir created here (during installation): 

C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\data\

to 
h:\mysql_db\

having
h:\mysql_db\data

I haven't copy my data dir with my databases! I just wanted to have the server running with data dir pointing to h:\mysql_db\data.

I attached 2 images from the DOS command line with dir you aseked me. Everything is fine! 

I include also de my.ini file, that it is exactly the default one, except changing the datadir

How can i verified if system user, which is the user mysqld.exe is running as, has access to H:/mysql_db/data?

dir and my.ini file

Attachment: bug68584.zip (application/x-zip-compressed, text), 170.95 KiB.

Looks to me like David experienced a variation of what I also did here:
http://bugs.mysql.com/bug.php?id=67041

@Peter Laursen
Yes, i saw your bug info when searching for a solution.

What i cannot understand is why I cannot change the path where my data is whiout problems.

Thank you for the feedback.

I can not repeat described behavior.

Your error log contains:

> 2013-03-05 16:43:12 5236 [Note] Recovering after a crash using H:/mysql_db/DTRILLOPH-bin

While for option

> datadir = "H:/mysql_db/data"

This should be 2013-03-05 16:43:12 5236 [Note] Recovering after a crash using H:/mysql_db/data/DTRILLOPH-bin

Could you also please upload full error log file: it should contain path to the directory it tries to start with.

This is my Error log file.

2013-03-08 11:29:33 5916 [Note] Plugin 'FEDERATED' is disabled.
2013-03-08 11:29:33 5916 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
2013-03-08 11:29:33 5916 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-08 11:29:33 5916 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-03-08 11:29:33 5916 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-08 11:29:33 5916 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-08 11:29:33 5916 [Note] InnoDB: Initializing buffer pool, size = 55.0M
2013-03-08 11:29:33 5916 [Note] InnoDB: Completed initialization of buffer pool
2013-03-08 11:29:33 5916 [ERROR] InnoDB: read can't be opened in .\ibdata1 mode
2013-03-08 11:29:33 5916 [ERROR] InnoDB: The system tablespace must be writable!
2013-03-08 11:29:33 5916 [ERROR] Plugin 'InnoDB' init function returned error.
2013-03-08 11:29:33 5916 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2013-03-08 11:29:33 5916 [ERROR] Unknown/unsupported storage engine: INNODB
2013-03-08 11:29:33 5916 [ERROR] Aborting

2013-03-08 11:29:33 5916 [Note] Binlog end
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'partition'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_METRICS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_CMP'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'INNODB_TRX'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'BLACKHOLE'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'ARCHIVE'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'MRG_MYISAM'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'MyISAM'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'MEMORY'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'CSV'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'sha256_password'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'mysql_old_password'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'mysql_native_password'
2013-03-08 11:29:34 5916 [Note] Shutting down plugin 'binlog'
2013-03-08 11:29:34 5916 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld: Shutdown complete

After reading the error log file, i decided to remove all files from h:\mysql_db\data but left the directories (directories are exactly the same as C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\data

Now all InnoDB file is OK, but the server continues giving error 1067.

The error log file now is:
2013-03-08 11:41:08 3464 [Note] Plugin 'FEDERATED' is disabled.
2013-03-08 11:41:08 3464 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
2013-03-08 11:41:08 3464 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-08 11:41:08 3464 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-03-08 11:41:08 3464 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-08 11:41:08 3464 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-08 11:41:08 3464 [Note] InnoDB: Initializing buffer pool, size = 55.0M
2013-03-08 11:41:08 3464 [Note] InnoDB: Completed initialization of buffer pool
2013-03-08 11:41:08 3464 [Note] InnoDB: The first specified data file .\ibdata1 did not exist: a new database to be created!
2013-03-08 11:41:08 3464 [Note] InnoDB: Setting file .\ibdata1 size to 12 MB
2013-03-08 11:41:08 3464 [Note] InnoDB: Database physically writes the file full: wait...
2013-03-08 11:41:08 3464 [Note] InnoDB: Setting log file .\ib_logfile101 size to 48 MB
2013-03-08 11:41:10 3464 [Note] InnoDB: Setting log file .\ib_logfile1 size to 48 MB
2013-03-08 11:41:11 3464 [Note] InnoDB: Renaming log file .\ib_logfile101 to .\ib_logfile0
2013-03-08 11:41:11 3464 [Warning] InnoDB: New log files created, LSN=45781
2013-03-08 11:41:11 3464 [Note] InnoDB: Doublewrite buffer not found: creating new
2013-03-08 11:41:11 3464 [Note] InnoDB: Doublewrite buffer created
2013-03-08 11:41:11 3464 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-08 11:41:11 3464 [Warning] InnoDB: Creating foreign key constraint system tables.
2013-03-08 11:41:11 3464 [Note] InnoDB: Foreign key constraint system tables created
2013-03-08 11:41:11 3464 [Note] InnoDB: Creating tablespace and datafile system tables.
2013-03-08 11:41:11 3464 [Note] InnoDB: Tablespace and datafile system tables created.
2013-03-08 11:41:11 3464 [Note] InnoDB: Waiting for purge to start
2013-03-08 11:41:11 3464 [Note] InnoDB: 1.2.10 started; log sequence number 0
2013-03-08 11:41:12 3464 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b1d1379d-87dc-11e2-b375-e02a82cc0b34.
2013-03-08 11:41:12 3464 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-08 11:41:14 3464 [Note] IPv6 is not available.
2013-03-08 11:41:14 3464 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2013-03-08 11:41:14 3464 [Note] Server socket created on IP: '0.0.0.0'.
2013-03-08 11:41:14 3464 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2013-03-08 11:41:14 3464 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2013-03-08 11:41:14 3464 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2013-03-08 11:41:14 3464 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2013-03-08 11:41:14 3464 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
2013-03-08 11:41:14 3464 [ERROR] Event Scheduler: Failed to open table mysql.event
2013-03-08 11:41:14 3464 [ERROR] Event Scheduler: Error while loading from disk.
2013-03-08 11:41:14 3464 [Note] Event Scheduler: Purging the queue. 0 events
2013-03-08 11:41:14 3464 [ERROR] Aborting

2013-03-08 11:41:14 3464 [Note] Binlog end
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'partition'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_METRICS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_CMP'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'INNODB_TRX'
2013-03-08 11:41:14 3464 [Note] Shutting down plugin 'InnoDB'
2013-03-08 11:41:14 3464 [Note] InnoDB: FTS optimize thread exiting.
2013-03-08 11:41:14 3464 [Note] InnoDB: Starting shutdown...
2013-03-08 11:41:15 3464 [Note] InnoDB: Shutdown completed; log sequence number 1600607
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'BLACKHOLE'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'ARCHIVE'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'MRG_MYISAM'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'MyISAM'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'MEMORY'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'CSV'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'sha256_password'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'mysql_old_password'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'mysql_native_password'
2013-03-08 11:41:15 3464 [Note] Shutting down plugin 'binlog'
2013-03-08 11:41:15 3464 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld: Shutdown complete

If I try lo start the server like this, the server starts without problems:

On a cmd i write:

mysqld --default-file="C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\my.ini" --console --explicit_defaults_for_timespamp

What is happening when trying to start as a service?

I have exactly the same problem on
------------------------------------
Windows 7 and Windows Server 2008 R2
------------------------------------
using mysql-installer-community-5.6.10.1.msi.

All worked OK with 5.6.9.
For upgrade I uninstalled 5.6.9 completely and installed 5.6.10.
The service started and everything was working OK.

Now I stop the service to modify the data dir location.
I copied the data dir to the new location, e.g. from C: to D:
and changed in my.ini only
datadir="C:\ProgramData\MySQL\MySQL Server 5.6\data\"
to
datadir="D:\ProgramData\MySQL\MySQL Server 5.6\data\".

When I start the service again, I get the error 1046.

Error 1045 or 1067?
The case is i have a VM empty, i mean, with no mysql server. i will install the 5.6.10 version and modify the datadir to see if it's a general bug!

Sorry, the night was late.
I checked on Windows 7 again.

The error is 1067, in german "Der Prozess wurde unerwartet beendet".

I did the install process on a Vistual Machine using VMWare. 
I use a Windows XP SP3 empty, that's it: nothing installed.

I installed MySQL 5.6.10 and everything works OK!

I stop the service.

I copy the data dir to c:\datadir

Change de datadir property to c:\datadir

Start the service again!

Error 1067 one more time!

That confirms that there is a bug!!

Thank you for the feedback.

This is the cause of the problem:

> On a cmd i write:
>
> mysqld --default-file="C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\my.ini" --console --explicit_defaults_for_timespamp
>
> What is happening when trying to start as a service?

You make changes in wrong my.ini See http://dev.mysql.com/doc/refman/5.6/en/option-files.html for default location of configuration files.

Alternatively you can pass this configuration file as parameter for the service.

To [12 Mar 12:26] Sveta Smirnova 
At least im my case i work with the correct my.ini,
on windows C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.
Reasons:
When i don't change the datadir but set in my.ini
1. ssl mode, including the names and locations of certificates,
2. change max_allowed_packet
this is accepted when i start the service an all works OK (SSL!).

I write on the right my.ini file.

The service is created like this:
"C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\my.ini" MySQL56

And THIS is the file iI'm changing!

Finally, i got WHY was this error happening!

When creating the service, It used a user like NTblablabla, instead of a local user.

After changing this, everything was OK!

i'm happy to find the solution. It wasn't the datadir, but how the service was created (the user)!

i hope this can be useful to other users!

Thanks to david trillo. We solved the problem also.

It was not a matter of correct my.ini.
It was a matter of user rights of service and data dir.

Let me describe it for Windows Server 2008 R2, which was the last OS i used.
I am always logged in as administrator, but that is not the point.

Important is:
The installer installs the service by default for the user: "network service"
(see properties of the service MySQ56).
If we copy the data dir to a new location (e.g. C: to D:) it has by default
the security rights of the user: "local system"
(see properties of the the new data dir).
---------------------------------------------------------
These different user/rights are the reason for 1067 here.
---------------------------------------------------------

Now we have at least 2 choices:
1. change the service user to: "local system" (as david trillo did)
or
2. add to the data dir security properties the user: "network service",
with all rights.

Both solution work OK when the service is started!

On other OS the problem should be solved analogous.
I hope this is helpfull for others.
Thanks to david trillo again.

I'm happy that at least, one user has needed to use my solution!!

david trillo - you helped at least two users. Thanks!

Great!!

Wichen Moormann thanks you!
after i guaranty to the D drive where the mysql folder installed  
Full Control for the NETWORK_ACCOUNT it's workd!

yonathan garti FINE!
Thanks for your reply.

Thank's a lot for the correct pointer, Its the user of the service that causes the issue.On windows, just right click on the  mysql service >>properties>>logon tab>>select Local System Account radio button >>apply >>ok
You are good to go....)

Thank you David, I just spent 4 hours trying to figure this out.  Thought I was going mad.  Cheers!