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 --fo
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
(e
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, Ju
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 mea
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 suppor
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
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
a
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 get
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 a
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
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 hash
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]
S
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 AxBxC
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
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 auto_in
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
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 I
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, which
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 ifnull(c.field3,'s
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 so
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 d
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,
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
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
fr
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 a
arithmetics with it (calculate time spans etc.), right?
>
> correct me if i'm wrong, since i had some chaotic encounters
> with DATE and TIMESTAMP values at the beginning of my 'mysql
> time', and i'm using INT unix timestamps since then...
>
> -yves
&g
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
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 yo
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 [mailto:[EMA
>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
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 B
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, e.g.
- Original Message -
From: "Rudy Metzger" <[EMAIL PROTECTED]>
To: "Phil Bitis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, July 14, 2003 10:18 AM
Subject: RE: Improving insertion performance by locking tables
>From what I know is,
M ...
I did not run this vs a DB so please excuse syntax errors and if I
forgot some brackets. But in principle it should work fine.
Cheers
/rudy
-Original Message-
From: Shazia Fazili [mailto:[EMAIL PROTECTED]
Sent: maandag 14 juli 2003 19:35
To: Rudy Metzger
Subject: RE: Rows into
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 command
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 ',act.action,
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 command
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,
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 comple
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: Krasimir_Slave
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 analyze
could be handy.
-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 tr
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]
Se
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
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
-Original
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
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
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. Spl
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 how
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
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 theor
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 query
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
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: Joha
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 hav
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'),
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 with
error, John!
Cheers
/rudy
-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
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 co
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) th
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. Whic
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 Falke
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 Br
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 content
The optimal way for query speed would be to combine the most commonly
used categories. Because when you search the query categories, you will
find out that there is a small set of combinations (say 10) which are
used 90% of the time.
Example: you have the categories A, B, C, D, E, F, G, H, I. Afte
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 t
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 can't
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
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
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
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 14:47
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) '20
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 'W
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
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;
+---+
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
76 matches
Mail list logo
