Re: Space is filling up
Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. My databases (mysql,rt3,test) are taking only about 2k. I am worry about the ib*log and ibdata* files. Mostly the ibdata files which are taking 5 gig. I am not sure if I can clean those up or rotate or something else to reclaim the disk space. (root)@webrt:/usr/local/mysql/data# /usr/local/bin/ls -lh total 5.2G -rw-rw1 mysqlmysql 25K Jul 4 2003 ib_arch_log_00 -rw-rw1 mysqlmysql2.5K Aug 27 2003 ib_arch_log_02 -rw-rw1 mysqlmysql2.5K Aug 28 2003 ib_arch_log_04 -rw-rw1 mysqlmysql100M Jul 8 20:38 ib_logfile0 -rw-rw1 mysqlmysql100M Jun 29 20:23 ib_logfile1 -rw-rw1 mysqlmysql1.9G Jul 8 20:37 ibdata1 -rw-rw1 mysqlmysql3.1G Jul 8 20:33 ibdata2 -rw-r--r--1 mysqlmysql1.6K Sep 15 2003 my.cnf drwxr-x---2 mysqlmysql 512 Jul 4 2003 mysql drwx--2 mysqlmysql 512 Jul 30 2003 newdb drwx--2 mysqlmysql1.5K Jun 30 23:10 rt3 drwxr-x---2 mysqlmysql 512 Jul 28 2003 test -rw-rw1 mysqlother 67K Nov 21 2003 webrt.err -rw-rw1 mysqlmysql 3 Nov 21 2003 webrt.pid Thanks for all the help Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Space is filling up
Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data file too big
On Fri, 23 Jan 2004, Mikhail Entaltsev wrote: Date: Fri, 23 Jan 2004 09:34:52 +0100 From: Mikhail Entaltsev [EMAIL PROTECTED] To: Gregory Newby [EMAIL PROTECTED], Asif Iqbal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: data file too big I believe that this will flush those logs: mysql reset master; Is there any chance of loosing any data if I do that. Its a production machine with over 200 thousand customer tickets No.. It won't shrink any of InnoDB datafiles: 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 It could help if you have some MySQL binary logs: hostname-bin.001 ... hostname-bin.00N I don't. Is there any other suggestions ? Best regards, Mikhail. - Original Message - From: Gregory Newby [EMAIL PROTECTED] To: Asif Iqbal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:32 PM Subject: Re: data file too big I believe that this will flush those logs: mysql reset master; -- Greg On Thu, Jan 22, 2004 at 05:23:07PM -0500, Asif Iqbal wrote: Hi All My data file has all these files (root)@webrt:/usr/local/mysql/data# du -sh * 25K ib_arch_log_00 3.0Kib_arch_log_02 3.0Kib_arch_log_04 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 2.0Kmy.cnf 70K mysql 2.0Knewdb 39M rt3 12K test 67K webrt.err 1.0Kwebrt.pid Is there anyway I can remove some of them so I can get some space back ? I am using mysql -4.0.13 on Solaris 8 -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- 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] -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data file too big
Hi All My data file has all these files (root)@webrt:/usr/local/mysql/data# du -sh * 25K ib_arch_log_00 3.0Kib_arch_log_02 3.0Kib_arch_log_04 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 2.0Kmy.cnf 70K mysql 2.0Knewdb 39M rt3 12K test 67K webrt.err 1.0Kwebrt.pid Is there anyway I can remove some of them so I can get some space back ? I am using mysql -4.0.13 on Solaris 8 -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
apostrophe error
msg.pgp Description: PGP message
Re: apostrophe error
msg.pgp Description: PGP message
Re: link in your book (was Re: apostrophe error)
msg.pgp Description: PGP message
Re: Newbie: need form to input records View report
Troy T. Hall wrote: I know this sounds stupid but I'm totally lost. I've created a MySQL DB whose purpose is to track customers who have not gotten a newspaper. I've created all the necessary fields, and have managed to learn how to add/delete/modify the records in mysqlcc, but what I want is to have a predesigned form like you'd find on a webpage where you simply enter the complaints and hit submit or whatever, and it responds with the complaint ID # ( ComplaintID is an autoincrement field in the db ) Then I need to be able to print a report in a nicely labeled/readable format showing all entries made that day where the chargeable field is not List (Chargeable is an enum consisting of yes, no, list). Will someone please be kind enough to point me in the right direction. I can't seem to get my head around this concept. Troy oh, if it matters, I'm using Linux Mandrake 9.2 with the Gnome 2.0 GDM. I also have qtDesigner, OO, Screem. Have you looked at RT ( http://http://bestpractical.com/rt/ ) ? We are using it to receive customer complains. When the customer sends an email they get a Complain ID and there are lot of other things as optional The best part is it is free and and have excellent mailing list with supports. It requires Perl/MySQL/Apache+mod_perl1 Very easy to manage and excellent tool -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 pgp0.pgp Description: PGP signature
SQL syntax error
Hi All I have been pushing my syslogs to the following mysql table However whenever it sees lines with a ' (apostrophe) it complains about SQL syntax Here are two lines with ' from my syslog: Jan 1 03:58:15 dal-svcs-02.inet.qwest.net 203: *Jan 1 08:58:13.926 UTC: %PFINIT-SP-5-CONFIG_SYNC: Sync'ing the startup configuration to the standby Router Jan 01 00:57:06 [65.119.67.5.17.126] %NTP-W-NOTIMEZONE, Time will not be set until timezone is configured; use 'system set timezone' to configure Here is how it complains: failed to run query: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ing the startup configuration to the standby Router ')' at line failed to run query: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'system set timezone' to configure ', '087) (RST) ')' at line 1 Here is how my mysql table looks like +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | timestamp | varchar(16) | YES | | NULL|| | host | varchar(255) | YES | | NULL|| | prog | varchar(255) | YES | | NULL|| | mesg | text | YES | | NULL|| +---+--+--+-+-++ Is there anyway I can modify the host,prog and mesg field types to accept apostrophe as part of the record ? Thanks for all the help (Happy New Year !!) -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem
Carlos Andre Moura de Amorim wrote: i have a follow problem, please, help-me [EMAIL PROTECTED] db]# mysql --user=root --password=xx ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) try to add -h hostname -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 .vs. MySQL 4.0.13 (fwd)
I was wondering if anyone can give a direction on this sub-query issue. Do I must upgrade it to MySQL 4.1 alpha or is there an alternative on MySQL 4.0.13 as a workaround to sub-query Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- Forwarded message -- Date: Tue, 16 Sep 2003 14:31:21 -0400 (EDT) From: Asif Iqbal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL 4.1 .vs. MySQL 4.0.13 Hi I have a user who needs to do the following [snip] I believe I will need sub-selects for the following type of query. Subqueries are not supported until MySQL 4.1. I will need to do longest-match lookups in the DB to mimic the way that IP routers do longest-match routing lookups. In the most basic case, I will have the following table entries that I need to do a bitwise-AND comparison on and output ONLY the most-specific route match. There will be other columns, but this should suffice to illustrate why sub-queries are required: CREATE TABLE as_aggregate ( as_num INTEGER, ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT); CREATE TABLE static_route ( router VARCHAR(32), ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT, next_hop_valINTEGER); And then I need rows matching only the most specific bitwise-AND between the static_route table and the as_aggregate table: SELECT sr.router, sr.ipaddr_val, sr.prefix_length sr.OTHER_COLUMNS aa.as_num, aa.ipaddr_val, aa.netmask_val, aa.OTHER_COLUMNS FROMas_aggregate aa, (SELECT sr.router, sr.ipaddr_val, sr.prefix_length, MAX(msaa.prefix_length) AS most_specific_length FROMstatic_route sr, as_aggregate msaa WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val msaa_netmask_val GROUP BY sr.router, sr.ipaddr_val, sr.prefix_length ) WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val sr.netmask_val AND aa.prefix_length = most_specific_length; - I would prefer not to create temporary tables because: * this data will change a lot as I tweak it and poll routers over and over * the size of the temporary table will be O(routes * as_aggregate) * I am going to index all the columns that do bitwise AND so that row pairings can be made by consulting the index only. This should make the most-specific comparison nice and fast. Also, I may have to do two levels of most-specific indirection for complex route resolution. This will further complicate temporary tables. [/snip] Now My question is if there any possible way I can stick to the current installation MySQL 4.0.13 and have the user do his job with some workaround ? Thanks for your suggestions -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 .vs. MySQL 4.0.13 (fwd)
I wonder if my user can follow section 1.7.4.1 in mysql.com for what he wants to do without me upgrading the MySQL from 4.0.x to 4.1 alpha This is what my user wants [snip] I believe I will need sub-selects for the following type of query. Subqueries are not supported until MySQL 4.1. I will need to do longest-match lookups in the DB to mimic the way that IP routers do longest-match routing lookups. In the most basic case, I will have the following table entries that I need to do a bitwise-AND comparison on and output ONLY the most-specific route match. There will be other columns, but this should suffice to illustrate why sub-queries are required: CREATE TABLE as_aggregate ( as_num INTEGER, ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT); CREATE TABLE static_route ( router VARCHAR(32), ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT, next_hop_valINTEGER); And then I need rows matching only the most specific bitwise-AND between the static_route table and the as_aggregate table: SELECT sr.router, sr.ipaddr_val, sr.prefix_length sr.OTHER_COLUMNS aa.as_num, aa.ipaddr_val, aa.netmask_val, aa.OTHER_COLUMNS FROMas_aggregate aa, (SELECT sr.router, sr.ipaddr_val, sr.prefix_length, MAX(msaa.prefix_length) AS most_specific_length FROMstatic_route sr, as_aggregate msaa WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val msaa_netmask_val GROUP BY sr.router, sr.ipaddr_val, sr.prefix_length ) WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val sr.netmask_val AND aa.prefix_length = most_specific_length; - I would prefer not to create temporary tables because: * this data will change a lot as I tweak it and poll routers over and over * the size of the temporary table will be O(routes * as_aggregate) * I am going to index all the columns that do bitwise AND so that row pairings can be made by consulting the index only. This should make the most-specific comparison nice and fast. Also, I may have to do two levels of most-specific indirection for complex route resolution. This will further complicate temporary tables. [/snip] Now My question is if there any possible way I can stick to the current installation MySQL 4.0.13 and have the user do his job with some workaround ? Thanks for your suggestions -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 .vs. MySQL 4.0.13
Hi I have a user who needs to do the following [snip] I believe I will need sub-selects for the following type of query. Subqueries are not supported until MySQL 4.1. I will need to do longest-match lookups in the DB to mimic the way that IP routers do longest-match routing lookups. In the most basic case, I will have the following table entries that I need to do a bitwise-AND comparison on and output ONLY the most-specific route match. There will be other columns, but this should suffice to illustrate why sub-queries are required: CREATE TABLE as_aggregate ( as_num INTEGER, ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT); CREATE TABLE static_route ( router VARCHAR(32), ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT, next_hop_valINTEGER); And then I need rows matching only the most specific bitwise-AND between the static_route table and the as_aggregate table: SELECT sr.router, sr.ipaddr_val, sr.prefix_length sr.OTHER_COLUMNS aa.as_num, aa.ipaddr_val, aa.netmask_val, aa.OTHER_COLUMNS FROMas_aggregate aa, (SELECT sr.router, sr.ipaddr_val, sr.prefix_length, MAX(msaa.prefix_length) AS most_specific_length FROMstatic_route sr, as_aggregate msaa WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val msaa_netmask_val GROUP BY sr.router, sr.ipaddr_val, sr.prefix_length ) WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val sr.netmask_val AND aa.prefix_length = most_specific_length; - I would prefer not to create temporary tables because: * this data will change a lot as I tweak it and poll routers over and over * the size of the temporary table will be O(routes * as_aggregate) * I am going to index all the columns that do bitwise AND so that row pairings can be made by consulting the index only. This should make the most-specific comparison nice and fast. Also, I may have to do two levels of most-specific indirection for complex route resolution. This will further complicate temporary tables. [/snip] Now My question is if there any possible way I can stick to the current installation MySQL 4.0.13 and have the user do his job with some workaround ? Thanks for your suggestions -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 .vs. MySQL 4.0.13 (fwd)
Hi I have a user who needs to do the following [snip] I believe I will need sub-selects for the following type of query. Subqueries are not supported until MySQL 4.1. I will need to do longest-match lookups in the DB to mimic the way that IP routers do longest-match routing lookups. In the most basic case, I will have the following table entries that I need to do a bitwise-AND comparison on and output ONLY the most-specific route match. There will be other columns, but this should suffice to illustrate why sub-queries are required: CREATE TABLE as_aggregate ( as_num INTEGER, ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT); CREATE TABLE static_route ( router VARCHAR(32), ipaddr_val INTEGER, netmask_val INTEGER, prefix_length SMALLINT, next_hop_valINTEGER); And then I need rows matching only the most specific bitwise-AND between the static_route table and the as_aggregate table: SELECT sr.router, sr.ipaddr_val, sr.prefix_length sr.OTHER_COLUMNS aa.as_num, aa.ipaddr_val, aa.netmask_val, aa.OTHER_COLUMNS FROMas_aggregate aa, (SELECT sr.router, sr.ipaddr_val, sr.prefix_length, MAX(msaa.prefix_length) AS most_specific_length FROMstatic_route sr, as_aggregate msaa WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val msaa_netmask_val GROUP BY sr.router, sr.ipaddr_val, sr.prefix_length ) WHERE sr.ipaddr_val msaa.netmask_val = msaa.ipaddr_val sr.netmask_val AND aa.prefix_length = most_specific_length; - I would prefer not to create temporary tables because: * this data will change a lot as I tweak it and poll routers over and over * the size of the temporary table will be O(routes * as_aggregate) * I am going to index all the columns that do bitwise AND so that row pairings can be made by consulting the index only. This should make the most-specific comparison nice and fast. Also, I may have to do two levels of most-specific indirection for complex route resolution. This will further complicate temporary tables. [/snip] Now My question is if there any possible way I can stick to the current installation MySQL 4.0.13 and have the user do his job with some workaround ? Thanks for your suggestions -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Error ib_logfile0 of different size
On Tue, 26 Aug 2003, Heikki Tuuri wrote: Asif, now take the old log files from the safe place and put them back! That will save your data. The instructions about changing the size of the log files are the following: http://www.innodb.com/ibman.html#Adding_and_removing If you want to change the number or the size of your InnoDB log files, you have to shut down MySQL and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log files from the log file directory, edit my.cnf, and start MySQL again. InnoDB will tell you at the startup that it is creating new log files. That worked, thanks a lot. Now I am wondering if their is any other modification on my system with 4x450Mhz, 4gig RAM, mysql 4.0.13 that I should look into to improve the performance Here is my cnf file look like [client] #password = your_password port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #skip-networking #log-bin server-id = 1 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates Also not sure what I should do here, keep the comments? [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout I am doing any replication. I have just one server. I am using MyISAM and InnoDB Type tables and Row formats were Dynamic Thanks again -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication with multiple servers in Mysql ?
Hi Jeremy Zawodny (and all) I am using your mytop and this is what I am seeing MySQL on localhost (4.0.13-standard-log) up 0+10:48:22 [11:14:37] Queries: 85.6k qps:2 Slow: 207.0 Se/In/Up/De(%):86/05/05/02 qps now:2 Slow qps: 0.0 Threads: 11 ( 2/ 4) 00/00/00/00 Cache Hits: 37.6k Hits/s: 1.0 Hits now: 0.0 Ratio: 51.0% Ratio now: 0.0% Key Efficiency: 99.2% Bps in/out: 727.8/ 1.5k Now in/out: 40.8/ 4.2k It seems my mysql is little slow. I have 4 450Mhz with 4 gig RAM and I am using my-huge.cnf. I don't have any secondary server. Wonder if you or anyone else could suggest any place for improvement. Thanks a lot -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance
Sorry for reposting , wrong subject in the prebious one -- Forwarded message -- Date: Wed, 27 Aug 2003 11:17:36 -0400 (EDT) From: Asif Iqbal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Replication with multiple servers in Mysql ? Hi Jeremy Zawodny (and all) I am using your mytop and this is what I am seeing MySQL on localhost (4.0.13-standard-log) up 0+10:48:22 [11:14:37] Queries: 85.6k qps:2 Slow: 207.0 Se/In/Up/De(%):86/05/05/02 qps now:2 Slow qps: 0.0 Threads: 11 ( 2/ 4) 00/00/00/00 Cache Hits: 37.6k Hits/s: 1.0 Hits now: 0.0 Ratio: 51.0% Ratio now: 0.0% Key Efficiency: 99.2% Bps in/out: 727.8/ 1.5k Now in/out: 40.8/ 4.2k It seems my mysql is little slow. I have 4 450Mhz with 4 gig RAM and I am using my-huge.cnf. I don't have any secondary server. Wonder if you or anyone else could suggest any place for improvement. Thanks a lot -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Error ib_logfile0 of different size
I already have mysql 4.0.13 running with all InnoDB tables. I am trying to use my-huge.cnf since I have 4 gig RAM with 4 x 450 Mhz processes In my MySQL data directory I have two ib_logfile files -rw-rw 1 mysqlmysql5242880 Aug 26 01:55 ib_logfile0 -rw-rw 1 mysqlmysql5242880 Aug 25 19:03 ib_logfile1 Now if I add this line in my cnf file (as recommended by my-huge.cnf) innodb_log_file_size = 100M I get the following error InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 104857600 bytes! 030826 1:42:15 Can't init databases 030826 1:42:15 Aborting So as a workaround I commented the innodb_log_file_size. But I really don't like this solution. Is there a better solution ? Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Error ib_logfile0 of different size
On Tue, 26 Aug 2003, Victoria Reznichenko wrote: Asif Iqbal [EMAIL PROTECTED] wrote: I already have mysql 4.0.13 running with all InnoDB tables. I am trying to use my-huge.cnf since I have 4 gig RAM with 4 x 450 Mhz processes In my MySQL data directory I have two ib_logfile files -rw-rw 1 mysqlmysql5242880 Aug 26 01:55 ib_logfile0 -rw-rw 1 mysqlmysql5242880 Aug 25 19:03 ib_logfile1 Now if I add this line in my cnf file (as recommended by my-huge.cnf) innodb_log_file_size = 100M I get the following error InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 104857600 bytes! 030826 1:42:15 Can't init databases 030826 1:42:15 Aborting So as a workaround I commented the innodb_log_file_size. But I really don't like this solution. Is there a better solution ? Move old log files to the safe place. Edit my.cnf and then start MySQL server. If I move the old log in a safe place and use innodb_log_file_size = 100M, this is what I get 030826 01:40:38 mysqld started 030826 1:40:39 InnoDB: Log file /usr/local/mysql/data/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /usr/local/mysql/data/ib_logfile0 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 030826 1:40:47 InnoDB: Log file /usr/local/mysql/data/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /usr/local/mysql/data/ib_logfile1 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Cannot initialize created log files because InnoDB: data files were not in sync with each other InnoDB: or the data files are corrupt. 030826 1:40:54 Can't init databases 030826 1:40:54 Aborting -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
split database
I am running mysql 4.0.13 with innodb. I have data since 2000. I don't necessarily need to work with any data older than one year. Therefore I would like to split the database. Then I like to backup data older than one year and keep anything newer than one year and put it back to the same tables. So basically . DB_OLD (2000-2001) | DB (2000-2003) ---| . DB (2002-2003) That way all my applications will still work with the database and no need to change the table name or database name This is my game plan 1. Dump the database to database.sql 2. Remove all the entries older than one year and save it as database_new.sql 3. Drop the database 4. Source database_new.sql 5. Not sure what I need to do with the innodb data/log Please help/suggest -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb doc in mysql.com
On Sun, 24 Aug 2003, Heikki Tuuri wrote: Asif, - Original Message - From: Asif Iqbal [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 23, 2003 10:09 PM Subject: innodb doc in mysql.com In mysql.com site on section 7.5.4.1 it says and I qoute If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all datafiles, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding `.frm' files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. If I do this, will it break my database ? I am still new with InnoDB concept of course you will lose all your data if you delete the ibdata files. My ibdata1 is reaching the disk limit ? Can I rotate it or do I need to add another partition and reference new ibdata2 to that disk ? Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb doc in mysql.com
In mysql.com site on section 7.5.4.1 it says and I qoute If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all datafiles, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding `.frm' files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. If I do this, will it break my database ? I am still new with InnoDB concept Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ibdata1 is close to disk size
Hi I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL? Please help. [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M query_cache_size = 16M query_cache_limit = 8M log-bin server-id = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /usr/local/mysql/var/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/var/ #innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 ( I had to comment the innodb to stop crashing ) [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Please help Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump w/o password
Hi All I have to run this to dump the database mysqldump -u root -pPASS --opt database database.tmp Is there anyway I can hide the PASS is a file ? I was trying to put it in /root/.my.cnf as this [client] password = PASS That seems to be not working. I am not sure if mysqldump even reads that file. The only option worked for me is putting the password in a file /root/.pass and chmod it to 000 and then run this mysqldump -u root -p`cat /root/.pass` --opt database database.tmp However I don't like this option because it tells you where the password is residing. I need to put this in cron so I don't like the option of showing the filename Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB data file is growing
My ibdata1 file is growing. Do I need to worry about that ? I am guessing it will automatically delete old data to fit the size, correct ? Also how do I limit the growth size. I tried to put max:2000M , but since my Innodb is crashing and can't restart mysql I removed the whole my.cnf file all together to fix the crash. So now I am not sure how I can prevent the growth of InnoDB. I looked through the InnoDB website but I don't see it addressing InnoDB crash issue Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqld is keep crashing
Is there a pdf format of this ibman page ? OR is there a good book on Mysql+innodb out there ? Thanks On Fri, 1 Aug 2003, Heikki Tuuri wrote: Asif, - Original Message - From: Asif Iqbal [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, July 31, 2003 10:41 PM Subject: Mysqld is keep crashing This is what I bumped into now 030731 15:39:50 mysqld started 030731 15:39:52 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3491794745 InnoDB: Doing recovery: scanned up to log sequence number 0 3491800490 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 32 row operations to undo InnoDB: Trx id counter is 0 3532544 030731 15:39:53 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 3532191, 32 rows to undoInnoDB: Error: trying to access page number 2650767236 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030731 15:39:59 InnoDB: Assertion failure in thread 1 in file fil0fil.c line 1176 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 030731 15:39:59 mysqld ended Anyone would know how I can fix it your InnoDB database is now corrupt. That may be a result of mixing ibdata files or ib_logfiles. I recall you were trying to modify my.cnf 2 days ago and had some problems. You can try starting with http://www.innodb.com/ibman.html#Forcing_recovery: set-variable = innodb_force_recovery = 4 and dump your tables and recreate your ibdata files and ib_logfiles. I am using my-huge.cnf . I have 4 450Mhz with 4 gig ram -- Asif Iqbal Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld is keep crashing
This is what I bumped into now 030731 15:39:50 mysqld started 030731 15:39:52 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3491794745 InnoDB: Doing recovery: scanned up to log sequence number 0 3491800490 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 32 row operations to undo InnoDB: Trx id counter is 0 3532544 030731 15:39:53 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 3532191, 32 rows to undoInnoDB: Error: trying to access page number 2650767236 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030731 15:39:59 InnoDB: Assertion failure in thread 1 in file fil0fil.c line 1176 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 030731 15:39:59 mysqld ended Anyone would know how I can fix it I am using my-huge.cnf . I have 4 450Mhz with 4 gig ram -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Books advice
I just ordered this book MySQL The definitive guide to using, programming, and administering MySQL 4 by Paul Dubois I found it more technical than MySQL cook book by Paul Dubois On Thu, 31 Jul 2003, Ralph Guzman wrote: Here are two other books that I would recommend, specially the first one: * SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by Michael J. Hernandez, John L. Viescas Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko -Original Message- From: Fawad Siddiqui [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 5:06 PM To: [EMAIL PROTECTED] Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Books advice
ISBN 0-7357-1212-3 On Fri, 1 Aug 2003, Lucas Fonzalida - Soporte Tecnico wrote: Asif, can you paste here the ISBN? Thanks!! Lucas -Mensaje original- De: Asif Iqbal Enviado el: Jueves, 31 de Julio de 2003 22:05 Para: Ralph Guzman CC: 'Fawad Siddiqui'; [EMAIL PROTECTED] Asunto: RE: Books advice I just ordered this book MySQL The definitive guide to using, programming, and administering MySQL 4 by Paul Dubois I found it more technical than MySQL cook book by Paul Dubois -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Impossible mysqldump - Please HELP!!!
On Thu, 31 Jul 2003, Ola Ogunneye wrote: I am running mysql 4.0.13 on Apache 2.0.47 and PHP 4.3.2. I can log onto my Mysql and I can show databases, and create users and passwords. OS is Windows 2000 Advanced Server. In trying to backup my database, I used the MySQL Second edition syntax and also used something somebody suggested in a previous email, unfortunately I got error code 1064 in both cases. I tried the following: Name of database in this case is candleplace and I got this syntax from MYSQL 2nd Edition. mysql mysqldump --opt candleplace /archive/mysql/candleplace.073103 You run it from shell prompt What I got was: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump --opt candleplace /archive/mysql/candleplace.073103' at line 1 I also tried backing up the following way after creating dbase directory in my D drive: mysql mysqldump -u ola -p --opt --database --complte-insert --flush-logs d:/dbase/candleplace.2003-7-31 You run it from dos prompt Each time I tried any of the commands to run mysqldump, I never included the ; and I end up getting the - and when I put in the ; then I get the error message. I have tried using mysqlhotcopy and I still got the same result. Is there something that I am doing wrong please point me in the right direction. Thank you all very much Ola A. Ogunneye Senior Accountant Adventist Healthcare - Financial Services 1801 Research Boulevard, Suite 400 Rockville, MD 20850 Tel: 301.315.3274 Fax: 301.315.3265 Email: [EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file of different size
On Wed, 30 Jul 2003, Paul DuBois wrote: What I'd do if it were me in your situation: - Remove the innodb_data_file_path line from your my.cnf file done - Restart the server; it should come up normally now done - Perform a complete dump of all your databases (just in case) (use mysqldump) done - Perform a complete dump of your InnoDB tables (use mysqldump) how do I do this ? this is where I am stuck - Remove your InnoDB tables - Shut down the server - Remove the default InnoDB data file and log files (these will be the files that begin with ib in your data directory) - Shut down the server - Add the innodb_data_file_path line to your my.cnf file - Start the server - Load the dump file back into the server to recreate your InnoDB tables Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file of different size
innodb_data_file_path = ibdata1:1266M;ibdata2:10M:autoextend might work. But best to make a backup of the MySQL datadir first, just in That did it. However I could not put ibdata2. It was saying no access rights to file ibdata2. Once I removed the ibdata2 entry it started fine. Another thing I noticed that it can't create the pid file. However it has no problem creating the err file. Would you know why is it so ? case something goes wrong. Regards, Heikki . Subject: Re: innodb data file of different size From: Paul DuBois Date: Wed, 30 Jul 2003 00:07:49 -0500 At 22:08 -0400 7/29/03, Asif Iqbal wrote: I just decided to use my.cnf and bumped into this error message 030729 22:04:22 mysqld started InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size InnoDB: 81024 pages (rounded down to MB) InnoDB: than specified in the .cnf file 16384 pages! InnoDB: Could not open data files 030729 22:04:23 Can't init databases 030729 22:04:23 Aborting 030729 22:04:23 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030729 22:04:23 /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld: Shutdown Complete 030729 22:04:23 mysqld ended I am assuming I need to change values in the following line taken from my.cnf file innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend Please help Most likely you were running without any InnoDB-related options in your option file, so it was using its default values. Then you added the option shown above, which explicitly specifies sizes for the data files -- and which does not match the number or size of the default data file. What I'd do if it were me in your situation: - Remove the innodb_data_file_path line from your my.cnf file - Restart the server; it should come up normally now - Perform a complete dump of all your databases (just in case) (use mysqldump) - Perform a complete dump of your InnoDB tables (use mysqldump) - Remove your InnoDB tables - Shut down the server - Remove the default InnoDB data file and log files (these will be the files that begin with ib in your data directory) - Shut down the server - Add the innodb_data_file_path line to your my.cnf file - Start the server - Load the dump file back into the server to recreate your InnoDB tables Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is a good benchmark?
Now I am running these processes for my mysql serever /bin/sh /usr/local/mysql/bin/safe_mysqld --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/webrt.pid /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld --defaults-extra-file=/usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/data/my.cnf --basedir=/usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/webrt.pid Now on the same logic if I run 4 pair ( the above two processes ) will it imporve my mysql query performance done through my DBIx::SeacrhBuilder ? Thanks On Tue, 29 Jul 2003, Dan Nelson wrote: In the last episode (Jul 29), Asif Iqbal said: Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any suggestion on how to improve it ? Run it 4 times simultaneously in separate windows. You got 4 CPUs, use them :) All that benchmark query does is tell you how fast one cpu is. -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is a good benchmark?
On my E420R with 4 x 450MHz and 4 gb mem what mysql variables should I need to play with to improve the following output mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (2.89 sec) Sorry for newbie question On Tue, 29 Jul 2003, Dan Nelson wrote: In the last episode (Jul 29), Asif Iqbal said: Now I am running these processes for my mysql serever /bin/sh /usr/local/mysql/bin/safe_mysqld --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/webrt.pid /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld --defaults-extra-file=/usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/data/my.cnf --basedir=/usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/webrt.pid Now on the same logic if I run 4 pair ( the above two processes ) will it imporve my mysql query performance done through my DBIx::SeacrhBuilder ? No; the server will use as many CPUs as you have automatically. To take advantage of multiple CPUs you need multiple clients working in parallel. Oracle can split complicated queries into sections and let different processors work on each part (Parallel Query option), but mysql will only use one processor per query. -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb data file of different size
I just decided to use my.cnf and bumped into this error message 030729 22:04:22 mysqld started InnoDB: Error: data file /usr/local/mysql/data/ibdata1 is of a different size InnoDB: 81024 pages (rounded down to MB) InnoDB: than specified in the .cnf file 16384 pages! InnoDB: Could not open data files 030729 22:04:23 Can't init databases 030729 22:04:23 Aborting 030729 22:04:23 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030729 22:04:23 /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld: Shutdown Complete 030729 22:04:23 mysqld ended I am assuming I need to change values in the following line taken from my.cnf file innodb_data_file_path = ibdata1:10M;ibdata2:10M:autoextend Please help Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bottleneck
How do I debug my live mysql query on a specific database to find the query time, memory usage, etc. ? Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is a good benchmark?
Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any suggestion on how to improve it ? On Wed, 23 Jul 2003, John May wrote: Xserve 1ghz - 1.08 sec G3 333mhz - 2.78 sec - John On my p4 2gig mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (0.86 sec) -Original Message- From: Jake Johnson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 8:34 AM To: Mysql Subject: What is a good benchmark? I ran this benchmark on my pIII 500 and was wondering what everyone else was getting? mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (2.59 sec) Regards, Jake Johnson [EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access Denied for User: root@127.0.0.1(Using password: NO)
You can try one or the other mysql -h localhost -u root -ppasswd db or mysql -h `uname -n` -u root -ppasswd db in solaris On Mon, 30 Jun 2003, Twibell, Cory L wrote: Did you flush the privileges after you gave root a password? The error you get is a result of not using a password to login. -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 6:05 PM To: [EMAIL PROTECTED] Subject: Access Denied for User: [EMAIL PROTECTED](Using password: NO) Please somebody help me. I have installed MySql 4.0.13 and it works up until I try to apply security settings to the root user at localhost. I see the mysql database and I check the privileges and there is no password for the roo user. But when I assign a password to make it secure, I can run phpmyadmin, but I cannot get into the database anymore. All I get is: MySql Said: Access Denied for user: [EMAIL PROTECTED] (Using password: NO) This situation has happened 3 times and I had to reinstall MySql and start from scratch. Can someone please point me in the right direction, this is my first try and it has been quite a serious learning experience. Thank you in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql database dump
Can I dump a database while the database is running in mysql ? -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql query output get wrapped
mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql query output get wrapped
mysql select * from Tickets limit 1 \g ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.01 sec) That did not help . I not \G is not the one On Tue, 3 Jun 2003, Sagar, Sanjeev wrote: Try -E option or put \g in end of your sql statement like below Mysql -u id -ppasswd -E -e show innodb status OR Mysql -u id -ppasswd -e show innodb status \g I am not sure it is little g or capital G. Try both way. -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: mysql query output get wrapped mysql select * from Tickets limit 1; ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+- --+-+---+--+--+- ---+--+-++-+ -+--+---+-+-+--- --+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
something like this ++-+---++++---+---+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+---+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+---+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
Did not work prompt echo select * from Tickets limit 1; | mysql mydb res prompt cat res id EffectiveId Queue TypeIssueStatement Resolution Owner Subject InitialPriority FinalPriority PriorityStatus TimeWorked TimeLeftToldStarts Started Due Resolved LastUpdatedBy LastUpdated Creator Created Disabled 5 5 4 ticket NULLNULL16 RE: phonebook 10 80 10 resolved0 NULL1970-01-01 00:00:00 NULLNULL1970-01-01 00:00:00 NULL1 2001-04-17 18:38:02 1 2001-04-17 18:26:46 0 Still wrapped :-) On Tue, 3 Jun 2003, Thomas Spahni wrote: Asif, try this: prompt echo select * from Tickets limit 1; | mysql mydb result.file You get everything in ONE line. Cheers, Thomas On Tue, 3 Jun 2003, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
I did that before with a switch , just forgot On Tue, 3 Jun 2003, Cal Evans wrote: use a smaller font. This is really a function of your shell or client, not MySQL. humbly, =C= * Cal Evans * http://www.christianperformer.com * Stay plugged into your audience * The measure of a programmer is not the number of lines of code he writes but the number of lines he does not have to write. * - Original Message - From: Asif Iqbal [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 03, 2003 10:57 AM Subject: Re: mysql query output get wrapped something like this ++-+---++++---+- --+-+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | ++-+---++++---+- --+-+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 | ++-+---++++---+- --+-+ *unwrapped* On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:39 -0400 6/3/03, Asif Iqbal wrote: I want the columns unwrapped to make it easy to read. Sorry if I confused you earlier I still don't know what you mean. What would this output *look like*? Please show an example. On Tue, 3 Jun 2003, Paul DuBois wrote: At 11:27 -0400 6/3/03, Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ | 5 | 5 | 4 | ticket | NULL | NULL | 16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+ ---+-+---+--+--+ +--+-++-+--- --+--+---+-+-+-- ---+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally What would that look like? I'm having trouble understanding how your request isn't self-contradictory (it seems you want vertical output, but horizontal?). Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place
Re: mysql query output get wrapped
That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
I am on solaris 2.8 sparc. You less works fine if I use \G switch. But I want (as you explained) list of column names in one line in first row and the results are thereafter in single rows instead of getting wrapped. Like I said wish I remebered the swithc for that On Tue, 3 Jun 2003, Chris Tucker wrote: What platform are you on? If you're on windows you probably won't have less installed, in which case you'll need to either (a) install it or (b) use a different pager (not sure what you'll have with windows, you'd have to independently research it). I'm assuming that what you want is to have the list of column names as your first row, then each row thereafter on a single line in your display window (i.e. no wrapping of those lines on display). If you need to get column names into the output for each cell, then you'll need to do some post-processing of your own. Chris Asif Iqbal wrote: That works with \G switch , but without the \G switch Istill get it wrapped wish I can remeber the switch On Tue, 3 Jun 2003, Chris Tucker wrote: The pager option can be used to accomplish this (if you don't mind having things running through, e.g., less). When I need to do this I just do: mysql \P less -S mysql my query here You'll get unwrapped output (the -S option to less tells it to truncate rather than wrap over-long lines). You can use anything you like as your pager (as far as I can tell): all it has to do is take some input on the input stream and write its output out to the output stream. It is up to the pager how it does this: in the example of less it will paginate and not wrap. Other pagers can be used to achieve different results, processing the stream however you like, based on your needs. Cheers, Chris Asif Iqbal wrote: mysql select * from Tickets limit 1; ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner | Subject | InitialPriority | FinalPriority | Priority | Status | TimeWorked | TimeLeft | Told| Starts | Started | Due | Resolved | LastUpdatedBy | LastUpdated | Creator | Created | Disabled | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ | 5 | 5 | 4 | ticket | NULL | NULL |16 | RE: phonebook | 10 |80 | 10 | resolved | 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | 2001-04-17 18:26:46 |0 | ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ 1 row in set (0.00 sec) Is there a way I can get the output unwrapped something like this mysql select * from Tickets limit 1 \G *** 1. row *** id: 5 EffectiveId: 5 Queue: 4 Type: ticket IssueStatement: NULL Resolution: NULL Owner: 16 Subject: RE: phonebook InitialPriority: 10 FinalPriority: 80 Priority: 10 Status: resolved TimeWorked: 0 TimeLeft: NULL Told: 1970-01-01 00:00:00 Starts: NULL Started: NULL Due: 1970-01-01 00:00:00 Resolved: NULL LastUpdatedBy: 1 LastUpdated: 2001-04-17 18:38:02 Creator: 1 Created: 2001-04-17 18:26:46 Disabled: 0 1 row in set (0.00 sec) Except I want it Horizontally Thanks Asif -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
