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 tex
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 table/alia
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 auto_incre
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 the
andy thomas wrote:
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 surname
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 port
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 t
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
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]
www
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
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 sec_to_time(unix_timestamp(transfer_e
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) as
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 subtractio
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 Tim
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
fail
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,
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)" <[EMA
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() cl
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 machine
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
ins
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 l
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
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
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 re
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 quer
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
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 M
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 chan
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 el.e
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
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 Messag
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
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
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 p
"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.
"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 spec
"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?
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 o
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 te
"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?
Ab
"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
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 https://order.
> 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'
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 th
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 My
Hi,
You could use either something like this
SELECT gallery_id, gallery_name
FROM galleries g
WHERE keywords rlike '(^|,)$keyword_id(,|$)';
or
SELECT gallery_id, gallery_name
FROM galleries g
WHERE $keyword_id in (keywords);
and replace the $key
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]
Subj
> 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.
>
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 bo
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
use,
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 **
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 t
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 impo
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,
- Csong
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 unsubscr
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 Eas
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 lik
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.
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:
-
SEL
> 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 opti
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 to
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]."
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 unless
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 sen
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 sen
65 matches
Mail list logo
