Re: MySQL version 4.1.x
Hi! - Original Message - From: Sasha Pachev [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, February 19, 2004 12:58 AM Subject: Re: MySQL version 4.1.x lee wrote: Anyone know when 4.1.x is scheduled for going Production? The website says it's currently in Alpha. We'd like to upgrade past 4.0.18 if possible, to have the improved subqueries, etc. in 4.1.x. I would guess 4.1.1 is roughly as good on the old features as the latest 4.0. However, I would stay away from subqueries for at least another six months except for a few that you really benefit from and have tested to make sure they work right and fast. A couple of months back my guess was that 4.1 would be labeled 'Production' in September 2004, or even a bit earlier. I would still give the same guess. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ 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 InnoDB which also backs up MyISAM tables 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: Which type of table to use?
I've been watching the group for a long time, and there is a huge debate about MyISAM vs InnoDB for large tables. It really does depend on what you want to use your table for, what hardware you running on, and what size you consider to be large! For me, I also have mainly numerical data, and 1-2 concurrent users at a time, generally only viewing the data, I found (to date, ongoing experiment) innodb with no performance configuration (same as default in supplied my.cnf files) tweaking slightly faster than a highly tweaked and studied MyISAM table with exactly the same data, server, application etc. Table sizes queried at that stage added up to 1GB. Under the InnoDB conversion, I ran into just under 2GB. MyISAM also has a slight maintenance (offline I run MyISAMChk frequently sorting the rows and indexes) overhead, which I understand InnoDB not to have. Many other sorts of bottlenecks creep in more easily at all layers of typical database usage, because ODBC, for instance, works (performance wise) differently on WIN XP SP1 and WIN 2K, fully SP-patched and updated, due to bugs under XP! JDBC works better or worse depending on which VM, and if you're following proper specification methods and good programming choices, or not! By wiser choice of ODBC settings under XP SP1, I had performance improve up to 8 times in my application. Under 2K, my performance merely doubled with the same settings. So OS, drivers, etc are huge players to be reckoned with as well. Bottom line, IMHO, benchmark them both, for your requirements, in your targetted environment(s). No respected DBA I know of makes any strategic choice without first benchmarking specific requirements, especially for large databases where users are going to be interactively querying them! User time, ultimately/normally, is the most expensive thing to waste. regards Rob -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Wednesday 18 February 2004 14:48 To: Craig Robinson Cc: [EMAIL PROTECTED] Subject: Re: Which type of table to use? Craig Robinson wrote: Hi, I intend to use MySQL for a database application, and I am not sure whether to use MyISAM or InnoDB tables. There will be a very large amount of (mainly numerical) data. The relations between the tables will be reasonably simple. Which table type would you recommend? The relationships between tables can be satisfied by either table type if they can be satisfied by the relational model. InnoDB does offer foreign key constraints though, allowing you to leave integrity checking to the database. InnoDB will also allow you to easily ROLLBACK transactions. MyISAM is faster in many cases, but InnoDB offers higher concurrency. Also, I find InnoDB tables take up about twice the space as MyISAM - is this correct? Yes, it is correct. One of the reasons is that InnoDB has to worry about keeping track of multiple versions of rows and which version of each row is to be seen by each currently running transaction. Cheers, Craig Robinson. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: oracledump.pl error
Obviously I wrote my original message too quickly and did not make myself clear. I am well aware that DB2 and numerous other commercial products work on Linux. What I meant to say was that, of all of the products that I could find listed on www.mysql.com for converting data from Oracle to MySQL, all of the commercial products except SQLPorter are written specifically for MS Windows and do not have Linux versions. That leaves the most obvious options for converting the data in my Oracle database to MySQL being oracledump.pl or rolling my own solution. If there are other tools that will do the job easily, either open source or low-cost commercial, I would like to hear about them. Rhino wrote: I don't have any insight into your problem but I want to take exception with your assertion that no other commercial products work on Linux. In fact, DB2 works on Linux and has for some time. Here's a link to information about this product: http://www-306.ibm.com/software/data/db2/udb/ If you nose around the IBM site a bit, you should be able to find a free full-function copy of DB2 for Linux. (By free, I mean you don't need a licence for it unless you use it in production. This enables you to get very acquainted with DB2 for free and only buy a licence if you like it enough to use it in production.) Rhino - Original Message - From: J. Allen Crider [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 10:54 PM Subject: oracledump.pl error I have just decided to try to learn something about MySQL after several years of working with Oracle and wanted to transfer the data I have in an Oracle 9i database to a new MySQL database. Since this is strictly for personal use, I can't justify the cost of SQLPorter, and none of the other commercial products work on Linux, so I decided to try oracledump.pl. However, I am getting the following error when I attempt to run it: DBD::Oracle::st execute failed: ORA-01459: invalid length for variable character string (DBD ERROR: OCIStmtExecute) [for statement ``SELECT a.COLUMN_ID, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.DATA_DEFAULT, b.COMMENTS FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME'' with params: :p1='ALBUMS']) at ./oracledump.pl line 471. (followed by several other errors obviously caused by this error.) Running the same statement in sqlplus works fine. I am running Oracle 9i Release 2 on Gentoo Linux. Any ideas on what I'm doing wrong? Allen Crider Huntsville, AL [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]
SQL-help needed
Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58.
Re: oracledump.pl error
Sasha Pachev wrote: J. Allen Crider wrote: I have just decided to try to learn something about MySQL after several years of working with Oracle and wanted to transfer the data I have in an Oracle 9i database to a new MySQL database. Since this is strictly for personal use, I can't justify the cost of SQLPorter, and none of the other commercial products work on Linux, so I decided to try oracledump.pl. However, I am getting the following error when I attempt to run it: DBD::Oracle::st execute failed: ORA-01459: invalid length for variable character string (DBD ERROR: OCIStmtExecute) [for statement ``SELECT a.COLUMN_ID, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.DATA_DEFAULT, b.COMMENTS FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME'' with params: :p1='ALBUMS']) at ./oracledump.pl line 471. (followed by several other errors obviously caused by this error.) Running the same statement in sqlplus works fine. I am running Oracle 9i Release 2 on Gentoo Linux. Any ideas on what I'm doing wrong? Wild guess - oracledump.pl has some kind of compatibily issue with your version of Oracle or there is a problem with the Oracle DBD driver. In any case, try debugging it - it is just trying to pull the names of Oracle table columns from the system tables, and is not doing it quite right. I would begin debugging by first making sure the Oracle DBD works ( you should be able to execute a simple Perl script that connects and reads Oracle data). P.S. Huntsville, AL is a very nice place I'm afraid part of my problem is that I haven't made time to learn Perl yet. Gentoo does not have an ebuild for the Oracle driver that I could find, so I downloaded DBD-Oracle 1.15 from CPAN and built it from source on my system. I did a make test after the make, which seemed to work after I got the environment variables correct. Since I did not provide a list of tables on the command line when I ran oracledump.pl and ALBUMS is a table in my Oracle database, I'm assuming that oracledump.pl was able to connect to my database and successfully execute the query SELECT TABLE_NAME FROM USER_TABLES. I found the error message on Oracle's web site, but their description of the error doesn't help me much: *Cause: * The buffer length was less than the minimum required or greater than its length at bind time minus two bytes. I don't know what buffer this is referring to, and since I don't know Perl, I've had no success trying to find anything in oracledump.pl that might be the buffer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 3.23 to 4.0 Problem
Im trying to upgrade 3.23 on my redhat 9 machine. I have one lone perl-dbd-mysql libmysqlclient dependency that is throwing me off. when i try to upgrade it wont go. so i try to uninstall that perl rpm and it says its mysql 3.23 needs it and when i try to uninstall mysql 3.23 it says the perl dbd needs it. how do i get around this catch? __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Last inserted id
Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? Thanks Binay
Error 2013 while using load data
Hi! I get error 2013: Lost connection to mysql server during query, while loading data into a table using load data infile... This happens only, when load data needs much time and files are rather big. Is there any time_out parameter i have to set/change? Best regards, Kai Wissenschaftliches Institut der AOK (WIdO) Kortrijker Straße 1 53177 Bonn Tel.: 0228/843-142 (Sekr.: -393) Fax: 0228/843-144 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23 to 4.0 Problem
I think that fastest way to get round this (mite be wrong) is to use the binary. Then all you need to do is change the sym link! This is a bit of a work round but should work? Simon - Original Message - From: Ross O [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 19, 2004 9:12 AM Subject: Upgrading from 3.23 to 4.0 Problem Im trying to upgrade 3.23 on my redhat 9 machine. I have one lone perl-dbd-mysql libmysqlclient dependency that is throwing me off. when i try to upgrade it wont go. so i try to uninstall that perl rpm and it says its mysql 3.23 needs it and when i try to uninstall mysql 3.23 it says the perl dbd needs it. how do i get around this catch? __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- 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: Last inserted id
The two simultaneous insert statements will be have separate connections to the database and last_insert_id() is connection specific. So if you're running apache, and you're worried about two different apache child processes getting the same connection ID, don't. Because those two children will have separate connections to the DB. If you're forking or threading and using the same connection, it becomes a bit more complex. Mark. On Thu, 2004-02-19 at 09:17, Binay wrote: Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? Thanks Binay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last inserted id
'connection ID' below should be 'last insert id'. Sorry, it's 2am here and I'm fresh out of coffee. BTW the information you want is here: http://www.mysql.com/doc/en/Information_functions.html#IDX1409 The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. On Thu, 2004-02-19 at 09:26, Mark Maunder wrote: The two simultaneous insert statements will be have separate connections to the database and last_insert_id() is connection specific. So if you're running apache, and you're worried about two different apache child processes getting the same connection ID, don't. Because those two children will have separate connections to the DB. If you're forking or threading and using the same connection, it becomes a bit more complex. Mark. On Thu, 2004-02-19 at 09:17, Binay wrote: Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? Thanks Binay -- Mark Maunder [EMAIL PROTECTED] ZipTree.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last inserted id
Binay [EMAIL PROTECTED] wrote: Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? LAST_INSERT_ID() is a connection specific function. It returns last generated value for auto_increment column for the current connection. Some other info about this function you can find in the MySQL manual: http://www.mysql.com/doc/en/Information_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last inserted id
Thanks Mark... You popped me out of great confusion/problem. Binay - Original Message - From: Mark Maunder [EMAIL PROTECTED] To: Binay [EMAIL PROTECTED] Cc: mysql users [EMAIL PROTECTED] Sent: Thursday, February 19, 2004 3:03 PM Subject: Re: Last inserted id 'connection ID' below should be 'last insert id'. Sorry, it's 2am here and I'm fresh out of coffee. BTW the information you want is here: http://www.mysql.com/doc/en/Information_functions.html#IDX1409 The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. On Thu, 2004-02-19 at 09:26, Mark Maunder wrote: The two simultaneous insert statements will be have separate connections to the database and last_insert_id() is connection specific. So if you're running apache, and you're worried about two different apache child processes getting the same connection ID, don't. Because those two children will have separate connections to the DB. If you're forking or threading and using the same connection, it becomes a bit more complex. Mark. On Thu, 2004-02-19 at 09:17, Binay wrote: Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? Thanks Binay -- Mark Maunder [EMAIL PROTECTED] ZipTree.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with with-extra-charsets=none
Przemyslaw Popielarski [EMAIL PROTECTED] wrote: I'm trying to build MySQL 4.0.18 for Linux x86 2.4.24 from source without support for extra charsets. Lets say I need only latin1. My configure: FLAGS=-O3 -march=pentium4 -mcpu=pentium4 -fomit-frame-pointer CXX=gcc CXXFLAGS=-O3 -march=pentium4 -mcpu=pentium4 -felide-constructors -fno-exceptions -fno-rtti -fomit-frame-pointer \ ./configure --prefix=/usr \ --with-mysqld-user=mysql \ --with-unix-socket-path=/var/run/mysql/mysql.sock \ --localstatedir=/var/lib/mysql \ --enable-assembler \ --without-debug \ --enable-thread-safe-client \ --without-bench \ --with-extra-charsets=none \ --program-prefix= \ --program-suffix= \ --without-innodb \ --without-isam \ --disable-shared \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static \ pentium4-slackware-linux then of course make; make install etc. And: mysql -uroot -p show variables: character_set | latin1 character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | Why do I still get all these character sets and how to get rid of them ? If you want to have only latin1 configure MySQL with --with-charset=latin1 option, additional character sets you can define with --with-extra-charsets option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 2013 while using load data
Bungarz, Kai [EMAIL PROTECTED] wrote: Hi! I get error 2013: Lost connection to mysql server during query, while loading data into a table using load data infile... This happens only, when load data needs much time and files are rather big. Is there any time_out parameter i have to set/change? Yes, there are wait_timeout and interactive_timeout variables (default value is 28800 seconds). Check also value of max_allowed_packet variable: http://www.mysql.com/doc/en/Gone_away.html http://www.mysql.com/doc/en/SHOW_VARIABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default UTF-8 Encoding
On Tuesday 17 February 2004 18:16, you wrote: Do you know what the default character set for MySQL is? By default MySQL uses latin1 (ISO-8859-1) character set. Thank you! -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Saturday, February 14, 2004 4:18 AM To: [EMAIL PROTECTED] Subject: Re: Default UTF-8 Encoding David Perron [EMAIL PROTECTED] wrote: Is there a way to change the default mysql encoding to be something else, say UTF-16LE at the session level? MySQL doesn't support UTF-16LE. If you want to set up connection character set you can: - execute SET CHARACTER SET from the client - run mysql client with --default-character-set option (or put this option to the my.cnf file) http://www.mysql.com/doc/en/Charset-connection.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: oracledump.pl error
Hi Maybe I am missing something. Why not install MySQL on a windows box and use one of those porters. Then just copy your tables to the Linux box. Mike - Original Message - From: J. Allen Crider [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: February 19, 2004 3:37 AM Subject: Re: oracledump.pl error Obviously I wrote my original message too quickly and did not make myself clear. I am well aware that DB2 and numerous other commercial products work on Linux. What I meant to say was that, of all of the products that I could find listed on www.mysql.com for converting data from Oracle to MySQL, all of the commercial products except SQLPorter are written specifically for MS Windows and do not have Linux versions. That leaves the most obvious options for converting the data in my Oracle database to MySQL being oracledump.pl or rolling my own solution. If there are other tools that will do the job easily, either open source or low-cost commercial, I would like to hear about them. Rhino wrote: I don't have any insight into your problem but I want to take exception with your assertion that no other commercial products work on Linux. In fact, DB2 works on Linux and has for some time. Here's a link to information about this product: http://www-306.ibm.com/software/data/db2/udb/ If you nose around the IBM site a bit, you should be able to find a free full-function copy of DB2 for Linux. (By free, I mean you don't need a licence for it unless you use it in production. This enables you to get very acquainted with DB2 for free and only buy a licence if you like it enough to use it in production.) Rhino - Original Message - From: J. Allen Crider [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 10:54 PM Subject: oracledump.pl error I have just decided to try to learn something about MySQL after several years of working with Oracle and wanted to transfer the data I have in an Oracle 9i database to a new MySQL database. Since this is strictly for personal use, I can't justify the cost of SQLPorter, and none of the other commercial products work on Linux, so I decided to try oracledump.pl. However, I am getting the following error when I attempt to run it: DBD::Oracle::st execute failed: ORA-01459: invalid length for variable character string (DBD ERROR: OCIStmtExecute) [for statement ``SELECT a.COLUMN_ID, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.DATA_DEFAULT, b.COMMENTS FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME'' with params: :p1='ALBUMS']) at ./oracledump.pl line 471. (followed by several other errors obviously caused by this error.) Running the same statement in sqlplus works fine. I am running Oracle 9i Release 2 on Gentoo Linux. Any ideas on what I'm doing wrong? Allen Crider Huntsville, AL [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Log mysql connections
Hello, I'm am trying to make MySQL Server to log connections (attempts, successes, failures) to a Syslog. I know that it's possible to log all queries but I just want the connections. Has anyone already done this? Is there a patch file that I can use? Thank you, António Fernandes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subselect in aggregate function in MySQL 4.1.1a-alpha
Hi, I have some logging data from a webserver in a table and want to do some analysis. I infact want to see how many files are requested at one time. To do this I SELECT COUNT(time) anz FROM table GROUP BY time ORDER BY anz DESC This gives me the number of requests at any time. I now want to see the average number of requests per time. I thus thought I can use a subselect like SELECT AVG(SELECT COUNT(time) anz FROM table GROUP BY time) FROM table That seems impossible, it in fact simply does not work. Is it wrong by my design or is it MySQL design not to pass a subselect to an aggregate function? Best wishes, Bernhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unknown
reply -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.1 Performance
I have begun working with MySQL 4.1.1 using the utf8 encoding to capture unicode data. I converted an existing MySQL 4.0 (latin1) database by doing a dump and load (with the new databases character set to utf8). I'm noticing that the performance on the new 4.1 database is about 5 times slower. Is this to be expected? If so, is there a plan to address this issue? If you are currently not aware of this issue I am willing to try and put together a sample populated database and query that illustrates the problem. Please let me know if you would like me to do this. -Brian Brian Wintz Deployment Architect 6450 Via Real [EMAIL PROTECTED] (Embedded Carpinteria CA 93101, USATel: (805) 566-5235 image moved http://www.qad.com/ to file: pic29492.gif) A Passion for Manufacturing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyODBC Help
I'm copying this to the list (hope you don't mind). You'll find you get quicker and better responses when all the experts on the list see your question. Plus, someone else may have a similar question and benefit from the answers you get. I've never seen this problem myself, but it looks like a configure error. I'd bet that if you look in your Makefile, you'll see a -L/yes/lib instead of -L/path/to/lib for some library. Perhaps you told configure --with-some-lib rather than --with-some-lib=/path/to/lib? I'd suggest you `make distclean` then try configure/make again. Assuming you get the same error, you could post your configure command and see if anyone can spot what you need to change. Michael Morgan, Andrew R. wrote: Mr. Stassen, Not trying to take advantage of your help this morning, but I wanted to know if you knew the answer to this problem now too. I'm trying to install MyODBC and now I get this error upon 'make': ../libtool[1296]: yes/lib: not found libtool: link: cannot determine absolute directory name of `yes/lib' make[2]: *** [libmyodbc3.la] Error 1 make[2]: Leaving directory `/virtual/MyODBC-3.51.06/driver' make[1]: *** [all] Error 2 make[1]: Leaving directory `/virtual/MyODBC-3.51.06/driver' make: *** [all-recursive] Error 1 Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Problems
Hello, I am trying to create some tables that I can use the delete on cascade function for. This would help me code the project and ensure data integrity. I am on the docs @ http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html but I am not quite understanding the syntax. I am creating two test tables to work out the logic. What I want to do is delete a certain 'ID' from 'table 'ONE' and have it delete the same foreign key id of the same value in table 'TWO'. Here is what I have entered into the mysql command. mysql create table one (id varchar(2) not null, name varchar(20), primary key ( id)) type=INNODB; That is table one, with a primary key on ID; Now the foreign key table; mysql create table two (fid varchar(2), fname varchar(20), fOREIGN KEY (fid) RE FERENCES one(id) on delete cascade); Insert data: one: idname 1'scott' 2'benjamin' two: fidname 1 'twoscott' 2 'twobenjamin' Problem is when I delete from one where id = '1' it does not delete the foreign key one in table two. I hope I explained this well. thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLDriverCS-n-EasyQueryTools-3.0.11
I am desperately looking for some programming examples for building custom gui's. And can't find any online using MySQLDriverCS. I have one built but I need to find better examples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is MySQL optimised for - BETWEEN or AND?
Hi, I have a select query which is selecting all records in a table (which has around 8,000,000 rows in). time is a field of type time. Should I be using = and = or BETWEEN to find records in a certain range? Which does MySQL optimise for? select time,price from csq where id = 12345 and date = now() and time = '10:00' and time = '11:00'; select time,price from csq where id = 12345 and date = now() and time between '10:00' and '11:00'; Kind Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication issues during Failover
On Wed, Feb 18, 2004 at 02:18:59PM -0800, Gowtham Jayaram wrote:
Hello all:
I am looking into the Replication issues that surface in a Failover
scenario (Master goes down and Slave is pressed into service).
I understand that it is critical for the Master and Slave databases
to mirror each other for Replication to work.
mirror each other implies a dual-master scenario. Is that what
you're planning? If so, are you aware of the problems in doing so,
such as the lack of conflict resolution?
I am trying to ensure that this integrity is maintained when the
Failover scenario occurs.
Reading the MySQL FAQ, I see the following recommendation.
1. On the Master, execute these commands:
mysql FLUSH TABLES WITH READ LOCK;
mysql SHOW MASTER STATUS;
Record the log name and the offset from the output
of the SHOW MASTER STATUS statement.
2. On the Slave, issue this command, where the
replication coordinates that are the arguments to the
MASTER_POS_WAIT() function are the values recorded in
the previous step:
mysql SELECT MASTER_POS_WAIT('log_name',
log_offset', [timeout]);
The SELECT statement will block until the Slave
reaches the specified log file and offset. At that
point, the Slave will be in sync with the Master and
the statement will return.
3. On the Master, issue the following statement to
allow the master to begin processing updates again:
mysql UNLOCK TABLES;
4. Also, I plan to add an extra (maybe redundant)
check to make sure that the SQL Thread is in 'Has read
all relay log' state.
QUESTIONS:
Q1. Will the above steps be __sufficient__ to synchronize the
databases?
Well, you really haven't said much about how the failover and recovery
are going to work. What you've described above is a partial method of
syncing the two servers. But it's not clean when and how you inted to
use it.
Q2. Are there any gotchas that I need to watch out for?
Yes. ;-)
I would also like to hear any recommendations of how to handle the
scenario in which the Master database crashes. Since I cannot
connect to the Master database, I cannot perform above Steps 1, 2
and 3.
Correct. If the master is dead, the slave has the data is has and
there's not much else you can do about it until the master comes back
up (assuming it does at all).
Q3. Will Step 4 work in this scenario (since it is file based)? If
it does work is it sufficient to ensure database mirroring?
Maybe.
Q4. If Step 4 does not work what is the recommendation for this
scenario.
Can you be more clear on the setup you have in mind? Does pressed
into service mean you want to slave to take updates too?
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 158 days, processed 1,941,264,213 queries (141/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication issues during Failover
I left out the details because of the fear of putting
people to sleep. Here it goes
The requirement is to design High Availability for an
Application that is using a MySQL database. The
following is the configuration I have in mind.
CONFIGURATION:
- Two machines, Primary and Secondary. Each machine
has the __capability__ to run an instance of our
Application and an instance of MySQL Server.
- Bidirectional replication is setup between the
Primary and Secondary, i.e.; MySQL on both machines
are running with '--log-bin' and without
'--log-slave-updates'.
- A Heartbeat Manager runs on both boxes providing
the status of the Application (ACTIVE, STAND-BY or
DOWN).
- Also, the Application will be designed to ensure
that a database access or update is possible only if
the Application is ACTIVE.
INITIAL STATE:
- The Heartbeat Managers are running on both the
machines.
- To start off, Primary is acting as the Master.
The Application and the MySQL process on the Primary
are ACTIVE, performing database operations at any
given time.
- Secondary is the Slave. The Application and the
MySQL process on the Secondary are in STAND-BY mode,
i.e., no data access or update.
- All the data written to the Primary database is
being replicated onto the Secondary database via MySQL
Replication.
FAILOVER SCENARIO:
- Primary becomes unavailable. Primary is DOWN.
- The Heartbeat Mgr on the Secondary becomes aware
and makes the Secondary ACTIVE. This means
- The secondary will start taking requests and
making updates to the database.
- In short the secondary is the Master now.
Later
- Primary is brought back up again. It will be in
the STAND-BY state.
- Since bidirectional replication is setup, the
updates on the Secondary get replicated onto the
Primary.
Now to the questions;
mirror each other implies a dual-master scenario.
Is that what
you're planning? If so, are you aware of the
problems in doing so,
such as the lack of conflict resolution?
Yes. The Slave is expected to take over when the
Master goes down.
No, I am not aware of 'lack of conflict resolution'.
I am waiting for the 'High Performance MySQL' book to
explain this to me :-). Would be great if you explain
it or point me to some link.
Q2. Are there any gotchas that I need to watch out
for?
Yes. ;-)
Hopefully the above details will help you provide me
more information.
Also, on a more general tone:
- How do people using MySQL as the database provide
High availability solutions?
- What is the alternative to Replication?
- Is the 'shared storage device' solution preferred in
which you have the Primary and Secondary pointing to
the same storage area; when machine fails, the other
machine is brought online against the same data.
All feedback is most welcome.
Gowtham.
--- Jeremy Zawodny [EMAIL PROTECTED] wrote:
On Wed, Feb 18, 2004 at 02:18:59PM -0800, Gowtham
Jayaram wrote:
Hello all:
I am looking into the Replication issues that
surface in a Failover
scenario (Master goes down and Slave is pressed
into service).
I understand that it is critical for the Master
and Slave databases
to mirror each other for Replication to work.
mirror each other implies a dual-master scenario.
Is that what
you're planning? If so, are you aware of the
problems in doing so,
such as the lack of conflict resolution?
I am trying to ensure that this integrity is
maintained when the
Failover scenario occurs.
Reading the MySQL FAQ, I see the following
recommendation.
1. On the Master, execute these commands:
mysql FLUSH TABLES WITH READ LOCK;
mysql SHOW MASTER STATUS;
Record the log name and the offset from the
output
of the SHOW MASTER STATUS statement.
2. On the Slave, issue this command, where the
replication coordinates that are the arguments to
the
MASTER_POS_WAIT() function are the values recorded
in
the previous step:
mysql SELECT MASTER_POS_WAIT('log_name',
log_offset', [timeout]);
The SELECT statement will block until the Slave
reaches the specified log file and offset. At that
point, the Slave will be in sync with the Master
and
the statement will return.
3. On the Master, issue the following statement to
allow the master to begin processing updates
again:
mysql UNLOCK TABLES;
4. Also, I plan to add an extra (maybe redundant)
check to make sure that the SQL Thread is in 'Has
read
all relay log' state.
QUESTIONS:
Q1. Will the above steps be __sufficient__ to
synchronize the
databases?
Well, you really haven't said much about how the
failover and recovery
are going to work. What you've described above is a
partial method of
syncing the two servers. But it's not clean when
and how you inted to
use it.
Q2. Are there any gotchas that I need to watch out
for?
Yes. ;-)
I would also like to hear any recommendations of
how to handle the
scenario in
RE: SQL-help needed
I'm still a beginner myself but try something like SELECT COUNT(YEAR), WINNER1 AS WINNER, WINNER2 AS WINNER, YEAR FROM CHAMPIONS WHERE CLASS = hd GROUP BY WINNER; I think this will give you something like: COUNT(YEAR) WINNER YEAR 2 carl1957 2 carl1985 1 mattias 1957 1 erik1985 Again I am a beginner and would have to test this to see if it actually gives me the right info or if I would have to tweek it. Respectfully, Ligaya Turmelle Computer Programmer Guam International Country Club 495 Battulo Street Dededo, Guam 96912 Tel: (671) 632-4445 Fax: (671) 632-4440 Reservations: (671) 632-4422 (GICC) -Original Message- From: Carl Schéle, IT, Posten [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 6:50 PM To: [EMAIL PROTECTED] Subject: SQL-help needed Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query help
This is probably tediously basic for all you super whiz MySQL people but help me out if you can. I have 2 tables in my database (there will be more) table_Applics table_keywords I want to select columns of information from table_applics based on the ID results from table_keywords. something like this I guess, Select ID From Keywords Where markets = 'Financial' This then gives me a list of ID's which I then want to take to table_applics and get the row of information for each ID number in the list that exist Select ID,NAME,LNAME,ADDRESS1 from table_applics Whats the best way to achieve this in a single query ? can any one help me with the Logic !!! Here from you soon I hope, Best regards Andy Fletcher
Re: SQL-help needed
Carl Schéle, IT, Posten wrote: Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. The following is close to what you want: CREATE TEMPORARY TABLE champs (winner CHAR(10), year INT); # change the column types to match table champions INSERT INTO champs SELECT winner_1, year FROM champions WHERE class='hd'; INSERT INTO champs SELECT winner_2, year FROM champions WHERE class='hd' AND winner_2 IS NOT NULL; # some of your winner_2 spots are empty. If they're empty strings # instead of NULL, change IS NOT NULL to != '' SELECT * FROM champs ORDER by winner, year; +-+--+ | winner | year | +-+--+ | carl| 1957 | | carl| 1985 | | erik| 1985 | | mattias | 1957 | +-+--+ 4 rows in set (0.01 sec) SELECT count(*), winner FROM champs GROUP BY winner; +--+-+ | count(*) | winner | +--+-+ |2 | carl| |1 | erik| |1 | mattias | +--+-+ 3 rows in set (0.01 sec) === It seems to me that your table design is what makes this difficult. If you changed it to something like the following, where wintype denotes winner_1 or winner_2, this would be easier: id class winner wintype year - 1 hd carl 1 1957 2 hs daniel 1 1982 3 hd erik 1 1985 4 js erik 1 1974 5 hd mattias2 1957 6 hd carl 2 1985 You could then go straight to the select: SELECT * FROM champions WHERE class='hd' ORDER by winner, year; or SELECT count(*), winner FROM champions WHERE class='hd' GROUP BY winner; You could use a variant of the INSERT-SELECTs above to fill the new table, if you decide to go that way. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems connecting to MySql on WebSphere 5.1
I've been running an application on WebSphere 5.0 (Linux) that uses the MySql Connector/J 3.10 to connect to a MySql-max 4.0.15 database (also on Linux). All was working fine, until I recently installed WebSphere 5.1. Now my datasources (configured exactly as before) no longer work - WebSphere seems unable to find them. When I start the server, I get the following error when my application starts: [2/19/04 16:04:40:481 EST] 3c6d95c5 DSConfigurati W DSRA0174W: Warning: GenericDataStoreHelper is being used. [2/19/04 16:04:40:891 EST] 3c6d95c5 ConnectionFac I J2CA0122I: Resource reference jdbc/staging could not be located, so default values of the following are used: [Resource-ref settings] res-auth: 1 (APPLICATION) res-isolation-level: 0 (TRANSACTION_NONE) res-sharing-scope:true (SHAREABLE) res-resolution-control: 999 (undefined) [Other attributes] isCMP1_x: false (not CMP1.x) isJMS: false (not JMS) Has anyone else had problems with WAS 5.1 access MySql datasources? Tom [EMAIL PROTECTED] = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last inserted id
At 9:33 + 2/19/04, Mark Maunder wrote: 'connection ID' below should be 'last insert id'. Sorry, it's 2am here and I'm fresh out of coffee. BTW the information you want is here: http://www.mysql.com/doc/en/Information_functions.html#IDX1409 The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. That page pertains to LAST_INSERT_ID(), which is a server side function and is not the same as the mysql_insert_id() client-side function. Their behaviors are not exactly the same, though for purposes of the original question, they'll both do the job: Return the AUTO_INCREMENT value generated by the current connection, without being interfered with by activity on other connections that also generate AUTO_INCREMENT values. On Thu, 2004-02-19 at 09:26, Mark Maunder wrote: The two simultaneous insert statements will be have separate connections to the database and last_insert_id() is connection specific. So if you're running apache, and you're worried about two different apache child processes getting the same connection ID, don't. Because those two children will have separate connections to the DB. If you're forking or threading and using the same connection, it becomes a bit more complex. Mark. On Thu, 2004-02-19 at 09:17, Binay wrote: Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prefixing Column Names in Select
On Wednesday 18 February 2004 09:24 pm, Ligaya Turmelle wrote: You can set it as an alias using AS . Respectfully, Ligaya Turmelle Yes, I was aware of this option, but I was looking for a way to automatically alias all fields with a set prefix or postfix. On a big table it's a lot of typing to alias all fields. I'm lazy! Thanks. James Hicks sql mysql row query table column -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prefixing Column Names in Select
On Wednesday 18 February 2004 09:24 pm, Ligaya Turmelle wrote: You can set it as an alias using AS . Respectfully, Ligaya Turmelle Yes, I was aware of this option, but I was looking for a way to automatically alias all fields with a set prefix or postfix. On a big table it's a lot of typing to alias all fields. I'm lazy! Thanks. James Hicks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird Sorting Question.
Hi,
I've got a sorting question that I can't figure out. The page in
question is here:
http://toolstudios.com/intera/careers/index.php
I'd like it to show up like this:
Accounting
job 1
job 2
Engineer
job 1
job 2
IT
job 1
job 2
Basically, I'd like the categories of employment to be alphabetical.
Here's the SQL stuff now:
?php
include(common.inc);
$connection = @mysql_connect($db_host,$db_user,$db_pass) or
die(Couldn't Connect.);
$db = @mysql_select_db($db_name, $connection) or die(Couldn't select
database.);
$sql =SELECT
id,status,title,summary,description,post_date,hidden,job_id,contact_emai
l
FROM careers
WHERE hidden = '2'
ORDER BY job_id,title ;
$result = @mysql_query($sql, $connection) or die(Error #.
mysql_errno() . : . mysql_error());
while ($row = mysql_fetch_array($result)) {
$id=$row['id'];
$status=$row['status'];
$title=$row['title'];
$summary=$row['summary'];
$description=$row['description'];
$post_date=$row['post_date'];
$hidden=$row['hidden'];
$job_id=$row['job_id'];
$contact_email=$row['contact_email'];
// lookup sub_cat name and display, only if it's new
if ($job_id != $old_job_cat){
// New Connection and selection from Sub Category db
$connection = @mysql_connect($db_host,$db_user,$db_pass) or
die(Couldn't Connect.);
$db = @mysql_select_db($db_name, $connection) or die(Couldn't
select database.);
$cat_sql =SELECT job_id, category FROM career_categories WHERE
job_id = '$job_id' ORDER BY category ASC;
$cat_result = @mysql_query($cat_sql, $connection) or die(Error #.
mysql_errno() . : . mysql_error());
while ($cat_row = mysql_fetch_array($cat_result)) {
$job_id=$cat_row['job_id'];
$category=$cat_row['category'];
// We then print out the RESULT row
$job_heading = br\n FONT
SIZE=\4\B$category/B/FONT\n;
$display_block .= $job_heading br\n;
}
}
// This ends the Sub Cat Row, and continues the Job Listings
Row
$old_job_cat = $job_id;
$display_block .= a
href=\detail_action.php?id=$id\$title/a - $summarybr\n;
}
$num = @mysql_num_rows($result);
if ($num != 0) {
include(list_results.php);
} else {
include(list_no_rslt.php);
}
?
The problem is, when the script looks for job_ID and attempts to see
if it matches anything, it doesn't return an array of results...just 1.
So, can't really alphabetize one result. Any workarounds?
Thanks,
mto
--
Michael O'Neal
Head Honcho
http://www.WhirledMedia.com
[EMAIL PROTECTED]
Boulder, Colorado
720.436.2922
IM: AOL-emptyo1
MSN: [EMAIL PROTECTED]
Yahoo: bambisapartment
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does Dropping a table affect it's indexes?
Quick question... What you drop a table are the indexes for that table dropped to? I'm about to write a script to take a data pull every night and re-populate a table with the results, then have my apps run off of the new consolidated table for a speed increase. If I drop the Consolidated table, then re-create it with the new data pull, will I need to re-create the indexes as well? Is there any performance cost/benefit to simply deleting all data from the table and then re-populating it as opposed to droping and re-creating it? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting values from two different tables:
Hi all, I'm basically trying to get a count of banner ad impresssions and banner ad click-thrus from phpAdsNew into a simple report on another site to figure out how to accurately get the stats to pass onto php and smarty to output onto a webpage for a user to see their stats on an application I'm working on: mysql SELECT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(DISTINCT phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +--+--+- +--+ | md5_url | md5_status | adviews | adclicks | +--+--+- +--+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 615 | 616 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1147 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 691 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 607 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1192 | 1212 | +--+--+- +--+ 5 rows in set (0.56 sec) mysql SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(DISTINCT phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +--+--+- +--+ | md5_url | md5_status | adviews | adclicks | +--+--+- +--+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 615 | 616 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1147 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 691 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 607 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1192 | 1212 | +--+--+- +--+ 5 rows in set (0.53 sec) mysql SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid =phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +--+--+- +--+ | md5_url | md5_status | adviews | adclicks | +--+--+- +--+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 616 |0 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1165 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 694 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 |0 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1212 |0 | +--+--+- +--+ 5 rows in set (0.17 sec) This
Error installing MyODBC - Please help...
Hello all. I'm trying to 'make' MyODBC, and I receive the following error: gcc -DHAVE_CONFIG_H -I. -I. -I. -I/usr/local/mysql/include/mysql -Iyes/include -O3 -DDBUG_OFF -c utility.c -Wp,-MD,.deps/utility.TPlo -fPIC -DPIC -o utility.lo /bin/ksh ../libtool --mode=link gcc -O3 -DDBUG_OFF -o libmyodbc3.la -rpath /usr/local/lib -release 3.51.06 catalog.lo connect.lo cursor.lo dll.lo error.lo execute.lo handle.lo info.lo misc.lo myodbc3.lo options.lo prepare.lo results.lo transact.lo utility.lo -L/usr/local/mysql/lib/mysql -lmysqlclient -Lyes/lib -L/usr/local/mysql/lib/mysql -lm -lnsl -lsocket -lodbcinst ../libtool[1296]: yes/lib: not found libtool: link: cannot determine absolute directory name of `yes/lib' make[2]: *** [libmyodbc3.la] Error 1 make[2]: Leaving directory `/virtual/MyODBC-3.51.06/driver' make[1]: *** [all] Error 2 make[1]: Leaving directory `/virtual/MyODBC-3.51.06/driver' make: *** [all-recursive] Error 1 does anyone know what this means or what I need to do to resolve this issue? Thanks
Re: BCE/CE Dates
... I have a, possibly not so unusual problem, with dates. The standard MySQL datatime accounts for all dates starting between the year and inclusive. ... Take a look at using Julian Day numbers. This date form is used in astronomy and general calendar conversions. Every day has an integer number and it handles pretty much any date you can imagine (as in the lifetime of the universe). Just convert your dates to JDNs for storage and convert back for display. Lots of code out there to do this. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.0.11 STABLE Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.0.11, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. Version 3.0.11 is a bugfix release for the STABLE tree that is suitable for use with any MySQL version including MySQL-4.1 or MySQL-5.0. It is now available in source and binary form from the Connector/J download pages at http://www.mysql.com/downloads/api-jdbc-stable.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) You might also be interested in learning about new features in Connector/J as well as hearing sessions from other Java and MySQL-related developers in person at the MySQL Users' Conference, which will be held in Orlando, April 14th-16th, 2004 (see http://www.mysql.com/events/uc2004/ for more information). Have fun with this release, and looking forward to meeting you in Orlando if you can make it! -Mark - From the changelog: - Trigger a 'SET NAMES utf8' when encoding is forced to 'utf8' _or_ 'utf-8' via the 'characterEncoding' property. Previously, only the Java-style encoding name of 'utf-8' would trigger this. - AutoReconnect time was growing faster than exponentially (BUG#2447). - Fixed failover always going to last host in list (BUG#2578) - Added 'useUnbufferedInput' parameter, and now use it by default (due to JVM issue http://developer.java.sun.com/developer/bugParade/bugs/4401235.html) - Detect 'on/off' or '1','2','3' form of lower_case_table_names on server. - Return 'java.lang.Integer' for TINYINT and SMALLINT types from ResultSetMetaData.getColumnClassName() (fix for BUG#2852). - Return 'java.lang.Double' for FLOAT type from ResultSetMetaData.getColumnClassName() (fix for BUG#2855). - Return '[B' instead of java.lang.Object for BINARY, VARBINARY and LONGVARBINARY types from ResultSetMetaData.getColumnClassName() (JDBC compliance). - Issue connection events on all instances created from a ConnectionPoolDataSource. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFANS+atvXNTca6JD8RAoQMAJ4lBucpaVDCXldcfC09FGxRkIRfrACgwRXC ydgSTN55m6KwHfOChNxgIBw= =g1MU -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump via tcp/ip memory problem
I've dumped alot of databases before using mysqldump, and am trying to dump a larger database than normal, about 2.2GB in size.. The largest table just over 12 million rows... It's dumping over a network to a tape backup server.. I start the job off: /usr/local/bin/mysqldump -c -F --host=prv-master1 \ --password=blahblah --port=3306 --user=blahblah --verbose mdb1 /tapesource/MDB1/mdb1.db It runs for bit, dumping some smaller tables, then gets the the largest table (12mil row) .. runs for a bit and reports Killed Dmesg shows: __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process mysqldump Which leads to a memory problem, or lack of... The box does have approx. 500MB of free ram... Is it just eating it up buffering the network response from the server? Mysqldump on client is Ver 8.22 Distrib 3.23.57 Mysqld on server is 3.23.55-log Thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf on MySQL-Server-4.0.17
Hi All, I´m trying to do some customization in a MySQL-Server-4.0.17 to gain a better performance. We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD on a FreeBSD-5.2.1 box dedicated for MySQL. My trouble is when I create the my.cnf file and start MySQL. MySQL don´t stat. the /usr/local/mysql/aguia.err file shows: - 040219 16:34:46 mysqld started mysqld in malloc(): error: allocation failed mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=1044480 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 335471 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. mysqld in free(): error: recursive call Fatal signal 6 while backtracing 040219 16:34:46 mysqld ended - My my.cnf file is: - [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id = 1 innodb_data_home_dir = /usr/local/mysql/ innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_group_home_dir = /usr/local/mysql/ innodb_log_arch_dir = /usr/local/mysql/ innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout - Without the /etc/my.cnf file the MySQL works fine, except in some moments that it get slow. Does anyone knows what could be wrong? Any help would be Appreciated. Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query help
This is probably tediously basic for all you super whiz MySQL people but help me out if you can. I have 2 tables in my database (there will be more) table_Applics table_keywords I want to select columns of information from table_applics based on the ID results from table_keywords. something like this I guess, Select ID From Keywords Where markets = 'Financial' This then gives me a list of ID's which I then want to take to table_applics and get the row of information for each ID number in the list that exist Select ID,NAME,LNAME,ADDRESS1 from table_applics Whats the best way to achieve this in a single query ? can any one help me with the Logic !!! Here from you soon I hope, Best regards Andy Fletcher -- You can try that: I do not know if it is what you are looking for: SELECT - FROM TABLE1 INNER JOIN TABLE2 USING (common_column) GROUP BY -- ORDER BY ; Another way: SELECT - FROM TABLE1 INNER JOIN TABLE2 ON table1.field=table2.field (field as common_column) GROUP BY -- ORDER BY ; --- I hope that it works. Marcelo Araujo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reporting Engines for MySQL
I'm looking for a good report generator (similar to Crystal Reports) for MySQL that runs on a Linux/Apache system. I'm currently using a php scripts that I wrote, but its being unbearable to keep up with the report demand. So i'm looking for something I could easily integrate into my current web app. I took a look at the MySQL portal software area on their site (http://www.mysql.com/portal/software/reporting/index.html), but could not find anything just for reporting. It seem most of the utils where for administrating. I was wondering if anybody from the community could recommend anything? Thanks Matt -- Matt Silva Empower Software Technologies, LLC 27851 Bradley Rd. Suite 120 Sun City, CA 92586 PH: (909) 672-6257 WB: www.storagecommander.com EM: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication: Setting up a slave with LOAD DATA FROM MASTER
I run MySQL version 4.0.16 on my linux box. My replication is working fine when I drop my database and import it on the master server. It seems to me that configuration files for both master and slave are OK. Privillege too. I define a user with the following right's: * Reload * File * Super * Execute * Replication client * Replication slave Now, if want to set up a new slave following these steps: * Start mysql on the slave * Run the following command to set up my slave LOAD DATA FROM MASTER; Nothing happens ?. What is going wrong ? Thanks in advance. Pierre LUGUERN IBM Business Consulting Services Tour Descartes 2 avenue Gambetta - La Défense 5 92066 Paris La Défense Ext: 01 49 05 55 63 Int: (33) 5563 Fax: 01 49 05 81 18 mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is MySQL optimised for - BETWEEN or AND?
On Thu, 2004-02-19 at 08:24, Alex Greg wrote: I have a select query which is selecting all records in a table (which has around 8,000,000 rows in). time is a field of type time. Should I be using = and = or BETWEEN to find records in a certain range? Which does MySQL optimise for? I can not speak to specifics in the code, but I have 150 million row InnoDB table that has an int field that stores a unix_timestamp() and I've gotten slightly better performance using BETWEEN rather than AND, but this is largely anecdotal, as I don't have hard numbers. Your best bet would be to test performance yourself. Be sure you are using an index on that column in your query (verify that with explain) -- using an index will make the most difference, of course. -- Andy Bakun: get used to it [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf in MySQL-Server-4.0.17
Hi All, I´m trying to do some customization in a MySQL-Server-4.0.17 to gain a better performance. We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD on a FreeBSD-5.2.1 box dedicated for MySQL. My trouble is when I create the my.cnf file and start MySQL. MySQL don´t stat. the /usr/local/mysql/aguia.err file shows: - 040219 16:34:46 mysqld started mysqld in malloc(): error: allocation failed mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=1044480 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 335471 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. mysqld in free(): error: recursive call Fatal signal 6 while backtracing 040219 16:34:46 mysqld ended - My my.cnf file is: - [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id = 1 innodb_data_home_dir = /usr/local/mysql/ innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_group_home_dir = /usr/local/mysql/ innodb_log_arch_dir = /usr/local/mysql/ innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout - Without the /etc/my.cnf file the MySQL works fine, except in some moments that it get slow. Does anyone knows what could be wrong? Any help would be Appreciated. Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.1 Performance
Hi! On Feb 18, Brian Wintz wrote: I have begun working with MySQL 4.1.1 using the utf8 encoding to capture unicode data. I converted an existing MySQL 4.0 (latin1) database by doing a dump and load (with the new databases character set to utf8). I'm noticing that the performance on the new 4.1 database is about 5 times slower. Is this to be expected? If so, is there a plan to address this issue? No, I never heard about this problem. If you are currently not aware of this issue I am willing to try and put together a sample populated database and query that illustrates the problem. Please let me know if you would like me to do this. Yes, please do! Then, please, submit a bugreport at http://bugs.mysql.com/ Thank you for your help! Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect in aggregate function in MySQL 4.1.1a-alpha
[EMAIL PROTECTED] wrote: Hi, I have some logging data from a webserver in a table and want to do some analysis. I infact want to see how many files are requested at one time. To do this I SELECT COUNT(time) anz FROM table GROUP BY time ORDER BY anz DESC This gives me the number of requests at any time. I now want to see the average number of requests per time. I thus thought I can use a subselect like SELECT AVG(SELECT COUNT(time) anz FROM table GROUP BY time) FROM table That seems impossible, it in fact simply does not work. Is it wrong by my design or is it MySQL design not to pass a subselect to an aggregate function? If I've got you right you need: SELECT AVG(anz) FROM (SELECT COUNT(time) anz FROM table GROUP BY time) as table1; -- 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]
Newbie question
I need a little advice on where to get started. I want to create a db and simple form that will populate the db. Which language is best? What should I read to help me along? tia Walt
RE: MySQL 4.1.1 Performance
Brian, I know that I have been using 4.1.0 and 4.1.1 and when compared to 4.0.x, 4.1.x is much faster for us. We have been pushing over 3000 queries per second with 4.1.1 without any problems. Now we aren't using utf8, but I don't think that would really be the problem. Donny -Original Message- From: Brian Wintz [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 9:39 PM To: [EMAIL PROTECTED] Subject: MySQL 4.1.1 Performance I have begun working with MySQL 4.1.1 using the utf8 encoding to capture unicode data. I converted an existing MySQL 4.0 (latin1) database by doing a dump and load (with the new databases character set to utf8). I'm noticing that the performance on the new 4.1 database is about 5 times slower. Is this to be expected? If so, is there a plan to address this issue? If you are currently not aware of this issue I am willing to try and put together a sample populated database and query that illustrates the problem. Please let me know if you would like me to do this. -Brian Brian Wintz Deployment Architect 6450 Via Real [EMAIL PROTECTED] (Embedded Carpinteria CA 93101, USATel: (805) 566-5235 image moved http://www.qad.com/ to file: pic29492.gif) A Passion for Manufacturing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.23 JOIN describe to another query
Is it possible to JOIN the results of a DESCRIBE TABLE to some other query? The semantics of what I would like to do is the following: SELECT d_fldcomment from desc LEFT JOIN DESCRIBE footable ON desc.d_table = 'footable' AND desc.d_field = Field; so the resultset would look like a regular DESCRIBE table except it would also include my tacked on comment. I KNOW the need for this will be obsoleted in mySQL 5 but people will be stuck on prev versions for the next 2 years. Any clever way to do this or fake it so everything is in one resultset? I already did it in the obvious boneheaded way and as always when that happens one things THERE HAS TO BE A BETTER WAY still on 3.23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems connecting to MySql on WebSphere 5.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom O'Neil wrote: I've been running an application on WebSphere 5.0 (Linux) that uses the MySql Connector/J 3.10 to connect to a MySql-max 4.0.15 database (also on Linux). All was working fine, until I recently installed WebSphere 5.1. Now my datasources (configured exactly as before) no longer work - WebSphere seems unable to find them. When I start the server, I get the following error when my application starts: [2/19/04 16:04:40:481 EST] 3c6d95c5 DSConfigurati W DSRA0174W: Warning: GenericDataStoreHelper is being used. [2/19/04 16:04:40:891 EST] 3c6d95c5 ConnectionFac I J2CA0122I: Resource reference jdbc/staging could not be located, so default values of the following are used: [Resource-ref settings] res-auth: 1 (APPLICATION) res-isolation-level: 0 (TRANSACTION_NONE) res-sharing-scope:true (SHAREABLE) res-resolution-control: 999 (undefined) [Other attributes] isCMP1_x: false (not CMP1.x) isJMS: false (not JMS) Has anyone else had problems with WAS 5.1 access MySql datasources? Tom, Make sure you're using 'com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource' as the datasource implementation class that you plug into your WebSphere config...For some reason, no other classes (Driver or plain DataSource) seem to work in 5.1sp1 or newer. FYI, I just tested this yesterday in WS 6.0 preview, and it has the same issue. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFANV/qtvXNTca6JD8RAnWcAKCD9n3If9DLMSJqOwc4ygbNpt+hsgCgmxQ4 Hd/VPa5IieQfH+Y/jnZfciM= =W/GZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is MySQL optimised for - BETWEEN or AND?
Andy Bakun wrote: On Thu, 2004-02-19 at 08:24, Alex Greg wrote: I have a select query which is selecting all records in a table (which has around 8,000,000 rows in). time is a field of type time. Should I be using = and = or BETWEEN to find records in a certain range? Which does MySQL optimise for? I can not speak to specifics in the code, but I have 150 million row InnoDB table that has an int field that stores a unix_timestamp() and I've gotten slightly better performance using BETWEEN rather than AND, but this is largely anecdotal, as I don't have hard numbers. Your best bet would be to test performance yourself. Be sure you are using an index on that column in your query (verify that with explain) -- using an index will make the most difference, of course. As BETWEEN is equivalent to the AND form anyway, I think the performance difference could be simply due to the smaller amount of parsing involved and simpler optimiser path. If you think about it, and AND statement can join predicates about different columns that could be in different tables (or even databases). The BETWEEN form says that both constraints are on the same column, and says so in a manner that's easier to deal with, for example: SELECT * FROM table WHERE col1 = 25 AND col2 38 AND col1 = 1000; Just the fact that the end-points on your col1 index are decied at two different points of the query will have some small impact on performance. I'm betting that prepared statement and stored procedure versions would have basically identical performance regardless of the form used though. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with with-extra-charsets=none
Egor Egorov wrote: Why do I still get all these character sets and how to get rid of them ? If you want to have only latin1 configure MySQL with --with-charset=latin1 option, additional character sets you can define with --with-extra-charsets option. This was the first what I did. And the result is the same: ./configure --prefix=/usr \ --with-mysqld-user=mysql \ --with-unix-socket-path=/var/run/mysql/mysql.sock \ --localstatedir=/var/lib/mysql \ --enable-assembler \ --without-debug \ --enable-thread-safe-client \ --without-bench \ --with-charset=latin1 \ --program-prefix= \ --program-suffix= \ --without-innodb \ --without-isam \ --disable-shared \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static \ pentium4-slackware-linux SHOW VARIABLES: | character_set | latin1 | character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 -- ./ premax ./ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last inserted id
This will only be a problem if the two inserts are using the same mysql connection/link_indentifier. This isn't usually the case in a web environment, unless you are using a persistent mysql connection across multiple instances of the same script. mysql_insert_id() returns the last insert id for your current mysql connection, and if each execution of the script uses a different mysql connection then what you mentioned will not be a problem. Hope this helps. Toro Binay wrote: Hi I have a php script which insert a row in one of my table. Now i want the auto_generated id produced by this insert query. I know i can use mysql_insert_id function to fetch that auto_generated id. But my question is say two or more person visiting the same page/script causes a insert operation in the table at the same time. so there are chances of getting wrong auto_generated ids for different visitors. why am i saying this can be clear from below example. Say one insert operation is in the progress and by the time control switches/call to mysql_insert_id function another insert operation starts .. so ultimately mysql_insert_id will fetch 2nd insert operation id which should not be the case. How to resolve this case?? Thanks Binay # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Dropping a table affect it's indexes?
It's my understanding that doing a simple delete delete from table_name actually DOES drop and recreate the table (and thus its indexes). On the other hand, if you are continually adding deleting records, you might well need to do a periodic 'analyze table_name' or 'optimize table_name' to maintain optimum performance clear the deleted record chain. steve At 03:21 PM 2/19/04, Jeff McKeon wrote: Quick question... What you drop a table are the indexes for that table dropped to? I'm about to write a script to take a data pull every night and re-populate a table with the results, then have my apps run off of the new consolidated table for a speed increase. If I drop the Consolidated table, then re-create it with the new data pull, will I need to re-create the indexes as well? Is there any performance cost/benefit to simply deleting all data from the table and then re-populating it as opposed to droping and re-creating it? Thanks, Jeff ++ | Steve Edberg [EMAIL PROTECTED] | | Database/Programming/SysAdmin(530)754-9127 | | University of California, Davis http://pgfsun.ucdavis.edu/ | +-- Gort, Klaatu barada nikto! --+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with with-extra-charsets=none
I think --with-extra-charsets=none means that no additional character sets will be _compiled_ into the server. Complex character sets must be compiled into MySQL, but simple ones can be loaded dynamically. I think the character sets you are seeing are ones that can be loaded dynamically. For instance the big5 character set is probably not listed because it is one that has to be compiled in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Massive memory utiliazation
Hi James, Your key_buffer is using tons of memory at 1.5 GB! table_cache is probably too big, too. Matt - Original Message - From: James Kelty Sent: Saturday, February 14, 2004 3:03 AM Subject: Massive memory utiliazation Hello, We have currently tuned MySQL for a high rate of traffic. But, now we are seeing issues with memory usage. It reaches about 2GB and the server becomed wildly unstable. Below is our my.cnf file. Can anyone point out any glarring errors? We are running this on a Dell 2650 with Red Had Advanced Server v2.1 with Kernel 2.4.9-e.25smp and Hyper threading. Thanks a lot! [client] port= 3306 # The MySQL server [mysqld] datadir = /var/lib/mysql port= 3306 skip-locking set-variable= max_connections=800 set-variable= key_buffer=1500M set-variable= max_allowed_packet=1M set-variable= table_cache=16384 set-variable= sort_buffer=256k set-variable= record_buffer=256k set-variable= record_rnd_buffer=256k set-variable= thread_cache=64 set-variable= thread_concurrency=32 set-variable= myisam_sort_buffer_size=64M set-variable= interactive_timeout=300 set-variable= open_files_limit=6 set-variable= wait_timeout=300 set-variable= long_query_time=5 set-variable= tmp_table_size=16M server-id = 0 # Adding bin log for PIT recovery log-bin #set-variable = bdb_cache_size=768M #set-variable = bdb_max_lock=10 log-slow-queries=/var/log/slowqueries.log [safe_mysqld] open-files-limit=6 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very large datasets
Greetings, I'm part of a project that deals with high-resolution topological data generated by LIDAR technology and we're considering using mySQL to store the row data (xyz triplets). Right now we have aproximatelly 40 GB of ASCII files with such data. In this format a triplet is represented by ~29 bytes and if I load them as double in the db, it will take 24 bytes, so the final size of a simple xyz table will be of the same magnitude. My questions is, is there any server fine-tuning that I can do in order to improve performances for such a large dataset? Does anyone have any positive experience dealing with this kind of databases? Thank you, Dorel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Dropping a table affect it's indexes?
Steve Edberg wrote: It's my understanding that doing a simple delete delete from table_name actually DOES drop and recreate the table (and thus its indexes). On the other hand, if you are continually adding deleting records, you might well need to do a periodic 'analyze table_name' or 'optimize table_name' to maintain optimum performance clear the deleted record chain. steve Sort of. There's a subtlty here: DELETE FROM table; Will go and delete all rows from a table. If you're using InnoDB tables, new transactions will not see this take effect until you have issued a COMMIT (unless they're set to READ_UNCOMMITED isolation level). I'm not sure if MyISAM is optimised for this special case. The command you're looking for that drops and recreates in one go is TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is the same as: DROP TABLE table; CREATE TABLE table (...); For MyISAM tables, this is likely to be much faster as MySQL can just delete the MYI and MYD files associated with the particular table. For InnoDB tables, dropping a table involves manipulating the tablespace. As a result, it doesn't currently support the TRUNCATE statement. DELETE does specifically delete rows one by one, so actually DROPing the table and reCREATE-ing it will be faster. Regards, Chris At 03:21 PM 2/19/04, Jeff McKeon wrote: Quick question... What you drop a table are the indexes for that table dropped to? I'm about to write a script to take a data pull every night and re-populate a table with the results, then have my apps run off of the new consolidated table for a speed increase. If I drop the Consolidated table, then re-create it with the new data pull, will I need to re-create the indexes as well? Is there any performance cost/benefit to simply deleting all data from the table and then re-populating it as opposed to droping and re-creating it? Thanks, Jeff ++ | Steve Edberg [EMAIL PROTECTED] | | Database/Programming/SysAdmin (530)754-9127 | | University of California, Davis http://pgfsun.ucdavis.edu/ | +-- Gort, Klaatu barada nikto! --+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump via tcp/ip memory problem
Hi, Yeah, by default mysqldump buffers the result of the SELECT * FROM table query in memory before writing the SQL statements (using mysql_store_result()). If you use the --opt option (or at least -q or --quick), it dumps the data as it gets it (using mysql_use_result()). Hope that helps. Matt - Original Message - From: [EMAIL PROTECTED] Sent: Thursday, February 19, 2004 1:23 PM Subject: mysqldump via tcp/ip memory problem I've dumped alot of databases before using mysqldump, and am trying to dump a larger database than normal, about 2.2GB in size.. The largest table just over 12 million rows... It's dumping over a network to a tape backup server.. I start the job off: /usr/local/bin/mysqldump -c -F --host=prv-master1 \ --password=blahblah --port=3306 --user=blahblah --verbose mdb1 /tapesource/MDB1/mdb1.db It runs for bit, dumping some smaller tables, then gets the the largest table (12mil row) .. runs for a bit and reports Killed Dmesg shows: __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process mysqldump Which leads to a memory problem, or lack of... The box does have approx. 500MB of free ram... Is it just eating it up buffering the network response from the server? Mysqldump on client is Ver 8.22 Distrib 3.23.57 Mysqld on server is 3.23.55-log Thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crosstab queries?
I have a query where I want to display the column headings as row headings. Here is my query: Code:SELECT DISTINCTROW Avg(DateDiff(OrderDate, POItem.ReceivedDate)) AS AvgLeadTime, Min(DateDiff(Po.OrderDate, POItem.ReceivedDate)) AS MinLeadTime, Max(DateDiff(Po.OrderDate, POItem.ReceivedDate)) AS MaxLeadTime FROM Products INNER JOIN (POItem INNER JOIN PO ON POItem.PONo = PO.PONo) ON Products.ProductID = POItem.ProductID WHERE (((DateDiff(PO.OrderDate, POItem.ReceivedDate))=1) AND (Products.VendorPart=7745k55)) GROUP BY Products.VendorPart;Any suggestions?
Re: Problems connecting to MySql on WebSphere 5.1
Mark, I am using the 'com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource' class, which worked fine in 5.0. I've done some further debugging and figured out a way to get the datasource to at least run. If I explicitly pass the user and password parameters (along with serverName, databaseName, and port), rather than use the JAAS authentication entry I created, I still get the same error as below, however the datasource does in fact work. If I try to use the JAAS entry, it actually connects to the server, which gives the following error: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) Passing the login as parameters gives me the error below, but my code can, in fact, access the datasource. [2/19/04 21:57:33:376 EST] 3c66be23 DSConfigurati W DSRA0174W: Warning: GenericDataStoreHelper is being used. [2/19/04 21:57:33:706 EST] 3c66be23 ConnectionFac I J2CA0122I: Resource reference jdbc/staging could not be located, so default values of the following are used: [Resource-ref settings] res-auth: 1 (APPLICATION) res-isolation-level: 0 (TRANSACTION_NONE) res-sharing-scope:true (SHAREABLE) res-resolution-control: 999 (undefined) [Other attributes] isCMP1_x: false (not CMP1.x) isJMS: false (not JMS) [2/19/04 21:57:34:287 EST] 3c66be23 WSRdbDataSour I DSRA8203I: Database product name : MySQL [2/19/04 21:57:34:287 EST] 3c66be23 WSRdbDataSour I DSRA8204I: Database product version : 4.0.15-max [2/19/04 21:57:34:287 EST] 3c66be23 WSRdbDataSour I DSRA8205I: JDBC driver name : MySQL-AB JDBC Driver [2/19/04 21:57:34:287 EST] 3c66be23 WSRdbDataSour I DSRA8206I: JDBC driver version : mysql-connector-java-3.0.10-stable ( $Date: 2004/01/13 21:56:18 $, $Revision: 1.27.2.33 $ ) Tom --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom O'Neil wrote: I've been running an application on WebSphere 5.0 (Linux) that uses the MySql Connector/J 3.10 to connect to a MySql-max 4.0.15 database (also on Linux). All was working fine, until I recently installed WebSphere 5.1. Now my datasources (configured exactly as before) no longer work - WebSphere seems unable to find them. When I start the server, I get the following error when my application starts: [2/19/04 16:04:40:481 EST] 3c6d95c5 DSConfigurati W DSRA0174W: Warning: GenericDataStoreHelper is being used. [2/19/04 16:04:40:891 EST] 3c6d95c5 ConnectionFac I J2CA0122I: Resource reference jdbc/staging could not be located, so default values of the following are used: [Resource-ref settings] res-auth: 1 (APPLICATION) res-isolation-level: 0 (TRANSACTION_NONE) res-sharing-scope:true (SHAREABLE) res-resolution-control: 999 (undefined) [Other attributes] isCMP1_x: false (not CMP1.x) isJMS: false (not JMS) Has anyone else had problems with WAS 5.1 access MySql datasources? Tom, Make sure you're using 'com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource' as the datasource implementation class that you plug into your WebSphere config...For some reason, no other classes (Driver or plain DataSource) seem to work in 5.1sp1 or newer. FYI, I just tested this yesterday in WS 6.0 preview, and it has the same issue. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFANV/qtvXNTca6JD8RAnWcAKCD9n3If9DLMSJqOwc4ygbNpt+hsgCgmxQ4 Hd/VPa5IieQfH+Y/jnZfciM= =W/GZ -END PGP SIGNATURE- = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb recovery problems
Hi, I have been backing up via the dubious method of copying the database data folder onto another machine where it is properly backed up onto DLT. (yes, I know I should have used mysqldump!) Recovering some tables today I copied the files back into their position (including the ibdata1 file and the other id_* files) but the data is not recovered the table. There are no errors being given though. It is just as if I haven't recovered the file - the data is still missing The ibdata1 file and all the other id_* files from backup appear to be the same size as they are after the data had been deleted Any help greatly appreciated! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure with many parameters
To make it simple, is it possible to make function having many undecided parameters with MySQL 5.0 stored procedure/function? For example, add(3) add(3,5) add(3,5,1,4,9) add(3,5,1,4,9,8,6,7,2) In C, we use pointer to handle this, but I don't know how to code this at stored function CREATE FUNCTION statement. I looked at the document, but it didn't say about many undecided parameters. Someone help me please. __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speeding up index creation under InnoDB
I was wondering what the bottleneck was. I'm adding a dozen indexes to the same large-ish InnoDB table. Each successive index takes a bit longer (45 seconds or so on a dual P3-933 with 2 gig of RAM). Is it disk additional tables-space management that is taking the extra time? Would faster disks help? David
MySQL Replication scheme - DMZ - LAN
Network configuration: - Firewall connected to Internet, with two ports: LAN (protected) and DMZ - Web server on the DMZ segment - Internal Servers on the LAN segment. We would like to introduce a web database application on the Web server, using a Slave replication of a Master database running on the LAN Server. We have two kinds of problems: - For running replication we need to open ports on the LAN segment of the firewall to let the Slave read binary logs. - If we want web users to change information we need to operate on the Master Database (and open other ports). Is there another kind of replication schema that keep security of the LAN segment ? Thanks to all, Alberto Fabbro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
Chris, - Alkuperäinen viesti - Lähettäjä: Chris Nolan [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Monday, February 16, 2004 1:56 PM Aihe: Re: InnoDB Hot Backup + MySQL embedded? Heikki, Thank you greatly for answering my questions. Your obvious dedication to the open source world and high-quality of software cannot be understated. thank you :). Upon reading some of the InnoDB source code, I've noticed that C seems to be your language of choice. That said, it seems more and more of the literature that is promoted around my university emphasises testing methods related to OO languages like Java and C++ or looking at different programming paradigms such as logical programming (my university formally trains all students in Prolog and optionally Mercury) and functional programming (Our very first CS subject involves Haskell programming! It used to involve Miranda). Is there anything in the way of strategies that you would recommend to developers (I'm working on a MySQL/InnoDB based server app at the moment. Anyone else who reads this message please hit me with your 2c) regarding C programming and testing (they drill C into us with great rigor at the university I attend, but after second year it boils down to assignment submissions and that's about it)? C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. My opinion is based on: 1) The object-oriented paradigm recommends that algorithms are structured around 'objects', that is, around data structures. But I think it is easier for humans to understand complex algorithms, like the splitting of a node in a B-tree, if the algorithms are presented on their own terms, and not fragmented around the 'objects' involved. Donald Knuth of the Stanford University remarked that proving the correctness of an 'object-oriented' algorithm is hard, because keeping track of autonomous 'objects' is difficult. 2) C++ encourages programmers to use 'implicit' operations. In C++ functions can have the same name, even though they take a different number of parameters, and the data type of the parameters can differ. There can be implicit constructor and destructor operations for objects. I believe that the use implicit operations is prone to bugs, and makes a program harder to read. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In all programming languages I think the following are important programming conventions: 1) Remove redundancy from your code. Analogous to 'normalizing' your relational database. 2) Use a lot of comments. 3) Use a lot of assertions, which capture bugs early on. The reason that I ask is that you have managed to create the world's fastest transactional database handler that runs on such a large number of different architectures and operating systems. Also, the fact that you've taken everything that Oracle tout as making them brilliant and doing it many times better than they have displays your commitment and incredible set of skills. I must say that I have never before heard such praise in software world :). Often it is just the opposite. The Usenet is full of threads saturated with bashing and name-calling. I hope that one day that the software I am currently building will also contribute to both the commercial software world and the open source community. The fact that you've achieved this with such success and that you post to the MySQL mailing list so often is why I ask you these questions. Regards, Chris Best regards Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PEAR DB 1.6.0 has been released
Greetings: Crack open the beer, PEAR DB 1.6.0 is here! (Hey, I'm a bit giddy with excitement that my intense work during the past seven weeks has come to fruition.) For those unfamiliar with PEAR DB, it's a package of PHP classes that provide an object oriented API with common methods of accessing thirteen of PHP's database driver extensions: dBase, FrontBase, InterBase, Informix, mSQL, MS SQL Server, MySQL, Oracle, ODBC (tested with DB2 and Access), PostgreSQL, SQLite and Sybase. Key links for PEAR DB: Download:http://pear.php.net/get/DB Change Log: http://pear.php.net/package-changelog.php?package=DB Manual: http://pear.php.net/manual/en/package.database.php Home Page: http://pear.php.net/package/DB Notable changes since 1.5.0RC2 include: * Tons of bug fixes, making the package actually work for more than just MySQL systems. * New portability features, making it possible to write applications which can be easily ported between DBMS's. * Improved error reporting. * Getting prepare/execute to work the same way for all DBMS's and allow escaping of placeholder characters. * Deploying tableInfo() in more drivers and officially moving it from DB_result to DB_common. See the documentation for proper usage. * Making the test suite simpler to use and work on both windows and *nix platforms. * Various optimizations, several of which were uncovered using Zend Studio's Code Analyzer. * Countless documentation corrections. * Full PHP 5 compatibility. * Requiring PHP to be at version 4.2.0 or higher. * Deprecating quote() and quoteString(). If you're one of the lucky few people using PHP's mysqli extension, do note that the DB file/class has been renamed from mysql4 to mysqli, but the online documentation won't reflect this modification until they're rebuilt on Sunday. Similarly, there are a few other features and documentation bugs that won't show up there until then either. Is Dutch, German or Russian your native language? The manual needs translating into these languages. Join in by signing up for the pear-doc mailing list at http://pear.php.net/support.php. For those of you in and around New York City this coming Tuesday, February 24th, I'll be giving a presentation about this at the NYPHP meeting. See http://nyphp.org/ for more info. If you like the changes I've made to DB since version 1.5.0RC2, make a donation: http://www.analysisandsolutions.com/donate/donate.htm Enjoy, --Dan PS: I'm not on this list/newsgroup. Just posting this as an announcement. So, if you wish to contact me, please do so directly or via cc. -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question
- Original Message - From: Walt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 19, 2004 8:00 AM Subject: Newbie question I need a little advice on where to get started. I want to create a db and simple form that will populate the db. Which language is best? What should I read to help me along? That's pretty hard to answer since you haven't said anything about your skills, your environment, etc. Java is a really neat language but there's a pretty substantial learning curve to it. If you already know one or more programming languages, you should say so; it's quite possible that the language you already know can be used to do the work you require. If you are doing this work strictly on your own and for yourself, you can choose pretty much any language like Java, Perl, Php, C, C++, etc. On the other hand, if you are part of an IT shop, you should probably use the shop language, whatever it is. If you are doing this work for a customer and will hand maintenance of the program over to them, you should choose a language that your customer can support. Etc. etc. There are many possible options but the best one depends on your situation. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.1 Performance
If it's really utf8 problem lets check this out. Please submit the report as Sergei had asked. Regards, Igor -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 1:02 PM To: 'Brian Wintz'; [EMAIL PROTECTED] Subject: RE: MySQL 4.1.1 Performance Brian, I know that I have been using 4.1.0 and 4.1.1 and when compared to 4.0.x, 4.1.x is much faster for us. We have been pushing over 3000 queries per second with 4.1.1 without any problems. Now we aren't using utf8, but I don't think that would really be the problem. Donny -Original Message- From: Brian Wintz [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 9:39 PM To: [EMAIL PROTECTED] Subject: MySQL 4.1.1 Performance I have begun working with MySQL 4.1.1 using the utf8 encoding to capture unicode data. I converted an existing MySQL 4.0 (latin1) database by doing a dump and load (with the new databases character set to utf8). I'm noticing that the performance on the new 4.1 database is about 5 times slower. Is this to be expected? If so, is there a plan to address this issue? If you are currently not aware of this issue I am willing to try and put together a sample populated database and query that illustrates the problem. Please let me know if you would like me to do this. -Brian Brian Wintz Deployment Architect 6450 Via Real [EMAIL PROTECTED] (Embedded Carpinteria CA 93101, USATel: (805) 566-5235 image moved http://www.qad.com/ to file: pic29492.gif) A Passion for Manufacturing -- 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]
Can't get automake, make or make install to work
I am running a debian linux server and I need mysqlplus installed. There isn't a package yet for debian (at least not to my knowledge) to install it. So, I downloaded the mysql++ 1.7.9 source to install it. I am running version 2.95.4 of gcc. Is there something I am doing wrong? Do I need to install newer distributions? Here are the various error messages I get. When I run automake I get the errors: automake: Makefile.am: required file `./INSTALL' not found automake: Makefile.am: required file `./NEWS' not found automake: Makefile.am: required file `./COPYING' not found automake: Makefile.am: required file `./AUTHORS' not found automake: Makefile.am: required file `./ChangeLog' not found automake: couldn't open `Configure': No such file or directory When I run make I get: /usr/bin/ld: cannot find -lz collect2: ld returned 1 exit status make[2]: *** [libsqlplus.la] Error 1 make[2]: Leaving directory `/var/www/mysql/mysql++-1.7.9/sqlplusint' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/var/www/mysql/mysql++-1.7.9' make: *** [all-recursive-am] Error 2 And when I run make install I get: /usr/bin/ld: cannot find -lz collect2: ld returned 1 exit status make[1]: *** [libsqlplus.la] Error 1 make[1]: Leaving directory `/var/www/mysql/mysql++-1.7.9/sqlplusint' make: *** [install-recursive] Error 1 balrog:/home/gandalf/mysql/mysql++-1.7.9# Thanks
