Strange problem with mysqldump / automysqlbackup (ERROR 1300)

--Apple-Mail-4--892927405
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

I've been using automysqlbackup 2.5 for years on a particular database, =
and it's always performed great. Recently, however, I've become =
encountering problems when trying to re-import one of its dumped sql =
files. (Not sure if it matters, but the database file in question is =
large and growing -- about 10GB. The other databases automysqlbackup =
backs up are fine.)

Basically on the import, MySQL fails and returns an error indicating a =
problem with the dump file:

mysql -u root -p < dump_file.sql (~10GB)=20
Enter password:=20
ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422'

Sure enough, I look at the line in dump_file.sql, which should contain =
two unsigned ints, and two unsigned small ints:

[...],(32562206,1228?03422,1641,135),[...]

And yup, there's a question mark in the middle of the second unsigned =
int, for some strange reason. Not in any of the other rows in that =
statement. When I look at the existing database from which the dump file =
was made, that row is fine:

mysql> SELECT * FROM bandwidth WHERE id =3D 32562206;
+----------+------------+-----------+-------+
| id | time_sec | device_id | bytes |
+----------+------------+-----------+-------+
| 32562206 | 1228803422 | 1641 | 135 |
+----------+------------+-----------+-------+
1 row in set (0.00 sec)


So... It appears either mysqldump and/or automysqlbackup is having a =
problem dumping a true copy of the database.

Anyone else run into this sort of thing? Any suggestions? Thanks.

....Rene=

--Apple-Mail-4--892927405--

RE: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

Have you tried dumping that table manually using mysqldump on the command l=
ine to confirm it's not an issue with automysqlbackup?

Regards,
Gavin Towey

-----Original Message-----
From: Ren=E9 Fournier [mailto:m5@renefournier.com]
Sent: Friday, November 20, 2009 8:31 AM
To: mysql
Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

I've been using automysqlbackup 2.5 for years on a particular database, and=
it's always performed great. Recently, however, I've become encountering p=
roblems when trying to re-import one of its dumped sql files. (Not sure if =
it matters, but the database file in question is large and growing -- about=
10GB. The other databases automysqlbackup backs up are fine.)

Basically on the import, MySQL fails and returns an error indicating a prob=
lem with the dump file:

mysql -u root -p < dump_file.sql (~10GB)
Enter password:
ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422'

Sure enough, I look at the line in dump_file.sql, which should contain two =
unsigned ints, and two unsigned small ints:

[...],(32562206,1228?03422,1641,135),[...]

And yup, there's a question mark in the middle of the second unsigned int, =
for some strange reason. Not in any of the other rows in that statement. Wh=
en I look at the existing database from which the dump file was made, that =
row is fine:

mysql> SELECT * FROM bandwidth WHERE id =3D 32562206;
+----------+------------+-----------+-------+
| id | time_sec | device_id | bytes |
+----------+------------+-----------+-------+
| 32562206 | 1228803422 | 1641 | 135 |
+----------+------------+-----------+-------+
1 row in set (0.00 sec)


So... It appears either mysqldump and/or automysqlbackup is having a proble=
m dumping a true copy of the database.

Anyone else run into this sort of thing? Any suggestions? Thanks.

....Rene

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
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: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

Yes, still exhibits this problem -- although at a different line in the =
file. (Seems random.) I should also mention, the backup is running =
across a local network. The machine the starts automysqlbackup (and =
internally, mysqldump) connects over TCP/IP to the database server. I am =
going to try running mysqldump on the database server itself, but it's =
not something I can do during normal hours -- plus at 10GB, it's a =
time-consuming thing to test. (Would it matter? I guess I need to find =
out.)

In any case, it seems clear that during the dump, mysqldump is =
periodically adding a character to the dump file.

On 2009-11-20, at 12:41 PM, Gavin Towey wrote:

> Have you tried dumping that table manually using mysqldump on the =
command line to confirm it's not an issue with automysqlbackup?
>=20
> Regards,
> Gavin Towey
>=20
> -----Original Message-----
> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
> Sent: Friday, November 20, 2009 8:31 AM
> To: mysql
> Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
>=20
> I've been using automysqlbackup 2.5 for years on a particular =
database, and it's always performed great. Recently, however, I've =
become encountering problems when trying to re-import one of its dumped =
sql files. (Not sure if it matters, but the database file in question is =
large and growing -- about 10GB. The other databases automysqlbackup =
backs up are fine.)
>=20
> Basically on the import, MySQL fails and returns an error indicating a =
problem with the dump file:
>=20
> mysql -u root -p < dump_file.sql (~10GB)
> Enter password:
> ERROR 1300 (HY000) at line 426: Invalid utf8 character string: =
'?03422'
>=20
> Sure enough, I look at the line in dump_file.sql, which should contain =
two unsigned ints, and two unsigned small ints:
>=20
> [...],(32562206,1228?03422,1641,135),[...]
>=20
> And yup, there's a question mark in the middle of the second unsigned =
int, for some strange reason. Not in any of the other rows in that =
statement. When I look at the existing database from which the dump file =
was made, that row is fine:
>=20
> mysql> SELECT * FROM bandwidth WHERE id =3D 32562206;
> +----------+------------+-----------+-------+
> | id | time_sec | device_id | bytes |
> +----------+------------+-----------+-------+
> | 32562206 | 1228803422 | 1641 | 135 |
> +----------+------------+-----------+-------+
> 1 row in set (0.00 sec)
>=20
>=20
> So... It appears either mysqldump and/or automysqlbackup is having a =
problem dumping a true copy of the database.
>=20
> Anyone else run into this sort of thing? Any suggestions? Thanks.
>=20
> ...Rene
>=20
> The information contained in this transmission may contain privileged =
and confidential information. It is intended only for the use of the =
person(s) named above. If you are not the intended recipient, you are =
hereby notified that any review, dissemination, distribution or =
duplication of this communication is strictly prohibited. If you are not =
the intended recipient, please contact the sender by reply email and =
destroy all copies of the original message.


