SAPDB on RedHat 9

[Shervin]

I am new to SAPDB and install it on Red Hat 9 ...When I wanted to start an
  instance ,I encounter with error
  24994 runtime environment error [db_online -s] check knldiag ,kernel
  died before reaching admin state
  and in knldiag i see :
  ERR 11330 Corehand Aborting due to Signal 11

For solving this problem ,[EMAIL PROTECTED] said me below solution

Set the environment variable LD_ASSUME_KERNEL=2.2.5 before starting
x_server.

Something like this:

 LD_ASSUME_KERNEL=2.2.5
 export LD_ASSUME_KERNEL
 /x_server

Now ,In creation of DB I can go ahead after applying above suggestion but at
middle of it I encounter with this error

 24988 sol error [until execute INIT COMFIT] ,-
connection broken

I will appreciate to help me at earliest possible.


Thanks


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backing up all databases

[Michael Stassen]

You're joking, right?  (Perhaps you thought the original question was a 
joke, too, as root normally has access to all dbs?)

As I understand the manual , 
that will give root access to every db, from every host except 
localhost, with no password!  I can't imagine that's a good idea.  And 
even so, I don't think this will help, as he's connecting from localhost.

If we take the question at face value, it appears he has some dbs that 
root can't access.  (I've never tried it, but I supppose it's possible 
to revoke root's access to a particular db.)  If we assume 
[EMAIL PROTECTED] has a password we don't want to change, the correct 
command would be

  GRANT ALL ON *.* to [EMAIL PROTECTED];

If he wants to change root's password at the same time, he would need to 
add the IDENTIFIED BY clause

  GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword';

Am I missing something?

Michael

Dathan Vance Pattishall wrote:
Look at GRANT on mysql.com

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFITED BY '';

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688
-->-Original Message-
-->From: Randall Perry [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 3:05 PM
-->To: [EMAIL PROTECTED]
-->Subject: Backing up all databases
-->
-->Am testing this command to backup databases:
-->
-->/usr/local/mysql/bin/mysqldump --opt --all-databases >
-->/usr/local/mysql/data/mysqldump
-->
-->Am running as root, but it only backs up databases root has access
too.
-->
-->What's the best way to handle this -- give root full perms on all
dbs?
-->
-->
-->--
-->Randall Perry
-->sysTame
-->
-->Xserve Web Hosting/Co-location
-->Website Development/Promotion
-->Mac Consulting/Sales
-->
-->http://www.systame.com/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Import from excel to MYSQL

[Lists - Dustin Krysak]

Is there an easy way to get an excel spread sheet imported into a MYSQL
database?

Any links to a tutorial?

Thanks in advance!


Dustin



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: self-join, group by, single SQL query

[Robert Citek]

On Tuesday, November 11, 2003, at 03:06  PM, Roger Baklund wrote:

* Robert Citek
Is there a way to do a self-join and a group by in a single SQL query?
See the max-concat trick, described here:

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >
Thanks for the tip.  It looks like it should do the trick.  I'll 
experiment a bit and then post back  the results.

Again, thanks.

Regards,
- Robert
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

memoryTrap

[Jaliya Seneviratne]

Hi
In my program, i have a process to do a set of calculations to around 15 000 
records.Earlier it was working nice.But this time database crashed in the middle of 
the execution.Error lod says that "we intensionally create a a memory trap" ???.  
I tried  to recover the database but still im want able to do that.My mysql version is 
3.23.51 max-nt. Can anyone of you tell me the reson for this and any remedial action.

Re: Mysql just stopped working on my server - any way to track down the problem?

[Matt W]

Hi Matt,

So one database "disappeared" all of a sudden while the others are OK?
Is MySQL running on FreeBSD? If so, that'll be the problem :-) and we'll
point you toward the fix.


Matt


- Original Message -
From: "Matt Babineau"
Sent: Tuesday, November 11, 2003 3:28 PM
Subject: RE: Mysql just stopped working on my server - any way to track
down the problem?


> Its not a startup problem, that works fine. What happened was my php
> application stopped working. This server has multiple databases on it
> mind you. The other databases were working fine.
>
> So one database stopped working and the application could not connect
to
> it. so I did a 'service mysql restart' and it started working again
like
> magic. I looked in the .err file and it only shows when the mysql
server
> stops and starts. I can't see the file on this machine, but it just
> seems to be logging starts and stops.
>
> Thanks for the reply hope this helps-
>
> Matt
>
> On Tue, 2003-11-11 at 19:20, Dathan Vance Pattishall wrote:
> > Can you post what the *.err file said? A key/value in /etc/my.cnf
might
> > be causing a startup problem.
> >
> >
> > - Dathan Vance Pattishall
> >   - Sr. Programmer and mySQL DBA for FriendFinder Inc.
> >   - http://friendfinder.com/go/p40688
> >
> >
> > -->-Original Message-
> > -->From: Matt Babineau [mailto:[EMAIL PROTECTED]
> > -->Sent: Tuesday, November 11, 2003 1:16 PM
> > -->To: [EMAIL PROTECTED]
> > -->Subject: Mysql just stopped working on my server - any way to
track
> > down
> > -->the problem?
> > -->
> > -->Hi All-
> > -->
> > -->I am running 4.0.15-standard on RH9. My mysql database just
stopped
> > -->working, is there a way I can log information about why it stops
like
> > -->this? the *.err was unhelpful.
> > -->
> > -->Any help here is appreciated. Thanks
> > -->
> > -->-Matt


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

back up database

[Didier Godot]

Hi everyone,
 
this is my first post, you will excuse me for my english cause french is my fluent language.
 
So i'm also new to mysql, tonight my question is how to make a back up of all the database on MySQL 4.0.12 running on netware server 6
 
i hava acces to the web console but i don't know the process to do that.
 
thanks for you help...
 
Didier
 
Didier GodotAnalyste en informatiqueCollège Jean-de-Brébeuf[EMAIL PROTECTED]http://www.brebeuf.qc.ca/
BEGIN:VCARD
VERSION:2.1
X-GWTYPE:USER
FN:Godot, Didier
TEL;WORK:5385
ORG:;Service Informatique
EMAIL;WORK;PREF;NGW:[EMAIL PROTECTED]
N:Godot;Didier
TITLE:Analyste en informatique
END:VCARD


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL query question

[Leo]

try group by

SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, 
count(person.name) as "Sum People"
FROM
firmal INNER JOIN (
person INNER JOIN lokasjon
ON person.lokid = lokasjon.lokid)
ON firmal.firmalid = person.firmalid
group by firmal.beskrivelse, lokasjon.navn

-leo-

From: Paal Eriksen 
  To: [EMAIL PROTECTED] 
  Sent: Tuesday, November 11, 2003 7:11 PM
  Subject: SQL query question
   
  SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as 
Location
  FROM
  firmal INNER JOIN (
  person INNER JOIN lokasjon
  ON person.lokid = lokasjon.lokid)
  ON firmal.firmalid = person.firmalid
   
  Businessline, Location, Sum people
  A  AA   10
  A  AB   30
  B  AA   5
  B  AB   27
  B  AC   90

Re: Some help with a complex query

[Leo]

it would help alot if you dump the table structure for us
  - Original Message - 
  From: Elisenda 
  To: [EMAIL PROTECTED] 
  Sent: Tuesday, November 11, 2003 7:10 PM
  Subject: Some help with a complex query


  I have a query which tries to select different fields from 5 different
  tables. 

  In WHERE part I have write all the conditions and relationships. Perhaps two
  many.

  The main table for me is FASE. From this table I try to find all the other
  information.

  I guess I'm doing something wrong but I don't know what.

  SELECT 

  CE.CE_CENTRO, 
  CE.CE_DOMICILIO, 
  CE.CE_CP,
  CE.CE_POBLACION, 
  CE.CE_PROV, 
  PP.PP_CONTACTO, 
  PP.PP_CARGO, 
  CA.CA_HORARIO,
  AU.AU_A_M, 
  AU.AU_A_F, 
  FASE.PR_DATE_VISITA_1

  FROM AU, CA, CE,FASE,PP

  WHERE

  FASE.SQL_ID_PY='P081' AND
  FASE.PR_FLAG= '1' AND
  CA.CA_ID_IDIOMA_A= '6' AND
  AU.AU_NIVEL= '13.14' AND
  AU.AU_Aula= fase.AU_PR_Aula AND
  AU.AU_ID_CE = CA.CA_ID_CE AND
  CE.CE_ID_CE = CA.CA_ID_CE AND
  CE.CE_ID_CE = Fase.SQL_ID_CE AND
  CE.CE_ID_CE = PP.PP_ID_CE AND
  Fase.PR_PP_ID_Coord = PP.PP_ID_PP


  Thank you fro your help in advanced.

  Eli


  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Mysql just stopped working on my server - any way to track down the problem?

[Matt Babineau]

Its not a startup problem, that works fine. What happened was my php
application stopped working. This server has multiple databases on it
mind you. The other databases were working fine.

So one database stopped working and the application could not connect to
it. so I did a 'service mysql restart' and it started working again like
magic. I looked in the .err file and it only shows when the mysql server
stops and starts. I can't see the file on this machine, but it just
seems to be logging starts and stops.

Thanks for the reply hope this helps-

Matt

On Tue, 2003-11-11 at 19:20, Dathan Vance Pattishall wrote:
> Can you post what the *.err file said? A key/value in /etc/my.cnf might
> be causing a startup problem.
> 
> 
> - Dathan Vance Pattishall
>   - Sr. Programmer and mySQL DBA for FriendFinder Inc.
>   - http://friendfinder.com/go/p40688
> 
> 
> -->-Original Message-
> -->From: Matt Babineau [mailto:[EMAIL PROTECTED]
> -->Sent: Tuesday, November 11, 2003 1:16 PM
> -->To: [EMAIL PROTECTED]
> -->Subject: Mysql just stopped working on my server - any way to track
> down
> -->the problem?
> -->
> -->Hi All-
> -->
> -->I am running 4.0.15-standard on RH9. My mysql database just stopped
> -->working, is there a way I can log information about why it stops like
> -->this? the *.err was unhelpful.
> -->
> -->Any help here is appreciated. Thanks
> -->
> -->-Matt
> -->
> -->
> -->--
> -->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: Error 127 = Record-file is crashed

[Dathan Vance Pattishall]

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: rmck [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 3:17 PM
-->To: [EMAIL PROTECTED]
-->Subject: Error 127 = Record-file is crashed
-->
-->Hello,
-->
-->I have "mysql-standard-4.0.16-pc-linux-i686" installed on a 4gig mem,
-->2cpu system, RH 9.
-->I have a large table (Data records: 72426930) that is now giving me
this
-->error "Error 127"
-->when doing selects...
-->
-->This is my first crash of any kind with Mysql. So I'm a liitle
confused
-->on what I should do.
-->The manual states
-->use  myisamchk, then you read further and it says use REPAIR TABLE...
-->
-->I'm confused can someone give me a 1 to end step on repairing this
-->table??
-->
-->At this point I brought my mysqld down.
-->
-->Tried running this:
-->[root]# myisamchk -r  Nov03
-->- recovering (with keycache) MyISAM-table 'Nov03'
-->Data records: 72426930
-->myisamchk: error: Can't create new tempfile: '/opt/logs/Nov03.TMD'
-->MyISAM-table 'Nov03' is not fixed because of errors
-->Try fixing it by using the --safe-recover (-o) or the --force (-f)
option
-->
-->So I tried this:
-->[root]# myisamchk -rf Nov03
-->[EMAIL PROTECTED] ip_logs]# myisamchk -rf Nov03
-->- recovering (with keycache) MyISAM-table 'Nov03'
-->Data records: 72426930
-->5939000
-->
-->It looks like its doing something
-->
-->my data dir and the table in question:
-->-rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD
-->-rw-rw1 mysqlmysql5346325504 Nov 11 14:52 Nov03.MYI
-->-rw-rw1 mysqlmysql631242752 Nov 11 14:52 Nov03.TMD
-->
-->Please advise...

Good you turned off mysql and ran this command. If you didn't you would
just cause further corruption. Next the TMD file means that as myisamchk
repairs, this temp file above is appended as the datafile is compared to
the index file. This is to ensure that the source data file is not
modified (yet) in case of row loss. If you need to recover the lost rows
that occur during this repair (source data file will be stored in a .BAK
file) then it's possible.

So, what you need to do right now is wait till that 631242752 file size
equals the 8812359152 file size.

Myisamchk will report how many rows have been lost if any during the end
of the repair.


-->
-->Thanks
-->Rob
-->
-->
-->
-->--
-->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: max_user_connections problem after upgrading

[Henrik Skotth]

Hello!

I have tested this now, and that isn't the case. Any other ideas?

-- Henrik


Michael McTernan skrev:
> Hi,
>
> Have you tried "netstat -a" on the box with the MySQL server?  This
> command
> (Linux) will show what is connected to where, and will help you double
> check
> that there really aren't any open connections to the server.
>
> Thanks,
>
> Mike
>
>> -Original Message-
>> From: Henrik Skotth [mailto:[EMAIL PROTECTED]
>> Sent: 10 November 2003 18:54
>> To: [EMAIL PROTECTED]
>> Subject: Re: max_user_connections problem after upgrading
>>
>>
>> That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there
>> is
>> no limit to exceed.
>> Also, we aren't getting the error messages ALL the time, they start to
>> appear after a day or two and gets more and more frequent untill I
>> restart
>> mysql. Any other ideas?
>>
>> -- Henrik
>>
>> gerald_clark skrev:
>> > Are you sure you are net exceeding the setting for
>> > maximum connections per hour for that user?
>> >
>> > Henrik Skotth wrote:
>> >
>> >>Hi!
>> >>
>> >>What I meant was that even if there are currently only two user
>> >>connections being used, and the limit is 300, we still get the
>> "already
>> more than max_user_connections" error...
>> >>
>> >>-- Henrik
>> >>
>> >>gerald_clark skrev:
>> >>
>> >>
>> >>>Henrik Skotth wrote:
>> >>>
>> >>>
>> >>>
>> Hello all,
>> 
>> We recently upgraded to 4.0, it went
>> very well and the performance gains have been great.
>> But now the server has started to act strangely. Every few days, the
>> 
>> 
>> >>server starts to refuse connections, saying
>> >>
>> >>
>> that there is already more than max_user_connections, but there is
>> 
>> 
>> >>really only one or two active connections and our max_user_connections
>> is 300. I have to take down and restart the server to solve the problem,
>> and it keeps happening over and over again every few days...
>> >>
>> >>
>> Am I the only one having this problem? Any suggestions?
>> 
>> Regards,
>> -- Henrik Skotth, Hogwarts.nu
>> 
>> 
>> 
>> 
>> 
>> >>>Are there 298 or 299 inactive connections?
>> >>>If so, why are they not being closed?
>> >>>
>> >>>
>> >>>--
>> >>>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]
>
>
>
>
>
>
> --
> 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 just stopped working on my server - any way to track down the problem?

[Matt Babineau]

Hi All-

I am running 4.0.15-standard on RH9. My mysql database just stopped
working, is there a way I can log information about why it stops like
this? the *.err was unhelpful.

Any help here is appreciated. Thanks

-Matt


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Error 127 = Record-file is crashed

[rmck]

Hello,

I have "mysql-standard-4.0.16-pc-linux-i686" installed on a 4gig mem, 2cpu system, RH 
9. 
I have a large table (Data records: 72426930) that is now giving me this error "Error 
127" 
when doing selects...

This is my first crash of any kind with Mysql. So I'm a liitle confused on what I 
should do. 
The manual states
use  myisamchk, then you read further and it says use REPAIR TABLE... 

I'm confused can someone give me a 1 to end step on repairing this table?? 

At this point I brought my mysqld down. 

Tried running this:
[root]# myisamchk -r  Nov03
- recovering (with keycache) MyISAM-table 'Nov03'
Data records: 72426930
myisamchk: error: Can't create new tempfile: '/opt/logs/Nov03.TMD'
MyISAM-table 'Nov03' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option

So I tried this:
[root]# myisamchk -rf Nov03
[EMAIL PROTECTED] ip_logs]# myisamchk -rf Nov03

- recovering (with keycache) MyISAM-table 'Nov03'
Data records: 72426930
5939000

It looks like its doing something

my data dir and the table in question:
-rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD
-rw-rw1 mysqlmysql5346325504 Nov 11 14:52 Nov03.MYI
-rw-rw1 mysqlmysql631242752 Nov 11 14:52 Nov03.TMD

Please advise...

Thanks
Rob



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Backing up all databases

[Dathan Vance Pattishall]

Look at GRANT on mysql.com

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFITED BY '';


- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Randall Perry [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 3:05 PM
-->To: [EMAIL PROTECTED]
-->Subject: Backing up all databases
-->
-->Am testing this command to backup databases:
-->
-->/usr/local/mysql/bin/mysqldump --opt --all-databases >
-->/usr/local/mysql/data/mysqldump
-->
-->Am running as root, but it only backs up databases root has access
too.
-->
-->What's the best way to handle this -- give root full perms on all
dbs?
-->
-->
-->--
-->Randall Perry
-->sysTame
-->
-->Xserve Web Hosting/Co-location
-->Website Development/Promotion
-->Mac Consulting/Sales
-->
-->http://www.systame.com/
-->
-->
-->
-->--
-->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]

Backing up all databases

[Randall Perry]

Am testing this command to backup databases:

/usr/local/mysql/bin/mysqldump --opt --all-databases >
/usr/local/mysql/data/mysqldump

Am running as root, but it only backs up databases root has access too.

What's the best way to handle this -- give root full perms on all dbs?


-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Development/Promotion
Mac Consulting/Sales

http://www.systame.com/



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: newlines and carriage returns in string data

[Matt W]

Hi Stephen,

Oh yes, the second one will work fine. :-)

Note, however, that the string may not be *exactly* the same, even if it
appears to be. For example, if I copied the second query and executed it
on my Windows system, the newline would actually be \r\n and not just \n
as with the first query (since Windows uses \r\n to represent newlines).
So the inserted data would be one byte longer, even though it looks the
same.

For this reason, mysqldump (and others) always uses the first version,
to insure that the restored data is *exactly* the same.

Hope that helps.


Matt


- Original Message -
From: "Stephen Fromm"
Sent: Tuesday, November 11, 2003 4:17 PM
Subject: newlines and carriage returns in string data


> Is it OK to have actual newline/carriage return characters in string
data?
>
> E.g. consider the insert statement
> INSERT into table t1 ( ) VALUES('Here is a newline:\nThere it was!');
>
> Now consider the statement, typed as
> INSERT into table t1 ( ) VALUES('Here is a newline:
> There it was!');
>
> Is the second version legal?
>
> Another way of saying this (I guess) is:  what kinds of characters are
> allowed in a string constant?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Dirt Slow Query On Datetime Range...the saga continues

[Michael Shuler]

Thanks for the quick help everyone...OK I made a few changes

The Query is now:
SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm =
'testrealm.com') AND ('2003-10-11 16:00:00' BETWEEN AcctStartTime AND
AcctStopTime)
Which uses the RealmAndStart index (which as you see in the next line has
been improved slightly).

And I modified the last key to:
KEY `RealmAndStart` (`Realm`,`AcctStartTime`,`AcctStopTime`)

I also though about it for a while and had an inspirational idea that if I
make a key that looks like this:
KEY `StartStopRealm` (`AcctStartTime`,`AcctStopTime`,`Realm`)

It would go even faster because it will narrow down to the records within
the time frame (which is about 1000 records) and then down by the realm name
which would result in ~150 records to count.  Oddly enough in the EXPLAIN it
doesn't even consider it as a possible index to use.  What gets even more
odd is that I swapped the BETWEEN and the Realm in the WHERE clause and then
it decided to use just the plain Realm index...

SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
('2003-10-11 16:00:00' BETWEEN AcctStartTime AND AcctStopTime) AND (Realm =
'testrealm.com') 


Is this a bug and is there a way to force MySQL to use an index that you
know is a better choice?

Thanks again,
Michael Shuler


-Original Message-
From: Michael Shuler [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 11, 2003 3:44 PM
To: [EMAIL PROTECTED]
Subject: Dirt Slow Query On Datetime Range


OK, I give up. To anyone out there who can help me, please explain why this
query runs slower than dirt.  The table has about 1,300,000 records in it,
which is not supposed to be a big deal for MySQL to deal with.  I have tried
it with MyISAM and then changed it to InnoDB which made it even slower but
at least the rest of my queries can continue and not be blocked.  This query
takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC.  In my opinion it
should be 10x faster than that at the very least.

This table is used for RADIUS accounting, all I want to do is find the peak
utilization port utilization for the day.  The only way I have figured out
how to do this is take samples every 5 min and store the highest one.  Here
is the table:

CREATE TABLE `ServiceRADIUSAccounting` (
  `RadAcctId` bigint(21) NOT NULL auto_increment,
  `AcctSessionId` varchar(32) NOT NULL default '',
  `AcctUniqueId` varchar(32) NOT NULL default '',
  `UserName` varchar(64) NOT NULL default '',
  `Realm` varchar(64) default '',
  `NASIPAddress` varchar(15) NOT NULL default '',
  `NASPortId` int(12) default NULL,
  `NASPortType` varchar(32) default NULL,
  `AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
  `AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
  `AcctSessionTime` int(12) default NULL,
  `AcctAuthentic` varchar(32) default NULL,
  `ConnectInfo_start` varchar(32) default NULL,
  `ConnectInfo_stop` varchar(32) default NULL,
  `XmitSpeed` varchar(6) default NULL,
  `RecvSpeed` varchar(6) default NULL,
  `AcctInputOctets` int(12) default NULL,
  `AcctOutputOctets` int(12) default NULL,
  `CalledStationId` varchar(11) NOT NULL default '',
  `CallingStationId` varchar(11) NOT NULL default '',
  `AcctTerminateCause` varchar(32) NOT NULL default '',
  `ServiceType` varchar(32) default NULL,
  `FramedProtocol` varchar(32) default NULL,
  `FramedIPAddress` varchar(15) NOT NULL default '',
  `AcctStartDelay` int(12) default NULL,
  `AcctStopDelay` int(12) default NULL,
  PRIMARY KEY  (`RadAcctId`),
  KEY `UserName` (`UserName`),
  KEY `FramedIPAddress` (`FramedIPAddress`),
  KEY `AcctSessionId` (`AcctSessionId`),
  KEY `AcctUniqueId` (`AcctUniqueId`),
  KEY `AcctStartTime` (`AcctStartTime`),
  KEY `AcctStopTime` (`AcctStopTime`),
  KEY `NASIPAddress` (`NASIPAddress`),
  KEY `Realm` (`Realm`),
  KEY `RealmAndStart` (`Realm`,`AcctStartTime`)
) TYPE=InnoDB AUTO_INCREMENT=4468368 ;


And here is the query:

SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm =
'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND AcctStopTime
>= '2003-11-11 15:30:00')

When I do an explain I get:
tabletype  possible_keys
key   key_len  ref   rows  Extra  
ServiceRADIUSAccounting  ref
AcctStartTime,AcctStopTime,Realm,RealmAndStart  RealmAndStart 65   const
73394 Using where 

Perhapse my InnoDB file needs to be "optimized" if such a thing exists.  I
don't know why this takes so long but I can definitly use some help. Thanks!



Michael Shuler



-- 
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: Dirt Slow Query On Datetime Range

[Matt W]

Hi,

Well, as a side note, you *could* use

'2003-11-11 15:30:00' BETWEEN AcctStopTime AND AcctStartTime

but MySQL can't optimize that to use indexes.

Besides, even if it was on the same column, BETWEEN is treated exactly
the same as <= and >= so BETWEEN wouldn't help anyway (except for
legibility). :-)

