Re: Cannot start mysqld
check if rpm creates mysql user and group. then mysql_install_db, chown mysql:mysql /var/lib/mysql -R and finally try starting mysqld again t 050810 20:26:52 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050810 20:26:52 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot start mysqld
Thanks for your reply. I confirmed that user and group, mysql, exists and performed the chown as instructed but same thing. Here is my log again after I did this: 050810 23:18:09 mysqld started 050810 23:18:09 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050810 23:18:09 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050810 23:18:09 InnoDB: Flushing modified pages from the buffer pool... 050810 23:18:09 InnoDB: Started; log sequence number 0 43634 050810 23:18:09 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.h ost' doesn't exist 050810 23:18:09 mysqld ended Michael. Pinter Tibor (tibyke) wrote: check if rpm creates mysql user and group. then mysql_install_db, chown mysql:mysql /var/lib/mysql -R and finally try starting mysqld again t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long text insert failure
Liz you didn't mention by I suppose you're using Windows client too i.e.
mysql.exe. A quick test revealed that in such a case char limit is =255
i.e. that's limitation of Windows Command Prompt: command.com or in case
of Win NT,2000,XP - cmd.exe
try writing your long SQL command in text file and import from there ;-)
try:
mysql (your host, login etc. options here ) import.sql
or from within mysql prompt source import.sql;
HTH
Elizabeth Bonifacio wrote:
Dear Guys,
Can please anyone advice me how to successfully insert long text data
into my innodb table rawlog with table stucture as follows:
mysql desc rawlog;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| log | longtext | YES | | NULL| |
+---+--+--+-+-+---+
1 row in set (0.03 sec)
here is one sample of the syslog data I'm trying to insert into the
table without success:
insert into rawlog values ('133date=2005-07-25 time=12:38:23
device_id=FGT1002105200379 log_id=0022010001 type=traffic
subtype=allowed pri=notice vd=root SN=1321 duration=180 policyid=1
proto=17 service=29716/udp status=accept src=192.168.2.63
srcname=192.168.2.63 dst=193.11.28.37 dstname=193.11.28.37
src_int=internal dst_int=external sent=46 rcvd=86 sent_pkt=1
rcvd_pkt=1 src_port=10055 dst_port=29716 vpn=n/a tran_ip=202.189.48.98
tran_port=43957 dir_disp=org tran_disp=noop');
I've already tried changing my column data type from varchar to text
types but still the insert statement cannot be typed all the way to
the end of statement.I can only type half of the syslog data. Does it
has something to do
with my system? I'm using mysql version 4.1.11 running in windows xp
32 bit. My max_allowed_packet is 1048576, do I need to increase this?
by how much?
Please advice me as a need to insert lots of long syslog data in this table.
All the best.
Elizabeth
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
no one has any info to help me out? all i need to know if there is a way to speed up the query or will i have to live with it. this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage structure: maps - rows: 700 +--++-+---+++ | id | mip | map | userid | author | filename | +--++-+---+++ maps_rating - rows: 2,000 +--+-+--++---+---+ | id | map | rating | userid | ipaddress | dateline | +--+-+--++---+---+ user - rows: 10,000 +--+---+ | userid | username | +--+---+ -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 8/9/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query This way the list members can make better suggestions. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
I think it's not fair to expect the list to reply with the kind of information you have provided. Are these tables indexed et all? What indices are you using? Have you tried to see what explain tell you about the plan the optimizer will use to execute the query? Rest assured, you surely stand a better chance of reply if you provide information on the above set of questions. Cheers Manoj - Original Message - From: Sebastian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, August 11, 2005 3:52 PM Subject: Re: help with slow query no one has any info to help me out? all i need to know if there is a way to speed up the query or will i have to live with it. this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage structure: maps - rows: 700 +--++-+---+++ | id | mip | map | userid | author | filename | +--++-+---+++ maps_rating - rows: 2,000 +--+-+--++---+---+ | id | map | rating | userid | ipaddress | dateline | +--+-+--++---+---+ user - rows: 10,000 +--+---+ | userid | username | +--+---+ -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 8/9/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MSSQL to MySQL mapping
Have you adjusted your queries? MSSQL uses a different dialect (T-SQL), then MySQL uses. There are quite some difference, although some queries still might work. Arjan. -Original Message- From: John c [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 10, 2005 08:12 PM To: mysql@lists.mysql.com Subject: MSSQL to MySQL mapping We have a web based application running on IIS 5.0 using MS SQL Server 2000 as the DBMS; we use ODBC to connect to the DB. We migrated our DB to MySQL and used the MySQL ODBC driver. It appears that some of the SQL statements that are accepted by SQL Server 2000 are not accepted by MySQL. Is there a mapping from MSSQL to MySQL statements? Thank you John C. _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long text insert failure
Thanks a lot guys. I tried my insert using MYSQL Query Browser and
manage to insert the syslog. Remo, you're correct, the Windows Command
prompt limitation is the cause since i'm using windows xp cmd.exe.
I'll try to do import as you've suggested.
What a relief, i can proceed in building my syslog database.
Thanks again. You guys are the BEST!
Liz
On 8/11/05, Remo Tex [EMAIL PROTECTED] wrote:
Liz you didn't mention by I suppose you're using Windows client too i.e.
mysql.exe. A quick test revealed that in such a case char limit is =255
i.e. that's limitation of Windows Command Prompt: command.com or in case
of Win NT,2000,XP - cmd.exe
try writing your long SQL command in text file and import from there ;-)
try:
mysql (your host, login etc. options here ) import.sql
or from within mysql prompt source import.sql;
HTH
Elizabeth Bonifacio wrote:
Dear Guys,
Can please anyone advice me how to successfully insert long text data
into my innodb table rawlog with table stucture as follows:
mysql desc rawlog;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| log | longtext | YES | | NULL| |
+---+--+--+-+-+---+
1 row in set (0.03 sec)
here is one sample of the syslog data I'm trying to insert into the
table without success:
insert into rawlog values ('133date=2005-07-25 time=12:38:23
device_id=FGT1002105200379 log_id=0022010001 type=traffic
subtype=allowed pri=notice vd=root SN=1321 duration=180 policyid=1
proto=17 service=29716/udp status=accept src=192.168.2.63
srcname=192.168.2.63 dst=193.11.28.37 dstname=193.11.28.37
src_int=internal dst_int=external sent=46 rcvd=86 sent_pkt=1
rcvd_pkt=1 src_port=10055 dst_port=29716 vpn=n/a tran_ip=202.189.48.98
tran_port=43957 dir_disp=org tran_disp=noop');
I've already tried changing my column data type from varchar to text
types but still the insert statement cannot be typed all the way to
the end of statement.I can only type half of the syslog data. Does it
has something to do
with my system? I'm using mysql version 4.1.11 running in windows xp
32 bit. My max_allowed_packet is 1048576, do I need to increase this?
by how much?
Please advice me as a need to insert lots of long syslog data in this table.
All the best.
Elizabeth
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF8 support in MySQL 4.0
Marco wrote: So how can I do that? There's nothing special you need to do with MySQL itself. Somehow your program obtains UTF-8 data. Insert said data into database. That's it. Perhaps you should read up on UTF-8, to see why this is so. Again, don't expect the database server to be able to do proper sorting, and searching will be tricky. But it can be made to work. Ultimately, it comes down to whether it's more work to work around these problems, or upgrade to v4.1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL C API Version incompatibility
Sujay Koduri wrote: I was connecting to MySQL 5.0.4 through the C API and it was workign fine. But when I downgraded to MYSQL 4.1.13, the same code is giving the following error. Try rebuilding your program against the v4.1 APIs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0.10 compilation error
Hi, I imagine there should be an option to disable yaSSL. if you use ./configure you have to explicitely add --with-yassl to enable it (which is not what you want I presume). HIMH Karam Chand wrote: I plan to modify mysqldump and mysql import utility to support SP, Triggers etc and plan to send a patch to the MySQL development tree. Thus downloading the binary does not seem to be option :) --- Martijn Tonies [EMAIL PROTECTED] wrote: I am trying to compile 5.0.10. While compiling i am getting the following error: e:\mysql5010abetasrc\extra\yassl\src\yassl_imp.cpp(393) : fatal error C1001: INTERNAL COMPILER ERROR (compiler file 'msc1.cpp', line 2701) Please choose the Technical Support command on the Visual C++ Help menu, or open the Technical Support help file for more information and 5-6 more similar to this. I am also getting this one: LINK : fatal error LNK1181: cannot open input file '..\extra\yassl\Debug\yassl.lib' What should I do? Download the binaries? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleanup of connections
Hello Ben! I am in no ways a socket expert, so I can only provide a general remark and pass on what I got from hearsay (below): When pursuing such things, please provide information about the operating systems used, client/server assignment etc. Gleb Paharenko wrote: Hello. MySQL usually waits some time for data from client even if connection is broken, because MySQL could no nothing about it. [[...]] Also different xxx_timeout variables are influenced as well. another job, a cron job, that Kills this script/connection if it takes more than 2 minutes. I was just wondering if this would leave the connection open in MySQL. Yes, in my opinion, this could be the cause of your problem. I heard that when a process terminates on a Unix style system, its open sockets are closed by the OS; this also applies to forceful termination by a killing signal. Then, the communication partner will be informed about the socket being closed and can terminate the connection. On Windows systems, this closing was said not to happen, so a socket remains open unless the process really closed it, and timeout detection on the other side is the only way out. So information about the OSs and machines involved may be significant to judge the situation. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Uninstalling the MySQL cleanly.
hi, Can any one list the steps or give me any pointers how to cleanly uninstall an upper version of MySQL, so that after wards if we install lower version it should not be affected by any left overs of the previous installation. Thank you sujay
Re: Uninstalling the MySQL cleanly.
Can any one list the steps or give me any pointers how to cleanly uninstall an upper version of MySQL, so that after wards if we install lower version it should not be affected by any left overs of the previous installation. What OS? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Uninstalling the MySQL cleanly.
Sorry , I didn't send this in person to you intentionally. sujay -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 11, 2005 2:42 PM To: Sujay Koduri Subject: Re: Uninstalling the MySQL cleanly. . OS is RH9 and kernel is 2.4.20 I suggest you send this to the MySQL list :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com sujay -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 11, 2005 2:33 PM To: mysql@lists.mysql.com Subject: Re: Uninstalling the MySQL cleanly. Can any one list the steps or give me any pointers how to cleanly uninstall an upper version of MySQL, so that after wards if we install lower version it should not be affected by any left overs of the previous installation. What OS? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB FILES ERRORS
Hi, I'm trying to use MySQL Blob columns to store files (docs,gifs,pdf.) but I'm getting corrupted files from the DB. the applications runs php, loading file content with addslashes but in order to spot the error i've tried it with mysql only like this: column file is a MEDIUMBLOB INSERT INTO XX SET file=LOAD_FILE(/tmp/teste.pdf); and them SELECT file INTO OUTFILE /tmp/teste_frommysql.pdf FROM XX WHERE id=12; and teste_frommysql.pdf cant be loaded ls -l shoes that the sizes are different: 270713 Aug 2 11:28 /tmp/teste.pdf (ORIGINAL) 277405 Aug 10 17:21 /tmp/teste_frommysql.pdf I've made a test with a text file and noticed that the file from MySQL has a added LineBreak at the end.Is thta the problem? how can I fix this? This is my config: Linux 2.6.11-1.1369_FC4 mysql-server-4.1.11-2 mysql-4.1.11-2 cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0.10 compilation error
Hello. Comments to this bug might be interesting for you: http://bugs.mysql.com/bug.php?id=9056 Karam Chand [EMAIL PROTECTED] wrote: I plan to modify mysqldump and mysql import utility to support SP, Triggers etc and plan to send a patch to the MySQL development tree. Thus downloading the binary does not seem to be option :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANTS for tables - why is create possible?
Hello. In my opinion, it means that user [EMAIL PROTECTED] is able to create table address in your current database (SHOW GRANTS shows that the name of the database is implicitly added to the table name). Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, What is the purpose of this GRANT statement? GRANT CREATE ON tablename TO [EMAIL PROTECTED]; eg: GRANT CREATE ON address TO [EMAIL PROTECTED]; It's possible, but what is it supposed to do? I can understand this grant on a global (server) and database level, but on a table level? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling the MySQL cleanly.
Hello. I guess you have rpm installation, rpm -e should help you in this case. Find installed mysql packets with `rpm -qa |grep -i mysql` command. If you have troubles with making a choice, send the list of packets which you got to the list. Sujay Koduri [EMAIL PROTECTED] wrote: OS is RH9 and kernel is 2.4.20 I suggest you send this to the MySQL list :-) With regards, Can any one list the steps or give me any pointers how to cleanly uninstall an upper version of MySQL, so that after wards if we install lower version it should not be affected by any left overs of the previous installation. What OS? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP! sql command question for mysql
Hello. I'm not sure about the speed of this query, but it seems to work: mysql source g.sql +---+---++ | id| price | vendor | +---+---++ | OG012 |20 | b | | OG013 |40 | c | +---+---++ [EMAIL PROTECTED] mysql-debug-5.0.10-beta-linux-i686]$ cat g.sql SELECT p4.id ,p4.price ,p4.vendor FROM ( SELECT p3.id ,p3.price ,p3.vendor ,MIN( p3.rating) FROM ( SELECT p1.id ,p1.price ,p1.vendor ,q1.rating FROM p p1 INNER JOIN q q1 ON p1.vendor = q1.vendor WHERE p1.price = ( SELECT MIN(p2.price) FROM p p2 WHERE p2.id = p1.id ) ) AS p3 GROUP BY p3.id, p3.price ) AS p4 ; See: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Joe Culler [EMAIL PROTECTED] wrote: Hello there, first of all, my english isn't good, hope you understand what I mean. I have a table name p like that: mysql select * from p; +---+---++ | id| price | vendor | +---+---++ | OG012 |40 | a | | OG012 |20 | b | | OG012 |20 | c | | OG013 |40 | c | +---+---++ and I have another table name q: mysql select * from q; +++ | vendor | rating | +++ | a | 1 | | b | 2 | | c | 3 | +++ My question is how do I find the minimal price for each id and vendor rating is highest. I wish my result is: +---+---++ | id| price | vendor | +---+---++ | OG012 |20 | b | | OG013 |40 | c | +---+---++ Since vender b and c are the same price for id(OG012), but vendor b has=20 minimal rating then vendor c. Many thanks, Joe. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Hello. I've created tables similar to your and the query runs fast enough on my test data (maybe I have good indexes). Please, provide the EXPLAIN output for your query and exact definitions of your tables (use SHOW CREATE TABLE). Sebastian [EMAIL PROTECTED] wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage structure: maps - rows: 700 +--++-+---+++ | id | mip | map | userid | author | filename | +--++-+---+++ maps_rating - rows: 2,000 +--+-+--++---+---+ | id | map | rating | userid | ipaddress | dateline | +--+-+--++---+---+ user - rows: 10,000 +--+---+ | userid | username | +--+---+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot start mysqld
Hello. Check that mysql_install_db filled mysql database in the same directory as your datadir. See: http://dev.mysql.com/doc/mysql/en/unix-post-installation.html Gobi [EMAIL PROTECTED] wrote: Thanks for your reply. I confirmed that user and group, mysql, exists and performed the chown as instructed but same thing. Here is my log again after I did this: 050810 23:18:09 mysqld started 050810 23:18:09 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050810 23:18:09 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050810 23:18:09 InnoDB: Flushing modified pages from the buffer pool... 050810 23:18:09 InnoDB: Started; log sequence number 0 43634 050810 23:18:09 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.h ost' doesn't exist 050810 23:18:09 mysqld ended Michael. Pinter Tibor (tibyke) wrote: check if rpm creates mysql user and group. then mysql_install_db, chown mysql:mysql /var/lib/mysql -R and finally try starting mysqld again t -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auditing mysql logins
Is there any way to have MySQL generate a log of successful/failed login attempts? Or even better: have this information sent to syslog? (using mysql 4.1) signature.asc Description: OpenPGP digital signature
Re: BLOB FILES ERRORS
Hello. Use DUMPFILE instead of OUTFILE. See: http://dev.mysql.com/doc/mysql/en/select.html jose nuno neto [EMAIL PROTECTED] wrote: Hi, I'm trying to use MySQL Blob columns to store files (docs,gifs,pdf.) but I'm getting corrupted files from the DB. the applications runs php, loading file content with addslashes but in order to spot the error i've tried it with mysql only like this: column file is a MEDIUMBLOB INSERT INTO XX SET file=LOAD_FILE(/tmp/teste.pdf); and them SELECT file INTO OUTFILE /tmp/teste_frommysql.pdf FROM XX WHERE id=12; and teste_frommysql.pdf cant be loaded ls -l shoes that the sizes are different: 270713 Aug 2 11:28 /tmp/teste.pdf (ORIGINAL) 277405 Aug 10 17:21 /tmp/teste_frommysql.pdf I've made a test with a text file and noticed that the file from MySQL has a added LineBreak at the end.Is thta the problem? how can I fix this? This is my config: Linux 2.6.11-1.1369_FC4 mysql-server-4.1.11-2 mysql-4.1.11-2 cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Uninstalling the MySQL cleanly.
Hi gleb,
I am including the packages which I tried to install along with the program
and the error I am getting when I tried running it.
Packages
MySQL-client-4.1.13-0.i386.rpm
MySQL-server-4.1.13-0.i386.rpm
MySQL-devel-4.1.13-0.i386.rpm
MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm
The error I am getting for the below program is
Binding of columns failed
2036: Using unsupported buffer type: 0 (parameter: 1)
I tried running the program using the following command.
gcc -g prog_name -lmysqlclient
This is the program I am going to test.
#include stdio.h
#include mysql/mysql.h
#define INSERT select column_name from information_schema WHERE Table_Name
= ?
//This table is already existing. All the columns in the table are of type
varchar.
int main()
{
MYSQL mysql;
MYSQL_STMT *stmt;
MYSQL_BIND bind[1];
MYSQL_BIND bind_result[1];
char str_in[50], str[2];
unsigned long length[1];
unsigned long str_in_len;
my_bool is_null[1];
if(!mysql_init(mysql))
{
exit(3);
}
if(!mysql_real_connect(mysql, localhost,user, pass, db, 0, NULL,
0))
{
printf(%d: %s \n,mysql_errno(mysql), mysql_error(mysql));
exit();
}
stmt = mysql_stmt_init(mysql);
if(!stmt)
{
printf(Out of memory..\n);
exit(3);
}
if ( mysql_stmt_prepare(stmt, INSERT, strlen(INSERT)) )
{
printf(Prepare statement failed for insertion..:( \n);
exit(3);
}
bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
bind[0].buffer = (char *)str_in;
bind[0].buffer_length = 50;
bind[0].is_null = 0;
bind[0].length = str_in_len;
strncpy(str_in, information_schema, 50);
str_in_len = strlen(str_in);
if ( mysql_stmt_bind_param(stmt,bind) )
{
/*Failing here **/
printf(Binding of columns failed\n);
printf(%d: %s \n,mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
exit(3);
}
if (mysql_stmt_execute(stmt))
{
printf(Executing prepared statement failed\n);
exit(3);
}
memset(bind_result, 0, sizeof(bind_result));
bind_result[0].buffer_type = MYSQL_TYPE_STRING;
bind_result[0].buffer = (char *)str;
bind_result[0].buffer_length = 2;
bind_result[0].is_null = is_null[0];
bind_result[0].length = length[0];
if (mysql_stmt_bind_result(stmt, bind_result))
{
printf( mysql_stmt_bind_result() failed\n);
printf( %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
printf( mysql_stmt_store_result() failed\n);
printf( %s\n, mysql_stmt_error(stmt));
exit(0);
}
while (!mysql_stmt_fetch(stmt))
{
if (is_null[0])
printf( NULL\n);
else
printf( %s(%ld)\n, str, length[0]);
}
printf(The no of rows affected are %d\n,mysql_stmt_affected_rows(stmt));
if (mysql_stmt_close(stmt))
{
printf(Error while closing mysql statement\n);
exit(3);
}
mysql_close(mysql);
return 0;
}
Thank you
sujay
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 11, 2005 2:58 PM
To: mysql@lists.mysql.com
Subject: Re: Uninstalling the MySQL cleanly.
Hello.
I guess you have rpm installation, rpm -e should help you in this case.
Find installed mysql packets with `rpm -qa |grep -i mysql` command.
If you have troubles with making a choice, send the list of packets which
you got to the list.
Sujay Koduri [EMAIL PROTECTED] wrote:
OS is RH9 and kernel is 2.4.20
I suggest you send this to the MySQL list :-)
With regards,
Can any one list the steps or give me any pointers how to cleanly
uninstall
an upper version of MySQL,
so that after wards if we install lower version it should not be
affected
by
any left overs of the previous installation.
What OS?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot start mysqld
I find that I can start mysqld by using the --skip-grant-tables option but as soon as I run mysqld without that option, it dies again. I did try running mysql_install_db --user=mysql but when I did mysqlshow mysql, it was empty. Any ideas on how to proceed? Thanks, Michael. Gleb Paharenko wrote: Hello. Check that mysql_install_db filled mysql database in the same directory as your datadir. See: http://dev.mysql.com/doc/mysql/en/unix-post-installation.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot start mysqld
Never mind. I figured it out. I had to run the install_db script as mysql user. Gobi wrote: I find that I can start mysqld by using the --skip-grant-tables option but as soon as I run mysqld without that option, it dies again. I did try running mysql_install_db --user=mysql but when I did mysqlshow mysql, it was empty. Any ideas on how to proceed? Thanks, Michael. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP! sql command question for mysql
Can I just ask a quick question about this one, mainly about the Group By statement and how its used in MySQL. It appears that you don't need to specify in the Group By clause all the columns that are referenced without an aggregate function i.e vendor is missed out from: GROUP BY p3.id, p3.price. I'm just asking because my background is MSSQL and you would have to specify vendor. For example this query would be invalid in MSSQL but valid in MySQL: select id, vendor, sum(price) from p group by id But this one would work in both: select id, vendor, sum(price) from p group by id, vendor Each query giving a different result. Regards Ben. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 23:29 To: mysql@lists.mysql.com Subject: Re: HELP! sql command question for mysql Hello. I'm not sure about the speed of this query, but it seems to work: mysql source g.sql +---+---++ | id| price | vendor | +---+---++ | OG012 |20 | b | | OG013 |40 | c | +---+---++ [EMAIL PROTECTED] mysql-debug-5.0.10-beta-linux-i686]$ cat g.sql SELECT p4.id ,p4.price ,p4.vendor FROM ( SELECT p3.id ,p3.price ,p3.vendor ,MIN( p3.rating) FROM ( SELECT p1.id ,p1.price ,p1.vendor ,q1.rating FROM p p1 INNER JOIN q q1 ON p1.vendor = q1.vendor WHERE p1.price = ( SELECT MIN(p2.price) FROM p p2 WHERE p2.id = p1.id ) ) AS p3 GROUP BY p3.id, p3.price ) AS p4 ; See: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Joe Culler [EMAIL PROTECTED] wrote: Hello there, first of all, my english isn't good, hope you understand what I mean. I have a table name p like that: mysql select * from p; +---+---++ | id| price | vendor | +---+---++ | OG012 |40 | a | | OG012 |20 | b | | OG012 |20 | c | | OG013 |40 | c | +---+---++ and I have another table name q: mysql select * from q; +++ | vendor | rating | +++ | a | 1 | | b | 2 | | c | 3 | +++ My question is how do I find the minimal price for each id and vendor rating is highest. I wish my result is: +---+---++ | id| price | vendor | +---+---++ | OG012 |20 | b | | OG013 |40 | c | +---+---++ Since vender b and c are the same price for id(OG012), but vendor b has=20 minimal rating then vendor c. Many thanks, Joe. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ == For more information on Hargreaves Lansdown, visit our web site http://www.hargreaveslansdown.co.uk IMPORTANT NOTICE This email is intended solely for the recipient and is confidential and not for third party unauthorised distribution. If an addressing, or transmission, error has misdirected this email, please notify the author by replying to this email or notifying the system manager ([EMAIL PROTECTED]) if you are not the intended recipient you must not disclose, distribute, copy, print or rely on this email. Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are not authorised to enter into any contract through email and therefore nothing contained herein should be construed as such. This email has been prepared using information believed by the author to be reliable and accurate but Hargreaves Lansdown makes no warranty as to the accuracy or completeness. In particular, Hargreaves Lansdown does not accept responsibility for any changes made to this email after it was sent. All group companies are Authorised and regulated by
Re: HELP! sql command question for mysql
It appears that you don't need to specify in the Group By clause all the columns that are referenced without an aggregate function True. For example this query would be invalid in MSSQL but valid in MySQL: select id, vendor, sum(price) from p group by id In this case, `vendor` would hold the vendor value from some random record with the `id` indicated. In practice, it's the first one as it appears in the table, but that is not guaranteed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP! sql command question for mysql
Scott Noyes wrote: It appears that you don't need to specify in the Group By clause all the columns that are referenced without an aggregate function True. For example this query would be invalid in MSSQL but valid in MySQL: select id, vendor, sum(price) from p group by id In this case, `vendor` would hold the vendor value from some random record with the `id` indicated. In practice, it's the first one as it appears in the table, but that is not guaranteed. Right. Mysql allows this as a convenience, but warns against selecting columns which do not have unique values per group. See the manual for the details http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP! sql command question for mysql
Ok ,thanks. I did notice that it effectively 'ignored' vendor and brought back a random vendor. In this case it ignored b and returned a but still used b's price in the total. Just something for me to remember I guess. Thanks for the response. -Original Message- From: Scott Noyes [mailto:[EMAIL PROTECTED] Sent: 11 August 2005 15:39 To: Ben Smith Cc: mysql@lists.mysql.com Subject: Re: HELP! sql command question for mysql It appears that you don't need to specify in the Group By clause all the columns that are referenced without an aggregate function True. For example this query would be invalid in MSSQL but valid in MySQL: select id, vendor, sum(price) from p group by id In this case, `vendor` would hold the vendor value from some random record with the `id` indicated. In practice, it's the first one as it appears in the table, but that is not guaranteed. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ == For more information on Hargreaves Lansdown, visit our web site http://www.hargreaveslansdown.co.uk IMPORTANT NOTICE This email is intended solely for the recipient and is confidential and not for third party unauthorised distribution. If an addressing, or transmission, error has misdirected this email, please notify the author by replying to this email or notifying the system manager ([EMAIL PROTECTED]) if you are not the intended recipient you must not disclose, distribute, copy, print or rely on this email. Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are not authorised to enter into any contract through email and therefore nothing contained herein should be construed as such. This email has been prepared using information believed by the author to be reliable and accurate but Hargreaves Lansdown makes no warranty as to the accuracy or completeness. In particular, Hargreaves Lansdown does not accept responsibility for any changes made to this email after it was sent. All group companies are Authorised and regulated by the Financial Services Authority and registered in England and the registered office is Kendal House, 4 Brighton Mews, Clifton, Bristol, BS8 2NX. Telephone: 0117 9889880 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cache queries
Hi.. I'm using cache queries with MySQL 4.0.13 during three days and I get a little better speed. Value of Query_cache_size = 20 Mb. I have two questions. 1.- Is there any way to see which queries are being stored ?? 2.- How can I delay qcache_lowmen_pruenes ?? In three day values of it is 64421 Is it too ?? +-+--+ | Variable_name | Value| +-+--+ | Qcache_queries_in_cache | 3106 | | Qcache_inserts | 447577 | | Qcache_hits | 2697410 | | Qcache_lowmem_prunes| 64421| | Qcache_not_cached | 1421991 | | Qcache_free_memory | 10226504 | | Qcache_free_blocks | 418 | | Qcache_total_blocks | 6997 | | Threads_cached | 0| +-+--+ Thank you !!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to determine right value for max_allowed_packet?
I think I've foind the culprit: a problem (logical, not physical) had been discovered with a couple of tables which were fixed by truncating them in the production replication master and reloading them from a mysqldump of the corrected tables from the qc/dev database. the dump was done w/the -e (which makes sense) flag hence REALLY BIG individual insert statements even though the table itself wasn't that big nor were any individual rows. does this sound plausible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.6/69 - Release Date: 8/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error no: 12 (
Hello, I am running MySQL on a WinXP box. I am getting the following error: Error no: 12 can't read dir of './tablename/' (errorcode: 2) What does this error mean? Karam __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade 3.23 to 4.12 slowness
Hello- I have a user list of about 200 entries My original config was Fedora Core 3, Mysql 3.23 I upgraded today to Fedora Core 4 which comes with MySQL 4.12. AFter updating all the packages etc and setting up the website I run again, I notice that I had slow query's during searches. More investigation revealed that I can reproduce this in the mysql command mode as well. If I do a long SELECT query (multiple columns across 3 tables), the query can take 20-30 seconds. If I reduce the number of columns in that SELECT statement, the speed increases. I would expect this anyhow, however in 3.23 I had NO hesitation at all and the result set came immediately back to me. Ive read through the upgrade web pagess on the mysql site and did what needed to be done (which wasnt much to begin with) and result is the same. here's my select statement: SELECT userid, nickname, city, state, country, email, sex, birthday, marital_stat, num_children, height, weight, smoke, drink, image1, description, age_range_min, age_range_max, is_active FROM users1, user_stats1, user_wants1, user_account1 WHERE sex='m' AND userid=user_stats1.id AND userid=user_wants1.id AND userid=user_account1.id order by sex LIMIT 200 Any ideas/help is much appreciated. Im a relative newbie to mysql and databases in general. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
well i managed to solve the problem myself, and im no sql genius... i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. i think that is about all the improvement i can get.. but if there is still room for more speed i'd like to know.. Sebastian wrote: Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.6/69 - Release Date: 8/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practices for deleting and restoring records - moving vs flagging
Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Any thoughts/ideas ? -- In Peace, Saqib Ali http://www.xml-dev.com/blog/ Consensus is good, but informed dictatorship is better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cache queries
Paco Martinez Rodriguez wrote: Hi.. I'm using cache queries with MySQL 4.0.13 during three days and I get a little better speed. Value of Query_cache_size = 20 Mb. This is 20 Mega Bits. I suppose you meant to mean 20 MegaBytes (20MB), or even 20 Mibibytes (20MiB). I have two questions. 1.- Is there any way to see which queries are being stored ?? About this I don't know. Does anyone? 2.- How can I delay qcache_lowmen_pruenes ?? In three day values of it is 64421 Is it too ?? By increasing Query_cache_size, you get qcache_lowmen_pruenes lower after a while. THis may be because of the Qcache_free_blocks value, beeing the free mem ammount about half total mem (Qcache_free_memory =~ 10MB) +-+--+ | Variable_name | Value| +-+--+ | Qcache_queries_in_cache | 3106 | | Qcache_inserts | 447577 | | Qcache_hits | 2697410 | | Qcache_lowmem_prunes| 64421| | Qcache_not_cached | 1421991 | | Qcache_free_memory | 10226504 | | Qcache_free_blocks | 418 | | Qcache_total_blocks | 6997 | | Threads_cached | 0| +-+--+ Thank you !!! -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. The first is what I like more. While in the first to mark as deleted (or restore), you only have to change one column, and in the second, you have to move (and move again to restore) from one table to another. Getting the value from the normal value to store it in the second could lead to a problem Implement the first in a developed schema, is just add a column of type bool (for example) with the default beeing not deleted. The second has the other problem of a change in the schema of the normal table has to be done in the deleted values table. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). This lets you to have two different tables of deleted values. Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a If you mean The Primary key for Table1 is auto-generated by using auto_increment, it is not impossible. You can just copy the entire contents of the row. different table. However if we just flag the record as deleted the restoring is quite easy. As I said. Any thoughts/ideas ? Just my opinion, and it seems to be the opinion of mambo developers, as they implement the deletion of values to restore like this way, and they have also a published column. If they have done this they would need 4 tables: published_and_not_deleted, published_and_deleted, not_published_and_not_deleted and not_published_and_deleted. I would say that the second is very bad. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT search result requirement
I am looking into using the FULLTEXT search features for our FAQ system. Problem is the 50% limitation. We aren't going to have thousands of questions or articles, so the odds of most of the questions/articles matching is high and a desireable effect for us. Is there a away to disable this requirment? Or should I be looking in a totally different direction for something of this scale? Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search result requirement
2005/8/11, Eric Jensen [EMAIL PROTECTED]: I am looking into using the FULLTEXT search features for our FAQ system. Problem is the 50% limitation. We aren't going to have thousands of questions or articles, so the odds of most of the questions/articles matching is high and a desireable effect for us. Is there a away to disable this requirment? Yes: use IN BOOLEAN MODE for your fulltext searches. See the MySQL manual for further information. Jan Pieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Sebastian wrote: well i managed to solve the problem myself, and im no sql genius... i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. i think that is about all the improvement i can get.. but if there is still room for more speed i'd like to know.. Sebastian wrote: Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage It seems to me that an index on maps_rating.rating, maps.userid, user.userid might help. Also making the query ALTER TABLE maps ORDER BY maps.dateline DESC once a day or more, would help the ordering. I may be saying too include much indexes (probably the first), but it may not make bad at all (probably updates/inserts would be slower). Making some tests might help to see what is the best. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auditing mysql logins
I use the -l to mysqld and I get /usr/libexec/mysqld, Version: 3.23.58-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument 050811 14:49:34 1 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 050811 14:49:37 2 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 050811 14:49:38 3 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 4 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 050811 14:49:45 5 Connect [EMAIL PROTECTED] on 050811 14:49:49 5 Query show databases 050811 14:49:56 5 Quit 050811 14:50:46 6 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password:YES) 050811 14:50:51 7 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 050811 14:50:55 8 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) 050811 14:51:00 9 Connect [EMAIL PROTECTED] on 050811 14:56:15 9 Quit man mysqld for more On Thu, 11 Aug 2005, Johannes B. Ullrich wrote: Is there any way to have MySQL generate a log of successful/failed login attempts? Or even better: have this information sent to syslog? (using mysql 4.1) -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interpreting mysqlcheck output: Size of datafile is: x Should be: y
Good afternoon, Our /var partition crashed last night, and as a result, several MySQL tables were corrupted. After cleaning up the partition, we repaired those tables via mysqlcheck. When we started using the webapp built on top of those tables today, we noticed that rows were missing from some of the corrupted tables. Those tables appear to be tables where mysqlcheck reported that Size of datafile is: x Should be: y. For example: Table Op Msg_typeMsg_text cs.carescoutHH check error Size of datafile is: 4640132 Should be: 4640484 cs.carescoutHH check error Corrupt As I interpret that output, it suggests that (before repairs) the file /path/to/mysql/cs/carescoutHH.MYD was only 4,640,132 bytes when it should have been 4,640,484 bytes. I have assumed that if mysqlcheck repairs a table then by definition all of the data is still present in that table. Or, stated differently, that mysqlcheck would report that it was unable to repair a table if it rebuilt the structure of the table but lost data during the process. But perhaps I have assumed that incorrectly. Is it possible that mysqlcheck repaired the table, but that rows are still missing from the table? That, when faced with inadequate data to rebuild a row, it (silently) discards that row? Thanks for your help. -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB FILES ERRORS
Hi, I remembered something when playing wiht BLOB. It looks that the select statement does not work well with BLOB. It always add or modify the BLOB a little bit. Later I found out that the client programming interfaces, perl,C++ etc tend to do well on BLOB and files. Kemin Gleb Paharenko wrote: Hello. Use DUMPFILE instead of OUTFILE. See: http://dev.mysql.com/doc/mysql/en/select.html jose nuno neto [EMAIL PROTECTED] wrote: Hi, I'm trying to use MySQL Blob columns to store files (docs,gifs,pdf.) but I'm getting corrupted files from the DB. the applications runs php, loading file content with addslashes but in order to spot the error i've tried it with mysql only like this: column file is a MEDIUMBLOB INSERT INTO XX SET file=LOAD_FILE(/tmp/teste.pdf); and them SELECT file INTO OUTFILE /tmp/teste_frommysql.pdf FROM XX WHERE id=12; and teste_frommysql.pdf cant be loaded ls -l shoes that the sizes are different: 270713 Aug 2 11:28 /tmp/teste.pdf (ORIGINAL) 277405 Aug 10 17:21 /tmp/teste_frommysql.pdf I've made a test with a text file and noticed that the file from MySQL has a added LineBreak at the end.Is thta the problem? how can I fix this? This is my config: Linux 2.6.11-1.1369_FC4 mysql-server-4.1.11-2 mysql-4.1.11-2 cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M
Re: Error no: 12 (
Hello. [EMAIL PROTECTED] gleb]$ perror 2 OS error code 2: No such file or directory Karam Chand [EMAIL PROTECTED] wrote: Hello, I am running MySQL on a WinXP box. I am getting the following error: Error no: 12 can't read dir of './tablename/' (errorcode: 2) What does this error mean? Karam __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling the MySQL cleanly.
Hello.
Your program works for me. Try to explicitly specify the location
of libmysql using -L command line option for gcc. This link might
be helpful:
http://dev.mysql.com/doc/mysql/en/debugging-client.html
Sujay Koduri [EMAIL PROTECTED] wrote:
Hi gleb,
I am including the packages which I tried to install along with the program
and the error I am getting when I tried running it.
Packages
MySQL-client-4.1.13-0.i386.rpm
MySQL-server-4.1.13-0.i386.rpm
MySQL-devel-4.1.13-0.i386.rpm
MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm
The error I am getting for the below program is
Binding of columns failed
2036: Using unsupported buffer type: 0 (parameter: 1)
I tried running the program using the following command.
gcc -g prog_name -lmysqlclient
This is the program I am going to test.
#include stdio.h
#include mysql/mysql.h
#define INSERT select column_name from information_schema WHERE Table_Name
= ?
//This table is already existing. All the columns in the table are of type
varchar.
int main()
{
MYSQL mysql;
MYSQL_STMT *stmt;
MYSQL_BIND bind[1];
MYSQL_BIND bind_result[1];
char str_in[50], str[2];
unsigned long length[1];
unsigned long str_in_len;
my_bool is_null[1];
if(!mysql_init(mysql))
{
exit(3);
}
if(!mysql_real_connect(mysql, localhost,user, pass, db, 0, NULL,
0))
{
printf(%d: %s \n,mysql_errno(mysql), mysql_error(mysql));
exit();
}
stmt = mysql_stmt_init(mysql);
if(!stmt)
{
printf(Out of memory..\n);
exit(3);
}
if ( mysql_stmt_prepare(stmt, INSERT, strlen(INSERT)) )
{
printf(Prepare statement failed for insertion..:( \n);
exit(3);
}
bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
bind[0].buffer = (char *)str_in;
bind[0].buffer_length = 50;
bind[0].is_null = 0;
bind[0].length = str_in_len;
strncpy(str_in, information_schema, 50);
str_in_len = strlen(str_in);
if ( mysql_stmt_bind_param(stmt,bind) )
{
/*Failing here **/
printf(Binding of columns failed\n);
printf(%d: %s \n,mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
exit(3);
}
if (mysql_stmt_execute(stmt))
{
printf(Executing prepared statement failed\n);
exit(3);
}
memset(bind_result, 0, sizeof(bind_result));
bind_result[0].buffer_type = MYSQL_TYPE_STRING;
bind_result[0].buffer = (char *)str;
bind_result[0].buffer_length = 2;
bind_result[0].is_null = is_null[0];
bind_result[0].length = length[0];
if (mysql_stmt_bind_result(stmt, bind_result))
{
printf( mysql_stmt_bind_result() failed\n);
printf( %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
printf( mysql_stmt_store_result() failed\n);
printf( %s\n, mysql_stmt_error(stmt));
exit(0);
}
while (!mysql_stmt_fetch(stmt))
{
if (is_null[0])
printf( NULL\n);
else
printf( %s(%ld)\n, str, length[0]);
}
printf(The no of rows affected are %d\n,mysql_stmt_affected_rows(stmt));
if (mysql_stmt_close(stmt))
{
printf(Error while closing mysql statement\n);
exit(3);
}
mysql_close(mysql);
return 0;
}
Thank you
sujay
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 11, 2005 2:58 PM
To: mysql@lists.mysql.com
Subject: Re: Uninstalling the MySQL cleanly.
Hello.
I guess you have rpm installation, rpm -e should help you in this case.
Find installed mysql packets with `rpm -qa |grep -i mysql` command.
If you have troubles with making a choice, send the list of packets which
you got to the list.
Sujay Koduri [EMAIL PROTECTED] wrote:
OS is RH9 and kernel is 2.4.20
I suggest you send this to the MySQL list :-)
With regards,
Can any one list the steps or give me any pointers how to cleanly
uninstall
an upper version of MySQL,
so that after wards if we install lower version it should not be
affected
by
any left overs of the previous installation.
What OS?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing
Re: cache queries
Hello. You don't have enough free blocks in cache, and I guess it has a big fragmentation. Follow recommendations from: http://dev.mysql.com/doc/mysql/en/query-cache-configuration.html Decrease the value of query_cache_min_res_unit and run FLUSH QUERY CACHE from time to time. Paco Martinez Rodriguez [EMAIL PROTECTED] wrote: Hi.. I'm using cache queries with MySQL 4.0.13 during three days and I get a little better speed. Value of Query_cache_size =3D 20 Mb. I have two questions.=20 1.- Is there any way to see which queries are being stored ?? 2.- How can I delay qcache_lowmen_pruenes ?? In three day values of it is 64421 Is it too ?? +-+--+ | Variable_name | Value| +-+--+ | Qcache_queries_in_cache | 3106 | | Qcache_inserts | 447577 | | Qcache_hits | 2697410 | | Qcache_lowmem_prunes| 64421| | Qcache_not_cached | 1421991 | | Qcache_free_memory | 10226504 | | Qcache_free_blocks | 418 | | Qcache_total_blocks | 6997 | | Threads_cached | 0| +-+--+ Thank you !!! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling the MySQL cleanly.
Hello.
Maybe you have old includes too, use -I to specify the location of
4.1.13 includes.
Sujay Koduri [EMAIL PROTECTED] wrote:
Hi gleb,
I am including the packages which I tried to install along with the program
and the error I am getting when I tried running it.
Packages
MySQL-client-4.1.13-0.i386.rpm
MySQL-server-4.1.13-0.i386.rpm
MySQL-devel-4.1.13-0.i386.rpm
MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm
The error I am getting for the below program is
Binding of columns failed
2036: Using unsupported buffer type: 0 (parameter: 1)
I tried running the program using the following command.
gcc -g prog_name -lmysqlclient
This is the program I am going to test.
#include stdio.h
#include mysql/mysql.h
#define INSERT select column_name from information_schema WHERE Table_Name
= ?
//This table is already existing. All the columns in the table are of type
varchar.
int main()
{
MYSQL mysql;
MYSQL_STMT *stmt;
MYSQL_BIND bind[1];
MYSQL_BIND bind_result[1];
char str_in[50], str[2];
unsigned long length[1];
unsigned long str_in_len;
my_bool is_null[1];
if(!mysql_init(mysql))
{
exit(3);
}
if(!mysql_real_connect(mysql, localhost,user, pass, db, 0, NULL,
0))
{
printf(%d: %s \n,mysql_errno(mysql), mysql_error(mysql));
exit();
}
stmt = mysql_stmt_init(mysql);
if(!stmt)
{
printf(Out of memory..\n);
exit(3);
}
if ( mysql_stmt_prepare(stmt, INSERT, strlen(INSERT)) )
{
printf(Prepare statement failed for insertion..:( \n);
exit(3);
}
bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
bind[0].buffer = (char *)str_in;
bind[0].buffer_length = 50;
bind[0].is_null = 0;
bind[0].length = str_in_len;
strncpy(str_in, information_schema, 50);
str_in_len = strlen(str_in);
if ( mysql_stmt_bind_param(stmt,bind) )
{
/*Failing here **/
printf(Binding of columns failed\n);
printf(%d: %s \n,mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
exit(3);
}
if (mysql_stmt_execute(stmt))
{
printf(Executing prepared statement failed\n);
exit(3);
}
memset(bind_result, 0, sizeof(bind_result));
bind_result[0].buffer_type = MYSQL_TYPE_STRING;
bind_result[0].buffer = (char *)str;
bind_result[0].buffer_length = 2;
bind_result[0].is_null = is_null[0];
bind_result[0].length = length[0];
if (mysql_stmt_bind_result(stmt, bind_result))
{
printf( mysql_stmt_bind_result() failed\n);
printf( %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
printf( mysql_stmt_store_result() failed\n);
printf( %s\n, mysql_stmt_error(stmt));
exit(0);
}
while (!mysql_stmt_fetch(stmt))
{
if (is_null[0])
printf( NULL\n);
else
printf( %s(%ld)\n, str, length[0]);
}
printf(The no of rows affected are %d\n,mysql_stmt_affected_rows(stmt));
if (mysql_stmt_close(stmt))
{
printf(Error while closing mysql statement\n);
exit(3);
}
mysql_close(mysql);
return 0;
}
Thank you
sujay
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 11, 2005 2:58 PM
To: mysql@lists.mysql.com
Subject: Re: Uninstalling the MySQL cleanly.
Hello.
I guess you have rpm installation, rpm -e should help you in this case.
Find installed mysql packets with `rpm -qa |grep -i mysql` command.
If you have troubles with making a choice, send the list of packets which
you got to the list.
Sujay Koduri [EMAIL PROTECTED] wrote:
OS is RH9 and kernel is 2.4.20
I suggest you send this to the MySQL list :-)
With regards,
Can any one list the steps or give me any pointers how to cleanly
uninstall
an upper version of MySQL,
so that after wards if we install lower version it should not be
affected
by
any left overs of the previous installation.
What OS?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Hello. i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. Have a look here: http://dev.mysql.com/doc/mysql/en/order-by-optimization.html http://dev.mysql.com/doc/mysql/en/group-by-optimization.html Sebastian [EMAIL PROTECTED] wrote: well i managed to solve the problem myself, and im no sql genius... i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. i think that is about all the improvement i can get.. but if there is still room for more speed i'd like to know.. Sebastian wrote: Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade 3.23 to 4.12 slowness
Hello. Maybe you had configuration file with different settings. Nevertheless, send the information about your RAM size, the output of SHOW VARIABLES, configuration file, table definitions (use SHOW CREATE TABLE), EXPLAIN output for your query, and list should help you. Jeff Lacki [EMAIL PROTECTED] wrote: Hello- I have a user list of about 200 entries My original config was Fedora Core 3, Mysql 3.23 I upgraded today to Fedora Core 4 which comes with MySQL 4.12. AFter updating all the packages etc and setting up the website I run again, I notice that I had slow query's during searches. More investigation revealed that I can reproduce this in the mysql command mode as well. If I do a long SELECT query (multiple columns across 3 tables), the query can take 20-30 seconds. If I reduce the number of columns in that SELECT statement, the speed increases. I would expect this anyhow, however in 3.23 I had NO hesitation at all and the result set came immediately back to me. Ive read through the upgrade web pagess on the mysql site and did what needed to be done (which wasnt much to begin with) and result is the same. here's my select statement: SELECT userid, nickname, city, state, country, email, sex, birthday, marital_stat, num_children, height, weight, smoke, drink, image1, description, age_range_min, age_range_max, is_active FROM users1, user_stats1, user_wants1, user_account1 WHERE sex='m' AND userid=user_stats1.id AND userid=user_wants1.id AND userid=user_account1.id order by sex LIMIT 200 Any ideas/help is much appreciated. Im a relative newbie to mysql and databases in general. Jeff -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb engine breaks snapshot-based backup strategy
Am I doing something wrong, or does the innodb engine design preclude loading a server with a readonly database snapshot? I'm talking about 4.1.x (or maybe 4.x) and linux lvm snapshots (lvm2) specifically about MySQL-4.1.12 (mysql-4.1.12-2.FC4.1.x86_64.rpm) tested on an AMD-64 + FC4 (linux 2.6.11 (or .12, maybe) kludges used debug build (--with-debug=full) variation of the rpm I believe a common backup strategy (works for myisam) is the following: -- -flush tables with read lock -lvmcreate -s (snapshot) -unlock tables -mount snapshot (readonly) -mysqld_safe --defaults-file=path-to/my.cnf2 --err-log=/tmp/elog2 where my.cnf2 specifies the snapshot mount point as the datadir -mysqldump --defaults-file=path-to/my.cnf2 --databases --opt testdb testdb.dump (..time passes..) Then after completion of the dump, umount and lvremove the snapshot --- This seems to me to be a reasonably useful recipe -- does anyone have issues with this statement? For innodb I have made what I think is appropriate adjustments to the config and procedure, but cannot get it to work. The first problem is that mysqld fails to start because it cannot open ibdata1 (..) for read-write (on the ro snapshot mount). I can get some encouragement of sorta-almost getting it to work by: -kludging the code in innobase/os/os0file.c to open the data files with O_RDONLY and changing the locking to F_RDLCK. -copying the ib_logfile[01] files to r/w disk and adjusting the innodb_log* config After the above, I can actually get the cnf2-server to load and even respond to some simple select queries. I did no extensive testing, but instead tried to run mysqldump. It seems to proceed nicely -- but after a while fails with .. InnoDB: Warning: we did not need to do crash recovery, but log scan InnoDB: progressed past the checkpoint lsn 0 93795910 up to lsn 0 93795920 050811 9:53:24 InnoDB: Error: Write to file /mnt/scratch/mysql/ibdata1 failed at offset 0 1048576. InnoDB: 16384 bytes should have been written, only -1 were written. .. 050811 9:53:24 InnoDB: Assertion failure in thread 46912496362752 in file fil0fil.c line 3924 InnoDB: Failing assertion: ret InnoDB: We intentionally generate a memory trap. .. server terminates Now I didn't try to debug mysqldump any further, because it seems there may be pervasive assumptions that the ibdata files are writable (I guess stemming from the multi-user concurrency, or something like that). Shame though, eh? Q? Perhaps there are some further mods similar to the flavor of my kludges, that allow innodb to run in a fully-readonly mode? Q? Perhaps the opt_readonly is aready part of the solution? Q? Perhaps there's a better way (preferably w/o buying InnoDB Hot Backup). Q? I do kinda feel that innodb (any database) really ought to allow a readonly mode. Am I unreasonable? SIDE ISSUE ** It *is* possible to get to get the dump to work by mounting the snapshot read-write! No kludging, no moving of the logfiles required. Q? Am I wrong to be squeamish about mounting lvm snapshots read-write? Q? Has anybody done such -- and verified that the dumps are valid? Q? Even if that works and is safe, am I out-of line asking for readonly? - Thanks all, for your forbearance! ...jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade 3.23 to 4.12 slowness
Im running a 2.8Ghz celeron P4, 1Gbyte RAM on a 160Gb HD (IDE). mysql show variables; +-++ | Variable_name | Value | +-++ | back_log| 50 | | basedir | /usr/ | | bdb_cache_size | 8388600 | | bdb_home| /var/lib/mysql/ | | bdb_log_buffer_size | 32768 | | bdb_logdir | | | bdb_max_lock| 1 | | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days| 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024 | | have_archive| NO | | have_bdb| YES | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv| NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam
Storing manually diggested Passwords with MD5
Hello all, Is there any function that I could use on a SQL statement to store a password on a table manually using an algorithm like MD5? Thanks, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing manually diggested Passwords with MD5
MySQL actually has an MD5() function:
mysql select MD5('password');
+--+
| MD5('password') |
+--+
| 5f4dcc3b5aa765d61d8327deb882cf99 |
+--+
1 row in set (0.18 sec)
Partha
--
Partha Dutta, Senior Consultant
MySQL Inc, NY, USA, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 11, 2005 7:21 PM
To: mysql@lists.mysql.com
Subject: Storing manually diggested Passwords with MD5
Hello all,
Is there any function that I could use on a SQL statement to store a
password on a table manually using an algorithm like MD5?
Thanks,
C.F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing manually diggested Passwords with MD5
I am sorry. I meant another thing and wrote something else.
If I issue: INSERT INTO table1 values ('username',MD5('password'))
I will have the password stored in the database with MD5.
What I actually need is a manual way to get the password back, that is
decoding it.
By using a software this is easy but what I want to know is by using a
simple query operation.
Is there any functions that I can use inside a SELECT statement that
would show the password decoded?
Thanks,
C.F.
C.F. Scheidecker Antunes wrote:
Hello all,
Is there any function that I could use on a SQL statement to store a
password on a table manually using an algorithm like MD5?
Thanks,
C.F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing manually diggested Passwords with MD5
C.F. Scheidecker Antunes wrote:
I am sorry. I meant another thing and wrote something else.
If I issue: INSERT INTO table1 values ('username',MD5('password'))
I will have the password stored in the database with MD5.
What I actually need is a manual way to get the password back, that is
decoding it.
By using a software this is easy but what I want to know is by using a
simple query operation.
Is there any functions that I can use inside a SELECT statement that
would show the password decoded?
MD5 is not reversible.
But you could do something like
SELECT password=MD5(?)
to see if the password they supplied matches the one that was previously
stored.
Thanks,
C.F.
C.F. Scheidecker Antunes wrote:
Hello all,
Is there any function that I could use on a SQL statement to store a
password on a table manually using an algorithm like MD5?
Thanks,
C.F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing manually diggested Passwords with MD5
C.F. Scheidecker Antunes wrote: I will have the password stored in the database with MD5. What I actually need is a manual way to get the password back, that is decoding it. The whole point of MD5 is that you cannot decode it once encoded. When someone enters their password, just MD5 what they entered and compare it to the database. If it matches, they got it right. If not, they didn't. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking Issue
Hi all , I have been experiencing intermittent locking issues with MYSQL. It appears that sometimes a query will lock reliease its lock, and is causing other queries to wait and wait until the connection limit is reached and i am locked out of the database. Has anyone ever had anything like this happen? The setup: Redhat 9.0 , Kernel 2,4,20-8smp mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 MyISAM Tables (And unless InnoDB can support fulltext or some other equivalent , migrating isnt an option at present) ext2fs Our Datbase Activity: We have a somewhat active website. Things run fairly smoothly for the most part , although we do have some slow queries from time to time. We have far more selects than updates , but updates are still reasonably active. Frequently , an update will get locked while a slower query is running. Sometimes we can experience a large backup waiting for a slow query , but typically everything sorts out once the slow query finishes. Rarely , however , a query will be in a locked state and will not let go of its lock. Subsequent updates lock , and subsequent selects lock. Eventually , if the above has happened , the connection table will fill up. We dont have any scripts that explicitly LOCK TABLES , aside from our backup script which uses mysqlhotcopy. Is it possible that the mysqlhotcopy LOCK TABLES could interfere with the locking from the website activity? I apologise for the vagueness of this request , I really dont know what direction would be best to further diagnose this. If you have any advice , it would be greatly appreciated. thanks for your time! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of a RaQ4?
I am hosting on a RaQ4 which is terribly underpowered. Within a minute of a reboot it's CPU and RAM are both red in the admin screen. The app is optimized as much as possible but it just gets too much traffic, too many MySQL connections. It's maxed out at 512K RAM. I have a generic 2.5GHz P4 machine sitting here - would it be significantly faster for MySQL? I know it's not a server class machine but at this point I'm more interested in handling the traffic than in reliability. Thanks for any thoughts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question in SERIALIZABLE transaction isolation level
In the transaction isolation level,Database send some duplicate entry message to me. Why? _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrade 3.23 to 4.12 slowness
Ok I appear to have narrowed down the issue. it appears to be the last table I had listed named 'user_account1': mysql describe user_account1; +-+--+--+-++---+ | Field | Type | Null | Key | Default| Extra | +-+--+--+-++---+ | id | bigint(20) | | | 0 | | | is_active | varchar(10) | | || | | last_login | date | | | -00-00 | | | reason | varchar(100) | YES | | NULL | | | term_reason | varchar(100) | YES | | NULL | | | last_login_time | time | | | 00:00:00 | | +-+--+--+-++---+ 6 rows in set (0.00 sec) When I removed the is_active and user_account1 from the following select statement it is immediate: SELECT userid, nickname, city, state, country, email, sex, birthday, marital_stat, num_children, height, weight, smoke, drink, born_again, image1, is_active FROM users1, user_stats1, user_wants1, user_account1 WHERE sex='m' AND userid=user_stats1.id AND userid=user_wants1.id AND userid=user_account1.id; Id still like to know why, but at least its a huge start. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Sebastian [EMAIL PROTECTED] wrote on 08/11/2005 01:19:30 PM: well i managed to solve the problem myself, and im no sql genius... i thought i had an index on maps_rating.map which i didn't.. adding an index on it improved the query. i think that is about all the improvement i can get.. but if there is still room for more speed i'd like to know.. Sebastian wrote: Jigal van Hemert wrote: Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query sorry for the lack of info. there are a couple of indexes on maps table, but i am not using them unless i use a where clause. not sure if the format is going to appear correctly on mailing list email, here is the info: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ /NULL/ 626 Using temporary; Using filesort 1 SIMPLE maps_rating ALL /NULL/ /NULL/ /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref PRIMARY PRIMARY 4 site.maps.userid 1 CREATE TABLE `maps` ( `id` int(10) unsigned NOT NULL auto_increment, `mip` smallint(6) NOT NULL default '0', `map` varchar(50) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `filename` varchar(50) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `mip` (`mip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; CREATE TABLE `maps_rating` ( `id` int(10) unsigned NOT NULL auto_increment, `map` int(10) NOT NULL default '0', `rating` smallint(6) NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `ipaddress` varchar(15) NOT NULL default '', `dateline` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; the query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage This is not to solve your problem (you already did that) but to respond to your request for possible additional optimizations. One of the basic principles I try to use when optimizing is to JOIN as little data as possible, even if it takes more than one step. What your original query does is to JOIN three tables then GROUP BY on the resulting combinations of records. If you eliminated all of the duplication from your secondary tables (maps_rating and user) by performing your AVG and COUNT in separate steps, you reduce the amount of data you need to reprocess through the GROUP BY by an order of magnitude. Less data = less time. Here is how I would approach your problem. I would create a temp table (or a static table if you run this often enough) that contains whatever statistics you want (your COUNTs, AVGs, etc.) then join that to the `maps` table to fill in the rest of the columns you wanted in your report. The whole query would resemble something like this: CREATE TEMPORARY TABLE tmpRatings(KEY(map)) SELECT map , avg(rating) as rating , count(id) as votes FROM maps_rating GROUP BY map; SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN user ON (user.userid = maps.userid) LEFT JOIN tmpRatings ON tmpRatings.map = maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage; Assuming an average of 10 ratings per map, you save at least 10x the processing time in your final query as compared to your original. We did add a little processing to create the statistics table, however the additional overhead is not nearly as much as we saved so the net gain will still be quite noticeable. Please give it a shot and let me know how it compares to your original. Thanks. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: upgrade 3.23 to 4.12 slowness
Jeff Lacki wrote: Ok I appear to have narrowed down the issue. it appears to be the last table I had listed named 'user_account1': mysql describe user_account1; +-+--+--+-++---+ | Field | Type | Null | Key | Default| Extra | +-+--+--+-++---+ | id | bigint(20) | | | 0 | | | is_active | varchar(10) | | || | | last_login | date | | | -00-00 | | | reason | varchar(100) | YES | | NULL | | | term_reason | varchar(100) | YES | | NULL | | | last_login_time | time | | | 00:00:00 | | +-+--+--+-++---+ 6 rows in set (0.00 sec) Is that user_account1.id field indexed? show create table user_account1; -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to determine right value for max_allowed_packet?
Hello. Maybe. Use --skip-extended-insert in this case. Sid Lane [EMAIL PROTECTED] wrote: I think I've foind the culprit: a problem (logical, not physical) had been discovered with a couple of tables which were fixed by truncating them in the production replication master and reloading them from a mysqldump of the corrected tables from the qc/dev database. the dump was done w/the -e (which makes sense) flag hence REALLY BIG individual insert statements even though the table itself wasn't that big nor were any individual rows. does this sound plausible? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz, httpd-2.0.54.tar.gz, php-5.0.4.tar.gz etc. on a Intel(R) Pentium(R) 4 CPU 2.40GHz with 1GB RAM this is just a temporary until we buy a new high end server. my-large.cnf is the configuration in the /etc/my.cnf CentOS release 4.0 (Final) is the linux distribution. what is the ideal configuration so that 20,000 user does not encounter the too many connections error when they browse our website. thank you very much. rgds, Joeffrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
