Completely new to mySQL
Hi list, I have a few questions. I have to start a new project and I have to choose the db. The most difficult choice of all. My question is : could mySQL be used as a enterprise database with approx. 50 users connecting to db ? The db is financial critical, so everything should happen in the context of a transaction. Is mySQL most used as webdb ? Or could it be used as well to serve as a enterprise db ? And the second question is : is there a performance drop when used on Win32 ? Many thanks in advance, Nico Callewaert
Re: question about mysql_use_result()
Please reply to [EMAIL PROTECTED] (not to me personally), so that others can follow this thread. At 18:55 -0700 10/6/03, Don Cohen wrote: Paul DuBois writes: > >Perhaps I misunderstood the doc but I thought by using _use_result > >I was asking for the server not to send the next tuple until I asked > > Correct, the server doesn't send the row until you ask for it, > but you cannot tell it, "don't send any more". But before you wrote: > Anyway, if you call mysql_free_result(), it will take care of > retrieving and discarding and unfetched rows. If so, this does seem to be a way of telling the server you don't want any more results. No. mysql_free_result() may keep you from having to do it yourself, but the rows must still be fetched. It's just that mysql_free_result() does it for you. The real question is then whether the cost of reading n results out of m and then doing the free_result is really proportional to n or to m. It's n+m in any case. All rows must be fetched. > >for it. Otherwise what's all this stuff about using less space cause > >you only have to store one tuple at a time, and the stuff about not > >using this method if you might want to wait for a long time in the > >middle which would cause locking problems? > > I'm not sure how either of those factors are relevant to the question > at hand. The first part (less space) would be relevant if the entire result were sent from the server and you just retrieved the rows incrementally from some buffer in the client. Even if the server internally generated the entire output at the beginning and stored them itself, but just delivered the rows incrementally as the client asked, then there would be no need to keep tables locked while there were undelivered tuples. > You might want to use LIMIT in conjunction with SQL_CALC_FOUND_ROWS. I think this means that the query includes a limit. If so that requires that you know the limit before you see any results. It includes a LIMIT, but there's more to the story. Check SQL_CALC_FOUND_ROWS (and FOUND_ROWS()) in the manual. This may not suit your purposes; then again, it may. I want a way to look at the result and decide that's enough. I believe I've already indicated that you cannot. A cancel may be implemented at some point, but I don't know when. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
At 18:09 -0700 10/6/03, Don Cohen wrote: Paul DuBois writes: > At 17:26 -0700 10/6/03, Don Cohen wrote: > >http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html > >says > > > > you must retrieve all the rows even if you determine in > > mid-retrieval that you've found the information you were looking > > for. > > > >My question is: why? > > Because there is no provision in the client/server protocol > whereby the client can interrupt a transfer from the server that > has started. Perhaps I misunderstood the doc but I thought by using _use_result I was asking for the server not to send the next tuple until I asked Correct, the server doesn't send the row until you ask for it, but you cannot tell it, "don't send any more". A future revision of the protocol may allow the client to cancel a transfer, but it's not possible now. for it. Otherwise what's all this stuff about using less space cause you only have to store one tuple at a time, and the stuff about not using this method if you might want to wait for a long time in the middle which would cause locking problems? I'm not sure how either of those factors are relevant to the question at hand. > >In fact I thought that the normal interface for a database would show > >a screen full of results and then let you decide whether to go on to > >the next screen or quit. It seems crazy that if you do a query that > >gives a million results you have to retrieve them all. > > If you're really making the server do all the work of retrieving > a million rows, especially for an interactive program, you might > consider rewriting the query. For example, with LIMIT. > > Anyway, if you call mysql_free_result(), it will take care of > retrieving and discarding and unfetched rows. The whole point here is that I *DON'T* want the server to generate all those rows. But I don't necessarily know how many I'll need at the beginning. Perhaps in some cases it will be necessary to generate all the rows in order to return the first one (for instance, if I ask for them to be sorted), but for the queries I intend to use I'd hope it would be possible to generate n tuples in time o(n). You might want to use LIMIT in conjunction with SQL_CALC_FOUND_ROWS. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
Paul DuBois writes: > At 17:26 -0700 10/6/03, Don Cohen wrote: > >http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html > >says > > > > you must retrieve all the rows even if you determine in > > mid-retrieval that you've found the information you were looking > > for. > > > >My question is: why? > > Because there is no provision in the client/server protocol > whereby the client can interrupt a transfer from the server that > has started. Perhaps I misunderstood the doc but I thought by using _use_result I was asking for the server not to send the next tuple until I asked for it. Otherwise what's all this stuff about using less space cause you only have to store one tuple at a time, and the stuff about not using this method if you might want to wait for a long time in the middle which would cause locking problems? > >In fact I thought that the normal interface for a database would show > >a screen full of results and then let you decide whether to go on to > >the next screen or quit. It seems crazy that if you do a query that > >gives a million results you have to retrieve them all. > > If you're really making the server do all the work of retrieving > a million rows, especially for an interactive program, you might > consider rewriting the query. For example, with LIMIT. > > Anyway, if you call mysql_free_result(), it will take care of > retrieving and discarding and unfetched rows. The whole point here is that I *DON'T* want the server to generate all those rows. But I don't necessarily know how many I'll need at the beginning. Perhaps in some cases it will be necessary to generate all the rows in order to return the first one (for instance, if I ask for them to be sorted), but for the queries I intend to use I'd hope it would be possible to generate n tuples in time o(n). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving database from Windows -> *nix?
Adam Clauss wrote: I am going to possibly have to transfer a MySQL database that is currently running on Windows to a Linux/Unix box (not sure exactly what version it is running yet). Any pitfalls to avoid here? Thanks Adam Clauss [EMAIL PROTECTED] I would use 'mysqldump' to backup the databases. I usually use the --opt flag, which sets a couple of other flags - check the docs. I would also be very careful that you don't have any reserved words in tables / fields, and if you do, either change them, or check the man pages for 'quote fields' or something like that. It makes mysqldump put quotes around everything, which protects it from the parser. If you have been able to successfully dump / restore all of your databases in the past, you can ignore this bit ( you have been backing up and testing restores, haven't you ... ). One other gotcha is that Linux is case-sensitive, so if you have queries with capitalisation that doesn't exactly match what's in the database, you will have problems, as a Windows-based MySQL server will let you get away with it, but a Linux-based one will insist that the table / field you're after doesn't exist unless the case of each character is the same. Other than that, I don't think you'll have any issues. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
At 17:26 -0700 10/6/03, Don Cohen wrote: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? Because there is no provision in the client/server protocol whereby the client can interrupt a transfer from the server that has started. In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. If you're really making the server do all the work of retrieving a million rows, especially for an interactive program, you might consider rewriting the query. For example, with LIMIT. Anyway, if you call mysql_free_result(), it will take care of retrieving and discarding and unfetched rows. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about mysql_use_result()
http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. BTW I tried searching for an answer in the list archives but the search there doesn't seem to work very well. I type in mysql_use_result() and get back messages that don't mention it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving database from Windows -> *nix?
I am going to possibly have to transfer a MySQL database that is currently running on Windows to a Linux/Unix box (not sure exactly what version it is running yet). Any pitfalls to avoid here? Thanks Adam Clauss [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving Database
Hi, I am moving a myself database from one hosting company to another for a website. I have statements like: INSERT INTO mpn_shopping_4nstats_datecnt VALUES ( '22022002', '79'); But MySQL comes back with: INSERT INTO mpn_shopping_4nstats_datecnt VALUES ( \'22022002\', \'79\'); MySQL said: You have an error in your SQL syntax near '\'22022002\', \'79\')' at line 1 Some reason, MySQL doesn't like the quotes. single or double. Is there some switch I can set on this msql server? phpMyAdmin 2.2.0 MySQL 3.23.56 running on localhost Thanks John - - - - - John Hung P. Ho, Consultant [EMAIL PROTECTED] - (619) 277-0164 virtual office, PST
Reading text file with variable white space
I am using MySQL (4.1.0-alpha-max-nt) on Windows 2000. I'm trying to read a text file that looks a bit like this: 40 3 10 M000 ... 1000... ACBB... ... where the ... indicate more characters. I've created the following table to store this data ( id int NOT NULL, year INT NOT NULL, test INT NOT NULL, elig CHAR(4) NOT NULL, writing CHAR(9) NOT NULL, spelling CHAR(36) NOT NULL, reading CHAR(30) NOT NULL, numeracy CHAR(38) NOT NULL ); The character fields are all fixed width, but the first integer field has some leading spaces. When I try LOAD DATA INFILE the results are pretty much rubbish: | 40 |0 |0 | 1100 | 11001 | 1 ACBBDCBBAADDDCABDA | ABDBACABAACC M | M Can I change something in my CREATE TABLE statement or add some options to LOAD DATA so that I can read the file as it is? Or must I pre-process it to zero-fill the first field? Thanks for your advice. Regards, Andrew C. Ward CAPE Centre Department of Chemical Engineering The University of Queensland Brisbane Qld 4072 Australia [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searh question
If I have a database with various dates in it, how can I search for all instances of the same date. Note that I do not know what the dates are (or how many there will be) so the search can't be hardcoded. These dates will be added based on user input from a website. Is there some kind of comparison function where I can search on field that have the same values..? TIA PS I want to end up with a table that follows this format (this is just an example) 01/01/03 Name Department Title Name Department Title Name Department Title Name Department Title Name Department Title 05/01/03 Name Department Title Name Department Title Name Department Title Name Department Title Name Department Title -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump from cron > empty file
So then Paul DuBois says... > One way to set the path would be to place your mysqldump command in > a helper script: > > #! /bin/sh > export PATH=your-path-setting-here > mysqldump > > Then invoke the helper script from the crontab, rather than invoking > mysqldump directly. You can set the PATH in the crontab itself. Just use something like PATH=your-path-setting-here mysqldump in your crontab. See "man 5 crontab". Groeten,- Jacco -- +-+ The time is 23:11 on Monday October 6 2003. | IRL: Jacco van Schaik | Outside it's 13 degrees with a strong breeze | mail: [EMAIL PROTECTED] | from the west. Inside, xmms is playing | URL: www.frontier.nl | "Maybe Angels" by Sheryl Crow. +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump from cron > empty file
At 17:09 -0400 10/6/03, Patrick Larkin wrote: On Monday, October 6, 2003, at 05:04 PM, Paul DuBois wrote: Please reply to [EMAIL PROTECTED] (not to me personally), so that others can follow this discussion. At 16:56 -0400 10/6/03, Patrick Larkin wrote: On Monday, October 6, 2003, at 04:37 PM, Paul DuBois wrote: Does the PATH setting for jobs run by cron include the directory where mysqldump is located? Hmmm. Don't know. I have other cron jobs doing things in other places. How do I set this path? Thanks for replying... Patrick One way to set the path would be to place your mysqldump command in a helper script: #! /bin/sh export PATH=your-path-setting-here mysqldump Then invoke the helper script from the crontab, rather than invoking mysqldump directly. But it's probably easier just to modify the crontab entry to invoke mysqldump by its full pathname. Sorry. I'm new to the list and didn't realize the headers didn't include the LIST as the replyto. mysqldump IS in the path, like I said, I can issue the command with no problem manually from the shell. Its only when I cron the command when I get an empty file. I don't understand. Here you say that mysqldump is in the path, but above you say that you don't know if mysqldump is in the path for cron jobs. I suggest you change the crontab entry to invoke mysqldump by its full pathname and then see what happens. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump from cron > empty file
On Monday, October 6, 2003, at 05:04 PM, Paul DuBois wrote: Please reply to [EMAIL PROTECTED] (not to me personally), so that others can follow this discussion. At 16:56 -0400 10/6/03, Patrick Larkin wrote: On Monday, October 6, 2003, at 04:37 PM, Paul DuBois wrote: Does the PATH setting for jobs run by cron include the directory where mysqldump is located? Hmmm. Don't know. I have other cron jobs doing things in other places. How do I set this path? Thanks for replying... Patrick One way to set the path would be to place your mysqldump command in a helper script: #! /bin/sh export PATH=your-path-setting-here mysqldump Then invoke the helper script from the crontab, rather than invoking mysqldump directly. But it's probably easier just to modify the crontab entry to invoke mysqldump by its full pathname. Sorry. I'm new to the list and didn't realize the headers didn't include the LIST as the replyto. mysqldump IS in the path, like I said, I can issue the command with no problem manually from the shell. Its only when I cron the command when I get an empty file. Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump from cron > empty file
Please reply to [EMAIL PROTECTED] (not to me personally), so that others can follow this discussion. At 16:56 -0400 10/6/03, Patrick Larkin wrote: On Monday, October 6, 2003, at 04:37 PM, Paul DuBois wrote: Does the PATH setting for jobs run by cron include the directory where mysqldump is located? Hmmm. Don't know. I have other cron jobs doing things in other places. How do I set this path? Thanks for replying... Patrick One way to set the path would be to place your mysqldump command in a helper script: #! /bin/sh export PATH=your-path-setting-here mysqldump Then invoke the helper script from the crontab, rather than invoking mysqldump directly. But it's probably easier just to modify the crontab entry to invoke mysqldump by its full pathname. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump from cron > empty file
At 16:33 -0400 10/6/03, Patrick Larkin wrote: Hello - I have the following command to back up a MySQL database on a remote machine. Works beautifully from the command line when run manually as root: mysqldump -h 192.227.20.50 -u root --password=yourmama --opt my_database > /Dumps/my_database.dump This results is a nifty SQL file with data. However, when I attempt to run the same command from the root user's crontab, it results in an empty file. Does the PATH setting for jobs run by cron include the directory where mysqldump is located? Any idea what I'm doing wrong. The command is running from root's cron file. The file has rw privs for root and the enclosing directory is owned by root. It doesn't have a problem running manually... -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump from cron > empty file
Hello - I have the following command to back up a MySQL database on a remote machine. Works beautifully from the command line when run manually as root: mysqldump -h 192.227.20.50 -u root --password=yourmama --opt my_database > /Dumps/my_database.dump This results is a nifty SQL file with data. However, when I attempt to run the same command from the root user's crontab, it results in an empty file. Any idea what I'm doing wrong. The command is running from root's cron file. The file has rw privs for root and the enclosing directory is owned by root. It doesn't have a problem running manually... Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing backups and restores from slaves
On Mon, Oct 06, 2003 at 12:43:37PM -0700, David L. Sifry wrote: > An update to the problem, and a workaround (bug report?): > > Jeremy Zawodny wrote: > > >While the tables are locked, be sure to also copy the slave's > >master.info file, relay log(s), and relay index file. Transfer all of > >that to the other slave and you should be good to go. > > > >Does that make sense? > > > > > > > Actually, that's exactly what I did (the tarball of the /var/lib/mysql > directory includes the master.info, relay logs, and relay index file) > but with no joy. > > What finally got it to work was to make the following changes: > > The relay bin file copied over is called "slave1-relay-bin.xxx" and the > relay bin index file is called "slave1-relay-bin.index". > > I had to change things around because the new slave I was copying to is > called "slave2". > > 1. mv slave1-relay-bin.xxx slave2-relay-bin.xxx > 2. mv slave1-relay-bin.index slave2-relay-bin.index > > Then I had to edit the first line of relay-log.info to point to the new > files, changing "slave1-relay-bin.xxx" to "slave2-relay-bin.xxx": > > Then I started mysql, and did a SLAVE START and everything worked. > > Is this a bug? Not really. It's working as designed. One thing you might do is to explicitly name the relay files in your my.cnf file so that they're hostname independant. relay-log = /path/to/blah/relay-log relay-log-index = /path/to/blah/relay-index Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 22 days, processed 839,067,119 queries (425/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile problem
Hope I am posting to the right list. I am new here. I have a sun system: SunOS spot 5.6 Generic_105181-05 sun4u sparc SUNW,Ultra-4 With gcc installed: # gcc -v Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.7.2.3/specs gcc version 2.7.2.3 I am trying to compile: mysql-4.1.0-alpha and I a getting this error: gcc -O3 -DDBUG_OFF -DHAVE_RWLOCK_T -o isamchk isamchk.o sort.o libnisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lpthread -lthread -lz -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread source='isamlog.c' object='isamlog.o' libtool=no \ depfile='.deps/isamlog.Po' tmpdepfile='.deps/isamlog.TPo' \ depmode=gcc /bin/ksh ../depcomp \ gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../include -O3 -DDBUG_OFF -DHAVE_RWLOCK_T -c `test -f isamlog.c || echo './'`isamlog.c In file included from /usr/include/sys/wait.h:24, from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.7.2.3/include/stdlib.h:22, from ../include/my_global.h:235, from ../include/my_base.h:26, from ../include/nisam.h:27, from isamdef.h:20, from isamlog.c:23: /usr/include/sys/resource.h:148: warning: `struct rlimit64' declared inside parameter list /usr/include/sys/resource.h:148: warning: its scope is only this definition or declaration, /usr/include/sys/resource.h:148: warning: which is probably not what you want. /usr/include/sys/resource.h:149: warning: `struct rlimit64' declared inside parameter list isamlog.c: In function `set_maximum_open_files': isamlog.c:715: storage size of `rlimit64' isn't known make[2]: *** [isamlog.o] Error 1 make[2]: Leaving directory `/usr/src/mysql-4.1.0-alpha/isam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-4.1.0-alpha' make: *** [all] Error 2 I was only able to get this far because I changed this in configure: #CXXFLAGS="$CXXFLAGS -fno-implicit-templates -fno-exceptions -fno-rtti" to: CXXFLAGS="$CXXFLAGS -fno-implicit-templates -fno-rtti" Any ideas what I can do? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: MySQL w/dual-master replication?]
Hey all, I sent this a few days ago, but it may have gotten lost in the weekend for many of you. Don't worry, I won't keep re-posting it. :) I'm hoping someone out there is doing some sort of high-availability replication and can give me a few pointers. Thanks in advance! Don Original Message Subject: MySQL w/dual-master replication? Date: Sat, 04 Oct 2003 11:07:43 -0700 From: Don MacAskill <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Hi there, I realize it's not supported, but nonetheless, I need to investigate how possible it is. The primary goal is high-availability, not performance scaling. It sounds like if I'm careful, it might work out ok. Careful means: - No auto_increment columns... handle unique key assignment in my app - Update/insert/delete on primary master only except in case of failure - Prevent possibly 'flap' by automatically using secondary master exclusively after primary master failure. Bring up primary master manually and manually tell the app when to start using it again after it's allowed to catch back up. Are there any other gotchas I need to worry about? Is anyone actually doing this in a production environment? I'd love to hear from you. Another option is to use master/slave, and have a monitoring app change the slave's status to master after the master has died. There's info about this in the FAQ (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm afraid the documentation on the sequence of events is pretty vague. Does anyone have any insight as to exactly how it works? In particular, one part of the doc says to use 'STOP SLAVE; RESET MASTER; CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE; CHANGE MASTER TO' ... which is appropriate? Certainly, I understand why 'STOP SLAVE', but why 'RESET MASTER'? Would the sequence of events differ if we're just dealing with 1 master/1 slave instead of 1 master/multiple slaves? Once the old master joins back up, I can let it be a slave for awhile to catch back up, then swap back, correct? Thanks in advance! Don MacAskill http://onethumb.smugmug.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing backups and restores from slaves
An update to the problem, and a workaround (bug report?): Jeremy Zawodny wrote: While the tables are locked, be sure to also copy the slave's master.info file, relay log(s), and relay index file. Transfer all of that to the other slave and you should be good to go. Does that make sense? Actually, that's exactly what I did (the tarball of the /var/lib/mysql directory includes the master.info, relay logs, and relay index file) but with no joy. What finally got it to work was to make the following changes: The relay bin file copied over is called "slave1-relay-bin.xxx" and the relay bin index file is called "slave1-relay-bin.index". I had to change things around because the new slave I was copying to is called "slave2". 1. mv slave1-relay-bin.xxx slave2-relay-bin.xxx 2. mv slave1-relay-bin.index slave2-relay-bin.index Then I had to edit the first line of relay-log.info to point to the new files, changing "slave1-relay-bin.xxx" to "slave2-relay-bin.xxx": Then I started mysql, and did a SLAVE START and everything worked. Is this a bug? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Thanks for your help _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem setting root password.
Hi folks, The following is copied exactly from my machine, except for the munged password. Setting the root password can't be completed, because the host apparently can't connect the mysql server. What am I doing wrong?: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h melody.yalemusic.ca password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting mysqld daemon with databases from /var/lib/mysql [EMAIL PROTECTED] local]# /usr/bin/mysqladmin -u root password 'xxx' [EMAIL PROTECTED] local]# /usr/bin/mysqladmin -u root -h melody.yalemusic.ca password 'xxx' /usr/bin/mysqladmin: connect to server at 'melody.yalemusic.ca' failed error: 'Host 'melody.yalemusic' is not allowed to connect to this MySQL server' [EMAIL PROTECTED] local]# Thanks, Rob Yale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to uninstall MySQL
This may seem like a strange problem but I have unwittingly managed to install two instances of MySQL on RedHat 8. One is the compiled binary version (which is what starts when the system is booted) and the other was from an RPM. I 'thought' I have removed the binary flavor before installing the rpm flavor but I was wrong. I need to get rid of both instances of MySQL so I can start over. I am having a devil of a time trying to get a DBD-Msql-Mysql module installed and since I have created such a mess (to the point where I had to rename the mysql.d file in order to get Apache to run again, my solution is to take everything off, reinstall the RPMs for MySQL server, client, shared, and devel, and THEN try to get the DBD-Mysql module installed. (And hopefully this time it will be able to find the files it wants in the places it wants them). And just so you know, I've already tried CPAN and also a force install but with negative results each time (in that it absolutely refused to install). All this because I'm trying to load WebEvent calendar software and it needs the DBI-DBD modules. Finally, I'm very new to Linux and have to drag all my books and documentation with me every time I go back to work on the server so please do not assume that I know what I'm doing. I'm a web designer who has been tasked to learn how to deal with the server sink or swim style. I will be very grateful for any guidance. Carol Andrejak DSU Webmaster Grossley Hall Rm. 7 302-857-7045
RE: RAID, miiror OR replication?
Richard, If you want to protect against hard drive failures then a RAID setup is probably the best option. A RAID will ensure that you always have an up-to-the-instant backup of all of your data in case a drive goes bad; however, a RAID won'tstop a bug, virus, or error from screwing up your database. If this is your production server then I would suggest that you also invest in a secondary backup system (such as a tape drive). -Rob -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2003 3:36 PM To: [EMAIL PROTECTED] Subject: RAID, miiror OR replication? I am wanting to protect myself against future potential hard drive failures on my database server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing backups and restores from slaves
On Sun, Oct 05, 2003 at 11:55:03PM -0700, David L. Sifry wrote: > I've got a somewhat advanced mysql administration question for y'all. > Here's my situation: > > I've got a master database that is doing lots of inserts, deletes, and > updates. It has a number of slaves hanging off of it. One of those > slaves is completely unloaded - so it keeps very close track of the > master. Let's call that machine slave1. My problem is doing restores > from backups. The only way I've been able to get a reliable restore to > occur is to do a FLUSH TABLES WITH READ LOCK on the master, then copy > the /var/lib/mysql/* directories, do a SHOW MASTER STATUS on the master, > and then UNLOCK TABLES on the master. Then after copying the snapshot > to a new slave I do a CHANGE MASTER command on the new slave and > everything works. > > So far, so good. Yes. I suspect that mysqlhotcopy might be useful too, but you've probably already automated the process. > The problem is that these databases are pretty large, and are regularly > updated. Stopping the master to do a snapshot is a pretty drastic > move. What I'd like to do is find a good recipe to use slave1 as the > machine to snapshot. I've tried the following, but things don't ever > seem to work: > > On slave1: > > mysql> SLAVE STOP; > mysql> FLUSH TABLES WITH READ LOCK; > Then I switch over to /var/lib/mysql/ and tarball the directory > cd /var/lib/mysql > tar zcvpPf /var/tmp/snapshot.tar.gz . > > Then I copy over the snapshot over to the new slave, and do the following: > /etc/init.d/mysql stop > cd /var/lib/mysql > tar zxvpPf /var/tmp/snapshot.tar.gz > > Then, when the tarball is finished unpacking, I run: > > /etc/init.d/mysql start > > I then log in as mysql superuser, and run the command: > mysql> slave start; While the tables are locked, be sure to also copy the slave's master.info file, relay log(s), and relay index file. Transfer all of that to the other slave and you should be good to go. Does that make sense? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 22 days, processed 836,548,967 queries (425/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL table crashed and doesn't repair
Hi Stanley, You also have the options to indicates mysqld to checks and repairs tables automatically as it detects corruption or crash. Put in the section mysqld of your my.cnf file these 2 variables : first: set-variable = myisam-recover=BACKUP,FORCE (it will try to do 'safe' repair, otherwise make a backup of problematic table and a force repair) second: set-variable = myisam_sort_buffer_size=4M (if you have enough memory, use greater value; this accelerates the process of repair). The automatic check and/or repair will leave a message in the .err file to indicates that a corruption or crash has occurs. PS: Try it with version 4.0.x Hope; this resolves Thierno6C :MySQL 4.0.15 - Original Message - From: "Alexis da Cruz Henriques Guia" <[EMAIL PROTECTED]> To: "Stanley Joseph" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, October 06, 2003 5:31 PM Subject: Re: MYSQL table crashed and doesn't repair > > Execute in shell myisamchk --help > > This will list a serie of options to repair your tables. Obviously, this only > works to MyISAM tables. > > ;) > Alexis > > Quoting Stanley Joseph <[EMAIL PROTECTED]>: > > > Dear All , > > A few tables of my database crashed and are not getting repaired .What is > > the best way to recover the data . > > Any suggestion will be of great help . > > > > Regards > > > > Stanley Joseph > > > > +44 (0) 208 665 4291 (W) > > +44 (0) 777 621 0132 (M) > > http://www.themobilerepublic.com > > The Price, The Choice, The Service > > > > This email and any attachments are confidential. They may contain privileged > > information and are intended for the named addressee(s) only. They must not > > be distributed without our consent. If you are not the intended recipient, > > please notify us immediately and do not disclose, distribute, or retain this > > email or any part of it. Unless expressly stated, opinions in this email are > > those of the individual sender, and not of The Mobile Republic. We believe > > but do not warrant that this e-mail and any attachments are virus free. You > > must therefore take full responsibility for virus checking. The Mobile > > Republic and its subsidiaries reserve the right to monitor all email > > communications through their networks. > > > > > > > > > > -- > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID, mirror OR replication?
On Mon, 2003-10-06 at 14:21, Richard Reina wrote: > I am wanting to protect myself against future potential hard drive > failures on my DB server running version 3.23.49a. Should I try and set > up a RAID, a mirror or would the best solution be to set up MySQL > replication. Any suggestions would be greatly appreciated. > Richard, if you have the resources available I would suggest doing both RAID and Replication. RAID 5 maximizes your disk space, while making your system pretty fault tolerant. (this of course assumes Hot Swappable SCSI Drives). The replication gives you the added level of fault tolerance, plus on a busy server DB reads can be offloaded to the replicant freeing up resources on the Master. Don't know how familiar you are with RAID so this is a breakdown of the most common options. RAID0 - disk Stripeing (very fast reads but one drive fails and everything is lost). Absolutely no fault tolerance. But an option for a Replicant. RAID1 - disk mirroring (Duplicate copy of everything on another harddrive - the problem is that you have to duplicate your drives. If you have a 80GB disk, you need 2 of them, but you still only use 80GB. RAID0+1 - disk striping w/ Mirroring, you have 2 RAID0 volumes of identical size that mirror to each other. You get the speed of RAID0, and the fault tolerance of RAID1. If you have 2 80GB disk striped, now you need 4 80GB disks and you only get space of 2 of the 80GBs. RAID5 - In my opinion the best choice. You maximize available space, since its (N-1) * Drive capacity. Meaning The number of drives - 1 is your capacity. The equivilent of 1 drive is used to store parity information. If one drive fails, the RAID Controller can autocorrect the missing information on the fly so your system slows down, but stays available. You remove the bad drive, put a new one in, and the new drive gets rebuilt and in a few hours you are back to full steam. You build a raid set with 4 80GB drives, your available capacity would be 240GB (4 Drives - 1 for parity) * 80GB. > Richard > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dummy problem with UPDATE for DATETIME field
Thank you, even if I checked a lot the syntax, couldn't see it (too obvious). Sorry for the time... --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 13:06 -0400 10/6/03, Franck Babin wrote: > >It's a syntax error. Even when I try without DATE_FORMAT: > > > >UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = > >1 WHERE activity_date = > >'2003-06-10 11:15:00' AND type_no = 1; > > Ah. > > Perhaps spelling UDPATE as UPDATE will fix the problem. :-) > > > > > > --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 12:55 -0400 10/6/03, > >Franck Babin wrote: > >> >I have made some changed but it still doesn't work: > >> > > >> >UDPATE activity SET activity_date = '2003-10-06 11:00:00', > >>type_no = 1 WHERE > >> >DATE_FORMAT(activity_date,'%Y-%m-%d %H:%i:%s') = '2003-06-10 > >> >11:15:00' AND type_no = 1; > >> > > >> >I want to update only rows where equals > >>'2003-06-10 11:15:00' > >> >Is this formulation correct? > >> > > >> > is really a DATETIME field. > >> >Thanks. > >> > >> In that case, you don't need DATE_FORMAT() at all. Just compare > >>activity_date > >> to the datetime value directly. > >> > >> You still haven't indicated what you mean by the statement not working. > >> It doesn't find any rows to update? Do you get any results from > >> the following statement, which simply tests the comparison? > >> > >> SELECT activity_date FROM activity > > > WHERE activity_date = '2003-06-10 11:15:00'; > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL table crashed and doesn't repair
Execute in shell myisamchk --help This will list a serie of options to repair your tables. Obviously, this only works to MyISAM tables. ;) Alexis Quoting Stanley Joseph <[EMAIL PROTECTED]>: > Dear All , > A few tables of my database crashed and are not getting repaired .What is > the best way to recover the data . > Any suggestion will be of great help . > > Regards > > Stanley Joseph > > +44 (0) 208 665 4291 (W) > +44 (0) 777 621 0132 (M) > http://www.themobilerepublic.com > The Price, The Choice, The Service > > This email and any attachments are confidential. They may contain privileged > information and are intended for the named addressee(s) only. They must not > be distributed without our consent. If you are not the intended recipient, > please notify us immediately and do not disclose, distribute, or retain this > email or any part of it. Unless expressly stated, opinions in this email are > those of the individual sender, and not of The Mobile Republic. We believe > but do not warrant that this e-mail and any attachments are virus free. You > must therefore take full responsibility for virus checking. The Mobile > Republic and its subsidiaries reserve the right to monitor all email > communications through their networks. > > > > > -- > 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]
RAID, miiror OR replication?
I am wanting to protect myself against future potential hard drive failures on my database server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dummy problem with UPDATE for DATETIME field
At 13:06 -0400 10/6/03, Franck Babin wrote: It's a syntax error. Even when I try without DATE_FORMAT: UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = 1 WHERE activity_date = '2003-06-10 11:15:00' AND type_no = 1; Ah. Perhaps spelling UDPATE as UPDATE will fix the problem. :-) --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 12:55 -0400 10/6/03, Franck Babin wrote: >I have made some changed but it still doesn't work: > >UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = 1 WHERE >DATE_FORMAT(activity_date,'%Y-%m-%d %H:%i:%s') = '2003-06-10 >11:15:00' AND type_no = 1; > >I want to update only rows where equals '2003-06-10 11:15:00' >Is this formulation correct? > > is really a DATETIME field. >Thanks. In that case, you don't need DATE_FORMAT() at all. Just compare activity_date to the datetime value directly. You still haven't indicated what you mean by the statement not working. It doesn't find any rows to update? Do you get any results from the following statement, which simply tests the comparison? SELECT activity_date FROM activity > WHERE activity_date = '2003-06-10 11:15:00'; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "No more room in index file"
Hi Jacco, Perhaps the manual needs to be enhanced, but it is only a guide, perhaps the language should be softened or 'error 136' modified that it could be either problem. Either way I hope this fixes you problem, my guess would be you have very large rows of data, which is why you did not hit the error 135 first. I hope this takes care of it for you, we have created very large tables (150 million rows) and had good success, as have others. If this does not fix it we may have dig a little deeper. Let us know. Best of luck, Ken - Original Message - From: "Jacco van Schaik" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, October 06, 2003 12:00 PM Subject: Re: "No more room in index file" > So then Ken Menzel says... > > Check out > > MAX_ROWS = # > > and > > AVG_ROW_LENGTH = # > > options in http://www.mysql.com/doc/en/CREATE_TABLE.html > > > > Set MAX_ROWS to a very very large number > > You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE > > Syntax using table options at the end. > > > > You can verify your changes by running SHOW TABLE STATUS before the > > change then again after the change. > > > > Have you done this already? > > We tried it once, but it failed due to an unconnected problem (out of > diskspace). The thing is, the manual explicitly states (in para. > 4.4.6.9) that this is a solution for an error 135 (No more room in > record file) and that an error 136 (No more room in index file) is > fixed by a table repair. So we decided to try something different > first. > No matter, we've freed up some diskspace now, so we'll try again. Thanks > for replying. I'm sure I'll be back if it doesn't work ;-) > > Groeten, - Jacco > > -- > +-+ > | IRL: Jacco van Schaik | The time is 17:52 on Monday October 6 2003. > | mail: [EMAIL PROTECTED] | Here at Frontier HQ it's 13 degrees with a > | URL: www.frontier.nl | fresh breeze from the west. > +-+ > > > -- > 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]
RAID, mirror OR replication?
I am wanting to protect myself against future potential hard drive failures on my DB server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT(*) faster?
At 11:40 -0500 10/6/03, Steve Buehler wrote: I am using PHP & MySQL for a program that I am writing. I have a table in my database that has a column with dates in it in the form -mm-dd. Sometimes there is nothing in the table with the search date that I am using. Other times there might be 1000's of rows with that in the date column. I am trying to find out if there are any rows with the date that I am searching for in the 'date' column. Would the fastest way be to use $date=2003-02-16; SELECT COUNT(*) FROM `games` WHERE `date`='$date' or is there a faster way? You might try SELECT 1 FROM `games` WHERE `date`='$date' LIMIT 1 and then see if you get a row back. LIMIT allows MySQL to perform some optimizations (such as terminating query processing once it finds enough matching records). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dummy problem with UPDATE for DATETIME field
It's a syntax error. Even when I try without DATE_FORMAT: UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = 1 WHERE activity_date = '2003-06-10 11:15:00' AND type_no = 1; --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 12:55 -0400 10/6/03, Franck Babin wrote: > >I have made some changed but it still doesn't work: > > > >UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = 1 WHERE > >DATE_FORMAT(activity_date,'%Y-%m-%d %H:%i:%s') = '2003-06-10 > >11:15:00' AND type_no = 1; > > > >I want to update only rows where equals '2003-06-10 11:15:00' > >Is this formulation correct? > > > > is really a DATETIME field. > >Thanks. > > In that case, you don't need DATE_FORMAT() at all. Just compare activity_date > to the datetime value directly. > > You still haven't indicated what you mean by the statement not working. > It doesn't find any rows to update? Do you get any results from > the following statement, which simply tests the comparison? > > SELECT activity_date FROM activity > WHERE activity_date = '2003-06-10 11:15:00'; > > > > > > --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 11:28 -0400 10/6/03, > >Franck Babin wrote: > >> >I have this simple sql statement: > >> > > >> >UDPATE activity > >> >SET activity_date = '9.10.2003 10:00:00', type_no = 1 > >> >WHERE DATE_FORMAT(activity_date,'%d.%m.%Y %H:%i') = '09.10.2003 10:00' > >> >AND type_no = 1; > >> > > >> >I'm not able to execute the query. It seems that the >> >activity_date = '9.10.2003 10:00:00'> > >> >portion is not valid. is a DATETIME field. > >> >Any idea? > >> > >> What do you mean by "is not valid"? The comparison doesn't work? The > >> correct value doesn't get stored into the row? > >> > >> In any case, if activity_date really is a DATETIME column, you must > >> store values in it using '-MM-DD hh:mm:ss' format, not in the format > >> you store above. > >> > >> If activity_date is *not* really a DATETIME column, but is stored using > >> values in 'DD.MM. hh:ss' format, then DATE_FORMAT() will not work > >> correctly. > >> > >> -- > >> Paul DuBois, Senior Technical Writer > >> Madison, Wisconsin, USA > >> MySQL AB, www.mysql.com > >> > >> Are you MySQL certified? http://www.mysql.com/certification/ > >> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >> > > > >__ > >Post your free ad now! http://personals.yahoo.ca > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dummy problem with UPDATE for DATETIME field
At 12:55 -0400 10/6/03, Franck Babin wrote: I have made some changed but it still doesn't work: UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = 1 WHERE DATE_FORMAT(activity_date,'%Y-%m-%d %H:%i:%s') = '2003-06-10 11:15:00' AND type_no = 1; I want to update only rows where equals '2003-06-10 11:15:00' Is this formulation correct? is really a DATETIME field. Thanks. In that case, you don't need DATE_FORMAT() at all. Just compare activity_date to the datetime value directly. You still haven't indicated what you mean by the statement not working. It doesn't find any rows to update? Do you get any results from the following statement, which simply tests the comparison? SELECT activity_date FROM activity WHERE activity_date = '2003-06-10 11:15:00'; --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 11:28 -0400 10/6/03, Franck Babin wrote: >I have this simple sql statement: > >UDPATE activity >SET activity_date = '9.10.2003 10:00:00', type_no = 1 >WHERE DATE_FORMAT(activity_date,'%d.%m.%Y %H:%i') = '09.10.2003 10:00' >AND type_no = 1; > >I'm not able to execute the query. It seems that the activity_date = '9.10.2003 10:00:00'> >portion is not valid. is a DATETIME field. >Any idea? What do you mean by "is not valid"? The comparison doesn't work? The correct value doesn't get stored into the row? In any case, if activity_date really is a DATETIME column, you must store values in it using '-MM-DD hh:mm:ss' format, not in the format you store above. If activity_date is *not* really a DATETIME column, but is stored using values in 'DD.MM. hh:ss' format, then DATE_FORMAT() will not work correctly. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
I think you want just "ORDER BY commentid DESC" .The previous answer orders by commentref first, which is not what I think you want. Maybe you want "ORDER BY commentid, commentref DESC" This doesn't seem to be making sure that the "newest" commentid is the result... Seems to be random. At 09:11 AM 10/6/2003 -0400, you wrote: >try > > select commentid, commentref >from comments > ORDER by > commentref ,commentid DESC; > > > -Original Message- > > From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] > > Sent: Thursday, October 02, 2003 2:37 PM > > To: [EMAIL PROTECTED] > > Subject: sorting/grouping > > > > > > > > > > I have a very simple table. > > > > commentid, commentref (each field is an INT) > > > > Each record is a comment... commentid is the ID (each new record is a > > higher #).. and the commentref field if the "story" the > > comment refers to. > > > > I want to be able to list the stories in order from "most > > recent comment" > > onward. > > > > I've tried > > > > select commentid, commentref from comments GROUP BY > > commentref ORDER by > > commentid DESC; > > > > However, the results are NOT showing me the stories in order > > from most > > active comments onward... Maybe too much soda and sugar - > > but any thoughts? > > > > > > > > > > -- > > 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] Shawn Cummings Engineering Project Manager Global NAPs 10 Merrymount Rd Quincy, MA 02169 Desk 617-507-5150 VoIP 617-507-3550 [EMAIL PROTECTED] -- 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: COUNT(*) faster?
I think this is OK. But if you want to get a recordset with data and know how much records you have with only ONE query, you may use: $date=2003-02-16; SELECT col1, col2, coln FROM `games` WHERE `date`='$date' $num_rows=0; $num_rows=mysql_num_rows($rs_resource_identifier); I don't remember exactly Try it. Thanks Emery - Original Message - From: "Steve Buehler" <[EMAIL PROTECTED]> To: "mysql" <[EMAIL PROTECTED]> Sent: Monday, October 06, 2003 18:40 Subject: COUNT(*) faster? > I am using PHP & MySQL for a program that I am writing. I have a table in > my database that has a column withut dates in it in the form > -mm-dd. Sometimes there is nothing in the table with the search date > that I am using. Other times there might be 1000's of rows with that in > the date column. I am trying to find out if there are any rows with the > date that I am searching for in the 'date' column. Would the fastest way > be to use > $date=2003-02-16; > SELECT COUNT(*) FROM `games` WHERE `date`='$date' > or is there a faster way? > > > -- > 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]
Opensource ERP software with MySQL back end? Anyone know of one?
I have MS Access front end with MySQL back end working in my office. MSAccess via CrossOver office on Mandrake is horrible. So, I am looking now to probably go with ERP (or ERP/CRM) software that I can get freely out there. I have no IT budget, I am the IT budget, so I have to do something. Any ideas where I could find software like that. I do want it to run on MySQL, since that I have working perfectly. Apollo - Visit CARMEL MUSIC & ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dummy problem with UPDATE for DATETIME field
I have made some changed but it still doesn't work: UDPATE activity SET activity_date = '2003-10-06 11:00:00', type_no = 1 WHERE DATE_FORMAT(activity_date,'%Y-%m-%d %H:%i:%s') = '2003-06-10 11:15:00' AND type_no = 1; I want to update only rows where equals '2003-06-10 11:15:00' Is this formulation correct? is really a DATETIME field. Thanks. --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 11:28 -0400 10/6/03, Franck Babin wrote: > >I have this simple sql statement: > > > >UDPATE activity > >SET activity_date = '9.10.2003 10:00:00', type_no = 1 > >WHERE DATE_FORMAT(activity_date,'%d.%m.%Y %H:%i') = '09.10.2003 10:00' > >AND type_no = 1; > > > >I'm not able to execute the query. It seems that the >activity_date = '9.10.2003 10:00:00'> > >portion is not valid. is a DATETIME field. > >Any idea? > > What do you mean by "is not valid"? The comparison doesn't work? The > correct value doesn't get stored into the row? > > In any case, if activity_date really is a DATETIME column, you must > store values in it using '-MM-DD hh:mm:ss' format, not in the format > you store above. > > If activity_date is *not* really a DATETIME column, but is stored using > values in 'DD.MM. hh:ss' format, then DATE_FORMAT() will not work > correctly. > > -- > Paul DuBois, Senior Technical Writer > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COUNT(*) faster?
I am using PHP & MySQL for a program that I am writing. I have a table in my database that has a column with dates in it in the form -mm-dd. Sometimes there is nothing in the table with the search date that I am using. Other times there might be 1000's of rows with that in the date column. I am trying to find out if there are any rows with the date that I am searching for in the 'date' column. Would the fastest way be to use $date=2003-02-16; SELECT COUNT(*) FROM `games` WHERE `date`='$date' or is there a faster way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row-level privileges in mysql
I think this is not implemented. But Logically, I also think it is not easy to implement since rows get added dynamically. So, it is not easy to predict what will be in the table. Since I needed such a feature in my application, I created an ACL (access control list) table that allows me to control who can UPDATE which rows in other tables. Of course this is only possible when a user accesses the DB through my application. In case a user connects to the database using other Clients, he will bypass my ACL. Hope this helps Thanks Emery - Original Message - From: "Madhavi Kutty" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, October 06, 2003 18:12 Subject: Row-level privileges in mysql > Hi, > Does MySQL support row-level access privileges? > > Regards, > Madhavi > > __ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
At 12:09 -0400 10/6/03, Cummings, Shawn (GNAPs) wrote: For instance; mysql> select commentid, commentref from comments GROUP BY commentref ORDER by commentref DESC, commentid; +---++ | commentid | commentref | +---++ |80 |188 | |73 |187 | |76 |185 | |56 |181 | |59 |180 | |60 |179 | |50 |169 | ... You can see for record 181 that "56" is the "highest" number after 181 is grouped. If you include a GROUP BY in your query the way you do without including an aggregate function in the column output list, the query will boil down the output to include one row per commentref value, and choose in an indeterminate fashion what values to display in the other output columns. That's why you're not getting the results you want. The query using max() suggested by Joseph Bueno looks closer to what you should be using, as far as I can tell. However this is not true; mysql> select commentid, commentref from comments WHERE commentref=181; +---++ | commentid | commentref | +---++ |56 |181 | |57 |181 | |79 |181 | +---++ You can see above that id(79) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
At 12:01 -0400 10/6/03, Cummings, Shawn (GNAPs) wrote: This doesn't seem to be making sure that the "newest" commentid is the result... Seems to be random. Your requirements are unclear. If you want output sorted by descending order of commentid, then your original query should produce that. In that case, however, the GROUP BY seems irrelevant. Given that you've included a GROUP BY, it appears that what you may want is output grouped by commentref, with rows for a given commentref value sorted by descending commentid value. In that case, the query suggested by Dan appears to me to be what you want. Yet you state that neither query produces the correct result. Perhaps you could clarify what you want. Please show a sample result, indicate why it is not correct, and indicate what the correct result would look like. You might also indicate what "most active" in your original description has to do with the problem to be solved. There is nothing in your table structure that appears to relate to assessment of "activity". At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the "story" the comment refers to. I want to be able to list the stories in order from "most recent comment" onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - > but any thoughts? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row-level privileges in mysql
Hi, Does MySQL support row-level access privileges? Regards, Madhavi __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sorting/grouping
Have you tried: select commentref,max(commentid) from comments group by commentref Regards, Joseph Bueno Cummings, Shawn (GNAPs) wrote: This doesn't seem to be making sure that the "newest" commentid is the result... Seems to be random. At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; > -Original Message- > From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 02, 2003 2:37 PM > To: [EMAIL PROTECTED] > Subject: sorting/grouping > > > > > I have a very simple table. > > commentid, commentref (each field is an INT) > > Each record is a comment... commentid is the ID (each new record is a > higher #).. and the commentref field if the "story" the > comment refers to. > > I want to be able to list the stories in order from "most > recent comment" > onward. > > I've tried > > select commentid, commentref from comments GROUP BY > commentref ORDER by > commentid DESC; > > However, the results are NOT showing me the stories in order > from most > active comments onward... Maybe too much soda and sugar - > but any thoughts? > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
For instance; mysql> select commentid, commentref from comments GROUP BY commentref ORDER by commentref DESC, commentid; +---++ | commentid | commentref | +---++ |80 |188 | |73 |187 | |76 |185 | |56 |181 | |59 |180 | |60 |179 | |50 |169 | ... You can see for record 181 that "56" is the "highest" number after 181 is grouped. However this is not true; mysql> select commentid, commentref from comments WHERE commentref=181; +---++ | commentid | commentref | +---++ |56 |181 | |57 |181 | |79 |181 | +---++ You can see above that id(79) At 09:11 AM 10/6/2003 -0400, Dan Greene wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; > -Original Message- > From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 02, 2003 2:37 PM > To: [EMAIL PROTECTED] > Subject: sorting/grouping > > > > > I have a very simple table. > > commentid, commentref (each field is an INT) > > Each record is a comment... commentid is the ID (each new record is a > higher #).. and the commentref field if the "story" the > comment refers to. > > I want to be able to list the stories in order from "most > recent comment" > onward. > > I've tried > > select commentid, commentref from comments GROUP BY > commentref ORDER by > commentid DESC; > > However, the results are NOT showing me the stories in order > from most > active comments onward... Maybe too much soda and sugar - > but any thoughts? > > > > > -- > 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] Shawn Cummings Engineering Project Manager Global NAPs 10 Merrymount Rd Quincy, MA 02169 Desk 617-507-5150 VoIP 617-507-3550 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting MySQL activity as a per database value
Jeremy Zawodny writes: > On Fri, Oct 03, 2003 at 08:29:17AM -0400, John Murtari wrote: > > Folks, > > > > I've done a lot of searches, tried out the tools I could > > find (like phpMyAdmin/mtop). We have a server with about 30 DB's > > and we would LOVE to get a report that gives us queries/hour, cpu, > > etc.. against each DB so that we can measure activity. > > Can't easily do that today, since MySQL doesn't track many per-db stats. > > > We are running 4.0.13 on Redhat 7.2 -- we can see > > some commands added to "limit" activity on a per DB basis. So I > > assume there is some "counting" going on somewhere. Is there any > > way to access those values? > > Really? > > There are some per-user limits: > > http://www.mysql.com/doc/en/GRANT.html > > But I may have missed per-db limits. Where are they documented? > Yes, that is what I was thinking of when I said per-db above. In our local operation we only assign one user/db so it works out the same. Is there any visibility into that data? i.e. if I limit some user to X MAX_QUERIES_PER_HOUR -- can I actually see how they are doing? What the current rate is? Many thanks! -- John ___ John Murtari Software Workshop Inc. [EMAIL PROTECTED] 315.695.1301(x-211)"TheBook.Com" (TM) http://www.thebook.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dummy problem with UPDATE for DATETIME field
At 11:28 -0400 10/6/03, Franck Babin wrote: I have this simple sql statement: UDPATE activity SET activity_date = '9.10.2003 10:00:00', type_no = 1 WHERE DATE_FORMAT(activity_date,'%d.%m.%Y %H:%i') = '09.10.2003 10:00' AND type_no = 1; I'm not able to execute the query. It seems that the portion is not valid. is a DATETIME field. Any idea? What do you mean by "is not valid"? The comparison doesn't work? The correct value doesn't get stored into the row? In any case, if activity_date really is a DATETIME column, you must store values in it using '-MM-DD hh:mm:ss' format, not in the format you store above. If activity_date is *not* really a DATETIME column, but is stored using values in 'DD.MM. hh:ss' format, then DATE_FORMAT() will not work correctly. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
This doesn't seem to be making sure that the "newest" commentid is the result... Seems to be random. At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; > -Original Message- > From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 02, 2003 2:37 PM > To: [EMAIL PROTECTED] > Subject: sorting/grouping > > > > > I have a very simple table. > > commentid, commentref (each field is an INT) > > Each record is a comment... commentid is the ID (each new record is a > higher #).. and the commentref field if the "story" the > comment refers to. > > I want to be able to list the stories in order from "most > recent comment" > onward. > > I've tried > > select commentid, commentref from comments GROUP BY > commentref ORDER by > commentid DESC; > > However, the results are NOT showing me the stories in order > from most > active comments onward... Maybe too much soda and sugar - > but any thoughts? > > > > > -- > 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] Shawn Cummings Engineering Project Manager Global NAPs 10 Merrymount Rd Quincy, MA 02169 Desk 617-507-5150 VoIP 617-507-3550 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "No more room in index file"
So then Ken Menzel says... > Check out > MAX_ROWS = # > and > AVG_ROW_LENGTH = # > options in http://www.mysql.com/doc/en/CREATE_TABLE.html > > Set MAX_ROWS to a very very large number > You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE > Syntax using table options at the end. > > You can verify your changes by running SHOW TABLE STATUS before the > change then again after the change. > > Have you done this already? We tried it once, but it failed due to an unconnected problem (out of diskspace). The thing is, the manual explicitly states (in para. 4.4.6.9) that this is a solution for an error 135 (No more room in record file) and that an error 136 (No more room in index file) is fixed by a table repair. So we decided to try something different first. No matter, we've freed up some diskspace now, so we'll try again. Thanks for replying. I'm sure I'll be back if it doesn't work ;-) Groeten,- Jacco -- +-+ | IRL: Jacco van Schaik | The time is 17:52 on Monday October 6 2003. | mail: [EMAIL PROTECTED] | Here at Frontier HQ it's 13 degrees with a | URL: www.frontier.nl | fresh breeze from the west. +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dummy problem with UPDATE for DATETIME field
your date format is not correct. It should be activity_date = '2003-9-10 10:00:00'. -Original Message- From: Franck Babin [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2003 8:28 AM To: [EMAIL PROTECTED] Subject: Dummy problem with UPDATE for DATETIME field I have this simple sql statement: UDPATE activity SET activity_date = '9.10.2003 10:00:00', type_no = 1 WHERE DATE_FORMAT(activity_date,'%d.%m.%Y %H:%i') = '09.10.2003 10:00' AND type_no = 1; I'm not able to execute the query. It seems that the portion is not valid. is a DATETIME field. Any idea? __ Post your free ad now! http://personals.yahoo.ca -- 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: "No more room in index file"
Check out MAX_ROWS = # and AVG_ROW_LENGTH = # options in http://www.mysql.com/doc/en/CREATE_TABLE.html Set MAX_ROWS to a very very large number You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE Syntax using table options at the end. You can verify your changes by running SHOW TABLE STATUS before the change then again after the change. Have you done this already? Ken - Original Message - From: "Jacco van Schaik" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, October 06, 2003 8:44 AM Subject: "No more room in index file" > Hi, > > We have a database with a table whose data file (.MYD) has grown to 42 > GB and an associated index (.MYI) file that's grown to 17 GB, at which > point MySQL gave us an error 136 (No more room in index file). > > The MySQL manual says (in paragraph 4.4.6.9 "How to Repair Tables") that > this type of error can be fixed with a "repair table", but in our case > that doesn't seem to have helped. The maximum size of the index file, > as reported by myisamchk -d -v is still 17 GB with a keyfile pointer > size of 3 bytes. > > Since then we've tried to drop the index and re-create it, but that > hasn't helped either. The new index file is slightly smaller than it > was before, but it's obviously only a matter of time until we'll run > into the same limitation again. > > Does anyone know what we need to do to force MySQL to start using 4-byte > keyfile pointers? > > Groeten, - Jacco > > -- > +-+ > | IRL: Jacco van Schaik | The time is 14:20 on Monday October 6 2003. > | mail: [EMAIL PROTECTED] | Here at Frontier HQ it's 15 degrees with a > | URL: www.frontier.nl | strong breeze from the west. > +-+ > > > -- > 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]
Dummy problem with UPDATE for DATETIME field
I have this simple sql statement: UDPATE activity SET activity_date = '9.10.2003 10:00:00', type_no = 1 WHERE DATE_FORMAT(activity_date,'%d.%m.%Y %H:%i') = '09.10.2003 10:00' AND type_no = 1; I'm not able to execute the query. It seems that the portion is not valid. is a DATETIME field. Any idea? __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No more connexion available
I recently had my DB stuck, probably because they were too many connexions. Even as root I couldn't connect (which is contradictory to http://www.mysql.com/doc/en/Too_many_connections.html). I had to manually "kill -9" the processes... How can I avoid this? how can I know what happened? TIA Greg RedHat 9 ; MySQL 3.23.56 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC Error
All, Yo gang! Having a problem with the MySQL ODBC 3.51 driver. I've got a MySQL volume which has a user table (tbl_usr). I'm linking this table to an Access database on my workstation (via ODBC link). The account being used for the connection has select, update, and insert rights to all tables in the database. I'm populating the linked (MySQL) table with data I've got on my workstation in an MS Access database. To do this, I'm simply executing an "insert into ... select ... from" statement. INSERT INTO tbl_usr (fst_nm, lst_nm, tle_nm, email_addr) SELECT tbl_stage.First, tbl_stage.Last, tbl_stage.Title, tbl_stage.Email FROM tbl_stage; However, each time I execute the statement I receive an error (see attached file - mysql_odbc_err.pdf). The error message provided isn't very helpful. Can anyone elaborate? Client Environment: - Windows 2000 (SP3) - MS Access 2000 Server Environment: - MacOS X 10.2.6 (Jaguar) - MySQL 4.0.13-standard Regards, Adam <> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL table crashed and doesn't repair
Dear All , A few tables of my database crashed and are not getting repaired .What is the best way to recover the data . Any suggestion will be of great help . Regards Stanley Joseph +44 (0) 208 665 4291 (W) +44 (0) 777 621 0132 (M) http://www.themobilerepublic.com The Price, The Choice, The Service This email and any attachments are confidential. They may contain privileged information and are intended for the named addressee(s) only. They must not be distributed without our consent. If you are not the intended recipient, please notify us immediately and do not disclose, distribute, or retain this email or any part of it. Unless expressly stated, opinions in this email are those of the individual sender, and not of The Mobile Republic. We believe but do not warrant that this e-mail and any attachments are virus free. You must therefore take full responsibility for virus checking. The Mobile Republic and its subsidiaries reserve the right to monitor all email communications through their networks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Join Issue
How would one join multiple table and selected all records from a joined table whether they exist OR not? I can get the records if they exist in a second table and I can get the records if they don't exist, but is there a way to combine the two? I have, for example, a statement like this (selects where doesn't exist): SELECT c.Game_Date AS Date, c.Game_Time AS Time, a.Team_Name AS Home_Team, b.Team_Name AS Away_Team, e.Game_ID FROM schedule c JOIN Teams a ON c.Home_Team = a.ID JOIN Teams b ON c.Away_Team = b.ID LEFT JOIN broadcasts e ON c.ID = e.Game_ID WHERE e.Game_ID IS NULL Any ideas? __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: thread problem
I'm using linux, Red Hat 7.3 Linux version 2.4.22, and 3.23.55-Max-log. Is This information ok?, or I'm missing something. Greetings Mikel _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
try select commentid, commentref from comments ORDER by commentref ,commentid DESC; > -Original Message- > From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 02, 2003 2:37 PM > To: [EMAIL PROTECTED] > Subject: sorting/grouping > > > > > I have a very simple table. > > commentid, commentref (each field is an INT) > > Each record is a comment... commentid is the ID (each new record is a > higher #).. and the commentref field if the "story" the > comment refers to. > > I want to be able to list the stories in order from "most > recent comment" > onward. > > I've tried > > select commentid, commentref from comments GROUP BY > commentref ORDER by > commentid DESC; > > However, the results are NOT showing me the stories in order > from most > active comments onward... Maybe too much soda and sugar - > but any thoughts? > > > > > -- > 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]
sorting/grouping
I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the "story" the comment refers to. I want to be able to list the stories in order from "most recent comment" onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can NOT drop the database
Adam, please Cc: [EMAIL PROTECTED], so that others can follow this discussion. If the innodb_table_monitor does not show those #sql... tables in InnoDB's own data dictionary at all, then you can simply delete the .frm files. They are simply orphaned .frm files. You do not need to shut down mysqld for that. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: "Adam Hardy" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Lähetetty: Monday, October 06, 2003 1:14 PM Aihe: Re: can NOT drop the database > Thanks Heikki. > > In the output of innodb_table_monitor I can't actually see the name of > any orphaned tables. Anyway, it's not an issue until those versions are > released. > > Before then, I wonder if it is possible to delete the database directory > completely. Will that cause mysql problems? (Obviously I would shut down > the daemon first). > > Adam > > On 10/06/2003 09:38 AM Heikki Tuuri wrote: > > Adam, > > > > I am sorry, I tested the procedure below and noticed that there was an > > assertion that fails. I have now fixed the assertion to 4.0.16 and 4.1.1. > > You have to wait for those versions. Meanwhile, you can use the > > innodb_table_monitor to check what those #sql... tables are. > > > > If mysqld crashed in the middle of an ALTER TABLE, then in crash recovery > > InnoDB probably rolled back all insertions to those tables, and they are now > > empty. > > > > Generally the procedure in >= 4.0.16 and >= 4.1.1 to recover a table whose > > name is #sql... is: > > > > You can use > > > > CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; > > > > with an arbitrary table definition. InnoDB ignores the table definition > > anyway, because internally the above is converted to a RENAME TABLE. > > > > Then delete the file > > > > rsql..._recover_innodb_tmp_table.frm > > > > and rename the file (in Linux you must enclose the file name #sql... in > > double quotes " ") > > > > #sqlfrm > > > > to > > > > rsql..._recover_innodb_tmp_table.frm > > > > Then you should be able to access the table > > > > rsql..._recover_innodb_tmp_table > > > > Best regards, > > > > Heikki > > > > - Original Message - > > From: "Adam Hardy" <[EMAIL PROTECTED]> > > To: "Heikki Tuuri" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Sunday, October 05, 2003 11:11 PM > > Subject: Re: can NOT drop the database > > > > > > > >>Just got back onto this again now. > >> > >>To quote the docs you pointed me at: > >> > >> > >>If MySQL crashes in the middle of an ALTER TABLE operation, you may end > >>up with an orphaned temporary table inside the InnoDB tablespace. With > >>innodb_table_monitor you see a table whose name is #sql..., but since > >>MySQL does not allow accessing any table with such a name, you cannot > >>dump or drop it. The solution is to use a special mechanism available > >>starting from version 3.23.48 of InnoDB. > >> > >>If you have an orphaned table #sql... inside the tablespace, then by > > > > calling > > > >>CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; > >> > >>where the table definition is similar to the temporary table, you can > >>make InnoDB to rename the orphaned table to > >>`rsql..._recover_innodb_tmp_table`. Then you can dump and drop the > >>renamed table. The backquotes around the table name are needed because a > >>temporary table name contains the character '-'. > >><<< > >> > >>It was a crash during an ALTER TABLE operation, I remember it, but it > >>was a while back so I am unable to tell what these 2 orphan table > >>definitions are - even what the tables were called unfortunately. > >> > >>According to the blurb above I need the definition to rename the orphan > >>table. So it looks like I'm stuck. > >> > >>Even so I tried a few random guesses at the table definitions without > >>getting anywhere. It seems mysql doesn't like my backquotes - with my > >>en_UK keyboard I have "`" which I think should be OK, so how come I get > >>an error? > >> > >>Have you got any more suggestions? > >> > >> > >>Thanks > >>Adam > >> > >> > >>On 10/01/2003 03:30 PM Heikki Tuuri wrote: > >> > >>>Adam, > >>> > >>>you can use the innodb_table_monitor > >>> > >>>http://www.innodb.com/ibman.html#InnoDB_Monitor > >>> > >>>and the advice at > >>> > >>>http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict > >>> > >>>to resolve the problem. > >>> > >>>Best regards, > >>> > >>>Heikki > >>>Innobase Oy > >>>http://www.innodb.com > >>>InnoDB - transactions, row level locking, and foreign keys for MySQL > >>>InnoDB Hot Backup - a hot backup tool for MySQL > >>>Order MySQL support from http://www.mysql.com/support/index.html > >>> > >>>- Alkuperäinen viesti - > >>>Lähettäjä: "Adam Hard
"No more room in index file"
Hi, We have a database with a table whose data file (.MYD) has grown to 42 GB and an associated index (.MYI) file that's grown to 17 GB, at which point MySQL gave us an error 136 (No more room in index file). The MySQL manual says (in paragraph 4.4.6.9 "How to Repair Tables") that this type of error can be fixed with a "repair table", but in our case that doesn't seem to have helped. The maximum size of the index file, as reported by myisamchk -d -v is still 17 GB with a keyfile pointer size of 3 bytes. Since then we've tried to drop the index and re-create it, but that hasn't helped either. The new index file is slightly smaller than it was before, but it's obviously only a matter of time until we'll run into the same limitation again. Does anyone know what we need to do to force MySQL to start using 4-byte keyfile pointers? Groeten,- Jacco -- +-+ | IRL: Jacco van Schaik | The time is 14:20 on Monday October 6 2003. | mail: [EMAIL PROTECTED] | Here at Frontier HQ it's 15 degrees with a | URL: www.frontier.nl | strong breeze from the west. +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Thanks for your help _ Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query-Display current month and last 11 months...
Maybe like: SELECT something FROM tablename WHERE date_column > DATE_SUB(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-','01'), INTERVAL @n MONTH) @n is the number of months you want. If you want data from the current month, @n would be 0. -- Diana Soares On Mon, 2003-10-06 at 07:23, [EMAIL PROTECTED] wrote: > Hi all, >Having a slight problem with mysql select query right here. > I've learnt that if I were to select a particular data within the last 30 days, > this is what my select query should be like: > > SELECT something FROM tablename > WHERE TO_DAYS(NOW(()_TO_DAYS(date_column)<=30; > > (This query selects all records with a 'date_column' value within the last 30 days.) > > Now my question is: What if I would like to display data for the CURRENT MONTH > and the last 11 months???(May also said to be the LAST MONTHS) > HOw should my select query be like?? > Hope to receive some help soon. > Any help given is greatly appreciated. > > Regards, > Irin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION syntax (Request for feature)
Hi!
It depends, what language use you to include SQL commands. I use Java and if
I use scrollable result set I can resolv the problem inside a class.
Best regards
On Mon, 6 Oct 2003 10:00:37 +0200, Director General: NEFACOMP wrote
> Hi Group,
>
> I want to use a UNION in order to SELECT columns from several tables
> but I wish MySQL could use the following syntax:
>
> SELECT {several fields} FROM (Table1 UNION Table2 UNION Table3 .
> ) WHERE condition
>
> Currently I think MySQL only supports the following:
>
> (SELECT {several fields} FROM Table1 WHERE condition) UNION
> (SELECT {several fields} FROM Table2 WHERE condition) UNION
> (SELECT {several fields} FROM Table3 WHERE condition) UNION
> (SELECT {several fields} FROM Table4 WHERE condition)
>
> If the string {several fields} contains like 50 columns and the
> tables are for instance 100, you understand how long the query sent
> to the server will be (of course it works but ) For those who
> did Mathematics, you may be remembering that it is easier to write
> 10 * (20 + 30 + 40 + 50 + 60 + 70 ) than writing 10 * 20 + 10 * 30 +
> 10 * 40 + 10 * 50 + 10 * 60 + 10 * 70
>
> Thanks,
> __
> NZEYIMANA Emery Fabrice
> NEFA Computing Services, Inc.
> P.O. Box 5078 Kigali
> Office Phone: +250-51 11 06
> Office Fax: +250-50 15 19
> Mobile: +250-08517768
> Email: [EMAIL PROTECTED]
> http://www.nefacomp.net/
Laszlo Illyes
Library-informatics
Sapientia University
(Csikszereda) Miercurea-Ciuc
Tel:+40266317310
Fax:+40266317310/+40266371121
Mobil:+40740055706
E-mail: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION syntax (Request for feature)
Hi Group,
I want to use a UNION in order to SELECT columns from several tables but I wish MySQL
could use the following syntax:
SELECT {several fields} FROM (Table1 UNION Table2 UNION Table3 . ) WHERE condition
Currently I think MySQL only supports the following:
(SELECT {several fields} FROM Table1 WHERE condition) UNION
(SELECT {several fields} FROM Table2 WHERE condition) UNION
(SELECT {several fields} FROM Table3 WHERE condition) UNION
(SELECT {several fields} FROM Table4 WHERE condition)
If the string {several fields} contains like 50 columns and the tables are for
instance 100, you understand how long the query sent to the server will be (of course
it works but )
For those who did Mathematics, you may be remembering that it is easier to write 10 *
(20 + 30 + 40 + 50 + 60 + 70 )
than writing 10 * 20 + 10 * 30 + 10 * 40 + 10 * 50 + 10 * 60 + 10 * 70
Thanks,
__
NZEYIMANA Emery Fabrice
NEFA Computing Services, Inc.
P.O. Box 5078 Kigali
Office Phone: +250-51 11 06
Office Fax: +250-50 15 19
Mobile: +250-08517768
Email: [EMAIL PROTECTED]
http://www.nefacomp.net/
error messages in different languages
Hi group, I have two questions about error messages in MySQL. 1. Section 4.6.2 talks about Non-English error messages. It says that for recompiling the errmsg.sys file, one will issue a comp_err command at the SHELL prompt. Does such a utility exist for Windows? I mean can someone tell me when to find the 'comp_err.exe' in the MySQL Windows distribution? 2. One can change the error message file used by MySQL to instruct it to to send error messages in a given language. My question is that my application will be used by both English and French speakers. So, you understand that I don't need to use this or that error file; I need to use both at the same time. Is there anyway to instruct MySQL to use both error files and send messages based on client request? So that whenever the connected client will get personalized error messages as MySQL accepts different character SETs to be used based on Client request? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
Re: can NOT drop the database
Adam, I am sorry, I tested the procedure below and noticed that there was an assertion that fails. I have now fixed the assertion to 4.0.16 and 4.1.1. You have to wait for those versions. Meanwhile, you can use the innodb_table_monitor to check what those #sql... tables are. If mysqld crashed in the middle of an ALTER TABLE, then in crash recovery InnoDB probably rolled back all insertions to those tables, and they are now empty. Generally the procedure in >= 4.0.16 and >= 4.1.1 to recover a table whose name is #sql... is: You can use CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; with an arbitrary table definition. InnoDB ignores the table definition anyway, because internally the above is converted to a RENAME TABLE. Then delete the file rsql..._recover_innodb_tmp_table.frm and rename the file (in Linux you must enclose the file name #sql... in double quotes " ") #sqlfrm to rsql..._recover_innodb_tmp_table.frm Then you should be able to access the table rsql..._recover_innodb_tmp_table Best regards, Heikki - Original Message - From: "Adam Hardy" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, October 05, 2003 11:11 PM Subject: Re: can NOT drop the database > Just got back onto this again now. > > To quote the docs you pointed me at: > > > If MySQL crashes in the middle of an ALTER TABLE operation, you may end > up with an orphaned temporary table inside the InnoDB tablespace. With > innodb_table_monitor you see a table whose name is #sql..., but since > MySQL does not allow accessing any table with such a name, you cannot > dump or drop it. The solution is to use a special mechanism available > starting from version 3.23.48 of InnoDB. > > If you have an orphaned table #sql... inside the tablespace, then by calling > > CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; > > where the table definition is similar to the temporary table, you can > make InnoDB to rename the orphaned table to > `rsql..._recover_innodb_tmp_table`. Then you can dump and drop the > renamed table. The backquotes around the table name are needed because a > temporary table name contains the character '-'. > <<< > > It was a crash during an ALTER TABLE operation, I remember it, but it > was a while back so I am unable to tell what these 2 orphan table > definitions are - even what the tables were called unfortunately. > > According to the blurb above I need the definition to rename the orphan > table. So it looks like I'm stuck. > > Even so I tried a few random guesses at the table definitions without > getting anywhere. It seems mysql doesn't like my backquotes - with my > en_UK keyboard I have "`" which I think should be OK, so how come I get > an error? > > Have you got any more suggestions? > > > Thanks > Adam > > > On 10/01/2003 03:30 PM Heikki Tuuri wrote: > > Adam, > > > > you can use the innodb_table_monitor > > > > http://www.innodb.com/ibman.html#InnoDB_Monitor > > > > and the advice at > > > > http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict > > > > to resolve the problem. > > > > Best regards, > > > > Heikki > > Innobase Oy > > http://www.innodb.com > > InnoDB - transactions, row level locking, and foreign keys for MySQL > > InnoDB Hot Backup - a hot backup tool for MySQL > > Order MySQL support from http://www.mysql.com/support/index.html > > > > - Alkuperäinen viesti - > > Lähettäjä: "Adam Hardy" <[EMAIL PROTECTED]> > > Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> > > Kopio: <[EMAIL PROTECTED]> > > Lähetetty: Wednesday, October 01, 2003 3:24 PM > > Aihe: Re: can NOT drop the database > > > > > > > >>Hi Heikki, > >> > >>a similar problem happened to me and I got the error: > >> > >>ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30' > >> > >>After reading your post, I checked in the data directory and there are > >>two files there, both of file type data: > >> > >>#sql-2b2_30.frm > >>#sql-ffa_2.frm > >> > >>I certainly didn't put them there myself - I think they must have come > >>from mySQL automatically at some point. > >> > >>Are they real tables in the database? I can't see them when I use 'show > >>tables'. Is it a permissions problem? I have this on another database > >>that I was trying to restore from a dump. I had to rename the database > >>in the end. > >> > >>I am using InnoDB tables for some of my data. > >> > >>Regards > >>Adam > >> > > -- > Running mySQL 4.1.0 on Linux 2.4.20 RH9 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
