Apache Friends Support Forum

Apologies for the long post. I would appreciate help with this problem as I can't see what the problem is.
I have updated my MySQL database which I use for local church data by using the latest XAMPP installation of MySQL and PHP. I am now trying to restore the data I exported from the old database into the new one. The small Admin database imports without problems but the large graveyard database with some 6,000 records fails. The error messages are multiple instances of the same message -
Error
Static analysis:
17041 errors were found during analysis.
An opening bracket followed by a set of values was expected. (near "," at position 346)
Unexpected token. (near "," at position 346)
Unexpected token. (near "(" at position 349)
and so on ....

Further down the list it says
SQL query:
INSERT INTO `gravestones` (`grave_index`, `grave_forename`, `grave_surname`, `grave_funeral_ddmm`, `grave_funeral_yyyy`, `grave_dod`, `grave_ageatdeath`, `grave_register`, `grave_stonenumber`, `grave_type`, `grave_address`, `grave_cause`, `grave_dob`, `grave_area`, `grave_row`, `grave_grave`, `grave_date`, `grave_updated`, `grave_image`) VALUES, (1, 'Elizabeth', 'Normond', '17-Feb', '1731', '---', '---', 'YPR42/3', '---', '---', 'Gill', '---', '---', 'Current area', '---', '---', '---', '2015-09-13 21:51:49', ''), (2, 'Ann', 'Richardson', '21-Feb', '1731', '', '', 'YPR42/3', '', '', 'Preston Hows', '', '', '', '', '', '', '', ''), (3, and so forth

and finally
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
(1, 'Elizabeth', 'Normond', '17-Feb', '1731', '---', '---', 'YPR42/3', '---', ' at line 1

The file I am importing is
-- phpMyAdmin SQL Dump
-- version 4.2.5
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Nov 27, 2015 at 10:34 AM
-- Server version: 5.0.95-log
-- PHP Version: 5.5.14

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: `graveyard`
--

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

--
-- Table structure for table `admin`
--

CREATE TABLE IF NOT EXISTS `admin` (
`gy_adminindex` int(1) NOT NULL primary key auto_increment,
`gy_username` varchar(20) NOT NULL,
`gy_password` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`gy_adminindex`, `gy_username`, `gy_password`) VALUES
(1, 'Wardens', '********');

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

--
-- Table structure for table `gravestones`
--

CREATE TABLE IF NOT EXISTS `gravestones` (
`grave_index` int(10) NOT NULL primary key auto_increment,
`grave_forename` varchar(25) NOT NULL default '---',
`grave_surname` varchar(25) NOT NULL default '---',
`grave_funeral_ddmm` varchar(20) default '---',
`grave_funeral_yyyy` varchar(4) NOT NULL default '---',
`grave_dod` varchar(20) NOT NULL default '---',
`grave_ageatdeath` varchar(20) default '---',
`grave_register` varchar(10) default '---',
`grave_stonenumber` varchar(20) default '---',
`grave_type` varchar(9) NOT NULL default '---',
`grave_address` varchar(100) NOT NULL default '---',
`grave_cause` varchar(25) default '---',
`grave_dob` varchar(20) default '---',
`grave_area` varchar(20) NOT NULL default 'Current area',
`grave_row` varchar(20) NOT NULL default '---',
`grave_grave` varchar(20) default '---',
`grave_date` varchar(20) default '---',
`grave_updated` varchar(20) NOT NULL default '---',
`grave_image` varchar(255) default '---'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gravestones`
--

INSERT INTO `gravestones` (`grave_index`, `grave_forename`, `grave_surname`, `grave_funeral_ddmm`, `grave_funeral_yyyy`, `grave_dod`, `grave_ageatdeath`, `grave_register`, `grave_stonenumber`, `grave_type`, `grave_address`, `grave_cause`, `grave_dob`, `grave_area`, `grave_row`, `grave_grave`, `grave_date`, `grave_updated`, `grave_image`) VALUES,
(1, 'Elizabeth', 'Normond', '17-Feb', '1731', '---', '---', 'YPR42/3', '---', '---', 'Gill', '---', '---', 'Current area', '---', '---', '---', '2015-09-13 21:51:49', ''),
(2, 'Ann', 'Richardson', '21-Feb', '1731', '', '', 'YPR42/3', '', '', 'Preston Hows', '', '', '', '', '', '', '', ''),
(3, 'Margaret', 'Benn', '31-Mar', '1731', '', '', 'YPR42/3', '', '', 'Netherend', '', '', '', '', '', '', '', ''), and so on ending
..................
6051, 'Patience Christine', 'West', '7/9', '2015', '22/9/2015', '---', '---', '---', '---', '---', '---', '---', 'Current area', '---', '---', '2015-10-10 21:26:23', '---', '---'),
(6052, 'Geraldine Patricia', 'Pattinson', '8/10', '2015', '8/10/15', '---', '---', '---', '---', '---', '---', '---', 'Current area', '---', '---', '2015-10-10 21:27:54', '---', '---');

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

I had similar errors in phpmyadmin, therefore i decided to run imports without the help of phpmyadmin, instead i use the mysql interpreter from commandline (in a terminal). Lets say, your export file is mydata.sql, you can run an import via:

Code: Select all

c:/xampp/bin/mysql --user=root --password=putyourpasswordhere database <mydata.sql

where "database" is your Database.

When your mysql command interpreter does not like the lines beginning with "--", you should edit that file before and delete all that lines.

Thanks for that Nobbie, but unfortunately it didn't work.
Not really knowing much about SQL I applied a bit of lateral thinking and somewhat to my surprise it worked! In case it is of any help to anyone at my low level of SQL knowledge who hits this problem, this is what I did.
I created the table I needed manually via phpMyAdmin.
Using that database and table I entered a dummy set of data.
Next I exported that using the Export function in phpMyAdmin. File A
I then deleted the table with the dummy record from my database and imported File A that I had just created back into my database - that worked.
I opened the file I couldn't get to import, File B, in Notepad and copied the data section i.e. from VALUES( to the final ;
I then opened the File A which I had been able to import and pasted the data I needed over the dummy data.
Finally I again deleted the table from my database and ran Import again using File B.
Eureka - I've recovered my 6000 plus records.

I'm sure the experts would be able to point to a much simpler way, but what the heck, my method worked! Why I have no idea - I couldn't see any difference between the command sections of the file that would import and the one that wouldn't.

Begadoc wrote:SQL query:
INSERT INTO
`gravestones` (`grave_index`, `grave_forename`, `grave_surname`, `grave_funeral_ddmm`, `grave_funeral_yyyy`, `grave_dod`, `grave_ageatdeath`, `grave_register`, `grave_stonenumber`, `grave_type`, `grave_address`, `grave_cause`, `grave_dob`, `grave_area`, `grave_row`, `grave_grave`, `grave_date`, `grave_updated`, `grave_image`)
VALUES,
(1, 'Elizabeth', 'Normond', '17-Feb', '1731', '---', '---', 'YPR42/3', '---', '---', 'Gill', '---', '---', 'Current area', '---', '---', '---', '2015-09-13 21:51:49', ''), (2, 'Ann', 'Richardson', '21-Feb', '1731', '', '', 'YPR42/3', '', '', 'Preston Hows', '', '', '', '', '', '', '', ''), (

if you've done a c&P Ithink that the comma behind VALUES is the reason

As I said, experts will point out an easier way. Never mind, I enjoyed the challenge!!

Thanks - I'll remember next time to look for errant commas.

This problem with imports is a bug in phpMyadmin as detailed here:
https://github.com/phpmyadmin/phpmyadmin/issues/12054

The fix is here
https://github.com/phpmyadmin/sql-parser/commit/d2e37de1dbec251556f35712bddcd61d24baaee6

but note that the file is located at
\phpMyAdmin\libraries\sql-parser\src\Utils\BufferedQuery.php
and there is only this one file to edit.

I am using PHPMyadmin v4.5.0.2 on XAMPP v5.6.14-0.
My BufferedQuery.php code was almost a copy of the corrected version. Nevertheless, I tried to fix it but no luck. Still get errors when importing db.

I was so happy thinking it was finally solved...

Any thoughts?

Thanks.

EDIT: OK, I am kind of confused right now. I have just updated PHPMyAdmin to latest stable version (v4.5.5.1) to test. I have tried to import the db again and...perfect. No errors. Then I am checking my BufferedQuery.php file and it is the uncorrected version.
Why is this working?

Cheers!