Re: beginner stuff; cant create database
Wiley, E:\mysqladmin create TestDB mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'testdb'. (err no: 13)' Error 13 means permission denied. Check if the MySQL has write access to E:\. BTW, I don't think E:\ is a very good place to store your databases. I would recommend at least something like E:\data\ for this. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: wiley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 03, 2003 5:44 AM Subject: beginner stuff; cant create database Easy question for beginner. Why am I getting this error ? I just installed Mysql and I'm trying to create a database. Beginner stuff... E:\mysqladmin create TestDB mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'testdb'. (err no: 13)' thanks in advance for anything... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: datadir and socket
Alex, I'm happy to hear you were able to resolve the socket problem! But still I have question regarding my.cnf: I searched the whole file system and found only one my.cnf in /etc/, and no .my.cnf. All I can find about the datadir and socket configurations are just staying in /etc/my.cnf and /etc/init.d/mysql. So, I really don't understand that, although I have make all the necessary changes, why for some certain situation mysql still takes the old configuration info and where it takes them from. I know that some Linux dists do things that I (as a user mostly under Win) find rather strange. I'm not informed which dist you are using, and even if I knew - well, as said, I leave this question to the Linux gurus on this list. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Alex Shi [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; mysql group [EMAIL PROTECTED] Sent: Monday, February 03, 2003 10:39 PM Subject: Re: datadir and socket Hi Stefan, Thanks a lot! With your help my issue resolved. Now both the command line connection and PHP connection can work fine. But still I have question regarding my.cnf: I searched the whole file system and found only one my.cnf in /etc/, and no .my.cnf. All I can find about the datadir and socket configurations are just staying in /etc/my.cnf and /etc/init.d/mysql. So, I really don't understand that, although I have make all the necessary changes, why for some certain situation mysql still takes the old configuration info and where it takes them from. Again, thanks for your help and patient. I know my issue already resolved. I asked the question because I want to learn something more in detail. Best Regards, Alex Alex, If I put a copy of my.cnf to datadir would that be any help? Any what is home_directory? Sorry I am kind of newbie for in-depth knowledge of MySQL. Nope. You don't have three copies of my.cnf (one of them actually called .my.cnf, note the dot) to make things more complicated, but to give you more flexibility. Ask the gurus on this list (Paul?! ;-) for a good example why one should have /etc/my.cnf (global options) AND datadir/my.cnf (server options). But for .my.cnf, you can use this to store username/password for the MySQL client, or even to store a different socket through which to connect to the server. And why the /etc/init.d/mysqld (this is a shell script) still has some hard-coded configuration information even we already have 3 copies of my.cnf? I had assume it should be able to retrieve conf info from conf files, and this was what the role of a conf file supposed to be I leave this one to the Linux folks. [client] OK, I added this entry into my.cnf, and it does help for connection issued from command line, but it doesn't work for PHP connection function (issued drom web). Right. This only works for clients that read the option file(s) when starting. PHP won't, but with PHP that's no problem, as the manual explains for mysql_connect(): The server parameter can also include a port number. eg. hostname:port or a path to a socket eg. :/path/to/ socket for the localhost. Note: Support for :port was added in PHP 3.0B4. Support for :/path/to/socket was added in PHP 3.0.10. Details: http://www.php.net/manual/en/function.mysql-connect.php Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Alex Shi [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; mysql group [EMAIL PROTECTED] Sent: Monday, February 03, 2003 3:27 AM Subject: Re: datadir and socket There are three my.cnf you should look for: 1) /etc/my.cnf = global options 2) datadir/my.cnf = server options, overriding global options 3) home_directory/.my.cnf = user specific options, overriding server options Thanks for your reply! The information does make sense. However, I searched the whole file system but found only /etc/my.cnf. If I put a copy of my.cnf to datadir would that be any help? Any what is home_directory? Sorry I am kind of newbie for in-depth knowledge of MySQL. I am curiouse that why mysql comes with 3 copies of same (?) configuration file. And why the /etc/init.d/mysqld (this is a shell script) still has some hard-coded configuration information even we already have 3 copies of my.cnf? I had assume it should be able to retrieve conf info from conf files, and this was what the role of a conf file supposed to be To make things a bit more complicated, you cannot only start the server with the socket option, but also the client (mysql). In the my.cnf's mentioned above, also check
Re: datadir and socket
Alex, If I put a copy of my.cnf to datadir would that be any help? Any what is home_directory? Sorry I am kind of newbie for in-depth knowledge of MySQL. Nope. You don't have three copies of my.cnf (one of them actually called .my.cnf, note the dot) to make things more complicated, but to give you more flexibility. Ask the gurus on this list (Paul?! ;-) for a good example why one should have /etc/my.cnf (global options) AND datadir/my.cnf (server options). But for .my.cnf, you can use this to store username/password for the MySQL client, or even to store a different socket through which to connect to the server. And why the /etc/init.d/mysqld (this is a shell script) still has some hard-coded configuration information even we already have 3 copies of my.cnf? I had assume it should be able to retrieve conf info from conf files, and this was what the role of a conf file supposed to be I leave this one to the Linux folks. [client] OK, I added this entry into my.cnf, and it does help for connection issued from command line, but it doesn't work for PHP connection function (issued drom web). Right. This only works for clients that read the option file(s) when starting. PHP won't, but with PHP that's no problem, as the manual explains for mysql_connect(): The server parameter can also include a port number. eg. hostname:port or a path to a socket eg. :/path/to/ socket for the localhost. Note: Support for :port was added in PHP 3.0B4. Support for :/path/to/socket was added in PHP 3.0.10. Details: http://www.php.net/manual/en/function.mysql-connect.php Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Alex Shi [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; mysql group [EMAIL PROTECTED] Sent: Monday, February 03, 2003 3:27 AM Subject: Re: datadir and socket There are three my.cnf you should look for: 1) /etc/my.cnf = global options 2) datadir/my.cnf = server options, overriding global options 3) home_directory/.my.cnf = user specific options, overriding server options Thanks for your reply! The information does make sense. However, I searched the whole file system but found only /etc/my.cnf. If I put a copy of my.cnf to datadir would that be any help? Any what is home_directory? Sorry I am kind of newbie for in-depth knowledge of MySQL. I am curiouse that why mysql comes with 3 copies of same (?) configuration file. And why the /etc/init.d/mysqld (this is a shell script) still has some hard-coded configuration information even we already have 3 copies of my.cnf? I had assume it should be able to retrieve conf info from conf files, and this was what the role of a conf file supposed to be To make things a bit more complicated, you cannot only start the server with the socket option, but also the client (mysql). In the my.cnf's mentioned above, also check for [client] OK, I added this entry into my.cnf, and it does help for connection issued from command line, but it doesn't work for PHP connection function (issued drom web). Alex Shi and [mysql] and see if socket=/var/lib/mysql/mysql.sock is specified anywhere. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Alex Shi [EMAIL PROTECTED] To: mysql group [EMAIL PROTECTED] Sent: Sunday, February 02, 2003 12:03 AM Subject: datadir and socket Hi, I have a MySQL 2.23.36 running on a Linux RH 7.1. Its database directory is /var/lib/mysql/. For some reason I want to change it to /home/mysql/. So I did following things: 1. modify /etc/my.cnf: changed all /var/lib/mysql/ to /home/mysql/ 2. modyfy /etc/init.d/mysqld: changed /var/lib/mysql/ to /home/mysql/ 3. copied all database files from /var/lib/mysql/ to /home/mysql/ 4. restart mysqld OK. everything looks fine for connections from foreign machines. However, for local connections, say connections from a locally hosted web site via PHP, or a command line connection issued from shell, it will return following error message: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' This was weird, since I had assumed that I made all the neccary changes in /etc/my.cnf and /etc/init.d/mysqld. Seems like mysql has some configure information hard-coded into its buildup or whatever, and cannot be modified after installation? Alex Shi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail
Re: database mirroring in mysql
Inbal, a snapshot is simply a backup of the database at a well defined point in time. A snapshot is essential for replication to work. Master and slave are supposed to start with exactly the same set of data (snapshot) in the beginning. There are several ways to create a snapshot on the master and copy this snapshot to the slave. If you can afford to take the master server down for a moment, one convenient way to do this is: (After having configured the master and slave for replication, i.e. switching on the binlog etc.) 1. Take the master server down. 2. Copy the database(s) you want to replicate to the datadir of the slave server. 3. Restart master and slave. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Inbal Ovadia [EMAIL PROTECTED] To: 'Stefan Hinz, iConnect (Berlin)' [EMAIL PROTECTED]; Inbal Ovadia [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, February 03, 2003 12:43 PM Subject: RE: database mirroring in mysql Hi and thanks for the reply. After I read all about replication, I have a little question. In section 4.10.3 How To Set Up Replication, in step 3 it is written and then take a snapshot of the data on your master server. I am working on windows 2000 and I don't understand, how do I do the snapshot??? And another thing. Before I am doing all this steps, do I need to create the slave database? where do I need to create it? Thanks -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Monday, February 03, 2003 12:29 AM To: Inbal Ovadia; [EMAIL PROTECTED] Subject: Re: database mirroring in mysql Inbal, Is there a possibility, in MySql, to define a mirror to my database, Yes. In MySQL, it's called replication, and it's quite easy to setup. More info: http://www.mysql.com/doc/en/Replication.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Inbal Ovadia [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 02, 2003 4:56 PM Subject: database mirroring in mysql Hi all, Is there a possibility, in MySql, to define a mirror to my database, meaning that I define another database that will be a copy of my database and every operation that I will make on my database, will be done automatically in the second database? Or if I define database that contain only part of the tables in my database and when I will do some operations on one of those tables, the operations will be done in the second database? Thanks, Inbal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: date issue
Daniel, (you posted this question twice, but I will only answer it once ;-) have a month string January from a get query string like so ?month=January , and i need to check a range of results within an endate so i need WHERE end_date $_GET['month'] , You could use the DATE_FORMAT() function of MySQL for this: mysql SELECT DATE_FORMAT(NOW(),'%M'); +-+ | DATE_FORMAT(NOW(),'%M') | +-+ | February| +-+ You can't compare this with something like 'January', though, because 'January' is a string while the above result is a date. If your query string has 'month=January', this is a design problem, and you will have to use a bit of array magic to convert your string into a date for comparison. Someone slap me if there is an easy way _in MySQL_ to accomplish this. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Daniel Rossi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 03, 2003 1:41 AM Subject: date issue hi there i am having an issue formatting a date string back to mysql for a query, say i have a month string January from a get query string like so ?month=January , and i need to check a range of results within an endate so i need WHERE end_date $_GET['month'] , how can i format the month string back to mysql so it can understand it , let me know thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: restoring lost data
Peter, sorry to say, but I guess your data have gone to Nirvana. Is there any logs of the database commmands There is. It's called binlog (binäre update log), and you can turn it on by starting the server like mysqld --log-bin (or put log-bin in the [myslqd] section of your my.cnf / my.ini). This will log every command that changes data, and if you have an initial backup of your databases, you can restore your data with the backup file plus reissuing the commands in the binlog file (except for the last one, of course ;). I was pretty stupid a accidently deleted a little too much from a table, and I now I regret doing so.. if it's lost, it's lost, but if it isn't that would be better... You can avoid this problem for the future by starting your mysql client with the --safe-updates option (or put this in your my.cnf/my.ini, in the [client] section). This will prevent problems arising from commands like 'update tbl set col=val' and 'delete from tbl' with no WHERE clause. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: peter a [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 03, 2003 9:28 AM Subject: restoring lost data this may seem as a desperate questions, but is there anway to restore deleted data from a table? Is there any logs of the database commmands that for example sql-servere has, that can be used to rollback to the a specific time and in that way restore the old content of a database. I was pretty stupid a accidently deleted a little too much from a table, and I now I regret doing so.. if it's lost, it's lost, but if it isn't that would be better... thanks./peter a - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: # of connected user
Mustafa, How i list connected users(active) to mysql server? mysql SHOW PROCESSLIST; ++---+---+--+-+---+ | Id | User | Host | db | Command | Time | ++---+---+--+-+---+ | 2 | superuser | localhost | test | Sleep | 20216 | | 3 | superuser | localhost | test | Query | 0 | ++---+---+--+-+---+ it is important to know connected users for me? Depends. If you're getting into trouble with something (too many connections error, running short of memory etc.) knowing how many users are connected can become important. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Mustafa Yelmer [EMAIL PROTECTED] To: mysql group [EMAIL PROTECTED] Sent: Friday, January 31, 2003 12:49 PM Subject: # of connected user How i list connected users(active) to mysql server? it is important to know connected users for me? Mysql runs in server-client system, and each host of clients are different (i assume) Mustafa Yelmer Software Engineer +90 212 2128200-1228 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Making Lin/Win share DataSource
JD, But..., wait..., I am now still in Linux and haven't gone to or tried it in Windows [kind of allergic..., you know :)]. But, assuming the core datasource for Lin-Win is not different at all, I believe that in Windows it wouldn't be a big deal at all. In fact. As I understand, Windows can access /home/myfiles/MySQL/ as D:\MySQL\. So, after installing MySQL under Windows (doesn't even have to be the same version as under Linux, though I would recommend this), all you have to do is put the following entry in C:\WinNT\my.ini: [mysqld] datadir=d:/MySQL You will have to move the database files there, of course. If you installed MySQL with default settings, the databases will be in C:\mysql\data\. Make sure the MySQL server is down when you move the databases. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: JD [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 01, 2003 5:18 AM Subject: Making Lin/Win share DataSource On Fri, 2003-01-31 at 06:39, Stefan Hinz, iConnect (Berlin) wrote: Hallo Stefan, Thanks for your kind reply. But anyway, me too,I've solved the problem as well...:) :) So, here is what I did in term of commands: 1- $ mount /home/myfiles [this /home/myfiles is the D:\ partition in my dual-boot laptop. I'm now mounting it simply still as a normal user -- NOT as root] 2- $ mkdir /home/myfiles/MySQL 3- $ su [obvious..., accessing the root privilege] 4- $ mv /var/lib/mysql/* /home/myfiles/MySQL/ 5- $ vi /etc/my.cnf [surely..., changing the datadir line to become and to look like: datadir:/home/myfiles/MySQL/ 6- $ safe_mysqld And all done! The only mistake that I did earlier was that I as a normal-user copied/moved the data to the vfat partition. This way, when I started mysqld as root, I had no privilege at all over the datasource. But..., wait..., I am now still in Linux and haven't gone to or tried it in Windows [kind of allergic..., you know :)]. But, assuming the core datasource for Lin-Win is not different at all, I believe that in Windows it wouldn't be a big deal at all. Nice! So now no matter I'm in Windows or Linux, my data can always be intact...:) BTW..., if one tries this way too, please inform the success/failure. JD JD, my answer may seem ridiculous, too, but with the following setup you should be able to use MySQL databases and tables both under Linux and Windows on the same machine. About the only thing you need is access to the MySQL data directory from both Linux and Windows. Regardless of the operating system, mysqladmin variables will show you where DATADIR is. Of course (though it may sound even more ridiculous), you have to have MySQL installed both under Linux and under Windows. Whether you've started mysqld or not, you can issue mysqld --print-defaults to find out the location of DATADIR. However, this will not show you DATADIR if it's not in my.cnf / my.ini. Now, let's say DATADIR is in /var/lib/mysql/ under Linux, and under Windows this is c:\mysql\data\ (I'm not informed how paths will look like when you share them between OS's). In this case, you would edit /etc/my.cnf under Linux and fill in: [mysqld] datadir=/var/lib/mysql And, under Windows, this would be the following in c:\winnt\my.ini (assuming you boot Win NT/2000/XP): [mysqld] datadir=c:\mysql\data Now, I suggest you start the MySQL server (both OS's) as follows: shell mysqld --standalone --console This will output a lot of start information, so you can find out what's going wrong at startup. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: JD [EMAIL PROTECTED] To: [EMAIL PROTECTED] Hallo list, this might sound ridiculous. But, because my computer is dual booting Linux/Windows, I'm looking for ways to put the datasource in windows D:\ or vfat, so that the data can be shared mutually by the two OS. But until now I still cannot do that. I have tried to: - change the owner to mysql; - change the /etc/my.cnf Well, does somebody out there do something like this already? Thank you. JD - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual
Re: New user on OS X
Gary, [h24-85-217-157:/usr/local/bin] garydr% mysqldump -h localhost -u root bpw_dbbpw_db_backup.sql bpw_db_backup.sql: Permission denied. This means that MySQL is not allowed to write in this directory. Which directory? ;-) Well, as you don't specify a path, bpw_db_backup.sql will be created in the bin/ subdirectory of your MySQL installation directory, because the mysqldump executable is located there. I have no idea what's going on. I understand that this command should create an sql file with my database structure and data in it. Sounds good. However, I can't run the command. mysqldump -u root bpw_db some-nice-directory/bpw_db_backup.sql should do the trick, where some-nice-directory is a location where the MySQL server has write access. (BTW, you don't have to specify -h localhost when it's localhost.) I read in a manual I could get help as follows: [h24-85-217-157:/usr/local/bin] garydr% mysqldump --help mysqldump: Command not found. This is strange. As I can see above, mysqldump was found in the first place. Can anyone help me, or direct me to a good source of information related to running mysql on OS X? For example, I can't figure out where in my system the mysql database files are located. If I could, at least I could just make backup copies to CD. If you want a better understanding about MySQL, and you have half an hour to spend, read this: http://www.mysql.com/articles/mysql_intro.html For mysqldump, the manual is here: http://www.mysql.com/doc/en/mysqldump.html If you want to find out where your database directory is, you can start the mysql client tool (named mysql, in exactly the same location where mysqldump is), and issue: SHOW VARIABLES LIKE 'datadir'; If you're not able to locate where all the MySQL binaries are, you can try: which mysqld AFAIK, OS X is Unix, so this should work. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Gary Reimer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 01, 2003 4:12 AM Subject: New user on OS X I'm a new mysql user running on OS X. I have set up a test database and am trying to use mysqldump to create a backup. However here is what happens: [h24-85-217-157:/usr/local/bin] garydr% mysqldump -h localhost -u root bpw_dbbpw_db_backup.sql bpw_db_backup.sql: Permission denied. I have no idea what's going on. I understand that this command should create an sql file with my database structure and data in it. Sounds good. However, I can't run the command. I read in a manual I could get help as follows: [h24-85-217-157:/usr/local/bin] garydr% mysqldump --help mysqldump: Command not found. But as you can see, it doesn't work. Can anyone help me, or direct me to a good source of information related to running mysql on OS X? For example, I can't figure out where in my system the mysql database files are located. If I could, at least I could just make backup copies to CD. Any help would be greatly appreciated. Thanks in advance, Gary -- Gary Reimer Winnipeg MB Canada - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: host.frm
Paul, I have the same problem, the file does exist and the ownership is the mysql user, but the entry in the log file still says it cant find this file when starting mysql daemon. And the manual said: If you don't set up the grant tables, the following error will appear in the log file when you start the server: mysqld: Can't find file: 'host.frm'. http://www.mysql.com/doc/en/Post-installation.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Paul Roskilly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 01, 2003 5:47 PM Subject: FW: Re: host.frm -Original Message- From: Paul Roskilly [mailto:[EMAIL PROTECTED]] Sent: 01 February 2003 13:11 To: '[EMAIL PROTECTED]' Subject: Re: host.frm I have the same problem, the file does exist and the ownership is the mysql user, but the entry in the log file still says it cant find this file when starting mysql daemon. Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: datadir and socket
Alex, However, for local connections, say connections from a locally hosted web site via PHP, or a command line connection issued from shell, it Remote connections are made through TCP/IP. That's why you get the sock error only for local connections. will return following error message: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' Somewhere over the rainbow - no, but in one of the my.cnf files on your local machine there is still a socket=/var/lib/mysql/mysql.sock entry. Another possible thing is the server is started by a script (mysqld_safe?) with the --socket=/var/lib/mysql/mysql.sock option. There are three my.cnf you should look for: 1) /etc/my.cnf = global options 2) datadir/my.cnf = server options, overriding global options 3) home_directory/.my.cnf = user specific options, overriding server options To make things a bit more complicated, you cannot only start the server with the socket option, but also the client (mysql). In the my.cnf's mentioned above, also check for [client] and [mysql] and see if socket=/var/lib/mysql/mysql.sock is specified anywhere. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Alex Shi [EMAIL PROTECTED] To: mysql group [EMAIL PROTECTED] Sent: Sunday, February 02, 2003 12:03 AM Subject: datadir and socket Hi, I have a MySQL 2.23.36 running on a Linux RH 7.1. Its database directory is /var/lib/mysql/. For some reason I want to change it to /home/mysql/. So I did following things: 1. modify /etc/my.cnf: changed all /var/lib/mysql/ to /home/mysql/ 2. modyfy /etc/init.d/mysqld: changed /var/lib/mysql/ to /home/mysql/ 3. copied all database files from /var/lib/mysql/ to /home/mysql/ 4. restart mysqld OK. everything looks fine for connections from foreign machines. However, for local connections, say connections from a locally hosted web site via PHP, or a command line connection issued from shell, it will return following error message: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' This was weird, since I had assumed that I made all the neccary changes in /etc/my.cnf and /etc/init.d/mysqld. Seems like mysql has some configure information hard-coded into its buildup or whatever, and cannot be modified after installation? Alex Shi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: database mirroring in mysql
Inbal, Is there a possibility, in MySql, to define a mirror to my database, Yes. In MySQL, it's called replication, and it's quite easy to setup. More info: http://www.mysql.com/doc/en/Replication.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Inbal Ovadia [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 02, 2003 4:56 PM Subject: database mirroring in mysql Hi all, Is there a possibility, in MySql, to define a mirror to my database, meaning that I define another database that will be a copy of my database and every operation that I will make on my database, will be done automatically in the second database? Or if I define database that contain only part of the tables in my database and when I will do some operations on one of those tables, the operations will be done in the second database? Thanks, Inbal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
Marco, mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? The small answer is: It's a feature, and it's documented as well: Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. http://www.mysql.com/doc/en/DATETIME.html BTW: In tables like yours I always have _two_ timestamp columns, like: letzteAenderung TIMESTAMP erstellDatumTIMESTAMP On INSERT, I set erstellDatum to now(). letzteAenderung will automatically be set to the same value. On UPDATE, erstellDatum will be left untouched (because it's not the first TIMESTAMP column), and letzteAenderung will be set to NOW() without me having to think of it. A very convenient feature, indeed :) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Marco Deppe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 12:17 PM Subject: TIMESTAMP field is updated unintentionally Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) -- Best regards, Marco mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: partitionong MyISAM tables??
Prasanth, Is there any way to partition MyISAM tables in mysql? i have a huge table and want to partition it. An easy way to slice it into chunk would be: CREATE TABLE tblPart_1 SELECT * FROM tblFull LIMIT 0,1000; CREATE TABLE tblPart_2 SELECT * FROM tblFull LIMIT 1000,1000; CREATE TABLE tblPart_3 SELECT * FROM tblFull LIMIT 2000,1000; etc. With InnoDB tables, you can do exactly the same. If you need the tables to look like one big table later on you can use the MERGE table format (if your tblPart_* are MyISAM tables). CREATE TABLE tblFullMerge (col1 ..., col2 ..., ...) TYPE=MERGE UNION(tblPart_1,tblPart2,...); With InnoDB, you cannot use MERGE tables. I don't know if that's what you want. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Prasanth Krishna [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 12:36 PM Subject: partitionong MyISAM tables?? Is there any way to partition MyISAM tables in mysql? i have a huge table and want to partition it. Do InnoDB tables support partitioning? thanks. Prasanth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Making Lin/Win share DataSource
Sherzod, : Now, let's say DATADIR is in /var/lib/mysql/ under Linux, and under : Windows this is c:\mysql\data\ (I'm not informed how paths will look : like when you share them between OS's). : Guys, are you sure you can share files between two absolutely different file systems just like that? I don't think it's gonna be as easy as you want it to be. The only problem is that you have to make your disk mountable both under Linux and Windows. If you succeed with this, there's no problem at all to use MySQL data across those plattforms. MySQL's MyISAM tables are platform independent, and so are MERGE and HEAP. I'm not sure about BDB and InnoDB, but I guess they are platform independent, too. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Sherzod Ruzmetov [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; JD [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, February 01, 2003 2:04 AM Subject: RE: Making Lin/Win share DataSource Wow, one hell of a discussion! :-) : Now, let's say DATADIR is in /var/lib/mysql/ under Linux, and under : Windows this is c:\mysql\data\ (I'm not informed how paths will look : like when you share them between OS's). : Guys, are you sure you can share files between two absolutely different file systems just like that? I don't think it's gonna be as easy as you want it to be. There should be some midleware solutions that may help you do that. In any event, I'm curious to find out myself. Sherzod - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: line breaks
Tarik, - what is the stored character code of the enter key in the text field, i am figuring out to find that character and replace it with the br element, for the exact display, The character is \n (the new line character). If you use PHP and have magic_quotes=on in your php.ini, PHP will automatically escape certain characters like \n. If magic_quotes=off, you can use the addslashes() function to accomplish this. When retrieving the data out of MySQL to display them in a textarea field once again, you have to do nothing. The web browser will take care of converting \n into something the operating thinks is a new line (Unix: \n, Windows: \r\n, MacOS: \r). If you want to display the data outside of form fields, you will have to convert \n into something HTML knows is a new line. HTML uses for example br or p for this. In PHP, you can use the nl2br() function that will convert \n into br. If you don't use PHP, things might be a bit more complicated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Tarik Kutluca [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 5:22 PM Subject: line breaks Hi, From a web form I am collecting information to a table. On the form there is a textarea element storing to a text field on the mysql table. Since the textarea can hold the enter key, it's stored in to the field also, but when i try to call that cell and display it on the web page, i see that line breaks are ignored. But the sentences must be displayed as they are entered. - what is the stored character code of the enter key in the text field, i am figuring out to find that character and replace it with the br element, for the exact display, - do you have a shorter way to do this? thank you - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: timing
Mary, The Production server has MYSQL 3.23 and an older version of MYODBC then I have. They run in 8 minutes on my test server , about 4 minutes each. Printing to a PDF driver. On the Production Server they take HOURS to run and print. Maybe this won't help too much, but I've experienced that in some cases MyODBC 2.50 is sooo much slower than MyODBC 3.51. An export I did from MS Access to MySQL (some 1.5 million records) took hours with 2.50, and only 3 minutes with 3.51. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Mary Stickney [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 3:08 PM Subject: timing I have 2 Crystal Reports 8.5 (for the record I didn't make these reports) that draw straight from the MYSQL database. They use only 1 table. I am calling them to print from VB 6.0 Theses reports have lots of complex calculations in them, counts , sums and groups. My test server has the same code , same database , same indexes , same amount of memory as the Production server, My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal installed on my test server. The Production server has MYSQL 3.23 and an older version of MYODBC then I have. No Crystal Reports installed. And the server is a faster machine then mine. They run in 8 minutes on my test server , about 4 minutes each. Printing to a PDF driver. On the Production Server they take HOURS to run and print. Any ideas... Mary Stickney TAG-TMI Data Warehouse Programmer 402-474-7612 x 3099 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [ mysqlimport ]
Elby, I created a file.sql with mysqldump. mysqldump my_db my_table file.sql What I do to get this file with the mysqlimport? mysqlimport ?? You do this with mysql in batch mode, not with mysqldump: shell mysql your_database file.sql Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Elby Vaz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 9:39 PM Subject: [ mysqlimport ] I created a file.sql with mysqldump. mysqldump my_db my_table file.sql What I do to get this file with the mysqlimport? mysqlimport ?? Thanks, e. _ MSN Messenger: converse com os seus amigos online. http://messenger.msn.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Making Lin/Win share DataSource
JD, my answer may seem ridiculous, too, but with the following setup you should be able to use MySQL databases and tables both under Linux and Windows on the same machine. About the only thing you need is access to the MySQL data directory from both Linux and Windows. Regardless of the operating system, mysqladmin variables will show you where DATADIR is. Of course (though it may sound even more ridiculous), you have to have MySQL installed both under Linux and under Windows. Whether you've started mysqld or not, you can issue mysqld --print-defaults to find out the location of DATADIR. However, this will not show you DATADIR if it's not in my.cnf / my.ini. Now, let's say DATADIR is in /var/lib/mysql/ under Linux, and under Windows this is c:\mysql\data\ (I'm not informed how paths will look like when you share them between OS's). In this case, you would edit /etc/my.cnf under Linux and fill in: [mysqld] datadir=/var/lib/mysql And, under Windows, this would be the following in c:\winnt\my.ini (assuming you boot Win NT/2000/XP): [mysqld] datadir=c:\mysql\data Now, I suggest you start the MySQL server (both OS's) as follows: shell mysqld --standalone --console This will output a lot of start information, so you can find out what's going wrong at startup. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: JD [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 7:11 AM Subject: Making Lin/Win share DataSource Hallo list, this might sound ridiculous. But, because my computer is dual booting Linux/Windows, I'm looking for ways to put the datasource in windows D:\ or vfat, so that the data can be shared mutually by the two OS. But until now I still cannot do that. I have tried to: - change the owner to mysql; - change the /etc/my.cnf Well, does somebody out there do something like this already? Thank you. JD - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question about Table_Options
Jonathan,
The O'Reilly Managing Using MySQL show on page 288 a table been
created
with two options ...)AUTO_INCREMENT = 1, TYPE=InnoDB;
But the MySQL manual says or:-
table_options:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or AUTO_INCREMENT = #
Oh, I stumbled over this one, too. Here's my explanation :)
mysql CREATE TABLE autotest_myisam (id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY) TYPE=MyISAM AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.08 sec)
mysql INSERT INTO autotest_myisam VALUES (NULL);
Query OK, 1 row affected (0.01 sec)
mysql SELECT * FROM autotest_myisam;
+-+
| id |
+-+
| 100 |
+-+
1 row in set (0.02 sec)
mysql CREATE TABLE autotest_InnoDB (id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY) TYPE=InnoDB AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.10 sec)
mysql INSERT INTO autotest_InnoDB VALUES (NULL);
Query OK, 1 row affected (0.07 sec)
mysql SELECT * FROM autotest_InnoDB;
++
| id |
++
| 1 |
++
1 row in set (0.00 sec)
As you see, TYPE=MyISAM AND AUTO_INCREMENT=100 works, but TYPE=InnoDB
AND AUTO_INCREMENT=100 does not work. But isn't it supposed to be 'xor'
instead of 'or'? ;-)
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Jonathan Bedford [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, January 31, 2003 9:12 AM
Subject: Question about Table_Options
Hi
When creating a table can you use multiple Table_Options?
The O'Reilly Managing Using MySQL show on page 288 a table been
created
with two options ...)AUTO_INCREMENT = 1, TYPE=InnoDB;
But the MySQL manual says or:-
table_options:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or AUTO_INCREMENT = #
.
Thanks
Jonathan
-
This mail sent through IMP: http://horde.org/imp/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql, PHP and Javascript
Steve, this list is not the right place to ask JavaScript questions. Please check out an appropriate list at http://php.net/. But, anyway, it's possible to mix JS and PHP, no problem. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Steve Davies [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 9:27 AM Subject: MySql, PHP and Javascript Hi All Is it possible to mix javascript and PHP in the same script?? I have a number of web based apps written in PHP/MySql and while they are functionally pretty good they are aesthetically garbage. I'll like to pretty up the interfaces with rollovers etc, but haven't got time to learn JS properly especially if I have to completly re-write the functionality. Any help greatly appreciated Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Difference between MySQL versions
Vikram, Can someone point me to amn (exhaustive) list of features in MySQL Max that are not present in MySQL Standard? Or any other documentation that lists the differences between the two? http://www.mysql.com/products/mysql/index.html (not too exhaustive, though) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vikram Vaswani [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 11:29 AM Subject: Difference between MySQL versions Hi there, Can someone point me to amn (exhaustive) list of features in MySQL Max that are not present in MySQL Standard? Or any other documentation that lists the differences between the two? Thanks in advance, Vikram -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Getting Images in and out of a Blob
Mike, Has anyone done any work with getting images into and out of a MYSQL database? I have used mysql for some time, but never stored an image in a blob field. I am curious on how to insert the image from a web form upload, and how to display the picture on another HTML page. You might want to start here: http://www.mysql.com/doc/en/String_functions.html. Look for LOAD_FILE. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Mike Walth [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 9:26 AM Subject: Getting Images in and out of a Blob Has anyone done any work with getting images into and out of a MYSQL database? I have used mysql for some time, but never stored an image in a blob field. I am curious on how to insert the image from a web form upload, and how to display the picture on another HTML page. Preferred web scripting is ColdFusion, but PHP/PERL would be helpful too. Thank you for your help. Mike Walth CinoFusion - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock is missing - Please....
Thomas, I installed MySQL on my server and after reboot i got the error message when starting mysql: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) Is mysqld up and running? Try ps xa | grep mysqld. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Thomas Schlagbauer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 30, 2003 2:04 PM Subject: Re: mysql.sock is missing - Please Hi, I don't know who is receiving my message, but i have an important request on you. I also have the same problem. I installed MySQL on my server and after reboot i got the error message when starting mysql: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) the file mysqld.sock doesn't exist on the server. I also can't find the file mysql.server. I'm using Debian 3.0. Debian-Package: mysql-server shell mysql --version gives the following: mysql Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686) Please help me! Thank you - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 1148 by remote mysql +execute +insert
Dirk, you're right. LOCAL is disabled by default with all newer versions of MySQL (server and client). One can enable LOCAL by _compiling_ MySQL with --enable-local-infile, but this is not possible for binaries, of course. The manual says you can enable LOCAL with --local-infile=1, either for the server and/or for the mysql client. This doesn't work for me, though. I'm still waiting for an explanation by a guru/developer whether I am misunderstanding something, or if this is a bug, or even a feature. http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Dirk Hennrichs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 29, 2003 7:25 AM Subject: Error 1148 by remote mysql +execute +insert I am using mysql 3.23.59. By using the command mysql -h Remote --database=db -uuser -ppassword --execute load data local infile .. I get error nr. 1148: The used command is not allowed with this MySQL Version. Before I used mysql 3.23.37 and the above command was OK. I tested the --execute command with oder SQL Statements like select, update, everything works fine. Only the load command seems to be disabled. Has anybody an idea if the --execute and load infile tag can be enabled with version 3.23.59. My fear is that any new updated version does not allow this command, and I need it !!! ;-) Any hint ? Dirk Hennrichs - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL error (Errcode: 13)
Dhanashri, ALTER TABLE `timesheet` ADD `phase_desc` VARCHAR( 50 ) Error on rename of '.\etime\timesheet.MYI' to '.\etime\#sql2-64c-19.MYI' (Errcode: 13) I had the same kind of problem with ALTER TABLE and MySQL 4.0.5 under Win2K. Errorcode 13 means it's a permission problem. I never solved this problem, but it disappeared an never returned after I upgraded to 4.0.5. I you do not want to upgrade to version 4 (for whatever reason, as it runs stable as rock), you might want to use the workaround I used: CREATE TABLE timesheetCopy SELECT * FROM timesheet; DROP TABLE timesheet; RENAME TABLE timesheetCopy TO timesheet; ALTER TABLE timesheet ADD phase_desc VARCHAR(50); For some strange reason, I never encountered permission errors with copied tables, only with the original ones. My phpMySQL version is 3.23.55-nt Well, that's rather your MySQL version. See above what I said about upgrading. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Dhanashri Peramanu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 27, 2003 10:25 PM Subject: MySQL error (Errcode: 13) Anybody have an idea about the following error (Errcode: 13)? I am simply trying to add one more field in the table timesheet. My phpMySQL version is 3.23.55-nt Thanks. -Dhanashri Peramanu -- -- -- -- -- Error SQL-query : ALTER TABLE `timesheet` ADD `phase_desc` VARCHAR( 50 ) MySQL said: Error on rename of '.\etime\timesheet.MYI' to '.\etime\#sql2-64c-19.MYI' (Errcode: 13) -- -- -- -- - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my.ini
Vicente, language=spanish You could try language=c:/mysql/share/spanish. With my 4.0.7 on Win2K, both works. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 8:59 AM Subject: my.ini Hi, This is my 'my.ini' file where I specified 'language=spanish' but the sql errors appear in English yet. I don't see another line after this that changes the value. Can somebody help me? # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 language=spanish #socket=MySQL skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/mysql/ datadir = c:/mysql/data/ # Uncomment the following if you are NOT using BDB tables #skip-bdb # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:400M innodb_data_home_dir = c:\ibdata innodb_log_group_home_dir = c:\iblogs innodb_log_arch_dir = c:\iblogs 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=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SELECT, JOIN and WHERE query
Victor, good shot! I thought of this one in the first place: SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id 2 But unfortunately, it will yield: +-+-+ | user_id | team_id | +-+-+ |1000 | 1 | |1001 | 3 | |1002 | 3 | |1003 | 3 | |1004 | 1 | |1005 | 1 | |1005 | 3 | +-+-+ 7 rows in set (0.00 sec) Where it should yield: +-+ |1002 | |1004 | +-+ A subselect would be most simple: SELECT u.user_id FROM user_profile u WHERE user_id NOT IN (SELECT user_id FROM team_member t WHERE t.team_id = 2) It's too late am I'm too tired to rewrite this query so it will work in MySQL = 4.0.9. With MySQL 4.1 (alpha out now!!!) we have subselects :) Good night, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'heiko mundle' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 5:30 PM Subject: RE: Help with SELECT, JOIN and WHERE query SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id 2 -Original Message- From: heiko mundle [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:08 AM To: [EMAIL PROTECTED] Subject: Help with SELECT, JOIN and WHERE query Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +-+ | user_id | +-+ |1000 | |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | | 1 |1004 | | 1 |1005 | | 2 |1006 | | 2 |1003 | | 2 |1000 | | 2 |1001 | | 2 |1005 | | 3 |1001 | | 3 |1005 | | 3 |1002 | | 3 |1003 | | ... |... | +-+-+ Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ Thanks for any hint Kind regards Heiko Mundle -- +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Language errors
Vicente, How can I change my error language in SQL (MySQL)? Have I to introduce a new set-variable in my.ini? In the [mysqld] section of my.cnf / my.ini, type: language=spanish Alternatively, you can start mysqld with the --language=spanish option. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 27, 2003 10:31 AM Subject: Language errors Hello, How can I change my error language in SQL (MySQL)? Have I to introduce a new set-variable in my.ini? Thanks ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Language errors
Vicente, I've tried this, but it hasn't effect. In WinMySQLadmin 1.4, section Variables, language has this value yet 'c:\mysql\share\english' Does this look Spanish to you? 1064: Algo está equivocado en su sintax cerca '' en la linea 1 Maybe you didn't restart your MySQL server. Another problem could be that there is another entry in [mysqld] _after_ language=spanish which would take precedence. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, January 27, 2003 1:06 PM Subject: Re: Language errors Hi, I've tried this, but it hasn't effect. In WinMySQLadmin 1.4, section Variables, language has this value yet 'c:\mysql\share\english' - Original Message - From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] To: Vicente Valero [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, January 27, 2003 12:23 PM Subject: Re: Language errors Vicente, How can I change my error language in SQL (MySQL)? Have I to introduce a new set-variable in my.ini? In the [mysqld] section of my.cnf / my.ini, type: language=spanish Alternatively, you can start mysqld with the --language=spanish option. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 27, 2003 10:31 AM Subject: Language errors Hello, How can I change my error language in SQL (MySQL)? Have I to introduce a new set-variable in my.ini? Thanks ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECTing across a Network
Obafemi, Please can someone tell me how to run a SELECT between databases on a network. With MySQL, this is not possible. AFAIK there are no plans to implement this in the near future, or to implement this feature at all. Customers using MS SQL Server asked me the same thing, as MS SQL Server does have this feature. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Isaac Anietye Inyang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 27, 2003 3:23 PM Subject: SELECTing across a Network Hello Please can someone tell me how to run a SELECT between databases on a network. I want to SELECT some data from a mySQL server named PC1 to a table on a second mySQL server named PC2. Thanks in advance. Isaac Anietye Inyang Systems Engineer Cooperative Information Network Obafemi Awolowo University Ile-Ife - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Adding a user problem
Devin, GRANT ALL PRIVILEGES ON devin.* TO dmiller@% IDENTIFIED BY password; The command returns a Query ok and 0 rows affected. However, when I try to login with dmiller (mysql -u dmiller -p) I get a 1045 Access denied error. With mysql -u dmiller -p, the hostname will be localhost (the default). Obviously, there is an entry in the privilege table mysql.user which takes precedence over '%'. You have two ways to solve this problem: 1) GRANT ALL ON devin.* TO 'dmiller'@'localhost' IDENTIFIED BY 'password'; 2) mysql -h machine_name -u dmiller -p In case #2, you have to substitute machine_name by the name of the machine you are connecting from. Don't use 'localhost', though ;-) If you're curious as to why and how: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database _Administration.html#Connection_access Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Devin Miller [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 27, 2003 9:03 PM Subject: Adding a user problem I am pretty new to mysql and have been teaching myself slowly how to use it. I am however stuck on a problem. I login to mysql as user root and created a database named devin. I then issue the following commands: GRANT ALL PRIVILEGES ON devin.* TO dmiller@% IDENTIFIED BY password; The command returns a Query ok and 0 rows affected. However, when I try to login with dmiller (mysql -u dmiller -p) I get a 1045 Access denied error. I have triple checked spelling and such and it looks good as far as I know. Any Ideas? Thanks a lot in advance for your help. Btw I am using MySQL ver. 3..23.52. Devin Miller No electrons were hurt in the making of this email. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Segmentation fault when scanning mysqld port
Manuel, Benjamin, /usr/local/mysql/3.23.55/bin/safe_mysqld: line 280: 1757 Segmentation fault And again, this looks like the SuSE bug to me. Here's my posting about an error like this on Red Hat: cite who=myself Synopsis: Server crashes on remote connection I'm not a Unix guru but I experienced the same sympton on SuSE Linux 8.0/8.1, and I heard of people reporting the same of Red Hat Linux 7.x/8.x. MySQL runs stable, you can connect from localhost via socket and tcp/ip, but as soon as you try a connect from some other machine, mysqld segfaults. In all cases I heard of, the problem was some glibc version that caused all the trouble. You say you use glibc-2.2.93-5 This version # looks a bit strange to me, well ... In a SuSE Linux Newsgroup I heard that everything's okay if you use = 2.2.5-151. We use glibc-2.2.5-164 on SuSE Linux 8.1, and with our 3.23.54, we've had no problems at all. /cite HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Manuel Kiessling [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, January 25, 2003 9:44 PM Subject: Re: Segmentation fault when scanning mysqld port On Sat 2003-01-25 at 14:16:35 +0100, [EMAIL PROTECTED] wrote: Hello, i just installed MySQL 3.23.55 on a SuSE Linux 8.1 system (compiled myself), and after firing up mysqld and starting a scan (via nmap; my server has no firewall) from another server, i get the following message: /usr/local/mysql/3.23.55/bin/safe_mysqld: line 280: 1757 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 1760 - killed 030125 15:06:29 mysqld restarted Hope someone can say something to this. Try a pre-compiled binary? Without testing, I simply presume that such an obvious bug would not stay long. So your binary looks shaky. Okay, I tested against the older 3.23.49 and cannot reproduce this. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Segmentation fault when scanning mysqld port
Manuel, And again, this looks like the SuSE bug to me. that's good news, at least now i know it's not just me. Anyone has a good tutorial about updating glibc? :-) If you're lucky, you might be able to use this RPM: ftp://ftp.suse.com/pub/suse/i386/update/8.1/rpm/i586/glibc-2.2.5-161.i58 Now, this seems like version ...-161 to me. On my linux box, I have ...-164 right now. No problem with MySQL. (I wonder why SuSE and - supposedly - Red Hat put old glibc's in their dists. There seems no good reason for doing this.) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Manuel Kiessling [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] Cc: Benjamin Pflugmann [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, January 26, 2003 11:07 AM Subject: Re: Segmentation fault when scanning mysqld port hello, Stefan Hinz, iConnect (Berlin) wrote: Manuel, Benjamin, And again, this looks like the SuSE bug to me. that's good news, at least now i know it's not just me. Anyone has a good tutorial about updating glibc? :-) -- Manuel Kiessling - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there anything like Query Analyser for MySQl
Scott, I was wondering, is there a program simialar to MS SQL server - Query Analyser, where you can write sql queries to query MySQl databases? It's called EXPLAIN. More info: http://www.mysql.com/doc/en/EXPLAIN.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Scott Lewis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 26, 2003 11:25 AM Subject: Is there anything like Query Analyser for MySQl I was wondering, is there a program simialar to MS SQL server - Query Analyser, where you can write sql queries to query MySQl databases? Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Client History
Darren, Where does the mysql client store the query history information? .mysql_history in your home directory, for example. Under Win it doesn't get stored, AFAIK. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Darren Young [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 25, 2003 12:23 AM Subject: Client History Where does the mysql client store the query history information? I'm referring to the information that it pulls up when you hit the up or down arrow keys. There was a rather complex query that I came up with a while ago and don't feel like 'up arrowing' all night... Thanks, Darren Young mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: upgrade mySQL
Lewell, ERROR 1064 at line 1: You have an error in your SQL syntax. Check the manual that corresponds to your mySQL server version for the right syntax to use near 'mysql_fix_privilege_tables' at line 1 The script your speaking of is a Unix shell script, and there is nothing like that in the Windows binary dist, AFAIK. The MySQL 4.0.x setup should set up the new privileges (privilege tables), too. I guess this will only happen when you delete / rename your old mysql database beforehand. I'll send Cc: to Miguel who is in care of setting up the dists at MySQL AB, AFAIK. He should know what to do best to change the priv tables to have the new privs available. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: pazenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 25, 2003 1:18 PM Subject: upgrade mySQL Greetings I'm currently upgrading mySQL v3.23 to mySQL v4.0, the installation of v4.0 was a success,I tried to execute mysql_fix_privilege_tables c:\mysql\binmysql -e mysql_fix_privilege_tables but i keep on getting the error: ERROR 1064 at line 1: You have an error in your SQL syntax. Check the manual that corresponds to your mySQL server version for the right syntax to use near 'mysql_fix_privilege_tables' at line 1 what is the right way of running this script? OS: win98se mySQL: version 4.0.9 -gamma winMySQLAdmin 1.4: started lewell The content of this email when sent is as full as practical. Due to the settling of the words the contents might not appear as full as it was but the weight remains the same. The contents is being sent by weight and not by volume. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question about date range
Chuck, select * from orderheader where orderid like '$date' ; The $date variable equals 20030124% Am I right to suppose your query doesn't work? Did you try something like: $sql = select * from orderheader where orderid like '$date'; print $sql; in your application? If have not enough information, but a possible bug could be that $date is not what you expect it to be, but rather something like: 20030124\% HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Chuck Barnett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 24, 2003 6:33 PM Subject: question about date range Hi, I inherited a db that has a varchar(30) column that holds a date/time stamp in the form of MMDDHHmmss (ex: 20030124093952) Well I want to select a range based on the first 8 characters(MMDD). I currently select a single day by select * from orderheader where orderid like '$date' ; The $date variable equals 20030124% What can I do to get this range selection query to work? Thanks, Chuck Barnett - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Displaying news across several pages
Pag, as Rich said: select * from mytable limit 10,10 select * from mytable limit 20,10 In your PHP (?) application, you would have this like: select * from mytable limit $start,$range You could set up range (e.g. in config.inc.php) to whatever you want for the site, and you would add $range to $start when the user clicks on next, and substract $range from $start when the user clicks on previous. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Rich Gray [EMAIL PROTECTED] To: Pag [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 25, 2003 12:03 PM Subject: RE: Displaying news across several pages when 2 arguments are specified for LIMIT then the first argument is the offset and the second is the max number of rows e.g. below select * from mytable limit 10,10 select * from mytable limit 20,10 Rich -Original Message- From: Pag [mailto:[EMAIL PROTECTED]] Sent: 25 January 2003 05:10 To: Roger Davis; [EMAIL PROTECTED] Subject: Displaying news across several pages Hi, I am a beginner in this MySQL thing, and i am trying to figure out how i can split, for example, some news articles i already have in a table, into several pages. On the site i am building, i get the 10 first news and display them. I use SELECT FROM NEWS ... ORDER BY DATE DESC LIMIT 10. Is there any mysql command to select the next 10 items, for example? something like LIMIT from 10 to 20 hehe, i know it sounds weird. :-) Thanks. Pag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by birthday not year
Mike, example birthday=1972/02/14 How do i tell mysql to order by the day (14) only so that I can list same birthday in a month in acending order. If your birthdays are stored in a DATE column, your example would be 1972-02-14. In this case, you can use the date functions of MySQL. Here are some examples (my table datum has only one column named datum, type DATE: mysql SELECT * FROM datum ORDER BY DAYOFMONTH(datum); ++ | datum | ++ | 2002-02-08 | | 2003-02-08 | | 1964-12-17 | | 1958-08-29 | ++ 4 rows in set (0.02 sec) mysql SELECT * FROM datum ORDER BY MONTH(datum), DAYOFMONTH(datum); ++ | datum | ++ | 2002-02-08 | | 2003-02-08 | | 1958-08-29 | | 1964-12-17 | ++ 4 rows in set (0.01 sec) HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Mike Doanh Tran [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 24, 2003 11:14 PM Subject: order by birthday not year Hi, I want to use the order by clause to list the birthday field by the birthday not year. How do i do this? example birthday=1972/02/14 How do i tell mysql to order by the day (14) only so that I can list same birthday in a month in acending order. Thanks -- Mike Doanh Tran Shuffle Master Inc. Website: http://internal.shufflemasterrd.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Windows InnoDB
Darren, Is the InnoDB table type available on Windows? Yes. If you want it to be the default table type, start the server with --default-table-type=innodb. Note that you will have to use a newer version of MySQL ( 3.23.somewhat). In 3.23.x, you will have to enable InnoDB, in 4.0.x, it's enabled by default. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Darren Young [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 24, 2003 11:03 PM Subject: Windows InnoDB Is the InnoDB table type available on Windows? Darren Young [EMAIL PROTECTED] mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cannot connect from WInXp MYSQL server - help
Scott, most probably, you've got a glibc problem on your Red Hat box. Try upgrading to a newer version of glibc, and if the problem (mysqld dies when trying to connect from anywhere else than localhost) has gone, I was right :) (There was a very similar question a couple of days ago on this list. I don't know if the Red Hat problem is exactly the same as the SuSE problem I could solve by updating glibc, but the symptom is the same.) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Scott Lewis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 25, 2003 6:51 PM Subject: Cannot connect from WInXp MYSQL server - help OK, I have a mysql server running on my home server. The main os is Redhat8. I know that Mysql is running on port 3306 as its showing as listening. I ahve opened the tcp port for boh internal and external connections - fione. I ahve also disbaled it to test aswell. Anyway - I have loaded several MYsql clients on my winxp pro pc on my home LAN, eg Dbmanager. I ahve set the settinsg fine, and each time I try and connection I get the error:- Lost connection to mysql durring query. In the loags it says:- Number of processes running now: 1 mysqld process hanging, pid 2522 - killed 030125 17:49:36 mysqld restarted /usr/libexec/mysqld: ready for connections So - its killing the pid, which I assume is my connections. Any ideas on what to look for. Scott www.toptravelsites.net www.myeyes-youreyes.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: insert query
Murthy, how to insert only blank spaces into a field using insert query. Its getting trimmed off. If you are using VARCHAR for this, you're right. It's a documented behaviour of MySQL (Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the ANSI SQL specification.)): http://www.mysql.com/doc/en/CHAR.html If you need the spaces, you will have to use CHAR. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Murthy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 24, 2003 11:44 AM Subject: insert query how to insert only blank spaces into a field using insert query. Its getting trimmed off. Regards, Murthy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: updating tables
Fabrizio, (still only guessing, as you did not post the table structures ...) i need also to change structure of the master table (have only a primary key) or the changes made on the updatable_table are enough? Your updatable_table needs to have a unique key (best is a primary key), so REPLACE can tell whether it should INSERT a record (if there is no unique key entry) or actually REPLACE an existing record (if there is an entry with this key already). Let's say you have tbl1 with your original data: mysql SELECT * FROM tbl1; ++--+ | id | sometext | ++--+ | 1 | foo | | 2 | bar | | 3 | foofoo | | 4 | barbar | ++--+ 4 rows in set (0.00 sec) Now, you have tbl2 with some data: mysql SELECT * FROM tbl2; ++--+ | id | sometext | ++--+ | 3 | foofoo | | 4 | barbar | ++--+ 2 rows in set (0.00 sec) 'id' in tbl2 needs to be unique (in my case, it's the primary key), so REPLACE will work as expected: mysql REPLACE INTO tbl2 SELECT * FROM tbl1; Query OK, 4 rows affected (0.00 sec) Datensõtze: 4 Duplikate: 2 Warnungen: 0 Note that 4 records were affected, but there were 2 duplicates. The records with id 3 and 4 were overwritten by the REPLACE statement. Now tbl2 has these data: mysql SELECT * FROM tbl2; ++--+ | id | sometext | ++--+ | 1 | foo | | 2 | bar | | 3 | foofoo | | 4 | barbar | ++--+ 4 rows in set (0.01 sec) HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Fabrizio Tivano [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 24, 2003 10:50 AM Subject: Re: updating tables Stefan, The problem look be solved; i've just changed structure of the updatable table. First this table have only 1 primary key, now my table have some indexed unique keys. and now if i do a replace into command look's like to work fine. I have just a question: i need also to change structure of the master table (have only a primary key) or the changes made on the updatable_table are enough? regards, fabrizio On Fri, 24 Jan 2003 00:10:19 +0100 Stefan Hinz, iConnect \(Berlin\) [EMAIL PROTECTED] wrote: Fabrizio, please send the table structures of table_1 and table_2 (DESCRIBE ...) so we can do more for you than just guess what the problem might be. REPLACE seems good in the first place, but if it just INSERTs then there's a problem with the (primary) keys. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Fabrizio Tivano [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 5:22 PM Subject: updating tables hello dear all, i have a problem updating tables: in my queries i try to use REPLACE but is same like INSERT command. ...i need only to refresh table_1 with new entries from table_2. any ideas? TIA fabrizio - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Fabrizio Tivano WAN System Administrator SAD Trasporto Locale S.p.a. Corso Italia 13/N 39100 BOLZANO e-mail : [EMAIL PROTECTED] Tel: +390471450268 Fax: +390471450253 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: smallest dumpfiles in Windows in one step?
Andrew, Good morning everyone Thanks for your optimism. It's 5 o'clock in the afternoon, here in Berlin ;) I tried the following in Windows; but stopped mysqldump after the resulting file exceeded 1GB: mysqldump -q -C -uusername -ppassword dbname dbname.dmp Well, Unix seems to be so much better in many ways as you can do modularized things like mysqldump -q -uusername -p dbname | gzip -c dbname.gz But this won't help you too much. Here's how I solved the problem of backing up my MySQL databases on a Win2K box at home to a Linux box in my office. A mysqldump of all my databases will only produce about 50 MB of SQL, so maybe this solution isn't suitable for you. On the other hand, I have only 128 MBit upstream from my Win machine, and maybe you will have a faster network connection. I use: c:\mysql\bin mysqldump --all-databases --add-drop-table mysql -h myoffice -u username -p This will take some time, and it's most probably not the smartest thing one could do, but it works nicely. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Andrew Gould [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 24, 2003 1:47 PM Subject: smallest dumpfiles in Windows in one step? Good morning everyone, I'm running short on harddrive space on my Windows computer. What's the best way to get the smallest dump file in one step in Windows XP? On my FreeBSD computer, the following command produces an 808MB dumpfile from an 11GB database in one step: mysqldump -q -uusername -p dbname | gzip -c dbname.gz I tried the following in Windows; but stopped mysqldump after the resulting file exceeded 1GB: mysqldump -q -C -uusername -ppassword dbname dbname.dmp Thanks, Andrew Gould __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Install on WIN2k
Norris, ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Not to be impolite, but I'd say you should simply start the MySQL server :) Check system control administration services if there is a service called Mysql, and if it's running. If not, install the service (I'd suggest to use winmysqladmin.exe for this) and start it. If you're having problems like the MySQL server starts but crashes immediately after starting, you can try this in a DOS window: c:\mysql\bin mysqld --standalone --console This will give you output in the DOS window so you can see why it keeps crashing. And, of course, you can use any other server than mysqld, e.g. mysqld-max-nt.exe, to try the same. mysqld-max... has InnoDB tables switched on by default, and there is a good chance something's wrong in your setup and InnoDB can't create its tablespace etc. You will see all of this in the DOS window output, though. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Norris, Joseph [EMAIL PROTECTED] To: Mysql_List (E-mail) [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 1:55 AM Subject: Install on WIN2k Hello, I have been running mysql on windows 2000 for about 8 months. Now I have to install it on a new win2k box. I get everything installed and go to get into mysql and I am getting this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Any and all help appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [ current date ]
Elby, I want to construct a table, and in this table I have a column (date type) that if I insert blank date, the MySQL automaticly insert the current date of system. You can use a TIMESTAMP column for this. If you insert NULL (or even nothing at all) it will be set to the current date and time. If you only want to see the current date, you can use DATE_FORMAT() when you select the record(s). The TIMESTAMP will also be set to the current date and time when you UPDATE the record. For details, see: http://www.mysql.com/doc/en/DATETIME.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Elby Vaz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 6:48 PM Subject: [ current date ] hello people, I want to construct a table, and in this table I have a column (date type) that if I insert blank date, the MySQL automaticly insert the current date of system. How do I do it? Thanks, Elby. _ MSN Messenger: converse com os seus amigos online. http://messenger.msn.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: updating tables
Fabrizio, please send the table structures of table_1 and table_2 (DESCRIBE ...) so we can do more for you than just guess what the problem might be. REPLACE seems good in the first place, but if it just INSERTs then there's a problem with the (primary) keys. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Fabrizio Tivano [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 5:22 PM Subject: updating tables hello dear all, i have a problem updating tables: in my queries i try to use REPLACE but is same like INSERT command. ...i need only to refresh table_1 with new entries from table_2. any ideas? TIA fabrizio - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Install on WIN2k
Joseph, ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) Okay, if MySQL is up and alive, you should check on which port it is listening. For instance, you could (in a DOS window) do: mysqld --print-defaults to find out if there are some strange configuration settings. If the port is the problem, you could try to connect somehow like this: mysql -h localhost -p 3307 HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Norris, Joseph [EMAIL PROTECTED] To: 'Stefan Hinz, iConnect (Berlin)' [EMAIL PROTECTED]; Norris, Joseph [EMAIL PROTECTED]; Mysql_List (E-mail) [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 5:20 PM Subject: RE: Install on WIN2k Stefan, I have checked these things. To re-state the problem. I already have mysql up and running on several servers with apps accessing them from my web site. This is just a new install but it has been so long that I have forgotten what to do about this localhost situation. Thanks. -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 23, 2003 2:27 AM To: Norris, Joseph; Mysql_List (E-mail) Subject: Re: Install on WIN2k Norris, ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Not to be impolite, but I'd say you should simply start the MySQL server :) Check system control administration services if there is a service called Mysql, and if it's running. If not, install the service (I'd suggest to use winmysqladmin.exe for this) and start it. If you're having problems like the MySQL server starts but crashes immediately after starting, you can try this in a DOS window: c:\mysql\bin mysqld --standalone --console This will give you output in the DOS window so you can see why it keeps crashing. And, of course, you can use any other server than mysqld, e.g. mysqld-max-nt.exe, to try the same. mysqld-max... has InnoDB tables switched on by default, and there is a good chance something's wrong in your setup and InnoDB can't create its tablespace etc. You will see all of this in the DOS window output, though. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Norris, Joseph [EMAIL PROTECTED] To: Mysql_List (E-mail) [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 1:55 AM Subject: Install on WIN2k Hello, I have been running mysql on windows 2000 for about 8 months. Now I have to install it on a new win2k box. I get everything installed and go to get into mysql and I am getting this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Any and all help appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem (server crashes) with Version 3.23.54 on remoteconnections
Martin, Larry, Synopsis: Server crashes on remote connection I'm not a Unix guru but I experienced the same sympton on SuSE Linux 8.0/8.1, and I heard of people reporting the same of Red Hat Linux 7.x/8.x. MySQL runs stable, you can connect from localhost via socket and tcp/ip, but as soon as you try a connect from some other machine, mysqld segfaults. In all cases I heard of, the problem was some glibc version that caused all the trouble. You say you use glibc-2.2.93-5 This version # looks a bit strange to me, well ... In a SuSE Linux Newsgroup I heard that everything's okay if you use = 2.2.5-151. We use glibc-2.2.5-164 on SuSE Linux 8.1, and with our 3.23.54, we've had no problems at all. HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Martin Abell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Larry Airaghi [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 6:10 PM Subject: Problem (server crashes) with Version 3.23.54 on remoteconnections MySQL support: none Synopsis: Server crashes on remote connection Severity: serious Priority: high Category: mysql Class: Bug / Local Problem? Release: mysql-3.23.54 (Source distribution) mysql-server-3.23.54a-4 mysql-3.23.54a-4 mysql-devel-3.23.54a-4 (used rpms from updates.redhatcom) RedHat 8.0, kernel 2.4.18-19.8.0 i686 i686 i386 GNU/Linux Architecture: i686 Pentium 4, 1.9 Ghz 512 Mb memory glibc-2.2.93-5 A second machine also has the same symptoms. It is similar, except the disk drive setup is completely different. SYMPTOM In the log (below) any line beginning Tcp port: 3306 is an attempt to connect remotely (using a GUI client, or using command line mysql, or using a perl program) that fails. That's the only evidence of the attempt. (Maybe a different logging option would give more info? We used --debug=d,general,query as suggested in manual.) Queries from localhost all seem to work. For example, with perl, Apache log (with specifics edited out) says: DBI-connect(databasename:hostname.domainname.net:3306) failed: Lost connection to MySQL server during query at /path/cat_show.pl line 22. And... and other open connections (localhost) were lost at the same time and had to reconnect on next query. Using mysql at command line gives basically the same complaint. The Uptime shows that mysqld has restarted mysql in each case. Connection attempts are as user martin. User table is: +---++--+-+ | Host | User | Password | All_priv| +---++--+-+ | localhost | root | | Y | | localhost | martin | | Y | | % | martin | | Y | +---++--+-+ (All users have all privileges and a password.) ipTables is disabled. I did a shutdown and ran ISAMCHK on MYI files as suggested in manual. Any feedback would be appreciated. Martin Abell SpeedSpan = LOG tail -50 sp31.log Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument 030122 22:51:47 1 Connect root@localhost on 1 Statistics 1 Quit /usr/libexec/mysqld, Version: 3.23.54-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument 030122 23:05:12 1 Connect root@localhost on 1 Query SHOW VARIABLES LIKE 'pid_file' 1 Shutdown /usr/libexec/mysqld, Version: 3.23.54-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument 030122 23:22:54 1 Connect Access denied for user: 'root@localhost' (Using password: YES) 030122 23:23:21 2 Connect root@localhost on 030123 9:15:57 3 Connect root@localhost on 030123 9:16:02 3 Init DB mysql 3 Query show databases 3 Query show tables 3 Field List columns_priv 3 Field List db 3 Field List func 3 Field List host 3 Field List tables_priv 3 Field List user 030123 9:16:12 3 Query select * from user 030123 9:17:23 4 Connect root@localhost on 4 Statistics 4 Quit 030123 9:17:51 5 Connect root@localhost on 5 Statistics 5 Quit /usr/libexec/mysqld, Version: 3.23.54-log, started with: Tcp port:
Re: Which is the difference?
Octavian, seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT. I tried this with MyISAM and InnoDB, and it doesn't seem to be table handler dependent. Below is the output of my test. --- mysql insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.11 sec) Datensõtze: 2 Duplikate: 0 Warnungen: 0 mysql select * from a; ++--+ | id | name | ++--+ | 1 | one | | 2 | two | ++--+ 2 rows in set (0.03 sec) mysql select last_insert_id(); +--+ | last_insert_id() | +--+ |1 | +--+ 1 row in set (0.02 sec) mysql insert into a values(null, 'three'); Query OK, 1 row affected (0.05 sec) mysql select last_insert_id(); +--+ | last_insert_id() | +--+ |3 | +--+ 1 row in set (0.00 sec) -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 5:52 PM Subject: Which is the difference? Hello all, I've tried the following sql queries: mysql create table a(id int unsigned not null auto_increment primary key, name text); Query OK, 0 rows affected (0.01 sec) mysql insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select last_insert_id() from a; +--+ | last_insert_id() | +--+ |1 | |1 | +--+ 2 rows in set (0.01 sec) #I've tried a second time: mysql select last_insert_id() from a; +--+ | last_insert_id() | +--+ |1 | |1 | +--+ 2 rows in set (0.00 sec) #I've tried to put a limit clause to see the last inserted ID only once: mysql select last_insert_id() from a limit 1; +--+ | last_insert_id() | +--+ |1 | +--+ 1 row in set (0.00 sec) #I've tried a second time and the value is still 1 mysql select last_insert_id() from a limit 1; +--+ | last_insert_id() | +--+ |1 | +--+ 1 row in set (0.00 sec) #Now I've tried to find the last inserted ID by using where id is null but ... mysql select id from a where id is null; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) #The first trial was successfully, but the second not: mysql select id from a where id is null; Empty set (0.01 sec) #And from this point on, I get only empty responses. Please tell me why. And BTW, if I insert more records in a single query, how can I find the real last one? Is the only solution counting the number of new entered records, and adding this number to the number returned by the last_insert_id() function? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.1 features
Daniel, you can find a list of the new features here: http://www.mysql.com/doc/en/News-4.1.x.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Daniel Kiss [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:55 AM Subject: Re: 4.1 features On Wed, Jan 22, 2003 at 06:44:11AM +0100, Daniel Kiss wrote: Hi all, Can anyone tell where can I find some documentation about the new features released in MySQL 4.1? The features aren't all there, but here's what I've seen (and used) so far: * mixing of character sets in a table * sub-queries * spatial indexes I'm sure you'll hear more about it as the development evolves. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 3 days, processed 89,206,568 queries (311/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table joins are slow things to deal with. . .
Steve, ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); First thing that comes into mind is: You said you indexed the join fields (i.e. CaseNumber), but what about Defendants.Defendant? Okay, you did. Hmm. Next thing would be to check if the appropriate keys were used (CaseNumber, Defendant). Yes, they were used. Next thing would be to check if the optimizer chose a good join type: | Defendants | range | CaseNumber,Defendant | Defendant | 30 | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | The join types are range and eq_ref, that's fine. The number of examined rows in Defendants seems okay, too: rows | Extra | 82756 | where used | Okay, I leave this one to the gurus :-/ Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Steve Quezadas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 12:32 AM Subject: Table joins are slow things to deal with. . . Maybe I'm dumb for saying this, but sql joins seems expensive to do in terms of performance (yes, I indexed the joined fields). If I do a query search of a 2,600,000 record defendant table WITHOUT a join (SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%;). Performance is generally zippy at .53 seconds (which ends up pulling about 34,000 rows). HOWEVER, once I join this table with the much smaller [Cases] table (about 140,000 rows), performance plummets to 8.79 seconds! Quite a drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%);'. I get about 10,500 rows returned here, but man, what an increase in time. So my main question is: Is this degradation in performance to be expected with a join? What I am thinking about doing is perhaps doing two smaller queries instead of one larger one. My first query can just query the very large Defendants table without a join and create a temporary table of CaseNumbers (takes about .53 seconds). Then I can join that temporary table into the much smaller [Cases] table and then run that part of the query (which takes about 1.23 seconds). So bottom line: Results of big query with one join: 8.79 seconds Results of creating two smaller queries: 1.76 seconds (.53 seconds + 1.23 seconds). So I am thinking about optimizing the query in my PHP program to make two smaller queries instead of one bigger query. Is this a standard thing to do to increase speed? Or am I doing something stupid and inefficient in the join that I am not aware of? - Steve PS Here is some information about my tables and indexes: ([Defendant] Query WITHOUT a join) - .53 seconds. EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%; ++---+---+---+-+--+- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+---+-+--+- --+-+ | Defendants | range | Defendant | Defendant | 30 | NULL | 82756 | where used; Using temporary | ++---+---+---+-+--+- --+-+ ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+-- -+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+-- -+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL | 82756 | where used | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | Defendants.CaseNumber | 1 | where used | +++--+-+-+-- -+---++ Table and index information Cases Table - 140,000 records +---+---+--+-+-+---+ | Field
Re: Indian Languages Support
Dear Velmani, what do you mean with Indian? AFAIK there are 36 languages spoken in India. Do you mean English? ;-) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: N-Velmani [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:38 AM Subject: Indian Languages Support Hi All, Could any one tell me how to get the Indian Languages Support in MySQL??? Regards, N.Velmani - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is the difference?
Benjamin, Yes, that is the documented behaviour: http://www.mysql.com/doc/en/Miscellaneous_functions.html (If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row.) Gee, I just copied this sentence into my new (German) book (short reference of MySQL) about two weeks ago blush Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] Cc: Octavian Rasnita [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:29 PM Subject: Re: Which is the difference? Hello. On Wed 2003-01-22 at 08:53:23 +0100, [EMAIL PROTECTED] wrote: seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT. Yes, that is the documented behaviour: http://www.mysql.com/doc/en/Miscellaneous_functions.html Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Joins are damn slow. . .
Steve, SQL joins are damn slow! I have a simple database of about 37,000 records in [Court Cases] and 2,000,000 records in [Defendants] (soon to be more). Please post the structures of your 2 tables (at least, the relevant parts), and the join query that gets so slow. Otherwise, we can only guess. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Steve Quezadas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 10:30 AM Subject: Joins are damn slow. . . Maybe I'm a bit naive in saying this but. . . SQL joins are damn slow! I have a simple database of about 37,000 records in [Court Cases] and 2,000,000 records in [Defendants] (soon to be more). When I do a search on [Defendants] (the big table) alone, a saerch can go in like .7 seconds. But if I do a search on [Defendants] joined to [Cases], the search jumps to about 5 seconds. (yes, I indexed the joining fields and the search terms). This bites. . . However. I notice that if I do two separate searches it goes quicker (about 2.5 seconds combined). I can do a criteria search on defendants and then put all the resulting case numbers in a temporary table. Then do a join of that temporary table to the much smaller Cases table and do a search on that. I get the same results, and the query time is halved. Ummm, is there any reason why I shouldn't do this? (Other than the inelegance of running two queries instaed of one) Do people do stuff like this for performance reasons? - Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copy database question
Tom, I have Database 1, table userdata, field username I need to copy the above mentioned data to: Database2, Table chat_data, field nick in phpMyAdmin 2.3.3pl1 you can accomplish this as follows: 1. Choose Database 1 in the left frame. 2. Click table userdata in the left frame. 3. Click the Operations tab in the right frame. 4. Choose Copy table to (database.table) in the right frame. 5. Choose Database 2 (must exist), fill in chat_data, choose Structure and data. Klick Go. 6. Choose Database 2 and then chat_data in the left frame. 7. Click the Structure tab in the right frame. 8. For column username, click Change, fill in nick, click Save. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Tom Nickels [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 1:23 PM Subject: Copy database question Hi everybody, I am new on mysql. I use phpadmin for adminstration. My problem: I have Database 1, table userdata, field username I need to copy the above mentioned data to: Database2, Table chat_data, field nick I have no clue how to do it! Can anybody tell me the string? Thanks for help! Tom - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Whats between a varchar text ??
Craig,
I want to have users give a description in a form and varchar(255)
wont give
me enough space so I thought I would use text(650) but it seems the
'text'
type wont take the limit of (650), it simply offers the user (65535):
way
too much.
In MySQL, you have TINYTEXT (2**8 = 256, like (VAR)CHAR), TEXT (2**16 =
65536), MEDIUMTEXT (2**24) and LONGTEXT (2**32). Unlike CHAR/VARCHAR,
you cannot restrict the length of TEXT column types.
Now for the good news: All TEXT types will store data dynamically, i.e.
let's say you store 10 bytes in a TEXT column, then this will require 12
bytes (2 bytes to store the actual length), not 65536 bytes.
If you want to restrict the amount of text your users can enter, you
will have to do this in your application. Just to point out what I mean,
here's a rather radical method using PHP:
$user_description = substr($user_description,0,650);
mysql_query(INSERT INTO userdata (description) VALUES
('$user_description'));
Alternatively, you could use:
mysql_query(INSERT INTO userdata (description) VALUES
(LEFT('$user_description',650)));
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Craig melia [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 6:27 PM
Subject: Whats between a varchar text ??
Hi
I am building my 1st app in php with Mysql and I hope you can help.
I want to have users give a description in a form and varchar(255)
wont give
me enough space so I thought I would use text(650) but it seems the
'text'
type wont take the limit of (650), it simply offers the user (65535):
way
too much.
Is there anything I can do to set a column type to recieve just 650
characters of text, or something similar?
Craig
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql and Database Link
Frank, Yep Jermey are right what he says - Anyone know about using Database link in Mysql??? This is not built in. You will have to do this within your application. You can also use some of the MySQL helper apps for this, like mysqldump in combination with mysql (the command line tool), like this: laptop mysqldump myDB | mysql -h myserver myDB Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: MySQL [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 7:36 PM Subject: Re: Mysql and Database Link Yep Jermey are right what he says - Anyone know about using Database link in Mysql??? Regards Frank - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Egor Egorov [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 20, 2003 4:35 PM Subject: Re: Mysql and Database Link On Mon, Jan 20, 2003 at 03:49:00PM +0200, Egor Egorov wrote: On Monday 20 January 2003 06:36, MySQL wrote: I'm woundering if Mysql can use Database link, like Oracle can??? Eg. If i have some tables at my labtop, and want to make the same table(s) at my server with this command: create table test as select * from test@database_link (This is how you do it on Oracle), but how do I do this in Mysql You can use CREATE .. SELECT statement: http://www.mysql.com/doc/en/CREATE_TABLE.html but you must create indexes manually. I think you misunderstand. Those are not really the same. Links in Oracle (as described to me) allow you to connect multiple servers together--so you can join between tables on different servers, for example. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 1 days, processed 46,297,176 queries (320/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: simple windows client for mysql
Dave, I am looking for an easy to use, easy to install mysql client which would enable users to query my database. What about a web browser? It's most probably installed on all laptops. You could use some simple PHP script (not as mighty as phpMyAdmin, I would suggest) to convert SQL queries into HTML replies. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: David Driscoll [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 12:53 AM Subject: simple windows client for mysql I have a fairly basic mysql database running on unix. Now I need to make this data available to other people. These would be various engineers and administrators using every version of windows from 95 (japanese version) to XP all using laptops connected to our internal network. I am looking for an easy to use, easy to install mysql client which would enable users to query my database. It seems that installing mysql server for windows on each machine would do this but it seems like using a shovel to swat a fly. Suggestions? Cheap is good too. Thanks Dave Driscoll - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Text field question
Robyn,
Could anyone please tell me how I write an SQL statement to seperate
text from one field into multiple fields based on a aprticular
character
(i.e. tab/comma/space). For example, I have a text field containing:
Text=Robyn Bailey Brisbane
I understand you have all of this in just ONE text field (that's not a
good idea, though).
You could use the MySQL string functions to separate substrings, but
that's not a good idea, either, because SQL is not designed for tasks
like this.
You should rather use your favourite programming language (mine is PHP
:) and do something like this (the example is with PHP, but it will work
the same way in PERL etc.):
$sql = SELECT mytext FROM myentries;
$res = mysql_query($sql);
$i = 0;
while($row = mysql_fetch_array($res)) {
$arr[$i] = explode('=', $row[0]);
$i++;
}
When the while loop ends, you have the split values in $arr, and you can
cycle through $arr to create your report.
Note that this is untested code, just to give you an idea what to do.
BTW: After splitting up mytext into pieces ($arr) you should consider
inserting the split values into a new table with a better table design.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Robyn Bailey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, January 20, 2003 2:08 AM
Subject: FW: Text field question
Could anyone please tell me how I write an SQL statement to seperate
text from one field into multiple fields based on a aprticular
character
(i.e. tab/comma/space). For example, I have a text field containing:
Text=Robyn Bailey Brisbane
I want to seperate this (per query) into 3 fields so that I can sort
and
count the fields: Name=Robyn Surname=Bailey Location=Brisbane
I dont want it permanently just per query (for a report).
Thanks in advance
Robyn Bailey, CISSP
This email and any attachments are subject to copyright. They may also
contain confidential information. This email and any attachments may
not
be distributed, reproduced, copied, stored or transmitted in any form
or
by any means, without the prior written consent of Bridge Point
Communications Pty Ltd ABN 29 083 424 668. Any personal information in
this email must be handled in accordance with the Privacy Act 1988
(Cth). Emails may be interfered with, may contain computer viruses or
other defects and may not be successfully replicated on other systems.
Bridge Point Communications Pty Ltd gives no warranties in relation to
these matters. If you have any doubts about the authenticity of an
email
purportedly sent by us, please contact Bridge Point Communications Pty
Ltd immediately.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query problem
Jon, Where can I look to see the Warnings? MySQL 4.1 will come with an enhanced error/warnings reporting system where you can use SHOW ERRORS and SHOW WARNINGS (see http://www.mysql.com/doc/en/SHOW_WARNINGS.html for details). If you don't have 4.1 (as most of us) you can tell the server to log warnings, too, by starting it with the --warnings option (or write warnings without quotes in the mysqld section of your my.cnf / my.ini and restart the server). You can see warnings in the MySQL error file. Under Unix, this file is called hostname.err (where hostname is the name of your machine), under Windows it's mysql.err. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jon Miller [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 20, 2003 12:49 PM Subject: query problem I ran a query as follows: Insert Into db_test.tbl_dbaddr (Client, Contact, Addr, OfcPhone) SELECT Client AS Client, Business AS Contact, Addr AS Addr, OfcPhone AS OfcPhone From AddressBook.tbl_addr; I got the following Records: 8989 Duplicates: 16 Warnings:394 Where can I look to see the Warnings? Also the information in the Contact and Addr field is wrong. How do I delete the entire table. I believe I cannot use Drop. Using Select * delete from tbl_dbaddr yields a syntax error. Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to generate my.cnf based on existing configuration?
Jannie, /etc/my.cnf. But there is no my.cnf file at that directory. So my question is:How to generate my.cnf based on existing configuration? You can see the server options for the MySQL server by issuing mysqld --print-defaults The output is pretty ugly, but it will show you all settings for the server. Next step, you can put those options into /etc/my.cnf, somehow like this: Let's say, the output starts like this: mysqld would have been started with the following arguments: --basedir=c:/mysql --datadir=c:/mysql/data --tmpdir=c:/mysql/temp Then you put this in my.cnf: [mysqld] basedir=c:/mysql datadir=c:/mysql/data tmpdir=c:/mysql/temp Restarting the MySQL server, it will start with exactly the options as before. Next step, you can experiment with different settings ... Read more: http://www.mysql.com/doc/en/Command-line_options.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jannie Qu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 20, 2003 9:58 PM Subject: How to generate my.cnf based on existing configuration? sql, query Hi, all, I started to support on an old mysql production database on SunOS 5.8 and mysql version 3.23.47 with only MyISAM type of tables. I take a look on /etc/init.d/mysql file, and in this file, it will refer /etc/my.cnf. But there is no my.cnf file at that directory. I cannot shutdown the database right now in order to test the /etc/init.d/mysql and I am afraid without my.cnf in the specified directory will eventually cause this mysql scrip fail. So my question is:How to generate my.cnf based on existing configuration? Thank you and regards, Jannie _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it normal?
Octavian, mysql create table test(id float); mysql insert into test values(1.123), (3.1495); mysql select * from test where id=1.123; Empty set (0.00 sec) Shouldn't this last query show me the record that has the id=1.123? Well ... 1.123 is greater than 1.123, see: mysql select id from octavian where id 1.123; ++ | id | ++ | 1.123 | | 3.1495 | ++ 2 rows in set (0.00 sec) Now here's why. Floats are simply not precise: mysql select id*1 from octavian where id 1.123; +-+ | id*1| +-+ | 11230.000257492 | -- | 31494.998931885 | +-+ 2 rows in set (0.00 sec) What query should I use to do this? I leave this one to Paul or one of the other gurus. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Monday, January 20, 2003 9:34 AM Subject: Is it normal? Hi all, I've tried the following SQL queries: mysql create table test(id float); Query OK, 0 rows affected (0.00 sec) mysql insert into test values(1.123), (3.1495); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from test where id=1.123; Empty set (0.00 sec) Shouldn't this last query show me the record that has the id=1.123? What query should I use to do this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: New Beginner to List
Doll, My web server only accommodates mysql. I wouldn't say only, though :) Now, what do I need to do get this table operational for my website? I wanted to create a search program for users. Use PHP. It's easy to learn and works smoothly with MySQL. You will find lots of examples how to transform your MySQL data into HTML in the online documentation: http://www.php.net/docs.php Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Doll Hargrove [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 20, 2003 11:48 PM Subject: New Beginner to List List Members: My web server only accommodates mysql. I've started to trancribe an 1866 census record with 8 columns of information. The file was created in excel and I used an csv extension (delimited) to import this information into the mysql data table. The tables is completed. Now, what do I need to do get this table operational for my website? I wanted to create a search program for users. Your help will be appreciated! -- Researching Macon County Alabama Surnames: Bryant, Harris, Thomas, Webb, Wilson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: hot to obtain metadata on tables
Nedim, I am new to MySql. I need to obtain information about tables including the names and types of each column, the primary keys etc. SHOW TABLE STATUS; To see only tables starting with t: SHOW TABLE STATUS LIKE 't%'; http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: M.Nedim Alpdemir [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 6:32 PM Subject: hot to obtain metadata on tables Hi all, I am new to MySql. I need to obtain information about tables including the names and types of each column, the primary keys etc. I understand that the describe table_name command does this. But are there some system tables (similar to MS SQL server or IBM DB2) that I can query for more detailed information? Can I obtain this kind of information by a single select statement (using show tables and describe etc.). Any help will be appreciated? Thanks in advance? Nedim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql
Hi, but the commnads don't work unless i have ./ infront of the command for example: mysqladmin won't work unless its written ./mysqladmin. If you're logged in as root, log out and log in as another Linux user. (I'm a Windoze fan, but AFAIK root must type ./ for command for security reasons.) HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: M A [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 7:48 PM Subject: mysql HI i am new to mysql.. i have just installed the source code under /user/local/mysql but the commnads don't work unless i have ./ infront of the command for example: mysqladmin won't work unless its written ./mysqladmin. please let me know if i am doing something wrong Thanks _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A mysql php question
Ryan,
this line is not correct:
$line = mysql_fetch_array($result, MYSQL_ASSOC);
Instead, you will need to loop through the result set, like:
while($row = mysql_fetch_array($result)) {
for($i=0; $i mysql_num_fields($result); $i++) {
echo $row[$i];
}
}
You can find more info here: http://www.php.net/manual/en/ref.mysql.php
I leave formatting the output up to you ;-)
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Ryan McDougall [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Sunday, January 19, 2003 6:47 PM
Subject: A mysql php question
Hello everyone,
I'm not sure if this is the proper place to be asking this question as
I
believe it is more of a question on how to traverse through php arrays
and such
but it is using a mysql_query function. So if this not the place
pleace don't
read any further and ignore and/or delete this message.
So I am new to mysql AND php, with a background in Oracle SQL. Anyway
I am just
starting out writing a web application for a DB of the collection of
videos,
games, and cds of me and my wife's. So I want to start out with just
making a
table with the field names as the header rows and then the data
underneath the
corresponding field name/column. The current code (last part of this
message)
only prints out the right column headers/field names and then puts the
right
information under it but it only gives me the first record, I want all
records.
*example of current code output*
IdNum FName LName
1 Ryan McDougall
This is my code so far, be prepared to laugh as this is pretty bad
code I'm
sure (I just don't want to be held liable for any injuries from
falling out of
your chairs :-P ):
html
headtitleMy PHP test file/title/head
body
?php
$link = mysql_connect (localhost, username, password)
or die (Could not connect br\n);
print (Connected successfullybr\n);
mysql_select_db(DBname) or die(Could not select database);
$query = SELECT * FROM Who;
$result = mysql_query ($query)
or die (Query failed);
$line = mysql_fetch_array($result, MYSQL_ASSOC);
print table\n;
print \ttr\n;
foreach ($line as $key = $value) {
print \t\ttd$key/td\n;
}
print \t/tr\n;
print \ttr\n;
foreach ($line as $col_value) {
print \t\ttd$col_value/td\n;
}
print \t/tr\n;
print /table\n;
mysql_free_result ($result);
mysql_close ($link);
?
/body
/html
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Substraction
Brian, create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. Trying to confirm this with MySQL 4.0.7 on a Win2K box: mysql create table test(id int unsigned); Query OK, 0 rows affected (0.56 sec) mysql insert into test values(10); Query OK, 1 row affected (0.08 sec) mysql select 15 - id from test; +-+ | 15 - id | +-+ | 5 | +-+ 1 row in set (0.06 sec) Now, once again with phpMyAdmin 2.3.3pl1: SELECT 15 - id FROM test LIMIT 0, 30 15 - id 5 But with the following statement (same with phpMyAdmin): mysql select 5 - id from test; +--+ | 5 - id | +--+ | 18446744073709551611 | +--+ 1 row in set (0.00 sec) Check the 1 in your 15. Maybe there's something wrong. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Brian Lindner [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Sunday, January 19, 2003 10:28 PM Subject: Re: Substraction Octavian, Sunday, January 19, 2003, 7:31:57 AM, you wrote: Hi all, I've tried the following query: create table test(id int unsigned); insert into test values(10); select 15 - id from test; The result is 18446744073709551611 instead of -5. I ran this.. and it worked for me MySql 4.0.7 on Linux... ran it through phpMyAdmin Am I doing something wrong? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL PHP
Ryan, Eric, Did u compile PHP with mysql support enabled?as in did your configure line have something like this ./configure --with-mysql=/path/to/mysql? You can test this by writing a file (named whatever you want, I call it phpinfo.php) with one single line like this: ?=phpinfo()? Check in your browser, like http://localhost/phpinfo.php, and it should tell you if MySQL support is enabled. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Kamara Eric R-M [EMAIL PROTECTED] To: Ryan McDougall [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED]; RedHat 8.0 Psyche [EMAIL PROTECTED] Sent: Saturday, January 18, 2003 5:26 PM Subject: Re: MySQL PHP Hello Ryan, Did u compile PHP with mysql support enabled?as in did your configure line have something like this ./configure --with-mysql=/path/to/mysql? Regards, Eric === Kamara Eric Rukidi Mpuuga Computer Frontiers International Plot 32 Lumumba Avenue P.O Box 12510,Kampala Tel :256-41-340417/71 Email:[EMAIL PROTECTED] Web :http://www.cfi.co.ug On Sat, 18 Jan 2003, Ryan McDougall wrote: hello all, I'm posting on 2 lists because I'm not sure were the problem is, so I'm hoping that some one can help me out. I'm running a stock RH8 machine running amongst other things MySQL 3.23.52-3, Apache 2.0, PHP 4.2.2-8.0.5 When I try a simple php script that is this (sensitive information changed to protect me): html headtitleMy PHP test file/title/head body ?php $link = mysql_connect (localhost, user, password) or die (Could not connect); print (Connected successfully); $query = SELECT * FROM who; $result = mysql_db_query (mcdougrsMedia, $query) or die (Query failed); ? /body /html The page produces the following output: Fatal error: Call to undefined function: mysql_connect() in /home/username/public_html/testing.php on line 5 Can you guys see what is wrong... or if there is other information that is needed that isn't there please let me know and I will post whatever information is needed to fix it. TIA, Ryan __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PRIMARY KEY
Rob, If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, How does any application ask for the PRIMARY KEY? Is this an ODBC call, or are their command line and built-in functions? I couldn't find this in the documentation. Primarily, this is a feature for ODBC applications like MS Access. I don't know of any other app that really longs to have a primary key for each table, but if there were such apps, MySQL would act as described in the manual (with or without ODBC). Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Rob Pecherer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 18, 2003 6:38 PM Subject: PRIMARY KEY According to the MySQL documentation (Section 6.5.3, CREATE TABLE syntax): If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY. How does any application ask for the PRIMARY KEY? Is this an ODBC call, or are their command line and built-in functions? I couldn't find this in the documentation. Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PRIMARY KEY
Rob, I'm not questioning what MySQL does, only how. MySQL does not act without a request, so my question is (now), How do you ask MySQL what the PRIMARY KEY of a table is? SHOW KEYS FROM tbl In an application, next thing you would do is check what the value of 'Key_name' is (which is the column name of the returned result set), and if it is 'PRIMARY', then you have the PK. I don't know how (ODBC) apps like MS Access would ask MySQL for a PK column of a table, but MySQL must have a built-in mechanism to answer such a question with something else but the value 'PRIMARY'. Maybe some guru on the list has a more precise information?! P.S. I lived/worked in Berlin 1984-85 developing an RDBMS for Siemens on a joint project w. Intel. It never reached the market. Great City!! I miss alt bier. :-) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Rob Pecherer [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] Sent: Saturday, January 18, 2003 7:57 PM Subject: RE: PRIMARY KEY Greetings Stefan, and thanks. I'm not questioning what MySQL does, only how. MySQL does not act without a request, so my question is (now), How do you ask MySQL what the PRIMARY KEY of a table is? Thanks, Rob P.S. I lived/worked in Berlin 1984-85 developing an RDBMS for Siemens on a joint project w. Intel. It never reached the market. Great City!! I miss alt bier. -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 18, 2003 11:35 AM To: Rob Pecherer; [EMAIL PROTECTED] Subject: Re: PRIMARY KEY If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, How does any application ask for the PRIMARY KEY? Is this an ODBC call, or are their command line and built-in functions? I couldn't find this in the documentation. Primarily, this is a feature for ODBC applications like MS Access. I don't know of any other app that really longs to have a primary key for each table, but if there were such apps, MySQL would act as described in the manual (with or without ODBC). Regards, -- Stefan Hinz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Authenticating users
Cesar,
your PHP problem is here:
$query1 = SELECT * FROM r2k_admin WHERE adminuser = $user;
It's supposed to be WHERE adminuser = '$user' , because $user is a
string.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Cesar Aracena [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, January 16, 2003 5:13 PM
Subject: Authenticating users
Hi all,
I have this admin site where the administrators can add new ones of
their kind (admin level) and now I'm trying to make a query so no
usernames are duplicated in MySQL.
The query goes like this:
$query1 = SELECT * FROM r2k_admin WHERE adminuser = $user;
$result1 = mysql_query($query1);
$num_rows = mysql_num_rows($result1);
$errors=0;
if ($num_rows == 0)
{
$query = INSERT INTO r2k_admin (adminid, adminname, adminemail,
adminuser, adminpassword) VALUES (NULL, '$name', '$email', '$user',
password('$pass'));
$result = mysql_query($query) or die(mysql_errno());
}
else if ($num_rows 0)
{
$errors++;
}
But the PHP part of the program tells me that I have an error in my
MySQL syntax in the line where $num_rows is declared, right after the
first query. Anyone knows what's this all about?
Thanks in advance,
Cesar L. Aracena
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(0299) 156-356688
Neuquén (8300) Capital
Argentina
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Password problem
Neil, GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I don't know if you have any other entries in the mysql.user / mysql.db tables. If so, there could be another entry for 'newsletter'@'something_else_but_not_%'. Whatever the reason - it's always a good idea to specify user names the classical MySQL way, i. e. 'user'@'machine', and not - as in most other DBMS - only as 'user'. What the manual says about it, is: The simple form user is a synonym for user@%. Details: http://www.mysql.com/doc/en/GRANT.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Neil Aggarwal [EMAIL PROTECTED] To: Mysql list [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 6:00 PM Subject: Password problem Hello: As the root user, I created a new database and user account: CREATE DATABASE newsletter; GRANT ALL PRIVILEGES ON newsletter.* TO newsletter IDENTIFIED BY 'testPass'; FLUSH PRIVILEGES; I then tried to access the databse via the command-line client: /usr/local/mysql/bin/mysql -u newsletter -ptestPass newsletter And I get this error: ERROR 1045: Access denied for user: 'newsletter@localhost' (Using password: YES) I checked that mysql has it set-up correctly in the user and db tables by logging in as the root account. select * from user give me this line: | % | newsletter | 61fa73f50740c213 | N | N | N | N | N | N | N | N | N| N | N | N | N | N select * from db gives me this line: | % | newsletter | newsletter | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Any ideas? Thanks, Neil. -- Neil Aggarwal JAMM Consulting, Inc.(972) 612-6056, http://www.JAMMConsulting.com Custom Internet DevelopmentWebsites, Ecommerce, Java, databases - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Populating one table with data from another
Michael, I have a table, products containing a 944 rows. One of the fields is fgNumber, I have another table, categoryRelatedToProducts which also has an fgNumber field and currently has no data. I'd like to add all 944 fgNumbers to the categoryRelatedToProducts table. INSERT INTO categoryRelatedToProducts /* great table name ;-) */ (categoryRelatedToProducts.fgNumber) SELECT products.fgNumber FROM products; is what you might want to do. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Michael Knauf/Niles [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 6:01 PM Subject: Populating one table with data from another Ok, this has got to be easy, but I'm not getting it right... I have a table, products containing a 944 rows. One of the fields is fgNumber, I have another table, categoryRelatedToProducts which also has an fgNumber field and currently has no data. I'd like to add all 944 fgNumbers to the categoryRelatedToProducts table. Can I do this with one sql statement? Michael - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to insert entire text file in a table column
Haisam, I want to insert into content an entire text document. How do I do that without puting the entire content into a variable, let's say in perl, and then writing the INSERT statement. In MySQL, you have the LOAD_FILE() function to do this: http://www.mysql.com/doc/en/String_functions.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Haisam K. Ido [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 11:41 PM Subject: How to insert entire text file in a table column I have the following table, CREATE TABLE cp ( id INT(11) NOT NULL auto_increment, fileVARCHAR(128) NOT NULL, content LONGTEXT, UNIQUE KEY keyword(id,file), PRIMARY KEY (id) ) TYPE=INNODB; I want to insert into content an entire text document. How do I do that without puting the entire content into a variable, let's say in perl, and then writing the INSERT statement. Is there a way to give the path to a file and for mysql to use that path to insert the content? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL y claves foraneas
Vicente, the language on this list is English. I will try to answer your question, anyway. The patch you're speaking of is no patch, but another table handler in MySQL. Native tables are MyISAM, and MySQL has a number of alternative tables it can handle. One of them is InnoDB. In MySQL, you can use MyISAM and InnoDB together. If you need features like foreign keys, the MyISAM table handler cannot handle this (this is planned for the future, anyway). Momentarily, you will have to use InnoDB for features like this. I am not informed which operating system you use, but if it's Windows, you will find a number of MySQL servers in the bin/ directory (e.g. c:\mysql\bin). If you start mysqld, you will have no InnoDB tables activated, and thus no foreign key support. To enable InnoDB, you will have to start mysql-max (or, on NT systems, mysqld-max-nt). Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 8:40 AM Subject: MySQL y claves foraneas Hola, Tengo la versión 3.23.54 instalada sobre Windows. He esta leyendo algunos documentos acerca de el soporte de claves foraneas y he visto que para ello dicha versión necesita un patch llamado MySQL-MAX. En la sección de downloads he visto que existe la versión 3.23.54-MAX, ¿es este el patch necesario? En tal caso he visto que no es ningun archivo instalable, sino que en su interior hay carpetas llamadas como lib, include, bin... ¿Qué debo hacer con ellas, copiarlas sobre la carpeta de MySQL y rearrancar el servidor?. Gracias ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Archive and retrieval in MySQL
Ahmed, 1. How can i trigger a database archive when a certain data base limit is reached in a table. Ex: when 1,00,000 records is reached, archive to file and clean table. You will have to wait for MySQL 5.0. This version will have SQL triggers. 2. How can i restore multiple files into database ? I am not sure if I get you right. If you mean how to restore backups, it's easy with MySQL: 1. Make the backups using mysqldump, e.g. mysqldump --all-databases backupfile 2. Restore what's in backupfile: mysql backupfile Have a look at http://www.mysql.com/doc/en/mysqldump.html for details. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Ahmed S K Anis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 2:00 PM Subject: Archive and retrieval in MySQL HI, I have a few questions related to DB archving / restore capability of MySQL. Most of them are design issues but have a lot to do with what MySQL provides. PLease share your view on them. 1. How can i trigger a database archive when a certain data base limit is reached in a table. Ex: when 1,00,000 records is reached, archive to file and clean table. 2. How can i restore multiple files into database ? 3.When i try to view data through my application, how can i trigger the restore the data from Archive file on demand ? Regards Anis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: MySQL 4.0.7-gamma: Bugs affecting the privilege system
Victoria, Thank you for bug reports, both bugs are fixed. That's fast :-)) What about the LOCAL bug (or feature)? I.e. you can start mysqld --local-infile=1, or you can start MySQL Monitor like mysql --local-infile=1, or even both, and it won't help. You cannot use load data LOCAL. Furthermore, client tools report that the option local-infile is unrecognized, but the manual says it's a valid option: For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option -- local-infile[=1], or disabled with --local-infile=0. Well, here's the output: c:\mysql\binmysql --local-infile=1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.7-gamma-max-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql USE test; Database changed mysql LOAD DATA LOCAL INFILE 'f:/import.txt' INTO TABLE t; ERROR 1148: Das used Kommando ist mit dieser MySQL Version nicht erlaubt Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 2:55 PM Subject: re: Re: MySQL 4.0.7-gamma: Bugs affecting the privilege system On Wednesday 08 January 2003 22:32, Stefan Hinz iConnect \(Berlin\) wrote: Have you tried to reproduce them? Bug #1: GRANT USAGE doesn't seem to create a user: mysql GRANT USAGE ON *.* TO 'foo'@'foohost'; Query OK, 0 rows affected (0.26 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; Empty set (0.03 sec) mysql SELECT Host, User, Password FROM mysql.user WHERE User = 'foo'; +-+--+--+ | Host| User | Password | +-+--+--+ | foohost | foo | | +-+--+--+ 1 row in set (0.04 sec) Bug #2: SHOW GRANTS shows wrong GRANT statement: mysql GRANT USAGE ON `footable`.* TO 'foo'@'foohost' IDENTIFIED BY 'foobar' - WITH GRANT OPTION; Query OK, 0 rows affected (0.03 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; +--- | Grants for foo@foohost +--- | GRANT USAGE ON *.* TO 'foo'@'foohost' IDENTIFIED BY PASSWORD '4655c05b05f1 | GRANT ON `footable`.* TO 'foo'@'foohost' WITH GRANT OPTION +--- 2 rows in set (0.00 sec) Yes, I can confirm both bugs you encountered. My settings: Server version: 4.0.7-gamma-max-nt-log OS: Win2K SP2 Maybe worth mentioning, a database 'footable' doesn't exist on my machine. Double-checking this, I found out it doesn't matter if a database exists or not. Bug #2 happens with database 'test', too. Regards, Thank you for bug reports, both bugs are fixed. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading to version 4.
Maximo, My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. PHP's mysql_connect() is just a wrapper around the C API mysql_connect. As the manual says: This function is deprecated. It is preferable to use mysql_real_connect() instead. I assume that PHP 4.2.3 is already using mysql_real_connect(). If this is not the case, I'm quite sure (if no one here disagrees ;) that PHP 5 will. And I'm really sure that it will always be called mysql_connect() in PHP, just to keep things easy. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Maximo Migliari [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 7:45 PM Subject: Upgrading to version 4. In the MySQL manual, under http://www.mysql.com/doc/en/Upgrading-from-3.23.html it says: --- The old C API functions mysql_drop_db, mysql_create_db, and mysql_connect are not supported anymore, unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. Instead of doing this, it is preferable to change the client to use the new 4.0 API. - My company's website is running version 3.23.53a, and the main client that uses MySQL is PHP 4.2.3. Will mysql_connect() no longer work in PHP? Although this manual entry is talking about the C API functions, I assume PHP uses these to communicated with MySQL. Perhaps I might be wrong, but I would like to just double check. Thanks, Maximo. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Removing users
Ed, That would work but is there no way to completely remove their record? With REVOKE, you can partially or totally revoke privileges from users. What you can't do with REVOKE (in MySQL) is to erase a user completely. For this, you will have to DELETE FROM mysql.user WHERE User = 'user2bremoved'. You can even use this as an alternative way to remove users. You need two statements for this, just in case ... DELETE FROM mysql.user WHERE User = 'user2bremoved'; DELETE FROM mysql.db WHERE User = 'user2bremoved'; Opposed to GRANT and REVOKE DELETE will not cause the server to notice the privilege changes. You need another statement for this: FLUSH PRIVILEGES; This will force the server to reload the grant tables, and thus, the privileges. Details: http://www.mysql.com/doc/en/User_Account_Management.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: [EMAIL PROTECTED] To: Michael T. Babcock [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 7:10 PM Subject: Re: Removing users That would work but is there no way to completely remove their record? Ed On Mon, 13 Jan 2003, Michael T. Babcock wrote: [EMAIL PROTECTED] wrote: I've read the manual concerning REVOKE commands but how would I completely remove a user inside MySQL? I can't imagine that it's as easy as removing them from the user table. If you remove them (or blank their password) in the user table, they'll have no way of logging in, since MySQL won't be able to authenticate them. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL LOAD_FILE problems
Herbert, Using MySQL, I have a problem where I am attempting to upload a 16.4MB file into a BLOB field, but the insert fails with a ERROR 1030: Got error 139 from table handler BLOB can store data up to 64 KB only. Use MEDIUMBLOB (up to 16 MB) or LONGBLOB (up to 4 GB). For MEDIUMBLOB, be aware that the default communication buffer size is 1 MB only. To increase it, put set-variable = max_allowed_packet=16M in your my.cnf / my.ini file and restart the MySQL server. For LONGBLOB, be aware that the communication buffer can only be set to 16 MB maximum. INSERTing data bigger than 16 MB means you will have to use UPDATE to insert the data in pieces. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Herbert Kunzmann [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 9:03 AM Subject: MySQL LOAD_FILE problems Using MySQL, I have a problem where I am attempting to upload a 16.4MB file into a BLOB field, but the insert fails with a ERROR 1030: Got error 139 from table handler and after trying again I get ERROR 1062: Duplicate entry 'bla' for key 1 BUT the entry was never made. I need to repair the table in order to continue on. Can anyone give me a hint on what to do ? I remain somewhat lost... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: install problem... (permissions?)
Christopher, 030111 13:41:11 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) Did you run the install_db script? If not, MySQL can't find the grant tables. See http://www.mysql.com/doc/en/Post-installation.html for details. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Christopher Bergeron [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 7:29 PM Subject: install problem... (permissions?) Hey guys, I'm having a little problem with my new mysql installation. I built from source, and everything built find and installed okay (I think). Here's the problem: when I start safe_mysqld it just ends. Here's the snippet from my error log: /usr/local/libexec/mysqld: File './proto2-bin.1' not found (Errcode: 13) 030111 13:41:11 Could not use proto2-bin for logging (error 13) 030111 13:41:11 /usr/local/libexec/mysqld: Can't create/write to file '/usr/local/var/proto2.pid' (Errcode: 13) 030111 13:41:11 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 030111 13:41:11 /usr/local/libexec/mysqld: Error on delete of '/usr/local/var/proto2.pid' (Errcode: 13) 030111 13:41:11 mysqld ended Is there an ownership or permission problem here? Can anyone help me out here? Much thanks in advance, CB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
Frank, c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Doesn't this assume that I have command line access? Since I am in a hosted (ISP) environment, I am trying to do this through phpMyAdmin. Can I not do this through phpMyAdmin? Right. It means you need command line access on the QA host, which I assumed was a local machine. Of course, it's also possible to use phpMyAdmin for this: 1. Choose the database you want the imported tables to go. 2. Click the SQL tab. 3. Click Browse to choose my_database_export.sql. 4. Click Okay. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 10, 2003 8:09 PM Subject: Re: Copying MySql database to others Stefan, Let's say, you exported database my_database, and you saved the export file as c:\mysql\my_database_export.sql on your QA machine. You can import it with this command (assuming that database my_database exists on your QA machine, but has no tables in it): c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Doesn't this assume that I have command line access? Since I am in a hosted (ISP) environment, I am trying to do this through phpMyAdmin. Can I not do this through phpMyAdmin? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: file permissions problem on ALTER TABLE
Dirkjan,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I
encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table.
Last 3.23 I used was 3.23.49, and I didn't have an ALTER TABLE problem,
neither on Win98, Win2K, nor Linux. I did have problems like the ones
you describe with 4.0.1, 4.0.3 and 4.0.4 under Win98 and Win2K. Using
4.0.7 now und Win98 and Win2K, the ALTER TABLE problems have gone.
Reporting this problem to the list, I heard strange advices like switch
lower_case_table_names to 0. I believe it's a MySQL on Win bug with the
4.0.x versions mentioned above, and it might also affect 3.23 version
after 3.23.49.
My advice is: Update, MySQL 4.0.7 is real funky :)
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Dirkjan Ochtman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, January 10, 2003 7:40 PM
Subject: Bug: file permissions problem on ALTER TABLE
Hi there,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I
encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table. I kept
encountering
this problem (trying to go from VARCHAR(6) to CHAR(6) for an index
field),
but it would only work after I restarted the mysqld-max-nt.
Immediately
after restarting the server, it worked, but then after a few minutes
I
tried
to ALTER another field in the table, and it had the same problem. So
I
restarted the server again, it went through, but the problem
persisted. I
checked the file permissions, but everyone has full control to the
files.
The table now looks like this:
CREATE TABLE postcodes (
id mediumint(8) unsigned NOT NULL auto_increment,
postcode char(6) NOT NULL default '',
plaats smallint(5) unsigned NOT NULL default '0',
straat mediumint(8) unsigned NOT NULL default '0',
low mediumint(5) unsigned NOT NULL default '0',
high mediumint(5) unsigned NOT NULL default '0',
parity enum('0','1','2') NOT NULL default '2',
PRIMARY KEY (id),
KEY postcode (postcode)
) TYPE=MyISAM;
It has 570,441 rows.
Is this a bug in MySQL, or am I doing something wrong? I was using
phpMyAdmin to do the changes, but I don't think that should make any
difference.
Regards,
Dirkjan Ochtman
P.S. I really dislike the spam/off-topic bot at
[EMAIL PROTECTED] It
doesn't want this email, and that gives me a kind of
we-don't-want-help-even-if you-really-try-to-describe-the-problem kind
of
feeling.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
Frank, Since I have limited command line access (ISP hosted database), I will probably have to use phpMyAdmin. Part of my logic was to copy my MySql database to another MySql database, so that I can use the second database for QA purposes. When I perform the Export as above, does that make a copy in another database or is that to an external file? If it is an external file, would I have to Import it to another database that I create? 6. Check Save as file. This means phpMyAdmin will write the export file to a file which it will send to the browser. You can save this file on the machine where the other (QA) MySQL server is running. On this machine, you can load the export like this: Let's say, you exported database my_database, and you saved the export file as c:\mysql\my_database_export.sql on your QA machine. You can import it with this command (assuming that database my_database exists on your QA machine, but has no tables in it): c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 3:37 AM Subject: Re: Copying MySql database to others Thanks everyone for your input. Stefan, From, within phpMyAdmin? snip 1. In the left frame, choose the database you want to backup / copy. 2. Click the EXPORT tab in the right frame. 3. Choose the tables in the database you want to backup. 4. Choose Structure and data. 5. Check Enclose table and field names with backquotes if your table or column names might contain special characters (like ä, ö, ü). 6. Check Save as file. 7. Click Go. Since I have limited command line access (ISP hosted database), I will probably have to use phpMyAdmin. Part of my logic was to copy my MySql database to another MySql database, so that I can use the second database for QA purposes. When I perform the Export as above, does that make a copy in another database or is that to an external file? If it is an external file, would I have to Import it to another database that I create? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Resetting the auto_increment to start from 1
Paul, It's a rather old book, which deals with MySQL 3.23, and not with MySQL 4.x. Actually, he's talking about MySQL Cookbook (p549). Which is a new book, which is why I said *may* reset the counter rather than *will* reset the counter as was true in older versions of MySQL. Oops. Sorry for calling the brand new MySQL Cookbook an old book! :/ TRUNCATE TABLE tbl This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT counter etc. Not always! Try this script: CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; SELECT * FROM t; TRUNCATE TABLE t; INSERT INTO t SET i = NULL; SELECT * FROM t; You're right, and my 4.0.7 behaves the same way. Is TRUNCATE supposed to behave this way? (The manual doesn't say anything about TRUNCATE and AUTO_INCREMENT.) Actually, the counter is reset to 0, not 1. The first inserted value then is auto-incremented, and thus becomes 1. Sure about that? Create a new table and try SHOW TABLE STATUS LIKE 't' and you'll get: [snip] Auto_increment: 1 Oh, well ... There's a slight contradiction in the manual, but you're right again, anyway: When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. So, if initially value=1, then the first auto_increment value would be 1+1. Anyway, we know what the manual wants to tell us, so sorry for being precocious. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; Octavian Rasnita [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 1:49 AM Subject: Re: Resetting the auto_increment to start from 1 At 20:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote: Octavian, I've read the following in a MySQL book: It's a rather old book, which deals with MySQL 3.23, and not with MySQL 4.x. Actually, he's talking about MySQL Cookbook (p549). Which is a new book, which is why I said *may* reset the counter rather than *will* reset the counter as was true in older versions of MySQL. DELETE FROM tbl_name WHERE 1 0; In MySQL 3.23, this was a workaround to force the server to delete a table row by row. By default, 3.23 would on DELETE FROM tbl just do a DROP TABLE + CREATE TABLE, because this was faster in most cases than deleting the rows. This behaviour wasn't ANSI SQL compliant, though. MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI compliant. This means, it will always delete the rows, not DROP/CREATE the table. To do the latter, use TRUNCATE TABLE tbl This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT counter etc. Not always! Try this script: DROP TABLE IF EXISTS t; CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; SELECT * FROM t; TRUNCATE TABLE t; INSERT INTO t SET i = NULL; SELECT * FROM t; See if you get the output I do (MySQL 4.0.8): +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ +---+ | i | +---+ | 4 | +---+ What's the solution? Do this: ALTER TABLE t AUTO_INCREMENT = 1; Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. Actually, the counter is reset to 0, not 1. The first inserted value then is auto-incremented, and thus becomes 1. Sure about that? Create a new table and try SHOW TABLE STATUS LIKE 't' and you'll get: mysql show table status like 't'\G *** 1. row *** Name: t Type: InnoDB Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: NULL Index_length: 0 Data_free: 0 Auto_increment: 1 Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: InnoDB free: 14336 kB Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 8:33 AM Subject: Resetting the auto_increment to start from 1 Hi all, I've read the following in a MySQL book: A special case of record deletion occurs when you clear out a table entirely using a DELETE with no WHERE clause: DELETE FROM tbl_name; In this case, the sequence counter may be reset to 1, even for table types for which values normally
Re: Re: Load local data infile problem
Jennifer, Why would they have to do that? The file does not need to be in that directory. In order to use LOAD DATA INFILE without LOCAL the file just needs to be somewhere on the server that mysqld is running on Exactly this is the point. Most ISPs (at least all the big ones who offer MySQL here in Germany) have their MySQL servers running on separate machines. As a regular customer (this applies to business customers as well) you will get _no_ account at all on the MySQL host machines. This is why you _have_ to use LOCAL to bulk import data. Obviously this does not negate the fact that LOCAL is sometimes needed, but allowing all users to write to mysql/bin is not needed at all for any reason that I can see. Maybe I am missing something? No, this was just an extreme example, thus the smiley ;-) From the docs -- http://www.mysql.com/doc/en/LOAD_DATA.html If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL Version 3.22.6 or later.) This is the way it _should_ be, and the way it _was_ until 4.0.1 or so. With the recent versions (I tested 4.0.5 and 4.0.7 binary distributions), LOCAL will not work at all. This is a bug, not a (security) feature. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; Charles Mabbott [EMAIL PROTECTED]; 'Prathmesh J. Mahidharia' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 11:21 PM Subject: RE: Re: Load local data infile problem Imagine an ISP giving every customer write privileges for the mysql/bin directory ... ;-/ Why would they have to do that? The file does not need to be in that directory. In order to use LOAD DATA INFILE without LOCAL the file just needs to be somewhere on the server that mysqld is running on and be readable by the mysqld user. I load my files in from my home directory because I don't think the mysql base dir and data dir are a great spot to arbitrarily put files (and I don't have permission to them w/o su-ing). If you are connecting via localhost, have FILE permission on the DB, and can create a readable file somewhere on that server, you would be fine. We do not allow LOCAL on our servers as we are running replication and 3.23.54 won't support it. I do not have write permission to any directories except my home directory. I have never run into any problems with LOAD DATA that were not my own fault, usually it is error 13 because I typed the path wrong or didn't chmod the file. Obviously this does not negate the fact that LOCAL is sometimes needed, but allowing all users to write to mysql/bin is not needed at all for any reason that I can see. Maybe I am missing something? From the docs -- http://www.mysql.com/doc/en/LOAD_DATA.html If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL Version 3.22.6 or later.) -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 9:40 AM To: Charles Mabbott; 'Prathmesh J. Mahidharia'; [EMAIL PROTECTED] Subject: Re: Load local data infile problem Charles, I posted the same problem a couple of days ago. LOCAL will not work because of a security improvement the MySQL folks applied. LOAD DATA INFILE C:\\mysql\\fred.txt INTO TABLE data_table; Hope this helps, but only a workaround... Without LOCAL, quite alot of things will not work. Imagine an ISP giving every customer write privileges for the mysql/bin directory ... ;-/ Unfortunately, Monty did'nt mention if this is fixed in 4.0.8 or going to be fixed in 4.0.9 or 4.1. Personally, I regard this security improvement rather a bug than a feature. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Case Problems...
Nick, CREATE DATABASE LookAtMe; the database is created but as: lookatme This is not a bug, but a feature. By default, MySQL 4.0.x has lower_case_table_names set to 1. If you want to change this behaviour, put this in the [mysqld] section of your c:\my.cnf or c:\winnt\my.ini file, and restart the MySQL server: set-variable = lower_case_table_names=0 Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Nick Stuart [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 7:52 PM Subject: Case Problems... I seem to have found a bug with 4.0.7. I just want to make sure the issue hasn't been covered before I submit a report. I have 4.0.7 installed on windows 2000 server with all the service packs and stuff. When I connect to the database through a Linux client (haven't tried it on the box itself) and issue: CREATE DATABASE LookAtMe; the database is created but as: lookatme with no caps. Anybody run into this before? I know Winblow$ isn't case sensitive and all but it should still create the database/folder as I type it. -- -Nick Stuart USM Computer Science Major Visit us at http://csforum.newtsplace.com (run with LAMP) Filter Fodder: mysql, sql, queries, why isn't CREATE or DATABASE in the god damn list! =D - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: remote connect crash
Dmitry, MySQL server segmentation faults when remote mysql client tries to connect on source and binary distributions. Local client connect does not cause any problems whatsoever. I had the same sort of problem with MySQL 3.23.5x servers on SuSE Linux 8.0/8.1, and some newsgroup postings reported the same thing with SuSE Linux. (No one had an explanation or a solution for this.) Which OS do you have? Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Dmitry V. Sokolov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 6:07 PM Subject: remote connect crash Good day, could you help me to solve this problem? MySQL server segmentation faults when remote mysql client tries to connect on source and binary distributions. Local client connect does not cause any problems whatsoever. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load local data infile problem
Paul, Personally, I regard this security improvement rather a bug than a feature. The implementation certainly is problematic, but the underlying issues that it tries to address is definitely real and not to be ignored. People who say otherwise generally don't understand what those issues are. Alright, now you got me :/ What I mean is there should be a way to turn LOCAL on again. It's certainly nice if you can turn it off for enhanced security, but why can't you turn it on again without compiling the server from source? The manual section says that there is a --local-infile[=1] option for the mysql CLI to turn it on, but it only mentions --local-infile=0 for the server to turn it off. To me, this looks like --local-infile=1 was intended to turn LOCAL on again, though the manual doesn't mention it. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; Charles Mabbott [EMAIL PROTECTED]; 'Prathmesh J. Mahidharia' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 4:37 AM Subject: Re: Load local data infile problem At 18:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote: Charles, I posted the same problem a couple of days ago. LOCAL will not work because of a security improvement the MySQL folks applied. LOAD DATA INFILE C:\\mysql\\fred.txt INTO TABLE data_table; Hope this helps, but only a workaround... Without LOCAL, quite alot of things will not work. Imagine an ISP giving every customer write privileges for the mysql/bin directory ... ;-/ Unfortunately, Monty did'nt mention if this is fixed in 4.0.8 or going to be fixed in 4.0.9 or 4.1. Personally, I regard this security improvement rather a bug than a feature. The implementation certainly is problematic, but the underlying issues that it tries to address is definitely real and not to be ignored. People who say otherwise generally don't understand what those issues are. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load local data infile problem
Charles, I posted the same problem a couple of days ago. LOCAL will not work because of a security improvement the MySQL folks applied. LOAD DATA INFILE C:\\mysql\\fred.txt INTO TABLE data_table; Hope this helps, but only a workaround... Without LOCAL, quite alot of things will not work. Imagine an ISP giving every customer write privileges for the mysql/bin directory ... ;-/ Unfortunately, Monty did'nt mention if this is fixed in 4.0.8 or going to be fixed in 4.0.9 or 4.1. Personally, I regard this security improvement rather a bug than a feature. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Charles Mabbott [EMAIL PROTECTED] To: 'Stefan Hinz, iConnect (Berlin)' [EMAIL PROTECTED]; 'Prathmesh J. Mahidharia' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 3:04 AM Subject: RE: Load local data infile problem What I did was setup my file in mysql directory (i.e. fred.txt) LOAD DATA INFILE C:\\mysql\\fred.txt INTO TABLE data_table; Hope this helps, but only a workaround... Chuck http://68.43.100.7:81/aa8vs == Patriotism is the willingness to kill and be killed for trivial reasons. - Bertran Russell -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 3:59 PM To: Prathmesh J. Mahidharia; [EMAIL PROTECTED] Subject: Re: Load local data infile problem Prathmesh, I have just installed mysql and am facing problems with the Load data local infile problem. I went through the manual and did all that was said like including the command --enable-local-infile and --local-infile[=1], but it does not seem to work. I posted the same problem a couple of days ago. LOCAL will not work because of a security improvement the MySQL folks applied. As far as I could figure out, the only way to solve this issue is to grab a source distribution and compile MySQL with --enable-local-infile. All the command line options the manual describes do not work at all! I wonder if - finally and hopefully - someone from MySQL will reply to the LOCAL problem mails, and tell us what to do (maybe there's a workaround), or when the problem will be solved. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Prathmesh J. Mahidharia [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 5:22 AM Subject: Load local data infile problem hello there, I have just installed mysql and am facing problems with the Load data local infile problem. I went through the manual and did all that was said like including the command --enable-local-infile and --local-infile[=1], but it does not seem to work. I have the version 4.0.6-gamma-max-nt. If u could plz, help me out with that, that would be very nice of u! I m developing a project at the moment and have got to make large entries into mysql tables, but this command is not working properly and i m stuck! Could u plz help me out with that..? Thank u in advance. Regards: Prathmesh Mahidharia PRATHMESH J. MAHIDHARIA B.E. (Electrical) M.Sc.Information Technology (Murdoch Uni, Australia) Phone no: (+61) 0421167375 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Resetting the auto_increment to start from 1
Octavian, I've read the following in a MySQL book: It's a rather old book, which deals with MySQL 3.23, and not with MySQL 4.x. DELETE FROM tbl_name WHERE 1 0; In MySQL 3.23, this was a workaround to force the server to delete a table row by row. By default, 3.23 would on DELETE FROM tbl just do a DROP TABLE + CREATE TABLE, because this was faster in most cases than deleting the rows. This behaviour wasn't ANSI SQL compliant, though. MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI compliant. This means, it will always delete the rows, not DROP/CREATE the table. To do the latter, use TRUNCATE TABLE tbl This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT counter etc. Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. Actually, the counter is reset to 0, not 1. The first inserted value then is auto-incremented, and thus becomes 1. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 8:33 AM Subject: Resetting the auto_increment to start from 1 Hi all, I've read the following in a MySQL book: A special case of record deletion occurs when you clear out a table entirely using a DELETE with no WHERE clause: DELETE FROM tbl_name; In this case, the sequence counter may be reset to 1, even for table types for which values normally are not reused (MyISAM and InnoDB). For those types, if you wish to delete all the records while maintaining the current sequence value, tell MySQL to perform a record-at-a-time delete by including a WHERE clause that specifies some trivially true condition: DELETE FROM tbl_name WHERE 1 0; --- Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. I use MySQL 4.05 under Windows 2000. Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 4.0.7-gamma: Bugs affecting the privilege system
Alexander, Have you tried to reproduce them? Bug #1: GRANT USAGE doesn't seem to create a user: mysql GRANT USAGE ON *.* TO 'foo'@'foohost'; Query OK, 0 rows affected (0.26 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; Empty set (0.03 sec) mysql SELECT Host, User, Password FROM mysql.user WHERE User = 'foo'; +-+--+--+ | Host| User | Password | +-+--+--+ | foohost | foo | | +-+--+--+ 1 row in set (0.04 sec) Bug #2: SHOW GRANTS shows wrong GRANT statement: mysql GRANT USAGE ON `footable`.* TO 'foo'@'foohost' IDENTIFIED BY 'foobar' - WITH GRANT OPTION; Query OK, 0 rows affected (0.03 sec) mysql SHOW GRANTS FOR 'foo'@'foohost'; +--- | Grants for foo@foohost +--- | GRANT USAGE ON *.* TO 'foo'@'foohost' IDENTIFIED BY PASSWORD '4655c05b05f1 | GRANT ON `footable`.* TO 'foo'@'foohost' WITH GRANT OPTION +--- 2 rows in set (0.00 sec) Yes, I can confirm both bugs you encountered. My settings: Server version: 4.0.7-gamma-max-nt-log OS: Win2K SP2 Maybe worth mentioning, a database 'footable' doesn't exist on my machine. Double-checking this, I found out it doesn't matter if a database exists or not. Bug #2 happens with database 'test', too. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Alexander M. Turek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 4:39 PM Subject: RE: MySQL 4.0.7-gamma: Bugs affecting the privilege system Hi Stefan, -Original Message- From: Stefan Hinz, iConnect (Berlin) Alexander, - After having set up both machines completely, I installed the Win32 binary distribution of MySQL 4.0.7-gamma. Although I did not upgrade from an earlier version, the privileges tables still have the old structure without the new columns introduced with MySQL 4.0.2-beta! Furthermore, the script menioned in the documentation (mysql_fix_privilege_tables) is obiously not included in the distribution. Because of this, there is no way to use the new privilege system but fixing the tables manually... I encountered the same issue, and Miguel Solórzano from MySQL AB replied that it was his fault, simply forgetting the new privilege tables in the Win32 dist. mysql_fix_privilege_tables is nowhere to find, but a list member who had pulled the 4.1 source found it in there and sent it to me. I extracted the SQL statements, ran the script, and now my 4.0.7 has all the new privileges. You'll find it at the bottom of this mail. Maybe this can fix the other bugs you reported, too. Hi Stefan, It does not. I fixed the priv tables manually, but the other two bugs are still occuring. Have you tried to reproduce them? Thanks, Alexander - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
Frank, I need to copy a MySql Production database to QA. What is the best method of doing this? Do I need to write SQL queries or is there another quicker method? There are alot of possibilities to accomplish this fast. From the command line? shell mysqldump -uusername -ppassword your_database export_file.sql Or, if you want to copy / backup all your databases: shell mysqldump -uusername -ppassword export_file.sql --all-databases From, within phpMyAdmin? First make sure you have a recent copy of phpMyAdmin. 2.3.3pl1 is the most recent (http://phpmyadmin.net). You can only backup one database at a time with this tool, not all of them like with --all-databases. 1. In the left frame, choose the database you want to backup / copy. 2. Click the EXPORT tab in the right frame. 3. Choose the tables in the database you want to backup. 4. Choose Structure and data. 5. Check Enclose table and field names with backquotes if your table or column names might contain special characters (like ä, ö, ü). 6. Check Save as file. 7. Click Go. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 08, 2003 6:18 PM Subject: Re: Copying MySql database to others I need to copy a MySql Production database to QA. What is the best method of doing this? Do I need to write SQL queries or is there another quicker method? From the command line? From, within phpMyAdmin? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load local data infile problem
Prathmesh, I have just installed mysql and am facing problems with the Load data local infile problem. I went through the manual and did all that was said like including the command --enable-local-infile and --local-infile[=1], but it does not seem to work. I posted the same problem a couple of days ago. LOCAL will not work because of a security improvement the MySQL folks applied. As far as I could figure out, the only way to solve this issue is to grab a source distribution and compile MySQL with --enable-local-infile. All the command line options the manual describes do not work at all! I wonder if - finally and hopefully - someone from MySQL will reply to the LOCAL problem mails, and tell us what to do (maybe there's a workaround), or when the problem will be solved. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Prathmesh J. Mahidharia [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 5:22 AM Subject: Load local data infile problem hello there, I have just installed mysql and am facing problems with the Load data local infile problem. I went through the manual and did all that was said like including the command --enable-local-infile and --local-infile[=1], but it does not seem to work. I have the version 4.0.6-gamma-max-nt. If u could plz, help me out with that, that would be very nice of u! I m developing a project at the moment and have got to make large entries into mysql tables, but this command is not working properly and i m stuck! Could u plz help me out with that..? Thank u in advance. Regards: Prathmesh Mahidharia PRATHMESH J. MAHIDHARIA B.E. (Electrical) M.Sc.Information Technology (Murdoch Uni, Australia) Phone no: (+61) 0421167375 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: re[2]: Backups mechanism
Richard, I agree with the post... I feel there should be an inbuilt backup routine. AFAIK there's going to be a hot backup routine build into MySQL 4.1. At this moment, my net connection has gone done, but you should find it at http://www.mysql.com/doc/en/News-4.1.0.html. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Richard Morton [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED]; 'Jonas Widarsson' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 7:49 PM Subject: re[2]: Backups mechanism Hi, I agree with the post... I feel there should be an inbuilt backup routine. (maybe built into something like MySQLFRONT or MyCC.) The shell script below, as far as I understand will not work with InnoDB only MyISAM tables. (please correct me if I am mistaken) It seems that MyCC is far far far behind commercial GUI's, but I feel that this is one part of the product that most people, even ISP's who use MySQL for free would pay for - i.e. a GREAT fully integrated MySQL GUI. Thanks Richard What we have done it right a small script: It logs on to the MySQL server. It then locks the databases and tables we have put in a config file. Simply copies the data files to a new directory. Unlocks the tables. Once this is done (copy if fast) you can tar or zip up the copied files. Simon -Original Message- From: Jonas Widarsson [mailto:[EMAIL PROTECTED]] Sent: 07 January 2003 15:07 To: [EMAIL PROTECTED] Subject: Backups mechanism Hello world! Every attempt I have made to find a decent way of backing up a database ends up with tons of reading to show hundreds of ways to do database backups. I want to know which way to do complete backups is most commonly use d by professional users. (Windows and Linux) I have pleasant experience from Microsoft SQL Server 2000 where backung up is very confident, easy to understand and just a matter of a mouse click. Is there any similarily convenient way to do this with mysql, for example: * a shell script (windows / Linux) that works as expected without days of configuration? * scheduled backups, complete or differential? * GUI-solution ??? (mysql control center does not even mention the word backup) Backup is such an important issue. Why does it seem like it is something that the developers don't care about? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Regards, Richard Morton Quantum Communications +44 (0) 7899 750400 [EMAIL PROTECTED] www.quantum-comms.com - This email, its content and any attachments is PRIVATE AND CONFIDENTIAL to Quantum Communications and is intended for the recipient(s) named above only. If received in error please notify the sender and destroy the original message and attachments. Emails may be monitored or recorded. It is the responsibility of the recipient to ensure that the e-mail and/or any attachment is virus free. The views expressed by the author may not reflect the views of Quantum Communications Limited. - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query mysql on another server
Paul, Dave, If I understood the initial question correctly, your first suggestion is not what he wants. I think he's talking about accessing two servers from the same connection, which is not possible: [snip] You can't write a SQL statement that refers to tables hosted by another server. (I've seen claims that this can be done, but they always turn out to have been made by people who haven't actually tried it.) It's possible with M$ SQL Server. I guess that's the background for this question. I've recently performed a training course for an organisation where they want to migrate from SQL Server to MySQL, and they miss this feature quite a bit. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Christensen, Dave [EMAIL PROTECTED]; 'Chris Boget' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 7:35 PM Subject: RE: Query mysql on another server At 12:28 -0600 1/7/03, Christensen, Dave wrote: You can use the command line mysql with the host option, i.e. --host=hostname, to connect to another machine. Likewise, you could set up a data source using ODBC to look at the database on the second host machine through which you could generate queries against the tables on the other host's database tables. If I understood the initial question correctly, your first suggestion is not what he wants. I think he's talking about accessing two servers from the same connection, which is not possible: Throughout this chapter, I've been making the implicit assumption that all the tables involved in a multiple-table operation are managed by a single MySQL server. If this assumption is invalid, the tables become more difficult to work with. A connection to a MySQL server is specific to that server. You can't write a SQL statement that refers to tables hosted by another server. (I've seen claims that this can be done, but they always turn out to have been made by people who haven't actually tried it.) -- MySQL Cookbook, p671 -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 11:12 AM To: Chris Boget; [EMAIL PROTECTED] Subject: Re: Query mysql on another server At 10:08 -0600 1/7/03, Chris Boget wrote: We have a *nix network and on that network we have 2 web servers. One for development (server1) and one for production (server2). And on each of those servers we have MySQL installed. Is there a way we can query a table on server2 from server1? No. I know that when you are using a database from the mysql command line, you can query a table in another database just by using select databasename.tablename. But the same method using servername doesn't work. Is this even possible? No. thnx, Chris sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LEFT JOIN function locking up when using large database
Rob, as Jennifer stated, the problem is that your query doesn't use indexes. SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; some condition would be of interest here. If MySQL can't use indexes here, it will have to scan all of table2 (the big one) to find out which rows match. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Rob Taft [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 06, 2003 7:53 PM Subject: LEFT JOIN function locking up when using large database I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll call these table1 and table2. The query uses both tables: SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; table2.table1_ID is just an int that matches an ID from table1. This way I can get several values from 2 tables with one query. This works great when table 1 has 100 entries and table2 has 1,000 entries. But when i increase both by a factor of 10, the query never returns anything. I let it sit for 10 mins before giving up, and my CPU usage is at 100% the whole time. Any suggestions as to what the problem is? I tried this on both 3.23 and 4.0.7 and got the same results. The [EMAIL PROTECTED] would not take my email. Rob Taft [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with drop table
Francesco, ERROR : Error on delete of .'\camcomm.tmpge.MYI' The problem persists. I don't know the reason of this error. I ask if a new version of Mysql (the version 4.0.7 i s released) is better. Most probably. I had problems like that with 4.0.3 and 4.0.4, but not yet with 4.0.7 (all under Win2K). Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Francesco [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, January 05, 2003 3:10 PM Subject: problem with drop table Hello, I tried to drop a table with Mysql 4.0.1 alpha nt and I have encountered the following problem: ERROR : Error on delete of .'\camcomm.tmpge.MYI' The problem persists. I don't know the reason of this error. I ask if a new version of Mysql (the version 4.0.7 i s released) is better. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
