Re: Queue / FIFO in MySQL?
Please check out http://q4m.31tools.com/*. *It is a message queue storage engine with sql interface. Havent used it in a production setup though we did some tests. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Reset of Status Parameters
I would also add Baron's maakit http://www.maatkit.org/ ( innotop ) for innodb details to the arsenal. On Fri, Jun 20, 2008 at 3:11 PM, Ian Simpson [EMAIL PROTECTED] wrote: I tend to use the 'mytop' program, which shows the average queries/second for the entire lifetime and for the last 5 seconds, as well as showing a bunch of other statistics and a list of running queries. It's a handy little monitoring tool. On Fri, 2008-06-20 at 12:17 +0530, Venu Madhav Padakanti wrote: I am using MySQL version 5.0.22, I am interested in knowing the current performance on the MySQL. With the status command we can get the queries per second but it will average since the beginning of time when SQL was up and running and not the current rate? Is there any way to reset that parameter so that the data can reflect current without restarting the MySQL Thanks in advance ..venu -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Very slow inserts into InnoDB tables
Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Very slow inserts into InnoDB tables
also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Very slow inserts into InnoDB tables
replication based inserts are serial whereas most of the time the inserts on masters are concurrent. this leads to the slaves falling behind. to tackle this we have used the following strategies : 1. Use raid 0 on the slaves (master users raid 10) so as to speed up writes. 2. pre fetch and cache the data that needs to be modified by the slave sql thread. 3. set innodb flush trx log commit to 2 or even 0. 4. Out of desperation sometimes disable innodb double write and also xa support. On Fri, Jun 13, 2008 at 7:33 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi guys, thanks for pitching in. The inserts are from replication; we're not using transactions on the master (yet), and I don't think there's a way of telling MySQL to batch incoming replication statements if they're not already in a transaction. Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: The working server (which in addition to replicating is also handling a bunch of read queries) Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements
Re: Circular replication
Chain replication is fine as long as reading stale data from the last slave in your chain is ok. the staleness depends on the write throughput and capacity of the intermediate slaves. But Chain replication with circular replication is a definite no no in prod since if any intermediate fails, you will not be able to restore it easily and the data goes out of sync. On Dec 5, 2007 12:31 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi All, Is circular replication or chain replication is suitable for production environment. Whether any testing has been done. If yes then, Please let me know. There is any other issue related to circular replication. Thanks -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- Thanks Alex http://alexlurthu.wordpress.com
Re: Determining Table Storage Engine Type on Crashed Table
How about looking at the .frm file of the table. On 11/28/07, Richard Edward Horner [EMAIL PROTECTED] wrote: FYI, this did not work :) Thanks though! Rich(ard) On Nov 23, 2007 3:37 AM, Paul McCullagh [EMAIL PROTECTED] wrote: Maybe this will work: SHOW CREATE TABLE table_name; On Nov 21, 2007, at 9:42 PM, Richard Edward Horner wrote: Hey everybody, Hopefully some of you are already enjoying time off. I am not...yet :) Anyway, is there a way to determine what storage engine a table is using if it's crashed? When it's fine, I can just run: mysql show table status like 'table_name'; +-++-++ ++-+--+-- +---++- +-+-+--- +--++-+ | Name| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-++-++ ++-+--+-- +---++- +-+-+--- +--++-+ | table_name | MyISAM | 10 | Fixed | 985984 | 13 |12817792 | 3659174697238527 | 34238464 | 0 | 1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21 15:28:18 | latin1_swedish_ci | NULL || | +-++-++ ++-+--+-- +---++- +-+-+--- +--++-+ 1 row in set (0.00 sec) As you can see, the second column returned is the Engine. In this case, MyISAM. Now, if I crash the table, it doesn't work: mysql show table status like 'table_name'; +-++-++-- ++-+-+-- +---++-+- ++---+--+ +- ---+ | Name| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-++-++-- ++-+-+-- +---++-+- ++---+--+ +- ---+ | table_name | NULL |NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL| NULL| NULL | NULL | NULL | NULL | Table './blah/table_name' is marked as crashed and should be repaired | +-++-++-- ++-+-+-- +---++-+- ++---+--+ +- ---+ 1 row in set (0.00 sec) Now, let's assume for a moment this were an InnoDB table. If I were to try and run repair, it would say that the storage engine does not support repair so clearly it knows what the storage engine is. How do I get it to tell me? Or I guess a broader more helpful question would be, What are all the ways to determine a table's storage engine type? Thanks, -- Richard Edward Horner Engineer / Composer / Electric Guitar Virtuoso [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Richard Edward Horner Engineer / Composer / Electric Guitar Virtuoso [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sent from Gmail for mobile | mobile.google.com Thanks Alex http://alexlurthu.wordpress.com -- MySQL General Mailing
Re: show slave staus
You can set pager command to grep out unwanted fields. On 11/28/07, bruce [EMAIL PROTECTED] wrote: hi.. simple question!! mysql show slave status returns a number of fields, with information on the status of the slave. is there a way to only return the field(s) i'm interested in... i thought that i had figured this out, but i can't recall, and it's been awhile since i've played with this! thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sent from Gmail for mobile | mobile.google.com Thanks Alex http://alexlurthu.wordpress.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Speed
The second query might be faster due to caching. On 11/26/07, Alexander Bespalov [EMAIL PROTECTED] wrote: Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct, nas, can, acctStatusType where acct.can_id = can.id and acct.acctStatusType_id = acctStatusType.id and acct.day = '2007-09-01' and acct.day = '2007-11-01' and acct.nas_id = nas.id and can.can = 10 and acctStatusType.acctStatusType = 'Stop' group by acct.nas_id ; EXPLAIN shows the following: ++-++++- ---+-+-+---++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++++- ---+-+-+---++ | 1 | SIMPLE | can| const | PRIMARY,can| can | 2 | const | 1 | Using filesort | | 1 | SIMPLE | acctStatusType | const | PRIMARY,acctStatusType | acctStatusType | 10 | const | 1 || | 1 | SIMPLE | acct | ref| index1 | index1 | 4 | const,const | 63827 | Using where| | 1 | SIMPLE | nas| eq_ref | PRIMARY| PRIMARY| 4 | GWF.acct.nas_id | 1 || ++-++++- ---+-+-+---++ I have the following table with 59742411 rows: mysql describe acct; +---+---+--+-+-- ---+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-- ---+---+ | date | datetime | | | -00-00 00:00:00 | | | user_id | int(10) unsigned | | PRI | 0 | | | nas_id| int(10) unsigned | | PRI | 0 | | | can | smallint(5) unsigned | YES | | NULL | | | can_id| smallint(5) unsigned | YES | MUL | NULL | | | acctStatusType_id | tinyint(3) unsigned | | PRI | 0 | | | acctTerminateCause_id | tinyint(3) unsigned | | | 0 | | | sweetEventContext_id | tinyint(3) unsigned | | | 0 | | | acctSessionId | varchar(8)| | PRI | | | | acctDelayTime | mediumint(8) unsigned | | | 0 | | | acctSessionTime | mediumint(8) unsigned | YES | | NULL | | | acctInputOctets | bigint(20) unsigned | YES | | NULL | | | acctOutputOctets | bigint(20) unsigned | YES | | NULL | | | wisprBwMaxUp | int(10) unsigned | YES | | NULL | | | wisprBwMaxDown| int(10) unsigned | YES | | NULL | | | day | date | | PRI | -00-00 | | | acctMultiSessionId| varchar(27) | YES | | NULL | | +---+---+--+-+-- ---+---+ mysql show index from acct; +---++--+--+---+ ---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+---+ ---+-+--++--++-+ | acct | 0 | PRIMARY |1 | user_id | A | 53341 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |2 | nas_id| A | 277871 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |3 | acctStatusType_id | A | 558340 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |4 | acctSessionId | A |59742411 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |5 | day | A |59742411 | NULL | NULL | | BTREE | | | acct | 1 | index1 |1 | can_id| A | 467 | NULL | NULL | YES | BTREE | | | acct | 1 | index1 |
Re: Database page corruption on disk occurring during mysqldump on a fresh database
The checksum errors might be due to various reasons. We had similar issue where we restored the database multiple times, replaced the ram sticks nothing helped. Finally we drilled down the issue to the chassis. Recommend testing the restore on a different machine to rule out any hardware issue. -- Thanks Alex http://alexlurthu.wordpress.com On 9/5/07, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi This might be happening due to two reasons; 1 The system date might not be correct. 2. Some things wrong with log postion (Incorrect log position) Regards, Krishna Chandra Prajapati On 8/31/07, Maurice Volaski [EMAIL PROTECTED] wrote: A 64-bit Gentoo Linux box had just been upgraded from MySQL 4.1 to 5.0.44 fresh (by dumping in 4.1 and restoring in 5.0.44) and almost immediately after that, during which time the database was not used, a crash occurred during a scripted mysqldump. So I restored and days later, it happened again. The crash details seem to be trying to suggest some other aspect of the operating system, even the memory or disk is flipping a bit. Or could I be running into a bug in this version of MySQL? Here's the output of the crash --- InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 533. InnoDB: You may have to recover from a backup. 070827 3:10:04 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex [dump itself deleted for brevity] ;InnoDB: End of page dump 070827 3:10:04 InnoDB: Page checksum 646563254, prior-to-4.0.14-form checksum 2415947328 InnoDB: stored checksum 4187530870, prior-to-4.0.14-form stored checksum 2415947328 InnoDB: Page lsn 0 4409041, low 4 bytes of lsn at page end 4409041 InnoDB: Page number (if stored to page already) 533, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 35 InnoDB: (index PRIMARY of table elegance/image) InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 533. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. -- Maurice Volaski, [EMAIL PROTECTED] Computing Support, Rose F. Kennedy Center Albert Einstein College of Medicine of Yeshiva University -- 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, Ed Ventures e-Learning Pvt. Ltd, 201,202, Ashoka Bhoopal Chambers, S P Road, Secunderabad 53. Ph. No. - 040-39188771 Url: www.ed-ventures-online.com
Re: recovering from 'disk full' mysql error
stop slave;
change master to master_log_file='Relay_Master_Log_File' ,
master_log_pos=Exec_master_log_pos;
#Get the values for the above command from your show slave status output.
start slave;
The above process should fix your problem.
On 9/4/07, Russell E Glaue [EMAIL PROTECTED] wrote:
I had a disk full error on the master MySQL (4.1.22), which was
replicating to a
slave (4.1.22).
My question is, how do I recover a slave replica when the master had a
disk full
error, space was freed, but the 'disk full' issue left a corrupted master
binlog?
1) Do I have to reinitialize the slave from scratch,
2) or can I use some CLI tool to repair the damaged binlog (see below)
3) or can I skip to the next binlog
This is assuming I performed a stop and start of the master mysql, then
'purge
binary logs' from master.
And would the resolution be the same if I did not start and stop the
master mysql?
According to bug 9949 (http://bugs.mysql.com/bug.php?id=9949) this was
addressed
in 4.1.9.
I am running 4.1.22, and have experienced this issue as outlined.
I am not finding a good sum of reasoning and process to proceed with
resolving
this type of predicament. Can anyone clue me in to resolving this?
I purged enough binlogs to free up disk space on master, and then started
the
slave replica process on the slave.
I get this famous error:
-
070904 12:46:26 [Note] Slave I/O thread: connected to master
'[EMAIL PROTECTED]:3306', replication started in log '
master1-bin.54' at
position 138164107
070904 12:46:26 [ERROR] Error reading packet from server: binlog truncated
in
the middle of event (server_errno=1236)
070904 12:46:26 [ERROR] Got fatal error 1236: 'binlog truncated in the
middle of
event' from master when reading data from binary log
-
I do a binlog dump on 'master1-bin.54', and I get the below error
('Error in
log format or read error') at the end of the output:
-
#070904 11:46:22 server id 2 end_log_pos 137541177 Query
thread_id=48871
exec_time=0 error_code=0
#070904 11:46:22 server id 2 end_log_pos 137655584 Query
thread_id=48871
exec_time=0 error_code=0
#070904 11:46:22 server id 2 end_log_pos 137655719 Query
thread_id=48773
exec_time=0 error_code=0
#070904 11:46:22 server id 2 end_log_pos 137770204 Query
thread_id=48773
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 137770339 Query
thread_id=48870
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 137962851 Query
thread_id=48870
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 137962986 Query
thread_id=48871
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 138152129 Query
thread_id=48871
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 138152264 Query
thread_id=48773
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 138163972 Query
thread_id=48773
exec_time=0 error_code=0
#070904 11:46:24 server id 2 end_log_pos 138164107 Query
thread_id=48870
exec_time=0 error_code=0
ERROR: Error in Log_event::read_log_event(): 'read error', data_len:
81904,
event_type: 2
Could not read entry at offset 138164107:Error in log format or read error
-
-RG
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Thanks
Alex
http://alexlurthu.wordpress.com
Re: Delete query question
reply inline On 9/5/07, Olaf Stein [EMAIL PROTECTED] wrote: delete from geno_260k where ident=(select ident from geno_260k where a1=0 group by ident having count(a1)25); When a sub query returns more than one row in a where clause, then = should be replaced by the in . -- Thanks Alex http://alexlurthu.wordpress.com
Re: Simple questio SQL
select * from Products order by Visited desc limit 10; should give you the top 10 products. On 9/5/07, Tomas Abad [EMAIL PROTECTED] wrote: HI All, I have a Table and want to know the most visited products. Products - Id - Name - Visited Visited is numeric. -- Thanks Alex http://alexlurthu.wordpress.com
Re: innodb engine status
To have a good understanding on the show innodb status output checkout http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ One area you can look at is the LATEST DETECTED DEADLOCK. But in most cases have found calculations on the status variables more helpful. -- Alex http://alexlurthu.wordpress.com On 8/29/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Friends, When i do SHOW INNODB STATUS\G. It gives me details of transaction happening on INNODB engine. Please let me know what all information i should consider from this output to ensure if everything is fine or there are issue which i should address. I am using mysql version 5.0.41-community-log regards anandkl
Re: SCRIPT OR TOOL TO GIVE CHANGES IN DATABASES
If you want to track the schema level changes, updates, inserts etc you always have the binlogs/update logs. If you want to track select queries also you have the general query log. Check out http://dev.mysql.com/doc/refman/4.1/en/log-files.html for the different logs available. Of course be wary of the performance implications. -- Alex http://alexlurthu.wordpress.com/ On 8/29/07, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi All, Is there is any script or tool that generate the report and send an email for changes done in the databases; There is any otherway (manual) So that i can look that what changes has been done in the database today. Regards, Krishna
Re: thread_concurrency in linux
I am not sure whether you are talking about innodb_thread_concurrency. If so please check out http://www.mysqlperformanceblog.com/2006/06/05/innodb-thread-concurrency for more details. Innodb_thread_concurrency works on linux. Thanks Alex On 8/29/07, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, Does anyone know if thread_concurrency works in linux or is it just limited to Solaris and Windows? I know the general rule is number of CPU's*2 but will this actually have any effect with Linux's threading model? Thanks for any help :) Andrew Mysql, query This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu
Re: [MySQL] Fastest way to Search from a Table
On 8/7/07, Tharabar [EMAIL PROTECTED] wrote: SELECT `field1`,`field2`,`field3` FROM 'table' WHERE `field5` LIKE '%keyword%1' AND `field5` LIKE '%keyword2%' The most trivial way to speedup a query is to add an index on the columns that are being used in the where clause. But doing 'LIKE' operation with the pattern beginning with '%' it renders the index use less. If you use MyISAM you can always use full text search on a text field for such use cases. -- Thanks Alex http://alexlurthu.wordpress.com
Re: MySQL database move
Hi Ace, If you cant affort downtime and if you are using innodb try removing auto extend on the current data file and create a datafile in a different partition and put autoextend on the same. If you are using MyISAM , you can move few tables to different disk use symlinks. -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu On 7/9/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Ace, Can't you zip or move old bin-logs to a different disk and release some free space on the current drive. regards anandkl On 7/9/07, Ace [EMAIL PROTECTED] wrote: Hi, We have crisis. Disk with MySQL database is full. Now we want to move database to another disk. How can we do it? -- Thanks, Rajan
Re: bin-log position.
Anand, If the dump was taken from the slave then the binlog positions that were dumped out are that of the slave. Now you might do 2 things : 1. Setup a slave of your master To do this use the binlog positions that you obtained using the command show slave status on your slave. The master log file will be mysql-bin.29 . But your show slave output status doesnt show the exe_master_log position. You need to use that position for setting up a new slave of your master. 2. Setup a slave of your slave To do this use the binglog positions in your mysqldump output. P.S: Make sure you setup the hostnames in the change master command appropriately. -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu
Re: bin-log position.
You will be taking a chance if you use Read_Master_Log_Pos: 106683511. It is not guaranteed that whatever is read from master is definitely applied. The apply of the read sqls might have stopped somewhere and in that case Read_Master_Log_Pos and exec_master_log_pos might be different. -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu On 7/9/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Alex, Thanks a lot, I missed on taking the exe_master_log position value. If i start from Read_Master_Log_Pos: 106683511, will it cause any harm. regards anandkl On 7/9/07, Alex Arul Lurthu [EMAIL PROTECTED] wrote: Anand, If the dump was taken from the slave then the binlog positions that were dumped out are that of the slave. Now you might do 2 things : 1. Setup a slave of your master To do this use the binlog positions that you obtained using the command show slave status on your slave. The master log file will be mysql-bin.29 . But your show slave output status doesnt show the exe_master_log position. You need to use that position for setting up a new slave of your master. 2. Setup a slave of your slave To do this use the binglog positions in your mysqldump output. P.S: Make sure you setup the hostnames in the change master command appropriately. -- Thanks Alex http://blog.360.yahoo.com/alex.lurthu
Re: Performance problem MySQL 4.0.20
On 7/6/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: How does the cpu power influence the speed of a query? Sort is a cpu intensive process. *Check if you are suffering from locks on the tables you look up.* Also, was the table in the system where the queries are running faster rebuilt recently. You can try rebuilding the tables. How is the overrall system load on both machines. Is one overloaded than the other ? The machine which takes longer to excute the query might be busy serving some other process other than MySQL. Thanks Alex
Re: bin-log position.
On 7/6/07, Ananda Kumar [EMAIL PROTECTED] wrote: -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.03', MASTER_LOG_POS=311; As see in the mysqldump is saying to start recovery from bin-log file ' mysql-bin.03' and position=MASTER_LOG_POS=311, You need to use the positions specified in the mysqldump since when u restore using the data, you will get data only till the snapshot specified in the dump. Thanks Alex
Re: Performance problem MySQL 4.0.20
On 7/6/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: How does the cpu power influence the speed of a query? Sort is a cpu intensive process. *Check if you are suffering from locks on the tables you look up.* Also, was the table in the system where the queries are running faster rebuilt recently. You can try rebuilding the tables. How is the overrall system load on both machines. Is one overloaded than the other ? The machine which takes longer to excute the query might be busy serving some other process other than MySQL. Thanks Alex
Re: mysqldump on slave.
You havent mentioned which version of mysql your are on. If on an older version i would recommend the option --opt. Also if you are running a dump using single-transaction option on a loaded system with writes, things might slow down significantly due to different versions of rows which the server needs to maintain due to the dump. Thanks Alex On 7/6/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I am planning to take mysqldump on slave running innodb engine. I going to use the below command /usr/bin/mysqldump --user=root --password= --flush-logs --all-databases --master-data=2 --single-transaction alldbdump.sql Before doing the mysqldump, should i stop the slave or the above command is enough, please let know, also please let me know if any other option i can use to speed up mysqldump. regards anandkl
Re: Cannot Connect: Can't create a new thread (errno 11)
Chris, Did you check if there are any user level MAX_USER_CONNECTIONS restriction set while granting access ? Thanks Alex On 7/4/07, Chris Faust [EMAIL PROTECTED] wrote: Folks, I'm wondering is someone could help us in trying to resolve the above error, we have read through what threads we could find and tried all the suggestions, but have had no luck. There are no memory problems, in fact when this happens if there is still a localhost client connection to the server you can do whatever you want, everything is running normal. It just refuses all new connections. When it happens there is plenty of memory on the machine and no load at all (everything is running just as it should). Once things fall below 90, everything comes back. The problem happens when the process list hits the 91st process every time. 90 processes, no problems, the 91st process/connection gets the above error and any after that simple gets Can't connect to MySQL server. We've tried all the obvious things, messing with the number of connections, open files etc. Nothing seems to work. We are running MySQL 5 on Centos 5 (flavor of RedHat). The machine is dedicated to MySQL. Below is a link to a text file that shows the OS limits for the mysql account on the machine and the output from show variables (including it made the message to large for the list). http://208.3.90.212/wtresults.txt If anyone has any suggestions, it would be appreciated. Thanks!
Re: Performance problem MySQL 4.0.20
Hi, Run explain plan on both the machines and post the same. ~Alex On 7/4/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello Ananda, yes, the testmachine has the same data. Regards, Spiker -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Ratheesh, If you still want to place you blob data in the database, seperate out the blob storage to a seperate table. This will help you alleviate few of your performance and maintenance problems. ~Alex On 6/22/07, Steve Edberg [EMAIL PROTECTED] wrote: At 12:11 PM +0530 6/22/07, Ratheesh K J wrote: Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance Yes, storing files - especially non-textual files - in the file system instead of the database is generally considered the best practice. At one point I had created a document management system that stored everything in the database as you are doing; my rationale was that it allowed me to manage permissions using the existing database permissions, and to back up the whole database using mysqldump, vs mysqldump + doing a tar of the files. However, I abandoned this approach for the following reasons: (1) Storing non-plaintext items (eg; pictures) in the database makes it bigger and slower without added value - you can't (at least not yet, or in the foreseeable future) do a meaningful search on a blob. (2) It becomes more difficult to split storage out onto multiple filesystems; eg, leaving the database files in /var/database, putting the documents themselves into /home/docmanager, etc. (3) It makes queries on the commandline unwieldy; if you have a blob field, doing a select * to check a record's contents can dump a lot of garbage on the screen. (4) It can make doing incremental backups more difficult; if the documents themselves are relatively static, but the document metadata stored in the database is very dynamic, it becomes simple to do a compact daily database dump + a weekly document directory backup (for example) if the files are not in the database. What I do is create a unique SHA1 hash when a file is uploaded (eg; sha1(rand()). The original filename and the 40-character hash are stored in the database, and the document is stored in the filesystem using the hash as the filename. I can optionally compress and encrypt the document as well, storing the encryption key in the database. This gives (for me) adequate document security. An additional advantage is that you can take advantage of the filesystem tree if you have a large number of documents. For example, if a document hash is 'f0118e9bd2c4fb29c64ee03abce698b8', you can store the file in the directory tree f0/11/8e/f0118e9bd2c4fb29c64ee03abce698b8 (extending to as many levels as you feel necessary). By keeping the number of files per directory fairly small, file retrieval becomes relatively fast. As the hashes approximate a random distribution, you should always have a close-to-balanced tree. Lastly, I store a hash of the document itself in the database as well. This allows me to detect if duplicate files are uploaded, and to determine if a previously-uploaded file has been corrupted in some way. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication
check if you are seeing any access denied errors in the mysql error log. ~Alex On 5/14/07, richard [EMAIL PROTECTED] wrote: Ian P. Christian wrote: richard wrote: as far as I can see, these commands select which db's to replicate on the slave that currently exist on the master server. What i am asking is, if I set a server up as a slave, can I have other databases on the slave that are not part of the replication system? Yes - I've been doing this for some time, works fine. mmm, ok. I've set replication up and have show master and show slave output displaying the following. Any updates I make to the master are not transferred to the slave. The log position increases on the master but doesn't change on the slave. Any ideas? cheers Rich mysql show master status \G *** 1. row *** File: bla-bin.004 Position: 681 Binlog_do_db: foo_db Binlog_ignore_db: 1 row in set (0.00 sec) mysql show slave status \G *** 1. row *** Slave_IO_State: Checking master version Master_Host: 192.168.1.110 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bla-bin.004 Read_Master_Log_Pos: 512 Relay_Log_File: db_box-relay-bin.01 Relay_Log_Pos: 4 Relay_Master_Log_File: bla-bin.004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: foo_db Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 512 Relay_Log_Space: 4 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Redo logs take to much disk space
the ask lies in expire_logs_days. If you set this to optimal number of days, logs older than the configured days will get purged. ~Alex On 5/15/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: One question about this, is it safe to turn of log_bin? i think, you can. the log is necessary for data replication and sometimes for data recovery. you can read about it here: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html Or can you tweak it somehow so that it won't' take some much disk space? you can bzip old logs if you need them but don't want them to take so much space :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup solution.
On 5/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I have take a mysqldump of my entire database, is it possible to restore just one table from this mysqldump. Yes thats possible. cat your-dump-filename | grep tablename u want to restore mysql -u user -ppassword should do it. Also, i have taken binary backup of my database, and one of the datafile is corrupt, is it possible to restore just this file from backup and start the mysqldb. Mysql database is running in innodb engine. If all files are from the same cold binary backup, then you will be able to do a restore. Else it will be highly unlikely. ~Alex
Re: Memory Problems
On 5/15/07, Christoph Klünter [EMAIL PROTECTED] wrote: I have set the sort_buffer_size to 1G but even this doesn't help. Any hints ? Should we try a 64Bit-OS ? setting sort_buffer_size to 1GB is not recommended. it is a thread specific configuration parameter which means each thread will be eligible to get a sort_buffer_size of 1GB. On a 32 bit system, you can push to a approx 3.2 GB usage for the database by doing kernel level hacks mentioned at http://www.puschitz.com/TuningLinuxForOracle.shtml#GrowingTheOracleSGATo2.7GBInx86RHEL2.1WithoutVLMor by using VLM also. But if you are using a mysql 32bit build, then you will not be able to use more than 4GB theoretically. Using 64bit OS and 64bit build of mysql will enable you to use memory greater than 4GB effectively. ~Alex
Re: backup solution.
On 5/15/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Alex, Thanks for the info, For the second question, do you mean i should restore the entire backup or just that one file from my backup. All the files should be from the same backup. AFAIK, MySQL doesnt have an option to recover only one datafile. Hence restore the entire backup. ~Alex
Re: Memory Problems
On 5/15/07, Micah Stevens [EMAIL PROTECTED] wrote: I think you may be able to get around this by using multiple key buffers? (MySQL 4.1 or later) key buffers caches only index data and they dont help with sorting like sort_buffer. they dont impact innodb engine. even while using multiple key buffers, the memory allocated will belong to the same process and the limitation is at the process level. ~Alex
Re: replication
techincally yes you can have. replication is all about the queries that are getting executed on the master will be executed on the slave. So , if the database that exists on the master exists on the slave , then you should have no problems. Thanks Alex On 5/14/07, richard [EMAIL PROTECTED] wrote: as far as I can see, these commands select which db's to replicate on the slave that currently exist on the master server. What i am asking is, if I set a server up as a slave, can I have other databases on the slave that are not part of the replication system? MySQL 中文网 wrote: of course setup by the option replication-do-db or replication-ignore-db :) richard wrote: Hi, I have a server (master version 4.0.1) that has a database that I want to replicate on another server (slave version 4.1.20). The slave already has another db on it. Is is possible to have a mixture of replicated and non replicated dbs running on a slave? cheers Rich -- ** MySQL 中文网 叶金荣 MySQL独立咨询师、系统架构师、培训师 业务: LAMP架构设计、解决方案、负载均衡，MySQL 技术咨询、培训、支持 手机：13466608698 [EMAIL PROTECTED] (或 [EMAIL PROTECTED]) 网址：http://imysql.cn ** http://imysql.cn 为用户提供一个专业的、自由的MySQL中文交流社区，欢迎来访 给你的祝福,要让你招架不住! 2007/05/14 17:50:01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump Files
On a different note just running a slave will not help when there is some accidental deletion of data or tables or databases. Hence have a slave as Mikhail mentioned and also run regular backups on the slave. Preferably a tar of your mysql server after shutting it down, also ensure you have log-bin and log-slave-updates enabled on your slave. Push the tar to more than one location and hosts so that you can survive disk corruptions. This way you can ensure you can recover from most possible database corruption scenarios. Thanks Alex On 1/30/07, Mikhail Berman [EMAIL PROTECTED] wrote: Hi David, Is the space on hard-drive is major concern of yours or abilities to recover from crash is? Backups are usually taking to be able to recover from a crash. Which in its turn means if there is a way to recover faster it is better. Having slave that is constantly updated gives you very quick way of recovering if master goes down. Just point you PHP scripts to slave and be happy. If you need additional back up, do them from full slave. Stop it for a while, do backups and then restart slave again. For huge backups in our office we use old, and I mean old, Dells with huge 500GB drives running one of *Nix's Regards, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 29, 2007 3:27 PM To: Mikhail Berman Cc: mysql Subject: RE: Mysqldump Files Hi Mikhail, I don't think that would save much space, in terms of file size. The tables that are actively getting inserts are large and growing larger (~750,000 records), and those that have no activity are either currently empty or have less than a hundred records in them. So just dumping the active tables will comprise I'd guess 99% or more of the database size. David -- Hi David, Let me point you in a bit different direction. You are already running replication as it seems from your E-mail So, why not just run chained replication from second to the third server and use replicate-do-table = [table_name] in my.cnf of the third server to limit selection of tables to be used by web server. Or do full replication to another server from the first one for full backup? Regards, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 29, 2007 2:33 PM To: mysql Subject: Mysqldump Files Howdy Guys and Gals, We are acquiring data on background radiation in a master-slave server environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 records per day. The data are insert-only into about 25 of 31 tables in the database -- no updates are ever applied to the data. Information from the database is used via select statements for graphical display and report generation amongst other uses. A PHP backup script using mysqldump runs as a cron job each night from a third server which also functions as an intranet webserver. After 1 1/2 years of operation, the mysqldump file of the entire database is roughly 760 MB, and this takes under 2 minutes to create. Once bzipped and tarred, the entire file is 31.7 MB in size, and this part of the backup process now takes 46-47 minutes. The rate of acquisition of data will be fairly constant, and up to 3 years of data will be kept on the live master-slave, so simply doubling all these values seems a realistic expectation for a full backup of the database after 3 years. Data older than 3 years would be deleted from the master-slave system. How long it would be reasonable to keep doing a full dump of the database versus using mysqldump with a where clause, i.e., doing a daily incremental backup, say of the last 24 hours. Also, what are the key mysqldump and/or server variables to pay attention to in order to process these large, maybe multi-gigabyte dump files? Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up a join COUNT
and also an index on users.entity_id (will help the join) should solve your problem. Thanks Alex On 1/24/07, Brent Baisley [EMAIL PROTECTED] wrote: You should create indexes on the fields you search on most. In this case, you are searching on the user_type field, so create an index on that field. Otherwise you need to scan the entire table to find out which users are of the type you are searching for. - Original Message - From: James Tu [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 12:04 PM Subject: speeding up a join COUNT I'm performance testing my 'users' table. It currently has roughly 1M user records. The 'geo_entities' table has ~ 250 records. Here's my query. SELECT users.entity_id, geo_entities.entity_name, geo_entities.short_code, COUNT( users.entity_id) FROM users, geo_entities WHERE users.user_type = 'user' AND users.entity_id = geo_entities.id GROUP BY entity_id LIMIT 0 , 30 It took 51 seconds to execute. Both tables only have an index on their unique record id. Is there a way to speed up this up? -James -- 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: Backup: Copy MYSQL Directory or use MysqlDump
Hi, MySQL Dump is logical. Hence it is mostly slower than locking the MyISAM tables and copying them or shutting down the server and taring the entire MySQL directory if you are using innodb. If you are using innodb tables only you can run mysqldump with --single-transaction option to take a consistent backup even when the server is running. Hence both has advantages and disadvantages. It will more of depend on your use case. Thanks Alex On 1/23/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi, Hope I have faced this: If we copy the files with 'cp' command, the permissions will not be retained. You have to assign it on restoring. But in the mysqldump utility, everything are retained as it is. Hope, mysqldump utility provides more options related to db than that of 'cp' command. Thanks Visolve DB Team. - Original Message - From: altendew [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 6:10 AM Subject: Backup: Copy MYSQL Directory or use MysqlDump I want to create a backup for my MySQL database every single night. I am currently running on a linux box. I have a backup drive located as /backup Would it be more effecient to use mysqldump tool, or use the unix command dump or cp Andrew -- View this message in context: http://www.nabble.com/Backup%3A-Copy-MYSQL-Directory-or-use-MysqlDump-tf3061490.html#a8513292 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - *From:* Alex Arul [EMAIL PROTECTED] *To:* Ratheesh K J [EMAIL PROTECTED] *Sent:* Tuesday, January 23, 2007 11:57 AM *Subject:* Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 but your thread cache has only 8. So please do bump it up. Also generate explain plan on both versions of mysql and provide create table statement of the tables and the query. FYI, index_merge_optimization is used when more than one index can be used for execution. Thanks Alex On 1/23/07, Ratheesh K J [EMAIL PROTECTED] wrote: Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J
Re: Why would a SELECT COUNT(*)... WHERE... take way way longer than SELECT COUNT(primary_key)... WHERE... ?
count(*) is slow in innodb due to Multi Versioning. Which table type are you using ? Thanx Alex On 6/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi everybody, MySQL 5.0.21 running on RedHat EL4, 2GHz CPU, 2,5GB RAM, RAID5/128MB RAM. At one point I had to issue the following query on a 1.8GB 42mil records table: SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ; As it took forever to complete (I mean more than 20 minutes) I've stopped the client and attempted to work around. The table looks like this: EXPLAIN geoRecord; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | georecordkey| int(11) | NO | PRI | NULL| auto_increment | | geobiorecordkey | int(11) | NO | MUL | 0 || | geolocationtext | varchar(85) | YES | MUL | NULL|| | geoprecision| int(11) | YES | MUL | NULL|| | geolatitude | float | YES | MUL | NULL|| | geolongitude| float | YES | MUL | NULL|| +-+-+--+-+-++ The indexes on it look like this: SHOW INDEX FROM geoRecord ; +---++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+-+---+-+--++--++-+ | geoRecord | 0 | PRIMARY |1 | georecordkey| A |47808182 | NULL | NULL | | BTREE | | | geoRecord | 1 | geobiorecordkey |1 | geobiorecordkey | A |47808182 | NULL | NULL | | BTREE | | | geoRecord | 1 | geolocationtext |1 | geolocationtext | A |11952045 | NULL | NULL | YES | BTREE | | | geoRecord | 1 | geoprecision|1 | geoprecision| A |23904091 | NULL | NULL | YES | BTREE | | | geoRecord | 1 | geolatitude |1 | geolatitude | A | 5976022 | NULL | NULL | YES | BTREE | | | geoRecord | 1 | geolongitude|1 | geolongitude| A | 5976022 | NULL | NULL | YES | BTREE | | +---++-+--+-+---+-+--++--++-+ Also: EXPLAIN SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ; ++-+---+---+---+-+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+-+-+--+--+--+ | 1 | SIMPLE | geoRecord | range | geolatitude | geolatitude | 5 | NULL | 44891097 | Using where; Using index | ++-+---+---+---+-+-+--+--+--+ I've run a CHECK TABLE on it, it came OK too. I've run ANALYZE TABLE geoRecord already. Attempting to solve the problem I've come up with this: SELECT COUNT(georecordkey) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ; This query takes 1 min and 20 seconds and gives the right answer (still way too long IMO) but then why on earth is the COUNT(*) behaving so bad? If there's a mistake it's got to be so obvious that it eludes me. :-| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing mysql client/server interactions
Hi All, Our current setup has the mysql server and the client app run on the same host. We are using mysql++ (ports/mysql++-1.7.9) for the database client api. The application returns huge data sets in the order of 50K. When we do a show processlist most of the threads spend their time in the state sending data. With the current setup data seems to go up and down the network stack for sending data to a client on the same host. Can this be short circuited somehow for better performance. Thanx Alex
Re: Help with subqueries...
On 4/28/06, Daevid Vincent [EMAIL PROTECTED] wrote: vmware reviewit # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 Given two tables: CREATE TABLE `logs` ( `id` bigint(20) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `body` text, PRIMARY KEY (`id`), ) ENGINE=InnoDB; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(30) NOT NULL, `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, `login_date` datetime default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), ) ENGINE=InnoDB; I want a query that will show me the username and the single most current log body and date for every user. I think I need to use a sub query, but I can't even get the very basic example one to work... http://dev.mysql.com/doc/refman/5.0/en/subqueries.html mysql SELECT * FROM logs WHERE user_id = (SELECT id FROM users); ERROR 1242 (21000): Subquery returns more than 1 row try SELECT * FROM logs WHERE user_id in (SELECT id FROM users); mysql SELECT * FROM logs WHERE users.id = (SELECT id FROM users); ERROR 1054 (42S22): Unknown column 'users.id' in 'where clause' the table logs has only the column user_id . mysql SELECT logs.*, users.username - FROM logs JOIN users ON users.id = logs.id - WHERE user_id = (SELECT id FROM users ORDER BY username); ERROR 1242 (21000): Subquery returns more than 1 row again replace = by in. Hope this helps. Thanx Alex
Re: Import .frm .myd .myi to Mysql
HI, Take look at your my.cnf and obtain the location of your mysql data directory. Login into mysql create the database into which you want to import the tables. What this effectively does is create a directory under your mysql data directory. Now copy the .myd, .myi and .frm files to the directory. I am not sure whether you need to restart the server. Try accessing the tables without restart, if not successful restart the server :) eg. if the data directory is /usr/var/mysql If you created a database called xyz then you have to copy the .myd, .myi and .frm files to the directory /usr/var/mysql/xyz Hope this helps. Thanx Alex On 4/18/06, hicham [EMAIL PROTECTED] wrote: On 4/18/06, Dominik Klein [EMAIL PROTECTED] wrote: hicham schrieb: Hello I'm new bie user of mysql, I need to create a database and import some frm .myd .myi files to that database , also if you can point me to some easy to start tutorial for how to create user account in mysql , create a database , actually, I have a php / mysql application with a database which comes in a bench of files with .frm , .myd and .myi , what are theses files ? and I don't know how to get these files imported to tha mysql db . Thanks hicham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Problem
Hi Just try this select distinct(a.id) from test a , test b where a.code = 23 and b.code = 45 and a.id = b.id Regards, -Arul - Original Message - From: Robert Gehrig [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 10:52 PM Subject: Select Problem Hi all I have a detail table that has multiple records associated with an ID number Both fields are integers E.G. Id Code 4 23 4 27 34 23 34 45 34 28 What I need to find is the Id where the code is 23 and 45 for the same Id (the result in this case would be 34) How do I do this with a SQL query Thanks Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating a DataSource and Connection in Tomcat for MYsql Database
Hi All I had created a DataSource in Tomcat Admin Console. But i am not getting a connection ds.getConnection() results in error. The error is java.sql.SQLException : cannot load JDBC Driver class null But while creating the datasource i had given the driverclassname as org.gjt.mm.mysql.Driver What could be going wrong Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Boolean Data Type in MySQL
Hi All Does MySQl Support Boolean Datatypes -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
offtopic - Information Tech in Brazil
Hi Guys Sorry this is quite off topic. Any idea hows IT doing in Brazil... -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT SELECT and VALUES
Hi SImon
Try this
INSERT INTO
table1
SELECT
12 as fixedvalue1 , 20 as fixedvalue2 ,table2.A , table2.B
FROM
table2
WHERE
table2.id = 10
First the SELECT Query is executed from the table 2 which has id = 10
The Result would be something like this
fixedvalue1fixedvalue2table2.A table2.B
12 20 'A''B'
12 20 'AA''BB'
Then these values are inserted in table 1
- Original Message -
From: Simon Green [EMAIL PROTECTED]
To: 'Mysql (E-mail)' [EMAIL PROTECTED]
Sent: Thursday, July 18, 2002 3:51 PM
Subject: INSERT SELECT and VALUES
Hi All
I am trying to copy data from one table to another using in MySQL
INSERT INTO table1
SELECT table2
statment but I would like to add some fixed values to this and so some
thing like.
INSERT INTO table1
SELECT table2
VALUES ('A','B');
But I can not find if this can be done or how
Thanks
Simon
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help me with this query
Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help me with this query
Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql 3.23.51 on Solaris
Hi All I had installed MYSQL 3.23.51 on Solaris Sparc 2.8 The Login name i used to loginto Solaris is MySQL.I didnt use the root account to install MySQL When i try to connect to that MySQL Database from a Windows Machine using MySql Front Client , it says that Connection Failed 2003 - Cannot Connect to MySQL server on 10.1.1.95 (10061) What could be the problem -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help me with this query
Hi I Dont think this will work coz I need the company which has serviceid = 1 AND serviceid = 2 and serviceid = 3 So let's take this Query , Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 If i have another company say companyid = 5 ,who has serviceid = 1 AND serviceid = 6 AND serviceid = 7 , even this company will be retrieved which will be against the search criteria. Also another company whose companyid = 7 has serviceid = 1 AND serviceid = 2 AND serviceid = 3 AND serviceid = 5 .This company will not be retrieved since the count is not 3.But by search criteria , this company should be retrieved. So how do we handle these situations -Arul - Original Message - From: Francisco Reinaldo [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Cc: Arul [EMAIL PROTECTED] Sent: Wednesday, July 17, 2002 3:27 AM Subject: Re: help me with this query Hi, Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 Bye and Good Luck. --- Arul [EMAIL PROTECTED] wrote: Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Optimization
Hi All The Query below took around 175 Secs to return 22 Rows..Any way this query can be optimized SELECT Distinct(C.Company_Name) ,U.UserID , IF(U.CompanyID IS NULL , NULL ,C.Company_Name) as Company_Name, U.COMPANYID, U.PASSWORD, U.INDIVIDUAL_COMPANY_NAME, U.USER_NAME,U.FIRST_NAME, U.LAST_NAME, U.MIDDLE_NAME, U.EMAIL,U.PROMOTION_CODE,U.TITLE, U.DESIGNATION, U.USER_STATUS, U.USER_APP_STATUS, U.PHONE,U.ADDRESS, U.CREATED_ON,U.FAX, U.ZIP, U.DEPARTMENT, U.MODIFIED_ON, U.CITY,U.STATE, U.INDUSTRYID, U.COUNTRYID, U.CERTIFICATIONID,U.TECHNOLOGY_AREAID, U.HORIZONTAL_STANDARDS_EXP, U.ROLEID,U.ROLE_OTHER, U.YEARS_OF_EXP, U.OTHER_CERTIFICATION2, U.PROF_MEMBERSHIP1, U.OTHER_CERTIFICATION3, U.PROF_MEMBERSHIP2,U.OTHER_CERTIFICATION1, U.HOURLY_RATE, U.PROF_MEMBERSHIP3,U.WORK_PREFERENCE, U.OTHER_CERTIFICATION4, U.ADDITIONAL_COMMENTS,U.WELCOME_MESSAGE, I.Industry_Name,T.Technology_Area, R.Role FROM User_Type_Details UTD, User_Type_Details B , Users U ,TempGeneralSearch1 T1,Users A LEFT JOIN Company C on (U.CompanyID = C.CompanyID) LEFT JOIN Expert_Info EI on (U.UserID = EI.UserID) LEFT JOIN User_Horizontal_Market UHM on (U.UserID = UHM.UserID) LEFT JOIN Industry I on (U.IndustryID = I.IndustryID) LEFT JOIN Technology_Area T on (U.Technology_AreaID = T.Technology_AreaID) LEFT JOIN Role R on (U.RoleID = R.RoleID ) WHERE U.UserID = UTD.UserID AND U.User_Status='ACT' AND U.User_App_Status='APP' AND UTD.User_TypeID IN (4,0,2,3) AND T1.TempID=117 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Optimization
Hi I Found Group By to be a bit faster...but still it takes 30 secs to execute a query... What could be done..I have Indexing on almost all the tables.. -Arul - Original Message - From: Dicky Wahyu Purnomo [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 08, 2002 4:25 PM Subject: Re: Query Optimization Pada Mon, 8 Jul 2002 16:07:04 +0530 Arul [EMAIL PROTECTED] menulis : Hi All The Query below took around 175 Secs to return 22 Rows..Any way this query can be optimized try to use group by instead of distinct i usually found group by is faster ;-) anyway ... performance of query is more related to : - table definition - indexing - mysql parameter values - OS and hardware spec/limitation -- Let's call it an accidental feature. -- Larry Wall MySQL 3.23.51 : up 17 days, Queries : 365.147 per second (avg). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do we make this query work in 3.23.51
Hi I had written a query which executes in MySql 4.0.1 But this is not working in 3.23.51 coz i had used Union function in the query which is not supported by 3.23.51 So any idea how this can be made to work on 3.23.51. I need to have only one Column in the result as TYPENAME SELECT CT.Company_Type as TYPENAME FROM RFQ_Details RD, Company_Type CT WHERE RD.RFQID = 4 AND RD.SEARCH_Type = 'COMPANY' AND Company_TypeID = RD.TypeID UNION SELECT UT.USER_Type as TYPENAME FROM RFQ_Details RD, User_Type UT WHERE RD.RFQID = 4 AND RD.SEARCH_Type = 'USER' AND User_TypeID = RD.TypeID - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do we make this query work in 3.23.51
Yea Andreas I had that idea but i thought that would be a performance issue... So i thought of avoiding this temp table... I am looking into an option where i can do it in 1 single query... -Arul - Original Message - From: Oswald, Andreas [EMAIL PROTECTED] To: 'Arul' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 02, 2002 3:09 PM Subject: AW: How do we make this query work in 3.23.51 Hi Arul, maybe copying the columns into a temporary table might do the job. I don't know how big your tables are, if performance might be a problem. The following statements should create the query you wanted: /*creating temporary table*/ CREATE TABLE tmp_Information ( TYPENAME (any typ-definition varchar,int???) ); /*inserting rows for company information*/ INSERT INTO tmp_Information SELECT CT.Company_Type as TYPENAME FROM RFQ_Details RD, Company_Type CT WHERE RD.RFQID = 4 AND RD.SEARCH_Type = 'COMPANY' AND Company_TypeID = RD.TypeID; /*inserting rows for user information*/ INSERT INTO tmp_Information SELECT UT.USER_Type as TYPENAME FROMRFQ_Details RD, User_Type UT WHERE RD.RFQID = 4 AND RD.SEARCH_Type = 'USER' AND User_TypeID = RD.TypeID; /*do whatever you want with the rows of the union*/ SELECT TYPENAME FROM tmp_Information; /*erase the temporary table*/ DROP TABLE tmp_Information Hopes that helps. Take care Andreas -Ursprüngliche Nachricht- Von: Arul [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 2. Juli 2002 11:04 An: MySQL Betreff: How do we make this query work in 3.23.51 Hi I had written a query which executes in MySql 4.0.1 But this is not working in 3.23.51 coz i had used Union function in the query which is not supported by 3.23.51 So any idea how this can be made to work on 3.23.51. I need to have only one Column in the result as TYPENAME SELECT CT.Company_Type as TYPENAME FROM RFQ_Details RD, Company_Type CT WHERE RD.RFQID = 4 AND RD.SEARCH_Type = 'COMPANY' AND Company_TypeID = RD.TypeID UNION SELECT UT.USER_Type as TYPENAME FROM RFQ_Details RD, User_Type UT WHERE RD.RFQID = 4 AND RD.SEARCH_Type = 'USER' AND User_TypeID = RD.TypeID - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL On Solaris
Hi All Can anyone refer me to any document which speaks about Installing MySQL 3.23.51 on Solaris 2.8 Sparc -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL On Solaris
The Document at www.mysql.com/manual is not clear for a Mysql beginner like me... Any Step by Step procedure... What should i do with the downloaded tar file .. I Had installed MySql in Windows.. But installing it in Solaris is not clear... -Arul - Original Message - From: Arul [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Tuesday, July 02, 2002 6:43 PM Subject: MySQL On Solaris Hi All Can anyone refer me to any document which speaks about Installing MySQL 3.23.51 on Solaris 2.8 Sparc -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help me with this query
Hi All I have two tables User and Company User tables has userid , companyid and status Company table has companyid and companyname It's not necessary that all users should have company.There can be users without company also and for them the companyid is null. so i need all the users whose status are active and if they have companyid , i need the companyname of their company In Oracle i can write this as select u.userid , (select companyname from company where companyid = u.companyid) from users u where u.status = 'ACT' how do i get this in mysql i wrote one query select u.userid , c.companyname from users u , company c where u.companyid = c.companyid AND u.status = 'ACT' This returned only the users who have company...how do i take the users also who doesnt have a company Regards -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql 4.0
Hi All In MySql 3.23.51 , i was able to add values into the child table I have created a table parent and also another table child where parentid column in child is a foreign key to the id column in parent table It was possible that i have no entries in parent table , and i was able to insert null values for parentid in child table.. This was a feature in 3.23.51 whereas this was not available in 3.23.49 When i tried MySQL 4.0.1 , i was shocked to see that i was not able to insert null values in the child table which was supported in 3.23.51 Any ideas -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql 4.0.2
Hi As i already stated , i am not able to insert a null value in a child table in 4.0.1 which i was able to do in 3.23.51 I have created a table parent and also another table child where parentid column in child is a foreign key to the id column in parent table It was possible that i have no entries in parent table , and i was able to insert null values for parentid in child table.. This was a feature in 3.23.51 whereas this was not available in 3.23.49 Any idea whether this will be supported in 4.0.2 and where i can get that from.. In the MySQL site i could see only upto 4.0.1 alpha release.. -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foriegn Key
Hi Dubey Actually foreign keys are supporte dfor INNODB Table types.. Whats the version of MYSQL u r using -Arul - Original Message - From: D.K.Dubey [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Sent: Monday, July 01, 2002 4:18 PM Subject: Foriegn Key Hi All, Can anybody tell mysql supports foreign constraints. If i use Referential Integrity cascade then, will MySql delete the same record in another table? Thanks in advance. Regards Daya Krishan Dubey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Left outer join
Hi Can i use left outer join in MySQL. Like i have one condition in Oracle ex: select * from user,company where u.companyid = c.companyid (+) can i rephrase the query in mysql like this select * from user left outer join company c using (companyid) -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Minus
Hi All MYSQL Supports Unions in 4.0.1. Similarly any idea when it will support other SET Operators Like Minus Regards, -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with a query
Hi Mikhail What i meant to say is that My First Query returns 3 rows satisfying two conditions Like CI.IndustryID IN (2,3) and Count(C.CompanyID) = 2 So when i add another OR say (CI.IndustryID IN (2,3) OR C.b2b = 'N') and Count(C.CompanyID) = 2 I guess it should aways have the possibilty of returning more rows or the same rows..I wonder how the number of rows returned is decreased with an OR Condition Regards, -Arul - Original Message - From: Arul [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Saturday, June 29, 2002 10:32 AM Subject: Re: Problem with a query No Mikhail I dont think i can agree with you . If you could see my first query which returned 3 rows also had a Having Clause. I just added an OR condition inside the query which should always increase the Number of Rows Returned..Am i correct..Check the Query plzz.. SELECT C.Companyid,C.B2b FROM Company C ,Company C1 , Company_Industries CI, Company_Type_Details CTD,Users U,User_Type_Details UTD Where C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid AND UTD.UserId=U.UserId AND UTD.User_Typeid=2 AND CTD.Company_Typeid=3 AND CTD.App_Status='APP' AND U.UserID 2 AND C.Company_App_Status='APP' AND C.Company_Status='ACT' AND C.CompanyID = CI.CompanyID AND C.CompanyID = C1.CompanyID AND (CI.IndustryID IN (2,3) ) Group By C.CompanyID,C.B2b HAVING Count(C.CompanyID) = 2 This Query returns 3 rows. CompanyIDB2B 64Y 77N 78Y Then i thought of checking B2b = 'N' with an OR Condition.So My Query became like this SELECT C.Companyid,C.B2b FROM Company C ,Company C1 , Company_Industries CI, Company_Type_Details CTD,Users U,User_Type_Details UTD Where C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid AND UTD.UserId=U.UserId AND UTD.User_Typeid=2 AND CTD.Company_Typeid=3 AND CTD.App_Status='APP' AND U.UserID 2 AND C.Company_App_Status='APP' AND C.Company_Status='ACT' AND C.CompanyID = CI.CompanyID AND C.CompanyID = C1.CompanyID AND (CI.IndustryID IN (2,3) OR C.B2b = 'N' ) Group By C.CompanyID,C.B2b HAVING Count(C.CompanyID) = 2 So in Theory this Query Should return more results if the B2b is 'N' Since this is an OR Query. But I got only two rows.The Result was CompanyIDB2B 64Y 78Y What happened to Company 77 whose B2B was 'N' Instead if i put an AND instead of OR , i get the result what i could judge ie: CompanyIDB2B 77N Can u please explain me what's happenning Regards, -Arul - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; Ralf Narozny [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Friday, June 28, 2002 8:17 PM Subject: Re: Problem with a query Arul, everything is correct. Let's assume you have this info: C.CompanyIDC.B2bCI.IndustryID 64Y2 64Y3 77N2 77N3 77N5 78Y2 78Y3 SELECT C.Companyid,C.B2b FROM Company C ,Company C1 , Company_Industries CI, Company_Type_Details CTD,Users U,User_Type_Details UTD Where C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid AND UTD.UserId=U.UserId AND UTD.User_Typeid=2 AND CTD.Company_Typeid=3 AND CTD.App_Status='APP' AND U.UserID 2 AND C.Company_App_Status='APP' AND C.Company_Status='ACT' AND C.CompanyID = CI.CompanyID AND C.CompanyID = C1.CompanyID AND (CI.IndustryID IN (2,3) OR C.B2b = 'N' ) Group By C.CompanyID,C.B2b HAVING Count(C.CompanyID) = 2 It will check (CI.IndustryID IN (2,3) OR C.B2b = 'N' ) after that count number of record and after that take only that rows where Count(C.CompanyID) = 2. So we will loose C.CompanyID = 77, because Count(C.CompanyID) will be 3. The same story will be if you will substitute OR with AND. Best regards, Mikhail. - Original Message - From: Arul [EMAIL PROTECTED] To: Ralf Narozny [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent
Converting to MySQL from Oracle
Hi All
I tried to port this Oracle Query to MySQL.But Couldnt do it..
I am porting this to MySQL 3.23.51 ..The problem is mainly coz there's
another query within Decode function
The Query is
SELECT DECODE(U.CompanyID, NULL, U.UserID, (SELECT A1.UserID FROM Users A1,
User_Type_Details A2 WHERE A1.UserID = A2.UserID AND
A1.CompanyID = U.CompanyID AND A2.User_TypeID =2)) as UserID
FROM Transaction_Data T, Rfq_Data R, Rfq_Details RD, Users U,
User_Type_Details UTD
WHERE T.TransactionID = R.TransactionID AND
R.RfqID = RD.RfqID AND
RD.TypeID = UTD.User_TypeID AND
RD.Search_Type = 'USER' AND
T.TransactionID = 200 AND
U.UserID = UTD.UserID AND
(R.Industryid=1 or U.IndustryID = R.IndustryID) AND
U.Technology_AreaID = R.Technology_AreaID AND
R.Horizontal_MarketID IN (Select Horizontal_MarketID FROM
User_Horizontal_Market WHERE UserID = U.UserID) AND
U.UserID T.UserID AND R.Is_Response = 'N' AND U.User_App_Status NOT IN
('NAP')
Could some one please help me
-Arul
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL 4.0
Hi Thomas I am always ready to give a try .. But u see Managers will not be ready to take such a risk...and Morever our client will not give a try ..:(( -Arul - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 1:27 PM Subject: Re: MYSQL 4.0 Arul, as you are intending to use open source software for free why not give it a try and help with debugging? 4.0.1 is remarkably stable except for some glitches in replication and boolean fulltext search. Alpha status applies mainly to the newly developed features. Go ahead and have fun! Thomas On Wed, 26 Jun 2002, Arul wrote: Hi Any idea when MySQL 4.0 stable release will be made.. As of now only Alpha is released.. Also any sites which run on 4.0 Alpha Regards, -Arul To pass the List : sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL 4.0
Hi But Still Noone have throwed some light on the Date when MySql 4.0 Release will be made formally which could enable me to use the Unions Can anyone in this group please let me know... -Arul - Original Message - From: Arul [EMAIL PROTECTED] To: Thomas Spahni [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 2:12 PM Subject: Re: MYSQL 4.0 Hi Thomas I am always ready to give a try .. But u see Managers will not be ready to take such a risk...and Morever our client will not give a try ..:(( -Arul - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 1:27 PM Subject: Re: MYSQL 4.0 Arul, as you are intending to use open source software for free why not give it a try and help with debugging? 4.0.1 is remarkably stable except for some glitches in replication and boolean fulltext search. Alpha status applies mainly to the newly developed features. Go ahead and have fun! Thomas On Wed, 26 Jun 2002, Arul wrote: Hi Any idea when MySQL 4.0 stable release will be made.. As of now only Alpha is released.. Also any sites which run on 4.0 Alpha Regards, -Arul To pass the List : sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Does Yahoo Use MySQL?
Hi Just was curious to know... I saw a posting by one of the guys here that Yahoo uses MySQL. Is this true.. Any idea how many MySQL servers does Yahoo Use amd for which domain they use MYSQL. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does Yahoo Use MySQL?
Hey Mike I just wanted to know what Database does Yahoo Uses.. Not the OS :) -Arul - Original Message - From: mos [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, June 28, 2002 11:05 AM Subject: Re: Does Yahoo Use MySQL? At 10:54 PM 6/27/2002, you wrote: Hi Just was curious to know... I saw a posting by one of the guys here that Yahoo uses MySQL. Is this true.. Any idea how many MySQL servers does Yahoo Use amd for which domain they use MYSQL. -Arul Arul, How much hoo could a Yahoo hoo, if a Yahoo could hoo hoo. :-) Sorry, I couldn't resist. It's been a long day. I don't know if they're running MySQL, but Yahoo is running FreeBSD. Their server has been up for 28.99 days and their max uptime was 64.59 days. Compare that to www.mysql.com. The site www.mysql.com is running Apache/1.3.26 (Unix) mod_gzip/1.3.19.1a mod_mysql_include/1.2b PHP/4.2.1 on Linux. www.mysql.com has been up for 279.01 days and the max was an amazing 492.34 days. Surely www.msn.com can beat that! With all their technology. The site www.msn.com is running Microsoft-IIS/5.0 on Windows 2000 Site has been up for 14 days running on Windows 2000. The max was 151.08 days. www.microsoft.com doesn't fare much better which averaged a measly 34 day uptime over the past year. If this isn't a plug for Linux webservers, I don't know what is. Want to know more? See www.netcraft.com . Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SubQueries and Temp Tables
Agreed Ralf... Not all SubQueries can be replaced by Joins.. Thats why we have planned for Temp Tables.. Any ideas on it.. - Original Message - From: Ralf Narozny [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Wednesday, June 26, 2002 3:00 PM Subject: Re: SubQueries and Temp Tables Hiho hiho! Which joins could that be? I think most if not all subqueries should be replacable with joins. Greetings Ralf Arul wrote: Hi All I am currently porting our application from Oracle to MySQL. We have some subqueries in oracle which cannot be ported into MySQL.We even tried some joins which didnt work out.. So Could anyone throw some light on Temporary tables.Is this the right way to do it.. Since our main query is dependent on the inner query we thought we could run the inner queryfirst and create a temp table.Then we could have a join between the main table and the inner table. Is this advisable.. Any other better options? -- Ralf Narozny Splendid Internet GmbH Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How can this Oracle Query converted to MySQL
SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) This is to Pass the Mail Server : sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYSQL 4.0
Hi Any idea when MySQL 4.0 stable release will be made.. As of now only Alpha is released.. Also any sites which run on 4.0 Alpha Regards, -Arul To pass the List : sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can this Oracle Query converted to MySQL
Hi All Sorry Guys...I just cut a small part of a bi Query where i had problems but i didnt see the User Table missing in the From List :(( Thanx for your Help.. -Arul - Original Message - From: Rob Vieira [EMAIL PROTECTED] To: 'Benjamin Pflugmann' [EMAIL PROTECTED]; Arul [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 5:42 AM Subject: RE: How can this Oracle Query converted to MySQL In a nutshell, this can be rewritten as a join - no sub-select needed, so no problem (this is true of the vast majority - albeit not all - sub-selects). Because the U table is missing (indeed, it looks like a lot of your join informaiton is missing too), there's no way for me to give you an accurate rewrite as a JOIN, but make Company_Industries a table that you join to, join it to Company on CompanyID, and add the IndustryID conditions and you're set. Rob -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 26, 2002 1:04 PM To: Arul Cc: MySQL Subject: Re: How can this Oracle Query converted to MySQL Hi. First, I removed the CC to the java list, because this problem is not java-related in any way. Second, I suggest you do some reading in the manual, the questions you asked recently are mostly answered there. On Wed 2002-06-26 at 15:03:19 +0530, [EMAIL PROTECTED] wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) I started to try to rewrite the query to sub-selects, when I noticed that it is broken. You have no table aliased to U, so U.UserID makes no sense. And it looks as if an restriction on Company is missing (else, the sub-query simply select all IndustrIDs in Company_Industries). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
@@IDENTITY
Hi
I m using mysql 3.23.51 on win 2k
Well..I have a user table where i have the userid column as primary key and
auto_increment column and name as varchar(25).
I insert values in the table such as
Insert into user (name) values ('AA');
Then When i do select @@Identity in the next time , it tells me as 0
Why is this happenning..
I need to get the last inserted value of the primary key ..right
-Arul
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: @@IDENTITY
Even the last_insert_id() returns 0
is it coz the table type is INNODB
- Original Message -
From: Jan Peuker [EMAIL PROTECTED]
To: Arul [EMAIL PROTECTED]
Sent: Friday, June 21, 2002 4:16 PM
Subject: Re: @@IDENTITY
You are using M$-Syntax, in MySQL you should use:
SELECT last_insert_id()
http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html
regards,
jan
- Original Message -
From: Arul
To: MySQL
Sent: Friday, June 21, 2002 12:17 PM
Subject: @@IDENTITY
Hi
I m using mysql 3.23.51 on win 2k
Well..I have a user table where i have the userid column as primary key
and
auto_increment column and name as varchar(25).
I insert values in the table such as
Insert into user (name) values ('AA');
Then When i do select @@Identity in the next time , it tells me as 0
Why is this happenning..
I need to get the last inserted value of the primary key ..right
-Arul
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Setting up InnoDB
Hi I tried setting up MySQl 3.23.50 Max I had installed and when i tried to start , it throws up the error message .But i need InnoDb to be started.. The error message when i start mysql is C:\mysql\binmysqld-max --standalone --console Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add for example, innodb_data_file_path = ibdata1:30M But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html mysqld-max: ready for connections But my.ini in my machine contains the innodb_data_file_path and also i had created the iblogs and ibdata in c: My.ini looks like this #This File was made using the WinMySQLAdmin 1.4 Tool #5/29/2002 1:52:16 PM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=C:/mysql #bind-address=10.1.1.72 datadir=C:/mysql/data #language=C:/mysql/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-max.exe user=root password=root # You can write your other MySQL server options here # ... # innodb_data_home_dir = c:\ibdata # Datafiles must be able to # hold your data and indexes innodb_data_file_path = ibdata1:20M;ibdata2:20M # Set buffer pool size to 50 - 80 % # of your computer's memory set-variable = innodb_buffer_pool_size=10M set-variable = innodb_additional_mem_pool_size=1M innodb_log_group_home_dir = c:\iblogs # .._log_arch_dir must be the same # as .._log_group_home_dir innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_log_files_in_group=3 # Set the log file-size to about # 15 % of the buffer pool size set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M # Set ..flush_log_at_trx_commit to # 0 if you can afford losing # a few last transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Any idea what could be the problem Regards -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do i connect my Java Program to a remote MYSQL
Hi All How do i connect to mysql on a remote machine.. Is there any client tools available Regards, -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Users
Hi all How do i create users in mysql.. After installing i just start mysql and connect to it.. Whats the default username and password it uses.. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Decode to Case
Hi All I am currently in the process of converting our database from Oracle to MySql. I Use 3.23.50 Max on Win 2k Well..here's a query where we use decode function in oracle...How do i change it to MYSQL The Query is -- SELECT C.Company_Name, DECODE(U.CompanyID, Null, U.USERID, (SELECT DISTINCT A.UserID FROM Users AWHERE A.User_TypeID = 2))as UserID,U.COMPANYID, U.PASSWORD FROM Users U, Company C WHERE U.CompanyID = C.CompanyID (+) ; --- Well...as per the query , if the company id in the user table is null , we need to select the UserID from the usertable and if the company id has any values , we need to select the distinct of the users in the company table I Thought of using CASE...so it went like this Select C.Company_Name , If(U.CompanyID is null , U,UserID,11) as UserID , U.CompanyID , U.Password From users left join company on u.companyid = c.company id... This by default assigns userid as 11 when companyid is nullBut how do we get the value from the user table instead of 11 Any ideas for replacing subqueries inside decode function...MySql doesnt support sub queries ...so how do i write queries inside a CASE condition Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Decode to Case
Hi Tommy My Requirement is not to select a non null value ...I need to select the companyid and if the companyid is null i need to select the userid from user table and if the companyid is not null , there may be many users for the company and i need to select the user i need (where user_type = 2) -Arul - Original Message - From: Tommy Claasens - Q Data KZN [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Wednesday, June 05, 2002 5:54 PM Subject: RE: Decode to Case Try a function called coaleace It will return the non null value. But i dont have the exact syntax with me nor the manual. Hope it helps Tommy -Original Message- From: Arul [mailto:[EMAIL PROTECTED]] Sent: Wed, 05 Jun 2002 13:26 To: MySQL Subject: Decode to Case Hi All I am currently in the process of converting our database from Oracle to MySql. I Use 3.23.50 Max on Win 2k Well..here's a query where we use decode function in oracle...How do i change it to MYSQL The Query is -- -- -- SELECT C.Company_Name, DECODE(U.CompanyID, Null, U.USERID, (SELECT DISTINCT A.UserID FROM Users AWHERE A.User_TypeID = 2))as UserID,U.COMPANYID, U.PASSWORD FROM Users U, Company C WHERE U.CompanyID = C.CompanyID (+) ; -- -- --- Well...as per the query , if the company id in the user table is null , we need to select the UserID from the usertable and if the company id has any values , we need to select the distinct of the users in the company table I Thought of using CASE...so it went like this Select C.Company_Name , If(U.CompanyID is null , U,UserID,11) as UserID , U.CompanyID , U.Password From users left join company on u.companyid = c.company id... This by default assigns userid as 11 when companyid is nullBut how do we get the value from the user table instead of 11 Any ideas for replacing subqueries inside decode function...MySql doesnt support sub queries ...so how do i write queries inside a CASE condition Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Null Values in child table with no values in Parent Table
Hi All I have created a table parent and also another table child where parentid column in child is a foreign key to the id column in parent table Is it possible that i have no entries in parent table , and i am inserting null values for parentid in child table.. sql,query Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where do i get 3.23.50 Max
Hi All MySQL Max doesnt support the feature of the column ,which is referred to be a foreign key , to be null But the Inno Db Site says We suppress the FOREIGN KEY check if any of the column values in the foreign key or referenced key to be checked is the SQL NULL.This is compatible with Oracle Ref : http://www.innodb.com/ibman.html#InnoDB_foreign_keys The release is MySQL/InnoDB-3.23.50, April 23, 2002 But where do i get this version... In www.mysql.org i could only download Max 3.23.49 Any ideas.. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL-Max-3.23.50 is downloadable from http://www.mysql.com/downloads/mysql-3.23-pre.html
Hi Heikki Tuuri I am not sure whether it's the Max version of MySql 3.23.50 Coz the URL differs for ordinary version and Max Version.In the Max version , i couldnt see anything beyond 3.23.49 -Arul - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 4:22 PM Subject: MySQL-Max-3.23.50 is downloadable from http://www.mysql.com/downloads/mysql-3.23-pre.html Hi! People on the mailing list are asking where they can download MySQL-(Max)-3.23.50. It is available from http://www.mysql.com/downloads/mysql-3.23-pre.html. The release .50 is a regular stable release for all the other platforms except Linux. In the Linux version there is a bug in the custom-built glibc library. But as far as I know the bug has not caused much problems to Linux users either. Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC Drivers
Hi Could anyone suggest me the JDBC Drivers available for MySql. Can the driver support Connection Pooling and RowSet -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unions
Hi All Is there anything Equivalent in MYSQL for Union in Oracle.. As per my understanding mysql doesnt support unions ...so any suggestions what could be done.. -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Having Null in parent table and values in child table
Hi All I have created a table parent and also another table child where parentid column in child is a foreign key to the id column in parent table Is it possible that i have no entries in parent table , and i am inserting null values for parentid in child table.. sql,query Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Alternative to decode() in Oracle
Hi All Is there any function in mysql which acts similar like decode() in Oracle.. The decode function acts like a procedural case statement inside a single SQL statment to return different values or columns based on the the values of other columns in the select statement. It is extremely versatile and can get you out of many tight spots. It was a favorite hacker tool before PL/SQL and worth learning how to drive. Mind you, decode() is not ANSI, and I have not seen it in the other major RDBMS offerings. Its one of the really nice things Oracle provides. (OK, so MS-Access provides iif, but these have to be nested and it gets ugly). -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using Row Id
Hi All How do i use RowId 's in MYSQL.. Can anyone throw some light on it Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternative to decode() in Oracle
Thanx Lewis..It worked
- Original Message -
From: Neville Lewis [EMAIL PROTECTED]
To: MySQL [EMAIL PROTECTED]
Sent: Friday, May 31, 2002 12:34 PM
Subject: RE: Alternative to decode() in Oracle
Have a look below:
--
If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2,
else it returns expr3. IF() returns a numeric or string value, depending
on
the context in which it is used:
mysql select IF(12,2,3);
- 3
mysql select IF(12,'yes','no');
- 'yes'
mysql select IF(strcmp('test','test1'),'no','yes');
- 'no'
---
expr1 is evaluated as an integer value, which means that if you are
testing
floating-point or string values, you should do so using a comparison
operation:
mysql select IF(0.1,1,0);
- 0
mysql select IF(0.10,1,0);
- 1
---
mysql SELECT CASE 1 WHEN 1 THEN one WHEN 2 THEN two ELSE more END;
- one
mysql SELECT CASE WHEN 10 THEN true ELSE false END;
- true
mysql SELECT CASE BINARY B when a then 1 when b then 2 END;
- NULL
---
mysql select 'Monty!' REGEXP 'm%y%%';
- 0
mysql select 'Monty!' REGEXP '.*';
- 1
mysql select 'new*\n*line' REGEXP 'new\\*.\\*line';
- 1
mysql select a REGEXP A, a REGEXP BINARY A;
- 1 0
mysql select a REGEXP ^[a-d];
- 1
-
Did that help?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Setting Auto Increment
Hi All Is it that i can make a column auto increment only if its data type is integer. Can i not set auto increment value for a numeric datatype -Arul sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating a Foreign Key
Hi All I am currently using 3.23.49 Max on Win 2K. I just saw from the docs that this version supports foreign key...So i just thought of working on it... I just created two tables parent and child CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) TYPE=INNODB; Then i inserted values into Parent Insert into parent (id) values(1); Insert into parent (id) values(2); Insert into parent (id) values(3); Then i inserted values into the child Insert into child values(1,1) Insert into child values(1,2) Insert into child values(1,3) Insert into child values(2,1) Insert into child values(2,2) Insert into child values(2,8) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |1 | 1 | |1 | 2 | |1 | 3 | |2 | 1 | |2 | 2 | |2 | 8 | |+--+---+ 9 rows in set (0.01 sec) If the foreign key is set i should have not been able to insert 8 in the child table as a parent_id. Also i deleted 2 from the parent tabl which didnt afftect the child table... So can anyone let me know the steps involved in creating an Inno Db and to achieve the Foreign key relationship Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC Transactions
Hi All, We are currently running a website which is running on Weblogic and Oracle DB.I just thought of porting it to JBoss and MySQL. Well..i am not sure how far does MySQL supports Transactions.. I am currently using 3.23.49 Max on Win 2K. Does this version of MySQL Supports transaction... -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB and My ISAM Tables
Hi All First of all i would like to know the basic difference between Inno DB and My ISAM Table types.. I wanted mySQL to support AutoIncrement , Transactions,Foreign Key Constraints,Blob, Text etc...so as per the documents i thought of using a InnoDB Table... Currently i am using 3.23.49 Max on Win 2K...I created two tables parent and child CREATE TABLE parent(id INT NOT NULL,PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT,INDEX par_ind (parent_id),FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; After creating the tables when i executed the Query mysqlshow table status This says that the Table is of type MyISAM. This confuses me a lottt...Also the table i had created is not working for Foreign Key Relations.. What could be the soltuion...Kindly Advice -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC Transactions
Thanx Mark But i am new to MySQL... First of all i would like to know the basic difference between Inno DB and My ISAM Table types.. I wanted mySQL to support AutoIncrement , Transactions,Foreign Key Constraints,Blob, Text etc...so as per the documents i thought of using a InnoDB Table... Currently i am using 3.23.49 Max on Win 2K...I created two tables parent and child CREATE TABLE parent(id INT NOT NULL,PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT,INDEX par_ind (parent_id),FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; After creating the tables when i executed the Query mysqlshow table status This says that the Table is of type MyISAM. This confuses me a lottt...Also the table i had created is not working for Foreign Key Relations.. What could be the soltuion...Kindly Advice -Arul - Original Message - From: Mark Matthews [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 4:47 PM Subject: Re: JDBC Transactions Arul wrote: Hi All, We are currently running a website which is running on Weblogic and Oracle DB.I just thought of porting it to JBoss and MySQL. Well..i am not sure how far does MySQL supports Transactions.. I am currently using 3.23.49 Max on Win 2K. Does this version of MySQL Supports transaction... -Arul Yes. You will need to use a transaction-capable table type like InnoDB (preferred) or BDB to get true transaction semantics. -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: JDBC Transactions
- Original Message - From: Luca Lafranchi [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 5:10 PM Subject: Re: JDBC Transactions Hi Arul, this is not a direct answer to your question, only a note about foreign keys and MySQL: if I'm right (please someone correct me if not), foreign keys aren't implemented in MySQL: you can define them in your create statement if you want, but they will not work. Ciao Luca Arul wrote: Thanx Mark But i am new to MySQL... First of all i would like to know the basic difference between Inno DB and My ISAM Table types.. I wanted mySQL to support AutoIncrement , Transactions,Foreign Key Constraints,Blob, Text etc...so as per the documents i thought of using a InnoDB Table... Currently i am using 3.23.49 Max on Win 2K...I created two tables parent and child CREATE TABLE parent(id INT NOT NULL,PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT,INDEX par_ind (parent_id),FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; After creating the tables when i executed the Query mysqlshow table status This says that the Table is of type MyISAM. This confuses me a lottt...Also the table i had created is not working for Foreign Key Relations.. What could be the soltuion...Kindly Advice - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SubQueries
Hi All i am running MySql 3.23.49 Max on Win 2K Does this version of MySQL supports subqueries. Also does this support transaction.. Where can i get the details reg this.. Regards, Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Alternate to varchar2
Hi All I am trying to port my oracle Databse to mysql. I am using 3.23.49 version of mysql. i had certain columns in oracle which had varchar2(900) But in mysql varchar doesnt support more than 255 so what could be the alternate in this case.. Can i use Blob or text..But again if i use blob and if i use a like condition in my query , it would be a performance problem. Could anyone suggest a solution for this Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
