phpMyAdmin - MySQL import errors

Question

I have a Wordpress website (web A) and a staging site (web B) in which I'm working on updates and changes. On web A I receive comments from users, which I would like to export to web B. I understand the right way is through the comments database on phpMyAdmin.

I found this post from CMSMind, in which the process of exporting and then importing comments on phpMyAdmin is thoroughly explained. However, I've encountered a couple of issues when importing.

The original comments database originally had this line of code under the SQL tab and it worked fine:

SELECT * FROM `LzlcSCHMcomments` WHERE 1

I tried to do a test by importing a single comment, but some errors were found:

SELECT * FROM `LzlcSCHMcomments` WHERE 1

INSERT INTO `LzlcSCHMcomments` (`comment_ID`, `comment_post_ID`, `comment_author`, `comment_author_email`, `comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt`, `comment_content`, `comment_karma`, `comment_approved`, `comment_agent`, `comment_type`, `comment_parent`, `user_id`) VALUES
(28814, 5900, 'AUTHOR', 'EMAIL', '', 'IP', '2022-10-18 06:34:30', '2022-10-18 05:34:30', 'COMMENT', 0, '0', 'Mozilla/5.0 (Windows NT 6.1; ) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.72 Safari/537.36', 'comment', 0, 0);

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `LzlcSCHMcomments`
--
ALTER TABLE `LzlcSCHMcomments`
  ADD PRIMARY KEY (`comment_ID`),
  ADD KEY `comment_post_ID` (`comment_post_ID`),
  ADD KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  ADD KEY `comment_date_gmt` (`comment_date_gmt`),
  ADD KEY `comment_parent` (`comment_parent`),
  ADD KEY `comment_author_email` (`comment_author_email`(10));

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `LzlcSCHMcomments`
--
ALTER TABLE `LzlcSCHMcomments`
  MODIFY `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28817;
COMMIT;

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

ERROR 1 - Static analysis:

1 error found during analysis. Unexpected conditions order. (near "FROM" at position 9)

ERROR 2 - MySQL said:

#1064 - Something's wrong in your syntax near 'INSERT INTO LzlcSCHMcomments (comment_ID, comment_post_ID, `comment_aut...' on line 3

ERROR 3 - MODIFY `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28817; COMMIT;

On this COMMIT there's an error stating: No operations were started previously (near COMMIT).

This is the first time I work on my databases, so I really appreciate your help. Thanks a lot in advance!

ScaisEdge · Accepted Answer · 2022-10-19 16:56:16Z

2

In your code seems is smissing the semicolon at the end of the select
try using

SELECT * FROM `LzlcSCHMcomments` WHERE 1;

and for the error related to

ALTER TABLE `LzlcSCHMcomments`
   MODIFY `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, 
AUTO_INCREMENT=28817;
COMMIT;

You should keep in mind that you have already done the primary / autoincrement column key for the table

 ALTER TABLE `LzlcSCHMcomments`
  ADD PRIMARY KEY (`comment_ID`),

so the db engine raise the error .. you should simply remove the code for alter the table and add the modify of the column comment_id

The primary key and auto_increment must be applied one tile only so you should use this code

ALTER TABLE `LzlcSCHMcomments`
  ADD PRIMARY KEY (`comment_ID`),
  ADD KEY `comment_post_ID` (`comment_post_ID`),
  ADD KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  ADD KEY `comment_date_gmt` (`comment_date_gmt`),
  ADD KEY `comment_parent` (`comment_parent`),
  ADD KEY `comment_author_email` (`comment_author_email`(10));

ALTER TABLE `LzlcSCHMcomments` AUTO_INCREMENT=28817;

edited Oct 19, 2022 at 16:56

answered Oct 18, 2022 at 9:46

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

imthemoisturizer Over a year ago

You're right, I had completely missed that, thanks a lot! Now it shows this error (besides the COMMIT), do you have any insights on how to fix that?: #1068 - Multiple primary keys defined

ScaisEdge Over a year ago

answer updated .. you don't need the alter for modify the comment_id primary key or al least not in the wya you have done

imthemoisturizer Over a year ago

I'm not sure if I've understood correctly, I modified it like this, but now I receive an error stating "Duplicate entry '28814' for the key 'PRIMARY'", so I understand there's still something I'm doing wrong

imthemoisturizer Over a year ago

I forgot to mark this as solved, thank you so much for helping me out, your reply was exactly what I needed. Have a great day!

Collectives™ on Stack Overflow

phpMyAdmin - MySQL import errors

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related