mysql 4.1.14 memory leak.
I'm using mysql 4.1.14 rpm's on Fedora Core 4. I've setup max_heap_table_size to allow for 500M heap tables. Currently I use roughly 435M in heap by about 16 different tables. Some use btree, other's use default hash. The problem is, over time mysql looks like this in top: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2898 mysql 15 0 1127m 626m 2076 S 18.3 62.5 2974:17 mysqld-max mysql is forcing the machine to swap. I've tried dropping all heap tables that are in use and this does not free the ram. I have to restart mysql and and using init-file reload the heap tables and everything is fine for a couple of days. My problem is strikingly similar to this: http://groups.google.com/group/mailing.database.myodbc/browse_thread/thread/485647dae02b59a1/32f0009e9cb135b7?lnk=stq=virtual+heap+mysqlrnum=1utoken=rT79JjoAAABobq0US6-f3p1tupn-bp7-GyqMAsXdt4_lvPhOluyGzfrEz8xuJ8FzZhQCB5gw1_s38laLLlcPg_ShAKo-q_vP I'm using the mysql rpm's from mysql.com http://mysql.com. Other than restarting mysql nightly, is there anything I could do to further debug this? Thanks, Mysql Rocks.
Re: insert into... select... duplicate key
Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert into... select... duplicate key
That is the effect that I am looking for, but exactly the method that I am trying to avoid. If I type the column names into my INSERT... SELECT and someone later adds a column to the table, I would have to go back into my program and update the statement. I am looking for a way to do it dynamically in order to avoid maintenance of the statement in my program later. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into... select... duplicate key
Well I haven't realy found a select method in which you can isolate a field. Like a complementary method, in which you select like one field, but shows the fields except the field which you have used in your select-statement. So you excually want to dynamically insert the records, not knowing how many fields you excually have; excluding the auto-numbering field. Wouldn't it be better to use PHP or another API in which you retrieve the fields and create an SQL statement using these variables and having the knowledge of creating the sql-statement? You could also try to automate an export and use the load datafile to import the information; but then again you have to rewrite the datafile. Best regards, Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: That is the effect that I am looking for, but exactly the method that I am trying to avoid. If I type the column names into my INSERT... SELECT and someone later adds a column to the table, I would have to go back into my program and update the statement. I am looking for a way to do it dynamically in order to avoid maintenance of the statement in my program later. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert into... select... duplicate key
I am using Cold Fusion and as I stated in my original message, if I were using MySQL 5, then I could use information_schema to retrieve the column names in the table and do it with variables in Cold Fusion. I do that on all my pages on the MySQL 5 servers with which I work. However, the server I am working with currently is MySQL 4 and I am unaware of any way to retrieve the column names from a table in MySQL 4. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Well I haven't realy found a select method in which you can isolate a field. Like a complementary method, in which you select like one field, but shows the fields except the field which you have used in your select-statement. So you excually want to dynamically insert the records, not knowing how many fields you excually have; excluding the auto-numbering field. Wouldn't it be better to use PHP or another API in which you retrieve the fields and create an SQL statement using these variables and having the knowledge of creating the sql-statement? You could also try to automate an export and use the load datafile to import the information; but then again you have to rewrite the datafile. Best regards, Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: That is the effect that I am looking for, but exactly the method that I am trying to avoid. If I type the column names into my INSERT... SELECT and someone later adds a column to the table, I would have to go back into my program and update the statement. I am looking for a way to do it dynamically in order to avoid maintenance of the statement in my program later. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Web-based reporting tool?
How about BIRT? http://www.eclipse.org/birt/ I have tried it with MySQL, and it works OK. Still investigating. Andy -Original Message- From: Wiebe de Jong [mailto:[EMAIL PROTECTED] Sent: Fri 23/09/2005 02:25 To: mysql@lists.mysql.com Cc: Subject:RE: Web-based reporting tool? If you're considering Java, how about looking at Jasper? http://jasperreports.sourceforge.net/ Wiebe -Original Message- From: Warrick Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 9:09 AM To: mysql@lists.mysql.com Subject: Web-based reporting tool? This seems to be a common question, but answers aren't that common... What are people using as a web-based reporting tool? I'd like to add a user-facing interface to allow users to run pre-defined reports (in which they may need to enter data, like Start Date and End Date) against their data. I've done a bunch of Google searching, and there's always Crystal Reports. That is cost-prohibitive currently, though I was discussing this with them. However, there are issues where we may want to split our current database server into a larger number of servers, and then the license issues crop up again. What else is good and reliable? I'm looking at QLR Manager, looked at Agata, downloaded a number of other programs to find out they are intended for running on the desktop (as opposed to being a web-based app). I'm not overly concerned with language, either, although the database server is currently a Windows box. I'm using PHP for some stuff, but could run Java, etc. if needed for the right software. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into... select... duplicate key
I am not familiour with Cold Fusion but: cant you use 'show columns from table' ?? and use the result object? This normally works in e.g. C or PHP danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: I am using Cold Fusion and as I stated in my original message, if I were using MySQL 5, then I could use information_schema to retrieve the column names in the table and do it with variables in Cold Fusion. I do that on all my pages on the MySQL 5 servers with which I work. However, the server I am working with currently is MySQL 4 and I am unaware of any way to retrieve the column names from a table in MySQL 4. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Well I haven't realy found a select method in which you can isolate a field. Like a complementary method, in which you select like one field, but shows the fields except the field which you have used in your select-statement. So you excually want to dynamically insert the records, not knowing how many fields you excually have; excluding the auto-numbering field. Wouldn't it be better to use PHP or another API in which you retrieve the fields and create an SQL statement using these variables and having the knowledge of creating the sql-statement? You could also try to automate an export and use the load datafile to import the information; but then again you have to rewrite the datafile. Best regards, Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: That is the effect that I am looking for, but exactly the method that I am trying to avoid. If I type the column names into my INSERT... SELECT and someone later adds a column to the table, I would have to go back into my program and update the statement. I am looking for a way to do it dynamically in order to avoid maintenance of the statement in my program later. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myhostname-bin.000nn @ /usr/local/mysql/var
Hi Miguel, Those files are the binary logs that MySQL keeps of its doings. You can find more information here: http://dev.mysql.com/doc/mysql/en/binary-log.html. Depending on the number of tables and the number of data-altering statements, these logs may become very big very quickly. Kind regards, -- /Martijn On Sunday 25 September 2005 02:56, Miguel Cardenas wrote: Hello list I wanted to know how much space was using the database am working on, but found some big files I don't know what they do... these files are located in /usr/local/mysql/var and are named 'myhostname-bin.000nn' (nn is 1 to 29 at this moment)... I don't know that are those files, so I did a 'less' on them and it appears to be binary data with mixed full insert commands for part of my existing database. My question is... why are those files there? are they really needed or can be removed? they are really big and contain old insert commands that will not be used never more... can I simple delete all of them? Thanks for any information!!! -- /Martijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird database files
Had problem with our database this weekend, apparently an app did an insert query that was huge size wise and this totally boogered up replication downstream. Also I cant read past that point in the binlog using mysqlbinlog on the master server. It complains that: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1953458240, event_type: 119 ERROR: Could not read entry at offset 66113944 : Error in log format or read error And then there are the weird table files that showed up in the data directory for the database (all MyISAM): -rw-rw1 mysqlmysql 14K Sep 12 11:50 #sql-7c1c_217c.frm -rw-rw1 mysqlmysql1.8G Sep 12 11:54 #sql-7c1c_217c.MYD -rw-rw1 mysqlmysql 92M Sep 12 12:09 #sql-7c1c_217c.MYI Anyone ever see something like this before? Are they files for a temp table maybe? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating HUGE reports
I think there are some required indexes missing in your table structure. If you showed us your real SHOW CREATE TABLE CONTACTS \G and SHOW CREATE TABLE LISTS \G as well as text of SELECTs that kill your mysqld, you'd get better replies. 250k/60k records is not that huge tables really. On 9/25/05, Miguel Cardenas [EMAIL PROTECTED] wrote: Hello... After solving some problems with the code, now am working with the real one database. On my tests was playing with 20,000 records and worked pretty fine sorting by X or Y field and setting some 'where ...' conditions. At this moment the database has 250,000+ records, new insertions are pretty fast, searchs are pretty fast too, but generating reports is almost impossible, the computer may hang one hour and still be working with any output. My tables are something like this: CONTACTS - about 250,000 records -- | id (INT) | contact(CHAR) | active(BOOL) | ... | -- LISTS - about 60,000 records (category for some contacts) | id (INT) | category(CHAR) | Contacts may have a category, although not all of them have one, and some of them may be in more than one, so it is not option to use a 'category' field inside the CONTACTS table... perhaps, intuitively could have a 'SET' type field with categories, but there are not fixed categories and may (will) grow with usage... There are two conditions that should be both acomplished: Cond 1. CONTACTS.active = 1 Select all active contacts Cond 2. LISTS.category = (or ) mycagegory Add/remove contacts from the report that are listed in mycategory If I try a select on both tables by conditioning CONTACTS.id=LISTS.ID and LISTS.category=something the whole system becomes slow and unusable, and still do not wait enough to see how long it will take. This task will be performed once or twice a week, so it must be optimized, and don't know what should be better... 1st option: let the complex select finish (have no idea of how long will take) 2nd option: generate a temporal table with results matching Cond1, and then apply Cond2 to the temporal table. Commonly, the 'category' applies to discard contacts, more than choosing them, so I would simply remove LIST.id records from TEMPORAL table where LIST.id=TEMPORAL.id and then would have the final report. But... what should be better? a single select with complex conditions or a temporal table with multiple parses to append/discard records for every category used (one or various)? Thanks for any comment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedures MS SQL Server to MySQL
Taking on online class for SQL and am down to the last two classes and cannot make the following work. This is a MS SQL Server query that I have not been able to solve through the MySQL Documentation: CREATE PROCEDURE CountPhoneNumbers AS DECLARE @count INTEGER SELECT @count = COUNT (*) FROM Customer WHERE HomePhone IS NOT NULL Print @count I have tried numerous variations of the following: CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT @count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// // was set to be the delimiter for the creation and have tried putting various parts of the query into the parentheses. The error messages always seem to occur around the @count variable. I am using MySQL v.5.0.12 on Debian Etch. TIA, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures MS SQL Server to MySQL
Jim, I have tried numerous variations of the following: CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT @count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// // was set to be the delimiter for the creation and have tried putting various parts of the query into the parentheses. The error messages always seem to occur around the @count variable. I am using MySQL v.5.0.12 on Debian Etch. The stored procedure docs (http://dev.mysql.com/doc/mysql/en/create-procedure.html) might not forbid prefixing declared variable names prefixed with @, but all the examples on that page show vars without such a prefix. The @ prefix in MySQL is for client user vars. The simplest way to retreive a count frm an SP is just to run the query in the SP. MySQL will return the value, for example: SET GLOBAL log_bin_trust_routine_creators = TRUE; DROP PROCEDURE IF EXISTS CountPhoneNumbers; DELIMITER | CREATE PROCEDURE CountPhoneNumbers () BEGIN SELECT COUNT(*) FROM customer WHERE Phone IS NOT NULL; END; | DELIMITER ; CALL CountPhoneNumbers(); +--+ | COUNT(*) | +--+ | 91 | +--+ (Set log_bin_trust_routine_creators to bypass MySQL's declaration rules regarding deterministicness.) If you really want to return the result in a variable, declare a user var in the client, declare an OUT var in the SP, have the SP SELECT INTO it, and pass the user var to the SP in the call: SET @x=0; SET GLOBAL log_bin_trust_routine_creators = TRUE; DROP PROCEDURE IF EXISTS CountPhoneNumbers; DELIMITER | CREATE PROCEDURE CountPhoneNumbers ( OUT count INT ) BEGIN SELECT COUNT(*) INTO count FROM customer WHERE Phone IS NOT NULL; END; | DELIMITER ; CALL CountPhoneNumbers(@x); SELECT @x; PB http://www.artfulsoftware.com - Jim Seymour wrote: Taking on online class for SQL and am down to the last two classes and cannot make the following work. This is a MS SQL Server query that I have not been able to solve through the MySQL Documentation: CREATE PROCEDURE CountPhoneNumbers AS DECLARE @count INTEGER SELECT @count = COUNT (*) FROM Customer WHERE HomePhone IS NOT NULL Print @count I have tried numerous variations of the following: CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT @count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// // was set to be the delimiter for the creation and have tried putting various parts of the query into the parentheses. The error messages always seem to occur around the @count variable. I am using MySQL v.5.0.12 on Debian Etch. TIA, Jim Seymour -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Documenting and visualizing a database
This is probably a FAQ, but I haven't been able to find the answer. Briefly, I am looking for tools that will help me document a database. Visualization would be nice too, so that I could quickly see the relations between tables. Less briefly. I am new to MySQL, SQL in general and databases. I have inherited a project that uses, Apache, PHP, and MySQL. The MySQL Press book, MySQL Tutorial has been extremely helpful. My predecessor, who did know something about MySQL, left no documentation. As I am trying to figure out what is what and what the relationships are between tables, I've got paper and pencil pictures, and a text file full of notes. But, mostly it is just an understanding -- for the parts that I understand -- in my head. Now I can always redraw my paper and pencil stuff with something like xfig, but it struck me that there is probably a standard, well conventional, notation for this kind of thing and tools for the purpose. I'm a bit of a geezer and my memory isn't as reliable as it used to be. Plus, I don't want any successor on this project to curse me quiet as vehemently as I curse my predecessor. So advice or pointers to advice would be very welcome. As for tools, I'd be looking for things that can run on Unix systems (GNU/Linux and/ or OS X). If I've hit the wrong list, I sure someone will tell me where to go. Cheers, -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
The long story short is we use the fact that MySQL has the ability to run the SQL thread and the IO thread of replication separately, and control them individually. I'm fairly green with replication, but I have a simple cron job that starts a PHP program that issues a slave start, watches for the time behind master to be zero seconds, then issues a slave stop. This repeats every 10 minutes (it takes about one minute to update 10 minutes of master data), so my slave is at most (worst case) 10 minutes behind the master. This could be done every two hours or even once per day. I'll be setting up a second master to do this same thing once per day to act as my daily backup. Once the daily backup completes replication, I can flush tables and backup the database tables to the backup device for long term backups. What are the differences between doing this and turning the SQL and IO threads on spearetly? Just IMO, that seems like alot of manipulation that's not really necessary, but it's possible I'm missing something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
I'll be setting up a second master to do this same thing once per day to act as my daily backup. Oops...I meant to say second slave. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
I would start by writing down what you believe the database consists of: 1. The table structures -- write them down, commit them to paper. 2. The relationships you believe exist between the tables. Document them in writing and visually. Use whatever tool works for now -- don't make the mistake of allowing the tools to stand in the way of proper documentation. Use Visio (I know it isn't Linux or open source, but most people can master it fast.) Or just use pen, paper, ruler, and a nice old fashioned programmer's symbol template -- the kind that is made of plastic. (I actually used them for many years.) The point is, commit your knowledge to paper. Now look at the code components. 1. Print and organize all the code that exists. 2. Study the code; determine how each component relates to the others. Diagram this program flow as above for the tables. Don't let lack of software stop you. Pen and paper is better than exactly nothing. You may have to experiment with the code to determine the processing flow. Almost certainly you will, in fact. As to learning MySQL and PHP, there is really only one good technical writer for MySQL: Paul DuBois. His book MySQL 3rd edition is a must-read. But even Paul is not a magician; you can't learn MySQL from a book alone. You need Paul's book, and the willingness to practice working with MySQL. Make yourself a user account, give yourself privileges on a play database, open his book, and go for it. Of the various PHP writers, I really have great respect for Tim Converse and Joyce Parks. I suggest getting their excellent book on PHP 5. It's a shame they don't cover PHP 5.1, but get the book anyhow and play with it. As with MySQL, don't just read the book: start writing your own PHP code and playing with it. Bob Cochran Jeffrey Goldberg wrote: This is probably a FAQ, but I haven't been able to find the answer. Briefly, I am looking for tools that will help me document a database. Visualization would be nice too, so that I could quickly see the relations between tables. Less briefly. I am new to MySQL, SQL in general and databases. I have inherited a project that uses, Apache, PHP, and MySQL. The MySQL Press book, MySQL Tutorial has been extremely helpful. My predecessor, who did know something about MySQL, left no documentation. As I am trying to figure out what is what and what the relationships are between tables, I've got paper and pencil pictures, and a text file full of notes. But, mostly it is just an understanding -- for the parts that I understand -- in my head. Now I can always redraw my paper and pencil stuff with something like xfig, but it struck me that there is probably a standard, well conventional, notation for this kind of thing and tools for the purpose. I'm a bit of a geezer and my memory isn't as reliable as it used to be. Plus, I don't want any successor on this project to curse me quiet as vehemently as I curse my predecessor. So advice or pointers to advice would be very welcome. As for tools, I'd be looking for things that can run on Unix systems (GNU/Linux and/ or OS X). If I've hit the wrong list, I sure someone will tell me where to go. Cheers, -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL upgrade from 3.x to 4.1 for Chinese databases
No one can provide a perfect solution. It seems that this is really a difficult problem for the MySQL. YL wrote: I tried from 4.1 to 5.01, everything works fine without change the data files. I used Chinese in both gb and b5 (different columns in the same table) without any encoding setting at database level. So the problem seems in 3.* to 4.*. If mysqldump worded for you. My suggestion is to write a script to automat this data conversion. - Original Message - From: Chenzhou Cui [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: YL [EMAIL PROTECTED] Sent: Friday, September 23, 2005 6:54 AM Subject: Re: MySQL upgrade from 3.x to 4.1 for Chinese databases YL wrote: Did you tried just copy those 3.23 files into a newly installed 4.1 data folder? What's happen? Yes. I tried. The result is all Chinese words were turned into unrecognized characters. In my system, there are tens of databases, which include thousands of tables. A very large system, I feel. - Original Message - From: Chenzhou Cui [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 23, 2005 12:40 AM Subject: MySQL upgrade from 3.x to 4.1 for Chinese databases Dear all, On my server there are thousands of database files contenting Chinese records. The total data volumn is over 20GB. Is there an easy way to upgrade the whole system from MySQL 3.23.x to 4.1.x? mysqldump is NOT a good way for me. It will be a hard and time costing work. Is it possible to directly use the binary db files, *.frm, *.MYI, *.MYD, and keep the Chinese support? This is a very big and important problem facing many Chinese users. cheers, Chenzhou -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005 -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005 -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
On Sep 25, 2005, at 5:44 PM, Robert L Cochran wrote: I would start by writing down what you believe the database consists of: 1. The table structures -- write them down, commit them to paper. Thanks, I've already printed out all of table structure information. 2. The relationships you believe exist between the tables. Document them in writing and visually. That is what I have started to do. Because the stuff that I was writing down seemed, well, fairly structured, I'd assumed that there were some useful conventions for recording these. Use whatever tool works for now -- don't make the mistake of allowing the tools to stand in the way of proper documentation. Of course. But I was hoping that existing tools might remind me to note down things that I might not have occurred to me to note down. Now look at the code components. 1. Print and organize all the code that exists. 2. Study the code; determine how each component relates to the others. Diagram this program flow as above for the tables. Don't let lack of software stop you. Pen and paper is better than exactly nothing. I wasn't looking for software for this part, though something like ctags for PHP would be nice. After printing everything out, the next thing I did was put things under revision control. As to learning MySQL and PHP, there is really only one good technical writer for MySQL: Paul DuBois. His book MySQL 3rd edition is a must-read. Thanks. But even Paul is not a magician; you can't learn MySQL from a book alone. You need Paul's book, and the willingness to practice working with MySQL. Of course. The Tutorial from MySQL AB requires that. And I've successfully added some new required things to the project. Of the various PHP writers, I really have great respect for Tim Converse and Joyce Parks. Again, thanks for the recommendation. But I'm still left puzzled. If people haven't developed tailored tools to document a database, then I find more than a bit of irony in the fact that people who specialize in organizing data in useful ways would not have developed a way to organize data that they need to make use of on a daily basis. Cheers, -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
Jeffrey, But I'm still left puzzled. If people haven't developed tailored tools to document a database, then I find more than a bit of irony in the fact that people who specialize in organizing data in useful ways would not have developed a way to organize data that they need to make use of on a daily basis. There are quite a few db design tools that can write data models from MySQL databases, but for various reasons, more run on Windows than on *nix. One of our favourites is Dezign from Datanamic; inexpensive and good. If you have access to a Windows box, it might be worth your while to do the reverse engineering there, using one of those tools. One tool that can produce a UML model from a MySQL db under *nix is DB Visual Architect, but it's pricey. MySQL AB recently purchased such a tool, DB Designer, rechristened it MySQL Workbench, just released an alpha version for Windows. PB http://www.artfulsoftware.com - Jeffrey Goldberg wrote: On Sep 25, 2005, at 5:44 PM, Robert L Cochran wrote: I would start by writing down what you believe the database consists of: 1. The table structures -- write them down, commit them to paper. Thanks, I've already printed out all of table structure information. 2. The relationships you believe exist between the tables. Document them in writing and visually. That is what I have started to do. Because the stuff that I was writing down seemed, well, fairly structured, I'd assumed that there were some useful conventions for recording these. Use whatever tool works for now -- don't make the mistake of allowing the tools to stand in the way of proper documentation. Of course. But I was hoping that existing tools might remind me to note down things that I might not have occurred to me to note down. Now look at the code components. 1. Print and organize all the code that exists. 2. Study the code; determine how each component relates to the others. Diagram this program flow as above for the tables. Don't let lack of software stop you. Pen and paper is better than exactly nothing. I wasn't looking for software for this part, though something like ctags for PHP would be nice. After printing everything out, the next thing I did was put things under revision control. As to learning MySQL and PHP, there is really only one good technical writer for MySQL: Paul DuBois. His book MySQL 3rd edition is a must-read. Thanks. But even Paul is not a magician; you can't learn MySQL from a book alone. You need Paul's book, and the willingness to practice working with MySQL. Of course. The Tutorial from MySQL AB requires that. And I've successfully added some new required things to the project. Of the various PHP writers, I really have great respect for Tim Converse and Joyce Parks. Again, thanks for the recommendation. But I'm still left puzzled. If people haven't developed tailored tools to document a database, then I find more than a bit of irony in the fact that people who specialize in organizing data in useful ways would not have developed a way to organize data that they need to make use of on a daily basis. Cheers, -j -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into... select... duplicate key
Relevant bits of the conversation so far, with my thoughts at the end:
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for the
full record. I know that I can use the information_schema to do this in
MySQL 5, but the server I am currently work with is MySQL 4. Basically, I
am looking for a way to select all of the columns in a record except one,
so that the auto-incrementing primary key will automatically insert
itself. Of course, if anyone has any other suggestions for a work around,
that would be good, too.
Danny Stolle wrote:
You have to use the fields in your into -statement and select statement,
not including the field having the auto-numbering so if e.g. field1 has
autonumbering -
insert into table1 (field2, field3) select (field2, field3) from table1;
autonumbering will automatically be applied :-)
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
That is the effect that I am looking for, but exactly the method that I
am trying to avoid. If I type the column names into my INSERT... SELECT
and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do it
dynamically in order to avoid maintenance of the statement in my program
later.
Danny Stolle wrote:
So you actually want to dynamically insert the records, not knowing how
many fields you actually have; excluding the auto-numbering field.
Wouldn't it be better to use PHP or another API in which you retrieve the
fields and create an SQL statement using these variables and having the
knowledge of creating the sql-statement?
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
I am using Cold Fusion ... However, the server I am working with
currently is MySQL 4 and I am unaware of any way to retrieve the column
names from a table in MySQL 4.
Danny Stolle wrote:
I am not familiar with Cold Fusion but: cant you use 'show columns from
table' ?? and use the result object?
This normally works in e.g. C or PHP
That should work, but seems a lot of effort. Another option would be to use
a temporary table to store the row(s) to be copied. Assuming the
auto_increment column is named id, it would look something like this:
# select the row(s) to be copied into a temp table
CREATE TEMPORARY TABLE dupe SELECT * FROM yourtable WHERE {conditions};
# change the id column to allow NULLs
ALTER TABLE dupe CHANGE id id INT;
# change the id(s) to NULL
UPDATE dupe SET id=NULL;
# copy the rows back to the original table
INSERT INTO yourtable SELECT * FROM dupe;
# clean up
DROP TABLE dupe;
This works because inserting a row with a NULL in the auto_increment id
column works the same as leaving the column out.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert into... select... duplicate key
Actually, both of your solutions worked. Thanks much for the input guys.
Rob
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, September 26, 2005 12:20 AM
To: Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master)
Cc: Danny Stolle; mysql@lists.mysql.com
Subject: Re: insert into... select... duplicate key
Relevant bits of the conversation so far, with my thoughts at the end:
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for
the
full record. I know that I can use the information_schema to do this
in
MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I
am looking for a way to select all of the columns in a record except
one,
so that the auto-incrementing primary key will automatically insert
itself. Of course, if anyone has any other suggestions for a work
around,
that would be good, too.
Danny Stolle wrote:
You have to use the fields in your into -statement and select
statement,
not including the field having the auto-numbering so if e.g. field1
has
autonumbering -
insert into table1 (field2, field3) select (field2, field3) from
table1;
autonumbering will automatically be applied :-)
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
That is the effect that I am looking for, but exactly the method that
I
am trying to avoid. If I type the column names into my INSERT...
SELECT
and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do
it
dynamically in order to avoid maintenance of the statement in my
program
later.
Danny Stolle wrote:
So you actually want to dynamically insert the records, not knowing
how
many fields you actually have; excluding the auto-numbering field.
Wouldn't it be better to use PHP or another API in which you retrieve
the
fields and create an SQL statement using these variables and having
the
knowledge of creating the sql-statement?
Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
I am using Cold Fusion ... However, the server I am working with
currently is MySQL 4 and I am unaware of any way to retrieve the
column
names from a table in MySQL 4.
Danny Stolle wrote:
I am not familiar with Cold Fusion but: cant you use 'show columns
from
table' ?? and use the result object?
This normally works in e.g. C or PHP
That should work, but seems a lot of effort. Another option would be to
use
a temporary table to store the row(s) to be copied. Assuming the
auto_increment column is named id, it would look something like this:
# select the row(s) to be copied into a temp table
CREATE TEMPORARY TABLE dupe SELECT * FROM yourtable WHERE
{conditions};
# change the id column to allow NULLs
ALTER TABLE dupe CHANGE id id INT;
# change the id(s) to NULL
UPDATE dupe SET id=NULL;
# copy the rows back to the original table
INSERT INTO yourtable SELECT * FROM dupe;
# clean up
DROP TABLE dupe;
This works because inserting a row with a NULL in the auto_increment id
column works the same as leaving the column out.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
Peter Brawley wrote: MySQL AB recently purchased such a tool, DB Designer, rechristened it MySQL Workbench, just released an alpha version for Windows. You're kidding? I thought I'd seen the last of DB Designer. Where can we get it? I checked out the dev section of the website and looked under graphical clients, but it's nowhere to be found. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ADDING ALLOWED HOSTS
Hello I've just read the users management, it is pretty clear but have a technical doubt... I have a server at the office and want to work on it from home sometimes. It has not a fixed ip, is ADSL and address changes from time to time. Am able to access the server via ssh by using a domain over dynamic address (it gets updated constantly so there's no problem to reach the server). My doubt is... The providers domain is always dsl-IPADDRESS.provider.com and have a domain over dynamic IP configured as MYDOMAIN.subdomain.com which points to the current IPADDRESS. Would it be possible to add an entry to the 'Host' table using MYDOMAIN.subdomain.com ??? I was thinking that it depends on how the validation process works... if the domain is converted to IP and then compared with the incoming IP then it should work, but, if the incoming IP is converted to domain name and then compared to the database will not work since that returns the name of the provider and not MYDOMAIN... Server does not work on weekend so I can't test right now, but I wanted to ask, anybody knows how is the validation process of the IP? 1. does MySQL convert domain name in database into IP and then compares with the incoming connection IP? or 2. the incoming connection IP is converted to domain and then compared to the domain names and IPs in the hosts table? It would be great to work from home, but don't want to set the permission to the provider's subnet domain name, but to the customized domain set to the dynamic ip (MYDOMAIN) instead. Any suggestion? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
