Re: Table full
hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem
Dear mysql supports, I want to change the font color for database mysql , for example I want to insert data with red color when I select this data the font color is the default black.It's possible to change the font color. thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help joining tables in a query
Greetings, I'm facing a difficult query at the moment. I have tried many different queries but still not get the desired result. My case: Tables: shipped ( id, shipdate, sn); undelivered (id, undate, sn); return (id, redate, sn); I need the following output: DATE SHIPMENTS UNDELIVERED RETURNS Fri 1 may 2005 87 11 4 Sat 2 may 2005 82 17 5 ........ ... So far, Im have succesfulyl manage to get the result using just one table: DATE SHIPMENTS Fri 1 may 2005 87 Sat 2 may 2005 82 ..... With the query: SELECT DATE_FORMAT(shipdate,'%a %e %b %Y'), COUNT(shipdate) FROM shipped WHERE (YEAR(shipdate) = 2004) AND (MONTH(shipdate) = 05) GROUP BY shipdate ORDER BY shipdate DESC However, joining the 3 tables and getting the right results is being a nightmare. I have already tried different left and right joins, with no success. Please any help, hints, or light on the query will be greately appreciated. James.
AES Encryption
Hi, I think of using AES Encryption for some time now, because it seems to be the most secure encryption method in MySQL at this moment and table encryption of some sort is not possible. In the documentation I read BLOB fields are recommended for storage of data with AES_Encrypt, otherwise you might lose data. Does this method of storing data with encryption in BLOB fields also have disadvantages (besides speed and queries being longer), like how much bigger will the data get? Also, while testing I experienced that inputting something like more then 255 characters with AES_Encrypt, the AES_Decrypt function doesn't give back more then 255 characters. Is this correct, or am I doing something wrong here? I intent to use the EAS encryption functions for a webbased student administration system which stores data like student names, addresses, results, etc, and because some of the data is quite important, like addresses and results (may not be changed or viewed by unauthorized persons) and the database environment is not of high security (shared host, because of costs) I intend to use these encryption functions. Do you also have some recommendations on if I'm doing the most reasonable thing here to use the AES encryption? And a question I always had is where to store the keys? Currently I'm thinking somewhere in the webapplication source code, but I'm not so sure about this. Any help would be greatly appreciated, Thanks, Martin
Re: problem
Hi, don't you really want to write also in Times new roman your mysql data ? you forgot the tools, OS ... (client) or it's a big jock. Mathias Selon nicolas ghosn [EMAIL PROTECTED]: Dear mysql supports, I want to change the font color for database mysql , for example I want to insert data with red color when I select this data the font color is the default black.It's possible to change the font color. thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb design question
Greetings list, In an analysis I was sent, there is 1 table with a simple set of rules, e.g. a_table (id, day, max_hours, min_hours, min_days, start_time, stop_time, max_attendants, ...) No biggie there. The problem is the user has to be able to define exceptions to those rules. These exceptions will at one time be a quantitative value, i.e. a minimum or maximum number of attendants for a specific day, at other times it will concern a start_time that has to be met at least X times a month, etc. I've more or less broken the table structure for this down to a couple of tables, like this: table_case_quantity table_case_actions table_case_day table_case_time ... I 'concatenated' (foreign keys) related tables together into tables that specify a certain condition. At this time, there are 3 kinds of exception tables. In other words, I've managed to normalize this, no problem there. And now for the question :) When looking up exceptions, I would like it if there is just 1 table 'exceptions', that looks like this: exceptions (id, exception_type, id_of_the_exception_in_the_table_of_that_exception_type), i.e. while keeping a foreign constraint. In bashed up SQL syntax, I guess what I'd like to do is FOREIGN KEY (exception_type) REFERENCES table_of_the_exception_type (--- notice no id, just the table) FOREIGN KEY (id_of_the_...) REFERENCES table_of_the_exception_type(id) I'm thinking this has come up earlier, the question is how to do it. The other option is querying the db for every exception-type-table, which I'd prefer not doing. TIA, Stijn Verholen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys
Is it good practice to have a primary key for a table even though the table has a foreign key that is the primary key of another table? If so, why is this the case? I would appreciate if you point me to some resources that point out the advantages and disadvantages of both approaches. Thank you. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys (Follow Up)
As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnnoDb - change innodb_buffer_pool_size ?
Hello Michael! Michael Gale wrote: Hello, I have finished reading something similer on the web, why is there a 2GB limit ? I assume you are using Linux on an x86 compatible machine (Pentium, Athlon, ...), so you have 32 bit addresses. 32 bit give you 4 G different values; as each address identifies a single byte, you can address 4 GB on the machine. The Linux kernel differs between user and system addresses, this is essential to pass data around, for example in system calls. Traditionally, this was done on the highest address bit (2 GB system address space, 2 GB user). With the growing size of applications, this has become configurable (with kernel generation) to a 1 / 3 ratio. If I can compile the kernel to say eys there is 4GB of memory why can mysql not use 3GB of it ? But still these 3 GB (space of virtual addresses) must hold everything the application (in your case: MySQL) can address, so it contains code, stacks of all threads in the process, static data, all configurable buffers etc. As a first step, you can use the size command on mysqld to get some of these values, then calculate the total thread stack space and add it. Subtract that from your user address space size (2 or 3 GB), and distribute the result among all buffers, caches etc. In any case, I see no way to use 3 GB just for the InnoDB buffer pool on a 32 bit system. Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer 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: Primary and Foreign Keys (Follow Up)
If your FK really is unique, you don't need two tables. Example First table Second Table ID FK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary and Foreign Keys (Follow Up)
Hello. I appreciate your input. To clarify, I will provide you with an example: I have a table called business and another one called food_business. The field business_id is a primary key of table business and a foreign key of table food_business. In this case, the foreign key is unique and although this is a one-to-one relationship, it would be inappropriate to merge these 2 tables since food_business is a specialization of business and will therefore contain fields that only apply to food businesses and not to any generic business. - Asad On Wed, 15 Jun 2005, Stefan Kuhn wrote: If your FK really is unique, you don't need two tables. Example First table Second Table IDFK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tough queries
Thanks for the reply, but I think you misinterpreted what I'm looking
for here.
For the first query I want to be able to get a row record of (in one
query possibly)
team_name for first team (team_id1)
team_name for second team (team_id2)
and each of these team points
for a list of N games retreived (and NULL values for the scores if there
are no points).
Same kind of query for the player stats where I'd retreive these infos
on each row record :
player_name
total goals
total assists
total points
sorted by total points DESC
Thanks
David
[EMAIL PROTECTED] wrote:
Hi,
for the first query,
select concat(team_id,' (',sum(points),')') from games,points
where games.game_id=points.game_id
and games.team_id1=points.team_id
group by team_id
can solve the problem.
For the second, join players and points.
Mathias
Selon David Legault [EMAIL PROTECTED]:
Hello,
I'm a regular user of MySQL but lately on a personal project I've run
into some very complexe query management and am a little bit confused on
how to get it working. This is a hockey league stats website application.
I have 4 tables with the following columns:
teams - team_id | team_name
players - player_id | team_id | player_name
games - game_id | team_id1 | team_id2
points- point_id | game_id | team_id | goal_player_id | pass_player_id1
| pass_player_id2
The kind of queries I'd like to perform would be (if possible in one
query or a subset of queries) something to generate the following as a
list of N games with the scores (if the game was played and team name)
which would use the games, teams and points tables.
Desired Output
Team A (2) - Team B (7)
Team D (3) - Team C (1)
...
Thus, it needs to retreive the team names, and total score for each game
that I want to list (using other criteria not essential in the example)
Another Query would be to have the points of each player listed for a
team (in ORDER DESC or total points):
Team C:
Player | Goals | Assists | Points
AA 8 1 9
BB 5 3 8
CC 3 2 5
DD 1 2 3
If you could explain a little bit how each query answer you provide
works, I'd like it. If you have any tutorials or good articles about
such complexe queries, I'd be thankfull to be able to read them to help me.
Thanks
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared Statements in Stored Procedures.
I've been playing around with stored procedures and I seem to be able to
consistantly crash msqld but I'm unsure wether it's because I'm doing
something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql.
I know I'm 'pushing my luck' with the code below but I was trying it out of
interest more than anything.
For reference the new table is being created by the SP but mysqld then
crashes. I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora
Core 3.
DELIMITER $$
DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username
char(100), IN sz_deletedby char(100))
BEGIN DECLARE tablename char(120);
SET tablename=CONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
SET @SQLcmd=CONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest WHERE
uid = ',i_uid,' AND username = ',sz_username,'');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted
TIMESTAMP FIRST');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=CONCAT('DROP TABLE ',tablename);
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
END$$
DELIMITER ;
CREATE TABLE `usertest` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(100) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO usertest VALUES('502','dvs');
CALL DeleteUser('502','dvs','rhall-superuser');
mysqld.log:-
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x46cf74, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815f807
0x4d47c8
(nil)
0x84d4a28
0x84d4a4d
0x84d46cb
0x811071d
0x8131552
0x812631a
0x8167cc1
0x81678f1
0x81731ae
0x824a5aa
0x824a421
0x824a4fa
0x82482b4
0x8248a96
0x8174dfa
0x81771a9
0x816f8b4
0x816f1e4
0x816e7a4
0x4ce341
0x1ddfee
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8c99520 = SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD
COLUMN datedeleted TIMESTAMP FIRST')
thd-thread_id=1
resolve_stack_dump -s /usr/lib/mysql/mysqld-max.sym -n /tmp/mysqld.stack
0x815f807 handle_segfault + 423
0x4d47c8 (?)
(nil)
0x84d4a28 __default_terminate + 24
0x84d4a4d __terminate + 29
0x84d46cb __pure_virtual + 43
0x811071d val_str__12Item_splocalP6String + 45
0x8131552 val_str__16Item_func_concatP6String + 178
0x812631a check__22Item_func_set_user_var + 154
0x8167cc1 check__12set_var_userP3THD + 49
0x81678f1 sql_set_variables__FP3THDPt4List1Z12set_var_base + 49
0x81731ae mysql_execute_command__FP3THD + 9390
0x824a5aa exec_core__13sp_instr_stmtP3THDPUi + 26
0x824a421 reset_lex_and_exec_core__13sp_lex_keeperP3THDPUibP8sp_instr + 209
0x824a4fa execute__13sp_instr_stmtP3THDPUi + 122
0x82482b4 execute__7sp_headP3THD + 644
0x8248a96 execute_procedure__7sp_headP3THDPt4List1Z4Item + 806
0x8174dfa mysql_execute_command__FP3THD + 16634
0x81771a9 mysql_parse__FP3THDPcUi + 249
0x816f8b4 dispatch_command__F19enum_server_commandP3THDPcUi + 1732
0x816f1e4 do_command__FP3THD + 196
0x816e7a4 handle_one_connection + 740
0x4ce341 (?)
0x1ddfee (?)
--
Best regards,
Rob Hall - Red Hat Certified Engineer
Technical Team Leader
Newsquest Digital Media
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared Statements in Stored Procedures.
Hi Rob,
I've been playing around with stored procedures and I seem to be able to
consistantly crash msqld but I'm unsure wether it's because I'm doing
something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql.
I know I'm 'pushing my luck' with the code below but I was trying it out
of
interest more than anything.
For reference the new table is being created by the SP but mysqld then
crashes. I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora
Core 3.
Well, obviously, it should never crash ... but ...
What's the point in preparing these statements?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
http://www.upscene.com
DELIMITER $$
DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username
char(100), IN sz_deletedby char(100))
BEGIN DECLARE tablename char(120);
SET tablename=CONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
SET @SQLcmd=CONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest
WHERE
uid = ',i_uid,' AND username = ',sz_username,'');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted
TIMESTAMP FIRST');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=CONCAT('DROP TABLE ',tablename);
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
END$$
DELIMITER ;
CREATE TABLE `usertest` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(100) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO usertest VALUES('502','dvs');
CALL DeleteUser('502','dvs','rhall-superuser');
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write subqueries?
Hello! another simpler solution : ... Thanks, Mathias! You helped me greatly! Now my program works as desired. -- Good luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Visual Basic .NET Oledb Provider
Hi all, I would need an oledb provider for MySQL. Do you know where I can found a good one. Thanks in advance. Leonardo J. Belén. A-AR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared Statements in Stored Procedures.
On Wednesday 15 June 2005 15:10, Martijn Tonies wrote: Hi Rob, I've been playing around with stored procedures and I seem to be able to consistantly crash msqld but I'm unsure wether it's because I'm doing something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql. I know I'm 'pushing my luck' with the code below but I was trying it out of interest more than anything. For reference the new table is being created by the SP but mysqld then crashes. I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora Core 3. Well, obviously, it should never crash ... but ... What's the point in preparing these statements? It was simply attempting to ensure that the created table would be unique when the SP was called. If you've multiple threads/users application side I didn't want to use a single table name and have the SP fall over itself. Obviously there are other (more elegant) solutions but as stated I was playing with SPs to see what I could get them to do. -- Best regards, Rob Hall - Red Hat Certified Engineer Technical Team Leader Newsquest Digital Media -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM index file recovery/writing by hand
Hi all. I am desperately seeking advice on quite unusual matter with MyISAM tables. We have application with very large volume of data being posted into database at once, and in order to speed process up, an attempt to write MYD files by hand has been attempted. However, MySQL wont load such table properly, unless MYI (index) file is written accordingly. Present recovery tools (myisamchk) are normalizing database by index, so I cant just put there empty MYI file and ask for recovery (then I will get empty table, basically). Is there any recovery tool that restores MYI file by MYD? Or is there any documentation available somewhere (except for sources) where one can find how MYI files are organized? Thanks in advance Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnnoDb - change innodb_buffer_pool_size ?
On Tuesday 14 June 2005 11:57 pm, Michael Gale wrote: Even if I change the value to 2G it still will not load ? Because the value was 500M before do I not have to remove the ib* files and have them recreated ? 2g is still to large.. its 2gig for all buffer pools, not just that one.. I keep mine to 1.5 and all the others are really low or turned off. Jeff pgpTAtr1JpbRo.pgp Description: PGP signature
RE: Considering migration from MyISAM to InnoDB
Thank you. I know how to migrate tables using ALTER TABLE - my question has more to do with *whether* I should migrate. For smaller databases (10mb in size) is it really beneficial? Details in my original post quoted below. Thanks again, Michael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 7:00 PM To: Stembridge, Michael Cc: mysql@lists.mysql.com Subject: Re: Considering migration from MyISAM to InnoDB see the my.cnf examples in the install dir, and look at innodb* variables. you can migrate each table just using : alter table toto engine=innodb; Mathias Selon Stembridge, Michael [EMAIL PROTECTED]: I currently use MyISAM on an internal web application server; our data takes up 10mb at this time, though this is likely to grow substantially in the coming year. The database sees moderate heavy read and moderate write usage from 50 users. We're upgrading our sever from Red Hat 7.3 to SuSE Linux Enterprise 9.0 soon and have considered migrating to InnoDB as part of our upgrade.I like the performance increases I've seen documented here http://www.innodb.com/bench.php, though I'm not sure our environment calls for InnoDB. Thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Backup/Dump
It is recommended that you upgrade to 4.0.x first from 3.23.xx, because of any changes to the grant tables in the mysql database, make sure you read the upgrade notes before upggrading, here's a good link to upgrade, http://dev.mysql.com/doc/mysql/en/upgrade.html I find it very simple to zip all the databases to be dumped from the old server, and unzip them into the new box instead of a mysqlimport etc and hopefully it should work, # zip -r backup.zip database1 database2 ... . the go to data dir and #unzip backup.zip Kishore On 6/14/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: different box, different versions, use export/import (backup table or LOAD DATA) Mathias Selon Kory Wheatley [EMAIL PROTECTED]: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES Encryption
At 04:32 AM 6/15/2005, you wrote: Hi, I think of using AES Encryption for some time now, because it seems to be the most secure encryption method in MySQL at this moment and table encryption of some sort is not possible. I really wish MySQL would support table wide encryption because more and more news reports are showing the lax security is causing big problems for companies. Look at the CitiGroup fiasco: http://www.tallahassee.com/mld/tallahassee/business/11886144.htm http://www.oregonlive.com/business/oregonian/index.ssf?/base/business/1118225190236830.xmlcoll=7 This is going to cost them millions of dollars in PR and possibly a few lawsuits. If the database had table wide encryption, the loss of the tapes wouldn't have made the news. Also stories of hard drives walking out of secure rooms or re-sold with data still on it, does not instill any confidence in current security protocols. IMHO, table wide encryption would solve a lot of these problems. Other databases have implemented table wide encryption, why not MySQL? If the government passes a law to force this data to be encrypted, MySQL would be at a definite disadvantage to those databases that have table wide encryption. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES Encryption
If the government passes a law to force this data to be encrypted, The last thing I want is the Government mandating encryption for anyone. If you want a law to help here, I'd rather there be a law forcing companies to disclose which encryption scheme they're using, and which customer info is being encrypted. And even with that said, laws are only good for those who abide by them. :-) -Cassj -Original Message- From: mos [EMAIL PROTECTED] Sent: Jun 15, 2005 11:00 AM To: mySQL list mysql@lists.mysql.com Subject: Re: AES Encryption At 04:32 AM 6/15/2005, you wrote: Hi, I think of using AES Encryption for some time now, because it seems to be the most secure encryption method in MySQL at this moment and table encryption of some sort is not possible. I really wish MySQL would support table wide encryption because more and more news reports are showing the lax security is causing big problems for companies. Look at the CitiGroup fiasco: http://www.tallahassee.com/mld/tallahassee/business/11886144.htm http://www.oregonlive.com/business/oregonian/index.ssf?/base/business/1118225190236830.xmlcoll=7 This is going to cost them millions of dollars in PR and possibly a few lawsuits. If the database had table wide encryption, the loss of the tapes wouldn't have made the news. Also stories of hard drives walking out of secure rooms or re-sold with data still on it, does not instill any confidence in current security protocols. IMHO, table wide encryption would solve a lot of these problems. Other databases have implemented table wide encryption, why not MySQL? If the government passes a law to force this data to be encrypted, MySQL would be at a definite disadvantage to those databases that have table wide encryption. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE and specifying DEFAULT
Les Schaffer wrote: Gordon Bruce wrote: I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows upgrading to 5.0.6 solved the problem. do i need to let MySQL developers know about this or do they monitor the list or once a release is gone, i can assume THIS problem was fixed? thanks to all for the fast help. i am new to the mysql list and i can see it works. les schaffer Les, i don't know if you have managed to solve your problem. it took a while but i asked my brother to try it on his mysql which is on a windows platform. the result was that he also got the nulls after ID_streettype_spec_hosp. he is working with version 5.0 mysql server. this has got to be a bug, or there must be something else going on. Danny Stolle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES Encryption
mos wrote: At 04:32 AM 6/15/2005, you wrote: Hi, I think of using AES Encryption for some time now, because it seems to be the most secure encryption method in MySQL at this moment and table encryption of some sort is not possible. I really wish MySQL would support table wide encryption because more and more news reports are showing the lax security is causing big problems for companies. Look at the CitiGroup fiasco: http://www.tallahassee.com/mld/tallahassee/business/11886144.htm http://www.oregonlive.com/business/oregonian/index.ssf?/base/business/1118225190236830.xmlcoll=7 This is going to cost them millions of dollars in PR and possibly a few lawsuits. If the database had table wide encryption, the loss of the tapes wouldn't have made the news. Also stories of hard drives walking out of secure rooms or re-sold with data still on it, does not instill any confidence in current security protocols. IMHO, table wide encryption would solve a lot of these problems. Other databases have implemented table wide encryption, why not MySQL? If the government passes a law to force this data to be encrypted, MySQL would be at a definite disadvantage to those databases that have table wide encryption. Mike You can use an encrypted file system, and encrypt your dumps before archiving. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Primary and Foreign Keys (Follow Up)
In this case, business_id should be both the primary key and a foreign key. If you don't set it as a primary key, then the database would allow duplicate entries into the food_business table. By implementing this simple rule, you can prevent a whole class of application bugs from causing problems. On a different note, some databases will, on update, log the whole row if there is no primary key specified. This means that the transaction log can get very full very fast because all of the fields are logged even though only one field is changed. In short, since the field does uniquely identify each row in the table, it should be made the primary key. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 -Original Message- From: Asad Habib [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 15, 2005 5:19 AM To: Stefan Kuhn Cc: mysql@lists.mysql.com Subject: Re: Primary and Foreign Keys (Follow Up) Hello. I appreciate your input. To clarify, I will provide you with an example: I have a table called business and another one called food_business. The field business_id is a primary key of table business and a foreign key of table food_business. In this case, the foreign key is unique and although this is a one-to-one relationship, it would be inappropriate to merge these 2 tables since food_business is a specialization of business and will therefore contain fields that only apply to food businesses and not to any generic business. - Asad On Wed, 15 Jun 2005, Stefan Kuhn wrote: If your FK really is unique, you don't need two tables. Example First table Second Table ID FK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM index file recovery/writing by hand
Hello. documentation available somewhere (except for sources) where one can find how MYI files are organized? I don't think other sources except internals.texi (located in the Doc directory) are publicly available. Sergei Rodionov [EMAIL PROTECTED] wrote: Hi all. I am desperately seeking advice on quite unusual matter with MyISAM tables. We have application with very large volume of data being posted into database at once, and in order to speed process up, an attempt to write MYD files by hand has been attempted. However, MySQL wont load such table properly, unless MYI (index) file is written accordingly. Present recovery tools (myisamchk) are normalizing database by index, so I cant just put there empty MYI file and ask for recovery (then I will get empty table, basically). Is there any recovery tool that restores MYI file by MYD? Or is there any documentation available somewhere (except for sources) where one can find how MYI files are organized? Thanks in advance Sergei -- 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: Prepared Statements in Stored Procedures.
Hello.
I suggest you to check your procedure on the latest bk source, and
if the server keeps crashing report a bug.
Rob Hall [EMAIL PROTECTED] wrote:
I've been playing around with stored procedures and I seem to be able to=20
consistantly crash msqld but I'm unsure wether it's because I'm doing=20
something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql.
I know I'm 'pushing my luck' with the code below but I was trying it out of=
=20
interest more than anything.
=46or reference the new table is being created by the SP but mysqld then=20
crashes. I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora=20
Core 3.
DELIMITER $$
DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username=20
char(100), IN sz_deletedby char(100))
BEGIN DECLARE tablename char(120);
SET tablename=3DCONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
SET @SQLcmd=3DCONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest W=
HERE=20
uid =3D ',i_uid,' AND username =3D ',sz_username,'');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=3DCONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted=20
TIMESTAMP FIRST');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=3DCONCAT('DROP TABLE ',tablename);
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
END$$
DELIMITER ;
CREATE TABLE `usertest` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(100) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;
INSERT INTO usertest VALUES('502','dvs');
CALL DeleteUser('502','dvs','rhall-superuser');
mysqld.log:-
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=3D0x46cf74, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815f807
0x4d47c8
(nil)
0x84d4a28
0x84d4a4d
0x84d46cb
0x811071d
0x8131552
0x812631a
0x8167cc1
0x81678f1
0x81731ae
0x824a5aa
0x824a421
0x824a4fa
0x82482b4
0x8248a96
0x8174dfa
0x81771a9
0x816f8b4
0x816f1e4
0x816e7a4
0x4ce341
0x1ddfee
New value of fp=3D(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and=20
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8c99520 =3D SET @SQLcmd=3DCONCAT('ALTER TABLE ', tablename,=
' ADD=20
COLUMN datedeleted TIMESTAMP FIRST')
thd-thread_id=3D1
resolve_stack_dump -s /usr/lib/mysql/mysqld-max.sym -n /tmp/mysqld.stack
0x815f807 handle_segfault + 423
0x4d47c8 (?)
(nil)
0x84d4a28 __default_terminate + 24
0x84d4a4d __terminate + 29
0x84d46cb __pure_virtual + 43
0x811071d val_str__12Item_splocalP6String + 45
0x8131552 val_str__16Item_func_concatP6String + 178
0x812631a check__22Item_func_set_user_var + 154
0x8167cc1 check__12set_var_userP3THD + 49
0x81678f1 sql_set_variables__FP3THDPt4List1Z12set_var_base + 49
0x81731ae mysql_execute_command__FP3THD + 9390
0x824a5aa exec_core__13sp_instr_stmtP3THDPUi + 26
0x824a421 reset_lex_and_exec_core__13sp_lex_keeperP3THDPUibP8sp_instr + 209
0x824a4fa execute__13sp_instr_stmtP3THDPUi + 122
0x82482b4 execute__7sp_headP3THD + 644
0x8248a96 execute_procedure__7sp_headP3THDPt4List1Z4Item + 806
0x8174dfa mysql_execute_command__FP3THD + 16634
0x81771a9 mysql_parse__FP3THDPcUi + 249
0x816f8b4 dispatch_command__F19enum_server_commandP3THDPcUi + 1732
0x816f1e4 do_command__FP3THD + 196
0x816e7a4 handle_one_connection + 740
0x4ce341 (?)
0x1ddfee (?)
=2D-=20
Best regards,
=A0Rob Hall - Red Hat Certified Engineer
=A0Technical Team Leader
=A0Newsquest Digital Media
--
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: Prepared Statements in Stored Procedures.
At 14:57 +0100 6/15/05, Rob Hall wrote:
I've been playing around with stored procedures and I seem to be able to
consistantly crash msqld but I'm unsure wether it's because I'm doing
something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql.
I know I'm 'pushing my luck' with the code below but I was trying it out of
interest more than anything.
For reference the new table is being created by the SP but mysqld then
crashes. I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora
Core 3.
Several problems have been found with SQL prepared statements within
stored routines, so use of prepared statements within stored routines
has been disabled for now. See the notes for bugs #10975, #7115, and
#10605 here:
http://dev.mysql.com/doc/mysql/en/news-5-0-8.html
DELIMITER $$
DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username
char(100), IN sz_deletedby char(100))
BEGIN DECLARE tablename char(120);
SET tablename=CONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
SET @SQLcmd=CONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest WHERE
uid = ',i_uid,' AND username = ',sz_username,'');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted
TIMESTAMP FIRST');
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
SET @SQLcmd=CONCAT('DROP TABLE ',tablename);
prepare stmt from @SQLcmd;
execute stmt;
deallocate prepare stmt;
END$$
DELIMITER ;
CREATE TABLE `usertest` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(100) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO usertest VALUES('502','dvs');
CALL DeleteUser('502','dvs','rhall-superuser');
mysqld.log:-
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x46cf74, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815f807
0x4d47c8
(nil)
0x84d4a28
0x84d4a4d
0x84d46cb
0x811071d
0x8131552
0x812631a
0x8167cc1
0x81678f1
0x81731ae
0x824a5aa
0x824a421
0x824a4fa
0x82482b4
0x8248a96
0x8174dfa
0x81771a9
0x816f8b4
0x816f1e4
0x816e7a4
0x4ce341
0x1ddfee
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8c99520 = SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD
COLUMN datedeleted TIMESTAMP FIRST')
thd-thread_id=1
resolve_stack_dump -s /usr/lib/mysql/mysqld-max.sym -n /tmp/mysqld.stack
0x815f807 handle_segfault + 423
0x4d47c8 (?)
(nil)
0x84d4a28 __default_terminate + 24
0x84d4a4d __terminate + 29
0x84d46cb __pure_virtual + 43
0x811071d val_str__12Item_splocalP6String + 45
0x8131552 val_str__16Item_func_concatP6String + 178
0x812631a check__22Item_func_set_user_var + 154
0x8167cc1 check__12set_var_userP3THD + 49
0x81678f1 sql_set_variables__FP3THDPt4List1Z12set_var_base + 49
0x81731ae mysql_execute_command__FP3THD + 9390
0x824a5aa exec_core__13sp_instr_stmtP3THDPUi + 26
0x824a421 reset_lex_and_exec_core__13sp_lex_keeperP3THDPUibP8sp_instr + 209
0x824a4fa execute__13sp_instr_stmtP3THDPUi + 122
0x82482b4 execute__7sp_headP3THD + 644
0x8248a96 execute_procedure__7sp_headP3THDPt4List1Z4Item + 806
0x8174dfa mysql_execute_command__FP3THD + 16634
0x81771a9 mysql_parse__FP3THDPcUi + 249
0x816f8b4 dispatch_command__F19enum_server_commandP3THDPcUi + 1732
0x816f1e4 do_command__FP3THD + 196
0x816e7a4 handle_one_connection + 740
0x4ce341 (?)
0x1ddfee (?)
--
Best regards,
Rob Hall - Red Hat Certified Engineer
Technical Team Leader
Newsquest Digital Media
--
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: Mysql Backup/Dump
I just moved my databases to 4.1 from 3.23. It went pretty smoothly. First I used mysqldump to back up the data. Then installed 4.1. First thing did was import only the mysql table. Then ran /mysql_fix_privilege_tables command. After that I was able to login to mysql with my old root password. On 6/15/05, Kishore Jalleda [EMAIL PROTECTED] wrote: It is recommended that you upgrade to 4.0.x first from 3.23.xx, because of any changes to the grant tables in the mysql database, make sure you read the upgrade notes before upggrading, here's a good link to upgrade, http://dev.mysql.com/doc/mysql/en/upgrade.html I find it very simple to zip all the databases to be dumped from the old server, and unzip them into the new box instead of a mysqlimport etc and hopefully it should work, # zip -r backup.zip database1 database2 ... . the go to data dir and #unzip backup.zip Kishore On 6/14/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: different box, different versions, use export/import (backup table or LOAD DATA) Mathias Selon Kory Wheatley [EMAIL PROTECTED]: I want to backup our entire Mysql database structure for mysql 3.23.58 and dump it in the newly installed mysql 4.1.10 What is the best command to do a backup and dump everything into the new MYSQL version on a different box? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sharif Islamhttp://www.sharifislam.com Research Programmer University of Illinois, Urbana-Champaign Library Systems Office217-244-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Visual Basic .NET Oledb Provider
Why not use the .NET provider... http://dev.mysql.com/downloads/connector/net/1.0.html - Original Message - From: Leonardo Javier Belén [EMAIL PROTECTED] To: Dev - MySQL - MList mysql@lists.mysql.com Sent: Wednesday, June 15, 2005 7:24 AM Subject: Visual Basic .NET Oledb Provider Hi all, I would need an oledb provider for MySQL. Do you know where I can found a good one. Thanks in advance. Leonardo J. Belén. A-AR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems using Binary Install on Mac OS 10.4.1
Hello All, I am trying to install MySQL 4.1.12 on my Powermac G4. My OS is 10.4.1. I downloaded the binary distribution from the MySQL website. I uncompressed it and opened the .dmg installer. I ran the installer. Where it prompted for the Administrator's password, I gave it. The installation completed successfully. I then followed the instructions and installed the StartUp item without problem. I then tried to access the database and I get the following message: - mysql -u root ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) - Should the installer have installed mysql.sock in /tmp on Macs running OS 10.4.1? Is there a step I missed or is missing in the documentation? Can MySQL run on a Mac using 10.4.1? Thank you for your time. Elton = NOVA505 W. Olive Ave. Suite 550 Elton Hughes (IT) Sunnyvale CA 94086 Phone: 408-730-7235 Fax: 408-730-7643 - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Considering migration from MyISAM to InnoDB
It depends on what you need out of the database. Unfortunately fulltext indexes are still only supported by MyISAM which locks a good deal of the tables we have out of innodb. Also, we attempted to migrate some tables to innodb and system performance got worse! No one ever responded to my post to the list regarding the issue found on March 3, 2005 12:30:36 entitled innodb update issues. It seems in a lot of cases you should only start new projects on innodb and be weary of moving an existing project over. Quoting Stembridge, Michael [EMAIL PROTECTED]: Thank you. I know how to migrate tables using ALTER TABLE - my question has more to do with *whether* I should migrate. For smaller databases (10mb in size) is it really beneficial? Details in my original post quoted below. Thanks again, Michael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 7:00 PM To: Stembridge, Michael Cc: mysql@lists.mysql.com Subject: Re: Considering migration from MyISAM to InnoDB see the my.cnf examples in the install dir, and look at innodb* variables. you can migrate each table just using : alter table toto engine=innodb; Mathias Selon Stembridge, Michael [EMAIL PROTECTED]: I currently use MyISAM on an internal web application server; our data takes up 10mb at this time, though this is likely to grow substantially in the coming year. The database sees moderate heavy read and moderate write usage from 50 users. We're upgrading our sever from Red Hat 7.3 to SuSE Linux Enterprise 9.0 soon and have considered migrating to InnoDB as part of our upgrade.I like the performance increases I've seen documented here http://www.innodb.com/bench.php, though I'm not sure our environment calls for InnoDB. Thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Considering migration from MyISAM to InnoDB
If it works fine currently, the rule usually is: don't mess with it. :) I would stick with MyISAM if I were you, it should be plenty good enough for such a small table and usage as you described. If you are experiencing performance issues, I would recommend looking at indexes if you haven't already done so. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 14 Jun 2005, Stembridge, Michael wrote: I currently use MyISAM on an internal web application server; our data takes up 10mb at this time, though this is likely to grow substantially in the coming year. The database sees moderate heavy read and moderate write usage from 50 users. We're upgrading our sever from Red Hat 7.3 to SuSE Linux Enterprise 9.0 soon and have considered migrating to InnoDB as part of our upgrade.I like the performance increases I've seen documented here http://www.innodb.com/bench.php, though I'm not sure our environment calls for InnoDB. Thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is best open-source platform alternative to overcome innodb 2Gb memory limit on Linux? FreeBSD?
Hi, I am currently running a large database (around 20Gb) on a 32bit x86 Linux platform. Many of my larger data-crunching queries are disk-bound due to the limitation described in the innodb configuration documentation: *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| may allow the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB: Being a responsible citizen, I have my innodb_buffer_pool_size set below 2Gb. But the time has come to scale the application, so I need an alternative solution that will allow me to set innodb_buffer_pool_size as high as my heart desires (or at least well beyond 2Gb). Do any of you have battle-tested recommendations? How about FreeBSD? From what I can gather, it is a good idea to build MySQL on FreeBSD linked with the Linux Thread Library. Would doing so re- invoke the 2Gb limit? I look foward to your collective responses. Thanks! Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best open-source platform alternative to overcome innodb 2Gb memory limit on Linux? FreeBSD?
Why not go AMD-64? Dual Opteron, with 8/16/32 gig of RAM? Get a 3ware SATA drive, and run Gentoo for AMD-64. You can increase your innodb buffer pool to use almost all that space. You can make your buffer pool as large as the physical RAM in your machine can support. No 2.5 gig per process, 4-gig limit on addressable memory (without the address-extensions). Your hardware is holding you back more than your operating system. David Brady Brown wrote: Hi, I am currently running a large database (around 20Gb) on a 32bit x86 Linux platform. Many of my larger data-crunching queries are disk-bound due to the limitation described in the innodb configuration documentation: *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| may allow the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB: Being a responsible citizen, I have my innodb_buffer_pool_size set below 2Gb. But the time has come to scale the application, so I need an alternative solution that will allow me to set innodb_buffer_pool_size as high as my heart desires (or at least well beyond 2Gb). Do any of you have battle-tested recommendations? How about FreeBSD? From what I can gather, it is a good idea to build MySQL on FreeBSD linked with the Linux Thread Library. Would doing so re- invoke the 2Gb limit? I look foward to your collective responses. Thanks! Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management: conclusion
Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands Thanx everybody for sharing some cool information on User Management. I have read some cool ideas and arguments on the options which i suggested. I guess there is no best or a most prefered method on user management. The method that is being used is the most suitable in the environment the database is used and the database administrator's prefered working method offcourse. But i must conclude that by reading the messages carefully the most prefered method was option 3: creating multiple user IDs for each task that the user would perform on the database. Creating these roles and assigning the user(s) to this specific role (=RBAC). The advantage is that you can create custom roles for specific tasks and communicate these roles to the users who will use them. You would also get a small amount of connections which can leed to high performance tuning from your MySql database. Still it would take a lot of time consuming thinking when you design and configure your database in dealing with User Management. Hope you all don't mind if i come up with more topic discussions like this one. I must say it is a real mind breaker. Hope you enjoyed this discussion; please feel free to continue. I shall share my opinion on your comments. Best Regards, Danny Stolle EmoeSoft, Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems using Binary Install on Mac OS 10.4.1
http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Double check that mysqld is running. I usually find out that it's not when I get that error. kgt Elton Hughes wrote: Hello All, I am trying to install MySQL 4.1.12 on my Powermac G4. My OS is 10.4.1. I downloaded the binary distribution from the MySQL website. I uncompressed it and opened the .dmg installer. I ran the installer. Where it prompted for the Administrator's password, I gave it. The installation completed successfully. I then followed the instructions and installed the StartUp item without problem. I then tried to access the database and I get the following message: - mysql -u root ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) - Should the installer have installed mysql.sock in /tmp on Macs running OS 10.4.1? Is there a step I missed or is missing in the documentation? Can MySQL run on a Mac using 10.4.1? Thank you for your time. Elton = NOVA505 W. Olive Ave. Suite 550 Elton Hughes (IT) Sunnyvale CA 94086 Phone: 408-730-7235 Fax: 408-730-7643 - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems using Binary Install on Mac OS 10.4.1
Hello Kristen, It looks like I am running mysqld. - ps xa | grep mysqld 21281 p1- S 0:00.08 /bin/sh ./bin/mysqld_safe --datadir=/usr/ local/mysql/ 21323 p1- R 0:00.98 /usr/local/mysql/bin/mysqld --defaults- extra-file=/us 21324 p1- R 7:11.11 /bin/sh ./bin/mysqld_safe --datadir=/usr/ local/mysql/ 26615 p1 U+ 0:00.00 grep mysqld - I will check some more of the suggestions on that webpage. I am also curious about mysql.sock. I understand its function, but I do not understand its installation. Thank you for your help. Elton On Jun 15, 2005, at 10:41 AM, Kristen G. Thorson wrote: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Double check that mysqld is running. I usually find out that it's not when I get that error. kgt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
allow-subscribe not working on mysql list?
Hi, I've recently subscribed using the allow-subscribe method as described here: Posting from an alternative address when post are allowed only to subscribers.: http://www.ezmlm.org/ezman/ezman1.html But I'm still getting posts to the mysql lists on this address?? ezmlm did not complain when I set it up earlier this morning. /Per Jessen, Zurich -- http://www.spamchek.ch/freetrial - managed anti-spam and anti-virus solution. Lassen Sie sich überzeugen - 30 Tage Kostenlos! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.7-beta has been released
Hi, A new version of MySQL Community Edition 5.0.7-beta Open Source database management system has been released. This version includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the fourth published Beta release in the 5.0 series. All attention will continue to be focused on fixing bugs and stabilizing 5.0 for later production release. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual open and resolved bugs in this version. Changes in release 5.0.7: - Functionality added or changed: * Placeholders now can be used for LIMIT in prepared statements. (Bug #7306 (http://bugs.mysql.com/7306)) * SHOW BINARY LOGS now displays a File_size column that indicates the size of each file. * The --delayed-insert option for mysqldump has been disabled to avoid causing problems with storage engines that do not support INSERT DELAYED. (Bug #7815 (http://bugs.mysql.com/7815)) * Improved the optimizer to be able to use indexes for expressions of the form indexed_col NOT IN (val1, val2, ...) and indexed_col NOT BETWEEN val1 AND val2.. (Bug #10561 (http://bugs.mysql.com/10561)) * Removed mysqlshutdown.exe and mysqlwatch.exe from the Windows ``No Installer'' distribution (they had already been removed from the ``With Installer'' distribution before). Removed those programs from the source distribution. * Removed WinMySQLAdmin from the source distribution and from the ``No Installer'' Windows distribution (it had already been removed from the ``With Installer'' distribution before). Bugs fixed: * Using ORDER BY to sort the results of an IF() that contained a FROM_UNIXTIME() expression returned incorrect results due to integer overflow. (Bug #9669 (http://bugs.mysql.com/9669)) * Fixed a server crash resulting from accessing InnoDB tables within stored functions. This is handled by prohibiting statements that do an explicit or explicit commit or rollback within stored functions or triggers. (Bug #10015 (http://bugs.mysql.com/10015)) * Fixed a server crash resulting from the second invocation of a stored procedure that selected from a view defined as a join that used ON in the join conditions. (Bug #6866 (http://bugs.mysql.com/6866)) * Using ALTER TABLE for a table that had a trigger caused a crash when executing a statement that activated the trigger, and also a crash later with USE db_name for the database containing the table. (Bug #5894 (http://bugs.mysql.com/5894)) * Fixed a server crash resulting from an attempt to allocate too much memory when GROUP BY blob_col and COUNT(DISTINCT) were used. (Bug #11088 (http://bugs.mysql.com/11088)) * Fixed a portability problem for compiling on Windows with Visual Studio 6. (Bug #11153 (http://bugs.mysql.com/11153)) * The incorrect sequence of statements HANDLER tbl_name READ index_name NEXT without a preceding HANDLER tbl_name READ index_name = (value_list) for an InnoDB table resulted in a server crash rather than an error. (Bug #5373 (http://bugs.mysql.com/5373)) * On Windows, with lower_case_table_names set to 2, using ALTER TABLE to alter a MEMORY or InnoDB table that had a mixed-case name also improperly changed the name to lowercase. (Bug #9660 (http://bugs.mysql.com/9660)) * The server timed out SSL connections too quickly on Windows. (Bug #8572 (http://bugs.mysql.com/8572)) * Executing LOAD INDEX INTO CACHE for a table while other threads where selecting from the table caused a deadlock. (Bug #10602 (http://bugs.mysql.com/10602)) * Fixed a server crash resulting from CREATE TABLE ... SELECT that selected from a table being altered by ALTER TABLE. (Bug #10224 (http://bugs.mysql.com/10224)) * The FEDERATED storage engine properly handled outer joins, but not inner joins. (Bug #10848 (http://bugs.mysql.com/10848)) * Consistently report INFORMATION_SCHEMA table names in uppercase in SHOW TABLE STATUS output. (Bug #10059 (http://bugs.mysql.com/10059)) * Fixed a failure of WITH ROLLUP to sum values properly. (Bug #10982 (http://bugs.mysql.com/10982)) * Fixed server crash on Windows caused by USE db_name where db_name is a Windows device name such as LPT1 or PRN. (CAN-2005-0799 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0799)) (Bug #9148
Re: Table full
Mathias, Thanks for the reply. I couldn't find a server variable named max_temp_table_size but I did find one named max_heap_table_size. Is that what you meant? BTW, I forgot to mention that I'm using MySQL 4.0.20. Could it be that this variable that you mention is only in later versions? Basically, what I'm trying to do is a multi-table update statement. If I select too many rows I get the Table #sql-123 is full error. If I bite off a small enough chunk, the query works. Thanks again, Tripp --- [EMAIL PROTECTED] wrote: hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
sorri it's tmp_table_size. mysql show variables like '%table%'; ++--+ | Variable_name | Value| ++--+ | innodb_file_per_table | OFF | | innodb_table_locks | ON | | lower_case_table_names | 1| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 256 | | table_type | InnoDB | | tmp_table_size | 9437184 | ++--+ 8 rows in set (0.00 sec) What are : show create table toto; the count(*) ? the query ? Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Mathias, Thanks for the reply. I couldn't find a server variable named max_temp_table_size but I did find one named max_heap_table_size. Is that what you meant? BTW, I forgot to mention that I'm using MySQL 4.0.20. Could it be that this variable that you mention is only in later versions? Basically, what I'm trying to do is a multi-table update statement. If I select too many rows I get the Table #sql-123 is full error. If I bite off a small enough chunk, the query works. Thanks again, Tripp --- [EMAIL PROTECTED] wrote: hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
Mathias, Here's the query: UPDATE customer_indicator INNER JOIN customer_listing_pref ON customer_listing_pref.customer_id = customer_indicator.customer_id AND customer_listing_pref.store_id = customer_indicator.store_id AND customer_listing_pref.store_id = @OLD_STORE_ID LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id AND contact_log.store_id = @OLD_STORE_ID LEFT JOIN sent ON sent.pref_id = customer_listing_pref.pref_id SET customer_indicator.employee_id = @NEW_EMPLOYEE_ID, customer_indicator.store_id = @NEW_STORE_ID, customer_listing_pref.store_id = @NEW_STORE_ID, sent.store_id = @NEW_STORE_ID, contact_log.store_id = @NEW_STORE_ID WHERE customer_indicator.employee_id = @OLD_employee_id AND customer_indicator.store_id = @OLD_STORE_ID AND customer_indicator.customer_id BETWEEN 2 AND 23000; ++--+ | Variable_name | Value| ++--+ | lower_case_table_names | 0| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 64 | | table_type | MYISAM | | tmp_table_size | 33554432 | ++--+ I don't explicitly create any tables for this operation. I'm just trying to run the query. If I make the range in the BETWEEN condition of the WHERE claus sufficiently small the query runs. Otherwise I get the table is full error. So it seems that MySQL is doing some table creation behind the scenes. I pretty certain that I have enough disk space to perform the operation (I have about 10GB free). Thanks, Tripp --- [EMAIL PROTECTED] wrote: sorri it's tmp_table_size. mysql show variables like '%table%'; ++--+ | Variable_name | Value| ++--+ | innodb_file_per_table | OFF | | innodb_table_locks | ON | | lower_case_table_names | 1| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 256 | | table_type | InnoDB | | tmp_table_size | 9437184 | ++--+ 8 rows in set (0.00 sec) What are : show create table toto; the count(*) ? the query ? Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Mathias, Thanks for the reply. I couldn't find a server variable named max_temp_table_size but I did find one named max_heap_table_size. Is that what you meant? BTW, I forgot to mention that I'm using MySQL 4.0.20. Could it be that this variable that you mention is only in later versions? Basically, what I'm trying to do is a multi-table update statement. If I select too many rows I get the Table #sql-123 is full error. If I bite off a small enough chunk, the query works. Thanks again, Tripp --- [EMAIL PROTECTED] wrote: hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myodbc cygwin
Dear All, I tried several times to install myodbc 3.51.11 in cygwin (newest, recently updated version) without any success. ./configure works perfect, including the optional packages unixodbc and mysql which I have already installed without any problems. Make fails and make configure too. The error in make is: execute.c:721 : error: conflicting types for `sqlputdata` make [2] leaving directory ... Many thanks for help, Jan ___ Dr. Jan Bartholdy Freiheitsweg 64 16515 Oranienburg OT Friedrichsthal phone +49 3301 808129 fax +49 3301 808130 cellphone +49 172 3945713 email [EMAIL PROTECTED] + Virus checked by G DATA AntiVirusKit Version: AVK 15.0.5457 from 13.06.2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
At 04:35 PM 6/15/2005, you wrote: Mathias, Here's the query: UPDATE customer_indicator INNER JOIN customer_listing_pref ON customer_listing_pref.customer_id = customer_indicator.customer_id AND customer_listing_pref.store_id = customer_indicator.store_id AND customer_listing_pref.store_id = @OLD_STORE_ID LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id AND contact_log.store_id = @OLD_STORE_ID LEFT JOIN sent ON sent.pref_id = customer_listing_pref.pref_id SET customer_indicator.employee_id = @NEW_EMPLOYEE_ID, customer_indicator.store_id = @NEW_STORE_ID, customer_listing_pref.store_id = @NEW_STORE_ID, sent.store_id = @NEW_STORE_ID, contact_log.store_id = @NEW_STORE_ID WHERE customer_indicator.employee_id = @OLD_employee_id AND customer_indicator.store_id = @OLD_STORE_ID AND customer_indicator.customer_id BETWEEN 2 AND 23000; ++--+ | Variable_name | Value| ++--+ | lower_case_table_names | 0| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 64 | | table_type | MYISAM | | tmp_table_size | 33554432 | ++--+ I don't explicitly create any tables for this operation. I'm just trying to run the query. If I make the range in the BETWEEN condition of the WHERE claus sufficiently small the query runs. Otherwise I get the table is full error. So it seems that MySQL is doing some table creation behind the scenes. I pretty certain that I have enough disk space to perform the operation (I have about 10GB free). Thanks, Tripp --- [EMAIL PROTECTED] wrote: sorri it's tmp_table_size. mysql show variables like '%table%'; ++--+ | Variable_name | Value| ++--+ | innodb_file_per_table | OFF | | innodb_table_locks | ON | | lower_case_table_names | 1| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 256 | | table_type | InnoDB | | tmp_table_size | 9437184 | ++--+ 8 rows in set (0.00 sec) What are : show create table toto; the count(*) ? the query ? Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Mathias, Thanks for the reply. I couldn't find a server variable named max_temp_table_size but I did find one named max_heap_table_size. Is that what you meant? BTW, I forgot to mention that I'm using MySQL 4.0.20. Could it be that this variable that you mention is only in later versions? Basically, what I'm trying to do is a multi-table update statement. If I select too many rows I get the Table #sql-123 is full error. If I bite off a small enough chunk, the query works. Thanks again, Tripp --- [EMAIL PROTECTED] wrote: hi, seems to be a temp table (sybase notation). see max_temp_table_size Mathias Selon Emmett Bishop [EMAIL PROTECTED]: Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Tripp, This problem may occur if your table is quite large (several gb in size). The update may make the table too large to address using conventional MySQL pointers. You may need to modify the table so it has a Max Rows= option where is the max rows you expect the table to have and this forces MySQL to use a larger table pointer. When my tables exceeded 100 million rows, I got a similar error. I added Max Rows=10 to the table definition to solve the problem. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
Mike, Thanks for the insight. The sent table has about 7 million records. The other tables involved have tens of thousands of records or there abouts. Not your 100 million size but certainly worth exploring. Thanks again, Tripp --- mos [EMAIL PROTECTED] wrote: Tripp, This problem may occur if your table is quite large (several gb in size). The update may make the table too large to address using conventional MySQL pointers. You may need to modify the table so it has a Max Rows= option where is the max rows you expect the table to have and this forces MySQL to use a larger table pointer. When my tables === message truncated === __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is best open-source platform alternative to overcome innodb 2Gb memory limit on Linux? FreeBSD?
I agree The New AMD's (Can't say just opertron) but the 246 and 248 CPU's are moving data between cpu and Ram at 6 gig per second versus 2gig for the Xeon's peak right now. The New opterons communicate with ram better than any other CPU on the market and with the right MySql setup that is a huge benefit. My News site platform is going to be moving from Xeon to AMD for that very reason. Our software is written to avoid harddrive calls at all cost to keep our page load super fast. I would add to his suggestion a RAID 0-1 setup that would double your drive output speed. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.prnewsnow.com Free content for your website 469 228 2183 -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 15, 2005 11:17 AM To: Brady Brown Cc: mysql@lists.mysql.com Subject: Re: What is best open-source platform alternative to overcome innodb 2Gb memory limit on Linux? FreeBSD? Why not go AMD-64? Dual Opteron, with 8/16/32 gig of RAM? Get a 3ware SATA drive, and run Gentoo for AMD-64. You can increase your innodb buffer pool to use almost all that space. You can make your buffer pool as large as the physical RAM in your machine can support. No 2.5 gig per process, 4-gig limit on addressable memory (without the address-extensions). Your hardware is holding you back more than your operating system. David Brady Brown wrote: Hi, I am currently running a large database (around 20Gb) on a 32bit x86 Linux platform. Many of my larger data-crunching queries are disk-bound due to the limitation described in the innodb configuration documentation: *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| may allow the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB: Being a responsible citizen, I have my innodb_buffer_pool_size set below 2Gb. But the time has come to scale the application, so I need an alternative solution that will allow me to set innodb_buffer_pool_size as high as my heart desires (or at least well beyond 2Gb). Do any of you have battle-tested recommendations? How about FreeBSD? From what I can gather, it is a good idea to build MySQL on FreeBSD linked with the Linux Thread Library. Would doing so re- invoke the 2Gb limit? I look foward to your collective responses. Thanks! Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
