Re: Databases are used to store data,
Databases are used to store data This line spoke the loudest to me. Over the years I had become very proficient with FileMaker Pro's built in scripting language. I had even gotten FileMaker to construct the web pages that would be used to connect to FileMaker (including writing the page's Javascript data validation). The line between database and processing language had become very blurred for me. I'll take your advice and keep the XHTML coding in PHP (but I will use Steps 1 and 2 to create the imgName that PHP will use). I'd say it's a wrong statement to make. Database are used to store and guarantee valid data as much as possible would be much better, IMO. Add to that, that we have triggers, stored routines etc, and we have an active database. 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]
adding 3 values
Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R.
RE: adding 3 values
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 10:08 To: mysql@lists.mysql.com Subject: adding 3 values Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R. SELECT SUM(single_rooms, double_rooms, twin_rooms) from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF(SUM(single_rooms, double_rooms, twin_rooms) 10,1,0) from TABLE; Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: adding 3 values
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 10:08 To: mysql@lists.mysql.com Subject: adding 3 values Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R. SELECT SUM(single_rooms, double_rooms, twin_rooms) from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF(SUM(single_rooms, double_rooms, twin_rooms) 10,1,0) from TABLE; Sorry, brain was switched off when I wrote that. It should be: SELECT single_rooms+double_rooms+twin_rooms from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF((single_rooms+double_rooms+twin_rooms) 10,1,0) from TABLE; Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding 3 values
Ok, I have this so far $query = SELECT * FROM properties where single_rooms+double_rooms+twin_rooms10 and rent 100; This is fine but what I really want to do it this $query = SELECT * FROM properties WHERE single_rooms+double_rooms+twin_rooms10 AND single_rooms+double_rooms+twin_rooms10 AND rent 100; This is starting to get messy. Can I set up an alias for the total? I tried this without success. $query = SELECT *, single_rooms+double_rooms+twin_rooms AS total FROM properties WHERE total 2 AND total 10 R. - Original Message - From: Edward Kay [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, May 10, 2007 10:17 AM Subject: RE: adding 3 values -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 10:08 To: mysql@lists.mysql.com Subject: adding 3 values Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R. SELECT SUM(single_rooms, double_rooms, twin_rooms) from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF(SUM(single_rooms, double_rooms, twin_rooms) 10,1,0) from TABLE; Sorry, brain was switched off when I wrote that. It should be: SELECT single_rooms+double_rooms+twin_rooms from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF((single_rooms+double_rooms+twin_rooms) 10,1,0) from TABLE; Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Thu, May 10, 2007 at 03:37:26AM +0100, Jon Ribbens wrote: I've just upgraded all the clients and servers to 5.0.41 (which looks like it just came out); I'll see what happens. It hasn't solved the problem, but it has changed the error message to: OperationalError: (2013, Lost connection to MySQL server at 'reading authorization packet', system error: 0) (still nothing in the logfile). Does this ring any bells for anyone? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Enterprise 5.0 x64 Intel C++
I'm a customer of MySQL Enterprise 5.0 The binary for Red Hat Enterprise 5 x64 compiled with Intel C++ does't exists anymore ? Tnks ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequently MyISAM TABLE corruption.....Pls help
Hello Gerald, Data_lengthMax_data_length Index_length 596483288281474976710655 33758208 580 MB is table size and 32MB is index size. The default maximum MyISAM size is 4GB. Now, tell me where is an issue? regards, Nilnandan Joshi DBA-SDU Juriscape Gerald L. Clark-2 wrote: Nilnandan wrote: Hello all, I have one server which has mysql 5.0.27 installed. There is one table named table1. that table has 122000 records..It has 114 fields and 22 indexes. Now this table always been corrupt. I have try to found the solution but i couldn't. Pls help me ASAP. I have used CHECK and REPAIR option I have given here the output. 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Table 'table1' is marked as crashed and should be repaired 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Sort aborted How big is the index file? the data file? Has either reached the file size limit of your filesystem, or the default maximum MyISAM size? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Frequently-MyISAM-TABLE-corruption.Pls-help-tf3715472.html#a10412877 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
At 06:03 AM 5/10/2007, you wrote: reading authorization packet If you do a google search: http://www.google.ca/search?q=lost+mysql+connection+%22reading+authorization+packet%22hl=enstart=90sa=N you'll find about a hundred web sites encountering the exact same error. Ironically these errors were caught by the google indexing engine when the site failed to display the HTML page, so it got the error message instead. If you click on the link today, the error of course is gone. But if you click on the cache link, you'll see the same error message as it occurred (frozen in time as it were). Maybe the problem is with the hardware, like your network card disconnecting from the MySQL server machine? Or TCP/IP? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequently MyISAM TABLE corruption.....Pls help
Some additional resources for fixing corrupt MyISAM tables: http://dev.mysql.com/doc/refman/5.0/en/myisam-table-problems.html http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html , especially: http://dev.mysql.com/doc/refman/5.0/en/repair.html I don't know for sure as it will depend on your data and your indices, but with 580 MB of data, 122000 records, and 22 indices, I would expect that your index file should be larger than it is (just 32 MB). I would suggest that perhaps your index file is corrupt and needs to be rebuilt - look at the stage 2 and stage 3 procedures mentioned in the repair page (last URL above). Be sure to make backup copies! Read it and understand it before you do it! Best of luck, Dan On 5/10/07, Nilnandan [EMAIL PROTECTED] wrote: Hello Gerald, Data_lengthMax_data_length Index_length 596483288281474976710655 33758208 580 MB is table size and 32MB is index size. The default maximum MyISAM size is 4GB. Now, tell me where is an issue? regards, Nilnandan Joshi DBA-SDU Juriscape Gerald L. Clark-2 wrote: Nilnandan wrote: Hello all, I have one server which has mysql 5.0.27 installed. There is one table named table1. that table has 122000 records..It has 114 fields and 22 indexes. Now this table always been corrupt. I have try to found the solution but i couldn't. Pls help me ASAP. I have used CHECK and REPAIR option I have given here the output. 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Table 'table1' is marked as crashed and should be repaired 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Sort aborted How big is the index file? the data file? Has either reached the file size limit of your filesystem, or the default maximum MyISAM size? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Frequently-MyISAM-TABLE-corruption.Pls-help-tf3715472.html#a10412877 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding 3 values
This is starting to get messy. Can I set up an alias for the total? That's exactly what HAVING is for. PB [EMAIL PROTECTED] wrote: Ok, I have this so far $query = SELECT * FROM properties where single_rooms+double_rooms+twin_rooms10 and rent 100; This is fine but what I really want to do it this $query = SELECT * FROM properties WHERE single_rooms+double_rooms+twin_rooms10 AND single_rooms+double_rooms+twin_rooms10 AND rent 100; This is starting to get messy. Can I set up an alias for the total? I tried this without success. $query = SELECT *, single_rooms+double_rooms+twin_rooms AS total FROM properties WHERE total 2 AND total 10 R. - Original Message - From: Edward Kay [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, May 10, 2007 10:17 AM Subject: RE: adding 3 values -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 10:08 To: mysql@lists.mysql.com Subject: adding 3 values Hi, I have 3 integer values in the table single_rooms, double_rooms, twin _ooms but want to add them all up to do a comparison to see if the combined number of rooms is less than ten. Ta, R. SELECT SUM(single_rooms, double_rooms, twin_rooms) from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF(SUM(single_rooms, double_rooms, twin_rooms) 10,1,0) from TABLE; Sorry, brain was switched off when I wrote that. It should be: SELECT single_rooms+double_rooms+twin_rooms from TABLE; Or, if you want a boolean value depending if there are less than 10: SELECT IF((single_rooms+double_rooms+twin_rooms) 10,1,0) from TABLE; Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysterious 'Lost connection' errors
On Thu, May 10, 2007 at 08:58:37AM -0600, mos wrote: If you do a google search: http://www.google.ca/search?q=lost+mysql+connection+%22reading+authorization+packet%22hl=enstart=90sa=N you'll find about a hundred web sites encountering the exact same error. Indeed, I noticed that ;-) Maybe the problem is with the hardware, like your network card disconnecting from the MySQL server machine? Or TCP/IP? The only comment I could find from a MySQL employee suggested that. But I don't believe them. There are no other network problems visible, and the 'authorization packet' is mid-way through the connection setup (i.e. the TCP/IP connection has already been successfully used to both send and receive data if we get as far as the 'authorization packet'). I suspect some sort of bug in the MySQLd authentication code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
On 5/9/07, James Tu [EMAIL PROTECTED] wrote: The database server and the web server are on separate machines. Table A contains a record for each user. Let's say Table B contains 'relationship' information. They can be of type 'friend' or 'family'. If a user knows another user, this relationship would be kept in this table, along with the type of relationship. Table B can get big. 10,000's or maybe 100,000's. I'm doing a query in PHP and want to end up with two arrays. One for type friend and one for type family. Which is better: (Method 1) Do ONE query for all the records that meet a certain criteria (let's say 'active'). Then use PHP to loop through the results and put each record into either the friend array or the family array. (Method 2) Do TWO queries. One just for friend. Loop through the records and put into friend array; Then do another query for family...and loop through again. In general, you don't want to introduce arbitrarily large result sets into PHP. PHP is fast, but there are memory limits and speed of iteration limits. In general, you want to structure things so that MySQL returns exactly the results you need, and in the order you need. In general: a)Check your database design to be sure that the queries you are interested in are O(log N). If not, make them that way, by rethinking your database design and/or adding indexes. b)See if you can get all the data you want in one query. In the example you gave, I think the WHERE clause syntax will allow checking for certain of an enumerated type, i.e WHERE (X=3 OR X=5) ... that kind of thing. So, retrieving friends and family in one query shouldn't be a problem. Two queries should not be required. Here is what you need to remember: a)Designs that aren't O(log N) for the queries you are interested in often catch up with you as the database grows. b)There is a speed hierarchy involved. PHP is the slowest of all, so if you loop over records in PHP it needs to be a guaranteed small set. MySQL takes a one-time hit parsing the SQL statement, but after that it can operate on the database FAR faster than PHP can. In general, let MySQL do the work, because it can do the sorting, filtering, etc. FAR faster than PHP. Dave.
Join Error
I'm running the following query: SELECT S.Name As School,S.State, CASE WHEN Unfinished.Cnt IS NULL THEN 'Yes' ELSE 'bNo/b' END As AllSubmitted, COALESCE(Part.Cnt,0) As StudentCount, COALESCE(Adv.Cnt,0) As AdvisorCount FROM InvHead I JOIN Schools S On S.ID=I.ChapterID LEFT OUTER JOIN (SELECT I2.ChapterID,Count(*) As Cnt FROM InvHead I2 WHERE I2.RegFinishedDate IS NULL GROUP BY I2.ChapterID) AS Unfinished ON Unfinished.ChapterID=S.ID LEFT OUTER JOIN (SELECT ChapterID,Count(*) As Cnt FROM Participants P JOIN StatusCodes S ON S.Code=P.Status WHERE S.PersonType='S' GROUP BY ChapterID) AS Part ON Part.ChapterID=S.ID LEFT OUTER JOIN (SELECT ChapterID,Count(*) As Cnt FROM Participants P JOIN StatusCodes S ON S.Code=P.Status WHERE S.PersonType='A' GROUP BY ChapterID) AS Adv ON Adv.ChapterID=S.ID GROUP BY S.State, S.Name ORDER BY S.State,S.Name When I run this through my asp.net application, I get the error, #42000The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay When I refresh, ir just backup and try again, it runs fine. I've tried setting SQL_BIG_SELECTS=1 in my.ini, but MySQL won't re-start in services when I do that. MAX_JOIN_SIZE is set to 4294967295. That seems pretty huge to me, But, I guess I can change it, but what do I change it to? Or, is there a better way to do this by changing my query? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
Thanks David! This the kind of answer that I was looking for (more about general PHP and MySQL performance) I think b/c of the way the tables are designed, I have to perform multiple queries, unfortunately. I think I'll have to do some performance testing at some point. But for now I will let MySQL do the work instead of filtering with an IF in PHP. -James On May 10, 2007, at 12:20 PM, David T. Ashley wrote: On 5/9/07, James Tu [EMAIL PROTECTED] wrote: The database server and the web server are on separate machines. Table A contains a record for each user. Let's say Table B contains 'relationship' information. They can be of type 'friend' or 'family'. If a user knows another user, this relationship would be kept in this table, along with the type of relationship. Table B can get big. 10,000's or maybe 100,000's. I'm doing a query in PHP and want to end up with two arrays. One for type friend and one for type family. Which is better: (Method 1) Do ONE query for all the records that meet a certain criteria (let's say 'active'). Then use PHP to loop through the results and put each record into either the friend array or the family array. (Method 2) Do TWO queries. One just for friend. Loop through the records and put into friend array; Then do another query for family...and loop through again. In general, you don't want to introduce arbitrarily large result sets into PHP. PHP is fast, but there are memory limits and speed of iteration limits. In general, you want to structure things so that MySQL returns exactly the results you need, and in the order you need. In general: a)Check your database design to be sure that the queries you are interested in are O(log N). If not, make them that way, by rethinking your database design and/or adding indexes. b)See if you can get all the data you want in one query. In the example you gave, I think the WHERE clause syntax will allow checking for certain of an enumerated type, i.e WHERE (X=3 OR X=5) ... that kind of thing. So, retrieving friends and family in one query shouldn't be a problem. Two queries should not be required. Here is what you need to remember: a)Designs that aren't O(log N) for the queries you are interested in often catch up with you as the database grows. b)There is a speed hierarchy involved. PHP is the slowest of all, so if you loop over records in PHP it needs to be a guaranteed small set. MySQL takes a one-time hit parsing the SQL statement, but after that it can operate on the database FAR faster than PHP can. In general, let MySQL do the work, because it can do the sorting, filtering, etc. FAR faster than PHP. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
On 5/10/07, James Tu [EMAIL PROTECTED] wrote: I think b/c of the way the tables are designed, I have to perform multiple queries, unfortunately. Hi James, My suggestion to you would be that if you have a situation you don't believe you can handle in one query, post all the details to the MySQL list and let others take a whack at it. I've not yet encountered a situation where the database can't be designed for one query results. I also believe that MySQL has temporary table functionality: http://www.xaprb.com/blog/2006/03/26/temporary-table-subtleties-in-mysql/ http://www.devwebpro.com/devwebpro-39-20010817Temporary-Tables-With-MySQL.html I don't know how this works (I guess I should read the manual), but I think this would give you the ability in many cases to have MySQL (rather than PHP) do the heavy lifting. It will be much more efficient in MySQL than in PHP. Good luck, Dave.
Re: Which is a better design?
David:
I definitely can get the result set using one query, but what I do
with the result set has me thinking about breaking it up into two
queries.
Here's an example with a simple table:
describe collection;
+--+-+--+-
+-++
| Field| Type| Null | Key |
Default | Extra |
+--+-+--+-
+-++
| id | bigint(20) unsigned | | PRI |
NULL| auto_increment |
| receiver_id | bigint(20) unsigned | | MUL |
0 ||
| set_type_id | int(2) unsigned | | |
0 ||
| card_id | int(3) unsigned | | |
0 ||
| completed_set_id | bigint(20) unsigned | | |
0 ||
| created_on_gmt | datetime| | | -00-00
00:00:00 ||
+--+-+--+-
+-++
I want to end up with two PHP arrays. One for set_type_id = 22 and
one for set_type_id=21.
(1) one query method:
SELECT * from collection WHERE set_type_id=22 OR set_type_id=21;
...do query...
while( $row = $this-db-fetch_array_row() ){
if ($row['set_type_id'] == 21){
$array_a[] = $row;
} else {
$array_b[] = $row;
}
}
(2) two query method:
SELECT * from collection WHERE set_type_id=22;
...do query...
while( $row = $this-db-fetch_array_row() ){
$array_a[] = $row;
}
SELECT * from collection WHERE set_type_id=21;
...do query...
while( $row = $this-db-fetch_array_row() ){
$array_b[] = $row;
}
Which method is better? I still think that based on David's comments
regarding MySQL being more performative I'm leaning towards option (2).
-James
On May 10, 2007, at 12:54 PM, David T. Ashley wrote:
On 5/10/07, James Tu [EMAIL PROTECTED] wrote:
I think b/c of the way the tables are designed, I have to perform
multiple queries, unfortunately.
Hi James,
My suggestion to you would be that if you have a situation you
don't believe
you can handle in one query, post all the details to the MySQL list
and let
others take a whack at it.
I've not yet encountered a situation where the database can't be
designed
for one query results.
I also believe that MySQL has temporary table functionality:
http://www.xaprb.com/blog/2006/03/26/temporary-table-subtleties-in-
mysql/
http://www.devwebpro.com/devwebpro-39-20010817Temporary-Tables-With-
MySQL.html
I don't know how this works (I guess I should read the manual), but
I think
this would give you the ability in many cases to have MySQL (rather
than
PHP) do the heavy lifting. It will be much more efficient in MySQL
than in
PHP.
Good luck, Dave.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Community Server 5.0.41 has been released
Dear MySQL users, MySQL Community Server 5.0.41, a new version of the popular Open Source Database Management System, has been released. The release is now available in source and binary form from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes from version to version in the MySQL source code as compared to the last released version of MySQL Community Server, the MySQL Community Server 5.0.37 release. It can also be viewed online at http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-41.html Functionality added or changed: * If you use SSL for a client connection, you can tell the client not to authenticate the server certificate by specifying neither --ssl-ca nor --ssl-capath. The server still verifies the client according to any applicable requirements established via GRANT statements for the client, and it still uses any --ssl-ca/--ssl-capath values that were passed to server at startup time. (Bug#25309: http://bugs.mysql.com/25309) * Prefix lengths for columns in SPATIAL indexes are no longer displayed in SHOW CREATE TABLE output. mysqldump uses that statement, so if a table with SPATIAL indexes containing prefixed columns is dumped and reloaded, the index is created with no prefixes. (The full column width of each column is indexed.) (Bug#26794: http://bugs.mysql.com/26794) * The output of mysql --xml and mysqldump --xml now includes a valid XML namespace. (Bug#25946: http://bugs.mysql.com/25946) * The mysql_create_system_tables script was removed because mysql_install_db no longer uses it in MySQL 5.0. * The syntax for index hints has been extended to enable explicit specification that the hint applies only to join processing. See Section 13.2.7.2, Index Hint Syntax. (Bug#21174: http://bugs.mysql.com/21174) * Binary distributions for some platforms did not include shared libraries; now shared libraries are shipped for all platforms except AIX 5.2 64-bit. (Bug#13450: http://bugs.mysql.com/13450, Bug#16520: http://bugs.mysql.com/16520, Bug#26767: http://bugs.mysql.com/26767) * NDB Cluster: It is now possible to restore selected databases or tables using ndb_restore. (Bug#26899: http://bugs.mysql.com/26899) * NDB Cluster: Several options have been added for use with ndb_restore --print_data to facilitate the creation of data dump files. (Bug#26900: http://bugs.mysql.com/26900) * To satisfy different user requirements, we provide several servers. mysqld is an optimized server that is a smaller, faster binary. Each package now also includes mysqld-debug, which is compiled with debugging support but is otherwise configured identically to the non-debug server. * Added the --secure-file-priv option for mysql-test-run.pl, which limits the effect of the load_file command for mysqltest and for the LOAD DATA and SELECT ... INTO OUTFILE statements to work with files in a given directory. (Bug#18628: http://bugs.mysql.com/18628) * Added the hostname system variable, which the server sets at startup to the server hostname. * The server now includes a timestamp in error messages that are logged as a result of unhandled signals (such as mysqld got signal 11 messages). (Bug#24878: http://bugs.mysql.com/24878) Bugs fixed: * The patches for Bug#19370: http://bugs.mysql.com/19370 and Bug#21789: http://bugs.mysql.com/21789 were reverted. * NDB Cluster: NDB tables having MEDIUMINT AUTO_INCREMENT columns were not restored correctly by ndb_restore, causing spurious duplicate key errors. This issue did not affect TINYINT, INT, or BIGINT columns with AUTO_INCREMENT. (Bug#27775: http://bugs.mysql.com/27775) * NDB Cluster: NDB tables with indexes whose names contained space characters were not restored correctly by ndb_restore (the index names were truncated). (Bug#27758: http://bugs.mysql.com/27758) * NDB Cluster: Some queries that updated multiple tables were not backed up correctly. (Bug#27748: http://bugs.mysql.com/27748) * NDB Cluster: Joins on multiple tables containing BLOB columns could cause data nodes run out of memory, and to crash with the error NdbObjectIdMap::expand unable to expand. (Bug#26176: http://bugs.mysql.com/26176) * NDB Cluster (APIs): Using NdbBlob::writeData() to write data in the middle of an existing blob value (that is, updating the value) could overwrite some data past the end of the data to be changed. (Bug#27018: http://bugs.mysql.com/27018) * NDB Cluster: Under certain rare circumstances, DROP TABLE or TRUNCATE of an
Re: Question on InnoDB support
Hi Waldo, all! [EMAIL PROTECTED] wrote: I'm making an assessment of MySQL possible official adoption in my company. A question I have is: with InnoDB codebase being owned by Oracle, is there any impact to its reliability and support? Any other issues? The developers of InnoDB have never been employees of MySQL AB but rather worked for an independent company, Innobase Oy (AFAIR). There were contracts between Innobase and MySQL about cooperation and support, and this worked well to the satisfaction of all parties (including the customers). Last year, *after* Innobase was bought by Oracle, these contracts were renewed (the old period had expired), and cooperation and support continue as before. InnoDB developers continue to be active on this mailing list and in MySQL bug handling, so I see no reason to be concerned. Regards, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com MySQL GmbH, Hauptsitz: Radlkoferstr. 2, D-81373 München Geschäftsführer: Kaj Arnö - HRB München 162140 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
On 5/10/07, James Tu [EMAIL PROTECTED] wrote: David: I definitely can get the result set using one query, but what I do with the result set has me thinking about breaking it up into two queries. Technical Details Omitted Ah, OK, I misunderstood. You want to (get two results, each of which is useful individually) rather than (issue two queries, then iterate in PHP to combine the query results). Two queries seem fine in that case. Just one caution: be aware that another process (such as a web user) can sneak in in between your two queries and modify the database and render the two sets of query results inconsistent with one another. To give you an example, suppose you issue three queries in order (I'm going to botch the syntax here): SELECT COUNT(*) FROM USERS; (call this A) SELECT COUNT(*) FROM USERS WHERE IDX = 10; (call this B) SELECT COUNT(*) FROM USERS WHERE IDX 10; (call this C) It is very possible (in the presence of other simultaneous database activity) that A != B + C. It depends on the application whether this is significant. Table locking is the easiest way to prevent this if it matters. Dave.
dbnightly maintenance backup script
I wrote a perl script to handle all of our regular mysql maintenance tasks, which I thought might be useful to others. It's meant for an enviroment with binary logging turned on, but is fairly flexible. Although if you're backing up multiple databases you'll have to modify it a bit, since in our case we only care about backing up one database (if you do modify it for multidatabase, please send me your diffs). http://thwip.sysadmin.org/dbnightly The syntax is: dbnightly [action [action ...]] It will perform the actions in the order you give them on its commandline. Actions it knows how to do are: 1. maint - Run a bunch of SQL queries for nightly maintenace (put the queries you want in the DBMAINT section of the script) 2. full - a full mysqldump, into the backup directory, gzip'ed and with the database name and datetimestamp in the filename 3. partial - a partial mysqldump of a list of tables you choose, into the backup directory, gzip'd 4. flush - flush binary logs 5. logs - copy new or modified binary logs to the backup directory and gzip them, delete any that have been deleted from the mysql directory, and don't copy gzip ones that haven't changed since last backed up The resulting backup directory is all gzip'ed and suitable for rsync'ing. We run it from crontab, and it produces output like this: 2007-05-10 06:00 dbnightly: Database maintenace Table Op Msg_typeMsg_text databasename.tablename optimizestatus OK 2007-05-10 06:00 dbnightly: Database maintenance done 2007-05-10 06:00 dbnightly: Partial dump of databasename to /home/maintusr/backups 2007-05-10 06:01 dbnightly: Partial dump complete: databasename-partial.sql 2007-05-10 06:02 dbnightly: Flushing binary logs 2007-05-10 06:02 dbnightly: Copying /var/lib/mysql/binlogfile.90 to /home/maintusr/backups 2007-05-10 06:02 dbnightly: Copying /var/lib/mysql/binlogfile.91 to /home/maintusr/backups 2007-05-10 06:02 dbnightly: Done It also syslogs, like this: May 10 05:00:01 hostname dbnightly: Database maintenace May 10 05:00:04 hostname dbnightly: Database maintenance done May 10 05:00:04 hostname dbnightly: Partial dump of databasename to /home/maintusr/backups May 10 05:01:18 hostname dbnightly: Partial dump complete: databasename-partial.sql May 10 05:02:14 hostname dbnightly: Flushing binary logs May 10 05:02:15 hostname dbnightly: Copying /var/lib/mysql/binlogfile.90 to /home/maintusr/backups May 10 05:02:38 hostname dbnightly: Copying /var/lib/mysql/binlogfile.91 to /home/maintusr/backups May 10 05:02:39 hostname dbnightly: Done Both of these are from dbnightly maint partial flush logs, which we run 6 nights a week. On the other night, we run dbnightly maint full logs (no need to flush because --flush-logs is in the $fulldump options). Note: the dirsyncgz script I posted recently was a modified version of the binlogs subroutine from this script (dbnightly was not complete yet) -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] http://thwip.sysadmin.org/ cos, is perl God? 'No, Larry Wall is God. Perl is the Language of God. But I thought you don't believe in God? That's OK, I don't believe in Larry Wall either. -- a conversation with Mike Sackton over lunch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
If you are dong as two seperate queries, I recommend using a transactional table type setting the read isolation mode to repeatable read and doing both your queries within a single transaction. (David, sorry about the double send) - michael On 5/10/07, David T. Ashley [EMAIL PROTECTED] wrote: On 5/10/07, James Tu [EMAIL PROTECTED] wrote: David: I definitely can get the result set using one query, but what I do with the result set has me thinking about breaking it up into two queries. Technical Details Omitted Ah, OK, I misunderstood. You want to (get two results, each of which is useful individually) rather than (issue two queries, then iterate in PHP to combine the query results). Two queries seem fine in that case. Just one caution: be aware that another process (such as a web user) can sneak in in between your two queries and modify the database and render the two sets of query results inconsistent with one another. To give you an example, suppose you issue three queries in order (I'm going to botch the syntax here): SELECT COUNT(*) FROM USERS; (call this A) SELECT COUNT(*) FROM USERS WHERE IDX = 10; (call this B) SELECT COUNT(*) FROM USERS WHERE IDX 10; (call this C) It is very possible (in the presence of other simultaneous database activity) that A != B + C. It depends on the application whether this is significant. Table locking is the easiest way to prevent this if it matters. Dave. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dbnightly maintenance backup script
Thanks for sharring Ofer. I'll throw the one I wrote and use into the mix too. http://daevid.com/examples/daily_backup_tgz.sh Simply put it in your /etc/cron.daily/ And then every so often monitor /backups/ and delete stuff that's getting old. (it does some cleanup) d -Original Message- From: Ofer Inbar [mailto:[EMAIL PROTECTED] Sent: Thursday, May 10, 2007 12:24 PM To: mysql@lists.mysql.com Subject: dbnightly maintenance backup script I wrote a perl script to handle all of our regular mysql maintenance tasks, which I thought might be useful to others. It's meant for an enviroment with binary logging turned on, but is fairly flexible. Although if you're backing up multiple databases you'll have to modify it a bit, since in our case we only care about backing up one database (if you do modify it for multidatabase, please send me your diffs). http://thwip.sysadmin.org/dbnightly The syntax is: dbnightly [action [action ...]] It will perform the actions in the order you give them on its commandline. Actions it knows how to do are: 1. maint - Run a bunch of SQL queries for nightly maintenace (put the queries you want in the DBMAINT section of the script) 2. full - a full mysqldump, into the backup directory, gzip'ed and with the database name and datetimestamp in the filename 3. partial - a partial mysqldump of a list of tables you choose, into the backup directory, gzip'd 4. flush - flush binary logs 5. logs - copy new or modified binary logs to the backup directory and gzip them, delete any that have been deleted from the mysql directory, and don't copy gzip ones that haven't changed since last backed up The resulting backup directory is all gzip'ed and suitable for rsync'ing. We run it from crontab, and it produces output like this: 2007-05-10 06:00 dbnightly: Database maintenace Table Op Msg_typeMsg_text databasename.tablename optimizestatus OK 2007-05-10 06:00 dbnightly: Database maintenance done 2007-05-10 06:00 dbnightly: Partial dump of databasename to /home/maintusr/backups 2007-05-10 06:01 dbnightly: Partial dump complete: databasename-partial.sql 2007-05-10 06:02 dbnightly: Flushing binary logs 2007-05-10 06:02 dbnightly: Copying /var/lib/mysql/binlogfile.90 to /home/maintusr/backups 2007-05-10 06:02 dbnightly: Copying /var/lib/mysql/binlogfile.91 to /home/maintusr/backups 2007-05-10 06:02 dbnightly: Done It also syslogs, like this: May 10 05:00:01 hostname dbnightly: Database maintenace May 10 05:00:04 hostname dbnightly: Database maintenance done May 10 05:00:04 hostname dbnightly: Partial dump of databasename to /home/maintusr/backups May 10 05:01:18 hostname dbnightly: Partial dump complete: databasename-partial.sql May 10 05:02:14 hostname dbnightly: Flushing binary logs May 10 05:02:15 hostname dbnightly: Copying /var/lib/mysql/binlogfile.90 to /home/maintusr/backups May 10 05:02:38 hostname dbnightly: Copying /var/lib/mysql/binlogfile.91 to /home/maintusr/backups May 10 05:02:39 hostname dbnightly: Done Both of these are from dbnightly maint partial flush logs, which we run 6 nights a week. On the other night, we run dbnightly maint full logs (no need to flush because --flush-logs is in the $fulldump options). Note: the dirsyncgz script I posted recently was a modified version of the binlogs subroutine from this script (dbnightly was not complete yet) -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] http://thwip.sysadmin.org/ cos, is perl God? 'No, Larry Wall is God. Perl is the Language of God. But I thought you don't believe in God? That's OK, I don't believe in Larry Wall either. -- a conversation with Mike Sackton over lunch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'? [SOLVED]
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]
Sent: Friday, May 04, 2007 1:22 AM
To: mysql@lists.mysql.com
Subject: How do I find products when a user types freeform
strings like 'Sony 20 TV' or '20 Sony TV'?
I'm having trouble figuring out the logic/query I want.
I know that all those ORs are not right.
I'm doing this in PHP and mySQL (of course),
so if it can't be done with a single query, I can split it up.
Here's the challenge, given a text field search box, someone enters:
Sony 20 TV
How do I search for that, not knowing which fields are which?
For example, they could have also entered:
20 Sony TV
This is the one I have now, but (as you probably noticed), it
will return many rows,
I expect that most of the time 1 row will be returned, but
I'm getting a grip more than I want (or the customer would want), and
also rows that have nothing to do with the search terms.
SELECT products.*, companies.name AS company_name,
categories.name AS category_name
FROM products
LEFT JOIN companies ON company_id = companies.id
LEFT JOIN categories ON category_id = categories.id
WHERE products.enabled = 1
AND(
(products.model LIKE 'sony%' OR products.model
LIKE '20%' OR products.model LIKE 'tv%')
OR (products.upc LIKE 'sony' OR products.upc LIKE
'20' OR products.upc LIKE 'tv')
OR (products.name LIKE '%sony%' OR products.name
LIKE '20%' OR products.name LIKE '%tv%')
OR (companies.name LIKE 'sony%' OR companies.name
LIKE '20%' OR companies.name LIKE 'tv%')
OR (categories.name LIKE '%sony%' OR categories.name
LIKE '20%' OR categories.name LIKE '%tv%')
)
ORDER BY categories.name DESC, products.name ASC, companies.name ASC;
(and that just gets uglier the more words in the search)
++--+--+--+---
+
| id | name | model| company_name |
category_name |
++--+--+--+---
+
| 1 | 20 TV | STV20-KVR-HD | Sony | Tube
| ---
| 2 | 36 TV | STV36-KVR-HD | Sony | Tube
|
| 4 | Ultra-Plasma 62 | UP62F900 | Sony |
Plasma|
| 5 | Value Plasma 38 | VPR542_38| Sony |
Plasma|
| 6 | Power-MP3 5gb| 09834wuw34 | Sony | MP3
Players |
| 3 | Super-LCD 42| SLCD42hd002 | Sony | LCD
|
| 7 | Super-Player 1gb | SP1gb| Sony | Flash
|
| 8 | Porta CD | pcd500 | Sony | CD
Players|
..
++--+--+--+---
+
Obviously the person wanted id = 1 in this case.
Unrelated, is there any speed improvement using JOIN instead
of LEFT JOIN ?
Think millions of products.
Thanks for help and suggestions...
Daevid.
I'll attach a .php file, but this list server may strip it off, so I'll also
paste it below, sorry for any formatting issues in
advance...
?php
if ($_POST['keywords'])
{
$_POST['keywords'] = stripslashes($_POST['keywords']);
$words = preg_split(/\s+/,$_POST['keywords'], -1,
PREG_SPLIT_NO_EMPTY);
}
$sql = 'SELECT products.* FROM product_table WHERE 1 ';
$sql .= keyword_filter($words, array('products.model%', 'products.upc',
'%products.name%', 'companies.name%', '%categories.name%'),
true);
$sth = SQL_QUERY($sql);
/**
* Builds the WHERE portion of a SQL statement using the keywords in various
columns with wildcard support.
*
* @return string SQL statement fragment
* @parammixed $words either a string of words space deliminated or an
array of words
* @paramarray $columns an array of table.column names to search the
$words in. Use % as a wildcard for example pass in
'username%' or '%username%'.
* @paramboolean $and (true) whether the words have to be ANDed or ORed
together.
* @author Daevid Vincent [EMAIL PROTECTED]
* @since1.0
* @version 1.4
* @date 05/10/07
* @todo This should handle +, - and just like google or yahoo or
other search engines do.
*/
function keyword_filter($words, $columns, $and = true)
{
// this maybe useful
//
http://wiki.ittoolbox.com/index.php/Code:Translate_Boolean_Query_to_SQL_select_statement
// http://www.ibiblio.org/adriane/queries/
//
http://www.zend.com/zend/tut/tutorial-ferrara1.php?article=tutorial-ferrara1kind=tid=8238open=1anc=0view=1
//
http://evolt.org/article/Boolean_Fulltext_Searching_with_PHP_and_MySQL/18/15665/index.html
// http://www.databasejournal.com/features/mysql/article.php/3512461
// this would be great, but the dumb-asses don't work with InnoDB
tables. GRRR!
// http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
finding next and prev record in mysql
How would I find the next id and the prev id in sql statement like the one below. The id number is not going to be in order so I can't do a or limit 1 on the search SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' AND members_id= '8' ORDER BY lastname
