SQL Syntax

[davy_yg]

Hallo,

This is funny. I export mysql codes to the webserver. Yet, I cannot run the codes correctly, there are some errors when I run the codes:

Error
SQL query:

--
-- Database: `project_infracom`
--
-- --------------------------------------------------------
--
-- Table structure for table `infracom_admin`
--
CREATE TABLE IF NOT EXISTS `infracom_admin` (

`admin_ID` bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
`admin_login` varchar( 70 ) NOT NULL DEFAULT '',
`admin_pass` varchar( 70 ) NOT NULL DEFAULT '',
`admin_token` varchar( 70 ) NOT NULL DEFAULT '',
`admin_nicename` varchar( 60 ) NOT NULL DEFAULT '',
`admin_email` varchar( 100 ) NOT NULL DEFAULT '',
`admin_url` varchar( 100 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `admin_ID` ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT =4;

MySQL said:

1146 - Table 'project_infracom.infracom_admin' doesn't exist

-----------------

How to fix it?

[cereal]

Hi,

drop the table and retry. If it does not work check the status:

show engine innodb status\G

And verify if there are errors, you could also perform check table infracom_admin but this can stop the server, read the documentation before you try. Your issue can happen if you move the data files from a server to another. Refer to:

• https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html
• https://dev.mysql.com/doc/refman/5.6/en/check-table.html
• https://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html
• http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

[davy_yg]

There is no table in project_infracom. I already create the database and try to import the table into the database.

I try to check the status: show engine innodb status\G

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 '\G' at line 1

[cereal]

Hi, I understand the table does not exists, I was suggesting to drop it anyway to force the system to delete the data file, or at least to see if this command would make any difference. The problem happens because you have manually moved the .ibd and .frm files to the database directory:

/var/lib/mysql/project_infracom/

But the id of the table will change in the new database and here starts the corruption. The simplest solution is to manually remove the ibd file from the database directory, so remove infracom_admin.ibd and if it still does not work remove also the frm file. And everything should work fine.

If you perform show tables you can probably see infracom_admin, but if you try to do any action the server will reply that it does not exists, in case the frm file is missing, then you not even see the table name in the result set, but the presence of the ibd file blocks any attempt to create the new table.

Check the documentation to understand how you can move the ibd files between databases, until MySQL 5.5 you have to use the RENAME command, starting from MySQL 5.6 these files are managed differently:

• http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
• http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

Take especially attention to the 5.5 link, check the last paragraph Portability Considerations for .ibd Files it explains how to perform these operations.

Regarding \G this is only a method to display the results vertically, when you open a connection to the MySQL monitor client, in the first line you should see something like:

Welcome to the MySQL monitor. Commands end with ; or \g.

So, you can replace \G with the usual ;.

Docs: https://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html

[rch1231]

You have to tell the server which database to use to create the table. Add the following in front of your sql code:

USE project_infracom;

if that does not work try creating the database as the first line:

CREATE database project_infracom;

[davy_yg]

after I DROP the project_infrac DATABASE and recreate it. Then, I type USE project_infracom the first table syntax works. Yet, now I have another error:

Error
SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `infracom_links`
--
CREATE TABLE IF NOT EXISTS `infracom_links` (

`link_ID` bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
`link_title` text NOT NULL ,
`link_description` text NOT NULL ,
`link_class` varchar( 200 ) NOT NULL DEFAULT '',
`link_url` varchar( 200 ) NOT NULL DEFAULT '',
`link_color` varchar( 200 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `link_ID` ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT =4;

MySQL said:

1146 - Table 'project_infracom.infracom_links' doesn't exist

[davy_yg]

How to fix the SQL Syntax so that everything works perfectly?

[cereal]

The problem seems to be the same of the previous, but it targets a different table. If so it does not seem to be a syntax error.

Can you explain us how do you export and import your SQL files? Are you copying the ibd and frm files as I was supposing in my previous post?

[davy_yg]

I export and import the _.sql file instead of copying files from the data/ files. Which is better?

[cereal]

Moving the .sql files is the easiest method. But if you encounter such problems there's must be something that interferes with your action.

Have you checked the error log of MySQL?

Can you access the database directory in your server? If yes, you should see infracom_links.ibd and infracom_links.frm by manually removing them you should be able to create the table.

Also this seems related to your problem:

• http://bugs.mysql.com/bug.php?id=66211
• https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

[davy_yg]

Hello,

I change the method of moving mysql files by copying data/project_infracom

infracom_admin.frm
infracom_links.frm
infracom_pages.frm
infracom_settings.frm
db.opt

to the web server, yet I still have some errors even after I copy paste the database: http://www.indonusa.net.id/infracom/

and when I check the database in phpmyadmin I only see:

database: project_infracom

with no tables.

[davy_yg]

I finally find the answer. It's XAMPP error. Everything works fine after I reinstall the XAMPP to a newer version.