Problem with join syntax
Hi there, I have some trouble with the syntax of join query and some perfomance isues. I did split my data due to the rules of normalisation. So there is a website table and a comment table and I would like to find out if the user with the id '10215' has a stored comment or website. Here is my stmt: SELECT w.website, c.comment FROM user_websites w LEFT JOIN user_comments AS c ON c.user_id = '10215' WHERE w.user_id = '10215' So what if comments contains 1 million entries. Or what if I have 10 such tables and would have to make a left join on each of them? I fear that the performance would suffer. is there a better way to do this in one stmt? Thanx for any help, Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [Fwd: Add existing related data to a table]
Thanks for this response, it is one way round the problem but is going to require copying the tables, some of them very large a number of times over and over. Is their no way of simply adding the data alongside the existing columns? Alex -- Alexander Shaw Agricultural Stock and Assignment Photography -Original Message- From: Sabine Richter [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 23:12 To: [EMAIL PROTECTED] Subject: [Fwd: Add existing related data to a table] Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hello Alexander, your problem is near to Taylor Lewick's and I have a similar suggestion for you: 1) create newtab select tableA.*, tableB.SubcatID from tableA.*, tableB where ... 2) delete tablea 3) rename table newtab to tablea If there are more rows in tableB (1:n), you don't loose data. The data of tabA will be dublicated. Ok, that are 3 statements. Bye Sabine Alexander Shaw wrote: Hi, I'm looking for some suggestions of making a one time update to some of my tables to make them useable with new forms and queries I'm creating. I have a series of tables which hold related data and only the last part of the data is stored in a final table, which means to search data based on some of the information higher up the table structure is making some very complex and dodgy queries. I want to insert the related data into the final table but can't think of a way of doing it. The example below might help me explain better what I mean. Consider two tables - TableA which contains PicID and KeywordID TableB which contains SubcatID and KeywordID What I want to do is insert the corresponding values for relating to KeywordID for SubCatID into a new column in TableA. Any suggestions how to work this to add the extra information to existing records? Alex -- Alexander Shaw Agricultural Stock and Assignment Photography p.s. to get throught he spam trap - sql query --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANNOUNCE: moodss-16.1
### CHANGES ### --- version 16.1 --- - in Edit menu, added New Page entry, renamed New entry to New Viewer - allowed page deletion through drop and drop from page tab into eraser, only if page is empty or if it is the last remaining page - when a new page is created, automatically open its tab label for editing, and display a short help text in message area - in preferences dialog box, added canvas pages section where use can choose if pages tabs are positioned on the top side or on the bottom side - viewers circulation using Tab and Shift-Tab keystrokes is now scoped to current page - when editing a page tab label, initially select all characters in entry ### README ### This is moodss (Modular Object Oriented Dynamic SpreadSheet) version 16.1. Moodss won in the Best System Admin Technology category (Tcl Tips and Tricks, Valuable Real World Programming Examples) at the O'Reilly Tcl/Tk 1999 Conference. Linux Magazine calls it a lifesaver. Tucows gives it 5 stars (cows or penguins :-). Moodss is a modular application. It displays data described and updated in one or more modules, which can be specified in the command line or dynamically loaded or unloaded while the application is running. Data is originally displayed in tables. Graphical viewers (graph, bar, 3D pie charts, ...), summary tables (with current, average, minimum and maximum values) and free text viewers can be created from any number of table cells, originating from any of the displayed viewers. The display area can be extended by adding pages with notebook tabs. Thresholds can be set on any number of cells. Moomps (shipped with moodss) is a monitoring daemon which works using configuration files created by moodss. Thresholds, when crossed, create messages in the system log, and eventually trigger the sending of email alert messages. Specific modules can easily be developed in the Tcl, Perl and Python scripting languages or in C. A thorough and intuitive drag'n'drop scheme is used for most viewer editing tasks: creation, modification, type mutation, destruction, ... and thresholds creation. Table rows can be sorted in increasing or decreasing order by clicking on column titles. The current configuration (modules, tables and viewers geometry, ...) can be saved in a file at any time, and later loaded at the user's convenience, thus achieving a dashboard functionality. The module code is the link between the moodss core and the data to be displayed. All the specific code is kept in the module package. Since module data access is entirely customizable (through C code, Tcl, Perl, Python, HTTP, ...) and since several modules can be loaded at once, applications for moodss become limitless. For example, thoroughly monitor a dynamic web server on a single dashboard with graphs, using the Apache, MySQL, ODBC, cpustats, memstats, ... modules. If you have replicated servers, dynamically add them to your view, even load the snmp module on the fly and let your imagination take over... Along with a core trace module, arp, cpustats, diskstats, interrupts, kernmods, memstats, minimal, mounts, netdev, pci, ps, random, route, sensors, system modules for Linux, MySQL (myerrorlog, myhealth, myprocs, myquery, myreplication, mystatus, myvars), odbcquery, ping, snmp, snmptrap for UNIX, apache and apachex, Minimal and Random Perl modules, minipy and randpy Python modules are included. Running wish moodss ps cpustats memstats mimics the top application with a graphic edge and remote monitoring capability. Thorough help is provided through menus, widget tips, a message area, a module help window and a global help window with a complete HTML documentation. Moodss is multi-langual thanks to Tcl internationalization capabilities. So far only English and partially French are supported. Help with other languages will be very warmly welcomed. Development of moodss is continuing and as more features are added in future versions, backward module code compatibility will be maintained. I cannot thank the authors of the tkTable, BLT, MIME/SMTP and the HTML libraries enough for their great work. In order to run moodss, you need to install the following packages (unless you can use the rpm utility, see below): Tcl/Tk 8.3.1 or above, at (or at a mirror near you) http://dev.scriptics.com/ or ftp://ftp.scriptics.com/ the latest tkTable widget library at: http://tktable.sourceforge.net/ the latest BLT library at: ftp://tcltk.sourceforge.net/pub/tcltk/blt/ eventually the latest tclperl library for writing modules in Perl, or the latest tclpython library for writing modules in Python at: http://jfontain.free.fr/ (see the INSTALL file for complete instructions, for UNIX and also Windows platforms). You also have the option of using the moodss rpm file (also in my homepage), if you are using a Redhat Linux system (6.0 or above). You can find the required tcl, tk, tktable, blt, tcpperl and other rpms at: http://jfontain.free.fr/ Whether you like it (or
Re: [Fwd: Add existing related data to a table]
Hi, My opinion: If you design your DataBase in correspondence with Boyce-Codd rules i think you can avoid all this complications. Good luck, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Alexander Shaw [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 12:53 PM Subject: RE: [Fwd: Add existing related data to a table] Thanks for this response, it is one way round the problem but is going to require copying the tables, some of them very large a number of times over and over. Is their no way of simply adding the data alongside the existing columns? Alex -- Alexander Shaw Agricultural Stock and Assignment Photography -Original Message- From: Sabine Richter [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 23:12 To: [EMAIL PROTECTED] Subject: [Fwd: Add existing related data to a table] Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hello Alexander, your problem is near to Taylor Lewick's and I have a similar suggestion for you: 1) create newtab select tableA.*, tableB.SubcatID from tableA.*, tableB where ... 2) delete tablea 3) rename table newtab to tablea If there are more rows in tableB (1:n), you don't loose data. The data of tabA will be dublicated. Ok, that are 3 statements. Bye Sabine Alexander Shaw wrote: Hi, I'm looking for some suggestions of making a one time update to some of my tables to make them useable with new forms and queries I'm creating. I have a series of tables which hold related data and only the last part of the data is stored in a final table, which means to search data based on some of the information higher up the table structure is making some very complex and dodgy queries. I want to insert the related data into the final table but can't think of a way of doing it. The example below might help me explain better what I mean. Consider two tables - TableA which contains PicID and KeywordID TableB which contains SubcatID and KeywordID What I want to do is insert the corresponding values for relating to KeywordID for SubCatID into a new column in TableA. Any suggestions how to work this to add the extra information to existing records? Alex -- Alexander Shaw Agricultural Stock and Assignment Photography p.s. to get throught he spam trap - sql query --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.360 / Virus Database: 199 - Release Date: 07/05/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
Re: question about differences with mysql on windows and unix
Norris, Saturday, May 18, 2002, 12:19:33 AM, you wrote: Neoen On my unix box when I use mysql I can spawn vi and edit the sql statement or Neoen save it out for reuse. Neoen This feature does not appear to be present on the windows version - or did I Neoen miss something? I can use it on Windows, too. Save your statements in the text file using Notepad or other text editor and then use is as on *nix MySQL. But don't forget that end-of-line differs on *nix and windows. Neoen Thanks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: How do I copy over a new database?
Doug, Saturday, May 18, 2002, 6:45:20 AM, you wrote: DC Please forgive this easy question from a newbie: DC I was given a MySQL database file from a client with a .sql extension. I DC already have several databases setup and running under MySQL on our server. DC But how would I copy this new database over? Do I need to load it or can I DC copy it as is into a directory. If it's a dump file you should create database with mysqladmin or using CREATE DATABASE statement: http://www.mysql.com/doc/m/y/mysqladmin.html http://www.mysql.com/doc/C/R/CREATE_DATABASE.html and then you should load data into your database from the file mysql database_name file_name.sql DC Please advise. DC Thanks in advance! DC Doug Coning -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql Client fails to connect
Federico, Friday, May 17, 2002, 8:23:35 PM, you wrote: FC After installing MySql Client Version 1.75 on my Win98 Pc, I tried to FC connect to mysql database on a Linux Box. FC this is the error message FC Host not allowed to connect to this MySQL server. FC This is my configuration file [skip] FC I don't know if I have to configure something else on windows or the linux FC server to connect? You should allow to connect from your host for your user. Look at: http://www.mysql.com/doc/A/c/Access_denied.html FC TIA. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM - Innodb
Hi! I am getting real interested too. :) I took a look at the manual, but I did not see answers to a simple question like whether innodb files are compatible (query-wise) with MyISAM tables, for instance, or whether I need to rewrite all queries. Perhaps a guru here could explain this. Yes, InnoDB tables are query-wise identical to MyISAM tables. You can also mix different table types in a single SQL statement, for example a join. In performance there is some fluctuation due to different optimizations. Also, InnoDB tables take more disk space, and COUNT(*) of a table is slower, because InnoDB does not store the row count internally. - Mark Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
VC++6.0 MFC app with MyODBC 2.50 no cursor name avb.
I get a No cursor name available window in an Visual C++ 6.0 MFC application whenever i try to make updates to the database. I use a CRecordset class to connect to the database trough MyODBC 2.50.39. MySQL server is 3.23.49 stable release on Windows 2000 Pro. I'm kinda new in the area of MySQL. If someone could help me it would get me out of a lot of trouble. Thanks ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
×îа汾JSP¡¢PHPÐéÄâÖ÷»ú£¬300ÔªÆð¾ÍÄÜÓµÓÐ!(Ãâ·ÑÊÔÓÃ)
×𾴵Ŀͻ§£¬ÄúºÃ£¡ ʱ´´ÍøÂçJSP PHPÐéÄâÖ÷»úÈ«ÃæÉý¼¶³É¹¦£¬Ö§³Ö×î¸ß°æ±¾£¬¸øÓè ¸ü¶àÖ§³Ö£¬¸ßËÙ¡¢Îȶ¨£¡Êǽ¨Á¢ÆóÒµ¡¢¸öÈËÍøÕ¾µÄ×î¼ÑÑ¡Ôñ: ²ÉÓÃ×îÐÂCatalina ÄÚºË Ö§³ÖServlet 2.3 and JSP 1.2 JDBC mm.mysql-2.0.12 JDBC microsoft.jdbc.sqlserver J2SDK1.4.0 ÖйúµçÐÅ2GÖ±½Ó½ÓÈëCHINANETÖ÷¸É! Ö§³Ö¿Õ¼äÊýÐÍ: 5È˺Ï×âÐÍ ¡¢ÐéÄâרÓÃÐÍB ¡¢ÐéÄâרÓÃÐÍD Ö§³ÖJSP (+Javamail1.2) ¶ÀÁ¢CGI-BIN : 300Ôª/Äê=50M¿Õ¼ä£«100MÓÊÏä 1000Ôª/Äê=100M¿Õ¼ä£«300MÓÊÏ䣫MYSQL(15M) 1800Ôª/Äê=250M¿Õ¼ä£«800MÓÊÏ䣫MYSQL JDBC(50M) 2300Ôª/Äê=250M¿Õ¼ä£«800MÓÊÏ䣫SQLSERVER 2000(50M) 4300Ôª/Äê=3G¿Õ¼ä£«2000MÓÊÏ䣫MYSQL(¿Õ¼ä²»ÏÞ)£«Ö§³Ö¶àWEB-CONTEXT Ö§³ÖManager¡¢ÍøÕ¾ÖØÆð¡¢Ôö¼ÓеÄweb applicationµÈµÈËùÓи߼¶¹¦ÄÜ Ö§³ÖPHP4(´ø1.2¼ÓËÙÆ÷) ¶ÀÁ¢CGI-BIN : 300Ôª/Äê=100M¿Õ¼ä£«100MÓÊÏä 800Ôª/Äê=150M¿Õ¼ä£«300MÓÊÏ䣫MYSQL(15M) 1300Ôª/Äê=250M¿Õ¼ä£«800MÓÊÏ䣫MYSQL(50M) 2300Ôª/Äê=250M¿Õ¼ä£«800MÓÊÏ䣫SQLSERVER 2000(50M) 3800Ôª/Äê=3G¿Õ¼ä£«1500MÓÊÏ䣫MYSQL(¿Õ¼ä²»ÏÞ) 4500Ôª/Äê=3G¿Õ¼ä£«2000MÓÊÏ䣫SQL SERVER 2000(¿Õ¼ä²»ÏÞ)£«Ö§³Ö¶àWEB-CONTEXT Ö§³ÖManager¡¢ÍøÕ¾ÖØÆð¡¢Ôö¼ÓеÄweb applicationµÈµÈËùÓи߼¶¹¦ÄÜ Ãâ·ÑÊÔÓã¬ÂúÒâºóÔÙ½»·Ñ£¡ÎÒÃÇ»¹±£Ö¤ÊÔÓõĿռäÓëʹÓõĿռä¾ø¶ÔÊÇͬһƷÖÊ! »¶ÓÄú·ÃÎÊToday's Network http://www.todaynic.com 0756--2125593 2125523 2125583 2125563 0756--2236575 2125594 2216376 QQÔÚÏß×Éѯ£º107929341 108786612 ¡î¡î´øVDNS×¢²áÓòÃû£¬¿ÉÒÔ²úÉú´Î¼¶ÓòÃû£¬¹¦ÄܸúWWWÒ»ÑùÇ¿´ó£¡ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie mysql install
Installed MySQL using Fink to the Mac X OS. When attempting to run, I am getting this error. ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' Help in explaining this error and how to fix the installation would be appreciated. Thanks in advance. Jeffrey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing UTF-8 text with MM.MySQL 2.0.13 driver.
I am trying to store UTF-8 encoded text with MM.MySQL 2.0.13 driver. I am
using MySQL 3.23.49, MM.MySQL 2.0.13 JDBC driver, j2sdk1.4.0.
I have had success with the code below (which stores the UTF-8 as a binary
stream), but am wondering if there is an easier way. Specifically I am
wondering about the MM.MySQL useUnicode and characterEncoding params. The
code below works regardless how you set these params.
Do useUnicode and characterEncoding allow you to store UTF-8 with regular
.setString() methods? What exactly do they do? I can't find any
explanation more than:
useUnicode should the driver use Unicode character encodings when handling
strings? (true/false) false
characterEncoding if useUnicode is true, what character encoding should the
driver use when dealing with strings? none
Is setBinaryStream() the only way to store UTF-8?
Thank you very much in advance!!!
John D. Stein
// Store a unicode character (u) in the database and see if what you get
// back from the database (urc) equals the original string you put in.
public void testBinaryUTF8() throws Exception {
String u = \u65E5;
byte[] ua = u.getBytes(UTF-8);
ByteArrayInputStream is = new ByteArrayInputStream(ua);
String updateSQL = UPDATE sp_i18n_dev.lengthtest SET n255 = ? WHERE
id = 1;
String selectSQL = SELECT n255 from sp_i18n_dev.lengthtest WHERE id =
1;
Connection conn = DataSource.getConn(dsName);
PreparedStatement pst = conn.prepareStatement(updateSQL);
pst.setBinaryStream(1, is, ua.length);
pst.execute();
pst = conn.prepareStatement(selectSQL);
ResultSet rs = pst.executeQuery();
rs.next();
BufferedReader brin = new BufferedReader(new
InputStreamReader(rs.getBinaryStream(1), UTF-8));
String urc = brin.readLine();
System.out.println(
Original string equals string from db: +
u.equals(urc));
DataSource.cleanup(conn, dsName, pst, rs);
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE-ing a table with keys to a related table?
Hi, hope somebody can help me - what I want to do is simple I'm sure, but I can't work out how to do it: I want to replace a column in a table, containing text, with a key to another new table which holds the text. Example: Given a table, 'people', which contains two columns, name, and town, like this: name town ---- fred london joliverpool amy chicago mary chicago I create a new table, towns: townidtown -- 1 london 2 liverpool 3 chicago So far good. Now I want to add a townid column to 'people', and update to point to the corresponding row in 'towns'. I add a new column, townid, but then how do I update the values?? I have tried both of these: UPDATE people SET people.townid = towns.townid WHERE people.town=towns.town; and UPDATE people INNER JOIN towns ON people.town = towns.town SET people.townid = towns.townid; mysql gives syntax errors in both cases (both work ok with access 2000). Is there a way to do this without creating temporary intermediate tables? Any help greatly appreciated! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: VC++6.0 MFC app with MyODBC 2.50 no cursor name avb.
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Saturday, May 18, 2002 5:17 AM To: [EMAIL PROTECTED] Subject: VC++6.0 MFC app with MyODBC 2.50 no cursor name avb. I get a No cursor name available window in an Visual C++ 6.0 MFC application whenever i try to make updates to the database. I use a CRecordset class to connect to the database trough MyODBC 2.50.39. MySQL server is 3.23.49 stable release on Windows 2000 Pro. I'm kinda new in the area of MySQL. If someone could help me it would get me out of a lot of trouble. Thanks ! Check the list archive .. http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:5441:200205:nkckmapjdedbik boiogc Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyISAM - IN
Hi, I'm converting my tables from MyISAM to InnoDB. One of them returns an error Error: 1114 - The table '#sql-7b67_321' is full - How can i avoid this error? Table Structure- wmkt_client,CREATE TABLE `wmkt_client` ( `idclient` int(11) NOT NULL auto_increment, `realname` varchar(80) NOT NULL default '', `fkemail` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `dtLastUp` datetime default NULL, `fkuser` int(11) NOT NULL default '1', PRIMARY KEY (`idclient`), KEY `ixFkemail` (`fkemail`) ) TYPE=MyISAM with 220945 records Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] (19) 3256-3577 ICQ 136192234 sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BookMark
Sorry, my mistake in not explaining in details I have problem on 'Multiple-step operation generated errors. Check each status value'. when i use Visual Basic ADODC OCX to addnew. This is the sample of my source code. The problem crop up when the bookmark value 1. againxxx1: Form1.DtaOut.Recordset.AddNew ' MsgBox Form1.DtaOut.Recordset.Bookmark If Form1.DtaOut.Recordset.Bookmark 1 Then Form1.DtaOut.Recordset.CancelUpdate Form1.DtaOut.Recordset.Close Form1.DtaOut.Recordset.Open Form1.DtaOut.RecordSource = Select * from GblOutDB Where id = 1 Form1.DtaOut.Refresh GoTo againxxx1 'Form1.DtaOut.Recordset.Bookmark = 1 End If Form1.DtaOut.Recordset(aDate) = Format(Date, mm/dd/) Format(Time, hh:mm:ss ampm) Form1.DtaOut.Recordset(method) = 2 Form1.DtaOut.Recordset(sp) = Gblsp Form1.DtaOut.Recordset(mobile) = Form1.DtaIn.Recordset(Sender) Form1.DtaOut.Recordset(Sender) = Form1.DtaIn.Recordset(Sender) Form1.DtaOut.Recordset(try) = 0 Form1.DtaOut.Recordset(member) = GblMember Form1.DtaOut.Recordset(Serviceid) = GblServiceID Form1.DtaOut.Recordset(msgid) = Form1.DtaIn.Recordset(msgid) Form1.DtaOut.Recordset(Priority) = GblPriority Form1.DtaOut.Recordset(Status) = Normal Form1.DtaOut.Recordset(smstype) = Gblsmstype Form1.DtaOut.Recordset(aMT) = GblaMT Form1.DtaOut.Recordset(smsfile) = Form1.DtaIn.Recordset(smsfile) Form1.DtaOut.Recordset(msg) = GblError1 Form1.DtaOut.Recordset.Update At 06:03 AM 5/17/2002 -0500, Gerald R. Jensen wrote: Bookmarking?! No, MySQL does not support bookmarking ... it is relational database manager, not a word processor. All you have to do is save the query you run, and it will return the same results. - Original Message - From: SB Lim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 17, 2002 5:42 AM Subject: BookMark I am using VB6 with MYSQL, Is MYSQL support Bookmarking ? How can I implement that on the MYSQL ? Regards SB Lim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.0.1 Bugs
List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. Ric. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database and all backups lost at same time
This is an odd one. I seem to have lost all databases and backups simultaneously. I keep daily backups for upto 7 days. This morning after a power outage I noted I was unable to login. Every user and password I tried was denied. I checked the files in the mysql data folder and they were all there and seemed fine. They matched exactly to those in the past 7 days of backups. I even tried to restore from each of the 7 days of backups with no result. I removed all the files in the data/mysql/ folder and restarted mysql again. I was able to login as root with no password but found every other database I tried to use came back with 'ERROR 1049: Unknown database'. I also checked these files and they are all there. I am now able to 'use mysql' since it is empty. Any ideas on what may have happened. Since it seems to affect all databases and all backups I have to believe there is some corruption within the mysql server itself and not the data files. -Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: 4.0.1 Bugs
Fra: Richard Clarke [mailto:[EMAIL PROTECTED]] Sendt: 18. maj 2002 22:29 Emne: 4.0.1 Bugs List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. May we see a) your queries and b) the tables involved? Without such information it would just be a guessing game... - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.1 Bugs
1) CREATE TABLE `summary_rts` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB CREATE TABLE `summary_rts_old` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB create table rank_union select * from summary_rts union select * from summary_rts_old; The error began to occur after the sum of rows was roughly 200,000. InnoDB free space was in the range of 4 gigs and disk space 2gigs. 2) Can't really give tables and queries here since they form a large set of statistical analysis queries. Only information I can helpfully give is that the same set of roughly 30 queries is run every 5 seconds 24/7. After about 5-7 days the db server segv's on both our 4.4-release machines. Ric. - Original Message - From: Carsten Gehling [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; MYSQL [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 9:48 PM Subject: SV: 4.0.1 Bugs Fra: Richard Clarke [mailto:[EMAIL PROTECTED]] Sendt: 18. maj 2002 22:29 Emne: 4.0.1 Bugs List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. May we see a) your queries and b) the tables involved? Without such information it would just be a guessing game... - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database and all backups lost at same time
Not a direct answer to your question here, but why keep backups on the same server and in the same storage medium? Ric. - Original Message - From: kibserv-FtS Setiqueue [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 9:44 PM Subject: Database and all backups lost at same time This is an odd one. I seem to have lost all databases and backups simultaneously. I keep daily backups for upto 7 days. This morning after a power outage I noted I was unable to login. Every user and password I tried was denied. I checked the files in the mysql data folder and they were all there and seemed fine. They matched exactly to those in the past 7 days of backups. I even tried to restore from each of the 7 days of backups with no result. I removed all the files in the data/mysql/ folder and restarted mysql again. I was able to login as root with no password but found every other database I tried to use came back with 'ERROR 1049: Unknown database'. I also checked these files and they are all there. I am now able to 'use mysql' since it is empty. Any ideas on what may have happened. Since it seems to affect all databases and all backups I have to believe there is some corruption within the mysql server itself and not the data files. -Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
random order by id
Hey guys, i have a db and a table with id and questions now i want these questions to be listed in a random order, is there a way to format my SQL query or do i need some PHP work to? i can ofcourse select a random one, that's no problem, but how do i make sure that that one does not get chosen again. and that after it has printed all the records it stops any ideas? Jule -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED]| | http://blindtheory.cjb.net | | __ | |/\/ \/\| - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP] random order by id
On Saturday 18 May 2002 18:00, you wrote: yeah i tried doing that in phpMyAdmin, and it gave me an error and when i use SELECT * FROM 4172_questions ORDER BY RAND() LIMIT $row ($row being the number of rows)as my query it gives me the: Warning: Supplied argument is not a valid MySQL result resource in /home/blindtheory/web/quiz/take_quiz/take_quiz_question_1.php on line 15 error. Jule. On Saturday 18 May 2002 17:46, you wrote: If you are using MySQL try looking at this page for the function rand(): http://www.mysql.com/doc/M/a/Mathematical_functions.html Here's a little overview of what it says: In MySQL Version 3.23, you can, however, do: SELECT * FROM table_name ORDER BY RAND() This is useful to get a random sample of a set SELECT * FROM table1,table2 WHERE a=b AND cd ORDER BY RAND() LIMIT 1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed. RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version. Cheers! Rick Nobody will ever win the Battle of the Sexes. There's just too much fraternizing with the enemy. - Henry Kissinger From: Jule [EMAIL PROTECTED] Date: Sat, 18 May 2002 17:41:59 -0400 To: [EMAIL PROTECTED] Subject: [PHP] random order by id Hey guys, i have a db and a table with id and questions now i want these questions to be listed in a random order, is there a way to format my SQL query or do i need some PHP work to? i can ofcourse select a random one, that's no problem, but how do i make sure that that one does not get chosen again. and that after it has printed all the records it stops any ideas? Jule -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED] | | http://blindtheory.cjb.net | | __ | |/\/ \/\| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED]| | http://blindtheory.cjb.net | | __ | |/\/ \/\| - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
apparent bug
To Whom It May Concern: I have reported the following symptoms in UNIX using 'mysqlbug' character mode utility. I am including the text of that problem statement with this email. Additionally, I am attaching several sample DDL input files, and the results of SHOW COLUMNS FROM table name output. Voila, the mysqlbug problem text: *** SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: buff To: [EMAIL PROTECTED] Subject: DDL CREATE TABLE data format CHAR(x) compiles as VARCHAR(x) Description: My DDL files for CREATE TABLE statements contain no VARCHAR(x) data formats. However, when compiling all DDL for a given database. For some reason invisible to me, some of the columns whose data format is CHAR(x) end up being created as VARCHAR(x). The set of columns that mutate in this fashion is consistent, i.e., the same set of columns always get this error. However, the rime or reason for this set seems to be happenstance. That is: For a given dB name with a parallel set of DDL the same columns mutate to VARCHAR. In other words: I use DDL set A with dB name 1, DDL set B with dB name 2, etc., etc. NOTE: pair A1, B2, etc get consistent within pair, but consistently different between pair sets of VARCHAR columns. The string /VARCHAR/cannot be found via grep in any of my DDL scripts. I can email or FTP a DDL script. How-To-Repeat: I will email or FTP a set of DDL scripts Fix: NONE Submitter-Id: [EMAIL PROTECTED] Originator: Buff Organization: cynchrony.com hosted by interland.com MySQL A.B's customer is Interland MySQL support: interland.com says -- none Synopsis: CHAR(X) mutates in DDL compile varchar(x) Severity: critical Priority: medium Category: mysql Class: sw-bug Release:mysql-3.22.32 (Source distribution) Environment: FreeBSD cynchrony.com 4.1.1-STABLE FreeBSD 4.1.1-STABLE #0: Tue Aug 21 22:35:59i GMT 2001 root@osbuilder:/usr/obj/usr/src/sys/SERVER i386 MySql Version: +---+ | version() | +---+ | 3.22.32 | +---+ * end of mysqlbug problem statement text In preparing data for this email I noticed that it seems to happen for columns added after a certain period of time. Originally, the Question_Asked_Quantitative table did not have the Question_Subscale_Name column. Generation number 2 DDL version 09 of the trimergence_main database did not have the column, whereas generation number 3 DDL version 13 of the trimergence_main database added that and other columns. The current version of mysql is listed in the mysqlbug problem statement text, above I do not know which version mysql was when generation 2 of the database was created. attachments follow -- generation 09 show cols === Field TypeNullKey Default Extra Questionnaire_IDint(11) PRI 0 Question_Number int(11) PRI 0 Question_Subscale_Name varchar(32) PRI Question_Text textNULL Generation 09 DDL = use trimergence_main2; CREATE TABLE Customer_Authentication ( Customer_ID CHAR(30) NOT NULL, Customer_NameCHAR(128) NOT NULL, Company_SignUp_date DATE NOT NULL, Company_Expire_Date DATE NOT NULL, Customer_Validation_flag CHAR(1) NOT NULL, Contact_Person CHAR(128) NOT NULL, Contact_emailCHAR(128) NOT NULL, Customer_address_street CHAR(128), Customer_address_city CHAR(64), Customer_address_state_cd CHAR(2), Contact_area_codeCHAR(3) NOT NULL, Contact_phoneCHAR(8) NOT NULL, Customer_address_zipcode CHAR(10), Customer_URL_nameCHAR(128), PRIMARY KEY (Customer_ID) ); CREATE UNIQUE INDEX XPKCustomer_Authentication ON Customer_Authentication ( Customer_ID ASC ); CREATE TABLE Question_Asked_Qualitative ( Questionnaire_ID INTEGER NOT NULL, Question_Number INTEGER NOT NULL, Question_Asked_Text TEXT NOT NULL, Question_Answer_Text TEXT NOT NULL, PRIMARY KEY (Questionnaire_ID, Question_Number) ); CREATE UNIQUE INDEX XPKQuestion_Asked_Qualitative ON Question_Asked_Qualitative ( Questionnaire_ID ASC, Question_Number ASC ); CREATE TABLE Question_Asked_Quantitative ( Questionnaire_ID INTEGER NOT NULL, Question_Number INTEGER NOT NULL, Question_TextTEXT NOT NULL, PRIMARY KEY (Questionnaire_ID, Question_Number) ); CREATE UNIQUE
Re: apparent bug
Hi, this is a feature. see http://www.mysqldeveloper.com/manual/index.pcgi?section=Silent_column_changes ;-) Buff Smith wrote: To Whom It May Concern: I have reported the following symptoms in UNIX using 'mysqlbug' character mode utility. I am including the text of that problem statement with this email. Additionally, I am attaching several sample DDL input files, and the results of SHOW COLUMNS FROM table name output. Voila, the mysqlbug problem text: *** SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: buff To: [EMAIL PROTECTED] Subject: DDL CREATE TABLE data format CHAR(x) compiles as VARCHAR(x) Description: My DDL files for CREATE TABLE statements contain no VARCHAR(x) data formats. However, when compiling all DDL for a given database. For some reason invisible to me, some of the columns whose data format is CHAR(x) end up being created as VARCHAR(x). The set of columns that mutate in this fashion is consistent, i.e., the same set of columns always get this error. However, the rime or reason for this set seems to be happenstance. That is: For a given dB name with a parallel set of DDL the same columns mutate to VARCHAR. In other words: I use DDL set A with dB name 1, DDL set B with dB name 2, etc., etc. NOTE: pair A1, B2, etc get consistent within pair, but consistently different between pair sets of VARCHAR columns. The string /VARCHAR/cannot be found via grep in any of my DDL scripts. I can email or FTP a DDL script. How-To-Repeat: I will email or FTP a set of DDL scripts Fix: NONE Submitter-Id: [EMAIL PROTECTED] Originator: Buff Organization: cynchrony.com hosted by interland.com MySQL A.B's customer is Interland MySQL support: interland.com says -- none Synopsis: CHAR(X) mutates in DDL compile varchar(x) Severity: critical Priority: medium Category: mysql Class: sw-bug Release:mysql-3.22.32 (Source distribution) Environment: FreeBSD cynchrony.com 4.1.1-STABLE FreeBSD 4.1.1-STABLE #0: Tue Aug 21 22:35:59i GMT 2001 root@osbuilder:/usr/obj/usr/src/sys/SERVER i386 MySql Version: +---+ | version() | +---+ | 3.22.32 | +---+ * end of mysqlbug problem statement text In preparing data for this email I noticed that it seems to happen for columns added after a certain period of time. Originally, the Question_Asked_Quantitative table did not have the Question_Subscale_Name column. Generation number 2 DDL version 09 of the trimergence_main database did not have the column, whereas generation number 3 DDL version 13 of the trimergence_main database added that and other columns. The current version of mysql is listed in the mysqlbug problem statement text, above I do not know which version mysql was when generation 2 of the database was created. attachments follow -- generation 09 show cols === Field TypeNullKey Default Extra Questionnaire_IDint(11) PRI 0 Question_Number int(11) PRI 0 Question_Subscale_Name varchar(32) PRI Question_Text textNULL Generation 09 DDL = use trimergence_main2; CREATE TABLE Customer_Authentication ( Customer_ID CHAR(30) NOT NULL, Customer_NameCHAR(128) NOT NULL, Company_SignUp_date DATE NOT NULL, Company_Expire_Date DATE NOT NULL, Customer_Validation_flag CHAR(1) NOT NULL, Contact_Person CHAR(128) NOT NULL, Contact_emailCHAR(128) NOT NULL, Customer_address_street CHAR(128), Customer_address_city CHAR(64), Customer_address_state_cd CHAR(2), Contact_area_codeCHAR(3) NOT NULL, Contact_phoneCHAR(8) NOT NULL, Customer_address_zipcode CHAR(10), Customer_URL_nameCHAR(128), PRIMARY KEY (Customer_ID) ); CREATE UNIQUE INDEX XPKCustomer_Authentication ON Customer_Authentication ( Customer_ID ASC ); CREATE TABLE Question_Asked_Qualitative ( Questionnaire_ID INTEGER NOT NULL, Question_Number INTEGER NOT NULL, Question_Asked_Text TEXT NOT NULL, Question_Answer_Text TEXT NOT NULL, PRIMARY KEY (Questionnaire_ID, Question_Number) ); CREATE UNIQUE INDEX XPKQuestion_Asked_Qualitative ON Question_Asked_Qualitative ( Questionnaire_ID ASC, Question_Number ASC ); CREATE TABLE Question_Asked_Quantitative (
Re: Database and all backups lost at same time
Not to add insult to injury, but keeping backups on the same machine and media your databases are installed on is asking for trouble. And power outages are predictable, so UPS protection is critical. Enough said. I would speculate that one or more of the files in the /data/mysql/ folder were open for a write operation when the power went out, corrupting the file(s). Since the mysql database controls rights/privs for the other databases, problems with it could transcend to problems accessing the other DB's. We have a process in place to prevent data loss: - All database schema are installed via scripts, which are stored on CD-RW media. - All privileges on our MySQL servers are scripted. Once installed, these scripts are kept on the CD-RW with the database schema scripts. - Every morning at 2am, a cronjob creates a backup of each database (including mysql) - When the backups are safely created, the cronjob does an SSH connection to another server and ftp's the backups to the other machine. - The backups are moved to CD-RW This is not a bullet-prof plan, but it gets the job done for us. Gerald Jensen - Original Message - From: kibserv-FtS Setiqueue [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 3:44 PM Subject: Database and all backups lost at same time This is an odd one. I seem to have lost all databases and backups simultaneously. I keep daily backups for upto 7 days. This morning after a power outage I noted I was unable to login. Every user and password I tried was denied. I checked the files in the mysql data folder and they were all there and seemed fine. They matched exactly to those in the past 7 days of backups. I even tried to restore from each of the 7 days of backups with no result. I removed all the files in the data/mysql/ folder and restarted mysql again. I was able to login as root with no password but found every other database I tried to use came back with 'ERROR 1049: Unknown database'. I also checked these files and they are all there. I am now able to 'use mysql' since it is empty. Any ideas on what may have happened. Since it seems to affect all databases and all backups I have to believe there is some corruption within the mysql server itself and not the data files. -Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database and all backups lost at same time
Well this is a stand alone operation. I don't currently have the ability to copy files to another machine. And we do have a UPS. The problem turned out to be a bad power cord into the computer itself. Never had that happen before. Now my question is thishow can the 7 days of backups also be corrupted as well...those files were not open at the time of the crash? And if only open files are affected...would database files not actively accessed at the time of the crash reasonably be safe? -Jason - Original Message - From: Gerald R. Jensen [EMAIL PROTECTED] To: kibserv-FtS Setiqueue [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 6:53 PM Subject: Re: Database and all backups lost at same time : Not to add insult to injury, but keeping backups on the same machine and : media your databases are installed on is asking for trouble. And power : outages are predictable, so UPS protection is critical. Enough said. : : I would speculate that one or more of the files in the /data/mysql/ folder : were open for a write operation when the power went out, corrupting the : file(s). Since the mysql database controls rights/privs for the other : databases, problems with it could transcend to problems accessing the other : DB's. : : We have a process in place to prevent data loss: : - All database schema are installed via scripts, which are stored on CD-RW : media. : - All privileges on our MySQL servers are scripted. Once installed, these : scripts are kept on the CD-RW with the database schema scripts. : - Every morning at 2am, a cronjob creates a backup of each database : (including mysql) : - When the backups are safely created, the cronjob does an SSH connection to : another server and ftp's the backups to the other machine. : - The backups are moved to CD-RW : : This is not a bullet-prof plan, but it gets the job done for us. : : Gerald Jensen : : - Original Message - : From: kibserv-FtS Setiqueue [EMAIL PROTECTED] : To: MySQL List [EMAIL PROTECTED] : Sent: Saturday, May 18, 2002 3:44 PM : Subject: Database and all backups lost at same time : : : This is an odd one. I seem to have lost all databases and backups : simultaneously. : : I keep daily backups for upto 7 days. This morning after a power outage I : noted I was unable to login. Every user and password I tried was denied. I : checked the files in the mysql data folder and they were all there and : seemed fine. They matched exactly to those in the past 7 days of backups. : I even tried to restore from each of the 7 days of backups with no result. : : I removed all the files in the data/mysql/ folder and restarted mysql again. : I was able to login as root with no password but found every other database : I tried to use came back with 'ERROR 1049: Unknown database'. I also : checked these files and they are all there. I am now able to 'use mysql' : since it is empty. : : Any ideas on what may have happened. Since it seems to affect all databases : and all backups I have to believe there is some corruption within the mysql : server itself and not the data files. : : -Jason : : : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail : [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : : : : : - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query question in join table with null
I have been battling with a complex query that us giving me a tremendous headache.. I have a MASTER table with 7 other tables (I can not change the database) The master table contains values (Key's) which I then need to lookup in another table that has 2 fields, the Key and a Description field. Everything works fine, but when one field in the master table has a null value, the entire query craps out. In MSSQL, I would use a IF THEN ELSE statement to get around this.. Is there such a feature in MySQL (running v3.23)? My Query is below: Select MASTER.N_NUMBER, MASTER.SERIAL_NUMBER, MASTER.YEAR_MFR, MASTER.REGISTRANT_NAME, MASTER.STREET1, MASTER.STREET2, MASTER.REGISTRANT_CITY, MASTER.REGISTRANT_STATE, MASTER.REGISTRANT_ZIP_CODE, MASTER.REGISTRANT_REGION, MASTER.COUNTY_MAIL, MASTER.COUNTRY, MASTER.LAST_ACTION_DATE, MASTER.CERTIFICATE_ISSUE_DATE, MASTER.APPROVED_OPERATION_CODES, MASTER.TYPE_ENGINE, MASTER.MODE_S_CODE, MASTER.FRACT_OWNER, ACFTREF.MANUFACTURER_NAME, ACFTREF.MODEL_NAME, ACFTREF.AIRCRAFT_TYPE, ACFTREF.ENGINE_TYPE, ACFTREF.AIRCRAFT_CATEGORY, ACFTREF.AMATEUR_CERTIFICATION, ACFTREF.NUMBER_ENGINES, ACFTREF.NUMBER_SEATS, ACFTREF.AIRCRAFT_WEIGHT, ACFTREF.AIRCRAFT_CRUISING_SPEED, AIRCRAFT_CODES.DESCRIPTION, AIRWORTHINESS_CODES.DESCRIPTION, ENGINE.ENGINE_MANUFACTURER, ENGINE.ENGINE_MODEL_NAME, ENGINE.ENGINE_TYPE, ENGINE.ENGINE_HORSEPOWER_THRUST, ENGINE.FUEL_CONSUMED, REGISTRANT_CODES.DESCRIPTION, MASTER_STATUS_CODES.DESCRIPTION From MASTER MASTER, ACFTREF ACFTREF, AIRCRAFT_CODES AIRCRAFT_CODES, AIRWORTHINESS_CODES AIRWORTHINESS_CODES, ENGINE ENGINE, REGISTRANT_CODES REGISTRANT_CODES, MASTER_STATUS_CODES MASTER_STATUS_CODES Where ( MASTER.AIRCRAFT_MFR_MODEL = ACFTREF.MANUFACTURER_MODEL_SERIES AndMASTER.TYPE_AIRCRAFT = AIRCRAFT_CODES.AIRCRAFT_CODE AndMASTER.AIRWORTHINESS_CLASSIFICATION = AIRWORTHINESS_CODES.AIRWORTHINESS_CODE AndMASTER.ENGINE_MFR_MODEL = ENGINE.ENGINE_CODE AndMASTER.TYPE_REGISTRANT = REGISTRANT_CODES.REGISTRANT_CODE AndMASTER.STATUS = MASTER_STATUS_CODES.STATUS_CODE ) AND MASTER.N_NUMBER = '3868J' The MASTER.STATUS field is the one that could sometimes contain a NULL.. Any help would be greatly appreciated! Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.1 Bugs
Richard, can you upload via ftp gzipped dumps of the two tables in the UNION to /pub/mysql/secret at support.mysql.com? About the crashes which occur in 5 - 7 days: please send me the error log of MySQL 'hostname'.err so that I can resolve the stack dumps, if they exist. 4.0.1 is already a rather old release and several bugs have been fixed since then. Unfortunately, 3.23 does not support unions. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Richard Clarke [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, May 19, 2002 12:27 AM Subject: Re: 4.0.1 Bugs 1) CREATE TABLE `summary_rts` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB CREATE TABLE `summary_rts_old` ( `cid` int(11) NOT NULL default '0', `hits` int(10) unsigned default '0', `uniq` int(10) unsigned default '0', `reload` int(10) unsigned default '0', `hourly` int(10) unsigned default '0', `daily` int(10) unsigned default '0', `mtime` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`cid`,`mtime`), KEY `time` (`mtime`) ) TYPE=InnoDB create table rank_union select * from summary_rts union select * from summary_rts_old; The error began to occur after the sum of rows was roughly 200,000. InnoDB free space was in the range of 4 gigs and disk space 2gigs. 2) Can't really give tables and queries here since they form a large set of statistical analysis queries. Only information I can helpfully give is that the same set of roughly 30 queries is run every 5 seconds 24/7. After about 5-7 days the db server segv's on both our 4.4-release machines. Ric. - Original Message - From: Carsten Gehling [EMAIL PROTECTED] To: Richard Clarke [EMAIL PROTECTED]; MYSQL [EMAIL PROTECTED] Sent: Saturday, May 18, 2002 9:48 PM Subject: SV: 4.0.1 Bugs Fra: Richard Clarke [mailto:[EMAIL PROTECTED]] Sendt: 18. maj 2002 22:29 Emne: 4.0.1 Bugs List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. May we see a) your queries and b) the tables involved? Without such information it would just be a guessing game... - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Maintaining Database connections
We are using VB6 to access the MYSQL, while we use ADO OCX to connect to the database, we are able control the timeout, but using connection string, we are unable to . As our current applications perform minimum of one update/addnew per second and sometime, even more. There are at least 10 applications accessing the same table. At lately, we have experience a lot of incident whereby the connection are lost or timeout. My Question :- 1) How to control the time out using connection string ? 2) Under our working environments, Inno is ISAM is better 3) How to error trap the connection existence ? 4) Sometime, during update using the ADO OCX, we will get Multiple Step generate error Regards SB Lim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Storing UTF-8 text with MM.MySQL 2.0.13 driver.
You would need to put useUnicode=truecharacterEncoding=UTF-8 in your JDBC
url, like the README directs. See
http://java.sun.com/j2se/1.4/docs/guide/intl/encoding.doc.html for a list of
supported encodings.
-mark
- Original Message -
From: John D. Stein [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, May 18, 2002 10:43 AM
Subject: Storing UTF-8 text with MM.MySQL 2.0.13 driver.
I am trying to store UTF-8 encoded text with MM.MySQL 2.0.13 driver. I am
using MySQL 3.23.49, MM.MySQL 2.0.13 JDBC driver, j2sdk1.4.0.
I have had success with the code below (which stores the UTF-8 as a binary
stream), but am wondering if there is an easier way. Specifically I am
wondering about the MM.MySQL useUnicode and characterEncoding params. The
code below works regardless how you set these params.
Do useUnicode and characterEncoding allow you to store UTF-8 with regular
.setString() methods? What exactly do they do? I can't find any
explanation more than:
useUnicode should the driver use Unicode character encodings when handling
strings? (true/false) false
characterEncoding if useUnicode is true, what character encoding should
the
driver use when dealing with strings? none
Is setBinaryStream() the only way to store UTF-8?
Thank you very much in advance!!!
John D. Stein
// Store a unicode character (u) in the database and see if what you get
// back from the database (urc) equals the original string you put in.
public void testBinaryUTF8() throws Exception {
String u = \u65E5;
byte[] ua = u.getBytes(UTF-8);
ByteArrayInputStream is = new ByteArrayInputStream(ua);
String updateSQL = UPDATE sp_i18n_dev.lengthtest SET n255 = ? WHERE
id = 1;
String selectSQL = SELECT n255 from sp_i18n_dev.lengthtest WHERE id
=
1;
Connection conn = DataSource.getConn(dsName);
PreparedStatement pst = conn.prepareStatement(updateSQL);
pst.setBinaryStream(1, is, ua.length);
pst.execute();
pst = conn.prepareStatement(selectSQL);
ResultSet rs = pst.executeQuery();
rs.next();
BufferedReader brin = new BufferedReader(new
InputStreamReader(rs.getBinaryStream(1), UTF-8));
String urc = brin.readLine();
System.out.println(
Original string equals string from db: +
u.equals(urc));
DataSource.cleanup(conn, dsName, pst, rs);
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select except
Hey guys and gals, is there a query that selects all comuns from a table, except the one's i have defined? Jule -- |\/\__/\/| | Jule Slootbeek | | [EMAIL PROTECTED]| | http://blindtheory.cjb.net | | __ | |/\/ \/\| - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
