source backup.sql - troubleshoot

Hello, I have a mysqldump file created by AutoMySQLBackup.=0AAnd it ha=
ngs when I do a =0Amysql > source backup.sql=0AIt is 32 MB - it creates 4 t=
ables and after creation of each table it populates it.=0AActually 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 comma=
nd it is that is the problem.=0AMy second thought is that there must be a q=
uite a few troubleshooting techniques i could/should use - except I dont kn=
ow hwtat they are. All advice gratefully received! BTW Platform r=
edhat 5.2, Server version 5.0.54a-enterprise -Syd =0A =



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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:sydneypuente@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=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: source backup.sql - troubleshoot

On Tue, May 25, 2010 at 2:39 PM, Sydney Puente wro=
te:
> 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 pop=
ulates 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 e=
xecuting 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 tec=
hniques i could/should use - except I dont know hwtat they are.
>
> All advice gratefully received!
>
> BTW Platform redhat 5.2, =A0 Server version =A0 =A0 =A0 5.0.54a-enterpris=
e
>
> -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.

--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.org

Re: source backup.sql - troubleshoot

--0016363b90ec3121ec04877b1d02
Content-Type: text/plain; charset=ISO-8859-1

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

--0016363b90ec3121ec04877b1d02--

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=gcdmg-mysql-2@m.gmane.org

Re: source backup.sql - troubleshoot

--005045013c622f87c304877bb8a3
Content-Type: text/plain; charset=ISO-8859-1

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

--005045013c622f87c304877bb8a3--

Re: source backup.sql - troubleshoot

--0-521777498-1274869113=:84746
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hello,=0AThe invocation syntax is OK I think.=0Ayes backup.sql was just a =
generic name. I think the sql file could be called anything.=0AAnd there i=
s a use event=0AThe result of sourcing the sql file is to create only the 1=
st (of the expected 4 ) tables - and it hangs - I never get the mysql promp=
t back. =0A-- MySQL dump 10.11=0A--=0A-- Database: event=0A-- ----=
--------------------------------------------------=0A-- Server version =
5.0.54a-enterprise /*!40101 SET @OLD_CHARACTER_SET_CLIENT=3D@@CHARAC=
TER_SET_CLIENT */;=0A/*!40101 SET @OLD_CHARACTER_SET_RESULTS=3D@@CHARACTER_=
SET_RESULTS */;=0A/*!40101 SET @OLD_COLLATION_CONNECTION=3D@@COLLATION_CONN=
ECTION */;=0A/*!40101 SET NAMES utf8 */;=0A/*!40103 SET @OLD_TIME_ZONE=3D@@=
TIME_ZONE */;=0A/*!40103 SET TIME_ZONE=3D'+00:00' */;=0A/*!40014 SET @OLD_U=
NIQUE_CHECKS=3D@@UNIQUE_CHECKS, UNIQUE_CHECKS=3D0 */;=0A/*!40014 SET @OLD_F=
OREIGN_KEY_CHECKS=3D@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=3D0 */;=0A/*!4=
0101 SET @OLD_SQL_MODE=3D@@SQL_MODE, SQL_MODE=3D'NO_AUTO_VALUE_ON_ZERO' */;=
=0A/*!40111 SET @OLD_SQL_NOTES=3D@@SQL_NOTES, SQL_NOTES=3D0 */; --=0A-=
- Current Database: `event`=0A-- CREATE DATABASE /*!32312 IF NOT EXIST=
S*/ `event` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `event`;=0A.=
.... Can I have the commands being issued echo to the screen somehow, s=
o I can identify which command is causing the problem?=0AOr investigate the=
problem in some other way? TIA -Syd=0A =0Amysql> source /home/sy=
dney/event/event_2010-05-25_02h07m.Tuesday.sql;=0AQuery 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 O=
K, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)=0A=
=0AQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.0=
0 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affe=
cted (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database cha=
nged=0AQuery 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) ________________________________=0AFro =
m: Johan De Meersman =0ATo: a.smith@ukgrid.net=0ACc: Sy=
dney Puente ; mysql@lists.mysql.com=0ASent: Wed, 26=
May, 2010 10:15:47=0ASubject: Re: source backup.sql - troubleshoot =
=0AOn Wed, May 26, 2010 at 10:54 AM, wrote:=0A=
>=0A>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, sour=
ce is an alternative way to read in data from a file that Id never seen bef=
ore. 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:=0A>=
=0A>> shell> mysql db_name < backup-file.sql=0A>>OR=0A>> shell>=
mysql -e "source /path-to-backup/backup-file.sql" db_name=0A> 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 ad=
ded automatically by mysqldump if you use the --databases parameter. I=
t could be argued, however, that this allows accidental restores of a produ=
ction database, whereas the omittance of the use clause means that the clie=
nt will barf as soon as you start the restore without target db specificati=
on, because you're trying to create objects outside of a database. Bot=
h approaches are valid. =0A-- =0ABier met grenadyn=0AIs als mosterd by=
den wyn=0ASy die't drinkt, is eene kwezel=0AHy die't drinkt, is ras een ez=
el
--0-521777498-1274869113=:84746--

