How to setup inno db in mysql 5.5.8

Question

I'm trying to restore a database from an sql file through phpmyadmin import function. I got this error while trying to import the database:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 15 

Tried going around in my.ini, and done some editing:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = C:\wamp\bin\mysql\mysql5.5.8\data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = C:\wamp\bin\mysql\mysql5.5.8\data/
innodb_log_arch_dir = C:\wamp\bin\mysql\mysql5.5.8\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

But I got this error:

#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured) 

Tried running the show engines in mysql console, and found out that support for innodb is set to default. I didn't install mysql separately, installed it via wampserver. The default for the home dir and group dir is c:\mysql\data, but that directory doesn't exist. So what I did was to put:

C:\wamp\bin\mysql\mysql5.5.8\data

I'm not exactly sure if I'm doing the right thing. How do I do this properly? Please help. Thanks!

Answer 1

First thing first. The configuration you've made is irrelevant to the previous error shown in MySQL log.

Since version 5.5, MySQL changed the syntax of TYPE=INNODB to ENGINE=INNODB in DDL statements. This implies you need to review those affected SQL statements issued by your web application in order to rectify the problem.

I see you are using PHPMyAdmin. This package may not be updated to reflect the syntax change. Check out the latest release of PHPMyAdmin and upgrade it if possible.

Make sure there is no such parameter in the option file like my.cnf as follows:

skip-innodb

Otherwise, Innodb engine will be turned off even you have set some other parameters correctly.

Alternatively, to verify if InnoDB is the Default Storage Engine, you may issue the following command in MySQL client console to confirm whether InnoDB is available:

SHOW VARIABLES LIKE 'have_innodb';

If the result is NO, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one.

By the way, the last error message prompt you to check the configuration about MySQL socket which may be a good hint to find out what's wrong actually in MySQL options file. If you are so sure that you haven't got anything wrong in MySQL options file, you can try terminating all MySQL processes currently running at the background before you start up MySQL server again. This makes sure no other MySQL process keeps the local socket occupied.

Hope this helps!