Michael, I wouldn't worry about changing VARCHAR to CHAR. Instead, add
AcctStopTime to the end of the "RealmAndStart" index:

ALTER TABLE ServiceRADIUSAccounting
DROP INDEX Realm, -- This is redundant anyway
DROP INDEX RealmAndStart,
ADD INDEX RealmAndStartAndStop (Realm, AcctStartTime, AcctStopTime);

Then it won't need the random seeks to the data file (EXPLAIN should say
"Using index"). Might make it fast enough that you can go back to
MyISAM. :-)

Hope that helps.


Matt


- Original Message -
From: "Mike Johnson"
Sent: Tuesday, November 11, 2003 4:17 PM
Subject: RE: Dirt Slow Query On Datetime Range


From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

> Change your query to use BETWEEN rather that <= and >=.
>
> --ja



> > And here is the query:
> >
> > SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting
> WHERE (Realm =
> > 'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00'
> AND AcctStopTime
> > >= '2003-11-11 15:30:00')


His WHERE clause is on two different fields (AcctStartTime and
AcctStopTime). I don't think a BETWEEN clause is what's needed...


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Foreign Key

[Stephen Fromm]

- Original Message - 
From: "Shravan Durvasula" <[EMAIL PROTECTED]>
To: "MySQL HELP" <[EMAIL PROTECTED]>
Sent: Tuesday, November 11, 2003 12:48 PM
Subject: Foreign Key


> Hi all:
>
> I have a table A(Id, Type). Primary Key is "Id"
> I have another table B(Id, State). Primary Key is "Id"
>
> I also have another table C(Id, ConditionId). Primary Key is "Id". But,
"ConditionId" could be "Id" values from Table A or Table B. So i want to
make it a foreign key. But how can i make the same attribute a foreign key
for more than one table?

If you really want to follow the canons of relational database theory, you
have to be very careful when you do this.  You can read up on this stuff in
discussions of the EER model (*enhanced* entity-relationship model).  I
think it's called a "category".

The way I've done it is to make another table, with just an Id column; call
it AB.  Each Id in A appears exactly once in AB, as does each Id in B.  (So
Id in A is both a pk for A and an fk pointing from A to AB; similarly for B.
And of course Id in AB is the pk for AB.)  Then ConditionId in C is a fk
pointing at AB, not at A or B.

> Thanks,
> -skd
>
>
> -
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Dirt Slow Query On Datetime Range

[Michael Shuler]

No reason for the varchar so I guess I can change it to char if that will
help.
I have tried many different settings in the my.cnf file for inno db but have
seen little improvement.  Any thoughts there?

-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 11, 2003 4:02 PM
To: Michael Shuler
Cc: [EMAIL PROTECTED]
Subject: Re: Dirt Slow Query On Datetime Range


The first thing I noticed is that you are using varchar instead of 
char, why? Unless you have a variable length field like text or blob, 
you can and should use char.
As far as I know, you can't "optimize" InnoDB tables, but you can 
optimize MyISAM using the analyze command as I recall.

Next, have you played with your my.cnf settings? I assume you have, but 
it's a question that must be asked.


On Tuesday, November 11, 2003, at 04:44 PM, Michael Shuler wrote:

> OK, I give up. To anyone out there who can help me, please explain why
> this
> query runs slower than dirt.  The table has about 1,300,000 records in 
> it,
> which is not supposed to be a big deal for MySQL to deal with.  I have 
> tried
> it with MyISAM and then changed it to InnoDB which made it even slower 
> but
> at least the rest of my queries can continue and not be blocked.  This 
> query
> takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC.  In my opinion it
> should be 10x faster than that at the very least.
>
> This table is used for RADIUS accounting, all I want to do is find the
> peak
> utilization port utilization for the day.  The only way I have figured 
> out
> how to do this is take samples every 5 min and store the highest one.  
> Here
> is the table:
>
> CREATE TABLE `ServiceRADIUSAccounting` (
>   `RadAcctId` bigint(21) NOT NULL auto_increment,
>   `AcctSessionId` varchar(32) NOT NULL default '',
>   `AcctUniqueId` varchar(32) NOT NULL default '',
>   `UserName` varchar(64) NOT NULL default '',
>   `Realm` varchar(64) default '',
>   `NASIPAddress` varchar(15) NOT NULL default '',
>   `NASPortId` int(12) default NULL,
>   `NASPortType` varchar(32) default NULL,
>   `AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
>   `AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
>   `AcctSessionTime` int(12) default NULL,
>   `AcctAuthentic` varchar(32) default NULL,
>   `ConnectInfo_start` varchar(32) default NULL,
>   `ConnectInfo_stop` varchar(32) default NULL,
>   `XmitSpeed` varchar(6) default NULL,
>   `RecvSpeed` varchar(6) default NULL,
>   `AcctInputOctets` int(12) default NULL,
>   `AcctOutputOctets` int(12) default NULL,
>   `CalledStationId` varchar(11) NOT NULL default '',
>   `CallingStationId` varchar(11) NOT NULL default '',
>   `AcctTerminateCause` varchar(32) NOT NULL default '',
>   `ServiceType` varchar(32) default NULL,
>   `FramedProtocol` varchar(32) default NULL,
>   `FramedIPAddress` varchar(15) NOT NULL default '',
>   `AcctStartDelay` int(12) default NULL,
>   `AcctStopDelay` int(12) default NULL,
>   PRIMARY KEY  (`RadAcctId`),
>   KEY `UserName` (`UserName`),
>   KEY `FramedIPAddress` (`FramedIPAddress`),
>   KEY `AcctSessionId` (`AcctSessionId`),
>   KEY `AcctUniqueId` (`AcctUniqueId`),
>   KEY `AcctStartTime` (`AcctStartTime`),
>   KEY `AcctStopTime` (`AcctStopTime`),
>   KEY `NASIPAddress` (`NASIPAddress`),
>   KEY `Realm` (`Realm`),
>   KEY `RealmAndStart` (`Realm`,`AcctStartTime`)
> ) TYPE=InnoDB AUTO_INCREMENT=4468368 ;
>
>
> And here is the query:
>
> SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm
> =
> 'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND 
> AcctStopTime
>> = '2003-11-11 15:30:00')
>
> When I do an explain I get:
> tabletype  possible_keys
> key   key_len  ref   rows  Extra
> ServiceRADIUSAccounting  ref
> AcctStartTime,AcctStopTime,Realm,RealmAndStart  RealmAndStart 65   
> const
> 73394 Using where
>
> Perhapse my InnoDB file needs to be "optimized" if such a thing
> exists.  I
> don't know why this takes so long but I can definitly use some help.
> Thanks!
>
> 
>
> Michael Shuler
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newlines and carriage returns in string data

