Install of mysql 4.1.x on RH 9

Dear all, I am positive this has been asked a 1000 times before, but i cannot find it anywhere on the archive. Maybe a good search function there would help :) Ok, here my problem: I am trying to install mysql 4.1.1 on RH 9.0, but get the following errror: [EMAIL PROTECTED] mysql]# rpm -Uvh

DATE / DATETIME in IF(): BUG???

Dear all,   anyone knows why the following is going wrong (I expect 1 in the columns Total and Open)? I suppose it is because MySQL is internally casting the IF expression (which is DATE vs DATE or DATE vs DATETIME) internally into a STRING before comparison, but not sure about that   (String)

RE: Multiple Join Issue

If you want to restrict the tuples you get from 'broadcasts' use AND instead of WHERE. Cheers /rudy -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: dinsdag 7 oktober 2003 15:42 To: Wayne Helman Cc: [EMAIL PROTECTED] Subject: Re: Multiple Join Issue Leave off the

RE: printing reports

You can use the mysql command client, it that is what you mean. You can also pass the query to the command client and then spool the output. mysql the query report file (or something like that, just check the manual) to do it really without any other tools, third party or not, you can always

RE: Problem reading my.cnf

Try to quote the password: password= 'test#istest$' or password= test#istest$ Not sure if MySQL is doing command expansion. If yous, use the first example, if not, it should not matter. Cheers /rudy -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: maandag

RE: replication question

Replication just executes the commands in the binary log (i.e INSERT, UPDATE, DELETE, ...). So if you make sure that you do not issue a DDL for creation/altering in the master DB, everything should work fine. However, I would advice against it (it is not a supported feature, but a trick) Cheers

RE: Convert date from .txt file

Or load the date into a column date_str and then convert and copy the date into a column date AFTER loading. Cheers /rudy ps: I already posted a function of how to convert this date format into a MySQL date format somewhere on this list (prev month I think) -Original Message- From: Jay

RE: Security related! Not possible to hide table structure. I couldn't find..... ?

