Re: A question about mysql database backup.
On 02/25/2004 09:13 AM Frederic Wenzel wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Are back-ticks actually part of ANSI SQL? Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My backup scripts and how reliable are they?
On 02/26/2004 09:02 AM Scott Haneda wrote: As per the reco of someone on this list, I created a bash script that runs once a day to do the following: # set path to final destination location=/Volumes/foo/sql_dumps/ # set db_list to the list of databases db_list=`echo show databases | /usr/local/mysql/bin/mysql -N -u -p` for db in $db_list; do echo dumping $db to $location$db.sql /usr/local/mysql/bin/mysqldump -u -p --opt $db $location$db.sql done echo changing to directory $location cd $location echo Now in: pwd echo begin gzipping and tarballing tar -zcf $location$time.tar.gz *.sql echo removing: ls -la $location*.sql rm $location*.sql echo All your MySql Database are Belong to Us; echo $location$time.tar.gz The first thing I would like to know, is what you all think of this method and how secure is it to run the username and passord in the file, if not, what other options do I have? Seems OK if you're not happy with hot backups. The password is not very safe though, unless it's under root. Perhaps you could make a user with a bizarre user name no password minimum rights, just for this op. It's debatable how secure it would be. Second question, when I do a dump out of phpmyadmin, I get 1 line at a time insert into statements, when they come from my script, I get one insert concatenated with the rest. They both work, so aside from one file being larger than the other, what are the pros and cons? The 1line at a time file would be more easily editable. One field in a few databases is of the type password, phpmyadmin outputs it as 0x6ad6600d88afb42e5bef276c039330cc and my script above yields something like this (- 3?$ How do I made sure I have a fully restorable backup? Try to run with the restored database (close down mysql, move the database directory, re-initialize mysql then run your restore) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: German Language characters storage.
On 02/24/2004 04:45 PM Michael Johnson wrote: Can MySQL store German characters such as ä, o, u and ß Yes it can. It can store anything digital. It's what you program that counts. :) Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max key length 500 in myisam index
I've got to create a table that has the following: CREATE TABLE access ( query VARCHAR(255) NOT NULL, INDEX (query) ); and mysql is telling that the max bytes allowed is 500 for key length. The docs say I can change this by recompiling, which I would like to avoid having to do. Is there any way around this to get a 255 character length field with index? This is going to store weblog entries, so it's going to be a huge table. Is there a different table type / column type / index type I could use to achieve what I need? thanks in advance Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
netstat
I'm running a web server with Apache Tomcat and mySQL for the first time and I've been following various recommendations to make the server more secure (it's Linux Debian). One of these recommendations is to keep the number of open ports to a minimum. I'm looking at netstat (see the output below) and it makes me wonder if the mysql port needs to be open at all, since the tomcat and mysql database are running on the same machine. Can I block off these ports? Would I have to use a firewall to do that? (like ip_tables) Thanks Adam Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp0 0 localhost:8005 *:* LISTEN 30723/java tcp0 0 *:mysql *:* LISTEN 1890/mysqld tcp0 0 *:www *:* LISTEN 30723/java tcp0 0 *:ssh *:* LISTEN 248/sshd tcp0 0 *:12121 *:* LISTEN 267/perl tcp0 0 *:smtp *:* LISTEN 239/master tcp0 0 *:https *:* LISTEN 30723/java tcp0 0 localhost:mysql localhost:1311 ESTABLISHED 1890/mysqld tcp1 0 localhost:1312 localhost:mysql CLOSE_WAIT 30723/java tcp0 0 localhost:1311 localhost:mysql ESTABLISHED 30723/java -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF-8 support
On 12/16/2003 01:35 AMnbsp;Ligaya Turmelle wrote: Can anyone tell me what the current support for the UTF8 character set is? How strong is it? Do I have to do anything special to save the characters? I will be getting the characters from a webpage form and inserting the characters with PHP. It depends if you are prepared to go with the 4.1.1 alpha version or not, which has complete UTF-8 support. Production version 4.0.x lacks some of the UTF-8 facilities but I believe many people are using it with work-arounds where necessary. Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
adding first users in user table
Hi Listers, I have just installed mysql following the instructions for the binary distro under linux and I added my root user and password using mysqladmin as mentioned in scripts/mysql_install_db. In the mysql.user table I see my [EMAIL PROTECTED] and [EMAIL PROTECTED] but I also see two entries with blank user names. | host| user +-+-- | localhost | root | localhost | | harda1.myserver.com | root | harda1.myserver.com | +-+-- 4 rows in set (0.00 sec) Have I made a mistake that inserted these 2 blank users? Or are they there for a good reason? Thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: running 4.0.15 side-by-side with 4.1alpha
On 11/11/2003 09:21 AM Egor Egorov wrote: Adam Hardy [EMAIL PROTECTED] wrote: I am sure I say someone asking a similar question on the list before but I can't find anything in the archives, nor anything relevant in the mysql docs. You can find info how to run multiple MySQL servers at: http://www.mysql.com/doc/en/Multiple_servers.html Excellent, thanks Egor. I didn't see it there before in the admin section. I was looking in Installation. Regards Adam -- mySQL 4.1.0 + connector/J 3.0.9 + j2sdk 1.4.2 + Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running 4.0.15 side-by-side with 4.1alpha
Hi, I am sure I say someone asking a similar question on the list before but I can't find anything in the archives, nor anything relevant in the mysql docs. Is it possible to have two versions of mysql running side-by-side on linux and if so, are there any installation instructions anywhere? I imagine that things such as port numbers, start-up daemons etc have to be configured differently from the standard install. Thanks Adam -- mySQL 4.1.0 + connector/J 3.0.9 + j2sdk 1.4.2 + Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can NOT drop the database
Just got back onto this again now. To quote the docs you pointed me at: If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. With innodb_table_monitor you see a table whose name is #sql..., but since MySQL does not allow accessing any table with such a name, you cannot dump or drop it. The solution is to use a special mechanism available starting from version 3.23.48 of InnoDB. If you have an orphaned table #sql... inside the tablespace, then by calling CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; where the table definition is similar to the temporary table, you can make InnoDB to rename the orphaned table to `rsql..._recover_innodb_tmp_table`. Then you can dump and drop the renamed table. The backquotes around the table name are needed because a temporary table name contains the character '-'. It was a crash during an ALTER TABLE operation, I remember it, but it was a while back so I am unable to tell what these 2 orphan table definitions are - even what the tables were called unfortunately. According to the blurb above I need the definition to rename the orphan table. So it looks like I'm stuck. Even so I tried a few random guesses at the table definitions without getting anywhere. It seems mysql doesn't like my backquotes - with my en_UK keyboard I have ` which I think should be OK, so how come I get an error? Have you got any more suggestions? Thanks Adam On 10/01/2003 03:30 PM Heikki Tuuri wrote: Adam, you can use the innodb_table_monitor http://www.innodb.com/ibman.html#InnoDB_Monitor and the advice at http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict to resolve the problem. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: Adam Hardy [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Wednesday, October 01, 2003 3:24 PM Aihe: Re: can NOT drop the database Hi Heikki, a similar problem happened to me and I got the error: ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30' After reading your post, I checked in the data directory and there are two files there, both of file type data: #sql-2b2_30.frm #sql-ffa_2.frm I certainly didn't put them there myself - I think they must have come from mySQL automatically at some point. Are they real tables in the database? I can't see them when I use 'show tables'. Is it a permissions problem? I have this on another database that I was trying to restore from a dump. I had to rename the database in the end. I am using InnoDB tables for some of my data. Regards Adam -- Running mySQL 4.1.0 on Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tomcat, Connection Pooling, and MySQL
you need to put mysql-connector-javax in tomcat/common/lib and then specify parameter namedriverClassName/name valuecom.mysql.jdbc.Driver/value /parameter in your server.xml Adam On 10/03/2003 04:35 PM Dan Greene wrote: I got it working... unfournately it's on my laptop at home, not here at work with me I think that the issues was that the class names given in the documentation for the jdbc driver for MySQL were wrong look at the listing of the contents of the jar file, and see if you can find the right one... Sorry that I'm being incredibly vague I set it up months ago Dan Greene -Original Message- From: Steven Nakhla [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 9:42 AM To: MySQL Subject: Tomcat, Connection Pooling, and MySQL Has anyone managed to setup Tomcat to use MySQL for database connection pooling? I've found this document which gives information on it: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasourc e-examples-howto.html However, when I try and run it I get messages about not being able to find the hsql driver class. From searching on Google, it seems that this is a common error, but there are no solutions posted. Has anyone managed to get it up and running successfully? I'd really appreciate any advice! Thanks! Steve Nakhla - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- Running mySQL 4.1.0 on Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can NOT drop the database
Hi Heikki, a similar problem happened to me and I got the error: ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30' After reading your post, I checked in the data directory and there are two files there, both of file type data: #sql-2b2_30.frm #sql-ffa_2.frm I certainly didn't put them there myself - I think they must have come from mySQL automatically at some point. Are they real tables in the database? I can't see them when I use 'show tables'. Is it a permissions problem? I have this on another database that I was trying to restore from a dump. I had to rename the database in the end. I am using InnoDB tables for some of my data. Regards Adam On 10/01/2003 09:09 AM Heikki Tuuri wrote: Feng, do you have some non-MySQL file in that database directory under the datadir of MySQL? Naturally, MySQL will not drop the directory if it contains something more than just MySQL tables. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Wang Feng [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, October 01, 2003 8:37 AM Subject: Re: can NOT drop the database Not at all!! after I typed the 'drop database my_account_database', I got the following message: Query OK, 0 rows affected (0.00sec). and I do the 'show databases', that one is still there. cheers, feng - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 3:31 PM Subject: Re: can NOT drop the database no permissions errors ? Greetings, I have a database called 'my_account_database' and I tried to drop it by the command 'drop database my_account_database', it doesn't work. The database is still there, can not be dropped although the tables contained in the database have gone. I subsequently created another 2 databases and could drop them successfully. Tried the 'drop database my_account_database' again before sending this message, still doesn't work. So, what could be the problem? Have you seen this situation before? btw, I can't find out any command which can be used to simply rename a database, please advise!! cheers, feng -- 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] -- Running mySQL 4.1.0 on Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1051: Unknown table on running drop database
I'm getting this error: ERROR 1051: Unknown table '#sql-279_28,#sql-333c_1' after trying to drop a database. I found nothing in the docs or the archives about how to sort it out. Can anybody tell me what steps I should take to remedy this? I'm using 4.1.0 Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set problem Linux - Windows
From a quick search of the online docs, it seems this is what you need: http://www.mysql.com/doc/en/Charset-CONVERT.html but I think it is only in 4.1.0 alpha. Feel free to correct me, I'm no guru here. Adam On 09/18/2003 12:06 PM Marcin Giedz wrote: - Original Message - From: Adam Hardy [EMAIL PROTECTED] To: Marcin Giedz [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:34 AM Subject: Re: Character set problem Linux - Windows Hi Marcin, have you tried using unicode? Not yet Adam but though about it!!! If I change to unicode how to change existing strings with Polish letters in mysql tables?? Marcin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set problem Linux - Windows
Hi Marcin, have you tried using unicode? Adam On 09/17/2003 02:02 PM Marcin Giedz wrote: Hi all, My company(situated in Poland) produce cross platform software based on Borland Delphi/Kylix. We also use mysql server to store all our date.Server is configured with latin2 character set. Nowadays several users work on Linux boxes(Debian + XFree 4.3.1 + KDE 3.1.3 + iso 8859-2 with LC_ALL=pl_PL) and rest on Windows 2000. The following problem occured: - when linux user insert string with Polish letters into table it looks OK but only on Linux application. In windows Polish letters like s with upper mark - and a with lower mark - and also z with upper mark - are changed with +- etc... - when windows user insert string with Polish letters into table it looks OK but only on Windows application. In linux Polish letters like s with upper mark - and a with lower mark - and also z with upper mark - are changed with +- etc... Is it possible to fix it??? Thanks,Marcin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I select the column names?
Hi Jake, I think I know what you mean, but it depends on how you access your db. You want the metadata - in JDBC you would go something like this: metaData = resultSet.getMetaData(); colName1 = metaData.getColumnName(1); On 09/04/2003 02:50 PM Fortuno, Adam wrote: I don't follow your question. Please elaborate. Cheers, Adam -Original Message- From: Jake Johnson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 1:46 AM To: Mysql Subject: How can I select the column names? I am trying to select the column names from a table to be displayed in a web page. Is there anyway to select column names without desc? Thanks, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax on an UPDATE
Hi All, I'm programming a method to delete a parent record and all its children in a child table in one go. This is what I have: UPDATE item i, category_item ci SET i.date_deleted = ?, ci.date_deleted = ? WHERE ci.item_id = i.item_id AND ci.category_id = ? and it works. But then I realised that I have never used this syntax to delete from two tables simultaneously before. I would like to know if it is meant to work, or if it is a dirty hack, and whether it is SQL standard, ie. can I use this if I want to run my app on Oracle? (Not that I do but I might want to sell it to people who do) thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
