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 <sydneypuente@stripped>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 | 10000 |
> +---------------------------------+------------------------+
> 36 rows in set (0.00 sec)
>
>
>
> ________________________________
> From: Johan De Meersman <vegivamp@stripped>
> To: Sydney Puente <sydneypuente@stripped>
> Cc: a.smith@stripped; mysql@stripped
> 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 <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
>
>
>
>
>
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