If you give access rights to a user on a DB, he will always be able to see the table structure. This is how it is implemented in MySQL (which does not mean that I like this). Cheers /rudy -Original Message- From: QWERTY [mailto:[EMAIL PROTECTED] Sent: maandag 4 augustus 2003

RE: Help!!!

CREATE TABLE NAME ( SELECT name, work FROM A UNION ALL SELECT name, home FROM A WHERE home IS NOT NULL ); CREATE TABLE ADDRESS ( SELECT work FROM A UNION ALL SELECT home FROM A WHERE home IS NOT NULL ); Cheers /rudy -Original Message- From: Colt

RE: Select via Perl

Can you please post the source code? In any case, I would consider using DBI. Cheers /rudy -Original Message- From: Ashwin Kutty [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 17:12 To: [EMAIL PROTECTED] Subject: Select via Perl I am trying to read a file and see if the

RE: join query result difference between 3.23.49 and 4.0.13

Better ifnull(sum(amount_paid),0) Because if you add up a column which contains NULL and NOT NULL values, all NULL values are SKIPPED for the calculation. This implies that if the column ONLY contains NULL values, the result is NULL. Would be great if MySQL could post a chart somewhere of how

RE: Query debugging

Sorry, MySQL does not like the ( ). Try it without them :) Cheers /rudy -Original Message- From: Colt Brunton [mailto:[EMAIL PROTECTED] Sent: vrijdag 18 juli 2003 15:38 To: [EMAIL PROTECTED] Subject: Query debugging Hi all I am trying to adapt a query, (kindly) given by Rudy and I

RE: Best practice column type for storing decimal currency amounts?

-Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 22:03 To: Rudy Metzger; [EMAIL PROTECTED] Subject: Re: Best practice column type for storing decimal currency amounts? Thanks for the reply, Rudy. My source for the statement that decimal values

RE: mysql setup compared to oracle

1. No, especially not MyISAM. In MyISAM, a database (you can compare that to instance) is just a directory on disk. Every table in this DB (instance) again is file (well actually 3 files, one for data, one for metadata, one for index information). InnoDB looks a bit similar, as it also uses the

RE: mysql connection error

Also make sure that the port is not firewalled. Cheers /rudy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 9:56 To: zafar rizvi Cc: [EMAIL PROTECTED] Subject: Re: mysql connection error Keep in mind you need 2 things to happen: A)

RE: join query result difference between 3.23.49 and 4.0.13

I also fell into this NULL trap. Make sure to read the change log concerning NULL values! They are still buggy (imho) and with every change I have the feeling that new bugs/features are introduced. Example: Select sum(1) from foo where 1 = 3; This statement actually returns a row with NULL.

RE: mysql setup compared to oracle

Hmmm... right. You can compare the server to the instance. And the user to the DB (in the scheme, not at authentication) Thanx for the pointer! /rudy -Original Message- From: Jim Smith [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 11:06 To: Rudy Metzger; 'Harald Falkenberg

RE: Managing big tables

Maybe you could get some speed increase for you queries by setting the record_buffer to a higher value. Because with fixed row length this buffer fills up faster too. Although I doubt that you will gain a lot... /rudy -Original Message- From: Alexander Schulz [mailto:[EMAIL PROTECTED]

RE: MySQLDump

It does not fail if some tables are not locked. But like you said, you could miss data. So the better way is first to lock, then flush and then dump the table. It does not backup the transaction log (if you mean the binary log with transaction log). Even worse, you normally have no way of telling

RE: One Big Data Base or Many Smaller Ones

Really depends on what exactly you want to achieve. But as I read the mail below, create a lot of smaller ones. If the ISP however charges you for each DB it is also easy to only create one huge DB, just do not make the table names too long. Developers are lazy in typing... :) /rudy

RE: Best practice column type for storing decimal currency amounts?

Problem is that DECIMALs are currently stored as FLOAT or DOUBLE in the DB (at least MyISAM). MySQL AB is busy with adding a true monetary type (like MONEY) to the system. What I am doing when using monetary values is putting them into floats if I can live with rounding problems or put them into

RE: Complex select statement

You are not allowed to use grouping functions in the WHERE clause. To restrict on grouped values, you have to put them into the HAVING clause. Cheers /rudy -Original Message- From: Roy Walker [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 23:25 To: Rudy Metzger; [EMAIL PROTECTED

RE: Single Record Locking - Permanent?

I would add a flag to the record (e.g. 'user_lock'). You then have to evaluate this flag in your application. Or you can wait until 5.0 for triggers where you then can do it in the DB (or maybe not, dunno how far triggers will go). Maybe MERGE tables could help you, not sure about this though.

RE: Foreign Keys

InnoDB is using a totally different concept than MyISAM. Where in MyISAM all the data is in one file, InnoDB uses the principle of Tablespaces (like ORACLE). This puts the whole tables (and metadata) into one HUGE file (in theory. In practice this file can be split and extends can be defined of

RE: Simple newbie question - CURTIME()

Please check the manual, search for DATE_ADD(). /rudy -Original Message- From: Johannes Pretorius [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 8:01 To: [EMAIL PROTECTED] Cc: Pieter Subject: Simple newbie question - CURTIME() Good day \-=0=-=00- I have looked in the manual and

RE: subselect question... shouldn't this work?

These are not real subselects. You speak of a subselect when you are define a select in the FROM clause of a kwiri. If you define them in the WHERE clause, you speak of derived tables. Putting them even into the SELECT clause is something specific to MySQL (as far as I know). Ok, now this is

RE: Can someone help me??

Did you do that on the mysql command line? Or did you use a different client and/or API? Cheers /rudy -Original Message- From: Sbandy [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 11:21 To: [EMAIL PROTECTED] Subject: Can someone help me?? I am new in mysql I wrote this

RE: Connection problem!!! Windows to Linux

If you manually updated the tables, use flush privileges to inform the DB server of your changes. Cheers /rudy -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 11:23 To: [EMAIL PROTECTED] Subject: Re: Connection problem!!! Windows to Linux Aric

RE: Simple newbie question - CURTIME()

WRONG! What will happen in this case? The DB is converting/casting the DATE (curtime()) into an integer and then ads 6. So what you get does not necessarily represent a valid date/time! Just replace 06 with 24 and see what you yet. Cheers /rudy -Original Message- From:

RE: Simple newbie question - CURTIME()

SELECT EXTRACT(HOUR_SECOND FROM 1999-07-02 01:02:03); -Original Message- From: Johannes Pretorius [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 13:32 To: [EMAIL PROTECTED] Cc: Pieter Subject: RE: Simple newbie question - CURTIME() Thanks \0-\-==-0-= I have seen my error and have

RE: date conversion

SELECT concat( substring_index(DATE,'-',-1), '-', lpad( field(substring_index(substring_index(DATE,'-',2),'-',-1), 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug', 'Sep','Oct','Nov','Dec'), 2,'0'),

RE: How to terminate dead connections that are in SLEEP?

Kill them :) http://www.mysql.com/doc/en/KILL.html Cheers /rudy -Original Message- From: PAUL MENARD [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 16:44 To: [EMAIL PROTECTED] Subject: How to terminate dead connections that are in SLEEP? Good morning all, I have a problem

RE: Rows into Columns

/rudy -Original Message- From: Shazia Fazili [mailto:[EMAIL PROTECTED] Sent: maandag 14 juli 2003 19:35 To: Rudy Metzger Subject: RE: Rows into Columns Hi Rudy, Thnaks for ur reply. Your solution is adding up all Payments, while I don't want all the Payments to be summed. I want

RE: !!! NEWBIW !!! how to start? !!! NEWBIE !!!

Best method to start, restart, the server is with the service command: service mysql start service mysql stop service mysql restart Of course, this assumes that you have it configured in the init.d, which is something mysql installation normally does itself (at least with the rpm). These

RE: Find entry with max date.

SELECT c.name, c.surname, substring( if ( max(concat(cont.date,'Conctact ',cont.date) max(concat(compl.date,'Complaint ',compl.date), if (max(concat(cont.date,'Conctact ',cont.date) max(concat(act.date,' Action

RE: auto number primary key - restarting

Please check the history on this list. There are numerous answers to this problem. Cheers /rudy -Original Message- From: Miroslav I. [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 14:10 To: [EMAIL PROTECTED] Subject: auto number primary key - restarting Hi, is there an SQL

RE: Managing big tables

I do not really think that optimizing (in your case compressing, thus cleaning up free space) is much faster with fixed record length on LARGE tables. Why? When optimizing the table the DB rebuilds the file record for record to a temporary file and then moves it back to the original file (well,

RE: Managing big tables

Always take care what you want to achieve! And consider the circumstances. Yes, adding a lot of indexes makes queries faster. But makes inserts/deletes/updates slower. Alex's problem is NOT that his/her queries takes too long, the problem is that optimize takes too long. Which is something

RE: auto number primary key - restarting

truncate table_name does both in one statement. And even optimizes the table (frees up unused disk space). However take care that you cannot rollback this DDL. Cheers /rudy -Original Message- From: Chris Boget [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:29 To:

RE: What about auto number primary key - wrapping?

Set the autoincrement column to the max value or the given data type (via alter table), insert a record and see what happens... Cheers /rudy -Original Message- From: TheMechE [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:40 To: [EMAIL PROTECTED] Subject: What about auto number

RE: Managing big tables

. -Original Message- From: Veysel Harun Sahin [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 16:24 To: Rudy Metzger Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Managing big tables Sorry rudy, but I can not understand what you try to say! I can only say

RE: Can mysql handle this load?

with DATE and TIMESTAMP values at the beginning of my 'mysql time', and i'm using INT unix timestamps since then... -yves -Ursprüngliche Nachricht- Von: Rudy Metzger [EMAIL PROTECTED] An: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Adam Gerson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED

RE: Complex select statement

I never heard before that you can use a select statement in an arithmetic expression. Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) should work. Anyway, it is considered a subselect and therefore does not work yet. However, in 4.1 you should also be able to

RE: My Stupidity (Various Questions)

2. 3MB is no problem at all (given that your hardware has free HD space and that you do not have a quota of say 4MB) 3. with the new version you can also change session parameters on the mysql command line. Use the keywords GLOBAL and LOCAL for global and/or local changes. But this implies that

RE: grouping / sorting etc..

SELECT col1, IF(count(*)!=count(col2),NULL,'BAD') FROM table1 GROUP BY col1; This ONLY works EXACLTY for the case you submitted. Please note that your example is wrong (I think). 3 should also return NULL, shouldn't it? /rudy -Original Message- From: Christopher Knight

RE: Improving insertion performance by locking tables

From what I know is, that MySQL always locks the MyISAM table before you insert, update or delete something from it. So the key here is not so much if you should lock the table, but how you insert the data (single inserts vs multi inserts). Multi inserts are the way to go. By locking the table you

RE: Rows into Columns

SELECT invoiceid, IF(count(*)=1,sum(payment),0) pay1, IF(count(*)=2,sum(payment),0) pay2, IF(count(*)=3,sum(payment),0) pay3, IF(count(*)=4,sum(payment),0) pay4, IF(count(*)=5,sum(payment),0) pay5, IF(count(*)=6,sum(payment),0) pay6 FROM payment GROUP

RE: columns to rows

IF the dates are limited and can be agreed upon before running the kwiri, you can use: SELECT no, IF (date=d1, data, NULL) d1, IF (date=d2, data, NULL) d2, IF (date=d3, data, NULL) d3 FROM table GROUP BY no; It will also NOT work if one date can contain multiple data,

RE: Improving insertion performance by locking tables

Message- From: Phil Bitis [mailto:[EMAIL PROTECTED] Sent: maandag 14 juli 2003 11:44 To: [EMAIL PROTECTED] Subject: Re: Improving insertion performance by locking tables Is there a limit to the number of records I can insert in a multiple-value insert? - Original Message - From: Rudy

RE: mysql naming scheme

The a, b, ... is applied to older versions which got a bugfix. E.g. if the current version would be 3.23.44 and a bug is found in 3.23.33 which is fixed, then 3.23.33 becomes 3.23.33a and so on. Please note that normally bugs are only fixed in the latest version, so you have to upgrade. However

RE: Pull updated Records without a date field?

You could add a column 'is_updated' and set it to 'Y' when it is updated. But depends very much on what you want to achieve by it /rudy -Original Message- From: Keith Hamilton [mailto:[EMAIL PROTECTED] Sent: donderdag 10 juli 2003 18:39 To: MySQL Subject: Pull updated Records without a

RE: Left Join - Revisted

I am not sure if I fully understand your problem, but I think you have to move the IS NULL to the LEFT JOIN condition. Cheers /rudy SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.blocked_id FROM useronline,

RE: 2 or more LEFT JOIN?

FROM table1 LEFT JOIN table2 ON table1.column = table2.column LEFT JOIN table3 ON table1.column = table3.column -Original Message- From: Krasimir_Slaveykov [mailto:[EMAIL PROTECTED] Sent: donderdag 10 juli 2003 16:07 To: [EMAIL PROTECTED] Subject: 2 or more LEFT JOIN? Hello mysql, If I

RE: How to connect o remote MySQL Database

http://www.mysql.com/products/myodbc/index.html -Original Message- From: Azrin Aris [mailto:[EMAIL PROTECTED] Sent: vrijdag 11 juli 2003 12:15 To: [EMAIL PROTECTED] Subject: How to connect o remote MySQL Database I have a MySQL Server in a Windows Box. How can I connect to the server

RE: Encrypt().

Encrypt() using a system call to encrypt the string. So if your system does not support crypt(), you are out of luck. Windows does not support crypt(). How to get around this? Well, the best way to solve it is to install linux on your PC. The easier one is to use another encrypting methods, such

RE: i nned ur help

Replace the commented lines with: Method 1 (with counter): @array_data = (); ... $array_data[$counter++] = @row; Method 2 (without counter): @array_data = (); # initialize it $array_data[$#array_data] = @row; # put the array into the array Method 3 (with

RE: JOIN within LEFT JOIN

SELECT ... FROM forms f LEFT JOIN staff_assignments a ON f.form_id = a.form_id LEFT JOIN staff s ON a.staff_id = s.staff_id; Given that left joins are faster in MySQL anyway, this also gives you speed. Cheers /rudy -Original Message- From: MightyData [mailto:[EMAIL PROTECTED]

RE: Why is it better to have a composite key with the largest cardinality 1st?

Do you mean that your table has a total of 1 rows? If yes, there is (generally) no need to add column b and/or c to the primary key, as this makes the information redundant (already A is unique). If you mean that you table has 4 rows (so 10K A x 2 B x 2 C) and only the combination of

RE: mysql startup script problem

Actually, mysql is only the client used to connect to mysqld. mysqld does NOT automatically start up when it crashes. But you can use mysqld_safe (or safe_mysqld which is a softlink to former). This script then takes care of logging and restarting the server. Init.d uses mysqld_safe. Cheers /rudy

RE: RESETTING AUTO_INCREMENT

Or drop and recreate the table (that's actually what truncate is doing) /rudy -Original Message- From: Miguel Perez [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 20:08 To: [EMAIL PROTECTED] Subject: RESETTING AUTO_INCREMENT Hi everyone: Does anyone know how to reset the

RE: order of table joins or where clauses relevant?

The order of tables in the from does NOT make any difference. The optimizer (normally) will rewrite the kwiri to suit its needs. Also the order in the WHERE does not make any difference. You should put all restrictions into the WHERE clause, which is MUCH faster than putting them into the HAVING.

RE: REGEX

Could you maybe send us your table definition, some example data and an expected output? /rudy -Original Message- From: Doug Wolfgram [mailto:[EMAIL PROTECTED] Sent: donderdag 10 juli 2003 4:53 To: [EMAIL PROTECTED] Subject: REGEX I am familiar with regex in general, but here is what

RE: Faster reindexing

You should also check myisam_max_extra_sort_file_size and myisam_max_sort_file_size. NOTE: I heard/know that these variables are dependant on each other, however I cannot remember anymore how. It was something that if a is not set, b is not considered. Also check out myisam_repair_threads,

RE: constructing SQL problem

SELECT ... FROM TABLE1 a, TABLE2 b, LEFT JOIN TABLE3 c ON a.field1 = c.field1 AND c.field3 != 'string' WHERE a.field1 = b.field1; If field3 in table c can be NULL and you still want to have this record included you have to modify it to ... AND

RE: Bulk loading data

Paul, Did you try using REPLACE instead of UPDATE/INSERT? Could give you some more speed enhancement. Cheers /rudy -Original Message- From: Paul Chvostek [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 15:21 To: cmars Cc: [EMAIL PROTECTED] Subject: Re: Bulk loading data On Tue,

RE: recursive sql statement

Everything from month to years. Might even be tomorrow... /rudy -Original Message- From: Bernhard Schmidt [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:07 To: [EMAIL PROTECTED] Subject: Re: recursive sql statement hi victoria sorry for this mysql newbie question, but what

RE: mysqld max

Please note that InnoDB is included as from MySQL 4.0 in the standard release. So the easiest way to use InnoDB would be to grab a RPM or binary distribution and install it on your system. No need to compile one yourself then. However, if for some reasons you must have 3.x or are on a not

RE: InnoDB: Operating system error number 13 in a file operation

If you got the error message during creation of the innoDB files (e.g. during install) you HAVE TO DELETE all files and restart the installation. There is no way around this! However, if the file got corrupted after installation (e.g. you already used it for days) you can repair it (maybe someone

RE: Can mysql handle this load?

Why using int for date? Better suited would be DATE or DATETIME (or even TIMESTAMP, depending how you want to use it). For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp when you make them UNSIGNED. For status I would choose CHAR(1), you can put a lot of information into that, which

RE: Is it possible to dump images into a database?

Use LOAD DATA INFILE if you want to import it from the filesystem or use INSERT/UPDATE if you do it from an application (e.g. perl, php). Make sure that the column which stores them is defined as BLOB and not as TEXT. Look up the BLOB definition for size limitation and variations on BLOBs. For

RE: Can mysql handle this load?

MySQL allows only ONE primary key per table, so you can only use one. However, you can also define UNIQUE INDEXES. The major difference here is that primary key columns may not contain NULL values, however UNIQUE KEY columns may contain NULL values. If your question now is: Is it better to use

RE: Can mysql handle this load?

Mike, #2 is not 100% correct. Only the columns in the lookup table (table2) need to be indexed. Why? The optimizer first gets a limited set of table1 and then checks if it can join this subset with a key from table2. So for the join condition only table2 needs to be indexed. However, you are

backup / superuser only

Dear all, Is there a way to start mysqld in a way, that say only a superuser can connect. I would need this e.g. for restoring a backup, because I do not want users to connect to the server while I restore the binary log. I already check the options for mysqld but could not find anything

problems with perl DBI:DBD mysql

maybe not the 100% correct list, but then again... SCRIPT - #!/usr/bin/perl -w use DBI; my $dbh = DBI-connect( DBI:mysql:database=xxx, xxx, xxx ) or die( Cannot connect to DB\n

load data infile....

Dear all, I have the following problem with load data... Please excuse layout, but the linewidth is just too short // --- // Here is my table definition // --- mysql desc customer;