The table '#sql_6d1b_0' is full during multitable UPDATE

[Jocelyn Fournier]

Hi,

I've just encountered a strange problem when trying to update a table :

UPDATE searchmainhardwarefr0, searchjoinhardwarefr0 SET
searchmainhardwarefr0.numeropost=searchjoinhardwarefr0.topic WHERE
searchmainhardwarefr0.numreponse=searchjoinhardwarefr0.numreponse;
ERROR 1114 (HY000): The table '#sql_11be_0' is full

searchjoinhardwarefr0 contains only 70624 rows, and searchmainhardwarefr0
contains 946113 rows.

However I succeed in updating the table with the following query :

UPDATE searchmainhardwarefr0, threadhardwarefr0 SET
searchmainhardwarefr0.numeropost=threadhardwarefr0.numeropost WHERE
searchmainhardwarefr0.numreponse=threadhardwarefr0.numreponse;

threadhardwarefr0 contains 76291 ans is also larger on the disk.

This sounds like a bug for me, but I want to be sure I didn't miss anything.

I'm using MySQL-4.1.8

Thanks,
  Jocelyn


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FLUSH syntax query

[Gleb Paharenko]

Hello.



Yes.





Karam Chand <[EMAIL PROTECTED]> wrote:

> So if I use FLUSH MASTERS and FLUSH SLAVES, it will

> have the same effect with new versions of MySQL too.

> 

> Karam

> --- Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> 

>> Hello.

>> 

>> > Also, from which version of MySQL LOCAL and

>> > NO_WRITE_TO_BINLOG options supported?

>> 4.1.1

>> 

>> RESET MASTER and RESET SLAVE were named FLUSH MASTER

>> and FLUSH SLAVES 

>> before MySQL 3.23.26.

>> 

>> Karam Chand <[EMAIL PROTECTED]> wrote:

>> > Hello,

>> > 

>> > From the MySQL docs:

>> > 

>> > http://dev.mysql.com/doc/mysql/en/FLUSH.html

>> > 

>> > What does FLUSH MASTER and FLUSH SLAVES does? Its

>> not

>> > documented?

>> > 

>> > Also, from which version of MySQL LOCAL and

>> > NO_WRITE_TO_BINLOG options supported?

>> > 

>> > Regards,

>> > Karam

>> > 

>> > __

>> > Do You Yahoo!?

>> > Tired of spam?  Yahoo! Mail has the best spam

>> protection around 

>> > http://mail.yahoo.com 

>> > 

>> 

>> 

>> 

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble w/ mysqldump (images attached)

[Gleb Paharenko]

Hello.



mysqldump usually produced

  SET NAMES utf8 

at the begining of the dump file. The clues may be in this. Send us

the output of such statements:

  SHOW CREATE TABLE avatardata;

  SHOW CREATE DATABASE 'put the name of the avatar database';

  SHOW VARIABLES LIKE '%char%';

and your my.cnf file. Use --default-character-set=latin1 command line option

for mysqldump.



[EMAIL PROTECTED] wrote:

> Hi Dr.

>  The avatars still show fine on 4.18a -- but the problem occurs when I 
> actually do a dump and reimport the dump file.  That's when something goes 
> array.. Kinda weird if you ask me.. I wish that vBulletin wouldn't actually 
> hard code the binary in a table, lol.. It's got me totally baffled! :)

> 

> 

> In a message dated 1/7/2005 4:02:04 AM Eastern Standard Time, "Dr. Frank 
> Ullrich" <[EMAIL PROTECTED]> writes:

> 

>>Hi,

>>

>>[EMAIL PROTECTED] schrieb:

>>

>>> Hi Tom,

>>> Thanks for the reply!  I show the following information for my DB,

>>> and shows the same for both the 3.23 DB And the 4.18a DB

>>> 

>>> Field     Type  Collation 

>>> avatardata  mediumtext  latin1_swedish_ci 

>>> 

>>> I pasted a data table from the bad avatar and the good avatar

>>> to a file differential program, there was no differential at all

>>> that the system found..

>>

>>that seems to point towards a client issue.

>>Which client do you use to look at the atachments (I think I have heard 

>>about problems with php and 4.1.x on this list recently)?

>>

>>As a further test I would suggest that you take the data table (.myd 

>>file?) from the 4.1.8 db and copy it into a __test__ 3.23 db replacing 

>>the data table there (it's myisam isn't it?). See if the avatars are ok 

>>when you read them from the 3.23 db.

>>

>>Regards,

>>   Frank.

>>

>>

>>> 

>>> I'm not too sure where or what to do to change this information? Do you mean

>>> that I recompile MySQL using different ./configure commands?

>>> 

>>> Thanks Tom!

>>> 

>>> 

>>> 

