sql error

ataarticles

New Member
Messages
53
Reaction score
0
Points
0
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.
 
Last edited:

ataarticles

New Member
Messages
53
Reaction score
0
Points
0
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/
 
Last edited:

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
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

New Member
Messages
53
Reaction score
0
Points
0
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.
 
Last edited:

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
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

New Member
Messages
2
Reaction score
0
Points
1
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
 
Top