Upgrade from 3.22 to 4.0
I upgraded as stated in the subject, but my databases don't show up in mysql. The databases are still physically there, but mysql just ignores them. Apparently, it overwrote all my old system db data, because the users and privileges were gone too. I installed the binary version first, hoping that would be fast and painless. It didn't work, so I installed the source version, and it works fine, except that my system information is gone. I can add my databases back, but I can't re-create all the tables, and I'm not sure what that would do. The only step in the upgrade guide I didn't do was the mysql_convert_ table_format script, because I didn't have DBD set up on my perl installation, and had problems installing it. Also, I wasn't sure if it was strictly necessary. Is there a way to get my databases and tables back? I don't think I backed up my mysql db before upgrading (which I obviously should have done, in retrospect). Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DECIMAL math?
Bill, - Original Message - From: Bill Todd [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, September 08, 2003 3:02 AM Subject: DECIMAL math? Since DECIMAL fields are stored as strings how are calculations performed? Does MySQL use string math routines or does it convert the string to a double and use the double for the calculation? decimal math is currently handled by converting the decimal type to a double. The real question is, can floating point imprecision errors occur when using the DECIMAL type? I think yes, if you use a decimal with many significant numbers. http://www.mysql.com/doc/en/Open_bugs.html Calculation is done with BIGINT or DOUBLE (both are normally 64 bits long). It depends on the function which precision one gets. The general rule is that bit functions are done with BIGINT precision, IF, and ELT() with BIGINT or DOUBLE precision and the rest with DOUBLE precision. One should try to avoid using unsigned long long values if they resolve to be bigger than 63 bits (9223372036854775807) for anything else than bit fields. MySQL Server 4.0 has better BIGINT handling than 3.23. This is a well-known problem. String-based arithmetic is coming in a future version of MySQL. I have a question, too: if you are the Bill Todd who posts to the Borland newsgroups, what is the status of the new DBExpress driver for MySQL? The problem in old drivers was that they established a new connection for each individual SQL statement. Transactions and several other MySQL features did not work because of that. I saw some 4 weeks ago a Borland engineer mention that this is now fixed in a beta release of the driver. Is it so? Bill Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock escallation etc?
Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 05, 2003 4:31 AM Subject: Lock escallation etc? Hi all! Here's a question for all my fellow geeks / coders / software engineers / curious DB admins. I've been doing a lot of reading lately, and have noted a few things: 1. Out of the big three commercial databases, only Oracle seems to support nested transactions. Is there any practical purpose for such things now that InnoDB has partial rollbacks? If there is a purpose for such a construct, would implementing it hurt InnoDB's performance? I'm not asking for the feature at all, I'm simply curious. I believe transaction savepoints, which are supported by InnoDB, DB2, SQL Server, and Oracle, can do most of the things which nested transactions can. Simply establish a savepoint at the start of your subtransaction. I was not aware that Oracle has also a nested transaction feature, I thought they only have savepoints. 2. Reading up on MS SQL Server, the designers at MS seem to attribute the speed of the product to three major factors: * Their pool-of-threads architecture * The fact that tables are locked as much as they need to be and no more, with automatic lock escallation as required (Database-Table-Page-Row) In InnoDB and Oracle, big SELECTs are normally run as non-locking consistent reads. Then, of course, there is no need for lock escalation. DB2 and SQL Server are not multiversioned databases, like Oracle and InnoDB. To get consistent reports from tables you must in DB2 and SQL Server lock the data you read, and they will escalate the locks to page or table level when a threshold in the number of locks is reached. InnoDB's row locks are stored in a bitmap. They fit in very small space. That is why lock escalation in InnoDB is not needed even for locking reads, or UPDATE or DELETE. Lock escalation would also save some CPU time, but I think it would be much less than 10 % in a typical application. * Optimistic Conflict Control Obviously, the first of these is coming to MySQL eventually as listed in the TODO pages. Would adding the second point to MyISAM be useful at all, given the fact that we already have INSERT DELAYED? Would adding the second point to InnoDB speed it up, slow it down or would the benefits and pitfalls basically cancel each other out (Considering how fast InnoDB already is, I've a feeling that this is not something that would help performance)? As for Optimistic Conflict Control, this wouldn't apply to MyISAM, would it? Seeing InnoDB include it would be interesting, as Microsoft themselves have been a bit vauge as to the integrity implications of this feature. Application programmers seem to prefer pessimistic locking methods. In an optimistic method you cannot avoid transaction rollbacks due to serialization conflicts. If you use pessimistic locks and design your transactions carefully, then you can ensure no deadlocks occur, and you will not get any unexpected rollbacks. Please note: None of the above are feature requests! This is just one guy's curiosity getting the better of him. Regards, Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL research
Hi! - Original Message - From: Quoc kinh [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, September 08, 2003 7:04 AM Subject: MySQL research Dear Sirs, We are Vietnamese students and both of us are interested in database. Therefore, we did choose our graduation thesis that is researching some DBMS and designing a small DBMS which is relyed on that research. SO documents about MySQL architecture, especially about its storing in disk and memory are very important for us. Take a look at the file internals.texi in the public mysqldoc BitKeeper tree. Also, http://www.innodb.com/books.html contains some interesting links by Peter Gulutzan and others. Look also in the MySQL/InnoDB source code, and in the manual http://www.innodb.com/ibman.html. Can you be so kind for helping us ? We hope indeed yours help. Thank you very much. Yours truly. KINH QUOC NGYEN TUAN TRAN KIM Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very slow connexion
A DNS problem, may be ... Marc. -Message d'origine- De : JOUANNET, Rodolphe [mailto:[EMAIL PROTECTED] Envoyé : vendredi 5 septembre 2003 13:02 À : [EMAIL PROTECTED] Objet : Very slow connexion Hi, This problem is specific to the WAN connexion, not to the LAN connexion. I've a Sun Fire 880, 8 Go RAM, Solaris 8 and MySQL 4.0.13 64 bits. The first connexion to the database is about 15s to 30s (too slow). Is there a known problem about MySQL connexion (paramaters, bugs ;-) ) or is it a network problem ? Best regards -- 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]
question about mysql backup
Hi all, Up until now we always created database backups using the mysqldump tool. But I'm wondering, if it is possible to just create a tar archive of the complete mysql data directory. We are running on Linux (2.4.18) and we use mysql 4.014. Thanks, Harm de Laat Informatiefabriek The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 3.22 to 4.0
David B. Held [EMAIL PROTECTED] wrote: I upgraded as stated in the subject, but my databases don't show up in mysql. The databases are still physically there, but mysql just ignores them. Apparently, it overwrote all my old system db data, because the users and privileges were gone too. I installed the binary version first, hoping that would be fast and painless. It didn't work, so I installed the source version, and it works fine, except that my system information is gone. I can add my databases back, but I can't re-create all the tables, and I'm not sure what that would do. The only step in the upgrade guide I didn't do was the mysql_convert_ table_format script, because I didn't have DBD set up on my perl installation, and had problems installing it. Also, I wasn't sure if it was strictly necessary. Is there a way to get my databases and tables back? I don't think I backed up my mysql db before upgrading (which I obviously should have done, in retrospect). Do you lose all your databases and tables or tables of the database 'mysql'? Do you have databases directories and files of tables in these dirs? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calling my native functions
Hello, i know this must be the dumbest question by far (i think i´m even blushing myself), but how do i call my own native functions? i did all the steps in the item_create.* and the item_func.* but when call it from the mysql prompt i always get a sintax error, even if i call some function of their own like abs which is also defined in those files. Thanks!
Calling my native functions
Hello, i know this must be the dumbest question by far (i think i´m even blushing myself), but how do i call my own native functions? i did all the steps in the item_create.* and the item_func.* but when call it from the mysql prompt i always get a sintax error, even if i call some function of their own like abs which is also defined in those files. My function is called get_update and it is almost the same as the abs function. Thanks!
ANN: EMS MySQL Manager 2.6 released
EMS HiTech company is pleased to announce MySQL Manager for Windows 2.6 - the next version of our powerful MySQL administration and development tool! You can download the latest version and user's guide from http://www.mysqlmanager.com/download.phtml What's new in version 2.6? 1. We've added the MySQL Server v4.1 support to MySQL Manager. Starting from this version MySQL Manager allows you to connect to MySQL 4.1 using its authentication protocol, so no more client incompatibility messages are going to appear. 2. We've added a possibility to disable the separated thread creation for each data/result view in tables and SQL editors. This may be necessary if maximum allowed connection limit is too low. To use this possibility, just check the Use shared connection for each data view within a database option, which is located on the Grid - Advanced tab of the Environment Options dialog. 3. The foreign key checks can be disabled now while transferring databases by MySQL Manager. You can check the Disable foreign key checks option on the last step of the Transfer Database Wizard for this purpose. (*) This option is also available in the Extract Metadata Wizard, if it is checked, then the SET FOREIGN_KEY_CHECKS=0 command is generated at the top of the script. Please see full press release at http://www.ems-hitech.com/news.phtml?id=408 14. Fixed bug with transferring binary data. (*) (*) - Professional Edition only What is MySQL Manager? EMS MySQL Manager provides powerful tools for MySQL Server administration and object management. Its Graphical User Interface (GUI) allows you to create/edit all MySQL database objects in a simple and direct way, design databases visually, run SQL scripts, manage users and administer user privileges, visually build SQL queries, extract, print, and search metadata, create database structure reports in HTML format, export/import data, view/edit BLOBs, and supplies many more services that will make your work with the MySQL server as easy as it can be... Don't forget to check out other our products: http://www.ems-hitech.com/sqlmanagers Powerful database administration tools for MySQL, InterBase/FireBird, PostgreSQL and DBISAM servers http://www.ems-hitech.com/sqlutils/ Cross-platform data management utilities for MySQL, MS SQL, PostgreSQL and InterBase/FireBird servers http://www.ems-hitech.com/components/ Powerful components for Delphi/C++ Builder developers We hope you'll like our products. Thank you for your attention. Best Regards, EMS HiTech Team http://www.ems-hitech.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock escallation etc?
Heikki, Thank you for educating us all! Your obvious knowledge of other database vendor's products is impressive to say the least. In case it is of any interest to you, my information regarding Oracle supporting nested transactions comes from a third-year university text, used by the uni I am attending in our database course. The text is Database Systems and Concepts, 4th Edition by Silberschatz, Korth and Sudarshan. The last three chapters are essentially descriptions of product features and architecture for each of Oracle 9i, IBM DB2 8 and MS SQL Server 2000. This is where it is specifically mentioned that Oracle supports nested transactions (and also specifically mentioned that DB2 does not). The fact that the authors have not included any information about the world's fastest disk-based transaction-capable storage engine is a bit of a let down for an otherwise excellent book. On your TODO list, I have noticed that you have listed multiple tablespaces. In the above-mentioned information on Oracle, a big deal is made out of the way database partitioning is used to increase performance through way of the optimiser being able to select only required partitions for a given query and that various other partition operations assist with backups and adding data etc. Will multiple tablespaces add to the already impressive list of a bit like Oracle, but totally superior.? Regards, Chris On Mon, 2003-09-08 at 07:06, Heikki Tuuri wrote: Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 05, 2003 4:31 AM Subject: Lock escallation etc? Hi all! Here's a question for all my fellow geeks / coders / software engineers / curious DB admins. I've been doing a lot of reading lately, and have noted a few things: 1. Out of the big three commercial databases, only Oracle seems to support nested transactions. Is there any practical purpose for such things now that InnoDB has partial rollbacks? If there is a purpose for such a construct, would implementing it hurt InnoDB's performance? I'm not asking for the feature at all, I'm simply curious. I believe transaction savepoints, which are supported by InnoDB, DB2, SQL Server, and Oracle, can do most of the things which nested transactions can. Simply establish a savepoint at the start of your subtransaction. I was not aware that Oracle has also a nested transaction feature, I thought they only have savepoints. 2. Reading up on MS SQL Server, the designers at MS seem to attribute the speed of the product to three major factors: * Their pool-of-threads architecture * The fact that tables are locked as much as they need to be and no more, with automatic lock escallation as required (Database-Table-Page-Row) In InnoDB and Oracle, big SELECTs are normally run as non-locking consistent reads. Then, of course, there is no need for lock escalation. DB2 and SQL Server are not multiversioned databases, like Oracle and InnoDB. To get consistent reports from tables you must in DB2 and SQL Server lock the data you read, and they will escalate the locks to page or table level when a threshold in the number of locks is reached. InnoDB's row locks are stored in a bitmap. They fit in very small space. That is why lock escalation in InnoDB is not needed even for locking reads, or UPDATE or DELETE. Lock escalation would also save some CPU time, but I think it would be much less than 10 % in a typical application. * Optimistic Conflict Control Obviously, the first of these is coming to MySQL eventually as listed in the TODO pages. Would adding the second point to MyISAM be useful at all, given the fact that we already have INSERT DELAYED? Would adding the second point to InnoDB speed it up, slow it down or would the benefits and pitfalls basically cancel each other out (Considering how fast InnoDB already is, I've a feeling that this is not something that would help performance)? As for Optimistic Conflict Control, this wouldn't apply to MyISAM, would it? Seeing InnoDB include it would be interesting, as Microsoft themselves have been a bit vauge as to the integrity implications of this feature. Application programmers seem to prefer pessimistic locking methods. In an optimistic method you cannot avoid transaction rollbacks due to serialization conflicts. If you use pessimistic locks and design your transactions carefully, then you can ensure no deadlocks occur, and you will not get any unexpected rollbacks. Please note: None of the above are feature requests! This is just one guy's curiosity getting the better of him. Regards, Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Finding out the password a client is using
I have a clientprogram tring to connect to a MySQL database via ODBC, but the client is receiving '(access is denied to XX with password yes). ' How do I find out what password the client is sending? - Kenneth Lundström [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql backup
You may tar de mysql data directory but you shouldn't do it with mysql server running (because it is using the files). If you do not want to stop the mysql server (or lock it from writes), you can, for example, use mysqlhotcopy first to create an identical data directory and then do a tar on that copy (and then remove the copy). Cheers, On Mon, 2003-09-08 at 09:58, [EMAIL PROTECTED] wrote: Hi all, Up until now we always created database backups using the mysqldump tool. But I'm wondering, if it is possible to just create a tar archive of the complete mysql data directory. We are running on Linux (2.4.18) and we use mysql 4.014. Thanks, Harm de Laat Informatiefabriek The Netherlands -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different select syntax in 4.0.xx?
Sebastian Hoffmann [EMAIL PROTECTED] wrote: I have just updated from MySQL 3.23 to MySQL 4.0.14. My operating system is Mac OS X (10.2.6). I have now encountered an odd problem with some select statements which appear to work differently in the new version: I want to do a join on two tables which are in two different databases. In 3.23.xx, the following worked fine: mysql SELECT count(bncUserData.1062255927_sebhoff_stat.fnum) FROM bncUserData.1062255927_sebhoff_stat, bncData.headerInfo WHERE bncUserData.1062255927_sebhoff_stat.fnum=bncData.headerInfo.fnum and bncData.headerInfo.spowri=1; +-+ | count(bncUserData.1062255927_sebhoff_stat.fnum) | +-+ | 3 | +-+ 1 row in set (0.01 sec) However, if I try the same thing (with slightly different table names, but it's the same content and structure...) with 4.0.14, I get the following: mysql SELECT * FROM bncUserData.1062150666_sebhoff_stat, bncData.headerInfo WHERE bncUserData.1062150666_sebhoff_stat.fnum=bncData.headerInfo.fnum and bncData.headerInfo.spowri=1; ERROR 1109: Unknown table 'bncUserData.1062150666_sebhoff_stat' in where clause If I add aliases, however, everything works fine: mysql SELECT count(t1.fnum) FROM bncUserData.1062150666_sebhoff_stat as t1, bncData.headerInfo as t2 WHERE t1.fnum=t2.fnum and t2.spowri=1; ++ | count(t1.fnum) | ++ | 3 | ++ 1 row in set (0.08 sec) Reading the manual didn't help - as far as I can tell, aliases are optional... What am I doing wrong? What have I missed? Nothing. It's known bug, but it's not fixed yet. :( It would be pretty bad if I actually had to change the individual SQL queries - they are all created on the fly by perl scripts and rewriting this code would take a lot of time and debugging... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange slow queries (4.0.14)
I run a site with a few million MySQL requests a day, but I've run into a strange problem which I'm trying to slove. The server is running mysql 4.0.14, Apache 2.0.47, mod_perl2-1.99r09, DBI 1.38 and DBD-mysql 2.1026 on a FreeBSD 4.8 machine. About once or twice a day, I get a bunch of slow queries that doesn't look like they should be slow. And most of the day they aren't. Examples: # Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 update innodb_tbl set col1=col1-1000, col2=col2-1000, col3=col3-1000 where primary_key=50; # Query_time: 46 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 update innodb_tbl set col4=1 where primary_key=50; # Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 select * from innodb_tbl where primary_key=1 for update; . The list goes on and on... All of my slow queries are really fast at all other times. Fast as in instant. This doesn't look like a load issue, and I'm having problems figuring out what is causing this. It's mostly updates that are behaving slow, I even had one take 3306 seconds. The number of rows in the 'slowest' table is about 2000. Any ideas where to start digging and what I should try doing? I really need this solved. Regards, Erlend Simonsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Has the list gone down...
I haven't gotten any e-mails from the list in several days, are there problems? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Functions
Hello, in order to call a native function do i have to add something else to its name, i.e. mysql_get_update? because everytime i try to call my native function from the mysql prompt it gives me a syntax error.
SubQueries and IN
Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL IN does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the WHERE sales.customer_id = 10. (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! Thanks Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Functions
Gustavo, Prefix them with 'SELECT FUNCTION_CALL;' (e.g. 'SELECT VERSION();'). Regards, Adam -Original Message- From: Gustavo Castro [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:03 AM To: [EMAIL PROTECTED] Subject: Functions Hello, in order to call a native function do i have to add something else to its name, i.e. mysql_get_update? because everytime i try to call my native function from the mysql prompt it gives me a syntax error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Andy Hall wrote: Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL IN does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. subqueries requieres 4.x try SELECT product_id, name, description FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE NOT sales.customer_id = 10 I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the WHERE sales.customer_id = 10. (pretty sure this query would work in MS-SQL) this seems a bit different then this before but should work, does SELECT * FROM sales WHERE sales.customer_id = 10 AND sales.sale_id IS NULL return any results? There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SubQueries and IN
Andy: Sub queries are supported as of version 4.1 (see link #1). As for your query, double-check the syntax in the select piece. Specifically take out the 'sales.sale_id' and anything else from the 'sales' table. Then try again. Regards, Adam Link #1 - http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -Original Message- From: Andy Hall [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:02 AM To: [EMAIL PROTECTED] Subject: SubQueries and IN Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL IN does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the WHERE sales.customer_id = 10. (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! Thanks Andy Hall. -- 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: SubQueries and IN
sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. As far as I know, subqueries are only supported in MySQL v4(.1?)+ Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
* Andy Hall I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL IN does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. That is a very old version... you should upgrade if you can. Version 4.0 is the current recommended version: URL: http://www.mysql.com/downloads/index.html sub-queries will be allowed from mysql version 4.1 (not yet stable): URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html See also this page, describing which versions of MySQL will support which new feature: URL: http://www.mysql.com/doc/en/Roadmap.html I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the WHERE sales.customer_id = 10. (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! You need to get the conditions for the LEFT JOIN out of the WHERE clause: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id AND sales.customer_id = 10 WHERE sales.sale_id IS NULL Hope this helps, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Thanks for the query suggestions, but unfortunately none of them seem to do the trick. Not possible to upgrade to 4.x at the moment, so I am going to have to do it in 2 queries; one to get the list of ID's, then create a list in PHP and drop it into the second query. Thanks for the help! Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
You need to get the conditions for the LEFT JOIN out of the WHERE clause: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.sale_id IS NULL AND sales.customer_id = 10 I lied in my last email - this did do the trick! I was not aware that you could stick multiple clauses for the ON section. It all makes sense now! Thanks again Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Andy Hall [EMAIL PROTECTED] wrote: I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL IN does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the WHERE sales.customer_id = 10. (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! You can rewrite the initial query as: SELECT products.* FROM products, sales LEFT JOIN sales ss ON products.product_id=ss.product_id AND sales.product_id=ss.product_id WHERE sales.customer_id=10 AND ss.product_id IS NULL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yet another server vendor inquiry
Names of vendors who are happy to provide servers applicable for high load Linux/MySQL. Willing to do custom configurations. Anyone? -- Michael Bacarella24/7 phone: 1-646-641-8662 Netgraft Corporation http://netgraft.com/ Finger email address for public key. Key fingerprint: C40C CB1E D2F6 7628 6308 F554 7A68 A5CF 0BD8 C055 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. you are wrong, or do you know the table-structure? sales.sale_id can be NULL while customer_id can be 10 ! -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 3.22 to 4.0
Egor Egorov [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] [...] Do you lose all your databases and tables or tables of the database 'mysql'? Do you have databases directories and files of tables in these dirs? Never mind. Apparently, 3.22 was installed to /usr/local, and 4.0 was installed to /usr/local/mysql. Maybe I should have installed it to /usr/local again instead. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow select
I have an application, where I use the following select-query: select h.huvud_id,datum, start_tid, stopp_tid,l.lokal_beteckning, namn,adress,rubrik,amne,grupp,kursnamn,lr.signatur from huvud_schema as h,lokaler as l,huvud_amnen as ha,amnen as a, grupper as g,huvud_grupper as hg,kurser as k,kurs_huvud as kh, larare as lr,larare_huvud as lrh where h.lokal_beteckning=l.lokal_beteckning and h.huvud_id=ha.huvud_id and a.amn_id=ha.amn_id and h.huvud_id=hg.huvud_id and g.grupp_id=hg.grupp_id and h.huvud_id=kh.huvud_id and k.kurs_id=kh.kurs_id and h.huvud_id=lrh.huvud_id and lr.signatur=lrh.signatur and datum between '2003-09-01' and '2004-01-30' order by datum,start_tid,huvud_id,a.amn_id,lr.signatur The problem is that its very slow. Time measured (approx. 100 sec.) for 390 records. I have made an explain select etc with the same select. Which seem to point out the table amnen (alias a) or huvud_amnen (alias ha) as the culprit: table;type;possible_keys;key;key_len;ref;rows;Extra a;ALL;PRIMARY;NULL;NULL;NULL;7;Using temporary; Using filesort ha;ref;PRIMARY;PRIMARY;4;a.amn_id;3;Using index g;ALL;PRIMARY;NULL;NULL;NULL;13; lr;index;PRIMARY;PRIMARY;10;NULL;19;Using index k;ALL;PRIMARY;NULL;NULL;NULL;20; h;eq_ref;PRIMARY,datum;PRIMARY;4;ha.huvud_id;1;Using where l;eq_ref;PRIMARY;PRIMARY;15;h.lokal_beteckning;1; kh;eq_ref;PRIMARY;PRIMARY;8;k.kurs_id,h.huvud_id;1;Using where; Using index hg;eq_ref;PRIMARY;PRIMARY;8;g.grupp_id,h.huvud_id;1;Using where; Using index lrh;eq_ref;PRIMARY;PRIMARY;14;lr.signatur,h.huvud_id;1;Using where; Using index Why are for instance hg using eq_ref while ha uses ref, and g have no need for temporary or filesort, all though these tables are structured identically to a and ha. Any suggestions from what Ive presented so far? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb multiple tablespaces
Hi all, A few weeks ago, someone mentioned that Innodb would soon have multiple tablespaces available under MySQL. I saw that Innodb.com shows this on their to do list. Is anyone aware of the status of this upgrade, or a site that would have details regarding the upgrade? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Yet another server vendor inquiry
El Lunes, 8 de Septiembre de 2003 09:01, Michael Bacarella escribió: Names of vendors who are happy to provide servers applicable for high load Linux/MySQL. Willing to do custom configurations. Anyone? -- Michael Bacarella24/7 phone: 1-646-641-8662 Netgraft Corporation http://netgraft.com/ Finger email address for public key. Key fingerprint: C40C CB1E D2F6 7628 6308 F554 7A68 A5CF 0BD8 C055 Try: www.calforniadigital.com www.monarchcomputer.com Regards. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb multiple tablespaces
Sean, I am at this very moment programming them :). The deadline is Sept 15th, 2003. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html . Subject: Innodb multiple tablespaces From: sean peters Date: Mon, 8 Sep 2003 11:16:27 -0500 Hi all, A few weeks ago, someone mentioned that Innodb would soon have multiple tablespaces available under MySQL. I saw that Innodb.com shows this on their to do list. Is anyone aware of the status of this upgrade, or a site that would have details regarding the upgrade? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backing up all MySQL DBs
Hi everybody I'm pretty new to MySQL (and to this list). My problem is I need to get a dump of all databases in seperate files, I know how to do one database to one file and all databases to one file. So far I'm using this command: mysqldump --user $USERNAME --password=$PASSWORD $DBNAME | gzip $DBNAMEdb_$DATE.sql.gz Is there a way selecting all databases? René Mølsted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE TABLE and CHECK Clause
hi, it's the third time i'm trying to post this message to the list.. so please reply , even if it is for saying nothing.. only to be sure that this mail have been distributed. Thanx --- ok here we are, first of all, hi to every one since i'm new to this list. then it's my first real advanced experience with mysql, even if i've already used SQL Server, PostgreSQL and (kof kof) Access. well, then i'm trying to create a heavy database with many relations and check clauses. And i'm blocking on the check clause constraint. So i'm asking here for precisions: is the check clause working ? (i've seen some post saying that it is not working) Wich kind of tables (BDB, InnoDB, ) is supporting CHECK clauses, CASCADE DELETE, CASCADE UPGRADE, REFERENCES. and what is the problem with the following Query ? : CREATE TABLE IF NOT EXISTS definitions_avantages ( id_avantage BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom_avantage VARCHAR(128) NOT NULL UNIQUE CONSTRAINT CHECK ( ''), description_avantage TEXT) thanx for your help. Kraan Deufe even more than Kojak ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1064 in native function (?)
Hello,
i´m using the 3.23.57 version and i´ve declared a native function, but when
i call it (SELECT GETUPDATE(1);) i
get this line
ERROR 1064: You have an error in you SQL syntax near '(1)' at line 1;
I think i´ve followed all the steps right:
1)added the line in lex.h
{GETUPDATE ...)
2)added this line to item_create.h
Item *create_func_getupdate(Item* a);
3)added this definition to item_create.cc
Item *create_func_getupdate(Item* a){
return new Item_func_getupdate(a);}
4)added this to item_func.h
class Item_func_getupdate :public Item_num_func
{
public:
Item_func_getupdate(Item *a) :Item_num_func(a) {}
const char *func_name() const { return getupdate; }
double val();
longlong val_int();
enum Item_result result_type () const { return INT_RESULT; }
void fix_length_and_dec() { decimals=0; max_length=21; }
};
5) and at last added this to item_func.cc
longlong Item_func_getupdate::val_int()
{
FILE *fic;
fic = fopen(gus.txt,w);
fprintf(fic,Ejecucion \n);
fclose(fic);
return 0;
}
as you may see it´s a simple program which the only thing it has to do it´s
printing
that line (in the future it will do more things, but until then...).
I really need help because this is getting on my nerves!
Anyways, thank you very much!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Sebastian Tobias Mendel genannt Mendelsohn [EMAIL PROTECTED] wrote: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. you are wrong, or do you know the table-structure? No, I don't know table structure. sales.sale_id can be NULL while customer_id can be 10 ! Probably you misundernstood me. Look at the following example, there are 2 test table: t1 and t2. mysql select * from t1; +--+ | id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) mysql select * from t2; +--+--+ | id | name | +--+--+ |1 | vita | |3 | egor | |5 | tony | +--+--+ 3 rows in set (0.00 sec) Now I want to do this simple SELECT statement that is like author want to do: SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2 WHERE name='egor'). For versions before 4.1 I can rewrite NOT IN() using LEFT JOIN. Here is the output of LEFT JOIN: mysql select * from t1 left join t2 on t1.id=t2.id; +--+--+--+ | id | id | name | +--+--+--+ |1 |1 | vita | |2 | NULL | NULL | |3 |3 | egor | |4 | NULL | NULL | |5 |5 | tony | +--+--+--+ 5 rows in set (0.01 sec) As you can see, if I add condition t2.id IS NULL to the WHERE clause, I can't retrieve rows where name='egor'. That is why author didn't get any rows. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
$sth-rows problem
I've cobbled together a short PERL script to delete older records from the
tables in a database. The DELETE works fine but when I try to put in a
statement to return the number of rows deleted, I get an error.
The code reads as follows:
sub run_query {
# print Query: , $statement, \n;
my $sth = $dbh-do($statement)
or die Can't prepare $statement: $dbh-errstr\n;
$numrows = ($sth-rows); # This is where the problem is
print $numrows;
}
$dbh= DBI-connect(DBI:mysql:$db:host=$host, $user, $pass);
$statement=SELECT rid FROM router;
# print Query: , $statement, \n;
$sth = $dbh-prepare($statement)
or die Can't prepare $statement: $dbh-errstr\n;
$rv = $sth-execute
or die Can't execute the query: $sth-errstr\n;
while (@row = $sth-fetchrow_array ())
{
push (@rid, $row[0]);
}
foreach $rid (@rid) {
$statement=DELETE FROM ifInOctets_$rid WHERE dtime '$deldate
$deltime';
run_query($statement);
print $statement\t$numrows\n;
$statement=DELETE FROM ifOutOctets_$rid WHERE dtime '$deldate
$deltime';
run_query($statement);
print $statement\n;
$statement=DELETE FROM ifInErrors_$rid WHERE dtime '$deldate
$deltime';
run_query($statement);
print $statement\n;
$statement=DELETE FROM ifInUcastPkts_$rid WHERE dtime '$deldate
$deltime';
run_query($statement);
print $statement\n;
$statement=DELETE FROM ifOutUcastPkts_$rid WHERE dtime '$deldate
$deltime';
run_query($statement);
print $statement\n\n;
}
The error message is:
Can't call method rows without a package or object reference at
./cleandb.pl line 34.
Could someone please kindly point out the glaringly obvious thing that I'm
missing?
Thanks,
Jack
Jack Coxen
IP Network Engineer
TelCove
712 North Main Street
Coudersport, PA 16915
814-260-2705
Re: CREATE TABLE and CHECK Clause
Hi, Check this first: http://www.mysql.com/doc/en/CREATE_TABLE.html On Mon, 2003-09-08 at 18:34, Kraan Deufe wrote: hi, it's the third time i'm trying to post this message to the list.. so please reply , even if it is for saying nothing.. only to be sure that this mail have been distributed. Thanx --- ok here we are, first of all, hi to every one since i'm new to this list. then it's my first real advanced experience with mysql, even if i've already used SQL Server, PostgreSQL and (kof kof) Access. well, then i'm trying to create a heavy database with many relations and check clauses. And i'm blocking on the check clause constraint. So i'm asking here for precisions: is the check clause working ? (i've seen some post saying that it is not working) Wich kind of tables (BDB, InnoDB, ) is supporting CHECK clauses, CASCADE DELETE, CASCADE UPGRADE, REFERENCES. and what is the problem with the following Query ? : CREATE TABLE IF NOT EXISTS definitions_avantages ( id_avantage BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom_avantage VARCHAR(128) NOT NULL UNIQUE CONSTRAINT CHECK ( ''), description_avantage TEXT) thanx for your help. Kraan Deufe even more than Kojak ;) -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE and CHECK Clause
Kraan Deufe [EMAIL PROTECTED] wrote: ok here we are, first of all, hi to every one since i'm new to this list. then it's my first real advanced experience with mysql, even if i've already used SQL Server, PostgreSQL and (kof kof) Access. well, then i'm trying to create a heavy database with many relations and check clauses. And i'm blocking on the check clause constraint. So i'm asking here for precisions: is the check clause working ? (i've seen some post saying that it is not working) No, Currently CHECK doesn't work. Wich kind of tables (BDB, InnoDB, ) is supporting CHECK clauses, CASCADE DELETE, CASCADE UPGRADE, REFERENCES. FOREIN KEY CONSTRAINTS with ON DELETE CASCADE, ON UPDATE CASCADE is supported on InnoDB tables. and what is the problem with the following Query ? : CREATE TABLE IF NOT EXISTS definitions_avantages ( id_avantage BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom_avantage VARCHAR(128) NOT NULL UNIQUE CONSTRAINT CHECK ( ''), -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with the download of the Mysql GUI
To whom it may concerned, I am a web development support person working for North Carolina AT State University. I am involved in a library project right now which requires a MySQL free software installed on my computer so I can create a mysql database for the project. By searching the internet I found that MySQL GUI would be the perfect software for me to try. But I am not quite sure how to download it on my computer which has windows XP operating system. Could you give me instructions on how to download the software so I can start using it as soon as possible because the project deadline is getting closer. Thanks a lot! Looking forward to hearing from you! Liwen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different select syntax in 4.0.xx?
Hi! On Sep 07, Sebastian Hoffmann wrote: Hello, I have just updated from MySQL 3.23 to MySQL 4.0.14. My operating system is Mac OS X (10.2.6). I have now encountered an odd problem with some select statements which appear to work differently in the new version: I want to do a join on two tables which are in two different databases. In 3.23.xx, the following worked fine: mysql SELECT count(bncUserData.1062255927_sebhoff_stat.fnum) FROM bncUserData.1062255927_sebhoff_stat, bncData.headerInfo WHERE bncUserData.1062255927_sebhoff_stat.fnum=bncData.headerInfo.fnum and bncData.headerInfo.spowri=1; +-+ | count(bncUserData.1062255927_sebhoff_stat.fnum) | +-+ | 3 | +-+ 1 row in set (0.01 sec) However, if I try the same thing (with slightly different table names, but it's the same content and structure...) with 4.0.14, I get the following: mysql SELECT * FROM bncUserData.1062150666_sebhoff_stat, bncData.headerInfo WHERE bncUserData.1062150666_sebhoff_stat.fnum=bncData.headerInfo.fnum and bncData.headerInfo.spowri=1; ERROR 1109: Unknown table 'bncUserData.1062150666_sebhoff_stat' in where clause What am I doing wrong? What have I missed? Could it be case sensitivity issue ? On Mac OS X file names are case insensitive, right ? Try the query with table names written all lowercase (or all uppercase). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with the download of the Mysql GUI
I would use MySQL CC (command center, I think...)... I seem to remember someone mentioning that mysql gui is discontinued... CC is available from the mysql.org site, and is very easy to install on windows xp (it's on my laptop...) -Original Message- From: Liwen Han [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 2:34 PM To: [EMAIL PROTECTED] Subject: Need help with the download of the Mysql GUI To whom it may concerned, I am a web development support person working for North Carolina AT State University. I am involved in a library project right now which requires a MySQL free software installed on my computer so I can create a mysql database for the project. By searching the internet I found that MySQL GUI would be the perfect software for me to try. But I am not quite sure how to download it on my computer which has windows XP operating system. Could you give me instructions on how to download the software so I can start using it as soon as possible because the project deadline is getting closer. Thanks a lot! Looking forward to hearing from you! Liwen -- 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: $sth-rows problem
On 8 Sep 2003 at 14:15, Jack Coxen wrote:
sub run_query {
# print Query: , $statement, \n;
my $sth = $dbh-do($statement)
or die Can't prepare $statement: $dbh-errstr\n;
$numrows = ($sth-rows); # This is where the problem is
print $numrows;
}
This is a DBI question, not a MySQL question. Have a look at the
documentation for the do() method in DBI. It returns the number of
rows, not a statement handle.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1064 in native function (?)
Hi!
On Sep 08, Gustavo Castro wrote:
Hello,
i?m using the 3.23.57 version and i?ve declared a native function, but when
i call it (SELECT GETUPDATE(1);) i
get this line
ERROR 1064: You have an error in you SQL syntax near '(1)' at line 1;
I think i?ve followed all the steps right:
1)added the line in lex.h
{GETUPDATE ...)
What did you add to lex.h, exactly ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Btw, you may ask these questions on [EMAIL PROTECTED], instead
of general mysql list.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL full text search multiple tables
Greetings, I have been exploring MySQL's full text search feature and have not been able to find any information on querying a full text search across multiple tables. Do you have to make recursive queries to each table? Thank you in advance, Steve Radabaugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1064 in native function (?)
ups, i knew i had to copy that line properly.
what i added to the lex.h was this
{ GETUPDATE,SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_getupdate)}
i copied the line from the abs function. in fact, as you may see, all my
function is nothing but a copy of the abs function with little
modifications.
- Original Message -
From: Sergei Golubchik [EMAIL PROTECTED]
To: Gustavo Castro [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; Fortuno, Adam [EMAIL PROTECTED]
Sent: Monday, September 08, 2003 10:30 PM
Subject: Re: Error 1064 in native function (?)
Hi!
On Sep 08, Gustavo Castro wrote:
Hello,
i?m using the 3.23.57 version and i?ve declared a native function, but
when
i call it (SELECT GETUPDATE(1);) i
get this line
ERROR 1064: You have an error in you SQL syntax near '(1)' at line 1;
I think i?ve followed all the steps right:
1)added the line in lex.h
{GETUPDATE ...)
What did you add to lex.h, exactly ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Btw, you may ask these questions on [EMAIL PROTECTED], instead
of general mysql list.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.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: Need help with the download of the Mysql GUI
Dan Greene wrote: I would use MySQL CC (command center, I think...)... I seem to remember someone mentioning that mysql gui is discontinued... You remember right, the MySQL GUI IS discontinued. Why it is still availiable for download, ask MySQL Jakob ^-- To Unix or not to Unix. That is the question whether 'tis nobler in the mind to suffer slings and arrows of vast documentation or to take arms against a sea of buggy OS and by raping the support lines end then? ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BDB transaction isolation levels?
What transaction isolation level(s) does BDB support? I can't find any information on BDB transactions section 7.6 of the manual. Thanks, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to Create DB Connection
Hi, I am using the binary distribution of mySQL for Windows-2000 and am not able to connect to the DBserver. I used the Setup executable to install the application. I verified that the my.ini file was created and contained appropriate information. But, when I right-click with my mouse on the stop light icon- to start the service- nothing happens. I tried to ping the port that mySQL was supposed to be listening at, and it said that the connection was refused. So, it seems like I have not done something properly. Any thoughts as to how to trouble shoot this problem? Thanks, Scott = Scott D. Spiegler President Innovative Technical Solutions Pawtucket, RI 02861 __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BDB transaction isolation levels?
On Mon, Sep 08, 2003 at 03:26:34PM -0700, Bill Todd wrote: What transaction isolation level(s) does BDB support? I can't find any information on BDB transactions section 7.6 of the manual. BDB only does READ COMMITTED. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 1 days, processed 43,048,848 queries (401/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1064 in native function (?)
ups, i knew i had to copy that line properly.
what i added to the lex.h was this
{ GETUPDATE,SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_getupdate)}
i copied the line from the abs function. in fact, as you may see, all my
function is nothing but a copy of the abs function with little
modifications.
- Original Message -
From: Sergei Golubchik [EMAIL PROTECTED]
To: Gustavo Castro [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; Fortuno, Adam [EMAIL PROTECTED]
Sent: Monday, September 08, 2003 10:30 PM
Subject: Re: Error 1064 in native function (?)
Hi!
On Sep 08, Gustavo Castro wrote:
Hello,
i?m using the 3.23.57 version and i?ve declared a native function, but
when
i call it (SELECT GETUPDATE(1);) i
get this line
ERROR 1064: You have an error in you SQL syntax near '(1)' at line 1;
I think i?ve followed all the steps right:
1)added the line in lex.h
{GETUPDATE ...)
What did you add to lex.h, exactly ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Btw, you may ask these questions on [EMAIL PROTECTED], instead
of general mysql list.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.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]
binary data fields
are there any issues regarding special characters that should be escaped when storing binary data in a BLOB field? any special ascii codes that must be checked for before inserting data into a table? i remember reading something about ascii(26) causing some problems when using mysqldump and dumping back into a table. would this be related to the win32 version? abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bug report
030905 10:39:38 mysqld started 030905 10:39:40 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 2 3128426578 InnoDB: Doing recovery: scanned up to log sequence number 2 3128426578 InnoDB: Last MySQL binlog file position 0 762953481, file name ./makiki-bin.016 030905 10:39:40 InnoDB: Flushing modified pages from the buffer pool... 030905 10:39:40 InnoDB: Started /data/upena/soft/mysql/bin/mysqld: ready for connections. Version: '4.0.14-max-log' socket: '/tmp/mysql.sock' port: 3306 030905 20:06:18 InnoDB: Warning: using a partial-field key prefix in search A mysqld process already exists at Sun Sep 7 00:26:58 HST 2003 A mysqld process already exists at Sun Sep 7 00:27:59 HST 2003 A mysqld process already exists at Sun Sep 7 00:28:45 HST 2003 InnoDB: Error: trying to access page number 3422338945 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030908 11:27:35 InnoDB: Assertion failure in thread 370700 in file fil0fil.c line 1176 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 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=16777216 read_buffer_size=131072 max_used_connections=4 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x884f9d8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe3faf68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d9a90 0x40039a24 0x82ccda7 0x82a0809 0x82a0c55 0x82937b5 0x82c5e24 0x82c50e6 0x82730d3 0x828d7f5 0x8284dc0 0x8286e3f 0x821c398 0x813b40e 0x8140e6e 0x8143631 0x80e51cb 0x80e7fba 0x80e3483 0x80e2edd 0x80e26ce 0x40036e17 0x40191dda New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
escaping slashes not present in table?
mysql and php question: the magic_quotes_gpc is set to 1. when i echo it to the browser, it shows the added slashes. i inserted these same values into a database. when i read the database values and printed them, i forgot to use stripslashes. but i was surprised to see that the slashes were already removed. now i know this depends on magic_quotes_runtime, which i checked, was OFF, so how did the slashes get removed without me explicitly doing so? what's more interesting is that when i view the data in the table using phpMyAdmin, the quote i put wasn't escaped with a backslash. when i tried to do a dump/export of the table, the output showed a backslash before the quote. so how come? the slashes should be showing when i say SELECT * FROM MYTABLE. abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions abou innodb
We're considering switching to InnoDB tables for a couple of tables in our database where we need transactions. We're complete newbies with reference to innodb, although we are seasoned veterans with regards to MySQL/MyISAM and general concepts. We have come up with a couple questions that we haven't been able to find answers on elsewhere. If the answers are available online somewhere, please let me know. 1) can you join a MyISAM table to an innodb table in a select? 2) Under 'Restrictions...' in the manual, it says: When you restart the MySQL server, InnoDB may reuse an old value for an AUTO_INCREMENT column. Under what circumstances does this occur? 3) Are there any replication issues? 4) Any issues regarding a mixed MyISAM/InnoDB environment that we should worry about? 5) Anyone who has done this in the past -- how much of a pain was it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escaping slashes not present in table?
Abs wrote: mysql and php question: the magic_quotes_gpc is set to 1. when i echo it to the browser, it shows the added slashes. i inserted these same values into a database. when i read the database values and printed them, i forgot to use stripslashes. but i was surprised to see that the slashes were already removed. now i know this depends on magic_quotes_runtime, which i checked, was OFF, so how did the slashes get removed without me explicitly doing so? what's more interesting is that when i view the data in the table using phpMyAdmin, the quote i put wasn't escaped with a backslash. when i tried to do a dump/export of the table, the output showed a backslash before the quote. so how come? the slashes should be showing when i say SELECT * FROM MYTABLE. abs I think this is why people recommend that you *don't* use PHP's magic quotes. I hit this problem in a few areas and decided to turn it off. Use PHP's functions: stripslashes() and addslashes() You'll be sorry later if you don't, and continue using magic quotes... -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions abou innodb
Hi i have just started to use them aswell to do proper relationships between tables to add data restrictions on deleting records. 1) can you join a MyISAM table to an innodb table in a select? This seems to work. 2) Under 'Restrictions...' in the manual, it says: When you restart the MySQL server, InnoDB may reuse an old value for an AUTO_INCREMENT column. Under what circumstances does this occur? I have noticed say you empty the table it wont reset the auto inc back to 1 but from the start of the last auto inc until u drop the table and redo it. 3) Are there any replication issues? 4) Any issues regarding a mixed MyISAM/InnoDB environment that we should worry about? 5) Anyone who has done this in the past -- how much of a pain was it? Found alot of pain when creating the relationships with data already in there and if there are null values on the join keys. I have learnt if there is no value inputted against a joined table create another row in the joined table with a N/A and then update all the rows with null to this, it'll work then. Cool things about this is, if u delete a row from the other table and there is still keys joined to it in the child table it'll cause error. If you have selected cascade delete, if you delete the row in the parent it will delete all the rows in the child table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difference between Serializable and Repeatable Read with InnoDB
Since InnoDB does not allow phantom reads with Repeatable Read isolation (which are allowed in the ANSI SQL definition of Repeatable Read) what is the difference between these two isolation levels. Is it just serialization and that is all? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do InnoDB rollback segments expand dynamically?
Using InnoDB with an autoextend tablespace, if I start a transaction that results in many record versions, will the rollback segments grow dynamically and force the tablespace to grow dynamically to provide the required room for record versions in the rollback segments? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question on ansi mode
hi, I am working for our product to support MySQL ansi mode. But I don't understand the the meaning of the option ONLY_FULL_GROUP_BY, the description in manual is You can use a field/expression in GROUP BY that is not in the field list. , but I think it should be You can't Isn't it? thanks - Do You Yahoo!? []+
RE: Questions abou innodb
1) can you join a MyISAM table to an innodb table in a select? Absolutely. 2) Under 'Restrictions...' in the manual, it says: When you restart the MySQL server, InnoDB may reuse an old value for an AUTO_INCREMENT column. Under what circumstances does this occur? If you perform an INSERT into an InnoDB table w/ an AUTO_INCREMENT column, and for whatever reason the query fails (non-unique value in some unique column, or whatever), the AUTO_INCREMENT counter will still be incremented, despite the fact that the statement was rolled back. This happens even if you do an INSERT IGNORE, and the value that's been eaten will be returned by LAST_INSERT_ID() even though no row was actually inserted. Now, if I'm reading the documentation correctly, basically when you start MySQL the InnoDB engine does a SELECT MAX(auto_inc_column) FROM ... FOR UPDATE, and uses that to initialize the in-memory counter. So if the last thing that happened on a particular table before MySQL was shut down was a bunch of failed INSERTs, you'll have received some values which pointed to nothing at the time, but will wind up being reused by InnoDB after the restart. 3) Are there any replication issues? I have not encountered any InnoDB-specific replication issues. If anything, replication should be easier to deal with as you're guaranteed to not have half a transaction be visible on the slave if the link is severed... 4) Any issues regarding a mixed MyISAM/InnoDB environment that we should worry about? Just be real careful to not accidentally count on a ROLLBACK undoing something in a MyISAM table... 5) Anyone who has done this in the past -- how much of a pain was it? It's not a pain at all. There are things you need to be aware of that can bite you in the rear if you're looking for InnoDB's major benefits: Multi-versioned row-level locking is really really great. Finding out that INSERT INTO x SELECT ... FROM y still acquires a table lock on y is not so great. That's fixed in recent 4.0.x releases I think, but you get the idea. Deadlocks are also quite irritating, and need to be considered. In general, I tend to use InnoDB for pretty much everything. Assuming that you use transactions wisely InnoDB seems to be substantially faster for most operations than MyISAM (or at least, not noticably slower -- with a couple minor caveats, such as SELECT COUNT(*) FROM x), and it definitely scales better to large numbers of simultaneous users. Also, you get the benefits of FOREIGN KEY constraints and what not. There are a couple minor situations where InnoDB doesn't behave in a manner similar to MyISM: Having an AUTO_INCREMENT column as a secondary column in a PRIMARY KEY. If you do this in MyISAM, a separate counter value is maintained for each unique prefix, but this is NOT the case with InnoDB. InnoDB of course does not support FULLTEXT indexes, and so forth. -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--skip-locking and 'enable locking' in my.cnf
Hi all. I'm using MySQL-4.0.14, compiled by Gentoo's ebuild script. Somewhere along the line, I've picked up a '--skip-locking' flag that I can't find the source of. It's not in the /etc/init.d/mysql script. It's not in my my.cnf. Gentoo has a start-stop script which is run by scripts in /etc/init.d, but I doubt it's in there. Maybe it's a build-time option. Anyway, wherever it is, ps ax shows the mysqld is running with --skip-locking. It's been suggested that I add 'enable-locking' to the [mysqld] section of my.cnf. If I do this, which option will win: skip-locking, or enable-locking? -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --skip-locking and 'enable locking' in my.cnf
At 12:00 +1000 9/9/03, Daniel Kasak wrote: Hi all. I'm using MySQL-4.0.14, compiled by Gentoo's ebuild script. Somewhere along the line, I've picked up a '--skip-locking' flag that I can't find the source of. It's not in the /etc/init.d/mysql script. It's not in my my.cnf. Gentoo has a start-stop script which is run by scripts in /etc/init.d, but I doubt it's in there. Maybe it's a build-time option. Anyway, wherever it is, ps ax shows the mysqld is running with --skip-locking. /etc/init.d/mysql invokes mysqld_safe. Have a look there. It's been suggested that I add 'enable-locking' to the [mysqld] section of my.cnf. You might want to reconsider. It's disabled by default on all systems as of MySQL 4, and was disable by default before that on Linux. What do you want it for? If I do this, which option will win: skip-locking, or enable-locking? skip-locking, because it's added on the command line by mysqld_safe, and command line options take precedence over those in option files. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions abou innodb
InnoDB of course does not support FULLTEXT indexes, and so forth. Which is a pain, because i want the foreign key relationships but fulltext indexing at the same time :\ As for transactions , is it simply by doing this? start transaction do query commit if error rollback ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions abou innodb
At 13:06 +1000 9/9/03, [EMAIL PROTECTED] wrote: InnoDB of course does not support FULLTEXT indexes, and so forth. Which is a pain, because i want the foreign key relationships but fulltext indexing at the same time :\ As for transactions , is it simply by doing this? start transaction do query commit if error rollback Not quite. It's either commit or rollback, not commit and possibly rollback. start transaction do query if error rollback else commit -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions abou innodb
start transaction do query if error rollback else commit -- Heh thats what i meant ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maxDB
What is all this talk about max db, like why did they go and buy out SAP DB ? What is going to happen to different databases under the mysql banner or will the maxdb be implemented in mysql ? Because some of the features in sap, i think everyone would want into mysql, man i wish i could code c++ and help it move along quicker :\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --skip-locking and 'enable locking' in my.cnf
Paul DuBois wrote: At 12:00 +1000 9/9/03, Daniel Kasak wrote: It's been suggested that I add 'enable-locking' to the [mysqld] section of my.cnf. You might want to reconsider. It's disabled by default on all systems as of MySQL 4, and was disable by default before that on Linux. I see. I had always thought that locking would be a good idea for us. We have 40 or so people on a LAN, running MS Access XP front-ends, and some web clients as well. Many of the Access front-ends are in 'datasheet' mode - ie where users can see multiple records on the one screen. Why is locking disabled and not recommended? I had a look on the web site, and saw a few pages on locking, but didn't find any mention of locking being disabled. I found the source of the '--skip-locking' switch - it was in the mysqld_safe scripts. Thanks for that :) I haven't removed it yet - I'm thinking about it. See above... -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --skip-locking and 'enable locking' in my.cnf
At 13:25 +1000 9/9/03, Daniel Kasak wrote: Paul DuBois wrote: At 12:00 +1000 9/9/03, Daniel Kasak wrote: It's been suggested that I add 'enable-locking' to the [mysqld] section of my.cnf. You might want to reconsider. It's disabled by default on all systems as of MySQL 4, and was disable by default before that on Linux. I see. I had always thought that locking would be a good idea for us. We have 40 or so people on a LAN, running MS Access XP front-ends, and some web clients as well. Many of the Access front-ends are in 'datasheet' mode - ie where users can see multiple records on the one screen. I don't understand what external locking is supposed to give you here. Its primary use is for allowing multiple servers that access the same data directory to cooperate, or to allow mysqld and programs like myisamchk to cooperate. But both of these are inherently dangerous propositions unless the OS file locking is perfectly reliable. You're describing a system involving (I think) one server and a bunch of clients. This is the normal case, and the server handles contention among clients using its own internal locking, which should be sufficient. Why is locking disabled and not recommended? I had a look on the web site, and saw a few pages on locking, but didn't find any mention of locking being disabled. See: http://www.mysql.com/doc/en/System.html Note the part about Linux. I found the source of the '--skip-locking' switch - it was in the mysqld_safe scripts. Thanks for that :) I haven't removed it yet - I'm thinking about it. See above... -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Difference between Serializable and Repeatable Read with InnoDB
Conceptually, SERIALIZABLE just adds LOCK IN SHARE MODE to every SELECT query. Other than that there is not much difference. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Bill Todd [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 7:13 PM To: [EMAIL PROTECTED] Subject: Difference between Serializable and Repeatable Read with InnoDB Since InnoDB does not allow phantom reads with Repeatable Read isolation (which are allowed in the ANSI SQL definition of Repeatable Read) what is the difference between these two isolation levels. Is it just serialization and that is all? Bill -- 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: Backing up all MySQL DBs
Sure, use the --all-databases option instead of $DBNAME http://www.mysql.com/doc/en/mysqldump.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: René Mølsted [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 11:51 AM To: [EMAIL PROTECTED] Subject: Backing up all MySQL DBs Hi everybody I'm pretty new to MySQL (and to this list). My problem is I need to get a dump of all databases in seperate files, I know how to do one database to one file and all databases to one file. So far I'm using this command: mysqldump --user $USERNAME --password=$PASSWORD $DBNAME | gzip $DBNAMEdb_$DATE.sql.gz Is there a way selecting all databases? René Mølsted -- 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 multiple tablespaces
And will you be making your deadline? ;) -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:38 AM To: [EMAIL PROTECTED] Subject: Re: Innodb multiple tablespaces Sean, I am at this very moment programming them :). The deadline is Sept 15th, 2003. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html . Subject: Innodb multiple tablespaces From: sean peters Date: Mon, 8 Sep 2003 11:16:27 -0500 Hi all, A few weeks ago, someone mentioned that Innodb would soon have multiple tablespaces available under MySQL. I saw that Innodb.com shows this on their to do list. Is anyone aware of the status of this upgrade, or a site that would have details regarding the upgrade? 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: Has the list gone down...
Well, I saw your message. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 7:46 AM To: [EMAIL PROTECTED] Subject: Has the list gone down... I haven't gotten any e-mails from the list in several days, are there problems? -- 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: Unable to Create DB Connection
Well Scott, you're not done installing the Server yet. Go to the Doc sub_dir of your MySQL root dir. and brows the *manual_toc.html* file. You will find the info you need to get started there. Just a suggestion, don't use the winmysqladmin.exe GUI, or any other GUI tool for now, get used to the command line client first. BTW, The author of probably the best book ever written on MySQL frequently responds to e-mails on this list. Some well intended advice, get his book (MySQL Second Edition). Regards, Matt =-Original Message- =Hi, = =I am using the binary distribution of mySQL for =Windows-2000 and am not able to connect to the =DBserver. I used the Setup executable to install the =application. I verified that the my.ini file was =created and contained appropriate information. But, =when I right-click with my mouse on the stop light =icon- to start the service- nothing happens. I tried =to ping the port that mySQL was supposed to be =listening at, and it said that the connection was =refused. = =So, it seems like I have not done something properly. =Any thoughts as to how to trouble shoot this problem? = =Thanks, Scott = --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 9/1/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --skip-locking and 'enable locking' in my.cnf
On Mon, Sep 08, 2003 at 10:42:33PM -0500, Paul DuBois wrote: See: http://www.mysql.com/doc/en/System.html Note the part about Linux. Paul, you might update that page. It's extoling the virtues of the 2.2 kernel and SMP. But 2.4 is clearly superior in that department. And the 2GB file size limit has been gone for a couple years now. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 1 days, processed 47,861,708 queries (374/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied problem
I try to improve the security on the mysql and now I can't get into it. I get this massage. Access denied for user: '@localhost' to database 'mysql' Is the same throw webmin or phpMyAdmin Can anybody please help Im runnin Redhat 7.3 and mysql 3.23.56 /harly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Library licencing
Hi all! A group of friends and I are looking to build a funky piece of software that we plan to release under the dual-licencing model that MySQL AB and Innobase Oy use. My question, of course, relates to libraries for MySQL access. The code will be in either C or C++ (which means either the MySQL C API or Connector/C++). Is there anything special that we will need to do due to the nature of the licence for the libraries? The GPL version of the code would obviously be fine, but the commercial version would clearly need to pay up in some cases. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange replication problem
I have one master and one slave which I am upgrading to 4.0.14 from 4.0.12. To start the upgrade I stopped the slave, took a snapshot of it's data directory, cleared it's binary logs, and switched to the 4.0.14 binaries. I then restarted the slave thread to get it caught up with the master. The slave also runs with --log-slave-updates so that it has a copy of all of the data from the point of the snapshot. This afternoon at 2:10pm I switched our mysql.domain.com CNAME to point to the slave instead of the master (this is relevant). At this point, the slave is acting as the master and taking all of the updates. When I was sure all of the clients were using the slave I stopped it's slave thread and took down the master server to upgrade it as well. I rebuilt the master's data directory from the snapshot I'd taken previously on the slave. At this point I told the master to replicate the data off of the slave. Here's the strange part. The I/O thread seems to be grabbing the data off of the slave correctly. It writes relay logs just fine. However, the SQL thread doesn't update the database. SHOW SLAVE STATUS indicates that both parts are running normally. The I/O thread continues to write relay log files (deleting old ones as it goes as if it doesn't need them anymore). Then... at the point in logs for 2:10pm today when the CNAME was switched, all of the sudden the SQL thread decides to start updating the database. There isn't anything strange in the binary logs that I can see accept that the 'log_pos' value drops a fair amount at the same time it decides to start updating the database. I don't know what the means if anything. Why isn't it updating the database for all of the relay data? Considering that I've completely wiped the master's data directory except for the snapshot, cleared it's binary logs, and it's innodblogs... I'm completely at a loss for how it can know the exact time it stopped getting normal updates when it's CNAME changed. If you have any questions about my environment I'd be happy to answer them. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug report
Eric, Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it please use the resolve_stack_dump program in combination with the mysqld.sym which are shipped with the MySQL distro. You may have table corruption. Run CHECK TABLE on your tables. If it prints something to the .err log, please send the output to me. If you have problems starting up mysqld or dumping your tables, see http://www.innodb.com/ibman.html#Forcing_recovery for help. What does uname -a say about your Linux kernel? You should upgrade to a kernel = 2.4.20 if not yet running one. Earlier Linux kernels seem to cause corruption in many computers. 030905 20:06:18 InnoDB: Warning: using a partial-field key prefix in search Any idea what SQL query might be causing the above warning? Do you use LIKE 'abcd%' ? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Eric Aubourg [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 2:37 AM Subject: bug report 030905 10:39:38 mysqld started 030905 10:39:40 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 2 3128426578 InnoDB: Doing recovery: scanned up to log sequence number 2 3128426578 InnoDB: Last MySQL binlog file position 0 762953481, file name ./makiki-bin.016 030905 10:39:40 InnoDB: Flushing modified pages from the buffer pool... 030905 10:39:40 InnoDB: Started /data/upena/soft/mysql/bin/mysqld: ready for connections. Version: '4.0.14-max-log' socket: '/tmp/mysql.sock' port: 3306 030905 20:06:18 InnoDB: Warning: using a partial-field key prefix in search A mysqld process already exists at Sun Sep 7 00:26:58 HST 2003 A mysqld process already exists at Sun Sep 7 00:27:59 HST 2003 A mysqld process already exists at Sun Sep 7 00:28:45 HST 2003 InnoDB: Error: trying to access page number 3422338945 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030908 11:27:35 InnoDB: Assertion failure in thread 370700 in file fil0fil.c line 1176 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 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=16777216 read_buffer_size=131072 max_used_connections=4 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x884f9d8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe3faf68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d9a90 0x40039a24 0x82ccda7 0x82a0809 0x82a0c55 0x82937b5 0x82c5e24 0x82c50e6 0x82730d3 0x828d7f5 0x8284dc0 0x8286e3f 0x821c398 0x813b40e 0x8140e6e 0x8143631 0x80e51cb 0x80e7fba 0x80e3483 0x80e2edd 0x80e26ce 0x40036e17 0x40191dda New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it -- 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]
