sql error

ataarticles · Nov 6, 2010

Hello,

website: http://ataartic.x10hosting.com/

Cannot upload sql database to x10 as it keeps showing 'information schema' errors:

SQL query:

--
-- Database: `information_schema`
`information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

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 '`information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' at line 3

It mentions line 3, but referring to the sql code, not sure what is wrong here:

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

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- Host: localhost
-- Generation Time: Oct 30, 2010 at 03:53 PM
-- Server version: 5.1.50
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `information_schema`
`information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `information_schema`;

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

--
-- Table structure for table `CHARACTER_SETS`
--

CREATE TEMPORARY TABLE `CHARACTER_SETS` (
`CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
`DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '',
`DESCRIPTION` varchar(60) NOT NULL DEFAULT '',
`MAXLEN` bigint(3) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

--
-- Dumping data for table `CHARACTER_SETS`
--

INSERT INTO `CHARACTER_SETS` (`CHARACTER_SET_NAME`, `DEFAULT_COLLATE_NAME`, `DESCRIPTION`, `MAXLEN`) VALUES
('big5', 'big5_chinese_ci', 'Big5 Traditional Chinese', 2),
('dec8', 'dec8_swedish_ci', 'DEC West European', 1),
('cp850', 'cp850_general_ci', 'DOS West European', 1),

Line 3 shows as: -- Host: localhost which seems ok. Maybe doesn't like the different php versions.

gomarc · Nov 6, 2010

Hi ataarticles,

It’s most likely you do not need to restore the information_schema database.

Are they other databases you need to restore from the sql backup?

If so, you can try the solution proposed here: http://x10hosting.com/forums/programming-help/139191-cant-restore-mysql-database.html#post751366, assuming you have a database to spare.

ataarticles · Nov 7, 2010

Hi,
Referring to your old posts on replacing Code:

CREATE DATABASE `information_schema` ... ;
USE `information_schema`;
And change them to something like:

Code:
CREATE DATABASE `old_schema` . . . ;
USE `old_schema`;

... i replaced all information_schema to 'old schema' with Editplus. It replace hundreds, and tried to upload again.
Only this time it came up with similar error but as 'old schema'

SQL query:

--
-- Database: `old_schema`
`old_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

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 '`old_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' at line 3

It is fairly small database of about 30 plus articles from old domain: http://organicgardeninglog.com/

misson · Nov 7, 2010

As the error says, "`databaseName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;" isn't a valid SQL statement. To set the default character set for a database, use the ALTER DATABASE statement. Basically, prefix the problematic statement with ALTER DATABASE and it should fix the syntax error.

However, as gomarc points out, you don't need to restore `information_schema` as it's a system database. You shouldn't even have appropriate privileges to alter `information_schema` nor to create tables in it, so the statements will most likely fail even after fixing the syntax error. Delete the section that deals with `information_schema` and you'll be fine.

ataarticles · Nov 14, 2010

Hi,
Ok, tried to replace

-- Database: `information_schema`
--
CREATE DATABASE `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `information_schema`;

WITH:

-- Database: `ALTER DATABASE`
--
CREATE DATABASE `ALTER DATABASE;
USE `ALTER DATABASE`;

Only this time, there was an issue with the execution time:

Fatal error: Maximum execution time of 300 seconds exceeded in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/string_mb.lib.php on line 48

Maybe that worked, but x10hosting have limited execution time. But unsure though.

However, i tried to just remove information_schema section but that gave a database does not exist error. Hence, replaced it with 'ALTER DATABASE'.

---------- Post added at 05:31 PM ---------- Previous post was at 04:26 PM ----------

Just to be sure, do i need to replace this section with 'ALTER DATABASE':

