Re: Just a small newbie question
Hi Charlie, Don't worry, all questions are OK. :-) Instead of trying to double-click mysql.exe, open your Command Prompt and, assuming the mysql\bin directory isn't in your PATH, navigate to to mysql\bin directory and run mysql from there or specify the full path. e.g. C:\cd \mysql\bin C:\mysql\binmysql -OR- C:\C:\mysql\bin\mysql (Sorry if you already know how to do that. ;-)) And then when mysql quits, the window won't close but will stay there and you can see what the error message is. Tell us what that error is. Hope that helps. Matt - Original Message - From: Charlie Brewer Sent: Monday, September 22, 2003 12:05 AM Subject: Just a small newbie question G'Evening, Im extremely new to MySQL. Im sitting here using a self teach book to try to guide my way through it. Anyways, the book is discussing adding users and setting priveledges. Now the book is vague on how to do so, but I believe I use the bin/mysql.exe file (Im on windows obviously). Problem is when I go to it, all it does and open and close real fast. Am I doing something wrong? Thanks, sorry for the noob question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any ideas on how to authenticate to mysql thru PAM?
Sorry for repeat email...but this seems like an omission in mysql functionality. Sooo many apps come w/pam support, or the ability to use ssl. I realize that in order to use an ssl cert, you'd somehow need to lookup the subject dn in the cert and go against ldap to get a uname/pwd, etc. From a web application, like php, I know I can use existing auth name/pwd vars and pass them thru to mysql...works great. Other web content mgmt systems (Cocoon) and things like JSP could easily do the same thing. But I'm tired of entering my uname/pwd on the command line!! There must be something I'm missing in getting this to happen. I really don't want to write a script that does this as I generally don't like to keep creds in anything except root-owned /etc/shadow, etc. Then again, a user's private certs are only protected by the user's own credentials...so I guess it wouldn't be TOO stupid to create a script owned by user that passes user password thru to mysql...but this smells hacky (not in good sense). mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to update/set a default value for field via delete statement
Daevid Vincent [EMAIL PROTECTED] wrote: I have my schema set so that a field in a table has a default value of 16. I also have a script that initializes the database, but I don't want to delete the record since I want the other fields' data preserved. Is there a way to find out what the default schema value is so that I can issue an UPDATE and set it back? You can use SHOW COLUMNS or SHOW CREATE TABLE statements. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE failing on OS X Server
Bill Leonard [EMAIL PROTECTED] wrote: Let me preface this by saying I am not a MySQL guru myself, but more of a general sys admin... ! One of our users is trying to execute the LOAD DATA INFILE command (using PHP to talk to MySQL). It is failing for some reason... Everything I believe is set properly, i.e: - the user has the file priv enabled - the file they are reading is world readable - they are specifing absolute path (also tried relative path as well) Please, provide exactly error message. System specifics is MySQL 3.23.53 on OS X Server 10.2.4, this is Apple's standard build. PHP is at 4.3.1 The user has a pretty much identical test environment he claims it works there (and works even without the file priv enabled) Does LOAD DATA INFILE work without FILE privilege or LOAD DATA LOCAL? So there is something fundamental I am not seeing... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X Installation and Setup
After successfully setting up PostgreSQL and better understanding the user and permission problems I attempted to reinstall mySQL which had been running but b/c of permission stuff i could only access databases that began with test. I installed the OS X bundle with Startup module and then got the server up and running no problem but as soon as I tried to get the mysqladmin i got the infamous ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) later i tried again and it seems to have gotten worse, i looked into the .err file after the server wouldn't start up after i aborted the first time and this was what it had: 030922 16:37:52 mysqld started 030922 16:37:53 Can't start server: Bind on TCP/IP port: Address already in use 030922 16:37:53 Do you already have another mysqld server running on port: 3306 ? 030922 16:37:53 Aborting I tried to kill 3306 and it claimed there was no such process I have a GUI fronted called MacSQL already installed that i was using before when I could only edit test_... db's in mySQL but I doubt that could be causing a problem since it didn't for PostgreSQL so i really don't know what is going on with this. Sorry to be bothering the list again but i followed the directions the best I could and this really shouldn't be this problematic, hopefully if i can get it work I can help clarify the documentation with my experiences. Thanks again -- Andy Callan St. Thomas More College Rm M112 Mounts Bay Rd. Crawley, WA 6009 IM: CALid05 www.nd.edu/~acallan1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
plz help
I have this statement that works perfectly fine in Access and MSSQL: (In words, i first get the top 50 id values according to the where clause and then i get the maximum of those id values) SELECT Max(id) FROM (select top 50 id from systemevents where infoId in ( 3 ) AND id 100 order by id ASC) AS t1 (Note that id 100 changes in every iteration of the loop in my code) I want to run a similar query in MYSQLIs there *any* way to accomplish it ? Thanx in advance Best Regards Wajih -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW SLAVE HOSTS returns nothing ?
Ion-Mihai Tetcu [EMAIL PROTECTED] wrote: I had set up replication between 2 4.0.14 servers and everything seems to work OK. Except that SHOW SLAVE HOSTS on muster returns nothing. What am I missing ? Use report-host option on the slave: http://www.mysql.com/doc/en/Replication_Options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: plz help
Use the LIMIT clause in mysql instead of TOP clause for access and mssql. Luck ! - Original Message - From: Wajih-ur-Rehman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 22, 2003 12:22 PM Subject: plz help I have this statement that works perfectly fine in Access and MSSQL: (In words, i first get the top 50 id values according to the where clause and then i get the maximum of those id values) SELECT Max(id) FROM (select top 50 id from systemevents where infoId in ( 3 ) AND id 100 order by id ASC) AS t1 (Note that id 100 changes in every iteration of the loop in my code) I want to run a similar query in MYSQLIs there *any* way to accomplish it ? Thanx in advance Best Regards Wajih -- 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: plz help
I know about this limit statement but even if i write the following query, it *still does not work* because of the nested select statement: SELECT Max(id) FROM (select id from systemevents where infoId in ( 3 ) AND id 100 order by id ASC LIMIT 50) AS t1 - Original Message - From: Catalin PLACINTA [EMAIL PROTECTED] To: Wajih-ur-Rehman [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, September 22, 2003 2:33 PM Subject: Re: plz help Use the LIMIT clause in mysql instead of TOP clause for access and mssql. Luck ! - Original Message - From: Wajih-ur-Rehman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 22, 2003 12:22 PM Subject: plz help I have this statement that works perfectly fine in Access and MSSQL: (In words, i first get the top 50 id values according to the where clause and then i get the maximum of those id values) SELECT Max(id) FROM (select top 50 id from systemevents where infoId in ( 3 ) AND id 100 order by id ASC) AS t1 (Note that id 100 changes in every iteration of the loop in my code) I want to run a similar query in MYSQLIs there *any* way to accomplish it ? Thanx in advance Best Regards Wajih -- 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]
AW: AW: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT
Dear Toro Hill, Thank you for the gentle explanation, I believe you mean this: Hence it is not possible to insert, update or modify anything into TABLE PRODUCT_ORDER Because : No is auto Increment, cannot be directly manipulated, product_category product_id customer_id is all Foreign Keys and hence will be actualised through the references indirectly, when they are changed, the new values will be propagated, no insert statement is possible into a foreign key or anything which is auto Increment, Please correct me, foreign key constraints prevent values to be modified in variables, it is only possible through references, When an external variable is inserted, and through references copied into TABLE PRODUCT_ORDER, then the auto increment will do what it should. #INSERT INTOPRODUCT_ORDER(some variable) VALUES( some values ); is not possible, for any variable or value. Is this true for PRODUCT_ORDER ? Pleas tell me, what is the purpose of ON UPDATE CASCADE ON DELETE RESTRICT ? Is that necessary ? Why ? Example please ? Yours Sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Toro Hill [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 17. September 2003 01:35 An: Morten Gulbrandsen Cc: [EMAIL PROTECTED] Betreff: Re: AW: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT The first foreign key contraints in the following table definition mean this: Any record that is inserted into the product_order table must have values for product_category and product_id that exist in the category and id fields of a record in the product table. The second foreign key contraints means that any record that is inserted into the product_order table must have a value for customer_id that exists in the id field of a record in the customer table. CREATE TABLE PRODUCT_ORDER ( noINT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_idINT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id) ) TYPE=INNODB; Therefore, when you try and execute the last insert statement below it fails because the value for customer_id is not in the id field of any of the records in the customer table. Hence the foreign key constraint defined stops you from doing the last insert, which is what it should do. INSERT INTOPRODUCT(category, id, price) VALUES(1, 1, 0.1 ); INSERT INTOCUSTOMER(id) VALUES (2); INSERT INTOPRODUCT_ORDER(customer_id) VALUES(1); I hope this helps. Toro -Ursprüngliche Nachricht- Von: Toro Hill [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 16. September 2003 03:36 An: Morten Gulbrandsen Cc: [EMAIL PROTECTED] Betreff: Re: FOREIGN KEY() REFERENCES ON UPDATE CASCADE ON DELETE RESTRICT I believe that your ON UPDATE CASCADE clause should be in the definition for the PRODUCT and CUSTOMER table rather than the PRODUCT_ORDER table. However, I don't think that it will work how you expect. ON UPDATE CASCADE means that everytime you update a row in this table then all rows in other tables that reference this table (via a foreign key) will be updated also. So if there are no rows in PRODUCT_ORDER then ON UPDATE CASCADE will not insert new rows when you add rows to the other tables. What will happen instead is that any row (that already exists) in your PRODUCT_ORDER table will be updated with the new data that has been updated in one of the other tables. This is my understanding of how it works anyway. For further information go to http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Hope this helps. Toro Morten Dear Toro Thank you for your honest comment, the statement ON UPDATE CASCADE does not INSERT, UPDATE or CASCADE anything. The code needs explanation. It is from the reference manual as a complex example with minor modifications. I hope that the company MySQL AB could take a look at it. Confer 7.5.5.2 FOREIGN KEY Constraints http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Please correct me, if some of you are able to get anything relational out of it. The first I expect from any database is that the examples from the Reference manual compiles and runs. I do get error messages, which are 'correct' I believe due to the constraints. But no expected relational behaviour. Like the parent child example from the reference manual. I believe the tables PRODUCT and CUSTOMER are entity types and the table PRODUCT_ORDER is a relationship type. However foreign keys are not correctly implemented in MySQL. As we all can see from this example. /Morten USE test; DROP TABLE IF EXISTS PRODUCT_ORDER,
MySQL user privileges
Hi, All my MySQL users have access to all databases and all tables, allthough I've only given them access to one. How can I correct this? Is it possible to have them *only* see their database when i.e. typing 'show databases;', and also refuse them connection to any database but their own? Thanks in advance. Best regards, Haakon Nilsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Decimal digit problem
Hi, I have a serious problem with a site. It's a ASP site running on IIS5 and MySQL 4.0.15. I have a DECIMAL(10,2) field in a table. I've now experienced that on some days, the decimal digit is ignored, meaning that a value of ie. 14.00 is represented af 1400, 135.00 as 13500. I have search all the documentation but I haven't found nothing. I've solved the problem using the FORMAT function but I'd preffer to using the code with formatnumber asp function instead the rewriting all the code and querys. Does anybody now the possible solution? TIA, Alessandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication fails after reboot of master
I have two MySQL machines, each is both master and slave for each other (to en sure high availability). Then today I rebooted one machine, and when the machine came online again, the other machine did not replicate from the rebooted machine any more. Why can that be, do I have to do something special after a reboot? I have now done a LOAD DATA FROM MASTER; on the machine that was not rebooted, and now it works again. Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
errors
Hello I often get these errormessages. Why? * X clients is using or hasnt closed the table properly (No clients are using the table.) * found X parts, should be X * table is marked as crash (table stopped working, i need to repair it) Im using mysql-nt 4.0.12 Myodbc 2.x on some systems and myodbc 3.51.x on some. I got a service(win32) that have a odbcpool (singleton) that keeps connections open and hands them to other threads (iocp) when requested. There are also a webserver that access the db through odbc. I've not specified any specific instructions when creating the tables. //Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join not using first primay key, per explain
I have four tables that i'm trying to join together most are pretty small(100-200 rows tops) and one, the Response table is 127,000 rows. The query i'm currently executing is SELECT Business_Unit.Business_Unit, Question.Text_Long, AVG(Response) from Question INNER JOIN Response on Question.Question_Key = Response.Question_Key INNER JOIN Survey_Response on Survey_Response.Survey_Key = Response.Survey_Key INNER JOIN Business_Unit on Survey_Response.BUKey = Business_Unit.BUKey WHERE Question.SurveyID = 1 Group by Business_Unit.BUKey I'll actually need to join in two more tables, but to this point MySQL contol center won't execute the query, telling me that it would have to examine to many records. The Query above returns in about 12 seconds adn i'd really like to cut that down, if possible. When looking at EXPLAIN it doesn't seem to be using the first KEY from the Question table...which i'd have to imaging is slowing it down considerably..or is it, there seems to be very little information based on the rest of the data. +++---++-++++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-++++ | Question| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 49 | where used; Using temporary| | Response| ref| PRIMARY | PRIMARY| 4 | Question.Question_Key| 1267 || | Survey_Response| eq_ref | PRIMARY | PRIMARY| 4 | Response.Survey_Key| 1 || | Business_Unit| eq_ref | PRIMARY | PRIMARY| 4 | Survey_Response.BUKey| 1 || +++---++-++++ Any Thoughts/Suggestions are apprecitated. Jeff Question -- +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | [NULL] | | | Text_Short | varchar(255) | YES | | [NULL] | | | Category_ID | int(11) | YES | | [NULL] | | | SurveyID| int(11) | | PRI | 0 | | | End_Date| datetime | YES | | [NULL] | | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ **This has the Primary key at the end of the table...would this matter to MySQL??** The layout of the three tables are as follows Response (127,000) +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ Survey_Response +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | Survey_Key| int(11) | | PRI | [NULL] | auto_increment | | Sex | varchar(5) | YES | | [NULL] || | Age | varchar(5) | YES | | [NULL] || | Ethnicity | varchar(5) | YES | | [NULL] || | Title | varchar(5) | YES | | [NULL] || | Functional_Area | varchar(5) | | | || | Years_of_Service | varchar(5) | YES | | [NULL] || | Employment_Source | varchar(20) | | | || | BUKey | int(11) | YES | | [NULL] || +---+-+--+-+-++ Business_Unit +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | BUKey | int(11) | | PRI | 0 | | | BU_Number | int(11) | | | 0 | | | Business_Unit | varchar(55) | YES | | [NULL] | | | End_Date | datetime| YES | | [NULL] | | | RegionKey | int(11) | YES | | [NULL] | | | Count | int(6) | | | 0 | | +---+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication fails after reboot of master
S?ren Neigaard [EMAIL PROTECTED] wrote: I have two MySQL machines, each is both master and slave for each other (to en sure high availability). Then today I rebooted one machine, and when the machine came online again, the other machine did not replicate from the rebooted machine any more. Why can that be, do I have to do something special after a reboot? Check error log, you can see error message here. I have now done a LOAD DATA FROM MASTER; on the machine that was not rebooted, and now it works again. -- 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]
Re: MySQL user privileges
H?kon Nilsen \(Exinet AS\) [EMAIL PROTECTED] wrote: All my MySQL users have access to all databases and all tables, allthough I've only given them access to one. How can I correct this? Do you have entry for anonymous user in the table 'user'? Is it possible to have them *only* see their database when i.e. typing 'show databases;', and also refuse them connection to any database but their own? If you use 3.23.xx you should run mysqld with --safe-show-database option: http://www.mysql.com/doc/en/Command-line_options.html From 4.0 user should have SHOW DATABASES privilege. -- 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]
Re: LOAD DATA INFILE failing on OS X Server
Thank you for the response Victor! On 9/22/03 4:02 AM, Victoria Reznichenko [EMAIL PROTECTED] wrote: One of our users is trying to execute the LOAD DATA INFILE command (using PHP to talk to MySQL). It is failing for some reason... Everything I believe is set properly, i.e: - the user has the file priv enabled - the file they are reading is world readable - they are specifing absolute path (also tried relative path as well) Please, provide exactly error message. The .err log is very slight on comments... Only startups and shutdowns save for a couple of errors in August. So it doesn¹t appeat to be recording anyting about why this particular command is not working. Is there a way to make logging more verbose, or a different location for another log I am not aware of? System specifics is MySQL 3.23.53 on OS X Server 10.2.4, this is Apple's standard build. PHP is at 4.3.1 The user has a pretty much identical test environment he claims it works there (and works even without the file priv enabled) Does LOAD DATA INFILE work without FILE privilege or LOAD DATA LOCAL? LOAD DATA INFILE does work on his development machine without (and with) the FILE priv. Have not yet tested LOAD DATA LOCAL in either case (it wouldn't help solve the task if it did, but if it helps troubleshoot this problem we will try! Thanks, Bill -- Bill Leonard [EMAIL PROTECTED] www.machinemen.com407.464.0147 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GIS (ARC GIS, ARC info, IDRISI, ...) MySQL Interaction?
Dear all, At the moment I'm working via an R (geoR) MySQl for the querying, analysis and displaying of geographical data. The problem is that this is not a GIS environment. Does somebody not how I can query results via ARC INFO (or another GISpackage) out of our mySQL database and create a raster or shape file? The data that we are working with is point data (latitude, longitude, z=data). (arc: rodbc connenct...) Were can I find more info about the MySQL - GIS interaction? Who can I contact? The easiest would be of course to export the data via mysql save it as a table and then (secondly) import it in ARCgis, IDRISI and ARCview... Thanks, Jan __ Jan Verbesselt Research Associate Lab of Geomatics and Forest Engineering K.U. Leuven Vital Decosterstraat 102. B-3000 Leuven Belgium Tel:+32-16-329750 Fax: +32-16-329760 http://perswww.kuleuven.ac.be/~u0027178/VCard/mycard.php?name=janv http://gloveg.kuleuven.ac.be/ __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL user privileges
Thank you, Egor, I *had* the anonymous user, but I deleted it since I couldn't find the use for it. But I figured out what the problem was. I was using --safe-show-database, but it didn't seem to work. My error was that I gave users privileges. I also gave the privileges to the user on the database. So I removed the privileges on the user (select, insert, delete, insert), and then it worked. So, now the users don't have any privileges as a user, but privileges on spesific databases for the user. Thanks! Best regards, Haakon Nilsen - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 22, 2003 2:32 PM Subject: Re: MySQL user privileges H?kon Nilsen \(Exinet AS\) [EMAIL PROTECTED] wrote: All my MySQL users have access to all databases and all tables, allthough I've only given them access to one. How can I correct this? Do you have entry for anonymous user in the table 'user'? Is it possible to have them *only* see their database when i.e. typing 'show databases;', and also refuse them connection to any database but their own? If you use 3.23.xx you should run mysqld with --safe-show-database option: http://www.mysql.com/doc/en/Command-line_options.html From 4.0 user should have SHOW DATABASES privilege. -- 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]
Re: Mysqlhotcopy and incorrect database name error - We must be dense
Gilmore, Jason [EMAIL PROTECTED] wrote: A mysqlhotcopy issue has stumped no less than three of us here today at work, ensuring that the solution is glaringly obvious. We're trying to use mysqlhotcopy to copy a few databases. We want to make a local copy, nothing exotic, just want to move the database backups to the directory /backups/. In particular, we're attempting to make use of the following syntax: mysqlhotcopy db_name [/path/to/new_directory] However, we receive an error when executing the following: %mysqlhotcopy -u root --suffix=091703 staff /backups If you use --suffix option you should not specify location, because in this case mysqlhotcopy create a new database, f.e. staff091703, in the MySQL data dir. That is why you get incorrect database name error. The error is: DBI connect('../backups/wjgilmore/;host=localhost;mysql_read_default_group=m ysqlhotcopy','root',...) failed: Incorrect database name '/backups/' at ./mysqlhotcopy line 747 In short, what's happening is that mysqlhotcopy thinks that /backups/ is a database that we'd like backed-up. Which, according to the following syntax form (shown in the mysqlhotcopy page of the MySQL manual): mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory Interestingly, when using a regular expression, the destination directory is recognized immediately: %mysqlhotcopy jan-2003./^sales/ /backups/ The Juicy details: * Redhat 7.2 * Perl 5.6.1 * Mysql 4.0.9-gamma (although we've tried mysqlhotcopy on three different mysql versions, with no luck) * Yes, the user has permission to write to /backups/ * Yes, the user has select and reload permissions. Thanks for any insight. This is driving all of us crazy. Also looked all over the newsgroups regarding this, a few individuals have posted similar questions, however none were answered. -- 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]
User variables not working
Hi group, Is there anything I need to set in MySQL in order to use USER variables? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
Difference between FLOAT and DECIMAL numbers
Will someone tell me the real difference between FLOAT numbers and DECIMAL numbers? What are the implications when I use either of those types? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
Pageouts
On OS X, when issuing the top command in the CLI, my pageouts value is: large number (0) eg: the number in parentheses is zero, but the other number is large. Is this a sign of a problem? It's a rather busy server with around 80 databases totalling 500MB or so. If this is a problem, how do I resolve it? Thanks! - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Querying for continuous bookings
Hi there, My problem at this time is following: I have a table of position bookings, having information of a position code, beginning time of the booking and end time: Pos | Begings | Ends APP | 2003-09-30 11:00:00 | 2003-09-30 12:15:00 APP | 2003-09-30 12:15:00 | 2003-09-30 13:00:00 DEP | 2003-09-30 10:30:00 | 2003-09-30 13:30:00 ... Now I should make a query that, in some way, gives me an information of the positions that are booked without any pause for specified time. For example, 2003-09-30 11:00 - 2003-09-30 13:00 should return APP and DEP. Anyway, if I queried for period of 2003-09-30 10:30 - 2003-09-30 12:20, I should receive only DEP. Any ideas how to build such a query? Thanks for information, Ville M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: join not using first primay key, per explain
Jeff, Try creating a new index on Question containing just the question_key field, and try it again. Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 22 September 2003 13:23 To: [EMAIL PROTECTED] Subject: join not using first primay key, per explain I have four tables that i'm trying to join together most are pretty small(100-200 rows tops) and one, the Response table is 127,000 rows. The query i'm currently executing is SELECT Business_Unit.Business_Unit, Question.Text_Long, AVG(Response) from Question INNER JOIN Response on Question.Question_Key = Response.Question_Key INNER JOIN Survey_Response on Survey_Response.Survey_Key = Response.Survey_Key INNER JOIN Business_Unit on Survey_Response.BUKey = Business_Unit.BUKey WHERE Question.SurveyID = 1 Group by Business_Unit.BUKey I'll actually need to join in two more tables, but to this point MySQL contol center won't execute the query, telling me that it would have to examine to many records. The Query above returns in about 12 seconds adn i'd really like to cut that down, if possible. When looking at EXPLAIN it doesn't seem to be using the first KEY from the Question table...which i'd have to imaging is slowing it down considerably..or is it, there seems to be very little information based on the rest of the data. +++---++-++--- -++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-++--- -++ | Question| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 49 | where used; Using temporary| | Response| ref| PRIMARY | PRIMARY| 4 | Question.Question_Key| 1267 || | Survey_Response| eq_ref | PRIMARY | PRIMARY| 4 | Response.Survey_Key| 1 || | Business_Unit| eq_ref | PRIMARY | PRIMARY| 4 | Survey_Response.BUKey| 1 || +++---++-++--- -++ Any Thoughts/Suggestions are apprecitated. Jeff Question -- +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | [NULL] | | | Text_Short | varchar(255) | YES | | [NULL] | | | Category_ID | int(11) | YES | | [NULL] | | | SurveyID| int(11) | | PRI | 0 | | | End_Date| datetime | YES | | [NULL] | | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ **This has the Primary key at the end of the table...would this matter to MySQL??** The layout of the three tables are as follows Response (127,000) +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ Survey_Response +---+-+--+-+-+ + | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+ + | Survey_Key| int(11) | | PRI | [NULL] | auto_increment | | Sex | varchar(5) | YES | | [NULL] | | | Age | varchar(5) | YES | | [NULL] | | | Ethnicity | varchar(5) | YES | | [NULL] | | | Title | varchar(5) | YES | | [NULL] | | | Functional_Area | varchar(5) | | | | | | Years_of_Service | varchar(5) | YES | | [NULL] | | | Employment_Source | varchar(20) | | | | | | BUKey | int(11) | YES | | [NULL] | | +---+-+--+-+-+ + Business_Unit +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | BUKey | int(11) | | PRI | 0 | | | BU_Number | int(11) | | | 0 | | | Business_Unit | varchar(55) | YES | | [NULL] | | | End_Date | datetime| YES | | [NULL] | | | RegionKey | int(11) | YES | | [NULL] |
Re: Querying for continuous bookings
well, I am working with mysql for less than 2 months so I may not give you right answer but I guess we cannot solve this problem without programming. The point is how to combine the times to have a one POS with continuous time. this query is an example to combine to records: SELECT table1.pos, table1.Begings, table2.Ends FROM mytable as table1, mytable as table2 WHERE table1.pos = table2.pos AND table1.Ends = table2.Begings ORDER BY table1.pos, table1.Begings, table2.Begings but this is not working if there are 3 records (or more) which should be combined together. so if you don't have this case, you can work more on this query to have the other records and do the search on the final query. - Original Message - From: Ville Mattila [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 22, 2003 10:56 AM Subject: Querying for continuous bookings Hi there, My problem at this time is following: I have a table of position bookings, having information of a position code, beginning time of the booking and end time: Pos | Begings | Ends APP | 2003-09-30 11:00:00 | 2003-09-30 12:15:00 APP | 2003-09-30 12:15:00 | 2003-09-30 13:00:00 DEP | 2003-09-30 10:30:00 | 2003-09-30 13:30:00 ... Now I should make a query that, in some way, gives me an information of the positions that are booked without any pause for specified time. For example, 2003-09-30 11:00 - 2003-09-30 13:00 should return APP and DEP. Anyway, if I queried for period of 2003-09-30 10:30 - 2003-09-30 12:20, I should receive only DEP. Any ideas how to build such a query? Thanks for information, Ville M. -- 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: Pageouts
John May wrote: On OS X, when issuing the top command in the CLI, my pageouts value is: large number (0) eg: the number in parentheses is zero, but the other number is large. Is this a sign of a problem? It's a rather busy server with around 80 databases totalling 500MB or so. If this is a problem, how do I resolve it? It's not a problem. The large number is the number of pageouts since restart, the number in parentheses is the number of pageouts in the last second. If you see the number in parentheses is non-zero frequently, then you're havinig paging. In this case you should set up and edit a my.cnf file to tune the memory parameters of mysqld so it doesn't page. The details of top can be found by typing 'man top' in the terminal window. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHA literal String Syntax Help
I need help with the proper syntax of my INSERT Statement. I have spoken to the support staff of my RADIUS Vendor they stated that enable to support SHA The Coolum for password has to have the encrypted password prefixed with {SHA} not just the hash I need to include the literal string of {SHA} plus the hash, see below. Quote from support When you select a user password from the table Radius will then retrieve: {SHA}15346b593c4d0cf05fb6e67a5669d852e6550481 This one encrypts the whole string {SHA}'smith' mysql INSERT INTO user_profile (userid,password,alias,profile) - VALUES ('bob',SHA1({SHA}'smith'),'max',default); Query OK, 1 row affected (0.00 sec) This one pukes mysql INSERT INTO user_profile (userid,password,alias,profile) - VALUES ('bob',({SHA}SHA1'smith'),'max',default); ERROR 1064 You have an error in you SQL syntax This one has a could mismatch mysql INSERT INTO user_profile (userid,password,alias,profile) - VALUES ('bob',SHA,HA1'smith'),'max',default); Please help I'm new to SQL and it's syntax flow. Thanks Jeff Stout CSG Systems, Inc. 303-200-3204 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie Quote Question
I know this may seem like a trivial question, however I am new to SQL and it's syntax. I need to know what the difference between single 'quoting' and double quoting a string. When and why do I use one or the other, Any help answering this would be greatly appreciated. Thanks Jeff Stout CSG Systems, Inc. 303-200-3204 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb
Does anybody know what this error is all about? and how do to get rid of it... It started when I upgraded 4.0.13 to 4.0.15 --- 030922 5:17:30 InnoDB: Error: page 1 log sequence number 0 768348475 InnoDB: is in the future! Current system log sequence number 0 330400180. InnoDB: Your database may be corrupt. -- -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with optimizing table
One of the developers is having a problem optimizing a table in MySQL 4.0.14-standard on Solaris 9. The optimize seems to succeed but when he tries to access the table with a SQL client, we get an error of something to the effect that MySQL cannot fine MESSAGES.MYI. After running 'myisamchk -o' on the table, corruption is found and repaired. This happens every time the optimize is run on the table. I think the problem may be reltated to disk space; we are rather low on some file systems. Our pet theory is that the optimize writes out a temporary DB which fails. Does anyone know what exactly optimize writes out to the OS? I cannot find any information on this. Thanks, Ben Ricker Wellinx.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and php Installation for Linux.
Hi, I am trying to install Mantis on Linux but I am totally new for linux as well as for Mantis. I have managed to install Linux. I was not sure which components are required for Mantis So I have selected all the components. Now my Linux is UP and need you help for the further installation. Please let me know how to install Mysql and PHP machine. Please advice the necessary steps for the installation. With Warm Regards Harpreet Singh Chana Phone : @ 4326 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb
Gustavo, - Original Message - From: Gustavo A. Baratto [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, September 22, 2003 7:33 PM Subject: innodb Does anybody know what this error is all about? and how do to get rid of it... It started when I upgraded 4.0.13 to 4.0.15 --- 030922 5:17:30 InnoDB: Error: page 1 log sequence number 0 768348475 InnoDB: is in the future! Current system log sequence number 0 330400180. InnoDB: Your database may be corrupt. -- you have probably put old ib_logfiles to your database, and the log sequence number in the log files is lagging behind what is in the ibdata files. You can artificially inflate the log sequence number of the log files by creating a dummy table and inserting and deleting rows in it. Increasing the lsn by 500 MB should take less than an hour. You can monitor the lsn with SHOW INNODB STATUS. After that run CHECK TABLE on all your tables. Wrong log sequence numbers can cause corruption. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Jon Hancock [EMAIL PROTECTED] writes: When you issue this null ALTER TABLE, is the entire table locked during the build? The table is read-only during the build. i.e. Is the only way to defragment to effectively take the table offline during the rebuild? Well. Not quite offline, but almost. Is there a method to estimate time to do this rebuild? I usually go with rows * 1/5000 seconds. But it varies with hardware and table complexity. My tables are not very complex. thanks, Jon - Original Message - From: Per Andreas Buer [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:17 PM Subject: Re: innodb and fragmentation Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- 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] -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie Quote Question
as I know, there is no any difference between single and double except the environment. in ANSI mode, just you should use single quotation. for more : http://www.mysql.com/doc/en/String_syntax.html - Original Message - From: Stout, Jeff [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 22, 2003 12:19 PM Subject: newbie Quote Question I know this may seem like a trivial question, however I am new to SQL and it's syntax. I need to know what the difference between single 'quoting' and double quoting a string. When and why do I use one or the other, Any help answering this would be greatly appreciated. Thanks Jeff Stout CSG Systems, Inc. 303-200-3204 -- 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: Changing the data directory.
At 4:22 PM -0400 9/19/03, Jeff McKeon wrote: We've got mysql 3.23 installed on a redhat system via the rpm's that come with RedHat 8.0. I'd like to change the default data directory so something other than /var/lib/mysql. I know this is supposed to be possible with a start switch of --datadir=/path/to/data but it doesn't seem to work. It should work. Perhaps I'm putting the switch in incorrectly then because when I do... /etc/init.d/mysqld --datadir=/path/to/data And then do... Mysqladmin variables The output says the data directory is the default /var/lib/mysql/ The startup of mysql uses the /etc/init.d/mysqld script so I suppose I'll need to change something in that and add the switch, I just can't seem to figure out where. I wouldn't change the script, it'll get overwritten if you upgrade. You might try editing /etc/my.cnf (create it if it doesn't exist) and add this to it: [mysqld] datadir=/path/to/data This I tried and the results were that the server didn't start properly... Changed the my.cnf to: [mysqld] datadir=/DATA/mysql/data socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Stopped and started mysqld: [EMAIL PROTECTED] root]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [EMAIL PROTECTED] root]# /etc/init.d/mysqld start Starting MySQL:[ OK ] [EMAIL PROTECTED] root]# mysqladmin variables mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! Alternatively, rename /var/lib/mysql to something else (or remove it) and recreate /var/lib/mysql as a symlink to where you really want the data directory. Make sure the target of the symlink exists. Any suggestions? Thanks, Jeff -- 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/[EMAIL PROTECTED]
RE: Mysqlhotcopy and incorrect database name error - We must be dense
Your not using mysqlhotcopy correctly. /usr/bin/mysqlhotcopy db_name[./table_regex/] [new_db_name | directory] The problem is this mysqlhotcopy -u root --suffix=091703 staff /backups look at -u here is a format from inside mysqlhotcopy mysqlhotcopy --method='scp -Bq -i /usr/home/foo/.ssh/identity' --user=root --password=secretpassword \ db_1./^nice_table/ [EMAIL PROTECTED]:~/path/to/new_directory - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Gilmore, Jason [mailto:[EMAIL PROTECTED] --Sent: Monday, September 22, 2003 4:35 AM --To: [EMAIL PROTECTED] --Subject: FW: Mysqlhotcopy and incorrect database name error - We must be --dense -- --Good morning, -- --We're still battling with this; haven't heard back from anyone on the --list... Resending one more time in the hopes that it catches somebody's --eye. -- --Thanks so much, --Jason -- ---Original Message- --From: Gilmore, Jason --Sent: Wednesday, September 17, 2003 1:36 PM --To: [EMAIL PROTECTED] --Subject: Mysqlhotcopy and incorrect database name error - We must be --dense -- -- --Hi there, -- --A mysqlhotcopy issue has stumped no less than three of us here today at --work, ensuring that the solution is glaringly obvious. We're trying to --use mysqlhotcopy to copy a few databases. We want to make a local copy, --nothing exotic, just want to move the database backups to the directory --/backups/. -- --In particular, we're attempting to make use of the following syntax: -- --mysqlhotcopy db_name [/path/to/new_directory] -- --However, we receive an error when executing the following: -- --% -- --The error is: --DBI --connect('../backups/wjgilmore/;host=localhost;mysql_read_default_grou p=m --ysqlhotcopy','root',...) failed: Incorrect database name '/backups/' at --./mysqlhotcopy line 747 -- --In short, what's happening is that mysqlhotcopy thinks that /backups/ is --a database that we'd like backed-up. Which, according to the following --syntax form (shown in the mysqlhotcopy page of the MySQL manual): -- --mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory -- --Interestingly, when using a regular expression, the destination --directory is recognized immediately: -- --%mysqlhotcopy jan-2003./^sales/ /backups/ -- --The Juicy details: --* Redhat 7.2 --* Perl 5.6.1 --* Mysql 4.0.9-gamma (although we've tried mysqlhotcopy on three --different mysql versions, with no luck) --* Yes, the user has permission to write to /backups/ --* Yes, the user has select and reload permissions. -- --Thanks for any insight. This is driving all of us crazy. Also looked all --over the newsgroups regarding this, a few individuals have posted --similar questions, however none were answered. -- --Best, --Jason -- --== --Jason Gilmore --Systems Developer --The Fisher College of Business -- --340 Mason Hall --250 W. Woodruff Ave. --Columbus, Ohio 43210 --The Ohio State University -- --e: [EMAIL PROTECTED] --t: 614-292-9754 -- --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: Mysqlhotcopy and incorrect database name error - We must be dense
I mean look at the --suffix - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] --Sent: Monday, September 22, 2003 1:26 PM --To: 'Gilmore, Jason'; [EMAIL PROTECTED] --Subject: RE: Mysqlhotcopy and incorrect database name error - We must be --dense -- --Your not using mysqlhotcopy correctly. -- --/usr/bin/mysqlhotcopy db_name[./table_regex/] [new_db_name | directory] -- -- --The problem is this --mysqlhotcopy -u root --suffix=091703 staff /backups --look at -u -- -- --here is a format from inside mysqlhotcopy -- --mysqlhotcopy --method='scp -Bq -i /usr/home/foo/.ssh/identity' user=root --password=secretpassword \ -- db_1./^nice_table/ [EMAIL PROTECTED]:~/path/to/new_directory -- -- -- -- --- Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -Original Message- From: Gilmore, Jason [mailto:[EMAIL PROTECTED] Sent: Monday, September 22, 2003 4:35 AM To: [EMAIL PROTECTED] Subject: FW: Mysqlhotcopy and incorrect database name error - We must --be dense Good morning, We're still battling with this; haven't heard back from anyone on the list... Resending one more time in the hopes that it catches --somebody's eye. Thanks so much, Jason -Original Message- From: Gilmore, Jason Sent: Wednesday, September 17, 2003 1:36 PM To: [EMAIL PROTECTED] Subject: Mysqlhotcopy and incorrect database name error - We must be dense Hi there, A mysqlhotcopy issue has stumped no less than three of us here today --at work, ensuring that the solution is glaringly obvious. We're trying --to use mysqlhotcopy to copy a few databases. We want to make a local --copy, nothing exotic, just want to move the database backups to the --directory /backups/. In particular, we're attempting to make use of the following syntax: mysqlhotcopy db_name [/path/to/new_directory] However, we receive an error when executing the following: % The error is: DBI connect('../backups/wjgilmore/;host=localhost;mysql_read_default_g rou --p=m ysqlhotcopy','root',...) failed: Incorrect database name '/backups/' --at ./mysqlhotcopy line 747 In short, what's happening is that mysqlhotcopy thinks that /backups/ --is a database that we'd like backed-up. Which, according to the --following syntax form (shown in the mysqlhotcopy page of the MySQL manual): mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory Interestingly, when using a regular expression, the destination directory is recognized immediately: %mysqlhotcopy jan-2003./^sales/ /backups/ The Juicy details: * Redhat 7.2 * Perl 5.6.1 * Mysql 4.0.9-gamma (although we've tried mysqlhotcopy on three different mysql versions, with no luck) * Yes, the user has permission to write to /backups/ * Yes, the user has select and reload permissions. Thanks for any insight. This is driving all of us crazy. Also looked --all over the newsgroups regarding this, a few individuals have posted similar questions, however none were answered. Best, Jason == Jason Gilmore Systems Developer The Fisher College of Business 340 Mason Hall 250 W. Woodruff Ave. Columbus, Ohio 43210 The Ohio State University e: [EMAIL PROTECTED] t: 614-292-9754 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables not working
Director General: NEFACOMP [EMAIL PROTECTED] wrote: Is there anything I need to set in MySQL in order to use USER variables? No. What is wrong with user variables for you? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie gets access denied/invalid authorization (answered)
At 06:04 PM 9/21/03 -0700, Ray Tayek wrote: hi, trying out opnecms. so i installed mysql on slak 8.0. added a root password (something like ... root password opencms) like the doc says, so i can do a: use mysql and create databases and tables if am root on the slak box. but only if i am root. trying to create a database (db2) as a normal use gets a: ... turns out that the password (for mysql) did not take for some reason. i was able (as root) to manually delete the entries with empty user fields and change the password for the remaining two fields, so it works fine (got opencms to work). thanks --- ray tayek http://tayek.com/ actively seeking mentoring or telecommuting work vice chair orange county java users group http://www.ocjug.org/ hate spam? http://samspade.org/ssw/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
What follows is a short story, all true and quite stressful. No database servers were harmed in the making of this server, but a couple were threatened with loose rack mount rails. We are trying to move over to InnoDB, but we have a few problems that we just can't figure out: First, for some reason, MySQL claims it can not claim more than .5Gigs of RAM from a system that has 4Gigs of RAM total and not being used for anything but MySQL. Secondly, MySQL replication leaves a problem for our data warehouse and replication. When you use MySQL binary replication, it has been my experience that it is all or nothing. You can't choose just one database to replicate. You can start or stop just one database on the slave from being replicated. If you stop one, they all stop. This is a problem, because what we need to do is stop replication at midnight, and then do a dump of the database. Just one of them. When we stop it now, they all stop so now we have databases that are idle and not getting up to date replication while this one database gets mysqldumped for hours. InnoDB hot backup is a swell thing, but it doesn't dump the database in a useable format for anything except bringing an entire server online. This doesn't help us when we just want a simple mysqldump file of one database to do data warehouse work. Before, what we did with MyISAM was a crude but workable in house replication system that used the text file update logs to replicate to a slave. This allowed us to replicate by database, and in turn only affect that one database for replication and dumping. It also allowed us to attach a data warehouse program to the replication so that it could grab the information it needed. With MySQL binary replication, we can not do either of these activities. So, in a nutshell, this is our problems with InnoDB. I really want to get these problems fixed so I can keep InnoDB around, and use that nice hot backup program. It would be painful to have to stick with MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Formatting a string for entry into MySQL
Hi, This is more of a PHP question, but I can't find the answer. I'm trying to generate a string that contains a br, to insert into a MySQL table. It appears the br is being stripped out either just before or during the update. ** code ** // has both info, update both $ad_contact = $tr_email; $ad_contact .= br; $pSep = -; $ad_contact .= $tr_p1AC; $ad_contact .= $pSep; $ad_contact .= $tr_p1PRE; $ad_contact .= $pSep; $ad_contact .= $tr_p1SUF; // update the record $qUpdate = UPDATE subscriber_ads SET ad_contact = '$ad_contact' WHERE subid = $sid; print($qUpdatebr); $rUpdate = mysql_query($qUpdate, $CCB) or die(mysql_error()); The print() statement shows the correct format in the browser. I've looked at printf() and sprintf(), but can't quite figure the syntax. I want the br in the string so it will output correctly in the browser when that data is retrieved from the DB. Suggestions? Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumb Question - Moving Data from Access to MySQL
Dumb Question. I need to move my data from their current access database to my new MySQL server through an ODBC connection. The tables on both ends are set up identially. I have added a linked table to the access database called EXPARTAB1. The data is in EXPARTAB. I tried: INSERT INTO EXPARTAB1 VALUES (SELECT * FROM EXPARTAB) I have used exportSQL from CYNERGI which is a very simple script for access, but the file it creates is 125 Megs to add the data, and I can't easily work with that. Ugh! And I can't just UNLOAD/LOAD it all to a file like I did back in my Informix Days. It's gotta be simple, but this had been beating me up all day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Just a small newbie question
Ok well I reinstalled again and it seems to be working ok now. Thanks anyways ;) --- Matt W [EMAIL PROTECTED] wrote: Hi Charlie, Don't worry, all questions are OK. :-) Instead of trying to double-click mysql.exe, open your Command Prompt and, assuming the mysql\bin directory isn't in your PATH, navigate to to mysql\bin directory and run mysql from there or specify the full path. e.g. C:\cd \mysql\bin C:\mysql\binmysql -OR- C:\C:\mysql\bin\mysql (Sorry if you already know how to do that. ;-)) And then when mysql quits, the window won't close but will stay there and you can see what the error message is. Tell us what that error is. Hope that helps. Matt - Original Message - From: Charlie Brewer Sent: Monday, September 22, 2003 12:05 AM Subject: Just a small newbie question G'Evening, Im extremely new to MySQL. Im sitting here using a self teach book to try to guide my way through it. Anyways, the book is discussing adding users and setting priveledges. Now the book is vague on how to do so, but I believe I use the bin/mysql.exe file (Im on windows obviously). Problem is when I go to it, all it does and open and close real fast. Am I doing something wrong? Thanks, sorry for the noob question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Proud member of www.Dragonmount.com The Largest -Wheel of Time- Community on the Internet! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Excel 2 CSV into phpMyAdmin
is there a trick to saving Excel spreadsheets into a CSV format that will import into MySQL or phpMyAdmin reliably? my CSV files get jumbled up, if they load in at all. There is a bug i have been told concerning columns that contain alphanumeric and just numeric fields. I use Excel [office 2k version] on Win2k to create my CSV files. and the target apps and database is phpMyAdmin 2.5.3-rc3 with MySQL 3.23.56 -- Regards, Carmoda [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]