g'day,
Am having a bit of a problem with using IF. This is the error message I get:
-
SELECT rac.name AS race,
IF (
el.entityType =1, ch. * , npc. *
)charData
FROM characters AS ch, entityLocation AS el, races AS rac, npc AS npc
WHERE el.entityType
IN ( 1, 10 ) AND el.containerType =6 AND
Hello all,
Thank you to everyone who assisted me.
I'm wondering about my theory around the between as posted previously.
Can anyone provide further insights regarding that theory?
Kind regards
Emmanuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
On Monday, June 07, 2004 20:23, Justin Swanhart wrote
It can only ever return one row from table b, for
each row on table a, due
to the contents that is stored in table b. The
table contains in excess of
a million records. What happens as a result of the
I assume that you have multiple
Nik Belajcic wrote:
I have a strange problem importing data from a text file. There are 1353
rows in the text file (generated by a Perl script) but only 1000 get
imported into MySQL. I am clueless why would this be happening - it
seems as if there was a cutoff point at 1000 rows which, of course,
Hi,
I have a situation where, within a PHP loop, I am creating over one hundred SQL
statement (INSERTS and UPDATES).
Would it cut any overhead to send 20 ; seperated queries in one mysql_query()
statement, and therefore only execute mysql_query() 5 times for 100 statements, as
opposed to
Hi,
I have a situation where, within a PHP loop, I am creating over one hundred SQL
statement (INSERTS and UPDATES).
Would it cut any overhead to send 20 ; seperated queries in one mysql_query()
statement, and therefore only execute mysql_query() 5 times for 100 statements, as
opposed to
Hi,
There is a thing I forgot to mention in my previous email - if you are
replicating your database please follow the steps described in
http://dev.mysql.com/doc/mysql/en/PURGE_MASTER_LOGS.html
or you might end with missing data on your slave servers. A binary log should
not be deleted
Here it is
#!/usr/bin/perl
use strict;
use DBI;
my $db_host=localhost;
my $db_user=username;
my $db_pass=password;
my $db_name=database;
my $mail_prog = '/usr/lib/sendmail';
my $email='[EMAIL PROTECTED]';
my $from_email='[EMAIL PROTECTED]';
sub mysql_die{
if ($_[0]){ print $_[0].\n;}
Hi Andrea,
The ' isn't NOT a stopword, it's simply not a word-boundary character,
which I think is what you want. And there is no way I know of to change
that in MySQL... unless you edit the source of course and compile it
yourself. :-) Is that an option for you? If so, I think you just need
The ' isn't NOT a stopword, it's simply not a word-boundary character,
which I think is what you want.
Yes, I expressed myself badly, but you have just greatly understood my
problem.
change that in MySQL... unless you edit the source of course and
compile it yourself. :-) Is that an option
Hi Keith,
I think your problem comes down to the fact that IF() is a function
that must return one value, when you put in your '*' you might be
specifying more then one value to return.
/Johan
Keith wrote:
g'day,
Am having a bit of a problem with using IF. This is the error message I get:
-
On Tue, 8 Jun 2004, Renato Cramer wrote:
Hello Andy,
I don't known if this is possible without handle string, what I don't guess
recommended, because of performance and legibility of code.
One suggestion will be store in column 'surname' (or other) the data already
in format of sort.
In
On Tue, 8 Jun 2004, Franz, Fa. PostDirekt MA wrote:
Hi,
it is not possible to handle all cases proper.
You can just handle all cases you know with the REPLACE-function,
so you simply delete the prefixes in the WHERE-clause.
But that only works for all prefixes you know.
If you do like
On Tue, 8 Jun 2004, Andy Eastham wrote:
Andy,
Just:
select substring_index(surname,' ',-1) as r from advisers order by r;
Yes, that did the trick!
Thanks,
Andy
-Original Message-
From: andy thomas [mailto:[EMAIL PROTECTED]
Sent: 08 June 2004 15:57
To: Andy Eastham
Cc:
Hi,
What is the maximum number of simulataneous connections
that can exist for MySQL. Can we change this limit? If yes please tell
me how to do this. I am using MySQL4.1 in Linux.
Thanks,
ramana.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
Hi,
I have a table that has a few short text fields [text(4000), text(1000)]
I would like to index. Do you think it is a good idea to index them
simply, or is it better if I create auxilary fields which hold the MD5
for the text fields and index those? Would that be faster?
Thank you,
-
venkata ramana wrote:
Hi,
What is the maximum number of simulataneous connections
that can exist for MySQL. Can we change this limit? If yes please tell
me how to do this. I am using MySQL4.1 in Linux.
Thanks,
ramana.
look at max_connections in my.cnf
bye
a
--
Imagination is more
Hi Everyone.
I have the following a table with a varchar column that contains a comma
delimited list of id's from another table that relates the item keywords in
the other table.
The table keywords contains
keyword_id (int/auto increment/primary key)
Keyword (varchar/normal key)
The galleries
Many Thanks to Dobromir Velev,
And for those without PERL but with PHP (or prefer PHP) here is a quick and
dirty port to PHP
REMEMBER TO MAKE A BACKUP OF YOUR FILES BEFORE TESTING OR USING THIS
SCRIPT
IT IS RECOMMENDED YOU TEST THIS SCRIPT IN A NON PRODUCTION ENVIRONMENT
FIRST
Misao wrote:
I have 4 MySQL 4.0.16 servers, all with Dual 3ghz Xeons and 4GB of RAM.
They use InnoDB for all tables, and the ibdata file is 70GB.
The DBs seem to be a little slow, and the darn thing is always using a huge
chunk of swap. I've tried increasing and decreasing what it's allowed to
On Tue, 8 Jun 2004, Michael Stassen wrote:
The proposed solution to sort on a portion of the surname field will work,
but it has a drawback. If you sort on the result of a function applied to a
column, you prevent the use of any index on that column. If your data set
and user base are both
For example:
gallery_id | gallery_name | keywords
1 | test | 1,2,3,4
2 | test2| 3,4,5,6
And I won't to get all the galleries with where the have the
keywords 2, which in this case would be record 1 or keyword 4
which would be both record.
SELECT
Already tried that, but is 2 appears at the end of the list is doesn't get
picked up because there is no comma at the end of the list
Best regards
Andrew Dixon
-Original Message-
From: Dean Urmson [mailto:[EMAIL PROTECTED]
Sent: 11 June 2004 12:53
To: [EMAIL PROTECTED]
On Fri, 11 Jun 2004 13:16:12 +0200, Alberto Mucignat [EMAIL PROTECTED] wrote:
venkata ramana wrote:
Hi,
What is the maximum number of simulataneous connections
that can exist for MySQL. Can we change this limit? If yes please tell
me how to do this. I am using MySQL4.1 in
On Friday, June 11, 2004, 7:00:39 AM, Csongor wrote:
FC Hi,
FC I have a table that has a few short text fields [text(4000), text(1000)]
FC I would like to index. Do you think it is a good idea to index them
FC simply, or is it better if I create auxilary fields which hold the MD5
FC for the text
Already tried that, but is 2 appears at the end of the list
is doesn't get picked up because there is no comma at the end
of the list
Are there spaces between the commas???
If not then
SELECT gallery_id, gallery_name
FROMgalleries
WHERE
keywords = '2'--
You better download the binary release from mysql.com and install it. It's statically
linked so it should work fine on all Linuxes.
Correctly installed MySQL binary release works fine and generally needs no tweaking to
start and run.
--
For technical support contracts, goto
Martijn Tonies [EMAIL PROTECTED] wrote:
If your table contains a timestamp field, it will update each time the
row
is altered. Otherwise I don't think it's possible.
That's on a per ROW basis, not TABLE basis.
But if it is on every row, you can MAX() it to get the latest row update,
Andrea Gangini [EMAIL PROTECTED] wrote:
Well yes, it's an option. I really need this functionality.
But on mysql site, under source downloads, there's this warning: For
maximum stability and performance, we recommend that you use the binaries we
provide.
Is it really true?
Absolutely.
Craig Harding [EMAIL PROTECTED] wrote:
MySQL protocol is a binary protocol. Still it's not encrypted and
data could be sniffed.
Use MySQL-Max and SSL connections to encrypt data in protocol.
I'm wondering how mysql actually sends its data to a mysql client? Is it
binary data or plain text
There are too many reasons for that. Yes, users who press reset button, power
outage,
buggy hardward or OS, even corrupted MySQL builds may cause this to happen.
Example: we often have this kind of troubles on FreeBSD 5.x on high load with big
databases
(tens of gigs). This happens because of
Tabor J. Wells [EMAIL PROTECTED] wrote:
Is it safe to just shutdown ServerB, copy all of the mysql data dir (including
the ibdata files) to ServerC, restart ServerB, and then change the my.cnf of
ServerC to set a new server-id, master-host, replicate-do-db entries and start
ServerC?
You
Misao [EMAIL PROTECTED] wrote:
Take a deep look at http://dev.mysql.com/doc/mysql/en/Server_system_variables.html
But ensure that this is MySQL who takes so much memory. Watch other variables
like key_buffer. MySQL probably should not take that much memory if these are
the only variables
Scott Fletcher [EMAIL PROTECTED] wrote:
I also have another table that use 4 columns of 800 characters along
with 5 columns that use 250 characters. I'm thinking of using TEXT for
9 of those columns.
If you don't plan to store pure binary data in these fields, choose the TEXT
type.
--
Wendell Dingus [EMAIL PROTECTED] wrote:
RedHat Enterprise 3WS, fully up2date. MySQL binary RPMs for AMD64 won't even
start for me, what were they built on?. I installed the .src.rpm and built one
myself (-bb --target amd64). Installed that and all seems well.
This seems strange. Can you
venkata ramana [EMAIL PROTECTED] wrote:
What is the maximum number of simulataneous connections
that can exist for MySQL. Can we change this limit? If yes please tell
me how to do this. I am using MySQL4.1 in Linux.
You can tweak max_connections variable, but on Linux the limit is
I do have ssl compiled in but I just want to compare the ssl connection
with the regular connection to make sure that I'm actually encrypting
the data. call me paranoid.
thanks,
craig.
Egor Egorov wrote:
Craig Harding [EMAIL PROTECTED] wrote:
MySQL protocol is a binary protocol. Still it's not
Excellent, thanks for that. I have used the first way as the second way gave
me different results (lower record count) from what I was getting with it
via a server side script. The first way provide me with the same record
count. Thanks.
Best regards
Andrew Dixon
-Original
Hi,
I have two tables in my database. One that holds information on bookings on
varous projects for a scheduling system, and the other holds project
informtaion.
Is it possible to produce a report that lists total bookings by project a
month with one query i.e.
Jan Feb Mar Apr
You can do this as a UNION statement or the long way. This is a UNION
example:
select rac.name as race, ch.*
from characters as ch
inner join races as rac
on rac.raceID = ch.raceID
inner join entityLocation el
on el.entityID = ch.characterID
and el.visibility =60
and
On Fri, Jun 11, 2004 at 03:38:05PM +0300,
Egor Egorov [EMAIL PROTECTED] is thought to have said:
Tabor J. Wells [EMAIL PROTECTED] wrote:
Is it safe to just shutdown ServerB, copy all of the mysql data dir (including
the ibdata files) to ServerC, restart ServerB, and then change the
I've heard some recent rumblings that MySQL before 4.1 wasn't
multithreaded on OS X. However, in actual usage I see every
indication that is IS.
Does anyone have a definitive answer either way? Thanks!
- John
--
---
John
Hello *
reproducable Problem:
Content of UNION:
logs_20040608,logs_20040609,logs_20040611,logs_20040612,logs_20040613,logs_20040614,
logs_20040615
results: 0E0
DBD::mysql::db do failed: Can't open file: '#sql-13c1_12.MRG'. (errno: 144) at
/usr/local/sbin/new_MERGE_table.pl line 276.
Unable to
A few pointers:
Almost every time, the issue is queries that need optimization. Figure out
which queries are happening at the slow times and look those over
carefully. I cannot count how many times I was asked to throw hardware at
an issue related to MySQL which was really related to bad
On Fri, Jun 11, 2004 at 03:34:14PM +0300, Egor Egorov wrote:
Andrea Gangini [EMAIL PROTECTED] wrote:
Well yes, it's an option. I really need this functionality. But
on mysql site, under source downloads, there's this warning: For
maximum stability and performance, we recommend that
Hello,
I'm trying to find out if mysql 4.0.20 support PAM interface? I find a
pam_mysql utility but there is nothing in the documentation regarding to
pam.
Does anybody has any experince use plugin to replace authentication in
mysql?
Thanks
Aysun
Hi all-
I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible
miscoding of my outer join, but I have a scenario in which I've replicated a
table set up and query from an Oracle application and I'm not getting the
same result set. The following script sets up the representative
Great! It works. I did have to eliminate the parentheses in the SELECT
part:
INSERT PRIVILEGES (login, Permission_ID)
SELECT ('newuser', Permission_ID)
FROM PRIVILEGES
WHERE login='user1'
had to be
INSERT PRIVILEGES (login, Permission_ID)
SELECT 'newuser', Permission_ID
FROM PRIVILEGES
WHERE
I'm using MySQL version 4.0.18. I have two datetime columns in the same
table, one that represents a start time and the other that represents an end
time. I would like to write a query that will show the difference between
these two columns in a HH:MM:SS format. The values of the two columns as
I have been using a local copy of the current production version of MySQL in a windows
environment while we evaluate porting a MS Access front-end to make use of MySQL. We
are ready to go prime-time within my team and as part of this exercise installed a
LINIX version of MySQL on another
Hi Rick,
First, the MySQL IsNULL() function does not operate like the ORACLE or MS
SQL version. It is merely a test and returns either 1 or 0. You will need
to use COALESCE() to provide a non-null replacement for a null value.
I am not sure what you are trying to accomplish with your EXISTS()
You probably want SEC_TO_TIME:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table
Eamon Daly
- Original Message -
From: Dirk Bremer (NISC) [EMAIL
Here is an example using sec_to_time. Note that the results are inconsistent
and sometimes inaccurate. It seems that when the difference is less than one
minute, the result is correct, when it is over one minute, the result is
incorrect.
select ident,
transfer_start,
transfer_end,
Check to make sure the linux box has its port open (do a MySQL ping).
From the windows box, telnet to your linux box on port 3306 (or whatever
you set your linux server to listen on in your my.cnf file) you should see
the version# of the server and a bunch of non-text information. If that
fails,
Looking at the results further reveals that a numeric subtraction is being
performed on the two datetime fields rather than a date-type subtraction.
Any thoughts on how to perform a date subtraction in version 4.0.18?
Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
USA Central
select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start));
Dirk Bremer (NISC) wrote:
Looking at the results further reveals that a numeric subtraction is being
performed on the two datetime fields rather than a date-type subtraction.
Any thoughts on how to perform a date
Close, but time_to_sec requires a time argument, not a datetime argument. My
next iteration is:
select ident,
transfer_start,
transfer_end,
sec_to_time(time_to_sec(substring(transfer_end,12,8)) -
time_to_sec(substring(tra
cast(transfer_end - transfer_start as signed)
I didn't see where these were 4.1+ function so I think it will work. I
refer you to:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into
'negative' time differences)
SELECT
Hi Shawn-
First, thanks for responding. You're re-written query works as I would
expect, even using IFNULL instead of COASLESCE (see PS:).
I'm not sure I explained my issue well enough. Basically, I feel that outer
joins with correlated sub-queries using not exists are broken in MySQL.
The NOT
Bingo, Shawn wins the virtual beer(s). I never looked at the unix_timestamp
function. Thanks to everyone else and wishing you virtual beers as well!
Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471
[EMAIL PROTECTED]
Hello,
I am trying to retrieve a cross join of two tables. Table one contains an id column,
table two contains a column that can list up to three id's from table one.
SELECT dix_ondemand_genre.id, dix_ondemand_shows.genre,
id IN(dix_ondemand_shows.genre) as test
FROM dix_ondemand_genre CROSS
Rick,
You are most welcome. So sorry for the function confusion, I must be
getting tired if I am confusing ISNULL() with IFNULL(). I sure am glad it's
FRIDAY!!!
About your NOT EXISTS() vs correlated subqueries. I tried to simulate
acting as the query engine by creating an intermediate results
Thanks for the pointers. We can connect from the Linux box to my PC so we're convinced
the network is not an issue, but still cannot connect from my PC to the new MySQL
server installed on the Linux box. When we monitor the packets coming in we can see
the request to connect and to MySQL at
andy thomas wrote:
snip
Well, this was fixed in the end by this query:
select substring_index(surname,' ',-1) as r from advisers order by r
which produced the desired result. But we have since had complaints from
individuals wanting their surnames sorted differently! People from Germany
with
MySQL is the only process that runs on these boxes. We dedicate the servers
to MySQL since the DBs are so large. One of the possible problems is that
these servers used to be MyISAM DBs, but we upgraded to InnoDB once we moved
from 3.23.33 to 4.0.16. It is hard to tell the MyISAM variables from
I must be missing something about create temporary table. Here are two sql
commands. The first works the second fails:
CREATE TABLE mytable (id int(10) NOT NULL auto_increment, data
varchar(255), PRIMARY KEY (id) );
CREATE TEMPORARY TABLE mytable2 (id int(10) NOT NULL
I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.
SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;
Not unique
On Friday 11 June 2004 07:00 am, Fagyal, Csongor wrote:
Hi,
I have a table that has a few short text fields
[text(4000), text(1000)] I would like to index. Do
you think it is a good idea to index them simply,
or is it better if I create auxilary fields which
hold the MD5 for the text fields
68 matches
Mail list logo