Starts from line 455 to line 833
INSERT INTO `COLUMNS` (`TABLE_CATALOG`, `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `ORDINAL_POSITION`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `DATA_TYPE`, `CHARACTER_MAXIMUM_LENGTH`, `CHARACTER_OCTET_LENGTH`, `NUMERIC_PRECISION`, `NUMERIC_SCALE`, `CHARACTER_SET_NAME`, `COLLATION_NAME`, `COLUMN_TYPE`, `COLUMN_KEY`, `EXTRA`, `PRIVILEGES`, `COLUMN_COMMENT`) VALUES
(NULL, 'information_schema', 'CHARACTER_SETS', 'CHARACTER_SET_NAME', 1, '', 'NO', 'varchar', 32, 96, NULL, NULL, 'utf8', 'utf8_general_ci', 'varchar(32)', '', '', 'select', ''),
(NULL, 'information_schema', 'CHARACTER_SETS', 'DEFAULT_COLLATE_NAME', 2, '', 'NO', 'varchar', 32, 96, NULL, NULL, 'utf8', 'utf8_general_ci', 'varchar(32)', '', '', 'select', ''),
(NULL, 'information_schema', 'CHARACTER_SETS', 'DESCRIPTION', 3, '', 'NO', 'varchar', 60, 180, NULL, NULL, 'utf8', 'utf8_general_ci', 'varchar(60)', '', '', 'select', ''),
(NULL, 'information_schema', 'CHARACTER_SETS', 'MAXLEN', 4, '0', 'NO', 'bigint', NULL, NULL, 19, 0, NULL, NULL, 'bigint(3)', '', '', 'select', ''),
(NULL, 'information_schema', 'CLIENT_STATISTICS', 'CLIENT', 1, '', 'NO', 'varchar', 64, 192, NULL, NULL, 'utf8', 'utf8_general_ci', 'varchar(64)', '', '', 'select', ''),
(NULL, 'information_schema', 'CLIENT_STATISTICS', 'TOTAL_CONNECTIONS', 2, '0', 'NO', 'int', NULL, NULL, 10, 0, NULL, NULL, 'int(21)', '', '', 'select', ''),
(NULL, 'information_schema', 'CLIENT_STATISTICS', 'CONCURRENT_CONNECTIONS', 3, '0', 'NO', 'int', NULL, NULL, 10, 0, NULL, NULL, 'int(21)', '', '', 'select', ''),
(NULL, 'information_schema', 'CLIENT_STATISTICS', 'CONNECTED_TIME', 4, '0', 'NO', 'int', NULL, NULL, 10, 0, NULL, NULL, 'int(21)', '', '', 'select', ''),

....etc.

misson · Nov 14, 2010

Please use

Code:

, [php] or [html] tags as appropriate to delineate and format code (for SQL, [code] is most appropriate).

You seriously need to study SQL syntax and what these statements mean, then reread what I wrote, paying close attention. Here are a few places you went wrong:

[list]
[*]"[FONT="Courier New"]ALTER DATABASE[/FONT]" isn't a database name, it's a statement. It doesn't replace `information_schema`, which is a database name.
[*]Nothing is syntactically wrong with the [FONT="Courier New"]CREATE DATABASE[/FONT] and [FONT="Courier New"]USE[/FONT] statements, so they don't need to be altered (deleted, but not altered).
[*]Quotes must be balanced. By leaving the backticks off of [FONT="Courier New"]CREATE DATABASE `ALTER DATABASE;[/FONT], the database name includes the newline and "[FONT="Courier New"]USE[/FONT]" 
[*][quote="ataarticles, post: 759120"]Just to be sure, do i need to replace this section with 'ALTER DATABASE':[/QUOTE] 
You don't need to replace some section with an [FONT="Courier New"]ALTER DATABASE[/FONT] statement, you need to remove the section. The note about [FONT="Courier New"]ALTER DATABASE[/FONT] was to explain why you're getting the error, but fixing the error is a moot point because you don't need the section that generates the error. As stated previously,
[quote="misson, post: 757013"]Delete the section that deals with `information_schema` [...][/QUOTE]
The whole section, from the first line that mentions `information_schema` to the next [FONT="Courier New"]CREATE[/FONT]/[FONT="Courier New"]ALTER[/FONT] statement, not just the [FONT="Courier New"]CREATE DATABASE[/FONT] and [FONT="Courier New"]USE[/FONT] statements. You need to delete anything statements that operate on tables in the database, since you don't have write privileges for anything in `information_schema`.
[code]  ---- Start deletion here ----
-- Database: `information_schema`
--
CREATE DATABASE `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `information_schema`;

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

--
-- Table structure for table `COLUMNS`
--

CREATE TABLE `COLUMNS` 
...
  ---- End deletion here ----

-- Database: `...`
--
CREATE DATABASE ...

Note: beyond my start/end deletion comments, your script may not look exactly like my example.
[/list]

AaronGmes · May 6, 2022

How I can repair that error?

The sql-mode strict_trans_tables is enabled in your mysql server, please contact your host provider to disable it.

Its appear me when I chosse the option of installation

sql error

ataarticles

New Member

gomarc

Member

ataarticles

New Member

misson

Community Paragon

ataarticles

New Member

misson

Community Paragon

AaronGmes

New Member

Free Web Hosting

Our Community

Legal