[Stephen Fromm]

Is it OK to have actual newline/carriage return characters in string data?

E.g. consider the insert statement
INSERT into table t1 ( ) VALUES('Here is a newline:\nThere it was!');

Now consider the statement, typed as
INSERT into table t1 ( ) VALUES('Here is a newline:
There it was!');

Is the second version legal?

Another way of saying this (I guess) is:  what kinds of characters are
allowed in a string constant?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Dirt Slow Query On Datetime Range

[Mike Johnson]

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

> Change your query to use BETWEEN rather that <= and >=.
> 
> --ja



> > And here is the query:
> > 
> > SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting 
> WHERE (Realm =
> > 'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' 
> AND AcctStopTime
> > >= '2003-11-11 15:30:00')


His WHERE clause is on two different fields (AcctStartTime and AcctStopTime). I don't 
think a BETWEEN clause is what's needed...


-- 
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Dirt Slow Query On Datetime Range

[jabbott]

Change your query to use BETWEEN rather that <= and >=.

--ja

On Tue, 11 Nov 2003, Michael Shuler wrote:

> OK, I give up. To anyone out there who can help me, please explain why this
> query runs slower than dirt.  The table has about 1,300,000 records in it,
> which is not supposed to be a big deal for MySQL to deal with.  I have tried
> it with MyISAM and then changed it to InnoDB which made it even slower but
> at least the rest of my queries can continue and not be blocked.  This query
> takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC.  In my opinion it
> should be 10x faster than that at the very least.
> 
> This table is used for RADIUS accounting, all I want to do is find the peak
> utilization port utilization for the day.  The only way I have figured out
> how to do this is take samples every 5 min and store the highest one.  Here
> is the table:
> 
> CREATE TABLE `ServiceRADIUSAccounting` (
>   `RadAcctId` bigint(21) NOT NULL auto_increment,
>   `AcctSessionId` varchar(32) NOT NULL default '',
>   `AcctUniqueId` varchar(32) NOT NULL default '',
>   `UserName` varchar(64) NOT NULL default '',
>   `Realm` varchar(64) default '',
>   `NASIPAddress` varchar(15) NOT NULL default '',
>   `NASPortId` int(12) default NULL,
>   `NASPortType` varchar(32) default NULL,
>   `AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
>   `AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
>   `AcctSessionTime` int(12) default NULL,
>   `AcctAuthentic` varchar(32) default NULL,
>   `ConnectInfo_start` varchar(32) default NULL,
>   `ConnectInfo_stop` varchar(32) default NULL,
>   `XmitSpeed` varchar(6) default NULL,
>   `RecvSpeed` varchar(6) default NULL,
>   `AcctInputOctets` int(12) default NULL,
>   `AcctOutputOctets` int(12) default NULL,
>   `CalledStationId` varchar(11) NOT NULL default '',
>   `CallingStationId` varchar(11) NOT NULL default '',
>   `AcctTerminateCause` varchar(32) NOT NULL default '',
>   `ServiceType` varchar(32) default NULL,
>   `FramedProtocol` varchar(32) default NULL,
>   `FramedIPAddress` varchar(15) NOT NULL default '',
>   `AcctStartDelay` int(12) default NULL,
>   `AcctStopDelay` int(12) default NULL,
>   PRIMARY KEY  (`RadAcctId`),
>   KEY `UserName` (`UserName`),
>   KEY `FramedIPAddress` (`FramedIPAddress`),
>   KEY `AcctSessionId` (`AcctSessionId`),
>   KEY `AcctUniqueId` (`AcctUniqueId`),
>   KEY `AcctStartTime` (`AcctStartTime`),
>   KEY `AcctStopTime` (`AcctStopTime`),
>   KEY `NASIPAddress` (`NASIPAddress`),
>   KEY `Realm` (`Realm`),
>   KEY `RealmAndStart` (`Realm`,`AcctStartTime`)
> ) TYPE=InnoDB AUTO_INCREMENT=4468368 ;
> 
> 
> And here is the query:
> 
> SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm =
> 'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND AcctStopTime
> >= '2003-11-11 15:30:00')
> 
> When I do an explain I get:
> tabletype  possible_keys
> key   key_len  ref   rows  Extra  
> ServiceRADIUSAccounting  ref
> AcctStartTime,AcctStopTime,Realm,RealmAndStart  RealmAndStart 65   const
> 73394 Using where 
> 
> Perhapse my InnoDB file needs to be "optimized" if such a thing exists.  I
> don't know why this takes so long but I can definitly use some help.
> Thanks!
> 
> 
> 
> Michael Shuler
> 
> 
> 
> --
> 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: Dirt Slow Query On Datetime Range

[Brent Baisley]

The first thing I noticed is that you are using varchar instead of 
char, why? Unless you have a variable length field like text or blob, 
you can and should use char.
As far as I know, you can't "optimize" InnoDB tables, but you can 
optimize MyISAM using the analyze command as I recall.

Next, have you played with your my.cnf settings? I assume you have, but 
it's a question that must be asked.

On Tuesday, November 11, 2003, at 04:44 PM, Michael Shuler wrote:

OK, I give up. To anyone out there who can help me, please explain why 
this
query runs slower than dirt.  The table has about 1,300,000 records in 
it,
which is not supposed to be a big deal for MySQL to deal with.  I have 
tried
it with MyISAM and then changed it to InnoDB which made it even slower 
but
at least the rest of my queries can continue and not be blocked.  This 
query
takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC.  In my opinion it
should be 10x faster than that at the very least.

This table is used for RADIUS accounting, all I want to do is find the 
peak
utilization port utilization for the day.  The only way I have figured 
out
how to do this is take samples every 5 min and store the highest one.  
Here
is the table:

CREATE TABLE `ServiceRADIUSAccounting` (
  `RadAcctId` bigint(21) NOT NULL auto_increment,
  `AcctSessionId` varchar(32) NOT NULL default '',
  `AcctUniqueId` varchar(32) NOT NULL default '',
  `UserName` varchar(64) NOT NULL default '',
  `Realm` varchar(64) default '',
  `NASIPAddress` varchar(15) NOT NULL default '',
  `NASPortId` int(12) default NULL,
  `NASPortType` varchar(32) default NULL,
  `AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
  `AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
  `AcctSessionTime` int(12) default NULL,
  `AcctAuthentic` varchar(32) default NULL,
  `ConnectInfo_start` varchar(32) default NULL,
  `ConnectInfo_stop` varchar(32) default NULL,
  `XmitSpeed` varchar(6) default NULL,
  `RecvSpeed` varchar(6) default NULL,
  `AcctInputOctets` int(12) default NULL,
  `AcctOutputOctets` int(12) default NULL,
  `CalledStationId` varchar(11) NOT NULL default '',
  `CallingStationId` varchar(11) NOT NULL default '',
  `AcctTerminateCause` varchar(32) NOT NULL default '',
  `ServiceType` varchar(32) default NULL,
  `FramedProtocol` varchar(32) default NULL,
  `FramedIPAddress` varchar(15) NOT NULL default '',
  `AcctStartDelay` int(12) default NULL,
  `AcctStopDelay` int(12) default NULL,
  PRIMARY KEY  (`RadAcctId`),
  KEY `UserName` (`UserName`),
  KEY `FramedIPAddress` (`FramedIPAddress`),
  KEY `AcctSessionId` (`AcctSessionId`),
  KEY `AcctUniqueId` (`AcctUniqueId`),
  KEY `AcctStartTime` (`AcctStartTime`),
  KEY `AcctStopTime` (`AcctStopTime`),
  KEY `NASIPAddress` (`NASIPAddress`),
  KEY `Realm` (`Realm`),
  KEY `RealmAndStart` (`Realm`,`AcctStartTime`)
) TYPE=InnoDB AUTO_INCREMENT=4468368 ;
And here is the query:

SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm 
=
'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND 
AcctStopTime
= '2003-11-11 15:30:00')
When I do an explain I get:
tabletype  possible_keys
key   key_len  ref   rows  Extra
ServiceRADIUSAccounting  ref
AcctStartTime,AcctStopTime,Realm,RealmAndStart  RealmAndStart 65   
const
73394 Using where

Perhapse my InnoDB file needs to be "optimized" if such a thing 
exists.  I
don't know why this takes so long but I can definitly use some help.
Thanks!



Michael Shuler



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Foreign Key

[Shravan Durvasula]

Hi all:
 
I have a table A(Id, Type). Primary Key is "Id"
I have another table B(Id, State). Primary Key is "Id"
 
I also have another table C(Id, ConditionId). Primary Key is "Id". But, "ConditionId" 
could be "Id" values from Table A or Table B. So i want to make it a foreign key. But 
how can i make the same attribute a foreign key for more than one table?
 
Thanks,
-skd


-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

Dirt Slow Query On Datetime Range

[Michael Shuler]

OK, I give up. To anyone out there who can help me, please explain why this
query runs slower than dirt.  The table has about 1,300,000 records in it,
which is not supposed to be a big deal for MySQL to deal with.  I have tried
it with MyISAM and then changed it to InnoDB which made it even slower but
at least the rest of my queries can continue and not be blocked.  This query
takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC.  In my opinion it
should be 10x faster than that at the very least.

This table is used for RADIUS accounting, all I want to do is find the peak
utilization port utilization for the day.  The only way I have figured out
how to do this is take samples every 5 min and store the highest one.  Here
is the table:

CREATE TABLE `ServiceRADIUSAccounting` (
  `RadAcctId` bigint(21) NOT NULL auto_increment,
  `AcctSessionId` varchar(32) NOT NULL default '',
  `AcctUniqueId` varchar(32) NOT NULL default '',
  `UserName` varchar(64) NOT NULL default '',
  `Realm` varchar(64) default '',
  `NASIPAddress` varchar(15) NOT NULL default '',
  `NASPortId` int(12) default NULL,
  `NASPortType` varchar(32) default NULL,
  `AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
  `AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
  `AcctSessionTime` int(12) default NULL,
  `AcctAuthentic` varchar(32) default NULL,
  `ConnectInfo_start` varchar(32) default NULL,
  `ConnectInfo_stop` varchar(32) default NULL,
  `XmitSpeed` varchar(6) default NULL,
  `RecvSpeed` varchar(6) default NULL,
  `AcctInputOctets` int(12) default NULL,
  `AcctOutputOctets` int(12) default NULL,
  `CalledStationId` varchar(11) NOT NULL default '',
  `CallingStationId` varchar(11) NOT NULL default '',
  `AcctTerminateCause` varchar(32) NOT NULL default '',
  `ServiceType` varchar(32) default NULL,
  `FramedProtocol` varchar(32) default NULL,
  `FramedIPAddress` varchar(15) NOT NULL default '',
  `AcctStartDelay` int(12) default NULL,
  `AcctStopDelay` int(12) default NULL,
  PRIMARY KEY  (`RadAcctId`),
  KEY `UserName` (`UserName`),
  KEY `FramedIPAddress` (`FramedIPAddress`),
  KEY `AcctSessionId` (`AcctSessionId`),
  KEY `AcctUniqueId` (`AcctUniqueId`),
  KEY `AcctStartTime` (`AcctStartTime`),
  KEY `AcctStopTime` (`AcctStopTime`),
  KEY `NASIPAddress` (`NASIPAddress`),
  KEY `Realm` (`Realm`),
  KEY `RealmAndStart` (`Realm`,`AcctStartTime`)
) TYPE=InnoDB AUTO_INCREMENT=4468368 ;


And here is the query:

SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm =
'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND AcctStopTime
>= '2003-11-11 15:30:00')

When I do an explain I get:
tabletype  possible_keys
key   key_len  ref   rows  Extra  
ServiceRADIUSAccounting  ref
AcctStartTime,AcctStopTime,Realm,RealmAndStart  RealmAndStart 65   const
73394 Using where 

Perhapse my InnoDB file needs to be "optimized" if such a thing exists.  I
don't know why this takes so long but I can definitly use some help.
Thanks!



Michael Shuler



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Some help with a complex query

[Roger Baklund]

* Elisenda
> I have a query which tries to select different fields from 5 different
> tables.
>
> In WHERE part I have write all the conditions and relationships.
> Perhaps two many.

Joining 5 tables should not be a problem, but having indexes on the relevant
columns may be essential, especially on large tables.

> The main table for me is FASE. From this table I try to find all the other
> information.
>
> I guess I'm doing something wrong but I don't know what.

What is the problem? Do you get an error message, does it return unexpected
results, or is it just too slow?

> SELECT
>
> CE.CE_CENTRO,
> CE.CE_DOMICILIO,
> CE.CE_CP,
> CE.CE_POBLACION,
> CE.CE_PROV,
> PP.PP_CONTACTO,
> PP.PP_CARGO,
> CA.CA_HORARIO,
> AU.AU_A_M,
> AU.AU_A_F,
> FASE.PR_DATE_VISITA_1
>
> FROM AU, CA, CE,FASE,PP
>
> WHERE
>
> FASE.SQL_ID_PY='P081' AND
> FASE.PR_FLAG= '1' AND
> CA.CA_ID_IDIOMA_A= '6' AND
> AU.AU_NIVEL= '13.14' AND
> AU.AU_Aula= fase.AU_PR_Aula AND
> AU.AU_ID_CE = CA.CA_ID_CE AND
> CE.CE_ID_CE = CA.CA_ID_CE AND
> CE.CE_ID_CE = Fase.SQL_ID_CE AND
> CE.CE_ID_CE = PP.PP_ID_CE AND
> Fase.PR_PP_ID_Coord = PP.PP_ID_PP

It's difficult to suggest changes without knowing what the problem is... :)
I can however safely suggest that you use a consistent letter casing on your
table names... is it FASE, Fase or fase? On some MySQL servers this will
make a difference, on others it may not. (I think mysql on windows is case
insensitive by default, but this may be changed at compile-time, iirc.)