--
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: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

--Apple-Mail-5--789503472
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=utf-8

It's just odd, because that error was on 5.0. I'm running 5.1.39 -- =
surely enough time for the MySQL devs to fix it right?

Could it be with extended inserts that the max statement length is =
greater than the default 16MB max allowed packet? I'm increasing that =
value to 256 MB and going to try again.

Thanks for the idea though. At least I'm not the only one to have =
encountered that error.

....Rene

On 2009-11-21, at 5:44 AM, 王浩 wrote:

> i googled the problem,it says that's a mysql bug.This is the source =
page =
:http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#bac kup_restore,
> And i suggest you ignore the error when import or correct the dump to =
the source database and then import again.
>=20
> Best wishes!


--Apple-Mail-5--789503472--

Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

--001485f8d1828e1f820478ea777c
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

There is a bug in mysqldump that prevents mysql from version 5.0.80 from
importing dumps from a old version of mysqldump.
I had this verified by MySQL support.
You can have a look at this bug: http://bugs.mysql.com/bug.php?id=3D41486

Cheers

Claudio



2009/11/21 René Fournier

> It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surel=
y
> enough time for the MySQL devs to fix it right?
>
> Could it be with extended inserts that the max statement length is greate=
r
> than the default 16MB max allowed packet? I'm increasing that value to 25=
6
> MB and going to try again.
>
> Thanks for the idea though. At least I'm not the only one to have
> encountered that error.
>
> ...Rene
>
> On 2009-11-21, at 5:44 AM, 王浩 wrote:
>
> > i googled the problem,it says that's a mysql bug.This is the source pag=
e
> :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#bac kup_restore,
> > And i suggest you ignore the error when import or correct the dump to t=
he
> source database and then import again.
> >
> > Best wishes!
>
>


--=20
Claudio

--001485f8d1828e1f820478ea777c--

Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

--Apple-Mail-6--758093796
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=utf-8

Except that, in my case, both machines are running 5.1.39. Mac "OS X" =
PPC 64-bit. I tried importing another, different dump file of the same =
database, and this time mysql returned a different error:

Sat Nov 21 13:16:20 -- Minas-Tirith :: mysql -u root -p < dump_file.sql=20=

Enter password:=20
ERROR 1136 (21S01) at line 6300: Column count doesn't match value count =
at row 6253

So, a different error. And it appears that each time mysqldump runs, the =
resulting file has different problem.


On 2009-11-21, at 5:05 PM, Claudio Nanni wrote:

> There is a bug in mysqldump that prevents mysql from version 5.0.80 =
from
> importing dumps from a old version of mysqldump.
> I had this verified by MySQL support.
> You can have a look at this bug: =
http://bugs.mysql.com/bug.php?id=3D41486
>=20
> Cheers
>=20
> Claudio
>=20
>=20
>=20
> 2009/11/21 René Fournier
>=20
>> It's just odd, because that error was on 5.0. I'm running 5.1.39 -- =
surely
>> enough time for the MySQL devs to fix it right?
>>=20
>> Could it be with extended inserts that the max statement length is =
greater
>> than the default 16MB max allowed packet? I'm increasing that value =
to 256
>> MB and going to try again.
>>=20
>> Thanks for the idea though. At least I'm not the only one to have
>> encountered that error.
>>=20
>> ...Rene
>>=20
>> On 2009-11-21, at 5:44 AM, 王浩 wrote:
>>=20
>>> i googled the problem,it says that's a mysql bug.This is the source =
page
>> =
:http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#bac kup_restore,
>>> And i suggest you ignore the error when import or correct the dump =
to the
>> source database and then import again.
>>>=20
>>> Best wishes!
>>=20
>>=20
>=20
>=20
> --=20
> Claudio


--Apple-Mail-6--758093796--

Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)

--Apple-Mail-11--611670150
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1

So, as a followup, I ran mysqldump on the actual server (with the output =
directed over AFP to another machine on the network) -- as opposed to =
running mysqldump on the destination server and connecting to the =
databases over TCP/IP (both are running 5.1.39 PPC 64-bit). *That* dump =
file imported OK. So...

It appears there's a bug in mysqldump that occurs intermittently when =
using TCP/IP connections to the MySQL database server. I haven't seen =
this bug occur on smaller databases, but it was pretty consistent on my =
non-damaged database of 9GB+.

Anyway, for now I'm going to run automysqlbackup on the actual machine =
and simply dump to an external drive or another server on the network.

On 2009-11-20, at 12:58 PM, Ren=E9 Fournier wrote:

> Yes, still exhibits this problem -- although at a different line in =
the file. (Seems random.) I should also mention, the backup is running =
across a local network. The machine the starts automysqlbackup (and =
internally, mysqldump) connects over TCP/IP to the database server. I am =
going to try running mysqldump on the database server itself, but it's =
not something I can do during normal hours -- plus at 10GB, it's a =
time-consuming thing to test. (Would it matter? I guess I need to find =
out.)


--Apple-Mail-11--611670150--