mysql down problem.
Hello, all. when I direct telnet to mysql server the mysql daemon is down or restarted like this. telnet my.mysql.server 3306 [root@www tmp]# /usr/local/mysql/bin/mysql.server start [root@www tmp]# Starting mysqld daemon with databases from /usr/local/mysql/var and telnet to mysql port at this server the service is ended like this. [root@www tmp]# /usr/local/mysql/bin/safe_mysqld: line 273: 12704 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 021104 18:11:50 mysqld ended But when I started with mysql user, the service itself is down and when I started with root user, the mysql service is restarted. my mysql version is ./mysql -V ./mysql Ver 11.18 Distrib 3.23.52, for pc-linux-gnu (i686) Is this a mysql bug or not? Please how can I solve this problem. Thanks in advance. _ MSN Messenger¸¦ ÅëÇØ ¿Â¶óÀÎ»ó¿¡ Àִ ģ±¸¿Í ´ëȸ¦ ³ª´©¼¼¿ä. http://messenger.msn.co.kr - 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
getting around a subselect
As a novice in (My)sql I encounter this problem: I would like to select from a table first the 39 records with the highest variable1 and order them on variable2. So I need something like a subselect and I can't figure out a way around it. Ideally something like: select * from table1 order by var2 in (select * from table1 order by var1 limit 0,39); Best regards Hein - 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
ASP/MYSQL
Hello members,
I'm new to mysql and need to conect to from ASP pages.
I have written the following code in ASP script
!- #include file=C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\adovbs.inc -
%
dim email,objrs,strsql,strconn
email=Request.Form (email)
strconn = DSN=ebyte
strsql=select * from log where email=(' email ');
set objrs=server.CreateObject (adodb.recordset)
With objrs
.ActiveConnection = strconn
.Source = strsql
.Open
End With
Response.Write objrs(email)
Set objrs = Nothing
%
i get the following error msg on the browser
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified
i have checked the dsn and the adminstrator say its fine.
Background info
mysql installed on linux server and site hosted on iis server on windows
2000
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, November 04, 2002 9:57 AM
Subject: getting around a subselect
As a novice in (My)sql I encounter this problem:
I would like to select from a table first the 39 records with the highest
variable1 and order them on variable2.
So I need something like a subselect and I can't figure out a way around
it.
Ideally something like:
select * from table1 order by var2 in (select * from table1 order by var1
limit
0,39);
Best regards
Hein
-
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
ORDER BY and charset iso-8859-2 and windows-1250
Hello all, I have problem with ORDER BY in MySQL if I have some characters in iso-8859-2 and windows-1250. What I should do for fix it. Thanks Radek - 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: getting around a subselect
Check out how to get around subselect queries in the manual. But incase your query is a little complex and cannot be solved as explained in the manual, use temporary table(s). When I had this problem I went round it using MySQL Temporary tables. So in your case, you can do something like: CREATE TEMPORARY TABLE Hein SELECT * FROM table1 ORDER BY var1 LIMIT 0,39 SELECT * FROM Hein ORDER BY var2; It works fine and fast as well in my applications. Hope this solves your problem. Best Regards, 'Gbamila - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 04, 2002 10:57 AM Subject: getting around a subselect As a novice in (My)sql I encounter this problem: I would like to select from a table first the 39 records with the highest variable1 and order them on variable2. So I need something like a subselect and I can't figure out a way around it. Ideally something like: select * from table1 order by var2 in (select * from table1 order by var1 limit 0,39); Best regards Hein - 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
Problems with ORDER BY from C API (works from command-line tool mysql.exe)
Hi! This SQL works perfect when running the command line tool mysql.exe SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 BUT when running from the C API, the ORDER BY part doesn't seem active, why? Please help. /Christer - 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: Problems with ORDER BY from C API (works from command-linetool mysql.exe)
Christer Holmström (at Home) wrote: Hi! This SQL works perfect when running the command line tool mysql.exe SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 BUT when running from the C API, the ORDER BY part doesn't seem active, why? Please help. /Christer Hi, What do expect with ORDER BY 2 ? If you want to arder by sum(duration) you should use: SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s Hope this helps -- Joseph Bueno - 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 uses 98% CPU
Pada Mon, 4 Nov 2002 11:55:59 +0100 Toni [EMAIL PROTECTED] menulis: Hello I've got mysql 3.23.47 running on a freebsd 4.5 box for long time without problems, until now that consumes nearly all available CPU. Also, I am unable to stop the server using mysqladmin -u root shutdown (the process freezes) neither can restart it. If I kill -9 the process it restarts and keeps using all my resources. Aparently there isn't any other active process on the machine. I have checked at mysql.com but I couldn't find any related information. ¿Where could this problem come from? ¿Any solution? Check the running queries show processlist ... or mysqladmin -p processlist ... or even better use mytop / mtop for some condition (high cpu usage), optimize your tables, or mysqlcheck -or -A -p You should avoid kill -9 (safe_mysqld/mysql) ... it's only for desperate move ... :D -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP: Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone: +62 21 79199577 - HP: +62 8158787286 Web: http://www.1rstwap.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
select using regexp
Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for cat should match any of: The cat sat on the mat It was a large cat. Cat food is interesting. Dog. Cat. Fish. but not match in a catatonic state it was a catastrophe scattergun approach It looks as if the MySQL REGEXP function is what I need here, but I can't work out from the documentation how to get what I want. Any suggestions? Mark - 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: Problems with ORDER BY from C API (works from command-line tool mysql.exe)
Hi, and thanks for the response, but unfortunately the same problem with both below SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 DESC; SELECT exe, sum(duration) AS s FROM data GROUP exe BY ORDER BY s DESC; Works from mysql.exe but not from C API. /Christer -Original Message- From: Joseph Bueno [mailto:joseph.bueno;trader.com] Sent: den 4 november 2002 11:57 To: Christer Holmström (at Home) Cc: [EMAIL PROTECTED] Subject: Re: Problems with ORDER BY from C API (works from command-line tool mysql.exe) Christer Holmström (at Home) wrote: Hi! This SQL works perfect when running the command line tool mysql.exe SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 BUT when running from the C API, the ORDER BY part doesn't seem active, why? Please help. /Christer Hi, What do expect with ORDER BY 2 ? If you want to arder by sum(duration) you should use: SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s Hope this helps -- Joseph Bueno - 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: MySql uses 98% CPU
On Mon, 2002-11-04 at 11:58, Dicky Wahyu Purnomo wrote: Pada Mon, 4 Nov 2002 11:55:59 +0100 Toni [EMAIL PROTECTED] menulis: Hello I've got mysql 3.23.47 running on a freebsd 4.5 box for long time without problems, until now that consumes nearly all available CPU. Also, I am unable to stop the server using mysqladmin -u root shutdown (the process freezes) neither can restart it. If I kill -9 the process it restarts and keeps using all my resources. Aparently there isn't any other active process on the machine. I have checked at mysql.com but I couldn't find any related information. ¿Where could this problem come from? ¿Any solution? Check the running queries show processlist ... or mysqladmin -p processlist ... or even better use mytop / mtop for some condition (high cpu usage), optimize your tables, or mysqlcheck -or -A -p You should avoid kill -9 (safe_mysqld/mysql) ... it's only for desperate move ... :D We get the same thing on FreeBSD machines quite often, and it's not related to the actual work being done; MySQL can sit idle but use up all CPU. It happens after a few days/weeks of uptime. Switching to Linux fixed it ;) The difference with what Dicky describes is that for us the MySQL servers still works; it just looks like there's a rogue thread running around doing nothing. - 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
Help Records Missing
A table using Berkeley Tables should have 55000 records. The mysql server crashed. When running this query: select id from customers; returns 16000 records running: select count(id) from customers; returns 16000 records however running a specific query on an specific id i.e. select * from customers where id = 6243655; returns a valid record with data. when scrolling through the above query records this id does NOT exist. The data I need to recover is *there* and seems to be accessible only through a direct query. It seems like the indexing for this table is corrupt. How do you repair an index on a DB Berkeley Table I can only find references to myiamchk which states is can only be used for MyISAM tables? Any help would be GREAT! cheers steve / * Stephen Hitchner * * Senior Developer * Number 41 Media Corporation * p 250.414.0410 * f 250.414.0411 * [EMAIL PROTECTED] * www.number41media.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: select using regexp
Mark, It looks like you should be using full-text indexes and the match and against functions to me. Check out section 6.8 in the manual. Andy mysql query -Original Message- From: Mark Goodge [mailto:mark;good-stuff.co.uk] Sent: 04 November 2002 11:21 To: [EMAIL PROTECTED] Subject: select using regexp Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for cat should match any of: The cat sat on the mat It was a large cat. Cat food is interesting. Dog. Cat. Fish. but not match in a catatonic state it was a catastrophe scattergun approach It looks as if the MySQL REGEXP function is what I need here, but I can't work out from the documentation how to get what I want. Any suggestions? Mark - 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: select using regexp
Hi
You could use either normal or fulltext searches. Regexp may well be a good
answer (not used it myself). The following should also work
SELECT *
FROM table
WHERE
field LIKE % cat %
OR field LIKE % cat. %
OR field LIKE % cat, %
(note the spaces to make sure you get only complete words)
or if you have a mysql version that supports FULLTEXT
SELECT * FROM table
WHERE MATCH (field) AGAINST ('cat');
http://www.mysql.com/doc/en/Fulltext_Search.html
which is much more elegant
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Mark Goodge [mailto:mark;good-stuff.co.uk]
Sent: 04 November 2002 11:21
To: [EMAIL PROTECTED]
Subject: select using regexp
Hi,
I've got a problem that I'm hoping someone can help with. I need to do
a query against a text column in order to extract entries that will
match whole words only - for example, a search for cat should match
any of:
The cat sat on the mat
It was a large cat.
Cat food is interesting.
Dog. Cat. Fish.
but not match
in a catatonic state
it was a catastrophe
scattergun approach
It looks as if the MySQL REGEXP function is what I need here, but I
can't work out from the documentation how to get what I want.
Any suggestions?
Mark
-
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
Problem in decimal separator
I have a problem when I load a text file using the sentence load data infile as long as decimals are separated by commas and mysql ignores those decimals, is there any way to specify that decimal separator is comma or point? - 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
Logging In Errors
Hi Group, I made some changes to user privileges in mysql and now I cant log in. Can anyone provide a step by step guide as to how I can fix the issue below, without reinstalling mysql? And if I reinstall, will I lose all of the databases created? Warning: Access denied for user: 'root@localhost' (Using password: NO) in /usr/local/etc/httpd/htdocs/mysql/lib.inc.php on line 308 Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) in /usr/local/etc/httpd/htdocs/mysql/lib.inc.php on line 308 Error Thanks Steve - 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: Logging In Errors
Stop mysqld and start it again using the --skip-grant-tables command line option. -- FROM THE MANUAL AT http://www.mysql.com/doc/en/Command-line_options.html This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.) -- It seems that you are trying to access mysql using the root account without a password. If part of your changes to the user accounts have been to change (or add) the password for the root account you need to specify that password. I can't remember how that is done in PHP, but from the command-line you would use this: mysql -u root -p Using the -p switch will tell the client to prompt you for the password. There is more helpful info at http://www.mysql.com/doc/en/Access_denied.html. Duncan --- Duncan Salada Titan Systems Corporation 301-925-3222 x375 -Original Message- From: Steve J Racicot [mailto:sracicot;nhbis.com] Sent: Monday, November 04, 2002 7:24 AM To: [EMAIL PROTECTED] Subject: Logging In Errors Hi Group, I made some changes to user privileges in mysql and now I cant log in. Can anyone provide a step by step guide as to how I can fix the issue below, without reinstalling mysql? And if I reinstall, will I lose all of the databases created? Warning: Access denied for user: 'root@localhost' (Using password: NO) in /usr/local/etc/httpd/htdocs/mysql/lib.inc.php on line 308 Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) in /usr/local/etc/httpd/htdocs/mysql/lib.inc.php on line 308 Error Thanks Steve - 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
Distributing table-files to other machines
At the moment, the files for tables in a database are all stored in mysql/data/databasename/. Is it possible to distribute the table-files to different discs, or even better, different machines, and still keep the flexability of having everything in one database. Example 1: Prefs: 1 Database 5 tables 5 discs Explanation: Put the first table on the same DISC as the database. The other tables have one DISC each. Example 2: Prefs: 1 database 5 tables 5 servers Explanation: Put the first table on the same SERVER as the database. The other tables have one SERVER each. Any thoughts? // Michelle __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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: Logging In Errors
I would love to do that, but I am getting access denied errors when I do: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'nhbhost@localhost' everything I do gives me an access denied... Steve - Original Message - From: ErwiniEtzsche [EMAIL PROTECTED] To: Steve J Racicot [EMAIL PROTECTED] Sent: Monday, November 04, 2002 3:09 PM Subject: Re: Logging In Errors On Monday 04 November 2002 19:23, you wrote: Hi Group, I made some changes to user privileges in mysql and now I cant log in. Can anyone provide a step by step guide as to how I can fix the issue below, without reinstalling mysql? And if I reinstall, will I lose all of the databases created? Warning: Access denied for user: 'root@localhost' (Using password: NO) in /usr/local/etc/httpd/htdocs/mysql/lib.inc.php on line 308 Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) in /usr/local/etc/httpd/htdocs/mysql/lib.inc.php on line 308 Error Thanks Steve try this: mysqld --skip-grant-tables [and now try logging in again with your -u user and -p password] or, dig more what we can find at chapter4 MySQL manual. regards, -- Erwin M Sitorus TROTOAR IT SOLUTIONS http://www.trotoar.web.id - Kebijaksanaan berasal dari Kedunguan Absurditas adalah cikal-bakal Kesempurnaan - 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
MyCC
Hi Everybody I just downloaded the MyCC program from the MySQL website. Does it have the capability of being able to select an external text editor, as I would like to edit inserted HTML. Thanks for any reply Kevin Ratcliffe Bolton 6th Form College Bolton, UK - 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 DATETIME substraction problem/?
You can't just subtract datetime fields. Check the date and time functions in the manual. Jim Hogan wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like: My calc elapsed_time 45 85 11 11 16 16 9 9 22 62 14 14 73 73 59 99 65 105 20 20 12 12 43 4083 That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window! Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it. - 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 windows connection problem
Hello. I'm running version 3.23 of MySql server for Windows on a Win2K machine. I'm using the standard C API in my client software. A single client connects succesfully, but when I run a number of concurrent clients on the same machine (more than apr. 32), the connection (mysql_real_connect() function) randomly fails , returning the Can't connect to MySQL server on 'localhost' (10061)' error. I set a pretty large number (more than a thousand) for max_connections variable. Are there any windows limitations (TCP/IP etc.?) that I'm running into, or maybe there are other mysql limitations which I have to take into account? Thank you, in advance. Gera. - 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
connecting to mysql server from other machines
I have a huge problem. I have several remote machines that connect to a mysql server. On Friday, the mysql server started returning ERROR 2013: Lost connection to MySQL server during query Any idea. Michael Gargiullo Network Administrator Warp Drive Networks 590 Valley Health Plaza Paramus NJ 07650 1-201-576-9292 x260 1-201-225-0007 - F www.warpdrive.net - 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 and binary data
I am using Qt 3.0.5's QSql database classes to store information in a MySQL database. MySQL is at version 3.23.49. I am trying to store binary data into a column marked as MEDIUMBLOB (or even LONGBLOB) but am having difficulties. Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. The data I am trying to insert is an exectuable application, although I have tried it with a normal text file and have run into the same problems. Can anyone shed some light onto what could be causing this problem? Thanks!! Steve Nakhla [EMAIL PROTECTED] __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.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: connecting to mysql server from other machines
See this thread : https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=75128 - Original Message - From: Michael Gargiullo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 04, 2002 9:26 AM Subject: connecting to mysql server from other machines I have a huge problem. I have several remote machines that connect to a mysql server. On Friday, the mysql server started returning ERROR 2013: Lost connection to MySQL server during query Any idea. Michael Gargiullo Network Administrator Warp Drive Networks 590 Valley Health Plaza Paramus NJ 07650 1-201-576-9292 x260 1-201-225-0007 - F www.warpdrive.net - 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: Problems with ORDER BY from C API (works from command-linetool mysql.exe)
Then I would suggest you check your C code. The program 'mysql' uses the C API. Christer Holmström (at Home) wrote: Hi, and thanks for the response, but unfortunately the same problem with both below SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 DESC; SELECT exe, sum(duration) AS s FROM data GROUP exe BY ORDER BY s DESC; Works from mysql.exe but not from C API. /Christer -Original Message- From: Joseph Bueno [mailto:joseph.bueno;trader.com] Sent: den 4 november 2002 11:57 To: Christer Holmström (at Home) Cc: [EMAIL PROTECTED] Subject: Re: Problems with ORDER BY from C API (works from command-line tool mysql.exe) Christer Holmström (at Home) wrote: Hi! This SQL works perfect when running the command line tool mysql.exe SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 BUT when running from the C API, the ORDER BY part doesn't seem active, why? Please help. /Christer Hi, What do expect with ORDER BY 2 ? If you want to arder by sum(duration) you should use: SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s Hope this helps - 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: select using regexp
There are too many exceptions for this to be usefull.
What about lines ending in cat. or cat,
Your example won't match them.
Perhaps % cat.% and % cat,% patterns might be more helpfull,
but what about lines that begin with cat?
Peter Lovatt wrote:
Hi
You could use either normal or fulltext searches. Regexp may well be a good
answer (not used it myself). The following should also work
SELECT *
FROM table
WHERE
field LIKE % cat %
OR field LIKE % cat. %
OR field LIKE % cat, %
(note the spaces to make sure you get only complete words)
or if you have a mysql version that supports FULLTEXT
SELECT * FROM table
WHERE MATCH (field) AGAINST ('cat');
http://www.mysql.com/doc/en/Fulltext_Search.html
which is much more elegant
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Mark Goodge [mailto:mark;good-stuff.co.uk]
Sent: 04 November 2002 11:21
To: [EMAIL PROTECTED]
Subject: select using regexp
Hi,
I've got a problem that I'm hoping someone can help with. I need to do
a query against a text column in order to extract entries that will
match whole words only - for example, a search for cat should match
any of:
The cat sat on the mat
It was a large cat.
Cat food is interesting.
Dog. Cat. Fish.
but not match
in a catatonic state
it was a catastrophe
scattergun approach
It looks as if the MySQL REGEXP function is what I need here, but I
can't work out from the documentation how to get what I want.
Any suggestions?
Mark
-
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
-
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: 4.0.4 stalls
Compiling the 4.0.4 source seems to have solved the problem. Regards, Tomek Heikki Tuuri wrote: Tomek, then it is best to either test 3.23.53a or wait for 4.0.5. But please report if those were able to solve the thrashing. Regards, Heikki - Original Message - *From:* Tomek Dudziak mailto:tomek;allegro.pl *To:* Heikki Tuuri mailto:Heikki.Tuuri;innodb.com *Cc:* [EMAIL PROTECTED] mailto:mysql;lists.mysql.com *Sent:* Saturday, November 02, 2002 7:58 PM *Subject:* Re: 4.0.4 stalls I am now able to reproduce this bug in testing env. If you'd like me to run any commands while the server is hanging, please let me know. I tried set-variable = innodb_thread_concurrency=1 and set-variable = thread_concurrency=1 Didn't help. Tomek Heikki Tuuri wrote: Tomek, - Original Message - From: Tomek Dudziak [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, November 02, 2002 1:34 PM Subject: Re: 4.0.4 stalls [EMAIL PROTECTED] (Tomek Dudziak) wrote in message news:[EMAIL PROTECTED]... about 300 queries per second Server runs nicely for some time. Then suddenly processes begin to eat up all the CPU. Load goes to 100s. It takes several seconds to connect, so number of processes rises. Disk activity stops after some time, but still it's hard to connect. When I shut down apache servers, problem IMMEDIATELY (1-2 sec) goes away. Server goes back to normal. I was told that this is a glibc problem and 4.0.4 was not compiled right. Can someone from MySQL confirm this? When's the next release the problem certainly sounded like thread thrashing. My guess is that 4.0.5 will be released Nov 15th, 2002. If possible, you can try 3.23.53a which has a new glibc. You can also try adding to the [mysqld] section of my.cnf: set-variable = innodb_thread_concurrency=1 Please report to the mailing list [EMAIL PROTECTED] if you were able to solve the thrashing problem. We want feedback from the field about how the new glibc is faring. date? Tomek Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - 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: Help Records Missing
How about mysqldump? Stephen Hitchner wrote: A table using Berkeley Tables should have 55000 records. The mysql server crashed. When running this query: select id from customers; returns 16000 records running: select count(id) from customers; returns 16000 records however running a specific query on an specific id i.e. select * from customers where id = 6243655; returns a valid record with data. when scrolling through the above query records this id does NOT exist. The data I need to recover is *there* and seems to be accessible only through a direct query. It seems like the indexing for this table is corrupt. How do you repair an index on a DB Berkeley Table I can only find references to myiamchk which states is can only be used for MyISAM tables? Any help would be GREAT! cheers steve / * Stephen Hitchner * * Senior Developer * Number 41 Media Corporation * p 250.414.0410 * f 250.414.0411 * [EMAIL PROTECTED] * www.number41media.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 - 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: Problems with ORDER BY from C API (works from command-line tool mysql.exe)
The code works in that sence that I get resultsets, the only thing that
does not work is the ORDER BY option for the SELECT statement. The
resultset is ordered by the exe column instead.
Should I run MyODBC instead?
Is the sourcecode for mysql.exe available?
Is there any miss-usage of mysql_query, mysql_store_result etc?
/Christer
void StatisticsToday()
{
charszSQL[1024];
MYSQL_RES* res;// Resultset
MYSQL_ROW row;// Row
int i, j;
// Tried both below
strcpy(szSQL, SELECT DISTINCT exe, sum(duration) AS s FROM data
GROUP BY exe ORDER BY s DESC);
strcpy(szSQL, SELECT DISTINCT exe, sum(duration) FROM data
GROUP BY exe ORDER BY 2 DESC;);
if (!mysql_query( myData, szSQL)) {
res = mysql_store_result( myData ) ;
i = (int) mysql_num_rows( res ) ;
while ( row = mysql_fetch_row( res ) )
{
j=mysql_num_fields( res );
sprintf(szId, %s %s, row[0], row[1]);
}
mysql_free_result( res ) ;
} else {
printf( Couldn't execute %s on the server !\n, szSQL )
;
}
}
-Original Message-
From: gerald_clark [mailto:gerald_clark;suppliersystems.com]
Sent: den 4 november 2002 15:51
To: Christer Holmström (at Home)
Cc: 'Joseph Bueno'; [EMAIL PROTECTED]
Subject: Re: Problems with ORDER BY from C API (works from
command-line tool mysql.exe)
Then I would suggest you check your C code.
The program 'mysql' uses the C API.
Christer Holmström (at Home) wrote:
Hi, and thanks for the response, but unfortunately the same problem
with both below
SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 DESC;
SELECT exe, sum(duration) AS s FROM data GROUP exe BY ORDER BY s DESC;
Works from mysql.exe but not from C API.
/Christer
-Original Message-
From: Joseph Bueno [mailto:joseph.bueno;trader.com]
Sent: den 4 november 2002 11:57
To: Christer Holmström (at Home)
Cc: [EMAIL PROTECTED]
Subject: Re: Problems with ORDER BY from C API (works from
command-line tool mysql.exe)
Christer Holmström (at Home) wrote:
Hi!
This SQL works perfect when running the command line tool mysql.exe
SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2
BUT when running from the C API, the ORDER BY part doesn't seem
active, why?
Please help.
/Christer
Hi,
What do expect with ORDER BY 2 ?
If you want to arder by sum(duration) you should use:
SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s
Hope this helps
-
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 and binary data
- Original Message - From: Steven Nakhla [EMAIL PROTECTED] To: QT [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, November 04, 2002 3:47 PM Subject: MySQL and binary data I am using Qt 3.0.5's QSql database classes to store information in a MySQL database. MySQL is at version 3.23.49. I am trying to store binary data into a column marked as MEDIUMBLOB (or even LONGBLOB) but am having difficulties. Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. The data I am trying to insert is an exectuable application, although I have tried it with a normal text file and have run into the same problems. Can anyone shed some light onto what could be causing this problem? Thanks!! I think you may be running into the max_allowed_packet size, which defaults to 1048576 (1M). Beyond that, the mysqld server issues a Packet too large error and closes the connection. Restart the server with the set-variable=max_allowed_packet=16M option, or any other value of your choice. - Mark - 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 uses 98% CPU
On Mon, Nov 04, 2002 at 12:37:15PM +0100, Nicolas MONNET (Tech) wrote: We get the same thing on FreeBSD machines quite often, and it's not related to the actual work being done; MySQL can sit idle but use up all CPU. It happens after a few days/weeks of uptime. That's a known bug in FreeBSD's threading implementation. Disable name resolution and it'll go away. Some details are here: http://jeremy.zawodny.com/blog/archives/000203.html#000203 and http://jeremy.zawodny.com/blog/archives/000264.html#000264 Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 90 days, processed 1,874,326,961 queries (240/sec. avg) - 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 and binary data
Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. You've hit the default maximum packet size of 1 MB. This is a protocol option - the MySQL client-server protocol limits itself to packets of a certain size. This is both a client and server option. You need to increase both. Here's what I have in my.cnf, in the [mysql] and [mysqld] sections: set-variable= max_allowed_packet=16M - 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
safe_mysqld crashing
I tried to send this Friday, but was a bit frazzled and probably didn't give enough information. So, I'll try again and see if anyone can help. My RedHat 7.3 system running MySQL 3.23.52 crashed. On reboot, everything came up normally except MySQL. When safe_mysqld is run, I get the following error: Number of processes running now: 1 mysqld process hanging, pid 15139 - killed 021101 16:01:41 mysqld restarted /opt/mysql/bin/safe_mysqld: line 273: 15159 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 15162 - killed 021101 16:01:42 mysqld restarted /opt/mysql/bin/safe_mysqld: line 273: 15182 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 15185 - killed 021101 16:01:42 mysqld restarted /opt/mysql/bin/safe_mysqld: line 273: 15205 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 This continues until the safe_mysqld process is manually killed. Looking at the script, it appears that the threads are being tested to make sure they aren't hanging. For some reason, the daemon is killing every thread as if it was hung. It looks to me as if this is a comparison against the pid but haven't wrapped my head around the code enough to figure it out completely. I moved all the databases out of the data directory to ensure it wasn't a massively corrupted table causing the issue. Even with no dbs, the problem exists. I've also tried --skip-grant-tables and a completely new compile and install. No errors are written and the bin log contains: # at 4 #021101 16:33:50 server id 1 Start: binlog v 1, server v 3.23.52-log created 021101 16:33:50 Google didn't produce any helpful tips and the list archives seem pretty quiet on this problem. Anyone have a clue? -- Kip Turk, RHCE [EMAIL PROTECTED] Systems Administrator/Killer of Spam/Writer of Code/Penguin Proponent West Central Net - tel: 915.234.5678 / 800.695.9016 fax: 915.656.0071 -.-. --- -.. . / -- --- -. -.- . -.-- --..-- / .- -.-. -.- . .-. - 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
MAX_BINLOG_SIZE
I am using MySQL V4.0.2alpha and I have set MAX_BINLOG_SIZE to 2M in order to not have to run a CRON job or something similar to do a FLUSH LOGS intermittently. This should cause the binary log file to be rotated after it has reached 2M according to the documentation. This hasn't been happening as I have binary log files that have reached 240M. It is rotating them but definitely not always at 2M. From what I can work out it seems to rotate the log after it has been idle for a little while and it has an SQL statement that should be written to the binary log. I have been performing load tests on a replicated MySQL database by inserting about 500,000 records into an InnoDB table and the binary log files do not get rotated until after my last insert statement has completed and the database has been idle for a period of time (I have not tested how long it takes) and then I go to do another insert. Is this a 4.0.2alpha feature and is fixed in later versions or is the way it was meant to work in which case the documentation might need a little re-wording. Regards, Michael Ryan __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.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: select using regexp
Hi,
REGEXP is much more powerful than LIKE; you can match full words
with this syntax:
SELECT *
FROM TABLE
WHERE field REGEXP [[::]]cat[[::]];
(Easy, isn't it ? ;) )
You can find more examples in the manual:
http://www.mysql.com/doc/en/Regexp.html
Regards,
Joseph Bueno
NetClub
gerald_clark wrote:
There are too many exceptions for this to be usefull.
What about lines ending in cat. or cat,
Your example won't match them.
Perhaps % cat.% and % cat,% patterns might be more helpfull,
but what about lines that begin with cat?
Peter Lovatt wrote:
Hi
You could use either normal or fulltext searches. Regexp may well be a
good
answer (not used it myself). The following should also work
SELECT *
FROM table
WHERE
field LIKE % cat %
OR field LIKE % cat. %
OR field LIKE % cat, %
(note the spaces to make sure you get only complete words)
or if you have a mysql version that supports FULLTEXT
SELECT * FROM table
WHERE MATCH (field) AGAINST ('cat');
http://www.mysql.com/doc/en/Fulltext_Search.html
which is much more elegant
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
Birmingham
UK
www.sunmaia.net
tel. 0121-242-1473
International +44-121-242-1473
---
-Original Message-
From: Mark Goodge [mailto:mark;good-stuff.co.uk]
Sent: 04 November 2002 11:21
To: [EMAIL PROTECTED]
Subject: select using regexp
Hi,
I've got a problem that I'm hoping someone can help with. I need to do
a query against a text column in order to extract entries that will
match whole words only - for example, a search for cat should match
any of:
The cat sat on the mat
It was a large cat.
Cat food is interesting.
Dog. Cat. Fish.
but not match
in a catatonic state
it was a catastrophe
scattergun approach
It looks as if the MySQL REGEXP function is what I need here, but I
can't work out from the documentation how to get what I want.
Any suggestions?
Mark
-
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
[Error] winmysqladmin.exe - Entry Point not Found
I have the following problem after installing MySql version 3.23.53-max on the Windows NT Server. 1. When I start winmysqladmin.exe, I get a error as follows, - Entry Point not found. The procedure entry point CreateToolhelp32Snapshot could not be located in the dynamic library KERNEL32.DLL. My System Configuration Server : Windows NT Server RAM: 256 MB HDisk : 20 GB (partitioned C-4GB, D-16GB) Major S/Ws : Oracle 8i Microsoft Visual Studio Version 6 I am not getting ant errors when it is run on windows 98 / windows Me. Please Let me know the solution for the above problem. Regards, Sujith S Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in Change the way you talk. Indiatimes presents Valufon, Your PC to Phone service with clear voice at rates far less than the normal ISD rates. Go to http://www.valufon.indiatimes.com. Choose your plan. BUY NOW. - 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: Distributing table-files to other machines
You can move the files and then set up symlinks to them in the /mysql/data/dbName directory. Arthur -Original Message- From: Michelle de Beer [mailto:michelledebeer;yahoo.com] Sent: Monday, November 04, 2002 8:21 AM To: [EMAIL PROTECTED] Subject: Distributing table-files to other machines At the moment, the files for tables in a database are all stored in mysql/data/databasename/. Is it possible to distribute the table-files to different discs, or even better, different machines, and still keep the flexability of having everything in one database. Example 1: Prefs: 1 Database 5 tables 5 discs Explanation: Put the first table on the same DISC as the database. The other tables have one DISC each. Example 2: Prefs: 1 database 5 tables 5 servers Explanation: Put the first table on the same SERVER as the database. The other tables have one SERVER each. Any thoughts? // Michelle __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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 - 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
New to MySQL, ported from MS Access. Using ASP MyODBC - UPDATE problem...
Hi, I've recently made the move over from Access to MySQL. So far, everything has been fine. I'm using: Win2K server (ASP) WinMySQLadmin 1.4, MyCC 0.8.4-alpha, MyODBC 3.51 and MySQL 3.23.51-nt First problem: (fixed) -- I had used some keywords as table names, but SQL in a Nutshell by O'Reilly put me on the straight track. Second problem: (also fixed) Then I had problems using this line: % If MyRS.recordcount 0 Then %... I discovered that under ASP, the recordcount is -1, so I worked around this by using the following line: % If MyRS.EOF = False Then %. All was fine, and exporting data straight from Access2000 into MySQL was easy. I then found one bizarre problem: Third Problem: (Circumvented) - I could edit any old record set - originally created on Access, but not newly created ones! Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Query-based update failed because the row to update could not be found. I spent quite some time looking for information on how to fix this, and drew a blank. I can display all the info from the new record set, but I can't update it... ..that is until I tried a different way to update it: Old way: 1) Open connection (sql=select * from mytablename where UserID = '1') 2) MyRS(UserName) = New Value 3) MyRS.Update '-- Error occurred at this line 4) MyRS.Close New way: 1) Open connection with update directly in sql statement: (sql=UPDATE mytablename SET UserName = 'New Value' WHERE UserID = '1') So... here's my questions to you knowledgeable lot: a) Why did the second method work, and not the first? - is this a MyODBC bug? b) Why is this only happening to NEW rows in the database? - is it a MyODBC bug? c) could I gain/fix anything by changing over to MySQL 4 ? d) how do YOU normally update MySQL records from within ASP? Many thanks for any insight. As I said, I've made it work, so I'm not too troubled, but I would like to code things in the 'standard' way. Luis. - 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
DeadLock bug using mysql/Innodb
Description:
Hello,
I have a deadlock problem when I try to execute some update sentences
using 'Lock For Update'.
I need to create a new document which is identified by a unique number,
which is stored in the field 'Ped' of the table 'Pedidos'.
To obtain a new document number I add 1 to the counter of the last document,
and the counter has to begin with a character set formed by 4 characters.
For example, I want to obtain the last document number which begins
by 'CA02'. The steps I must follow are:
- I obtain the last document which begins by 'CA02'. I do the selection of
the last document using 'Select ... For Update':
Select Ped From Pedidos Where Ped'CA02' And Ped'CA02Z'
Order by Ped Desc Limit 1 For Update
Now, I have the last document which begins by 'CA02' and I don´t
allow anyone to access to this record because I set an exclusive lock using
'For Update'.
If other user tries to create a new document with begins with the same charater
set 'CA02', he must execute the same above sentence and this user remains locked.
But when the first user tries to insert the new document with the new number,
it obtains the following error:
Error 1213: Deadlock found When trying to get lock; try restarting transaction
InnoDB executes a rollback sentence for the first user and the second user
is unlocked.
I will explain the sentences executed with the client of mysql 4.0.4:
User 1 User 2
-- --
Begin; Begin;
Select Ped from Pedidos Select Ped from Pedidos
where Ped'CA02' where Ped'CA02'
and Ped'CA02Z' and Ped'CA02Z'
order by Ped desc limit 1order by Ped desc limit 1
for update; for update;
+++CA02000155 ---locked
Insert into Pedidos(ped)
values('CA02000156');
Error 1213 Deadlock found. Unlocked...
It seems that the user 2 locks user 1, but it should not be because user 2 is actually
locked by user 1.
On the other hand, the following sentences work fine:
User 1 User 2
-- --
Begin; Begin;
Select Ped from Pedidos Select Ped from Pedidos
wherewhere
left(Ped,4)='CA02' left(Ped,4)='CA02'
order by Ped descorder by Ped desc
limit 1 for update; limit 1 for update;
+++CA02000155 ---locked
Insert into Pedidos(ped)
values('CA02000156');
Ok.
Commit; ---unlocked
I have sent to ftp://support.mysql.com/pub/mysql/secret the table definition
and data (Pedidos.txt) in a compressed file named Pedidos.zip so you can
reproduce the bug.
Thanks in advance,
Rafa
How-To-Repeat:
Select Ped from Pedidos where Ped'CA02' and Ped'CA02Z' order by
Ped DESC limit 1 for update
Fix:
-
Synopsis:Subject:DeadLock bug using mysql/Innodb
Submitter-Id: submitter ID
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release:mysqld 4.0.4 beta(InnoDB)
Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System:Windows 2000
Compiler: -
Architecture: i
__
The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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
newbie: SQL question
Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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 and binary data
The problem is that you are starting mysql with the wrong arguments. I think you need to set the max_allowed_packet parameter to something larger than the default 1 M. I got mine to do that by using this command: safe_mysqld --user=mysql -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M -Borries I am using Qt 3.0.5's QSql database classes to store information in a MySQL database. MySQL is at version 3.23.49. I am trying to store binary data into a column marked as MEDIUMBLOB (or even LONGBLOB) but am having difficulties. Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. The data I am trying to insert is an exectuable application, although I have tried it with a normal text file and have run into the same problems. Can anyone shed some light onto what could be causing this problem? Thanks!! Steve Nakhla [EMAIL PROTECTED] __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ -- List archive and information: http://lists.trolltech.com/qt-interest/ - 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 compile problem under Solaris 9
I have tried compiling the latest 3.23 version of mysql under Solaris 9 using gcc 3.2 and keep getting the following error: g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_RWLOCK_T -c sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX*, unsigned int, bool)': sql_lex.cc:168: `get_hash_symbol' undeclared (first use this function) make[3]: *** [sql_lex.o] Error 1 make[3]: Leaving directory `/opt/build/mysql-3.23.53/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/opt/build/mysql-3.23.53/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/opt/build/mysql-3.23.53' make: *** [all-recursive-am] Error 2 I have found other postings with this same issue through google searches, however, I never came across a resolution. Here are my configure options: CFLAGS=-O6 ./configure --prefix=/usr/local/mysql --with-low-memory Any sugguestions? Thanks! Andy - 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: newbie: SQL question
http://hotwired.lycos.com/webmonkey/backend/databases/ might be a good place, haven't read it, but I send people that want to learn HTML to webmonkey, and they liked it. -Original Message- From: Admin-Stress [mailto:meerkapot;yahoo.com] Sent: Monday, November 04, 2002 10:50 AM To: [EMAIL PROTECTED] Subject: newbie: SQL question Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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 - 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: safe_mysqld crashing
On Mon, 4 Nov 2002, Alston, Tony wrote: Kip, Do you use RedHat's 'up2date' program? No, but it was a problem with glibc (sort of) anyhow. The problem is resolved. For posterity, here's what we did. 1.) Per the bug report at https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=75128, I added 'set-variable = thread_stack=256k'. This was enough to resolve the issue. 2.) We also found that the grub.conf hadn't been properly edited to boot the kernel we wanted. Most of our problem seems to have been due to issues between the kernel (2.4.18-3smp) and glibc (2.2.5-40) when it came up after the reboot. I intend to test the new glibc (ftp://people.redhat.com/jakub/glibc/errata/) at my next maintenance window and will post an update if anything further changes. - sql to bypass the filter - -- Kip Turk, RHCE [EMAIL PROTECTED] Systems Administrator/Killer of Spam/Writer of Code/Penguin Proponent West Central Net - tel: 915.234.5678 / 800.695.9016 fax: 915.656.0071 -.-. --- -.. . / -- --- -. -.- . -.-- --..-- / .- -.-. -.- . .-. - 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: explain bug?
Robert, Monday, November 04, 2002, 12:39:34 AM, you wrote: RGp I've tried an EXPLAIN of the SELECT below. RGp Please can anyone tell me why it's RGp Impossible WHERE noticed after reading const tables RGp ? RGp I've droped and created the table - but the error/message occurs. RGp When I leave '(chetPosition = 1)' away, it works! RGp A bug or ??? [skip] RGp mysql explain RGp - SELECT * FROM hmChartEntry RGp - WHERE (ctId = 1) AND RGp - (chSubId = 1) AND RGp - (chetPosition = 1) RGp - ; RGp +-+ RGp | Comment | RGp +-+ RGp | Impossible WHERE noticed after reading const tables | RGp +-+ RGp 1 row in set (0.00 sec) [skip] It's not a bug, it means there are no rows that satisfy the conditions in WHERE clause. -- 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 - 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: connecting a remote mysql-server always fails
Jochen, Saturday, November 02, 2002, 3:49:18 PM, you wrote: JK when I try to connect to a remote computer running mysql JK I always get the following error on the remote machine: JK Number of processes running now: 1 JK mysqld process hanging, pid 7262 - killed JK 021029 21:28:13 mysqld restarted JK /usr/local/libexec/mysqld: ready for connections JK mysql -uroot -hxxx.xxx.xxx.xxx -p JK When I use PHP and the according sql commands I get the same JK results. JK Isn't this the right command? JK Even when I setup phpmyadmin to show me different hosts running JK mysql I only can connect to the local machine running mysql. JK Any tips? This problem is related to RedHat and glibc: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=75128 Check also mailing list archives, there were a lot of similar posts. -- 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 - 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: Select by row number
horizonx, Saturday, November 02, 2002, 2:43:07 PM, you wrote: There is no row number in MySQL. You should use ORDER BY to retrieve data in particular order. handf What about the following info in the manual p. 55? handf Add a PRIMARY KEY or UNIQUE key to the table and use this. New in Version handf 3.23.11: If the PRIMARY or UNIQUE key consists of only one column and this handf is handf of type integer, one can also refer to it as _rowid. It's not an internal or physical number (If I've got you right you asked about internal row number). -- 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 - 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: ORDER BY and charset iso-8859-2 and windows-1250
Bc., Monday, November 04, 2002, 12:14:53 PM, you wrote: BRK I have problem with ORDER BY in MySQL if I have some characters in iso-8859-2 BRK and windows-1250. What I should do for fix it. Set up corresponding default-character-set in my.cnf. -- 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 - 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: Problem in decimal separator
Juan_Andres_Torregrosa, Monday, November 04, 2002, 1:44:11 PM, you wrote: Jaedddc I have a problem when I load a text file using the sentence load data Jaedddc infile as long as decimals are separated by commas and mysql ignores those Jaedddc decimals, is there any way to specify that decimal separator is comma or Jaedddc point? No, you can't. -- 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 - 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: Newbie question: Storing text
Paul, Saturday, November 02, 2002, 5:11:43 PM, you wrote: P I am kind of new to MySQL. I have ordered MySQL Cookbook but I still P have a week before it arrives. In the meantime, I am looking for some P basic help. P I am looking to store the text/description for the purpose of retrieving P it on the website. What would be the most appropriate way to store it? P Text or long varchar (something like varchar(1000)? P Which field supports full text search? P Hob about if I want to store the description that is html formatted? P Would that choice differ (TEXT/VARCHAR)? There is no long varchar in MySQL. You can find the maximum length for all column types here: http://www.mysql.com/doc/en/Column_types.html Full-text search is supported on VARCHAR() and TEXT columns. -- 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 - 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: mysqladmin missing
aman, Saturday, November 02, 2002, 6:18:58 PM, you wrote: am If I have an old copy of Mysql on the system and want to uninstall that, how am would I make sure that all the database associated should get deleted, or do am I do it manually? If you uninstall MySQL server, data directory will not be deleted. -- 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 - 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 DATETIME substraction problem/?
Jim, Sunday, November 03, 2002, 8:44:43 PM, you wrote: JH I am working on an analysis that is very much dependent on calculating JH time differences in seconds. The most simple example: JH I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ JH seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS JH as expected and actual differences between time_end and time_begin are JH usually on the order of 20-120 seconds. JH I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; JH When I browse the resulting data, the elapsed seconds often do, but do JH not always, agree with my own calculation of the elapsed seconds. For JH example, the first 12 rows look like: JH My calc elapsed_time JH 45 85 JH 11 11 JH 16 16 JH 9 9 JH 22 62 JH 14 14 JH 73 73 JH 59 99 JH 65 105 JH 20 20 JH 12 12 JH 43 4083 JH That last one is a bit off!! There appeared to be a common problem with a JH 40-second difference, but obvioulsy that last one throws that pattern out JH the window! JH Anyhow, I have Googled quite a bit and have looked through JH http://www.mysql.com/doc/en/Date_and_time_functions.html several times and JH did not see a different function of syntax for the UPDATE that I want to JH do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 JH I can simply use +/- operators JH By way of troubleshooting, I created 6 other elapsed_time variables using JH small, medium and big int, and float, double and decimal to see if the JH result was different, but the result was the same for each. You can't apply arithmetic directly on DATETIME columns. You should use TIME_TO_SEC(time_end)-TIME_TO_SEC(time_begin) -- 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 - 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: newbie: SQL question
Try this: http://www.devshed.com/Server_Side/MySQL - Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot - 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
NO PROBLEM: Re: mysql DATETIME substraction problem/?
On Mon, 4 Nov 2002, gerald_clark wrote: You can't just subtract datetime fields. Check the date and time functions in the manual. Thanks, I should know I run the risk of posting a RTFM/FAQ when I'm tired! I got off track by misinterpreting In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() in the manual when all I needed was UNIX_TIMESTAMP() (that I misinterpreted when I looked at it the first time). Anyhow, I can *nearly* just subtract datetime fields and all is right with the world. Jim On Mon, 4 Nov 2002, gerald_clark wrote: You can't just subtract datetime fields. Check the date and time functions in the manual. Jim Hogan wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like: My calc elapsed_time 45 85 11 11 16 16 99 22 62 14 14 73 73 59 99 65 105 20 20 12 12 43 4083 That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window! Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it. - 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
Missing my.cnf file
Having some other folks helping me out here with this MySQL problems that I am having. I have found out that I am missing the my.cnf from the /etc directory and I am trying to create the file from scratch, this is what I have so far and also MySQL is installed from .tgz in this directory. /usr/local/MySQL/mysql-3.23.52-pc-linux-gnu-i686 Here is the what I have so far for my.cnf, please advise if this is right or not #missing this config. file for MySQL [mysql] datadir=/usr/local/MySQL/mysql-3.23.52-pc-linux-gnu-i686/bin socket=/tmp/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysql] err-log=/var/log/mysql.log #I do not show this file in /var/log nor do I show a mysql.log in the MySQL directory pid-file=/var/run/mysqld/mysqld.pid #I also do not show this under /var/run * thanks again -Chris = * GAIM ID: cmmiller1973 * __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.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: Newbie question: Storing text
At 19:15 +0200 11/4/02, Egor Egorov wrote: Paul, Saturday, November 02, 2002, 5:11:43 PM, you wrote: P I am kind of new to MySQL. I have ordered MySQL Cookbook but I still P have a week before it arrives. In the meantime, I am looking for some P basic help. P I am looking to store the text/description for the purpose of retrieving P it on the website. What would be the most appropriate way to store it? P Text or long varchar (something like varchar(1000)? P Which field supports full text search? P Hob about if I want to store the description that is html formatted? P Would that choice differ (TEXT/VARCHAR)? There is no long varchar in MySQL. You can find the maximum length for all column types here: http://www.mysql.com/doc/en/Column_types.html Full-text search is supported on VARCHAR() and TEXT columns. And CHAR(). -- 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 - 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: help create new user
Jeremy Zawodny wrote: On Mon, Nov 04, 2002 at 12:54:17AM -0500, Bob Lockie wrote: That fixed it, thanks. What did it use since I didn't specify an @'localhost'? It used '%' which matches everyhing *except* localhost. The manual explains this. Jeremy It's not obvious in the MySQL manual. I did a search. -- From Mozilla and GNU/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: Problems with ORDER BY from C API (works from command-line tool mysql.exe)
Hmm, sorry, but it works! Found the problem, it was all my misstake. It
was my win32 dialoglist that was sorted :-(
Thanks!
/Christer
-Original Message-
From: Christer Holmström (at Home) [mailto:Christer.Holmstrom;home.se]
Sent: den 4 november 2002 15:59
To: 'gerald_clark'
Cc: 'Joseph Bueno'; [EMAIL PROTECTED]
Subject: RE: Problems with ORDER BY from C API (works from
command-line tool mysql.exe)
The code works in that sence that I get resultsets, the only thing that
does not work is the ORDER BY option for the SELECT statement. The
resultset is ordered by the exe column instead.
Should I run MyODBC instead?
Is the sourcecode for mysql.exe available?
Is there any miss-usage of mysql_query, mysql_store_result etc?
/Christer
void StatisticsToday()
{
charszSQL[1024];
MYSQL_RES* res;// Resultset
MYSQL_ROW row;// Row
int i, j;
// Tried both below
strcpy(szSQL, SELECT DISTINCT exe, sum(duration) AS s FROM data
GROUP BY exe ORDER BY s DESC);
strcpy(szSQL, SELECT DISTINCT exe, sum(duration) FROM data
GROUP BY exe ORDER BY 2 DESC;);
if (!mysql_query( myData, szSQL)) {
res = mysql_store_result( myData ) ;
i = (int) mysql_num_rows( res ) ;
while ( row = mysql_fetch_row( res ) )
{
j=mysql_num_fields( res );
sprintf(szId, %s %s, row[0], row[1]);
}
mysql_free_result( res ) ;
} else {
printf( Couldn't execute %s on the server !\n, szSQL )
;
}
}
-Original Message-
From: gerald_clark [mailto:gerald_clark;suppliersystems.com]
Sent: den 4 november 2002 15:51
To: Christer Holmström (at Home)
Cc: 'Joseph Bueno'; [EMAIL PROTECTED]
Subject: Re: Problems with ORDER BY from C API (works from
command-line tool mysql.exe)
Then I would suggest you check your C code.
The program 'mysql' uses the C API.
Christer Holmström (at Home) wrote:
Hi, and thanks for the response, but unfortunately the same problem
with both below
SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 DESC;
SELECT exe, sum(duration) AS s FROM data GROUP exe BY ORDER BY s DESC;
Works from mysql.exe but not from C API.
/Christer
-Original Message-
From: Joseph Bueno [mailto:joseph.bueno;trader.com]
Sent: den 4 november 2002 11:57
To: Christer Holmström (at Home)
Cc: [EMAIL PROTECTED]
Subject: Re: Problems with ORDER BY from C API (works from
command-line tool mysql.exe)
Christer Holmström (at Home) wrote:
Hi!
This SQL works perfect when running the command line tool mysql.exe
SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2
BUT when running from the C API, the ORDER BY part doesn't seem
active, why?
Please help.
/Christer
Hi,
What do expect with ORDER BY 2 ?
If you want to arder by sum(duration) you should use:
SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s
Hope this helps
-
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
Size
I and my friend will do a test with SQL Server, Oracle and MySQL We´ll start with a table with 2.000.000 rows. What´s the best table type to do it? I thought to do in InnoDB but how much do I set the size? Maybe 200MB? Regards Alexander sql, query ___ Yahoo! Encontros O lugar certo para encontrar a sua alma gêmea. http://br.encontros.yahoo.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: New to MySQL, ported from MS Access. Using ASP MyODBC - UPDATEproblem...
Luis Sanches wrote: Hi, I've recently made the move over from Access to MySQL. So far, everything has been fine. I'm using: Win2K server (ASP) WinMySQLadmin 1.4, MyCC 0.8.4-alpha, MyODBC 3.51 and MySQL 3.23.51-nt First problem: (fixed) -- I had used some keywords as table names, but SQL in a Nutshell by O'Reilly put me on the straight track. Second problem: (also fixed) Then I had problems using this line: % If MyRS.recordcount 0 Then %... I discovered that under ASP, the recordcount is -1, so I worked around this by using the following line: % If MyRS.EOF = False Then %. All was fine, and exporting data straight from Access2000 into MySQL was easy. I then found one bizarre problem: Third Problem: (Circumvented) - I could edit any old record set - originally created on Access, but not newly created ones! Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Query-based update failed because the row to update could not be found. I spent quite some time looking for information on how to fix this, and drew a blank. I can display all the info from the new record set, but I can't update it... ..that is until I tried a different way to update it: Old way: 1) Open connection (sql=select * from mytablename where UserID = '1') 2) MyRS(UserName) = New Value 3) MyRS.Update '-- Error occurred at this line 4) MyRS.Close New way: 1) Open connection with update directly in sql statement: (sql=UPDATE mytablename SET UserName = 'New Value' WHERE UserID = '1') So... here's my questions to you knowledgeable lot: a) Why did the second method work, and not the first? - is this a MyODBC bug? b) Why is this only happening to NEW rows in the database? - is it a MyODBC bug? c) could I gain/fix anything by changing over to MySQL 4 ? d) how do YOU normally update MySQL records from within ASP? Many thanks for any insight. As I said, I've made it work, so I'm not too troubled, but I would like to code things in the 'standard' way. You just need a correct OPTION value passed to the connect string or setting them in the DSN configuration. Check the following code, which has .update, .addnew and .delete methods from ADO: http://www.mysql.com/products/myodbc/manual.html#MyODBC_With_VB -- Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306 ___/ www.mysql.com USA - 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: BETWEEN
sql,query Would it be possible to supply BETWEEN in any order (or rather to allow to do that)? Such as WHERE timestamp BETWEEN 2002100100 AND 20021031235959 would get the same results with WHERE timestamp BETWEEN 20021031235959 AND 2002100100 or any other case where BETWEEN can be used it really is BETWEEN those values wouldn't that be just a simple greater than check - 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 compile problem under Solaris 9
I've found that previous build-attempts that have failed (IE did you have a bad/nonexistant LD_LIBRARY_PATH, fix it, and then try 'make' again?) cause this, and even a make distclean doesn't fix it. Try starting from a freshly untarred source ... that's always done it for me. -=| Ben - Original Message - From: Andy Elmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 04, 2002 10:58 AM Subject: mysql compile problem under Solaris 9 I have tried compiling the latest 3.23 version of mysql under Solaris 9 using gcc 3.2 and keep getting the following error: g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_RWLOCK_T -c sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX*, unsigned int, bool)': sql_lex.cc:168: `get_hash_symbol' undeclared (first use this function) make[3]: *** [sql_lex.o] Error 1 make[3]: Leaving directory `/opt/build/mysql-3.23.53/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/opt/build/mysql-3.23.53/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/opt/build/mysql-3.23.53' make: *** [all-recursive-am] Error 2 I have found other postings with this same issue through google searches, however, I never came across a resolution. Here are my configure options: CFLAGS=-O6 ./configure --prefix=/usr/local/mysql --with-low-memory Any sugguestions? Thanks! Andy - 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 Berkeley DB
Hello Everyone, I have been going through the documentation on MySQL and on Berkeley DB looking for an answer for my question for several weeks and have yet to come up with one, so I figured I post it. I have a situation where the customer that I have wants to use their current Berkeley DB, although my system needs the SQL and schema definition that MySQL offers. Is there any way to apply these layers to the current Berkeley DB implementation by using MySQL or portions of it? I have to send out the proposal by tomorrow, so if anyone could answer me on this ASAP, that would be great! Wiley - 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: Cannot Log into MySQL
If you have installed MySql in some non-standard location, or if the original installation was from the OS install, it may be that you need to link the socks. I have done this, others will probably complain. I had this problem on many systems newly installed. I fixed it with ln -s /var/lib/path/to/mysql.sock /tmp/mysql.sock ~Kelly W. Black -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Friday, November 01, 2002 6:26 PM To: CM Miller; [EMAIL PROTECTED] Subject: Re: Cannot Log into MySQL At 16:27 -0800 11/1/02, CM Miller wrote: Still workin' at it here. Ok, I've added to my PATH /usr/local/MySQL/mysql-3.23.-52-pc-linux-gnu-i686/bin and at least I'm getting this now $ mysql Error 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) Sounds like the server isn't running. I'm not sure what it is trying to tell me, btw, I'm running MySQL on the same machine that I'm trying to log into, so I don't know if this makes a difference or not. thanks again -Chris - 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: mysql dieing
Typically if your OS runs, the mysql will too. You should only be worried with hardware if you are running large database apps on a slow system. -Original Message- From: Stephen Hitchner [mailto:steve;number41media.com] Sent: Friday, November 01, 2002 9:40 PM To: Mysql Subject: mysql dieing Hi I'm new to this list so forgive me if I break some rules with this first post. I have been receiving random mysql errors over the past few days where the server just dies. The server dies at various times, under various loads. I have included as much information as I can from various places on the server. I spent a bit of time looking around for possible known errors with little luck. I think I have narrowed this problem to hardware, but I was just wondering if anyone has experienced this. I'm using: FreeBSB 4.6 Mysql 3.23.51 Any help would be great! cheers steve / * mysqlbug */ From: kosh To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Number41Media Admin Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.51-max (Official MySQL-max binary) Environment: machine, os, target, libraries (multiple lines) System: FreeBSD mail-sql.x.com 4.6-RELEASE FreeBSD 4.6-RELEASE #0: Tue Jun 11 06:14:12 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys /compile/GENERIC i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions - fno-r tti' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 132 Jun 10 21:18 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Aug 21 05:27 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 579412 Jun 10 21:18 /usr/lib/libc.so.4 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL-max binary' --with-extra-charsets=complex --with-server-suffix=-max -- enable-thread-safe-client --enable-local-infile --enable-assembler --with-na med-z-libs=not-used --disable-shared --with-berkeley-db --with-innodb 'CFLAGS=-O3 -fno-omit-frame-pointer' CXX=gcc CC=gcc 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions - fno-rtti' Perl: This is perl, version 5.005_03 built for i386-freebsd /*** * mysql mail-sql.x.err file */ mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist 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=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=16 max_connections=100 threads_connected=5 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 021101 21:03:55 mysqld restarted 021101 21:06:49 bdb: Log sequence error: page LSN 70:7230244; previous LSN 375 1164233 021101 21:06:50 bdb: Log sequence error: page LSN 70:7378020; previous LSN 380 8564133 021101 21:06:50 bdb: Log sequence error: page LSN 70:7621572; previous LSN 383 3442355 021101 21:07:34 bdb: ./rogers_mail_berkeley_ah/customer_recipients.db: close: 1 blocks left pinned 021101 21:07:34 bdb: ./rogers_mail_berkeley_ah/customers.db: close: 2 blocks left pinned mysqld in free(): warning: chunk is already free 021101 21:07:34 Can't init databases 021101 21:07:34 mysqld ended /*** * My Mysql Log */ 01Nov2002 21:04:05 SELECT: SELECT customers.id as id, customers.account as account, customers.salutation as salutation, customers.firstname as firstname, customers.lastname as lastname, customers.address1 as address1, customers.address2 as address2, customers.city as city, customers.postalcode as
RE: rounding behavior
Use the ROUND statement ROUND(sum(CLICKS),sum(IMPS)/sum(CLICKS)*100.0,0),2); -Original Message- From: David Garamond [mailto:davegaramond;icqmail.com] Sent: Sunday, November 03, 2002 2:31 AM To: [EMAIL PROTECTED] Subject: rounding behaviour hi, mysql select 1/29; +--+ | 1/29 | +--+ | 0.03 | +--+ 1 row in set (0.00 sec) mysql select 100.0*1/29; ++ | 100.0*1/29 | ++ | 3.448 | ++ 1 row in set (0.00 sec) mysql select 1/29*100.0; ++ | 1/29*100.0 | ++ | 3.45 | ++ 1 row in set (0.00 sec) i am slightly puzzled by mysql's behaviour in the first case. this seems to be a float division, but why does mysql rounds it to two digits after decimal? if this is supposed to be an integer division, wouldn't it be better/more predictable for mysql to return 0? this behaviour could cause subtle problem/errors because people really didn't expect this kind of behaviour. for example, i just found out today, after weeks of operation, that my sql expression: SELECT ...,if(sum(CLICKS),sum(IMPS)/sum(CLICKS)*100.0,0) as CTR FROM T generates CTR that are rounded to two digits after decimal (yes, IMPS and CLICKS are integer fields). however, after i change the expression to this: SELECT ...,if(sum(CLICKS),100.0*sum(IMPS)/sum(CLICKS),0) as CTR FROM T all is well. -- dave - 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
Q about replication timing
I'm looking into options to improve performance of mysql in our app and have a question about replication. I need the following guarantee, which I believe is not possible with replication ... If a client successfully updates the master, any subsequent read from a slave must reflect that update. Or to put it negatively, it must be impossible for a client to update the master and subsequently read an old (pre-update) value from the slave. Am I correct in assuming that replication can not make this guarantee? -- Ray Zimmerman / e-mail: [EMAIL PROTECTED] / 428-B Phillips Hall Sr Research / phone: (607) 255-9645 / Cornell University Associate / FAX: (815) 377-3932 / Ithaca, NY 14853 - 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
Maintaining #'s between 2 databases
I have two databases (rt2 jobs) and within each of these DBs tables that autoincrement email Ticket #'s and Work Request #'s respectively. I'm looking for the best way to maintain the numbers between the databases (e.g. if I had a rt2, jobs, then rt2 requests: rt2 issues ticket #1024, then jobs issues #1025, then rt2 issues #1026) More information: rt2 is the Request Tracker email ticketing system (http://www.bestpractical.com/rt) which uses Perl to insert tickets and jobs is a custom PHP front end for inserting tickets into a separate database (living on the same server, using same the mysql install) A messy solution would be one big database and one big table that uses the same autoindex key. I appreciate ideas on a better solution (e.g. Cross inserts?, a 3rd index table)? Thanks, Mike - 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 shared library.
Hi, I'm trying to access mysql shared library. mysql_init() function returns the address of MYSQL structure . I need to know what the structure looks like.? can all mysql structure parameters be configured in /etc/my.cfg ? 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
Interesting Challenge
Hi sql query wizards! I need some help. Is there a way I can take this query here = mysql SELECT cell, sector, - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell - HAVING sector=1 AND (cell=148 or cell=3); +--++--+--+--+---+--+--+--+--+-- --+--+ | cell | sector | att | lc | csh | drops | tccf | bpp | bpc | suf | blocks | mou | +--++--+--+--+---+--+--+--+--+-- --+--+ |3 | 1 | 734 | 12 |6 | 2.52 | 21 |0 |0 |0 | 2.86 | 1501 | | 148 | 1 | 2746 | 93 | 30 | 4.59 | 63 |0 |0 |1 | 2.33 | 4672 | +--++--+--+--+---+--+--+--+--+-- --+--+ And have it display the two rows as a total sum together in one row? I have been struggling with this and could really use some help. Thanks in advance for any ideas. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? - 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: Q about replication timing
On Mon, Nov 04, 2002 at 02:04:35PM -0500, Ray Zimmerman wrote: I'm looking into options to improve performance of mysql in our app and have a question about replication. I need the following guarantee, which I believe is not possible with replication ... If a client successfully updates the master, any subsequent read from a slave must reflect that update. Or to put it negatively, it must be impossible for a client to update the master and subsequently read an old (pre-update) value from the slave. Am I correct in assuming that replication can not make this guarantee? Yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 90 days, processed 1,877,572,814 queries (240/sec. avg) - 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
(beginner) mysql connection problem!
Hi everyone, On my local machine I had mysql installed, and I was able to log in and doing a test on it. but today as I am trying to login using mysql or mysql -u root -p, I got the error message saying; ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), I had not touch the setting of the mysql at all. What should I do to solve this problem?? Thank you all for your help. - 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
Scripts/mysql_install_db error
Any suggestions?? I just installed and compiled mysql 3.23.53 and I get
this error:
[root@Alpha mysql-3.23.53]# scripts/mysql_install_db
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked agaist 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=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80379
K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x8084786
0x8131728
0x8147d56
0x808bc51
0x808e15e
0x8086ed3
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instr
uctions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x825a810 = CREATE TABLE db ( Host char(60) binary
DEFAULT '' N
OT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16)
binary DEFAUL
T '' NOT NULL, Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Insert_priv e
num('N','Y') DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') DEFAULT
'N' NOT N
ULL, Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL, Create_priv
enum('N','Y
') DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Grant
_priv enum('N','Y') DEFAULT 'N' NOT NULL, References_priv
enum('N','Y') DEFAUL
T 'N' NOT NULL, Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Alter_priv en
um('N','Y') DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY
User (Use
r) ) comment='Database privileges'
thd-thread_id=1
Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 1 did to cause the crash. In some cases of
really
bad corruption, the values shown above may be invalid
The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash
Installation of grant tables failed!
Examine the logs in /usr/local/mysql/var for more information.
You can also try to start the mysqld daemon with:
/usr/local/mysql/libexec/mysqld --skip-grant
You can use the command line tool
/usr/local/mysql/bin/mysql to connect to the mysql
database and look at the grant tables:
shell /usr/local/mysql/bin/mysql -u root mysql
mysql show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /usr/local/mysql/var that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com
Please consult the MySQL manual section: 'Problems running
mysql_install_db',
and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the /usr/local/mysql/bin/mysqlbug
script!
---
Aaron Axelsen
AIM: AAAK2
Email: [EMAIL PROTECTED]
URL: www.amadmax.com
It said, Insert disk #3, but only two will fit!
One picture is worth 128K words.
-
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: Interesting Challenge
mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SELECT. - 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: (beginner) mysql connection problem!
Sounds like mysql service hasn't started yet. -Original Message- From: David Wu [mailto:dwu;stepup.ca] Sent: Monday, November 04, 2002 2:59 PM To: [EMAIL PROTECTED] Subject: (beginner) mysql connection problem! Hi everyone, On my local machine I had mysql installed, and I was able to log in and doing a test on it. but today as I am trying to login using mysql or mysql -u root -p, I got the error message saying; ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), I had not touch the setting of the mysql at all. What should I do to solve this problem?? Thank you all for your help. - 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: Maintaining #'s between 2 databases
create a sequence table. (Possibly in a third database?) sequence sequenceID bigint label varchar(25) NextValue bigint Put 1 record in the table for each autoincrement field using the fieldname as the label. Then use it instead of the built-in autoincrement fields. begin; select @nextID:=NextValue from sequence; update sequence set NextValue = (NextValue+1); commit; ADODB does this (php.weblogs.com) but they create 1 table for each sequence. (for locking purposes) if you are using InnoDB tables (and why WOULDN'T you be?) then you can easily modify their code to use a single sequence table. (I did) HTH, =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Mike Patterson [mailto:mp72;excite.com] Sent: Monday, November 04, 2002 1:19 PM To: [EMAIL PROTECTED] Subject: Maintaining #'s between 2 databases I have two databases (rt2 jobs) and within each of these DBs tables that autoincrement email Ticket #'s and Work Request #'s respectively. I'm looking for the best way to maintain the numbers between the databases (e.g. if I had a rt2, jobs, then rt2 requests: rt2 issues ticket #1024, then jobs issues #1025, then rt2 issues #1026) More information: rt2 is the Request Tracker email ticketing system (http://www.bestpractical.com/rt) which uses Perl to insert tickets and jobs is a custom PHP front end for inserting tickets into a separate database (living on the same server, using same the mysql install) A messy solution would be one big database and one big table that uses the same autoindex key. I appreciate ideas on a better solution (e.g. Cross inserts?, a 3rd index table)? Thanks, Mike - 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
Scripts/mysql_install_db error
Any suggestions?? I just installed and compiled mysql 3.23.53 and I get
this error:
[root@Alpha mysql-3.23.53]# scripts/mysql_install_db
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked agaist 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=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80379
K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x8084786
0x8131728
0x8147d56
0x808bc51
0x808e15e
0x8086ed3
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instr
uctions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x825a810 = CREATE TABLE db ( Host char(60) binary
DEFAULT '' N
OT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16)
binary DEFAUL
T '' NOT NULL, Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Insert_priv e
num('N','Y') DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') DEFAULT
'N' NOT N
ULL, Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL, Create_priv
enum('N','Y
') DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Grant
_priv enum('N','Y') DEFAULT 'N' NOT NULL, References_priv
enum('N','Y') DEFAUL
T 'N' NOT NULL, Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Alter_priv en
um('N','Y') DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY
User (Use
r) ) comment='Database privileges'
thd-thread_id=1
Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 1 did to cause the crash. In some cases of
really
bad corruption, the values shown above may be invalid
The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash
Installation of grant tables failed!
Examine the logs in /usr/local/mysql/var for more information.
You can also try to start the mysqld daemon with:
/usr/local/mysql/libexec/mysqld --skip-grant
You can use the command line tool
/usr/local/mysql/bin/mysql to connect to the mysql
database and look at the grant tables:
shell /usr/local/mysql/bin/mysql -u root mysql
mysql show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /usr/local/mysql/var that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com
Please consult the MySQL manual section: 'Problems running
mysql_install_db',
and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the /usr/local/mysql/bin/mysqlbug
script!
---
Aaron Axelsen
AIM: AAAK2
Email: [EMAIL PROTECTED]
URL: www.amadmax.com
It said, Insert disk #3, but only two will fit!
One picture is worth 128K words.
-
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: DeadLock bug using mysql/Innodb
Rafa,
- Original Message -
From: [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Monday, November 04, 2002 6:06 PM
Subject: DeadLock bug using mysql/Innodb
Description:
Hello,
I have a deadlock problem when I try to execute some update sentences
using 'Lock For Update'.
I need to create a new document which is identified by a unique number,
which is stored in the field 'Ped' of the table 'Pedidos'.
To obtain a new document number I add 1 to the counter of the last
document,
and the counter has to begin with a character set formed by 4 characters.
For example, I want to obtain the last document number which begins
by 'CA02'. The steps I must follow are:
- I obtain the last document which begins by 'CA02'. I do the selection of
the last document using 'Select ... For Update':
Select Ped From Pedidos Where Ped'CA02' And Ped'CA02Z'
Order by Ped Desc Limit 1 For Update
Now, I have the last document which begins by 'CA02' and I don´t
allow anyone to access to this record because I set an exclusive lock
using
'For Update'.
If other user tries to create a new document with begins with the same
charater
set 'CA02', he must execute the same above sentence and this user remains
locked.
But when the first user tries to insert the new document with the new
number,
it obtains the following error:
Error 1213: Deadlock found When trying to get lock; try restarting
transaction
InnoDB executes a rollback sentence for the first user and the second user
is unlocked.
I will explain the sentences executed with the client of mysql 4.0.4:
User 1 User 2
-- --
Begin; Begin;
Select Ped from Pedidos Select Ped from Pedidos
where Ped'CA02' where Ped'CA02'
and Ped'CA02Z' and Ped'CA02Z'
order by Ped desc limit 1order by Ped desc limit 1
for update; for update;
+++CA02000155 ---locked
Insert into Pedidos(ped)
values('CA02000156');
Error 1213 Deadlock found. Unlocked...
It seems that the user 2 locks user 1, but it should not be because user 2
is actually
locked by user 1.
On the other hand, the following sentences work fine:
User 1 User 2
-- --
Begin; Begin;
Select Ped from Pedidos Select Ped from Pedidos
wherewhere
left(Ped,4)='CA02' left(Ped,4)='CA02'
order by Ped descorder by Ped desc
limit 1 for update; limit 1 for update;
+++CA02000155 ---locked
Insert into Pedidos(ped)
values('CA02000156');
Ok.
Commit; ---unlocked
I have sent to ftp://support.mysql.com/pub/mysql/secret the table
definition
and data (Pedidos.txt) in a compressed file named Pedidos.zip so you can
reproduce the bug.
thank you for a very detailed study of the problem.
This is not a bug but inoptimality. A waiting next-key lock represents a
cursor which has already started its scan. An ascending cursor might have
scanned the place where the other user is trying to insert.
An example (users run with AUTOCOMMIT=0):
CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a)) TYPE = INNODB;
INSERT INTO t VALUES (90);
INSERT INTO t VALUES (110);
User 1: SELECT * FROM t WHERE a = 100 FOR UPDATE;
User 2: SELECT * FROM t WHERE a = 100 FOR UPDATE;
Now if user 1 tries to do INSERT INTO t VALUES (100), he will receive a
deadlock. The algorithm is that a cursor in InnoDB always travels in one
direction. User 2 has his cursor waiting on the row (110) for an exclusive
row next-key lock. If we allowed user 1 to insert (100), it would be a
'phantom row' in User 2's SELECT: if User 1 committed his transaction, and
User 2 would repeat his own SELECT, a new row (100) would have appeared in
the result set.
A way to fix the inoptimality would be to let User 2 reverse his cursor if
it ends up waiting for a row lock and there is an insert immediately before
that row. But that is a bit complicated.
In your case the cursor probably travels downwards. But the InnoDB lock
table is not aware of the direction the cursor came from.
A general rule with InnoDB index record 'gap' and next-key locks is that
they block inserts by other users but do not give the lock holder the right
to do an insert. Since purge can remove delete marked records, gaps may
merge, and two users may even both hold an X-lock on some gap in the index.
Thanks in advance,
Rafa
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
sql query
How-To-Repeat:
Select Ped from Pedidos where Ped'CA02' and Ped'CA02Z' order by
Ped DESC limit 1 for update
Fix:
-
Synopsis:Subject:DeadLock bug using mysql/Innodb
Submitter-Id: submitter ID
Originator: Rafa
Organization:
Difficult query
Hello, I have a table with only ips in it. I want to get all networks the ips belong to: Example: 123.123.123.1 123.123.132.2 123.123.123.3 1.1.1.4 1.1.1.5 1.1.1.6 12.12.12.1 12.12.12.2 12.12.12.10 The result should be: 123.123.123.0 1.1.1.0 12.12.12.0 Instead of converting on matching row (for example 123.123.123.2) to 123.123.123.0 it can be left as 123.123.123.2 or even can be truncated to 123.123.123 The table is stored on a 3.23.43 mySQL Server. The table type is varchar(15) but this can be changed. Thanks, Alexander Newald - 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 DATETIME substraction problem/?
Hello. On Sun 2002-11-03 at 10:44:43 -0800, [EMAIL PROTECTED] wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators Read that again. It only says that for using INTERVAL, i.e. SELECT 1997-12-31 23:59:59 + INTERVAL 1 SECOND; is allowed instead of SELECT DATE_ADD(1997-12-31 23:59:59, INTERVAL 1 SECOND); By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. [...] That's because your problem does not stem from your storage. Btw, you can easily see that by using SELECT time_end-time_begin FROM mytable LIMIT 20; Regards, Benjamin. -- [EMAIL PROTECTED] - 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: Problems with ORDER BY from C API (works from command-line tool mysql.exe)
Hi. On Mon 2002-11-04 at 11:56:40 +0100, [EMAIL PROTECTED] wrote: Christer Holmström (at Home) wrote: [...] This SQL works perfect when running the command line tool mysql.exe SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 BUT when running from the C API, the ORDER BY part doesn't seem active, why? [...] What do expect with ORDER BY 2 ? That is a shortcut for what you explain below. If you want to arder by sum(duration) you should use: SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s See http://www.mysql.com/doc/en/SELECT.html (look for column position) Regards, Benjamin. -- [EMAIL PROTECTED] - 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: Difficult query
On Mon, 4 Nov 2002, Alexander Newald wrote: Hello, I have a table with only ips in it. I want to get all networks the ips belong to: Example: 123.123.123.1 123.123.132.2 123.123.123.3 1.1.1.4 1.1.1.5 1.1.1.6 12.12.12.1 12.12.12.2 12.12.12.10 The result should be: 123.123.123.0 1.1.1.0 12.12.12.0 Instead of converting on matching row (for example 123.123.123.2) to 123.123.123.0 it can be left as 123.123.123.2 or even can be truncated to 123.123.123 The table is stored on a 3.23.43 mySQL Server. The table type is varchar(15) but this can be changed. Possibly not the most optimized solution, but it functions as requested. select distinct(concat(substring_index(field, '.', 3), '.0')) as network from table order by network; field and table should be replaced with your actual names of course. -- Kip Turk, RHCE [EMAIL PROTECTED] Systems Administrator/Killer of Spam/Writer of Code/Penguin Proponent West Central Net - tel: 915.234.5678 / 800.695.9016 fax: 915.656.0071 -.-. --- -.. . / -- --- -. -.- . -.-- --..-- / .- -.-. -.- . .-. - 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 seg faults on remote connections
Same thing here. Local sockets are working find. Remote connections segfault. Getting ready to try the *workaround*. I am using mysql from the stock install of RH8.0 Chris - 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 seg faults on remote connections
Whats the workaround?? How should I start with mysql with defualt setting in a command line??? Thanks guys On Monday, November 4, 2002, at 01:33 PM, Chris West wrote: [EMAIL PROTECTED] - 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: Difficult query
Or, you could use distinct and substring_index instead of my last convoluted answer :) -Original Message- From: Kip Turk [mailto:kipt;wcc.net] Sent: Monday, November 04, 2002 4:17 PM To: Alexander Newald Cc: mySQL Mailing Liste Subject: Re: Difficult query On Mon, 4 Nov 2002, Alexander Newald wrote: Hello, I have a table with only ips in it. I want to get all networks the ips belong to: Example: 123.123.123.1 123.123.132.2 123.123.123.3 1.1.1.4 1.1.1.5 1.1.1.6 12.12.12.1 12.12.12.2 12.12.12.10 The result should be: 123.123.123.0 1.1.1.0 12.12.12.0 Instead of converting on matching row (for example 123.123.123.2) to 123.123.123.0 it can be left as 123.123.123.2 or even can be truncated to 123.123.123 The table is stored on a 3.23.43 mySQL Server. The table type is varchar(15) but this can be changed. Possibly not the most optimized solution, but it functions as requested. select distinct(concat(substring_index(field, '.', 3), '.0')) as network from table order by network; field and table should be replaced with your actual names of course. - 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: (beginner) mysql connection problem!
You might also need to add hostname. It depends on what GRANT sql statement was used to add the user id. Something like mysql -u userid -p -h hostname dbasename should work. If not, check that mysqld is in fact running. You can use ps -aux | grep mysql Regards, ~Kelly W. Black -Original Message- From: David Wu [mailto:dwu;stepup.ca] Sent: Monday, November 04, 2002 11:59 AM To: [EMAIL PROTECTED] Subject: (beginner) mysql connection problem! Hi everyone, On my local machine I had mysql installed, and I was able to log in and doing a test on it. but today as I am trying to login using mysql or mysql -u root -p, I got the error message saying; ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), I had not touch the setting of the mysql at all. What should I do to solve this problem?? Thank you all for your help. - 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: mysql DATETIME substraction problem/?
More appropriately, use curdate()feature from within sql. select * where date= curdate()-1; for example ~Kelly W. Black -Original Message- From: Benjamin Pflugmann [mailto:benjamin-mysql;pflugmann.de] Sent: Monday, November 04, 2002 1:06 PM To: Jim Hogan Cc: [EMAIL PROTECTED] Subject: Re: mysql DATETIME substraction problem/? Hello. On Sun 2002-11-03 at 10:44:43 -0800, [EMAIL PROTECTED] wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators Read that again. It only says that for using INTERVAL, i.e. SELECT 1997-12-31 23:59:59 + INTERVAL 1 SECOND; is allowed instead of SELECT DATE_ADD(1997-12-31 23:59:59, INTERVAL 1 SECOND); By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. [...] That's because your problem does not stem from your storage. Btw, you can easily see that by using SELECT time_end-time_begin FROM mytable LIMIT 20; Regards, Benjamin. -- [EMAIL PROTECTED] - 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: Interesting Challenge
This simply returns me to the documentation. Thanks -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 12:13 PM To: Mysql (E-mail) Subject: RE: Interesting Challenge mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SEL ECT. - 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: mysql shared library.
Try using mysqlshow variables; Some variables can be exported to the sql server via the environment table. For example: shellulimit=2046 shellexport ulimit shellmysqld restart Hope this helps, ~Kelly W. Black -Original Message- From: Nissim Lugasy [mailto:lugasy;lerc.nasa.gov] Sent: Monday, November 04, 2002 11:21 AM To: [EMAIL PROTECTED] Subject: mysql shared library. Hi, I'm trying to access mysql shared library. mysql_init() function returns the address of MYSQL structure . I need to know what the structure looks like.? can all mysql structure parameters be configured in /etc/my.cfg ? 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 - 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: (beginner) mysql connection problem!
If it was a problem with mysql permissions you would get an access denied for user@host using password(YES/NO) error. Since the error is cannot connect through socket, either the socket is not in the specified directory or you do not have permission to it or mysqld is not running. -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05;sprintspectrum.com] Sent: Monday, November 04, 2002 1:21 PM To: 'David Wu' Cc: Mysql (E-mail) Subject: RE: (beginner) mysql connection problem! You might also need to add hostname. It depends on what GRANT sql statement was used to add the user id. Something like mysql -u userid -p -h hostname dbasename should work. If not, check that mysqld is in fact running. You can use ps -aux | grep mysql Regards, ~Kelly W. Black -Original Message- From: David Wu [mailto:dwu;stepup.ca] Sent: Monday, November 04, 2002 11:59 AM To: [EMAIL PROTECTED] Subject: (beginner) mysql connection problem! Hi everyone, On my local machine I had mysql installed, and I was able to log in and doing a test on it. but today as I am trying to login using mysql or mysql -u root -p, I got the error message saying; ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), I had not touch the setting of the mysql at all. What should I do to solve this problem?? Thank you all for your help. - 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 - 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 MY.INI file
Hello, How do you enable logging of slow queries in the MY.INI file under Windows? Also, does anyone have a reasonably good MY.INI file for a configuration under Windows 2000 for a local MySQL host? Thanks, Dan Cumpian - 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
newbie: creating database error
After playing around with 'test' database, then I would like to create my own database. I logged in into mysql using root account and created a database. But, when I logged in using regular user, I got his error : ERROR 1044: Access denied for user: '@localhost' to database 'card' What necessary to do to create database? I tried to create a database using regular user, still got same error. I did change create_priv='Y' in user table from mysql database for this regular user. Anyone know how the basic steps to create database? or any documentation/website resource would be appreciated. Thanks. kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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
MySQL on Win2000
Hello, I am running MySQL on my local Windows 2000 box and I am experiencing extreme slowdowns. I have an application that writes query data to MySQL using two, separate threads. After writing 2000-3000 records, all of a sudden, MySQL goes to 98% CPU usage and throughput drops to 1 update every 2 seconds. I am running MySQLD-NT (3.23.52). I have copies the MEDIUM.CNF file for use. Any ideas on what I can do to prevent this from happening? FYI, it does not appears to be an I/O problems, as the hard drives are idle when this happens. Thanks, Dan Cumpian - 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: JDBC J/Connect driver is seriously slow against InnoDB
Mark: Once of the great things about MySQL is the prompt attention and response on this list. Thanks for replying to this email so quickly. Read on... --- Mark Matthews [EMAIL PROTECTED] wrote: j.random.programmer wrote: Hi: Thought I'd mention this (using the latest dev J/connect): A) DatabaseMetaData.getColumns() does not return the last 4 columns (SCOPE_*) but it should according to the API spec I'll look into fixing this right away. You should write Sun as well, as their JDBC compliance testsuite doesn't look for this :( It's kind of a moot point anyway because those SCOPE_* columns don't really do anything as far as I can tell. But hey, you may as well be complete. B) InnoDB, on their benchmark page, say that inserting 100,000 rows into the DB is about 5 seconds. http://www.innodb.com/bench.html Can you point out where it says this? When I look at the benchmark page, I see that 100,000 inserts take 25 seconds, which JDBC can pretty much match (read on for more). Quoted (from the page in question): InnoDBMyISAM 100 000 inserts25 s. 40 s. 100 000 selects on primary key57 s. 58 s. 100 000 selects on secondary key 68 s. 95 s. True. I mistakenly mis-remembered this figure. The right figure *is* 25s. I am finding this to be more like 300-400 seconds. Try this: I think something is broken with your network, or your machine, read on for more ... I would double-check your network. I just ran your code here on my desktop runnin Linux, which is known not to have the fastest java support, to my test server (100 mbit private network), and it takes 30 seconds, which is 5 seconds longer than 100,000 inserts using DBI (which is using native code to access the database, btw), but is not long enough for HotSpot to actually optimize the code, either. You are right. It looks like some sort of network problem. I tried this on a seperate set of 2 machines both on a different network. The driver was running on a windows 2000 box (dual 1 GB) hitting the mysql box (dual xeon, 1 GB, linux) over a 100 Mbit line. This test now took about 55 seconds. I have a feeling that switching to 4.x would make things even faster, seeing as you are getting 30 seconds with a slower box. Are you using 4.x internally for your testing ? Best regards, --j __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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 MY.INI file
At 17:27 -0500 11/4/02, [EMAIL PROTECTED] wrote: Hello, How do you enable logging of slow queries in the MY.INI file under Windows? It's not Windows-specific: [mysqld] log-slow-queries or [mysqld] log-slow-queries=filename In the former case, the default name is HOSTNAME-slow.log in the data directory. sql, query - 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
Locking is hard!?
Hi!
I have a rather large problem. I run a multi-process-robo-data-collector
wich are connected to a single database.
To make things faster, it runs on several machines leeching on the
processor-power that machines can spare.
The problem is to make them do diffrent things all the time!
This is a record: id ; data ; more_data ; done (Y/N)
When I start the loop, I perform a simple query: SELECT id from users
where done = 'N' LIMIT 1
Then : UPDATE users set done = 'Y' WHERE id = '$id' and $id is the id
collected from previous query.
Then some time goes (5-15 secs) and i: UPDATE users set data =
'blahblah',more_data = 'more blah' WHERE id = '$id'
On top of that, I insert more data into the table: INSERT DELAYED INTO
users (id,done) VALUES('some_id','N')
back to start of loop again...
The problem is to get the table locked so the other processes NEVER fetches
the same id and they tend to do
that on the very short time that elapses between the first SELECT and
UPDATE query.
Regards
-
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: newbie: creating database error
try www.mysql.com, i heard they have docs there you can read...:) -Original Message- From: Admin-Stress [mailto:meerkapot;yahoo.com] Sent: Monday, November 04, 2002 1:41 PM To: [EMAIL PROTECTED] Subject: newbie: creating database error After playing around with 'test' database, then I would like to create my own database. I logged in into mysql using root account and created a database. But, when I logged in using regular user, I got his error : ERROR 1044: Access denied for user: '@localhost' to database 'card' What necessary to do to create database? I tried to create a database using regular user, still got same error. I did change create_priv='Y' in user table from mysql database for this regular user. Anyone know how the basic steps to create database? or any documentation/website resource would be appreciated. Thanks. kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.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 - 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: Problems with ORDER BY from C API (works from command-line tool mysql.exe)
At 11:56 +0100 11/4/02, Joseph Bueno wrote: Christer Holmström (at Home) wrote: Hi! This SQL works perfect when running the command line tool mysql.exe SELECT exe, sum(duration) FROM data GROUP BY exe ORDER BY 2 BUT when running from the C API, the ORDER BY part doesn't seem active, why? Please help. /Christer Hi, What do expect with ORDER BY 2 ? If you want to arder by sum(duration) you should use: SELECT exe, sum(duration) AS s FROM data GROUP BY exe ORDER BY s How will that be different than ORDER BY 2, which sorts using the second column? Your alternate query does the same thing. Hope this helps -- Joseph Bueno - 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: Interesting Challenge
I know this is not elegant, but have have you tried using a temporary table? It adds up your function column correctly. There was an example of this earlier today from Oyekanmi - Re: getting around a subselect, http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:123911:200211:onbajmklkgifeckohcpa Try create temporary table temp your query here; select sum(cell),sum(sector),sum(att), sum(etc) from temp; John Black, Kelly W [PCS] wrote: Hi sql query wizards! I need some help. Is there a way I can take this query here = mysql SELECT cell, sector, - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell - HAVING sector=1 AND (cell=148 or cell=3); +--++--+--+--+---+--+--+--+--+-- --+--+ | cell | sector | att | lc | csh | drops | tccf | bpp | bpc | suf | blocks | mou | +--++--+--+--+---+--+--+--+--+-- --+--+ |3 | 1 | 734 | 12 |6 | 2.52 | 21 |0 |0 |0 | 2.86 | 1501 | | 148 | 1 | 2746 | 93 | 30 | 4.59 | 63 |0 |0 |1 | 2.33 | 4672 | +--++--+--+--+---+--+--+--+--+-- --+--+ And have it display the two rows as a total sum together in one row? I have been struggling with this and could really use some help. Thanks in advance for any ideas. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? - 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
