Re: MySQL C API - Lost connection?
I don't know if this will answer the question: Is MySQL located on the same computer your script/software is running on..?? If this is not the case you have a chance that your network connection to the computer which MySQL is running on loses some packets which results in the above problem.. Another option to prevent these mistakes is to always check if a query was succesfully executed.. If not, retry the query, or send/log an error... I must say that with my experience of MySQL I can't recall any experience in which I lost a connection other than that the network connection fell away.. Regards, David - Original Message - From: Benjamin Charles Tehan [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, November 25, 2001 02:23 Subject: MySQL C API - Lost connection? I'm using MySQL 3.23.43 running on Linux with a 2.2.16 kernel. My software does around 50,000 inserts a day and from some of those inserts they result in: Lost connection to MySQL server during query The software is developed in C, using the mysql C api to talk to the database. Problem is.. once I get this error I'd assume I have lost connection to the database and that I would need to reconnect and try to insert the query again. For this instance I DO NOT reconnect to the database and the application continues to make more queries to the database but they work fine even after I get the above result from the queries I had made before. Does the MySQL C API automatically reconnect when a connection is lost? If so does it try to execute the query again once it has reconnected to the database? If it doesn't automatically reconnect then why am I getting this error when the application really has not lost connection with the database. Considering it can succesfully insert queries after the above result.. Any help would be appreciated. Regards, Benjamin Charles Tehan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Failure to enforce length of fields to index corrupts tables
Description: Documentation section 6.5.7 regarding create index states: For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax. (On BLOB and TEXT columns the length is required.) It's still possible in the mysql client to request the creation of an index with indexlength NULL on TEXT fields. This apparently works okay on creation of the index, insert and select operations, but breaks horribly (marks table as crashed with corrupted indexes) on update or delete operations. Same problem occurs with fulltext indexes -- indeed, I had an index created as a concatenation of two text fields (Name,Description). On running check-table and then show index, this fulltext index had a null cardinality; and, when dropped, it dropped immediately (too quickly to have data in it). We're using mysql4 in production since we need the fast fulltext indexing. http://ftp.avsim.com/library/ is the site running your backend. How-To-Repeat: Create table with field-type of TEXT. CREATE INDEX AnyName ON Table(TextField); Issue an update to a pre-existing record for the incorrectly indexed TextField. Fix: mysql should throw an error to the client if request is made to index a text field without arity; or assume 255 (which works okay). Submitter-Id: n/a Originator:Matt Johnson Organization: AVSIM Online http://www.avsim.com/ MySQL support: none Synopsis: mysql must enforce arity/length requirement when indexing TEXT fields. Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.0-alpha (Official MySQL RPM) Environment: System: Linux avsim.colo.cais.net 2.2.14-5.0 #1 Tue Mar 7 21:07:39 EST 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 26 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101836 Jan 15 2001 /lib/libc-2.1.3.so -rw-r--r--1 root root 20273324 Jan 15 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 15 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server '--with-comment=Official MySQL RPM' Perl: This is perl, version 5.005_03 built for i386-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: number of connections keeps increasing
Hi takashi,
My environment is :
Win2000, jdk1.3, tomcat 4,
Well, i am sure i added a snippet which closes statemnt and the connection.
for example, below is the snippet from one of my pages content.jsp :
% if (stmt!=null) { // actually this is a curly left brace
stmt.close(); } // actually this is a curly right brace
if (conn!=null){ // stmt is a Statement object
conn.close(); // conn is a Connection object
} %
whenever i call this page , number of connections inreases by one
it seems that the connections can not be closed
Any idea or suggestions ?
thanks a lot in advance
best regards :)
- Original Message -
From: "TAKAHASHI, Tomohiro" [EMAIL PROTECTED]
To: "yilmaz" [EMAIL PROTECTED]
Sent: Sunday, November 25, 2001 8:02 PM
Subject: Re: number of connections keeps increasing
I want to know environment about Java, JDBC, Servlet-Conatiner, and so
on.
Maybe, I think you forget to close connection in JSP somewhere.
yilmaz wrote:
hi takashi,
i don't know which jsp page exactly causes this problem,
because i have more than 30 JSP pages in my application, and i always
close my statements and connections after the process is finished.
But , i think probably the problem comes from MySQL itself
below is my MySQL environment:
This Report was made using the WinMySQLadmin 1.0 Tool
snip
--
TAKAHASHI, Tomohiro
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Symbolic Links to Data
Hello, I'm having some trouble in re-locating my MySQL databases to a different directory. My scenario is: Platform: Red Hat Linux 6.2 MySQL: 3.23.43 MySQL data currently stored in /var/lib/mysql The database name is music (so files are therefore in /var/lib/mysql/music) I wish to move the database music to /home/martyn/mysql. I create the above directory, then go into /var/lib/mysql and do mv music /home/martyn/mysql which moves the music database where I want it. I then do ln -s /home/martyn/mysql/music music which creates a sym link to the new location. This all seems to work fine (i.e. as a sym link should) but when I run the mysql client and type show databases; the music database is no longer shown. Is there anything additional to the above that I need to do to get this to work? Many thanks, Martyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Joining table with itself
I tried: select ... from T1 a inner join T1 b using(...); and it doesn't work. I get a message about Can't reopen table: 'a' . Is this not something that belongs in section 1.4.4 Functionality Missing from MySQL of the manual? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: number of connections keeps increasing
Hi,
How do you get JDBC Connection, named 'conn'?
It is from Driver Manger or Connection Pooling Utility?
Is 'conn' variable auto variable of method or instance variable of JSP?
Generally, JSP is called from multiple threads.
Do you lock 'conn' variable by synchronization?
I suggest we use try-catch-finally block to close resources related to
database, and watch whether 'conn' is closed with debug message as below
synchronized(conn) {
try {
...
}
catch(Excpetion ex) {
...
}
finally {
try{
conn.close();
System.out.println("closed!!");
}
catch(Exception ex){}
}
}
Thanks.
yilmaz wrote:
Hi takashi,
My environment is :
Win2000, jdk1.3, tomcat 4,
Well, i am sure i added a snippet which closes statemnt and the connection.
for example, below is the snippet from one of my pages content.jsp :
% if (stmt!=null) { // actually this is a curly left brace
stmt.close(); } // actually this is a curly right brace
if (conn!=null){ // stmt is a Statement object
conn.close(); // conn is a Connection object
} %
whenever i call this page , number of connections inreases by one
it seems that the connections can not be closed
Any idea or suggestions ?
thanks a lot in advance
best regards :)
--
TAKAHASHI, Tomohiro
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL C API - Lost connection?
The MySQL server is localhost. As for checking if the query was successful or not, this was the check, and that was the result of the failure. if (mysql_query(mysql,sqlcmd)) logger([sql] %s\n%s \n\n,sqlcmd,mysql_error(mysql)); sqlcmd is the query If the query fails logger() is executed which logs the query and the failure.. mysql_error(mysql) is the reason why it failed. The query failed with the result(error) Lost connection to MySQL server during query If it passed logger() would never be called on. The failed queries I have to manually insert into the database, the syntax is fine. It's maybe only 1 query in every 1,000 that fails. But when the daemon inserts 50,000 queries a day over a week that's a fair bit of failures which I can not put up with. Just trying to figure out if I have to write a second function to re-connect and retry to insert the failed query or does the mysql api automatically reconnect and insert the failed query. Id assume it automatically reconnects considering other queries after the failed ones work fine without error. Thanks for those suggestions David. Regards, Benjamin Charles Tehan Is MySQL located on the same computer your script/software is running on..?? Another option to prevent these mistakes is to always check if a query was succesfully executed.. Regards, David I'm using MySQL 3.23.43 running on Linux with a 2.2.16 kernel. My software does around 50,000 inserts a day and from some of those inserts they result in: Lost connection to MySQL server during query The software is developed in C, using the mysql C api to talk to the database. Problem is.. once I get this error I'd assume I have lost connection to the database and that I would need to reconnect and try to insert the query again. For this instance I DO NOT reconnect to the database and the application continues to make more queries to the database but they work fine even after I get the above result from the queries I had made before. Does the MySQL C API automatically reconnect when a connection is lost? If so does it try to execute the query again once it has reconnected to the database? If it doesn't automatically reconnect then why am I getting this error when the application really has not lost connection with the database. Considering it can succesfully insert queries after the above result.. Any help would be appreciated. Regards, Benjamin Charles Tehan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Symbolic Links to Data
Martyn Wendon wrote: Hello, I'm having some trouble in re-locating my MySQL databases to a different directory. My scenario is: Platform: Red Hat Linux 6.2 MySQL: 3.23.43 MySQL data currently stored in /var/lib/mysql The database name is music (so files are therefore in /var/lib/mysql/music) I wish to move the database music to /home/martyn/mysql. I create the above directory, then go into /var/lib/mysql and do mv music /home/martyn/mysql which moves the music database where I want it. I then do ln -s /home/martyn/mysql/music music which creates a sym link to the new location. This all seems to work fine (i.e. as a sym link should) but when I run the mysql client and type show databases; the music database is no longer shown. Is there anything additional to the above that I need to do to get this to work? Many thanks, Martyn Hello, No any problem with the installation ( rpm package ) of MySQL ? Edward. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: String Comparision
At 11:23 PM -0500 11/24/01, Gary Huntress wrote: I have a table of user info containing mixed case text. One user has created a database named brain and another has created a database Brain. I was surprised to see that: select * from users where dbname= Brain returns both brain and Brain. I read section 6.3.2.2 and select binary dbname from users where dbname=brain also returns both records. SELECT dbname FROM users WHERE BINARY dbname = brain perhaps? This surprises me. Have I missed another option somewhere? Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Symbolic Links to Data
Hi Edward, No problems in general with the installation. Everything has been working fine, I need to move the database files as I am running out of space on the /var partition. Kind Regards, Martyn - Original Message - From: [EMAIL PROTECTED] To: Martyn Wendon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, November 25, 2001 4:53 PM Subject: Re: Symbolic Links to Data Martyn Wendon wrote: Hello, I'm having some trouble in re-locating my MySQL databases to a different directory. My scenario is: Platform: Red Hat Linux 6.2 MySQL: 3.23.43 MySQL data currently stored in /var/lib/mysql The database name is music (so files are therefore in /var/lib/mysql/music) I wish to move the database music to /home/martyn/mysql. I create the above directory, then go into /var/lib/mysql and do mv music /home/martyn/mysql which moves the music database where I want it. I then do ln -s /home/martyn/mysql/music music which creates a sym link to the new location. This all seems to work fine (i.e. as a sym link should) but when I run the mysql client and type show databases; the music database is no longer shown. Is there anything additional to the above that I need to do to get this to work? Many thanks, Martyn Hello, No any problem with the installation ( rpm package ) of MySQL ? Edward. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: API documentation issue : inconsistency in mysql_real_connect documentation
So, if you pass NULL in as the first parameter to mysql_real_connect, a possibility opened up by the manual, what is the return. If it is a MYSQL* connection handle if the connection was successful, where did the space for that handle come from. I agree with Mario - if someone is going to go through the documentation, as recommended on every post, the least that can be expected is that the documentation should be un-ambiguous. Regards Quentin -Original Message- From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] Sent: Sunday, 25 November 2001 2:20 a.m. To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: API documentation issue : inconsistency in mysql_real_connect documentation M. A. Alves writes: What is the semantics of passing a NULL mysql connection to mysql_real_connect()? Does it create a new handler? This is the only possible interpretation of the result value as documented. However it is also (abundantly) documented that the handle must be created with mysql_init(). So what stands? (I transcribe below the relevant part of the manual. The last sentence of the excerpt is the crux of the problem.) This is not a pressing issue, just one of correctness. I find it a bit surprising that such an inconsistency, or incompletness, still exists in the documentation of such a very important part of mysql as the only API means of making connections. 8.4.3.171 mysql_real_connect() . . . The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect() you must call mysql_init() to initialize the MYSQL structure. . . . 8.4.3.173 Return Values A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter, unless you pass NULL for that parameter. (http://www.mysql.com/documentation/mysql/bychapter/manual_toc.html) -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150-180 PORTO, Portugalmob 351+939354002 Hi! I think that our documentation is quite clear on that issue. First parameter should be a pointer returned by mysql_init, while a return value from mysql_real_connect is used for testing purposes. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Symbolic Links to Data
Jan, Thanks for the pointer, indeed it was a rights issue, and all sorted now. Regards, Martyn - Original Message - From: [EMAIL PROTECTED] To: Martyn Wendon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, November 25, 2001 7:53 PM Subject: Re: Symbolic Links to Data Martyn, On Sun, 25 Nov 2001, Martyn Wendon wrote: No problems in general with the installation. Everything has been working fine, I need to move the database files as I am running out of space on the /var partition. This is surely an access rights problem. If you'd su to the user running mysql (probably 'mysql') (full command: su - mysql, or, if you don't have/know the password of the mysql user, use first su -, then as root su - mysql), and then try accessing the directory you mentioned as user mysql, you'll soon see whether you're allowed to or not. If not, set the appropriate rights and things should work. In case you need them, manpages to look at would be: chown/chgrp, chmod, su. Hope this helps. Sincerely, Jan -- Mr. Jan-Aage Bruvoll IT Project Manager 20 Min Holding, Thurgauerstrasse 40, CH-8050 Zurich Zurich office: +41 1 307 4293, fax: +41 1 307 4281 Office/fax: +44 2072408283 Mobile: +44 7740291600 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: String Comparision
At 12:19 2001-11-25 -0600, Paul DuBois wrote: select * from users where dbname= Brain returns both brain and Brain. I read section 6.3.2.2 and select binary dbname from users where dbname=brain also returns both records. SELECT dbname FROM users WHERE BINARY dbname = brain perhaps? How would that locate records with Brain? Doesn't sound like a solution to me... -- (database,sql,query,table) GRRR!!! Marjolein Katsma [EMAIL PROTECTED] Java Woman - http://javawoman.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Need to repair InnoDb tables
I think your diagnosis is correct. There is an FRM without a corresponding InnoDB table. The question is, what causes this, and how do we prevent it? It appears to be a bug related to CREATE TABLE and DROP TABLE. I have found it a good idea to commit and reconnect after creating an InnoDB table. Erik -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 17, 2001 2:20 AM To: [EMAIL PROTECTED] Subject: Re: Need to repair InnoDb tables Brent, maybe you have moved .frm files around? If there is an orphaned .frm file for a table without a corresponding table inside InnoDB data files you get the following errors: mysql show tables; +-+ | Tables_in_test2 | +-+ | fare| +-+ 1 row in set (0.34 sec) mysql describe fare; ERROR 1016: Can't open file: 'fare.InnoDB'. (errno: 1) mysql drop table fare; ERROR 1051: Unknown table 'fare' mysql and in the MySQL error log you get: 011017 9:11:52 InnoDB: Started mysqld: ready for connections InnoDB: Cannot find table test2/fare from the internal data dictionary InnoDB: of InnoDB though the .frm file for the table exists. Maybe you InnoDB: have deleted and recreated InnoDB data files but have forgotten InnoDB: to delete the corresponding .frm files of InnoDB tables? InnoDB: Error: table test2/fare does not exist in the InnoDB internal InnoDB: data dictionary though MySQL is trying to drop it. InnoDB: Have you copied the .frm file of the table to the InnoDB: MySQL database directory from another database? The way to fix the problem is to delete the .frm file from the MySQL database directory in question. Regards, Heikki http://www.innodb.com/ibman.html In my sample database, all of the tables are InnoDb. For some reason 2 of the tables can no longer be accessed. I keep getting error 1016. 1) Where can I find a list of error codes and their meanings? 2) How do I fix an InnoDb table? TIA Brent mysql show tables; +---+ | Tables_in_isample | +---+ | absence | | event | | member| | president | | score | | student | +---+ 6 rows in set (0.00 sec) mysql describe absence; ERROR 1016: Can't open file: 'absence.InnoDB'. (errno: 1) mysql describe president; ERROR 1016: Can't open file: 'president.InnoDB'. (errno: 1) mysql select * from absence; ERROR 1016: Can't open file: 'absence.InnoDB'. (errno: 1) mysql select * from president; ERROR 1016: Can't open file: 'president.InnoDB'. (errno: 1) mysql drop table president; ERROR 1051: Unknown table 'president' mysql drop table absence; ERROR 1051: Unknown table 'absence' mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Any datetime gurus out there?
At 2:45 PM -0800 11/25/01, Mark Rissmann wrote: Hey guys, Can anybody out there give me a helping hand? I have a table of the following format UserId LoginDate(datetime) LogoutDate(datetime) 1 2001-11-25 11:00:00 2001-11-25 13:45:00 2 2001-11-26 22:25:00 2001-11-27 02:00:00 I want to write a query that will calculate the hours worked for each record and then sum them up? Convert each of them to seconds since the epoch (beginning of 1970), take the difference to get number of seconds worked. Divide by the appropriate value, e.g., 60 to get minutes worked. This is going directly to a report engine so I have little control over it (like running multiple queries) except I am able to manipulate the final value and format it. Thanks for any help. Mark Rissmann - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DELETE does not work?
Hello, If someone could give me a pointer on this issue: I have a table called codes with only one field left. When trying this in PHP: $result=mysql_query(DELETE FROM codes WHERE left='$temp'); I get a syntax error... This must be some stupid mistake... But I am stuck... Thanks, sincerely, Gil. -- http://planenews.com PGP public key at: keskydee.com/gil.asc ICQ: 3310801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DELETE does not work?
At 6:59 PM -0500 11/25/01, Gil G. wrote: Hello, If someone could give me a pointer on this issue: I have a table called codes with only one field left. When trying this in PHP: $result=mysql_query(DELETE FROM codes WHERE left='$temp'); I get a syntax error... A syntax error from PHP or from MySQL? Might not be a problem with your query at all. If it is, try adding this line prior to the mysql_query() call: $temp = addslashes ($temp); Might be that $temp contains quotes or something. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Selecting boundary dates for a given week using date/time functions?
Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting boundary dates for a given week using date/time functions?
Well, whatever language you are using to output your data, there should be some unix tampstamp converting function. In php it's date(). On Sunday 25 November 2001 04:20 pm, Chris Cooper wrote: Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: DELETE does not work? Fixed!
On 26 Nov 2001 at 0:39, [EMAIL PROTECTED] wrote: Isn't left a reserved word? As in left join? But then, create shouldn't have worked either... Hello, Thanks a lot, that worked! I changed the name of the field, bingo! Sincerely, Gil. PS: for spam filter: database -- http://planenews.com PGP public key at: keskydee.com/gil.asc ICQ: 3310801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting boundary dates for a given week using date/time functions?
Hi Chris, The following query should work for you. select date_sub(date_col, interval weekday(date_col) day), date_add(date_col, interval 6-weekday(date_col) day) from table_name Anvar. At 07:20 PM 25/11/2001 -0500, you wrote: Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql win32 binary w/ innodb: error number 3
Hi folks, I need help to start my server with InnoDB. I have manually created innodb_data_home_dir, which is c:\mysql\ibdata. The mysql server is running on Windows ME platform. my.cnf is as follows: # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking default-character-set=latin1 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/mysql/ datadir = c:/mysql/data/ # Uncomment the following rows if you are using InnoDB tables # Create the directories ibdata and iblogs before to start the server. # If necessary, modify the size of the files innodb_data_file_path = ibdata1:200M innodb_data_home_dir = c:\mysql\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\mysql\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 #.._arch_dir must be the same as .._log_group_home_dir innodb_log_arch_dir = c:\mysql\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = lower_case_table_names =0 set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 # Uncomment the following row if you are using a Max server and you don't want the # InnoDb tables #skip-innobd [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key=16M [client_fltk] help_file= c:\mysql\sql_client\MySQL.help client_file= c:\mysql\MySQL.options history_length=20 database = test queries_root= c:\mysql\queries last_database_file= c:\mysql\lastdb The error I am getting is as follows: C:\c:\mysql\bin\mysqld-max --standalone InnoDB: Warning: operating system error number 3 in a file operation. InnoDB: Cannot continue operation. I appreciate your great help. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
command-line tools segfaulting
I am tring to install MySQL on a raq2. There's already a version of MySQL
installed on the box -- I put it there a year and a half ago.
Here's what I've done:
(1) Configure like so:
./configure --with-low-memory --disable-assembler --disable-shared \
--with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static \
mipsel-unknown-linux-gnu
and it dies:
mini_client.cc: In function `struct st_mysql * mc_mysql_connect(struct
st_mysql *, const char *, const char *, const char *, const char *,
unsigned int, const char *, unsigned int)':
mini_client.cc:623: passing `int *' as argument 5 of
`gethostbyname_r(const char *, hostent *, char *, unsigned int, hostent
**, int *)'
(2) configure like so (this is rough, I didn't write it down):
./configure --with-low-memory
collect[2]: ld failed, blah blah blah
(3)
so I installed the newest binutils (2.11.2) from gnu.org, and did a make
clean. then configured like this:
./configure --with-low-memory --with-gnu-ld
and then set LD to /usr/local/bin/ld in the Makefile and rebuilt it.
The server appears to be fine, but all the command-line tools (mysqldump,
mysql, mysqladmin) segfault immediately.
$ strace mysqladmin ping
[snip]
sigaction(SIGINT, {0x4044c4, [], SA_RESTART}, {SIG_DFL}) = 0
sigaction(SIGTERM, {0x4044c4, [], SA_RESTART}, {SIG_DFL}) = 0
socket(PF_UNIX, SOCK_STREAM, 0) = 3
fcntl(3, F_GETFL) = 0x2 (flags O_RDWR)
connect(3, {sun_family=AF_UNIX, sun_path=/tmp/mysql.sock}, 110) = 0
brk(0x1000d000) = 0x1000d000
setsockopt(3, IPPROTO_IP1, [8], 4) = -1 EOPNOTSUPP (Operation not
supported)
setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
read(3, \0\0\0\0, 4) = 4
read(3, (\0\0\0003.44\0\5\0\0\0B6\'\\~..., 40) = 40
open(, O_RDONLY) = 4
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
$ strace mysql
connect(3, {sa_family=4160, sa_data=/tmp/mysql.soc}, 110) = 0
brk(0x1000c000) = 0x1000c000
setsockopt(3, IPPROTO_IP1, [8], 4) = -1 EOPNOTSUPP (Operation not
supported)
setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 2147482016) = 0
read(3, (\0\0\0, 4) = 4
read(3, \n3.23.44\0\7\0\0\0cJ*/O|H.\0,\0..., 40) = 40
open(, O_RDONLY) = 4
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
help, please.
Paul
shad 96c / uw cs 2001 / mac activist / eda / fumbler
fan of / jewel / sophie b. / sarah slean / steve poltz / emm gryner /
/ x-files / buffy / dawson's creek / habs / bills / 49ers /
t h i n k d i f f e r e n t.
I'd rather see the world from another angle. -- Jewel Kilcher
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting boundary dates for a given week using date/timefunctions?
Perfect. Thanks, Anvar. -- coop On Sun, 2001-11-25 at 22:20, Anvar Hussain K.M. wrote: Hi Chris, The following query should work for you. select date_sub(date_col, interval weekday(date_col) day), date_add(date_col, interval 6-weekday(date_col) day) from table_name Anvar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mac OS X and MySQL 3.23.45
From what I see in the release notes, MySQL 3.23.45 is an important release for Mac OS X since it fixes the bug in which one could not use mysqladmin shutdown and had to force the mysqld to quit using kill -9. Two questions: 1) Will there be a binary for MySQL 3.23.45 for MacOS X 10.0.x (Darwin 1.3.x)? 2) How do I find out what options are compiled into the binary? BTW, Thanks to MySQL AB for making a binary specific to Mac OS X! -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql win32 binary w/ innodb: error number 3
At 20:45 25/11/01 -0800, Bryan Chong wrote: Hi, Hi folks, I need help to start my server with InnoDB. I have manually created innodb_data_home_dir, which is c:\mysql\ibdata. The mysql server is running on Windows ME platform. my.cnf is Notice that beside the c:\mysql\ibdata directory you need to create the c:\mysql\iblogs too. Before to try again the first start, delete the files created in c:\mysql\ibdata. Regards, Miguel as follows: # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking default-character-set=latin1 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/mysql/ datadir = c:/mysql/data/ # Uncomment the following rows if you are using InnoDB tables # Create the directories ibdata and iblogs before to start the server. # If necessary, modify the size of the files innodb_data_file_path = ibdata1:200M innodb_data_home_dir = c:\mysql\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\mysql\iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 #.._arch_dir must be the same as .._log_group_home_dir innodb_log_arch_dir = c:\mysql\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = lower_case_table_names =0 set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 # Uncomment the following row if you are using a Max server and you don't want the # InnoDb tables #skip-innobd [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key=16M [client_fltk] help_file= c:\mysql\sql_client\MySQL.help client_file= c:\mysql\MySQL.options history_length=20 database = test queries_root= c:\mysql\queries last_database_file= c:\mysql\lastdb The error I am getting is as follows: C:\c:\mysql\bin\mysqld-max --standalone InnoDB: Warning: operating system error number 3 in a file operation. InnoDB: Cannot continue operation. I appreciate your great help. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: String Comparision
Paul, At 16:56 2001-11-25 -0600, Paul DuBois wrote: database,sql,query,table At 7:40 PM +0100 11/25/01, Marjolein Katsma wrote: At 12:19 2001-11-25 -0600, Paul DuBois wrote: select * from users where dbname= Brain returns both brain and Brain. I read section 6.3.2.2 and select binary dbname from users where dbname=brain also returns both records. SELECT dbname FROM users WHERE BINARY dbname = brain perhaps? How would that locate records with Brain? Doesn't sound like a solution to me... It wouldn't locate such records. BINARY makes the comparison case sensitive. WHERE dbname = brain returns only brain records WHERE dbname = Brain returns only Brain records But the original report (which I quoted, and still quote) was that the latter (the intended effect) did not work as it returned also brain. Your solution is apparently no solution to this problem. So the question remains what the solution (or the cause of the problem!) is. -- (database,sql,query,table) GRRR!!! Marjolein Katsma [EMAIL PROTECTED] Java Woman - http://javawoman.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Arrayoutofindexexception
hi all, first of all, since this is a jdbc specific question, you might say i should post to java mysql mailing list, but for some reasons my java mysql subscribtion seems didn't succeed although i got a confirmation and a welcome email. (of course i confirmed my subscribtion) Anyway my request is: can someone tell me please what does ArrayIndexOutOfBoundsException exacly mean? and how can it be solved? i couldn't figure out where the problem is altough i spent almost half working day. below is the error message : java.lang.ArrayIndexOutOfBoundsException at org.gjt.mm.mysql.PreparedStatement.set(PreparedStatement.java:1192) at org.gjt.mm.mysql.PreparedStatement.setString(PreparedStatement.java:581) at org.apache.jsp._0002fay_0002fmember_jsp._jspService(_0002fay_0002fmember_jsp .java:178) . i have exactly same number of column names as variables in preparedstatemnet and same number of ? marks. i am using mySQL version 3.23 with tomcat 4.0 b7 under win 2000. Can anyone help me please? thanks in advance wish everybody a day without troubles :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
