Re: source backup.sql - troubleshoot
Hmm, probably not that, then. Strange. You already said there's 100% cpu on one core while that executes. Can you see if there's disk activity going on ? After you kill the script, can you check the contents of that table, to see if any data from the hanging statement is in there ? Can you try to run the statement by hand, to see if it executes ? I have to admit I'm a bit lost, here. Whenever I see a MySQL server hanging, it tends to be because it's waiting for me to free up space so it can continue writing logs or whatever. On Wed, May 26, 2010 at 3:11 PM, Sydney Puente wrote: > Thanks. > Getting better informed by the minute! > plenty of disk space (GBs) - datafiles small MB > > > mysql> show variables like '%inno%'; > > +-++ > | Variable_name | Value | > +-++ > | have_innodb | YES| > | innodb_additional_mem_pool_size | 20971520 | > | innodb_autoextend_increment | 8 | > | innodb_buffer_pool_awe_mem_mb | 0 | > | innodb_buffer_pool_size | 268435456 | > | innodb_checksums| ON | > | innodb_commit_concurrency | 0 | > | innodb_concurrency_tickets | 500| > | innodb_data_file_path | ibdata1:10M:autoextend | > | innodb_data_home_dir|| > | innodb_adaptive_hash_index | ON | > | innodb_doublewrite | ON | > | innodb_fast_shutdown| 1 | > | innodb_file_io_threads | 4 | > | innodb_file_per_table | ON | > | innodb_flush_log_at_trx_commit | 1 | > | innodb_flush_method || > | innodb_force_recovery | 0 | > | innodb_lock_wait_timeout| 50 | > | innodb_locks_unsafe_for_binlog | OFF| > | innodb_log_arch_dir || > | innodb_log_archive | OFF| > | innodb_log_buffer_size | 1048576| > | innodb_log_file_size| 5242880| > | innodb_log_files_in_group | 2 | > | innodb_log_group_home_dir | ./ | > | innodb_max_dirty_pages_pct | 90 | > | innodb_max_purge_lag| 0 | > | innodb_mirrored_log_groups | 1 | > | innodb_open_files | 300| > | innodb_rollback_on_timeout | OFF| > | innodb_support_xa | ON | > | innodb_sync_spin_loops | 20 | > | innodb_table_locks | ON | > | innodb_thread_concurrency | 8 | > | innodb_thread_sleep_delay | 1 | > +-++ > 36 rows in set (0.00 sec) > > > > ________ > From: Johan De Meersman > To: Sydney Puente > Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com > Sent: Wed, 26 May, 2010 12:58:07 > Subject: Re: source backup.sql - troubleshoot > > 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 > 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; &
Re: source backup.sql - troubleshoot
Thanks.
Getting better informed by the minute!
plenty of disk space (GBs) - datafiles small MB
mysql> show variables like '%inno%';
+-++
| Variable_name | Value |
+-++
| have_innodb | YES|
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 268435456 |
| innodb_checksums| ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500|
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir||
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown| 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method ||
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout| 50 |
| innodb_locks_unsafe_for_binlog | OFF|
| innodb_log_arch_dir ||
| innodb_log_archive | OFF|
| innodb_log_buffer_size | 1048576|
| innodb_log_file_size| 5242880|
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag| 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300|
| innodb_rollback_on_timeout | OFF|
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 1 |
+-++
36 rows in set (0.00 sec)
From: Johan De Meersman
To: Sydney Puente
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
Sent: Wed, 26 May, 2010 12:58:07
Subject: Re: source backup.sql - troubleshoot
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 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;
>>/*!4 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
>To: Sydney Puente
>>Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
>>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 er
Re: source backup.sql - troubleshoot
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 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;
> /*!4 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
> To: Sydney Puente
> Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
> 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
> 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
> >>Q
Re: source backup.sql - troubleshoot
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;
/*!4 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
To: Sydney Puente
Cc: a.sm...@ukgrid.net; mysql@lists.mysql.com
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 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
>>To: a.sm...@ukgrid.net
>>Cc: Sydney Puente ; mysql@lists.mysql.com
>>Sent: Wed, 26 May, 2010 10:15:47
>>Subject: Re: source backup.sql - troubleshoot
>
>
>
>
>
>>On Wed, May 26, 2010 at 10:54 AM, 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
Re: source backup.sql - troubleshoot
On 26/05/2010 12.18, Sydney Puente wrote: [...] 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? [...] Not sure if this was already mentioned: mysql --show-warnings -v -u -p < dump_file You can use -vv or -vvv for more verbose output. Regards Dimitre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 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 > To: a.sm...@ukgrid.net > Cc: Sydney Puente ; mysql@lists.mysql.com > Sent: Wed, 26 May, 2010 10:15:47 > Subject: Re: source backup.sql - troubleshoot > > > > > On Wed, May 26, 2010 at 10:54 AM, 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
Re: source backup.sql - troubleshoot
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 To: a.sm...@ukgrid.net Cc: Sydney Puente ; mysql@lists.mysql.com Sent: Wed, 26 May, 2010 10:15:47 Subject: Re: source backup.sql - troubleshoot On Wed, May 26, 2010 at 10:54 AM, 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
Re: source backup.sql - troubleshoot
On Wed, May 26, 2010 at 10:54 AM, 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
Re: source backup.sql - troubleshoot
Quoting Johan De Meersman : On Wed, May 26, 2010 at 10:20 AM, wrote: If you are trying to restore from backup.sql to a database called soure then your syntax is wrong. You should be running: mysql source < backup.sql You are assuming that the file named "backup.sql" contains a backup of the database named "backup". There is no grounds in the mail that support that assumption; I would even say that 'backup.sql' is a rather generic name for a file containing a backup. Such naming is not uncommon for a one-shot backup; or it might be used as a placeholder in an explanation. 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 thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
On Wed, May 26, 2010 at 10:20 AM, wrote: > If you are trying to restore from backup.sql to a database called soure > then your syntax is wrong. You should be running: > > mysql source < backup.sql > You are assuming that the file named "backup.sql" contains a backup of the database named "backup". There is no grounds in the mail that support that assumption; I would even say that 'backup.sql' is a rather generic name for a file containing a backup. Such naming is not uncommon for a one-shot backup; or it might be used as a placeholder in an explanation. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: source backup.sql - troubleshoot
Quoting Sydney Puente : Hello, I have a mysqldump file created by AutoMySQLBackup. And it hangs when I do a mysql > source backup.sql If you are trying to restore from backup.sql to a database called soure then your syntax is wrong. You should be running: mysql source < backup.sql Assuming you don´t need a password to connect to mysql... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
On Tue, May 25, 2010 at 2:39 PM, Sydney Puente wrote: > Hello, > > I have a mysqldump file created by AutoMySQLBackup. > And it hangs when I do a > mysql > source backup.sql > It is 32 MB - it creates 4 tables and after creation of each table it > populates it. > Actually it only creates the 1st table before hanging. > > My first thought is it would be nice to echo each of the commands it is > executing so I can tell whoch command it is that is the problem. > My second thought is that there must be a quite a few troubleshooting > techniques i could/should use - except I dont know hwtat they are. > > All advice gratefully received! > > BTW Platform redhat 5.2, Server version 5.0.54a-enterprise > > -Syd The easiest way to see what MySQL is working on is to start up another connections and run "SHOW PROCESSLIST". Note that the command output will be truncated if it is long at all. If you need to see the entire query run "SHOW FULL PROCESSLIST". Alternatively start up the mysql client with the --verbose flag. I don't recall in what version that became available. You will probably flood your term if you use this option. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: source backup.sql - troubleshoot
Use something like SQLYog, load your .sql file into the editor, run all the commands one at a time, or in bulk. Look at the "info" window for what line failed. > -Original Message- > From: Sydney Puente [mailto:sydneypue...@yahoo.com] > Sent: Tuesday, May 25, 2010 2:39 PM > To: mysql@lists.mysql.com > Subject: source backup.sql - troubleshoot > > Hello, > > I have a mysqldump file created by AutoMySQLBackup. > And it hangs when I do a > mysql > source backup.sql > It is 32 MB - it creates 4 tables and after creation of each > table it populates it. > Actually it only creates the 1st table before hanging. > > My first thought is it would be nice to echo each of the > commands it is executing so I can tell whoch command it is > that is the problem. > My second thought is that there must be a quite a few > troubleshooting techniques i could/should use - except I dont > know hwtat they are. > > All advice gratefully received! > > BTW Platform redhat 5.2, Server version 5.0.54a-enterprise > > -Syd > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
