Re: Problem with mysql_install_db
Hi Mickey and others, Here is the location of the lxrun information. ftp://ftp2.sco.com/pub/skunkware/emulators/lxrun-FAQ.html This is for lxrun. MySQL does run in a native form on mysql and it uses gcc. To build or run MySQL on OpenServer you need to install the libraries and linker from the OpenServer CD. You also will need FSU-threads. www.mysql.com in the manual has instructions for building or running binaries. Please look at the manual. http://dev.mysql.com/doc/mysql/en/Installing_binary.html and http://dev.mysql.com/doc/mysql/en/SCO.html Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ADODB.Connection - How do I open a DB, create if not there, open table, create if not there and add a single record?
This should be simple, no? I can't make it work
Objective:
- bring up a panel to ask for MySQL User/Host/Password and save settings if
we get logged in.
- when we start (likely on a new machine) create a new Weather DB if it
doesn't exist
- when we start ( ) create a new TimeTable table if it doesn't exist
- loop through our one per hour loop and add new records as we go
I know this code is likely trash but it is my first attempt and I can find
no example how to do what I think should be simple. THANKS!!
Main.bas: (contains global vars)
Global gCnn As ADODB.Connection
Global pRs As ADODB.Recordset
Global SQL As String, UserName As String, UserPassword As String,
UserHost As String
Connect.frm: (loaded on startup - verifies connection)
Private Sub ClickConnect:
Set gCnn = New ADODB.Connection
Set pRs = New ADODB.Recordset
gCnn.ConnectionTimeout = 60
gCnn.CommandTimeout = 400
gCnn.CursorLocation = adUseClient
gCnn.Open DRIVER={MySQL ODBC 3.51 Driver}; _
user= UserName _
;password= UserPassword _
;server= UserHost _
;option=20499
If gCnn.State = 1 Then
SaveSetting App.Title, Settings, UserHost, UserHost
SaveSetting App.Title, Settings, UserName, UserName
SaveSetting App.Title, Settings, UserPassword, UserPassword
frmWeather.Show vbModal
Unload Me 'we logged on OK, so we will save this info for next
logon
Else
MsgBox Unable to establish the connection. Check your settings and
try again., vbCritical, Error While Connecting
End
End If
frmWeather (Main Form):
FormLoad:
gCnn.Open Weather
If gCnn.State 1 Then 'create database if it does not exist
gCnn.Execute Create Database Weather, , adExecuteNoRecords
gCnn.Open Weather
If gCnn.State 1 Then
MsgBox Failed to access Weather DB
End
End If
End If
On Error GoTo loadstp
SQL = CREATE TABLE IF NOT EXISTS `TimeTable` ( _
`Time` varchar(30) NOT NULL default '', _
`Temperature` varchar(20) NOT NULL default '', _
PRIMARY KEY (`Time`) _
) TYPE=MyISAM
gCnn.Execute SQL, , adExecuteNoRecords 'create table if not exist
SQL = SELECT * FROM TimeTable
pRs.Open SQL, gCnn, adOpenDynamic, adLockOptimistic, adCmdText
[loop logic to loop every hour]
With pRs
.AddNew
.Fields(Time) = Time()
.Fields(Temperature) = fromSendor
.Update
End With
[end loop logic]
Exit Sub
loadstp:
MsgBox Err.Description
End Sub
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MSSQL to MYSQL
Hello list, I have to move a database in MSSQL to MYSQL, with the table structures and all the respective data. I tested one product to do this, SQLyog, and it works fine, except for some little problems with indexes and primary/secondary keys. I want to know if anyone have been dealing with the same problem to recommend me some suggestions/tips/tricks. Do you know another program/script/ways to do this migration ? I want to migrate everything from the original MSSQL database, like indexes, relationships, and so on. Can you guys recommend me some actions or tips to take ? Thanks in advance, -- Rodrigo Galindez Information Management Assistant Center for Human Rights and Environment (CEDHA) Gral Paz 186 10 A 5000 - Cordoba - Argentina Tel/fax 54-351-4256278 [EMAIL PROTECTED] www.cedha.org.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF: how to get chracter set or convert stinrg to another character set
Hi all, I have two question about UDF(User Defined Function) (MySQL-4.1.1-alpha-Max-log and 4.0.18-log) 1. how to get character_set_* in UDF. I want to get following character_set_* information in UDF. MySQL-4.1.1: character_set_serve character_set_system character_set_database character_set_client character_set_connection character_set_results MySQL-4.0.18 character_set 2. how to convert character encoding in UDF. eg: convert ujis stinrg which was provided by client to utf8. Thanks for any help. -- HIROSE, Masaaki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding defaults
Hi Boyd, Can I ask why it really matters? :-) I would assume the DEFAULT value is stored at creation time; but the end result would be the same either way. BTW, I hate how MySQL's SHOW CREATE TABLE quotes DEFAULT INT-family values. :-( It shouldn't do that. Matt - Original Message - From: Boyd E. Hemphill Sent: Friday, April 09, 2004 9:49 PM Subject: Question regarding defaults Hello: I have need to declare a column as type integer then default is at '0' (that is a string with a zero in it). An example may be: Create table foo ( foo_id int not null default '0' ) My question centers on the notion of implicit type conversion. Is the server converting the type at the time the ddl (not really too big a deal) or is it doing the conversion at run time (i.e. each time a row is inserted in the DB). Thanks for your time and expertise! Best Regards, Boyd E. Hemphill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index is not being built with large database
Hi sascha, How's the space on your datadir partition (or wherever this table is)? I believe MySQL creates the temp tables during ALTER in the DB directory, not the tmpdir. If the space there is OK, have you checked the error log for anything related? Matt - Original Message - From: sascha mantscheff Sent: Friday, April 09, 2004 4:21 PM Subject: Fulltext index is not being built with large database I'm trying to build a fulltext index on a table with about 4 million entries with 2 varchar and one text field. The indexing starts and runs for about 1/2 to 1 hour, then the process stops without any error message. And leaves me with no index. I checked for the size in tmp and redirected it to a partition with 50GB space (about 15 times as much as the database tables). Mysql runs on gentoo-linux 2.4 with 800MB RAM with server version 4.0.17. Any clues, hints or tips? Thank you. sascha mantscheff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup
Hi Steve, You might want to look at FLUSH TABLES WITH READ LOCK. That's a query to run from mysql, but I'm sure you can get it to work in your shell script (you need to maintain the MySQL connection while doing the backup). I don't know much about that, though. I think you just run UNLOCK TABLES when you're finished. Matt - Original Message - From: Steve Sills Sent: Tuesday, April 06, 2004 8:17 PM Subject: backup I want to use rsync to backup my db server, how do i lock all the tables for all the db's to read only so i cando my backup, then unlock them again. It needs to be done from the command line, not the mysql program. Anyone have any ideas? I have looked and couldn't find the answer i was looking before. Its running from a shell script, from my backup machine. Its currently setup to shut down the server, however i don't want to have to do this. Thanks in advance. Steve Sills Platnum Computers, President http://www.platnum.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index is not being built with large database
Hi Matt, thanks for the help. Meanwhile the index has been built, and I assume it's a problem of terminal sessions: At first I tried it with a remote mysql client issuing the ALTER TABLE command. This session was closed after some inactivity. Then I logged in to the database host and retried - same result. Then I packed the commands in a shell script which I ran with nohup - no results whatsoever. No error log entry, no index, no nohup.out. Then I tried it again with a terminal session, but this time I configured the terminal program to send null bytes every 60 seconds to keep the connection open. And voila - after 19 hours the index was built. So it may be a flaw, a bug or even a feature in the mysql client/server protocol - I assume I'll never know. But the index works, and it's speed, ease of use and maintainability will be strong arguments against htdig. s.m. Am Sat, 10 Apr 2004 04:01:15 -0500 hat Matt W [EMAIL PROTECTED] geschrieben: Hi sascha, How's the space on your datadir partition (or wherever this table is)? I believe MySQL creates the temp tables during ALTER in the DB directory, not the tmpdir. If the space there is OK, have you checked the error log for anything related? Matt - Original Message - From: sascha mantscheff Sent: Friday, April 09, 2004 4:21 PM Subject: Fulltext index is not being built with large database I'm trying to build a fulltext index on a table with about 4 million entries with 2 varchar and one text field. The indexing starts and runs for about 1/2 to 1 hour, then the process stops without any error message. And leaves me with no index. I checked for the size in tmp and redirected it to a partition with 50GB space (about 15 times as much as the database tables). Mysql runs on gentoo-linux 2.4 with 800MB RAM with server version 4.0.17. Any clues, hints or tips? Thank you. sascha mantscheff -- sascha mantscheff hahnenbach 51570 windeck [EMAIL PROTECTED] telefon +49-2292-922 492 telefax +49-2292-922 493 mobil +49-171-620 0380 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change the date format.
David Carlos Brunstein [EMAIL PROTECTED] wrote: By these days I'm facing a system DB migration to MySQL (the last production version) and I have the followin problem. All the SELECTs sentences are all over the application, and for date/datetime filters are using the dd/mm/ format in the WHERE clause. Is the same in the INSERT/UPDATE statementes. I've studied with MySQL documentation, that use the -mm-dd format for the internal storage and for the data interchange with the clients. So the questions are: Is there any way to set the date/datetime format to MySQL engine, so the client can see it as dd/mm/? Is there any solution with no change to the client application code? Use DATE_FORMAT() function to retrieve dates in the specific format: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html But in this case you should rewrite your queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
privileges
mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
privileges
hi I have redhat 7.2 running on my server with plesk i forgot mysql password for user root so i reset it by following way services mysqld stop safe_mysqld --skip-grant-tables mysqladmin -u root fulsh-privileges password newpassword services mysqld restart. now i can logon to mysql by root user but i can not create and modify databases it gives an error access denied for user [EMAIL PROTECTED] i do not know how to recover root privileges please help me mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE search on several fields
Hello, I have a database with fields : - firstname - lastname And I want to do a search from the query string john smith in order to find anyone having john in his firstname and smith in the lastname or smith in the firstname and john in the lastname. It tried : SELECT * FROM members WHERE CONCAT(firstname, ' ', lastname) LIKE '%john%smith%' But it doesn't work Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE search with different orders
Hello again, To follow my previous message, the CONCAT method does works, but now my problem is to make a search where the order doesn't count : then to find members whose firstname is john, lastname smith and vice-versa. I would like to use an operator or a function for this, but I cannot enumerate all possible combinations (in this case yes, because the query only has 2 words, but with a query with 6 words it would make 6! combinations then a very long query !). Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: privileges
On Saturday 10 April 2004 08:15 am, [EMAIL PROTECTED] wrote: hi I have redhat 7.2 running on my server with plesk i forgot mysql password for user root so i reset it by following way services mysqld stop safe_mysqld --skip-grant-tables mysqladmin -u root fulsh-privileges password newpassword services mysqld restart. now i can logon to mysql by root user but i can not create and modify databases it gives an error access denied for user [EMAIL PROTECTED] i do not know how to recover root privileges please help me I had the same problem a few weeks ago with RH 9. I backed up the databases in /var/lib/mysql and then removed the RH mysqld rpms. I then downloaded the latest binary installer (4.0.18) from http://www.mysql.com and followed the directions to install it. Everything works fine now, and when privileges get screwed up (yes, believe it or not, I screwed them up again while trying to 'secure' my db), I can rerun the script that sets up the mysql table and things are back to normal. Best of luck, Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIKE search with different orders
Hmm. You might be best off using the FIND_IN_SET function (check the manual at mysql.com for more information). In short, if you replace all spaces in your string to search with commas, you should be able to do something like: SELECT * FROM xyz WHERE FIND_IN_SET(test_column, your string here) 0 Not 100% sure, as I haven't tried quite this approach! Another, more long winded way might be to explode your string out into a temporary table and compose a query which will bring back all matching rows (by multiple joins to the temporary table). FIND_IN_SET looks like a better alternative! Cheers, Matt -Original Message- From: Tarik ANSARI [mailto:[EMAIL PROTECTED] Sent: 10 April 2004 14:51 To: [EMAIL PROTECTED] Subject: LIKE search with different orders Hello again, To follow my previous message, the CONCAT method does works, but now my problem is to make a search where the order doesn't count : then to find members whose firstname is john, lastname smith and vice-versa. I would like to use an operator or a function for this, but I cannot enumerate all possible combinations (in this case yes, because the query only has 2 words, but with a query with 6 words it would make 6! combinations then a very long query !). Thank you -- 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]
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]
install: config root user fails
warning: complete db server newbie! (I've done plenty of SQL JDBC coding, but never had to care about the server side until now ;-) Installed the binary distribution packages for Mac OS X: mysql-max-4.0.l8.pkg MySQLStartupItem.pkg no problem. But then when trying to configure the root user for the db, cut and paste straight from Chapter 2 of the manual fails: $ mysqladmin -u root password asdf $ mysqladmin -u root -h `hostname` password asdf mysqladmin: connect to server at 'quickbeam.local' failed error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server' Please help point me in the right direction, DouglasDD PS - I'm sure that this has come up before, but searching on list.mysql.com is currently broken (DP failed for all queries). TONS OF DETAILS: Mac OS X 10.3.3 (not server edition) on a PowerPC G5 dual 2GHz $ uname -a Darwin quickbeam.local 7.3.0 Darwin Kernel Version 7.3.0: Fri Mar 5 14:22:55 PST 2004; root:xnu/xnu-517.3.15.obj~4/RELEASE_PPC Power Macintosh powerpc $ mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.18, for apple-darwin6.8 on powerpc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.18-max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 40 min 30 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 1 Queries per second avg: 0.002 Network: the mac is 192.168.1.100 (behind a LinkSys from the cable modem) -- Why, oh why, didn't I take the *blue* pill? Douglas D. Dickinson -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change the date format.
Hi Egor. Thanks for your answer. I'm relatively new to MySQL. Isn't any way -like an environment variable or configuration file setting or something like that- to change the date format at the MySQL side, not at client side ? Regards, David. == David Carlos Brunstein System Analyst / Software Developer Buenos Aires, Argentina Mail to: David _ Brunstein @ Yahoo . Com . ar IM: DavidBrunstein @ Hotmail . Com -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Saturday, April 10, 2004 8:53 AM To: [EMAIL PROTECTED] Subject: Re: Change the date format. David Carlos Brunstein [EMAIL PROTECTED] wrote: By these days I'm facing a system DB migration to MySQL (the last production version) and I have the followin problem. All the SELECTs sentences are all over the application, and for date/datetime filters are using the dd/mm/ format in the WHERE clause. Is the same in the INSERT/UPDATE statementes. I've studied with MySQL documentation, that use the -mm-dd format for the internal storage and for the data interchange with the clients. So the questions are: Is there any way to set the date/datetime format to MySQL engine, so the client can see it as dd/mm/? Is there any solution with no change to the client application code? Use DATE_FORMAT() function to retrieve dates in the specific format: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html But in this case you should rewrite your queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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]
Useless index key cache
Hi, Thanks to the reply from Sergei, I understand why MySQL requires SAME block size on all indexes when load index into cache However, this feature basically useless for the table that has any fulltext index. Based on what I've seen, fulltext indexes use 2048 block size, whereas others use 1024. So, at current time, we can not pre-load index anyway...am I right? Anyway to make all index block size all the same, no matter what type of indexes? Thanks so much for any help! Haitao __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I avoid filesort with BETWEEN and ORDER BY
I am trying to optimize a query using both BETWEEN and ORDER BY but after months of reading and research I still can not get the hang of this. Details are as follows: 2 Tables CREATE TABLE `p_ad` ( `id` int(11) NOT NULL auto_increment, `cat` mediumint(9) NOT NULL default '0', `title` varchar(50) default NULL, `description` text, `location` varchar(50) default NULL, `pcode` varchar(8) default NULL, `pcode_id` smallint(4) default NULL, `ph` varchar(50) default NULL, `email` varchar(50) default NULL, `user_id` int(11) NOT NULL default '0', `date` timestamp(14) NOT NULL, `price` decimal(10,2) default NULL, `email_priv` tinyint(1) default '0', PRIMARY KEY (`id`), KEY `cat_pc_date` (`cat`,`pcode_id`,`date`), KEY `c_p_d` (`cat`,`pcode`,`date`), KEY `user` (`user_id`), KEY `cat_date` (`cat`,`date`) ) TYPE=MyISAM; CREATE TABLE `p_cat` ( `id` mediumint(9) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `parent` mediumint(11) default '0', `lft` mediumint(11) NOT NULL default '0', `rgt` mediumint(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `LFT` (`lft`), KEY `PARENT` (`parent`) ) TYPE=MyISAM; Query as follows: EXPLAIN SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; +---+---++--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---++--+-+--+---+-+ | p | ALL | cat_pc_date,c_p_d,cat_date | NULL |NULL | NULL | 60002 | Using temporary; Using filesort | | c | range | PRIMARY,LFT | LFT| 3 | NULL | 1 | Using where | +---+---++--+-+--+---+-+ Is there any way I can get a query like this to avoid using a temporary table and filesort. ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade which client?
I needed 5to do some subqueries. I had mysql-3.23. Somehow I thought mysql4 included subqueries. Loaded mysql-4.0. Converted everything and got it working except it doesn't do subqueries. Therefore we installed mysql-4.1.1 6 hours of figuring out the permissions finally we got the databases all fixed and now I can actually query the tables I see. Now there is 1 error left that we're a little confused about. We logged into the server locally via: mysql -u root -p It verifies the password and lets us in. We create the users for all the php scripts via: Mysqlgrant all on scratch.* to 'fred'@'localhost' identified by 'password'; And then when we run our php scripts the mysql-server says; Error MySQL said: #1250 - Client does not support authentication protocol requested by server; consider upgrading MySQL client So what client is mysql-server talking about? Is it the php-mysql rpm or the MySQL-client rpm. The server was installed as a standard binary gunzip. We didn't see a mysql-client binary gunzip just the rpm. Our box currently has the following services: MySQL-client-4.1.1-0 php-mysql-4.3.4-2 mysql-standard-4.1.1-alpha-pc-linux-i686 on a redhat9 os running a 2.4.20-30.9 kernel to be fair we installed the mysql-client after we got the first error message. Before that we had no mysql-client and addes users via mysqladmin. I don't think I can upgrade the php-mysql any higher yet. Can someone let us know what we need to upgrade? Brian Duke SS-Technical Customer Assistance Level(3) Communications 720-888-7797
RE: upgrade which client?
I needed to do some subqueries. I had mysql-3.23. Somehow I thought mysql4 included subqueries. Loaded mysql-4.0. Converted everything and got it working except it doesn't do subqueries. Therefore we installed mysql-4.1.1 6 hours of figuring out the permissions finally we got the databases all fixed and now I can actually query the tables I see. Now there is 1 error left that we're a little confused about. We logged into the server locally via: mysql -u root -p It verifies the password and lets us in. We create the users for all the php scripts via: Mysqlgrant all on scratch.* to 'fred'@'localhost' identified by 'password'; And then when we run our php scripts the mysql-server says; Error MySQL said: #1250 - Client does not support authentication protocol requested by server; consider upgrading MySQL client So what client is mysql-server talking about? Is it the php-mysql rpm or the MySQL-client rpm. The server was installed as a standard binary gunzip. We didn't see a mysql-client binary gunzip just the rpm. Our box currently has the following services: MySQL-client-4.1.1-0 php-mysql-4.3.4-2 mysql-standard-4.1.1-alpha-pc-linux-i686 on a redhat9 os running a 2.4.20-30.9 kernel to be fair we installed the mysql-client after we got the first error message. Before that we had no mysql-client and added users via mysqladmin. I don't think I can upgrade the php-mysql any higher yet. Can someone let us know what we need to upgrade? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
syntax error on create
hi I am new to mysql and have a create script that when sourced throws a syntax error. The script : CREATE TABLE employee ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE position ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, position_type INTEGER UNSIGNED NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE company ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE company_position ( company_id INTEGER UNSIGNED NOT NULL, position_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY(company_id, position_id), INDEX company_id_FKIndex1(company_id), INDEX position_id_FKIndex2(position_id), FOREIGN KEY(company_id) REFERENCES company(id) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY(position_id) REFERENCES position(id) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; returns : 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 'position(id) ON DELETE NO ACTION ON UPDATE NO ACT Can anyone explain why this is happening ??? Thanks in advance. Warwick Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many server instances
Howdy all, I am having trouble configuring my server parameters with my.cnf because there seems to be several mysql server instances (mysqld processes) running on my linux box. What I would like to do is bump up the innodb_buffer_pool_size on the server to 512M (the box has 3GB of RAM). The problem is that there seem to be several instances of mysql server running concurrently on the box. Each one of them allocates 512M for the buffer pool and the box grinds to a halt, completely out of RAM to do anything! I've included a snippet of the top command display (I hope that you can read it easily). PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM CTIME CPU COMMAND 18913 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:01 3 mysqld 18914 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 1 mysqld 18915 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:01 1 mysqld 18916 mysql 15 0 30160 29M 2944 S 0.0 0.9 83:48 0 mysqld 18917 mysql 15 0 30160 29M 2944 S 0.0 0.9 6:25 1 mysqld 18918 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 3 mysqld 18919 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:32 0 mysqld 18920 mysql 15 0 30160 29M 2944 S 0.0 0.9 12:05 1 mysqld 18921 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 3 mysqld 31277 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:24 3 mysqld 31807 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 0 mysqld 31808 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:02 0 mysqld 32211 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 0 mysqld 32212 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 2 mysqld In windows when I run mysql as a service there is only one mysql process running. The configuration settings have proven to work quite well there because there is never more than one mysql process. So, how do I have only one instance of the server running at any given time under linux? I'm starting mysql using the mysqld_safe script through the command: service mysql start I'm pretty new to linux so perhaps that is where my problem lies. Any ideas as to what I'm doing incorrectly? Thanks, Tripp __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I specify a primary key to be primary, unique and index ?
I learned that there are three types of indexes (PRIMARY, UNIQUE, and INDEX). Now assuming I create a performance-critical PRIMARY key, will I better have to specify UNIQUE and INDEX for this column also !? It should be obvious that a primary key is unique anyway, and an index as well, shouldnt it !? Please note, I am not after saving disk space here, performance is all I am after, and such a three-fold indexing exercise just seems redundant to me in the best case scenario, or harmful even, am I right there !? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql join help
I suppose this would be easier with subselects but I am using MySQL 4: I want all orders that are of orderStatus 2 and whose orderitems contain a product that is in a productparent category greater than 2. An orderitem can only have one product, and that product has a single certain product parent (defined in the products table). This is how the tables are related: members - orders - orderitems - products - productparents I have tried the following, but I know it is not correct: SELECT count(*) FROM orders AS o LEFT JOIN members AS m USING (memberId) LEFT JOIN orderItems AS oi ON (o.orderId=oi.orderId) LEFT JOIN products AS p ON (oi.productId=p.productId) AND (p.productParentId 2) WHERE (oi.orderId IS NULL) AND (o.orderStatus=2); -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MSSQL to MYSQL
Hi Rodrigo. I'm facing a similar task but from Informix to MySQL. What I do is: 1. Obtain a SQL script to create the logical database (an Informix tool give it). You can use Erwin, with reverse engineer and the save the script. 2. Add the Type=INNODB clause for each CREATE sentence. 3. Divide the scritp into tow: one for the tables creation (with its primary key) (CreateDB.sql) and another one for the alter tables to create the foreing keys (AlterDB.sql). 4. Create a script for loading data from TXT files. First you have to save every table data from SQL Server into TXT files, then load them into MySQL tables (LoadDB.sql). 5. Run the AlterDB.sql script (step 3). It works fine to me. Regards, David. == David Carlos Brunstein System Analyst / Software Developer Buenos Aires, Argentina Mail to: David _ Brunstein @ Yahoo . Com . ar IM: DavidBrunstein @ Hotmail . Com -Original Message- From: Rodrigo Galindez [mailto:[EMAIL PROTECTED] Sent: Saturday, April 10, 2004 3:38 AM To: [EMAIL PROTECTED] Subject: MSSQL to MYSQL Hello list, I have to move a database in MSSQL to MYSQL, with the table structures and all the respective data. I tested one product to do this, SQLyog, and it works fine, except for some little problems with indexes and primary/secondary keys. I want to know if anyone have been dealing with the same problem to recommend me some suggestions/tips/tricks. Do you know another program/script/ways to do this migration ? I want to migrate everything from the original MSSQL database, like indexes, relationships, and so on. Can you guys recommend me some actions or tips to take ? Thanks in advance, -- Rodrigo Galindez Information Management Assistant Center for Human Rights and Environment (CEDHA) Gral Paz 186 10 A 5000 - Cordoba - Argentina Tel/fax 54-351-4256278 [EMAIL PROTECTED] www.cedha.org.ar -- 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]
