Re: C API first row not being returned from a query
I use :
numRows = mysql_num_rows( Result);
numFields = mysql_num_fields( Result);
for( j=0; j numRows; j++) {
mysql_data_seek( Result, j);
CurrentRow = mysql_fetch_row( Result);
for( k = 0; k numFields; k++)
printf( %s\t, CurrentRow[ k]);
printf( \n);
}
and it works
Try to add
mysql_data_seek( Result, 0);
before your loop.
Santino
At 14:06 -0800 26-10-2003, Carl B. Constantine wrote:
I'm writing an application and have the following SQL Query written in
C/GTK+ code:
select customer_id, phone, last_name, first_name,
company, account_code from customers;
OK, I then issue the following C commands:
results = mysql_store_result(conx);
numRows = mysql_num_rows(results);
g_print(There are %d rows returned\n, numRows);
return(results);
The print shows 7 rows returned, which is correct. I then have a while
loop to step through each row like so:
i = 0;
while (db_row = mysql_fetch_row(results))
{
g_print(getting data...\n);
id = db_row[0];
phone = db_row[1];
last = db_row[2];
first = db_row[3];
company = db_row[4];
account = db_row[5];
row =
g_strconcat(db_row[0],,,db_row[1],,,db_row[2],,,db_row[3],,,db_row[4],
,,db_row[5],0L);
g_print(Row %d is: %s\n,i,row);
i++;
}
OK, the problem is, I don't get the very first row, I only get the last
6 rows. Can anyone tell me WHY this is? It doesn't really make sense.
This code supposedly prints rows 1-7 but really only prints 2-7.
Your help is greatly appreciated.
--
.''`. Carl B. Constantine
: :' : [EMAIL PROTECTED]
`. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8
`- Debian GNU/Linux -- The power of freedom
Claiming that your operating system is the best in the world because more
people use it is like saying McDonalds makes the best food in the world.
--
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]
inittab
Hi how can i write star tine to inittab (in AIX 4,3) for auto-star mysql during star-up server? Tomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Linker Problem
I wrote a small C program that execute a query (show processlist). If the name of the file is *.c all works fine. If the name of the file is *.cp the linker gives an error: gcc -g -I'/usr/include/mysql' -c Santino.cp gcc Santino.o -L'/usr/lib' -lmysqlclient -lz -o test Santino.o(.eh_frame+0x11): In function `main': /home/santino/java/mysql/top/Santino.cp:75: undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status If I remove all MySql calls in Santino.cp all works fine. Can someone help me? Thanks. Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Supported data types
Lay,
Lay Hoon Tan wrote:
Does MySQL support serial (auto-increment a number) and currency types ?
1. You can create an auto-increment field:
mysql create table t1 (a int not null primary key auto_increment, b char(10));
Query OK, 0 rows affected (0.02 sec)
mysql insert into t1 (b) values ('rec1'),('rec2'),('rec3');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from t1;
+---+--+
| a | b|
+---+--+
| 1 | rec1 |
| 2 | rec2 |
| 3 | rec3 |
+---+--+
3 rows in set (0.01 sec)
2. There is no special MONEY type in MySQL. But you can use DECIMAL
for this purposes. We used DECIMAL(12,2) to store money in an
ISP accounting system several some time ago. It worked very fine.
Hope this helps.
It seems that both are not supported but I would just like to confirm this.
Thank you
--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia
___/ www.mysql.com +7-912-856-80-21
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem using mysql
Hello! My name is Sergiu and I'm a software engineer from Romania. I have a problem using MySql and I hope you can give me an answer at my problem. The MySql server worked without any problem until I installed on the same machine the Visual Studio .Net 7.1 package. After I installed this package, I observed that I can't use my database from MySql. Also, the mysql.exe file for starting the MySql console doesn't work. The MySql server is running, but my databases aren't visible. Can you tell me what the problem can be? Maybe that package installed some components that affect MySql application? I had the same problem using MySql when I tried to install JBuilder application. I thank you in advance for your help. Best regards, Sergiu Muresan
interpreting extended statistics
Hi I am a new MySQL dba tuning a busy Apache/MySQL installation. I could do with some advice from the gurus! Here are some of the statistics for the last 24 hours: Com_admin_commands978,760 What Admin_Commands does this include - at 11.3 per sec this is a lot! Com_analyze 0 The indexes have not been analyzed at all: so the optimizer assumes no data skew. How good is the optimizer: will having accurate stats cause if to scan too much, or is it safe to assume that any stats are good stats? Does everyone out there analyze their tables? Connections 536,998 So it is processing about 6.2 connections per second. Lots. It is configured for a max of 700 connections, and generally has about 300 connection processes. My question is if it has a connection pool (from apache) are these really new connections (i.e. start a new connection process, allocate memory etc) - or is it efficiently re-using existing connections from the pool and just incrementing the counter? Sort_merge_passes 25 Sort_range57,128 Sort_rows 11,368,012 Sort_scan 126,770 The machine has a 1MB sort_buffer_size - which for 300-700 connections is 300-700MB that I would prefer to use for the key_buffer. Is there any wany I can tell from these stats that it is safe to reduce the sort_buffer_size? Ive got as much info as I can from newsgroups and the manual: but tuning MySQL seems more like magic than science. Any comments much appreciated!! Mark (p.s. heres the full list of changes for 24 hours: Aborted_clients 11 Aborted_connects 0 Bytes_received726,146,766 Bytes_sent3,574,227,784 Com_admin_commands978,760 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_change_db 1,510,345 Com_change_master 0 Com_check 0 Com_commit1,787 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_delete33,768 Com_drop_db 0 Com_drop_function 0 Com_drop_index0 Com_drop_table0 Com_flush 0 Com_grant 0 Com_insert164,361 Com_insert_select 12 Com_kill 0 Com_load 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_purge 0 Com_rename_table 0 Com_repair Com_replace 20,631 Com_replace_select1 Com_reset 0 Com_restore_table 0 Com_revoke0 Com_rollback 0 Com_select5,780,420 Com_set_option103 Com_show_binlogs 0 Com_show_create 103 Com_show_databases0 Com_show_fields 103 Com_show_grants 0 Com_show_keys 0 Com_show_logs 0 Com_show_master_status0 Com_show_open_tables 0 Com_show_processlist 0 Com_show_slave_status 0 Com_show_status 4 Com_show_innodb_status0 Com_show_tables 3 Com_show_variables0 Com_slave_start 0 Com_slave_stop0 Com_truncate 0 Com_unlock_tables 0 Com_update895,584 Connections 536,998 Created_tmp_disk_tables 7,928 Created_tmp_tables57,552 Created_tmp_files 50 Delayed_insert_threads0 Delayed_errors0 Flush_commands0 Handler_delete157,555 Handler_read_first19,825 Handler_read_key 31,733,152 Handler_read_next 1,109,099,218 Handler_read_prev 0 Handler_read_rnd 9,864,713 Handler_read_rnd_next -102,644,059 Handler_update3,657,710 Handler_write 9,068,562 Key_blocks_used 0 Key_read_requests 123,092,508 Key_reads 1,090 Key_write_requests1,221,214 Key_writes470,310 Max_used_connections 0 Not_flushed_key_blocks0 Not_flushed_delayed_rows 0 Open_tables 0 Open_files-1 Open_streams 0 Opened_tables 256 Questions 8,944,173 Select_full_join 10,210 Select_full_range_join0 Select_range 4,898 Select_range_check2 Select_scan 1,629,106 Slave_running 0 Slave_open_temp_tables0 Slow_launch_threads 0 Slow_queries 1 Sort_merge_passes 25 Sort_range57,128 Sort_rows 11,368,012 Sort_scan 126,770 Table_locks_immediate 7,149,531 Table_locks_waited136,276 Threads_cached0 Threads_created 8,738 Threads_connected 48
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- 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/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Jon, FULLTEXT is far away, unless we find a corporate sponsor. Could your company sponsor the porting? Journaled file systems are just extra overhead for InnoDB, though the overhead seems to be small. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: Jon Hancock [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lähetetty: Monday, October 27, 2003 10:42 AM Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- 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]
cause of table crash
Hello I get the following errors/warnings on a table: Table is marked as crashed Not used space is supposed to be: 3688 but is: 3624 record delete-link-chain corrupted corrupt What can be the cause of this crash? Med vänlig hälsning/Best Regards Datatal Utveckling AB Jonas Gauffin Tel direct: +46 (0) 498 25 30 16 Tel Support: +46 (0) 498 25 30 30 Fax: +46 (0) 498 25 30 99 http://www.datatal.se I try every day, but sometimes I fail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hi, As I have said before, I'm not Heikki, but I'm such a massive geek I'm likely to have one or two useful bits of info for you. :-) 1. You'd have a rough time getting indexes and tables to be seperated out, unless you were willing to set up your various symlinks/hardlinks by hand. Even then, you may be inviting problems. Additionally, no date has been announced for FULLTEXT indexing on InnoDB tables, and Heikki considers it a low priority by the looks of things (not having a go at the god of multiversioned DBs, just making a possibly incorrect observation). 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Hope this helps! Regards, Chris Jon Hancock wrote: Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- 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]
index problem
Hi, We upgrade one of our servers, but physical and mysql from 3.23.55-max-log to 3.23.58-max-log and run into some problems with a few queries. This is the old server: mysql explain select count(id) from table where entered date_sub(now(), INTERVAL 15 DAY) and DAYOFYEAR(entered) = DAYOFYEAR(date_sub(now(), INTERVAL 14 DAY)) and status in (1, 2); ++---++---+-+--+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---++---+-+--+---++ | table | range | IDX_orders_2,IDX_orders_12 | IDX_orders_12 | 4 | NULL | 49681 | where used | ++---++---+-+--+---++ 1 row in set (0.02 sec) What we did in the move was copy the *.frm and *.MYD and head -c 2048 on every *.MYI and then myisamchk -qr table On the new server the _exakt same_ explain query gives me: ++--+---+--+-+--+-++ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-++ | orders | ALL | IDX_orders_12 | NULL |NULL | NULL | 1841825 | where used | ++--+---+--+-+--+-++ 1 row in set (0.00 sec) Looking for explanations. br Robin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User and permissions/grants - HELP!
C. Reeve [EMAIL PROTECTED] wrote: I just installed MySQL version 4 and as usual, trying to set up users and grant permissions is a nightmare. I followed the instructions in the MySQL manual and granted permissions to a user, like below: GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; I can log in with this user, but I can't do anything (i.e. create a database). There is also no mysql database (there is though if I log in as root). Could you connect to the MySQL server? What exactly error messages did you get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
MySQLGUI downloads failure
Hello, i just wasn´t able to download the MySQLGUI (Windows static) under http://www.mysql.de/Downloads/mysqlgui/mysqlgui-win32-static-1.7.5-2.zip Is it still available? Thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing rpm installation to binary version
Hello, I have been testing with an RPM distribution of MySQL version 4.0.15. Now that I have bought a licenced version how do I change over to use the binary (version 4.0.16) instead of the previously installed rpm version? Operating system: Linux Red Hat 7.3 Thanks Manjit This message is confidential and for use by the addressee only. If you are not the correct addressee, then kindly return the message to the sender and delete it from your computer. Internet communications are not secure and TraderMade accepts no responsibility for changes made to this message after it was sent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User and permissions/grants - HELP!
i dont think there's any privilege called ALL PRIVILEGES. You need to say just ALL, like: GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; Enjoy Nitin - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 7:43 PM Subject: Re: User and permissions/grants - HELP! C. Reeve [EMAIL PROTECTED] wrote: I just installed MySQL version 4 and as usual, trying to set up users and grant permissions is a nightmare. I followed the instructions in the MySQL manual and granted permissions to a user, like below: GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; I can log in with this user, but I can't do anything (i.e. create a database). There is also no mysql database (there is though if I log in as root). Could you connect to the MySQL server? What exactly error messages did you get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API first row not being returned from a query
* Santino ([EMAIL PROTECTED]) wrote:
I use :
numRows = mysql_num_rows( Result);
numFields = mysql_num_fields( Result);
for( j=0; j numRows; j++) {
mysql_data_seek( Result, j);
CurrentRow = mysql_fetch_row( Result);
for( k = 0; k numFields; k++)
printf( %s\t, CurrentRow[ k]);
printf( \n);
}
and it works
Try to add
mysql_data_seek( Result, 0);
I'll remember the mysql_data_seek call. However, I did find my problem.
It seems I was calling mysql_fetch_row( Result) once BEFORE returning to
my calling routing to fetch the rows out, thus I was only getting the
last 6 rows. DOH!
Thanks for the help.
--
.''`. Carl B. Constantine
: :' : [EMAIL PROTECTED]
`. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8
`- Debian GNU/Linux -- The power of freedom
Claiming that your operating system is the best in the world because more
people use it is like saying McDonalds makes the best food in the world.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert ... Select question
Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing rpm installation to binary version
you can either erase the previous installation and then install the newer one or simply upgrade the previous one. - Original Message - From: Manjit Patel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:02 PM Subject: Changing rpm installation to binary version Hello, I have been testing with an RPM distribution of MySQL version 4.0.15. Now that I have bought a licenced version how do I change over to use the binary (version 4.0.16) instead of the previously installed rpm version? Operating system: Linux Red Hat 7.3 Thanks Manjit This message is confidential and for use by the addressee only. If you are not the correct addressee, then kindly return the message to the sender and delete it from your computer. Internet communications are not secure and TraderMade accepts no responsibility for changes made to this message after it was sent. -- 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: Insert ... Select question
of course, the syntax is: insert into db3.table2 (column list) select column list from db1.table1 for more information have a look at http://www.mysql.com/doc/en/INSERT_SELECT.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:28 PM Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing table properties
I have a program running with MySQL as the database. I have MySQLFront running as my viewer and I want to change something in the database that will allow me to view more items in a drop down table within my program. Not having created this program but being a user on this system, I am not sure what would I look for to change this set up. Right now I am only allowed to view 40 odd items before it doesn't scroll down anymore. I have more items in the specific area but am unable to acess them. Could you offer any info to me as to where or what to look for? Any assistance is helpful as I am new and teaching myself as I go along. Thanks in advance! Krystan _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE FUNCTION problem
Hello, I have created new udf function which converts time from NTP format to timestamp. I compile it with the following command: gcc -Wall -shared -o ntp2timestamp.so ntp2timestamp.cc with no errors. Then I copy this file to /usr/local/mysql (libmysql* files are located here and /etc/ld.so.conf file contains this path as well) and execute the following under mysql: CREATE FUNCTION ntp2timestamp RETURNS STRING SONAME ntp2timestamp.so; and I get an error: ERROR 1126: Can't open shared library 'ntp2timestamp.so' (errno: 22 ntp2timestamp.so: cannot open shared object file: No such file o) MySQL is installed from RPM and I have found that it might be configured with --with-mysqld-ldflags=-all-static instead of --withmysqld-ldflags=-rdynamic and I dought this is the problem but I ahve also found the following sentence in manual: -- cut here -- For mysqld to be able to use UDF functions, you should con gure MySQL with --withmysqld- ldflags=-rdynamic The reason is that to on many platforms (including Linux) you can load a dynamic library (with dlopen()) from a static linked program, which you would get if you are using --with-mysqld-ldflags=-all-static If you want to Chapter 9: Extending MySQL 559 use an UDF that needs to access symbols from mysqld (like the methaphone example in `sql/udf_example.cc' that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). -- cut here -- so is it nessesary to configure it with --with-mysqld-ldflags=-rdynamic? I'd like it to be installed from RPM rather source code, is there another workaround? Thanks in advance. Best Regards, -- George Chelidze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing table properties
u mean, in MySQLFront or other program - Original Message - From: Krystan Daxner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:46 PM Subject: Changing table properties I have a program running with MySQL as the database. I have MySQLFront running as my viewer and I want to change something in the database that will allow me to view more items in a drop down table within my program. Not having created this program but being a user on this system, I am not sure what would I look for to change this set up. Right now I am only allowed to view 40 odd items before it doesn't scroll down anymore. I have more items in the specific area but am unable to acess them. Could you offer any info to me as to where or what to look for? Any assistance is helpful as I am new and teaching myself as I go along. Thanks in advance! Krystan _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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]
unexpected results from query between tables
I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234677 'bob' 'smith' '10:00:00' '11:00:00' 1234677 1235677 'mike' 'williams' '10:00:00' '11:00:00' 12365554447 12365554447 'debra' 'stone' '10:30:00' '11:30:00' 1237446 1237446 'ken' 'marwood' '11:00:00' '12:00:00' 12385585888 12385585888 'bill' 'shireton' '11:15:00' '11:15:00' 12395585888 'laura' 'acree' '11:15:00' '12:15:00' 12405585888 'dora' 'lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234677 'bob' 'smith' 1234677 'bob' 'smith' 1235677 'mike' 'williams' 1235677 'mike' 'williams' 1235677 'mike' 'williams' 1235677 'mike' 'williams' 12365554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert ... Select question
Nikos, Yes. Use the following syntax: INSERT INTO db_name.tbl_name (col, col, col) SELECT col, col, col FROM db_name.tbl_name; Regards, Adam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 9:58 AM To: [EMAIL PROTECTED] Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing rpm installation to binary version
I have successfully done this when both versions have been rpm files but how do I upgrade from an rpm to a binary. I have unpacked the binary file installed it but the previous version keeps being started. And when I try to erase the previous version it says it is not installed! -Original Message- From: Nitin [mailto:[EMAIL PROTECTED] Sent: 27 October 2003 15:17 To: Manjit Patel; [EMAIL PROTECTED] Subject: Re: Changing rpm installation to binary version you can either erase the previous installation and then install the newer one or simply upgrade the previous one. - Original Message - From: Manjit Patel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:02 PM Subject: Changing rpm installation to binary version Hello, I have been testing with an RPM distribution of MySQL version 4.0.15. Now that I have bought a licenced version how do I change over to use the binary (version 4.0.16) instead of the previously installed rpm version? Operating system: Linux Red Hat 7.3 Thanks Manjit This message is confidential and for use by the addressee only. If you are not the correct addressee, then kindly return the message to the sender and delete it from your computer. Internet communications are not secure and TraderMade accepts no responsibility for changes made to this message after it was sent. -- 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]
Ann: New Article on SSH Port Forwarding of MyODBC
Hi Everyone; I have published a new article covering SSH port forwarding of MySQL sessions, which can be viewed at http://www.vbmysql.com/articles/sshtunnel.html The article covers connecting a Windows client to a *NIX based server. Anyone with previous experience will probably find the article redundant (although I would not mind feedback), but it should be useful for those without experience in port forwarding. In any case I hope you find it useful. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unexpected results from query between tables
You need a LEFT JOIN select f.controlnum,f.referencenum,f.fname,f.lname from first f, left join second s on f.controlnum = s.controlnum where s.controlnum IS NULL and f.inputtime '07:00:00' Larry Brown wrote: ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unexpected results from query between tables
The simple JOIN (which is what you have requested with the A, B syntax) builds a conceptual table in which every row in A is paired with every row in B, then passes the result on to the WHERE filter. Of course, it doesn't actually do that, because it would take an enormous time, but it mimics that behaviour. Therefore, in the simple join there will be a massive number of rows, but none where the second is null. To get an entry where the second is null is the province of the LEFT JOIN, which forces an entry for every row in the first (left) table even if there is no entry in the second (right) table. You can then use the null-ness of the second table in the WHERE field. Try something on the lines of SELECT f.controlnum, f.referencenum, f.fname, f.lname FROM first f LEFT JOIN ON f.controlnum = s.controlnum WHERE s.controlnum IS NULL AND f.inputtime '07:00:00' |-+--- | | Larry Brown | | | [EMAIL PROTECTED]| | | tworks.com | | | | | | 27/10/2003 15:24| | | | |-+--- --| | | | To: MySQL List [EMAIL PROTECTED] | | cc: | | Subject: unexpected results from query between tables | --| I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00'1234677 1235 677 'mike''williams' '10:00:00' '11:00:00'12365554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00'1237446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00'12385585888 1238 5585888 'bill''shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora''lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --
Re: unexpected results from query between tables
You are using the wrong syntax, try SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; The query, you are using produces cross join while you need to implement left join for your problem. Enjoy Nitin - Original Message - From: Larry Brown [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:54 PM Subject: unexpected results from query between tables I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00' 1234 677 1235 677 'mike' 'williams' '10:00:00' '11:00:00' 1236 5554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00' 1237 446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00' 1238 5585888 1238 5585888 'bill' 'shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora' 'lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- 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: Really slow query (compared with Visual FoxPro)
Chris wrote: Hmm It's just occured to me that you're basically copying and entire table from one place to another. If I recall correctly, FoxPro cheats somewhat in this situation - it just copies the concerned files! Which table type are you using (something I should have asked in the beginning)? Can we see the output of SHOW CREATE TABLE table_name; ? Regards, Chris Hi, I'm using MyISAM tables. BTW, I'm almost done with the optimization chapter from the manual... and I haven't had one of those Eureka! moments... anyway I'll keep trying... any more suggestions? Thanks again. Hector Here's an example of what you requested: mysql show create table tmp; +---++ | Table | Create Table | +---++ | tmp | CREATE TABLE `tmp` (`tel` int(11) default NULL) TYPE=MyISAM CHARSET=latin1 | +---++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: unexpected results from query between tables
Thank you all. The world makes sense again. :) Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 10:54 AM To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: unexpected results from query between tables The simple JOIN (which is what you have requested with the A, B syntax) builds a conceptual table in which every row in A is paired with every row in B, then passes the result on to the WHERE filter. Of course, it doesn't actually do that, because it would take an enormous time, but it mimics that behaviour. Therefore, in the simple join there will be a massive number of rows, but none where the second is null. To get an entry where the second is null is the province of the LEFT JOIN, which forces an entry for every row in the first (left) table even if there is no entry in the second (right) table. You can then use the null-ness of the second table in the WHERE field. Try something on the lines of SELECT f.controlnum, f.referencenum, f.fname, f.lname FROM first f LEFT JOIN ON f.controlnum = s.controlnum WHERE s.controlnum IS NULL AND f.inputtime '07:00:00' |-+--- | | Larry Brown | | | [EMAIL PROTECTED]| | | tworks.com | | | | | | 27/10/2003 15:24| | | | |-+--- --- ---| | | | To: MySQL List [EMAIL PROTECTED] | | cc: | | Subject: unexpected results from query between tables | --- ---| I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00'1234677 1235 677 'mike''williams' '10:00:00' '11:00:00'12365554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00'1237446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00'12385585888 1238 5585888 'bill''shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora''lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- 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:
InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)
On Monday, October 27, 2003, at 07:45 AM, Chris Nolan wrote: 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Just out of curiosity, has anyone been able to get InnoDB to use a raw partition in OSX? When I tried it, it complained about the file already existing. - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about compability of clients and the deamon
Hi all, I am investigating upgrading from our current release of MySql. (We use Red-Hat 7.2 and it ships with 3.23.41). Anyway, I am interested in upgrading to either the latest 3.23 release or the latest stable 4.0 release. My concerns are which is an easier and/or less risky upgrade? We have our own server programs that use mysql embedded libraries to access the database.. since the development rpms have been updated, I assume changes have been made to this stuff -- but is it as easy as just recomplining? Does code typically have to be altered (for either release)? And last but not least, if we install mysql4 on a machine but then want to downgrade server revisions, would they be compatible to each other? IE: If we compile a server against the 4.0 development rpms, could it run against a 3.2 deamon... and vice versa? I've been reading alot about upgrading on the website and it seems to point towards an easy process, but my boss is very nervous about it - and I was hoping to get some 'been-there-done-that' advice from the group. Cheers, --brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Conversion
My commercial online web application currently handles 1 users with peak loadings of 100-500 concurrent hits. Windows 2000 Server, IIS 5.0, JRun 4.0 and Enterprise Database Engine. There is a considerable amount of write activity to 10 of the 60 tables. The total size of the data is 5-10GB. 1) Is a migration to either MySQL or SAPdb appropriate? 2) Are SAPdb and MySQL the same database products? 3) Does either database engine support a Standby database? 4) Can you provide links to performance tuning, backup, and recovery documentation? 5) What is the counterpart of Oracle's archive re-do logs in either SAPdb or MySQL? THANKS Best Regards; CCCary _ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bind params and default values
Hi all, i've been having an issue with binding params. If i have created an INSERT query with a param binding to a particular column, call it column A, is there any way that i can use that (prepared) query to get the db defined default value into column A? Im at a loss! thanks much, sean peters [EMAIL PROTECTED] BTW: i have posted a similar question to perlmonks under the title DBI bind params column defaults -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: reproducible error 17
Hi, *** 1. row *** Master_Host: 10.2.12.224 Master_User: rep Master_Port: 3306 Connect_retry: 60 Master_Log_File: ef224-bin.020 Read_Master_Log_Pos: 409223612 Relay_Log_File: ef242-relay-bin.016 Relay_Log_Pos: 330063332 Relay_Master_Log_File: ef224-bin.020 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 25 Last_error: Error 'Can't create symlink './ff_recent/#sql-17f7_c.MYI' pointing at '/var/lib/mysql/ff_recent/#sql-17f7_c.MYI' (Error 17)' on query 'ALTER TABLE recentmembers_20 add have_children tinyint unsigned not NULL default 0'. Default database: 'ff_recent' Skip_counter: 0 Exec_master_log_pos: 348632736 Relay_log_space: 390689457 Are the master and slave located on two different machines, or on one single machine? Are you using symlinks to place tables or databases at other locations than the datadir? If yes: * How did you do it (with manual Unix 'ln -s', with CREATE TABLE ... DATA DIRECTORY= INDEX DIRECTORY=, another way?). * Did you do it the same way on master and slave? Do master and slave have the same tree structure (i.e. do the directories ./ff_recent/ and /var/lib/mysql/ff_recent all exist on the 2 machines?). If you could provide a simple complete testcase like: create a symlinked table on the master by following these steps, then do this ALTER, then do that ALTER, this could surely help us. Thank you. -- For technical support contracts, visit https://order.mysql.com/?ref=mgbi Are you MySQL certified? visit http://www.mysql.com/certification/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Guilhem Bichot [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer /_/ /_/\_, /___/\___\_\___/ Bordeaux, France ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB2 express
Does anyone have much experience with this? How does it stack up to MySQL? I ask because I'm in the middle of preparing a presentation to get MySQL on the 'approved' software list at my workplace, and I will have to address MySQL versus this product (as well as Oracle Express, though it seems to target a different market...) Thanks, Cary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB not restoring from dump file
Hey everyone, First post to the MySQL list so please be gentle. I recently emptied some tables I shouldn't have in a db of mine and I want to restore the data from a dump file made a few days ago. MySQL version is 3.23. This is the command I used to create the dump: mysqldump --opt -u root --password=password dbname dbname.dump I'm trying to restore that file with: mysql -u root -ppassword dbname dbname.dump I'm getting the following error: ERROR 1064 at line 118: You have an error in your SQL syntax near 'unique (email) ) TYPE=MyISAM' at line 21 Someone suggested to me that it might be because I have a reserved word for a column name but I checked this option out and I do not. Where did I go wrong? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Mojtaba Faridzad wrote: BUT my experience: try to change the logic of your report not to retrieve large number of records. user LIMIT to create the reports page by page. this is the best and even better for the user. Ok thanks, but how exactly do I change-the-logic of this query: mysql select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; I also found this in the manual: Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are: * If the use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as this will require us to do much fewer seeks.) Note that if such a query uses |LIMIT| to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result. So, it seems that I REALLY need to change the logic of this query, since it obviusly would do a table scan. Any help would be greatly appreciated! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bind params and default values
---Original Message-
--From: sean peters [mailto:[EMAIL PROTECTED]
--Sent: Monday, October 27, 2003 10:17 AM
--To: [EMAIL PROTECTED]
--Subject: bind params and default values
--
--Hi all, i've been having an issue with binding params. If i have
created
--an
--INSERT query with a param binding to a particular column, call it
column
--A, is there any way that i can use that (prepared) query to get the
db
--defined default value into column A?
Yes don't put it in your list. Mysql will automatically add the default
on the insert if it is not defined. For example
Col Default
A - default 'Hi'
B - default 'Lo'
C - default '0'
my $query = 'INSERT INTO made_up_table (B,C) VALUES (?,?)';
$DBI_dbh-prepare($query);
@data = ('NotLo','1');
$DBI_dbh-execute(@data);
Does this help?
--
--Im at a loss!
--thanks much,
--
--sean peters
--[EMAIL PROTECTED]
--
--BTW: i have posted a similar question to perlmonks under the title
DBI
--bind
--params column defaults
--
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http://friendfinder.com/go/p40688
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB not restoring from dump file
can you send the contents of your dumpfile up to this point. assuming its line 118 of hte dumpfile and 21 of this build table query Jeff Chris W. Parker [EMAIL PROTECTED]To: [EMAIL PROTECTED] .comcc: Subject: DB not restoring from dump file 10/27/2003 01:45 PM Hey everyone, First post to the MySQL list so please be gentle. I recently emptied some tables I shouldn't have in a db of mine and I want to restore the data from a dump file made a few days ago. MySQL version is 3.23. This is the command I used to create the dump: mysqldump --opt -u root --password=password dbname dbname.dump I'm trying to restore that file with: mysql -u root -ppassword dbname dbname.dump I'm getting the following error: ERROR 1064 at line 118: You have an error in your SQL syntax near 'unique (email) ) TYPE=MyISAM' at line 21 Someone suggested to me that it might be because I have a reserved word for a column name but I checked this option out and I do not. Where did I go wrong? Thanks, Chris. -- 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: DB not restoring from dump file
---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 AM --To: [EMAIL PROTECTED] --Subject: DB not restoring from dump file -- --Hey everyone, -- --First post to the MySQL list so please be gentle. -- -- --I recently emptied some tables I shouldn't have in a db of mine and I --want to restore the data from a dump file made a few days ago. -- --MySQL version is 3.23. -- --This is the command I used to create the dump: -- --mysqldump --opt -u root --password=password dbname dbname.dump -- --I'm trying to restore that file with: -- --mysql -u root -ppassword dbname dbname.dump -- --I'm getting the following error: -- --ERROR 1064 at line 118: You have an error in your SQL syntax near --'unique (email) --) TYPE=MyISAM' at line 21 -- -- --Someone suggested to me that it might be because I have a reserved word --for a column name but I checked this option out and I do not. -- --Where did I go wrong? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 Look at that line and make sure there is not a DOS character line in it. This is what I can think of off the top of my head. Could you provide line 21 and a few lines above that? It could be a missed comma. -- -- --Thanks, --Chris. -- --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: Conversion
---Original Message- --From: Charles Cary [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:10 AM --To: [EMAIL PROTECTED] --Subject: Conversion -- --My commercial online web application currently handles 1 users with --peak --loadings of 100-500 concurrent hits. Windows 2000 Server, IIS 5.0, JRun --4.0 --and Enterprise Database Engine. There is a considerable amount of write --activity to 10 of the 60 tables. The total size of the data is 5-10GB. -- --1) Is a migration to either MySQL or SAPdb appropriate? Sure mySQL can handle that with no problem given a sizeable dedicated server, --2) Are SAPdb and MySQL the same database products? Hmm sortof from my understanding but not really. Helpful huh? --3) Does either database engine support a Standby database? What do you mean by standby database? Replication? If it's replication, then yes it can. --4) Can you provide links to performance tuning, backup, and recovery --documentation? Goto mysql.com. search for tuning or ask your questions here. OR look in mysql_INSTALL_DIR for a directory called support and look at my-huge.cnf --5) What is the counterpart of Oracle's archive re-do logs in either SAPdb --or --MySQL? Can't help you out here. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 10:52 AM said: can you send the contents of your dumpfile up to this point. assuming its line 118 of hte dumpfile and 21 of this build table query Line 118 is the beginning of the 'customers' table definition and line 21 is the last line of that definition. Thanks for you help so far. Chris. DUMP: -- MySQL dump 8.22 -- -- Host: localhostDatabase: aardcart - -- Server version 3.23.56 -- -- Table structure for table 'cart' -- DROP TABLE IF EXISTS cart; CREATE TABLE cart ( id int(10) unsigned NOT NULL auto_increment, phpsessid varchar(32) NOT NULL default '', cust_id int(10) unsigned NOT NULL default '0', created datetime NOT NULL default '-00-00 00:00:00', lastaccessed datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE cart DISABLE KEYS */; -- -- Dumping data for table 'cart' -- LOCK TABLES cart WRITE; INSERT INTO cart VALUES (1,'89ba2322b4b0723bf052ffc2758266a9',6,'2003-10-07 14:50:17','2003-10-10 16:27:49'),(2,'89ba2322b4b0723bf052ffc2758266a9',12,'2003-10-07 16:04:01','2003-10-07 16:05:09'),(10,'cdf88dc1c29adde03b6e3e79942f2a07',0,'2003-10-22 09:48:01','2003-10-22 09:48:01'); /*!4 ALTER TABLE cart ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'cart_contents' -- DROP TABLE IF EXISTS cart_contents; CREATE TABLE cart_contents ( id int(10) unsigned NOT NULL auto_increment, cart_id int(10) unsigned NOT NULL default '0', prod_id varchar(25) NOT NULL default '', price float(4,2) unsigned NOT NULL default '0.00', qty smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE cart_contents DISABLE KEYS */; -- -- Dumping data for table 'cart_contents' -- LOCK TABLES cart_contents WRITE; INSERT INTO cart_contents VALUES (1,1,'testing013',99.00,4),(10,1,'MONUTB',145.00,1),(9,1,'MONUTB',145.00 ,1),(6,1,'MONUTB',145.00,2),(8,1,'ATITCB',194.00,1),(11,10,'testing019', 104.00,1),(12,10,'testing011',99.00,1); /*!4 ALTER TABLE cart_contents ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'cart_contents_attributes' -- DROP TABLE IF EXISTS cart_contents_attributes; CREATE TABLE cart_contents_attributes ( cart_cont_id int(10) unsigned NOT NULL default '0', attr_id smallint(5) unsigned NOT NULL default '0', option_id smallint(5) unsigned NOT NULL default '0', cart_id int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM; /*!4 ALTER TABLE cart_contents_attributes DISABLE KEYS */; -- -- Dumping data for table 'cart_contents_attributes' -- LOCK TABLES cart_contents_attributes WRITE; INSERT INTO cart_contents_attributes VALUES (9,40,118,1),(8,24,4,1),(8,7,17,1),(8,6,14,1),(6,40,118,1),(8,2,8,1),(10 ,40,118,1),(12,39,113,10); /*!4 ALTER TABLE cart_contents_attributes ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'categories' -- DROP TABLE IF EXISTS categories; CREATE TABLE categories ( id int(10) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', lft smallint(5) unsigned NOT NULL default '0', rgt smallint(5) unsigned NOT NULL default '0', description tinytext NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE categories DISABLE KEYS */; -- -- Dumping data for table 'categories' -- LOCK TABLES categories WRITE; INSERT INTO categories VALUES (31,'Riot Gear',98,99,''),(30,'Entry Tools',96,97,''),(29,'Hydration',94,95,''),(28,'Load Bearing',78,93,''),(27,'Gloves',74,77,''),(26,'Apparel',70,73,''),(21,'A rmor',52,53,''),(20,'WMD',24,51,''),(19,'Less-Lethal',6,23,''),(25,'Flas hlights',68,69,''),(24,'Batons',62,67,''),(23,'Duty Gear',56,61,''),(22,'Holsters',54,55,''),(32,'Gear Bags/Cases',100,101,''),(38,'Root',1,102,''),(39,'Nuclear',49,50,''),(40 ,'Biological',45,48,''),(41,'Chemical',27,44,''),(73,'Force Protection',4,5,''),(74,'Batons',2,3,''); /*!4 ALTER TABLE categories ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'customers' -- DROP TABLE IF EXISTS customers; CREATE TABLE customers ( id int(10) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', address1 varchar(40) NOT NULL default '', address2 varchar(40) default '', city varchar(20) NOT NULL default '', state char(2) NOT NULL default '', zip varchar(10) NOT NULL default '', phone varchar(20) default '', fax varchar(20) default '', email varchar(32) NOT NULL default '', newsletter tinyint(1) NOT NULL default '0', password varchar(32) NOT NULL default '', signupdate datetime NOT NULL default '-00-00 00:00:00', lastvisit datetime NOT NULL default '-00-00 00:00:00', type tinyint(3) unsigned NOT NULL default '0', security smallint(6) NOT NULL default '0', company varchar(64) default '', PRIMARY KEY (id), UNIQUE KEY unique (email) ) TYPE=MyISAM;
RE: DB not restoring from dump file
Try changing the keyname unique to email. SO: UNIQUE KEY email (email) ---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 11:04 AM --To: [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: RE: DB not restoring from dump file -- --[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] --on Monday, October 27, 2003 10:52 AM said: -- -- can you send the contents of your dumpfile up to this point. -- assuming its line 118 of hte dumpfile and 21 of this build table query -- --Line 118 is the beginning of the 'customers' table definition and line --21 is the last line of that definition. -- --Thanks for you help so far. -- --Chris. -- -- --DUMP: -- MySQL dump 8.22 Host: localhostDatabase: aardcart --- Server version3.23.56 -- Table structure for table 'cart' -- --DROP TABLE IF EXISTS cart; --CREATE TABLE cart ( -- id int(10) unsigned NOT NULL auto_increment, -- phpsessid varchar(32) NOT NULL default '', -- cust_id int(10) unsigned NOT NULL default '0', -- created datetime NOT NULL default '-00-00 00:00:00', -- lastaccessed datetime NOT NULL default '-00-00 00:00:00', -- PRIMARY KEY (id) --) TYPE=MyISAM; -- --/*!4 ALTER TABLE cart DISABLE KEYS */; -- Dumping data for table 'cart' -- -- --LOCK TABLES cart WRITE; --INSERT INTO cart VALUES --(1,'89ba2322b4b0723bf052ffc2758266a9',6,'2003-10-07 --14:50:17','2003-10-10 --16:27:49'),(2,'89ba2322b4b0723bf052ffc2758266a9',12,'2003-10-07 --16:04:01','2003-10-07 --16:05:09'),(10,'cdf88dc1c29adde03b6e3e79942f2a07',0,'2003-10-22 --09:48:01','2003-10-22 09:48:01'); -- --/*!4 ALTER TABLE cart ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'cart_contents' -- --DROP TABLE IF EXISTS cart_contents; --CREATE TABLE cart_contents ( -- id int(10) unsigned NOT NULL auto_increment, -- cart_id int(10) unsigned NOT NULL default '0', -- prod_id varchar(25) NOT NULL default '', -- price float(4,2) unsigned NOT NULL default '0.00', -- qty smallint(5) unsigned NOT NULL default '0', -- PRIMARY KEY (id) --) TYPE=MyISAM; -- --/*!4 ALTER TABLE cart_contents DISABLE KEYS */; -- Dumping data for table 'cart_contents' -- -- --LOCK TABLES cart_contents WRITE; --INSERT INTO cart_contents VALUES --(1,1,'testing013',99.00,4),(10,1,'MONUTB',145.00,1),(9,1,'MONUTB',145 .00 --,1),(6,1,'MONUTB',145.00,2),(8,1,'ATITCB',194.00,1),(11,10,'testing01 9', --104.00,1),(12,10,'testing011',99.00,1); -- --/*!4 ALTER TABLE cart_contents ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'cart_contents_attributes' -- --DROP TABLE IF EXISTS cart_contents_attributes; --CREATE TABLE cart_contents_attributes ( -- cart_cont_id int(10) unsigned NOT NULL default '0', -- attr_id smallint(5) unsigned NOT NULL default '0', -- option_id smallint(5) unsigned NOT NULL default '0', -- cart_id int(10) unsigned NOT NULL default '0' --) TYPE=MyISAM; -- --/*!4 ALTER TABLE cart_contents_attributes DISABLE KEYS */; -- Dumping data for table 'cart_contents_attributes' -- -- --LOCK TABLES cart_contents_attributes WRITE; --INSERT INTO cart_contents_attributes VALUES --(9,40,118,1),(8,24,4,1),(8,7,17,1),(8,6,14,1),(6,40,118,1),(8,2,8,1), (10 --,40,118,1),(12,39,113,10); -- --/*!4 ALTER TABLE cart_contents_attributes ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'categories' -- --DROP TABLE IF EXISTS categories; --CREATE TABLE categories ( -- id int(10) unsigned NOT NULL auto_increment, -- name varchar(50) NOT NULL default '', -- lft smallint(5) unsigned NOT NULL default '0', -- rgt smallint(5) unsigned NOT NULL default '0', -- description tinytext NOT NULL, -- PRIMARY KEY (id) --) TYPE=MyISAM; -- --/*!4 ALTER TABLE categories DISABLE KEYS */; -- Dumping data for table 'categories' -- -- --LOCK TABLES categories WRITE; --INSERT INTO categories VALUES (31,'Riot Gear',98,99,''),(30,'Entry --Tools',96,97,''),(29,'Hydration',94,95,''),(28,'Load --Bearing',78,93,''),(27,'Gloves',74,77,''),(26,'Apparel',70,73,''),(21 ,'A --rmor',52,53,''),(20,'WMD',24,51,''),(19,'Less-Lethal',6,23,''),(25,'F las --hlights',68,69,''),(24,'Batons',62,67,''),(23,'Duty --Gear',56,61,''),(22,'Holsters',54,55,''),(32,'Gear --Bags/Cases',100,101,''),(38,'Root',1,102,''),(39,'Nuclear',49,50,''), (40 --,'Biological',45,48,''),(41,'Chemical',27,44,''),(73,'Force --Protection',4,5,''),(74,'Batons',2,3,''); -- --/*!4 ALTER TABLE categories ENABLE KEYS */; --UNLOCK TABLES; -- Table structure for table 'customers' -- --DROP TABLE IF EXISTS customers; --CREATE TABLE customers ( -- id int(10) unsigned NOT NULL auto_increment, -- fname varchar(20) NOT NULL default '', -- lname varchar(20) NOT NULL default '', -- address1 varchar(40) NOT NULL default '', -- address2 varchar(40) default '', -- city
illusive query
Earlier I was given help understanding the need for using a left join. This was a precursory query to arrive at my final solution which I had not touched on since I believed that by getting the join correct I could get the result. It seems to be evading me though. Still using the following example table.. I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234677 'bob' 'smith' '10:00:00' '11:00:00' 1234 677 1235677 'mike' 'williams' '10:00:00' '11:00:00' 1236 5554447 12365554447 'debra' 'stone' '10:30:00' '11:30:00' 1238 5585888 1237446 'ken' 'marwood' '11:00:00' '12:00:00' 12385585888 'bill' 'shireton' '11:15:00' '11:15:00' 12395585888 'laura' 'acree' '11:15:00' '12:15:00' 12405585888 'dora' 'lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's whose reference numbers do not match the reference numbers that are linked with the controlnum's from table two together with all of the records in table two. I can't follow that description and I wrote it! Maybe an example... This is the result I want... 1234677 'bob' 'smith' 12365554447 'debra' 'stone' 1237446 'ken' 'marwood' 12385585888 'bill' 'shireton' So the result set does not include a record such as 1235 because it's reference number matches a reference number from a record from the same table referenced by table two. It includes all other records. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: illusive query
---Original Message- --From: Larry Brown [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 11:32 AM --To: MySQL List --Subject: illusive query -- --Earlier I was given help understanding the need for using a left join. --This --was a precursory query to arrive at my final solution which I had not --touched on since I believed that by getting the join correct I could get --the --result. It seems to be evading me though. Still using the following --example table.. -- --I have two tables. One table has entries --controlnum,referencenum,fname,lname,inputtime,outputtime the second table --has controlnum,referencenum. -- --In table one referencenum can have and does have duplicates. The second --table is populated with a subset of data from the first table but --referencenum is unique. For instance... -- --1234677 'bob' 'smith' '10:00:00' '11:00:00' --1234 --677 --1235677 'mike' 'williams' '10:00:00' '11:00:00' --1236 --5554447 --12365554447 'debra' 'stone' '10:30:00' '11:30:00' -- 1238 --5585888 --1237446 'ken' 'marwood' '11:00:00' '12:00:00' --12385585888 'bill' 'shireton' '11:15:00' '11:15:00' --12395585888 'laura' 'acree' '11:15:00' '12:15:00' --12405585888 'dora' 'lindsey' '11:15:00' '12:15:00' -- --ok, now I want to run a query that results in all of the controlnum's --whose --reference numbers do not match the reference numbers that are linked with --the controlnum's from table two together with all of the records in table --two. I can't follow that description and I wrote it! Maybe an --example... --This is the result I want... -- --1234 677 'bob' 'smith' --1236 5554447 'debra' 'stone' --1237 446 'ken' 'marwood' --1238 5585888 'bill' 'shireton' -- --So the result set does not include a record such as 1235 because it's --reference number matches a reference number from a record from the same --table referenced by table two. It includes all other records. Well the hint to let you know what to do is that the data is in the 2 tables AND you want all the NON matching ref numbers. From this hint you need a LEFT OUTER JOIN or LEFT JOIN SELECT t2.* FROM t1 LEFT JOIN t2 ON t1.ref = t2.ref WHERE t2.ref is NULL; Give me everything from t2 where ALL the rows in t2 do not match the rows in t1. -- -- -- --Larry S. Brown --Dimension Networks, Inc. --(727) 723-8388 -- -- -- --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]
My Company DB Wars
My old Micro-Economics professor must be chortling in his grave... The bosses told me a few minutes ago to quit pusing MySQL for an internal project and to move my proof-of-concept tables from MySQL running on a Linux desktop with 512mb of ram and the bloody DB on an external USB to a DB2 database running on a Win2K server with 1GB ram and a 120 GB raid system. The reason? TPTB simply can't believe that a licensed MySQL system at $450 for the base license, no connected user fees and $2,500 per year for advanced support (we need InnoDB, FK constraints and transaction safe tables) can possibly be as good as DB2 at a minimum of 5 times the software cost. Afterall, DB2 has triggers and stored procedures (although nobody -- including the contractors actually doing the coding -- can point to a single stored procedure or trigger that is essential to the project). The contractors ARE making fairly extensive use of views but I've already figured out three ways around them. Go figger. I will continue to use MySQL for testbedding. Also, since I'm the gate-keeper for the contractor's code, I'm going to keep their stuff as generic as possible. BTW, one question, does MySQL run on the AS400? Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bind params and default values
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: sean peters [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 11:54 AM --To: Dathan Vance Pattishall --Subject: Re: bind params and default values --INSERT INTO my_table (A, B) VALUES ( COALESCE(?, A), COALESCE(?, B) ) -- --I think this should work, but am now wondering how much time this will --actually save me. I say this because in this situation, even though the --query --is preparsed, mysql still needs to calculate the coalesce values each --time. mySQL currently does not respect prepare, although 5.0 will. So, it will not save you anytime. -- --Im really weighing potential time tradeoffs here. -- --My other thought is to create prepared queries for each combination of --fields --that will show up for my insert queries. Because of the nature of my --task, --there should only be about 10-20 queries for each table for each process, --which isnt horrible to store, but then my perl script needs to take the --time --to decide which cached query to use, and again, i think that i haven't --gained --anything timewise. Well remember for your proposal to work in the manner stated above you will need to have a statement handle, thus you need a connection established to the mysql server for the 10-20 SQL commands. 10-20 connections remaining persistent can become a problem later on if your concern about scale. Also since mysql does not support prepare like Oracle does YET, you might just be better off looking for speed in other areas such as tuning the mysql server or looking at your table structure: rem keys etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My Company DB Wars
My old Micro-Economics professor must be chortling in his grave... The bosses told me a few minutes ago to quit pusing MySQL for an internal project and to move my proof-of-concept tables from MySQL running on a Linux desktop with 512mb of ram and the bloody DB on an external USB to a DB2 database running on a Win2K server with 1GB ram and a 120 GB raid system. The reason? TPTB simply can't believe that a licensed MySQL system at $450 for the base license, no connected user fees and $2,500 per year for advanced support (we need InnoDB, FK constraints and transaction safe tables) can possibly be as good as DB2 at a minimum of 5 times the software cost. Oh dear the classic case of , if its more expensive it must be good right ? Afterall, DB2 has triggers and stored procedures something i am waiting for (although nobody -- including the contractors actually doing the coding -- can point to a single stored procedure or trigger that is essential to the project). The contractors ARE making fairly extensive use of views but I've already figured out three ways around them. and would you like to share how to get around this ?? Go figger. I will continue to use MySQL for testbedding. Also, since I'm the gate-keeper for the contractor's code, I'm going to keep their stuff as generic as possible. BTW, one question, does MySQL run on the AS400? Randy keep up the struggle man. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: My Company DB Wars
Tell them Yahoo, Google and others use mysql in the order of power 50 servers. 50-1000 servers actually. So, with all the great talent @ Yahoo / Google as well as these 2 companies being profitable maybe some things in life are really 2 good to be true. Usually no brain-er debates are the best way to win with managers. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Randy Chrismon [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 11:51 AM --To: [EMAIL PROTECTED] --Subject: My Company DB Wars -- -- --My old Micro-Economics professor must be chortling in his grave... -- --The bosses told me a few minutes ago to quit pusing MySQL for an --internal project and to move my proof-of-concept tables from MySQL --running on a Linux desktop with 512mb of ram and the bloody DB on an --external USB to a DB2 database running on a Win2K server with 1GB ram --and a 120 GB raid system. The reason? TPTB simply can't believe that --a --licensed MySQL system at $450 for the base license, no connected user --fees and $2,500 per year for advanced support (we need InnoDB, FK --constraints and transaction safe tables) can possibly be as good as --DB2 at a minimum of 5 times the software cost. Afterall, DB2 has --triggers and stored procedures (although nobody -- including the --contractors actually doing the coding -- can point to a single stored --procedure or trigger that is essential to the project). The --contractors ARE making fairly extensive use of views but I've already --figured out three ways around them. -- --Go figger. -- --I will continue to use MySQL for testbedding. Also, since I'm the --gate-keeper for the contractor's code, I'm going to keep their stuff --as generic as possible. -- --BTW, one question, does MySQL run on the AS400? -- --Randy -- --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: My Company DB Wars
It's because you forgot to put the new cover sheet on your TPS (transaction per second, in this case) report Did you get the memo? Similar thing happened to my college... they doubled their tuition over 5 years because as the president of the univeristy put it, 'good colleges are expensive' I'll send you that memo :D Dan Greene -Original Message- From: Randy Chrismon [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 2:51 PM To: [EMAIL PROTECTED] Subject: My Company DB Wars My old Micro-Economics professor must be chortling in his grave... The bosses told me a few minutes ago to quit pusing MySQL for an internal project and to move my proof-of-concept tables from MySQL running on a Linux desktop with 512mb of ram and the bloody DB on an external USB to a DB2 database running on a Win2K server with 1GB ram and a 120 GB raid system. The reason? TPTB simply can't believe that a licensed MySQL system at $450 for the base license, no connected user fees and $2,500 per year for advanced support (we need InnoDB, FK constraints and transaction safe tables) can possibly be as good as DB2 at a minimum of 5 times the software cost. Afterall, DB2 has triggers and stored procedures (although nobody -- including the contractors actually doing the coding -- can point to a single stored procedure or trigger that is essential to the project). The contractors ARE making fairly extensive use of views but I've already figured out three ways around them. Go figger. I will continue to use MySQL for testbedding. Also, since I'm the gate-keeper for the contractor's code, I'm going to keep their stuff as generic as possible. BTW, one question, does MySQL run on the AS400? Randy -- 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]
updating records without changing timestamp fields
hi there, the subject tells almost everything, I try to update records in a table and everytime I do this, the timestamp field ist set to now(). how can I keep the previously stored value for this field (there are values created before)? thanks for your help, henning -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New Article on SSH Port Forwarding of MyODBC
Hi Mike I have to ask this without wanting a howto explanation but at least a basic understanding What this means is that if an unscrupulous individual gets between your client and the server, they can have full access to all information transmitted. how can someone do this? without it being known? Andrew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 27 October 2003 15:39 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Ann: New Article on SSH Port Forwarding of MyODBC Hi Everyone; I have published a new article covering SSH port forwarding of MySQL sessions, which can be viewed at http://www.vbmysql.com/articles/sshtunnel.html The article covers connecting a Windows client to a *NIX based server. Anyone with previous experience will probably find the article redundant (although I would not mind feedback), but it should be useful for those without experience in port forwarding. In any case I hope you find it useful. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto increment sequence
Is there a way in mYSQL to just define an auto increment sequence rather than defining a field inside a table which is an auto increment. Bsically I need some functionality similarto that in ORACLE where CREATE SEQUENCE seq INCREMENT BY 1 START WITH 1 MINVALUE 1; basically creates a seq that increments by one each time thanks, priyanka _ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My Company DB Wars
Hi, You could bring the attention to www.mysql.com - probably German software company SAP would not integrate with MySQL, if there were any kind of problems. Further You could look at the success stories ( http://www.mysql.com/press/user_stories/index.html ), and You will find NASA, Yahoo! Finance among others. best regards Peter - Original Message - From: Randy Chrismon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:50 PM Subject: My Company DB Wars My old Micro-Economics professor must be chortling in his grave... The bosses told me a few minutes ago to quit pusing MySQL for an internal project and to move my proof-of-concept tables from MySQL running on a Linux desktop with 512mb of ram and the bloody DB on an external USB to a DB2 database running on a Win2K server with 1GB ram and a 120 GB raid system. The reason? TPTB simply can't believe that a licensed MySQL system at $450 for the base license, no connected user fees and $2,500 per year for advanced support (we need InnoDB, FK constraints and transaction safe tables) can possibly be as good as DB2 at a minimum of 5 times the software cost. Afterall, DB2 has triggers and stored procedures (although nobody -- including the contractors actually doing the coding -- can point to a single stored procedure or trigger that is essential to the project). The contractors ARE making fairly extensive use of views but I've already figured out three ways around them. Go figger. I will continue to use MySQL for testbedding. Also, since I'm the gate-keeper for the contractor's code, I'm going to keep their stuff as generic as possible. BTW, one question, does MySQL run on the AS400? Randy -- 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: updating records without changing timestamp fields
That is the purpose of timestamp. Henning Heil wrote: hi there, the subject tells almost everything, I try to update records in a table and everytime I do this, the timestamp field ist set to now(). how can I keep the previously stored value for this field (there are values created before)? thanks for your help, henning -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating records without changing timestamp fields
On 27 Oct 2003 at 21:21, Henning Heil wrote: how can I keep the previously stored value for this field (there are values created before)? Explicitly SET timestamp_column = timestamp_column. See here: http://www.mysql.com/doc/en/DATETIME.html But if you never want the TIMESTAMP column to update automatically, you shouldn't be using TIMESTAMP in the first place. You probably want DATETIME instead. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating records without changing timestamp fields
use timestamp_col = timestamp_col in your query, to override the NOW() affect. I just passed by this comment this morning http://www.mysql.com/doc/en/DATETIME.html -- user comments at bottom of page hth Jeff Henning Heil [EMAIL PROTECTED]To: MySQL List [EMAIL PROTECTED] com cc: Subject: updating records without changing timestamp fields 10/27/2003 03:21 PM hi there, the subject tells almost everything, I try to update records in a table and everytime I do this, the timestamp field ist set to now(). how can I keep the previously stored value for this field (there are values created before)? thanks for your help, henning -- 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: updating records without changing timestamp fields
* Henning Heil the subject tells almost everything, I try to update records in a table and everytime I do this, the timestamp field ist set to now(). how can I keep the previously stored value for this field (there are values created before)? You can assign the current value to it: UPDATE table1 SET some_col = 5, other_col = test, timestamp_col = timestamp_col WHERE key_col = 123; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)
Gabriel, - Original Message - From: Gabriel Ricard [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 27, 2003 6:46 PM Subject: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1) On Monday, October 27, 2003, at 07:45 AM, Chris Nolan wrote: 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Just out of curiosity, has anyone been able to get InnoDB to use a raw partition in OSX? When I tried it, it complained about the file already existing. did you add the newraw keyword? http://www.innodb.com/ibman.html#Disk_io_and_raw_devices 12.1 Disk i/o and raw devices Starting from 3.23.41, you can also use a raw disk partition (a raw device) as a data file. When you create a new data file you have to put the keyword newraw immediately after the data file size in innodb_data_file_path. The partition must be equal to or larger than the size you specify. Note that 1M in InnoDB is 1024 x 1024 bytes, while in disk specifications 1 MB usually means 1000 000 bytes. innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw When you start the database again you MUST change the keyword to raw. Otherwise InnoDB will write over your partition! Starting from 3.23.44, as a safety measure InnoDB prevents a user from modifying data when any partition with newraw is specified. After you have added a new partition, shut down the database, edit my.cnf replacing newraw with raw, and restart. innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw By using a raw disk you can on Windows and on some Unixes perform non-buffered i/o. In Windows raw disk i/o, starting from 4.1.1, you can allocate a disk partition as a data file like this: innodb_data_home_dir= innodb_data_file_path=//./D::10Gnewraw - Gabriel Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating records without changing timestamp fields
[EMAIL PROTECTED] wrote on 27.10.2003 21:31 use timestamp_col = timestamp_col in your query, to override the NOW() affect. I just passed by this comment this morning http://www.mysql.com/doc/en/DATETIME.html -- user comments at bottom of page hth Jeff all, thanks for your help! sorry i missed the thread this morning, it's hard to have an eye on all the lists :-( good to know what timestamp was originally meant to do, very useful functionality of course (if one knows how it works!). thanks again, henning -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto increment sequence
On Mon, 27 Oct 2003 15:24:37 -0500, Priyanka Gupta [EMAIL PROTECTED] wrote: Is there a way in mYSQL to just define an auto increment sequence rather than defining a field inside a table which is an auto increment. Bsically I need some functionality similarto that in ORACLE where CREATE SEQUENCE seq INCREMENT BY 1 START WITH 1 MINVALUE 1; basically creates a seq that increments by one each time No. Obviously, you can achieve the same result by creating a table with one auto_increment column set to the specifications you need, but MySQL does not have an exact corrolary to Oracle's sequence. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: updating records without changing timestamp fields
Add Two Time stamps. Only one will get update. Mat -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 1:31 PM To: MySQL List Cc: Henning Heil Subject: Re: updating records without changing timestamp fields On 27 Oct 2003 at 21:21, Henning Heil wrote: how can I keep the previously stored value for this field (there are values created before)? Explicitly SET timestamp_column = timestamp_column. See here: http://www.mysql.com/doc/en/DATETIME.html But if you never want the TIMESTAMP column to update automatically, you shouldn't be using TIMESTAMP in the first place. You probably want DATETIME instead. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- 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: My Company DB Wars
Hi, You could bring the attention to www.mysql.com - probably German software company SAP would not integrate with MySQL, if there were any kind of problems. Sorry a bit off topic, speaking of the germans, it seems that PHP and MYsql is prolific in germany, as a widely used choice. I work soley with php and mysql and have been for a good few years now. I was wondering how hard it would be to find work in germany doing this, and how i could go about it ? Sorry for offtopic. There is still alot of limitations, but 4 is a huge step from 3, still waiting for the 4.1 release. I really wish i could code c, to help with the process ;\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating records without changing timestamp fields
On 27-Oct-2003 Henning Heil wrote: hi there, the subject tells almost everything, I try to update records in a table and everytime I do this, the timestamp field ist set to now(). how can I keep the previously stored value for this field (there are values created before)? UPDATE [table] SET [timestamp]=[timestamp], ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: reproducible error 17
---Original Message- --From: Guilhem Bichot [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:55 AM --To: [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: RE: reproducible error 17 -- --Hi, -- --Are the master and slave located on two different machines, or on one --single machine? The master and slave are located on 2 different machines. --Are you using symlinks to place tables or databases at other locations --than the datadir? Yes, in the db directory under the datadir. I have tables which are one name symlinked to the tables which where altered. These fake tables or tables which are synonyms to the src tables did not have any actions performed on them at this moment in time. For example recent_members.frm - /var/lib/mysql/ffadult_recent/recentmembers.frm recent_members.MYI - /var/lib/mysql/ffadult_recent/recentmembers.MYI recent_members.MYD - /var/lib/mysql/ffadult_recent/recentmembers.MYD The tables altered where recentmembers(_*) --If yes: --* How did you do it (with manual Unix 'ln -s', with --CREATE TABLE ... DATA DIRECTORY= INDEX DIRECTORY=, another way?). --* Did you do it the same way on master and slave? Used ln -s -- --Do master and slave have the same tree structure (i.e. do the --directories ./ff_recent/ and /var/lib/mysql/ff_recent all exist on the 2 --machines?). Yes -- --If you could provide a simple complete testcase like: --create a symlinked table on the master by following these steps, then --do this ALTER, then do that ALTER, this could surely help us. -- I wish I could but all my servers ( 56) are in production. I will try late at night, so our users are not affected. Anything for the mysql team, thanks for your hard work. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php temp table question
Does anyone know whether the use of persistent connections with php will allow a temp table created by a script to linger around and cause a problem with the next execution of the script when it tries to create the temp table again? Also if it does present a problem with the next script execution trying to create the temp table again, if I drop the temp table at the end of the script will I still have problems if the script is run by two client in tandem? For instance two people connect, both hit the script at about the same time. One script creates the temp table and before it can drop the table the second script tries to create the table. Will it see the table created by the other script? Again the use of persistent connections would be a the heart of this I would think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New Article on SSH Port Forwarding of MyODBC
Think of your information like a letter in the mail. Well, for this example, think of it even more as a post-card. Your message is written on the outside where the anyone that touches it can read it. This post-card is going through the post office. It is going through mail centers, hubs, etc. Whatever path it needs to take to get from point A to Point B. At anyone of these post offices, if someone wants to read it, they just look at it, read it, then pass it along like normal. How would you ever know someone read it? Regular internet traffic is not encrypted, when you send a request, you send a written request like a post-card. Anyone in the middle could decide to read it. If you encrypt it, then they may have a harder time reading it, they would have to decrypt it. Of course, with the encrypted message, they could say, photocopy your post card, pass the post-card along, then work on figuring out how to read it in their spare time. The better your encryption, the longer it would take them to figure out what it says. But, say your mail system is all internal. You are sending mail from inside your building to inside your building. In this case, if the system is set up right, your mail will go from your office, to another office with-out ever leaving your building, which makes it much more secure. Someone in your own building would have to be devious enough to read your post-cards.(hopefully, your internal mail system would know not to send this message out to the normal post-office, just so the post-office will send it back to you) Anyway, that's my take on it. -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc 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]
php temp table question (for mysql)
Does anyone know whether the use of persistent connections with php will allow a temp table created by a script to linger around and cause a problem with the next execution of the script when it tries to create the temp table again? Also if it does present a problem with the next script execution trying to create the temp table again, if I drop the temp table at the end of the script will I still have problems if the script is run by two client in tandem? For instance two people connect, both hit the script at about the same time. One script creates the temp table and before it can drop the table the second script tries to create the table. Will it see the table created by the other script? Again the use of persistent connections would be a the heart of this I would think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB2 express
I'm resending this, since I never saw it come through the first time around... Does anyone have much experience with this? How does it stack up to MySQL? I ask because I'm in the middle of preparing a presentation to get MySQL on the 'approved' software list at my workplace, and I will have to address MySQL versus this product (as well as Oracle Express, though it seems to target a different market...) Thanks, Cary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
Dathan Vance Pattishall mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 11:28 AM said: Try changing the keyname unique to email. UNIQUE KEY email (email) Thanks, this worked. I ended up having to change two more instances of the same error in different tables. Why would the mysqldump command create a dump file with a syntax error in it? Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I can't figure out what I thought would be a simple query..
All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing GROUP BY
Hi! I've found this in: http://www.mysql.com/information/presentations/presentation-oscon2000-2719/ Instead of doing a lot of |GROUP BY|s on a big table, create summary tables of the big table and query this instead. Would you please tell me how to create summary tables that can help me get this query info: mysql select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; This is the table info: mysql describe traf_oper; ++--+---+--+-+-+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+-+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+-+---+ Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php temp table question
Thanks, I got the answer from a php developer. In case anyone is wondering, according to him the table is dropped at the end of the script execution regardless of whether you use persistent connections or not. -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:04 PM To: MySQL List Subject: php temp table question Does anyone know whether the use of persistent connections with php will allow a temp table created by a script to linger around and cause a problem with the next execution of the script when it tries to create the temp table again? Also if it does present a problem with the next script execution trying to create the temp table again, if I drop the temp table at the end of the script will I still have problems if the script is run by two client in tandem? For instance two people connect, both hit the script at about the same time. One script creates the temp table and before it can drop the table the second script tries to create the table. Will it see the table created by the other script? Again the use of persistent connections would be a the heart of this I would think. -- 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: New Article on SSH Port Forwarding of MyODBC
That is a pretty fair take on network traffic. SSH has strong enough encryption to make the decryption effort not worth it to all but those with supercomputers. As for the internal mail, I think we can agree that some mail would be critical enough to warrant encryption even on an internal network. Mike Quoting Adam Trimeloni [EMAIL PROTECTED]: Think of your information like a letter in the mail. Well, for this example, think of it even more as a post-card. Your message is written on the outside where the anyone that touches it can read it. This post-card is going through the post office. It is going through mail centers, hubs, etc. Whatever path it needs to take to get from point A to Point B. At anyone of these post offices, if someone wants to read it, they just look at it, read it, then pass it along like normal. How would you ever know someone read it? Regular internet traffic is not encrypted, when you send a request, you send a written request like a post-card. Anyone in the middle could decide to read it. If you encrypt it, then they may have a harder time reading it, they would have to decrypt it. Of course, with the encrypted message, they could say, photocopy your post card, pass the post-card along, then work on figuring out how to read it in their spare time. The better your encryption, the longer it would take them to figure out what it says. But, say your mail system is all internal. You are sending mail from inside your building to inside your building. In this case, if the system is set up right, your mail will go from your office, to another office with-out ever leaving your building, which makes it much more secure. Someone in your own building would have to be devious enough to read your post-cards.(hopefully, your internal mail system would know not to send this message out to the normal post-office, just so the post-office will send it back to you) Anyway, that's my take on it. -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc 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: DB not restoring from dump file
It dumps the contents of the db table in its original form. If you upgrade to a new version-mysql will respect the old table format even if some of the column / table / key names are reserved names in the new mysql version. BUT if you try to add back to the mysql server a dump table that has this reserve key / column / table name then it will see it as an error. Hope this makes sense. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 1:32 PM --To: Dathan Vance Pattishall; [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: RE: DB not restoring from dump file -- --Dathan Vance Pattishall mailto:[EMAIL PROTECTED] --on Monday, October 27, 2003 11:28 AM said: -- -- Try changing the keyname unique to email. -- -- UNIQUE KEY email (email) -- --Thanks, this worked. -- --I ended up having to change two more instances of the same error in --different tables. -- --Why would the mysqldump command create a dump file with a syntax error --in it? -- -- -- --Chris. --Don't like reformatting your Outlook replies? Now there's relief! --http://home.in.tum.de/~jain/software/outlook-quotefix/ -- --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: reproducible error 17
On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote: ---Original Message- --From: Guilhem Bichot [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:55 AM --To: [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: RE: reproducible error 17 -- --Hi, -- --Are the master and slave located on two different machines, or on one --single machine? The master and slave are located on 2 different machines. --Are you using symlinks to place tables or databases at other locations --than the datadir? Yes, in the db directory under the datadir. I have tables which are one name symlinked to the tables which where altered. These fake tables or tables which are synonyms to the src tables did not have any actions performed on them at this moment in time. For example recent_members.frm - /var/lib/mysql/ffadult_recent/recentmembers.frm recent_members.MYI - /var/lib/mysql/ffadult_recent/recentmembers.MYI recent_members.MYD - /var/lib/mysql/ffadult_recent/recentmembers.MYD The tables altered where recentmembers(_*) I have run some tests and what triggers the problems is this symlinking. If I understood you correctly, your setup is analogous to mine which is: - the datadir of my MySQL server is /m/data/4/1/ - in this I have a database called test: /m/data/4/1/test/ - I have this in /m/data/4/1/test/ : lrwxrwxrwx1 guilhem qq 24 Oct 27 23:25 tbl_.frm - /m/data/4/1/test/tbl.frm -rw-rw1 guilhem qq 8620 Oct 27 23:30 tbl.frm lrwxrwxrwx1 guilhem qq 24 Oct 27 23:26 tbl_.MYD - /m/data/4/1/test/tbl.MYD -rw-rw1 guilhem qq 84 Oct 27 23:30 tbl.MYD lrwxrwxrwx1 guilhem qq 24 Oct 27 23:26 tbl_.MYI - /m/data/4/1/test/tbl.MYI -rw-rw1 guilhem qq 1024 Oct 27 23:30 tbl.MYI (tbl_ is a synonym for the real tbl table). On my master (no replication) I got: MASTER flush tables; Query OK, 0 rows affected (0.00 sec) MASTER desc tbl; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | a | int(11) | YES | | NULL| | | b | int(11) | YES | | NULL| | | c | int(11) | YES | | NULL| | | d | int(11) | YES | | NULL| | | e | int(11) | YES | | NULL| | | f | int(11) | YES | | NULL| | | g | int(11) | YES | | NULL| | | h | int(11) | YES | | NULL| | | i | int(11) | YES | | NULL| | | k | int(11) | YES | | NULL| | | l | int(11) | YES | | NULL| | | m | int(11) | YES | | NULL| | | n | int(11) | YES | | NULL| | | o | int(11) | YES | | NULL| | | p | int(11) | YES | | NULL| | | q | int(11) | YES | | NULL| | | r | int(11) | YES | | NULL| | | s | int(11) | YES | | NULL| | +---+-+--+-+-+---+ 18 rows in set (0.00 sec) MASTER flush tables; Query OK, 0 rows affected (0.00 sec) MASTER alter table tbl drop s; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 This show that altering the real table is ok. Now we use the synonym (the symbolic link) instead: MASTER desc tbl_; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | a | int(11) | YES | | NULL| | | b | int(11) | YES | | NULL| | | c | int(11) | YES | | NULL| | | d | int(11) | YES | | NULL| | | e | int(11) | YES | | NULL| | | f | int(11) | YES | | NULL| | | g | int(11) | YES | | NULL| | | h | int(11) | YES | | NULL| | | i | int(11) | YES | | NULL| | | k | int(11) | YES | | NULL| | | l | int(11) | YES | | NULL| | | m | int(11) | YES | | NULL| | | n | int(11) | YES | | NULL| | | o | int(11) | YES | | NULL| | | p | int(11) | YES | | NULL| | | q | int(11) | YES | | NULL| | | r | int(11) | YES | | NULL| | +---+-+--+-+-+---+ 17 rows in set (0.00 sec) MASTER alter table tbl_ drop r; ERROR 25: Can't create symlink './test/#sql-1aa7_4.MYI' pointing at '/m/data/4/1/test/#sql-1aa7_4.MYI' (Error 17) MASTER alter table tbl_ drop r; ERROR 25: Can't create symlink './test/#sql-1aa7_4.MYI' pointing at '/m/data/4/1/test/#sql-1aa7_4.MYI' (Error 17) This is the same error as you: it's impossible to alter the synonym. What happens is that ALTER TABLE tbl_ ...: - Creates temporary files
RE: I can't figure out what I thought would be a simple query..
I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- 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]
Unusual date storage requirement
Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. So, my homecooked solution is to use a DATE column, and another column as a flag to denote whether the day-of-month is valid. Then i'll need to properly craft my searches to understand this. I know that this is a horrible break of normalization, etc. but i haven't come up with a better solution. Also note that this is for a data warehouse, and myself and 1 other programmer are the only people who manipulate the data. thanks in advance sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unusual date storage requirement
what about forcing the date to the first of whatever month the sale was in? if it's got a valid date, put that, otherwise, put the first. -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:33 PM To: [EMAIL PROTECTED] Subject: Unusual date storage requirement Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. So, my homecooked solution is to use a DATE column, and another column as a flag to denote whether the day-of-month is valid. Then i'll need to properly craft my searches to understand this. I know that this is a horrible break of normalization, etc. but i haven't come up with a better solution. Also note that this is for a data warehouse, and myself and 1 other programmer are the only people who manipulate the data. thanks in advance sean peters [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]
Re: Unusual date storage requirement
Sorry, i wasn't completely clear. Well, thats actually what i am going to do. A date column won't take anything but a valid date (sort of - feb. 30 is valid). But i still need to know whether the day of month is meaningful or not, for various search / display purposes. My users will raise a major stink if i tell them that the date is the first, but it actually isn't, so the display functionality for the system will check the date validity flag, and if invalid, only display the month and year. The flag field is there for exactly this purpose. thanks sean peters [EMAIL PROTECTED] On Monday 27 October 2003 16:44, John Jolet wrote: what about forcing the date to the first of whatever month the sale was in? if it's got a valid date, put that, otherwise, put the first. -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:33 PM To: [EMAIL PROTECTED] Subject: Unusual date storage requirement Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. So, my homecooked solution is to use a DATE column, and another column as a flag to denote whether the day-of-month is valid. Then i'll need to properly craft my searches to understand this. I know that this is a horrible break of normalization, etc. but i haven't come up with a better solution. Also note that this is for a data warehouse, and myself and 1 other programmer are the only people who manipulate the data. thanks in advance sean peters [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]
Re: Unusual date storage requirement
On Mon, Oct 27, 2003 at 05:32:34PM -0500, sean peters wrote: Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. So, my homecooked solution is to use a DATE column, and another column as a flag to denote whether the day-of-month is valid. Then i'll need to properly craft my searches to understand this. Why not just set the day value to '00' if you don't have a value, and then check that in your client code? That way, no extra columns. I.e. if you don't have a day value, then your DATE will be $sale-date-year . - . $sale-date-month . -00, or whatever. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual date storage requirement
On Mon, 27 Oct 2003 17:58:38 -0500, sean peters [EMAIL PROTECTED] wrote: Well, thats actually what i am going to do. A date column won't take anything but a valid date (sort of - feb. 30 is valid). But i still need to know whether the day of month is meaningful or not, for various search / display purposes. My users will raise a major stink if i tell them that the date is the first, but it actually isn't, so the display functionality for the system will check the date validity flag, and if invalid, only display the month and year. Not sure what kind of feedback you are looking for here, but it seems to me the only other solution would be to actually roll your own date format using separate columns for year, month, and day-of-month. Then you could leave day of month NULL, where appropriate. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why can't innodb_log_file_size change
Trying to increase my innodb_log_file_size I get this message 031027 16:01:02 InnoDB: Data file ./ibdata2 did not exist: new to be created 031027 16:01:02 InnoDB: Setting file ./ibdata2 size to 2000 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 78643200 bytes! 031027 16:01:57 Can't init databases 031027 16:01:57 Aborting 031027 16:01:57 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 031027 16:01:57 /usr/local/mysql/bin/mysqld: Shutdown Complete The mysql doc says this: innodb_log_file_size Size of each log file in a log group in megabytes. Sensible values range from 1M to 1/nth of the size of the buffer pool specified below, where n is the number of log files in the group. The bigger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But bigger log files also mean that recovery will be slower in case of a crash. The combined size of log files must be 4 GB on 32-bit computers. The default is 5M. It doesn't say anything about not being able to increase the size of the log file after its been set. Any work a rounds? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
Re: Unusual date storage requirement
At 5:32 PM -0500 10/27/03, sean peters wrote: Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. No, they're not. Use '00' for the missing parts, e..g., '1970-03-00' or '1948-00-00'. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual date storage requirement
On Mon, 27 Oct 2003 18:34:34 -0600, Paul DuBois [EMAIL PROTECTED] wrote: At 5:32 PM -0500 10/27/03, sean peters wrote: Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. No, they're not. Use '00' for the missing parts, e..g., '1970-03-00' or '1948-00-00'. For future reference, does this effect order by clauses in the way one would expect? (ie., 00 precedes 01). The docs make it seem as though use of invalid dates results in undefined behavior. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual date storage requirement
At 4:41 PM -0800 10/27/03, bluejack wrote: On Mon, 27 Oct 2003 18:34:34 -0600, Paul DuBois [EMAIL PROTECTED] wrote: At 5:32 PM -0500 10/27/03, sean peters wrote: Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. No, they're not. Use '00' for the missing parts, e..g., '1970-03-00' or '1948-00-00'. For future reference, does this effect order by clauses in the way one would expect? (ie., 00 precedes 01). The docs make it seem as though use of invalid dates results in undefined behavior. Can you indicate which part of the docs you're referring to? Thanks. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB not restoring from dump file
Hi, Well, UNIQUE is a reserved word in all versions of MySQL... As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump, by default, does not put backticks around column/index names. Use the -Q or --quote-names option with mysqldump if you want it to. How was the reserved name allowed in the first place? Because it must have had backticks around it -- probably from something stupid like phpMyAdmin that always puts backticks around everything. :-( Matt - Original Message - From: Dathan Vance Pattishall Sent: Monday, October 27, 2003 4:02 PM Subject: RE: DB not restoring from dump file It dumps the contents of the db table in its original form. If you upgrade to a new version-mysql will respect the old table format even if some of the column / table / key names are reserved names in the new mysql version. BUT if you try to add back to the mysql server a dump table that has this reserve key / column / table name then it will see it as an error. Hope this makes sense. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Chris W. Parker [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 1:32 PM --To: Dathan Vance Pattishall; [EMAIL PROTECTED] --Cc: [EMAIL PROTECTED] --Subject: RE: DB not restoring from dump file -- --Dathan Vance Pattishall mailto:[EMAIL PROTECTED] --on Monday, October 27, 2003 11:28 AM said: -- -- Try changing the keyname unique to email. -- -- UNIQUE KEY email (email) -- --Thanks, this worked. -- --I ended up having to change two more instances of the same error in --different tables. -- --Why would the mysqldump command create a dump file with a syntax error --in it? -- -- -- --Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual date storage requirement
On Mon, 27 Oct 2003 19:11:53 -0600, Paul DuBois [EMAIL PROTECTED] wrote:
For future reference, does this effect order by clauses in the way one
would expect? (ie., 00 precedes 01). The docs make it seem as though
use of invalid dates results in undefined behavior.
Can you indicate which part of the docs you're referring to?
Thanks.
Well, I guess I should just say the expected behavior is not
described by the docs. Claims such as these:
The supported range is '1000-01-01 00:00:00' to '-12-31 23:59:59'.
(``Supported'' means that although earlier values might work, there is
no guarantee that they will.)
and
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the
``zero'' value of the appropriate type ('-00-00 00:00:00',
'-00-00', or 00).
along with the many warnings against using values that could be
interpreted as times rather than dates or visa versa under
relaxed interpretation situations would make me very leary
about using anything that is not clearly a valid date, and
somewhat uncertain as to what mysql will do with the data
under the hood.
(all that and much more available at:
http://www.mysql.com/doc/en/DATETIME.html)
-bluejack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't figure out what I thought would be a simple query..
Hi guys, Have you seen the manual page for The Rows Holding the Group-wise Maximum of a Certain Field: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html I think that's what you want to do. You can either use another temporay table, the MAX-CONCAT trick, or the LEFT JOIN ... IS NULL trick from the comment on March 16, 2003. In MySQL 4.1, you could also use a subselect. Keep in mind with the LEFT JOIN ... IS NULL trick, the more duplicate values you have on your non-unique column, the more inefficient it will be. However, it seems like the subselect method would have this problem also. Can someone tell me if this is true or am I thinking wrong? Hmm. Hope that helps. Matt - Original Message - From: Larry Brown Sent: Monday, October 27, 2003 4:29 PM Subject: RE: I can't figure out what I thought would be a simple query.. I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff Sent: Monday, October 27, 2003 4:37 PM Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit Optimization??
Does mysql do any optimization for then one use `select ... limit x,y`? For example, I have table with 200 records and want to do page web interface to this table. When i use `select ... from table limit 150, 20 `, it will need more and more times (on my here is more than 60 sec). well,that is so slowly for the web interface. can any case make it quickly help,plz. Thx for all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption and weird service terminations
Hi Mike, For the corruption, upgrade to 4.0.16, since it may be caused by a corruption bug in versions before 4.0.15. Matt - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 12:07 PM Subject: RE: Corruption and weird service terminations Hi everyone, I'm having some odd intermitent problems with MySQL-nt 4.0.14 on a Win2K server platform that's fully updated with MS service packs and security patches. My large database on this server is having some quite significant corruption problems requiring me to run repair on tables almost every other day. Related to this problem, I am having trouble with the database server service crashing a couple times a day. Is this a problem other people have run in to before and if so, is there a solurion to it? Thanks for any help you can give me. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE FUNCTION problem
Hi George, I think the MySQL-Max RPM is dynamically linked (all -max binaries actually) if you want to give it a try. Hope that helps. Matt - Original Message - From: George Chelidze Sent: Monday, October 27, 2003 9:18 AM Subject: CREATE FUNCTION problem Hello, I have created new udf function which converts time from NTP format to timestamp. I compile it with the following command: gcc -Wall -shared -o ntp2timestamp.so ntp2timestamp.cc with no errors. Then I copy this file to /usr/local/mysql (libmysql* files are located here and /etc/ld.so.conf file contains this path as well) and execute the following under mysql: CREATE FUNCTION ntp2timestamp RETURNS STRING SONAME ntp2timestamp.so; and I get an error: ERROR 1126: Can't open shared library 'ntp2timestamp.so' (errno: 22 ntp2timestamp.so: cannot open shared object file: No such file o) MySQL is installed from RPM and I have found that it might be configured with --with-mysqld-ldflags=-all-static instead of --withmysqld-ldflags=-rdynamic and I dought this is the problem but I ahve also found the following sentence in manual: -- cut here -- For mysqld to be able to use UDF functions, you should con gure MySQL with --withmysqld- ldflags=-rdynamic The reason is that to on many platforms (including Linux) you can load a dynamic library (with dlopen()) from a static linked program, which you would get if you are using --with-mysqld-ldflags=-all-static If you want to Chapter 9: Extending MySQL 559 use an UDF that needs to access symbols from mysqld (like the methaphone example in `sql/udf_example.cc' that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). -- cut here -- so is it nessesary to configure it with --with-mysqld-ldflags=-rdynamic? I'd like it to be installed from RPM rather source code, is there another workaround? Thanks in advance. Best Regards, -- George Chelidze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hanging processes in MySQL 3.23.53
Hi folks, I have a bit of a problem. I'm running 3.23.53 which I've compiled up from source (because the RPMs are not an option for me). I have a process that does a fairly large select statement every 10 minutes - up until a few days ago it was all find and dandy. A few days ago I did a massive delete from one of the tables (getting rid of a lot of old records), and since then things have gone awry. The select statement seems to get stuck in the COPY TO tmp table stage, and starts to back up fairly heavily. Each of the cron-run processes gets to this COPY TO TMP TABLE stage and locks up, which consumes all available slots on the server and the whole things comes to a grinding halt. I've already run an optimize table on the table, and that got rid of all the empty space freed up by the delete. Any ideas why, after the massive delete, things have started slowing right down (or locking up entirely)? THanks for help. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit Optimization??
Hi, Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long as filesort isn't used for an ORDER BY. But your LIMIT 150, 20 will take much longer (assuming filesort isn't used) than, say, LIMIT 1000, 20. This is because it has to scan over 1.5M rows first. It's not really possible to just start at row 150. If you're not joining another table or something where it first needs to know how many rows from the table match, you can do something like this with 2 queries in your code. The first just scans the index which is much faster than scanning the data file. SELECT id FROM table ORDER BY id LIMIT 150, 20; Then take the first and last of those ids and run this query to get the other columns: SELECT * FROM table WHERE id BETWEEN @low_id AND @high_id ORDER BY id; Hope that helps. Matt - Original Message - From: avenger Sent: Monday, October 27, 2003 7:57 PM Subject: Limit Optimization?? Does mysql do any optimization for then one use `select ... limit x,y`? For example, I have table with 200 records and want to do page web interface to this table. When i use `select ... from table limit 150, 20 `, it will need more and more times (on my here is more than 60 sec). well,that is so slowly for the web interface. can any case make it quickly help,plz. Thx for all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cause of table crash
Hi, What version of MySQL are you using? Maybe it's because of a corruption bug in versions 4.0.3 - 4.0.14. Try upgrading to the latest version. Hope that helps. Matt - Original Message - From: Datatal AB - Gauffin, Jonas Sent: Monday, October 27, 2003 6:40 AM Subject: cause of table crash Hello I get the following errors/warnings on a table: Table is marked as crashed Not used space is supposed to be: 3688 but is: 3624 record delete-link-chain corrupted corrupt What can be the cause of this crash? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Partial replicate InnoDB - MyISAM
This thread started as Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1. I may want to have one MySQL server as the Read only Search server. If I did this and I have all InnoDB table on my Master, then could I replicate only certain columns into the MyISAM slave ? Any other efficient ideas on how to do this? thanks, Jon - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Jon Hancock [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:45 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hi, As I have said before, I'm not Heikki, but I'm such a massive geek I'm likely to have one or two useful bits of info for you. :-) 1. You'd have a rough time getting indexes and tables to be seperated out, unless you were willing to set up your various symlinks/hardlinks by hand. Even then, you may be inviting problems. Additionally, no date has been announced for FULLTEXT indexing on InnoDB tables, and Heikki considers it a low priority by the looks of things (not having a go at the god of multiversioned DBs, just making a possibly incorrect observation). 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Hope this helps! Regards, Chris Jon Hancock wrote: Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- 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]
Re: Partial replicate InnoDB - MyISAM
Hmm How much lag time can you afford between the master's contents being updated and the slave being updated? Taking advantage of MyISAM's compressed table features might help performance if that is an issue. Every independant test out there shows that in the cases where InnoDB is slower for reads than MyISAM, the speed differential is quite small. In those cases, the differences come from InnoDB's higher disk space and RAM usage seemingly. Just some of my thoughts. Regards, Chris On Tue, 28 Oct 2003 01:40 pm, Jon Hancock wrote: This thread started as Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1. I may want to have one MySQL server as the Read only Search server. If I did this and I have all InnoDB table on my Master, then could I replicate only certain columns into the MyISAM slave ? Any other efficient ideas on how to do this? thanks, Jon - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Jon Hancock [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:45 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hi, As I have said before, I'm not Heikki, but I'm such a massive geek I'm likely to have one or two useful bits of info for you. :-) 1. You'd have a rough time getting indexes and tables to be seperated out, unless you were willing to set up your various symlinks/hardlinks by hand. Even then, you may be inviting problems. Additionally, no date has been announced for FULLTEXT indexing on InnoDB tables, and Heikki considers it a low priority by the looks of things (not having a go at the god of multiversioned DBs, just making a possibly incorrect observation). 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Hope this helps! Regards, Chris Jon Hancock wrote: Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: Hanging processes in MySQL 3.23.53
Which platform are you using? Which FS? Regards, Chris On Tue, 28 Oct 2003 01:14 pm, Dan Goodes wrote: Hi folks, I have a bit of a problem. I'm running 3.23.53 which I've compiled up from source (because the RPMs are not an option for me). I have a process that does a fairly large select statement every 10 minutes - up until a few days ago it was all find and dandy. A few days ago I did a massive delete from one of the tables (getting rid of a lot of old records), and since then things have gone awry. The select statement seems to get stuck in the COPY TO tmp table stage, and starts to back up fairly heavily. Each of the cron-run processes gets to this COPY TO TMP TABLE stage and locks up, which consumes all available slots on the server and the whole things comes to a grinding halt. I've already run an optimize table on the table, and that got rid of all the empty space freed up by the delete. Any ideas why, after the massive delete, things have started slowing right down (or locking up entirely)? THanks for help. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
