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: 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: again with SELECT
Rather than using primes, you could use the SET datatype http://www.mysql.com/doc/en/SET.html This allows for a color to be zero or more values at the same time. SELECT * FROM tbl_name WHERE color = '1,2,3,n' Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 4:51 PM To: 'gamin'; 'danchik'; 'Fred Whipple'; [EMAIL PROTECTED] Subject: RE: again with SELECT Normally (this is what most ppl would think), for a given item you have one single unique color, but as i see this is not in your case. You should reorganize your tables. But if for some reason you cannot, here is a suggestion (just off my head). Assuming that colorIDs do not take very large values. To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. I dont think deleting will be such a problem also, say you want to delete all items which have colorID = 2. Find the corresponding prime, lets say it is P then you simply need to check if mod(colorPRODUCT, P) = 0. Of course no indexes can be used directly. Well, not such a bright suggestion !! G -- 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: 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:
");
echo("self.location.replace('http://trashankarna.net/community/login/ouch.php')");
echo("");
}
$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"]) { ?>
Läs e-postmeddelande
Skriv ett
e-postmeddelande |
Min adressbok |
Skapa mapp |
";
print "";
print $rad["body"];
print "";
print "Tillbaka till inkorgen";
mysql_close($open);
?>
My lasa.php looks like this:
");
echo("self.location.replace('http://trashankarna.net/community/login/ouch.php')");
echo("");
}
$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"]) { ?>
Läs e-postmeddelande
Skriv ett
e-postmeddelande |
Min adressbok |
Skapa mapp |
";
print "";
print $rad["body"];
print "";
print "Tillbaka till inkorgen";
mysql_close($open);
$open = mysql_connect("localhost", "community", "tarot");
mysql_db_query("iwepost", "UPDATE meddelanden SET last = 'j' WHERE id =
'$id'");
?>
Someone has tried my community from another computer that isn't within the network,
and every seem to work, but not E-post. When they did choose to read a message in the
inbox they got this message:
Warning: MySQL Connection Failed: Access denied for user:
'[EMAIL PROTECTED]' (Using password: YES) in
C:\apache\htdocs\community\meddelanden\lasa.php on line 43
I have made changes from localhost to my domain http://trashankarna.net in the
httpd.conf file, so this might work now, but I can check it from my own computer.
I have made two sign in names cassandra and cassandra33 with passwords 123456 that you
can use to check it if you want to help me.
Thanks/Cassandra
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate records
You can always catch the Duplicate Key error and respond accordingly in your PHP script. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 1:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: edit', 'delete', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );"; $response = mysql_query( $insert_data, $dbh ); $get_table_data = "SELECT * FROM ccfs"; $response = mysql_query( $get_table_data, $dbh ); ?> Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 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: 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: 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: 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=en&edition=ca&q=convert+xml+to+mysql&btn 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, 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] > > -- 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 ' > > ftp://cc278355-a.groni1.gr.home.nl/c64/games/' & > filename & '.gif " > > ftp://cc278355-a.groni1.gr.home.nl/c64/games/' & > filename & '.zip > "> ' & urldesc & ' > > '& desc &' > > > ' 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: 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: 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: Confused about MyISAM vs InnoDB tabel types
You should find the following informative: http://www.mysql.com/doc/en/Table_types.html InnoDB offers transaction support, and seems to recover better from crashes. You do sacrifice some speed and features such as FULLTEXT index support. Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: PAUL MENARD [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 24, 2003 2:51 PM > To: [EMAIL PROTECTED] > Subject: Confused about MyISAM vs InnoDB tabel types > > > Can anyone either summarize for me a comparison between the > MyISAM and InnoDB MySQL table type? > > I am getting ready to upgrade from MySQL 3.23.42 to 4.0.13 in > the coming week and started reading the upgrade documents on > the www.mysql.com site. Never had even thought about using > another table type since my current database seems to work > fine. But thought I would ask. > > > -- 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: 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: -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. 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: 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: .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: 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: 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: 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: 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: 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: 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]
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: 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]
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> > "$DBNAME"db_"$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]
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: 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]
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]
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]
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]
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]
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]
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: 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]
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: 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: 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]
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]
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: New MySQL user question: record security
Hi All; I have just recieved a request to build a database using mySQL and I have a bit of a dilemma. The database is to be used for relations between politicians and their constituents, and I have to have tables for constituents and their information. I would like to have one main database and not seperate databases for each politician, but I would like to make sure that they can only see information on THEIR OWN constituants and not the constituants of their competitors. Is there any way to grant permissions only where the constituency matches theirs? I can do this easily enough through the application, but I would like to extend such security to the database itself. Mike Hillyer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Permissions Question
I am trying to make a database system in MYSQL, but I need to restrict access to certain records in a table, is there any way to only allow access to records in a table that have a field matching a certain value? Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Application security
Just a quick question, if I am going to make a database using MYSQL and have front end applications, is it better to give each user a username/pass and have them enter it into the front end to be passed to the server, or should I give the app a password, hardcode it into the app for database connections, and have a user/pass table which the app uses to authenticate users? I would like the users to not be able to connect directly to the server, as different, competing client's data is stored on the same table, and the app has to make sure they cannot see eachother's data. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql
I believe this is one of those few cases where the database will silently optimize your datatypes, it will be transparent from the client point of view, but will make the database more efficient. Nothing to worry about. Mike Hillyer Dynamergy Software -Original Message- From: Pushkar Pradhan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 13, 2002 3:32 PM To: [EMAIL PROTECTED] Subject: sql I'm not very familiar with dbs, I'm creating tables in mysql 3.23.49 and even though I specified a field as CHAR (e.g. customer CHAR(9)) when I do a show create table on this table it displays that this field is a VARCHAR(9). | sales | CREATE TABLE `sales` ( `item` varchar(20) default NULL, `employee` varchar(9) default NULL, `quantity` smallint(5) unsigned zerofill default NULL, `price` float default NULL, `customer` varchar(9) default NULL, `date` date default NULL ) TYPE=MyISAM | Is this okay behaviour? -Pushkar S. Pradhan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Changing passwords and deleting users
Hi All; I am working on a front end to my database, but I am running into a bit of trouble. I have a user who has the proper privileges and grant option create other users, but I need to know this: can that user delete users he has created (or at least disable them), and can users change their own passwords? This is all being done for a VB front end, so I need to be able to do these things using SQL statements. Any help would be appreciated. Thanks, Mike Hillyer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
You need an index, it should drastically cut the query time. See: http://www.mysql.com/doc/en/CREATE_INDEX.html#IDX1466 Mike Hillyer Dynamergy Software -Original Message- From: Steinar Kolnes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 13, 2002 4:50 PM To: Mysql List Subject: Need help how to make Directory system in MySQL with 6.5 mill subscribers ? Hi there, I have to make a large 6.5 million names and numbers database in MySql(maybe not so large for some of you). Yet it is very simple, here is my sql file: create table subscriber ( id bigint unsigned not null auto_increment primary key, subscr_id bigint unsigned, telco_idint unsigned, first char (80), lastchar (40), address char (40), postcode int unsigned ); NB I also merged first and middle names into one "first"; All the above should be searchable. I have a separate table that take cares of postcodes and post names. However the search is very slow. It takes more than 3 minutes for a query to search for first and last name; Example: select * from subscriber where first like 'steinar%' and last like 'kolnes%'; Is there any out there that have an suggestion how I can speed things up, even if I increases the size to more than 10 mill. I planned to have separate tables for first, last and street addresses, however is this a good idea ? Rgs Steinar Kolnes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Security question
Hi there, I posted this a few days ago and recieved no responses, so I thought I would post it again: Hi All; I am working on a front end to my database, but I am running into a bit of trouble. I have a user who has the proper privileges and grant option create other users, but I need to know this: can that user delete users he has created (or at least disable them), and can users change their own passwords? This is all being done for a VB front end, so I need to be able to do these things using SQL statements. Any help would be appreciated. Thanks, Mike Hillyer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LIMITing result sets - Spoiled by MySQL
Well, I am not sure about MS, but in Oracle you have to use the rownum pseudocolumn and a pseudocolumn, an example: SELECT * FROM (SELECT * FROM mytable ORDER BY somedatefield) WHERE rownum > 20 AND rownum < 30; or something like that. It may be similar for MSsql, I am not sure. Mike P.S. my example may be wrong, it's been a while since I went to mySQL, and I have not gone back to Oracle. -Original Message- From: Michael Marano [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 2:37 PM To: [EMAIL PROTECTED] Subject: LIMITing result sets - Spoiled by MySQL I am used to working in MySQL, and am now working with MS SQLServer :( I'm hoping someone else has been in this unfortunate situation before and can help me out. I'm looking to do pagination of some resultset in JSP. in MySQL I would have used something like: SELECT * FROM mytable ORDER BY somedatefield LIMIT 20,30 to get the third page of results with 10 results per page. MSSQL offers me NOTHING as kind as LIMIT. I have a query that returns ~60,000 results, and I don't want to have to grab them all on each page, and then move to the range that I need. Thanks for the help, MIchael - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BLOB and ADO
Well, first of all this belongs in the myODBC thread as the implementation of BLOB fields in mySQL is excellent in my opinion. But to answer your question, you cannot simply put a binary file into a variant type (or any other type) variable and have it get updated. You need to use the ADO stream object. I do not pretend to know what it is doing behind the scenes, but it works quite well in my VB6 front end. The article explaining how the stream object works can be found here: http://support.microsoft.com/default.aspx?scid=kb;[LN];Q258038. One caveat, the default install of mysql allows for files no larger than 1MB, this can be fixed by adjusting the max_allowed_packet variable in my.cnf to a higher number. I use 16M, which is the limit of the mySQL 3.X branch, the 4.x series is limited only by server memory, but if your server is accessed across a WAN, a 16M limit may be a good idea. I found it was for my application. Good Luck, Mike Hillyer -Original Message- From: Bruno Batarelo [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 18, 2002 8:05 AM To: [EMAIL PROTECTED] Subject: BLOB and ADO Greetings There is a persistant problem while trying to insert a string from within VB 6.0 application to the LONGBLOB field. I use ADO and code for adding new record is as followes: TabelaTekst.AddNew TabelaTekst!Polje = BinaryString TabelaTekst.Update and error is: "Multiple-step operation generated errors. Check each status value." I do not know what to do. I use MySQL 4.01 and MyODBC 3.51.03. It occures only with this field data type. I would appreciate any help since I'm in big trouble if I do not solve this. Thank You all. Bruno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: possibly off topic: any VB programmers out there?
Well I guess the first point to make is that you would not be so off topic in the MyODBC list. I personally stay away from the "data" set of controls. I stick to manually loading recordsets into boxes, and thus avoid problems such as the one you point out. To do what you are doing, I would manually compare the entry to the recordset and take action accordingly. Something else to consider is that the add button will likely be what the users will be looking for, and may not think to type in something manually when they do not find what they are looking for in the combo box (I have gone against the grain, and in an enterprise environment you can always train to make sure the user knows what to expect, but what about when you cannot train the user?) Ultimately, my advice would be to switch to a regular combo box and handle the checks against the record set yourself, but that is just me. If you choose that course, feel free to e-mail me and I will see if I can help you work it out. Mike Hillyer Dynamergy Software -Original Message- From: Tom Emerson [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 31, 2002 7:47 PM To: mySQL (E-mail) (E-mail) Subject: possibly off topic: any VB programmers out there? I've got a strange problem using VB to connect to a mysql database via ODBC. The problem isn't with connecting, but rather with one particular control -- the "datalist/combo" control. In fact, the "problem" doesn't seem to have anything to do with databases directly, BUT I figured that there is sufficient VB "talent" on this list that someone has seen this and knows how to "fix" it. (indirectly, I'm not able to reliably detect if a row exists or not using this control) Background: the "datacombo" control will automatically load itself with values taken from a dataset -- this is really cool for "lookup" fields where you want to make it easy for the end user to select related data. Being a "combo" type item, it ALSO allows the user to type in a completely NEW value, and that's where things seem to go downhill. In particular, the control has a property/field called "macthedwithlist" which returns true if the user selects or types in a value actually in the list, and another property called "selecteditem" which returns a "bookmark" [record number] of the selected value. This bookmark can then be applied to the related dataset to retrieve the full record for display or editing. The problem I'm running into is that if you TYPE IN the value directly (i.e., without using the mouse to point-n-click or the arrow keys to scroll through the list), the "bookmark" contains a "null" value EVEN IF THE USER TYPES IN A MATCHING ENTRY. (and here, a "null" bookmark would imply that the entered value doesn't match anything) What I'm trying to accomplish is to combine two activities into one logical activity -- for example, "inventory item maintenance". I want to build a form that allows editing of all the particulars for an inventory item (item code, description, qty, etc.) There are two (well, three) activities that can occur: a new item can be added, or an existing item can be modified (or deleted). The "traditional" way to do this would be to place an "add" button on the form to clear the form and allow an item to be defined, generating an annoying error if the user enters an existing "code" number. Likewise, you would place a "find" button that generates an equally annoying error if you DON'T type in the proper item code. What I want to do is combine these activities via the "combo" box -- if you select a existing item the form displays the data and allows for modifications. Likewise, if you type in a new value, the program implicitly "adds" a new (blank) record -- note that since I've determined from the "key" value entered whether to add or modify an entry, there is no need to display an "annoying error". The part my program falls over on is that if you TYPE in an EXISTING item code, the two tests that you can perform to see if this is a "new" entry generate conflicting values: "matchedwithlist" will return TRUE (meaning we should MODIFY the entry), and "selecteditem=null" also returns TRUE (which would indicate we need to ADD a new entry...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sysdate or curdate as default date in mysql
If what you are looking for is the current date to be used as date of creation, then remain unchanged, you will have to specify sysdate as a value during an insert. You may benefit from the timestamp datatype, which sets itself to the current date when any DML statements (insert, update) are performed. Mike -Original Message- From: Chugh Shalini [mailto:[EMAIL PROTECTED]] Sent: Monday, September 02, 2002 6:41 AM To: [EMAIL PROTECTED] Subject: sysdate or curdate as default date in mysql Dear All! Can we define sysdate or curdate as default date for a column of datatype 'date' while creating a table? Regards Sql, mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Connect to Database over the Internet
Hi Bryan; First I would direct you to the MyODBC mailing list, as it is generally the place where we VB programmers converse. At any rate, you need MyODBC installed (check the products heading of the MySQL web site for the MyODBC download), and your connection string is wrong. I would detail it, but as I recently wrote an article that has an explanation of connection strings and a sample one, I will instead just make a shameless plug: check out www.dynamergy.com/mike/articles/blobaccessvb.html and you should find what you need. Mike Hillyer Dynamergy Software -Original Message- From: Bryan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 2:00 AM To: [EMAIL PROTECTED] Subject: Connect to Database over the Internet Hello All: I would appreciate some help on how to configure my application code or the computer which mysql resides on so it can be accesed over the intenet. Putting aside the obvious security concerns how can I achieve this. The application is written in VB using ADO to access the database. Currently this is the connect string I currently use to connect to the database. .ConnectionString = "Data Source = REGIONAL;SERVER=xx.xx.xx.xxx;User ID = root; Password " It works fine connecting to the database when run on the same computer which contains the MYSQL database. When I try the application from a remote computer configured it does not connect. No firewall is involved. I suspect I need to configure certain networking protocols on the host/ server machine, but am lost as to what I should be doing. The operating system is XP professional. Any suggestions. Thanks -=Bryan=- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
