Moving Site & Changing Databases

[Xarcell]

I recently left nightmare host and I'm now with arvixe. I backed of up files and db. Transferred my domain and created a new DB. When I try to import my sql file, it throws an error with avea and only imports the avea tables(4 of them). I haven't gotten around to using avea yet so the avea doesn't really matter.

Any help with this?

The error was:

CREATE TABLE  `smf_aeva_fields` (

id_field INT( 11 ) NOT NULL AUTO_INCREMENT ,
name VARCHAR( 100 ) NOT NULL DEFAULT  '',
TYPE VARCHAR( 20 ) NOT NULL DEFAULT  'text',
options TEXT NOT NULL ,
required TINYINT( 1 ) NOT NULL DEFAULT 0,
searchable TINYINT( 1 ) NOT NULL DEFAULT 0,
DESC TEXT NOT NULL ,
bbc TINYINT( 1 ) NOT NULL DEFAULT 0,
albums TEXT NOT NULL ,
PRIMARY KEY ( id_field )
) TYPE = MYISAM ;

MySQL said:

#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 'desc text NOT NULL,
bbc tinyint(1) NOT NULL default 0,
albums text NOT NULL,' at line 8

When you visit the domain, the error it shows on the webpage says the table smf_settings doesn't exist.

[excaliburj]

I think it may be objecting to the word "desc" (reserved word for DESCending).

May require use of backticks (like used around the table name) for that column name.

[Xarcell]

I think it may be objecting to the word "desc" (reserved word for DESCending).

May require use of backticks (like used around the table name) for that column name.

I have no idea what that means...

[excaliburj]

If you look at the query, there is a line:

Code Select Expand

DESC TEXT NOT NULL ,

That defines a column in the table named 'DESC' (most likely description). But 'DESC" is also a MySQL reserved word (used for DESCending order). So that is what might be casuing a problem with this query.

If so, one possible solution could be to edit the SQL to use backticks around the column name. Backticks are the key on a standard keyboard to the left of the number '1' on the top row. And you can see how they are used in the block you quoted around the table name - `smf_aeva_fields`

[CapadY]

Try the next query, I tested it:

Code Select Expand


CREATE TABLE  `smf_aeva_fields` (
`id_field` INT(11) AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL DEFAULT  '',
`type` VARCHAR(20) NOT NULL DEFAULT  'text',
`options` TEXT NOT NULL ,
`required` TINYINT(1) NOT NULL DEFAULT 0,
`searchable` TINYINT(1) NOT NULL DEFAULT 0,
`desc` TEXT NOT NULL ,
`bbc` TINYINT(1) NOT NULL DEFAULT 0,
`albums` TEXT NOT NULL ,
PRIMARY KEY (`id_field`)
) TYPE = MYISAM


[Xarcell]

@ excaliburj

I think I understand better now. Backticks on column names.

@ capady

Did now work. Still gave error.

I tried removing all aeva query's from the sql file but no luck with that either. I get an error with that, but looks like most tables are there, except for smf_settings.

[Xarcell]

Actually the whole page error says "Table 'xarcell_irdb.smf_settings' doesn't exist".

[CapadY]

And did you have a look via PHPMyAdmin if this table excist ?
Is it the right databasename ?

[Xarcell]

And did you have a look via PHPMyAdmin if this table excist ?
Is it the right databasename ?

No the table does not exist, I thought the slq file would create one?

The database name has changed from "irdb" to "xarcell_irdb". I used repair_setting.php to make sure this was changed properly.

[CapadY]

It look likes the backup you made isn't complete or it isn't restored completely.

Have a look in de .sql file if it is there and restore that table manualy using running a query in PHPMyAdmin