and some more on the root-access issue;
I tried the flush-privileges command and got the following; mysqladmin flush-privileges mysqladmin: reload failed; error: 'Access denied. You need the RELOAD privilege for this operation' - So, how can I set full access-rights to root the easiest way? (thank you) N.P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using one query to save data in 4 tables
Here... look at this: LOCK TABLES `presa_im` WRITE, `presa_im_titlu` WRITE, `presa_im_continut` WRITE; INSERT INTO `presa_im` (`nr`, `pag`, `ordine`) VALUES (5, 1, CEILING(RAND()*1000)); SELECT @ID:=LAST_INSERT_ID(); INSERT INTO `presa_im_title` VALUES(@ID, 'TITLE in Romanian', 'TITLE in English', 'TITLE in French'); INSERT INTO `presa_im_content` VALUES(@ID, 'Content in Romanian', 'Content in English', 'Content in Fench'); UNLOCK TABLES; This way you do not need to actualy capture the value of the last insert id from MySQL in your application... Gabriel PREDA www.amr.ro www.lgassociations.info - Original Message - From: James Black [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, March 26, 2005 12:14 AM Subject: re: using one query to save data in 4 tables -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if this would be possible, and which version of mysql would be needed. Basically, I want to store a user in one table, then get the id for the user I just saved, and store three more rows, each in a different database, using the user's id in the insert statements. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCRH8iikQgpVn8xrARAsw5AJ0SxPoMMcy1QUa1GNNEdfg51Q7Q8ACfYHg7 NdFWteuQU4JjSfx7yYS++9k= =JZD5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install Mysql----to ./mysql_install_db ERROR
Hello. Do you have previous installations of MySQL server? [1]+ Done ./mysqld_safe What is in the error log? See: http://dev.mysql.com/doc/mysql/en/starting-server.html mandrake 10.1 mysql 4.1.10a [EMAIL PROTECTED] bin]# ./mysql_install_db Installing all prepared tables ERROR: 1062 Duplicate entry 'localhost-root' for key 1 ERROR: 1062 Duplicate entry 'localhost-' for key 1 Fill help tables angin continue next steup also error [EMAIL PROTECTED] bin]#./mysqld_safe [1]4789 [EMAIL PROTECTED] bin]#Starting mysqld daemon with databases from /usr/local/mysql/var STOPPING server from pid file /usr/local/mysql/var/localhost.pid 050308 21:23:00 mysqld ended [1]+ Done ./mysqld_safe [EMAIL PROTECTED] bin]#./mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'(111) yen0622 [EMAIL PROTECTED] wrote: -- 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: access-trouble using root
Hello.
It seems that you already have the password set to 'my_password'.
But in MySQL access privilege system the host from you're connecting
also has the value. You may enable the general log and find the
user and the host which can't login. Check that you have those entries
in your mysql.user table. See:
http://dev.mysql.com/doc/mysql/en/privilege-system.html
http://dev.mysql.com/doc/mysql/en/query-log.html
I can't login to mysql neither via the shell as root using mysql -u
root -p (without the -p it works, but then I don't seem to be able to
do anything), neither can I login via webmin's interface using root
as my password and my assigned password.
You may check your permissions with SHOW GRANTS statement. See:
http://dev.mysql.com/doc/mysql/en/show-grants.html
If you have MySQL older than 4.1.2 use SELECT CURRENT_USER() to
obtain the user name for you session. See also:
http://dev.mysql.com/doc/mysql/en/resetting-permissions.html
X y [EMAIL PROTECTED] wrote:
Over on http://forums.mysql.com/read.php?10,20058,20058#msg-20058 I
posted a msg connected to me having trouble to login to my MySQL
database handler.
I might save some space here in your email by asking you to go to the
above forum-thread, and my last post in the thread goes;
Ok, so I did the following;
1. Stop mysqld and restart it with the --skip-grant-tables option as
described earlier.
2. Connect to the mysqld server with this command: shell mysql -u root
3. Issue the following statements in the mysql client: mysql UPDATE
mysql.user SET Password=PASSWORD('newpwd')
- WHERE User='root';
mysql FLUSH PRIVILEGES;
Replace ``newpwd'' with the actual root password that you want to use.
4. You should be able to connect using the new password.
I got the following result;
mysql UPDATE mysql.user SET Password=PASSWORD('my_password') WHERE
User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 5 Changed: 0 Warnings: 0
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
The thing is that this doesn't work, and I suspect that somehow,
webmin and my browser happened to remove some privileges of thr root
user while changing password through mentioned webmin.
I can't login to mysql neither via the shell as root using mysql -u
root -p (without the -p it works, but then I don't seem to be able to
do anything), neither can I login via webmin's interface using root
as my password and my assigned password.
My follow-up question is; where are these settings stored, and how can
I change back so that root has full access-rights again???
Thanks in advance!!!
N.P
--
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: Database access
Hello. Maybe these links will be helpful: http://dev.mysql.com/doc/mysql/en/privilege-system.html http://dev.mysql.com/doc/mysql/en/grant.html Russ [EMAIL PROTECTED] wrote: I run SuSE 9.1 with mysql 4.0.18. I have two databases plus the two created by my sql. I working on a new security program for one of the databases where the user will login to a webpage for information. The program is presently running on localhost. When I added the necessary user id and password for the program I noticed it shows up in all of the database priveliges. Is there a way to limit it to one database and specifically on table? Connection code: actual user and password are in code. Only SELECT authority is on the database $conn = mysql_connect(localhost, username, password) or die(mysql_error()); mysql_select_db(Lions,$conn) or die(mysql_error()); This works. But I don't want someone to beable to read the other databases or tables in the Lions database. Any help would be appreciated!!! -- 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: Character Set problem
Hello. See: http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html I've read through all the supposed fixes and other posts all over the place, but, to be honest, I'm confused. I'm not really sure what the fix is. Check that you have the charsets directory in c:\mysql\share. Stephen Moretti (cfmaster) [EMAIL PROTECTED] wrote: Hi there, I'm on mysql 4.1.10a on Windows 2003 Server. I'm getting : File 'c:\mysql\share\charsets\?.conf' not found (Errcode: 22) ^GCharacter set '#33' is not a compiled character set and is not specified in the 'c:\mysql\share\charsets\Index' file when some PHP applications try accessing their database. I know this is classified as Bug number 312 (http://bugs.mysql.com/bug.php?id=312). I've read through all the supposed fixes and other posts all over the place, but, to be honest, I'm confused. I'm not really sure what the fix is. Is there actually a fix? If there is a fix, would someone be kind enough to give me an idiots guide on what to do please? Regards Stephen -- 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]
InnodDB question / my.cnf
Hello I need little bit help i have 1 ibdata file (now) innodb_data_file_path=ibdata1:10M:autoextend:max:262M works fine but when i make innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend or autoextend:max:201 then my 4.1 mysql server will not start, cuase of a syntax error. In the Document. on mysql.com is the syntax ok. b.) what happens when ibdata1 is full (262MB)? with my.cnf entry innodb_data_file_path=ibdata1:10M:autoextend:max:262M thx bye richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.3-beta has been released
Hi, A new version of MySQL Community Edition 5.0.3-beta Open Source database management system has been released. This version now includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the first Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual open and resolved bugs in this version. Changes in release 5.0.3: Functionality added or changed: * New privilege CREATE USER was added. * Security improvement: The server creates `.frm', `.MYD', `.MYI', `.MRG', `.ISD', and `.ISM' table files only if a file with the same name does not already exist. (Thanks to Stefano Di Paola [EMAIL PROTECTED] for finding and informing us about this issue.) * Security improvement: User-defined functions should have at least one symbol defined in addition to the xxx symbol that corresponds to the main xxx() function. These auxiliary symbols correspond to the xxx_init(), xxx_deinit(), xxx_reset(), xxx_clear(), and xxx_add() functions. mysqld by default no longer loads UDFs unless they have at least one auxiliary symbol defined in addition to the main symbol. The --allow-suspicious-udfs option controls whether UDFs that have only an xxx symbol can be loaded. By default, the option is off. mysqld also checks UDF filenames when it reads them from the mysql.func table and rejects those that contain directory pathname separator characters. (It already checked names as given in CREATE FUNCTION statements.) See section 25.2.3.1 UDF Calling Sequences for simple functions, section 25.2.3.2 UDF Calling Sequences for aggregate functions, and section 25.2.3.6 User-defined Function Security Precautions. (Thanks to Stefano Di Paola [EMAIL PROTECTED] for finding and informing us about this issue.) * Support for the ISAM storage engine has been removed. If you have ISAM tables, you should convert them before upgrading. See section 2.10.1 Upgrading from Version 4.1 to 5.0. * Support for RAID options in MyISAM tables has been removed. If you have tables that use these options, you should convert them before upgrading. See section 2.10.1 Upgrading from Version 4.1 to 5.0. * Added support for AVG(DISTINCT). * ONLY_FULL_GROUP_BY no longer is included in the ANSI composite SQL mode. (Bug #8510) * mysqld_safe will create the directory where the UNIX socket file is to be located if the directory does not exist. This applies only to the last component of the directory pathname. (Bug #8513) * The coercibility for the return value of functions such as USER() or VERSION() now is ``system constant'' rather than ``implicit.'' This makes these functions more coercible than column values so that comparisons of the two do not result in Illegal mix of collations errors. COERCIBILITY() was modified to accommodate this new coercibility value. See section 12.8.3 Information Functions. * User variable coercibility has been changed from ``coercible'' to ``implicit.'' That is, user variables have the same coercibility as column values. * Boolean full-text phrase searching now requires only that matches contain exactly the same words as the phrase and in the same order. Non-word characters no longer need match exactly. * CHECKSUM TABLE returns a warning for non-existing tables. The checksum value remains NULL as before. (Bug #8256) * The server now includes a timestamp in the Ready for connections message that is written to the error log at startup. (Bug #8444) * Added SQL_NOTES session variable to cause Note-level warnings not to be recorded. (Bug #6662) * Allowed the service-installation command for Windows servers to specify a single option other than --defaults-file following the service name. This is for compatibility with MySQL 4.1. (Bug #7856)
ERROR 1105: Unknown error with flush logs
Hi, We are running mysql-4.0.22 on Gentoo Linux. We do regular mysqlhotcopy of all databases and do regular flush logs for transaction logs. However, My flush logs failed since yesterday, the mysqladmin flush-logs gave me error: /usr/bin/mysqladmin: refresh failed; error: 'Unknown error' When I login to server, do 'flush logs' in command line, got error ' ERROR 1105: Unknown error ' Anyone has idea on how to resolve this issue, please share with us. Thanks. Jennifer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need opinion on FmPro Migrator
Hi, all, I need to convert databases from FileMaker to MySQL. I searched the web and found this product FmPro Migrator I am wondering if people here have used it and know how well it works. Or are there any other way to do the conversion out there? Ted Zeng Adobe Systems Inc.
Re: Slow access Apache + PHP + MySQL
What is your MySql configuration file? What query is slow? Have you tested your query with mysql tool from a command line? What is your schema? What is the result of describe select ... I suggest you to look at dns because, if i'm not wrong, MySql tries to resolve ips and this can slow down connection time. (There is a option to skip this). Santino Cusimano At 11:21 -0500 28-03-2005, Andre Matos wrote: What kind of detail do you need? I checked the DNS, and it is seems to working fine. Andre On 3/27/05 10:02 AM, Santino [EMAIL PROTECTED] wrote: It could be a DNS problem, but you must give us more details. Santino At 10:47 -0500 26-03-2005, Andre Matos wrote: Hi List, I have 4 web based systems developed using PHP4 and MySQL accessed for 10 users. The Web Server and Database Server were running ok on a Mac OS X 10.3 G4 dual. However, since we move to a new server, the access becomes very slow. This was not expected since we move to a 64 bits high performance machine. Now, we are using MySQL version 4.1.9 with Apache 2.0.52 and PHP 4.3.10, all compiled and running on a Linux Fedora X86_64. My first thought was the systems, but since I have not changed 3 of the 4 systems, I start to look to the database. I monitored the MySQL using MySQL Administrator, but I couldn't identify any problem. It looks ok, but not completely sure if really is. The system administrator told me that could be the PHP session, but again, he also was not complete sure about this. It is a big problem since I need to check in 3 places: MySQL, Apache, or PHP. Does anyone had this kind of problem or has any suggestion or direction to help me to identify and solve this issue? Any help will be appreciated!!! Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUERY ordering clarification
Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY ordering clarification
Hello, Assume nothing. Use ORDER BY or re-think your approach to the problem. MarkP On Mon, 28 Mar 2005 09:43:09 -0800 (PST), Grant Giddens [EMAIL PROTECTED] wrote: Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Papadakis Head of RD Phaistos Networks, S.A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY ordering clarification
Grant Giddens [EMAIL PROTECTED] wrote on 03/28/2005 12:43:09 PM: Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ No, without a GROUP BY (which, unless told to do otherwise, sorts its results) or an ORDER BY clause in your query there is no guarantee that queries will return rows in the same order for each execution. If you need to have results in a particular order, you must use an ORDER BY clause to guarantee that order. You don't have to sort by column names, you can sort on the results of functions too (created in the same pattern as your sample query): ORDER BY IF(sku=$sku1, 1, if(sku=$sku2, 2, if(sku=$sku3,3,4))) Before I get flamed... Yes, if the query's results were cached then you should get the same rows in the same order for the same query. However, there is no guarantee that your query will return (or cache) the rows sorted by the sequence of the terms your IN clause. In fact I doubt that you would get many results that happened (randomly) to be in the order of the values as you specify them in your IN clause. To guarantee a record sequence, you must use an ORDER BY. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: QUERY ordering clarification
No, it won't. You'll have to add an ORDER BY clause using FIELD, like so: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) ORDER BY FIELD(sku, $sku1, $sku2, $sku3, $sku4) ASC For more information, see page 330 of the MySQL Cookbook, or look for FIELD in the MySQL docs: http://dev.mysql.com/doc/mysql/en/string-functions.html Eamon Daly - Original Message - From: Grant Giddens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 28, 2005 11:43 AM Subject: QUERY ordering clarification Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.03 Still crashes on win32
Warning, do not install 5.03 it still crashes on win32. This occured after very few minutes of testing. I will send more info as I locate it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.03 Still crashes on win32
Where do I look for the server stopping error. The .err file looks happy with no error listed after server crashes and stops. Here is the last entry... 050328 10:34:30 InnoDB: Started; log sequence number 0 43634 050328 10:34:30 InnoDB: Starting recovery for XA transactions... 050328 10:34:30 InnoDB: 0 transactions in prepared state after recovery 050328 10:34:30 [Warning] 'db' entry 'socaldata [EMAIL PROTECTED]' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE. 050328 10:34:30 [Warning] 'db' entry 'socaldata [EMAIL PROTECTED]' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE. 050328 10:34:35 [Note] MySQL: ready for connections. Version: '5.0.3-beta-nt' socket: '' port: 3306 Official MySQL binary - Original Message - From: Fredrick Bartlett [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, March 28, 2005 10:13 AM Subject: 5.03 Still crashes on win32 Warning, do not install 5.03 it still crashes on win32. This occured after very few minutes of testing. I will send more info as I locate it. -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: mysqlimport blocks all access to database
Yes I am appending to the end of an existing database. So why are rows 1 to N locked if I'm only adding rows at N+1? Wouldn't the write privileges apply to rows being modified? And during this period even an interactive mysql shell hangs until the mysqlimport completes. i.e. I the database is pretty much inaccessible until mysqlimport completes. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 12:30 AM To: [EMAIL PROTECTED] Subject: Re: mysqlimport blocks all access to database Hi Joseph, I have been trying to use mysqlimport to load a primarily read only database with data at regular intervals. My problem occurs when my tables are myisam. In this case all access to the database and the tables blocks until mysqlimport completes. The -lock-tables=false parameter does not help. Is this the normal operation? If not how can I still have concurrent read access to the database while mysqlimport is running? Correct me if I am wrong, but the write request has privilege. So ANY read request is queued until the write request finishes. Otherwise you would get inconsistent read results. Am I correct assuming that you are appending the imported data to the existing data in the database ? Best regards Nils Valentin Tokyo / Japan www.be-known-online.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
construct table for fast insert/select
Hi, I have tables with only 2 fields, key (char(30)) and value(blob), the value is about 2K. The table is going to store more than 20 million entries. I will keep inserting, selecting and deleting the table. I wonder what will the best way be to construct table, i.e., how should I do 'create table '. I know I need to index the key, but shall I also index the value? shall I set up key or primary key for the key? What is the way to construct table to achieve the best performance based on the fact that it's a simple (only 2 fileds) but giant table? Thanks very much. ~Grace - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
MySQL 5.0.3 --no-auto-rehash
Hi, I'm testing 5.0.3. I make a rpm update from 5.0.2. After it mysqld through mysqlamanager doesn't start. I'll be watching logs and I saw the problem was in mysql section on my.cnf. If I comment --no-auto-rehash it works. I can't find info on --no-auto-rehash. Someone can tell what is it? Thnx. Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: problem with mysql-max-5.0.3 for Solaris 8 32 bit
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It would appear that the package is for the 64-bit OS, even though it is listed as being for the 32-bit one. I see directories, that failed to install, of: /usr/local/mysql-max-5.0.3-beta-sun-solaris2.8-sparc-64bit/sql-bench I had gotten my file from http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.3-beta-sun-solaris2.8-sparc.pkg.gz/from/http://mysql.mirrors.pair.com/ - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCSGQkikQgpVn8xrARAqr1AJsFslyPkjRck+5uNTPbU3gxoLx9fwCeJEEn fdly9uy3J0L38pkizClQNzY= =hmti -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.3 --no-auto-rehash
At 16:30 -0300 3/28/05, Alejandro D. Burne wrote: Hi, I'm testing 5.0.3. I make a rpm update from 5.0.2. After it mysqld through mysqlamanager doesn't start. I'll be watching logs and I saw the problem was in mysql section on my.cnf. If I comment --no-auto-rehash it works. That's strange, for two reasons: - The [mysql] section shouldn't affect mysqld. - Options in my.cnf shouldn't be listed with leading dashes. The option should be no-auto-rehash in an option file, not --no-auto-rehash I can't find info on --no-auto-rehash. Someone can tell what is it? It's listed here: http://dev.mysql.com/doc/mysql/en/mysql.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED)
Classification: UNCLASSIFIED Caveats: NONE All, Having stability issues w/ Mysql 4.1.10 (innodb) on RH Linux 2.4.9-e.59smp running AS 2.1. DB crashes randomly during execution of various SQL code (all using user variables and temporarly tables and mostly simple selects, inserts, joins, etc.). Using source-compiled binaries (./configure --prefix /data/mysql-4.1.10 --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static). Database crashes about 3-5 times a day and usually recovers, but sometimes dies with: [ERROR] Can't start server: Bind on TCP/IP port: Address already in use [ERROR] Do you already have another mysqld server running on port: 3306 ? [ERROR] Aborting relevant log is: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=3 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x952cd40 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe3ff328, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808ed97 0x82f348a 0x80e1a59 0x80b87b8 0x808517a 0x808e977 0x809c2d0 0x82f0c27 0x831fc9a New value of fp=(nil) failed sanity check, terminating stack trace! Please read HYPERLINK http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=18 The manual page at HYPERLINK http://www.mysql.com/doc/en/Crashing.html http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 050328 15:07:27 mysqld restarted 050328 15:07:28 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... 050328 15:07:28 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 15 2363300476. InnoDB: Doing recovery: scanned up to log sequence number 15 236330 050328 15:07:28 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 90221, file name ./performdb-1-bin.000107 050328 15:07:29 InnoDB: Flushing modified pages from the buffer pool... 050328 15:07:29 InnoDB: Started; log sequence number 15 236330 /data/mysql-4.1.10/libexec/mysqld: ready for connections. Version: '4.1.10-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution Performed stack trace on all recent crashes with same results for each: 0x808ed97 handle_segfault + 423 0x82f348a pthread_sighandler + 162 0x80e1a59 write__9MYSQL_LOGP9Log_event + 1817 0x80b87b8 close_temporary_tables__FP3THD + 248 0x808517a cleanup__3THD + 106 0x808e977 end_thread__FP3THDb + 23 0x809c2d0 handle_one_connection + 928 0x82f0c27 pthread_start_thread + 171 0x831fc9a thread_start + 4 Anyone have an idea as the the meaning of the trace? Any help greatly appreciated. What other information can I provide? Thinking of trying standard binaries Thanks, Tate Classification: UNCLASSIFIED Caveats: NONE
my.cnf setup
I have lot of update/insert queries as well as select queries with a lot of sorts. Please let me know what settings should I use for optimal performance. I am using MyISAM tables. The db size is 30 G. Thanks - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: MySQL 5.0.3 --no-auto-rehash
OK, I confuse a little, this is the message: [EMAIL PROTECTED] init.d]# /etc/rc.d/init.d/mysql start Starting MySQL/usr/sbin/mysqlmanager: unknown option '--no-auto-rehash' in my.cnf exists no-auto-rehash, if I comment this line mysqld starts up without errors. maybe my.cnf came from 5.0.2 and this option it's not supported on 5.0.3? Alejandro. On Mon, 28 Mar 2005 14:21:28 -0600, Paul DuBois [EMAIL PROTECTED] wrote: At 16:30 -0300 3/28/05, Alejandro D. Burne wrote: Hi, I'm testing 5.0.3. I make a rpm update from 5.0.2. After it mysqld through mysqlamanager doesn't start. I'll be watching logs and I saw the problem was in mysql section on my.cnf. If I comment --no-auto-rehash it works. That's strange, for two reasons: - The [mysql] section shouldn't affect mysqld. - Options in my.cnf shouldn't be listed with leading dashes. The option should be no-auto-rehash in an option file, not --no-auto-rehash I can't find info on --no-auto-rehash. Someone can tell what is it? It's listed here: http://dev.mysql.com/doc/mysql/en/mysql.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: MySQL 5.0.3 --no-auto-rehash
At 17:50 -0300 3/28/05, Alejandro D. Burne wrote: OK, I confuse a little, this is the message: [EMAIL PROTECTED] init.d]# /etc/rc.d/init.d/mysql start Starting MySQL/usr/sbin/mysqlmanager: unknown option '--no-auto-rehash' in my.cnf exists no-auto-rehash, if I comment this line mysqld starts up without errors. maybe my.cnf came from 5.0.2 and this option it's not supported on 5.0.3? --no-auto-rehash has never been a mysqld option. It's a mysql option. It shouldn't be in any my.cnf file other than the [mysql] section. What does your my.cnf file look like? Alejandro. On Mon, 28 Mar 2005 14:21:28 -0600, Paul DuBois [EMAIL PROTECTED] wrote: At 16:30 -0300 3/28/05, Alejandro D. Burne wrote: Hi, I'm testing 5.0.3. I make a rpm update from 5.0.2. After it mysqld through mysqlamanager doesn't start. I'll be watching logs and I saw the problem was in mysql section on my.cnf. If I comment --no-auto-rehash it works. That's strange, for two reasons: - The [mysql] section shouldn't affect mysqld. - Options in my.cnf shouldn't be listed with leading dashes. The option should be no-auto-rehash in an option file, not --no-auto-rehash I can't find info on --no-auto-rehash. Someone can tell what is it? It's listed here: http://dev.mysql.com/doc/mysql/en/mysql.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Re: MySQL 5.0.3 --no-auto-rehash
Ok, in my first post I wrote ...I'll be watching logs and I saw the problem was in mysql section on my.cnf I don't understand why, but if I uncomment it doesn't work. My my.cnf: # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking #skip-innodb key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M
RE: MySQL account permissions
On Monday, March 28, 2005 15:07, Philippe Reynolds wrote: Hi, I've just create an account and given it all privileges for database_name.*. However when I try to 'load data infile' it tell's me that the account doesn't permit it. When I use the 'root' account everything is fine. Can you guys help? Cheers Phil The user needs the FILE priv. This is a global priv. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
number of table joins in MySQL 5.xx
Is the maximum number of table joins still 31 in MySQL 5.xx?
RE: Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED)
Classification: UNCLASSIFIED Caveats: NONE Implemented latest 4.1.10a-standard-log binary version and saw another crash w/in about 3 hrs. Here's the log: 050328 15:39:35 mysqld started 050328 15:39:36 InnoDB: Started; log sequence number 15 2379024139 /data/mysql/bin/mysqld: ready for connections. Version: '4.1.10a-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=6 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x96225e90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe7f458, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808b193 0x82debe8 0x80dbbf8 0x80b3caf 0x8081c2f 0x808ad8b 0x8098c56 0x82dc39c 0x8305d2a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=3549 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 050328 17:22:04 mysqld restarted 050328 17:22:04 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... 050328 17:22:04 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 15 2433246331. InnoDB: Doing recovery: scanned up to log sequence number 15 2435610788 050328 17:22:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 10782286, file name ./performdb-1-bin.000111 050328 17:22:06 InnoDB: Flushing modified pages from the buffer pool... 050328 17:22:06 InnoDB: Started; log sequence number 15 2435610788 /data/mysql/bin/mysqld: ready for connections. Version: '4.1.10a-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) And the stack trace: 0x808b193 mysql_unlock_read_tables__FP3THDP13st_mysql_lock + 131 0x82debe8 gbksortorder + 8 0x80dbbf8 mysql_prepare_update__FP3THDP13st_table_listT1PP4ItemUiP8st_order + 248 0x80b3caf yyparse__FPv + 59439 0x8081c2f sql_type__C10Field_geomR6String + 367 0x808ad8b mysql_errno_to_sqlstate + 43 0x8098c56 __static_initialization_and_destruction_0 + 5974 0x82dc39c my_strntol_8bit + 172 0x8305d2a canonicalize + 530 This one looks a bit different that the last ... Tate -Original Message- From: Baumrucker, Christopher T Mr ITA-IC/Lockheed Martin [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 3:22 PM To: 'mysql@lists.mysql.com' Subject: Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE All, Having stability issues w/ Mysql 4.1.10 (innodb) on RH Linux 2.4.9-e.59smp running AS 2.1. DB crashes randomly during execution of various SQL code (all using user variables and temporarly tables and mostly simple selects, inserts, joins, etc.). Using source-compiled binaries (./configure --prefix /data/mysql-4.1.10 --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static). Database crashes about 3-5 times a day and usually recovers, but sometimes dies with: [ERROR] Can't start server: Bind on TCP/IP port: Address already in use [ERROR] Do you already have another mysqld server running on port: 3306 ? [ERROR] Aborting relevant log
Locking and MERGE tables
I'm sure the answer is a grim one, but suppose I have tables foo_1 and foo_2, each with unique primary keys. I then create a MERGE table named foo. I have a routine which moves rows from one table to another thusly: LOCK TABLES foo_1 WRITE, foo_2 WRITE; INSERT INTO foo_2 SELECT * FROM foo_1 WHERE id = 100; DELETE FROM foo_1 WHERE id = 100; UNLOCK TABLES; Unfortunately, this seems to completely hose any selects on the MERGE table: all selects die with read_const: Got error 127 when reading table until I issue FLUSH TABLES. Is there a Right Way to move a row from foo_1 to foo_2 short of explicitly locking foo? It seems unwieldy to have to supply the names of any and all MERGE tables to my routine. Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED)
Hi, It seems your latest resolve is wrong. (perhaps you've done it against 4.1.10 symbols ?) Doing it on 4.1.10a symbols gives me : 0x808b193 handle_segfault + 423 0x82debe8 pthread_sighandler + 184 0x80dbbf8 MYSQL_LOG::write(Log_event *) + 1564 0x80b3caf close_temporary_tables(THD *) + 247 0x8081c2f THD::cleanup(void) + 119 0x808ad8b end_thread(THD *, bool) + 19 0x8098c56 handle_one_connection + 950 0x82dc39c pthread_start_thread + 220 0x8305d2a thread_start + 4 which basically is the same than the ones you got before. Regards, Jocelyn C. Tate Baumrucker wrote: Classification: UNCLASSIFIED Caveats: NONE Implemented latest 4.1.10a-standard-log binary version and saw another crash w/in about 3 hrs. Here's the log: 050328 15:39:35 mysqld started 050328 15:39:36 InnoDB: Started; log sequence number 15 2379024139 /data/mysql/bin/mysqld: ready for connections. Version: '4.1.10a-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=6 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x96225e90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe7f458, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808b193 0x82debe8 0x80dbbf8 0x80b3caf 0x8081c2f 0x808ad8b 0x8098c56 0x82dc39c 0x8305d2a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=3549 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 050328 17:22:04 mysqld restarted 050328 17:22:04 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... 050328 17:22:04 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 15 2433246331. InnoDB: Doing recovery: scanned up to log sequence number 15 2435610788 050328 17:22:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 10782286, file name ./performdb-1-bin.000111 050328 17:22:06 InnoDB: Flushing modified pages from the buffer pool... 050328 17:22:06 InnoDB: Started; log sequence number 15 2435610788 /data/mysql/bin/mysqld: ready for connections. Version: '4.1.10a-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) And the stack trace: 0x808b193 mysql_unlock_read_tables__FP3THDP13st_mysql_lock + 131 0x82debe8 gbksortorder + 8 0x80dbbf8 mysql_prepare_update__FP3THDP13st_table_listT1PP4ItemUiP8st_order + 248 0x80b3caf yyparse__FPv + 59439 0x8081c2f sql_type__C10Field_geomR6String + 367 0x808ad8b mysql_errno_to_sqlstate + 43 0x8098c56 __static_initialization_and_destruction_0 + 5974 0x82dc39c my_strntol_8bit + 172 0x8305d2a canonicalize + 530 This one looks a bit different that the last ... Tate -Original Message- From: Baumrucker, Christopher T Mr ITA-IC/Lockheed Martin [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 3:22 PM To: 'mysql@lists.mysql.com' Subject: Mysql 4.1.10 on Linux 2.4.9-e.59smp crash (UNCLASSIFIED) Classification: UNCLASSIFIED Caveats: NONE All, Having stability issues w/ Mysql 4.1.10 (innodb) on RH Linux 2.4.9-e.59smp running AS 2.1. DB crashes randomly during execution of various SQL code (all using user variables and temporarly tables and mostly
SELECT help
Hi, I am tring to do a select from 2 tables. Table1: sku title Table 2: sku feature SELECT table1.title, table2.feature FROM table1, table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seems to work to some extint, but I am getting way too many results (more than 3). It's returning all combinations of sku and feauture even if they don't share the same sku. I modified the select to: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seemed to work almost correctly. I have some items in table2 that don't have a feature and therefor don't have a row associated with them. For example, if I have 3 items in each table, the above select works fine. If I have 3 items in table1 and 2 items in table2 the above query only gives me 2 results. table1 will always be fully populated and table2 might be missing some features. How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
You want a LEFT JOIN: SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 USING (sku) WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC I strongly suggest picking up Paul DuBois' MySQL: http://www.kitebird.com/mysql-book/ Eamon Daly - Original Message - From: Grant Giddens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 28, 2005 6:00 PM Subject: SELECT help Hi, I am tring to do a select from 2 tables. Table1: sku title Table 2: sku feature SELECT table1.title, table2.feature FROM table1, table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seems to work to some extint, but I am getting way too many results (more than 3). It's returning all combinations of sku and feauture even if they don't share the same sku. I modified the select to: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seemed to work almost correctly. I have some items in table2 that don't have a feature and therefor don't have a row associated with them. For example, if I have 3 items in each table, the above select works fine. If I have 3 items in table1 and 2 items in table2 the above query only gives me 2 results. table1 will always be fully populated and table2 might be missing some features. How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? Thanks, Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
Gran Giddens writes: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC ... How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? This is a job for 'LEFT JOIN' :) Given this data from your described tables: mysql select * from table1; +--+---+ | sku | title | +--+---+ |1 | A | |2 | B | |3 | C | +--+---+ 3 rows in set (0.00 sec) mysql select * from table2; +--+-+ | sku | feature | +--+-+ |1 | a | |1 | aa | |2 | b | |2 | bb | |2 | bbb | +--+-+ 5 rows in set (0.00 sec) SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 using (sku) WHERE table1.sku in (1, 2, 3) ORDER BY FIELD(table1.sku, 1, 2, 3) ASC mysql SELECT table1.title, table2.feature - FROM table1 LEFT JOIN table2 using (sku) - WHERE table1.sku in (1, 2, 3) - ORDER BY FIELD(table1.sku, 1, 2, 3) ASC - ; +---+-+ | title | feature | +---+-+ | A | a | | A | aa | | B | bbb | | B | b | | B | bb | | C | NULL| +---+-+ 6 rows in set (0.04 sec) Take a look at the manual for 'LEFT JOIN' to see where I came up with this information. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION, LIMIT, and FOUND_ROWS()
If I use the LIMIT clause without using SQL_CALC_FOUND_ROWS in a non-UNION statement, MySQL returns the number of rows found up to the LIMIT. Using SQL_CALC_FOUND_ROWS forces MySQL to keep going on checking all hits, and that affects performance. However, if I use the LIMIT clause at the end of a UNION query, MySQL returns the number of all matches even if I don't use SQL_CALC_FOUND_ROWS! This is causing performance problems because I want MySQL to stop counting all the matches as soon as it finds hits matching the limit. How do I force MySQL quit calculating all the hits and stick to the LIMIT clause in UNION queries? In other words, how do I force my SQL to execute the UNION statement the same way it executes a non-UNION statement with a LIMIT clause but no SQL_CALC_FOUND_ROWS? I appreciate your help. Homam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to use LIMIT in both UNION ALL statement and then ORDER?
MySQL seems to let me use the LIMIT clause in both parts of a UNION ALL query, but as soon as I add an ORDER BY CLAUSE, it gives me a syntax error. For example, this query executes fine: SELECT * FROM A WHERE X = 1 LIMIT 1000 UNION ALL SELECT * FROM B WHERE Y = 1 LIMIT 1000 But this returns an error: SELECT X, Y FROM A WHERE W = 1 LIMIT 1000 UNION ALL SELECT X, Y FROM B WHERE W = 1 LIMIT 1000 ORDER BY X Any way to let sort the result other than a temp table? Thanks, Homam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'Can't connect to local MySQL server....' error
hi... a server went from RH8 to FC2. it appears that the guy who did the upgrade didn't perfrom any backups... i get a 'Can't connect to local MySQL server through socket...' error. i've tried to 'fix' the tables 'mysql_fix_privilege_tables' with no luck... i've tried to start/restart with no luck. i've lloked through google/mysql with no luck... any ideas as to what might be causing the problems... if i can get the daemon started, i'll (hopefully) be ok... thanks bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to use LIMIT in both UNION ALL statement and then ORDER?
Nevermind, I found in the documentation that I need to paranthesize the SELECT parts of the UNION ALL, like this: (SELECT X, Y FROM A WHERE W = 1 LIMIT 1000) UNION ALL (SELECT X, Y FROM B WHERE W = 1 LIMIT 1000) ORDER BY X --- Homam S.A. [EMAIL PROTECTED] wrote: MySQL seems to let me use the LIMIT clause in both parts of a UNION ALL query, but as soon as I add an ORDER BY CLAUSE, it gives me a syntax error. For example, this query executes fine: SELECT * FROM A WHERE X = 1 LIMIT 1000 UNION ALL SELECT * FROM B WHERE Y = 1 LIMIT 1000 But this returns an error: SELECT X, Y FROM A WHERE W = 1 LIMIT 1000 UNION ALL SELECT X, Y FROM B WHERE W = 1 LIMIT 1000 ORDER BY X Any way to let sort the result other than a temp table? Thanks, Homam -- 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: Is there a way to use LIMIT in both UNION ALL statement and t hen ORDER?
On Monday, March 28, 2005 21:07, Homam S.A. wrote: MySQL seems to let me use the LIMIT clause in both parts of a UNION ALL query, but as soon as I add an ORDER BY CLAUSE, it gives me a syntax error. For example, this query executes fine: SELECT * FROM A WHERE X = 1 LIMIT 1000 UNION ALL SELECT * FROM B WHERE Y = 1 LIMIT 1000 But this returns an error: SELECT X, Y FROM A WHERE W = 1 LIMIT 1000 UNION ALL SELECT X, Y FROM B WHERE W = 1 LIMIT 1000 ORDER BY X (SELECT X, Y FROM A WHERE W = 1 LIMIT 1000) UNION ALL (SELECT X, Y FROM B WHERE W = 1 LIMIT 1000) ORDER BY X Without the parens, this looks like an order by on just the second query, and since this is after the LIMIT clause that is invalid. It should work fine with the parens. Any way to let sort the result other than a temp table? Thanks, Homam -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Can't connect to local MySQL server....' error
On Monday, March 28, 2005 21:36, bruce wrote: hi... a server went from RH8 to FC2. it appears that the guy who did the upgrade didn't perfrom any backups... i get a 'Can't connect to local MySQL server through socket...' error. i've tried to 'fix' the tables 'mysql_fix_privilege_tables' with no luck... i've tried to start/restart with no luck. i've lloked through google/mysql with no luck... any ideas as to what might be causing the problems... if i can get the daemon started, i'll (hopefully) be ok... Is there anything in the error log? You could try starting it from the command line to see what errors you get. The following will work assuming you installed using the rpm's. Otherwise the location of mysqld and the user may differ. #su - mysql #/usr/sbin/mysqld Run this and see what errors are reported. thanks bruce [EMAIL PROTECTED] -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why doesn't MySQL cache queries that start with parenthesis?
I have a query of the form: (SELECT A, B from X ORDER BY A LIMIT 1000) UNION ALL (SELECT A, B from Y ORDER BY A LIMIT 1000) ORDER BY A I thought may be each query needs to start with SELECT, so I wrapped the above query in a derived-table expression like: SELECT * FROM ( (SELECT A, B from X ORDER BY A LIMIT 1000) UNION ALL (SELECT A, B from Y ORDER BY A LIMIT 1000) ORDER BY A ) as MyAlias But MySQL refuses to execute it and gave me this error: Error Code : 1248 Every derived table must have its own alias Now when I gave each SELECT part of the UNION query its own alias, as the above error message suggests, MySQL still won't execute it and gave me a syntax error. Is there a way to force MySQL to cache the above query, or at least fool it into caching it? Thanks, Homam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why doesn't MySQL cache queries that start with parenthesis? (further info)
It seems that MySQL freaks out whenever it seems something that looks like a derive table and refuses it to cache. Even a non-UNION query like: SELECT * FROM (SELECT * FROM X WHERE A = 5) AS DerivedTable Won't be cached. I read a comment in the documentation that if you put SQL_CACHE in the SELECTs of the parenthesized queries, it will cache the individual queries: http://dev.mysql.com/doc/mysql/en/query-cache.html That's not true. It won't cache even the parenthsized queries, and the execution time is still the same with SQL_CACHE and not. I have the query cache configured to cache every query unless I explicitly tell not to, so there's nothing in my configuration that prevents from caching. This is very frustrating. It seems everything time I find a solution to an obstacle, I stumble across a nastier one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maximum number of indexes
hi all, in a table of say 100 fields, how many (maximum )numbers of indexes can be created... thx in adv. - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
