persistent temporary tables
i have come to realise that temporary tables are only persistent for 30 secs, is there a way to extend that ? My applications is attempting to store data before and entry is made and then insert the data from the temporary table after the entry is inserted then drop the temp table. This should work in theory but even with peristent php connections the tables are lost. Any ideas ? I have had to create heap tables with random named tables for each user, then drop them as i would with temp tables, is this a good idea ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indices and Constraints.
Thats usually setup in the same table schema no ? On 02/12/2004, at 9:52 PM, [EMAIL PROTECTED] wrote: Hi All, In oracle I can query user_constraints, user_indexes for getting the constraints and indices for a particular table. User_constraints and user_idexes are view which holds all the constraints and indices for a particular table. Need to know the similar one in MySQL. Do we have views or any other system tables in MySQL 4.0.21 which OUTPUTS the constraints and indices in a particular table? Please reply ASAP. Regards, Narasimha Ver: MYSQL 4.0.21 Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub queries
Hi there, I have Mysql 4.1 on my development machine, I have been trying to test out if I am going to be able to do this. What I would like to do is return a one to many resultset but without the duplicated results in the first query. Hows is this going to be possible ? I would like to get all the records out of the second table from a key from the first table. I got this using 4.1.5 mysql select * from shotlist s limit 1 union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' I have also notice union joins add one resultset after the other, how do you mix this in, or add that resultset as a column in the row instead of one after the other ? Also I am trying to push for 4.1 to be installed on the servers i build web apps on. When will be a possible date to say that gamma which is practically production quality, to actually say production quality ? heheh. Our systems guy will only trust it if it says that, god only knows that latest versions are always feature rich and bug fixed darn. Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populating a SELECT from a database
here is how i would do it, a slight change to the if else ..
function filelist($fileID) {
$result=mysql_query(SELECT ID,title,type FROM files);
echo BR;
echo select name=\file\option value=\0\---NONE---/option;
if (mysql_num_rows($result)==0) {
//no files
} else {
while ($row=mysql_fetch_array($result)) {
$ID=$row[ID];
$title=$row[title];
$type=$row[type];
echo option .$ID==$fileID ? selected : . value=\$ID\ . $type
. - . $title .
/option;
}
}
echo /selectBR;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with a complicated join
man i didnt even know you can do this AND s.date q.date i assumed that goes in a where clause ? -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: Wednesday, May 26, 2004 8:01 PM To: [EMAIL PROTECTED] Subject: Re: need help with a complicated join In article [EMAIL PROTECTED], Harald Fuchs [EMAIL PROTECTED] writes: SELECT q.symbol, q.date, q.quote * product (s.split_from / s.split_to) AS adjusted_quote FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date The problem is how to define the 'product' aggregate (along the lines of 'sum'). [ I like talking to myself :-) ] A workaround for the missing product aggregate would be SELECT q.symbol, q.date, q.quote, q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1 FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date but don't ask me how that performs... -- 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: Add/Edit/Delete Form Woes
Here is a methodology i use
action=updateID=1
action=insert
action=edit
on delete if the delete button is pressed that is $_POST['delete'] it will
delete the entry
i use a switch statement on the action request
-Original Message-
From: David Blomstrom [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 6:44 PM
To: [EMAIL PROTECTED]
Subject: Add/Edit/Delete Form Woes
I've been trying to learn how to make a form that
visitors can use to add, delete or modify information
on a MySQL table. I made such a form that worked when
I first jumped into MySQL a few weeks ago, but now I
can't seem to recreate it.
I'm following a tutorial at
http://hotwired.lycos.com/webmonkey/99/21/index3a_page6.html?tw=pr
ogramming
However, I'm adapting it to my own table...
Database = world
Table = Continents
Rows:
CCode
Name1
Type1
Group
Hemisphere
ID1
The resulting form LOOKS great but doesn't work so
great. My table has just 12 rows, and I can see every
one of them represented:
Africa continent (DELETE)
Antarctica continent (DELETE)
Australia continent (DELETE)
Eurasia continent (DELETE)
Middle East continent (DELETE)
North America continent (DELETE)
South America continent (DELETE)
Arctic Ocean ocean (DELETE)
Atlantic Ocean ocean (DELETE)
Indian Ocean ocean (DELETE)
Pacific Ocean ocean (DELETE)
Southern Ocean ocean (DELETE)
ADD A RECORD
[The following precede form input boxes]
CCode:
Name1:
Type1:
Group:
Hemisphere:
ID1:
* * * * * * * * * *
Everything above is linked, except the last six lines,
which merely identify form input boxes.
If I click the linked word Africa, it takes me to
this URL:
http://localhost/geowebworks/php/exp3.php?CCode=caf
That looks right; caf (for continent-Africa) is the
value for the CCode field on the Africa row. But if I
type something in the form and press submit, the
change isn't reflected in my MySQL table.
The weird thing is, in an earlier incarnation, I was
at least able to delete the Southern Ocean row - but
no other rows. Now that I've purged the last of the
error messages, I apparently can't add, delete or
modify anything.
I've struck out with three different PHP forums, so
I'm wondering if the problem might lie with my MySQL
table. The tutorial made extensive use of a field
named id, which I replaced with CCode. Does that
particular field have to be a primary key, foreign
key, etc.?
I posted the code below. Do you have any idea what's
wrong?
[PHP]
?php echo ?xml version=\1.0\
encoding=\iso-8859-1\?.; ?
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0
Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
titleUntitled Document/title
meta http-equiv=Content-Type content=text/html;
charset=iso-8859-1 /
/head
body
?php
$db = mysql_connect(localhost, USERNAME,
PASSWORD);
mysql_select_db(world,$db);
if ($submit) {
// here if no ID then adding else we're editing
if ($CCode) {
$sql = UPDATE Continents SET
CCode='$CCode',Name1='$Name1',Type1='$Type1',Group='$Group',Hemisp
here='$Hemisphere',ID1='$ID1'
WHERE CCode=$CCode;
} else {
$sql = INSERT INTO Continents
(CCode,Name1,Type1,Group,Hemisphere,ID1) VALUES
('$CCode','$Name1','$Type1','$Group','$Hemisphere','$ID1');
}
// run SQL against the DB
$result = mysql_query($sql);
echo Record updated/edited!p;
} elseif ($delete) {
// delete a record
$sql = DELETE FROM Continents WHERE
CCode=$CCode;
$result = mysql_query($sql);
echo $sql Record deleted!p;
} else {
// this part happens if we don't press submit
if (!$CCode) {
// print the list if there is not editing
$result = mysql_query(SELECT * FROM
Continents,$db);
while ($myrow = mysql_fetch_array($result)) {
printf(a href=\%s?CCode=%s\%s %s/a \n,
$PHP_SELF, $myrow[CCode], $myrow[Name1],
$myrow[Type1], $myrow[Group],
$myrow[Hemisphere], $myrow[ID1]);
printf(a
href=\%s?CCode=%sdelete=yes\(DELETE)/abr,
$PHP_SELF, $myrow[CCode]);
}
}
?
P
a href=?php echo $PHP_SELF?ADD A RECORD/a
P
form method=post action=?php echo $PHP_SELF?
?php
if ($id) {
// editing so select a record
$sql = SELECT * FROM Continents WHERE
CCode=$CCode;
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
$id = $myrow[CCode];
$Name1 = $myrow[Name1];
$Type1 = $myrow[Type1];
$Group = $myrow[Group];
$Hemisphere = $myrow[Hemisphere];
$ID1 = $myrow[ID1];
// print the id for editing
?
input type=hidden name=CCode value=?php echo
$CCode ?
?php
}
?
CCode:input type=Text name=CCode value=?php
echo $CCode ?br
Name1:input type=Text name=Name1 value=?php
echo $Name1 ?br
Type1:input type=Text name=Type1 value=?php
echo $Type1 ?br
Group:input type=Text name=Group value=?php
echo $Group ?br
Hemisphere:input type=Text name=Hemisphere
value=?php
RE: Database design.. Asking again
Man 3 times same thread ! What I could consider to you, I don't really understand what you are getting at, what is wrong with 1000 users firstly ? And in the entry table store their userID which is stored in a session when they login ? So when they enter in data it stores their userID into a column , is that right ? userID = autoinc userID in the user table -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 2:50 PM To: MySql Subject: Database design.. Asking again Sorry for the post again, I hijacked a thread and wanted to get this on the correct track. I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? If anyone thinks I should just allow one table to store all this, with that table having 70 million records in it, then I can of course go that road as well. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
Innodb + full text searching
Hi there, I am aware this isnt possible. I would like the power of Innodb, but one of my applications also requires the boolean search within blocks of text, how can i do this efficiently ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Users Table
your hostname ? -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Saturday, May 01, 2004 10:53 PM To: [EMAIL PROTECTED] Subject: MySQL Users Table Hi, I have just installed MySQL on Windows 2000, after inspecting the mysql users table I have noticed that there are four users: Host User localhost root gcw root % root gcw I know about the two root accounts but where did the gcw user come from, there is no documentation on the mysql site? THanks for your help _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- 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: Reports
i'm using an app called report manager, its a report designer for linux and windows done in delphi and kylix. -Original Message- From: Bob Ramsey [mailto:[EMAIL PROTECTED] Sent: Saturday, May 01, 2004 8:36 AM To: [EMAIL PROTECTED] Subject: Re: Reports [EMAIL PROTECTED] wrote: hi, Is there a way to do report writing from Mysql databases? I want to transfer all the records from Mysql to a file. i used the Into OUTFILE but it doesnt display properly.I want to diplay it properly like records or reports. is there a way?? Thanks, Liz You are going to want to add formatting with something like PERL or PHP. Alternately, you can use an odbc connection to have MS Access connect to your mysql server and make the reports for you. Let me know if you need help setting it up. Bob -- 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: when 4.1.2 release
Huh as in production ? -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:52 AM To: [EMAIL PROTECTED] Subject: Re: when 4.1.2 release Marek Lewczuk [EMAIL PROTECTED] wrote: Hello, when do you plan to release 4.1.2 version ? It will be released in several weeks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
two auto-inc fields possible ?
Hi there i was wondering if its possible to have two auto-inc fields ? I am having to generate ticket numbers and i want it to look like DEP-1, DEP-2, NEW-1, NEW-02 where NEW and DEP are the first 3 letters of of the department associated with the ticket. How is this possible ? I've noticed postgres has the serial data type is there an equiv in mysql ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: two auto-inc fields possible ?
cool so i create a unique field say ticket_number , then when inserting
concat it to look like NEW-20040414-01 or NEW-01 u reckon ?
-Original Message-
From: Terence [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 7:29 PM
To: [EMAIL PROTECTED]
Subject: Re: two auto-inc fields possible ?
nope i don't think it's possible
BUT you can use the concat feature to get the result:
select concat('NEW-',running_id), concat('DEP-',running_id')
so long as your running numbers are the same...we use this for
our helpdesk
system too :)
good luck.
- Original Message -
From: electroteque [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 5:11 PM
Subject: two auto-inc fields possible ?
Hi there i was wondering if its possible to have two auto-inc
fields ? I am
having to generate ticket numbers and i want it to look like DEP-1,
DEP-2, NEW-1, NEW-02 where NEW and DEP are the first 3 letters
of of the department associated with the ticket. How is this
possible ? I've
noticed postgres has the serial data type is there an equiv in mysql ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql MAtch against query help
Hehe I've also had a problem with searching for something like it's ,
anything with a single quote doesnt return anything, maybe try adding a
slash , C\+\+ ?? heheh maybe i'm wrong, it could be a limitation.
-Original Message-
From: David Taiwo [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 9:32 PM
To: [EMAIL PROTECTED]
Subject: Mysql MAtch against query help
I have a query that searches my database for people
with C++ on their resume .
Query
=
select * ,match(Res_resume) AGAINST ('C++') as
kewyordscore from member,memberprofile,resume left
join stateprovince on stateid = Res_state
where mem_id = mempf_memid and
match(Res_resume) AGAINST ('+C++' IN BOOLEAN MODE)
I have the ft_min_word_len set to 2 , but for some
reason , it still does not return a record when i
search for C++. Is there a way to escape the ++ when i
do a search , or is there something i am not doing
right.
The query works when i do a search for 'PR' or 'P*' .
so i am sure that i reindexed my database.
I am using Mysql version 4.1.1 on windows.
Any help owuld be great ... thx
Dave
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
http://sbc.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issue compiling mysql with ssl on solaris
Ok i worked out a possible bug with mysql 4.1 compiling openssl on solaris i keep getting this error gcc -DDEFAULT_CHARSET_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql /var \ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DDONT_USE_RAID -I. -I. -I.. -I.. /include /opt/csw/include/openssl -O3 -DDBUG_OFF -D_FILE_OFFSET_BITS=64 -DHAVE_R WLOCK_T -c libmysql.c -MT libmysql.lo -MD -MP -MF .deps/libmysql.TPlo -fPIC -DP IC -o .libs/libmysql.lo sparc-sn-solaris2.8-gcc: cannot specif -o with -c or -S and multiple compilation s *** Error code 1 make: Fatal error: Command failed for target `libmysql.lo' Current working directory /usr/share/src/mysql-4.1.1-alpha/libmysql_r *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /usr/share/src/mysql-4.1.1-alpha *** Error code 1 make: Fatal error: Command failed for target `all' my configure is ./configure --prefix=/usr/local/mysql --with-openssl-includes=/opt/cs w/include/openssl --with-openssl-libs=/opt/csw --without-docs --with-libwrap =/us r --with-mysqld-user=mysql -with-pstack --enable-thread-safe-client what seems to be the problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limiting Return
look for SUBSTRING_INDEX(field,'\.',2) -Original Message- From: Keith [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:51 PM To: [EMAIL PROTECTED] Subject: Limiting Return g'day, 've had a good look through the manual and archives but can't find an answer. I'm pulling, among other things, a field from the DB that holds description data (a few paragrahps of text). I want to limit it so that it only pulls the first 50 words (or characters if thats not possible) Cheers, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5 Application Error
What is your question? Lol exactly show some errors man :D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help on PHP code
well dude u never showed what the parser returned, i am pretty sure u need
curly brackets if u are including more than one line in an if statement
-Original Message-
From: Togochog Enhebatu [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 28, 2004 2:53 PM
To: [EMAIL PROTECTED]
Subject: help on PHP code
hi,
Can any one help find the bug of the following code for me? I am new in
MySQL and PHP, and trying to varify the user ID and password.
help is appreciated.
batu,
!DOCTYPE HTML PUBLIC
-//W3C//DTD HTML 4.0 Transitional//EN
http://www.w3.org/TR/html4/loose.dtd;
html
headtitleCustomer Details/title/head
body bgcolor=green
h1Customer Details/h1
?php
$nickname = $HTTP_POST_VARS['nickname'];
if(empty($nickname))
echo 'brnickname field should not be blank.';
$password = $HTTP_POST_VARS['password'];
if(empty($password))
echo 'brpassword field should not be blank.';
?
?php
$connection=mysql_connect(localhost,csc3223,abdasa);
mysql_select_db(csc3223,$connection);
$result=mysql_query(SELECT nickname,password,status FROM Exam_Provider
WHERE (n
ickname = $nickname) and (password = $password), $connection);
while ($row = mysql_fetch_array($result))
{
echo you have not been approved yet;
}
mysql_close($connection);
?
a href=form.htmlbrbrReturn to the customer form/a
/body
/html
_
Watch high-quality video with fast playback at MSN Video. Free!
http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
--
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: convert
i have converted access to mysql easily using the sqlyog gui tool -Original Message- From: karl james [mailto:[EMAIL PROTECTED] Sent: Sunday, February 08, 2004 11:09 AM To: [EMAIL PROTECTED] Subject: convert Hey guys, I was wondering if there is a converter for access databases to mysql or phpmyadmin I downloaded some stats files for my football league, but they are in access, Anyway I can convert them? Karl James (TheSaint) [EMAIL PROTECTED] http://theufl.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search
when when when will it be available for innodb ? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, January 24, 2004 11:43 AM To: Sidar LC.; [EMAIL PROTECTED] Subject: Re: Fulltext search At 18:34 -0600 1/23/04, Sidar LC. wrote: How can I implement fulltext search engine on InnoDB and MySQL 5. You can't. FULLTEXT indexes are supported only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- 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: Lock tables in myisam
rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb and fulltext
Hi i was wondering if there was ever going to be a time when Innodb can also be fulltext indexable ? Being that i just started to work with Innodb and cant believe how proper it feels of a relationional database over Myisam. Like with Myisiam you cant set relationships up or is that going to change ? As there has been a few projects which needs fulltext but would be good setting up innodb aswell. Also when setting up relationships if i setup on delete to use cascade it will delete all records from the other table joined to that row, this is a wicked feature although, how can i still stop it from being deleted accidently then ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CPU Usage and MySQL...
yes mytop is very wicked it helps monitor the queries and gives you benchmarks -Original Message- From: K Old [mailto:[EMAIL PROTECTED] Sent: Sunday, August 31, 2003 2:02 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: CPU Usage and MySQL... On Sat, 2003-08-30 at 23:18, [EMAIL PROTECTED] wrote: Hello, I have a strange problem with my mysql server. The mysql process seems to be using between 60 and 70 percent of the CPU at all times. Even when it is seemingly idle (not active queries). I'm not sure what to look for to see what it is doing other than truss'ing the process (I'm running it on Sparc Solaris 9 Ultra III procs). I have included the status and variables out put just incase something obvious pop's up there. Thanks alot! Hello, I ran across this tool the other day and it is awesome. Basically it is like the top utility for *nix, but it's for mysql. It basically gives you a live look into the database and what queries it's processing, etc in real time. Give it a try http://jeremy.zawodny.com/mysql/mytop/ Kevin -- K Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: punctuation in fulltext searching
hmm well sorry to be unclear i know this works but it would return more results than needed also i cant expect users to add this themselves, like i would have to add the astrerix to every word in that case like i do to get all words ;\ -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 7:41 PM To: [EMAIL PROTECTED] Subject: Re: punctuation in fulltext searching Daniel Rossi [EMAIL PROTECTED] wrote: Hi i have just come across an issue where a word is not being searched up if there is any punctuation ie. AMROZI'S will not be search upon if you type AMROZI , please help Take a look at * operator which you can use in BOOLEAN MODE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: reindexing
when i reimport i usually drop all the indexes first anyway as it takes forever i have found that a repair table quick usually helps after reindexing too -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 8:09 AM To: 'MySQL' Subject: reindexing I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- 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: importing Access databases
there is an app called sqlyog it imports access directly into mysql -Original Message- From: SpamBox [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:13 AM To: Robert Morgan; mysqllist Subject: Re: importing Access databases Robert Morgan wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. There is 'mdbtools' on sourceforge, which allows you to view data in an MDB file and export it. I would not go about it this way though. I would create the tables in MySQL - paying close attention to field types, and then link the MySQL tables to Access via the MyODBC driver, and then use queries to dump the data from the Access tables into the MySQL tables. Alternatively, you could export the data to a CSV file and use 'load data infile' on the MySQL server to import the data. But linking the tables in Access is easier... Dan -- 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: MATCH AGAINST () ('XXX' IN BOOLEAN MODE) SYNTEX ERROR
the only thing i could see a problem is the decimal but maybe not
-Original Message-
From: Francis Van-Lare [mailto:[EMAIL PROTECTED]
Sent: Saturday, July 12, 2003 4:42 PM
To: [EMAIL PROTECTED]
Subject: MATCH AGAINST () ('XXX' IN BOOLEAN MODE) SYNTEX ERROR
I have run this command without Stating IN BOOLEAN MODE and my search goes
fine. However this query returns a Syntax error WHEN IN BOOLEAN MODE
OPTION IS ADDED , can anyone help ?
Table is MyISAM type running on Linux 8.0
SELECT isbn, title, suggestedretail FROM bookscatalogue WHERE MATCH
(isbn, title ) AGAINST ('+Visual Basic.NET' IN BOOLEAN MODE);
Returns:
Error
SQL-query :
SELECT isbn, title, suggestedretail
FROM bookscatalogue WHERE MATCH (isbn, title )AGAINST ('+Visual Basic.NET'
IN BOOLEAN MODE )
MySQL said:
You have an error in your SQL syntax near 'BOOLEAN MODE
--
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: Slow batch load after 500MB
check the cpu load sometimes is gets real intensive , so i have found , the cpu's are they -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Thursday, July 10, 2003 9:55 PM To: [EMAIL PROTECTED] Subject: Re: Slow batch load after 500MB James E Hicks III [EMAIL PROTECTED] wrote: I have encountered an interesting problem using mysql. On both 3.23 and another 4.01 server when I try to batch load a file with sql statements (ex. mysql -u user -p mysql_statements.SQL) and the file is greater that 500MB it never seems to finish. I have never had the patiences to see if it ever does. Last night I watched the tables as they were loading and as soon as the DB got to ~ 500 MB the inserts would slow down to a crawl. Before it gets to 500MB the server is loading 100's of records at a time, after 500MB the server may load 2 records/5minutes. When I broke the mysql_statements.SQL into smaller files and consecutively loaded these files, the load only took 5 minutes top. Any ideas what I can do to get these large files to load without breaking them down? Is there a server config value I missed? James E Hicks III Looks quite strange: I have used to load gigabytes of data this way with no problem. Try to upgrade server to the latest binary release (4.0.13?) for your OS.. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Faster reindexing
when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: Mysql - Dual Xeon or Dual Opteron
yes i was very impressed with the benchmark on the opteron , frankly i rekon the opterons are more optimised to a unix environment where xeons are more optimised for a windoze environment, we just got a dual 2G zeon dell system for a production box for a project which is doin full text searching on a large database it seems to do ok in a linux environment under mysql4 , well its very impressive very fast searches 4000 records in 1s but i havent personally tried this system on a dual opteron system sadly -Original Message- From: Konstantin Yotov [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 9:35 PM To: [EMAIL PROTECTED] Subject: Mysql - Dual Xeon or Dual Opteron Hello! :) Our server is very heavy loaded: CPUs states are in 0.0% idle almost all day. Linux 7.3 running myslq 3.23.56 and apache 1.3.27 on 2x1GHz Intel, 1GB RAM, 40GB WD 7200 8MB cache. Mysql have around 166 threads and perforn over 400 Queries per second. We are going to uprade our server but I'am wondering between new Opteron (1.4GHz)and Xeon (2.4). We are going to choose Dual Xeon solutions but after reading article about Opteron and Xeon performance with Mysql and Linux http://www.aceshardware.com/read.jsp?id=55000261, where Opteron is 38% faster on query than Xeon, I am very confused what to do. If anybody can help with more information about this, I would be very appriated. Kosyo __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database table is read only
this is a phpbb specific question notice its a custom error message -Original Message- From: HA. Mooduto [mailto:[EMAIL PROTECTED] Sent: Saturday, July 05, 2003 8:13 PM To: [EMAIL PROTECTED] Subject: database table is read only dear all, i has a problem to installing phpbb using mysql database. An error has occured during installation (An error occured trying to update the database Table 'phpbb_config' is read only). what is the problem ? please help me... -- This mail sent through PSP: http://www.polinpdg.ac.id/ -- 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: stored procedures in mysql5
sorry insane is ozzy slang for awesome, the best whatever u want to call it . well , ok from what i know and have asked about , they are precompiled functions , actually embedded into the server which do routines , so therefore all u have to do is call the procedure from the API, therefore ok now its standardised , makes it modular , therefore all u need to do is change the function if any changes are to be made , but i guess how the hell do u recompile, as in completely recompile the source , or just restarting the server? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 8:14 AM To: electroteque; [EMAIL PROTECTED] Subject: Re: stored procedures in mysql5 insane you mean extremely fast? or easy to use? i'd like to know what those SPs are good for... -yves -Ursprüngliche Nachricht- Von: electroteque [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Montag, 30. Juni 2003 00:09 Betreff: RE: stored procedures in mysql5 thanks this is pretty insane cant wait for its release :D -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 6:07 AM To: electroteque Cc: Mysql Subject: Re: stored procedures in mysql5 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote: ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; There are a lot of SP examples in the source distribution or BK tree. Have a look at mysql-test/t/sp.test. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stored procedures in mysql5
ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL/INNODB speed on large databases
On Sat, Jun 28, 2003 at 01:47:28PM -0400, Wendell Dingus wrote: Hello, I've got a situation I'm hoping someone here can help me out with. We have a web server serving up data via some PHP programs pulling from MySQL (3.23.56 currently) in INNODB tables. There are 40+ (active) databases totalling a bit over 28 Gigabytes at this point. As we add more users speed is beginning to drop... Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of RAM. 4 x 15K RPM drives RAID5. It's hitting load averages 12 during peak times and database query speeds are really starting to drag... p3's are ancient architecture now , but i have found trnasactions and full text searching to be very cpu intensive I've talked some with the consulting folks at MySQL and have the intention of contracting them to help analyze our database layout, structure, and queries to help optimize things. hmm the most common thing is to send the list an explain table and maybe the table layout someone may help out, could be an indexing problem. My quandry is this though.. The existing server has 4GB but I can't get innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that route if it could be utilized, I've not figured out how though and assume that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a 64-bit address space sounds good. hey funny u mentioned that i had a read @ thoms hardware i must recommend this as a worthy read http://www17.tomshardware.com/cpu/20030422/index.html ok so basically in a unix environment the opteron blew all over the XEON especially in the mysql benchmark, so the opteron/linux is going to be the recommended cheaper alternative server to sun/solaris, although obviouslly XEON kicked ass in rendering situations on an XP desktop as its optimised for it , so its the situation really i'm currently investigating in the right XEON chip and mobo for a XP rendering workstation for audio/video stuff, but if i were to build a unix server opteron all the way I have the bosses ear for hardware _now_ though and want to move forward before that opportunity passes. I'll post the database specs at the end of this. If you were hosting that much data without a ton of inserts all day long but lots of selects where would you concentrate your efforts? Speed of CPU or speed/quantity of RAM or speed of drives or what? maybe send a grab of top or something would be good Mytop output after about 28 hours of uptime so this is just Friday during business hours access primarily: MySQL on localhost (3.23.56-Max-log) up 1+04:56:30 [13:29:15] Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 Threads Total: 33Active: 1 Cached: 0 Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 hmm mytop is a good app -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedures in mysql5
thanks this is pretty insane cant wait for its release :D -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 6:07 AM To: electroteque Cc: Mysql Subject: Re: stored procedures in mysql5 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote: ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; There are a lot of SP examples in the source distribution or BK tree. Have a look at mysql-test/t/sp.test. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg) -- 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: mysql database dump
lol of course mysqldump -u username -p databasename databasename.sql -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 3:08 PM To: [EMAIL PROTECTED] Subject: mysql database dump Can I dump a database while the database is running in mysql ? -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- 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: Advanced Query Help (My brain hurts!)
would u have an example of how to use union then ? like on the second select i need to reutn a heap of rows from a field from the first select -Original Message- From: MyLists [mailto:[EMAIL PROTECTED] Sent: Saturday, June 28, 2003 10:42 AM To: MySQL List; otherguy Cc: Terry Vanstory Subject: Re: Advanced Query Help (My brain hurts!) How about a UNION statement? If the two queries are independently returning what you need, then you can just append the two results by using UNION. Good Luck! Dennis - Original Message - From: otherguy [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Cc: Terry Vanstory [EMAIL PROTECTED] Sent: Friday, June 27, 2003 8:50 PM Subject: Advanced Query Help (My brain hurts!) Hey guys, I'm about to dump a doozy on your collective knowledge and goodwill, and hope for some help or some pointers. I'm not great with advanced SQL, and I've gotten as far as my brain and the resources I've been using will allow me to get for the time being. I need help with two things: 1) How can I effectively combine the following two queries? The only difference between them is the `appl` condition. I effectively need to do an INNER JOIN on the zip code of the results of both queries so that only zip codes that exist in both result sets are returned: SELECT quota_zip2.zipcode, SUM(quota_control2.quota_actual) AS sum_actual, SUM(quota_control2.quota_limit) AS sum_limit FROM quota_zip2 INNER JOIN quota_control2 ON (quota_zip2.agent_code = quota_control2.agent_code) AND (quota_zip2.appl = quota_control2.appl) WHERE quota_control2.appl = CIRG GROUP BY quota_zip2.zipcode HAVING sum_actual = sum_limit ORDER BY quota_zip2.zipcode; SELECT quota_zip2.zipcode, SUM(quota_control2.quota_actual) AS sum_actual, SUM(quota_control2.quota_limit) AS sum_limit FROM quota_zip2 INNER JOIN quota_control2 ON (quota_zip2.agent_code = quota_control2.agent_code) AND (quota_zip2.appl = quota_control2.appl) WHERE quota_control2.appl = CILT GROUP BY quota_zip2.zipcode HAVING sum_actual = sum_limit ORDER BY quota_zip2.zipcode; I cannot change the where clause to WHERE `appl` = CIRG OR `appl` = CILT because there might be data that would result in a situation where the the sum_actual would meet or exceed the sum_limit for a zip code (using both `appl`'s in the where), whereas running them separately would result in the sum_actual not being met for one of the `appl`'s for that zip_code (it would have been exceeded for the other `appl`). 2) Once I have this query, how can I then update a third table based on it? I basically need to run: UPDATE listmaster SET status = WD WHERE zipcode = any zipcode in results of the query from above. I think that this would involve another inner join, but at this point I'm _WAY_ over my head. I've included a dump of sample tables and data at the end of this e-mail. For the record: 1) I know that this whole thing would be easier to do programatically (it would take a while, but even I could do it that way). Unfortunately due to complications of the environment over which I have absolutely no control, that's not an option. 2) This database is not of my design. 3) This will eventually go into a nightly maintenance job, so query execution time is not a big issue. Finally, in advance, I really appreciate any time and effort any of you are willing to put in. Hopefully there's someone out there that enjoys figuring stuff like this out who has more expertise than I do. Any assistance rendered will result in many thanks from me. Thanks again, -Cameron Wilhelm -=-=-=-=-=-=-=BEGIN DUMP OF SAMPLE DATA=-=-=-=-=-=-=-=-=- # Tables dumped 2003-06-27 19:08:11 -0600 # Created by CocoaMySQL (Copyright (c) 2002-2003 Lorenz Textor) # # Host: localhost Database: nbl_test # ** # Dump of table listmaster # -- CREATE TABLE `listmaster` ( `id` int(11) NOT NULL auto_increment, `zipcode` char(5) default NULL, `status` char(2) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) TYPE=MyISAM; INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (1,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (2,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (3,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (4,1,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (5,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (6,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (7,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (8,2,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (9,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (10,3,NC); INSERT INTO `listmaster` (`id`,`zipcode`,`status`) VALUES (11,3,NC);
multiple mysql instances and virtualhosts
this has prob been bought up many of times but how would it be possible to setup different mysql source instances for each individual virtualhost on a machine ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: XML in MySQL
geez mysql is an open source product you cannot expect so much too soon over a very expensive commercial product which has been out for years , for xml i usually generate it on the fly when i am extracting the data using the field names as the xml tag nodes. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 7:55 AM To: chad pratt Cc: [EMAIL PROTECTED] Subject: Re: XML in MySQL On Sun, Jun 29, 2003 at 08:18:24PM -0400, chad pratt wrote: PHP-SOAP, PEAR SOAP, NuSOAP need more SOAP? SOAP is one thing. But what about storing XML documents in the DB (with validation on the way in) and querying them using XPath? Oracle does that. MySQL does not. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 620,269,579 queries (365/sec. avg) -- 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: Mysql language
c++ ? -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 9:49 PM To: [EMAIL PROTECTED] Subject: Mysql language Hi, I was looking in what language what myslq written in? Just curious Anthony -- 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: REPOST: FULLTEXT searching help needed. Please somebody help.
create fulltext index Name on Table(field) i didnt get this
i usually do add fulltext field (field) i think , is that wrong ??
-Original Message-
From: Santino [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 6:14 PM
To: [EMAIL PROTECTED]
Subject: Re: REPOST: FULLTEXT searching help needed. Please somebody
help.
I think your index is corrupted because I expect a 1.5 GB index and not 8M!
You can see word list wit a utility (sorry I don't remember te name
ft_dump).
I suggest You to drop fulltext index, duplicate database and remove
some rrecords.
Then create index index again.
Some questions:
Do You have disk space to index?
Can your system handle files larger than 2GB?
When mysql is indexing You can check your data file direcory and You
ca see that one file (the index) is growing in size.
The command I use is:
create fulltext index Name on Table(field)
I have some tables with about 300.000 records (about 2k each) and all
works fine:
I rebuild index in minutes and the query needs only 1 or 2 seconds.
Santino
At 9:59 +0200 5-06-2003, H M Kunzmann wrote:
Hello All.
I am using Redhat 9.0 with MySQL 4.0.12-0.
I've hit something of a dead-end with fulltext searching and I don't
know where to look next.
I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.
I've created a fulltext index on the table, with
alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...
If I do a fulltext search against this table, it takes about
1.5 minutes.
select filename from table2002 where match(data) against ('whatever')
If I do an explain on my query, it confirms that the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data| 0| | 1 |using where|
+--+-+-++---+---++---+
I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).
This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?
Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M
Should I set anything else ?
What can I do to get respectable return times ?
Please lend me a helping hand...
Thanks in advance.
H M Kunzmann
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 different Mysql DB on the same server
u need two different instances , totally different source directories for instance , an they run on ifferent pors say one on 3306 and one on 3307 mind u php ha problems connecting to something not on 3306 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 5:55 PM To: [EMAIL PROTECTED] Subject: 2 different Mysql DB on the same server Hi to all, I need to have on the same PC (that is the Server) 2 different mysql DBs; I need it because I need some data on the standard mysql dir (for example ...\mysql\data\) and some data on a different dir. I installed mysql simply installing phpdev423; therefore I found out automatically the DB data on a precise dir (...\phpdev423\mysql\data); when I created a new database automatically the data were put inside the dir ...\phpdev423\mysql\data\name_new_database\. Now I have to create a new DB on a totally different dir (and different HD on the same PC): how can I do it? is there a .ini or .cnf file were I can specify the path? And moreover, can I specified 2 different paths because I need 2 different DBs on 2 different dir? Thak You in Advance for any suggestion Fausto -- 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: ft_boolean_default='AND'
myisam/ft_static.c'. so i have to edit this and recompile ? what a pain in the ass , i'll have to try and format it then -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 12:53 AM To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: ft_boolean_default='AND' Hi! On Jun 02, Egor Egorov wrote: electroteque [EMAIL PROTECTED] wrote: hi guys , i was wondering if this variable , is available for 4.0.12 ? we tried to make it work but mysql would not start , if this is still not available , what are the best ways for me to try and format a search say for instance currently if i search for two words There was no ft_boolean_default variable. Still it's possible to make boolean search to do AND by default. There is ft_boolean_syntax variable, it specifies that '+' means AND and space ' ' means OR. You can change it to be other way around. Unfortunately to change this variable one have to rebuild MySQL. See http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ -- 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]
ft_boolean_default='AND'
hi guys , i was wondering if this variable , is available for 4.0.12 ? we tried to make it work but mysql would not start , if this is still not available , what are the best ways for me to try and format a search say for instance currently if i search for two words something1 something2 something3 something4 it will get either exact matches notice the space if i try something1 something2 + something3 something4 doesnt work if i try something1 something2+something3 something4 works without spaces this is a really unfriendly syntax for not technical users , how can i make this search more friendly and make it default to AND ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
