By Fred75 on

Hello all,

Please pardon me if my question happens to be stupid, or if it has been asked by somebody else already...

Here is the situation I am in:

1. I need to install Drupal 7 so that it uses the MyISAM storage engine, because I know for sure my hosting provider does not support InnoDB.

2. When I try and install Drupal 7 on my own machine with InnoDB enabled on my MySQL server, the install.php script runs fine (but, of course, my database contains nothing but InnoDB tables).

3. When I change my my.ini file so that it reads:
-> default-storage-engine=MYISAM (line 84)
-> skip-innodb (line 165)
Here is the ugly error message I get on screen 3 of the installation procedure (prerequisites):
-> SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown storage engine 'InnoDB'

Question : Why doesn't Drupal default peacefully to the MyISAM engine when InnoDB is disabled?
Did I forget something really obvious?

Many thanks in advance for your answers! I am looking forward to building my first Drupal 7 website :)

Fred

⋅ Categories: Drupal 7.x

Comments

Fred75’s picture

Fred75 commented

Re-hello all,

Since everybody appeared to be asleep and I thought I did not have much to lose with a new installation running on a dev. server, I finally decided to try and tamper with Drupal's core in order to see if I could somehow solve the problem the hard way...

As a rule, this is not a good idea, I know.

Anyway, here is what I have done:
In includes>database>mysql>shema.inc, on line 84, I changed 'mysql_engine' => 'InnoDB' to 'mysql_engine' => 'MyISAM'.

Then, the install script ran without complaint, and I now find myself with a working Drupal 7 website relying on MyISAM tables.

I do not like to customize the core files, though, since this will probably cause problems when I want to update my website to a newer minor release.
Do we really need the mention of the storage engine to be hard-coded in schema.inc?

Anyway, thanks for any comments you might have on this issue.

Frederique

mccune’s picture

mccune commented

I just started using Uniform Server and ran into the same problem.

Managed to fix the problem without modifying Drupal core.

Here is what I did:
Open the following file: RootFolder\usr\local\mysql\my.ini

Comment out the following option (line: 58):
skip-innodb
So it becomes:
#skip-innodb

Restart UniformServer.

I also made the same change to the following files that you'll find in the same folder:
medium_my.ini (line: 55)
small_my.ini (line: 57)

Don't know if the original poster was also using Uniform Server but I hope this is helpful.

igster’s picture

igster commented

This was exactly my issue. Thanks for posting the solution!

Florian Gypser’s picture

Florian Gypser commented

hi there,

i have exactly the same problem when creating a field image in D7. since i am a starter could you kindly help me out telling me where i can find the my.ini file ; ))

thanks a lot !
florian

safwan’s picture

safwan commented

This worked perfectly, i am also using uniform server

ericduran’s picture

ericduran commented

This is indeed an issue with the lastest D7 version and MySQL 5.5.

The fix for this is pretty basic. You can either switch to the lastest Drupal 7.x-dev version or you can apply the patch over at #1136854: MySQL 5.5 breaks speedy testing which made it into Drupal 7.x 4 days ago.

Alternative you can wait till Drupal 7.9/10 which should include this fix and fixes the MyISAM issue.

Eric

raylit’s picture

raylit commented

It seems there is no solution. How does a working D7 site just turn up unfixable errors like this.

The website encountered an unexpected error. Please try again later.
Error messagePDOException: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB': SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 167 of /home/public_html/drupal/includes/lock.inc).

Np one seems to be able to solve this?

I have already upgraded to the latest dev version D7 October 10 with no solution. Can even get to the site now.

sankatha’s picture

sankatha commented

D7 supports transactions now in its DB abstraction layer and uses PDO. MyISAM storage engine does not support transactions so don't think you can use MyISAM in parallel with the D7 DB abstraction layer. Hope this helps

meganme’s picture

meganme commented

I am also getting the error - Failed to connect to your database server. The server reports the following message: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'.

I am somewhat of a newbie to Drupal but know my user/pass is correct but I can not get past the database setup screen. Any insight would be helpful.

Thanks!
Megan

ludo.r’s picture

ludo.r
Location Brussels
commented

I am also stuck in install with the same error

Failed to connect to your database server. The server reports the following message: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'.

What could be the cause of this problem?

I'm running Mysql 5.0.24a with PDO activated (Drupal 7.9).

EDIT : oops, it seems this topic is not exactly the same problem, i will create a new one.

jackokorn’s picture

jackokorn commented

Hi there. Did you ever find a solution to the install error:

SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'.

I'm stuck on the same error.

ludo.r’s picture

ludo.r
Location Brussels
commented

Did you check your mysql version?

Beware, "Client API version" is different than "server version"!

Server version must be 5.x

syednayab’s picture

syednayab commented

Thank you very much,

it helped!