MySQL 5.0.17 on FreeBSD 4.7 - zlib error
Just throwing this out there for the archives. Installing 5.0.17 MySQL Client on a FreeBSD VPS2 Virtual server from Verio kept throwing an error looking for zlib.la in the zlib library. This system didn't have a system wide zlib installed, so the bundled library kept trying to be used instead. Finally got it working by installing the zlib library from source (zlib.net) and then doing the port install. Hope this helps anyone else who hits this problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee <[EMAIL PROTECTED]> wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can foreign keys reference tables in another database?
I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database design - master table of countries and state/provinces
I'm trying to think of the ways I can accomplish having a master database of countries and states/provinces that I can reference in several other databases. I'd like to have a table of countries available for selection by the users in a web app, but I'm not sure of the best way to then allow them to pick the state/province in which they reside in that country. I've thought of a foreign key in the state table that references the parent country and do a state/province lookup off of that (SELECT state FROM tblStates JOIN ON tblState.countryID=WhatYouChoseAlready, or something similar - my syntax may not be correct). Is there another way that has been used by anyone with good success? Any suggestions welcome. And would ISO.org be the best place to find such country and province lists, or perhaps the Postal Service? Any past experience? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup database with MyISAM and InnoDB tables together
> > -Original Message- > From: Scott Plumlee [mailto:[EMAIL PROTECTED] > Sent: Friday, June 17, 2005 10:21 AM > To: mysql@lists.mysql.com > Subject: Backup database with MyISAM and InnoDB tables together > > I'm not clear on best practice to use on a database containing both > MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use > > mysqldump --opt, thus getting the --lock-tables option, but for the > InnoDB the --single-transaction is preferred. Since they are mutually > exclusive, is there a best practice to get consistent state of the > tables when the database dump is performed? > > Would `mysqldump --opt --skip-lock-tables --single-transaction` be best > for a database that is mostly InnoDB tables, but does have a few MyISAM > tables? > > WOuld I be better off locking the database from any updates/inserts, and > > specifying particular commands for individual tables? > > Any advice appreciated, including RTFMs with links. > > > Gordon Bruce wrote: If you are runing binary log and do a FLUSH LOGS mysqldump --opt --skip-lock-tables MyISAM table names FLUSH LOGS mysqldump --opt --single-transaction INNODB table names You have a recoverable state with the combination of the mysqldump file and the binary log file that was started by the 1st FLUSH LOGS command. The recovered database wil be restored to the date time that the 2nd FLUSH LOGS command was issued instead of the start time of the backup, but you won't have to lock all of your tables and it wil give you a consistent state across a mixed INNODB MyISAM environment. The downside is -you have 3 files to deal with -you have to maintain the table names in the mysqldump commands -you have a small risk of a change ocurring in the MyISAM tables between time the 2nd FLUSH LOGS is executed and the 2nd mysqldump command is executed Thanks for the tip. I haven't looked into binary logs too much, just learned about them the other day when I had to correct my own mistake and restore a table. Is is best practice to go with tables of all one sort to allow for consistent state when doing backups like this, or are mixed tables the norm in most databases? I went with the InnoDB in order to not have to do row level locking on the tables as transactions were performed and I've been very pleased with the results. I might consider just going with all InnoDB to make it easy, as those are the majority of my tables in this case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup database with MyISAM and InnoDB tables together
I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Defaul of NOW()
I didn't think you could have a DEFAULT of NOW() because it's not a true static value. Seems I read that in one of Paul DuBois' books. Jim Winstead wrote: On Wed, May 26, 2004 at 06:20:22PM -0700, Scott Haneda wrote: I have a field in mysql 4, using InnoDB Field is timestamp 14 and defualt is set to 00, which I want to be the result of NOW() so that every record made will get NOW() as the value, I can not get it to work... ALTER TABLE `addresses` CHANGE `added` `added` TIMESTAMP( 14 ) DEFAULT 'NOW()'; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 It tells me it worked, but then it reverts back to the zero's. If you do a SHOW CREATE TABLE on the table, you'll see that it has actually ignored your DEFAULT. (What is has done is actually transformed it to '00', since that is what the string 'NOW()' becomes when you convert it to a TIMESTAMP.) Read this section in the manual for information on how the default value for TIMESTAMP columns is handled: http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html Support for specifying how TIMESTAMP columns get updated is coming in 4.1.2. Right now, it is only documented in the change notes: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html Jim Winstead MySQL AB -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement where value depends on auto-generated value from previous insert
Found that function right after I posted. Thanks, it should do exactly what I need. Roger Baklund wrote: * Scott Plumlee I've got two separate tables, each with id fields that are auto-increment. The created fields below are timestamps. The tables are Innodb tables using transactions to process the statements. This will be an online registration process for our business, using PHP and MySQL. PHP is using session ids for tracking state. table1 id first_name last_name created etc. table2 - id table1_id created etc I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id). Any best practices to doing this? Use the LAST_INSERT_ID() function: INSERT INTO table1 SET first_name='roger',last_name='baklund'; INSERT INTO table2 SET table1_id=LAST_INSERT_ID(),etc='whatnot'; This function is connection specific, you will get the correct id even if you have multiple simultaneous users. http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 > http://www.mysql.com/doc/en/Information_functions.html#IDX1428 > -- Roger -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement where value depends on auto-generated value from previous insert
Scott Plumlee wrote: I've looked through the PHP Cookbook and the MySQL cookbook and haven't seen a solution. I've thought about trying to make some unique hash with the data to be inserted but if there's another identical set of data, then the hash would match. I can't use a timestamp in the hash I think I was trying to say I can't use the time that the row was created because I don't know if the timestamp value I get upon insert will exactly match the value I would get from something like a NOW() statement. IE, how do I know if the insert happens at the same moment I get a time value? -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement where value depends on auto-generated value from previous insert
I've got two separate tables, each with id fields that are auto-increment. The created fields below are timestamps. The tables are Innodb tables using transactions to process the statements. This will be an online registration process for our business, using PHP and MySQL. PHP is using session ids for tracking state. table1 id first_name last_name created etc. table2 - id table1_id created etc I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id). Any best practices to doing this? I've considered adding additional fields to both tables to represent a unique id or hash that will be generated by PHP. That way I can tie the two tables together and pull the newly-generated table1.id value out based on the unique hash and insert it into table2. But I'd rather not do that if I don't have to. I don't know if the tables are locked with transactions. If they were, I could insert into table1, then find the last id generated for table1 and then put that into table2. Do transactions behave this way? I can't just use the PHP session id because someone with the same session may register another person, and then I would have the same session ID in two rows. I can't assume the names or other info are unique either. Any ideas or is the PHP unique id/hash going to be my best bet? It just seems wasteful to have to add another field just to tie the data together until I can tie it together with the generated ids. I've looked through the PHP Cookbook and the MySQL cookbook and haven't seen a solution. I've thought about trying to make some unique hash with the data to be inserted but if there's another identical set of data, then the hash would match. I can't use a timestamp in the hash Now that I'm thinking about it, could I do this: 1. Generate a timestamp value 2. Insert into table1 all the info I need. 3. Create a unique hash from the timestamp and inserted info 4. Find the row and id from table1 where the hash of the timestamp value from step 1 and the data in the row match the hash from step 3 5. Put the id value into table 2 Seems like a lot of work to do to find the answer. Any gurus got an idea? Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp woes
Ryan A wrote: /** Somewhat new myself but I believe you can do something like SELECT (whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - ($days_last*24*60*60)) < UNIX_TIMESTAMP(dat_and_tim). I will add the the PHP Cookbook and the MySQL Cookbook are godsends and that's where I got the solution. Grab a copy of these to help out - I've got a project due in a week and I'm using these non-stop. ***/ Hey, Thanks its working like a charm now to get the number of daysbut am still confused on how do I do the second comparision: selecting been sayyy 15th of Feb and 23rd of Feb.. any ideas? Thanks, -Ryan Again, I think you can convert each of the bookend dates to a unix-format time (time since epoch) in PHP (I assume that those dates aren't entered in the db) and then use that same UNIX_TIMESTAMP function on your table entries. Then pull entries out where the table values are between those two values. -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp woes
[EMAIL PROTECTED] wrote: Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table "tbl_users" with a field "dat_an_time" which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have searched google/the manual for the answers to the below questions but I only found answers if the field is a datetime field and not a timestamp, I cannot change the format as I am the new developer of an old (already live) project and lots of scripts are already accessing this table. Please tell me how to do this with my current setup. Two questions: Question 1. In my php script I am taking the value of "days_last" which should run a select query to display all the records in the last $days_last days (for those of you who dont program in PHP/Perl $days_last is the variable that is a number which the user selects. eg: 10 or 5 or 35 etc) How do I format the query to get the results from my "dat_an_time" timestamp(14) field? Somewhat new myself but I believe you can do something like SELECT (whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - ($days_last*24*60*60)) < UNIX_TIMESTAMP(dat_and_tim). I will add the the PHP Cookbook and the MySQL Cookbook are godsends and that's where I got the solution. Grab a copy of these to help out - I've got a project due in a week and I'm using these non-stop. -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
