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 <vegiv...@tuxera.be>
To: Sydney Puente <sydneypue...@yahoo.com>
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 <sydneypue...@yahoo.com> 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 <vegiv...@tuxera.be>
>To: Sydney Puente <sydneypue...@yahoo.com>
>>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 <sydneypue...@yahoo.com>
>>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 <vegiv...@tuxera.be>
>>>>To: a.sm...@ukgrid.net
>>>>Cc: Sydney Puente <sydneypue...@yahoo.com>; 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, <a.sm...@ukgrid.net> 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
