browsing in search-results
I make a fulltext-search and store the results in a temporary table so the user can browser the temporary table (5 results per page...). I think this is of better performance instead of setting LIMIT $start,$elements. correct? what would happen if several visitors of the website make a search: each user a temorary table?? Thanx. -- fvgi242ss - Webmaster wlanhacking.de http://mail.wlanhacking.de/cgi-bin/mailman/listinfo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT() returns not correct character set
Dear MySQL fans,
I tested CONCAT() with binary strings and I got strange result.
Manual says:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the arguments include any binary strings, the result is a binary
string.
But the following test says:
bianry + latin1 latin1 (not bianry)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: It's party time!
On 2/25/06, Martijn Tonies [EMAIL PROTECTED] wrote: I'm sorry to say, there won't be any version for Linux. Not this year, at least. You can, however, connect to MySQL running on a Linux host. Martijn Tonies Too bad. Do you provide the winbox so that I can connect? 'Cause I've nothing but Kubuntu and Fedora at home. From what I've heard, Database Workbench works fine under Win4Lin and Wine. That's all I can say for now :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with transfer databases from different Mysql versions
Hi, all I have installed a portal server and which has Mysql 4.0.15-nt with it. Before I installed this server, I have Mysql 4.1 in my PC. But thing is that they will conflict and I have to delete the old Mysql service. So I used mysqldump to export the databases from the 4.1 version to the sql files. But when I use source to retrieve those database in the 4.0.15-nt version, it reports errors. Can anyone tell me how I should tackle this problem? I am also concerned what the -nt here means? Thanks a lot. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why do these transactions show table locks?
My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why do these transactions show table locks?
It might be important to note that I have a delete trigger on the ELEMS table, also, this INSERT call is being made from a stored procedure. The stored procedure only has one line, this INSERT statement. Could this have anything to do with bug# 16229? http://bugs.mysql.com/bug.php?id=16229 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 9:33 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Why do these transactions show table locks? My understanding is that innodb should not be using table locks for insert, update, or delete. However, the following transactions are showing table locks. What's up? R. ---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 4, query id 566875 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting -- ---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 6, query id 565737 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9 223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting -- ---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 5, query id 564870 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9 223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting -- ---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 250 MySQL thread id 7, query id 563809 squid.tripwire.com 10.150.1.30 root System lock INSERT INTO ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_ LCSEV) VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9 223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0) --- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not getting list email?
I subscribed to this list about four days ago and am not getting any mail. I see on the web page that there have been new posts. What is wrong. What can I do to fix it? -- Barton L. Phillips Applied Technology Resources, Inc. Tel: (818)652-9850 Web: http://www.applitec.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL query
Please does anybody knows the content of the CD that comes with MySQL 5 Certification Study Guide and how relevant it's to passing the exams? Can someone out there provide me with a link to his copy for download? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting COUNT() TO return 0 for null matches in a query, how?
I have the following query which counts the records from a table called registration that have an schedule_id that matches a record in another table called schedules. The below query works fine but how can I get it to return a COUNT() of 0 each instance where there is no record in the registration table that matches a schedules.id record? SELECT schedules.id, schedules.start_date, schedules.end_date, COUNT(schedules.id) FROM schedules, registration_and_attendance WHERE registration_and_attendance.schedule_id = schedules.id GROUP BY schedules.id ORDER BY start_date -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting COUNT() TO return 0 for null matches in a query, how?
Ferindo Middleton Jr wrote: I have the following query which counts the records from a table called registration that have an schedule_id that matches a record in another table called schedules. The below query works fine but how can I get it to return a COUNT() of 0 each instance where there is no record in the registration table that matches a schedules.id record? SELECT schedules.id, schedules.start_date, schedules.end_date, COUNT(schedules.id) FROM schedules, registration_and_attendanceWHERE registration_and_attendance.schedule_id = schedules.id GROUP BY schedules.id ORDER BY start_date Your query, rewritten to use an explicit join (with the join condition in the ON clause, where it belongs, rather than in the WHERE clause) and table aliases: SELECT s.id, s.start_date, s.end_date, COUNT(s.id) FROM schedules s JOIN registration_and_attendance ra ON ra.schedule_id = s.id GROUP BY s.id ORDER BY s.start_date; This query finds only rows from schedules that have matching entries in registration_and_attendance. As you have seen, you can't count what isn't there. If you change the JOIN to a LEFT JOIN, however, you are guaranteed to get an output row for every single id in schedules (the table on the left). For each schedules.id that is not present in registration_and_attendance, you get a result row with NULLs for each selected column in registration_and_attendance (the table on the right). Next, we need to take advantage of the fact that count(field) only counts non-NULL values of field. The key is to count something in the table on the right, say registration_and_attendance.schedule_id, because it will be NULL (and have a 0 count) when there are no matches. Thus, I believe the query you want is SELECT s.id, s.start_date, s.end_date, COUNT(ra.schedule_id) FROM schedules s LEFT JOIN registration_and_attendance ra ON ra.schedule_id = s.id GROUP BY s.id ORDER BY s.start_date; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Heikki. I found the ibdata1 and ib_log files located in only '/var/lib/mysql' which is defined as the mysql home folder. The last time my.cnf was editied was on the 9th of Dec, and the database was created somewhere in the month of January. So the database would have been created with the same configurations. Thankfully, I have a dump of the original database. Looks like I have to restore all of the 35 GB of data again. Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Saturday, February 25, 2006 4:05 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, ok, then the most probable explanation is that someone had edited my.cnf earlier, when mysqld was running. When mysqld was restarted, it read the new my.cnf, and got confused. Please ask your sysadmins to scan the file system of the computer if they can find another ibdata1 file or ib_logfiles. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, February 25, 2006 12:14 PM Subject: RE: error 1016 : cant open ibd file even though it exists --=_NextPart_000_001D_01C63A22.BB0C91A0 Content-Type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: 7bit Hello David. There was supposed to be a power outage in our office that day. So the server was shut down. Finally when the power was back, the machine was plugged on. That's all. No file system change. Nothing. Regards, Rithish. -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Saturday, February 25, 2006 3:32 PM To: Rithish Saralaya Cc: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Hi Rithish, After reading Heikkis points, I am inclined to agree. Did your sysadmins change a filesystem during the maintenance? Did they forget to restore a directory if they changed disks? What was the maintenance that was performed? Your InnoDB files disappeared at some point because the server would not have recreated them otherwise. I am sure it was a graceful shutdown, but something has changed. These things just don't happen by themselves. Regards Rithish Saralaya wrote: The mysql server was shut down for maintenance. However it was a graceful shutdown and restart. That's all. No files were touched or removed. How could this have happened, I fail to see. Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, February 24, 2006 3:55 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file
