PHPMyAdmin Export/Import

[FrizzleFried]

I am trying to import some info to a mod that uses the SMF database.  I am having difficulty determining exactly what settings I need to EXPORT/IMPORT using PHPMyAdmin.  If I attempt to export as SQL file... then attempt to simply import using that very same file without touching it... (aside from changing it to import as a new table)... it errors out.  OBVIOUSLY I am exporting/importing incorrectly.

Can someone run down the settings I am suppose to use to properly export/import tables from the SMF database?

Any/all help would be GREATLY appreciated.

[Kindred]

errors out is a useless description....


What is the ACTUAL error message?

[live627]

It times out?

[FrizzleFried]

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----
eNo1zbEKwjAYBGCh2/8U9wAxNKlByBZKqEKaxKRWcBWHQskm6NubCt54fNzZlELS6AV6CWe9hoCE
EJSnWjdE4ydfnIbiindy/1hXuuaEkBnMYP3EMNsKb0vBGO5n50ylLcXRaBx4yxXFU9wMC3lb6bg4
wi3l9SZn/KDxLPQ7oGb3zxcWUiEE
----END CUT----
----BEGIN RAW----

ERROR: C1 C2 LEN: 1 2 11
STR: 

MySQL: 5.5.32-cll
USR OS, AGENT, VER: Win MOZILLA 5.0
PMA: 4.0.5
PHP VER,OS: 5.3.17 Linux
LANG: en
SQL:


----END RAW----

SQL query:



MySQL said: Documentation
#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

Again... all I did was EXPORT the table... change the name of the table to be created as well as the name of the table to have the data inserted... and then save... then try to import the file.  I get this error but the new table is created (but not created correctly it appears)...

[MrPhil]

If it's timing out, you will need to either use a "BigDump" type utility which can import and export large .sql files, or break up the task into smaller pieces (import one table at a time, or even part of a table at a time). If it's complaining about duplicate keys, first make sure you aren't accidentally overwriting your SMF database! Either have the export add IF EXIST ... DROP TABLE ... CREATE TABLE, or do it manually, to start with an empty table (assuming you're not trying to do an incremental update). By the way, if your target application doesn't need a database, but just the data, phpMyAdmin can export as a CSV file.

[MrPhil]

This is SMF data? Or did you export as a gzipped (or otherwise compressed) file, and tried to import as a plain .sql file?

[FrizzleFried]

This is a table used with an SMF mod... I export as SQL and then try to import using SQL.

EDIT: Basically I am building an arcade game review site.  I am attempting to import arcade game/manufacturers/years so I don't have to manually enter 5000+ records.  Before I get to the point where I add my own data though,  I wanted to make sure I could export/import correctly so I created 9 records through the mod.  I then exported the table that this data is stored in... changed the name of the table to be created and the name of the table to insert the data to... then tried to import to see if it imported correctly to the new table (for testing purposes)...

Here is where it's not working.

I am attaching the SQL file in question.

[Kindred]

well... that is - fairly obviously, not SQL data in that import...

What are the first 10 lines of your export data file?

[MrPhil]

