Perl DBI Interfacer
Hello, I was wondering if anyone knows PERL DBI, if so please respond to
[EMAIL PROTECTED] personally, or to the list
My question is, I'm trying to build a 'custom' querier for a survey program... based
on the information submitted, the program will generate an SQL Query statement. The
command that my program works absolutely find if I copy/paste it into MySQL, however,
DBI won't pass it... if I pass the QUERY Along to DBI typed in manually it works...
Any Idea's?
Here is a copy of the block of code
#Attempt to build an artificial SQL Query
#
$i=0;
if($vars{'f_name'} ne ) {
@query[0] = AND `f_name` = \'$vars{'f_name'}\';
$i++;
}
if($vars{'l_name'} ne ) {
@query[$i] = AND `l_name` = \'$vars{'l_name'}\';
$i++;
}
if($vars{'stud_num'} ne ) {
@query[$i] = AND `stud_num` = \'$vars{'stud_num'}\';
$i++;
}
if($vars{'grade'} ne ) {
@query[$i] = AND `grade` = \'$vars{'grade'}\';
$i++;
}
foreach $item(@query) {
$dastring = join( ,$dastring,$item);
#$end = \;
#$dastring = join(,$dastring,$end);
}
#print Dum Dum Dum... The query is b$dastring/b\n;
#Build the remaining query string
$first_part = SELECT
`level`,`f_name`,`l_name`,`stud_num`,`grade`,`last_survey` FROM `users` WHERE 1;
$finalstring = join( , $first_part,$dastring);
print The Final Product b$finalstring/b\n;
#
#End of Artificial Query
#Connect to the SQL Server specified in the %sql hash
$dbh = DBI-connect(dbi:mysql:$sql{'database'}, $sql{'username'},
$sql{'password'});
#Pepare an SQL Query for execution
$sth = $dbh-prepare($finalstring);
#Executes the SQL Command in the previous statement
$rc = $sth-execute; #executes the search
#my $row_hash; #defines $row_hash as a local variable.
#Grab and breakup the row hash
#while($row_hash = $sth-fetchrow_hashref) {
$vars{'f_name'}=$row_hash-{f_name}; #Loads the Username
$vars{'l_name'}=$row_hash-{l_name}; #Loads the password
$vars{'stud_num'}=$row_hash-{stud_num}; #Loads the Student #
$vars{'grade'}=$row_hash-{grade}; #loads the grade
$vars{'l_survey'}=$row_hash-{last_survey}; #loads the date of the last
$vars{'level'}=$row_hash-{level}; #Grabs the user level
print endofhtml;
tr
td$vars{'f_name'}/td
td$vars{'l_name'}/td
td$vars{'stud_num'}/td
td$vars{'grade'}/td
td$vars{'l_survey'}/td
td$vars{'level'}/td
/tr
endofhtml
}
$sth-finish; #finishes the SQL Query
$dbh-disconnect(); #Disconnect the database to save connections
Any assistance that can be provided is much appriated as this code is for my school's
survey program
Thanks
Kevin
access denied ????
ok guyz im lost im creating my tables and after i ve created em i tried to change two of the fields to FULLTEXT and this is what i get : Database zabanDb running on localhost Error SQL-query : ALTER TABLE `ctnt_inf` ADD FULLTEXT(`summary`) MySQL said: Error on rename of '.\zabandb\ctnt_inf.MYI' to '.\zabandb\#sql2-5b0-7e.MYI' (Errcode: 13) ACCESS DENIED ! ! ! ! ! what the hell is rong with this thing ? cud anyone help me plz _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
access denied ????
ok guyz im lost im creating my tables and after i ve created em i tried to change two of the fields to FULLTEXT and this is what i get : Database zabanDb running on localhost Error SQL-query : ALTER TABLE `ctnt_inf` ADD FULLTEXT(`summary`) MySQL said: Error on rename of '.\zabandb\ctnt_inf.MYI' to '.\zabandb\#sql2-5b0-7e.MYI' (Errcode: 13) ACCESS DENIED ! ! ! ! ! what the hell is rong with this thing ? cud anyone help me plz thnx a million toby . _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re-using $dbh
I have written a web application using Perl, DBI and MYSQL 3.23.41. I have built the application so that only the interface logic is in the perl cgi scripts (not fastcgi) and all of the real work is done is in perl modules, one of which I call Mydefaults.pm which holds the db connect information but every sql access has a dbconnect infront of it. I do not perform a disconnect anywhere. q1. am I right in saying that I am establishing a connection for EVERY sql statement. i.e. there is no re-use of the connection within the CGI thread. q2. if q1 then can I set-up and export my own dbconnect function that holds the $dbh and only creates one (i.e. issues a dbconnect) if there isn't one. q3. does the lack of a disconnect affect how the statements are committed q4. is there a better solution. q5. is there a good link to some documentation where I can work out how to get sql logging working when I start mysql using safe_mysqld (my.cnf? which section? what in the startup script?) tvm, Owain. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Basic SQL join question
Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- --- Adam Randall http://www.xaren.net/ [EMAIL PROTECTED] http://nt.xaren.net/ [EMAIL PROTECTED] Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible. -- Peter H. Lewis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Configuring Tomcat JNDI Connection to MySQL
i`ve tried to solve this problem, and i think i did it!
First of all:
I've tried with:
Tomcat 4.0.4
Mysql 3.23.49
You should have in your application's lib these libraries:
- commons-collections.jar
- commons-dbcp.jar
- commons-pool.jar
(You can find these jar in www.apache.org)
- mysql-uncomp.jar(driver package)
My server.xml looks like:
...
!--
--
DefaultContext
Resource
name=jdbc/COLLECTIONS
auth=Container
type=javax.sql.DataSource
description=BW Database/
ResourceParams
name=jdbc/COLLECTIONS
parameter
namefactory/name
valueorg.apache.commons.dbcp.BasicDataSourceFactory/value
/parameter
parameter
namedriverClassName/name
valueorg.gjt.mm.mysql.Driver/value
/parameter
parameter
nameusername/name
valueXXX/value
/parameter
parameter
namepassword/name
valueXXX/value
/parameter
parameter
nameurl/name
valuejdbc:mysql://localhost:3306/collections/value
/parameter
/ResourceParams
/DefaultContext
!--
--
...
and the class where i retrive a connection:
package tests;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class MysqlTomcatDatasourceTest extends HttpServlet {
static final private String CONTENT_TYPE = text/html;
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
Context t_ctx = null;
DataSource t_ds = null;
Connection t_conn = null;
PreparedStatement t_stm = null;
ResultSet t_rs = null;
PrintWriter t_out = null;
response.setContentType(CONTENT_TYPE);
try {
t_out = response.getWriter();
t_out.println(html);
t_out.println(headtitleDatasource/title/head);
t_out.println(body);
t_out.println(pi'm in./p);
t_out.println(p);
t_ctx = new InitialContext();
t_ds =
(DataSource)t_ctx.lookup(java:comp/env/jdbc/COLLECTIONS);
System.out.println(done);
t_conn = t_ds.getConnection();
t_stm = t_conn.prepareStatement(select countryname from
countries);
t_rs = t_stm.executeQuery();
while (t_rs.next()){
t_out.println(t_rs.getString(1)+br);
}
t_out.println(/p);
t_out.println(/body/html);
try {t_rs.close();}catch(SQLException e){}
try {t_stm.close();}catch(SQLException e){}
try {t_conn.close();}catch(SQLException e){}
}catch (Exception e) {
throw new ServletException(e);
}
}
}
I hope this help
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: My Replication Story
Danny, Wednesday, September 18, 2002, 6:26:37 PM, you wrote: DH -BEGIN PGP SIGNED MESSAGE- DH Hash: SHA1 DH Victoria Reznichenko wrote: DH | As far as I remember there was found and fixed the similar bug in DH | 4.0.3. Could you provide output of SHOW CREATE TABLE for testing? Hi! DH When you say 'SHOW CREATE TABLE' I assume you mean during the creation of DH the temporary table (SHOW CREATE TEMPORARY TABLE)? In which case mysql DH politely informs me that i have an error in my SQL syntax ;-) Oops! Sorry, it's my fault. I miss that tables in the query are temporary. What is the structure of temporary tables nevertheless? DH I cant afford to take down the master database since it is in use round DH the clock but i have upgraded the slave server to 4.0.3 with an DH identical copy of the data restored from dds. I was unable to reproduce DH the problem either, probably because the database wasn't under load just DH merely hosting my single connection. DH Strangely this is the same behaviour I got from the main database after DH its upgrade, worked fine with 2-3 connections but started jumpin' under DH real load. DH I know this probably isn't of much use to you, i'll try writing up a few DH perl scripts to create some virtual load and see what happens then. DH If there's anything else i can provide for you to help trace this bug DH down. let me know. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: mysql -p as simple user ?
hans, Friday, September 20, 2002, 8:00:08 PM, you wrote: hs have done a succesful update 3.23.36 to 2.23.47 on my mandrake-box 8.2, hs but have now that question : hs as user root it is possible to login now with mysql -p the hs password-question appears, typing in the root-password is okay and I can hs see the prompt mysql but as a simple user and doing all the steps hs above I get : hs [papabaer@hanna papabaer]$ mysql -p hs Enter password: hs ERROR 1045: Access denied for user: 'papabaer@localhost' (Using hs password: YES) hs is it not possible, doing a login as a simple user - not only as root ? hs thought, using mysql -p does the trick ? You should give privileges for non-'root' user. Look in the manual how to do it: http://www.mysql.com/doc/en/GRANT.html hs or may I have to do some changes more ? and if so, where I have to do hs that steps ? what I have to do there ? hs hope someone can help me. I know, this is a really question for hs dummies but I don't know it. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mySQL references
Hi Michael, This is kind of vague request:) MySQL will just store data, the efficiency of searching is mostly depend on how good the database design is, and how good your search program is. However, MySQL offers a unique feature called fulltext searching capability which will make your life so much easier if you are to write a search script using MySQL backend. See: http://www.mysql.com/doc/en/Fulltext_Search.html Here are some articles on the issue: http://www.businessweek.com/magazine/content/01_39/b3750038.htm http://www.boxesandarrows.com/archives/002869.php http://phpbuilder.com/columns/tim19990117.php3 Hope this helps... Gurhan On Thu, 2002-09-19 at 23:24, Michael Forbes wrote: Could anyone provide a couple of web sites which are currently using a searchable database created with mysql, for us to preview? We would like to develop our own database, and are not sure of its capabilities.. Regards, Michael Forbes, Pres., design development industries, LLC t 219.759.3831 e [EMAIL PROTECTED] u http://www.ddi-llc.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with ordering and grouping with distinct ...
Hi all, I have a table with three columns: Destination varchar(254) SentDatetimestamp(14) UserID varchar(32) I want to be able to get a list of the 100 most recently used numbers, based on the timestamp (SentDate) column. I want to have them ordered such that the most recently used number is first. Using the example data below, the list I would like to finish up with is (order manually, but I think it is right): +--+ | Destination | +--+ | 447812106183 | | 447775906857 | | 447781484245 | | 447967305499 | | 447812106198 | | 447812106154 | | 447781484234 | | 447775906851 | | 447967305450 | +--+ How do I do this with an SQL statement? The one that I thought would work does not take the most recent timestamp when grouping :( mysql SELECT DISTINCT Destination, SentDate FROM OutboundMessages WHERE UserID='2' GROUP BY Destination ORDER BY SentDate LIMIT 100; +--++ | Destination | SentDate | +--++ | 447967305499 | 20020529123518 | | 447967305450 | 20020604213249 | | 447775906851 | 20020604214940 | | 447812106183 | 20020606173004 | | 447781484234 | 20020612133629 | | 447812106198 | 20020622194238 | | 447775906857 | 20020622204826 | | 447812106154 | 20020622205026 | | 447781484245 | 20020626004507 | +--++ 13 rows in set (0.37 sec) mysql SELECT DISTINCT Destination, SentDate FROM OutboundMessages WHERE UserID='2' ORDER BY SentDate LIMIT 100 +--++ | Destination | SentDate | +--++ | 447812106183 | 20020921134113 | | 447775906857 | 20020921134001 | | 447781484245 | 20020626004530 | | 447781484245 | 20020626004529 | | 447781484245 | 20020626004507 | | 447781484245 | 20020626002957 | | 447781484245 | 20020625174529 | | 447967305499 | 20020623112634 | | 447812106198 | 20020623112541 | | 447775906857 | 2002065446 | | 447812106198 | 2002065434 | | 447812106154 | 2002064006 | | 447812106154 | 20020622205026 | | 447775906857 | 20020622204826 | | 447812106198 | 20020622194504 | | 447812106198 | 20020622194250 | | 447812106198 | 20020622194238 | | 447967305499 | 20020621090855 | | 447781484234 | 20020612133947 | | 447781484234 | 20020612133629 | | 447967305499 | 20020612133230 | | 447967305499 | 20020606175612 | | 447812106183 | 20020606174826 | | 447812106183 | 20020606173004 | | 447967305499 | 20020606171146 | | 447967305499 | 20020606171002 | | 447967305499 | 20020606122804 | | 447967305499 | 20020605143105 | | 447775906851 | 20020604214940 | | 447967305499 | 20020604214058 | | 447967305450 | 20020604213249 | | 447967305499 | 20020529123940 | | 447967305499 | 20020529123518 | +--++ Many thanks, Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL supports no ¤ (Euro currency) char?
Description: While trying to login to the MySQL database (as root), I failed for no apparent reason. I believe this migth be because my password includes the ¤ (Euro / European currency) char. It works _perfectly_ if I remove that one char or replace it with another. Dunno if this is really a bug or intended. How-To-Repeat: Set a password including that Euro char... Fix: Don't use the Euro char? ;) Submitter-Id: Sebastian Scheible Originator:Sebastian Scheible Organization: MySQL support: none Synopsis: Euro character not allowed in passwords? Severity: non-critical Priority: low Category: mysql Class: change-request Release: mysql-3.23.52 (Official MySQL RPM) Environment: AMD Athlon 1400, SuSE Linux 8.0, ...? System: Linux Deliverance 2.4.18-4GB #1 Wed Mar 27 13:57:05 UTC 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1394302 Aug 10 19:55 /lib/libc.so.6 -rw-r--r--1 root root 25361748 Aug 10 19:24 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 19:05 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/info --includedir=/usr/include --mandir=/usr/share/man '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DELETE SQL Statement.
Could you pls suggest me why this query is not working. I have to delete all duplicate records from me table. here sno is auto increment and domain is table field. 1.DELETE FROM tbl_raw_whois_lookup WHERE domain = '007X.COM' ORDER BY sno LIMIT 1; coulsd you pls send my a sql query for deleting duplicate records. Thanks Regards! Abhi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERROR 1130 : Host 'localhost' is not allowed to connect to this MySQL Server error
I unzipped and installed MySQL 3.23.52 from free-download on my PC running WindowsMe. First time it worked by running 'mysqld' and 'mysqlc' and I made a table and loaded data into it. Then I deleted all rows from 'mysql.user' table where 'host=localhost' in my attempt to make it secure, however, I had to halt using my computer and I closed it. When I re-started, it started giving 'ERROR 1130 : Host 'localhost' is not not allowed to connect to this MySQL server'. I read a lot of manual. I do not have 'mysql_install_db' script to remake the privilege tables. Finally I deleted all files related to mySQL to re-do its installation. Again I unzipped and installed MySQL from the .zip file downloaded earlier. This time 'user.MYD' is not empty but 'host.myd', 'db.myd' are empty (zero bytes). But I have the same problem. 'mysqld' runs silently in the background. But 'mysqlc' and 'mysqladmin' output the same above error and I cannot connect and enter into command line mode to do ANSI SQL commands on tables. Can anyone help me what to do and what should be the entries in 'my.cnf' or 'my.ini' file and else where. My regards to all. __ Post your free ad now! http://personals.yahoo.ca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Website with next SQL standard 200X (final draft)
Hello MySQL, I send one message earlier questioning whether SQL really was not a standard anymore. According to the documents I could find on the issue, SQL is not truly a standard anymore, but may become one again. Reference: http://www.tdan.com/i016hy01.htm and http://www.zdnet.com.au/builder/architect/database/story/0,234918,20266105-1,00.htm There is however work on a new SQL standard, probably SQL 2003. This is currently in final committee dated 08/15/2002, that can be found at http://www.wiscorp.com/SQLStandards.html. I am sure if there exists plans to enforce the new standard yet, but it should certainly be of interest to look at the new standard might aim for. You should enjoy reading - there are more than 3000 pages for you on the standard... -- Best regards, Kenneth mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl DBI Interfacer
On Sat, Sep 21, 2002 at 02:06:01AM -0500, Kevin wrote: Hello, I was wondering if anyone knows PERL DBI, if so please respond to [EMAIL PROTECTED] personally, or to the list My question is, I'm trying to build a 'custom' querier for a survey program... based on the information submitted, the program will generate an SQL Query statement. The command that my program works absolutely find if I copy/paste it into MySQL, however, DBI won't pass it... if I pass the QUERY Along to DBI typed in manually it works... I'm not in the mood to unpeel this code, just to see output. As I'm curious, could you set the DBI trace (to 2 or better), run your query, and show us the logs? That we can see what DBI is getting, and what it's actually complaining about (since you didn't tell us...) Thanks Kevin -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: I've found a bug in MySQL.
Dear Sir, I've found a bug in the function mysql_real_escape_string. For example, i use this function like below: mysql_real_escape_string(auth_sql_server, escapeUser, user, strlen(user)); now user = cHm%Ilu!, but escapeUser equals to Chm134739000!(random), instead of cHm%Ilu!. can you help me to solve the problem? yours sincerely, Ricetons. (passing filter: sql,query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB 3.23.52, foreign keys and update/cascade problem
Hi all: I'm seeing a strange problem updating a field if that field is referenced as a FK in another table. Consider: CREATE TABLE A ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id) ); CREATE TABLE B ( id INTEGER NOT NULL AUTO_INCREMENT, AID INTEGER, name VARCHAR(20), PRIMARY KEY (id), INDEX(AID) , FOREIGN KEY (AID) REFERENCES A (id) ON UPDATE CASCADE ); mysql insert into A values (null, 'one'), (null, 'two'); mysql select * from A; ++--+ | id | name | ++--+ | 1 | one | | 2 | two | ++--+ 2 rows in set (0.00 sec) mysql insert into B values (null, 1, 'hello'), (null, 1, 'world'); mysql select * from B; ++--+---+ | id | AID | name | ++--+---+ | 1 |1 | hello | | 2 |1 | world | ++--+---+ 2 rows in set (0.00 sec) AND HERE IS THE PROBLEM: mysql update A set id = 5 where id = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Note, the innodb manual says: If you defined ON DELETE CASCADE or SET NULL and updated the referenced key in the parent row, InnoDB deleted or updated the child row. This is now changed to conform to SQL-92: you get the error 'Cannot delete parent row'. --- NOTE, There is no 'ON DELETE' constraint in my example so what on earth is going on ? Can anyone explain why an update is not getting cascaded ? I mean, that's the whole POINT of the update constraint. What am I doing wrong ? Best regards, --j __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Interesting innodb activity with 3.23.52
Adrian, - Original Message - From: Adrian Liang [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, September 21, 2002 6:48 AM Subject: Interesting innodb activity with 3.23.52 Hi, We experienced some interesting things when we upgraded to Mysql-Max 3.23.52 (Red Hat 7.1, 2.4.7-10enterprise). It looked like after a sustained amount of large disk activity, the whole system would slow to a crawl and CPU idle % would go down to 0 for about 30 seconds before it popped back. We tried fiddling around with the configuration files and even tried another kernel (2.4.9-34enterprise) but without any luck. What did work was downgrading our MySQL version to 3.23.49a . Once we downgraded, everything worked fine. Has anyone seen anything like this before? Ideally we'd like to take advantage of all the changes made between .49a and .52. this sounds like the well-known 'thread thrashing' problem in Linux. It also occurs with MyISAM tables. CPU usage increases 100-fold to normal. Small changes in glibc seem to affect this. Some users have got a good version by compiling themselves and linking with the glibc on their own computer. The new Linux O(1) thread schedulers may solve this problem. Adrian Liang Em: [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
what is thread cache ?
Hello Mysql . I saw the using show status smth like Threads_cached . I searched the mysql site with no luck , finding just a simple explanation like : Number of threads in the thread cache. That wasn't very .. explainng , so searching further i found how to enable the thread cache , using set-variable=thread_cache_size=4 . So i'd like to know how the thread cache working . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: what is thread cache ?
On Sun, Sep 22, 2002 at 01:36:49AM +0300, cristian ditoiu wrote: I saw the using show status smth like Threads_cached . I searched the mysql site with no luck , finding just a simple explanation like : Number of threads in the thread cache. That wasn't very .. explainng , so searching further i found how to enable the thread cache , using set-variable=thread_cache_size=4 . So i'd like to know how the thread cache working . My guess is: MySQL seems to start a separate thread for each concurrent query and the thread exits when the query completes. Creating a thread often involves an expensive system call. The thread cache instructs MySQL to keep N threads around, even if they're not actively running queries, thereby eliminating thread creation overhead. Setting this value higher had a noticeable effect for me. Results may vary. -- Michael Bacarella | Netgraft Corp | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | http://netgraft.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL-Max-3.23.52-1.i386.rpm is missing many files.
Description: I'm trying to install MySQL MAX 3.23.52 (MySQL, with transactions enabled). My rpm attempts goes like this... 1. Check for installed MySQL... [root@Zeke mysql]# rpm -q -a | fgrep -i mysql [root@Zeke mysql]# 2. Nothing, so I attempt an install... [root@Zeke mysql]# rpm -i MySQL-Max-3.23.52-1.i386.rpm Giving mysqld a couple of seconds to restart var/tmp/rpm-tmp.34280: /etc/rc.d/init.d/mysql: No such file or directory QUESTIONS 1. Why is the mysql start/stop script missing? 2. In addition, OTHER FILES are missing, as compared to the tar/gzip binary: mysql-max-3.23.52-pc-linux-gnu-i686.tar.gz Thanks in advance. /AL How-To-Repeat: 1. Download the Intel Linux RPM file: MySQL-Max-3.23.52-1.i386.rpm 2. su as root 3. rpm -i MySQL-Max-3.23.52-1.i386.rpm 4. See the error /etc/rc.d/init.d/mysql: No such file or directory Fix: BETTER FIX: 1. Re-build the rpm file for MySQL-Max-3.23.52 2. While you guys are at it, provide the rpm for the other modules (ask RedHat for help on this, if necessary). If you want me to do some of the work, I'll volunteer the time. ... Just give me some RH linux box to login to. ... I have only 1 box at home, which I use to support my consulting clients, so I can't fool around with the box too much. WORKAROUND: 1. Install the RPM file, in spite of its problems: MySQL-Max-3.23.52-1.i386.rpm 2. Install tar/gzip file: mysql-max-3.23.52-pc-linux-gnu-i686.tar.gz ...This way, other rpm files that depend on MySQL-Max-3.23.52-1 won't have problems (assuming that they are properly installed). Submitter-Id: [EMAIL PROTECTED] Originator:alg Organization: Al G, WowSignal.Com MySQL support: none Synopsis: MySQL-Max-3.23.52-1.i386.rpm is missing many files. Severity: serious Priority: medium Category: mysql Class: change-request Release: mysql-3.23.52-max (Official MySQL-max binary) Environment: uname -a output: Linux Zeke 2.2.16-22 #1 Tue Aug 22 16:16:55 EDT 2000 i586 unknown rpm -q -a output: sed-3.02-8 which-2.11-4 awesfx-0.4.3a-7 zlib-1.1.3-12 control-center-devel-1.2.1-5 dialog-0.9a-3 esound-0.2.19-3 gcc-objc-2.96-54 glade-0.5.9-3 gnome-media-1.2.0-7 gpm-1.19.3-4 ImageMagick-5.2.2-5 kdebase-1.1.2-48 kgcc-1.1.2-40 libpng-1.0.8-1 losetup-2.10m-5 metamail-2.7-25 multimedia-2.1-20 njamd-0.7.0-3 pciutils-devel-2.1.8-8 procmail-3.14-5 quota-2.00pre3-7 rp3-1.1.4-4 SDL-1.1.4-1 strace-4.2-9 tetex-dvilj-1.0.7-7 trn-3.6-25 xboard-4.0.7-3 xloadimage-4.1-16 bash-doc-2.04-11 imap-devel-4.7c2-12 mod_php-4.0.1pl2-9 php-ldap-4.0.1pl2-9 python-tools-1.5.2-27 w3c-libwww-devel-5.2.8-6 bind-devel-8.2.3-1 termcap-11.0.1-3 fileutils-4.0x-3 apmd-3.0final-18 psmisc-19-4 aspell-0.32.5-1 autorun-2.61-5 bzip2-devel-1.0.1-3 XFree86-xfs-4.0.1-1 SysVinit-2.78-10 control-panel-3.18-2 db1-devel-1.85-4 diffstat-1.27-5 ee-0.3.12-1 esound-devel-0.2.19-3 flex-2.5.4a-11 gd-1.8.3-4 gedit-0.9.0-3 glib-devel-1.2.8-4 gnome-core-1.2.1-33 gnome-objc-1.0.2-9 gnome-utils-1.2.0-7 gpm-devel-1.19.3-4 readline-4.1-5 imlib-1.9.8.1-2 iptables-1.1.1-2 kdegames-1.1.2-6 kdeutils-1.1.2-6 less-358-7 libjpeg-devel-6b-13 libtiff-devel-3.5.5-7 lilo-21.4.4-10 lsof-4.47-5 man-pages-1.30-4 mkbootdisk-1.2.8-2 mount-2.10m-5 ncompress-4.2.4-20 netscape-common-4.75-2 openjade-1.3-6 ORBit-0.5.3-2 pilot-link-devel-0.9.3-10 postgresql-7.0.2-17 pump-0.8.3-2 pythonlib-1.27-1 rdate-1.0-6 rhn_register-gnome-1.0-7 rpm-devel-4.0-4 samba-common-2.0.7-21ssl sendmail-8.11.0-8 slocate-2.2-5 stunnel-3.8-4 tcl-8.3.1-46 tetex-dvips-1.0.7-7 tin-1.4.4-2 trojka-1.1-17 vim-common-5.7-6 xcdroast-0.98-1 XFree86-tools-4.0.1-1 xmailbox-2.5-12 xscreensaver-3.25-4 cxhextris-1.0-22 giftrans-1.12.2-7 itcl-3.1.0-46 libpcap-0.4-29 octave-2.0.16-1 php-imap-4.0.1pl2-9 postgresql-python-7.0.2-17 python-docs-1.5.2-27 sysreport-1.0-4.3 w3c-libwww-5.2.8-6 xsysinfo-1.7-3 filesystem-2.0.7-1 grep-2.4.2-4 modutils-2.3.14-3 bdflush-1.5-14 cracklib-2.7-8 cpio-2.4.2-20 diffutils-2.7-21 exmh-2.2-8 gd-devel-1.8.3-4 kernel-headers-2.4.0-0.26
in mysql-max-3.23.52-pc-linux-gnu-i686.tar.gz dist: init/mysqld.server has incompatibility problems with RH Linux
Description: File: support-files/mysqld.server This file has problems when run under RH Linux 7.0, and I suppose in other Linux versions. How-To-Repeat: 1. Download: mysql-max-3.23.52-pc-linux-gnu-i686.tar.gz 2. Install as per instructions in INSTALL-BINARY . 3. Problems apparent when invoking: mysqld.server start Fix: I made the following fixes in my own copy of mysqld.server, which I checked into RCS. I successfully tested under RH7.0. (Note: the success only relates to start/stop of mysqld). RCS file: RCS/mysql.server,v retrieving revision 1.1 retrieving revision 1.2 diff -r1.1 -r1.2 36,37c36,38 datadir=/usr/local/mysql/data basedir= --- basedir=/usr/local/mysql datadir=$basedir/data bindir=$basedir/bin 39,45d39 if test -z $basedir then basedir=/usr/local/mysql bindir=./bin else bindir=$basedir/bin fi 48c42 pid_file=$datadir/`@HOSTNAME@`.pid --- pid_file=$datadir/`hostname`.pid Submitter-Id: [EMAIL PROTECTED] Originator:[EMAIL PROTECTED] Organization: [EMAIL PROTECTED] MySQL support: none Synopsis: in mysql-max-3.23.52-pc-linux-gnu-i686.tar.gz dist: init/mysqld.server has incompatibility problems with RH Linux Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.52-max (Official MySQL-max binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.52-max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 12 min 38 sec Threads: 1 Questions: 9 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 0.012 Environment: uname -a output: Linux Zeke 2.2.16-22 #1 Tue Aug 22 16:16:55 EDT 2000 i586 unknown rpm -q -a output: sed-3.02-8 which-2.11-4 awesfx-0.4.3a-7 zlib-1.1.3-12 control-center-devel-1.2.1-5 dialog-0.9a-3 esound-0.2.19-3 gcc-objc-2.96-54 glade-0.5.9-3 gnome-media-1.2.0-7 gpm-1.19.3-4 ImageMagick-5.2.2-5 kdebase-1.1.2-48 kgcc-1.1.2-40 libpng-1.0.8-1 losetup-2.10m-5 metamail-2.7-25 multimedia-2.1-20 njamd-0.7.0-3 pciutils-devel-2.1.8-8 procmail-3.14-5 quota-2.00pre3-7 rp3-1.1.4-4 SDL-1.1.4-1 strace-4.2-9 tetex-dvilj-1.0.7-7 trn-3.6-25 xboard-4.0.7-3 xloadimage-4.1-16 bash-doc-2.04-11 imap-devel-4.7c2-12 mod_php-4.0.1pl2-9 php-ldap-4.0.1pl2-9 python-tools-1.5.2-27 w3c-libwww-devel-5.2.8-6 bind-devel-8.2.3-1 termcap-11.0.1-3 fileutils-4.0x-3 apmd-3.0final-18 psmisc-19-4 aspell-0.32.5-1 autorun-2.61-5 bzip2-devel-1.0.1-3 XFree86-xfs-4.0.1-1 SysVinit-2.78-10 control-panel-3.18-2 db1-devel-1.85-4 diffstat-1.27-5 ee-0.3.12-1 esound-devel-0.2.19-3 flex-2.5.4a-11 gd-1.8.3-4 gedit-0.9.0-3 glib-devel-1.2.8-4 gnome-core-1.2.1-33 gnome-objc-1.0.2-9 gnome-utils-1.2.0-7 gpm-devel-1.19.3-4 readline-4.1-5 imlib-1.9.8.1-2 iptables-1.1.1-2 kdegames-1.1.2-6 kdeutils-1.1.2-6 less-358-7 libjpeg-devel-6b-13 libtiff-devel-3.5.5-7 lilo-21.4.4-10 lsof-4.47-5 man-pages-1.30-4 mkbootdisk-1.2.8-2 mount-2.10m-5 ncompress-4.2.4-20 netscape-common-4.75-2 openjade-1.3-6 ORBit-0.5.3-2 pilot-link-devel-0.9.3-10 postgresql-7.0.2-17 pump-0.8.3-2 pythonlib-1.27-1 rdate-1.0-6 rhn_register-gnome-1.0-7 rpm-devel-4.0-4 samba-common-2.0.7-21ssl sendmail-8.11.0-8 slocate-2.2-5 stunnel-3.8-4 tcl-8.3.1-46 tetex-dvips-1.0.7-7 tin-1.4.4-2 trojka-1.1-17 vim-common-5.7-6 xcdroast-0.98-1 XFree86-tools-4.0.1-1 xmailbox-2.5-12 xscreensaver-3.25-4 cxhextris-1.0-22 giftrans-1.12.2-7 itcl-3.1.0-46 libpcap-0.4-29 octave-2.0.16-1 php-imap-4.0.1pl2-9 postgresql-python-7.0.2-17 python-docs-1.5.2-27 sysreport-1.0-4.3 w3c-libwww-5.2.8-6 xsysinfo-1.7-3 filesystem-2.0.7-1 grep-2.4.2-4 modutils-2.3.14-3 bdflush-1.5-14 cracklib-2.7-8 cpio-2.4.2-20 diffutils-2.7-21 exmh-2.2-8
Re: MySQL-Max-3.23.52-1.i386.rpm is missing many files.
At 20:17 -0400 9/21/02, alg wrote: Description: I'm trying to install MySQL MAX 3.23.52 (MySQL, with transactions enabled). My rpm attempts goes like this... 1. Check for installed MySQL... [root@Zeke mysql]# rpm -q -a | fgrep -i mysql [root@Zeke mysql]# 2. Nothing, so I attempt an install... [root@Zeke mysql]# rpm -i MySQL-Max-3.23.52-1.i386.rpm You're supposed to install the non-max rpm first, then install the max rpm. That's why you're seeing so many files missing. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOINing unequal-sized tables (grouped columns)
I've seen this kind of question asked, but have not found any useful answers. So, at the risk of being redundant and repeating what has already been covered: I want to join the groupings of table columns. The tables are potentially different lengths. A toy example (I figure bankers/accountants have been using SQL longer than anyone :-) ): mysql select * from deposits; ++--+-+ | date | acct | damount | ++--+-+ | 2002-09-21 |1 | 100.00 | | 2002-09-21 |2 | 20.00 | | 2002-09-21 |1 | 75.00 | ++--+-+ 3 rows in set (4.73 sec) mysql select * from withdrawals; ++--+-+ | date | acct | wamount | ++--+-+ | 2002-09-21 |2 |5.00 | | 2002-09-21 |1 | 50.00 | ++--+-+ 2 rows in set (0.45 sec) Ideally, I would like to end up with: ++--+-+-+ | date | acct | damount | wamount | ++--+-+-+ | 2002-09-21 |1 | 175.00 | 50.00 | | 2002-09-21 |2 | 20.00 |5.00 | ++--+-+-+ Of course, doing: mysql select w.date,w.acct,sum(damount),sum(wamount) from withdrawals as w left outer join deposits as d on (w.acct = d.acct) group by w.date,w.acct; ++--+--+--+ | date | acct | sum(damount) | sum(wamount) | ++--+--+--+ | 2002-09-21 |1 | 175.00 | 100.00 | | 2002-09-21 |2 |20.00 | 5.00 | ++--+--+--+ 2 rows in set (2.49 sec) is wrong. Doing multiple queries and/or creating a permanent or temporary transaction table works, but I was hoping to get something in one select, without having to transform tables being created from separate sources. I almost thought this would work (mySQL 4.0): mysql select date,acct,sum(damount) from deposits group by date,acct union select date,acct,sum(wamount) from withdrawals group by date,acct; ++--+--+ | date | acct | sum(damount) | ++--+--+ | 2002-09-21 |1 | 175.00 | | 2002-09-21 |2 |20.00 | | 2002-09-21 |1 |50.00 | | 2002-09-21 |2 | 5.00 | ++--+--+ 4 rows in set (0.73 sec) Not quite. Any thoughts? I would think this is a well-known and well-solved problem. My SQL skills are slap-dash at best, learned in fits and starts, so forgive me if I've missed something obvious here. Thanks! Carl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow performance with MERGE tables
Hello,
I am running a rather simple query on a merge table that seems to be taking
much longer than it should.
First let me show the table status of the tables I have merged into table
'blah':
NameTypeRow_format RowsAvg_row_length Data_length
Max_data_length Index_lengthData_free Auto_increment Create_time
Update_time Check_time Create_options Comment
accounting_2002W29 MyISAM Fixed 4 435 17401868310773759 3072
0 NULL2002-09-19 21:11:30 2002-09-20 19:27:01 NULL
accounting_2002W30 MyISAM Fixed 10 435 43501868310773759 3072
0 NULL2002-09-19 21:24:35 2002-09-20 18:37:51 NULL
accounting_2002W31 MyISAM Fixed 1612741 435 701542335 1868310773759
975564800 NULL2002-09-19 21:08:09 2002-09-20 19:45:32 NULL
accounting_2002W32 MyISAM Fixed 2341152 435 1018401120 1868310773759
142214144 0 NULL2002-09-19 21:08:15 2002-09-20 19:45:32 NULL
accounting_2002W33 MyISAM Fixed 3204059 435 1393765665 1868310773759
195347456 0 NULL2002-09-19 21:08:25 2002-09-20 19:45:32 NULL
accounting_2002W34 MyISAM Fixed 3388325 435 1473921375 1868310773759
207994880 0 NULL2002-09-19 21:08:43 2002-09-20 19:45:32 NULL
accounting_2002W35 MyISAM Fixed 3322128 435 1445125680 1868310773759
204946432 0 NULL2002-09-20 11:26:21 2002-09-20 19:45:32 NULL
And the query I run takes very long:
mysql select count(distinct nas_ip) from blah;
++
| count(distinct nas_ip) |
++
|116 |
++
1 row in set (15 min 43.27 sec)
I consider this very long because if I do the same query on one of the merged
tables it is much faster:
mysql select count(distinct nas_ip) from accounting_2002W35;
++
| count(distinct nas_ip) |
++
| 96 |
++
1 row in set (42.03 sec)
Note I have restarted the server before each query to not let caching affect
this. I don't understand why the merge table takes so long. It would be faster
for me to issue a query for the nas_ip in each table, output this to a
temporary table, and then do a select count(distinct) on this table.
Why is the merge table showing bad performance on this query?
Here are some more details.
System: Linux 2.2.16 (Redhat 6.2)
show create table accounting_2002W35;
CREATE TABLE `accounting_2002W35` (
`acct_status_type` enum('Start','Stop') NOT NULL default 'Start',
`user_name` char(80) NOT NULL default '',
`nas_ip` char(15) NOT NULL default '',
`acct_session_time` mediumint(8) unsigned default NULL,
`acct_session_id` char(30) NOT NULL default '',
`timestamp` datetime NOT NULL default '-00-00 00:00:00',
`framed_ip` char(15) default NULL,
`input_bytes` int(10) unsigned default NULL,
`output_bytes` int(10) unsigned default NULL,
`calling_station_id` char(80) default NULL,
`called_station_id` char(80) default NULL,
`baud_rate` mediumint(8) unsigned default NULL,
`framed_protocol` char(20) default NULL,
`class` char(80) default NULL,
`visp_id` mediumint(8) unsigned default NULL,
`country_id` tinyint(3) unsigned default NULL,
`city_id` mediumint(8) unsigned default NULL,
`provider_id` mediumint(8) unsigned default NULL,
UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`),
KEY `nas_ind` (`nas_ip`)
) TYPE=MyISAM
(all accounting tables are the same as above)
show create table blah;
CREATE TABLE `blah` (
`acct_status_type` enum('Start','Stop') NOT NULL default 'Start',
`user_name` char(80) NOT NULL default '',
`nas_ip` char(15) NOT NULL default '',
`acct_session_time` mediumint(8) unsigned default NULL,
`acct_session_id` char(30) NOT NULL default '',
`timestamp` datetime NOT NULL default '-00-00 00:00:00',
`framed_ip` char(15) default NULL,
`input_bytes` int(10) unsigned default NULL,
`output_bytes` int(10) unsigned default NULL,
`calling_station_id` char(80) default NULL,
`called_station_id` char(80) default NULL,
`baud_rate` mediumint(8) unsigned default NULL,
`framed_protocol` char(20) default NULL,
`class` char(80) default NULL,
`visp_id` mediumint(8) unsigned default NULL,
`country_id` tinyint(3) unsigned default NULL,
`city_id` mediumint(8) unsigned default NULL,
`provider_id` mediumint(8) unsigned default NULL,
UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`),
KEY `nas_ind` (`nas_ip`)
) TYPE=MRG_MyISAM
UNION=(accounting_2002W29,accounting_2002W30,accounting_2002W31,accounting_2002W32,accounting_2002W33,accounting_2002W34,accounting_2002W35)
Thanks
TEXT field causes block points outside data file msg
Hi all. I'm new to MySQL, so please forgive me if this issue is already well-known. I'm running MySQL server 3.23.28-gamma-debug on WinME. Yes, I know, that version is almost 2 years old (and the OS is lousy, don't bother to tell me). It came on a CD that came with a book, so I installed it for convenience just to casually play around instead of waiting for a fresh version to download. I'm having a problem that's shaking my faith in MySQL, though, so I'd like to see if I can get an answer to it before I just go and upgrade the server version. I made a table where one field is TEXT type. I put a few records into the table (never more than 3 records!), then ran a check on it, and it was fine. Ran a repair, that was fine. Ran an extended repair, and got a few info messages such as Found block that points outside data file at 72. In some cases, the extended repair went on to work (status/OK below those info lines), but in other cases it showed error messages and it couldn't do the repair! Also, sometimes other errors would be produced upon then attempting to drop the table (the code 13 comes to mind), and only some of the table's files would be deleted and I'd have to delete the others manually after shutting down mysqld. I then simplified the table greatly, and still got the block that points outside info messages on an extended-repair. Here's a very simple example using a simplified table: CREATE TABLE clients ( client_id int(10) unsigned NOT NULL auto_increment, zip mediumint(5) unsigned zerofill DEFAULT '0' NOT NULL, description text, PRIMARY KEY (client_id), KEY IDX_zip (zip) ); INSERT INTO clients VALUES (null,12345,'This is the description for the first client'); INSERT INTO clients VALUES (null,67890,'This is the description for the second client'); I run that, then do an extended-mode repair, and I get those Found block that points outside data file at... messages. The original version of the table, that has more fields, would sometimes also cause the more serious problems noted above (can't repair, errors in dropping table), and sometimes not. This is quite recreateable and repeatable; I've created / populated / extended-repaired / dropped both simple and more complex versions of the table over and over, and I always get at least the info messages above. I've never put more than 3 records in these tables, either! Any ideas? This is quite disturbing. It never seemed to affect the ability to read the table's contents (SELECT * FROM clients always worked fine), and changing the TEXT field to VARCHAR(255) solves the problem beautifully, but the fact that a supported column type (TEXT) can produce weird extended-repair messages and sometimes even corrupt the table (at least enough to prevent repair/drop) with only THREE records present is quite unsettling. I want to use MySQL for a production application and this has me nervous. I need excellent stability/reliablity. Thanks guys! If you could, please Cc: me on any replies because I'm not a subscriber to the list. Thanks! __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT statement help
I want to extract data into a temporary table such that unique records with the same primary key (in this case an invoice number) are written to one record, with each of the unique fields extracted being written to a separate field in the temporary table record. I have created my temporary table, and a SELECT that pulls out my data, but I can't work out the appropriate syntax to write the unique fields to the invoice record I'm creating. I think I need something like a while invoice = current value, select... I've gone through the documentation, but can't find what I need for the necessary sql. I'd appreciate any assistance. Thanks, Des Dougan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
strange processlist
Thanks to Jeremy Zawodny for his great tool, I was able to capture this during a server meltdown at the hands of an upraded MySQL binary (From 3.23.32 to 3.23.52). My story is a famliar one -- a db (MyISAM tables) that ran fine for 3 years all of sudden brings our cpu to a halt with an upgraded binary from MySQL. RH, Linux 2.2.16-22enterprise, harmless load averages until a few respectable queries are issued and we get crippled. Never seen a processlist like this though. It happens within 30 seconds of the server getting throttled. Thanks, | 17546 | root| localhost | NULL| Connect | NULL | login | NULL | | 17547 | root| localhost | NULL| Connect | NULL | login | NULL | | 17548 | root| localhost | NULL| Connect | NULL | login | NULL | | 17549 | root| localhost | NULL| Connect | NULL | login | NULL | | 17550 | root| localhost | NULL| Connect | NULL | login | NULL | | 17551 | root| localhost | NULL| Connect | NULL | login | NULL | | 17552 | root| localhost | NULL| Connect | NULL | login | NULL | | 17553 | root| localhost | NULL| Connect | NULL | login | NULL | | 17554 | root| localhost | NULL| Connect | NULL | login | NULL | | 17555 | root| localhost | NULL| Connect | NULL | login | NULL | | 17556 | root| localhost | NULL| Connect | NULL | login | NULL | | 17557 | root| localhost | NULL| Connect | NULL | login | NULL | | 17558 | root| localhost | NULL| Connect | NULL | login | NULL | | 17559 | root| localhost | NULL| Connect | NULL | login | NULL | | 17560 | root| localhost | NULL| Connect | NULL | login | NULL | | 17561 | root| localhost | NULL| Connect | NULL | login | NULL | | 17562 | root| localhost | NULL| Connect | NULL | login | NULL | | 17563 | root| localhost | NULL| Connect | NULL | login | NULL | | 17564 | root| localhost | NULL| Connect | NULL | login | NULL | | 17565 | root| localhost | NULL| Connect | NULL | login | NULL | | 17566 | root| localhost | NULL| Connect | NULL | login | NULL | +---+--+---+-+-+--+--- __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT statement help
At 21:21 -0700 9/21/02, Des Dougan wrote: I want to extract data into a temporary table such that unique records with the same primary key (in this case an invoice number) are written to one record, with each of the unique fields extracted being written to a separate field in the temporary table record. I have created my temporary table, and a SELECT that pulls out my data, but I can't work out the appropriate syntax to write the unique fields to the invoice record I'm creating. I think I need something like a while invoice = current value, select... I've gone through the documentation, but can't find what I need for the necessary sql. You're not going to, either. SQL doesn't act according to your description. Well, that's not strictly true. You *can* come up with SQL to do this, but it's going to be along the lines of SQL that generates another SQL statement that you then execute seperately, and it's going to be a huge mess. If you *really* want to do this (that, is create a denormalized result), you're probably better off doing it within the context of a programming language that lets you manipulate the data outside of SQL. I'd appreciate any assistance. Thanks, Des Dougan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
