Thread

Dear EE —

I’m trying to upgrade to 213 (from a 171 installation). I’ve tried every way I could understand to achieve this. Among many threads. I’ve gave a shot to this one as well (even though not directly related) — http://ellislab.com/forums/viewthread/186735/.

I keep getting the same error over and over again.

Error Number: 1075

Incorrect table definition; there can be only one auto column and it must be defined as a key

ALTER TABLE `exp_weblog_data` DROP KEY `entry_id`

Filename: updates/ud_200.php

Line Number: 1382

Working on a local server using MAMP.

Among other things, I’ve done:
- Increased memory limit on php.ini
- Repaired/Optimized tables
- Manually cleared up tables (security hashes, referrers)

I’ve even tried installing a fresh EE213 and using DataGrab to set-up entries from scratch, however that didn’t do the trick either.

I’m running on my last fuse and I’m hoping you guys can run your magic dust through this one as well : ) — I’m sure its an easy one.

Cheers.

— Valon.

Hi Valon,

Can you tell us what you tried from the related thread? Did you run suggested commands on your db like Greg suggested?

Hi John.

Thanks for the reply.

Yes I did run those commands in phpMyadmin, but they wouldn’t work as there’s no PRIMARY KEY on my tables. (?!)

Any other command I can run?

Hi John. Just a friendly bump : ) /// Any ideas on how to go about this error?

Any guidance/update would be appreciated.

Cheers.

— Valon.

Can anyone else help on this one as I’m stuck and have no logical options to proceed on?

Cheers.

— Valon.

Nothing? No “We’re checking on it” !?

I can’t remember EE support being this slow and unresponsive (!?).

— Valon.

====

Hey Valon—

I’m sorry your support thread wasn’t revisited sooner by one of us. We work down a list of support requests, and are just catching up on the weekend rush.

It sounds to me like your SQL server might be in strict mode, and that could cause a problem like this. Could you check a few things for me? In your CP, go to Admin > Utilities > SQL Manager. Paste here or include a screenshot of the contents of the SQL Info table, and then click on System Variables. Search for a variable which includes the word ‘strict’ in the name and paste the name and value of that variable here.

If your SQL server is indeed in strict mode, I would recommend turning that off and trying the upgrade again from the beginning. I’m keeping my eye on this thread to make sure we get this taken care of for you, Valon.

Kevin

Hi Kevin & thanks for the reply.

I am used to EE high standards of support & couldn’t settle/expect any less 😊 — Cheers for the support.

===

As for your request attached is SQL info table screenshot.

Also, the variable which includes the word ‘strict’ is: slave_exec_mode | STRICT

I guess the SQL server is in strict mode?

Thanks Kevin.

— Valon.

===

Hmm… no, that’s not the line I was looking for. Are there any other instances of the word “strict” on that page?

It looks like this is going to be a tough one to figure out. Just wanted to let you know that we’re working on it!

Kevin

Valon—

Thanks again for your patience on this! Just like in the thread you referenced above, with your original DB intact, could you please go to Admin > Utilities > SQL Manager and then the Query Form and type this command:

show indexes from exp_weblog_data

as well as this one:

show create table exp_weblog_data

...and post a screenshot of the output from those queries? This will help us troubleshoot the problem.

Also, are you able to run altering DB queries on the dev server without fear and can rollback to the working DB if required?

Kevin

Hi Kevin & thanks for the support.

Attached are both screenshots of the requested queries.

And, Yes I can rollback anytime to the working DB.

Looking forward to the solution on this one.

Cheers.

— Valon.

====

Valon—

I’m going to escalate this to see if I can get anyone else to reproduce this problem. I’ve run this upgrade 10 times with various DB configurations on my dev server and cannot force it to get the same error you’re getting.

While I’m escalating it, I want you to try one thing. Rollback your site so that your current version is the latest EE v1.7.1. Go back into the SQL Manager and run this query:

ALTER TABLE `exp_weblog_data` DROP KEY `entry_id`

Is the same bit of SQL code that was giving you an error during upgrade, so I want to see if it does the same just running it by itself. This might make it easier to isolate and test the problem than to have to upgrade and rollback the DB every time we need to test a potential solution.

If you don’t get an error upon running that query, then run this one again and post the screenshot of the output.

show indexes from exp_weblog_data

Also, is this on a server we could get access to with your permission or is this a locally-hosted environment?

Kevin

Hi Kevin —

After running query

ALTER TABLE `exp_weblog_data` DROP KEY `entry_id`

I received

MySQL ERROR:
Error Number: 1075
Description: Incorrect table definition; there can be only one auto column and it must be defined as a key
Query: ALTER TABLE `exp_weblog_data` DROP KEY `entry_id`

Hence, the indexes screenshot remains the same.

And, Yes I’m trying to upgrade this locally. But, if we can’t fix it here I can deploy it Live for you to have access on? Let me know if this is the route we should go.

Thanks Kevin.

Cheers.

— Valon.

If you could deploy it on a server we could get access to (but not your actual live site), that would be most appreciated. Do that and try the upgrade on that server yourself first and let us know the results. If the upgrade fails with the same error there, I’ll get some login information from you and get in there myself to see what’s going on.

Thanks, Valon.

Kevin

Hey Kevin —

Now I remembered as I had this on Live already and many of the errors I would receive were related to timeouts on php.ini (based on some the other threads I was reading). Does that make sense!?

Regardless, let me place this on a live/test server and see where I get to. I’ll post the findings.

Thanks for your patience Kevin.

— Valon.