Re: doc for administration mysql
Hello. Use the manual. See: http://dev.mysql.com/doc/refman/5.0/en/ I like MySQL (3rd Edition) by Paul Dubois as well. There a lot of other good books: http://dev.mysql.com/books/ Bayrouni wrote: Hello all, Wich are the best doc for (mysql administration) beginners Thank you -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE statement causes signal 11 on 5.0.16
Hello. Please, could you explain what does it mean 'Signal 11 without fail'. Usually after receiving such a signal MySQL crashes. Of course, it should do this in normal circumstances. What is in the error log? Check if the problem still exists on official binaries of 5.0.18. Ian Sales (DBA) wrote: Hi, I'm running a 5.0.16 instance on a Debian box (2.6.13 kernel). The following statement causes a signal 11 without fail, and each time when mysqld_safe restarts the daemon, no socket file is created: UPDATE X_Products.product_details AS pd , X_Products.tblMaxProductStockDisplay AS sd SET pd.allocated = IF( pd.stock_levelsd.intMaxStockDisplay , (pd.stock_level-sd.intMaxStockDisplay) , 0 ) WHERE pd.product_uid = sd.intProductID; I can find no reference to unsupported syntax or a bug. Has anyone else had the same happen to them? - ian -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql driving make.
Hello. Please, could you explain more in detail what is the problem to check table dependencies? mysql command line client supports the batch mode, and you can run queries with -e option in the command line. See: http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html Hugh Sasse wrote: I have a database backed-website in development. (It's Rails based, but for this question it probably doesn't matter.) To make sure things are clean during development I re-generate things from a script. Some things I generate depend on tables existing. Other things depend on tables being populated with data. And my testing depends on the web application having been created as well as the tables populated. Clearly the above dependency graph is asking for a Makefile. So how do I check the table dependencies from make? Searching for mysql and make mostly turns up building instructions, of course, so it's tricky to find the answer to this. This is with Mysql 4.1.x, cygwin and Solaris. Thank you Hugh -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE statement causes signal 11 on 5.0.16
Gleb Paharenko wrote: Hello. Please, could you explain what does it mean 'Signal 11 without fail'. Usually after receiving such a signal MySQL crashes. Of course, it should do this in normal circumstances. What is in the error log? Check if the problem still exists on official binaries of 5.0.18. - sorry, idiomatic English :-) signal 11 without fail means it *always* causes a signal 11. I have tried upgrading to 5.0.18, but encountered the same problem. I downgraded back to 5.0.16 because the DEFINER functionality added to triggers in 5.0.17 does not allow triggers to be fired off my replicated queries. - the error log gives a stack dump, and says this could be because you hit a bug. I'm looking for a little more information than that... - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.1.16 starting trouble
Privet! This is a bug: http://bugs.mysql.com/bug.php?id=15965 Have a look here as well: http://bugs.mysql.com/bug.php?id=15151 Most probable you will have to install the latest development source, which has this bug fixed: http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html Trux wrote: Hi! Sorry for my english, im russian. I have trouble with starting mysql-4.1.16. I compiled it from source, and when i run: # mysql_install_db --user=mysql i've got: - ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 060115 10:47:57 [ERROR] Aborting 060115 10:47:57 [Note] /usr/libexec/mysqld: Shutdown complete bin/mysql_install_db: line 299: 20545 Broken pipe cat $fill_help_tables - Anyone know how solve it? Thanks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration of DB from MySQL 4.0.20 to 4.0.24
Hello. Sorry for such a late reply. The problem is in the duplicate line `dealer_type_name`. Remove one of these lines. Please, always send a copy of the message to the list. Most probably somebody whould have suggest you the same, if you had posted you mail to the list as well. Kuai cybermalandro cybermalandro wrote: Sorry about that -- -- Table structure for table `dealer_type` -- CREATE TABLE `dealer_type` ( `dealer_type_id` int(11) NOT NULL auto_increment, `dealer_type_type` varchar(40) NOT NULL default '', `dealer_type_name` varchar(40) NOT NULL default '', PRIMARY KEY (`dealer_type_id`,`dealer_type_id`) ) TYPE=MyISAM PACK_KEYS=1; Here it is, thanks a lot for your help. Kuai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE statement causes signal 11 on 5.0.16
Hello. Resolve a stack trace and provide it to the list. See: http://dev.mysql.com/doc/refman/5.0/en/crashing.html http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html Now about the replication problem. As I've understood you have triggers only on the slave. In my opinion, to solve this problem, you should just recreate all triggers after an upgrade. If you use mysqldump during the upgrade, it should recreate triggers automatically. Ian Sales (DBA) wrote: Gleb Paharenko wrote: Hello. Please, could you explain what does it mean 'Signal 11 without fail'. Usually after receiving such a signal MySQL crashes. Of course, it should do this in normal circumstances. What is in the error log? Check if the problem still exists on official binaries of 5.0.18. - sorry, idiomatic English :-) signal 11 without fail means it *always* causes a signal 11. I have tried upgrading to 5.0.18, but encountered the same problem. I downgraded back to 5.0.16 because the DEFINER functionality added to triggers in 5.0.17 does not allow triggers to be fired off my replicated queries. - the error log gives a stack dump, and says this could be because you hit a bug. I'm looking for a little more information than that... - ian -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE statement causes signal 11 on 5.0.16
Gleb Paharenko wrote: Hello. Resolve a stack trace and provide it to the list. See: http://dev.mysql.com/doc/refman/5.0/en/crashing.html http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html - resolving the stack dump gives me this:- 0x8150650 + 135595600 0xe420 + -7136 0x9aec5f0 + 162448880 0x834e4e8 + 137684200 0x81edff2 + 136241138 0x81b632e + 136012590 0x819c51d + 135906589 - which doesn't help... Now about the replication problem. As I've understood you have triggers only on the slave. In my opinion, to solve this problem, you should just recreate all triggers after an upgrade. If you use mysqldump during the upgrade, it should recreate triggers automatically. - I have tried manually creating the triggers after the upgrade. That's not the problem. The problem is that replicated queries fire off the triggers, but replicated queries run as system user. There is no system user in the privilege tables, so I can't give the account the privileges necessary to run triggers. As a result, the triggers always fail and break replication. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.1.16 starting trouble
On Tuesday 17 January 2006 14:37, Gleb Paharenko wrote: This is a bug: http://bugs.mysql.com/bug.php?id=15965 Have a look here as well: http://bugs.mysql.com/bug.php?id=15151 Most probable you will have to install the latest development source, which has this bug fixed: http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html Well, thanks! What about next release in 4.1.x? May be it will fix this bug? -- Tue Jan 17 16:10:15 ALMT 2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.1.16 starting trouble
What about next release in 4.1.x? May be it will fix this bug? I hope it will. Trux wrote: On Tuesday 17 January 2006 14:37, Gleb Paharenko wrote: This is a bug: http://bugs.mysql.com/bug.php?id=15965 Have a look here as well: http://bugs.mysql.com/bug.php?id=15151 Most probable you will have to install the latest development source, which has this bug fixed: http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html Well, thanks! What about next release in 4.1.x? May be it will fix this bug? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.5-alpha has been released
Hi, MySQL 5.1.5-alpha, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a new alpha development release, adding new features and fixing recently discovered bugs. NOTE: This Alpha release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. We welcome and appreciate your feedback! News from the ChangeLog: Functionality added or changed: * Added the XML functions ExtractValue() and UpdateXML(). ExtractValue() returns the content of a fragment of XML matching a given XPath expression. UpdateXML() replaces the element selected from a fragment of XML by an XPath expression supplied by the user with a second XML fragment (also user-supplied), and returns the modified XML. See Section 12.9, XML Functions. * Added the --base64-output option to mysqlbinlog to print all binary log entries using base64 encoding. This is for debugging only. Logs produced using this option should not be applied on production systems. * Added the --port-open-timeout option to mysqld to control how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened. (Bug #15591 (http://bugs.mysql.com/15591)) * Two new Hungarian collations are included: utf8_hungarian_ci and ucs2_hungarian_ci. These support the correct sort order for Hungarian vowels. However, they do not support the correct order for sorting Hungarian consonant contractions; this issue will be fixed in a future release. * Plugins can now have status variables, that are shown in SHOW STATUS. Bugs fixed: * InnoDB: Comparison of indexed VARCHAR CHARACTER SET ucs2 COLLATE ucs2_bin columns using LIKE could fail. (Bug #14583 (http://bugs.mysql.com/14583)) * Creating a trigger caused a server crash if the table or trigger database was not known because no default database had been selected. (Bug #14863 (http://bugs.mysql.com/14863)) * Issuing a DROP USER command could cause some users to encounter a hostname is not allowed to connect to this MySQL server error. (Bug #15775 (http://bugs.mysql.com/15775)) * The --plugin_dir option was not working. Also fix error with specifying parser name for fulltext. (Bug #16068 (http://bugs.mysql.com/16068)) * Attempting to insert into a table partitioned by LIST a value less than any specified in one of the table's partition definitions resulted in a server crash. In such cases, mysqld now returns ERROR 1500 (HY000): Table has no partition for value v , where v is the out-of-range value. (Bug #15819 (http://bugs.mysql.com/15819)) Additional notes: * The Windows package, mysql-noinstall-5.1.5-alpha-win32.zip, contains just the mysqld-max.exe and mysqld-max-nt.exe binaries; because mysqld.exe, mysqld-nt.exe, and mysqld-debug.exe have not been built for this version. If you previously used one of the latter binaries, you may need to adapt your settings. Enjoy! Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown problem with backup restore
I am trying to restore a backup on a two processor machine with a Debian installation with 2.4 kernel and 2GB or RAM, but when it reaches a MyISAM table of about 1.8 GB it stops restoring with no error output. The MySQL version is Distrib 5.0.16. I have tried to restore it in many other systems (unstable Debian, Ubuntu, Windows 2000) and all of them worked fine. Trying to figure out if the table had problems, I used myisamchk and it returned OK. I tried to backup and restore just this table and it restored fine. I have tried almost everything, but the only way it worked is by restoring the table independently. It is not an acceptable way for backing up the database because it will grow up to a size in which it could be very hard to backup. The backup was created with mysqldump --opt ... and I try to restore it with mysql -u root -pPwd DB file.sql. Any ideas??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0 error after upgrade
Can someone help me. I was running v4 and just upgraded the version to v5.0. Most of my tables are MyISAM with some InnoDb. Most of the MyISAM tables are char fields but a few have integer columns. When trying to create a new table using the Administrator 1.1 program it is fine creating columns which are of type char but it fails when creating a column of type integer. The error message is ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET latin1 COLLATE latin1_general_ci; Can someone advise me what has changed in the upgrade and how I can get back to using integers? kERRY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHAR vs TEXT and fast Row Updates
Hi All, We have an application that needs to update rows in the database 'as fast and efficiently' as possible. To this end, we've tried re-designing the table - can someone clarify the following assumptions we're about to make? 1. Fixed length CHAR fields are quicker to update than VARCHAR fields (because the field size is constant) 2. If we 'pre-create' 5,000 records in a table - and run an UPDATE on this, to 'replace' records in the table - it will be faster than if we run a 'DELETE' followed by 'INSERT' operation on the table (to keep the number of records to 5,000) The only other query I have is regarding TEXT fields. Some of the data in the table isn't big enough to be held in a CHAR/VARCHAR column (i.e. it's over 255 characters). Do we get any 'saving' by using a TEXT field, and pre-populating this with say 2K of 'spaces' when we create the 5,000 rows - and then ensuring that the UPDATE operation always writes 2K of text to the field? - e.g. will this avoid MySQL having to 'free up' the space for the field, then re-allocate 2K again for it. Or is it not that 'clever'? I realise the savings from the above could be fairly small, but when you're processing thousands of updates an hour, it all adds up - if we can have MySQL doing 'less work' for the updates. Thanks in advance, -Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relaying mysql datas
Hi, Recently i think sqlrelay is a solution for me, but now i don't know. I try to explain what I would like to do. I have written a software on win32 platform in c++. It's using mysql database and connecting to it with libmysql.dll. The task is connect two local network throught the internet using one database. (or master / slave database ?) The modifications have to come into force immediately on local networks but the syncronization between networks can be late. I have no problem on local network of course. The problem is come forward when i would like to connect to the mysql server throught internet. When i'm running querys which hit a lot of records it takes a long time to get result and while mysql serves this query the local clients is waiting too because of select read lock. (nolock is not a solution) I planed to use one database server and sqlrelay, but unfortunately i have to realize that sqlrelay is not transparent - so i can't connect to it like to a mysql server. I can't rewrite the win32 software to use sqlrelay c++ api so i can't use it? Please help if you have any idea / software to handle this situation! MySQL master/slave replication maybe a solution but i don't know what's happening when i trying to modifiy the slave database? The modification not get on the master, am i right? Thank you in advance! Best Regards Zsolt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHAR vs TEXT and fast Row Updates
Hi, 2006/1/17, Karl Pielorz [EMAIL PROTECTED]: Hi All, 1. Fixed length CHAR fields are quicker to update than VARCHAR fields (because the field size is constant) There is no advantage if not all your field in your table are fixed size. as soon as you add a text/blob column, you loose the fixed row length. Keep in mind that index will also be fixed-length, and it can be more efficient to have varchar to have quick select. Do we get any 'saving' by using a TEXT field, and pre-populating this with say 2K of 'spaces' when we create the 5,000 rows - and then ensuring that the UPDATE operation always writes 2K of text to the field? - e.g. will this avoid MySQL having to 'free up' the space for the field, then re-allocate 2K again for it. space is not reclaimed after deletion until you run an : optimize table. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Browser and MySQL 4.0 - UTF-8
Hello everybody, I'm using Query Browser 1.1.17 on Windows with a MySQL 4.0 database server on Linux and was just editing a piece of text for a web CMS in the database. But what do I get to see there? The content was stored as UTF-8, although the website uses ISO-8859-1 encoding, the data was stored in that encoding before, it was displayed correctly in Query Browser editor and the database server doesn't transparently support Unicode at all. Why the hell is it UTF-8 now? The entire system isn't supposed to support that encoding, yet is it used. I haven't found any option to turn it off of course, because it is not supported. So where's that hidden option please to tell Query Browser to NOT use any kind of Unicode knowledge? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about CONTAINS SQL
- Original Message - From: wangxu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 17, 2006 12:35 AM Subject: question about CONTAINS SQL I notice there are one section in the manual: CONTAINS SQL indicates that the routine does not contain statements that read or write data. And that the option is default. It's true? If i wouldn't do read or write in routine.What can i do yet? Commands like GRANT or REVOKE or CREATE TABLE don't read or write data within tables but they involve SQL so commands like this need CONTAINS SQL, rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA options. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date: 16/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to copt table not whole database
Thank you Shawn. What I originally thought is that if there is any command like 'sqldump' to copy a few tables of a database. Xiaobo Xiaobo Chen [EMAIL PROTECTED] wrote on 01/16/2006 03:33:36 PM: Hi, all How should I copy a table of a database? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Do you want the data or the structure too? Just the data: CREATE TABLE newtable SELECT * from oldtable; Data and the original structure (no foreign keys): CREATE TABLE newtable LIKE oldtable; INSERT newtable SELECT * FROM oldtable; Both forms of CREATE TABLE documented here: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to copt table not whole database
Xiaobo, Using mysqldump, you can specify which tables to copy. The man for mysqldump is as such: mysqldump [options] [dbname [tablename]] Say db 'db' has tables x, y, and z in it. You only want to copy table x. mysqldump db x x.sql(or whatever you want to name it). Dan Thank you Shawn. What I originally thought is that if there is any command like 'sqldump' to copy a few tables of a database. Xiaobo Xiaobo Chen [EMAIL PROTECTED] wrote on 01/16/2006 03:33:36 PM: Hi, all How should I copy a table of a database? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Do you want the data or the structure too? Just the data: CREATE TABLE newtable SELECT * from oldtable; Data and the original structure (no foreign keys): CREATE TABLE newtable LIKE oldtable; INSERT newtable SELECT * FROM oldtable; Both forms of CREATE TABLE documented here: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Most efficient way to design this table
How did you get these byte calculations? I thought an INT took up 4 bytes and char(10) would take 10 bytes. http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Grant, I suggest to change both key fields to Integers. Numeric keys take less space and are faster. Your Product_Feature table then may have: - product_id INT unsigned - feature INT unsigned having a Primary Key of 8 bytes i.s.o. 141 bytes. Thus a smaller index, less disk reads and more speed. HTH, Cor Vegelin - Original Message - From: Grant Giddens To: Sent: Tuesday, January 17, 2006 2:28 AM Subject: Most efficient way to design this table Hi, I currently have a table with 2 columns, product_id and feature. Each product_id can have multiple features. My two columns are: product_id is type char(13) feature is type varchar(128) In order to make sure I don't have the same feature listed twice for a particular product, I have set the PRIMARY key on product_id and feature. I have lots of products and lots of features. Is design bloating my available key_buffer memory with too much data? Is there a better way to index this data? Thanks, Grant - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP.
Re: how to copt table not whole database
Dan, thank you very much. That's what I want. Xiaobo Xiaobo, Using mysqldump, you can specify which tables to copy. The man for mysqldump is as such: mysqldump [options] [dbname [tablename]] Say db 'db' has tables x, y, and z in it. You only want to copy table x. mysqldump db x x.sql(or whatever you want to name it). Dan Thank you Shawn. What I originally thought is that if there is any command like 'sqldump' to copy a few tables of a database. Xiaobo Xiaobo Chen [EMAIL PROTECTED] wrote on 01/16/2006 03:33:36 PM: Hi, all How should I copy a table of a database? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Do you want the data or the structure too? Just the data: CREATE TABLE newtable SELECT * from oldtable; Data and the original structure (no foreign keys): CREATE TABLE newtable LIKE oldtable; INSERT newtable SELECT * FROM oldtable; Both forms of CREATE TABLE documented here: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration of DB from MySQL 4.0.20 to 4.0.24
My fault about not replying to the list. What is the cause of this extra line? is this a mysqldump bug in that older version of MySQL? how come I was able to export and import this DB in the same version but different platform? Any ideas? Thanks, cybm On 1/17/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Sorry for such a late reply. The problem is in the duplicate line `dealer_type_name`. Remove one of these lines. Please, always send a copy of the message to the list. Most probably somebody whould have suggest you the same, if you had posted you mail to the list as well. Kuai cybermalandro cybermalandro wrote: Sorry about that -- -- Table structure for table `dealer_type` -- CREATE TABLE `dealer_type` ( `dealer_type_id` int(11) NOT NULL auto_increment, `dealer_type_type` varchar(40) NOT NULL default '', `dealer_type_name` varchar(40) NOT NULL default '', PRIMARY KEY (`dealer_type_id`,`dealer_type_id`) ) TYPE=MyISAM PACK_KEYS=1; Here it is, thanks a lot for your help. Kuai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--replicate-rewrite-db fails when attempting to drop a table
Hi List, I have been asked to setup replication between two customers servers for one particular database, which I will call in this post 'livedb'. The customer also has a copy of this database on the slave which they use for testing. This copy does not contains the same data as the master and the customer wants to leave it this way, they therefore asked if it was possible to replicate to a different database name, e.g. livedb_backup. I suggested the use of the --replicate-rewrite-db option. When I set this up and tested it I decided to create a new table and then drop it to make sure it was working ok ( if it wasn't I didn't want to taint the existing data ). I created a table called test_replication and it was promptly created on the slave, but, when I issued a DROP TABLE command the replication stopped and the follwing error was present in the .err file: 060117 15:25:22 Slave: Error 'Unknown table 'test_replication'' on query. Default database: 'livedb_backup'. Query: 'DROP TABLE `livedb`.`test_replication`', Error_code: 1051 Both servers are Windows running version 4.0.26 ( one is win2k the other win2k3 ). Am I doing something wrong or is this a bug? Here is the relevant section of my.ini on the slave: ---8- [mysqld] basedir=D:/mysql datadir=D:/mysql/data set-variable=max_connections=300 log-bin server-id=20 log-error log-slow-queries log-slave-updates log-warnings replicate-do-db=livedb_backup replicate-rewrite-db=livedb-livedb_backup ---8- Thanks Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Droping multiple tables by a pattern in the table name
Hello, I'm using mysql 4.1.14-standard. I would like to drop multiple tables in one or few queries. I don't have an easy way to predict their names (I have a way to find their names, but it's by using software and I prefer doing it with mysql). All of the table names have a fixed part which I know, and I thought of using the fixed, known part of the names to delete all of these tables at once. Can someone lead my to the way doing it with a mysql queries? I thought of using the combination of 'SHOT TABLES LIKE '%regexp%'; and DROP, but I don't know how to combine them. Thanks ahead, Yair. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OK, need a little inspiration here...
I am stomped and not sure how to get results from the particular type of query. While I am not sure, if this is an appropriate place to ask, if nothing else perhaps someone will direct me to a more appropriate forum. I am trying to figure out how to return the latest record in each group of records identified by some hash, e.g. (^ are rows I want), ^ 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000542 | 20031018214128 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000525 | 20031018210622 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000513 | 20031017010947 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^ 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | ^ 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | ^ 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000152 | 20030917084805 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000151 | 20030915095857 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | The result I want is: | 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | I tried GROUP BY on the hash, but then MySQL grabs the earliest record. (Anyway to influence the sorting order before GROUP BY is applied?) E.g., | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | Alternatively, I also tried MAX(date), which of course is incorrect, because while returning the latest date for the hash--good!, it looses touch with the remaining columns of the record, i.e, ids and dates are mismatched, | outside provided sample | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^^^ ignore | 000417 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | outside provided sample | b8b993464ec303bbff704f582e4f7b5b9ea100bf | ^^^ ignore, again record outside the provided sample Am I missing some kind of magic somewhere or do I need to resort to extra columns and/or extra steps to get what I want? I am using 4.1.16 on Linux with 512 MB of RAM, so not sure if using intermediate tables would be a well performing solution, but if there is one, I still want to hear about it. :-) TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OK, need a little inspiration here...
Gmail User wrote: I am stomped and not sure how to get results from the particular type of query. While I am not sure, if this is an appropriate place to ask, if nothing else perhaps someone will direct me to a more appropriate forum. I am trying to figure out how to return the latest record in each group of records identified by some hash, e.g. (^ are rows I want), ^ 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000542 | 20031018214128 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000525 | 20031018210622 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000513 | 20031017010947 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^ 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | ^ 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | ^ 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000152 | 20030917084805 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000151 | 20030915095857 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | The result I want is: | 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | The trick I use in a situation like this is to LEFT JOIN the table to itself. Suppose the three columns in the output you gave are id INT, time DATETIME, and csum VARCHAR from the table Checksums. I'd do something like: SELECT c.* FROM Checksums c LEFT JOIN Checksums c2 ON c.csum = c2.csum AND c.time c2.time WHERE c2.id IS NULL The ON clause of the LEFT JOIN will pair up each row of Checksums with another row of Checksums sharing the same csum, and having a more recent time. The records with the most recent time will be paired with a NULL row, and selected for return by the WHERE clause. -Maciej Babinski -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple load query
hi all... whats the problem with this: load data infile 'stores.txt' into table useyourcash_sports_us (chain, store, address1, address2, city, state, zip,web) FIELDS TERMINATED BY '\t' i get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY '\t'' at line 1 why feilds term... is wrong there? thanks a lot... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple load query
Your FIELDS clause is not in the right place: http://dev.mysql.com/doc/refman/5.0/en/load-data.html HTH, James Harvard At 7:06 pm -0500 17/1/06, kalin mintchev wrote: whats the problem with this: load data infile 'stores.txt' into table useyourcash_sports_us (chain, store, address1, address2, city, state, zip,web) FIELDS TERMINATED BY '\t' i get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY '\t'' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using SET time_zone for localization
I'm using SET time_zone = 'US/Hawaii' to convert timezones and it's not
working.
What am I doing wrong?
I have verified that the session.time_zone var is set correctly.
if(defined('ZMM_USER_LOCALE')) {
$query = SET time_zone = ' .
mysql_real_escape_string(ZMM_USER_LOCALE) .'; ;
$sel_result = @mysql_query($query);
}
$query = SELECT `int_resp_id`, ;
$sel_result = @mysql_query($query);
If I make the second $query = SELECT NOW() I get the correct time
adjusted values.
Does this method only work for INSERTS or when you want to adjust the
NOW() value? Not for pulling select statements out of the db?
