sql error

[ataarticles]

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]

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: Can't restore mysql database, assuming you have a database to spare.

[ataarticles]

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]

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]

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]

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:

• "ALTER DATABASE" 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 CREATE DATABASE and USE statements, so they don't need to be altered (deleted, but not altered).
• Quotes must be balanced. By leaving the backticks off of CREATE DATABASE `ALTER DATABASE;, the database name includes the newline and "USE"

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

  You don't need to replace some section with an ALTER DATABASE statement, you need to remove the section. The note about ALTER DATABASE 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,
  

  Delete the section that deals with `information_schema` [...]

  The whole section, from the first line that mentions `information_schema` to the next CREATE/ALTER statement, not just the CREATE DATABASE and USE 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`.
  

    ---- 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.