>>> [EMAIL PROTECTED]  wrote on Thursday, January

>>> 06, 2005 4:57 PM:

>>> 

>>> 

Sorry, forgot the attachments.  These are the same exact two

avatars from the same user, using my 3.23 backup, for the

good avatar, then the 4.18 bad avatar

>>> 

>>> 

>>> Looks like a character set issue - what's the column type, BLOB or TEXT or

>>> something in between?

>>> 

>>> This could be due to the server converting UTF-8 into a different character

>>> set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into

>>> 0x3F, which is "?" and often indicates that the character does not exist in

>>> the target collation. Basically, MySQL is treating the content as text, and

>>> replacing characters which it doesn't understand with "?". Try using a

>>> different collation or character set, and importing again?

>>> 

>>> Unfortunately, the conversion is not reversible - a set of characters have

>>> been replaced with a single character, so although the image is the same

>>> binary size, some of the data has been permanently lost unless you can

>>> restore from the backup.

>>> 

>>> cheers,

>>> 

>>> Tom

>>> 

>>> 

>>> In a message dated 1/6/2005 12:48:28 PM Eastern Standard Time, Tom 
>>> Molesworth <[EMAIL PROTECTED]> writes:

>>> 

>>> 

[EMAIL PROTECTED]  wrote on Thursday, January

06, 2005 4:57 PM:





>Sorry, forgot the attachments.  These are the same exact two

>avatars from the same user, using my 3.23 backup, for the

>good avatar, then the 4.18 bad avatar



Looks like a character set issue - what's the column type, BLOB or TEXT or

something in between?



This could be due to the server converting UTF-8 into a different character

set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into

0x3F, which is "?" and often indicates that the character does not exist in

the target collation. Basically, MySQL is treating the content as text, and

replacing characters which it doesn't understand with "?". Try using a

different collation or character set, and importing again?



Unfortunately, the conversion is not reversible - a set of characters have

been replaced with a single character, so although the image is the same

binary size, some of the data has been permanently lost unless you can

restore from the backup.



cheers,



Tom





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EM

Re: SHOW SLAVE STATUS hangs while LOAD DATA FROM MASTER runs (4.0.23)

[Gleb Paharenko]

Hello.



In bug database I haven't found any verified bugs. And there was

a non-official binaries. In most cases you should use official

binaries of the latest release. At:

  http://bugs.mysql.com/bug.php?id=4570

updating to the newest OS has solved the problem.





Chris Waterson <[EMAIL PROTECTED]> wrote:

> Hi!  I never saw a follow-up to the post about SHOW SLAVE STATUS 

> hanging while LOAD DATA FROM MASTER is running as of about 4.0.20 on. 

> ()

> 

> Is this a known bug?  Or a feature?

> 

> thanks!

> chris

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: GRANT can't grant with a password?

[Gleb Paharenko]

Hello.



As said at:

  http://dev.mysql.com/doc/mysql/en/SET_PASSWORD.html



Only clients with access to mysql database can set passwords for

other accounts.







"Joshua J. Kugler" <[EMAIL PROTECTED]> wrote:

> I've read the sections on GRANT's and permissions, and done some googling, 
> and 

> still haven't found what I'm looking for.

> 

> I have a user that has USAGE and GRANT global privs and all privs and GRANT 
> on 

> database rubric.

> 

> However, when they try to run this query:

> 

> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON rubric.* TO 
> 'user'@'localhost' 

> IDENTIFIED BY 'password';

> 

> They get the error

> 

> ERROR 1044: Access denied for user 'user'@'host' to database 'mysql'

> 

> They can log in just fine, so it is not a matter of host name.

> 

> I found a post that seemed to allude to the fact that a user with GRANT could 

> only create a new user via GRANT if there was not IDENTIFIED BY clause. 

> (However, a user with write permissions to the mysql database could).  I 

> verified this to be the case when this query,

> 

> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON rubric.* TO 'user'@'localhost'

> 

> run as the user in question, worked and created the user, albeit with no 

> password.

> 

> Is there a way for a user with GRANT privs to create a user *with* a password?

> 

> j- k-

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: easy way to drop a tempoary table created by Create view?

[Gleb Paharenko]

Hello.



Use -e command line option for mysql client program. For example:

  mysql -u root -p test -e 'drop view v'





sam <[EMAIL PROTECTED]> wrote:

> Hi,

> 

> What is the easiest way to drop a table created by "create view"?

> I m using mysql 5.0. I would like to execute the drop from Unix commandline.

> 

> Thanks

> Sam

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why DATETIME takes 8 bytes?

[Paul DuBois]

