Problems installing MySQL 4.1 under Fedora Core 3
I have a Pentium III with Fedora Core 3 notebook and I need to install
MySQL 4.1 on it.
There was no mysql installed before so I've downloaded the packages and
did what I usually do to install
the packages. They install great. But when I try to run MySQL I have
problems as it does not run.
It tries for a while and then quits.
I am using the 4.1.11-0.i386 version.
I have checked the persmissions under /var/lib/mysql and they were user
mysql group root. So I've changed to
the MySQL group by using chgrp -R mysql /var/lib/mysql
I've created an /etc/my.cnf file by copying the standard
/usr/share/doc/packages/MySQL-server/my-medium.cnf
It still does not work.
My /var/log/messages file shows the following:
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:
denied { append } for pid=5693 exe=/usr/sbin/mysqld
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:
denied { append } for pid=5693 exe=/usr/sbin/mysqld
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.267:0): avc:
denied { write } for pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5
ino=653096 scontext=root:system_r:mysqld_t
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.269:0): avc:
denied { write } for pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5
ino=653096 scontext=root:system_r:mysqld_t
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:54:10 presario2700 lsb_log_message: failed
I have no idea what might be wrong.
Can anyone help me out?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re : Problems installing MySQL 4.1 under Fedora Core 3
It only shows this: 050413 23:53:35 mysqld started 050413 23:53:35 mysqld ended Nothing conclusive. prasanna a wrote: Hi please cat the below error file and see /var/lib/mysql/presario2700.err __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems installing MySQL 4.1 under Fedora Core 3
I have a Pentium III with Fedora Core 3 notebook and I need to install
MySQL 4.1 on it.
There was no mysql installed before so I've downloaded the packages and
did what I usually do to install
the packages. They install great. But when I try to run MySQL I have
problems as it does not run.
It tries for a while and then quits.
I am using the 4.1.11-0.i386 version.
I have checked the persmissions under /var/lib/mysql and they were user
mysql group root. So I've changed to
the MySQL group by using chgrp -R mysql /var/lib/mysql
I've created an /etc/my.cnf file by copying the standard
/usr/share/doc/packages/MySQL-server/my-medium.cnf
It still does not work.
My /var/log/messages file shows the following:
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:
denied { append } for pid=5693 exe=/usr/sbin/mysqld
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:
denied { append } for pid=5693 exe=/usr/sbin/mysqld
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.267:0): avc:
denied { write } for pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5
ino=653096 scontext=root:system_r:mysqld_t
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.269:0): avc:
denied { write } for pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5
ino=653096 scontext=root:system_r:mysqld_t
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:54:10 presario2700 lsb_log_message: failed
I have no idea what might be wrong.
Can anyone help me out?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems installing MySQL 4.1 under Fedora Core 3
I am pretty sure hat is due to the SELinux policy being set to restrictive. For
the brute force fix try editing /etc/selinux/config and set the variable
SELINUX=Permissive. There is a more subtle approach that maintains beeter
security explained under the SELinux topic in the Fedora doc, sorry I don't
have the link handy. Basically you can realx the policy via the targetted
settings.
Good luck
//jjs
-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED]
Sent: Wed 4/13/2005 10:56 PM
To: mysql@lists.mysql.com
Subject: Problems installing MySQL 4.1 under Fedora Core 3
I have a Pentium III with Fedora Core 3 notebook and I need to install
MySQL 4.1 on it.
There was no mysql installed before so I've downloaded the packages and
did what I usually do to install
the packages. They install great. But when I try to run MySQL I have
problems as it does not run.
It tries for a while and then quits.
I am using the 4.1.11-0.i386 version.
I have checked the persmissions under /var/lib/mysql and they were user
mysql group root. So I've changed to
the MySQL group by using chgrp -R mysql /var/lib/mysql
I've created an /etc/my.cnf file by copying the standard
/usr/share/doc/packages/MySQL-server/my-medium.cnf
It still does not work.
My /var/log/messages file shows the following:
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:
denied { append } for pid=5693 exe=/usr/sbin/mysqld
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:
denied { append } for pid=5693 exe=/usr/sbin/mysqld
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.267:0): avc:
denied { write } for pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5
ino=653096 scontext=root:system_r:mysqld_t
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.269:0): avc:
denied { write } for pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5
ino=653096 scontext=root:system_r:mysqld_t
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:54:10 presario2700 lsb_log_message: failed
I have no idea what might be wrong.
Can anyone help me out?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Crash Diagnosis
Hello. What is in error log? See: http://dev.mysql.com/doc/mysql/en/error-log.html http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html Jason Johnson [EMAIL PROTECTED] wrote: I am running MySQL 4.1.8 on Windows 2000. Sporadically, the service will stop. It does not seem to be in relationship with load on the service itself, or the box. At seemingly random intervals, the service will go kaput for no readily apparent reason. My question to you isn't hey, what's wrong? but more about how I go diagnosing the problem. Are there any tools, utilities, logs I should be inspecting that I may not be aware of? Any help would be much appreciated. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
very complicated queries (for me at least).
It's official. I need help ;)
Let's do the table structures quickly.
mysql DESCRIBE FlightData;
+---+--+--+-+-++
| Field | Type | Null | Key | Default
| Extra |
+---+--+--+-+-++
| EntryID | int(10) unsigned | | PRI | NULL
| auto_increment |
| Network | enum('I','V')| | | V
||
| TimeStamp | timestamp| YES | | -00-00
00:00:00 ||
| Tracker | varchar(38) | YES | | NULL
||
| PilotCallSign | varchar(20) | | MUL |
||
| PilotCID | mediumint(6) unsigned| | MUL | 0
||
| PilotRealName | varchar(50) | | |
||
| CurHeading| tinyint(3) unsigned zerofill | | | 000
||
| CurAlt| smallint(5) unsigned | | | 0
||
| CurGS | smallint(4) unsigned | | | 0
||
| Plane | varchar(20) | | |
||
| Transponder | smallint(4) unsigned | | | 0
||
| QNHHg | varchar(5) | | |
||
| QNHMb | varchar(8) | | |
||
| Enroute | time | | | 00:00:00
||
| Feul | time | | | 00:00:00
||
| AirportDep| varchar(4) | | |
||
| AirportDes| varchar(4) | | |
||
| AirportAlt| varchar(4) | | |
||
| PositionCur | varchar(22) | | |
||
| PositionDep | varchar(22) | | |
||
| PositionDes | varchar(22) | | |
||
| PLGS | smallint(4) unsigned | | | 0
||
| PLAlt | varchar(10) | | |
||
| PLDepTime | varchar(4) | YES | |
||
| PLFlightType | enum('','I','S','V') | | |
||
| FlightPlan| text | | |
||
| Remarks | varchar(150) | | |
||
| ActDepTime| time | | | 00:00:00
||
| Logon | time | | | 00:00:00
||
+---+--+--+-+-++
mysql DESCRIBE Airports;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| EntryID | smallint(5) unsigned | | PRI | NULL| auto_increment |
| CountryID | smallint(5) unsigned | | MUL | 0 ||
| Name | varchar(50) | | MUL | ||
| IATA | char(3) | | MUL | ||
| ICAO | varchar(4) | | MUL | ||
| Long | varchar(50) | | | ||
| Lat | varchar(50) | | | ||
| Alt | int(11) | | | 0 ||
| City | varchar(200) | | | ||
+---+--+--+-+-++
9 rows in set (0.00 sec)
Sample data.
mysql SELECT * FROM FlightData LIMIT 1\G
*** 1. row ***
EntryID: 3471
Network: V
TimeStamp: 2005-04-14 07:27:42
Tracker: df0363ea-fe06-1027-b1dd-00e01884a424
PilotCallSign: AAL133
PilotCID:
PilotRealName: x
CurHeading: 124
CurAlt: 34887
CurGS: 469
Plane: H/DC10/W
Transponder: 7207
QNHHg: 29.80
QNHMb: 1009.14
Enroute: 06:00:00
Feul: 10:00:00
AirportDep: KLAX
AirportDes: MMMX
AirportAlt:
PositionCur: 32.06073/-114.70269
PositionDep: 33.942536/-118.408075
PositionDes: 19.435278/-099.07
PLGS: 480
PLAlt: 35000
PLDepTime: 640
PLFlightType: I
FlightPlan: HOLTZ5 JLI ASUTA ALTAR PPE ZCL GABTU
Remarks: SEL/CJ-AG /V/
ActDepTime: 06:40:00
Logon: 06:32:49
mysql SELECT * FROM Airports LIMIT 1\G
***
Re: book advice
Hi David; I can recommend any Paul Dubois books but especially the Certificate study guide which basically is free if you consider taking the exam. The book comes with a 50$ voucher for the exam if I remmember that correctly + it is one of the best books to get up and running as fast as possible. Best regards Nils Valentin Tokyo / Japan www.be-known-online.com Hi, I'm a newbie and looking for a book to help me learn mysql. I have come across a book called Beginning MySQL by Robert Sheldon and Geoff Moes. Can anyone recommend this book? Or, if not, what book can you recommend for a newbie. David -- 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: book advice
Hi, I'm a newbie and looking for a book to help me learn mysql. I have come across a book called Beginning MySQL by Robert Sheldon and Geoff Moes. Can anyone recommend this book? Or, if not, what book can you recommend for a newbie. If there is a big college near you they might have IT courses for working professionals, I mean introductory courses on database design and SQL. Then good MYSQL books will just crystallize for you when read them. My 2 cents... Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update ... where col is null problem.
Hi... I have problem with update query. Select query with the same where is OK. mysql select AcctStartTime,AcctSessionTime from radacct where AcctStopTime is null; +-+-+ | AcctStartTime | AcctSessionTime | +-+-+ | 2005-04-11 12:16:19 | 191154 | | 2005-04-13 06:51:59 | 37984 | | 2005-04-13 16:28:51 |3617 | | 2005-04-13 17:36:57 | 16279 | | 2005-04-13 17:37:01 | 16280 | | 2005-04-13 17:37:03 | 16279 | | 2005-04-13 22:15:41 | 0 | | 2005-04-13 22:15:56 | 0 | | 2005-04-13 22:23:55 | 44018 | | 2005-04-14 10:34:14 | 0 | +-+-+ 10 rows in set (0.00 sec) but update: mysql update radacct set AcctStopTime=null where AcctStopTime is null; Query OK, 0 rows affected (0.00 sec) I know that this update query is stupid byt it is only to show problem. this is more/less my table: mysql desc radacct; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | RadAcctId | bigint(21) | | PRI | NULL| auto_increment | | AcctStartTime | datetime| | MUL | -00-00 00:00:00 | | | AcctStopTime | datetime| | MUL | -00-00 00:00:00 | | | AcctSessionTime| int(12) | YES | | NULL| | my sql system/version: debian/mysql 4.1.11 -- Emil Wilmanski [EMAIL PROTECTED] Galeria-M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update ... where col is null problem.
I use = 0. It works for me. -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' - Original Message - From: Emil Wilmanski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, April 14, 2005 11:19 AM Subject: update ... where col is null problem. Hi... I have problem with update query. Select query with the same where is OK. mysql select AcctStartTime,AcctSessionTime from radacct where AcctStopTime is null; +-+-+ | AcctStartTime | AcctSessionTime | +-+-+ | 2005-04-11 12:16:19 | 191154 | | 2005-04-13 06:51:59 | 37984 | | 2005-04-13 16:28:51 |3617 | | 2005-04-13 17:36:57 | 16279 | | 2005-04-13 17:37:01 | 16280 | | 2005-04-13 17:37:03 | 16279 | | 2005-04-13 22:15:41 | 0 | | 2005-04-13 22:15:56 | 0 | | 2005-04-13 22:23:55 | 44018 | | 2005-04-14 10:34:14 | 0 | +-+-+ 10 rows in set (0.00 sec) but update: mysql update radacct set AcctStopTime=null where AcctStopTime is null; Query OK, 0 rows affected (0.00 sec) I know that this update query is stupid byt it is only to show problem. this is more/less my table: mysql desc radacct; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | RadAcctId | bigint(21) | | PRI | NULL| auto_increment | | AcctStartTime | datetime| | MUL | -00-00 00:00:00 | | | AcctStopTime | datetime| | MUL | -00-00 00:00:00 | | | AcctSessionTime| int(12) | YES | | NULL| | my sql system/version: debian/mysql 4.1.11 -- Emil Wilmanski [EMAIL PROTECTED] Galeria-M -- 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]
Help with a tricky/impossible query...
Hi,
In SQL you need to define the data that you want to work with:
create table z ( z int(5) not null primary key);
insert into z values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22);
If you need more values you could use a simple perl looping construct:
for ($i=0; $i = $max; $i++) {
$dbh-do( q{ insert into z set z = $i });
}
Once you have the table filled, it's easy to explode the x/y ranges by seq:
select seq, z
from wibble, z
where z between x and y
Then the rows with seq=1 are:
| seq | z |
+-++
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
The rest is as you wanted.
Regards, Paul
On 14/4/05 1:11 am, Andrew Braithwaite [EMAIL PROTECTED] wrote:
Here's what I need to do...
create table wibble(
seq int(3) auto_increment primary key,
x int(5),
y int(5)
);
insert into wibble set x=5, y=10;
insert into wibble set x=1, y=3;
insert into wibble set x=17, y=22;
mysql select * from wibble;
+-+--+--+
| seq | x| y|
+-+--+--+
| 1 |5 | 10 |
| 2 |1 |3 |
| 3 | 17 | 22 |
+-+--+--+
3 rows in set (0.09 sec)
So I want to run a query to explode the x/y ranges by seq.
The required output is:
mysql select some clever things from wibble where some clever stuff happens
here;
+-+--+
| seq | z|
+-+--+
| 1 |1 |
| 1 |2 |
| 1 |3 |
| 1 |4 |
| 1 |5 |
| 2 |1 |
| 2 |2 |
| 2 |3 |
| 3 | 17 |
| 3 | 18 |
| 3 | 19 |
| 3 | 20 |
| 3 | 21 |
| 3 | 22 |
+-+--+
14 rows in set (0.17 sec)
Can anyone help me to achieve this result?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client Side Query cache
Hi, I was wondering if there is any query cache code/lib somewhere to cache certains queries ? I'm always doing the same queries, (and the result never change, so I could spare the round-trip to the server), but caching each tine the data for it is a bit of work. Thanks, for your suggestions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE question - is it possible?
Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very complicated queries (for me at least).
I, for one, don't have the time to work my way through your entire note;
there's just too much involved to be able to spare that much time from more
urgent work.
May I suggest that you resolve the problem *indirectly* by paring the
situation down to the absolute smallest and simplest model you can that
doesn't betray the true nature of what you're doing, get that working, and
then transpose your solution to your real situation? I know it is not as
direct as solving your actual problem but if you prototype in the way I've
suggested, you've got a lot better chance of solving the problem on your own
*and* understanding it thoroughly.
Rhino
- Original Message -
From: Chris Knipe [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, April 14, 2005 4:16 AM
Subject: very complicated queries (for me at least).
It's official. I need help ;)
Let's do the table structures quickly.
mysql DESCRIBE FlightData;
+---+--+--+-+---
--++
| Field | Type | Null | Key | Default
| Extra |
+---+--+--+-+---
--++
| EntryID | int(10) unsigned | | PRI | NULL
| auto_increment |
| Network | enum('I','V')| | | V
||
| TimeStamp | timestamp| YES | | -00-00
00:00:00 ||
| Tracker | varchar(38) | YES | | NULL
||
| PilotCallSign | varchar(20) | | MUL |
||
| PilotCID | mediumint(6) unsigned| | MUL | 0
||
| PilotRealName | varchar(50) | | |
||
| CurHeading| tinyint(3) unsigned zerofill | | | 000
||
| CurAlt| smallint(5) unsigned | | | 0
||
| CurGS | smallint(4) unsigned | | | 0
||
| Plane | varchar(20) | | |
||
| Transponder | smallint(4) unsigned | | | 0
||
| QNHHg | varchar(5) | | |
||
| QNHMb | varchar(8) | | |
||
| Enroute | time | | | 00:00:00
||
| Feul | time | | | 00:00:00
||
| AirportDep| varchar(4) | | |
||
| AirportDes| varchar(4) | | |
||
| AirportAlt| varchar(4) | | |
||
| PositionCur | varchar(22) | | |
||
| PositionDep | varchar(22) | | |
||
| PositionDes | varchar(22) | | |
||
| PLGS | smallint(4) unsigned | | | 0
||
| PLAlt | varchar(10) | | |
||
| PLDepTime | varchar(4) | YES | |
||
| PLFlightType | enum('','I','S','V') | | |
||
| FlightPlan| text | | |
||
| Remarks | varchar(150) | | |
||
| ActDepTime| time | | | 00:00:00
||
| Logon | time | | | 00:00:00
||
+---+--+--+-+---
--++
mysql DESCRIBE Airports;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra
|
+---+--+--+-+-++
| EntryID | smallint(5) unsigned | | PRI | NULL| auto_increment
|
| CountryID | smallint(5) unsigned | | MUL | 0 |
|
| Name | varchar(50) | | MUL | |
|
| IATA | char(3) | | MUL | |
|
| ICAO | varchar(4) | | MUL | |
|
| Long | varchar(50) | | | |
|
| Lat | varchar(50) | | | |
|
| Alt | int(11) | | | 0 |
|
| City | varchar(200) | | | |
|
+---+--+--+-+-++
9 rows in set (0.00 sec)
Sample data.
mysql SELECT * FROM FlightData LIMIT 1\G
*** 1. row ***
EntryID: 3471
Re: book advice
David, Can anyone recommend this book? Or, if not, what book can you recommend for a newbie. I always recommend Paul Dubois' New Riders Book: * http://www.kitebird.com/mysql-book/ Paul's probably lurking on the list. I bought the 1999 edition and I still refer to it :) DSL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a tricky/impossible query...
Thanks for the idea,
Unfortunately I can't do that as the ranges involved are unknown and
will be from 1 to several billion at lease. I can't have another table
that needs to be augmented each time my ranges change.
Any other ideas?
Cheers,
Andrew
-Original Message-
From: Paul B van den Berg [mailto:[EMAIL PROTECTED]
Sent: Thu 14 April 2005 10:47
To: MySQL
Cc: Andrew Braithwaite
Subject: Help with a tricky/impossible query...
Hi,
In SQL you need to define the data that you want to work with:
create table z ( z int(5) not null primary key); insert into z values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(
17),(18),(19),(20),(21),(22);
If you need more values you could use a simple perl looping construct:
for ($i=0; $i = $max; $i++) {
$dbh-do( q{ insert into z set z = $i });
}
Once you have the table filled, it's easy to explode the x/y ranges by
seq:
select seq, z
from wibble, z
where z between x and y
Then the rows with seq=1 are:
| seq | z |
+-++
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
The rest is as you wanted.
Regards, Paul
On 14/4/05 1:11 am, Andrew Braithwaite [EMAIL PROTECTED] wrote:
Here's what I need to do...
create table wibble(
seq int(3) auto_increment primary key, x int(5), y int(5) );
insert into wibble set x=5, y=10;
insert into wibble set x=1, y=3;
insert into wibble set x=17, y=22;
mysql select * from wibble;
+-+--+--+
| seq | x| y|
+-+--+--+
| 1 |5 | 10 |
| 2 |1 |3 |
| 3 | 17 | 22 |
+-+--+--+
3 rows in set (0.09 sec)
So I want to run a query to explode the x/y ranges by seq.
The required output is:
mysql select some clever things from wibble where some clever stuff
mysql happens
here;
+-+--+
| seq | z|
+-+--+
| 1 |1 |
| 1 |2 |
| 1 |3 |
| 1 |4 |
| 1 |5 |
| 2 |1 |
| 2 |2 |
| 2 |3 |
| 3 | 17 |
| 3 | 18 |
| 3 | 19 |
| 3 | 20 |
| 3 | 21 |
| 3 | 22 |
+-+--+
14 rows in set (0.17 sec)
Can anyone help me to achieve this result?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE question - is it possible?
Micha Berdichevsky [EMAIL PROTECTED] wrote on 14/04/2005 12:53:31: Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. I think you want to do a FULLTEXT search: see http://dev.mysql.com/doc/mysql/en/fulltext-search.html This requres using a FULLTEXT index on your column and using the MATCH command. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIKE question - is it possible?
I think that if you explode your words with AND it should work in any order: SELECT * FROM table WHERE c LIKE '%word1%' AND c LIKE '%word2%' AND c LIKE '%word3%'; but there's maybe something better to do! From: Micha Berdichevsky [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: LIKE question - is it possible? Date: Thu, 14 Apr 2005 13:53:31 +0200 Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL_BIG_RESULT
learnt SQL_BIG_RESULT in the select syntax builds a temporary table on the result (if I'm right). What will the Select statement to access such temporary table look like? - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: LIKE question - is it possible?
Not sure if your query works. But what should work is SELECT * FROM table WHERE c LIKE %word1% and c LIKE %word2% and c LIKE %word3%; But might be slow ... Am Thursday 14 April 2005 13:53 schrieb Micha Berdichevsky: Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index table query structure
If for example I have a table with the 3 columns: name,age,sex I build an index table on the column 'name'. how can i get result of the other rows (i.e age and) from the main table if i 'select' a name from the main table? In short, what is the structure(or should i say syntax?) of a selected statement for an index table? - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Index table query structure
If for example I have a table with the 3 columns: name,age,sex I build an index table on the column 'name'. how can i get result of the other rows (i.e age and) from the main table if i 'select' a name from the main table? In short, what is the structure(or should i say syntax?) of a selected statement for an index table? - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
using MyAdmin to build the MySql-Having trouble setting auto-increment for primary key
I am new to MySql and I am having troubles getting the primary keys to do what I want. Here is the situation. I am building a database where there are 2 types of users that can access and change information. Because each type of user will be accessing a different part of the database, I wanted to set it where Type 1 users got a primary key UserID that was even, while Type 2 users got a UserID that was odd. I have set the auto-increment value in both registration forms to 2 but when I set the default in the type 1's to 2 and the type2's to 1, it does not work and they are both getting the same numbers for their user id. Is there something I am missing or is there a different field that I need to set to make this work right or is this something that can't be done in MySql? Any help would be appreciated. P.S. I am more familiar with MSSqlServer than I am with MySql, if that helps. I know how to do this in MSSqlServer but I am lost here. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 4/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a tricky/impossible query...
On Thu, 14 Apr 2005 11:57:50 +0100
Andrew Braithwaite [EMAIL PROTECTED] wrote:
Thanks for the idea,
Unfortunately I can't do that as the ranges involved are unknown and
will be from 1 to several billion at lease. I can't have another table
that needs to be augmented each time my ranges change.
Any other ideas?
Cheers,
Andrew
I Think you really need the second table, but you can fill it as needed:
my $x=1700;
my $y=2200;
$dbh-do( q{ insert into wibble set x=$x, y=$y });
for ($i=$x; $i =$y ; $i++) {
$dbh-do( q{ insert ignore into z set z = $i });
}
Regards, Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table types
hi list, im a newbie around here. i'v gone through some parts of the mysql documentation. however, i'd like to know the meaning of spatial, clustered and unique table types. or is it column types? - A passion till tomorrow, Opeyemi Obembe | ng.clawz.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Index table query structure
Yemi Obembe wrote:
If for example I have a table with the 3 columns: name,age,sex I
build an index table
index table ? There is no such thing.
on the column 'name'. how can i get result of
the other rows (i.e age and)
other rows? You mean other columns?
from the main table
What main table?
if i 'select' a
name from the main table? In short, what is the structure(or should i
say syntax?) of a selected statement for an index table?
There is no index table. There are only tables and indexes. Any index
is related to one or more columns in a table, but it is transparent when
it comes to the usage of the table.
Let's say your example table is named 'names'. It could be created like
this:
CREATE TABLE names (
name varchar(30) NOT NULL,
age tinyint not null,
sex enum('f','m','unknown') default 'unknown'
);
In general 'age' is not a good column, it is better to store year of
birth or date of birth. That way you don't need to know the year of data
collection to calculate the current age... but this may not be relevant
in all applications.
Lets insert a few records:
INSERT INTO names VALUES
('ken',25,'m'),
('barbie',22,'f');
Lets select something:
mysql SELECT age FROM names WHERE name = 'barbie';
+-+
| age |
+-+
| 22 |
+-+
1 row in set (0.03 sec)
Now, if you add an index on this table, _nothing_ changes in the way you
use the table. Adding an index with the ALTER TABLE statement:
mysql ALTER TABLE names ADD INDEX (name);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT age FROM names WHERE name = 'barbie';
+-+
| age |
+-+
| 22 |
+-+
1 row in set (0.03 sec)
Note that the index we created was not unique. Most tables should have
some unique column, and this column should be defined as a PRIMARY KEY.
You can also define a UNIQUE index on a column or multiple columns in
addition to the primary key, if needed. Primary keys and unique keys are
used to prevent duplicates in your tables. A non-unique index, like the
one we created above, will only increase the speed of the queries when
that particular indexed column is used in the WHERE clause. In this
case, with two rows, it has no use. It will not prevent duplicates, like
a primary key or an unique index would. So you should probably design
your table in one of the following ways, depending on your needs:
# non-unique name, multiple barbies may exist:
CREATE TABLE names (
id integer NOT NULL PRIMARY KEY,
name varchar(30) NOT NULL,
age tinyint NOT NULL ,
sex enum('f','m','unknown') NOT NULL DEFAULT 'unknown',
INDEX (name)
);
# unique name, no relations
CREATE TABLE names (
name varchar(30) NOT NULL PRIMARY KEY,
age tinyint NOT NULL ,
sex enum('f','m','unknown') NOT NULL DEFAULT 'unknown'
);
# unique name, relates to other tables (using id)
CREATE TABLE names (
id integer NOT NULL PRIMARY KEY,
name varchar(30) NOT NULL,
age tinyint NOT NULL,
sex enum('f','m','unknown') NOT NULL DEFAULT 'unknown',
UNIQUE (name)
);
Further reading:
URL: http://dev.mysql.com/doc/mysql/en/select.html
URL: http://dev.mysql.com/doc/mysql/en/create-table.html
URL: http://dev.mysql.com/doc/mysql/en/alter-table.html
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_BIG_RESULT
Yemi Obembe wrote: learnt SQL_BIG_RESULT in the select syntax builds a temporary table on the result (if I'm right). What will the Select statement to access such temporary table look like? This is an internal temporary table, the result is returned in the normal way, and the temporary table is destroyed. SQL_BIG_RESULT tells the engine the query result will be big, so the engine will not try to keep everything in memory (normally fastest), it will use a temporary table to solve the query. The result is the same, but without SQL_BIG_RESULT the engine would first try to fit the query in memory, when failing it would build the disk-based temporary table anyway, thus it would take more time. URL: http://dev.mysql.com/doc/mysql/en/select.html#id2674115 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: graphs
Perl and GD would do the trick. Take a look at how RDDtools does it! Andy -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 13, 2005 11:08 PM To: prathima rao; mysql@lists.mysql.com Subject:Re: graphs prathima rao wrote: hello, can anyone suggest if there is any software available to create graphs using mysql as database regards prathima rao My favourite has always been JpGraph - http://www.aditus.nu/jpgraph/ - it's a PHP library. You can use it on a web server or on a stand-alone PHP installation. It's not MySQL-specific, but there are some tutorials around demonstrating some graphs based on data from MySQL. I'm sure there are Perl libraries that also do graphing, but I've never been bothered to research to much - they'd have to be good to surpass JpGraph. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to spell check a DB/column?
Hello, I have been googling and asking on several forums with no luck, so I'm coming here for the ultimate answer :) eheh Is there a way to spell check a mysql column (or at least table)? Thanks in advance Ludovic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using MyAdmin to build the MySql-Having trouble setting auto-increment for primary key
Dana Terrell wrote: I am new to MySql and I am having troubles getting the primary keys to do what I want. Here is the situation. I am building a database where there are 2 types of users that can access and change information. Because each type of user will be accessing a different part of the database, I wanted to set it where Type 1 users got a primary key UserID that was even, while Type 2 users got a UserID that was odd. Add another column for user type. Encoding special meaning to certain values of an otherwise unrelated column is a bad idea. I have set the auto-increment value in both registration forms to 2 but when I set the default in the type 1's to 2 and the type2's to 1, it does not work and they are both getting the same numbers for their user id. Auto increment fields increment by one, not the default value. Is there something I am missing or is there a different field that I need to set to make this work right or is this something that can't be done in MySql? Any help would be appreciated. P.S. I am more familiar with MSSqlServer than I am with MySql, if that helps. I know how to do this in MSSqlServer but I am lost here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to spell check a DB/column?
Ludovic Coumétou wrote: Hello, I have been googling and asking on several forums with no luck, so I'm coming here for the ultimate answer :) eheh Is there a way to spell check a mysql column (or at least table)? Thanks in advance Ludovic Several. 1. Dump - spell check - reload. 2. Write a program to read - spell check - write. 3. Write a UDF that spellchecks a column. The best solution is to make sure your data is correct BEFORE inserting it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X and MySQL table corruption...
We have a large OS X MySQL deployment on multiple servers and we have experienced a range of weirdness with table corruption that I was never able to fully determine the cause for. Moving to G5 Xserves (from G5 Towers and G4 Xserves) has seen all the problems go away as if a switch were thrown. I don't have an explanation or even a root cause, but I also don't have a problem any more. The thing I see in this thread that several people are talking about is the differences and conflicts caused with OS X Server's MySQL install and the MySQL AB install... There were some issues starting with 4.0.17 that weren't fixed until 4.0.19 that could affect Mac users under certain circumstances. Apple was last I looked still deploying 4.0.18, I don't know what's current with 10.3.8, and I expect a significant version change from Apple when Tiger comes out at the end of the month. Our way of dealing with this is firstly to use MySQL AB binaries. Secondly we make sure the path includes /usr/local/mysql/bin/. Next we edit the Startup script to make sure it is launching mysqld_safe from /usr/local/mysql/bin and not from /usr/bin and finally, and this is the most important one... As root you: cd /usr/bin/ rm my* ln -s /usr/local/mysql/bin/* . This is not only replacing the Apple Binaries with the MySQL binaries, it is protecting you as you upgrade MySQL versions... Each time you upgrade MySQL versions the symlink to /usr/local/mysql/bin/ will always point to the current version of MySQL you are using. The only thing you need to watch is from time to time Apple will update their MySQL installs (snuck into a System Update), which will overwrite your symlinks with new Apple Binaries (which is why the real fix it so make sure you have the right path settings and replace the startup scripts, even if Apple overwrites your symlinks, you'll still be calling your binaries). Still, you should get in the habit of checking the files in /usr/bin/my* each time you install a System update in case Apple has updated versions. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No Priviliges and no snort DB in phpmyadmin
Hi All, I've installed phpmyadmin and have a snort database in mysql, but, it doesn't appear in phpmyadmin. I know it's there, because base is using it to display info from snort on the machine. Why wouldn't it appear in phpmyadmin. It appears with the same set up on my work test machine. What should I look for to fix this..? Also, I have no priviliges for create database. Below is my connection info. Cheers. Welcome to phpMyAdmin 2.6.1-pl3 *MySQL 4.1.10a-standard running on localhost as [EMAIL PROTECTED] Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_multi at startup on Mac OS X not working
I have successfully configured mysqld_multi to have mysql 4.1.11 and
5.0.3 beta running on the same machine:
I would like to see how you configured mysqld_multi to do that, if you could
send me the information off list I'd appreciate it.
# mysqld_multi start
# exit
% mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running
%
However, I can't get this to work at system startup time. Starting up
a single mysql server works fine, with the following
/Library/StartupItems/MySQL/MySQL script:
#!/bin/sh
. /etc/rc.common
if [ ${MYSQL:=-YES-} = -YES- ]; then
ConsoleMessage Starting MySQL database server
/usr/local/mysql/bin/mysqld_safe
fi
But if I change /usr/local/mysql/bin/mysqld_safe to
/usr/local/mysql/bin/mysqld_multi start , no servers start up. There
are also no error messages in the .err logs: the last item there is
the previous 'normal shutdown'.
Any ideas? I would think that there should be no difference between
executing mysqld_multi from a root shell and executing it at startup
time, but apparently it's not the same.
You need to be careful... There isn't a difference between running
mysqld_multi at the command line and running it inside a script - remember
what you are running at startup isn't mysqld_multi but rather this command:
/System/Library/StartupItems/MySQL/MySQL start
That script then calls mysqld_multi, or not, depending on some variables in
the script...
What happens when you run
/System/Library/StartupItems/MySQL/MySQL start
At the command prompt... I venture a guess that the results are still no
mysqls start.
Let me share my startup script with you...
#!/bin/sh
. /etc/rc.common
StartService ()
{
if [ ${MYSQL=-NO-} = -YES- ]; then
ConsoleMessage Starting MySQL
/usr/local/mysql/bin/mysqld_multi start
fi
}
StopService ()
{
/usr/bin/mysqladmin ping /dev/null 21
if [ $? -eq 0 ]; then
ConsoleMessage Stopping MySQL
/usr/local/mysql/bin/mysqladmin --socket=/tmp/mysql.sock shutdown
/usr/local/mysql/bin/mysqladmin --socket=/tmp/mysql2.sock shutdown
else
ConsoleMessage MySQL is not running
fi
}
RestartService ()
{
StopService
StartService
}
RunService $1
There are some minor differences in how mine (which is working) and yours
seem to be configured... Let's look at those... What version of OS X are you
working on? Mine is running on 10.3.8, has been running on the previous
versions of 10.3 also. The major difference I see is the test on if to start
or not... This will be important. You have:
if [ ${MYSQL:=-YES-} = -YES- ]; then
While I have:
if [ ${MYSQL=-NO-} = -YES- ]; then
I don't know why yours is different, I know that mine works, it is Apple's
script and test, I just changed the binary it executes.
The other factor here is /etc/hostconfig - it must have a line that looks
like this:
MYSQL=-YES-
If YES is actually NO or if the line is not present at all, the startup
script will not execute the script.
Actually /etc/hostconfig is what the Startup scripts use to tell it what to
start or not, if you want to bounce your server and not have mysql start
when it reboots you can edit /etc/hostconfig and set the YES to a NO for the
MYSQL=-YES- line, just be sure to change it back when you are done.
When all is said and done you don't need to restart the whole machine to see
if your script is working.. You can simply run:
/System/Library/StartupItems/MySQL/MySQL start
And you will find out if you are working.
I also have some changes in the shutdown part of the script, because I use
mysqld_multi to start it, the original use of mysqladmin to shutdown the
single instance isn't going to shutdown both instances... So I add a line
for each instance to call mysqladmin shutdown and point it to each socket
file that is configured in the my.cnf file for each instance.
Hope that helps.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Help with Match without using a subQuery
many thanks
that was exactly what I needed.
Building upon the initial question:
Is there a way to get Match statements to do partial word
searches...like %LIKE% ?
For example, if the below Match Against query with 'chris' does not
bring up anything...
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
Query the database again with 'chris*' ? Is querying the database(if
the first query fails) twice the best way to do this?
AND MATCH (
media.name, media.product
)
AGAINST (
'chris*'
)
Is there a standard way of doing this ?
thanks Shawn :)
g
On Apr 13, 2005, at 11:00 AM, [EMAIL PROTECTED] wrote:
Graham Anderson [EMAIL PROTECTED] wrote on 04/13/2005 01:46:35 PM:
Hi
I am trying to set up my Match statements to filter the result of the
main query [which works]
If 'chris' does not exist in the first MATCH statement [AND MATCH
(media.name, media.product)], then the results get a bit screwed up :(
If the first match statement finds something, then the query works
fine
What would be a better way to structure this...without using a
subquery
as I am on MYSQL 3.23
SELECT media.id, media.product AS product, media.name AS name,
Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id
AS
mediatype, mediaType.name, media.product, media.path
FROM media, artist, mediaType
WHERE media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
OR MATCH (
artist.fname, artist.lname
)
AGAINST (
'chris'
)
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
it's a parentheses issue. Your query looks like this in the where
clause
WHERE artist_ID AND mediaType_ID AND first match OR second match
Which gets evaluated like
WHERE (artist_ID AND mediaType_ID AND first match) OR second match.
Any record that matched your second match condition also satisfied your
WHERE clause. Because you wanted to match on artist_Id and mediaType_ID
plus one of the match conditions, you needed to put a set of
parentheses
around BOTH of your match conditions so that your WHERE clause looked
like:
WHERE artist_ID AND mediaType_ID AND (first match OR second match)
Here is an updated version of your original query (I also changed your
implicit inner joins to explicit ones (it's a pet peeve)):
SELECT media.id, media.product AS product
, media.name AS name
, Concat_WS( ' ', artist.fname, artist.lname ) AS artist
, mediaType.id AS mediatype
, mediaType.name
, media.product
, media.path
FROM media
INNER JOIN mediaType
ON media.mediaType_id = mediaType.id
INNER JOIN artist
ON media.artist_id = artist.id
WHERE MATCH (media.name, media.product)
AGAINST ('chris')
OR MATCH (artist.fname, artist.lname)
AGAINST ('chris')
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit Which IP Address MySQL Answers On
I'm running MySQL 3.23.58 on a Win2k server with multiple IP addresses and I'm wondering whether there is a way to limit the MySQL server to answering on just one IP address. -- A. Clausen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Help with Match without using a subQuery
Graham Anderson [EMAIL PROTECTED] wrote on 04/14/2005 11:51:58 AM:
many thanks
that was exactly what I needed.
Building upon the initial question:
Is there a way to get Match statements to do partial word
searches...like %LIKE% ?
For example, if the below Match Against query with 'chris' does not
bring up anything...
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
Query the database again with 'chris*' ? Is querying the database(if
the first query fails) twice the best way to do this?
AND MATCH (
media.name, media.product
)
AGAINST (
'chris*'
)
Is there a standard way of doing this ?
thanks Shawn :)
g
On Apr 13, 2005, at 11:00 AM, [EMAIL PROTECTED] wrote:
Graham Anderson [EMAIL PROTECTED] wrote on 04/13/2005 01:46:35 PM:
Hi
I am trying to set up my Match statements to filter the result of the
main query [which works]
If 'chris' does not exist in the first MATCH statement [AND MATCH
(media.name, media.product)], then the results get a bit screwed up
:(
If the first match statement finds something, then the query works
fine
What would be a better way to structure this...without using a
subquery
as I am on MYSQL 3.23
SELECT media.id, media.product AS product, media.name AS name,
Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id
AS
mediatype, mediaType.name, media.product, media.path
FROM media, artist, mediaType
WHERE media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
OR MATCH (
artist.fname, artist.lname
)
AGAINST (
'chris'
)
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
it's a parentheses issue. Your query looks like this in the where
clause
WHERE artist_ID AND mediaType_ID AND first match OR second match
Which gets evaluated like
WHERE (artist_ID AND mediaType_ID AND first match) OR second match.
Any record that matched your second match condition also satisfied
your
WHERE clause. Because you wanted to match on artist_Id and
mediaType_ID
plus one of the match conditions, you needed to put a set of
parentheses
around BOTH of your match conditions so that your WHERE clause looked
like:
WHERE artist_ID AND mediaType_ID AND (first match OR second match)
Here is an updated version of your original query (I also changed your
implicit inner joins to explicit ones (it's a pet peeve)):
SELECT media.id, media.product AS product
, media.name AS name
, Concat_WS( ' ', artist.fname, artist.lname ) AS artist
, mediaType.id AS mediatype
, mediaType.name
, media.product
, media.path
FROM media
INNER JOIN mediaType
ON media.mediaType_id = mediaType.id
INNER JOIN artist
ON media.artist_id = artist.id
WHERE MATCH (media.name, media.product)
AGAINST ('chris')
OR MATCH (artist.fname, artist.lname)
AGAINST ('chris')
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Sorry, according to:
http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html full text
searches are only exact match (with or without case sensitivity).
RE: Limit Which IP Address MySQL Answers On
In your my.cnf, put a bind-address directive: bind-address=xxx.xxx.xxx.xxx Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit Which IP Address MySQL Answers On
A. Clausen [EMAIL PROTECTED] wrote on 04/14/2005 12:19:05 PM: I'm running MySQL 3.23.58 on a Win2k server with multiple IP addresses and I'm wondering whether there is a way to limit the MySQL server to answering on just one IP address. -- A. Clausen Yes. http://dev.mysql.com/doc/mysql/en/server-options.html You want to use the bind-address option. You can use it on the command line or include it in the config file. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MyISAM and Dirty Reads
Sounds like this is what you want: http://dev.mysql.com/doc/mysql/en/handler.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 13 Apr 2005, Homam S.A. wrote: Is there a way to use dirty reads (that acquire no read locks on the table) with MyISAM tables? I want to avoid having the read requests queuing up while the table is updated, and I can tolerate a small margin of inconsistency for the sake of throughput. So far I found only information about scheduling cues (e.g. LOW_PRIORITY, DELAYED, etc) and using table handlers, but even with these cue, you still have the potential of queuing up reads or starving updates. For example, in MS SQL Server, you can either give a per-query lock hint or use a read-uncommitted transaction isolation level. MyISAM doesn't have a SET TRANSACTION READ UNCOMMITTED equivalent to InnoDB, and the SELECT statement doesn't have lock hints like (NOLOCK). So is there a way to allow reads to go through when the table is updated, or allow updates to proceed without waiting for prending reads to finish up? Thanks, Homam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Egg vs chicken kind question
Hi all I will first set out what I need and then why I need it. Since I'm currently implementing I can still change stuff. I have a product input form that stores the information in a table. The product can be assigned different categories and what categories it is assigned is saved in the product_categorized table by using the product key AND the category key. This tecnhique is done to associate pictures and pages with the product as well. So products table -id categories table -id pages table -id pictures table -id product_categorized -id -product id -category id product_pages -id -page_id product_pictures -id -picture_id So far so good but there is one catch. How to do this when there is no product id yet? That is when the user clicks on the link add new product the come to the form which has amongs others, a link to attach page, and attach product. I thank you in advance for your time and answers. With kind regards Andy Pieters -- Registered Linux User Number 379093 -- ---BEGIN GEEK CODE BLOCK- Version: 3.12 GAT/O/CM d- s:+ a- C UL P+ L+++ E--- W+++ N++ o+ K w--- O+++ M- V PS+ PE++ Y+ PGP+++ t+ 5-- X R !tv b DI+++ D++ G e-- h+ r--- y+ -- ---END GEEK CODE BLOCK-- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- pgpncdHf1mcAa.pgp Description: PGP signature
Saving current row value before it is changed
Hello, I am currently looking at moving some of my FoxPro tables to a MySQL implementation and I am hoping someone can help me with how to do the following. In my current code, whenever anyone makes a change to a row the original value of the row is written to a history table with the same name with an x appended to it. In addition the x table has a column to name the person who made the change and a timestamp of when it was made. I do this so I can go back if an account appears to have paid out too much so I can see what the value of the particular row was when a transaction was processed. Since I am using Visual Basic and DAO/ADO right now this basically required me to create a replacement to the .Update method that first writes the data to the x table and then updates the live table. The drawback is that I can't use a sql Update call because I don't have any control over the results. When I saw that MySQL was going to support triggers I thought this would solve my problem since I could put in a before update and a before delete trigger and save the old data to another table. However, after reading more closely I see that you can't do any table access from within the stored procedure so that will not work. Is there any way to solve something like this in MySQL? It just seems like something that has to have been encountered by other people. Thanks, Darrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Still having problems with MySQL 4.1 on Fedora Core 3
Hello all, I now have disabled my SELinux and so it should work. However it still does not work. I have a bunch of errors now on my .err file that I am copying bellow. I've tried to create the tables again by issuing mysql_install_db and then tried to run the daemon. Is there any ideias on how to fix this? Thanks in advance: errors from .err file: 050414 11:19:19 mysqld started 050414 11:19:19 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050414 11:19:20 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050414 11:19:21 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050414 11:19:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050414 11:19:23 InnoDB: Started; log sequence number 0 0 050414 11:19:24 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050414 11:19:24 mysqld ended 050414 11:21:15 mysqld started 050414 11:21:15 [Warning] Asked for 196608 thread stack, but got 126976 050414 11:21:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050414 11:21:15 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43684 050414 11:21:15 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.01 050414 11:21:15 InnoDB: Flushing modified pages from the buffer pool... 050414 11:21:15 InnoDB: Started; log sequence number 0 43684 050414 11:21:15 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:21:15 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:21:15 mysqld ended 050414 11:23:49 mysqld started 050414 11:23:49 [Warning] Asked for 196608 thread stack, but got 126976 050414 11:23:49 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050414 11:23:49 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43684. InnoDB: Doing recovery: scanned up to log sequence number 0 43724 InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.04 050414 11:23:49 InnoDB: Flushing modified pages from the buffer pool... 050414 11:23:49 InnoDB: Started; log sequence number 0 43724 050414 11:23:49 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Still having problems with MySQL 4.1 on Fedora Core 3
I am running MySQL-server-4.1.8-0 without problem on FC3. This could also be hardware related. Joey -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 1:24 PM To: Mysql List Subject: Still having problems with MySQL 4.1 on Fedora Core 3 Hello all, I now have disabled my SELinux and so it should work. However it still does not work. I have a bunch of errors now on my .err file that I am copying bellow. I've tried to create the tables again by issuing mysql_install_db and then tried to run the daemon. Is there any ideias on how to fix this? Thanks in advance: errors from .err file: 050414 11:19:19 mysqld started 050414 11:19:19 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050414 11:19:20 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050414 11:19:21 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050414 11:19:22 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050414 11:19:23 InnoDB: Started; log sequence number 0 0 050414 11:19:24 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050414 11:19:24 mysqld ended 050414 11:21:15 mysqld started 050414 11:21:15 [Warning] Asked for 196608 thread stack, but got 126976 050414 11:21:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050414 11:21:15 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43684 050414 11:21:15 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.01 050414 11:21:15 InnoDB: Flushing modified pages from the buffer pool... 050414 11:21:15 InnoDB: Started; log sequence number 0 43684 050414 11:21:15 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:21:15 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:21:15 mysqld ended 050414 11:23:49 mysqld started 050414 11:23:49 [Warning] Asked for 196608 thread stack, but got 126976 050414 11:23:49 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050414 11:23:49 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43684. InnoDB: Doing recovery: scanned up to log sequence number 0 43724 InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.04 050414 11:23:49 InnoDB: Flushing modified pages from the buffer pool... 050414 11:23:49 InnoDB: Started; log sequence number 0 43724 050414 11:23:49 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 mysqld ended -- 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]
Query Performance
Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! -- Fernando Henrique Giorgetti [EMAIL PROTECTED] Departamento de Tecnologia http://www.gruponet.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Performance
From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED] Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! The first thing I'd do is index the `result` field, as you're checking against it in the WHERE clause. However, if I remember indexing behavior correctly, that won't help if you only have a a few unique values in that column. Give it a shot, though, I imagine it'd definitely help. If that doesn't drastically improve it, I'd also look into a way around performing the date and time functions in the query. I don't know if that's possible, but depending on what this is feeding to (most likely PHP or Perl), it may be quicker to do those calculations in the wrapping script (if there is one, that is). HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Performance
From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED] Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! Oh, I'm sorry. I read your CREATE statement too quickly the first time and didn't notice that the `time_result` index was across both `time` and `result`. In that case, indexing `result` separately may not help at all. Might be worth a shot, though, if you have the disk space and time to play around with it. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design question
Hello, I have two questions: I would like to know whether I am violating the principle of atomicity in doing the following: I have a form which has a field with the following options: Choice1, choice2 and choice3. Each of the above choices have two further subchoices : subChoice1, subchoice2. so in my form I have a pull-down list and these are the choices I have: choice1-subchoice1 choice1-subchoice2 choice2-subchoice1 and so on. Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Thank you. -- -Mahmoud Badreddine
Re: Egg vs chicken kind question
Andy Pieters [EMAIL PROTECTED] wrote on 04/14/2005 12:55:20 PM: Hi all I will first set out what I need and then why I need it. Since I'm currently implementing I can still change stuff. I have a product input form that stores the information in a table. The product can be assigned different categories and what categories it is assigned is saved in the product_categorized table by using the product key AND the category key. This tecnhique is done to associate pictures and pages with the product as well. So products table -id categories table -id pages table -id pictures table -id product_categorized -id -product id -category id product_pages -id -page_id product_pictures -id -picture_id So far so good but there is one catch. How to do this when there is no product id yet? That is when the user clicks on the link add new product the come to the form which has amongs others, a link to attach page, and attach product. I thank you in advance for your time and answers. With kind regards Andy Pieters You have to disable those links (or just not present them as options) until the user has filled in enough information to create a valid Product record. Otherwise you will end up with orphaned photos and pages. It's just as you think, you have to create a Product before you can create it's associated elements. This is something you should be able to control in your client-side code. Once the user has filled in enough information to create a Product, you can enable the links to add pages or photos. When they click one of those links while on the form to create a Product, you first process the information they have entered and make a new Product record then you take them to the appropriate other page. The only difference between those buttons and the save button is which page the user winds up on after you process the click. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Saving current row value before it is changed
It sounds like you are looking to do an INSERT SELECT. http://dev.mysql.com/doc/mysql/en/insert-select.html It allows you to insert into one table based on a select from another table. That will take care of creating your history. You then need to do an update on the main table. It's still a two step process that you have to do in your code, but it should accomplish what you are trying to do. On Apr 14, 2005, at 1:24 PM, Darrell A. Sullivan, II wrote: Hello, I am currently looking at moving some of my FoxPro tables to a MySQL implementation and I am hoping someone can help me with how to do the following. In my current code, whenever anyone makes a change to a row the original value of the row is written to a history table with the same name with an x appended to it. In addition the x table has a column to name the person who made the change and a timestamp of when it was made. I do this so I can go back if an account appears to have paid out too much so I can see what the value of the particular row was when a transaction was processed. Since I am using Visual Basic and DAO/ADO right now this basically required me to create a replacement to the .Update method that first writes the data to the x table and then updates the live table. The drawback is that I can't use a sql Update call because I don't have any control over the results. When I saw that MySQL was going to support triggers I thought this would solve my problem since I could put in a before update and a before delete trigger and save the old data to another table. However, after reading more closely I see that you can't do any table access from within the stored procedure so that will not work. Is there any way to solve something like this in MySQL? It just seems like something that has to have been encountered by other people. Thanks, Darrell -- 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]
Re: Egg vs chicken kind question [solved]
On Thursday 14 April 2005 20:52, [EMAIL PROTECTED] wrote: You have to disable those links (or just not present them as options) until the user has filled in enough information to create a valid Product record. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thank you Andy -- Registered Linux User Number 379093 -- ---BEGIN GEEK CODE BLOCK- Version: 3.12 GAT/O/CM d- s:+ a- C UL P+ L+++ E--- W+++ N++ o+ K w--- O+++ M- V PS+ PE++ Y+ PGP+++ t+ 5-- X R !tv b DI+++ D++ G e-- h+ r--- y+ -- ---END GEEK CODE BLOCK-- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- pgpNloAasNcRV.pgp Description: PGP signature
Re: Database design question
Mahmoud, Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Your 'choice1-subchoice1' etc are combined values, so they aren't atomic. From your three example dropdown values, it looks as if 'choice' and 'subchoice' have a many-to-many relationship, and if that's so, you'll probably need a second table for 'subchoice' and a third table to store combinations of choice and subschoice. There are examples of disadvantages of not putting a table in 2NF or 3NF at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf, click on 'Normalisation and the normal forms', read the sections on 1NF, 2NF and 3NF. PB - Mahmoud Badreddine wrote: Hello, I have two questions: I would like to know whether I am violating the principle of atomicity in doing the following: I have a form which has a field with the following options: Choice1, choice2 and choice3. Each of the above choices have two further subchoices : subChoice1, subchoice2. so in my form I have a pull-down list and these are the choices I have: choice1-subchoice1 choice1-subchoice2 choice2-subchoice1 and so on. Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Thank you. No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: book advice
Check out MySQL Tutorial by Luke Welling (MySQL Press). It's easy to understand and not overwhelming. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Performance
Fernando Henrique Giorgetti [EMAIL PROTECTED] wrote on 04/14/2005 02:34:30 PM: Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! -- Fernando Henrique Giorgetti [EMAIL PROTECTED] Departamento de Tecnologia http://www.gruponet.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] If I unfold and indent your query I get: select user , count(distinct concat( date_format(from_unixtime(time), %d/%m/%Y) , - , time_format(from_unixtime(time), %H:%i) ) , ipaddr , urlparent ) as qtd , sec_to_time(sum(duration)/1000) as duration , sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; Your COUNT() operator seems to be trying to execute a COUNT((concatenated date to nearest minute), ipaddr, urlparent). I may have unfolded it incorrectly but that's how it seems to me. I think you meant to put the ipaddr and urlparent fields INTO the CONCAT() but I am just working from what I got. There is a faster way to compute time to the nearest minute than what you are doing with the string conversions. Just do an integer division of your TIME value by 60 and throw away the remainder like this: time DIV 60 or like this: FLOOR(time/60) (http://dev.mysql.com/doc/mysql/en/arithmetic-functions.html) If I understand your COUNT(DISTINCT ) statement correctly, you want to know how in how many different minutes the user used either a unique ipaddr or a different urlparent. Am I close? You can also compute as the OR of two ranges (which may end up using the index or it may not...) So this could be a valid revision of your original query: select user , count(distinct concat( FLOOR(time/60) , ipaddr , urlparent ) ) as qtd , sec_to_time(sum(duration)/1000) as duration , sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and ( result 'TCP_DENIED/403' OR result 'TCP_DENIED/403' ) group by user; Note: GROUP BY includes a free ORDER BY unless you specify otherwise. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: book advice
At 20:32 +0930 4/14/05, David Lloyd wrote: David, Can anyone recommend this book? Or, if not, what book can you recommend for a newbie. I always recommend Paul Dubois' New Riders Book: * http://www.kitebird.com/mysql-book/ Paul's probably lurking on the list. I bought the 1999 edition and I still refer to it :) Thanks for the recommendation! For new readers, I'd recommend the third edition (2005). :-) DSL -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL connection with other database engines
hi, can MySQL engine connect to databases in any other database engine ??? i have read that this can be done but i was not successful with it!! can u tell me how to connect to such databases. i am trying it through c++ code and also if the OS will effect that... regards sujata __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order important in grant commands?
Dear MySQL List, Is order important when issuing grant commands? Linux, Fedora Core 3 MySQL server version: 4.1.11-standard, installed via RPM. perl DBI module installed from CPAN on top of this version. I am trying to allow all privileges to a given user to a given database from inside my local domain, but allow only select privileges to the same user on the same database from outside that domain. If I use the bad order (see below) of the grant commands, I get a situation where the local user does not have insert privileges. The user's name is user, the database name is calib and the domain is domain.org. Good order: grant select on calib.* to user@%; grant all on calib.* to user@%.domain.org; Bad order: grant all on calib.* to user@%.domain.org; grant select on calib.* to user@%; With the bad order, write privilege seems turned off. I get errors like execute failed: INSERT command denied to user 'user'@'claspc2.domain.org' for table 'RunIndex' from the perl DBI module. With the good order, the same script works! In between invocations, I use the revoke command to wipe out this user/db combo completely from the db table. With both grant orders, the mysql.db tables looks the same, with the Y's and N's exactly where I expect them, at least from the mysql command line. Any helpful hints? Derisive comments? - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Performance
You could probably save a bit of processing time by changing: concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)) to: date_format(from_unixtime(time), %d/%m/%Y - %H:%i) This would mean half the date conversions would be executed. Separating out the 'time' and 'result' indicies will probably help too. Cheers, Andrew On 14/4/05 6:34 pm, Fernando Henrique Giorgetti [EMAIL PROTECTED] wrote: Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Still having problems with MySQL 4.1 on Fedora Core 3
C.F. Scheidecker Antunes wrote: Hello all, I now have disabled my SELinux and so it should work. However it still does not work. I have a bunch of errors now on my .err file that I am copying bellow. I've tried to create the tables again by issuing mysql_install_db and then tried to run the daemon. Is there any ideias on how to fix this? Thanks in advance: errors from .err file: InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.04 050414 11:23:49 InnoDB: Flushing modified pages from the buffer pool... 050414 11:23:49 InnoDB: Started; log sequence number 0 43724 050414 11:23:49 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 mysqld ended Error 13 is a file privileges problem. Mysql probably does not own the files. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
On Friday 08 April 2005 10:38 am, Chris Scheller wrote: i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? Why wouldn't they sync up? If you left off at master-005, pos 123 (lets say). It should pick up right there.. If your missing bin log files, then you need to set up the master to not delete logs the slave has not processed yet. This would assure your up to date, once the slave comes back alive.. Or am I missing something? Jeff pgpsJU5d3COjX.pgp Description: PGP signature
RE: slave out of sync
Often times when my replication breaks, it is a quasi monumental task to re-sync. Luckily, my replication is occurring on a data warehouse, where the tables represent each days worth of data. If replication fails, I can usually, get replication back on track by fixing whatever occurs in the data/servername.err file. If the files get out of sync, I have to abandon replication to get me back synced and reset the master reset master, remove the master and relay files on the slave. Usually I will do a quick snapshot on the master and reset replication on the master, transfer files to the slave, and start slave replication, almost like doing a clean setup. Since I am out of sync by usually at most 24 hours, I only have to transfer a couple of tables to get back into sync. I guess the moral to this long winded story, is be very careful how you design your tables so recovering from replication failure is easier. David Norman Wells Fargo Services -Original Message- From: Jeff Smelser [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 1:16 PM To: mysql@lists.mysql.com Subject: Re: slave out of sync On Friday 08 April 2005 10:38 am, Chris Scheller wrote: i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? Why wouldn't they sync up? If you left off at master-005, pos 123 (lets say). It should pick up right there.. If your missing bin log files, then you need to set up the master to not delete logs the slave has not processed yet. This would assure your up to date, once the slave comes back alive.. Or am I missing something? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error when importing mysql table into excel via ODBC
Hello, I have a mysql ODBC driver 3.51.11.00 installed on a xp machine in order to connect to mysql server (on unix) remotely via the ODBC driver. The ODBC driver was working well. What I am trying to do is to import one of the mysql table into excel through New Database Query option in Excel. However, I was not able to import the table , an error was generated at the end of Query Wizard set up: Unknown table histsum_0 in field list The table is actually called histsum but I don't know why it is saying histsum_0. Has anyone ran into this error before? I have four tables in the same database, only this one couldn't be imported, the other three are working fine. I have searched over the internet but didn't find much info on this error. Any help would be appreciated! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error when importing mysql table into excel via ODBC
I have had this same exact problem. It has forced me to use cancel when it prompts for the table and input the SQL manually. Basically I connect to the datasource and hit cancel to the next screen to allow me to enter the SQL. I would love to know why this happens as well. David Norman Wells Fargo Services -Original Message- From: Huang, Ou [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 1:33 PM To: mysql@lists.mysql.com Subject: Error when importing mysql table into excel via ODBC Hello, I have a mysql ODBC driver 3.51.11.00 installed on a xp machine in order to connect to mysql server (on unix) remotely via the ODBC driver. The ODBC driver was working well. What I am trying to do is to import one of the mysql table into excel through New Database Query option in Excel. However, I was not able to import the table , an error was generated at the end of Query Wizard set up: Unknown table histsum_0 in field list The table is actually called histsum but I don't know why it is saying histsum_0. Has anyone ran into this error before? I have four tables in the same database, only this one couldn't be imported, the other three are working fine. I have searched over the internet but didn't find much info on this error. Any help would be appreciated! -- 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: Help with a tricky/impossible query...
One way would be to build a count table with one column starting with value 1 and incrementing by 1 up to say 500 rows or how many your max y value is. Then just select seq,val from wibble,count where val between x and y create table count (val INT unsigned default '0' not null primary key) -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 13, 2005 7:33 PM To: MySQL Subject: Re: Help with a tricky/impossible query... I should mention that I'm constrained to version 4.0.n so no sub queries for me! Andrew On 14/4/05 1:11 am, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, I need some help with a tricky query. Before anyone asks, I cannot bring this functionality back to the application layer (as much as I'd like to). Here's what I need to do... create table wibble( seq int(3) auto_increment primary key, x int(5), y int(5) ); insert into wibble set x=5, y=10; insert into wibble set x=1, y=3; insert into wibble set x=17, y=22; mysql select * from wibble; +-+--+--+ | seq | x| y| +-+--+--+ | 1 |5 | 10 | | 2 |1 |3 | | 3 | 17 | 22 | +-+--+--+ 3 rows in set (0.09 sec) So I want to run a query to explode the x/y ranges by seq. The required output is: mysql select some clever things from wibble where some clever stuff happens here; +-+--+ | seq | z| +-+--+ | 1 |1 | | 1 |2 | | 1 |3 | | 1 |4 | | 1 |5 | | 2 |1 | | 2 |2 | | 2 |3 | | 3 | 17 | | 3 | 18 | | 3 | 19 | | 3 | 20 | | 3 | 21 | | 3 | 22 | +-+--+ 14 rows in set (0.17 sec) Can anyone help me to achieve this result? Thanks, Andrew SQL, Query -- 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]
Multiple sums in a single select?
Hi Guys, I am trying to work out if it is possible to create a select statement that will sum table data into columns with different criteria and have it all in one select statement. For instance I have the following that computes the quantity of a product sold within the last 60 days: select op.products_id, sum(op.products_quantity) as products_sold_60 from orders_products op, orders o where op.orders_id = o.orders_id and to_days(o.date_purchased) (to_days(now()) - 60) GROUP BY op.products_id I would like to list both the total quantity of product sold as well as the quantity sold in the last 60 days in the same columns. I want to do this as a single select statement, so I can easily format the data in a tabular control without needing to write a whole subclass for the control. No matter what I do, I can't figure out how to sum two columns separately! Ie: I want to join the following into a single statement with the above: select op.products_id, sum(op.products_quantity) as products_sold from orders_products op, orders o where op.orders_id = o.orders_id GROUP BY op.products_id Any suggestions? Regards, -- Kendall Bennett Chief Executive Officer SciTech Software, Inc. Phone: (530) 894 8400 http://www.scitechsoft.com ~ SciTech SNAP - The future of device driver technology! ~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple sums in a single select?
You can use an IF in a SUM if you add them based on special conditions: e.g.: SUM(IF(field = 1, field_to_sum, 0)) -Original Message- From: Kendall Bennett [mailto:[EMAIL PROTECTED] Sent: Donnerstag, 14. April 2005 23:55 To: mysql@lists.mysql.com Subject: Multiple sums in a single select? Hi Guys, I am trying to work out if it is possible to create a select statement that will sum table data into columns with different criteria and have it all in one select statement. For instance I have the following that computes the quantity of a product sold within the last 60 days: select op.products_id, sum(op.products_quantity) as products_sold_60 from orders_products op, orders o where op.orders_id = o.orders_id and to_days(o.date_purchased) (to_days(now()) - 60) GROUP BY op.products_id I would like to list both the total quantity of product sold as well as the quantity sold in the last 60 days in the same columns. I want to do this as a single select statement, so I can easily format the data in a tabular control without needing to write a whole subclass for the control. No matter what I do, I can't figure out how to sum two columns separately! Ie: I want to join the following into a single statement with the above: select op.products_id, sum(op.products_quantity) as products_sold from orders_products op, orders o where op.orders_id = o.orders_id GROUP BY op.products_id Any suggestions? Regards, -- Kendall Bennett Chief Executive Officer SciTech Software, Inc. Phone: (530) 894 8400 http://www.scitechsoft.com ~ SciTech SNAP - The future of device driver technology! ~ -- 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]
Problem With Mysql Ms Access with Time
Hi: I have this data: ventas_encabezado_historico_sat Query VEH_EMPRESA VEH_TIENDA VEH_FECHA VEH_STATUS VEH_TOTAL VEH_HORA DLC T01 6/1/2003 V 30 8:31:05 AM DLC T01 6/1/2003 V 114.28 8:38:44 AM DLC T01 6/1/2003 V 63.2 8:41:13 AM DLC T01 6/1/2003 V 8.75 8:42:47 AM dlc T01 6/1/2003 V 12.35 8:50:59 AM dlc T01 6/1/2003 V 63.97 8:53:10 AM DLC T01 6/1/2003 V 10 8:54:24 AM DLC T01 6/1/2003 V 50.65 8:57:54 AM DLC T01 6/1/2003 V 36 9:01:04 AM Where veh_fecha is type date, veh_hora is type date and veh_total is double And I have this code where I try to get the # of registres and a sum in a specific date for a specific time interval (15 minutes) but the select do not work, it returns 0 o the result of the full day, what Im doing wrong? HORA_INICIO = TimeSerial(7, 0, 0) '7:00 am Do Cantidad_Ventas = 0 VALOR_VENTAS = 0 'voy a calcular los valores de atención strSQL = select Count(*) as Cantidad, Sum(veh_total) as Valor from _ ventas_encabezado_historico_sat where _ veh_empresa=' EMP ' and _ veh_tienda=' Tienda ' and _ veh_fecha= MakeUSDate(MAX_ESTADISTICA) and _ Veh_hora= MakeUSTime(HORA_INICIO) And _ veh_hora MakeUSTime(HORA_INICIO + TimeSerial(0, 15, 0)) Set RST = DB.OpenRecordset(strSQL) 'ya tengo calculado los valores, los tengo que guardar Cantidad_Ventas = RST!Cantidad If IsNull(RST!valor) = True Then VALOR_VENTAS = 0 Else VALOR_VENTAS = RST!valor End If RST.Close Set RST = DB.OpenRecordset(Select * from estad_atencion where 1=0) RST.AddNew RST!est_empresa = EMP RST!est_tienda = Tienda RST!est_fecha = MAX_ESTADISTICA RST!est_hora = HORA_INICIO RST!est_atencion = Cantidad_Ventas RST!est_valor = VALOR_VENTAS RST.Update RST.Close HORA_INICIO = HORA_INICIO + TimeSerial(0, 15, 0) Loop Until HORA_INICIO TimeSerial(22, 0, 0) Function MakeUSDate(X As Variant) As String If Not IsDate(X) Then Exit Function MakeUSDate = # Month(X) / Day(X) / Year(X) # End Function Function MakeUSTime(X As Variant) As String If Not IsDate(X) Then Exit Function MakeUSTime = # Hour(X) : Minute(X) : Second(X) # End Function -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005
Re: slave out of sync
Jeff Smelser wrote: On Friday 08 April 2005 10:38 am, Chris Scheller wrote: i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? Why wouldn't they sync up? If you left off at master-005, pos 123 (lets say). It should pick up right there.. If your missing bin log files, then you need to set up the master to not delete logs the slave has not processed yet. This would assure your up to date, once the slave comes back alive.. Or am I missing something? this is what i thought as well. the error i got was for the position offset not matching up. when i looked at the master it was at file bin.003 and offset of something like 123,456. the slave was still at file bin.001 and offset 654,321. i don't delete the bin log files. i thought the slave would just catch up or am i doing something wrong here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client Side Query cache
Mister Jack wrote: Hi, I was wondering if there is any query cache code/lib somewhere to cache certains queries ? I'm always doing the same queries, (and the result never change, so I could spare the round-trip to the server), but caching each tine the data for it is a bit of work. Thanks, for your suggestions MySQL has a query cache. Check the documentation. Add the lines: query_cache_type= 1 query_cache_size= 16M to the [safe_mysqld] of your /etc/mysql/my.cnf file and restart mysql. It will cache all queries on a *connection*. ie to receive a cached result, you have to issue the query from the same connection as when the query was originally run. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The $cfg['PmaAbsoluteUri'] directive MUST be set in your configuration file!
Hi All, I get the following message when opening phpmyadmin, The $cfg['PmaAbsoluteUri'] directive MUST be set in your configuration file! The mbstring PHP extension was not found and you seem to be using multibyte charset. Without mbstring extension phpMyAdmin is unable to split strings correctly and it may result in unexpected results. Below is what I have for $cfg['PmaAbsoluteUri'] and phpmyadmin seems to work with no problems, except the error message. I have Japanese installed, but, don't use it on the system that much, only when reading Japanese in a browser or in email. Do I need to add the extension if I'm not using Japanese in any databases.? Cheers. $cfg['http://localhost/phpMyAdmin-2.6.1-pl3'] = ''; Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
