Re: table conversion problems
Sergei Skarupo wrote: Hi Donny, Thanks for your reply. This table only uses ints and floats, but the floats are allowed to be null, which means, as far as I understand, that it's not a fixed row length... What makes you think that? VARCHAR, TEXT, and BLOB are the variable-length column types http://dev.mysql.com/doc/mysql/en/Storage_requirements.html. By the way, Paul DuBois writes about the 4G limit in MySQL, second edition, and does not mention that fixed rows make a difference as far as the size limit is concerned. The 4Gb limit for MyISAM tables is a matter of pointer size. It is easily overcome with the AVG_ROW_LENGTH and MAX_ROWS options. Practically, MyISAM tables can be as large as your OS/filesystem can handle. This is documented in the manual http://dev.mysql.com/doc/mysql/en/Table_size.html, which nakes no mention, that I can see, of variable-length rows changing the situation. In fact, AVG_ROW_LENGTH would be pointless if only tables with fixed-length rows need apply. Could you please point me to a page about copying data in chunks on InnoDB website? http://dev.mysql.com/doc/mysql/en/Converting_tables_to_InnoDB.html In any case, it's kind of too late now -- my alter command is already running :) True. -- Sergei Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication config without stopping master server
Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me the solution. Regards Naveen
Getting rid of duplicates
I read CSV files into a table using
mysqlimport --fields-terminated-by=',' --replace .
That would work fine and, by using the appropriate unique index, it
would deal with the fact that some of the data appears in multiple CSV
files. However, though the same data is in several files, the date
format is not the same in all of them. The result is that there are
duplicate records in the resulting table without violating unique
keys.
I devised a way of adding an additional column and converting the
dates into a proper date format thereby making the duplicates
detectable. There are several ways I can get rid of them:
a) Write out the table using SELECT * DISTINCT INTO OUTFILE with the
appropriate GROUP BY. Then truncate the table and read back from the
OUTFILE using LOAD DATA INFILE.
b) Create a new table with the same structure but with a slightly
different multiple-column unique key. Use REPLACE INTO the new table
and the new key will remove the duplicates. Truncate the original
table, and copy back to the records from the interim table.
There's a major hassle with method a). The OUTFILE has to be removed
before the same file name can be used again and that requires access
privileges to the database directory. I'm not entirely happy with
method b) either since it is not particularly scalable nor generic.
I'd have thought this would be an FAQ, but it appears not so from my
searching. I normally work with a language called S (dialect R) which
handles matrices in a multitude of ways. Removing duplicates is
straightforward in that language, but from what I know about SQL so
far, it is rather complicated in MySQL.
What do other people do with duplicates?
TIA
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI and Mysql error 2006
Le ven 27/08/2004 à 00:37, Yannick Warnier a écrit :
Hi there,
I'm having a problem with MySQL within a Perl::DBI usage. I've turned it
upside-down and cannot find what it's related to.
My Perl script creates multiple databases and populates them with
tables. The problem appears when creating the second database (in the
foreach statement):
#
# Get a database connection to use to create new databases
#
my $tempDSN = 'DBI:mysql:database=test:host=localhost';
our $tempDBH =DBI-connect($tempDSN,$login,$password)
|| die Could not connect to test database: $! ;
#
# Go through a list of databases and create them one by one
#
foreach my $db ( keys(%$list_of_DBs) ) {
#
# Create a DB with the handler we have
#
my $create = $tempDBH-func('createdb',$db,'admin')
||die Could not create database $db : $!;
#
# Connect to the newly created database
#
my $dbDSN = DBI:mysql:database=$db:host=localhost;
my $dbDBH = DBI-connect($dbDSN,$login,$password)
|| die Could not connect to database $db : $!;
#
# Create a set of tables in this database
#
foreach my $table ( keys(%$list_of_tables) ) {
$dbDBH-do(CREATE TABLE ... );
}
#
# Disconnect from the newly created database
#
$dbDBH-disconnect() || die ...;
}
#
# Disconnect from the temporary database connection
#
$tempDBH-disconnect();
So the first database is created with all it's table, but when I get to
the second, I get this error:
Could not create database ...
And when asking more detail with $DBI::errstr:
ERROR: 2006 'MySQL server has gone away'
I've really searched this down in my doc, on the net, and with
DBI-trace(5) but the only thing I figured out is that somehow the
temporary database connection is shut down by something without asking.
But I'm not sure... How could I?
Anyway, if somebody has any idea what this might come from or has come
to something similar in the past, please give me a hint.
I've taken another path and decided to execute mysqladmin to create my
databases.
I find it awful but it works, so...
Thanks anyway,
Yannick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication config without stopping master server
The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me the solution. Regards Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help for error 123
`table` is HEAP type table Sometimes,I do a query of select in `table`,I got the error, mysql error: Record has changed since last read in table 'table' I check the mysql log file, 040827 8:28:06 Got error 123 when reading table './db/table' I need help for this -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with prepared select statements with parameters in where clause
Hi,
I'm having serious trouble getting prepared statements with bound parameters
in the where clause to work over the C API with MySQL 4.1.3. The Bugtracker on
mysql.com has similar bugs for 4.1.2, which are marked as closed and fixed in
4.1.3 so I wanted to make sure that I'm doing things correctly in my code
before fileing an official bug report. Operating system is Linux 2.6.
The query is declared as
SELECT UserID FROM users WHERE Login=? AND Password=?.
I should be getting exactly one row back from mysql, but mysql_stmt_fetch()
just returns MYSQL_NO_DATA.
The same prepared query but with explicitly hardcoded parameters works.
Thanks for any help.
The table in question looks like the following:
mysql show create table users\G
*** 1. row ***
Table: users
Create Table: CREATE TABLE `users` (
`UserID` bigint(20) unsigned NOT NULL auto_increment,
`Login` char(64) NOT NULL default '',
`Password` char(128) NOT NULL default '',
PRIMARY KEY (`UserID`,`Login`,`Password`),
UNIQUE KEY `Login` (`Login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And the code looks like this (altered from the example in section 21.2.7.5):
#include stdlib.h
#include stdio.h
#include string.h
#include mysql/mysql.h
#define STRING_SIZE 50
#define SELECT_SAMPLE SELECT UserID, Login, Password FROM users WHERE Login=? AND
Password=?
int main(void)
{
MYSQL *mysql;
MYSQL_STMT*stmt;
MYSQL_BINDbind[3]; /* results */
unsigned long length[3];
int row_count;
long long int int_data;
char login[STRING_SIZE];
char password[STRING_SIZE];
my_bool is_null[3];
MYSQL_BINDpbind[2]; /* parameters */
unsigned long plength[2];
char *pdata[2];
my_bool p_is_null[2];
mysql = mysql_init(NULL);
mysql_real_connect( mysql, localhost, myuser, mypassword,
mydb, 0, /tmp/mysql-4.1.sock, 0 );
/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, mysql_stmt_init(), out of memory\n);
exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
fprintf(stderr, mysql_stmt_prepare(), SELECT failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, prepare, SELECT successful\n);
plength[0] = plength[1] = STRING_SIZE * sizeof(char);
p_is_null[0] = p_is_null[1] = 0;
pdata[0] = (char*)malloc( STRING_SIZE * sizeof(char) );
pdata[1] = (char*)malloc( STRING_SIZE * sizeof(char) );
/* STRING PARAMETER */
pbind[0].buffer_type= MYSQL_TYPE_STRING;
pbind[0].buffer= (char *)pdata[0];
pbind[0].buffer_length=STRING_SIZE * sizeof(char);
pbind[0].is_null= p_is_null[0];
pbind[0].length= plength[0];
/* STRING PARAMETER */
pbind[1].buffer_type= MYSQL_TYPE_STRING;
pbind[1].buffer= (char *)pdata[1];
pbind[1].buffer_length= STRING_SIZE * sizeof(char);
pbind[1].is_null= p_is_null[1];
pbind[1].length= plength[1];
if( mysql_stmt_bind_param( stmt, pbind ) ) {
fprintf( stderr, mysql_stmt_bind_param() failed\n );
fprintf( stderr, %s\n, mysql_stmt_error( stmt ) );
exit(0);
}
mysql_real_escape_string( mysql, pdata[0], testuser1,
strlen(testuser1)*sizeof(char) );
plength[0] = strlen( pdata[0] ) + 1;
mysql_real_escape_string( mysql, pdata[1], bla, strlen(bla)*sizeof(char) );
plength[1] = strlen( pdata[1] ) + 1;
printf( Executing query with parameters %s and %s\n, pdata[0], pdata[1] );
/* Execute the SELECT query */
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, mysql_stmt_execute(), failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Bind the result buffers for all 4 columns before fetching them */
/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONGLONG;
bind[0].buffer= (char *)int_data;
bind[0].is_null= is_null[0];
bind[0].length= length[0];
/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)login;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= is_null[1];
bind[1].length= length[1];
/* STRING COLUMN */
bind[2].buffer_type= MYSQL_TYPE_STRING;
bind[2].buffer= (char *)password;
bind[2].buffer_length= STRING_SIZE;
bind[2].is_null= is_null[2];
bind[2].length= length[2];
/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind))
{
fprintf(stderr, mysql_stmt_bind_result() failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, mysql_stmt_store_result() failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Fetch all rows */
row_count= 0;
fprintf(stdout, Fetching results ...\n);
while (!mysql_stmt_fetch(stmt))
{
row_count++;
fprintf(stdout, row %d\n, row_count);
/* column 1 */
fprintf(stdout,column1 (bigint) : );
if (is_null[0])
fprintf(stdout, NULL\n);
else
fprintf(stdout, %lld(%ld)\n, int_data, length[0]);
/* column 2 */
fprintf(stdout,
Re: huge innodb data files
Mayuran, Well, I´m not a MySQL expert, but I think that a good configuration in the my.cf file can make it better. Ronan - Original Message - From: Mayuran Yogarajah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 5:37 PM Subject: huge innodb data files Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ? thanks, M -- 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]
Sort by COUNT(field_name) ?
Example: I have a search box on my webpage and all searchstring are saved in a
database.
$foresp = mysql_query(SELECT string, COUNT(string) FROM searchstat GROUP BY string
,$db);
while ($data = mysql_fetch_array($foresp)) {
echo br($data[1]) . str_replace('', 'lt;', $data[string]) . ;
}
However, I would like to write the result out with the most popular search strings
first. Is it possible to sort the output by count(field_name) ?
Med venlig hilsen
Birger Langkjer
Dialogcentret
Re: Slow Queries on Fast Server?
Thanks Brent and Donny, hopefully this info will help get to the root of the problem with the fulltext search. The table structure is very, very simple: mysql describe product_fulltext; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | product_id | int(9) | | PRI | 0 | | | search_text | text | | MUL | | | +-++--+-+-+---+ 2 rows in set (0.00 sec) Space usage : TypeUsage Data502,455 KB Index 440,412 KB Total 942,867 KB Row Statistic : Statements Value Format dynamic Rows3,237,981 Row length ø158 Row size ø 298 Bytes MySQL 4.0.20-standard-log Official MySQL RPM I also calculated the average text feild length: mysql SELECT AVG(LENGTH(search_text)) AS avg_length FROM product_fulltext; ++ | avg_length | ++ | 147.2239 | ++ 1 row in set (33.34 sec) Is my average text length too long? Is MySQL 4.0.20 really that slow for fulltext searching? If so, how much will performance increase by upgrading to 4.1.x? Is upgrading difficult? You may try doubling or tripling your sort_buffer and myisam_sort_buffer settings and maybe you read_buffer. My sort_buffer is 10Mb, the read_buffer is 2Mb and the myisam_sort_buffer is 64Mb ... are these still too low? I'm think I'm going to try to install mytop to get more performance info. Currently, my temporary solution is to limit the query to 2000 rows but it still takes 4-5 seconds and doesn't give a complete picture for search results. Thanks for any help on this, - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG Function
Hi Craig, Sorry this is such a slow response but I have been swamped and I didn't see where anyone else has responded yet. First, we need to calculate the average ID per user but round the average to the nearest whole number. CREATE TEMPORARY TABLE tmpResults SELECT cast((AVG(id)+ .5) as integer) as average,u.user_id, u.username FROM users u INNER JOIN routes rt ON u.user_id = rt.user_id INNER JOIN ranking rnk ON rnk.rating = rt.rating WHERE username='$username' GROUP BY u.user_ID, username Now, maybe we can give you the results you wanted. SELECT rnk.rating as user_avg, tr.username, tr.user_id FROM tmpResults tr INNER JOIN ranking rnk ON tr.average = rnk.id OR if you wanted to see all of the Routes with the average information and user information all in the same query SELECT rnk.rating as user_avg, tr.username, tr.user_id, r.rating, r.route FROM tmpResults tr INNER JOIN ranking rnk ON tr.average = rnk.id INNER JOIN routes r ON r.user_id = tr.user_id As always, we need to clean up after ourselves: DROP TABLE tmpResults Hope this helped... Shawn Green Database Administrator Unimin Corporation - Spruce Pine Craig Hoffman [EMAIL PROTECTED] wrote on 08/24/2004 09:24:55 PM: Hey Everyone, I can you some assistance on this query. I have three tables one is called ranking and the other is called routes and finally the users table. The users table is not really important. The ranking table looks like this: id rating 15.0 2 5.1 3 5.2 4 5.3 5 5.3a 6 5.3b and so on... The routes table looks like this: user_id route rating 1 somename 5.2 1 5.3 1 5.3a Here's my query: SELECT ranking.rating, AVG(id), users.username, users.user_id, routes.rating, routes.user_id FROM ranking, routes, users WHERE username='$username' AND users.user_id = routes.user_id AND ranking.rating = routes.rating GROUP BY username What I am trying to do is find the average rating for this user. For example: 5.2 = 3 5.3 = 4 5.3a = 5 ___ 3 + 4 + 5 = 12 / 3 = 4 So 4 = 5.3 The average for this user would be 5.3. Any help would be most appreciated. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Many to Many: Does this make sense ?
I've hit on this subject before but I want to redefine and get some feedback. Right now I have a table called Member_Titles Members having multiple titles can put them in the datbases. I allow up to 5 to be held by the user and have a count() by ID to test before allowing insertion. Here's my problem. For the web side of things (and this is a database related question), I'm using a RAD tool. Meaning it's point and click on fields and tables and the code is generated automagically. The one problem I've run into is the RAD tool doesn't have the auto capabilities to allow multiple insert transactions into the same table on one page. To allow 5 titles I need 5 pages. From a users standpoint, it's not horrible, but not the best. So, now I'm podering a Many to Many, but I realize there would need to be multiple Member_Title tables, Member_Title1, Member_Title2, Member_Title3, etc. From a join angle, I guess I can live with it, provided the smoke and mirrors for my users are there. My concern is the query part. With only the one Member_Title table, the search is relatively simple, select title from member.title where title = jerl; from the M2M, I gather it's going to be select title from member.title1,member.title2, etc where title = jerk; Anything else I should be considering here and does it make sense to change the schema in this way ? I have 2 other similar forms with one holding 5 records. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort by COUNT(field_name) ?
You can use a query like this one
SELECT string, COUNT(string) as co FROM searchstat GROUP BY string order by co
DESC;
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Friday 27 August 2004 16:03, Dialogcentret wrote:
Example: I have a search box on my webpage and all searchstring are saved
in a database.
$foresp = mysql_query(SELECT string, COUNT(string) FROM searchstat GROUP
BY string ,$db);
while ($data = mysql_fetch_array($foresp)) {
echo br($data[1]) . str_replace('', 'lt;', $data[string]) . ;
}
However, I would like to write the result out with the most popular search
strings first. Is it possible to sort the output by count(field_name) ?
Med venlig hilsen
Birger Langkjer
Dialogcentret
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sort by COUNT(field_name) ?
Try:
SELECT string, COUNT(string) AS length FROM searchstat GROUP BY string ORDER
BY length
You can DESC at the end if you want the longest string first.
Cheers,
Paul
[EMAIL PROTECTED]
tel: 020 7446 7536
fax: 0207 833 4289
http://videoisland.com/ - The UK's ultimate DVD library
:-Original Message-
:From: Dialogcentret [mailto:[EMAIL PROTECTED]
:Sent: 27 August 2004 14:04
:To: [EMAIL PROTECTED]
:Subject: Sort by COUNT(field_name) ?
:
:Example: I have a search box on my webpage and all
:searchstring are saved in a database.
:
:$foresp = mysql_query(SELECT string, COUNT(string) FROM
:searchstat GROUP BY string ,$db);
:
:while ($data = mysql_fetch_array($foresp)) { echo
:br($data[1]) . str_replace('', 'lt;', $data[string]) . ; }
:
:However, I would like to write the result out with the most
:popular search strings first. Is it possible to sort the
:output by count(field_name) ?
:
:
:
:Med venlig hilsen
:Birger Langkjer
:Dialogcentret
:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One form multiple inserts
I did that as a convenience. Basically it provided me two ways of uniquely identifying any row in a table(an auto_inc integer, and some unique text column). The autoincremented values should never repeat. HOWEVER because I did not put a UNIQUE constraint on them, it is possible for someone to come back later and change one to match another. In a production environment, I would have created the UNIQUE constraint. The other thing that did for me is to provide numeric values for my table relationships. Otherwise I would have had to duplicate my primary keys (text columns) in every child table. Having all of that text in the database multiple times would take up much more room than just the integers. You are correct in saying that I could have written my tables to look like: CREATE TABLE member ( ID int auto_increment primary key, login varchar(25) not null, ... other columns... , UNIQUE(login) ) and achieved the same basic structure. You asked about the wisdom of normalizing on Titles. If you want each person to have their own list of titles, you are in a one-to-many situation. Between lists, titles can duplicate but each person will have their own list. This structure requires only 2 tables, member and title. The difference is that you put a member_id column on the title table so that you can keep up with which titles a person has held and for what dates. Otherwise you could have a blank field on just the member table and the user is limited to only 1 title, ever. The problem with the single-field design is that you would not have any history of the other titles a person held because every time the title changes, the old title is wiped out and replaced by the new one. With the two-table model, you will be able to keep a running history of all titles held by a member. Which model you choose depends on your application needs and what you need to do with the information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 08/25/2004 05:11:07 PM: Too funny! I'm slowly coming to grips on the M2M. I decided not to opt for it in this situation as I still believe it would not address my issues. My problem, or a better to phrase it , my solution is that I am not supplying titles. They are, to the user , blank fields , left to them to supply their title. Now I may have missed your point. Though it was 1-3 that held the same title twice. That much I grasped. Anyway, because there are literally a few thousand titles I'd need to insert I opted not to at this point unless or until I start seeing a trend of common ones. Does this make any sense ? Please tell me if I'm wrong Also another quick question: In your example tables I noticed you have an ID for the member plus a Primary Key ID ? Is this also common ? In particular with my tables for members I have 1 primary key column which is also the member ID . Is that okay ? Stuart --- [EMAIL PROTECTED] wrote: CREATE TABLE member ( MemberID int auto_increment , Login varchar(25) not null primary key , Name varchar(30) not null # , (other fields as necessary) , KEY(ID) ) CREATE TABLE title ( TitleID int auto_increment , TitleName varchar(25) not null primary key # , (other fields as needed) , KEY (ID) ) CREATE TABLE member_title ( ID int auto_increment , member_ID int not null , title_ID int not null , startdate datetime , enddate datetime , KEY(ID) )
Re: Many to Many: Does this make sense ?
Stuart, I'm using a RAD tool. Meaning it's point and click on fields and tables and the code is generated automagically. The one problem I've run into is the RAD tool doesn't have the auto capabilities to allow multiple insert transactions into the same table on one page. To allow 5 titles I need 5 pages. From a users standpoint, it's not horrible, but not the best. So, now I'm podering a Many to Many, but I realize there would need to be multiple Member_Title tables, Member_Title1, Member_Title2, Member_Title3, etc. Sounds like a dreadul idea, breaks several relational rules and will make more problems down the road c. Why not lose the RAD tool, find a better one? PB - Original Message - From: Stuart Felenstein To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 8:33 AM Subject: Many to Many: Does this make sense ? I've hit on this subject before but I want to redefine and get some feedback. Right now I have a table called Member_Titles Members having multiple titles can put them in the datbases. I allow up to 5 to be held by the user and have a count() by ID to test before allowing insertion. Here's my problem. For the web side of things (and this is a database related question), I'm using a RAD tool. Meaning it's point and click on fields and tables and the code is generated automagically. The one problem I've run into is the RAD tool doesn't have the auto capabilities to allow multiple insert transactions into the same table on one page. To allow 5 titles I need 5 pages. From a users standpoint, it's not horrible, but not the best. So, now I'm podering a Many to Many, but I realize there would need to be multiple Member_Title tables, Member_Title1, Member_Title2, Member_Title3, etc. From a join angle, I guess I can live with it, provided the smoke and mirrors for my users are there. My concern is the query part. With only the one Member_Title table, the search is relatively simple, select title from member.title where title = jerl; from the M2M, I gather it's going to be select title from member.title1,member.title2, etc where title = jerk; Anything else I should be considering here and does it make sense to change the schema in this way ? I have 2 other similar forms with one holding 5 records. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries on Fast Server?
If you are sorting the result, setting a limit only speeds things up for data transfer of the result set since MySQL still has to find all the records, sort them, then deliver only the first X records. You can usually tell how much time is spent on the transfer of the result set vs. finding the result by doing a select count(*) rather than actually selecting data. As for average length, I don't think that's the issue. The system I'm designing searches resumes, so while there are only about 15k records, the average length is about 3 pages of text. My data size is actually about the same size as yours. My response time on a test machine is about 6-8 seconds, but the test machine is woefully underpowered for real use (Mac 350Mhz G4 256MB RAM). I would definitely try increasing your buffer variables. Double them and see what happens. Many times on this list people have gotten significant performance boosts from setting high sort buffers. Please post results if it works. On Aug 27, 2004, at 9:28 AM, [EMAIL PROTECTED] wrote: Thanks Brent and Donny, hopefully this info will help get to the root of the problem with the fulltext search. The table structure is very, very simple: mysql describe product_fulltext; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | product_id | int(9) | | PRI | 0 | | | search_text | text | | MUL | | | +-++--+-+-+---+ 2 rows in set (0.00 sec) Space usage : TypeUsage Data502,455 KB Index 440,412 KB Total 942,867 KB Row Statistic : Statements Value Format dynamic Rows3,237,981 Row length ø158 Row size ø 298 Bytes MySQL 4.0.20-standard-log Official MySQL RPM I also calculated the average text feild length: mysql SELECT AVG(LENGTH(search_text)) AS avg_length FROM product_fulltext; ++ | avg_length | ++ | 147.2239 | ++ 1 row in set (33.34 sec) Is my average text length too long? Is MySQL 4.0.20 really that slow for fulltext searching? If so, how much will performance increase by upgrading to 4.1.x? Is upgrading difficult? You may try doubling or tripling your sort_buffer and myisam_sort_buffer settings and maybe you read_buffer. My sort_buffer is 10Mb, the read_buffer is 2Mb and the myisam_sort_buffer is 64Mb ... are these still too low? I'm think I'm going to try to install mytop to get more performance info. Currently, my temporary solution is to limit the query to 2000 rows but it still takes 4-5 seconds and doesn't give a complete picture for search results. Thanks for any help on this, - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many to Many: Does this make sense ?
Thank you for the stop sign. As for the RAD tool I'm open to suggestions, but I think I've read about them all. PHP - MySQL platform. Stuart --- Peter Brawley [EMAIL PROTECTED] wrote: Stuart, I'm using a RAD tool. Meaning it's point and click on fields and tables and the code is generated automagically. The one problem I've run into is the RAD tool doesn't have the auto capabilities to allow multiple insert transactions into the same table on one page. To allow 5 titles I need 5 pages. From a users standpoint, it's not horrible, but not the best. So, now I'm podering a Many to Many, but I realize there would need to be multiple Member_Title tables, Member_Title1, Member_Title2, Member_Title3, etc. Sounds like a dreadul idea, breaks several relational rules and will make more problems down the road c. Why not lose the RAD tool, find a better one? PB - Original Message - From: Stuart Felenstein To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 8:33 AM Subject: Many to Many: Does this make sense ? I've hit on this subject before but I want to redefine and get some feedback. Right now I have a table called Member_Titles Members having multiple titles can put them in the datbases. I allow up to 5 to be held by the user and have a count() by ID to test before allowing insertion. Here's my problem. For the web side of things (and this is a database related question), I'm using a RAD tool. Meaning it's point and click on fields and tables and the code is generated automagically. The one problem I've run into is the RAD tool doesn't have the auto capabilities to allow multiple insert transactions into the same table on one page. To allow 5 titles I need 5 pages. From a users standpoint, it's not horrible, but not the best. So, now I'm podering a Many to Many, but I realize there would need to be multiple Member_Title tables, Member_Title1, Member_Title2, Member_Title3, etc. From a join angle, I guess I can live with it, provided the smoke and mirrors for my users are there. My concern is the query part. With only the one Member_Title table, the search is relatively simple, select title from member.title where title = jerl; from the M2M, I gather it's going to be select title from member.title1,member.title2, etc where title = jerk; Anything else I should be considering here and does it make sense to change the schema in this way ? I have 2 other similar forms with one holding 5 records. Thank you, Stuart -- 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: 1 day 28 min insert
Mikhail Entaltsev wrote: Hi, insert into 321st_stat select * from stat_in group by primary key fields from 321st_stat table; did you try to use this query? Best regards, Mikhail. Ran it, it took at least 24 hours, it finished but never gave me the total time, when I checked the server mysql dropped me back to the command prompt, with no time or number of records :( Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Could you execute show create table 321st_stat and show create table stat_in and send results back? Best regards, Mikhail. - Original Message - From: matt ryan [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:45 PM Subject: Re: 1 day 28 min insert Mikhail Entaltsev wrote: Hi, insert into 321st_stat select * from stat_in group by primary key fields from 321st_stat table; did you try to use this query? Best regards, Mikhail. Ran it, it took at least 24 hours, it finished but never gave me the total time, when I checked the server mysql dropped me back to the command prompt, with no time or number of records :( Matt -- 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: Many to Many: Does this make sense ?
- Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Peter Brawley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:06 AM Subject: Re: Many to Many: Does this make sense ? Thank you for the stop sign. As for the RAD tool I'm open to suggestions, but I think I've read about them all. PHP - MySQL platform. I don't have an alternate RAD tool to suggest since I don't know what's out there. Let me suggest a different *design* that may work well with your existing RAD tool. Most database designers would never implement a true many-to-many relationship in a real database. Instead, they would break the many-to-many relationship into two one-to-many relationships based centered on something called an association table (or sometimes an intersection table). For example, in your case, I would create these tables: Members (1 row per member) member_idmember_nameetc. AJones... BSmith... Primary key(member_id) Titles (1 row per title) -- title_idtitle_nameetc. 1Bullitt... 2Serpico ... Primary key(title_id) Member_title (1 row for each title associated with a member) --- member_idtitle_id A1 A2 B2 Primary key(member_id, title_id) Foreign key #1 (member_id) Foreign key #2 (title_id) In other words, Jones owns both movies while Smith owns only Serpico. This design is not using mnemonic codes - it's a lot harder to think of good mnemonics for people and movie titles than for airlines - so you will usually have to join back to the Members and Titles tables to find out the name of the member or the title of his movie. The tables are small and the joins should be very efficient so this shouldn't be a problem for you. This design allows any member to own as many movies as they want. If you want to limit it to 5 movies per member, your application code can simply count how many the member currently has before allowing an insert of a new movie. In this way, your RAD tool only needs to come up with a form that allows one row to be inserted into each table. It sounds as if it can already do this. Of course users will have to invoke that form once for each title that they want so they may find this tedious. Then again, how many people will really want 5 movies in one go? Aren't they more likely to pick one or two at a time? In that case, they'd only be invoking that form once or twice, not five times, so they might not object too much. Do you see why this design is a better approach? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many to Many: Does this make sense ?
I think this design is a better approach but only if it's based on human tendencies (aka max 2 movies at a clip) Othewise (and if I'm missing something tell me please) if they should decide to enter 5 or 10 then the form comes at them 5 or 10 times. Your right though my RAD will allow it. Right now I've created a series of pages, with options to either enter another one (next page), or proceed to the next category. In effect that would be similar. Fortunately unlike movies, this form is probably going to be used infrequently. Once they've maxed out , the only option after this is replace or delete. Finally I don't think M2M or 12M make total sense to me. I'm not sure about the interim table. A query with some joins would net back the same results. What I think is it's necessary to the 12M process. Thank you, Stuart --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Peter Brawley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:06 AM Subject: Re: Many to Many: Does this make sense ? Thank you for the stop sign. As for the RAD tool I'm open to suggestions, but I think I've read about them all. PHP - MySQL platform. I don't have an alternate RAD tool to suggest since I don't know what's out there. Let me suggest a different *design* that may work well with your existing RAD tool. Most database designers would never implement a true many-to-many relationship in a real database. Instead, they would break the many-to-many relationship into two one-to-many relationships based centered on something called an association table (or sometimes an intersection table). For example, in your case, I would create these tables: Members (1 row per member) member_idmember_nameetc. AJones... BSmith... Primary key(member_id) Titles (1 row per title) -- title_idtitle_nameetc. 1Bullitt... 2Serpico ... Primary key(title_id) Member_title (1 row for each title associated with a member) --- member_idtitle_id A1 A2 B2 Primary key(member_id, title_id) Foreign key #1 (member_id) Foreign key #2 (title_id) In other words, Jones owns both movies while Smith owns only Serpico. This design is not using mnemonic codes - it's a lot harder to think of good mnemonics for people and movie titles than for airlines - so you will usually have to join back to the Members and Titles tables to find out the name of the member or the title of his movie. The tables are small and the joins should be very efficient so this shouldn't be a problem for you. This design allows any member to own as many movies as they want. If you want to limit it to 5 movies per member, your application code can simply count how many the member currently has before allowing an insert of a new movie. In this way, your RAD tool only needs to come up with a form that allows one row to be inserted into each table. It sounds as if it can already do this. Of course users will have to invoke that form once for each title that they want so they may find this tedious. Then again, how many people will really want 5 movies in one go? Aren't they more likely to pick one or two at a time? In that case, they'd only be invoking that form once or twice, not five times, so they might not object too much. Do you see why this design is a better approach? Rhino -- 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]
Moving MySQL data from Windows 4.0.12 to Linux 4.0.18
I am switching from a Windows computer to a Linux computer and when I dump the data from Windows to Linux I have no problem except for the fact that some of my characters have been converted to strange characters and when the data is displayed on a web page they show up as ?. I am sure that it has to do with character sets but I am not sure what to do about it. Any help would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add new database into existing MYSQL database
I could write the statement for you if you could answer these three things: 1) which user account would you like to modify the permissions of. 2) which database are you granting access to 3) what permissions do you want the user account of 1) to have on the database of 2) Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yong Wang [EMAIL PROTECTED] wrote on 08/27/2004 11:38:31 AM: Hi, Shawn: Thank you very much for you point out the privileges problem. I just read the mysql document. There are several privilege level, global, database, table, column. I guess I only need do in global lelvel. We have three databases, two exist before and work fine. Could you please give me an example how to use grant command ? I only add one database to an existing username (with oow databses before) ? Do I have to use revoke command . I need to be very careful not screw up the existing databases. Thanks a lot. Yong [EMAIL PROTECTED] 2004-8-25 15:00:25 If you do not use the GRANT, REVOKE, and DROP USER statements to create and destroy user accounts, you must manually FLUSH PRIVILEGES. It's all documented in the manual: http://dev.mysql.com/doc/mysql/en/GRANT.html and in even more detail here: http://dev.mysql.com/doc/mysql/en/User_Account_Management.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yong Wang [EMAIL PROTECTED] wrote on 08/24/2004 03:15:39 PM: Hi, all: I use root login to create a new databse in the existing server. Then I use the existing user name in the mysql user table as user name, add the database and username .. info into mysql db table. Then mysqladmin to load the table. When I login the database using the existing user name, I only can see previously existing database without seeing the new added database. What is the problem ? Thanks a lot. Yong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18
On Fri, 27 Aug 2004, Lehman, Jason (Registrar's Office) wrote: I am switching from a Windows computer to a Linux computer and when I dump the data from Windows to Linux I have no problem except for the fact that some of my characters have been converted to strange characters and when the data is displayed on a web page they show up as ?. I am sure that it has to do with character sets but I am not sure what to do about it. Any help would be appreciated. I suspect you are using MySQL version 4 or later? I'm not sure how/what you are using to extract the data and display it on a web page but we had a similar problem after we upgraded from MySQL 3.23.18 to 4.0.18 and our experiences may be of some help to you. We use Macromedia Cold Fusion MX 6.1 with apache on Linux - as Cold Fusion is an ODBC-oriented environment, they supply the Merant ODBC driver for MySQL to connect the two. After the upgrade, things like the UK pound symbol and apostrophes in text fields were being displayed as black squares or '?' on a web page even though they appeared correctly if viewed with the mysql command-line client. After a lot of investigation I eventually found that I had to add a parameter like: useUnicode=truecharacterEncoding=Windows-1252 to the ODBC/MySQL driver which solved the problem. It sounds to me as if you have a similar problem in your environment although the fix in your case will be different. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18
I should have added what I was using to do the transfer. I am using phpmyadmin to do a dump to a gzipped file and then I am importing. Thanks for the info. -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 12:29 PM To: Lehman, Jason (Registrar's Office) Cc: [EMAIL PROTECTED] Subject: Re: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18 On Fri, 27 Aug 2004, Lehman, Jason (Registrar's Office) wrote: I am switching from a Windows computer to a Linux computer and when I dump the data from Windows to Linux I have no problem except for the fact that some of my characters have been converted to strange characters and when the data is displayed on a web page they show up as ?. I am sure that it has to do with character sets but I am not sure what to do about it. Any help would be appreciated. I suspect you are using MySQL version 4 or later? I'm not sure how/what you are using to extract the data and display it on a web page but we had a similar problem after we upgraded from MySQL 3.23.18 to 4.0.18 and our experiences may be of some help to you. We use Macromedia Cold Fusion MX 6.1 with apache on Linux - as Cold Fusion is an ODBC-oriented environment, they supply the Merant ODBC driver for MySQL to connect the two. After the upgrade, things like the UK pound symbol and apostrophes in text fields were being displayed as black squares or '?' on a web page even though they appeared correctly if viewed with the mysql command-line client. After a lot of investigation I eventually found that I had to add a parameter like: useUnicode=truecharacterEncoding=Windows-1252 to the ODBC/MySQL driver which solved the problem. It sounds to me as if you have a similar problem in your environment although the fix in your case will be different. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many to Many: Does this make sense ?
Stuart, Both Rhino and I have suggested the same basic data storage structure to you. What you need to understand is that your RAD tool is not supporting your application design. You can use a form with 5 or 10 blanks on it where the user can list all of their choices. Then when the user submits the multiple field input form, your handling code will need to go down the list of fields and make the appropriate database entries. 1 form with 5 fields. User is happy, database is happy. You have kept the user from entering too many choices because you only gave them as many fields as you wanted them to have. In this case, the application's user interface is enforcing a business rule through its design. Your data base doesn't care how many titles each member has (from 0 to several billion) but your application requirements need the user to have no more than 5. That's a business rule and you code for that above the database (either in your validation code or, in this case, through UI design) I would stick with the many-to-many database design as you don't want people mis-typing titles all of the time:Godfather is not The Godfather which is different than The God Father and wouldn't match The Godfather I ...(can you see where I am going?). Keep a separate table of titles, even if they are used only once or never at all. That way if anyone needs to look one up, you already have a list. You could also add titles to the list BEFORE any members wants to see them and you can make sure they are spelled correctly. I only trust users to type in things that the users are expert at and I don't think that your users are going to be a bunch of film historians. I would pre-enter what I could and review any new entries they make (nobody said your users can't add to the list, did they...). Let us know if any of this makes sense or not, please. This is an important topic in database design and you are not the first person, nor will you be the last, to make this design decision. The more we discuss this, the better off the next person with this problem will be. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 08/27/2004 11:47:33 AM: I think this design is a better approach but only if it's based on human tendencies (aka max 2 movies at a clip) Othewise (and if I'm missing something tell me please) if they should decide to enter 5 or 10 then the form comes at them 5 or 10 times. Your right though my RAD will allow it. Right now I've created a series of pages, with options to either enter another one (next page), or proceed to the next category. In effect that would be similar. Fortunately unlike movies, this form is probably going to be used infrequently. Once they've maxed out , the only option after this is replace or delete. Finally I don't think M2M or 12M make total sense to me. I'm not sure about the interim table. A query with some joins would net back the same results. What I think is it's necessary to the 12M process. Thank you, Stuart --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Peter Brawley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:06 AM Subject: Re: Many to Many: Does this make sense ? Thank you for the stop sign. As for the RAD tool I'm open to suggestions, but I think I've read about them all. PHP - MySQL platform. I don't have an alternate RAD tool to suggest since I don't know what's out there. Let me suggest a different *design* that may work well with your existing RAD tool. Most database designers would never implement a true many-to-many relationship in a real database. Instead, they would break the many-to-many relationship into two one-to-many relationships based centered on something called an association table (or sometimes an intersection table). For example, in your case, I would create these tables: Members (1 row per member) member_idmember_nameetc. AJones... BSmith... Primary key(member_id) Titles (1 row per title) -- title_idtitle_nameetc. 1Bullitt... 2Serpico ... Primary key(title_id) Member_title (1 row for each title associated with a member) --- member_idtitle_id A1 A2 B2 Primary key(member_id, title_id) Foreign key #1 (member_id) Foreign key #2 (title_id) In other words, Jones owns both movies while Smith owns only Serpico. This design is not using mnemonic codes - it's a lot harder to think of good
Re: Many to Many: Does this make sense ?
As I said in my reply, if you go with the association table design, you will likely write your code so that the movie information will be prompted 5 times if the member chooses 5 movies (or 3 times if they choose 3 movies, etc.). However, you don't HAVE to do it that way. You could always create a form that asks them for up to 5 movies. You'd probably have to create that form manually though, unless your RAD tool has the ability to do that. You say that this design doesn't completely make sense to you. What are your questions about it? I assure you, I'm not making this design up out of thin air; much smarter people than I have created this design and it is very widely used in relational databases around the world. I'm just trying to tell you how the pros do things. If you have specific questions, I'll do my best to explain why the design is the way it is. Rhino - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; Peter Brawley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 11:47 AM Subject: Re: Many to Many: Does this make sense ? I think this design is a better approach but only if it's based on human tendencies (aka max 2 movies at a clip) Othewise (and if I'm missing something tell me please) if they should decide to enter 5 or 10 then the form comes at them 5 or 10 times. Your right though my RAD will allow it. Right now I've created a series of pages, with options to either enter another one (next page), or proceed to the next category. In effect that would be similar. Fortunately unlike movies, this form is probably going to be used infrequently. Once they've maxed out , the only option after this is replace or delete. Finally I don't think M2M or 12M make total sense to me. I'm not sure about the interim table. A query with some joins would net back the same results. What I think is it's necessary to the 12M process. Thank you, Stuart --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Peter Brawley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:06 AM Subject: Re: Many to Many: Does this make sense ? Thank you for the stop sign. As for the RAD tool I'm open to suggestions, but I think I've read about them all. PHP - MySQL platform. I don't have an alternate RAD tool to suggest since I don't know what's out there. Let me suggest a different *design* that may work well with your existing RAD tool. Most database designers would never implement a true many-to-many relationship in a real database. Instead, they would break the many-to-many relationship into two one-to-many relationships based centered on something called an association table (or sometimes an intersection table). For example, in your case, I would create these tables: Members (1 row per member) member_idmember_nameetc. AJones... BSmith... Primary key(member_id) Titles (1 row per title) -- title_idtitle_nameetc. 1Bullitt... 2Serpico ... Primary key(title_id) Member_title (1 row for each title associated with a member) --- member_idtitle_id A1 A2 B2 Primary key(member_id, title_id) Foreign key #1 (member_id) Foreign key #2 (title_id) In other words, Jones owns both movies while Smith owns only Serpico. This design is not using mnemonic codes - it's a lot harder to think of good mnemonics for people and movie titles than for airlines - so you will usually have to join back to the Members and Titles tables to find out the name of the member or the title of his movie. The tables are small and the joins should be very efficient so this shouldn't be a problem for you. This design allows any member to own as many movies as they want. If you want to limit it to 5 movies per member, your application code can simply count how many the member currently has before allowing an insert of a new movie. In this way, your RAD tool only needs to come up with a form that allows one row to be inserted into each table. It sounds as if it can already do this. Of course users will have to invoke that form once for each title that they want so they may find this tedious. Then again, how many people will really want 5 movies in one go? Aren't they more likely to pick one or two at a time? In that case, they'd only be invoking that form once or twice, not five times, so they might not object too much. Do you see why this design is a better approach? Rhino --
Re: huge innodb data files
Hmm, well... It really shouldn't take 2 min to select from an empty table, no matter what you have in my.cnf. So, something else is happening. One way that InnoDB can take forever to read from an empty table is if there's a transaction still in progress that was started some time ago. Perhaps there's a process that's looking at the database, having started a transaction, and has never committed. Are you sure this isn't the case? Or, perhaps you have many transactions which get interval locks on your empty table? Does the problem go away if you stop and restart the server? How about if you do a TRUNCATE TABLE on your offending empty table, which will discard it and recreate it? If that's not it, perhaps something is wrong with the InnoDB database files. Can you dump the data with mysqldump, delete the InnoDB database files, and recreate the database? If that doesn't help, or if the problem returns, you should post the result of a SHOW INNODB STATUS when the problem is happening. You may need to pay MySQL AB or InnoDB Oy for some help. = original message follows = From: Ronan Lucio [EMAIL PROTECTED] To: Mayuran Yogarajah [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: huge innodb data files Date: Fri, 27 Aug 2004 09:49:51 -0300 Mayuran, Well, I´m not a MySQL expert, but I think that a good configuration in the my.cf file can make it better. Ronan - Original Message - From: Mayuran Yogarajah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 5:37 PM Subject: huge innodb data files Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many to Many: Does this make sense ?
My remarks are interspersed below for reasons which will probably be obvious. Rhino - Original Message - From: [EMAIL PROTECTED] To: Stuart Felenstein Cc: [EMAIL PROTECTED] ; Peter Brawley ; Rhino Sent: Friday, August 27, 2004 12:33 PM Subject: Re: Many to Many: Does this make sense ? Stuart, Both Rhino and I have suggested the same basic data storage structure to you. What you need to understand is that your RAD tool is not supporting your application design. You can use a form with 5 or 10 blanks on it where the user can list all of their choices. Then when the user submits the multiple field input form, your handling code will need to go down the list of fields and make the appropriate database entries. 1 form with 5 fields. User is happy, database is happy. You have kept the user from entering too many choices because you only gave them as many fields as you wanted them to have. In this case, the application's user interface is enforcing a business rule through its design. Your data base doesn't care how many titles each member has (from 0 to several billion) but your application requirements need the user to have no more than 5. That's a business rule and you code for that above the database (either in your validation code or, in this case, through UI design) I'm fine with what you've said so far. I would stick with the many-to-many database design Really? Have you *ever* seen a true many-to-many relationship implemented in a real database? I don't recall seeing one in over 20 years of system work and some rather large databases. I've heard a lot of reasons why a pure many-to-many relationship is a very bad thing to implement and have always stayed clear of them myself. I'm very reluctant to suggest implementing a many-to-many, especially to a newbie who doesn't fully grasp the consequences of doing so as you don't want people mis-typing titles all of the time:Godfather is not The Godfather which is different than The God Father and wouldn't match The Godfather I ...(can you see where I am going?). Keep a separate table of titles, even if they are used only once or never at all. That way if anyone needs to look one up, you already have a list. You could also add titles to the list BEFORE any members wants to see them and you can make sure they are spelled correctly. I only trust users to type in things that the users are expert at and I don't think that your users are going to be a bunch of film historians. I would pre-enter what I could and review any new entries they make (nobody said your users can't add to the list, did they...). I think you're off on a bit of a tangent here. You're talking about validation edits here, not many-to-many relationships. I agree that it would probably be better to have users pick movies from an existing list rather than typing the names in themselves; the reasons you give are perfectly valid for doing so. But I don't see where this ties in directly to the question of implementing a many-to-many relationship. If I were building Stuart's system, I'd create the Titles table and put all of the rows in it before the user ever used the system; the user would be allowed to see the contents of the Titles table and then click on whichever movies they wanted, up to their limit of 5. That way, they couldn't request Godfather, The God Father, or some other movie that doesn't exist in the real world. Of course, you'd still need a mechanism to add movies to the list; you could go several different ways on that. If you were routinely adding most new releases to the Titles table already, you might get by with a simple request form where the user identifies a movie he'd like to see in the list and you eventually acquire it and add it to the list on his behalf. But if users were making a lot of requests, particularly for obscure titles, you might handle that differently. Let's set that issue aside for now though; it's off topic in my view. Let us know if any of this makes sense or not, please. This is an important topic in database design and you are not the first person, nor will you be the last, to make this design decision. The more we discuss this, the better off the next person with this problem will be. Agreed!! No one was born knowing database design and even experts can learn new tricks or new reasons for doing - or not doing - things. Newbies can learn a lot from experts. Experts even learn from newbies sometimes. So continuing to discuss important topics like this can benefit all concerned. No one wants to browbeat you into doing something you don't understand or believe in. I think everyone concerned wants you to do the right things for the right reasons and wants you to know why you are doing them so that you can justify it in your own mind. Rhino
Re: Many to Many: Does this make sense ?
I don't see the bigger picture yet. I'm 3 weeks in database design :). What I understand and perceive to be of value in this design is the reinforcement of the foreign keys. Related to that, the control over record deletion, cascading, etc. I mentioned earlier that the interim table that must exist in the many to many / one to many design makes no sense to me. I guess it serves as some type of support structure ? I agree that it would be better to have a list prepared for users to choose from. I'm working on a job board. Looking out on the vast sea of careers and positions it's pretty staggering as to the amount of job titles out there. My plan was to track the more common ones and start building a list from there. As it relates to the many to many issue, well I am still confused. I promise to read more and hit my head with the book. Stuart --- [EMAIL PROTECTED] wrote: Stuart, Both Rhino and I have suggested the same basic data storage structure to you. What you need to understand is that your RAD tool is not supporting your application design. You can use a form with 5 or 10 blanks on it where the user can list all of their choices. Then when the user submits the multiple field input form, your handling code will need to go down the list of fields and make the appropriate database entries. 1 form with 5 fields. User is happy, database is happy. You have kept the user from entering too many choices because you only gave them as many fields as you wanted them to have. In this case, the application's user interface is enforcing a business rule through its design. Your data base doesn't care how many titles each member has (from 0 to several billion) but your application requirements need the user to have no more than 5. That's a business rule and you code for that above the database (either in your validation code or, in this case, through UI design) I would stick with the many-to-many database design as you don't want people mis-typing titles all of the time:Godfather is not The Godfather which is different than The God Father and wouldn't match The Godfather I ...(can you see where I am going?). Keep a separate table of titles, even if they are used only once or never at all. That way if anyone needs to look one up, you already have a list. You could also add titles to the list BEFORE any members wants to see them and you can make sure they are spelled correctly. I only trust users to type in things that the users are expert at and I don't think that your users are going to be a bunch of film historians. I would pre-enter what I could and review any new entries they make (nobody said your users can't add to the list, did they...). Let us know if any of this makes sense or not, please. This is an important topic in database design and you are not the first person, nor will you be the last, to make this design decision. The more we discuss this, the better off the next person with this problem will be. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 08/27/2004 11:47:33 AM: I think this design is a better approach but only if it's based on human tendencies (aka max 2 movies at a clip) Othewise (and if I'm missing something tell me please) if they should decide to enter 5 or 10 then the form comes at them 5 or 10 times. Your right though my RAD will allow it. Right now I've created a series of pages, with options to either enter another one (next page), or proceed to the next category. In effect that would be similar. Fortunately unlike movies, this form is probably going to be used infrequently. Once they've maxed out , the only option after this is replace or delete. Finally I don't think M2M or 12M make total sense to me. I'm not sure about the interim table. A query with some joins would net back the same results. What I think is it's necessary to the 12M process. Thank you, Stuart --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Peter Brawley [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:06 AM Subject: Re: Many to Many: Does this make sense ? Thank you for the stop sign. As for the RAD tool I'm open to suggestions, but I think I've read about them all. PHP - MySQL platform. I don't have an alternate RAD tool to suggest since I don't know what's out there. Let me suggest a different *design* that may work well with your existing RAD tool. Most database designers would never implement a true many-to-many relationship in a real database. Instead, they would break the many-to-many relationship into two one-to-many relationships based centered on something
Good book
Since I'm new with MySQL, I want to apologize for asking questions that should've been answered by googling, RTFM and such. However, I'm looking for a good book that deals with MySQL and PHP whether it be single book (combined with PHP and MySQL) or separate books for each. I am intensive in Linux Operating System and bash shell scripting, C++, Access and html + css (using notepad only). With my knowledge, which book(s) do you recommend the most? (prefably with execrises) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18
I had a situation you are seeing, what I ended up doing was create the db's in the new mysql server, setup replication, do LOAD DATA FROM MASTER let that run for a bit (while the first server is not in production of course) then after it synched up, shut down them both, removed the replication information and it started to work again. I may have copied the tables' files over, but I do not recall (~1.5years ago) HTH Thanks, James -Original Message- From: Lehman, Jason (Registrar's Office) [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 12:32 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18 I should have added what I was using to do the transfer. I am using phpmyadmin to do a dump to a gzipped file and then I am importing. Thanks for the info. -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 12:29 PM To: Lehman, Jason (Registrar's Office) Cc: [EMAIL PROTECTED] Subject: Re: Moving MySQL data from Windows 4.0.12 to Linux 4.0.18 On Fri, 27 Aug 2004, Lehman, Jason (Registrar's Office) wrote: I am switching from a Windows computer to a Linux computer and when I dump the data from Windows to Linux I have no problem except for the fact that some of my characters have been converted to strange characters and when the data is displayed on a web page they show up as ?. I am sure that it has to do with character sets but I am not sure what to do about it. Any help would be appreciated. I suspect you are using MySQL version 4 or later? I'm not sure how/what you are using to extract the data and display it on a web page but we had a similar problem after we upgraded from MySQL 3.23.18 to 4.0.18 and our experiences may be of some help to you. We use Macromedia Cold Fusion MX 6.1 with apache on Linux - as Cold Fusion is an ODBC-oriented environment, they supply the Merant ODBC driver for MySQL to connect the two. After the upgrade, things like the UK pound symbol and apostrophes in text fields were being displayed as black squares or '?' on a web page even though they appeared correctly if viewed with the mysql command-line client. After a lot of investigation I eventually found that I had to add a parameter like: useUnicode=truecharacterEncoding=Windows-1252 to the ODBC/MySQL driver which solved the problem. It sounds to me as if you have a similar problem in your environment although the fix in your case will be different. Andy -- 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: AVG Function
Craig Hoffman wrote:
Mark,
Yes its close and thank you. The problem I am having is I am able to
generate the correct ranking.id for that particular user but I can't
seem to make it equal the ranking.rating.
ID ranking.rating
9 = 5.6 (example)
Here's my query:
SELECT routes.user_id, ranking.rating, ROUND(AVG(ranking.id), 0)
avg_ranking, users.username, users.user_id, routes.rating FROM ranking,
routes, users WHERE username='$username' AND routes.user_id =
users.user_id AND ranking.rating = routes.rating GROUP BY routes.user_id
//echo some stuff out
echo(td align='right'.$row[ranking.rating]. /td);
I know I need to make the avg_ranking or the ranking.id = ranking.rating
but I can't seem to get it work. Any more suggestions? Again thanks
for all your help.
-- Craig
rant9 is never equal to 5.6./rant
Sorry, but I teach math and that sort of thing drives me nuts.
I mention this because I think it is part of why this is causing you
trouble. For every row of ranking you've shown us, ranking.id does not
equal ranking.rating, so you cannot make it equal the ranking.rating.
Yes, I know what you meant. You're using that as shorthand for getting the
corresponding ranking.rating for the ranking.id. But that shorthand
obscures the problem. You need to select the rows for the given user to
look at them to calculate the average. You need the resulting average id to
look up the corresponding rating. You see? By avoiding the shorthand, I
think it is a little more obvious that this takes 2 steps. You cannot
magically select the row with the average id at the same time you are
selecting the rows to be averaged.
If you want the average for a particular user, say user_id = 1, as in your
example, you can do it in 2 steps with a user variable (see sample data at
the end):
SELECT @avg_rank_id:=ROUND(AVG(ranking.id), 0) avg_rank_id
FROM ranking, routes
WHERE routes.user_id = 1
AND ranking.rating = routes.rating;
+-+
| avg_rank_id |
+-+
| 4 |
+-+
1 row in set (0.00 sec)
SELECT * FROM ranking WHERE id = @avg_rank_id;
+++
| id | rating |
+++
| 4 | 5.3|
+++
1 row in set (0.00 sec)
If you want to get the average for each user_id, you can do it in 2 steps
with a temporary table:
CREATE TEMPORARY TABLE rank_avg
SELECT user_id, ROUND(AVG(ranking.id), 0) AS avg_rank_id
FROM ranking, routes
WHERE ranking.rating = routes.rating
GROUP BY routes.user_id;
SELECT rank_avg.user_id, ranking.rating
FROM rank_avg, ranking
WHERE ranking.id = rank_avg.avg_rank_id;
+-++
| user_id | rating |
+-++
| 1 | 5.3|
| 2 | 5.3a |
+-++
2 rows in set (0.00 sec)
DROP TABLE rank_avg;
{I see that Shawn has sent you a solution equivalent to this as I was typing.}
If you have mysql 4.1, you can use a (correlated) subquery to combine both
steps in one query:
SELECT rt.user_id, rnk.rating
FROM routes rt, ranking rnk
WHERE rnk.id = (SELECT ROUND(AVG(ranking.id), 0)
FROM ranking, routes
WHERE ranking.rating = routes.rating
AND routes.user_id = rt.user_id)
GROUP BY rt.user_id;
+-++
| user_id | rating |
+-++
| 1 | 5.3|
| 2 | 5.3a |
+-++
2 rows in set (0.01 sec)
This will be inefficient, however, so I don't recommend it.
Finally, as a mathematician, I must point out that calling this the average
ranking is almost certainly misleading. Aside from the rounding, you are
treating your ratings as if they were on a linear scale, but their values
imply otherwise. That is, what you are doing assumes that the difference
between 5.3a and 5.3b is the same as the difference between 5.1 and 5.2. I
don't know anything about your data other than what you've told us, but I'd
be surprised if that assumption were accurate.
Michael
Data for the above examples:
USE test;
DROP TABLE IF EXISTS ranking;
CREATE TABLE ranking
(
id INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
rating VARCHAR(5)
);
INSERT INTO ranking
VALUES (1, '5.0'), (2, '5.1'), (3, '5.2'),
(4, '5.3'), (5, '5.3a'), (6, '5.3b'),
(7, '5.4'), (8, '5.5'), (9, '5.6');
DROP TABLE IF EXISTS routes;
CREATE TABLE routes
(
user_id INT(3) UNSIGNED,
route CHAR(7),
rating CHAR(4)
);
INSERT INTO routes
VALUES (1, 'Route 1','5.2'), (1, 'Route 2', '5.3'), (1, 'Route 3', '5.3a'),
(2, 'Route 1','5.2'), (2, 'Route 2', '5.3'), (2, 'Route 3', '5.6');
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Good book
I suggest: For PHP and MySQL: PHP and MySQL Web Development (Sams Publishing) by Luke Welling and Laura Thomson For MySQL: MySQL (second edition) by Paul Dubois -Mensaje original- De: Scott Hamm [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 27 de Agosto de 2004 11:16 a.m. Para: 'Mysql ' (E-mail) Asunto: Good book Since I'm new with MySQL, I want to apologize for asking questions that should've been answered by googling, RTFM and such. However, I'm looking for a good book that deals with MySQL and PHP whether it be single book (combined with PHP and MySQL) or separate books for each. I am intensive in Linux Operating System and bash shell scripting, C++, Access and html + css (using notepad only). With my knowledge, which book(s) do you recommend the most? (prefably with execrises) -- 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: Many to Many: Does this make sense ?
Rhino, You and I are on the same page. I also never seen a true many-to-many relationship in database design. The closest approximation I have seen is the 3 table design like you and I have both proposed. It's the only way I have ever seen to model a many-to-many design, that's why I called it that. Sorry for my semantic faux pas (You say po-tay-to, I say po-tah-to. ;-) As to the subject at hand, relational data structures: The one-to-many relationship. This is what typically exists between items that are in a parent-child relationship. Examples of this could be a person and their phone numbers, a business and it's employees, a discussion topic and its responses. Notice how there is always one item relating to several other items? Hence the name one-to-many. Let's say you wanted a database that can store peoples names and all of their phone numbers. You could create one table with every possible column for each type of phone this person has. (I am leaving out the column type definitions to save space) CREATE TABLE person ( Name, Title, Address, City, State, Zip, HousePhone, CellPhone, FaxPhone, OfficePhone, SecretaryPhone, GaragePhone, DoctorPhone ) But what happens when you need to add a new type of phone number? You would be forced to change your table design and possibly several sections of code. This is a BAD design and should never happen. What you need are two tables, one for personal information, and one for phone numbers. CREATE TABLE person ( ID Name, Title, Address, City, State, ZIP ) CREATE TABLE PhoneNumber ( person_ID, Number, Type ) Each entry in the PhoneNumber table will equate a person to a number and identify what type of phone number it is. Can you see any reasonable limits to how many different numbers you can store for each person with this type of design?(of course there are limits to how many records a database can hold be we aren't talking about those). Where the single-table model limited you to just a few, very particular, phone numbers the two-table model allows you complete flexibility. Let's examine your case of members and titles. Basically you will have certain people with common job titles (Database Analyst, Janitor, Receptionist, etc.) and duplicating that information over and over again in your database takes up lots of space but it could fit into a two-table model. It would look basically like this: CREATE TABLE member ( ID, Name, ... other fields ... ) CREATE TABLE title ( member_ID, JobTitle ) To avoid storing the same JobTitle multiple time in your title table, you would need to change your design so that all titles are stored only once and create an association table to link members to their titles. CREATE TABLE member ( ID, Name, ... other fields ... ) CREATE TABLE title ( ID, JobTitle ) CREATE TABLE title_member( title_ID, member_ID ) This kind of design will allow each JobTitle to be associated with multiple members and each member can be associated with multiple JobTitles (many items of one kind can associate with many items of another kind, or the items participate in a many-to-many relationship). Each association of a member to a job title (or job title to a member, depending on how you want to look at it) is an entry in the title_member table. Here is a sample of how some data might look. member title_membertitle ++--+ +--+---++++ | ID | Name | | title_id | member_id || ID | JobTitle | ++--+ +--+---++++ | 1 | John | |1 | 1 || 1 | Janitor| | 2 | Mary | |1 | 2 || 2 | Secretary | | 3 | Sam | |2 | 3 || 3 | Dog Walker | | 4 | Jane | |3 | 3 || 4 | Astronaut | ++--+ |3 | 4 |+++ +--+---+ Two people have been Janitors, John and Mary. Sam has been a Secretary and a Dog Walker. Jane has also been a Dog Walker. So far, nobody has been an Astronaut. This type of design gives you both flexibility and control as each element (a member or a title) exists only once in your data structures. To be more descriptive, we could call the title_member table something else like WorkHistory or PositionsHeld or PreviousTitles (imagination encouraged). Does this help you with the bigger picture? I know I can ramble off-topic and into the wrong direction (I have done it before) so let me know where I missed, please? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Many to Many: Does this make sense ?
Shawn, Yes, we *are* on the same page, now that I understand that you didn't mean to implement the many-to-many relationship in a single table. Stuart, Read what Stuart has described in this note. The association table is the one he calls TItle_Member. This table is *crucial* to the design, not just a nice-to-have. It gets one new row every time a member gets an additional job and records the member_id of the member and the title_id of the job. This is the *heart* of the many-to-many relationship; it's how you know which jobs are held by which people and which people hold which jobs. With this table, you can get the member_id of every person who has a particular job and the job_id of every job ever done by a given member_id. If you need to know the name of the person rather than their member_id, you simply join to the Member table using the member_id foreign key of Member_Title. By the same token, if you need to know the job name, you join to the Title table using the title_id. Naturally, you do both joins if you want to know both the job name and the member name. If you use this design, you should be able to store any information you want very concisely and get back anything you want to know very easily. Your primary and foreign keys will make sense and will be easily enforced. Is everything clear now? Believe me, the Systems community has a lot of experience with the issues raised by many-to-many relationships and this is the way we've been handling those issues for many years now. Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino Cc: [EMAIL PROTECTED] ; Stuart Felenstein Sent: Friday, August 27, 2004 2:05 PM Subject: Re: Many to Many: Does this make sense ? Rhino, You and I are on the same page. I also never seen a true many-to-many relationship in database design. The closest approximation I have seen is the 3 table design like you and I have both proposed. It's the only way I have ever seen to model a many-to-many design, that's why I called it that. Sorry for my semantic faux pas (You say po-tay-to, I say po-tah-to. ;-) As to the subject at hand, relational data structures: The one-to-many relationship. This is what typically exists between items that are in a parent-child relationship. Examples of this could be a person and their phone numbers, a business and it's employees, a discussion topic and its responses. Notice how there is always one item relating to several other items? Hence the name one-to-many. Let's say you wanted a database that can store peoples names and all of their phone numbers. You could create one table with every possible column for each type of phone this person has. (I am leaving out the column type definitions to save space) CREATE TABLE person ( Name, Title, Address, City, State, Zip, HousePhone, CellPhone, FaxPhone, OfficePhone, SecretaryPhone, GaragePhone, DoctorPhone ) But what happens when you need to add a new type of phone number? You would be forced to change your table design and possibly several sections of code. This is a BAD design and should never happen. What you need are two tables, one for personal information, and one for phone numbers. CREATE TABLE person ( ID Name, Title, Address, City, State, ZIP ) CREATE TABLE PhoneNumber ( person_ID, Number, Type ) Each entry in the PhoneNumber table will equate a person to a number and identify what type of phone number it is. Can you see any reasonable limits to how many different numbers you can store for each person with this type of design?(of course there are limits to how many records a database can hold be we aren't talking about those). Where the single-table model limited you to just a few, very particular, phone numbers the two-table model allows you complete flexibility. Let's examine your case of members and titles. Basically you will have certain people with common job titles (Database Analyst, Janitor, Receptionist, etc.) and duplicating that information over and over again in your database takes up lots of space but it could fit into a two-table model. It would look basically like this: CREATE TABLE member ( ID, Name, ... other fields ... ) CREATE TABLE title ( member_ID, JobTitle ) To avoid storing the same JobTitle multiple time in your title table, you would need to change your design so that all titles are stored only once and create an association table to link members to their titles. CREATE TABLE member ( ID, Name, ... other fields ... ) CREATE TABLE title ( ID,
Clarifying the MySQL Licensing Policy Documents Pt. II
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greetings All, Sorry for the long delay in following up! First, I have resigned from my position as MySQL AB Community Advocate for personal reasons. If you have concerns regarding licensing and community issues that you wish to directly engage MySQL in, you should write to either [EMAIL PROTECTED] or [EMAIL PROTECTED] Also, rather than create a separate request tracker for the licensing issues, our webmaster has (smartly) added a category in the standard MySQL bug tracker (http://bugs.mysql.com/). So, let us recap where we left off in these discussions with a slightly modified version of the notes from my prior message on this issue: As a way to cooperatively work to address some of the licensing concerns held by members of the MySQL community and the broader Free Software/Open Source community, we are initiating a community review of our licensing policy documents. This means that we are soliciting feedback about issues in the policy documents that people find inaccurate or unclear. From this feedback we will work to improve the policy documents. Please note that we cannot guarantee that we will address or accept all of the issues raised or suggestions made. The licensing is both complex and is the cornerstone of our business. Past experience shows that we must work carefully, incrementally and with community involvement in this area. As with development of the FLOSS exception though, I hope that we can reach a compromise that is satisfactory for almost everyone. If you wish to participate, the process for doing so is simple. Review some or all of the following documents: * http://www.mysql.com/products/licensing/ * http://www.mysql.com/products/licensing/commercial-license.html * http://www.mysql.com/products/licensing/opensource-license.html * http://www.mysql.com/products/licensing/faq.html When you encounter an issue that you find confusing or inaccurate, please file a bug report in the MySQL bug tracking system at http://bugs.mysql.com/. Please check to see if the bug has already been reported - if so, consider commenting on the existing bug. If creating a new bug, please make sure to set the category of the bug to Licensing. Additionally, it would be optimal if you could send a note to the MySQL community list on the issue - forwarding the message provided by the bug tracking system should be an easy way to do this. I would like the discussion to take place on the MySQL community list, as it is easier to keep track of the issues in a single, low-traffic setting. I prefer not to Cc the MySQL General list beyond this initial email, so as to avoid cluttering an already busy mailing list. As for the policy documents, MySQL CEO Mårten Mickos made a small set of alterations to the docs that he noted at http://slashdot.org/comments.pl?sid=118195cid=9987564 Thanks to everyone for their input and effort here! It has been good working with you! Cheers! - -- Zak Greant -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (Darwin) iD8DBQFBL3x8ctLVEMjfMB0RAjmIAJ40f+eyAO6m3alwA4Nb1aZb2dOZ2wCg4w6t OHCGMCl1oMZGxuNfYC/SaGA= =cR3c -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: huge innodb data files
We had a similar problem (though not quite as bad). I re-organized the datafiles (and fixed some indexes, etc) and we got a vast speed improvement. I'd suggest you shutdown the database, use mysqldump to take a dump of the database, move the old datafiles out of the way, fix your my.cnf to create new datafiles of about the same size (and if you can, on different disks), and then reimport the dump. Note that it would be much faster on the import if you used the new extended insert (-e or --extended-insert), assuming the version of MySQL you are using supports it. You didn't indicate the version, which might be helpful. David Ronan Lucio wrote: Mayuran, Well, I´m not a MySQL expert, but I think that a good configuration in the my.cf file can make it better. Ronan - Original Message - From: Mayuran Yogarajah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 5:37 PM Subject: huge innodb data files Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ? thanks, M -- 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: Many to Many: Does this make sense ?
Now I am totally clear on it! Took a few reiterations but I'm there. Even more I'm going to abandon the idea of titles. To implement it correctly, per the examples, you provided is an impossibility. At least something when weighed out doesn't mean enough to the effort it would take. Plus the lack it would still leave. I'm going to go over my schema though and see where the many to many relationships will work well for me. I'm assuming telephone numbers (SL Green's example) that are specific to the user / member need not necessarily be broken out ? Perhaps they should be. Stuart --- Rhino [EMAIL PROTECTED] wrote: Shawn, Yes, we *are* on the same page, now that I understand that you didn't mean to implement the many-to-many relationship in a single table. Stuart, Read what Stuart has described in this note. The association table is the one he calls TItle_Member. This table is *crucial* to the design, not just a nice-to-have. It gets one new row every time a member gets an additional job and records the member_id of the member and the title_id of the job. This is the *heart* of the many-to-many relationship; it's how you know which jobs are held by which people and which people hold which jobs. With this table, you can get the member_id of every person who has a particular job and the job_id of every job ever done by a given member_id. If you need to know the name of the person rather than their member_id, you simply join to the Member table using the member_id foreign key of Member_Title. By the same token, if you need to know the job name, you join to the Title table using the title_id. Naturally, you do both joins if you want to know both the job name and the member name. If you use this design, you should be able to store any information you want very concisely and get back anything you want to know very easily. Your primary and foreign keys will make sense and will be easily enforced. Is everything clear now? Believe me, the Systems community has a lot of experience with the issues raised by many-to-many relationships and this is the way we've been handling those issues for many years now. Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino Cc: [EMAIL PROTECTED] ; Stuart Felenstein Sent: Friday, August 27, 2004 2:05 PM Subject: Re: Many to Many: Does this make sense ? Rhino, You and I are on the same page. I also never seen a true many-to-many relationship in database design. The closest approximation I have seen is the 3 table design like you and I have both proposed. It's the only way I have ever seen to model a many-to-many design, that's why I called it that. Sorry for my semantic faux pas (You say po-tay-to, I say po-tah-to. ;-) As to the subject at hand, relational data structures: The one-to-many relationship. This is what typically exists between items that are in a parent-child relationship. Examples of this could be a person and their phone numbers, a business and it's employees, a discussion topic and its responses. Notice how there is always one item relating to several other items? Hence the name one-to-many. Let's say you wanted a database that can store peoples names and all of their phone numbers. You could create one table with every possible column for each type of phone this person has. (I am leaving out the column type definitions to save space) CREATE TABLE person ( Name, Title, Address, City, State, Zip, HousePhone, CellPhone, FaxPhone, OfficePhone, SecretaryPhone, GaragePhone, DoctorPhone ) But what happens when you need to add a new type of phone number? You would be forced to change your table design and possibly several sections of code. This is a BAD design and should never happen. What you need are two tables, one for personal information, and one for phone numbers. CREATE TABLE person ( ID Name, Title, Address, City, State, ZIP ) CREATE TABLE PhoneNumber ( person_ID, Number, Type ) Each entry in the PhoneNumber table will equate a person to a number and identify what type of phone number it is. Can you see any reasonable limits to how many different numbers you can store for each person with this type of design?(of course there are limits to how many records a database can hold be we aren't talking about those). Where the single-table model limited you to just a few, very particular, phone numbers the two-table model allows you complete flexibility. Let's examine your case of members and titles. Basically you will have certain people with common job titles (Database Analyst,
powerpoint and mysql
Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Mikhail Entaltsev wrote: Could you execute show create table 321st_stat and show create table stat_in and send results back? I have no key's on the temp table, stat_in, do you think adding keys on the whole primary key would be faster? I wasnt sure if you could join mysql keys, the key is called primary key so would it be a.primary key = b.primary key ? mysql explain select a.* from stat_in a left outer join 321st_stat b on a.don=b.don and a.dic=b.dic and a.niin=b.niin and a.sta=b.sta and a.qty=b.qty and a.fr_ric=b.fr_ric and a.suf=b.suf and a.dte_txn =b.dte_txn where isnull(b.don); | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | | 1 | SIMPLE | a | ALL| NULL | NULL| NULL | NULL| 77269086 | | | 1 | SIMPLE | b | eq_ref | PRIMARY,don,niin,dic,dte_txn | PRIMARY |39 | finlog.a.dic,finlog.a.niin,finlog.a.fr_ric,finlog.a.don,finlog.a.suf,finlog.a.dte_txn,finlog.a.sta | 1 | Using where; Not exists | 2 rows in set (0.11 sec) ---+ | 321st_stat | CREATE TABLE `321st_stat` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '', PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), KEY `don` (`don`), KEY `niin` (`niin`), KEY `stor` (`stor`), KEY `dic` (`dic`), KEY `ctasc` (`ctasc`), KEY `dte_txn` (`dte_txn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=900,000,000 PACK_KEYS=1 | 1 row in set (0.03 sec) | stat_in | CREATE TABLE `stat_in` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=99,999,999 PACK_KEYS=1 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: powerpoint and mysql
How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug -- 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]
mysqlimport - HP-UX 11.11 bus error (coredump)
Subject: HP-UX 11.11/4.0.20 mysqlimport BUS ERROR Description: Installed mysql from the binary download on mysql.com according to the INSTALL-BINARY instructions. Attempted to use mysqlimport as described in the online documentation (http://dev.mysql.com/doc/mysql/en/mysqlimport.html) using simple two line file imptest.txt. Received bus error and coredump as follows: mysql describe imptest; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| int(11) | YES | | NULL| | | n | varchar(30) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.02 sec) mysql exit Bye % cat imptest.txt 100 Max Sydow 101 Count Dracula % mysqlimport --local test imptest.txt Bus error(coredump) % file core core: core file from 'mysqlimport' - received SIGBUS % uname -a HP-UX myhost B.11.11 U 9000/800 547706587 unlimited-user license % getconf KERNEL_BITS 64 How-To-Repeat: See above. Same problem occurs with either of these binaries. mysql-standard-4.0.20-hp-hpux11.11-hppa2.0w-64bit.tar.gz mysql-standard-4.0.20-hp-hpux11.11-hppa2.0w.tar.gz mysql-standard-4.0.17-hp-hpux11.11-hppa2.0w.tar.gz Fix: I need one. My data loader depends on mysqlimport! Submitter-Id: submitter ID Originator:Gary Lehr Organization: Computer Sciences Corporation MySQL support: none Synopsis: mysqlimport bus error on HP-UX 11.11 Severity: Serious. I can't deploy my app without this. Priority: High Category: mysql Class: sw-bug Release: mysql-4.0.20-standard (Official MySQL-standard binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.0.20, for hp-hpux11.11 on hppa2.0w Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.20-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 20 hours 25 min 40 sec Threads: 1 Questions: 30 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 3 Queries per second avg: 0.000 C compiler: C++ compiler: Environment: System: HP-UX ndceqign B.11.11 U 9000/800 547706587 unlimited-user license Some paths: /usr/contrib/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /opt/gcc/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.0.1 /specs Configured with: ./configure : (reconfigured) ./configure : (reconfigured) ./configure : (reconfigured) ./configure : (reconfigured) ./configure Thread model: single gcc version 3.0.1 Compilation info: CC='cc' CFLAGS='+DAportable' CXX='aCC' CXXFLAGS='+DAportable' LDFLAGS='' ASFLAGS='' LIBC: lrwxr-xr-x 1 root root 8 Mar 6 2002 /lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1867776 Dec 7 2001 /lib/libc.1 -r-xr-xr-x 1 binbin1814528 Jun 27 2003 /lib/libc.2 -r--r--r-- 1 binbin2537200 Jun 27 2003 /lib/libc.a lrwxr-xr-x 1 root root15 Mar 6 2002 /lib/libc.sl - /usr/lib/libc.2 lrwxr-xr-x 1 root root 8 Mar 6 2002 /usr/lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1867776 Dec 7 2001 /usr/lib/libc.1 -r-xr-xr-x 1 binbin1814528 Jun 27 2003 /usr/lib/libc.2 -r--r--r-- 1 binbin2537200 Jun 27 2003 /usr/lib/libc.a lrwxr-xr-x 1 root root15 Mar 6 2002 /usr/lib/libc.sl - /usr/lib/libc.2 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--disable-shared' '--with-embedded-server' '--with-innodb' 'CC=cc' 'CFLAGS=+DAportable' 'CXXFLAGS=+DAportable' 'CXX=aCC' Perl: This is perl, version 5.005_03 built for PA-RISC2.0 Gary F. Lehr, PhD. Technical Lead, North America GIS GPES Unix Engineering Newark, DE 302-391-8490 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: powerpoint and mysql
On Fri, 2004-08-27 at 14:30, Victor Pendleton wrote: How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug That is part of the equation. I know that in ppt I can add objects but I don't see anywhere for any kind of datasource. I didn't know if anyone was working on a possible plugin or maybe knew of a way to make it work. Here's what I am doing. I handle all of the music at my church. Every week we use a projector and a laptop to put the words to the songs on a screen. I am taking the task of putting the songs on the laptop and realized that for the past two years they were creating new presentations for each week. I think this is a waste of time. At a bare minimum I am going to dump all of the songs into some sort of text document in a single directory - named by the title of the song. I use mysql (I'm not a programmer) on some very simple php/mysql stuff at work and am familiar with inputting data and then retrieving it again. If I could have a db with just two columns (title, song) I and a way to actually import this data to ppt I could make it work. In a perfect world I would create a much larger db with song keys, dates of last play, etc. I hope this clears my request up. As for MS Query or DSN, I don't use MS Query (not sure what it is) and I have no clue what DSN is either. Sorry. Thanks for the input. Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
I see two things I would change: First, the column 321st_stat.dic is the first column of your primary key and has a second index on just it. That second index is redundant and could be deleted. Second, in your WHERE clause you say : WHERE isnull(b.don) . That forces the engine to run a function on every row of the b.don column to return a logical value. The simpler and faster thing to have said is : WHERE b.don is null . That is a direct, native comparison and will use an index if one is available. You _could_ try adding an identical primary key to the stat_in table as you have on the 321st_stat table. However, since we need all of the rows from stat_in in the results, I am not sure that it will help speed up the join (because it's a left join). Even though I think the index would be ignored, it is worth a try to see if it would make a difference with a new EXPLAIN.. Have you considered wrapping your insert with : ALTER TABLE `321st_stat` DISABLE KEYS ALTER TABLE `321st_stat` ENABLE KEYS ? matt ryan [EMAIL PROTECTED] wrote on 08/27/2004 03:25:58 PM: Mikhail Entaltsev wrote: Could you execute show create table 321st_stat and show create table stat_in and send results back? I have no key's on the temp table, stat_in, do you think adding keys on the whole primary key would be faster? I wasnt sure if you could join mysql keys, the key is called primary key so would it be a.primary key = b.primary key ? mysql explain select a.* from stat_in a left outer join 321st_stat b on a.don=b.don and a.dic=b.dic and a.niin=b.niin and a.sta=b.sta and a.qty=b.qty and a.fr_ric=b.fr_ric and a.suf=b.suf and a.dte_txn =b.dte_txn where isnull(b.don); | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | | 1 | SIMPLE | a | ALL| NULL | NULL| NULL | NULL| 77269086 | | | 1 | SIMPLE | b | eq_ref | PRIMARY,don,niin,dic,dte_txn | PRIMARY |39 | finlog.a.dic,finlog.a.niin,finlog.a.fr_ric,finlog.a.don,finlog.a. suf,finlog.a.dte_txn,finlog.a.sta | 1 | Using where; Not exists | 2 rows in set (0.11 sec) ---+ | 321st_stat | CREATE TABLE `321st_stat` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '', PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), KEY `don` (`don`), KEY `niin` (`niin`), KEY `stor` (`stor`), KEY `dic` (`dic`), KEY `ctasc` (`ctasc`), KEY `dte_txn` (`dte_txn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=900,000,000 PACK_KEYS=1 | 1 row in set (0.03 sec) | stat_in | CREATE TABLE `stat_in` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=99,999,999 PACK_KEYS=1 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PowerPoint and mysql
Looking quickly at PowerPoint, you *can* do what you want to do, however, here's the catch, there is no quick way to pull from a file or data source and have it write to the field in pp. You can do some VB trickery with the built in VBA in it, where you can use the MySQL ODBC driver and could read from a db and write to the screen. Another (probably better) way would have a standalone app read the db, and with some GUI (could be VB) take over the screen (such as pp does) and display the music. I'm not sure how your church is setup with the pp presentation, but these are my initial thoughts on the matter. I realize you aren't a programmer, however, from a quick run through, I don't see a way to do what you are asking with out any programming. MSQuery is a program that usually comes with office that allows you to connect to db's inside of office apps. A DSN is a way for programs to connect to DBs (simple description). You can setup DSNs in systems under Data Sources (ODBC) under administrative tools (windows 2k/xp). HTH Thanks, James -Original Message- From: redhat [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:45 PM To: mysql Subject: RE: powerpoint and mysql On Fri, 2004-08-27 at 14:30, Victor Pendleton wrote: How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug That is part of the equation. I know that in ppt I can add objects but I don't see anywhere for any kind of datasource. I didn't know if anyone was working on a possible plugin or maybe knew of a way to make it work. Here's what I am doing. I handle all of the music at my church. Every week we use a projector and a laptop to put the words to the songs on a screen. I am taking the task of putting the songs on the laptop and realized that for the past two years they were creating new presentations for each week. I think this is a waste of time. At a bare minimum I am going to dump all of the songs into some sort of text document in a single directory - named by the title of the song. I use mysql (I'm not a programmer) on some very simple php/mysql stuff at work and am familiar with inputting data and then retrieving it again. If I could have a db with just two columns (title, song) I and a way to actually import this data to ppt I could make it work. In a perfect world I would create a much larger db with song keys, dates of last play, etc. I hope this clears my request up. As for MS Query or DSN, I don't use MS Query (not sure what it is) and I have no clue what DSN is either. Sorry. Thanks for the input. Doug -- 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 c-api 1064 mysql_real_query: issue with upgrade to 4.1.3
Hi,
For some reason, all my sql queries fail with a 1064;
These were all working under mysql-4.0 but had to
upgrade to 4.1 so i could use nested subqueries
i have attached a simple 10 line c-code as proof of
concept. Obviously there is nothing wrong with the
query...
#include stdio.h
#include stdlib.h
#include unistd.h
#include string.h
#include mysql.h
static MYSQL handle;
int main() {
char query[1500];
int c;
mysql_init(handle);
if (!mysql_real_connect(handle, localhost,
mysql, mysql, honey_db, 0, NULL, 0)) {
fprintf(stderr, connect failed %s\n,
mysql_error(handle));
exit(1);
}
sprintf(query, select * from test);
printf(%s\n, query);
c = mysql_real_query(handle, query,
strlen(query)+500);
if (c)
{
printf(query failed...%d %d\n, c,
mysql_errno(handle));
printf(mysql_error(handle));
}
}
The output is as follows (the first two lines are my
debug output)
select * from test
query failed...1 1064
You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version
for the right syntax to use near '' at line 1:
I have tried both FreeBSD
mysql-standard-4.1.3-beta-unknown-freebsd4.7-i386
and the linux-4.1.3 rpm with identical results.
Please let me know what im doing wrong The table
test exists in honey_db and is accessible by the
mysql user.
Thanks!
__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
First of all, IMHO index 321st_stat.dic is useless since you have dic as the first field in 321st_stat.PRIMARY KEY. Second, I would recommend add KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`) on stat_in table and set PACK_KEYS=0 for stat_in table. Then measure execution time of select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; If it is relatively short then the problem is in number of keys and PACK_KEYS for 321st_stat table. Please, let me know about your results. Best regards, Mikhail. - Original Message - From: matt ryan [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 9:25 PM Subject: Re: 1 day 28 min insert Mikhail Entaltsev wrote: Could you execute show create table 321st_stat and show create table stat_in and send results back? I have no key's on the temp table, stat_in, do you think adding keys on the whole primary key would be faster? I wasnt sure if you could join mysql keys, the key is called primary key so would it be a.primary key = b.primary key ? mysql explain select a.* from stat_in a left outer join 321st_stat b on a.don=b.don and a.dic=b.dic and a.niin=b.niin and a.sta=b.sta and a.qty=b.qty and a.fr_ric=b.fr_ric and a.suf=b.suf and a.dte_txn =b.dte_txn where isnull(b.don); | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | | 1 | SIMPLE | a | ALL| NULL | NULL| NULL | NULL| 77269086 | | | 1 | SIMPLE | b | eq_ref | PRIMARY,don,niin,dic,dte_txn | PRIMARY |39 | finlog.a.dic,finlog.a.niin,finlog.a.fr_ric,finlog.a.don,finlog.a.suf,finlog. a.dte_txn,finlog.a.sta | 1 | Using where; Not exists | 2 rows in set (0.11 sec) ---+ | 321st_stat | CREATE TABLE `321st_stat` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '', PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), KEY `don` (`don`), KEY `niin` (`niin`), KEY `stor` (`stor`), KEY `dic` (`dic`), KEY `ctasc` (`ctasc`), KEY `dte_txn` (`dte_txn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=900,000,000 PACK_KEYS=1 | 1 row in set (0.03 sec) | stat_in | CREATE TABLE `stat_in` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=99,999,999 PACK_KEYS=1 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many to Many: Does this make sense ?
My replies are interspersed below. Rhino - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Stuart Felenstein [EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:05 PM Subject: Re: Many to Many: Does this make sense ? Now I am totally clear on it! Took a few reiterations but I'm there. Even more I'm going to abandon the idea of titles. I was breathing a sigh of relief that we'd finally articulated the idea clearly enough. But now I'm not so sure What do you mean you are going to abandon the idea of titles? To implement it correctly, per the examples, you provided is an impossibility. At least something when weighed out doesn't mean enough to the effort it would take. Plus the lack it would still leave. Huh? I don't think we proved that it was impossible to implement titles. In fact, I think we proved the exact opposite, that the right way to implement them was with association tables. Maybe you should show us your revised database design before you try to implement it, just to be sure that you aren't missing something important. I'm going to go over my schema though and see where the many to many relationships will work well for me. I'm assuming telephone numbers (SL Green's example) that are specific to the user / member need not necessarily be broken out ? Perhaps they should be. I think Shawn's example (correct me if I'm wrong, Shawn) was meant to show that if you have a variable number of types of phone numbers and new types of phone numbers appearing regularly, it would make more sense to store them as he showed than to keep adding new phone number columns to the member table. When I was a kid, back in the early mists of time, most people had one and only one phone number. (There were still even people that had no phones at all.) The phone, if you had one, probably sat in your kitchen. Very few people even had extension phones and it was almost unheard of for people to have a second phone line in the house; it might happen if the house was both a residence and the site of a small business. In those days, it was pretty rare for people to call you at the office about something that had to do with your personal life; for example, if your cheque to the hydro company was overdue, they'd call you at home. So, if you wanted to record information about people in a database, you'd probably just record their home phone number. Today, things have changed a great deal. Now it is very common for a home phone to have many extensions. Many people have two or more phone lines; one might be for faxes, the other for voice calls and DSL. Many people have cell phones so that you can reach them when they are out. Many companies are far more tolerant of their employees discussing personal matters on company time. Many people have pagers, Blackberries, etc. So what information would you store for a person if you were building a database today? Well, depending on how urgently you wanted to talk to them, you might store every number they had. If you are building an employee database and one particular employee was utterly critical to your operations, you might want to store his home phone number, his cell number, his work number, the number of his cottage in case he was on vacation, a pager number, and so on, on the theory that you could just keep dialing numbers in the case of a crisis and find him. So how do you implement that in the database? Well, you *could* simply add a new column for each type of phone number: one for home number, one for office number, one for fax number, one of pager, one for the cottage number, etc. etc. Or, you could use Shawn's idea and store the phone numbers in a separate table. If you stored the phone numbers the way Shawn described, you wouldn't have to change the Member table at all, even if several new types of phone numbers emerged over the years; in that case, you'd just add the code for the new phone number type, say 'STCB' for Star Trek Comm Badge, and then store all STCB numbers in the Phone Number table, right along with the home phone numbers, cell phone numbers, etc. Whether to break out the phone numbers that way or just keep adding new phone number columns to the Member table is up to you; both are valid and both have their positive and negative consequences. Speaking strictly for myself, I tend to break things like that out into their own tables as soon as I anticipate that the situation might happen. But others might prefer to keep things like phone numbers in the Member table until they see a real increase in phone number types. That's a judgement call based on your experience. You don't have a lot of experience yet so you might settle for flipping a coin ;-) But seriously, you should try to reason through the consequences, positive and negative, of each approach and then choose the one that you think best in light of what you expect will happen in
RE: PowerPoint and mysql
On Fri, 2004-08-27 at 15:06, James wrote: Looking quickly at PowerPoint, you *can* do what you want to do, however, here's the catch, there is no quick way to pull from a file or data source and have it write to the field in pp. You can do some VB trickery with the built in VBA in it, where you can use the MySQL ODBC driver and could read from a db and write to the screen. Another (probably better) way would have a standalone app read the db, and with some GUI (could be VB) take over the screen (such as pp does) and display the music. I'm not sure how your church is setup with the pp presentation, but these are my initial thoughts on the matter. I realize you aren't a programmer, however, from a quick run through, I don't see a way to do what you are asking with out any programming. MSQuery is a program that usually comes with office that allows you to connect to db's inside of office apps. A DSN is a way for programs to connect to DBs (simple description). You can setup DSNs in systems under Data Sources (ODBC) under administrative tools (windows 2k/xp). HTH Thanks, James -Original Message- From: redhat [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:45 PM To: mysql Subject: RE: powerpoint and mysql On Fri, 2004-08-27 at 14:30, Victor Pendleton wrote: How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug That is part of the equation. I know that in ppt I can add objects but I don't see anywhere for any kind of datasource. I didn't know if anyone was working on a possible plugin or maybe knew of a way to make it work. Here's what I am doing. I handle all of the music at my church. Every week we use a projector and a laptop to put the words to the songs on a screen. I am taking the task of putting the songs on the laptop and realized that for the past two years they were creating new presentations for each week. I think this is a waste of time. At a bare minimum I am going to dump all of the songs into some sort of text document in a single directory - named by the title of the song. I use mysql (I'm not a programmer) on some very simple php/mysql stuff at work and am familiar with inputting data and then retrieving it again. If I could have a db with just two columns (title, song) I and a way to actually import this data to ppt I could make it work. In a perfect world I would create a much larger db with song keys, dates of last play, etc. I hope this clears my request up. As for MS Query or DSN, I don't use MS Query (not sure what it is) and I have no clue what DSN is either. Sorry. Thanks for the input. Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks for the info. What resource did you use to come up with this information - can you share that? I suppose it may be worth checking to see if Access might work more readily than mysql?? I have limited exposure to Access and don't really like it but it may be an easier route?? I would like to view your resources though. thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
You _could_ try adding an identical primary key to the stat_in table as you have on the 321st_stat table. However, since we need all of the rows from stat_in in the results, I am not sure that it will help speed up the join (because it's a left join). Even though I think the index would be ignored, it is worth a try to see if it would make a difference with a new EXPLAIN.. But it should speed up grouping by PRIMARY KEY in: insert into 321st_stat select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
If that were a valid SQL statement, I would agree with you. What you wrote **only** functions through a MySQL-specific SQL extension that permits non-aggregated, non-grouped columns to exist in the SELECT clause. I do not understand why you believe that a GROUP BY test will prove any sort of performance gain for his original INSERT problem. It took just over 24 hours to do an INSERT IGNORE to add just about 1000 rows to a 77 million row table. What metric of that performance will your GROUP BY test help to prove? Respecfully puzzled, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mikhail Entaltsev [EMAIL PROTECTED] wrote on 08/27/2004 04:36:43 PM: You _could_ try adding an identical primary key to the stat_in table as you have on the 321st_stat table. However, since we need all of the rows from stat_in in the results, I am not sure that it will help speed up the join (because it's a left join). Even though I think the index would be ignored, it is worth a try to see if it would make a difference with a new EXPLAIN.. But it should speed up grouping by PRIMARY KEY in: insert into 321st_stat select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; Mikhail.
Re: Many to Many: Does this make sense ?
Let me answer the first part now and then continue reading. You absolutely gave me some great ideas / examples. I understand it very well now. I'm abandoning it solely because creating that association list -1) may take forever and still come up way short 2)I see how others have implemented the same type of application and have not provided specific job title allocation. I hope that gets you settled back again! Stuart --- Rhino [EMAIL PROTECTED] wrote: My replies are interspersed below. Rhino - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Stuart Felenstein [EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:05 PM Subject: Re: Many to Many: Does this make sense ? Now I am totally clear on it! Took a few reiterations but I'm there. Even more I'm going to abandon the idea of titles. I was breathing a sigh of relief that we'd finally articulated the idea clearly enough. But now I'm not so sure What do you mean you are going to abandon the idea of titles? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PowerPoint and mysql
I didn't look on the net, I used PowerPoint, to come to this conclusion: Now, I'm not sure of your office install, so let me summarize what I have installed: Office XP fully patched, when I installed it, I installed everything except ms office toolbar and the stupid paperclip ;^D Ok, inside PowerPoint, I went to Tools--Macro--Visual Basic Editor This opens up a new window (vb editor) From here I deduced that you can access slide properties (hit F2, then where it says All Libraries there is a thing there that says PowerPoint) that you can set the slide(s) properties, ie: text, location of text boxes, etc. I am no way able to help you with this part, I try to steer clear of Office and DB's (other than access). Access may work, but again, I'm a VB programmer, and I steer clear of using any office type dependencies. I have done large display news tickers where they would in effect take over a screen, read information from a DB and post the information on a Label in a Form. There was also a separate app that did the updating of the db. Sorry I couldn't give you a definite How-To, but this is the best advice I can give. Thanks, James -Original Message- From: redhat [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:36 PM To: mysql Subject: RE: PowerPoint and mysql On Fri, 2004-08-27 at 15:06, James wrote: Looking quickly at PowerPoint, you *can* do what you want to do, however, here's the catch, there is no quick way to pull from a file or data source and have it write to the field in pp. You can do some VB trickery with the built in VBA in it, where you can use the MySQL ODBC driver and could read from a db and write to the screen. Another (probably better) way would have a standalone app read the db, and with some GUI (could be VB) take over the screen (such as pp does) and display the music. I'm not sure how your church is setup with the pp presentation, but these are my initial thoughts on the matter. I realize you aren't a programmer, however, from a quick run through, I don't see a way to do what you are asking with out any programming. MSQuery is a program that usually comes with office that allows you to connect to db's inside of office apps. A DSN is a way for programs to connect to DBs (simple description). You can setup DSNs in systems under Data Sources (ODBC) under administrative tools (windows 2k/xp). HTH Thanks, James -Original Message- From: redhat [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:45 PM To: mysql Subject: RE: powerpoint and mysql On Fri, 2004-08-27 at 14:30, Victor Pendleton wrote: How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug That is part of the equation. I know that in ppt I can add objects but I don't see anywhere for any kind of datasource. I didn't know if anyone was working on a possible plugin or maybe knew of a way to make it work. Here's what I am doing. I handle all of the music at my church. Every week we use a projector and a laptop to put the words to the songs on a screen. I am taking the task of putting the songs on the laptop and realized that for the past two years they were creating new presentations for each week. I think this is a waste of time. At a bare minimum I am going to dump all of the songs into some sort of text document in a single directory - named by the title of the song. I use mysql (I'm not a programmer) on some very simple php/mysql stuff at work and am familiar with inputting data and then retrieving it again. If I could have a db with just two columns (title, song) I and a way to actually import this data to ppt I could make it work. In a perfect world I would create a much larger db with song keys, dates of last play, etc. I hope this clears my request up. As for MS Query or DSN, I don't use MS Query (not sure what it is) and I have no clue what DSN is either. Sorry. Thanks for the input. Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks for the info. What resource did you use to come up with this information - can you share that? I suppose it may be worth checking to see if Access
Re: 1 day 28 min insert
If that were a valid SQL statement, I would agree with you. What you wrote **only** functions through a MySQL-specific SQL extension that permits non-aggregated, non-grouped columns to exist in the SELECT clause. He is working with MySQL and that query is working in MySQL. So everything should be fine. :) But I agree that it is MySQL-specific query. I do not understand why you believe that a GROUP BY test will prove any sort of performance gain for his original INSERT problem. It took just over 24 hours to do an INSERT IGNORE to add just about 1000 rows to a 77 million row table. What metric of that performance will your GROUP BY test help to prove? Yes, it is unclear from my previous email. Let's look at that query: insert into 321st_stat select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; Why it could be slow? Either select part is slow, either insert or both. If select part is slow then adding KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`) on stat_in table should help. If select part is fast then IMHO the problem is in number of keys and PACK_KEYS for 321st_stat table. Right? Best regards, Mikhail. - Original Message - From: [EMAIL PROTECTED] To: Mikhail Entaltsev Cc: matt ryan ; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:40 PM Subject: Re: 1 day 28 min insert If that were a valid SQL statement, I would agree with you. What you wrote **only** functions through a MySQL-specific SQL extension that permits non-aggregated, non-grouped columns to exist in the SELECT clause. I do not understand why you believe that a GROUP BY test will prove any sort of performance gain for his original INSERT problem. It took just over 24 hours to do an INSERT IGNORE to add just about 1000 rows to a 77 million row table. What metric of that performance will your GROUP BY test help to prove? Respecfully puzzled, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PowerPoint and mysql
One way to get started on this is to record a macro in PowerPoint where you are adding a textbox with some text in it to a slide I tried this and this was the code (VBA) ActiveWindow.Selection.SlideRange.Shapes.AddTextbox(msoTextOrientationHorizo ntal, 72#, 120#, 540#, 36#).Select ActiveWindow.Selection.ShapeRange.TextFrame.WordWrap = msoTrue ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=1, Length:=0).Select With ActiveWindow.Selection.TextRange .Text = Some Song Title With .Font .Name = Times New Roman .Size = 24 .Bold = msoFalse .Italic = msoFalse .Underline = msoFalse .Shadow = msoFalse .Emboss = msoFalse .BaselineOffset = 0 .AutoRotateNumbers = msoFalse .Color.SchemeColor = ppForeground End With End With ActiveWindow.Selection.Unselect Basically what you have here is the base code to add a textbox to a blank slide, position it and add some text to it. Where the text is being added, you read data from your database and output that text. I am assuming there is going to be some standard format to all the presentations, the only change being the text displayed. You can have the slides created with the textboxes and simply set the text based on some initial selection from the user (song name or some such). This is not a complete solution to your problem but should point you to the right direction. Start of simple first. Forget about reading from a database for the moment. First get dynamic slides created. Once you have that working move on to retrieving the text from the database and displaying on trhe slides. Navin -Original Message- From: James [mailto:[EMAIL PROTECTED] Sent: Friday, 27 August 2004 04:58 To: 'redhat'; 'mysql' Subject: RE: PowerPoint and mysql I didn't look on the net, I used PowerPoint, to come to this conclusion: Now, I'm not sure of your office install, so let me summarize what I have installed: Office XP fully patched, when I installed it, I installed everything except ms office toolbar and the stupid paperclip ;^D Ok, inside PowerPoint, I went to Tools--Macro--Visual Basic Editor This opens up a new window (vb editor) From here I deduced that you can access slide properties (hit F2, then where it says All Libraries there is a thing there that says PowerPoint) that you can set the slide(s) properties, ie: text, location of text boxes, etc. I am no way able to help you with this part, I try to steer clear of Office and DB's (other than access). Access may work, but again, I'm a VB programmer, and I steer clear of using any office type dependencies. I have done large display news tickers where they would in effect take over a screen, read information from a DB and post the information on a Label in a Form. There was also a separate app that did the updating of the db. Sorry I couldn't give you a definite How-To, but this is the best advice I can give. Thanks, James -Original Message- From: redhat [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:36 PM To: mysql Subject: RE: PowerPoint and mysql On Fri, 2004-08-27 at 15:06, James wrote: Looking quickly at PowerPoint, you *can* do what you want to do, however, here's the catch, there is no quick way to pull from a file or data source and have it write to the field in pp. You can do some VB trickery with the built in VBA in it, where you can use the MySQL ODBC driver and could read from a db and write to the screen. Another (probably better) way would have a standalone app read the db, and with some GUI (could be VB) take over the screen (such as pp does) and display the music. I'm not sure how your church is setup with the pp presentation, but these are my initial thoughts on the matter. I realize you aren't a programmer, however, from a quick run through, I don't see a way to do what you are asking with out any programming. MSQuery is a program that usually comes with office that allows you to connect to db's inside of office apps. A DSN is a way for programs to connect to DBs (simple description). You can setup DSNs in systems under Data Sources (ODBC) under administrative tools (windows 2k/xp). HTH Thanks, James -Original Message- From: redhat [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:45 PM To: mysql Subject: RE: powerpoint and mysql On Fri, 2004-08-27 at 14:30, Victor Pendleton wrote: How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple
Connectivity
Hi, I have one mysql database and one oracle database.I want to fetch data from mysql db to oracle db in realtime.How to do this? I also wanted to tarnsfer 14 tables from one mysql database to Oracle how to do this? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
Database connectivity
Hi, I wanted to copy setup some procedure which pickup data from mysql and load into oracle database.How to do this? I would also like to know how to establish a connection between mysql and oracle database? thanks - Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now.
Re: Many to Many: Does this make sense ?
I guess I'll have to wait until you've had a chance to mull the whole note over ;-) I really don't know what you're saying. I can't think of anything I said that would have discouraged you from creating the association table. Certainly the number of columns in the table shouldn't discourage you since there are typically only two columns and they are usually both short codes. The number of rows *may* be a concern though; if you have a lot of job titles and a keep track of every job every member has had, you could end up with a lot of rows. Maybe that's your concern. In any case, I hope you can explain what you are going to do instead of the association table if you have definitely abandoned it. Maybe we can critique that design for you and help you improve it or at least avoid the most common pitfalls. Rhino - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Stuart Felenstein [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:47 PM Subject: Re: Many to Many: Does this make sense ? Let me answer the first part now and then continue reading. You absolutely gave me some great ideas / examples. I understand it very well now. I'm abandoning it solely because creating that association list -1) may take forever and still come up way short 2)I see how others have implemented the same type of application and have not provided specific job title allocation. I hope that gets you settled back again! Stuart --- Rhino [EMAIL PROTECTED] wrote: My replies are interspersed below. Rhino - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Stuart Felenstein [EMAIL PROTECTED] Sent: Friday, August 27, 2004 3:05 PM Subject: Re: Many to Many: Does this make sense ? Now I am totally clear on it! Took a few reiterations but I'm there. Even more I'm going to abandon the idea of titles. I was breathing a sigh of relief that we'd finally articulated the idea clearly enough. But now I'm not so sure What do you mean you are going to abandon the idea of titles? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems Upgrading from 3.23 to 4.0
Greetings, I have a couple of questions that were not covered in the upgrade documentation. 1) The documentation discusses creating the new mysql database with the new privledges incorporated. Not a problem with that concept. 2) I can move my other database directories to the new data directory - Not a problem with that move. 3) My problem is how does one get the information that is contained in the old mysql database into the the new mysql database other than having to manually enter all of the information again, such as create database, grant user etc. Is there some script or something that I seem to be missing. Have spent a tremendous amount of time digging thru the scripts and documentation but to no avail. Please advise Keith
Re: Problems Upgrading from 3.23 to 4.0
At 17:06 -0700 8/27/04, Keith Brownmiller wrote: Greetings, I have a couple of questions that were not covered in the upgrade documentation. 1) The documentation discusses creating the new mysql database with the new privledges incorporated. Not a problem with that concept. You don't need to create a *new* mysql database for an upgrade. You use your old one. After installing the upgrade, run the mysql_fix_privilege_tables script to modify your existing grant tables to add the new privileges to them. http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html 2) I can move my other database directories to the new data directory - Not a problem with that move. 3) My problem is how does one get the information that is contained in the old mysql database into the the new mysql database other than having to manually enter all of the information again, such as create database, grant user etc. Is there some script or something that I seem to be missing. Have spent a tremendous amount of time digging thru the scripts and documentation but to no avail. Please advise Keith -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared statements C API 4.1.3
Is anyone using the new prepared statements C API in MySQL 4.1.3 with success? I see a number of people having issues - I'd like to hear about someone (anyone) actually using them successfully (platforms, compilers, issues, un-documented/mis-documented usage, etc.). I haven't been able to get it working (Sparc Solaris 9, 32 bit, binary-max 4.1.3beta). For me, it's one of the compelling reasons to upgrade, but I have yet to see it work, and given 4.1 is already to beta, I'm surprised by how problematic it seems to be at this point. If you've had success, please share. Thx, R
c api and creating looped queries
I know this is more along the lines of a c question; however, I am trying to write a loop to iterate insertions into a mysql database and was hoping someone would have a quick fix for this. I am used to using php with the luxury of the following syntax some loop giving values $column1 and $column2 usually from some array or parsing of a file $query = mysql_query( insert into my_table values ( null, '$column1', 'some description $column2' ); next iteration Can anyone just show a one liner of how to do this in c where the values are column1 and column2? I know there is a string concatenation function, it just seems so clumsey to write it out, get the string length of each of the two variables and create a new longer line. I won't be suprised if that is what I have to do though. ( being new to c and finding out how much more work it entails:-) ). Hope this make since at nearly 1 am. Been a long day... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: c api and creating looped queries
On Sat, 2004-08-28 at 00:48, I wrote: I know this is more along the lines of a c question; however, I am trying to write a loop to iterate insertions into a mysql database and was hoping someone would have a quick fix for this. I am used to using php with the luxury of the following syntax some loop giving values $column1 and $column2 usually from some array or parsing of a file $query = mysql_query( insert into my_table values ( null, '$column1', 'some description $column2' ); next iteration Can anyone just show a one liner of how to do this in c where the values are column1 and column2? I know there is a string concatenation function, it just seems so clumsey to write it out, get the string length of each of the two variables and create a new longer line. I won't be suprised if that is what I have to do though. ( being new to c and finding out how much more work it entails:-) ). Hope this make since at nearly 1 am. Been a long day... OK, hate to answer my own question but sprintf is what I was looking for. It is hard to switch languages! Especially in this direction. ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bug report!!
server not starting beginnig two-three days ago. All went well until then, when i had some large queries on server, and i think that was the moment when it crashed. I tried increasing the amount of innodb_buffer_pool_size from 8 to 16M , it started now, but the same list of errors appears in the log. Notice: same server, exactly same configuration runs as master on a different machine, and didn't cause such problems. I had some trouble (sometimes it stucks when i try to connect from console with mysql). i'd like someone who handles bug to take a look on this error log: MySQL: ready for connections. Version: '5.0.0-alpha-max-debug-log' socket: '' port: 3306 Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\sql_lex.cpp:144' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\mulalloc.c:51' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\client.c:1824' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\client.c:1825' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c:181' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\sql_base.cpp:869' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c:99' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:100' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_open.c:132' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:804' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:815' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:834' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:835' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c:99' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:834' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:835' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_open.c:91' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c:99' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\table.cpp:647' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\ha_innodb.cpp:4766' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\mulalloc.c:51' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\lock.cpp:414' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\mysys\my_alloc.c:99' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\sql_lex.cpp:152' Error: Memory allocated at C:\build500\build\mysql-5.0.0-alpha\sql\log_event.cpp:1196 was overrun, discovered at 'C:\build500\build\mysql-5.0.0-alpha\sql\sql_lex.cpp:153' Error: Memory allocated at
