MySql Version 4.xx problems with update/Table full

1) installation
OS = Windows 2000
MySql = 4.0.12-nt (binary download from you site)

All tables are myISAM

2) description

2.1) Problem1 :

I have one table (calc_t1) that contains 489 875 rows. The total data size
reported by show status is 155 400 900
I have another table (calc_t9) that contains 489 875 rows. The total data
size reported by show status 46 252 364

Both table uses the same primary key, made of five fields declared as
varchar(50). Actually, none of them exceeds 12 characters (note: later,I
tried to reduce the declared field size, but the problem described below
still occurs)

The keys are the same in both files (checked by doing a Select count(*)
from Calc_T1 inner join Calc_T9 using (....), which returned 489875)


Now I want to move some data from Calc_T9 to Calc_T1.

I tried something like

UPDATE Calc_T1,Calc_T9
set calc_T1.xxx= calc_T9.xxx, Calc_T1.yyy=calc_T9.yyy, ...
where (the join condition with all five fields that make the key)

and I receive the message ERROR 1114 : Table Calc_T1 full.

Note that all updated fields are declared 'double'

I also tried
Update Calc_T1 inner join Calc_T9 using (....) set calc_T1.xxx=
calc_T9.xxx, Calc_T1.yyy=calc_T9.yyy, ...

without more success.

The variable 'big_tables' does not appear in Show Variables like '%big%'.

Anyway, I added
Set Big_Table=1;
but this does not change anything.

I didn't find a solution using MySql and moved back to MsAccess2000 to
execute the query (tables attached using Odbc)
This is a real MsAccess query, not a pass-thru query.

2.2) Problem 2:
Using the same table Calc_T1, I want to move one value from a single record
table :

Update calc_T1,De_Param
set Scenario_cost=Param_Scenario_costs;

The table De_Param contains only one record and Calc_T1 contains 489 875
rows (same table as above)
and I receive the message ERROR 1114 : Table Calc_T1 full.

The problem was solved by using:

Select @a:=Param_Scenario_costs from De_param;
Update Calc_T1 set Scenario_costs=@a;




+---------------------------------------------------------+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+---------------------------------------------------------+


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MySql Version 4.xx problems with update/Table full

Serge_Louvet@mckinsey.com writes:
>
> 1) installation
> OS = Windows 2000
> MySql = 4.0.12-nt (binary download from you site)
>
> All tables are myISAM
>
> 2) description
>
> 2.1) Problem1 :
>
> I have one table (calc_t1) that contains 489 875 rows. The total data size
> reported by show status is 155 400 900
> I have another table (calc_t9) that contains 489 875 rows. The total data
> size reported by show status 46 252 364
>
> Both table uses the same primary key, made of five fields declared as
> varchar(50). Actually, none of them exceeds 12 characters (note: later,I
> tried to reduce the declared field size, but the problem described below
> still occurs)
>
> The keys are the same in both files (checked by doing a Select count(*)
> from Calc_T1 inner join Calc_T9 using (....), which returned 489875)
>
>
> Now I want to move some data from Calc_T9 to Calc_T1.

HI!

The above is not a fully repeatable test case, plus it does not look
like a bug either.

You are either hitting some filesystem limit or you need to increase
MAX_ROWS on T1 table.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MySql Version 4.xx problems with update/Table full

Serge_Louvet@mckinsey.com writes:
>
> Ok,
>
> index length is 23 436 286 and max index length is 4 398 046 510 079 (see
> attached file 'myisamchkout.txt')
> By the way, is this really 4,3 terabytes ?
>
> I found a repeatable script to generate the error (other attached files)
> related to DE_PARAM:
>
> 1) genpblm.sql
> This file contains a script that, on my computer, generates the error
> message.
>
> 2) genpblmlog.txt
> This file contains the contents of the message window from MySqlCC.
>
> As you can see, the last update statement works, while the update statement
> just before generates an error 1114. In between, you have the single Select
> statement
>
> 3) rep1status.txt is the results of a show table status.
>
> 4) the next files DE_PARAM.* make the single record table DE_PARAM.
>
> Hope you will get the same error on your side, so that we have a repeatable
> pblm.
>
> S.Louvet
>
> (See attached file: de_param.MYI)(See attached file: de_param.MYD)(See
> attached file: de_param.frm)
>
>
> (See attached file: myisamchkout.txt)(See attached file: rep1Status.txt)
> (See attached file: genpblm.sql)(See attached file: genpblm.log.txt)
>
>

Thank you for your bug report !!!

This was truly a bug in multi-table updates.

A fix will come in 4.0.13, but this is a patch:
===== sql/sql_update.cc 1.79 vs edited =====
*** /tmp/sql_update.cc-1.79-13879 Wed Mar 19 00:45:43 2003
--- edited/sql/sql_update.cc Mon Mar 31 21:16:40 2003
***************
*** 741,747 ****
(error != HA_ERR_FOUND_DUPP_KEY &&
error != HA_ERR_FOUND_DUPP_UNIQUE))
{
! if (create_myisam_from_heap(table, tmp_table_param + offset, error, 1))
{
do_update=0;
DBUG_RETURN(1); // Not a table_is_full error
--- 741,747 ----
(error != HA_ERR_FOUND_DUPP_KEY &&
error != HA_ERR_FOUND_DUPP_UNIQUE))
{
! if (create_myisam_from_heap(tmp_table, tmp_table_param + offset, error, 1))
{
do_update=0;
DBUG_RETURN(1); // Not a table_is_full error


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org