db design
which method is better to do. I have 5 tables. They represent sections/parts of a companies standards. There are 13 main categories, each of those categories has subsections (some with 3, some with 10 or more), those subsections have subsections, etc. Which table design is better to do. Table A ID Std_no Name Table B ID table_a_id Std_no Name Table C ID table_b_id Std_no Name Table D ID table_c_id Std_no Name Table E ID table_d_id Std_no Name In this case, each table links back to the one before it. via the unique ID OR Table A std_a Name Table B std_a std_b Name Table C std_a std_b std_c Name Table D std_a std_b std_c std_d Name Table E std_a std_b std_c std_d std_e Name In this case, additional fields are used and all pieces linking to the previous part of the standard are used. This will be used in a web app. Cascading Drop down boxes will be used and a person will select the standard. Thus, they select Item 3 from table A, selection box B has only items related to from table A that are related to Item 3. They select from selection box c which may (or may not) have items related to what was in table B. I hope this makes sense. Hard to diagram it here. Thanks! Brett -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.waldo.com/www.fmsystems.biz); /Lasso Professional Alliance Member ID #LPA135259 (www.lassosoft.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sun to buy Mysql - mysql press release
http://mysql.com/news-and-events/sun-to-acquire-mysql.html -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.waldo.com/www.fmsystems.biz); /Lasso Professional Alliance Member ID #LPA135259 (www.lassosoft.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: Sun to buy Mysql
http://www.reuters.com/article/mergersNews/idUSWNAS661820080116 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.waldo.com/www.fmsystems.biz); /Lasso Professional Alliance Member ID #LPA135259 (www.lassosoft.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No Primary Key and No Index?
Does not having a Primary Key and No indexes really speed up inserts significantly? We have a log table. it has the fields, cart_id, referer, remote_ip, server_name, user_agent, company, action, type, and value that we are tracking vistor log information for our ecommerce site. Every page is tracked that a person goes to in the log file. Currently we have about 500,000 rows. So I am wondering if we are really saving that much by not having a Primary Key and no indexes. Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, that error is only when doing a stop/shutdown. So it doesnt make a lot of sense that would be the problem. Here's the log where I did a stop and start. 060724 20:54:25 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown 060724 20:54:25 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060724 20:54:25 [Note] Slave I/O thread killed while reading event 060724 20:54:25 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.01', position 5582202 060724 20:54:25 [Note] Error reading relay log event: slave SQL thread was killed 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 6 user: 'bharvey' 060724 20:54:28 [Warning] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Forcing close of thread 5 user: 'bharvey' 060724 20:54:28 InnoDB: Starting shutdown... 060724 20:54:29 InnoDB: Shutdown completed; log sequence number 0 44044 060724 20:54:29 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete 060724 20:55:10 InnoDB: Started; log sequence number 0 44044 060724 20:55:10 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.22-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060724 20:55:10 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.01' at position 5582202, relay log '.\FMSbuilding-relay-bin.05' position: 17820 060724 20:55:10 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.01' at position 5582202 I did some manual inserts into the database on the slave, just to make sure there was no problem - those worked fine and were searchable. Brett Are there any other messages apart from the 2013 error? Are there any options on the filesystem that are perhaps stopping any writing by the mysql user? At this point, I have to say I'm just about out of ideas. Regards -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, It is pointing correctly. Plus, I can use a mysql browser and 3rd party tools on the slave server and read the databases/tables properly. I've tried stopping and restarting. I've rebooted... I'm baffled too, obviously. lol Thanks! Brett I am absolutely baffled as to how the binlog/relaylog can be updated but the dbs/tables etc. are not being touched. The only other thing is to check the datadir on your server. Is it actually pointing to where you think it is? You can do a SHOW VARIABLES LIKE 'datadir'; and check it is pointing to where you think it should be. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, Thanks for attempting to help. When I look at my show slave status, its updating. When I look at the FMSbuilding-relay-bin, its updating! It has the information that's taken place on the master server. I can see information in the FMSbuilding-relay-bin on the slave server that I did on the master. But, the databases/tables/data on the slave are not being updated. Brett I am a little bit lost as to why this is happening. The only suggestion I have at this point is to go through the replication troubleshooting in this chapter and see if something there is causing the problem. Looking at what you have done and the docs, I don't think this will help. This may be a bug. Sorry I can't be of more assistance. http://dev.mysql.com/doc/refman/5.0/en/replication.html I did find this in the bug system, you may be able to either re-open it or log a new one http://bugs.mysql.com/bug.php?id=10157 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, I could try and create a table. But I've got it set just for the one database "FMS". The error log, last night, was showing this (I think I had it in my first post) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed The two are on the same network, same switch in fact. I can do commands from one to the other without problem. The logs seem to update in that sense but when looking at the data - no go. I'll check binlogs on the slave. Brett Just about to go when your email rocked up 8-) Like you, I can't see any reason why it is not replicating according to the logs. Have you tried to create a database on the master and see if it appears on the slave? What commands are being replicated at this time? You can use the mysqlbinlog command to check the binary log to see what it is trying to replicate. There are certain commands that are not replicated and we want to eliminate those from the equation. Try the create database command eg. On master CREATE DATABASE THING; On Slave SHOW DATABASES; You should see the database thing on your slave. You can then drop the database on the master and it should disappear. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -----Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 9:51 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem David, mysql> show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql> show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
David, mysql> show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.01 | 903763 | | | +---+--+--+--+ 1 row in set (0.00 sec) mysql> show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xxx.xxx.xxx Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.01 Read_Master_Log_Pos: 903763 Relay_Log_File: FMSbuilding-relay-bin.04 Relay_Log_Pos: 901275 Relay_Master_Log_File: FMSweb-bin.01 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 903763 Relay_Log_Space: 901275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Gnite to you! lol I just got up... Thanks! Brett Hi Brett, Can you please do the following : SHOW MASTER STATUS; (on the master) and SHOW SLAVE STATUS; (on the slave) Thanks P.S I will be going to bed soon so don't worry if you don't hear anything, I'll look at it first thing in the morning. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
RE: Replication Problem
Dave, I tried reset slave as you mentioned - no change. Nothing updating. Same error. Brett Aah, sorry, I didn't explain myself very well 8-( At the mysql prompt RESET SLAVE; This tells the slave to reset itself back, and forget the current settings (eg. the log position etc., not the master or such details) to where the master started and begin replication from there. There is also a complementary RESET MASTER which tells the master to get rid of all binary logs and start again. RESET SLAVE is documented here http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:49 PM To: mysql@lists.mysql.com Subject: RE: Replication Problem Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message----- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks & Regards Dilipkumar -- --- - /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); --- - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Problem
Hello David, Reset how? I did the CHANGE MASTER TO and set it as what was specified from the Show Master command after I tar'd the files. Thanks! Brett Hi Brett, Did you reset the master and/or slave? Looking at the logs, it appears as though the slave is trying to read from a different position in the log than the master has reached. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Brett Harvey [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 1:36 PM To: mysql@lists.mysql.com Subject: Re: Replication Problem Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks & Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem
Hi, Thanks for the response. Yes - I have that in my config also. replicate-do-db=FMS Thanks Brett Hi, Did u mention what all databases has to be replicated in ur cnf file as (replicate-do-db=database-name). Thanks & Regards Dilipkumar -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem
Well, I successfully setup replication for one server/slave. Now I'm trying to do the same for another set. I've tried both the tar method and load data from master and can't seem to get either to work. Nothing replicates to the slave. I'm running Mysql 5.022. === My error log is showing this - note the error reading packet from server. 060723 21:01:01 InnoDB: Started; log sequence number 0 44044 060723 21:01:01 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 349828, relay log '.\FMSbuilding-relay-bin.06' position: 236 060723 21:01:01 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 349828 060723 21:01:01 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.21-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL) 060723 21:01:11 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 060723 21:01:11 [Note] Slave I/O thread killed while reading event 060723 21:01:11 [Note] Slave I/O thread exiting, read up to log 'FMSweb-bin.04', position 349828 060723 21:01:11 [Note] Error reading relay log event: slave SQL thread was killed 060723 21:01:41 [Note] Slave SQL thread initialized, starting replication in log 'FMSweb-bin.04' at position 98, relay log '.\FMSbuilding-relay-bin.01' position: 4 060723 21:01:41 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FMSweb-bin.04' at position 98 After taring my Master Status was this: mysql> SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 98 | | | +---+--+--+--+ 1 row in set (0.00 sec) Upon doing a Start Slave I had his for Slave Status show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 206.103.218.36 Master_User: FMSReplicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: FMSweb-bin.04 Read_Master_Log_Pos: 349828 Relay_Log_File: FMSbuilding-relay-bin.02 Relay_Log_Pos: 349966 Relay_Master_Log_File: FMSweb-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: FMS Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 349828 Relay_Log_Space: 349966 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) My Master Status is mysql> show master status\g +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | FMSweb-bin.04 | 349828 | | | +---+--+--+--+ 1 row in set (0.00 sec) === My Master Config is server-id=1 My Salve Config is server-id=2 replicate-do-db=FMS master-host=xxx.xxx.xxx.xxx master-user=FMSReplicate master-password=password relay-log=FMSbuilding-relay-bin log-bin=FMSbuilding-bin I did: CHANGE MASTER TO -> MASTER_HOST='xxx.xxx.xxx.xxx', -> MASTER_USER='TenenzReplicate', -> MASTER_PASSWORD='password', -> MASTER_LOG_FILE='FMSweb-bin.04', -> MASTER_LOG_POS=98; any ideas? how do I get it to replicate properly? Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
Re: Unidata to Mysql
Hello David, Thanks for the response. I don't know which version yet. I just started a month ago with the company and am just starting on this project. I will find out. The site has not been updated in 5 years though... so the Unidata database must be at least 6 years old. Brett At 06:57 AM +0930 05/09/06, David Logan wrote: Hi Brett, Which version of Unidata? I doubt very much if the migration toolkit would assist with this. You will probably have to re-normalise the data due to the multi-value aspects of the Unidata/Universe database. This would probably require the addition of several more tables to cope (dependent on the original design of the database). Regards -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unidata to Mysql
Has anyone converted from Unidata db to Mysql? How easy/difficult is it to do? Does the Mysql Migration toolkit help with that process? an old consulting company setup a website with Unidata and perl... we want to convert to mysql... Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
Both methods are "commonly" used. Another method used is to have multiple pages like you've mentioned but save all the data into a session (of just pass it from page to page) until all 3 pages are complete and then write all the data at once The question to ask yourself is, do you want just partial data, an incomplete form, to be in your database/table. If you don't, then method 1 or the method I just mentioned is the way to go. Otherwise you'll have incomplete data in your dataset. Surveys do both of any/all 3 of these methods. Of course, with ajax now, I'm seeing more written to the database while a user is inputing their data and then the web application has to remove, at some point, incomplete data. Good Luck! Have to develop form with over 100 input fields plus 40 different drop downs. Seeking advice on technique to use. Thinking about single form where user has to use the power bar to move deeper into the form. This has benefit that every thing is written to the database at one time, but the draw back is the user frustration in entering so much data at one time. Alternate thoughts are breaking it down into 2 or 3 separate pages with each page being written to the data base. First page does a insert to create the row, then following pages do updates to complete populating the row with data. Has anyone done anything like this or have seen this done before. What advice can you offer. Where to look for examples. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL HAVING statement ?
"Rhino" <[EMAIL PROTECTED]> wrote: Second, you're using the HAVING clause incorrectly in your query. HAVING only works with GROUP BY, i.e. you MUST have a GROUP BY to be able to use HAVING. (Note: You can have a GROUP BY without using HAVING but you cannot use HAVING unless a GROUP BY is present.) Since you have no GROUP BY, there is no way that this query will ever work. I disagree. SELECT CONCAT(people2.First_Name, ' ', people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = 'FACULTY' AND zNAME LIKE '%' ORDER BY events.date_start DESC This wouldn't work. select userfirstnm, userlastnm, concat(userfirstnm, ' ',userlastnm) as zname from FMS.WebUsers_sql where zname Like "%brett%" results in Unknown column 'zname' in 'where clause'. There "where" must be on the real column name. "Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. " http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html However, select userfirstnm, userlastnm, concat(userfirstnm,' ',userlastnm) as zname from FMS.WebUsers_sql having zname Like "%brett%" works. Having must come after any grouping, but a group by is not required. Per the documentation "A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. (Standard SQL requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.)" The concat is the aggregate function. However, it works on just aliases also. SELECT userfirstnm as first, userlastnm, from FMS.WebUsers_sql having first Like "%brett%" What made this work was simply using the % he had forgotten SELECT CONCAT(people2.First_Name, " ", people2.Last_Name) AS zNAME, events.name_short, events.date_start FROM people2 INNER JOIN event_people ON people2.ID = event_people.peopleID INNER JOIN events ON event_people.eventID = events.ID WHERE event_people.people_role = "FACULTY" HAVING zNAME LIKE "%%" ORDER BY events.date_start DESC -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
Re: SQLyog Enterprise
Love it - use it all the time. Saw it at the Mysql conference, bought it that night. Use it every day. Well worth $50. I'm contemplating buying SQLyog Enterprise for $49 dollars (non-commerical) for personal use. Do anyone use it and how does it fare in your opinion? -- Power to people, Linux is here. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Securing Mysql 4.1 on the mac.
Hello,
I went through the install for mysql 4.1.11 on the mac. Two different macs.
When I leave the password empty (don't make changes), I can connect
with CocoaMysql and the Mysql Administrator without any problem.
Obviously, I don't want to leave the root (or any user) without a
password. So, I follow the instructions. from shell>
mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');
doing a select hostname, user from mysql.user; shows me the password
is now set.
I do "flush privileges" as it says.
Now, I try logging in with CocoaMysql or the Mysql Administrator and
it fails! every time.
I've had no problem doing this on my PC. On the mac I seem to be
stuck. I've repeated the process several times.
I tried upgrading the grant tables. Still, no go.
Suggestions? Ideas? I really don't want to leave these open, as I'm
sure every agrees would be wrong.
Thanks!
Brett
--
/Brett C. Harvey;
/Creative-Pages.Net, President;
/Facility Management Systems, CTO (www.fmsystems.biz);
/Lasso Partner Association Member ID #LPA135259
(www.omnipilot.com/www.lassopartner.com);
MAX_JOIN_SIZE error. 4.0 v 4.1
I have a query that worked just fine in Mysql 3.x and 4.0. It no longer works in 4.1. I receive the "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay" error My max_join_size is set at the default 4294967295. It appears that is also the max, correct? When I set it lower, it goes lower. When I set it higher it reverts to max_join_size=4294967295. I've simplified the fields being returned for this example. Many more fields are returned. Additionally, in this case, only one record is being found. However, in most cases, one hundred or so are being returned. This is my sql statement: SELECT PlanBudget.SN FROM FOO.PlanBudget LEFT JOIN FOO.Clients ON PlanBudget.Client_No=Clients.Client_No LEFT JOIN FOO.UserList ON Clients.ULno=UserList.ULno LEFT JOIN FOO.Users ON UserList.UserNo=Users.UserNo LEFT JOIN FOO.PGrps ON if( Clients.ProjectGroupsOpt="Standard",PlanBudget.PGrp_No=PGrps.PGrp_No And PGrps.Client_No=0 And PGrps.Client_Type=Clients.Client_Type, PlanBudget.PGrp_No=PGrps.PGrp_No And PlanBudget.Client_No=PGrps.Client_No ) LEFT JOIN FOO.YrOpts ON if( Clients.YrOptOpt="Standard",PlanBudget.Year_Do_no=YrOpts.Yr_No And YrOpts.Client_No=0 And YrOpts.Client_Type=Clients.Client_Type, PlanBudget.Year_Do_no=YrOpts.Yr_No And PlanBudget.Client_No=YrOpts.Client_No ) LEFT JOIN FOO.Priorities ON if( Clients.PrioritiesOpt="Standard",PlanBudget.Priority_No=Priorities.Priority_No And Priorities.Client_No=0 And Priorities.Client_Type=Clients.Client_Type, PlanBudget.Priority_No=Priorities.Priority_No And PlanBudget.Client_No=Priorities.Client_No ) LEFT JOIN FOO.Fund_Srcs ON if( Clients.FundSourcesOpt="Standard",PlanBudget.FundSrc_no=Fund_Srcs.Fund_Src_no And Fund_Srcs.Client_No=0 And Fund_Srcs.Client_Type=Clients.Client_Type, PlanBudget.FundSrc_no=Fund_Srcs.Fund_Src_no And PlanBudget.Client_No=Fund_Srcs.Client_No ) LEFT JOIN FOO.Rtypes ON if( Clients.RtypesOpt="Standard",PlanBudget.RMType_no=Rtypes.Rmtype_no And Rtypes.Client_No=0 And Rtypes.Client_Type=Clients.Client_Type, PlanBudget.RMType_no=Rtypes.Rmtype_no And PlanBudget.Client_No=Rtypes.Client_No ) LEFT JOIN FOO.Users Fac_Admin_No ON PlanBudget.Fac_Admin_No=Fac_Admin_No.UserNo LEFT JOIN FOO.Users Fac_Manager_No ON PlanBudget.Fac_Manager_No=Fac_Admin_No.UserNo WHERE PlanBudget.constant=1 and PlanBudget.uniqueid = "RBB43M2VZIATIQ45VGKR-0A021b38e22F87KmO19132B9" The Explain of the select says: table type possible_keys key key_len ref rows Extra PlanBudget ref UniqueID UniqueID 50 const 1 Using where Clients ref Client_No,Client_No_2 Client_No 2 FOO.PlanBudget.Client_No 1 UserList ref ULno,ULno_2 ULno 2 FOO.Clients.ULno 1 Users ref UserNo UserNo 2 FOO.UserList.UserNo 1 Using index PGrps indexClient_No_3 259 116 Using index YrOpts indexClient_No_3 259 81 Using index Priorities indexClient_No_3 25942 Using index Fund_Srcs indexClient_No_3 25944 Using index Rtypes indexClient_Type 259 100 Using index Fac_Admin_No ref UserNo UserNo 2 FOO.PlanBudget.Fac_Admin_No 1 Using index Fac_Manager_No indexUserNo 2 111 Using index What, if anything, can I do? As I mentioned, it worked fine in Mysql 3.x and 4.0. It's not functioning in Mysql 4.1 Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com);
Re: Xserve G5
> Has anyone had any experiences running MySQL on a Xserve G5 with Macintosh OSX? We are thinking of purchasing some new hardware to run our MySQL server. The > 64-bit architecture is something we would like to take advantage of. I've had great success running it on my 4-year old Mac laptop (using the latest OS), and the Xserve is quite a machine (see #3 at http://www.top500.org/list/2003/11/). I think you'll have to compile MySQL on the Xserve to take advantage of the 64-bit architecture. The Xserve G5 with Panther (10.3) comes with MySql 4 on it already. Thats not saying its optimized to take advantage of the 64 bit architecture though. Infact, Mac's own OS (10.3) isn't optimized to take advantage of it yet. We love our G4 and G5 serves running panther. -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO; /Lasso Partner Association Member ID #LPA135259; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL - Join How?
Roger,
Not sure if I understood your problem, but have you tried combining your ON
clause with a WHERE clause with an OR condition:
Unfortunately, that wouldn't do it. the "client type" would be
changing based on the client. I see I goofed up however. Below is
the corrected version. The Join has to be based on whether the
particular records Priority Option is standard or custom. If it's
standard, it needs to match the reference_no, client_no=0 (which is
standard) and the records client type must match the priority client
type.
If it's custom, then the reference_no and client_no have to match.
SELECT WO.client_no, WO.wo_number, WO.priority_no,
Client.client_name, Client.client_no, Client. Client_type,
Priority.Priority_vw
FROM FMS.WorkOrder
LEFT JOIN FMS.Client ON WO.Client_No=Client.Client_No LEFT JOIN FMS.Priority
ON
IF ( Client.PriorityOption=("standard"),
WO.priority_ref_no=Priority.priority_ref_no And
Priority.Client_No=0 And Priority.client_type=Client.client_type.,
WO.priority_ref_no=Priority.priority_ref_no And
WO.Client_No=Priority.Client_No
)
SELECT ...
FROM FMS.WorkOrder WO
LEFT JOIN FMS.Client ON
WO.Client_No=Client.Client_No
LEFT JOIN FMS.Priority ON
WO.priority_ref_no=Priority.priority_ref_no
WHERE
(Client.Client_type="School District" AND
Priority.Client_No=0 AND
Priority.client_type="School District")
OR
(WO.Client_No=Priority.Client_No)
--
Roger
--
/Brett C. Harvey;
/Creative-Pages.Net, President;
/Facility Management Systems, Technology Director;
/Lasso Partner Association Member ID #LPA135259;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL - Join How?
Is it possible to basically do a conditional within a join (or perhaps this is where a subquery is needed)? I have a workorder table that I'm doing a join with. Each Client may be a different client type and thus needs to be joined from one of two different tables. Also, each client may choose to use a set of standard "priorities" we have for each client type or define their own. If I was doing a SELECT that was for just one client, this wouldn't be a big deal because the client type and which option (standard or custom) would be know. However, we want to be able to show all workorders from all clients at once. The Table Structure is basically this Client table: Client_no: Int Client_name:VarChar Client_type:VarChar PriorityOption: (Custom or Standard) WO table: Client_no Int wo_number Int priority_ref_no tinyInt Priority Table: Client_no Int priority_ref_no tinyInt priority_vw VarChar Client_type:VarChar SELECT WO.client_no, WO.wo_number, WO.priority_no, Client.client_name, Client.client_no, Client. Client_type, Priority.Priority_vw FROM FMS.WorkOrder LEFT JOIN FMS.Client ON WO.Client_No=Client.Client_No LEFT JOIN FMS.Priority ON IF ( Client.Client_type="School District", WO.priority_ref_no=Priority.priority_ref_no And Priority.Client_No=0 And Priority.client_type="School District", WO.priority_ref_no=Priority.priority_ref_no And WO.Client_No=Priority.Client_No ) MySQL version 3.23.54 -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, Technology Director; /Lasso Partner Association Member ID #LPA135259;
