MySQL Windows Bot Alert - Secure Your Servers
There is a new bot spreading on the Internet that targets insecure MySQL installations on Windows, as reported at http://isc.sans.org/diary.php?isc=a508f4a185755af19ea8bd45444a570b. I am now going to quote the advisory: -- Infection Method The bot uses the MySQL UDF Dynamic Library Exploit. In order to launch the exploit, the bot first has to authenticate to mysql as 'root' user. A long list of passwords is included with the bot, and the bot will brute force the password. Once connected, the bot will create a table called 'bla' using the database 'mysql'. The 'mysql' database is typically used to store administrative information like passwords, and is part of every mysql install. The only field in this database is a BLOB named 'line'. Once the table is created, the executable is written into the table using an insert statement. Then, the content of is written to a file called 'app_result.dll' using 'select * from bla into dumpfile app_result.dll'. The 'bla' table is dropped once the file is created. In order to execute the 'app_result.dll', the bot creates a mysql function called 'app_result' which uses the 'app_result.dll' file saved earlier. This function is executed, and as a result the bot is loaded and run. -- This bot will then attempt to infect other machines. MySQL installations are at risk if proper security practices have not been followed. You need to act now to secure your Windows MySQL installation from this bot and help prevent the spread of the worm. The steps are very simple: 1. Firewall port 3306 from outside access. No MySQL servers should ever be exposed directly to the internet. If you do not have a firewall, look at the various free software firewalls available. 2. Secure your root account. See http://dev.mysql.com/doc/mysql/en/default-privileges.html and http://dev.mysql.com/doc/mysql/en/security-against-attack.html. Specifically, ensure that the root account has a STRONG password that cannot be easily guessed, and remove the [EMAIL PROTECTED] account from the grant tables: Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.9-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql; Database changed mysql DELETE FROM user WHERE host = '%' AND user = 'root'; Query OK, 1 row affected (0.03 sec) mysql FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec) mysql This will prevent logins from external machines. 3. Disable networking. If feasible, remove network access to MySQL completely by using the skip-networking option in your configuration file and restarting the server. You can still connect via named pipes on an NT based system. If your server is behind a firewall, and you have a strong root password you are not vulnerible to this worm. If not, take the necesscary steps now to ensure that your system is not infected. -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-380-6535 Mobile: +1 403-330-0870 MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: www.mysqluc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing from plain text?
It sounds like you want the LOAD DATA function, found at http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html As long as there is some wat of distinguishing the two fields in the file, you can load them into MySQL. Mike Hillyer Robin Lynn Frank wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 My apologies for so basic a question, but I am just getting started with mysql. I have a text file that looks like this: textother text text2 othertext2 [...] I want the text in the left column in one field and the text in the righ column in the other field. I've been looking thru the docs, but am unsure of what I am seeing. Can someone point me to an example? TIA. - -- Robin Lynn Frank Director of Operations Paradigm-Omega, LLC == A bug in the code is worth two in the documentation. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Sed quis custodiet ipsos custodes? iD8DBQFBGlEko0pgX8xyW4YRAxmkAJ9lEM6RNUMrKvU3p8MwpKXbV/JJ1ACeJMfc RRUmJEs6tw2h65IagfylVHQ= =Cw38 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Initiate perl script in linux from a Windows VBA program
Annie Law wrote: Hi,I would appreciate help with the following. I would like to be ableto initiate a perl script that is residing on a linux server from anExcel workbook. I am working on the Excel workbook in Windows. Iwould appreciate any pointers, sample code for accomplishing this inVBA or some other method.The perl script that I am trying to call will take the informationfrom a mysql database and update the Excel sheet.The script will also take information from the Excel sheet and updatethe database.thanks very much. - Post your free ad now! Yahoo! Canada Personals Why wouldn't you just use ODBC to connect to the server and work directly? Remotely initiating a perl script would involve creating a telnet or SSH session from within Excel using VBA, which would be a far more complex undertaking. -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-380-6535 The Open Source movement has become a major force across the software industry, and MySQL is the world's most popular open source database. --Fortune Magazine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: outer join
The (+) indicates an OUTER JOIN. This should work: SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1 LEFT JOIN Store_Information A2 ON A1.store_name = A2.store_name GROUP BY A1.store_name; Scott Purcell wrote: Hello, I am working through a sql tutorial, and would like to perform this (written for oracle) outer join using mysql. SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name I am reading the docs, but do not understand. Could someone please give me a hand with this? Thanks, Scott Purcell -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-686- The Open Source movement has become a major force across the software industry, and MySQL is the world's most popular open source database. --Fortune Magazine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
Larry Lowry wrote: Well I'm trying to move to MySQL from the MS SQL Server world. Most data elements are easy except for the uniqueidentifier. In the MySQL world what is the preferred/best way to store a uniqueidentifier? The easiest would just be a char(36). Thanks. Larry Lowry In this case I would say easiest is best. There is no specific field for uniqueidentifier. Mike Hillyer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: uc2004.vbmysql.com - Conference Blog Collection and Image Gallery
Hi All; I would like to take a moment to announce the launch of http://uc2004.vbmysql.com. This site is intended to serve as a resource for the MySQL community as a blog collection and photo gallery related to the 2004 MySQL User Conference Expo (http://www.mysql.com/news-and-events/users-conference/). I will be posting my User Conference related blog entries and photographs here and encourage all other attendees to do so. For those who cannot attend I hope this can give at least a little insight into the happenings at the user conference. For those who will be there hopefully you can get someone else's notes if they attend a session you could not. All content at http://uc2004.vbmysql.com will be under a Creative Commons license (http://creativecommons.org/licenses/by-nd-nc/1.0/), so be sure you are comfortable with the license terms before posting images in the gallery. If you will be posting a blog entry, instructions as to sending a trackback are located onsite. Since only blog excerpts are listed you need not worry about your entire blog entries being under the Creative Commons license. Once again, attendees please ping the site when blogging about the conference, and I encourage you to post your photos in the gallery. I hope everyone can benefit from this! Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: VBMySQLDirect API
Hi All; Sorry to repost, but it appears this announcement was dated 2001 and probably fell through some email systems: VBMySQL.com is pleased to announce the launch of a new projects page at http://projects.vbmysql.com. The first (and flagship) project is VBMySQLDirect. VBMySQLDirect is a new MySQL C API wrapper written by longtime site contributor Robert Rowe. VBMySQLDirect is a fork of the MyVbQl API and is available for Visual Basic developers and all Windows developers who have access to COM objects. VBMySQLDirect offers improved performance over ODBC, and also offers improvements over the previous MyVbQl API in terms of better memory management, BLOB support, and better ADO compatibility. VBMySQLDirect uses a more recent MySQL API as it's basis as well and therefore supports more recent functionality than MyVbQl. VBMySQLDirect is available at http://projects.vbmysql.com/vbmysqldirect Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: New VBMySQLDirect API
VBMySQL.com is pleased to announce the launch of a new projects page at http://projects.vbmysql.com. The first (and flagship) project is VBMySQLDirect. VBMySQLDirect is a new MySQL C API wrapper written by longtime site contributor Robert Rowe. VBMySQLDirect is a fork of the MyVbQl API and is available for Visual Basic developers and all Windows developers who have access to COM objects. VBMySQLDirect offers improved performance over ODBC, and also offers improvements over the previous MyVbQl API in terms of better memory management, BLOB support, and better ADO compatibility. VBMySQLDirect uses a more recent MySQL API as it's basis as well and therefore supports more recent functionality than MyVbQl. VBMySQLDirect is available at http://projects.vbmysql.com/vbmysqldirect Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ann: New article at www.vbmysql.com
Hi All; I would like to take a moment to announce that I have finished work on a new article at vbmysql.com! The latest article, titled The VB-MySQL Tutorial - Part 1, covers the basics of application and database design, covering subjects such as entity design, relationships, and the creation of CREATE TABLE statements. In fact, here's the table of contents: 1. Introduction 2. Choosing an Application 3. Listing Requirements and Features * The Development Triangle 4. Entity Design * Users * Groups * Events 5. Entity Relationships 6. Designing The Database * Choosing A Primary Key * Choosing Field Names and Required Fields * One-To-Many Relationships * Many-To-Many Relationships 7. Normalizing The Database * First Normal Form * Second Normal Form * Third Normal Form 8. Choosing Column Types and Writing CREATE TABLE Statements * NOT NULL, DEFAULT, AUTO_INCREMENT, and PRIMARY KEY * CHAR vs. VARCHAR * Storing Phone Numbers * ENUM Columns * Date Columns * TIMESTAMP Fields * Composite Primary Keys * Defining Our Remaining Tables 9. Conclusion Like most of the material at www.vbmysql.com, it features a hands-on approach and is written so that an absolute beginner can benefit. This article is generally database and programming language independant and should be useful even to those who do not use VB as their language of choice. I hope you like it! Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ann: New Article At vbmysql.com!
Hi All; The last MySQL newsletter linked to my article titled Protecting MySQL Sessions With SSH Port Forwarding, available at http://www.vbmysql.com/articles/sshtunnel.html. Response was positive, but there were multiple requests for information on hosting SSH sessions on a Windows server. In response to requests I have issued a followup article called (most creatively) Protecting MySQL Sessions With SSH Port Forwarding (Part 2), which is available at http://www.vbmysql.com/articles/ssh-tunnel-part2.html. This followup gives instructions for installing the OpenSSH For Windows package and also covers opening and closing SSH tunnels from within Visual Basic. In any case, I hope you find these of use! Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Functions and Procedures in Mysql
You are correct. I interpreted Function or a procedure to be the questioner referring to stored procedures with two seperate terms. Mike Hillyer -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 25, 2003 8:06 PM To: mike Cc: [EMAIL PROTECTED] Subject: Re: Functions and Procedures in Mysql In the last episode (Nov 25), mike said: wanted to know if one can write a Function or a procedure in a mysql 4.0.12..??? This is not going to be supported until MySQL 5. Mysql has supported user-defined functions since 3.21. http://www.mysql.com/doc/en/Adding_functions.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is your hourly rate?
I know this is a little off-topic, but I have been approached to do some consulting to move an ISAM based app to MySQL. The potential customer is asking an hourly rate but as I have not done MySQL work as a consultant I am not sure what to charge. Any Ideas? Thanks, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN question
Hi all; I was given a query today of the following: SELECT record_id, record_title,artist_name,label_name,record_catalog FROM record_profile LEFT JOIN artist_profile,label_profile ON record_profile.artist_id = artist_profile.artist_id OR record_profile.label_id = label_profile.label_id GROUP BY record_id The user is trying to LEFT JOIN the artist and label tables to the record table, and I realized that it has been quite a while since I did a LEFT JOIN two tables to the same source table. Anyone know how to do this? I can't remember if this is how it would be done: SELECT record_id, record_title,artist_name,label_name,record_catalog FROM record_profile LEFT JOIN artist_profile ON record_profile.artist_id = artist_profile.artist_id LEFT JOIN label_profile ON record_profile.label_id = label_profile.label_id GROUP BY record_id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question about InnoDB and external locking
At this point it is pure academic curiousity. I am putting together a cheap cluster to play with shared drive failover, and thought I would see what happens when I point two MySQL machines at the same drive while I'm at it. Mike -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Behalf Of Jeremy Zawodny Sent: Thursday, September 11, 2003 5:17 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Jeremy Zawodny Subject: Re: Question about InnoDB and external locking On Thu, Sep 11, 2003 at 03:41:21PM -0700, [EMAIL PROTECTED] wrote: Ok, here's another question. Given effective external locking by the OS, could MyISAM tables achieve this? Yes. If so, do you know any operating systems that would have reliable external locking? I'm not sure what the state of file locking is in various OSes. I believe it's generally not a problem unless you also throw NFS into the mix... Out of curiosity, why do you need to do this? I've found that it's a rare need. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about InnoDB and external locking
Hi All; First of all, I think this will probably be a question for Heikki. If I remember correctly, InnoDB and the MySQL external locking flag are unrelated as InnoDB tables are unaffected by external locks. Now the question: is it possible for two MySQL servers to access the same tablespace in a shared disk cluster? If not, would this ever be on a to-do list or is it too much trouble to implement? Thanks, Mike Hillyer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Difference between Serializable and Repeatable Read with InnoDB
Conceptually, SERIALIZABLE just adds LOCK IN SHARE MODE to every SELECT query. Other than that there is not much difference. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Bill Todd [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 7:13 PM To: [EMAIL PROTECTED] Subject: Difference between Serializable and Repeatable Read with InnoDB Since InnoDB does not allow phantom reads with Repeatable Read isolation (which are allowed in the ANSI SQL definition of Repeatable Read) what is the difference between these two isolation levels. Is it just serialization and that is all? Bill -- 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: Backing up all MySQL DBs
Sure, use the --all-databases option instead of $DBNAME http://www.mysql.com/doc/en/mysqldump.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: René Mølsted [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 11:51 AM To: [EMAIL PROTECTED] Subject: Backing up all MySQL DBs Hi everybody I'm pretty new to MySQL (and to this list). My problem is I need to get a dump of all databases in seperate files, I know how to do one database to one file and all databases to one file. So far I'm using this command: mysqldump --user $USERNAME --password=$PASSWORD $DBNAME | gzip $DBNAMEdb_$DATE.sql.gz Is there a way selecting all databases? René Mølsted -- 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: Innodb multiple tablespaces
And will you be making your deadline? ;) -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:38 AM To: [EMAIL PROTECTED] Subject: Re: Innodb multiple tablespaces Sean, I am at this very moment programming them :). The deadline is Sept 15th, 2003. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html . Subject: Innodb multiple tablespaces From: sean peters Date: Mon, 8 Sep 2003 11:16:27 -0500 Hi all, A few weeks ago, someone mentioned that Innodb would soon have multiple tablespaces available under MySQL. I saw that Innodb.com shows this on their to do list. Is anyone aware of the status of this upgrade, or a site that would have details regarding the upgrade? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Has the list gone down...
Well, I saw your message. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 7:46 AM To: [EMAIL PROTECTED] Subject: Has the list gone down... I haven't gotten any e-mails from the list in several days, are there problems? -- 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: How to Ask Questions the Smart Way...
After a few too many bad questions, I wrote something on a similar vein, but a little shorter: http://www.vbmysql.com/mike/blog/archives/11.php Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New Article on SET Datatype
Hi Everyone; As I was perusing the MySQL documentation, I noticed that there is a lack of documentation regarding the MySQL SET datatype and the queries used to manipulate it. In fact, the comments are longer than the documentation. A google search shows a similar lack up information regarding SET. I have therefore added a new article to my site for those interested in the MySQL SET datatype, which can be found at: http://www.vbmysql.com/articles/mysqlsetdatatype.html It is written for general MySQL users, and I would appreciate any feedback to ensure the information is clear and accurate. Thanks, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regular expresion replace possibility?
UPDATE mytable SET mytext = REPLACE(mytext,'',''); Assuming you wised to strip a double quote, modify to suit. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED] Sent: Monday, July 21, 2003 2:49 PM To: [EMAIL PROTECTED] Subject: Regular expresion replace possibility? Is it possible to run a query that will just alter text possibly using a regular expression? I have about 250 rows that I want to strip quotes out of. Does anyone know of an easy way to do this? Dean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regular expresion replace possibility?
You have to search for 'string functions' to find it. Problem is that a search for REPLACE will bring up the REPLACE syntax, not the REPLACE() syntax (not the brackets ;-) ) Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED] Sent: Monday, July 21, 2003 3:39 PM To: Mike Hillyer; [EMAIL PROTECTED] Subject: Re: Regular expresion replace possibility? Worked like a charm! I couldn't find anything about this in MySQL docs though... Neither before I knew what to search for nor after. What's up with that? Dean - Original Message - From: Mike Hillyer [EMAIL PROTECTED] To: Dean Householder [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 21, 2003 1:53 PM Subject: RE: Regular expresion replace possibility? UPDATE mytable SET mytext = REPLACE(mytext,'',''); Assuming you wised to strip a double quote, modify to suit. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED] Sent: Monday, July 21, 2003 2:49 PM To: [EMAIL PROTECTED] Subject: Regular expresion replace possibility? Is it possible to run a query that will just alter text possibly using a regular expression? I have about 250 rows that I want to strip quotes out of. Does anyone know of an easy way to do this? Dean -- 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: Can mysql handle this load?
Well that all depends. The real reason for a primary key is to prevent duplicates, therefore, if a combination of fields needs to be unique, then a multiple primary key makes sense, especially if other tables will reference the field combination (for example, detail items on an invoice where the invoice number will not be unique, and the detail number will not be unique, but the combination of the two will be). As for question #2, if two fields, one on each table, will be used to join tables together, both fields should be indexed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Adam Gerson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 9:09 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? Is it better to set multiple primary keys or to set one key and index the other columns? If I have a primary key as a field in another table should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, [EMAIL PROTECTED] wrote: i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to dump images into a database?
Yes it is. If you are using VB look at www.vbmysql.com/articles/blobaccessvb.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 8:49 AM To: [EMAIL PROTECTED] Subject: Is it possible to dump images into a database? Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- 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: RESETTING AUTO_INCREMENT
Why are you looking to reset it? If you mean resetting when there is no data in a table, a truncate table should start the auto_increment over again. If you are referring to recovering some auto_increment values that were previously used by no rows now use them, it is better to avoid this. That way you can prevent some potential conflicts. You can reset the auto_increment with ALTER TABLE tablename AUTO_INCREMENT = 1; but know what you are doing when you do. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Miguel Perez [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:08 PM To: [EMAIL PROTECTED] Subject: RESETTING AUTO_INCREMENT Hi everyone: Does anyone know how to reset the auto_increment value of certain table. Any ideas or sugestions Greetings in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- 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: NAS vs SAN for data directory
Well, I would avoid NAS as it introduces unwanted overhead when accessing the data. As for SAN versus the internal RAID5, I am not sure, I would say benchmark the two and see which comes out better, or wait to see if there is anyone out there that has dealt with both. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: SAQIB [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 8:45 AM To: [EMAIL PROTECTED] Subject: NAS vs SAN for data directory Hello All, I am trying to decide which storage I should use for the Data Directory. My application has lots of 'SELECT's (80%) and fewer UPDATEs/INSERTs(20 %). I have the following choices of Data storage 1) Xiotech SAN (66 Mhz FCAL) 2) NAS 3) Internal SCSI 3 RAID 5 Will I achieve any better performance using one storage vs the other storage? In the past (for about 2 years), I have I used SAN with 33Mhz FCAL, and the performance has been quite exceptional. And I have encountered no problems. Any suggestions? Thanks Saqib Ali http://www.xml-dev.com -- 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: My query string might be too long
Well, I have sent queries over 4 times as long without difficulty, so I do not think you are coming up against a limit on query length. I would not use MySQL 5 for any production or even development machines, as a development tree is not even guaranteed to work. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 8:13 AM To: [EMAIL PROTECTED] Subject: My query string might be too long Is there a line limit to a query in MySQL? ( I couldn't find this in the MySQL manual) Here is my query: SELECT a.field_name, b.field_option, c.project_name FROM field_master a, field_options_master b, project_master c where a.field_option_id = b.id and a.project_id=b.project_id and a.project_id='1' and c.id='1' and field_name='State' If I try to put this all on one line (in MySQL Control Center), the line stops at a.project_id=b.project_. In my Java code, I have this query all on one line, but it fails. Do I have to try and put in hard breaks? I would prefer to have a stored procedure and send the stored proc. parameters. I see that a development tree of 5.0 is available now. 5.0 has stored procedure support in it. Maybe I should bite the bullet and install 5.0? Do you know when 5.0 is scheduled for production release? Thanks, Kevin -- 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: Newbie questions (3) - listeners / BLOB / PHP
No Yes www.php.net Regards, Mike Hillyer -Original Message- From: Jeremy [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 1:49 PM To: [EMAIL PROTECTED] Subject: Newbie questions (3) - listeners / BLOB / PHP Hi I'm from an Oracle background and wanted to ask a couple of questions: 1) Have installed mysql 4 on a linux server. If I want to access this from another machine (i.e. run a MySQL-compliant client), is there a 'listener' of some sort that has to run on the server where the database is? 2) Can anyone tell me if MySQL allows the storage of BLOB data? 3) What is a good resource for learning about PHP MySQL 'together'? Thanks for any and all pointers - happy to help myself if you can direct me to where i will find the info. cheers Jeremy --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: Fri 04/07/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count Rows?
If your table is MyISAM, then SELECT COUNT(*) FROM tablename Will return a rowcount without a major performance hit as the rowcount is stored and a table scan is not needed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Roy W [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 9:57 AM To: [EMAIL PROTECTED] Subject: Count Rows? Is there a simple MySQL command that will give a Row Count (# of records) WITHOUT running a select (huge database) Thanks! Roy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More tables or more joins
Well, lets say that you suddenly remember that you need column X in the user table. In the normalized model you have to do one ALTER TABLE statement. In the design you have in place you need n ALTER TABLE statements where n = the number of users. It can also be easier to program against and manage normalized data. That being said, if your users have security concerns you need to maintain separate tables, as there are no views in MySQL (yet) and therefore you cannot prevent users from seeing each other's data in a normalized model. On another note, 2 million rows should not pose any performance issues, I can search tables with millions of rows and get back results quickly as long as I practice proper indexing (having fixed length rows also helps and is not hard to achieve). I would say that as long as contact privacy is not a concern, use the normalized approach for management ease. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jackson Miller [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 10:47 AM To: Jake Johnson Cc: [EMAIL PROTECTED] Subject: Re: More tables or more joins I appreciate the idea of normalizing, but those tables wouldn't meet the spec. There would also have to be a column value table at the very least. Also, why would you have user_id and cont_id in both the user_table and the contract table. Also if you read my post you would see that I am talking about a minimum of 200 users each with an average of 20,000 contacts (with no overlap). This means that the contact table would have a minimum of 2,000,000 rows just to get started. The alternative would be to have 200 tables with 20,000 rows each. I understand that having this many tables is crazy, but I don't understand why it is not better. -Jackson On Wednesday 02 July 2003 11:49 am, Jake Johnson wrote: You don't want to have a separate table for each user. That would cause a maintenance nightmare. Try normalizing your data user table -- user_id cont_id user_name Contract lookup cont_id Cont_Name Contract Column Lookup -- col_id col_name Contract table user_id Cont_id col_id qty This should be a good start... Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Wed, 2 Jul 2003, Jackson Miller wrote: I am working on a program that is essentially a contact management tool for multiple users. There are currently about 200 users and will be over 1000 eventually. Each user may have between 10 and 500,000 contacts. Where it gets interesting is that each user needs to have the ability to control the fields that it is storing for it's contacts. I am considering giving each user it's own table for storing contacts. In this scenerio I would provide a means for editing the columns in the table. The other scenerio is to have a table to store field names, their type, and their default value and their account relationship. Then another table would store the contacts for all accounts with an account relationship. A final table would store relationships and values of contacts and the fields. I am mostly concerned with speed. My guess is that the first scenerio will be faster as long as all the queries only search the contacts for one account (i.e. one table). However I am a little concerned about having hundreds (and eventually thousands) of tables. Does anyone have experience with this kind of situation? Thanks, -Jackson -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie SELECT problem
Well, it is important to remember that SELECT DISTINCT simply restricts that the WHOLE ROW is distinct, therefore it takes into account all columns, not just the sessionID column, when deciding if a row is distinct. One way to do this would be to do SELECT sessionID, userID, date, time FROM sti_tracking WHERE sessionID IN (SELECT DISTINCT sessionID FROM sti_tracking WHERE userID = 99); Assuming you have MySQL 4.1 that is (which supports subselects). Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Tim Winters [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 10:56 AM To: [EMAIL PROTECTED] Subject: Newbie SELECT problem Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated 1600 Bedford Highway, Suite 212 Bedford, Nova Scotia B4A 1E8 www.samplingtechnologies.com [EMAIL PROTECTED] [EMAIL PROTECTED] Office: 902 450 5500 Cell: 902 430 8498 Fax:: 902 484 7115 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Having MySQL Server and databases on different computers
Well, if we are talking about a one to one relationship between MySQL and repository, you can always share the folder the data files will be stored in using NFS or SMB, and then just adjust the datadir entry in the my.cnf file appropriately. The performance of such a solution will probably be degraded though. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Aleksandr Zingorenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 11:05 AM To: [EMAIL PROTECTED] Subject: Having MySQL Server and databases on different computers I am wondering if it is possible to run MySQL Server on one computer on a LAN, but have all the databases be stored on another computer on that LAN (such that the other machine is like a data repository that the MySQL server machine can access when needed without having to store any of the data in itself). In the case this is not possible, what operation can achieve something that is closest to what I have described? Thank you very much in advance. -- 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: Help with privilege tables - IMPORTANT
Well, you do not show what UPDATE privileges your user possesses, so I am not sure where your problem lies. However, 2.23.57 has a fix that may be relevant to your situation: Fixed security problem where mysqld didn't allow one to UPDATE rows in a table even if one had a global UPDATE privilege and a database SELECT privilege. http://www.mysql.com/doc/en/News-3.23.57.html But it depends on how you GRANTed UPDATE privileges. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:26 PM To: '[EMAIL PROTECTED]' Subject: Help with privilege tables - IMPORTANT All, I have the user table which contains user record | Host | User| Pass | Select...| --- |% || | N... | The db record is | Host | Db| User | Select...| -- | %| Test1 || 'Y'... | | %| Test2 || 'Y'... | All privileges are Y in the db table. When I try to do a MULTI table update like: update Test1 A, Test2 B set A.value = 'VALUE' where A.ID = B.ID; All I get is 'update not allowed for user (@localhost)'. When I changed the user table to allow the global update and select privilege, it worked. Is the multi table update correct for the privileges tables? I don't want the user to have global access, only db access. Thanks Cory Twibell Lockheed Martin Space Systems Company Electronic Combat Development Systems [EMAIL PROTECTED] (303) 971-3184 -- 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: Help with privilege tables - IMPORTANT
Well, with that in mind, does the user have the appropriate INSERT privileges? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:49 PM To: Mike Hillyer; Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT My user in the user table has NO privileges. All privileges for that user are done in the db table. Using MySQL 4.1.0-alpha -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:41 PM To: Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Well, you do not show what UPDATE privileges your user possesses, so I am not sure where your problem lies. However, 2.23.57 has a fix that may be relevant to your situation: Fixed security problem where mysqld didn't allow one to UPDATE rows in a table even if one had a global UPDATE privilege and a database SELECT privilege. http://www.mysql.com/doc/en/News-3.23.57.html But it depends on how you GRANTed UPDATE privileges. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:26 PM To: '[EMAIL PROTECTED]' Subject: Help with privilege tables - IMPORTANT All, I have the user table which contains user record | Host | User| Pass | Select...| --- |% || | N... | The db record is | Host | Db| User | Select...| -- | %| Test1 || 'Y'... | | %| Test2 || 'Y'... | All privileges are Y in the db table. When I try to do a MULTI table update like: update Test1 A, Test2 B set A.value = 'VALUE' where A.ID = B.ID; All I get is 'update not allowed for user (@localhost)'. When I changed the user table to allow the global update and select privilege, it worked. Is the multi table update correct for the privileges tables? I don't want the user to have global access, only db access. Thanks Cory Twibell Lockheed Martin Space Systems Company Electronic Combat Development Systems [EMAIL PROTECTED] (303) 971-3184 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with privilege tables - IMPORTANT
Hmm, if you have UPDATE privilege on A and SELECT on A and B then it should work. Do you have UPDATE granted on B (though I know you should not need it)? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 4:26 PM To: Mike Hillyer; Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Ok, the user has the appropriate privileges in the db table. The user can select, insert, update, deletewhatever. What he CAN'T do is a MULTI-TABLE UPDATE using just the db privileges table. -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 4:22 PM To: Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Well, with that in mind, does the user have the appropriate INSERT privileges? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:49 PM To: Mike Hillyer; Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT My user in the user table has NO privileges. All privileges for that user are done in the db table. Using MySQL 4.1.0-alpha -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:41 PM To: Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Well, you do not show what UPDATE privileges your user possesses, so I am not sure where your problem lies. However, 2.23.57 has a fix that may be relevant to your situation: Fixed security problem where mysqld didn't allow one to UPDATE rows in a table even if one had a global UPDATE privilege and a database SELECT privilege. http://www.mysql.com/doc/en/News-3.23.57.html But it depends on how you GRANTed UPDATE privileges. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:26 PM To: '[EMAIL PROTECTED]' Subject: Help with privilege tables - IMPORTANT All, I have the user table which contains user record | Host | User| Pass | Select...| --- |% || | N... | The db record is | Host | Db| User | Select...| -- | %| Test1 || 'Y'... | | %| Test2 || 'Y'... | All privileges are Y in the db table. When I try to do a MULTI table update like: update Test1 A, Test2 B set A.value = 'VALUE' where A.ID = B.ID; All I get is 'update not allowed for user (@localhost)'. When I changed the user table to allow the global update and select privilege, it worked. Is the multi table update correct for the privileges tables? I don't want the user to have global access, only db access. Thanks Cory Twibell Lockheed Martin Space Systems Company Electronic Combat Development Systems [EMAIL PROTECTED] (303) 971-3184 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with privilege tables - IMPORTANT
That is my conclusion as well. I think you should post it as such barring any comments otherwise. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 4:51 PM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Yes, I have the UPDATE for table B. I belive this is a bug. -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 4:47 PM To: Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Hmm, if you have UPDATE privilege on A and SELECT on A and B then it should work. Do you have UPDATE granted on B (though I know you should not need it)? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 4:26 PM To: Mike Hillyer; Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Ok, the user has the appropriate privileges in the db table. The user can select, insert, update, deletewhatever. What he CAN'T do is a MULTI-TABLE UPDATE using just the db privileges table. -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 4:22 PM To: Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Well, with that in mind, does the user have the appropriate INSERT privileges? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:49 PM To: Mike Hillyer; Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT My user in the user table has NO privileges. All privileges for that user are done in the db table. Using MySQL 4.1.0-alpha -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:41 PM To: Twibell, Cory L; [EMAIL PROTECTED] Subject: RE: Help with privilege tables - IMPORTANT Well, you do not show what UPDATE privileges your user possesses, so I am not sure where your problem lies. However, 2.23.57 has a fix that may be relevant to your situation: Fixed security problem where mysqld didn't allow one to UPDATE rows in a table even if one had a global UPDATE privilege and a database SELECT privilege. http://www.mysql.com/doc/en/News-3.23.57.html But it depends on how you GRANTed UPDATE privileges. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Twibell, Cory L [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 3:26 PM To: '[EMAIL PROTECTED]' Subject: Help with privilege tables - IMPORTANT All, I have the user table which contains user record | Host | User| Pass | Select...| --- |% || | N... | The db record is | Host | Db| User | Select...| -- | %| Test1 || 'Y'... | | %| Test2 || 'Y'... | All privileges are Y in the db table. When I try to do a MULTI table update like: update Test1 A, Test2 B set A.value = 'VALUE' where A.ID = B.ID; All I get is 'update not allowed for user (@localhost)'. When I changed the user table to allow the global update and select privilege, it worked. Is the multi table update correct for the privileges tables? I don't want the user to have global access, only db access. Thanks Cory Twibell Lockheed Martin Space Systems Company Electronic Combat Development Systems [EMAIL PROTECTED] (303) 971-3184 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: .fil
In that case they are probably fixed width files. If they are not fixed with and are in fact delimited by tab or comma, you can use LOAD DATA to bring them in. See http://www.mysql.com/doc/en/LOAD_DATA.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ben Ferderer [mailto:[EMAIL PROTECTED] Sent: Friday, June 27, 2003 10:07 AM To: 'Paul DuBois'; [EMAIL PROTECTED] Subject: RE: .fil The extension of the data files specific to my companys inventory and accounting info. If it helps this programs seems to be written in cobol or acucobol. Ben Ferderer System Administrator Radio TV Equipment [EMAIL PROTECTED] 800.288.9134 -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, June 27, 2003 10:46 AM To: Ben Ferderer; [EMAIL PROTECTED] Subject: Re: .fil At 10:36 -0500 6/27/03, Ben Ferderer wrote: Are .fil files associated or view able with sql at all. Someone mentioned to me that they might be. My companys database uses .fil files and I want to be able to link to the information in them for web based inventory display. What's a .fil file? Or --- Am I way off base here? Make it a great day! Ben Ferderer System Administrator Radio TV Equipment [EMAIL PROTECTED] 800.288.9134 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: Is the MyODBC password encrypted?
Plain text. Use of the upcoming MyODBC 3.52 will make it in binary form, but not encrypted. Is the remote server MySQL 4.x? You could possibly use Stunnel (www.stunnel.org) to access the server via SSL. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mike Fish (Shoal Computer Solutions Ltd) [mailto:[EMAIL PROTECTED] Sent: Friday, June 27, 2003 10:51 AM To: [EMAIL PROTECTED] Subject: Is the MyODBC password encrypted? Hi I'm connecting to a MySQL server on the internet from a VB6 app on Win2K using MyODBC. I understand that the connection itself is not secure. I cannot implement SSH for the connection as my ISP does not support this with MySQL. However, when MyODBC sends the username and password etc to the MySQL server, is it encrypted or sent a plain text? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: use of UNION
I think you are after subselects more than a UNION. The latest example of me using union was where a had 2 queries with table A and B: first query has tableA LEFT JOIN tableb, second query had tableb LEFT JOIN tablea. By using UNION I was able to retrieve the results of both with one query. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Daniel Rossi [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:15 PM To: [EMAIL PROTECTED] Subject: use of UNION hi there , i finally worked out union joines are supported in 4.0 i thought it was 4.1 , anyway what are possible examples of its uses ? say i'm trying to join two tables the second table has 10 rows returned with the key of the first table i would like to only get one record from the first table is this possible in the union statement ? currently i have to loop through the first query statement then in that loop do another select statement within the loop and loop through the second query statement returning those ten rows, if i join them on the same query i'll get multiple records of the first table -- 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: Need your help in a search query
This may not help with the comma problem, but your app looks like a good candidate for FULLTEXT searching, check out http://www.mysql.com/doc/en/Fulltext_Search.html for information. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Tom Johnson [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 7:23 AM To: [EMAIL PROTECTED] Subject: Need your help in a search query Hi MySQL Experts, I am pretty new at this and need your help in figuring out if it is possible to create a query to search for words in a given field. What I am trying to do is allow a user to enter words to search for a given title in the database. For example, a person is looking up the title 20,000 Leagues Under the Sea but is entering only 2 as the search criteria. When I run the following query, I get no results. The problem is with the comma in 20,000. Any suggestions on how to find the title even though the user only entered 2??? I am running this on the web using PHP and MySQL. Possible someone knows of a good search script in PHP that might help also? Here is the query I was trying to run: SELECT * FROM ardata WHERE title LIKE '%user_input%'; I am using MySQL 4.0.13 and PHP 4.3.2 Thanks for helping! Tom Johnson -- 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: Best Pratices for mySQL Backups in Enterprise
If your tables are InnoDB, you could look at InnoDB HotBackup: http://www.innodb.com/hotbackup.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: SAQIB [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 11:45 AM To: [EMAIL PROTECTED] Subject: Best Pratices for mySQL Backups in Enterprise We are in the process of implementing enterprise wide (20,000+ users) application that will use mySQL as the Database engine. I was wondering if the slashdot readers can provide me some details about best practices / experiences for Backing Up and Restoring mySQL Databases. I am planning to setup a cron job, to lock the tables, use msqlhotcopy and then unlock the tables. Is that a good backup strategy? Is there a ready-made perl script that I can use? Is there a commercial solution for Backups? What other things do I need to keep in mind? What do other people do in production use, where the DB is frequently? I have already read mySQL: The definitive guide to to using, programming and administring mySQL 4, but did not find ay good information about backing up and restoring, in the 24x7 operation for Data Center use. Any ideas will highly appreciated. Thanks Saqib Ali -- 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: error message
Sounds like a corrupt table, try REPAIR TABLE: http://www.mysql.com/doc/en/REPAIR_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Steven Dowd [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 11:13 AM To: [EMAIL PROTECTED] Subject: error message can someone help me with this error, with my database in MySQL 3.23.49 SQL-query : SELECT * FROM `traffic`.`trafficdb` LIMIT 132947 MySQL said: Got error 127 from table handler have I lost the data, or is there something odd happened which i should be able to fix? Steven Dowd -- 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: user@% vs user@localhost question
I believe % doesn't include localhost, but I could be wrong. % Does indeed include localhost. At least it does on 4.0.13. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SCO UnixWare porting of MYSQL
I simply followed the directions listed here: http://www.mysql.com/doc/en/SCO_UnixWare.html And I had no problems. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Sudhipan Sharma [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 3:08 AM To: [EMAIL PROTECTED] Subject: SCO UnixWare porting of MYSQL Hi ! Just wanted to know if there is any installation procedure available on = UnixWare 7.1.x. There is patch available for SCO UnixWare libz.so, which = I have downloaded but Iam not able to initialize ./Configure script. = Any help/support will be appreciated. Thanks In Advance Regards Sudhipan -- --- Sudhipan Sharma SCO Group - formerly Caldera International. 56, Janpath New Delhi - 110 001 INDIA. Ph: 91-11-23736466, Fax : +91-11-23359997 Web :http://www.sco.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Large file : InnoDB or MyISAM
In a situation with many concurrent reads and writes an InnoDB table would be preferable. See http://www.mysql.com/doc/en/Table_types.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: JOUANNET, Rodolphe [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 2:30 AM To: [EMAIL PROTECTED] Subject: Large file : InnoDB or MyISAM Hi to all, I will have to do with a very big file (approx 600 millions of records). Which is the best table handler for this king of table : InnoDB or MyISAM (many INSERT and MANY SELECT, no UPDATE - statistics file). Best regards. -- 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: MyISAM
MyISAM tables do not support row-level locking, only table locking. See http://www.mysql.com/doc/en/Table_locking.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Cedric Gavage [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 2:14 AM To: [EMAIL PROTECTED] Subject: MyISAM Hi all, I have a question about MyISAM, during an UPDATE for a row, is it a row locking or a table locking? -- Cedric Gavage [EMAIL PROTECTED] http://unixtech.be - http://gavage.com - OpenPGP: 0xED325C64 -- 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: Dumping data
I think you need the -T option, which will break out table data to separate files. Here's a description from the manual: QUOTE -T, --tab=path-to-some-directory Creates a table_name.sql file, that contains the SQL CREATE commands, and a table_name.txt file, that contains the data, for each give table. The format of the `.txt' file is made according to the --fields-xxx and --lines--xxx options. Note: This option only works if mysqldump is run on the same machine as the mysqld daemon, and the user/group that mysqld is running as (normally user mysql, group mysql) needs to have permission to create/write a file at the location you specify. /QUOTE You will also want to look at http://www.mysql.com/doc/en/mysqldump.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Rob [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 1:57 AM To: MySql Subject: Dumping data Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** All information contained in this email is confidential and may be used by the intended recipient only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Subselect functionality
I am not sure that could be viewed as a subselect, as it in not a SELECT within an SELECT, but is instead a SELECT within a CREATE. I would imagine that the SELECT within the CREATE is easier to implement that the actual SELECT within a SELECT. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 1:12 AM To: [EMAIL PROTECTED] Subject: Subselect functionality Hi MySQL Fans ;-), I have a question regarding the MySQL feaures. From Version 4.1 Full subselect support was/is announced. However if I understood correctly then already from Version3.23-41 (or earlier) there are some subselect functions already included. For xample if I try this: CREATE TABLE tblname_new SELECT * FROM tblname_ori; then it works. Or do I have a misunderstanding of subselects (nested queries) ?? Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: Initializing primary key values for existing table
You need simply add the column predefined as AUTO_INCREMENT and PRIMARY KEY: CREATE TABLE incrtest ( name varchar(100) NOT NULL ) TYPE=MyISAM; INSERT INTO incrtest VALUES(ben); INSERT INTO incrtest VALUES(bob); INSERT INTO incrtest VALUES(bom); INSERT INTO incrtest VALUES(gddo); INSERT INTO incrtest VALUES(billy); ALTER TABLE incrtest ADD prikey INT auto_increment PRIMARY KEY; mysql SELECT * FROM incrtest; +---++ | name | prikey | +---++ | ben | 1 | | bob | 2 | | bom | 3 | | gddo | 4 | | billy | 5 | +---++ 5 rows in set (0.00 sec) As you can see, the values are added automatically. Regards, Miek Hillyer www.vbmysql.com -Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 12:11 AM To: [EMAIL PROTECTED] Subject: Initializing primary key values for existing table I needed to add a new, autoincrementing, primary key column to a table and have been struggling to figure out how to assign an initial, unique value to each row. I finally accomplished my task, but feel sure there's an easier way. Here is my solution: 1. Add the column: alter table mytable add mycolumn int auto_increment; 2. Set up a user variable: @mycounter = 0; 3. Assign the initial values by incrementing the counter: Update mytable set mycolumn = max((@mycounter := @mycounter + 1), @mycounter); 4. Finally, set the column to be the primary key: alter table mytable set primary key mycolumn; This seems like a roundabout way of doing things. Can any of you improve on it? Thanks, John -- 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: datetime column dummy question
The TIMESTAMP column type does this for you: See: http://www.mysql.com/doc/en/DATETIME.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: MaFai [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 11:01 AM To: [EMAIL PROTECTED] Subject: datetime column dummy question Hello, mysql, A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. Best regards. MaFai [EMAIL PROTECTED] 2003-06-25 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Execution Time in mysql
You may want to try disabling the index during the insert: ALTER TABLE table1 DISABLE KEYS; insert into table1 select * from table2; ALTER TABLE table1 ENABLE KEYS; And see what that does. See: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Amit Lonkar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 9:33 PM To: [EMAIL PROTECTED] Subject: Query Execution Time in mysql Hi All, I have 2 tables say table1 and table2 in the database. I am using the following query to copy all the data from table2 to table1. insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. Please let know if any solution is available. Thanks Amit Lonkar __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- 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: MySQL 4.0.13 GRANT syntax
Does enclosing the username and host in single quotes help?
I.E. GRANT SELECT ON *.* TO 'update'@'localhost' IDENTIFIED BY 'update';
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: Adam Lawrence [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 25, 2003 8:10 AM
To: [EMAIL PROTECTED]
Subject: MySQL 4.0.13 GRANT syntax
I am attempting to modify the GRANT table using the syntax
specified in the
MySQL 4.0.13 documentation, and am getting error messages claiming the
syntax is incorrect. (I'm running MySQL on Windows 98, by the
way.) I used
mysqlc with root access.
mysql USE mysql;
Database changed
mysql GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update';
ERROR 1064: You have an error in your SQL syntax. Check the
manual that
corresponds to your MySQL server version for the right syntax
to use near
'[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1
mysql
From the manual, the syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
... and the following examples:
mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT ALL PRIVILEGES ON *.* TO monty@%
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED];
mysql GRANT USAGE ON *.* TO [EMAIL PROTECTED];
It appears that my syntax is consistant with the examples
provided in the
4.0.13 documentation. The root account, of course, has full
privileges with
GRANT.
Any ideas?
--
Adam Lawrence
Sustaining Engineering
Tectrol Inc.
--
--
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: Updating MySQL db's
You could use replication of the occasional MySQLDump. Replication will make your development server a slave to your production server, causing every query done on the master to be reproduced on the slave. See http://www.mysql.com/doc/en/Replication.html for more info. Mysqldump will create a script that will dump the data from production to your development box. If you will be manipulating the data on the dev server extensively, you will need this method to bring it back to conformance, as replication slaves (your dev box) would not be able to handle non-SELECT queries. See http://www.mysql.com/doc/en/mysqldump.html for info. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Leo Genyuk [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 12:54 PM To: MYSQL-List (E-mail) Subject: Updating MySQL db's I have two server running Appache + PHP + MySQL. Server #1 is production and server#2 is development. I would like to keep MySQL DB on server#2 up to date. That is any changes happening on server#1 I would like to be reflected on server#2. Does anyone know how to do this? Thank you in advance. Leo. -- 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: MySQL 3.51.06 thru Lotus Approach
Sounds like your windows install process is not complete, check http://www.mysql.com/doc/en/Windows_installation.html for more info. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Russ Guillemot [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 4:15 PM To: POST Subject: MySQL 3.51.06 thru Lotus Approach I use Lotus Approach on (Win XP) to open a text comma delimited file, then I save it as a dbf4 file. But tiimes are changing and files are getting too big for dbf4, so I want to have the unlimited power of mySQL to be able to open HUGE files.. I'm trying to get my Lotus Approach to be able to communicate with mySQL 3.51 driver.. mySQL 3.51 driver is installed (I see it listed in Approaches choices). I open a text file with Approach, and ask it to save it as mySQL 3.51. It says: MySQL ODBC 3.51 Driver Can't connect to MySQL server on 'localhost' (10061) I hit ok and it takes me to the mySQL ODBC 3.51 Driver - DSN Configuration, where it has the following dialog box: DSN INFO Data Source Name: myodbc3-test Description: MySQL ODBC 3.51 TEST DSN MySQL Connection Parameters Host/Server Name (or IP) - (DEFAULT - localhost) Database Name - (DEFAULT - test) User - (DEFAULT - root) Password - blank Port (if not 3306) - (DEFAULT - 3306) I just don't know what to do from here... does mySQL need to be running on my machine? if so, how do I do that? I have installed mysql-4.0.13-win.zip, and there is a folder on my C drive named mysql which contains files like my-huge, my-large, etc.. but nothing in program files to start or run.. so I'm pretty stuck, looking for the next move.. many thanks Russ -- 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: Strange Mull in show fields and table keeps crashing
MUL indicates a that the column is part of a composite INDEX. SHOW INDEX FROM tablename Should let you know what columns make up the MUL column INDEX. If this was not supposed to be indexed, your table is corrupt, best try MyIsamChk Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Karl J. Stubsjoen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 3:13 PM To: [EMAIL PROTECTED] Subject: Strange Mull in show fields and table keeps crashing Hello, I have this table: CLUB Field Type Null Key Default Extra clubid int(11) PRI NULL auto_increment clubusgf int(11) UNI 0 program char(3) url varchar(75) email varchar(75) phone varchar(14) fax varchar(14) address1 varchar(75) MUL address2 varchar(75) city varchar(75) notes varchar(255) zip varchar(10) clubname varchar(75) contact_primary varchar(40) state char(2) contact_secondary varchar(40) See address1 above the the MUL next to it. What is that? Also, this table keeps crashing, it crashes when I make an edit to anything in this field. So I copy the column, move the data over and then I can make edits in this field. However, another field in my table will get this strange MUL indication. From this point forward then, any changes to the data in that column will cause the table to crash. I've succesfully repaired the table a 1/2 dozen times or so... and copied/renamed about 4 of the columns as they took on this MUL characteristic. Any ideas how to fix this problem? Any ideas what is going on? Here is my version of MySQL: Server version: 4.0.0-alpha Karl -- 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: Driver 3.51 Not Found 2
For automated setup under windows, the MyODBC-3.51.06.exe file should be downloaded. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ellen Cain [mailto:[EMAIL PROTECTED] Sent: Saturday, June 21, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: Driver 3.51 Not Found 2 I downloaded MyODBC-3.51.06.zip , unzipped it, and there isn't a setup.exe file - only 'setup.c' Should MyODBC-3.51.06.exe be downloaded? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Driver 3.51 Not Found
That would be because you have not installed it. MyODBC does not install by default, and you may not need it. If you are going to be accessing your new database through ODBC, then go to http://www.mysql.com/downloads/api-myodbc-3.51.html where you can download the latest driver. If you will not be using ODBC, then you need not worry about that message. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ellen Cain [mailto:[EMAIL PROTECTED] Sent: Saturday, June 21, 2003 10:05 AM To: [EMAIL PROTECTED] Subject: Driver 3.51 Not Found Hello, I'm new to mySQL. In Windows, after clicking winmysqladmin.exe in Explorer, I get the message - Driver 3.51 Not Found. Any help would be greatly appreciated. Ellen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: varchar restriction
It will not. If such a change is desirable, you will have to make the change in the related table as well. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 7:57 AM To: MySQL-Lista Subject: varchar restriction Hi MySQL gurus If I chnage a varchar in one table will it effect another related column in another table? Andrew -- 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: SQL query - 3 tables - 3rd one conatins records to not display
What you need is a LEFT JOIN. When you use a LEFT JOIN, you get all rows from your main table, with either the data from the penpals_privmsgs_block table if there is corresponding data, or NULL if there is no related row. Take a look here: http://www.mysql.com/doc/en/JOIN.html for more information. Here's a quick rewrite, you fill in the blanks: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID FROM useronline LEFT JOIN penpals_privmsgs_block ON useronline.something = penpals_privmsgs_block.somethingelse, penpals_fav WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals.privmsgs_block.something IS NULL Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: SQL query - 3 tables - 3rd one conatins records to not display I have a SQL query that needs to reference three different tables. I'm creating an online buddy list of members who are online. I have all of this functioning but am trying to also reference another table where the user is being blocked, in which case I do not what the user's name to be shown in the user's buddy list. I'm using the code below: //SET $colname TO USER'S ID SESSION $colname = $HTTP_SESSION_VARS['svUserID']; //SELECT THE TABLES FROM DATABASE SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID FROM useronline, penpals_fav, penpals_privmsgs_block // HERE I CHECK IF THE USER'S NAME IS IN THE FAVORITIES USER TABLE // AND THE ONLINE USER TABLE ALL OF WHICH WORKS FINE WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals_privmsgs_block.user_id = $colname It gets tricky here when I try to select from another table because this table will not always have the user's name in it, only if the user is blocked by another user will there be a record, in which case we do not want the user's name to be displayed. I was thinking this, but then realized that this would only create a list of those who are being blocked which not what I'm trying to do. AND penpals_privmsgs_block.blocked_id != colname -- 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: varchar restriction
If I have table A with a varchar and table B with a varchar, any modification I make to A will have NO EFFECT WHATSOEVER on table B, and vice-versa. If I want to change the varchar in table A and in table B, I will have to make the change to each one separately and explicitly. Does that make it more clear? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Efficient E [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:50 AM To: Mike Hillyer; [EMAIL PROTECTED]; MySQL-Lista Subject: RE: varchar restriction not sure I understand your relpy Mike do you mean it will effect the other table so I will have to chnage it. Andrew -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: 23 June 2003 15:04 To: [EMAIL PROTECTED]; MySQL-Lista Subject: RE: varchar restriction It will not. If such a change is desirable, you will have to make the change in the related table as well. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 7:57 AM To: MySQL-Lista Subject: varchar restriction Hi MySQL gurus If I chnage a varchar in one table will it effect another related column in another table? Andrew -- 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/mysql? [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 18/06/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replacing text on query..
Take a look at the REPLACE() function: http://www.mysql.com/doc/en/String_functions.html#IDX1202 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Nick Stuart [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:58 AM To: MySQL List Subject: Replacing text on query.. Hello all. I was wandering if it was possible to do a general replacement of text on a query. What I want to do is for any fields that equal 'false' to be replaced with 0 and any fields that equal 'true' be replaced with 1. Now I know you can do if statements, but I have a whole bunch of fields and don't really want to go down the route on all of the fields. But if I have to I will. Thanks for the advice! -Nick -- 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: varchar restriction
Perhaps you better watch your language and better explain your question because if I did not answer your question than I do not know what you are asking. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 9:24 AM To: Mike Hillyer; [EMAIL PROTECTED]; MySQL-Lista Subject: RE: varchar restriction ok ok I get it what you are saying if I change a tables attributes from varchar on one table it wont chnage another tables varchars attributes AND WHY THE FUCK WOULD I EXPECT IT TO!, what I am asking is it necessary to make chnages to the other table. -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: 23 June 2003 15:50 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; MySQL-Lista Subject: RE: varchar restriction If I have table A with a varchar and table B with a varchar, any modification I make to A will have NO EFFECT WHATSOEVER on table B, and vice-versa. If I want to change the varchar in table A and in table B, I will have to make the change to each one separately and explicitly. Does that make it more clear? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Efficient E [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:50 AM To: Mike Hillyer; [EMAIL PROTECTED]; MySQL-Lista Subject: RE: varchar restriction not sure I understand your relpy Mike do you mean it will effect the other table so I will have to chnage it. Andrew -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: 23 June 2003 15:04 To: [EMAIL PROTECTED]; MySQL-Lista Subject: RE: varchar restriction It will not. If such a change is desirable, you will have to make the change in the related table as well. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 7:57 AM To: MySQL-Lista Subject: varchar restriction Hi MySQL gurus If I chnage a varchar in one table will it effect another related column in another table? Andrew -- 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/mysql? [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 18/06/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 18/06/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query - 3 tables - 3rd one conatins records to not display
Well, the following line does not join the penpals_privmsgs_block.user_id to anything: penpals_privmsgs_block left join penpals_fav on penpals_privmsgs_block.user_id Anyhow, by LEFT JOINing the block table to the penpals_fav table you are saying you want one row for every row in the block table, with entries in the fav table when they can be linked, and NULL otherwise. I think you want the order of the tables reversed: penpals_fav LEFT JOIN penpals_privmsgs_block ON penpals_fav.user_id = pempals_privmsgs_block.user_id That way you get one row for each row in penpals_fav, with either the block information form the block table, or NULL. The try putting the AND penpals_privmsgs_block.blocked_id IS NULL line back in? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 9:26 AM To: [EMAIL PROTECTED] Subject: RE: SQL query - 3 tables - 3rd one conatins records to not display OK so now I have something like this: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.user_id, penpals_privmsgs_block.blocked_id FROM useronline, penpals_privmsgs_block left join penpals_fav on penpals_privmsgs_block.user_id WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals_privmsgs_block.blocked_id IS NULL only this brings back nothing as when I remove the AND penpals_privmsgs_block.blocked_id IS NULL statement it results all the people online, but the penpals_privmsgs_block.blocked_id always equals 1 (the value I'm looking for on the blocked user only)for every record and none are null, which is in fact not the case. -- 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: UPDATE and ORDER BY
Then you do need it, but like Paul said, you need MySQL 4.x, or you need to run two queries, one to retrieve the row you want to update, and one to do the update. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 4:21 PM To: wayne Cc: [EMAIL PROTECTED] Subject: RE: UPDATE and ORDER BY Hi, I left out the LIMIT 1 in the example I copied... I only want the first occurance updated. wayne [EMAIL PROTECTED] 23/06/2003 23:22 To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:RE: UPDATE and ORDER BY Have you tried it without it? I cannot seen a reason for using it... With out it all records in subscriptions will be updated with pick+1 where username='webmaster' and dbname. Wayne -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 23 June 2003 23:09 To: [EMAIL PROTECTED] Subject: UPDATE and ORDER BY Hi, Any ideas why I get an error when i use ORDER BY in an UPDATE statement? Also is there anywhere i can lookup the error codes? UPDATE `Subscriptions` SET picks=picks+1 WHERE (username = 'webmaster' AND picksdatabasename = 'BetaTestService') ORDER BY subscriptionid ASC ADODB.Connection.1 error '80004005' SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]You have an error in your SQL syntax near 'ORDER BY price ASC' at line 1 Thanks in advance. Martin -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple mysql instances and virtualhosts
It is definitely possible, see http://www.mysql.com/doc/en/Multiple_servers.html Of course, you could save yourself some trouble and just setup a separate database on the same instance. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: electroteque [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 4:22 PM To: Mysql Subject: multiple mysql instances and virtualhosts this has prob been bought up many of times but how would it be possible to setup different mysql source instances for each individual virtualhost on a machine ? -- 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: Connecting ASP
You can access MySQL databases from ASP by using MyODBC (now Connector/ODBC) just like you would any other ODBC datasource. I would suggest doing google searches on MyODBC and ASP in the same search, or adapt some code from my site www.vbmysql.com, and look in the MyODBC FAQ for examples. Also subscribe to the MyODBC list at lists.mysql.com Here's a sample linked to from the MyODBC FAQ: http://www.devarticles.com/art/1/50 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jorge Cornejo [mailto:[EMAIL PROTECTED] Sent: Friday, June 20, 2003 1:44 AM To: [EMAIL PROTECTED] Subject: Connecting ASP Hi, I'm hosting a website in a Linux server. By now I'm learning PHP and it's amazing. How ever I need to create a online system and meanwhile I'll to do it in ASP (which I already handle). My host gives me MySQL and I use it ok. Now, what I'll like to do is to know how to connect my ASP page to a MySQL database? I can do it in my Win32 PC, but with MyODBC. So, how can I do it? Hope somebody helps me. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is There An Automated Way To Convert A Well-structured XML File T o Its Corresponding Tables In A MySQL Database ?
You need only ask once. This article by Paul Dubois may help :http://www.kitebird.com/articles/mysql-xml.html This Google search resulted in hundreds of resources: http://www.google.com/search?hl=enedition=caq=convert+xml+to+mysqlbtn meta%3Dsearch%3Dsearch=Search+the+Web Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Eldrid Rensburg [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 8:18 AM To: [EMAIL PROTECTED] Subject: Is There An Automated Way To Convert A Well-structured XML File T o Its Corresponding Tables In A MySQL Database ? Importance: High How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ? And How do we normalize this well-structured XML file prior to conversion ? This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: suggestions - server options/mysql variables
If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Derick Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:08 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: suggestions - server options/mysql variables Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- 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: selecting PRIMARY KEY when there is no unique value
When I last did an invoice-type project, he had the header table with an invoice# as PRIMARY KEY, then we had an invoice_line table with a rowid PRIMARY KEY as auto-increment and a invoice# with a non-unique key INDEX. This worked fine for our purposes, so I would probably go with option #1 I guess. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:16 AM To: [EMAIL PROTECTED] Subject: selecting PRIMARY KEY when there is no unique value Hi, In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? thank -- 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: Help me!!!
mailto:[EMAIL PROTECTED] This is what I found at lists.mysql.com Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Luis Enrique Bauzá Peña [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:00 AM To: Lista Mysql Inglés Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
Well first of all, I am obviously having an off day for using AND at all
in my example.
Why not this?
if (isset($HTTP_GET_VARS['check00'])) {
$SQL .= penpals.agegroup = '00' OR;
}
This is probably a question for the PHP general mailing list as it is
more a code question. Essentially you just build the query in chunks. By
the nature of checkboxes you probably just need Ors, not ANDs. You may
need to rip the trailing OR, maybe replacing it with an AND if you need
to add some other condition on the end that needs an AND. Unless of
course someone can belong to more than one agegroup. If so you better
post what the table looks like.
Regards,
Mike Hillyer
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 1:25 PM
To: [EMAIL PROTECTED]
Subject: RE: Complex SQL involving 10 checkboxes
OK, I've done this.
Problem occurs when a user selects 1 and 9.
The SQL statement I have reads like so:
if (isset($HTTP_GET_VARS['check00'])) {
$age00 = '00';
$s_age00 = penpals.agegroup = $age00 AND;
}
Problem is the AND statement. Some times the user will pick
checkbox 1 and 5
but not 2,3,4,6, 7, 8, and 9. And then again there will be
times when a user
only uses one check box needing the AND statement on the end,
but at other
times the user will select more than one needing an OR
statement between the
two checkboxes. Make sense?
-- Original Message ---
From: Mike Hillyer [EMAIL PROTECTED]
To: vernon [EMAIL PROTECTED],
Sent: Thu, 19 Jun 2003 12:57:20 -0600
Subject: RE: Complex SQL involving 10 checkboxes
Well, if one checkbox represents ages 1-5, another represents 6-9,
and another represents 10-15, then you have the checkboxes
add where
clauses. Here is a pseudocode/basic example:
*START WITH A SELECT*
SQL = SELECT * FROM TABLE WHERE
*BUILD THE WHERE CLAUSES*
IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND
IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND
IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND
*TRIM THE UNUSED AND STATEMENT
SQL = left(sql, len(SQL) - 3)
Add some GROUP and ORDER and LIMIT clauses as needed and send the
resulting string to the server.
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:53 PM
To: [EMAIL PROTECTED]
Subject: Complex SQL involving 10 checkboxes
I'm trying to setup a SQL statement that involes using 10
different
checkboxes. The checkboxes repersent age groups that I need
to forward to an
SQL statement. What would be the easiest way to do this?
As I think this through I'm thinking I have to check it 1 and
2 are checked
and not 3-9 and so forth which can drive a person to drink
espically when I
have other SQL statemenst I need to add to it as well.
Any ideas on the best way to do this? I have this whole thing
I was going to
post to the list but figured I start out with the basic
question first.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--- End of Original Message ---
--
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: Complex SQL involving 10 checkboxes
Why not have each one look like this:
if (isset($HTTP_GET_VARS['check00'])) {
$SQL .= penpals.agegroup = '00' OR;
}
Then after you go through them all, strip the last two characters (the
trailing OR) and then attach and AND
That way it will accommodate any checks, and because you rip the last or
and replace it with an and, you are always Ok for what follows.
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 1:49 PM
To: Mike Hillyer
Subject: RE: Complex SQL involving 10 checkboxes
if (isset($HTTP_GET_VARS['check00'])) {
$SQL .= penpals.agegroup = '00' OR;
}
Because there is a statement after these that neends to have the AND
statement.
This is probably a question for the PHP general mailing
list as it is
more a code question.
There I always get them telling me to come here, SQL issue. :(
Essentially you just build the query in
chunks. By the nature of checkboxes you probably just need Ors, not
ANDs. You may need to rip the trailing OR, maybe replacing it with
an AND if you need to add some other condition on the end
that needs
an AND. Unless of course someone can belong to more than one
agegroup. If so you better post what the table looks like.
It's not that they belong to more than one age group they may
be looking for
more than one age group, which is the case (it's a dating site). The
statement immidiately after words MUST have the AND
statement. My only
problem is with the checkboxes. Maybe they select only one in
which case it
needs and AND if they select more than one it needs an OR and
AND on the
last one. Make sense?
V
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
Well, from what limited info I have, it looks like your image tag is not closed properly. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Rolf C [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 2:57 PM To: [EMAIL PROTECTED] Subject: SQL query question Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- 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: DELETE or RENAME tables?
DROP TABLE tablename; ALTER TABLE tablename RENAME newname; http://www.mysql.com/doc/en/ALTER_TABLE.html http://www.mysql.com/doc/en/DROP_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 10:13 AM To: [EMAIL PROTECTED] Subject: DELETE or RENAME tables? How do you DELETE or RENAME a table? Thanks. -- 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: RAID hardware suggestions/experience
I have heard good thing about 3Ware, but I would suggest looking at the 8500-4 in combination with Western Digital's Raptor drive (http://www.tomshardware.com/storage/20030501/index.html). The Raptor is a 10,000 RPM SATA drive which, combined with the 8500-4 SATA Raid card should give excellent performance at a great price. I would also look at Opteron based servers if you are looking for performance for a good price. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 10:59 AM To: [EMAIL PROTECTED] Subject: Re: RAID hardware suggestions/experience Anyone had any experience with 3Ware 7500-4 IDE RAID or the Promise SX-6000 IDE RAID cards? Specifically for Linux. Heard bad things about Promise, good about 3Ware. David - Original Message - From: Patrick Shoaf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 9:40 AM Subject: Re: RAID hardware suggestions/experience I am using 4 120G IDE Drives with an Adaptec IDE RAID Controller on RedHat Linux providing 240G of RAID 5 storage. While not quite as fast as SCSI, I have found this to work very well. You should be able to pickup a nice dual processor XENON 2.4Ghz system w/1G Ram and IDE RAID loaded with RedHat Linux ES for around $4,000. At 12:25 PM 6/17/2003, you wrote: Hi there, Our databank with all tables and idices is about 130GB big. The biggest limitations we encounter are on the I/O side. Therefore we are willing to update our data storage system to a RAID system (RAID 0+1, RAID 5, or RAID 10). Has anyone experience with such RAID systems? What should we buy? From whom should we buy (We are located in New York City)? Do you have any experience you want to share? Thank you very much for your help and support! Bernd Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- 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: RAID hardware suggestions/experience
To me the question of reliability is that of the drive, not the interface. I cannot see SATA itself being any more or less reliable than ATA drives. I think certain controllers will accept a new drive that has similar characteristics as long as the replacement drive is larger than the lost drive. I think the low capacity is a recognition that more performance for the price is more desirable than more capacity with the target market of this drive. After all, you don't see many 180GB SCSI drives, performance is more a concern than capacity (you can get the capacity from RAID anyway. Regards, Mike Hillyer www.vbmysql.com A significant question remains for SATA: basic drive reliability. Related to that is length of time drive will remain available. A dirty secret of RAID is that when a drive goes it must be replaced you must replace it with the same drive (please..please tell me I'm wrong). So, unless you have a spare in the back you will end up replacing 3 drives (assuming Raid 5). That may be why the WD model has such low capacity compared with the normal IDE drives. Just my 2 cents worth. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- 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: SHOW PROCESSLIST
This is a know bug that has been addressed in version 4.0.13, you will need to upgrade. See http://bugs.mysql.com/bug.php?id=164 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Shane Kirk [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 12:25 PM To: [EMAIL PROTECTED] Subject: SHOW PROCESSLIST Hi, we've got a linux server (version 4.0.12) and we seem to be having a strange issue.whenever we view the processlist, it ALWAYS shows connections coming from the localhost instead of the remote machine's name/ip. Is there any particular reason this might be happening? Viewing netstat's output from a shell resolves remote addresses just fine. So it's no doubt a MySQL configuration option I'm missing or set wrong somewhere. Any ideas? -Shane -- 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: mysql design question ?
What you need to do is specify username in your select query and JOIN the two tables together. Marks table: User_id | Marks User Table User_id | Name Query: SELECT User.Name, Marks.Marks FROM User, Marks WHERE User.User_id = Marks.User_id Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Jonas Geiregat [mailto:[EMAIL PROTECTED] Sent: Monday, June 16, 2003 9:55 AM To: [EMAIL PROTECTED] Subject: mysql design question ? | user_id | Marks | Now I have an other table where I keep the name etc.. from each user. You will all kill me for this, but in access you could the setup a relation between user_id and the id of the user in the user table. Is something like that possible in mysql ? So that if I select some data from the | user_id | Marks | table I get his name instead of his ID ? or should I first get his ID, and with that ID query his marks out of the table ? -- 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: Access denied
Did you GRANT SHOW DATABASES to the user? If not then they will not be able to use the command. The SHOW DATABASES GRANT clause is a new addition. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ronan Lucio [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2003 8:34 AM To: [EMAIL PROTECTED] Subject: Access denied Hi All, I created a user dfn and I granted access to only the database dfn. Now, if I log as dfn user query for SHOW DATABASES, mysql returns me the error: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Would anybody know what could be wrong? Thank's Ronan -- 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: Access denied
GRANT SHOW DATABASES will allow a user to see all databases, whether the user can access them or not. -Original Message- From: Ronan Lucio [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2003 8:56 AM To: Mike Hillyer Cc: [EMAIL PROTECTED] Subject: Re: Access denied Mike, On Fri, 13 Jun 2003 08:38:32 -0600 Mike Hillyer [EMAIL PROTECTED] wrote: Did you GRANT SHOW DATABASES to the user? If not then they will not be able to use the command. The SHOW DATABASES GRANT clause is a new addition. I'm sorry but I don't have experience with MySQL. Actually, I installed PhpMyAdmin in an ISP for the clients manage his/her own database. So, I created the account with the command: GRANT ALL PRIVILEGES ON dfn.* TO [EMAIL PROTECTED] IDENFIED BY 'password'; GRANT ALL PRIVILEGES ON dfn.* TO dfn@% IDENFIED BY 'password'; Now, when I access PhpMyAdmin with user dfn, it shows some errors in the left frame, where should show the client's database (dfn). If I grant SHOW DATADABES to this user, Will he see all databases or just the databases that he has permissions? Thank's Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext searching and query order question
Have you tried adding force index on your fulltext index?
Something similar to this:
select field1,field2,field3 from table FORCE INDEX(index2)
where field1='something'
and field2='something_else'
and field3='something_more'
and match(field4) against ('word1 word2' in boolean mode)
order by field1,field2,field3
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: H M Kunzmann [mailto:[EMAIL PROTECTED]
Sent: Friday, June 13, 2003 10:33 AM
To: [EMAIL PROTECTED]
Subject: fulltext searching and query order question
Hi all.
I have a fulltext index on a table.
If I have the following fields:
field1,field2,field3,field4
Field4 being the fulltext field.
I have the following indices:
index1-field1,field2,field3
index2-fulltext field4
If I do a select:
select * from table where match(index2)
against ('word1 word2' in boolean mode);
I get a very fast result.
Essentially I want to do the following:
If I do a select field1,field2,field3 from table
where field1='something'
and field2='something_else'
and field3='something_more'
and match(field4) against ('word1 word2' in boolean mode)
order by field1,field2,field3
I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.
How do I get a fulltext search to be able to sort according
to a different field ???
Thanks for all assistance :-)
Regards,
Herbert
--
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: Please help me!
As this is really a PHP specific question, you may get a better response on the PHP
general mailing list at http://www.php.net/mailing-lists.php
Anyway, if my limited experience in PHP serves correctly, you may need to call
echo(mysql_error()); near the potentially troublesome code.
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: Varghonan [mailto:[EMAIL PROTECTED]
Sent: Friday, June 13, 2003 10:36 AM
To: [EMAIL PROTECTED]
Subject: Please help me!
Hi!
I am new here and wonder if anyone could help me with a problem in my community.
The adress is http://trashankarna.net/community/default.php
I can't figure this out. I get this message when I choose to read a message in my
outbox/utkorg:
Warning: 1 is not a valid MySQL-Link resource in
C:\apache\htdocs\community\meddelanden\lasaut.php on line 69
I can read the message, anyway.
My lasaut.php looks like this:
?php @session_start();
@$user = $iwcuser;
@$pass = $iwcpass;
if(!$user) {
echo(script language=JavaScript);
echo(self.location.replace('http://trashankarna.net/community/login/ouch.php'));
echo(/script);
}
$open = mysql_connect(localhost, community, tarot);
$info = mysql_db_query(iwcusers, SELECT * FROM users WHERE username =
'$user', $open);
$rad = mysql_fetch_array($info);
if($user == $rad[username] $pass == $rad[password]) { ?
?php // top.inc ?
?php require ('c:\\apache\htdocs\community\_inc\top.inc'); ?
?php // Innehåll ?
table cellpadding=0 cellspacing=0 style=border-width: 1pt; border-color:
#00; border-style: solid align=left
tr
td class=menux width=20/td
td class=menuxbrspan class=rubrikLäs e-postmeddelande/spanbrimg
src=/community/_media/line.gif vspace=5 height=1 width=100%/td
td class=menux width=10/td
/tr
tr
td class=menux width=10/td
td class=menux width=600 height=200 valign=topspan class=brodtext
a href=/community/meddelanden/skriv.php class=bodylankSkriv ett
e-postmeddelande/a |
a href=/community/meddelanden/adressbok.php class=bodylankMin adressbok/a |
a href=/community/meddelanden/mappar.php class=bodylankSkapa mapp/a |
p
?php $open = mysql_connect(localhost, community, tarot);
$db = mysql_db_query(iwepost, SELECT * FROM meddelanden WHERE id = '$id');
$rad = mysql_fetch_array ($db);
print $rad[subject];
print | ;
print $rad[datum];
print brbr;
print pre class=body;
print $rad[body];
print /pre;
print Tillbaka till a href=/community/meddelanden/default.php
class=bodylankinkorgen/a;
mysql_close($open);
?
p
?php // bottom.inc ?
?php require ('c:\\apache\htdocs\community\_inc\bottom.inc'); ?
?php mysql_close($open); ?
?php } ?
My lasa.php looks like this:
?php @session_start();
@$user = $iwcuser;
@$pass = $iwcpass;
if(!$user) {
echo(script language=JavaScript);
echo(self.location.replace('http://trashankarna.net/community/login/ouch.php'));
echo(/script);
}
$open = mysql_connect(localhost, community, tarot);
$info = mysql_db_query(iwcusers, SELECT * FROM users WHERE username =
'$user', $open);
$rad = mysql_fetch_array($info);
if($user == $rad[username] $pass == $rad[password]) { ?
?php // top.inc ?
?php require ('c:\\apache\htdocs\community\_inc\top.inc'); ?
?php // Innehåll ?
table cellpadding=0 cellspacing=0 style=border-width: 1pt; border-color:
#00; border-style: solid align=left
tr
td class=menux width=20/td
td class=menuxbrspan class=rubrikLäs e-postmeddelande/spanbrimg
src=/community/_media/line.gif vspace=5 height=1 width=100%/td
td class=menux width=10/td
/tr
tr
td class=menux width=10/td
td class=menux width=600 height=200 valign=topspan class=brodtext
a href=/community/meddelanden/skriv.php class=bodylankSkriv ett
e-postmeddelande/a |
a href=/community/meddelanden/adressbok.php class=bodylankMin adressbok/a |
a href=/community/meddelanden/mappar.php class=bodylankSkapa mapp/a |
p
?php $open = mysql_connect(localhost, community, tarot);
$db = mysql_db_query(iwepost, SELECT * FROM meddelanden WHERE id = '$id');
$rad = mysql_fetch_array ($db);
print $rad[subject];
print | ;
print $rad[datum];
print brbr;
print pre class=body;
print $rad[body];
print /pre;
print Tillbaka till a href=/community/meddelanden/default.php
class=bodylankinkorgen/a;
mysql_close($open);
$open = mysql_connect(localhost, community, tarot);
mysql_db_query(iwepost, UPDATE meddelanden SET last = 'j' WHERE id =
'$id');
?
p
?php // bottom.inc ?
?php require ('c:\\apache\htdocs\community\_inc\bottom.inc'); ?
?php mysql_close($open
RE: delete record
That depends, do you have a backup tape? ;) Unfortunatly a DELETE is a one-way trip. In theory the data is still there is the spot on the hard-drive has not been overwritten, but there are no tools (that I know of) to retrieve that row. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Arcangelo [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 9:20 AM To: [EMAIL PROTECTED] Subject: delete record Hi, I've done these operations: - I deleted one record - I didn't execute optimize table Do you know if it's possible to undelete the record? Many thanks Arcangelo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: in in where
Subselects such as this are only available in MySQL 4.1 and higher. You can upgrade, or rewrite this query as follows: SELECT tblSiteConfig.fldSiteID FROM tblSiteConfig, tblServerConfig WHERE tblSiteConfig.fldServerName = tblServerConfig.fldServerName Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 11:23 AM To: [EMAIL PROTECTED] Subject: in in where My first day with MySQLworking with a Access to MySQL project... when I try to execute the query: SELECT tblSiteConfig.fldSiteID FROM tblSiteConfig WHERE tblSiteConfig.fldServerName in (SELECT fldServerName from tblServerConfig) I get: [My_MYSQL] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT fldServerName from tblServerConfig)' at line 3 I'm not able to find out the error. Please help. Please suggest a good source for learning MySQL SQL. Thanks, raj Unless expressly stated to the contrary, the views expressed in this email are not necessarily the views of National Grid Transco plc or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees make no representation and accept no liability for its accuracy or completeness. This e-mail, and any attachments are strictly confidential and intended for the addressee(s) only. The content may also contain legal, professional or other privileged information. If you are not the intended recipient, please notify the sender immediately and then delete the e-mail and any attachments. You should not disclose, copy or take any action in reliance on this transmission. You may report the matter by calling us on + 44(0) 1455 230999 Please ensure you have adequate virus protection before you open or detach any documents from this transmission. The Group Companies do not accept any liability for viruses. An e-mail reply to this address may be subject to monitoring for operational reasons or lawful business practices. -- 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: query question
SELECT * FROM mytable WHERE LENGTH(phone) 10; http://www.mysql.com/doc/en/String_functions.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Susan Ator [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 11:23 AM To: [EMAIL PROTECTED] Subject: query question Is there a way to check if a field has x number of characters in it? I have a table with a column (phone) which, for the moment is varchar(25). I need to find those entries which have less than 10 characters. Can this be done? Thanks susan -- 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: DATE
Use the ALTER TABLE syntax, see http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Wong Zach-CHZ013 [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 3:43 PM To: 'Paul DuBois'; [EMAIL PROTECTED] Subject: DATE Hi How do I rename a new column ? How do I drop a column ? From MySQL documentation, it refers to rename/drop table. I dont want to do that. Thanks Paul for your aid. -- 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: INT type
The 11 is the display width and does not affect the size of values that can be stored in the column. Here is a quote from http://www.mysql.com/doc/en/Numeric_types.html: --BEGIN QUOTE-- As an extension to the ANSI/ISO SQL92 standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT as listed in the tables above. Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 4. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width. --END QUOTE-- Regards, Mike Hillyer -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 11:26 AM To: [EMAIL PROTECTED] Subject: INT type Hi, I defined a field as INT type but when I checked in phpMyAdmin, it showed INT(11). Still I cannot enter higher 2147483647 (signed int) in this field. Does 11 mean that MySQL needs 11 bytes to keep it? I changed the length to 2 (for example) but still I could save higher values such as 999 in it!!! What does length mean in INT(length)? Thanks in advance -- 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: Practice test
I think the other thing to remember is that this is only the Core exam, and as far as I can tell a fair amount of the content you suggest will be part of the Pro exam. I remember once seeing stirrings of a DBA exam as well, so I guess the Core is just to get your feet wet and show a knowledge of the basics before moving to the higher level exams. The problem with more complicated exams is that they become difficult to administer and grade automatically. The benefit of MySQL using VUE to administer tests is that the tests can be offered in hundreds of locations around the world thanks to the automated testing process. This means that the lady administering my Core exam didn't have to know MySQL from a cheese sandwich. A more complicated exam means instead of choosing from three convenient testing locations in my city, I have to make an extended trip to a testing location where there will be a professional examiner. This all means more cost to me in both time and money. I think certification exams are a tradeoff. The RedHat Certified Engineer exam is supposed to be practical and hands on (you are asked to install, not how to install), and is supposedly in fair demand in the market, but the tradeoff is that I count maybe 20-30 locations on their website that you can take the exam, at a cost of $749 plus travel to the nearest center. When I check for a MySQL exam center, I see more test centers in California alone that RedHat offers in all of north America, and I pay a lot less for the exam. While the MySQL Core exam may be less challenging than an in-depth hands-on exam, I think the exam is good value for the money. It shows you know the basics without checking the manual and can spot basic syntax problems before running the query. I have to agree that most certification tests are a brain-dump, but hey, most HR people do not have the knowledge to check whether you know what the job descriptions need to know, so you have to have something to show you meet the requirements in a job description. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 12:09 PM To: MySQL List Subject: Re: Practice test Paul DuBois wrote: At 7:51 -0700 6/10/03, Jeremy Zawodny wrote: Is this syntax valid in MySQL? [some query] What's the point of such a question? Anyone with access to MySQL can find out by simply running the query. You don't have access to MySQL during the test. You have to know whether or not the syntax is correct. I think the point is that this isn't representative of how effectively we can use MySQL. Yes, the test can measure whether or not we know syntax details, but in a real-life situation that's not a very critical piece of knowlege to have. They did ask at least one what does this query DO question. I think those are much better for a certification exam. Exactly. Procedural questions, and questions that probe the depth of understanding, are harder to write for such an exam... but much more useful in evaluating how well an individual will perform on the job. Examples might include: 1) How do you set up replication? 2) How are replication conflicts resolved? 3) How do you recover from a disk failure? 4) To what extent can you recover from data changes introduced by a rogue program? 5) What is the impact of the CHECK constraint? 6) You create a HEAP table. The server is brought down, and then up again. You issue a SELECT against the HEAP. What happens? 7) A database table has become corrupt after a power outage. What should you do? 8) Which of the following queries will not benefit from indexes? 9) Which of the following queries corresponds to the following query with a NOT EXISTS subquery? 10) Match the following filenames to their corresponding table structures. And so on. Heck, the commonly asked questions on this list would make a pretty good test! Bruce Feist (retired database instructor / courseware designer) -- 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: InnoDB
I will let others give their opinions on which one is best, but it is important to point out that you are not making a one-or-the-other decision. One of the advantages of using MySQL is that you can choose the right table handler on a table-by-table basis. This means that you can have sales and inventory tables be innodb and take advantage of transactions, but keep your log tables in MyISAM for extra speed and fulltext searching (just an example). Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 12:32 PM To: [EMAIL PROTECTED] Subject: InnoDB Hi, I need your experience to help me which table type it's better. I am going to define my database with more than 100 tables and some tables have more than 200,000 records. I know that InnoDB has some advantages against MyISAM such as transactions or foreign keys. But I am not sure that these services cause slowing process or not. If you want to start a database project, which table type do you prefer to use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Other front ends (Was: RE: I need advice. MSAccess frontend to PH P/MYSQL. ANyone done this?)
It depends on what you want to do with the font end. You would always look at MyCC and SQLyog at http://www.mysql.com/products/mysqlcc/index.html and http://www.webyog.com/ respectively. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: David Brodbeck [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 2:06 PM To: [EMAIL PROTECTED] Subject: Other front ends (Was: RE: I need advice. MSAccess frontend to PH P/MYSQL. ANyone done this?) -Original Message- From: MyLists [mailto:[EMAIL PROTECTED] The main thing you should realize right off the bat is that Access gives you a lot of functionality with buttons and other controls and they are quite easy to control with macros and/or VBA. However, depending on the complexity of your forms, most of this functionality will be a challenge to replicate on the web, regardless of which language you choose. Are there any open-source database front-ends that have similar functionality to MS Access? We're currently using Access with tables linked to MySQL, but find it leaves something to be desired when it comes to stability and license flexibility. We'd really like to move to something open-source. PHP doesn't really fit the bill because a web-based interface isn't ideal for our application -- we prefer the more interactive feel of Access's forms. -- 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: empty a table
Try TRUNCATE TABLE tablename--http://www.mysql.com/doc/en/TRUNCATE.html Or DELETE FROM tablename Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Fabio Bernardo [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 3:33 PM To: Mysql (E-mail) Subject: empty a table Hi there, Im a new user Whats the statemant to empty a table... I mean I dont wanna delete one or two rows ...and not drop the table, i just wanna empty it... i tried something like: empty table, but it didnt work... thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Batch Deletes...
You may find that it is the index that is slowing you down. Have you tried disabling the index(s) on your table before the delete and re-enabling them after? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Todd Gruben [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:02 AM To: [EMAIL PROTECTED] Subject: Batch Deletes... In an effort to speed up large batch deletes i devised this script. LOCK TABLES t1 WRITE; SELECT * INTO OUTFILE '/data/dump/t1' FROM t1 where date=2003-06-09; TRUNCATE TABLE t1; LOAD DATA INFILE '/data/dump/t1' INTO TABLE t1; UNLOCK TABLES; this script would work great without the LOCK TABLES clause. I receive an error saying there active locked tables. This is a heap table and I am running mysql 4.0.12 Any ideas? i need the lock tables because there are some updates that will be happening to the current data, i just need to remove the old expired data. This takes about 30secs and my delete scripts takes about 4 hours. It is deleting about 500,000 rows out of a 4 million row table with several indexes. -Todd -- 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: fastest way to get last row
SELECT * FROM table ORDER BY id DESC LIMIT 1; Regards, Miek Hillyer www.vbmysql.com -Original Message- From: Chris Edwards [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 12:23 PM To: MySQL List Subject: fastest way to get last row Hi Does anyone have tips on the fastest way to get the last row in a table. What I have is an int as the the primary key, using auto inc. I currently do a select max(id), then run another query retrieving a row based on the the max(id). Its two queries. Would I be able to get it all into one? Not using mysql 4... Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Bruce Feist [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:13 PM Subject: Re: array/pointer question Peter Brawley wrote: MySQL, like other relational databases, does not support arrays or pointers. I didn't write that. PB -- 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: **field type for large amount of text***
Well, that would be TEXT, MEDIUMTEXT, or LARGETEXT, they hold 65535, 16777215, and 4294967295 characters accordingly. I personally would stick to TEXT, maybe MEDIUMTEXT. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: DuSTiN KRySaK [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 12:38 PM To: [EMAIL PROTECTED] Subject: **field type for large amount of text*** What is the best field type to use for a field that will be used to display a large amount of text (and possibly numbers) IE a field that will display the main text for a web page. Thanks -Dustin -- 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: Turning off column and value reconciliation
Why not just specify what columns you are inserting into? This check has to be in place, because in the case of a mismatch MySQL would not know which value goes into which column. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Campbell D. McCausland [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 1:13 PM To: [EMAIL PROTECTED] Subject: Turning off column and value reconciliation I'm getting the error message:- http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. Is there a way to achieve this? thanks, Campbell --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- 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: SSL and Windows
Are you looking to encrypt fields or the entire session between client and server? SSL is used for the latter, for the former you could look at the ENCODE and DECODE functions. (See http://www.mysql.com/doc/en/Miscellaneous_functions.html) I can use them without openssl being installed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Sparky Kopetzky [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 1:43 PM To: My Sql List Subject: SSL and Windows Does anyone know how to turn the SSL on for mySql for Windows?? I need to encrypt and decrypt certain items in a database and the manual says add the OpenSSL package but there isn't one for Windows. Help! Thanks in advance!! Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning off column and value reconciliation
There is no way to turn it off (well, no practical way, with open source nothing is impossible). Can you add dummy columns to the mysql table to the insert statements match up? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Campbell D. McCausland [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 2:17 PM To: [EMAIL PROTECTED] Subject: Re: Turning off column and value reconciliation Mike, Keith, Thank you for your responses. Unfortunately, the sql I'm loading is output from another tool, so I don't get to vote on the format of the insert statements. If it's not possible to just turn the check off, then I guess I'll have to consider writing a perl script or something to add the column specifiers. Please confirm that there is no way to turn this check off. That way, I'll be able to stop looking for one. 8o) best regards, Campbell At 03:14 PM 6/9/2003 -0400, Keith C. Ivey wrote: On 9 Jun 2003 at 12:13, Campbell D. McCausland wrote: http://forums.devshed.com/t49723/s.htmlGeneral error: Column count doesn't match value count I know what it means, but I need MySQL to turn off this checking. I'm loading legacy data into a new database that has some extra columns and I just want to fill the old columns with the legacy data. It's hard to know since you don't show us any of your code, but it looks like you should add the list of column names to your INSERT or LOAD statement rather than letting the list default to all the columns. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org --- BridgePoint UML --- Campbell D. McCausland Tel:(520) 544 2881 x21 Dir. Research and DevelopmentFax:(520) 544 2912 Project Technology Inc 7400 N. Oracle Road, Suite 365 Tucson Arizona [EMAIL PROTECTED] 85704-6342 www.projtech.com USA DesignPoint Model Compilers -- -- 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: load file
That should work just fine (although you should end the line with ; and not l) Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 3:54 AM To: [EMAIL PROTECTED] Subject: load file Hi, I have a a text file delimited with | and I don't how to load the data into a table and say delimited by '|' I think it is this: LOAD DATA INFILE 'C:\data.txt' INTO TABLE table2 FIELDS TERMINATED BY '|'l Any clarification would help. Anthony -- 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: Count on Multiple Tables
Can you show some table structure so we have something work with? It's hard to recommend a query when we do not know what your sales table structure is. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 4:00 PM To: [EMAIL PROTECTED] Subject: Count on Multiple Tables I've been stuck on this one all morning. Can't seem to figure it out. I have 2 tables, one with affiliate sales and another with affiliate clickthroughs. I have to query both tables, so that I can get clickthrough dates, hits, and then query affiliate sales table to get number of orders for each date. I want to display the results like this: DATE | TOTAL HITS | TOTAL SALES 05/03/2003 6 1 05/04/2003 7 0 I've managed to get dates and total hits by using the following query: SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*) AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND YEAR(affiliate_clientdate) = '2003' GROUP BY date; But then I can't get the total number of sales on affiliate sales table. Any suggestions? Your help is greatly appreciated. -- 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]
