Re: Innodb vs myisam
Hi, I have executed ANALYZE TABLE for myisam tables, but still myisam is showing more scanning of rows as compared to innodb. What does ANALYZE TABLE command exactly do for myisam storage engine. Thanks Krishna On Wed, Apr 2, 2008 at 9:48 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Apr 2, 2008 at 8:52 AM, Jay Pipes [EMAIL PROTECTED] wrote: The MyISAM isn't scanning more rows. It's that the InnoDB rows output in EXPLAIN is an estimate and the MyISAM one is accurate... -jay Also, if he was testing one storage engine vs another he might have dumped the table and reimported with the new engine. If he did not ANALYZE TABLE for a myisam then EXPLAIN will give large row counts in my experience. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
--log-slave-update
Hi All, I have Master - Slave setup, with just one slave. Can i set --log-slave-update on slave. I have mysql running on debain with 8 cpu and 8 GB RAM. Also in this link http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html Its says not to set --log-slave-update on slave, so does this apply only if there are more than one slave connecting to the same master, or does it apply also on setup with one master and one slave. regards anandkl
Re: Innodb vs myisam
Hi, On myisam storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | user_course_pay_comp1 | 30 | NULL| *256721* | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+---+-+-++-+ 2 rows in set (0.00 sec) On innodb storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+-+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | idx_user_course_payment | 9 | NULL| *256519* | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+-+-+-++-+ 2 rows in set (0.00 sec) I have executed ANALYZE TABLE COMMAND on both the system (innodb and myisam) Yet there is a small difference. Highlighted in red color Is it the behavior of myisam or innodb or interal working of the storage engines. Thanks, Krishna On Wed, Apr 2, 2008 at 9:06 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Horribly ugly stuff I know I sure as heck am not going to spend half an hour to turn those queries into something understandable, and I expect no one else will either. If you want help please remove all extraneous details (turn table and columns names in t1,t2,col1,col2, etc or descriptive names like parent, child, datetime_end) and send out something that is easy to reproduce. You get a cupcake if you include ddl that populates itself with random data. Also, using /G instead of a semi colon will make database output a heck of a lot easier to read in email form. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
Re: Cross database queries
On Thu, Apr 3, 2008 at 2:04 AM, Ben Roberts [EMAIL PROTECTED] wrote: Hello, Does anybody have any info on cross-database joins? i.e. doing a join across various tables that are located inside different databases (albeit on the same database server). Yes, Implemented and working fine without any issue. I'm interesested in any potential pitfalls, limitations, and performance issues. There doesn't appear to be much information about this on the web (at least not that I can find so far). Does querying multiple databases in a single query count as multiple connections? No I'm using the InnoDB engine. Do foreign key constraints work across different databases too? Haven't tried Is using cross-database joins considered good practice? Its just like a single database Thanks in advance for any help. Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Krishna Chandra Prajapati Email-id: [EMAIL PROTECTED]
Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
Just try mysqladmin ping that whether mysql is working or not
Krishna
On Thu, Apr 3, 2008 at 5:45 AM, Vidal Garza [EMAIL PROTECTED] wrote:
Hi...
I need test mysql connections but i have a problem...
freebsd# uname -a
FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan 16
04:18:52 UTC 2008 [EMAIL PROTECTED] mailto:
[EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386
freebsd# mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2
freebsd# more /var/db/mysql/my.cnf
[CLIENT]
port = 3306
socket = /tmp/mysql.sock
[MYSQLD]
port = 3306
ndbcluster
socket = /tmp/mysql.sock
query_cache_type = 1
query_cache_size = 26214400
ndb-connectstring=192.168.6.1
max_user_connections = 500
max_connections = 3000
max_connect_errors = 10
table_cache = 2048
thread_cache_size = 500
# log-bin=/usr/local/etc/mysql/log-binario.txt
[MYSQL_CLUSTER]
ndb-connectstring=192.168.6.1
freebsd#
on the script i have...
cnt=0
while true; do
query=insert into Mytable values('99','`date +%d-%H:%M`','$cnt');
/usr/local/bin/mysql -u root -ppasswd -e $query MYDB /dev/null
if [ $cnt -eq 99 ];then
break
else
cnt=`expr $cnt + 1`
fi
done
but it return error on the screen like and this its my PROBLEM! I would
like that all connections finish ok. The errors not apear on mysql_error
file.
: Can't connect to local MySQL server through socket '/tmp/mysql.sock'
(61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (61)
: Can't connect to local MySQL server through socket '/tmp/mysql.sock'
(61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (61)
: Can't connect to local MySQL server through socket '/tmp/mysql.sock'
(61)
ERRORERROR 2002 (HY000) 2002 (HY000): Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (61)
: Can't connect to local MySQL server through socket '/tmp/mysql.sock'
(61)
ERROR 2002 (HY000)ERROR: Can't connect to local MySQL server through
socket '/tmp/mysql.sock' (61)
The file mysql.sock exist...
freebsd# ll /tmp
total 10
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .ICE-unix
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .X11-unix
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .XIM-unix
drwxrwxrwt 2 root wheel 512 Mar 13 04:00 .font-unix
drwxrwxr-x 2 root operator 512 Mar 10 06:44 .snap
srwxrwxrwx 1 mysql wheel 0 Apr 2 16:04 mysql.sock
freebsd#
i change the owner but nothing
freebsd# ll /tmp/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Apr 2 16:04 /tmp/mysql.sock
freebsd#
the Mysql its up
freebsd# ps awx | grep mysqld
25121 p2 S 1:21.87 [mysqld]
freebsd#
When i connect to mysql it show me error
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
but i try again and i in
mysql show status like 'Thre%';
+---+---+
| Variable_name | Value |
+---+---+
| Threads_cached | 94 |
| Threads_connected | 26 |
| Threads_created | 120 |
| Threads_running | 3 |
+---+---+
4 rows in set (0.14 sec)
mysql
--
Ing. Vidal Garza Tirado
Depto. Sistemas
Aduanet S.A. de C.V.
Tel. (867)711-5850 ext. 4346, Fax (867)711-5855.
Ave. César López de Lara No. 3603 Int. B Col Jardín.
Nuevo Laredo, Tamaulipas, México.
--
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que está limpio.
For all your IT requirements visit: http://www.aduanet.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Krishna Chandra Prajapati
MySQL DBA,
Email-id: [EMAIL PROTECTED]
Re: --log-slave-update
Hi, There is nothing that, you can not enable log_slave_updates on slave. Basically, it is useful in a situation when you have 1 master and 2 or more slave in chain series. In this model 1st slave server should work as master as well as slave. So, log_slave_update and binlog can be enabled to log all the queries to binlog. In this way 2nd slave get the updates from 1st slave. Krishna Chandra Prajapati On Thu, Apr 3, 2008 at 12:02 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have Master - Slave setup, with just one slave. Can i set --log-slave-update on slave. I have mysql running on debain with 8 cpu and 8 GB RAM. Also in this link http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html Its says not to set --log-slave-update on slave, so does this apply only if there are more than one slave connecting to the same master, or does it apply also on setup with one master and one slave. regards anandkl -- Krishna Chandra Prajapati MySQL DBA, Email-id: [EMAIL PROTECTED]
Can some one help me write it shorter?
I need querylike this that make a row of numbers (in one execute) SELECT a from ( SELECT -1 a UNION ALL SELECT -2 UNION ALL SELECT -3 UNION ALL SELECT -4 UNION ALL SELECT -5 UNION ALL SELECT -6 UNION ALL SELECT -7 UNION ALL SELECT -8 UNION ALL SELECT -9) t; -- View this message in context: http://www.nabble.com/Can-some-one-help-me-write-it-shorter--tp16467090p16467090.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: only myisam storage engine
Hi All, i set the skip-innodb in my.cnf, but when i do the below, it still shows some default values assigned to innodb parameters, can i avoid this as there is some amount of memory allocated to innodb, and i dont what that. I want all resource to allocated to only myisam. Please let me know how i can achive this. mysql show variables like '%innodb%'; +-+--+ | Variable_name | Value| +-+--+ | have_innodb | DISABLED | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8| | innodb_buffer_pool_awe_mem_mb | 0| | innodb_buffer_pool_size | 8388608 | | innodb_checksums| ON | | innodb_commit_concurrency | 0| | innodb_concurrency_tickets | 500 | | innodb_data_file_path | | | innodb_data_home_dir| | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1| | innodb_file_io_threads | 4| | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1| | innodb_flush_method | | | innodb_force_recovery | 0| | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | 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_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0| | innodb_mirrored_log_groups | 1| | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8| | innodb_thread_sleep_delay | 1| +-+--+ On 4/2/08, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Thanks a lot for you reply. Will do this setup. regards anandkl On 4/2/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Enter in my.cnf file [mysqld] skip-bdb skip-innodb Regards Rakesh -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2008 9:59 AM To: MySql Subject: only myisam storage engine Hi All, We have a linux box running debain, with 8 cpu and 8 GB RAM, we want only myisam engine to be running on this. So, should i not setup any innodb parameters or is there any other way to have only myisam engine running on this machine, please let me know. regards anandkl The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: --log-slave-update
But, it also says, that if slave1 is made master and if log-slave-update is set on it, then slave2 might receive data that it might have arleady got from the old master, which might cause errors like duplicate keys etc. On 4/3/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, There is nothing that, you can not enable log_slave_updates on slave. Basically, it is useful in a situation when you have 1 master and 2 or more slave in chain series. In this model 1st slave server should work as master as well as slave. So, log_slave_update and binlog can be enabled to log all the queries to binlog. In this way 2nd slave get the updates from 1st slave. Krishna Chandra Prajapati On Thu, Apr 3, 2008 at 12:02 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have Master - Slave setup, with just one slave. Can i set --log-slave-update on slave. I have mysql running on debain with 8 cpu and 8 GB RAM. Also in this link http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html Its says not to set --log-slave-update on slave, so does this apply only if there are more than one slave connecting to the same master, or does it apply also on setup with one master and one slave. regards anandkl -- Krishna Chandra Prajapati MySQL DBA, Email-id: [EMAIL PROTECTED]
Re: only myisam storage engine
Ananda Kumar schrieb: Hi All, i set the skip-innodb in my.cnf, but when i do the below, it still shows some default values assigned to innodb parameters, can i avoid this as there is some amount of memory allocated to innodb, and i dont what that. I want all resource to allocated to only myisam. Please let me know how i can achive this. maybe compile mysql without innodb support mysql show variables like '%innodb%'; +-+--+ | Variable_name | Value| +-+--+ | have_innodb | DISABLED | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8| | innodb_buffer_pool_awe_mem_mb | 0| | innodb_buffer_pool_size | 8388608 | | innodb_checksums| ON | | innodb_commit_concurrency | 0| | innodb_concurrency_tickets | 500 | | innodb_data_file_path | | | innodb_data_home_dir| | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1| | innodb_file_io_threads | 4| | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1| | innodb_flush_method | | | innodb_force_recovery | 0| | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | 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_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0| | innodb_mirrored_log_groups | 1| | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8| | innodb_thread_sleep_delay | 1| +-+--+ On 4/2/08, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Thanks a lot for you reply. Will do this setup. regards anandkl On 4/2/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Enter in my.cnf file [mysqld] skip-bdb skip-innodb Regards Rakesh -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2008 9:59 AM To: MySql Subject: only myisam storage engine Hi All, We have a linux box running debain, with 8 cpu and 8 GB RAM, we want only myisam engine to be running on this. So, should i not setup any innodb parameters or is there any other way to have only myisam engine running on this machine, please let me know. regards anandkl The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: only myisam storage engine
you mean to day, i install mysql using source. But i have installed using binary, is there any way i can do it . regards anandkl On 4/3/08, Uwe Kiewel [EMAIL PROTECTED] wrote: Ananda Kumar schrieb: Hi All, i set the skip-innodb in my.cnf, but when i do the below, it still shows some default values assigned to innodb parameters, can i avoid this as there is some amount of memory allocated to innodb, and i dont what that. I want all resource to allocated to only myisam. Please let me know how i can achive this. maybe compile mysql without innodb support mysql show variables like '%innodb%'; +-+--+ | Variable_name | Value| +-+--+ | have_innodb | DISABLED | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8| | innodb_buffer_pool_awe_mem_mb | 0| | innodb_buffer_pool_size | 8388608 | | innodb_checksums| ON | | innodb_commit_concurrency | 0| | innodb_concurrency_tickets | 500 | | innodb_data_file_path | | | innodb_data_home_dir| | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1| | innodb_file_io_threads | 4| | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1| | innodb_flush_method | | | innodb_force_recovery | 0| | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | 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_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0| | innodb_mirrored_log_groups | 1| | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8| | innodb_thread_sleep_delay | 1| +-+--+ On 4/2/08, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Thanks a lot for you reply. Will do this setup. regards anandkl On 4/2/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Enter in my.cnf file [mysqld] skip-bdb skip-innodb Regards Rakesh -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2008 9:59 AM To: MySql Subject: only myisam storage engine Hi All, We have a linux box running debain, with 8 cpu and 8 GB RAM, we want only myisam engine to be running on this. So, should i not setup any innodb parameters or is there any other way to have only myisam engine running on this machine, please let me know. regards anandkl The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
On Wed, Apr 2, 2008 at 11:32 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, On myisam storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | user_course_pay_comp1 | 30 | NULL| 256721 | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+---+-+-++-+ 2 rows in set (0.00 sec) On innodb storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+-+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | idx_user_course_payment | 9 | NULL| 256519 | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+-+-+-++-+ 2 rows in set (0.00 sec) I have executed ANALYZE TABLE COMMAND on both the system (innodb and myisam) Yet there is a small difference. Highlighted in red color Is it the behavior of myisam or innodb or interal working of the storage engines. Thanks, Krishna ANALYZE TABLE: http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html What stands out to me is that the used key is different between the two explains and that innodb index is not present in the possible key column. Take a look at the key portion of http://dev.mysql.com/doc/refman/5.0/en/using-explain.html . Try FORCE INDEX hinting the query and see what if it comes back with: The query would be something like: explain select ui.user_id, ucp.user_id, ucp.payment_date from user_info ui FORCE INDEX(user_course_pay_comp1), user_course_payment ucp where ui.user_id=ucp.user_id; I am not an innodb expert (or frankly even a user) but my guess is that innodb can process the query somewhat more efficiently using a secondary index noted in the second manual entry cited above. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
Hi Vidal, all ! Vidal Garza wrote: Hi... I need test mysql connections but i have a problem... freebsd# uname -a FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan 16 04:18:52 UTC 2008 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386 freebsd# mysql --version mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2 [[...]] but it return error on the screen like and this its my PROBLEM! I would like that all connections finish ok. The errors not apear on mysql_error file. : Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) [[...]] That 61 is not for decorative purposes, it has a significance. Search your system header files for the error number 61, it should be ECONNREFUSED (Connection refused). Then, check your FreeBSD documentation for the possible reasons of this error number. This might give you a hint. On the machine I can access (FreeBSD 6.0), man connect contains this: ERRORS The connect() system call fails if: [[...]] [ECONNREFUSED] The attempt to connect was forcefully rejected. I did not check other calls whether they might return ECONNREFUSED. At the moment, I am at a loss what might cause this reply. Sadly, Stevens (Advanced Programming in the Unix Environment) does not describe it, rather refers to his Unix Network Programming (which I do not have). I propose you consult Google or some other search engine. Your attempt to change the socket owner was bound to fail, IMO, because a permission problem would have produced a different error number (typically, EACCESS). HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert select
hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert select
Hi Hiep, you can put in either xxx = NULL or you can skip it completely: insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2; Regards, /Johan Hiep Nguyen skrev: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert select
On Thu, 3 Apr 2008, Johan Höök wrote: Hi Hiep, you can put in either xxx = NULL or you can skip it completely: insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2; Regards, /Johan Hiep Nguyen skrev: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep insert into tbl_1(fld1,fld2,fld3) select NULL,fld_b, NOW() from tbl_2 where fld_a = '5' limit 1; how do i prevent the insertion when select returned empty record? thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert select
Hi Hiep, Hiep Nguyen skrev: On Thu, 3 Apr 2008, Johan Höök wrote: Hi Hiep, you can put in either xxx = NULL or you can skip it completely: insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2; Regards, /Johan Hiep Nguyen skrev: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep insert into tbl_1(fld1,fld2,fld3) select NULL,fld_b, NOW() from tbl_2 where fld_a = '5' limit 1; how do i prevent the insertion when select returned empty record? When the select returns an empty set the insert simply wont happen. /Johan thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
Please actually read my reply before asking the same question. As I stated, InnoDB outputs *estimated* row counts in EXPLAIN, whereas MyISAM outputs *accurate* row counts. -jay Krishna Chandra Prajapati wrote: Hi, On myisam storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | user_course_pay_comp1 | 30 | NULL| *256721* | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+---+-+-++-+ 2 rows in set (0.00 sec) On innodb storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+-+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | idx_user_course_payment | 9 | NULL| *256519* | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+-+-+-++-+ 2 rows in set (0.00 sec) I have executed ANALYZE TABLE COMMAND on both the system (innodb and myisam) Yet there is a small difference. Highlighted in red color Is it the behavior of myisam or innodb or interal working of the storage engines. Thanks, Krishna On Wed, Apr 2, 2008 at 9:06 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Horribly ugly stuff I know I sure as heck am not going to spend half an hour to turn those queries into something understandable, and I expect no one else will either. If you want help please remove all extraneous details (turn table and columns names in t1,t2,col1,col2, etc or descriptive names like parent, child, datetime_end) and send out something that is easy to reproduce. You get a cupcake if you include ddl that populates itself with random data. Also, using /G instead of a semi colon will make database output a heck of a lot easier to read in email form. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To replicate or not to replicate that is the question
I need to put a read-only copy of a single table on a database on another server so remote customers can have read access to it. I built a new MySQL server and placed in my DMZ. I can use SSH forwarding to access it from outside. Now I need to get a copy of the table from my internal MySQL server to this new server. I was originally planning on using replication, however the more I research it, the less I think it will do what I want. Here are my requirements: 1) Minimal changes to the internal server, can't take it down. 2) Single table for now, but may need more later. 3) New server will NEVER have write access. 4) Doesn't have to be real-time, but as close as possible would be nice. Does any one have any suggestions, or is replication the way to go? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To replicate or not to replicate that is the question
This is something I do with replication. You can replicate a list of tables, see the 'replicate_do_table' config option. Then you can euse the 'read_only' option. Replication still works but nothing else. But it would give you more options by using GRANT instead of 'read-only': CREATE USER dmz@'%' IDENTIFIED BY ...; GRANT SELECT ON *.* TO dmz@'%'; Then you can have a 'dba' user which can still work on the database: CREATE USER dba@'%' IDENTIFIED BY ...; GRANT ALL ON *.* TO dba@'%'; Ben David Ruggles wrote: I need to put a read-only copy of a single table on a database on another server so remote customers can have read access to it. I built a new MySQL server and placed in my DMZ. I can use SSH forwarding to access it from outside. Now I need to get a copy of the table from my internal MySQL server to this new server. I was originally planning on using replication, however the more I research it, the less I think it will do what I want. Here are my requirements: 1) Minimal changes to the internal server, can't take it down. 2) Single table for now, but may need more later. 3) New server will NEVER have write access. 4) Doesn't have to be real-time, but as close as possible would be nice. Does any one have any suggestions, or is replication the way to go? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spurious select ERROR 1191 when insert into ... select * is done on fulltext table
Description:
copying a table with a fulltext index via
insert into ft1 select * from ft2;
into a identical table sometimes leads to select error 1191
when concurrent select's are running.
this happens in an enviroment where the searched table is
periodically created as a temporary table and finally
copied into the searched table.
so the locking time is restricted to copying and the
searches running parallel are not locked while creating
the new search table.
the error 1191 occurs to the search selects after copying, but
only sometimes not always.
without running concurrent selects while copying,
the error seems not to happen.
the mysql database version is fresh fetched from the server:
mysql-5.0.51a-linux-i686-glibc23.tar.gz
but this error could be reproduced with v5.0.45 too.
How-To-Repeat:
script 1:
create a table with a fulltext indexed column,
fill with some data and run selects on this
table in a loop.
in this test the select result is not of interest,
only if there is a problem flagged, or a success exit code.
script t1.sh:
---
#!/bin/bash
##
## create table ft1 with one fulltext column:
##
mysql -B test EOF
drop table if exists ft1;
CREATE TABLE ft1 (
data text NOT NULL,
FULLTEXT KEY data (data)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
EOF
max=1000
##
## fill table with data:
##
i=0
while [ $i -lt $max ] ; do
i=$(($i + 1))
echo insert into ft1 values ('textstring');
done | mysql -B test
##
## loop endless selecting data.
##
## on success (regardless if there is a match) print a . on success
## or print the mysql error message.
##
while true ; do
mysql -B test -e select match (data) against ('something'), data \
from ft1 where match (data) against ('something') /dev/null
if [ $? = 0 ] ; then
echo -n .
fi
done
exit 0
---
script 2:
the second script does the copying forth and back:
- drop the temp table, create and fill it.
- lock
- empty search table and fill from temp table
- unlock
running the second script with a parameter sets the count how
many times this copying should be done.
script t2.sh:
---
#!/bin/bash
if [ $1 != ] ; then
loop=$1
else
loop=9
fi
i=0
while [ $i -lt $loop ] ; do
i=$(($i + 1))
mysql -B test EOF
drop table if exists ft2;
create table ft2 like ft1;
insert into ft2 select * from ft1;
lock table ft1 write, ft2 read;
delete from ft1;
insert into ft1 select * from ft2;
unlock tables;
select count(*) from ft1;
EOF
done
---
testing:
- run t1.sh
should start printing dots for every select:
.
[...]
- run t2.sh
t2 should print the select count(*) output for every loop run:
count(*)
1000
[...]
- as t2.sh starts, the first script starts to output errors between
the successful dots:
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
...ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching
the column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
...ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching
the column list
[...]
the spurious behavior can be observed by running the t2.sh copy script
manyally one copy after an other:
t2.sh 1
t2.sh 1
[...]
somtimes the output of the t1.sh looping script changes to only
errors, flagging a broken table:
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
[...]
and then continuing with the copies:
t2.sh 1
t2.sh 1
[...]
the output of the t1.sh looping script changes
back to dots, showing the table is usable.
Fix:
a fix is not known.
a usable workaround seems to be adding a repair table quick:
insert into ft1 select * from ft2;
+ repair table ft1 quick;
unlock tables;
Submitter-Id: [EMAIL PROTECTED]
Originator:Erik Schoenfelder
Organization:
Gaertner Datensysteme GbR, E-Mail: [EMAIL PROTECTED]
38114 Braunschweig, Hamburger Str. 273a, Germany, Gesellschafter:
Re: To replicate or not to replicate that is the question
I need to put a read-only copy of a single table on a database another server so remote customers can have read access to it. I use SQLYob Job Agent to synchronise two MySQL databases. See http://www.webyog.com/en/ for more information. I use SSH to so port forwarding from one box to the other, and then use the SQLYog Job Agent via cron every minute to sync any changes from specified tables on the source DB to the remote DB. You can do it without the SSH component if you don't care about an encrypted tunnel. You could also look into the mysqltookit (now called maatkit) at http://www.maatkit.org/ - there's a tool called mk-table-sync (see http://maatkit.sourceforge.net/doc/mk-table-sync.html for more info). This works a little differently to the SQLYob Job Agent so you might find it more suitable depending upon your application. There's a nice comparison between these two solutions at http://www.xaprb.com/blog/2007/04/05/mysql-table-sync-vs-sqlyog-job-agent/ Hope this is helpful. Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
Krishna Chandra Prajapati escribió: Just try mysqladmin ping that whether mysql is working or not Krishna On Thu, Apr 3, 2008 at 5:45 AM, Vidal Garza [EMAIL PROTECTED] wrote: Hi... I need test mysql connections but i have a problem... freebsd# uname -a FreeBSD freebsd.aduanet.net 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Wed Jan 16 04:18:52 UTC 2008 [EMAIL PROTECTED] mailto: [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386 freebsd# mysql --version mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.3 (i386) using 5.2 I read, the connect information, but i dont find the answer... I change the connection metod script. I set #!/bin/sh ... /usr/local/bin/mysql -h 127.0.0.1 -u root -pmypassword MYDB -e $query ... the -h option but i have a another message error... ... ... ERRORERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60) ERROR 2003 (HY000) 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60) ERROR: Can't connect to MySQL server on '127.0.0.1' (60) 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60) ERRORERRORERRORERROR 2003 (HY000) 2003 (HY000) 2003 (HY000) 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (60) : Can't connect to MySQL server on '127.0.0.1' (60) ... There arent message on the systems logs and mysql logs... With a shell i test mysql with ping ... [ Thu Apr 3 11:11:53 CST 2008 ] mysqld is alive [ Thu Apr 3 11:12:35 CST 2008 ] mysqld is alive ... I have a cuestion, where do you find the system header files error number (61)? -- Ing. Vidal Garza Tirado Depto. Sistemas Aduanet S.A. de C.V. Tel. (867)711-5850 ext. 4346, Fax (867)711-5855. Ave. César López de Lara No. 3603 Int. B Col Jardín. Nuevo Laredo, Tamaulipas, México. -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. For all your IT requirements visit: http://www.aduanet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Warehouse on MySQL questions
Hey folks, I'm currently deciding whether to build a decent sized (around 300-500GB, although honestly, I've got little to base that on at the moment) data warehouse in postgreSQL or MySQL. I've developed several in MS SQL and postgreSQL, but the client is comfortable with MySQL, and I'd prefer to use that as the platform since it will be less painful for them to manage when I'm gone. I'm hoping that someone with experience building a warehouse on MySQL will be able to answer two outstanding questions I have: 1) Several sources seem to suggest MyISAM is a good choice for data warehousing, but due to my lack of experience in a transaction-less world, this makes me a little nervous. How do you handle data inconsistency problems when ETL jobs fail? (For the record, I don't use a separate tool for the ETL; I usually use perl/shell scripts to interact with the file system, and pl/pgsql or transact-sql once the data is loaded into the staging database. For each file that is loaded, I'll identify steps that must be posted together, and wrap them in a transaction in the ETL job.) I can see doing something like manually cleaning out the necessary tables before you re-run, but that seems a bit messy to me. Anyone figure out a better approach? 2) Isn't the lack of bitmap indexes a problem in the warehouse? Most FKs in the fact tables will be low cardinality columns; queries that didn't use date would be very slow on large fact tables (MS SQL had this problem). Has anyone run into this with MySQL? Many thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
Hi, Vidal Garza wrote: [[...]] I have a cuestion, where do you find the system header files error number (61)? I do that brute-force: #! /bin/sh # # FGIN shell script to use fgrep on all /usr/include/h files # # $1, $2, ... options and arguments given to fgrep # # Simple example: #FGIN seteuid return the line(s) in system header files where # 'seteuid()' is defined or otherwise mentioned. # # 2004-09-13 Joerg Bruehe Initial published version find /usr/include -follow -name '*.h' -print | xargs fgrep -n $* This should work regardless of nested includes. It is meant for any system definition, not just for error numbers, that's why I do not restrict the list of file names. If you are looking for an error number (as opposed to an identifier), you may want to filter the output a bit, depending on your system's conventions (example from LinuX): The line you target for is #define ECONNREFUSED111 /* Connection refused */ but all you have is the 111 (the number). 1) grep -n in the script makes it write the line number, so you can filter for : # define (tab or blank, any number) E FGIN 111 | grep ':#define[]*E' (the square bracket contains a blank and a tab). On my system, this brings the output down from 926 lines (the 111 matches a postal code in the GPL comment !) to 9 lines. 2) If you are searching for error numbers, it is highly likely that the file name contains err: FGIN 111 | grep '^[^:]*err' (string err before the first colon). This returns 11 lines for me. 3) Combine the two, and it is only one hit (for me): FGIN 111 | grep ':#define[ ]*E' | grep '^[^:]*err' Try on your system, using 61. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recent change in behaviour when inserting into NOT NULL fields??
Hi Up until version 5.0.44 (on linux) it appeared that you could do stuff like deliberately insert a NULL into a NOT NULL varchar field and it would be silently converted to an empty string. Similarly if you didn't specify a value it appeared to use what is describe in the docs as the DEFAULT() function to enter an empty string in the column However, I just upgraded from 5.0.44 to 5.0.54 and now the behaviour has changed so that this errors noisily. The docs on SQL Modes http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html - suggests that this behaviour can be controlled, but as far as I can see I don't have the |STRICT_ALL_TABLES or ||STRICT_TRANS_TABLES options enabled anyway..? How can I return to the old behaviour (at least until I update my app)? Is this change in behaviour defined anywhere? Thanks Ed W | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Warehouse on MySQL questions
I've built several datamarts using perl and MySQL. The largest ones have been up to about 30GB, so I'm not quite on your scale. for #1, I have an etl_id in the fact table so I can track back any particular ETL job. I typically make it a dimension and include date, time, software version, etc. That doesn't help so much if you're messing up your dimension tables, but I haven't typically run into that problem based on the designs I've used. For #2, I haven't built anything big enough for it to be a concern yet.. Also, LOAD DATA INFILE is your friend :) On Thu, Apr 3, 2008 at 11:28 AM, Dre [EMAIL PROTECTED] wrote: Hey folks, I'm currently deciding whether to build a decent sized (around 300-500GB, although honestly, I've got little to base that on at the moment) data warehouse in postgreSQL or MySQL. I've developed several in MS SQL and postgreSQL, but the client is comfortable with MySQL, and I'd prefer to use that as the platform since it will be less painful for them to manage when I'm gone. I'm hoping that someone with experience building a warehouse on MySQL will be able to answer two outstanding questions I have: 1) Several sources seem to suggest MyISAM is a good choice for data warehousing, but due to my lack of experience in a transaction-less world, this makes me a little nervous. How do you handle data inconsistency problems when ETL jobs fail? (For the record, I don't use a separate tool for the ETL; I usually use perl/shell scripts to interact with the file system, and pl/pgsql or transact-sql once the data is loaded into the staging database. For each file that is loaded, I'll identify steps that must be posted together, and wrap them in a transaction in the ETL job.) I can see doing something like manually cleaning out the necessary tables before you re-run, but that seems a bit messy to me. Anyone figure out a better approach? 2) Isn't the lack of bitmap indexes a problem in the warehouse? Most FKs in the fact tables will be low cardinality columns; queries that didn't use date would be very slow on large fact tables (MS SQL had this problem). Has anyone run into this with MySQL? Many thanks in advance! -- 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: Data Warehouse on MySQL questions
On Thu, Apr 3, 2008 at 2:28 PM, Dre [EMAIL PROTECTED] wrote: 1) Several sources seem to suggest MyISAM is a good choice for data warehousing, but due to my lack of experience in a transaction-less world, this makes me a little nervous. MyISAM has the advantage of very fast loading. It's much faster to load than InnoDB. However, InnoDB had better query performance on the warehouse data I used. How do you handle data inconsistency problems when ETL jobs fail? Usually, with a warehouse system the answer is that you rebuild it. I have built an incrementally loading ETL system, but I was under the impression that is not the norm. I can see doing something like manually cleaning out the necessary tables before you re-run, but that seems a bit messy to me. Anyone figure out a better approach? I think it's pretty common to use a Y-loading approach, with one empty schema and then do an atomic RENAME at the end to swap the new tables into place. When I used MyISAM tables, I did it that way. 2) Isn't the lack of bitmap indexes a problem in the warehouse? Most FKs in the fact tables will be low cardinality columns; queries that didn't use date would be very slow on large fact tables (MS SQL had this problem). Has anyone run into this with MySQL? You can bundle up low-cardinality columns by using a junk dimension. It made a big difference for me. You make a table with all of the combinations of the low-cardinality fields and assign a key to each combination. There are some good presentation on data warehousing available from the website for the MySQL Users Conference. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert select
The beauty of this language is exactly as Johan says, you can skip the obvious, Just insert all the other non-obvious columns. In the event that you have numerous defaulted columns, though, it's best to supply a NULL so the syntax is parallel (IMO), or alternatively to name the columns. But either way, for your auto-increment column, I would suggest skipping it completely and just worry about the other columns. As Johan has suggested, you can even substitute functions' return values for what you wish to insert. You can do almost anything you want in the MySQL Restaurant. A. On Thu, Apr 3, 2008 at 10:37 AM, Johan Höök [EMAIL PROTECTED] wrote: Hi Hiep, you can put in either xxx = NULL or you can skip it completely: insert into tbl_1(fld2,fld3) select fld_b, NOW() from tbl_2; Regards, /Johan Hiep Nguyen skrev: hi all, i have a question on insert ... select statement. tbl_1(fld1,fld2,fld3, ) fld1 int primary key auto_increment not null tbl_2(fld_a,fld_b,fld_c,...) how do i construct my select statement so that fld1 is auto increment? insert into tbl_1(fld1,fld2,fld3) select xxx, fld_b, NOW() from tbl_2 where fld_a = '5'; what should 'xxx' be??? my goal is to get fld1 = auto increment fld2 = fld_b fld3 = NOW() i saw someone used '1', other used null for xxx. i'm confused. thanks. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