Please tell us what the problem is, and if it's about efficiency, post the
output of "EXPLAIN SELECT ", that should get us started. :)

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: After installing MySQL 4.0.16 (rpm), it did not create /etc/my.cnf ?

[Admin-Stress]

After I copied /usr/share/mysql/my-large.cnf it to /etc/my.cnf

Then I restart mysql, and executed ps -ax 

I got this :

32175 pts/3S  0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
--pid-file=/var/lib/mysql/myserver.pid
32200 pts/3S  0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql 
--user=mysql
--pid-file=/var/lib/mysql/myserver.pid --skip-locking --port=3306

Why there is no reference to /etc/my.cnf there ?

Because if I see in other 3.23.58 (rpm), it's like this :

 2653 ?S  0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf
 2678 ?S  0:02 /usr/libexec/mysqld --defaults-file=/etc/my.cnf 
--basedir=/usr
--datadir=/var/lib/mysql --user=mysql --pid-file=/var/run

Something wrong with my 4.0.16 (rpm) installation ?

Or should I edit something in the /etc/my.cnf ? it just straight copy from
/usr/share/mysql/my-large.cnf

Please help,

Thanks

--- Dathan Vance Pattishall <[EMAIL PROTECTED]> wrote:
> Create one.
> 
> Look in your base directory under support-files
> Look for my-small.cnf
> 
> 
> - Dathan Vance Pattishall
>   - Sr. Programmer and mySQL DBA for FriendFinder Inc.
>   - http://friendfinder.com/go/p40688
> 
> 
> -->-Original Message-
> -->From: Admin-Stress [mailto:[EMAIL PROTECTED]
> -->Sent: Tuesday, November 11, 2003 11:44 AM
> -->To: [EMAIL PROTECTED]
> -->Subject: After installing MySQL 4.0.16 (rpm), it did not create
> -->/etc/my.cnf ?
> -->
> -->Hi,
> -->
> -->My server is using RedHat 9.0.
> -->
> -->I just CHANGED the mysql from 3.23.58 (rpm) to 4.0.16 (rpm).
> -->
> -->What I did :
> -->
> -->  # rpm -e 
> -->  # rm -rf /var/lib/mysql
> -->
> -->  then install the 4.0.16 (rpm)
> -->
> -->  # rpm -i MySQL-server  MySQL-client ... MySQL-devel ... MySQL-
> -->shared-compat
> -->
> -->After this,
> -->
> -->I can start the server.
> -->I can change the root password using mysqladmin.
> -->I can create database.
> -->
> -->But I cant find /etc/my.cnf
> -->
> -->is this normal ?
> -->
> -->I verified by issuing: rpm -ql MySQL-server ...
> -->But again, I did not see /etc/my.cnf
> -->
> -->Anyone know what is the 'standard' my.cnf for MySQL 4.0.16 ?
> -->
> -->Thanks,
> -->
> -->__
> -->Do you Yahoo!?
> -->Protect your identity with Yahoo! Mail AddressGuard
> -->http://antispam.yahoo.com/whatsnewfree
> -->
> -->--
> -->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 you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Upgraded to 4.0.16 from 3.2.x --- why did service file change?

[Dathan Vance Pattishall]

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Brian Snyder [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 1:12 PM
-->To: [EMAIL PROTECTED]
-->Subject: RE: Upgraded to 4.0.16 from 3.2.x --- why did service file
-->change?
-->
-->On Tue, 2003-11-11 at 16:04, Dathan Vance Pattishall wrote:
-->> - Dathan Vance Pattishall
-->>   - Sr. Programmer and mySQL DBA for FriendFinder Inc.
-->>   - http://friendfinder.com/go/p40688
-->>
-->>
-->> -->-Original Message-
-->> -->From: Brian Snyder [mailto:[EMAIL PROTECTED]
-->> -->Sent: Tuesday, November 11, 2003 12:51 PM
-->> -->To: [EMAIL PROTECTED]
-->> -->Subject: Upgraded to 4.0.16 from 3.2.x --- why did service file
-->> change?
-->> -->
-->> -->I upgraded from RPM and I noticed that the /etc/rc.d/init.d
script
-->> was
-->> -->changed from mysqld to mysql.  It also appears as if the
'status'
-->> option
-->> -->that you can pass into service was removed.
-->> -->
-->> -->I'm just curious why the owners would do that?
-->>
-->> Maybe because your actually not starting the mysqld daemon, your
-->> starting a wrapper script.
-->>
-->> -->What is the rationale to changing a script name and removing
-->> -->funtionality?
-->>
-->> No functionality has been removed. What are you noticing?
-->
-->The old mysql service script was called mysqld , the new one is
called
-->mysql.
-->
-->You used to be able to say 'service mysqld status' and get an OK if
-->running.  Now support for status was removed, and its just start,
stop,
-->and restart.
-->
-->BTW, This is on redhat.

chkconfig --add mysql


-->
-->brian
-->
-->
-->--
-->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: C API: undefined reference

[Aftab Jahan Subedar]

alright. looking at your directory this should be ok.

gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib/mysql -lmysqlclient
;)
Aftab Jahan Subedar
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
sms://+447765341890
tel://+88027519050
[EMAIL PROTECTED]
http://www.DhakaStockExchangeGame.com/
hAj wrote:
Hello Jahan,

on 2003.11.11 07:43, Aftab Jahan Subedar at [EMAIL PROTECTED] wrote:


use the following options to compile

gcc seeLog.c -o seeLog -I/usr/local/include -L/usr/local/lib/mysql
-lmysqlclient
Strangely, your options and mine came out with the same error:
The first is yours and 2nd is mine.
~/www ->gcc seeLog.c -o seeLog -I/usr/local/include -L/usr/local/lib/mysql
-lmysqlclient
/tmp/ccuX7I5J.o: In function `main':
/tmp/ccuX7I5J.o(.text+0x2a): undefined reference to `mysql_connect'
collect2: ld returned 1 exit status
~/www ->gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient
/tmp/ccZxcaCR.o: In function `main':
/tmp/ccZxcaCR.o(.text+0x2a): undefined reference to `mysql_connect'
collect2: ld returned 1 exit status
What does this "undefined reference" really imply, that the lib binary is
broken ?
Below is to show you where the .h and lib file I need are located on this
particular system I'm trying to run the thing:
~/www ->find / -name '*mysql.h*' 2>&-
/home/temp/installd/buildapache/php-4.3.3/ext/dbx/dbx_mysql.h
/home/temp/installd/buildapache/php-4.3.3/ext/mysql/libmysql/mysql.h
/home/temp/installd/buildapache/php-4.3.3/ext/mysql/php_mysql.h
/home/cpapachebuild/buildapache/php-4.3.3/ext/dbx/dbx_mysql.h
/home/cpapachebuild/buildapache/php-4.3.3/ext/mysql/libmysql/mysql.h
/home/cpapachebuild/buildapache/php-4.3.3/ext/mysql/php_mysql.h
/usr/include/mysql/mysql.h
~/www ->find / -name '*mysqlclient*' 2>&-
/usr/lib/mysql/libmysqlclient.a
/usr/lib/mysql/libmysqlclient.la
/usr/lib/mysql/libmysqlclient_r.a
/usr/lib/mysql/libmysqlclient_r.la
/usr/lib/libmysqlclient.so
/usr/lib/libmysqlclient.so.12
/usr/lib/libmysqlclient.so.12.0.0
/usr/lib/libmysqlclient_r.so
/usr/lib/libmysqlclient_r.so.12
/usr/lib/libmysqlclient_r.so.12.0.0
/usr/lib/libmysqlclient.so.10
/usr/lib/libmysqlclient.so.10.0.0
/usr/lib/libmysqlclient_r.so.10
/usr/lib/libmysqlclient_r.so.10.0.0
/usr/lib/libmysqlclient.so.9
/usr/lib/libmysqlclient.so.6


Best,
hAj



Aftab Jahan Subedar
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
sms://+447765341890
tel://+88027519050
[EMAIL PROTECTED]
http://www.DhakaStockExchangeGame.com/
hAj wrote:

Hello MySQL pros worldwide,

~/www ->cat seeLog.c
#define USE_OLD_FUNCTIONS
#include 
#include 
int main() {
MYSQL mysql;
MYSQL *mysqldb = NULL;
mysqldb = mysql_connect(&mysql, "geneofcube.net", "USERID",
"PASSWORD");
return 0;
}
~/www ->gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient
use the following options to compile
-I/usr/local/include -L/usr/local/lib/mysql -lmysqlclient
jahan



/tmp/ccEj3tmv.o: In function `main':
/tmp/ccEj3tmv.o(.text+0x2a): undefined reference to `mysql_connect'
collect2: ld returned 1 exit status
~/www ->
As shown above, I'm having a problem getting rid of a compilation error
(undefined reference) coming out with a very simple c code (seeLog.c) which
I wrote for a testing purpose.
Got no I idea what I'm doing wrong or missing here.
I'd appreciate any of your suggestions.

Best,
hAj










--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database-design

[Saqib Ali]

you need to normalize your database structure, and create an ER diagram
based on that.


On Tue, 11 Nov 2003, Meli Meli wrote:

>
> I have a table with following structure:
>
> Id
> first quarter
> second quarter
> third quarter
> last quarter
> year
> month
> week
> day
>
> On an entry not all fields of the four quarter fields are covered with values.
> Following combinations are possible:
>
> first quarter | second quarter | third quarter | last quarter
>
>
>
> xnullnull   null
>
> null  x  null   null
>
> null null  xnull
>
> null nullnullx
>
> x  x null   null
>
> x null xnull
>
> x null   null x
>
> null   x   xnull
>
> null   x  nullx
>
> null  null x  x
>
> x   x   xnull
>
> x   x null x
>
> x null  x  x
>
> null   xx  x
>
> x  xx  x
>
>
>
> The table will receive many thousands of entry’s.
>
> Would it be better to divide the table in to 15 small tables in order to not 
> register fields with null values?
>
>
>
> Thanks for helping
>
> Regards Martin
>
>
>
> -
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: self-join, group by, single SQL query

[Roger Baklund]

* Robert Citek
> Is there a way to do a self-join and a group by in a single SQL query?

Yes, but I'm not sure if that is what you need...

> For example, I have an PurchaseOrder table (see below) and would like
> to know what was the most recent order-number for each customer and the
> amount of that order.  So far, I have been able to do this in two steps:
>1) create a temporary table of the group by data
>2) join the new table with the original
>
> Thanks in advance for any pointers or URLs to on-line docs explaining
> how to do this.

See the max-concat trick, described here:

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >

SELECT CustNo,MAX(CONCAT(OrderNo,'|',Amnt))
  FROM PO
  GROUP BY CustNo;

You may need to pad OrderNo with zeroes, if they are not all the same number
of digits, and you can split the column in the SQL using string functions,
see the example at the url above.

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Upgraded to 4.0.16 from 3.2.x --- why did service file change?

[Brian Snyder]

