Re: Do InnoDB rollback segments expand dynamically?
Bill, - Original Message - From: Bill Todd [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 4:27 AM Subject: 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? yes. 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 this 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: --skip-locking and 'enable locking' in my.cnf
- Original Message - From: Jeremy Zawodny Sent: Monday, September 08, 2003 11:33 PM Subject: 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. Heh, I was thinking the same thing. :-) And BTW, what are the file size limits now for the different Linux file systems (ext2/3, Reiser)? 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) Off topic: Been wondering about that Yahoo! MySQL server; are those MyISAM or InnoDB tables with that many queries/sec? -- 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
Hi! On Sep 09, 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. This option is not really about locking but only about external locking - which is necessary when several programs access the same table files, e.g. if you have several mysqld running on the same data directory. As this is hardly your setup :) - I assume you have only one mysqld process running - you don't need external locking. 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]
windows CE ???
Is there any problems using the jdbc-driver to connect to a mysql database from a windows CE device? Any recommentations? -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checksum over tables
Is there a way of getting one checksum for a structure of tables of a database. The problem we are facing is that we have 2 database servern with the same table structure and with the same data in the tables, it is NOT possible to sync both of them! The data in the data is not changed very often, but when we would like to just run a program over both of them and get from each one a checksum and then compare both of them. Is there a way of doing this, it has to be for the table structure and the table data. michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied problem
Michael Harly [EMAIL PROTECTED] wrote: 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' ? It means that there is no entry in the table user for 'your_user'@'localhost'. Where 'your_user' is a user name that you use to connect to the MySQL server. Is the same throw webmin or phpMyAdmin Can anybody please help I?m runnin Redhat 7.3 and mysql 3.23.56 -- 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]
Re: MySQL full text search multiple tables
Steve Radabaugh [EMAIL PROTECTED] wrote: 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? You can't create fulltext index on columns from different tables. Boolean full-text search can work without fulltext index. So, you can use columns from different tables in boolean full-text search, but it would be slow. -- 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]
MySQL Optimisation
Hi, I'm running MySQL on a Linux server with 2 processors, 4 Gb memory and SCSI disks. I am using MySQL to manage a database with 3 million reports and various auxiliary tables which are also fairly large. My question is: How do I make the best use of the 4 Gb memory available? I've tried increasing the key_buffer to 512M because most queries are between 3-6 tables using indexed fields. What other tuning should I try? regards, Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slightly ot - mysql.h errors
Hi All I'm currently trying to learn C so that I can recode some php/mysql apps I've got but I've run into probs right at the start. I'm trying to compile the examples in PDuBois MySQL (myclient.c the really easy one ;-( ) and get the errors below. I'm using SuSE 8.2 but also get the same errors under WinXP I'm obviously doing something wrong and I'd be grateful for any pointers Cheers Steve # gcc -c -I/usr/include/mysql myclient.c In file included from myclient.c:2: /usr/include/mysql/mysql.h:128: error: parse error before MEM_ROOT /usr/include/mysql/mysql.h:128: warning: no semicolon at end of struct or union /usr/include/mysql/mysql.h:129: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:169: error: parse error before MEM_ROOT /usr/include/mysql/mysql.h:169: warning: no semicolon at end of struct or union /usr/include/mysql/mysql.h:176: error: parse error before '}' token /usr/include/mysql/mysql.h:176: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:183: error: parse error before MYSQL_DATA /usr/include/mysql/mysql.h:183: warning: no semicolon at end of struct or union /usr/include/mysql/mysql.h:185: error: parse error before field_alloc /usr/include/mysql/mysql.h:185: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:189: error: parse error before '*' token /usr/include/mysql/mysql.h:189: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:191: error: parse error before '}' token /usr/include/mysql/mysql.h:191: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:196: error: parse error before '*' token /usr/include/mysql/mysql.h:197: error: parse error before '*' token /usr/include/mysql/mysql.h:198: error: parse error before '*' token /usr/include/mysql/mysql.h:199: error: parse error before '*' token /usr/include/mysql/mysql.h:201: error: parse error before '*' token /usr/include/mysql/mysql.h:202: error: parse error before '*' token /usr/include/mysql/mysql.h:203: error: parse error before '*' token /usr/include/mysql/mysql.h:205: error: parse error before '*' token /usr/include/mysql/mysql.h:206: error: parse error before '*' token /usr/include/mysql/mysql.h:207: error: parse error before '*' token /usr/include/mysql/mysql.h:208: error: parse error before '*' token /usr/include/mysql/mysql.h:209: error: parse error before '*' token /usr/include/mysql/mysql.h:210: error: parse error before '*' token /usr/include/mysql/mysql.h:211: error: parse error before '*' token /usr/include/mysql/mysql.h:212: error: parse error before '*' token /usr/include/mysql/mysql.h:214: error: parse error before '*' token /usr/include/mysql/mysql.h:214: error: parse error before '*' token /usr/include/mysql/mysql.h:214: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:222: error: parse error before '*' token /usr/include/mysql/mysql.h:222: error: parse error before '*' token /usr/include/mysql/mysql.h:223: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:224: error: parse error before '*' token /usr/include/mysql/mysql.h:227: error: parse error before '*' token /usr/include/mysql/mysql.h:227: error: parse error before '*' token /usr/include/mysql/mysql.h:233: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:242: error: parse error before '*' token /usr/include/mysql/mysql.h:243: error: parse error before '*' token /usr/include/mysql/mysql.h:244: error: parse error before '*' token /usr/include/mysql/mysql.h:245: error: parse error before '*' token /usr/include/mysql/mysql.h:247: error: parse error before '*' token /usr/include/mysql/mysql.h:248: error: parse error before '*' token /usr/include/mysql/mysql.h:250: error: parse error before '*' token /usr/include/mysql/mysql.h:251: error: parse error before '*' token /usr/include/mysql/mysql.h:252: error: parse error before '*' token /usr/include/mysql/mysql.h:253: error: parse error before '*' token /usr/include/mysql/mysql.h:254: error: parse error before '*' token /usr/include/mysql/mysql.h:256: error: parse error before '*' token /usr/include/mysql/mysql.h:257: error: parse error before '*' token /usr/include/mysql/mysql.h:258: error: parse error before '*' token /usr/include/mysql/mysql.h:259: error: parse error before '*' token /usr/include/mysql/mysql.h:261: error: parse error before '*' token /usr/include/mysql/mysql.h:262: error: parse error before '*' token /usr/include/mysql/mysql.h:263: error: parse error before '*' token /usr/include/mysql/mysql.h:263: error: parse error before '*' token /usr/include/mysql/mysql.h:263: warning: data definition has no type or storage class /usr/include/mysql/mysql.h:264: error: parse error before '*' token /usr/include/mysql/mysql.h:264: error: parse error before '*' token /usr/include/mysql/mysql.h:264: warning: data definition has no type or storage class
Innodb crash under Win2000
Description: Hello, We are working with mysqld-max-nt 4.0.14 under Win2000 service-pack4. We have a server with two Pentium-III MMX 500Mhz proccesors and 780MB Ram. We work with InnoDB tables. We have reserved 300MB to InnoDB and use a RAID 5. We were working when Mysql/Innodb crashed. We were not able to connect to MySql. We noticed that There wasnt the mysqld-max-nt service (this service is manual) So, we ran the service. Then, I tried to check the database but I lost the connection when I was checking a determined table, lincompras. So I run the service again, and altered the lincompras table type to myisam and later to innodb, checked again the database and didnt get any error. The error log file is: InnoDB: Dump of the child page: 030909 8:43:03 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 6068cb63041d0425042186968aac45 We have been working without problems but when I have opened the error log file I have seen the next error: MySql: preparado para conexiones 030909 12:05:22 InnoDB: error clustered record for sec rec not found InnoDB: index CliFecArt table peco/linventas InnoDB: sec index record RECORD: info bits 0 0: len 6; hex 303030333839; asc 000389;; 1: len 3; hex 8fa6ac; asc .;; 2: len 8; hex 3030343231343130; asc 00421410;; 3: len 10; hex 4a41303330b032323137; asc JA0302217;; 4: len 10; hex 58413033303033353134; asc XA03003514;; 5: len 2; hex 8001; asc .;; InnoDB: clust index record RECORD: info bits 0 0: len 10; hex 4a413033303035313931; asc JA03005191;; 1: len 0; hex ; asc ;; 2: len 2; hex 8001; asc .;; 3: len 6; hex 005acd6e; asc ...Zn;; 4: len 7; hex 805c010084; asc ..\..;; 5: len 0; hex ; asc ;; 6: len 3; hex 8fa729; asc .);; 7: len 2; hex 3030; asc 00;; 8: len 8; hex 3030323731303430; asc 00271040;; 9: len 32; hex 524f4c4c4f203530204d54532e43494e5441205445534142414e442d34363631; asc ROLLO 50 MTS.CINTA TESABAND-4661;; 10: len 8; hex f03f; asc ..?;; 11: len 1; hex 31; asc 1;; 12: len 8; hex 4a40; asc ..J@;; 13: len 4; hex 5242; asc ..RB;; 14: len 8; hex fca9f1d24d023640; asc M.6@;; 15: len 4; hex 8041; asc ..A;; 16: len 4; hex ; asc ;; 17: len 2; hex 4547; asc EG;; 18: len 3; hex 202020; asc;; 19: len 4; hex ; asc ;; 20: TRANSACTION 0 5953488, ACTIVE 1 sec, OS thread id 1864 fetching rows, thread declared inside InnoDB 415 MySQL thread id 89, query id 54296 192.168.1.210 lourdes Sending data SELECT `LinVentas`.`Can`, `LinVentas`.`MCa`, `LinVentas`.`Pre`, `LinVentas`.`Dto`, `Clientes`.`Ruta_Comercial`, `Ruta_Comercial`.`Nom`, `LinVentas`.`C InnoDB: Make a detailed bug report and send it InnoDB: to [EMAIL PROTECTED] Any ideas? Thanks in advance, Rafa How-To-Repeat: - Fix: - Synopsis:Innodb crash under win2000 Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.14 Exectutable: mysqld-max-nt Environment: 2 Pentium III-MMX, 500 MHZ, 780 MB System:Windows 2000 Compiler: - Architecture: i __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Questions Per Hour Per User?
I implement some simple per-user limits by setting max_questions to 800 (via GRANT). This is ok for virtually all users, but a few routinely hit that limit and are locked out for the hour. The choice of 800 queries/hour was arbitrary on my part. I would prefer to gather metrics on usage in order to pick a more appropriate limit. Besides grepping the log file, is there a way to query the server to determine how many queries have been performed by a user in the current hour? I assume there should be, since that is how a user would be locked out in the event that they hit a limit. Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- 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: Unable to Create DB Connection
Scott, You should be able to find a file titled 'mysql.err' in your data directory. This file will contain information that should help you get started. -Original Message- From: Scott D. Spiegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 6:59 AM To: [EMAIL PROTECTED] Subject: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Optimisation
* Jonathan Edwards I'm running MySQL on a Linux server with 2 processors, 4 Gb memory and SCSI disks. I am using MySQL to manage a database with 3 million reports and various auxiliary tables which are also fairly large. My question is: How do I make the best use of the 4 Gb memory available? I've tried increasing the key_buffer to 512M because most queries are between 3-6 tables using indexed fields. What other tuning should I try? If you only use MyIsam tables, key_buffer_size is one of the most important variables, you could probably increase it even more. If possible, make it so big that all your index files (*.MYI-files) would fit, but not more than 50% of your total memory: URL: http://www.mysql.com/doc/en/SHOW_VARIABLES.html URL: http://www.mysql.com/doc/en/Server_parameters.html If you also use InnoDb tables, there are a number of other variables you should look at: URL: http://www.mysql.com/doc/en/InnoDB_start.html URL: http://www.mysql.com/doc/en/InnoDB_tuning.html You don't describe your data or your usage pattern of the data. Are the rows very big? What's the total data size? index size? Are your tables fully normalized? Do you retrieve many rows at a time? Are there many simultanous users? Are the rows being updated? deleted? These things matter when it comes to optimization. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB data porting problem.
Hi guys, I have an INNODB database and I want to port the data to another one. Please let me know of the feasibilty of the idea and if any tools are available as this regards. Regards Barry
Re: Backing up all MySQL DBs
There's also # backup databases for dbname in `echo 'show databases;' | mysql -u$dbuser -p$dbpassword` do echo Backing up database $dbname... $destdir/backup.log mysqldump -u$dbuser -p$dbpassword $dbname | zip -q9 $destdir/db_$dbname.zip - $destdir/backup.log done WILL [EMAIL PROTECTED] - Original Message - From: Mike Hillyer [EMAIL PROTECTED] To: René Mølsted [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, September 08, 2003 9:02 PM Subject: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.h errors
Sinisa Milivojevic wrote: On Mon, 08 Sep 2003 19:57:25 +0100 Steve Davies [EMAIL PROTECTED] wrote: Hi All I'm currently trying to learn C so that I can recode some php/mysql apps I've got but I've run into probs right at the start. I'm trying to compile the examples PDuBois MySQL (myclient.c the really easy one ;-( ) and get the errors below. I'm using SuSE 8.2 but also get the same errors under WinXP I'm obviously doing something wrong and I'd be grateful for any pointers Cheers Steve Seems like you have damaged include files. Download latest source tarball from www.mysql.com. -- Sincerely, Yeah tried that - same error with includes from the tarball. Maybe I'm missing something when I call it - I'm using gcc -c -I/usr/include/mysql myclient.c -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do InnoDB rollback segments expand dynamically?
Heikki, You can get the new driver at http://codecentral.borland.com/codecentral/ccweb.exe/author?authorid=163237 I have not tried it to see if the multiple connection problem is fixed. I am new to MySQL and have not tried to use it with dbExpress yet. Bill -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:44 PM To: [EMAIL PROTECTED] Subject: Re: Do InnoDB rollback segments expand dynamically? Bill, - Original Message - From: Bill Todd [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 4:27 AM Subject: 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? yes. 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 this 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB data porting problem.
Yes it still is a MySQL DB but not on the same system albeit another system on the network. Regards Barry -- 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 Tue, Sep 09, 2003 at 01:30:51AM -0500, Matt W wrote: 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) Off topic: Been wondering about that Yahoo! MySQL server; are those MyISAM or InnoDB tables with that many queries/sec? That particular server uses both. It's about 30% InnoDB and 70% MyISAM. 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 63,716,173 queries (383/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] Blue World Lasso Professional 7 to include embedded MySQL
MySQL Colleagues: If you're interested in a new commercial-grade Web application server product which includes a commercial license of MySQL 4 embedded, please read on. We're excited to provide a version of Lasso with MySQL 4 built-in. This yields superior performance and provides tighter security and easier installation and maintenance. Thanks in advance for your interest. Enjoy! Bill - FOR IMMEDIATE RELEASE September 9, 2003 BLUE WORLD ANNOUNCES LASSO PROFESSIONAL 7 Support for embedded MySQL, Unicode, SOAP, image manipulation, data caching, and more to position Lasso Professional 7 as a leading Web application server. Bellevue, WA--September 9, 2003--Blue World Communications, Inc.--pioneers of the Web Data Engine(tm)--today announced Lasso Professional 7, a significant upgrade to its award-winning Web application server product for building and serving data-driven Web sites. Lasso Professional 7 introduces support for various industry standards while maintaining the ease-of-use and robust security that have won Lasso the support of thousands of Web developers worldwide. Lasso Professional 7 provides Web application developers a tool specifically designed to meet their most demanding needs without compromising ease-of-use, offering unprecedented productivity and flexibility, said Bill Doerrfeld, CEO of Blue World. Compliance with the latest industry standards positions Lasso Professional 7 at the forefront within the rapidly evolving Web application server tool arena. Lasso Professional 7 New Features Lasso Professional 7 (LP7) provides all of the features expected in an advanced Web application server, including the following new features: Embedded MySQL 4 Database Server - Includes a full commercial license of the MySQL database server embedded into the Lasso Server application. The embedded Lasso MySQL database server provides numerous advantages to the Lasso MySQL database provided in LP6 including greater performance, support for transactions, tighter security, and easier installation and maintenance. Internationalization and Localization - LP7 is now Unicode compliant, supporting hundreds of languages and character sets. Numbers, dates, and currencies can now be represented in localized formats. Dramatically Enhanced Performance - New byte-code compiler, algorithms and architecture provide vastly improved performance. LP7 outperforms virtually all other middleware products, in many cases by wide margins. SOAP/WSDL/XML Support - Facilitates communication with Web services via XML-based standards for data exchange. Supports SOAP calls so Lasso can be a Web services host. New XML implementation provides dramatic performance increase for both large and small documents while operating within a smaller memory footprint. Image Manipulation - Get information about images, resize images on the fly, create thumbnails, create watermarked images, or convert images from one format to another automatically. Apache 2 Web Server Support - Supports Apache 2 on Mac OS X (in addition to Apache 1.x) and Red Hat Linux. Caching for Enhanced Performance - Performance when communicating with slower databases can be dramatically enhanced through easy-to-use caching tags. Allows data served from databases such as FileMaker Pro to be served at speeds rivaling data served from MySQL databases. Enhanced and Simplified PDF Tags - Existing PDFs are now editable. PDF tags have been streamlined and take advantage of new features provided in an updated iText library. Now its easy to add dynamic content to existing PDF-based templates. Enhanced Database Communication - Programmatically control data source connections and transaction support via enhanced inline functionality. Ensures appropriate submission of data via Commit and Rollback routines. Manipulate database results as an array of returned records. Enhanced File Manipulation - Simplifies file manipulation with files now treated as objects. Provides enhanced performance for repeated access to the same file. Enhanced Network Communications - Simplifies network communications with network elements now treated as objects. Provides TCP and UDP support, listeners, and persistent connections. SSL support allows secure communications. Multi-Server Support - Facilitates load balancing and clustering by allowing multiple LP7 servers to share session data. LassoApp Maintenance - Lists open LassoApps within Lasso Administration for easier maintenance. Lasso Administration Enhancements - Streamlined interface focuses on administration tasks (e.g. Setup, Utility, Support) and provides new sections for controlling LassoApps and caching control settings. Remote Installation Support - Facilitates remote installation via the command line on Mac OS X. Lasso Security Enhancements - Can now check authorization against specific usernames and groups or against a custom username
RE: Unable to Create DB Connection
Oh, right- I forgot to mention that. Part of the error message in trying to connect to the database said that it couldn't find the mysql.err file. I installed mySQL in C:\mysql but the error message from the DOS shell said it couldn't find the file: D:\mysql\data\english\mysql.err. I am not sure why it was looking for that file on the D drive when I expected it to look on C:\mysql\data\english\mysql.err. That may not be the precise path that I am quoting, but - let's assume it is for a minute- the message was confusing in that mySQL was looking for that file on my D drive, when the application is installed on C. Is there some way to tell mySQL to look in the a path of the C drive? Thanks, Scott --- Christensen, Dave [EMAIL PROTECTED] wrote: Scott, You should be able to find a file titled 'mysql.err' in your data directory. This file will contain information that should help you get started. -Original Message- From: Scott D. Spiegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 6:59 AM To: [EMAIL PROTECTED] Subject: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] = 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]
How to get previous and next result
Hi there! I'm currently developing a web where we let users create their own forums. All the messages (wherever they are posted) are stored in the same mysql table. When users read a certain message I would like to display the previous and next message in that forum. Since all the messages (of the different forums) are stored in the very same table I don't find how to guess what's the ID of the previous and next message. Is there any way to find those IDs? Thanks. Kisses, Maria __ 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: Do InnoDB rollback segments expand dynamically?
At 12:43 AM 9/9/2003, you wrote: Bill, - Original Message - From: Bill Todd [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 4:27 AM Subject: 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? yes. 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 this so? Heikki, Most people have given up on DBExpress for MySQL because of bugs. If you want a MySQL component that works well really with Delphi, check out MySQL DAC 1.50 from CoreLabs http://www.crlab.com/. They have an eval that you can download. I've been using it for the past 3 months and I'm really impressed. They have a FetchRows property that allows the query to fetch as little at 25 rows at a time from a large query (you can specify how many rows to fetch at a time). This allows a million row query to open instantly. The documentation and support are also excellent. And of course it handles InnoDb tables. :-) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial Capabilities of MySQL
Does anyone know of any programs that will parse e00 files and load the data into MySQL? Does anyone have any real world experience with the spatial capabilities of MySQL? Is it still somewhat beta or is it really ready for prime time? Thanks. Mike __ 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: How to get previous and next result
Maria, If you simply have a table with an id field that has the auto-increment attribute (field is int(10)) then you can get the current id and go from there. To get the current ID of a record you just created, you can use the mysql_insert_id($link) function in PHP, or the MySQL equivalent. Remember that mysql_insert_id can only be used after an INSERT and before a COMMIT. Of course, my advice assumes that each related message is stored in id sequence. If it is not, then your application might have to assign a unique sequential id (in addition to the id field I mentioned above) that associates the messages together as a group. Then you can construct an appropriate query to extract just what you need. I hope this helps. I am certain that you will have many other offers with advice for you that may be different than mine. I am an old database programmer and so think in those terms often, not like a SQL designer! Hugs, Mark Richards -Original Message- From: Maria Garcia Suarez [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 11:53 To: [EMAIL PROTECTED] Subject: How to get previous and next result Hi there! I'm currently developing a web where we let users create their own forums. All the messages (wherever they are posted) are stored in the same mysql table. When users read a certain message I would like to display the previous and next message in that forum. Since all the messages (of the different forums) are stored in the very same table I don't find how to guess what's the ID of the previous and next message. Is there any way to find those IDs? Thanks. Kisses, Maria __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions abou innodb
SET AUTOCOMMIT=0; -- Disable automatic COMMITs after each statement. -- Tx #1. Do your work here. COMMIT; -- or ROLLBACK if there was an error. -- Tx #2. Do more work here. COMMIT; -- etc... Keep in mind that errors can result in either the offending statement being rolled back (leaving the rest of the transaction intact), or in some cases the whole transaction. Also, certain types of query will result in an implicit COMMIT (UNLOCK TABLES for example). You would be wise to familiarize yourself with the particulars of how different statements and errors interact with transactions before attempting to actually implement them. Familiarizing yourself with how and why deadlocks happen, how to avoid them, and what to do when they happen is also wise and will save you a lot of time and down the road. -JF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 8:07 PM To: [EMAIL PROTECTED] Subject: 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get previous and next result
Hi there! --- Mark Richards [EMAIL PROTECTED] wrote: If you simply have a table with an id field that has the auto-increment attribute (field is int(10)) then you can get the current id and go from there. To get the current ID of a record you just created, you can use the mysql_insert_id($link) function in PHP, or the MySQL equivalent. Remember that mysql_insert_id can only be used after an INSERT and before a COMMIT. Of course, my advice assumes that each related message is stored in id sequence. If it is not, then your application might have to assign a unique sequential id (in addition to the id field I mentioned above) that associates the messages together as a group. Then you can construct an appropriate query to extract just what you need. I hope this helps. I am certain that you will have many other offers with advice for you that may be different than mine. I am an old database programmer and so think in those terms often, not like a SQL designer! The problem is that the ID is auto-incremented for all the messages in the table, not in groups. The first message posted in any forum of the site will have ID=1, the second ID=2 (wherever is it posted) and so on Kisses, Maria __ 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: How to get previous and next result
As I suggested.. Of course, my advice assumes that each related message is stored in id sequence. If it is not, then your application might have to assign a unique sequential id (in addition to the id field I mentioned above) that associates the messages together as a group. Then you can construct an appropriate query to extract just what you need. In other words, it's possible that it is up to your application to handle the organization and relation of the records. -m- -Original Message- From: Maria Garcia Suarez [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 12:35 To: Mark Richards; [EMAIL PROTECTED] Subject: RE: How to get previous and next result Hi there! --- Mark Richards [EMAIL PROTECTED] wrote: If you simply have a table with an id field that has the auto-increment attribute (field is int(10)) then you can get the current id and go from there. To get the current ID of a record you just created, you can use the mysql_insert_id($link) function in PHP, or the MySQL equivalent. Remember that mysql_insert_id can only be used after an INSERT and before a COMMIT. Of course, my advice assumes that each related message is stored in id sequence. If it is not, then your application might have to assign a unique sequential id (in addition to the id field I mentioned above) that associates the messages together as a group. Then you can construct an appropriate query to extract just what you need. I hope this helps. I am certain that you will have many other offers with advice for you that may be different than mine. I am an old database programmer and so think in those terms often, not like a SQL designer! The problem is that the ID is auto-incremented for all the messages in the table, not in groups. The first message posted in any forum of the site will have ID=1, the second ID=2 (wherever is it posted) and so on Kisses, Maria __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get previous and next result
next: select cols from table where id YOUROLDID and forums = CURRENT_FORUM order by id limit 1 previous: select cols from table where id YOUROLDID and forums = CURRENT_FORUM order by id desc limit 1 however, this may not be as fast or efficient enough ou might want to create another id field that is a 'counter' for the messages in each forum. so instead of keeping track of the overall message id, keep track of the forum and the message id for that forums ie UMID = unique message id FMID = forum message id fid = forum id UMIDFMIDFID 1 1 a 2 2 a 3 3 a 4 1 b 5 1 c 6 2 b 7 4 a 8 3 b If this doesnt make any sense... it did in my head and Im probably doing a bad job of explaining it. Good luck -Original Message- From: Maria Garcia Suarez [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 10:53 AM To: [EMAIL PROTECTED] Subject: How to get previous and next result Hi there! I'm currently developing a web where we let users create their own forums. All the messages (wherever they are posted) are stored in the same mysql table. When users read a certain message I would like to display the previous and next message in that forum. Since all the messages (of the different forums) are stored in the very same table I don't find how to guess what's the ID of the previous and next message. Is there any way to find those IDs? Thanks. Kisses, Maria __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange replication problem
I found the problem which I will outline here just in case anyone else runs across it in the future: It appears that a slave will not replicate data from it's own server-id. In my case, a large portion of the binary logs on my slave had originally come from the master, so when the master tried to re-replicate the data, it simply ignored entries from it's own server-id. This makes complete sense, however, I can't find anywhere in the MySQL documention that explains this behavior. The documention only says that the master and slaves should have unique server-ids. -Mike 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]
Query won't use index
I have a query that won't seem to use an index. See below for the EXPLAIN, the tables and the indexes (relevant fields only, so no need to ask me why I'm bothering to do a query with nothing else in it). Note that if I change select t.Desc to select t.type_id, then MySQL correctly uses the index. What am I missing? - explain select t.Desc from files f, types t where t.type_id = f.type_id | t| ALL | PRIMARY,type_id | NULL|NULL | NULL |3 | | | f| ref | type_id | type_id | 4 | t.type_id | 2322 | Using index | mysql describe types; | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | type_id| int(11) | | PRI | NULL| auto_increment | | Desc | char(6) | YES | | NULL|| mysql describe files; --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+ | id | int(11) | | PRI | NULL | auto_increment | | type_id| int(11) | | MUL | 1 mysql show index from files; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | --+---+-+--++-+ | files | 0 | PRIMARY|1 | id | A |6965 | NULL | NULL | | | files | 1 | id |1 | id | A | 6965 | NULL | NULL | | | files | 1 | type_id|1 | type_id| A | 2 | NULL | NULL | | mysql show index from types; | Table | Non_unique | Key_name| Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Comment | +---+-+--++-+ | types | 0 | PRIMARY |1 | type_id | A | 3 | NULL | NULL | | | types | 1 | type_id |1 | type_id | A |NULL | NULL | NULL | | Thanks in advance! - Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get previous and next result
Having developed such an app myself (albeit in Oracle where the wonder of 'connect by'
exists which according to 'to-do' lists is coming for MySQL yippie!), this is what
I recommend...
1- if your forum is 'threaded', i.e. a message is in response to another, you can use
that linking to get your next/previous post based on post date, and the necessary
'in-reply-to-id' field.
2- if not, use a post_date field (which you should probably have anyway) and pull up
the next record with post_date {current msg post_date} in same forum, and last
record by post_date {current msg post_date} in same forum
-Original Message-
From: Maria Garcia Suarez [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 09, 2003 11:53 AM
To: [EMAIL PROTECTED]
Subject: How to get previous and next result
Hi there!
I'm currently developing a web where we let users
create their own forums. All the messages (wherever
they are posted) are stored in the same mysql table.
When users read a certain message I would like to
display the previous and next message in that forum.
Since all the messages (of the different forums) are
stored in the very same table I don't find how to
guess what's the ID of the previous and next message.
Is there any way to find those IDs?
Thanks.
Kisses,
Maria
__
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using windows authentication for mysql server
Hi , Is there any way i can use the os authentication in Windows(2000 or XP) to work with the mysql server? So that a person can get access to the server when they log in to a machine? Regards, Madhavi __ 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]
undefined reference to `errno' in Red Hat Linux 9
I cant build mysql-3.23.36 in Red hat Linux 9. But I can build succussfully in Red hat Linux 7 and 8. If I use mysql version greater than 4 I didnt get any error. But I want to build mysql-3.23.36. make[2]: Entering directory `/usr/local/mysql-3.23.36/client' /bin/sh ../libtool --mode=link c++ -O3 -DDBUG_OFF -fno-implicit-templates -rdynamic -o mysql mysql.o readline.o sql_string.o completion_hash.o ../readline/libreadline.a -lncurses ../libmysql/libmysqlclient.la -lz -lcrypt -lnsl -lm c++ -O3 -DDBUG_OFF -fno-implicit-templates -rdynamic -o .libs/mysql mysql.o readline.o sql_string.o completion_hash.o ../readline/libreadline.a -lncurses ../libmysql/.libs/libmysqlclient.so -lz -lcrypt -lnsl -lm -lz -lcrypt -lnsl -lm -Wl,--rpath -Wl,/usr/local/lib/mysql ../libmysql/.libs/libmysqlclient.so: undefined reference to `errno' collect2: ld returned 1 exit status make[2]: *** [mysql] Error 1 make[2]: Leaving directory `/usr/local/mysql-3.23.36/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/mysql-3.23.36' Please help me to solve this error thanks --mathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query won't use index
Ken, The problem is that you've got a compound index on files which type_id isn't the first item. If you create a new index on files, just on type_id, all will be fine. Mysql would only be able to use a compound index for this query if type_id was the first column in it. Andy -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: 09 September 2003 18:19 To: [EMAIL PROTECTED] Subject: Query won't use index I have a query that won't seem to use an index. See below for the EXPLAIN, the tables and the indexes (relevant fields only, so no need to ask me why I'm bothering to do a query with nothing else in it). Note that if I change select t.Desc to select t.type_id, then MySQL correctly uses the index. What am I missing? - explain select t.Desc from files f, types t where t.type_id = f.type_id | t| ALL | PRIMARY,type_id | NULL|NULL | NULL |3 | | | f| ref | type_id | type_id | 4 | t.type_id | 2322 | Using index | mysql describe types; | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | type_id| int(11) | | PRI | NULL| auto_increment | | Desc | char(6) | YES | | NULL|| mysql describe files; --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+ | id | int(11) | | PRI | NULL | auto_increment | | type_id| int(11) | | MUL | 1 mysql show index from files; | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Comment | --+---+-+--++-+ | files | 0 | PRIMARY|1 | id | A |6965 | NULL | NULL | | | files | 1 | id |1 | id | A |6965 | NULL | NULL | | | files | 1 | type_id|1 | type_id | A | 2 | NULL | NULL | | mysql show index from types; | Table | Non_unique | Key_name| Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Comment | +---+-+--++-+ | types | 0 | PRIMARY |1 | type_id | A | 3 | NULL | NULL | | | types | 1 | type_id |1 | type_id | A |NULL | NULL | NULL | | Thanks in advance! - Ken -- 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: [PHP] Lost Connection to MySQL server during query
I am using mysql_connect. But I just changed it to mysql_pconnect and it did exactly the same thing. -Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 10:48 AM To: Donald Tyler Cc: [EMAIL PROTECTED] Subject: Re: [PHP] Lost Connection to MySQL server during query Are you using mysql_pconnect or just mysql_connect? Donald Tyler wrote: I have a script that is processing data from a single table and splitting it into smaller normalized tables. The script takes anywhere between 5-10 minutes to complete, and as it runs it constantly outputs a report on its progress. The script seems to run fine the first time I run it, but with subsequent runs (To see if it incorrectly creates duplicates of records etc), after a couple of minutes the script ends with an error message saying Lost connection to MySQL server during query. During the first run, the script is mostly just chopping up the data and placing it in the new tables, and doing a little comparing. But during subsequent runs, all of the records in the new tables should already exist, so the script see's this, and instead of creating new records, it compares them to see if its correct. Does anyone have any idea why it would be losing connection all the time? This is just being run on a test server at the moment. Being used as the server and also the client, configured as follows: Windows XP Tablet Edition PHP 4.3.3 PHP Extensions: XmlRPC MySQL 4.0.14 Apache 2.0.47 Please help, this has been making my life hell for a week now! Thanks Donald -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SSL
Dear Sir/Madam: I have download MySQL 4.0 Windows version server binaries. Would you please tell me is this version support SSL? How to config it? Thanks a lot, Hong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search from multiple tables...
Hello everybody! Fulltext search fulfills all my needs, only it would be great if someone walked me through using multiple tables with this feature. How to address this MATCH to indexes on different tables? Things like MATCH(table1.column, table2.column) give me errors. Thanx in advance, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL SSL
Hong, I suggest reviewing the MySQL manual (sections 4.3.9.1 through 4.3.9.4 inclusive). As best I know, those are the manual sections dealing with SSL. 4.3.9.1 - http://www.mysql.com/doc/en/Secure_basics.html 4.3.9.2 - http://www.mysql.com/doc/en/Secure_requirements.html 4.3.9.3 - http://www.mysql.com/doc/en/Secure_Create_Certs.html 4.3.9.4 - http://www.mysql.com/doc/en/Secure_GRANT.html Regards, Adam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 10:34 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: MySQL SSL Dear Sir/Madam: I have download MySQL 4.0 Windows version server binaries. Would you please tell me is this version support SSL? How to config it? Thanks a lot, Hong -- 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: Fulltext search from multiple tables...
I have this strange feeling of deja-vu This was just posted to the list recently (last week?), and currently, you cannot do cross-table full-text indexes, excepting boolean text searches, which would be slow. I have this strange feeling of deja-vu Dan Greene -Original Message- From: Kutt Niinepuu [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 2:40 PM To: [EMAIL PROTECTED] Subject: Fulltext search from multiple tables... Hello everybody! Fulltext search fulfills all my needs, only it would be great if someone walked me through using multiple tables with this feature. How to address this MATCH to indexes on different tables? Things like MATCH(table1.column, table2.column) give me errors. Thanx in advance, -- 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]
HELP PLEASE the weirdest error 2013 / connection ?
Hi, this is the weirdest error I have ever encountered. We get an error 2013 lost connection when we try to connect via mysql to another machine having mysql on it as well. The weird part is that when we change the IP address of the connecting machine to another service provider it works fine. The problem does not happen the other way around, and the machines have the exact same configuration and both machine's mysql's have been upgraded to have the exact same version when we ran out of options on how to solve this. We checked passwords/hosts/users etc... It just does not make sense and we frankly ran out of options. Please help if you can. thanks Stew
Query pages by Alphabet
I have a page that is a directory of names ordered by lastname, firstname (e.g. SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 0,10). I have it paginated so that there are 10 results per page. What I'd like to do instead of having page numbers (which can be unhelpful when trying to page through people's names) is be more like a phone book and let people click on links that are the first letters of their last names For example, let's say my result set for SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 30, 10 is Jackson Johnson Knutson Kraig Liver Lombard Marx Maxx Milton Nixon The page link would be J-N I know how to get the letters for one page (well I think I do at least), but I want to get them for all pages So basically I'd have something like this for my page list: A B-C D E-G F-H I J-N O-Z and clicking on each page would result in entries only from that letter. The trick is I don't want more than 10 entries a page. Is there an easy way to do this? Possibly in a single query? Thanks -dd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using windows authentication for mysql server
At 10:29 -0700 9/9/03, Madhavi Kutty wrote: Hi , Is there any way i can use the os authentication in Windows(2000 or XP) to work with the mysql server? So that a person can get access to the server when they log in to a machine? MySQL doesn't know anything about your OS accounts. You use GRANT to set up MySQL accounts. You can, if you like, set up these accounts to have names and passwords like those of the OS accounts, but that's not necessary. -- 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]
Fw: [SQL] foreign key from a table to the *same* table
Hi to all...i've a problem...would anyone help me? I've a table called COURSES, where there are some universitary courses. I've a table called DIDACTIC_UNITS, where there are some subjects with some info like the professor, the course year, etc. The problem is that some didactic units can be lender to other didactic units. For example, if you are a student of the course of 'Medicine', you will see the lessons of Internet and the www with the student of the course of 'Information technology'. In this example, the didactic unit Internet and the www of the course 'Information technology' is the didactic unit lender for the didactic unit 'Information technology' of the course 'Medicine'. So, i need a sort of 'foreign key' from the table DIDACTIC_UNITS to the same table DIDACTIC_UNITSwith a field called DIDACTIC_UNIT_LENDER that point to the record of the didactic unit lender, or null if the didactic unit don't need another didactic unit. Below some of the table metadataplease help me...MySQL let me create the table and insert records, but problem begins with the clause ON DELETE SET NULL if i try to delete a record of a didactic unit that is a didactic unit lender for another didactic unit, the deletion falied So i think this isn't a good solutions...but how i can solve this problem? CREATE TABLE DIDACTIC_UNITS ( ID INTEGER NOT NULL AUTO_INCREMENT, ID_COURSE INTEGER NOT NULL, ID_PROFESSOR INTEGER, ID_PERIOD_OF_LESSON INTEGER, ID_DIDACTIC_UNIT_LENDER INTEGER, NAME CHAR(50), COURSE_YEAR INTEGER UNSIGNED, HOURS_OF_LESSON INTEGER UNSIGNED, CFU INTEGER UNSIGNED, OBLIGATORY BOOL DEFAULT 1, PRIMARY KEY(ID), INDEX didactic_units_id_course (ID_COURSE), INDEX didactic_units_id_professor (ID_PROFESSOR), INDEX didactic_units_id_period_of_lesson (ID_PERIOD_OF_LESSON), INDEX didactic_units_id_didactic_unit_lender (ID_DIDACTIC_UNIT_LENDER), FOREIGN KEY(ID_COURSE) REFERENCES COURSES(ID) ON DELETE CASCADE, FOREIGN KEY(ID_PROFESSOR) REFERENCES USERS(ID) ON DELETE SET NULL, FOREIGN KEY(ID_PERIOD_OF_LESSON ) REFERENCES PERIODS_OF_LESSON (ID) ON DELETE SET NULL, FOREIGN KEY(ID_DIDACTIC_UNIT_LENDER ) REFERENCES DIDACTIC_UNITS (ID) ON DELETE SET NULL ) TYPE = InnoDB; Please help methanks very much! Marco -- 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
- Original Message - 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...) /originalmessage I second that. It's easy on Linux too. Licks SQL Server 2000 Enterprise manager into a cocked hat (as my mother used to say) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP PLEASE the weirdest error 2013 / connection ?
Thanks Martin, No I do not have that option unfortunately. I really do suspect that it has nothing to do with mysql and that rather it is the router/IPs configuration and called them upon this, but they said all is fine. So I frankly have no clue where to go from here. thanks again for any suggestions ps: what is a flaky IP address? Stew At 03:10 PM 9/9/2003 -0700, Martin Gainty wrote: Sounds like the First ISP is providing a IP Address that is flaky. Can you drop the flaky first ISP and go exclusively with the second? -Martin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 11:50 AM Subject: HELP PLEASE the weirdest error 2013 / connection ? Hi, this is the weirdest error I have ever encountered. We get an error 2013 lost connection when we try to connect via mysql to another machine having mysql on it as well. The weird part is that when we change the IP address of the connecting machine to another service provider it works fine. The problem does not happen the other way around, and the machines have the exact same configuration and both machine's mysql's have been upgraded to have the exact same version when we ran out of options on how to solve this. We checked passwords/hosts/users etc... It just does not make sense and we frankly ran out of options. Please help if you can. thanks Stew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using windows authentication for mysql server
Hi, Thanks for your reply... I'm aware of this..But wondering if there is any way to do this, i mean integrate the mysql authentication with windows authentication... Thought it'd be good if users of my database application needn't type their username and passwords every time they start the app. Regards,Madhavi --- Paul DuBois [EMAIL PROTECTED] wrote: At 10:29 -0700 9/9/03, Madhavi Kutty wrote: Hi , Is there any way i can use the os authentication in Windows(2000 or XP) to work with the mysql server? So that a person can get access to the server when they log in to a machine? MySQL doesn't know anything about your OS accounts. You use GRANT to set up MySQL accounts. You can, if you like, set up these accounts to have names and passwords like those of the OS accounts, but that's not necessary. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ __ 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: Query pages by Alphabet
I'm not quite following what you are asking. If you want to limit the result to only 10 items per page, you're going to need to come up with some paginating code (it actually isn't that hard). You could easily get more than 10 names starting with a single letter. If you want to create specific links that show only name beginning with a letter or set of letters, then you need to do a search: SELECT fn, ln FROM directory WHERE ln like A% ORDER BY ln,fn LIMIT 10 or for a group of letters SELECT fn, ln FROM directory WHERE ln between BAAA AND CZZZ ORDER BY ln,fn LIMIT 10 That's actually kind a fudge on the search for a group of letters. Technically you should search for between A and D to get all names beginning with B-C, but I think it reads better this way from a coding readability standpoint. On Tuesday, September 9, 2003, at 02:55 PM, Dave Dash wrote: I have a page that is a directory of names ordered by lastname, firstname (e.g. SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 0,10). I have it paginated so that there are 10 results per page. What I'd like to do instead of having page numbers (which can be unhelpful when trying to page through people's names) is be more like a phone book and let people click on links that are the first letters of their last names For example, let's say my result set for SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 30, 10 is Jackson Johnson Knutson Kraig Liver Lombard Marx Maxx Milton Nixon The page link would be J-N I know how to get the letters for one page (well I think I do at least), but I want to get them for all pages So basically I'd have something like this for my page list: A B-C D E-G F-H I J-N O-Z and clicking on each page would result in entries only from that letter. The trick is I don't want more than 10 entries a page. Is there an easy way to do this? Possibly in a single query? Thanks -dd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query pages by Alphabet
And although it blows out your single-query theory further out of the water, you could query your listing for a count of each starting letter of the last name, getting something like this: [syntax will be wrong (closer to Oracle syntax), but I don't have a mysql installation at work to get it right] select substr(upper(ln),0,1), count(1) from directory group by substr(upper(lname),0,1) (I don't think MySQL needs the group by clause (it implies any non-specified non-group columns), but it's good practice for others to be able to maintain your code) A 2 B 15 C 4 E 2 (note skipping D, as there may be some letters that don't appear...) you could use your front/middle-end to go through this list first, grouping out your letters for the letter-specific queries (supplied by Brent below). I would reccomend caching out results of this query, as it won't change often enough to skew the results (likely) if you want to limit to 10 per page, you are going to need further pagination for entries with more than 10 entries per letter... -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 3:38 PM To: Dave Dash Cc: [EMAIL PROTECTED] Subject: Re: Query pages by Alphabet I'm not quite following what you are asking. If you want to limit the result to only 10 items per page, you're going to need to come up with some paginating code (it actually isn't that hard). You could easily get more than 10 names starting with a single letter. If you want to create specific links that show only name beginning with a letter or set of letters, then you need to do a search: SELECT fn, ln FROM directory WHERE ln like A% ORDER BY ln,fn LIMIT 10 or for a group of letters SELECT fn, ln FROM directory WHERE ln between BAAA AND CZZZ ORDER BY ln,fn LIMIT 10 That's actually kind a fudge on the search for a group of letters. Technically you should search for between A and D to get all names beginning with B-C, but I think it reads better this way from a coding readability standpoint. On Tuesday, September 9, 2003, at 02:55 PM, Dave Dash wrote: I have a page that is a directory of names ordered by lastname, firstname (e.g. SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 0,10). I have it paginated so that there are 10 results per page. What I'd like to do instead of having page numbers (which can be unhelpful when trying to page through people's names) is be more like a phone book and let people click on links that are the first letters of their last names For example, let's say my result set for SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 30, 10 is Jackson Johnson Knutson Kraig Liver Lombard Marx Maxx Milton Nixon The page link would be J-N I know how to get the letters for one page (well I think I do at least), but I want to get them for all pages So basically I'd have something like this for my page list: A B-C D E-G F-H I J-N O-Z and clicking on each page would result in entries only from that letter. The trick is I don't want more than 10 entries a page. Is there an easy way to do this? Possibly in a single query? Thanks -dd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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]
GRANT and REVOKE issue...
I seem to be having some trouble with using GRANT and REVOKE. I am implementing a user management form in my application that does the following: REVOKE ALL ON mydb.* FROM 'Username'@'%' I get an error Access denied for user: 'MyAdmin@ipaddress' to database 'mydb'. The Revoke statement is being executed (via MyODBC 3.51) connected as a user with the permission defined in the following script: -- Create User account if not exists: GRANT USAGE ON mydb.* to 'MyAdmin'@'%' identified by 'MyPwd'; GRANT UPDATE ON mysql.* to 'MyAdmin'@'%' identified by 'MyPwd'; FLUSH PRIVILEGES; -- Make sure no previously created user by this name has anything beyond the required privileges: REVOKE ALL ON mydb.* FROM 'MyAdmin'@'%'; FLUSH PRIVILEGES; USE mydb; -- assign required privileges: GRANT USAGE ON mydb.* TO 'MyAdmin'@'%' identified by 'MyPwd' WITH GRANT OPTION; GRANT DELETE, INSERT, SELECT, UPDATE ON TableX TO 'MyAdmin'@'%' identified by 'MyPwd' WITH GRANT OPTION; GRANT DELETE, INSERT, SELECT, UPDATE ON TableY TO 'MyAdmin'@'%' identified by 'MyPwd' WITH GRANT OPTION; GRANT SELECT ON TableZ TO 'CL.Admin'@'%' IDENTIFIED BY 'CL.4d31n' WITH GRANT OPTION; FLUSH PRIVILEGES; Any ideas why this doesn't work as I expect it to? I couldn't find any related info in the MySQL doc'n. TIA, Tore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.x LOAD DATA FROM MASTER wiping out tables...
I'm running MySQL 4.0.14 on Mac OS X 10.2.6 on a development server. We have around 60 tables on the development server and the production servers. 18 tables that exist on the production server are replicated to our development server. We use the replicate-wild-do-table config option to tell it which tables to replicate from the master (production server) to the slave (dev server). Somehow a REPAIR statement on one of the replicated tables failed on the slave recently. It left the .TMD temporary table file, and the .MYD data file disappeared altogether. I did STOP SLAVE; RESET SLAVE; LOAD DATA FROM MASTER; and it reloaded the 18 replicated tables from the production server. However, it also wiped out the other 44 tables that existed on the dev server. I looked at the manual page for the LOAD DATA FROM MASTER command (http://www.mysql.com/doc/en/LOAD_DATA_FROM_MASTER.html), and in the first paragraph it states: Will honor table and database exclusion rules specified with replicate-* options. So, why would it not honor my replicate-wild-do-table configuration and ONLY load those tables? Why would it wipe out everything in the database before doing reloading the replicated tables? Gabriel Ricard iMaxSales.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Distributing a DB
Hi, We are trying to find a way to distribute a large MySQL database across several systems, each configured as a master to a slave. At this point we are tossing architectural ideas around and here is where we are right now: Primary (Master) MySQL DB | +++---+ | | | | partitionA-G partitionH-M partitionN-SpartitionT-Z | | | | | | | | (MySQL Replication) | | | | VV VV slaveA-GslaveH-M slaveN-S slaveT-Z (slaves) Machines - Primary DB dual 2.2+ Ghz/1Gb RAM and 250Gb of RAID 1 storage, dual Gb eth (Gb Ethernet Switch) PartitionA-G dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionH-M dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionN-S dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionT-Z dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth (Gb Ethernet Switch) SlaveA-G dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveH-M dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveN-S dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveT-Z dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth The idea is that users would typically connect to the PartitionA-Z for normal read access. Overflow queries would connect to the SlaveA-Z. Update processes would connect to the Primary DB machine. For what its worth, we will be running RH 9.0, MySQL 4.0??? (depending on features we need to accomplish this); no two-phase commit transactional support required, no stored procs. I am not certain about how to split the database across multiple machines (or is can be done). we are also toying with the idea of using a hardware load balancer as a fabric of sorts to route traffic and possibly bi-directional replication shudder. Has anyone ever tried this? Have any thoughts? Thanks in advance. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Distributing a DB
If you're accessing your db through JDBC, an idea that I've been following is the c-jdbc project... http://c-jdbc.objectweb.org/ it's software raid clustering for databases... it's still in beta, but it looks very promising for easy clustering. Combined w/ MySQL's master/slave setup, it could be a very robust solution... it basically creates a virtual db out of the connected machines, to the point where you can have different tables on different boxes. It's at least worth looking into -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 4:36 PM To: [EMAIL PROTECTED] Subject: Distributing a DB Hi, We are trying to find a way to distribute a large MySQL database across several systems, each configured as a master to a slave. At this point we are tossing architectural ideas around and here is where we are right now: Primary (Master) MySQL DB | +++---+ | | | | partitionA-G partitionH-M partitionN-SpartitionT-Z | | | | | | | | (MySQL Replication) | | | | VV V V slaveA-GslaveH-M slaveN-S slaveT-Z (slaves) Machines - Primary DB dual 2.2+ Ghz/1Gb RAM and 250Gb of RAID 1 storage, dual Gb eth (Gb Ethernet Switch) PartitionA-G dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionH-M dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionN-S dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth PartitionT-Z dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth (Gb Ethernet Switch) SlaveA-G dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveH-M dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveN-S dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth SlaveT-Z dual 2.2+ Ghz/2Gb RAM and 2Tb of RAID 5 storage, dual Gb eth The idea is that users would typically connect to the PartitionA-Z for normal read access. Overflow queries would connect to the SlaveA-Z. Update processes would connect to the Primary DB machine. For what its worth, we will be running RH 9.0, MySQL 4.0??? (depending on features we need to accomplish this); no two-phase commit transactional support required, no stored procs. I am not certain about how to split the database across multiple machines (or is can be done). we are also toying with the idea of using a hardware load balancer as a fabric of sorts to route traffic and possibly bi-directional replication shudder. Has anyone ever tried this? Have any thoughts? Thanks in advance. Tony -- 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: Distributing a DB
In a message dated 9/9/03 4:52:53 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: If you're accessing your db through JDBC, an idea that I've been following is the c-jdbc project... Very nice, but we will be using LAMP (Perl and PHP). :-/ http://c-jdbc.objectweb.org/ it's software raid clustering for databases... it's still in beta, but it looks very promising for easy clustering. Combined w/ MySQL's master/slave setup, it could be a very robust solution... Interestingly comparable... it basically creates a virtual db out of the connected machines, to the point where you can have different tables on different boxes. Sounds very similar, but I need true partitioning, not separate tables. It's at least worth looking into Sure is, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost Connection to MySQL server during query
Hi Donald, Is the script possibly sending a query larger than max_allowed_packet (1MB default)? Have you read http://www.mysql.com/doc/en/Gone_away.html ? Matt - Original Message - From: Donald Tyler [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 10:36 AM Subject: Lost Connection to MySQL server during query I have a script that is processing data from a single table and splitting it into smaller normalized tables. The script takes anywhere between 5-10 minutes to complete, and as it runs it constantly outputs a report on its progress. The script seems to run fine the first time I run it, but with subsequent runs (To see if it incorrectly creates duplicates of records etc), after a couple of minutes the script ends with an error message saying Lost connection to MySQL server during query. During the first run, the script is mostly just chopping up the data and placing it in the new tables, and doing a little comparing. But during subsequent runs, all of the records in the new tables should already exist, so the script see's this, and instead of creating new records, it compares them to see if its correct. Does anyone have any idea why it would be losing connection all the time? This is just being run on a test server at the moment. Being used as the server and also the client, configured as follows: Windows XP Tablet Edition PHP 4.3.3 PHP Extensions: XmlRPC MySQL 4.0.14 Apache 2.0.47 Please help, this has been making my life hell for a week now! Thanks Donald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL full text search multiple tables
Hi,
Actually, I don't see why you can't have your full-text indexes on
seperate tables and use a query like this:
SELECT
MATCH(t1.col) AGAINST('string') + MATCH(t2.col) AGAINST('string') AS rel
FROM table1 t1
INNER JOIN table2 t2 ON (t2.id=t1.id)
WHERE MATCH(t1.col) AGAINST('string') AND -- or use OR :-)
MATCH(t2.col) AGAINST('string')
And even add ORDER BY rel DESC if you want.
If you use IN BOOLEAN MODE, you CAN use one MATCH(t1.col, t2.col), which
should use the full-text index from both tables.
HOWEVER, with either method, I'm not sure that the join is done using
the id index. :-( I think a full scan may be done on table2. This is
very bad if table2 has a lot of rows.
Matt
- Original Message -
From: Victoria Reznichenko [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, September 09, 2003 3:48 AM
Subject: Re: MySQL full text search multiple tables
Steve Radabaugh [EMAIL PROTECTED] wrote:
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?
You can't create fulltext index on columns from different tables.
Boolean full-text search can work without fulltext index. So, you can
use columns from different tables in boolean full-text search, but it
would be slow.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
many innodb datafiles on the same disk
Does anyone know if it is better to have 1 large innodb datafile on a disk or if it is better to have a few smaller datafiles? Here is what I have. Both drives are 15K scsi running at full 160MB/S speed. /var/lib/mysql/ - contains all myisam tables as well as a 1GB innodb datafile. /mysql2 - contains 3 innodb datafiles. Each 3GB in size. Would I be better off with a 9GB datafile on the /mysql2 disk or the 3 smaller files? The reason I ask is this machine is 1 of 2 slaves. We're planning on replacing our primary database with this one. I've run a query on this machine which is taking about a minute to run. If I run the same query on the other slave, it only takes about 13 seconds. The only difference in the mysql setup is that the other slave has a 6.2GB datafile on the /mysql2 partition instead of the 3 3GB datafiles. The other machine is also about 1/2 the speed of this one and runs on 5200rpm IDE drives. Below are the specs for the two machines. I'd try creating just a single datafile on the /mysql2 drive, but I don't want to shut down both slaves if I don't have to. Slow running slave - Athlon XP2200, 768MB RAM, 2 15K scsi drives running at full 160MB/s speed. set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=50M Fast running slave - Pentium III 600MHz, 500MB RAM, 2 5200 RPM drives. set-variable = innodb_buffer_pool_size=200M set-variable = innodb_additional_mem_pool_size=20M Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia If it's not broketweak it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with compiling MySQL
Hi all, I'm trying to compile MySQL 4.0.14 on a SUN Enterprise 220R, running on Solaris 9 12/02 with the latest patch clusters installed. I use gcc 3.2.3 to compile, along with gnu make 3.80 I've followed instructions in the MySQL Docs from www.mysql.com, so I've run this command : CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \-fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \--with-mysqld-ldflags=-all-staticThe configure command goes well, but when I type make, it starts making, but suddenly it drops back to the command line with the following message: ar cru libreadline.a readline.o funmap.o keymaps.o vi_mode.o parens.o rltty.o complete.o bind.o isearch.o display.o signals.o util.o kill.o undo.o macro.o input.o callback.o terminal.o xmalloc.o history.o histsearch.o histexpand.o histfile.o nls.o search.o shell.o tilde.o /bin/bash: ar: command not found make[2]: *** [libreadline.a] Error 127 make[2]: Leaving directory `/mail/Software/MySQL/4.0.14/mysql-4.0.14/readline' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/mail/Software/MySQL/4.0.14/mysql-4.0.14' make: *** [all] Error 2 During the make, I also see messages like this: gcc: unrecognized option '-03' while the documentation says the option '-03' is necesary. Can anybody help me on this ? Cheers, Andy
Re: Problems with compiling MySQL
Andy Kannberg wrote: Hi all, I'm trying to compile MySQL 4.0.14 on a SUN Enterprise 220R, running on Solaris 9 12/02 with the latest patch clusters installed. I use gcc 3.2.3 to compile, along with gnu make 3.80 I've followed instructions in the MySQL Docs from www.mysql.com, so I've run this command : CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \-fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \--with-mysqld-ldflags=-all-staticThe configure command goes well, but when I type make, it starts making, but suddenly it drops back to the command line with the following message: ar cru libreadline.a readline.o funmap.o keymaps.o vi_mode.o parens.o rltty.o complete.o bind.o isearch.o display.o signals.o util.o kill.o undo.o macro.o input.o callback.o terminal.o xmalloc.o history.o histsearch.o histexpand.o histfile.o nls.o search.o shell.o tilde.o /bin/bash: ar: command not found make[2]: *** [libreadline.a] Error 127 make[2]: Leaving directory `/mail/Software/MySQL/4.0.14/mysql-4.0.14/readline' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/mail/Software/MySQL/4.0.14/mysql-4.0.14' make: *** [all] Error 2 During the make, I also see messages like this: gcc: unrecognized option '-03' while the documentation says the option '-03' is necesary. Can anybody help me on this ? Cheers, Andy For starters, that's an O - a capital 'o', not the number 0, in -O3. The 'ar' program is a part of 'binutils'. Maybe you need to install some development libraries or something. Or at least binutils. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table corruption using DISABLE/ENABLE KEYS
I'm using MySQL v4.0.12 on Redhat Linux 9.0 I have a python script (using the MySQLdb module) that inserts a large batch of records to several MyISAM tables. The largest table (named 'log') has 20 columns. A single column defines the primary key. There isn't an auto increment column. Many of the other columns in the log table are indexed as well. This seemed like an ideal case to DISABLE KEYS, insert the data and then ENABLE KEYS based on the MySQL documentation. And running some time tests proves that this approach runs much faster. However, in doing so, the log table becomes corrupted (or at least the indecies do). mysql select * from log order by log_id; ERROR 1030: Got error 124 from table handler If I execute the same query without the order by clause things behave as normal. It seems as though the indecies (I've tried several of them) have become corrupted. I have tried write locking all of the tables prior to disabling the keys but that does not fix the problem. If I run mysqlcheck or 'REPAIR TABLE log;' the table/indecies behave normally. The question is this-- why do I need to repair the table in order for it to be usable? If I remove the disable/enable keys there are no indexing problems. The problem occurs only when the keys are disabled and enabled. Has anyone encountered this behavior before? Is it normal for me to *have* to run a repair table statement in order for this to work? Thank you for any information, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How much memory does MySQL need?
I know, I know, with database servers more is always better.g However, assume I need to deploy MySQL using InnoDB to multiple sites. Hardware cost is a significant consideration. Is there any information available on MySQL's memory requirements? Most database vendors give you something as a starting point but I could not find any information in the MySQL Manual. Can someone point me to a source? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How much memory does MySQL need?
In the last episode (Sep 09), Bill Todd said: I know, I know, with database servers more is always better.g However, assume I need to deploy MySQL using InnoDB to multiple sites. Hardware cost is a significant consideration. Is there any information available on MySQL's memory requirements? Most database vendors give you something as a starting point but I could not find any information in the MySQL Manual. http://www.mysql.com/doc/en/Memory_use.html http://www.mysql.com/doc/en/Server_parameters.html You can tune it to use as little as 32MB or as much memory as you have available. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calling a stored procedure from MS Access
Hi all. I have MySQL-5 running and have made a simple stored procedure which accepts a varaible and selects from a table based on that variable: create procedure msp_BillingBySavingsNo(input_SavingsNo int) select * from Billing where SavingsNo=input_SavingsNo If I enter the mysql client and type: call msp_BillingBySavingsNo(4211) I get the expected result; a few records that match the query. However if I create a pass-through query in MS Access, point it at MySQL, and type the same exact same text as above, I get: 'SELECT in a stored procedure must have INTO (# 1282)' Anyone know what's wrong? Thanks! -- 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: Distributing a DB
By partitioning, you mean having one table divided across N partitions? Such a feat isn't directly possible with MySQL, however you can create N tables instead of 1, and use a table of Type=RAID on the master to unify them for purposes of queries that need to access data from many partitions. -JF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 1:59 PM To: [EMAIL PROTECTED] Subject: Re: Distributing a DB In a message dated 9/9/03 4:52:53 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: If you're accessing your db through JDBC, an idea that I've been following is the c-jdbc project... Very nice, but we will be using LAMP (Perl and PHP). :-/ http://c-jdbc.objectweb.org/ it's software raid clustering for databases... it's still in beta, but it looks very promising for easy clustering. Combined w/ MySQL's master/slave setup, it could be a very robust solution... Interestingly comparable... it basically creates a virtual db out of the connected machines, to the point where you can have different tables on different boxes. Sounds very similar, but I need true partitioning, not separate tables. It's at least worth looking into Sure is, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling a stored procedure from MS Access
Daniel Pass thru query executes on an ODBC Database. Is your MySQL Server setup as an ODBC Database? -Martin - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 4:17 PM Subject: Calling a stored procedure from MS Access Hi all. I have MySQL-5 running and have made a simple stored procedure which accepts a varaible and selects from a table based on that variable: create procedure msp_BillingBySavingsNo(input_SavingsNo int) select * from Billing where SavingsNo=input_SavingsNo If I enter the mysql client and type: call msp_BillingBySavingsNo(4211) I get the expected result; a few records that match the query. However if I create a pass-through query in MS Access, point it at MySQL, and type the same exact same text as above, I get: 'SELECT in a stored procedure must have INTO (# 1282)' Anyone know what's wrong? Thanks! -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling a stored procedure from MS Access
Martin Gainty wrote: Daniel Pass thru query executes on an ODBC Database. Is your MySQL Server setup as an ODBC Database? -Martin Yes of course. -- 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: Distributing a DB
In a message dated 9/9/03 7:53:59 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: By partitioning, you mean having one table divided across N partitions? Yes, I do. Such a feat isn't directly possible with MySQL, however you can create N tables instead of 1, and use a table of Type=RAID on the master to unify them for purposes of queries that need to access data from many partitions. I've read about using symbolic links, but only for using multiple data directories, not splitting a file (not to mention, across more than one node). Admittedly, I am not a Linux/*nix guru, just adventurous and doing research soliciting opinions/ideas before I dive in. Since MySQL doesn't directly support this, any ideas on doing it at the OS level to 'trick' MySQL into believing its all in a single directory as a single table? It's looking more like I may have to build a DBsmart API to select the partition/machine containing the data I need. I recall a group of sharpsters at Temple U. did something similar to that. /t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Distributing a DB
I've read about using symbolic links, but only for using multiple data directories, not splitting a file (not to mention, across more than one node). Symlinks wont work for splitting a file... Since MySQL doesn't directly support this, any ideas on doing it at the OS level to 'trick' MySQL into believing its all in a single directory as a single Ironically enough, I was at one point looking for a Linux kernel module that would have done something fairly close to this... Never got it anywhere NEAR done though. However, even if you *could* split the data file at the OS level, whatever did the splitting would need to be aware of how the data is structured and split it in a meaningful way. Simply chopping the file into N pieces would be worse than useless (you'd very likely wind up with a row that was partly in one file and partly in another, plus you wouldn't be able to reliably predict which rows were in which partitions). table? It's looking more like I may have to build a DBsmart API to select the partition/machine containing the data I need. I recall a group of sharpsters at Temple U. did something similar to that. I'm unfamiliar with any such project. However you still have the problem of naming collisions. If you want the table to be named foo regardless of which partition it's in, and the master has copies of all partitions -- how would you arrange things on the master to avoid the naming collision? Thus we come back to using distinct table names for the partitions. On the master you can have a table foo which is the logical aggregate of all the partitions, and on any given machine you can have a table foo which is the logical aggregate of all the partitions that exist on that particular machine if you use the RAID table type. Alternatively you could consider a database like DB2 which supports seamlessly partitioning a table across N machines in a DB cluster based on the value of a key column (the portioning key). In such an arrangement, all rows with value X exist on server A, all rows with value Y exist on server B, and any process accessing any server will see one table that contains all rows (accessing rows from another server will be slower of course). -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing Unprintable Characters
I need to store strings encrypted with TripleDES that sometimes contain unprintable characters (Hexidecimal values under 20). The values do not seem to properly store in CHAR columns and I don't see any other column types that might work. Anyone have any other suggestions? Thank you in advance! Best Regards, Andrew Query sql
Re: Storing Unprintable Characters
In the last episode (Sep 09), Andrew Kuebler said: I need to store strings encrypted with TripleDES that sometimes contain unprintable characters (Hexidecimal values under 20). The values do not seem to properly store in CHAR columns and I don't see any other column types that might work. CHAR fields are allowed to strip trailing blanks. Try a BLOB field. Also make sure you escape your strings with whatever feature your language makes available. In C, for example, you would use mysql_real_escape_string(). If your interface supports bind variables, use them instead. http://www.mysql.com/doc/en/mysql_real_escape_string.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 Date Comparing
Dear All, We recently upgraded from 3.23 to 4.1. We used to use queries such as: SELECT field FROM table WHERE date_field like '2003-10-10%' (The date_field is a datetime field) Since we found the performance quicker than using the date functions. However this no longer works in 4.1, and the only way I can find to do the above is SELECT field FROM table WHERE YEAR(date_field) = YEAR(curdate()) AND MONTH(date_field) = MONTH(curdate()) AND DAY(date_field) = DAY(curdate()) Is there a better way, since I cant seem to find any functions to return the date_field to a date. JFYI, I have tried: SELECT ... FROM ... WHERE DATE(date_field) = curdate() according to the manual DATE() is available as of MySQL 4.1.1. SELECT ... FROM ... WHERE CURRENT_DATE(date_field) = curdate() Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 Date Comparing
This should work, but will not use any index on date_field: SELECT field FROM table WHERE DATE_FORMAT(date_field, %Y-%m-%d) = '2003-10-10'; This is better and will allow an index to be used if appropriate: SELECT field FROM table WHERE date_field = 2003-10-10 00:00:00 AND date_field DATE_ADD(2003-10-10 00:00:00, INTERVAL 1 DAY); -JF -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 8:21 PM To: [EMAIL PROTECTED] Subject: MySQL 4.1 Date Comparing Dear All, We recently upgraded from 3.23 to 4.1. We used to use queries such as: SELECT field FROM table WHERE date_field like '2003-10-10%' (The date_field is a datetime field) Since we found the performance quicker than using the date functions. However this no longer works in 4.1, and the only way I can find to do the above is SELECT field FROM table WHERE YEAR(date_field) = YEAR(curdate()) AND MONTH(date_field) = MONTH(curdate()) AND DAY(date_field) = DAY(curdate()) Is there a better way, since I cant seem to find any functions to return the date_field to a date. JFYI, I have tried: SELECT ... FROM ... WHERE DATE(date_field) = curdate() according to the manual DATE() is available as of MySQL 4.1.1. SELECT ... FROM ... WHERE CURRENT_DATE(date_field) = curdate() Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table corruption using DISABLE/ENABLE KEYS
On Tue, Sep 09, 2003 at 02:56:56PM -0700, [EMAIL PROTECTED] wrote: This seemed like an ideal case to DISABLE KEYS, insert the data and then ENABLE KEYS based on the MySQL documentation. And running some time tests proves that this approach runs much faster. However, in doing so, the log table becomes corrupted (or at least the indecies do). mysql select * from log order by log_id; ERROR 1030: Got error 124 from table handler If I execute the same query without the order by clause things behave as normal. It seems as though the indecies (I've tried several of them) have become corrupted. You should send a test caes into MySQL so they can fix the bug. Have you seen the section of the manual that describes how to report a bug and supply the files necessary to repoduce it? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 2 days, processed 83,341,207 queries (385/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting data from multiple databases
I have 2 databases, in each is a table called 'resources', my cleint has asked that they always be identical to each other. Database1 will be the master by which all else is made equal. My question: Should I just create website #2 to talk to Database #1, I am hesitant to do this as I have a connection reference that is nested in hundreds of scripts, I would have to create a second connection reference in all those files. I was thinking, perhaps mysql has a 'mirror' feature, or some command where I can tell it to either keep a eye on another table and always try to match it up. As a last resort, I can create a administrative system whereby the client works on one database, and when done, clicks a button that will drop the table in the secondary database, and copy the original into its place. Can someone tell me the pros and cons and offer up some suggestions? Thanks all. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem starting up mysql
Hi all, I installed mysql 4.0.14 binary distribution on RedHat 8.0 following the instructions in the manual. But I can't get it to work. Please help. When I start mysql using the command: shell bin/mysqld_safe --use=mysql the following message shows: Starting mysqld daemon with databases from /var/lib/mysql 030910 01:29:23 mysqld ended I have no idea what is going on here. If I try to test it with: shellcd sql-bench; perl run-all-tests I get the following error message: Got error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/m ysql.sock' (2)' when connecting to DBI:mysql:database=test;host=localhost with u ser: '' password: '' And mysql log has the following message 030904 13:03:06 mysqld started 030904 13:03:07 InnoDB: Started 030904 13:03:07 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 030904 13:03:07 Aborting 030904 13:03:07 InnoDB: Starting shutdown... 030904 13:03:09 InnoDB: Shutdown completed 030904 13:03:09 /usr/local/mysql/bin/mysqld: Shutdown Complete 030904 13:03:09 mysqld ended Anyone can tell me what goes wrong here? Thanks a lot. Claire __ 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]
Lost Connection to MySQL server during query
I have a script that is processing data from a single table and splitting it into smaller normalized tables. The script takes anywhere between 5-10 minutes to complete, and as it runs it constantly outputs a report on its progress. The script seems to run fine the first time I run it, but with subsequent runs (To see if it incorrectly creates duplicates of records etc), after a couple of minutes the script ends with an error message saying Lost connection to MySQL server during query. During the first run, the script is mostly just chopping up the data and placing it in the new tables, and doing a little comparing. But during subsequent runs, all of the records in the new tables should already exist, so the script see's this, and instead of creating new records, it compares them to see if its correct. Does anyone have any idea why it would be losing connection all the time? This is just being run on a test server at the moment. Being used as the server and also the client, configured as follows: Windows XP Tablet Edition PHP 4.3.3 PHP Extensions: XmlRPC MySQL 4.0.14 Apache 2.0.47 Please help, this has been making my life hell for a week now! Thanks Donald