Re: source backup.sql - troubleshoot

--0016e6465136d1dbe104877cd4cb
Content-Type: text/plain; charset=ISO-8859-1

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.smith@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

--0016e6465136d1dbe104877cd4cb--

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=gcdmg-mysql-2@m.gmane.org

Re: source backup.sql - troubleshoot

--0-1850613089-1274873220=:87754
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Thanks Johan,=0AAh I see.=0ASo this line for example: /*!40101 SET @OL=
D_CHARACTER_SET_CLIENT=3D@@CHARACTER_SET_CLIENT */;=0Agenerates the first=
=0AQuery OK, 0 rows affected (0.00 sec) I did not realise, it looks li=
ke a comment.=0ANot sure about disk activity - top says 100% on 1 of the 4 =
CPUS Seems to be this causing problems=0ADROP TABLE IF EXISTS `ping`;=
=0ACREATE TABLE `ping` (=0A `TestName` varchar(50) default '',=0A `TimeSt=
amp` int(11) default '0',=0A `Elapsedtime` int(11) default '0',=0A `Fail`=
int(11) default '0'=0A) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8; --=0A=
-- Dumping data for table `ping`=0A-- LOCK TABLES `ping` WRITE;=0A/*!4=
0000 ALTER TABLE `ping` DISABLE KEYS */;=0AI think it is this causing probl=
ems=0AINSERT INTO `ping` VALUES ('Test1',1258368123,1,0),('Test1',125836813=
4,1,0),('Test1',1258368144,0,0),('Test1',1258368158,4,0),('T est1',125836816=
9,1,0)...=0A... TIA Syd =0A__________________ ______________=
=0AFrom: Johan De Meersman =0ATo: Sydney Puente puente@yahoo.com>=0ACc: a.smith@ukgrid.net; mysql@lists.mysql.com=0ASent: W=
ed, 26 May, 2010 11:35:22=0ASubject: Re: source backup.sql - troubleshoot=
The "use event" statement is the one that results in the "database ch=
anged" 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 activ=
ity 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,=0A>>The invocation syntax is OK I think.=0A>>yes backup.sql was jus=
t a generic name. I think the sql file could be called anything.=0A>>And t=
here is a use event=0A>>The result of sourcing the sql file is to create on=
ly the 1st (of the expected 4 ) tables - and it hangs - I never get the mys=
ql prompt back.=0A>=0A>=0A>>-- MySQL dump 10.11=0A>>--=0A>>-- Database:=
event=0A>>-- ------------------------------------------------------=0A>>- -=
Server version 5.0.54a-enterprise=0A>=0A>>/*!40101 SET @OLD_CHARACTE=
R_SET_CLIENT=3D@@CHARACTER_SET_CLIENT */;=0A>>/*!40101 SET @OLD_CHARACTER_S=
ET_RESULTS=3D@@CHARACTER_SET_RESULTS */;=0A>>/*!40101 SET @OLD_COLLATION_CO=
NNECTION=3D@@COLLATION_CONNECTION */;=0A>>/*!40101 SET NAMES utf8 */;=0A>>/=
*!40103 SET @OLD_TIME_ZONE=3D@@TIME_ZONE */;=0A>>/*!40103 SET TIME_ZONE=3D'=
+00:00' */;=0A>>/*!40014 SET @OLD_UNIQUE_CHECKS=3D@@UNIQUE_CHECKS, UNIQUE_C=
HECKS=3D0 */;=0A>>/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=3D@@FOREIGN_KEY_CHEC=
KS, FOREIGN_KEY_CHECKS=3D0 */;=0A>>/*!40101 SET @OLD_SQL_MODE=3D@@SQL_MODE,=
SQL_MODE=3D'NO_AUTO_VALUE_ON_ZERO' */;=0A>>/*!40111 SET @OLD_SQL_NOTES=3D@=
@SQL_NOTES, SQL_NOTES=3D0 */;=0A>=0A>>--=0A>>-- Current Database: `event`=
=0A>>--=0A>=0A>>CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 D=
EFAULT CHARACTER SET latin1 */;=0A>=0A>>USE `event`;=0A>>....=0A>=0A>>Can I=
have the commands being issued echo to the screen somehow, so I can identi=
fy which command is causing the problem?=0A>>Or investigate the problem in =
some other way?=0A>=0A>>TIA=0A>=0A>>-Syd=0A>=0A>>mysql> source /home/sydney=
/event/event_2010-05-25_02h07m.Tuesday.sql;=0A>>Query OK, 0 rows affected (=
0.00 sec)=0A>=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=0A>>Query OK, 0 =
rows affected (0.00 sec)=0A>=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=
=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=0A>>Query OK, 0 rows affected=
(0.00 sec)=0A>=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=0A>>Query OK, =
0 rows affected (0.00 sec)=0A>=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=
=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=0A>>Query OK, 0 rows affected=
(0.00 sec)=0A>=0A>>Database changed=0A>>Query OK, 0 rows affected (0.20 se=
c)=0A>=0A>>Query OK, 0 rows affected (0.12 sec)=0A>=0A>>Query OK, 0 rows af=
fected (0.00 sec)=0A>=0A>>Query OK, 0 rows affected (0.00 sec)=0A>=0A>=0A>=
=0A>=0A>=0A>=0A>>________________________________=0A>>From: Johan De Meersm=
an =0A>>To: a.smith@ukgrid.net=0A>>Cc: Sydney Puente ydneypuente@yahoo.com>; mysql@lists.mysql.com=0A>>Sent: Wed, 26 May, 2010 1=
0:15:47=0A>>Subject: Re: source backup.sql - troubleshoot=0A>=0A>=0A>=0A>=
=0A>=0A>>On Wed, May 26, 2010 at 10:54 AM, wrote:=0A>=
=0A>=0A>>>=0A>>>Actually Im assuming a DB name of "source", which I mention=
ed 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 see=
n before. However the syntax would still seem to be bad, assuming the comma=
nd is being run from the command prompt as opposed to the mysql command pro=
mpt. According to the man page the two options from the command prompt are:=
=0A>>=0A>>=0A>>>> shell> mysql db_name < backup-file.sql=0A>>>>OR=0A>=
>>> shell> mysql -e "source /path-to-backup/backup-file.sql" db_name=
=0A>>>=0A>=0A>>Ahh :-)=0A>=0A>>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 --da=
tabases parameter.=0A>=0A>>It could be argued, however, that this allows ac=
cidental restores of a production database, whereas the omittance of the us=
e clause means that the client will barf as soon as you start the restore w=
ithout target db specification, because you're trying to create objects out=
side of a database.=0A>=0A>>Both approaches are valid.=0A>=0A>=0A>>--=0A>>B=
ier met grenadyn=0A>>Is als mosterd by den wyn=0A>>Sy die't drinkt, is eene=
kwezel=0A>>Hy die't drinkt, is ras een ezel=0A>=0A>=0A>=0A>> =
=0A-- =0ABier met grenadyn=0AIs als mosterd by den wyn=0ASy die't drinkt, i=
s eene kwezel=0AHy die't drinkt, is ras een ezel
--0-1850613089-1274873220=:87754--