At 10:04 -0500 1/8/05, Frank Bax wrote:
At 06:00 AM 1/8/05, Ehud Shapira wrote:
I don't understand why DATETIME takes 8 bytes.  It's just a waste, 
since DATE+TIME take 6 bytes.  And in fact, while DATE and TIME are 
each rounded up to bytes on its own, a combined DATETIME should 
only take 5 bytes:

14 bits for year
04 bits for month
05 bits for day
05 bits for hour
06 bits for minutes
06 bits for seconds
---
40 bits

datetime is "displayed" as -MM-DD HH:MM:SS - it is *not* stored 
that way.  It is stored as a *nix timestamp - an integer number of 
seconds since 1970-01-01 00:00:00.  A 4-byte integer field has 
historically been used on *nix systems for this purpose, but this 
has an upper limit of 2038.  A larger *nix timestamp is now used to 
avoid the equivalent of Y2K in 2038.  The 8-byte *nix timestamp 
accommodates micro-seconds.
No, it's the TIMESTAMP type that's stored as second since the epoch.
DATETIME is stored like this:
Part 1 is a 32-bit integer containing year*1 + month*100 + day.
Part 2 is a 32-bit integer containing hour*1 + minute*100 + second.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Why DATETIME takes 8 bytes?

[Frank Bax]

At 12:15 PM 1/8/05, Tom Crimmins wrote:
[snip]
datetime is "displayed" as -MM-DD HH:MM:SS - it is *not* stored that
way.  It is stored as a *nix timestamp - an integer number of seconds since
1970-01-01 00:00:00.
[/snip]
Actually datetime is not stored as epoch time. It has a range from
1000-01-01 00:00:00 to -12-31 23:59:59 because it is a combination of a
date and a time field as Neculai wrote. You may be thinking of a timestamp,
which is tored as a 4 byte int.

Thanks for correction.  The lower limit on my 4.0.20 system appears to be 
0001-01-01 00:00:00. Also, 4.0.20 appears to accept invalid dates, such as 
those with zero for year, month or day.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Why DATETIME takes 8 bytes?

[Tom Crimmins]

[snip]
datetime is "displayed" as -MM-DD HH:MM:SS - it is *not* stored that
way.  It is stored as a *nix timestamp - an integer number of seconds since
1970-01-01 00:00:00.
[/snip]

Actually datetime is not stored as epoch time. It has a range from
1000-01-01 00:00:00 to -12-31 23:59:59 because it is a combination of a
date and a time field as Neculai wrote. You may be thinking of a timestamp,
which is tored as a 4 byte int.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
From: Frank Bax [mailto:[EMAIL PROTECTED] 
Sent: Saturday, January 08, 2005 9:04 AM
To: mysql@lists.mysql.com
Subject: Re: Why DATETIME takes 8 bytes?

At 06:00 AM 1/8/05, Ehud Shapira wrote:
>I don't understand why DATETIME takes 8 bytes.  It's just a waste, 
>since
>DATE+TIME take 6 bytes.  And in fact, while DATE and TIME are each 
>DATE+rounded
>up to bytes on its own, a combined DATETIME should only take 5 bytes:
>
>14 bits for year
>04 bits for month
>05 bits for day
>05 bits for hour
>06 bits for minutes
>06 bits for seconds
>---
>40 bits


datetime is "displayed" as -MM-DD HH:MM:SS - it is *not* stored that
way.  It is stored as a *nix timestamp - an integer number of seconds since
1970-01-01 00:00:00.  A 4-byte integer field has historically been used on
*nix systems for this purpose, but this has an upper limit of 2038.  A
larger *nix timestamp is now used to avoid the equivalent of Y2K in 2038.
The 8-byte *nix timestamp accommodates micro-seconds.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Why DATETIME takes 8 bytes?

[Frank Bax]

At 06:00 AM 1/8/05, Ehud Shapira wrote:
I don't understand why DATETIME takes 8 bytes.  It's just a waste, since 
DATE+TIME take 6 bytes.  And in fact, while DATE and TIME are each rounded 
up to bytes on its own, a combined DATETIME should only take 5 bytes:

14 bits for year
04 bits for month
05 bits for day
05 bits for hour
06 bits for minutes
06 bits for seconds
---
40 bits

datetime is "displayed" as -MM-DD HH:MM:SS - it is *not* stored that 
way.  It is stored as a *nix timestamp - an integer number of seconds since 
1970-01-01 00:00:00.  A 4-byte integer field has historically been used on 
*nix systems for this purpose, but this has an upper limit of 2038.  A 
larger *nix timestamp is now used to avoid the equivalent of Y2K in 
2038.  The 8-byte *nix timestamp accommodates micro-seconds.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with MySQL 4.1.8 Not starting under Fedora Core3

[Neculai Macarie]

> When I su mysql and run mysqld I get this:
> 050107 14:09:12 [Warning] Asked for 196608 thread stack, but got 126976
> mysqld: Can't create/write to file '/tmp/ib5ArcLz' (Errcode: 13)
> 050107 14:09:12  InnoDB: Error: unable to create temporary file; errno: 13
> 050107 14:09:12 [ERROR] Can't init databases
> 050107 14:09:12 [ERROR] Aborting

Make sure that mysql user can write to the /tmp directory (error 13 is
Permission denied).

-- 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Why DATETIME takes 8 bytes?

[Ehud Shapira]

Greetings.
I don't understand why DATETIME takes 8 bytes.  It's just a waste, since 
DATE+TIME take 6 bytes.  And in fact, while DATE and TIME are each rounded 
up to bytes on its own, a combined DATETIME should only take 5 bytes:

14 bits for year
04 bits for month
05 bits for day
05 bits for hour
06 bits for minutes
06 bits for seconds
---
40 bits
Any thoughts?
Ehud Shapira.
_
Don't just search. Find. Check out the new MSN Search! 
http://search.msn.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: lock the tables

[Tom Crimmins]

Correct, if the form generates independent insert statements then they will
not "bump into each other", even with an auto_increment.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
From: leegold
Sent: Saturday, January 08, 2005 4:46 AM
To: No name
Subject: RE: lock the tables


On Sat, 8 Jan 2005 04:06:44 -0600 , "Tom Crimmins" said:
> Unless your form is changing the same rows and order of operations is 
> important (which is probably not the case), there is no need to lock 
> the tables. So if each time the form is submitted it inserts a new 
> row, there is no reason to lock the tables.

So in MYSQL/PHP different users inserting data into the same tables (w/an
autoincremt as the PK) - they will not bump into each other Under "normal"
circumstances. Correct?
If they did i will know it very fast:^)

