Re: In need of a dummy select statement ...
Thanks a lot the clarification. This works for me with no exceptions. Srinivas. Cersosimo, Steve wrote: You need not select a table in MySQL as you do in Oracle. Select 2 + 2; Is valid in MySQL and should return 4. It at least lets you know if MySQL is functional. By mysqladmin ping will do that too. Steve Cersosimo [EMAIL PROTECTED] Bellsouth Internet Services When all is said and done, more is said than done -Original Message- From: Srinivas B.S.S [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 1:22 AM To: Don Read Cc: [EMAIL PROTECTED] Subject: Re: In need of a dummy select statement ... Thanks for the reply. It just have to be no-op and it need not necessarily be a select statement. Any one of the options given by you will work for me if they are executable from a program written using MyODBC. Thanks, Srinivas. Don Read wrote: On 08-Dec-2003 Srinivas B.S.S wrote: Hi, I am a newbie to mysql db. I need a select statement which will just execute and doesn't return any results. Also, it should not depend on any table which is specific to a database. For example, Oracle has a table named 'tab' which will be present in all databases so my dummy select statement looks like 'select tname from tab where 0=1'. Could this functionality be achieved in mysql ? Is there any table in mysql which is similar to Oracle's 'tab' ? Does it have to be a SELECT? Or just a NO-OP? SET @nothing=0; -or- DO 0; - or - SHOW TABLES LIKE 'asdfzxcv1234'; Regards, * The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential, proprietary, and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all computers. 113
Re: Mysql user
SELECT user FROM mysql.user; - Original Message - From: Binay [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Sent: Friday, December 05, 2003 3:25 PM Subject: Mysql user Hi all, Can any one tell me the command to list all mysql users ??? Thanks in advance Binay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Find duplicates query
select ID from YourTable group by ID having count(ID) 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLGUI Win32 Static Binary
I seem to be downloading a corrupted zip file? Help! ### Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the National Bank of Malawi Ltd (NBM). The information, including that in any attachments, contained in this electronic mail transmission is privileged and confidential and intended only for the use of the person or entity to whom it is addressed. If the reader of this message is not the intended recipeint, you are hereby notified that you must not use, copy, distribute or disclose this e-mail or any part of its contents or take any action in reliance on it. If you have received this e-mail in error, please e-mail the sender by replying this message, then deleting it immediately. All reasonable precautions have been taken to ensure no viruses are present in this e-mail. NBM cannot accept responsibility for loss or damage arising from the use of this e-mail or attachments and recommend that you subject them to your virus checking procedures prior to use. ### This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.com/
Recompiling Applications after Upgrade of MySQL.
Hello Listers. I would like to upgrade from 3.4 to MySQL 4.1 and would just like to know. Any Apps that have been compiled using ./configure --with-mysql option. Would these apps have to be recompiled? Regards Denham Eva ORACLE DBA. -- Your information went that way -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key is not used
Hello, i'm having a problem with mysql. when i put an index on a datetime column, it's never used. for example the sql: 'EXPLAIN SELECT * FROM actions ORDER BY datum' shows: table type possible_keys key key_len ref rows Extra actions ALL NULL NULL NULL NULL 75853 Using filesort even if i use the sql: 'EXPLAIN SELECT * FROM actions USE INDEX (datum) ORDER BY datum' mysql doesn' use the index :-(( tahnks for any help, corin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key is not used
Hi, I'm not saying I fully understand the MySQL optimizer, as I never had to deal with it before... but: Why should it use an index if the statement is returning ALL rows? It might be faster to read them into memory ( 75853 isn't really much ) and sort them there instead of moving the diskhead back and forth between the index and the data. i'm having a problem with mysql. when i put an index on a datetime column, it's never used. for example the sql: 'EXPLAIN SELECT * FROM actions ORDER BY datum' shows: table type possible_keys key key_len ref rows Extra actions ALL NULL NULL NULL NULL 75853 Using filesort even if i use the sql: 'EXPLAIN SELECT * FROM actions USE INDEX (datum) ORDER BY datum' mysql doesn' use the index :-(( With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update: key is not used
Hello, update of my last email sent. when i use the sql: 'EXPLAIN SELECT datum FROM actions ORDER BY datum' the key gets used: table type possible_keys key key_len ref rows Extra actions index NULL datum 8 NULL 75859 Using index however as soon as i select more column like sql: 'EXPLAIN SELECT datum,id FROM actions ORDER BY datum' the key isn't used anymore :-(. might this be bug of mysql? Corin --- This is a forwarded message From: Corin Langosch [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Tuesday, December 9, 2003, 10:11:03 AM Subject: key is not used ===8==Original message text=== Hello, i'm having a problem with mysql. when i put an index on a datetime column, it's never used. for example the sql: 'EXPLAIN SELECT * FROM actions ORDER BY datum' shows: table type possible_keys key key_len ref rows Extra actions ALL NULL NULL NULL NULL 75853 Using filesort even if i use the sql: 'EXPLAIN SELECT * FROM actions USE INDEX (datum) ORDER BY datum' mysql doesn' use the index :-(( tahnks for any help, corin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ===8===End of original message text=== -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: key is not used
Hello, thanks for your fast reply. even when i use EXPLAIN SELECT * FROM `actions` ORDER BY datum LIMIT 10 the key isn't used. the query takes about 2s :-( Corin Tuesday, December 9, 2003, 10:19:16 AM, you wrote: MT Hi, MT I'm not saying I fully understand the MySQL optimizer, as MT I never had to deal with it before... but: MT Why should it use an index if the statement is returning ALL MT rows? It might be faster to read them into memory ( 75853 MT isn't really much ) and sort them there instead of moving the MT diskhead back and forth between the index and the data. i'm having a problem with mysql. when i put an index on a datetime column, it's never used. for example the sql: 'EXPLAIN SELECT * FROM actions ORDER BY datum' shows: table type possible_keys key key_len ref rows Extra actions ALL NULL NULL NULL NULL 75853 Using filesort even if i use the sql: 'EXPLAIN SELECT * FROM actions USE INDEX (datum) ORDER BY datum' mysql doesn' use the index :-(( MT With regards, MT Martijn Tonies MT Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL MT Server. MT Upscene Productions MT http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: key is not used
Hi Corin, As I said - I don't have the slightest idea about the MySQL optimizer, but I'm trying to learn. thanks for your fast reply. even when i use EXPLAIN SELECT * FROM `actions` ORDER BY datum LIMIT 10 the key isn't used. the query takes about 2s :-( What if you do: SELECT ... WHERE DATUM some-value With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key is not used
Corin Langosch wrote: Hello, thanks for your fast reply. even when i use EXPLAIN SELECT * FROM `actions` ORDER BY datum LIMIT 10 the key isn't used. the query takes about 2s :-( What does it show if you do a show indexes from actions --- If you are sure the optimizer is wrong, you can FORCE it to use an index. The USE INDEX option only suggests an index to mysql. SELECT * FROM actions FORCE INDEX (datum) ORDER BY datum And see if it makes a speed difference. Most often, if MySql didn't use it, then it won't help but I'm sure it can be wrong once in a while. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with replication
SQL Hi all! Nearly very night I have this problem. This query is done every 3 hours. But problem is happened only at 3:00 Both master and slave are 4 CPU (2 with HTT) computers. Both have mysql 4.0.16 And when I test table analit. There are many records which have the same conditions as in WHERE statement. This records are inserted into analit before this UPDATE. UPDATE finds records on master, I have checked the records on master and I see the result of UPDATE. But there is the error on slave server. If I run any query on analit like select count(*) from analit where label=1070931600 and after this SLAVE STOP; SLAVE START problem is solved. Is it a bug in server? Can mysqld break the order of replication query on multy-CPU computers? Any ideas, please. ++-+-+---+ -+-+--+---+ ---+--+---+ -+-++ - - - - - +--+- +-+ _ Master_Host_ Master_User _ Master_Port _ Connect_retry _ Master_Log_File _ Read_Master_Log_Pos _ Relay_Log_File _ Relay_Log_Pos _ Relay_Master_Log_File _ Slave_IO_Running _ Slave_SQL_Running _ Replicate_do_db _ Replicate_ignore_db _ Last_errno _ Last_error _ Skip_counter _ Exec_master_log_pos _ Relay_log_space _ ++-+-+---+ -+-+--+---+ ---+--+---+ -+-++ - - - - - +--+-+ -+ _ XXX _ Z _ _ 60_ a0-bin.029 _ 2210728 _ a1-relay-bin.001 _ 5880071 _ a0-bin.029 _ Yes _ No_ atas_ _ 1032 _ Error 'Can't find record in 'analit'' on query 'UPDATE analit, price_grp SET analit.status=price_grp.status, analit.currency=price_grp.currency WHERE analit.price=price_grp.price AND analit.service=price_grp.service AND analit.city=price_grp.city AND label=1070931600'. Default database: '' _ 0_ 2210187 _ 5880612 _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication/binary log
Use PURGE {MASTER|BINARY} LOGS TO 'log_name' instead of RESET MASTER. From the manual: Deletes all the binary logs listed in the log index that are strictly prior to the specified log or date. The logs also are removed from this list recorded in the log index file, so that the given log now becomes the first. (...) You must first check all the slaves with SHOW SLAVE STATUS to see which log they are reading, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log. http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html -- DS On Mon, 2003-12-08 at 23:09, Mayuran Yogarajah wrote: We are running MySQL 3.23 in production, and have replication setup in the following manner: There are two machines (m1 and m2). Replication is setup in a circular way. Both machines are master and slave, more specifically, m1 is master to m2 and m2 is master to m1. I checked today and saw that one of the machines had a bunch of binary log files (see below). I read in the MySQL documentation that you can delete the logs by issuing a RESET MASTER command. I am wonder how this will affect replication. Is this going to break replication in any way? Is it safe to simply delete the binary log files manually ? (Id prefer to do this). Any input would be helpful. Thank you. MySQL data directory: total 4388640 drwx--2 mysqlmysql4096 Aug 17 19:24 mysql drwx--2 mysqlmysql4096 Aug 17 19:46 test -rw-rw1 mysqlmysql 445 Aug 17 20:42 mw01-bin.001 drwxr-xr-x8 root root 4096 Aug 17 23:51 .. -rw-rw1 mysqlmysql 111 Aug 24 04:02 mw01-bin.003 -rw-rw1 mysqlmysql 308 Aug 24 04:02 mw01-bin.002 -rw-rw1 mysqlmysql 111 Aug 31 04:02 mw01-bin.005 -rw-rw1 mysqlmysql 244491 Aug 31 04:02 mw01-bin.004 -rw-rw1 mysqlmysql 111 Sep 7 04:02 mw01-bin.007 -rw-rw1 mysqlmysql 28177 Sep 7 04:02 mw01-bin.006 -rw-rw1 mysqlmysql7947 Sep 13 23:59 mw01-bin.008 -rw-rw1 mysqlmysql 111 Sep 14 04:02 mw01-bin.010 -rw-rw1 mysqlmysql3513 Sep 14 04:02 mw01-bin.009 -rw-rw1 mysqlmysql 111 Sep 21 04:02 mw01-bin.012 -rw-rw1 mysqlmysql30791885 Sep 21 04:02 mw01-bin.011 -rw-rw1 mysqlmysql 111 Sep 28 04:02 mw01-bin.014 -rw-rw1 mysqlmysql111270867 Sep 28 04:02 mw01-bin.013 -rw-rw1 mysqlmysql12105202 Sep 28 19:18 mw01-bin.015 -rw-rw1 mysqlmysql 111 Oct 5 04:02 mw01-bin.017 -rw-rw1 mysqlmysql38094517 Oct 5 04:02 mw01-bin.016 -rw-rw1 mysqlmysql 111 Oct 12 04:02 mw01-bin.019 -rw-rw1 mysqlmysql276605852 Oct 12 04:02 mw01-bin.018 -rw-rw1 mysqlmysql61917421 Oct 12 23:48 mw01-bin.020 -rw-rw1 mysqlmysql 111 Oct 19 04:02 mw01-bin.022 -rw-rw1 mysqlmysql101760652 Oct 19 04:02 mw01-bin.021 -rw-rw1 mysqlmysql 111 Oct 26 04:02 mw01-bin.024 -rw-rw1 mysqlmysql579578833 Oct 26 04:02 mw01-bin.023 -rw-rw1 mysqlmysql 479 Nov 2 04:02 mw01-bin.026 -rw-rw1 mysqlmysql844900359 Nov 2 04:02 mw01-bin.025 -rw-rw1 mysqlmysql 111 Nov 9 04:02 mw01-bin.028 -rw-rw1 mysqlmysql869670836 Nov 9 04:02 mw01-bin.027 drwx--2 mysqlmysql4096 Nov 10 21:15 Viper -rw-rw1 mysqlmysql 111 Nov 16 04:02 mw01-bin.030 -rw-rw1 mysqlmysql700865150 Nov 16 04:02 mw01-bin.029 -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.032 -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.031 -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.034 -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.033 -rw-rw1 mysqlmysql 86 Dec 7 01:23 mw01-bin.035 srwxrwxrwx1 mysqlmysql 0 Dec 7 01:25 mysql.sock -rw-rw1 mysqlmysql 570 Dec 7 01:30 mw01-bin.index -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.037 -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.036 drwxr-xr-x5 mysqlmysql4096 Dec 7 01:30 . -rw-rw1 mysqlmysql 63 Dec 7 01:49 master.info -rw-rw1 mysqlmysql861518654 Dec 8 17:53 mw01-bin.038 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.0.16
Hi , Is anyone else experiencing a degrade in performance after upgrading from 3.23.53 to mysql 4.0.16. After the upgrade the system load is extremly high +/- 6 and up and the cpu is about 98% busy with the mysql process. Regards Stanley variables below. SQL-querySQL-query (126 Records) Variable_nameValue back_log 50 basedir /usr/local/mysql-standard-4.0.16-pc-linux-i686/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 60 convert_character_set datadir /disk2/mysql/ default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -gt;lt;()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_stopword_file (built-in) have_bdb NO have_crypt YES have_innodb YES have_isam YES have_raid NO have_symlink YES have_openssl NO have_query_cache YES init_file innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdown ON innodb_flush_method innodb_lock_wait_timeout 50 innodb_log_arch_dir ./ innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 600 join_buffer_size 4190208 key_buffer_size 536870912 language /usr/local/mysql-standard-4.0.16-pc-linux-i686/share/mysql/english/ large_files_support ON local_infile ON locked_in_memory OFF log OFF log_update OFF log_bin OFF log_slave_updates ON log_slow_queries ON log_warnings OFF long_query_time 10 low_priority_updates OFF lower_case_table_names OFF max_allowed_packet 134216704 max_binlog_cache_size 4294967295 max_binlog_size 536870912 max_connections 16384 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_recover_options OFF myisam_sort_buffer_size 67108864 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit 81930 pid_file /disk2/mysql//sybase.rtlimedia.nl.pid log_error port 3306 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_size 0 query_cache_type ON query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 33550336 read_only OFF read_rnd_buffer_size 262144 rpl_recovery_rank 0 server_id 2 slave_net_timeout 3600 skip_external_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /tmp/mysql.sock sort_buffer_size 33554424 sql_mode 0 table_cache 1024 table_type MYISAM thread_cache_size 512 thread_stack 126976 tx_isolation REPEATABLE-READ timezone MET tmp_table_size 33554432 tmpdir /tmp/ transaction_alloc_block_size 8192 transaction_prealloc_size 4096 version 4.0.16-standard-log wait_timeout 600 _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to joins - this simple one doesn't work.
Try using '$id' : SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id' On Mon, 2003-12-08 at 23:18, fatblokeonbike wrote: . I'm new to MySQL joins with PHP and I can't get this to work - and I don't understand *why* it won't work. Table 1 - Countries - has fields: country Table 2 - Properties - has fields: area, reference_number Table 3 - Images - has fields: image_filename, reference_number The first page goes - $Query=SELECT country FROM countries; $Result=mysql_db_query ($DBName, $Query, $Link); while ($Row=mysql_fetch_array($Result)) { print(a href=\country.php?id=$Row[country]\$Row[country]/abr); } The visitor, wanting to see an area, clicks on a particular country's link and arrives in the country.php page, which contains - $id = $HTTP_GET_VARS[country];. $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number ; This works, but it delivers the images of every country. To call the images from just one country, I try - $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource I've played around with it, but I confess myself beat. I expect the answer's terribly simple - but then, as everyone keeps telling me, so am I. If you can help, thanks in advance. Iain. -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practices for sharing members database between different portals
Dear All, Please comment on Best Practices for sharing members database between different portals. Suppose we have 3 portals running on different networks. Assignment is to make a single Login/Pass for all portals, means once LogedIn in one of the portal, could able to access the other portals without loging In. Constraints are, every portal have different Database structure and also have different Global / Session variables. Please share your experience and which approach is the best practice. Regards, TM
Re: Comparing strings containing possible quotes
On Mon, 08 Dec 2003 19:07:43 +, zzapper [EMAIL PROTECTED] wrote: Hi Ya, I have a typical select as follows SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%#form.searchtext#%') B) Now I can filter any quotes from form.searchtext easy enough, but how do I filter them the Left Hand Side eg from txtDevName?) Is there no mysql solution to this? Most Where clauses are thus WHERE LHS Like/= RHS Now MySQL provides lots of operators for the RHS eg WHERE LHS RLIKE RHS But what I want to do is preprocess/filter the LHS before doing the comparison, I can find no information on this. In the past I have cheated by creating an additional column in my database eg txtDevNameClean which has all non-alphanumerics removed. am I barking up the wrong tree?? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running MySQL Daemons with same data files
Dear All, I am working in Linux 8.0 and running mysql 4.0.16. I am trying to access same data files from the two mysql daemons. I mean there are two PC's running with mysql and one NAS server where data files are kept. To run mysql on the machines I first mount the NAS file system and then run it like shellsafe_mysqld --datadir=$PATH_TO_DATA_FILE) Daemons are running well but there is problems with synchronization of the data files i.e when i insert some tuples in the tables its not immediately writing then to file its kept in the cache only and when I try to see from the other machine its displaying the old tuples. I want after every transaction or query the database update the data files, how can I do this? And always read from the data files for select operations. My main aim is no cache operation to use the mysql as file system(for every operation file operations only), I know it will degrade the database performance but its the requirement OR if there is any other way by which I can achieve this, please tell me. regards bhartendu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delayed Insert Question
Hi list, I've got a small MyISAM table which is used for some statistics. I'm only doing insert into this table. I need that clients doing INSERT queries can exit as soon as possible. So, i'm using INSERT DELAYED with some good succes. But I've got a question. If i decrease delayed_insert_limit to ten secondes for example, is that mean that delayed_queue will be flushed every ten secondes ? Is there an other variable that specify the flush time ? For the moment, MySQL can handle an average of 92.12 query/sec. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ?? .tcshrc On OS X ??
.tcshrc is the config file for your settings on the environment variables for your session of tcsh. When your looking for $SHELL your looking for the command line interpretor. FreeBSD can use most any of them. bash, csh, ksh,, sh, tcsh, etc. Usually the .rc file is found in your home directory i.e. /home/bsmythe/.tcshrc the file usually contains things like your $PATH variable or $PROMPT. Your best bet is to type env (on FreeBSD at least). That should give you a list of environment variables if you like the way your command line works now then I wouldn't change too many things. So you have a MAC. Look in your McManual to see where you set things like the type of command line prompt. Does it let you change the prompt from SHELL to SHELL_8:00AM:\ . The file you use to set this environment variable is the file you use to set the PATH. OS X 10 is claimed to be a kissing cousin to FreeBSD so I will make that assumption. First you will get stuck. But your system is like a 4wheeldrive and these lists are the wench to drag you out of places you ought not be. So add a good helping of persistence. You can do this. I'm an ex-truck driver. Good scrappy computers can be found for less than 50$ for an IBM PC. Load it up with FreeBSD. Buy a Good FreeBSD book Like FreeBSD Unleashed by Michael Urban and Brian Tiemann. Then just port down the Mysql rdbms. Use your Mac to administer the thing. SSH to the FreeBSD box from the Mac and get used to working roughly remote. You can do this. It's just going to take persistence on the command line. -Original Message- From: Lost Idols [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 1:46 PM To: [EMAIL PROTECTED] Subject: ?? .tcshrc On OS X ?? I had tips from some people here to install the complete MySQL from Server Logistics on my Mac OS X 10.2 and so I did (I also installed Apache 2 and PHP from them). Now I'm on Step one, but got stuck here. (I always get stuck while trying this MySQL). In the pdf-file that came along with the installation it says: shell echo $SHELL This will return the path to the shell you are using. In most cases it will return either /bin/tcsh (tcsh shell) or /bin/bash (bash shell). If you are using the tcsh shell, you will have to modify the file .tcshrc located within your home directory. If this file does not exist, then you will have to create a new one. After opening the file in your text editor, add the following line at the bottom of the file: setenv PATH $PATH:/Library/MySQL/bin Save the file. But when I try to find this .tcshrc file (whisch is the one showing up when I write that line in my Terminal. I can't find it (not by using Sherlock or when looking). I guess it should be in MyComputer / Library / MySQL / bin. But it isn't. So I wrote the line they explain in the end of the text above and try to save it as .tcshrc, but my computer tells me I can't use a name with a dot in the beginning. So I tried to take that away and then drop the file from the desktop to the bin folder, but then it tells m I'm not allowed to change that folder?! What am I supposed to do??? I'm really a newbie here... and it seems I'll always be... I've tried this sooo many times the last week now. I'm about to give up soon. :-( _ Hitta rätt köpare på MSN Köp Sälj http://www.msn.se/koposalj -- 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: Comparing strings containing possible quotes
Hi Ya. I seem to have solved my problem if I clean out any punctuation from my search string (see below (ColdFusion script)) cfset form.searchtext=replace(form.searchtext,'[[:punct:]]','','all') When I perform the WHERE LHS-Containing-Quote LIKE RHS-without-quotes The LIKE appears to ignore the quotes (which is what I want) BUT IS THAT A FEATURE OF LIKE??? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update: key is not used
Corin Langosch [EMAIL PROTECTED] wrote: update of my last email sent. when i use the sql: 'EXPLAIN SELECT datum FROM actions ORDER BY datum' the key gets used: table type possible_keys key key_len ref rows Extra actions index NULL datum 8 NULL 75859 Using index however as soon as i select more column like sql: 'EXPLAIN SELECT datum,id FROM actions ORDER BY datum' the key isn't used anymore :-(. might this be bug of mysql? No. In the first case MySQL can take all data from the index file. In the second case it should read data file, too. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Delayed Insert Question
On Tue, Dec 09, 2003 at 12:17:41PM +0100, David Bordas wrote: So, i'm using INSERT DELAYED with some good succes. But I've got a question. If i decrease delayed_insert_limit to ten secondes for example, is that mean that delayed_queue will be flushed every ten secondes ? Is there an other variable that specify the flush time ? No - delayed_insert_limit refers to how many rows a DELAYED thread will insert at once before checking if any other SELECTs are waiting for the table. The process (and all related variables you can tweak) are documented here: http://www.mysql.com/doc/en/INSERT_DELAYED.html -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql speed test
hi! i have 2 machines. both of them running mysql daemon. i want to check the speed difference, and i am looking for a method how can i do this ? is there an official tool for this thing ? Vaso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
So, i'm using INSERT DELAYED with some good succes. But I've got a question. If i decrease delayed_insert_limit to ten secondes for example, is that mean that delayed_queue will be flushed every ten secondes ? Is there an other variable that specify the flush time ? No - delayed_insert_limit refers to how many rows a DELAYED thread will insert at once before checking if any other SELECTs are waiting for the table. The process (and all related variables you can tweak) are documented here: http://www.mysql.com/doc/en/INSERT_DELAYED.html Thanks Chris, I've read mysql doc sereval times, but i can't find any varaible that specify when the delayed queue was flushed. If I understand, I can increase delayed_insert_limit for better performance, but I should also increase the delayed_queue as well ? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
On Tue, Dec 09, 2003 at 02:18:58PM +0100, David Bordas wrote: I've read mysql doc sereval times, but i can't find any varaible that specify when the delayed queue was flushed. Well, I suppose that's because there isn't one. The DELAYED thread handles that by itself. You don't want it too large because if mysql crashes while you have rows sat waiting to be written, they're lost. If I understand, I can increase delayed_insert_limit for better performance, but I should also increase the delayed_queue as well ? If you increase delayed_insert_limit then you're effectively giving the DELAYED thread more preferencee to the table; it will write more rows (once it can, ie there's a phase of time where there's no locks on the table) in a batch, which potentially makes other selects wait longer. Inserting delayed_queue_size means the clients can pile more and more rows into the DELAYED thread while it gets chance to write. This may give your clients a bit of a boost, but only if the DELAYED thread fills up; at a default of 1000, you must be doing a lot of inserts to reach that. Remember if you have a lot of rows waiting and mysql crashes, they're lost. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table UPDATE Emulation
Mike Brum [EMAIL PROTECTED] wrote: Hopefully someone can help me out with this - I have two tables in mySQL: comments and diary I want to populate a new field in comments with the current values of diary - but only if they meet certain criteria. Now, the problem is this - my webserver is using an older version of mySQL that doesn't support UPDATEs from multiple tables (that's not possible until v4.0.4) and unfortunately, them updating the install isn't a possibility. I know if it was v4.0.4+ I could juse use: UPDATE comments,diary SET comments.diary_date=diary.date WHERE comments.refid=diary.ID I also don't think I can use sub-selects in queries either. Btw - I'm currently running v3.23.58. Use programming language to retrieve data with SELECT statement and then do UPDATE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: cross database queries
H. Steuer [EMAIL PROTECTED] wrote: im just wondering if there are any limitations in cross database queries like: SELECT one.* FROM db1.one, db2.two; are there any differences in joining tables from within various databases to joinin tables from within the same database? No. the background of my question is that various users which all have their own database need to share common data. so i wanted to create one common database which they all have access to. i did some tests and all succeeded. but want to be sure before heading production status. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Delayed Insert Question
Tuesday, December 09, 2003 2:51 PM Chris Elsworth wrote: If you increase delayed_insert_limit then you're effectively giving the DELAYED thread more preferencee to the table; it will write more rows (once it can, ie there's a phase of time where there's no locks on the table) in a batch, which potentially makes other selects wait longer. Inserting delayed_queue_size means the clients can pile more and more rows into the DELAYED thread while it gets chance to write. This may give your clients a bit of a boost, but only if the DELAYED thread fills up; at a default of 1000, you must be doing a lot of inserts to reach that. Thank you Chris, I think I understand now. Remember if you have a lot of rows waiting and mysql crashes, they're lost. Well, I know that, but loosing 2000 inserts when i made more than 3 000 000 a day isn't a big problem. This table is for statistics only, data isn't very important and MySQL doesn't crash as often happyily :) Now i just need to choose, I can boost the insert ratio but i'll take some risks, or i can leave all as default ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with mysqlimport
I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamkanono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul
running mysql 3.23 and mysql 4.0 on same linux/suse box
Hi there, I'm having problems running two versions of mysql simultaneously on the same box. The versions I'm using are: * mysql 3.23.48 * mysql 4.0.16 What happens: * I have the 3.23 version up and running (can see that it has 5 processes running with ps, and 'bin/mysqladmin status' shows that it's nicely running locally * When I start up the 4.0.16 version it seems to bring down the 3.23 version... First, the 4.0.16 version itself starts up with error messages and the 3.23 can no longer be queried ('bin/mysqladmin status' says it cannot connect). The error message from the 4.0.16 startup is: 'Can't start server : Bind on unix socket: Permission denied' What I did with the 4.0.16 is to have it run on a different socket file. Anyone can help me out on this? What could go wrong when you have two versions running? Am I running with wrong user? But then, why does the start of 4.0.16 somehow crash the 3.23 version (the processes of the 3.23 version are then still there but not reachable. Killing the child processes of the mysql daemon brings 3.23 up and running again)? Thanks for any help! cheers, -se -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport
is there a higher level directory that does not allow excecution? does it work if you run it as root? can you 'vi /root/mysql/gwarancje.txt'? just some thoughts Jeff Pawe Filutowski [EMAIL PROTECTED]To: [EMAIL PROTECTED] rfam.pl cc: Subject: Problem with mysqlimport 12/09/2003 10:27 AM I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL installation with SuSE Linux 9.0 and YaST
Franz, it could not be easier. 1. install MySQL using Yast's software menu. 2. use the Yast run level editor to have MySQL up at run levels 3 and 5 and to start the application. 3. be root in a xterm window and type: mybox:~ # mysql ... and you will see: mysql here you are accessing a running system from the client. Use GRANT to set a password for root and to add new users. Where is the problem? Liebe Grüsse Thomas Spahni On Sun, 7 Dec 2003, Franz Edler wrote: I am not very experienced with Linux, but SuSE Linux 9.0 and YaST makes it very comfortable for me to install various SW-packages. Therefore I try to install also MySQL with YaST. There are MySQL packages (Version 4.0.15) included in SuSE Linux 9.0 distribution and I try to install the server and the client with YaST. But after installation of these packages with YaST - which is the easy part - one has to configure various parameters for MySQL to work properly e.g. create the database files, define group and user, grant access ... Unfortunately the MySQL-manual (which is a big document) does not give any guidelines for installing with YaST. After several times re- and de-installing mysql and also SuSE-Linux itself I still have troubles to get the server running. Has anyone already done a MySQL-installation with SuSE Linux 9.0 and YaST? Is there any quick installation guide for this task? I would be very happy to get some help. Franz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: view warnings?
I'm pretty new to MySQL, but I already like it. Kudos to the developers! I recently did a mysqlimport on a few data files, and it came back with over 4000 warnings. How can I actually see what the warnings were? I'm still not sure what it was upset about... You can't. From 4.1.1 you can see warning if you use LOAD DATA INFILE statement: http://www.mysql.com/doc/en/SHOW_WARNINGS.html I used mysqlimport because I could import a bunch of files all at once. Does LOAD DATA have this capability? TIA. - B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ?? .tcshrc On OS X ??
I had tips from some people here to install the complete MySQL from Server Logistics on my Mac OS X 10.2 and so I did (I also installed Apache 2 and PHP from them). Now I'm on Step one, but got stuck here. (I always get stuck while trying this MySQL). In the pdf-file that came along with the installation it says: shell echo $SHELL This will return the path to the shell you are using. In most cases it will return either /bin/tcsh (tcsh shell) or /bin/bash (bash shell). If you are using the tcsh shell, you will have to modify the file .tcshrc located within your home directory. If this file does not exist, then you will have to create a new one. After opening the file in your text editor, add the following line at the bottom of the file: setenv PATH $PATH:/Library/MySQL/bin Save the file. =) The file is actually in your home directory. What are you using for your text editor? Pico is probably the easiest, so to do the step above, go to the terminal and type: pico ~/.tcshrc The .tcshrc file is hidden because it starts with a ., so you don't see it in the finder, nor with a regular ls of a directory. You have to do an ls -a to see it. But if you want to do this step a little easier, type this: echo 'setenv PATH $PATH:/Library/MySQL/bin' ~/.tcshrc fyi, the shell replaces the ~ with /Users/yourusername before it does anything else. But when I try to find this .tcshrc file (whisch is the one showing up when I write that line in my Terminal. I can't find it (not by using Sherlock or when looking). I guess it should be in MyComputer / Library / MySQL / bin. But it isn't. So I wrote the line they explain in the end of the text above and try to save it as .tcshrc, but my computer tells me I can't use a name with a dot in the beginning. So I tried to take that away and then drop the file from the desktop to the bin folder, but then it tells m I'm not allowed to change that folder?! What am I supposed to do??? I'm really a newbie here... and it seems I'll always be... I've tried this sooo many times the last week now. I'm about to give up soon. You're in unix now, which is really cool, but has a learning curve to it. I recommend reading the tutorials on www.osxfaq.com, they'll explain a lot. - B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for Spain and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Let's try some debugging on your data, as the query looks fine to me. What about running the following queries right in the MySQL console? SELECT * FROM properties WHERE country='Spain'; That should give you all you need but images.image_filename. If that returns what it should, then take reference_number from the results and lookup in images using it: SELECT image_filename FROM images WHERE reference_number=[reference_number]; (where [reference_number] is from the previous result) If the first query returns nothing, then your problem is that there's no record for properties.country='Spain'. If the second returns nothing, then there's no record for images.reference_number matching Spain's reference_number in properties. Either of those things being off would result in the actual join query returning nothing. Let me know how those turn out... -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ?? .tcshrc On OS X ??
.tcshrc is the config file for your settings on the environment variables for your session of tcsh. When your looking for $SHELL your looking for the command line interpretor. FreeBSD can use most any of them. bash, csh, ksh,, sh, tcsh, etc. Usually the .rc file is found in your home directory i.e. /home/bsmythe/.tcshrc the file usually contains things like your $PATH variable or $PROMPT. Your best bet is to type env (on FreeBSD at least). That should give you a list of environment variables if you like the way your command line works now then I wouldn't change too many things. Under OS X the home directories are in /Users. So you have a MAC. Look in your McManual to see where you set things like the type of command line prompt. Does it let you change the prompt from SHELL to SHELL_8:00AM:\ . The file you use to set this environment variable is the file you use to set the PATH. OS X behaves like most unixii, except for the different location of the home directories. OS X 10.2 defaults to tcsh, 10.3 defaults to bash. OS X 10 is claimed to be a kissing cousin to FreeBSD so I will make that assumption. First you will get stuck. But your system is like a 4wheeldrive and these lists are the wench to drag you out of places you ought not be. So add a good helping of persistence. You can do this. I'm an ex-truck driver. I'm pretty sure OS X is a combination of NetBSD and NeXT, but don't quote me on that. I use IRIX and OS X daily, and they are almost identical at the command line level (except that OS X has a bunch of extra tools for interacting with the GUI/clipboard/applescript/etc, which is awesome). Good scrappy computers can be found for less than 50$ for an IBM PC. Load it up with FreeBSD. Buy a Good FreeBSD book Like FreeBSD Unleashed by Michael Urban and Brian Tiemann. Then just port down the Mysql rdbms. Use your Mac to administer the thing. SSH to the FreeBSD box from the Mac and get used to working roughly remote. You can do this. It's just going to take persistence on the command line. There's no reason to do this! Why would anyone want to stick a headache-in-a-box on their desk. The Mac will do everything he needs as soon as he figures out how his shell works. - B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1.0
Is there anyway to still get the Solaris 9 64 bit binary for version 4.1.0 - now that 4.1.1 alpha appears to be out the 4.1.0 binaries don't seem to be available on the download page Thanks Mark Needleman Product Manager - Standards Sirsi Corporation email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with mysqlimport
If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Asking again... please help me!!!
I did ask this before, but haven't got any help yet. I'm trying this again... so please let me know if you know. Setting up MySQL and PHP on my Mac OS X 10.2 Just made it work... well, at least I now have a databse that I created and a table with two things inserted. I can also see them when I do a SELECT... So, since I know it's working, I started my DW MX to try to work from there, but I just get an error. I've been setting up the database with all the info and when I want to select from the list of databases I just get the following message: HTTP Error Code 404 File Not Found. Here are some possible reasons for the problem: 1) There is no testing server running on the server machine. What's wrong? Any clues here in this list? Staffan PS. I'm a newbie, so please write in newbie language ;-) _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Asking again... please help me!!!
HTTP 404 is an error code, when you try to reach a file, what is not exist. For example, if you have a webserver, http://mydomain.com and you have only an index.html nothing else, if you write http://mydomain.com/myfile.html then you will get this error, because myfile.html is not an existing file. It's also, if there is no WEBserver on that machine. So it's not MySql or PHP or Mac problem. Vaso -Original Message- From: Lost Idols [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:15 PM To: [EMAIL PROTECTED] Subject: Asking again... please help me!!! I did ask this before, but haven't got any help yet. I'm trying this again... so please let me know if you know. Setting up MySQL and PHP on my Mac OS X 10.2 Just made it work... well, at least I now have a databse that I created and a table with two things inserted. I can also see them when I do a SELECT... So, since I know it's working, I started my DW MX to try to work from there, but I just get an error. I've been setting up the database with all the info and when I want to select from the list of databases I just get the following message: HTTP Error Code 404 File Not Found. Here are some possible reasons for the problem: 1) There is no testing server running on the server machine. What's wrong? Any clues here in this list? Staffan PS. I'm a newbie, so please write in newbie language ;-) _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- 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]
How can I dump and empty a table?
Dear list: I apologize post this question, but I`m newbie with MySQL. I have a database called tacacs, it has many tables and one of them is called accounting. This table has many data stored since 2 month ago and it is very big and it increases frequently everyday. I`d like to know how can I dump (backup) the data from this table and left it empty periodically, because I have to do some queries from a Web UI? I`ll be very greateful if someone could give me some suggestions with this issue? Thank you, EDWIN LIMACHI N. DATACOM - Instalaciones Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Asking again... please help me!!!
MySQL doesn't return HTTP error codes. Your problem is not with mysql but with Dreamweaver's browser not being pointed at a running webserver. Did you set up Apache (or some other server) when you set up PHP and prove that both are functioning? Matt -Original Message- From: Lost Idols [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 11:15 AM To: [EMAIL PROTECTED] Subject: Asking again... please help me!!! I did ask this before, but haven't got any help yet. I'm trying this again... so please let me know if you know. Setting up MySQL and PHP on my Mac OS X 10.2 Just made it work... well, at least I now have a databse that I created and a table with two things inserted. I can also see them when I do a SELECT... So, since I know it's working, I started my DW MX to try to work from there, but I just get an error. I've been setting up the database with all the info and when I want to select from the list of databases I just get the following message: HTTP Error Code 404 File Not Found. Here are some possible reasons for the problem: 1) There is no testing server running on the server machine. What's wrong? Any clues here in this list? Staffan PS. I'm a newbie, so please write in newbie language ;-) _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- 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 can I dump and empty a table?
Lets do this: mysqldump -u user -p [database_name] [tablename] ./thefilewhatyouwouldliketocreate.dmp Check that file. VAso -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:25 PM To: [EMAIL PROTECTED] Subject: How can I dump and empty a table? Dear list: I apologize post this question, but I`m newbie with MySQL. I have a database called tacacs, it has many tables and one of them is called accounting. This table has many data stored since 2 month ago and it is very big and it increases frequently everyday. I`d like to know how can I dump (backup) the data from this table and left it empty periodically, because I have to do some queries from a Web UI? I`ll be very greateful if someone could give me some suggestions with this issue? Thank you, EDWIN LIMACHI N. DATACOM - Instalaciones Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975
Re: Problem with mysqlimport
I tryed this option but i got following error: mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: gwarancje MySQL version is 3.23.49 What Can I do ?? Regards - Original Message - From: Matt Griffin [EMAIL PROTECTED] To: 'Pawe3 Filutowski' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:11 PM Subject: RE: Problem with mysqlimport If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- 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]
Maximum query size
Is there an easy way to determine the largest sql query I can pass between a perl/C app to my MySQL database? It seems to wig out around the 1 meg range but without resorting to trial and error I'm not sure how to get an exact figure. Thanks, Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum query size
The default maximum packet size is 1 meg. You can check your current server setting with: SHOW VARIABLES LIKE 'max_allowed_packet'; See A.2.9 Packet too large Error http://www.mysql.com/doc/en/Packet_too_large.html for directions to change this setting in the server and client. Michael Mark wrote: Is there an easy way to determine the largest sql query I can pass between a perl/C app to my MySQL database? It seems to wig out around the 1 meg range but without resorting to trial and error I'm not sure how to get an exact figure. Thanks, Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.1 with cp1251
Hi! In my.cnf: [mysqld] ... default-character-set=cp1251 default_collation=cp1251_general_ci ... Then, after show variables like '%character%' I have: character_set_servercp1251 character_set_clientcp1251 character_set_connectioncp1251 character_set_resultscp1251 character_set_systemutf8 character_set_databaselatin1 ^ Why WBR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating temp table is causing locking?
Hello all, I'm having an issue with mySQL that causes some poor performance. I'm running mysql Ver 13.5 Distrib 4.1.0-alpha, for portbld-freebsd5.1 (i386); front-end is Apache 1.3.27 with PHP 4.1.1. My database is using all MyISAM tables, with 50 or so tables and about 60MB of total data. My overides on variable defaults are: [mysqld] set-variable = max_connections=500 set-variable = table_cache=128 set-variable = key_buffer_size=32M set-variable = query_cache_size=32M set-variable = long_query_time=3 set-variable = tmpdir=/usr/tmp CPU use on the mySQL server is usually 20% - 30%; but every so often it jumps to almost 100% and remains there for some time. My site slows to a crawl. When I check SHOW PROCESSLIST I see a large number of processes in 'Locked' status, and one as 'Creating tmp table'. When I manually kill this process, the server returns to normal. I turned on slow query logging and general query logging, and I'll be looking through them for any strange queries, but aside from that, is there anything I can do to prevent this locking issue? Thanks all, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
MySQL 4.1.1 create table : why negatives values need quotation marks?
Hi. First, sorry, if my problem is know or documented. I have made a rapid search on the MySQL site, but without success. Here is my problem : the following 'create table' command refuses to execute on 4.1.1, if -1 is not put between a quottation mark: create table testTable (name varchar(10), age smallint default -1); If the value -1 is changed into '-1', the command works. Is this the correct behaviour in 4.1.1 ? An application here (written by a colleague) was using numbers without quotation marks as default values in the create command. The application worked untill today. Before he updates the code, I would like to have more precisions. Following is the output of the create commande with a 4.1.1 and a 4.0.3 MySQL server. mysql select version(); +--+ | version()| +--+ | 4.1.1-alpha-standard | +--+ 1 row in set (0.00 sec) mysql create table testTable (name varchar(10), age smallint default -1); 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 '-1)' at line 1 mysql select version(); ++ | version() | ++ | 4.0.3-beta-max | ++ mysql create table testTable (name varchar(10), age smallint default -1); Query OK, 0 rows affected (0.01 sec) Does somebody have the same problem ? Any idea ? Thanks in advance. Christophe. -- *** Christophe DIARRA Institut de Physique Nucleaire 15, Rue Georges Clemenceau Bat 102 - S2I 91406 ORSAY Cedex Tel: (33) 1 69 15 65 60 Fax: (33) 1 69 15 64 70 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
limatation on user name in mysql - 16 characters
There is limitation on user name in mysql - it can be up to 16 characters. How change this limit? Need I recompile mysql? Thanks! OldFrog. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: limatation on user name in mysql - 16 characters
[snip] There is limitation on user name in mysql - it can be up to 16 characters. How change this limit? Need I recompile mysql? [/snip] [UNTESTED WARNING!] Why not just ALTER the user table? [/UNTESTED WARNING!] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing strings containing possible quotes
Three messages quoted, with my responses interspersed: zzapper wrote: [8 Dec 2003 19:07:49 -] Hi Ya, I have a typical select as follows SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%#form.searchtext#%') However users a complaining that if they search for for a name that contains a quote eg a development named King's Reach it is not found. A) Now it simply won't match King's Reach whether I include the quote or not. WHY?? (Have the quotes been converted ie to URL Encoding)) Consider what your query looks like when form.searchtext contains a single quote. Using your example, you get: SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King's Reach%') The single quote in King's completes the quoted string! I'll rewrite it with a newline for emphasis: SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King' s Reach%') s Reach%' is not valid SQL, so I expect that if you checked, you would find that mysql is throwing a syntax error in this case (or waiting for the 4th '). B) Now I can filter any quotes from form.searchtext easy enough, but how do I filter them the Left Hand Side eg from txtDevName?) You don't want to filter the quotes from the input string, because they exist in the data. (Well, you could filter from both sides, but that's inefficient.) What you need to do is escape the quotes with backslashes before sending them to mysql. Then your query will look like this: SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King\'s Reach%') So, you need to replace ' with \' in your form.searchtext. I'm not familiar with ColdFusion, but I expect you can use a variant of the replace function you mention below to do the job. PHP has a single function call to handle escaping text -- perhaps ColdFusion does as well? I look forward to seeing how you solve this. [9 Dec 2003 10:59:11 -] Is there no mysql solution to this? Most Where clauses are thus WHERE LHS Like/= RHS Now MySQL provides lots of operators for the RHS eg WHERE LHS RLIKE RHS But what I want to do is preprocess/filter the LHS before doing the comparison, I can find no information on this. In the past I have cheated by creating an additional column in my database eg txtDevNameClean which has all non-alphanumerics removed. am I barking up the wrong tree?? I think so. As I described above, I think the problem is with your query, not mysql. Hence, you must escape the search text on the application side to ensure valid SQL before sending to mysql. Note that single quotes are not your only worry. As your form stands now (I'm assuming based on the piece you've shown), imagine what could happen if the user entered something like King'); DELETE FROM TABLE ytbl_development WHERE txtDevName LIKE ' as the search text (form.searchtext). You would end up with SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%King'); DELETE FROM TABLE ytbl_development WHERE txtDevName LIKE '%') The moral is that you should always validate user input before passing it to mysql. [9 Dec 2003 11:54:58 -] Hi Ya. I seem to have solved my problem if I clean out any punctuation from my search string (see below (ColdFusion script)) cfset form.searchtext=replace(form.searchtext,'[[:punct:]]','','all') When I perform the WHERE LHS-Containing-Quote LIKE RHS-without-quotes The LIKE appears to ignore the quotes (which is what I want) BUT IS THAT A FEATURE OF LIKE??? It's hard to say without seeing the query. I don't believe LIKE ignores quotes, but if your wildcard (%) is in the right place, it could seem to. I think your emphasis on LHS versus RHS is confusing the issue. If you'd like to pursue this, post your query and the results you get so we can see what you mean. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.1 FTS 2-level?
Does Mysql 4.1.1 have the two level index system integrated into it for full text searches? Thanks. :) -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I dump and empty a table?
Hi Vaso: I tried with your suggestion, it works fine. I have the backup now. I have two questions: How can I erase the actual data from my table? and How can I restore them from my backup file? Thank you and best regards, EDWIN Vasoczki Ferenc [EMAIL PROTECTED] 09/12/2003 12:28 Para [EMAIL PROTECTED] cc Asunto RE: How can I dump and empty a table? Lets do this: mysqldump -u user -p [database_name] [tablename] ./thefilewhatyouwouldliketocreate.dmp Check that file. VAso -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:25 PM To: [EMAIL PROTECTED] Subject: How can I dump and empty a table? Dear list: I apologize post this question, but I`m newbie with MySQL. I have a database called tacacs, it has many tables and one of them is called accounting. This table has many data stored since 2 month ago and it is very big and it increases frequently everyday. I`d like to know how can I dump (backup) the data from this table and left it empty periodically, because I have to do some queries from a Web UI? I`ll be very greateful if someone could give me some suggestions with this issue? Thank you, EDWIN LIMACHI N. DATACOM - Instalaciones Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is mySQL right for me??
From what you describe, I have to wonder if a database is even truly appropriate for the situation at all. How large is the data set in question? If it's very small, save for actual image data (which could be stored as files) the question becomes how often and under what circumstances the data will *change* If it's relatively fixed then why not hard-code it in an include file (or encode it as XML and slurp it in via whatever nifty mechanism ASP provides for doing so)? Assuming that a database is the appropriate solution here then MySQL should be just fine. -JF -Original Message- From: GREG BARBER [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 7:21 PM To: '[EMAIL PROTECTED]' Subject: Is mySQL right for me?? Hi All, I want to create a small database that allows a user to populate a report by choosing many different parameters whcih are linked to a variety of images and text entries. The front end would be HTML/ASP based and would feature several choices that the user makes to define what is included in the final report or form. The output would then be printed and forgotten about. The next user could then come along and create their own custom page from the options available. Is mySQL capable of creating such as system? I can't imagine it would be complicated, rather just a collection of queries that are then arranged on the output page according to preset positioning instructions. Regards Greg MAIL IS CONFIDENTIAL. If you have received this e-mail in error, please notify us by return e-mail and delete the document. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Barwon Health is not liable for the proper and complete transmission of the information contained in this communication or for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for sharing members database between different portals
On Tue, 09 Dec 2003 15:26:10 -0600 Tariq Murtaza [EMAIL PROTECTED] wrote: Please comment on Best Practices for sharing members database between different portals. Suppose we have 3 portals running on different networks. Assignment is to make a single Login/Pass for all portals, means once LogedIn in one of the portal, could able to access the other portals without loging In. Constraints are, every portal have different Database structure and also have different Global / Session variables. In one word, webservices, more specifically the nuSoap library (for PHP). Take the user databases out of the 3 portals and unify them in one place. Add an interface (webservice server) in front of the user db that will handle user authentication and session tasks. Add code in the 3 portals that will talk SOAP to the user db interface, thus making the portals webservice clients. The beautiful part is, the portals can have completely different databases, languages, or webservers. They just each need a bunch of functions that speak SOAP and that implement a common user auth/session API. If you're worried about having to connect to the user db for each page a portal serves, you can cache the session ID locally in each portal's own database after the authentication. But you'll run into some other issues this way. There's however one big problem I see here: I don't see how you're gonna convince a browser to remember state information from one site address and apply it automatically to the other two portals upon login. You can't set cookies for other domains. -- Skippy - Romanian Web Developers - http://ROWD.ORG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with mysqlimport
Paul, --local is a valid option for mysqlimport in 3.23.49 according to the manual. (Source: http://www.cict.fr/app/mysql/manual.html#mysqlimport) What is the entire command you are using? Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 12:06 PM To: [EMAIL PROTECTED] Subject: Re: Problem with mysqlimport I tryed this option but i got following error: mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: gwarancje MySQL version is 3.23.49 What Can I do ?? Regards - Original Message - From: Matt Griffin [EMAIL PROTECTED] To: 'Pawe3 Filutowski' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:11 PM Subject: RE: Problem with mysqlimport If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA LOCAL INFILE
Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL License
Hi, I recently purchased a licence. I received this email. Do I download the binaries myself or are you sending me a box? If I download it myself, do I need to create an account? If I have one, I've long forgotten it. Regards, Len Buchanan Datascape Technologies Inc. At 06:07 PM 12/5/2003 +0100, you wrote: Dear Customer: Included is your MySQL Classic License, ordered from MySQL AB. License number(s): 299376. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in WinMySQLadmin 1.4
My SQL Team, When launching WinMySQLadmin 1.4 on multi-displayed systems, the admin application launches center between both displays, not display 1, as it should. Thanks, Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE
From: Daniel Kiss [mailto:[EMAIL PROTECTED] Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 I believe it's... LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE tablename FIELDS TERMINATED BY ',' OPTONALLY ENCLOSED BY ''; -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: limatation on user name in mysql - 16 characters
Jay Blanchard wrote: [snip] There is limitation on user name in mysql - it can be up to 16 characters. How change this limit? Need I recompile mysql? [/snip] [UNTESTED WARNING!] Why not just ALTER the user table? [/UNTESTED WARNING!] Thank for reply. I ALTER all 'User' columns in 'mysql' database, but it bring no effect. OldFrog. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bug in WinMySQLadmin 1.4
Having a multi-display system myself, I disagree with this being a bug. It's more of a lack of a feature, being multi-display aware Also, some multi-display software (matrox) tricks windows into it thinking it's one display with a _very_ weird pixel width (2048 x 768), skipping over window's internal multi-display support. My $0.02, Dan Greene -Original Message- From: Ray Ragan [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:10 PM To: [EMAIL PROTECTED] Subject: Bug in WinMySQLadmin 1.4 My SQL Team, When launching WinMySQLadmin 1.4 on multi-displayed systems, the admin application launches center between both displays, not display 1, as it should. Thanks, Ray -- 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: LOAD DATA LOCAL INFILE
If it's an option, I would run your datafile through a processor (sed on unix, ultraedit on windows) to search and replace the string with \ and try it with fields terminated by ',' optionally enclosed by '' as mentioned by Mike Johnson's posting (escaped by '\' is default) -Original Message- From: Daniel Kiss [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- 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: LOAD DATA LOCAL INFILE
Thanks for the help. The problem was the line terminating character. My source files are in DOS format '\r\n' and not the default posix one '\n'. Thanks again. If it's an option, I would run your datafile through a processor (sed on unix, ultraedit on windows) to search and replace the string with \ and try it with fields terminated by ',' optionally enclosed by '' as mentioned by Mike Johnson's posting (escaped by '\' is default) -Original Message- From: Daniel Kiss [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- 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: Maximum query size
Mark wrote: Is there an easy way to determine the largest sql query I can pass between a perl/C app to my MySQL database? It seems to wig out around the 1 meg range but without resorting to trial and error I'm not sure how to get an exact figure. the max size would be limited by the variable max_allowed_packet so, if you do a show variables like 'max_allowed_packet' it will show you the limit. By default, it is set to 1047552 bytes. If you want to increase that, add a line to the server's my.cnf file, in the [mysqld] section : set-variable = max_allowed_packet=2M and restart mysql server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avarage monthly import
Andrew Braithwaite wrote: Are you using MySQL? yes of course :) OK, if you are then first simplify your query: group by 1 thanks, I didn't know it. Then add the AVG column which will work ok with the group by : select date_format(dt_imp,'%Y/%m') as date, SUM(imp), AVG(imp) from sp group by 1 order by 1 this doesn't work. this query extract the avarage of the import in the month, but I want the avarage importo for all the month. I could explain me not very well, my English isn't good. Let me try with an example: if I have the same data 2002-12 10 2003-01 5 2003-02 11 the avarage I want is ((10 + 5 + 11) / 3) = 8.6. with the example you gave me I got +-+++ | month | SUM| avg| +-+++ | ... | | 2002/02 | 238.30 | 14.017647 | | 2002/03 |1385.95 | 62.997727 | | 2002/04 | 475.30 | 20.665217 | | 2002/05 | 171.10 | 10.693750 | | ... | +-+++ thanks for your patience G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test
test - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: replication/binary log
Diana Soares wrote: Use PURGE {MASTER|BINARY} LOGS TO 'log_name' instead of RESET MASTER. From the manual: Deletes all the binary logs listed in the log index that are strictly prior to the specified log or date. The logs also are removed from this list recorded in the log index file, so that the given log now becomes the first. (...) You must first check all the slaves with SHOW SLAVE STATUS to see which log they are reading, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log. http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html That worked quite nicely, thank you :) Is there some reason why MySQL keeps these log files ? Why wouldn't it delete them as a new one got created? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication/binary log
On Tue, 9 Dec 2003, Mayuran Yogarajah wrote: Diana Soares wrote: Use PURGE {MASTER|BINARY} LOGS TO 'log_name' instead of RESET MASTER. From the manual: Deletes all the binary logs listed in the log index that are strictly prior to the specified log or date. The logs also are removed from this list recorded in the log index file, so that the given log now becomes the first. (...) You must first check all the slaves with SHOW SLAVE STATUS to see which log they are reading, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log. http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html That worked quite nicely, thank you :) Is there some reason why MySQL keeps these log files ? Why wouldn't it delete them as a new one got created? thanks You can also use those to restore a database that is lost somehow. Just run all the binary logs and pipe them into mysql from the last snapshot you have taken a backup from. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
. Dear List, My thanks to those who replied. I'm embarrassed as anything - the bloke I'm doing this thing for has now changed his tiny mind and has decided to have a set number of images per property, whereas before he was contemplating accepting a widely variable number. I can thus stick all the fields in the one table and, with that, my need for Joins goes west. While this now needs loads'v table reconstruction, at least I'm well able in that area. Thanx again for your assistance and offers of assistance. Yrs, Iain. - I keep having transmission trouble on my bike - my legs get tired www.johnstone-wheelers.co.uk The friendliest cycling club in Scotland?! And now - The Bunch! www.johnstone-wheelers.co.uk/phpBB2/index.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing strings containing possible quotes
On Tue, 09 Dec 2003 13:24:03 -0500, Michael Stassen [EMAIL PROTECTED] wrote: You don't want to filter the quotes from the input string, because they exist in the data. (Well, you could filter from both sides, but that's inefficient.) What you need to do is escape the quotes with backslashes before sending them to mysql. Then your query will look like this: Michael, thanks for answering I've solved some of my quote problems (which were just down to mistakes).(BTW LIKE does NOT ignore quotes) How would you filter say non-alphanumerics from the input string? What is the syntax? I've previously wanted to do a regexp on the input string but this not seem to be permitted zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] . Dear List, My thanks to those who replied. I'm embarrassed as anything - the bloke I'm doing this thing for has now changed his tiny mind and has decided to have a set number of images per property, whereas before he was contemplating accepting a widely variable number. I can thus stick all the fields in the one table and, with that, my need for Joins goes west. While this now needs loads'v table reconstruction, at least I'm well able in that area. Thanx again for your assistance and offers of assistance. Damn, I was really hoping to get to the bottom of the problem. Ah well. FYI, might I suggest going with your original design anyway? If this guy changes his mind like this often, then he may very well decide, a month after launch, that he wants to go back to a variable # of images per property. In that case you'd be right back at the drawing board. If you build it that flexible from day one, though, he can change his mind to his heart's content. Anyway, good luck! -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tune ?
Hello, Is there any RH 9 kernel tuning tips for system running Mysql 4.0 db? Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Popper mysql db problem
I'm new to MySQL, so this might be a stupid question/problem, but I can't figure out the answer: I'm trying to install the Popper webmail client on my web server. I'm following the instructions exactly. It told me to log in to the mysql console with mysql -u root -p and then create database popper, which I did. It then said to GRANT insert, update, select, delete, alter, drop, create ON popper.* TO [EMAIL PROTECTED] IDENTIFIED BY 'theuserpassword'; I changed the [EMAIL PROTECTED] to [EMAIL PROTECTED] and the 'theuserpassword' to an appropriate password, but whenever I switch to the mysql user account and mysql -p popper and put the specified password in, it gives me a ERROR 1045: Access denied for user: '[EMAIL PROTECTED]@localhost' (Using password: YES) I've even tried mysql [EMAIL PROTECTED] -p and it gives me the same error. What am I doing wrong? -Michael Sullivan- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Solaris 9 sparc with Perl
Greetings, I can not get the DBD::mysql module for perl to install correctly. It continues to complain about the mysql.so file. What worries me is the solaris dist does not include a libmysqlclient.so file which I do believe the linux DBD:mysql needs to install. Any help is most welcome. Trevor
Setting MySql Port
Hi All, I want to change the default port on mysql server from 3306 to 5 (for example). The server is NT. Has anyone done this before? Any help really appreciated, Cheers Gavin
Production problem porting from PHP to ASP
Dear Members, For the last two years we have had a large software application written in PHP 4.0.6 with a MySQL 3.23.56 database all running on a single box with RedHat Linux 9.0. This has worked fantasticly and it is very fast. For certain business reasons that I wont go into detail here, we had to rewrite all the code from PHP to Microsoft ASP. The box that contained the MySQL database on Linux remained as is. A second box was purchased and runs the ASP code on Windows 2000 Server. The existing database on the Linux box is called by an ODBC connection on the Windows machine. The problem is that now the software runs terribly slower. The reason could not be that anything is wrong with the MySQL database since the database on the Linux box was absolutely unchanged. I suspect that the culprit is that PHP connects through a native MySQL driver, while in our new way ASP connects through ODBC. Does anyone have any ideas about this ? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting MySql Port
On Wed, 10 Dec 2003, Gavin Dimmock wrote: Hi All, I want to change the default port on mysql server from 3306 to 5 (for example). The server is NT. Has anyone done this before? Any help really appreciated, Can either modify the service to start with the --port parameter or add in your my.cnf (or my.ini, whichever you're using) the line port = 5 under the [mysqld] section. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Production problem porting from PHP to ASP
Yes ODBC connections is considerably slower i have found, it could also be the native connections class you may be using for ASP, but why why why. Bad mistake. Dear Members, For the last two years we have had a large software application written in PHP 4.0.6 with a MySQL 3.23.56 database all running on a single box with RedHat Linux 9.0. This has worked fantasticly and it is very fast. For certain business reasons that I wont go into detail here, we had to rewrite all the code from PHP to Microsoft ASP. The box that contained the MySQL database on Linux remained as is. A second box was purchased and runs the ASP code on Windows 2000 Server. The existing database on the Linux box is called by an ODBC connection on the Windows machine. The problem is that now the software runs terribly slower. The reason could not be that anything is wrong with the MySQL database since the database on the Linux box was absolutely unchanged. I suspect that the culprit is that PHP connects through a native MySQL driver, while in our new way ASP connects through ODBC. Does anyone have any ideas about this ? David -- 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: Production problem porting from PHP to ASP
David Potter wrote: Dear Members, For the last two years we have had a large software application written in PHP 4.0.6 with a MySQL 3.23.56 database all running on a single box with RedHat Linux 9.0. This has worked fantasticly and it is very fast. For certain business reasons that I wont go into detail here, we had to rewrite all the code from PHP to Microsoft ASP. The box that contained the MySQL database on Linux remained as is. A second box was purchased and runs the ASP code on Windows 2000 Server. The existing database on the Linux box is called by an ODBC connection on the Windows machine. The problem is that now the software runs terribly slower. The reason could not be that anything is wrong with the MySQL database since the database on the Linux box was absolutely unchanged. I suspect that the culprit is that PHP connects through a native MySQL driver, while in our new way ASP connects through ODBC. Does anyone have any ideas about this ? David That's right. ODBC is an additional abstraction layer between your code and MySQL, whereas the MySQL driver that mod_php uses is optimised for use explicitely between MySQL and PHP. There is a lot of extra baggage that you're dragging around with ODBC. Also, I assume that the app originally ran on the one box, ie apache with mod_php was on the same box as MySQL. Putting the web server on a different box and running queries across a network is obviously going to slow things down. I would also expect that IIS / ASP is slower than Apache / PHP, no matter what DB drivers you're using. So yes, your app should run considerably slower. If you post some details about what you're doing, maybe someone can give you some pointers as to how to optimise things. Bad luck about the decision from above :-( -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and failover question
I'm thinking about doing this: Setting up 2 mysql servers each replicating to the other (each a slave of the other). Code the connect/j connection creation to try one server on connect. If it fails, connect to the other server. Both servers are writeable, but my code is never talking to more than one server at a time. The one question I have here is this: DBX is replicating to DBY. DBY is replicating to DBX. If apps are writing to DBX and DBX fails, then apps will reconnect to DBY and continue writing. Is it possible that some writes from DBX haven't replicated to DBY, conflicting writes occur in DBY and when DBX is brought back online, DBY's slave SQL thread fails when it sees DBX's unpropagated writes? What are ways to get around this short of a DBA manually skipping the conflicts in DBY? Thanks
INSERT INTO FROM same table failing
Hi, In mysql 4.014, the below INSERT statement works fine: INSERT INTO TEST_TABLE SELECT 20, name FROM TEST_TABLE WHERE id = 10 But in mysql 4.1, it fails, I get a message: Error Code : 1066 Not unique table/alias: 'TEST_TABLE' (10 ms taken) Question: Is this a problem with 4.1 or is it supposed to fail? thanks, Ramesh Table is: CREATE TABLE TEST_TABLE (id mediumint(9) NOT NULL, name varchar(255)) TYPE = InnoDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sending array data using php mail
Hi, can anyone tell me what is the problem of my script: $sql2 = SELECT * FROM Cash WHERE HP='$HP' and SignUpDate='$SignUpDate'; $rows1 = mysql_query($sql2,$linkptr1) ; $OwnnerMail =[EMAIL PROTECTED]; $Subject = Testing; while ( $row = mysql_fetch_array($rows1) ) { $newvalue = $row[Password]; echo $newvalue\n; } $Body = $newvalue; $From = $HP; mail( $OwnnerMail,$Subject, $Body, From: $From); I can see all the data for $newvalue in browser. But, when i receive email, i can only see the last data. can i know what is the problem? thank you. - This mail sent through IMP: http://horde.org/imp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sending array data using php mail
hi the error is in this line: $newvalue = $row[Password]; make it $newvalue.= $row[Password]; (notice ^ the dot - for concatenation with the previous value). and if u want to see each password in the browser too, in that same loop, put: echo {$row[Password]\n; so it would now look like: while ( $row = mysql_fetch_array($rows1) ) { $newvalue.=$row[Password]; echo {$row[Password]\n; } u may also want to use a br in ur echo line to make the browser output nicer, the \n affects only the html code. and while u're at it, put a \n in $newline so that ur email looks nicer. abs BT Yahoo! Broadband - Save £80 when you order online today. Hurry! Offer ends 21st December 2003. The way the internet was meant to be. http://uk.rd.yahoo.com/evt=21064/*http://btyahoo.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading from 3.23 to 4.0 and trouble vith auto_increment
Hi, just got a new MacOS X Server 10.3 (Panther) that came with MySQL 4.0.14. Previous system we had was MacOS X 1.2, the really old one. I old system I was running MySQL 3.23.39 (if I now remember it right). I have good reason to believe that we made the change from old to new by the book. We took double backups from every table we had. Then we installed the new system and uploaded the databases. Everything was ok, mostly. For some strange reason, some of the tables just went berjerk. The main trouble is in the id field and it's auto_increment. Normally when I add a new line the new id is previous+1 (like 8993, 8994, 8995 ect.), but now the new id would be 0 and after that when I try to add a new line, I get the announcement: Duplicate entry '0' for key 1 The strange thing is that this does not happen in every table, just in some of them. Does anyone have a clue what is happening? TIA Jude Vihervaara www.paikalliset.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql user
Greetings every body I'm developing a web application using JSP ,Mysql and jrun application server.i need the code of user authentication in jsp.Can any one supply the same Your help would be appreciated Regards Aby Technical Services Abhinav Chaturvedi TransAction Solutions Pty Ltd PO Box 6764 Silverwater NSW 1811 Ph (02) 9647-4624 Mob +61(0) 422-149-614 Fax (02) 9647- 4660 email[EMAIL PROTECTED] Warning! This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed. This communication may contain protected material. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. If you have received this email in error, please telephone the sender on (02) 9647 4624 TransAction Solutions employs third party virus checking software.TransAction Solutions will not be held responsible for the inability of a third party software packages to detect or prevent the propagation of any virus how ever so generated -Original Message- From: Binay [mailto:[EMAIL PROTECTED] Sent: Friday, 5 December 2003 6:25 PM To: mysql users Subject: Mysql user Hi all, Can any one tell me the command to list all mysql users ??? Thanks in advance Binay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]