Mysql 4.0.13 with Linuxthreads problem in FreeBSD 5.1-beta - SOLVED!
Hi, I had a problem running MySQL-4.0.13 with Linuxthreads in FreeBSD 5.0. Before I compiled mysql-4.0.13 with WITH_CHARSET=win1251 WITH_OPENSSL=yes WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes options. It was compiled and installed properly. However when I try to connect to mysql server it says: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2). And /tmp/mysql.sock file was not in place. After few days I compiled mysql-4.0.13 from FreeBSD ports collection using: BUILD_STATIC=yes WITH_LINUXTHREADS=yes options. Now mysql works fine with Linuxthreads. It seems like dynamic building is not working right away. thanks anyway to all who tried to help me, Ganbold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
web app ideas
Hello all, This is my first post here so I'll make it worth it. I'm a programming student and am wanting to make a simple PHP and MySQL web based address book simply for a resume topic. It's going to be just simple enough to add contacs, remove them, and look them up. I'm curious as to exactly what I need to keep on the web server. Can I just have my html and php pages along with the one database uploaded to the server? I found a free hosting site that hosts PHP and MySQL sites and I wanted to get some advice from more experienced people. /TIMMY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I use MDK 8.2 and MySQL 4.0.13 i found this error ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Can anyone help me to solve this problem ? Thank u for advance
RE: ERROR 2002: Can't connect to local MySQL server through socke t '/var/lib/mysql/mysql.sock' (2)
Is your server daemon running? -Original Message- From: Pratchaya Chatuphian [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:25 AM To: [EMAIL PROTECTED] Subject: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I use MDK 8.2 and MySQL 4.0.13 i found this error ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Can anyone help me to solve this problem ? Thank u for advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 2002: Can't connect to local MySQL server through socke t '/var/lib/mysql/mysql.sock' (2)
You should be able to find a file in your data directory titled hostname.err. What is the content of that file? If the server is having problems running, you'll more than likely find information in that .err file. -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:31 AM To: 'Pratchaya Chatuphian'; '[EMAIL PROTECTED]' Subject: RE: ERROR 2002: Can't connect to local MySQL server through socke t '/var/lib/mysql/mysql.sock' (2) Is your server daemon running? -Original Message- From: Pratchaya Chatuphian [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:25 AM To: [EMAIL PROTECTED] Subject: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I use MDK 8.2 and MySQL 4.0.13 i found this error ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Can anyone help me to solve this problem ? Thank u for advance -- 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: Mysql 4.0.13 with Linuxthreads problem in FreeBSD 5.1-beta - SOLVED!
On Mon, Jun 09, 2003 at 04:14:05PM +0900, Ganbold wrote: Hi, I had a problem running MySQL-4.0.13 with Linuxthreads in FreeBSD 5.0. Before I compiled mysql-4.0.13 with WITH_CHARSET=win1251 WITH_OPENSSL=yes WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes options. It was compiled and installed properly. However when I try to connect to mysql server it says: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2). And /tmp/mysql.sock file was not in place. After few days I compiled mysql-4.0.13 from FreeBSD ports collection using: BUILD_STATIC=yes WITH_LINUXTHREADS=yes options. Now mysql works fine with Linuxthreads. It seems like dynamic building is not working right away. thanks anyway to all who tried to help me, Hmm. I wonder if the problem really was with with static vs. dynamic or something else? I know that when I first tried to get LinuxThreds working right, I pulled my hair out trying combinations of things I found in various web searches. Finally, I just copied the Makefile from the MySQL port and used that as the basis for my own. That made all the difference. I don't know which of the options did it (and part of me doesn't care), but after that it was pretty easy going. It's good to hear this still works in FreeBSD 5.x. Thanks for the update. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 182,443,458 queries (349/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index failure, cannot generate.
I'm guessing blob data? ~1500MB / 400rows = ~3.75MB /row Perfect guess. Two columns: Filename (char len 20) and Text ~ 3.75MB/row. -- Herbert Michael Kunzmann Binary Chaos Magician signature.asc Description: This is a digitally signed message part
is it possible to get around 4 billion row limit
Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detailed FULLTEXT index and search help needed
Hi Herbert,
I was reading both your threats once more carefully (the original post and the
repost).
1) I assume you are using the MyISAM table format.
You wrote that you have 400 text documents with each ~3.75 MB size.
2) How is the data access trend to such documents ? Do you update them all
regularly or do you use them as read only (mostly)?
3) Would it make sense to split them into several MyISAM tables or even
databases or directories. In case of using several MyISAM tables you could
create an index individually and use them as a merged table.
This way you should be able to work around the problem and also be able to
reduce the maintenance for the future.
Please let me know if I went down the wrong way ;-) or if I missed the spot
again ;-).
Best regards
Nils Valentin
Tokyo/Japan
2003 6 5 02:25H M Kunzmann :
Hello All.
I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.
I've created a fulltext index on the table, with
alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...
If I do a fulltext search against this table, it takes about
1.5 minutes.
select filename from table2002 where match(data) against ('whatever')
If I do an explain on my query, it confirms the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data| 0| | 1 |using where|
+--+-+-++---+---++---+
I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).
This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?
Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M
Should I set anything else ?
Please lend me a helping hand...
Thanks in advance.
H M Kunzmann
--
Valentin Nils
Internet Technology
E-Mail: [EMAIL PROTECTED]
URL: http://www.knowd.co.jp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delphi 7 and MySQL 4.1.0 alpha
Hello, I'm trying to get connection on MySQL 4.1.0 alpha edition by using the SQLCOnnection (dbExpress) object. I am having the error 'Unable to load libmysql.dll'. I placed this file into the winnt\system32 folder... still didn't work. I put the whole path into the VendorLib... still didn't work. Any ideas? I have to use this version of MySQL as it succefully supports the SubSelects. Thanks George Christoforakis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to empty the cache?
Hi. I'm doing some testings and I want to know how to empty the cache in mysql (versions 3.23 and 4.0) between the differents tests. Iago Sineiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to empty the cache?
On Mon, Jun 09, 2003 at 12:30:23PM +0200, Iago Sineiro wrote: Hi. I'm doing some testings and I want to know how to empty the cache in mysql (versions 3.23 and 4.0) between the differents tests. What cache? The query only exists in MySQL 4.0. If you want an empty key buffer, I'd suggest stopping and starting MySQL. That won't affect the operating system's disk cache but it will flush any caching MySQL has done. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 184,858,313 queries (347/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delphi 7 and MySQL 4.1.0 alpha
Did you try to put this dll into your application directory? The common way to use this dll is to hold it together with executable. [EMAIL PROTECTED] wrote: Hello, I'm trying to get connection on MySQL 4.1.0 alpha edition by using the SQLCOnnection (dbExpress) object. I am having the error 'Unable to load libmysql.dll'. I placed this file into the winnt\system32 folder... still didn't work. I put the whole path into the VendorLib... still didn't work. Any ideas? I have to use this version of MySQL as it succefully supports the SubSelects. Thanks George Christoforakis. -- Veysel Harun Sahin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to empty the cache?
Hi Iago, I am unsure if I understand your question correctly, MySQL has a t least 5 different chaches (to name only the most important ones). key_cache table_cache thread_cache hostname_cache evt. disc cache (memory) or do you mean buffer variables ? sort_buffer record_buffer join_buffer_size myisam_sort_buffer net_buffer_length tmp_table_size I guess if you lookup any of the variables above you will quickly sort out the ones which applies to your question and the ones that don't. Sorry, but with the information given I am unable to be more precise. Best regards Nils Valentin Tokyo/Japan 2003 6 9 19:30Iago Sineiro : Hi. I'm doing some testings and I want to know how to empty the cache in mysql (versions 3.23 and 4.0) between the differents tests. Iago Sineiro -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with DBI connection interaction with sub-process
Hi,
Maybe this is a DBI question rather than MySQL, but here
goes...
I have a perl script which forks in order to work around a
memory leak in XML::Parser.
The child parses the data and the parent reads the
results back from the child to populate some temporary
tables in the database.
What seems to happen is that when the child exits, the
DBI connection to the database is lost and automatically
reconnected (how?). Obviously this blows away my temporary
tables.
Does anyone know how to avoid this ?
I've tried $SIG{CHLD}='IGNORE'; with no effect.
My current work around is to read all the data into the
parent and then do the DB work, but is there a better way ?
regards,
Martin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't send Register ID
Thank you so much ;-)
- Original Message -
From: Fred van Engen [EMAIL PROTECTED]
To: Robson Oliveira [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, June 08, 2003 2:30 PM
Subject: Re: Can't send Register ID
Hi,
On Sun, Jun 08, 2003 at 01:53:24PM -0300, Robson Oliveira wrote:
I'm trying to send a e-mail with the record register ID from a PHP code
query and don't show the value.
// Connect to database
$db = mysql_connect (localhost, my_db, passxx);
$db = mysql_connect (localhost, my_db, passxx);
if (!$db) {
print 'mysql_connect error: '.mysql_error();
}
else {
// Select the database
mysql_select_db(my_db, $db);
if (!mysql_select_db(my_db, $db)) {
print 'mysql_select_db error: '.mysql_error();
}
else {
// Select user registration ID from the database based on the last record
string $ssecurity_record from the registration page.
$register_id = mysql_query( SELECT register_id from table_register WHERE
$ssecurity_record = ssecurity_record;
Are you sure it gets this far? Your line above won't compile because of
a missing and a missing ).
$result = mysql_query( SELECT register_id from table_register WHERE
$ssecurity_record = ssecurity_record );
if (!result) {
print 'mysql_query error: '.mysql_error();
}
else {
$row = mysql_fetch_row($result);
if (!row) {
print 'mysql_fetch_row: no row, error: '.mysql_error();
}
else {
$register_id = $row['register_id'];
echo $register_id = At this time the value is blank when I print the value
}
}
}
}
Please, If someone know how to print the record value and Select function in
php send to me.
Read the manual, check return values, etc. If you're running PHP in
apache, then check the error_log and use the error_log() function
instead of print as I use in the code above.
My code above is untested BTW.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
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 all mysql versions from 4.1 support sub selects?
Hi Mysql List, Will all mysql versions (std, max and debug) from 4.1 support sub selects or will this only be included f.e in the max version ? Best regards -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with odbc
Hi all... I have a problem connecting an ODBC to my mysql database I give my Ip username and password but it returns me the address... Access deny . I ma sure that username and password are correct.. Someone can give me suggests please? [EMAIL PROTECTED] http://www.motormaniaci.com il portale per gli appassionati di motore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with DBI connection interaction with sub-process
You could try forking before making a connection to the database. A forked program will share the same resources (I think), and the DBI could be getting the child signal and closing connection. However, any resources you create after the fork will be your own. Hope it helps (bit of a guess really), Robin Keech Java Developer Synectics Ltd -Original Message- From: Martin Waite [mailto:[EMAIL PROTECTED] Sent: 09 June 2003 12:10 To: MySQL List Subject: problem with DBI connection interaction with sub-process snip What seems to happen is that when the child exits, the DBI connection to the database is lost and automatically reconnected (how?). Obviously this blows away my temporary tables. Does anyone know how to avoid this ? snip regards, Martin --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.474 / Virus Database: 272 - Release Date: 18/04/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Certification study quide from Dubois, Hinz and Pedersen
Hello Paul Dubois, Stefan Hinz, Carsten Pedersen, hello Mysql List, I was wondering if you guys would let me know about the release schedule of the MySQL Certification Study Guide which is about to be released - as announced on the UC 2 months ago. Any information much appreciated - no info on www.amazon.com so far. Best regards -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with DBI connection interaction with sub-process
Hi Robin, This sounds bang-on. Many thanks. regards, Martin On Mon, 2003-06-09 at 13:34, Robin Keech wrote: You could try forking before making a connection to the database. A forked program will share the same resources (I think), and the DBI could be getting the child signal and closing connection. However, any resources you create after the fork will be your own. Hope it helps (bit of a guess really), Robin Keech Java Developer Synectics Ltd -Original Message- From: Martin Waite [mailto:[EMAIL PROTECTED] Sent: 09 June 2003 12:10 To: MySQL List Subject: problem with DBI connection interaction with sub-process snip What seems to happen is that when the child exits, the DBI connection to the database is lost and automatically reconnected (how?). Obviously this blows away my temporary tables. Does anyone know how to avoid this ? snip regards, Martin --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.474 / Virus Database: 272 - Release Date: 18/04/2003 -- 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: Problems with odbc
If you are using Windows 9x or any Microsoft Operating System ... please check if you have setup the User DSN (Control Panel - ODBC Data Source Administrator )correctly.. If you do, I think it will work fine. - Original Message - From: Sbandy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 7:17 PM Subject: Problems with odbc Hi all... I have a problem connecting an ODBC to my mysql database I give my Ip username and password but it returns me the address... Access deny . I ma sure that username and password are correct.. Someone can give me suggests please? [EMAIL PROTECTED] http://www.motormaniaci.com il portale per gli appassionati di motore -- 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: Problems with odbc
I solve my problem due to a missconfiguration of my server on table grant. Thanks a lot to all and sorry for the disturb At 19.48 09/06/2003 +0700, Edwin Tanaga wrote: If you are using Windows 9x or any Microsoft Operating System ... please check if you have setup the User DSN (Control Panel - ODBC Data Source Administrator )correctly.. If you do, I think it will work fine. - Original Message - From: Sbandy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 09, 2003 7:17 PM Subject: Problems with odbc Hi all... I have a problem connecting an ODBC to my mysql database I give my Ip username and password but it returns me the address... Access deny . I ma sure that username and password are correct.. Someone can give me suggests please? [EMAIL PROTECTED] http://www.motormaniaci.com il portale per gli appassionati di motore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.motormaniaci.com il portale per gli appassionati di motore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get meta data info in MySQL
My database editor uses: describe name; Karen Chu wrote: I want to be able to get all table names in a database and all the column names and associated data type from a particular table. I wonder how to do that in MySQl. I understand I can use show tables to see the table info, but I want to be able to do 'select' in order to get the results back. I want to know if there is something like select name from sysobject in Sybase or select name from all_tables in Oracle. Thanks. I would appreciate any advice. Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux install problem
Steve Mansfield wrote: Having run MySQL successfully for a while in a WAMP environment, I'm trying to set up on Linux now (and am a real Linux newbie). I'm using the 4.3.1 version that came as an RPM with SuSE 8.2. The problem is - it doesn't work. Here's where the various elements have installed to: /usr/bin various mysql progs scripts, incl mysql mysqladmin mysql_install_db safe_mysqld etc /usr/sbin mysqld /var/lib/mysql mysql.sock mysql.log linux-bin.001 etc when I run mysql_install_db (in /usr/bin), it creates the 'mysql' dir in var/lib/mysql - so the path to the default database files becomes: /var/lib/mysql/mysql/ I'll bet you didn't run mysql_install_db with the --user=mysql option. chown -R mysql.mysql /var/lib/mysql should do the trick. From /usr/bin, I run: safe_mysqld --user=mysql and get the message Starting mysqld daemon with databases from /var/lib/mysql but then the daemon shuts down straight away. When I look in the log file, I find: /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) This is because mysqld can't access /var/lib/mysql/mysql. perror 13 will show you that it is a permissions problem. It looks as though the mysql_install_db has put the files in the wrong place. No, just installed them owned by the user running the script. I am assuming 'root'. Can anyone give me some advice as to how I sort this out...? Thanks. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help! How to generate a list of consecutive numbers with a SELECT?
I've tried to find a way to generate a list of consecutive numbers with a SELECT statement. I would like something like Select ... that would result the numbers between 1 and 20 on separate rows. So far, the only solution I have found is to have a table with consecutive numbers from 1 to 1000, and then I can just select the range I want from that table. I've looked in the documentation, but haven't found anything about a nicer solution. If anyone knows of a better solution please share. Thanks in advance. Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get meta data info in MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: Thanks for the reply! Yes, I understand I can do show tables to show all table names in a database and I can do describe table_name to get all column information for a table. But in the application programming, I want to be able to do 'select' and be able to see table names and column info as a query result set. Can MySQL do that? There must be a place to store this information. In oracle or Sybase, these are store in system tables (actual table) so I can do 'select' from them to get a result set. I am new to MySQL. I wonder where these meta data stored in MySQL. Any thoughts are appreciated. Thanks! Either of those commands (show/describe) return the data in a result set form. Is there a reason that doesn't work for you? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5JnhtvXNTca6JD8RAkm2AKCNzJUSCLnrzXKvAMnmQ2W5cwzYoQCfVbPn okkkEY8/4XQL91gUyQw6zQw= =jZ+B -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL privileges System
At 12:24 -0700 6/8/03, Mike Walth wrote: Hello: I was wondering if anyone had any advice to a couple questions. 1) Can I add additional columns to the user and db tables of mysql. These would be for administrative purposes only. You can, but doing so is unsupported, and the server won't do anything with them. You also introduce the possibility of conflict with changes made the the grant tables in future releases. 2) I'm trying to figure out how the privileges effect the system. I understand what each one does, but haven't found a clear explanation of what the difference is in the db table and the user table. If any one can explain this I would appreciate it. user = global privileges db = db-specific privileges 3) I'm looking to come up with the most secure set of privileges for the user and db tables. I want the users to be able to select, insert, delete, create tables, just the basic functions on their database only. Multiple databases are hosted on the same mysql server. Don't grant them privileges in the user table, because those are global. Grant them on a database-specific level. Thank you for your help in these questions. Mike Walth CinoFusion -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to empty the cache?
Sorry for the ambiguity. I'm testing the useful of adding indexes to one table for one type of query. When I execute the same query for the second time it executes faster beacuse it's using the cache (the key_cache or the query cache if it is enabled). Is it possible to empty the key_cache (and also the query cache) without shutdown the mysql server? Iago Sineiro. -Mensaje original- De: Nils Valentin [mailto:[EMAIL PROTECTED] Enviado el: lunes, 09 de junio de 2003 12:48 Para: Iago Sineiro; MySql Mail List Asunto: Re: How to empty the cache? Hi Iago, I am unsure if I understand your question correctly, MySQL has a t least 5 different chaches (to name only the most important ones). key_cache table_cache thread_cache hostname_cache evt. disc cache (memory) or do you mean buffer variables ? sort_buffer record_buffer join_buffer_size myisam_sort_buffer net_buffer_length tmp_table_size I guess if you lookup any of the variables above you will quickly sort out the ones which applies to your question and the ones that don't. Sorry, but with the information given I am unable to be more precise. Best regards Nils Valentin Tokyo/Japan 2003 6 9 19:30Iago Sineiro : Hi. I'm doing some testings and I want to know how to empty the cache in mysql (versions 3.23 and 4.0) between the differents tests. Iago Sineiro -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp ---Publicidad nete a los miles de sin pareja en Meetic... te vas a enamorar! http://www.iespana.es/_reloc/email.meetic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Batch Deletes...
You may find that it is the index that is slowing you down. Have you tried disabling the index(s) on your table before the delete and re-enabling them after? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Todd Gruben [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:02 AM To: [EMAIL PROTECTED] Subject: Batch Deletes... In an effort to speed up large batch deletes i devised this script. LOCK TABLES t1 WRITE; SELECT * INTO OUTFILE '/data/dump/t1' FROM t1 where date=2003-06-09; TRUNCATE TABLE t1; LOAD DATA INFILE '/data/dump/t1' INTO TABLE t1; UNLOCK TABLES; this script would work great without the LOCK TABLES clause. I receive an error saying there active locked tables. This is a heap table and I am running mysql 4.0.12 Any ideas? i need the lock tables because there are some updates that will be happening to the current data, i just need to remove the old expired data. This takes about 30secs and my delete scripts takes about 4 hours. It is deleting about 500,000 rows out of a 4 million row table with several indexes. -Todd -- 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: How to empty the cache?
In the last episode (Jun 09), Iago Sineiro said: Sorry for the ambiguity. I'm testing the useful of adding indexes to one table for one type of query. When I execute the same query for the second time it executes faster beacuse it's using the cache (the key_cache or the query cache if it is enabled). Is it possible to empty the key_cache (and also the query cache) without shutdown the mysql server? flush tables should empty all mysql caches. But remember that MySQL also makes heavy use of the OSes buffer cache (for MyISAM table data, for example). If you really want reliable results, shut down mysql, then dismount and remount the filesystem holding your table and index files between tests, to force the OS the release its filesystem buffers. Alternatively, dd'ing a file larger than your physical RAM should also flush the OS cache. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get meta data info in MySQL
I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 7:30 AM To: Karen Chu Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: How to get meta data info in MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: Thanks for the reply! Yes, I understand I can do show tables to show all table names in a database and I can do describe table_name to get all column information for a table. But in the application programming, I want to be able to do 'select' and be able to see table names and column info as a query result set. Can MySQL do that? There must be a place to store this information. In oracle or Sybase, these are store in system tables (actual table) so I can do 'select' from them to get a result set. I am new to MySQL. I wonder where these meta data stored in MySQL. Any thoughts are appreciated. Thanks! Either of those commands (show/describe) return the data in a result set form. Is there a reason that doesn't work for you? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5JnhtvXNTca6JD8RAkm2AKCNzJUSCLnrzXKvAMnmQ2W5cwzYoQCfVbPn okkkEY8/4XQL91gUyQw6zQw= =jZ+B -END PGP SIGNATURE- -- 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: How to get meta data info in MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. MySQL does not currently store this information in system tables. 'SHOW' / 'DESCRIBE' are treated as queries by MySQL (just a special case of a query), so I'm still confused as why they won't work for you. Is there a reason that you _have_ to use system tables for this functionality? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5KWHtvXNTca6JD8RAoI2AKDJoYzsvK2UEnA98ttB7wCm6GxOcwCgot7g IN4IyjvSI6aApNvx9tEYvHE= =VPrD -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to empty the cache?
Thanks. I will try it. Iago Sineiro. -Mensaje original- De: Dan Nelson [mailto:[EMAIL PROTECTED] Enviado el: lunes, 09 de junio de 2003 16:54 Para: Iago Sineiro CC: MySql Mail List Asunto: Re: How to empty the cache? In the last episode (Jun 09), Iago Sineiro said: Sorry for the ambiguity. I'm testing the useful of adding indexes to one table for one type of query. When I execute the same query for the second time it executes faster beacuse it's using the cache (the key_cache or the query cache if it is enabled). Is it possible to empty the key_cache (and also the query cache) without shutdown the mysql server? flush tables should empty all mysql caches. But remember that MySQL also makes heavy use of the OSes buffer cache (for MyISAM table data, for example). If you really want reliable results, shut down mysql, then dismount and remount the filesystem holding your table and index files between tests, to force the OS the release its filesystem buffers. Alternatively, dd'ing a file larger than your physical RAM should also flush the OS cache. -- Dan Nelson [EMAIL PROTECTED] ---Publicidad Znete a los miles de sin pareja en Meetic... !te vas a enamorar! http://www.iespana.es/_reloc/email.meetic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My SQL ERROR 2002
skjois [EMAIL PROTECTED] wrote: I get this error ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) how can i get rid of this? Easily. Start MySQL and get rid of the message. :-) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Help! How to generate a list of consecutive numbers with a SELECT?
Martin Szabo [EMAIL PROTECTED] wrote: I've tried to find a way to generate a list of consecutive numbers with a SELECT statement. I would like something like Select ... that would result the numbers between 1 and 20 on separate rows. So far, the only solution I have found is to have a table with consecutive numbers from 1 to 1000, and then I can just select the range I want from that table. Not exactly what you are looking for, but you can use SQL variables in a manner like: SET @a=1; SELECT @a:[EMAIL PROTECTED],@a:[EMAIL PROTECTED] ... in that case you can use the @a:[EMAIL PROTECTED] construct in your SQL statement and the result value will be incremented by one each time it is retrieved. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Do all mysql versions from 4.1 support sub selects?
Nils Valentin [EMAIL PROTECTED] wrote: Will all mysql versions (std, max and debug) from 4.1 support sub selects or will this only be included f.e in the max version ? In all. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Loading database information from a text file.
from a command prompt mysqldump -opt database dumpfile Curtis On Sunday 08 June 2003 02:59, JeRRy wrote: Okay thanks. One last thing. From the mysql prompt how can I generate a database backup? (So I can see the database setup in notepad so I can backup and transfer easily online.) Jerry --- Paul DuBois [EMAIL PROTECTED] wrote: At 14:52 +1000 6/8/03, JeRRy wrote: Hi, I have a text file with all my database information to create databases to setting up tables. I have looked throught the mysql docs onto how to load these on from the mysql prompt. But all I can find is: load data infile filename.txt into table whatever But this is not what I am looking for. :( As the tables are already defined in the text file and the appropiate database name to enter them into. Is there a way to load these into the database directly instead of doing each table seperately from the mysql prompt? (not web admin) Thanks in advance. Kind Regards, Jerry Do you mean that the text file contains SQL statements? If so, you can execute them like this: mysql filename or maybe (if you don't select the database within the file): mysql dbname filename -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ http://mobile.yahoo.com.au - Yahoo! Mobile - Check compose your email via SMS on your Telstra or Vodafone mobile. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: array/pointer question
Hi, MySQL, like other relational databases, does not support arrays or pointers. Arrays and pointers do not fit within the relational model. It would take quite a bit of text to describe what the relational model is and how to use SQL to manipulate data. I suggest that you read a good text on relational databases and the relational model first suggested by Dr. Codd and then come back and ask questions. Anyone on this list would be happy to answer them. Anybody got any good titles or URLs for Ioana? John Griffin -Original Message- From: Ioana [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 10:50 AM To: [EMAIL PROTECTED] Subject: array/pointer question Hello, I have just recently started learning mysql, and was wondering if it supports arrays/pointers(for linked lists) of any types, and if so, what is the syntax... could not find info on this in any manual. 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]
Re: Batch Deletes...
At 9:01 -0500 6/9/03, Todd Gruben wrote: In an effort to speed up large batch deletes i devised this script. LOCK TABLES t1 WRITE; SELECT * INTO OUTFILE '/data/dump/t1' FROM t1 where date=2003-06-09; TRUNCATE TABLE t1; LOAD DATA INFILE '/data/dump/t1' INTO TABLE t1; UNLOCK TABLES; this script would work great without the LOCK TABLES clause. I receive an error saying there active locked tables. This is a heap table and I am running mysql 4.0.12 At which statement does the error occur? Any ideas? i need the lock tables because there are some updates that will be happening to the current data, i just need to remove the old expired data. This takes about 30secs and my delete scripts takes about 4 hours. It is deleting about 500,000 rows out of a 4 million row table with several indexes. -Todd -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification study quide from Dubois, Hinz and Pedersen
offlist Hello Stefan, well I have my sources ... ;-) .. I guess its no secret ,especially as it was announced on the UC. I guess when you say Q3 you mean the english version, will there also be a german version ? If yes , when ? I am not asking to pin you down really I am asking so that I can make plans. I understand (unfortunately) that it is not you 3 guys deciding when it is released. As far as I understand you have finished the text and it is now in the process of proovereading perhaps and will then eventually come when the publisher thinks that it is a good time to marcet point, right ? Anyway to cut a long story short, I would appreciate if you could let me know the ISBN and publisher so that I can write it on my wish-list ;-) . Or ...Perhaps you could even give a hint to the publisher that I would also participate as a proove reader or beta-tester whatever described the position most. That would be marvelous, but we dont want to reach for the stars.. ;-) Best regards Nils Valentin Tokyo/Japan nils(at)knowd.co.jp 2003 6 10 00:31: Nils, Hello Paul Dubois, Stefan Hinz, Carsten Pedersen, Ah! How did you find out? ;-) I was wondering if you guys would let me know about the release schedule of the MySQL Certification Study Guide which is about to be released - as announced on the UC 2 months ago. Any information much appreciated - no info on www.amazon.com so far. I reckon the Study Guides will be in the bookstores end of Q3 -- don't pin me down on this. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array/pointer question
Arrays are coming from version 5.0 as far as I know the plans. Best regards Nils Valentin 2003 6 9 23:49Ioana : Hello, I have just recently started learning mysql, and was wondering if it supports arrays/pointers(for linked lists) of any types, and if so, what is the syntax... could not find info on this in any manual. Thank you! -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Deletes...
At 11:01 -0500 6/9/03, Todd Gruben wrote: in the TRUNCATE TABLE t1 This is the documented behavior: http://www.mysql.com/doc/en/TRUNCATE.html You cannot use TRUNCATE on tables for which you hold locks. On Monday 09 June 2003 10:57 am, you wrote: At 9:01 -0500 6/9/03, Todd Gruben wrote: In an effort to speed up large batch deletes i devised this script. LOCK TABLES t1 WRITE; SELECT * INTO OUTFILE '/data/dump/t1' FROM t1 where date=2003-06-09; TRUNCATE TABLE t1; LOAD DATA INFILE '/data/dump/t1' INTO TABLE t1; UNLOCK TABLES; this script would work great without the LOCK TABLES clause. I receive an error saying there active locked tables. This is a heap table and I am running mysql 4.0.12 At which statement does the error occur? Any ideas? i need the lock tables because there are some updates that will be happening to the current data, i just need to remove the old expired data. This takes about 30secs and my delete scripts takes about 4 hours. It is deleting about 500,000 rows out of a 4 million row table with several indexes. -Todd -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification study quide from Dubois, Hinz and Pedersen
Hi Paul, 2003 6 10 01:26Paul DuBois : At 1:08 +0900 6/10/03, Nils Valentin wrote: offlist Hello Stefan, well I have my sources ... ;-) .. I guess its no secret ,especially as it was announced on the UC. I guess when you say Q3 you mean the english version, will there also be a german version ? If yes , when ? Any idea if and /or when there will be a german version ? I am not asking to pin you down really I am asking so that I can make plans. I understand (unfortunately) that it is not you 3 guys deciding when it is released. As far as I understand you have finished the text and it is now in the process of proovereading perhaps and will then eventually come when the publisher thinks that it is a good time to marcet point, right ? Speculation of any kind is unwarranted. Of course. Anyway to cut a long story short, I would appreciate if you could let me know the ISBN and publisher so that I can write it on my wish-list ;-) . I still I would appreciate if you could let me know the ISBN and publisher so that I can write it on my wish-list ;-) . Or ...Perhaps you could even give a hint to the publisher that I would also participate as a proove reader or beta-tester whatever described the position most. That would be marvelous, but we dont want to reach for the stars.. ;-) Best regards Nils Valentin Tokyo/Japan nils(at)knowd.co.jp 2003N 6 10 j 00:31A: Nils, Hello Paul Dubois, Stefan Hinz, Carsten Pedersen, Ah! How did you find out? ;-) I was wondering if you guys would let me know about the release schedule of the MySQL Certification Study Guide which is about to be released - as announced on the UC 2 months ago. Any information much appreciated - no info on www.amazon.com so far. I reckon the Study Guides will be in the bookstores end of Q3 -- don't pin me down on this. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get meta data info in MySQL
Thanks for the reply! Please see my pseudo code below. @@@ mark is the place where my questions come from. I wonder if it is doable in MySQL. I wish to have code like this: (@@@ is where my questions come from) // connect to the DB // list all table names in the dtabase print trtd bgcolor=#1A4D80 align=center$table Table/font/td/tr; // $query_all_tables = select name from all_tables ='$table'; $result_table_names = mysql_query($query_all_tables) or die(no tables were ever defined\n); $row_table_name = mysql_fetch_array($result_table_names); $table_in_db = $row_table_name[Tables in the DB]; ... // once a table is selected, let's try to get all fields information in this table //@@@ $query_all_fields - select column_name from all_columns where table_name ='$selected_table'; $result_field_names = mysql_quert($query_all_fields) or die(no fields?\n); $row_field_name = mysql_fetch_array($result_field_names); $field_in_table = $row_field_name[Fields in the Table]; -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:20 AM To: Karen Chu Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: How to get meta data info in MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. MySQL does not currently store this information in system tables. 'SHOW' / 'DESCRIBE' are treated as queries by MySQL (just a special case of a query), so I'm still confused as why they won't work for you. Is there a reason that you _have_ to use system tables for this functionality? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5KWHtvXNTca6JD8RAoI2AKDJoYzsvK2UEnA98ttB7wCm6GxOcwCgot7g IN4IyjvSI6aApNvx9tEYvHE= =VPrD -END PGP SIGNATURE- -- 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]
Fw: 2 questions about indexing and testing speed
Hi, I'm learning MySQL and I got two question: 1) As I know, MySQL has a buffer to keep the last records which have been retreived. So if I run a query two times, the second time will be faster. How can I temporarly stop this service? because I want to run different queries and compare their speed and find the best solution for a problem. 2) A table has 2 fields, Active CHAR(1), and Company CHAR(40). Sometimes I want to search a company name and sometimes I want to search an active company. For faster searching, do I need to create two index like these: CREATE INDEX active ON customer (CONCAT(active, company)); CREATE INDEX company ON customer (company); If I create just the first index, do I still have a fast searching on company name ( without mentioning Active )? like this: SELECT * FROM customer WHERE company = 'my test company'; Thanks
Re: How to get meta data info in MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: Thanks for the reply! Please see my pseudo code below. @@@ mark is the place where my questions come from. I wonder if it is doable in MySQL. I wish to have code like this: (@@@ is where my questions come from) // connect to the DB // list all table names in the dtabase print trtd bgcolor=#1A4D80 align=center$table Table/font/td/tr; // $query_all_tables = select name from all_tables ='$table'; You could just replace this with 'SHOW TABLES' in MySQL, or if you need to match a particular table name or name pattern, you can add a LIKE clause at the end, see: http://www.mysql.com/doc/en/SHOW.html $result_table_names = mysql_query($query_all_tables) or die(no tables were ever defined\n); $row_table_name = mysql_fetch_array($result_table_names); $table_in_db = $row_table_name[Tables in the DB]; ... // once a table is selected, let's try to get all fields information in this table //@@@ $query_all_fields - select column_name from all_columns where table_name ='$selected_table'; Here you would just use 'SHOW COLUMNS FROM [tablename]' substituting whatever table name you wanted. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5MJ5tvXNTca6JD8RAqyaAJ99dRIGebW0WYUFZsHTU9YHMbDF6gCfb39w Km5B97EbYIJVM30MUY/DUrQ= =Wo3Q -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get meta data info in MySQL
At 10:11 -0700 6/9/03, Karen Chu wrote: Thanks for the reply! Please see my pseudo code below. @@@ mark is the place where my questions come from. I wonder if it is doable in MySQL. I'm not sure why you wonder that. You've received several replies so far that indicate you cannot use SELECT for this purpose, and that you should use SHOW or DESCRIBE instead. Several people also have asked if there is some reason you cannot use SHOW/DESCRIBE, and you don't appear to have answered that question. Is there some reason? I wish to have code like this: (@@@ is where my questions come from) // connect to the DB // list all table names in the dtabase print trtd bgcolor=#1A4D80 align=center$table Table/font/td/tr; // $query_all_tables = select name from all_tables ='$table'; $result_table_names = mysql_query($query_all_tables) or die(no tables were ever defined\n); $row_table_name = mysql_fetch_array($result_table_names); $table_in_db = $row_table_name[Tables in the DB]; ... // once a table is selected, let's try to get all fields information in this table //@@@ $query_all_fields - select column_name from all_columns where table_name ='$selected_table'; $result_field_names = mysql_quert($query_all_fields) or die(no fields?\n); $row_field_name = mysql_fetch_array($result_field_names); $field_in_table = $row_field_name[Fields in the Table]; -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:20 AM To: Karen Chu Cc: 'gerald_clark'; [EMAIL PROTECTED] Subject: Re: How to get meta data info in MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karen Chu wrote: I wish to have something like 'select name from all_tables' in oracle or 'select name from sysobjects' in Sybase. Oracle has 'show tables' or 'describe table' too, but actual meta data is *stored* in systems tables. I wonder if MySQL also stores these in system tables or files or somewhere else. MySQL does not currently store this information in system tables. 'SHOW' / 'DESCRIBE' are treated as queries by MySQL (just a special case of a query), so I'm still confused as why they won't work for you. Is there a reason that you _have_ to use system tables for this functionality? -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+5KWHtvXNTca6JD8RAoI2AKDJoYzsvK2UEnA98ttB7wCm6GxOcwCgot7g IN4IyjvSI6aApNvx9tEYvHE= =VPrD -END PGP SIGNATURE- -- 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] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
arrangement of fields in a table
Hi, I need to define for example 100 fields in a table. 8 of them are more important and many queries just need these fields. Should I define them before the other fields in the table or it doesn't matter? Is accessing the the beginning fields faster than the other fields? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ADD CONSTRAINT on InnoDB tables
Hi all, I've just installed mysql 4.0.1 on my pc and I'm trying to migrate a set of old MyISAM table to InnoDB type table, to introduce referential integrity on my data. I found this problem. I create an index for each key in the references and the sql command ALTER TABLE utenti ADD CONSTRAINT FOREIGN KEY (iddipartimento) REFERENCES dipartimenti(iddipartimento) ON DELETE RESTRICT ON UPDATE CASCADE; goes through correctly. But trying to display the 'create table' of table 'utenti', it seems that the ON DELETE RESTRICT had been ignored, giving me this output: | utenti | CREATE TABLE `utenti` ( `idutente` int(4) NOT NULL auto_increment, `username` varchar(30) default NULL, `password` varchar(30) default NULL, `realname` varchar(100) default NULL, `attivo` int(1) default '1', `data` date default '2003-06-01', `flag_respons` int(1) default NULL, `iddipartimento` int(4) NOT NULL default '0', PRIMARY KEY (`idutente`), KEY `iddipartimento` (`iddipartimento`), CONSTRAINT `0_155` FOREIGN KEY (`iddipartimento`) REFERENCES `dipartimenti` (`iddipartimento`) ON UPDATE CASCADE ) TYPE=InnoDB | reporting only the 'ON UPDATE CASCADE' directive. Finally, trying to specify only the 'ON DELETE RESTRICT' directive, no directive are displayed on the 'create table', showing once again that this specific directive is ignored. Nevertheless, the reference integrity works fine, beacuse I cannot DELETE a row referenced by another table: trying to do it, mysql delete from dipartimenti where iddipartimento=1; it results a ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails error. Anyone has an idea about the reason of this behavior? Thanks in advance. AVE ATQUE VALE Marco Barbato Tutto ciò che non viene donato andrà perduto. (proverbio indiano) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subselects in 4.1 and indexes
Im have a problem / misunderstanding with the 4.1 release. Im having issues with indexes related to 'IN' vs '=' in subselects. assume the subselect returns 1 value select SQL_NO_CACHE user_id from table_a where user_id in (select SQL_NO_CACHE user_id from table_b where ... blah) 9.5 seconds... select SQL_NO_CACHE user_id from table_a where user_id = (select SQL_NO_CACHE user_id from table_b where ... blah) 0.0 seconds explain select SQL_NO_CACHE user_id from table_a where user_id in (select SQL_NO_CACHE user_id from table_b where ... blah) ... not using index... explain select SQL_NO_CACHE user_id from table_a where user_id = (select SQL_NO_CACHE user_id from table_b where ... blah) .. using index on user_id and the subselect take 0.0 seconds to run as well.. Does just using the IN operator prohibit table_a from using the user_id index? Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrangement of fields in a table
On Mon, Jun 09, 2003 at 01:40:47PM -0400, Mojtaba Faridzad wrote: Hi, I need to define for example 100 fields in a table. 8 of them are more important and many queries just need these fields. Should I define them before the other fields in the table or it doesn't matter? Is accessing the the beginning fields faster than the other fields? What's most important is that you define the consecutively. It'll minimize the number of disk seeks necessary (on average) to retrieve them. Out of reading convenience, though, I'd put them at the beginning. I'm a firm believer in putting the most important stuff first just as a way of self-documenting my database tables. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 195,455,826 queries (350/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subselects in 4.1 and indexes
On Mon, Jun 09, 2003 at 12:46:32PM -0500, Christopher Knight wrote: Im have a problem / misunderstanding with the 4.1 release. Im having issues with indexes related to 'IN' vs '=' in subselects. assume the subselect returns 1 value select SQL_NO_CACHE user_id from table_a where user_id in (select SQL_NO_CACHE user_id from table_b where ... blah) 9.5 seconds... select SQL_NO_CACHE user_id from table_a where user_id = (select SQL_NO_CACHE user_id from table_b where ... blah) 0.0 seconds explain select SQL_NO_CACHE user_id from table_a where user_id in (select SQL_NO_CACHE user_id from table_b where ... blah) ... not using index... explain select SQL_NO_CACHE user_id from table_a where user_id = (select SQL_NO_CACHE user_id from table_b where ... blah) .. using index on user_id and the subselect take 0.0 seconds to run as well.. Does just using the IN operator prohibit table_a from using the user_id index? It's a known bug that I ran into a while back too. The IN condition wasn't optimized in the 4.1.0 release. I think it's already fixed in the BK tree but havne't checked yet. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 195,467,760 queries (350/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array/pointer question
Some intros on the web are listed at http://www.artfulsoftware.com/dbresources.html - Original Message - From: John Griffin To: Ioana ; [EMAIL PROTECTED] Sent: Monday, June 09, 2003 10:38 AM Subject: RE: array/pointer question Hi, MySQL, like other relational databases, does not support arrays or pointers. Arrays and pointers do not fit within the relational model. It would take quite a bit of text to describe what the relational model is and how to use SQL to manipulate data. I suggest that you read a good text on relational databases and the relational model first suggested by Dr. Codd and then come back and ask questions. Anyone on this list would be happy to answer them. Anybody got any good titles or URLs for Ioana? John Griffin -Original Message- From: Ioana [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 10:50 AM To: [EMAIL PROTECTED] Subject: array/pointer question Hello, I have just recently started learning mysql, and was wondering if it supports arrays/pointers(for linked lists) of any types, and if so, what is the syntax... could not find info on this in any manual. 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]
Re: array/pointer question
Peter Brawley wrote: MySQL, like other relational databases, does not support arrays or pointers. Of course, relational databases don't allow duplicate rows in table, either... g I have yet to find a relational database with that feature! Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array/pointer question
Peter Brawley wrote: MySQL, like other relational databases, does not support arrays or pointers. I didn't write that. PB
fastest way to get last row
Hi Does anyone have tips on the fastest way to get the last row in a table. What I have is an int as the the primary key, using auto inc. I currently do a select max(id), then run another query retrieving a row based on the the max(id). Its two queries. Would I be able to get it all into one? Not using mysql 4... Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Bruce Feist [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:13 PM Subject: Re: array/pointer question Peter Brawley wrote: MySQL, like other relational databases, does not support arrays or pointers. I didn't write that. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fastest way to get last row
SELECT * FROM table ORDER BY id DESC LIMIT 1; Regards, Miek Hillyer www.vbmysql.com -Original Message- From: Chris Edwards [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 12:23 PM To: MySQL List Subject: fastest way to get last row Hi Does anyone have tips on the fastest way to get the last row in a table. What I have is an int as the the primary key, using auto inc. I currently do a select max(id), then run another query retrieving a row based on the the max(id). Its two queries. Would I be able to get it all into one? Not using mysql 4... Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Bruce Feist [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:13 PM Subject: Re: array/pointer question Peter Brawley wrote: MySQL, like other relational databases, does not support arrays or pointers. I didn't write that. PB -- 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: fastest way to get last row
On Mon, Jun 09, 2003 at 02:22:56PM -0400, Chris Edwards wrote: Hi Does anyone have tips on the fastest way to get the last row in a table. What I have is an int as the the primary key, using auto inc. I currently do a select max(id), then run another query retrieving a row based on the the max(id). Its two queries. Would I be able to get it all into one? Not using mysql 4... SELECT * FROM mytable ORDER BY id DESC LIMIT 1 Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 195,670,361 queries (349/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi CPU on FreeBSD
We had the same problem. Upgrading Freebsd to 4.8 and recompiling MySQL to work with Linux style threads seemed to work. The MySQLd process no longer pops up to ~90% on each request. However, with the application we're running that queries the database (Request Tracker 3), we still get process bloat with certain queries (searches mostly). But, this problem doesn't come up with any other application, or with manual querys of the database, so we think it's a bug in the app. Lalo Gunnar Helliesen wrote: On Sat, 22 Jun 2002 17:43:12 -0700 Jeremy Zawodny wrote: On Sat, Jun 22, 2002 at 07:26:36PM -0500, mos wrote: We've managed to reproduce this pretty reliably at Yahoo and are working to track and fix the bug. If we find a resolution, I'll post a note here. There have been a few threads regarding high CPU utilization on FreeBSD systems. Yeah, I mostly ingored them because I thought it was a fluke. But it happens much more than I had thought. It's still happening on the site I first reported it on. We're currently running max-4.0.11-gamma, but the problem has been present since 3.x. FreeBSD 4.4 and 4.5. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array/pointer question
Peter Brawley wrote: Peter Brawley wrote: MySQL, like other relational databases, does not support arrays or pointers. I didn't write that. My apologies -- it was written by John Griffin, not Peter Brawley. I edited carelessly. Oops. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest way to get last row
Couldn't something like this work? SELECT id,foo,bar FROM tbl ORDER BY id DESC LIMIT 1 Thanks, Ryan Yagatich ,_, \ Ryan Yagatich [EMAIL PROTECTED] \ / Pantek Incorporated (877) LINUX-FIX / \ http://www.pantek.com/security(440) 519-1802 \ / Are your networks secure? Are you certain? / \___37AD0283C6E7B51D5C0610E5C5F103776D2C520B0B0B9E58___\ On Mon, 9 Jun 2003, Chris Edwards wrote: Hi Does anyone have tips on the fastest way to get the last row in a table. What I have is an int as the the primary key, using auto inc. I currently do a select max(id), then run another query retrieving a row based on the the max(id). Its two queries. Would I be able to get it all into one? Not using mysql 4... Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
diff between 7.2 and 8.0 rights?
Hi I have 2 servers both with 3.23.56-1 installed via rpm's proteus2 which is running RedHat 7.2 on 1 cpu and has been in use for over 1 year works as expected. [EMAIL PROTECTED] [/var/lib/mysql]# mysqladmin status Uptime: 515357 Threads: 1 Questions: 2412696 Slow queries: 782 Opens: 19654 Flush tables: 1 Open tables: 64 Queries per second avg: 4.682 [EMAIL PROTECTED] [/var/lib/mysql]# however proteus2a 1week old and running RedHat 8.0 (dual cpu) [EMAIL PROTECTED] mysql]# mysqladmin status mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' [EMAIL PROTECTED] mysql]# I have read documentation and tried various but cannot seem to resolve the problem. Any ideas? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
**field type for large amount of text***
What is the best field type to use for a field that will be used to display a large amount of text (and possibly numbers) IE a field that will display the main text for a web page. Thanks -Dustin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **field type for large amount of text***
Well, that would be TEXT, MEDIUMTEXT, or LARGETEXT, they hold 65535, 16777215, and 4294967295 characters accordingly. I personally would stick to TEXT, maybe MEDIUMTEXT. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: DuSTiN KRySaK [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 12:38 PM To: [EMAIL PROTECTED] Subject: **field type for large amount of text*** What is the best field type to use for a field that will be used to display a large amount of text (and possibly numbers) IE a field that will display the main text for a web page. Thanks -Dustin -- 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]
Duplicates, but different
Good Afternoon! I have a table like this; id (unique, auto-increment) foo char(6) bar char(10) foo may be duplicated from row to row, bar may be duplicated from row to row i.e. foo bar 949433 IRVINE 949514 NEWPORTBCH 949514 NEWPORTBCH 949533 IRVINE 949533 IRVINE 949633 SADLBK VLY It is OK for 'bar' to have multiple 'foo' that do not match each other. What I want to query for is 'foo' that match each other but have multiple 'bar'. I would like to do this with having to specifiy each 'foo' in turn. Can it be done in one query? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest way to get last row
Hi Thanks! Looks like everyone knew the exact answer. I guess I posted too soon, as I figured it out also. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com - Original Message - From: Ryan Yagatich [EMAIL PROTECTED] To: Chris Edwards [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:37 PM Subject: Re: fastest way to get last row Couldn't something like this work? SELECT id,foo,bar FROM tbl ORDER BY id DESC LIMIT 1 Thanks, Ryan Yagatich ,_, \ Ryan Yagatich [EMAIL PROTECTED] \ / Pantek Incorporated (877) LINUX-FIX / \ http://www.pantek.com/security(440) 519-1802 \ / Are your networks secure? Are you certain? / \___37AD0283C6E7B51D5C0610E5C5F103776D2C520B0B0B9E58___\ On Mon, 9 Jun 2003, Chris Edwards wrote: Hi Does anyone have tips on the fastest way to get the last row in a table. What I have is an int as the the primary key, using auto inc. I currently do a select max(id), then run another query retrieving a row based on the the max(id). Its two queries. Would I be able to get it all into one? Not using mysql 4... Thanks. -- 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]
money field
Hi, Which type do you usually use to keep money values? Decimal (12,2) or Float? to keep the first one, system takes 12 bytes but for the second one, 8 bytes. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi CPU on FreeBSD
On Mon, Jun 09, 2003 at 11:26:36AM -0700, Lalo Castro wrote: We had the same problem. Upgrading Freebsd to 4.8 and recompiling MySQL to work with Linux style threads seemed to work. Great. The MySQLd process no longer pops up to ~90% on each request. However, with the application we're running that queries the database (Request Tracker 3), we still get process bloat with certain queries (searches mostly). But, this problem doesn't come up with any other application, or with manual querys of the database, so we think it's a bug in the app. Is this process bloat on the MySQL site or on the RT/Perl side? If it's on the MySQL side, can you describe it a bit and perhaps post your my.cnf file along with a sample query and table structure that tends to trigger the bloat? I suspect that if it's a design deficiency in RT, Jesse and the folks at Best Practical would like to fix it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 195,846,290 queries (348/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Turning off column and value reconciliation
I'm getting the error message:- http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. Is there a way to achieve this? thanks, Campbell --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining unrelated data
I have three tables, to model a purchasing system: transaction number client date purchased_product transaction product price payed_amount transaction date amount This model allows me to have a transaction with multiple products and also record several payments on the trasaction, so a client can pay just a fraction of the pruchased total and come later and pay the rest. However creating a debt report in a single query is somehow dificult. I know how to do it with views in Oracle, but mySQL seems lack from them. If I relate the three tables in one query, some quantities appear multiple times. Suppose we have one transaction with two products and one payment. transaction number client date 1 Joe Doe June 10th purchased_product transaction product price 1 glass5 1 spoon3 payed_amount transaction date amount 1 June 10th 8 And the query in the regular way to do it is: Select * From transaction, purchased_product, payed_amount Where transaction.number = purchased_product.transaction And transaction.number = payec_amout.transaction Of course this gives me the payed amount twice. What I need to do at the end is a report with all the clients that own to the store. Any ideas? Ernesto
RE: Turning off column and value reconciliation
Why not just specify what columns you are inserting into? This check has to be in place, because in the case of a mismatch MySQL would not know which value goes into which column. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Campbell D. McCausland [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 1:13 PM To: [EMAIL PROTECTED] Subject: Turning off column and value reconciliation I'm getting the error message:- http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. Is there a way to achieve this? thanks, Campbell --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- 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: Turning off column and value reconciliation
On 9 Jun 2003 at 12:13, Campbell D. McCausland wrote: http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. It's hard to know since you don't show us any of your code, but it looks like you should add the list of column names to your INSERT or LOAD statement rather than letting the list default to all the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: money field
I would use Decimal (12,2). A Float may be smaller, but only because it rounds off the last several digits of large values. A single-precision float uses 23 bits for storing digits, meaning that for values over 8,388,607 you are loosing pennies. When storing a value around 100 Million you would loose about 12 cents. Not big losses, but when dealing with financial accounting it is generally an unacceptable inaccuracy. Grant Q -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 11:56 AM To: [EMAIL PROTECTED] Subject: money field Hi, Which type do you usually use to keep money values? Decimal (12,2) or Float? to keep the first one, system takes 12 bytes but for the second one, 8 bytes. Thanks -- 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: money field
A Float may be smaller, but only because it rounds off the last several digits of large values. ... Not big losses, but when dealing with financial accounting it is generally an unacceptable inaccuracy. More to the point, if the DB rounds oddly, your attempts to access the data might fail. While you might enter a value as $12.99, the DB might store it as 12.99001 or 12.8, potentially rendering a SELECT on that field usesless. DECIMAL (or NUMERIC) values are stored as strings and processed as numbers when they are accessed. This ensures that the value entered is the value stored. The 4-byte storage overhead is easily justified by this. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Quinlan, Grant [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, 09 June, 2003 15:16 Subject: RE: money field I would use Decimal (12,2). A Float may be smaller, but only because it rounds off the last several digits of large values. A single-precision float uses 23 bits for storing digits, meaning that for values over 8,388,607 you are loosing pennies. When storing a value around 100 Million you would loose about 12 cents. Not big losses, but when dealing with financial accounting it is generally an unacceptable inaccuracy. Grant Q -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 11:56 AM To: [EMAIL PROTECTED] Subject: money field Hi, Which type do you usually use to keep money values? Decimal (12,2) or Float? to keep the first one, system takes 12 bytes but for the second one, 8 bytes. Thanks -- 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: money field
I use Decimal, because I like my money calculations to be correct. Mojtaba Faridzad wrote: Hi, Which type do you usually use to keep money values? Decimal (12,2) or Float? to keep the first one, system takes 12 bytes but for the second one, 8 bytes. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL and Windows
Does anyone know how to turn the SSL on for mySql for Windows?? I need to encrypt and decrypt certain items in a database and the manual says add the OpenSSL package but there isn't one for Windows. Help! Thanks in advance!! Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net
Re: ADD CONSTRAINT on InnoDB tables
Marco, - Original Message - From: Marco B [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, June 09, 2003 8:45 PM Subject: ADD CONSTRAINT on InnoDB tables Hi all,=0D=0A=0D=0AI've just installed mysql 4.0.1 on my pc and I'm tryin= g to migrate a set of old MyISAM table to InnoDB type table, to introduce= referential integrity on my data.=0D=0A=0D=0AI found this problem. I cre= ... when I press 'Reply', your message gets scrambled with 0D and 0A codes, which are the carriage return and linefeed in Windows, I think :). Anyway, the answer to the question is that RESTRICT is the default if you do not specify an 'action'. That is why SHOW CREATE TABLE does not explicitly print the RESTRICT. =0AMarco Barbato=0D=0A=0D=0A-= Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SSL and Windows
Are you looking to encrypt fields or the entire session between client and server? SSL is used for the latter, for the former you could look at the ENCODE and DECODE functions. (See http://www.mysql.com/doc/en/Miscellaneous_functions.html) I can use them without openssl being installed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Sparky Kopetzky [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 1:43 PM To: My Sql List Subject: SSL and Windows Does anyone know how to turn the SSL on for mySql for Windows?? I need to encrypt and decrypt certain items in a database and the manual says add the OpenSSL package but there isn't one for Windows. Help! Thanks in advance!! Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL and Windows
http://www.google.com/search?q=openssl+windowsie=UTF-8oe=UTF-8 On Monday, June 9, 2003, at 01:43 PM, Sparky Kopetzky wrote: Does anyone know how to turn the SSL on for mySql for Windows?? I need to encrypt and decrypt certain items in a database and the manual says add the OpenSSL package but there isn't one for Windows. Help! Thanks in advance!! Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning off column and value reconciliation
Mike, Keith, Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Please confirm that there is no way to turn this check off. That way, I'll be able to stop looking for one. 8o) best regards, Campbell At 03:14 PM 6/9/2003 -0400, Keith C. Ivey wrote: On 9 Jun 2003 at 12:13, Campbell D. McCausland wrote: http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. It's hard to know since you don't show us any of your code, but it looks like you should add the list of column names to your INSERT or LOAD statement rather than letting the list default to all the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning off column and value reconciliation
There is no way to turn it off (well, no practical way, with open source nothing is impossible). Can you add dummy columns to the mysql table to the insert statements match up? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Campbell D. McCausland [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 PM To: [EMAIL PROTECTED] Subject: Re: Turning off column and value reconciliation Mike, Keith, Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Please confirm that there is no way to turn this check off. That way, I'll be able to stop looking for one. 8o) best regards, Campbell At 03:14 PM 6/9/2003 -0400, Keith C. Ivey wrote: On 9 Jun 2003 at 12:13, Campbell D. McCausland wrote: http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. It's hard to know since you don't show us any of your code, but it looks like you should add the list of column names to your INSERT or LOAD statement rather than letting the list default to all the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- 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: Turning off column and value reconciliation
On 9 Jun 2003 at 13:16, Campbell D. McCausland wrote: Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Another way would be to make a table with the old structure (without the new columns) and import into it, then do a SELECT ... INSERT from it into the real table, specifying the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning off column and value reconciliation
Thanks for the suggestion Mike, but I have more columns than data values already 8o) At 02:17 PM 6/9/2003 -0600, Mike Hillyer wrote: There is no way to turn it off (well, no practical way, with open source nothing is impossible). Can you add dummy columns to the mysql table to the insert statements match up? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Campbell D. McCausland [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 PM To: [EMAIL PROTECTED] Subject: Re: Turning off column and value reconciliation Mike, Keith, Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Please confirm that there is no way to turn this check off. That way, I'll be able to stop looking for one. 8o) best regards, Campbell At 03:14 PM 6/9/2003 -0400, Keith C. Ivey wrote: On 9 Jun 2003 at 12:13, Campbell D. McCausland wrote: http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. It's hard to know since you don't show us any of your code, but it looks like you should add the list of column names to your INSERT or LOAD statement rather than letting the list default to all the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning off column and value reconciliation
Thanks for the suggestion, but its a tossup between that and the perl 8o( I'll need to give it some thought about how much I want to port. MySQL is sure easier to install and also faster (I'm told anyway, without getting my data in I can't be sure). best, Campbell At 04:22 PM 6/9/2003 -0400, Keith C. Ivey wrote: On 9 Jun 2003 at 13:16, Campbell D. McCausland wrote: Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Another way would be to make a table with the old structure (without the new columns) and import into it, then do a SELECT ... INSERT from it into the real table, specifying the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi CPU on FreeBSD
the load average in my freebsd 5.0 (latest releng) compiled statically with linuxthreads decreased 80% after I tunned these variables: set-variable = key_buffer_size=100MB set-variable = read_buffer_size=5M set-variable = table_cache=500 set-variable = max_delayed_threads=0 set-variable = max_user_connections=25 set-variable = query_cache_size=50M set-variable = thread_cache_size=100 The only problem I couldn't solve is that some connections will never die... since most of this connections come from tomcat, I'm restarting it more often. Using query cache and thread cache will definetely give you a boost of performance This machine has lots of memory, so you have to be careful with variables like key_buffer_size, table_cache and query_cache_size... you need memory for that, don't let it swap. Lalo Castro wrote: We had the same problem. Upgrading Freebsd to 4.8 and recompiling MySQL to work with Linux style threads seemed to work. The MySQLd process no longer pops up to ~90% on each request. However, with the application we're running that queries the database (Request Tracker 3), we still get process bloat with certain queries (searches mostly). But, this problem doesn't come up with any other application, or with manual querys of the database, so we think it's a bug in the app. Lalo Gunnar Helliesen wrote: On Sat, 22 Jun 2002 17:43:12 -0700 Jeremy Zawodny wrote: On Sat, Jun 22, 2002 at 07:26:36PM -0500, mos wrote: We've managed to reproduce this pretty reliably at Yahoo and are working to track and fix the bug. If we find a resolution, I'll post a note here. There have been a few threads regarding high CPU utilization on FreeBSD systems. Yeah, I mostly ingored them because I thought it was a fluke. But it happens much more than I had thought. It's still happening on the site I first reported it on. We're currently running max-4.0.11-gamma, but the problem has been present since 3.x. FreeBSD 4.4 and 4.5. -- -- Gustavo Baratto - Programming and Technical Support [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi CPU on FreeBSD
On Mon, Jun 09, 2003 at 01:41:00PM +, Gustavo A. Baratto wrote: the load average in my freebsd 5.0 (latest releng) compiled statically with linuxthreads decreased 80% after I tunned these variables: What about actual CPU usage? Did is increase similarly? The load average isn't necessarily a good measure of performance. It's often a good measure of bottlenecks beyond the scheduler's control--suck as poor I/O. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 198,532,567 queries (349/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter table blocks other tables!
I have a mysql server containing multiple databases, one of the databases is very important that it is not slowed down by other databases. This database is used for a real time game server (card games), and any stalls causes all players to stall, they hate that. I've mostly solved this problem with a master/slave setup. The problem now is the other databases in the same mysql instance are getting larg. When I make changes to them with alter table, it can slow down and even block queries to the other databases for minutes at a time. My question is how do I prevent alter table in one database from blocking queries in another database? What I need is some way to say that any queries to this database (or specific tables) have top priority, always. Thanks for any assistance! Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi CPU on FreeBSD
CPU usage improved a lot as well... It's always above 90% free and before it was 70-75% idle... here is a snapshot: last pid: 58730; load averages: 0.09, 0.11, 0.08 up 9+20:59:57 17:11:24 216 processes: 1 running, 215 sleeping CPU states: 0.0% user, 1.8% nice, 1.2% system, 0.2% interrupt, 96.9% idle Jeremy Zawodny wrote: On Mon, Jun 09, 2003 at 01:41:00PM +, Gustavo A. Baratto wrote: the load average in my freebsd 5.0 (latest releng) compiled statically with linuxthreads decreased 80% after I tunned these variables: What about actual CPU usage? Did is increase similarly? The load average isn't necessarily a good measure of performance. It's often a good measure of bottlenecks beyond the scheduler's control--suck as poor I/O. Jeremy -- -- Gustavo Baratto - Programming and Technical Support [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table blocks other tables!
On Mon, Jun 09, 2003 at 01:05:43PM -0700, Dan Edwards wrote: I have a mysql server containing multiple databases, one of the databases is very important that it is not slowed down by other databases. This database is used for a real time game server (card games), and any stalls causes all players to stall, they hate that. I've mostly solved this problem with a master/slave setup. Hmm. Stalls are expected if the system gets CPU or disk I/O bottlenecked during the ALTER TABLE operation. The problem now is the other databases in the same mysql instance are getting larg. When I make changes to them with alter table, it can slow down and even block queries to the other databases for minutes at a time. My question is how do I prevent alter table in one database from blocking queries in another database? What I need is some way to say that any queries to this database (or specific tables) have top priority, always. It shouldn't block entirely. Can you snapshot the output for SHOW PROCESSLIST when this happens? Or maybe watch interactively with mytop? Also, which MySQL and operating system are you using? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 198,848,091 queries (347/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table blocks other tables!
You say you are using replication. In this situation, if you make an update to the master (using ALTER...) that takes a long time, this will get serialized into the binary log as normal and block all subsequent queries from executing on the slave until it has completed. One of the issues with MySQL replication is that it serializes the queries from the master into a single stream. I've done a rather poor job of explaining this. It's easiest understood when you think about how replication works: the master serializes commands out to a log file, which is then read, command by command, by the slave (on 4.0 servers by an IO thread, on 3.23 servers by a combined IO/SQL thread). The slave then executes each command as it comes in, command by command. If the next command takes a long time, it will be a long time before the following command can be executed. Imagine you have two commands in the queue that take the following times: 1) ALTER some big table : 303 secs 2) UPDATE some table SET some important thing : 1 sec The ALTER will begin executing on the slave and will not complete for 303 seconds. For this time, no more commands will be read from the slave and executed: it will wait until the ALTER is complete, and then carry on again. This is necessary to maintain consistency (imagine the UPDATE modified the same table that was just ALTER'd). Anyway, that may explain your problem. Or it may be way off base. :) Hope it helps a little, Chris Dan Edwards wrote: I have a mysql server containing multiple databases, one of the databases is very important that it is not slowed down by other databases. This database is used for a real time game server (card games), and any stalls causes all players to stall, they hate that. I've mostly solved this problem with a master/slave setup. The problem now is the other databases in the same mysql instance are getting larg. When I make changes to them with alter table, it can slow down and even block queries to the other databases for minutes at a time. My question is how do I prevent alter table in one database from blocking queries in another database? What I need is some way to say that any queries to this database (or specific tables) have top priority, always. Thanks for any assistance! Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB Transactions
Hi : I would like to know what happen if I start a transaction using BEGIN command I do some insert or update statements, and just before executing a ROLLBACK or COMMIT command I get disconnected and I can't execute those commands. For how long does the table remain locked, or in other words how unlock that table with another client connection?. Best regards _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default Datetime?
Hello I have a table for messages, and when a new message is inserted I want the current date and time to be inserted into the MessageDated and MessageTimed fields. I'm using MySQL 3.23.56 (?), with Chilisoft ASP, and using the MySQL Control Center interface. The problem is that no matter what I try, I seem to end up with 00 in the fields instead of the current date and time. For example, I have two hidden fields in my insert form, called MessageDated and MessageTimed, each set to % = now() %. I have the fields in the database set to DATETIME and also with default value 'now()'. But this doesn't seem to work. How can I get the default value to be now()? The only way I've succeeded is by setting the type of field to VARCHAR, but that's not ideal. Thanks in advance for any help you can give me! Best wishes Trevor
Re: Default Datetime?
On Mon, 2003-06-09 at 16:23, Trevor Sather wrote: How can I get the default value to be now()? The only way I've succeeded is by setting the type of field to VARCHAR, but that's not ideal. Trevor, take a look at the TIMESTAMP type: http://www.mysql.com/doc/en/DATETIME.html or, if you would rather not use that, then make sure you are formatting your now() in such a way that MySQL will use it. MySQL expects DATETIME fields to be : -MM-DD HH:MM:SS and if they are not, you will get the 00 you have been seeing. Basically, you have to format the date within your asp before you send it to mysql. hth, gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrangement of fields in a table
Hi Mohitaba, Sounds to me that it makes sense to put the important 8 fields at least in a separate table to speed up any SELECT requests. You may even consider putting a copy of the small table containing only the 8 fields into the memory as a heap table ( if they are not containing any blobs or text variables) if your access trend is going to be read only mostly. Generally speaking a table with 100 fields I guess would slow down operation immensly if you put statements like SELECT * FROM tablename; I would consider to spilt the data (if possible ) over several tables. Best regards Nils Valentin Tokyo/Japan 2003 6 10 02:40Mojtaba Faridzad : Hi, I need to define for example 100 fields in a table. 8 of them are more important and many queries just need these fields. Should I define them before the other fields in the table or it doesn't matter? Is accessing the the beginning fields faster than the other fields? thanks -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Installation Question
When executing from the COMMAND PROMPT, the following is returned:C:\cd mysql C:\mysqlcd bin C:\mysql\binmysqlshow -u Administrator --password=1234567890 mysqlDatabase: mysql+--+| Tables |+--+| columns_priv || db || func || host || tables_priv || user |+--+ C:\mysql\bin When using the MYSQL CONTROL CENTER, the following is returned. [Default] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I've trying reading the manual, and I don't have the privileges to change the System or User Administration Tables. Does anyone have a fix in the WIN 2000 Server environment? David M Friscia friscia.rootsweb.com [EMAIL PROTECTED]
Insert Image content via command line
Hi, I am trying to insert an image directly from command line into mysql table. My table looks like == Create table ImageTab (id int, content longblob); === I login to localhost. I have an image at /tmp. Then i issue this query insert into ImageTab values(1, load_file(/tmp/myimg.gif)); == No error. But when i check the content of the table, the content column is null. I login as root to do this. What have i done wrong? Please help. Thanks. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table blocks other tables!
Can you grab the output of 'vmstat' or iostat or something that'll measure disk I/O next time this happens? I'm going to try real hard not to do that again until it is upgraded or another solution is found. What'd help more is adding more disks not a single faster one--unless the SCSI disk is an order of magnitude faster. What is usually the better way to take advantage of multiple disks, raid stripping or seperating the dbs to different disks? I've been playing with scsi raid on the slave machine. It didn't help other performance problems as much as I was hoping it would. The issue there is takeing these large tables and generating reports. I have one report that takes about 6 hours. Also while I'm asking, the scsi setup I have on the slave is a p4 2.4, lsi160 card with 4 fujitsu Ultra160 10,000 rpm 9 gig drives, useing linux software raid. Definatly the cheapo route. My question is, how does this setup with the $45 lsi card compare to say a $300+ adaptec card with hardware raid. Especially considering that the cpu is so underutilized? Is there any good sites that compare mysql performance on different hardware? All I could find is on mysql.com with 4 year old hardware. Not off the top of my head. You could do the ALTER TABLE on another machine (a slave) and the copy it to the master. But that has other problems. That isn't too bad of an idea I'll have to think about that some more. Thanks for the help, I've been useing mysql for 4-5 years now without asking a single question, I'm glad that there is help now that I finally have. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default Datetime?
You can't use a default value of NOW(), to the best of my knowledge. However, if your INSERT statement is properly formatted, you should have no problem getting it to do as you please. Personally, rather than having two hidden form fields, I'd just setup my INSERT statement as follows (using PHP for the example): $query = INSERT INTO table (field, messageDated) VALUES( \.$value1.\, NOW() ); Being that the messageDated and messageTimed (not shown in example) fields aren't user variables, there's no problem with hard-coding them into the insert statement. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Trevor Sather [EMAIL PROTECTED] To: [EMAIL PROTECTED] Mysql. Com [EMAIL PROTECTED] Sent: Monday, 09 June, 2003 19:23 Subject: Default Datetime? Hello I have a table for messages, and when a new message is inserted I want the current date and time to be inserted into the MessageDated and MessageTimed fields. I'm using MySQL 3.23.56 (?), with Chilisoft ASP, and using the MySQL Control Center interface. The problem is that no matter what I try, I seem to end up with 00 in the fields instead of the current date and time. For example, I have two hidden fields in my insert form, called MessageDated and MessageTimed, each set to % = now() %. I have the fields in the database set to DATETIME and also with default value 'now()'. But this doesn't seem to work. How can I get the default value to be now()? The only way I've succeeded is by setting the type of field to VARCHAR, but that's not ideal. Thanks in advance for any help you can give me! Best wishes Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
