Re: MySQL database move
Hi Ace, If you cant affort downtime and if you are using innodb try removing auto extend on the current data file and create a datafile in a different partition and put autoextend on the same. If you are using MyISAM , you can move few tables to different disk use symlinks. -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu On 7/9/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Ace, Can't you zip or move old bin-logs to a different disk and release some free space on the current drive. regards anandkl On 7/9/07, Ace [EMAIL PROTECTED] wrote: Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan
Re: off-topic unsubscribe concern
To unsubscribe:http://lists.mysql.com/mysql?unsub= If you don't want people to follow this link, why are you including it? To stop the 'suffering', stop including junk that doesn't belong in outgoing email. Jochem Stern, stern, but fair ;) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive queries
Hi, Steffan A. Cline wrote: I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply The table structure is like so: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | forum_id | bigint(11) | NO | PRI | NULL | auto_increment | | project_id| bigint(11) | YES | MUL | 0 | | | forum_reply_id| bigint(11) | YES | MUL | 0 | | | forum_dev_id | bigint(11) | YES | MUL | 0 | | | forum_type| varchar(255) | YES | | | | | forum_subject | varchar(255) | YES | | | | | forum_message | longtext | YES | | NULL | | | forum_date_posted | timestamp| NO | | CURRENT_TIMESTAMP | | +---+--+--+-+---+--- -+ Test data is like so mysql select * from forums; +--+++--++-- -+-+ -+ | forum_id | project_id | forum_reply_id | forum_dev_id | forum_type | forum_subject | forum_message | forum_date_posted | +--+++--++-- -+-+ -+ |1 | 42 | 0 |1 || First Post| I am the First! | 2007-07-08 15:09:41 | |2 | 42 | 1 |1 || | I am a reply to the first | 2007-07-08 15:30:36 | |3 | 42 | 0 |1 || sample data | this is some sample data in a new thread| 2007-07-08 15:10:03 | |4 | 42 | 2 |1 || | this is a reply to the reply of the first post. | 2007-07-08 15:33:54 | +--+++--++-- -+-+ -+ 4 rows in set (0.00 sec) I am figuring that if the reply_to_id is 0 then it is a parent thread otherwise it is a child or child of a child etc. There are many ways to model hierarchies and trees in SQL. The one I think lends itself best to this query is nested sets. Joe Celko's book is the classic on it, though there are good descriptions of it in many places (Pro MySQL, and even for free online, for example the sample chapters in http://www.artfulsoftware.com/). Celko and others also give examples of other methods and the kinds of queries needed for them. I'd have to check, but offhand I don't think there's any way to write the query you want with the data structure you have, unless you use recursive Common Table Expressions, which MySQL doesn't support. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and 'ODBC'@'localhost' error
You didn't specify a user or password on your mysql command line when you redirected your input to your .sql file, and for some reason the default is [EMAIL PROTECTED] You need to use the -p and -u options on the command line. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive queries
On 7/8/07, Steffan A. Cline [EMAIL PROTECTED] wrote: I am trying to set up a forum where there are main posts with replies and replies to replies. Rather than using nested queries in my middleware I am thinking there has to be some kind of recursive query where I can output the results in a format like so: MAIN Reply to main reply to reply to main reply to main reply to 2nd reply to main MAIN Reply reply As another poster said, there are various ways to represent trees in databases. You may want to consult some of these references. In this particular case, nothing comes to mind that will give you both: a)The ability to represent an arbitrarily-deep hierarchy of responses, AND b)Will let you get the entire result set ordered the way you want in ONE query. I don't immediately see how to get both at the same time. If, for example, you were willing to sacrifice (a), then just set up integers (maybe 3 of them) allowing you to represent a nesting 3 deep then order by these integers on the query. But 3 is not arbitrarily-deep. If you were willing to sacrifice (b), then you could just represent the tree by a table of links that relate parent and child. Finding all the children for a parent is just select * from links where parent=29331 or something like this. Problem is, you would need to issue queries to traverse the tree. I can't immediately see a way to get both (a) and (b) simultaneously. But you can definitely get EITHER (a) or (b). Dave
Re: MySQL and 'ODBC'@'localhost' error
Hi All, Thanks a lot for your help. The thing is working now. Although I would like to mention a couple of points: 1. mysql -u root -p test_dev db/create.sql -- simply works !! 2. Another way as people suggested to make it work was to give permissions to user ODBC. I was not being able to do this earlier(as i had mentioned in my mail that i could give permissions to new user) but I figured out later(thanks to Martin's link !!) that the problem was due to the enable strict mode option which I had checked in during installation. So, for a shortcut, I simply uninstalled and re-installed MySQL, this time with this option, and I could now add new users !! Also now, I did not need use [1] to access the db/create.sql file, my earlier command works fine :) Thanks again for the support !! Kapil On 7/9/07, Jerry Schwartz [EMAIL PROTECTED] wrote: You didn't specify a user or password on your mysql command line when you redirected your input to your .sql file, and for some reason the default is [EMAIL PROTECTED] You need to use the -p and -u options on the command line. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- I can sum up everything i learned about life in three words -- IT GOES ON !!
error in login
Hello all Ive scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed % and localhost as posible hosts to be able to log in On remote machines I do a mysql -u web19_u1 -p -h10.10.10.2 p web19_db1 and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES). If I change this to hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. Ive tried removing the localhost from the user and only leaving % with no difference at all. Ive also tried the protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27
Re: error in login
Héctor S. Mendoza O. wrote: Hello all… I’ve scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed ‘%’ and ‘localhost’ as posible hosts to be able to log in On remote machines I do a “mysql -u web19_u1 -p -h10.10.10.2 –p web19_db1” and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a “ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES)”. If I change this to –hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. I’ve tried removing the ‘localhost’ from the user and only leaving ‘%’ with no difference at all. I’ve also tried the –protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27 Use a my.cnf file in the user's home directory. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why can I not edit my database using mySQL administrator ?
Dear everyone: I'm trying to simplify the operating of mySQL by using a GUI tool, mySQL administrator. But it seems that I was only allowed to view the databases which I have made up, but not allowed to edit them. Is it because I have to buy a lisence or something which I didn't buy? Thanks for any answer! Best Regards, yours sincerely, Weiqi == Weiqi Wang Hertford College Dep. of Engineering Science Oxford
RE: why can I not edit my database using mySQL administrator ?
Are you sure the MySQL user you have defined in your GUI tool has the proper table permissions? Just a thought -Chris From: Weiqi Wang [mailto:[EMAIL PROTECTED] Sent: Mon 7/9/2007 4:46 PM To: mysql@lists.mysql.com Subject: why can I not edit my database using mySQL administrator ? Dear everyone: I'm trying to simplify the operating of mySQL by using a GUI tool, mySQL administrator. But it seems that I was only allowed to view the databases which I have made up, but not allowed to edit them. Is it because I have to buy a lisence or something which I didn't buy? Thanks for any answer! Best Regards, yours sincerely, Weiqi == Weiqi Wang Hertford College Dep. of Engineering Science Oxford
RE: error in login
The problem persists, with /usr/bin/mysql or with a php application, same sympthoms TIA Hector -Mensaje original- De: Gerald L. Clark [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 09 de Julio de 2007 02:54 p.m. Para: Héctor S. Mendoza O. CC: mysql@lists.mysql.com Asunto: Re: error in login Héctor S. Mendoza O. wrote: Hello all Ive scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed % and localhost as posible hosts to be able to log in On remote machines I do a mysql -u web19_u1 -p -h10.10.10.2 p web19_db1 and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES). If I change this to hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. Ive tried removing the localhost from the user and only leaving % with no difference at all. Ive also tried the protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27 Use a my.cnf file in the user's home directory. -- Gerald L. Clark Supplier Systems Corporation -- 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: error in login
Hola Hector, el problema son las 2 tuplas que mencionas, ya que debido a su jerarquia pueden hacer un conflicto que te evita conectarte correctamente, en tu caso si deseas que el usuario se conecte desde donde sea (%) elimina la tupla de la tabla user donde aparezca el localhost, esto puede ser en la table user o en la tabla db de la base de datos mysql. Hector, the problem is maybe that you have 2 rows granting permissions to this user and is possible that due to hierarchies or precedence one overrides each other. I suggest to eliminate the localhost entry and leave the % because anyways you are allowing this user to connect from anywhere including the localhost. This problem can be on the user or db tables of the mysql database. Carlos Héctor S. Mendoza O. wrote: The problem persists, with /usr/bin/mysql or with a php application, same sympthoms TIA Hector -Mensaje original- De: Gerald L. Clark [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 09 de Julio de 2007 02:54 p.m. Para: Héctor S. Mendoza O. CC: mysql@lists.mysql.com Asunto: Re: error in login Héctor S. Mendoza O. wrote: Hello all… I’ve scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed ‘%’ and ‘localhost’ as posible hosts to be able to log in On remote machines I do a “mysql -u web19_u1 -p -h10.10.10.2 –p web19_db1” and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a “ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES)”. If I change this to –hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. I’ve tried removing the ‘localhost’ from the user and only leaving ‘%’ with no difference at all. I’ve also tried the –protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27 Use a my.cnf file in the user's home directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error in login
Gracias Carlos, I've tried that already with no results, I've left only the [EMAIL PROTECTED] and have no luck logging in from the same machine the mysqld is running, I can log in perfectly from anywhere. TIA Hector S. Mendoza O -Mensaje original- De: Carlos Proal [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 09 de Julio de 2007 04:50 p.m. CC: mysql@lists.mysql.com Asunto: Re: error in login Hola Hector, el problema son las 2 tuplas que mencionas, ya que debido a su jerarquia pueden hacer un conflicto que te evita conectarte correctamente, en tu caso si deseas que el usuario se conecte desde donde sea (%) elimina la tupla de la tabla user donde aparezca el localhost, esto puede ser en la table user o en la tabla db de la base de datos mysql. Hector, the problem is maybe that you have 2 rows granting permissions to this user and is possible that due to hierarchies or precedence one overrides each other. I suggest to eliminate the localhost entry and leave the % because anyways you are allowing this user to connect from anywhere including the localhost. This problem can be on the user or db tables of the mysql database. Carlos Héctor S. Mendoza O. wrote: The problem persists, with /usr/bin/mysql or with a php application, same sympthoms TIA Hector -Mensaje original- De: Gerald L. Clark [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 09 de Julio de 2007 02:54 p.m. Para: Héctor S. Mendoza O. CC: mysql@lists.mysql.com Asunto: Re: error in login Héctor S. Mendoza O. wrote: Hello all Ive scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed % and localhost as posible hosts to be able to log in On remote machines I do a mysql -u web19_u1 -p -h10.10.10.2 p web19_db1 and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES). If I change this to hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. Ive tried removing the localhost from the user and only leaving % with no difference at all. Ive also tried the protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27 Use a my.cnf file in the user's home directory. -- 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: error in login
On Mon, Jul 09, 2007 at 04:33:04PM -0500, Héctor S. Mendoza O. wrote: The problem persists, with /usr/bin/mysql or with a php application, same sympthoms On the local host (where the mysqld resides) I do the same command and get a ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES). If I change this to hlocalhost I can login correctly. Change the host entry to db1.intranet in your user table for the user web19_u1. I had the same problem on my mysql machine. I changed the host entry to the full qualified hostname and that was fine. Don't forget to flush the privileges ;-) Greetings Mario -- - | havelsoft.com - Ihr Service Partner für Open Source | | Tel: 033876-21 966 | | Notruf: 0173-277 33 60 | | http://www.havelsoft.com| | | | Inhaber: Mario Günterberg | | Mützlitzer Strasse 19 | | 14715 Märkisch Luch | - pgpZuexb9lTSS.pgp Description: PGP signature
回复： why can I not edit my databas e using mySQL administrator ?
MySQL administrator mainly focus on MySQL server status monitor/use management/security/backup/restore etc. If you want to edite database, you should use MySQL query browser, it's included in MySQL GUI tools package. Weiqi Wang [EMAIL PROTECTED] 写道： Dear everyone: I'm trying to simplify the operating of mySQL by using a GUI tool, mySQL administrator. But it seems that I was only allowed to view the databases which I have made up, but not allowed to edit them. Is it because I have to buy a lisence or something which I didn't buy? Thanks for any answer! Best Regards, yours sincerely, Weiqi == Weiqi Wang Hertford College Dep. of Engineering Science Oxford @@@^_^@@@ - 抢注雅虎免费邮箱3.5G容量，20M附件！
Variables not been updated when I fetch a cursor
Hello there, I have a problem, I wrote a stored procedure to retrieve some data from various tables and then put it in a temporary table to use it later, most of the procedure works just fine but there's something that doesn't and I can not guess what the error can be. Okay so, here's the thing: I declared a cursor that joins some tables: DECLARE cur_libros CURSOR FOR SELECT ISBN, CONCAT(Nombre, , Apellido), Nombre_Editorial, YEAR(Fecha_publicacion), Precio, Titulo FROM Autor JOIN Libro_Autor USING(Id_autor) JOIN Libro USING(ISBN) JOIN Libro_Editorial USING(ISBN) JOIN Editorial USING(Id_Editorial) WHERE Nombre = nom AND Apellido = ap; [ When I execute the SELECT sentence for this cursor it works just fine, the records are retrieved as I want them, so I know it isn't a problem with the table or the JOIN. ] Later in the procedure I fetch the cursor data into some local variables I have declared: FETCH cur_libros INTO myisbn, nombre_autor, editorial, ano_publicacion, precio, titulo; most of the variables are filled with data after the fetch but precio and titulo are not. I searched the Internet trying to find a solution and ended up in a bug report for mySQL: Bug #8692 Cursor fetches null values into variables Suggested fix: Variables should contain valid data. Fetch should fetch valid data into the variables. So I added some SET sentences to initialize the variables before fetching data into them: SET titulo = ; SET precio = 0; Still nothing... in fact the variables are never updated is not that the fetch set them to null is more like the fetch just don't touch them, they are never modified, no matter what I initialize them to the variables never change. I tried to check the data types to make sure they are the same that the ones in the table and the data types are Ok they match, so... What is the problem here? What are the more common causes for a variable not been updated in a FETCH? If I would have a little idea about that I could figure out what the problem is, so I would really appreciate your help. Thanks in advance. -- Sergio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database persistent read error
Hi Am trying to access a web page in browser and get results from an old mysql database. The database has since been updated, but still see the original items. On 3 computers, the same problem, on 2, have deleted apache and mysql, no change - uninstalled IE7 - reinstalled and eventually the problem cleared. On no.3 - did all the same things, uninstalled - reinstall - deleted IE7 files - nada... installed firefox - same problem. Can't figure where it is getting the data from. Eric