> 
> ---
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 
> -Original Message-
> From: leegold
> Sent: Saturday, January 08, 2005 3:39 AM
> To: No name
> Subject: lock the tables
> 
> I'm going to have a php web form that potentially many users will use 
> to insert into a MYSQL DB, maybe they will try at the same time. Do I 
> have to lock the tables that are being populated?
> 
> Will a solution queue insert requests or just say I am busy?
> 
> Thanks,
> Lee

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: lock the tables

[leegold]

On Sat, 8 Jan 2005 04:06:44 -0600 , "Tom Crimmins"
<[EMAIL PROTECTED]> said:
> Unless your form is changing the same rows and order of operations is
> important (which is probably not the case), there is no need to lock the
> tables. So if each time the form is submitted it inserts a new row, there
> is
> no reason to lock the tables.

So in MYSQL/PHP different users inserting data into the same tables
(w/an autoincremt as the PK) - they will not bump into each other Under
"normal" circumstances. Correct?
If they did i will know it very fast:^)




> 
> ---
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 
> -Original Message-
> From: leegold
> Sent: Saturday, January 08, 2005 3:39 AM
> To: No name
> Subject: lock the tables
> 
> I'm going to have a php web form that potentially many users will use to
> insert into a MYSQL DB, maybe they will try at the same time. Do I have
> to
> lock the tables that are being populated? 
> 
> Will a solution queue insert requests or just say I am busy?
> 
> Thanks,
> Lee

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: lock the tables

[Tom Crimmins]

Unless your form is changing the same rows and order of operations is
important (which is probably not the case), there is no need to lock the
tables. So if each time the form is submitted it inserts a new row, there is
no reason to lock the tables.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

-Original Message-
From: leegold
Sent: Saturday, January 08, 2005 3:39 AM
To: No name
Subject: lock the tables

I'm going to have a php web form that potentially many users will use to
insert into a MYSQL DB, maybe they will try at the same time. Do I have to
lock the tables that are being populated? 

Will a solution queue insert requests or just say I am busy?

Thanks,
Lee

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: lock the tables

[Jigal van Hemert]

> I'm going to have a php web form that potentially many users will use to
> insert into a MYSQL DB, maybe they will try at the same time. Do I have
> to lock the tables that are being populated?

You can use inserts, updates and selects without locking tables as long as
you don't need the table to remain constant between two queries.

Locking tables is useful for e.g.
- select the information you need
- do some extra calculations
- update the records you have previously selected
There are situations where you want to be certain that between the first and
the third step no information was altered in one or more tables.

With all the variations in syntax for inserts and updates (multi table
updates, insert...select, etc.) you will need table locks rarely.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

lock the tables

[leegold]

I'm going to have a php web form that potentially many users will use to
insert into a MYSQL DB, maybe they will try at the same time. Do I have
to lock the tables that are being populated? 

Will a solution queue insert requests or just say I am busy?

Thanks,
Lee

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]