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!

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 SEL

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 ORDE

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_privileg

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 > UN

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

'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 lu

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 e

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

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

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/ __

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 t

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 0x808

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 W

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 connec

number of table joins in MySQL 5.xx

Is the maximum number of table joins still 31 in MySQL 5.xx?

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.

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 MySQ

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 w

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

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

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.).

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 r

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

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. A

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 'c

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 pre

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

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: 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 doc

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

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) > > W

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 alw

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 conn

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.

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

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 dow

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 Documen

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:\my

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

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.u

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 tab

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',

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 -