[RESOLVED] Help with database

maddog720

I am trying to transfer a forum with phpMyadmin. The forum is in phpbb format. I keep getting the following error:

Error

SQL query:

-- MySQL dump 10.10

-- Host: mysql4..net Database: _phpbb

-- Server version 4.0.25-standard-log
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;

MySQL said: Documentation
#1065 - Query was empty

*Edit: I am trying to import the data from the old forum.

Houdini

Go back to phpMyAdmin select the database and the table then perform an export then check the as file, it should then do it for you properly, all you will need to do is tell phpMyAdmin what palce you want to put it in on your hard drive.

maddog720

Houdini wrote:
Go back to phpMyAdmin select the database and the table then perform an export then check the as file, it should then do it for you properly, all you will need to do is tell phpMyAdmin what palce you want to put it in on your hard drive.

I want to import, not export. Does this part look ok?

-- MySQL dump 10.10

-- Host: db245.perfora.net Database: db148172166

-- Server version 4.0.25-standard-log
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

-- Table structure for table `ad_auth_access`

DROP TABLE IF EXISTS ad_auth_access;
CREATE TABLE ad_auth_access (
group_id mediumint(8) NOT NULL default '0',
forum_id smallint(5) unsigned NOT NULL default '0',
auth_view tinyint(1) NOT NULL default '0',
auth_read tinyint(1) NOT NULL default '0',
auth_post tinyint(1) NOT NULL default '0',
auth_reply tinyint(1) NOT NULL default '0',
auth_edit tinyint(1) NOT NULL default '0',
auth_delete tinyint(1) NOT NULL default '0',
auth_sticky tinyint(1) NOT NULL default '0',
auth_announce tinyint(1) NOT NULL default '0',
auth_vote tinyint(1) NOT NULL default '0',
auth_pollcreate tinyint(1) NOT NULL default '0',
auth_attachments tinyint(1) NOT NULL default '0',
auth_mod tinyint(1) NOT NULL default '0',
KEY group_id (group_id),
KEY forum_id (forum_id)
) TYPE=MyISAM;

-- Dumping data for table `ad_auth_access`