That looks like base64-encoded data -- is that what the field in question contains? Take a look at the entire SQL statement that's failing: what's it trying to do, how is the field declared, how is the data written out (e.g., in ' delimiters)?  If there are delimiters, does the data contain said delimiter within it?

[FrizzleFried]

That looks like base64-encoded data -- is that what the field in question contains? Take a look at the entire SQL statement that's failing: what's it trying to do, how is the field declared, how is the data written out (e.g., in ' delimiters)?  If there are delimiters, does the data contain said delimiter within it?

I am a SQL newb.  You are speaking in gibberish to me. 

I simply EXPORTED as SQL file... then attempted to IMPORT the same file (with minor changes to the table name)... it broke.  What am I doing wrong during the EXPOR and/or IMPORT? 

[FrizzleFried]

well... that is - fairly obviously, not SQL data in that import...

What are the first 10 lines of your export data file?

I attached the file I am attempting to import... (REPLY #6)

[FrizzleFried]

Code Select Expand

-- phpMyAdmin SQL Dump
-- version 4.0.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 29, 2013 at 09:31 AM
-- 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: `ahaforum_review`
--

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

--
-- Table structure for table `smf_directory`
--

CREATE TABLE IF NOT EXISTS `smf_directorytest4` (
  `id_item` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `cat_id` int(11) NOT NULL,
  `views` int(11) NOT NULL DEFAULT '0',
  `author` int(11) NOT NULL,
  `comments` int(11) DEFAULT '0',
  `url` text NOT NULL,
  `date` int(11) unsigned NOT NULL DEFAULT '0',
  `approved` int(11) NOT NULL,
  `total_rates` varchar(5) DEFAULT '0',
  `icon` varchar(255) NOT NULL,
  PRIMARY KEY (`id_item`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

--
-- Dumping data for table `smf_directorytest4`
--

INSERT INTO `smf_directorytest4` (`id_item`, `name`, `description`, `cat_id`, `views`, `author`, `comments`, `url`, `date`, `approved`, `total_rates`, `icon`) VALUES
(1, 'Asteroids', 'Atari', 1, 17, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=6939', 1375458791, 1, '6.5', '52484ea0c8def.png'),
(12, '18 Wheeler: American Pro Trucker', 'Sega', 1, 5, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6763', 1380913784, 1, '0', '524f127873817.jpg'),
(11, '`88 Games', 'Konami', 1, 2, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6757', 1380913537, 1, '0', '524f11815e1ab.png'),
(13, '1941: Counter Attack', 'Capcom', 1, 0, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6765', 1380913954, 1, '0', '524f132292893.png'),
(6, 'A.P.B.', 'Atari\r\n', 1, 43, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=6795', 1375566439, 1, '4.5', '52484f4900055.png'),
(15, 'Amidar', 'Konami', 1, 3, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6883', 1380938905, 1, '0', '524f74bd8445c.png'),
(14, '1942', 'Capcom', 1, 2, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=6766', 1380914149, 1, '7.5', '524f13e54b6f2.png'),
(8, 'Marvel vs Capcom 2', 'Capcom ', 1, 0, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=8637', 1380852064, 1, '8.5', '524e2160d2450.jpg'),
(19, 'TESTING', 'SEGA', 1, 0, 1, 0, 'www.testgame.com', 1380994632, 1, '0', '');

/*!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 */;
         

That is a CUT/PASTE from Notepad++

[Kindred]

so....   that is very different from what is displayed in your error message....


instead of importing...   try going to the SQL tab in phpmyadmin and run

Code Select Expand


CREATE TABLE IF NOT EXISTS `smf_directorytest4` (
  `id_item` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `cat_id` int(11) NOT NULL,
  `views` int(11) NOT NULL DEFAULT '0',
  `author` int(11) NOT NULL,
  `comments` int(11) DEFAULT '0',
  `url` text NOT NULL,
  `date` int(11) unsigned NOT NULL DEFAULT '0',
  `approved` int(11) NOT NULL,
  `total_rates` varchar(5) DEFAULT '0',
  `icon` varchar(255) NOT NULL,
  PRIMARY KEY (`id_item`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20;


(although, why auto-increment=20, I have no idea...)

if that works, then try (still in the SQL tab)

Code Select Expand


INSERT INTO `smf_directorytest4` (`id_item`, `name`, `description`, `cat_id`, `views`, `author`, `comments`, `url`, `date`, `approved`, `total_rates`, `icon`) VALUES
(1, 'Asteroids', 'Atari', 1, 17, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=6939', 1375458791, 1, '6.5', '52484ea0c8def.png'),
(12, '18 Wheeler: American Pro Trucker', 'Sega', 1, 5, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6763', 1380913784, 1, '0', '524f127873817.jpg'),
(11, '`88 Games', 'Konami', 1, 2, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6757', 1380913537, 1, '0', '524f11815e1ab.png'),
(13, '1941: Counter Attack', 'Capcom', 1, 0, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6765', 1380913954, 1, '0', '524f132292893.png'),
(6, 'A.P.B.', 'Atari\r\n', 1, 43, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=6795', 1375566439, 1, '4.5', '52484f4900055.png'),
(15, 'Amidar', 'Konami', 1, 3, 1, 0, 'http://www.arcade-museum.com/game_detail.php?game_id=6883', 1380938905, 1, '0', '524f74bd8445c.png'),
(14, '1942', 'Capcom', 1, 2, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=6766', 1380914149, 1, '7.5', '524f13e54b6f2.png'),
(8, 'Marvel vs Capcom 2', 'Capcom ', 1, 0, 1, 1, 'http://www.arcade-museum.com/game_detail.php?game_id=8637', 1380852064, 1, '8.5', '524e2160d2450.jpg'),
(19, 'TESTING', 'SEGA', 1, 0, 1, 0, 'www.testgame.com', 1380994632, 1, '0', '');


[MrPhil]

Looking at the attached file in the vi editor, it has a bunch of crap on the very last line ^C^@^@^@^@^@^@^@^@^@. You might do well to clean that off. I don't see where your error message with all that base64 data comes from -- THIS file produced that?

You might want to clean up the 'Atari\r\n' entry to just 'Atari'. I have no idea if the \r\n is harmful to an SQL import, but it wouldn't hurt to clean up. I looked but didn't see any embedded ' in your data strings (should be \').

[FrizzleFried]

That worked Kindred!

I am guessing I can then use the bottom code in your post as a guideline for adding my 5000+ records?   Is there a limit in the SQL screen?  Should I keep it to like 500 records and import 10 different times?

EDIT: Looks like it is working (my putting my own data in using Kindreds method)... I still would like to know how many records at a time I should stick to?  Also,  how does one change the table names within PHPMyAdmin?   To test I want to change SMF_directory to SMF_directory1 and the test table SMF_directorytest4 to SMF_directory. Figured it out!

Thank you all for the help BTW!

[MoreBloodWine]

I experience the same error... this is what my hosts suggest since it seems to be a bug / error within PHPMyAdmin. My hosts says to either force an update of cPanel or contact cPanel support for help.