MySQL error - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMEST

awebhost

When I install Vanilla 2.5.2 ony my server, I have met the error: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMEST

How To solve it?

I used mysql 5.0/ php 5.6 /apache on my server

whu606

@awebhost

Welcome to the community.

Please see this thread: https://open.vanillaforums.com/discussion/36296/incorrect-table-definition-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default

awebhost

but I still can't find the answer in this link @whu606

awebhost

pls just write the solve way in the comment.

R_J

Your problem is your MySQL version: https://open.vanillaforums.com/discussion/comment/252824/#Comment_252824

awebhost

@R_J 说道：
Your problem is your MySQL version: https://open.vanillaforums.com/discussion/comment/252824/#Comment_252824

but I use mysql 5.5 / php 5.6 . it still shows me the error.
Does mysql 5.6 work?

R_J

Why don't you simply look at the links provided?

jray

I'm having the same problem. The Readme file says minimum requirement is MySQL 5.0. Is there a workaround besides upgrading MySQL?

I'm on a shared server and can't upgrade. My server is using version 5.5.51-38.2.

R_J

@jray: Upgrading MySQL is the best way. Can't you ask your hoster to do the upgrade?

There is a workaround, but that's a) ugly and b) possibly breaking functionality.

It is ugly because you have to hack the core files and you need to change the structure of the AccessToken table, but I hope it is only needed for API actions right now.

Open the file applications/dashboard/settings/structure.php and search for the following code:

$Construct
    ->table('AccessToken')
    ->primaryKey('AccessTokenID')
    ->column('Token', 'varchar(100)', false, 'unique')
    ->column('UserID', 'int', false, 'index')
    ->column('Type', 'varchar(20)', false, 'index')
    ->column('Scope', 'text', true)
    ->column('DateInserted', 'timestamp', ['Null' => false, 'Default' => 'current_timestamp'])
    ->column('InsertUserID', 'int', true)
    ->column('InsertIPAddress', 'ipaddress', false)
    ->column('DateExpires', 'timestamp', ['Null' => false, 'Default' => 'current_timestamp'])
    ->column('Attributes', 'text', true)
    ->set($Explicit, $Drop);

The problem is, that there are two lines which default to current_timestamp. So make the second one a fixed date, which will make the default value an expired token. From my understanding, this field is set whenever a new token is saved, so there should be no change in Vanillas funtionality, but you never know...

$Construct
    ->table('AccessToken')
    ->primaryKey('AccessTokenID')
    ->column('Token', 'varchar(100)', false, 'unique')
    ->column('UserID', 'int', false, 'index')
    ->column('Type', 'varchar(20)', false, 'index')
    ->column('Scope', 'text', true)
    ->column('DateInserted', 'timestamp', ['Null' => false, 'Default' => 'current_timestamp'])
    ->column('InsertUserID', 'int', true)
    ->column('InsertIPAddress', 'ipaddress', false)
    ->column('DateExpires', 'timestamp', ['Null' => false, 'Default' => Gdn_Format::ToDateTime()])
    ->column('Attributes', 'text', true)
    ->set($Explicit, $Drop);

x00

@jray said:
I'm having the same problem. The Readme file says minimum requirement is MySQL 5.0. Is there a workaround besides upgrading MySQL?

I'm on a shared server and can't upgrade. My server is using version 5.5.51-38.2.

That is why competition exists.

Shared server companies with the typical ftp/cpanel only envirment, offer poor value for money and even poorer value for money "upgrades" they try to push during their terrible technical support even though it likely won't solve the problem. Also other questionable practices such as throttling so called "unlimited" deals, brandishing and array of mostly useless software you could install for free tog et you to sign up, and encouraging insecure file management practices such as chmod 777.

For the same kind of money you could likely get something better with more control over your environment. It might require a little bit of a learning curve but that come with being a web admin.

These companies are on the way out, well the ones that fail to adapt anyway.

whu606

I'm with @x00 on this.

Hosted services are handy for just starting out.

It's how I got into things when I first started a website, but once I started our forum, I found that swapping to a VPS was cheaper and faster.

It is a learning curve, but there are tons of tutorials to get you going, and it's far better to have full control, imo.

R_J

You can set up a forum on your own vps without knowing anything about security which is a risk for you and your users. I agree that using a vps is the better approach, but i wouldn't advise to use it for the live forum before you know what you are doing, although it will be tempting when you feel the performance advantages

whu606

@R_J

I didn't know anything about it (other than that security was an issue) but the tutorials for setting up a VPS (at least for my provider) were excellent, and very clear about the need for, and how to, secure the server.

jray

Thank you. I installed 2.3 last night just to get into the software and start playing with it. I am going to look into the VPS option (I have coworkers who could help me with that), but I don't really want to spend $20+ per month when I'm just getting started.

Is 2.5 going to have the same MySQL requirements as 2.5.2? I'm assuming yes, but thought I'd see if that's an option to get a newer version while I wait for my hosting provider to upgrade.

whu606

@jray

A 1Gb RAM VPS will cost you $5 /mo (plus tax if you are outside the USA), cheaper, I would imagine, than your current provider. I have an active forum, and that is more than enough for our needs.

The one I use charges by the hour for the time a droplet is available, so you could spend a week exploring how things work, destroy the droplet at the end if you want, and have spent a dollar.

R_J

Yes, the table definition that is causing the above error message for older MySQL versions is already in Vanilla 2.5

By the way: I pay less than 5 Euro/Month for a virtual server and that's the normal price for a "small" server. Here is a German web page which compares web hosters and their services and what you see is a search for a small virtual server. Just to give you an impression of the prices...

jray

Thanks, I'll check that out. I looked at my current company and a few other popular ones and it looked like $20/mo was the least expensive option.