Re: why can't innodb_log_file_size change
You should be able to do this by: 1) Stopping the server cleanly 2) Removing the log files 3) Restarting the server with the new log file sizes set in your .cnf Chris Dathan Vance Pattishall wrote: Trying to increase my innodb_log_file_size I get this message 031027 16:01:02 InnoDB: Data file ./ibdata2 did not exist: new to be created 031027 16:01:02 InnoDB: Setting file ./ibdata2 size to 2000 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 78643200 bytes! 031027 16:01:57 Can't init databases 031027 16:01:57 Aborting 031027 16:01:57 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 031027 16:01:57 /usr/local/mysql/bin/mysqld: Shutdown Complete The mysql doc says this: innodb_log_file_size Size of each log file in a log group in megabytes. Sensible values range from 1M to 1/nth of the size of the buffer pool specified below, where n is the number of log files in the group. The bigger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But bigger log files also mean that recovery will be slower in case of a crash. The combined size of log files must be 4 GB on 32-bit computers. The default is 5M. It doesn't say anything about not being able to increase the size of the log file after its been set. Any work a rounds? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication of foreign key constraint names (InnoDB) inconsistent
Description: It is possible to get into a situation where foreign key constraints on a replication slave have different labels to the same foreign key constraint on the master. This normally causes replication to fail when a drop of a foreign key on the master is attempted (although presumably it could, in a particularly unfortunate circumstance, drop the wrong constraint for a given table). How-To-Repeat: Run the following on the master: CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY ); Then, on the slave run: CREATE TABLE t2 ( b INT NOT NULL PRIMARY KEY, FOREIGN KEY (b) REFERENCES t1 (a) ); And finally, on the master, run: CREATE TABLE t3 ( c INT NOT NULL PRIMARY KEY, FOREIGN KEY (c) REFERENCES t1 (a) ); If you now go into the master and look at the SHOW CREATE TABLE output for table t3 you should see something like: *** 1. row *** Table: t3 Create Table: CREATE TABLE `t3` ( `c` int(11) NOT NULL default '0', PRIMARY KEY (`c`), CONSTRAINT `0_15` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ) TYPE=InnoDB while in the slave you will see: *** 1. row *** Table: t3 Create Table: CREATE TABLE `t3` ( `c` int(11) NOT NULL default '0', PRIMARY KEY (`c`), CONSTRAINT `0_17` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ) TYPE=InnoDB Note the constraint name on the master is `0_15` while on the slave it is `0_17`. Fix: There are two workarounds at present: 1) Don't ever create a foreign key anywhere on the slave (unless it comes through the binlog from the master). This works fine until someone makes a mistake... 2) Accept that replication will halt. Drop the key from the master, then manually drop the key from the slave; set the slave skip counter to 1 and restart the slave. This is something of a problem if 100% uptime is expected/required. I would imagine this problem would also manifest if restoring a slave from a mysqldump of a master, or using any other mechanism that is not guaranteed to create the foreign keys in exactly the order they were created on the master. Making the parser and InnoDB foreign key management systems aware of constraint names being indicated in the foreign key definition would eliminate this problem: if I could reliably use a SHOW CREATE TABLE on the master and be guaranteed that either the table will create with the same foreign key labels, or will fail with an error if the labels are already in use elsewhere, then the problem will go away. If the constraint names also held the name of the table to which they are tied, the problem of collisions would also disappear: for example, CONSTRAINT `test_t3_0_15` would be a suitable label for the key on table t3 in database test. Submitter-Id: submitter ID Originator: [EMAIL PROTECTED] Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: Foreign key label replication inconsistent Severity: serious Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.15-standard (Official MySQL-standard binary) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.15, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.15-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 10 min 59 sec Threads: 3 Questions: 30 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 1 Queries per second avg: 0.046 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.4.18-3smp #1 SMP Thu Apr 18 07:27:31 EDT 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-113) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 13 16:32 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Mar 5 2003 /lib/libc-2.2.5.so -rw-r--r--1 root root 2312442 Mar 5 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 5 2003 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client'
Re: 2 Query with same criteria giving different number of rows
You're getting a distinct on just the prod_num in the first query, but are requesting distinct prod_num, description, line, and content in the second one: if any of those last three columns are different between rows, you will see ones additional to the count you get in the first query. You either need to count the same set of distinct columns in the first query or GROUP BY prod_num in the second. http://www.mysql.com/doc/en/SELECT.html http://www.mysql.com/doc/en/GROUP-BY-Functions.html Chris Hector Del Curto wrote: I have this 2 queries: SELECT COUNT(DISTINCT(LEFT(p.prod_num,5))) FROM products p, tlines l, prod_line pl WHERE l.Line = 'Solaris' AND l.id=pl.line_id AND pl.prod_id = p.id_product SELECT DISTINCT(LEFT(p.prod_num,5))as prod_num, p.description AS description,l.Line as line,l.Content as content FROM products p, tlines l, prod_line pl WHERE l.Line = 'Solaris' AND l.id=pl.line_id AND pl.prod_id = p.id_product The only difference between them is that in one I want the count of rows and on the other I want the columns. When I run the queries I get for example 24 as result of the count and on the other query I get 26 records. In those 26 records I get 2 prod_num duplicated when I'm asking for DISTINCT. Now, the other thing is if I only leave the prod_num column the number of rows are the same, if I add another column it changes to 26. I know I have some duplicates on the second table, and that's affecting the results, but I don't know why. Any hint on why is that happening? Thanks, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inconsistent replication of a DELETE in InnoDB
Hi, As I understand it any statement that fails with a foreign key error should not be written to the binlog and subsequently should not execute on the slave. Referring to the InnoDB manual, section 4.5: If a statement fails, for example, because of a foreign key violation, then the statement is not written to the binlog, and consequently not replicated to the slave Based on this I am wondering why I might get the following entry in my binlog: #030702 17:17:14 server id 1 log_pos 564513531 Query thread_id=6313269 exec_time=0 error_code=1217 use image; SET TIMESTAMP=1057191434; delete from image.image_records where image_collection_id=228; (where error_code=1217 is, according to http://www.mysql.com/doc/en/Error-returns.html, ER_ROW_IS_REFERENCED). This then leads into another issue. Because this command was written to the binlog it was executed on the slave. Presumably it resulted in the same return code (1217), as one mght expect with consistent data between master and slave, as the slave replication did not halt. However, it appears that the order in which InnoDB deletes records is non-deterministic, because a different set of rows were removed from the slave than from the master (suggesting they were deleted in a different order). This again makes me wonder why such a statement could make any permanent change to either the master or the slave, as I would expect the transaction in which it runs to be rolled back when the command fails. Is this an issue in the InnoDB transaction model that is known? For some background info: This table uses self-referencing foreign keys (pigs ear joins): row x in the table can reference row y. There are no cascading delete/update rules on the foreign key MySQL version is 4.0.12, running on Redhat 7.3 In grepping several gigs of logfiles this is the only occurence of error_code=1217 that I can find. I've not had a chance to experiment heavily with reproducing this issue but will attempt to do so shortly and raise an appropriate report with suitable schema and data if necessary. For now, if anyone could shed some light I'd be most appreciative. Regards, Chris Tucker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: incorrect SUM() results
You're running a join on table two which will result in the following set (if you remove the group by and sum()s): ABCCORP 500, 150, 350 (from table 1, row 1, join table 2, row 1) ABCCORP 500, 50, 300 (from table 1, row 1, join table 2, row 2) ABCCORP 500, 50, 250 (from table 1, row 1, join table 2, row 3) ABCCORP 300, NULL,NULL (from table 1, row 2, no match in table 2) If you sum those up you'll see that the results MySQL is giving are correct. Unfortunately I don't think you can do what you want to do without subselects or using two queries: effectively, you want to sum a set of summations. Try using a query like: CREATE TEMPORARY TABLE my_results SELECT TABLE1.accountno, TABLE1.invtotal as INVTOTAL, SUM(TABLE2.PAYMENT) as PAYMENT, TABLE1.invtotal - SUM( TABLE2.PAYMENT) as BALANCE FROM TABLE1 LEFT JOIN TABLE2 ON TABLE2.invoiceno = TABLE1.invoiceno GROUP BY TABLE1.accountno, TABLE1.invoiceno ORDER BY TABLE1.accountno and then do: SELECT accountno, sum(invtotal), sum(invtotal) - sum(balance) FROM my_results GROUP BY accountno; I've not tested that, but it should do something like what you want. Cheers, Chris Shaun Callender wrote: Hello, I'm using mySQL 4.0 trying to solve what I think is a simple problem. to calculate the balance by account # can this be done without the use of temporary tables ? TABLE 1 contains ACCOUNT #, INVOICE #, INV TOTAL TABLE 2 contains there can be multiple payment records associated with an invoice ACCOUNT #, INVOICE #, PAYMENT, PAYMENT DATE example TABLE 1 : ABCCORP, 1000, 500$ TABLE 1 : ABCCORP, 1001, 300$ TABLE 2 : ABCCORP, 1000, 150$, 2003-01-01 TABLE 2 : ABCCORP, 1000, 50$, 2003,02-01 TABLE 2 : ABCCORP, 1000, 50$, 2003,03-01 SQL STMT select TABLE1.accountno, SUM(TABLE1.invtotal) as INVTOTAL, SUM(TABLE2.PAYMENT) as PAYMENT SUM(TABLE1.invtotal ) - SUM( TABLE2.PAYMENT) as BALANCE LEFT JOIN TABLE2 ON TABLE2.invoiceno = TABLE1.invoiceno GROUP BY TABLE1.accountno ORDER BY TABLE1.accountno the result set will show which is wrong. ABCCORP,1800$,200$,1600$ It should be ABCCORP,800$,200$,300$ How can this be done correctly ? -- Shaun Callender (RSA Software Inc.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: droping foreign key
Upgrading to 4.0.13 will allow you to drop foreign keys. The innodb manual has info: http://innodb.com/ibman.html Chris Joe Gaffney wrote: Can someone help with trying to remove a foreign key constraint from a table. Each time I run the alter table statement to drop the foreign key I get the following error: Error Code : 1005 Can't create table Here is the table structure and the alter table statement I am executing: ALTER TABLE cgg_goals DROP FOREIGN KEY goal_id CREATE TABLE `cgg_goals` ( `GOAL_ID` int(11) NOT NULL auto_increment, `GOAL_DESC` varchar(100) default NULL, `HOUSEHOLD_ID` int(11) NOT NULL default '0', `RISK_LEVEL_ID` int(11) default '0', `GOAL_TYP_ID` int(11) default NULL, `YRS_TO_TARGET` double default '0', `TARGET_VALUE` double default '0', `MONTH_INFLOW` double default NULL, `MONTH_OUTFLOW` double default NULL, PRIMARY KEY (`GOAL_ID`,`HOUSEHOLD_ID`), KEY `INDX_HOUSEHOLD_ID` (`HOUSEHOLD_ID`), KEY `INDX_RISK_LEVEL_ID` (`RISK_LEVEL_ID`), KEY `INDX_GOAL_ID` (`GOAL_ID`), FOREIGN KEY (`RISK_LEVEL_ID`) REFERENCES `cgg_risk_level_def` (`RISK_LEVEL_ID`), FOREIGN KEY (`GOAL_ID`) REFERENCES `cgg_goal_def` (`GOAL_ID`), FOREIGN KEY (`HOUSEHOLD_ID`) REFERENCES `cgg_households` (`HOUSEHOLD_ID`) ) TYPE=InnoDB; I'm using version 4.0.12 running of Win2000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table blocks other tables!
You say you are using replication. In this situation, if you make an update to the master (using ALTER...) that takes a long time, this will get serialized into the binary log as normal and block all subsequent queries from executing on the slave until it has completed. One of the issues with MySQL replication is that it serializes the queries from the master into a single stream. I've done a rather poor job of explaining this. It's easiest understood when you think about how replication works: the master serializes commands out to a log file, which is then read, command by command, by the slave (on 4.0 servers by an IO thread, on 3.23 servers by a combined IO/SQL thread). The slave then executes each command as it comes in, command by command. If the next command takes a long time, it will be a long time before the following command can be executed. Imagine you have two commands in the queue that take the following times: 1) ALTER some big table : 303 secs 2) UPDATE some table SET some important thing : 1 sec The ALTER will begin executing on the slave and will not complete for 303 seconds. For this time, no more commands will be read from the slave and executed: it will wait until the ALTER is complete, and then carry on again. This is necessary to maintain consistency (imagine the UPDATE modified the same table that was just ALTER'd). Anyway, that may explain your problem. Or it may be way off base. :) Hope it helps a little, Chris Dan Edwards wrote: I have a mysql server containing multiple databases, one of the databases is very important that it is not slowed down by other databases. This database is used for a real time game server (card games), and any stalls causes all players to stall, they hate that. I've mostly solved this problem with a master/slave setup. The problem now is the other databases in the same mysql instance are getting larg. When I make changes to them with alter table, it can slow down and even block queries to the other databases for minutes at a time. My question is how do I prevent alter table in one database from blocking queries in another database? What I need is some way to say that any queries to this database (or specific tables) have top priority, always. Thanks for any assistance! Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
What platform are you on? If you're on windows you probably won't have less installed, in which case you'll need to either (a) install it or (b) use a different pager (not sure what you'll have with windows, you'd have to independently research it). I'm assuming that what you want is to have the list of column names as your first row, then each row thereafter on a single line in your display window (i.e. no wrapping of those lines on display). If you need to get column names into the output for each cell, then you'll need to do some post-processing of your own. Chris Asif Iqbal wrote: That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
Are you using the -S switch to less? Note that it's a capital S, not lower case. Does the output get properly sent through the less pager (i.e do you have to page through the results that get selected)? Check the manpage for less on your machine and find out what the truncate lines option is, if it is different (highly unlikely). Or try a different pager. Make sure you execute your SQL command using \g or ;. Make sure you don't have anything funky set in your client section of your my.cnf that may mess with pagers/output. See whether using tee (\T filename) tees the output into the specified output file without wrapping. Chris Asif Iqbal wrote: I am on solaris 2.8 sparc. You less works fine if I use \G switch. But I want (as you explained) list of column names in one line in first row and the results are thereafter in single rows instead of getting wrapped. Like I said wish I remebered the swithc for that On Tue, 3 Jun 2003, Chris Tucker wrote: What platform are you on? If you're on windows you probably won't have less installed, in which case you'll need to either (a) install it or (b) use a different pager (not sure what you'll have with windows, you'd have to independently research it). I'm assuming that what you want is to have the list of column names as your first row, then each row thereafter on a single line in your display window (i.e. no wrapping of those lines on display). If you need to get column names into the output for each cell, then you'll need to do some post-processing of your own. Chris Asif Iqbal wrote: That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: help creating foreign keys
Read the Foreign Key section of the InnoDB manual (http://innodb.com/ibman.html#InnoDB_foreign_keys): it explains why you may get an error code 150. Particularly: Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. So you need to have an index on the column foo_id in table bar. If you're running MySQL 4.0.13 you can get information on the last cause of a foreign key error using show innodb status. When working with InnoDB, and particularly features specific to InnoDB, I'd strongly recommend reading the InnoDB manual on innodb.com, as it covers most common issues like this one. Chris Justin Scheiber wrote: Hello, I want to create the following tables - where a foriegn key references an auto_incremented primary key of another table. In my simple logic, it seem like such a thing should be possible -- after all, i just need the value of the referenced primary key. I know you can't have 2 auto_increment columns in a table, and I have read up on the errno: 150 but it still seems like this should be possible.Do I need to rethink the table structure? Or do I just not understand something here? create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; create table bar ( bar_idint unsigned auto_increment, foo_idint unsigned, bar_value int, primary key (bar_id), foreign key(foo_id) references foo(foo_id), ) type=innodb; ERROR 1005: Can't create table './test/bar.frm' (errno: 150) -justin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query log/binlog inconsistency
Thanks for your response (and yours also, Jeremy). Both are spot on in terms of recognising it as a transaction commit issue. It turns out a developer was opening a transaction long before the query in question executed and had neglected to either commit it or roll it back: subsequently, the PHP page completed execution, the connection was dropped, and the transaction was implicitly rolled back. It's amazing how long it can take to find such a simple result: I only wish I had thought a little more a little earlier about what the binlog actually stores (namely records of transactions that have been committed!). Thanks again, Chris Heikki Tuuri wrote: Chris, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 29, 2003 10:17 AM Subject: Re: Query log/binlog inconsistency On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote: Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. Hmm. The fact that the it doesn't show up in the binlog *and* it never affects you data is good. That means the binlog is working properly. :-) At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). Agreed. My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). The first thing that comes to mind is that the abstraction layer you're using forgets to COMMIT the data, so InnoDB rolls it back and never write the query to the binlog. Jeremy's explanation is plausible. If the PHP library runs in the AUTOCOMMIT=0 mode, then the query is executed and reports modified rows, but when the connection ends mysqld rolls back the transaction because it was not explicitly committed. Also note that a deadlock or a lock wait timeout error rolls back the WHOLE current transaction. But I assume you did not get any of these errors or other errors? It would help if you could post the relevant query log excerpt. Jeremy Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg) -- 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: Problem starting mysql server
You should probably also take a look at your error log (dbmaster.err)...it'll tell you in much more detail why mysqld ended rather than continued running as expected. Often this will be something as simple as your directory permissions not being set right on your data dir... Chris Nick Arnett wrote: -Original Message- From: Rehaz Golamnobee [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:41 AM ... I have just upgraded my MySQL from version 3.23 to 4.0.13. However I cannot start the server. When I type mysqld_safe I get the following : [1] 1730 Linux:/# starting mysqld-max daemon with databases from /var/lib/mysql 030529 11:10:51 mysqld ended [1]+ Done mysqld_safe Sounds like it's never starting up at all, which explains why you can't connect to it. First thing I'd do is check your config file. Are you using the same config file as you did with 3.23? If so, you almost surely need to update it to suit 4.x, working from the examples that came with it. I don't recall details, but I'm quite sure that there are variable settings that will cause this behavior. You might first try one of the generic sample config files that came with your new version, just to see if it'll start up properly. Then try modifying one variable at a time, restarting after each. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup - the best way.
Alternatively, use InnoDB Hot Backup: http://www.innodb.com/hotbackup.html This will let you take a real-time backup of your InnoDB tables without taking the server down. Chris Rafal Jank wrote: Dnia Wed, 28 May 2003 12:57:59 +0200 Jarek Jarzebowski [EMAIL PROTECTED] zezna/a co nastpuje: Hi, I have MySQL 4.0.12 working on Debian Linux box. DB server uses MYISAM and InnoDB tables. MySQL docs shows what is the best way to backup InnoDB tables but I don't want to shutdown db server every backup time. I suppose it is not a good idea to just copy database files and innodb log files to the safe place because of unwanted rollbacks danger. What if I use mysqldump to backup databases data, and then I copy innodb log files to the safe place? I suppose I should flush tables first. You can mysqldump InnoDB tables too. Copying InnoDB files (data or redologs) without shuting down mysql is a bad idea. You can also use mysql replication if you have spare server and do a cold backup from it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query log/binlog inconsistency
Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). If anybody has an idea as to what may be happening, or better yet has seen this problem and maybe even has a solution, I'd be delighted to hear it. Further, if anyone can provide more details on what happens between the query log being written and the bin log being written (a rough process flow of what happens in the DB internals) that would be of great help (even if just to improve my knowledge of this stuff). Thanks in advance, Chris Tucker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-row insert
Why don't you use the load data command to import the data in to mysql? I've done this successfully for about 56,000 records. I currently don't have a larger data set, so that is definitely not a limit I hit. The server I need to load data into is replicated: load data with a local infile doesn't work across a replicated server on 3.23.x, and I can't place the file onto the master prior to running the load. Once we are able to migrate to 4.0.x that problem will go away and I am planning to rewrite to use that approach. Chris Server uname -a: Darwin localhost 5.5 Darwin Kernel Version 5.5: Thu May 30 14:51:26 PDT 2002; root:xnu/xnu-201.42.3.obj~1/RELEASE_PPC Power Macintosh powerpc Distro: MacOS 10.1.5 MySQL version: 3.23.51 On Thursday, September 19, 2002, at 06:55 PM, Chris Tucker wrote: I'm having some trouble with a sizable (but certainly not huge) multi-row insert statement. I can successfully execute the query with up to 7365 rows, but any more and it fails -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-row insert
Hi, I'm having some trouble with a sizable (but certainly not huge) multi-row insert statement. I can successfully execute the query with up to 7365 rows, but any more and it fails (ERROR 2006: MySQL server has gone away). More complete info: Total working query size: 1047426 bytes wait_timeout variable: 28800 Table type: MyISAM Columns: 13, including: 4 VARCHAR, 7 INT (varying sizes), 1 TEXT, 1 DATETIME. This fails through the mysql client copy/paste, mysql client using a piped file (mysql filename), and through Perl DBI. Server uname -a: Linux test-db 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown Distro: Red Hat Linux release 7.2 (Enigma) MySQL version: 3.23.52-log I'd be appreciative of any advice regarding limits that may exist on the amount of data that can be run through a single multi-row insert. The easy fix for me right now is to split into multiple multi-row inserts (splitting at 1000 rows/query seems to be working fine), but I'd rather get the proper solution or file a bug if need be. Cheers, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table creation error with innodb/ sql,query
Using indices will slow down inserts/updates on columns, and will use some amount of storage space (dependant on the size of the columns indexed, the number of columns indexed, etc.). However, they significantly improve performance when doing lookups in a table, which is especially important when executing joins (as you will most likely be doing between two tables tied by a foreign key). Thus, even if InnoDB did not require you to have an index on your FK columns (which it absolutely does), it almost certainly makes sense to have indices there anyway. -Chris On Tue, 2002-09-17 at 02:03, kayamboo wrote: Thanks a lot Chris It worked for me. But I refered somewhere , that using excessive index will eat up resources. So is it mandatory to use index even if I am not using them for something like search? regards Kayamboo Suresh - Original Message - From: Chris Tucker [EMAIL PROTECTED] To: kayamboo [EMAIL PROTECTED] Sent: Tuesday, September 17, 2002 5:35 PM Subject: Re: table creation error with innodb/ sql,query InnoDB requires that you have an index on both ends of a foreign key constraint. Thus, you need to index the type field of the person table. Change your create statement to: CREATE TABLE person( name VARCHAR(50) NOT NULL PRIMARY KEY, type VARCHAR(50) NOT NULL, dob DATE, INDEX (type), FOREIGN KEY(type) REFERENCES relation(type) ON DELETE CASCADE ) TYPE = INNODB ; And you should have no problems (note that if type was not the primary key on the relation table you would also have to explicitly declare an index for that column). Chris On Mon, 2002-09-16 at 22:45, kayamboo wrote: Hello experts I am trying to create innodb tables with foreign key relations but I get error 150 meaning that foregin key constraint can't be established. Here are the statements 1.CREATE TABLE relation( type VARCHAR(50) NOT NULL PRIMARY KEY)TYPE = INNODB; 2.CREATE TABLE person( name VARCHAR(50) NOT NULL PRIMARY KEY, type VARCHAR(50) NOT NULL, dob DATE, FOREIGN KEY(type) REFERENCES relation(type) ON DELETE CASCADE ) TYPE = INNODB ; But if I remove the type = innodb in the second statement, it is executed. Then I am using the ALTER TABLE syntax to create innodb tables. Any advice to avoid this alter table syntax? Or my create statement is wrong? Thanks in advance Regards Kayamboo Suresh sql,query,mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE syntax dummy question...
You need to escape the ' mark in your string. The query should be: update vbooth_data set optionText='Not at all, I''m waiting for the other shoe to drop' where (pollID=34 AND voteID=3); Note the '' within the string: the first tick escapes the second one. If you're more comfortable with C-style escaping, you can use \' instead (but this isn't so portable). Also, you shouldn't really be specifying your pollID and voteID as strings: take out the tick marks around those. Otherwise you're adding pointless (albeit small) load for the server to cast the string to an int. -Chris On Tue, 2002-09-03 at 10:31, Matthias Trevarthan wrote: Howdy, I'm trying to perform an update on a php poll table. Here is the table description: mysql describe vbooth_data; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | pollID | int(11) | | | 0 | | | optionText | char(50) | | | | | | optionCount | int(11) | | | 0 | | | voteID | int(11) | | | 0 | | +-+--+--+-+-+---+ 4 rows in set (0.00 sec) And here is my update query: update vbooth_data set optionText='Not at all, I'm waiting for the other shoe to drop' where (pollID='34' AND voteID='3'); When I hit Enter, it gives me this prompt: ' And nothing I input will do anything. I end up having to exit with CTRL-C or CTRL-D. I know this is probably a simple syntax issue, but what am I doing wrong?? And what is mysql looking for with that ' prompt? Thanks! Matthias - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Allowing a whole class C to access mysql server?
If you don't want to do any username checking (just checking a password) you can use a blank username. Easiest way to do this is probably something along the lines of: GRANT SELECT ON db.* TO temp@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'mypass'; UPDATE mysql.user SET user = '' WHERE user ='temp' AND host = '192.168.1.0/255.255.255.0'; FLUSH PRIVILEGES; Then anyone coming from the 192.168.1.* C-class will be able to access by just specifying a password. You may have to be a little careful that using a blank username doesn't kill any other perms you have set up: read the docs about the order in which things are matched to determine appropriate user perms. -Chris On Mon, 2002-08-26 at 16:15, Adam Ryan wrote: How can I get this to allow access from any user with the correct passwd to the main mysql server? Any user for the 192.168.1% class with a correct passwd. It seems I can't have a wildcard for the user statement below? Thanks -Original Message- From: Chris Tucker [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 25, 2002 6:58 PM To: Adam Ryan Cc: [EMAIL PROTECTED] Subject: Re: Allowing a whole class C to access mysql server? GRANT SELECT ON db.* TO user@'192.168.1.0/255.255.255.0' should do the trick: you just specify the IP you want to allow and the netmask to apply to it after the /. You could also use: GRANT SELECT ON db.* TO user@'192.168.1.%' as documented in the manual (http://www.mysql.com/doc/en/GRANT.html). Documentation on the netmask method can be found at http://www.mysql.com/doc/en/Connection_access.html -Chris On Sun, 2002-08-25 at 14:25, Adam Ryan wrote: Hi, I am trying to have a whole class C access the mysql server on the network. The class C needs to access many different database names. So adding an entry for each Ip address wouldn't work. Does anyone know how I can go about this? Thanks, Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL JOIN Challenge - Help Please
Actually, you should be able to get the data in one query: in general,
any time you are doing a restriction on some value being in a set of
values (such as order_id not in some list built from a subselect) you
can rewrite the query using LEFT OUTER JOIN's. In this case, the
solution would be something like:
SELECT *
FROM orders o
INNER JOIN transaction_log t
ON o.order_id = t.order_id
AND t.event_log_id = 1
LEFT OUTER JOIN transaction_log t2
ON t.order_id = t2.order_id
AND t2.event_log_id = 3
WHERE t2.order_id IS NULL;
The way this works is to join across the transaction_log table twice:
first, to retrieve all rows that reference the given order and have an
event log id of 1, and second to retrieve all rows that reference the
given order id and have an event log id of 2. We then restrict to get
only those rows that do not have valid values in the second join's
results (namely, those transactions that have an event id of 1, from the
initial (inner) join, but do not have a corresponding event id of 3,
from the second (outer) join).
On Sun, 2002-08-25 at 09:43, Brian Moon wrote:
Ok, I did not make the correlation between what rows you wanted and what the
data was. You are not getting this in one query. It would require a
sub-select. However, simple, but possibly slow queries will get it.
select * from transaction_log where event_log_id=3
get those back and
select order_id from transaction_log where order_id not in ({id list})
The list could be pretty big I guess.
Brian.
- Original Message -
From: Paul Maine [EMAIL PROTECTED]
To: Brian Moon [EMAIL PROTECTED]; MySQL MySQL [EMAIL PROTECTED]
Sent: Sunday, August 25, 2002 7:02 AM
Subject: RE: SQL JOIN Challenge - Help Please
| This is deceptively simple, and I very much appreciate your suggestion -
but
| it doesn't work. Your suggestion still returns records that I should not
| see. Once again, there is a one to many relationship between an order_id
in
| the order_id in the transaction response table. Please see my original
| email.
|
| Thank you
| Paul
| sql query
| -Original Message-
| From: Brian Moon [mailto:[EMAIL PROTECTED]]
| Sent: Sunday, August 25, 2002 12:11 AM
| To: Paul Maine; MySQL MySQL
| Subject: Re: SQL JOIN Challenge - Help Please
|
|
| If I read right, this is a pretty basic join so I am assuming that you
have
| never done a join in your life.
|
| select
| distinct order.order_id
| from
| order
| inner join
| transaction_log on
| order.order_id=transaction_log.order_id and
| transaction_log.event_log_id 3
|
| Brian.
|
|
| - Original Message -
| From: Paul Maine [EMAIL PROTECTED]
| To: MySQL MySQL [EMAIL PROTECTED]
| Sent: Saturday, August 24, 2002 7:31 PM
| Subject: SQL JOIN Challenge - Help Please
|
|
| | I have an order table and a transaction log table. The order table has
an
| | order_id and a number of other columns that are not germain to my
| question.
| | The transaction log has an order_ID and an event_log_id column. The
order
| id
| | from the order table will have a one to many relationship to the
order_id
| | data in the transaction log table. The event_log_id is an integer that
| | assumes a value of 1 if the credit card is authorized, a value of 2 if
the
| | credit card is denied or some other type of error, and a value of 3 if
the
| | credit card is delay captured.
| |
| | I need to create a query that would return all of the order.order_ids
that
| | are authorized but have not been delayed captured. How can I accomplish
| this
| | task?
| |
| | Thank You
| | Paul
| | sql query
| |
| |
| | -
| | Before posting, please check:
| |http://www.mysql.com/manual.php (the manual)
| |http://lists.mysql.com/ (the list archive)
| |
| | To request this thread, e-mail [EMAIL PROTECTED]
| | To unsubscribe, e-mail
| [EMAIL PROTECTED]
| | Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
| |
| |
|
|
| -
| Before posting, please check:
|http://www.mysql.com/manual.php (the manual)
|http://lists.mysql.com/ (the list archive)
|
| To request this thread, e-mail [EMAIL PROTECTED]
| To unsubscribe, e-mail
| [EMAIL PROTECTED]
| Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
|
|
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Allowing a whole class C to access mysql server?
GRANT SELECT ON db.* TO user@'192.168.1.0/255.255.255.0' should do the trick: you just specify the IP you want to allow and the netmask to apply to it after the /. You could also use: GRANT SELECT ON db.* TO user@'192.168.1.%' as documented in the manual (http://www.mysql.com/doc/en/GRANT.html). Documentation on the netmask method can be found at http://www.mysql.com/doc/en/Connection_access.html -Chris On Sun, 2002-08-25 at 14:25, Adam Ryan wrote: Hi, I am trying to have a whole class C access the mysql server on the network. The class C needs to access many different database names. So adding an entry for each Ip address wouldn't work. Does anyone know how I can go about this? Thanks, Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
