Re: Borland C++ Builder 2006 DLL Woes
I had to buy dbExpress libraries for MySQL from third party. Dusan [EMAIL PROTECTED] napsal(a): We just purchased The Borland Developer Studio 2006 IDE and are having significant problems using dbExpress objects to communicate with MySQL servers (both 4 and 5). Curiously, we can perform inserts but not selects, even though identical code in C++ Builder 6 worked just fine. The DLL in C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues? Thanks, David David P. Giragosian, Psy.D. Database and Software Developer MD Anderson Cancer Center Houston, TX 713-792-7898 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql problem
I have a database online and have a strange mysql problem. When I connect remotely from my desktop (same browse_database.php page) I get over 1000 results with the query below but when I run the same page on the remoted server this value is almost halved to 520. any ideas what is hapening? $total_rows = mysql_num_rows(mysql_query(SELECT * FROM $table_name)); It just seems to ignore them on the remote one as I am in under ross and ross2 and only one when I search for me. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gaining statistics from MySQL
On 2/20/07, Clyde Lewis [EMAIL PROTECTED] wrote: I'm looking to find a way to determine the number of transactions that a particular database is processing each min/hour/day/month/year http://dev.mysql.com/doc/refman/4.1/en/show-status.html http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html Looks like Com_commit is what you're looking for (though you can replace commit with most other types of query, i.e. Com_select, Com_insert...). This is a counter, so you'll need to record the value periodically and work out the differences for a period. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unauthenticated user
Hi, i got this results from show processlist | 11186 | unauthenticated user | 192.168.1.106:36198 | | Connect | NULL | login | | | 11187 | unauthenticated user | 192.168.1.106:36200 | | Connect | NULL | login | | | 11188 | unauthenticated user | 192.168.1.106:36201 | | Connect | NULL | login | | | 11189 | unauthenticated user | 192.168.1.106:36202 | | Connect | NULL | login | | | 11190 | unauthenticated user | 192.168.1.106:36203 | | Connect | NULL | login | | | 11191 | unauthenticated user | 192.168.1.106:36204 | | Connect | NULL | login | | | 11192 | unauthenticated user | 192.168.1.106:36205 | | Connect | NULL | login | | | 11193 | unauthenticated user | 192.168.1.106:36206 | | Connect | NULL | login | | | 11194 | unauthenticated user | 192.168.1.106:36207 | | Connect | NULL | login | | | 11195 | unauthenticated user | 192.168.1.106:36208 | | Connect | NULL | login | | | 11196 | unauthenticated user | 192.168.1.106:36209 | | Connect | NULL | login | | +---+--+-+--+-+--+---+--+ i was wondering if this is a bug or someone is trying to connect without authenticating... and would this processes eat max_connection value? thanks, Maling-List DISCLAIMER: This Message may contain confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you received this message in error please notify your Mail Administrator and delete this message immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of GMA New Media, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Argh! Another Server Has Gone Away
Hi, I know this problem is already on forums and archives but I don't understand mine ! My MySQL Server works well, there are many scripts and website using it, without any problem. Since few days I'm working on a new project and if a do (php) a mysql_connect and a mysql_query() I directly win a Server has gone away. My query is a simple INSERT INTO on an empty table. I already changed my MySQL config to an higher max_allowed_packet and watch all the configuration... I don't find any problem. Other idea ? :/ Sorry to bother you again with this problem ... -- Kévin Labécot Analyste Programmeur www.cv.labecot.fr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
I think that's only true if the server is using the default system time zone. In addition, you can set a per-connection time zone and use time zone sensitive functions such as CONVERT_TZ(). All of these mean that MySQL needs to be aware of time zone definitions. There could also be trouble if you are using replication across time zones, I suspect. There are explicit instructions for building time zone tables in section 5.9.8 of the manual. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 5:17 PM To: Sun, Jennifer; [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: MySQL Daylight Savings Time Patch At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
I just ran mysql_tzinfo_to_sql on a CentOS (Linux) system, and it complained about the various Riyadh time zones: Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it. etc. Any idea whether or not this is normal? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Arbitrary Docs Machinery
The MySQL documentation team has created a way that enables us to extract arbitrary pieces of MySQL documentation and recombine them into a new document. With that machinery for creating arbitrary docs, we could, for example, do this: * Bond the Cluster docs with the rest of the Storage Engines. * Create a Cluster and Partitioning standalone guide. * Collate all the Windows sections into a single document. * Create a Connectors/APIs document where each of the current connector sections is a chapter in the new book. * Put a section anywhere, and remap any section to any block (i.e. section to chapter or appendix, chapter to appendix, you name it). Basically, we can combine excerpts from any documentation that can be found on http://dev.mysql.com/doc. The arbitrary docs machinery doesn't work in a copy-and-paste manner, but rather by including blocks (chapters, appendices, or sections) of the original documentation. This means that whenever the underlying documentation changes the arbitrary docs will also change. (You may have noticed that this concept is similar to views in a database.) This is what our users can do with that machinery: 1. Look at our sample PDF file that demonstrates what can be done; it combines all Windows-related installation instructions from various docs files: http://svn.mysql.com/svnpublic/mysqldoc/arbitrary/windows-sample.pdf 2. Tell us ([EMAIL PROTECTED]) which new excerpts or combinations of documents you'd like us to create and make available on http://dev.mysql.com/doc. 3. Create arbitrary docs yourself. For this, you can find everything you need in the public copy of our mysqldoc repository (viewable with any web browser) on http://svn.mysql.com/svnpublic/documentation. To see how creating your own arbitrary documents works, try our sample files: 1. Use Subversion (SVN) to pull a working copy of http://svn.mysql.com/svnpublic/mysqldoc. 2. You need an environment that has make and xsltproc installed (any Unix-like system, or Cygwin on Windows). To create PDF files, you also need fop. 3. A sample arbitrary specification file (windows.aspec) and the necessary DocBook wrapper (windows.xml) are located in the arbitrary directory. A sample of the file generated from that specification is provided as windows-sample.pdf. 4. To generate HTML or PDF output for yourself: * Change to the upmost directory and run make realclean - this will clean out any old and temporary files. * Change to the arbitrary directory and run make idmap.refs - this will rebuild all the ID map reference files which are required for the arbitrary functionality. * Within arbitrary, run make windows-arbitrary.pdf to make the PDF version, or make windows-arbitrary.html to create the HTML version. Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead. Berlin, Germany (UTC +1:00) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unauthenticated user
Hi JM, JM wrote: i got this results from show processlist | 11186 | unauthenticated user | 192.168.1.106:36198 | | Connect | NULL | login | | | 11187 | unauthenticated user | 192.168.1.106:36200 | | Connect | NULL | login | | That's just fine. The user is in login state, so he/she/it is not yet authenticated. There might be a DNS issue slowing down login, you might check if you can reverse lookup the hostnames of the connecting users. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
Aren't the time zone tables new in 4.1.3? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Bryan S. Katz [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 9:03 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch Same exact issue on v5.0.27. I installed another windows patch, that did nothing, but then changed my system clock to march 12th, and then back to present day. Now the DST shifts are correct in the future and incorrect in the past. I've destroyed the timezone tables, and they made no difference. Which means that they were never being used. Any ideas? - Original Message - From: Bryan S. Katz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 20, 2007 7:50 PM Subject: MySQL Daylight Savings Time Patch Running 4.1.22, on windows 98, I'm having trouble getting the time zone tables to actually work. I've loaded the tables as per: http://dev.mysql.com/downloads/timezones.html, and followed the diagnostics as per: http://lists.mysql.com/mysql/205115 . It appears that my results from said diagnostic indeed prove that my tables are correct. However, I don't see those tables being used. The following statements yield varying results on my linux/windows machines (I run about ten servers). SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-4-1 00:00:00') + 2*60*60),'%H'); SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-3-11 00:00:00') + 2*60*60),'%H'); SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-4-2 00:00:00') + 2*60*60),'%H'); SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-3-12 00:00:00') + 2*60*60),'%H'); The linux machine -- with empty timezone tables is correct. 2007-3-11 and 2006-4-2 result in midnight plus 2 hours being 3am -- correct in the past and correct in the future. On my modern XP machines, the future is correct and the past is incorrect. On my older XP/98 machines, the past is correct and the future is incorrect. It is on my 98 server that I am now playing, having loaded timezone tables, and going crazy. I have upgraded my win98 timezones via a registry update. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If it's any consolation, I got the exact same warnings. However, I don't know if it's normal either. - -- Skype: cannona MSN/Windows Messenger: [EMAIL PROTECTED] (don't send email to the hotmail address.) - - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'Paul DuBois' [EMAIL PROTECTED]; 'Sun, Jennifer' [EMAIL PROTECTED]; 'Dan Buettner' [EMAIL PROTECTED]; 'Chris White' [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 8:45 AM Subject: RE: MySQL Daylight Savings Time Patch I just ran mysql_tzinfo_to_sql on a CentOS (Linux) system, and it complained about the various Riyadh time zones: Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it. etc. Any idea whether or not this is normal? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) - GPGrelay v0.959 Comment: Key available from all major key servers. iD8DBQFF3GZ+I7J99hVZuJcRAvUUAJ4xHKNQtxYBSrpDqadTzPdBx3uQIwCfRZkL uQ5ODv/bD5SN5CW9JpYIlxQ= =z+FD -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and FOREIGN KEY
Hi to all, I am a student, i am studing Mysql FOREIGN KEY for to do a little progect, but i have this problem: i create this table: CREATE TABLE Cliente( codcliente VARCHAR(6) NOT NULL, nome VARCHAR(10) NOT NULL,cognome VARCHAR(20) NOT NULL, citta VARCHAR(10), indirizzo VARCHAR(20), tel VARCHAR(7) NOT NULL, PRIMARY KEY (codcliente))ENGINE=INNODB; CREATE TABLE villaggio (idvillaggio INT AUTO_INCREMENT, nome VARCHAR(15) NOT NULL, stato VARCHAR(3) NOT NULL, descrizione VARCHAR(30),PRIMARY KEY(idvillaggio)) ENGINE=INNODB; but when i create this table: CREATE TABLE offerta(idofferta INT AUTO_INCREMENT,of_idvillaggio INT NOT NULL, INDEX(of_idvillaggio),FOREIGN KEY(of_idvillaggio),REFERENCES villaggio(idvillaggio) ON UPDATE CASCADE ON DELETE RESTRICT)ENGINE=INNODB; i have this error: REFERENCES error control manual p.s. table villaggio is father and table offerta is children i see the manual but i don't see error, help me !!! excuse me for my bad english Best Regads Antonio ___ L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Result of select is broken by running another select
Hi all, I have an extremely strange problem here. A particular part of a project I work on has two SQL queries -- both selects, both join similar tables, and neither modifies anything. Running the first one works until I run the second. After that, running the first returns no results (it previously returned 1 row). The only way to make the first run again is to comment out a particular part of it (which is also in the other query) and run it. Uncommenting that part then makes it run as usual until the second query is run again. I can provide more information on the actual data and tables involved if required but am leaving it out for now as I have a feeling this may be some sort of caching issue (because of the repeated part of query being key to making it work again). If anyone feels this information may assist in diagnosing the problem please let me know. The first query (the one which breaks) is this: SELECT * FROM privilege Paccess JOIN privilege Phome ON Phome.user_id = '26' AND Phome.type = 'network-member' -- AND (Phome.network_id = Paccess.network_id OR Paccess.network_id IS NULL) WHERE Paccess.user_id = '4' AND Paccess.type = 'user-manage'; The commented out line is the one which must be commented to make it run again. The second query (the one which makes the first one break) is this: SELECT U.id, U.name FROM user U -- find each user's home network JOIN privilege Phome ON Phome.user_id = U.id AND Phome.type = 'network-member' -- current user's user-management access rights LEFT JOIN privilege Paccess ON Paccess.user_id = '4' AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL) AND Paccess.type = 'user-manage'; You can clearly see the second to last line is repeated from the first. This is running on MySQL Ver14.12 Distrib 5.0.22 for Win32 on a Windows 2000 box. I have set up a Windows XP box with same version and duplicated the database content onto it but cannot replicate the problem there. Please help, this is driving me mad! --rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and FOREIGN KEY
Micol lupen wrote: Hi to all, I am a student, i am studing Mysql FOREIGN KEY for to do a little progect, but i have this problem: i create this table: CREATE TABLE Cliente( codcliente VARCHAR(6) NOT NULL, nome VARCHAR(10) NOT NULL,cognome VARCHAR(20) NOT NULL, citta VARCHAR(10), indirizzo VARCHAR(20), tel VARCHAR(7) NOT NULL, PRIMARY KEY (codcliente))ENGINE=INNODB; CREATE TABLE villaggio (idvillaggio INT AUTO_INCREMENT, nome VARCHAR(15) NOT NULL, stato VARCHAR(3) NOT NULL, descrizione VARCHAR(30),PRIMARY KEY(idvillaggio)) ENGINE=INNODB; but when i create this table: CREATE TABLE offerta(idofferta INT AUTO_INCREMENT,of_idvillaggio INT NOT NULL, INDEX(of_idvillaggio),FOREIGN KEY(of_idvillaggio),REFERENCES villaggio(idvillaggio) ON UPDATE CASCADE ON DELETE RESTRICT)ENGINE=INNODB; i have this error: REFERENCES error control manual p.s. table villaggio is father and table offerta is children i see the manual but i don't see error, help me !!! excuse me for my bad english Best Regads Antonio _ L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html of_idvillaggio INT NOT NULL idvillaggio INT These are not the same type. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and FOREIGN KEY
Hi, Micol lupen wrote: FOREIGN KEY(of_idvillaggio),REFERENCES villaggio(idvillaggio) ^^^ check here! ON UPDATE CASCADE ON DELETE RESTRICT)ENGINE=INNODB; No comma before REFERENCES. REFERENCES is part of the foreign key definition. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Growing innodb size
Hello list, I would like to grow my innodb table space, the only problem that I have is that I did not declare any size in the config file since we were not using it to start with. If I modify the config file, will this override the current innodb file or will it grow it ? Or should dump all the data, modify the config file and re-import everything ? Thanks. NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et nest destiné qu'à la personne à qui il est adressé. Si vous nêtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement lexpéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt derreur puisque les informations quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row count inconsistency
The table was created and then loaded and not modified in any way I'm aware of afterwards. It's on a local, only accessible by me server. Really weird thing about it is that I wrote/ran a program specifically to find any gaps in the id sequence - because of the size of the table it took days to run but the result was 1-100537311 IOW it confirms the max id that Mysql gave, but also indicates that there are no gaps in the row id's all the way through. This doesn't make sense to me in light of Mysql reporting the count as posted previously i.e. mysql select count(*) from fidcid; +---+ | count(*) | +---+ | 100480507 | +---+ 1 row in set (0.09 sec) I did do some testing before letting the program run, but here's the main code: my($cur,$prv,$rating,$line) = (1); $line = $cur-; for $cur(1..100537311) { $rating = getDat $DB ({table='fidcid',cols='id,cId,fId,ring',cond=id=$cur}); if($prv != $cur-1) { $line .= $prv\n; print $line; $line = $cur- } $prv = $cur; print $cur at ,`time /T\n` if not $cur % 10 } print $line,100537311\n On 2/20/07, Steve Edberg [EMAIL PROTECTED] wrote: At 6:23 PM -0500 2/19/07, Marty Landman wrote: Hi, I've got a very large table set up and have defined the id as auto_increment. No rows have been added, deleted, or replaced since the initial load so I'd expect the row count to equal the max(id) since mysql describe fidcid; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | fId | smallint(5) unsigned | NO | MUL | || | cId | mediumint(8) unsigned | NO | MUL | || | ring | tinyint(3) unsigned | NO | | || ++---+--+-+-++ 4 rows in set (0.38 sec) But this is not the case, as seen below: mysql select count(*) from fidcid; +---+ | count(*) | +---+ | 100480507 | +---+ 1 row in set (0.09 sec) mysql select max(id) from fidcid; +---+ | max(id) | +---+ | 100537311 | +---+ 1 row in set (0.22 sec) mysql Any ideas on what might've happened to explain this? Had the table been used before? The auto_increment counter is normally not reset, for example: mysql create table test (id int unsigned auto_increment not null primary key); Query OK, 0 rows affected (0.03 sec) mysql insert into test values (null),(null),(null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from test; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) mysql delete from test; Query OK, 3 rows affected (0.00 sec) mysql insert into test values (null),(null),(null); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from test; ++ | id | ++ | 4 | | 5 | | 6 | ++ 3 rows in set (0.00 sec) You can either drop/recreate the auto_increment field or explicitly reset it using an alter table tablename auto_increment=1 statement. See http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html for more info. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- Web Installed Formmail - http://face2interface.com/formINSTal/ Webmaster's BBS - http://bbs.face2interface.com/
view data is inaccurate
I'm creating a view, but I'm having a problem. What you see when you SELECT * the view is different from what you would see if you just ran the SQL that is used to create the view. Here's an example of what I mean: So first of all here's the SQL that gets me the data I want: SELECT DISTINCT(applicanthistory.contact_id) as cid, ( SELECT statustype.statustypelabel FROM statustype, applicanthistory WHERE statustype.statustype_id = applicanthistory.statustype_id AND applicanthistory.contact_id = cid ORDER BY applicanthistory.statusdate DESC, applicanthistory.statustype_id LIMIT 1 ) as currentstatus FROM applicanthistory It returns this: +-+---+ | cid | currentstatus | +-+---+ | 1 | N | | 2 | N | | 3 | N | | 4 | N | | 5 | N | | 6 | P | | 7 | N | | 8 | N | | 9 | N | | 10 | N | +-+---+ Here's the SQL used to create the view: CREATE VIEW contactextension_view AS SELECT DISTINCT(applicanthistory.contact_id) as cid, ( SELECT statustype.statustypelabel FROM statustype, applicanthistory WHERE statustype.statustype_id = applicanthistory.statustype_id AND applicanthistory.contact_id = cid ORDER BY applicanthistory.statusdate DESC, applicanthistory.statustype_id LIMIT 1 ) as currentstatus FROM applicanthistory When I do this: SELECT * FROM contactextension_view; I see: +-+---+ | cid | currentstatus | +-+---+ | 1 | N | | 2 | N | | 3 | N | | 4 | N | | 5 | N | | 6 | N | | 7 | N | | 8 | N | | 9 | N | | 10 | N | +-+---+ Notice cid = 6. The results from the first query (not the view) are correct. Why oh why would the view show something different from the sql that is used to create it? I thought a view was more or less just an alias for a query. Thanks in advance for any help. -Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger
Any way to get this to work inside an after update trigger? insert into max_donations (donation_id, contact_id) select donation_id, contact_id from donation_test where conatct_id = NEW.contact_id and total_amount = contact_max_amount; thanks winn Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Growing innodb size
Hi, Jean-Sebastien Pilon wrote: I would like to grow my innodb table space, the only problem that I have is that I did not declare any size in the config file since we were not using it to start with. If I modify the config file, will this override the current innodb file or will it grow it ? Or should dump all the data, modify the config file and re-import everything ? Chances are that innodb isn't even functional if you did not specify a tablespace any way. You can check with: mysql SHOW VARIABLES LIKE LIKE 'innodb_data%'; If there is a data path given you'll see how it has been set up. Do you want a fixed size or should it grow automatically? Anyways, you might want to check this documentation: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html NOTICE: You should not send privileged and confidential information to a maillinglist ;) regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger
insert into max_donations (donation_id, contact_id) select donation_id, contact_id from donation_test where conatct_id = (You missppelled contact_id) NEW.contact_id and total_amount = contact_max_amount; - Original Message - From: Winn Johnston [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 11:26:42 AM (GMT-0500) Auto-Detected Subject: trigger Any way to get this to work inside an after update trigger? insert into max_donations (donation_id, contact_id) select donation_id, contact_id from donation_test where conatct_id = NEW.contact_id and total_amount = contact_max_amount; thanks winn Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know. -- 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: row count inconsistency
Marty Landman wrote: The table was created and then loaded and not modified in any way I'm aware of afterwards. It's on a local, only accessible by me server. Really weird thing about it is that I wrote/ran a program specifically to find any gaps in the id sequence - because of the size of the table it took days to run but the result was 1-100537311 IOW it confirms the max id that Mysql gave, but also indicates that there are no gaps in the row id's all the way through. This doesn't make sense to me in light of Mysql reporting the count as posted previously i.e. mysql select count(*) from fidcid; +---+ | count(*) | +---+ | 100480507 | +---+ 1 row in set (0.09 sec) If this table is InnoDB, then count(*) is just an approximation. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Retrieving foreign keys and references
Hi: Below is the following relevant create table syntax CREATE Table providers( [snipped] FOREIGN KEY (status) REFERENCES provider_status(ID,title), UNIQUE KEY ID (ID) ) TYPE=MyISAM; and below is the relevant output from a describe query: mysql show columns from providers; +---+---+--+-+++ | Field| Type | Null | Key | Default| Extra +---+---+--+-+++ | status | int(6) | YES || NULL | +---+---+--+-+++ Is there a syntax equivalent of 'describe' that can show me the references? thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
SHOW CREATE TABLE providers\G - Original Message - From: Tim Johnson [EMAIL PROTECTED] To: MySQL General Mailing List mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 2:59:35 AM (GMT-0500) Auto-Detected Subject: Retrieving foreign keys and references Hi: Below is the following relevant create table syntax CREATE Table providers( [snipped] FOREIGN KEY (status) REFERENCES provider_status(ID,title), UNIQUE KEY ID (ID) ) TYPE=MyISAM; and below is the relevant output from a describe query: mysql show columns from providers; +---+---+--+-+++ | Field| Type | Null | Key | Default| Extra +---+---+--+-+++ | status | int(6) | YES || NULL | +---+---+--+-+++ Is there a syntax equivalent of 'describe' that can show me the references? thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- 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: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:01, Rolando Edwards wrote: SHOW CREATE TABLE providers\G Hi Rolando: That doesn't do it on my machine (linux, ver 4.0.2) Here's what I see mysql SHOW CREATE TABLE providers\G *** 1. row *** Table: providers Create Table: CREATE TABLE `providers` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '**', `nick_name` varchar(10) NOT NULL default '**', `email_address` varchar(80) NOT NULL default '**', `start_date` date NOT NULL default '-00-00', `company_ID` int(11) NOT NULL default '0', `status` int(6) default NULL, `modified` timestamp(10) NOT NULL, UNIQUE KEY `ID` (`ID`) ) TYPE=MyISAM 1 row in set (0.00 sec) As you can see there is no reference info for column `status` thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
SHOW INDEXES FROM `providers`; By the way, what version of MySQL are you using ??? - Original Message - From: Tim Johnson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 3:35:46 AM (GMT-0500) Auto-Detected Subject: Re: Retrieving foreign keys and references On Wednesday 21 February 2007 17:01, Rolando Edwards wrote: SHOW CREATE TABLE providers\G Hi Rolando: That doesn't do it on my machine (linux, ver 4.0.2) Here's what I see mysql SHOW CREATE TABLE providers\G *** 1. row *** Table: providers Create Table: CREATE TABLE `providers` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '**', `nick_name` varchar(10) NOT NULL default '**', `email_address` varchar(80) NOT NULL default '**', `start_date` date NOT NULL default '-00-00', `company_ID` int(11) NOT NULL default '0', `status` int(6) default NULL, `modified` timestamp(10) NOT NULL, UNIQUE KEY `ID` (`ID`) ) TYPE=MyISAM 1 row in set (0.00 sec) As you can see there is no reference info for column `status` thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- 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: Growing innodb size
+---++ | Variable_name | Value | +---++ | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | /var/lib/mysql/| +---++ There is one defined, since I have innodb tables in there, very small ones. But the one I need know will grow easily to 5GB of data and will archive rows that are 30+ days old once every week. Since I am not running out of space on the machine, I though I could give 10GB to the datafile that will contain this new table... Should I do something like ? innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M Will this keep the data in ibdata1 intact ? Is it good to create a separate tablespace like this ? -Original Message- From: Nils Meyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 11:33 AM To: mysql@lists.mysql.com Subject: Re: Growing innodb size Hi, Jean-Sebastien Pilon wrote: I would like to grow my innodb table space, the only problem that I have is that I did not declare any size in the config file since we were not using it to start with. If I modify the config file, will this override the current innodb file or will it grow it ? Or should dump all the data, modify the config file and re-import everything ? Chances are that innodb isn't even functional if you did not specify a tablespace any way. You can check with: mysql SHOW VARIABLES LIKE LIKE 'innodb_data%'; If there is a data path given you'll see how it has been set up. Do you want a fixed size or should it grow automatically? Anyways, you might want to check this documentation: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et nest destiné qu'à la personne à qui il est adressé. Si vous nêtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement lexpéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt derreur puisque les informations quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger
I keep getting this error ERROR 1415 (0A000): Not allowed to return a result set from a trigger --- Rolando Edwards [EMAIL PROTECTED] wrote: insert into max_donations (donation_id, contact_id) select donation_id, contact_id from donation_test where conatct_id = (You missppelled contact_id) NEW.contact_id and total_amount = contact_max_amount; - Original Message - From: Winn Johnston [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 11:26:42 AM (GMT-0500) Auto-Detected Subject: trigger Any way to get this to work inside an after update trigger? insert into max_donations (donation_id, contact_id) select donation_id, contact_id from donation_test where conatct_id = NEW.contact_id and total_amount = contact_max_amount; thanks winn Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
Hi Tim, Foreign key definitions are parsed but ignored by MyISAM tables. Try InnoDB or PBXT (http://www.primebase.com/xt) :) On Feb 21, 2007, at 9:35 AM, Tim Johnson wrote: On Wednesday 21 February 2007 17:01, Rolando Edwards wrote: SHOW CREATE TABLE providers\G Hi Rolando: That doesn't do it on my machine (linux, ver 4.0.2) Here's what I see mysql SHOW CREATE TABLE providers\G *** 1. row *** Table: providers Create Table: CREATE TABLE `providers` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '**', `nick_name` varchar(10) NOT NULL default '**', `email_address` varchar(80) NOT NULL default '**', `start_date` date NOT NULL default '-00-00', `company_ID` int(11) NOT NULL default '0', `status` int(6) default NULL, `modified` timestamp(10) NOT NULL, UNIQUE KEY `ID` (`ID`) ) TYPE=MyISAM 1 row in set (0.00 sec) As you can see there is no reference info for column `status` thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:43, Rolando Edwards wrote: SHOW INDEXES FROM `providers`; By the way, what version of MySQL are you using ??? 4.0.20 -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row count inconsistency
At 10:34 AM -0600 2/21/07, Gerald L. Clark wrote: Marty Landman wrote: The table was created and then loaded and not modified in any way I'm aware of afterwards. It's on a local, only accessible by me server. Really weird thing about it is that I wrote/ran a program specifically to find any gaps in the id sequence - because of the size of the table it took days to run but the result was 1-100537311 IOW it confirms the max id that Mysql gave, but also indicates that there are no gaps in the row id's all the way through. This doesn't make sense to me in light of Mysql reporting the count as posted previously i.e. mysql select count(*) from fidcid; +---+ | count(*) | +---+ | 100480507 | +---+ 1 row in set (0.09 sec) If this table is InnoDB, then count(*) is just an approximation. -- Gerald L. Clark Supplier Systems Corporation select count(*), as well as other functions like max(), min() etc should be accurate regardless of table type; it's the 'show table status' report that may be inaccurate for Innodb: http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html Going back to the original problem: What is the table type MySQL version? Also, if you drop the auto_increment column and recreate it (on a copy of the original table, if necessary), are these results repeatable? Also, if the server has been shutdown improperly, there may be table corruption: MyISAM tables: http://dev.mysql.com/doc/refman/5.0/en/myisam-table-problems.html InnoDB problems: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html steve PS. This may be an obvious question, but: are you sure data loading was finished before running the select count(*) and select max(id) queries? -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch
Yes they are; and 22 = 3 - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'Bryan S. Katz' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 10:30 AM Subject: RE: MySQL Daylight Savings Time Patch Aren't the time zone tables new in 4.1.3? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Bryan S. Katz [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 9:03 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch Same exact issue on v5.0.27. I installed another windows patch, that did nothing, but then changed my system clock to march 12th, and then back to present day. Now the DST shifts are correct in the future and incorrect in the past. I've destroyed the timezone tables, and they made no difference. Which means that they were never being used. Any ideas? - Original Message - From: Bryan S. Katz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 20, 2007 7:50 PM Subject: MySQL Daylight Savings Time Patch Running 4.1.22, on windows 98, I'm having trouble getting the time zone tables to actually work. I've loaded the tables as per: http://dev.mysql.com/downloads/timezones.html, and followed the diagnostics as per: http://lists.mysql.com/mysql/205115 . It appears that my results from said diagnostic indeed prove that my tables are correct. However, I don't see those tables being used. The following statements yield varying results on my linux/windows machines (I run about ten servers). SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-4-1 00:00:00') + 2*60*60),'%H'); SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-3-11 00:00:00') + 2*60*60),'%H'); SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-4-2 00:00:00') + 2*60*60),'%H'); SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-3-12 00:00:00') + 2*60*60),'%H'); The linux machine -- with empty timezone tables is correct. 2007-3-11 and 2006-4-2 result in midnight plus 2 hours being 3am -- correct in the past and correct in the future. On my modern XP machines, the future is correct and the past is incorrect. On my older XP/98 machines, the past is correct and the future is incorrect. It is on my 98 server that I am now playing, having loaded timezone tables, and going crazy. I have upgraded my win98 timezones via a registry update. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:51, Paul McCullagh wrote: Hi Tim, Hello Paul Foreign key definitions are parsed but ignored by MyISAM tables. Understood. Thanks Try InnoDB or PBXT (http://www.primebase.com/xt) :) for the time being, I'm going to stick with MyISAM. I've got a possible solution I will try later - and the target is a online accounting system for a *very* small company (mine) with a *very* small number of clients. Here we go - it's weird, but it might work. I use a default value, which is itself a foreign key that point to a meta-table example status int(6) default 1, 1 is the ID for a table with columns `ID` 'descriptor`, where the desciptor field holds something like this provider_status.ID.title the script executes the show cols query, then queries the metadata table where provider_status.ID.title tells the script to pull `ID' and `title' from provider_status and load the values into a select/option list form entity. I'm sure that this will not be optimal performance-wise, but where speed is not an issue, will save much coding time. Since it will be the weekend before I try this out, I welcome observations and any references to meta-data approaches using MySQL /MyISAM. Thanks to everybody for the quick responses. regards -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA Alaska Internet Solutions (2 hairy guys in log cabins) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My.cnf and my.ini
I¹m running MAMP (www.mamp.info) on my Mac OSX box as a local way of developing. I¹m running into a slight problem that the mamp site doesn¹t talk about. The 1.5 beta1 seems to have the option skip-innodb enabled and I can¹t seem to find out where this is located to disable it and enable the use of innodb tables. There is no my.cnf or my.ini file and the command to start MySQL is: /Applications/MAMP/Library/bin/mysqld_safe --port=8889 --socket=/Applications/MAMP/tmp/mysql/mysql.sock --lower_case_table_names=0 --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --log-error=/Applications/MAMP/logs/mysql_error_log I was wondering what I can do to enable innodb? The only thing I can think of is that the people who make MAMP have disabled innodb when they compiled MySQL in which case am I out of luck? Thanks.
Re: unauthenticated user
Would it be possible to prevent reverse lookup? Im using the latest GA version.. thanks, On Wednesday 21 February 2007 23:12, Nils Meyer wrote: Hi JM, JM wrote: i got this results from show processlist | 11186 | unauthenticated user | 192.168.1.106:36198 | | Connect | | NULL | login | | | 11187 | unauthenticated user | 192.168.1.106:36200 | | Connect | | NULL | login | | That's just fine. The user is in login state, so he/she/it is not yet authenticated. There might be a DNS issue slowing down login, you might check if you can reverse lookup the hostnames of the connecting users. regards Nils Mailing-List -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table - adding constraints?
Chris White wrote: Jay Paulson wrote: 2) both tables have data in them. This is most likely your issue then, depending on the table size, go through and make sure that anything in the referenced column matches the referencing column. You should also be able to use SHOW INNODB STATUS to see what's possibly failing. No, it's because you already have a constraint in your schema called fk_regions. Just change the constraint name to one that is unique for your schema... This is the reason I usually name myu constraints like: fk_fromtable_totable So this doesn't become an issue. cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Triggers to Maintain a Table to prevent complex join statements...
Cory Robin wrote: We have a statement that joins 8 different tables to pull multiple rows from each table. I have heard of people using triggers to create, update and delete records in a table based on statements in other tables. The obvious result of this would be to SPEED up results right? :) I'd love to find someone with experience with this that I can pick info from. lol Post the SQL you are using and I'll give you a hand. Cheers! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]