2.0.6: Export from DB A & Import to exact mirror DB B... SQL Error ?

[MoreBloodWine]

Anyone else got possible insight in to this ? This is a new cloud server I'm on so I've never ran into this problem beofore, imports & exports have always worked fine for me without error... below is a converation wih me and my host. Just want to see if any of you guys have any other ideas.

Me

I've tried this upload with two different types of exports from anothr DB. Whenever I try to do an import I get this error. Can you please try and fix whatever is wrong and for my own curiosity, tell me what was wrong after fixing it, Ty.

Error
There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:
----BEGIN CUT----
eNo1jEEKwjAUBYXu/ineAeKnaQ1KdqGEKqRJTFo9gLgolOwEvb2t4CzfPMamFJJGJ9E1cNZrSDSQ
kvK4zhXR8MlXp6FYcdvsH8tCU04IWcD01o8CN7se73PBxeLENcXBaBy4ZkXxHDctQt4CLcsj3Fxe
b3LG9xrPQr82Vbs/X1DYH30=
----END CUT----
----BEGIN RAW----

ERROR: C1 C2 LEN: 1 2 11
STR:

MySQL: 5.5.32-cll
USR OS, AGENT, VER: Win IE 8.0
PMA: 4.0.5
PHP VER,OS: 5.3.17 Linux
LANG: en
SQL:
----END RAW----


SQL query:



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

Host

Hello,

Please supply us with the a step-by-step instruction along with the SQL queries you run so that we can reproduce the issue on this end.

Should you require any further assistance, please don't hesitate to contact us.

---
Kind regards,
Sergey P.
L3 System Administrator

Me

This was all done from the client side of things so here's the user info.

https://eojmarket.com:2083/frontend/x3/sql/PhpMyAdmin.html

Name: <removed>
PW: <removed>

Step 1: Goto DB eojmarke_EoJForums
Step 2: Export
Step 3: Method Custom
Step 4: Highlight / select only smf_tp_shoutbox
Step 5: Selecta Data
Step 6: Click go & download file
Step 7: Goto DB eojmarke_EoJMP
Step 8: Empty / tuncate smf_tp_shoutbox to remove any possiblee existing data
Step 9: Import
Step 10: Browse / select file
Step 11: Click go

Result:

Error
There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:
----BEGIN CUT----
eNo1jEEKwjAUBYXu/ineAeKnaQ1KdqGEKqRJTFo9gLgolOwEvb2t4CzfPMamFJJGJ9E1cNZrSDSQ
kvK4zhXR8MlXp6FYcdvsH8tCU04IWcD01o8CN7se73PBxeLENcXBaBy4ZkXxHDctQt4CLcsj3Fxe
b3LG9xrPQr82Vbs/X1DYH30=
----END CUT----
----BEGIN RAW----

ERROR: C1 C2 LEN: 1 2 11
STR:

MySQL: 5.5.32-cll
USR OS, AGENT, VER: Win IE 8.0
PMA: 4.0.5
PHP VER,OS: 5.3.17 Linux
LANG: en
SQL:
----END RAW----


SQL query:



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

-------

I've tried this several different ways with the same end result.

Host

Hello Carol,

It appears that you are just trying to import the data from the file to the table with the existing structure. This may require enabling of LOCAL INFILE support in MySQL. We would not recommend going with this function as it has some security issues. But if you decide to enable it, you would need to add local-infile to /etc/my.cnf and make sure that MySQL "show variables" shows local-infile enabled. It should be enabled for both, your MySQL user and server-wide to get it working.

Should you require any further assistance, please don't hesitate to contact us.

---
Kind regards,
Sergey P.
L3 System Administrator

Me

How is it such a security issue, I have done this on oher servers before, shared ones mind you to which I have never had root access.

Host

Hello,

Details of this security issue are presented on this document:

http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html

Please check and let s know how you wish to proceed further.

We are looking forward to your reply.

Thank you for contacting support team.
Please feel free to contact us for further help.

Ionut Jar
L2 Technical Support

Me

I looked that over and am curious. With this doing what it does, causing my uploads to fail. How is one supposed to get jobs done where data needs to be uploaded wihout turning all that stuff on ? From what I can tell, all I / anyone is able to do is just get successful exports. So imports are effectively never possible.

Ok, so notice in the hosts one reply "in red" he says this may require... that means what it means which is why I am here to see if anyone else has any ideas.

