Re: insert BLOB in perl
Hi, Jeremy Thanks for your response. I got the following message while running perl script. -- output -- processing DCP_0260.JPG size=148135 DBD::mysql::st execute failed: You have an error in your SQL syntax near '9ÿ\0 ¨Ý¦»;ßÈïÓõ,EÂ~9íÒ¬(9àJS~PÃve Ï®G`Z¾NÒXã;ã\0çúÓºwéÓQtWîCÏ' at line 1 at ./upload line 54. -- output -- Do you have any idea? Regards, Vincent Chen --- Jeremy Zawodny [EMAIL PROTECTED] wrote: On Sat, Oct 27, 2001 at 09:08:40PM -0700, Vincent Chen wrote: Dear all, It might be a FAQ, but bothered me several days. I tried to insert a BLOB,acturally a picture, into a table. but always got SQL error. But you haven't told us the error message you're getting! Have you double-checked the DBI manual page? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 52 days, processed 1,161,021,649 queries (258/sec. avg) __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: innodb problem (with JDBC/transactions)
Erik, please send me the lock monitor output. Version 3.23.44 has a better monitor where it is easier to correlate who locked what. But the way to proceed with an older lock monitor is to run individual SQL statements and look what they lock. In a single user environment all locks should be due to the operations of the single user. At 12:56 AM 10/28/01 -0400, you wrote: I tried the lock monitor, and the info it produced seemed to be impossible to correlate with the actual data operations. On top of that, I run it as a service, so I have to uninstall the service just to do use the lock monitor, I think you can just go the the NT control panel, the Services icon, and manually disable the mysqld service. Then go to the bin directory and do ...mysqld-max --console When doing debugging it is always advisable to run mysqld from the MS-DOS prompt. Then you see what is happening. so hesitate to run it. If you think you can actually make sense out of its output, let me know, and I'll do it again and e-mail it. I have had similar problems using SQL Server and Microsoft's JDBC driver, but was able to get rid of them by setting the transaction isolation to TRANSACTION_READ_UNCOMMITTED. This didn't help with MySQL/JDBC, though. I tried all isolation levels to no avail. That suggests it produced deadlocks also on MS SQL Server. Setting to 'read uncommitted' reduces locking and prevents some deadlocks. But the consistency of transactions is then compromised. Here is the pattern... The JSP reads all the rows in the table. I can then successfully update individual rows. With each update it rereads all rows again. Even if I don't update any rows, it still deadlocks when I then try to create a new row. The only time it doesn't deadlock is when I restart everything, and then create before I read anything. Does all this happen in a single transaction within a single connection? Do you call COMMIT in between? Are there several concurrent users? What is an exact sequence of operations which leads to a deadlock? Does it do a SELECT ... FOR UPDATE? These questions would be answered by looking at the lock monitor output. Why do you read the rows several times? If you read the rows and use a locking read (...FOR UPDATE or ...LOCK IN SHARE MODE), then no one else can change them in the meantime until you call COMMIT. If you insert new rows, bear in mind InnoDB does next-key locking to prevent phantoms: reading all the rows with a locking read also prevents all inserts by others to the result set. This is different from Oracle which does not prevent phantoms. See the manual at http://www.innodb.com/ibman.html Keep in mind that all of this is managed via EJBs and the container, and I am using connection pooling. This means that this activity may occur over several connections. Are there concurrent operations? A deadlock should only happen if there are at least 2 users, or if the client is badly written and it divides the work of a single user to two connections. Considering that I lead three systems over the past two years using VB/COM/ADO and Oracle and SQL Server without a single deadlock, even though they all use a lot of transactions, this is not encouraging. I need to be able to deliver quickly, yet I'm stuck on my first EJB because I insist on using open source. PLEASE HELP! The solution is to analyze the locking behavior of your application. What it locks at what phase, and when does it call COMMIT to release the locks. JDBC Driver: mm.mysql-2.0.6.1.jar MySQL: mysql-max-3.23.42-win Thanks, Erik Regards, Heikki http://www.innodb.com -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Friday, October 19, 2001 3:11 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: innodb problem (with JDBC/transactions) Erik, run the MySQL server mysqld from a command prompt and do with the mysql client: mysqlcreate table innodb_lock_monitor(a int) type = innodb; (assuming you run a recent version). Then mysqld will print lock information to the standard output and you see what is happening. Regards, Heikki http://www.innodb.com/ibman.html Has anyone encountered locking problems using JDBC with InnoDB tables and transactions? I am wondering if the MySQL JDBC was designed to handle transactions properly since InnoDB is a relatively recent addition to MySQL. The problem I am running into is the 100 locking error, as if the previous transaction hasn't committed. What complicates this is that I am using Bean Managed Persistance (BMP) with container managed transactions with JBoss as my EJB container. This means that I don't actually write the transaction code, so it's hard to tell exactly what's going on. A transaction is generally defined by the container as each method call that the client makes. I have the transaction option set to Required for all the bean's methods, and the 10 error occurs when the client makes a second call for the same
Re:MySQL server has gone away
Pablo, if your application claims that MySQL server has gone away but you can see with the interactive client 'mysql' that it still is up and works if you submit new queries, then the problem is probably in your C application program. Please post the relevant C code here. Maybe some reader spots some error from it. Does the error come with the very first SQL statement you try to do from your C program? Regards, Heikki http://www.innodb.com Hi, people ! I'm having a trouble with a program written in C. This program try to create a record on a table, and it receives the 2006 error code, MySQL server has goneaway. I've looked at the MySQL manual, searched the Web but the solutions posted doesn't worked for me. I've checked the mysqld, and it's up and running. I've runned manually the same INSERT command of the C program from mysql. It worked and created the recordsuccesfully. Any help ? Sorry for my poor English :-) Thanks in advance Pablo - 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
Anyone worked with the new 4.0 on RH6.2?
Having problems getting the server started. First mysql_install_db reported that resolveip can't resolve my servers host name (of cowa) bla bla bla but this means I only have to use ip addresses in my grant stuff. Fine but the problem is that mysql_safe --user=mysql starts then stops... that's about it. No fanfare... not entries in the cowa.log file (except of course the start and stop lines) I ran resolveip cowa and it core dumps. I checked /var/lib/mysql and wow the mysql and test dirs are empty. Ok seeing the documentation says safe_mysqld rather than the actual mysqld_safe maybe, I just need to be direct to the correct process. Please help! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access Denied
On Sun, 2001-10-28 at 13:52, Anoop Gupta wrote: Error: 1045 - Access denied for user: 'root@' (Using password: NO) Can you please help me out for the same.. Check out the manual. There are some good chapters about this: http://www.mysql.com/doc/A/c/Access_denied.html http://www.mysql.com/doc/R/e/Request_access.html http://www.mysql.com/doc/G/R/GRANT.html -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Räpina, Estonia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Python/mysql problem
Experiencing a weird problem retrieving an mxDateTime value from a mysql
db in a Python script using the MySQLdb module. Here's what I'm doing,
roughly:
connection = MySQLdb.connect(db = DB_NAME)
cursor = connection.cursor()
cursor.execute('select date_entered, description '
'from issue_actions '
'where issue_id = ' + selectedIssueId +
' order by date_entered')
actions = cursor.fetchall()
...do stuff iterating thru actions, this select works perfectly
cursor.execute('select description, when_opened from issues where id =
' + selectedIssueId)
result = cursor.fetchone()
This retrieves the tuple ('issue description',). The mxDateTime
object is missing. Every other select I run that gets a datetime
works fine. This single instance always just returns the description.
If I run this series of commands in the Python interactive window, all
works
fine--datetime object is returned (this is in a cgi script, so
debugging is a bit harder). If I go into mysql and run it all is
fine. I'm completely confused re what's going wrong here.
Any ideas?
Disclaimer: I'm a database and mysql newbie.
Many thanks for any pointers.
-Chris
-
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
Grouping some data, not grouping others?
My project is a database of locations, searchable by multiple keywords. When a user searches the database a selection list of possible locations appears. A location will appear in the list once for each photo of that location. If the user selects an item from the list they are shown detailed information about the location and ALL of the photos. In the selection list I want to rank the locations by relevancy (how many of the chosen keywords did it match) and then by the quality of the photograph (subjective ranking: 1 - 5). PROBLEM: I can easily order the locations by relevancy (using GROUP BY) or the photos by quality, but how can I order the photos in the same order (relevancy) that the locations are ordered? To be able to extract the relevancy (using COUNT) I need to GROUP the data - but then won't be able to display the photos individually. I have three main tables: locations, photos, keywords The keywords-locations relationship is many-to-many, so there is a 'linking' table kw_link. Seems that creating a temporary table (of the GROUPed data) may be the answer, but I'm hoping there's a more straightforward approach and I've never worked with a temp table. If that IS the best route, how could I approach it to avoid conflicts between multiple users hitting at the same time? I'm using PHP as a front end, but could go to Perl if there's a reason to. Any suggestions would be most welcome. Thank you. Nelson -- === Nelson Goforth Lighting for Moving Pictures 01.303.322.5042 http://www.earthnet.net/~ngoforth/film - 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
Problem with Batch mode
Hi all! I've got trouble when i need to run mysql with batch mode. I can't run it. I did these following steps shell mysql -u root -p --batch password : ** - then the screen had no effect. I do this step too shell mysql -u root -p -B password : ** - this had no effect too. Please give me some advices. Thanks Regard Feny -- Sent through GMX FreeMail - http://www.gmx.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
MySQLGUI hangs on certain commands
I've been trying to play with MySQLGUI lately (running the win32 version of the GUI, Linux version of the server). I can connect fine. Run queries fine. But, when I try to do any grants on a database or edit a table, the client connects to the server as many times as it can (before the server runs out of connections available) and hangs until I forcibly kill it. Does anyone know why that would happen?? 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
Optimizing query (2nd attempt)
I have a query as follows: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND users.username=testuser; When I run an 'explain' on it, I get: +++---+-+-+-+--- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- --++ | log| ALL| time | NULL|NULL | NULL| 1192384 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 | where used | | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- --++ 4 rows in set (0.01 sec) Now.. If I exclude the 'users.username=testuser' and substitute it for the userid that I got in a previous query (i.e. userid=2) so that the query becomes: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND log.userid=2; and run an explain, I get... +++---+-+-+-+--- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- ++ | log| ref| time,userid | userid | 4 | const | 27198 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 || | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- ++ 4 rows in set (0.00 sec) Big difference from 1.19million rows to 27198 rows... My question is this. How can I optimize the query with the left joins so that the optimizer will first grab the userid from the username and then use the userid index on log to return the results fast? Thanks in advance, 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: Optimizing query (2nd attempt)
do you have an index defined on users.username ? - Original Message - From: David Wolf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:26 PM Subject: Optimizing query (2nd attempt) I have a query as follows: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND users.username=testuser; When I run an 'explain' on it, I get: +++---+-+-+-+--- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- --++ | log| ALL| time | NULL|NULL | NULL| 1192384 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 | where used | | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- --++ 4 rows in set (0.01 sec) Now.. If I exclude the 'users.username=testuser' and substitute it for the userid that I got in a previous query (i.e. userid=2) so that the query becomes: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND log.userid=2; and run an explain, I get... +++---+-+-+-+--- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- ++ | log| ref| time,userid | userid | 4 | const | 27198 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 || | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- ++ 4 rows in set (0.00 sec) Big difference from 1.19million rows to 27198 rows... My question is this. How can I optimize the query with the left joins so that the optimizer will first grab the userid from the username and then use the userid index on log to return the results fast? Thanks in advance, 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 - 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: Optimizing query (2nd attempt)
Yes.. There is an index on users.username :) David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:46 AM Subject: Re: Optimizing query (2nd attempt) do you have an index defined on users.username ? - Original Message - From: David Wolf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:26 PM Subject: Optimizing query (2nd attempt) I have a query as follows: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND users.username=testuser; When I run an 'explain' on it, I get: +++---+-+-+-+--- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- --++ | log| ALL| time | NULL|NULL | NULL| 1192384 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 | where used | | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- --++ 4 rows in set (0.01 sec) Now.. If I exclude the 'users.username=testuser' and substitute it for the userid that I got in a previous query (i.e. userid=2) so that the query becomes: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND log.userid=2; and run an explain, I get... +++---+-+-+-+--- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- ++ | log| ref| time,userid | userid | 4 | const | 27198 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 || | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- ++ 4 rows in set (0.00 sec) Big difference from 1.19million rows to 27198 rows... My question is this. How can I optimize the query with the left joins so that the optimizer will first grab the userid from the username and then use the userid index on log to return the results fast? Thanks in advance, 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 - 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
Bug with Insert Delayed in MySQL 4.0 ?
Hi, A quite strange bug just occurs with MySQL 4.0 : When I execute show processlist, I get : mysql show processlist; +---+-+---+++--+ ---+ --+ | Id| User| Host | db | Command| Time | State | Info | +---+-+---+++--+ ---+ --+ | 1684 | DELAYED | localhost | Hardwarefr | Delayed_insert | 656 | Waiting on cond | searchhardwarefr10 | | 1942 | DELAYED | localhost | Hardwarefr | Delayed_insert | 486 | Waiting on cond | searchhardwarefr11 | | 2035 | DELAYED | localhost | Hardwarefr | Delayed_insert | 909 | Waiting on cond | searchhardwarefr9 | | 2406 | DELAYED | localhost | Hardwarefr | Delayed_insert | 543 | Waiting on cond | searchconthardwarefr1 | | 4460 | DELAYED | localhost | Hardwarefr | Delayed_insert | 574 | Waiting on cond | searchconthardwarefr6 | | 4526 | DELAYED | localhost | Hardwarefr | Delayed_insert | 1396 | Waiting on cond | searchconthardwarefr10 | | 6105 | DELAYED | localhost | Hardwarefr | Delayed_insert | 1579 | Waiting on cond | searchconthardwarefr11 | | 8127 | DELAYED | localhost | Hardwarefr | Delayed_insert | 5053 | Waiting on cond | searchhardwarefr12 | | 20544 | DELAYED | localhost | Hardwarefr | Delayed_insert | 1802 | Waiting on cond | searchconthardwarefr9 | | 25776 | DELAYED | localhost | Hardwarefr | Delayed_insert | 2670 | Waiting on cond | searchhardwarefr8 | | 39954 | mysql | localhost | Hardwarefr | Query | 420 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 39979 | DELAYED | localhost | Hardwarefr | Delayed_insert | 23 | Waiting on cond | searchhardwarefr7 | | 39984 | mysql | localhost | Hardwarefr | Query | 405 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 40114 | mysql | localhost | Hardwarefr | Query | 311 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 40149 | mysql | localhost | Hardwarefr | Query | 311 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='chipset' OR mot='i810' AND date = '2000-10-08' | | 40165 | DELAYED | localhost | Hardwarefr | Delayed_insert | 72 | Waiting on cond | searchhardwarefr4 | | 40219 | mysql | localhost | Hardwarefr | Query | 277 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 40224 | mysql | localhost | Hardwarefr | Query | 277 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 40281 | mysql | localhost | Hardwarefr | Query | 210 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 40283 | mysql | localhost | Hardwarefr | Query | 202 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='1.2' AND date = '2001-01-08'| | 40303 | mysql | localhost | Hardwarefr | Query | 208 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='lcd' AND date = '2001-10-08'| | 40361 | mysql | localhost | Hardwarefr | Query | 189 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='antec' AND date = '2001-08-08' | | 40363 | root| localhost | NULL | Query | 0| NULL | show processlist | | 40388 | mysql | localhost | Hardwarefr | Query | 171 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='celeron' AND date = '2001-10-08'| | 40399 | mysql | localhost | Hardwarefr | Query | 174 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='antec' AND date = '2001-08-08' | | 40417 | mysql | localhost | Hardwarefr | Query | 148 | Waiting for table | SELECT topic FROM searchhardwarefr8 WHERE mot='Leadtek' OR mot='GeForce' OR mot='III' OR mot='Ti200' | | 40448 | mysql | localhost | Hardwarefr | Query | 162 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='antec' AND date = '2001-08-08' | | 40486 | mysql | localhost | Hardwarefr | Query | 147 | Waiting for table | SELECT topic FROM searchconthardwarefr1 WHERE mot='celeron' AND date = '2001-10-08'| | 40519 | DELAYED | localhost | Hardwarefr | Delayed_insert | 138 | Waiting on cond |
Re: Optimizing query (2nd attempt)
maybe you heva to declare a compound index with userid and username. the sql parser now does not use the index on username. - Original Message - From: David Wolf [EMAIL PROTECTED] To: Tore Van Grembergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:46 PM Subject: Re: Optimizing query (2nd attempt) Yes.. There is an index on users.username :) David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:46 AM Subject: Re: Optimizing query (2nd attempt) do you have an index defined on users.username ? - Original Message - From: David Wolf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:26 PM Subject: Optimizing query (2nd attempt) I have a query as follows: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND users.username=testuser; When I run an 'explain' on it, I get: +++---+-+-+-+--- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- --++ | log| ALL| time | NULL|NULL | NULL| 1192384 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 | where used | | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- --++ 4 rows in set (0.01 sec) Now.. If I exclude the 'users.username=testuser' and substitute it for the userid that I got in a previous query (i.e. userid=2) so that the query becomes: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND log.userid=2; and run an explain, I get... +++---+-+-+-+--- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- ++ | log| ref| time,userid | userid | 4 | const | 27198 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 || | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- ++ 4 rows in set (0.00 sec) Big difference from 1.19million rows to 27198 rows... My question is this. How can I optimize the query with the left joins so that the optimizer will first grab the userid from the username and then use the userid index on log to return the results fast? Thanks in advance, 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 - 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)
Re: Optimizing query (2nd attempt)
How do you do a compound index to index between two tables? David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:57 AM Subject: Re: Optimizing query (2nd attempt) maybe you heva to declare a compound index with userid and username. the sql parser now does not use the index on username. - 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: Optimizing query (2nd attempt)
I seem to have fixed it.. I ran myisamchk on all the tables--and now the indexes work as expected.. Very strange indeed.. Though, there is a strange twist now: explain reports fewer rows to be checked than are displayed with the query runs.. Is that normal? David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:57 AM Subject: Re: Optimizing query (2nd attempt) maybe you heva to declare a compound index with userid and username. the sql parser now does not use the index on username. - Original Message - From: David Wolf [EMAIL PROTECTED] To: Tore Van Grembergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:46 PM Subject: Re: Optimizing query (2nd attempt) Yes.. There is an index on users.username :) David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:46 AM Subject: Re: Optimizing query (2nd attempt) do you have an index defined on users.username ? - Original Message - From: David Wolf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:26 PM Subject: Optimizing query (2nd attempt) I have a query as follows: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND users.username=testuser; When I run an 'explain' on it, I get: +++---+-+-+-+--- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- --++ | log| ALL| time | NULL|NULL | NULL | 1192384 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 | where used | | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- --++ 4 rows in set (0.01 sec) Now.. If I exclude the 'users.username=testuser' and substitute it for the userid that I got in a previous query (i.e. userid=2) so that the query becomes: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND log.userid=2; and run an explain, I get... +++---+-+-+-+--- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- ++ | log| ref| time,userid | userid | 4 | const | 27198 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 || | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- ++ 4 rows in set (0.00 sec) Big difference from 1.19million rows to 27198 rows... My question is this. How can I optimize the query with the left joins so that the optimizer will first grab the userid from the username and then use the userid index on log to return the results fast? Thanks in advance, 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:
Re: Optimizing query (2nd attempt)
rows The rows column indicates the number of rows MySQL believes it must examine to execute the query. - Original Message - From: David Wolf [EMAIL PROTECTED] To: Tore Van Grembergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 5:09 PM Subject: Re: Optimizing query (2nd attempt) I seem to have fixed it.. I ran myisamchk on all the tables--and now the indexes work as expected.. Very strange indeed.. Though, there is a strange twist now: explain reports fewer rows to be checked than are displayed with the query runs.. Is that normal? David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:57 AM Subject: Re: Optimizing query (2nd attempt) maybe you heva to declare a compound index with userid and username. the sql parser now does not use the index on username. - Original Message - From: David Wolf [EMAIL PROTECTED] To: Tore Van Grembergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:46 PM Subject: Re: Optimizing query (2nd attempt) Yes.. There is an index on users.username :) David - Original Message - From: Tore Van Grembergen [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 8:46 AM Subject: Re: Optimizing query (2nd attempt) do you have an index defined on users.username ? - Original Message - From: David Wolf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:26 PM Subject: Optimizing query (2nd attempt) I have a query as follows: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND users.username=testuser; When I run an 'explain' on it, I get: +++---+-+-+-+--- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- --++ | log| ALL| time | NULL|NULL | NULL | 1192384 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 | where used | | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- --++ 4 rows in set (0.01 sec) Now.. If I exclude the 'users.username=testuser' and substitute it for the userid that I got in a previous query (i.e. userid=2) so that the query becomes: SELECT log.entity, log.action, LEFT(users.username,10) AS username, LEFT(boards.title,15) AS Board, LEFT(topics.subject,22) as Subject, log.postid, log.extraid, LEFT(from_unixtime(log.logtime),19) AS time, log.ip FROM log LEFT JOIN users ON log.userid = users.id LEFT JOIN boards ON log.boardid=boards.id LEFT JOIN topics ON log.topicid = topics.id WHERE log.logTime UNIX_TIMESTAMP(2000-10-26 23:00:00) AND log.userid=2; and run an explain, I get... +++---+-+-+-+--- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+-+-+-+--- ++ | log| ref| time,userid | userid | 4 | const | 27198 | where used | | users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1 || | boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1 || | topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1 || +++---+-+-+-+--- ++ 4 rows in set (0.00 sec) Big difference from 1.19million rows to 27198 rows... My question is this. How can I optimize the query with the left joins so that the optimizer will first grab the userid from the username and then use the userid
Re: Optimizing query (2nd attempt)
Not quite fixed.. When I run the query without limiting by time, it still fails to use the userid key. i.e. if I only select where users.username=testuser, I'd expect that users.username to return the users.id=2, and to search using the indexed log.userid=2 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
mySQL and E-Commerce ?
Anyone have a list of major e-commerce companies that use mySQL for 'significant' website and backend functions? 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
Re: mySQL and E-Commerce ?
Jon Shoberg wrote: Anyone have a list of major e-commerce companies that use mySQL for 'significant' website and backend functions? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) Hi, Have you already looked at MySQL web site : http://www.mysql.com/company/users.html ? Or do you need something different ? Regards -- Joseph Bueno NetClub/Trader.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Ultimate DB Server
- Original Message - From: Todd Williamsen [EMAIL PROTECTED] To: 'Mike Rogers' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 9:36 AM Subject: RE: Ultimate DB Server Ram plays a big factor in queries, most queries are stored in ram. Also depends on which platform as well. Agreed, this is especially true when you are talking about a MySQL server with dual processors, as mentioned in the original post. Some OSs arguably handle dual processors much better than others, while others don't support them at all. Hardware can also be critical - dual processors are old-hat for Intel board manufacturers while AMD is still fairly new to the arena. Last fully-custom DB server I set-up for MySQL was a dual PIII 800s (on a Tyan board - easy to configure and rig to the case), 512Mb RAM and 30 gb hard-drive - works like a charm. Good Luck, Dennis Salguero ** Beridney Computer Services [EMAIL PROTECTED] http://www.beridney.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT Query in PHP
Congrats on getting to 48 hours - let us know which platform you prefer when you reach the number of hours that it took to get the 'Must Consult Some Else' tag! :-) QB -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: Saturday, 27 October 2001 5:48 p.m. To: 'Mike'; 'Woolsey, Fred' Cc: [EMAIL PROTECTED] Subject: RE: SELECT Query in PHP Yes, I think HTML, mySQL and PHP is a marriage made in heaven but... Online docs is like reading stereo instructions in a foreign language most of the time. I tried the webmonkey.com tutorial and it was ok.. Funny thing is that the script they used, I couldn't get to work... Go figure... Just my luck! Granted I am so new to PHP I think I just eclipsed the 48 hour mark... So am I still a rookie? Lol!! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: Mike [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 9:33 PM To: Woolsey, Fred Cc: '[EMAIL PROTECTED] ' Subject: Re: SELECT Query in PHP I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, Apache 1.2, and PHP3. Nothing beats the online documentation.. amen to that. :) You might try some sites like webmonkey.com for php tutorials. I think that's where I went when I decided to learn php about.. oh about a year ago. Mike Woolsey, Fred wrote: Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available for free on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exaggerate, but at least you'll find the info you need. Cheers (and remember, wer immer streibend sich bemueht, den koennen wir erloesen.- apologies to Goethe) Fred Woolsey -Original Message- From: Todd Williamsen To: [EMAIL PROTECTED] Sent: 10/26/2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - 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 - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: insert BLOB in perl
Hi,
I am guessing that you default MySQL's charset to big5.
Switch to latin1 and try to insert the image again. See if
the error still persists.
Why this error exists is beyond my knowledge though.
I am guessing that Perl DBI escapes ' \ in bind_param call.
And MySQL escapes the data again when using big5 charset,
which ultimate causes an error because of double escaping.
Let me show a test case which mimic your problem:
From shell:
mysql test;
drop table if exists test;
create table test ( data varchar(10) );
quit
Back to shell:
cd /tmp;
echo ³\\ data.txt
perl -MDBI -e 'my $dbh =
DBI-connect(DBI:mysql:test;mysql_read_default_file=$ENV{HOME}/.my.cnf, undef,
undef, { RaiseError =
1 }); open(FILE, data.txt); read(FILE, my $data, -s data.txt); close(FILE); chomp
$data; $dbh-prepare(INSERT INTO test
values(?))-execute($data)'
Study the error message, you should get the idea as to
why you have problems inserting binary data into MySQL
when it's running using big5 as charset.
Regards,
Jindo
-Original Message-
From: Vincent Chen [mailto:[EMAIL PROTECTED]]
Sent: Sunday, October 28, 2001 3:52 PM
To: [EMAIL PROTECTED]
Subject: Re: insert BLOB in perl
Hi, Jeremy
Thanks for your response. I got the following message
while running perl script.
-- output --
processing DCP_0260.JPG
size=148135
DBD::mysql::st execute failed: You have an error in
your SQL syntax near '9y\0
¡LY|?;sEiOo,EA~9iO?(9aJS~PAve??IRG`Z?NOXa;a\0cuOoweOQtWiC?I'
at line 1 at ./upload line 54.
-- output --
Do you have any idea?
Regards,
Vincent Chen
--- Jeremy Zawodny [EMAIL PROTECTED] wrote:
On Sat, Oct 27, 2001 at 09:08:40PM -0700, Vincent
Chen wrote:
Dear all,
It might be a FAQ, but bothered me several days.
I tried to insert
a BLOB,acturally a picture, into a table. but
always got SQL error.
But you haven't told us the error message you're
getting!
Have you double-checked the DBI manual page?
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell:
(408) 685-5936
MySQL 3.23.41-max: up 52 days, processed
1,161,021,649 queries (258/sec. avg)
__
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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
difficulty compiling v4 --without-innodb
Hello All! Having difficulty compiling v4-alpha --without-innodb. I am -not- a compiler-gooroo. All I can do is try to decode error messages. Never had a single problem compiling earlier versions -or- 4-alpha -without- --without-innodb. All of the above happens under both gcc-2.95.3/RH7 rpm and hand rolled 2.96 (I differentiate between them by symlink in /usr/bin.) Compile always works fine when I remove --without-innodb. There's reference to Apachetoolkit below because I did originally compile through that to make the Apache/php/MySQL integration easier and everything worked fine. So I don't mind letting the source reside there. Since then I have done several other successful manual 4-alpha compiles that worked fine. (Trying to get a standard/stable sock location working right.) Here's my configure script: #!/bin/sh cd /usr/local/src/Apachetoolbox-1.5.41/src/mysql-4.0.0-alpha make distclean rm -f config.cache config.log ./configure \ --prefix=/home/mysql\ --with-mysqld-user=mysql\ --with-debug\ --bindir=/usr/local/sbin\ --sbindir=/usr/local/sbin \ --without-innodb After configure, Makefile says: CONF_COMMAND = ./configure --prefix=/home/mysql --with-mysqld-user=mysql --with-debug --bindir=/usr/local/sbin --sbindir=/usr/local/sbin --without-innodb That looks cool. make ends with (wrapping and spacing lines for clarity): c++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/home/mysql\ -DDATADIR=\/home/mysql/var\ -DSHAREDIR=\/home/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I. -g -O -DSAFE_MUTEX -fno-implicit-templates -fno-exceptions -fno-rtti -c handler.cc c++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/home/mysql\ -DDATADIR=\/home/mysql/var\ -DSHAREDIR=\/home/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I. -g -O -DSAFE_MUTEX -fno-implicit-templates -fno-exceptions -fno-rtti -c ha_heap.cc c++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/home/mysql\ -DDATADIR=\/home/mysql/var\ -DSHAREDIR=\/home/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I. -g -O -DSAFE_MUTEX -fno-implicit-templates -fno-exceptions -fno-rtti -c ha_myisam.cc c++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/home/mysql\ -DDATADIR=\/home/mysql/var\ -DSHAREDIR=\/home/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I. -g -O -DSAFE_MUTEX -fno-implicit-templates -fno-exceptions -fno-rtti -c ha_myisammrg.cc c++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/home/mysql\ -DDATADIR=\/home/mysql/var\ -DSHAREDIR=\/home/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I. -g -O -DSAFE_MUTEX -fno-implicit-templates -fno-exceptions -fno-rtti -c ha_berkeley.cc make[3]: *** No rule to make target `../innobase/include/../ib_config.h', needed by `ha_innobase.o'. Stop. make[3]: Leaving directory `/usr/local/src/Apachetoolbox-1.5.41/src/mysql-4.0.0-alpha/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/usr/local/src/Apachetoolbox-1.5.41/src/mysql-4.0.0-alpha/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/src/Apachetoolbox-1.5.41/src/mysql-4.0.0-alpha' make: *** [all-recursive-am] Error 2 There is an innobase/ib_config.h.in but no .h. Any clues? Thanks much. Have a :)day! jb ob-filter-words: database table sql -- jim barchuk [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: Ultimate DB Server
Hey guys, I am also in the same position as this guy. I was wondering if someone could offer up some specs on a server or any good vendors. I will have two servers. Both will be linux. One will be a client/server server for data entry via the internet and the other will be a web server accessed by php via the internet. The databases will be about 400mb estimated for the first year. Any recommendations or info would be greatly appreciated. (Or things NOT to do) :) Mark Rissmann Drifting Sands LLC - Original Message - From: Mike Rogers [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 9:07 AM Subject: Ultimate DB Server I'm questioning whether anyone has done benchmarks on various hardware for PGSQL and MySQL. I'm either thinking dual P3-866's, Dual AMD-1200's, etc. I'm looking for benchmarks of large queries on striped -vs- non-striped volumes, different processor speeds, etc. Any thoughts people? - 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:difficulty compiling v4 --without-innodb
Jim, you probably should #undef HAVE_INNOBASE_DB in some file, maybe sql/handler.h Search with grep where the above has been defined. Regards, Heikki Hello All! Having difficulty compiling v4-alpha --without-innodb. I am -not- a compiler-gooroo. All I can do is try to decode error messages. Never had a single problem compiling earlier versions -or- 4-alpha -without- --without-innodb. All of the above happens under both gcc-2.95.3/RH7 rpm and hand rolled 2.96 (I differentiate between them by symlink in /usr/bin.) Compile always works fine when I remove --without-innodb. There's reference to Apachetoolkit below because I did originally compile through that to make the Apache/php/MySQL integration easier and everything worked fine. So I don't mind letting the source reside there. Since then I have done several other successful manual 4-alpha compiles that worked fine. (Trying to get a standard/stable sock location workingright.) Here's my configure script:#!/bin/sh cd /usr/local/src/Apachetoolbox-1.5.41/src/mysql-4.0.0-alphamake distclean rm -f config.cache config.log./configure \ --prefix=/home/mysql\ --with-mysqld-user=mysql\ --with-debug\ --bindir=/usr/local/sbin\ --sbindir=/usr/local/sbin \ --without-innodb - 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: Automatically adding the date to a record.
Hello Whats the easiest way to automatically add the current date to a record whenever a new record is inserted? I have tried CREATE TABLE TimeStamp DATE NOT NULL DEFAULT 'DATE()' ...); But this doesn't work. I have tried with TIMESTAMP type instead and this too doesn't work I just get about 14 0's. Any ideas please A TIMESTAMP column ought to do exactly what you're looking for. Don't specify it as NOT NULL, don't set a default value. If you still have problems, try posting the exact CREATE or ALTER statement that you use, and don't forget to tell us which version of MySQL you're using. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Mysql extensions
Hi, Can sb explain me the difference between *.MYD, *.MYI and *.ISM in MySQL. What have change between version 3.22 and 3.23 concerning these. Thx in advance Stef - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:MySQL server has gone away
Yes, Heikki, the error come from the very first SQL statement of my C program Thanks for your help. Pablo --- Heikki Tuuri [EMAIL PROTECTED] escribió: Pablo, if your application claims that MySQL server has gone away but you can see with the interactive client 'mysql' that it still is up and works if you submit new queries, then the problem is probably in your C application program. Please post the relevant C code here. Maybe some reader spots some error from it. Does the error come with the very first SQL statement you try to do from your C program? Regards, Heikki http://www.innodb.com Hi, people ! I'm having a trouble with a program written in C. This program try to create a record on a table, and it receives the 2006 error code, MySQL server has goneaway. I've looked at the MySQL manual, searched the Web but the solutions posted doesn't worked for me. I've checked the mysqld, and it's up and running. I've runned manually the same INSERT command of the C program from mysql. It worked and created the recordsuccesfully. Any help ? Sorry for my poor English :-) Thanks in advance Pablo - 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 Conectate a Internet GRATIS con Yahoo! Conexión: http://conexion.yahoo.com.ar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql extensions
Designmedia writes: Can sb explain me the difference between *.MYD, *.MYI and *.ISM in MySQL. What have change between version 3.22 and 3.23 concerning these. The manual does a pretty good job of this, but to summarize, the MyISAM format was added in 3.23 and it's the default table format. MyISAM tables are faster and have quite a few features that ISAM tables lack. The only reason I can think of for not using MyISAM is that you have an application that relies on AUTO_INCREMENT IDs being reused. http://www.mysql.com/doc/I/S/ISAM.html http://www.mysql.com/doc/M/y/MyISAM.html Thx in advance Stef Hmm... not Stef from User Friendly, I hope. Noo... he'd never join about something technical. :-) //C - needs his daily UF fix -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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
Table Names
Hello All, I need to create a series of MySQL tables with numerical value name, which for some reason I can't. IE: CREATE TABLE 101 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 201 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 301 ( tagnumber varchar(8) not null default '' ) I've looked thru various documentation about table reservered names and couldn't find any info about numerical table names only. Are numerical valued tables names not allowed then?? TIA, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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: Ultimate DB Server
I was planning Dual P3-866 with 2GB of RAM running Linux 2.2.x What do you think? - Original Message - From: Todd Williamsen [EMAIL PROTECTED] To: 'Mike Rogers' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 1:36 PM Subject: RE: Ultimate DB Server Ram plays a big factor in queries, most queries are stored in ram. Also depends on which platform as well. Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: Mike Rogers [mailto:[EMAIL PROTECTED]] Sent: Sunday, October 28, 2001 11:08 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Ultimate DB Server I'm questioning whether anyone has done benchmarks on various hardware for PGSQL and MySQL. I'm either thinking dual P3-866's, Dual AMD-1200's, etc. I'm looking for benchmarks of large queries on striped -vs- non-striped volumes, different processor speeds, etc. Any thoughts people? - 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 - 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: Ultimate DB Server
Yeah- I am thinking dual P3-866 on an Asus board (CUV4X-D), 2GB of RAM, striped 18.1GB 7200RPM drives (U160) Linux 2.2.x is probably the way I'm thinking. Seems to be a nice stable place to go for a good locked down server. Of course, nobody is getting shells on it or anything, so it's strictly MySQL and PGSQL. - Original Message - From: Dennis Salguero [EMAIL PROTECTED] To: Todd Williamsen [EMAIL PROTECTED]; 'Mike Rogers' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 2:44 PM Subject: Re: Ultimate DB Server - Original Message - From: Todd Williamsen [EMAIL PROTECTED] To: 'Mike Rogers' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 9:36 AM Subject: RE: Ultimate DB Server Ram plays a big factor in queries, most queries are stored in ram. Also depends on which platform as well. Agreed, this is especially true when you are talking about a MySQL server with dual processors, as mentioned in the original post. Some OSs arguably handle dual processors much better than others, while others don't support them at all. Hardware can also be critical - dual processors are old-hat for Intel board manufacturers while AMD is still fairly new to the arena. Last fully-custom DB server I set-up for MySQL was a dual PIII 800s (on a Tyan board - easy to configure and rig to the case), 512Mb RAM and 30 gb hard-drive - works like a charm. Good Luck, Dennis Salguero ** Beridney Computer Services [EMAIL PROTECTED] http://www.beridney.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table Names
Hello All, I need to create a series of MySQL tables with numerical value name, which for some reason I can't. IE: CREATE TABLE 101 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 201 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 301 ( tagnumber varchar(8) not null default '' ) I've looked thru various documentation about table reservered names and couldn't find any info about numerical table names only. Are numerical valued tables names not allowed then?? Correct. Table names may contain numbers, even start with numbers -- but cannot consist solely of numbers. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: Ultimate DB Server
At present, I seem to be running out of time and through my research have come up with the following: - High bus speed. When you have 2GB of ram or something (needed with PGSQL, smart with MySQL), bus speed becomes the bottleneck. Any good processor out there (AMD is new to the SMP market, but supposedly quite stable... and fast... considering DDR RAM and high bus speeds, Intel is proven- though P4's are still considered unstable for running a database server. As well, P3's ABOVE 1GHz ARE NOT GOOD FOR DATABASE SERVERS. Go with maybe a P3-866 or P3-900.) should do the trick. SMP will be useful as long as the OS uses it (Linux is a good choice). Web servers will be the least of your worries. The database server will have no user accounts and you will probably comment out the whole inetd.conf file, and shut down each and every daemon. You would have an SSH server and the database ports open- so keeping really ahead of security is no biggy. Probably a good firewall rule to block all but internal machines would be useful too. The databases tend to use a lot more load than the webserver. You should be okay with something simple for that. PGSQL loads the whole DB into Shared RAM if possible, so get lots if you use PGSQL, and it makes MySQL faster, so do it with mysql. A disk good enough to sync regularly is helpful. You assume speed on most wouldn't make a diff, except for loading. Databases will be loaded on startup, rather than constant disk activity like on the web server. So you should be okay with a reasonable HDD. Good Luck! -- Mike - Original Message - From: Mark Rissmann [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 4:54 PM Subject: Re: Ultimate DB Server Hey guys, I am also in the same position as this guy. I was wondering if someone could offer up some specs on a server or any good vendors. I will have two servers. Both will be linux. One will be a client/server server for data entry via the internet and the other will be a web server accessed by php via the internet. The databases will be about 400mb estimated for the first year. Any recommendations or info would be greatly appreciated. (Or things NOT to do) :) Mark Rissmann Drifting Sands LLC - Original Message - From: Mike Rogers [EMAIL PROTECTED] Sent: Sunday, October 28, 2001 9:07 AM Subject: Ultimate DB Server I'm questioning whether anyone has done benchmarks on various hardware for PGSQL and MySQL. I'm either thinking dual P3-866's, Dual AMD-1200's, etc. I'm looking for benchmarks of large queries on striped -vs- non-striped volumes, different processor speeds, etc. Any thoughts people? - 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: Table Names
Thanks. Guess I'll need to come up with another naming scheme. :) On Mon, 29 Oct 2001 00:48:44 +0100, Carsten H. Pedersen [EMAIL PROTECTED] wrote: I need to create a series of MySQL tables with numerical value name, which for some reason I can't. IE: CREATE TABLE 101 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 201 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 301 ( tagnumber varchar(8) not null default '' ) I've looked thru various documentation about table reservered names and couldn't find any info about numerical table names only. Are numerical valued tables names not allowed then?? Correct. Table names may contain numbers, even start with numbers -- but cannot consist solely of numbers. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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 Security w/ PHP
I have been building an extensive dB with MySQL for a large Insurance Company and am nearing the completion stage. I have build several PHP forms to show the preliminary pages to the various groups who will be using this dB. I have created the sign-in page where each user has types in their ID and PW. From then on each query uses those variables for credentials. To avoid duplication, I would like to use the USER form from the MySQL dB. Since that uses the Password(PW) function, I can't seem to get my validation query to work properly. For example: SELECT User,Password from user where (User=$ID AND Password=$PW); Result: Empty Set. SELECT User,Password from user where (User=$ID AND Password($PW)); Result: Will match User only, will accept ANY PW. Does anyone know how to properly check off the PW from the User table in the MySQL db? Thanks, Kevin - 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: Table Names
I need to create a series of MySQL tables with numerical value name, which for some reason I can't. IE: CREATE TABLE 101 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 201 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 301 ( tagnumber varchar(8) not null default '' ) I've looked thru various documentation about table reservered names and couldn't find any info about numerical table names only. Are numerical valued tables names not allowed then?? Mickalo, manual_Reference.html#Legal_names 7.1.5 Database, Table, Index, Column, and Alias Names A name may start with any character that is legal in a name. In particular, a name may start with a number (this differs from many other database systems!). However, a name cannot consist only of numbers. If they are university courses or similar, why not just put a letter in front. If you are trying to construct the table name under some program control, you can still achieve this... Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Security w/ PHP
Have you tried something like SELECT User,Password from user where (User=$ID AND Password($PW)=password); ie. equate the result of password(string) to the stored, encrypted, password Regards Quentin -Original Message- From: Kevin Maynard [mailto:[EMAIL PROTECTED]] Sent: Monday, 29 October 2001 11:52 a.m. To: [EMAIL PROTECTED] Subject: MySQL Security w/ PHP I have been building an extensive dB with MySQL for a large Insurance Company and am nearing the completion stage. I have build several PHP forms to show the preliminary pages to the various groups who will be using this dB. I have created the sign-in page where each user has types in their ID and PW. From then on each query uses those variables for credentials. To avoid duplication, I would like to use the USER form from the MySQL dB. Since that uses the Password(PW) function, I can't seem to get my validation query to work properly. For example: SELECT User,Password from user where (User=$ID AND Password=$PW); Result: Empty Set. SELECT User,Password from user where (User=$ID AND Password($PW)); Result: Will match User only, will accept ANY PW. Does anyone know how to properly check off the PW from the User table in the MySQL db? Thanks, Kevin - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Security w/ PHP
Hi! On Oct 28, Kevin Maynard wrote: I have been building an extensive dB with MySQL for a large Insurance Company and am nearing the completion stage. I have build several PHP forms to show the preliminary pages to the various groups who will be using this dB. I have created the sign-in page where each user has types in their ID and PW. From then on each query uses those variables for credentials. To avoid duplication, I would like to use the USER form from the MySQL dB. You shouldn't do it. These table is used by mysql server internally, and shouldn't be accessed directly. To modify this user table one should use GRANT and REVOKE statements. To read it - SHOW GRANTS. (actually, it can be accessed directly, but ONLY if you're know what you're doing. It looks like you aren't) Since that uses the Password(PW) function, I can't seem to get my validation query to work properly. For example: SELECT User,Password from user where (User=$ID AND Password=$PW); Result: Empty Set. SELECT User,Password from user where (User=$ID AND Password($PW)); Result: Will match User only, will accept ANY PW. SELECT User,Password from user where (User=$ID AND Password=PASSWORD($PW)); Does anyone know how to properly check off the PW from the User table in the MySQL db? The proper way is not to use system tables, and mysql db in general. Create your own database, and User table in it. And, the last, you'd better use MD5() instead of PASSWORD(). 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
PHP Tutorial for total beginner??
Can someone suggest a link to a total beginner PHP/MySQL tutorial? I've got MySQL set up and running (finally!) on Linux, and really need an interface for forms inputting of data. So far, I'm setting up a database as a contact manager/sales transaction history manager on steroids, and I'm extremely happy with where this is going. It's a learning curve, but if I could only learn what PHP was, and how to set it up to work with MySQL, I'd really be in business. Manually inputting data with MySQL commands or loading via text files is just not going to be the way for me to go. I don't mind studying up, if someone can help me get a clue. Thanks a lot. Chip. [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: Table Names
On Sun, 28 Oct 2001 22:59:46 -, DL Neil [EMAIL PROTECTED] wrote: I need to create a series of MySQL tables with numerical value name, which for some reason I can't. IE: CREATE TABLE 101 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 201 ( tagnumber varchar(8) not null default '' ) CREATE TABLE 301 ( tagnumber varchar(8) not null default '' ) I've looked thru various documentation about table reservered names and couldn't find any info about numerical table names only. Are numerical valued tables names not allowed then?? Mickalo, manual_Reference.html#Legal_names 7.1.5 Database, Table, Index, Column, and Alias Names A name may start with any character that is legal in a name. In particular, a name may start with a number (this differs from many other database systems!). However, a name cannot consist only of numbers. If they are university courses or similar, why not just put a letter in front. If you are trying to construct the table name under some program control, you can still achieve this... That was the main idea, to name the tables to coincide with a shopping cart product id values for universal table selection. but we've come up with another idea... :) thanks for pointing out what I obviously missed in the manual! Time for new glasses. ;) Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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: PHP Tutorial for total beginner??
http://www.devshed.com/Server_Side/PHP/PHP101 -Original Message- From: Chip Rose. [mailto:[EMAIL PROTECTED]] Sent: Monday, 29 October 2001 12:31 p.m. To: [EMAIL PROTECTED] Subject: PHP Tutorial for total beginner?? Can someone suggest a link to a total beginner PHP/MySQL tutorial? I've got MySQL set up and running (finally!) on Linux, and really need an interface for forms inputting of data. So far, I'm setting up a database as a contact manager/sales transaction history manager on steroids, and I'm extremely happy with where this is going. It's a learning curve, but if I could only learn what PHP was, and how to set it up to work with MySQL, I'd really be in business. Manually inputting data with MySQL commands or loading via text files is just not going to be the way for me to go. I don't mind studying up, if someone can help me get a clue. Thanks a lot. Chip. [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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: PHP Tutorial for total beginner??
From: Chip Rose. [EMAIL PROTECTED] Date: Sun, 28 Oct 2001 18:31:29 -0500 To: [EMAIL PROTECTED] Subject: PHP Tutorial for total beginner?? Can someone suggest a link to a total beginner PHP/MySQL tutorial? I've got MySQL set up and running (finally!) on Linux, and really need an interface for forms inputting of data. So far, I'm setting up a database as a contact manager/sales transaction history manager on steroids, and I'm extremely happy with where this is going. It's a learning curve, but if I could only learn what PHP was, and how to set it up to work with MySQL, I'd really be in business. Manually inputting data with MySQL commands or loading via text files is just not going to be the way for me to go. I don't mind studying up, if someone can help me get a clue. I think this tutorial by Kevin Yank is excellent. I printed it and went through it several times. It was recently expanded and published as a book, and since I found it so helpful, I immediately ordered the book. http://www.mysql.com/articles/ddws/index.html -- Lowell Allen Graphic Design for Print and Web - 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
Data Normalization Paradox
Hi, This a question for the programmers out there with proper style/form. I'm about to start my first real PHP/MySQL project, and since I want to do it correctly, I got a book to help me. It gives some basic information on RDBM's and mentions an organizational process called normalization. Because of my lack of experience, I'm unsure as to whether this is truly a standard or not. Among other criteria, it mentions that in first order normalization No repeating groups of data are allowed. It then gives a table with 3 rows of data, and shows that one of the columns (company name) has the same entry in two of the rows. It says this table is not in 1st normal form. For the correct way of doing it, it shows the same table, with that column changed to (company name id) and links it to another table that has two columns, company name id, and company id. Frankly, I don't understand how doing this fixes the problem, because now instead of the company name repeating (a string), the company name id (an int)repeats. Could someone explain (maybe convince is a better word) how that creates a more organized database? Now here's the paradox. The project I'm working on is for a judging system of an animation competition. Judges judge on a 1 - 10 basis. There are more than ten teams, so therefore a score will be repeated at least once. My intention was to have a column for the judges score. Am I to create a column that should be judges score id and have it link to a table that corresponds judges score id to judges score? That would create a table that would look like this: 1 1 2 2 3 3 and so on. I would be replacing an int with an int. What are your thoughts? Thanks, and sorry for the longwindness (just my style) Kurt - 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: Compiling C APIs
Douglas Blood ([EMAIL PROTECTED]) writes: When I try to compile the default MyC API that is on the Contributed API page I get the following error: I believe that the error has to do with the way database.h includes mysql.h and that it cant call the methods that are in it. /cygdrive/d/DOCUME~1/ADMINI~1/LOCALS~1/Temp/cc5hhVhM.o(.text+0x8e):sampl e1.c: undefined reference to `mysql_connect' /cygdrive/d/DOCUME~1/ADMINI~1/LOCALS~1/Temp/cc5hhVhM.o(.text+0xa7):sampl e1.c: undefined reference to `mysql_error' It's probably because you're not using -lmysqlclient. -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London - 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: Data Normalization Paradox
Among other criteria, it mentions that in first order normalization No repeating groups of data are allowed. It then gives a table with 3 rows of data, and shows that one of the columns (company name) has the same entry in two of the rows. It says this table is not in 1st normal form. For the correct way of doing it, it shows the same table, with that column changed to (company name id) and links it to another table that has two columns, company name id, and company id. Right. The main reasons for doing this are to minimize data storage space (the number '1' takes far less space to store than the string 'Foo Company'), ensure data integrity (make sure you don't have entries such as 'Foo Company' and 'foo company'), and to speed queries (matching integers can be performed by the sql server MUCH qiucker than matching strings). Hope this helps, Ryan Fox - 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: Data Normalization Paradox
-Original Message- From: Ryan Fox [mailto:[EMAIL PROTECTED]] Sent: 29 October 2001 00:55 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Data Normalization Paradox Among other criteria, it mentions that in first order normalization No repeating groups of data are allowed. It then gives a table with 3 rows of data, and shows that one of the columns (company name) has the same entry in two of the rows. It says this table is not in 1st normal form. For the correct way of doing it, it shows the same table, with that column changed to (company name id) and links it to another table that has two columns, company name id, and company id. Right. The main reasons for doing this are to minimize data storage space (the number '1' takes far less space to store than the string 'Foo Company'), ensure data integrity (make sure you don't have entries such as 'Foo Company' and 'foo company'), and to speed queries (matching integers can be performed by the sql server MUCH qiucker than matching strings). Hope this helps, Ryan Fox I'd say that minimizing data storage isn't even a reason, just a mere sideeffect. Data normalisation ensures that whenever you want to change something about the name of the company, you only have to change the data in one place. It enforces data integrity. It makes life a lot easier, and queries (usually) faster. As for scoring, you should have a table ( animation_id, judge_id, score ) or something - with animation_id and judge_id as primary key. -S - 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
problem with update
Hi all! I am new to MySQl and am having trouble executing an UPDATE statement in a perl script. The same UPDATE statement works fine when used on the prompt. Also, the same perl script executes other statements like SELECT. I am running mysql 3.23.42, perl 5.6.1 on a Windows2000 system. Has anyone had a similar problem? Any help would be highly appreciated. Please do reply to me straight, as I have not subscribed to the mailing list. thanks, Prachi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: problem with update
Hi, You'll need to post your query and the error message to get a meaningful response. Quentin -Original Message- From: Prachi Shroff [mailto:[EMAIL PROTECTED]] Sent: Monday, 29 October 2001 1:36 p.m. To: [EMAIL PROTECTED] Subject: problem with update Hi all! I am new to MySQl and am having trouble executing an UPDATE statement in a perl script. The same UPDATE statement works fine when used on the prompt. Also, the same perl script executes other statements like SELECT. I am running mysql 3.23.42, perl 5.6.1 on a Windows2000 system. Has anyone had a similar problem? Any help would be highly appreciated. Please do reply to me straight, as I have not subscribed to the mailing list. thanks, Prachi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: Problem compiling MySQL-4.0.0-alpha with charset german1
Stephan Skusa ([EMAIL PROTECTED]) writes: ./conf_to_src .. german1 \ ./ctype_extra_sources.c make[2]: *** [ctype_extra_sources.c] Error 132 make[2]: Leaving directory `/usr/src/mysql-4.0.0-alpha/libmysql' I'm running RedHat 6.2 and I'm seeing the same error with latin1. It seems that some versions of gcc/egcs are generating incorrect instructions with optimization turned on - when I turned off optimization, everything worked fine. Alternately, you can try the compile flags listed for your platform in the INSTALL-SOURCES document in the source distro. -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London - 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 4.0.0 core dumps on build]
Description: ./configure;make produces core dump when conf_to_src runs. How-To-Repeat: make distclean;./configure;make Fix: Unknown. Submitter-Id: submitter ID Originator:Ed Carp Organization: Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London MySQL support: [none | licence | email support | extended email support ] Synopsis: MySQL 4.0.0 core dumps on build Severity: Priority: Category: mysql Class: Release: mysql-4.0.0 alpha Server: /usr/local/bin/mysqladmin Ver 8.21 Distrib 3.23.40, for pc-linux-gnu on i586 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.40 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 7 hours 27 min 25 sec Threads: 1 Questions: 97143 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 3.619 Environment: System: Linux adsl-208-191-206-105.dsl.rcsntx.swbell.net 2.2.17-14 #1 Mon Feb 5 17:53:36 EST 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 4 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so -rw-r--r--1 root root 20272704 Feb 29 2000 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 29 2000 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Apr 9 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure Perl: This is perl, version 5.005_03 built for i386-linux - 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: Problem with Batch mode
--- Feny Suha [EMAIL PROTECTED] wrote: Hi all! I've got trouble when i need to run mysql with batch mode. I can't run it. I did these following steps shell mysql -u root -p --batch password : ** - then the screen had no effect. I do this step too shell mysql -u root -p -B password : ** - this had no effect too. the screen had no effect means nothing to me. i usually use something like #!/bin/bash IDENTITY=`echo select text from config where item=\\\machine_key\\\ ; | mysql --quick -su user -pusersPassword database | tail -1 ` or #!/bin/bash while true do echo ' select count(*) from speed ; ' | mysql -B -A -u user -pusersPassword database | tail -1 date sleep 300 done when running mysql from a shell script. note the password on the command line so it doesn't have to be typed or sent. my guess is that when it's no effect you should type a valid SQL statement, ending with a semicolon, possibly carriage return and possibly EOF (^D) and only then expect a response. in batch mode prompts for a human will not be sent to stdout. duncan. __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mutlipe Tables and null values
I hope someone can help me out with this problem I have two tables one for users holding all there info each user has a unique ID. I also have a table that records each transaction each transaction has a unique ID and the ID of the user who made the transaction. I whish to do and SQL query that return a table that has the user ID, user name, and the number of transactions that user has pereformed. Here is a simplified version of my query : SELECT DISTINCT(u.user_ID),COUNT(t.trans_ID) as total,u.f_name,u.l_name FROM users u,transactions t WHERE u.user_ID=t.user_ID GROUP BY u.user_ID; The problem is if a user hasn't made any transactions they won't appear in the results of the above query because they won't have had thier ID in the transactions table. Can anyone help me generate a query that will return all users and if they have an transaction(s) the count of that and if none then null or zero. TIA Jamie - 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
range bug?
Hi, not sure if this is a known issue (ver 3.23.43 lists a fix called: Fixed unlikely bug, which returned non-matching rows, in SELECT with many tables and multi-column indexes and 'range' type.) Anyways, when I do a query like: SELECT sum(minutes) FROM stats_2001 WHERE sdate between 'Oct 29' and 'Oct 29'; I get the correct value (325.81 in this case) BUT when I do: SELECT sum(minutes) FROM `stats_2001` WHERE sdate between 'Oct 29' and 'Oct 30'; I get value: 5605.66 which is impossible - there are no rows with column sdate='Oct 30' (a cron job creates blank rows at midnight, and Oct 30 is tomorrow i.e. has not been created yet. (running: SELECT sum(minutes) FROM `stats_2001` WHERE sdate between 'Oct 30' and 'Oct 30'; yields the correct result, i.e. no result as there are no such rows.) Is this the known issue fixed in 3.23.43? Thanks, Sebastian running 3.23.41 on linux redhat 6.1 intel. - 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 4.0.0 getopt.c will not compile on OSF 5.1
Description:
Compilation of MySQL 4.0.0 on OSF 5.1 aborts in getopt.c due to
an error in the number of arguments to readv() and writev().
This boils down to the problem that compiling the following
2-line program with gcc 3.0.1 on OSF 5.1 screws up the header files:
#define _NO_PROTO
#include sys/uio.h
Actually, above line uses anglebrackets instead of double quotes but
mysqlbug eats those.
gcc -c x.c
In file included from x.c:2:
/usr/include/sys/uio.h:177:22: macro readv requires 3 arguments, but only 1 given
/usr/include/sys/uio.h:178:23: macro writev requires 3 arguments, but only 1 given
How-To-Repeat:
#!/bin/sh
base=/usr/home/gordonb/mysql
cd mysql-4.0.0-alpha
echo configure
CC=gcc -pthread -D_PTHREAD_USE_D4
export CC
CXX=gcc -pthread -D_PTHREAD_USE_D4
export CXX
./configure \
--prefix=${base} \
--with-unix-socket-path=${base}/tmp/mysql.sock \
--with-tcp-port=3307 \
--with-mysqld-user=gordonb \
--without-isam \
--without-innodb
gmake
(Note: I don't have root on this machine so I'm trying to
run it out of my own directory. I don't think that has
anything to do with this problem, though).
Compilation of getopt.c aborts with complaints about the
wrong number of arguments to readv() and writev().
precise description of the problem (multiple lines)
Fix:
I don't understand the problem that defining _NO_PROTO was
supposed to fix, but the problem goes away if that definition
is taken out.
Submitter-Id: submitter ID
Originator:Gordon L. Burditt
Organization:
Internet America
MySQL support: none [none | licence | email support | extended email support ]
Synopsis: MySQL 4.0.0 getopt.c fails to compile on OSF 5.1
Severity: critical
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.0-alpha (Source distribution)
Environment:
OSF1 version 5.1, gcc 3.0.1, gmake 3.79.1
Alpha DS-20 hardware
System: OSF1 beast V5.1 1885 alpha
Machine: alpha
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/local/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/alphaev6-dec-osf5.1/3.0.1/specs
Configured with: ../gcc-3.0.1/configure
Thread model: single
gcc version 3.0.1
Compilation info: CC='/usr/local/bin/gcc' CFLAGS='' CXX='/usr/local/bin/gcc'
CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root system17 Oct 8 12:29 /lib/libc.a - ../ccs/lib/libc.a
lrwxrwxrwx 1 root system17 Oct 8 12:29 /usr/lib/libc.a -
../ccs/lib/libc.a
Note that the configure command below is that of the PRODUCTION version
of MySQL on this machine, not the alternate server I'm trying to build.
Configure command: ./configure '--with-named-thread-libs=-lpthread -lmach -lexc'
--with-raid --localstatedir=/data0
-
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 4.0 compile fails on Linux]
Description: make[3]: Entering directory `/usr/local/src/mysql-4.0.0-alpha/sql' gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local\ - DDATADIR=\/usr/local/var\ -DSHAREDIR=\/usr/local/share/mysql \ -DHAVE_CONFIG_H -I../innobase/include -I./ ../include -I./../regex-I. -I../include -I. -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -c sql_lex.c c sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once sql_lex.cc:85: for each function it appears in.) sql_lex.cc:85: confused by earlier errors, bailing out make[3]: *** [sql_lex.o] Error 1 How-To-Repeat: make Fix: Unknown Submitter-Id: [EMAIL PROTECTED] Originator:Ed Carp Organization: Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London MySQL support: [none] Synopsis: MySQL 4.0 compile fails on Linux Severity: Priority: Category: mysql Class: Release: mysql-4.0.0 alpha Server: /usr/local/bin/mysqladmin Ver 8.21 Distrib 3.23.40, for pc-linux-gnu on i586 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.40 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 8 hours 18 min 5 sec Threads: 2 Questions: 106568 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 3.566 Environment: System: Linux adsl-208-191-206-105.dsl.rcsntx.swbell.net 2.2.17-14 #1 Mon Feb 5 17:53:36 EST 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 4 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so -rw-r--r--1 root root 20272704 Feb 29 2000 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 29 2000 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Apr 9 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure Perl: This is perl, version 5.005_03 built for i386-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MySQL 4.0.0 core dumps on build]
Stupid damned frigging spam filters... database, sql Forwarded Message: Ed Carp ([EMAIL PROTECTED]) writes: Ed Carp ([EMAIL PROTECTED]) writes: Description: ./configure;make produces core dump when conf_to_src runs. How-To-Repeat: make distclean;./configure;make Fix: Unknown. Editing the respective Makefile(s) and removing the first occurrance of -O3, removing conf_to_src.o, then re-running make seems to have worked, it let me get further in the build process... -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London -- Ed Carp, N7EKG - [EMAIL PROTECTED] - 214/341-4420 - http://www.pobox.com/~erc Squished Mosquito, Inc. Internet Applications Development Escapade Server-Side Scripting Language Development Team http://www.squishedmosquito.com Pensacola - Dallas - Dresden - London - 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
Implementing NEXT n BACK page in query
Dear list,
I would like to query on the first page only the last 50 records/rows (order
by ID desc) in
the following script file which uses MySQL, and I want to have NEXT page for
the 50 rows earlier than last 50 queried and continue similarly. I think I
can use
JavaScript for BACK page to go back. Could you pl add for me the script to
implement NEXT page or suggest me. There is LIMIT clause to use, but I only
know to use it to limit to certain number of query/rows.
Here is the script file:
html
body
?php
$db = mysql_connect(localhost, root, root);
mysql_select_db(penpal,$db);
if ($id)
$result = mysql_query(select * from penpal where id = $id,$db);
$myrow = mysql_fetch_array($result);
printf(bID:/b %s\nbr, $myrow[id]);
printf(bName:/b %s\nbr, $myrow[name]);
printf(bAge/Sex/Location:/b %s\nbr, $myrow[asl]);
printf(bDescription:/b %s\nbr, $myrow[description]);
printf(bEmail:/b %s\nbr, $myrow[email]);
} else {
$result = mysql_query(select * from penpal order by id desc, $db);
if ($myrow = mysql_fetch_array($result)) {
do {
printf(a href = \%s?id=%s\%s %s/abr\n, $PHP_SELF, $myrow[id],
$myrow[name], $myrow[asl]);
} while ($myrow = mysql_fetch_array($result));
} else {
echo Sorry, no records;
}
}
?
/body
/html
Thank you in advance.
NOBBY
-
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:difficulty compiling v4 --without-innodb
Hello Heikki! you probably should #undef HAVE_INNOBASE_DB in some file, maybe sql/handler.h Search with grep where the above has been defined. Having difficulty compiling v4-alpha --without-innodb. I am -not- a Sure did have that. A bunch of them in different files which is part of the reason I had such a tough time figuring out what was actually going on. Also had some other files that did *NOT* look familiar from other previous non-Apachetoolkit compiles. Then I found a reference in the toolkit log files stating 'CONFIGURING FOR INNODB.' Well, given that, toolkit apparently writes some config files, *.m4, that autoconf knows how to deal with but I don't know how to massage back to original. So I wiped the mysql subdir, untarred a new source tree, and configured and compiled clean with --without-innodb. Perfect. Guess I should have taken the easy way out right from the start. :) Apachetoolkit is a bit weird. -Sometimes- it asks me if I want to 'edit the php config file,' or the mysql config, or the apache config. -Or- not ask me, with no rhyme, reason, or consistency (as far as I could tell). Oh well, it worked for what I needed it to do. Thanks much. Have a :) day! jb -- jim barchuk [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
Help On Load Data File
Hi list member, I have a slight problem here. I am testing on the data convertion from text file into MySQL table. I am able to use the 'load data infile' command and insert the data into the table, however, with 1000 warnings. I don't know where I can see the warning messages ? Can you people help me to identify where MySQL store the warning / error messages generated? Thank you. Regards, Calvin Chin [EMAIL PROTECTED] Ipmuda Berhad - Building materials for a better world ! - 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: Help On Load Data File
Hi Calvin, Check out http://www.mysql.com/doc/L/O/LOAD_DATA.html and search for Warnings. A paragraph probably of particular interest to you is: Warnings occur under the same circumstances as when values are inserted via the INSERT statement (see section 6.4.3 INSERT Syntax), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row. The warnings are not stored anywhere; the number of warnings can only be used as an indication if everything went well. If you get warnings and want to know exactly why you got them, one way to do this is to use SELECT ... INTO OUTFILE into another file and compare this to your original input file. -Original Message- From: Calvin Chin [mailto:[EMAIL PROTECTED]] Sent: Sunday, October 28, 2001 9:08 PM To: [EMAIL PROTECTED] Subject: Help On Load Data File Importance: High Hi list member, I have a slight problem here. I am testing on the data convertion from text file into MySQL table. I am able to use the 'load data infile' command and insert the data into the table, however, with 1000 warnings. I don't know where I can see the warning messages ? Can you people help me to identify where MySQL store the warning / error messages generated? Thank you. Regards, Calvin Chin [EMAIL PROTECTED] Ipmuda Berhad - Building materials for a better world ! - 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: 3.23.43 fails merge test ON make test of src.
Michael Widenius wrote: Hi! R == R Talbot [EMAIL PROTECTED] writes: R Hi! rjtalbo == rjtalbo [EMAIL PROTECTED] writes: rjtalbo I am using Mysql 3.23.40 on Linux kernel 2.2.14 Caldera.. cut R Thanks for the response. R I did discover .. mysql-test-run --force in the manual after my R email..Sorry! R Tests failed were merge.. replace and rcp16 ( hope I got the last R one right) R Your binaries won't install on a Caldera System, not even the gnu R (generic).. What error do you get when trying to run mysqld from the Linux tar file? R Apparently something non-standard with Caldera Dir layout.. R I just updated all libraries, make, compiler.. This helped with R PostgreSQL compile R but not MySQL... R Spoke to Caldera, Kernal 2.2.14 reworked issues resolved patches by R Caldera added..! ok, lets try this from the start. It should not be that hard to get MySQL to work on Caldera. (I don't think the MERGE test is really critical, but it would be better to spend a little time to get this completely right). What gcc version do you have. What glibc version ? How did you configure MySQL ? Did you check the configure options from the Linux section in the MySQL manual ? R If I am to use this at all I suppose I will have to use early 3.23.xx R versions or R Change all the boxes on my Lan to Red Hat or S.u.SE.. R I have found, over the past six years, Caldera to be very Stable. I R attribute this to R the fact that it is reworked and never on the bleeding edge. As we can R see this R is not without trade offs. R Please note IBM feels the same way. The only Linuxes IBM supports are R Caldera R Red Hat, TurboLinux, SuSE. for its' Servers. Regards, Monty Hi Monty, Thank you for your reply.. It has taken me ten days to reply because of a lan issue and several down machines. I had been installing Mysql 3.23.42 on two OS2 machines and ran into some problems. The people on [EMAIL PROTECTED] helped me out. Yuri Dario's port of Mysql 3.23.42 to OS2 is great. Now back to my Linux install of Mysql 3.23.4x All my compiles of my Mysql above 3.22.32 fail merge, replace, and rpc16 Below is My bug report... Mysql 3.23.40 is runjning after the compile but, after a compile with errors 3.23.42 or .43 won't run the server starts and fails so I guess we should concentrate on 3.23.40 From: bobby [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: 3.23.4.x compiles but make test shows errors in merge, replace and rcp16 Description: See email above attached How-To-Repeat: COMPILE using (make 3.78.1) (gcc egcs-2.91.66) libraries libc-2.1.2.so libc.so.6 Fix:?? Submitter-Id: submitter ID Originator: R Talbot Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: serious Priority: high ] Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.40 (Source distribution) Server: /usr/local/mysql/bin/mysqladmin Ver 8.21 Distrib 3.23.40, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.40-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 hour 33 min 25 sec Threads: 1 Questions: 2 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 0 Queries per second avg: 0.000 Environment: machine, os, target, libraries (multiple lines) System: Linux fultower.ibmpeers 2.2.14 #1 SMP Thu Feb 17 11:49:42 MST 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Sep 2 2000 /lib/libc.so.6 - libc-2.1.2.so -rwxr-xr-x 1 root root 1249095 Feb 17 2000 /lib/libc-2.1.2.so -rw-r--r-- 1 root root 2266174 Feb 17 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Feb 17 2000 /usr/lib/libc.so lrwxrwxrwx 1 root root 34 Sep 2 2000 /usr/lib/libc.so.1 - ../../usr/i486-sysv4/lib/libc.so.1 lrwxrwxrwx 1 root root 14 Sep 2 2000 /usr/lib/libc-client.a - libc-client4.a -rw-r--r-- 1 root root 2033328 Feb 17 2000 /usr/lib/libc-client4.a Configure command: ./configure --prefix=/usr/local/mysql Perl: This is perl, version 5.005_03 built for i386-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Can not load database
Hello mysql I have been give the database files for a mysql database that I can not get to open and display records. I have run myisamchk, isamchk, mysqlcheck, mysqldump and all return the header does not support check/repair or I do get a bytesec error when I try to do the isamchk Data records: 11076 Wrong Bytesec: 1-162- 0 at 0; Skipped Data records: 0 Now I am using mysql 3.23 on win32 to view this and the possiblity of the database is a earlier version on linux is very high. Any Ideas on how to read the data? I really just need a mysqldump of the file, If you want to take a stab at it I will email you the isd,ism, frm files off list. 2.2megs I do not have any mdi, or mdy files for the database yet. Thanks in advance Sincerely Greg Maxwell - 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:difficulty compiling v4 --without-innodb
Did you mean V4 must ran with innodb? Oh! I have bad experience with innodb. It ate a lot my hard disk space. SF At 23:30 28/10/2001 -0500, jim barchuk wrote: Hello Heikki! you probably should #undef HAVE_INNOBASE_DB in some file, maybe sql/handler.h Search with grep where the above has been defined. Having difficulty compiling v4-alpha --without-innodb. I am -not- a Sure did have that. A bunch of them in different files which is part of the reason I had such a tough time figuring out what was actually going on. Also had some other files that did *NOT* look familiar from other previous non-Apachetoolkit compiles. Then I found a reference in the toolkit log files stating 'CONFIGURING FOR INNODB.' Well, given that, toolkit apparently writes some config files, *.m4, that autoconf knows how to deal with but I don't know how to massage back to original. So I wiped the mysql subdir, untarred a new source tree, and configured and compiled clean with --without-innodb. Perfect. Guess I should have taken the easy way out right from the start. :) Apachetoolkit is a bit weird. -Sometimes- it asks me if I want to 'edit the php config file,' or the mysql config, or the apache config. -Or- not ask me, with no rhyme, reason, or consistency (as far as I could tell). Oh well, it worked for what I needed it to do. Thanks much. Have a :) day! jb -- jim barchuk [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 - 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: pls help for index problem
hey, Well, how many records are in the table? How many would be returned by the query you present? My prefered (gw) table have 8313193 records for trail (on production should have 70,000,000 records approximately ) . And no. of records are returned have 71430 records by the query. Is the SQL you show the FULL sql? Yes, my full sql statement is that select * from gw where timerecord = '010902' . (Remarks, I want to find out data within a period for analyzing.) What is the output of the EXPLAIN SELECT Shown as below : table type possible_keys key key_len ref rows Extra gw ALL timerecord 8313193 where used What about SHOW INDEX FROM TABLE gw. mysql SHOW INDEX FROM gw; +---+--++---+--- ---+-+---+-+--+- --+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---+--++---+--- ---+-+---+-+--+- --+ | gw| 1 | timerecord |1 | timerecord | A | 119 | NULL | NULL | | | gw| 1 | esnindex|1 | esn | A | 113879 | NULL | NULL | | | gw| 1 | esnindex|2 | timerecord | A | 8313193 | NULL | NULL | | +---+--+---++--- ---+-+---+-+--+- --+ 3 rows in set (0.00 sec) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Thursday, October 25, 2001 6:09 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: FW: pls help for index problem Well, how many records are in the table? How many would be returned by the query you present? Is the SQL you show the FULL sql? What is the output of the EXPLAIN SELECT What about SHOW INDEX FROM TABLE gw. b. kmlau wrote: -Original Message- From: kmlau [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 9:42 AM To: 'Bill Adams' Subject: RE: pls help for index problem Thanks yr promptly reply !! It seems no any change(improvement) by running explain again after erase quotes. I also ran the command 'myisamchk -a gw.MYI' before sending this consulting mail !! Would U give me more advice ? regards, kmlau -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Tuesday, October 23, 2001 11:11 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: pls help for index problem kmlau wrote: I encountered a problem about indexing. I want to add index on timerecord field in table gw (shown as below) to speed up query relating with time. However, I use explain command (explain select * from gw where timerecord = '010902') to analyze the performace. As a result, it seems the query do not use this index. Would U tell me why and how to correct this !! U do not need to specify the timestamp as a string, e.g. remove the quotes: timerecord=10902. But more importantly run 'myisamchk -a' on the index (.MYI) file. Doing both of these will help. 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 - 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 -- Bill Adams TriQuint Semiconductor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL licencing [id:fns]
On Mon, 2001-10-29 at 05:41, Gabor Penoff wrote: Hi folks, We'd like to distribute a commercial software - written by ourselves - and we'd like to use MySQL as the SQL server solution. The question is: do we need to buy MySQL server licence or not? I read the MySQL licening page sometimes, but it's still not clean to me. We will use MySQL as a SQL server platform - but we san use a Postgres or any other standard SQL server instead of MySQL. In my opinion we do not need a MySQL server licence in this case - because our application is not depending directly on MySQL. Opinions? If you want really official answer, mail to [EMAIL PROTECTED] I forward current posting there. Those people are responsible about related topics. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Räpina, Estonia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ORDER BY bug; must also select column that is being ordered by
Hi! Luke Description: Luke Luke I have a table full of pages for a church website I am developing. Each has Luke some data (text) and a title, page id, updated (date) and created(date). I Luke want to select a list of pages with their id and title and updated date Luke according to when they were created (by created). When I select the id, Luke title, and updated date and sort by created, it displays the order wrong. Luke When I also tell it to select created, then it does it correctly. mysql SELECT pID, title, updated FROM pages WHERE category = '41' AND pID '' AND isCategory = 'n' AND isArchived = 'n' ORDER BY 'created'; The reason the above is NOT sorted correctly is that you have quotes around the 'created'; This means that you are sorting all rows according to the constant string 'created. Fix: Change 'created' to created Regards, Monty - 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: MyISAM API
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Wendy Dillon writes: Hi everyone, I realise that a number of people have already asked this but as it was a while ago thought more info may now be available: Is there any documentation for the MyISAM C API yet apart from the source code? In previous discussions of accessing MyISAM directly I see 2 issues that are frequently highlighted, the first being that the API is not available in client/server mode which does not affect my project and the second being that it is not possible to access the same data through SQL and MyISAM at the same time because of concurrency problems. This could be a major issue. Could anyone expand on this point? I can see from the source code that there is locking provided through calls to the MyISAM library, does anyone have any info on this? I would be grateful for any information, particularly from anyone who has directly accessed (or attempted to directly access) MyISAM databases, reading and writing records. Thanks, Wendy Sinisa Hi! Sinisa In 4.0.0 source code, you will find Docs subdir and in it myisam.txt. A couple of additional comments: In theory you can use MyISAM and MySQL on the same data if your system has reliable flock() calls and you are running the MySQL daemon with --enable-locking. Unfortunately not all systems, including Linux, doesn't have a very reliable lockd daemon so this may not always work as good at is should. Note that I haven't tested this on Linux 2.4, so things may work now. If you decide to use this, I recommend you to test this very throughly before putting this into production. Note that you can also directly access the MyISAM interface through the new HANDLER calls in MySQL 4.0! Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Utilizing the Database Server's Cache
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Jeremy Zawodny writes: Monty recently proposed some changes to MySQL's client/server protocol which will enable real prepared statements. So I'd guess it'll happen somewhere in the 4.x tree. Also just out of curiousity, how much of a performance hit does mysql take in compiling an execution plan? My understanding is that it's not a big deal unless you want to really pound your server with queries. My gut feel is that the effort which goes into parsing the query is similar (in order of magnitude) to actually deciding how to execute the query. The query cache will help the parsing overhead and the prepared queries will help the execution planning. cut A small additional comment: The prepared queries biggest advantage that one can send parameters in binary format, without quoting, between the server and client. This will speed up the communication protocol a great deal. Regards, Monty - 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
