Updating mysql database only with delta data
hi I have a huge mysql database on the live server. Every 15 days few 1000 records change in that database [additions/updations/deletions] I don't want to upload the entire mysql database every 15 days as the file size is huge. Is there anyway for me to just upload the delta data and use some script to apply those delta changes on the live server? regards, -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ -- __ IndiaInfo Mail - the free e-mail service with a difference! www.indiainfo.com Check out our value-added Premium features, such as an extra 20MB for mail storage, POP3, e-mail forwarding, and ads-free mailboxes! Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants not entirely propagated to slaves?
Atle Veka wrote: What version of MySQL are you using? Also, are you issuing only GRANT .. statements or modifying the privilege tables manually as well? Search for 'GRANT': http://dev.mysql.com/doc/mysql/en/replication-features.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 1 Apr 2005, Nico Sabbi wrote: hi, my mysql is a 4.0.21. After a flush privileges I can see all granted accesses. Thanks for you help! Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my data?
Hi, check the script /etc/rc.d/mysql which is the SuSE equivalent to mysqld_safe. That's where they set datadir= and you have to change this to reflect your new path. Change the paths to the socket and pid file as well. There should be no problem to move all data to a new place and start the server giving the new datadir on the command line. Then you do host:/etc/rc.d # ./mysql start If you are going to use mysqld_safe then its something like: prompt mysqld_safe --datadir=/data/mysql/mysqldata --user=mysql \ --pid-file=/data/mysql/mysqldata/mysqld.pid \ socket=/data/mysql/mysqldata/mysql.sock Regards, Thomas Spahni On Sat, 2 Apr 2005, kaustubh shinde wrote: Hi, I have suse 9.2 and MySQL 4.21 My basedir is /var/lib/mysql datadir /data/mysql/mysqldata Both the directories and subdirectories and files are owned by user mysq,l group mysql with rights 755. So I guess I have got the permissions part right or so I hope. Everything was working fine till I decided to move the data directory from its previous location to the above one. I had millions of problems after moving the data directory and the database won't start at all. Just to make things work I made the base and data dirs 777. Finally, I can now start it using `mysql.server start` But the good part ends here. I can only see mysql and test databases and i have to log on as root to mysql. My earlier users and databases won't show. Although I have the datafiles at the specified location. This might be coz I ran mysql_install_db again. If I change the rights of base and data dirs to 755, it won't work. I still can't start using mysqld_safe. My /etc/my.cnf has following: [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock user=mysql datadir=/data/mysql/mysqldata bdb_home=/data/mysql/mysqldata [mysql_server] basedir = /var/lib/mysql [mysql.server] basedir = /var/lib/mysql [mysqld_safe] err-log=/var/lib/mysql/mysqld.log innodb_data_home_dir=/data/mysql/mysqldata innodb_data_file_path=ibdata1:10M:autoextend innodb_log_group_home_dir=/data/mysql/ innodb_log_arch_dir=/data/mysql/ and so on.. anyway, so this my.cnf doesn't seem to make any difference. when i try mysqld_safe it gives Starting mysqld-max daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied 050402 07:39:03 mysqld ended tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log and I get 050402 07:40:29 mysqld started 050402 7:40:29 InnoDB: Started 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050402 7:40:29 Aborting 050402 7:40:29 InnoDB: Starting shutdown... 050402 7:40:31 InnoDB: Shutdown completed 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete 050402 07:40:31 mysqld ended and when i try to specify basedir with above command like mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log --basedir=/var/lib/mysql i get 050402 07:38:28 mysqld started 050402 7:38:28 Can't find messagefile '/var/lib/mysql/share/mysql/english/errmsg.sys' 050402 7:38:28 Aborting 050402 07:38:28 mysqld ended wonderful From what I understand, mysqld_safe is supposed to read these options from my.cnf. but its not and i have to specify them on command line. I have spent 4 days and sacrificed an enticing surfing trip on this and feel like i m the dumbest guy on face of earth. every problem seem to spawn off a new one as soon as its solved.. I will really appreciate if someone could just point out the exact problem to me and give a direction. Thanks in advance Kaustubh _ Screensavers unlimited! http://www.msn.co.in/Download/screensaver/ Download now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb - in usage
Hi, I had got webserver with mysql 4.0.20 (if I remember well) compiled from sources on slackware 9. Now, I've bought new machine, and I've installed gentoo with mysql 4.0.22. I've copied (in shell) datadir to new machine, preserving attributes. Now, every of my innodb table in phpmyadmin is not browsable and there are text in usage near name of them. I exported using mysqldump data from old mysql, and tried to do drop dababase `name`; in new one, to delete invalid db, and there was an error like these: unknown table table1,table2,table3 where table1 etc. stands for names of my tables in `name` database. What I should do? -- marcin lewandowski gg# 188068 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb: Alter table progress
Hi, Is there any way of checking the progress of an ALTER TABLE query on an InnoDB table? show innodb status isn't clear. Thanks, -- James Green Systems Administrator, StealthNET Ltd, www.stealthnet.co.uk Tel: 0870 800 1777 Intl: +44 1493 660066 Fax: 0870 135 1069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to run a file in MySQL
Hello all, This is probably really basic for all of you but I have been trying to find it in the manual without success... My question is if it is possible when you are logged in to MySQL to run a file with sql-statements in, instead of sit and execute each statement seperatly. The file I have is a to clean up my DB and to erase data that I don#t want to have, som all rows in the file is normal DELETE-statetments. Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to run a file in MySQL
The command you need is source filename ; Alternatively, if you are outside the mysql clined mysql filename Alec Joppe A [EMAIL PROTECTED] 04/04/2005 09:59 To mysql@lists.mysql.com cc Subject how to run a file in MySQL Hello all, This is probably really basic for all of you but I have been trying to find it in the manual without success... My question is if it is possible when you are logged in to MySQL to run a file with sql-statements in, instead of sit and execute each statement seperatly. The file I have is a to clean up my DB and to erase data that I don#t want to have, som all rows in the file is normal DELETE-statetments. Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to run a file in MySQL
Hi Joppe, Use SOURCE or \. to execute the filename: mysql SOURCE E:\SQL\MySQL\test_tables.sql Database changed ++ | Tables_in_test | ++ | a | | academies | . HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Joppe A [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 11:59 To: mysql@lists.mysql.com Subject: how to run a file in MySQL Hello all, This is probably really basic for all of you but I have been trying to find it in the manual without success... My question is if it is possible when you are logged in to MySQL to run a file with sql-statements in, instead of sit and execute each statement seperatly. The file I have is a to clean up my DB and to erase data that I don#t want to have, som all rows in the file is normal DELETE-statetments. Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Hello. Do you use a VARCHAR type for that column? It's maximum length is limited to 255 characters. I think, switching to TEXT type could solve the problem. [EMAIL PROTECTED] wrote: First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields of 256 char, but I cannot import this tex= t field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating mysql database only with delta data
Hello. MySQL supports incremental backups. See: http://dev.mysql.com/doc/mysql/en/backup.html BG Mahesh [EMAIL PROTECTED] wrote: hi I have a huge mysql database on the live server. Every 15 days few 1000 rec= ords change in that database [additions/updations/deletions] I don't want to upload the entire mysql database every 15 days as the file = size is huge. Is there anyway for me to just upload the delta data and use = some script to apply those delta changes on the live server? regards, -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ --=20 __ IndiaInfo Mail - the free e-mail service with a difference! www.indiainfo.c= om=20 Check out our value-added Premium features, such as an extra 20MB for mail = storage, POP3, e-mail forwarding, and ads-free mailboxes! Powered by Outblaze -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock with innodb
Hello.
What transaction isolation level do you use? By the
way - there's a fresh bug related to SELECT ... FOR UPDATE:
http://bugs.mysql.com/bug.php?id=9512
Philippe Poelvoorde [EMAIL PROTECTED] wrote:
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is the
same across several clients that try to do the same at the same time.
If I do it by hand with two mysql client, it works (one mysqlclient wait
on the SELECT ... FOR UPDATE while i can insert with the other one, then
the SELECT .. FOR UPDATE returns with the first mysqlclient), but with
my applications, I sometimes get an error 1213 (DEADLOCK) from innodb.
The documentation state that the transaction should be rerun. If I do
it, it works fine. What does cause this deadlock ?
If I trace my queries I could see the inserts going _twice_ and one does
fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE in
the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Replication
Hello. Not enough information to make a conclusion. Use SHOW SLAVE STATUS and information from the binary logs to determine the problem. See: http://dev.mysql.com/doc/mysql/en/replication-problems.html David Lloyd [EMAIL PROTECTED] wrote: Hi There, I have a replication setup on my local network (so any updates can be transported at around ethernet speed). Here's the behaviour I see: * MySQL Master - I do a whole slew of drop table and create tables * MySQL Slave - It doesn't pick them up ... until ... - I restart the slave It doesn't appear to have a problem with a single database table being dropped, only when I drop a whole heap at once [I'm replacing the underlying scheme with a heap of drop table ifs followed by create table]. I'm running the official mysql-4.1.10 Apple binaries. One on my OS X server [which is the master] and another on OS X [not server] [which is the slave]. Anywhere I can work out what might be happening or why the updates are being sent through? DSL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my data?
Hello. You may specify the configuration file to mysqld_safe with --defaults-file command line option. `basedir` variable points to location of MySQL installation. Have you installed MySQL to /var/lib/mysql? When you'll be able to login to the server, what does the following statement produce: show variables like 'datadir'; kaustubh shinde [EMAIL PROTECTED] wrote: Hi, I have suse 9.2 and MySQL 4.21 My basedir is /var/lib/mysql datadir /data/mysql/mysqldata Both the directories and subdirectories and files are owned by user mysq,l group mysql with rights 755. So I guess I have got the permissions part right or so I hope. Everything was working fine till I decided to move the data directory from its previous location to the above one. I had millions of problems after moving the data directory and the database won't start at all. Just to make things work I made the base and data dirs 777. Finally, I can now start it using `mysql.server start` But the good part ends here. I can only see mysql and test databases and i have to log on as root to mysql. My earlier users and databases won't show. Although I have the datafiles at the specified location. This might be coz I ran mysql_install_db again. If I change the rights of base and data dirs to 755, it won't work. I still can't start using mysqld_safe. My /etc/my.cnf has following: [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock user=mysql datadir=/data/mysql/mysqldata bdb_home=/data/mysql/mysqldata [mysql_server] basedir = /var/lib/mysql [mysql.server] basedir = /var/lib/mysql [mysqld_safe] err-log=/var/lib/mysql/mysqld.log innodb_data_home_dir=/data/mysql/mysqldata innodb_data_file_path=ibdata1:10M:autoextend innodb_log_group_home_dir=/data/mysql/ innodb_log_arch_dir=/data/mysql/ and so on.. anyway, so this my.cnf doesn't seem to make any difference. when i try mysqld_safe it gives Starting mysqld-max daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied 050402 07:39:03 mysqld ended tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log and I get 050402 07:40:29 mysqld started 050402 7:40:29 InnoDB: Started 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050402 7:40:29 Aborting 050402 7:40:29 InnoDB: Starting shutdown... 050402 7:40:31 InnoDB: Shutdown completed 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete 050402 07:40:31 mysqld ended and when i try to specify basedir with above command like mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log --basedir=/var/lib/mysql i get 050402 07:38:28 mysqld started 050402 7:38:28 Can't find messagefile '/var/lib/mysql/share/mysql/english/errmsg.sys' 050402 7:38:28 Aborting 050402 07:38:28 mysqld ended wonderful From what I understand, mysqld_safe is supposed to read these options from my.cnf. but its not and i have to specify them on command line. I have spent 4 days and sacrificed an enticing surfing trip on this and feel like i m the dumbest guy on face of earth. every problem seem to spawn off a new one as soon as its solved.. I will really appreciate if someone could just point out the exact problem to me and give a direction. Thanks in advance Kaustubh _ Screensavers unlimited! http://www.msn.co.in/Download/screensaver/ Download now! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
Hello. I don't have any ideas at least now. But additional information could be helpful. Do you connect from JBoss to the slave or master server? Please use SHOW PROCESSLIST to find in what state the server threads waste their time. If you find something interesting send it. Include also the output of SHOW STATUS and SHOW VARIABLES. Rafal Kedziorski [EMAIL PROTECTED] wrote: Hi, after extending our MySQL 4.0.23a installation to master-slave configuration two specific queries sended from our JBoss are 25-30 times slower. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements: 1. select count(distinct m.media_id) from category_tree c_tree, media_2_category m2c, media m, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) 2. select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner m2p, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and m2p.media_id = m.media_id and m2p.partner_id = ? and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) order by m2p.priority desc limit ?, ? Times: 1. - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis 2. needed from JBoss 500-800 millis - nedded from normal Java application 19 millis - nedded from normal Java application 20-30 millis All other sql statements generated by JBoss for entity beans are fast like bevore switching to master-slave configuration. Thru this queries the speed of our service is 2-3 times slower. After spend some hours checking our system, I have no more idea where is the problem. This is our my.cnf: [mysqld] datadir=/drbd/mysql log-bin server-id=20 set-variable= key_buffer=128M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= join_buffer_size=8M set-variable= query_cache_size=32M set-variable= record_buffer=4M set-variable= thread_cache_size=400 set-variable= max_connections=300 set-variable= long_query_time=10 log_long_format log_slow_queries innodb_data_file_path = ibdata1:2048M;ibdata2:10M:autoextend #innodb_buffer_pool_size = 384M innodb_buffer_pool_size = 1228M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 Regards, Rafal -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie :create table multi, index
Hello. Use something like: create table user( UserID int primary key, Password varchar (20), User_stats int); See: http://dev.mysql.com/doc/mysql/en/create-table.html Aji Andri [EMAIL PROTECTED] wrote: hi seniors, I'm trying to create a table, here my table properties, create table user ( UserID int primary, Password varchar (20), User_stats int multi ); i'm still confuse in User_stats properti's that is multi, what really use 'multi' is ? and what the conection between primary key and index Thx before the guide, Aji __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants not entirely propagated to slaves?
Hello. If you replicate the privilege tables in the mysql database and update those tables directly without using the GRANT statement, you must issue a FLUSH PRIVILEGES statement on your slaves to put the new privileges into effect. Nico Sabbi [EMAIL PROTECTED] wrote: Hi, it seems my Grants are not entirely propagated from the master to the slave (some are active, some are not). The slave is configured to replicate all databases, and the replication client has all privileges on the master. What is necessary to propagate every single grant? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hello. I don't know. With additional information we could make more exact conclusions. Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, For me it sounds like a glibc issue. BTW, currently the 4.1.10a build is compiled against glibc-2.2, does MySQL plan to build next releases against glibc-2.3 which seems to handle much better a high number of simultaneous connected threads ? Thanks ! Jocelyn Gleb Paharenko wrote: Hello. Please switch to the mysql-debug-4.1.10a version and send the error log with resolved stack trace. Include the output of the following statement as well: SHOW VARIABLES; I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. What about ulimits and free memory of your system? Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, Yes - am using the standard binaries and have even upgraded to mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error - does anyone have any ideas?=20 Cheers, Andrew -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thu 31 March 2005 02:31 To: mysql@lists.mysql.com Subject: Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, =20 I'm getting this strange error when there are more than 1100 mysql=20 connections connected to the same server. =20 [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of=20 available memory, you can consult the manual for a possible=20 OS-dependent bug' =20 I've had this running fine in the past with MySQL 4.0.17 and Red Hat=20 7.3 (linux 2.4..) but with the same hardware and MySQL versions using=20 Fedora core 2 (linux 2.6) I am getting these problems. =20 I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. =20 Has anyone had this before? =20 I would love to hear your thoughts and ideas.. =20 Cheers for the help, =20 Andrew =20 SQL, Query =20 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL to MSSQL
I posted a while back for a solution to make a MySQL DB into a MSQL DB can anyone help please? Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find missing record?
Jeremy Cole wrote: Hi, Both tables should hold the same number of records. However, I've discovered that 'Close' is one less than 'Open' (1693 vs 1694). How can I find out which record is missing from 'Close'? I know it's not the case of an extra entry in 'Open' because 1694 divides evenly by 7, whereas 1693 doesn't. This should do it: SELECT Open.id, Open.day FROM Open LEFT JOIN Close ON Open.id=Close.id AND Open.day=Close.day WHERE Close.id IS NULL Regards, Jeremy Excellent! Thank you. I had tried a few left joins but none of them worked. -- Amer Neely, Softouch Information Services Home of Spam Catcher W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | PHP | MySQL | CGI programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
resolving ambiguous column name in subquery
Hi, the following seems wrong to me. Not sure whether this is a bug. In short: a column-name in a subquery can refer to a table *outside* of the subquery (fair enough), but if the column-name is ambiguous between *inside* and *outside*, the parser assumes that it refers to the *inside* context. I would have thought it better to reject the query where it is ambiguous. Tom. drop table t1; create table t1 (c1 int); drop table t2; create table t2 (c2 int); delete from t1 where c1 not in (select c1 from t2); works, slightly confusing, but reasonable -- drop table t1; create table t1 (c int); drop table t2; create table t2 (c int); delete from t1 where c not in (select c from t2); this query works, but resolves the ambiguity in the *opposite* way to above. The subquery column now refers to the inside subquery. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
float type / concat
Hi list,
I'm using php/mysql, I was updating a table through phpmyadmin then I saw
that to update all the columns which type is declared to float the
developper of phpmyadmin have added a concat.
Something like:
UPDATE `tableInduction` SET `inductionType` = 'screening' AND CONCAT(
`volume` ) = '1' AND `growthVessel` = '24-96 well plates' AND CONCAT(
`pH_start` ) = '2'.
the column volume and pH_start are of type float, and it's the same for all
the columns I declare as float.
I suppose that this has some purpose, but I don't understand.
concat is an operator to merge 2 strings, for example concat
('string1','string2') should return string1string2.
What's the point of doing that for column's name?Is it a kind of cast
because '1' is considered as string because of the quotes?
In the manual I saw:
select concat(14.3) gives as a result '14.3'.
Is that the same kind of cast?
Why isn't that done for integers?
Additionally, I want to store 1 or 2 decimals floating point number, what is
the best?float?double?
I saw that using float may give you some unexpected problems, as all
calculations in MySQL are done with double-precision.
Thanks for your advice,
Melanie
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Line Breaks Problem
Hello,
I would like to describe the following problem and get an opinien
from list members.
My database values contains line breaks ('\r\n').
For example I have 1 row with 'value\r\n' in 'column_name'.
SELECT HEX(column_name)FROM table_name;
will return
76616C75650D0A
I'm running the following query (I want to get records and DO NOT
use '\r\n' im my WHERE clause)
SELECT * FROM table_name WHERE column_name='value';
On MySQL Ver 11.18 Distrib 3.23.55 that query returns 1 row,
on MySQL Ver 14.7 Distrib 4.1.9 empty set is return.
I did not found anything in change log files about that difference.
So, I wonder if behavior of newer MySQL version is feature or bug or
bug fix. What is correct?
Thanks!
--
Best regards,
Andrew Bidochko
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: resolving ambiguous column name in subquery
I take it all back. I see now why this behaviour is desirable, or at least standard. E.g., see: https://aurora.vcu.edu/db2help/db2s0/c2corr.htm On Apr 4, 2005 2:40 PM, Tom Cunningham [EMAIL PROTECTED] wrote: Hi, the following seems wrong to me. Not sure whether this is a bug. In short: a column-name in a subquery can refer to a table *outside* of the subquery (fair enough), but if the column-name is ambiguous between *inside* and *outside*, the parser assumes that it refers to the *inside* context. I would have thought it better to reject the query where it is ambiguous. Tom. drop table t1; create table t1 (c1 int); drop table t2; create table t2 (c2 int); delete from t1 where c1 not in (select c1 from t2); works, slightly confusing, but reasonable -- drop table t1; create table t1 (c int); drop table t2; create table t2 (c int); delete from t1 where c not in (select c from t2); this query works, but resolves the ambiguity in the *opposite* way to above. The subquery column now refers to the inside subquery. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X and MySQL table corruption...
I have been running into issues with MySQL table corruption issues on a couple of OS X systems. I end up with tables that need repair every day and some times multiples times per day. It's so bad now that I have a script that runs the mysql 'REPAIR TABLE...' command and then the myisamck command on all the tables afterwards. I currently admin 3 OS X systems each with MySQL / PHP based websites: - G4 Tower with 10.2.x client - G4 Tower with 10.3.8 server - Dual G5 Xserve with 10.3.8 server Each of the G4's exhibits the problem. I am only developing the G5 site and have not seen the issue yet on this system. On each system I upgraded the default Apple mysql install with the pkg installer supplied by MySQL. The 10.2 system I am still at MySQL 4.0.x and the 10.3 system I am at 4.1.10 (the latest release). When I upgraded I did discover that there were some issues with the new version and the old Apple supplied build. Apple installs theirs in /bin/ and the MySQL package in /usr/local/bin/. Early on I was running my mysql* command i.e. myisamck etc with specifying a path. On the 10.2 system I ended up running the 3.* Apple versions of MySQL on my 4.x tables royally screwing them up. I went for far as to delete the old /bin/mysql* files. On the new 10.3 G4 system I learned from my mistakes in took care of he path issues right away so I am pretty sure that I did not cause the corruption by manually using the old /bin/ binaries. I have a suspicion that this might still be part of the problem. I never had an issue with mysql when I was using MySQL 3.x. I built that 10.2 system when Jaguar was release (end of 2002 I believe) and it ran 24/7 and I cant recall ever seeing these issues before. I switched to 4.x for many of the new features that were lacking in 3.x. I was wondering if any one else out there is running MySQL 4.x on OS X and has seen any issues in this regard. I have run Tech Tool on both systems and done full scans. I have a good UPS on both systems and the new 10.3 system had brand new drives. Given all of this I am convinced either I am unlucky and have some obscure issues on both systems or there is some issue with MySQL 4.x on OS X or a conflict somewhere with the older Apple versions supplied with the OS. Thanks, Dan T -- Dan Tappin, P.Eng. - Senior Engineer O'Rourke Engineering Limited Calgary, AB (403) 298-9639 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery substitute in 4.0?
How can i do the following with 4.0? delete fom t1 where id in (select id from t1 where usr_id = 10 order by date_inserted limit 4,999) The ideia is to limit to 4 rows with the same usr_id value before i do an new insert with that user_id. Ending up with only 5 rows. I'm trying to not do a select with the Id's and dates and then sending the delete queries. But i'm clueless on how to do this being stuck to 4.0. Appreciate any hints. Thanks, Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock with innodb
Gleb Paharenko wrote:
Hello.
What transaction isolation level do you use? By the
way - there's a fresh bug related to SELECT ... FOR UPDATE:
http://bugs.mysql.com/bug.php?id=9512
tx_isolation is set to : REPEATABLE-READ (which is the default)
I've stripped everything uneeded from my code, and printf debug messages
before the query (the string sent to mysql_query ) and a printf after
the query returns. I do the query with two application in parallel,
sleep for 3 seconds after SELECT .. FOR UPDATE then carry on with the
rest of the code. So if I understand well the SELECT ... FOR UPDATE
statement, one of the application should hang on mysql_query() (from the
C api) until the other one either do a COMMIT or a ROLLBACK. But from
what I observe is that the 2 queries goes through _and_ returns. (thus i
get two empty sets and try to insert twice the various records in my two
tables)
If I try to do it on the command line, it works as expected.
Can this be related to the libmysql library ? I'm using on the client
side the version that comes with 4.0.20a for windows, and the server is
a 4.1.10a (Linux).
Thanks for your help,
Philippe Poelvoorde [EMAIL PROTECTED] wrote:
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is the
same across several clients that try to do the same at the same time.
If I do it by hand with two mysql client, it works (one mysqlclient wait
on the SELECT ... FOR UPDATE while i can insert with the other one, then
the SELECT .. FOR UPDATE returns with the first mysqlclient), but with
my applications, I sometimes get an error 1213 (DEADLOCK) from innodb.
The documentation state that the transaction should be rerun. If I do
it, it works fine. What does cause this deadlock ?
If I trace my queries I could see the inserts going _twice_ and one does
fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE in
the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sparc vs. x86 Solaris MySQL compatibility
Hi, We're running MySQL on Sparc Solaris now, but are considering moving to an Opteron Solaris box for price/performance reasons. Does anyone have any comments about MySQL's relative stability or performance on the two platforms? Also, are MySQL databases binary-compatible on the two platforms? If not, how would we convert? Thanks in advance for any help... Gary -- Gary Robinson CTO Emergent Music, LLC [EMAIL PROTECTED] 207-942-3463 Company: http://www.goombah.com Blog:http://www.garyrobinson.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery substitute in 4.0?
In article [EMAIL PROTECTED], Gabriel B. [EMAIL PROTECTED] writes: How can i do the following with 4.0? delete fom t1 where id in (select id from t1 where usr_id = 10 order by date_inserted limit 4,999) Put the result of the inner SELECT into a temporary table and then use the multi-table delete idiom of 4.0. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Michael, my problem is that I need a filed with precision for a field of exactly 595 characters! Only text field type with precision is the char type but its limit is 256 char. I've tried with text type, but precision were been ignored and my sql silently truncate it at 256 value. I solved my problem creating a temp table with my field splitted in three char fields: 250+250+95, then I've rebuild each line with the function concat()! Now I've another problem: after I've imported all txt files with a fully automated query, I need to import into a table with a blob field, a lot of little msword documents. Each record should have a single word file. I'd like to write a query (I hope without using api as php or other languages) that imports automatically all .doc files stored ina dir. Have any idea? Thanks Stefano -- Messaggio originale -- Subject: Re: Load data infile and text fields From: Michael Dykman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Date: Sat, 02 Apr 2005 09:18:56 -0500 What is the structure of the table you are importing to? you might have merely hit the natural limit of the column type. - michael dykman On Sat, 2005-04-02 at 06:51, [EMAIL PROTECTED] wrote: First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields of 256 char, but I cannot import this text field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem doing insert on a datetime field
I am having a problem doing an insert on a datetime field. Is there something I have to use to convert it to that form? I have it set up in a string as '2000/09/17 00:00:00' but that does not seem to work. Thanks for any help you can offer. Bob Rawlinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my data?
Thank you very much. that was a real life saver. u rock :) Regards Kaustubh Original Message Follows From: Thomas Spahni lt;[EMAIL PROTECTED]gt; To: kaustubh shinde lt;[EMAIL PROTECTED]gt; CC: mysql@lists.mysql.com Subject: Re: where is my data? Date: Mon, 4 Apr 2005 12:03:05 +0200 (CEST) Hi, check the script /etc/rc.d/mysql which is the SuSE equivalent to mysqld_safe. That's where they set datadir= and you have to change this to reflect your new path. Change the paths to the socket and pid file as well. There should be no problem to move all data to a new place and start the server giving the new datadir on the command line. Then you do host:/etc/rc.d # ./mysql start If you are going to use mysqld_safe then its something like: promptgt; mysqld_safe --datadir=/data/mysql/mysqldata --user=mysql \ --pid-file=/data/mysql/mysqldata/mysqld.pid \ socket=/data/mysql/mysqldata/mysql.sock Regards, Thomas Spahni On Sat, 2 Apr 2005, kaustubh shinde wrote: gt; Hi, gt; I have suse 9.2 and MySQL 4.21 gt; My basedir is /var/lib/mysql gt; datadir /data/mysql/mysqldata gt; Both the directories and subdirectories and files are owned by user mysq,l gt; group mysql with rights 755. So I guess I have got the permissions part gt; right or so I hope. gt; Everything was working fine till I decided to move the data directory from gt; its previous location to the above one. gt; I had millions of problems after moving the data directory and the database gt; won't start at all. gt; Just to make things work I made the base and data dirs 777. Finally, I can gt; now start it using `mysql.server start` gt; But the good part ends here. I can only see mysql and test databases and i gt; have to log on as root to mysql. My earlier users and databases won't show. gt; Although I have the datafiles at the specified location. This might be coz gt; I ran mysql_install_db again. If I change the rights of base and data dirs gt; to 755, it won't work. gt; gt; I still can't start using mysqld_safe. gt; gt; My /etc/my.cnf has following: gt; gt; [mysqld] gt; port= 3306 gt; socket = /var/lib/mysql/mysql.sock gt; user=mysql gt; datadir=/data/mysql/mysqldata gt; bdb_home=/data/mysql/mysqldata gt; gt; [mysql_server] gt; basedir = /var/lib/mysql gt; gt; [mysql.server] gt; basedir = /var/lib/mysql gt; gt; [mysqld_safe] gt; err-log=/var/lib/mysql/mysqld.log gt; gt; gt; innodb_data_home_dir=/data/mysql/mysqldata gt; innodb_data_file_path=ibdata1:10M:autoextend gt; innodb_log_group_home_dir=/data/mysql/ gt; innodb_log_arch_dir=/data/mysql/ gt; gt; and so on.. gt; anyway, so this my.cnf doesn't seem to make any difference. when i try gt; gt; mysqld_safe amp; it gives gt; gt; Starting mysqld-max daemon with databases from /var/lib/mysql gt; /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: gt; Permission denied gt; /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: gt; Permission denied gt; STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid gt; tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied gt; 050402 07:39:03 mysqld ended gt; tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied gt; gt; gt; So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log amp; gt; and I get gt; gt; 050402 07:40:29 mysqld started gt; 050402 7:40:29 InnoDB: Started gt; 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' gt; doesn't exist gt; 050402 7:40:29 Aborting gt; gt; 050402 7:40:29 InnoDB: Starting shutdown... gt; 050402 7:40:31 InnoDB: Shutdown completed gt; 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete gt; gt; 050402 07:40:31 mysqld ended gt; gt; gt; and when i try to specify basedir with above command like gt; mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log gt; --basedir=/var/lib/mysql amp; gt; gt; i get gt; 050402 07:38:28 mysqld started gt; 050402 7:38:28 Can't find messagefile gt; '/var/lib/mysql/share/mysql/english/errmsg.sys' gt; 050402 7:38:28 Aborting gt; gt; 050402 07:38:28 mysqld ended gt; gt; wonderful gt; gt; gt;From what I understand, mysqld_safe is supposed to read these options from gt; my.cnf. but its not and i have to specify them on command line. gt; gt; I have spent 4 days and sacrificed an enticing surfing trip on this and feel gt; like i m the dumbest guy on face of earth. every problem seem to spawn off a gt; new one as soon as its solved.. gt; gt; I will really appreciate if someone could just point out the exact problem gt; to me and give a direction. gt; gt; Thanks in advance gt; Kaustubh gt; gt; _ gt; Screensavers unlimited! http://www.msn.co.in/Download/screensaver/ Download gt; now! gt; gt; gt; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Writing a query to load all files from a local directory into a table as blob
If my poor english assists me, I'd like to ask you about how can I import a lot of small msword files into a mysql table where I've created a column definied as longblob. I'm using mysql 4.1 and I would avoid to start studing php or other language at this moment. In other word, my situation is: 1) weekly I receive a floppy from another company reporting a lot of business operations 2) this floppy has two types of information: structurated and not structurated and I need to import all into a mysql 4.1 database. For txt structurated files I've already written a query that automatically load all informations and insert them into the correct tables. The files has always the same name, so my query is fully automatic :-). For not structurated files (msword .doc files) I cannot find a solution. Every week they have a different name, so I need a procedure that look into a dir and get all .doc files and load each one into a record of my table. I'm using mysql server 4.1 over a freebsd machine, but all clients run under windows xp. Thanks for help Stefano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my data?
On Apr 2, 2005, at 7:58 AM, kaustubh shinde wrote: Hi, I have suse 9.2 and MySQL 4.21 My basedir is /var/lib/mysql datadir /data/mysql/mysqldata As it stands now, mysqld_safe is broken when you move your data directory, so this may cause problems. (See bug 7249 http://bugs.mysql.com/bug.php?id=7249.) In this case, however, I think this is not the problem. Both the directories and subdirectories and files are owned by user mysql, group mysql with rights 755. So I guess I have got the permissions part right or so I hope. datadir should be owned by mysql, group mysql, with permissions set to 700 or 750. Everything was working fine till I decided to move the data directory from its previous location to the above one. I had millions of problems after moving the data directory and the database won't start at all. Exactly. Where was it before? Just to make things work I made the base and data dirs 777. Finally, I can now start it using `mysql.server start` Don't do that! It's insecure. If this made a difference, it means you have problems you should fix. This just hides them, and exposes your server. But the good part ends here. I can only see mysql and test databases and i have to log on as root to mysql. My earlier users and databases won't show. Although I have the datafiles at the specified location. This might be coz I ran mysql_install_db again. When you ran mysql_install_db, you got a new data directory in the default location (presumably the same place your data used to be) with the default dbs, mysql and test. The default mysql db has user root and the anonymous user, '', each with no password. mysqld is now working because it is using this data directory, instead of yours. If I change the rights of base and data dirs to 755, it won't work. This is because you've made a bad choice of location (basedir) for your log and socket files. User mysql must be able to write to the directory where these will go, but user mysql should not be able to write to basedir (basedir should be owned by root). You need to move these to a directory owned by mysql (datadir) or a directory where everyone can write (e.g. /tmp). I still can't start using mysqld_safe. My /etc/my.cnf has following: [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock /data/mysql/mysqldata/mysql.sock or /tmp/mysql.sock should work. user=mysql datadir=/data/mysql/mysqldata bdb_home=/data/mysql/mysqldata [mysql_server] basedir = /var/lib/mysql [mysql.server] basedir = /var/lib/mysql [mysqld_safe] err-log=/var/lib/mysql/mysqld.log /data/mysql/mysqldata/mysqld.log should work. innodb_data_home_dir=/data/mysql/mysqldata innodb_data_file_path=ibdata1:10M:autoextend innodb_log_group_home_dir=/data/mysql/ innodb_log_arch_dir=/data/mysql/ and so on.. anyway, so this my.cnf doesn't seem to make any difference. when i try mysqld_safe it gives Starting mysqld-max daemon with databases from /var/lib/mysql Apparently, your distribution expects the data directory to be /var/lib/mysql, but that's your basedir. Hmm... /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied 050402 07:39:03 mysqld ended tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log and I get 050402 07:40:29 mysqld started 050402 7:40:29 InnoDB: Started 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050402 7:40:29 Aborting Right, the data directory wasn't found. 050402 7:40:29 InnoDB: Starting shutdown... 050402 7:40:31 InnoDB: Shutdown completed 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete 050402 07:40:31 mysqld ended and when i try to specify basedir with above command like mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log --basedir=/var/lib/mysql i get 050402 07:38:28 mysqld started 050402 7:38:28 Can't find messagefile '/var/lib/mysql/share/mysql/english/errmsg.sys' 050402 7:38:28 Aborting 050402 07:38:28 mysqld ended Wait a minute. You don't have share in /var/lib/mysql? If you set basedir, you are telling mysqld that everything needed to run can be found in subdirectories of basedir (bin, lib, man, share, etc.) OK, I'm guessing that this is an RPM install. /var/lib/mysql is not really your basedir, it is simply the old data directory. In that case, you need to remove all the basedir = /var/lib/mysql lines in your config file(s), and change the socket and log files to go in the new datadir, /data/mysql/mysqldata/. Then you ought to be able to start with just `mysqld_safe `. To be safe, since /var/lib/mysql is the compiled-in default location for the data directory, and you've moved your data directory to
Can a Function return multiple values?
I am trying to return multiple values from a Function, not sure if it is allowed: delimiter | create function cf_test() returns integer, varchar(255)/* tried with semicolon after varchar(255) */ begin declare p_col2 smallint; declare p_col3 varchar(255); select col2, col3 into p_col2, p_col3 from test where col1 = 1; return p_col1, p_col2; end | and i get the following error when i am trying to create this function: source test5.sql ERROR 1064 (42000): 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 ' varchar(255); begin declare p_col2 smallint; declare p_col3 varch' at line 2 Any advice would be greatly appreciated. Thanks Dinesh
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
Howdy all, I keep getting this error when I try to issue: mysql mysql -u root -p the same thing happens when I substitute any other database name, not just mysql. I have another,nearly identical machine from which I copied the contents of /var/lib/mysql/mysql/ . The permissions all appear to be fine. This is a new problem, it started after I changed the firewall settings to activate port 3306.. don't know if that's what caused this somehow. Oddly enough, I can access the database from a remote machine with no problem. Any help would be much appreciated. sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X and MySQL table corruption...
I've been running on OS X for a while, although I haven't had any really heavy usage sites. I also haven't had a single corruption problem on a live database in the 2+ years I've been running MySQL. Now, I say on a live database. For the first time I tried upgrading the MySQL that comes with OSX Server. I've always used OSX client with MySQL installed. The standard MySQL installation puts everything in /usr/local/bin, Apple puts some stuff in /bin, but other MySQL pieces in other places. After upgrading MySQL on OSX Server, I couldn't get it to run until I launched Apple's MySQL Manager, which is just a start and stop button really. Even then things weren't running right. I ended up searching for and deleting all the pieces of MySQL I could find, then installing the MySQL package again. What I thought was a clean install still wasn't working. Checking things over, I realized that permissions were wrong on some of the MySQL directories. I had forgotten to delete the MySQL receipts file (/Library/Receipts), which stores what OSX thinks the correct permissions should be. I fixed the permissions manually and everything now seems to be running fine on the test system running OS Server 10.3 on a 400Mhz G4 (ugh!). So, if you are trying to upgrade MySQL on OSX Server, it's far from straight forward. Make sure you delete all of Apple's installed pieces and delete the receipts file, then try installing the MySQL package. On Apr 4, 2005, at 11:00 AM, Dan Tappin wrote: I have been running into issues with MySQL table corruption issues on a couple of OS X systems. I end up with tables that need repair every day and some times multiples times per day. It's so bad now that I have a script that runs the mysql 'REPAIR TABLE...' command and then the myisamck command on all the tables afterwards. I currently admin 3 OS X systems each with MySQL / PHP based websites: - G4 Tower with 10.2.x client - G4 Tower with 10.3.8 server - Dual G5 Xserve with 10.3.8 server Each of the G4's exhibits the problem. I am only developing the G5 site and have not seen the issue yet on this system. On each system I upgraded the default Apple mysql install with the pkg installer supplied by MySQL. The 10.2 system I am still at MySQL 4.0.x and the 10.3 system I am at 4.1.10 (the latest release). When I upgraded I did discover that there were some issues with the new version and the old Apple supplied build. Apple installs theirs in /bin/ and the MySQL package in /usr/local/bin/. Early on I was running my mysql* command i.e. myisamck etc with specifying a path. On the 10.2 system I ended up running the 3.* Apple versions of MySQL on my 4.x tables royally screwing them up. I went for far as to delete the old /bin/mysql* files. On the new 10.3 G4 system I learned from my mistakes in took care of he path issues right away so I am pretty sure that I did not cause the corruption by manually using the old /bin/ binaries. I have a suspicion that this might still be part of the problem. I never had an issue with mysql when I was using MySQL 3.x. I built that 10.2 system when Jaguar was release (end of 2002 I believe) and it ran 24/7 and I cant recall ever seeing these issues before. I switched to 4.x for many of the new features that were lacking in 3.x. I was wondering if any one else out there is running MySQL 4.x on OS X and has seen any issues in this regard. I have run Tech Tool on both systems and done full scans. I have a good UPS on both systems and the new 10.3 system had brand new drives. Given all of this I am convinced either I am unlucky and have some obscure issues on both systems or there is some issue with MySQL 4.x on OS X or a conflict somewhere with the older Apple versions supplied with the OS. Thanks, Dan T -- Dan Tappin, P.Eng. - Senior Engineer O'Rourke Engineering Limited Calgary, AB (403) 298-9639 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
never mind.. works now.. 'localhost' not matched by '%' for some reason.
-Original Message- From: Samuel Flores [mailto:[EMAIL PROTECTED] Sent: Monday, April 04, 2005 12:52 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: ERROR 1044: Access denied for user: '@localhost' to database 'mysql' Howdy all, I keep getting this error when I try to issue: mysql mysql -u root -p the same thing happens when I substitute any other database name, not just mysql. I have another,nearly identical machine from which I copied the contents of /var/lib/mysql/mysql/ . The permissions all appear to be fine. This is a new problem, it started after I changed the firewall settings to activate port 3306.. don't know if that's what caused this somehow. Oddly enough, I can access the database from a remote machine with no problem. Any help would be much appreciated. sam -- 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]
Temporal databases MySQL
Greetings everyone, I haven't been able to find any piece of information on temporal databases and MySQL except the code from the TAU Project which seems to be experimental at best. Not even discussions on the mailing lists or on the forums but while I was looking for temporal, people may have been referring to this thing in other terms. And so I'm asking : Are there any obvious resources I've missed or would it be simpler just to post the issues in here ? Thank you, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL OUTER JOIN
Hello all, mysql 4.0.20 I'd like to know how one can do a full outer join. I've read some workaround with a UNION, but i need the join only on a few columns, while UNION will make double tuple if one column is not the same. I also would like to avoid temporary table if possible, since the query is on many millions of rows, and i saw performances suffer when working on multiple temporary tables. If anyone has an idea, he would be great. Thanks you Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Tuning - Table Joins
I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for taking a look. There is no error messages that can be posted, so I will try and describe what's happening as best I can. I am joining 3 tables in one query. I have had numerous people examine the queries and all have given their stamp of approval. What happens when I run it is MySQL takes the processor for a ride, spiking it to 100% until I restart mysqld. The tables range from 50,000 to 85,000 records, and the join is only supposed to return 1 record. My question to you is this: are there changes I can make to the configuration to improve performance? --or-- is data de-normalization my best option? Is there any more information you need from me to answer this question? Current setup: 2.4ghz Pentium 4, 1gb ram, 360gb 4-disc raid 5 array w/ 3ware chassis and card, fedora core 3 w/ all patches and updates, selinux -disabled-, mysql 4.1.10a, MyISAM table format. Again, thank you all in advance, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X and MySQL table corruption...
Ave, I run MySQL 4.x on my Power Mac G5 with Mac OS X 10.3.8 with PHP5. I have been running Apache Web Server and my websites on this machine for almost 6 months now. And twice I have faced table corruption which I had to fix using REPAIR TABLE. Twice in 6 months isn't bad at all, yet, I wonder why. Rahul S. Johari Coordinator, Internet Administration Informed Marketing Services Inc. 251 River Street Troy, NY 12180 Tel: (518) 266-0909 x154 Fax: (518) 266-0909 Email: [EMAIL PROTECTED] http://www.informed-sources.com On 4/4/05 12:59 PM, Brent Baisley [EMAIL PROTECTED] wrote: I've been running on OS X for a while, although I haven't had any really heavy usage sites. I also haven't had a single corruption problem on a live database in the 2+ years I've been running MySQL. Now, I say on a live database. For the first time I tried upgrading the MySQL that comes with OSX Server. I've always used OSX client with MySQL installed. The standard MySQL installation puts everything in /usr/local/bin, Apple puts some stuff in /bin, but other MySQL pieces in other places. After upgrading MySQL on OSX Server, I couldn't get it to run until I launched Apple's MySQL Manager, which is just a start and stop button really. Even then things weren't running right. I ended up searching for and deleting all the pieces of MySQL I could find, then installing the MySQL package again. What I thought was a clean install still wasn't working. Checking things over, I realized that permissions were wrong on some of the MySQL directories. I had forgotten to delete the MySQL receipts file (/Library/Receipts), which stores what OSX thinks the correct permissions should be. I fixed the permissions manually and everything now seems to be running fine on the test system running OS Server 10.3 on a 400Mhz G4 (ugh!). So, if you are trying to upgrade MySQL on OSX Server, it's far from straight forward. Make sure you delete all of Apple's installed pieces and delete the receipts file, then try installing the MySQL package. On Apr 4, 2005, at 11:00 AM, Dan Tappin wrote: I have been running into issues with MySQL table corruption issues on a couple of OS X systems. I end up with tables that need repair every day and some times multiples times per day. It's so bad now that I have a script that runs the mysql 'REPAIR TABLE...' command and then the myisamck command on all the tables afterwards. I currently admin 3 OS X systems each with MySQL / PHP based websites: - G4 Tower with 10.2.x client - G4 Tower with 10.3.8 server - Dual G5 Xserve with 10.3.8 server Each of the G4's exhibits the problem. I am only developing the G5 site and have not seen the issue yet on this system. On each system I upgraded the default Apple mysql install with the pkg installer supplied by MySQL. The 10.2 system I am still at MySQL 4.0.x and the 10.3 system I am at 4.1.10 (the latest release). When I upgraded I did discover that there were some issues with the new version and the old Apple supplied build. Apple installs theirs in /bin/ and the MySQL package in /usr/local/bin/. Early on I was running my mysql* command i.e. myisamck etc with specifying a path. On the 10.2 system I ended up running the 3.* Apple versions of MySQL on my 4.x tables royally screwing them up. I went for far as to delete the old /bin/mysql* files. On the new 10.3 G4 system I learned from my mistakes in took care of he path issues right away so I am pretty sure that I did not cause the corruption by manually using the old /bin/ binaries. I have a suspicion that this might still be part of the problem. I never had an issue with mysql when I was using MySQL 3.x. I built that 10.2 system when Jaguar was release (end of 2002 I believe) and it ran 24/7 and I cant recall ever seeing these issues before. I switched to 4.x for many of the new features that were lacking in 3.x. I was wondering if any one else out there is running MySQL 4.x on OS X and has seen any issues in this regard. I have run Tech Tool on both systems and done full scans. I have a good UPS on both systems and the new 10.3 system had brand new drives. Given all of this I am convinced either I am unlucky and have some obscure issues on both systems or there is some issue with MySQL 4.x on OS X or a conflict somewhere with the older Apple versions supplied with the OS. Thanks, Dan T -- Dan Tappin, P.Eng. - Senior Engineer O'Rourke Engineering Limited Calgary, AB (403) 298-9639 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning - Table Joins
On Apr 4, 2005, at 1:22 PM, Jason Johnson wrote: I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for taking a look. There is no error messages that can be posted, so I will try and describe what's happening as best I can. I am joining 3 tables in one query. I have had numerous people examine the queries and all have given their stamp of approval. What happens when I run it is MySQL takes the processor for a ride, spiking it to 100% until I restart mysqld. The tables range from 50,000 to 85,000 records, and the join is only supposed to return 1 record. With proper indexing, this should be quick. My question to you is this: are there changes I can make to the configuration to improve performance? --or-- is data de-normalization my best option? Neither. At least, not until we make sure the query is correct and the tables are properly indexed. Is there any more information you need from me to answer this question? Yes. Please provide the query, and the output of EXPLAIN on your query http://dev.mysql.com/doc/mysql/en/explain.html. The output for each table of SHOW CREATE TABLE tablename, or at least SHOW INDEXES FROM tablename, would be useful. A brief description of the point of the query, if it isn't obvious, would also help. Current setup: 2.4ghz Pentium 4, 1gb ram, 360gb 4-disc raid 5 array w/ 3ware chassis and card, fedora core 3 w/ all patches and updates, selinux -disabled-, mysql 4.1.10a, MyISAM table format. Again, thank you all in advance, Jason Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X and MySQL table corruption...
Hello, We have been running MySQL (about 50 databases, some medium-sized, some small, mix of MyISAM and InnoDB) on Mac OS X Client for more than a year (currently 10.3.8 with MySQL 4.1.10) and I have never seen any corrupt tables. We use only the MySQL-provided packages for our binaries. Maybe this will clean things up for good: (1) dump all databases; (2) remove all mysql binaries from your server; (3) fresh install of MySQL-provided (/usr/local/mysql) installation; (4) restore db's from dump. JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning - Table Joins
The premise of the query is to return required continuing education hours for the entire membership of the organization. Limited to one member when providing a membership ID. The query is a little bulky, and fortunately I cannot take credit for its design, but here goes (keep in mind that some of the values used in the where clauses are dynamically inserted): select memupdate.MemID, memupdate.Admit, memupdate.Birth, memupdate.Salut, memupdate.First, memupdate.Middle, memupdate.Last, memupdate.Company, memupdate.Add1, memupdate.Add2, memupdate.City, memupdate.State, memupdate.Zip, tblcc.grp0, tblcc.appl_year, tblcc.date_taken, tblcc.sponsor, tblcc.course, tblcc.appl_hrs_04, tblcc.appl_hrs_03, tblcc.appl_hrs_02, tblcc.appl_hrs_01, tblcd.Type, tblcd.title, tblsp.name AS SponsorName from ( ( ( memupdate inner join tblcc on memupdate.MemID = tblcc.member ) inner join tblcd on ( tblcc.course = tblcd.course ) and ( tblcc.sponsor = tblcd.sponsor ) and ( tblcc.course_grp0 = tblcd.grp0 ) and ( tblcc.dater = tblcd.dater ) and ( tblcc.sub = tblcd.sub ) ) inner join tblsp on ( tblcd.grp0 = tblsp.grp0 ) and ( tblcd.sponsor = tblsp.sponsor ) ) where memupdate.MemID = 300 and ( ( tblcc.appl_year ) = 2004 and ( tblcc.appl_year ) = 2005 ) order by tblcc.date_taken; Output of explain (note, 4 tables instead of the 3 I had mentioned): | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+- +--+---+-+ | 1 | SIMPLE | tblsp | ALL | NULL | NULL |NULL | NULL | 4082 | Using temporary; Using filesort | | 1 | SIMPLE | tblcd | ALL | NULL | NULL |NULL | NULL | 11563 | Using where | | 1 | SIMPLE | memupdate | ALL | NULL | NULL |NULL | NULL | 44059 | Using where | | 1 | SIMPLE | tblcc | ALL | NULL | NULL |NULL | NULL | 84567 | Using where | I must point out that when you see this, it may cause an adverse physical reaction which may include vomiting and/or heaving. The data is coming in from another source and unfortunately has to be typed this way. I'm not in control of how I get it, though I can lay the smack down on how it's handed off if need be. Also, to my surprise, these tables have been created using InnoDB, I apologize for misleading you in my first message. CREATE TABLE `tblsp` ( `record_status` varchar(255) NOT NULL default '', `grp0` varchar(255) NOT NULL default '', `sponsor` varchar(255) NOT NULL default '', `grp1` varchar(255) NOT NULL default '', `date_maint` varchar(255) NOT NULL default '', `who_maint` varchar(255) NOT NULL default '', `seq_maint` varchar(255) NOT NULL default '', `name` varchar(255) NOT NULL default '', `contact` varchar(255) NOT NULL default '', `addr1` varchar(255) NOT NULL default '', `addr2` varchar(255) NOT NULL default '', `city` varchar(255) NOT NULL default '', `st_prov` varchar(255) NOT NULL default '', `zip_code` varchar(255) NOT NULL default '', `country` varchar(255) NOT NULL default '', `phone_area` varchar(255) NOT NULL default '', `phone_exc` varchar(255) NOT NULL default '', `phone_nbr` varchar(255) NOT NULL default '', `phone_ext` varchar(255) NOT NULL default '', `fax_area` varchar(255) NOT NULL default '', `fax_exc` varchar(255) NOT NULL default '', `fax_nbr` varchar(255) NOT NULL default '', `fax_ext` varchar(255) NOT NULL default '', `accredited` varchar(255) NOT NULL default '', `type` varchar(255) NOT NULL default '', `date_opened` varchar(255) NOT NULL default '', `comment_flag` varchar(255) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblcd` ( `record_status` varchar(255) NOT NULL default '', `grp0` varchar(255) NOT NULL default '', `sponsor` varchar(255) NOT NULL default '', `course` varchar(255) NOT NULL default '', `dater` varchar(255) NOT NULL default '', `sub` varchar(255) NOT NULL default '', `date_opened` varchar(255) NOT NULL default '', `date_maint` varchar(255) NOT NULL default '', `who_maint` varchar(255) NOT NULL default '', `seq_maint` varchar(255) NOT NULL default '', `end_date` varchar(255) NOT NULL default '', `type` varchar(255) NOT NULL default '', `title` varchar(255) NOT NULL default '', `sp_require` varchar(255) NOT NULL default '', `req_hrs_01` varchar(255) NOT NULL default '', `req_hrs_02` varchar(255) NOT NULL default '', `req_hrs_03` varchar(255) NOT NULL default '', `req_hrs_04` varchar(255) NOT NULL default '', `req_hrs_05` varchar(255) NOT NULL default '', `req_hrs_06` varchar(255) NOT NULL default '', `facility` varchar(255) NOT NULL default '', `addr1` varchar(255) NOT NULL default '', `addr2` varchar(255) NOT NULL default '', `city`
Re: where is my data?
Hi Michael, Thanks a lot for a very informative and educational reply. The problem was that my base directory is /usr i.e. i have /usr/share/mysql/english.. and I was assuming it was /data/mysql or wherever i chose to put my datadir. I was clearly confused abt the concept of basedir. So , now i have basedir /usr , datadir /data/mysql/data and all the file permissions as meant to be. i.e. datadir owned by mysql and 700. and its a beautiful feeling to see it all work :) Thanks again for all the replies. I learned some useful stuff and suddenly the problem seems worthwhile. Regards, Kaustubh Original Message Follows From: Michael Stassen [EMAIL PROTECTED] To: kaustubh shinde [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: where is my data? Date: Mon, 04 Apr 2005 12:45:04 -0400 On Apr 2, 2005, at 7:58 AM, kaustubh shinde wrote: Hi, I have suse 9.2 and MySQL 4.21 My basedir is /var/lib/mysql datadir /data/mysql/mysqldata As it stands now, mysqld_safe is broken when you move your data directory, so this may cause problems. (See bug 7249 http://bugs.mysql.com/bug.php?id=7249.) In this case, however, I think this is not the problem. Both the directories and subdirectories and files are owned by user mysql, group mysql with rights 755. So I guess I have got the permissions part right or so I hope. datadir should be owned by mysql, group mysql, with permissions set to 700 or 750. Everything was working fine till I decided to move the data directory from its previous location to the above one. I had millions of problems after moving the data directory and the database won't start at all. Exactly. Where was it before? Just to make things work I made the base and data dirs 777. Finally, I can now start it using `mysql.server start` Don't do that! It's insecure. If this made a difference, it means you have problems you should fix. This just hides them, and exposes your server. But the good part ends here. I can only see mysql and test databases and i have to log on as root to mysql. My earlier users and databases won't show. Although I have the datafiles at the specified location. This might be coz I ran mysql_install_db again. When you ran mysql_install_db, you got a new data directory in the default location (presumably the same place your data used to be) with the default dbs, mysql and test. The default mysql db has user root and the anonymous user, '', each with no password. mysqld is now working because it is using this data directory, instead of yours. If I change the rights of base and data dirs to 755, it won't work. This is because you've made a bad choice of location (basedir) for your log and socket files. User mysql must be able to write to the directory where these will go, but user mysql should not be able to write to basedir (basedir should be owned by root). You need to move these to a directory owned by mysql (datadir) or a directory where everyone can write (e.g. /tmp). I still can't start using mysqld_safe. My /etc/my.cnf has following: [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock /data/mysql/mysqldata/mysql.sock or /tmp/mysql.sock should work. user=mysql datadir=/data/mysql/mysqldata bdb_home=/data/mysql/mysqldata [mysql_server] basedir = /var/lib/mysql [mysql.server] basedir = /var/lib/mysql [mysqld_safe] err-log=/var/lib/mysql/mysqld.log /data/mysql/mysqldata/mysqld.log should work. innodb_data_home_dir=/data/mysql/mysqldata innodb_data_file_path=ibdata1:10M:autoextend innodb_log_group_home_dir=/data/mysql/ innodb_log_arch_dir=/data/mysql/ and so on.. anyway, so this my.cnf doesn't seem to make any difference. when i try mysqld_safe it gives Starting mysqld-max daemon with databases from /var/lib/mysql Apparently, your distribution expects the data directory to be /var/lib/mysql, but that's your basedir. Hmm... /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied 050402 07:39:03 mysqld ended tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log and I get 050402 07:40:29 mysqld started 050402 7:40:29 InnoDB: Started 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050402 7:40:29 Aborting Right, the data directory wasn't found. 050402 7:40:29 InnoDB: Starting shutdown... 050402 7:40:31 InnoDB: Shutdown completed 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete 050402 07:40:31 mysqld ended and when i try to specify basedir with above command like mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log --basedir=/var/lib/mysql i get 050402 07:38:28 mysqld started 050402 7:38:28 Can't find messagefile
Re: Problem doing insert on a datetime field
Robert A. Rawlinson wrote: I am having a problem doing an insert on a datetime field. Is there something I have to use to convert it to that form? I have it set up in a string as '2000/09/17 00:00:00' but that does not seem to work. Thanks for any help you can offer. Bob Rawlinson Sorry! I found it. It was a statement just before that was causing the trouble. Bob Rawlinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbinlog troubles.
hello, I'm trying to restore a table from a full back up and then a binlog. this is a test table setup specifically for this. I have 34 rows in the full backup, another 5 in the binlog. I find the date of the last insert and use this as the --start-datetime for mysqlbinlog. The problem is that I always get the same error error: table foo already exists. Shouldn't it just be updating from the start-datetime and not trying to create/drop the already existing table? Sorry if this is a simple one, been searching for this for awhile now. # /usr/local/mysql/bin/mysqlbinlog --database=backuptest --start-datetime=2005-03-30 15:00:00 /data/mysql/logs/dbne1-bin.173 | /usr/local/mysql/bin/mysql -u seth -p backuptest Enter password: ERROR 1050 at line 10046: Table 'foo' already exists thanks. -seth -- Seth Itschner SparkNotes [EMAIL PROTECTED] V: (212) 633-3555 F: (212) 727-4827 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Tuning - Table Joins
At 12:22 PM 4/4/2005, you wrote: I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for taking a look. There is no error messages that can be posted, so I will try and describe what's happening as best I can. I am joining 3 tables in one query. I have had numerous people examine the queries and all have given their stamp of approval. What happens when I run it is MySQL takes the processor for a ride, spiking it to 100% until I restart mysqld. The tables range from 50,000 to 85,000 records, and the join is only supposed to return 1 record. My question to you is this: are there changes I can make to the configuration to improve performance? --or-- is data de-normalization my best option? Is there any more information you need from me to answer this question? Current setup: 2.4ghz Pentium 4, 1gb ram, 360gb 4-disc raid 5 array w/ 3ware chassis and card, fedora core 3 w/ all patches and updates, selinux -disabled-, mysql 4.1.10a, MyISAM table format. Again, thank you all in advance, Jason Jason, Try running Analyze Table on each of the tables. This will rebalance the index and get rid of deleted space. Returning one row from a 3 table join should take only ms if you're using indexes properly. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you solved your data loading problem. You've chosen an unusual solution. I'm not sure why it is important that the size of the column is exactly 595 characters but that is up to you. As I understand it, the BLOB and TEXT column types only store the amount of data that you give them; if I am right about that, your BLOB or TEXT column will only contain 595 characters of data, not 595 characters of data plus thousands of trailing blanks. (If I am misreading this, I hope someone else will jump in and correct me!) That means that your data will be in one field and you you won't have to concatenate it. With regards to your second problem, I could certainly do what you describe in a Java program - provided the MS Word documents are small enough to fit inside a BLOB! - but I'm not so sure how to do it without using a programming language. I just had a look at LOAD DATA INFILE to see if it can handle BLOBs but it says explicitly that it *CANNOT* handle BLOBs. Since the mysqlimport utility is just a front-end to LOAD DATA INFILE, I assume it has the same limitation. However, this page http://dev.mysql.com/doc/mysql/en/blob.html has some discussion of techniques to load BLOBs into tables. See the user comments in the bottom part of the page. All of them seem to use programming languages though. The user comments also raise an important issue: sometimes, it is better to store only a URL pointing to the document in the database and keep the actual document in the file system rather than the database. They suggest some guidelines to help you decide which approach is best for you. If you haven't considered these guidelines, you should. It might turn out that it is better for you to leave the documents outside of MySQL. Sorry I couldn't be more help. Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Monday, April 04, 2005 12:24 PM Subject: Re: Load data infile and text fields Rhino, many thanks for your answer! My problem is that I need a filed with precision for a field of exactly 595 characters! Only text field type with precision is the char type but its limit is 256 char. I've tried with text type, but precision were been ignored and my sql silently truncate it at 256 value. I solved my problem creating a temp table with my field splitted in three char fields: 250+250+95, then I've rebuild each line with the function concat()! Now I've another problem: after I've imported all txt files with a fully automated query, I need to import into a table with a blob field, a lot of little msword documents. Each record should get a single word file. I'd like to write a query (I hope without using api as php or other languages) that imports automatically all .doc files stored in a fixed directory. Have any idea? Thanks a lot Stefano -- Messaggio originale -- From: Rhino [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Load data infile and text fields Date: Sat, 2 Apr 2005 09:44:07 -0500 Stefano, The behaviour you are describing is normal, assuming that the column in your MySQL table is defined as CHAR(255) or VARCHAR(255). You didn't say which version of MySQL you are using. However, unless you are using MySQL 5.0.3 or later, 255 is the largest size available for a CHAR or VARCHAR column. (Starting with version 5.0.3, the maximum size of a VARCHAR is 65,536.) Assuming you are not on 5.0.3 or later, you should change your table definition to use one of the BLOB or TEXT datatypes: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB or TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. You should look at the definitions of these column types in the manual - http://dev.mysql.com/doc/mysql/en/storage-requirements.html - to see which one best suits your requirements; only you know the largest value that you want to store in the column. Basically: - TINYBLOB and TINYTEXT are for values less than 256 characters long (which is no better than CHAR or VARCHAR in your case) - BLOB and TEXT are for values less than 65536 characters long - MEDIUMBLOB and MEDIUMTEXT are for values less than 16777216 characters long - LONGBLOB and LONGTEXT are for values less than 4294967296 characters long Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 02, 2005 6:51 AM Subject: Load data infile and text fields First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine
Cannot execute query - Can't find file: (error: 9)
Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance Tuning - Table Joins
Your not indexing properly this should be a blink of a search. Or your looping your loops when you search. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Monday, April 04, 2005 1:30 PM To: MySQL list Subject: Re: Performance Tuning - Table Joins At 12:22 PM 4/4/2005, you wrote: I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for taking a look. There is no error messages that can be posted, so I will try and describe what's happening as best I can. I am joining 3 tables in one query. I have had numerous people examine the queries and all have given their stamp of approval. What happens when I run it is MySQL takes the processor for a ride, spiking it to 100% until I restart mysqld. The tables range from 50,000 to 85,000 records, and the join is only supposed to return 1 record. My question to you is this: are there changes I can make to the configuration to improve performance? --or-- is data de-normalization my best option? Is there any more information you need from me to answer this question? Current setup: 2.4ghz Pentium 4, 1gb ram, 360gb 4-disc raid 5 array w/ 3ware chassis and card, fedora core 3 w/ all patches and updates, selinux -disabled-, mysql 4.1.10a, MyISAM table format. Again, thank you all in advance, Jason Jason, Try running Analyze Table on each of the tables. This will rebalance the index and get rid of deleted space. Returning one row from a 3 table join should take only ms if you're using indexes properly. Mike -- 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]
subqueries *not* using indexes for IN clause
http://www.peerfear.org/rss/permalink/2005/04/02/BrokenMySQLSubqueries Whats up with this? As far as I can tell MySQL subqueries in 4.1.x releases are totally broken with IN clauses The major reason is that they don't use *ANY* indexes and resort to full table scans. Lets take two queries: mysql EXPLAIN SELECT * FROM FEED, ARTICLE WHERE ARTICLE.ID = 1628011 AND FEED.ID = ARTICLE.ID *** 1. row *** id: 1 select_type: SIMPLE table: FEED type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 2 rows in set (0.00 sec) Which is *great*. The join is using both of the PRIMARY indexes on the columns and only references one row. Can't get any better than that! Now lets rewrite the SELECT to use a subquery: mysql EXPLAIN SELECT * FROM FEED WHERE ID IN (SELECT ID FROM ARTICLE WHERE ID = 1628011) *** 1. row *** id: 1 select_type: PRIMARY table: FEED type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2316698 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 2 rows in set (0.00 sec) And here's where the fun begins. The FEED table won't use *ANY* index! It really can't get ANY worse than that. So either this is a bug in both 4.1.10 and 4.1.7 or the optimizer is just plain broken. Note that using FORCE INDEX doesn't work at all. -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
Re: subqueries *not* using indexes for IN clause
We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. greg Kevin A. Burton wrote: http://www.peerfear.org/rss/permalink/2005/04/02/BrokenMySQLSubqueries Whats up with this? As far as I can tell MySQL subqueries in 4.1.x releases are totally broken with IN clauses The major reason is that they don't use *ANY* indexes and resort to full table scans. Lets take two queries: mysql EXPLAIN SELECT * FROM FEED, ARTICLE WHERE ARTICLE.ID = 1628011 AND FEED.ID = ARTICLE.ID *** 1. row *** id: 1 select_type: SIMPLE table: FEED type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 2 rows in set (0.00 sec) Which is *great*. The join is using both of the PRIMARY indexes on the columns and only references one row. Can't get any better than that! Now lets rewrite the SELECT to use a subquery: mysql EXPLAIN SELECT * FROM FEED WHERE ID IN (SELECT ID FROM ARTICLE WHERE ID = 1628011) *** 1. row *** id: 1 select_type: PRIMARY table: FEED type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2316698 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: ARTICLE type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 2 rows in set (0.00 sec) And here's where the fun begins. The FEED table won't use *ANY* index! It really can't get ANY worse than that. So either this is a bug in both 4.1.10 and 4.1.7 or the optimizer is just plain broken. Note that using FORCE INDEX doesn't work at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting up a Chemical database
I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up a Chemical database
One table should be sufficient to handle this with one record for each product. - Asad On Mon, 4 Apr 2005, Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up a Chemical database
I'm afraid we can't tell you anything with virtually no information given by you. What is a product? Which are the informations you want to hold about a product? How do they relate to each other? What is the purpose of the database? Which sort of informations are people supposed to get out of it? If you answer these questions for yourself, the design should become visible. If any doubts, ask, but please be specific. Stefan P.S: If interested, the ER diagram of a chemical database can be found here: http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/*checkout*/nmrshiftdb/nmrshiftdb/doc/ER-Diagram_for_NMRShiftDB.ps?rev=HEADcontent-type=aplication/ps But chemical is a wide field ... Am Monday 04 April 2005 23:47 schrieb Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem: I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up a Chemical database
Dave, I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? Save it from Word as comma- or tab-delimited, create a MySQL database table, use ODBC Admin to create a DSN for that database, open the MySQL database in Access, in the Access database window click Tables on the Objects bar, select Get External Data from the File menu, click Import. PB No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this wise use of auto_increment?
Hello, I'm using MyISAM tables in mysql and in order to make a certain operation appear atomic, I need to insert records into 2 tables in a certain order. In particular, in one of the tables is a key that maps to multiple rows in the other table. This is the sane thing to do if there were'nt any order requirements: CREATE TABLE Bar ( ... other stuff ... key1 int unsigned not null auto_increment, UNIQUE (key1), ... ); CREATE TABLE Foo ( key1 int unsigned not null, key2 int unsigned not null, value int, PRIMARY KEY k (key1, key2) ); However, I need to insert a set of rows into Foo first, all with the same key1. But I need an ID that is unique to Bar! My 1st solution is: CREATE TABLE Bar ( ... other stuff ... key1 int unsigned not null, UNIQUE (key1), ... ); CREATE TABLE Foo ( key1 int unsigned not null, key2 int unsigned not null, value int, PRIMARY KEY k (key1, key2) ); CREATE TABLE BunchaIDs ( key1 int unsigned not null auto_increment primary key ); then what I can do is INSERT INTO BunchaIDs VALUES (); id = LAST_INSERT_ID(); INSERT INTO Foo (key1, key2, value) VALUES ..buncha values with key1=id; INSERT INTO Bar (, key1) VALUES (..., id); Unfortunately, using another table is rather complicated because there is actually a set of tables like Foo and Bar that are organized by days, so I would need an ID table for every day, which adds to maintenance operations... So my 2nd solution was I found I could do: CREATE TABLE Bar ( ... other stuff ... key1 int unsigned not null, UNIQUE (key1), ... ); CREATE TABLE Foo ( key1 int unsigned not null auto_increment, key2 int unsigned not null, value int, PRIMARY KEY k (key1, key2) ); then I could do something a little unorthodox... INSERT INTO Foo (key2, value) VALUES (1, 100); id = LAST_INSERT_ID(); INSERT INTO Foo (key1, key2, value) VALUES (id, 2, 101), (id, 3, 42), (id, 4, 77), .; INSERT INTO BAR (, key1) VALUES (..., id); I insert one record in Foo to get a unique id for key1, then insert the rest of the records into Foo specifying the same value for key1. Since records for Bar are always preceded by records for Foo, the key in Bar will be unique. In addition, the next time I insert into Foo *without* giving an id, mysql gives me a unique id, presumably one higher than the last. The questions are, is this use of auto_increment portable across SQL databases? How sound is it across versions of MySQL and into the future? Are there any possible problems I could run into? Thanks, Julian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Users and Max_questions
Is there a way to determine the question count of an individual user?I use the max_questions grant extensively in order to manage server resources and this would help me out a lot. Regards, Gary Huntress -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hide password when running mysqldump from a batch
This should work for mysqldump just as it does for mysql. What, precisely, do you mean by does not appear to work? Describe what happens. Do you get an error, or unexpected results? If an error, what's the error message? If unexpected results, what do you expect, and what do you get? Are you running mysqldump from the command line, or as a cron job? Michael On Apr 1, 2005, at 10:13 AM, rds wrote: No solution for this? Thanks --- rds [EMAIL PROTECTED] wrote: --- What you really want to avoid is having the password on the commandline. File permissions won't matter at all if you end up running a command that puts your password in the output of 'ps'! Command lines are always public information. Put the password for mysqldump in the running user's ~/.my.cnf instead, and tighten the permissions on *that* file. [client] password=Your password goes here I tried that; it does work with mysql but does not appear to work with mysqldump. Is there a way to supply the password to mysqldump when running dump from a batch scipt and avoid showing it on the command line? Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
On Apr 4, 2005, at 3:52 PM, Rhino wrote: Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you solved your data loading problem. You've chosen an unusual solution. I'm not sure why it is important that the size of the column is exactly 595 characters but that is up to you. As I understand it, the BLOB and TEXT column types only store the amount of data that you give them; if I am right about that, your BLOB or TEXT column will only contain 595 characters of data, not 595 characters of data plus thousands of trailing blanks. (If I am misreading this, I hope someone else will jump in and correct me!) That means that your data will be in one field and you you won't have to concatenate it. I think you missed this in the original post: I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. Importing from a fixed-width file requires a table with precisely matching column widths. I believe his (clever) solution was to import into 3 columns with the correct total width as a first pass, then CONCAT those 3 columns into one TEXT column. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subqueries *not* using indexes for IN clause
Greg Whalin wrote: We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table. But if I have to do that, might as well just use the join without the funky syntax. Still, it does simplify some sql which is difficult to do with a regular join (i.e. joining w/ max() col, etc.). In any rate, I agree. What is the point of claiming to offer sub-selects when thay are practically unusable in IN() statements which is how most people use subselects IMO. Yup... couldn't agree more! MySQL subqueries in 4.1 are at best useless and at worst Evil.. plain Evil ! ;) But nice try guys! This seems like it REALLY deserves a bug fix! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it any faster to use IGNORE with Load Data Infile?
I'm loading 100 million rows into a MyISAM table and I'm wondering what overhead is there when using the Load Data Infile REPLACE over Load Data Infile Ignore syntax. For example, does the REPLACE do a lookup prior to inserting the row? Would it be faster to use Ignore? There is no possibility of having duplicate unique keys so I'm wondering if one syntax has a speed benefit over the other. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb: Alter table progress
James, - Original Message - From: James Green [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, April 04, 2005 1:28 PM Subject: Innodb: Alter table progress Hi, Is there any way of checking the progress of an ALTER TABLE query on an InnoDB table? show innodb status isn't clear. hmm... in the newest versions of MySQL there is no way, except by monitoring the number of inserted rows in SHOW INNODB STATUS: Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779 That number is, of course, cluttered by other workload that you have. Thanks, -- James Green Systems Administrator, StealthNET Ltd, www.stealthnet.co.uk Tel: 0870 800 1777 Intl: +44 1493 660066 Fax: 0870 135 1069 Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cannot connect to the mysql server.thank you.
dear sir, nice to meet you. now i have installed the MySQL-ServerClient(4[1].0.15).but i cannot connect mysql server when i make a change. what is the wrong with what i do?l can i get your help? thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