On Tue, 2003-11-11 at 16:04, Dathan Vance Pattishall wrote:
> - Dathan Vance Pattishall
>   - Sr. Programmer and mySQL DBA for FriendFinder Inc.
>   - http://friendfinder.com/go/p40688
> 
> 
> -->-Original Message-
> -->From: Brian Snyder [mailto:[EMAIL PROTECTED]
> -->Sent: Tuesday, November 11, 2003 12:51 PM
> -->To: [EMAIL PROTECTED]
> -->Subject: Upgraded to 4.0.16 from 3.2.x --- why did service file
> change?
> -->
> -->I upgraded from RPM and I noticed that the /etc/rc.d/init.d script
> was
> -->changed from mysqld to mysql.  It also appears as if the 'status'
> option
> -->that you can pass into service was removed.
> -->
> -->I'm just curious why the owners would do that?
> 
> Maybe because your actually not starting the mysqld daemon, your
> starting a wrapper script.
> 
> -->What is the rationale to changing a script name and removing
> -->funtionality?
> 
> No functionality has been removed. What are you noticing?

The old mysql service script was called mysqld , the new one is called
mysql.

You used to be able to say 'service mysqld status' and get an OK if
running.  Now support for status was removed, and its just start, stop,
and restart.

BTW, This is on redhat.

brian


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: maintaining size of a db

[Scott H]

Repost - Haven't gotten any response on this and
can't find an answer.  If no one on the mysql
list knows, where does a fellow turn?  Help!

> Can't seem to find this one in the manual or
> archives - how do I control a db to maintain
> its size to an arbitrary value, say 20 GB? I 
> want to just rotate records, deleting those 
> that are oldest.
> 
> Thanks!!




.


=
--

To announce that there must be no criticism of the President, or that we are to stand 
by the President, right or wrong, is not only unpatriotic and servile, but is morally 
treasonable to the American public.
  -- Theodore Roosevelt, 1918






.

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Upgraded to 4.0.16 from 3.2.x --- why did service file change?

[Dathan Vance Pattishall]

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Brian Snyder [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 12:51 PM
-->To: [EMAIL PROTECTED]
-->Subject: Upgraded to 4.0.16 from 3.2.x --- why did service file
change?
-->
-->I upgraded from RPM and I noticed that the /etc/rc.d/init.d script
was
-->changed from mysqld to mysql.  It also appears as if the 'status'
option
-->that you can pass into service was removed.
-->
-->I'm just curious why the owners would do that?

Maybe because your actually not starting the mysqld daemon, your
starting a wrapper script.

-->What is the rationale to changing a script name and removing
-->funtionality?

No functionality has been removed. What are you noticing?

-->
-->Thanx,
--> brian
-->
-->
-->--
-->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: Unix Socket Daemon

[Dathan Vance Pattishall]

mySQL already does that look at /var/lib/mysql/mysql.sock=. You can add
skip-networking to my.cnf not to allow tcp connections at all.




- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Arnoldus Th.J. Koeleman [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 12:29 PM
-->To: [EMAIL PROTECTED]
-->Subject: Unix Socket Daemon
-->
-->I wanna bring up the mysql daemon on a unix domain socket.
-->
-->
-->
-->Any Idea how??
-->
-->
-->
-->I don't wanna use the localhost TCP




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query question (php)

[Obantec Support1]

- Original Message -
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Obantec Support1" <[EMAIL PROTECTED]>
Sent: Tuesday, November 11, 2003 8:46 PM
Subject: Re: Query question (php)


> * Obantec Support1
> > mysql 3.23.56 RH 8.0
> >
> > I have got the sql query to work but is there a better way of writing
the
> > syntax?
> >
> > $sql = "SELECT * FROM Contacts WHERE Categories=\"$calltype\" and
> > BusinessCodes !=\"R\" and BusinessCodes !=\"I\" and BusinessCodes
!=\"L\"
> > ORDER BY Company ASC";
> >
> > basically i Categories (a limit number of 4 or 5 types) but exclude 'n'
> > number of BusinessCodes defined by 1 or 2 ASCII letters.
> >
> > I tried BusinessCodes !=\"R\". !=\"I\". !=\"L\" but get Couldn't execute
> > query
>
> You could use the IN operator and single quotes to make it easier to read:
>
> $sql = "SELECT * FROM Contacts WHERE Categories='$calltype' and
>   BusinessCodes NOT IN ('R','I','L') ORDER BY Company ASC";
>
> --
> Roger
>
Thanks Roger

That's just the method i needed!

Mark




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query question (php)

[Roger Baklund]

* Obantec Support1 
> mysql 3.23.56 RH 8.0
> 
> I have got the sql query to work but is there a better way of writing the
> syntax?
> 
> $sql = "SELECT * FROM Contacts WHERE Categories=\"$calltype\" and
> BusinessCodes !=\"R\" and BusinessCodes !=\"I\" and BusinessCodes !=\"L\"
> ORDER BY Company ASC";
> 
> basically i Categories (a limit number of 4 or 5 types) but exclude 'n'
> number of BusinessCodes defined by 1 or 2 ASCII letters.
> 
> I tried BusinessCodes !=\"R\". !=\"I\". !=\"L\" but get Couldn't execute
> query

You could use the IN operator and single quotes to make it easier to read:

$sql = "SELECT * FROM Contacts WHERE Categories='$calltype' and
  BusinessCodes NOT IN ('R','I','L') ORDER BY Company ASC";

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Upgraded to 4.0.16 from 3.2.x --- why did service file change?

[Brian Snyder]

I upgraded from RPM and I noticed that the /etc/rc.d/init.d script was
changed from mysqld to mysql.  It also appears as if the 'status' option
that you can pass into service was removed.  

I'm just curious why the owners would do that? What is the rationale to
changing a script name and removing funtionality? It just seems to break
all scripts that relied on that functionality -- am I missing something?

Thanx,
 brian


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Unix Socket Daemon

[Arnoldus Th.J. Koeleman]

I wanna bring up the mysql daemon on a unix domain socket.

 

Any Idea how??

 

I don't wanna use the localhost TCP 

RE: After installing MySQL 4.0.16 (rpm), it did not create /etc/my.cnf ?

[Dathan Vance Pattishall]

Create one.

Look in your base directory under support-files
Look for my-small.cnf


- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Admin-Stress [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, November 11, 2003 11:44 AM
-->To: [EMAIL PROTECTED]
-->Subject: After installing MySQL 4.0.16 (rpm), it did not create
-->/etc/my.cnf ?
-->
-->Hi,
-->
-->My server is using RedHat 9.0.
-->
-->I just CHANGED the mysql from 3.23.58 (rpm) to 4.0.16 (rpm).
-->
-->What I did :
-->
-->  # rpm -e 
-->  # rm -rf /var/lib/mysql
-->
-->  then install the 4.0.16 (rpm)
-->
-->  # rpm -i MySQL-server  MySQL-client ... MySQL-devel ... MySQL-
-->shared-compat
-->
-->After this,
-->
-->I can start the server.
-->I can change the root password using mysqladmin.
-->I can create database.
-->
-->But I cant find /etc/my.cnf
-->
-->is this normal ?
-->
-->I verified by issuing: rpm -ql MySQL-server ...
-->But again, I did not see /etc/my.cnf
-->
-->Anyone know what is the 'standard' my.cnf for MySQL 4.0.16 ?
-->
-->Thanks,
-->
-->__
-->Do you Yahoo!?
-->Protect your identity with Yahoo! Mail AddressGuard
-->http://antispam.yahoo.com/whatsnewfree
-->
-->--
-->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: Database-design

[Chris Boget]

> On an entry not all fields of the four quarter fields are covered with
> values.   Following combinations are possible:  

Why don't you just use one extra column (apart from whatever other
data you are storing) with that column being a date field.  Then, you 
can determine what data is part of what time period as part of the query.
Perhaps I'm not understanding what your ultimate need is, but I don't
see any reason why you would need a schema such as the example
you provided or 15 extra tables.

Chris


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL benchmarks on the G5: Jaguar vs. Panther

[Jan Pieter Kunst]

If you're using only MyISAM tables for your data, then key_buffer 
should generally have as much RAM as it can have (up to the point 
where it makes sense, i.e., only has much RAM as is needed to store 
your index data). myisam_sort_buffer_size ought to be about 1/4 of 
your RAM.
I re-ran the benchmarks with a 'tuned' my.cnf (much more memory for 
various variables). It looks to me like there is no significant 
difference in the results.

I checked (with phpmyadmin, MySQL system variables) if the new my.cnf 
was actually being used: it was.

JP

New my.cnf + benchmark results:
==
set-variable= key_buffer=512M
set-variable= max_allowed_packet=1M
set-variable= table_cache=1024
set-variable= sort_buffer=8M
set-variable= net_buffer_length=32K
set-variable= record_buffer=8M
set-variable= thread_cache=8
set-variable= myisam_sort_buffer_size=256M
alter-table: Total time: 181 wallclock secs ( 0.21 usr  0.05 sys + 
0.00 cusr  0.00 csys =  0.26 CPU)
ATIS: Total time: 63 wallclock secs ( 5.06 usr  6.26 sys +  0.00 cusr 
0.00 csys = 11.32 CPU)
big-tables: Total time: 55 wallclock secs ( 6.60 usr 14.08 sys + 
0.00 cusr  0.00 csys = 20.68 CPU)
connect: Total time: 235 wallclock secs (36.76 usr 37.77 sys +  0.00 
cusr  0.00 csys = 74.53 CPU)
create: Total time: 287 wallclock secs ( 7.25 usr  2.56 sys +  0.00 
cusr  0.00 csys =  9.81 CPU)
insert: Total time: 3018 wallclock secs (415.53 usr 260.06 sys + 
0.00 cusr  0.00 csys = 675.59 CPU)
select: Total time: 1371 wallclock secs (46.60 usr 23.62 sys +  0.00 
cusr  0.00 csys = 70.22 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 25 wallclock secs ( 2.67 usr  2.41 sys +  0.00 
cusr  0.00 csys =  5.08 CPU)

Old my.cnf + benchmark results:
==
set-variable   = key_buffer=64M
set-variable   = max_allowed_packet=1M
set-variable   = table_cache=256
set-variable   = sort_buffer=4M
set-variable   = net_buffer_length=8K
set-variable   = record_buffer=1M
set-variable   = thread_cache=8
set-variable   = myisam_sort_buffer_size=8M
alter-table: Total time: 141 wallclock secs ( 0.22 usr  0.11 sys + 
0.00 cusr  0.00 csys =  0.33 CPU)
ATIS: Total time: 59 wallclock secs (10.59 usr  6.30 sys +  0.00 cusr 
0.00 csys = 16.89 CPU)
big-tables: Total time: 57 wallclock secs ( 7.53 usr 13.50 sys + 0.00 
cusr  0.00 csys = 21.03 CPU)
connect: Total time: 252 wallclock secs (44.31 usr 38.81 sys +  0.00 
cusr  0.00 csys = 83.12 CPU)
create: Total time: 298 wallclock secs ( 8.76 usr  3.06 sys +  0.00 
cusr  0.00 csys = 11.82 CPU)
insert: Total time: 3316 wallclock secs (594.82 usr 261.66 sys + 0.00 
cusr  0.00 csys = 856.48 CPU)
select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys +  0.00 
cusr  0.00 csys = 92.68 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 27 wallclock secs ( 4.19 usr  2.58 sys +  0.00 
cusr  0.00 csys =  6.77 CPU)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

After installing MySQL 4.0.16 (rpm), it did not create /etc/my.cnf ?

[Admin-Stress]

Hi,

My server is using RedHat 9.0.

I just CHANGED the mysql from 3.23.58 (rpm) to 4.0.16 (rpm).

What I did :

  # rpm -e 
  # rm -rf /var/lib/mysql
  
  then install the 4.0.16 (rpm)

  # rpm -i MySQL-server  MySQL-client ... MySQL-devel ... MySQL-shared-compat

After this,

I can start the server.
I can change the root password using mysqladmin.
I can create database.

But I cant find /etc/my.cnf

is this normal ?

I verified by issuing: rpm -ql MySQL-server ...
But again, I did not see /etc/my.cnf

Anyone know what is the 'standard' my.cnf for MySQL 4.0.16 ?

Thanks,

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Database-design

[Meli Meli]

I have a table with following structure:  
  
Id  
first quarter  
second quarter  
third quarter  
last quarter  
year  
month  
week  
day  
  
On an entry not all fields of the four quarter fields are covered with values.   
Following combinations are possible:  
  
first quarter | second quarter | third quarter | last quarter

 

xnullnull   null

null  x  null   null

null null  xnull

null nullnullx

x  x null   null

x null xnull

x null   null x

null   x   xnull

null   x  nullx

null  null x  x

x   x   xnull

x   x null x

x null  x  x

null   xx  x

x  xx  x

 

The table will receive many thousands of entry’s.

Would it be better to divide the table in to 15 small tables in order to not register 
fields with null values?

 

Thanks for helping

Regards Martin



-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

Re: Getting Corrupt MyISAM Table

[gerald_clark]

Héctor Villafuerte D. wrote:

mysql> repair table otr_hist;
+--++--+-+
| Table| Op | Msg_type | Msg_text|
+--++--+-+
| otr.otr_hist | repair | error| 28 when writing to datafile |
| otr.otr_hist | repair | status   | Operation failed|
+--++--+-+
2 rows in set (2 hours 17 min 50.28 sec)
mysql> show create table otr_hist;
ERROR 1016: Can't open file: 'otr_hist.MYI'. (errno: 144)
Should I try with myisamchk instead?
Thanks for your help,
Hector
perror 28

Error code  28:  No space left on device.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: A question on INTERVAL

[Dathan Vance Pattishall]

The parser does not execute the query before it parses it. Thus you
cannot do that. Use Code, and or have an idea what the INTERVAL UNIT is
prior to executing the query.



- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Erik Osterman [mailto:[EMAIL PROTECTED]
-->Sent: Wednesday, November 12, 2003 4:05 AM
-->To: [EMAIL PROTECTED]
-->Subject: A question on INTERVAL
-->
-->
-->I would like to do something like this
-->
-->SELECT * FROM orders,subscriptions
--> WHERE orders.subscription_id = subscriptions.id
--> created < created - INTERVAL subscriptions.term
subscriptions.unit;
-->
-->But I get "ERROR 1064: You have an error in your SQL syntax near
-->'subscriptions.unit)'"
-->
-->If I replace subscriptions.unit to 'YEAR' it works fine.
-->
-->The 'subscriptions' table has 2 columns: term (int) and unit (enum).
-->The enum types for unit are 'YEAR', 'MONTH', etc...
-->
-->Anyone have any ideas on how to use MySQL's wonderful date & time
-->functions
-->to accomplish what I am trying to do? I'd like to avoid using
approximate
-->techniques such as using 86400 seconds for a day, since calculating
-->months
-->and years _accurately_ is very involved.
-->
-->
-->P.S. DATE_SUB(created, INTERVAL subscriptions.term
subscriptions.unit)
-->had
-->the same parse error.
-->
-->
-->Regards,
-->
-->Erik Osterman
-->
-->
-->
-->
-->--
-->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]

A question on INTERVAL

[Erik Osterman]

I would like to do something like this

SELECT * FROM orders,subscriptions 
WHERE orders.subscription_id = subscriptions.id
created < created - INTERVAL subscriptions.term subscriptions.unit;

But I get "ERROR 1064: You have an error in your SQL syntax near
'subscriptions.unit)'"

If I replace subscriptions.unit to 'YEAR' it works fine.

The 'subscriptions' table has 2 columns: term (int) and unit (enum).
The enum types for unit are 'YEAR', 'MONTH', etc...

Anyone have any ideas on how to use MySQL's wonderful date & time functions
to accomplish what I am trying to do? I'd like to avoid using approximate
techniques such as using 86400 seconds for a day, since calculating months
and years _accurately_ is very involved.


P.S. DATE_SUB(created, INTERVAL subscriptions.term subscriptions.unit) had
the same parse error.


Regards,

Erik Osterman




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

large LOAD DATA INFILE and replication

[Mike Johnson]

I performed a LOAD DATA INFILE on two very large (2G and 997M) files to a db server 
that acts as a master to several slaves. The LOAD DATA was to a non-replicated db, and 
should not have been replicated, but it appears that, as it was not done as a LOAD 
DATA LOCAL INFILE, the data files were replicated (albeit uselessly) to the slave 
servers.
 
Right off the bat, this resulted in a packet size issue which we resolved by adjusting 
max_allowed_packet, but the disk capacity of the file systems on the slave hosts 
filled to capacity. At this point, we were unable to set SQL_SLAVE_SKIP_COUNTER 
anywhere past the point of the data load and ended up stuck with broken replication on 
the slave machines.
 
Here's a tail of an error log of one of the slaves:
 
03  7:04:10  Error in Log_event::read_log_event(): 'read error', data_len: 
825362754, event_type: 49
03  7:04:10  Error reading relay log event: slave SQL thread aborted because of 
I/O error
03  7:04:10  Slave: Could not parse relay log event entry. The possible reasons 
are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' 
on the binary log), the slave's relay log is corrupted (you can check this by running 
'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or 
slave's MySQL code. If you want to check the master's binary log or slave's relay log, 
you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. 
Error_code: 0
03  7:04:10  Error running query, slave SQL thread aborted. Fix the problem, and 
restart the slave SQL thread with "SLAVE START". We stopped at log 'central-bin.041' 
position 649082785
 
As stated above, we cannot set SQL_SLAVE_SKIP_COUNTER to go beyond position 649082785.
 
The master is Redhat Linux 9 running MySQL 4.0.15-standard-log, and the slaves are 
Redhat Linux 7.1 running MySQL 4.0.15-standard-log as well.
 
Has anyone run into this before?
 
 
-- 
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Getting Corrupt MyISAM Table

["Héctor Villafuerte D."]

Héctor Villafuerte D. wrote:

Hi all,
In the following lines I'll try to describe the situation the best I can.
Thanks in advance for your help!
Hector
-> Here's the situation:

* I have repeatedly got a corrupt table (which is pretty big, like 25M 
rows)
* MyISAM tables (the one with the problem is *otr_hist*)
* MySQL 4.0.16-nt
* Windows XP PRO (using NTFS, so file size shouldn't be a problem, 
right?)

-> Here are some of the messages from MySQL:

mysql> show tables;
ERROR 2013: Lost connection to MySQL server during query
mysql> show tables;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:5
Current database: otr
+---+
| Tables_in_otr |
+---+
| otr_hist  |
| otr_new   |
| otr_tmp1  |
| otr_tmp2  |
+---+
4 rows in set (0.78 sec)
mysql> show processlist;
++++--+-+---+---+--+
| Id | User   | Host   | db   | Command | Time  | State | 
Info |
++++--+-+---+---+--+
|  4 | villaf | localhost:1884 | otr  | Sleep   | 26130 |   | 
NULL |
|  5 | villaf | localhost:1997 | otr  | Query   | 0 | NULL  | show 
processlist |
++++--+-+---+---+--+

-> Here's the table structure:
CREATE TABLE `otr_hist` (
 `tel` char(8) default NULL,
 `telefb` char(14) default NULL,
 `rutaentran` char(7) default NULL,
 `rutasalien` char(7) default NULL,
 `minutos` int(16) default NULL,
 KEY `tel` (`tel`,`telefb`,`rutaentran`,`rutasalien`,`minutos`)
) TYPE=MyISAM
-> Here's the error:
mysql> show index from otr_hist;
ERROR 1016: Can't open file: 'otr_hist.MYI'. (errno: 144)
-> Here's the python script which should be doing all the job,
I think it's pretty easy to follow, if you like you can see the
thread "Additive UPDATE":
# Operators Traffic Report (otr)
# Hector Villafuerte D.
# 20031106
#
# otr.py : v.0.1 : Prepares the monthly Operators Traffic Report
import MySQLdb, string, os
db = MySQLdb.connect(host = "localhost", user = "villaf", passwd = 
"secret", db = "otr")
cursor = db.cursor()

def update_hist(csv):
   # Loads new data
   cursor.execute("""TRUNCATE TABLE otr_new""")
   cursor.execute("""ALTER TABLE otr_new DISABLE KEYS""")
   cursor.execute("""LOAD DATA INFILE '%s' INTO TABLE otr_new FIELDS 
TERMINATED BY ',' """
  """ENCLOSED BY '\"' IGNORE 1 LINES""" % (csv))
   cursor.execute("""ALTER TABLE otr_new ENABLE KEYS""")

   # Intersection between *otr_hist* & *otr_new*
   cursor.execute("""DROP TABLE IF EXISTS otr_tmp1""")
   cursor.execute("create table otr_tmp1 "
  "select a.tel, a.telefb, a.rutaentran, a.rutasalien, 
a.minutos + b.minutos as minutos "
  "from otr_hist as a join otr_new as b using(tel, 
telefb, rutaentran, rutasalien)")
   cursor.execute("""ALTER TABLE otr_tmp1 ADD INDEX (tel, telefb, 
rutaentran, rutasalien, minutos)""")

   # Difference between *otr_new* & *otr_hist*
   cursor.execute("""DROP TABLE IF EXISTS otr_tmp2""")
   cursor.execute("create table otr_tmp2 "
  "select a.* from otr_new as a left join otr_hist as 
b using(tel, telefb, rutaentran, rutasalien) "
  "where b.minutos is null")

   # Updates *otr_hist* with *otr_tmp1*
   cursor.execute("UPDATE otr_hist AS A, otr_tmp1 AS B "
  "SET A.minutos = B.minutos WHERE "
  "A.tel = B.tel AND A.telefb = B.telefb AND "
  "A.rutaentran = B.rutaentran AND A.rutasalien = 
B.rutasalien")

   # Updates *otr_hist* with *otr_tmp2*
   cursor.execute("""ALTER TABLE otr_hist DISABLE KEYS""")
   cursor.execute("""INSERT INTO otr_hist SELECT * FROM otr_tmp2""")
   cursor.execute("""ALTER TABLE otr_hist ENABLE KEYS""")
update_hist("c:tmp20031014.csv")
update_hist("c:tmp20031015.csv")
update_hist("c:tmp20031016.csv")
update_hist("c:tmp20031017.csv")
update_hist("c:tmp20031018_21.csv")
update_hist("c:tmp20031022.csv")
update_hist("c:tmp20031023.csv")
update_hist("c:tmp20031024.csv")
update_hist("c:tmp20031025_27.csv")
update_hist("c:tmp20031028.csv")
update_hist("c:tmp20031029.csv")
update_hist("c:tmp20031030.csv")
update_hist("c:tmp20031031.csv")
update_hist("c:tmp20031101.csv")
-> And here are the questions :)
* Is this a bug in 4.0.16?
* Would you suggest me another approach for this task, in order to 
avoid the corruption?

-> Strange stuff! I've just noticed this weird behaviour: I have 
cygwin so I did a *ls -l*
and a *dir* in the database directory. Now see the size of 
*otr_hist.MYI* in *ls*! Weird, isn't it?

C:\mysql\data\otr>dir
Volume in drive C has no label.
Volume Serial Number is 4C83-B7BD
Directory of C:\mysql\data\otr
11/11/2003  08:45 AM  .
11/11/2003  08:45 AM  ..
06/11/20

Getting Corrupt MyISAM Table

["Héctor Villafuerte D."]

Hi all,
In the following lines I'll try to describe the situation the best I can.
Thanks in advance for your help!
Hector
-> Here's the situation:

* I have repeatedly got a corrupt table (which is pretty big, like 25M rows)
* MyISAM tables (the one with the problem is *otr_hist*)
* MySQL 4.0.16-nt
* Windows XP PRO (using NTFS, so file size shouldn't be a problem, right?)
-> Here are some of the messages from MySQL:

mysql> show tables;
ERROR 2013: Lost connection to MySQL server during query
mysql> show tables;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:5
Current database: otr
+---+
| Tables_in_otr |
+---+
| otr_hist  |
| otr_new   |
| otr_tmp1  |
| otr_tmp2  |
+---+
4 rows in set (0.78 sec)
mysql> show processlist;
++++--+-+---+---+--+
| Id | User   | Host   | db   | Command | Time  | State | 
Info |
++++--+-+---+---+--+
|  4 | villaf | localhost:1884 | otr  | Sleep   | 26130 |   | 
NULL |
|  5 | villaf | localhost:1997 | otr  | Query   | 0 | NULL  | show 
processlist |
++++--+-+---+---+--+

-> Here's the table structure:
CREATE TABLE `otr_hist` (
 `tel` char(8) default NULL,
 `telefb` char(14) default NULL,
 `rutaentran` char(7) default NULL,
 `rutasalien` char(7) default NULL,
 `minutos` int(16) default NULL,
 KEY `tel` (`tel`,`telefb`,`rutaentran`,`rutasalien`,`minutos`)
) TYPE=MyISAM
-> Here's the error:
mysql> show index from otr_hist;
ERROR 1016: Can't open file: 'otr_hist.MYI'. (errno: 144)
-> Here's the python script which should be doing all the job,
I think it's pretty easy to follow, if you like you can see the
thread "Additive UPDATE":
# Operators Traffic Report (otr)
# Hector Villafuerte D.
# 20031106
#
# otr.py : v.0.1 : Prepares the monthly Operators Traffic Report
import MySQLdb, string, os
db = MySQLdb.connect(host = "localhost", user = "villaf", passwd = 
"secret", db = "otr")
cursor = db.cursor()

def update_hist(csv):
   # Loads new data
   cursor.execute("""TRUNCATE TABLE otr_new""")
   cursor.execute("""ALTER TABLE otr_new DISABLE KEYS""")
   cursor.execute("""LOAD DATA INFILE '%s' INTO TABLE otr_new FIELDS 
TERMINATED BY ',' """
  """ENCLOSED BY '\"' IGNORE 1 LINES""" % (csv))
   cursor.execute("""ALTER TABLE otr_new ENABLE KEYS""")

   # Intersection between *otr_hist* & *otr_new*
   cursor.execute("""DROP TABLE IF EXISTS otr_tmp1""")
   cursor.execute("create table otr_tmp1 "
  "select a.tel, a.telefb, a.rutaentran, a.rutasalien, 
a.minutos + b.minutos as minutos "
  "from otr_hist as a join otr_new as b using(tel, 
telefb, rutaentran, rutasalien)")
   cursor.execute("""ALTER TABLE otr_tmp1 ADD INDEX (tel, telefb, 
rutaentran, rutasalien, minutos)""")

   # Difference between *otr_new* & *otr_hist*
   cursor.execute("""DROP TABLE IF EXISTS otr_tmp2""")
   cursor.execute("create table otr_tmp2 "
  "select a.* from otr_new as a left join otr_hist as b 
using(tel, telefb, rutaentran, rutasalien) "
  "where b.minutos is null")

   # Updates *otr_hist* with *otr_tmp1*
   cursor.execute("UPDATE otr_hist AS A, otr_tmp1 AS B "
  "SET A.minutos = B.minutos WHERE "
  "A.tel = B.tel AND A.telefb = B.telefb AND "
  "A.rutaentran = B.rutaentran AND A.rutasalien = 
B.rutasalien")

   # Updates *otr_hist* with *otr_tmp2*
   cursor.execute("""ALTER TABLE otr_hist DISABLE KEYS""")
   cursor.execute("""INSERT INTO otr_hist SELECT * FROM otr_tmp2""")
   cursor.execute("""ALTER TABLE otr_hist ENABLE KEYS""")
update_hist("c:tmp20031014.csv")
update_hist("c:tmp20031015.csv")
update_hist("c:tmp20031016.csv")
update_hist("c:tmp20031017.csv")
update_hist("c:tmp20031018_21.csv")
update_hist("c:tmp20031022.csv")
update_hist("c:tmp20031023.csv")
update_hist("c:tmp20031024.csv")
update_hist("c:tmp20031025_27.csv")
update_hist("c:tmp20031028.csv")
update_hist("c:tmp20031029.csv")
update_hist("c:tmp20031030.csv")
update_hist("c:tmp20031031.csv")
update_hist("c:tmp20031101.csv")
-> And here are the questions :)
* Is this a bug in 4.0.16?
* Would you suggest me another approach for this task, in order to avoid 
the corruption?

-> Strange stuff! I've just noticed this weird behaviour: I have cygwin 
so I did a *ls -l*
and a *dir* in the database directory. Now see the size of 
*otr_hist.MYI* in *ls*! Weird, isn't it?

C:\mysql\data\otr>dir
Volume in drive C has no label.
Volume Serial Number is 4C83-B7BD
Directory of C:\mysql\data\otr
11/11/2003  08:45 AM  .
11/11/2003  08:45 AM  ..
06/11/2003  05:50 PM 8,682 ot

Re: offline replication with binlogs

[Egor Egorov]

"Mauro Marcellino" <[EMAIL PROTECTED]> wrote:
> 
> Hello again...
> 
> I am transferring binlogs between two networks that are not connected as an offline 
> replication solution.  I have two databases which I have configured the my.ini file 
> to create binlog entries for.  
> 
> Do both databases get written to the same log or is there a separate log for each 
> database.  If both are written to the same log how do you use the mysqlbinlog 
> command to update both databases?  Do you just run it twice like this?...

Queries will be written to the same log. If you look at the output of mysqlbinlog you 
can see USE database_name commands that change current database. So you need execute 
mysqlbinlog just once:
http://www.mysql.com/doc/en/mysqlbinlog.html
> 
> 
> mysqlbinlog binlog_name > db1
> 
> mysqlbinlog binlog_name > db2



-- 
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: GUI interface

[Martijn Tonies]

Hi Alisa,

> Hello.  I'm looking for a GUI client that will run under Windows 2000 and
> attach to Solaris 8 server that will allow me to see all the tables and
data
> in my mysql database.

If you're looking for another tool that does more, there's a new
tool in town: Database Workbench ( www.upscene.com ). It
yes has to earn its stripes, but give it a go.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: GUI interface

[Chris Boget]

> Hello.  I'm looking for a GUI client that will run under Windows 2000
> and attach to Solaris 8 server that will allow me to see all the tables 
> and data in my mysql database.

Others have mentioned phpMyAdmin.  While that is an excellent piece
of code, I find that it's someone slow to do what I need/want.  
I recommend checking out the following windows GUI clients:

mySQL-Front - development has been terminated for this software but
I'm sure you can find and download it from somewhere.  I find it very
useful

Mascon - http://scibit.com/products/mascon/ - there is a free version with
reduced functionality and a paid version with all functionality.

Chris




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: GUI interface

[Haydies]

Ye, me to, only its solaris 9. Dosn't matter what server you have
though.

phpMyAdmin is cool, I use it most of the time. Runs on the apache server
(also solaris), works like a dream in every browser I have used so its
fairly platform independant. :-)

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, November 11, 2003 2:19 PM
Subject: Re: GUI interface



if you want a windows client, get MySqlCC right off the home page
www.mysql.com.  I'm using htis in the exact setup you mentioned.

a webclient? -> http://www.phpmyadmin.net/home_page/

There are morethese are free.

hth
Jeff



  "Alisa Brouwer"
  <[EMAIL PROTECTED]>To:
<[EMAIL PROTECTED]>
   cc:
  11/11/2003 09:03 Subject:  GUI interface
  AM
  Please respond to
  alisa







Hello.  I'm looking for a GUI client that will run under Windows 2000 and
attach to Solaris 8 server that will allow me to see all the tables and
data
in my mysql database.

Thanks for any help.

Thank you,
Alisa Brouwer
Network Administrator
eServ, LLC
8200 North Allen Road
Peoria, IL 61615
(309) 691-4957  ext. 1033
cell - (309) 645-5318
[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: GUI interface

[jeffrey_n_Dyke]

if you want a windows client, get MySqlCC right off the home page
www.mysql.com.  I'm using htis in the exact setup you mentioned.

a webclient? -> http://www.phpmyadmin.net/home_page/

There are morethese are free.

hth
Jeff


   
 
  "Alisa Brouwer"  
 
  <[EMAIL PROTECTED]>To:   <[EMAIL PROTECTED]> 
   
   cc: 
 
  11/11/2003 09:03 Subject:  GUI interface 
 
  AM   
 
  Please respond to
 
  alisa
 
   
 
   
 




Hello.  I'm looking for a GUI client that will run under Windows 2000 and
attach to Solaris 8 server that will allow me to see all the tables and
data
in my mysql database.

Thanks for any help.

Thank you,
Alisa Brouwer
Network Administrator
eServ, LLC
8200 North Allen Road
Peoria, IL 61615
(309) 691-4957  ext. 1033
cell - (309) 645-5318
[EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

GUI interface

[Alisa Brouwer]

Hello.  I’m looking for a GUI client that will run under Windows 2000 and
attach to Solaris 8 server that will allow me to see all the tables and data
in my mysql database.

Thanks for any help.

Thank you,
Alisa Brouwer
Network Administrator
eServ, LLC
8200 North Allen Road
Peoria, IL 61615
(309) 691-4957  ext. 1033
cell - (309) 645-5318
[EMAIL PROTECTED]

Re: Retrieving the system type that MySQL is running on.

[Nathan Jones]

The user is to define the database and table names, but may not know the 
system that MySQL is on, and try to request a table or database expecting 
case sensitivity and wind up with case insensitivity.  I want to check the 
existence of the database or table, before trying to create it, rather than 
try to create it and find out it can't be created because it already 
exists.  An alternative (which is actually better) would be a query to 
determine case sensitivity, but I can't figure out how to get that info 
from MySQL either.

At 02:43 AM 11/11/2003, Matt W wrote:
Hi Nathan,

I don't think there's a way if MySQL is on a separate machine, as I'm
only aware of checking in PHP.
Hmm, maybe you could create a dummy table and see if it's case-sensitive
or not? He he.
Just curious, why would you need to know what OS MySQL is on?

Matt

- Original Message -
From: "Nathan Jones"
Sent: Monday, November 10, 2003 8:02 PM
Subject: Retrieving the system type that MySQL is running on.
> I'm relatively new to MySQL, and I'm writing a PHP script that doesn't
know
> what kind of system the MySQL database is on.  The script and database
> don't have to be on the same system, so I need a way to determine what
kind
> of system (*nix, Win, etc.) MySQL is running on.  Is there a "fool
proof"
> query to determine this?  If so, what is it?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Creating random data in a test table based on an existing tab le

[Luis Lebron]

Thanks for the help. I tried your suggestion but only got 1 row inserted in
the test table.

Luis

-Original Message-
From: Olof Tjerngren [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 3:01 AM
To: Luis Lebron
Cc: Mysql (E-mail)
Subject: Re: Creating random data in a test table based on an existing
table


How about somethine like this as a starting point:

insert into testuser (firstname,lastname) select 
u1.firstname,u2.lastname from user u1, user u2 order by rand() limit 1;

MvH,

Luis Lebron wrote:
> I have an users table for a php application that I am programming. The
> current users table has about 1500 records. I would like to create a test
> table (i.e. users_test) with 10,000 records based on random data from the
> first table (i.e. random first name combined with a random last name,
> etc...). Is it possible to do this via a MySQL query? 
> 
> Luis R. Lebron
> Sigmatech, Inc
> 

RE: Setting wait_timeout in my.cnf?

[Hoeven, Maarten van der]

tsss, how about that :)

yes, I can set interactive_timeout to a value in my.cnf
(interactive_timeout=30). This works! However, as a side-effect,
wait_timeout is *also* set to 30. 

Right, problem solved. Thanks, Nils.

Maarten

-Original Message-
From: Nils Valentin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 1:48 PM
To: [EMAIL PROTECTED]
Cc: Hoeven, Maarten van der
Subject: Re: Setting wait_timeout in my.cnf?


Hmm.

You could try 

show variables like "%timeout%";

perhaps interactive_timeout

 can help you out ?

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/


On Tuesday 11 November 2003 21:37, Hoeven, Maarten van der wrote:
> nope, that didnt help. Thanks though!
>
> Any other suggestions?
>
> -Original Message-
> From: Nils Valentin [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 11, 2003 1:00 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Setting wait_timeout in my.cnf?
>
>
>
> Hi Maarten,
>
> I havent tried it recently but substitute any underbar (_) with a
hiven
> (-)
> when doing a setting in the my.cnf.
>
>
> --
> kind regards
>
> Nils Valentin
> Tokyo/Japan
>
> http://www.be-known-online.com/mysql/
>
> On Tuesday 11 November 2003 20:22, Hoeven, Maarten van der wrote:
> > correction: section shoud be read as [mysqld]
> >
> > -Original Message-
> > From: Hoeven, Maarten van der
> > Sent: Tuesday, November 11, 2003 12:02 PM
> > To: [EMAIL PROTECTED]
> > Subject: Setting wait_timeout in my.cnf?
>
> --
> kind regards
>
> Nils Valentin
> Tokyo/Japan
>
> http://www.be-known-online.com/mysql/
>
> > Hi all,
> >
> > gosh, I must be overlooking something obvious. Running mysql 4.0.16.
I
> > want to set the wait_timeout in my.cnf (section [mysql]) like:
> >
> > wait_timeout = 300
> >
> > also tried the depricated
> >
> > set-variable = wait_timeout=300
> >
> > Both are not working; still default to 28800. Sure, I stopped and
> > started the server.
> >
> >
> > However, when I set the variable in the mysql-client with:
> >
> > set wait_timeout=30;
> >
> > the variable wait_timeout is changed!
> >
> >
> > What am I overlooking? I really want to set this in my config-file,
> > rather that setting it manually in the client... Any hints?
> >
> >
> > PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8
> >
> >
> > Maarten
> > --
> > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > --
> > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
>
> ---
>
> --
> kind regards
>
> Nils Valentin
> Tokyo/Japan
>
> http://www.be-known-online.com/mysql/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> --
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/
--
Zie ook/see also: http://www.knmi.nl/maildisclaimer.html 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Setting wait_timeout in my.cnf?

[Victoria Reznichenko]

"Hoeven, Maarten van der" <[EMAIL PROTECTED]> wrote:
> 
> gosh, I must be overlooking something obvious. Running mysql 4.0.16. I
> want to set the wait_timeout in my.cnf (section [mysql]) like:
> 
> wait_timeout = 300
> 
> also tried the depricated
> 
> set-variable = wait_timeout=300
> 
> Both are not working; still default to 28800. Sure, I stopped and
> started the server.

Set up value for interactive_timeout variable, too.

> 
> 
> However, when I set the variable in the mysql-client with:
> 
> set wait_timeout=30;
> 
> the variable wait_timeout is changed!
> 
> 
> What am I overlooking? I really want to set this in my config-file,
> rather that setting it manually in the client... Any hints?
> 
> 
> PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

SPADE on RAH

[Shervin]

Hi

I am new to SPADE and install to on Red Hat 9 ...When I wanted to start an
instance ,I encounter with error
 24994 runtime environment error [db_online -s] check knldiag ,kernel
died before reaching admin state
and in knldiag i see :
  ERR 11330 Corehand Aborting due to Signal 11


I appreciate anybody to help me


thanks


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Setting wait_timeout in my.cnf?

[Nils Valentin]

Hmm.

You could try 

show variables like "%timeout%";

perhaps interactive_timeout

 can help you out ?

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/


On Tuesday 11 November 2003 21:37, Hoeven, Maarten van der wrote:
> nope, that didnt help. Thanks though!
>
> Any other suggestions?
>
> -Original Message-
> From: Nils Valentin [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 11, 2003 1:00 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Setting wait_timeout in my.cnf?
>
>
>
> Hi Maarten,
>
> I havent tried it recently but substitute any underbar (_) with a hiven
> (-)
> when doing a setting in the my.cnf.
>
>
> --
> kind regards
>
> Nils Valentin
> Tokyo/Japan
>
> http://www.be-known-online.com/mysql/
>
> On Tuesday 11 November 2003 20:22, Hoeven, Maarten van der wrote:
> > correction: section shoud be read as [mysqld]
> >
> > -Original Message-
> > From: Hoeven, Maarten van der
> > Sent: Tuesday, November 11, 2003 12:02 PM
> > To: [EMAIL PROTECTED]
> > Subject: Setting wait_timeout in my.cnf?
>
> --
> kind regards
>
> Nils Valentin
> Tokyo/Japan
>
> http://www.be-known-online.com/mysql/
>
> > Hi all,
> >
> > gosh, I must be overlooking something obvious. Running mysql 4.0.16. I
> > want to set the wait_timeout in my.cnf (section [mysql]) like:
> >
> > wait_timeout = 300
> >
> > also tried the depricated
> >
> > set-variable = wait_timeout=300
> >
> > Both are not working; still default to 28800. Sure, I stopped and
> > started the server.
> >
> >
> > However, when I set the variable in the mysql-client with:
> >
> > set wait_timeout=30;
> >
> > the variable wait_timeout is changed!
> >
> >
> > What am I overlooking? I really want to set this in my config-file,
> > rather that setting it manually in the client... Any hints?
> >
> >
> > PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8
> >
> >
> > Maarten
> > --
> > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > --
> > Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
>
> ---
>
> --
> kind regards
>
> Nils Valentin
> Tokyo/Japan
>
> http://www.be-known-online.com/mysql/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> --
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL query question

[Paal Eriksen]

So close, Thanks you very much Andy. I tried one similar to your suggestion, but 
didn't get quite the result i expected.
 
Cheers
Paal

Ny versjon av Yahoo! Messenger 
Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom

RE: Re: Setting wait_timeout in my.cnf?

[Hoeven, Maarten van der]

nope, that didnt help. Thanks though!

Any other suggestions?

-Original Message-
From: Nils Valentin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 1:00 PM
To: [EMAIL PROTECTED]
Subject: Re: Setting wait_timeout in my.cnf?



Hi Maarten,

I havent tried it recently but substitute any underbar (_) with a hiven
(-)
when doing a setting in the my.cnf.


--
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

On Tuesday 11 November 2003 20:22, Hoeven, Maarten van der wrote:
> correction: section shoud be read as [mysqld]
>
> -Original Message-
> From: Hoeven, Maarten van der
> Sent: Tuesday, November 11, 2003 12:02 PM
> To: [EMAIL PROTECTED]
> Subject: Setting wait_timeout in my.cnf?

--
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

> Hi all,
>
> gosh, I must be overlooking something obvious. Running mysql 4.0.16. I
> want to set the wait_timeout in my.cnf (section [mysql]) like:
>
> wait_timeout = 300
>
> also tried the depricated
>
> set-variable = wait_timeout=300
>
> Both are not working; still default to 28800. Sure, I stopped and
> started the server.
>
>
> However, when I set the variable in the mysql-client with:
>
> set wait_timeout=30;
>
> the variable wait_timeout is changed!
>
>
> What am I overlooking? I really want to set this in my config-file,
> rather that setting it manually in the client... Any hints?
>
>
> PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8
>
>
> Maarten
> --
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> --
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

---

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Zie ook/see also: http://www.knmi.nl/maildisclaimer.html 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL query question

[Andy Eastham]

Pael,

Try this:

SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location,
count(person.[uniqueid])
FROM firmal INNER JOIN (
person INNER JOIN lokasjon
ON person.lokid = lokasjon.lokid)
ON firmal.firmalid = person.firmalid
GROUP BY firmal.beskrivelse, lokasjon.navn

Replace [uniqueid] with the primary key of the person table.

Andy
> -Original Message-
> From: Paal Eriksen [mailto:[EMAIL PROTECTED]
> Sent: 11 November 2003 12:11
> To: [EMAIL PROTECTED]
> Subject: SQL query question
>
>
> Hi, i have the following query:
>
> SELECT person.name as Name, firmal.beskrivelse as Businessline,
> lokasjon.navn as Location
> FROM
> firmal INNER JOIN (
> person INNER JOIN lokasjon
> ON person.lokid = lokasjon.lokid)
> ON firmal.firmalid = person.firmalid
>
> which will give me a list of Name, Businessline, Location. What
> i'm trying to do is to get a list which is grouped on
> Businessline and Location. Then i want to list a count of name at
> each location and businessline. How can i achieve this, if it's possible?
> So it should be like this:
>
> Businessline, Location, Sum people
> A  AA   10
> A  AB   30
> B  AA   5
> B  AB   27
> B  AC   90
>
> Paal
>
> Ny versjon av Yahoo! Messenger
> Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt
> så morsom



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL query question

[Paal Eriksen]

Hi, i have the following query: 
 
SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as 
Location
FROM
firmal INNER JOIN (
person INNER JOIN lokasjon
ON person.lokid = lokasjon.lokid)
ON firmal.firmalid = person.firmalid

which will give me a list of Name, Businessline, Location. What i'm trying to do is to 
get a list which is grouped on Businessline and Location. Then i want to list a count 
of name at each location and businessline. How can i achieve this, if it's possible?
So it should be like this:
 
Businessline, Location, Sum people
A  AA   10
A  AB   30
B  AA   5
B  AB   27
B  AC   90
 
Paal

Ny versjon av Yahoo! Messenger 
Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom

Re: Setting wait_timeout in my.cnf?

[Nils Valentin]

Hi Maarten,

I havent tried it recently but substitute any underbar (_) with a hiven (-)
when doing a setting in the my.cnf.


--
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

On Tuesday 11 November 2003 20:22, Hoeven, Maarten van der wrote:
> correction: section shoud be read as [mysqld]
>
> -Original Message-
> From: Hoeven, Maarten van der
> Sent: Tuesday, November 11, 2003 12:02 PM
> To: [EMAIL PROTECTED]
> Subject: Setting wait_timeout in my.cnf?

--
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

> Hi all,
>
> gosh, I must be overlooking something obvious. Running mysql 4.0.16. I
> want to set the wait_timeout in my.cnf (section [mysql]) like:
>
> wait_timeout = 300
>
> also tried the depricated
>
> set-variable = wait_timeout=300
>
> Both are not working; still default to 28800. Sure, I stopped and
> started the server.
>
>
> However, when I set the variable in the mysql-client with:
>
> set wait_timeout=30;
>
> the variable wait_timeout is changed!
>
>
> What am I overlooking? I really want to set this in my config-file,
> rather that setting it manually in the client... Any hints?
>
>
> PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8
>
>
> Maarten
> --
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> --
> Zie ook/see also: http://www.knmi.nl/maildisclaimer.html

---

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MaxDB Release?

[Nils Valentin]

Hello Iago,

I dont know anything, but I remember that a while back (3 months ago) some
people mentioned that the 15th November should be a remarkable day for a
MySQL announcement.

Wether this is true or not I dont know.

Best regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

On Tuesday 11 November 2003 19:23, Iago Sineiro wrote:
> Hi.
>
> Some curiosity. Does anyone know when the release of MySQL MaxDB 7.5
> version will come out?
>
> In the MySQL web page at http://www.mysql.com/sapdb/timeline.html says that
> the release was planned for 15 of October but now we are at 11 of November
> and nothing has been released. Is it delayed until MySQL 4.1.1 is released?
>
> Iago.

---

-- 
kind regards

Nils Valentin
Tokyo/Japan

http://www.be-known-online.com/mysql/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Setting wait_timeout in my.cnf?

[Hoeven, Maarten van der]

correction: section shoud be read as [mysqld]

-Original Message-
From: Hoeven, Maarten van der 
Sent: Tuesday, November 11, 2003 12:02 PM
To: [EMAIL PROTECTED]
Subject: Setting wait_timeout in my.cnf?


Hi all,

gosh, I must be overlooking something obvious. Running mysql 4.0.16. I
want to set the wait_timeout in my.cnf (section [mysql]) like:

wait_timeout = 300

also tried the depricated

set-variable = wait_timeout=300

Both are not working; still default to 28800. Sure, I stopped and
started the server.


However, when I set the variable in the mysql-client with:

set wait_timeout=30;

the variable wait_timeout is changed!


What am I overlooking? I really want to set this in my config-file,
rather that setting it manually in the client... Any hints?


PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8


Maarten
--
Zie ook/see also: http://www.knmi.nl/maildisclaimer.html 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Zie ook/see also: http://www.knmi.nl/maildisclaimer.html 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MaxDB Release?

[Iago Sineiro]

Hi.

Some curiosity. Does anyone know when the release of MySQL MaxDB 7.5 version
will come out?

In the MySQL web page at http://www.mysql.com/sapdb/timeline.html says that
the release was planned for 15 of October but now we are at 11 of November
and nothing has been released. Is it delayed until MySQL 4.1.1 is released?

Iago.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Some help with a complex query

[Elisenda]

I have a query which tries to select different fields from 5 different
tables. 

In WHERE part I have write all the conditions and relationships. Perhaps two
many.

The main table for me is FASE. From this table I try to find all the other
information.

I guess I'm doing something wrong but I don't know what.

SELECT 

CE.CE_CENTRO, 
CE.CE_DOMICILIO, 
CE.CE_CP,
CE.CE_POBLACION, 
CE.CE_PROV, 
PP.PP_CONTACTO, 
PP.PP_CARGO, 
CA.CA_HORARIO,
AU.AU_A_M, 
AU.AU_A_F, 
FASE.PR_DATE_VISITA_1

FROM AU, CA, CE,FASE,PP

WHERE

FASE.SQL_ID_PY='P081' AND
FASE.PR_FLAG= '1' AND
CA.CA_ID_IDIOMA_A= '6' AND
AU.AU_NIVEL= '13.14' AND
AU.AU_Aula= fase.AU_PR_Aula AND
AU.AU_ID_CE = CA.CA_ID_CE AND
CE.CE_ID_CE = CA.CA_ID_CE AND
CE.CE_ID_CE = Fase.SQL_ID_CE AND
CE.CE_ID_CE = PP.PP_ID_CE AND
Fase.PR_PP_ID_Coord = PP.PP_ID_PP


Thank you fro your help in advanced.

Eli


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Setting wait_timeout in my.cnf?

[Hoeven, Maarten van der]

Hi all,

gosh, I must be overlooking something obvious. Running mysql 4.0.16. I
want to set the wait_timeout in my.cnf (section [mysql]) like:

wait_timeout = 300

also tried the depricated

set-variable = wait_timeout=300

Both are not working; still default to 28800. Sure, I stopped and
started the server.


However, when I set the variable in the mysql-client with:

set wait_timeout=30;

the variable wait_timeout is changed!


What am I overlooking? I really want to set this in my config-file,
rather that setting it manually in the client... Any hints?


PS. Linux Redhat 9 out of the Box, kernel 2.4.20-8


Maarten
--
Zie ook/see also: http://www.knmi.nl/maildisclaimer.html 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

self-join, group by, single SQL query

[Robert Citek]

Hello all,

Is there a way to do a self-join and a group by in a single SQL query?

For example, I have an PurchaseOrder table (see below) and would like 
to know what was the most recent order-number for each customer and the 
amount of that order.  So far, I have been able to do this in two steps:
  1) create a temporary table of the group by data
  2) join the new table with the original

Thanks in advance for any pointers or URLs to on-line docs explaining 
how to do this.

Regards,
- Robert
-

$ cat <
create temporary table PO1 select max(OrderNo) as "OrderNo", CustNo 
from PO group by CustNo;
select PO1.OrderNo, PO1.CustNo, PO.Amnt from PO1, PO where PO1.OrderNO 
= PO.OrderNO and PO1.CustNo = PO.CustNo;
!

+-++--+
| OrderNo | CustNo | Amnt |
+-++--+
| 101 |   1001 |   20 |
| 102 |   1001 |   25 |
| 103 |   1001 |   40 |
| 101 |   1002 |   20 |
| 102 |   1002 |   30 |
| 101 |   1003 |   25 |
| 102 |   1003 |   55 |
+-++--+
+-++--+
| OrderNo | CustNo | Amnt |
+-++--+
| 103 |   1001 |   40 |
| 102 |   1002 |   30 |
| 102 |   1003 |   55 |
+-++--+




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with query comparing to a DATE on Linux OpenOffice

[Marc]

On Tue, 2003-11-11 at 00:15, Alaios wrote:
> what a front-end is? And what OO1.1.0 provides?

MySQL is a database server and a front-end is another program that you
use to get to that server and use the database. OpenOffice is a complete
office software package (word processor, spreadsheet, ...) that also has
a database interface. You can check it out at www.openoffice.org

-Marc

> 
> Marc <[EMAIL PROTECTED]> wrote:I'm using OpenOffice 1.1.0 as a front-end to MySQL 
> 4.0.14 on Gentoo
> Linux and can't compare a field to CURDATE(). The error is, "The field
> cannot be compared with a date". The column is formatted as a DATE, and
> I can perform the compare fine using mysql.
> 
> I realize this is probably related only to OpenOffice, and not a mysql
> problem, but I haven't gotten any answers on the OOo lists. So I'm
> hoping some kind soul on the mysql/myodbc lists might have experienced
> this and have an answer.
> 
> Thanks for any ideas.
> 
> -- 
> Marc 
-- 
Marc <[EMAIL PROTECTED]>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query question (php)

[Obantec Support1]

Hi

mysql 3.23.56 RH 8.0

I have got the sql query to work but is there a better way of writing the
syntax?

$sql = "SELECT * FROM Contacts WHERE Categories=\"$calltype\" and
BusinessCodes !=\"R\" and BusinessCodes !=\"I\" and BusinessCodes !=\"L\"
ORDER BY Company ASC";

basically i Categories (a limit number of 4 or 5 types) but exclude 'n'
number of BusinessCodes defined by 1 or 2 ASCII letters.

I tried BusinessCodes !=\"R\". !=\"I\". !=\"L\" but get Couldn't execute
query

Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Creating random data in a test table based on an existing table

[Olof Tjerngren]

How about somethine like this as a starting point:

insert into testuser (firstname,lastname) select 
u1.firstname,u2.lastname from user u1, user u2 order by rand() limit 1;

MvH,

Luis Lebron wrote:
I have an users table for a php application that I am programming. The
current users table has about 1500 records. I would like to create a test
table (i.e. users_test) with 10,000 records based on random data from the
first table (i.e. random first name combined with a random last name,
etc...). Is it possible to do this via a MySQL query? 

Luis R. Lebron
Sigmatech, Inc


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Cronjob / rights problem.

[H M Kunzmann]

>  /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user:
> '@localhost' (Using password: NO)'

You can specify all the user & host details that you want to use in the
mysqladmin command. 

mysqladmin -u john --password=mypassword

You will need to make sure that you use a user/host combination that has
access. Granting access is done through adding records into the mysql
database's tables. Depending on which type of access you intend on
granting, you will need to enter records into different tables.

-- 
Herbert Michael Kunzmann
Binary Chaos Magician
http://www.dreamstroke.com


signature.asc
Description: This is a digitally signed message part

Re: SQL [Q] how to migrate 4.0 -> 4.1

[Andrey Kotrekhov]

Добрый день.

> Andrey Kotrekhov <[EMAIL PROTECTED]> wrote:
> > Hi, All.
> > Other question is:
> > Can I create case sensitive field with right ordering of national
> > characters.
> >
>
> Take a look at collation name: "ci" at the end of the collation name means case 
> insensitive, "cs" - case sensitive, "bin" - binary:
>   http://www.mysql.com/doc/en/Charset-MySQL.html
>

Thank you. But what is about binary fields?
When I start mysqld-4.0.. field in table are char(x) binary.
But when I start mysql-4.1.0 in the same table the same field is not
binary.
Is this bug?
Or is there right way to restore binary flag on field.

>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
>  / /|_/ / // /\ \/ /_/ / /__   [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]
>
>

Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
тел. +380 562 34-00-44

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Nuke_

[H M Kunzmann]

> Looking at our dbase I noticed that a number of tables have been added
> 
> The all start nuke_
> 
> I have no idea who added them, I can only assume my ISP added them, do
> they have a purpose, can I remove them etc

The nuke_ tables are created automatically by a portal/conent management
system called phpnuke. There are some variants and spin offs of this
package which may or may not use the same table/database naming
convention.

Removing the tables would break the application. If it is no longer
used/ you don't use it, you are probably safe in deleting them.

-- 
Herbert Michael Kunzmann
Binary Chaos Magician
http://www.dreamstroke.com


signature.asc
Description: This is a digitally signed message part

RE: C API: undefined reference

[Nick Gaugler]

http://www.mysql.com/doc/en/mysql_connect.html

This function is deprecated. It is preferable to use
mysql_real_connect() instead.



#include
#include

int main(void) {
MYSQL mysql;

if(mysql_init(&mysql) == NULL) { 
fprintf(stderr,"Unable to initlize MySQL structure.\n");
return(1);
}

if(mysql_real_connect(&mysql,"127.0.0.1","user","password", "db", 0,
NULL, 0) == NULL) { 
fprintf(stderr,"Unable to connect to MySQL: %s\n",
mysql_error(&mysql));
return(1);
}

return(0);
}


mybox:/home/nickgsu > gcc seeLog.c -o seeLog -I/usr/local/mysql/include
-L/usr/local/mysql/lib -lmysqlclient -lz
mybox:/home/nickgsu > ./seeLog 
Unable to connect to MySQL: Access denied for user: '[EMAIL PROTECTED]'
(Using password: YES)




Good luck,


nickg


-Original Message-
From: hAj [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 10, 2003 4:03 PM
To: [EMAIL PROTECTED]
Subject: C API: undefined reference

Hello MySQL pros worldwide,


~/www ->cat seeLog.c
#define USE_OLD_FUNCTIONS
#include 
#include 

int main() {
MYSQL mysql;
MYSQL *mysqldb = NULL;

mysqldb = mysql_connect(&mysql, "geneofcube.net", "USERID",
"PASSWORD");

return 0;
}
~/www ->gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient
/tmp/ccEj3tmv.o: In function `main':
/tmp/ccEj3tmv.o(.text+0x2a): undefined reference to `mysql_connect'
collect2: ld returned 1 exit status
~/www ->


As shown above, I'm having a problem getting rid of a compilation error
(undefined reference) coming out with a very simple c code (seeLog.c)
which
I wrote for a testing purpose.
Got no I idea what I'm doing wrong or missing here.

I'd appreciate any of your suggestions.


Best,
hAj


-- 
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: running 4.0.15 side-by-side with 4.1alpha

[Adam Hardy]

On 11/11/2003 09:21 AM Egor Egorov wrote:
Adam Hardy <[EMAIL PROTECTED]> wrote:
I am sure I say someone asking a similar question on the list before but 
I can't find anything in the archives, nor anything relevant in the 
mysql docs.
You can find info how to run multiple MySQL servers at:
http://www.mysql.com/doc/en/Multiple_servers.html

Excellent, thanks Egor. I didn't see it there before in the admin 
section. I was looking in Installation.

Regards
Adam
--
mySQL 4.1.0 + connector/J 3.0.9 + j2sdk 1.4.2 + Linux 2.4.20 RH9
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: running 4.0.15 side-by-side with 4.1alpha

[Egor Egorov]

Adam Hardy <[EMAIL PROTECTED]> wrote:
> I am sure I say someone asking a similar question on the list before but 
> I can't find anything in the archives, nor anything relevant in the 
> mysql docs.

You can find info how to run multiple MySQL servers at:
http://www.mysql.com/doc/en/Multiple_servers.html

> 
> Is it possible to have two versions of mysql running side-by-side on 
> linux and if so, are there any installation instructions anywhere? I 
> imagine that things such as port numbers, start-up daemons etc have to 
> be configured differently from the standard install.




-- 
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]

MySQL on Windows 2000 Server

[Kocsis, Bela]

Hi to all,


  I'm implementing a database in MySQL runnig on Windows 2000 Server. It's
working fine. I'm wondering what kind of rights do need MySQL Server to run
on Windows 2000. Now its runnig as service with administrator rights. But I
am affraid that seriosly effect the security of this server.


 Thanks in advence,


   Bela Kocsis 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

remote and local access problem

[Jan Emmer]

I have MySQL 3.23.52 server running on 192.168.1.1.
This behavior seems to be strange for me:
In mysql client runned on 192.168.1.1 I set these commands:

CREATE DATABASE pokus1;
GRANT ALL PRIVILEGES ON pokus1.* TO [EMAIL PROTECTED] IDENTIFIED
BY 'heslo'; GRANT ALL PRIVILEGES ON pokus1.* TO pokus1@'%'
IDENTIFIED BY 'heslo';

Then I leave the client and I try to call mysql client under new
created user:

mysql -u pokus1 -pheslo pokus1

It connects succesfully to server.

So I try this:

mysql -h 192.168.1.1 -u pokus1 -pheslo pokus1

it gives:

ERROR 2013: Lost connection to MySQL server during query

but from another machine the same command connects.

How to set up GRANT permissions to be able to connect with IP
both locally and remotely?

Thanx in advance

P.S.: this works on Linux RedHat 8.0 and IRIX 6.5.x (but on SGI I
have some 4.x version of MySQL). On AIX 4.3 with MySQL 3.23.51
the mysql command with -h option connects.


-- 
Potrebujete vice prostoru pro vase stranky?
Ptejte se na http://sluzby.volny.cz/cs/product/ftp_paid



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Retrieving the system type that MySQL is running on.

[Matt W]

Hi Nathan,

I don't think there's a way if MySQL is on a separate machine, as I'm
only aware of checking in PHP.

Hmm, maybe you could create a dummy table and see if it's case-sensitive
or not? He he.

Just curious, why would you need to know what OS MySQL is on?


Matt


- Original Message -
From: "Nathan Jones"
Sent: Monday, November 10, 2003 8:02 PM
Subject: Retrieving the system type that MySQL is running on.


> I'm relatively new to MySQL, and I'm writing a PHP script that doesn't
know
> what kind of system the MySQL database is on.  The script and database
> don't have to be on the same system, so I need a way to determine what
kind
> of system (*nix, Win, etc.) MySQL is running on.  Is there a "fool
proof"
> query to determine this?  If so, what is it?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: In which version of MySQL was ADD COMMENT added

[Matt W]

Hi Karam,

Comments per *column* were added in 4.1.0.
Comments per *table* were added in 3.23.0.

http://www.mysql.com/doc/en/News-4.1.0.html
http://www.mysql.com/doc/en/News-3.23.0.html

Hope that helps.


Matt


- Original Message - 
From: "Karam Chand"
Sent: Monday, November 10, 2003 11:42 PM
Subject: In which version of MySQL was ADD COMMENT added


> Hello
> 
> Can somebody tell me the exact version of MySQL from
> which the functionality of adding COMMENTS to columsn
> was added.
> 
> Karam


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]