I mean I could try to do what he says with that enable, but it's not something I'd rather do if it's a security risk. Not to mention everytime I need to import something I would have to enable what he said to enable and then disable it to so not to have an open security risk.

[margarett]

Can you past here some of the first lines of the .sql fine?

If you choose to export only data, it should import into a table with the exact same structure. I usually choose structure and data but I guess it should work like that.
Will try here in my localhost.

[Shambles]

Interesting.


http://www.simplemachines.org/community/index.php?topic=513425.msg3628959#msg3628959

[MoreBloodWine]

Can you past here some of the first lines of the .sql fine?

If you choose to export only data, it should import into a table with the exact same structure. I usually choose structure and data but I guess it should work like that.
Will try here in my localhost.

I've tried it with different methods from the options on the page when choosing the export settings but all with the same result. I'm not modifying anything that would oherwise break anything so I don't know whats up.

Interesting.


http://www.simplemachines.org/community/index.php?topic=513425.msg3628959#msg3628959

Interesting indeed.

[Kindred]

please past the top 30 lines or so of your SQL file in a code box.

[MoreBloodWine]

please past the top 30 lines or so of your SQL file in a code box.

First 30 of all files with exception to all lines from DB B.

DB A Data

Code Select Expand

-- phpMyAdmin SQL Dump
-- version 4.0.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 29, 2013 at 06:26 PM
-- Server version: 5.5.32-cll
-- PHP Version: 5.3.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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: `eojmarke_EoJForums`
--

--
-- Dumping data for table `smf_tp_shoutbox`
--

INSERT INTO `smf_tp_shoutbox` (`id`, `value1`, `value2`, `value3`, `type`, `value4`, `value5`, `value6`, `value7`, `value8`, `edit`) VALUES
(4, 'Woot, I got first shout in ;-p', '1216105547', '<a href="http://www.eojmarket.com/index.php?action=profile;u=1" style="color: #FF0000">MoreBloodWine</a>', 'shoutbox', '24.154.67.200', 1, '', 0, '', 0),
(5, 'Yo this site looks dope', '1216182078', '<a href="http://www.eojmarket.com/index.php?action=profile;u=5">Herbalacious</a>', 'shoutbox', '24.237.136.126', 5, '', 0, '', 0),
(6, 'Ya I know, I''m that good lol...', '1216185000', '<a href="http://www.eojmarket.com/index.php?action=profile;u=1" style="color: #FF0000">MoreBloodWine</a>', 'shoutbox', '24.154.67.200', 1, '', 0, '', 0),

DB A Structure & Data

Code Select Expand

-- phpMyAdmin SQL Dump
-- version 4.0.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 29, 2013 at 06:26 PM
-- Server version: 5.5.32-cll
-- PHP Version: 5.3.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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: `eojmarke_EoJForums`
--

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

--
-- Table structure for table `smf_tp_shoutbox`
--

CREATE TABLE IF NOT EXISTS `smf_tp_shoutbox` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

DB B Structure

Code Select Expand

-- phpMyAdmin SQL Dump
-- version 4.0.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 29, 2013 at 06:34 PM
-- Server version: 5.5.32-cll
-- PHP Version: 5.3.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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: `eojmarke_EoJMP`
--

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

--
-- Table structure for table `smf_tp_shoutbox`
--

CREATE TABLE IF NOT EXISTS `smf_tp_shoutbox` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value1` text,
  `value2` text,
  `value3` text,
  `type` tinytext,
  `value4` text,
  `value5` int(11) NOT NULL DEFAULT '-2',
  `value6` text,
  `value7` tinyint(4) NOT NULL,
  `value8` text,
  `edit` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2164 ;

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


[margarett]

Nhe... Or, like in the other topic, you have some trash at the end of the file or... I don't know

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

Line 1 is a comment (as usual in a backup) and has nothing to do with the text presented in the error...

[MoreBloodWine]

Something about what I pasted in my last reply did not let the page load so here goes again... this time with an image. This is the last four lines from all creaed exports. When the last line is copied and pasted it makes that L thing referenced in the error.

[MoreBloodWine]

The issue as my host has discovered is an issue within PHPMyAdmin and suggests a force update of cPanel or contact cPanel for support. So I'm considering this resolved even thouh the error still exists but hopefully wont for long.