Re: Alter table command don't work
Graham Reeds wrote: Quentin Bennett wrote: http://dev.mysql.com/doc/refman/4.1/en/alter-table.html From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; That is the syntax I used. What about prior to 4.1.2? Perhaps it was not available? See http://www.dev.mysql.com/doc/refman/4.1/en/charset-table.htm You may have to dump the data and then reload into a new table with the correct charset. You may be able to copy it using an INSERT SELECT (not sure though, haven't had to do it) Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL commercial licence
Adam Lipscombe wrote: Folks, We have a commercial product that uses mysql 4.0.24. We bought a commercial licence for this version. We want to upgrade to 5.0.21. I spoke with the MySQL saleswoman this morning and she says they don't do upgrades and want another entire licence fee for v5.x. Does anyone else have experience of this? Upgrading commercial licences? What's the story? Thanks - Adam Hi Adam, That is a very interesting development. Is your license under the MySQL Network? I am considering using this for a number of new servers and would have to rethink my strategy if this is the case. I notice their Network FAQ has Q: Does MySQL Network include MySQL 5.0? A: Yes, MySQL Network includes all updates and upgrades including MySQL 5.0 It is also very interesting that the Network product automatically includes a GPL covered product rather than a commercial one by default. How many people actually check on that before purchase? Are they aware they have purchased a GPL product and are now obligated under that license to GPL their distributed products? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unidata to Mysql
Hi Brett, Which version of Unidata? I doubt very much if the migration toolkit would assist with this. You will probably have to re-normalise the data due to the multi-value aspects of the Unidata/Universe database. This would probably require the addition of several more tables to cope (dependent on the original design of the database). Regards > > Has anyone converted from Unidata db to Mysql? How easy/difficult > is it to do? Does the Mysql Migration toolkit help with that process? > > an old consulting company setup a website with Unidata and perl... we > want to convert to mysql... > > Thanks! > > -- > > /Brett C. Harvey; > /Creative-Pages.Net, President; > /Facility Management Systems, CTO (www.fmsystems.biz); > /Lasso Partner Association Member ID #LPA135259 > (www.omnipilot.com/www.lassopartner.com); > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should we petition GoDaddy?
James Eaton wrote: - Original Message - From: "Nicolas Verhaeghe" <[EMAIL PROTECTED]> To: "'MySQL General'" Sent: Saturday, April 15, 2006 11:20 PM Subject: Should we petition GoDaddy? I am slowly considering leaving GoDaddy, who has a very good bandwidth and ok tech support (I have seen better but much much worse) and acceptable prices, but unfortunately does not support MySQL 5 and PHP 5 either. I wonder if I could give these people a chance but one thing I'd like to do is see if we could petition these guys and simply threaten to close out our accounts if they do not get on with the music. The current version of MySQL is 3.23. I have no time upgrading my dedicated server to a RedHat machine with custom PHP and MySQL, I don't have the time to do that. Who think we could make them make the right move and pretty quickly? Save your energy and just move your web site to another host. Don't be too impressed by extravagent bandwidth claims. In a shared hosting environment, where servers are often grossly overloaded, you'll either have very little chance of reaching those bandwidth numbers, or if you do, you'll be asked to upgrade or close your account because of the high processing load needed to push that much traffic. Who is GoDaddy and should I care? Is this an international issue? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Anago Chima wrote: Please, I want to know if there is a way I can back up all the databases in my MySQL server to a text file just with a single mysqldump query. I run MySQL Server Version 4.1.14 on Windows XP pro __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Hi, From the manual, http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html use -A or --all-databases as an option Regards David Logan South Australia When in trouble, or in doubt Run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
mos wrote: At 09:27 PM 3/31/2006, you wrote: I have been offering free database hosting for over 4 years and I've been doing it on a shoestring.My last MySQL server was a generic 1GHz system with 256MB RAM running Redhat 9. The performance was surprisingly good because the query loads were not typically high. One persistent problem was the initial connection times. On that old system if I had less than approx 10,000 separate databases then the connection times were "fast", and on the order of 1 second or so. If I had more than 10,000 databases this dramatically changed the connection times to well over 15 seconds or more. I always attributed this connection lag to a problem with the filesystem and the large number of directories. The old server had RH9 and ext3 with no htree support which I was told could help with this problem. I recently bought a new 2.4 GHz system with 1GB of RAM and installed Fedora 4 with ext3 and htree support. All new hardware, faster drives, more RAM and updated software. I thought I was golden! Well, I have 14,000 databases on this new system and it is as slow as the old 1GHz system. The tuning articles I've read, and the sample my-*.cnf files that ship with the tarball appear to apply to the more typical installation of a single huge database rather than thousands of individual dbs. Can anyone offer any suggestions? Thanks, Gary Huntress Gary, Just a guess, but could the problem be the 14,000 directories you have to store the 14,000 databases? The problem could be the OS directory structure. Putting the data into fewer databases will likely solve the problem or perhaps move half of the directories to another drive. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Gary, I think that Mike may have hit the nail on the head. I've a few unix directories with multiple thousand files and they do become a bit of a problem to manage speedwise. Perhaps, as Mike has suggested, place half of them on another drive. The other option could be to run multiple instances of MySQL, each having a different port number (this could be based on username or something similar) eg. A-D port 3306, E-H 3307, etc. and reducing the number of dbs per instance (server) that way. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
Not wanting to be rude but this question would probably be answered better in a php group. I realise there are many users of php that subscribe to this list, but this is a mysql list not php. If you go to http://www.php.net/support.php you will find a large number of resources (including lists at http://www.php.net/mailing-lists.php), not to mention examples of code, irc channels etc. Regards fbsd_user wrote: Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1016 : cant open ibd file even though it exists
d files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: "Rithish Saralaya" Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout
Re: Surviving MySQL crash
Foo Ji-Haw wrote: Heikki Tuuri wrote: Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Thanks for the reply Heikki. Let me rephrase my problem: the data remains intact, but I suspect the indexes are corrupted. Basically I have to run myisamcheck and mysqlcheck to get my tables operational again. No data loss though. Logan (thanks Logan) suggested a periodic flush call. I am wondering if auto flushing is available? Hi, You could set that up as a small cron job (Are you using unix/Linux?) like so 15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES' This would run a FLUSH TABLES once every hour at 15 minutes past. If you are using Windows, sorry I'm not sure how to do it there. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection problem after long idle periods
Michael Lai wrote: I am currently using MySQL 5.0.15 with mysql-connector-java-3.1.11. I can access the database from my JSPs with no problem except for one small issue. After a long delay (usually overnight), when someone first tries to access the database, I would get the following error: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Broken pipe STACKTRACE: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) at java.net.SocketOutputStream.write(SocketOutputStream.java:136) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2690) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.apache.jsp.processLogin_jsp._jspService(org.apache.jsp.processLogin_jsp:81) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595) ** END NESTED EXCEPTION ** Last packet sent to the server was 0 ms ago. But afterwards, on the second and subsequent attempts, the database runs fine. It is only the first try after a long idle period that I get this error message. I am not sure how to fix this. I am guessing that the database connection is down after a long idle period and have to recreate a connection first after the first try. Hi Michael, Try looking at http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html more specifically the connect-timeout variable. You should use SHOW VARIABLES LIKE "%connect%"; and check the value. It maybe this and you can stretch it out further if you need to. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting the data directory.
Adam Alkins wrote: Hi, There's a mysqld startup option --datadir=/path/to/data which you can use to alter your data directory. Simply adjust your startup scripts to suite. Regards, -Adam On 1/31/06, Scott Johnson <[EMAIL PROTECTED]> wrote: Hi All, I am trying to find out how to change the location of the data files. I have not yet found a generic entry for the my.conf file to set a data and log location. Can I move the exiting data directory form the mySQL tree and put a soft link to a new location on another partition? thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Alkins http://www.rasadam.com Hi Scott and Adam, You don't even have to modify the startup scripts, just add datadir=/path/to/mysql/stuff to the my.cnf file and it will point to there. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout
Re: mysqldump: Got errno 32 on write ?
Anthony Ettinger wrote: Turns out I forgot the gzip > filename, only had | gzip filename. But the real problem here is I'm getting packet loss and loosing my connection, which is probably why I get that error 32. On 1/5/06, Anthony Ettinger <[EMAIL PROTECTED]> wrote: I am, I'm running a remote command mysqldump | gzip > some file. On 1/5/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote: Hi, Are you running mysqldump through into a pipe eg: into tar or similar? I mention this because : test1=>perror 32 System error: 32 = Broken pipe Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Friday, 6 January 2006 2:17 PM To: mysql@lists.mysql.com Subject: mysqldump: Got errno 32 on write ? "mysqldump: Got errno 32 on write" -- any ideas? I think it may be mysqldump is not run as user, because dump dir is 0700?? -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html Hi Anthony, Are you running the mysqldump from one host and gzipping on another? This could be the result of a network issue. Another thing to check is the size of your mysqldump, some versions of gzip (certainly the older ones) have size limitations. You may be busting these. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can i get a mysql technology standard
wangxu wrote: >Can i get a technology standard? > >It include database capability,table capability,row number limit in table,blob >object capability etc... > Hi, A good read of the manual at http://dev.mysql.com/doc/refman/5.0/en/index.html would give you almost all of the information that you require. Especially the page http://dev.mysql.com/doc/refman/5.0/en/introduction.html which gives the various capabilities of the database in general. If you look under the datatype definitions, you will find the maximum size of each datatype and any limitations they may have. Each engine is fairly well described in the section http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html along with any special features and/or limitations. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rentering a sql script
Jon Miller wrote: I have a sql script that needs to be re entered using the following command mysql < create.sql but the database already exists. IS there a way to overwrite or update the database using the script, or do I have to delete the existing database? I have a sql script that needs to be re entered using the following command mysql < create.sql but the database already exists. IS there a way to overwrite or update the database using the script, or do I have to delete the existing database? Hi Jon, You will have to drop the database first if you wish to start from scratch. If you wish to update the records, the syntax at http://dev.mysql.com/doc/refman/5.0/en/create-database.html would be useful, especially the IF NOT EXISTS bit. This will allow you to bypass the CREATE DATABASE requirement. You may also use a similar syntax for the CREATE TABLE statement. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout
Re: Error 1013
Karl Krelove wrote: David Logan wrote: Karl Krelove wrote: I'm trying to import a large amount of data from an Access database containing information about 9,000+ students in a school system. I've created a table 'student_list' to hold the data and issued the following command: LOAD DATA INFILE 'home/karl/Student_List.csv' into table student_list FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; In response I get: ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/home/karl/Student_List.csv' (Errcode: 2) I've looked up the error on the MySQL website and found the error listed as Error 1013 in the listing of server errors for MySQL 4.1, but I can't find an explanation. 1st question - the obvious one - does anyone know what is triggering the error? 2nd question - why does MySQL only list the errors? I could already read what it said in the CLI client. Is there a place on the web site that actually explains what specific errors mean? Thanks in advance for any input. Karl Krelove Hi Karl, Do you have your home directories under the mysql path? When you are doing a LOAD DATA INFILE the following rules (from the manual) apply to the pathname I suspect, based on these rules, it will be looking for the file relative to /var/lib/mysql as it states in the error message. Further info is available at http://dev.mysql.com/doc/refman/5.0/en/load-data.html Regards My first reaction as I read your response was "But I had just run a LOAD INFILE command on another table from the same database - same directory, same command, nothing different except the filename and the table name - with no path problem." Then I looked a little closer at the LOAD INFILE I pasted into this message. What a difference a single slash can make! When I re-ran the command in MySQL using the same path but with a slash at the _beginning_ - '/home/karl/Student_List.csv' - it worked and the data was imported in about a hundredth of a second! Thanks for pointing me toward a path problem. Does "Can't get stat" translate to "File not found?" And as for my other general question, is there a resource online (or even in a bound manual) that actually explains MySQL error messages? There is that huge listing on the MySQL website that _lists_ a gazillion errors and their numeric codes, but no links to explanations. Thanks again. Karl Hi Karl, There are many many error codes unfortunately. I don't know of any specific resource, however in the manual there are a couple of the appendices that can be very useful, appendix A especially. I find that I just have to read the manual for the command that I have issues with otherwise. "Can't get stat" indeed means as you thought. A stat is a unix system function that returns details on the file, eg: ownership, group, size, date last modified etc. It is telling you it can't find it to retrieve the info required. Pleased to see it all worked ok. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1013
Karl Krelove wrote: I'm trying to import a large amount of data from an Access database containing information about 9,000+ students in a school system. I've created a table 'student_list' to hold the data and issued the following command: LOAD DATA INFILE 'home/karl/Student_List.csv' into table student_list FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; In response I get: ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/home/karl/Student_List.csv' (Errcode: 2) I've looked up the error on the MySQL website and found the error listed as Error 1013 in the listing of server errors for MySQL 4.1, but I can't find an explanation. 1st question - the obvious one - does anyone know what is triggering the error? 2nd question - why does MySQL only list the errors? I could already read what it said in the CLI client. Is there a place on the web site that actually explains what specific errors mean? Thanks in advance for any input. Karl Krelove Hi Karl, Do you have your home directories under the mysql path? When you are doing a LOAD DATA INFILE the following rules (from the manual) apply to the pathname When locating files on the server host, the server uses the following rules: * If an absolute pathname is given, the server uses the pathname as is. * If a relative pathname with one or more leading components is given, the server searches for the file relative to the server's data directory. * If a filename with no leading components is given, the server looks for the file in the database directory of the default database. Note that these rules mean that a file named as |./myfile.txt| is read from the server's data directory, whereas the same file named as |myfile.txt| is read from the database directory of the default database. For example, the following |LOAD DATA| statement reads the file |data.txt| from the database directory for |db1| because |db1| is the current database, even though the statement explicitly loads the file into a table in the |db2| database: mysql> *|USE db1;|* mysql> *|LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;|* Note that Windows pathnames are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them. For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use |LOAD DATA INFILE| on server files, you must have the |FILE| privilege. I suspect, based on these rules, it will be looking for the file relative to /var/lib/mysql as it states in the error message. Further info is available at http://dev.mysql.com/doc/refman/5.0/en/load-data.html Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user to start/stop mysql without db access?
mel list_php wrote: Hi, This may be a strange question, but I was wondering if it is possible to have a user starting/stopping the mysql server who wouldn't have access to the data. To explain: I want to set up a server as user "Groupinstaller" for example. This is a virtual user to allow other people of my group to start/stop the server by becoming "Groupinstaller". But I don't want this user to be able to see the different databases of the different people, nor touch the data directory. For example, I have a database called db1 and my coworker has a db called db2. I am root of mysql, so I have access to all the databases, but he is just a user with access to his db and not to mine. If no other solution, I could eventually do with a read access, but I really don't want to allow write. I was thinking at replacing the typical "mysql" user by the "Groupinstaller" one, but this user owns the data directory... Does anybody know if it is possible, and how? thanks, melanie _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ Hi, If you are using a *nix operating system, then consider using sometbing like sudo or ssu to allow the other user access to the startup/shutdown script only. sudo can be found at http://www.sudo.ws/sudo/ not sure where ssu comes from though. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help! Problem moving MySQL data dir to a larger file system
Hi Daniel, Check your permissions. Your mysqld process is probably trying to create the log file but can't and is telling you via the error code 13 message $ perror 13 System error: 13 = Permission denied $ Regards Daniel C. Mahoney wrote: I'm running MySQL (--ersion says "mysql Ver 14.7 Distrib 4.1.10a, for pc-linux-gnu (i686)) on a RedHat Enterprise ES Release 4 system. The datadir was in it's own filesystem, mounted at /mysql, but it's now filled up (it's a 15 GB partition and it has 1.5 MB free). I shut down MySQL (did a "kill `cat /mysql/mysql.pid` and waited for the daemon to exit), created a new directory (/z/mysql, in a filesystem with about 30 GB free), and verified that the files copied ok. I now try to restart mysql with "/usr/local/bin/mysqld_safe --datadir=/z/mysql --pid-file=/z/mysql/mysql.pid > /dev/null 2>&1 &". When I do a "ps ax" afterwards I don't see mysqld running. The host name is www2, so I looked in /z/mysql/www2.err and saw: 051107 00:32:49 mysqld started /usr/local/libexec/mysqld: File './www2-bin.46' not found (Errcode: 13) 051107 0:32:49 [ERROR] Could not use www2-bin for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. 051107 0:32:49 [ERROR] Aborting 051107 0:32:49 [Note] /usr/local/libexec/mysqld: Shutdown complete 051107 00:32:49 mysqld ended This confuses the crap out of me! I looked again in the /mysql directory, and there is no file named www2-bin.46 prewent. There is a www2-bin.45, and that same file is present in /z mysql and is the same size. So why is mysql looking for that non-existent file? And what do I have to do to make this work again? This is kind of a critical problem for us. -- Dan Mahoney [EMAIL PROTECTED] "How you behave towards cats here below determines your status in Heaven." Robert Heinlein "There are two means of refuge from the miseries of life - music and cats" - Albert Schweitzer -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Failure to install on Solaris.
13/sql' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/scratch/hgs/mysql-4.1.13' gmake: *** [all] Error 2 neelix hgs 57 %> ls /usr/lib/*pthread* /usr/lib/libpthread.so /usr/lib/llib-lpthread /usr/lib/libpthread.so.1 /usr/lib/llib-lpthread.ln neelix hgs 58 %> Any suggestions as to how I get around this and get the whole thing installed in /usr/local/mysql-4.1.13 ? Thank you, Hugh Hi Hugh, Do you have the pthread library path in the -L path somewhere? It doesn't seem to be able to find it. You might like to use crle to put the paths in in a more permanent fashion Regards David -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where did my disk space go?
Hi Siegfried, In the mysql data directory, -rw-rw1 mysqlmysql 358975 Mar 10 14:28 aaudbasa01.log -rw-rw1 mysqlmysql 25088 Feb 15 08:08 ib_arch_log_00 -rw-rw1 mysqlmysql 5242880 Jun 22 11:20 ib_logfile0 -rw-rw1 mysqlmysql 5242880 Feb 15 08:08 ib_logfile1 The above files maybe in slightly different places, it depends on whether you are using the InnoDB engine or the MyISAM one. The InnoDB logging files are the ib_logfiles. You could also check the my.cnf for any form of logging being switched on, in particular binary logging. You could check for this as well. This will be in your my.cnf file as # Replication Master Server (default) # binary logging is required for replication log_bin If so, you could be filling up your disk quite easily Regards > Thank you David and Sebastion, > I am not doing this in a transaction (at least, I did not do anything > special to start a transaction) and I have no need for a transaction. > > How do I check the log files you two suggest? > > Thanks, > Siegfried > > -Original Message- > From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 03, 2005 8:10 PM > To: Siegfried Heintze; mysql@lists.mysql.com > Subject: RE: Where did my disk space go? > > Hi Siegfried, > > I would check your transaction logs. Are you doing this as one giant > transaction? The system may be filling up the logs just in case you need > to rollback. > > Regards > > David Logan > Database Administrator > HP Managed Services > 148 Frome Street, > Adelaide 5000 > Australia > > +61 8 8408 4273 - Work > +61 417 268 665 - Mobile > +61 8 8408 4259 - Fax > > > -Original Message- > From: Siegfried Heintze [mailto:[EMAIL PROTECTED] > Sent: Thursday, 4 August 2005 10:00 AM > To: mysql@lists.mysql.com > Subject: Where did my disk space go? > > I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for > many > (> 20) hours issuing SQL UPDATE and DELETE commands. The update commands > should not be increasing the storage requirements, I'm just updating > integer > values. > > I've noticed several times now that I run out of disk space. I started > with > a gigabyte free. Last time, I aborted the program, compressed my disk, > retrieved much lost disk space and started again. > > Now I tried that again: no luck. I rebooted and recompressed again. I'm > still out of disk space. > > It seems that MySQL just keeps using more and more disk space. > > How can I retrieve my lost disks pace? > > Thanks, > Siegfried > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installer for 4.0.13
Mayuresh Kshirsagar wrote: Hi Peter, Its the requirement for the project to use 4.0.13. I visites the page you mentioned. but the only build available there which i can see is 4.0.24. Am I missing something? Regards, Mayuresh - Original Message - From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Mayuresh Kshirsagar'" <[EMAIL PROTECTED]>; "'MYSQL Mailing list'" Sent: Wednesday, May 25, 2005 12:22 PM Subject: RE: Installer for 4.0.13 Mayuresh Kshirsagar <mailto:[EMAIL PROTECTED]> wrote: Where can I find the installer for MYSQL DB 4.0.13 for solaris/linux. I couldn't find it on the site. Could you guide me please. Is there any reason why you don't want the latest build of version 4.0? If not, you can find them here: http://dev.mysql.com/downloads/mysql/4.0.html Peter Normann Hi Mayuresh, You can find the older versions here http://downloads.mysql.com/archives.php?p=mysql-4.0&o=solaris Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout
Re: Need help in locating configuration file
http://dev.mysql.com/doc/mysql/en/mysql-config.html&e=9888 If you have questions, try http://dev.mysql.com/doc/mysql/en/index.html first as you will often find the answer there. BTW this manual is for the latest version that you are using. > Hi, > Iam using Mysql5.04 > What does it mean, > > "To install the mysqli extension for PHP, use the > --with-mysqli=mysql_config_path/mysql_config > configuration option where mysql_config_path > represents the location of the mysql_config > program that comes with MySQL versions greater > than 4.1." > > in this,is mysql_config file is same as my.ini and > the path to it in windows 2000 NT (on my system is > )c:\Program Files\mysql\mysql server 5.0 ;" is it > correct, then after editing the php.ini file why > doesnot it is working. > > Any help is welcome. > > Thanks > Sreedhar > > > > __ > Do you Yahoo!? > Read only the mail you want - Yahoo! Mail SpamGuard. > http://promotions.yahoo.com/new_mail > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql client update
Philip Denno wrote: I'm no database expert, so I may be wrong but I do not think this functionality exists. Usually this type of notification is handled at the application level. That is build an application layer on top of the database which handles all reads and writes to the database. This layer would also keep track of all connected clients and would notify when a record is modified/inserted/created. Cheers, Philip. -Original Message- From: Eric White [mailto:[EMAIL PROTECTED] Sent: May 17, 2005 10:44 AM To: mysql@lists.mysql.com Subject: mysql client update Hi, Where should I look to find information about having clients notified when a table/record is updated? I have a situation where multiple clients will each have an open database connection, and I would like for them to be notified when the database is modified by a member of the group. Something like a trigger that informs all clients that a new record has been added for example. Thanks in advance. Regards Hi, Triggers were added at 5.0.2 They have some limitations but are documented in the manual at http://dev.mysql.com/doc/mysql/en/create-trigger.html Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup a database
Rafael Diaz Valdes wrote: Hi, I 'm using mysqldump to backup my database, I wrote in my backup.sh the following string: $MYSQL/bin/mysqldump --opt databasename -user=root --password=rootpassword > backup/mysql/databasename.sql I include backup.sh in my cron weekly (/etc/cron.weekly) but when it is executed appear databasename.sql empty, else if I run backup.sh in a shell konsole it is executed ok, do you know why can not execute the string in the cron ?? my mysql version is : 4.0.17 regards rafael Hi Rafael, Check the value of the variable $MYSQL. It is probably not being set by cron and should be set in your shell script. Cron starts with only a couple of variables set and you have to start everything else. Are you getting any output from the cron daemon? It should be in the logs somewhere or else emailed to root. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't read database
[EMAIL PROTECTED] wrote: I'm working on a database crash recovery. Wanting to repair the tables (check table zf_forums), in 'mysql' mode as root user I get the following respons: mysql> use zforum Can't read dir of './zforum/' (Errcode: 13) Database changed mysql> use zforum Database changed mysql> check table zf_forums; ++---+--+-+ | Table| Op| Msg_type | Msg_text | ++---+--+-+ | zforum.zf_forums | check | error| Can't find file: './zforum/zf_forums.frm' (errno: 13) | ++---+--+-+ 1 row in set (0.00 sec) mysql> \q [EMAIL PROTECTED] mysql# perror 13 Error code 13: Permission denied I've made root also part of the mysql group (this group has all rwx permissions) --- to no avail. Does anyone know why as root I can't access the tables? Thanks. Good Morning, The OS user 'root' is different to the MySQL user root. They share only the name in common. Judging by your first error message, have you checked the permissions of the 'zforum' directory in the mysql data directory? It seems unable to change to the correct database. Check the ownership of the directory, IMHO I think it should be owned/group mysql. If the permissions are correct, then you need to check the .frm file hasn't disappeared for the table. If it has then you need to follow the instructions at http://dev.mysql.com/doc/mysql/en/repair.html, I think you may have reached stage 4. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RES: phpmyadmin can't connect via browser
Mark Sargent wrote: Robert Restad wrote: snip snip Hope I've given enuff info this time. Cheers, again. (Oh, btw, I'm not a php programmer etc, I'm just doing this so I can say to a future employer, yes, I can install/setup/config php/mysql etc if you need it). Mark Sargent. Hi All, ok, am I better off installing an earlier version, since they make it so damn hard just to get a simple thing working in php5..? Absolutely rediculous, this. Cheers. Mark Sargent. Perhaps the mysql, php and phpmyadmin documentation may help. I had phpmyadmin up and running in about 10 minutes. http://dev.mysql.com/doc/mysql/en/index.html http://www.php.net http://www.phpmyadmin.net The docs are most comprehensive and go over everything you have noted above. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables,
Carlos Bergueira wrote: Hi, I need to know some things: - Where can i get (url of document) the MySql database fields ? (For example, i need to list all tables of a database (database: mydb) and for each table properties like comments, size, record number, etc ?). I use PHP to write output. - I need to do the same with all fields of each table. (The output i need is explain above) Fields: field(0), field(1), field(2), etc.. Records: 1,MyClient_1,56888554, etc... Thanks in advance. Cumprts, Carlos Bergueira O SAPO já está livre de vírus com a Panda Software, fique você também! Clique em: http://antivirus.sapo.pt Hi Carlos, If you go to http://www.php.net and search for mysql you will find a number of different functions that will get you the info you need. Also try http://dev.mysql.com/doc/mysql/en/index.html (The english version, there are several possibly in languages which you may prefer) If you need further functionality you can look at http://pear.php.net and look for the DB libraries. They have many functions to return column names etc. along with examples. If you want practical examples, look at phpMyadmin (http://www.phpmyadmin.net) as this does almost exactly what you are trying to do, this is an open source product and you get many, many examples of your request. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Url http://highperformancemysql.com/
John McCaskey wrote: On Fri, 2005-02-18 at 08:08 -0800, Jason Martin wrote: On Fri, Feb 18, 2005 at 10:06:38AM +0100, Anton Kornexl wrote: There should be tools on this website, but i see only a message from Infektion Group. What happened ? Looks like the website got hacked. I guess they should have written a book on high security mysql instead? John A. McCaskey Hi, I just tried http://www.highperformancemysql.com and it works fine. You can also try http://jeremy.zawodny.com/mysql/ Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout
Re: MySQL database and user creation from script file
[EMAIL PROTECTED] wrote: Hi, I have to create user and database using script file. My requirements are given below. 1. Login as root 2. Execute the script file for database and user creation. 3. Exit My script file should have mysql -u root create database mnms; user creation command The script file will be called in Win batch file and the same batch file will be executed. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Perhaps reading the manual might be part of your exercise. Try http://dev.mysql.com/doc/mysql/en/index.html Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
