key constraints
Hi there i am having another issue with key constraints now, i emptied the tables and made the foreign keys fine although now when i try and insert a row into the table i get this ERROR 1216: Cannot add or update a child row: a foreign key constraint fails What could be the problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Command not found
Hello, I know I am asking a very silly question, but it's really bothering me for quite a while: I installed the MySQL 4.0 on my Linxus machine (red hat 7.3). The installation shows no problem, and I can see that the 'mysqld' server is actually runing through a command like "./bin/mysqladmin version". However, it just does not allow me to switch to the mysql client program from my login shell. Whenever I try to do so, it also says that "bash: mysql: command not found". I don't know how to deal with it. Could some MySQL experts please kindly help me with this? Regards, Hon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Personal Servers
If setting up apache on a local *nix box is more than what you need try phpdev. http://www.firepages.com.au/devindex.htm ~Jeff - Original Message - From: "Jakob Dölling" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 12:31 Subject: Re: Personal Servers > [EMAIL PROTECTED] wrote: > > > I'd recommend Apache, www.apache.org, but be aware that Windows OSes come > > A "have to" for geeks and those who want to rely blindly on their software. > I think Nicola is better served with a search on Tucows or a similar portal > for http servers for non-geeks. 602Pro LanSuite 2003 would be "good" > choice". But there is IMO any server software as reliable as the Apache > httpd. > > Regards, > Jakob > ^-- > To Unix or not to Unix. That is the question whether 'tis nobler in the > mind to suffer slings and arrows of vast documentation or to take arms > against a sea of buggy OS and by raping the support lines end then? ;> > > -- > 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: Constraint Symbol
I worked out the problem, it worked if i emptied the table, what could that be all about ? >>> Daniel Rossi <[EMAIL PROTECTED]> 09/05/03 10:50am >>> What is the definition of the constraint symbol there is no example of it in the docs ? Also on a few tables i get errors trying to setup foreign keys ALTER TABLE departments ADD FOREIGN KEY (departmentID) REFERENCES departments_join(departmentID) ON UPDATE CASCADE this for example gives me Cannot add or update a child row: a foreign key constraint fails Any ideas ? -- 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]
ODBC connection to MySQL problem
Hi, Problems: "[MYSQL][ODBC 3.51 driver] Client does not support authentication protocol requested by server. Consider upgrading MySQL client." I am facing this problem when i try to add Data Source in User DSN in ODBC. It is fine if i don't put the password setting in MySQL database. But error occurs when i set the password. I am using MySQL4.1-alpha windows. How to solve this problem? Thanks for any help. Yahoo! Games - Who Wants to Be A Millionaire? Play now!
MYSQL GUI
there is no acknowledgement when a table is created. You don't find out about it until you turn off the gui and restart it. Respectfully, Ligaya Turmelle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC connection to MySQL problem
Hi, Problems: "[MYSQL][ODBC 3.51 driver] Client does not support authentication protocol requested by server. Consider upgrading MySQL client." I am facing this problem when i try to add Data Source in User DSN in ODBC. It is fine if i don't put the password setting in MySQL database. But error occurs when i set the password. I am using MySQL4.1-alpha windows. How to solve this problem? Thanks for any help. Yahoo! Games - Who Wants to Be A Millionaire? Play now!
advice on change of data design
Hi all, I'm trying to merge and existing list of 900-odd email-list subscribers into an existing membership system. The existing system uses the userid (eg "Justin") as the primary key. Obviously, I don't have the leisure of asking 900-odd people what their preferred userid is, so I've decided that I need to change the way that users login from userid|pass to email|pass. This way I can just send out a random password to each existing subscriber. I have some concerns about members needing to type such a long email address in to login, but putting that aside, my main concern is that the email address should be something that can be changed, so it can't be the primary key. I've come up with the following data design, which I'd appreciate comments on: userid (INT 5, primary key) email (varchar 255, unique) password (varchar 32, md5hash) firstname (varchar 50) lastname (varchar 50) So, when a user logs in, I check for a match on email and password, then assign the userid to the session, probably also assigning their first & last names as another session variable, for a human-readable name on message boards etc etc. When the user changes his/her email address, it won't affect their primary keys, scattered across many tables... it will just affect how they log in. Any comments? Justin French -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL not starting
In the last episode (Sep 04), Jerry Rasmussen said: > I recently installed mysql 4 from source. After compiling everything I > am unable to start mysql. This is the error I get in the log when I try > to start mysql. > > 030901 19:41:35 Fatal error: Can't open privilege tables: Can't find > file: './mysql/host.frm' (errno: 13) > > I am trying to get this to run on a Red Hat 9 install of Linux. Any > help would be appreciated. $ perror 13 Error code 13: Permission denied Check the permissions on the datadir and make sure whatever user mysqld is running as can read the files. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX in a SubSelect
MySQL 4.0 (the current stable brench) does not support subqueries. This feature is present in the beta brench of MySQL, v4.1. http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Subqueries Sincerily, Leonardo Rodrigues - Original Message - From: "Oscar (TOMCAT)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 11:45 PM Subject: MAX in a SubSelect Hi, I'm using MySQL 4.0.1 for Linux/Windows... I need to perform a query like this: SELECT AVALIACAO, COUNT(AVALIACAO) FROM AUDITORIAS WHERE IDVERSAO = (SELECT MAX(IDVERSAO) FROM VERSOES WHERE DATA = '2003-08-23') GROUP BY AVALIACAO And its doesn't work in this version... There is other way to do that ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MAX in a SubSelect
Hi,
I'm using MySQL 4.0.1 for Linux/Windows... I need to perform a query like this:
SELECT AVALIACAO, COUNT(AVALIACAO)
FROM AUDITORIAS
WHERE IDVERSAO = (SELECT MAX(IDVERSAO) FROM VERSOES WHERE DATA = '2003-08-23')
GROUP BY AVALIACAO
And its doesn't work in this version...
There is other way to do that ???
Thanks,
Oscar
PS. Above the sample script to create the tables needed to run this example query.
DROP TABLE VERSOES;
CREATE TABLE VERSOES
(
IDVERSAO CHAR(4),
DATA DATE
)
;
DROP TABLE AUDITORIAS;
CREATE TABLE AUDITORIAS
(
PROGRAMA CHAR(10),
AVALIACAO CHAR(2),
IDVERSAO CHAR(4)
)
;
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V001','2003-08-22');
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V002','2003-08-23');
INSERT INTO VERSOES (IDVERSAO, DATA) VALUES ('V003','2003-08-23');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V001');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','ER','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V002');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG1','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG2','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG3','ER','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG4','OK','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG5','OK','V003');
INSERT INTO AUDITORIAS (PROGRAMA, AVALIACAO, IDVERSAO) VALUES ('PG6','OK','V003');
Re: web hosting/PHP MyAdmin
Hi Matthew, Before I started using phpMyAdmin, I didn't want anything to do with it. :-) But now I find it to be nice for quick things like browsing tables, quick edits, table statistics, etc. For queries that return large amounts of text, it's MUCH more legible than the command line. :-) Keep in mind though that it totally *SUCKS* for backing up and restoring any but the smallest databases -- because of PHP time-outs, memory limits, file upload limits, etc. The only [reliable] way to go is using mysqldump and mysql from the command line -- through the shell. So yeah, it's really a good idea to have shell access for backup/restore. Else you'll have to ask the host to do it. A pain, if they'll even do it. :-) Matt - Original Message - From: "Matthew K. Gold" Sent: Thursday, September 04, 2003 6:32 PM Subject: web hosting/PHP MyAdmin > Hi, > > I need to move my site to a host that supports PHP and MySQL. I found a > nice deal that provides a lot of good stuff for an affordable price > (sitelutions.com is offering 5 MySQL databases, PHP support, cgi, etc etc > for $9.95/month). > > There's only one problem--they don't allow you to edit the MySQL databases > through a shell--you have to use a web-based control panel and phpMyAdmin. > > My problem is that I'm used to editing my MySQL databases through the shell, > and I find the phpMyAdmin admin kind of confusing. > > I have two questions: > > 1. Was anyone out there in my position before, but then once you tried > phpMyAdmin, you found that you liked it a lot? > > 2. Does anyone know of any better, or equal deals out there that also allow > shell access? > > Thanks very much in advance for your help. > > best, > > Matthew Gold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL not starting
I do not have a my.ini. I do have a my.cnf. Any other ideas. On Fri, 2003-09-05 at 00:44, Martin Gainty wrote: > did you look at datadir specification in my.ini? > -Martin > - Original Message - > From: "Jerry Rasmussen" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, September 04, 2003 5:59 PM > Subject: MYSQL not starting > > > > I recently installed mysql 4 from source. After compiling everything I > > am unable to start mysql. This is the error I get in the log when I try > > to start mysql. > > > > 030901 19:41:35 Fatal error: Can't open privilege tables: Can't find > > file: './mysql/host.frm' (errno: 13) > > > > I am trying to get this to run on a Red Hat 9 install of Linux. Any > > help would be appreciated. > > > > Thanks > > Jerry > > > > > > -- > > 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: web hosting/PHP MyAdmin
I like it also, but of all things, I have trouble with the display on Internet Explorer sometimes, strange only because everybody seems to write everything to be IE compatible nowadays, and it hangs, so I use Mozilla on it and have never had a problem. It makes some nice printouts of the table structures. And as was mentioned, it does have the raw query window, so you can do practically anything in it that you could do from the command line... Kelley Mike Morton wrote: > Matthew: > > Personally I prefer the shell - but I do have clients who's host providers > only allow phpMyAdmin - it is a pretty powerful interface - once you spend > about 10 mins in there it is pretty easy to use, and just remember that it > does have a raw sql 'field' that will let you run any query that you have > permission for. > > The only thing to watch for is that it is a recent version of phpmyadmin - > some providers have older versions because they believe them to be more > stable, and while that may be true, you lose out on a lot of the newer and > more powerful features offered now. > > It is worth noting that with these providers also, as long as you have FTP > access, you CAN install your own phpmyadmin version - just name it a > different directory than what they offer. > > Overall phpMyAdmin is a very good alternative when you just cant get shell - > and in some cases can make life easier as well. > > Two thumbs up ;) > > On 9/4/03 7:32 PM, "Matthew K. Gold" <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > I need to move my site to a host that supports PHP and MySQL. I found a > > nice deal that provides a lot of good stuff for an affordable price > > (sitelutions.com is offering 5 MySQL databases, PHP support, cgi, etc etc > > for $9.95/month). > > > > There's only one problem--they don't allow you to edit the MySQL databases > > through a shell--you have to use a web-based control panel and phpMyAdmin. > > > > My problem is that I'm used to editing my MySQL databases through the shell, > > and I find the phpMyAdmin admin kind of confusing. > > > > I have two questions: > > > > 1. Was anyone out there in my position before, but then once you tried > > phpMyAdmin, you found that you liked it a lot? > > > > 2. Does anyone know of any better, or equal deals out there that also allow > > shell access? > > > > Thanks very much in advance for your help. > > > > best, > > > > Matthew Gold > > > > -- > Cheers > > Mike Morton > > > * > * Tel: 905-465-1263 > * Email: [EMAIL PROTECTED] > * > > > "Indeed, it would not be an exaggeration to describe the history of the > computer industry for the past decade as a massive effort to keep up with > Apple." > - Byte Magazine > > Given infinite time, 100 monkeys could type out the complete works of > Shakespeare. Win 98 source code? Eight monkeys, five minutes. > -- NullGrey > > -- > 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: MYSQL not starting
did you look at datadir specification in my.ini? -Martin - Original Message - From: "Jerry Rasmussen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 5:59 PM Subject: MYSQL not starting > I recently installed mysql 4 from source. After compiling everything I > am unable to start mysql. This is the error I get in the log when I try > to start mysql. > > 030901 19:41:35 Fatal error: Can't open privilege tables: Can't find > file: './mysql/host.frm' (errno: 13) > > I am trying to get this to run on a Red Hat 9 install of Linux. Any > help would be appreciated. > > Thanks > Jerry > > > -- > 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: Constraint Symbol
> SHOW TABLE STATUS FROM yourdatabasename LIKE 'departments' > which will show us the constraints for table departments..-Martin- Apologies constraint was missing, this works ALTER TABLE assets ADD CONSTRAINT FOREIGN KEY (end_dateID) REFERENCES end_dates(end_dateID) ON DELETE CASCADE +---+++--++-+--- --+--+---++-+--- --++--+---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--++-+--- --+--+---++-+--- --++--+---+ | end_dates | InnoDB | Fixed | 17 |963 | 16384 |NULL |0 | 0 | 18 | NULL | NULL| NULL | row_format=FIXED | InnoDB free: 11264 kB | +---+++--++-+--- --+--+---++-+--- --++--+---+ Where this wont ALTER TABLE assets ADD CONSTRAINT FOREIGN KEY (manufacturerID) REFERENCES manufacturers (manufacturerID) ON DELETE RESTRICT +---+++--++- +-+--+---++- +-+++---+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--++- +-+--+---++- +-+++---+ | manufacturers | InnoDB | Dynamic| 19 |862 | 16384 |NULL |0 | 0 | 21 | NULL | NULL| NULL | row_format=DYNAMIC | InnoDB free: 10240 kB | +---+++--++- +-+--+---++- +-+++---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: web hosting/PHP MyAdmin
Matthew: Personally I prefer the shell - but I do have clients who's host providers only allow phpMyAdmin - it is a pretty powerful interface - once you spend about 10 mins in there it is pretty easy to use, and just remember that it does have a raw sql 'field' that will let you run any query that you have permission for. The only thing to watch for is that it is a recent version of phpmyadmin - some providers have older versions because they believe them to be more stable, and while that may be true, you lose out on a lot of the newer and more powerful features offered now. It is worth noting that with these providers also, as long as you have FTP access, you CAN install your own phpmyadmin version - just name it a different directory than what they offer. Overall phpMyAdmin is a very good alternative when you just cant get shell - and in some cases can make life easier as well. Two thumbs up ;) On 9/4/03 7:32 PM, "Matthew K. Gold" <[EMAIL PROTECTED]> wrote: > Hi, > > I need to move my site to a host that supports PHP and MySQL. I found a > nice deal that provides a lot of good stuff for an affordable price > (sitelutions.com is offering 5 MySQL databases, PHP support, cgi, etc etc > for $9.95/month). > > There's only one problem--they don't allow you to edit the MySQL databases > through a shell--you have to use a web-based control panel and phpMyAdmin. > > My problem is that I'm used to editing my MySQL databases through the shell, > and I find the phpMyAdmin admin kind of confusing. > > I have two questions: > > 1. Was anyone out there in my position before, but then once you tried > phpMyAdmin, you found that you liked it a lot? > > 2. Does anyone know of any better, or equal deals out there that also allow > shell access? > > Thanks very much in advance for your help. > > best, > > Matthew Gold > -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock escallation etc?
Hi all! Here's a question for all my fellow geeks / coders / software engineers / curious DB admins. I've been doing a lot of reading lately, and have noted a few things: 1. Out of the "big three" commercial databases, only Oracle seems to support nested transactions. Is there any practical purpose for such things now that InnoDB has partial rollbacks? If there is a purpose for such a construct, would implementing it hurt InnoDB's performance? I'm not asking for the feature at all, I'm simply curious. 2. Reading up on MS SQL Server, the designers at MS seem to attribute the speed of the product to three major factors: * Their pool-of-threads architecture * The fact that tables are locked as much as they need to be and no more, with automatic lock escallation as required (Database->Table->Page->Row) * Optimistic Conflict Control Obviously, the first of these is coming to MySQL eventually as listed in the TODO pages. Would adding the second point to MyISAM be useful at all, given the fact that we already have INSERT DELAYED? Would adding the second point to InnoDB speed it up, slow it down or would the benefits and pitfalls basically cancel each other out (Considering how fast InnoDB already is, I've a feeling that this is not something that would help performance)? As for Optimistic Conflict Control, this wouldn't apply to MyISAM, would it? Seeing InnoDB include it would be interesting, as Microsoft themselves have been a bit vauge as to the integrity implications of this feature. Please note: None of the above are feature requests! This is just one guy's curiosity getting the better of him. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Constraint Symbol
SHOW TABLE STATUS FROM yourdatabasename LIKE 'departments' which will show us the constraints for table departments..-Martin- Original Message - From: "Daniel Rossi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 5:50 PM Subject: Constraint Symbol What is the definition of the constraint symbol there is no example of it in the docs ? Also on a few tables i get errors trying to setup foreign keys ALTER TABLE departments ADD FOREIGN KEY (departmentID) REFERENCES departments_join(departmentID) ON UPDATE CASCADE this for example gives me Cannot add or update a child row: a foreign key constraint fails Any ideas ? -- 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 not starting
I recently installed mysql 4 from source. After compiling everything I am unable to start mysql. This is the error I get in the log when I try to start mysql. 030901 19:41:35 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) I am trying to get this to run on a Red Hat 9 install of Linux. Any help would be appreciated. Thanks Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Constraint Symbol
What is the definition of the constraint symbol there is no example of it in the docs ? Also on a few tables i get errors trying to setup foreign keys ALTER TABLE departments ADD FOREIGN KEY (departmentID) REFERENCES departments_join(departmentID) ON UPDATE CASCADE this for example gives me Cannot add or update a child row: a foreign key constraint fails Any ideas ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
supersmack question regarding dictionary
If I wanted to make a dictionary entry that would just provide a sequential # starting at X and every time its used, it adds one... Is this possible? How? I do not quite understand the docs, but it seems like unique and template has this ability. Thanks, Adam
web hosting/PHP MyAdmin
Hi, I need to move my site to a host that supports PHP and MySQL. I found a nice deal that provides a lot of good stuff for an affordable price (sitelutions.com is offering 5 MySQL databases, PHP support, cgi, etc etc for $9.95/month). There's only one problem--they don't allow you to edit the MySQL databases through a shell--you have to use a web-based control panel and phpMyAdmin. My problem is that I'm used to editing my MySQL databases through the shell, and I find the phpMyAdmin admin kind of confusing. I have two questions: 1. Was anyone out there in my position before, but then once you tried phpMyAdmin, you found that you liked it a lot? 2. Does anyone know of any better, or equal deals out there that also allow shell access? Thanks very much in advance for your help. best, Matthew Gold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
I'm not too familiar with this.. someone else today used the value 50, when in fact based on their avg_row_length being reported as: Avg_row_length: 2257832 Your average row length is reported as: Avg_row_length = 20564 From: http://www.mysql.com/doc/en/CREATE_TABLE.html AVG_ROW_LENGTH An approximation of the average row length for your table. You only need to set this for large tables with variable size records. So if you are using a fixed length records, you don't need this.. otherwise. my best guess is use say: 22000 ?? If someone else has more experience with this issue, please toll in and make a suggestion based on the above values... On Thu, 4 Sep 2003, Keith Bussey wrote: > Thanks I will make new tables and transfer the data over =) > > Just wodnering though, any advice on how to tell what to set AVG_ROW_LENGTH to ? > > -- > Keith Bussey > > Wisol, Inc. > Chief Technology Manager > (514) 398-9994 ext.225 > > > Quoting [EMAIL PROTECTED]: > > > > > On Thu, 4 Sep 2003, Keith Bussey wrote: > > > Running that shows me the following: > > > > > > mysql> SHOW TABLE STATUS FROM email_tracking LIKE 'email_body_old'; > > > > > > ++++++-+-+--+---++-+-+-++-+ > > > | Name | Type | Row_format | Rows | Avg_row_length | > > Data_length | > > > Max_data_length | Index_length | Data_free > > > | Auto_increment | Create_time | Update_time | Check_time > > >| Create_options | Comment | > > > > > > ++++++-+-+--+---++-+-+-++-+ > > > | email_body_old | MyISAM | Dynamic| 208853 | 20564 | > > 4294967292 | > > > 4294967295 | 1820672 | 0 > > > | 450438 | 2002-10-21 11:58:24 | 2003-08-31 01:01:41 | 2002-11-30 > > > 01:41:00 || | > > > > > > ++++++-+-+--+---++-+-+-++-+ > > > 1 row in set (0.00 sec) > > > > > > There's your problem... your 3 bytes off your max_data_length .. which > > is giving the table full error > > > > Check: http://www.mysql.com/doc/en/Full_table.html at the bottom for > > help to alter table to keep growing > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 16:54, Keith Bussey wrote: > Thanks I will make new tables and transfer the data over =) > > Just wodnering though, any advice on how to tell what to set > AVG_ROW_LENGTH to ? Eh? Isn't that was I was saying 5.5 hours ago? This isn't anything new. Here's the message again in case it got lost somewhere: Begin quoted text On 4 Sep 2003 at 10:53, Keith Bussey wrote: > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup > ;p) Yikes! Are you running into any file system limits? Have you dealt with files larger than 4 GB on that server before with no problems? If not, you may have run into a MySQL bug of some sort. An alternative way to get the table to have 5-byte pointers would be to create the new table (same CREATE TABLE query as for the old structure, except add "MAX_ROWS=70 AVG_ROW_LENGTH=2" to the end) and then copy all the records into it: INSERT INTO email_body_NEW SELECT * FROM email_body; Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't matter, as long as their product is between 2**32 and 2**40 - 1. End quoted text -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select statement
Darryl, The following query would return all rows in employee that existed in emp2 (**Assuming 'employee' and 'emp2' have a common field 'key'). SELECT e1.* FROM employee AS e1, emp2 AS e2 WHERE e1.key = e2.key; There is a great book that introduces SQL (SQL-1 and SQL-2 concepts) titled "The Practical SQL Handbook" (ISBN: 0-201-44787-8), which I've kept in my library since college. If you're in the mood, thumb through it. Regards, Adam -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 2:58 PM To: [EMAIL PROTECTED] Subject: Select statement Greetings, I am just trying to wrap my brain around joins. I have a table employee. For each record in employee, I want to see if a record exists in table emp2 based on a field value in both tables. IE, for each employee for each emp2 if employee.field1 = emp2.field3 then do something interesting. end emp2 loop end employee loop. can someone point me to the right join syntax to get this done ? thanks, Darryl -- 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: Table is full error
Thanks I will make new tables and transfer the data over =) Just wodnering though, any advice on how to tell what to set AVG_ROW_LENGTH to ? -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting [EMAIL PROTECTED]: > > On Thu, 4 Sep 2003, Keith Bussey wrote: > > Running that shows me the following: > > > > mysql> SHOW TABLE STATUS FROM email_tracking LIKE 'email_body_old'; > > > ++++++-+-+--+---++-+-+-++-+ > > | Name | Type | Row_format | Rows | Avg_row_length | > Data_length | > > Max_data_length | Index_length | Data_free > > | Auto_increment | Create_time | Update_time | Check_time > >| Create_options | Comment | > > > ++++++-+-+--+---++-+-+-++-+ > > | email_body_old | MyISAM | Dynamic| 208853 | 20564 | > 4294967292 | > > 4294967295 | 1820672 | 0 > > | 450438 | 2002-10-21 11:58:24 | 2003-08-31 01:01:41 | 2002-11-30 > > 01:41:00 || | > > > ++++++-+-+--+---++-+-+-++-+ > > 1 row in set (0.00 sec) > > > There's your problem... your 3 bytes off your max_data_length .. which > is giving the table full error > > Check: http://www.mysql.com/doc/en/Full_table.html at the bottom for > help to alter table to keep growing > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On Thu, 4 Sep 2003, Keith Bussey wrote: > Running that shows me the following: > > mysql> SHOW TABLE STATUS FROM email_tracking LIKE 'email_body_old'; > ++++++-+-+--+---++-+-+-++-+ > | Name | Type | Row_format | Rows | Avg_row_length | Data_length | > Max_data_length | Index_length | Data_free > | Auto_increment | Create_time | Update_time | Check_time >| Create_options | Comment | > ++++++-+-+--+---++-+-+-++-+ > | email_body_old | MyISAM | Dynamic| 208853 | 20564 | 4294967292 | > 4294967295 | 1820672 | 0 > | 450438 | 2002-10-21 11:58:24 | 2003-08-31 01:01:41 | 2002-11-30 > 01:41:00 || | > ++++++-+-+--+---++-+-+-++-+ > 1 row in set (0.00 sec) There's your problem... your 3 bytes off your max_data_length .. which is giving the table full error Check: http://www.mysql.com/doc/en/Full_table.html at the bottom for help to alter table to keep growing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: keeping a fulltext index in memory
Hi Mark, I'm no Linux expert, but I think you would look at the difference between the SIZE and RSS values in top (or the equivs. in other progs...). Also IIRC, from your first message, I don't think you're using a full-text index in your query, are you? I think I saw column LIKE '%word%' and not MATCH(...) AGAINST(...)? Matt - Original Message - From: "Mark" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 03, 2003 6:35 PM Subject: Re: keeping a fulltext index in memory >Any chance you OS swapped out part of your key_buffer? See if any of >mysqld's memory is sitting in swap. > >Jeremy Hi, how exactly do I tell this on linux? Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query hung up in "Copying to tmp table"
Hi Kevin, I imagine the query is examining/returning so many rows that it's going to take a long time to create the needed temporary table (how long have you it go?). To start with, show us the EXPLAIN output for the problem SELECT, along with the SELECT. Also the size of the involved tables (rows and MB). Matt - Original Message - From: "Kevin Fries" Sent: Wednesday, September 03, 2003 6:51 PM Subject: Query hung up in "Copying to tmp table" > I'm having a problem where a complex SELECT query begins eating up lots > of CPU and never returns. In "show processlist", it > reports "Copying to tmp table". Other queries seem to get locked > waiting for this query, as well. > > This is on version 3.23.36 of mySQL, and I'm considering upgrading to > the latest 3.23.57, to see if that improves the performance. > I can't find any matching references in the Changes web pages that > indicate this, though. Can anyone verify this? > > Alternately, I'm trying to find ways to possibly improve the step of > "copying to tmp table". According to my "show variables" output, the > "tmpdir" is /tmp, and /tmp has plenty of room (85 megs or so). But > while the query is running, I don't even see a corresponding file there. > The CPU is spiking with top reporting mysql as eating 85% CPU. So I > then assume that the 'problem' is happening before the file is created? > Does that sound right? > > If someone has a better reference for tracking down the source of a > problem like this, other than > http://www.mysql.com/doc/en/MySQL_Optimisation.html, I'd be very > appreciative. > > thanks, > Kevin Fries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with variable tuning in my.cnf
I'd bump up these two settings. innodb_buffer_pool_size=32M innodb_additional_mem_pool_size=16M I'm not sure what the max is on innodb_buffer_pool_size, but that is where innodb caches data. The more that is in cache means less disk reads which equals speed. walt K Old wrote: > > Hello everyone, > > I am using a product that came packaged with Mysql 4.0.8-gamma and I > believe our configuration settings in my.cnf are too low and was wanting > a second opinion, and I'm aware of the my-huge.cnf file that comes > bundled with the mysql source and our variables are set way below the > my-huge.cnf defaults. We are using InnoDB tables and our tablespace > size is currently 2.3GB. We are running a 2Ghz IBM e-server with 3.5GB > RAM. Here's what are current settings are for mysqld in my.cnf: > > [mysqld] > port= 1205 > socket = /tmp/mysql.1205.sock > datadir = /home/edi/si/mysql/data > basedir = /home/edi/si/mysql > skip-locking > set-variable= key_buffer=16M > set-variable= max_allowed_packet=100M > set-variable= table_cache=64 > set-variable= sort_buffer=512K > set-variable= net_buffer_length=8K > set-variable= myisam_sort_buffer_size=8M > log-bin > server-id = 1 > transaction-isolation=READ-COMMITTED > #SI server > innodb_data_file_path = ibdata1:2256M;ibdata2:50M:autoextend > innodb_data_home_dir = /home/edi/si/mysql/var/ > innodb_log_group_home_dir = /home/edi/si/mysql/var/ > innodb_log_arch_dir = /home/edi/si/mysql/var/ > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=5M > set-variable = innodb_log_buffer_size=8M > innodb_flush_log_at_trx_commit=1 > innodb_log_archive=0 > set-variable = innodb_buffer_pool_size=32M > set-variable = innodb_additional_mem_pool_size=16M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=600 > set-variable = wait_timeout=114748364 > > Thanks, > Kevin > -- > K Old <[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: Table is full error
On 4 Sep 2003 at 16:10, Keith Bussey wrote: > The MYI for that table is 1.7M, and no the text field isn't indexed > > Other table has all fixed-length records except 2 varchar fields > > Thus, if I understand right, then because the field is text it uses > more pointers than a larger table that has no text fields (in my case > atleast) ? No, as I understand it, any MyISAM table with dynamic records should give the "table full" error when it reaches 4 GB, if it was created without specifying MAX_ROWS or AVG_ROW_LENGTH. If your other table doesn't have fixed-length records and was created without specifying either of those options, then I don't know how it got to 9 GB (unless maybe it doesn't have any indexes?). What does the output from SHOW TABLE STATUS and SHOW CREATE TABLE look like for it (not that that's likely to help with solving the problem for your other table)? -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL does not release locks
Heikki, There is not concunrrently running ANY queries on the table I am ALTERing. In some cases I am the only person connected to the server. Unfortunately I am not able to provide you with the proper test case because this problem is not repeatable. Yes, I encounter this problem very frequently but sometimes I am geting error messages and sometimes I am not. There is two kind of queries that cause my problems: 1) I add new columns to the table "ALTER TABLE tblName ADD COLUMN ... " 2) I make updates "UPDATE TABLE tblName SET field1='something' WHERE field2=" These queries are executed succesfully but following queries are blocked. Maybe UPDATE/ALTER queries are still hanging around and database engine thinks that operation has not been completed!? ( I do not know database engine internals ;=) This was a wild guess). I am looking forward your reply, Teemy Teemu, what kind of queries, if any, you are concurrently running on the tables you are ALTERing? " 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. " This warning means that there may still be queries running on the table MySQL is trying to drop. InnoDB must delay the actual drop operation. It would be valuable if you could provide a repeatable test case which generates this warning. This should have been fixed in some MySQL-4.0.xx, but the bug seems to persist. 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 ... Subject: Re: MySQL does not release locks From: Teemu Kuulasmaa Date: Thu, 04 Sep 2003 14:54:56 +0300 Hi, Unfortunately, nobody responded my mail. I am still trying to solve the problem I described in my earlier mail (quoted below). I am mailing again because I have some new information about the issue. I converted all my tables to InnoDB (MyISAM -> InnoDB) and this solved the problem. With InnoDB tables I have not encountered any table locks BUT database engine is continuously reporting a new kind of warnings. A section from the server ".err" log file: <> 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. <> MySQL database engine is still behaving badly but InnoDB engine is able to overcome or prevent this. Has anyone any ideas?!?! Maybe Heikki Tuuri could explain behavior of the InnoDB engine. Sincerely, Teemu Teemu wrote: Hi I have problems with mysql (4.0.6-4.0.14b) on windows 2000 SP3. MySQL locks tables when I alter table structure or execute update queries. I know that this is the exactly what database engine is supposed to do but the engine doesn't release the locks at all. This happens frequently but not allways. Approximately every third alter/update query locks table "permanently". Recently I found out that by executing "FLUSH TABLES" release locks and I am able to keep on working with the table. I have been useing only MyISAM table types. Table locking is annoying because there might be concurrent users useing the same table. They are not able to access the table at all or SELECT queries returns wrong number of records. I use different clients to execute queries phpMyAdmin, mysqlcc, mysql, MSAccess (ODBC). Table locking occurs independently of client used. For example phpMyAdmin sometimes shows following error message when I try to alter structure of locked table: ERROR 7: Error on rename of '.\front\industry.MYI to '.\front\#sql-a64-439.MYI' (ERROR: 13) I checked error code 13: Permission denied. But there shouldn't be permission problems because I use account having all privileges and only some of my queries cause these king of errors. I am not alone with this kind of problem because there is a lot of reports in various mailing lists. I searched from web and news groups but nobody knows how to overcome the issue. It might be that the problem is win32 specific. I would be more than thankfull if someone knows reason for table locking. Sincerely, Teemu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
Quoting "Keith C. Ivey" <[EMAIL PROTECTED]>: > On 4 Sep 2003 at 15:31, Keith Bussey wrote: > > > I do have another Database on the same machine which has a table that > > is a lot bigger (about 9 GIGs) and it wasn't created with any of the > > special table options suggested by Keith C. Ivey below. > > Does the other table have fixed-length records (no VARCHAR, TEXT, or > BLOB columns)? If so, the numbers for the pointers are in records, > not bytes, so the table can reach 2**32 - 1 records before it's full. > You can see what the maximum data length is with SHOW TABLE STATUS. > > For teh email_boy table, do you have any index other than the one for > the primary key? The .MYI file isn't getting big, is it? The MYI for that table is 1.7M, and no the text field isn't indexed Other table has all fixed-length records except 2 varchar fields Thus, if I understand right, then because the field is text it uses more pointers than a larger table that has no text fields (in my case atleast) ? > -- > Keith C. Ivey <[EMAIL PROTECTED]> > Tobacco Documents Online > http://tobaccodocuments.org > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
Quoting Colbey <[EMAIL PROTECTED]>: > Hrm.. interesting.. I know I've personally hit the 4gb limit before and > had to do a fix for it.. but never hit an internal tablesize limitation.. > > You didn't mention what version of mysql your running... I mentioned in my original email ;p.4.0.13-standard > Also if the table has alot of columns and 1 of more of those columns has > alot of data, perhaps considering looking at normalizing the table into a > few? Could potentially speed up operations.. The table with a lot of columns (by alot I mean like 25 or so) is running great, it just has millions of records...but there are no text fields in it, there are fields that link to tables with id-text_field. The problem is one of these tables (2 column table) with ID - text_field. > Not the easy fix your looking for but a thought... > > Try running: SHOW TABLE STATUS FROM database LIKE 'table_name'; > > It will check the max index/data sizes mysql is configured/allocated > for... Running that shows me the following: mysql> SHOW TABLE STATUS FROM email_tracking LIKE 'email_body_old'; ++++++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++++-+-+--+---++-+-+-++-+ | email_body_old | MyISAM | Dynamic| 208853 | 20564 | 4294967292 | 4294967295 | 1820672 | 0 | 450438 | 2002-10-21 11:58:24 | 2003-08-31 01:01:41 | 2002-11-30 01:41:00 || | ++++++-+-+--+---++-+-+-++-+ 1 row in set (0.00 sec) > On Thu, 4 Sep 2003, Keith Bussey wrote: > > > I do have another Database on the same machine which has a table that is a > lot > > bigger (about 9 GIGs) and it wasn't created with any of the special table > > options suggested by Keith C. Ivey below. > > > > The difference is this table has many more fields, while the email_body one > (the > > one with the problem) as only 2: > > > > an ID autonumber field, and a text field. > > > > Perhaps there is some bug/limitation in Mysql whereby a field can only have > so > > much size ?? > > > > -- > > Keith Bussey > > > > Wisol, Inc. > > Chief Technology Manager > > (514) 398-9994 ext.225 > > > > > > Quoting Colbey <[EMAIL PROTECTED]>: > > > > > > > > Most likely it's the 4GB OS limitation... My suggestion is to create a > > > new table using mysql's built in raid option... span the table over > > > multiple files to allow of much larger table growth... > > > > > > migrate all the rows over to the new spanned table.. > > > > > > > > > > > > On Thu, 4 Sep 2003, Keith C. Ivey wrote: > > > > > > > On 4 Sep 2003 at 10:53, Keith Bussey wrote: > > > > > > > > > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup > ;p) > > > > > > > > Yikes! Are you running into any file system limits? Have you dealt > > > > with files larger than 4 GB on that server before with no problems? > > > > If not, you may have run into a MySQL bug of some sort. > > > > > > > > An alternative way to get the table to have 5-byte pointers would be > > > > to create the new table (same CREATE TABLE query as for the old > > > > structure, except add "MAX_ROWS=70 AVG_ROW_LENGTH=2" to the > > > > end) and then copy all the records into it: > > > > > > > >INSERT INTO email_body_NEW SELECT * FROM email_body; > > > > > > > > Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't > > > > matter, as long as their product is between 2**32 and 2**40 - 1. > > > > > > > > -- > > > > Keith C. Ivey <[EMAIL PROTECTED]> > > > > Tobacco Documents Online > > > > http://tobaccodocuments.org > > > > > > > > > > > > -- > > > > 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: Table is full error
On 4 Sep 2003 at 15:31, Keith Bussey wrote: > I do have another Database on the same machine which has a table that > is a lot bigger (about 9 GIGs) and it wasn't created with any of the > special table options suggested by Keith C. Ivey below. Does the other table have fixed-length records (no VARCHAR, TEXT, or BLOB columns)? If so, the numbers for the pointers are in records, not bytes, so the table can reach 2**32 - 1 records before it's full. You can see what the maximum data length is with SHOW TABLE STATUS. For teh email_boy table, do you have any index other than the one for the primary key? The .MYI file isn't getting big, is it? -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
Hrm.. interesting.. I know I've personally hit the 4gb limit before and had to do a fix for it.. but never hit an internal tablesize limitation.. You didn't mention what version of mysql your running... Also if the table has alot of columns and 1 of more of those columns has alot of data, perhaps considering looking at normalizing the table into a few? Could potentially speed up operations.. Not the easy fix your looking for but a thought... Try running: SHOW TABLE STATUS FROM database LIKE 'table_name'; It will check the max index/data sizes mysql is configured/allocated for... On Thu, 4 Sep 2003, Keith Bussey wrote: > I do have another Database on the same machine which has a table that is a lot > bigger (about 9 GIGs) and it wasn't created with any of the special table > options suggested by Keith C. Ivey below. > > The difference is this table has many more fields, while the email_body one (the > one with the problem) as only 2: > > an ID autonumber field, and a text field. > > Perhaps there is some bug/limitation in Mysql whereby a field can only have so > much size ?? > > -- > Keith Bussey > > Wisol, Inc. > Chief Technology Manager > (514) 398-9994 ext.225 > > > Quoting Colbey <[EMAIL PROTECTED]>: > > > > > Most likely it's the 4GB OS limitation... My suggestion is to create a > > new table using mysql's built in raid option... span the table over > > multiple files to allow of much larger table growth... > > > > migrate all the rows over to the new spanned table.. > > > > > > > > On Thu, 4 Sep 2003, Keith C. Ivey wrote: > > > > > On 4 Sep 2003 at 10:53, Keith Bussey wrote: > > > > > > > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) > > > > > > Yikes! Are you running into any file system limits? Have you dealt > > > with files larger than 4 GB on that server before with no problems? > > > If not, you may have run into a MySQL bug of some sort. > > > > > > An alternative way to get the table to have 5-byte pointers would be > > > to create the new table (same CREATE TABLE query as for the old > > > structure, except add "MAX_ROWS=70 AVG_ROW_LENGTH=2" to the > > > end) and then copy all the records into it: > > > > > >INSERT INTO email_body_NEW SELECT * FROM email_body; > > > > > > Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't > > > matter, as long as their product is between 2**32 and 2**40 - 1. > > > > > > -- > > > Keith C. Ivey <[EMAIL PROTECTED]> > > > Tobacco Documents Online > > > http://tobaccodocuments.org > > > > > > > > > -- > > > 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: Personal Servers
[EMAIL PROTECTED] wrote: > I'd recommend Apache, www.apache.org, but be aware that Windows OSes come A "have to" for geeks and those who want to rely blindly on their software. I think Nicola is better served with a search on Tucows or a similar portal for http servers for non-geeks. 602Pro LanSuite 2003 would be "good" choice". But there is IMO any server software as reliable as the Apache httpd. Regards, Jakob ^-- To Unix or not to Unix. That is the question whether 'tis nobler in the mind to suffer slings and arrows of vast documentation or to take arms against a sea of buggy OS and by raping the support lines end then? ;> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
I do have another Database on the same machine which has a table that is a lot bigger (about 9 GIGs) and it wasn't created with any of the special table options suggested by Keith C. Ivey below. The difference is this table has many more fields, while the email_body one (the one with the problem) as only 2: an ID autonumber field, and a text field. Perhaps there is some bug/limitation in Mysql whereby a field can only have so much size ?? -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Colbey <[EMAIL PROTECTED]>: > > Most likely it's the 4GB OS limitation... My suggestion is to create a > new table using mysql's built in raid option... span the table over > multiple files to allow of much larger table growth... > > migrate all the rows over to the new spanned table.. > > > > On Thu, 4 Sep 2003, Keith C. Ivey wrote: > > > On 4 Sep 2003 at 10:53, Keith Bussey wrote: > > > > > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) > > > > Yikes! Are you running into any file system limits? Have you dealt > > with files larger than 4 GB on that server before with no problems? > > If not, you may have run into a MySQL bug of some sort. > > > > An alternative way to get the table to have 5-byte pointers would be > > to create the new table (same CREATE TABLE query as for the old > > structure, except add "MAX_ROWS=70 AVG_ROW_LENGTH=2" to the > > end) and then copy all the records into it: > > > >INSERT INTO email_body_NEW SELECT * FROM email_body; > > > > Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't > > matter, as long as their product is between 2**32 and 2**40 - 1. > > > > -- > > Keith C. Ivey <[EMAIL PROTECTED]> > > Tobacco Documents Online > > http://tobaccodocuments.org > > > > > > -- > > 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: Table is full error
Most likely it's the 4GB OS limitation... My suggestion is to create a new table using mysql's built in raid option... span the table over multiple files to allow of much larger table growth... migrate all the rows over to the new spanned table.. On Thu, 4 Sep 2003, Keith C. Ivey wrote: > On 4 Sep 2003 at 10:53, Keith Bussey wrote: > > > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) > > Yikes! Are you running into any file system limits? Have you dealt > with files larger than 4 GB on that server before with no problems? > If not, you may have run into a MySQL bug of some sort. > > An alternative way to get the table to have 5-byte pointers would be > to create the new table (same CREATE TABLE query as for the old > structure, except add "MAX_ROWS=70 AVG_ROW_LENGTH=2" to the > end) and then copy all the records into it: > >INSERT INTO email_body_NEW SELECT * FROM email_body; > > Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't > matter, as long as their product is between 2**32 and 2**40 - 1. > > -- > Keith C. Ivey <[EMAIL PROTECTED]> > Tobacco Documents Online > http://tobaccodocuments.org > > > -- > 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: MySQL 4.0.14 stops responding to PHP 4.3.2
> No, it turns out this is not the key. With mysql_connect() I'm actually > failing MORE often than with mysql_pconnect - so far it hasn't stayed > up 15 minutes without error. (Fortunately, I have a cron job checking > on it and restarting.) After the failed connection attempt, there will be an error message you can get from mysql_error(). What is it? Also, rather than restart, will "mysqladmin flush-hosts" fix it as well? (We have to run the flush-hosts every hour in a cron job. Never bothered to figure out the root cause as this fixes things.) --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB and lots of UPDATES
> Use transaction: > > begin > update ... > update ... > ... > update ... > commit; > > This way you will only have a syncs to disk at every commit instead of every > update. This won't help -- I'm not doing a batch process. Each update is coming from a different connection... --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select statement
On Thu, 4 Sep 2003 13:57:53 -0500, Darryl Hoar <[EMAIL PROTECTED]> wrote: Joins can in fact be rather confusing at first. The MySQL manual doesn't help out much, either, I'm afraid. There are lots of good references available though. I don't know of any online right off hand, but _SQL Queries for Mere Mortals_ (Hernandez and Viescas) is a very good book. Anyway, try this: SELECT * FROM employee JOIN emp2 ON employee.field1 = emp2.field3 This will give a combination of all columns from both tables where the ON condition holds true. To restrict the columns returned, change the * to list the columns. To get all employees with NULL for those who don't have an entry in emp2 change the JOIN to a LEFT JOIN. Hope this helps. Michael Greetings, I am just trying to wrap my brain around joins. I have a table employee. For each record in employee, I want to see if a record exists in table emp2 based on a field value in both tables. IE, for each employee for each emp2 if employee.field1 = emp2.field3 then do something interesting. end emp2 loop end employee loop. can someone point me to the right join syntax to get this done ? thanks, Darryl -- Michael Johnson < [EMAIL PROTECTED] > Internet Application Programmer, Pitsco, Inc. 620-231-2424x516 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select statement
Greetings, I am just trying to wrap my brain around joins. I have a table employee. For each record in employee, I want to see if a record exists in table emp2 based on a field value in both tables. IE, for each employee for each emp2 if employee.field1 = emp2.field3 then do something interesting. end emp2 loop end employee loop. can someone point me to the right join syntax to get this done ? thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with variable tuning in my.cnf
Hello everyone, I am using a product that came packaged with Mysql 4.0.8-gamma and I believe our configuration settings in my.cnf are too low and was wanting a second opinion, and I'm aware of the my-huge.cnf file that comes bundled with the mysql source and our variables are set way below the my-huge.cnf defaults. We are using InnoDB tables and our tablespace size is currently 2.3GB. We are running a 2Ghz IBM e-server with 3.5GB RAM. Here's what are current settings are for mysqld in my.cnf: [mysqld] port= 1205 socket = /tmp/mysql.1205.sock datadir = /home/edi/si/mysql/data basedir = /home/edi/si/mysql skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=100M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M log-bin server-id = 1 transaction-isolation=READ-COMMITTED #SI server innodb_data_file_path = ibdata1:2256M;ibdata2:50M:autoextend innodb_data_home_dir = /home/edi/si/mysql/var/ innodb_log_group_home_dir = /home/edi/si/mysql/var/ innodb_log_arch_dir = /home/edi/si/mysql/var/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=32M set-variable = innodb_additional_mem_pool_size=16M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=600 set-variable = wait_timeout=114748364 Thanks, Kevin -- K Old <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table is full error
I also had table is full error, today actually. mysql> alter table mytable max_rows = 2000 avg_row_length=50; mysql> show table status like 'mytable' \G *** 1. row *** Name: mytable Type: MyISAM Row_format: Dynamic Rows: 157 Avg_row_length: 2257832 Data_length: 354479668 Max_data_length: 1099511627775 Index_length: 3072 Data_free: 0 Auto_increment: NULL Create_time: 2003-09-04 12:17:56 Update_time: 2003-09-04 12:18:09 Check_time: NULL Create_options: max_rows=4294967295 avg_row_length=50 Comment: 1 row in set (0.03 sec) this fixed my problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ISAM....the name sounds so familiar....
In the last episode (Sep 05), Chris Nolan said: > One of my clients has two applications running on SCO OpenServer (I > said a naughty word...oh dear...) boxes. Personally, I have major > personal and professional problems with this current arrangement and > am trying as quickly as possible to move them away from these ancient > things. > > Interestingly, both of the applications in question (written in COBOL > for reasons I fail to understand) both mention ISAM storage engines > when starting. Is there any relationship here to the storage engine > MySQL uses as it's default? I'm just looking for an easy way of > pulling this data out should it be needed for whatever reason and > would prefer to do it with a proper database, written in a proper > language, running on a proper operating system (preferably running on > a proper server platform, like an x86-64 box). ISAM just stands for "indexed sequential access method"; basically any flat file database with an optional separate index file can be called ISAM. MySQL almost certainly will not be able to open those databases. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL syntax question
Hi, Have a look at: http://www.mysql.com/doc/en/String_functions.html You can find there functions to use in SELECT and WHERE clauses, like UPPER(), LOWER(), SUBSTRING(), etc. and http://www.mysql.com/doc/en/String_comparison_functions.html for string comparison functions (LIKE, REGEXP, MATCH AGAINST, ...). On Thu, 2003-09-04 at 18:32, Darryl Hoar wrote: > greetings, > When I am doing a select or update statement, I was wondering if there were > functions to compare strings. IE > > Select * from employee,emp2 where uppercase(employee.fname) > matches(emp2.fname*) > > that is to compare two fields from two tables and see if they match > regardless of whether > one is upper,lower,mixed case. Also see if table1.field1 is a partial match > to another. > > So, > JOHNATHAN would match Jon or Jonny. > > thanks, > Darryl -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ISAM....the name sounds so familiar....
Chris, Wow, tell us how you really feel. Less age is getting the better of me ISAM (indexed sequential access method) tables are used by a few different DMS such as Informix. ISAM was original developed by IBM. It allows data access sequentially or randomly via indices. Anyway, are you asking if MySQL can simply use the .ISM files from this other OS? To the best of my knowledge, ISAM files are stored in a OS' native language - the ISAM files are not platform independent. MySQL Specific: http://www.mysql.com/doc/en/ISAM.html Regards, Adam -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 9:48 PM To: [EMAIL PROTECTED] Subject: ISAMthe name sounds so familiar Hi all, One of my clients has two applications running on SCO OpenServer (I said a naughty word...oh dear...) boxes. Personally, I have major personal and professional problems with this current arrangement and am trying as quickly as possible to move them away from these ancient things. Interestingly, both of the applications in question (written in COBOL for reasons I fail to understand) both mention ISAM storage engines when starting. Is there any relationship here to the storage engine MySQL uses as it's default? I'm just looking for an easy way of pulling this data out should it be needed for whatever reason and would prefer to do it with a proper database, written in a proper language, running on a proper operating system (preferably running on a proper server platform, like an x86-64 box). Regards, Chris -- 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 syntax question
greetings, When I am doing a select or update statement, I was wondering if there were functions to compare strings. IE Select * from employee,emp2 where uppercase(employee.fname) matches(emp2.fname*) that is to compare two fields from two tables and see if they match regardless of whether one is upper,lower,mixed case. Also see if table1.field1 is a partial match to another. So, JOHNATHAN would match Jon or Jonny. thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myODBC on Cobalt RAQ4
Hello all, I was wondering if any of you have ever added the MyODBC to a Cobalt raq4? I have downloaded the RPM ( MyODBC-3.51.01.i386-1.rpm ) When I go to rpm the file I recieve the error below--- [root /JON]# rpm -ivh MyODBC-3.51.06-1.i386.rpm error: failed dependencies: libc.so.6(GLIBC_2.2) is needed by MyODBC-3.51.06-1 [root /JON]# - I have downloaded the glibc-2.2.4-23.i386.rpm and tryed to load that and I recive many errors complaining [root /JON]# rpm -Uvh glibc-2.2.4-23.i386.rpm error: failed dependencies: libdl.so.1 is needed by frontpage-4.0-3 /lib/ld-linux.so.1 is needed by libc-5.3.12-31 libdb.so.2 is needed by python-1.5.2-13 libdb.so.2 is needed by rpm-3.0.5-9.6x libdb.so.2 is needed by rpm-build-3.0.5-9.6x libdb.so.2 is needed by rpm-python-3.0.5-9.6x libdb.so.2(GLIBC_2.0) is needed by python-1.5.2-13 libdb.so.2(GLIBC_2.0) is needed by rpm-3.0.5-9.6x libdb.so.3 is needed by openldap-1.2.9-6 libdb.so.3 is needed by pam-0.72-7 libdb.so.3 is needed by perl-5.00503-2 libdb.so.3 is needed by python-1.5.2-13 libdb.so.3 is needed by chiliasp-3.5.2L-C3 libdb.so.3 is needed by vim-enhanced-5.7-0.6x libdb.so.3 is needed by cyrus-sasl-1.5.24-C4 libdb.so.3 is needed by apache-1.3.20-RaQ4_1C4stackguard libdb.so.3 is needed by apache-admsrv-1.3.20-RaQ4_1C4stackguard libdb.so.3 is needed by sendmail-8.10.2-C4 libdb.so.3(GLIBC_2.0) is needed by python-1.5.2-13 libdb.so.3(GLIBC_2.1) is needed by openldap-1.2.9-6 libdb.so.3(GLIBC_2.1) is needed by pam-0.72-7 libdb.so.3(GLIBC_2.1) is needed by perl-5.00503-2 libdb.so.3(GLIBC_2.1) is needed by cyrus-sasl-1.5.24-C4 libdb.so.3(GLIBC_2.1) is needed by sendmail-8.10.2-C4 [root /JON]# Any help would be appeciated :) -- Best Regards, Phil Phillip Blancher Web Developer & Lead Graphic Designer < http://www.ontarioweb.ca > 1-866-209-0349 < [EMAIL PROTECTED] > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 9/1/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling libwrap support fails - why?
i am attempting to compile libwrap support into mysql 4.0.14. my host is rh7.2 with 2.4.18-18.7.x kernel. i am using gcc-3.3.1 to compile with. my version of tcp_wrappers is 7.6-19. i was wondering if there was a version incompatibility somewhere, or if there was somestep in compilation i was missing. the error is regarding too many arguments to ()void tcpd.h (yes, this isn't exact text, but for those familiar w/ the error, this should be enough, everyone else just look the other way) also, could anyone tell me what CFLAGS, CXXFLAGS, CPPFLAGS, and configure options i might want to use to optimize this build for my platform, i intend to use this mysql as a backend for a mail server...so it will have a high insert/query load. thanks, shon -- "power off einstein" - moe syzlak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL does not release locks
Teemu, what kind of queries, if any, you are concurrently running on the tables you are ALTERing? " 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. " This warning means that there may still be queries running on the table MySQL is trying to drop. InnoDB must delay the actual drop operation. It would be valuable if you could provide a repeatable test case which generates this warning. This should have been fixed in some MySQL-4.0.xx, but the bug seems to persist. 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 ... Subject: Re: MySQL does not release locks From: Teemu Kuulasmaa Date: Thu, 04 Sep 2003 14:54:56 +0300 Hi, Unfortunately, nobody responded my mail. I am still trying to solve the problem I described in my earlier mail (quoted below). I am mailing again because I have some new information about the issue. I converted all my tables to InnoDB (MyISAM -> InnoDB) and this solved the problem. With InnoDB tables I have not encountered any table locks BUT database engine is continuously reporting a new kind of warnings. A section from the server ".err" log file: <> 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. <> MySQL database engine is still behaving badly but InnoDB engine is able to overcome or prevent this. Has anyone any ideas?!?! Maybe Heikki Tuuri could explain behavior of the InnoDB engine. Sincerely, Teemu Teemu wrote: > Hi > > I have problems with mysql (4.0.6-4.0.14b) on windows 2000 SP3. MySQL > locks tables when I alter table structure or execute update queries. I > know that this is the exactly what database engine is supposed to do but > the engine doesn't release the locks at all. This happens frequently but > not allways. Approximately every third alter/update query locks table > "permanently". Recently I found out that by executing "FLUSH TABLES" > release locks and I am able to keep on working with the table. I have > been useing only MyISAM table types. > > Table locking is annoying because there might be concurrent users useing > the same table. They are not able to access the table at all or SELECT > queries returns wrong number of records. > > I use different clients to execute queries phpMyAdmin, mysqlcc, mysql, > MSAccess (ODBC). Table locking occurs independently of client used. > For example phpMyAdmin sometimes shows following error message when I > try to alter structure of locked table: > > ERROR 7: Error on rename of '.\front\industry.MYI to > '.\front\#sql-a64-439.MYI' (ERROR: 13) > > I checked error code 13: Permission denied. But there shouldn't be > permission problems because I use account having all privileges and only > some of my queries cause these king of errors. > > I am not alone with this kind of problem because there is a lot of > reports in various mailing lists. I searched from web and news groups > but nobody knows how to overcome the issue. It might be that the problem > is win32 specific. > > I would be more than thankfull if someone knows reason for table locking. > > Sincerely, > > Teemu > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 10:53, Keith Bussey wrote: > Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) Yikes! Are you running into any file system limits? Have you dealt with files larger than 4 GB on that server before with no problems? If not, you may have run into a MySQL bug of some sort. An alternative way to get the table to have 5-byte pointers would be to create the new table (same CREATE TABLE query as for the old structure, except add "MAX_ROWS=70 AVG_ROW_LENGTH=2" to the end) and then copy all the records into it: INSERT INTO email_body_NEW SELECT * FROM email_body; Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't matter, as long as their product is between 2**32 and 2**40 - 1. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14 stops responding to PHP 4.3.2
On Thursday, Sep 4, 2003, at 10:03 US/Eastern, Parker Morse wrote: On Thursday, Sep 4, 2003, at 01:27 US/Eastern, Antony Dovgal wrote: Please, read http://www.php.net/manual/en/features.persistent-connections.php I recommend you not to use *_pconnect. There are some big problems with persistent connections and these problems are 'by design'. Use SQLrelay if you need real connection pooling. In your case MySQL probably says 'too many connections' and you can catch this error message if you'll turn on error_log in php.ini. That sounds like a plausible explanation - by restarting mysqld I'd be closing all the open connections, admittedly the hard way. I've changed the mysql_pconnect() calls to mysql_connect(), and I'm reading up in the manual. I don't understand all of it yet, which should probably tell me to stick to mysql_connect! Meanwhile, I'll see if I have any more failures using mysql_connect. No, it turns out this is not the key. With mysql_connect() I'm actually failing MORE often than with mysql_pconnect - so far it hasn't stayed up 15 minutes without error. (Fortunately, I have a cron job checking on it and restarting.) I have error_log turned on in php.ini, but there's nothing at the specified path. Likewise, the mysql_error log is only showing the restarts. pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with #1111 - Invalid use of group function
Hi! Try this: SELECT TD.project_id, P.project_manager, SUM( TD.time_hours_worked ) as hours FROM time_daily TD INNER JOIN projects P ON P.project_id = TD.time_project_id WHERE TD.time_user_id = 'xpt' HAVING hours <> '0.00' Hope this helps! On Wed, 2003-09-03 at 17:24, Cory Hicks wrote: > Hey folks, > > I am trying to run the following sql query in mysql: > > SELECT TD.project_id, P.project_manager > FROM time_daily TD > INNER JOIN projects P ON P.project_id = TD.time_project_id > WHERE TD.time_user_id = 'xpt' AND ( > SUM( TD.time_hours_worked ) <> '0.00' > ) > > And I keep getting the # errno.- Invalid use of group function - > > I don't want to pull out any rows where the SUM of time_hours_worked is > '0.00'... > > I would be most grateful if anyone has any suggestions > > Many thanks! > > Cory -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1, derived tables, and privileges
Dude ... You got the same name as me ... So does that mean I can blame any idiotic statements I make on you ? We got a popular name. Mike -Original Message- From: Michael Johnson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 8:51 AM To: [EMAIL PROTECTED] Subject: MySQL 4.1, derived tables, and privileges I hope someone can help me on this. I've searched the mailing list archives and the manual and can't find anything, except for user comments in the manual that confirm what I'm finding. I'm using derived tables users with restricted privileges. The only way I can get the derived tables to work though is to give the user global SELECT privileges. I don't want to do this. Am I missing something? If not, is there a way around this problem? The error I get is: #1142 - select command denied to user: '[EMAIL PROTECTED]' for table '/tmp/#sql_135_0' My select looks something like: SELECT t1id as "Key", IFNULL(k2, dfltValue) as "Value" FROM t1 NATURAL JOIN (SELECT t1id, k2 FROM t2 WHERE k3=1) as derived; My tables have the columns: t1: t1id, dfltValue 1 a 2 b t2: t1id, k2, k3 1c 1 1d 2 2e 2 Note that not all values of t1id exist in t2 for a given k3. Hence, this select gets all t1id keys with a default value if it doesn't exist in t2. Desired result: Key Value 1c 2b The privileges for [EMAIL PROTECTED] are: No global priveleges (setting Create_tmp_table_priv makes no difference) t1: SELECT, REFERENCES t2: SELECT, INSERT, UPDATE, REFERENCES If I grant global SELECT it works as expected. Again, create_tmp_table_priv is irrelevant at this point. Any thoughts on why this is working like this? Thanks, Michael PS If there is a better way to do this query, I'd like to know that, too, but I've run into this problem at other places where I *know* I have to use derived tables, so the problem is still pertinent to my use. -- Michael Johnson < [EMAIL PROTECTED] > Internet Application Programmer, Pitsco, Inc. 620-231-2424x516 -- 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: Here is a strange one...
This is the output from check table ArpMon; mysql> check table ArpMon; +--+---+--+-+ | Table| Op| Msg_type | Msg_text| +--+---+--+-+ | arpdb.ArpMon | check | warning | 0 clients is using or hasn't closed the table properly | | arpdb.ArpMon | check | warning | Size of datafile is: 1796436 Should be: 1796176 | | arpdb.ArpMon | check | error| Keypointers and record positions doesn't match | | arpdb.ArpMon | check | warning | Found 27507 parts Should be: 27498 parts | | arpdb.ArpMon | check | error| Corrupt | +--+---+--+-+ 5 rows in set (0.85 sec) -James On Wed, 2003-09-03 at 18:41, Paul DuBois wrote: > At 16:49 -0700 9/3/03, James Kelty wrote: > >So, we have this table: ArpMon that looks like this: > > > >-- mysql> describe ArpMon; > >++---+--+-+-+---+ > >| Field | Type | Null | Key | Default | Extra | > >++---+--+-+-+---+ > >| am_mac | varchar(20) | | PRI | | | > >| am_ip | varchar(255) | | PRI | | | > >| am_rtr | varchar(255) | YES | | NULL| | > >| am_if | int(10) | YES | | NULL| | > >| am_ts | timestamp(14) | YES | | NULL| | > >++---+--+-+-+---+ > > > >When I do a count(*) on this table, we get this result: > > > >mysql> select count(*) from ArpMon; > >+--+ > >| count(*) | > >+--+ > >|27498 | > >+--+ > >1 row in set (0.01 sec) > > > >Ok, that's good. But! If I run select * from ArpMon, I only get 111 entries > > That's odd. > > >and if I run select count(am_rtr) from ArpMon, I get 111 entries as well as > > That could, if you have only 111 non-NULL am_rtr values. > > >select am_rtr from ArpMon. > > > >But! It I run select am_mac from ArpMon, I get the 27498 entries. > > > >What's up with that? Can someone help me to figure this one out? > > What does CHECK TABLE ArpMon tell you? > > > > >-James > > > >James Kelty > >E-Commerce / Financial Systems Administrator > >Portland State University > >503.725.9152 > >[EMAIL PROTECTED] -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'Commands out of sync' when moving app from Linux to MacOS X
I have a complex web application consisting of several programs totalling about 100k lines of C code that has been running successfully under Linux. To improve security, I want to port the application to MacOS X, where considerably less effort goes into designing security breaches. When I try compiling my programs on MacOS X, they compile fine, but in many cases I am getting a "Commands out of Sync" error when previously I had no errors. They seem to fail consistently in the same place, but I'm having a really tough time telling why they're failing. The problem seems to happen in the following circumstances: * I have nested queries - that is, I run one query in a loop, and have the results from that query drive another. For instance, select id from menu where title = 'test' and then select id, title from submenu where menu_id = [menu ID from first query] * It appears to be linked to a function I have where I use mysql_fetch_fields or mysql_fetch_field to get all the field names in a query. At first I thought there might be some kind of cleanup required for those functions, but I could not find it. What I did notice was that switching from mysql_fetch_fields to mysql_fetch_field was able to solve the problem in some, but not all, cases. * My SQL query function that I use for all queries returning values uses mysql_store_result. I never use the use_result function, so that can be ruled out as a reason for the problem. I was wondering in what way this type of thing would work differently in MacOS X versus Linux, and what sorts of solutions I should look to to trouble shoot the problem. I have written mySQL C programs in MacOS X before, and none of them have given me this kind of difficulty. In fact, I normally have no trouble moving the programs between Linux, MacOS X and Irix ... except now. I have used the same basic functions in other projects and they have worked fine on all platforms, so I'm baffled as to what I might have done wrong here. One other thing that changed is that I'm accessing the mySQL database server remotely from the MacOS X box, while leaving the actual database on the Linux box, which for now is still running the working copy of the web application. Would that make any difference? Can anyone point me in the right direction or suggest some ways to approach the problem? Many thanks. D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Personal Servers
I'd recommend Apache, www.apache.org, but be aware that Windows OSes come with a personal version of IIS that can be used as a single server (i.e., no virtuals). Check Windows Components in Add/Remove Programs. The Apache site has good documentation and some IIS info can be found in the Windows help files. Prabu Subroto <[EMAIL PROTECTED]To: [EMAIL PROTECTED] oo.com> cc: Subject: Re: Personal Servers 09/04/2003 08:06 AM Many... but try Xitami and Apache. They're great. BTW, XP? Try SuSE, far better then Windoof. --- Nicola Hartland <[EMAIL PROTECTED]> wrote: > I am not sure this is the right place for this > question but I am trying to find a personal server > that I can install on my PC, I run XP that I can > have to test the web pages. Does anyone have any > suggestions and how to configure them. > > thanks in advance > > Nicci __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.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]
FULLTEXT search
Hi all, At the moment, we all know that InnoDB does not yet have FULLTEXT indexes. This is not another message asking as to when we will have that functionality. This message is different. On the mailing list for DBMail, we've been discussing MySQL table types, with everyone highlighting the fact that MyISAM does not support transactions while InnoDB does (in addition to foreign keys). Then, I raised the point that MyISAM may not have transactions, but it does have FULLTEXT indexes, which could be a massive source of speed for MyISAM-based tables. My question is this: Obviously, if you throw SQL statements enclosed in BEGIN/COMMIT and issue ROLLBACK statements on MyISAM tables, MyISAM does the right thing and ignores it (personally, I think it would be better if it alerted the nearest admin, so that they could come and deal with any person touching their finely tuned database server). Is the converse-ish statement true? Is there any way that FULLTEXT searches could be executed on InnoDB (and other type) tables currently or with a quick patch that uses a full table scan? I'd be willing to get together with a few people to write such a thing, as it would speed development while FULLTEXT is a MyISAM exclusive while still allowing testing against all MySQL tables in the immediate future. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1, derived tables, and privileges
I hope someone can help me on this. I've searched the mailing list archives and the manual and can't find anything, except for user comments in the manual that confirm what I'm finding. I'm using derived tables users with restricted privileges. The only way I can get the derived tables to work though is to give the user global SELECT privileges. I don't want to do this. Am I missing something? If not, is there a way around this problem? The error I get is: #1142 - select command denied to user: '[EMAIL PROTECTED]' for table '/tmp/#sql_135_0' My select looks something like: SELECT t1id as "Key", IFNULL(k2, dfltValue) as "Value" FROM t1 NATURAL JOIN (SELECT t1id, k2 FROM t2 WHERE k3=1) as derived; My tables have the columns: t1: t1id, dfltValue 1 a 2 b t2: t1id, k2, k3 1c 1 1d 2 2e 2 Note that not all values of t1id exist in t2 for a given k3. Hence, this select gets all t1id keys with a default value if it doesn't exist in t2. Desired result: Key Value 1c 2b The privileges for [EMAIL PROTECTED] are: No global priveleges (setting Create_tmp_table_priv makes no difference) t1: SELECT, REFERENCES t2: SELECT, INSERT, UPDATE, REFERENCES If I grant global SELECT it works as expected. Again, create_tmp_table_priv is irrelevant at this point. Any thoughts on why this is working like this? Thanks, Michael PS If there is a better way to do this query, I'd like to know that, too, but I've run into this problem at other places where I *know* I have to use derived tables, so the problem is still pertinent to my use. -- Michael Johnson < [EMAIL PROTECTED] > Internet Application Programmer, Pitsco, Inc. 620-231-2424x516 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ISAM....the name sounds so familiar....
Hi all, One of my clients has two applications running on SCO OpenServer (I said a naughty word...oh dear...) boxes. Personally, I have major personal and professional problems with this current arrangement and am trying as quickly as possible to move them away from these ancient things. Interestingly, both of the applications in question (written in COBOL for reasons I fail to understand) both mention ISAM storage engines when starting. Is there any relationship here to the storage engine MySQL uses as it's default? I'm just looking for an easy way of pulling this data out should it be needed for whatever reason and would prefer to do it with a proper database, written in a proper language, running on a proper operating system (preferably running on a proper server platform, like an x86-64 box). Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting auto increment back to zero after it reaches it limit
> I'm using InnoDB tables. I've set one of my fields to auto increment. > This field has a relationship with another table. I need to > reset auto increment back to 1 after it reaches the max value (And it will). A you sure about that last? If you use BIGINT (63 bits), at 1000 inserts per second (a generous rate, IMO), you will not run out for over 200 million years. May I suggest that when it fially does run out, it wioll be Someone Elses Problem? If this is not acceptable, put $1 in an interest bearing account today. After about 100 million years, withdraw the sum and use it to pay MySQL AB to implement 128 bit ints. That should see you out for the rest of the lifetime of the universe. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone using MySQL with kernel 2.6 / RedHat 9.0?
Hi all! Is anyone running MySQL on Linux kernel 2.6 or the kernel shipped with RedHat 9.0? I am curious as to the impact of the new thread implementation on MySQL's stellar performance (I like picking on the Oracle, MS SQL Server and Foxpro admins that I have to work with. What exactly is a Foxpro admin anyway? Do they reindex things and nothing else?). Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign keys in MySQL 5.0
Hi all! Looking at one of the things that is set to be added to MySQL 5.0 (foreign keys for all table types), I have the following question: Will we be able to have foreign keys that involve tables of different types? Any early indications about the performance implications of having such things in one's schema? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting auto increment back to zero after it reaches it limit
Hi - I'm using InnoDB tables. I've set one of my fields to auto increment. This field has a relationship with another table. I need to reset auto increment back to 1 after it reaches the max value (And it will). Does anyone know how I can do this ? I basically need functionality similar to Oracle sequences. Your help is much appreciated Brenda Cooney -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
On 4 Sep 2003 at 10:13, Keith Bussey wrote: > -rw-rw 1 mysqlmysql4.0G Aug 31 01:01 email_body.MYD > > Thus if I try and insert one more row I get the error: > > ERROR 1114: The table 'email_body' is full By default, MyISAM tables use 4-byte pointers to indicate positions in the data file. So if your data file gets bigger than 4 GB (or larger for fixed-length records, but that's not what you have), you get that error: http://www.mysql.com/doc/en/Full_table.html Figure out how many records you're likely to need and do ALTER TABLE email_body MAX_ROWS=; -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Spatial Extension
Fraser MacLeod <[EMAIL PROTECTED]> wrote:
>
> I have set up mySQL 4.1 and everything seems to work fine. I have created a
> database, and populated it, queried it, etc. Now I would like to add spatial
> data (points, lines, polygons);
>
> Using a mysql client I am able to insert spatial values into a table using WKT
> representation. Similarly I can query these columns, but the result is always
> [NULL].
>
> For example:
>
> SELECT AsText(PointFromText('POINT(10,10)'))
Remove comma from the POINT definition.
mysql> SELECT AsText(PointFromText('POINT(10 10)'));
+---+
| AsText(PointFromText('POINT(10 10)')) |
+---+
| POINT(10 10) |
+---+
1 row in set (0.00 sec)
>
> will return [NULL]
>
> Any ideas what I need to do to fix this problem
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
I'm not sure it has to do with the number of rows, but in any case this is what happened: mysql> select count(*) from email_body; +--+ | count(*) | +--+ | 208853 | +--+ 1 row in set (0.00 sec) mysql> alter table email_body MAX_ROWS=70; Query OK, 315 rows affected (0.23 sec) Records: 315 Duplicates: 0 Warnings: 0 mysql> select count(*) from email_body; +--+ | count(*) | +--+ | 319 | +--+ 1 row in set (0.00 sec) Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting "Keith C. Ivey" <[EMAIL PROTECTED]>: > On 4 Sep 2003 at 10:13, Keith Bussey wrote: > > > -rw-rw 1 mysqlmysql4.0G Aug 31 01:01 email_body.MYD > > > > Thus if I try and insert one more row I get the error: > > > > ERROR 1114: The table 'email_body' is full > > By default, MyISAM tables use 4-byte pointers to indicate positions > in the data file. So if your data file gets bigger than 4 GB (or > larger for fixed-length records, but that's not what you have), you > get that error: > > http://www.mysql.com/doc/en/Full_table.html > > Figure out how many records you're likely to need and do > > ALTER TABLE email_body MAX_ROWS=; > > -- > Keith C. Ivey <[EMAIL PROTECTED]> > Tobacco Documents Online > http://tobaccodocuments.org > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem Connecting To Mysql Server
Hi there ##ERROR 2013: Lost connection to MySQL server during query ## I get this error when i try to remotely connect to my mysql server, it has been working until i changed the server's ip and isp, when i re-change the mysql server to the old ip address (first isp), it's working again . When i do a netstat -atn in my mysql server, i get : tcp 0 48 xxx.local.ip.xxx :3306 yyy.outsideserver.xxx:2886 FIN_WAIT1 A telnet to my server through the port 3306 goes well i'm under linux 8 and mysql 4.0.13 Does anybody knows how i can fix this ? Thanx a Lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copiando uma base de dados
Bom dia, Fiz um arquivo de instalacao para o meu sistema usando o install shield e estou com algumas duvidas qto a instalar a base de dados. Segue o que eu jah fiz: (1) Meu script de instalacao vai replicar o diretorio "C:\mysql\bin" na maquina do usuario (2) Copiarah tb "libmysql.dll" do diretorio "C:\mysql\lib\opt" para o diretorio system do cliente (3) Instalei meu sistema e as dlls dele - logico (4) se for WindowNT ou compativel roda o comando "C:\mysql\mysqld-nt --install" se nao adiciona um link na pasta "inicializar" do menu "iniciar" para "c:\mysqld" Agora o que falta: Estou usando tabelas innodb, como faco para copiar meus dados nos diretorios do mysql? Grato, Dirso.
Re: innodb and fulltext
Actually I think it can be done, but I need some help on the hooks on MySQL and the approval of Heikki... The matter is that I can be an interesting mental exercise and I think that it is missing to be a real 'de facto' world wide product. Any help? Leo. - Original Message - From: "electroteque" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 9:09 AM Subject: innodb and fulltext > Hi i was wondering if there was ever going to be a time when Innodb can also > be fulltext indexable ? Being that i just started to work with Innodb and > cant believe how proper it feels of a relationional database over Myisam. > Like with Myisiam you cant set relationships up or is that going to change ? > As there has been a few projects which needs fulltext but would be good > setting up innodb aswell. > > Also when setting up relationships if i setup on delete to use cascade it > will delete all records from the other table joined to that row, this is a > wicked feature although, how can i still stop it from being deleted > accidently then ? > > > -- > 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]
SQL syntax on an UPDATE
Hi All, I'm programming a method to delete a parent record and all its children in a child table in one go. This is what I have: UPDATE item i, category_item ci SET i.date_deleted = ?, ci.date_deleted = ? WHERE ci.item_id = i.item_id AND ci.category_id = ? and it works. But then I realised that I have never used this syntax to delete from two tables simultaneously before. I would like to know if it is meant to work, or if it is a dirty hack, and whether it is SQL standard, ie. can I use this if I want to run my app on Oracle? (Not that I do but I might want to sell it to people who do) thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auto Increment ID of Inserted Row
well, that'll teach me not to update my java api bookmarks to 1.4 the getGeneratedKeys() calls works like a charm! Thanks for the help > -Original Message- > From: Mark Matthews [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 02, 2003 4:09 PM > To: Dan Greene > Cc: [EMAIL PROTECTED] > Subject: Re: Auto Increment ID of Inserted Row > > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Dan Greene wrote: > > > (newbie to MySQL) > > > > I've been banging my head against the wall on this one for > a bit now, > and I understand that last_insert_id() is per-connection > based, but most > webapps are connection pooled (simple) or clustered (harder). > What are > my options to get the id of the inserted row in a webapp? As a side > note, I'm using JDBC to access the DB. > > > > my thoughts: > > 1- use an innoDB table, start a txn (lock the table), insert, select > max(id_column), end txn (unlock the table) > > > > 2- make an id pool table (innodb), have app server grab > pool of ids at > startup, and when pool is empty in similar manner (lock, > update, select, > unlock) > > > > 3- look to other product (don't make me do this one ;) ) > > > > 4- continue to bang head against the wall > > > > > > please cc me on any replies, as although I sent a subscription > request, I'm not on list yet... > > Is there a reason you don't hold on to the same connection during the > lifespan of one of your web 'transactions'? Also, to avoid a > round trip > to the server you should use Statement.getGeneratedKeys(). > > -Mark > > - -- > Mr. Mark Matthews > MySQL AB, Software Development Manager, J2EE and Windows Platforms > Office: +1 708 557 2388 > www.mysql.com > > Are you MySQL Certified? > http://www.mysql.com/certification/ > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.3 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQE/VPjstvXNTca6JD8RAs3RAKC6T+RQnStioRLAJ1cwHHsBMwuPxgCeJU+N > co0jO0c6pCDxIwxMAHaHkCk= > =Nkgp > -END PGP SIGNATURE- > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table is full error
Hey all, I have a table with the following size: -rw-rw 1 mysqlmysql4.0G Aug 31 01:01 email_body.MYD Thus if I try and insert one more row I get the error: ERROR 1114: The table 'email_body' is full Now I'm wondering why this is and if there is any options ot increase max table size ? I have other databases on other servers with much larger tables without problem. I'm running MySQL-standard 4.0.13 on FreeBSD 4.8-Stable Thanks! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14 stops responding to PHP 4.3.2
On Thursday, Sep 4, 2003, at 01:27 US/Eastern, Antony Dovgal wrote: Please, read http://www.php.net/manual/en/features.persistent-connections.php I recommend you not to use *_pconnect. There are some big problems with persistent connections and these problems are 'by design'. Use SQLrelay if you need real connection pooling. In your case MySQL probably says 'too many connections' and you can catch this error message if you'll turn on error_log in php.ini. That sounds like a plausible explanation - by restarting mysqld I'd be closing all the open connections, admittedly the hard way. I've changed the mysql_pconnect() calls to mysql_connect(), and I'm reading up in the manual. I don't understand all of it yet, which should probably tell me to stick to mysql_connect! Meanwhile, I'll see if I have any more failures using mysql_connect. Thanks, pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I select the column names?
Hi Jake, I think I know what you mean, but it depends on how you access your db. You want the metadata - in JDBC you would go something like this: metaData = resultSet.getMetaData(); colName1 = metaData.getColumnName(1); On 09/04/2003 02:50 PM Fortuno, Adam wrote: I don't follow your question. Please elaborate. Cheers, Adam -Original Message- From: Jake Johnson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 1:46 AM To: Mysql Subject: How can I select the column names? I am trying to select the column names from a table to be displayed in a web page. Is there anyway to select column names without desc? Thanks, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport problems
Thanks Paul, the options file worked fine! Neil Belch - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Neil Belch" <[EMAIL PROTECTED]>; "mySQL List" <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 2:48 AM Subject: Re: mysqlimport problems > At 0:34 +0100 9/4/03, Neil Belch wrote: > >Hi all, new to the list and new to mySQL... > > > >I've only been at it for a few days and am looking at importing text > >files to databases. > >I'm having trouble with mysqlimport and would appreciate any help or > >solutions as to what I'm doing wrong - I'm sure its a dumb mistake! > > > >I tried LOAD DATA INFILE and it worked fine. Here's the code I used for it: > > > >LOAD DATA LOCAL INFILE 'master_name.txt' > >INTO TABLE master_name > >FIELDS TERMINATED BY ':::' > >ENCLOSED BY '"' > >LINES TERMINATED BY '\r\n' > > > >This works fine. Every record is in the table and all the data is in there. > > > > > >When I try the same thing with mysqlimport, the records are there > >but there's no data in any of the fields! > >The code I used for it: > > > >mysqlimport -L -h localhost -u supercontact -ppassword > >--fields-terminated-by=::: --fields-enclosed-by=\" > >--lines-terminated-by=\r\n contactDB master_name.txt > > > >However, that whole command above won't fit on the DOS prompt in one > >go, so I have to enter most of the options in one command, then the > >remaining ones along with the db name and txt file in a second > >command - it seems to work as the records are still added but > >there's no data. > > Eh? That can't work; you must enter all the arguments in a *single* command. > > You may be able to shorten the command by omitting -h localhost, it > should default to the local host anyway. Or you could put options in > an option file such as C:\my.cnf: > > [mysqlimport] > local > host=localhost > user=supercontact > fields-terminated-by=::: > > etc. > > Of course, if you don't want to use those options the next time you > run mysqlimport, you'll need to remove them from the option file. > > > >(On another note, is there a way to fit the command onto one line in > >the DOS prompt?) > > > >Any help is greatly appreciated. > >Thanks, > > > > > >Neil Belch > > > -- > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld 4.0.14 crash when mysql_install_db
hi, i have built and installed mysql-4.0.14 to /usr/local/mysql and want to set up initial grant tables now but mysql_install_db fails and I am lost. What shall I do now? Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8432fd0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf5fea68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8091aea 0x825fe5a 0x8276277 0x80a24a6 0x80a56cb 0x80a22c0 0x809c849 0x8259fce 0x8299fc4 bin/mysql_install_db: line 1: 868 Killed /usr/local/mysql/libexec/mysqld --bootstrap --skip-grant-tables --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --skip-innodb --skip-bdb Installation of grant tables failed! best regards, Axel Siebenwirth -- Scotty: Captain, we din' can reference it! Kirk: Analysis, Mr. Spock? Spock: Captain, it doesn't appear in the symbol table. Kirk: Then it's of external origin? Spock: Affirmative. Kirk: Mr. Sulu, go to pass two. Sulu: Aye aye, sir, going to pass two. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Personal Servers
Many... but try Xitami and Apache. They're great. BTW, XP? Try SuSE, far better then Windoof. --- Nicola Hartland <[EMAIL PROTECTED]> wrote: > I am not sure this is the right place for this > question but I am trying to find a personal server > that I can install on my PC, I run XP that I can > have to test the web pages. Does anyone have any > suggestions and how to configure them. > > thanks in advance > > Nicci __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I select the column names?
> I am trying to select the column names from a table to be displayed in a > web page. Is there anyway to select column names without desc? If you are using PHP, you can look at the following: http://us4.php.net/manual/en/function.mysql-field-name.php Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: mysqld-nt claims all available CPU
MySQLd-nt (v4.0.14) is eating up all the available CPU resources on a Windows 2000 server (dual 933 Pentium, 1GB RAM), with a maximum of 40 users so far. I have tried each combination of the default configuration files, and each variety of the executable (nt, max-nt etc.), and it's always the same. The webpage to access the database is written is ASP, and all connections are closed when finished with before the page is finished. Has anyone any ideas why it shoots up to the top like this? Many thanks Gary
Personal Servers
I am not sure this is the right place for this question but I am trying to find a personal server that I can install on my PC, I run XP that I can have to test the web pages. Does anyone have any suggestions and how to configure them. thanks in advance Nicci
Re: loading in grant tables
"Gronquist, Jim M" <[EMAIL PROTECTED]> wrote: > > I can log into mysql using > > safe_mysqld --skip-grant-tables & > > mysql -u root -mysql > > The directions say that from mysql I should be able to manually execute > the SQL commands in mysql_install_db > > and then run mysqladmin flush-privileges afterword. > > > > It is not apparent to me how to manually execute the SQL commands in > mysql_install_db mysql_install_db isn't SQL command. mysql_install_db is a script that creates MySQL privilege tables. You should run script from the shell: http://www.mysql.com/doc/en/mysql_install_db.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld-nt claims all available CPU
MySQLd-nt (v4.0.14) is eating up all the available CPU resources on a Windows 2000 server (dual 933 Pentium, 1GB RAM), with a maximum of 40 users so far. I have tried each combination of the default configuration files, and each variety of the executable (nt, max-nt etc.), and it's always the same. The webpage to access the database is written is ASP, and all connections are closed when finished with before the page is finished. Has anyone any ideas why it shoots up to the top like this? Many thanks Gary
RE: How can I select the column names?
I don't follow your question. Please elaborate. Cheers, Adam -Original Message- From: Jake Johnson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 1:46 AM To: Mysql Subject: How can I select the column names? I am trying to select the column names from a table to be displayed in a web page. Is there anyway to select column names without desc? Thanks, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- 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]
innodb and fulltext
Hi i was wondering if there was ever going to be a time when Innodb can also be fulltext indexable ? Being that i just started to work with Innodb and cant believe how proper it feels of a relationional database over Myisam. Like with Myisiam you cant set relationships up or is that going to change ? As there has been a few projects which needs fulltext but would be good setting up innodb aswell. Also when setting up relationships if i setup on delete to use cascade it will delete all records from the other table joined to that row, this is a wicked feature although, how can i still stop it from being deleted accidently then ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting only numbers from a string or vice versa
Aki Karjalainen wrote:
Hi.
I've got a problem where I need to drop everything but numbers from a string and evaluate against it.
E.g. SELECT * FROM MYDATA WHERE DROP_ALL_LETTERS(MYSTRING) = '1254'
or
SELECT * FROM MYDATA WHERE REMOVESUBSTRINGS('A-Z', MYSTRING) = '1254'
10 points to the one whos knows how to do a query like that.
select * from mydata
where mystring regexp '[A-Z]*1[A-Z]*2[A-Z]*5[A-Z]*4[A-Z]*';
should do it.
Regards
Joseph Bueno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL does not release locks
Hi, Unfortunately, nobody responded my mail. I am still trying to solve the problem I described in my earlier mail (quoted below). I am mailing again because I have some new information about the issue. I converted all my tables to InnoDB (MyISAM -> InnoDB) and this solved the problem. With InnoDB tables I have not encountered any table locks BUT database engine is continuously reporting a new kind of warnings. A section from the server ".err" log file: <> 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. <> MySQL database engine is still behaving badly but InnoDB engine is able to overcome or prevent this. Has anyone any ideas?!?! Maybe Heikki Tuuri could explain behavior of the InnoDB engine. Sincerely, Teemu Teemu wrote: Hi I have problems with mysql (4.0.6-4.0.14b) on windows 2000 SP3. MySQL locks tables when I alter table structure or execute update queries. I know that this is the exactly what database engine is supposed to do but the engine doesn't release the locks at all. This happens frequently but not allways. Approximately every third alter/update query locks table "permanently". Recently I found out that by executing "FLUSH TABLES" release locks and I am able to keep on working with the table. I have been useing only MyISAM table types. Table locking is annoying because there might be concurrent users useing the same table. They are not able to access the table at all or SELECT queries returns wrong number of records. I use different clients to execute queries phpMyAdmin, mysqlcc, mysql, MSAccess (ODBC). Table locking occurs independently of client used. For example phpMyAdmin sometimes shows following error message when I try to alter structure of locked table: ERROR 7: Error on rename of '.\front\industry.MYI to '.\front\#sql-a64-439.MYI' (ERROR: 13) I checked error code 13: Permission denied. But there shouldn't be permission problems because I use account having all privileges and only some of my queries cause these king of errors. I am not alone with this kind of problem because there is a lot of reports in various mailing lists. I searched from web and news groups but nobody knows how to overcome the issue. It might be that the problem is win32 specific. I would be more than thankfull if someone knows reason for table locking. Sincerely, Teemu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting only numbers from a string or vice versa
Hi.
I've got a problem where I need to drop everything but numbers from a string and
evaluate against it.
E.g. SELECT * FROM MYDATA WHERE DROP_ALL_LETTERS(MYSTRING) = '1254'
or
SELECT * FROM MYDATA WHERE REMOVESUBSTRINGS('A-Z', MYSTRING) = '1254'
10 points to the one whos knows how to do a query like that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I Need Server / Client Help
Hello, I am making a avatar chat you can see some of my work and I hope you could help me in a small area that im needing help in which is making your client and server to be able to split avatars such as like on www.habbohotel.com you have one user with his / her own avatar and you have another user with his / her own avatar I am creating my own server for this which is called ShockServer I know this isnt the place maybe not to ask this but I have got alot of help using this group other than the fuse group all I get is like porn sites which are pretty good but im wanting help not pleasure at the moment that is lol... If any one has any idea on how to do this please email me or do somthing.. lol because im needing to know this so I can get started with more programming... www.ExternalVibe3D.com/walkdemo4.htm - New Version no errors in the engine yay www.ExternalVibe3D.com/walkdemo5.htm - Upcoming version No engine but you can sit | well did before we deleted the old engine because we are rewriting it so it will have AI so actually it will walk around the furni... :) I know the habbo style is used this is a demo not a clone I have my own but not done with the walking sprites :) thanks anyway Fusion City External Vibe 3D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Tables
You said -- I am trying to establish temporary tables that are accessible via MyODBC. I can create the table with data in it and query it from the MySQL command line, but it never shows up through MyODBC. Is there something magical about temporary (memory-based) tables that prevents this? I think you are misundersanding temorary tables. Temporary tables are not necessarily in ram: unless you specify otherwise, they will be created on disk. However, temporary tables are (a) only accessible via the connection which created them and (b) deleted automatically when that connection is closed. This means that you do not have to think og unique table names for short-life scratch tables. What I think you want is a Heap table, created by specifying TYPE=HEAP in your create command - see http://www.mysql.com/doc/en/HEAP.html . Heap tables have certain restrictions, and disappear when mysql is stopped, but are visible to all users. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem Connecting To mysql server
Hi there ##ERROR 2013: Lost connection to MySQL server during query ## i get this error when i try to remotely connect to my mysql server, it has been working until i changed the server's ip and isp, when i re-change the mysql server to the old ip address (first isp), it's working again . when i do a netstat -atn in my mysql server, i get : tcp0 48 xxx.local.ip.xxx :3306 yyy.outsideserver.xxx:2886 FIN_WAIT1 a telnet to my server through the port 3306 goes well i'm under linux 8 and mysql 4.0.13 Does anybody knows how i can fix this ? ThanX LoT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The value of def_value must be a constant; it cannot be an expression or refer to other columns.
Creating Tables The general attributes can be given for any column type, with a few exceptions. You can specify NULL or NOT NULL to indicate whether a column can hold NULL values. For all but BLOB and TEXT types, you can specify DEFAULT def_value to indicate that a column should be assigned the value def_value when a new row is created that does not explicitly specify the column's value. The value of def_value must be a constant; it cannot be an expression or refer to other columns. === Hello programmers, This is from Mr. Paul DuBois MySQL textbook, Page 101. I'd like to know if this is a definition from the SQL standard, Can this be possible by correct implementation of 'actions', 'triggers' and constrains ? Yours sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change default data directory
thanks for the valuable tips sanjay gupta - Original Message - From: "Thierno Cissé" <[EMAIL PROTECTED]> To: "'sanjay gupta'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 3:50 PM Subject: RE: how to change default data directory > Hi Sanjay, > I see two ways to proceed : > 1 - after install, move or copy (with -R option) your actual datadir > under /var/lib/mysql > (that is to say all the directories located under /var/lib/mysql/*) > to /var1/lib/mysql, > and then modify in your my.cnf the variable > to correspond with the new directory. > NOTE : stop the mysqld daemon before copying or moving directories. > > 2 - before install, configure in your my.cnf the futur directory to use > with the variable ; > then place it under /etc/ - this cause the script > to find in /etc/my.cnf the datadir to use. > After install, you'll see that the directories mysql and test are > placed under > > Hope this help > Thierno6C > > -Original Message- > From: sanjay gupta [mailto:[EMAIL PROTECTED] > Sent: jeudi 4 septembre 2003 09:35 > To: [EMAIL PROTECTED] > Subject: how to change default data directory > > > Hi All , > I am using RH7.1 box . I am preparing to install mysql > 4.0.13. Previously when i installed the mysql the mysql data directory > was /var/lib/mysql . I uninstall the mysql . Now i want my data > directory to be /var1/lib/mysql instead of /var/lib/mysql . I want this > because var1 is mounted to my raid device /dev/md0 and i want mysql > data directory should be /var1 > > any suggestions > > sanjay > > > > -- > 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: how to change default data directory
thanks for valuable tips - Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 04, 2003 3:30 PM Subject: Re: how to change default data directory > "sanjay gupta" <[EMAIL PROTECTED]> wrote: > >I am using RH7.1 box . I am preparing to install mysql 4.0.13. > > Previously when i installed the mysql the mysql data directory was > > /var/lib/mysql . I uninstall the mysql . Now i want my data directory to be > > /var1/lib/mysql instead of /var/lib/mysql . I want this because var1 is > > mounted to my raid device /dev/md0 and i want mysql data directory should > > be /var1 > > > > Move databases to the new MySQL data dir. Run mysqld with --datadir=/var1/lib/mysql option or put to the my.cnf > > [mysqld] > datadir=/var1/lib/mysql > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to change default data directory
Hi Sanjay, I see two ways to proceed : 1 - after install, move or copy (with -R option) your actual datadir under /var/lib/mysql (that is to say all the directories located under /var/lib/mysql/*) to /var1/lib/mysql, and then modify in your my.cnf the variable to correspond with the new directory. NOTE : stop the mysqld daemon before copying or moving directories. 2 - before install, configure in your my.cnf the futur directory to use with the variable ; then place it under /etc/ - this cause the script to find in /etc/my.cnf the datadir to use. After install, you'll see that the directories mysql and test are placed under Hope this help Thierno6C -Original Message- From: sanjay gupta [mailto:[EMAIL PROTECTED] Sent: jeudi 4 septembre 2003 09:35 To: [EMAIL PROTECTED] Subject: how to change default data directory Hi All , I am using RH7.1 box . I am preparing to install mysql 4.0.13. Previously when i installed the mysql the mysql data directory was /var/lib/mysql . I uninstall the mysql . Now i want my data directory to be /var1/lib/mysql instead of /var/lib/mysql . I want this because var1 is mounted to my raid device /dev/md0 and i want mysql data directory should be /var1 any suggestions sanjay -- 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]
Temporary Tables
I am trying to establish temporary tables that are accessible via MyODBC. I can create the table with data in it and query it from the MySQL command line, but it never shows up through MyODBC. Is there something magical about temporary (memory-based) tables that prevents this? I am running MySQL 4 on RedHat 9 with the latest MyODBC. The create syntax works as advertised: create temporary table X select * from Y where Z = 'abc'; Incidentally, MyODBC works fine with disk-based tables. I just set up an ODBC DSN and can access all the tables via Excel, MS Word, MS Access, etc. except the temporary tables. TIA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Tables
I am trying to establish temporary tables that are accessible via MyODBC. I can create the table with data in it and query it from the MySQL command line, but it never shows up through MyODBC. Is there something magical about temporary (memory-based) tables that prevents this? I am running MySQL 4 on RedHat 9 with the latest MyODBC. The create syntax works as advertised: create temporary table X select * from Y where Z = 'abc'; Incidentally, MyODBC works fine with disk-based tables. I just set up an ODBC DSN and can access all the tables via Excel, MS Word, MS Access, etc. except the temporary tables. TIA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
