List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 26 2010 11:58am
Subject:Re: source backup.sql - troubleshoot
View as plain text  
A-ha ! :-)

That's an InnoDB table, allright. Can you check if your InnoDB file is set
to autoextend ? It smells like it's full and waiting for more space or
something.

Either look in your my.cnf file, or do a "show variables like '%inno%';".
Check the filesize of your InnoDB datafiles, too.


On Wed, May 26, 2010 at 1:27 PM, Sydney Puente <sydneypuente@stripped>wrote:

> Thanks Johan,
> Ah I see.
> So this line for example:
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
> generates the first
> Query OK, 0 rows affected (0.00 sec)
>
> I did not realise, it looks like a comment.
> Not sure about disk activity - top says 100% on 1 of the 4 CPUS
>
> Seems to be this causing problems
> DROP TABLE IF EXISTS `ping`;
> CREATE TABLE `ping` (
>  `TestName` varchar(50) default '',
>  `TimeStamp` int(11) default '0',
>  `Elapsedtime` int(11) default '0',
>  `Fail` int(11) default '0'
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> --
> -- Dumping data for table `ping`
> --
>
> LOCK TABLES `ping` WRITE;
> /*!40000 ALTER TABLE `ping` DISABLE KEYS */;
> I think it is this causing problems
> INSERT INTO `ping` VALUES
>
> ('Test1',1258368123,1,0),('Test1',1258368134,1,0),('Test1',1258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169,1,0)...
> ...
>
> TIA
>
> Syd
>
>
> ________________________________
> From: Johan De Meersman <vegivamp@stripped>
> To: Sydney Puente <sydneypuente@stripped>
> Cc: a.smith@stripped; mysql@stripped
> Sent: Wed, 26 May, 2010 11:35:22
> Subject: Re: source backup.sql - troubleshoot
>
> The "use event" statement is the one that results in the "database changed"
> message. You can easily count the result messages after that to find out
> which statement is hanging.
>
> It seems very strange to me that the import would just hang, and not exit
> with an error. Is there any disk activity ongoing ? Aren't your disks (or
> your innodb tablespace) full ? Is there anything in the systemlogs that
> might be relevant ?
>
>
>
> On Wed, May 26, 2010 at 12:18 PM, Sydney Puente <sydneypuente@stripped>
> wrote:
>
> >Hello,
> >>The invocation syntax is OK  I think.
> >>yes backup.sql was just a generic name.  I think the sql file could be
> called anything.
> >>And there is a use event
> >>The result of sourcing the sql file is to create only the 1st (of the
> expected 4 ) tables - and it hangs - I never get the mysql prompt back.
> >
> >
> >>-- MySQL dump 10.11
> >>--
> >>--     Database: event
> >>-- ------------------------------------------------------
> >>-- Server version       5.0.54a-enterprise
> >
> >>/*!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 */;
> >
> >>--
> >>-- Current Database: `event`
> >>--
> >
> >>CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT
> CHARACTER SET latin1 */;
> >
> >>USE `event`;
> >>....
> >
> >>Can I have the commands being issued echo to the screen somehow, so I can
> identify which command is causing the problem?
> >>Or investigate the problem in some other way?
> >
> >>TIA
> >
> >>-Syd
> >
> >>mysql> source /home/sydney/event/event_2010-05-25_02h07m.Tuesday.sql;
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Database changed
> >>Query OK, 0 rows affected (0.20 sec)
> >
> >>Query OK, 0 rows affected (0.12 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >>Query OK, 0 rows affected (0.00 sec)
> >
> >
> >
> >
> >
> >
> >>________________________________
> >>From: Johan De Meersman <vegivamp@stripped>
> >>To: a.smith@stripped
> >>Cc: Sydney Puente <sydneypuente@stripped>; mysql@stripped
> >>Sent: Wed, 26 May, 2010 10:15:47
> >>Subject: Re: source backup.sql - troubleshoot
> >
> >
> >
> >
> >
> >>On Wed, May 26, 2010 at 10:54 AM, <a.smith@stripped> wrote:
> >
> >
> >>>
> >>>Actually Im assuming a DB name of "source", which I mentioned in my
> first post. Thats actually an bad assumption as, as Ive just read, source is
> an alternative way to read in data from a file that Id never seen before.
> However the syntax would still seem to be bad, assuming the command is being
> run from the command prompt as opposed to the mysql command prompt.
> According to the man page the two options from the command prompt are:
> >>
> >>
> >>>>       shell> mysql db_name < backup-file.sql
> >>>>OR
> >>>>       shell> mysql -e "source /path-to-backup/backup-file.sql"
> db_name
> >>>
> >
> >>Ahh :-)
> >
> >>It is quite possible for the backup file to contain a "use mydatabase"
> statement - I usually do this, makes my restores easier. The clause is added
> automatically by mysqldump if you use the --databases parameter.
> >
> >>It could be argued, however, that this allows accidental restores of a
> production database, whereas the omittance of the use clause means that the
> client will barf as soon as you start the restore without target db
> specification, because you're trying to create objects outside of a
> database.
> >
> >>Both approaches are valid.
> >
> >
> >>--
> >>Bier met grenadyn
> >>Is als mosterd by den wyn
> >>Sy die't drinkt, is eene kwezel
> >>Hy die't drinkt, is ras een ezel
> >
> >
> >
> >>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
>
>
>
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Thread
source backup.sql - troubleshootSydney Puente25 May
  • RE: source backup.sql - troubleshootDaevid Vincent26 May
  • Re: source backup.sql - troubleshootRob Wultsch26 May
  • Re: source backup.sql - troubleshoota.smith26 May
    • Re: source backup.sql - troubleshootJohan De Meersman26 May
      • Re: source backup.sql - troubleshoota.smith26 May
        • Re: source backup.sql - troubleshootJohan De Meersman26 May
          • Re: source backup.sql - troubleshootSydney Puente26 May
            • Re: source backup.sql - troubleshootJohan De Meersman26 May
              • Re: source backup.sql - troubleshootSydney Puente26 May
                • Re: source backup.sql - troubleshootJohan De Meersman26 May
                  • Re: source backup.sql - troubleshootSydney Puente26 May
                    • Re: source backup.sql - troubleshootJohan De Meersman26 May
            • Re: source backup.sql - troubleshootDimitre Radoulov26 May