Re: PRINT statement?
There are such things as extensions to the standard, and many languages besides BASIC that have the ability to output a character string. No need to be snippy. I will look into the --silent option, thanks! Peter Brawley wrote: Stephen Cook wrote: I appreciate it but SELECT isn't quite what I want. It adds an extra 4 to 6 lines to the output (drawing the table, headers, row counts, etc). PRINT simply outputs whatever comes after it: PRINT is not a SQL command. The mysql client (fortunately) does not speak Basic. To minimise output in the mysql client, have a look at the -s --silent option. PB - PRINT 'hey you!' would show: hey you! Not a big deal I suppose but it makes for a lot more scrolling around. I've started just dumping the comments (i.e. '') into a table with a timestamp, so I can review it afterwards. Its a close second. Rhino wrote: Thanks, Quentin, for the documentation. Assuming that the Transact-SQL Help file is using various terms in the same way as MySQL does, particularly string expression and function, I think we will find that the SQL SELECT will do all of the things that Stephen has come to expect from the PRINT statement in MS SQL Server. I've just put together an SQL Script that I think demonstrates that SELECT can do mostl of the same things as the PRINT statement. Here is the script, which works perfectly in MySQL 4.0.15: = select === S C R I P T B E G I N S === as ; select CONNECT TO DATABASE as Action; use tmp; select DROP/CREATE TABLE as Action; drop table if exists users; create table if not exists users (user_id smallint not null, user_fname char(20) not null, user_lname char(20) not null, user_birthdate date not null, user_education_years int not null, primary key(user_id)); select POPULATE TABLE AND DISPLAY CONTENTS as Action; insert into users values (1, 'Alan', 'Adams', '1970-04-08', 15), (2, 'Bill', 'Baker', '1964-02-01', 18), (3, 'Cass', 'Cooke', '1981-12-04', 12), (4, 'Dina', 'Davis', '1944-06-06', 19), (5, 'Earl', 'Edger', '1990-08-02', 17); select * from users; select SET AND DISPLAY SCRIPT VARIABLES as Action; set @minimum_education_years = 16; set @birthdate_of_youngest_legal_worker = date_sub(curdate(), interval 16 year); select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select EXECUTE QUERIES THAT USE SCRIPT VARIABLES as Action; select concat(Get users who have more than , @minimum_education_years, years of education) as Query; select * from users where user_education_years = @minimum_education_years; select concat(Get users who are old enough to work, i.e. were born before , @birthdate_of_youngest_legal_worker) as Query; select * from users where user_birthdate = @legal_to_work; select DISPLAY FUNCTION RESULTS as Action; select as Function, as Value UNION select curdate()=, curdate() UNION select now()=, now() UNION select Firstname+Lastname=, concat(user_fname, ' ', user_lname) from users where user_id = 1; select === S C R I P T E N D S === as ; = and this is the output of the script: = +---+ | | +---+ | === S C R I P T B E G I N S === | +---+ 1 row in set (0.00 sec) +-+ | Action | +-+ | CONNECT TO DATABASE | +-+ 1 row in set (0.00 sec) Database changed +---+ | Action| +---+ | DROP/CREATE TABLE | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-+ | Action | +-+ | POPULATE TABLE AND DISPLAY CONTENTS | +-+ 1 row in set (0.00 sec) Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 +-++++--+ | user_id | user_fname | user_lname | user_birthdate | user_education_years | +-++++--+ | 1 | Alan | Adams | 1970-04-08 | 15 | | 2 | Bill | Baker | 1964-02-01 | 18 | | 3 |
Re: PRINT statement?
Stephen Cook wrote: There are such things as extensions to the standard, and many languages besides BASIC that have the ability to output a character string. No need to be snippy. I will look into the --silent option, thanks! I also tend to use -BN with these kind of scripts: mysql -u user -BN dbname file.sql mysql -u root -BN -e SHOW DATABASES etc. Regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL commercial licence
Folks, We have a commercial product that uses mysql 4.0.24. We bought a commercial licence for this version. We want to upgrade to 5.0.21. I spoke with the MySQL saleswoman this morning and she says they don't do upgrades and want another entire licence fee for v5.x. Does anyone else have experience of this? Upgrading commercial licences? What's the story? Thanks - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL commercial licence
Adam Lipscombe wrote: Folks, We have a commercial product that uses mysql 4.0.24. We bought a commercial licence for this version. We want to upgrade to 5.0.21. I spoke with the MySQL saleswoman this morning and she says they don't do upgrades and want another entire licence fee for v5.x. Does anyone else have experience of this? Upgrading commercial licences? What's the story? Thanks - Adam Hi Adam, That is a very interesting development. Is your license under the MySQL Network? I am considering using this for a number of new servers and would have to rethink my strategy if this is the case. I notice their Network FAQ has Q: Does MySQL Network include MySQL 5.0? A: Yes, MySQL Network includes all updates and upgrades including MySQL 5.0 It is also very interesting that the Network product automatically includes a GPL covered product rather than a commercial one by default. How many people actually check on that before purchase? Are they aware they have purchased a GPL product and are now obligated under that license to GPL their distributed products? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Quick Linux/MySQL performance questions.
Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Thanks a lot your help, guys. Best regards, RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL commercial licence
Checking. I don't think the customer bought the network version. Apparently its MySQL Pro Licence V4. It's a bit rich not to offer upgrades at a discount IMO. Adam -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: 12 May 2006 11:52 To: Adam Lipscombe Cc: 'MySQL List' Subject: Re: MySQL commercial licence Adam Lipscombe wrote: Folks, We have a commercial product that uses mysql 4.0.24. We bought a commercial licence for this version. We want to upgrade to 5.0.21. I spoke with the MySQL saleswoman this morning and she says they don't do upgrades and want another entire licence fee for v5.x. Does anyone else have experience of this? Upgrading commercial licences? What's the story? Thanks - Adam Hi Adam, That is a very interesting development. Is your license under the MySQL Network? I am considering using this for a number of new servers and would have to rethink my strategy if this is the case. I notice their Network FAQ has Q: Does MySQL Network include MySQL 5.0? A: Yes, MySQL Network includes all updates and upgrades including MySQL 5.0 It is also very interesting that the Network product automatically includes a GPL covered product rather than a commercial one by default. How many people actually check on that before purchase? Are they aware they have purchased a GPL product and are now obligated under that license to GPL their distributed products? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
RV Tec schrieb: Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). PS (*nix) should tell you how your processors are used. 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. Depends on which lib mysql uses in your case. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Consenus on best column type for Latitude / Longitude?
We use float. I have no idea if that's better or worse, but that's what we use. -Sheeri On 4/26/06, René Fournier [EMAIL PROTECTED] wrote: Just curious the majority use. I've been using decimal(18,14), but that appears bigger than necessary... Maybe varcar(21) for latitude, and varchar(22) for longitude? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VIEW not working with myODBC in XP and Access 2003
This is an Access problem; you'll need to find folks who know access to fix it. Try: http://www.tek-tips.com/viewthread.cfm?qid=1146857page=1 ?? -Sheeri On 4/26/06, Daevid Vincent [EMAIL PROTECTED] wrote: I have a critical problem that I hope there is a simple solution for. I've just spent a couple days converting a very messy hack to populate a table using a much more elegant VIEW solution now. Everything is going great, except now the whole point of this VIEW is so that people using MS Access (or other ODBC) can use the VIEW. It doesn't work!?! I've followed all of this: http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html I can import/link any other non-VIEW table. I've given FULL permissions to my ODBC user in mysql.mysql.user (and other appropriate) places just in case. Access pops up an error box that says Could not execute query; could not find linked table I'm using these versions: Windows XP http://dev.mysql.com/downloads/connector/odbc/3.51.html [EMAIL PROTECTED]:/lockdown# mysql --version mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 4.3 -- 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: PRINT statement?
Stephen Cook wrote: There are such things as extensions to the standard, and many languages besides BASIC that have the ability to output a character string. No need to be snippy. The preference expressed is to that SQL not be bowdlerised into Microsoftese. PB - I will look into the --silent option, thanks! Peter Brawley wrote: Stephen Cook wrote: I appreciate it but SELECT isn't quite what I want. It adds an extra 4 to 6 lines to the output (drawing the table, headers, row counts, etc). PRINT simply outputs whatever comes after it: PRINT is not a SQL command. The mysql client (fortunately) does not speak Basic. To minimise output in the mysql client, have a look at the -s --silent option. PB - PRINT 'hey you!' would show: hey you! Not a big deal I suppose but it makes for a lot more scrolling around. I've started just dumping the comments (i.e. '') into a table with a timestamp, so I can review it afterwards. Its a close second. Rhino wrote: Thanks, Quentin, for the documentation. Assuming that the Transact-SQL Help file is using various terms in the same way as MySQL does, particularly string expression and function, I think we will find that the SQL SELECT will do all of the things that Stephen has come to expect from the PRINT statement in MS SQL Server. I've just put together an SQL Script that I think demonstrates that SELECT can do mostl of the same things as the PRINT statement. Here is the script, which works perfectly in MySQL 4.0.15: = select === S C R I P T B E G I N S === as ; select CONNECT TO DATABASE as Action; use tmp; select DROP/CREATE TABLE as Action; drop table if exists users; create table if not exists users (user_id smallint not null, user_fname char(20) not null, user_lname char(20) not null, user_birthdate date not null, user_education_years int not null, primary key(user_id)); select POPULATE TABLE AND DISPLAY CONTENTS as Action; insert into users values (1, 'Alan', 'Adams', '1970-04-08', 15), (2, 'Bill', 'Baker', '1964-02-01', 18), (3, 'Cass', 'Cooke', '1981-12-04', 12), (4, 'Dina', 'Davis', '1944-06-06', 19), (5, 'Earl', 'Edger', '1990-08-02', 17); select * from users; select SET AND DISPLAY SCRIPT VARIABLES as Action; set @minimum_education_years = 16; set @birthdate_of_youngest_legal_worker = date_sub(curdate(), interval 16 year); select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select EXECUTE QUERIES THAT USE SCRIPT VARIABLES as Action; select concat(Get users who have more than , @minimum_education_years, years of education) as Query; select * from users where user_education_years = @minimum_education_years; select concat(Get users who are old enough to work, i.e. were born before , @birthdate_of_youngest_legal_worker) as Query; select * from users where user_birthdate = @legal_to_work; select DISPLAY FUNCTION RESULTS as Action; select as Function, as Value UNION select curdate()=, curdate() UNION select now()=, now() UNION select Firstname+Lastname=, concat(user_fname, ' ', user_lname) from users where user_id = 1; select === S C R I P T E N D S === as ; = and this is the output of the script: = +---+ | | +---+ | === S C R I P T B E G I N S === | +---+ 1 row in set (0.00 sec) +-+ | Action | +-+ | CONNECT TO DATABASE | +-+ 1 row in set (0.00 sec) Database changed +---+ | Action| +---+ | DROP/CREATE TABLE | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-+ | Action | +-+ | POPULATE TABLE AND DISPLAY CONTENTS | +-+ 1 row in set (0.00 sec) Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 +-++++--+ | user_id | user_fname | user_lname | user_birthdate | user_education_years | +-++++--+ | 1 | Alan | Adams | 1970-04-08 |
What is the best coding ethics related to mysql
Hi all, I have been using mysql from last few years but for small projects only, recently i have been on to some good projects, I want toknow what is the best coding practices for mysql to kee it fast etc. I mean in mine earlier post one friend told me that size upto 4 GB can be achieved with Mysql. I want to know: 1)Which is better a long table in terms of nos. of columns or use join and increase the columns. eg. in simple registration site we have 20 columns , we should use it in same table or use it in two diff. tables. 2)To use indexes to the maximum or restrain its use. 3)etc. Pl. point me to good advanced tutorial of mysql. Also is there any certification of mysql, php etc. Thanks, Abhishek Jain
Re: Quick Linux/MySQL performance questions.
Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions. (fwd)
1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). PS (*nix) should tell you how your processors are used. PS does show me about the CPU usage, but it doesnt tell me which processor, or if they're being used at the same time. Or am I missing a magic PS switch? 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. Depends on which lib mysql uses in your case. Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Thanks. I'll try to bump it up to 4GB to see what happens. Thanks a lot! RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY making recordset non-updatable
That's a problem with SQL Server -- google search on your error and you'll see that that's associated with SQL server, not MySQL. -Sheeri On 5/11/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Thanks for the response! Unfortunately, it is that simple. A basic Select * from vwMyView yields an updatable recordset. Adding Order by Name to the end does not allow an update. I should mention that the error associated with the lack of update is: Insufficient Key Column Information for Updating or Refreshing I have since futhered my troubleshooting and determined that I actually AM able to update the recordset when the order by is applied in some situations. Apparently, I can order by any field that is in the view's main table (the table that all of the other tables left join off of) and still be able to update. It is when I order by a field that is not from this main table that I get the above error and inability to update. I am still at a loss as to how to fix this so that I can order by any field I wish. Any input is greatly appreciated. Thanks, Travis Eland From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thu 5/4/2006 4:15 PM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable Maybe I'm thick You have a view, called vwMyView. You SELECT rows from it, and you're able to update the view? Yet when you SELECT with an ORDER BY clause, you're not allowed to update the view? I just do not understand how a read statement affects DML. I think you're going to have to post the query you're using, as it's more complex than a SELECT. Perhaps you're using a REPLACE INTO SELECT statement? Or UPDATE WHERE IN (SELECT)? -Sheeri On 4/19/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Heya. I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): Select * from vwMyView where id = ? If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. Is this a known issue? Is there something that I could possibly be missing? I apologize for the lack of actual code, but I appreciate any insight! Thanks! -- 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: What is the best coding ethics related to mysql
abhishek jain schrieb: Hi all, I have been using mysql from last few years but for small projects only, recently i have been on to some good projects, I want toknow what is the best coding practices for mysql to kee it fast etc. I mean in mine earlier post one friend told me that size upto 4 GB can be achieved with Mysql. I want to know: 1)Which is better a long table in terms of nos. of columns or use join and increase the columns. eg. in simple registration site we have 20 columns , we should use it in same table or use it in two diff. tables. Depends on what you want to do with that table. If you have lotsa crossover questions and need to mix up stuff. use more tables. otherwise use less. 2)To use indexes to the maximum or restrain its use. To maximum. Read also the optimize sections of dev.mysql.com. They help a lot. 3)etc. Blah blah Pl. point me to good advanced tutorial of mysql. Also is there any certification of mysql, php etc. What kind of cert you are talking about? SSL certs or what? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan RV Tec wrote: Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Thanks a lot your help, guys. Best regards, RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
On 5/12/06, RV Tec [EMAIL PROTECTED] wrote: Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Be aware that Gentoo builds glibc by default twice (read the einfo of the ebuild?) one with linuxthreads and the other with NPTL, you can set USE flags to avoid that (I guess its a matter of compatibility). so, you may be running MySQL with linuxthreads instead. Check it. More RAM, that's my advice, since your system seems pretty powerful. Also check MySQL manual on optimization, and check the variables and the way they are set on your system. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
Hope it is useful. I agree, you may want to look at adding another card and disks, for speed and to segregate the various operations (temp, logging, data). Splitting up your MYD and MYI files may help, though if you have enough RAM to keep indexes in memory, maybe you don't need to do that. With the sheer size of your data, I suggest you consider some form of striping with your RAID, not just mirroring. I'm a big fan of RAID 10 personally but if your data is pretty static then RAID 5 gives you the read speed at a lower cost. If you have a lot of write operations then RAID 5 may not be such a good choice. You might be surprised how much you will gain in read speed and therefore MySQL query speed if you go from RAID 1 to say a 6-disk RAID 10 setup. Depends on funds of course. For a good LSI card and 6 small fast internal disks you're probably looking at $2K or so. Depending on what you have now you could put MySQL logging on some inexpensive slower disks and re-use existing disks in a new setup. Good luck! Dan RV Tec wrote: Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY making recordset non-updatable
Hey Sheeri. Thanks again for the response. I do agree that this is a known problem with SQL Server. My problem is that I am using a Windows Visual Basic application to access a Linux based MySQL Server via MyODBC/Connector. There is no longer SQL Server in the loop. I believe this is a Microsoft Cursor Engine error that is being issued, and it just happens to be the same one that is associated with the SQL Server issue. I have been able to reproduce my problem on many levels. I created 2 basic tables, created a view where table1 left join table2. If, in Visual Basic, I call the view and order by a table1 field, I can update. If I order by a table2 field, I can no longer update and get the Invalid Key Column for Updating or Refreshing error. While the issue stems from the view definition in MySQL, the actual problem could be caused by Visual Basic, MyODBC or MySQL itself. I don't know what to try next. As always, any help is greatly appreciated. Thanks again, Travis Eland -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Fri 5/12/2006 10:01 AM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable That's a problem with SQL Server -- google search on your error and you'll see that that's associated with SQL server, not MySQL. -Sheeri On 5/11/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Thanks for the response! Unfortunately, it is that simple. A basic Select * from vwMyView yields an updatable recordset. Adding Order by Name to the end does not allow an update. I should mention that the error associated with the lack of update is: Insufficient Key Column Information for Updating or Refreshing I have since futhered my troubleshooting and determined that I actually AM able to update the recordset when the order by is applied in some situations. Apparently, I can order by any field that is in the view's main table (the table that all of the other tables left join off of) and still be able to update. It is when I order by a field that is not from this main table that I get the above error and inability to update. I am still at a loss as to how to fix this so that I can order by any field I wish. Any input is greatly appreciated. Thanks, Travis Eland From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thu 5/4/2006 4:15 PM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable Maybe I'm thick You have a view, called vwMyView. You SELECT rows from it, and you're able to update the view? Yet when you SELECT with an ORDER BY clause, you're not allowed to update the view? I just do not understand how a read statement affects DML. I think you're going to have to post the query you're using, as it's more complex than a SELECT. Perhaps you're using a REPLACE INTO SELECT statement? Or UPDATE WHERE IN (SELECT)? -Sheeri On 4/19/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Heya. I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): Select * from vwMyView where id = ? If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. Is this a known issue? Is there something that I could possibly be missing? I apologize for the lack of actual code, but I appreciate any insight! Thanks! -- 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: Optimizing range search with two-table ORDER BY
Hi Jesse, Have you tried the following: 1) ordering by only part.d and seeing how long the query takes 2) putting an index on (part.d, cwGroup.stripped_cw) and seeing how long the query takes. 1 will help pinpoint the problem, and 2 might actually help. -Sheeri SELECT part.d, quotation.qt, cwGroup.cw FROM cwGroup JOIN quotation ON (quotation.id = cwGroup.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( part.d BETWEEN 1950 AND 1970 AND cwGroup.stripped_cw LIKE 'man%' ) ORDER BY part.d, cwGroup.stripped_cw LIMIT 25 and the EXPLAIN for it looks like: *** 1. row *** id: 1 select_type: SIMPLE table: cwGroup type: range possible_keys: quotation_id,stripped_cw key: stripped_cw key_len: 101 ref: NULL rows: 8489 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.cwGroup.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY,d key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 Extra: Using where Without the ORDER BY it drops to about 1.5s and EXPLAIN no longer shows the use of temporary and filesort. An even worse example, but unfortunately a common need in this app, is a query that returns a lot of rows (but which I'm paging through, of course), such as: SELECT part.d, quotation.qt, cwGroup.cw FROM cwGroup JOIN quotation ON (quotation.id = cwGroup.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( cwGroup.stripped_cw BETWEEN 'ant' AND 'asx' ) ORDER BY cwGroup.stripped_cw, part.d LIMIT 25 This takes 2m31s to execute, obviously due to the large number of rows (the total result is about 47K rows), but a similar query without the ORDER BY took only .08s (though a COUNT(*) took a similar 2-3m): *** 1. row *** id: 1 select_type: SIMPLE table: cwGroup type: range possible_keys: quotation_id,stripped_cw key: stripped_cw key_len: 101 ref: NULL rows: 54745 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.cwGroup.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 Extra: Other queries, as said, are more complicated, adding additional columns in the searches or joining in other tables (sometimes with range searches here as well), but these don't seem to affect the underlying problem. Adding multiple-column indexes also doesn't affect things in any significant way. Any thoughts? I clearly need a significant speed improvement, not just a tweak like making a bigger sort_buffer_size or getting faster disks. Thanks for reading this far. Jesse Sheidlower -- 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: InnoDB Memory Problem causing mysql to crash
Dobromir, As I said in my first message (see the message quoted below), MySQL could use up to 4.991913 G of memory. So you could use more than 4GB. Check out the calculation below. Also read the rest of my message, regarding thread size, the manual page for crashing, max_connections, slow query logs, and disk partitions. You haven't indicated that you've done any of what I mentioned, and you might be using more than 4G anyway. -Sheeri On 5/8/06, Dobromir Velev [EMAIL PROTECTED] wrote: Hi, I'm aware of the fact that this is a 32 bit system - and I've tried to make sure that mysqld will not use more than 4 GB. As you can see the innodb_buffer_pool_size is 2 Gb and the total amount of memory used by the MyISAM key buffer size and the per thread variables is less then 2 GB. There are no other services on this machine so the memory should not be a problem. This server was working fine for almost a year until recently it started crashing. Could it be some memory problem I've ran into and can you suggest anything I can do to avoid similar problems in the future. Thanks Dobromir Velev On Saturday 06 May 2006 01:23, Heikki Tuuri wrote: Dobromir, you are running a 32-bit operating system. Then the size of the mysqld process is limited to 2 GB, or at most to 4 GB. The amount of total RAM 8 GB does not help here, since 2^32 = 4 G. You should reduce the key_buffer_size or innodb_buffer_pool_size in my.cnf. 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: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, May 05, 2006 10:50 PM Subject: Re: InnoDB Memory Problem causing mysql to crash Well, according to my calculations: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB (I used the default binlog_cache_size value of 32K plus your settings) MySQL could use up to 4.991913 G of memory. Shouldn't be a problem, unless of course your 8G of machine is running something other than MySQL. Is it? Because the fact that it could not allocate memory means that something was trying to use memory that didn't exist Did MySQL dump a core file? Did you follow this advice? You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap= for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html Did you read the man page? The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Also, did you try to look at your slow query logs to see if there was some kind of query hogging memory? What about backups running at the same time? I'll note that you maxxed out your connections, which shouldn't cause a crash, but might indicate that your server tuning is not up-to-date with your actual usage. Are your data and logfiles are on a diffferent partitions? We had problems with one machine where the data and logfiles were on the same partition, and it would crash -- we moved to a machine that was the same except for the different OS partitions, and it didn't crash! We figure the disk seeking just killed the OS so it segfaulted the mysql process. -Sheeri On 5/4/06, Dobromir Velev [EMAIL PROTECTED] wrote: Hi, I'm trying to resolve why InnoDB is crashing. It happened twice for the l= ast month without obvoius reason Any help will be appreciated. Dobromir Velev My Server is Red Hat Enterprise Linux ES release 3 (Taroon Update 7) 2.4.21-32.0.1.ELs= mp Dual 3.2 GHz Intel Xeon 8 GB RAM with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives my.cnf settings innodb_buffer_pool_size=3D2000M innodb_additional_mem_pool_size=3D20M innodb_log_file_size=3D150M innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D0 innodb_lock_wait_timeout=3D50 key_buffer_size=3D1000M read_buffer_size=3D500K read_rnd_buffer_size=3D1200K sort_buffer_size=3D1M thread_cache=3D256 thread_concurrency=3D8 thread_stack=3D126976 myisam_sort_buffer_size=3D64M max_connections=3D600 The error log shows the following message: InnoDB: Fatal error: cannot allocate 1048576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 2263507272 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a
Re: optemizer for mySQL!
http://www.devshed.com/c/a/MySQL/A-Technical-Tour-of-MySQL/ The MySQL server has it built in. -Sheeri On 5/8/06, Jim [EMAIL PROTECTED] wrote: Hi All, Didn't know there was an optemizer for mySQL. Where can I get it from? Thanks Jim Best regards, Jim Clark Project Manager Multilink Systems Ph: 03 9425 9400 Fax: 03 9425 9811 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: #1191 - Can't find FULLTEXT index matching the column list
On 5/11/2006 9:17 AM Fan, Wellington wrote: Damn, I really didn't mean to use that subject line; Sorry all! Nor should you reply to a message when starting a new thread. It screws up the threading whether or not you change the subject. Please start all new threads with a new message. Thanks, Drew -- Be a Great Magician! Visit The Alchemist's Warehouse http://www.alchemistswarehouse.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY making recordset non-updatable
Well, that clears things up on my end. I was confused as to how a SELECT statement produces updatable results because SELECT just gives output. Go into MySQL on the commandline and see if Select * from vwMyView and Select * from vwMyView ORDER BY Name produce the same results, just in a different order. If they do, as expected, then the problem is not in MySQL. Since you can update it in the application with the former select query and not the latter, I don't think it's permissions or anything; rather something in the application. It sounds like Visual Basic, as ODBC just passes connections along to MySQL. -Sheeri On 5/12/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Hey Sheeri. Thanks again for the response. I do agree that this is a known problem with SQL Server. My problem is that I am using a Windows Visual Basic application to access a Linux based MySQL Server via MyODBC/Connector. There is no longer SQL Server in the loop. I believe this is a Microsoft Cursor Engine error that is being issued, and it just happens to be the same one that is associated with the SQL Server issue. I have been able to reproduce my problem on many levels. I created 2 basic tables, created a view where table1 left join table2. If, in Visual Basic, I call the view and order by a table1 field, I can update. If I order by a table2 field, I can no longer update and get the Invalid Key Column for Updating or Refreshing error. While the issue stems from the view definition in MySQL, the actual problem could be caused by Visual Basic, MyODBC or MySQL itself. I don't know what to try next. As always, any help is greatly appreciated. Thanks again, Travis Eland -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Fri 5/12/2006 10:01 AM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable That's a problem with SQL Server -- google search on your error and you'll see that that's associated with SQL server, not MySQL. -Sheeri On 5/11/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Thanks for the response! Unfortunately, it is that simple. A basic Select * from vwMyView yields an updatable recordset. Adding Order by Name to the end does not allow an update. I should mention that the error associated with the lack of update is: Insufficient Key Column Information for Updating or Refreshing I have since futhered my troubleshooting and determined that I actually AM able to update the recordset when the order by is applied in some situations. Apparently, I can order by any field that is in the view's main table (the table that all of the other tables left join off of) and still be able to update. It is when I order by a field that is not from this main table that I get the above error and inability to update. I am still at a loss as to how to fix this so that I can order by any field I wish. Any input is greatly appreciated. Thanks, Travis Eland From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thu 5/4/2006 4:15 PM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable Maybe I'm thick You have a view, called vwMyView. You SELECT rows from it, and you're able to update the view? Yet when you SELECT with an ORDER BY clause, you're not allowed to update the view? I just do not understand how a read statement affects DML. I think you're going to have to post the query you're using, as it's more complex than a SELECT. Perhaps you're using a REPLACE INTO SELECT statement? Or UPDATE WHERE IN (SELECT)? -Sheeri On 4/19/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Heya. I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): Select * from vwMyView where id = ? If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. Is this a known issue? Is there something that I could possibly be missing? I apologize for the lack of actual code, but I appreciate any insight! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL
Re: Quick Linux/MySQL performance questions.
I'll add make sure logs and data are on separate partitions so you're not doing excessive seeking back and forth. -Sheeri On 5/12/06, Dan Buettner [EMAIL PROTECTED] wrote: Hope it is useful. I agree, you may want to look at adding another card and disks, for speed and to segregate the various operations (temp, logging, data). Splitting up your MYD and MYI files may help, though if you have enough RAM to keep indexes in memory, maybe you don't need to do that. With the sheer size of your data, I suggest you consider some form of striping with your RAID, not just mirroring. I'm a big fan of RAID 10 personally but if your data is pretty static then RAID 5 gives you the read speed at a lower cost. If you have a lot of write operations then RAID 5 may not be such a good choice. You might be surprised how much you will gain in read speed and therefore MySQL query speed if you go from RAID 1 to say a 6-disk RAID 10 setup. Depends on funds of course. For a good LSI card and 6 small fast internal disks you're probably looking at $2K or so. Depending on what you have now you could put MySQL logging on some inexpensive slower disks and re-use existing disks in a new setup. Good luck! Dan RV Tec wrote: Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- 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: What is the best coding ethics related to mysql
The courses MySQL offers are excellent. Perhaps good courses to take would be the MySQL DBA or Developer Certification Tutorials, and then take the certification tests. http://www.mysql.com/training/ I've taken their courses and have only good things to say. -Sheeri On 5/12/06, Barry [EMAIL PROTECTED] wrote: abhishek jain schrieb: Hi all, I have been using mysql from last few years but for small projects only, recently i have been on to some good projects, I want toknow what is the best coding practices for mysql to kee it fast etc. I mean in mine earlier post one friend told me that size upto 4 GB can be achieved with Mysql. I want to know: 1)Which is better a long table in terms of nos. of columns or use join and increase the columns. eg. in simple registration site we have 20 columns , we should use it in same table or use it in two diff. tables. Depends on what you want to do with that table. If you have lotsa crossover questions and need to mix up stuff. use more tables. otherwise use less. 2)To use indexes to the maximum or restrain its use. To maximum. Read also the optimize sections of dev.mysql.com. They help a lot. 3)etc. Blah blah Pl. point me to good advanced tutorial of mysql. Also is there any certification of mysql, php etc. What kind of cert you are talking about? SSL certs or what? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- 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]
MySQL 4.0.27 has been released - Security Update
Hi, MySQL 4.0.27, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/4.0.html http://downloads.mysql.com/archives.php?p=mysql-4.0v=4.0.27 and mirror sites. This is a security fix release and bugfix release for the 4.0 production release family. This MySQL 4.0.27 release includes the patches for recently reported security vulnerabilities in the MySQL client-server protocol. We would like to thank Stefano Di Paola [EMAIL PROTECTED] for finding and reporting these to us. Functionality added or changed: * The MySQL-server RPM now explicitly assigns the mysql system user to the mysql user group during the post-installation process. This corrects an issue with upgrading the server on some Linux distributions whereby a previously existing mysql user was not changed to the mysql group, resulting in wrong groups for files created following the installation. (Bug#12823: http://bugs.mysql.com/12823) * Better detection of connection timeout for replication servers on Windows allows elimination of extraneous Lost connection errors in the error log. (Bug#5588: http://bugs.mysql.com/5588) Bugs fixed: * Security fix: A malicious client, using specially crafted invalid login or COM_TABLE_DUMP packets was able to read uninitialized memory, which potentially, though unlikely in MySQL, could have led to an information disclosure. (CVE-2006-1516 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2006-1516), CVE-2006-1517 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2006-1517)) Thanks to Stefano Di Paola [EMAIL PROTECTED] for finding and reporting this bug. * MySQL-shared-compat-4.0.26-0.i386.rpm and 4.0.27 incorrectly depend on glibc 2.3 and cannot not be installed on a glibc 2.2 system. For MySQL 4.0, we recommend using the older MySQL-shared-compat-4.0.25-0.i386.rpm package. (Bug#16539: http://bugs.mysql.com/16539) * When myisamchk needed to rebuild a table, AUTO_INCREMENT information was lost. (Bug#10405: http://bugs.mysql.com/10405) * BIT_COUNT() could return an incorrect value for right table columns in a LEFT JOIN. (Bug#13044: http://bugs.mysql.com/13044) * An UPDATE statement which tried to update a column with a name beginning with an asterisk would cause the server to crash. This was because the MySQL server would wrongly expand the '*' character to the list of all table columns, causing the list of columns to become longer than the list of values. Now the server performs this expansion only if the '*' character is followed by a space. (Bug#15610: http://bugs.mysql.com/15610) * An INSERT ... SELECT statement between tables in a MERGE set can return errors when statement involves insert into child table from merge table or vice-versa. (Bug#5390: http://bugs.mysql.com/5390) * A LIMIT-related optimization failed to take into account that MyISAM table indexes can be disabled, causing Error 124 when it tried to use such an index. (Bug#14616: http://bugs.mysql.com/14616) * For a table that had been opened with HANDLER OPEN, issuing OPTIMIZE TABLE, ALTER TABLE, or REPAIR TABLE caused a server crash. (Bug#14397: http://bugs.mysql.com/14397) * Queries of the form (SELECT ...) ORDER BY ... were being treated as a UNION. This improperly resulted in only distinct values being returned (because UNION by default eliminates duplicate results). Also, references to column aliases in ORDER BY clauses following parenthesized SELECT statements were not resolved properly. (Bug#7672: http://bugs.mysql.com/7672) * SELECT DISTINCT with a GROUP BY clause caused a server crash. (Bug#13855: http://bugs.mysql.com/13855) * SHOW CREATE TABLE did not display any FOREIGN KEY clauses if a temporary file could not be created. Now SHOW CREATE TABLE displays an error message in an SQL comment if this occurs. (Bug#13002: http://bugs.mysql.com/13002) * MySQL programs in binary distributions for Solaris 8/9/10 x86 systems would not run on Pentium III machines. (Bug#6772: http://bugs.mysql.com/6772) * Queries against a MERGE table that has a composite index could produce incorrect results. (Bug#9112: http://bugs.mysql.com/9112) * The counters for the Key_read_requests, Key_reads, Key_write_requests, and Key_writes status variables were changed from unsigned long to unsigned long long to accommodate larger values before the variables roll-over and restart from 0. (Bug#12920: http://bugs.mysql.com/12920) * A concurrency problem for CREATE ... SELECT could cause a server crash.
Re: MySQL commercial licence
Not particularly. You're not buying an upgrade, you're buying a license for a new product. You can use MySQL all you want for free, you just can't repackage or embed it without a license. I don't see it as an upgrade issue really. You'll want to rewrite many queries, add new features, etc to get the full benefits of MySQL 5.0. You're making another product. A bit rich is what I'd call Oracle's licensing fees. Just remember, the money's paying developers to make the product better -Sheeri On 5/12/06, Adam Lipscombe [EMAIL PROTECTED] wrote: Checking. I don't think the customer bought the network version. Apparently its MySQL Pro Licence V4. It's a bit rich not to offer upgrades at a discount IMO. Adam -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: 12 May 2006 11:52 To: Adam Lipscombe Cc: 'MySQL List' Subject: Re: MySQL commercial licence Adam Lipscombe wrote: Folks, We have a commercial product that uses mysql 4.0.24. We bought a commercial licence for this version. We want to upgrade to 5.0.21. I spoke with the MySQL saleswoman this morning and she says they don't do upgrades and want another entire licence fee for v5.x. Does anyone else have experience of this? Upgrading commercial licences? What's the story? Thanks - Adam Hi Adam, That is a very interesting development. Is your license under the MySQL Network? I am considering using this for a number of new servers and would have to rethink my strategy if this is the case. I notice their Network FAQ has Q: Does MySQL Network include MySQL 5.0? A: Yes, MySQL Network includes all updates and upgrades including MySQL 5.0 It is also very interesting that the Network product automatically includes a GPL covered product rather than a commercial one by default. How many people actually check on that before purchase? Are they aware they have purchased a GPL product and are now obligated under that license to GPL their distributed products? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- 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: Consenus on best column type for Latitude / Longitude?
I worked on a system years ago that used binary encoded integer for latitude longitude. The Most Significant Bit (MSB) was a sign bit ( + or -). The next bit was 180 degrees The next bit was 090 degrees The next bit was 045 degrees. etc Functions were written to transform these into human readable vlaues. The beauty of this was that integer addition and subtraction could used directly on these values. On 5/12/06, sheeri kritzer [EMAIL PROTECTED] wrote: We use float. I have no idea if that's better or worse, but that's what we use. -Sheeri On 4/26/06, René Fournier [EMAIL PROTECTED] wrote: Just curious the majority use. I've been using decimal(18,14), but that appears bigger than necessary... Maybe varcar(21) for latitude, and varchar(22) for longitude? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 strange table creation 'func'
Hi Amer,
Indeed, the 'func' table in the 'mysql' database has the same
structure that you found in your client database. in your upgrade,
did something go wrong and you did a mysqldump of the mysql database
and reimport? Are you sure you're in the right database? What does
describe mysql.func;
show you?
Does the following work?
describe clientdb.func;
(where 'clientdb' is the name of the client database it's in)
If the former and the latter give you a table description and no
errors, I'd say it's OK to delete the func table in the client db. Is
it in all client dbs or just that one? A mysqldump/import could have
been editing and done wrongbut I haven't done an in-place upgrade,
so I can't say for sure what the upgrade might or might not do and if
there are bugs or not.
-Sheeri
On 5/11/06, Amer Neely [EMAIL PROTECTED] wrote:
I've just noticed in one of my databases a table named 'func', which I'm
positive I never created.
It was empty and has 4 columns:
mysql describe func;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| name | char(64) | NO | PRI | NULL| |
| ret | tinyint(1) | NO | | 0 | |
| dl| char(128)| NO | | NULL| |
| type | enum('function','aggregate') | NO | | NULL| |
+---+--+--+-+-+---+
4 rows in set (0.01 sec)
A search for 'func' in the 5.0 docs reveals there apparently is a system
table in the mysql database called 'func', but I can't find anything
about why it would be created in a client database.
It does appear in my mysql database as well, but no others. Anyone got
an idea where it's coming from? And can I delete it from the client
database?
I suspect this may have something to do with my recent upgrade to 5.0
but I don't see the purpose, especially as it only appears in one of my
client databases.
--
Amer Neely
Home of Spam Catcher
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | MySQL | CGI programming for all data entry forms.
We make web sites work!
--
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: ~Replication errors~
MySQL's pretty good when it describes an error. (if the error message is vague you might be screwed, but MySQL is pretty good). Which means: 1) You didn't properly change the max_allowed_packet on the master. what does show variables like max_allowed_packet; show you? 2) The slave is actually running fine and you're looking at an old error. What does SHOW SLAVE STATUS; show you on the master? 3) Did you change max_allowed_packet on the slave? I think it would need to be changed on the slave as well. -Sheeri On 5/10/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, Iam getting the following error on my Mysql Slave server. This happened when my disk space got full and there was no space left on the device. I managed to free up some space and then ran mysql STOP SLAVE mysql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000110', MASTER_LOG_POS=850202232; mysql START SLAVE I could find that replication started but with the following error. Can anyone help me out in fixing the issue ? I tried increasing the max_allowed_packet on master server but with no luck. 060510 0:56:22 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.000110' at position 850202232 060510 0:56:22 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 060510 0:56:22 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when readingdata from binary log 060510 0:56:22 [Note] Slave I/O thread exiting, read up to log 'mysql- bin.000110', position 850202232 Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- 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: sql-99
http://www.ncb.ernet.in/education/modules/dbms/SQL99/ -Sheeri On 5/10/06, Peng Yi-fan [EMAIL PROTECTED] wrote: hi, does anyone know where to download ISO-SQL-99? PDF is best. thanks Pang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
I diagree on point 1. If you warn your members that their password is insecure, and if you e-mail out passwords anyway, there's no reason not to have a secure password. Many people I know use an insecure password for many things, from silly required free registration sites (go ahead, break into my Washington Post account) to other services not important to them. But do I really care if someone ganks my wikipedia password? If you're a bank, sure, encrypt. Or if you have important data. But it's not a hard and fast rule. In fact, I'd venture to say don't use hard and fast rules. THINK about your situation, and if it makes sense. Does using SSL make sense? Does using encryption make sense? -Sheeri On 5/10/06, Johan Lundqvist [EMAIL PROTECTED] wrote: Hi Dave, 1st: Never, never, never store passwords in plain text!! Just don't do it. Store a hash of the password (ie md5 or something else). 2nd: Never pass any input from the Internet directly into a query without first checking it for sql injection. Take a look at Wikipedia article for a brief explanation and several links to further info. http://en.wikipedia.org/wiki/SQL_injection /Johan Critters wrote: Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar hack Appreciate any feedback. -- Dave -- 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: MySQL 5.0 strange table creation 'func'
sheeri kritzer wrote:
Hi Amer,
Indeed, the 'func' table in the 'mysql' database has the same
structure that you found in your client database. in your upgrade,
did something go wrong and you did a mysqldump of the mysql database
and reimport? Are you sure you're in the right database? What does
describe mysql.func;
Hi Sheeri,
I don't think I was doing anything other than trying to get 5.0
installed on my Win2K development box at home (which is a whole other
story). No dumping etc.
mysql describe mysql.func;
+---+--+--+-+--+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+--+---+
| name | char(64) | NO | PRI | NULL | |
| ret | tinyint(1) | NO | | 0| |
| dl| char(128)| NO | | NULL | |
| type | enum('function','aggregate') | NO | | function | |
+---+--+--+-+--+---+
4 rows in set (0.00 sec)
Does the following work?
describe clientdb.func;
(where 'clientdb' is the name of the client database it's in)
mysql describe hasbeans.func;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| name | char(64) | NO | PRI | NULL| |
| ret | tinyint(1) | NO | | 0 | |
| dl| char(128)| NO | | NULL| |
| type | enum('function','aggregate') | NO | | NULL| |
+---+--+--+-+-+---+
4 rows in set (0.02 sec)
mysql select * from hasbeans.func;
Empty set (0.00 sec)
mysql select * from mysql.func;
Empty set (0.00 sec)
So they are identical and both empty. I was pretty sure I could delete
the client one, but just wanted to check first.
And yes, it only appears in that one database. Very strange indeed. Oh
well, it will disappear shortly :)
Thanks for the response.
If the former and the latter give you a table description and no
errors, I'd say it's OK to delete the func table in the client db. Is
it in all client dbs or just that one? A mysqldump/import could have
been editing and done wrongbut I haven't done an in-place upgrade,
so I can't say for sure what the upgrade might or might not do and if
there are bugs or not.
-Sheeri
On 5/11/06, Amer Neely [EMAIL PROTECTED] wrote:
I've just noticed in one of my databases a table named 'func', which I'm
positive I never created.
It was empty and has 4 columns:
mysql describe func;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| name | char(64) | NO | PRI | NULL| |
| ret | tinyint(1) | NO | | 0 | |
| dl| char(128)| NO | | NULL| |
| type | enum('function','aggregate') | NO | | NULL| |
+---+--+--+-+-+---+
4 rows in set (0.01 sec)
A search for 'func' in the 5.0 docs reveals there apparently is a system
table in the mysql database called 'func', but I can't find anything
about why it would be created in a client database.
It does appear in my mysql database as well, but no others. Anyone got
an idea where it's coming from? And can I delete it from the client
database?
I suspect this may have something to do with my recent upgrade to 5.0
but I don't see the purpose, especially as it only appears in one of my
client databases.
--
Amer Neely
Home of Spam Catcher
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | MySQL | CGI programming for all data entry forms.
We make web sites work!
--
Amer Neely
Home of Spam Catcher
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | MySQL | CGI programming for all data entry forms.
We make web sites work!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning records in a circle
Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
- Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Are you trying to get these rows purely via SQL at the command line or in an SQL script? Or would an application be an option for you? If you are not willing to consider application code to grab the rows you want, the answer to your question is maybe. SQL has always been intended to return ALL of the rows that satisfy a query with a single invocation of the query, no matter how many rows that is. So if your query says: select * from mytab; you will normally get all of the rows that satisfy that query in one go, whether there are 0 rows, 100 rows, or a 100 million rows in the result. You _might_ be able to get the results you want by using the LIMIT clause. I'm not sure what version of MySQL you are using but the LIMIT clause is described in the MySQL 3.23/4.0/4.1 manual on this page: http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that you'll still pretty much need some sort of script in order to keep executing the query to get the next two records and you may need to change the parameters of the LIMIT clause at the same time. If you are willing to write application code, things get a lot easier. For instance, a Java program could easily grab rows from a result set for you two at a time, let you process them, then grab two more, etc. I expect that it would similarly easy to do the same thing in Perl and PHP and C. In short, a program gives you a lot more ability to do what you want to do with your database data. But some shops have very little programming expertise and prefer to do everything via SQL. If you work for one of those shops, you might not be able to get your records two at a time with SQL alone, unless you can write a script that takes advantage of the LIMIT clause. I don't pretend to know MySQL exhaustively so someone else may have another suggestion for you but the only two approaches I can think of that might meet your needs are to use the LIMIT clause or to write an application. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using a stored procedure inside a view
Hey folks I'm trying to crate a view that runs a stored procedure. Can anyone tell me what I might be doing wrong or if what I'm trying it possible? Here is what I get: mysql create view v as CALL flatscore(6,2); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL flatscore(6,2)' at line 1 Thanks, Chris C -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/338 - Release Date: 5/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning records in a circle
Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Are you trying to get these rows purely via SQL at the command line or in an SQL script? Or would an application be an option for you? If you are not willing to consider application code to grab the rows you want, the answer to your question is maybe. SQL has always been intended to return ALL of the rows that satisfy a query with a single invocation of the query, no matter how many rows that is. So if your query says: select * from mytab; you will normally get all of the rows that satisfy that query in one go, whether there are 0 rows, 100 rows, or a 100 million rows in the result. You _might_ be able to get the results you want by using the LIMIT clause. I'm not sure what version of MySQL you are using but the LIMIT clause is described in the MySQL 3.23/4.0/4.1 manual on this page: http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that you'll still pretty much need some sort of script in order to keep executing the query to get the next two records and you may need to change the parameters of the LIMIT clause at the same time. If you are willing to write application code, things get a lot easier. For instance, a Java program could easily grab rows from a result set for you two at a time, let you process them, then grab two more, etc. I expect that it would similarly easy to do the same thing in Perl and PHP and C. In short, a program gives you a lot more ability to do what you want to do with your database data. But some shops have very little programming expertise and prefer to do everything via SQL. If you work for one of those shops, you might not be able to get your records two at a time with SQL alone, unless you can write a script that takes advantage of the LIMIT clause. I don't pretend to know MySQL exhaustively so someone else may have another suggestion for you but the only two approaches I can think of that might meet your needs are to use the LIMIT clause or to write an application. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/337 - Release Date: 11/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions. (fwd)
On Fri, 2006-05-12 at 10:59 -0400, RV Tec wrote: Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. NPTL stands for Native POSIX Thread Library, so yeah, it's using NPTL. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: Returning records in a circle
Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. I don't know any way to do this directly with SQL, but I could be wrong, stored procedures may help you here, I do not use mysql 5, so I have not played with them yet. I would probably do this with a second table, and some SQL in the middle of it all to make it happen. Your second table would simply store the last id that you selected. So, you select from your second table, to get the last id you retrieved. Them you select from the primary table, where id retreived_id limit 2, once you have that, take the ID from the last record in the 2 you just got back, and insert/update that data into your second table. Make sure you add in app logic to deal with cases where the second table has never been inserted into, also when you wrap your record set, it will need to be reset, but I think you get the idea. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Returning records in a circle
I ran into this same type of question the other evening at a local linux group. I think that once you reach the end of the results set the only way to start back at the beginning of that results set is to do the query again. Once option - do your query and grab all the rows load them into a data structure - ie - a list of associative arrays Then all you need to do is incrementally go through the array. when you hit the end, just reset the index back to 0. -Original Message- From: Steffan A. Cline [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:52 PM To: mysql@lists.mysql.com Subject: Re: Returning records in a circle Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can get a set of records incrementally such as to get 2 then the next query get the next 2 then at the end of all records to get the 2 from the beginning? I need to keep going incrementally by 2 in a circle. Are you trying to get these rows purely via SQL at the command line or in an SQL script? Or would an application be an option for you? If you are not willing to consider application code to grab the rows you want, the answer to your question is maybe. SQL has always been intended to return ALL of the rows that satisfy a query with a single invocation of the query, no matter how many rows that is. So if your query says: select * from mytab; you will normally get all of the rows that satisfy that query in one go, whether there are 0 rows, 100 rows, or a 100 million rows in the result. You _might_ be able to get the results you want by using the LIMIT clause. I'm not sure what version of MySQL you are using but the LIMIT clause is described in the MySQL 3.23/4.0/4.1 manual on this page: http://dev.mysql.com/doc/refman/4.1/en/select.html. The problem is that you'll still pretty much need some sort of script in order to keep executing the query to get the next two records and you may need to change the parameters of the LIMIT clause at the same time. If you are willing to write application code, things get a lot easier. For instance, a Java program could easily grab rows from a result set for you two at a time, let you process them, then grab two more, etc. I expect that it would similarly easy to do the same thing in Perl and PHP and C. In short, a program gives you a lot more ability to do what you want to do with your database data. But some shops have very little programming expertise and prefer to do everything via SQL. If you work for one of those shops, you might not be able to get your records two at a time with SQL alone, unless you can write a script that takes advantage of the LIMIT clause. I don't pretend to know MySQL exhaustively so someone else may have another suggestion for you but the only two approaches I can think of that might meet your needs are to use the LIMIT clause or to write an application. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free
Re: Returning records in a circle
I'm sorry but I think you're all making this more complicated than it needs to be. I could easily write a Java program that obtained a result set, then processed that set in almost any way you wanted, including two records at a time. I could probably do the same in Perl or any other language supported by MySQL, although it might take a bit of time to learn the necessary parts of the language. Processing a result set is a very well-understood process and has been widely done millions of times in dozens of languages. You should not need to add any columns to the table to do it either. I might be able to offer more detail - and maybe even an example! - if a few things were explained to me. I'm still not clear on why two records are being processed at a time and what the relationship is between the records. About the only time I see twinned records are when someone is doing an accounting application where each debit is matched by an offsetting credit. But this doesn't seem to the case here. Perhaps you don't need to process two records at once? Also, am I right in assuming that you are allowing new records to be written to the table - and allowing existing records to be updated - while you run your query? If yes, is it possible that the keys of the new records will be interspersed with the existing keys? Or will new records always have a key value that is higher than the highest previous key value? If inserts and updates are happening, do they absolutely HAVE to happen simultaneously with your query? Or could you store them off to the side briefly or even just suspend the insert and update applications while the query runs? If there are no inserts and updates to worry about, you should be able to avoid any updates of duplicates by simply sorting all of the desired rows into order based on the key and then processing them one (or two) at a time. Things get a bit trickier if the table is being updated/inserted while your new program is running. I don't want to say much more until you've clarified what it is you are doing -- Rhino - Original Message - From: George Law [EMAIL PROTECTED] To: Steffan A. Cline [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, May 12, 2006 3:33 PM Subject: RE: Returning records in a circle I ran into this same type of question the other evening at a local linux group. I think that once you reach the end of the results set the only way to start back at the beginning of that results set is to do the query again. Once option - do your query and grab all the rows load them into a data structure - ie - a list of associative arrays Then all you need to do is incrementally go through the array. when you hit the end, just reset the index back to 0. -Original Message- From: Steffan A. Cline [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:52 PM To: mysql@lists.mysql.com Subject: Re: Returning records in a circle Well, basically it can be done to an extent in some kind of code. Basically I am drawing from a table 2 records at a time. I want to make sure that all records are pulled at least once. If at all possible not to have 2 from the same vendor. So, in this case. I have columns id, html, vendor So I can add columns as needed. I tried adding a flag and after returning to the client the 2 records I'd mark it as flag = 1 then search like this Select id, html from urls order by flag, id desc limit 3 Then after I have those I would then set the last of the 3 to flag = 1 so that on the next search I get the 2 after. In theory it worked fine but when multiple people hit the page simultaneously I had flags in different places and not in order. Maybe just mark them as flag = 1 after returned and then on search if found is 0 then set all to flag = 0 so they can be seen again? This doesn't seem so bad but them I guess I'd use distinct? If I cant use distinct with other parameters... ex: select id,html distinct(vendor) from urls where flag=0 limit 2; Would it be like : Select id,html from urls where flag = 0 group by distinct(vendor) limit 2 ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- From: Rhino [EMAIL PROTECTED] Date: Fri, 12 May 2006 14:20:10 -0400 To: Steffan A. Cline [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Returning records in a circle - Original Message - From: Steffan A. Cline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, May 12, 2006 1:38 PM Subject: Returning records in a circle Is there a way I can
Re: #1191 - Can't find FULLTEXT index matching the column list
See response below:
On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
It looks like today is my day! :)
I FULLTEXT indexed my table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_name` (`prod_name`),
FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;
When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list
What am I doing wrong?
You put 2 FULLTEXT indexes on different columns, and you're trying to
match against one multi-column index, which doesn't exist. Your table
creation allows:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
SELECT * FROM products
WHERE match (prod_description) against ('+red +shirt');
or
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt')
OR match (prod_description) against ('+red +shirt');
If you want to allow the query you originally wrote, you should have
one multi-column FULLTEXT index, like so:
FULLTEXT KEY `keyname` (`prod_name`,`prod_description`)
But I'm guessing what you want is to change your query -- which allows
you to match on either the name OR the description.
-Sheeri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown option --install
I'm going to guess that the path variable is only looking at the MySQL 3.23 mysqld binary. Try using a full path to the MySQL 5.0.20 binary and see if you still get errors. -Sheeri On 5/10/06, Miles Thompson [EMAIL PROTECTED] wrote: I am trying to install two MySQL servers to run as Windows XP services. One for work with php-gtk+ as mysqld1, MySQL 3.23.55 on port 3306 and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and testing. I'm following the manual's instructions in section 5.13.1.2. Starting Multiple Windows Servers as Services found at http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html The installation of mysqd1 went fine, but I consistently get an error when trying to install mysqld2, like so: C:\PROGRA~1\xampp\mysql\binmysqld-nt --install mysqld2 --defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf Which returns this error: 060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install' Huh? It's listed as one of the parameters after issuing mysqld-nt --help --verbose. More background: The previous instances of MySQL services have been removed. Have tried both forward / and back \ slashes in the defaults-file path Console window has been closed and reopened. mysql.ini in the \Windows directory has been renamed to mysql.ini.old Does anyone have any suggestions? They will be most welcome. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- 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: Questions about InnoDB, innodb_buffer_pool_size and friends
Hi Dan, You never really say what your memory problems are. Is MySQL crashing because you're trying to use too much memory? more comments inline On 5/11/06, Dan Trainor [EMAIL PROTECTED] wrote: What I'm dealing with here is memory problems using MySQL 5.0.19 under FreeBSD. Although I've enabled allocation of more memory per-process, as described by the FreeBSD notes for 5.0.x, I'm still seeing problems. [snip] We're trying to make our database 'hot', and stick as much of it as we can, into memory. I see a few problems with the current configuration (hopefully others can see more problems than I do): I don't have a innodb_log_file_size in there. I think that I would benefit from using this one, because the default is 5M. I believe that our bottleneck has a lot to do with disk I/O as well, so I think bringing this up substantially would help. Are your logs and data on a different partition? If not, put them on different partitions, preferably different disks. If we have four ibdataN files of 500M each, there's no way that we can make this database 'hot', especially when taking into consideration that this is a 32bit platform. On top of that, our innodb_buffer_pool_size is set to 1600M. ibdata files set up a tablespace allocation for innodb tables. This is the max they can get to, and MySQL reserves the disk space so nobody else can touch it. But what goes into memory is actual data and indexes. So memory isn't going to take 2,000M -- unless your tablespace is totally full. At least that's my understanding of it. Seems a waste to allocate memory for data that don't exist! Another idea would involve dumping a 32bit platform in favor of a 64bit platform, and just throw more memory at it. But who's employer would be fond of that? ;) Mine for one! You want your database hot, and in memory, ...but why? For performance? Performance for your paying customers? Who are currently complaining? Then buying hardware that can have more memory is a good investment. Basically, the question is, do you really need your database in memory or not? If so, your employer should be willing to spend the money. If not, your employer should be willing to deal with the fact that the db is not in memory, and any resultant slow performance. A rhetorical question to think about: How do you know your entire database isn't in memory? -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL crashes randomly
So, our MySQL master database crashes about once a week, then immediately recovers. We are running a Dell 2850 -- 64-bit Fedora Core 3 box with 6G of memory, 4 Intel Xeon processors, at 3.60 GHz speed each (says /proc/cpuinfo), each cpu cache size is 2048 Kb. It replicates to 2 slaves, which have the same hardware and memory. (the slaves don't crash). I've done everything at http://dev.mysql.com/doc/refman/4.1/en/crashing.html uname -a Linux dbhotsl1.manhunt.net 2.6.12-1.1381_FC3smp #1 SMP Fri Oct 21 04:22:48 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux cat /proc/meminfo MemTotal: 6142460 kB MemFree: 26564 kB Buffers: 15396 kB Cached: 805128 kB SwapCached: 1336 kB Active: 5503352 kB Inactive: 505792 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 6142460 kB LowFree: 26564 kB SwapTotal: 2096472 kB SwapFree: 2088036 kB Dirty: 1996 kB Writeback: 0 kB Mapped: 5195364 kB Slab: 78348 kB CommitLimit: 5167700 kB Committed_AS: 5532772 kB PageTables: 12384 kB VmallocTotal: 34359738367 kB VmallocUsed: 263636 kB VmallocChunk: 34359474295 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB The server regularly runs at 20-30 MB free memory all the time, so it's not (necessarily) a low memory issue. We get the dreaded Signal 11 error, and no core dumps even though we have core-file set in the [mysqld] of the my.cnf. Speaking of the my.cnf, here it is: --- [mysqld] core-file old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 10240 thread_cache = 80 ft_min_word_len = 3 # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/log/mysql/slow-queries.log log-error = /var/log/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # up to 15 Apache Servers with 256 connections each = 3840 # 5.8 G of memory = 2200 cxns # when you change this recalculate total possible mysqld memory usage!! # innodb_buffer_pool_size + key_buffer_size # + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) # + max_connections*2MB max_connections = 2200 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin=/var/log/mysql/dbhotsl1-bin server-id = 18 binlog-do-db = db1 binlog-do-db = db2 binlog-do-db = db3 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G;ibdata3:3G;ibdata4:3G; innodb_log_group_home_dir = /var/log/mysql/ innodb_log_files_in_group = 2 innodb_log_arch_dir = /var/log/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 ## [mysql.server] user=mysql basedir=/var/lib ## [safe_mysqld] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- And then the error file, pretty standard, not really telling me anything (and no stack trace): -- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2201 max_connections=2200 threads_connected=152 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5114862 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060427 23:56:44 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060427 23:56:44 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 752 3907332354. InnoDB: Doing recovery: scanned up to log sequence number 752 3912574976 InnoDB: Doing recovery: scanned up to log sequence number 752 3917817856 [...more of the same] InnoDB: Doing recovery: scanned up to log sequence number 752 4144467558 060427 23:57:09 InnoDB: Starting an apply batch of log records to
Re: MySQL crashes randomly
On 5/12/06, sheeri kritzer [EMAIL PROTECTED] wrote: So, our MySQL master database crashes about once a week, then immediately recovers. We are running a Dell 2850 -- 64-bit Fedora Core 3 box with 6G of memory, 4 Intel Xeon processors, at 3.60 GHz speed each (says /proc/cpuinfo), each cpu cache size is 2048 Kb. It replicates to 2 slaves, which have the same hardware and memory. (the slaves don't crash). I've done everything at http://dev.mysql.com/doc/refman/4.1/en/crashing.html uname -a Linux dbhotsl1.manhunt.net 2.6.12-1.1381_FC3smp #1 SMP Fri Oct 21 04:22:48 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux cat /proc/meminfo MemTotal: 6142460 kB MemFree: 26564 kB Buffers: 15396 kB Cached: 805128 kB SwapCached: 1336 kB Active: 5503352 kB Inactive: 505792 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 6142460 kB LowFree: 26564 kB SwapTotal: 2096472 kB SwapFree: 2088036 kB Dirty: 1996 kB Writeback: 0 kB Mapped: 5195364 kB Slab: 78348 kB CommitLimit: 5167700 kB Committed_AS: 5532772 kB PageTables: 12384 kB VmallocTotal: 34359738367 kB VmallocUsed: 263636 kB VmallocChunk: 34359474295 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB The server regularly runs at 20-30 MB free memory all the time, so it's not (necessarily) a low memory issue. We get the dreaded Signal 11 error, and no core dumps even though we have core-file set in the [mysqld] of the my.cnf. Speaking of the my.cnf, here it is: --- [mysqld] core-file old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 10240 thread_cache = 80 ft_min_word_len = 3 # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/log/mysql/slow-queries.log log-error = /var/log/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # up to 15 Apache Servers with 256 connections each = 3840 # 5.8 G of memory = 2200 cxns # when you change this recalculate total possible mysqld memory usage!! # innodb_buffer_pool_size + key_buffer_size # + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) # + max_connections*2MB max_connections = 2200 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin=/var/log/mysql/dbhotsl1-bin server-id = 18 binlog-do-db = db1 binlog-do-db = db2 binlog-do-db = db3 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G;ibdata3:3G;ibdata4:3G; innodb_log_group_home_dir = /var/log/mysql/ innodb_log_files_in_group = 2 innodb_log_arch_dir = /var/log/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 ## [mysql.server] user=mysql basedir=/var/lib ## [safe_mysqld] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- And then the error file, pretty standard, not really telling me anything (and no stack trace): -- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2201 max_connections=2200 threads_connected=152 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5114862 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060427 23:56:44 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060427 23:56:44 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 752 3907332354. InnoDB: Doing recovery: scanned up to log sequence number 752 3912574976 InnoDB: Doing recovery: scanned up to log sequence number 752 3917817856 [...more of the same] InnoDB: Doing recovery: scanned up to log sequence number 752 4144467558 060427 23:57:09
[SOLVED] Re: #1191 - Can't find FULLTEXT index matching the column list
Hi Sheeri
No, I wanted to search through both columns in the same time (and it
will be always at the same time) - the problem was I didn't know that I
have to have one multi-column index.
:)
But, it's fixed (after really helpful comments on this mailing list) and
works perfect
:)
Thanks to everybody!
-afan
sheeri kritzer wrote:
See response below:
On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
It looks like today is my day! :)
I FULLTEXT indexed my table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_name` (`prod_name`),
FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;
When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list
What am I doing wrong?
You put 2 FULLTEXT indexes on different columns, and you're trying to
match against one multi-column index, which doesn't exist. Your table
creation allows:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
SELECT * FROM products
WHERE match (prod_description) against ('+red +shirt');
or
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt')
OR match (prod_description) against ('+red +shirt');
If you want to allow the query you originally wrote, you should have
one multi-column FULLTEXT index, like so:
FULLTEXT KEY `keyname` (`prod_name`,`prod_description`)
But I'm guessing what you want is to change your query -- which allows
you to match on either the name OR the description.
-Sheeri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to extract common text string from field?
Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. Ferindo
Re: how to extract common text string from field?
Check the string functions SUBSTRING and LOCATE. As a hint look for the position of '@'. Ferindo Middleton escribió: Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to extract common text string from field?
Why not store them in separate columns? You could then have the domain field be a foreign key into another table. -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:14 PM To: mysql@lists.mysql.com Subject: how to extract common text string from field? Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to extract common text string from field?
In the last episode (May 12), Ferindo Middleton said: Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. SUBSTRING_INDEX http://dev.mysql.com/doc/refman/5.0/en/string-functions.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