/!40000 ALTER TABLE ad_auth_access DISABLE KEYS /;
LOCK TABLES ad_auth_access WRITE;
INSERT INTO ad_auth_access VALUES (3,17,0,0,0,0,

gardnc

-- Table structure for table `ad_auth_access`

DROP TABLE IF EXISTS ad_auth_access; // This line deletes the table if it exists

// This creates the table
CREATE TABLE ad_auth_access (
.........
........

// This locks the table and starts inserting records
LOCK TABLES ad_auth_access WRITE;
INSERT INTO ad_auth_access VALUES (3,17,0,0,0,0,

As long as you are seleteced to the database where you want to import the data, this will work.

maddog720

[QUOTE=gardnc]-- Table structure for table `ad_auth_access`

DROP TABLE IF EXISTS ad_auth_access; // This line deletes the table if it exists

// This creates the table
CREATE TABLE ad_auth_access (
.........
........

// This locks the table and starts inserting records
LOCK TABLES ad_auth_access WRITE;
INSERT INTO ad_auth_access VALUES (3,17,0,0,0,0,

As long as you are seleteced to the database where you want to import the data, this will work.[/QUOTE]

SQL query:

/!40000 ALTER TABLE ad_auth_access ENABLE KEYS /;

MySQL said: Documentation
#1065 - Query was empty

Got a little farther, the problem is it is a quite large file. Is there anything to do it automatically?

maddog720

Error

SQL query:

/ --

-- Table structure for table `ad_posts`

DROP TABLE IF EXISTS ad_posts CREATE TABLE ad_posts (
post_id mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
topic_id mediumint( 8 ) unsigned NOT NULL default '0',
forum_id smallint( 5 ) unsigned NOT NULL default '0',
poster_id mediumint( 8 ) NOT NULL default '0',
post_time int( 11 ) NOT NULL default '0',
poster_ip varchar( 8 ) NOT NULL default '',
post_username varchar( 25 ) default NULL ,
enable_bbcode tinyint( 1 ) NOT NULL default '1',
enable_html tinyint( 1 ) NOT NULL default '0',
enable_smilies tinyint( 1 ) NOT NULL default '1',
enable_sig tinyint( 1 ) NOT NULL default '1',
post_edit_time int( 11 ) default NULL ,
post_edit_count smallint( 5 ) unsigned NOT NULL default '0',
PRIMARY KEY ( post_id ) ,
KEY forum_id ( forum_id ) ,
KEY topic_id ( topic_id ) ,
KEY poster_id ( poster_id ) ,
KEY post_time ( post_time )
) TYPE = MYISAM ;

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 '/

DROP TABLE IF EXISTS ad_posts
CREATE TABLE ad_posts (

What is the problem here?

rincewind456

you could always use the command line to load the data from a file, look at the mysql manual for LOAD DATA INFILE, or there is also the option in phpmyadmin to load from a file under the import tab.

Houdini

This is the reason that I said to export the file using phpMyAdmin to your hard drive (not likely to have mistakes) then import the file again using phpMyAdmin, if it is too large then break it into smaller chunks and import it in a series of queries. A database of phpBB with all the data could be well over 15 meg.

maddog720

Houdini wrote:
This is the reason that I said to export the file using phpMyAdmin to your hard drive (not likely to have mistakes) then import the file again using phpMyAdmin, if it is too large then break it into smaller chunks and import it in a series of queries. A database of phpBB with all the data could be well over 15 meg.

The problem is I get errors when I try to upload it and I already have the file on a hardrive.

SQL query:

/ --

-- Table structure for table `ad_posts`

DROP TABLE IF EXISTS ad_posts ;

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 '/

DROP TABLE IF EXISTS ad_posts' at line 1

When I remove the ; I get the error I had in the above post.

This part is causing problems.

[CODE]Table structure for table `ad_posts`

DROP TABLE IF EXISTS ad_posts;
CREATE TABLE ad_posts (
post_id mediumint(8) unsigned NOT NULL auto_increment,
topic_id mediumint(8) unsigned NOT NULL default '0',
forum_id smallint(5) unsigned NOT NULL default '0',
poster_id mediumint(8) NOT NULL default '0',
post_time int(11) NOT NULL default '0',
poster_ip varchar(8) NOT NULL default '',
post_username varchar(25) default NULL,
enable_bbcode tinyint(1) NOT NULL default '1',
enable_html tinyint(1) NOT NULL default '0',
enable_smilies tinyint(1) NOT NULL default '1',
enable_sig tinyint(1) NOT NULL default '1',
post_edit_time int(11) default NULL,
post_edit_count smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (post_id),
KEY forum_id (forum_id),
KEY topic_id (topic_id),
KEY poster_id (poster_id),
KEY post_time (post_time)
) TYPE=MyISAM;

-- Dumping data for table `ad_posts`

LOCK TABLES ad_posts WRITE;
INSERT INTO ad_posts VALUES [/CODE]

Houdini

I just took the same SQL above and created the table ad_posts I used phpMyAdmin 2.7.0-pl1 MySQL5.0.18 Apache 2.2.0 and PHP 5.1.1. I went to an existing test database selected SQL pasted the query and it ran just fine. Here is what phpMyAdmin said;

DROP TABLE IF EXISTS ad_posts ;# MySQL returned an empty result set (i.e. zero rows).
CREATE TABLE ad_posts (
post_id mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
topic_id mediumint( 8 ) unsigned NOT NULL default '0',
forum_id smallint( 5 ) unsigned NOT NULL default '0',
poster_id mediumint( 8 ) NOT NULL default '0',
post_time int( 11 ) NOT NULL default '0',
poster_ip varchar( 8 ) NOT NULL default '',
post_username varchar( 25 ) default NULL ,
enable_bbcode tinyint( 1 ) NOT NULL default '1',
enable_html tinyint( 1 ) NOT NULL default '0',
enable_smilies tinyint( 1 ) NOT NULL default '1',
enable_sig tinyint( 1 ) NOT NULL default '1',
post_edit_time int( 11 ) default NULL ,
post_edit_count smallint( 5 ) unsigned NOT NULL default '0',
PRIMARY KEY ( post_id ) ,
KEY forum_id ( forum_id ) ,
KEY topic_id ( topic_id ) ,
KEY poster_id ( poster_id ) ,
KEY post_time ( post_time )
) TYPE = MYISAM ;# MySQL returned an empty result set (i.e. zero rows).

Then I dropped the table and saved the query as test.sql saved it to my hardrive and selected import then browsed to where I had saved the file and ran it again with no problem. Here is the import operation echoed from phpMyAdmin;

DROP TABLE IF EXISTS ad_posts ;# MySQL returned an empty result set (i.e. zero rows).

MySQL returned an empty result set (i.e. zero rows).

CREATE TABLE ad_posts (
post_id mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
topic_id mediumint( 8 ) unsigned NOT NULL default '0',
forum_id smallint( 5 ) unsigned NOT NULL default '0',
poster_id mediumint( 8 ) NOT NULL default '0',
post_time int( 11 ) NOT NULL default '0',
poster_ip varchar( 8 ) NOT NULL default '',
post_username varchar( 25 ) default NULL ,
enable_bbcode tinyint( 1 ) NOT NULL default '1',
enable_html tinyint( 1 ) NOT NULL default '0',
enable_smilies tinyint( 1 ) NOT NULL default '1',
enable_sig tinyint( 1 ) NOT NULL default '1',
post_edit_time int( 11 ) default NULL ,
post_edit_count smallint( 5 ) unsigned NOT NULL default '0',
PRIMARY KEY ( post_id ) ,
KEY forum_id ( forum_id ) ,
KEY topic_id ( topic_id ) ,
KEY poster_id ( poster_id ) ,
KEY post_time ( post_time )
) TYPE = MYISAM ;# MySQL returned an empty result set (i.e. zero rows).

MySQL returned an empty result set (i.e. zero rows).

Is that file saved as an SQL file not that a plain text file shouldn't work, cause I had no problems with your query.

maddog720

I have attached part of the database. It won't work for me when uploaded in full form. The part should be enough to experiment with. I would be willing to possibly email the full thing edited and/or undedited because I don't want all the forum info just floating around where anyone can get it.

Houdini

Look at the below
LOCK TABLES ad_groups WRITE;
INSERT INTO ad_groups VALUES (1,1,'Anonymous','Personal User',0,1),(2,1,'Admin','Personal User',0,1),(3,1,'','Personal User',0,1),(4,1,'','Personal User',0,1),(5,1,'','Personal User',0,1),(6,1,'','Personal User',0,1),(7,1,'','Personal User',0,1),(8,1,'','Personal User',0,1),(9,1,'','Personal User',0,1),(10,1,'','Personal User',0,1),(11,1,'','Personal User',0,1),(12,1,'','Personal User',0,1),(13,1,'','Personal User',0,1),(14,1,'','Personal User',0,1),(15,1,'','Personal User',0,1),(16,1,'','Personal User',0,1),(17,1,'','Personal User',0,1),(18,1,'','Personal User',0,1),(19,1,'','Personal User',0,1),(20,1,'','Personal User',0,1),(21,1,'','Personal User',0,1),(22,1,'','Personal User',0,1),(23,1,'','Personal User',0,1),(25,1,'','Personal User',0,1),(26,1,'','Personal User',0,1),(27,1,'','Personal User',0,1),(28,1,'','Personal User',0,1),(29,1,'','Personal User',0,1),(30,1,'','Personal User',0,1),(31,1,'','Personal User',0,1),(32,1,'','Personal User',0,1),(33,1,'','Personal User',0,1),(34,1,'','Personal User',0,1),(35,1,'','Personal User',0,1),(38,1,'','Personal User',0,1),(37,1,'','Personal User',0,1),(39,1,'','Personal User',0,1),(40,1,'','Personal User',0,1),(41,1,'','Personal User',0,1);
UNLOCK TABLES;
/
Do you see the red forward slash it can't be there in your file and here is what is after it

/

--
-- Table structure for table `ad_posts`
--

DROP TABLE IF EXISTS `ad_posts`;
CREATE TABLE `ad_posts` (

Want to guess why it failed?

maddog720

Houdini wrote:
Look at the below
LOCK TABLES ad_groups WRITE;
INSERT INTO ad_groups VALUES (1,1,'Anonymous','Personal User',0,1),(2,1,'Admin','Personal User',0,1),(3,1,'','Personal User',0,1),(4,1,'','Personal User',0,1),(5,1,'','Personal User',0,1),(6,1,'','Personal User',0,1),(7,1,'','Personal User',0,1),(8,1,'','Personal User',0,1),(9,1,'','Personal User',0,1),(10,1,'','Personal User',0,1),(11,1,'','Personal User',0,1),(12,1,'','Personal User',0,1),(13,1,'','Personal User',0,1),(14,1,'','Personal User',0,1),(15,1,'','Personal User',0,1),(16,1,'','Personal User',0,1),(17,1,'','Personal User',0,1),(18,1,'','Personal User',0,1),(19,1,'','Personal User',0,1),(20,1,'','Personal User',0,1),(21,1,'','Personal User',0,1),(22,1,'','Personal User',0,1),(23,1,'','Personal User',0,1),(25,1,'','Personal User',0,1),(26,1,'','Personal User',0,1),(27,1,'','Personal User',0,1),(28,1,'','Personal User',0,1),(29,1,'','Personal User',0,1),(30,1,'','Personal User',0,1),(31,1,'','Personal User',0,1),(32,1,'','Personal User',0,1),(33,1,'','Personal User',0,1),(34,1,'','Personal User',0,1),(35,1,'','Personal User',0,1),(38,1,'','Personal User',0,1),(37,1,'','Personal User',0,1),(39,1,'','Personal User',0,1),(40,1,'','Personal User',0,1),(41,1,'','Personal User',0,1);
UNLOCK TABLES;
/
Do you see the red forward slash it can't be there in your file and here is what is after it
/

--
-- Table structure for table `ad_posts`
--

DROP TABLE IF EXISTS `ad_posts`;
CREATE TABLE `ad_posts` (
Want to guess why it failed?

???

rincewind456

maddog720 wrote:
???

Remove the stray /

maddog720

Error

SQL query:

-- Table structure for table `ad_posts`

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 'CREATE TABLE ad_posts (
post_id mediumint(8) unsigned NOT

Houdini

DROP TABLE IF EXISTS ad_posts CREATE TABLE ad_posts (

Should be;

DROP TABLE IF EXISTS ad_posts;
CREATE TABLE ad_posts...................

maddog720

Houdini wrote:
Should be;

The database worked somewhat :queasy: . The data is there, but the forum still looks the same.

maddog720

I think I found the problem (I think) it is somthing like the old database was xx_xxx the new one is yy_xxx.

maddog720

maddog720 wrote:
I think I found the problem (I think) it is somthing like the old database was xx_xxx the new one is yy_xxx.

It is somewhat working know http://antidrm.org/forums/index.php?sid=5b4c1038f310dcd81d57ef83f58736db