MySQL Dumps

Discussion in 'Server Operation' started by latcarf, Feb 13, 2007.

  1. latcarf

    latcarf New Member

    I followed the instuctions for dumping databases and then restoring them but I keep getting
    Code:
    mysql> gallery_db < /root/gallery_db.sql;
    ERROR 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 'gallery_db < /root/gallery_db.sql' at line 1
    mysql>
    I can't help but think this is a setup issue since from Suse 10.1 to 10.2 it worked but now from Suse 10.1 to Debian Sarge it isn't working.

    Am I overlooking something?
     
  2. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    Yep, you're mixing shell and mysql commandlines.
    For shell usage:
    Code:
    mysql -u user -p password gallery_db < /root/gallery_db.sql
    For mysql usage:
    Code:
    # mysql
    mysql> use gallery_db;
    mysql> source /root/gallery_db.sql;
     
  3. latcarf

    latcarf New Member

    thanks for the help! I understand now... I did it from shell and got this error
    Code:
    server1:~# mysql -u root -psend4help911 gallery_db < /root/db_gallery.sql
    ERROR 1064 at line 23: 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 'DEFAULT CHARSET=latin1 COMMENT='Used to store albums'' at line
    server1:~#
    I followed it to the file and the line but I have no idea what it is telling me is wrong. Since there were 60K plus characters I shortened it up to a couple lines below the line the error describes!
    Code:
    -- MySQL dump 10.10
    --
    -- Host: localhost    Database: db_gallery
    -- ------------------------------------------------------
    -- Server version	5.0.18
    
    /*!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 */;
    /*!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 `albums`
    --
    
    DROP TABLE IF EXISTS `albums`;
    CREATE TABLE `albums` (
      `aid` int(11) NOT NULL auto_increment,
      `title` varchar(255) NOT NULL default '',
      `description` text NOT NULL,
      `visibility` int(11) NOT NULL default '0',
      `uploads` enum('YES','NO') NOT NULL default 'NO',
      `comments` enum('YES','NO') NOT NULL default 'YES',
      `votes` enum('YES','NO') NOT NULL default 'YES',
      `pos` int(11) NOT NULL default '0',
      `category` int(11) NOT NULL default '0',
      `thumb` int(11) NOT NULL default '0',
      `keyword` varchar(50) default NULL,
      `alb_password` varchar(32) default NULL,
      `alb_password_hint` text,
      PRIMARY KEY  (`aid`),
      KEY `alb_category` (`category`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Used to store albums';
    
    --
    -- Dumping data for table `albums`
    --
     
  4. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    What's the output of
    Code:
    mysql -V
    on the system you try to import the data on?
     
  5. latcarf

    latcarf New Member

    ah ha!
    Code:
    server1:~# mysql -V
    mysql  Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386)
    server1:~#
    and the system the dump came off of
    Code:
    server1:~# mysql -V
    mysql  Ver 14.12 Distrib 5.0.17, for suse-linux (i686) using readline 5.1
    server1:~#
    Can Debian use mysql5? and if so will
    Code:
    apt-get dist-upgrade mysql
    work?
     
  6. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    It's not that easy :eek:
    To install MySQL 5 (and perhaps PHP5), you might want to have a look at http://www.debian-administration.org/articles/357. You need backports to get Sarge up to speed. Or wait for the soon to be ready next Debian release, or switch to Ubuntu.

    As always: Linux offers a variety of choices.
     
  7. latcarf

    latcarf New Member

    lol... it's never easy but I seem to be having a hard enough time already to be taking on another challenge!
    I could never get a bootable iso for Ubuntu. Every time I downloaded, burned, and booted, it would boot to DOS as
    Code:
    [CR DOS]A:
    and while I could switch to the cd drive when I typed in "start" which seemed to be the executable for loading Ubuntu the screen would echo that the program could not be installed from DOS. I used the same box, burner, etc for Debian and Suse which both worked.

    How does Ubuntu differ from Debian? I am still dependent on GUI and Debian is dependent on Gnome and having used KDE in Suse I prefer KDE.
     
  8. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    Did you download the images from the Ubuntu site? I never had any problems downloading / burning ISO images for Ubuntu.
    A very bold statement would be: Ubuntu is one version ahead of Debian. It uses the testing branch of Debian and the add some specifics from there. If you are a KDE fan, there's Kunbutu http://www.kubuntu.org/index.php
     
  9. latcarf

    latcarf New Member

    I downloaded the kubuntu image and the same thing happens... when I boot to it it goes into Caldera DOS. I can get to the cd drive and see the files but can't do anything from DOS with them.
     
  10. martinfst

    martinfst ISPConfig Developer ISPConfig Developer

    How did you burn the CD? You need to burn it as a bootable iso image. All burning sw (at least the ones I know) have special options to burn these type of iso images.
     
  11. zcworld

    zcworld New Member

    @Latcarf

    did you get the SQL file problem fix

    if not
    check line 38
    change it from
    KEY `alb_category` (`category`)
    to
    KEY `alb_category` (`category`),

    anyway

    if your using an windows box
    open the file in a program called " context"
    and it will help you to see where any problems maybe
     
  12. latcarf

    latcarf New Member

    lol... it is a good thing I do not have a sharp mind I would hurt myself! I totally forgot to check image burn... thanks for the reminder. That booted it to kubuntu. I am going to try the complete install tomorrow.

    thanks again!
     
  13. latcarf

    latcarf New Member

    Not solved... what we did figure out was the sql versions were different. I am going to install a different setup that uses the same version the dump files were done in and that should eliminate the problem also.

    thanks for responding though! every little bit helps!! p.s. its a linux box... I do use windows also but for playing.
     
  14. zcworld

    zcworld New Member

    did it say what line it had a problem on
    like line error on line 240
     
  15. latcarf

    latcarf New Member

  16. zcworld

    zcworld New Member

    try using
    Code:
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `albums`;
    USE `albums`;
    
    
     
  17. latcarf

    latcarf New Member

    I really appreciate the help and will definitely remember this post if I still run into the same problem. Right now I am in the midst of a reinstall with a setup that I hope will be more in tune with what I have now, or at least with compatible software versions!
     

Share This Page