how to move the data directory?
I would like to know the correct way to move the mysql data directory to a different disk partition, so that the data can be accessed by mysql under linux or windows. I'm using Ubuntu 7.1 and Windows XP. I copied the data directory to a separate partition. I then changed the datadir in my.cnf, but Mysql would not start, warning: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' I'd appreciate your suggestions. -B. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select on multiple fields in several tables?
Hello- I'm working with an unfamiliar application, trying to figure out where my data is going. Is it possible to form a query to select from all the columns in several tables at once? Something equivalent to: SELECT FROM * WHERE * = 'john smith'; If that's not possible I'd still like to be able to search each table at once without specifying the column names: SELECT FROM table name WHERE * = 'john smith'; If that can't be done, can a sub query find the column names to be selected on? Basically, I'm trying to avoid doing separate selects on each column in each table. This is using MySQL 5.0.27 . Thanks for any suggestion or alternative methods! -John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on multiple fields in several tables?
Ryan Stille wrote: boll wrote: Hello- I'm working with an unfamiliar application, trying to figure out where my data is going. Is it possible to form a query to select from all the columns in several tables at once? Something equivalent to: SELECT FROM * WHERE * = 'john smith'; If that's not possible I'd still like to be able to search each table at once without specifying the column names: SELECT FROM table name WHERE * = 'john smith'; If that can't be done, can a sub query find the column names to be selected on? Basically, I'm trying to avoid doing separate selects on each column in each table. This is using MySQL 5.0.27 . Thanks for any suggestion or alternative methods! -John SELECT * FROM table1, table2, table3 WHERE table1.name = 'john smith' AND table2.city = 'Dallas' If the tables have fields named the same, this may not work very well. Then you'd need to list out the column names and alias some of them so they come out with different names. Yeah, that would be too complex to manage if the table structure changed. I don't think there is a way to wildcard the column names like you are asking, I can't imagine a case where you'd need to do that either. When would every column in your table have the same value? (john smith in your example). At this point I'm just trying to figure out: Where in the database did the input data get stored? This, then, is how I need to approach it: You can programmatically get a list of columns in a table, then when you are using Perl or PHP or whatever to build your query string, you can loop through the columns to list them. -Ryan Thanks, Ryan. -John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from two tables when they correspond, otherwise one table
This query will display from the 'products' and 'qty_price' tables when the 'products.sku' and 'qty_price.qsku' fields match: SELECT products.sku, products.title, products.price, qty_price.qty, qty_price.qprice FROM products, qty_price WHERE products.sku = qty_price.qsku AND products.vendor_id=15 AND prod_test.category='widgets'; I would like to change it so that it will display the fields from 'products' even if there is no corresponding row in the 'qty_price' table. Thanks in advance for your suggestions. J. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select from two tables when they correspond, otherwise one table
Thanks, Jocelyn. That's what I was looking for. Jocelyn Fournier wrote: Hi, SELECT products.sku, products.title, products.price, qty_price.qty, qty_price.qprice FROM products LEFT JOIN qty_price ON (products.sku = qty_price.qsku) WHERE products.vendor_id=15 AND products.category='widgets'; should do what you want. Regards, Jocelyn Fournier www.mesdiscussions.net boll a écrit : This query will display from the 'products' and 'qty_price' tables when the 'products.sku' and 'qty_price.qsku' fields match: SELECT products.sku, products.title, products.price, qty_price.qty, qty_price.qprice FROM products, qty_price WHERE products.sku = qty_price.qsku AND products.vendor_id=15 AND prod_test.category='widgets'; I would like to change it so that it will display the fields from 'products' even if there is no corresponding row in the 'qty_price' table. Thanks in advance for your suggestions. J. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Errcode: 13) after moving data directory
boll wrote: Dominik Klein wrote: Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Hi Dominik, In /etc/fstab the partition is mounted with this line: /dev/hdb2/mnt/FATvfatuid=27,gid=27,umask=000 0 0 ...which I think is the same as what you recommend (uid 27 is mysql) . What I really don't understand is: Why mysqld will start up and use the dataq on the FAT partition (as I want it to do) if I start it with mysqld_safe, but when I boot the computer or try, as root, service mysqld start, it fails to start with these log errors: 060421 08:43:10 mysqld started 060421 8:43:11 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060421 8:43:11 [ERROR] Aborting I'm guessing that since mysqld_safe runs as user mysql, maybe mysqld runs as a different user? How would I find that out? I will keep reading the manual, but will be grateful for any ideas. I was able to get mysqld to start at boot, by disabling selinux. Not happy to do it that way, but couldn't find any alternative. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Errcode: 13) after moving data directory
Dominik Klein wrote: Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Hi Dominik, In /etc/fstab the partition is mounted with this line: /dev/hdb2/mnt/FATvfatuid=27,gid=27,umask=000 0 0 ...which I think is the same as what you recommend (uid 27 is mysql) . What I really don't understand is: Why mysqld will start up and use the dataq on the FAT partition (as I want it to do) if I start it with mysqld_safe, but when I boot the computer or try, as root, service mysqld start, it fails to start with these log errors: 060421 08:43:10 mysqld started 060421 8:43:11 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060421 8:43:11 [ERROR] Aborting I'm guessing that since mysqld_safe runs as user mysql, maybe mysqld runs as a different user? How would I find that out? I will keep reading the manual, but will be grateful for any ideas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Errcode: 13) after moving data directory
Hi- Using MySQL 4.1.11 on Fedora 4. I moved my data directory to a FAT partition in order to share it with Windows dual-boot. Now when I try to start mysqld normally, it fails with these messages in the log: 060420 18:16:03 mysqld started 060420 18:16:03 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060420 18:16:03 [ERROR] Aborting However, I can start mysqld using: mysqld_safe, so I know it's possible. Any suggestions? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
