List:General Discussion« Previous MessageNext Message »
From:Sydney Puente Date:May 26 2010 1:11pm
Subject:Re: source backup.sql - troubleshoot
View as plain text  
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



      
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