Re: source backup.sql - troubleshoot

--005045013e91bc104804877dfc6e
Content-Type: text/plain; charset=ISO-8859-1

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;
> /*!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',1 258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169, 1,0)...
> ...
>
> TIA
>
> Syd
>
>
> ________________________________
> From: Johan De Meersman
> To: Sydney Puente
> Cc: a.smith@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.smith@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
>
>
>
>
>



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

--005045013e91bc104804877dfc6e--

Re: source backup.sql - troubleshoot

--0-740165995-1274879489=:2534
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Thanks.=0AGetting better informed by the minute!=0Aplenty of disk space (GB=
s) - datafiles small MB =0Amysql> show variables like '%inno%'; +=
---------------------------------+------------------------+= 0A| Variable_na=
me | Value |=0A+------------------------=
---------+------------------------+=0A| have_innodb | Y=
ES |=0A| innodb_additional_mem_pool_size | 20971520 =
|=0A| innodb_autoextend_increment | 8 |=
=0A| innodb_buffer_pool_awe_mem_mb | 0 |=0A| innodb_=
buffer_pool_size | 268435456 |=0A| innodb_checksums =
| ON |=0A| innodb_commit_concurrency =
| 0 |=0A| innodb_concurrency_tickets | 500 =
|=0A| innodb_data_file_path | ibdata1:10M:autoexten=
d |=0A| innodb_data_home_dir | |=0A| inno=
db_adaptive_hash_index | ON |=0A| innodb_doublewri=
te | ON |=0A| innodb_fast_shutdown =
| 1 |=0A| innodb_file_io_threads | 4 =
|=0A| innodb_file_per_table | ON =
|=0A| innodb_flush_log_at_trx_commit | 1 |=0A| i=
nnodb_flush_method | |=0A| innodb_force_=
recovery | 0 |=0A| innodb_lock_wait_timeout =
| 50 |=0A| innodb_locks_unsafe_for_binlog | OFF=
|=0A| innodb_log_arch_dir | =
|=0A| innodb_log_archive | OFF |=0A=
| innodb_log_buffer_size | 1048576 |=0A| innodb_log=
_file_size | 5242880 |=0A| innodb_log_files_in_gr=
oup | 2 |=0A| innodb_log_group_home_dir | =
../ |=0A| innodb_max_dirty_pages_pct | 90 =
|=0A| innodb_max_purge_lag | 0 |=
=0A| innodb_mirrored_log_groups | 1 |=0A| innodb_=
open_files | 300 |=0A| innodb_rollback_on_=
timeout | OFF |=0A| innodb_support_xa =
| ON |=0A| innodb_sync_spin_loops | 20 =
|=0A| innodb_table_locks | ON =
|=0A| innodb_thread_concurrency | 8 |=0A| inno=
db_thread_sleep_delay | 10000 |=0A+-----------------=
----------------+------------------------+=0A36 rows in set (0.00 sec)=0A=
=0A________________________________=0AFrom: Johan De Meersman mp@tuxera.be>=0ATo: Sydney Puente =0ACc: a.smith@uk=
grid.net; mysql@lists.mysql.com=0ASent: Wed, 26 May, 2010 12:58:07=0ASubjec=
t: Re: source backup.sql - troubleshoot A-ha ! :-) That's an Inno=
DB 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. Ei=
ther look in your my.cnf file, or do a "show variables like '%inno%';". Che=
ck the filesize of your InnoDB datafiles, too. On Wed, May 26, 2=
010 at 1:27 PM, Sydney Puente wrote: >Thanks =
Johan,=0A>>Ah I see.=0A>>So this line for example:=0A>=0A>=0A>>/*!40101 SET=
@OLD_CHARACTER_SET_CLIENT=3D@@CHARACTER_SET_CLIENT */;=0A>generates the fi=
rst=0A>=0A>Query OK, 0 rows affected (0.00 sec)=0A>=0A>I did not realise, i=
t looks like a comment.=0A>>Not sure about disk activity - top says 100% on=
1 of the 4 CPUS=0A>=0A>>Seems to be this causing problems=0A>>DROP TABLE I=
F EXISTS `ping`;=0A>>CREATE TABLE `ping` (=0A>> `TestName` varchar(50) def=
ault '',=0A>> `TimeStamp` int(11) default '0',=0A>> `Elapsedtime` int(11)=
default '0',=0A>> `Fail` int(11) default '0'=0A>>) ENGINE=3DInnoDB DEFAUL=
T CHARSET=3Dutf8;=0A>=0A>>--=0A>>-- Dumping data for table `ping`=0A>>--=0A=
>=0A>>LOCK TABLES `ping` WRITE;=0A>>/*!40000 ALTER TABLE `ping` DISABLE KEY=
S */;=0A>>I think it is this causing problems=0A>>INSERT INTO `ping` VALUES=
('Test1',1258368123,1,0),('Test1',1258368134,1,0),('Test1',1 258368144,0,0)=
,('Test1',1258368158,4,0),('Test1',1258368169,1,0)...=0A>>.. .=0A>=0A>>TIA=
=0A>=0A>>Syd=0A>=0A>=0A>=0A>>_______________________________ _=0A>>From: Joh=
an De Meersman =0A>To: Sydney Puente o.com>=0A>>Cc: a.smith@ukgrid.net; mysql@lists.mysql.com=0A>>Sent: Wed, 26 =
May, 2010 11:35:22=0A>=0A>Subject: Re: source backup.sql - troubleshoot=0A>=
=0A>>The "use event" statement is the one that results in the "database cha=
nged" message. You can easily count the result messages after that to find =
out which statement is hanging.=0A>=0A>>It seems very strange to me that th=
e import would just hang, and not exit with an error. Is there any disk act=
ivity ongoing ? Aren't your disks (or your innodb tablespace) full ? Is the=
re anything in the systemlogs that might be relevant ?=0A>=0A>=0A>=0A>>On W=
ed, May 26, 2010 at 12:18 PM, Sydney Puente wrote:=
=0A>=0A>>>Hello,=0A>>>>The invocation syntax is OK I think.=0A>>>>yes back=
up.sql was just a generic name. I think the sql file could be called anyth=
ing.=0A>>>>And there is a use event=0A>>>>The result of sourcing the sql fi=
le is to create only the 1st (of the expected 4 ) tables - and it hangs - I=
never get the mysql prompt back.=0A>>>=0A>>>=0A>>>>-- MySQL dump 10.11=0A>=
>>>--=0A>>>>-- Database: event=0A>>>>-- -------------------------------=
-----------------------=0A>>>>-- Server version 5.0.54a-enterprise=0A=
>>>=0A>>>>/*!40101 SET @OLD_CHARACTER_SET_CLIENT=3D@@CHARACTER_SET_CLIENT *=
/;=0A>>>>/*!40101 SET @OLD_CHARACTER_SET_RESULTS=3D@@CHARACTER_SET_RESULTS =
*/;=0A>>>>/*!40101 SET @OLD_COLLATION_CONNECTION=3D@@COLLATION_CONNECTION *=
/;=0A>>>>/*!40101 SET NAMES utf8 */;=0A>>>>/*!40103 SET @OLD_TIME_ZONE=3D@@=
TIME_ZONE */;=0A>>>>/*!40103 SET TIME_ZONE=3D'+00:00' */;=0A>>>>/*!40014 SE=
T @OLD_UNIQUE_CHECKS=3D@@UNIQUE_CHECKS, UNIQUE_CHECKS=3D0 */;=0A>>>>/*!4001=
4 SET @OLD_FOREIGN_KEY_CHECKS=3D@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=3D=
0 */;=0A>>>>/*!40101 SET @OLD_SQL_MODE=3D@@SQL_MODE, SQL_MODE=3D'NO_AUTO_VA=
LUE_ON_ZERO' */;=0A>>>>/*!40111 SET @OLD_SQL_NOTES=3D@@SQL_NOTES, SQL_NOTES=
=3D0 */;=0A>>>=0A>>>>--=0A>>>>-- Current Database: `event`=0A>>>>--=0A>>>=
=0A>>>>CREATE DATABASE /*!32312 IF NOT EXISTS*/ `event` /*!40100 DEFAULT CH=
ARACTER SET latin1 */;=0A>>>=0A>>>>USE `event`;=0A>>>>....=0A>>>=0A>>>>Can =
I have the commands being issued echo to the screen somehow, so I can ident=
ify which command is causing the problem?=0A>>>>Or investigate the problem =
in some other way?=0A>>>=0A>>>>TIA=0A>>>=0A>>>>-Syd=0A>>>=0A>>>>mysql> sour=
ce /home/sydney/event/event_2010-05-25_02h07m.Tuesday.sql;=0A>> >>Query OK, =
0 rows affected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=
=0A>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 r=
ows affected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A=
>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows=
affected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A>>>=
=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows af=
fected (0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A>>>=0A=
>>>>Database changed=0A>>>>Query OK, 0 rows affected (0.20 sec)=0A>>>=0A>>>=
>Query OK, 0 rows affected (0.12 sec)=0A>>>=0A>>>>Query OK, 0 rows affected=
(0.00 sec)=0A>>>=0A>>>>Query OK, 0 rows affected (0.00 sec)=0A>>>=0A>>>=0A=
>>>=0A>>>=0A>>>=0A>>>=0A>>>>________________________________ =0A>>>>From: Jo=
han De Meersman =0A>>>>To: a.smith@ukgrid.net=0A>>>>Cc:=
Sydney Puente ; mysql@lists.mysql.com=0A>>>>Sent: =
Wed, 26 May, 2010 10:15:47=0A>>>>Subject: Re: source backup.sql - troublesh=
oot=0A>>>=0A>>>=0A>>>=0A>>>=0A>>>=0A>>>>On Wed, May 26, 2010 at 10:54 AM, <=
a.smith@ukgrid.net> wrote:=0A>>>=0A>>>=0A>>>>>=0A>>>>>Actually Im assuming =
a DB name of "source", which I mentioned in my first post. Thats actually a=
n 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 st=
ill seem to be bad, assuming the command is being run from the command prom=
pt as opposed to the mysql command prompt. According to the man page the tw=
o options from the command prompt are:=0A>>=0A>>>=0A>>>>=0A>>>>>> she=
ll> mysql db_name < backup-file.sql=0A>>>>>>OR=0A>>>>>> shell> mysql =
-e "source /path-to-backup/backup-file.sql" db_name=0A>>>>>=0A>>>=0A>>>>Ahh=
:-)=0A>>>=0A>>>>It is quite possible for the backup file to contain a "use=
mydatabase" statement - I usually do this, makes my restores easier. The c=
lause is added automatically by mysqldump if you use the --databases parame=
ter.=0A>>=0A>>=0A>>>>It could be argued, however, that this allows accident=
al restores of a production database, whereas the omittance of the use clau=
se 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 o=
f a database.=0A>>=0A>>=0A>>>>Both approaches are valid.=0A>>>=0A>>>=0A>>>>=
--=0A>>>>Bier met grenadyn=0A>>>>Is als mosterd by den wyn=0A>>>>Sy die't d=
rinkt, is eene kwezel=0A>>>>Hy die't drinkt, is ras een ezel=0A>>>=0A>>>=0A=
>>>=0A>>>>=0A>=0A>=0A>>--=0A>>Bier met grenadyn=0A>>Is als mosterd by den w=
yn=0A>>Sy die't drinkt, is eene kwezel=0A>>Hy die't drinkt, is ras een ezel=
=0A>=0A>=0A>=0A>> =0A-- =0ABier met grenadyn=0AIs als mosterd by=
den wyn=0ASy die't drinkt, is eene kwezel=0AHy die't drinkt, is ras een ez=
el
--0-740165995-1274879489=:2534--

Re: source backup.sql - troubleshoot

--001485e98d083a75e204877f57eb
Content-Type: text/plain; charset=ISO-8859-1

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 | 10000 |
> +---------------------------------+------------------------+
> 36 rows in set (0.00 sec)
>
>
>
> ________________________________
> From: Johan De Meersman
> To: Sydney Puente
> Cc: a.smith@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;
> >>/*!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',1 258368144,0,0),('Test1',1258368158,4,0),('Test1',1258368169, 1,0)...
> >>...
> >
> >>TIA
> >
> >>Syd
> >
> >
> >
> >>________________________________
> >>From: Johan De Meersman
> >To: Sydney Puente
> >>Cc: a.smith@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.smith@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
> >
> >
> >
> >>
>
>
> --
> 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

--001485e98d083a75e204877f57eb--