Importing data
Dear friends, I import data to my table,from CSV file.CSV file has emails in each row. While importing, A window pops up stating number of rows imported, however when I see the table, nothing has been imported. Version of mysql, gui and structure of table has been pasted. Any guidance with regard to this scenerio and issue, please. Thank you. -- Mysql version: 4.0.17-nt Using SQLyog as gui ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | NO| int(5) unsigned | | PRI | NULL| auto_increment | | WEBADDRESS | varchar(50) | YES | | NULL|| | ADDRESS | varchar(200)| YES | | NULL|| | FIRSTNAME |varchar(240)| YES | | NULL| | | LASTNAME | varchar(240)| YES | | NULL|| | EMAIL | varchar(23) | | | | | | REMARKS | varchar(240)| YES | | NULL|| | PHONE | varchar(240)| YES | | NULL| | | FAX| varchar(240)| YES | | NULL| | | EMAIL2 | varchar(240)| YES | | NULL|| | STATE| varchar(240)| YES | | NULL|| | CITY | varchar(240)| YES | | NULL| | | COUNTRY| varchar(240)| YES | | NULL|| | ZIP| varchar(240)| YES | | NULL|| | POSTALCODE | varchar(240)| YES | | NULL|| | TITLE | varchar(240)| YES | | NULL|| | SALUTATION | varchar(240)| YES | | NULL|| | CUSTOM3| varchar(240)| YES | | NULL|| | CUSTOM4| varchar(240)| YES | | NULL|| | CUSTOM5| varchar(240)| YES | | NULL|| | CUSTOM6| varchar(240)| YES | | NULL|| | PRIMARYKEY | int(6) | | PRI | 0 || | COMPANY| varchar(29) | YES | | NULL|| ++-+--+-+-++ 23 rows in set (0.00 sec)
Weird problem for TIMESTAMP feild
Hi All, I am getting very weired problem after shifting our server. here is the details:- I have a table with two columns 1-TimeStamp timestamp(14) 2- SID bigint(20) when inserting _INSERT INTO _/_TABLENAME values (1258975462,125987)_ results in /_INSERT INTO _/_TABLENAME values (00,125987)_ Timestamp entry gets zeros. Any idea? whats happening Regards, *TM* /
RE: IMPORTING
Do you mean SQLYog? If so, this is a MySQL GUI - Info. at: http://www.webyog.com/sqlyog/index.php John R. Porter I.T. Services University of Strathclyde Faculty of Education 76 Southbrae Drive Glasgow G13 1PP e-mail: [EMAIL PROTECTED] Tel. 0141 950 3289 -Original Message- From: Robert J Taylor [mailto:[EMAIL PROTECTED] Sent: 23 July 2004 17:53 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: IMPORTING On Friday 23 July 2004 03:02 pm, you wrote: sqlog program What is sqlog program? Google shows me nothing familiar: http://www.google.com/search?q=sqlog+program You need to provide more information if you want help. How do you run this? What OS? What options are you selecting? How do you connect the sqlog program to MySQL? -- 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: Errcode: 27
Hi, I'm stuck on this really badly. Can anyone help me out please? Thanks, JS. I tried running the create index again using --verbose but this is all I got: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STLd39Et' (Errcode: 27) -- CREATE INDEX timeindex ON internet_usage (uid,time) -- Bye Can anyone tell me the specifics of what happens during the create index? I can see that the table being indexed gets copied but what happens in the code after that? Thanks alot. JS. Hi, I'm trying to run the following SQL (on mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc): CREATE INDEX timeindex ON internet_usage (time); CREATE INDEX urlindex ON internet_usage (urlid); but keep running into the following error: Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27) $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The create index gets as far as actually copying the 18GB internet_usage table, and then does some processing (not sure what), then falls over with the error 27. The message seems to be misleading because all the ulimit values are unlimited. Also the filesystem is large-file enabled as shown below: $ pwd /proxydb/mysql/data/proxy_logs $ ls -l total 58124344 -rw-rw 1 mysqlmysql 0 Jul 23 11:08 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jul 23 11:08 bulk_table.MYI -rw-rw 1 mysqlmysql 8970 Jul 09 14:05 bulk_table.frm -rw-rw 1 mysqlmysql18550068032 Jul 23 10:45 internet_usage.MYD -rw-rw 1 mysqlmysql5150286848 Jul 23 11:08 internet_usage.MYI -rw-rw 1 mysqlmysql 8856 Jul 09 14:05 internet_usage.frm -rw-rw 1 mysqlmysql380 Jul 09 14:08 url_actions.MYD File system name/proxydb NEW mount point[/proxydb] SIZE of file system (in 512-byte blocks) [131203072] Mount GROUP[] Mount AUTOMATICALLY at system restart? yes + PERMISSIONS read/write + Mount OPTIONS [] + Start Disk Accounting? no + Fragment Size (bytes) 4096 Number of bytes per inode 4096 Compression algorithm no Large File Enabled true Allocation Group Size (MBytes) 64 I'm not sure what else could be wrong. I've tried creating another table with the new keys and selecting * from internet_usage table but I still got the same error there. Here's some miscellaneous info which might be useful. mysql show indexes from internet_usage; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | internet_usage | 1 | uid |1 | uid | A | 23039 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.07 sec) ++++---++-+-+--+---++-+-+-+-+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++-+-+--+---++-+-+-+-+-+ | bulk_table | MyISAM | Dynamic| 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL| | | | internet_usage | MyISAM | Fixed | 579689626 | 32 | 18550068032 |137438953471 | 5150286848 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 10:45:04 | 2004-07-09 14:13:20 | max_rows=4294967295 | | | url_actions| MyISAM | Dynamic|18
Replication script pb
Hi, We have an environnment with a master and a slave. We run a script every hour (on the master only) that does something like this to backup some parameters : insert into backup(NULL,NULL) VALUES(NULL,NOW()) SET @backup_id = @@LAST_INSERT_ID INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param) It works perfectly on the master but the slave stop due to duplicate entries. the @backup_id do not pass the replication... any solution to have that script working ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] SQL Data Wizard 1.3 released
Hello, SQL Maestro Group is happy to announce the release of SQL Data Wizard 1.3 - a powerful Windows GUI utility for MySQL data export, import, database conversion and PHP script generation. The trial version of the software is available at http://www.sqlmaestro.com/products/mywizard/download.html. Starting from this version, SQL Data Wizard provides an ability to connect to a remote MySQL server through the Secure SHell (SSH) tunnel, which is considered as one of the most secure ways to communicate with remote servers over the Internet. Also several other features were added, as well as a few minor bugs were fixed. The full news article can be read at http://www.sqlmaestro.com/news.html?id=15. SQL Data Wizard is a powerful Windows GUI utility for managing your MySQL data. It provides you with a number of easy-to-use wizards for performing the required data manipulation easily and quickly. SQL Data Wizard allows you to generate PHP scripts for the selected tables and queries, convert any ADO-compatible database to the MySQL database, export data from MySQL tables and queries to most popular formats, and import data into the tables. Sincerely yours, SQL Maestro Group http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weird problem for TIMESTAMP feild
Invalid entry format for the timestamp field. Did this work before? -Original Message- From: Tariq Murtaza To: [EMAIL PROTECTED] Sent: 7/26/04 4:43 AM Subject: Weird problem for TIMESTAMP feild Hi All, I am getting very weired problem after shifting our server. here is the details:- I have a table with two columns 1-TimeStamp timestamp(14) 2- SID bigint(20) when inserting _INSERT INTO _/_TABLENAME values (1258975462,125987)_ results in /_INSERT INTO _/_TABLENAME values (00,125987)_ Timestamp entry gets zeros. Any idea? whats happening Regards, *TM* / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String substitution in MYSQL
I am looking for a string substitution command in MYSQL and was wondering if anyone can help me. Searching the archives was not fruitful. Here is an example : CREATE TABLE tbl (id INT(2), names VARCHAR(20)); INSERT INTO tbl VALUES (1, 'aaa'); INSERT INTO tbl VALUES (2, 'bbb; aaa; ccc'); SELECT * FROM tbl; +--+---+ | id | names | +--+---+ |1 | aaa | |2 | bbb; aaa; ccc | +--+---+ Now, suppose that I want to change all 'aaa' into 'zzz'. The following command works for id 1 but not id 2. UPDATE tbl SET names='zzz' WHERE names='aaa'; QUESTION : How do I change 'bbb; aaa; ccc' - 'bbb; zzz; ccc' ? These do not work either : UPDATE tbl SET names='zzz' WHERE names like %aaa%; UPDATE tbl SET names=%zzz% WHERE names like %aaa%; I could do this in Perl but prefer to do it in MYSQL for code brevity and speed. Thank you. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow server - any idea?
How often do you optimize/analyze your tables? Have you checked the index cardinality? What does an explain plan show? -Original Message- From: Julien Lavigne du Cadet To: [EMAIL PROTECTED] Sent: 7/25/04 4:26 PM Subject: Slow server - any idea? Hi eveybody, I've got problems since a few weeks with my mysql server. There are a lot of slow queries (about 1200 in less than 48 hours), even some that should absolutely not be slow like this one which is performing on a HEAP table : SELECT * FROM vb3_session WHERE sessionhash = '31d429cc3820a8bb141733de2cd306ba' AND lastactivity 1090778091 AND host = '65.50.5.140' AND idhash = '385f8c8da967afdd86399fb72d05'; I'm running a p4 2,4. 1Go RAM, DD IDE 80Go under FreeBSD and I've got the 4.0.20 version installed (anyway I tried to downgrade to 4.0.18 and it didn't changed anything). There are about 20 sites and a vb3 forum with 200 to 300 visitors at once. The server doesn't seem to consume much cpu as shown : 42992 mysql 2 0 226M 66256K poll 87:38 4.83% 4.83% mysqld Here is my config file : [mysqld] datadir=/var/db/mysql socket=/tmp/mysql.sock skip-locking skip-innodb query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=500 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_cache_size=64 key_buffer=150M join_buffer=1M max_allowed_packet=2M table_cache=768 record_buffer=1M sort_buffer_size=1M read_buffer_size=1M #read_rnd_buffer_size=768K max_connect_errors=10 # Try number of CPU's*2 for thread_concurrency thread_concurrency=2 myisam_sort_buffer_size=64M #log-bin server-id=1 log_slow_queries=/var/log/slow-queries.log long_query_time=1 [mysql.server] user=mysql basedir=/usr/local [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/db/mysql/srv1.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout Here is the status : Created tmp disk tables 706 Created tmp tables 162301 Created tmp files 138 Delayed insert threads 0 Delayed writes 0 Delayed errors 0 Flush commands 1 Handler commit 0 Handler delete 62700 Handler read first 10465 Handler read key 53413365 Handler read next 20806399 Handler read prev 8431183 Handler read rnd 12619723 Handler read rnd next 670650172 Handler rollback 0 Handler update 2921336 Handler write 23073711 Key blocks used 108984 Key read requests 135302387 Key reads 107438 Key write requests 214624 Key writes 184195 Max used connections 41 Not flushed key blocks 0 Not flushed delayed rows 0 Open tables 768 Open files 1321 Open streams 0 Opened tables 9238 Qcache queries in cache 4900 Qcache inserts 954259 Qcache hits 1556783 Qcache lowmem prunes 143367 Qcache not cached 120513 Qcache free memory 7149624 Qcache free blocks 2438 Qcache total blocks 14367 Rpl status NULL Select full join 739 Select full range join 63 Select range 135410 Select range check 0 Select scan 415678 Slave open temp tables 0 Slave running OFF Slow launch threads 0 Slow queries 1280 Sort merge passes 69 Sort range 128597 Sort rows 13431446 Sort scan 200597 Table locks immediate 2514328 Table locks waited 7966 Threads cached 39 Threads created 42 Threads connected 3 Threads running 1 I also have got this kind of messages in mysqld.log : 040725 12:56:47 Aborted connection 250044 to db: 'mondespe_lineage2' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 12:58:40 Aborted connection 250285 to db: 'animelan' user: 'animelan' host: `localhost' (Got timeout reading communication packets) 040725 13:09:59 Aborted connection 251722 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251896 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251891 to db: 'vb3_fansite' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:11:06 Aborted connection 251914 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:17:37 Aborted connection 252812 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:30:18 Aborted connection 254752 to db: 'mmoblogs' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:30:21 Aborted connection 254750 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:32:37 Aborted connection 255067 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) Any idea to solve the problem is welcome, Thanks to all, Julien. -- MySQL General Mailing List For list archives:
RE: String substitution in MYSQL
Try using the REPLACE command. -Original Message- From: Adaikalavan Ramasamy To: [EMAIL PROTECTED] Sent: 7/26/04 8:04 AM Subject: String substitution in MYSQL I am looking for a string substitution command in MYSQL and was wondering if anyone can help me. Searching the archives was not fruitful. Here is an example : CREATE TABLE tbl (id INT(2), names VARCHAR(20)); INSERT INTO tbl VALUES (1, 'aaa'); INSERT INTO tbl VALUES (2, 'bbb; aaa; ccc'); SELECT * FROM tbl; +--+---+ | id | names | +--+---+ |1 | aaa | |2 | bbb; aaa; ccc | +--+---+ Now, suppose that I want to change all 'aaa' into 'zzz'. The following command works for id 1 but not id 2. UPDATE tbl SET names='zzz' WHERE names='aaa'; QUESTION : How do I change 'bbb; aaa; ccc' - 'bbb; zzz; ccc' ? These do not work either : UPDATE tbl SET names='zzz' WHERE names like %aaa%; UPDATE tbl SET names=%zzz% WHERE names like %aaa%; I could do this in Perl but prefer to do it in MYSQL for code brevity and speed. Thank you. Regards, -- 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]
using mysql in commercial software
( if this is a FAQ or better asked elsewhere, can someone point me in the right direction? ) In general, what are the rules for using MySql as the database in a commercial software package? I am writing faxing software in windows. Currently it is just used by the client who paid me to write a custom package for their use. But I would also like to market the software to the general public. First question has to do with using MySql in the custom fax package used by my client. The client can install MySql for no charge on any or all of the PCs in their organization? My other question has to do with embedding MySql into my code when I sell it copy protected, closed source to any other clients that are willing to pay for it. Is that permitted? I need to make a living from my work. MySql looks to be exactly what I am looking for as the database in my windows c++ code. I hope I am able to use it! thanks, Steve Richter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication script pb
Have you tried using the last insert id function instead? SET @backup_id = last_insert_id() -Original Message- From: Philippe Poelvoorde To: [EMAIL PROTECTED] Sent: 7/26/04 7:03 AM Subject: Replication script pb Hi, We have an environnment with a master and a slave. We run a script every hour (on the master only) that does something like this to backup some parameters : insert into backup(NULL,NULL) VALUES(NULL,NOW()) SET @backup_id = @@LAST_INSERT_ID INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param) It works perfectly on the master but the slave stop due to duplicate entries. the @backup_id do not pass the replication... any solution to have that script working ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird problem for TIMESTAMP feild
According to the docs (http://dev.mysql.com/doc/mysql/en/DATETIME.html) The number 1258975462 equates to the date 1258-97-54 62:00:00 which is not a valid date. That's why you get the zero date value. That number could also equate to a unix_timestamp() value for the date 2009-11-23 06:24:22 select from_unixtime(1258975462) +---+ | from_unixtime(1258975462) | +---+ | 2009-11-23 06:24:22 | +---+ 1 row in set (0.05 sec) (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) What date did it represent in your data? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tariq Murtaza [EMAIL PROTECTED] wrote on 07/26/2004 05:43:04 AM: Hi All, I am getting very weired problem after shifting our server. here is the details:- I have a table with two columns 1-TimeStamp timestamp(14) 2- SID bigint(20) when inserting _INSERT INTO _/_TABLENAME values (1258975462,125987)_ results in /_INSERT INTO _/_TABLENAME values (00,125987)_ Timestamp entry gets zeros. Any idea? whats happening Regards, *TM* /
RE: String substitution in MYSQL
Thank you ! Works like a charm. Here is the successful syntax UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz'); I found this link to be quite useful http://www.jayallen.org/journey/2004/04/how_to_perform_a_mysql_search_and_replace Regards, Adai. On Mon, 2004-07-26 at 14:07, Victor Pendleton wrote: Try using the REPLACE command. -Original Message- From: Adaikalavan Ramasamy To: [EMAIL PROTECTED] Sent: 7/26/04 8:04 AM Subject: String substitution in MYSQL I am looking for a string substitution command in MYSQL and was wondering if anyone can help me. Searching the archives was not fruitful. Here is an example : CREATE TABLE tbl (id INT(2), names VARCHAR(20)); INSERT INTO tbl VALUES (1, 'aaa'); INSERT INTO tbl VALUES (2, 'bbb; aaa; ccc'); SELECT * FROM tbl; +--+---+ | id | names | +--+---+ |1 | aaa | |2 | bbb; aaa; ccc | +--+---+ Now, suppose that I want to change all 'aaa' into 'zzz'. The following command works for id 1 but not id 2. UPDATE tbl SET names='zzz' WHERE names='aaa'; QUESTION : How do I change 'bbb; aaa; ccc' - 'bbb; zzz; ccc' ? These do not work either : UPDATE tbl SET names='zzz' WHERE names like %aaa%; UPDATE tbl SET names=%zzz% WHERE names like %aaa%; I could do this in Perl but prefer to do it in MYSQL for code brevity and speed. Thank you. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb assertion failure after binary backup-restore
Hi List, I figured out the fix for it. I need to do a sync and wait for the sync to get over and take a backup. Thanks, Sp.Raja Original Message From: Sp.Raja [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Fri, Jul-23-2004 8:03 PM Subject: Re: Innodb assertion failure after binary backup-restore In some cases mysql crashes while restore is trying to destroy persistent databases and mysql.err says 000121 21:02:23 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 867418 InnoDB: Doing recovery: scanned up to log sequence number 0 867418 InnoDB: Page directory corruption: supremum not pointed to 000121 21:02:23 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex (all zeros and dots in ascii) ;InnoDB: End of page dump 000121 21:02:23 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page directory corruption: supremum not pointed to 000121 21:02:23 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex ...(all zeros and dots in ascii) ;InnoDB: End of page dump 000121 21:02:23 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer a1027ff8 InnoDB: buf pool start is at 2084, number of pages 512 000121 21:02:23 InnoDB: Assertion failure in thread 43 in file include/buf0buf.ic line 284 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=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=20 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 29183 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Writing a core file 000121 21:02:24 mysqld ended Thanks, Sp.Raja Original Message From: Sp.Raja [EMAIL PROTECTED] To: '[EMAIL PROTECTED] ' [EMAIL PROTECTED] Date: Fri, Jul-23-2004 7:13 PM Subject: Innodb assertion failure after binary backup-restore Hi List, We are using 4.0.15a MySQL. We need to backup database at times and we employ the following method FLUSH TABLES WITH READ LOCK tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1 UNLOCK TABLES and restore is reverse. We cannot overwrite all databases, some of them needs to be unchanged across backup and restores called persistent databases (just 1% of full data) so we employ the following mysqldump on persistent databases stop mysql cd /usr/local/mysql/data tar -zxf backup.tar.gz start mysql destroy persistent databases run mysqldump sql files to get database back to shape This method works fine expect for the case where backup is taken immediately after table creation and population. When we start the mysql after restore of this backup. It comes up, but mysql connections to it asking for persistent database destroy hangs. When I do a processlist I get ++--+---++-+--+-+--+ | Id | User | Host | db | Command | Time | State | Info | ++--+---++-+--+-+--+ | 1 | root | localhost || Query | 55 | Waiting on cond | drop database persistent1| | 4 | root | localhost || Query | 0| | show processlist | ++--+---++-+--+-+--+ When I open up another mysql client and query some of the tables, they also hang and processlist at that time shows ++--+---+-+-+--+-+-+ | Id | User | Host | db | Command | Time | State | Info| ++--+---+-+-+--+-+-+ | 1 | root | localhost | |
nested sets
Hello, I have a question related to nested sets: On my website a user should be able to create subusers and give them the rights to add/change/delete subsites of the users website. If I remove one or more rights to a user all of its subusers should have the same restrictions as the user. If I grant the rights to the user back all subusers should get their orginal rights Example root - User 1 a c d - User 2 a c d -- User 4 a c -- User 5 a c d --- User 7a --- User 8a -- User 6 a c d - User 3 a c a = add, c = change, d = delete Now I like to give User 2 the rights for adding (a) only: User should see their rights as root - User 1 a c d - User 2 a -- User 4 a c -- User 5 a c d --- User 7a --- User 8a -- User 6 a c d - User 3 a c but/and effective rights should be root - User 1 a c d - User 2 a -- User 4 a -- User 5 a --- User 7a --- User 8a -- User 6 a - User 3 a c If I give back full rights to User 2 all should be like at the beginning Any help is welcome! Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
If you go through the online manual of mysql, it points out that mysql can be used under gpl (then you have to be giving away the source code for your software with mysql) or else you have to purchase mysql commercially. In your case you sure need to buy it. On Mon, 2004-07-26 at 08:11, Steve Richter wrote: ( if this is a FAQ or better asked elsewhere, can someone point me in the right direction? ) In general, what are the rules for using MySql as the database in a commercial software package? I am writing faxing software in windows. Currently it is just used by the client who paid me to write a custom package for their use. But I would also like to market the software to the general public. First question has to do with using MySql in the custom fax package used by my client. The client can install MySql for no charge on any or all of the PCs in their organization? My other question has to do with embedding MySql into my code when I sell it copy protected, closed source to any other clients that are willing to pay for it. Is that permitted? I need to make a living from my work. MySql looks to be exactly what I am looking for as the database in my windows c++ code. I hope I am able to use it! thanks, Steve Richter -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to deal with a string of categories
Hi, I'd like to know how you guys write SQL command for this problem. there are some categories which I give them A, B, ... and I have a string field (catstr) with 10 characters in mytable to keep selected categories in a record. when user selects C, G, K, I keep CGK in catstr field. categories has been defined in cattable. now I want to write a query to retreive these catergories. query will have 10 columns (catstr is 10 characters) with description of categories. it means user doesn't see C, or G. I tried to open 10 times cattable with different alias and make the query but it looks like MySQL doesn't like it and doesn't let me open the same table more than once (even with different alias). I can create 10 temporary tables and solve this problem and it doesn't look good. or I can create my query with 10 CASE commands with I create them base on cattable. how you guys solve this problem? maybe there is a better solution which I don't know and very neat can solve this problem. thanks, Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
Perhaps I misunderstood the license but it would seem to me that for the original use, a commercial license is *not* needed, since anyone can privately use MySQL for their own private commercial use without a license (I think). From what I understand the only need for a commercial license comes when you're selling a product to the general public which relies on MySQL (or redistributing MySQL, etc). Please correct me if I'm wrong... - Original Message - From: Aman Raheja [EMAIL PROTECTED] To: Steve Richter [EMAIL PROTECTED] Cc: chat. mysql. [EMAIL PROTECTED] Sent: Monday, July 26, 2004 5:49 PM Subject: Re: using mysql in commercial software If you go through the online manual of mysql, it points out that mysql can be used under gpl (then you have to be giving away the source code for your software with mysql) or else you have to purchase mysql commercially. In your case you sure need to buy it. On Mon, 2004-07-26 at 08:11, Steve Richter wrote: ( if this is a FAQ or better asked elsewhere, can someone point me in the right direction? ) In general, what are the rules for using MySql as the database in a commercial software package? I am writing faxing software in windows. Currently it is just used by the client who paid me to write a custom package for their use. But I would also like to market the software to the general public. First question has to do with using MySql in the custom fax package used by my client. The client can install MySql for no charge on any or all of the PCs in their organization? My other question has to do with embedding MySql into my code when I sell it copy protected, closed source to any other clients that are willing to pay for it. Is that permitted? I need to make a living from my work. MySql looks to be exactly what I am looking for as the database in my windows c++ code. I hope I am able to use it! thanks, Steve Richter -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.com Brainbench Certified Linux (RedHat 9) Admin -- 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: table_cache size for large value in opened_tables
Hi Terence, Hi all, My system reports: Open_tables 512 Opened_tables 24,429 The docs say that if the latter is high I should increase the table cache size. (currently at 512) Looks like you should do that. How does one decide what size to increase it to? And is there a problem with one of the applications that's making this figure so high? Or is this normal behaviour? Whether this is a problem or not really depend on you application and how busy it is. Generally you want to avoid reopening tables on a busy server. There are two methods for knowing how to increase this number. 1) count how many tables you have in your databases and set this number larger. 2) Your app will probably not use all tables all the time so experiment with larger numbers. Remember to make sure the maximum open files limit of your OS and user that runs mysql is not exceeded. Run limit to determine what this is and make sure that you check for the user that runs MySQL. Hope this helps, Ken OS: RH9 Dual 2.4 Xeon 1 GIG RAM (btw, this kind of question i linked to my previous post for a performance tuning guide) Thanks! Here's my status Variable_name,Value, Aborted_clients,801, Aborted_connects,14, Bytes_received,1195564158, Bytes_sent,1491507399, Com_admin_commands,42960, Com_alter_table,317, Com_alter_db,0, Com_analyze,0, Com_backup_table,0, Com_begin,0, Com_change_db,1654089, Com_change_master,0, Com_check,0, Com_commit,59, Com_create_db,9, Com_create_function,0, Com_create_index,0, Com_create_table,465, Com_delete,100132, Com_delete_multi,21, Com_do,0, Com_drop_db,2, Com_drop_function,0, Com_drop_index,0, Com_drop_table,549, Com_flush,13, Com_grant,1, Com_ha_close,0, Com_ha_open,0, Com_ha_read,0, Com_help,0, Com_insert,6887163, Com_insert_select,48, Com_kill,0, Com_load,14, Com_load_master_data,0, Com_load_master_table,0, Com_lock_tables,15, Com_optimize,42, Com_purge,0, Com_purge_before_date,0, Com_rename_table,0, Com_repair,42, Com_replace,7873, Com_replace_select,0, Com_reset,0, Com_restore_table,0, Com_revoke,0, Com_rollback,17, Com_select,3152624, Com_set_option,27303, Com_show_binlog_events,0, Com_show_binlogs,0, Com_show_charsets,0, Com_show_column_types,0, Com_show_create_table,456, Com_show_create_db,0, Com_show_databases,89, Com_show_errors,0, Com_show_fields,1775, Com_show_grants,0, Com_show_keys,1643, Com_show_logs,0, Com_show_master_status,0, Com_show_new_master,0, Com_show_open_tables,0, Com_show_privileges,0, Com_show_processlist,73, Com_show_slave_hosts,0, Com_show_slave_status,0, Com_show_status,240, Com_show_innodb_status,0, Com_show_tables,2846, Com_show_table_types,0, Com_show_variables,96, Com_show_warnings,0, Com_slave_start,0, Com_slave_stop,0, Com_truncate,0, Com_unlock_tables,15, Com_update,5146761, Com_update_multi,0, Connections,108272, Created_tmp_disk_tables,71315, Created_tmp_tables,725937, Created_tmp_files,0, Delayed_insert_threads,0, Delayed_writes,0, Delayed_errors,0, Flush_commands,1, Handler_commit,219, Handler_delete,941394, Handler_read_first,483459, Handler_read_key,102941817, Handler_read_next,213625710, Handler_read_prev,448018710, Handler_read_rnd,3049520, Handler_read_rnd_next,222363654, Handler_rollback,16312, Handler_update,37894671, Handler_write,27010243, Key_blocks_used,353833, Key_read_requests,341367134, Key_reads,1172755, Key_write_requests,47942190, Key_writes,38265419, Max_used_connections,106, Not_flushed_key_blocks,0, Not_flushed_delayed_rows,0, Open_tables,512, Open_files,873, Open_streams,0, Opened_tables,30492, Questions,25298875, Qcache_queries_in_cache,23808, Qcache_inserts,2368548, Qcache_hits,8206562, Qcache_lowmem_prunes,29375, Qcache_not_cached,784005, Qcache_free_memory,40477464, Qcache_free_blocks,2311, Qcache_total_blocks,50436, Rpl_status,NULL, Select_full_join,12285, Select_full_range_join,16, Select_range,70254, Select_range_check,1, Select_scan,1618573, Slave_open_temp_tables,0, Slave_running,OFF, Slow_launch_threads,0, Slow_queries,201, Sort_merge_passes,0, Sort_range,132898, Sort_rows,2993422, Sort_scan,576536, Table_locks_immediate,17191738, Table_locks_waited,70304, Threads_cached,4, Threads_created,12439, Threads_connected,18, Threads_running,1, Uptime,1186406, Variable_name,Value, back_log,50, basedir,/usr/local/mysql/, binlog_cache_size,32768, bulk_insert_buffer_size,8388608, character_set,latin1, character_sets,big5 latin2_czech_ci dec8_swedish_ci cp850_general_ci latin1_german1_ci hp8_english_ci koi8r_general_ci latin1_swedish_ci latin2_general_ci swe7_swedish_ci ascii_general_ci ujis sjis cp1251_bulgarian_ci latin1_danish_ci hebrew tis620 euckr latin7_estonian_ci latin2_hungarian_ci koi8u_general_ci cp1251_ukrainian_ci gb2312 greek cp1250_general_ci latin2_croatian_ci gbk cp1257_lithuanian_ci latin5_turkish_ci latin1_german2_ci armscii8_general_ci utf8
Re: how to deal with a string of categories
Hi, I'd like to know how you guys write SQL command for this problem. there are some categories which I give them A, B, ... and I have a string field (catstr) with 10 characters in mytable to keep selected categories in a record. when user selects C, G, K, I keep CGK in catstr field. categories has been defined in cattable. now I want to write a query to retreive these catergories. query will have 10 columns (catstr is 10 characters) with description of categories. it means user doesn't see C, or G. I tried to open 10 times cattable with different alias and make the query but it looks like MySQL doesn't like it and doesn't let me open the same table more than once (even with different alias). I can create 10 temporary tables and solve this problem and it doesn't look good. or I can create my query with 10 CASE commands with I create them base on cattable. how you guys solve this problem? maybe there is a better solution which I don't know and very neat can solve this problem. Well, the problems you're encountering might give you an indication that a catstr column isn't the right thing to do... Create a: CATEGORIES table CatID A B C etc... YourTHingiesTable: SomeID 1 2 3 YourThingies_Categories table: SomeID, CatID: 1, A 1, C 2, D 2, A 2, F Create a link in YourThingies_Categories for each category something belongs to. Depending on how you want to display to which categories something belongs, you need to change your queries, but at least its very very easy to query for all objects in a specific category. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nested sets
Hi Though I would like to look up a way to implement what you want in MySQL, one thing was indicative and I wanted to point out. If, say there was a way, you could remove the c and d rights from subsequent users by removing it from User2, I think you are talking about some kind of history retention (or memory engine) if you want everything should be as before when you give the rights c and d to the User2 such that everything is as before (there is no default restoration but as it previously was) - you need to do some programming and need to have another table to store the history as I view it. HTH a bit! On Mon, 2004-07-26 at 09:46, Alexander Newald wrote: Hello, I have a question related to nested sets: On my website a user should be able to create subusers and give them the rights to add/change/delete subsites of the users website. If I remove one or more rights to a user all of its subusers should have the same restrictions as the user. If I grant the rights to the user back all subusers should get their orginal rights Example root - User 1 a c d - User 2 a c d -- User 4 a c -- User 5 a c d --- User 7a --- User 8a -- User 6 a c d - User 3 a c a = add, c = change, d = delete Now I like to give User 2 the rights for adding (a) only: User should see their rights as root - User 1 a c d - User 2 a -- User 4 a c -- User 5 a c d --- User 7a --- User 8a -- User 6 a c d - User 3 a c but/and effective rights should be root - User 1 a c d - User 2 a -- User 4 a -- User 5 a --- User 7a --- User 8a -- User 6 a - User 3 a c If I give back full rights to User 2 all should be like at the beginning Any help is welcome! Alexander Newald -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT if record NOT EXISTS
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Thank you.
Regards, Adai.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to deal with a string of categories
You should never create multi-purpose fields that you will need to use to reference other data, which is what you did here. The database can't use an index because the codes are not in set positions. You need to create a table for linking the categories with mytable, since you are trying to create a one to many relation. Your link table with have at least two fields, mytable_RecordID and catID. You would then do a three table join to get all the data. If you are only displaying one record at a time, you may just want to use your front end (ie. PHP) to link the category names. On Jul 26, 2004, at 10:55 AM, Mojtaba Faridzad wrote: Hi, I'd like to know how you guys write SQL command for this problem. there are some categories which I give them A, B, ... and I have a string field (catstr) with 10 characters in mytable to keep selected categories in a record. when user selects C, G, K, I keep CGK in catstr field. categories has been defined in cattable. now I want to write a query to retreive these catergories. query will have 10 columns (catstr is 10 characters) with description of categories. it means user doesn't see C, or G. I tried to open 10 times cattable with different alias and make the query but it looks like MySQL doesn't like it and doesn't let me open the same table more than once (even with different alias). I can create 10 temporary tables and solve this problem and it doesn't look good. or I can create my query with 10 CASE commands with I create them base on cattable. how you guys solve this problem? maybe there is a better solution which I don't know and very neat can solve this problem. thanks, Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to deal with a string of categories
May I suggest a design change as a solution? I would suggest that you should create a user/category table to replace your catstr field: CREATE TABLE user_category ( user_Id int not null, category_Code char(1) not null UNIQUE (user_Id, category_Code) ) There will be one record in the user_category table for each category that a user belongs to. To see a list of all of your users and to which cateogories each user belongs: SELECT u.user_Name, category_Name FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code or if you wanted a comma-separated list of categories for each person: SELECT u.user_Name, GROUP_CONCAT(category_Name) as categories FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code GROUP BY u.user_Name NOTE: you will have to change the query examples I gave you to match your actual table and field names This does not limit you to having only 10 (or 20 or 30) categories for each person. It also means that creating new categories will not require a change in your database design (changing the size of a column) but only adding or deleting records. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mojtaba Faridzad [EMAIL PROTECTED] wrote on 07/26/2004 10:55:22 AM: Hi, I'd like to know how you guys write SQL command for this problem. there are some categories which I give them A, B, ... and I have a string field (catstr) with 10 characters in mytable to keep selected categories in a record. when user selects C, G, K, I keep CGK in catstr field. categories has been defined in cattable. now I want to write a query to retreive these catergories. query will have 10 columns (catstr is 10 characters) with description of categories. it means user doesn't see C, or G. I tried to open 10 times cattable with different alias and make the query but it looks like MySQL doesn't like it and doesn't let me open the same table more than once (even with different alias). I can create 10 temporary tables and solve this problem and it doesn't look good. or I can create my query with 10 CASE commands with I create them base on cattable. how you guys solve this problem? maybe there is a better solution which I don't know and very neat can solve this problem. thanks, Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy [EMAIL PROTECTED] wrote on 26/07/2004
16:05:23:
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Simply create a UNIQUE index on the fields which you with to be unique.
Add into yoyr table cration the line
UNIQUE (firstname, lastname),
MySQL will then reject any attempt to make that combination non-unique.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TMPDIR on a 4.0.20.
Hi, I'm using MySQL 4.0.20 on a fairly loaded server, running OpenBSD 3.5 (P4 3Gz, 2GB RAM, SCSI U160 36GB disks), and the database itself is around 8GB. There are a couple of large tables (2.000.000 rows), but most of them are small. The data is retrieved intensively, so we can build reports and manage our system. In order to gain performance, I thought that I could map the tmpdir to a MFS directory (512MB). I really think that this would speed things up. Although, I have come to a couple of questions that I was not able to find the answer at MySQL documentation... maybe you guys can help me: 1) How do I know, how much space am I using at the TMPDIR (when it is used, that is)? 2) What would happen in case of space starvation? Is that likely to cause a MySQL crash? Or it would just abort the resource-eater query? Thanks a lot! Best regards, RV Tec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Your model is flawed.
My son and I have the same first and last names.
Therefore, we could not be users on your system.
Adaikalavan Ramasamy wrote:
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
Just do a normal insert, and check to see if it failed.
Then deal with it.
You could also use INSERT IGNORE.
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Thank you.
Regards, Adai.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Thanks Alec ! This works wonderfully.
But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
On Mon, 2004-07-26 at 16:20, [EMAIL PROTECTED] wrote:
Adaikalavan Ramasamy [EMAIL PROTECTED] wrote on 26/07/2004
16:05:23:
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Simply create a UNIQUE index on the fields which you with to be unique.
Add into yoyr table cration the line
UNIQUE (firstname, lastname),
MySQL will then reject any attempt to make that combination non-unique.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ARGH - Mysql won't stop eating RAM and finally it SEGV's
Richard Clarke [EMAIL PROTECTED] wrote: It is the proper binary from mysql.com. mysql-standard-4.1.3-beta-pc-linux-i686.tar.gz I have also filed a bug report for this problem. #4673 There is some extra information in the bug report describing what my application does. Okay, thank you. We'll take care. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
As I understand from the following link http://dev.mysql.com/doc/mysql/en/Using_the_MySQL_software_under_a_commercial_license.html You need to buy a commercial license if the software you are developing is not to be registered under GPL. Aman On Mon, 2004-07-26 at 09:55, Issac Goldstand wrote: Perhaps I misunderstood the license but it would seem to me that for the original use, a commercial license is *not* needed, since anyone can privately use MySQL for their own private commercial use without a license (I think). From what I understand the only need for a commercial license comes when you're selling a product to the general public which relies on MySQL (or redistributing MySQL, etc). Please correct me if I'm wrong... - Original Message - From: Aman Raheja [EMAIL PROTECTED] To: Steve Richter [EMAIL PROTECTED] Cc: chat. mysql. [EMAIL PROTECTED] Sent: Monday, July 26, 2004 5:49 PM Subject: Re: using mysql in commercial software If you go through the online manual of mysql, it points out that mysql can be used under gpl (then you have to be giving away the source code for your software with mysql) or else you have to purchase mysql commercially. In your case you sure need to buy it. On Mon, 2004-07-26 at 08:11, Steve Richter wrote: ( if this is a FAQ or better asked elsewhere, can someone point me in the right direction? ) In general, what are the rules for using MySql as the database in a commercial software package? I am writing faxing software in windows. Currently it is just used by the client who paid me to write a custom package for their use. But I would also like to market the software to the general public. First question has to do with using MySql in the custom fax package used by my client. The client can install MySql for no charge on any or all of the PCs in their organization? My other question has to do with embedding MySql into my code when I sell it copy protected, closed source to any other clients that are willing to pay for it. Is that permitted? I need to make a living from my work. MySql looks to be exactly what I am looking for as the database in my windows c++ code. I hope I am able to use it! thanks, Steve Richter -- -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Splitting data across tables
Andrew Hill [EMAIL PROTECTED] wrote: Perhaps it would be better to insert the timestamp, rather than letting MySQL set it. Sure. This gives consistency in your situation. Or, is there perhaps a way to let MySQL select which table to perform the insert into, based on the time? No. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using mysql in commercial software
-Original Message- From: Michael Abbott [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: RE: using mysql in commercial software This may not be strictly legal, but you could have the end user download MySQL.. to run with your software. exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? -Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: after upgrade unicode characters changed to question marks
Stefan Klopp [EMAIL PROTECTED] wrote: We recently upgraded our mysql server from 3.23 to 4.0.18 and have found that all of our Unicode characters are now being displayed as question marks (?). Anyway this only happens when viewing over the web as when we view via the shell mysql we can see the characters fine. Not a MySQL issue: both versions 3 4 don't manage unicode characters. Only 4.1 does. MySQL 3 4 just store the binary data. Try to debug your web software and first recompile it with .so.12 client libraries (the ones that comes with MySQL 4.0). -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: nested sets
Hello Aman, [..] I think he was talking about user roles. one creates a role and then assigns that role to the users. When one changes the role rights then automatically users' rights change according to the roles they have. The main problem I have to do the rollback. A 2nd table is no option because it might be possible that another users rights get be change before the rights of the first user has been restored. Using +-+-++-+-++++ | node_id | root_id | user | lft | rgt | a | c | d | +-+-++-+-++++ | 1 | 1 | User1 | 1 | 4 | 1 | 0 | 0 | +-+-++-+-++++ | 2 | 1 | User2 | 2 | 3 | 1 | 1 | 1 | +-+-++-+-++++ and using 1 or 0 for the a, c and d col would give the rights to the users. But how to check if the rights of the parent user do not are lower? In the exmaple above how to get 0 for c and d for User2 (because User2 is a subuser of User1)? Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning MySQL for Large Database
Michael Sleman [EMAIL PROTECTED] wrote: key_buffer = 384M Try to enlarge this up to, say, 1G and check it out how that helps. sort_buffer_size = 2M You may want to enlarge this as well. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update on installing mysql on linux
Levi Campbell [EMAIL PROTECTED] wrote: Okay, I got the RPM package installed but when I use rpm -I = mysql-server-4.0.20-0.i386.rpm, I am told that I need several files, = most of which I can't find in the Debian package library. I need the = following files: /usr/bin/perl, sh-utils and sh, where can I get these? Install it like this: rpm --install -v -h --nodeps MySQL-server-4.0.20-0.i386.rpm ... and other rpms Debian RPM database may be missing some dependencies but the chances are that MySQL will work installed without dependency check. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cygwin Mysql No Response
Will Martell [EMAIL PROTECTED] wrote: Not a MySQL issue. MySQL is a standalone native Win32 application that does not need Cygwin. The same applies to MySQL command-line client. Thank you for reading this email. I have the following Configuration Windows 2000 Server MySQL Win32 Binaries Installed Current Release of Cygwin installed I am trying to access mysql from the bash shell rather than the command line. It was working fine until now, but for some reason I can't issue commands to mysql from the bash shell. I have tried to uninstall and reinstall Cygwin. I have tried to uninstall and reinstall MySQL. But the problem is still there. I can connect from the C: prompt and I can issue a select statement from the BASH shell if I put the -e flag after mysql., but I can not just type mysql and get the standard, Welcome to MySQL blah blah.. example. $ mysql nothing happens. But if I do this $ mysql -e select * from user mysql I get rows returned. If anyone can shed some light on this problem, I would appreciate it. I am a newbie at GNU Shells. Thanks [-- text/plain, encoding 7bit, charset: US-ASCII, 86 lines, name: mysql_show_variables.txt --] +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | C:\mysql\ | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | C:\mysql\data\ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF
Re: mysqldump question
Jim McAtee [EMAIL PROTECTED] wrote: Can I easily restore individual databases and/or individual tables from a backup made with mysqldump? Yes if you will individually dump the tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple inserts in one statement
Scott Haneda [EMAIL PROTECTED] wrote: I was just reading a forum post on mysql, someone said it was possible in mysql 4 to insert data into more than one table at once. Is this possible, if so, where in the docs can I see a example of this? Not possible. What is possible is inserting several rows at once. See http://dev.mysql.com/doc/mysql/en/INSERT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: extracting substring from text blob during query
Redmond Militante [EMAIL PROTECTED] wrote: i have a large text blog that i want to extract strings out of.=20 the large text blob See http://dev.mysql.com/doc/mysql/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: nested sets
Hello Aman, Monday, July 26, 2004, 6:03:07 PM, you wrote: Hello, I have a question related to nested sets: On my website a user should be able to create subusers and give them the rights to add/change/delete subsites of the users website. If I remove one or more rights to a user all of its subusers should have the same restrictions as the user. If I grant the rights to the user back all subusers should get their orginal rights AR If, say there was a way, you could remove the c and d rights from AR subsequent users by removing it from User2, I think you are talking AR about some kind of history retention (or memory engine) I think he was talking about user roles. one creates a role and then assigns that role to the users. When one changes the role rights then automatically users' rights change according to the roles they have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why use MySql instead of Firebird or SQL Server express?
In light of the licensing restrictions on using MySql in a commercial package, why would MySql be prefered over Firebird or SQL Server 2005 express edition. Both appear to be no charge to redistribute compared to the $250 for MySql. http://firebird.sourceforge.net/ http://lab.msdn.microsoft.com/express/sql/default.aspx thanks, Steve Richter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld being killed by VM
Michael Gale [EMAIL PROTECTED] wrote: Please install MySQL official binary downloaded from http://www.mysql.com/ and check if the problem is gone. It very much looks like some build issues. Also please check that MySQL has enough memory to run - i.e. the key_buffer variable is reasonably high and also check other software on the server. I have a mysql server that VM is killing about once every two weeks. Now my sar utility shows that the memory on the box continues to grow at a small rate from start to stop. But I am not sure how accurate that is. Now I am running slackware 9.1 with kernel 2.4.23 with mysql 3.23 build 50 with InnoDB support. I checked the my.cnf file and found the following: The box is a dual xeon 2.4Ghz with 2GB of RAM __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process mysqld-max Where do I start to find out if the problem is with mysql ? or our application ? It appears that the mysqld-max process will try and use all the memory in the box and so the kernel will kill the process. Also if I used mysqld_safe would it restart after this ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: nested sets
On Mon, 2004-07-26 at 10:24, DebugasRu wrote: Hello Aman, Monday, July 26, 2004, 6:03:07 PM, you wrote: Hello, I have a question related to nested sets: On my website a user should be able to create subusers and give them the rights to add/change/delete subsites of the users website. If I remove one or more rights to a user all of its subusers should have the same restrictions as the user. If I grant the rights to the user back all subusers should get their orginal rights AR If, say there was a way, you could remove the c and d rights from AR subsequent users by removing it from User2, I think you are talking AR about some kind of history retention (or memory engine) I think he was talking about user roles. one creates a role and then assigns that role to the users. When one changes the role rights then automatically users' rights change according to the roles they have. He didn't mention about roles. I have a similar implementation on my website and let me explain the point I had, and would want Alexander to point out if this is what he wants. Consider he assigns the following Scene1 User2 a,c,d -User3 a,c -User4 c,d -User5 a,d -User6 c,d -User7 d If c is dropped from User2, it should look like Scene2 User2 a,d -User3 a -User4 d -User5 a,d -User6 d -User7 d Now if c is added back to User2, he wants to get Scene1 again. Would this not mean that there should be some kind of storage which as follows User2 c User3 c User4 c User5 - User6 c User7 - Such that on giving c to User2 would give the permissions as above to others. I say this approach because what if Scene 2 is changed as follows at some stage Scene3 - User2 a,d -User3 a -User4 -User5 a,d -User6 a -User7 d - Now if we put back c to User2 don't we want User3, User4 and User6 to get c normally - but now we won't be back to Scene1. I would expect Alexander's input, to check if I am pointing in the right direction. Regards Aman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump issue
Ginger Cheng [EMAIL PROTECTED] wrote: If a table has a column defined as 'float not null' and the corresponding txt file used to load it have sth like 'nan' for the column, although giving warnings, the record will be loaded and the column looks like 'nan' by using mysqlimport. But if you do mysqldump, it puts a 'null' in the column for the insert statement, which fails the later mysql mysqldump (cuz the table definition does not allow it). Can you prepare a testcase for that? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems transferring database
james patrick [EMAIL PROTECTED] wrote: I previously had a server runnning RH 7.3, cPanel 9.41 and MySQL 4.0.20. I'm moving to a different server running Fedora 1, DirectAdmin and MySQL 4.0.17. I have a large database (200mb) and I'm trying to move it over. 200mb is not a large database. 200gb may be considered as such. :) I made a dump using mysqldump -u USER -pPASSWORD DATABASE filename.sql, transferred it between servers via SCP, and tried importing it using mysql -u USER -pPASSWORD DATABASE filename.sql. Make a dump like that: mysqldump -u user -ppassword --opt -Q database filename.sql -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
from http://dev.mysql.com/doc/mysql/en/INSERT.html:
14.1.4 INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
...
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0),
and a row is inserted that would cause a duplicate value in a UNIQUE index
or PRIMARY KEY, an UPDATE of the old row is performed.
...
end quote
there is no IF NOT EXISTS syntax in INSERT, but you could make use of the
ON DUPLICATE KEY mechanism. Assuming you create a unique index on
firstname, lastname, your update might read:
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe')
ON DUPLICATE KEY UPDATE lastname = lastname;
which renders the insert neutral.
On Mon, 2004-07-26 at 11:05, Adaikalavan Ramasamy wrote:
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Thank you.
Regards, Adai.
--
- michael dykman
- [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to deal with a string of categories
Thank Martijn, Brent, and Shawn so much! I did not know about GROUP_CONCAT() funtion. I checked MySQL document page but just under string function in User Comments section there was an example of using it. Shawn, where can I find the syntax of this command? it looks like we can pass some parameters to it too (like SEPARATOR) thanks, Mojtaba - Original Message - From: [EMAIL PROTECTED] To: Mojtaba Faridzad Cc: [EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:15 AM Subject: Re: how to deal with a string of categories May I suggest a design change as a solution? I would suggest that you should create a user/category table to replace your catstr field: CREATE TABLE user_category ( user_Id int not null, category_Code char(1) not null UNIQUE (user_Id, category_Code) ) There will be one record in the user_category table for each category that a user belongs to. To see a list of all of your users and to which cateogories each user belongs: SELECT u.user_Name, category_Name FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code or if you wanted a comma-separated list of categories for each person: SELECT u.user_Name, GROUP_CONCAT(category_Name) as categories FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code GROUP BY u.user_Name NOTE: you will have to change the query examples I gave you to match your actual table and field names This does not limit you to having only 10 (or 20 or 30) categories for each person. It also means that creating new categories will not require a change in your database design (changing the size of a column) but only adding or deleting records. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mojtaba Faridzad [EMAIL PROTECTED] wrote on 07/26/2004 10:55:22 AM: Hi, I'd like to know how you guys write SQL command for this problem. there are some categories which I give them A, B, ... and I have a string field (catstr) with 10 characters in mytable to keep selected categories in a record. when user selects C, G, K, I keep CGK in catstr field. categories has been defined in cattable. now I want to write a query to retreive these catergories. query will have 10 columns (catstr is 10 characters) with description of categories. it means user doesn't see C, or G. I tried to open 10 times cattable with different alias and make the query but it looks like MySQL doesn't like it and doesn't let me open the same table more than once (even with different alias). I can create 10 temporary tables and solve this problem and it doesn't look good. or I can create my query with 10 CASE commands with I create them base on cattable. how you guys solve this problem? maybe there is a better solution which I don't know and very neat can solve this problem. thanks, Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong ?
mysql desc tb;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| myID | int(11) | | PRI | NULL| auto_increment |
| firstname | varchar(10) | YES | MUL | NULL||
| lastname | varchar(10) | YES | | NULL||
+---+-+--+-+-++
3 rows in set (0.00 sec)
mysql INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
DUPLICATE KEY UPDATE lastname = lastname;
ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
UPDATE lastname = lastname' at line 1
Alternatively, I am looking for 'try' equivalent in perl, so that if the
insert is duplicate, the rest of the script is still run. Thank you.
Regards, Adai.
On Mon, 2004-07-26 at 17:20, Michael Dykman wrote:
from http://dev.mysql.com/doc/mysql/en/INSERT.html:
14.1.4 INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
...
If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0),
and a row is inserted that would cause a duplicate value in a UNIQUE index
or PRIMARY KEY, an UPDATE of the old row is performed.
...
end quote
there is no IF NOT EXISTS syntax in INSERT, but you could make use of the
ON DUPLICATE KEY mechanism. Assuming you create a unique index on
firstname, lastname, your update might read:
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe')
ON DUPLICATE KEY UPDATE lastname = lastname;
which renders the insert neutral.
On Mon, 2004-07-26 at 11:05, Adaikalavan Ramasamy wrote:
I am creating a small database that keeps track of users and assigns
them a unique user ID.
The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :
CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John | Doe |
|2 | Jack | Doe |
|3 | John | Smith|
+--+---+--+
I get syntax error with the following :
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?
Thank you.
Regards, Adai.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
File size limits with mysql 4.1
I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Errcode: 27
Does anyone know what goes on (system wise) during the CREATE INDEX ? I can see it's copying the table file, what then though? Hi, I'm stuck on this really badly. Can anyone help me out please? Thanks, JS. I tried running the create index again using --verbose but this is all I got: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STLd39Et' (Errcode: 27) -- CREATE INDEX timeindex ON internet_usage (uid,time) -- Bye Can anyone tell me the specifics of what happens during the create index? I can see that the table being indexed gets copied but what happens in the code after that? Thanks alot. JS. Hi, I'm trying to run the following SQL (on mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc): CREATE INDEX timeindex ON internet_usage (time); CREATE INDEX urlindex ON internet_usage (urlid); but keep running into the following error: Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27) $ perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The create index gets as far as actually copying the 18GB internet_usage table, and then does some processing (not sure what), then falls over with the error 27. The message seems to be misleading because all the ulimit values are unlimited. Also the filesystem is large-file enabled as shown below: $ pwd /proxydb/mysql/data/proxy_logs $ ls -l total 58124344 -rw-rw 1 mysqlmysql 0 Jul 23 11:08 bulk_table.MYD -rw-rw 1 mysqlmysql 1024 Jul 23 11:08 bulk_table.MYI -rw-rw 1 mysqlmysql 8970 Jul 09 14:05 bulk_table.frm -rw-rw 1 mysqlmysql18550068032 Jul 23 10:45 internet_usage.MYD -rw-rw 1 mysqlmysql5150286848 Jul 23 11:08 internet_usage.MYI -rw-rw 1 mysqlmysql 8856 Jul 09 14:05 internet_usage.frm -rw-rw 1 mysqlmysql380 Jul 09 14:08 url_actions.MYD File system name/proxydb NEW mount point[/proxydb] SIZE of file system (in 512-byte blocks) [131203072] Mount GROUP[] Mount AUTOMATICALLY at system restart? yes + PERMISSIONS read/write + Mount OPTIONS [] + Start Disk Accounting? no + Fragment Size (bytes) 4096 Number of bytes per inode 4096 Compression algorithm no Large File Enabled true Allocation Group Size (MBytes) 64 I'm not sure what else could be wrong. I've tried creating another table with the new keys and selecting * from internet_usage table but I still got the same error there. Here's some miscellaneous info which might be useful. mysql show indexes from internet_usage; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | internet_usage | 1 | uid |1 | uid | A | 23039 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.07 sec) ++++---++-+-+--+---++-+-+-+-+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++-+-+--+---++-+-+-+-+-+ | bulk_table | MyISAM | Dynamic| 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL| | | | internet_usage | MyISAM | Fixed | 579689626 | 32 | 18550068032 |137438953471 | 5150286848 | 0 | NULL | 2004-07-09 14:05:56
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy wrote:
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
Assuming you have the unique index on (firstname, lastname), just do
INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
But how are you planning to handle multiple people named Jack Doe?
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Yes, this does what I want and does not produce an error (which caused
the remaining MYSQL syntax not to be executed). The firstname, lastname
was for example only. In my problem, these are two different identifiers
so I am not worried about multiple dual identifiers.
Thanks to Keith Ivey, Alec Cawley, Gerald Clark and Michael Dykman for
helping with this problem.
I learnt a lot from the list today. Thanks!
Regards, Adai.
On Mon, 2004-07-26 at 18:04, Keith Ivey wrote:
Adaikalavan Ramasamy wrote:
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
Assuming you have the unique index on (firstname, lastname), just do
INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
But how are you planning to handle multiple people named Jack Doe?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump question
At 20:58 -0600 7/21/04, Jim McAtee wrote: (Apologies in advance for the crossposting, but I asked the same questions on the MySQL Windows list and didn't get any replies) I need a simple backup mechanism for MySQL (3.2x) that will backup all databases on a server. Something that can be run from a scheduled Windows batch file. From what I've read, it looks like mysqldump.exe is the simplest method. The plan is to backup the server to a directory (file?) on the same machine and then use my standard backup software to backup that directory to my backup media of choice. Questions: Can I easily restore individual databases and/or individual tables from a backup made with mysqldump? I imagine read locks are placed on the data during a dump. What degree of granularity is employed in the locking when doing an all databases backup? That is, can I expect the entire server to be non-writable during the entire backup, or only one database or one table at a time? I see the mysqlhotcopy script mentioned almost any time mysqldump is recommended. What are the advantages (if any) to using mysqlhotcopy? On Windows, none. mysqlhotcopy runs on Unix and NetWare, but not Windows. mysqlhotcopy tells the the server to lock the table files, then it makes copies of those files while they are locked. Windows file-locking semantics prevent this from working, because you cannot copy a file while the server has it locked. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy [EMAIL PROTECTED] wrote on 26/07/2004
16:31:44:
But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
I don't think it is possible. There is a fairly serious architectural
split in SQL between SELECT, which is read-only and returns a two
dimensional table of results, and UPDATE (and its twins REPLACE and
INSERT), which update the database and return a single integer, usually
the count of rows updated. The command you are trying to do combines both.
The transport level would therefore not know whether to carry a
SELECT-style reply or an UPDATE-style reply.
This is a limitation on SQL, rather than MySQL. You have to do two
operations.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why use MySql instead of Firebird or SQL Server express?
Mysql is easier to install than Firebird and runs in more Operative Systems than SQL Server. Steve Richter wrote: In light of the licensing restrictions on using MySql in a commercial package, why would MySql be prefered over Firebird or SQL Server 2005 express edition. Both appear to be no charge to redistribute compared to the $250 for MySql. http://firebird.sourceforge.net/ http://lab.msdn.microsoft.com/express/sql/default.aspx thanks, Steve Richter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to deal with a string of categories
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Is where you will find the GROUP_CONCAT function in the manual. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mojtaba Faridzad [EMAIL PROTECTED] wrote on 07/26/2004 11:34:30 AM: Thank Martijn, Brent, and Shawn so much! I did not know about GROUP_CONCAT() funtion. I checked MySQL document page but just under string function in User Comments section there was an example of using it. Shawn, where can I find the syntax of this command? it looks like we can pass some parameters to it too (like SEPARATOR) thanks, Mojtaba - Original Message - From: [EMAIL PROTECTED] To: Mojtaba Faridzad Cc: [EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:15 AM Subject: Re: how to deal with a string of categories May I suggest a design change as a solution? I would suggest that you should create a user/category table to replace your catstr field: CREATE TABLE user_category ( user_Id int not null, category_Code char(1) not null UNIQUE (user_Id, category_Code) ) There will be one record in the user_category table for each category that a user belongs to. To see a list of all of your users and to which cateogories each user belongs: SELECT u.user_Name, category_Name FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code or if you wanted a comma-separated list of categories for each person: SELECT u.user_Name, GROUP_CONCAT(category_Name) as categories FROM user u INNER JOIN user_category uc ON uc.user_ID = u.user_id INNER JOIN category cat ON cat.category_Code = uc.category_Code GROUP BY u.user_Name NOTE: you will have to change the query examples I gave you to match your actual table and field names This does not limit you to having only 10 (or 20 or 30) categories for each person. It also means that creating new categories will not require a change in your database design (changing the size of a column) but only adding or deleting records. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mojtaba Faridzad [EMAIL PROTECTED] wrote on 07/26/2004 10:55:22 AM: Hi, I'd like to know how you guys write SQL command for this problem. there are some categories which I give them A, B, ... and I have a string field (catstr) with 10 characters in mytable to keep selected categories in a record. when user selects C, G, K, I keep CGK in catstr field. categories has been defined in cattable. now I want to write a query to retreive these catergories. query will have 10 columns (catstr is 10 characters) with description of categories. it means user doesn't see C, or G. I tried to open 10 times cattable with different alias and make the query but it looks like MySQL doesn't like it and doesn't let me open the same table more than once (even with different alias). I can create 10 temporary tables and solve this problem and it doesn't look good. or I can create my query with 10 CASE commands with I create them base on cattable. how you guys solve this problem? maybe there is a better solution which I don't know and very neat can solve this problem. thanks, Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple inserts in one statement
At 14:32 -0700 7/22/04, Scott Haneda wrote: I was just reading a forum post on mysql, someone said it was possible in mysql 4 to insert data into more than one table at once. Is this possible, if so, where in the docs can I see a example of this? Scott, Perhaps you could ask someone to provide an example of this. I'd be interested to see how this is done. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
On Mon, 26 Jul 2004 17:47:37 +0100, Adaikalavan Ramasamy
[EMAIL PROTECTED] wrote:
This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong ?
mysql desc tb;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| myID | int(11) | | PRI | NULL| auto_increment |
| firstname | varchar(10) | YES | MUL | NULL||
| lastname | varchar(10) | YES | | NULL||
+---+-+--+-+-++
3 rows in set (0.00 sec)
mysql INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
DUPLICATE KEY UPDATE lastname = lastname;
ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
UPDATE lastname = lastname' at line 1
You are probably running an older version of mysql that doesn't support this.
Try insert ignore.
Alternatively, I am looking for 'try' equivalent in perl, so that if the
insert is duplicate, the rest of the script is still run. Thank you.
eval.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
You must be getting an error code when inserting now. If that is related to index file size (that's what I had) . You can do ALTER TABLE tablename MAX_ROWS=big_num On Mon, 2004-07-26 at 11:48, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? -- - michael dykman - [EMAIL PROTECTED] -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why use MySql instead of Firebird or SQL Server express?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve Richter wrote: | In light of the licensing restrictions on using MySql in a commercial | package, why would MySql be prefered over Firebird or SQL Server 2005 | express edition. Both appear to be no charge to redistribute compared to the | $250 for MySql. | | http://firebird.sourceforge.net/ | | http://lab.msdn.microsoft.com/express/sql/default.aspx | | thanks, | | Steve Richter Steve, you're quoting a price for buying servers 'onesy-twosey' above, which doesn't appear to be your situation. I believe that what you're looking for is what's called an 'OEM' deal, if you want to commercially-license MySQL as an 'OEM', then you need to contact [EMAIL PROTECTED], as that pricing is always negotiated to meet your product's pricing and business model. MSDE has volume and concurrency limitations that are imposed by MS, compared to MySQL (IIRC, 25 concurrent users, and 2GB per database). It also is only available on the Windows platform. FirebirdSQL is an excellent project, but as far as I know there is not a single organization that stands behind it providing support, training, etc. as MySQL AB does with the MySQL product line. You are also much more likely to find people and third-party products that know, understand and work with MySQL than those that can work with FirebirdSQL out there in the marketplace. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBUjktvXNTca6JD8RAjOrAKCFLr/guM/miTygRMxnjcTQhd+dEwCcCj72 ZSMk+wfjNuPqxSb8h75/c2U= =SYAb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Insert into MyISAM table from Oracle Stored Procedure
Note: I installed MySQL on my labtop (a Windows XP machine) which is located at the same site as the Oracle server, just to compare the insert times with the remote MySQL database. Inserts which were taking over 4 minutes were now completing in 90 seconds or less. Approximately 1 second per insert is not great, but it is a lot better than what we were seeing before. So, we are planning on moving our MySQL database server to the same site as the Oracle database. Any other performance tuning suggestions would be be appreciated. Thanks! Bob [EMAIL PROTECTED] 19-Jul-2004 11:35 EST To: [EMAIL PROTECTED] cc: Subject:Slow Insert into MyISAM table from Oracle Stored Procedure I've developed an Oracle PL/SQL stored procedure that takes information from an Oracle 9.2.0.5 database and inserts this information into a MySQL 4.0.17 MyISAM table. The insert takes over 3 minutes to insert approximately 90 records based on an Oracle SQL Trace. A few things that may be factors in the slow performance I use MySQL ODBC 3.5.1 to connect Oracle to MySQL. Are there any parameters MySQL ODBC parameters that can be tuned to improve performance? I've tried to turn on tracing, but don't know if I am doing it correctly because I am not getting any .trc files. Are .trc files only generated on errors? The table in question has 98 columns with 3 text fields. It appears the insert statements actually inserts all non-TEXT fields first and then updates the record with the TEXT field data. I believe this is the expected behavior, but it is slowing things down a bit. It would be nice if I could trick the MySQL database into thinking it's inserting into a VARCHAR or CHAR field. The MySQL server resides a couple of hundred miles away from the Oracle server so Network latency is a factor. However, we do have a 786KB/s line with 70 ms latency which isn't bad. The only parameter/variable I've changed from the default on the MySQL server is ascii. There are probably some memory variables that could be tuned, but I'm not looking at high volumes yet, so I don't think that this would be the bottleneck. Any suggestions/recommendations would be much appreciated. Thanks, Bob Runion -- Here's the Oracle SQL Trace of the INSERT statement? INSERT INTO [EMAIL PROTECTED] VALUES (:1, :2, :3, :4, :5, :6, :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) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 97 1.12 23.37 0 0 0 0 Execute 97 0.23 157.27 0 0 097 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 194 1.35 180.64 0 0 097 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 18 (recursive depth: 1) * ** This e-mail message is intended only for the personal use of the recipient(s) named above. This message is confidential. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the sender immediately by e-mail and delete the original message. **
Re: File size limits with mysql 4.1
At 12:48 -0400 7/26/04, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? Are you using MyISAM tables? If so, you probably want to specify MAX_ROWS and/or AVG_ROW_LENGTH table options when you create the tables so that larger internal row pointers get used: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html For existing tables, you can use ALTER TABLE to change the option values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: run-all-test
This is a follow-up to my original post from yesterday. I've narrowed down
the problem to the test-connect script.
C:\mysql\benchperl test-connect --user=root --password=tops3cr3t
Testing server 'MySQL 4.0.20a nt' at 2004-07-26 13:01:26
Testing the speed of connecting to the server and sending of data
Connect tests are done 1 times and other tests 10 times
Testing connection/disconnect
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to MySQL server on 'localhost' (10048) at test-connect line 69
.
. (repeats a number of times)
.
Got error 'Can't connect to MySQL server on 'localhost' (10048)' after 3908
connects at test-connect line 79.
It's always dying at about 3900 connections. If I re-run the test
immediately after it fails, it typically aborts.
Is there a parameter I'm missing somewhere?
Thanks,
Chris
-Original Message-
From: Christopher M. DeBracy [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 25, 2004 10:19 PM
To: [EMAIL PROTECTED]
Subject: run-all-test
Have done a fresh install of 4.0.20 and am getting some odd
errors when running as root when I peform the tests:
***
C:\mysql\benchperl run-all-tests --user=root
--password=tops3cr3t Benchmark DBD suite: 2.15
Date of test:2004-07-25 22:11:28
Running tests on:Windows NT 5.1 x86
Arguments:
Comments:
Limits from:
Server version: MySQL 4.0.20a nt
Optimization:None
Hardware:
alter-table: Total time: 32 wallclock secs ( 0.06 usr 0.02
sys + 0.00 cusr 0. 00 csys = 0.08 CPU)
ATIS: Total time: 35 wallclock secs (12.09 usr 5.26 sys +
0.00 cusr 0.00 csys = 17.36 CPU)
big-tables: Total time: 29 wallclock secs (10.31 usr 10.36
sys + 0.00 cusr 0.0 0 csys = 20.67 CPU)
connect: DBI
connect('database=test;host=localhost','root',...) failed:
Can't co nnect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to MySQL server on 'localhost' (10048) at
./test-connect line 69 DBI
connect('database=test;host=localhost','root',...) failed:
Can't connect to
Re: using mysql in commercial software
Steve Richter wrote: -Original Message- From: Michael Abbott [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: RE: using mysql in commercial software This may not be strictly legal, but you could have the end user download MySQL.. to run with your software. exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? -Steve Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why use MySql instead of Firebird or SQL Server express?
FirebirdSQL is an excellent project, but as far as I know there is not a single organization that stands behind it providing support, training, etc. as MySQL AB does with the MySQL product line. Might want to take a look at http://www.ibphoenix.com/ Training is pretty pricey, but it's there. Cheers, Justin Quoting Mark Matthews [EMAIL PROTECTED]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve Richter wrote: | In light of the licensing restrictions on using MySql in a commercial | package, why would MySql be prefered over Firebird or SQL Server 2005 | express edition. Both appear to be no charge to redistribute compared to the | $250 for MySql. | | http://firebird.sourceforge.net/ | | http://lab.msdn.microsoft.com/express/sql/default.aspx | | thanks, | | Steve Richter Steve, you're quoting a price for buying servers 'onesy-twosey' above, which doesn't appear to be your situation. I believe that what you're looking for is what's called an 'OEM' deal, if you want to commercially-license MySQL as an 'OEM', then you need to contact [EMAIL PROTECTED], as that pricing is always negotiated to meet your product's pricing and business model. MSDE has volume and concurrency limitations that are imposed by MS, compared to MySQL (IIRC, 25 concurrent users, and 2GB per database). It also is only available on the Windows platform. FirebirdSQL is an excellent project, but as far as I know there is not a single organization that stands behind it providing support, training, etc. as MySQL AB does with the MySQL product line. You are also much more likely to find people and third-party products that know, understand and work with MySQL than those that can work with FirebirdSQL out there in the marketplace. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBUjktvXNTca6JD8RAjOrAKCFLr/guM/miTygRMxnjcTQhd+dEwCcCj72 ZSMk+wfjNuPqxSb8h75/c2U= =SYAb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - This mail sent through IMP: http://horde.org/imp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow server - any idea?
I've optimized the main tables (the forums one and a few others) a few days ago. About index cardinality, I don't know what to tell you. For a few tables it is high, like for the vbulletin postindex (higher than 11 000 000) but it's absolutely normal for such a forum. And about explain, we've got a few hundred tables so i can't tell you much :) Thanks for your help, Julien Lavigne du Cadet. - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Julien Lavigne du Cadet ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 26, 2004 3:06 PM Subject: RE: Slow server - any idea? How often do you optimize/analyze your tables? Have you checked the index cardinality? What does an explain plan show? -Original Message- From: Julien Lavigne du Cadet To: [EMAIL PROTECTED] Sent: 7/25/04 4:26 PM Subject: Slow server - any idea? Hi eveybody, I've got problems since a few weeks with my mysql server. There are a lot of slow queries (about 1200 in less than 48 hours), even some that should absolutely not be slow like this one which is performing on a HEAP table : SELECT * FROM vb3_session WHERE sessionhash = '31d429cc3820a8bb141733de2cd306ba' AND lastactivity 1090778091 AND host = '65.50.5.140' AND idhash = '385f8c8da967afdd86399fb72d05'; I'm running a p4 2,4. 1Go RAM, DD IDE 80Go under FreeBSD and I've got the 4.0.20 version installed (anyway I tried to downgrade to 4.0.18 and it didn't changed anything). There are about 20 sites and a vb3 forum with 200 to 300 visitors at once. The server doesn't seem to consume much cpu as shown : 42992 mysql 2 0 226M 66256K poll 87:38 4.83% 4.83% mysqld Here is my config file : [mysqld] datadir=/var/db/mysql socket=/tmp/mysql.sock skip-locking skip-innodb query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=500 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_cache_size=64 key_buffer=150M join_buffer=1M max_allowed_packet=2M table_cache=768 record_buffer=1M sort_buffer_size=1M read_buffer_size=1M #read_rnd_buffer_size=768K max_connect_errors=10 # Try number of CPU's*2 for thread_concurrency thread_concurrency=2 myisam_sort_buffer_size=64M #log-bin server-id=1 log_slow_queries=/var/log/slow-queries.log long_query_time=1 [mysql.server] user=mysql basedir=/usr/local [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/db/mysql/srv1.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout Here is the status : Created tmp disk tables 706 Created tmp tables 162301 Created tmp files 138 Delayed insert threads 0 Delayed writes 0 Delayed errors 0 Flush commands 1 Handler commit 0 Handler delete 62700 Handler read first 10465 Handler read key 53413365 Handler read next 20806399 Handler read prev 8431183 Handler read rnd 12619723 Handler read rnd next 670650172 Handler rollback 0 Handler update 2921336 Handler write 23073711 Key blocks used 108984 Key read requests 135302387 Key reads 107438 Key write requests 214624 Key writes 184195 Max used connections 41 Not flushed key blocks 0 Not flushed delayed rows 0 Open tables 768 Open files 1321 Open streams 0 Opened tables 9238 Qcache queries in cache 4900 Qcache inserts 954259 Qcache hits 1556783 Qcache lowmem prunes 143367 Qcache not cached 120513 Qcache free memory 7149624 Qcache free blocks 2438 Qcache total blocks 14367 Rpl status NULL Select full join 739 Select full range join 63 Select range 135410 Select range check 0 Select scan 415678 Slave open temp tables 0 Slave running OFF Slow launch threads 0 Slow queries 1280 Sort merge passes 69 Sort range 128597 Sort rows 13431446 Sort scan 200597 Table locks immediate 2514328 Table locks waited 7966 Threads cached 39 Threads created 42 Threads connected 3 Threads running 1 I also have got this kind of messages in mysqld.log : 040725 12:56:47 Aborted connection 250044 to db: 'mondespe_lineage2' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 12:58:40 Aborted connection 250285 to db: 'animelan' user: 'animelan' host: `localhost' (Got timeout reading communication packets) 040725 13:09:59 Aborted connection 251722 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251896 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251891 to db: 'vb3_fansite' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:11:06 Aborted connection 251914 to db: 'mysql' user: 'root' host: `localhost' (Got
Re: why use MySql instead of Firebird or SQL Server express?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: |FirebirdSQL is an excellent project, but as far as I know there is not a |single organization that stands behind it providing support, training, |etc. as MySQL AB does with the MySQL product line. | | | Might want to take a look at http://www.ibphoenix.com/ | | Training is pretty pricey, but it's there. | | Cheers, | | Justin Justin, Let me rephrase that as I didn't mean 'there isn't anyone out there doing support/training for FirebirdSQL'...What I meant is that there is _one_ place with MySQL you can go for training, support, licensing, partnerships, and that is MySQL AB. With other open source projects, such as FirebirdSQL, you _could_ be on your own to find the all-inclusive set of services that an OEM, ISV or database consumer that a _single_ source will get you with MySQL. -Mark - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] | - | This mail sent through IMP: http://horde.org/imp/ - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBVQdtvXNTca6JD8RAtnLAJoDpZ+jjT1rGXqVgH79mC507/c/QwCfePoq 37jbYHPzvVoy7cVXjJkBdAc= =2Sno -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using mysql in commercial software
Hi all, If I write a comercial software that allow my customer at instalation time to select between mysql, interbase or other data base is it legal? Regards, Laercio. -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 26 de julho de 2004 15:26 To: Steve Richter Cc: chat. mysql. Subject: Re: using mysql in commercial software Steve Richter wrote: -Original Message- From: Michael Abbott [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: RE: using mysql in commercial software This may not be strictly legal, but you could have the end user download MySQL.. to run with your software. exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? -Steve Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. -- 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]
SQL for detecting if Column/Index already exists?
Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. I'm using 4.1.1-alpha. Thanks much Shishir Ghate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1062: Duplicate entry
Hey, I am trying to create an index on a varchar column, but I am getting a suspicious error I am unable to overcome. Indeed there are duplicated rows but since there is no constraint nor a key in the table, I do not see why this error is generated. I would appreciate some help. Here are the details. mysql create index url_site_idx on URL(Site(255)); ERROR 1062: Duplicate entry 'Transmeta unveils futuristic Crusoe chip ' for key 1 mysql mysql select version(); +-+ | version() | +-+ | 4.0.17-standard | +-+ 1 row in set (0.00 sec) mysql show create table URL; +---+--- + | Table | Create Table | +---+--- + | URL | CREATE TABLE `URL` ( `Parent` text, `URL` text, `Site` text, `Description` text, `Topsite` int(11) default NULL, `Star` tinyint(1) default NULL ) TYPE=MyISAM | +---+--- + 1 row in set (0.00 sec) mysql describe URL; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | Parent | text | YES | | NULL| | | URL | text | YES | | NULL| | | Site| text | YES | | NULL| | | Description | text | YES | | NULL| | | Topsite | int(11)| YES | | NULL| | | Star| tinyint(1) | YES | | NULL| | +-++--+-+-+---+ 6 rows in set (0.00 sec) ## no indexes at all currently mysql show index from URL; Empty set (0.00 sec) Thanks. Eli Shemer. Siteware. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL for detecting if Column/Index already exists?
At 14:08 -0500 7/26/04, Ghate, Shishir wrote: Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. You could use the SHOW COLUMNS and SHOW INDEX statements? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: nested sets
[Aman's view of my problem] Hi, I think you know what my problem is. My solution so far is (from another mail): -- snip -- The main problem I have to do the rollback. A 2nd table is no option because it might be possible that another users rights get be change before the rights of the first user has been restored. Using +-+-++-+-++++ | node_id | root_id | user | lft | rgt | a | c | d | +-+-++-+-++++ | 1 | 1 | User1 | 1 | 4 | 1 | 0 | 0 | +-+-++-+-++++ | 2 | 1 | User2 | 2 | 3 | 1 | 1 | 1 | +-+-++-+-++++ and using 1 or 0 for the a, c and d col would give the rights to the users. But how to check if the rights of the parent user do not are lower? In the exmaple above how to get 0 for c and d for User2 (because User2 is a subuser of User1)? -- snip -- Look's like this is a really big problem!? Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using mysql in commercial software
I guess that would depend on what kind of license your database connection libraries come with. ** DISCLAIMER ** I AM NOT A LAWYER and THIS IS *NOT* LEGAL ADVICE. **DISCLAIMER ** Whenever I ask myself can I sell what I just created and keep my source code hidden, I rely on several factors: 1) What restrictions are placed on any source code I wrote, used, or re-used, as well as any image, sound, or other binary resources included in the creation of the product 2) What license do I posesses to use the tools I use to compile, compress, link, assemble, or otherwize create the product in any fashion. 3) What rights do I have to incorporate or redistribute any third-party libraries that the product will require. To find the answers for your circumstances, you must read and re-read the licensing agreements that apply to _everything_ you use to create your product. If you do not feel comfortable with your translation of the legal-ese in your licenses then you need to ask a competent law professional in your locality. Basically, software is rarely ever sold. What is paid for is the license to use a software product (program, library, source code, etc.) for a particular purpose. Some licenses allow for the unlimited personal use of some very powerful tools but when you go commercial with them (using them to create products for sale or transfer to another party) you must verify that your licenses permit you to do so. Each situation will be different. Every product will be different. You just have to go back and dig up all that stuff again and go over it. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Laercio Xisto Braga Cavalcanti [EMAIL PROTECTED] wrote on 07/26/2004 04:06:01 PM: Hi all, If I write a comercial software that allow my customer at instalation time to select between mysql, interbase or other data base is it legal? Regards, Laercio. -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 26 de julho de 2004 15:26 To: Steve Richter Cc: chat. mysql. Subject: Re: using mysql in commercial software Steve Richter wrote: -Original Message- From: Michael Abbott [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: RE: using mysql in commercial software This may not be strictly legal, but you could have the end user download MySQL.. to run with your software. exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? -Steve Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. -- 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: MySQL book
I totally recommend Paul Dubois's book. Excellent book I own the first edition. I bet the second edition is just as good and more up to date. Paul DuBois wrote: At 1:55 +0200 7/26/04, Schalk Neethling wrote: Can anyone suggest o great book to learn MySQL inside out? I am thinking of getting: *MySQL By* Paul DuBois http://www.informit.com/safari/author_bio.asp?ISBN=0735709211 - New Riders Publishing That's the first edition. I would suggest getting the second edition instead. :-) (http://www.kitebird.com/mysql-book/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why use MySql instead of Firebird or SQL Server express? (long)
Hi Steve, In light of the licensing restrictions on using MySql in a commercial package, why would MySql be prefered over Firebird or SQL Server 2005 express edition. Both appear to be no charge to redistribute compared to the $250 for MySql. http://firebird.sourceforge.net/ http://lab.msdn.microsoft.com/express/sql/default.aspx Well, IMO, Firebird is much easier to use than SQL Express - less locking issues, much more platforms ... Then again, I know Firebird pretty much inside out. Firebird also has Triggers and Stored Procedures, Check Constraints and Views, all of which don't exist in MySQL. (don't start about MySQL 5 - that's a no-go area) Mysql is easier to install than Firebird I have to disagree there, coming from a Win32 world. MySQL, on the other hand, has much more security related features (hosts etc that can be banned/granted). Security in Firebird should have some significant changes in v2 though. Firebird, as an open source project, is also relatively new. Many things, like a clear roadmap etc are still to be created. The Firebird Foundation is doing pretty well in collecting funds and has recently entered its second year. MSDE has volume and concurrency limitations that are imposed by MS, compared to MySQL (IIRC, 25 concurrent users, and 2GB per database). It also is only available on the Windows platform. SQLExpress is not MSDE, it looks like it though. SQLExpress has a 4Gb limit/database and I don't recall a user limit, but a concurrent-load limit of 5. That is, many can connect, but only 5 concurrent batches (select/insert/script) can be ran at the same time. This is controlled by the server, so no need to code around it or something like that... FirebirdSQL is an excellent project, but as far as I know there is not a single organization that stands behind it providing support, training, etc. as MySQL AB does with the MySQL product line. Right, this is true. Firebird (the project) isn't owned by anyone except the Firebird Admin group. It's direction is closely monitored by the Firebird Foundation - the two groups cooperate together with regards to funds, direction and PR. btw, Mark, Firebird is called Firebird, not FirebirdSQL. As somesaid earlier, IBPhoenix can deliver top-notch training as some of the long-time InterBase people started that company. You are also much more likely to find people and third-party products that know, understand and work with MySQL than those that can work with FirebirdSQL out there in the marketplace. Well, of course, but MySQL has been around much much longer than Firebird. However, Firebird is on the rise, and many InterBase (it's parent) products and third-party-product companies now provide tools for Firebird as well (ourselves included). With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql libs and multiple hostnames
Sorry to re-send, but I lost some mail and thought perhaps this was answered and I missed it, but looking at the archives I don't see any replies. Thanks! Charles I have a quick question about how programs linked against the mysql C libraries handle the following: -assume three mysql hosts, say 10.0.0.2, 10.0.0.3, and 10.0.0.4 -assume a dns name db.example.com that returns the following: [EMAIL PROTECTED] host db.example.com db.example.com has address 10.0.0.2 db.example.com has address 10.0.0.3 db.example.com has address 10.0.0.4 If my client program repeatedly connects to db.example.com and my nameserver round-robins through those IPs, will the client also keep cycling through those? What is the behaviour if one of those hosts does not respond? Will the client application then try the next one? Thanks, Charles ___ Charles Sprickman NetEng/SysAdmin Bway.net - New York's Best Internet - www.bway.net [EMAIL PROTECTED] - 212.655.9344 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.0.20 configure fails mac os x 10.3.1 client
I executed the following preparation work:
1. Upgraded Mac OS X 10.3.1 to 10.3.4
2. Installed two additional security updates
3. Examined the status of the Xcode packages
pre-installed on the box Documentation states it is
Xcode 1.0 for Mac OS 10.3 Panther
4. I ran all installers except, CHUD, DevExamples,
cross-development and x11
5. I upgraded Xcode 1.0 to Xcode 1.1
6. Added FLAG options to a .config wrapper for
configure
Ran .config with the following results:
1. configure terminates with exit 0 (successful return
?)
2. Welcome message printed to terminal
3. Many error messages were logged, first and second
of shown below
4. Approximately 208 Makefiles were created
What is the current state of the configure environment
- success as indicated by the return code? But what
of these error messages?
Since the state of the Xcode environment may be
suspect, I wonder if it makes sense to flush it, and
start over with a clean setup - which of course raises
the question how to clean up the current environment
correctly in order to start over.
Thanks for any suggestions.
Ron
configure:2913: checking for gcc option to accept ANSI
C
configure:2974: gcc -c -O3 -fno-omit-frame-pointer
conftest.c 5
configure:2977: $? = 0
configure:2980: test -s conftest.o
configure:2983: $? = 0
configure:3001: result: none needed
configure:3019: gcc -c -O3 -fno-omit-frame-pointer
conftest.c 5
conftest.c:2: error: parse error before me
configure:3022: $? = 1
configure: failed program was:
| #ifndef __cplusplus
| choke me
| #endif
.
.
.
configure:3586: gcc -c -O3 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions
-fno-rtti conftest.cc 5
configure: In function `int main()':
configure:3587: error: `exit' undeclared (first use
this function)
configure:3587: error: (Each undeclared identifier is
reported only once for
each function it appears in.)
configure:3589: $? = 1
configure: failed program was:
| #line 3569 configure
| /* confdefs.h. */
|
| #define PACKAGE_NAME
| #define PACKAGE_TARNAME
| #define PACKAGE_VERSION
| #define PACKAGE_STRING
| #define PACKAGE_BUGREPORT
| #define PACKAGE mysql
| #define VERSION 4.0.20
| #define PROTOCOL_VERSION 10
| #define DOT_FRM_VERSION 6
| #define SYSTEM_TYPE apple-darwin7.4.0
| #define MACHINE_TYPE powerpc
| /* end confdefs.h. */
|
| int
| main ()
| {
| exit (42);
| ;
| return 0;
| }
--- Michael Stassen [EMAIL PROTECTED]
wrote:
First, I should point out that the simplest course
would be to download the
precompiled binary from mysql. That said, I admit I
like to build from
source, largely because whenever things go wrong, I
always learn something.
On first glance, I see a few problems:
1) You are running OS X 10.3.1, but current is
10.3.4. I don't believe
that's the cause of the problem here, but there are
some important security
updates you are missing. I'd recommend running
Software Update to install
at least the security patches.
2) You appear to have Xcode 1.1, based on your gcc
version. The error
messages you are getting indicate that your system
header files, which
should be in /usr/include, cannot be found. Those
are normally put in place
by the Xcode installer along with gcc.
3) The second line is trying to compile with just
`gcc -c`. If you use the
recommended flags, you'd see `gcc -c -O3
-fno-omit-frame-pointer`. I'm
guessing you haven't seen the configure
recommendations in the manual
http://dev.mysql.com/doc/mysql/en/MySQL_binaries.html.
Based on those,
I've created a file named .config with the following
contents:
CC=gcc \
CFLAGS=-O3 -fno-omit-frame-pointer \
CXX=gcc \
CXXFLAGS=-O3 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions
-fno-rtti
./configure --prefix=/usr/local/mysql \
--localstatedir=/usr/local/mysql/data \
--with-extra-charsets=complex \
--enable-thread-safe-client \
--enable-local-infile \
--disable-shared
(My mail client is determined to wrap the CXXFLAGS
line. You should unwrap
it to one line.) The only change I've made relative
to the mysql
recommended settings is the --localstatedir path.
I've made .config executable (chmod +x .config), so
each time I need to
build a new version of mysql, I just copy .config
into the source directory
and run it with `. .config`. That saves me typing
each time and keeps my
settings constant across versions.
4) I expected to see configure:5362: gcc -E
conftest.cc, but your output
shows /lib/cpp instead of gcc -E. My config.log
for mysql 4.0.20
contains no mention of /lib/cpp.
At this point, it's hard to speculate, but I'm
wondering if perhaps you
either didn't do a full install of Xcode or
moved/deleted parts of it afterward.
I hope this is enough info to nudge you in the right
direction. If not, let
us know.
Michael
Ron Phelps wrote:
Environment:
OS: Mac OS X 10.3.1, client
mysql:
Re: why use MySql instead of Firebird or SQL Server express? (long)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martijn Tonies wrote: [snip] | SQLExpress is not MSDE, it looks like it though. SQLExpress | has a 4Gb limit/database and I don't recall a user limit, but a | concurrent-load limit of 5. That is, many can connect, but only | 5 concurrent batches (select/insert/script) can be ran at the | same time. This is controlled by the server, so no need to code | around it or something like that... | Martijn, Sorry, skipped right past the fact he was asking about SQLServer Express, however I consider 'Express' to be the MSDE of SQLServer 2k5, with a few higher limits. However consider that SQLServer Express is 1.) Not a shipping product (it's in Beta), and 2.) Requires the .Net framework version 2.0 to be installed (which is _also_ a beta), which (in my opinion) makes it not suitable for an ISV to look at _yet_. [snip] | Right, this is true. Firebird (the project) isn't owned by anyone | except the Firebird Admin group. It's direction is closely monitored | by the Firebird Foundation - the two groups cooperate together | with regards to funds, direction and PR. | | btw, Mark, Firebird is called Firebird, not FirebirdSQL. [snip] Sorry, just fell into 'vernacular', mostly because their website is 'www.firebirdsql.org' :p -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBW29tvXNTca6JD8RAk20AJ9gkpFxEH0bL+F6tVdxiGu1DqHliACfTudN dWxKIuALUppjnw2jvO/UXV0= =N0BN -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
thank you for the suggestion, I will give that a try. I thought it suspicious that the table stopped receiving data at 2 bytes under the natural 4G limit (8 byte int) which was standard under 3.22. As I said, I am using a development release and I have found 1 or 2 other regression errors along the way. On Mon, 2004-07-26 at 14:19, Paul DuBois wrote: At 12:48 -0400 7/26/04, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? Are you using MyISAM tables? If so, you probably want to specify MAX_ROWS and/or AVG_ROW_LENGTH table options when you create the tables so that larger internal row pointers get used: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html For existing tables, you can use ALTER TABLE to change the option values. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL for detecting if Column/Index already exists?
I looked at the SHOW COLUMNS statement and they have what I want, but I need to condition off them. For example, I don't want to execute an ALTER TABLE command to add a column if that column is already there. I've tried IF DOES NOT EXIST SHOW COLUMN ... followed by the ALTER TABLE command, but the if check doesn't seem to work on SHOW COLUMNS. Am I missing something, or is this just something that is not supported? Thanks Shishir Ghate -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 2:21 PM To: Ghate, Shishir; [EMAIL PROTECTED] Subject: Re: SQL for detecting if Column/Index already exists? At 14:08 -0500 7/26/04, Ghate, Shishir wrote: Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. You could use the SHOW COLUMNS and SHOW INDEX statements? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
I apologize for my skepticism of 15 minutes ago. I finally _read_ http://dev.mysql.com/doc/mysql/en/Table_size.html carefully, and indeed your suggestion is dead on. thank you again. On Mon, 2004-07-26 at 14:19, Paul DuBois wrote: At 12:48 -0400 7/26/04, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? Are you using MyISAM tables? If so, you probably want to specify MAX_ROWS and/or AVG_ROW_LENGTH table options when you create the tables so that larger internal row pointers get used: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html For existing tables, you can use ALTER TABLE to change the option values. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to show comments/annotations in MySQL client output
I run some mysql command files (just SQL statements in a file I read from standard input) and need to place some annotiations/comments in the output. If I place standard SQL comments (-- comment text) or MySQL comments (# comment text) they do not show up in the mysql client output. Well, in a way that makes sense - they are comments. I have tried using select ' comment text' ; and that works, but I get many, many lines instead of my one simple annotation - e.g.: -- select First comment ... -- +---+ | First comment ... | +---+ | First comment ... | +---+ 1 row in set (0.00 sec) Any/all ideas are appreciated - Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using mysql in commercial software
looks like the answer is no. As soon as fee based software touches the mysql install on the PC, the user is obligated to pay the $250. At least I guess it is the user who has to pay. Because once you pay for the mysql install, you can use as many fee based applications as you want. -Steve -Original Message- From: Laercio Xisto Braga Cavalcanti [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 3:06 PM To: 'gerald_clark'; 'Steve Richter' Cc: 'chat. mysql.' Subject: RE: using mysql in commercial software Hi all, If I write a comercial software that allow my customer at instalation time to select between mysql, interbase or other data base is it legal? Regards, Laercio. -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 26 de julho de 2004 15:26 To: Steve Richter Cc: chat. mysql. Subject: Re: using mysql in commercial software Steve Richter wrote: -Original Message- From: Michael Abbott [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: RE: using mysql in commercial software This may not be strictly legal, but you could have the end user download MySQL.. to run with your software. exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? -Steve Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select statement inbetween unix timestamp ranges
Hello, I have been pulling my hair out trying to get a SELECT statement to work using a range of Unix timestamps as the criteria. MySQL Version 4.0 SELECT FROM_UNIXTIME(time) FROM srvlog WHERE FROM_UNIXTIME(time = '1080948600') AND FROM_UNIXTIME(time = '1080997876'); I also tried this too: SELECT FROM_UNIXTIME(time) FROM srvlog WHERE FROM_UNIXTIME(time = '1080948600' AND FROM_UNIXTIME(time = '1080997876')); //Change the parenthesis to make one evaluation. Same result. This query returns all rows and does not stop at the '=' parameter of 1080997876. Even when I get this working, how do I display the output in the FROM _UNIXTIME(time, '%b %m %h:%m..etc) so I can control the way the date appears after the Unix time comparison has taken place? The time field is int(11) and this works the same on Linux and Windows so it is not an issue with the OS or the environment (unless I am missing something simple). I have read the MySQL Date and Time page but not quite found what I am looking for. Any help would be greatly appreciated. Many thanks, Craig.
Re: Select statement inbetween unix timestamp ranges
Craig Hibbert wrote: SELECT FROM_UNIXTIME(time) FROM srvlog WHERE FROM_UNIXTIME(time = '1080948600') AND FROM_UNIXTIME(time = '1080997876'); Why do you have FROM_UNIXTIME() in the WHERE clause? You said the time column was already in Unix time, and regardless, you're passing the function the result of a logical operator (which evaluates to 0 or 1) rather than a timestamp anyway. It appears that what you want is SELECT FROM_UNIXTIME(time) FROM srvlog WHERE time = 1080948600 AND time = 1080997876; or (a shorter alternative) SELECT FROM_UNIXTIME(time) FROM srvlog WHERE time BETWEEN 1080948600 AND 1080997876; -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql libs and multiple hostnames
On Mon, Jul 26, 2004 at 04:09:51PM -0400, Charles Sprickman wrote: I have a quick question about how programs linked against the mysql C libraries handle the following: -assume three mysql hosts, say 10.0.0.2, 10.0.0.3, and 10.0.0.4 -assume a dns name db.example.com that returns the following: [EMAIL PROTECTED] host db.example.com db.example.com has address 10.0.0.2 db.example.com has address 10.0.0.3 db.example.com has address 10.0.0.4 If my client program repeatedly connects to db.example.com and my nameserver round-robins through those IPs, will the client also keep cycling through those? What is the behaviour if one of those hosts does not respond? Will the client application then try the next one? The MySQL C client libarary doesn't treat this case specially. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_safe
OK, I ran sh mysqld_safe on my computer and it said Starting mysqld-max deamon., went to the next line on screen and didn't give me a command line, which leads me to think one of two things happened: 1. the server started successfully. 2. the server startup faild and locked up. I'm hoping it is no. 1 that happened. if so, how do I get a command prompt after I start the server? and if the server does work, how do I get it to startup with Debian Linux?
Re: Re[2]: nested sets - Perhaps a solution!
[how to store userpermissions in a tree in mysql] Hi, after a long time of try and error I have this as a result: mysql select * from node; +-+-+-+-+-+--+--+--+ | node_id | root_id | payload | lft | rgt | a| c| d| +-+-+-+-+-+--+--+--+ | 1 | 0 | master | 1 | 8 |1 |1 |1 | | 2 | 0 | user1 | 2 | 5 |0 |1 |1 | | 3 | 0 | user2 | 6 | 7 |1 |1 |1 | | 4 | 0 | user3 | 3 | 4 |1 |1 |1 | +-+-+-+-+-+--+--+--+ mysql SELECT node1.payload,COUNT(*) AS level FROM node AS node1, node as node2 where node1.lft between node2.lft and node2.rgt group by node1.lft; +-+---+ | payload | level | +-+---+ | master | 1 | | user1 | 2 | | user3 | 3 | | user2 | 2 | +-+---+ mysql select min(node2.a),min(node2.c),min(node2.d) from node as node1, node as node2 where (node1.lft between node2.lft and node2.rgt) and node1.payload = user3; +--+--+--+ | min(node2.a) | min(node2.c) | min(node2.d) | +--+--+--+ |0 |1 |1 | +--+--+--+ mysql select min(node2.a),min(node2.c),min(node2.d) from node as node1, node as node2 where (node1.lft between node2.lft and node2.rgt) and node1.payload = user1; +--+--+--+ | min(node2.a) | min(node2.c) | min(node2.d) | +--+--+--+ |0 |1 |1 | +--+--+--+ mysql select min(node2.a),min(node2.c),min(node2.d) from node as node1, node as node2 where (node1.lft between node2.lft and node2.rgt) and node1.payload = user2; +--+--+--+ | min(node2.a) | min(node2.c) | min(node2.d) | +--+--+--+ |1 |1 |1 | +--+--+--+ Can someone have a look at it? Is it right? If it is right - how much cpu will this type of query cost if I have 1 userids? Thanks, Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transferring MySQL db from RedHat to Debian
hello list! i'll be moving from a RedHat server to a Debian server very soon. despite the different linux distributions, is it ok to transfer my entire mysql database by just copying everything in /var/lib/mysql of the RedHat system to the Debian system? has anyone tried this before? the reason why i don't want to do the database transfer using data generated by mysqldump is because i want all the auto-generated record_ids to stay the same in the new system. any help will be greatly appreciated. thanks! shannon - Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish.
help query analysis
I am trying to analyze a query that is taking forever. I am new to this so or rather desperate. I would assume that my query would be faster, if the event id (*.cid) Primary key were used to search for rows in the iphdr table with the same event id, but I don't think that is happening. Could some one tell me if my assumption is correct and how can I get the query to use the primary *.cid keys. It appears that the developer created a table that joined signature, iphdr, and event tables together to solve the problem of speed to create the Acid_event table. I would still like to know how can I improve my query? thank you, Raymond -Original Message- From: Raymond Jacob Sent: Monday, July 26, 2004 19:50 To: Jacob, Raymond A Jr Subject: query analysis version: MySQL-Max-3.23.58-1 DESC iphdr; DESC event; DESC signature; DESC acid_event EXPLAIN select count(ip_dst) as ip_dst_count, inet_ntoa(ip_dst) from iphdr, event , signature where ( event.timestamp now() - interval 24 hour ) and (event.cid = iphdr.cid and event.signature = signature.sig_id and signature.sig_sid = 1432 ) group by ip_dst order by ip_dst_count desc limit 100; Iphdr Field|Type|Null|Key|Default|Extra sid|int(10) unsigned||PRI|0| cid|int(10) unsigned||PRI|0| ip_src|int(10) unsigned||MUL|0| ip_dst|int(10) unsigned||MUL|0| ip_ver|tinyint(3) unsigned|YES||NULL| ip_hlen|tinyint(3) unsigned|YES||NULL| ip_tos|tinyint(3) unsigned|YES||NULL| ip_len|smallint(5) unsigned|YES||NULL| ip_id|smallint(5) unsigned|YES||NULL| ip_flags|tinyint(3) unsigned|YES||NULL| ip_off|smallint(5) unsigned|YES||NULL| ip_ttl|tinyint(3) unsigned|YES||NULL| ip_proto|tinyint(3) unsigned|||0| ip_csum|smallint(5) unsigned|YES||NULL| event: Field|Type|Null|Key|Default|Extra sid|int(10) unsigned||PRI|0| cid|int(10) unsigned||PRI|0| signature|int(10) unsigned||MUL|0| timestamp|datetime||MUL|-00-00 00:00:00| Signature: Field|Type|Null|Key|Default|Extra sig_id|int(10) unsigned||PRI|NULL|auto_increment sig_name|varchar(255)||MUL|| sig_class_id|int(10) unsigned||MUL|0| sig_priority|int(10) unsigned|YES||NULL| sig_rev|int(10) unsigned|YES||NULL| sig_sid|int(10) unsigned|YES||NULL| Acid_event: Field TypeNullKey Default Extra sid int(10) unsignedPRI 0 cid int(10) unsignedPRI 0 signature int(10) unsignedMUL 0 sig_namevarchar(255)YES MUL NULL sig_class_idint(10) unsignedYES MUL NULL sig_priorityint(10) unsignedYES MUL NULL timestamp datetimeMUL -00-00 00:00:00 ip_src int(10) unsignedYES MUL NULL ip_dst int(10) unsignedYES MUL NULL ip_protoint(11) YES MUL NULL layer4_sportint(10) unsignedYES MUL NULL layer4_dportint(10) unsignedYES MUL NULL Query Analysis table|type|possible_keys|key|key_len|ref|rows|Extra event|range|sig,time|time|8|NULL|39382|where used; Using temporary; Using filesort iphdr|ALL|NULL|NULL|NULL|NULL|375383|where used signature|eq_ref|PRIMARY|PRIMARY|4|event.signature|1|where used cartesian product= 14,783,333,306 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW INNODB STATUS
Mark, How is it possible to have a hit rate of 1000/1000? Doesn't the buffer get initialized by cache misses? -- Matt [EMAIL PROTECTED] Re: INNODB SHOW STATUS From: Marc Slemko (marcsznep.com) Date: Wed Apr 21 2004 - 10:29:44 CDT On Tue, 20 Apr 2004, Emmett Bishop wrote: Howdy all, Quick question about what I'm seeing in the BUFFER POOL AND MEMORY section... I've configured the innodb_buffer_pool_size to be 128M and when I do a show variables like 'innodb%' I see | innodb_buffer_pool_size | 134217728 | So that looks good. However, I see the following in the BUFFER POOL AND MEMORY section of the output from the innodb monitor: -- BUFFER POOL AND MEMORY -- Total memory allocated 152389988; in additional pool allocated 1048576 Buffer pool size 8192 Free buffers 0 Database pages 7947 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 20345325, created 9857, written 763089 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Why does it say the buffer pool size is only 8M? Shouldn't it be 128M? Also, could someone explain the hit rate? I remember seeing in someone's recent post that the 1000/1000 is good, but I don't know what that means. Can someone suggest a good resouce that explains the contents of Innodb show status in detail. The page on www.mysql.com gives a very cursory overview of the output. Buffer pool size, free buffers, database pages, and modified database pages are in 16k pages. The buffer pool hit rate simply says the fraction of page reads satisfied from the innodb buffer cache, in this case 1000/1000 == 100%. Unfortunately, I'm not really aware of a better reference. Perhaps some of this is explained in High Performance MySQL, but I don't have a copy yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW INNODB STATUS
On Mon, 26 Jul 2004 18:03:25 -0700, Matt Solnit [EMAIL PROTECTED] wrote: How is it possible to have a hit rate of 1000/1000? Doesn't the buffer get inOn Mon, 26 Jul 2004 18:03:25 -0700, Matt Solnit [EMAIL PROTECTED] wrote: How is it possible to have a hit rate of 1000/1000? Doesn't the buffer get initialized by cache misses? That is a number after rounding so it may not be exactly 100%, and ISTR it is one of the states that is either reset every time you read or every so many seconds so any misses before then won't be included. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
flush table with read lock
Dear, [EMAIL PROTECTED], Does flush table with read lock timeout? E.g: After we excute flush table with read lock , All of the table in all of the database can't be updated or delete. But if we doesn't execute 'unlock tables',does it release the lock due to time out? Any idea appreciated. Best regards. MaFai [EMAIL PROTECTED] 2004-07-27
select in Mysql 4.0
I have a table like this +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(3) | | PRI | NULL | auto_increment | | idTable | int(3) unsigned | | | 0 | | | title | varchar(150) | YES | | NULL | | | description | varchar(150) | YES | | NULL | | | date | datetime | YES | | NULL | | +---+-+--+-+-++ Is there anyway to make select command like this in Mysql 4.0: select * from #temp where cod in (select max(cod) from #temp group by idtable) order by data desc could someone help me? thanks Fernando -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL for detecting if Column/Index already exists?
At 16:22 -0500 7/26/04, Ghate, Shishir wrote: I looked at the SHOW COLUMNS statement and they have what I want, but I need to condition off them. For example, I don't want to execute an ALTER TABLE command to add a column if that column is already there. I've tried IF DOES NOT EXIST SHOW COLUMN ... followed by the ALTER TABLE command, but the if check doesn't seem to work on SHOW COLUMNS. Am I missing something, or is this just something that is not supported? You can process the output of SHOW COLUMNS in your application language and use the result to construct the ALTER TABLE statement. I assume that you're using some kind of programming API to access MySQL here. If you're asking is this supported using SQL alone? the answer is no. Thanks Shishir Ghate -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 2:21 PM To: Ghate, Shishir; [EMAIL PROTECTED] Subject: Re: SQL for detecting if Column/Index already exists? At 14:08 -0500 7/26/04, Ghate, Shishir wrote: Hello, I am trying to write a db creation script that will create a database as a well as repair an existing database. What I can't seem to find is a set of key words that will allow me to detect if a column or index already exists in a table and thus skip the creation of the column or index. I've found the proper syntax to check for table existance, but I can't find the syntax for column/index existance. If someone could point me to a set of docs or an example SQL command, that would be great. You could use the SHOW COLUMNS and SHOW INDEX statements? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB maintenance
Hi Folks, Being relatively new to InnoDB I have read the books etc. but can't find anything about regular maintenance that may or may not need to be performed on the tablespaces. With other databases I have worked on, a regular dump and load of a database is generally required, on a semi-regular basis, in order to consolidate the data pages and to remove deleted record space thereby improving performance and disk usage. Does InnoDB require something similar? Thanks David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax
Re: mysqld_safe
At 16:50 -0600 7/26/04, Levi Campbell wrote: OK, I ran sh mysqld_safe on my computer and it said Starting mysqld-max deamon., went to the next line on screen and didn't give me a command line, which leads me to think one of two things happened: 1. the server started successfully. 2. the server startup faild and locked up. I'm hoping it is no. 1 that happened. if so, how do I get a command prompt after I start the server? Did you end the command with a character? and if the server does work, how do I get it to startup with Debian Linux? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transferring MySQL db from RedHat to Debian
At 17:39 -0700 7/26/04, Shannon R. wrote: hello list! i'll be moving from a RedHat server to a Debian server very soon. despite the different linux distributions, is it ok to transfer my entire mysql database by just copying everything in /var/lib/mysql of the RedHat system to the Debian system? has anyone tried this before? I'd expect it to work. the reason why i don't want to do the database transfer using data generated by mysqldump is because i want all the auto-generated record_ids to stay the same in the new system. I'd expect this to work, too. mysqldump will dump the values that those auto-generated IDs currently have, and they won't change when you reload them. AUTO_INCREMENT values aren't generated automatically unless you store NULL or zero into them, which won't be the case with mysqldump files. (I assume here that you didn't do something unsupported like change the values to negative numbers or zeros after generating the IDs.) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
